Excel подсчитывает количество ячеек с датами

Обновлено: 20.11.2024

Я ищу формулу для запуска COUNTIF (или аналогичной) в диапазоне ячеек, где содержащееся значение является датой, для увеличения счетчика - по сути, что-то вроде:

Чего мне не удалось найти, так это логического теста для части вопроса if_date(). Есть ли способ протестировать ячейку, чтобы проверить, является ли ее содержимое датой?

9 ответов 9

С функциями рабочего листа это сложно, потому что даты в Excel представляют собой просто отформатированные числа. Только функция ЯЧЕЙКА позволяет исследовать формат ячейки (и вы не можете применить это к диапазону, поэтому потребуется вспомогательный столбец). или, если у вас есть только даты и пробелы. или даты и текст, то достаточно использовать функцию COUNT, т.е.

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

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

Внимание! Будут учитываться ячейки с числами.

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

Преобразовать ячейку в текст, используя заданный формат.
Попробуйте использовать DATEVALUE, чтобы преобразовать его обратно в дату.
Исключить все ячейки, в которых DATEVALUE возвращает ошибку

В качестве формулы просто используйте приведенный ниже пример, заменив <> ссылкой на диапазон.

Вы должны ввести это как формулу массива с помощью CTRL + SHIFT + ENTER.

Это предполагает, что столбец возможных значений даты находится в столбце A. Вы можете сделать что-то подобное в соседнем столбце:

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

@barryhoudini: Это так близко. Не хватает только использования функции ТЕКСТ в качестве моста. user1600453 конкретизировал это (возможно, вдохновленный этим ответом).

Я не совсем уверен, что функция ТЕКСТ помогает — если значение в ячейке — это число вроде 50, оно все равно будет учитываться, поэтому предложение пользователя 1600453 по-прежнему не считает даты, как таковые — я не уверен это более эффективно, чем предложенный мной ответ (и, безусловно, более сложный)

Чтобы подсчитать числа или даты, соответствующие одному критерию (например, равно, больше, меньше, больше или равно или меньше или равно), используйте функцию СЧЁТЕСЛИ. В Excel 2007 и более поздних версиях для подсчета чисел или дат, попадающих в диапазон (например, больше 9000 и в то же время меньше 22500), можно использовать функцию СЧЁТЕСЛИМН. Если вы используете Excel 2003 или более раннюю версию, вы можете использовать функцию СУММПРОИЗВ для подсчета чисел, попадающих в диапазон (СЧЁТЕСЛИМН был введен в Excel 2007).

В Excel нет интерактивного решения, потому что некоторые функции не подходят для работы с векторами, например CELL, приведенный выше. Например, можно подсчитать все числа, абсолютное значение которых меньше 3, потому что ABS принимается внутри массива формул.

Поэтому я использовал следующую формулу массива (Ctrl+Shift+Enter после редактирования без фигурных скобок)

Если столбец F имеет

Считается 3! (-2,2 и 1). Чтобы показать, насколько ABS является удобной для работы с массивами функцией, давайте проведем простой тест: выберите G1:G5, цифру =ABS(F1:F5) в строке формул и нажмите Ctrl+Shift+Enter. Это как если бы кто-то написал Abs(F1:F5)(1), Abs(F1:F5)(2) и т. д.

Теперь я помещаю смешанные данные, включая 2 значения даты.

В этом случае CELL завершается ошибкой и возвращает 1

Это происходит потому, что CELL возвращает формат первой ячейки диапазона. (D4 — это формат m-d-y)

Поэтому остается только программировать! UDF (определяемая пользователем функция) для массива формул должна возвращать массив вариантов:

Теперь все просто (константа VbDate равна 7):

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

Как посчитать, содержат ли ячейки какие-либо даты/данные в Excel?

Сталкивались ли вы с проблемами при подсчете количества ячеек, содержащих любую дату или данные в определенном диапазоне в Excel? В этой статье я расскажу о способах подсчета, содержат ли ячейки какие-либо даты/данные в Excel.

Подсчитывать, содержат ли ячейки какую-либо дату в Excel
Подсчитайте, содержат ли ячейки какие-либо данные, с помощью функции СЧЁТЗ в Excel
Подсчет и выбор ячеек, если они содержат какие-либо данные в указанном диапазоне (только один клик)

Учитывать, содержат ли ячейки какую-либо дату в Excel

Этот метод вводит макрос VBA для подсчета ячеек, содержащих даты в определенном диапазоне в Excel.

Шаг 1. Откройте окно Microsoft Visual Basic для приложений, одновременно нажав клавиши Alt + F11.

Шаг 2. Нажмите «Вставить» > «Модуль», а затем вставьте следующий код VBA в окно «Модуль»:

VBA: подсчитывать, содержат ли ячейки дату

Function TypeCell(pRange As Range) As Variant

Шаг 3. В пустой ячейке введите формулу =SUM(IF(TypeCell(A1:E15)=7,1,0)) , а затем одновременно нажмите клавиши Ctrl + Shift + Enter.

Примечание. В формуле =SUM(IF(TypeCell(A1:E15)=7,1,0)), A1:E15 — это конкретный диапазон, в котором мы будем подсчитывать количество ячеек, содержащих даты, вы можете изменить его. исходя из ваших потребностей.

Теперь вы получите количество ячеек, содержащих даты в определенном диапазоне.

Легкий выбор и подсчет любых ячеек дат, если даты находятся между двумя датами в Excel

Если у вас установлен Kutools for Excel, вы можете применить его утилиту «Выбрать специальные ячейки», чтобы быстро выбрать и подсчитать любые ячейки даты, если даты находятся в определенном диапазоне дат. Полнофункциональная бесплатная пробная версия на 30 дней!

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

Подсчитайте, содержат ли ячейки какие-либо данные с помощью функции СЧЁТЗ в Excel

Функция COUNTA поможет вам легко определить количество ячеек с любыми данными в определенном диапазоне в Excel.

В пустой ячейке введите формулу =СЧЁТЕЗ(A1:E15) и нажмите клавишу Enter. В формуле диапазон A1:E15 — это конкретный диапазон, в котором вы будете подсчитывать количество ячеек с данными, и вы можете изменить его по своему усмотрению.

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

Подсчет и выбор ячеек, если они содержат какие-либо данные в указанном диапазоне

Если у вас установлен Kutools for Excel, вы можете применить его утилиту «Выбор непустых ячеек» для подсчета и выбора ячеек, если они содержат какие-либо данные, быстро в определенном диапазоне одним щелчком мыши.

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Бесплатная пробная версия сейчас!

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

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

Нажмите кнопку OK, чтобы закрыть это диалоговое окно.

Демонстрация: подсчет и выбор ячеек, если они содержат какие-либо данные в указанном диапазоне

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!

Excel позволяет подсчитывать ячейки между датами с помощью функции СЧЁТЕСЛИМН. Функция СЧЁТЕСЛИМН подсчитывает количество ячеек, соответствующих одному или нескольким критериям. Это пошаговое руководство поможет пользователям Excel всех уровней в подсчете ячеек между датами.

Рисунок 1. Конечный результат: подсчет ячеек между датами

Окончательная формула: =СЧЁТЕСЛИМН(C3:C9,">="&F2,C3:C9," = СЧЁТЕСЛИМН (диапазон_критериев1 , диапазон_критериев1 , [диапазон_критериев2 , критерий2 ]…)

Criteria_range1 : диапазон данных, который будет оцениваться с использованием критериев1.
Критерий1: критерий или условие, определяющее, какие ячейки будут учитываться.
Criteria_range2 и критерии2 необязательны; применяется только в том случае, если указано более одного критерия

Настройка наших данных

Наша таблица содержит список имен (столбец B) и дней рождения (столбец C). Ячейки F2 и F3 содержат дату начала и дату окончания, которые будут нашими критериями при подсчете количества ячеек между этими датами. Результирующий счетчик будет в ячейке F4.

Рисунок 2. Пример данных для подсчета ячеек между датами

Подсчет ячеек между датами с использованием ссылки на ячейку

Мы хотим определить количество празднующих, чьи дни рождения приходятся на 2013 г., где дата начала – 1 января 2013 г. (01.01.2013), а конечная – 31 декабря 2013 г. (31.12.2013). .

Чтобы подсчитать количество ячеек между указанными датами, мы выполним следующие действия:

Шаг 1 . Выберите ячейку F4

Шаг 2 . Введите формулу: =СЧЁТЕСЛИМН(C3:C9,">="&F2,C3:C9," : нажмите клавишу ВВОД

В нашей формуле есть два критерия. Подсчитываются даты, которые приходятся на 01.01.2013 или позже, и в то же время приходятся на 31.12.2013 или ранее. Символ « >= » означает «на или после», а « » означает «на или до». Амперсанд «&» используется для связи символов и дат, указанных в ячейках F2 и F3.

Рисунок 3. Использование функции COUNTIFS для подсчета ячеек между датами

Формула возвращает значение 2, которое соответствует двум знаменитостям, дни рождения которых приходятся на 2013 год, Энтони и Райану.

Подсчет ячеек между датами с использованием функции DATE

Подсчет ячеек между датами также можно выполнить с помощью СЧЁТЕСЛИМН и функции ДАТА. Функция ДАТА возвращает порядковый номер, представляющий конкретную дату, что делает ее полезной при вычислениях с датами. .

Синтаксис функции DATE

=ДАТА(год,месяц,день)

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

Чтобы подсчитать количество ячеек между датами 01.01.2013 и 31.12.2013, выполните следующие действия:

Шаг 1 . Выберите ячейку F4

Шаг 2 . Введите формулу: =СЧЁТЕСЛИМН(C3:C9,">="&ДАТА(2013,1,1),C3:C9," : нажмите клавишу ВВОД

Результат равен 2, как и в предыдущем примере.

Рисунок 4. Использование функций COUNTIFS и DATE для подсчета ячеек между датами

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Вы можете использовать функцию СЧЁТЕСЛИМН, чтобы подсчитать количество ячеек между двумя датами в файле Excel. В этом примере функция СЧЁТЕСЛИ не подходит, потому что вы не можете использовать СЧЁТЕСЛИ для нескольких критериев, а мы хотим подсчитать количество записей Excel, попадающих между двумя датами.

Пошаговая формула СЧЁТЕСЛИМН с двумя датами

Введите =СЧЁТЕСЛИМН(
Выберите или введите ссылку на диапазон для критерии_диапазона1. В моем примере я использовал именованный диапазон: День рождения .
Вставьте критерии1. Я хотел подсчитать все даты рождения после 1 января 1985 года, поэтому вставил ">

В нашем примере ячейка F3 содержит следующую формулу для подсчета, если дата находится между двумя датами:
=СЧЁТЕСЛИМН(День рождения, ">="&ДАТА(E3,1,1), День рождения, "

Поскольку операторы "> ДАТА функционируют из формулы СЧЁТЕСЛИМН, и все готово.

Что делать дальше?

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

В качестве альтернативы, если у вас есть дополнительные вопросы о том, как использовать СЧЁТЕСЛИМН с диапазонами дат и несколькими критериями, сообщите мне об этом, опубликовав комментарий.

Тысячи людей воспользовались моими бесплатными уроками Excel. Мой единственный вопрос: ты будешь следующим?

Оставить ответ Отменить ответ

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

19 комментариев к статье "Как считать ячейки между двумя датами с помощью СЧЁТЕСЛИМН"

Здравствуйте, подскажите, как использовать COUNTIFS из DATEDIF?

Я использовал DATEDIF для расчета разницы во времени списка данных, но теперь мне нужно сгруппировать и подсчитать их по нескольким категориям, таким как 0–2 дня, 1–3 дня, 4–6 дней и т. д.< /p>

Пожалуйста, сообщите, можно ли это сделать, и мы очень ценим ваш быстрый ответ! Спасибо!

Да, это можно сделать. Вы должны просто использовать результат DATEDIF в формуле COUNTIF.

Здравствуйте! Мне понравились ваши объяснения, и они действительно помогли мне. В настоящее время я работаю над проектом, который поставил меня в тупик.

Вот проект:

В настоящее время я пытаюсь автоматизировать процесс подсчета количества билетов на мероприятия, проданных за неделю. Каждую неделю я извлекаю данные из нашей программы продажи билетов, в которых указана дата покупки и название шоу. Эта информация находится на листе под названием «региондо-продажи-экспорт», поэтому я могу добавлять новые данные каждую неделю, а Excel автоматически подсчитывает количество проданных билетов.

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

Я знаю, что это неверно, но я написал свою формулу следующим образом:

B:B — диапазон дат из извлеченных данных отчета о продажах
J3 — дата начала
J4 — дата окончания
F:F — заголовок шоу в экспортируемом отчете о продажах
B7 — это название передачи в еженедельном отчете, с которым я пытаюсь сопоставить

Я написал вам электронное письмо.

Спасибо за урок! У меня все еще есть проблема с созданием правильной формулы. Я пытаюсь подсчитать количество вхождений для программы на основе промежутка времени, на основе СЕГОДНЯ(), с шагом 1 месяц, 3 месяца. 6 месяцев, 9 месяцев, 12 месяцев, 15 месяцев, 18 месяцев и 24 месяца.

Ячейки с AB87 по AB94 имеют приращения времени (1, 3, 6, 9 и т. д.)
Ячейки с AC87 по AC94 предназначены для подсчета количества вхождений в соответствующих ячейках в столбце AB на основе СЕГОДНЯ ( ). . . каждый день число может меняться в зависимости от даты

Ячейка AB85 — программа
Исходные данные — ячейки AR (программа) и H (дата выпуска) на отдельном листе («НЕОБРАБОТАННЫЕ ДАННЫЕ!»)

Если вы не можете включить в формулу «СЕГОДНЯ()», текущая дата находится в ячейке H2

Я уже три недели пытаюсь в этом разобраться, но так и не понял. Любая помощь, которую вы можете оказать, более чем приветствуется. Спасибо!

Трудно написать формулу, не видя самого файла и того, что вы туда включили. Если хотите, я могу просмотреть файл и внести предложения. Если, конечно, нет конфиденциальных данных.

Просто отправьте мне сообщение через страницу контактов, и я отвечу по электронной почте.

Всего наилучшего,
Раду

Отличный урок! Я работаю над листом, где:
Столбец A – это статус с данными "Пройдено" и "Выполняется"
Столбец B – это дата, когда учащиеся сдали экзамен
Столбец C – ячейка 1 – Дата начала
Ячейка 1 столбца D — это дата окончания

Теперь в столбце E я хочу подсчитать, сколько человек прошло в диапазоне дат столбцов C и D.

Спасибо за урок. Можете ли вы помочь мне понять, почему первая формула не работает, а вторая нет? A2 и A3 относятся к полям, которые сформулированы как поля даты и заполнены 2021-09-13 и 2021-09-14 соответственно. вставляйте даты каждый раз.

Глядя на вашу формулу, я вижу несколько проблем:

<р>2. У вас больше условий, чем нужно. Насколько я понимаю, вы хотите проверить, сколько ячеек содержат даты, находящиеся между двумя датами, указанными в ячейках A2 и A3. Для этого вам нужно всего два условия, а вы предоставляете три. Формула, которую вы должны использовать, такова:

Привет. Спасибо за формулу.

Я хочу сделать countif на основе ниже:
1. Уникальный идентификатор
2. Дата
3. Время начала
4. Время окончания

Я стремлюсь подсчитать количество зрителей сеансов веб-семинара во время его первой прямой трансляции. В настоящее время в данных есть все зрители с первого выхода в эфир до данных.

Пожалуйста, дайте мне знать, может ли это сделать countif.

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

Я бы сказал, что использование только СЧЁТЕСЛИМН не поможет вам получить правильный результат без некоторых дополнительных действий.

Если вы используете Office 365, в Excel есть несколько действительно полезных функций, таких как UNIQUE и FILTER, которые подходят для вашей задачи.

Читайте также: