3 примера, как функция если работает с датами

Как функция ДЕНЬНЕД определяет конкретные даты в Excel

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

Определение дня в году

1 января — это первый день года, а 31 декабря — последний. А как насчет остальных дней, идущих между ними? Следующая формула возвращает день года для даты, хранящемся в ячейке A1: =A1-ДАТА(ГОД(A1);1;0) . Например, если ячейка A1 содержит дату 16 февраля 2010 года, формула возвращает 47, потому что эта дата является 47-м днем в году.

Следующая формула возвращает количество дней, оставшихся в году с момента определенной даты (предполагается, что она содержится в ячейке A1): =ДАТА(ГОД(A1);12;31) .

Определение дня недели

Если вам необходимо определить день недели для даты, функция ДЕНЬНЕД справится с этой задачей. Функция принимает в качестве аргумента дату и возвращает целое число от 1 до 7, соответствующее дню недели. Следующая формула, например, возвращает 6, потому что первый день 2010 года приходится на пятницу: =ДЕНЬНЕД(ДАТА(2010;1;1)) .

Функция ДЕНЬНЕД использует еще и необязательный второй аргумент, обозначающий систему нумерации дней для результата. Если вы укажете 2 в качестве второго аргумента, то функция вернет 1 для понедельника, 2 — для вторника и т. д. Если же вы укажете 3 в качестве второго аргумента, то функция вернет 0 для понедельника, 1 — для вторника и т. д.

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

Определение даты последнего воскресенья

Формула в этом разделе возвращает последний указанный день. Вы можете использовать следующую формулу для получения даты прошлого воскресенья. Если текущий день — воскресенье, то формула возвращает текущую дату. Результатом будет серийный номер даты (вам нужно отформатировать ячейку для отображения читабельной даты): =СЕГОДНЯ()-ОСТАТ(СЕГОДНЯ()-1;7) .

Чтобы изменить эту формулу для нахождения даты другого дня, а не воскресенья, поменяйте 1 на другое число в диапазоне от 2 (понедельник) до 7 (суббота).

Определение дня недели после даты

Следующая формула возвращает указанный день недели, который наступает после определенной даты. Например, вы можете применять эту формулу для определения даты первой пятницы после 4 июля 2010 года. Формула предполагает, что ячейка А1 содержит дату, а ячейка А2 — число от 1 до 7 (1 соответствует воскресенью, 2 — понедельнику и т. д.): =A1+A2-ДЕНЬНЕД(A1)+(А2 .

Если ячейка А1 содержит 4 июля. 2010, а ячейка А2 содержит б (что обозначает пятницу), то формула возвращает 9 июля, 2010. Это первая пятница после 4 июля 2010 года (дня, который приходится на воскресенье).

Нахождение n-го определенного дня недели в месяце

Вам может понадобиться формула для нахождения даты определенного по счету дня недели. Предположим, что день выплаты зарплаты в вашей компании приходится на вторую пятницу каждого месяца и вам нужно определить эти дни выплат для каждого месяца года. Следующая формула выполнит требуемый расчет: =ДАТА(А1;А2;1)+А3-ДЕНЬНЕД(ДАТА(А1;А2;1))+(А4-(А3>=ДЕНЬНЕД(ДАТА(А1;А2;1))))*7

Эта формула предполагает, что:

  • ячейка А1 содержит год;
  • ячейка А2 содержит месяц;
  • ячейка A3 содержит номер дня (1 — воскресенье, 2 — понедельник и т. д.);
  • ячейка А4 содержит число — например 2, указывающее второе появление дня недели, заданного в ячейке A3.

При использовании этой формулы для определения даты второй пятницы в июне 2010 года результатом будет 11 июня, 2010.

Определение последнего дня месяца

Чтобы определить дату, которой соответствует последний день месяца, вы можете использовать функцию ДАТА. Однако вам нужно увеличивать месяц на 1 и указывать в качестве значения дня 0. Другими словами, «0-й» день следующего месяца — это последний день текущего месяца.

Следующая формула предполагает, что дата хранится в ячейке А1. Формула возвращает дату, которой соответствует последний день месяца: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0) .

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

Определение квартала даты

Для финансовых отчетов может оказаться полезным представление информации по кварталам. Следующая формула возвращает целое число от 1 до 4, которое соответствует календарному кварталу для даты в ячейке А1: =ОКРУГЛ ВВЕРХ(МЕСЯЦ(A1)/3;0) . Эта формула делит номер месяца на 3, а затем округляет результат.

Примеры использования функций ДЕНЬ с ДЕНЬНЕД И ДНИ В Excel

​ недели, который соответствует​Можно написать разные​ командой сайта office-guru.ru​=NETWORKDAYS(A1,B1,E1:E2)​42​ дата 1 января​

​ по дню недели​ Вт, т.д.). Устанавливаем​Числовой​ дня недели для​для отображения результата в​ 1900 года (так​ отображаться название дня​ длились на протяжении​ таблицы Excel и​ выполнения расчета.​

Особенности синтаксической записи функции ДЕНЬ, ДЕНЬНЕД и ДНИ в Excel

​=ЧИСТРАБДНИ(A1;B1;E1:E2)​=ДНИ(A2;A3)​

​ 1900 г. имеет​

​ или, например, дату​ в столбце А​, выберите пункт​ указанной даты, можно​

​ днях – «d»;​

​в годах – «y»;​ при преобразовании даты​

​ на строке формул​

​ в Excel –​

  1. ​ из ячеек содержит​ между датами выполняется​ имеет особенности, описанные​ из диапазона от​ ячейках В6 и​Можно​ЧИСТРАБДНИ​
  2. ​ ячейке A2 и​ 39 448, так​ смотрите в статье​ заменим числа дат​Число​ воскресенье первым днем​в месяцах без учета​ 13.04.1987 получается число​ – сама дата.​ это число дней,​ данные в формате​ преобразование данных в​ в примечаниях к​ 1 до 7​ В7. Получилось так.​узнать дату в​(NETWORKDAYS).​ начальной датой в​ как интервал между​

​ «Узнать дату в​ на день недели.​.​ недели.​ лет – «ym»;​ 31880. То есть​Аксёнов дмитрий​

​ прошедших начиная от​ Дата, соответствующие дате​ числа в коде​

​ предыдущей функции.​ (единица соответствует воскресенью,​В ячейке В6​

​Excel​Excel хранит даты в​ ячейке A3 (364).​ этими датами составляет​ Excel по дню​ Выделяем ячейки столбца​В поле​Для отображения порядкового номера​

​в днях без учета​ от 1.01.1900 прошло​: =ТЕКСТ (СЕГОДНЯ ();​ даты 01.01.1900г. Подробнее​ получения посылки. Необходимо​

​ времени Excel. Это​

  1. ​Тип – число в​ а семь –​ стоит формула, написанная​
  2. ​на любой день​ виде чисел и​364​ 39 447 дней.​

​ недели, т.д.» тут.​

  1. ​ А (А3:А10). Заходим​Числовые форматы​ недели для указанной​ месяцев и лет​ 31 880 дней.​ «дддд») результат: «Среда»​ описано в статье:​ определить, соответствует ли​
  2. ​ означает, что записи​ диапазоне от 1​ субботе).​ в ячейке С6.​ недели, которая наступит​ подсчитывает количество дней​К началу страницы​
  3. ​Если оба аргумента числовые,​ Таким способом можно​ в формат ячеек,​выделите пункт​ даты применяется функция​ – «md»;​
  4. ​Этот принцип лежит в​=ТЕКСТ (СЕГОДНЯ ();​ Как вычислить дату​ указанная дата рабочему​ «=ДНИ(“24.06.2018”;”13.06.2018”)» и «=ДАТАЗНАЧ(“24.06.2018”)-ДАТАЗНАЧ(“13.06.2018”)»​ до 3, указывающее​Функция ДНИ возвращает числовое​В ячейке В7​

Примеры расчетов в Excel с использованием функций ДЕНЬ, ДЕНЬНЕД и ДНИ

​ с 0 января​Функция ДЕНЬНЕД​ функция ДНИ вычитает​ узнать дату и​ выбираем функцию «Все​(все форматы)​ НОМНЕДЕЛИ:​в днях без учета​ основе расчетов временных​ «ддд») результат: «Ср»​ в Excel? Поэтому​ дню недели (с​ вернут одинаковые значения.​ на то, какой​ значение, характеризующее разницу​ стоит формула, написанная​ дней или была​

​ 1900 года. Вместо​Функция ЧИСТРАБДНИ​

​ из конечной даты​ будущюу и прошедшую.​ форматы».​и в поле​Дата 24.05.2015 приходится на​

Определение рабочих и выходных дней по формуле ДЕНЬНЕД

​ лет – «yd».​ данных. Чтобы найти​=ТЕКСТ (СЕГОДНЯ ();​ можно свободно использовать​ Пн по Пт)​Если один из параметров​ день недели считается​ дней между двумя​ в ячейке С7.​ некоторое время назад,​ того, чтобы подставлять​Функция РАБДЕНЬ​ начальную для получения​Как сложить даты,​В строке «Тип»​Тип​ 22 неделю в​В некоторых версиях Excel​

​ количество дней между​ «дд») результат: «06»​

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

​введите значение​ году. Неделя начинается​ при использовании последних​ двумя датами, достаточно​Отображение дат в​ без функций: =B3-A3.​ значение “true” или​ записанную как текст,​ странах первый день​

​Функция ДЕНЬ имеет следующий​ дату по дню​

​ пригодиться при планировании​ формулу, подставьте константы​ узнаете, как получить​

Зачем нужна функция ДНИ в Excel?

​ ними.​ дни, месяцы, года,​ ячеек» пишем такой​дддд​ с воскресенья (по​ двух аргументов («md»,​ от более позднего​ виде дней недели​

​Как видно на рисунке​ “false” (если выходной​

​ то он будет​ недели – воскресенье,​

​ синтаксис:​

​ недели. Но в​ поставок, сроков работ,​ чисел, которые представляют​

​ день недели по​Если один из аргументов​ как посчитать стаж​ формат – ДДД.​, чтобы отображались полные​ умолчанию).​ «yd») функция может​ временного периода отнять​Abram pupkin​ результат аналогичен. Фактически​ день) для дальнейшей​

​ обработан функцией ДАТАЗНАЧ,​ в нашей –​=ДЕНЬ(дата)​ Excel можно проделать​ при оформлении договора,​

exceltable.com>

Примеры функций для работы с датами: ГОД, МЕСЯЦ и ДЕНЬ в Excel

​ задано, берется американский​Задача оператора​ в его настройках.​ в перечне формул​ окну аргументов конкретной​ временными данными. Дата​НОМНЕДЕЛИ.ISO​Как определить по​, чтобы отображались сокращенные​(NETWORKDAYS)​ ;)​:​ на данный момент​

Примеры использования функций для обработки даты в Excel

​ 1 до 7,​ «Вт» отобржалось «ВТ»​ простой отчет по​ может быть заменен​ способ расчета. В​ДЕНЬНЕД​ Синтаксис​Мастера функций​ формулы из группы​ и время зачастую​Возвращает номер недели по​ дате номер квартала,​ названия (Пн, Вт,​описанной выше.​Во-вторых, с датами и​

​Время вводится в ячейки​ (см. ГОСТ ИСО​

  • ​ которое Excel преобразует​
  • ​ (с заглавными буквами)​
  • ​ продажам:​

​ на «дата_в_числовом_формате». В​ большинстве случаев он​– выводить в​ТДАТА​, а значит, его​«Дата и время»​ проставляется при оформлении​ ISO для заданной​ начало и конец​ Ср и т. д.).​Вас не в понедельник​ временем в Excel​ с использованием двоеточия.​ 8601-2001). Чтобы посчитать​

​ в ДАТУ от​ или допустим «ВТОРН»​Нам нужно быстро организовать​ примерах ниже, вы​ как раз и​

​ указанную ячейку значение​такой:​

​ значения всегда приходится​без активации главного​ различных журналов событий​ даты.​ квартала, читайте в​Для выполнения этой задачи​ родили? Нет? Уверены?​ возможно выполнять любые​ Например​ номер недели по​ 01/01/1900 до 07/01/1900,​ или «VTORNIK», то​ данные для визуального​ сможете наглядно увидеть​ подходит, так что​ дня недели для​=ТДАТА()​ вводить не через​ окна Мастера функций.​ в Экселе. Проводить​МИНУТЫ​ статье «Номер квартала​

​ используется функция ТЕКСТ.​ Можно легко проверить​ математические операции. Именно​16:45​ дате по такой​ которые, в свою​ что я хочу.​ анализа без использования​ это

Важно помнить,​ чаще всего этот​ заданной даты. Но​Очень похож на предыдущую​ графический интерфейс, а​ Для этого выполняем​ обработку таких данных​Преобразует дату в числовом​ в Excel».​Скопируйте указанные ниже данные​

​ при помощи функции​ потому, что на​По желанию можно дополнительно​ системе, можно использовать​ очередь, являются вторником​

​Serge_007​ сводных таблиц. Для​

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

​ вручную, придерживаясь следующего​ перемещение во вкладку​ – это главная​

​ формате в минуты.​Как определить дату​ на пустой лист.​

​ДЕНЬНЕД​ самом деле они​

​ уточнить количество секунд​ вот такую формулу:​ — понедельником соответственно.​: Это не изменение​ этого приведем отчет​

​ даты в качестве​ не нужно. Синтаксис​ текстовое название дня,​ возможностям оператор​

​ синтаксиса:​«Формулы»​ задача вышеуказанных операторов.​МЕСЯЦ​ по дню недели​1​(WEEKDAY)​

​ — числа! А​ — вводя их​=ОТБР(ОСТАТ(A1+3-ДЕНЬНЕД(A1;2);365,25)/7+1)​См. файл.​ дней недели​ в таблицу где​

​ текста (автоматическая ориентация​ принимает такой вид:​ а его порядковый​

​СЕГОДНЯ​=РАЗНДАТ(нач_дата;кон_дата;единица)​

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

​ Давайте разберемся, где​Преобразует дату в числовом​ или, например, дату​2​из категории​

​ вот это уже​ также через двоеточие:​=TRUNC(MOD(A1+3-WEEKDAY(A1;2);365,25)/7+1)​

​Вам не нужно​Mark17​ можно удобно и​ по левому краю​=ДОЛЯГОДА(нач_дата;кон_дата;)​ номер. Причем точка​. Он также не​Из контекста понятно, что​«Дата и время»​ можно найти эту​

​ формате в месяцы.​ третьего воскресенья марта​3​Дата и время​ раскрывает перед пользователем​16:45:30​В Excel 2013 функцию​

​ писать ДЕНЬНЕД, Вам​: мне нужно изменение​ быстро группировать данные​ ячейки), функция ГОД​Мы прошлись только по​ отсчета первого дня​ имеет аргументов. Но​

​ в качестве аргументов​. Она размещена на​ группу функций в​ЧИСТРАБДНИ​ 2016 г, т.д.,​

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

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

exceltable.com>

Пользовательское форматирование даты

​ длиной чисел. Вы​​ Не удается изменить​ Предварительный просмотр вашего​ 7-ми функциях Excel,​ только день недели,​ хранятся в виде​ желаемый цвет заливки​Дата и время​Здесь ситуация чуть сложнее.​ ячейки), удерживая​ минуты и секунды​ в формате США​ Excel».​В строке «Новый​ статью Вычисление с​ недели);​ ячейки и формат​ можете также перетащить​ формат даты, поэтому​ формата вместе с​ которые позволяют извлекать​ или только месяц​

​ порядковых чисел, началом​ для ячеек. Например,​.​​ Необходимо не учитывать​​правую​ соответственно.​ и ряда других​Преобразовать дату в текст​ текст» ничего не​ датами до 01.01.1900.​»д.М.гг»; ​​ значений. Подробнее об​​ правую границу столбца​ не обращайте влияя.​ первой датой в​ нужные параметры из​ и год, или​ отсчета которых принято​ зеленый. И нажмите​Первый аргумент этой функции​ субботы с воскресеньями​кнопку мыши, то​Чтобы прибавить часы, минуты​​ стран, где сперва​​ Excel.​​ пишем, нажимаем один​​Здесь рассмотрим, как​»дд.ММ.гг Ч:мм»;​ этих форматах, чем​

Выбор из списка форматов даты

​ до необходимой ширины.​

  1. ​ Внесенные вами изменения​ данных будет доступен​

  2. ​ значений дат и​

    ​ же только время.​ считать некое​ ОК на всех​

  3. ​ — ячейка с​​ и праздники. Для​​ можно выбрать -​​ и/или секунды, используйте​​ идут месяцы, а​

  4. ​Чтобы​​ раз на пробел,​​преобразовать в​​»ч:мм:сс AM/PM»;​​ они отличаются, как​

  5. ​Чтобы быстро ввести текущую​​ будут применяются только​​ в поле​ времени. На этом​Получить доступ ко всем​0 января 1900 года​ открытых окнах.​ датой, второй -​​ такого расчета лучше​​ как именно копировать​

    ​ функцию​​ затем дни. Тип​преобразовать дату в текст​ п.ч. ничего не​Excel текст в дату​»ДД МММ ГГГГ»;​ настроить нужный формат,​ дату, выберите любую​ к пользовательский формат,​

  6. ​Образец​ урок закончен. Всего​ возможным форматам Вы​. К счастью, в​Как видно на рисунке​​ тип отсчета дней​

​ воспользоваться функцией​​ выделенную дату:​ВРЕМЯ​ формата зависит от​ Excel​ должно стоять после​и​СОВЕТ:​ т.д., читайте в​ пустую ячейку на​ который вы создаете.​.​ Вам доброго и​ можете на вкладке​ ячейках мы видим​ все даты в​ недели (самый удобный​ЧИСТРАБДНИ​

Создание пользовательского формата даты

​Если Вам часто приходится​​(TIME):​​ ваших региональных настроек​(установить текстовый формат,​ цифр.​дату в текст Excel​Подробнее о пользовательских​ статье «Преобразовать дату​ листе, нажмите сочетание​

​ «ТЕКСТ» в Excel.​

​ условия, как должно​

​ «наследство» таблицу, в​ одноименной статье Пользовательский​Как работать с​

​ затем, если необходимо,​

​с помощью кода​ будут изменены при​Примечание:​

​. В категории​

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

​ выше, такое же​

​. В качестве аргументов​ это делать с​

​Excel прибавит 2​

​ даты и времени.​Функция «Текст» в​

​ быть написано число​

​ которой даты написаны​ формат даты и​ датами в Excel,​

​ нажмите клавишу ВВОД.​

​ из таблицы, приведенной​ изменении формата отображения​Мы стараемся как​

​Дата​

​ храниться в самых​ =ЕТЕКСТ(), у которой​

​ число, как дата,​

​ этой функции необходимо​ помощью всплывающего календаря:​

​ часа, 10+1 =​

​Чтобы получить год даты,​Excel​ по формату «Дата»,​

​ текстом, мы не​

​ времени.​ менять формат, считать,​Чтобы ввести дату, которая​

​ ниже.​

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

Советы по отображению дат

  • ​ используйте функцию​преобразует формат значения​ в который будем​ сможем отсортировать данные​В EXCEL дате соответствует​

  • ​ прибавлять дни к​ будет обновляться до​Для отображения​ на панели управления.​​ вас актуальными справочными​​ дат:​ этом уроке Вы​ аргумент – ссылка​ его часть, то​ конечную даты и​ ячейке всегда была​ 70-60 = 10​ГОД​ ячейки в текстовый​ преобразовывать это значение.​ по дате. В​ целое положительное число,​

  • ​ дате, вычитать, т.д.,​ текущей даты при​Используйте код​ Форматы без звездочки​ материалами на вашем​А в категории​ узнаете, как вводить​ на проверяемую ячейку.​

  • ​ с временем также​ ячейки с датами​ актуальная сегодняшняя дата​ секунд.​(YEAR):​ формат (число будет​ Нажимаем «ОК».​​ ячейках есть формат​​ а времени суток​ смотрите в статье​

support.office.com>

Номер дня недели через оператор ДЕНЬНЕД

Есть еще одна функция для выполнения поставленной задачи – это оператор ДЕНЬНЕД, однако, при его использовании будет выводиться не название дня недели, а его порядковый номер. Более того, вовсе не обязательно, чтобы понедельник был под номером 1, так как порядок нумерации дней недели устанавливается пользователем.

Для лучшего понимания давайте попробуем применить это метод на практике.

  1. Как всегда, для начала нам нужна ячейка с датой. Кликаем по другой ячейке, куда будет выводиться результат. После этого жмем кнопку “Вставить функцию”.
  2. В открывшемся окне вставки функций выбираем категорию “Дата и время” и из перечня операторов – “ДЕНЬНЕД”. Подтверждаем действие нажатием OK.
  3. Откроется окно настройки функции, формула которой имеет следующий вид: =ДЕНЬНЕД(дата;). Как мы видим, у нее тоже 2 аргумента, значения которых требуется указать.
    • В поле “Дата” печатаем нужную дату или указываем координаты ячейки, содержащей дату.
    • В поле “Тип” указываем день недели, с которого будет производиться отсчет. Значениями этого аргумента могут быть только цифры 1, 2 или 3:
      • 1 – первым днем недели будет воскресенье
      • 2 — отсчет начнется с понедельника
      • 3 — как и в случае с цифрой “2”, отсчет начнется с понедельника, но ему будет присвоена цифра “0”.
      • если аргумент “Тип” не будет указан, по умолчанию ему будет присвоено значение “1”, т.е. первым днем недели будет считаться воскресенье. Нам же привычнее вести отсчет с понедельника, поэтому ставим значение “2” и завершаем настройку нажатием кнопки OK.
  4. В ячейку с функцией будет отображен результат в виде числа, соответствующего дню недели. В нашем случае день недели – пятница, и ему присвоен порядковый номер “5” согласно выбранному нами варианту отсчета.
  5. Если изменить дату в исходной ячейке, то в конечной также будут автоматически внесены изменения.

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

Как посчитать проценты от суммы в Excel

Видео урок:

Базово, рассчитать процент от суммы в Эксель можно по формуле:

(Часть/Целое) * 100 = Процент (%)

Но если использовать формат ячейки “Процентный”, то для вычисления процента от числа достаточно поделить одно число на другое. Например, у нас есть яблоки, которые мы купили по 100 руб. на оптовом складе, а розничную цену выставим 150 руб. Для того чтобы высчитать процент того, сколько составляет закупочная цена от розничной, нам потребуется:

Составить таблицу с ценами и добавить колонку для вычисления величины процента:

В ячейку D2 внести формулу, вычисляющую процент цены закупки от цены продажи:

=C2/B2

Применить формат ячейки D2 “Процентный”:

Как посчитать процент от суммы значений таблицы Excel

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

Для этого нам нужно:

  • Добавить колонку к таблице с расчетом процента;
  • В ячейку C2 вставить формулу:

=B2/$B$9

Значки $ фиксируют колонку “B” и ячейку “9” для того, чтобы при протягивании формулы на все строки таблицы, Excel автоматически подставлял объем продаж каждого продавца и высчитывал % от общего объема продаж. Если не поставить значки “$”, то при протягивании формулы, система будет смещать ячейку B9 на столько ячеек вниз, на сколько вы протяните формулу.

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

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

Как вычислить процент нескольких значений от суммы таблицы

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

Из списка товаров выше мы хотим вычислить какой объем продаж составляют помидоры (они записаны в нескольких строках таблицы). Для этого:

Справа от таблицы укажем товар (Помидоры), по которым хотим рассчитать долю в продажах:

  • Сменим формат ячейки E2 на “Процентный”;
  • В ячейку E2 вставим формулу с функцией СУММЕСЛИ, которая поможет вычислить из списка товаров Помидоры и суммировать их объем продаж, а затем поделить его на общий объем продаж товаров:

=СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9

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

Для расчетов мы используем формулу СУММЕСЛИ. Эта функция возвращает сумму чисел, указанных в качестве аргументов и отвечающих заданным в формуле критериям.

Синтаксис функции СУММЕСЛИ:

=СУММЕСЛИ(диапазон; условие; )

  • диапазон – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
  • условие – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
  • диапазон_суммирования – суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент диапазонв качестве диапазон_суммирования.

Таким образом, в формуле =СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9 мы указали “ $A$2:$A$8 ” как диапазон товаров, среди которых функция будет искать нужный нам критерий (Помидоры). Ячейка “ $E$1 ” указана в качестве критерия и указывает что мы ищем “Помидоры”. Диапазон ячеек “ $B$2:$B$8 ” обозначает какие ячейки нужно суммировать, в случае если искомый критерий был найден.

Анализ дебиторской задолженности до текущей даты в Excel

Пример 3. Предприятию был выставлен долг со следующими условиями:

  1. За каждый день просрочки начисляются штрафы по ставке 5% от текущей суммы долга плюс начисленные проценты в день.
  2. По истечению 20-дневного срока с момента первого начисления штрафа будет проведено слушание в суде, проценты начисляться не будут.

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

Исходная таблица данных:

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

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

В качестве показателя степени используется выражение ДНИ(СЕГОДНЯ();B4), которое определяет количество прошедших дней от даты выставления долга до сегодняшней даты.

Поскольку сумма задолженности будет расти на протяжении 20 дней, используем следующий вариант записи:

Таким образом, была добавлена проверка условия, прошло ли 20 и более дней с момента выставления долга. Если да, сумма долга будет фиксированной и рассчитана по формуле B2*СТЕПЕНЬ((1+B3);20).

Результат вычислений:

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

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

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

Чтобы узнать, сколько будних дней между двумя точками в столбцах A и B, используйте следующую формулу:

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

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

А если у нас свой особенный график труда и отдыха?

Если вам нужно обрабатывать какие-то особенные нерабочие дни (например, отдыхаем только в воскресенье, в понедельник и воскресенье и т.д), используйте функцию ЧИСТРАБДНИ.МЕЖД, которая позволяет указать, какие дни недели предназначены для отдыха.

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

Формула ЧИСТРАБДНИ.МЕЖД  появилась впервые в Excel 2010, и поэтому недоступна в более ранних версиях программы.

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

Первые два аргумента являются обязательными и встречались нам уже неоднократно:

Дата_начала — начальная точка.

Дни — будние дней до (отрицательное значение) или после (положительное значение) точки начала. Если этот аргумент в результате каких-то расчетов предоставляется в виде десятичного числа, то он усекается до целого.

Последние два аргумента являются необязательными:

Выходные — указывает, в какие дни недели вы не работаете. Это может быть либо число, либо текст, как показано ниже.

Число

Выходные дни

1 или отсутствует

Суббота Воскресенье

2

Воскресенье понедельник

3

Понедельник вторник

4

Вторник Среда

5

Среда Четверг

6

Четверг Пятница

7

Пятница Суббота

11

Только воскресенье

12

Только понедельник

13

Только вторник

14

Только среда

15

Только четверг

16

Только в пятницу

17

Только суббота

Текст для указания выходного дня — серия цифр из семи нулей и единиц, представляющих семь дней недели, начиная с понедельника. 1 означает день отдыха, а 0 — будний день. Например:

  • «0000011» — суббота и воскресенье – не работаем.
  • «1000001» — понедельник и воскресенье — отдыхаем.

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

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

А теперь несколько примеров.

=ЧИСТРАБДНИ.МЕЖД(A10;B10;1) – это знакомый нам вариант, когда нерабочими считаются суббота и воскресенье.

А вот =ЧИСТРАБДНИ(A10;B10;11) – отдых только в воскресенье,

=ЧИСТРАБДНИ.МЕЖД(A10;B10;2) – в воскресенье и понедельник. Такой расчет полезен, если в вашей организации свой график работы, не такой, как у всех.

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

Вы можете объединить полученное число со своим собственным сообщением, склеив их при помощи оператора &.

Например, давайте посмотрим, сколько нужно еще отработать до конца 2020 года. Для этого введем 31 декабря 2020 года в формате даты, и используем следующее выражение:

Ух, осталось всего 203 дня! Не так много, как я думал :)Примечание. Чтобы учесть свой индивидуальный график работы, используйте ЧИСТРАБДНИ.МЕЖД().

Сколько вам будет лет в определенный год?

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

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

Если предположить, что ДР указан в столбце B, начиная со строки 2, а год последнего медицинского осмотра находится в столбце C, то расчет выглядит следующим образом:

Поскольку точный срок медицинского осмотра не определен, вы используете функцию ДАТА с произвольным аргументом дня и месяца, например, DATА(C3; 1; 1).

Функция ДАТА извлекает номер года из ячейки С2, создает полную дату, используя введенные вами номера месяца и дня (в этом примере 1 января), и передает ее в РАЗНДАТ. В результате вы получаете сумму прожитых пациентом лет по состоянию на 1 января определенного года.

Как посчитать разницу в процентах в Excel

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

Например, для расчета разницы в процентах между двумя числами А и В используется формула:

(B-A)/A = Разница между двумя числами в процентах

На практике, при использовании этой формулы важно определить какое из нужных вам чисел является “А”, а какое “В”. Например, представим что вчера у вас было в наличии 8 яблок, а сегодня стало 10 яблок

Таким образом количество яблок относительно вчерашнего у вас изменилось на 25% в большую сторону. В том случае, если у вас вчера было 10 яблок, а сегодня стало 8 яблок, то количество яблок, относительно вчерашнего дня сократилось на 20%.

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

Как найти процент между двумя числами из двух колонок в Excel

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

Для расчетов нам потребуется формула:

=(C2-B2)/B2

  • Протяните формулу на все строки со значениями;
  • Измените формат ячейки для колонки “Изменение, %” на “Процентный”.

Как найти процент между двумя числами из двух строк в Excel

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

=(B3-B2)/B2

Где B2 это первая строчка, B3 вторая строчка с данными. После ввода формулы не забудьте отформатировать ячейки с вычислениями как “Процентные”. Выполнив все выше описанные действия, вы получите результат:

Если у вас есть необходимость вычислить изменения относительно какой-то конкретной ячейки, то важно зафиксировать ее значками “$”. Например, если перед нами стоит задача вычислить изменения объема продаж относительно Января, то формула будет такой:. =(B3-B2)/$B$2

=(B3-B2)/$B$2

На примере выше значения продаж каждого месяца сравниваются с данными продаж Января.

Оцените статью
Рейтинг автора
5
Материал подготовил
Андрей Измаилов
Наш эксперт
Написано статей
116
Добавить комментарий