Можно ли отфильтровать массив по частичной строке и вывести несколько записей только один раз, используя одну формулу (без автофильтра, 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
Я решил эту задачу, не идеально (нужно 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! | |
Столбец Еперечислить все элементы столбца C, если соответствующая ячейка в столбце B содержит выражение D3. Формула в ячейке E4, которая копируется в E5-E14:
{=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).
Эта функция возвращает значения с определенным относительным положением в наборе данных. Это именно то, что нужно для правильного использованияЕСЛИиРЯДЫ, более глубоко вложенные. ЕСЛИ (логический тест,значение, если правда)это основная часть трюка: он создает массив номеров строк, где условие ЕСЛИ истинно (обратите внимание, чтоЕСЛИне имеет 'еще' значение, это будет простоЛОЖЬгде условие не верно)
- логический тест:ЕЧИСЛО(ПОИСК($D$3&"/*"; $B$4:$B$14))возвращает массив True и False в зависимости от того, возвращает ли SEARCH числовое значение или нет для каждой ячейки, заданной в диапазоне $B$4:$B$15.
Результат для верхнего примера:- ПОИСК:1, 2, 1, #ЗНАЧЕНИЕ, 2, #ЗНАЧЕНИЕ, 2, 1, 1, #ЗНАЧЕНИЕ, 1
- НОМЕР:ИСТИНА, ИСТИНА, ИСТИНА, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ИСТИНА, ИСТИНА, ЛОЖЬ, ИСТИНА
- значение, если true: ROW($B$4:$B$14)) возвращает массив, заполненный номерами строк массива $B$4:$B$14
Результат для верхнего примера: 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
ЕСЛИбудет объединятьлогический тест#1 изначение, если правда#2. Все значения в #1 будут проигнорированы, если #2 говорит ЛОЖЬ в той же позиции в массиве #2. Наконец, у нас есть массив, указывающий, в какой строке столбца B, в пределах диапазона, указанного вРЯД(...)выражение D3 найдено. Результат для верхнего примера: 4,5,6,8,10,11,12,14
- логический тест:ЕЧИСЛО(ПОИСК($D$3&"/*"; $B$4:$B$14))возвращает массив True и False в зависимости от того, возвращает ли SEARCH числовое значение или нет для каждой ячейки, заданной в диапазоне $B$4:$B$15.
СТРОКИ($E$4:E4)это просто трюк, который даст вам увеличивающееся число (например, 1 в F2, 2 в F3...). что используется вМАЛЕНЬКИЙкакарг2. Результат в первой ячейке (СТРОКИ(...)=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));
"")}
Вот что он делает:
- СЧЁТЕСЛИ(арг1,арг2)результаты массив длины диапазонаарг1, указывающий на 1, где находится совпадение записей варг2.
Результат для верхнего примера: 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0 - Соответствовать(искомое значение,множество,тип соответствия)ищет первое вхождение 0 в результирующем массивеСЧЁТЕСЛИ(...)какова 3-я позиция в верхнем примере?
- искомое значение:0, первое новое значение/не повторяющееся
- множество:результирующий массивСЧЁТЕСЛИ(...)
- тип соответствия:0 = точно
- ИНДЕКС(арг2из СЧЁТЕСЛИ, Сопоставление(...))наконец покажет новое/не дублированное значение, которое находится на 3-й позиции диапазонаарг2, что такое 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));
"")}
Вот что он делает:
- СЧЁТЕСЛИ(арг1,арг2)это основная часть трюка: он сравнивает текстовые значения, указанные варг2со всеми остальными текстовыми значениями, указанными варг1и возвращает его относительный ранг (алфавитный порядок).
Результат для верхнего примера:
3, 4, 2, 1, 5 - СТРОКИ($E$2:E2)это просто трюк, который даст вам увеличивающееся число (например, 1 в G2, 2 в G3...). что используется вСоответствоватькакискомое значение.
- Соответствовать(искомое значение,множество,тип соответствия)ищет первое появлениеСТРОКИ(...)в полученном массивеСЧЁТЕСЛИ(...).
Результат для верхнего примера:
4, 3, 1, 2, 5- клетка:СТРОКИ(...)=1 => 4
- клетка:СТРОКИ(...)=2 => 3
- клетка:СТРОКИ(...)=3 => 1
- клетка:СТРОКИ(...)=4 => 2
- клетка:СТРОКИ(...)=5 => 5
- ИНДЕКС(аргумент1 из СЧЁТЕСЛИ, Сопоставление(...))наконец покажет отсортированную запись, соответствующую ееСТРОКИ(...)результат. Результат для верхнего примера:
- клетка:СТРОКИ(...)=1 => 4 вСЧЁТЕСЛИмассив => c02
- клетка:СТРОКИ(...)=2 => 3 вСЧЁТЕСЛИмассив => c06
- клетка:СТРОКИ(...)=3 => 1 вСЧЁТЕСЛИмассив => c12
- клетка:СТРОКИ(...)=4 => 2 вСЧЁТЕСЛИмассив => c13
- клетка:СТРОКИ(...)=5 => 5 вСЧЁТЕСЛИмассив => c25
Пока все хорошо, последний шаг — объединить все в одну колонку. По крайней мере, нашел некоторыепомощьобъединение столбцов F&G (но сегодня я этого делать не буду).