ادغام چند فایل اکسل با حالت های مختلف

جمع آوری و ادغام داده از فایل های مختلف اکسل

موضوع ادغام فایل های مشابه در اکسل و جمع آوری دیتای دلخواه از این فایل ها (یک مطلب در مورد ادغام داده از شیت های مختلف یک فایل داریم که پیشنهاد میکنم حتما بخونی)، یکی از مسائل پر تکرار برای کاربران بشمار میاد و هر کسی به نوع با این موضوع سر و کار داره. مثلا فرض کنید فایل های مشابهی توسط همکاران در یک واحد سازمانی پر می شوند، حالا باید این فایل ها تجمیع بشن و از داده های اونها گزارشگیری انجام بشه. یا مثلا فرض کنید یک فایل کلی امتیاز دهی به همکاران در یک واحد وجود داره و هر هفته امتیاز های جدید از واحدهای دیگه ارسال میشن و دیتای جدید باید بره و در فایل تجمعی (از ابتدا تا کنون) جایگذاری بشه. یا در مثالی دیگه، فرض کنید که مدیران فروش در هر شعبه، آمار فروش خودشون رو در بازه های زمانی خاصی برای شما ارسال کرده اند و حالا باید این مقادیر فروش رو تحلیل و ارزیابی کنید پس باید ابتدا بتونیم این داده ها رو با هم تجمیع کنیم و بعد اقدام به گزارشگیری کنیم.

در همه این مثال ها چون ساختار فایل تقریبا مشابه هست و میدونیم در هر فایل کدوم داده ها رو لازم داریم، میتونیم دیتای مورد نظر رو مرج کنیم. این کار رو با ابزار پاورکوئری Power Query انجام میدیم. در ادامه به بررسی حالت های مختلف این مسئله می پردازیم:

در ابتدا اگر این فایل ها در جاهای مختلفی ذخیره شده اند، همه رو به یک فولدر مشترک منتقل کنید. در ادامه ۳ سناریو مختلف رایج این موضوع رو بررسی میکنیم.

1. در هر فایل اکسل دیتای مورد نظر در قالب یک Table و با نام مشابه ذخیره شده است.
2. داده های مورد نظر در هر فایل، در یک شیت با نام یکسان در همه فایل ها ذخیره شده است. (مثلا اسم شیت مورد نظر در همه این فایل ها “فروش” است و ما میخواهیم دیتای شیت “فروش” رو تجمع کنیم.)
3. هر فایل، شیت ها و Table های متنوعی داره و ما میخوایم شیت یا Table مشخصی رو تجمیع کنیم. این روش برای زمانی که شیت یا Table شما نام مشخصی نداره هم کاربرد داره.

سناریو اول: هر فایل، یک Table با ساختار مشابه جهت ادغام

در این روش، ساختار Table ها باید مشابه باشد (سرستون ها با نام یکسان)، تعداد ردیف ها میتونه متفاوت باشه. اگر بعضی از Table ها ستون های اضافه ای هم دارند، جای نگرانی نیست ما در نهایت یکی از جدول هایی که مورد نظرمون هست رو به عنوان نمونه دلخواه معرفی میکنیم (این نمونه رو در پاورکوئری به عنوان  Key میشناسیم). پاورکوئری از این نمونه استفاده میکنه و بقیه Table ها رو با توجه به این نمونه تجمیع و ادغام میکنه.

برای همین اگر ستون های اضافه ای (نسبت به فایل Key) وجود داشته باشه در نظر گرفته نمیشن و فقط ستون هایی که در فایل Key مشخص شدن، در نهایت تجمیع میشن.

مثال زیر یک نمونه واقعی است و یکی از شاگردام سر کلاس مطرح کرد که اینجا هم با هم حلش میکنیم. سوال این بود که در یک فایل اکسل، هر هفته، نمره هایی از ارزیابی همکاران دریافت میکرد و این دیتا باید میرفت در یک دیتابیس کل ادغام میشد که بعدا امکان گزارشگیری از روی داده ها وجود داشته باشه. نمونه داده رو در شکل ۱ مشاهده میکنید. (این داده ها در همه فایل ها در قالب Table و با نام مشابه “Evaluate” ذخیره شده اند)

شکل ۱- نمونه دیتای مورد نظر جهت ادغام

در حال حاضر ۳ فایل اکسل برای ۳ هفته اول بهمن مطابق شکل ۲ ذخیره کرده ایم:

شکل ۲- ذخیره ۳ فایل از ارزیابی کارکنان برای ۳ هفته اول بهمن

حالا برای تجمیع و ادغام این ۳ فایل، مراحل زیر را انجام میدهیم:

1. یک فایل اکسل خام رو باز میکنیم و از مسیر زیر روی From Folder کلیک میکنیم (شکل ۳)

