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

Question

У меня есть таблица, которая содержит ряд значений, представляющих серийные даты 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>

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

Спасибо

Answer 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 для пустых ячеек. Наконец, обратите внимание, что формулы массива получают довольно slo.ooo… o… o… w для больших наборов данных.

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

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

в столбец D и вот вы:

Answer 2

Простейший способ сделать это - использовать встроенные в Excel (2007/2010) функции Table и Pivot Table.

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

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

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

Пример Excel 2010

Пример Excel 2003

Answer 3

Если вы пытаетесь суммировать данные по дням (или получать ежедневный промежуточный итог), вы можете попробовать эту формулу массива. Введите это в строке формул и затем нажмите 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 . Чтобы извлечь сегмент DATE, вы можете использовать INT(A1) .