Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Как указать в качестве источника данных для сводной таблицы только часть массива по условию?, построить сводную таблицу по условию
 
Добрый вечер, знатоки Excel! :-)

Подскажите мне, пожалуйста, по сводным таблицам.

У меня есть список филиалов с артикулами и суммами продаж. Данные по всем филиалам у меня в одной таблице.
В общих данных 4 филиала, мне нужно, чтобы было 4 сводных на разных листах по каждому филиалу в отдельности. Я строила 4 сводных, затем фильтрами отбирала тот филиал, с которым работаю, но из-за огромного количества данных Excel начинает крайне медленно работать.

Подскажите, пожалуйста, возможно ли построить сводную таблицу, используя условие - на первом листе сводная с условием, что данные берутся только по Москве, на втором листе -  только по Липецку и т.д.?
 
Сводных много самых разных. Строятся по условиям.
Название темы должно отражать суть задачи. Предложите новое. Модераторы заменят.
 
Зачем плодить много листов, стройте сводную таблицу поместив номер филиала в область страницы,
артикул в область строк, суммы в область данных.
 
Предлагаю новое название темы: Не получается построить сводную таблицу по заданному условию.

Kuzmich, но ведь в этом случае сводная всё-равно построится по всем данным, просто я там смогу фильтровать по филиалам. А у меня так и получается - я общую сводную переношу на другие листы и там отфильтровываю.
А такого способа, чтобы в диапазон для построения сводной попадала только та часть таблицы, которая соответствует условиям, нет? И который бы изменялся в зависимости от количества данных.
К примеру, в филиале в Москве сначала было 10 продаж, потом я заменила таблицу с исходными данными, и там вместо 10 продаж уже 40, и чтобы сводная строилась именно по Москве, а не по всем остальным, и увеличивала диапазон в случае изменения исходника?

Единственный вариант, до которого я сейчас додумалась - построить одну сводную по всем филиалам, потом отфильтровать по каждому, скопировать и вставить данные на другие листы. Но в этом случае мне при изменении исходника постоянно придется копировать, это неудобно...
 
Что в лоб, что по лбу... Сколько вопросов по сводным можно сюда втиснуть?

Свести в одно стадо быков и курей.
Вставить квадрат в круглое отверстие.
Сделать сводную двоешников из 5 групп...

А теперь Вы опишите свою задачу
 
Вариант при помощи запросов Power Query. Также задачу можно решить при помощи SQL-запросов.
suricat555, у вас какая версия Excel?
Предлагаю название темы:
Как указать в качестве источника данных для сводной таблицы только часть массива по условию?
 
Цитата
suricat555 написал: потом отфильтровать по каждому, скопировать и вставить данные на другие листы
И в чем ЦЕЛЕСООБРАЗНОСТЬ такой заморочки? Чем стандартные средства сводной не устраивают?.. ;) ;)
Изменено: Z - 1 Июн 2018 15:28:59
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Как показывает практика "большие" начальники, для которых делаются отчеты, не любят тыкать в фильтры, говоря: мне надо, чтобы я открыл и у меня все было, чтобы я не тыкал тут кнопочки. Причины этого - вопрос уже для курилки
 
Цитата
StepanWolkoff написал: Как показывает практика...
А такая практика вами используется?
Изменено: Z - 1 Июн 2018 16:46:20
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Моя работа отучать от такой практики. Просто приходя даже в крупные компании, где обороты с девятью нулями и более, встречаешь таких руководителей
 
Цитата
StepanWolkoff написал: Моя работа отучать...
Уважаемый, вопрос прямой - вы используете параметр сводной "Отобразить страницы фильтра отчета" для того, чтобы, как просит ТС, РАСКИДАТЬ ПО ОТДЕЛЬНЫМ ЛИСТАМ СВОДНЫЕ ПО КАЖДОМУ ГОРОДУ?!
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Z, уважаемый, приношу извинения, когда отвечал вам, не было картинки или я не заметил, сам по себе прием Я использую.
 
Z, вот только в файле-примере от ТС, данная настройка неактивна. К чему бы это?
 
Цитата
PooHkrd написал: К чему бы это?
К тому, что эта возможность появляется тогда, когда помещают поле, в данном случае "Номер филиала", в область "Фильтр".
ps Есть маленькая хитрость: дабы оставить его и в области строк, то следует сделать двойника. Как вариант, не лучший, однако... ;)
pps Не помню, простите, была ли такая возможность в формате 97-2003.
Изменено: Z - 1 Июн 2018 17:43:09
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Спасибо вам всем за помощь!!!  :) Буду сейчас разбираться, как вы так сумели такую задачу решить! ;-)

