Формула Excel, которая возвращает строку, если в столбце найдено значение

Question

У меня есть список записей DNS, которые мне нужно отсортировать, чтобы получить хорошие записи.

На листе 1 у меня есть дамп необработанных данных, в столбце 1 - идентификатор зоны, который является числом.

На листе 2 у меня есть столбец, состоящий из идентификаторов зон, которые я хочу сохранить.

На листе 3 я ищу способ взять лист 1 столбца 1, чтобы увидеть, соответствует ли он одному из значений в листе 2 столбца 1. Если это так, то результатом должен быть весь ряд на листе 3.

Это возможно? Пример данных ниже:

Лист1 - 4 колонки

1   foo            A     IP_Address

1   foomaster    CNAME   IP_Address

392 jimmy          A     IP_Address

Sheet2

В колонке 1 приведен список допустимых идентификаторов зон, которые я хочу.

Sheet3

Если значение в sheet1-column1 существует в sheet2-column1, вставьте всю строку из sheet1.

Answer 1

Один быстрый и грязный способ сделать это с помощью = COUNTIF(). Если значение найдено, верните значение из ячейки A1, B1, C1 и т.д., Заполнив формулу справа.

В листе 3, ячейка A1, введите следующее:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)

Теперь используйте функции «Заполнить вправо» (Ctrl+R) и «Заполнить вниз» (Ctrl+D), чтобы применить формулу к необходимому количеству ячеек, в зависимости от количества столбцов +строк, ожидаемых в исходных данных, имеющихся у вас на листе 1. Если поиск будет успешным, он заполнит данные из этой строки на листе 1.

Если поиск не удастся, строка вернет FALSE. Если ячейка на листе 1 не имеет данных, она вернет 0. При желании вы можете вернуть пустой текст ("") вместо FALSE или 0 с формулой, например:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")

Сказать это снова - это быстро и грязно и будет иметь последствия для производительности, если у вас большой набор данных. Как правило, лучше размещать необработанные данные в базе данных - затем вы можете использовать сводную таблицу или простые запросы SQL для извлечения необходимых данных в требуемом формате.

Answer 2

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

=index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))

Так что это похоже на перетаскиваемый vlookup где вы будете заполнять новую таблицу того же размера, что и sheet1 , в случае, если она не найдет совпадений, она вернет #N/A , если вы захотите обработать это, затем поместите его в ИГЕРРОР как это

=IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")

Затем вам нужно будет отфильтровать и удалить записи "Нет совпадений". Но это формула, которую я лично использую для такого рода вещей.

Может , ' т.д.; Синтаксис не то же самое для вас из-за локали и разных версий Excel.

ура