Бир нече маалымат диапазонундагы пивот таблицасы

Проблеманы түзүү

Пивот таблицалары Excelдеги эң укмуштуу куралдардын бири. Бирок азыркыга чейин, тилекке каршы, Excel версияларынын бири да, мисалы, ар кандай барактарда же ар кандай таблицаларда жайгашкан бир нече баштапкы маалымат диапазондору үчүн корутунду түзүү сыяктуу жөнөкөй жана керектүү нерсени тез арада жасай албайт:

Баштоодон мурун, бир-эки пунктту тактап алалы. Априори, мен биздин маалыматтарда төмөнкү шарттар аткарылат деп ишенем:

• Таблицаларда каалаган маалыматтары бар каалаган сандагы саптар болушу мүмкүн, бирок алардын аталышы бирдей болушу керек.
• Булак таблицалары бар баракчаларда кошумча маалыматтар болбошу керек. Бир барак - бир стол. Башкаруу үчүн мен сизге баскычтоптун жарлыгын колдонууну сунуштайм Ctrl+End, бул сизди иш барагындагы акыркы колдонулган уячага жылдырат. Идеалында, бул маалымат таблицасындагы акыркы уяча болушу керек. Эгерде сиз басканда Ctrl+End таблицанын оң жагындагы же астындагы каалаган бош уячалар белгиленет – оң жагындагы бош тилкелерди же таблицадан кийинки таблицадан төмөнкү саптарды жок кылыңыз жана файлды сактаңыз.

Метод 1: Power Query аркылуу пивот үчүн таблицаларды түзүңүз

Excel үчүн 2010-жылдагы версиясынан баштап, каалаган маалыматты чогултуп, өзгөртүп, андан кийин пивот таблицасын түзүү үчүн булак катары бере турган акысыз Power Query кошумчасы бар. Бул кошумчанын жардамы менен биздин көйгөйдү чечүү кыйын эмес.

Биринчиден, келгиле, Excelде жаңы бош файл түзөлү – анда жыйын өтөт, андан кийин анда пивот таблицасы түзүлөт.

Андан кийин өтмөктө маалыматтар (эгер сизде Excel 2016 же андан кийинкиси болсо) же өтмөктө күч суроо (Эгер сизде Excel 2010-2013 болсо) буйрукту тандаңыз Сурам түзүү - Файлдан - Excel (Маалыматтарды алуу — Файлдан — Excel) жана чогултула турган таблицалар менен баштапкы файлды көрсөтүңүз:

Пайда болгон терезеде каалаган баракты тандап (кайсысы маанилүү эмес) жана төмөнкү баскычты басыңыз өзгөртүү (Түзөтүү):

Power Query Query Editor терезеси Excelдин үстүндө ачылышы керек. Панелдеги терезенин оң жагында Параметрлерди суроо биринчиден башка бардык автоматтык түрдө түзүлгөн кадамдарды жок кылуу - булак (Source):

Эми биз бардык барактардын жалпы тизмесин көрөбүз. Эгерде файлда маалымат барактарынан тышкары дагы башка каптал барактары бар болсо, анда бул кадамда биздин милдет таблицанын башындагы чыпка аркылуу башкалардын баарын кошпогондо, маалымат жүктөлүшү керек болгон барактарды гана тандоо болуп саналат:

Мамычадан башка бардык мамычаларды жок кылыңыз маалыматтартилкенин аталышын оң баскыч менен чыкылдатып, тандоо Башка тилкелерди жок кылыңыз (Алып салуу башка тилкелер):

Андан кийин колоннанын жогору жагындагы кош жебени чыкылдатуу менен чогултулган таблицалардын мазмунун кеңейте аласыз (белгилөө кутучасы Префикс катары түпнуска тилке атын колдонуңуз сиз аны өчүрө аласыз):

Эгер сиз бардыгын туура кылган болсоңуз, анда бул учурда сиз биринин астына чогултулган бардык таблицалардын мазмунун көрүшүңүз керек:

Бул баскыч менен столдун башына биринчи катарды көтөрүү үчүн калууда Баш аты катары биринчи сапты колдонуңуз (Биринчи сапты баш аты катары колдонуңуз) табулатура Home (Үй) жана чыпка аркылуу берилиштерден кайталанган таблица баштарын алып салыңыз:

Буйрук менен жасалган нерселердин баарын сактаңыз Жабуу жана жүктөө – Жабуу жана жүктөө… (Жабуу жана жүктөө — Жабуу жана жүктөө…) табулатура Home (Үй), жана ачылган терезеде параметрди тандаңыз Туташуу гана (Туташуу гана):

Баары. Бул кыскача куруу үчүн гана калат. Бул үчүн, өтмөккө өтүңүз Кыстаруу – Пивот таблицасы (Киргизүү — Пивот таблицасы), параметрди тандаңыз Тышкы маалымат булагын колдонуңуз (Тышкы маалымат булагын колдонуу)анан кнопканы басуу менен Байланышты тандаңыз, биздин өтүнүч. Пивотту андан ары түзүү жана конфигурациялоо бизге керектүү талааларды саптарга, мамычаларга жана баалуулуктар аймагына сүйрөө аркылуу толугу менен стандарттуу түрдө ишке ашат:

Эгер булак маалыматы келечекте өзгөрсө же дагы бир нече дүкөн барактары кошулса, анда буйрукту колдонуу менен суроону жана корутундуну жаңыртуу жетиштүү болот. Баарын жаңыртуу табулатура маалыматтар (Маалымат — баарын жаңылоо).

Метод 2. Макродо UNION SQL буйругу менен таблицаларды бириктиребиз

Биздин көйгөйдүн дагы бир чечими бул макрос менен берилген, ал команданы колдонуу менен пивот таблица үчүн маалымат топтомун (кэш) түзөт. UNITY SQL суроо тили. Бул команда массивде көрсөтүлгөн бардык таблицаларды бириктирет SheetNames китептин барактары бирдиктүү маалымат таблицасына. Башкача айтканда, физикалык түрдө ар кандай барактардан бирине диапазондорду көчүрүү жана чаптоо ордуна, биз компьютердин оперативдүү эсинде да ушундай кылабыз. Андан кийин макрос берилген аталыш менен жаңы баракты кошот (variable ResultSheetName) жана чогултулган кэштин негизинде ага толук кандуу (!) резюме түзөт.

Макросту колдонуу үчүн өтмөктөгү Visual Basic баскычын колдонуңуз иштеп (Иштеп чыгуучу) же баскычтоптун жарлыгы Alt+F11. Андан кийин биз меню аркылуу жаңы бош модулду киргизебиз Кыстаруу – модуль жана ал жерде төмөнкү кодду көчүрүү:

Sub New_Multi_Table_Pivot() Dim i Long Dim arSQL() String As Dim objPivotCache As PivotCache Dim objRS Объект катары Dim ResultSheetName катары String Dim SheetsNames Variant катары "баракча аталышы, анда пайда болгон пивот көрсөтүлө турган" Натыйжа "баракчасы" баштапкы таблицалары бар аттар SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта") 'биз ActiveWorkbook ReDim arSQL (1 To (UBound(SheetsNames) + 1) менен SheetsNames баракчаларынан таблицалар үчүн кэш түзөбүз. ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Кийинки i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) "" менен аяктайт "пайда болгон пивот таблицасын көрсөтүү үчүн баракты кайра түзүү Ката жөнүндө" Кийинки колдонмону улантуу.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo т. Name = ResultSheetName 'түзүлгөн кэш корутундусун бул баракта көрсөтүү ObjPivotCache Set = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = wsPivot менен эч нерсе жок objPivotCacheation) SettingPivotCache:AR3:"Set." objPivotCache = Nothing Range("A3"). End With End Sub'ду тандаңыз    

