'일상 이것저것/엑셀'에 해당되는 글 7건

[엑셀] 회비정리 파일

많은 분들이 다양한 그룹에서 친구분들과 계모임 많이 하고 있으실 겁니다. 계모임을 하면 돈 관리하는 분들이 계시죠. 제가 바로 그런 사람 중에 한 명입니다. 그래서 제가 사용하고 있는 회비정리 엑셀 파일을 공유해 드리고자 합니다. 각자 사용하고 있는 양식이 있으시겠지만 저만의 방식을 소개해드리고자 합니다. 

간단한 함수로만 만들었기 때문에 사용하시는 데는 어려움이 없을 것이라 생각합니다. 이 방법이 더 나으시다고 생각하시면 이용해 보시는 것도 좋을 것 같습니다.

우선 저는 2개의 시트를 이용해서 관리하고 있습니다. 1개는 간단히 보기 위함, 1개는 세부적으로 정리한 내용입니다. 

<첫 번째 시트>

<두 번째 시트>

이렇게 관리하고 있습니다.

우선 두 번째 시트를 설명드리도록 하겠습니다.

1) 직접 입력해야 하는 항목

- 회비 입금 날짜

- 지출 날짜, 금액, 내용

- 이자 날짜, 금액

2) 잔액 확인 공식 입력

- 회비(회비 입금현황): 회비금액 30,000원씩 입금을 하며 입금한 날짜의 횟수를 세서 곱해주는 함수 사용 (COUNTA: 비어있지 않은 셀의 개수)

   =30000*COUNTA(세부!B8:M11,세부!B14:M17)

- 이자: 이자금액의 합

   =SUM(U:U)

- 지출: 지출금액의 합

   =SUM(P:P)

- 잔액계: 총수입금액에서 지출금액을 뺌 (회비+이자-지출)

   =A3+B3-C3

3) 지출 및 이자 연도 확인

- Total 회비를 쉽게 확인하기 위함

- 지출 및 이자 연도 정리: 일자에서 연도만을 읽어냄(왼쪽에서 4자리 숫자)

   =LEFT(O7,4)

그다음 첫 번째 시트입니다.

1) 잔액

- 두 번째 시트에서 잔액 부분을 그대로 읽어 옵니다. 

   =세부!A3 

2) 미납액

- 가장 중요한 미납액 부분입니다.

- 12달 중에서 낸 개월 수를 뺀 후 (미납한 개월수만 남음), 회비금액(30,000원) 만큼을 곱해줍니다. 

   =(12-COUNTA(세부!B8:M8))*30000

- 해당 연도에 미납한 금액을 알 수 있습니다. 저는 주로 이렇게 사용하고 있습니다.

- 하지만, 현재 날짜 기준으로 미납액을 알고 싶으시면 다른 함수를 쓰면 됩니다. YEAR 함수를 이용하여 올해가 아니면 12를 입력하고, 올해가 맞으면 MONTH 함수를 이용하여 이번달까지의 미납액을 알 수 있도록 합니다.

   =(MONTH(TODAY())-COUNTA(세부!B14:M14))*30000

3) 지출

- 세부 시트에서 정리한 년도를 바탕으로 연도에 따른 지출금액의 합을 불러옵니다. 즉, 2022년도로 정리되어 있는 값을 더해줍니다.(SUMIF 함수 사용)

   =SUMIF(세부!$R:$R,total!H7,세부!$P:$P) --> 세부!R열은 연도를 정리해 놓은 열이고, P열은 지출 금액을 쓴 행입니다.

- 각 연도별 지출금액을 합계에 더해줍니다.

4) 이자

- 지출 부분과 똑같이 세부 시트에서 정리한 년도를 바탕으로 불러옵니다. 

   =SUMIF(세부!$V:$V,total!H10,세부!$U:$U) --> 세부!V열은 연도를 정리해 놓은 열이고, U열은 이자 금액을 쓴 행입니다.

- 각 연도별 이자금액을 합계에 더해줍니다.

저는 위와 같이 관리하고 있습니다. 간단한 함수로만 관리를 해서 파일을 제작하기는 어렵지가 않았습니다. 혹시나 계좌로만 관리하고 계시다면 엑셀로 간단히 정리할 수 있으니 사용해 보시면 좋을 것 같습니다. 

모든 계모임 총무들을 응원합니다!!

[Excel] D-day를 구해보자!!

