Бірнеше деректер ауқымындағы жиынтық кесте

Мәселені тұжырымдау

Жиынтық кестелер Excel бағдарламасындағы ең таңғажайып құралдардың бірі болып табылады. Бірақ әзірге, өкінішке орай, Excel нұсқасының ешқайсысы, мысалы, әртүрлі парақтарда немесе әртүрлі кестелерде орналасқан бірнеше бастапқы деректер ауқымы үшін қорытынды жасау сияқты қарапайым және қажетті нәрсені жылдам орындай алмайды:

Бастамас бұрын, бір-екі тармақты нақтылап алайық. Априори, менің ойымша, біздің деректерімізде келесі шарттар орындалады:

• Кестелерде кез келген деректері бар жолдардың кез келген саны болуы мүмкін, бірақ олардың тақырыбы бірдей болуы керек.
• Бастапқы кестелері бар парақтарда қосымша деректер болмауы керек. Бір парақ – бір үстел. Басқару үшін пернелер тіркесімін пайдалануды ұсынамын Ctrl+Соңы, ол сізді жұмыс парағындағы соңғы пайдаланылған ұяшыққа жылжытады. Ең дұрысы, бұл деректер кестесіндегі соңғы ұяшық болуы керек. Егер сіз басқан кезде Ctrl+Соңы кестенің оң жағындағы немесе астындағы кез келген бос ұяшық бөлектеледі – кестеден кейінгі оң жақтағы бос бағандарды немесе кестенің астындағы жолдарды жойып, файлды сақтаңыз.

1-әдіс: Power Query арқылы жиынтық үшін кестелерді құрастырыңыз

Excel бағдарламасына арналған 2010 нұсқасынан бастап кез келген деректерді жинап, түрлендіре алатын, содан кейін оны жиынтық кестені құру үшін көз ретінде бере алатын тегін Power Query қондырмасы бар. Бұл қондырманың көмегімен мәселемізді шешу қиын емес.

Алдымен Excel бағдарламасында жаңа бос файл жасайық – онда құрастыру орын алады, содан кейін онда жиынтық кесте жасалады.

Содан кейін қойындыда мәліметтер (егер сізде Excel 2016 немесе одан кейінгі нұсқасы болса) немесе қойындыда Қуат сұрауы (егер сізде Excel 2010-2013 болса) пәрменді таңдаңыз Сұраныс жасау – Файлдан – Excel (Деректерді алу — файлдан — Excel) және жиналатын кестелері бар бастапқы файлды көрсетіңіз:

Пайда болған терезеде кез келген парақты таңдаңыз (қайсысы маңызды емес) және төмендегі түймені басыңыз өзгеріс (Өңдеу):

Power Query Query Editor терезесі Excel бағдарламасының жоғарғы жағында ашылуы керек. Панельдегі терезенің оң жағында Параметрлерді сұрау біріншіден басқа барлық автоматты түрде жасалған қадамдарды жою – қайнар көз (Көз):

Енді біз барлық парақтардың жалпы тізімін көреміз. Егер файлда деректер парақтарына қосымша басқа бүйірлік парақтар болса, онда бұл қадамда біздің міндетіміз кесте тақырыбындағы сүзгіні пайдаланатын барлық қалғандарын қоспағанда, ақпаратты жүктеу қажет парақтарды ғана таңдау болып табылады:

Бағаннан басқа барлық бағандарды жойыңыз мәліметтербаған тақырыбын тінтуірдің оң жақ түймешігімен басып, таңдау арқылы Басқа бағандарды жою (Жою басқа бағандар):

Содан кейін бағанның жоғарғы жағындағы қос көрсеткіні басу арқылы жиналған кестелердің мазмұнын кеңейтуге болады (құсбелгі Бастапқы баған атауын префикс ретінде пайдаланыңыз сіз оны өшіре аласыз):

Егер сіз бәрін дұрыс орындаған болсаңыз, онда осы кезде бірінен соң бірі жиналған барлық кестелердің мазмұнын көруіңіз керек:

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

Пәрменмен орындалғанның бәрін сақтаңыз Жабу және жүктеу – жабу және жүктеу… (Жабу және жүктеу — жабу және жүктеу ...) қойындысы Home (Үй), және ашылған терезеде опцияны таңдаңыз Тек қосылым (Тек қосылым):

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

Егер бастапқы деректер болашақта өзгерсе немесе тағы бірнеше дүкен парақтары қосылса, пәрменді пайдаланып сұрауды және қорытындыны жаңарту жеткілікті болады. Барлығын жаңарту қойындысы мәліметтер (Деректер — Барлығын жаңарту).

2-әдіс. Макроста UNION SQL командасымен кестелерді біріктіреміз

