컴퓨터/엑셀

DeepSeek filter 이용하지 않고 small함수 배열식

풍경소리^^ 2025. 1. 26. 19:08
excel
Copy
=IFERROR(INDEX($B$2:$B$19, SMALL(IF($A$2:$A$19="총무부", ROW($A$2:$A$19)-ROW($A$2)+1), ROW(A1))), "")

이 수식은 다음과 같은 과정으로 동작합니다.

1. IF($A$2:$A$19="총무부", ROW($A$2:$A$19)-ROW($A$2)+1)

  • 목적: "총무부"에 해당하는 행 번호를 계산합니다.
  • 동작:
    • $A$2:$A$19 범위에서 "총무부"와 일치하는 셀을 찾습니다.
    • 일치하는 셀의 행 번호를 계산합니다. 이때, ROW($A$2:$A$19)-ROW($A$2)+1을 사용하여 상대적인 행 번호를 구합니다.
      • ROW($A$2:$A$19): A2:A19 범위의 각 셀의 행 번호를 반환합니다. 예: A2는 2, A3은 3, ..., A19는 19.
      • ROW($A$2): A2의 행 번호인 2를 반환합니다.
      • ROW($A$2:$A$19)-ROW($A$2)+1: 상대적인 행 번호를 계산합니다. 예: A2는 2-2+1=1, A3은 3-2+1=2, ..., A19는 19-2+1=18.
    • 결과적으로 "총무부"에 해당하는 행 번호만 남고, 나머지는 FALSE로 반환됩니다.

예시:

A계산 결과

총무부 1 (행 번호 2 - 2 + 1 = 1)
영업부 FALSE
사입팀 FALSE
비서실 FALSE
총무부 4 (행 번호 5 - 2 + 1 = 4)
임시 FALSE
임시 FALSE
사입팀 FALSE
총무부 8 (행 번호 9 - 2 + 1 = 8)
영업부 FALSE
경리부 FALSE
임시 FALSE
총무부 13 (행 번호 14 - 2 + 1 = 13)
임시 FALSE
임시 FALSE
경리부 FALSE
총무부 17 (행 번호 18 - 2 + 1 = 17)
사입팀 FALSE

2. SMALL(..., ROW(A1))

  • 목적: "총무부"에 해당하는 행 번호 중에서 가장 작은 값부터 순서대로 추출합니다.
  • 동작:
    • IF 함수에서 반환된 행 번호 배열(1, 4, 8, 13, 17) 중에서 ROW(A1)을 사용해 가장 작은 값부터 순서대로 추출합니다.
      • ROW(A1)은 1을 반환합니다. 따라서 SMALL(..., 1)은 가장 작은 값인 1을 반환합니다.
      • 수식을 아래로 드래그하면 ROW(A1)이 ROW(A2)로 변경되고, SMALL(..., 2)는 두 번째로 작은 값인 4를 반환합니다.
      • 이 과정을 반복하여 모든 행 번호를 순서대로 추출합니다.

예시:

  • D2 셀: SMALL(..., 1) → 1
  • D3 셀: SMALL(..., 2) → 4
  • D4 셀: SMALL(..., 3) → 8
  • D5 셀: SMALL(..., 4) → 13
  • D6 셀: SMALL(..., 5) → 17

3. INDEX($B$2:$B$19, ...)

  • 목적: SMALL 함수로 추출된 행 번호를 사용해 B2:B19 범위에서 해당 이름을 반환합니다.
  • 동작:
    • INDEX 함수는 B2:B19 범위에서 SMALL 함수로 추출된 행 번호에 해당하는 값을 반환합니다.
      • 예: SMALL(..., 1)이 1을 반환하면, INDEX($B$2:$B$19, 1)은 B2 셀의 값인 총무1을 반환합니다.
      • SMALL(..., 2)가 4를 반환하면, INDEX($B$2:$B$19, 4)는 B5 셀의 값인 총무2를 반환합니다.

예시:

  • D2 셀: INDEX($B$2:$B$19, 1) → 총무1
  • D3 셀: INDEX($B$2:$B$19, 4) → 총무2
  • D4 셀: INDEX($B$2:$B$19, 8) → 총무3
  • D5 셀: INDEX($B$2:$B$19, 13) → 총무4
  • D6 셀: INDEX($B$2:$B$19, 17) → 총무5

4. IFERROR(..., "")

  • 목적: 오류가 발생할 경우 빈 문자열("")을 반환합니다.
  • 동작:
    • INDEX 함수에서 더 이상 추출할 값이 없으면 오류가 발생합니다.
    • IFERROR 함수는 오류가 발생했을 때 빈 문자열("")을 반환하여 깔끔하게 처리합니다.

예시:

  • D7 셀: 더 이상 추출할 값이 없으면 ""(빈 문자열)을 반환합니다.

최종 결과

D

총무1
총무2
총무3
총무4
총무5

요약

  1. IF 함수: "총무부"에 해당하는 행 번호를 계산합니다.
  2. SMALL 함수: 행 번호를 순서대로 추출합니다.
  3. INDEX 함수: 행 번호에 해당하는 이름을 반환합니다.
  4. IFERROR 함수: 오류를 처리하여 빈 문자열을 반환합니다.

