Ҷадвали ҷамъбастӣ дар диапазони сершумори додаҳо

Тартиб додани мушкилот

Ҷадвалҳои пивот яке аз воситаҳои аҷибтарин дар Excel мебошанд. Аммо то ҳол, мутаассифона, ҳеҷ яке аз версияҳои Excel наметавонад чунин як кори оддӣ ва заруриро дар парвоз иҷро кунад, ба монанди сохтани хулоса барои якчанд диапазони ибтидоии додаҳо, масалан, дар варақаҳои гуногун ё ҷадвалҳои гуногун:

Пеш аз оғози кор, биёед якчанд нуктаро равшан кунем. Пеш аз ҳама, ман боварӣ дорам, ки дар маълумоти мо шартҳои зерин риоя карда мешаванд:

• Ҷадвалҳо метавонанд шумораи сатрҳои дорои ҳама гуна маълумот дошта бошанд, аммо онҳо бояд як сарлавҳа дошта бошанд.
• Дар варақаҳои дорои ҷадвалҳои манбаъ маълумоти иловагӣ набояд мавҷуд бошад. Як варақ - як ҷадвал. Барои назорат, ман ба шумо маслиҳат медиҳам, ки миёнабури клавиатураро истифода баред Ctrl+Поён, ки шуморо ба ячейкаи охирини истифодашуда дар варақаи корӣ интиқол медиҳад. Идеалӣ, ин бояд ячейкаи охирин дар ҷадвали маълумот бошад. Агар вақте ки шумо пахш кунед Ctrl+Поён ягон ячейкаи холӣ дар тарафи рост ё поёни ҷадвал таъкид карда мешавад - ин сутунҳои холии тарафи рост ё сатрҳои поёни ҷадвал пас аз ҷадвалро нест кунед ва файлро захира кунед.

Усули 1: Бо истифода аз Power Query ҷадвалҳо барои pivot созед

Аз версияи соли 2010 барои Excel сар карда, як замимаи ройгони Power Query мавҷуд аст, ки метавонад ҳама гуна маълумотро ҷамъоварӣ ва табдил диҳад ва сипас онро ҳамчун сарчашма барои сохтани ҷадвали пивот диҳад. Бо ёрии ин замима хал кардани проблемам мо асло душвор нест.

Аввалан, биёед дар Excel файли нави холӣ эҷод кунем - дар он монтаж сурат мегирад ва сипас дар он ҷадвали ҷамъоварӣ сохта мешавад.

Сипас дар ҷадвал Маълумот (агар шумо Excel 2016 ё дертар дошта бошед) ё дар ҷадвал Дархости барқ (агар шумо Excel 2010-2013 дошта бошед) фармонро интихоб кунед Эҷоди дархост - Аз файл - Excel (Маълумот гиред - Аз файл - Excel) ва файли сарчашмаро бо ҷадвалҳои ҷамъоварӣ муайян кунед:

Дар равзанаи пайдошуда ягон варақро интихоб кунед (фарқ надорад, ки кадомаш аст) ва тугмаи зерро пахш кунед иваз (Таҳрир):

Равзанаи муҳаррири дархости Power Query бояд дар болои Excel кушода шавад. Дар тарафи рости тиреза дар панел Параметрҳои дархост ҳама қадамҳои ба таври худкор сохташударо нест кунед, ба истиснои қадамҳои аввал - сарчашма (Сарчашма):

Ҳоло мо рӯйхати умумии ҳамаи варақҳоро мебинем. Агар ба ғайр аз варақаҳои маълумот дар файл якчанд варақаҳои паҳлӯии дигар мавҷуд бошанд, пас дар ин қадам вазифаи мо ин аст, ки танҳо он варақҳоеро интихоб кунем, ки аз онҳо маълумот бояд бор карда шавад, ба истиснои ҳамаи дигарон бо истифода аз филтр дар сарлавҳаи ҷадвал:

Ҳама сутунҳоро ба истиснои сутун нест кунед Маълумотбо пахши тугмаи рости сарлавҳаи сутун ва интихоб Сутунҳои дигарро нест кунед (Кор кардан сутунҳои дигар):

Пас шумо метавонед мундариҷаи ҷадвалҳои ҷамъшударо бо пахш кардани тирчаи дукарата дар болои сутун васеъ кунед (қуттии қайд Номи аслии сутунро ҳамчун префикс истифода баред шумо метавонед онро хомӯш кунед):

Агар шумо ҳама чизро дуруст анҷом дода бошед, пас дар ин лаҳза шумо бояд мундариҷаи ҳамаи ҷадвалҳои дар поёни дигар ҷамъшударо бинед:

Бо тугма баланд бардоштани сатри аввал ба сарлавҳаи ҷадвал боқӣ мемонад Сатри аввалро ҳамчун сарлавҳа истифода баред (Сатри аввалро ҳамчун сарлавҳа истифода баред) ҷадвали хона (Хона) ва бо истифода аз филтр сарлавҳаҳои ҷадвали такрориро аз маълумот хориҷ кунед:

Ҳама чизеро, ки бо фармон анҷом дода шудааст, захира кунед Пӯшед ва бор кунед - Пӯшед ва бор кунед… (Бастан ва бор кардан — Пӯшидан ва бор кардан ба…) ҷадвали хона (Хона), ва дар равзанаи кушодашуда, интихобро интихоб кунед Танҳо пайвастшавӣ (Танҳо пайвастшавӣ):

Ҳама чиз. Он танҳо барои сохтани хулоса боқӣ мемонад. Барои ин, ба ҷадвал равед Ворид - Ҷадвали ҷамъбастӣ (Ворид кардан — Ҷадвали ҷамъбастӣ), интихобро интихоб кунед Манбаи маълумоти берунаро истифода баред (Истифода аз манбаи додаҳои беруна)ва он гоҳ бо пахш кардани тугма Пайвастшавӣ интихоб кунед, дархости мо. Эҷод ва конфигуратсияи минбаъдаи pivot ба таври комилан стандартӣ тавассути кашола кардани майдонҳои ба мо дар сатрҳо, сутунҳо ва майдони арзишҳо зарурӣ сурат мегирад:

Агар маълумоти манбаъ дар оянда тағир ёбад ё якчанд варақаҳои дигари мағоза илова карда шаванд, он гоҳ барои нав кардани дархост ва хулосаи мо бо истифода аз фармон кофӣ хоҳад буд. Ҳамаро нав кунед ҷадвали Маълумот (Маълумот - Ҳамаро навсозӣ кунед).

Усули 2. Мо ҷадвалҳоро бо фармони UNION SQL дар макрос муттаҳид мекунем

Ҳалли дигари мушкилоти мо бо ин макрос муаррифӣ мешавад, ки бо истифода аз фармон маҷмӯи маълумотро (кеш) барои ҷадвали ҷамъоварӣ эҷод мекунад. БОЙГОНӢ Забони дархости SQL. Ин фармон ҷадвалҳоро аз ҳама дар массив нишондода муттаҳид мекунад Номҳои варақ варақаҳои китоб ба ҷадвали ягонаи додаҳо. Яъне, ба ҷои он ки диапазонҳоро аз варақҳои гуногун ба як нусхабардорӣ ва часбонед, мо дар хотираи RAM-и компютер ҳамин корро мекунем. Сипас макрос варақи навро бо номи додашуда (тағйирёбанда Натижа варақаи номи) ва дар асоси он дар асоси кэши ҷамъшуда хулосаи пурра (!) эҷод мекунад.

Барои истифодаи макрос, тугмаи Visual Basic-ро дар ҷадвал истифода баред таҳиякунанда (Таҳиягар) ё миёнабури клавиатура Alt+F11. Сипас мо ба воситаи меню як модули нави холӣ ворид мекунем Ворид - Модул ва рамзи зеринро дар он ҷо нусхабардорӣ кунед:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS Ҳамчун Объект Торикӣ Натоиҷи SheetName Ҳамчун сатри хира варақҳои Номҳо Ҳамчун Вариант 'номи варақ, ки дар он пивот дар натиҷа дар он варақаи Наҷотнамо" намоиш дода мешавад. номҳо бо ҷадвалҳои сарчашма SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'мо барои ҷадвалҳо аз варақаҳо аз SheetsNames бо ActiveWorkbook ReDim arSQL (1 Ба (UBound(SheetsNames) + 1) кэш ташкил мекунем. ) Барои 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; Source Data=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Бо 'аз нав эҷод кардани варақ барои намоиш додани ҷадвали ҷамъбастии натиҷа Дар Хатои идомаи Барномаи навбатӣ.DisplayAlerts = Варақаҳои кории бардурӯғ(ResultSheetName).Нест кардани маҷмӯи wsPivot = Варақаҳои корӣ.Илова кардани wsPivo т. Name = ResultSheetName 'нишон додани хулосаи кэши тавлидшуда дар ин варақ Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Ҳеҷ чиз бо wsPivot objPivotCachestination Table:)SetPivotCache:Pable. objPivotCache = Диапазон Ҳеҷ чиз ("A3"). Анҷомро бо поёни зер интихоб кунед    

Пас аз он макроси тайёрро бо миёнабури клавиатура иҷро кардан мумкин аст 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 ин функсияро аз муколамаи стандартӣ ҳангоми эҷоди ҳисоботи ҷадвали ҷамъоварӣ хориҷ кард. Ҳоло ин хусусият танҳо тавассути тугмаи фармоишӣ дастрас аст Устоди Ҷадвали ҷамъбастӣ(Устоди ҷадвали ҷамъбастӣ), ки агар хоҳед, онро ба панели асбобҳои дастрасии зуд илова кардан мумкин аст Файл - Параметрҳо - Танзими панели дастрасии зуд - Ҳама фармонҳо (Файл — Интихоб — Танзими панели дастрасии зуд — Ҳама фармонҳо):

Пас аз пахш кардани тугмаи иловашуда, шумо бояд дар қадами аввали устод имконоти мувофиқро интихоб кунед:

Ва он гоҳ дар равзанаи навбатӣ ҳар як диапазонро бо навбат интихоб кунед ва онро ба рӯйхати умумӣ илова кунед:

Аммо, боз, ин хулосаи мукаммал нест, бинобар ин, аз он зиёд интизор нашавед. Ман метавонам ин вариантро танҳо дар ҳолатҳои хеле содда тавсия диҳам.

• Эҷоди гузоришҳо бо ҷадвалҳои Pivot
• Ҳисобҳоро дар ҷадвалҳои ҷамъоварӣ насб кунед
• Макросҳо чист, чӣ тавр истифода бурдани онҳо, дар куҷо нусхабардории рамзи VBA ва ғайра.
• Ҷамъоварии маълумот аз якчанд варақ ба як (иловаи PLEX)