수식만 사용하여 부분 문자열로 반복 항목이 있는 다중 열 목록 필터링

Question

완벽하지는 않지만(3개의 열이 필요함) 해결했지만 매력적으로 작동합니다.

A | B     | C     | D      | E       | F       | G      
-------------------------------------------------------  
01| Array |Array  | Text  | search   | search  | ordered
02| Source|Source | sought| results  | results |
03|   #1  |  #2   | *a*   |    #1    |    #2   |
04| aa    | c12   |       | c12      | c12     | c02
05| ca    | c13   |       | c13      | c13     | c06    
06| ad    | c06   |       | c06      | c06     | c12
07| ee    | c11   |       | c02      | c02     | c13
08| fa    | c02   |       | c06      | c25     | c25      
09| gg    | c12   |       | c13      |         |
10| ba    | c06   |       | c06      |         |
11| aa    | c13   |       | c25      |         |
12| ad    | c06   |       | #NUM!    |         |
13| gt    | c12   |       | #NUM!    |         |
14| aa    | c25   |       | #NUM!    |         |

E열B 열의 해당 셀에 D3의 표현식이 포함되어 있으면 C 열의 모든 항목을 나열합니다. E5-E14에 복사되는 셀 E4의 수식:

{=INDEX(C:C;
        SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
                                 $B$4:$B$14));
                 ROW($B$4:$B$14));
              ROWS($E$4:E4)))}

수식을 배열로 입력하려면 Ctrl-Shift-Enter를 눌러야 하지만, 큰 테이블을 검색할 경우 꽤 오랜 시간이 걸릴 수 있으므로 주의하세요. 나는 1300개의 셀을 가지고 있는데, 수식을 입력하는 데에만 몇 분 이상이 걸렸고 다른 셀에 이를 처리하는 데 지체 없이 완료되었습니다.

다음은 수행되는 작업입니다.

색인 (인수1,인수2)항목/셀 n의 값을 출력합니다(인수2) C 열의 (인수1). N은 다음과 같이 계산됩니다.작은.
작은(인수1,인수2)k 번째를 반환해야 합니다(인수2) 데이터 세트에서 가장 작은 값(인수1).
이 함수는 데이터 세트에서 특정 상대적 순위를 갖는 값을 반환합니다. 이것이 바로 올바른 사용을 위해 필요한 것입니다.만약에그리고행, 더 깊게 중첩되었습니다.
만약에 (논리 테스트,true인 경우 값)트릭의 주요 부분입니다. IF 조건이 참인 행 번호의 배열을 만듭니다.만약에'가 없다또 다른' 가치는 단지거짓조건이 참이 아닌 경우)
- 논리 테스트:ISNUMBER(검색($D$3&"/*"; $B$4:$B$14))$B$4:$B$15 범위에 있는 각 셀에 대해 SEARCH 결과가 숫자 값인지 여부에 따라 True 및 False 배열을 반환합니다.
  위 예의 결과는 다음과 같습니다.
  1. 찾다:1, 2, 1, #값, 2, #값, 2, 1, 1, #값, 1
  2. IS번호:참, 참, 참, 거짓, 참, 거짓, 참, 참, 참, 거짓, 참
  3. true인 경우 값: ROW($B$4:$B$14))는 배열 $B$4:$B$14의 행 번호로 채워진 배열을 반환합니다.
    위 예의 결과는 4, 5, 6, 7, 8입니다. , 9, 10, 11, 12, 13, 14
만약에결합할 것이다논리 테스트#1 및true인 경우 값#2. #1의 모든 값은 무시됩니다. 여기서 #2는 배열 #2 내의 동일한 위치에서 FALSE로 표시됩니다. 마지막으로 주어진 범위 내에서 열 B의 어느 행을 나타내는 배열이 있습니다.열(...)D3의 발현이 발견되었습니다. 위 예의 결과는 4,5,6,8,10,11,12,14입니다.
행($E$4:E4)이는 단지 숫자를 증가시키는 트릭일 뿐입니다(예: F2에서는 1, F3에서는 2...). 무엇에 사용됩니까?작은~처럼인수2. 첫 번째 셀의 결과(행(...)=1)은 4(최저 값)가 되고, 두 번째는 5가 됩니다. 마지막에 각 후속 셀에는 D3의 표현식이 있는 B열의 행 번호/위치가 표시됩니다.

F열필터 중복, 가장 어려웠던 부분이 무엇이었나요? F열에는 E열에 나열된 모든 항목이 "한 번만" 나열됩니다.
F5에 입력한 수식입니다! (F4는 E4와 동일) ctrl-shift-enter 사용:

{=IFERROR(INDEX($C$2:$C$14;
                MATCH(0;
                      COUNTIF($E$4:E4;
                              $C$2:$C$14);
                      0));
          "")}

다음은 수행되는 작업입니다.

카운티(인수1,인수2)결과는 범위 길이의 배열입니다.인수1, 의 항목과 일치하는 위치를 1로 나타냅니다.인수2.
위 예의 결과는 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0입니다.
성냥(조회 값,정렬,일치 유형)결과 배열에서 처음으로 나타나는 0을 찾습니다.백작(...)위 예에서 세 번째 위치는 무엇입니까?
- 조회 값:0, 첫 번째 새 값/중복되지 않음
- 정렬:결과 배열백작(...)
- 일치 유형:0 = 정확히
색인(인수2COUNTIF, 일치(...))마지막으로 범위의 세 번째 위치에 있는 새/중복되지 않은 값을 표시합니다.인수2, 위의 예에서 c06은 무엇입니까?

G열마지막으로 모든 항목은 알파벳순으로 정렬됩니다. 이는 Ctrl-Shift-Enter를 사용하여 G4에 입력된 공식입니다.

{=IFERROR(INDEX($F$4:$F$14;
                        MATCH(ROWS($G$4:$G4);
                              COUNTIF($F$4:$F$14;
                                      "<="&$F$4:$F$14);
                              0));
        "")}

다음은 수행되는 작업입니다.

카운티(인수1,인수2)트릭의 주요 부분입니다. 이것은 주어진 텍스트 값을 비교합니다.인수2다른 모든 텍스트 값은 다음과 같습니다.인수1상대 순위(알파벳순)를 반환합니다.
위 예의 결과는
3, 4, 2, 1, 5 입니다.
행($E$2:E2)이는 단지 숫자를 증가시키는 방법일 뿐입니다(예: G2에서는 1, G3에서는 2...). 무엇에 사용됩니까?성냥~처럼조회 값.
성냥(조회 값,정렬,일치 유형)첫 번째 발생을 찾습니다.행(...)결과 배열에서백작(...).
위 예의 결과는
4, 3, 1, 2, 5 입니다.
1. 셀:행(...)=1 => 4
2. 셀:행(...)=2 => 3
3. 셀:행(...)=3 => 1
4. 셀:행(...)=4 => 2
5. 셀:행(...)=5 => 5
INDEX(COUNTIF의 arg1, 일치(...))마지막으로 해당 항목에 해당하는 정렬된 항목이 표시됩니다.행(...)결과. 위 예의 결과는 다음과 같습니다.
1. 셀:행(...)=1 => 4인치카운티배열 => c02
2. 셀:행(...)=2 => 3인치카운티배열 => c06
3. 셀:행(...)=3 => 1인치카운티배열 => c12
4. 셀:행(...)=4 => 2인치카운티배열 => c13
5. 셀:행(...)=5 => 5인치카운티배열 => c25

지금까지의 마지막 단계는 모든 것을 하나의 열로 결합하는 것입니다. 적어도 일부는 찾았습니다돕다F&G 열 병합(하지만 오늘은 하지 않겠습니다).

Answer 1

