ترکیب جداول در اکسل با استفاده از Power Query

ترکیب جداول در اکسل

ترکیب و یکپارچه سازی داده ها در اکسل یکی از کارهایی هست که خیلی مورد استفاده قرار میگیره. روش های مختلفی برای ادغام جدول در اکسل وجود داره. فرمول نویسی و استفاده از توابع جستجو مثل Vlookup و …، VBA، ابزارهایی مثل Consolidate، افزونه های آماده موجود برای این کار و … از روش های انجام این کار هستند. در این مقاله میخواهیم ترکیب داده ها و جداول رو با استفاده از پاور کوئری در اکسل انجام بدیم.

نحوه ارتباط جداول با استفاده از Power Query در اکسل

به زبان ساده، ابزار power query که در نسخه های ۲۰۱۶ و ۲۰۱۹ به عنوان Get & Transform شناخته میشه، ابزاری هست برای ترکیب و یکپارچه کردن، مرتب کردن و انتقال داده ها از منابع مختلف به یک فرمت دلخواه شما مثلا Table، Pivot Table یا Pivot Chart هست. این ابزار میتونه دو تا table رو با هم ترکیب کنه و یا اینکه داده ها رو از چندین تیبل و با توجه به ستون ها مشترکی که دارن، با هم ادغام کنه. قبل از اینکه شروع کنیم این چند نکته رو باید بدونیم:

• پاورکوئری یکی از ابزارهای اکسل هست که از ورژن ۲۰۱۶ به بعد، موجود هست و نیازی نیست کاری انجام بشه.اما برای ورژن های ۲۰۱۰ و ۲۰۱۳ میتونه به عنوان Add ins به اکسل اضافه بشه.
• جداولی که قراره ترکیب بشن، باید حداقل یک ستون مشترک داشته باشن که به عنوان کلید، مشخصه و … شناخته میشه. همچنین این ستون ها باید یکتا و بدون تکرار باشه.
• جدول میتونه داخل شیت فعلی یا هر شیت دیگه ای باشه.
• برخلاف فرمول نویس، پاور کوئری نمیاد داده ها رو از یک تیبل بچینه کنار تیبل بعدی. بلکه یک جدول جدید ایجاد میکنه که داده های دو جدول رو با هم ترکیب کرده.
• جدول نهایی، بصورت خودکار آپدیت نمیشه و باید بهش بگیم که کی اپدیت کنه.

منابع داده

به عنوان مثال میخواهیم سه جدول زیر رو با استفاده از ستون شماره سفارش و فروشنده با هم ادغام کنیم. دقت داشته باشید که جداول ما تعداد ردیف های مختلفی دارن و در جدول ۱ که اسامی فروشنده تکرار شده، ولی در جدول ۳ بصورت یکتا و بدون تکرار قرار گرفته. ما میخوایم داده های موجود در جدول ۱ رو با داده های مرتبط در دو جدول دیگه (نشان داده شده در شکل ۱) تجمیع کنیم و همه داده ها رو در یک جدول داشته باشیم.

شکل ۱- جداول مجزا که میخواهیم با هم ادغام کنیم

قبل از اینکه شروع کنیم، خیلی خیلی بهتره که اسم معناداری به تیبل ها اختصاص بدیم. این کار خیلی کمک میکنه که راحت تر تیبل ها رو انتخاب کنیم. اسم تیبل ها رو به شرح زیر تغییر میدیم. دقت داشته باشید که اسم ها میتونه فارسی هم باشه ولی ما پیشنهاد میکنیم که برای اینکار از حروف انگلیسی استفاده کنیم. قواعد نامگذاری محدوده ها رو از مقاله نامگذاری محدوده مطالعه کنید. Table1 رو به Orders، Table2 رو به Products، Table3 رو به Commissions تغییر میدیم.

ایجاد ارتباط های Power Query

قرار نیست جداول رو توی فایلمون کپی کنیم و فایل و شلوغ کنیم. میخوایم جداول رو در Power Query Editor اضافه کنیم و ارتباطات رو ایجاد کنیم و در نهایت نتیجه رو در قالب یک جدول ایجاد کنیم. برای این کار مراحل زیر رو انجام میدیم:

1. اولین جدول (Orders) رو انتخاب میکنیم یا روی یکی از سلول های این جدول کلیک میکنیم.
2. از مسیر زیر گزینه Table/Range From رو انتخاب میکنیم:

Data > Get & Transform

3. در پنجره Power Query Editor روی زبانه Close & Load کلیک میکنیم و گزینه Close and load to

شکل ۲- بستن پنجره Query Editor

4. با بستن این پنجره، یک فرم باز میشه که از ما میپرسه که میخوایم داده ها رو داخل ورک بوک ببینیم یا فقط ارتباط بینشون برقرار کنیم. گزینه create connection Only رو انتخاب کرده و OK میزنیم.

شکل ۳- ایجاد ارتباط بین جداول

بعد از زدن گزینه OK یک ارتباط با نام جدول مورد نظر ایجاد میشه و در نوار سمت راست که با نام Workbook Queries هست نمایش داده میشه.

5. این کار رو برای دو جدول دیگه هم انجام میدیم و بعد از طی این مراحل، نوار Workbook Queries رو بصورت شکل ۴ خواهیم داشت.

شکل ۴- نمایش سه جدول اضافه شده به پنجره Query

ترکیب دو جدول به یک جدول

حالا که ارتباطات رو ایجا کردیم. ببینیم چطور میشه دو جدول رو با هم ادغام کرد.

1. از تب Data و قسمت Get & Transform روی دکمه Get data کلیک میکنیم و از قسمت Combine Queries گزینه Merge رو انتخاب میکنیم:

شکل ۵- ادغام دو جدول در یک جدول

