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