rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Расчет возраста (количество полных лет)

| Категория: Приемы и советы, Формулы и функции |

13

Существует несколько способов, как можно вычислить количество полных лет между двумя указанными датами.

1-й способ:

В категории функций, работающими с датами, есть функция ДОЛЯГОДА [YEARFRAC], которая позволяет вычислить долю года количеством полных дней между двумя датами.

ДОЛЯГОДА(Нач_дата;Кон_дата;Базис) [YEARFRAC(Start_date;End_date;Basis)] – определяет долю году, которую составляет количество дней между начальной и конечной датой.

Базис задается одним из коэффициентом:
0 или опущен – Американский (NASD) 30/360
1 – Фактический/фактический
2 – Фактический/360
3 – Фактический/365
4 – Европейский 30/360

datedif1.png

Остается только скорректировать ответ, оставив только целую часть числа, воспользовавшись функцией ЦЕЛОЕ [INT]:

datedif2.png

2-й способ:

Воспользоваться функцией РАЗНДАТ [DATEDIF]. Не удивляйтесь, что не встретите такую функцию в списке функций, работающих с датами. Эта функция-невидимка в Excel. Она давно присутствует в программе, но по ней нет информации в том числе в справочной системе.

РАЗНДАТ(ДатаНачала;ДатаОкончания;Интервал)

Аргумент ДатаОкончания должна быть не менее аргумента ДатаНачала.

Интервал описывается особым образом:

y – количество полных лет, прошедших между ДатаНачала и ДатаОкончания;
m – количество полных месяцев между ДатаНачала и ДатаОкончания;
d – количество дней между ДатаНачала и ДатаОкончания.

Если рассматриваемые ДатаНачала и ДатаОкончания принадлежат одному и том же году, то можно вычислять со следующими интервалами:

ym – количество полных месяцев между ДатаНачала и ДатаОкончания;
yd – количество дней между ДатаНачала и ДатаОкончания;
md – количество дней между ДатаНачала и ДатаОкончания, словно они в одном месяце.

datedif3.png

Как не вводить даты из будущего?

| Категория: Приемы и советы, Работа с данными ячеек |

8

Предложим, что необходимо вводить данные в ячейки, при этом вероятность опечаток есть всегда. И необходимо контролировать ввод дат, т.е. вводимая дата не может превышать текущую дату. При этом следует учесть, что дата ежедневно изменяется.

Данная задача решается достаточно просто с использованием проверки данных:

  • Выделить диапазон ячеек.
  • На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation].
  • В диалоговом окне Проверка вводимых значений [Data Validation] на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – выбрать тип данных и поставить ограничение на вводимые значения.
  • Воспользуемся типом данных Дата [Date], чтобы задать ограничение на ввод даты. Т.к. дата ежедневно меняется, то необходимо сделать привязку к обновляемой дате. Это можно сделать, использовав функцию СЕГОДНЯ() [TODAY()]. Ограничение будет выглядеть следующим образом:

datavalid4.png

При необходимости, можно выводить своё сообщение об ошибке на экран, задав на вкладке Сообщение об ошибке [Error Alert]:

datavalid2.png

Сообщение будет появляться при условии, что установлен флажок Выводить сообщение об ошибке [Show error alert after invalid data is entered].

При вводе дат из будущего, можно будет увидеть “возмущения”:

datavalid3.png

При этом следует помнить, что дата на вашем компьютере должна быть установлена корректно!

Защита файла от открытия

| Категория: Приемы и советы |

6

Книгу Excel можно защитить как от открытия, так и от изменений. Если задан пароль для открытия файла, то рабочая книга может быть открыта только в случае, если пароль известен. Если установить защиту от изменений в рабочей книги, то изменения в книге могут быть сохранены только после указания пароля или путем пересохранения рабочей книги под другим именем.

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

1. Открыть диалоговое окно сохранения файла, нажав клавишу F12.

2. На кнопке Сервис [Tools], выбрать Общие параметры [General Options].

fileprotect1.png

3. Ввести Пароль для открытия файла [Password to open], OK.

fileprotect2.png

4. Повторить пароль, OK.

5. Нажать кнопку Сохранить [Save] и подтвердить замену существующего файла.

При открытии файла будет необходимость вводить пароль:

fileprotect3.png

И что самое приятное, то что количество попыток не ограничено! Всякий раз при открытии будет появляться окно 🙂

Отмена защиты происходит аналогично установки защиты от открытия, только вместо ввода пароля происходит его удаление из соответствующего поля.