Для решения некоторых задач при создании таблицы нужно в отдельной ячейке или внутри формулы указать количество дней в месяце, чтобы программа провела необходимые расчеты. В Экселе имеются инструменты, предназначенные для выполнения данной операции. Давайте рассмотрим различные способы применения данной возможности.
Вычислить количество дней в месяце в Экселе можно с помощью специальных операторов категории «Дата и время» . Чтобы выяснить, какой именно вариант лучше всего применить, нужно, прежде всего, установить цели выполнения операции. В зависимости от этого результат вычисления может выводиться в отдельный элемент на листе, а может использоваться и внутри другой формулы.
Наиболее простым способом решить данную задачу является комбинация операторов ДЕНЬ и КОНМЕСЯЦА .
Функция ДЕНЬ принадлежит к группе операторов «Дата и время» . Она указывает на конкретное число от 1 до 31 . В нашем случае задачей данного оператора будет указание последнего дня месяца при помощи встроенной функции в виде аргумента КОНМЕСЯЦА .
Синтаксис оператора ДЕНЬ следующий:
ДЕНЬ(дата_в_числовом_формате)
То есть, единственным аргументом данной функции является «Дата в числовом формате» . Его и будет задавать оператор КОНМЕСЯЦА . Нужно сказать, что дата в числовом формате отличается от привычного формата. Например, дата 04.05.2017 в числовом виде будет выглядеть, как 42859 . Поэтому этот формат Эксель использует только для внутренних операций. Он редко применяется для отображения в ячейках
Оператор КОНМЕСЯЦА предназначен для того, чтобы указывать порядковый номер последнего дня месяца, который находится на заданное количество месяцев вперед или назад от указанной даты. Синтаксис функции таков:
КОНМЕСЯЦА(нач_дата;число_месяцев)
Оператор «Начальная дата» содержит дату, от которой производится отсчет, или ссылку на ячейку, где она находится.
Оператор «Число месяцев» указывает на то количество месяцев, на которое следует производить отсчет от заданного числа.
Теперь давайте посмотрим, как это работает на конкретном примере. Для этого возьмем лист Excel, в одну из ячеек которого вписано определенное календарное число. Нужно при помощи указанного выше набора операторов определить, сколько дней в том месячном периоде, к которому это число относится.
Общая формула у нас приняла следующий вид:
ДЕНЬ(КОНМЕСЯЦА(B3;0))
В этой формуле переменным значением является только адрес ячейки (B3 ). Таким образом, если вы не хотите выполнять процедуру посредством Мастера функций , можно вставить данную формулу в любой элемент листа, просто заменив адрес ячейки, содержащей число, на тот, который актуален в конкретно вашем случае. Результат будет аналогичен.
Теперь давайте рассмотрим другую задачу. Требуется, чтобы количество дней выводилось не по заданному календарному числу, а по текущему. К тому же смена периодов производилась бы автоматически без участия пользователя. Хоть это и кажется странным, но данная задача легче предыдущей. Для её решения даже открывать Мастер функций не нужно, так как формула, выполняющая данную операцию, не содержит переменных значений или ссылок на ячейки. Вы просто можете вбить в ту ячейку листа, где хотите, чтобы отображался результат, следующую формулу без изменений:
ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))
Встроенная функция СЕГОДНЯ, которую мы применили в данном случае, отображает сегодняшнее число и не имеет аргументов. Таким образом, у вас в ячейке постоянно будет отображаться количество дней в текущем месяце.
В примерах выше мы показали, как выполнять вычисление количества дней в месяце по указанному календарному числу или автоматически по текущему месяцу с выводом результата в отдельную ячейку. Но нахождение данного значения может понадобиться и для вычисления других показателей. При этом расчет количества дней будет производиться внутри сложной формулы и не станет выводиться в отдельной ячейке. Посмотрим, как это сделать на примере.
Нам нужно сделать так, чтобы в ячейке отображалось то количество дней, которое осталось до конца текущего месяца. Как и в предыдущем способе, данный вариант действий не требует открытия Мастера функций . Вы можете просто вбить в ячейку следующее выражение:
ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))-ДЕНЬ(СЕГОДНЯ())
После этого в указанной ячейке будет выводиться число дней до конца месяца. Каждый день результат будет автоматически обновляться, а с начала нового периода отсчет начнется заново. Получается своеобразный таймер обратного отсчета.
Как видим, указанная формула состоит из двух частей. Первая из них представляет собой уже знакомое нам выражение расчета числа дней в месяце:
ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))
А вот во второй части производится вычитание из этого показателя сегодняшнего числа:
ДЕНЬ(СЕГОДНЯ())
Таким образом, при выполнении данного расчета формула вычисления количества дней является составной частью более сложной формулы.
Но, к сожалению, у версий программы ранее Excel 2007 отсутствует оператор КОНМЕСЯЦА . Как же быть тем пользователям, которые применяют старые версии приложения? Для них такая возможность существует посредством другой формулы, которая более массивна, чем описываемая выше. Посмотрим, как рассчитать количество дней в месяце по заданному календарному числу с помощью этого варианта.
Открывшееся окно имеет три поля. В поле «День» можно сразу ввести число «1» . Это будет неизменным действием для любой ситуации. А вот двумя другими полями придется заняться основательно.
Устанавливаем курсор в поле «Год» . Далее переходим к выбору операторов через знакомый нам треугольник.
ДЕНЬ(ДАТА(ГОД(D3);МЕСЯЦ(D3)+1;1)-1)
Секрет этой формулы прост. Мы с помощью неё определяем дату первого дня следующего периода, а потом отнимаем от неё один день, получая количество дней в указанном месяце. Переменной величиной в этой формуле является ссылка на ячейку D3 в двух местах. Если её заменить на адрес той ячейки, в которой находится дата в вашем конкретном случае, то можно просто вбить данное выражение в любой элемент листа без помощи Мастера функций .
Как видим, существует несколько вариантов узнать количество дней в месяце в Excel. Каким именно из них воспользоваться, зависит от конечной цели пользователя, а также от того, какой версией программы он пользуется.
Калькулятор дат предназначен для вычисления количества дней между датами, а также для нахождения даты путем прибавления или вычитания определенного количества дней к известной дате.
Для того, чтобы узнать какое число будет через определенное количество дней, воспользуйтесь этой опцией. Введите начальную дату и количество дней, которое нужно к ней прибавить. Для вычитания используйте значение с минусом. В калькуляторе также есть опция для прибавления только рабочих дней.
Этот метод расчета ответит на вопрос "сколько дней прошло с даты". Введите начальную дату и конечную дату и нажмите кнопку "рассчитать". Калькулятор покажет, сколько дней между введенными датами. Отдельно калькулятор покажет количество рабочих дней.
С помощью этой опции можно вычислить сколько дней осталось до определенного события, например до дня рождения или праздника. Для этого в поле начальной даты укажите сегодняшнее число, а в поле конечной даты - дату события.
Калькулятор может вычислять, прибавлять и вычитать как календарные дни, так и рабочие. Официальными нерабочими праздничными днями являются:
Если праздничный день выпал на субботу или воскресенье, его переносят на ближайший рабочий день. Но иногда выходные переносят совсем в другое место календаря. Например, субботу и воскресенье, выпавшие на новогодние праздники, могут перенести на май, чтобы продлить майские праздники.
Калькулятор при расчете дней учитывает как официальные праздничные даты, так и все переносы.
Примечание : данный пост является переводом статьи cmcenroe.me/2014/12/05/days-in-month-formula.html (Часть I ), а также авторским к нему дополнением (Часть II ). Не следует относиться к материалу серьёзно, а скорее как к разминке для ума, требующей не более чем школьных знаний арифметики и не имеющей практического применения. Всем приятного чтения!
Формализуя Другими словами, необходимо найти функцию f
, такую, что значение f(x)
для каждого месяца x
, представленного числом от 1 до 12, равняется количеству дней в этом месяце. Таблица значений аргумента и функции :
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Ответ
Целочисленное деление
это оператор, применяемый во многих языках программирования при делении двух целых чисел и отбрасывающий от частного дробную часть. Я буду изображать его как . Например:
Остаток от деления
это оператор, находящий остаток от деления. Во многих языках программирования применяется символ %
, я же буду использовать конструкции вида , например:
Замечу, что остаток от деления имеет равный с делением приоритет.
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
⌊ x ⁄ 8 ⌋ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
2 mod x | 0 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 29 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Function f(x) { return 28 + (x + Math.floor(x/8)) % 2 + 2 % x + 2 * Math.floor(1/x); }
Ответ
Для приведения этого выражения в алгебраическое, необходимо применить к результату выражения инъекцию вида:
Что позволит получить 1 при делении без остатка и 0 при делении с остатком, чтобы использовать её в формуле определения количества дней в месяце.
В качестве функции g"
можно использовать 1 минус остаток от деления для :
x | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
g"(x) | Infinity | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
x | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
g"(x) | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Таким образом выражение запишем как:
Применяя этот подход получим следующую функцию g(y)
, значением которой будет 1, если год високосный, или 0 в обратном случае:
y | 1990 | 1991 | 1992 | 1993 | 1994 | 1995 | 1996 | 1997 | 1998 | 1999 | 2000 |
---|---|---|---|---|---|---|---|---|---|---|---|
g(y) | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
y | 2000 | 2100 | 2200 | 2300 | 2400 | 2500 | 2600 | 2700 | 2800 | 2900 | 3000 |
g(y) | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
Напоминаю, что в рамках принятой договорённости оператор получения остатка от деления может быть изображен как mod , так и ⌊⌋.
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x, 2000) | 31 | 29 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
f(x, 2001) | 30 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 30 |
Для исправления этого досадного недоразумения добавим к январю 1 день уже известной нам формулой :
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x, 2000) | 31 | 29 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
f(x, 2001) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 30 |
Function f(x, y) { return 28 + ((x + Math.floor(x / 8)) % 2) + 2 % x + Math.floor((1 + (1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1))) / x) + Math.floor(1/x) - Math.floor(((1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1)))/x); }
Пример на C# ideone.com/fANutz .
1
. Я не умею пользоваться подобной мнемоникой, поэтому подсмотрел табличку в интернете.
2
. «Основы», или «Правило С Многими Исключениями», как и большинство правил.
3
. Изначально в римском календаре февраль был последним месяцем года, поэтому есть логика в том, что он короче всех остальных. Также есть логика в добавлении или удалении дня именно в конце года, поэтому его длина является переменной.
Upd. 1:
Альтернативный перевод первой части в
Бесплатный онлайн калькулятор Контур.Бухгалтерии вам поможет и подскажет, какое количество дней прошло между двумя заданными датами. Кроме того, если у вас возникла необходимость, вы можете посчитать сколько календарных, выходных или рабочих дней (часов) содержит указанный период года или нескольких лет.
Вы просто задаете конкретный день начала и конца и через доли секунд получаете расчет. Все данные онлайн-калькулятор считает самостоятельно. Если вы изменяете исходные дни недели, результат автоматически пересчитывается, с учетом високосного года.
Важно: нельзя брать из расчетов за прошлые года показатели рабочих дней/часов за месяц и предоставлять в качестве расчетов — данные будут различаться. Поэтому, лучше воспользуйтесь калькулятором.
Итак, порядок действий:
Вы — бухгалтер. Руководитель попросил вас в ближайшие пару минут предоставить данные по количеству рабочих часов, которые должны отработать все сотрудники компании в феврале. Количество работников вы можете легко определить — у вас перед глазами цифры. А вот количество часов нужно считать....А сколько там в феврале дней? А год-то високосный? А какие дни были выходными? А как определить количество дней праздников?
Решение: просто воспользуйтесь нашим виджетом. Всю информацию вы получите автоматически, вам не нужны настольные календари и калькуляторы.
Хотите вести бухучет, отправлять отчетность и делать расчеты в удобном и простом веб-сервисе? Попробуйте бесплатно 14 дней Контур.Бухгалтерии! Мы быстро вас научим, как сервисом пользоваться и ответим на все вопросы!
Примечание : данный пост является переводом статьи cmcenroe.me/2014/12/05/days-in-month-formula.html (Часть I ), а также авторским к нему дополнением (Часть II ). Не следует относиться к материалу серьёзно, а скорее как к разминке для ума, требующей не более чем школьных знаний арифметики и не имеющей практического применения. Всем приятного чтения!
Формализуя Другими словами, необходимо найти функцию f
, такую, что значение f(x)
для каждого месяца x
, представленного числом от 1 до 12, равняется количеству дней в этом месяце. Таблица значений аргумента и функции :
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Ответ
Целочисленное деление
это оператор, применяемый во многих языках программирования при делении двух целых чисел и отбрасывающий от частного дробную часть. Я буду изображать его как . Например:
Остаток от деления
это оператор, находящий остаток от деления. Во многих языках программирования применяется символ %
, я же буду использовать конструкции вида , например:
Замечу, что остаток от деления имеет равный с делением приоритет.
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
⌊ x ⁄ 8 ⌋ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
2 mod x | 0 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 29 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Function f(x) { return 28 + (x + Math.floor(x/8)) % 2 + 2 % x + 2 * Math.floor(1/x); }
Ответ
Для приведения этого выражения в алгебраическое, необходимо применить к результату выражения инъекцию вида:
Что позволит получить 1 при делении без остатка и 0 при делении с остатком, чтобы использовать её в формуле определения количества дней в месяце.
В качестве функции g"
можно использовать 1 минус остаток от деления для :
x | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
g"(x) | Infinity | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
x | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
g"(x) | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Таким образом выражение запишем как:
Применяя этот подход получим следующую функцию g(y)
, значением которой будет 1, если год високосный, или 0 в обратном случае:
y | 1990 | 1991 | 1992 | 1993 | 1994 | 1995 | 1996 | 1997 | 1998 | 1999 | 2000 |
---|---|---|---|---|---|---|---|---|---|---|---|
g(y) | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
y | 2000 | 2100 | 2200 | 2300 | 2400 | 2500 | 2600 | 2700 | 2800 | 2900 | 3000 |
g(y) | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
Напоминаю, что в рамках принятой договорённости оператор получения остатка от деления может быть изображен как mod , так и ⌊⌋.
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x, 2000) | 31 | 29 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
f(x, 2001) | 30 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 30 |
Для исправления этого досадного недоразумения добавим к январю 1 день уже известной нам формулой :
x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
f(x, 2000) | 31 | 29 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
f(x, 2001) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 30 |
Function f(x, y) { return 28 + ((x + Math.floor(x / 8)) % 2) + 2 % x + Math.floor((1 + (1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1))) / x) + Math.floor(1/x) - Math.floor(((1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1)))/x); }
Пример на C# ideone.com/fANutz .
1
. Я не умею пользоваться подобной мнемоникой, поэтому подсмотрел табличку в интернете.
2
. «Основы», или «Правило С Многими Исключениями», как и большинство правил.
3
. Изначально в римском календаре февраль был последним месяцем года, поэтому есть логика в том, что он короче всех остальных. Также есть логика в добавлении или удалении дня именно в конце года, поэтому его длина является переменной.
Upd. 1:
Альтернативный перевод первой части в