Начало работы в Excel
В свободном доступе Виды книг Excel, Горячие клавиши, Быстрое копирование элементов, Автоподбор ширины строк и столбцов.
Чтобы Excel распознал дату, ее можно ввести в разных форматах:
Время вводится в ячейки с использованием двоеточия. По желанию можно дополнительно уточнить количество секунд – вводя их также через двоеточие.
Дата и время одновременно записываются вместе через пробел.
Чтобы вставить текущую дату, можно воспользоваться одним из следующих способов:
Внешний вид (отображение) даты в ячейке зависит от формата даты. Он может быть с годами или без, месяцы словом или числами и т.д.). Чтобы задать нужный формат даты:
Правая кнопка мышки > Формат ячеек или сочетанием клавиш Ctrl+1 -> Вкладка "Число" > Числовой формат "Дата" и выбрать нужный вид.
Дата в Excel - это число (целое или дробное).
Если выделить ячейку с датой и установить для нее числовой или общий формат, то можно увидеть данное число, соответствующее дате:
Поэтому с датами и временем в Excel возможно выполнять любые математические операции.
Если в вашей ячейке лежит дата в виде текстовой строки, например, "8 март 2013", то для Excel это не дата, а текст. Чтобы преобразовать текстовую дату в полноценную и использовать в расчетах, можно использовать специальную функцию ДАТАЗНАЧ.
Но данная функция может преобразовать не каждый вид написания даты. В этом случае можно воспользоваться функцией ПОДСТАВИТЬ, которая заменяет разные символы на точки. А потом уже использовать функцию ДАТАЗНАЧ для преобразования получившегося текста в реальную дату.
КОНМЕСЯЦА(начальная дата; число месяцев)
Функция возвращает порядковый номер последнего дня месяца, отстоящего на указанное количество месяцев от даты. Используется для вычисления даты погашения или даты платежа, которая приходится на конец месяца.
Начальная дата — обязательный аргумент.
Число месяцев — Количество месяцев до или после начальной даты. Если нужно получить последнюю дату текущего месяца, то нужно поставить ноль. Положительное значение аргумента означает будущие даты; отрицательное значение — прошедшие даты. Если значение аргумента не является целым числом, оно усекается. Обязательный аргумент.
Пример:
Дата: 1-янв-23
Формула: =КОНМЕСЯЦА(A2;1)
Результат: 28.02.2023
Считается простым вычитанием: из конечной даты вычитаем начальную и переводим в другой формат (Общий или Числовой).
Например: =B1-A1
Здесь не учитываются субботы, воскресенья и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ. В качестве аргументов этой функции необходимо указать:
=ЧИСТРАБДНИ(дата начальная; дата конечная; выходные)
Чтобы прибавить (или вычесть) некоторое количество календарных дней, достаточно прибавить (или вычесть) к дате это число.
Чтобы прибавить или вычесть некоторое количество рабочих (банковских) дней:
Функция РАБДЕНЬ - позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных, суббот и воскресений, и государственных праздников). В качестве аргументов этой функции необходимо указать: начальную и конечную даты, ячейки с датами выходных.
=РАБДЕНЬ(дата; количество рабочих дней; выходные)
Чтобы прибавить или вычесть некоторое количество месяцев:
=ДАТАМЕС(дата; количество месяцев)
Чтобы прибавить или вычесть некоторое количество лет, специальной функции нет. Для этого можно использовать следующую формулу:
=ДАТА(ГОД(A1)+B1;МЕСЯЦ(A1);ДЕНЬ(A1))
Сначала мы разбираем дату на составляющие при помощи функций ДЕНЬ, МЕСЯЦ и ГОД, а потом собираем ее обратно функцией ДАТА, но предварительно добавляем к году нужное число.
Чтобы посчитать общее затраченное время, можно отдельные временные интервалы просто сложить.
Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять заново. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55.
=ДЕНЬ(А1) - выделить день из даты, которая указана в ячейке А1
=МЕСЯЦ(А1) - выделить месяц из даты, которая указана в ячейке А1
=ГОД(А1) - выделить год из даты, которая указана в ячейке А1
Если необходимо, чтобы месяц выводился не числом, а словом, можно использовать функцию ВЫБОР для подстановки нужного слова по номеру месяца:
=ВЫБОР(B2; "январь"; "февраль"; "март"; "апрель"; "май"; "июнь"; "июль"; "август"; "сентябрь"; "октябрь"; "ноябрь"; "декабрь")
=ДАТА(B3,B2,B1) - соединяет день, месяц и год, где
B3 - год,
B2 - месяц,
B1 - день.
Обратите внимание:
=ДЕНЬНЕД(Дата; Тип даты)
Тип даты >> Тип 1: с Вс=1 по Сб=7, Тип 2: с Пн=1 по Вс=7, Тип 3: с ПН=0 по Вс=6.Если необходимо, чтобы день недели выводился не числом(5), а текстом("пт" или "пятница"), то можно использовать еще и функцию ВЫБОР, чтобы подставить вместо цифры нужное текстовое значение:
=ВЫБОР(B1;"понедельник";"вторник";"среда";"четверг";"пятница";"суббота";"воскресенье")
=НОНЕДЕЛИ(Дата; Тип даты)
Тип даты >> Тип 1: с Вс=1 по Сб=7, Тип 2: с Пн=1 по Вс=7.
В этом случае первой неделей года считается та, на которую попадает 1 января.
=ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3;0)
Функция МЕСЯЦ в данном случае извлекает номер месяца для даты, а ОКРУГЛВВЕРХ –округляет результат до ближайшего наибольшего целого.
Если вам нужны римские (I, II, III, IV), а не арабские цифры в нумерации кварталов, то к формуле можно добавить функцию преобразования РИМСКОЕ:
=РИМСКОЕ(ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3;0))
Вычисление возраста или стажа
Используется функция РАЗНДАТ.
Синтаксис:
=РАЗНДАТ(начальная_дата;конечная_дата;способ_измерения),
где способ измерения может быть следующий:
"y" - разница в полных годах
"m" - разница в полных месяцах
"d" - разница в полных днях
"yd" - разница в днях с начала года без учета лет
"md" - разница в днях без учета месяцев и лет
"ym" - разница в полных месяцах без учета лет
В настоящее время данной функции в списке нет, она оставлена для совместимости со старыми версиями Excel. Но если ее ввети вручную. то она работает.
Пример:
=РАЗНДАТ(A1;A2;"y")
При желании подсчитать и вывести, например, ваш стаж в виде "3 г. 4 мес. 12 дн." можно использовать символ“&”для склеиванияфрагментов,т.е.ввести вячейку следующую формулу:
=РАЗНДАТ(A1;A2;"y")&"г."&РАЗНДАТ(A1;A2;"ym")&"мес."&РАЗНДАТ(A1;A2;"md")&"дн."
где А1 – ячейка с датой приема на работу, А2– с датой увольнения.
Можно создать автоматический универсальный календарь с помощью одной формулы, который будет показывать текущий месяц.
Но здесь нужно быть внимательным, так как формула задается для массива (диапазона ячеек) и если ее вбиь в одной ячейке, то она не будет работать.
Чтобы создать универсальный календарь, сделайте следующий шаблон:
=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(A1);МЕСЯЦ(A1);1))<>МЕСЯЦ(ДАТА(ГОД(A1);МЕСЯЦ(A1);1)-(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1);2)-1)+{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1);"";ДАТА(ГОД(A1);МЕСЯЦ(A1);1)-(ДЕНЬНЕД(ДАТА(ГОД(A1);МЕСЯЦ(A1);1);2)-1)+{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1)