Формулы добавления

Добавить дни

Добавить рабочие дни до даты

= РАБДЕНЬ (начало_даты; дни; праздники)

Если вам нужно добавить или вычесть рабочие дни (рабочие дни) для даты, чтобы можно было рассчитать дату в будущем или прошлом, которая пропускает выходные дни (и праздники), вы можете использовать функцию РАБДЕНЬ. Праздники не являются обязательными.

Добавить рабочие дни до даты

В этом примере формула в D5:

=РАБДЕНЬ(B5;C5;$B$8:$B$10)

Это добавляет 7 дней во вторник, 22 декабря и возвращает вторник, 5 января 2016 года. По умолчанию функция РАБДЕНЬ исключает выходные дни (суббота и воскресенье). В этом случае мы также предоставили список из трех праздничных дней, все из которых относятся к расчетному диапазону дат, что означает, что выходные и праздничные дни будут исключены.

Чтобы вычесть выходные из даты (вместо добавления рабочих дней), просто используйте отрицательное значение для дней. Например, чтобы получить дату 3 рабочих дня до даты в A1, вы можете использовать:

= РАБДЕНЬ (A1; -3)

Добавить рабочие дни без выходных

= РАБДЕНЬ.МЕЖД (дата начала; дни; «0000000»; праздничные дни)

Добавить рабочие дни без выходных

Чтобы добавить или вычесть дни рабочих дней к дате, соблюдая праздники, но при условии 7-дневной рабочей недели, вы можете использовать функцию РАБДЕНЬ.МЕЖД. В этом примере формула в D6:

=РАБДЕНЬ.МЕЖД(B5;C5;»0000000″;B8:B10)

Эта формула добавляет 7 дней работы во вторник, 22 декабря. Три отпуска предоставляются с использованием названного диапазона «праздники» (B9: B11), а выходные устанавливаются с использованием специального синтаксиса «0000000», что означает, что все дни недели являются рабочими днями. Результат: Вт, 31 декабря 2015 года.

РАБДЕНЬ.МЕЖД может вычислять дату в будущем или прошлом, что касается праздников и выходных. Чтобы указать, какие дни считаются выходными, вы можете использовать специальный код (полный список кодов здесь) или использовать «маску» для обозначения выходных дней с единицами и нулями. Метод маски является более гибким, поскольку он позволяет назначать любой день недели в выходные (т. е. Нерабочий день). Например:

= РАБДЕНЬ.МЕЖД (A1;3; «0000000») // без выходных

= РАБДЕНЬ.МЕЖД (A1;3; «1000000») // выходной = Пн

= РАБДЕНЬ.МЕЖД (A1;3; «1100000») // выходные= понедельник + вторник

= РАБДЕНЬ.МЕЖД (A1;3; «1110000») // выходные = Пн + Вт + Ср

Аргумент выходных дней предоставляется в виде 7 символов, представляющих понедельник-воскресенье. Используйте один (1), чтобы указать выходные, и ноль (0), чтобы указать рабочий день.

Поскольку мы хотим, чтобы все дни недели считались рабочими днями, мы используем «0000000».

Добавить дни и исключить определенные дни недели

= РАБДЕНЬ.МЕЖД (дата; дни; «образец»)

Чтобы добавить дни в дату, исключая определенные дни (исключая вторники и четверги, только среду и т. д.), вы можете использовать функцию РАБДЕНЬ.МЕЖД со специальным кодом шаблона.

Добавить дни и исключить определенные дни недели

=РАБДЕНЬ.МЕЖД(B6;7;»0000011″)

Эта формула добавляет 7 дней к дате в B7, за исключением суббот и воскресений.

Функция РАБДЕНЬ.МЕЖД основана на функции РАБДЕНЬ, которая предназначена для добавления рабочих дней к дате. РАБДЕНЬ автоматически исключает субботу и воскресенье, а также может исключать список пользовательских праздников. РАБДЕНЬ.МЕЖД делает то же самое, но позволяет исключить любые дни недели, в дополнение к праздникам.

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

Итак, если вы хотите добавить 7 дней к дате в ячейке A1, вы можете написать такие формулы:

= РАБДЕНЬ.МЕЖД (A1;7; «0000011») // исключить Сб; Вс

= РАБДЕНЬ.МЕЖД (A1;7; «0010011») // исключить Сб; Вс; Ср.

= РАБДЕНЬ.МЕЖД (A1;7; «0101011») // исключаем Сб; Вс; Вт; Чт

Добавить дни до даты

= Дата + дни

Чтобы добавить заданное количество лет к дате, вы можете использовать функцию ДАТАМЕС.

Добавить дни до даты