Data/ Get & Transform Data/ Get Data/ From File/ From Folder

شکل ۳- انتخاب گزینه مورد نظر از قسمت پاورکوئری جهت ادغام داده ها

2. در پنجره باز شده، آدرس فولدری که فایل های مورد نظر در آن ذخیره هستند رو تعیین میکنیم و ok میزنیم.
3. پنجره ای باز میشه که اطلاعات فایل های موجود در اون فولدر رو نشون میده. از پنجره باز شده روی Combine & Load کلیک میکنیم.

شکل ۴- وارد کردن فایل های مورد نظر به پاورکوئری

1. در پنجره نمایش داده شده باید جدول مورد نظر که قرار است به عنوان Key معرفی بشه رو مشخص کنیم. همونطور که در شکل ۵ نمایش داده شده در نوار سمت چپ لیست Table های موجود مشخص شده و ما با کلیک کردن روی جدول مورد نظر، اون جدول رو به عنوان نمونه به پاورکوئری معرفی میکنیم و از این به بعد پاورکوئری در هر فایل دنبال جدولی با نام Evaluate میگرده. همونطور که در تصویر مشخصه، جدول نمونه (Key) از قسمت Sample File از روی اولین فایل (First File) در فولدر، نمایش داده شده. اگر جدول مورد نظر در فایل خاصی وجود داره، کافیه لیست کشویی رو باز کرده و فایل مورد نظر که جدول نمونه در اون قرار داره رو انتخاب کنیم.

شکل ۵ – انتخاب فایل و جدول نمونه (Key) جهت یادگیری نحوه ادغام اطلاعات

5. بعد از زدن OK به یک شیت اکسل منتقل میشویم که همه داده های موجود در ۳ فایل مورد نظر رو نمایش میده. که همونطور که در شکل ۶ نمایش داده شده، ستون اول دیتای تجمیع شده نام فایل مورد نظر قرار گرفته که نشون میده کدوم دیتا مربوط به کدوم فایل هست. پس میتونیم اسم فایل اکسل رو هوشمندانه انتخاب کنیم که بتونیم از اون در گزارش ها استفاده کنیم.

شکل ۶ – دیتای تجمیع شده در یک شیت

اگر نیاز به ویرایش ستون ها و ردیف های این داده ها وجود داشت، قبل از اینکه دیتا رو در اکسل بارگزاری (Load) کنیم باید Combine and Edit رو بزنیم.

چند نکته مهم:

• وقتی یک Table رو به عنوان نمونه (Key) معرفی میکنیم، پاورکوئری از اسم ستون های همون جدول (Table) برای تجمیع داده ها در بقیه فایل استفاده میکنه. اگر جداول در سایر فایل ها ستون های اضافه تری داشته باشه، پاورکوئری از اونها میگذره و اصلا در گزارش نهایی دیده نمیشن. اگر سایر فایل ها، ستون های موجود در Key رو نداشته باشن، پاورکوئری بجای اون قسمت ها، خطای NULL نمایش میده.
• نیازی نیست ستون ها در همه جداول به ترتیب باشند، پاورکوئری از نام ستون برای تطبیق داده ها استفاده میکنه.
• وقتی جدول با نام مثلا Evaluate رو به عنوان نمونه (Key) به معرفی کردیم، پاورکوئری در همه فایل ها به دنبال جدولی با همین نام میگرده و اگر در فایلی این جدول با این نام جود نداشته باشه، پاورکوئری خطا خواهد داد.

اضافه کردن فایل های جدید به فولدر

تا الان اومدیم و داده های موجود در ۳ فایل مشابه در یک فولدر رو با هم تجمیع کردیم و در یک فایل اکسل جمع آوری کردیم. اما هنوز کار داریم!

هنر اصلی پاورکوئری اینه که میتونه همه فرآیندی که یکبار انجام دادیم رو بارها و بارها و فقط با زدن یک دکمه اجرا کنه!

یعنی مثلا اگر در همین مثال فایل ارزیابی هفته چهارم بهمن هم برسه و بخوایم داده هاش رو به فایل تجمیع شده اضافه کنیم کافیه که فایل هفته چهارم رو (که شامل جدول Evaluate هست) رو به فولدر اضافه کنیم (شکل ۷)

شکل ۷- اضافه کردن فایل جدید به فولدر

سپس روی جدول اکسل خروجی پاور کوئری کلیک راست کرده و refresh رو بزنیم.(شکل ۸)

شکل ۸- بروزرسانی جدول داده های تجمیع شده

سناریو دوم: هر فایل، یک شیت مشخص با نام مشابه جهت ادغام

در این مثال، داده ها در قالب Table ذخیره نشده اند اما هر فایل شامل یک شیت با نام یکسان است که میخواهیم داده های موجود در اون شیت خاص در همه فایل ها رو تجمیع کنیم.

