Визначення кількості днів у місяці в Microsoft Excel
Для вирішення деяких завдань при створенні таблиці потрібно в окремій клітинці або всередині формули вказати кількість днів у місяці, щоб програма провела необхідні розрахунки. У Ексель є інструменти, призначені для виконання даної операції. Давайте розглянемо різні способи застосування даної можливості.
зміст
Обчислення кількості днів
Обчислити кількість днів у місяці в Ексель можна за допомогою спеціальних операторів категорії «Дата і час». Щоб з'ясувати, який саме варіант краще всього застосувати, потрібно, перш за все, встановити цілі виконання операції. Залежно від цього результат обчислення може виводитися в окремий елемент на аркуші, а може використовуватися і всередині іншої формули.
Спосіб 1: комбінація операторів ДЕНЬ і КОНМЕСЯЦА
Найбільш простим способом вирішити це завдання є комбінація операторів ДЕНЬ і КОНМЕСЯЦА.
Функція ДЕНЬ належить до групи операторів «Дата і час». Вона вказує на конкретне число від 1 до 31. У нашому випадку завданням даного оператора буде вказівка останнього дня місяця за допомогою вбудованої функції у вигляді аргументу КОНМЕСЯЦА.
Синтаксис оператора ДЕНЬ наступний:
=ДЕНЬ(дата_в_числовом_формате)
Тобто, єдиним аргументом даної функції є «Дата в числовому форматі». Його і буде задавати оператор КОНМЕСЯЦА. Потрібно сказати, що дата в числовому форматі відрізняється від звичного формату. Наприклад, дата 04.05.2017 в числовому вигляді буде виглядати, як 42859. Тому цей формат Ексель використовує тільки для внутрішніх операцій. Він рідко застосовується для відображення в осередках
Оператор КОНМЕСЯЦА призначений для того, щоб вказувати порядковий номер останнього дня місяця, який знаходиться на задану кількість місяців вперед або назад від зазначеної дати. Синтаксис функції такий:
=КОНМЕСЯЦА(нач_дата;число_месяцев)
Оператор «Початкова дата» містить дату, від якої починається відлік, або посилання на осередок, де вона знаходиться.
Оператор «Число місяців» вказує на ту кількість місяців, на яке слід проводити відлік від заданого числа.
Тепер давайте подивимося, як це працює на конкретному прикладі. Для цього візьмемо лист Excel, в одну з комірок якого вписано певну календарне число. Потрібно за допомогою вказаного вище набору операторів визначити, скільки днів в тому місячному періоді, до якого це число відноситься.
- Виділяємо осередок на аркуші, в яку буде виводитися результат. Клацаємо по кнопці «Вставити функцію». Ця кнопка розміщена зліва від рядка формул.
- Запускається вікно Майстра функцій. Переходимо в розділ «Дата і час». Знаходимо і виділяємо запис «ДЕНЬ». Виробляємо клацання по кнопці «OK».
- Відкривається вікно аргументів оператора ДЕНЬ. Як бачимо, воно містить всього одне поле - «Дата в числовому форматі». Зазвичай тут встановлюють число або посилання на осередок, його містить, але у нас в даному полі буде знаходитися функція КОНМЕСЯЦА. Тому встановлюємо курсор в поле, а потім натискаємо на піктограму у вигляді трикутника зліва від рядка формул. Відкривається список недавно використаних операторів. Якщо ви в ньому виявите найменування «КОНМЕСЯЦА», то відразу клікайте по ньому для переходу у вікно аргументів цієї функції. Якщо ж цього найменування ви не знайдете, то в такому випадку клацайте по позиції «Інші функції ...».
- Знову запускається Майстер функцій і знову ми подорожуємо у ту ж групу операторів. Але на цей раз шукаємо найменування «КОНМЕСЯЦА». Після виділення зазначеного назви клацаємо по кнопці «OK».
- Виробляється запуск вікна аргументів оператора КОНМЕСЯЦА.
У першому його поле, що називається «Початкова дата», потрібно встановити то число, яке знаходиться у нас в окремій клітинці. Саме кількість днів в той період, до якого воно відноситься, ми і будемо визначати. Для того, щоб встановити адресу осередки, ставимо курсор в поле, а потім просто натискаємо по ній на аркуші лівою кнопкою миші. Координати тут же будуть відображені в віконці.
В поле «Кількість місяців» ставимо значення «0», так як нам потрібно визначити тривалість саме того періоду, до якого відноситься вказане число.
Після цього клацаємо по кнопці «OK».
- Як бачимо, після виконання останньої дії кількість днів у місяці, до якого належить вибране число, відобразилася в осередку на аркуші.
Загальна формула у нас прийняла такий вигляд:
=ДЕНЬ(КОНМЕСЯЦА(B3;0))
У цій формулі змінним значенням є тільки адреса комірки (B3). Таким чином, якщо ви не хочете виконувати процедуру за допомогою Майстра функцій, можна вставити цю формулу в будь-який елемент листа, просто замінивши адресу комірки, що містить число, на той, який актуальний в конкретно вашому випадку. Результат буде аналогічний.
урок: Майстер функцій в Ексель
Спосіб 2: автоматичне визначення кількості днів
Тепер давайте розглянемо іншу задачу. Потрібно, щоб кількість днів виводилося не по заданому календарного числа, а за поточним. До того ж зміна періодів проводилася б автоматично без участі користувача. Хоч це і здається дивним, але дана задача легше попередньої. Для її вирішення навіть відкривати Майстер функцій не потрібно, так як формула, яка виконує дану операцію, не містить змінних значень або посилань на осередки. Ви просто можете вбити в ту клітинку аркуша, де хочете, щоб з'явився результат, наступну формулу без змін:
=ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))
Вбудована функція СЬОГОДНІ, яку ми застосували в даному випадку, відображає сьогоднішнє число і не має аргументів. Таким чином, у вас в осередку постійно буде відображатися кількість днів у поточному місяці.
Спосіб 3: обчислення кількості днів для використання в складних формулах
У прикладах вище ми показали, як виконувати обчислення кількості днів у місяці за вказаною календарного числа або автоматично по поточному місяцю з висновком результату в окрему клітинку. Але знаходження даного значення може знадобитися і для обчислення інших показників. При цьому розрахунок кількості днів буде здійснюватись всередині складної формули і не стане виводитися в окремій клітинці. Подивимося, як це зробити на прикладі.
Нам потрібно зробити так, щоб в осередку відображалося то кількість днів, що залишився до кінця поточного місяця. Як і в попередньому способі, даний варіант дій не вимагає відкриття Майстра функцій. Ви можете просто вбити в клітинку такий вираз:
=ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))-ДЕНЬ(СЕГОДНЯ())
Після цього в зазначеній комірці буде виводитися число днів до кінця місяця. Кожен день результат буде автоматично оновлюватися, а з початку нового періоду відлік почнеться заново. Виходить своєрідний таймер зворотного відліку.
Як бачимо, зазначена формула складається з двох частин. Перша з них являє собою вже знайоме нам вираз розрахунку числа днів у місяці:
=ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))
А ось у другій частині проводиться віднімання з цього показника сьогоднішнього числа:
-ДЕНЬ(СЕГОДНЯ())
Таким чином, при виконанні даного розрахунку формула обчислення кількості днів є складовою частиною більш складної формули.
Спосіб 4: альтернативна формула
Але, на жаль, у версій програми раніше Excel 2007 відсутня оператор КОНМЕСЯЦА. Як же бути тим користувачам, які застосовують старі версії додатка? Для них така можливість існує за допомогою іншої формули, яка більш масивна, ніж описувана вище. Подивимося, як розрахувати кількість днів у місяці за заданим календарного числа за допомогою цього варіанта.
- Виділяємо осередок для виведення результату і переходимо у вікно аргументів оператора ДЕНЬ вже звичним для нас способом. Встановлюємо курсор в єдине поле цього вікна і натискаємо по переверненому трикутнику зліва від рядка формул. Переходимо в розділ «Інші функції ...».
- У вікні Майстра функцій в групі «Дата і час» виділяємо найменування «ДАТА» і тиснемо на кнопку «OK».
- Запускається віконце оператора ДАТА. Ця функція перетворює дату зі звичайного формату в числове значення, яке і повинен буде потім обробити оператор ДЕНЬ.
Вікно має три поля. В поле «День» можна відразу ввести число «1». Це буде незмінним дією для будь-якої ситуації. А ось двома іншими полями доведеться зайнятися грунтовно.
Встановлюємо курсор в поле «Рік». Далі переходимо до вибору операторів через знайомий нам трикутник.
- Все в тій же категорії Майстра функцій виділяємо найменування «РІК» і клацаємо по кнопці «OK».
- Запускається вікно аргументів оператора РІК. Він визначає рік по зазначеному числу. В єдиному полі вікна «Дата в числовому форматі» вказуємо посилання на клітинку, яка містить вихідну дату, для якої потрібно визначити кількість днів. Після цього не поспішаємо клацати по кнопці «OK», а натискаємо по найменуванню «ДАТА» в рядку формул.
- Потім ми знову повертається у вікно аргументів ДАТА. Встановлюємо курсор в поле «Місяць» і переходимо до вибору функцій.
- У Майстрі функцій клацаємо по найменуванню «МІСЯЦЬ» і тиснемо на кнопку «OK».
- Запускається вікно аргументів функції МІСЯЦЬ. Її завдання схожі з попереднім оператором, тільки вона виводить значення номера місяця. В єдине поле даного вікна встановлюємо ту ж саму посилання на вихідне число. Потім в рядку формул клацаємо по найменуванню «ДЕНЬ».
- Повертаємося у вікно аргументів ДЕНЬ. Тут нам належить зробити всього один невеликий штрих. В єдине поле вікна, в якому вже перебувають дані, додаємо в кінець формули вираз «-1» без лапок, а також ставимо «+1» після оператора МІСЯЦЬ. Після цього натискаємо на кнопку «OK».
- Як бачимо, в попередньо виділеної осередку відобразилося кількість днів у місяці, до якого належить вказане число. Загальна формула має такий вигляд:
=ДЕНЬ(ДАТА(ГОД(D3);МЕСЯЦ(D3)+1;1)-1)
Секрет цієї формули простий. Ми за допомогою неї визначаємо дату першого дня наступного періоду, а потім забираємо від неї один день, отримуючи кількість днів в зазначеному місяці. Змінною величиною в цій формулі є посилання на осередок D3 в двох місцях. Якщо її замінити на адресу тієї комірки, в якій знаходиться дата в вашому конкретному випадку, то можна просто вбити цей вислів в будь-який елемент листа без допомоги Майстра функцій.
урок: Функції дати і часу в Excel
Як бачимо, існує кілька варіантів дізнатися кількість днів у місяці в Excel. Яким саме з них скористатися, залежить від кінцевої мети користувача, а також від того, якою версією програми він користується.