Excel - Как найти общие значения текста из нескольких (> 2) столбцов?

Question

У меня есть 9 столбцов текстовых данных, и мне нужно знать общие термины, которые встречаются во всех девяти столбцах (или, возможно, различные комбинации столбцов). Я могу сделать 2 столбца =IF(ISERROR(MATCH(A1,$C$1:$C$133,0)),"",A1) и я могу найти текстовые значения, которые являются дубликатами по всей электронной таблице, но не могут вычислить Как посмотреть общие текстовые значения в нескольких разных столбцах.

Есть ли способ изменить =IF(ISERROR(MATCH(A1,$C$1:$C$133,0)),"",A1) чтобы я мог сравнивать 3, 4, ... 9 столбцов, а не просто два? Я использую Microsoft 2013, если это поможет.

Answer 1

У меня есть решение, которое будет работать, но оно уродливо. Я собираюсь предположить, что вы смотрите на ячейку A1 и видите, отображается ли она во всех 3 столбцах (D, E, F).

=MIN(MAX(($D$1:$D$3=$A1)*1),MAX(($E$1:$E$3=$A1)*1),MAX(($F$1:$F$3=$A1)*1))

Это формула массива, поэтому ее нужно вводить с помощью Ctrl + Shift + Enter

Теперь, как это работает, начиная с наизнанку

($ D $ 1:$ D $ 3 = $ A1)* 1 Сравните значения от D1 до D3 с A1, которые вернули бы истину или ложь. * 1 преобразует это значение в 1 и 0.

Max Если первая формула находит совпадение в столбце D, она возвращает 1, которое вернет Max. Если совпадений не найдено, максимальное (только) значение равно нулю.

Мин Если все столбцы содержат совпадение, все формулы максимума будут содержать 1, поэтому минимум будет 1. Если во всех столбцах нет совпадения, минимальное значение будет равно нулю.

Если вы хотите посчитать количество соответствующих столбцов, используйте сумму вместо min и запоминайте ctrl+shift+enter.

примечание: это будет одна максимальная формула для каждого столбца, который вы хотите найти.

Answer 2

Эта настройка будет работать для любого количества столбцов.

Сначала перейдите в «Диспетчер имен» (вкладка « Формулы ») и определите следующее:

Name: Range1
Refers to: =$A$1:$I$8

(Или что бы ни случилось, это рассматриваемый диапазон.)

Name: Arry1
Refers to: =COLUMN(Range1)-MIN(COLUMN(Range1))

Name: Arry2
Refers to: =ROW(INDEX(Range1,,1))-MIN(ROW(INDEX(Range1,,1)))+1

Name: Arry3
Refers to: =MMULT(0+(COUNTIF(OFFSET(INDEX(Range1,,1),,Arry1,,),INDEX(Range1,,1))>0),ROW(INDIRECT("1:"&COLUMNS(Range1)))^0)

Выход из диспетчера имен.

Тогда требуемая формула массива :

=IFERROR(INDEX(INDEX(Range1,,1),SMALL(IF(FREQUENCY(IF(INDEX(Range1,,1)<>"",IF(Arry3=COLUMNS(Range1),MATCH(INDEX(Range1,,1),INDEX(Range1,,1),0))),Arry2),Arry2),ROWS($1:1))),"")

Скопируйте вниз, пока не начнете получать пробелы для результатов.

С уважением

Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы просто нажимать Enter, вы сначала удерживаете клавиши Ctrl и Shift, и только затем нажимаете Enter. Если вы сделали это правильно, вы заметите, что Excel заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).

Answer 3

Я думаю, что вы могли бы получить довольно далеко с контрагентом: