Вопрос или проблема
Я пытаюсь сделать сумму с произведением с диапазоном дат и я очень нов в использовании массивов. Моя формула обычно имеет 115 строк и 30 столбцов, но для устранения неполадок я уменьшил её:
=SUMPRODUCT(('History'!$G$3:$I$4)*('History'!$C$3:$C$4='Calculator'!C8)*('History'!G1:I1="Order")*('History'!G2:I2&">="&'Calculator'!H2)*('History'!G2:I2&"<="&'Calculator'!H4))
Первая часть работает нормально, но когда я дохожу до ('History'!G2:I2&">="&'Calculator'!H2)*('History'!G2:I2&"<="&'Calculator'!H4)), я получаю #Value! в окне оценки формулы.
H2 & H4 — это даты, которые находят конкретные даты на странице Истории, основываясь на нескольких других условиях. Я дважды проверял все секции, которые касаются дат, и они отформатированы правильно.
Я протестировал Sumifs только с датами, чтобы выяснить, действительно ли это даты, и получил правильное значение, которое мне нужно. Sumifs('History'!G3:I3,'History'!G1:I1,"Order", 'History'!G2:I2,">="&'Calculator'!H2,'History'!G2:I2,"<="&'Calculator'!H4) = 1
Пожалуйста, дайте знать, если нужна дополнительная информация. Спасибо 🙂
Ответ или решение
Чтобы правильно использовать функцию SUMPRODUCT в Excel с учетом диапазона дат, важно понимать, как работать с массивами и условиями. В вашем вопросе описана ситуация, когда функция возвращает ошибку #VALUE! при использовании условий с датами. Давайте разберем ваш запрос более подробно и предложим исправление.
Проблема и анализ
Ваша формула:
=SUMPRODUCT(('History'!$G$3:$I$4)*('History'!$C$3:$C$4='Calculator'!C8)*('History'!G1:I1="Order")*('History'!G2:I2&">="&'Calculator'!H2)*('History'!G2:I2&"<="&'Calculator'!H4))Основные части формулы:
('History'!$C$3:$C$4='Calculator'!C8)— выбирает строки, где значение соответствует указанному.('History'!G1:I1="Order")— фильтрует столбцы с учетом необходимой метки.('History'!G2:I2&">="&'Calculator'!H2)и('History'!G2:I2&"<="&'Calculator'!H4)— эти части вызывают ошибку.
Ошибка возникает потому, что вы пытаетесь объединить массив с текстом, а Excel не может правильно обработать это в контексте математических операций. Вместо этого нужно использовать логическое сравнение без операторов конкатенации:
Правильный подход
Для корректной работы с условиями для дат вам следует использовать логические операторы. Исправленная версия формулы будет выглядеть следующим образом:
=SUMPRODUCT(('History'!$G$3:$I$4)*('History'!$C$3:$C$4='Calculator'!C8)*('History'!G1:I1="Order")*('History'!G2:I2>='Calculator'!H2)*('History'!G2:I2<='Calculator'!H4))Пояснение исправлений
-
Убраны знаки конкатенации (&) для требований по ранжированию дат. Теперь вы напрямую сравниваете значения ячеек с условиями
>=и<=. -
Убедитесь, что диапазоны имеют одинаковую структуру. Например, если вы используете G2:I2, убедитесь, что
H2иH4представляют даты и не содержат дополнительных пробелов или неверных форматов.
Дополнительные советы
- Проверьте форматирование ячеек с датами в диапазоне ‘History’ и в ‘Calculator’. Убедитесь, что эти ячейки имеют формат даты.
- Запустите тестовый вариант формул на небольшом наборе данных, чтобы подтвердить правильность работы.
- Используйте функцию
ISNUMBERдля проверки правильности преобразований значений, если проверка возвращает числа или даты.
Следуя этим шагам, вы сможете использовать SUMPRODUCT для суммирования значений в заданном диапазоне с учетом условий по датам. Если у вас возникнут дополнительные вопросы или нужны будут уточнения, не стесняйтесь спрашивать.