ڪيترن ئي ڊيٽا جي حدن ۾ پائيوٽ ٽيبل

مسئلي جي جوڙجڪ

Pivot Tables Excel ۾ سڀ کان وڌيڪ حيرت انگيز اوزارن مان هڪ آهن. پر هينئر تائين، بدقسمتيءَ سان، ايڪسل جو ڪو به نسخو اڏامڻ تي اهڙو سادو ۽ ضروري ڪم نٿو ڪري سگهي، جيئن موجود ڪيترن ئي ابتدائي ڊيٽا جي حدن لاءِ خلاصو ٺاهي، مثال طور، مختلف شيٽس تي يا مختلف جدولن ۾:

ان کان اڳ جو اسان شروع ڪريون، اچو ته ڪجهه نقطا واضح ڪريون. هڪ ترجيح، مان سمجهان ٿو ته اسان جي ڊيٽا ۾ هيٺيون شرطون ملن ٿيون:

• جدولن ۾ ڪنهن به ڊيٽا سان قطارن جو ڪو به تعداد ٿي سگھي ٿو، پر انھن وٽ ھڪڙو ئي مٿو ھجڻ گھرجي.
• ماخذ جدولن سان شيٽ تي ڪو به اضافي ڊيٽا نه هجڻ گهرجي. هڪ چادر - هڪ ٽيبل. ڪنٽرول ڪرڻ لاء، مان توهان کي ڪيبورڊ شارٽ ڪٽ استعمال ڪرڻ جي صلاح ڏيان ٿو Ctrl+آخر ۾، جيڪو توهان کي ورڪ شيٽ ۾ آخري استعمال ٿيل سيل ڏانهن منتقل ڪري ٿو. مثالي طور، هي ڊيٽا ٽيبل ۾ آخري سيل هجڻ گهرجي. جيڪڏھن توھان ڪلڪ ڪريو Ctrl+آخر ۾ ڪنهن به خالي سيل کي ساڄي طرف يا ٽيبل جي هيٺان نمايان ڪيو ويو آهي - انهن خالي ڪالمن کي ساڄي طرف يا ٽيبل جي هيٺان قطار کي ٽيبل کان پوءِ حذف ڪريو ۽ فائل کي محفوظ ڪريو.

طريقو 1: پاور سوال استعمال ڪندي پيوٽ لاءِ ٽيبل ٺاھيو

ايڪسل لاءِ 2010 ورجن کان شروع ٿي، اتي هڪ مفت پاور سوال ايڊ-ان آهي جيڪو ڪنهن به ڊيٽا کي گڏ ڪري ۽ تبديل ڪري سگهي ٿو ۽ پوءِ ان کي هڪ پيوٽ ٽيبل ٺاهڻ لاءِ ذريعو طور ڏئي ٿو. هن ايڊ-ان جي مدد سان اسان جو مسئلو حل ڪرڻ ڪو به ڏکيو نه آهي.

پهرين، اچو ته ايڪسل ۾ هڪ نئين خالي فائل ٺاهيو - ان ۾ اسيمبلي ٿيندي ۽ پوء ان ۾ هڪ پيوٽ ٽيبل ٺاهي ويندي.

پوء ٽيب تي ڊيٽا (جيڪڏهن توهان وٽ آهي Excel 2016 يا بعد ۾) يا ٽيب تي طاقت جي طلب (جيڪڏهن توهان وٽ آهي Excel 2010-2013) حڪم چونڊيو سوال ٺاهيو - فائل مان - ايڪسل (ڊيٽا حاصل ڪريو - فائل مان - Excel) ۽ ماخذ فائل کي گڏ ڪرڻ جي جدولن سان بيان ڪريو:

ونڊو ۾ جيڪو ظاهر ٿئي ٿو، ڪنهن به شيٽ کي چونڊيو (اهو مسئلو ناهي ته ڪهڙو آهي) ۽ هيٺ ڏنل بٽڻ کي دٻايو تبديل ڪريو (ايڊٽ ڪيو):

