Новости:

Из правил форума: Тема должна отражать суть вопроса, топики типа "help please" будут удаляться!

Главное меню

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

Автор Анна Б., 03.05.2013, 12:50

« назад - далее »

Анна Б.

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

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

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

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

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

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

Иногда движение вперед является результатом пинка сзади...

Serge 007

Цитата: Анна Б. от 03.05.2013, 12:50Суммпроизв не подходит, т.к. если один из критериев будет равен 0, то значение формулы тоже 0.
Это кто Вам такое сказал?

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

Цитата: Анна Б. от 03.05.2013, 12:50...свoдные диaграммы мне не подходят. Слишком много нюансов, которые нельзя автоматизировать...
Например?
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Анна Б.

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

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

Serge 007

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

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

Анна Б.

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

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

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

Serge 007

Цитата: Анна Б. от 03.05.2013, 14:57
...формула должна вытащить все данные за квартал (за три месяца)...
Тогда зачем Вы задействуете поле "Месяц"?!
У Вас же есть поле "Квартал", вот с ним и работайте

Цитата: Анна Б. от 03.05.2013, 14:57...если не указывать дистрибьютора, то формула считает всех дистрибьюторов, если не указывать год, то сумма за все года по оставшимся критериям...
Ну так не указывайте неверные критерии

Вообще немного странная задача. Насколько я понял, Вы хотите указывать критерии, которые отсутствуют, но что бы формула их игнорировала? Так? Если так, то зачем это надо? Если не так, то как надо?
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Михаил С.

Может так? =СУММЕСЛИМН($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))
Отдельное спасибо можно на QiWi-кошелек 909-771-53-87 или ЯД 41001136675053

Анна Б.

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

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

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

Анна Б.

Цитата: Михаил С. от 03.05.2013, 15:42
Может так? =СУММЕСЛИМН($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))


Вот возможно подходящий вариант, щас попробую разобраться с "*", и попробую внедрить в свою базу  :D
Иногда движение вперед является результатом пинка сзади...

Анна Б.

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

Михаил С.

В принципе, формулу можно упростить =СУММЕСЛИМН($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)(пример)
Отдельное спасибо можно на QiWi-кошелек 909-771-53-87 или ЯД 41001136675053

Анна Б.

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

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

Спасибо!!!  :) :) :)
Иногда движение вперед является результатом пинка сзади...