Визначення кількості днів у місяці в Microsoft Excel

Зміст

• Обчислення кількості днів
  • Спосіб 1: комбінація операторів ДЕНЬ і КОНМЕСЯЦА
  • Спосіб 2: автоматичне визначення кількості днів
  • Спосіб 3: обчислення кількості днів для використання в складних формулах
  • Спосіб 4: альтернативна формула
• Питання та відповіді

Для вирішення деяких завдань при створенні таблиці потрібно в окремій комірці або всередині формули вказати кількість днів у місяці, щоб програма провела необхідні розрахунки. В Ексель є інструменти, призначені для виконання даної операції. Давайте розглянемо різні способи застосування даної можливості.

Обчислення кількості днів

Обчислити кількість днів у місяці в Ексель можна за допомогою спеціальних операторів категорії " Дата і час» . Щоб з'ясувати, який саме варіант найкраще застосувати, потрібно, перш за все, встановити цілі виконання операції. Залежно від цього результат обчислення може виводитися в окремий елемент на аркуші, а може використовуватися і всередині іншої формули.

Спосіб 1: комбінація операторів ДЕНЬ і КОНМЕСЯЦА

Найбільш простим способом вирішити це завдання є комбінація операторів ДЕНЬ і КОНМЕСЯЦА .

Функція ДЕНЬ належить до групи операторів " Дата і час» . Вона вказує на конкретне число від 1 до 31 . У нашому випадку завданням даного оператора буде вказівка останнього дня місяця за допомогою вбудованої функції у вигляді аргументу КОНМЕСЯЦА .

Синтаксис оператора ДЕНЬ наступний:

=ДЕНЬ (дата_в_числовом_форматі)

Тобто, єдиним аргументом даної функції є " Дата в числовому форматі» . Його і буде задавати оператор КОНМЕСЯЦА . Потрібно сказати, що дата в числовому форматі відрізняється від звичного формату. Наприклад, дата 04.05.2017 в числовому вигляді буде виглядати, як 42859 . Тому цей формат Ексель використовує тільки для внутрішніх операцій. Він рідко застосовується для відображення в осередках

Оператор КОНМЕСЯЦА призначений для того, щоб вказувати порядковий номер останнього дня місяця, який знаходиться на задану кількість місяців вперед або назад від зазначеної дати. Синтаксис функції такий:

=КОНМЕСЯЦА (нач_дата;число_месяцев)

Оператор " Початкова дата» містить дату, від якої проводиться відлік, або посилання на клітинку, де вона знаходиться.

Оператор " число місяців» вказує на ту кількість місяців, на яке слід проводити відлік від заданого числа.

Тепер давайте подивимося, як це працює на конкретному прикладі. Для цього візьмемо лист Excel, в одну з осередків якого вписано певне календарне число. Потрібно за допомогою зазначеного вище набору операторів визначити, скільки днів в тому місячному періоді, до якого це число відноситься.

1. Виділяємо осередок на аркуші, в яку буде виводитися результат. Клацаємо по кнопці " Вставити функцію» . Ця кнопка розміщена зліва від рядка формул.

2. Запускається вікно майстри функцій . Переходимо в розділ " Дата і час» . Знаходимо і виділяємо запис »ДЕНЬ" . Виробляємо клацання по кнопці «OK» .

3. Відкривається вікно аргументів оператора ДЕНЬ . Як бачимо, воно містить всього одне поле – " Дата в числовому форматі» . Зазвичай тут встановлюють число або посилання на клітинку, його містить, але у нас в даному полі буде знаходитися функція КОНМЕСЯЦА . Тому встановлюємо курсор в поле, а потім натискаємо по піктограмі у вигляді трикутника зліва від рядка формул. Відкривається список нещодавно використаних операторів. Якщо ви в ньому виявите найменування " КОНМЕСЯЦА» , то відразу клікайте по ньому для переходу у вікно аргументів даної функції. Якщо ж даного найменування ви не знайдете, то в такому випадку клацайте по позиції " інші функції...» .