2. در پنجره باز شده، مراحل زیر رو انجام میدیم:
   • اولین جدول یعنی Orders رو از اولین لیست فروریز انتخاب میکنیم.
   • جدول دوم یعنی Products رو از فروریز دوم انتخاب میکنیم.
   • در هر دو پیش نمایش، روی ستون مشترک کلیک میکنیم تا هایلایت بشن.
   • در قسمت Join kind (در صورت وجود) لیست فروریز رو باز کرده و گزینه پیشفرض left outer(all from first, matching from second) رو انتخاب میکنیم.
   • Ok رو میزنیم.

شکل ۶- انتخاب جداول و ستون های مشترک بین آنها

بعد از انجام این مراحل، Power query Editor اولین جدول یعنی (Orders) رو با یک ستون اضافی نمایش میده. این ستون اضافی هنوز داده ای نداره که در ادامه این مسئله رو حل میکنیم.

انتخاب ستون از جدول دوم برای ادغام شدن با جدول اول

در این مرحله یک جدول داریم که از دو جدول تشکیل شده. برای تکمیل فرآیند ادغام، در Power Query Editor اقدامات زیر رو انجام میدیم:

1. در ستون اضافه شده، روی علامت (فلش های دو جهت) کلیک میکنیم.

شکل ۷- جداول ترکیب شده

2. در پنجره نمایش داده شده، مراحل زیر رو انجام میدیم:

• • گزینه Expand رو انتخاب میکنیم.
  • تیک ستون هایی که نمیخواهیم از جدول دوم انتقال داده بشه رو بر میداریم. در این مثال فقط تیک محصول رو میذاریم بمونه.
  • تیک گزینه use original column name as prefix رو برمیداریم. (اگر برنداریم، نام ستون قبل از نام جدول ظاهر میشه)

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

بعد از زدن OK جدولی رو مشاهده میکنیم که همه اطلاعات جدول اول رو داره، باضافه ستون هایی که از جدول دوم تعیین کردیم. اگر قرار باشه فقط دو جدول رو ادغام کنیم، کار اینجا به پایان میرسه اما اگه بخوایم جداول بیشتری رو با هم ترکیب کنیم مطابق با بقیه این آموزش عمل میکنیم:

ادغام بیش از دو جدول

اگر بیش از دو جدول رو بخوایم ادغام کنیم، باید چند کار دیگه انجام بدیم که در ادامه شرح میدهیم:

1. جدولی که از ترکیب دو جدول (در بالا) بدست آوردیم رو ذخیره میکنیم:
   • در Power query editor روی گزینه Close & Load کلیک میکنیم و گزینه Close and load to رو انتخاب میکنیم.
   • در پنجره import data گزینه only create connection رو انتخاب کرده و OK میزنیم.

این کار یک connection جدید به نام Merge 1 میسازه. که میتونیم با کلیک راست و Rename اسم جدول رو عوض کنیم. مطابق شکل ۹

شکل ۹- اضافه شدن جدول ادغام شده از دو جدول اولیه

2. مراحل Merge کردن رو همونطور که در بالا شرح داده شد، اجرا میکنیم و این بار جدول اول، جدول <erge1 و جدول دوم جدول Commission هست.

شکل ۱۰- اضافه کردن جدول سوم به جدول ترکیبی (جدول یک و دو)

3. حالا کافیه که ستون های مد نظر از جدول سوم رو تعیین کنیم و Expand کنیم. که در این مثال، فقط ستون درصد کمیسیون رو اضافه میکنیم.

شکل ۱۱- جدول ادغام شده نهایی از سه جدول شماره ۱، ۲ و ۳

اضافه کردن جدول ادغام شده به اکسل

برای اینگه جدول ادغام شده رو داخل اکسل مشاهده کنیم، کافیه اول گزینه Close and load to رو بزنیم و این بار از پنجره باز شده گزینه Table و New Worksheet رو انتخاب کنیم و OK رو بزنیم.

شکل ۱۲-نمایش جدول ادغام شده در قالب یک جدول جدید

با زدن Load جدول جدید که شامل ستونهای ترکیبی از سه جدول اولیه هست نمایش داده میشه و حالا هر گزارش دیگه ای بخوایم روی این دول میتونیم پیاده کنیم. فقط در حین این فرآیند، فرمت های عددی (Number Formats) تعیین شده انتقال داده نمیشن که باید مجددا اعمال بشه. در این مثال ستون تاریخ رو به فرمت شمسی و ستون کمیسیون رو به % تغییر میدیم و جدول نهایی بصورت شکل ۱۳ نمایش داده خواهد شد.

شکل ۱۳- جدول ادغام شده نهایی (ترکیبی از جدول ۱، ۲ و ۳  در شکل ۱)

بروزرسانی جدول ادغام شده

یکی از ویژگی های خوب این ابزار این هست که به محض اعمال تغییرات روی داده های مرجع، نیازی نیست که این مراحل دوباره طی بشه. بلکه فقط کافیه که جدول ادغام شده بروزرسانی بشه. برای این کار هم میتونیم روی جدول ادغام شده در پنجره Workbook Query کلیک کنیم و دکمه refresh رو بزنیم. (شکل ۱۴)

شکل ۱۴- بروز رسانی جدول ترکیبی، از قسمت Show Queries

یا اینکه از تب Query روی گزینه Refresh کلیک میکنیم. مطابق شکل ۱۵

شکل ۱۵- اعمال تغییرات انجام شده در داده های مرجع از تب Query

دانلود فایل این آموزش

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

ثبت ایمیل جهت دانلود فایل

مطمئن باشید ایمیل شما فقط در اختیار اکسل پدیا قرار خواهد گرفت و صرفا برای ارسال مطالب مرتبط استفاده خواهد شد.

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...