Среднее значение по 5 конкретным ячейкам, включая активный ряд, 2 выше /2 ниже пропущенных пробелов, а не включенные в диапазон

Question

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

В основном у меня есть годовая таблица, включая декабрь предыдущего года и январь следующего года. Приведенная здесь таблица представляет собой снимок того, как она будет выглядеть, средняя дневная цена 2-1-2, столбец "Дата" - это столбец А.

По сути, мне нужно ввести среднее значение за 5 дней из столбца среднесуточных значений за 5 потенциально несмежных дней. Мне нужно пропустить пустые ячейки, т. Е. Субботу и воскресенье, целиком и не включать их в какой-либо диапазон, но здесь, где у меня возникают проблемы, также необходимо пропустить определенные дни недели, то есть праздничные дни, когда цены не публикуются. Мне также нужно иметь возможность копировать / перетаскивать его во все ячейки столбца, например, декабрь 2017 года - январь 2019 года, на скользящей ежегодной основе с учетом государственных праздников, которые будут меняться каждый год или иметь возможность вносить простые изменения каждый год.

Я ввел вложенный оператор If, который возвращает 1 для всего, кроме субботы, воскресенья или праздничных дней, и 0 для всего, что есть. Имея ограниченные знания и тот факт, что я не смог найти что-то простое, чтобы исключить пробелы из столбца "Среднесуточное значение", это был / есть мой самый обнадеживающий вариант, но я не могу найти точную формулу, которая мне нужна, которая будет усреднять ячейку (я) для активная строка, а первые 2 сверху и снизу основаны на значении другого столбца.

Я нашел формулы в виде=IFERROR(SUM(A10;B13;C5;D6;D8)/((A10<>0)+(B13<>0)+(C5<>0)+(D6<>0)+(D8<>0));0),=AVERAGEIF(B1:B1:B3:B3:B5:B5:B7:B7,">0") на этом сайте &=AVERAGEIF($A$1:$A$500,D1,$B$1:$B$500) в другом месте, но проблема в том, что средние значения основаны на целых диапазонах, а не только на пяти, которые мне нужны. Я также нашел SUMPRODUCT не сделал много с этим, поскольку у этого, казалось, был подобный конечный результат.

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

Заранее спасибо и извинения за длину.

С Уважением,

Саймон

Answer 1

Вы можете поместить следующую формулу массива (crtl+shift+enter) в G3 (при условии на основе скриншота) и затем скопируйте:
{=AVERAGE(IF(ROW(F3:$F$90)<=SMALL(IF(F3:$F$90<>"";ROW(F3:$F$90));MIN(COUNT(F3:$F$90);5));IF(F3:$F$90<>"";F3:$F$90)))}

Некоторые комментарии:

• 5 жестко закодировано в формуле. Лучше было бы иметь это где-нибудь в ячейке, чтобы вы также могли рассчитать среднее значение за другие дни;
• Я предположил, что данные были в F3:$F$90;
• Он также вычислит среднее по пустым строкам, поэтому вам нужно добавить дополнительное, если вы предпочитаете, чтобы среднее также было пустым;
• Формула также работает для последних значений, она будет принимать все доступные значения, если их меньше 5.