완벽하지는 않지만(3개의 열이 필요함) 해결했지만 매력적으로 작동합니다.

A | B     | C     | D      | E       | F       | G      
-------------------------------------------------------  
01| Array |Array  | Text  | search   | search  | ordered
02| Source|Source | sought| results  | results |
03|   #1  |  #2   | *a*   |    #1    |    #2   |
04| aa    | c12   |       | c12      | c12     | c02
05| ca    | c13   |       | c13      | c13     | c06    
06| ad    | c06   |       | c06      | c06     | c12
07| ee    | c11   |       | c02      | c02     | c13
08| fa    | c02   |       | c06      | c25     | c25      
09| gg    | c12   |       | c13      |         |
10| ba    | c06   |       | c06      |         |
11| aa    | c13   |       | c25      |         |
12| ad    | c06   |       | #NUM!    |         |
13| gt    | c12   |       | #NUM!    |         |
14| aa    | c25   |       | #NUM!    |         |

E열B 열의 해당 셀에 D3의 표현식이 포함되어 있으면 C 열의 모든 항목을 나열합니다. E5-E14에 복사되는 셀 E4의 수식:

{=INDEX(C:C;
        SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
                                 $B$4:$B$14));
                 ROW($B$4:$B$14));
              ROWS($E$4:E4)))}

수식을 배열로 입력하려면 Ctrl-Shift-Enter를 눌러야 하지만, 큰 테이블을 검색할 경우 꽤 오랜 시간이 걸릴 수 있으므로 주의하세요. 나는 1300개의 셀을 가지고 있는데, 수식을 입력하는 데에만 몇 분 이상이 걸렸고 다른 셀에 이를 처리하는 데 지체 없이 완료되었습니다.

다음은 수행되는 작업입니다.

색인 (인수1,인수2)항목/셀 n의 값을 출력합니다(인수2) C 열의 (인수1). N은 다음과 같이 계산됩니다.작은.
작은(인수1,인수2)k 번째를 반환해야 합니다(인수2) 데이터 세트에서 가장 작은 값(인수1).
이 함수는 데이터 세트에서 특정 상대적 순위를 갖는 값을 반환합니다. 이것이 바로 올바른 사용을 위해 필요한 것입니다.만약에그리고행, 더 깊게 중첩되었습니다.
만약에 (논리 테스트,true인 경우 값)트릭의 주요 부분입니다. IF 조건이 참인 행 번호의 배열을 만듭니다.만약에'가 없다또 다른' 가치는 단지거짓조건이 참이 아닌 경우)
- 논리 테스트:ISNUMBER(검색($D$3&"/*"; $B$4:$B$14))$B$4:$B$15 범위에 있는 각 셀에 대해 SEARCH 결과가 숫자 값인지 여부에 따라 True 및 False 배열을 반환합니다.
  위 예의 결과는 다음과 같습니다.
  1. 찾다:1, 2, 1, #값, 2, #값, 2, 1, 1, #값, 1
  2. IS번호:참, 참, 참, 거짓, 참, 거짓, 참, 참, 참, 거짓, 참
  3. true인 경우 값: ROW($B$4:$B$14))는 배열 $B$4:$B$14의 행 번호로 채워진 배열을 반환합니다.
    위 예의 결과는 4, 5, 6, 7, 8입니다. , 9, 10, 11, 12, 13, 14
만약에결합할 것이다논리 테스트#1 및true인 경우 값#2. #1의 모든 값은 무시됩니다. 여기서 #2는 배열 #2 내의 동일한 위치에서 FALSE로 표시됩니다. 마지막으로 주어진 범위 내에서 열 B의 어느 행을 나타내는 배열이 있습니다.열(...)D3의 발현이 발견되었습니다. 위 예의 결과는 4,5,6,8,10,11,12,14입니다.
행($E$4:E4)이는 단지 숫자를 증가시키는 트릭일 뿐입니다(예: F2에서는 1, F3에서는 2...). 무엇에 사용됩니까?작은~처럼인수2. 첫 번째 셀의 결과(행(...)=1)은 4(최저 값)가 되고, 두 번째는 5가 됩니다. 마지막에 각 후속 셀에는 D3의 표현식이 있는 B열의 행 번호/위치가 표시됩니다.

