들어가기 전에
Excel에서 상위 10개 목록을 만들고자 할 때, 데이터를 직접 정렬하고 복/붙하는 것은 꽤 지루할 수 있습니다. 뿐만 아니라 기본 데이터가 변경될 때마다 전체 과정을 반복해야 하므로 여러 가지 문제가 생길 수도 있습니다. Excel의 TAKE와 DROP 함수를 사용하여 수동 업데이트의 번거로움을 해결하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: I use Excel's TAKE and DROP to make top-10 lists that update themselve
- URL: https://www.makeuseof.com/use-excels-take-drop-make-top-lists-update-themselves/
동적 배열 이해하기
동적 배열 이전에는 수식 결과가 단일 셀로 제한되었습니다. 열을 채우려면 채우기 핸들을 아래로 끌어야 했습니다. 동적 배열은 다릅니다. 단일 수식으로 여러 셀을 자동으로 채우는 전체 결과 범위를 반환할 수 있습니다. 이러한 자동 프로세스를 "스필링(spilling)"이라고 합니다.
수식에서 여러 결과가 생성되면 인접한 빈 셀로 결과가 스필링됩니다. Excel에서 전체 출력 범위에 얇은 파란색 테두리가 표시되므로 동적 배열임을 알 수 있습니다. 이 시각적 신호는 데이터가 활성 상태이며 원본 수식과 연결되어 있음을 나타냅니다.
파란색 테두리로 표시된 범위의 첫 번째 셀에만 수식을 입력하면 됩니다. 스필 영역 내의 다른 셀에 무언가를 입력하려고 하면 #SPILL! 오류가 발생합니다 . 전체 배열은 이 초기 수식 하나로 제어되므로 시트가 더 깔끔하고 오류 발생 가능성이 줄어듭니다.
이러한 원리는 TAKE 및 DROP 함수에도 적용됩니다. 원본 데이터가 변경되면 결과가 자동으로 조정되므로, 동적 배열을 사용하면 테이블 크기를 조정하고 결과를 수동으로 업데이트할 필요가 없습니다.
[참고] TAKE와 DROP은 Microsoft 365 및 Excel 2021 이상 버전에서 사용할 수 있습니다.
TAKE 함수를 사용하여 상위 항목을 가져오는 방법
TAKE 함수는 간단한 작업을 위해 설계되었습니다. 데이터세트의 시작이나 끝에서 특정 개수의 행을 추출하는 함수입니다. Excel에 원하는 행 개수만 지정하면 직접 복사하여 붙여넣을 필요 없이 자동으로 추출해 줍니다. 큰 표에서 요약이나 순위표를 만드는 데 효율적입니다.
TAKE 함수의 기본 사용 구분은 다음과 같습니다.
=TAKE(array, rows, [columns])
- array: 가져오려는 데이터의 소스 범위 또는 배열
- rows: 추출할 행 수(양수는 맨 위에서부터, 음수는 맨 아래에서부터 추출)
- [columns]: 선택 인수로, 왼쪽(양수) 또는 오른쪽(음수)에서 반환할 열 개수를 지정할 수 있음(생략하면 모든 열 반환)
실제 사례에 적용해 보겠습니다. 판매 데이터를 사용하여 상위 5개 판매 금액을 보여주는 동적 리더보드를 만들고자 합니다.
최고 실적을 내는 핵심은 먼저 데이터를 매출이 가장 높은 것부터 낮은 것 순으로 정렬하는 것입니다. 이를 위해 모든 사무직 직원이 알아야 할 Excel 수식 중 하나인 SORT 함수를 사용합니다.
다음 수식은 7번째 열(판매 금액)을 기준으로 내림차순(-1)으로 전체 표를 정렬합니다.
=SORT(A2:K33, 7, -1)
데이터가 정렬되었으므로 이제 TAKE를 사용하여 상위 5개 항목만 가져올 수 있습니다. 다음과 같이 SORT 함수를 TAKE 함수 안에 래핑하면 됩니다.
=TAKE(SORT(A2:K33, 7, -1), 5)
이 단일 수식은 먼저 전체 목록을 정렬한 다음 상위 5개 행을 추출하여 자동으로 업데이트되는 순위표를 만듭니다. 원본 표에서 판매 금액을 변경하면 다른 작업을 하지 않아도 이 목록에 새 순위가 즉시 반영됩니다.
필요하지 않은 데이터를 무시하는 데에도 유용한 DROP 함수
DROP 함수는 TAKE의 반대 개념으로, 특정 행을 가져오는 대신 데이터세트에서 제거합니다. 머리글, 합계 또는 건너뛰어야 할 관련 없는 항목이 포함된 복잡한 데이터를 다룰 때 유용합니다. 원하지 않는 행을 수동으로 선택하는 대신, Excel에서 맨 위 또는 맨 아래에서 몇 개의 행을 무시할지 지정할 수 있습니다.
구문은 TAKE와 유사하지만 논리는 반대입니다.
=DROP(array, rows, [columns])
- array: 데이터를 제거하여 수정하려는 소스 범위 또는 배열
- rows: 제거할 행의 개수(양수는 위에서부터, 음수는 아래에서부터 삭제)
- [columns]: 왼쪽(양수) 또는 오른쪽(음수)에서 열을 삭제하는 선택적 매개변수(생략하면 모든 열이 유지)
판매 데이터를 다시 사용하여, 실적이 가장 저조한 세 회사를 제외한 모든 회사를 분석하고 싶다고 가정해 보겠습니다. 따라서 DROP 명령을 사용하여 실적이 가장 저조한 회사를 제거할 수 있습니다.
먼저, 가장 실적이 저조한 기업을 파악하기 위해 데이터를 정렬하겠습니다.
=SORT(A2:K33, 7, 1)
-1 대신 1을 사용하여 판매 금액이 낮은 것부터 높은 것 순으로 정렬합니다. 이제 아래 세 행을 삭제하겠습니다.
=DROP(SORT(A2:K33, 7, 1), -3)
음수를 입력하면 Excel에서 정렬된 목록의 끝에서 3개 행을 제거합니다.
이 수식은 가장 성과가 낮은 직원을 제외한 모든 직원에 초점을 맞춘 깔끔한 데이터 세트를 제공하며, 이는 이상치로 인해 분석이 왜곡되지 않고 핵심 영업팀을 분석하는 데 유용합니다.
함수를 조합하여 진보된 보고서 만들기
TAKE와 DROP을 중첩하면 순위 목록의 중간에서 특정 데이터만 분리할 수 있습니다. 이는 중간 수준의 실적을 보이는 제품이나 상위권도 하위권도 아닌 제품을 파악하는 등 단계별 보고서를 만드는 데 유용합니다.
동일한 판매 데이터를 다시 사용해 보겠습니다. 이미 상위 5개와 나머지 목록은 찾았습니다. 하지만 6위부터 10위까지의 직원만 보고 싶다면 어떻게 해야 할까요? 이를 위해 안에서부터 바깥쪽으로 특정 순서대로 실행되는 수식을 사용합니다.
=TAKE(DROP(SORT(A2:K33, 7, -1), 5), 5)
Excel에서 해당 요청을 처리하는 방식은 다음과 같습니다. 먼저, SORT 함수는 전체 표를 매출 금액 기준으로 내림차순으로 정렬합니다. 그런 다음, DROP 함수는 정렬된 목록에서 상위 5개 행을 제거합니다. 그 후, TAKE 함수는 나머지 데이터에서 처음 5개 행을 가져와서 6위에서 10위까지의 실적을 정확히 파악합니다.
이 방법을 사용하면 최악의 실적을 포함하지 않고도 실적이 저조한 매출을 찾을 수 있습니다. 예를 들어, 두 번째로 실적이 저조한 매출을 찾으려면, 최하위 실적을 기록한 매출을 하나 빼고(-1), 나머지 목록에서 다음 네 개를 선택하면 됩니다. 이 공식은 약간 변형된 것입니다.
=DROP(TAKE(SORT(A2:K33, 7, 1), 5), 1)
여기서는 데이터가 오름차순으로 정렬되고(1), 처음 5개 행이 취해지고(5), 그 다음 가장 위의 행이 삭제됩니다(1).
더욱 심도 있는 보고서를 위해 이러한 기능을 FILTER와 결합하여 특정 지역의 순위표를 만듭니다. 북부 지역에서 상위 3개 업체를 찾으려면 먼저 데이터를 필터링한 후 정렬하고, 그 중 상위 3개 업체를 선택합니다.
=TAKE(SORT(FILTER(A2:K33, B2:B33="North"), 7, -1), 3)
이 수식은 완전히 동적인 지역별 순위표를 생성합니다. 판매 데이터가 변경되거나 영업 담당자가 다른 지역으로 이동하면 목록이 자동으로 업데이트됩니다.
마치며
Excel에서 데이터 순위를 매기는 방법은 많지만, 대부분은 복잡한 분석을 위해 설계되었거나 다소 번거롭게 느껴지는 수동 단계를 거쳐야 합니다. 깔끔하고 자동으로 업데이트되는 리더보드만 필요하다면 피벗 테이블이나 읽기 어려운 기존 수식을 다룰 필요가 없습니다. TAKE와 DROP을 함께 사용하면 결과에 훨씬 더 직접적으로 접근할 수 있습니다. 이 방법은 새로 고침할 필요 없이 간편하게 사용할 수 있는 솔루션이며, 누구나 쉽게 이해할 수 있는 수식을 사용합니다.
'Excel' 카테고리의 다른 글
| Excel 시트가 느려지는 3가지 수식 해결 방법 (1) | 2026.02.12 |
|---|---|
| 여러 개의 제목 행이 있는 Excel 데이터 쉽게 수정하는 방법 (0) | 2026.02.11 |
| Excel에서 데이터를 재구성하는 7가지 방법 (0) | 2026.02.09 |
| 새로운 콘텐츠 오픈 안내 (0) | 2026.02.08 |
| 정말 대단한 Excel MAP 함수 (0) | 2026.02.04 |
