مواد
مسئلي جي جوڙجڪ
اچو ته هڪ خوبصورت حل تي نظر وجهون هڪ تمام معياري حالتن لاءِ جنهن کي اڪثر Excel استعمال ڪندڙ جلد يا دير سان منهن ڏين ٿا: توهان کي جلدي ۽ خودڪار طريقي سان ڊيٽا گڏ ڪرڻ جي ضرورت آهي فائلن جي وڏي تعداد مان هڪ فائنل ٽيبل ۾.
فرض ڪريو اسان وٽ ھيٺ ڏنل فولڊر آھي، جنھن ۾ شاخن جي شھرن مان ڊيٽا سان گڏ ڪيتريون ئي فائلون شامل آھن.
فائلن جو تعداد ڪو به فرق نٿو پوي ۽ مستقبل ۾ تبديل ٿي سگھي ٿو. هر فائل کي هڪ شيٽ جو نالو ڏنو ويو آهي سهڻاجتي ڊيٽا جدول واقع آهي:
جدولن ۾ قطار (آرڊر) جو تعداد، يقينا، مختلف آهي، پر ڪالمن جو سيٽ هر جڳهه معياري آهي.
ٽاسڪ: سڀني فائلن مان ڊيٽا گڏ ڪرڻ لاءِ هڪ ڪتاب ۾ بعد ۾ خودڪار تازه ڪاري سان جڏهن شهر جون فائلون يا قطارون شامل ڪرڻ يا حذف ڪرڻ. حتمي گڏيل جدول جي مطابق، پوء ان کي ڪنهن به رپورٽ، pivot جدول، فلٽر-ترتيب ڊيٽا، وغيره تعمير ڪرڻ ممڪن ٿيندو. بنيادي شيء گڏ ڪرڻ جي قابل ٿي ڪرڻ آهي.
اسان هٿيار چونڊيندا آهيون
حل لاءِ، اسان کي ضرورت آهي ايڪسل 2016 جو جديد نسخو (ضروري ڪارڪردگي اڳ ۾ ئي ان ۾ ڊفالٽ طور تي ٺهيل آهي) يا ايڪسل 2010-2013 جا پوئين ورزن مفت ايڊ-ان انسٽال ٿيل سان. طاقت جي طلب Microsoft کان (ان کي ڊائون لوڊ ڪريو هتي). Power Query هڪ سپر لچڪدار ۽ سپر طاقتور اوزار آهي ڊيٽا کي ايڪسل ۾ لوڊ ڪرڻ لاءِ ٻاهرين دنيا مان، پوءِ ان کي ڪڍڻ ۽ پروسيس ڪرڻ. پاور سوال تقريباً سڀني موجود ڊيٽا ذريعن کي سپورٽ ڪري ٿو - ٽيڪسٽ فائلن کان SQL ۽ حتي Facebook 🙂
جيڪڏهن توهان وٽ Excel 2013 يا 2016 نه آهي، ته پوء توهان اڳتي نه پڙهي سگهو ٿا (صرف مذاق). ايڪسل جي پراڻن ورزن ۾، اهڙو ڪم صرف Visual Basic ۾ ميڪرو پروگرامنگ (جيڪو شروع ڪرڻ وارن لاءِ تمام ڏکيو آهي) يا هڪجهڙائي واري دستي ڪاپي ڪرڻ سان (جيڪو گهڻو وقت وٺندو آهي ۽ غلطيون پيدا ڪري ٿو) ڪري سگهجي ٿو.
قدم 1. ھڪڙي فائل ھڪڙي نموني طور درآمد ڪريو
پهرين، اچو ته هڪ ورڪ بڪ مان ڊيٽا درآمد ڪريون مثال طور، انهي ڪري ته ايڪسل "خيال کي کڻندو". ائين ڪرڻ لاءِ، هڪ نئون خالي ورڪ بڪ ٺاهيو ۽…
- جيڪڏھن توھان وٽ آھي Excel 2016، پوءِ ٽيب کوليو ڊيٽا ۽ پوءِ سوال ٺاهيو - فائل مان - ڪتاب مان (ڊيٽا - نئون سوال - فائل مان - ايڪسل کان)
- جيڪڏهن توهان وٽ ايڪسل 2010-2013 آهي پاور سوال ايڊ-ان انسٽال ٿيل آهي ته پوءِ ٽيب کوليو طاقت جي طلب ۽ ان تي چونڊيو فائل مان- ڪتاب مان (فائل مان - ايڪسل کان)
ان کان پوء، ونڊو ۾ جيڪو کلي ٿو، اسان جي فولڊر ڏانهن وڃو رپورٽون ۽ چونڊيو شهر جي فائلن مان ڪنهن کي (اها ڳالهه ناهي ته ڪهڙي هڪ، ڇو ته اهي سڀ عام آهن). ڪجهه سيڪنڊن کان پوءِ، نيويگيٽر ونڊو ظاهر ٿيڻ گهرجي، جتي توهان کي گهربل شيٽ چونڊڻ جي ضرورت آهي (سيلز) کاٻي پاسي، ۽ ان جو مواد ساڄي پاسي ڏيکاريو ويندو:
جيڪڏهن توهان هن ونڊو جي هيٺئين ساڄي ڪنڊ ۾ بٽڻ تي ڪلڪ ڪريو لاهيو (لوڊ)، پوءِ ٽيبل کي فوري طور تي ان جي اصل شڪل ۾ شيٽ تي درآمد ڪيو ويندو. ھڪڙي فائل لاء، اھو سٺو آھي، پر اسان کي اھڙيون ڪيتريون ئي فائلون لوڊ ڪرڻ جي ضرورت آھي، تنھنڪري اسان ٿوري مختلف طريقي سان وڃو ۽ بٽڻ کي دٻايو. اصلاح (ايڊٽ ڪيو). ان کان پوء، پاور سوال سوال ايڊيٽر کي هڪ الڳ ونڊو ۾ اسان جي ڪتاب مان ڊيٽا سان ظاهر ڪيو وڃي:
هي هڪ تمام طاقتور اوزار آهي جيڪو توهان کي اجازت ڏئي ٿو "ختم" ڪرڻ جي ٽيبل کي ڏسڻ لاءِ جيڪو اسان جي ضرورت آهي. جيتوڻيڪ ان جي سڀني ڪمن جو هڪ سطحي بيان تقريباً سؤ صفحن تي مشتمل هوندو، پر، جيڪڏهن تمام مختصر، هن ونڊو کي استعمال ڪندي توهان ڪري سگهو ٿا:
- غير ضروري ڊيٽا کي فلٽر ڪريو، خالي لائينون، خطن سان لائنون
- ھڪڙي يا وڌيڪ ڪالمن سان ڊيٽا کي ترتيب ڏيو
- ورجائي کان نجات حاصل ڪريو
- لچڪدار متن کي ڪالمن سان ورهايو (حد بندي ڪندڙ، اکرن جو تعداد، وغيره)
- متن کي ترتيب ۾ رکو (اضافي جاءن کي هٽايو، صحيح ڪيس، وغيره)
- ڊيٽا جي قسمن کي هر ممڪن طريقي سان تبديل ڪريو (نمبرن کي تبديل ڪريو جهڙوڪ ٽيڪسٽ عام نمبرن ۾ ۽ ان جي برعڪس)
- جدولن کي منتقل ڪريو (گھمڻ) ۽ ٻه طرفي ڪراس ٽيبل کي فليٽ ۾ وڌايو
- ٽيبل تي اضافي ڪالمن شامل ڪريو ۽ پاور سوال ۾ ٺهيل M ٻولي استعمال ڪندي انهن ۾ فارمولا ۽ افعال استعمال ڪريو.
- ...
مثال طور، اچو ته اسان جي ٽيبل تي مهيني جي ٽيڪسٽ نالي سان هڪ ڪالم شامل ڪريون، ته جيئن بعد ۾ پيوٽ ٽيبل رپورٽون ٺاهڻ ۾ آساني ٿئي. هن کي ڪرڻ لاء، ڪالمن جي سر تي صحيح ڪلڪ ڪريو تاريخ۽ حڪم چونڊيو نقل ڪيل ڪالم (نقل ڪالم)، ۽ پوءِ ڊپليڪيٽ ڪالمن جي هيڊر تي ساڄي ڪلڪ ڪريو جيڪو ظاهر ٿئي ٿو ۽ منتخب ڪريو حڪم تبديلي - مهيني - مهيني جو نالو:
هر قطار لاءِ مهيني جي متن جي نالن سان هڪ نئون ڪالم ٺاهيو وڃي. ڪالم جي عنوان تي ڊبل ڪلڪ ڪندي، توھان ان جو نالو مٽائي سگھوٿا ڪاپي جي تاريخ وڌيڪ آرام سان مهينومثال طور.
جيڪڏهن ڪجهه ڪالمن ۾ پروگرام ڊيٽا جي قسم کي صحيح طور تي سڃاڻي نه سگهيو، پوء توهان هر ڪالمن جي کاٻي پاسي فارميٽ آئڪن تي ڪلڪ ڪري ان جي مدد ڪري سگهو ٿا:
توهان هڪ سادي فلٽر استعمال ڪندي، غلطين يا خالي لائينن سان گڏ غير ضروري مينيجرز يا گراهڪ کي خارج ڪري سگھو ٿا:
ان کان علاوه، سڀ انجام ڏنل تبديليون ساڄي پينل ۾ مقرر ٿيل آھن، جتي اھي ھميشه ڦري سگھجن ٿيون (ڪراس) يا انھن جا پيرا ميٽر تبديل ڪري سگھجن ٿا (گيئر):
هلڪو ۽ خوبصورت، اهو ناهي؟
قدم 2. اچو ته اسان جي درخواست کي فنڪشن ۾ تبديل ڪريون
بعد ۾ هر درآمد ٿيل ڪتاب لاءِ ڪيل ڊيٽا جي سڀني تبديلين کي ورجائڻ لاءِ، اسان کي پنهنجي ٺاهيل درخواست کي هڪ فنڪشن ۾ تبديل ڪرڻو پوندو، جيڪو پوءِ لاڳو ڪيو ويندو، بدلي ۾، اسان جي سڀني فائلن تي. ائين ڪرڻ اصل ۾ بلڪل سادو آهي.
سوال ايڊيٽر ۾، ڏسو ٽيب ڏانھن وڃو ۽ بٽڻ تي ڪلڪ ڪريو ترقي يافته ايڊيٽر (ڏسو - ڳوڙهي ايڊيٽر). هڪ ونڊو کولڻ گهرجي جتي اسان جا سڀ پوئين عمل ڪوڊ جي صورت ۾ M ٻولي ۾ لکيل هوندا. مهرباني ڪري نوٽ ڪريو ته فائل جو رستو جيڪو اسان مثال طور درآمد ڪيو آهي ڪوڊ ۾ هارڊ ڪوڊ ٿيل آهي:
هاڻي اچو ته ڪجهه ترميمون ڪيون:
انهن جي معني سادو آهي: پهرين لائن (فائل پاٿ) => اسان جي عمل کي هڪ دليل سان فنڪشن ۾ تبديل ڪري ٿو فائيل پاٿ, ۽ هيٺ اسين هن متغير جي قيمت تي مقرر ٿيل رستو تبديل ڪندا آهيون.
سڀ. تي ڪلڪ ڪريو ختم ڪر ۽ هن کي ڏسڻ گهرجي:
ڊپ نه ٿيو ته ڊيٽا غائب ٿي وئي آهي - حقيقت ۾، سڀ ڪجهه ٺيڪ آهي، هر شيء کي هن وانگر ڏسڻ گهرجي 🙂 اسان ڪاميابي سان پنهنجو ڪسٽم فنڪشن ٺاهيو آهي، جتي ڊيٽا کي درآمد ڪرڻ ۽ پروسيسنگ لاء سڄو الگورتھم ڪنهن مخصوص فائل سان ڳنڍڻ کان سواء ياد ڪيو ويندو آهي. . اهو باقي رهي ٿو ان کي وڌيڪ سمجھڻ وارو نالو ڏيو (مثال طور حاصل ڊيٽا) فيلڊ ۾ ساڄي پاسي پينل ۾ پهريون نالو ۽ توهان حاصل ڪري سگهو ٿا گھر - بند ڪريو ۽ ڊائون لوڊ ڪريو (گهر - بند ۽ لوڊ). مهرباني ڪري نوٽ ڪريو ته فائل جو رستو جيڪو اسان مثال طور درآمد ڪيو آهي ڪوڊ ۾ هارڊ ڪوڊ ٿيل آهي. توهان مکيه Microsoft Excel ونڊو ڏانهن موٽندا، پر اسان جي فنڪشن سان ٺهيل ڪنيڪشن سان هڪ پينل ساڄي پاسي ظاهر ٿيڻ گهرجي:
قدم 3. سڀ فائلون گڏ ڪرڻ
تمام ڏکيو حصو پوئتي آهي، خوشگوار ۽ آسان حصو رهي ٿو. ٽيب ڏانھن وڃو ڊيٽا - سوال ٺاھيو - فائل کان - فولڊر مان (ڊيٽا - نئون سوال - فائل مان - فولڊر مان) يا، جيڪڏهن توهان وٽ آهي ايڪسل 2010-2013، ساڳي طرح ٽيب سان طاقت جي طلب. ونڊو ۾ جيڪو ظاهر ٿئي ٿو، فولڊر جي وضاحت ڪريو جتي اسان جون سڀئي ماخذ سٽي فائلون واقع آهن ۽ ڪلڪ ڪريو OK. ايندڙ قدم کي هڪ ونڊو کولڻ گهرجي جتي سڀئي ايڪسل فائلون هن فولڊر ۾ مليون (۽ ان جا ذيلي فولڊر) ۽ انهن مان هر هڪ لاءِ تفصيل درج ڪيا ويندا:
ڪلڪ ڪيو تبديل ڪريو (ايڊٽ ڪيو) ۽ ٻيهر اسان واقف سوال ايڊيٽر ونڊو ۾ وڃون ٿا.
هاڻي اسان کي اسان جي ٽيبل تي هڪ ٻيو ڪالم شامل ڪرڻو پوندو اسان جي ٺاهيل فنڪشن سان، جيڪو هر فائل مان ڊيٽا کي "پل" ڪندو. هن کي ڪرڻ لاء، ٽيب ڏانهن وڃو ڪالم شامل ڪريو - ڪسٽم ڪالم (ڪالمن شامل ڪريو - ڪسٽم ڪالم شامل ڪريو) ۽ ونڊو ۾ جيڪو ظاهر ٿئي ٿو، اسان جي فنڪشن داخل ڪريو حاصل ڊيٽا، ان لاءِ وضاحت ڪندي دليل جي طور تي هر فائل لاءِ مڪمل رستو:
تي ڪلڪ ڪرڻ کان پوء OK ٺهيل ڪالمن کي ساڄي پاسي اسان جي ٽيبل تي شامل ڪيو وڃي.
ھاڻي اچو ته سڀ غير ضروري ڪالمن کي ختم ڪريون (جيئن ته Excel ۾، ساڄي مائوس جي بٽڻ کي استعمال ڪندي - هٽايو)، صرف شامل ڪيل ڪالمن ۽ ڪالمن کي فائل جي نالي سان ڇڏي ڏيو، ڇاڪاڻ ته هي نالو (وڌيڪ واضح طور تي، شهر) هر قطار جي مجموعي ڊيٽا ۾ رکڻ لاء ڪارائتو ٿيندو.
۽ ھاڻي "واھ لمحو" - اسان جي فنڪشن سان شامل ڪيل ڪالمن جي مٿين ساڄي ڪنڊ ۾ پنھنجي تيرن سان آئڪن تي ڪلڪ ڪريو:
… اڻ چيڪ ڪريو اصل ڪالمن جو نالو استعمال ڪريو اڳياڙي طور (اصل ڪالم جو نالو استعمال ڪريو اڳياڙي طور)۽ ڪلڪ ڪريو OK. ۽ اسان جو فنڪشن هر فائل مان ڊيٽا کي لوڊ ۽ پروسيس ڪندو، رڪارڊ ٿيل الگورٿم تي عمل ڪندي ۽ هر شي کي عام ٽيبل ۾ گڏ ڪندي:
مڪمل خوبصورتي لاءِ، توھان پڻ ختم ڪري سگھو ٿا .xlsx ايڪسٽينشن کي پھرين ڪالمن مان فائل نالن سان - معياري متبادل سان "ڪجھ به نه" (ڪالمن جي ھيڊر تي ساڄي ڪلڪ ڪريو - سقراط) ۽ هن ڪالم جو نالو تبديل ڪريو جذبات. ۽ تاريخ سان گڏ ڪالمن ۾ ڊيٽا فارميٽ کي به درست ڪريو.
سڀ! تي ڪلڪ ڪريو گھر - بند ڪريو ۽ لوڊ ڪريو (گهر - بند ۽ لوڊ). سڀني شهرن لاءِ پڇا ڳاڇا ذريعي گڏ ڪيل سمورو ڊيٽا موجوده ايڪسل شيٽ تي ”سمارٽ ٽيبل“ فارميٽ ۾ اپ لوڊ ڪيو ويندو:
ٺهيل ڪنيڪشن ۽ اسان جي اسيمبلي جي فنڪشن کي ڪنهن به طريقي سان الڳ الڳ محفوظ ڪرڻ جي ضرورت ناهي - اهي عام طريقي سان موجوده فائل سان گڏ محفوظ ڪيا ويا آهن.
مستقبل ۾، فولڊر ۾ ڪنهن به تبديلي سان (شهرن کي شامل ڪرڻ يا ختم ڪرڻ) يا فائلن ۾ (لائنن جو تعداد تبديل ڪرڻ)، اهو ڪافي ٿيندو سڌو سنئون ٽيبل تي يا سوال تي ڪلڪ ڪريو ساڄي پينل ۾ ۽ چونڊيو. حڪم تازه ڪاري ۽ محفوظ ڪريو (ريفريش) - پاور سوال ڪجهه سيڪنڊن ۾ سڀ ڊيٽا ٻيهر "ٻيهر ٺاهي" ڪندو.
PS
ترميم. جنوري 2017 جي اپڊيٽس کان پوءِ، Power Query سکي ورتو ته ڪيئن Excel Workbooks گڏ ڪجي پاڻمرادو، يعني هاڻي ڪا الڳ فنڪشن ڪرڻ جي ضرورت ناهي - اهو خود بخود ٿئي ٿو. اهڙيء طرح، هن مضمون کان ٻئي قدم جي ضرورت نه آهي ۽ سڄو عمل واضح طور تي آسان ٿي وڃي ٿو:
- چونڊيو درخواست ٺاهيو - فائل کان - فولڊر کان - فولڊر چونڊيو - ٺيڪ
- فائلن جي لسٽ ظاهر ٿيڻ کان پوء، دٻايو تبديل ڪريو
- Query Editor ونڊو ۾، ڊبل تير سان بائنري ڪالمن کي وڌايو ۽ شيٽ جو نالو چونڊيو جيڪو هر فائل مان ورتو وڃي.
۽ اهو سڀ ڪجهه آهي! گيت!
- ڪراس ٽيب کي ٻيهر ڊزائين ۾ هڪ فليٽ ۾ ٺاهيو جيڪو پيوٽ ٽيبل ٺاهڻ لاءِ موزون آهي
- پاور View ۾ هڪ متحرڪ بلبل چارٽ ٺاهڻ
- مختلف ايڪسل فائلن مان شيٽ گڏ ڪرڻ لاءِ ميڪرو