D-day를 엑셀로 구하는 방법을 소개하려고 합니다. D-day 어플도 많이 나와있지만, 엑셀로 직접 계산해 보는 것도 재미있지 않을까 싶습니다.

1. D-day 날짜 확인

아래 그림은 제가 임의적으로 D-day 날짜를 지정해 보았습니다.

2. 일반적인 D-day  

우선 D-day 표시를 하기 위해 정해 놓은 D-day에서 오늘 날짜를 빼보도록 하겠습니다.

뺀 다음 D-day 가 지났다면 "만료"라고 표시하고, 안 지났다면 "D-" 글씨를 추가하여 D-xxx 이런 식으로 나타내고자 합니다.

오늘 날짜를 위해 TODAY() 함수를 사용했습니다 (2021.04.13 기준). 그리고 IF 함수를 이용하여 지났는지 아닌지를 판단하여 나타냈습니다.

=IF(A3-TODAY()<0,"만료","D-"&A3-TODAY())

위의 그림처럼 결과가 나왔습니다. D-day 구하기는 위의 함수처럼 작성하면 매우 간단합니다.

3. 영업일수 기준 D-day

그렇다면 D-day까지 며칠 일을 해야 하는 계산 해 보겠습니다. 며칠 일을 해야 하는지 영업일수를 계산하기 위해서 WORKDAYS 함수를 사용해 봤습니다. WORKDAYS 함수 중에서도 두 날짜 사이의 영업일수를 구하는 NETWORKDAYS 함수를 사용했습니다. NETWORKDAYS 함수는 두 날짜 사이의 영업일수를 구하고, 그중에서 휴일 일수를 빼는 함수입니다. 아래와 같이 사용합니다.

NETWORKDYAS(날짜1,날짜2,휴일)

우선 공휴일을 확인해서 휴일 날짜를 작성했습니다. 그리고 NETWORKDAYS 함수를 이용하여 오늘과 D-day 사이의 날짜를 구하고, 그중에서 휴일 일수를 빼도록 함수를 작성했습니다. 

=IF(A2-TODAY()<0,"만료","D-"&NETWORKDAYS(TODAY(),A2,D$2:D$11))

위의 그림처럼 결과가 나왔습니다. 결과를 보니 오늘 (2021.04.13)부터 올해 일할 날짜는 184일 남았습니다. 한 해가 지나고 나이 먹는 건 싫지만, 일해야 하는 날짜를 보면 기분이 그저 그냥 그렇습니다.

두 방법에 결과물을 비교해 보겠습니다.

단순히 날짜로 D-day 계산한 방법 (일반적인 D-day)이 왼쪽! 영업일수 기준으로 D-day 계산한 방법이 오른쪽!입니다.

기념일 등은 왼쪽 방법으로 관리하고, 회사 업무 등은 오른쪽 방법으로 관리하면 편할 것 같습니다.

[Excel] 순위 확인 및 공동 순위 찾아내기

엑셀로 공동 순위를 찾아내는 방법을 설명드리려고 합니다. 말씀드리면 이 방법은 많은 데이터에 사용해 보거나, 심심할 때 해보는 방법일 뿐이지, 수작업으로 하는 것이 훨씬 빠를 경우가 있습니다. 수작업이 빠르시면 수작업으로 하시는 것을 추천드립니다.

공동 순위를 찾아내는 방법입니다.

예를 들어 위와 같은 데이터가 있을 경우 1등부터 순위를 나열하고 공동 순위로는 누가누가 있는지 알고 싶을 것입니다. 그럼 가장 먼저 순위를 매겨 보도록 하겠습니다.

1. 순위 매기기

RANK라는 함수를 사용하면 순위를 매길 수 있습니다. RANK(값, 범위) 이런 식으로 작성하면 선택한 값이 범위 중에 몇 번째 인지 알 수 있습니다.

이렇게 하면 각 ID마다의 순위를 알 수 있습니다. 순위를 오름차순으로 정렬하게 되면 편하게 볼 수 있을 것입니다. 데이터가 적으면 아래와 같이 보면 더 편하게 정리가 가능합니다. 

하지만 데이터가 많거나 하나의 표로 정리하고 싶은 충동이 들 수도 있습니다. 그러기 위해 표로 정리해 보도록 하겠습니다.

2. 표로 정리하기

표로 정리하기 위하여 순위 중에서 원하는 순위만 골라내 보도록 하겠습니다. IF 함수를 이용하여 원하는 순위만 남기고 그 순위가 해당하는 행의 값이 남도록 하겠습니다.

IF($C$3:$C$16=$E4,ROW($C$3:$C$16),"")

이 함수를 이용하니 원하는 순위의 행의 값이 남게 되고, INDEX 함수를 사용하면 각 순위에 해당하는 ID가 불러와지게 됩니다. 하지만 공동 순위가 문제가 됩니다. 공동 순위에 해당하는 ID가 전부 불러오는데 문제가 생기게 됩니다. 이를 해결하기 위해 SMALL 함수를 이용하여 표에 나타날 수 있도록 하겠습니다.

SMALL(IF($C$3:$C$16=$E4,ROW($C$3:$C$16),""),COLUMN()-5)

위의 함수를 이용하니 표에 원하는 셀 (F열)에 IF 함수로 원하는 순위만 남은 값 중에 가장 위에 값 (첫 번째 순서)을 불러올 수 있게 됐습니다. 그리고 G열에는 원하는 순위 중 2번째 순서로 나온 값을 불러올 수 있게 됐습니다. 이제 여기에 INDEX 함수를 붙여서 표를 드래그해보도록 하겠습니다.

IFERROR(INDEX($A$3:$A$16,SMALL(IF($C$3:$C$16=$E4,ROW($C$3:$C$16),""),COLUMN()-5)-2),"-")

위의 그림과 같이 표에 정리가 되게 됩니다. 각 순위에 해당하는 ID가 불러와지고, 공동 순위의 경우 열 순서대로 나오게 됩니다. 공동 순위가 많아질수록 표가 가로로 길어질 것입니다. 

위의 예시는 간단히 보여드리려고 데이터를 적게 하였지만, 데이터가 많아지게 되면 유용하게 쓰일 수 있을 것 같습니다. 아니면 심심하신 분들도 한번 해보시길 추천드립니다. 엑셀 공부하는 셈 치고 하는 것도 재미있습니다.

*참고: 위의 함수는 표 안의 'F4'의 기준으로 작성된 것입니다.

[엑셀] 두그룹 리스트 합치기 (공통항목 제외하기)

엑셀을 하시다가 두 그룹의 리스트를 합쳐야 하는 경우가 있습니다. 쉽게 하는 방법은 그냥 복사+붙여 넣기 하면 쉽게 합쳐집니다. 하지만, 여기서 문제는 A 그룹과 B 그룹에서 공통으로 들어간 항목이 있어 겹친다는 것입니다.

예를 들면 아래와 같습니다.

복사+ 붙여 넣기 하면 색칠한 것과 같이 저렇게 몇 개의 항목이 겹쳐지게 되는 문제점이 있습니다. 겹치는 항목을 제외하고 목록을 합치는 법을 설명하고자 합니다.

<전체적인 방법 설명>

방법은 위의 예시 표를 기준으로 설명하겠습니다. 우선 이과를 기준으로 문과에서 이과에 해당되는 과목이 있으면 이를 제외한 리스트를 만들어서 합칠 예정입니다.

1. 이과와 문과 공통인 과목 찾아내기!

이는 'VLOOKUP' 함수를 이용하여 실시했습니다. 

=IFERROR(IF(VLOOKUP(B2,$A$2:$A$9,1,FALSE)=B2,"-"),B2)

위와 같이 함수를 이용하여 같은 과목이면 "-"표시를 하게 했고, 다른 과목이면 그냥 그대로 표시되게 했습니다.

위의 표는 그 결과입니다. '문과' 과목 중에서 '이과'와 공통인 과목은 지워지고 '문과'에만 있는 과목들이 남았습니다. (문과-1 열)

2. 문과의 남은 과목 리스트 정리

문과의 남은 리스트를 지난번에 포스팅했던 것과 같이 위쪽으로 몰아서 정리를 해 주었습니다.

지난번 리스트 빈칸 없애고 정리하는 법을 포스팅했던 내용 링크입니다. 

2020/06/30 - [일상 이것저것] - [Excel] 리스트 중 비셀 없애고 정리하는 법

위의 링크대로 정리해주면 아래와 같이 됩니다. (문과-2 열)

=IFERROR(IF(VLOOKUP(B2,$A$2:$A$9,1,FALSE)=B2,"-"),B2)

3. 이과와 문과 합치기

