Как сделать диапазон дат в excel

Как сделать диапазон дат в excel?

Формулами.

Исходные: A1 — начальная дата, В1 — длительность проекта в днях.

Протянуть формулу вниз.

Формулы строк, которые ниже требуемого диапазона дат, покажут 0 (ноль).

Нули можно скрыть:

закладка Файл-Параметры-Дополнительно-Для_листа…, снять галку отображения нулевых значений.

Если даты не используются в дальнейших вычилениях, вместо нуля можно выводить пустую строку:

Для периода, заданного в месяцах или годах, нужно определить количество дней в периоде или последнюю дату.

Например, для месяцев полная формула для А2:

Если показывать только даты помесячно:

Для уменьшения вычислений определение последней даты лучше вывести в отдельню ячейку и в формулах ссылаться на нее. Вариант:

Макросом.

Исходные: A1 — начальная дата, В1 — длительность проекта в месяцах.

Диапазон вставки: столбец Е — порядковый номер, столбец F — дата.

Формируется список дат с шагом 1 месяц:

Глава 12. Выборка из диапазона дат с помощью критерия в ином формате

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава Оглавление Следующая глава

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

Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Давайте подробнее изучим работу этих пяти формул.

  • Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
  • Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
  • Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
  • Формулы с вспомогательными столбцами как правило работают быстрее.
  • Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
  • Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
  • Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: » >=1 » &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
  • Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
  • Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.
  • Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
  • В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать ИнструментыНадстройкиАнализ Данных.
  • Эта формула работает быстрее, чем формулы и .
  • Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
  • Далее сравниваются два фрагмента, каждый полкченный конкатенацией.
  • Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.

Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах и критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.

Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)

Допустим поставки товаров от поставщика 1 раз в неделю. Банковские транзакции взаиморасчетов с поставщиком товаров производятся регулярно каждый вторник. Необходимо быстро проверить в какие дни дата поставки товара совпадает с датой взаиморасчетов. Это позволит предоставить ежемесячный отчет за прошлый месяц о взаиморасчетах поставщику в день поставки товара и сэкономить на курьерских услугах. Для этого следует выделить все даты предполагаемых поставок, припадающие на вторник.

Поиск даты в диапазоне дат Excel

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

  1. Выделите диапазон ячеек B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. Выберите: «Использовать формулу для определения форматируемых ячеек».
  3. Чтобы выбрать дни вторников в Excel для поля ввода введите формулу: =ДЕНЬНЕД(B2;2)=2 и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.

Результат выделения цветом каждого вторника в списке дат поставок товаров:

В формуле мы использовали функцию =ДЕНЬНЕД(), которая возвращает номер дня недели для исходной даты. В первом аргументе функции указываем ссылку на исходную дату. Ссылка относительная так как будет проверятся каждая дата в столбце B. Во втором аргументе обязательно следует указать число 2, чтобы функция правильно выдавала нам очередности дней недели (1-понедельник, 2-вторник и т.д.). Если не указать второй аргумент, то функция будет считать дни в английском формате: 1-sanday (неделя), 2-Monday (понедельник) и т.д.

Дальше идет оператор сравнения с числом 2. Если возвращаемое число функцией = 2 значит формула возвращает значение ИСТИНА и к текущей ячейке применяется пользовательский формат (красная заливка).

Выделения дат цветом только текущего месяца

В таблице представлен список проектов, которые должны быть реализованные на протяжении текущего полугодия. Список большой, но нас интересуют только те проекты, которые должны быть реализованы в текущем месяце. Визуально искать в списке даты текущего месяца – весьма неудобно. Можно использовать условное форматирование, чтобы Excel нам автоматически выделил цветом интересующие нас даты в списке. Пример таблицы проектов на текущий месяц в Excel:

Нам необходимо из целой таблицы выбрать дату в Excel на текущий месяц:

  1. Выделите диапазон ячеек B2:B11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. Снова выбираем: «Использовать формулу для определения форматируемых ячеек».
  3. В поле ввода введите формулу: =МЕСЯЦ(B2)=МЕСЯЦ($C$2) и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.

В результате визуально отчетливо видно дни текущего месяца:

