Использование номера недели как одного из критериев в формуле (Microsoft Excel)

Дэйв пытается создать формулу СУММЕСЛИМН, которая использует год как один критерий, а номер недели как другой критерий. Это позволит ему, например, суммировать все значения за определенную неделю в предыдущем году. Критерий номера недели должен основываться на номере недели, в пределах которого находится текущая дата. Таким образом, если сегодня находится в пределах недели 3, формула должна включать недели 1, 2 и 3 любого года, который он указывает. Дэйв уверен, что это можно сделать, но он не может придумать, как правильно выразить это в СУММЕСЛИМН.

Используя функцию рабочего листа СУММЕСЛИМН, важно точно понимать, что функции нужно в качестве параметров. При работе с двумя критериями (как хочет Дейв — год и номер недели) синтаксис формулы, основанной на СУММЕСЛИМН, будет выглядеть следующим образом:

В описании Дэйва у него есть ряд дат и ряд значений, связанных с этими датами. Параметр values_to_sum, очевидно, взят из тех значений, которые есть у Дейва, но в его данных нет значений years_to_compare и weeks_to_compare. Таким образом, они должны быть созданы во вспомогательных столбцах.

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

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

Скопируйте эти формулы вниз так, чтобы для каждой даты, отображаемой в столбце A, был связан номер года и недели. Имея эти вспомогательные столбцы, у вас есть все элементы данных, необходимые для работы с функцией СУММЕСЛИМН, описанной ранее. Чего у вас все еще нет, так это индикатора того, какой год вы хотите извлечь из значений. Это легко исправить, поместив год в ячейку F2. Теперь запрошенная формула будет выглядеть следующим образом:

Если вы сравните эту формулу с примером синтаксиса, приведенным ранее, вы заметите, что у вас есть values_to_sum (B: B), years_to_compare (C: C), year_criterion (F2), weeks_to_compare (D: D) и наконец, week_criterion. Это последний элемент, который необходимо немного пояснить; это выглядит так:

Поскольку функция WEEKNUM возвращает любой номер недели, подходящий (в данном случае) для сегодняшней даты, она может вернуть что-то вроде «7». Таким образом, week_criterion будет выглядеть так:

Это означает, что СУММЕСЛИМН будет учитывать только те номера недель в данных, которые меньше или равны 7.

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

В качестве последнего предостережения при использовании этой формулы вы должны точно понимать, как вы хотите рассчитать номер недели. В примерах в этом совете использовалась простейшая версия функции WEEKNUM как в столбце D, так и в формуле СУММЕСЛИМН. Есть параметры, которые можно использовать с WEEKNUM, чтобы настроить его работу. В некоторых случаях вы можете вместо этого рассчитать номер недели по ISO. Более подробную информацию о номерах недель WEEKNUM и ISO можно найти в этих советах:

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12964) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.