이제 '이과'와 '문과-2'의 두열을 함수 "OFFSET"을 이용하여 합칠 예정입니다. '이과'를 전부 불러오고, '이과'에서 끝나게 되면 '문과-2'로 넘어가서 불러오라는 함수를 사용할 예정입니다. '이과'에서 다 불러오고 그다음 빈칸을 넘어오게 되면 "OFFSET" 함수가 '숫자 0'으로 불러오게 됩니다.

이를 이용하여 '숫자 0'이 나오면 '문과-2'로 넘어가라고 "IF" 함수를 이용하였습니다.

=IF(OFFSET($A$1,ROW(A1),0)=0,OFFSET($D$1,ROW(D1)-COUNTA(A:A)+1,0),OFFSET($A$1,ROW(A1),0))

그 결과 나온 표입니다. 기존에 복사+붙여 넣기 한 항목과 비교해 보면 공통된 과목이 제외된 것을 보실 수 있습니다.

두열을 합치는데 공통된 내용을 제외하고 합치는 것은 이제 하실 수 있을 거라 생각합니다. 손으로 하나하나씩 찾아가며 하기에는 시간도 걸리고 실수도 있을 수 있으니 이렇게 사용하면 편할 것 같습니다. 또한, 지금 예시는 짧은 목록이지만 목록이 몇천 개 이상이라면 당연히 위와 같은 방법이 유용하게 쓰일 것 같습니다.

[Excel] 그룹별 평균, 표준편차, 최대값, 최소값 한번에 구하기

이번에는 평균, 표준편차, 최댓값, 최솟값을 한 번에 구하는 법을 소개하려고 합니다. 이것은 일반적으로 "AVERAGE", "STDEV.P", "MAX", "MIN"을 쓰면 된다고 생각하실 수 있는데, 해당 함수를 막상 쓰기에 어려울 때가 있습니다. 그리고 한 번에 구하기가 어려울 때가 있습니다. 그룹별로 포함된 수가 다르고 많은 경우 평균, 표준편차, 최댓값, 최솟값 등을 한 번에 구한다는 것은 쉽지 않습니다. 예를 들어 아래와 같은 상황입니다.

어떻게 해야 할지 고민하다 보면 평균을 구할 때 "AVERAGEIF" 함수가 생각나게 됩니다. 이 함수를 이용해서 그룹별 평균을 구하면 되겠다는 생각을 하게 됩니다. 그럼 표준편차, 최댓값, 최솟값, 중앙값 등은 어떻게 해야 할지 몰라 막히기 시작합니다. 그리고 고민하다가 그냥 그룹별로 하나씩 드래그해가며 구하시는 분들이 많을 것 같습니다. 이렇게 구하면 계산은 되지만 시간이 무척 오래 걸립니다. 거기에다 만약에 그룹이나 값의 개수가 10,000개를 넘어가게 되면 할 수가 없습니다.

위와 같은 상황에서 편하게 계산하기 위해 한번에 함수를 쓰고 드래그해서 복사하면 편하게 계산될 수 있는 법을 소개하려고 합니다.

바로 "OFFSET" 함수를 이용한 방법입니다.

그럼 우선 OFFSET 함수를 알아보겠습니다. OFFSET 함수는 기준 셀에서 지정한 행, 열 수만큼 이동한 후 해당 셀의 값을 읽을 때 사용합니다.

 1) OFFSET(기준 셀, 이동할 행수, 이동할 열수): 기준 셀에서 지정한 행, 열 수만큼 이동한 셀을 읽어 옵니다.

 2) OFFSET(기준 셀, 이동할 행수, 이동할 열수, 행수, 열수): 기준 셀에서 지정한 행, 열수만큼 이동한 셀을 기준으로 지정한 행수, 열수에 해당하는 범위를 읽어 옵니다.

예를 들어, "OFFSET(A1,2,5)"는 "A1" 셀에서 행으로 2칸, 열로 5칸 이동한 "F3" 셀을 읽어옵니다.

​"OFFSET(A1,2,5,3,4)"는 위와 같이 이동한 "F3" 셀에서부터 3행 4열 범위를 지정하므로 [F3:I5]가 됩니다.

이를 기반으로 우리가 원하는 범위에 AVERAGE, STDEV.P, MAX, MIN, MEDIAN 등의 함수를 붙여서 사용이 가능합니다.

(예) "AVERAGE(OFFSET(A1,2,5,3,4))", "MAX(OFFSET(A1,2,5,3,4))" 등)

이를 이용하여 위의 문제를 해결해 보려고 합니다.

고민 1. 어떻게 그룹별로 범위를 읽어 올 수 있을까?

OFFSET 함수를 이용하면 그룹별로 원하는 범위를 읽을 수 있을 것이라 생각했습니다. A그룹의 수학 점수는 D2:D4, B 그룹의 수학 점수는 D5:D7, C그룹의 수학 점수는 D8:D11 이므로, 각 그룹의 시작점과 각 그룹의 속한 개수만 알면 범위를 읽어 올 수 있겠다고 생각했습니다.

그럼 제가 할 일은 각 그룹의 각 과목이 시작하는 셀을 읽어오는 방법과, 각 그룹의 개수만 알면 될 것 같습니다.

일단 각 그룹의 개수는 "COUNTIF" 함수를 사용했습니다. 그리고 각 그룹의 각 과목이 시작하는 셀은 아래 표와 같습니다.

그래서 열은 1칸씩, 행은 각 그룹의 개수씩 추가되게끔 해서 OFFSET 함수를 이용하면 되겠다는 결과를 내렸습니다. 그냥 간단한 수열이라고 생각하면 더 편할 것 같습니다.

고민 2. 행, 열 방향으로 드래그를 했을 때 다른 그룹 또는 다른 과목에서도 동일하게 원하는 범위를 읽어 오는가?

저는 매번 함수를 사용하는 것보다 하나를 써서 드래그를 해서 복사해서 사용하고, 원래 data가 바뀌더라도 사용해 놓은 함수를 변형하지 않아도 결과가 나오는 것을 좋아합니다. Data가 바뀔 때마다, 각 조건을 따져가며 함수를 계속해서 바꿔야 한다면 너무 불편하다고 생각해서 그렇습니다.

우선 어떻게 결과를 정리할까 결과물을 생각해봤습니다.

제가 생각하는 데이터와 결과표입니다. 우선은 데이터 수가 적게 해서 만들어 보고자 합니다. 나중에 1,000개, 10,000개 이상의 데이터에서도 사용 가능합니다.

일단 그룹 내 속한 개수를 가져오고자 합니다. 우선 N에 해당하는 K2셀에는 "COUNTIF($C:$C,$J2)" 를 적용하였습니다. 그리고 같은 열에 복사하면 문제없이 나올 것입니다.

결과를 보니 잘 나왔습니다. 그다음 A1셀을 기준으로 각 그룹의 각 과목이 시작하는 셀로 이동시켜보려고 합니다.

행: A그룹을 위해서는 1, B 그룹을 위해서는 1+ A그룹 수, C그룹을 위해서는 1+ A그룹 수 + B그룹 수

열: A1에서 3칸 이동 이후에 한 칸씩 이동

이러한 고민 끝에 나온 것이 L2셀에 "OFFSET($A$1,ROW(E$1)+SUM($K$1:$K1),COLUMN(E$1)-1)" 입니다. 이것을 L2:O4까지 드래그해서 복사해보겠습니다.

테스트 해 본 결과 제가 원하는 셀을 읽어온 것을 볼 수 있습니다. 생각하는 대로 값이 나오는 것을 보니 매우 만족스럽습니다. 그럼 계산할 범위를 구해보면 행수만 적고, 여기에 해당하는 열은 1개 이기 때문에 1로 적겠습니다. 행수는 N 수를 쓰면 될 것 같습니다.

L2셀에 "(OFFSET($A$1,ROW(D$1)+SUM($K$1:$K1),COLUMN(D$1)-1,$K2,1))" 을 입력했습니다.

그럼 원하는 범위가 됩니다. 그럼 여기에 원하는 AVERAGE, STDEV.P, MAX, MIN을 더해보겠습니다. 함수를 넣고 L2:O4까지 드래그해서 복사해보겠습니다.

원하는 값이 계산된 것을 볼 수 있습니다. 이제 1,000개 이상의 행으로 늘어나도 쓰는데 문제없고 열이 늘어나도 똑같이 결과표에 열만 늘려가면 됩니다. 위와 같은 데이터 형식이라면 위의 식을 이용해서 편하게 원하는 값을 편하게 구할 수 있을 것 같습니다.

보시는 분들께 도움이 되었으면 좋겠습니다.

[엑셀] 겹치지 않게 난수 생성하기

엑셀 난수 생성한다고 하면 가장 먼저 떠오르는 함수는 바로 "RANDBETWEEN" 일 것입니다. 하지만 "RANDBETWEEN"의 단점은 난수를 생성하지만, 반복한 값이 나올 수 있다는 것이 문제입니다.

