У меня есть книга Excel, в которой каждая ячейка в столбце B содержит небольшую басню (историю). Эти ячейки (отформатированные как "Общие") содержат текстовые значения, большие по стандартам электронных таблиц: все, кроме одной, превышают ограничение в 255 символов для отображения текста, одна ячейка содержит более 3000 символов, и я полагаю, что большинство из них составляют от 500 до 700 знаков Вторая иллюстрация ниже показывает такую ячейку.
В другой книге у меня есть (мета) данные о баснях. В частности, меня интересует частота встречаемости некоторых слов. На иллюстрации, приведенной ниже, столбец C содержит список слов, которые меня интересуют. Столбец B содержит общее количество вхождений соответствующего слова (считая несколько вхождений в басне); это не имеет отношения к этому вопросу. Я также хочу посчитать количество басен, в которых появляется каждое слово (хотя бы один раз). Я называю это "Дисперсия" (см. Пояснение в комментарии ниже). Я использовал Ctrl+F ("Найти и заменить") и щелкнул "Найти все", что сообщает о количестве ячеек, содержащих строку поиска (см. Вторую иллюстрацию ниже). Я делал это по одному для каждого слова и вручную вводил числа в столбец N.
(Вы можете пропустить этот параграф.)
Мне нужны подсчеты для единичных, целых экземпляров слова, а не для производных форм корневых слов (даже не во множественном числе). Например, мой счет для "животного" должен возвращать счет только для "животного", а не "животного" или любого другого такого варианта.
Вначале я понял, что простой поиск слова может привести к ложному счету, потому что он будет включать слова, содержащие слово, которое я искал.
Я справился с этим, дополнив свои поисковые термины пробелами в начале и конце - в столбце E (например, "animal"), который содержит =" "&C2&" " - и убедившись, что столбец, по которому я проверяю эти слова, также Выделены такие слова.
Везде, где знак препинания падал рядом с последней или первой буквой слова в басне, я вставлял пробел, чтобы исключить любую такую смежность.
Например, «todo esto, porque siendo» стало «todo esto, porque siendo».
(Это было частично вдохновлено комментарием JNevill по этому вопросу:Excel COUNTIF не работает.)
Мои Ctrl+F поиски затем вернули меня, подсчитывает единичные случаи слов, которые я искал.
Конечно, это утомительно, отнимает много времени и подвержено ошибкам, поэтому мне стало интересно, не сможет ли какая-нибудь формула сделать то же самое, но быстрее.
Несколько постов / веб-страниц предположили, что функция COUNTIF могла бы сделать это, поэтому я экспериментировал с этим, но до сих пор мои попытки каждый раз терпели неудачу.
На приведенной выше иллюстрации столбец М содержит
=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2)
и, как вы можете видеть, он отображает "0", когда он должен совпадать с числом в столбце N (т. е. счет, найденный с помощью Ctrl+F).
(Как указано выше, в столбце E содержится искомая строка, то есть искомое слово , дополненное пробелами в начале и конце.)
Каждая конфигурация с использованием функции COUNTIF я пробовал, не удалась.
Ниже изображение ячейки B23 на моем листе басни (то есть одна басня). Представлению частично препятствует диалоговое окно "Найти и заменить", показывающее, что 13 ячеек (басни) содержат слово "животное" (т. Е. Они содержат строку "животное" с начальными и конечными пробелами) хотя бы один раз.
(Вам может потребоваться увеличить изображение, чтобы увидеть это ясно.)
Последнее, что я собираюсь показать вам, это то, что происходит, когда я использую функцию COUNTIF . Я думаю, что это может быть ключом к тому, почему я не могу заставить это работать, потому что, как только я добавляю диапазон из нужного столбца, прежде чем я даже нажму Enter, я вижу #VALUE!; #ЗНАЧЕНИЕ!; ... рядом с ассортиментом.
Это выглядит так:
Когда я нажимаю Enter, я получаю "0", который вы видите для ячеек в этом столбце.
Что я делаю неправильно? Как подсчитать количество басен, в которых появляется каждое слово (хотя бы один раз)?
Если вы не можете сказать по скриншотам, я использую Excel 2007.
