Данные с базы без сводных таблиц?

[Анна Б.]

И снова здравствуйте!!

Подскажите, какую функцию использовать для извлечения данных с базы данных.
Сразу скажу, что не получается работать со сводными таблицами, т.к. _сводные_ диаграммы мне не подходят. Слишком много нюансов, которые нельзя автоматизировать.

Нашла формулу Бдсумм. Отличная формула, в принципе то что нужно, но есть минусы для моего примера, в том что мне нужно вытаскивать несколько срок по заданным критериям, и иногда эти критерии расположены не в одну строку.

Суммпроизв тоже не подходит, т.к. если один из критериев будет равен 0, то значение формулы тоже 0. наверное можно заменить через если, но получится очень огромная формула, так как критериев много.

Пример прикладываю, очень надеюсь на вашу помощь! Слева база данных (сокращенная версия) справа таблицы в которые нужно вытащить данные.
СПАСИБО)) 

P.S.  В общем хочу облегчить себе работу - хочу одну табличку, в которой можно было бы указать критерии, и построить диаграммы (более 100 шт...)

[Serge 007]

Суммпроизв не подходит, т.к. если один из критериев будет равен 0, то значение формулы тоже 0.

Это кто Вам такое сказал?

Во вложении два решения, сводной и СУММПРОИЗВ()
Сводной предпочтительнее, т.к. в разы быстрее

...свoдные диaграммы мне не подходят. Слишком много нюансов, которые нельзя автоматизировать...

Например?

[Анна Б.]

Со сводными таблицами люблю работать, если из них нужно быстро вытащить данные,
а вот потом построить на основании этих данных графики, диаграммы - это уже проблема (возможно для меня)))))... Вернее построить не проблема, но постоянно при обновлении слетает форматирование, а у нас с этим строго((( вплоть до "запятых". и настраивать заново не хочется.
Также построила уже почти все графики, и тут решила добавить вычисляемое поле в последний график, и всё.... всё слетело. бросила я это дело и построила все заново с помощью суммеслимн. Гораздо быстрее получилось. только тоже если критерий - условие равен 0, то 0

А про сумму произведений - прикладываю пример, наверное вначале не совсем правильно изложила.
У меня много диаграмм, и на каждой то раскрыть помесячно, то за квартал, то всех дистрибьюторов указать в сумме, то по отдельным дистрибьюторам и т.п.

[Serge 007]

...при обновлении слетает форматирование...

Про эту проблему слышал на форумах, но сам никогда с ней за много лет работы не сталкивался, хотя я пользуюсь только свoдными диaграммами. Так что тут посоветовать мне Вам нечего, разве что работать в 2003 Excel. Никто вроде не жаловался на 2003-й в этой части

По СУММПРОИЗВ(). Какую цифру в Вашем примере Вы ожидаете получить, задавая критерием НУЛЕВОЙ, т.е. отсутствующий в БД месяц? Очевидно что с таким критерием должен получится НОЛЬ, т.к. невозможно иметь какие-либо данные на дату, не входящую в период с января по декабрь, т.е. календарный год

[Анна Б.]

Т.е. формула должна вытащить все данные за квартал (за три месяца), иначе мне придется сделать три строки с январем, февралем и мартом,  а потом их сложить.
И по всем критериям так. Т.е. если не указывать дистрибьютора, то формула считает всех дистрибьюторов, если не указывать год, то сумма за все года по оставшимся критериям и т.д.

И таким образом я сделаю одну таблицу, скопирую ее и уже в ней буду оставлять необходимые критерии для построения графиков.
И еще одно важное условие, мне нужно на графике сравнивать двух дистрибьюторов (Product 2/Product 3, Product 4/Product 5 и ....), соответственно в шапке таблицы должны быть их названия, поэтому мне не подошла бдсумм.

Прикладываю картинку одной из диаграмм, может будет более понятно.

[Serge 007]

...формула должна вытащить все данные за квартал (за три месяца)...

Тогда зачем Вы задействуете поле "Месяц"?!
У Вас же есть поле "Квартал", вот с ним и работайте

...если не указывать дистрибьютора, то формула считает всех дистрибьюторов, если не указывать год, то сумма за все года по оставшимся критериям...

Ну так не указывайте неверные критерии

Вообще немного странная задача. Насколько я понял, Вы хотите указывать критерии, которые отсутствуют, но что бы формула их игнорировала? Так? Если так, то зачем это надо? Если не так, то как надо?

[Михаил С.]

Может так?

Код Выделить Развернуть

=СУММЕСЛИМН($H$2:$H$18;$A$2:$A$18;ЕСЛИ(J7="";"*";J7);$B$2:$B$18;ЕСЛИ(K7="";"*";K7);$C$2:$C$18;ЕСЛИ(L7="";"*";L7);$D$2:$D$18;ЕСЛИ(M7="";"*";M7);$E$2:$E$18;ЕСЛИ(N7="";"*";N7);$F$2:$F$18;ЕСЛИ(O7="";"*";O7);$G$2:$G$18;ЕСЛИ(P7="";"*";P7))

[Анна Б.]

Я собираю инфо ежемесячно, ежеквартально, и за год. слайдов в презентации примерно 60-80, на каждом слайде по 4 диаграммы.....

Поэтому если мне за месяц нужен свод, буду указывать месяц, если за квартал, то месяц указывать не буду.
И вот как раз здесь Вы правильно заметили, если критерий не указывать, то формула должна игнорировать это поле.
Хочется настроить таким образом, что бы таблица была универсальной для любого условия, т.е. в моем понимании это   сводная таблица настроенная в ручную). 

БДСУММ игнорирует поля, по которым условия равны 0, наверное все таки придется использовать эту формулу (несмотря на неудобства  )

[Анна Б.]

Может так?

Код Выделить Развернуть

=СУММЕСЛИМН($H$2:$H$18;$A$2:$A$18;ЕСЛИ(J7="";"*";J7);$B$2:$B$18;ЕСЛИ(K7="";"*";K7);$C$2:$C$18;ЕСЛИ(L7="";"*";L7);$D$2:$D$18;ЕСЛИ(M7="";"*";M7);$E$2:$E$18;ЕСЛИ(N7="";"*";N7);$F$2:$F$18;ЕСЛИ(O7="";"*";O7);$G$2:$G$18;ЕСЛИ(P7="";"*";P7))

Вот возможно подходящий вариант, щас попробую разобраться с "*", и попробую внедрить в свою базу 

[Анна Б.]

Изначально побоялась писать длинную формулу, но она же будет одна для всех!!!!

[Михаил С.]

В принципе, формулу можно упростить

Код Выделить Развернуть

=СУММЕСЛИМН($H$2:$H$18;$A$2:$A$18;"*"&J7;$B$2:$B$18;"*"&K7;$C$2:$C$18;"*"&L7;$D$2:$D$18;ЕСЛИ(M7="";"*";M7);$E$2:$E$18;"*"&N7;$F$2:$F$18;"*"&O7;$G$2:$G$18;"*"&P7)а если в базе год вводить в тестовом формате , то так

Код Выделить Развернуть

=СУММЕСЛИМН($H$2:$H$18;$A$2:$A$18;"*"&J7;$B$2:$B$18;"*"&K7;$C$2:$C$18;"*"&L7;$D$2:$D$18;"*"&M7;$E$2:$E$18;"*"&N7;$F$2:$F$18;"*"&O7;$G$2:$G$18;"*"&P7)(пример)

[Анна Б.]

Спасибо! Формула работает. "*"& - это как раз и исключает нулевой критерий.

Года в моей таблице в формате "2012г.", так что с этим проблем не было, единственное почему-то с кварталом возникли проблемы.
Если необходимо вытащить данные за I квартал без указания месяца, то он вытаскивал за I, II и III кварталы, а IV не суммировал, как бы искал не точное совпадение, но эту проблему обошла, кварталы назвала в формате "1-ый кв". И все получилось.

Спасибо!!!