قبل از اینکه مراحل تجمیع کردن دیتا در این سناریو رو توضیح بدم، چند نکته رو باید توضیح بدم:

1. نام شیت مورد نظر باید عینا مشابه باشه. پاورکوئری میره توی هر فایل و شیت با نامه مشابه به چیزی که بهش معرفی کردیم رو پیدا میکنه و دیتاهاش رو میاره بیرون.
2. پاور کوئری به حروف بزرگ و کوچک انگلیسی حساسیت داره و اصطلاحا میگیم Case Sensitive هست. پس نام شیت مورد نظر اگر فارسی نیست، حتما این نکته رو باید رعایت کنیم. این موضوع در مورد نام ستون هم صادق هست.
3. مثل سناریو قبل، ترتیب ستون ها اهمیت نداره ولی عنوان ستون ها اهمیت داره که عینا مشابه باشه چون پاورکوئری با توجه به نام سرستون ها داده ها رو تطبیق میده و پیدا میکنه.

در این مثال ۴ فایل مشابه در مثال قبل رو داریم با این تفاوت که داده ها در قالب Table ذخیره نشده اند و همه فایل ها شامل شیتی به نام Data هستن و میخواهیم داده ها رو از این شیت ها تجمیع کنیم.

در این حالت هم مراحل عینا مشابه حالت قبل تکرار میشه و فقط زمانی که میخواهیم Key رو معرفی کنیم، روی شیت Data کلیک میکنیم که پاورکوئری بدونه باید در هر فایل بره سراغ شیتی به نام Data و داده های موجود در اون رو جمع آوری کنه.

حالا که روش مشابه هست و نکته ای خارج از مسائلی که گفتیم نداره، بیایم نتیجه رو بررسی کنیم و ببینیم آیا یاز به تغییر هست یا نه. وقتی فایل نهایی تجمیع شده رو باز میکنیم. متوجه میشیم که حدود ۱۲ ستون خالی اضافه تر و کلی ردیف های خالی در دیتابیس نهایی ایجاد شده. در واقع ما میدونیم که این ۴ فایل نهایتا ۳۰۰ ردیف داده خواهند داشت اما در فایل اکسل نهایی حدود ۳۲۶۰ ردیف ایجاد شده. (شکل ۹)

شکل ۹ – ردیف ها و ستون های خالی ایجاد شده در فایل تجمیع شده

اول ببینیم علت این قضیه چی هست و بعد بریم سراغ راه حل.

علت اینه که محدوده استفاده شده این فایل اکسل تا سلول Q815 هست. یعنی از ستون E تا Q و همه ردیف ها تا ردیف ۸۱۵ جزو محدوده استفاده شده یا اصطلاحا Used Range حساب شده. در حالیکه ما فقط ۴ ستون و نهایتا ۷۰ ردیف داده در هر فایل داریم. این موضوع رو از کجا متوجه میشیم؟

وقتی روی شیت قرار بگیریم و از تب Home و Go To/ Special گزینه Last Cell رو انتخاب کنیم، میره سراغ سلول Q815 که آخرین سلولیه که مورد استفاده اکسل در محاسباتش قرار گرفته. حالا چطور میتونیم این موضوع رو در پاورکوئری حل کنیم.

برای این کار، باید قبل از اینکه داده ها رو بارگذاری کنیم در شیت اکسل ویرایش رو انجام بدیم. پس بجای Combine & Load گزینه Combine & Transform Data رو میزنیم. و وارد محیطی مشابه شکل ۱۰ میشیم.

شکل ۱۰- محیط power query editor

در این قسمت باید ستون ها و ردیف هایی که نمیخواهیم رو حذف کنیم. برای این کار ستون های اضافی رو انتخاب میکنیم و از منوی کلیک راست، گزینه Remove Columns رو میزنیم. (در این مثال فقط ۴ ستون اول مورد نظر ماست که باید نگه داشته بشن، بقیه ستون ها تا ستون Q اضافی هستن و حذف باید بشن)

شکل ۱۱- حذف ستون های اضافی

حالا میخواهیم ردیف هایی که اضافی هستن رو حذف کنیم. همونطور که قبلا گفتیم اگر پاورکوئری دیتایی رو نتونه پیدا کنه، Null نمایش میده. پس کافیه ما Null ها رو از قسمت فیلتر داده ها حذف کنیم. مطابق شکل ۱۲ کافیه روی یکی از ستون ها زبانه فیلتر رو باز کنیم و تیک NULL رو برداریم. با این کار داریم به پاورکوئری میگیم که داده های Null رو در خروجی نهایی نشون نده.

شکل ۱۲- حذف ردیف های اضافی

