СУММПРОДУКТ занимает слишком много времени
У меня есть эти данные: -
Это то, чего я хочу достичь: -
При выборе клиента как «ABC» соответствующая сумма будет отображаться с января по июнь.
Я могу добиться этого с помощью SUMPRODUCT, но обработка занимает много времени, так как мои данные содержат более 10 000 строк. Есть ли более эффективный способ сделать это с помощью SUMIFS и т.д.?
Спасибо
Формулой можно поделиться?
Что находится в B1:G1? Даты или текст? Аналогично, что есть в K3::K14?
Это Даты.
@Bob Какую версию Excel вы используете?
МсОфис 2019...
@ Боб, у вас было 2 ответа, оба из которых должны были сделать предположения о вашей настройке, оба из которых сработали бы, если бы эти предположения были правильными, ни один из которых не работает для вас (согласно вашим комментариям). Очевидно, вы что-то недоговариваете. Пришло время добавить больше деталей к вашему вопросу: формула, которая у вас есть сейчас, правильное определение ваших выборочных данных, какие результаты вы на самом деле получили из формул ответов и т. д.
Ответы 2
Попробуйте следующую формулу:
=SUMIF($A$2:$A$9,$B$14,INDEX($B$2:$G$9,,MATCH($A15,$B$1:$G$1,0)))
Если это истинная дата, попробуйте формулу массива ниже. Excel365 не требует ввода массива.
=SUMIF($A$2:$A$9,$B$14,INDEX($B$2:$G$9,,MATCH(MONTH($A15),MONTH($B$1:$G$1),0)))
Нажмите CTRL+SHIFT+ENTER, чтобы оценить формулу, поскольку это формула массива.
Обновлено: если ваши данные структурированы так же, как предоставленный вами образец, то приведенная ниже формула будет более эффективной.
=SUMIFS(INDEX(B:G,,ROW($A1)),A:A,$J$1)
Отлично, это работает лучше, чем SUMPRODUCT. Можно ли выбрать всю строку вместо предустановки, например, от $A$2:SA$9 до A:A или от $B$2:$G$9 до B:G? В моих данных много строк, и они меняются ежедневно.
@Bob Если ваши данные хорошо структурированы, вы можете обрабатывать их более эффективно. Смотрите мой отредактированный ответ. Если вы нашли его работающим, пожалуйста, отметьте его как ответ (отметьте галочкой).
Пробовал это, но это не сработает, если я изменю mth - вместо января на июнь, теперь июнь на январь. Числа остаются прежними.
Можете ли вы поделиться образцом рабочей книги через Google Drive.
Вот функция, которую вы можете вставить в L2 в своем примере и скопировать до декабря.
=SUMIF(INDEX(Data,0,1),$L$1,INDEX(Data,0,MATCH(K2,Months,0)))
Как видите, у него есть 2 именованных диапазона, Data и Months.
Dataсостоит из всех строк и столбцов данных, A1: G9 в опубликованном вами примере. Я думаю, вы захотите сделать этот диапазон динамическим, то есть он должен автоматически изменять размер при добавлении или удалении данных. Можно сделать это формула, но это не эффективно. Поэтому вы делаете это в объявлении имени. Если вам нужна помощь с этим, пожалуйста, не спрашивайте здесь. Это другая тема: "динамические именованные диапазоны".Monthsдолжно иметь столько же столбцов, сколько «Данные», не ограничиваясь 12. Важно, чтобы он начинался в том же столбце, что иData, и чтобы его первое поле не содержало дату, которая может стать критерием поиска функции.
Столбец K также может иметь более 12 столбцов. В моей настройке я использовал пользовательский формат чисел mmm. Поэтому 1/1/21 и 1/1/22 будут отображаться одинаково, но функция ПОИСКПОЗ может различить их.
Я поместил дату начала в B1 и =EDATE(B1,1) в C1, которую скопировал справа. Точно так же K2 имеет =B1, K3 =EDATE(K2,1), который копируется вниз. Это гарантирует, что заголовки столбцов и строк выровнены.
Он не показывает правильный вывод. Он показывает январь = 0 (правильное значение 99), февраль = 99 (правильное значение 100), март = 100 (правильное значение 104), апрель = 104 (правильное значение 107).
Оно делает. Если он показывает неправильный, вы не настроили его правильно. Я предполагаю, что ваш диапазон Months не начинается с A1, но вы можете проверить с помощью =СУММ(Данные,0,2)), где 2 – столбец данных, а =ПОИСКПОЗ(K2, Месяцы,0), который должен возвращать номер столбца для суммирования. Таким образом, вы можете узнать, где исправить вашу настройку.
Я думаю, что СУММЕСЛИМН() - правильный путь. Пожалуйста, попробуйте и опубликуйте свои усилия, если это не сработает.