4. Запускається знову Майстер функцій і знову ми переміщаємося в ту ж групу операторів. Але на цей раз шукаємо найменування " КОНМЕСЯЦА» . Після виділення зазначеної назви клацаємо по кнопці «OK» .

5. Проводиться запуск вікна аргументів оператора КОНМЕСЯЦА .

   У першому його полі, що називається " Початкова дата» , потрібно встановити те число, яке знаходиться у нас в окремій комірці. Саме кількість днів в тому періоді, до якого воно відноситься, ми і будемо визначати. Для того, щоб встановити адресу комірки, ставимо курсор в поле, а потім просто натискаємо по ній на аркуші лівою кнопкою миші. Координати тут же будуть відображені у віконці.

   У полі " число місяців» ставимо значення «0» , так як нам потрібно визначити тривалість саме того періоду, до якого відноситься вказане число.

   Після цього клацаємо по кнопці «OK» .


6. Як бачимо, після виконання останньої дії кількість днів у місяці, до якого відноситься вибране число, відобразилася в комірці на аркуші.

Загальна формула у нас прийняла наступний вигляд:

=ДЕНЬ (КОНМЕСЯЦА (B3; 0))

У цій формулі змінним значенням є лише адреса комірки ( B3 ). Таким чином, якщо ви не хочете виконувати процедуру за допомогою майстри функцій , можна вставити дану формулу в будь-який елемент листа, просто замінивши адресу комірки, що містить число, на той, який актуальний в конкретно вашому випадку. Результат буде подібний.

Урок: Майстер функцій в Екселі

Спосіб 2: автоматичне визначення кількості днів

Тепер давайте розглянемо інше завдання. Потрібно, щоб кількість днів виводилося не по заданому календарному числу, а по поточному. До того ж зміна періодів проводилася б автоматично без участі користувача. Хоч це і здається дивним, але дана задача легше попередньої. Для її вирішення навіть відкривати Майстер функцій не потрібно, оскільки формула, що виконує дану операцію, не містить змінних значень або посилань на клітинки. Ви просто можете вбити в ту клітинку аркуша, де хочете, щоб відображався результат, наступну формулу без змін:

=ДЕНЬ (КОНМЕСЯЦА (СЬОГОДНІ (); 0))

Вбудована функція сьогодні, яку ми застосували в даному випадку, відображає сьогоднішнє число і не має аргументів. Таким чином, у вас в осередку постійно буде відображатися кількість днів в поточному місяці.

Спосіб 3: обчислення кількості днів для використання в складних формулах

У прикладах вище ми показали, як виконувати обчислення кількості днів у місяці за вказаним календарним числом або автоматично за поточним місяцем з виведенням результату в окрему клітинку. Але знаходження даного значення може знадобитися і для обчислення інших показників. При цьому розрахунок кількості днів буде проводитися всередині складної формули і не стане виводитися в окремій комірці. Подивимося, як це зробити на прикладі.

Нам потрібно зробити так, щоб в осередку відображалося ту кількість днів, яке залишилося до кінця поточного місяця. Як і в попередньому способі, даний варіант дій не вимагає відкриття майстри функцій . Ви можете просто вбити в клітинку наступний вираз:

=ДЕНЬ (КОНМЕСЯЦА (СЬОГОДНІ (); 0))-ДЕНЬ (СЬОГОДНІ ())

Після цього в зазначеній комірці буде виводитися число днів до кінця місяця. Кожен день результат буде автоматично оновлюватися, а з початку нового періоду відлік почнеться заново. Виходить своєрідний таймер зворотного відліку.

Як бачимо, зазначена формула складається з двох частин. Перша з них являє собою вже знайоме нам вираз розрахунку числа днів у місяці:

=ДЕНЬ (КОНМЕСЯЦА (СЬОГОДНІ (); 0))

