Сравнить несколько столбцов (более 2)

Question

Итак, допустим, у меня есть диаграмма с N столбцами данных. Что я хочу сделать, это сравнить каждый столбец с каждым другим столбцом и получить количество равных значений в обоих столбцах. Я знаю, что могу легко сравнить ДВА столбца, просто добавив третий столбец и установив значение 1, если содержимое обоих столбцов совпадает, а затем посчитав 1 в этом третьем столбце. (есть много сообщений людей, задающих этот вопрос, но это более сложно, чем это.) Мне нужно сравнить несколько столбцов, и я не хочу создавать новый столбец сравнения для каждой возможной пары существующих столбцов данных. (N может быть довольно большим, и этот метод в основном требуется около N ^2/2 * дополнительных столбцов. - не допускается) Похоже, что в одной формуле должно быть возможно подсчитать все совпадения между столбцом A и столбцом B, а затем просто скопировать это для сравнения A:C, A:D, B:C, B:D, C:D и т.д. для скольких столбцов есть. Тогда для этого потребуется только N ^2/2 ячеек, а не столько целых столбцов, что намного эффективнее. Любая помощь?

^{*(Технически N *(N-1)/2, но достаточно близко.)}

Answer 1

Вот пример с 3 колонками, который вы можете увеличить.

Сначала вам нужно назвать все исходные столбцы. Поместите заголовки столбцов на столбцы, которые будут использоваться в качестве имен диапазонов. Если (например) у вас есть сотни, но меньше 1000, поместите метки, такие как Col001, Col002, Col003 и т.д. После того, как вы напечатаете метки, нажмите Ctrl+A для этого блока, затем Alt+I (вставить), N (имя), C (Создайте); установите флажок Top Row и нажмите OK.

Теперь вам нужна матричная область для выполнения расчетов. Для n столбцов вам понадобится блок nxn, плюс одна строка и один столбец для меток:

В первой ячейке введите следующую формулу, а затем Shift+Ctrl+Enter, чтобы ввести ее как формулу массива:

= СУММ (ЕСЛИ (ДВССЫЛ (B $ 1)= ДВССЫЛ ($ А2), 1,0))

(Это означает, что сумма 1 для каждой ячейки в диапазоне "Col01", которая равна соответствующей ячейке в диапазоне "Col01"; если не равна, сумма 0.)

Теперь просто скопируйте эту формулу через оставшуюся часть столбца матрицы (не включайте скопированную ячейку в выборку вставки, иначе вы получите "Вы не можете изменить часть массива"). После заполнения всего столбца скопируйте этот столбец (только для ячеек расчета) в другие столбцы, чтобы заполнить матрицу.

Ячейки вдоль диагонали будут просто иметь общее количество строк в исходных столбцах (потому что, например, "Col01" всегда идеально совпадает с "Col01"). Ячейки, отраженные по диагонали, будут иметь идентичные значения, потому что (например) "Col02" против "Col01" имеет то же количество идентичных значений, что и "Col01" против "Col02". Они избыточны, а диагональ не особенно полезна, поэтому вы можете очистить их, чтобы сделать их более читабельными.

Добавил более подробно, в ответ на комментарий ...

На рисунке ниже A7:C16 (синие ячейки) содержат исходные данные. Метки в строке 6 применяются как имена диапазонов, выбрав A6:C16 и выбрав Alt+I (вставить), N (имя), C (создать), затем выбрав Top ON в диалоговом окне "Создать имена из выделения" и нажав ХОРОШО. (Теперь, например, = SUM (Col01) совпадает с = SUM (A7:A16)).

Диапазон B2:D4 - матрица счета. Выберите B2, введите или вставьте формулу и используйте Ctrl+Shift+Enter, чтобы ввести ее как массив. Скопируйте B2 в B3:B4. Затем скопируйте B2:B4 в C2:D4 (это немного суетно, потому что это формула массива). Зеленые клетки представляют количество, которое вы хотите достичь. Диагональ всегда максимальна, потому что (например) Col01 всегда равен Col01, ячейка для ячейки. Другие белые клетки на противоположной стороне диагонали являются избыточным зеркальным отображением зеленых клеток. Теперь вы можете масштабировать его в соответствии с вашими требованиями.

Функция INDIRECT означает использование текста в указанной ячейке в качестве имени диапазона. Так (например) = SUM (INDIRECT (B $ 1)) означает то же самое, что и = SUM (Col01). Знаки $ являются абсолютными ссылками, поэтому вы можете копировать и вставлять формулу по всей матрице без необходимости редактировать каждую из них. B $ 1 означает, что всегда используйте строку 1 в формуле, даже если вы копируете ее вниз. $ A2 означает, что всегда используйте столбец A, даже если вы копируете его поперек.

Еще подробнее ...:^)

Пожалуйста, убедитесь, что:

• Имена диапазонов применяются к исходным данным в плоской таблице (то есть к исходной таблице столбцов, которую вы хотите сравнить друг с другом).
• Формула массива применяется к каждой ячейке в матрице, где будет вычисляться количество совпадений

Мое предположение: имена диапазонов не были правильно применены к исходным данным в плоской таблице. В этом примере "Col01" должен "Refer to" = Sheet1!$ A7 $ 16, и значение (я) должно быть что-то вроде {"5"; "7"; "5"; "9"; ...

После правильного применения имен диапазонов формула массива в ячейках матрицы должна применяться следующим образом:

Теперь ... поскольку перестановки быстро умножаются (3 столбца -> 3 сравнения, 4 -> 6, 5 -> 10, 6 -> 15 и т.д. И т.д.), INDIRECT действительно пригодится - вы можно ввести формулу один раз в B2, а затем вставить ее во все остальные ячейки. (Если вы получите «Невозможно изменить часть массива», посмотрите на содержимое предыдущего ответа, вы поймете это.)

Без НЕПОСРЕДСТВЕННОГО В2 может быть:

{= СУММ (ЕСЛИ (Col01 = Col01,1,0))}

но это означает, что для каждой из других ячеек в матрице вам придется вручную изменить "Col01" на "Col02" и т. д., очень утомительно ...

Без имен диапазонов B2 может быть:

{= СУММ (ЕСЛИ (А7: А16 = А7: A16,1,0))}

но это редактирование было бы все более и более утомительным ...