☰

ПОНИМАНИЕ ВРЕМЕНИ/ДАТЫ В EXCEL

tag-icon • tag-icon microsoft-excel google-spreadsheets
ПОНИМАНИЕ ВРЕМЕНИ/ДАТЫ В EXCEL

У меня есть простая таблица данных в Excel, и мне нужна формула для нахождения общей суммы на сегодня. Так что это будет сумма всех сумм, которые попадают между 00:00 и 23:59 для даты TODAY(). Как мне это сделать?

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

Пример данных:

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

решение1

Первое, что нужно выяснить, это являются ли ваши даты текстом или числами, отформатированными Excel для отображения в виде дат. Это очень важное различие, и вам нужно последнее, поскольку оно позволяет использовать больше встроенных формул Excel. Вот некоторые возможные тесты:

  • =ЕСТЕКСТ(A2)
  • =ЕЧИСЛО(A2)
  • Измените форматирование ячеек на общее и посмотрите, изменится ли отображаемое содержимое.

Для этого решения ваши даты будут считаться числами. Если это не так, есть много вопросов/ответов о том, как преобразовать текстовую дату в числовой/формат Excel.

ПОНИМАНИЕ ВРЕМЕНИ/ДАТЫ В EXCEL

Excel хранит дату как целое число. Это количество дней с 0 января 1900 года. Таким образом, 1 — это 1 января 1900 года. (Я думаю, что на make базовый год — 1905, но я могу ошибаться). Таким образом, все даты в Excel — это просто целые числа с некоторым специальным форматированием, применяемым для их отображения в том виде, к которому мы привыкли. Время, с другой стороны, хранится как % дня или, если вы предпочитаете, десятичная часть дня. 0,5 представляет собой половину дня или обед. Поэтому, если вам нужно просто найти дату из даты со смешанным временем, вам нужно отбросить только целую часть. И наоборот, если вам нужно только время, вам нужно отбросить только десятичную часть.

Вариант 1 СУММПРОИЗВ

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

=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)

Примечание TODAY()— это изменчивая функция. Это означает, что она пересчитывается каждый раз, когда что-то на листе изменяется, даже если это не влияет на функцию. Неизменяемая функция (большинство обычных функций) пересчитывается только тогда, когда происходит изменение, которое влияет на них. В результате, если вы копируете изменчивую функцию во множество ячеек, вы можете затормозить свой компьютер каждый раз, когда что-то на листе изменяется/вводится.

Вариант 2 - СУММЕСЛИМН

SUMIFSэто обычная формула, в которой вы можете использовать полные ссылки на столбцы без беспокойства, и мой предпочтительный вариант того, как справиться с этой ситуацией. Эта формула имеет вид:

SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)

Итак, в вашем случае вы хотите проверить начало и конец дня, поэтому ваша формула должна будет выполнить условные проверки и в итоге должна выглядеть примерно так:

=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)

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