Excel OFFSET으로 숫자 목록의 최근 3행 합계 700 검산하기
수량 100·200·150·300·250이 차례로 쌓인 목록에서 최근 세 행은 150·300·250입니다. 합계는 700입니다. OFFSET으로 이 범위를 선택한 뒤 새 값 500을 맨 아래에 추가하면 선택 범위가 300·250·500으로 이동하고 합계는 1,050이 됩니다. 이 글은 그 이동을 셀 좌표와 숫자로 확인하는 실습입니다.
숫자 목록과 계산 셀을 분리하기
첨부 Window 시트에서 A열은 순번, B열은 수량입니다. D:E는 계산 영역이고 H:I는 설명입니다. 숫자가 늘어날 공간은 B2:B20으로 제한했습니다. 무제한 전체 열이나 다른 시트를 참조하지 않습니다. 사본을 열어 B2:B6에 다음 숫자가 있고 B7:B20은 비어 있는지 먼저 확인합니다.
| 행 | 순번 | B열 수량 |
|---|---|---|
| 2 | 1 | 100 |
| 3 | 2 | 200 |
| 4 | 3 | 150 |
| 5 | 4 | 300 |
| 6 | 5 | 250 |
여기서 최근은 날짜가 가장 늦다는 뜻이 아니라 입력 목록의 아래쪽이라는 뜻입니다. 이 실습에서는 날짜 열을 정렬하거나 거래 시점을 비교하지 않습니다. 중간에 빈칸이 없고 숫자만 이어지는 목록이라는 조건을 지켜야 합니다. 조건이 깨지면 합계 대신 입력 확인을 표시하도록 진단을 나누어 두었습니다.
OFFSET의 다섯 인수를 이 범위에 대입하기
Microsoft OFFSET 문서는 기준 셀에서 지정한 행·열만큼 떨어진 범위의 참조를 반환한다고 설명합니다. 이 예제에서 기본 형태는 다음과 같습니다.
=SUM(OFFSET($B$2,2,0,3,1))기준 B2에서 아래로 2행 이동하면 B4입니다. 열 이동은 0이므로 B열을 유지합니다. 높이는 3행, 너비는 1열이므로 반환 범위는 B4:B6입니다. 그 범위를 SUM으로 합산하면 150+300+250=700입니다. OFFSET의 두 번째 인수 2를 엑셀의 실제 행 번호 2로 오해하지 않는 것이 중요합니다.
범위 참조를 반환하는 함수이므로 합계가 필요할 때는 SUM 같은 집계 함수를 바깥에 둡니다. 참조의 이동과 합계를 한꺼번에 암기하기보다 기준 B2, 도착 B4, 끝 B6를 먼저 적어 보면 오류 위치를 좁힐 수 있습니다. 입력한 숫자는 이 설명을 위해 만든 가상 수량입니다.
입력 개수에서 이동 행 수 계산하기
첨부 E2는 =COUNT($B$2:$B$20)로 숫자 개수 5를 셉니다. E3에는 창 크기 3이 숫자로 저장되어 있습니다. E4는 다음 계산으로 시작점을 정합니다.
=MAX(0,E2-E3)5개 중 끝의 3개를 골라야 하므로 이동 행 수는 2입니다. 입력이 두 개뿐이라면 음수로 위쪽을 가리키지 않고 0행에서 시작합니다. 실제 합산 높이도 MIN(E2,E3)로 줄여 존재하는 숫자만 선택합니다. E3에는 1 이상의 정수만 넣습니다.
E5에 저장한 최종 수식은 다음과 같습니다. E6의 입력 조건이 TRUE인지 확인한 뒤에만 합산합니다.
=IF(AND(E6,E3>=1,E3=INT(E3)),SUM(OFFSET($B$2,E4,0,MIN(E2,E3),1)),"입력 확인")
E2=5, E3=3, E4=2, E5=700을 순서대로 대조합니다. E7은 =SUM(B2:B20)으로 전체 합계 1,000을 보여줍니다. 700과 1,000은 서로 다른 범위를 더한 값이므로 둘 중 하나가 틀렸다는 뜻이 아닙니다. 최근 세 행인지 전체 다섯 행인지 비교 대상을 분리해야 합니다.
중간 빈칸과 문자 숫자를 먼저 잡기
숫자 개수만 세어 범위 높이로 쓰면 중간 빈칸을 건너뛰고 끝의 정상 값을 놓칠 수 있습니다. 그래서 E6은 두 가지를 확인합니다. B2:B20에서 숫자 개수와 비어 있지 않은 셀 수가 같아야 하고, B2부터 숫자 개수만큼 내려간 연속 구간에도 그만큼의 숫자가 있어야 합니다.
=IF(E2=0,FALSE,AND(COUNT($B$2:$B$20)=COUNTA($B$2:$B$20),COUNT(OFFSET($B$2,0,0,E2,1))=E2))처음 입력은 두 조건이 모두 맞아 TRUE입니다. 사본에서 B4의 150을 지우면 숫자가 네 개 남지만 시작 네 칸 B2:B5에는 숫자가 세 개뿐입니다. E6은 FALSE가 되고 E5에는 입력 확인이 나타나야 합니다. B4를 숫자 150으로 복구하면 다시 TRUE와 700으로 돌아갑니다.
B4를 앞에 작은따옴표를 붙인 '150처럼 텍스트로 저장한 경우에도 E6은 FALSE여야 합니다. 작은따옴표는 숫자를 문자로 넣기 위한 Excel 입력 방법이며 데이터 자체에 더할 부호가 아닙니다. 셀 서식만 바꾸는 대신 값을 지운 뒤 숫자 150을 다시 입력하고 결과를 확인합니다. 이 파일은 오류 값을 임의의 0으로 바꾸어 합계가 맞는 것처럼 보이게 하지 않습니다.
새 숫자 500을 추가하는 순서
-
최초 값과 결과를 확인한 사본에서 A7에 순번 6, B7에 숫자 500을 입력합니다.
-
E2가 6, E4가 3으로 바뀌었는지 확인합니다.
-
B2에서 아래 3행인 B5부터 세 칸, 즉 B5:B7을 직접 읽습니다.
-
300+250+500=1050을 계산해 E5와 대조합니다. 전체 합계 E7은 1500입니다.
-
원래 예제로 돌아가려면 A7:B7 내용을 지우고 E5=700, E7=1000을 확인합니다.
새 값을 B8에 쓰고 B7을 비워 두면 연속 입력 조건이 깨집니다. 이때 입력 확인이 나오는 것은 합계 함수 고장이 아니라 범위 전제 위반입니다. 값을 당겨 정리할 때는 원본 순서를 먼저 확인하고 사본에서만 이동합니다. 불확실한 누락값을 0으로 채우지 않습니다.
증상별로 어느 셀부터 볼지 정하기
| 증상 | 확인 위치 | 복구 기준 |
|---|---|---|
| 입력 확인 표시 | E6, B2:B20, E3 | 연속 숫자와 양의 정수 창 크기 |
| 시작 범위가 한 행 밀림 | 기준 B2와 E4 | 최초 시작 B4, 추가 후 B5 |
| B21의 값이 집계되지 않음 | 고정 검사 범위 | 범위 확장 전 수식 전체 설계 확인 |
| 전체 합계를 최근 합계로 착각 | E5와 E7의 라벨 | 최초 700과1000 구분 |
| 결과가 숫자 대신 수식으로 보임 | 입력 셀의 텍스트 서식 | 수식으로 재입력하고 참조 확인 |
범위를 늘릴 때는 COUNT, COUNTA, 전체 합계에 쓰인 B2:B20도 함께 점검합니다. OFFSET의 한 부분만 바꾸면 입력 개수와 검사 범위가 서로 달라질 수 있습니다. 먼저 19개 이하의 작은 사본에서 재현하고 새 범위의 마지막 행까지 숫자를 넣어 확인한 뒤 업무 파일로 옮깁니다.
검증 범위와 다른 설계가 필요한 경우
완료 조건은 최초 700, 끝에500 추가 후1050, 중간 빈칸·문자 숫자에서 입력 확인, 복구 후700을 각각 확인하는 것입니다. 합계 하나만 맞는 것으로 연속성 검사를 대신하지 않습니다. 순서를 바꾸거나 중간 행을 지우면 최근 세 행의 뜻도 바뀝니다.
이 파일은 작은 연속 숫자 목록의 OFFSET 참조를 배우기 위한 자료입니다. 대규모 파일의 속도나 자동화 효과를 측정하지 않았으며 날짜 기준 최근 세 건, 조건에 맞는 마지막 세 건, 빈칸을 무시한 세 건에는 그대로 적용할 수 없습니다. 그런 목적은 입력 선택 규칙을 먼저 새로 정의해야 합니다.
Microsoft OFFSET 문서는 기준 셀에서 이동한 범위 참조와 높이·너비 인수를 설명합니다. 첨부 수식과 별도 산술을 대조했고, Microsoft Excel 앱을 직접 실행했다는 주장은 하지 않습니다. 생성 도구의 OFFSET 계산 한계와 별도 재계산 여부는 첨부 검증 설명에 구분해 적었습니다.
출처 원문 수집일: S1 2026-09-23.
작성자 소개