바로 위와 같은 상황입니다. 각 칸에는 RANDBETWEEN(1,100)을 입력했습니다. 난수 값은 1~100 중에 겹치지 않게 20개를 선택하고 싶을 때 위와 같은 일 때문에 문제가 생깁니다. 그래서 이런 상황을 없애보려고 합니다.

1. 우선은 겹치는 것이 무엇인지 확인해 봤습니다.

함수는 =IF(COUNTIF($B$4:B14, B14)=1,B14,"-") 이렇게 사용했습니다. 즉, 난수 생성해서 첫 번째 행부터 해서 선택된 행까지 반복된 숫자가 1개 이상이면 "-" 표시가 나타나게 했습니다.

2. 이후에는 지난번 설명했던 리스트 정리 함수를 이용해서 정리를 합니다.

2020/06/30 - [Excel] - [Excel] 리스트 중 비셀 없애고 정리하는 법

그러면 리스트가 정리되었습니다. 겹치지 않게 원하는 범위에서 원하는 개수만큼 난수를 생성했습니다.

이상 겹치지 않게 난수 생성하는 방법이었습니다.

[Excel] 리스트 중 빈셀 없애고 정리하는 법

다양한 데이터를 다루다 보면 엑셀을 필수적입니다. 그래서 엑셀을 잘 사용하기 위해 계속 공부하고 함수들을 합쳐보기도 하고, 새로운 함수도 배우기도 합니다. 그중 유용하게 쓰고 있는 엑셀 방법을 소개하고자 합니다. 리스트 중의 빈셀을 없애고 정리하는 법입니다.

<리스트 중 빈셀 없이 정리하는 법>

예를 들어 아래와 같은 상황입니다.

저기서 빈셀을 없애고 정리를 하고 싶습니다. 이게 몇 줄 안된다면 지워가며 정리하면 되지만 1000행이 넘어간다면 어떨까요? 그러면 함수를 이용해서 정리를 하게 되면 손쉽게 할 수 있을 것입니다. 하지만 기본적으로 제공되는 함수 중에서는 이를 해결할 만한 것은 없어 보입니다. 그래서 사용할 수 있는 함수를 만들어 보기로 했습니다.

1. 리스트에 값이 있느냐 비어 있느냐 검사하기

같지 않다 (<>)를 이용하여 함수를 우선 만들었습니다. 그랬더니 TRUE or FALSE로 나타납니다. 여기서 우리가 정리할 건 TRUE로 나타나는 것들입니다.

2. 행 수를 구하여 값을 부여합니다.

ROW 함수를 이용하여 행수를 구하고 이를 나눠줌으로써 위에서부터 큰 값을 갖게 임의의 값을 부여합니다. (FALSE는 0으로 나타나도록)

3. 임의의 값을 정리

LARGE 함수를 이용하여 0으로 나온 값을 제외하고, 큰 수부터 차례대로 정리합니다. 

4. 리스트에 맞는 순번을 찾습니다.

2번 단계에서 부여한 임의의 값을 이용하여 각 값들이 리스트에서 몇 번째 인지 계산합니다. 즉, 2번 단계에서 부여한 방법을 역으로 그대로 취한 다음 1을 빼주면 되겠죠?? (1을 빼는 이유는 2행부터 리스트가 시작하기 때문입니다.)

5. 리스트에서 각 번호에 맞는 값을 불러옵니다.

INDEX 함수를 이용하여 리스트에서 몇 번째에 해당하는 값을 불러옵니다.

6. FALSE로 나왔던 부분을 제거해 줍니다.

#DIV/0! 이게 보기 싫으니 제거해 주기로 합시다. "IFERROR" 함수를 이용하여 빈칸이 되도록 해줍니다. 최종적으로 원하는 대로 빈 셀들은 제거되고, 값이 있는 리스트로만 정리가 되었죠?

최종적인 함수의 형태입니다.

{=IFERROR(INDEX($B$2:$B$21,1/LARGE(($B$2:$B$21<>"-")/ROW($B$2:$B$21),ROW(A1))-1),"")}

필요하신 분들은 이 함수를 변형하셔서 사용하시면 될 것 같습니다. 이 함수는 제가 어렵게 정리한 걸 수도 있습니다. 정답이 아닐 수도 있지만 잘 정리가 되는 결과를 보여줍니다. 이를 활용하여 각자 자신에게 맞는 함수로서 사용하시기 바랍니다.