Z, целесообразность такой заморочки в том, чтобы у меня отчёты в статистику подготавливались по максимуму в автоматическом режиме. ))) И, возможно, за счет того, что на каждом отдельном листе будет сводная  только по определенному условию, быстродействие Excel увеличится...  
 
PooHkrd, у меня на работе 16 версия, но сейчас я дома тренируюсь такие таблицы делать, на 2003 версии.
 
PooHkrd, подскажите, пожалуйста, а чтобы мне разобраться в написании запросов Power Query, информации из справки Excel будет достаточно или надо какую-то дополнительную литературу почитать? Я ни разу раньше с такими вещами не сталкивалась...
 
Может пойти другим путем: Копирование строк с одинаковыми значениями ячеек на отдельные листы
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=101025
Цитата
дома тренируюсь такие таблицы делать, на 2003 версии.
В 2003 нет Power Query
 
suricat555 Сколько же у Вас данных (строк), что они так тормозят? Вообще сводные очень эффективный инструмент и настроить можно как фильтром так и срезом, но минус в том, что если в источнике не окажется этих значений (хотя у Вас маловероятно) они могут слететь. Но всегда есть выход.
Вы так хотели?
Изменено: skais675 - 1 Июн 2018 23:20:02
 
Цитата
skais675 написал:
Сколько же у Вас данных (строк), что они так тормозят?
Цитата
StepanWolkoff написал:
с девятью нулями и более
:-)

Цитата
suricat555 написал:
я дома тренируюсь такие таблицы делать, на 2003 версии
не совсем верный подход. Потом будет сложнее , более того, что будет сделано на 2016 не воспроизвести будет дома.

По идее есть один инструмент, который есть и в 2003 -MS Query и можно конечно использовать запрос к таблице , который отфильтровывает только нужные для сводной данные и на основе его делать сводную, но смысла в этом никакого нет, так как сама сводная делает это не хуже.
 
skais675, у меня сводные строятся на основе данных из 60 000 строк. Из них формируется 14 сводных, и функцией СУММЕСЛИ из списка с 2 000 строк тянется код к каждой сводной. Порой на то, чтобы добавить/изменить несколько строк в списке, уходит 1-1,5 минуты, а если зависнет, что бывает часто, то и вообще можно успеть чайку попить))))

Kuzmich, я попробовала использовать Ваш макрос, но это не совсем то, что мне надо, к сожалению. Макросы я тоже писать не умею. Подскажите, пожалуйста, что надо изучить, чтобы научиться их писать? :))) Я так чувствую, вы здесь все программисты, что так хорошо Excel владеете? :)
 
suricat555 если у сводных один источник, 60000 много но бывает и больше, смотря сколько отображается какие формулы вычисления, как построена сводная.
Попробуйте отключить вычисления и убедиться что тормозят сводные или суммесли (скорее в вычислениях тормоз и как они организованы). Кроме этого, смотря как строить сводную, если построить одну и потом ее копированием сделать еще 14 то источник будет обновляться один раз потому как общий, возможно ускорит. Ну а вообще если хотите могу помочь через teamviewer.  
Изменено: skais675 - 1 Июн 2018 23:27:04
 
Цитата
БМВ написал:
По идее есть один инструмент, который есть и в 2003 -MS Query и можно конечно использовать запрос к таблице , который отфильтровывает только нужные для сводной данные и на основе его делать сводную, но смысла в этом никакого нет, так как сама сводная делает это не хуже.
БМВ, но сводная же берет в качестве источника всю таблицу, а я хотела только часть попробовать... Попробую разобраться в MS Query, вдруг чем поможет)))

skais675, спасибо за подсказку, я попробую отключить вычисления, может быть найти альтернативу СУММЕСЛИ (если это они так тормозят работу) - может ВПР или еще что-нибудь. К сожалению, через teamviewer никто на работе не позволит подключаться, так что я попробую сама по Вашим подсказкам!  :) У меня есть еще один файл, где сводная строится на основе 250 000 строк, тоже безумно тормозит. А еще бывает неприятненько, когда 10 минут пытался добавить 20 строк, но Excel вдруг прекратил работу и ничего не сохранил.  8-0  
 