پاور سوال سوال ايڊيٽر ونڊو کولڻ گهرجي ايڪسل جي چوٽي تي. ونڊو جي ساڄي پاسي پينل تي درخواست جي پيراگراف سڀ پاڻمرادو ٺاهيل مرحلا حذف ڪريو سواءِ پهرين - ذريعو (ذريعو):

ھاڻي اسان سڀني شيٽن جي ھڪڙي عام فهرست ڏسون ٿا. جيڪڏهن ڊيٽا شيٽ کان علاوه فائل ۾ ڪجهه ٻيون سائڊ شيٽون به آهن، ته پوءِ هن مرحلي تي اسان جو ڪم صرف انهن شيٽن کي چونڊڻو آهي جن مان معلومات کي لوڊ ڪرڻ جي ضرورت آهي، سواءِ ٻين سڀني کي فلٽر استعمال ڪندي ٽيبل هيڊر ۾:

ڪالم کان سواءِ سڀ ڪالم ختم ڪريو ڊيٽاهڪ ڪالم جي عنوان تي صحيح ڪلڪ ڪندي ۽ چونڊيو ٻين ڪالمن کي ختم ڪريو (هٽايو ٻيا ڪالم):

توھان وري گڏ ڪري سگھوٿا گڏ ڪيل جدولن جي مواد کي ڪالم جي مٿئين پاسي ڊبل تير تي ڪلڪ ڪري (چيڪ باڪس اصل ڪالمن جو نالو استعمال ڪريو اڳياڙي طور توهان ان کي بند ڪري سگهو ٿا):

جيڪڏهن توهان سڀ ڪجهه صحيح طريقي سان ڪيو، ته هن نقطي تي توهان کي ڏسڻ گهرجي ته سڀني جدولن جو مواد هڪ ٻئي هيٺ گڏ ڪيو ويو آهي:

اهو بٽڻ سان ٽيبل جي هيڊر ڏانهن پهرين قطار کي وڌائڻ لاء رهي ٿو هيڊر طور پهرين لائن استعمال ڪريو (پهرين قطار کي هيڊر طور استعمال ڪريو) ٽئب گھر جي (گهريلو) ۽ فلٽر استعمال ڪندي ڊيٽا مان نقل ٽيبل هيڊر هٽايو:

حڪم سان ڪيو ويو سڀڪنھن شيء کي محفوظ ڪريو بند ڪريو ۽ لوڊ ڪريو - بند ڪريو ۽ لوڊ ڪريو ... (بند ڪريو ۽ لوڊ ڪريو - بند ڪريو ۽ لوڊ ڪريو ...) ٽئب گھر جي (گهريلو)، ۽ ونڊو ۾ جيڪو کلي ٿو، اختيار چونڊيو صرف ڪنيڪشن (صرف ڪنيڪشن):

سڀ ڪجهه. اهو صرف هڪ خلاصو تعمير ڪرڻ لاء رهي ٿو. هن کي ڪرڻ لاء، ٽيب ڏانهن وڃو داخل ڪريو - PivotTable (داخل ڪريو - پيوٽ ٽيبل)، اختيار چونڊيو خارجي ڊيٽا جو ذريعو استعمال ڪريو (ٻاهرين ڊيٽا جو ذريعو استعمال ڪريو)۽ پوء بٽڻ تي ڪلڪ ڪندي ڪنيڪشن چونڊيو، اسان جي درخواست. وڌيڪ ٺاھڻ ۽ پيوٽ جي ترتيب مڪمل طور تي معياري طريقي سان ٿئي ٿي فيلڊ کي ڇڪڻ سان اسان کي قطار، ڪالمن ۽ قدر واري علائقي ۾:

جيڪڏهن ماخذ ڊيٽا مستقبل ۾ تبديل ٿئي ٿي يا ڪجھ وڌيڪ اسٽور شيٽ شامل ڪيا ويا آهن، پوء اهو حڪم استعمال ڪندي سوال ۽ اسان جو خلاصو تازه ڪاري ڪرڻ لاء ڪافي هوندو. سڀني کي تازو ڪريو ٽئب ڊيٽا (ڊيٽا - سڀ تازو ڪريو).

طريقو 2. اسان ميڪرو ۾ UNION SQL ڪمانڊ سان جدولن کي متحد ڪريون ٿا

اسان جي مسئلي جو ٻيو حل هن ميڪرو جي نمائندگي ڪري ٿو، جيڪو ڪمانڊ استعمال ڪندي پيوٽ ٽيبل لاءِ ڊيٽا سيٽ (ڪيش) ٺاهي ٿو. UNITY SQL سوال ٻولي. هي حڪم سڀني صفن ۾ بيان ڪيل جدولن کي گڏ ڪري ٿو شيٽ جا نالا ڪتاب جا ورق هڪ واحد ڊيٽا ٽيبل ۾. اهو آهي ته، مختلف شيٽ کان هڪ تائين رينج کي جسماني طور تي ڪاپي ۽ پيسٽ ڪرڻ بدران، اسان ڪمپيوٽر جي ريم ۾ ساڳيو ڪم ڪندا آهيون. پوءِ ميڪرو ڏنل نالي سان نئين شيٽ شامل ڪري ٿو (متغير ResultSheetName) ۽ گڏ ڪيل ڪيش جي بنياد تي ان تي هڪ مڪمل (!) خلاصو ٺاهي ٿو.

ميڪرو استعمال ڪرڻ لاءِ، ٽيب تي Visual Basic بٽڻ استعمال ڪريو ڊولپر (ڊولپر) يا ڪي بورڊ شارٽ ڪٽ ھن+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 'sheet names جتي نتيجي ۾ pivot ظاھر ڪيو ويندو 'ResultN' of "arSetanSheetName" ماخذ جدولن سان نالا SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta")' اسان شيٽ مان ٽيبلن لاءِ ڪيش ٺاھيون ٿا SheetsNames With ActiveWorkbook ReDim arSQL(1 کان (UBound(SheetsNames) + 1) ) i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" اڳيون آئون سيٽ ڪريو 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 = غلط ورڪ شيٽ(ResultSheetName) سيٽ ڪريو wsPivot = Worksheets شامل ڪريو. wsPivo شامل ڪريو t. Name = ResultSheetName 'هن شيٽ تي ٺاهيل ڪيش جو خلاصو ڏيکاريو سيٽ objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) objPivotCache سيٽ ڪريو.Recordset = objRS سيٽ objRS = ڪجھ به نه سان wsPivotacheable:"Tivotachable. objPivotCache = ڪجھ به نه رينج ("A3"). منتخب ڪريو آخر سان گڏ آخر جي ذيلي    

ختم ٿيل ميڪرو وري ڪيبورڊ شارٽ ڪٽ سان هلائي سگھجي ٿو ھن+F8 يا ٽيب تي ميڪرو بٽڻ ڊولپر (ڊولپر - ميڪروس).

هن طريقي جا نقصان:

• ڊيٽا اپڊيٽ نه ڪئي وئي آهي ڇاڪاڻ ته ڪيش جو ذريعو جدولن سان ڪو به تعلق ناهي. جيڪڏهن توهان ماخذ ڊيٽا کي تبديل ڪريو ٿا، توهان کي ٻيهر ميڪرو هلائڻ گهرجي ۽ خلاصو ٻيهر ٺاهيو.
• جڏهن شيٽ جو تعداد تبديل ڪيو وڃي، اهو ضروري آهي ته ميڪرو ڪوڊ کي تبديل ڪرڻ لاء (array شيٽ جا نالا).
  

پر آخر ۾ اسان هڪ حقيقي مڪمل پيوٽ ٽيبل حاصل ڪندا آهيون، مختلف شيٽ مان ڪيترن ئي حدن تي ٺهيل آهي:

وولا!