حالا که کار ویرایش تمام شد، کافیه از تب Home گزینه Close & Load رو بزنیم. با این کار، همیشه مطمئنیم که ۴ ستون اول از هر شیت Data و ردیف هایی که پر هستن وارد خروجی نهایی داده های تجمیع شده میشن.

در اینجا هم مثل سناریو اول، اگر فایلی به فولدر اضافه بشه، کافیه Refresh انجام بشه تا همه کارهایی که انجام دادیم بصورت اتومات تکرار میشه و داده های فایل جدید (که شامل شیت Data ) هست رو به دیتابیس اصلی اضافه کنه.

سناریو سوم: هر فایل نه Table مشخصی داره و نه نام شیت یکسان!

گاهی اوقات فایل ها، نظم و شرایطی که گفتیم مثلا اسم شیت های مشابه یا نام جداول مشابه رو ندارن و ما میخوایم داده های مختلف در این شیت ها رو با هم ترکیب کنیم. همچنان میتونیم از پاورکوئری استفاده کنیم فقط باید چند مرحله اضافه تر رو هم انجام بدیم.

1. مراحل ۱ و ۲ رو مطابق حالت های قبل انجام میدیم و در مرحله ۳ بجای زدن Combine، روی گزبنه Transform Data کلیک میکنیم و وارد محیط Query Editor میشیم.
2. در این قسمت ستون اول و دوم (Content و Name) رو انتخاب کرده و از منوی کلیک راست، روی گزینه Remove Other Columns کلیک میکنیم.
3. حالا از تب Add Column روی گزینه Custom Column کلیک میکنیم و در پنجره نمایش داده شده، در قسمت Name نام دلخواه مشخص میکنیم و در قسمت Formula عبارت Workbook([Content]) رو تایپ میکنیم. (دقت داشته باشید حروف کوچک و بزرگ در پاورکوئری اهمیت داره و عینا همن عبارت باید تایپ بشه). حالا Ok رو میزنیم.

شکل ۱۳- اضافه کردن ستون ویژه برای ترکیب دیتابیس ها

4. بعد از زدن Ok میبینیم که یک ستون به نام Data Import اضافه شد و داخلش نوشته شده Table. حالا توضیح میدم که دقیقا چه اتفاقی داره میفته. ما از طریق ستون Name نام هر فایل رو به پاورکوئری معرفی کردیم، پاورکوئری هم، اشیا (آبجکت) داخل هر فایل (مثل شیت، Table و یا محدوده نامگذاری شده) رو پیدا میکنه که به نام Table در ستون مورد نظر نمایش داده میشه.حالا ما میتونیم روی فضای سفید کنار نام Table کلیک نیم ومحتوای هر ردیف رو در قسمت پایین ببینیم. میبینیم که اسم شیت و نام جدول موجود داخل فایل ۱۴۰۱-۱۱-(۱-۷) نمایش داده میشه. (شکل ۱۴)

شکل ۱۴- پیش نمایش داده های موجود در هر Table

5. حالا روی فلش دو طرفه نمایش داده شده روی ستون Import Data کلیک میکنیم و از لیست باز شده تیک گزینه Use original… رو بر میداریم و Ok رو میزنیم.

شکل ۱۵- نمایش اشیا موجود در هر فایل جهت تعیین نحوه نمایش سر ستون در اطلاعات ادغام شده

6. وقتی Ok میزنیم میبینیم که ستونهایی به دو ستون قبلی اضافه شد که برای هر فایل، اشیا موجود در اون فایل رو نشون میده. حالا ما جداول داخل هر فایل رو لازم داریم، پس روی ستون Kind باید Table ها رو فیلتر کنیم.

شکل ۱۶ – ستون Kind و نمایش لیستی از نوع اشیا موجود در هر فایل

7. روی زبانه فیلتر ستون kind کلیک میکنیم و از جدول باز شده، تیک همه رو برمیداریم و تیک table رو نگه میداریم که فقط ردیف های مربوط به جداول رو نمایش بده.

شکل ۱۷- انتخاب table از اشیا موجود در هر فایل جهت ادغام داده ها

8. حالا کافیه که روی ستون Name و Data کلیک کنیم و بقیه ستون رو با remove other columns حذف کنیم.
9. حالا کافیه روی زبانه گوشه ستون Data کلیک کنیم و محتوای دخل هر table رو فراخوانی کنیم. برای این کار هم مثل مرحله ۵ بعد از زدن زبانه، تیک گزینه Use Original… رو برمیداریم.
10. حالا داده های داخل جداول نمایش داده میشه (شکل ۱۸) و میتونیم دیتابیس ایجاد شده رو وارد اکسل کنیم. برای این کار باید از تب Home روی گزینه Close & Load کلیک کنیم که دیتای مورد نظر به شیت اکسل وارد بشه.

شکل ۱۸- ادغام داده های موجود در فایل های مختلف