Как вернуть уникальные значения из одного столбца на основе заполненности другого
Когда дело доходит до обработки данных в таблицах, часто возникает необходимость фильтровать значения в зависимости от заполненности других ячеек. В данной статье мы рассмотрим, как вернуть уникальные значения из первого столбца, которые полностью соответствуют значениям во втором столбце. Рассмотрим на примере.
Постановка задачи
Наша цель – вернуть список значений из первого столбца, которые имеют сопоставленные значения во втором столбце. Из нашего примера в третьем столбце должны появиться только значения "A" и "C", так как только у них все значения заполнены в соответствующих ячейках второго столбца. Значение "B" не будет отображаться, поскольку в столбце 1 имеются пустые ячейки, соответствующие "B" во втором столбце.
Подготовка данных
Исходные данные представлены в виде таблицы:
| Колонка 1 | Колонка 2 | Колонка 3 |
|---|---|---|
| А | 1 | А |
| А | 2 | С |
| А | 3 | |
| Б | 4 | |
| Б | ||
| С | 5 | |
| С | 6 | |
| С | 7 | |
| С | 8 |
Эта таблица иллюстрирует, как должны быть расположены данные для фильтрации.
Использование функции COUNTIFS
Первый этап заключается в использовании функции COUNTIFS, чтобы проверить количество заполненных ячеек в столбце B для каждого уникального значения в столбце A. С помощью следующей формулы мы можем отфильтровать нужные значения:
=UNIQUE(FILTER(A2:A10, COUNTIFS(A2:A10, A2:A10, B2:B10, "<>") = COUNTIFS(A2:A10, A2:A10)))Данная формула создаёт уникальный список значений из столбца "A", количество которых совпадает с количеством их заполненных значений в столбце "B".
Учет пустых строк
Если второй столбец содержит формулы, которые возвращают пустые строки, нужно немного изменить формулу, чтобы проверять не только на пустые ячейки, но и на пустые строки:
=UNIQUE(FILTER(A2:A10, BYROW(A2:A10, LAMBDA(_z, SUMPRODUCT((A2:A10=_z)*(B2:B10<>"")) = COUNTIF(A2:A10, _z)))))Эта версия использует функцию СУММПРОИЗВ для проверки содержимого хранящегося в ячейках, которые могут быть как пустыми, так и заполненными, что делает её более гибкой.
Альтернативные методы фильтрации
Еще один подход заключается в использовании функции XMATCH для фильтрации значений:
=UNIQUE(FILTER(A2:A10, ISNA(XMATCH(A2:A10, A2:A10 & B2:B10))))Тем не менее, стоит отметить, что если значения в столбце A могут совпадать с значениями в столбце B, рекомендуется использовать разделитель, чтобы избежать несоответствий:
=UNIQUE(FILTER(A2:A10, ISNA(XMATCH(A2:A10 & "|", A2:A10 & "|" & B2:B10))))Заключение
Таким образом, с помощью простых формул и функций Excel можно легко анализировать и фильтровать данные. Рассмотренные методы позволяют не только находить уникальные значения, но и обеспечивать проверку на заполненность, что критически важно в процессах отслеживания и обработки данных. Теперь вы знаете, как выполнить данную задачу, и можете использовать эти техники в своей работе!