Даяр макросту баскычтоптун жарлыгы менен иштетсе болот Alt+F8 же өтмөктөгү Макрос баскычы иштеп (Иштеп чыгуучу — Макрос).

Бул ыкманын терс жактары:

• Дайындар жаңыртылган эмес, анткени кэш булак таблицаларына байланышы жок. Эгер сиз баштапкы маалыматтарды өзгөртсөңүз, макросту кайра иштетип, корутундуну кайра түзүшүңүз керек.
• Барактардын санын өзгөртүүдө макрокодду (массив SheetNames).
  

Бирок аягында биз ар кандай барактардын бир нече диапазондоруна курулган чыныгы толук кандуу пивот столун алабыз:

Voilà!

Техникалык эскертүү: макросту иштетип жатканда "Провайдер катталган жок" сыяктуу катага туш болсоңуз, анда сизде Excel'дин 64 биттик версиясы бар же Office'тин толук эмес версиясы орнотулган (кирүү жок). Кырдаалды оңдоо үчүн макрокоддогу фрагментти алмаштырыңыз:

	 Провайдер=Microsoft.Jet.OLEDB.4.0;  

үчүн:

	Провайдер=Microsoft.ACE.OLEDB.12.0;  

Жана Microsoft веб-сайтынан Access'тен акысыз маалыматтарды иштетүү кыймылдаткычын жүктөп алып, орнотуңуз – Microsoft Access Database Engine 2010 Redistributable

Метод 3: Excelдин эски версияларынан Pivot Table устасын бириктирүү

Бул ыкма бир аз эскирген, бирок дагы эле айта кетүү керек. Формалдуу түрдө айтканда, 2003-жылга чейинки жана анын ичинде бардык версияларында, Pivot Table устасында "бир нече консолидация диапазону үчүн пивот куруу" опциясы бар болчу. Бирок, ушундай жол менен түзүлгөн отчет, тилекке каршы, чыныгы толук кандуу кыскача баяндаманын аянычтуу көрүнүшү гана болуп калат жана кадимки пивот таблицаларынын көптөгөн "чиптерин" колдобойт:

Мындай пивотто талаалар тизмесинде мамычанын аталыштары жок, ийкемдүү түзүм орнотуулары жок, колдонулган функциялардын жыйындысы чектелген жана жалпысынан мунун баары пивот таблицага анча окшошпойт. Балким, ошондуктан, 2007-жылдан баштап, Microsoft пивот таблицасы отчетторун түзүүдө стандарттык диалогдон бул функцияны алып салган. Эми бул функция ыңгайлаштырылган баскыч аркылуу гана жеткиликтүү Пивот таблица устасы(Пивот стол устасы), аны, эгер кааласа, аркылуу Ыкчам жетүү куралдар панелине кошууга болот Файл – Параметрлер – Ыкчам жетүү куралдар панелин өзгөчөлөштүрүү – Бардык буйруктар (Файл — Параметрлер — Ыкчам жетүү куралдар панелин ыңгайлаштыруу — Бардык буйруктар):

Кошулган баскычты чыкылдаткандан кийин, устанын биринчи кадамында ылайыктуу параметрди тандоо керек:

Анан кийинки терезеде ар бир диапазонду өз кезегинде тандап, аны жалпы тизмеге кошуңуз:

Бирок, дагы бир жолу, бул толук кандуу резюме эмес, андыктан андан көп нерсени күтпөңүз. Мен бул параметрди абдан жөнөкөй учурларда гана сунуш кыла алам.

• Пивот таблицалары менен отчетторду түзүү
• Пивот таблицаларында эсептөөлөрдү орнотуңуз
• Макрос деген эмне, аларды кантип колдонуу керек, VBA кодун кайда көчүрүү керек, ж.б.
• Бир нече барактан маалымат чогултуу (PLEX кошумчасы)