엑셀(EXCEL) 데이터의 시작과 끝의 위치를 찾아 값을 확인해 주는 배열함수(중간에 빈칸, 내용 무시하고 시작과 끝만 찾기) - 파일포함

오늘의 예시는 가상으로 맴버가 월별로 일한 날을 넣어둔 시트입니다.

어느달이 처음이고 끝인지를 알고 싶다고 한다고 칩시다.

오늘은 저 데이터의 시작과 끝을 알아보는 배열 함수를 알아보겠습니다.

중간의 빈칸은 무시하고 시작과 끝만 출력하겠습니다.(아래 파일도 업로드 해 두었습니다.)

1. 시작을 알아내는 함수

조금길어서 초보분들은 거부감이 있을 수 있지만 공부를 해 봅시다.

긴 함수를 볼 때는 안쪽에서부터 보는게 좋습니다.

※ 이 함수들은 둘 다 배열 함수임으로 Ctrl + Shift + Enter로 입력해야 합니다.

① NOT(ISBLANK(B2:M2))

ISBLANK는 빈칸일 경우 True, 빈칸이 아니면 False를 출력합니다.

여기에 NOT이 추가 되니까 꺼꾸로 빈칸이면 False, 빈칸이 아니면 True입니다.

즉 빈칸이 아닌 칸을 찾아 줍니다.

② MATCH(TRUE, NOT(ISBLANK(B2:M2)), 0)

Match는 똑같은 값을 찾아 냅니다.

그리고 여러개 똑같은 값이 있으면, 맨 처음 값을 출력합니다.

따라서 처음에 True 즉, 비지 않은 칸의 위치를 찾습니다.

③ INDEX($B$1:$M$1, MATCH(TRUE, NOT(ISBLANK(B2:M2)), 0))

index에 걸린 범위가 맨위의 1월~12월이 있는 행입니다.

맨 처음 빈칸이 아닌 월을 출력하게 됩니다.

이 INDEX의 범위를 변경하면 출력하는 값을 바꿀 수 있습니다.

2. 끝을 알아보는 함수

이 함수가 조금 더 어렵습니다.

하지만 한번 배열함수 원리를 이해하신 분이라면 금방 알수도 있습니다.

① (COLUMN(B2:M2)-1

Column이 배열함수로 들어가면, B2~M2 범위까지의 열 번호를 배열로 만듭니다.

첫 열이 이름이 들어갔음으로 2번째 부터 시작하기 위해 -1을 해 줍니다.

이 함수의 결과는 {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}의 행렬을 출력합니다.

그리고 데이터가 세로로 되어있으면 row 함수로 변경해야 합니다.

② (B2:M2<>"")

범위내에서 비어 있지 않는 셀(<>"")는 True(혹은 1), 비어있는 셀은 False(혹은 0)이 됩니다.

위의 예에서는 아래 그림의 첫번째 줄은 {0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0}가 됩니다.

비어있으면 0, 내용이 있으면 1이 되는 것이죠

③ MAX((B2:M2<>"")*(COLUMN(B2:M2)-1))

앞에 두 값을 곱합니다.

 {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} x {0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0} = {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0}

여기에 MAX 함수를 가하면 마지막 달의 번호인 10이 됩니다.

여기까지 이해하시면 다 이해하신 것입니다.

④ INDEX($B$1:$M$1, MAX((B2:M2<>"")*(COLUMN(B2:M2)-1)))

INDEX 함수로 범위내에서 10번째 값을 출력하게 됩니다.

이 INDEX의 범위를 변경하면 출력하는 값을 바꿀 수 있습니다.

시작과 끝을 출력한다는 목표만 보면 이것 말고도 다른 방법이 많습니다.

다른 배열함수 구조도 많지만 한 가지를 소개해 보자는 취지에서 하나 작성해 봤습니다.