000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти первую или последнюю пятницу каждого месяца в Excel?

По сути, эта формула использует функцию ДЕНЬНЕД для проверки количества дат, чтобы увидеть, попадают ли они в данный день недели (день_нед), и функцию СУММПРОИЗВ, чтобы подвести итог.
При задании даты ДЕНЬНЕД просто возвращает число от 1 до 7, которое соответствует определенному дню недели. По умолчанию 1 = воскресенье и 7 = суббота.
Для наших «широт и менталитета» устанавливаем второй аргумент функции ДЕНЬНЕД = 2.

Только тогда 1 = понедельник, 7 = воскрсенье и так далее.
Уловка этой формулы заключается в понимании того, что даты в Excel — это просто порядковые номера, начинающиеся 1 января 1900г. Например, 1 мая 2021г. — это серийный номер 44317, а 9 мая 2021г. — 44325.
Даты в Excel выглядят как даты только тогда, когда применяется числовой формат даты.
Возникает вопрос: как создать массив дат, который можно передать функции ДЕНЬНЕД, чтобы узнать соответствующие дни недели?
Ответ — использовать СТРОКА с функциями ДВССЫЛ следующим образом:

Когда цепочки дат имеют номера строк, функция DWSSYL интерпретирует их как строки. Это то, что возвращает функция STRING:

Прибавить рабочие дни исключая празники к дате в таблице Excel

  • A2 — дата начала;
  • B2 — количество работников;
  • C2 — данные по нерабочим дням недели;
  • D2 — красные календарные дни.

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

2. Определение дня недели с помощью оператора ТЕКСТ

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

Читайте так же:
Как округлить время до ближайшего часа / минуты / секунды в Excel?

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

  1. Итак, у нас есть определенная дата в таблице. Выбираем другую ячейку, где планируется отобразить название дня недели и отмечаем ее мышью. Далее нажимаем кнопку “Вставить функцию” (слева от строки формул).Определение дня недели с помощью оператора ТЕКСТ
  2. В открывшемся окне щелкаем по текущей категории функций и в раскрывшемся перечне кликаем по строке “Текстовые”.Определение дня недели с помощью оператора ТЕКСТ
  3. Из предложенного списка функций нам нужен “ТЕКСТ”, щелкаем по нему и далее жмем кнопку OK.Определение дня недели с помощью оператора ТЕКСТ

Определение дня недели с помощью оператора ТЕКСТ

  • «Значение» — это дата, день недели, из которого вы хотите извлечь данные. В этом поле можно ввести дату вручную или указать координаты ячейки. Для этого нужно щелкнуть в поле ввода значения, а затем щелкнуть в нужной ячейке с датой.
  • В поле «Формат» введите желаемый вариант дня недели: «dddd» — полное название, «ddddd» — сокращение. Когда вы закончите, нажмите OK.

Способ 3: считаем рабочие дни

Для расчета точного количества рабочих дней в определенном периоде используется функция ЧИСЛО ДНЕЙ().

Для иллюстрации рассмотрим следующую таблицу. В столбце A у нас начало месяца или контрольная дата, а в столбце B — конец месяца или контрольная дата. Поскольку будние дни исключают субботы и воскресенья, а также праздники в месяцах, мы заполним столбец C этими соответствующими датами.

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

Пример

Затем поставьте «=» и напишите функцию. Для меня это выглядит следующим образом:

В качестве аргументов указываются начальная и конечная даты (A5, B5). Последний аргумент — праздники ( C5 ). Разделите их с помощью «;» .

Читайте так же:
Как найти самую последнюю или самую раннюю дату в Excel?

ЧИСТРАБДНИ()

Нажав «Enter», выводится результат, в примере ячейки D5 — 21 дней.

Рассмотрим случай, когда месяц имеет несколько праздников. Например, Новый год и Рождество приходятся на январь. Выделите ячейку ( D6 ) и поставьте в ней знак равенства. Затем нажмите на букву «f» в строке формул. Перед вами откроется окно «Вставка функции». Выберите опцию «Полный алфавитный список» на вкладке «Категория» и найдите в списке нужную функцию. Нажмите «ОК» .

Список функций

Дальше необходимо выбрать аргументы функции. В «Нач_дата» выбираем начальное значение ( А6 ), в «Кон_дата» – конечное ( В6 ). В последнем поле вписываем даты праздников в скобках <> и кавычках «» . Потом нажимайте «ОК» .

Аргументы

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

D6

При указании диапазона дат в соответствующем поле вам не нужно будет вводить праздники вручную. В моем случае это C6:C7 .

Диапазон

Рабочие дни будут подсчитаны, и именно так будут выглядеть функции:

22 дня

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

В феврале были организованы 19 рабочих дней.

Последний месяц

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

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

Создание календаря из шаблона

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

Читайте так же:
Как определить или выделить истекшие или предстоящие даты в Excel?

Шаг 1: Для начала работы запустите программу и откройте меню «Файл».

Открываем в меню «Файл»

Шаг 2. Перейдите в раздел «Создать». Теперь воспользуйтесь поиском или нажмите на ссылку «Календари», чтобы просмотреть все доступные варианты.

Переходим в раздел «Создать», нажимаем на ссылку «Календари»

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

В разделе «Категория» выбираем подходящий нам шаблон календаря

Шаг 3. Щелкните по миниатюре понравившегося вам шаблона. Скачайте его, нажав на кнопку «Создать».

Щелкаем по миниатюре понравившегося шаблона, нажимаем на кнопку «Создать»

Шаг 4: Выбранный шаблон откроется в новом окне.

Шаблон выбранного календаря откроется в программе Excel

Отредактируйте его по своему усмотрению, добавьте свои данные и т.д. Теперь это будет ваш личный календарь с дополнительными функциями.

Меняем календарь по своему усмотрению, вносим свои данные

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

Есть смысл загрузить этот документ на Google Docs. Это облачное хранилище файлов любого типа со встроенным пакетом программ, аналогичным Microsoft Office. Загрузив его на облако, вы сможете получить доступ к своему календарю в любое время с любого компьютера, редактировать его онлайн самостоятельно и вместе с другими людьми, но самый важный плюс — это автосохранение, любые действия с таблицей будут сохраняться системой.

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

  1. Создайте список последовательных дат в Excel с помощью маркера заполнения пустого места
  2. Получите список всех дат между двумя указанными датами в Excel с помощью формулы.

1]Создайте список последовательных дат в Excel с помощью маркера заполнения.

Создайте список последовательных дат в Excel с помощью маркера заполнения

Функция «Заполнить дескриптор» в Microsoft Excel позволяет легко создать список последовательных дат. Это гораздо более простой способ сравнения дат между двумя датами, чем использование формулы, хотя он не очень помогает при сравнении дат между двумя датами.

Читайте так же:
Как округлить / округлить / округлить время до ближайшей четверти / получаса в Excel?

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

Потяните перо заливки вниз, чтобы создать список последовательных дат в программе Excel.

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

В Excel есть небольшая формула, которая выдает список всех дат между двумя заданными датами. Приведу пример:

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

После ввода формулы нажмите Enter:

Дата появляется рядом с начальной датой в ячейке C1.

Список всех дат между двумя датами в Excel

Введите следующую дату в ячейке C2:

Введите формулу в C1

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

Конечный результат

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

Решением этой проблемы может быть перетаскивание, вырезание и вставка.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector