Рабочие дни в табеле в зависимости от месяца в excel: Табель учета рабочего времени в excel ExcelLab.ru

ЧИСТРАБДНИ (функция ЧИСТРАБДНИ) — Служба поддержки Майкрософт

Excel

Формулы и функции

Другие функции

Другие функции

ЧИСТРАБДНИ (функция ЧИСТРАБДНИ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функции ЧИСТРАБДНИ в Microsoft Excel.

Описание

Возвращает количество рабочих дней между датами «нач_дата» и «кон_дата». Праздники и выходные в это число не включаются. Функцию ЧИСТРАБДНИ можно использовать для вычисления заработной платы работника на основе количества дней, отработанных в указанный период.

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

Синтаксис

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

Аргументы функции ЧИСТРАБДНИ описаны ниже.

• нач_дата    — обязательный аргумент. Начальная дата.

• кон_дата    Обязательный. Конечная дата.

• Праздники

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

Важно:  Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2012 г. воспользуйтесь выражением ДАТА(2012,5,23). Если ввести даты как текст, это может привести к возникновению проблем.

Замечания

• В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. имеет порядковый номер 1, а 1 января 2012 г. — 40909, так как от 1 января 1900 г.

  он отс0 до 40 909 дней.

• Если какой-либо из аргументов не является допустимой датой, networkDAYS возвращает #VALUE! значение ошибки #ЗНАЧ!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Дата	Описание
01. 10.2012	Дата начала проекта
01.03.2013	Дата окончания проекта
22.11.2012	Праздник
04.12.2012	Праздник
21.01.2012	Праздник
Формула	Описание	Результат
=ЧИСТРАБДНИ(A2;A3)	Число рабочих дней между датой начала (01. 10.2012) и датой окончания (01.03.2013).	110
=ЧИСТРАБДНИ(A2;A3;A4)	Число рабочих дней между датой начала (01.10.2012) и датой окончания (01.03.2013) с вычетом праздничного дня 22.11.2012 как выходного.	109
=ЧИСТРАБДНИ(A2;A3;A4:A6)	Число рабочих дней между датой начала (01.10.2012) и датой окончания (01.03.2013) с вычетом трех праздничных дней.	107

К началу страницы

Функция РАБДЕНЬ — Служба поддержки Майкрософт

Excel

Формулы и функции

Другие функции

Другие функции

Функция РАБДЕНЬ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. ..Меньше

В этой статье описаны синтаксис формулы и использование функции РАБДЕНЬ в Microsoft Excel.

Описание

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

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

Синтаксис

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

Аргументы функции РАБДЕНЬ описаны ниже.

• нач_дата    — обязательный аргумент. Начальная дата.

• Количество_дней    Обязательный. Количество дней до или после начальной даты, не являющихся выходными или праздниками. Положительное значение аргумента «количество_дней» обозначает дату в будущем, отрицательное — дату в прошлом.

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

Важно: Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем.

Замечания

• В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

• Если какой-либо из аргументов не является допустимой датой, то workDAY возвращает #VALUE! значение ошибки #ЗНАЧ!.

• Если start_date плюс дн. возвращает недействительные даты, то workDAY возвращает #NUM! значение ошибки #ЗНАЧ!.

• Если значение аргумента «количество_дней» не является целым числом, оно усекается.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные
01. 10.2008	Начальная дата
151	Осталось дней
26.11.2008	Праздник
04.12.2008	Праздник
21.01.2009	Праздник
Формула	Описание (результат)	Результат
=РАБДЕНЬ(A2;A3)	Отсчитывает 151 рабочий день с начальной даты (30. 04.09)	30.04.2009
=РАБДЕНЬ(A2;A3;A4:A6)	Отсчитывает 151 рабочий день с начальной даты, исключая праздники (05.05.09)	05.05.2009

Шаблон калькулятора расписаний Excel [БЕСПЛАТНАЯ ЗАГРУЗКА]

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

Я часто получаю вопросы о том, как рассчитать обычные и сверхурочные часы сотрудников на основе показателей «Время работы» и «Время отсутствия».

Я также не смог найти хороший шаблон табеля учета рабочего времени в Excel в Интернете, поэтому я решил его сделать.

Щелкните здесь, чтобы загрузить Excel Timesheet Calculator

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

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

Кроме того, когда вы распечатываете этот шаблон расписания, он идеально помещается на одной странице.

В этом руководстве рассматриваются:

Шаблон калькулятора расписания Excel

Вот снимок шаблона еженедельного расписания Excel:

Как только вы введете «Время работы» и «Время выхода», шаблон автоматически рассчитает обычные и сверхурочные часы.

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

Этот шаблон Excel доступен в трех форматах: Еженедельный Табель учета рабочего времени, Двухнедельный Табель учета рабочего времени и Ежемесячный Табель учета рабочего времени (предоставляется в виде различных вкладок в загружаемом файле).

Как использовать этот шаблон калькулятора расписания Excel

Вот шаги для использования этого шаблона расписания Excel:

• Выберите дату начала недели.

• Укажите выходные. Вы можете выбрать один из различных вариантов в раскрывающемся списке. Возможные варианты: без выходных, 1-дневные выходные (пн, вт…) или 2-дневные выходные (пт. и сб., сб. и вс..). Как только вы выберете выходные, эти дни будут заштрихованы красным цветом в расписании.

• Укажите время начала, количество обычных часов и почасовую ставку (обычную и сверхурочную). Время начала должно быть в формате чч:мм (24-часовой формат). Например, 6 утра будет 06:00, а 6 вечера будет 18:00.

• Если все выходные дни должны рассматриваться как сверхурочные, установите флажок. Если этот флажок не установлен, выходные дни также будут разделены на обычные и сверхурочные часы.

• Введите время входа и выхода для даты и часы перерыва (если есть). В этом табеле учета рабочего времени Excel есть формулы, которые автоматически рассчитывают общее количество обычных часов и сверхурочных часов (OT).
  • Обратите внимание, что часы перерыва автоматически вычитаются из обычных часов.

Несколько моментов, о которых следует помнить при использовании этого шаблона расписания Excel:

• Существует встроенная проверка, чтобы убедиться, что «Вовремя» не позднее, чем «Время выхода». В таком случае шаблон не позволит пользователю ввести время. [Это стало возможным благодаря правилам проверки данных] .
• Если рабочая смена сотрудника или члена команды продолжается до следующего дня (например, начинается в 18:00 и заканчивается в 6:00 на другой день), убедитесь, что время дня 1 — с 18:00 до 24:00, а время дня 2 время с 0:00 до 6:00.
• Не изменять никакие формулы в табеле учета рабочего времени. Делайте записи только в столбцах «Вовремя» и «Время выхода». При удалении записей удаляйте их только из столбцов «Время», «Время выхода» и «Часы перерыва».
• Я изменил поля страницы, чтобы при печати она помещалась на одном листе.

Что пошло на создание этого шаблона Excel Timesheet Calculator

• Формулы Excel: ряд функций Excel, таких как ДАТА, ПОИСКПОЗ, ЦЕЛОЕ, ЕСЛИ и ЕСЛИОШИБКА, используется для вычисления значений (таких как дата из выбора или обычная/ сверхурочные часы) в этом шаблоне табеля учета рабочего времени.
• Раскрывающийся список Excel: позволяет пользователю выбрать название месяца.
• Флажок — позволяет пользователю указать, должны ли выходные оплачиваться по ставке за сверхурочную работу или нет.
• Именованные диапазоны/: они используются для ссылки на данные в бэкэнде (на вкладке данных).
• Условное форматирование.: Используется для выделения строк, когда заданная дата является выходным днем.

Загрузить шаблон калькулятора расписания Excel

Если вы хотите создать шаблон Excel, сообщите мне об этом в разделе комментариев.

Часто задаваемые вопросы (FAQ)

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

  В:  Я вижу только шаблон еженедельного расписания. Где двухмесячные и ежемесячные шаблоны?
Ответ: Все три шаблона табеля учета рабочего времени я предоставил в виде отдельных вкладок. Вы найдете другие шаблоны, нажав на вкладку для шаблона (вкладки были названы соответственно).
  В:  Что делать, если я хочу отслеживать время нескольких сотрудников с помощью этого шаблона табеля учета рабочего времени. Как мне это сделать?
Ответ: этот шаблон сделан для одного человека на листе. Если вам нужно отслеживать нескольких сотрудников или членов команды, в этом случае вам необходимо создать несколько листов. 
  В:  Можно ли записать несколько листов в один и тот же шаблон?
Ответ: Этот шаблон предназначен только для одной временной смены, которую вы можете указать, указав время начала и количество обычных часов.
 В:  Могу ли я распечатать этот шаблон расписания?
Ответ: Да, эти шаблоны табелей умещаются на одной странице при печати. Вы можете перейти в «Файл», а затем нажать «Печать» или использовать сочетание клавиш Control + P. Это откроет страницу предварительного просмотра печати.
  В:  В шаблоне ежемесячного расписания, когда я выбираю 1 февраля в качестве даты начала, почему все еще отображаются даты из следующего месяца?
Ответ: Ежемесячный табель составляется таким образом, чтобы в общей сложности охватить 31 день. Таким образом, он покажет вам 31 день, начиная с даты, которую вы указали. 

Вам также могут пригодиться следующие шаблоны Excel:

• Календарь, интегрированный со списком дел
• Расчет времени в Excel (разница во времени, отработанные часы, сложение/вычитание)
• Список дел в Excel (4 шаблона).
• Шаблон календаря праздников (только для США).
• Управление проектами – Отслеживание отпусков сотрудников.
• Калькулятор общих расходов.
• Шаблон маршрута отпуска и упаковочного листа.
• Шаблон календаря Excel (ежемесячный и годовой)

Если вы ищете онлайн-калькулятор учета рабочего времени, проверьте это.

Табель рабочего времени

Автор: Оскар Кронквист Последнее обновление статьи: 07 февраля 2023 г.

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

На приведенном выше рисунке показан январь 2017 года. Просто введите название проекта в столбце A и часы в диапазоне ячеек B5:AF32. Строка 4 содержит даты января, строка 3 — дни недели, а строка 2 — номера недель. Суббота и воскресенье окрашены в серый цвет.

Что на этой странице

1. Как это работает
2. Как я построил этот тайм-трекер
   1. Строительные ежемесячные листы
   2. Выделите выходные
   3. Шаблон сетки — условное форматирование
   4. Создать оставшиеся месяцы
   5. Рабочий лист итогов
3. Получить файл Excel здесь

1.

Как это работает

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

Простая функция СУММЕСЛИ суммирует значения из каждого ежемесячного листа, Excel позаботится и об этом. В этой книге нет VBA, вы можете найти ссылку для получения в конце этой статьи.

Если вы измените год на листе «Сводка», вы заметите, что ежемесячные листы меняются соответственно, вам не нужно менять дни недели, номер недели или цвет выходных дней на каждом листе, Excel сделает это за вас.

Есть предложения по улучшению?

Вернуться к началу

2. Как я создал этот тайм-трекер

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

Вернуться к началу

2.

1 Построение месячных листов

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

Ячейка D1 содержит следующую формулу: =Summary!h2
Это позволяет изменить год в итоговом листе, и все другие ежемесячные листы будут немедленно обновлены.

Формула в ячейке I1: =DATE(D1,1,1)
Выберите ячейку I1 и нажмите CTRL + 1, чтобы открыть диалоговое окно форматирования ячеек. Измените категорию форматирования на «Пользовательский».

Изменить тип на MMMM; @
Если это не работает, вам нужно узнать региональные настройки в Windows и соответствующим образом изменить MMMM. На этой веб-странице Microsoft объясняется, как использовать диалоговое окно форматирования.

Вот как теперь выглядит ячейка I1:

Время добавить даты, введите от 1 до 31 в диапазоне ячеек B4:Ah5. Я также изменяю ширину ячейки на 21 пиксель. Введите «Проект» в ячейку A4, ячейки ниже содержат названия проектов.

Добавьте текст «Всего:» в ячейку AG4 и используйте эту формулу в ячейке AG5:=СУММ(B5:AF5)
Скопируйте ячейку AG5 и вставьте в диапазон ячеек AG6:AG32. См. рисунок ниже.

Добавьте текст «Всего:» в ячейку A33 и используйте эту формулу в ячейке B33:=СУММ(B5:B32)
Скопируйте ячейку A33 и вставьте в диапазон ячеек B33:AF33. См. рисунок выше.

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

=ЕСЛИ(МЕСЯЦ($B$4)=МЕСЯЦ(B4), ВЫБЕРИТЕ(ДЕНЬНЕД(B4,2),»M»,»T»,»W»,»T» ,»Ф»,»С»,»С»),»»). См. рисунок ниже.

Я также хочу, чтобы строка над днями недели содержала номер недели. Формула в ячейке B2:

=WEEKNUM(B4,1)

Но мне нужно только это число выше даты начала новой недели.

Формула в ячейке C2:

=ЕСЛИ((НОМЕР НЕДЕЛИ(C4,1)<>НОМЕР НЕДЕЛИ(B4,1))*(МЕСЯЦ($B$4)=МЕСЯЦ(B4)), НОМЕР НЕДЕЛИ(C4,1), «»)

Скопируйте ячейку C2 и вставьте в D2:AF2. См. рисунок ниже.

В начало

2.2 Выделение выходных

Следующая формула условного форматирования выделяет выходные серым цветом:

(ДЕНЬНЕД(4,2 B$)>5)*(МЕСЯЦ(4B$)=МЕСЯЦ(4B$) )

1. Выберите диапазон ячеек B4:AF32
2. Перейти на вкладку Главная на ленте
3. Нажмите левой кнопкой мыши на кнопку «Условное форматирование», а затем нажмите левой кнопкой мыши на «Новое правило..»
4. Нажмите левой кнопкой мыши на «Использовать формулу, чтобы определить, какие ячейки форматировать»
5. Вставьте приведенную выше формулу в поле «Форматировать значения, где эта формула верна:»
   
6. Нажмите левой кнопкой мыши на кнопку «Формат»
7. Перейти на вкладку «Заполнить»
8. Выберите цвет
   
9. Нажмите левой кнопкой мыши на кнопку OK

Рабочий лист «Январь» теперь выглядит так:

Условное форматирование меняется в зависимости от месяца и года, это не то, что вам нужно делать вручную.

В начало

2.3 Шаблон сетки — условное форматирование

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

=ЕСЛИ($AI$3=»Выкл»,МЕСЯЦ(B$4)= МЕСЯЦ($B$4))

Чтобы построить формулу форматирования, повторите описанные выше шаги с 1 по 8, за исключением того, что вместо выбора цвета перейдите на вкладку «Граница» и нажмите левой кнопкой мыши на «Контур», затем нажмите левой кнопкой мыши на кнопку OK. См. рисунок ниже.

Вернуться к началу

2.4 Создание оставшихся месяцев

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

1. Нажать правую кнопку мыши на рабочем листе «Январь».
2. Нажмите левой кнопкой мыши на «Переместить или скопировать…».
3. Выберите «Переместить в конец» и «Создать копию».
4. Нажмите левой кнопкой мыши на кнопку OK.

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

1. Переименовать лист после января в февраль
2. Измените формулу в ячейке I1 на =DATE(D1,2,1)
   Обратите внимание, что февраль — это второй месяц в году, а второй аргумент в формуле равен 2.

Повторите шаги 1 и 2 выше для всех оставшихся рабочих листов.

К началу страницы

2.5 Таблица итогов

Формула в ячейке B4:

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА! $A$5:$A$32 , MATCH(0 , COUNTIF(Summary!$B$2:B3 , Январь!$A$5:$A$32&»») , 0)) , ИНДЕКС(Фев!$A$5:$A $32 , MATCH(0 , COUNTIF(Summary!$B$2:B3 , Feb!$A$5:$A$32&»») , 0))) , INDEX(Мар!$A$5:$A$32 , MATCH(0 , СЧЁТЕСЛИ(Сводка!$B$2:B3 , Март!$A$5:$A$32&»») , 0))) , ИНДЕКС(Апр!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Сводка! $B$2:B3 , Апр!$A$5:$A$32&»») , 0))) , ИНДЕКС(Май!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , Май!$A$5:$A$32&»») , 0))) , ИНДЕКС(Июнь!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , Июнь!$A $5:$A$32&»») , 0))) , ИНДЕКС(Июль!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , Июль!$A$5:$A$32 &»») , 0))) , ИНДЕКС(авг!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , авг!$A$5:$A$32&»») , 0))) , ИНДЕКС(Сент!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , Сен!$A$5:$A$32&»») , 0))) , В DEX(октябрь!$A$5:$A$32 , MATCH(0 , СЧЁТЕСЛИ(Сводка!$B$2:B3 , окт!$A$5:$A$32&»») , 0))) , ИНДЕКС(ноябрь!$ A$5:$A$32 , MATCH(0 , COUNTIF(Summary!$B$2:B3 , Ноябрь!$A$5:$A$32&»») , 0))) , ИНДЕКС(Декабрь!$A$5:$A $32 , MATCH(0 , COUNTIF(Summary!$B$2:B3 , Dec!$A$5:$A$32&»») , 0))) , «»)

Объясняющая формула в ячейке B4

ИНДЕКС(Янв!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , Январь!$A$5:$A$32&»») , 0 ))

Шаг 1.

Проверка предыдущих значений над текущей ячейкой

Функция СЧЁТЕСЛИ вычисляет количество ячеек, соответствующих условию.

СЧЁТЕСЛИ( диапазон ,  критерии )

СЧЁТЕСЛИ(Сводка!$B$2:B3 , Январь!$A$5:$A$32&»»)

становится

СЧЁТЕСЛИ({0;»» }, {«Сп-1002»; «ТК-006»; «БР-4К3»; «ДК-1001»; «ГБ-458»; «»; «»; «»; «»; «»; «» ;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;» «})

и возвращает {0; 0; 0; 0; 0; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1}.

Обратите внимание, что функция СЧЁТЕСЛИ также возвращает 1 для пустых ячеек.

Шаг 2. Найдите относительное положение еще не показанного значения

Функция ПОИСКПОЗ возвращает относительное положение элемента в массиве или ссылке на ячейку, которая соответствует указанному значению в определенном порядке.

ПОИСКПОЗ( искомое_значение , искомое_массив , [ тип_сопоставления ])

Ищем значения, еще не отображенные в ячейках над текущей ячейкой. 0 (ноль) указывает, что он еще не был показан.

ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Сумма!$B$2:B3 , Январь!$A$5:$A$32&»») , 0)

становится

ПОИСКПОЗ(0 , {0; 0; 0; 0; 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}, 0 )

и возвращает 1.

Шаг 3. Возвращаемое значение

Функция ИНДЕКС возвращает значение из диапазона ячеек, которое вы указываете на основе номера строки и столбца.

ИНДЕКС( массив ,  [номер_строки] ,  [номер_столбца] )

ИНДЕКС(Янв!$A$5:$A$32 , ПОИСКПОЗ(0 , СЧЁТЕСЛИ(Сумма! $B$2! Янв3 $A$5:$A$32&»») , 0))

становится

ИНДЕКС(Ян!$A$5:$A$32, 1)

становится

ИНДЕКС({«Sp-1002»; «TK -006»; «БР-4К3»; «ДК-1001»; «ГБ-458»; «»; «»; «»; «»; «»; «»; «»; «»; «»; » «;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»;»»}, 1)

и возвращает «Sp -1002» в ячейке B4.

Шаг 4. Переход к следующему рабочему листу

Функция ЕСЛИОШИБКА позволяет использовать другую формулу, когда больше нет отображаемых значений. Такое поведение называется вложенными функциями и повторяется для включения всех рабочих листов.

ЕСЛИОШИБКА(ИНДЕКС(Ян!$A$5:$A$32 , СОВПАДЕНИЕ(0 , СЧЁТЕСЛИ(Итог!$B$2:B3 , Январь!$A$5:$A$32&»») , 0)) , ИНДЕКС( Feb!$A$5:$A$32 , MATCH(0 , COUNTIF(Summary!$B$2:B3 , Feb!$A$5:$A$32&»») , 0)))

Наверх

Шаблоны категория

Календарь — просмотр по месяцам
В этой статье описывается создание календаря, показывающего все дни выбранного месяца с соответствующими запланированными событиями. Что […]

Отображение диапазонов дат в календаре, часть 2
В этой статье я продемонстрирую календарь, который автоматически выделяет даты на основе диапазонов дат, календарь заполняет имена […]

График смен
Джефф спрашивает: Привет, Оскар! У меня есть перекрестная справочная таблица, которую мы используем для планирования смен. Ось X состоит из […]

Тепловая карта Годовой календарь
Календарь, показанный на изображении выше, выделяет события в зависимости от частоты. Производится лишь с несколькими условными […]

Шаблон Excel: Getting Things Done [VBA]
В этой статье я собираюсь продемонстрировать простую рабочую книгу, в которой вы можете создавать или удалять проекты и добавлять […]

Шаблон счета-фактуры с зависимыми выпадающими списками
В этой статье демонстрируется базовый шаблон счета-фактуры, который я создал. Это позволяет вам использовать выпадающие списки для быстрого выбора продуктов на […]

Выделение событий в годовом календаре
В этой статье показано, как выделить определенные диапазоны дат в годовом календаре, этот календарь позволяет изменить […]

Шаблон ежемесячного календаря
На изображении выше показан календарь, который является динамическим, то есть вы выбираете год и месяц, и календарь мгновенно обновляется […]

Шаблон недельного расписания
Я хочу поделиться этим простым еженедельным расписанием, которое я создал. Как пользоваться недельным расписанием Введите любую дату в ячейку […]

Бесплатный шаблон школьного расписания
Этот шаблон позволяет легко создать еженедельное школьное расписание, просто введите временные диапазоны и […]

Шаблон многоуровневого списка дел
Сегодня я поделюсь с вами шаблоном списка дел в Excel. Вы можете добавить текст на лист и […]

Базовый шаблон счета-фактуры
Ротанг спрашивает: В моей рабочей тетради есть три рабочих листа; «Покупатель», «Продавец» и «Оплата». В листе клиента у меня есть […]

Расширенный шаблон диаграммы Ганта
В этой диаграмме Ганта используется столбчатая диаграмма с накоплением для отображения задач и соответствующих им диапазонов дат. Завершенных дней […]

Создание ежемесячного табеля учета рабочего времени с помощью сводной таблицы
Сегодня я собираюсь продемонстрировать, насколько удивительны сводные таблицы! Взгляните на этот табель учета рабочего времени. Вы можете […]

Шаблон ежемесячного календаря #2
Я создал еще один шаблон ежемесячного календаря, который вы можете получить. Select a month and year in cells A1 and […]

Functions in this article

IFMONTHCHOOSEWEEKDAYWEEKNUMIFERRORINDEXMATCHCOUNTIF

More than 1300 Excel formulas

Excel formula categories

AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique отдельные записиУникальные отдельные значенияУникальные записиУникальные значенияПросмотр возвращаемых значений

Категории Excel

Домашняя страница

Последние обновленные статьи.

Функции Excel

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

Формулы Excel

Более 1300 формул, организованных в подкатегории.

Таблицы Excel

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

Расширенный фильтр

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

Проверка данных

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

Раскрывающийся список

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

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

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

Excel Solver

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

Диаграммы

Функция Excel, позволяющая визуализировать данные в виде графика.

Условное форматирование

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

Сводные таблицы

Позволяет быстро суммировать большие объемы данных в очень удобной для пользователя форме.