☰

Как сравнить последовательные даты Excel БЕЗ преобразования в даты типа мм/дд/гг?

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Как сравнить последовательные даты Excel БЕЗ преобразования в даты типа мм/дд/гг?

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

УЧИТЫВАТЬ:

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

ЖЕЛАЕМЫЙ РЕЗУЛЬТАТ:

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

По сути, для всех экземпляров SerialDate.SerialTime, значения данных SUM, связанные с SerialDate.*, независимо от *.SerialTime для этой даты.

Хотя я вижу, как это сделать, создав дополнительный столбец дат, отформатированный, поскольку =TEXT(<DateField>,"mm/dd/yyyy")я ищу решение, которое позволит мне обрабатывать это «преобразование» в формуле, напримерSUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

Имеете ли вы смысл? Есть у кого-нибудь идеи?

Спасибо

решение1

Предположим, что серийные данные находятся в столбце A, а соответствующие данные — в столбце B. Следующая формула

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

отобразит сумму после первого целого значения времени в столбце C при вводе в C1 и расширении до столбца. Если это все, что вам нужно, вы закончили.

Однако, если вам нужно, чтобы все ваши суммы располагались одна под другой без пустых строк между ними, вам придется использоватьформула массиваи вставьте дополнительный столбец для справочных меток. В столбце C выберите ячейки в строках, содержащих данные (т. е. если ваши данные заполняют строки с 1 по 100, выберите C1 по C100), и вставьте следующую формулу в строку формул (не непосредственно в ячейку!):

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

Обратите внимание, что для того, чтобы это сработало, вам необходимо сохранить его как формулу массива.подтвердив ввод формулы с помощью Ctrl+Shift+Enter(не Enter– если вы сделали это правильно, формула будет отображаться в скобках). Вам также придется настроить область действия по мере необходимости, так как формула возвращает 0 для пустых ячеек. Наконец, обратите внимание, что формулы массивов становятся довольно медленными.ooo…o…o…w на больших наборах данных.

После этого вы можете добавить

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

в столбец D и вот вам:

Формула массива в действии

решение2

Если вы пытаетесь суммировать данные по дням (или получить дневной промежуточный итог), вы можете попробовать эту формулу массива. Введите это в строку формул и нажмите Ctrl+ Shift+ Enter.:

=SUM((INT(datetimes)=D3)*somedata)

Где

  • datetimesсодержит ваши серийные даты в формате Generalилиmm/dd/yyyy h:mm
  • somedataсодержит некоторые числа для сложения (B2:B25 в моем примере ниже)

введите описание изображения здесь

Excel видит даты как целые числа (последовательные значения), а время как десятичные дроби (доли 24 часов). Например, 06/01/2012 3:00:00 PMравно 41061.625(это то, что вы увидите, если измените формат ячейки на General). Целое число представляет дату, а 0.625представляет 3:00 pmили 15:00или 15/24. Чтобы извлечь сегмент ДАТА, вы можете использовать INT(A1).

решение3

Самый простой способ сделать это — использовать встроенные функции Excel (2007/2010) «Таблицы» и «Сводные таблицы».

  1. Создайте таблицу Excel на основе ваших данных.
  2. Создайте вспомогательный столбец для даты, используя функцию Excel "INT", которая сведет все значения к соответствующему дню (целая часть вашего поля даты/времени). Вы также можете легко создать вспомогательный столбец для значения времени вашего поля.
  3. Создайте сводную таблицу, используя вашу таблицу данных в качестве источника.
  4. Установите метки строк в качестве вспомогательных дат, а опорные значения — в качестве точек данных (и вы можете добавить поле «Время» в качестве меток столбцов).

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

Если вы используете Excel 2003, вы можете сделать то же самое, используя функцию «Список» (вместо таблиц).

Пример Excel 2010

Эксель 2010

Пример Excel 2003

Эксель 2003

Связанный контент