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

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

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

= СУММЕСЛИМН (values_to_sum, years_to_compare, year_criterion, weeks_to_compare , week_criterion)

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

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

= YEAR (A2)

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

= WEEKNUM (A2)

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

= СУММЕСЛИМН (B: B, C: C, F2, D: D, »

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

»

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

»

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

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

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

http://excelribbon.tips.net/T007804http://excelribbon.tips.net/T007847

Источник: frestage.ru

Бытовой вопрос