☰

Формула Excel для расчета даты на основе двух ячеек и как скопировать ее в другие ячейки?

tag-icon • tag-icon microsoft-excel worksheet-function date
Формула Excel для расчета даты на основе двух ячеек и как скопировать ее в другие ячейки?

Начинающий пользователь Excel здесь. Вот упрощенная версия моей таблицы:

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

Я хочу создать формулы для расчета даты начала и даты окончания на основе значений в столбцах eday и duration, а также begindate:

start date = begindate + eday
end date = start date + duration

Итак, если я применю формулу для приведенного выше примера, я ожидаю следующий результат:

start date  end date    eday    duration
02/11/2020  02/11/2020  -21     4
03/15/2020  03/19/2020   12     0

begindate 03/03/2020всегда будет одинаковым.

Мне удалось заставить формулу работать, но когда я использую маркер заполнения, формула копируется неправильно.

Пожалуйста, помогите новичку с этими вопросами:

  1. Где лучше всего хранить начальную дату «03/03/2020» — на отдельном листе? жестко закодировать? в другом месте?

  2. Какова формула для даты начала и даты окончания?

  3. Как скопировать формулу во все строки столбца?

Большое спасибо.

решение1

Первое, что мне нравится помнить о датах и ​​времени в Excel, это то, что это на самом деле всего лишь числа, которые отформатированы так, чтобы выглядеть как даты/время с помощью числового форматирования. Сегодняшняя дата (07-сен-2019) — 43715, а текущее время (12:20AM) — 0.14302083...

Существуют функции рабочего листа, которые могут получить для вас магическое число (по сути, количество дней с 01.01.1900), например DATE(), TODAY()или NOW().

С этой целью, предполагая, что вашэдэйстолбец Cи ваши данные начинаются в строке 2, вы можете ввести свойДата началаформула в A2как =DATE(2020,3,3)+$C2и вашДата окончанияформула в B2виде =$A2+$D2. Это самый простой подход.

Обратите внимание, что я использовал абсолютные ссылки на столбцы ( $C, $A, $D), чтобы заставить Excel всегда искать в столбцах C, Aи D, независимо от того, куда я копирую/вставляю или заполняю, и я использовал относительные ссылки на строки ( 2), чтобы при копировании/вставке или заполнении вниз номер строки изменялся как часть заполнения (т. е. в строке 3формула будет выглядеть так =$A3+$D3). Иногда это нужно, иногда нет, поэтому вам следует поэкспериментировать с $и no $, чтобы увидеть, что это дает.

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

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

  • Вы можете использоватьИмя Менеджерадля хранения вашей фиксированной, постоянной даты, создав новое имя с именем BeginDateи установив его значение на =DATE(2020,3,3). Теперь вы можете обновить формулу начальной даты на , A2и =BeginDate+$C2это фиксированное значение будет использоваться везде.

  • Вы можете отформатировать диапазон из четырех столбцов какТаблица с заголовками, что позволит вам использовать эти имена заголовков вместо ссылок на столбцы. Если вы отформатируете диапазон как таблицу, то в первой строке данных вашегоДата началастолбец (который был A2) вы можете ввести =BeginDate+[eday]и в вашемДата окончаниястолбец (который был B2) вы можете ввести =[start date]+[duration]. Поскольку это таблица, обе формулы должны заполняться автоматически, по мере роста таблицы. Теперь примените простой числовой форматКороткое свиданиена вашДата началаиДата окончанияколонны.

  • Вы можете остановить отображение в таблице рассчитанных дат, если вы не ввели никаких значений, edayизменив durationформулу вДата началак более сложным:

    =IF([eday]="", "", BeginDate+[eday])

    ...и изменение формулы вДата окончанияк:

    =IF(OR([start date]="",[duration]=""), "", [start date]+[duration])

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