Вопрос или проблема
Следующая формула выглядит так, как будто она должна работать, но я продолжаю получать ошибки.
=IF(S4=1,AVERAGEIFS($Q$4:$Q$64,$S$4:$S$64,1,ROW($S$4:$S$64),”>=”&ROWS($S$4:$S$64)-19),AVERAGEIFS($Q$4:$Q$64,$S$4:$S$64,0,ROW($S$4:$S$64),”>=”&ROWS($S$4:$S$64)-9))
Ячейки Q4-Q64 содержат дневную выручку за последние 60 дней, S4-S64 имеют значение 1 для каждого рабочего дня и 0 для каждого выходного или праздничного дня. Если это рабочий день, нужно взять последние 20 рабочих дней для среднего значения. Если это выходной или праздничный день, нужно взять последние 10 выходных или праздничных дней для среднего значения.
.
Ответ или решение
Определение и устранение ошибок в формуле AVERAGEIFS с множественными критериями
Ваша задача — рассчитать среднюю выручку за определенные периоды на основе рабочих и выходных дней/праздников. Чтобы корректно разобрать вашу формулу, сначала рассмотрим, что именно она пытается достичь, а затем попробуем исправить возможные ошибки.
Формула:
=IF(S4=1, AVERAGEIFS($Q$4:$Q$64, $S$4:$S$64, 1, ROW($S$4:$S$64), ">=ROWS($S$4:$S$64)-19"), AVERAGEIFS($Q$4:$Q$64, $S$4:$S$64, 0, ROW($S$4:$S$64), ">=ROWS($S$4:$S$64)-9"))Проблемы и Решения:
- Функция ROW():
Проблема может заключаться в использовании функции ROW() в качестве критерия. ROW() возвращает номер строки листа, что может вызывать несоответствие с другими значениями, находящимися в диапазоне.
Решение: Возможно, следует использовать более прямое решение для нахождения данных за последние рабочие или выходные дни, используя INDIRECT() или OFFSET() для корректной индексации.
- Использование текстового сравнения:
Ваш критерий ">=ROWS($S$4:$S$64)-19" представлен в виде текста, что, скорее всего, приводит к логической ошибке, так как не является корректным элементом условия.
Решение: Попробуйте провести вычисления отдельно от AVERAGEIFS, возможно, в отдельных ячейках, и заменить текстовые ссылки на вычисленные значения.
Предлагаемое исправление:
Для более эффективного подхода, используйте следующие шаги:
-
Предварительная подготовка данных:
Используйте столбцы, чтобы упростить ввод данных за последние 20 рабочих дней и последние 10 выходных. Например, с помощью формулы-фильтра можно создать отдельные диапазоны только для рабочих и выходных дней.
-
Оптимизация формулы:
- Разделите вашу логику на две отдельные ячейки для разных условий (рабочие дни и выходные).
- Примените OFFSET() или INDIRECT() для выделения последних 20 бизнес-дней и 10 выходных из вашего диапазона.
Улучшенная формула:
=IF(S4=1, AVERAGE(OFFSET($Q$4, COUNTIF($S$4:$S$64, 1)-20, 0, 20)), AVERAGE(OFFSET($Q$4, COUNTIF($S$4:$S$64, 0)-10, 0, 10)))Заключение:
Этот подход предполагает создание более простых промежуточных значений, которые помогут в большей степени собрать значение для расчета средней выручки. Проверьте, все ли формулы корректно учитывают все условия. Надеюсь, это поможет устранить возникшие ошибки в вашем Excel-листе.