= B5 + C5

Даты в Excel — это только серийные номера. Число 1 представляет 1 января 1900 года, число 1000 — 26 сентября 1902 года и так далее.

Когда у вас есть действительная дата в Excel, вы просто добавляете дни непосредственно. Значения дня могут быть как положительными, так и отрицательными.

Например, с датой в A1 вы можете добавить 100 дней следующим образом:

= A1 + 100

В показанном примере формула решается следующим образом:

= B5 + C5

= 36861 + 7

= 36868

Когда отформатировано как дата, 36868 — 8 декабря 2000 года.

Добавить рабочие дни на персонализированные выходные дни

= РАБДЕНЬ.МЕЖД (начало_даты; дни; выходные; праздники)

Чтобы добавить или вычесть дни рабочих дней для даты, чтобы можно было рассчитать дату в будущем или прошлом, которая пропускает выходные дни (настраиваемые) и праздники, можно использовать функцию РАБДЕНЬ.МЕЖД. Праздники и выходные дни являются необязательными.

Добавить рабочие дни на персонализированные выходные дни

В этом примере формула в D5:

=РАБДЕНЬ.МЕЖД(B5;C5;11;B8:B10)

Эта формула добавляет 7 дней работы во вторник, 22 декабря. Три отпуска предоставляются, а выходные устанавливаются с использованием 11 аргументов в выходные, что означает «только воскресенье».

По умолчанию функция РАБДЕНЬ.МЕЖД исключает выходные дни (суббота и воскресенье). В этом случае, однако, мы поставили 11 для этого аргумента, что означает «только воскресенье».

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

Добавить время

= Время + (часы / 24)

Чтобы добавить определенное количество часов за раз, вы можете добавить часы, деленные на 24, или использовать функцию ВРЕМЯ.

Добавить время

В показанном примере формула в D5:

= B5 + (C5 / 24)

Примечание. Убедитесь, что результаты форматируются как время.

Время в Excel составляет 24 часа. Один час времени равен 1/24, а 1 минута времени равна 1 / (24 * 60) = 1/1440.

В результате, если у вас есть десятичное значение в течение 6 часов и время в A1, вы можете добавить 6 часов времени к значению в A1 следующим образом:

= A1 + (6/24)

Вы также можете добавить временные значения с помощью функции ВРЕМЯ. Чтобы добавить 15 часов к времени в A1, используйте:

= A1 + ВРЕМЯ (6;0;0)

Функция ВРЕМЯ избавляет вас от необходимости запоминать формулу для преобразования десятичных часов в Excel. Однако обратите внимание на то, что функция ВРЕМЯ будет возвращаться к нулю, когда значения превысят 24 часа.

Например, обратите внимание, как два подхода возвращают разные результаты в течение 25 часов:

= ВРЕМЯ(25;0;0) = 0;041667 = 1:00 в тот же день (1 час)

= 25/24 = 1.041667 = 1:00 на следующий день (25 часов)

Функция ВРЕМЯ возвращает эквивалент 1 часа, а 25/24 возвращает полное значение.

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

Один из способов избежать этой проблемы — использовать формулу, подобную этой:

= ОСТАТ (время — (часы / 24); 1)

Здесь функция ОСТАТ заботится о негативной проблеме, используя функцию ОСТАТ для «переворачивания» отрицательных значений в требуемое положительное значение.

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

Добавить минуты к часу

= Время + (минуты / 1440)

Чтобы добавить определенное количество минут за раз, вы можете добавить минуты, деленные на 1440, или использовать функцию ВРЕМЯ.

Добавить минуты к часу

В показанном примере формула в D5:

= B5 + (C5 / 1440)

Примечание. Убедитесь, что результаты форматируются как время.

Время в Excel составляет 24 часа. Один час времени равен 1/24, а 1 минута времени равна 1 / (24 * 60) = 1/1440.

В результате, если у вас есть десятичное значение в течение 15 минут и время в A1, вы можете добавить 15 минут времени к значению в A1 следующим образом:

= A1 + (15/1440)

Вы также можете добавить временные значения с помощью функции ВРЕМЯ. Чтобы добавить 15 минут к времени в A1, используйте:

= A1 + ВРЕМЯ (0;15;0)

Функция ВРЕМЯ избавляет вас от необходимости запоминать формулу для преобразования десятичных минут в Excel. Однако обратите внимание на то, что функция ВРЕМЯ будет возвращаться к нулю, когда значения превысят 24 часа.

Например, 25 часов времени составляет 1500 минут. Обратите внимание на отличия:

= ВРЕМЯ (0;1500;0) = 0;041667 = 1:00 в тот же день

= 1500/1440 = 1.041667 = 1:00 на следующий день

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

Один из способов избежания данной проблемы — использовать формулу, подобную этой:

= ОСТАТ(время — (минуты / 1440); 1)

Здесь функция ОСТАТ заботится о негативной проблеме, используя функцию ОСТАТ для «переворачивания» отрицательных значений в требуемое положительное значение.

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

Добавить месяцы до даты

= ДАТАМЕС (дата; месяцы)

Чтобы добавить заданное количество лет к дате, вы можете использовать функцию ДАТАМЕС.

Добавить месяцы до даты

В показанном примере формула в D5:

= ДАТАМЕС(B5; C5)

Функция ДАТАМЕС полностью автоматическая. Просто введите действительную дату и количество месяцев, а ДАТАМЕС вернет новую дату. Чтобы вычесть месяцы из даты, поставьте отрицательное значение.

Чтобы двигаться вперед и назад в годах с определенной даты, вы можете умножить на 12 внутри ДАТАМЕС следующим образом:

= ДАТАМЕС (A1;12 * 10) // 10 лет

=ДАТАМЕС (A1;12 * 50) // 50 лет

Добавить года

= ДАТА(ГОД (дата) + годы; МЕСЯЦ(дата); ДЕНЬ(дата))

Чтобы добавить определенное количество лет к дате, вы можете использовать формулу, основанную на функции ДАТА, с помощью функций ГОД, МЕСЯЦ и ДЕНЬ.

Добавить года

В показанном примере формула в D5:

= ДАТА(ГОД (B5) + C5; МЕСЯЦ (B5); ДЕНЬ (B5))

Работая изнутри, функции ГОД, МЕСЯЦ и ДЕНЬ извлекают соответствующие компоненты даты:

= ГОД(B5) // 1960

= МЕСЯЦ (B5) // 3

= ДЕНЬ(B5) // 8

На внешнем уровне функция ДАТА просто пересобирает значения компонентов обратно в действительную дату Excel. Чтобы добавить годы к дате, нам просто нужно добавить значение в C5 к компоненту года до повторной сборки:

= ДАТА(ГОД (B5) + C5; МЕСЯЦ (B5); ДЕНЬ (B5))

Формула затем решается следующим образом:

= ДАТА(1960 + 10;3;8)

= ДАТА(1970;3;8)

= 8-мар-1970

Примечание. Если вам нужно добавить «четную» кратность 12 месяцев к дате (например, 12, 24, 36, 48 и т. д.), вы можете использовать гораздо более простую формулу, основанную на функции ДАТАМЕС.

Назначать баллы на основе позднего времени

= ЕСЛИ(время <ЗНАЧЕН («0:05»); 0; ЕСЛИ(время <ЗНАЧЕН(«0:15»); 1;
ЕСЛИ(время <ЗНАЧЕН(«0:30»); 2; ЕСЛИ(время <ЗНАЧЕН(«0:60»); 3;
ЕСЛИ(время <ЗНАЧЕН(«4:00»); 4; 5)))))

Назначать баллы на основе позднего времени

Чтобы назначить штрафные очки за опоздание, вы можете использовать вложенную формулу ЕСЛИ. В показанном примере формула в E5:

= ЕСЛИ (D5 <ЗНАЧЕН («0:05»); 0; ЕСЛИ (D5 < ЗНАЧЕН («0:15»); 1;
ЕСЛИ (D5 < ЗНАЧЕН («0:30»); 2; ЕСЛИ (D5 < ЗНАЧЕН («0:60»); 3;
ЕСЛИ (D5 < ЗНАЧЕН («4:00»); 4; 5)))))

Примечание. Разделители строк добавлены для удобства чтения.

Эта формула является классическим примером вложенной формулы ЕСЛИ, которая проверяет пороговые значения в порядке возрастания. Чтобы соответствовать графику, показанному в G4: G10, формула сначала проверяет по времени в D5, чтобы увидеть, не меньше ли она составляет 5 минут. Если это так, присваиваются нулевые точки:

ЕСЛИ(D5 <ЗНАЧЕН(«0:05»); 0;

Если результат логического теста выше ЛОЖЬ, формула проверяет, меньше ли D5, чем следующий порог, который составляет 15 минут:

ЕСЛИ(D5 < ЗНАЧЕН («0:15»); 1;

Такая же картина повторяется на каждом пороге. Поскольку тесты выполняются по порядку, от самого маленького до самого большого, нет необходимости в более сложных скобках.

Функция ЗНАЧЕН используется для того, чтобы значение времени обработки Excel обрабатывалось на каждом пороге как число вместо следующего.

Оцените статью
Добавить комментарий