Біздің мәселеміздің тағы бір шешімі осы макроспен ұсынылған, ол пәрменді пайдаланып жиынтық кесте үшін деректер жинағын (кэш) жасайды. UNITY SQL сұрау тілі. Бұл пәрмен массивте көрсетілген барлық кестелерді біріктіреді Парақ атаулары кітап парақтары бір деректер кестесіне. Яғни, физикалық түрде әртүрлі парақтардан біреуіне диапазондарды көшіру және қоюдың орнына, біз компьютердің жедел жадында дәл осылай жасаймыз. Содан кейін макрос берілген атаумен жаңа парақты қосады (айнымалы 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 ретінде Нұсқа ретінде 'парақ атауы, онда нәтижесінде алынған жиынтық кескіні "Нәтижесі" парағы көрсетіледі. бастапқы кестелері бар атаулар SheetsNames = Массив("Альфа", "Бета", "Гамма", "Дельта") ' ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) көмегімен SheetsNames парақтарынан кестелер үшін кэш қалыптастырамыз. ) i = LBound (SheetsNames) 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; Деректер көзі=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Соңы "Нәтижедегі жиынтық кестені көрсету үшін парақты қайта жасау Қате туралы" Келесі қолданбаны жалғастыру.DisplayAlerts = False Worksheets(ResultSheetName).Жойылған wsPivot = Worksheets.Add wsPivo т. Name = ResultSheetName 'жасалған кэш жиынын осы парақта көрсету objPivotCache Set = ActiveWorkbook.PivotCaches.Add(xlExternal) орнату objPivotCache.Recordset = objRS Set objRS = wsPivot objPivotCachestinivation арқылы ештеңе жоқ TWSPivotCachestiniv=Set.AR3." objPivotCache = Ештеңе диапазон («A3»). Соңы бар соңды таңдаңыз.    

Дайын макросты содан кейін пернелер тіркесімі арқылы іске қосуға болады Alt+F8 немесе қойындыдағы Макростар түймешігін басыңыз әзірлеуші (Әзірлеуші ​​— макростар).

Бұл тәсілдің кемшіліктері:

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

Бірақ соңында біз әртүрлі парақтардың бірнеше диапазондарына салынған нақты толыққанды жиынтық үстелді аламыз:

Voilà!

Техникалық ескерту: макросты іске қосу кезінде «Провайдер тіркелмеген» сияқты қатені алсаңыз, сізде Excel бағдарламасының 64 биттік нұсқасы бар немесе Office бағдарламасының толық емес нұсқасы орнатылған (Access жоқ). Жағдайды түзету үшін макрокодтағы фрагментті ауыстырыңыз:

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

үшін:

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

Microsoft веб-сайтынан Access тегін деректерді өңдеу механизмін жүктеп алыңыз және орнатыңыз – Microsoft Access Database Engine 2010 Redistributable

3-әдіс: Excel бағдарламасының ескі нұсқаларынан жиынтық кесте шеберін біріктіру

Бұл әдіс біраз ескірген, бірақ әлі де айта кету керек. Ресми түрде айтқанда, 2003 жылға дейінгі және оның ішінде барлық нұсқаларда жиынтық кесте шеберінде «бірнеше шоғырландыру ауқымдары үшін топтама құру» опциясы болды. Алайда, осылай құрастырылған есеп, өкінішке орай, нақты толыққанды түйіндеменің өкінішті көрінісі ғана болады және әдеттегі жиынтық кестелердің көптеген «чиптерін» қолдамайды:

Мұндай жиынтықта өрістер тізімінде баған тақырыптары жоқ, икемді құрылым параметрі жоқ, қолданылатын функциялар жиынтығы шектеулі және, жалпы алғанда, мұның бәрі жиынтық кестеге өте ұқсас емес. Сондықтан 2007 жылдан бастап Microsoft жиынтық кесте есептерін жасау кезінде бұл функцияны стандартты диалогтан алып тастады. Енді бұл мүмкіндік тек пайдаланушы түймесі арқылы қол жетімді Жиынтық кесте шебері(жиынтық кесте шебері), қажет болса, жылдам қол жеткізу құралдар тақтасына арқылы қосуға болады Файл – Параметрлер – Жылдам қатынау құралдар тақтасын теңшеу – Барлық пәрмендер (Файл — Параметрлер — Жылдам кіру құралдар тақтасын теңшеу — Барлық пәрмендер):

Қосылған түймені басқаннан кейін шебердің бірінші қадамында сәйкес опцияны таңдау керек:

Содан кейін келесі терезеде әрбір ауқымды кезекпен таңдап, оны жалпы тізімге қосыңыз:

Бірақ, қайталаймын, бұл толыққанды қорытынды емес, сондықтан одан көп нәрсе күтпеңіз. Мен бұл опцияны өте қарапайым жағдайларда ғана ұсына аламын.

• Жиынтық кестелермен есептерді құру
• Жиынтық кестелерде есептеулерді орнату
• Макростар дегеніміз не, оларды қалай пайдалану керек, VBA кодын қайда көшіру керек және т.б.
• Бірнеше парақтан біреуіне деректерді жинау (PLEX қосымшасы)