Подсчитайте количество конкретных слов в списке

У меня есть несколько больших вычислений, так как у меня есть файл Excel со столбцом, представляющим список уникальных идентификаторов людей, которые работали над каждым инцидентом в нашей системе. Я хотел бы узнать общее количество вмешательств, которые были сделаны во всех случаях. Например, допустим, у меня есть это:

ID|People working on that incident ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ 0|AA0000 BB1111 CC2222 ZZ1234 1|BB1111 2|CC2222 ZZ1234 CC2222 ZZ1234 3|BB1111 CC2222 AA0000 BB1111 

У меня есть список с именем List, в котором есть зона со списком идентификаторов людей, которые я действительно хочу включить. Например, предположим, что первая зона List = {"AA0000","CC2222"} .

Теперь я хотел бы знать, сколько вмешательств было сделано нашими сотрудниками (в списке ) по всем инцидентам, которые у меня есть (у нас есть 4 в массиве выше). Результатом будет 6: 2 вмешательства для идентификатора инцидента 0, 0 для ID 1, 2 для ID 2 и 2 для ID 3.

Предполагая, что данные находятся в другой (закрытой) книге, как я могу рассчитать, используя мой список List и диапазон выше A1: B4 (я бы хотел в конечном итоге использовать целые столбцы, так скажем A: B)?


РЕДАКТИРОВАТЬ:

У меня уже есть что-то работающее, которое подсчитывает количество раз, когда определенное слово находится во всей колонке.

 SUM( LEN('[myFile.xlsx]Sheet1'!$A:$A) -LEN( SUBSTITUTE('[myFile.xlsx]Sheet1'!$A:$A;$Z$1;"") ) ) /LEN($Z$1) 

Z1 – это слово, которое я ищу (пример: CC2222) и '[myFile.xlsx]Sheet1'!$A:$A – столбец, в котором я ищу.

Не существует ли действительно простой способ заставить это работать с массивом вместо Z1? Длина всегда одна и та же (шесть плюс пробел).

Источник: http://office.microsoft.com/en-ca/excel-help/count-the-number-of-words-in-a-cell-or-range-HA001034625.aspx

Разделите исходные данные ColumnB с текстом в столбцы. Unpivot результата, удалите средний столбец и поверните то, что осталось.

Вы можете сделать это довольно легко с помощью функции, определяемой пользователем. Функция ниже принимает два аргумента. Первый – это диапазон, составляющий второй столбец, обозначенный выше «Люди, работающие над этим инцидентом». Второй – это ваш список, который представляет собой диапазон, состоящий из одной записи для каждого идентификатора, который вы хотите подсчитать. Как показано в вашем примере, если несколько идентичных идентификаторов показаны в одной записи (например, ваш ID 2 имеет CC2222, повторяемый дважды), они будут подсчитываться.

Чтобы ввести эту пользовательскую функцию (UDF), открывается редактор Visual Basic. Убедитесь, что ваш проект выделен в окне Project Explorer. Затем в верхнем меню выберите «Вставить / Модуль» и вставьте код ниже в открывшееся окно.

Чтобы использовать эту пользовательскую функцию (UDF), введите формулу типа

 =InterventionCount(B2:B5,H1:H2) 

в некоторой ячейке.

 Option Explicit Function InterventionCount(myRange As Range, myList As Range) As Long Dim RE As Object, MC As Object Dim vRange As Variant, vList As Variant Dim sPat As String Dim I As Long vRange = myRange vList = myList If IsArray(vList) Then For I = 1 To UBound(vList) If Not vList(I, 1) = "" Then _ sPat = sPat & "|" & vList(I, 1) Next I Else sPat = "|" & vList End If sPat = "\b(?:" & Mid(sPat, 2) & ")\b" Set RE = CreateObject("vbscript.regexp") With RE .Global = True .ignorecase = True .Pattern = sPat End With For I = 1 To UBound(vRange) Set MC = RE.Execute(vRange(I, 1)) InterventionCount = InterventionCount + MC.Count Next I End Function 

Для решения, отличного от VBA, вы можете использовать вспомогательную колонку. Опять же, List – это один столбец, который содержит список людей, которые вы хотите добавить, одну запись на ячейку.

Если ваши данные находятся в столбце B, добавьте столбец и введите эту формулу в B2:

Эта формула должна быть введена в массив ; и члены $ A: $ J представляют собой счетчик, допускающий до десяти элементов в записях в столбце B. Если их может быть больше, раскройте по мере необходимости: например, до 26 элементов, вы измените их на $ A : $ Z

 =SUM(N(TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),(COLUMN($A:$J)=1)+(COLUMN($A:$J)>1)*(COLUMN($A:$J)-1)*99,99))=(List))) 

Заполните, насколько это необходимо, затем SUM, чтобы получить общую сумму.


Чтобы ввести массив, введите формулу после ввода формулы в ячейку или панель формул, удерживая нажатой клавишу ctrl-shift , нажав кнопку enter . Если вы сделали это правильно, Excel поместит фигурные скобки {…} вокруг формулы.

Наконец, я пришел к совершенно другому решению, основанному на моей рабочей формуле для 1 сотрудника:

 SUM( LEN('[myFile.xlsx]Sheet1'!$A:$A) -LEN( SUBSTITUTE('[myFile.xlsx]Sheet1'!$A:$A;$Z$1;"") ) ) /LEN($Z$1) 

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

Это не так элегантно, как хотелось бы, и я чувствую, что это обходное решение, но поскольку это самое простое решение с точки зрения программирования и что мне нужны отдельные данные, это то, что мне действительно нужно сейчас.

+1 ко всем остальным ответам за вашу помощь.

  • COUNTIF, чтобы показать Blank, если Zero
  • Установка всех листов Excel с определенным уровнем масштабирования
  • Ярлык для применения «числового формата» с двумя знаками после запятой (отрицательные числа отображаются красным цветом с круглыми скобками)
  • Сделайте HLOOKUP не соответствующей подстроке, как Канзас в Арканзасе
  • Функция Excel SUMPRODUCT с использованием WEEKNUM
  • Excel VBA для заполнения цветов круговой диаграммы из ячеек с условным форматированием
  • Измените цвет метки данных, помещенной с использованием опции «наилучшего соответствия», за пределами круговой диаграммы - Excel 2010
  • Условное форматирование в Excel 2010 с переменными входами
  • Удаление содержимого из ячейки таблицы Word с помощью Excel VBA
  • Извлечение данных из неопределенного размера диапазона в Excel
  • Условное форматирование. Получение корректных ссылок на ячейки.
  • Interesting Posts

    Microsoft Visual basic 6.5 Игнорировать дело

    копировать пасту excel vba

    Пытается прочитать файл Excel (xls), не загружая его сначала

    Как вы получаете только имя файла, а не весь путь к файлу открытого файла?

    Заменить значение, основанное на условиях двух строк VBA

    Автоматическое создание сводной таблицы Excel VBA из нескольких динамических диапазонов консолидации

    Как я могу вызвать функции R из сценария Excel vba на Mac и Windows?

    Пропустить первое значение диапазона столбцов и добавить в список

    Sikuli: Как нажимать на каждый промежуток времени excel-клеток?

    Excel Vba – ошибка во время выполнения 1004 при попытке присвоить значение формулы серии для соты

    Вставить строку, если она еще не была

    TIMEVALUE в Excel конвертирует более 24 часов

    Найти определенный текст в строке

    макрос для вставки пустых строк на основе значения столбца

    Разделение адресов на столбцы в Excel

    Давайте будем гением компьютера.