Цитата
я попробовала использовать Ваш макрос, но это не совсем то, что мне надо, к сожалению.
Находясь на листе Исходные данные запустите макрос из стандартного модуля
Код
Sub RaznestiDannye()
Dim i As Long
Dim n As Long
Dim Criterij As String
Dim iName As String
Dim Sht As Worksheet
Application.ScreenUpdating = False
  Set Sht = ThisWorkbook.Worksheets("Исходные данные")
        Columns("H").Clear
     'отбор уникальных значений столбца A в столбец H
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy _
                                  , CopyToRange:=Range("H1"), Unique:=True
     'количество уникальных значений филиалов
      n = Cells(Rows.Count, "H").End(xlUp).Row
    For i = 2 To n          'цикл по уникальным значениям
        Criterij = Sht.Cells(i, "H")
        iName = Criterij    'имя новой книги
     If Not SheetExist(iName) Then  'функция проверки наличия листа в файле
       'создаем новый лист
       Worksheets.Add After:=Worksheets(Worksheets.Count)
         ActiveSheet.Name = iName
     Else
       Worksheets(iName).Activate
       Cells.Clear
     End If
       'ставим автофильтр по столбцу А
         Sht.Range("A1:C15").AutoFilter 1, Criterij
       'копируем видимые строки в новый лист
        Sht.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy
        With Worksheets(iName)
          .Range("A1").PasteSpecial xlPasteColumnWidths
          .Range("A1").PasteSpecial xlPasteFormats
          .Range("A1").PasteSpecial xlPasteValues
          Sht.AutoFilter.Range.AutoFilter
          .Range("A1").Select
          .Range("C" & .Cells(.Rows.Count, "C").End(xlUp).Row + 1) = _
                WorksheetFunction.Sum(.Range("C2:C" & .Cells(.Rows.Count, "C").End(xlUp).Row))
          .Range("C" & .Cells(.Rows.Count, "C").End(xlUp).Row).NumberFormat = "#,##0.00"
          .Range("C" & .Cells(.Rows.Count, "C").End(xlUp).Row).Font.Bold = True
        End With
    Next
Application.ScreenUpdating = True
End Sub

     'функция проверки наличия листа в файле, лист есть - true
Function SheetExist(iName As String) As Boolean
    On Error Resume Next
    With Worksheets(iName): End With
    SheetExist = (Err = 0)
End Function
 
Цитата
suricat555 написал:
1-1,5 минуты, а если зависнет, что бывает часто, то и вообще можно успеть чайку попить))))
Это означает что что-то очень не оптимально.

Off
Цитата
suricat555 написал:
Я так чувствую, вы здесь все программисты,
нет, я "тыжпрограммист" в смысле работа с программированием и Excel не связана совсем зато за что-только не отвечаю в области ИТ, и знаю еще очень профессиональных любителей на этом форуме.
Цитата
suricat555 написал:
Макросы я тоже писать не умею. Подскажите, пожалуйста, что надо изучить, чтобы научиться их писать?
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=5&TID=105302&TITLE_SEO=105302-oznakomlenie-s-makrosami пока там не сильно много ответов, наверно по тому, что ответ на вопрос неоднозначен.
Изменено: БМВ - 1 Июн 2018 23:44:20
 
suricat555, тут такое дело, я подозреваю, что вы используете сводные для сбора инфы с 60 килострок как промежуточные таблицы, а потом из этого всего получаете тот итог, который вам действительно нужен. А что если вы покажете структуру реальных данных на примере 10-20 строк и покажете, какой из этого нужно получить результат в итоге. Может быть вам предложат более оптимальное решение?
 
Цитата
suricat555 написал:
сводная же берет в качестве источника всю таблицу
Конечно я не знаю параметров вашего ПК, но средний современный ПК имеет такие характеристики, что 60000 - это не так и много для него. Ежемесячно обрабатывал статистику в 20-30к строк Сводные строились мгновенно, самое длительное это вставка самих значений и любые действия с источником данных, а там были еще предварительные расчеты, в том числе перевод времени к единому исходя из локации по каждой строке, перевод даты и времени из текста …. А ноутбук был не самый мощный.
 
PooHkrd, да, Вы абсолютно правы! Потом данные из этих сводных идут в другие таблицы в этом же файле в более укрупненном виде по статистическим кодам. Я после выходных в качестве примера прикреплю файлик с 20 строчками, как Вы сказали (там много всяких связей, я могу так и не вспомнить, что откуда тянется и куда дальше собирается).

Kuzmich, почему-то у меня не получилось запустить макрос. У меня вылезают ошибки

Но я предполагаю, что это я как-то криво вставляю...
Снимок.PNG (14.89 КБ)
Изменено: suricat555 - 1 Июн 2018 23:57:12
 
Цитата
БМВ написал: Конечно я не знаю параметров вашего ПК..
БМВ, моему ПК даже память увеличивали, и меняли ОС на Windows 10 специально чтобы Excel был быстрее, но по-моему это даже несколько отрицательно сказалось на скорости :D  :D  
 
Цитата
почему-то у меня не получилось запустить макрос. У меня вылезают ошибки
Макрос надо вставить в стандартный модуль. Запускать при активном листе "Исходные данные".
Можно сделать кнопочку.
Страницы: 1 2 След.
Читают тему (гостей: 1)