Зводная табліца для некалькіх дыяпазонаў даных

Пастаноўка задачы

Зводныя табліцы - адзін з самых дзіўных інструментаў у Excel. Але пакуль, на жаль, ні адна з версій Excel не можа на хаду зрабіць такую ​​простую і патрэбную рэч, як пабудаваць зводку для некалькіх дыяпазонаў зыходных дадзеных, размешчаных, напрыклад, на розных лістах або ў розных табліцах:

Перш чым мы пачнем, давайце ўдакладнім пару момантаў. Апрыёры я лічу, што ў нашых дадзеных выконваюцца наступныя ўмовы:

• Табліцы могуць мець любую колькасць радкоў з любымі дадзенымі, але яны павінны мець аднолькавы загаловак.
• На лістах з зыходнымі табліцамі не павінна быць лішніх даных. Адзін ліст – адна табліца. Для кіравання раю выкарыстоўваць спалучэнне клавіш Ctrl+канец, які перамяшчае вас у апошнюю выкарыстаную ячэйку працоўнага ліста. У ідэале гэта павінна быць апошняя ячэйка ў табліцы дадзеных. Калі пры націску на Ctrl+канец любая пустая ячэйка справа або пад табліцай вылучаецца - выдаліце ​​гэтыя пустыя слупкі справа або радкі пад табліцай пасля табліцы і захавайце файл.

Спосаб 1: Стварыце табліцы для зводу з дапамогай Power Query

Пачынаючы з версіі 2010 для Excel, існуе бясплатная надбудова Power Query, якая можа збіраць і пераўтвараць любыя даныя, а затым даваць іх у якасці крыніцы для стварэння зводнай табліцы. Вырашыць нашу праблему з дапамогай гэтай надбудовы зусім не складана.

Спачатку створым новы пусты файл у Excel - у ім будзе адбывацца зборка, а потым у ім будзе створана зводная табліца.

Затым на таб Дата (калі ў вас Excel 2016 або больш позняй версіі) або на ўкладцы Запыт на харчаванне (калі ў вас Excel 2010-2013) абярыце каманду Стварыць запыт – З файла – Excel (Атрымаць дадзеныя — З файла — Excel) і пакажыце зыходны файл з табліцамі, якія трэба сабраць:

У якое з'явілася акне абярыце любы ліст (не важна, які) і націсніце кнопку ніжэй Мяняць (Рэдагаваць):

Акно рэдактара запытаў Power Query павінна адкрыцца над Excel. З правага боку акна на панэлі Параметры запыту выдаліць усе аўтаматычна створаныя крокі, акрамя першага – крыніца (Крыніца):

Цяпер мы бачым агульны спіс усіх лістоў. Калі акрамя аркушаў дадзеных у файле ёсць яшчэ якія-небудзь бакавыя аркушы, то на гэтым кроку наша задача - выбраць толькі тыя аркушы, з якіх неабходна загрузіць інфармацыю, выключыўшы ўсе астатнія з дапамогай фільтра ў загалоўку табліцы:

Выдаліць усе слупкі, акрамя слупка Датапстрыкнуўшы правай кнопкай мышы загаловак слупка і выбраўшы Выдаліць іншыя слупкі (Выдаліць іншыя слупкі):

Затым вы можаце разгарнуць змесціва сабраных табліц, націснуўшы на двайную стрэлку ў верхняй частцы слупка (сцяжок Выкарыстоўвайце зыходнае імя слупка ў якасці прэфікса вы можаце адключыць):

Калі вы ўсё зрабілі правільна, то ў гэты момант вы павінны ўбачыць змесціва ўсіх табліц, сабраных адна пад адной:

Засталося кнопкай падняць першы радок да шапкі табліцы Выкарыстоўвайце першы радок у якасці загалоўкаў (Выкарыстоўваць першы радок у якасці загалоўкаў) таб Галоўная (Дадому) і выдаліць дублікаты загалоўкаў табліцы з дадзеных з дапамогай фільтра:

Захавайце ўсё зробленае з дапамогай каманды Зачыніць і загрузіць – Зачыніць і загрузіць у… (Зачыніць і загрузіць — Зачыніць і загрузіць у...) таб Галоўная (Дадому), і ў якое адкрылася акне абярыце опцыю Толькі злучэнне (Толькі злучэнне):

усё. Засталося толькі пабудаваць рэзюмэ. Для гэтага перайдзіце на ўкладку Устаўка – зводная табліца (Устаўка — зводная табліца), абярыце варыянт Выкарыстоўвайце знешнюю крыніцу даных (Выкарыстоўваць знешнюю крыніцу даных)а затым, націснуўшы кнопку Выберыце злучэнне, наша просьба. Далейшае стварэнне і налада зводкі адбываецца цалкам стандартным спосабам шляхам перацягвання патрэбных нам палёў у вобласць радкоў, слупкоў і значэнняў:

Калі зыходныя дадзеныя зменяцца ў будучыні або будзе дададзена яшчэ некалькі аркушаў захоўвання, то дастаткова будзе абнавіць запыт і нашу зводку з дапамогай каманды Абнавіць усё таб Дата (Дадзеныя — Абнавіць усе).

Спосаб 2. Аб'ядноўваем табліцы камандай UNION SQL ў макрас

Яшчэ адно рашэнне нашай праблемы прадстаўлена гэтым макрасам, які стварае набор даных (кэш) для зводнай табліцы з дапамогай каманды ЕДНАСЦЬ Мова запытаў SQL. Гэтая каманда аб'ядноўвае табліцы з усіх названых у масіве Імёны лістоў аркушаў кнігі ў адну табліцу даных. Гэта значыць, замест таго, каб фізічна капіяваць і ўстаўляць дыяпазоны з розных аркушаў у адзін, мы робім тое ж самае ў аператыўнай памяці кампутара. Затым макрас дадае новы ліст з дадзеным імем (пераменная Назва аркуша вынікаў) і стварае на ім паўнавартасную (!) зводку на аснове сабранага кэша.

Каб выкарыстоўваць макрас, выкарыстоўвайце кнопку Visual Basic на ўкладцы распрацоўшчык (Распрацоўшчык) або спалучэнне клавіш Alt+F11. Затым мы ўстаўляем новы пусты модуль праз меню Устаўка – модуль і скапіруйце туды наступны код:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'імя аркуша, дзе будзе адлюстроўвацца выніковая зводка ResultSheetName = "Pivot" 'масіў аркуша імёны з зыходнымі табліцамі SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'мы фарміруем кэш для табліц з лістоў з SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next 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 ) End With 'recreate the sheet to display the resulting pivot table On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivot. Name = resultsheetname 'адлюстраваць згенераванае зводку кэша на гэтым аркушы SET OBJPivotCache = ActiveWorkBook.pivotcaches.add (xleaxternal) усталяваць objpivotcache.recordset = objrs set objrs = нічога з wspivot objpivotcache. objPivotCache = Дыяпазон нічога ("A3"). Выберыце End With End Sub    

Затым гатовы макрас можна запусціць з дапамогай спалучэння клавіш Alt+F8 або кнопку Макрасы на ўкладцы распрацоўшчык (Распрацоўшчык — Макрасы).

Мінусы гэтага падыходу:

• Дадзеныя не абнаўляюцца, таму што кэш не мае сувязі з зыходнымі табліцамі. Калі вы зменіце зыходныя даныя, вы павінны зноў запусціць макрас і зноў пабудаваць зводку.
• Пры змене колькасці лістоў неабходна адрэдагаваць код макраса (масіў Імёны лістоў).
  

Але ў выніку атрымліваем сапраўдную паўнавартасную зводную табліцу, пабудаваную на некалькіх дыяпазонах з розных лістоў:

Вуаля!

Тэхнічная заўвага: калі пры запуску макраса вы атрымліваеце памылку накшталт «Пастаўшчык не зарэгістраваны», то, хутчэй за ўсё, у вас 64-бітная версія Excel або ўсталяваная няпоўная версія Office (няма доступу). Каб выправіць сітуацыю, заменіце фрагмент у кодзе макраса:

	 Пастаўшчык=Microsoft.Jet.OLEDB.4.0;  

у:

	Пастаўшчык=Microsoft.ACE.OLEDB.12.0;  

Спампуйце і ўсталюйце бясплатны механізм апрацоўкі даных з Access з вэб-сайта Microsoft – Microsoft Access Database Engine 2010 Redistributable

Спосаб 3: Майстар зводнай табліцы кансалідуе са старых версій Excel

Гэты метад трохі састарэў, але ўсё ж варта згадаць. Фармальна кажучы, ва ўсіх версіях да 2003 года ўключна ў майстры зводнай табліцы была магчымасць «пабудаваць зводную кропку для некалькіх дыяпазонаў кансалідацыі». Аднак пабудаваны такім чынам справаздачу, на жаль, будзе толькі жаласным падабенствам сапраўднай паўнавартаснай зводкі і не падтрымлівае многія «фішкі» звычайных зводных табліц:

У такой зводнай няма загалоўкаў слупкоў у спісе палёў, няма налады гнуткай структуры, абмежаваны набор выкарыстоўваных функцый, і, увогуле, усё гэта не вельмі падобна на зводную табліцу. Магчыма, таму, пачынаючы з 2007 года, Microsoft прыбрала гэтую функцыю са стандартнага дыялогу пры стварэнні справаздач зводнай табліцы. Цяпер гэтая функцыя даступная толькі праз карыстацкую кнопку Майстар зводнай табліцы(Майстар зводнай табліцы), які пры жаданні можна дадаць на панэль хуткага доступу праз Файл – Параметры – Наладзіць панэль хуткага доступу – Усе каманды (Файл — Параметры — Наладзіць панэль хуткага доступу — Усе каманды):

Пасля націску на дабаўленую кнопку неабходна на першым кроку майстра выбраць адпаведную опцыю:

А затым у наступным акне вылучыце па чарзе кожны дыяпазон і дадайце яго ў агульны спіс:

Але, зноў жа, гэта не паўнавартаснае рэзюмэ, так што не чакайце ад яго занадта шмат. Я магу рэкамендаваць гэты варыянт толькі ў вельмі простых выпадках.

• Стварэнне справаздач з дапамогай зводных табліц
• Наладзьце разлікі ў зводных табліцах
• Што такое макрасы, як імі карыстацца, куды скапіяваць код VBA і г.д.
• Збор даных з некалькіх лістоў на адзін (дапаўненне PLEX)