У меня есть электронная таблица Google с двумя столбцами: A и C. Столбец A имеет более тысячи строк с именами / предметами в их собственных ячейках, а столбец C имеет несколько сотен строк с переменной комбинацией вышеуказанных имен / предметов в его различных ячейках. , где каждое имя / тема отделяются от других точкой с запятой. Каждый из этих столбцов часто пополняется новыми записями в течение дня.
В качестве очень упрощенного визуального примера настройки:
**Column A: Names | Column B: Occurrences | Column C: List**
A2: Adam | B2: [Blank] | C2: Charles; Adam
A3: Bob | B3: [Blank] | C3: Adam
A4: Charles | B4: [Blank] | C4: Smith, Charles
A5: Smith, Charles | B5: [Blank] | C5: Bob Evans
A6: Bob Evans | B6: [Blank] | C6: Smith, Charles; Charles; Bob
A7: [etc.] | B7: [Blank] | C7: Bob Evans; Charles; Bob
A8: [etc.] | B8: [Blank] | C8: [etc.]
В настоящее время я использую следующую формулу для подсчета количества раз, когда каждая строка из столбца A (здесь, A2) появляется как подстрока в столбце C (здесь, от C2 до C7):
=ARRAYFORMULA(IF(A2="","",(SUMPRODUCT(REGEXMATCH(REGEXREPLACE(REGEXREPLACE($C$2:$C$7,"([\(\)\?])", ""),"(\w+),{0,1}\s+(\w+)","$1$2"),".*(^|\s)"& trim(REGEXREPLACE(REGEXREPLACE($A2,"([\(\)\?])", ""),"(\w+),{0,1}\s+(\w+)","$1$2"))&"(;|$).*")))))
Это дает правильные итоги, но кажется невероятно тяжелым после масштабирования; изменение или добавление какой-либо одной записи в столбец C приводит к тому, что весь лист пересчитывает тысячи записей, и для получения новых итогов требуется несколько минут. Многие из значений REGEXREPLACE используются здесь, потому что некоторые из записей имеют знаки препинания, такие как «()» и «?», Из-за таких ячеек, как «Erōs», «Олимпийские игры (23-е: 1984: Лос-Анджелес, Калифорния)», и «Фома, Аквинский, Святой, 1225? -1274».
Ближайший вариант подсчета, который я придумал, следующий:
=SUMPRODUCT((LEN(C$2:C$7)-LEN(SUBSTITUTE(C$2:C$7,A2,"")))/LEN(A2))
Тестирование показывает, что эта гораздо более простая формула может пересчитать весь лист за несколько секунд, но на самом деле она неправильно подсчитывает записи. В приведенном выше примере C2-C7 он даст итоговые значения 4 и 5 для «Боба» и «Чарльза», потому что он не делает различий между «Бобом» и «Бобом Эвансом» или «Чарльзом» и «Смитом, Чарльзом». ” Он должен правильно найти 2 и 3 соответственно.
Есть ли эффективный способ скорректировать приведенную выше формулу или создать новую, которая будет правильно подсчитывать все подстроки - ограничивая суммы точными совпадениями в столбце A, поскольку они находятся между точками с запятой в столбце C - без остановки вычислений листа по несколько минут за раз? Регулярные выражения были тем путем, которым я сначала пошел, но я думаю, что именно эти операции являются причиной того, что они занимают так много времени.
