Работа с датами Excel 2007
| Автор: | Индык Игорь Викторович |
| e-mail: | exelentc@yandex.ru |
Эта статья посвящена вопросам работы с датами в Excel 2007. В ней мы постараемся найти решения для самых популярных проблем пользователей при работе с датами.
Как мне ввести дату? Я набираю дату, а Эксель показывает число!
Нужно помнить, что Excel хранит даты в виде чисел. То есть, если Вы вводите 19.08.2011, Excel сохраняет это значение как 40774. Это число означает количество дней, отсчитанных от 1 января 1900 года.
Если при вводе даты Excel отображает число, необходимо выделить нужную ячейку или их диапазон, вызвать окно Формат ячеек (например, через контекстное меню), и на вкладке Число установить формат Дата.
Я работаю продавцом, и каждый день отправляю начальству отчет по продажам. Поэтому каждый день меняю дату вручную. Можно ли сделать так, что бы дата менялась автоматически?
Конечно. Специально для таких ситуаций Excel предлагает формулу СЕГОДНЯ. В нужную ячейку необходимо внести:
=СЕГОДНЯ()
И ежедневно отчет будет выводить текущую дату.
А мне нужно не сегодня, а за прошлый понедельник? Что мне делать?
Если сегодня у нас тоже понедельник, достаточно отнять от текущей даты 7 дней:
=СЕГОДНЯ()-7
Для других дней недели нужно отнять количество дней между этими днями недели.
Мне нужно, чтобы формула могла посчитать период в календаре. Например, человек отсутствовал с 15.09.09г. по 31.12.09г., то есть 16 дней в сентябре, плюс ещё три месяца (весь октябрь, ноябрь, декабрь), или 108 дней в общей сумме. Как написать формулу?
Как мы уже отмечали, Excel хранит даты как количество дней, отсчитанных от одной точки. Поэтому, чтобы найти разницу между двумя датами, достаточно написать формулу арифметической разницы между ячейкой с поздней датой и ячейкой с более ранней. Например, в нашем случае ячейка А1 содержит дату 15.09.09, ячейка А2 - 31.12.09. Формула будет иметь вид:
=А2-А1+1
Единицу нужно добавлять, поскольку 15.09.09 человек уже отсутствовал на работе.
В случае, если нужно вывести количество месяцев и дней (в нашем примере - 3 месяца 16 дней), можно воспользоваться формулами МЕСЯЦ и ДЕНЬ.
Формула МЕСЯЦ извлекает из введённой даты номер месяца с начала года, а формула ДЕНЬ - номер дня с начала месяца. Получив эти номера из наших дат, мы можем легко найти между ними разницу:
=МЕСЯЦ(А2)-МЕСЯЦ(A1) - результатом будет 3 =ДЕНЬ(B5)-ДЕНЬ(A5) - результатом будет 16
Эти числа можно вывести в одну ячейку в виде "3 месяца 16 дней" с помощью операнда & :
="Период отсутствия составил "&A2&" месяца и "&B2&" дней"
или функции СЦЕПИТЬ:
=СЦЕПИТЬ("Период отсутствия составил "; A2;" месяца и ";B2;" дней")
где А2 и В2 - ячейки, в которых находятся наши формулы, вычисляющие количество, соответственно, месяцев и дней.
Если период включает года, в вычисления необходимо включить функцию ГОД.
Помогите перевести из чисел в года и месяцы. Например, у нас есть 6,2 и 6,8 - то есть 6 лет 2 месяца и 6 лет 8 месяцев. Нам нужно получить сумму не 13 , а 12 лет 10 месяцев.
В этом условии присутствует ошибка. Excel определяет числа, введённые после запятой, как десятичные дроби. А это значит, что число 6,2 означает не "6 лет 2 месяца", а "6 целых 2 десятых года", что приблизительно даёт 6 лет 2 месяца и 13 дней. Если нужный период времени невозможно ввести в формате даты, мы рекомендуем сохранять целые значения лет, месяцев и дней в разных ячейках - это поможет избежать ошибок в вычислениях, и очень упростит операции. Так, чтобы получить нужный нам результат в 12 лет и 10 месяцев, достаточно будет просуммировать соответствующие ячейки:
Ну а как вывести эти числа в составе нужной фразы, мы уже написали выше.
А что делать, если мы суммируем больше чем два периода? Тогда у нас получается глупость типа 37 лет и 45 месяцев!
Согласны. Тогда давайте вычислим, сколько целых лет составляют 45 месяцев, прибавим их к 37 годам, и рассчитаем остаток месяцев:
Сначала с помощью формулы ЦЕЛОЕ узнаем, сколько целых лет составляет 45 месяцев:
=ЦЕЛОЕ(C11/12)
Ответ - 3 года.
Нам остается прибавить к общему количеству лет 3 года, и отнять от общего количества месяцев 3х12 = 36 месяцев.
Чтобы не создавать дополнительных ячеек с вычислениями на листе, можно ввести формулу ЦЕЛОЕ сразу в итоговую формулу в каждом столбце. Для количества лет она будет иметь вид:
=B11+ЦЕЛОЕ(C11/12)
И для остатка месяцев:
C11-ЦЕЛОЕ(C11/12)*12
Результат: 40 лет и 9 месяцев
