Можно ли отфильтровать массив по частичной строке и перечислить несколько записей только один раз, используя одну формулу (без автофильтра, VBA или дополнительных столбцов). Например, у меня есть следующая электронная таблица:

A | B  | C        | D       | F  
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      |
03| 1  | B/as     | V2      |
04| 2  | A/ab     | V3      |
05| 3  | B/ab     | V4      |
06| 3  | B/as     | V5      |
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V5      |
09| 3  | A/ab     | V5      |

Если я отфильтрую столбец B по классу «A /*», но покажу их значение только один раз, результат должен быть:

A | B  | C        | D       | F    
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      | V1
03| 1  | B/as     | V2      | V3
04| 2  | A/ab     | V3      | V2
05| 3  | A/ab     | V1      | 
06| 3  | B/as     | V4      |
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V2      |
09| 3  | A/ab     | V2      |

вместо

A | B  | C        | D       | F   
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      | V1
03| 1  | B/as     | V2      | V3
04| 2  | A/ab     | V3      | V1
05| 3  | A/ab     | V1      | V2
06| 3  | B/as     | V4      | V2
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V2      |
09| 3  | A/ab     | V6      |

Фильтруйте столбец по частичной строке работ (адаптировал это описание) так:

...
    IF(
                    ISNUMBER(Search("A/*"; $B$2:$B$9))  
...

это приводит к массиву с индексами списка $ B $ 2:$ B $ 9:

ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}

так что

IF(IS...): {1;3;4;8;9}

До сих пор я не нашел способа объединить это с подходом "список уникальных имен".

`MATCH(0;INDEX(COUNTIF(` 

как описано здесь

Мало что у меня есть, но это не работает так хорошо и вызывает довольно большую нагрузку на процессор, например, ячейка C8

{=IFERROR(INDEX(
               INDEX($D$2:$D$9;
                     IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
                        ROW($D$2:$D$9)-ROW($D$2)+1)));
               MATCH(0;
                     INDEX(COUNTIF($C$2:C7;
                                   INDEX($D$2:$D$9;
                                         IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
                                            ROW($D$2:$D$9)-ROW($D$2)+1)));
                     0;0);0));
          "error")  

1 ответ1

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 перечислены все элементы столбца C, если соответствующая ячейка в столбце B содержит выражение D3. Формула в ячейке Е4, что копируется в Е5-Е14:

{=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 ячеек, что заняло больше минуты, но только для ввода формулы, копирование в другие ячейки происходит без каких-либо задержек.

Вот что происходит:

  • INDEX (arg1 , arg2) выведет значение элемента / ячейки n (arg2) столбца C (arg1). N рассчитывается в малом.
  • SMALL (arg1 , arg2) должен возвращать k-е (arg2) наименьшее значение в наборе данных (arg1).
    Эта функция возвращает значения с определенным относительным положением в наборе данных. Это как раз то, что нужно для правильного использования IF и ROWS , глубже вложенных.
  • IF (логический тест , значение, если true) является основной частью трюка: он строит массив номеров строк, где условие IF является истинным (обратите внимание, что IF не имеет значения ' else ', это будет просто FALSE, где условие не соответствует действительности)

    • логический тест: ISNUMBER (SEARCH ($ D $ 3 & "/ *"; $ B $ 4: $ B $ 14)) возвращает массив значений True и False в зависимости от того, приводит ли SEARCH к числовому значению или нет для каждой ячейки, заданной в диапазоне $ B $ 4: $ B $ 15.
      Результат для верхнего примера:
      1. ПОИСК: 1, 2, 1, # ЗНАЧЕНИЕ, 2, # ЗНАЧЕНИЕ, 2, 1, 1, # ЗНАЧЕНИЕ, 1
      2. ISNUMBER: TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE
      3. значение, если true: ROW ($ B $ 4: $ B $ 14)) возвращает массив, заполненный номерами строк массива $ B $ 4: $ B $ 14
        Результат для верхнего примера: 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14

    Если IF объединит логический тест № 1 и значение, если оно истинно # 2. Все значения в # 1 будут игнорироваться, где # 2 говорит FALSE в той же позиции в массиве # 2. Наконец, у нас есть массив, указывающий, в какой строке столбца B, в диапазоне, указанном в ROW (...), найдено выражение D3. Результат для верхнего примера: 4,5,6,8,10,11,12,14

  • ROWS ($ E $ 4: E4) - это просто трюк, который даст вам увеличивающееся число (т.е. 1 в F2, 2 в F3 ...). что используется в SMALL как arg2. Результат в первой ячейке (ROWS (...) = 1) будет 4 (наименьшее значение), во второй 5 и так далее. В конце каждой последующей ячейки будет показан номер строки / позиция в столбце B, где находится выражение D3.

Столбец 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));
          "")}

Вот что происходит:

  • COUNTIF(arg1 , arg2) возвращает массив длины диапазона arg1 , указывающий на 1, где где - совпадение записей в arg2.
    Результат для верхнего примера:1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0
  • Match(значение поиска , массив , тип соответствия) ищет первое вхождение 0 в результирующем массиве COUNTIF(...), что является третьей позицией в верхнем примере
    • значение поиска: 0, первое новое значение / не дублируется
    • массив: результирующий массив COUNTIF(...)
    • тип соответствия: 0 = точно
  • INDEX(arg2 из COUNTIF, Match(...)) , наконец, покажет новое / не дублированное значение, которое находится на 3-й позиции диапазона arg2 , то есть c06 в верхнем примере.

Столбец G Наконец, все элементы будут упорядочены в алфавитном порядке. Это формула, введенная в G4 с помощью ctrl-shift-enter:

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

Вот что происходит:

  • COUNTIF(arg1 , arg2) является основной частью трюка: он сравнивает текстовые значения, заданные в arg2, со всеми другими текстовыми значениями, заданными в arg1, и возвращает его относительный ранг (в алфавитном порядке).
    Результат для верхнего примера:
    3, 4, 2, 1, 5
  • ROWS ($ E $ 2: E2) - это просто трюк, который даст вам возрастающее число (т. Е. 1 в G2, 2 в G3 ...). что используется в Match в качестве значения для поиска.
  • Match (значение поиска , массив , тип соответствия) ищет первое вхождение ROWS (...) в результирующем массиве COUNTIF(...).
    Результат для верхнего примера:
    4, 3, 1, 2, 5
    1. ячейка: ROWS (...) = 1 => 4
    2. ячейка: ROWS (...) = 2 => 3
    3. ячейка: ROWS (...) = 3 => 1
    4. ячейка: ROWS (...) = 4 => 2
    5. ячейка: ROWS (...) = 5 => 5
  • INDEX (arg1 из COUNTIF, Match (...)) , наконец, покажет отсортированную запись, соответствующую ее результату ROWS (...). Результат для верхнего примера:
    1. ячейка: ROWS (...) = 1 => 4 в массиве COUNTIF => c02
    2. ячейка: ROWS (...) = 2 => 3 в массиве COUNTIF => c06
    3. ячейка: ROWS (...) = 3 => 1 в массиве COUNTIF => c12
    4. ячейка: ROWS (...) = 4 => 2 в массиве COUNTIF => c13
    5. ячейка: ROWS (...) = 5 => 5 в массиве COUNTIF => c25

Пока все хорошо, последний шаг - объединить все в одну колонку. По крайней мере, найдена некоторая помощь по слиянию столбцов F & G (но я не буду делать это сегодня).

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .