Найти наиболее распространенное слово (подстрока) в Excel

Question

Итак, у меня есть столбец с именами баз данных, которые используют различные государственные филиалы. Вот так:

Filemaker
Access, Google Docs
Access, PT Avenue
Salesforce CRM
Microsoft SQL Server
iMIS
PT Avenue, Excel
Access, PT Avenue
Private Database
Google Docs, Constant Contact
Google Docs, Filemaker
Excel
Google Docs
Excel, MemberPlanet

Я хочу создать наиболее часто используемую базу данных. Хитрость в том, что некоторые из этих состояний используют несколько баз данных, которые перечислены в одной ячейке. Я пытался использовать эту формулу:

=INDEX(B5:F5,MODE(MATCH(B5:F5,B5:F5,0)))

Используя эту формулу, можно получить ответ «Access, PT Avenue», когда реальный ответ - "Документы Google". Предполагая, что несколько баз данных всегда будут разделяться запятой, есть ли способ вернуть только одну наиболее часто встречающуюся базу данных (это называется подстрокой, верно?)?

Answer 1

Вот решение, подобное тому, что Мате Юхас предложил в комментарии. Это несколько шагов, но они просты и не требуют сложных формул; Excel делает всю тяжелую работу.

• Разделите данные. Поскольку записи разделяются запятыми, просто выделите данные и используйте текст в столбцах на основе запятых. Это дало мне столбцы A и B ниже. Обратите внимание, что Text to Columns перезаписывает исходные значения, поэтому, если вам нужно сохранить их, просто работайте с копией / вставкой версии данных.

• После каждой запятой есть пробел, который нужно удалить, чтобы имена не отличались из-за пробела. В С3 я использовал:

  =TRIM(B3)
  

  и скопировал это вниз по колонке. Я добавил "База данных" в качестве метки столбца в А1 и С1, которые являются исходными данными для следующего шага.

• Я использовал сводную таблицу для каждого из диапазонов A и C и вставил их один под другим в столбец A. Это быстрое перетаскивание и несколько настроек:

• Я соединил результаты в столбце D со значениями «Копировать» и «Специальная вставка» (простое копирование / вставка будет работать, но это устранит границы). Это создает один список с количеством из каждого столбца.

• Я использовал это для создания новой сводной таблицы, чтобы объединить список с помощью SUM. Вот и все данные в одном списке.

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

  =INDEX(G21:G31,MATCH(MAX(H21:H31),H21:H31,0))
  

  Он находит значение MAX в столбце SUM, использует MATCH для идентификации строки и использует этот результат для выбора имени базы данных в левом столбце.

Answer 2

Как это устроено:

Я предполагаю, что у вас есть данные в A2:A15 .

• Выберите Data и выполните TEXT to COLUMN чтобы разделить их, используя COMMA качестве delimiter .
• Запишите эту формулу в B2 и заполните.

=COUNTIF($A$2:$A2,A2)

• Введите эту формулу обратного просмотра, чтобы получить результат.

=INDEX($A$2:$A$15,MATCH(3,$B$2:$B$15,0))

NB

• По логике, текстовый доступ и Google Docs встречаются одинаково, но так как Access стоит на первом месте, Формула выбирает его.

При необходимости измените ссылки на ячейки в формуле.