В представленной формуле для условного форматирования главную роль играет функция =МЕСЯЦ(), которая определяет номер месяца исходной даты. Ссылка на ячейку в аргументе первого раза использования функции МЕСЯЦ – относительная, так как проверятся будут несколько ячеек в столбце C. После функции стоит оператор сравнения с номером текущего месяца. Там уже используется абсолютная ссылка (значение аргумента неизменяемое). Если результаты вычислений по обе стороны оператора сравнения совпадают, тогда формула возвращает значение ИСТИНА и к текущей ячейке присваивается пользовательский формат (заливка зеленым цветом).

Теперь усложним задачу. Допустим у нас список проектов охватывает не полугодие, а несколько лет:

Тогда возникает сложность ведь в разные года номера месяцев будут совпадать и условное форматирование будет выделять лишние даты. Поэтому необходимо модернизировать формулу:

Мы добавили в формулу две функции, которые позволяют нам выбрать дни определенного месяца и года:

  1. =ГОД() – функция работает по тому же принципу что и =МЕСЯЦ(). Разница только в том, что возвращает из даты номер года.
  2. =И() – данное имя функции говорит само за себя. То есть должны совпадать и номера месяцев и их номера годов.

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

Читайте так же: как посчитать разницу между датами в днях.

Общая формула

Объяснение

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

В показанном примере формула в ячейке E5:

Как работает эта формула

Функция ТЕКСТ принимает числовые значения и преобразует их в текстовые значения, используя заданный вами формат. В этом примере мы используем формат «ммм д» для обеих функций TEКСT в E5. Результаты объединяются дефисом, используя простую конкатенацию.

Примечание. Другие примеры в столбце E используют разные текстовые форматы.

Дата окончания отсутствует

Если дата окончания отсутствует, формула не будет работать правильно, потому что дефис будет добавлен к дате начала (например, «1 марта»).

Чтобы обработать этот случай, вы можете обернуть конкатенацию и вторую функцию ТЕКСТ внутри ЕСЛИ следующим образом:

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

Отсутствует дата начала

Для обработки случая, когда обе даты отсутствуют, вы можете вложить еще один ЕСЛИ следующим образом:

Эта формула просто возвращает пустую строку («»), когда дата1 недоступна.

Источник:
http://word-office.ru/kak-sdelat-diapazon-dat-v-excel.html

Основные принципы работы с датами и временем в Excel

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

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

Читайте также  ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ) - Служба поддержки Office

С использованием дефисов

С использованием дроби

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells) :

Время вводится в ячейки с использованием двоеточия. Например

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать — как именно копировать выделенную дату:

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY) :

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка ЧислоОбщий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

  • Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! 😉
  • Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak) . После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS) .

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY) . Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Источник:
http://www.planetaexcel.ru/techniques/6/88/

Как суммировать значения между двумя датами

Давайте представим, что мы работаем в торговой компании. Руководитель поставил нам задачу посчитать сумму продаж за последние 15 дней. За конкретный промежуток времени.

Давайте рассмотрим как это сделать.

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

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

  • диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
  • диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
  • [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

Формула для суммирования значений между двумя датами

Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.

Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:

После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.

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

Как работает эта формула

В нашей формуле мы использовали логические операторы в функции СУММЕСЛИМН , которые помогают нам суммировать данные в указанном диапазоне дат.

Давайте разложим формулу на составные части для понимания ее работы:

  • Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
  • Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
  • Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
  • Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018

Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

Как суммировать значения между двумя динамическими датами

На примере выше мы рассмотрели как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней на ту дату, в которую мы открыли файл? Если мы не хотим каждый раз проставлять в формулу конкретные даты?

В этом случае нам поможет следующая формула:

=СУММЕСЛИМН(B2:B18;A2:A18;” =”&СЕГОДНЯ()-6)

Как работает эта формула

В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.

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

Во втором критерии мы указываем функции, что нужно суммировать данные больше или равные текущей дате минус 6 дней.

Таким образом, диапазон двух дат в формуле теперь выглядит как: “Посчитай данные за период с сегодняшнего дня, до минус 6 дней от сегодняшнего дня”.

Если у вас остались вопросы по этому примеру оставляйте их в комментариях.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Источник:
http://excelhack.ru/kak-summirovat-znacheniya-mezhdu-dvumya-datami/

Интервалы дат в Excel – функции обработки

Дорогие друзья, в предыдущих постах мы рассмотрели, что такое дата и время в Эксель, как разложить дату на составляющие и собрать её обратно из дня, месяца и года. То есть, мы работали с одной датой. Пора нам теперь поработать и с интервалами дат. В этом посте – распространенные функции обработки временных отрезков. Разберитесь в работе этих функций, и вы уже почти на коне!

Как прибавить несколько месяцев к дате Эксель

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

Например, чтобы прибавить к дате в ячейке А1 пять месяцев, мы использовали формулу: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+5;ДЕНЬ(А1)) . На самом деле, есть более простой и наглядный способ выполнить эту операцию – используем функцию ДАТАМЕС(Дата ; Количество_месяцев) .

У функции два обязательных аргумента :

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

Приведенный выше пример можно решить с помощью простой формулы: =ДАТАМЕС(А1;5) . Согласитесь, такая запись короче и легче для восприятия.

Как определить день недели в Excel

Часто нужно знать – какой день недели был (будет) в определенную дату. Как бы вы решали такую задачу? Вручную сложно, если нужно обработать несколько десятков, сотен, тысяч дат.

Воспользуйтесь функцией ДЕНЬНЕД(Дата ; Тип) . Она возвращает порядковый номер дня недели и имеет два аргумента :

  1. Дата, для которой нужно определить день недели – обязательный аргумент
  2. Тип – необязательный параметр, который указывает какой день недели считать первым. Например, в странах восточной Европы первый день недели – понедельник, в США – воскресенье. В любом случае, формула может считать первым днем любой день недели. Если аргумент не указан – первым днем считается воскресенье. При записи формулы – Excel выведет подсказку с перечнем возможных параметров
Читайте также  8 простых способов как посчитать в Excel сумму столбца

Функция ДЕНЬНЕД в Эксель

Когда вы получили порядковый номер дня недели, можно использовать, например, функцию условия ЕСЛИ для присвоения ему текстового имени, или обработать как-то иначе.

Как определить количество дней между датами в Эксель

Нет ничего проще, чем определить количество дней между датами. Просто вычтите более позднюю дату из ранней. Например, в ячейке А1 – дата начала работы над проектом, а в А2 – дата сдачи проекта. Тогда количество дней между ними можно посчитать так: =А2-А1 .

Эту же процедуру можно выполнить с помощью функции ДНИ(Конечная дата ; Начальная дата) . Видимой разницы между первым и вторым способами нет, они возвращают одинаковые результаты. Пользуйтесь этими способами ими по ситуации.

Как посчитать долю от года в Microsoft Excel

Если вам известен некий период, и нужно знать, какую часть календарного года он занимает, используйте функцию ДОЛЯГОДА(Начальная_дата ; Конечная_дата; Базис) . Как видите, у функции 3 агрумента :

  1. Начальная дата – дата старта изучаемого периода – обязательный аргумент
  2. Конечная дата – дата окончания период – обязательный аргумент
  3. Базис – базовые значения длительности года. При введении параметра программа выведет подсказку по выбору этого аргумента.

Например, проект начался 10.08.2015 и закончился 08.05.2016. Чтобы определить долю периода от календарного года, запишем формулу: =ДОЛЯГОДА(«10.08.2015″;»08.05.2016»;1) . Получим результат 0,7432. Отформатируем его в процентном формате и получим 74% года.

Как получить последний день месяца

Чтобы получить дату последнего дня месяца – используйте функцию КОНМЕСЯЦА(Дата ; Количество_месяцев) . Эта функция возвращает последний день заданной даты, или отстоящей от нее на определенное количество месяцев. Она использует 2 обязательных аргумента :

  • Дата – базовый день месяца, к которому нужно прибавить месяца и вывести последний итогового месяца
  • Количество месяцев – сколько месяцев нужно прибавить к дате. Укажите этот параметр равным нулю, если хотите получить последний день месяца, заданного в аргументом «Дата».

Как узнать номер недели в году

Если у вас есть дата, и вам нужно узнать порядковый номер этой недели в году, используйте функцию НОМНЕДЕЛИ(Дата;Базис):

  • Аргумент «Дата» — это ваша дата, которая принадлежит к искомой неделе (обязательный аргумент)
  • Базис – необязательный аргумент, указывающий какой день недели считается первым. По умолчанию для функции – это воскресенье.

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

Вам так же может быть интересно:

Источник:
http://officelegko.com/2016/02/09/intervalyi-dat-v-excel-funktsii-obrabotki/

10 популярных функций даты и времени в Microsoft Excel

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

Работа с функциями даты и времени

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

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

  1. Для введения формулы через Мастер функций выделите ячейку, где будет выводиться результат, а затем сделайте щелчок по кнопке «Вставить функцию». Расположена она слева от строки формул.

После этого происходит активация Мастера функций. Делаем клик по полю «Категория».

Из открывшегося списка выбираем пункт «Дата и время».

Кроме того, Мастер функций можно активировать, выделив ячейку на листе и нажав комбинацию клавиш Shift+F3. Существует ещё возможность перехода во вкладку «Формулы», где на ленте в группе настроек инструментов «Библиотека функций» следует щелкнуть по кнопке «Вставить функцию».

Имеется возможность перемещения к окну аргументов конкретной формулы из группы «Дата и время» без активации главного окна Мастера функций. Для этого выполняем перемещение во вкладку «Формулы». Щёлкаем по кнопке «Дата и время». Она размещена на ленте в группе инструментов «Библиотека функций». Активируется список доступных операторов в данной категории. Выбираем тот, который нужен для выполнения поставленной задачи. После этого происходит перемещение в окно аргументов.

Одной из самых простых, но вместе с тем востребованных функций данной группы является оператор ДАТА. Он выводит заданную дату в числовом виде в ячейку, где размещается сама формула.

Его аргументами являются «Год», «Месяц» и «День». Особенностью обработки данных является то, что функция работает только с временным отрезком не ранее 1900 года. Поэтому, если в качестве аргумента в поле «Год» задать, например, 1898 год, то оператор выведет в ячейку некорректное значение. Естественно, что в качестве аргументов «Месяц» и «День» выступают числа соответственно от 1 до 12 и от 1 до 31. В качестве аргументов могут выступать и ссылки на ячейки, где содержатся соответствующие данные.

Для ручного ввода формулы используется следующий синтаксис:

Близки к этой функции по значению операторы ГОД, МЕСЯЦ и ДЕНЬ. Они выводят в ячейку значение соответствующее своему названию и имеют единственный одноименный аргумент.

Своего рода уникальной функцией является оператор РАЗНДАТ. Он вычисляет разность между двумя датами. Его особенность состоит в том, что этого оператора нет в перечне формул Мастера функций, а значит, его значения всегда приходится вводить не через графический интерфейс, а вручную, придерживаясь следующего синтаксиса:

Из контекста понятно, что в качестве аргументов «Начальная дата» и «Конечная дата» выступают даты, разницу между которыми нужно вычислить. А вот в качестве аргумента «Единица» выступает конкретная единица измерения этой разности:

  • Год (y);
  • Месяц (m);
  • День (d);
  • Разница в месяцах (YM);
  • Разница в днях без учета годов (YD);
  • Разница в днях без учета месяцев и годов (MD).

В отличии от предыдущего оператора, формула ЧИСТРАБДНИ представлена в списке Мастера функций. Её задачей является подсчет количества рабочих дней между двумя датами, которые заданы как аргументы. Кроме того, имеется ещё один аргумент – «Праздники». Этот аргумент является необязательным. Он указывает количество праздничных дней за исследуемый период. Эти дни также вычитаются из общего расчета. Формула рассчитывает количество всех дней между двумя датами, кроме субботы, воскресенья и тех дней, которые указаны пользователем как праздничные. В качестве аргументов могут выступать, как непосредственно даты, так и ссылки на ячейки, в которых они содержатся.

Синтаксис выглядит таким образом:

Оператор ТДАТА интересен тем, что не имеет аргументов. Он в ячейку выводит текущую дату и время, установленные на компьютере. Нужно отметить, что это значение не будет обновляться автоматически. Оно останется фиксированным на момент создания функции до момента её перерасчета. Для перерасчета достаточно выделить ячейку, содержащую функцию, установить курсор в строке формул и кликнуть по кнопке Enter на клавиатуре. Кроме того, периодический пересчет документа можно включить в его настройках. Синтаксис ТДАТА такой:

Очень похож на предыдущую функцию по своим возможностям оператор СЕГОДНЯ. Он также не имеет аргументов. Но в ячейку выводит не снимок даты и времени, а только одну текущую дату. Синтаксис тоже очень простой:

Эта функция, так же, как и предыдущая, для актуализации требует пересчета. Перерасчет выполняется точно таким же образом.

Основной задачей функции ВРЕМЯ является вывод в заданную ячейку указанного посредством аргументов времени. Аргументами этой функции являются часы, минуты и секунды. Они могут быть заданы, как в виде числовых значений, так и в виде ссылок, указывающих на ячейки, в которых хранятся эти значения. Эта функция очень похожа на оператор ДАТА, только в отличии от него выводит заданные показатели времени. Величина аргумента «Часы» может задаваться в диапазоне от 0 до 23, а аргументов минуты и секунды – от 0 до 59. Синтаксис такой:

Кроме того, близкими к этому оператору можно назвать отдельные функции ЧАС, МИНУТЫ и СЕКУНДЫ. Они выводят на экран величину соответствующего названию показателя времени, который задается единственным одноименным аргументом.

Функция ДАТАЗНАЧ очень специфическая. Она предназначена не для людей, а для программы. Её задачей является преобразование записи даты в обычном виде в единое числовое выражение, доступное для вычислений в Excel. Единственным аргументом данной функции выступает дата как текст. Причем, как и в случае с аргументом ДАТА, корректно обрабатываются только значения после 1900 года. Синтаксис имеет такой вид:

Задача оператора ДЕНЬНЕД – выводить в указанную ячейку значение дня недели для заданной даты. Но формула выводит не текстовое название дня, а его порядковый номер. Причем точка отсчета первого дня недели задается в поле «Тип». Так, если задать в этом поле значение «1», то первым днем недели будет считаться воскресенье, если «2» — понедельник и т.д. Но это не обязательный аргумент, в случае, если поле не заполнено, то считается, что отсчет идет от воскресенья. Вторым аргументом является собственно дата в числовом формате, порядковый номер дня которой нужно установить. Синтаксис выглядит так:

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

Читайте также  Форматирование чисел в виде значений даты и времени - Служба поддержки Office

Оператор ДОЛЯГОДА производит долевой расчет отрезка года, заключенного между двумя датами ко всему году. Аргументами данной функции являются эти две даты, являющиеся границами периода. Кроме того, у данной функции имеется необязательный аргумент «Базис». В нем указывается способ вычисления дня. По умолчанию, если никакое значение не задано, берется американский способ расчета. В большинстве случаев он как раз и подходит, так что чаще всего этот аргумент заполнять вообще не нужно. Синтаксис принимает такой вид:

Мы прошлись только по основным операторам, составляющим группу функций «Дата и время» в Экселе. Кроме того, существует ещё более десятка других операторов этой же группы. Как видим, даже описанные нами функции способны в значительной мере облегчить пользователям работу со значениями таких форматов, как дата и время. Данные элементы позволяют автоматизировать некоторые расчеты. Например, по введению текущей даты или времени в указанную ячейку. Без овладения управлением данными функциями нельзя говорить о хорошем знании программы Excel.

Источник:
http://lumpics.ru/functions-date-and-time-in-excel/

Excel. Диапазон ячеек в формуле

Безусловно, понятие диапазона в Excel – одно из ключевых. Что это такое? Все мы знаем, что лист состоит из ячеек. Вот если несколько из них содержат какую-то информацию, то это диапазон. Простыми словами, это две и более ячейки в документе.

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

Как выбирать ячейки, строки и столбцы

Ячейка – это элемент, содержащий определенную информацию или который может ее содержать. Строка – это ячейки в ряд. Столбец, соответственно, в колонку. Все просто.

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

Чтобы выбрать ячейку, нужно нажать по ней. Каждая ячейка имеет адрес. Например, расположенная на пересечении колонки C и ряда 3 называется C3.

1

Соответственно, чтобы выбрать колонку, необходимо нажать на букву, отображающую название столбца. В нашем случае это колонка C.

2

Как можно догадаться, чтобы выбрать строчку, нужно сделать то же самое, только с названием ряда.

3

Диапазон ячеек: пример

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

Важно! Не квадратик в правом нижнем углу, а просто как бы потянуть за эту ячейку. Квадратик – это маркер автозаполнения, это немного другое.

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

4

Как заполнить диапазон

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

    Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2.

5

  • Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.
  • В результате получится следующее. Здесь мы заполнили все нужные ячейки цифрами 2.

    6

    Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

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

    7 8

    Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.

    9

    После этого осуществляем уже знакомое нами перетаскивание.

    10

    Перемещение диапазона

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

    Потом нужно просто переместить его в нужное место и отпустить мышь.

    11 12

    Копирование и вставка диапазона

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

    Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.

    13

    Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена».

    Следующий этап – вставить нужную информацию в другое место. Для этого необходимо найти ячейку, которая будет служить левым верхним углом диапазона, после чего аналогично вызвать контекстное меню, но при этом найти пункт «Вставить». Также можно воспользоваться стандартной комбинацией Ctrl + V, которая работает абсолютно в любых программах.

    14

    Как вставить определенную строку или столбец

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

    15

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

    16

    Таким вот способом у нас получилось вставить строку.

    17

    Именованные диапазоны

    Как может быть понятно из названия, под именованным подразумевается тот диапазон, которому было присвоено имя. Это значительно удобнее, поскольку повышается его информативность, что особенно полезно, например, если над одним документом работает сразу несколько человек.

    Назначить имя диапазону можно через Диспетчер имен, который можно найти в меню Формулы – Определенные имена – Диспетчер имен.

    Но вообще, способов несколько. Давайте рассмотрим некоторые примеры.

    Предположим, перед нами стоит задача определить объем продажи товаров. Для этой цели у нас отведен диапазон B2:B10. Чтобы присвоить имя, необходимо использовать абсолютные ссылки.

    18

    В общем, наши действия следующие:

    1. Выделить необходимый диапазон.
    2. Перейти на вкладку «Формулы» и там найти команду «Присвоить имя».
    3. Далее появится диалоговое окно, в котором необходимо указать имя диапазона. В нашем случае это – «Продажи».
    4. Там еще находится поле «Область», которое дает возможность выбрать лист, на котором этот диапазон находится.
    5. Проверьте, чтобы был указан правильный диапазон. Формула должна быть следующей: =’1сезон’!$B$2:$B$10
    6. Кликните «ОК».

    19

    Теперь можно вместо адреса диапазона вводить его имя. Так, с помощью формулы =СУММ(Продажи) можно вычислить сумму продаж для всех товаров.

    20

    Аналогично можно осуществить расчет среднего объема продаж с помощью формулы =СРЗНАЧ(Продажи) .

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

    В некоторых случаях лучше использовать относительную ссылку.

    Давайте теперь определим сумму продаж для каждого из четырех времен года. Ознакомиться с информацией о продажах можно на листе 4_сезона.

    На этом скриншоте диапазоны следующие.

    B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10

    Соответственно, нам нужно разместить формулы в ячейках B11, C11, D11 и E11.

    21

    Конечно, чтобы воплотить эту задачу в реальность, вы можете создать несколько диапазонов, но это немного неудобно. Значительно лучше воспользоваться одним. Чтобы упростить так себе жизнь, необходимо использовать относительную адресацию. В таком случае достаточно просто иметь один диапазон, который в случае с нами получит название «Сезонные_продажи»

    Для этого необходимо открыть диспетчер имен, ввести имя в диалоговом окне. Механизм тот же самый. Перед тем, как нажимать «ОК», нужно убедиться, что в строку «Диапазон» введена формула =’4сезона’!B$2:B$10

    В этом случае адресация смешанная. Как вы видите, перед названием колонки не указано знака доллара. Это позволяет суммировать значения, которые находятся в одинаковых строках, но разных столбцов.

    Далее методика действий та же самая.

    Теперь нам нужно ввести в ячейку B11 формулу =СУММ(Сезонные_Продажи) . Далее, используя маркер автозаполнения, переносим ее в соседние ячейки, и получается такой результат.

    22

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

    23

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

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

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

    24

    Автоматически изменяемые диапазоны

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

    Предположим, вы – инвестор, и вам требуется знать, сколько всего денег вы получили за время инвестиций в определенный объект. И предположим, у вас есть такой отчет.

    25

    Для этого есть функция «Динамические имена». Чтобы присвоить его, необходимо осуществить следующие действия:

    1. Открыть окно «Присвоить имя».
    2. Заполнить поля так, как указано на скриншоте.

    26

    Важно обратить внимание, что вместо диапазона используется формула с функцией СМЕЩ совместно с функцией СЧЕТ .

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

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

    Источник:
    http://office-guru.ru/excel/jacheiki-diapazony/excel-diapazon-yacheek-v-formule.html