- 최초 작성일: 2009-02-05
- 최종 수정일: 2026-09-24
- 조회수: 20 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 최초 판매정보 구하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
이번 강좌는 우리 사이트에서 활동하시는 김상면님께서 보내주신 내용입니다.
메일 하나(김상면님)
활용도가 엄청 떨어지지만, 주위에서 심심치 않게 자주 질문도 하고 얼마 전 엑사모 사이트에도 같은 질문이 있길래 한번 강의해 봅니다.
다음과 같은 자료에서 가장 왼쪽 혹은 가장 오른쪽 자료는 어떻게 구할까요. 즉, 각 점포별 최초 판매일의 해당 데이터를 구하고 싶다는 겁니다.
아무리 어려운 문제도 열심히 노력하면 아니 풀릴 리 없고 아무리 쉬운 문제라도 노력하지 않으면 어려운 법입니다.
한 10분만 생각해 보시고 아래의 버튼을 눌러 보세요.
- 최신 글을 불러오는 중...
최초 판매정보 구하기
핵심 요약: ISBLANK·MIN·INDIRECT 또는 OFFSET으로 첫 값 찾기
값이 드문드문 있는 행에서 가장 왼쪽(처음) 값은 MIN을, 가장 오른쪽(마지막) 값은 MAX를 써서 열 번호를 찾은 뒤 INDIRECT로 값을 가져오거나, 더 짧은 OFFSET 대안식을 씁니다.
- 최초 값: {=INDIRECT(ADDRESS(ROW(),MIN(IF(ISBLANK(범위),99999,COLUMN(범위))),4))}
- 마지막 값: 위 수식에서 MIN→MAX, 빈 셀 대체값 99999→0
- 더 짧은 대안: {=OFFSET(기준셀,0,MAX((LEN(범위)<>0)*COLUMN(범위))-오프셋)}
- 공통: 모두 배열 수식이므로 Ctrl+Shift+Enter로 입력
{=INDIRECT(ADDRESS(ROW(),MIN(IF(ISBLANK($C16:$G16),99999,COLUMN($C16:$G16))),4))}함수가 무지막지하게 어렵지요? 하나하나 설명 드리겠습니다.
ISBLANK : 함수는 주어진 셀이 공백인지 아닌지를 조사합니다.
IF : 주어진 조건이 참이면 앞쪽 수식을 거짓이면 뒤쪽 수식을 수행합니다.
MIN : 주어진 값들 중에서 최소값을 구하고
COLUMN 과 ROW는 행과 열을 구하는 함수이고
ADDRESS : 주어진 인수로부터 셀주소 문자열을 구합니다
INDIRECT : 주어진 문자열에서 셀 참조를 구합니다.
마지막으로 배열 수식이므로 ctrl + shift + enter 해야 하는거는 아시죠?
ISBLANK 함수와 IF 함수, COLUMN 함수로 공백이 아닌 셀의 열 번호를 모두 찾습니다. MIN 함수로 가장 작은 값을 찾으면 가장 왼쪽 셀의 열 번호가 되겠지요. 이제 ROW 함수로 행 번호를 얻은 후 ADDRESS 함수로 잘 조합해서 셀 주소 문자열을 구합니다. INDIRECT 함수로 문자열을 다시 셀 참조로 바꾸어서 가장 왼쪽 셀의 값을 찾는 겁니다.
다양한 함수들이 사용되었는데, 각 함수에 대한 도움말과 위 설명을 참고하시면 어렵지 않게 이해하시리라 믿습니다(택도 없는 소리 말라구요? 이런… ^^;).
오늘 강좌 여기서 끝! …이라고 하면 완전히 날로 먹는 게 되니까 조금 더 살펴보도록 하죠.
만약 최초 정보가 아니라 각 점포의 마지막 정보를 가져오려면 어떻게 해야 할까요?
그것도 문제라고… 수식에서 MIN 함수를 MAX 함수로 바꿔서
=INDIRECT(ADDRESS(ROW(),MAX(IF(ISBLANK($C16:$G16),9999,COLUMN($C16:$G16))),4))이렇게 하면 되지!!
그렇게 하시면 안되고 아래와 같이 하시면 됩니다.
{=INDIRECT(ADDRESS(ROW(),MAX(IF(ISBLANK($C16:$G16),0,COLUMN($C16:$G16))),4))}천성적으로 IF 함수에 알러지가 있는 분은 다음과 같이 하셔도 되겠군요. ^^ 사용되는 함수 종류도 적고 이해도 빠를 듯 합니다.
{=OFFSET(C4,0,MAX((LEN(C4:G4)<>0)*COLUMN(C4:G4))-3)}Offset 함수는 다들 아실테고… (잘 기억나지 않는 분은 얼른 도움말 찾아보세요!)
순서를 알려드리지요(복잡한 수식은 맨 안쪽부터 해석해 나오는 것 아시죠?).
- LEN(C4:G4)<>0): C4:G4 영역 중에서 값이 들어있는 셀을 구합니다.
- COLUMN(C4:G4)): 지정한 영역의 열 번호를 구합니다.
그런 다음, 이 둘을 곱해주면, 값이 들어있는 셀의 열 번호를 알 수 있습니다. MAX 함수를 이용하여 어떤 열의 값이 제일 큰 지를 파악하고, 마지막으로 Offset 함수를 이용하여 참조 영역을 지정하면 됩니다.
이 때 3은 왜 빼주었냐 하면… 그것은… 오래간만에 숙제로 내 드리지요!
오늘은 여기까지…
정리 — 첫 값·마지막 값 찾는 수식 정리
| 목적 | 수식(배열 수식) |
|---|---|
| 최초(가장 왼쪽) 값 | {=INDIRECT(ADDRESS(ROW(),MIN(IF(ISBLANK($C16:$G16),99999,COLUMN($C16:$G16))),4))} |
| 마지막(가장 오른쪽) 값 | {=INDIRECT(ADDRESS(ROW(),MAX(IF(ISBLANK($C16:$G16),0,COLUMN($C16:$G16))),4))} |
| 더 짧은 대안(마지막 값) | {=OFFSET(C4,0,MAX((LEN(C4:G4)<>0)*COLUMN(C4:G4))-3)} |
자주 묻는 질문 (FAQ)
Q1. 왜 MIN이나 MAX만으로는 이 문제를 풀 수 없나요?
MIN·MAX는 숫자 값 중 가장 작거나 큰 값을 찾을 뿐, 그 값이 어느 열(셀 위치)에 있는지는 알려주지 않습니다. 이 문제는 값 자체가 아니라 '가장 왼쪽/오른쪽에 값이 있는 열의 위치'를 찾아야 하므로 COLUMN, ADDRESS, INDIRECT 같은 위치 관련 함수가 추가로 필요합니다.
Q2. INDIRECT(ADDRESS(...)) 수식은 어떤 순서로 작동하나요?
먼저 ISBLANK와 IF로 값이 있는 열만 걸러 COLUMN 번호를 얻고, MIN(또는 MAX)으로 그중 가장 왼쪽(또는 오른쪽) 열 번호를 찾습니다. ADDRESS가 그 열 번호와 현재 행 번호로 셀 주소 문자열(예: "D16")을 만들고, INDIRECT가 그 문자열을 실제 셀 참조로 바꿔 값을 반환합니다.
Q3. IF 함수 없이 더 간단하게 쓸 수 있는 방법도 있나요?
네. {=OFFSET(기준셀,0,MAX((LEN(범위)<>0)*COLUMN(범위))-오프셋)} 형태를 쓰면 IF 없이도 같은 결과를 얻을 수 있습니다. LEN(범위)<>0으로 값이 있는 셀을 참(1)/거짓(0)으로 바꾸고 COLUMN과 곱해 값이 있는 열 번호만 남긴 뒤, MAX로 가장 큰 열 번호를 찾아 OFFSET으로 위치를 계산합니다.
마치며
VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
