Работа с датами

Друзья, начинаем работу с временем и датами в Excel.
Первые и самые простейшие функции: СЕГОДНЯ и ТДАТА.

Чтобы получить сегодняшнюю дату через формулу, то укажите:

= СЕГОДНЯ()

Плюс в том, что с этой датой можно проводить калькуляцию. Допустим вам нужна сегодняшняя дата плюс 3 дня, указываете:

= СЕГОДНЯ() + 3

Если требуется узнать зафиксировать не только дату, но и текущее время, то используется функцию ТДАТА:

= ТДАТА()

Необходимо помнить, что если у вас включен автоматический пересчет формул, то они дата и время будут рассчитаны на момент открытия книги.
Если требуется принудительно обновить значения – нажмите F9 (ручной пересчет формул).

​​Функция ДАТА

Следующая немаловажная функция для работы с датами, так собственно и называется ДАТА.

Синтаксис:

= ДАТА (год; месяц; день)

— Год – указываете год в виде числа или ссылкой на ячейку. Excel ведет отсчет от 1900, поэтому если укажете 5, то система поймет это как 1905 год. Советую использовать четырехзначное значение года.
— Месяц – указываете месяц в виде числа или ссылкой на ячейку.
— День – указываете день в виде числа или ссылкой на ячейку.

Выражение =ДАТА(2005;10;15) передаст дату 15.10.2005.

В чем плюс этой функции? С ее помощью возможно преобразовывать текстовые значения в даты.
То есть вы получили список в 3 столбца: год, месяц, день. Вам необходимы даты.

Функция ДАТА как раз справится с этой задаче.
Не забывайте также, что все функции дат позволяют прибавлять/вычитать дни, месяцы, годы.

Выражение = ДАТА (А1; В1+5; С1+10) будет прибавлять +5 месяцев от значения В1 и +10 дней от значения С1.

​​Сколько рабочих дней?

Наиболее частая задача кадровиков, менеджеров проектов и просто руководителей – найти количество рабочих дней между датами с учетом выходных и праздников.

Для этого в Excel есть прекрасная функция ЧИСТРАБДНИ

Синтаксис:

= ЧИСТРАБДНИ (нач дата; кон дата; [праздники])

— нач дата – начальная дата, от которой необходимо посчитать рабочие дни.
— кон дата – конечная дата, до которой считаете рабочие дни.
— [праздники] – необязательный аргумент.

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

​​Получаем год из даты

Есть ряд прекрасных функций в Excel, которые выбирают из даты год, месяц или день. Рассмотрим функцию ГОД.

Синтаксис:

= ГОД (дата в числовом формате)

— дата в числовом формате – ссылка на ячейку с датой или текстовое значение даты в двойных кавычках (“14.01.2020”)

В дальнейшем значение года можно использовать в других формулах/расчетах.

​​Получаем номер месяца и дня из даты

Салют, друзья!
Давно с вами не виделись. Меня затянуло в подготовку материала для курса, посты здесь могут запаздывать, за что извиняюсь.

Аналогично году (https://t.me/exstudy/223), из даты вы можете получить числовое значение месяца и дня. Давайте по порядку.

Функция МЕСЯЦ

= МЕСЯЦ (дата в числовом формате)

— дата в числовом формате – ссылка на ячейку с датой или текстовое значение даты в двойных кавычках (“14.01.2020”)


Функция ДЕНЬ

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

— дата в числовом формате – ссылка на ячейку с датой или текстовое значение даты в двойных кавычках (“14.01.2020”)

Ничего сложного. Дополнительно в видео ⬇️

​​Получаем номер недели

На работе мы часто оперируем номерами недель. Но без производственного календаря вряд ли кто вспомнит.
В Excel же есть простая функция НОМНЕДЕЛИ.

Синтаксис:

= НОМНЕДЕЛИ (дата; тип)

— дата - указываете дату, которая содержится в искомой неделе.
— тип - необязательный аргумент, тип системы отсчета (весьма важный аргумент). Приведу описания наиболее подходящих:

▫️1 (или если аргумент не указан) – система считает, что неделя начинается с воскресенья.
▫️2 – система считает, что неделя начинается с понедельника. Это классическая система отсчета для России, поэтому ставьте 2.
▫️ 21 – отсчет недель по стандарту ISO 8601 (как говорят оф. источники), который используются в западных странах. Суть в том, что первая неделя года считается первой в случае если содержит четверг. Если же 1 января пришлось на пятницу, то это вообще неделей не считается.

По итогу система посчитаем вам порядковый номер недели.

​​Считаем разницу между датами

Салют, друзья!
Всем прекрасного лета в его лучшем проявлении: походы, отдых и отличное солнечное настроение. Ну а я продолжаю выпускать материалы для обучения Excel.
Show must go on.

Сегодня выпускаю лонгрид, казалось бы, на банальную тему - как посчитать разницу между датами.
В это деле есть 2 пути. Первый прост как палка. Второй же подойдет для анализа крупного массива данных с датами.

Приступим же.

Способ №1. Вычитание или функция ДНИ

Мне нужно посчитать кол-во дней между датами. Что самое логичное можно сделать в этом случае? Конечно же применить арифметику, точнее Вычитание.
Из более поздней даты вычесть раннюю дату.

Пример: 13.02.2021 - 10.02.2021. По итогу Excel отдаст вам количество дней - 3.
Обратите внимание, что результат включает в себя кол-во дней между этими датами + 1 день даты "До".

Также работает вычитание ячеек со значениями дат.
▫️В ячейке А2 значение 13.02.2021.
▫️В ячейке В2 значение 10.02.2021.
▫️Выражение = А2- В2 выдаст аналогичный результат - 3.

Если ошиблись и поменяли начало с концом - не страшно, система посчитает то же самое значение, но только с минусом.

Способ №2. Функция РАЗНДАТ

Получить кол-во дней между датами здорово, однако если наша задача на анализ данных куда интереснее: получить кол-во месяцев между датами или же узнать кол-во дней проходит между покупками внутри месяца, неважно какого года?

Здесь уже можно взять функцию РАЗНДАТ.
Функции нет в справочнике Excel, поэтому не пробуйте вызвать ее через мастера "Вставить функцию". Придется вводить по памяти или по моей инструкции😉

Синтаксис функции:
= РАЗНДАТ (ранняя дата; поздняя дата; аргумент)

1) По датам предельно понятно. Особенность в том, что если вы перепутали начало с концом, то формула выдаст ошибку ЧИСЛО. Будьте внимательны.

2) Аргумент. Указываем латиницей обязательно в кавычках.
▫️"d" - считаем кол-во дней между датами. Аналог вычитания дат.
▫️"m" - считаем кол-во целых месяцев между датами.
▫️"y" - считаем кол-во целых лет между датами.
▫️"yd" - считаем кол-во дней между датами без учета лет. Простой пример: 14.12.2021 - 13.12.2020 = 366 дней. А если применим аргумент "yd" с функцией РАЗНДАТ, то система посчитает 1 день. То есть Excel не смотрит на года, только даты с учетом месяцев.
▫️"md" - считаем кол-во дней между датами без учета лет и месяцев. Системе неважно какой год и месяц, вычитает только дни.
▫️"ym" - считаем кол-во месяцев между датами без учета лет. Тоже самое как и "yd" только месяцы.

Для наглядности прикладываю скриншот с применением различных аргументов для РАЗНДАТ.

Немного юмора для фанов Игры престолов😅
Всем отличной недели!

Комбинации клавиш для дат

Салют, друзья!
Выбор комбинаций клавиш - индивидуальное дело, каждый подбирает их под свои рабочие задачи.

Тем не менее, для работы с датами/временем есть 2 неплохие комбинации, о которых вы скорее всего не знали:

▫️Ctrl + Shift + 4 - это аналог функции СЕГОДНЯ(), вставляет текущую дату.

▫️Ctrl + Shift + 6 - аналог функции ТДАТА(), вставляет текущее время.

Только учтите момент, что комбинации вставляют только значения. Текущее время/дата от функций обновляется автоматически и к ним можно привязать расчеты.

А если забыли об этих фукнциях, вспомните в предыдущем посте: https://t.me/exstudy/217

​​Считаем долю времязатрат

В Excel существует интересная функция, которая дает понять % времязатрат, который заняла у вас какой-либо процесс. Например, ведение нового производственного проекта в вашей компании.
Функция ДОЛЯГОДА

Синтаксис:

= ДОЛЯГОДА (нач дата; кон дата; базис)

нач дата – дата начала процесса.
кон дата– дата завершения процесса.
Базис – режим подсчета времени. Советую использовать 1, т.к. учитывает високосный год.

Запись = ДОЛЯГОДА (01.01.2021; 13.02.2021; 1) выдаст результат 12%. То есть процесс с 1 января по 13 февраля занял 12% от всего 2021 года.

Посмотрите видео👇

Кейсы. Получаем курс доллара с сайта ЦБ РФ

Кейс, кейс, кейс.
В этом материале посмотрим на загрузку данных из интернета напрямую в Excel. Ведь это же здорово: получать автоматом свежие данные и использовать их в расчетах.

В примере подключимся к сайту ЦБ РФ и загрузим в таблицу свежие данные по курсам валют.

Я бы сказал, что это первая ступень, следующий пост будет намного интереснее и откроет цикл PRO.

В материале куча материалов, поэтому читайте статью в телеграфе:

Материал в telegraph

PRO. Получаем данные с MOEX. Часть 1

Всем привет!
Начинаю цикл PRO материалов. В них помимо Excel мы затронем всякие хранилища данных, с которыми тоже нужно будет разобраться. Материалы из PRO сложнее, но и интереснее.

То, что вы увидите здесь – это лишь малая капля от всего моря возможностей. Если понравится и проявите упорство, то сможете применять эти навыки в жизни и тратить меньше времени на анализ данных.

А теперь к теме.

Что вы знаете об инвестировании?

Я думаю, за последние 2 года россияне поднабрали в финансовой грамотности. Никого уже не удивишь валютами, акциями, облигациями и ETF. Подавай всем квала и менее ликвидные бумаги.

Кто является крупнейшим организатором торгов в РФ?

Правильно, Московская биржа или MOEX (Moscow Exchange).
Логично предположить, что если MOEX крупнейший организатор в РФ, то наверно у этих ребят есть куча данных на все инструменты рынка: объемы торгов акциями Газпрома, последние курсы валют евро и т.д.

Да, у них есть свой продукт - Информационно-статистический сервер Московской биржи (сокращенно ИСС). По-простому это огромная база, которая содержит в себе информацию о рынке. Чтобы получить информацию из нее, нужно сделать запрос (http запрос).

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

Бесплатно ли это?
Да, но с ограничениями. Бесплатное использование ИСС позволяет выгружать данные с задержкой. То есть для мониторинга курса валюты или проверки доходности своих акций – пойдет.
Профессиональные участники рынка используют платную версию с обновлением данных в реальном времени.

В цикле PRO-материалов мы с вами соберем аналитику по валютам, акциям и облигациям с ИСС MOEX.
Первый кейс на сегодня: выгружаем актуальный курс доллара по ЦБ РФ прямо с MOEX. Учимся на простом примере, чтоб потом усложнить с акциями.

Читайте лонгрид «как это можно сделать» в телеграфе.

Материал telegraph

​​Кейсы. Прогрессия дат

Салют!

Уверен, каждый из вас умеет протягивать маркер заполнения для продления прогрессии дат/чисел и т.д.

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

Возьмем дату 10.10.2020.
Наша задача заключается в продлении этой прогрессии с шагом 2 только по месяцам. То есть берете каждый второй месяц, 10 октября, 10 декабря, 10 февраля и так далее до 2023 года.

1) Прописываете дату 10.10.2020 в ячейке А1. Оставляете выделение на ячейке.
2) На вкладке «Главная» – инструмент «Заполнение» - «Прогрессия».
3) Расположение – по столбцам (прогрессия протянется вниз).
4) Тип – Даты
5) Единицы – месяц
6) Шаг – 2
7) Предельное значение – 10.10.2023.
8) Нажимаете «ОК»

Excel создаст прогрессию дат по заданным параметрам.

Посмотрите видео👇
#кейсы

​​Расчет даты с учетом рабочих дней

Представим, что вы наняли бригаду строителей для ремонта вашей квартиры. Вам посчитали смету и отметили 3 этапа:
— Демонтажные работы
— Выравнивание стен
— Покраска стен

Строители гарантируют сроки: демонтажные работы продлятся 3 рабочих дня, выравнивание стен займет 10 рабочих дней, покраска выйдет в 4 рабочих дня. Как посчитать дату завершения каждого из этапов с учетом рабочих дней?

Здесь поможет отличная функция РАБДЕНЬ. Она высчитывает конечную дату с учетом рабочих дней.
Работает весьма просто:

= РАБДЕНЬ (начдата; числодней; [праздники])

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

Посмотрите в видео-примере как можно получить даты завершения работ уже с учетом рабочих дней строителей👇

PRO. Получаем данные с MOEX. Часть 2

У кого воскресенье - это день для саморазвития? Выкладываю новый материал из цикла PRO😉.

Продолжаем работать с Московской биржей и учимся выгружать котировки российских акций.

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

Новый материал из цикла PRO🔥