Ранее я задавал вопрос, не создавая учетную запись, и получил хороший ответ от пользователя Jeorje, но я не предоставил достаточно подробностей и не смог ответить на его комментарий ранее.
По этой причине я решил, что попробую сделать второй проход с большим количеством подробностей, примерами из таблицы, с которой я работаю, и возможностью продолжать отвечать на комментарии.
Я хочу создать систему, которая позволит выводить количество раз, когда ключевое слово или фраза были включены либо в абзац текста, либо в заголовок документа в течение диапазона дат, который можно изменить.
Формула, которую я в настоящее время использую для создания выходных данных в ячейке D1, подсчитывающих количество записей в диапазоне дат, выглядит следующим образом:
=COUNTIFS('Main Sheet'!A:A,">="&Trends!C1,'Main Sheet'!A:A,"<="&Trends!A1)
Формула, которую я в настоящее время использую для создания выходных данных в ячейке H2, подсчитывающих количество записей, включая ключевое слово из ячейки G2, выглядит следующим образом:
=(SUM((LEN('Main Sheet'!H:H)-LEN(SUBSTITUTE((LOWER('Main Sheet'!H:H)),LOWER(G2),"")))/LEN(G2)))
Мне было интересно, есть ли способ объединить эти две формулы, чтобы создать желаемый эффект, который я ищу, или есть ли третья формула, которая могла бы объединить данные из этих двух формул, чтобы создать желаемый эффект. Если я совсем не прав, я бы хотел это знать.
Спасибо!
Изображение основного листа
Изображение листа тенденций
решение1
Я считаю, что следующее поможет:
==SUM(IFERROR(IF( 'Main Sheet'!A:A >= Trends!C1, IF( 'Main Sheet'!A:A <= Trends!A1, FIND( G2, 'Main Sheet'!H:H ), NA() ), NA() ), " " ))
(Я добавил много дополнительных пробелов, чтобы элементы были немного заметнее. Каждый набор состоит из двух пробелов, поэтому вы можете использовать Find and Replaceдва пробела без пробелов, и все пробелы, которые я добавил, будут легко удалены.)
Версия формулы, которую я разработал (используя ячейки в МОЕЙ тестовой таблице), отлично работала в нескольких вариациях. Затем я вставил ссылки из ваших двух формул туда, где им место, заменив свои. Я не назвал пару листов и т. д., чтобы проверить, что я сделал это идеально... так что если вы обнаружите, что это не срабатывает, потратьте немного времени на то, чтобы убедиться, что я правильно скопировал и вставил, ну, знаете, пропустил символ или что-то еще.
Трудность, с которой я столкнулся, была IF()в том, что, например, если убить возможность SPILL, то это убьет создание {FALSE;FALSE,TRUE,FALSE}строки во внутренних работах Excel. Или, похоже, это механизм. Оставляет одну ячейку, которая МНОГО тестируется. Однако, как вы видите, не всегда, и в конце концов я нашел комбинацию, которая не испортила все.
В принципе, я использовал FIND()для работы Excel строку {}. Он ищет нужное вам значение, например, "вода", и выдает вам номер символа в строке, с которого он начинается. Так что вы можете получить "5" или "2,184". Но в строке, внутренне, вот так: {"5";"2184"} и COUNT()увидит их как два элемента, а НЕ пять цифр и, следовательно, пять элементов.
В любом случае, неудачи в сопоставлении диапазона дат приведут к ошибкам #NA, а неудачи в поиске значения в строках поиска приведут к ошибкам #VALUE. Успешные результаты дадут номер символа, в котором он нашел первый символ строки поиска. Таким образом, у вас будет строка {} ошибок и числовых значений, которые Excel представляет функции IFERROR(). Ее вывод — это значения, конечно, для не-ошибок и пробел для любых фактических ошибок. Вы можете использовать вместо этого набор двойных кавычек (""), но мне нравится пробел, потому что он заставляет результат рассматриваться как текст при любых обстоятельствах, поэтому он никогда не может каким-либо образом ошибиться, может быть, быть сочтенным как 0 Excel и засчитан. COUNT()любит только числа, поэтому принудительное их всегда быть текстом гарантирует, что они никогда не смогут добавиться к результату.
Затем COUNT()просто подсчитывает эту строку и возвращает количество мест, соответствующих всем трем элементам, начальную и конечную даты (диапазон дат) и вхождение искомой строки в ячейках поиска.
Надеюсь, я правильно выразил свою мысль!