Я решил это, не идеально (ему нужно 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, 2, 1, # ЗНАЧЕНИЕ, 2, # ЗНАЧЕНИЕ, 2, 1, 1, # ЗНАЧЕНИЕ, 1
- ISNUMBER: TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE
- значение, если 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
- ячейка: ROWS (...) = 1 => 4
- ячейка: ROWS (...) = 2 => 3
- ячейка: ROWS (...) = 3 => 1
- ячейка: ROWS (...) = 4 => 2
- ячейка: ROWS (...) = 5 => 5
- INDEX (arg1 из COUNTIF, Match (...)) , наконец, покажет отсортированную запись, соответствующую ее результату ROWS (...).
Результат для верхнего примера:
- ячейка: ROWS (...) = 1 => 4 в массиве COUNTIF => c02
- ячейка: ROWS (...) = 2 => 3 в массиве COUNTIF => c06
- ячейка: ROWS (...) = 3 => 1 в массиве COUNTIF => c12
- ячейка: ROWS (...) = 4 => 2 в массиве COUNTIF => c13
- ячейка: ROWS (...) = 5 => 5 в массиве COUNTIF => c25
Пока все хорошо, последний шаг - объединить все в одну колонку. По крайней мере, найдена некоторая помощь по слиянию столбцов F & G (но я не буду делать это сегодня).
