Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Timi от 15.09.2015, 08:31

Название: Суммировать данные в зависимости от фильтра - DAX - вопрос снят
Отправлено: Timi от 15.09.2015, 08:31
Добрый день!

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

Надеюсь, пример во вложении сделает вопрос более понятным.
Если есть возможность, поделитесь каким-нибудь незаурядным источником информации о применении SUMPRODUCT для таблиц.

Соответственно, SQL и VBA не предлагать.
Заранее спасибо.

Тема переименована. В следующий раз сами нормально называйте, хорошо? [МОДЕРАТОР]
Название: Re: Суммировать данные в зависимости от фильтра
Отправлено: cheshiki1 от 15.09.2015, 09:15
изучайте, проверяйте
=СУММПРОИЗВ((ИНДЕКС(Data;;ПОИСКПОЗ($D$11;Data[#Заголовки];0))=$E$11)*(Data[Criteria 3]=D14)*(Data[Criteria 4]=E14)*(Data[Criteria 5]=F14)*ИНДЕКС(Data;;ПОИСКПОЗ(""&G13&"";Data[#Заголовки];0)))
Название: Re: Суммировать данные в зависимости от фильтра
Отправлено: Czeslaw от 15.09.2015, 16:00
Смотрите приложение.
Название: Re: Суммировать данные в зависимости от фильтра
Отправлено: vikttur от 15.09.2015, 16:16
Czeslaw, нужно учитывать значение двух первых столбцов

Эта шустрее за СУММПРОИЗВ:
=СУММЕСЛИМН(ИНДЕКС($G$3:$L$9;;ПОИСКПОЗ($G$13;$G$2:$L$2;));ИНДЕКС($B$3:$C$9;;1+($D$11=$C$2));$E$11;$D$3:$D$9;D14;$E$3:$E$9;E14;$F$3:$F$9;F14)
Название: Re: Суммировать данные в зависимости от фильтра
Отправлено: Timi от 16.09.2015, 17:23
cheshiki1 , всё сработало! Хорошее решение! Никак не мог с синтаксисом разобраться, теперь всё понятно.
Czeslaw , как отметил vikttur , ваше решение не учитывает выбора одного из двух критериев и соответствующего значения для него.
vikttur , решение работает, но необходимо было решение через таблицы и sumproduct
В любом случае, всем спасибо за решения!
Название: Re: Суммировать данные в зависимости от фильтра
Отправлено: Timi от 20.11.2015, 12:50
Цитата: cheshiki1 от 15.09.2015, 09:15
изучайте, проверяйте
=СУММПРОИЗВ((ИНДЕКС(Data;;ПОИСКПОЗ($D$11;Data[#Заголовки];0))=$E$11)*(Data[Criteria 3]=D14)*(Data[Criteria 4]=E14)*(Data[Criteria 5]=F14)*ИНДЕКС(Data;;ПОИСКПОЗ(""&G13&"";Data[#Заголовки];0)))

cheshiki1, помните эту тему? Ваше решение работает. (см. файл). Но на большом объеме данных такой подход, естественно, сильно замедляет скорость обработки данных.
Вопрос: возможно ли как-то перенести это решение в DAX?
И, если не сложно, подскажите более-менее приличные ресурсы, где можно изучить DAX для PowerPivot...первичный поиск по сети даёт только поверхностную информацию...

Заранее спасибо!
Название: Re: Суммировать данные в зависимости от фильтра - DAX
Отправлено: Timi от 23.11.2015, 11:18
Вопрос снят!
В основном потому, что DAX - это отдельная история и его нужно учить и практиковаться.
Что я и сделал.
Вот тут:
Основы DAX для Power Pivot (на английском, но всё понятно и наглядно):
http://www.lynda.com/Excel-tutorials/Excel-Power-Pivot-DAX-Depth/374607-2.html (http://www.lynda.com/Excel-tutorials/Excel-Power-Pivot-DAX-Depth/374607-2.html) продолжительность курса - 2 часа 25 минут на нормально скорости, но можно быстрее

и ещё несколько по теме и вокруг неё:
http://www.lynda.com/Excel-tutorials/Data-Modeling-Excel-Power-Pivot/186871-2.html (http://www.lynda.com/Excel-tutorials/Data-Modeling-Excel-Power-Pivot/186871-2.html) 1 час 15 минут
http://www.lynda.com/Access-tutorials/Relational-Database-Fundamentals/145932-2.html (http://www.lynda.com/Access-tutorials/Relational-Database-Fundamentals/145932-2.html) 2 часа 40 минут
http://www.lynda.com/Excel-tutorials/Creating-Excel-Power-Pivot-Power-View-Dashboards/186873-2.html (http://www.lynda.com/Excel-tutorials/Creating-Excel-Power-Pivot-Power-View-Dashboards/186873-2.html) 1 час 12 минут

Ресурс не бесплатный, но после регистрации 10 дней бесплатно. Вполне достаточно, чтобы освоить необходимую тему. Там ещё куча всякого добра.
Приятного просмотра!