А ось у другій частині проводиться віднімання з цього показника сьогоднішнього числа:

-ДЕНЬ(СЬОГОДНІ ())

Таким чином, при виконанні даного розрахунку формула обчислення кількості днів є складовою частиною більш складної формули.

Спосіб 4: альтернативна формула

Але, на жаль, у версій Програми раніше Excel 2007 відсутній оператор КОНМЕСЯЦА . Як же бути тим користувачам, які застосовують старі версії Програми? Для них така можливість існує за допомогою іншої формули, яка більш масивна, ніж описувана вище. Подивимося, як розрахувати кількість днів у місяці по заданому календарному числу за допомогою цього варіанту.

1. Виділяємо осередок для виведення результату і переходимо у вікно аргументів оператора ДЕНЬ вже звичним для нас способом. Встановлюємо курсор в єдине поле цього вікна і натискаємо по перевернутому трикутнику зліва від рядка формул. Переходимо в розділ " інші функції...» .

2. У вікні майстри функцій у групі " Дата і час» виділяємо найменування »ДАТА" і тиснемо на кнопку «OK» .

3. Запускається віконце оператора ДАТА . Ця функція перетворює дату зі звичайного формату в Числове значення, яке і повинен буде потім обробити оператор ДЕНЬ .

   Відкрите вікно має три поля. У полі «День» можна відразу ввести число «1» . Це буде незмінною дією для будь-якої ситуації. А ось двома іншими полями доведеться зайнятися грунтовно.

   Встановлюємо курсор в поле «рік» . Далі переходимо до вибору операторів через знайомий нам трикутник.


4. Все в тій же категорії майстри функцій виділяємо найменування «рік» і клацаємо по кнопці «OK» .

5. Запускається вікно аргументів оператора рік . Він визначає рік за вказаною кількістю. В єдиному полі вікна " Дата в числовому форматі» вказуємо посилання на клітинку, що містить вихідну дату, для якої потрібно визначити кількість днів. Після цього не поспішаємо клацати по кнопці «OK» , а натискаємо по найменуванню »ДАТА" у рядку формул.

6. Потім ми знову повертається у вікно аргументів ДАТА . Встановлюємо курсор в поле »місяць" і переходимо до вибору функцій.

7. В Майстер функцій клацаємо по найменуванню »місяць" і тиснемо на кнопку «OK» .

8. Запускається вікно аргументів функції місяць . Її завдання схожі з попереднім оператором, тільки вона виводить значення номера Місяця. У єдине поле даного вікна встановлюємо ту ж саму посилання на вихідне число. Потім в рядку формул клацаємо по найменуванню «ДЕНЬ» .

9. Повернення до вікна аргументів ДЕНЬ . Тут нам належить зробити всього один невеликий штрих. В єдине поле вікна, в якому вже знаходяться дані, додаємо в кінець формули вираз «-1» без лапок, а також ставимо "+ 1 " після оператора місяць . Після цього натискаємо на кнопку «OK» .

10. Як бачимо, в попередньо виділеній комірці відобразилася кількість днів у місяці, до якого належить вказане число. Загальна формула має такий вигляд:

    =ДЕНЬ(ДАТА(рік(D3);місяць(D3)+1; 1) -1)

Секрет цієї формули простий. Ми за допомогою неї визначаємо дату першого дня наступного періоду, а потім віднімаємо від неї один день, отримуючи кількість днів у зазначеному місяці. Змінною величиною в цій формулі є посилання на клітинку D3 у двох місцях. Якщо її замінити на адресу тієї комірки, в якій знаходиться дата в Вашому конкретному випадку, то можна просто вбити цей вираз в будь-який елемент листа без допомоги майстри функцій .

Урок: функції дати та часу в Excel

Як бачимо, існує кілька варіантів дізнатися кількість днів у місяці в Excel. Яким саме з них скористатися, залежить від кінцевої мети Користувача, а також від того, якою версією програми він користується.