F열필터 중복, 가장 어려웠던 부분이 무엇이었나요? F열에는 E열에 나열된 모든 항목이 "한 번만" 나열됩니다.
F5에 입력한 수식입니다! (F4는 E4와 동일) ctrl-shift-enter 사용:

{=IFERROR(INDEX($C$2:$C$14;
                MATCH(0;
                      COUNTIF($E$4:E4;
                              $C$2:$C$14);
                      0));
          "")}

다음은 수행되는 작업입니다.

카운티(인수1,인수2)결과는 범위 길이의 배열입니다.인수1, 의 항목과 일치하는 위치를 1로 나타냅니다.인수2.
위 예의 결과는 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0입니다.
성냥(조회 값,정렬,일치 유형)결과 배열에서 처음으로 나타나는 0을 찾습니다.백작(...)위 예에서 세 번째 위치는 무엇입니까?
- 조회 값:0, 첫 번째 새 값/중복되지 않음
- 정렬:결과 배열백작(...)
- 일치 유형:0 = 정확히
색인(인수2COUNTIF, 일치(...))마지막으로 범위의 세 번째 위치에 있는 새/중복되지 않은 값을 표시합니다.인수2, 위의 예에서 c06은 무엇입니까?

G열마지막으로 모든 항목은 알파벳순으로 정렬됩니다. 이는 Ctrl-Shift-Enter를 사용하여 G4에 입력된 공식입니다.

{=IFERROR(INDEX($F$4:$F$14;
                        MATCH(ROWS($G$4:$G4);
                              COUNTIF($F$4:$F$14;
                                      "<="&$F$4:$F$14);
                              0));
        "")}

다음은 수행되는 작업입니다.

카운티(인수1,인수2)트릭의 주요 부분입니다. 이것은 주어진 텍스트 값을 비교합니다.인수2다른 모든 텍스트 값은 다음과 같습니다.인수1상대 순위(알파벳순)를 반환합니다.
위 예의 결과는
3, 4, 2, 1, 5 입니다.
행($E$2:E2)이는 단지 숫자를 증가시키는 방법일 뿐입니다(예: G2에서는 1, G3에서는 2...). 무엇에 사용됩니까?성냥~처럼조회 값.
성냥(조회 값,정렬,일치 유형)첫 번째 발생을 찾습니다.행(...)결과 배열에서백작(...).
위 예의 결과는
4, 3, 1, 2, 5 입니다.
1. 셀:행(...)=1 => 4
2. 셀:행(...)=2 => 3
3. 셀:행(...)=3 => 1
4. 셀:행(...)=4 => 2
5. 셀:행(...)=5 => 5
INDEX(COUNTIF의 arg1, 일치(...))마지막으로 해당 항목에 해당하는 정렬된 항목이 표시됩니다.행(...)결과. 위 예의 결과는 다음과 같습니다.
1. 셀:행(...)=1 => 4인치카운티배열 => c02
2. 셀:행(...)=2 => 3인치카운티배열 => c06
3. 셀:행(...)=3 => 1인치카운티배열 => c12
4. 셀:행(...)=4 => 2인치카운티배열 => c13
5. 셀:행(...)=5 => 5인치카운티배열 => c25

지금까지의 마지막 단계는 모든 것을 하나의 열로 결합하는 것입니다. 적어도 일부는 찾았습니다돕다F&G 열 병합(하지만 오늘은 하지 않겠습니다).

수식만 사용하여 부분 문자열로 반복 항목이 있는 다중 열 목록 필터링

답변1

관련 정보