이렇게 단계별로 동작하여 원하는 결과를 얻을 수 있습니다! 😊

 

filter.xlsx
0.01MB

 

=================================================

SMALL 함수는 데이터 집합에서 k번째로 작은 값을 반환하는 함수입니다. 엑셀에서 정렬이나 순위를 기반으로 값을 추출할 때 유용하게 사용됩니다.

구문

excel
Copy
SMALL(데이터 범위, k)
  • 데이터 범위: 값을 추출할 데이터의 범위입니다.
  • k: 몇 번째로 작은 값을 반환할지 지정합니다. 예: k=1은 가장 작은 값, k=2는 두 번째로 작은 값.

동작 원리

  1. 데이터 범위에서 값을 오름차순으로 정렬합니다.
  2. 정렬된 데이터에서 k번째로 작은 값을 반환합니다.

예제 1: 기본 사용법

다음과 같은 데이터가 있다고 가정합니다.

A

10
20
30
40
50
  • =SMALL(A1:A5, 1) → 가장 작은 값인 10을 반환합니다.
  • =SMALL(A1:A5, 2) → 두 번째로 작은 값인 20을 반환합니다.
  • =SMALL(A1:A5, 3) → 세 번째로 작은 값인 30을 반환합니다.

예제 2: 조건과 함께 사용

SMALL 함수는 조건과 함께 사용할 때 매우 유용합니다. 예를 들어, 특정 조건을 만족하는 값 중에서 k번째로 작은 값을 추출할 수 있습니다.

데이터:

AB

10 X
20 Y
30 X
40 Y
50 X
  • 조건: B 열이 X인 값 중에서 k번째로 작은 값을 추출합니다.
  • 수식:
    • IF(B1:B5="X", A1:A5)는 B 열이 X인 경우에만 A 열의 값을 반환합니다. 결과는 {10, FALSE, 30, FALSE, 50}입니다.
    • SMALL 함수는 이 결과에서 k번째로 작은 값을 반환합니다.
  • excel
    Copy
    =SMALL(IF(B1:B5="X", A1:A5), k)

결과:

  • k=1 → 10
  • k=2 → 30
  • k=3 → 50

예제 3: 행 번호 추출

SMALL 함수는 행 번호를 추출할 때도 사용됩니다. 예를 들어, 특정 조건을 만족하는 행 번호를 순서대로 추출할 수 있습니다.

데이터:

A

10
20
30
40
50
  • 조건: A 열의 값이 20보다 큰 행 번호를 추출합니다.
  • 수식:
    • IF(A1:A5>20, ROW(A1:A5)-ROW(A1)+1)는 A 열의 값이 20보다 큰 경우에만 상대적인 행 번호를 반환합니다. 결과는 {FALSE, FALSE, 3, 4, 5}입니다.
    • SMALL 함수는 이 결과에서 k번째로 작은 값을 반환합니다.
  • excel
    Copy
    =SMALL(IF(A1:A5>20, ROW(A1:A5)-ROW(A1)+1), k)

결과:

  • k=1 → 3
  • k=2 → 4
  • k=3 → 5

주의사항

  1. 배열 수식: 조건과 함께 사용할 때는 Ctrl + Shift + Enter를 눌러 배열 수식으로 입력해야 합니다.
  2. k 값: k가 데이터 범위의 개수를 초과하면 #NUM! 오류가 발생합니다. 이때는 IFERROR 함수로 오류를 처리할 수 있습니다.

요약

  • SMALL 함수는 데이터 집합에서 k번째로 작은 값을 반환합니다.
  • 조건과 함께 사용하면 특정 조건을 만족하는 값 중에서 k번째로 작은 값을 추출할 수 있습니다.
  • 행 번호를 추출할 때도 유용하게 사용됩니다.

추가 개선: MATCH 함수 사용

COUNTA 대신 MATCH 함수를 사용하면 더 정확하게 마지막 행을 찾을 수 있습니다.
MATCH 함수를 사용한 동적 범위 수식은 다음과 같습니다.

excel
Copy
=IFERROR(
  INDEX(
    OFFSET($C$4, 0, 0, MATCH("*", $C$4:$C$1000, -1), 1),
    SMALL(
      IF(
        OFFSET($B$4, 0, 0, MATCH("*", $B$4:$B$1000, -1), 1)=$F$3,
        ROW(OFFSET($B$4, 0, 0, MATCH("*", $B$4:$B$1000, -1), 1))-ROW($B$4)+1
      ),
      ROW(B1)
    )
  ),
  ""
)
  • MATCH("*", $B$4:$B$1000, -1):
    • *는 모든 값을 의미합니다.
    • -1은 내림차순으로 검색하라는 의미입니다.
    • 마지막 비어 있지 않은 셀의 위치를 반환합니다.

결론

  • OFFSET과 COUNTA 또는 MATCH를 사용하면 동적 범위를 쉽게 설정할 수 있습니다.
  • 데이터가 추가되면 자동으로 범위가 확장되므로 유지보수가 편리합니다.

filter.xlsx
0.02MB