ٽيڪنيڪل نوٽ: جيڪڏهن توهان کي هڪ غلطي ملي ٿي جهڙوڪ "فراهم ڪندڙ رجسٽر ٿيل نه آهي" جڏهن ميڪرو هلائي رهيا آهيو، ته گهڻو ڪري توهان وٽ ايڪسل جو 64-بٽ نسخو آهي يا Office جو هڪ نامڪمل نسخو نصب ٿيل آهي (ڪو به رسائي ناهي). صورتحال کي درست ڪرڻ لاء، ميڪرو ڪوڊ ۾ ٽڪرا تبديل ڪريو:

	 فراهم ڪندڙ=Microsoft.Jet.OLEDB.4.0;  

جي طرف:

	فراهم ڪندڙ=Microsoft.ACE.OLEDB.12.0;  

۽ ڊائون لوڊ ۽ انسٽال ڪريو مفت ڊيٽا پروسيسنگ انجڻ تائين رسائي کان Microsoft ويب سائيٽ - Microsoft Access Database Engine 2010 Redistributable

طريقو 3: PivotTable Wizard کي Excel جي پراڻي ورزن مان مضبوط ڪريو

اهو طريقو ٿورو پراڻو آهي، پر اڃا به قابل ذڪر آهي. رسمي طور تي ڳالهائڻ، سڀني نسخن ۾ ۽ 2003 تائين شامل آهن، اتي هڪ اختيار هو PivotTable Wizard ۾ "ڪيترن ئي استحڪام جي حدن لاء هڪ محور ٺاهي". بهرحال، هڪ رپورٽ هن طريقي سان ٺاهي وئي، بدقسمتي سان، صرف هڪ حقيقي مڪمل خلاصي جي هڪ افسوسناڪ نموني هوندي ۽ روايتي پيوٽ ٽيبل جي ڪيترن ئي "چپس" جي حمايت نه ڪندي:

اهڙي پيوٽ ۾، فيلڊ لسٽ ۾ ڪو به ڪالم هيڊنگ نه هوندو آهي، ڪو به لچڪدار ڍانچي سيٽنگ نه هوندو آهي، استعمال ٿيل ڪمن جو سيٽ محدود هوندو آهي، ۽ عام طور تي، اهو سڀ ڪجهه هڪ پيوٽ ٽيبل سان گهڻو ملندو ناهي. شايد اهو ئي سبب آهي، 2007 ۾ شروع ٿي، Microsoft هن فنڪشن کي معياري ڊائلاگ مان هٽائي ڇڏيو جڏهن پيوٽ ٽيبل رپورٽون ٺاهيندي. ھاڻي ھي فيچر صرف ڪسٽم بٽڻ ذريعي دستياب آھي PivotTable مددگار(Pivot Table Wizard)، جنهن کي، جيڪڏهن گهربل هجي، شامل ڪري سگهجي ٿو Quick Access Toolbar ذريعي فائل - آپشنز - ڪسٽمائيز تڪڙو رسائي ٽول بار - سڀ حڪم (فائل - آپشنز - ڪسٽمائيز تڪڙي رسائي ٽول بار - سڀ ڪمانڊ):

شامل ٿيل بٽڻ تي ڪلڪ ڪرڻ کان پوء، توهان کي جادوگر جي پهرين قدم تي مناسب اختيار چونڊڻ جي ضرورت آهي:

۽ پوءِ ايندڙ ونڊو ۾، هر حد کي موڙ ۾ چونڊيو ۽ ان کي عام فهرست ۾ شامل ڪريو:

پر، ٻيهر، هي هڪ مڪمل خلاصو نه آهي، تنهنڪري ان مان تمام گهڻي اميد نه رکو. مان هن اختيار جي سفارش ڪري سگهان ٿو صرف تمام سادي ڪيسن ۾.

• PivotTables سان رپورٽون ٺاهڻ
• PivotTables ۾ حساب ڪتاب قائم ڪريو
• ميڪرو ڇا آهن، انهن کي ڪيئن استعمال ڪجي، ڪٿي نقل ڪجي VBA ڪوڊ وغيره.
• ڊيٽا گڏ ڪرڻ ڪيترن ئي شيٽ کان هڪ تائين (PLEX اضافو)