سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

ترکیب جداول در اکسل با استفاده از 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

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

بستن پنجره Query Editor

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

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

ایجاد ارتباط بین جداول

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

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

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

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

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

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

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

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

ادغام دو جدول در یک جدول

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

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

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

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

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

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

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

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

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

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

  1. در پنجره نمایش داده شده، مراحل زیر رو انجام میدیم:
    • گزینه 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 اسم جدول رو عوض کنیم. مطابق شکل ۹

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

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

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

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

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

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

جدول ادغام شده نهایی از سه جدول شماره 1، 2 و 3

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

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

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

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

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

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

در این مثال ستون تاریخ رو به فرمت شمسی و ستون کمیسیون رو به % تغییر میدیم و جدول نهایی بصورت شکل ۱۳ نمایش داده خواهد شد.

جدول ادغام شده نهایی (ترکیبی از جدول 1، 2 و 3 در شکل 1)

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

نکته:
اگر ارتباط جداول رو بخوایم با دو ستون انجام بدیم، کافیه که ستون ها رو با Ctrl انتخاب کنیم. بقیه مراحل عینا مشابه مطالب شرح داده شده می باشد.

 

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

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

برای این کار هم میتونیم روی جدول ادغام شده در پنجره Workbook Query کلیک کنیم و دکمه refresh رو بزنیم. (شکل ۱۴)

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

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

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

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

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

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

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

آواتار
144

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • مری ۱۵ مرداد ۱۴۰۲ / ۵:۵۰ ب٫ظ

    سلام وقتتون بخیر. ممنون از آموزش های عالیتون. من همه مراحل رو انجام دادم فقط وقتی که جدول کردم تاریخ ها رو به صورت مربع های بی معنی نشون میده به این شکل (#############) . ممنون میشم راهنماییم کنید چطوری اینو درست کنم؟

    • آواتار
      حسنا خاکزاد ۱۷ مرداد ۱۴۰۲ / ۵:۱۲ ب٫ظ

      درود
      این ### نشان دهنده کمبود جا هست پهنای ستون زیاد بشه درست میشه

  • محسن ۳ تیر ۱۴۰۲ / ۸:۳۳ ب٫ظ

    سلام
    ممنون بابت آموزش هاتون
    من میخام vbaرا حرفه ای یادبگیرم لطف می کنید راهنمایی کنید

    • آواتار
      حسنا خاکزاد ۳ تیر ۱۴۰۲ / ۸:۴۷ ب٫ظ

      درود بر شما
      این دوره رو بررسی کنید
      از صفر آموزش داده شده و پشتیبانی از طریق سایت هم برقراره
      https://excelpedia.net/product/vba/

  • علی سوباشی ۱۷ فروردین ۱۴۰۲ / ۱۲:۰۴ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۹ فروردین ۱۴۰۲ / ۱۰:۴۸ ق٫ظ

      درود بر شما
      منظروتون از فالی های خالی یعنی ریف ها و ستون های خالی باید باشه احتمالا چون یک رسید داخل یک فایل باید باشه اینطور که من متوجه شدم
      در قسمت ادیتور، زبانه فیلتر رو باز کنید و تیک Blank رو بردارید که خالی ها رو منتقل نکنه

  • سید محسن ۹ دی ۱۴۰۱ / ۸:۴۲ ق٫ظ

    خیلی عالی بود
    بهترین راه برای ادغام دو فایل بود

  • امیر ۱۲ آذر ۱۴۰۱ / ۵:۰۱ ب٫ظ

    سلام وقتتون بخیر
    من چندین پروژه دارم که با کد یکتا شده ان،هر پروژه از چندین کالا تشکیل شده،میخوتام یه جدول درست کنم که کد پروژه رو وارد کنم و کالا هارو بازخوانی کنه برام از اکسلی که همه این دیتا ها روش هست.
    با MATCH INDEX فقط اولین رکورد رو فراخوانی میکنه،ولی هر پروژه حداقل ۵ رکورد داره که میخوام همشو توی یه TABLE برام بیاره
    میتونید راهنماییم کنید؟

    • آواتار
      حسنا خاکزاد ۲۴ دی ۱۴۰۱ / ۴:۳۶ ب٫ظ

      درود بر شما
      از جستجوی موارد تکراری استفاده کنید
      https://excelpedia.net/search-duplicates/
      اگر ورژن های بالاتر دارید، از filter استفاده کنید

  • نازی فتحی ۱۵ شهریور ۱۴۰۱ / ۱۰:۴۳ ق٫ظ

    نحوه ادغام دو فایل در اکسل ؟

  • fereshte adel inanloo ۱۰ تیر ۱۴۰۱ / ۱:۵۱ ب٫ظ

    با سلام
    ممنون از سایت بسیار خوبتون
    ما هر روز یک سری اطلاعات روزانه از فروش در قالب جدول اکسل داریم،بعد برای سر ماه میخاهیم این جدولها اطلاع داخلی شون با هم محاصبه شه و در قالب یک جدول نشان بده
    راهی هست که مثلا ۳۰ تا جدولو باهم فراخانی کنیم و محاصبات روش انجام بده و نتیجه نمایش داده شه در یک جدول
    ممنون اگه بتونید کمکتم کنید

    • آواتار
      حسنا خاکزاد ۱۰ تیر ۱۴۰۱ / ۱۱:۳۳ ب٫ظ

      درود بر شما
      بله دقیقا با پاورکوئری به راحتی میتونید انجام بدید

      اگر ساختارها عین همه، از دستور append استفاده کنید بجای merge

  • سید جواد موسوی ۲۱ آذر ۱۴۰۰ / ۵:۳۱ ب٫ظ

    سلام روز بخیر
    دو تا جدول داریم با شرح یکسان و هر جدول چهارستون و یک جدول با ۱۲۰ سطر و جدول دیگه با ۹۰ سطر بطوریکه این ۹۰ سطر ستون شرحش در ۱۲۰ تا موجود هست با چه تابعی اعداد متناظرش را در جلوی این جدول قرار دهیم
    با تشکر

    • آواتار
      حسنا خاکزاد ۲۱ آذر ۱۴۰۰ / ۷:۲۷ ب٫ظ

      درود
      اگه تکراری نداره با VLOOKUP هم شدنی هست
      اگر نه ،توضیح بیشتر باید بدید

  • حسینی ۲۷ مرداد ۱۴۰۰ / ۵:۵۷ ب٫ظ

    شما عالی هستید

  • توکل ۲۰ فروردین ۱۳۹۹ / ۶:۴۵ ب٫ظ

    بسیار عالی بود
    ممنون

  • مهین انصاری ۲۱ دی ۱۳۹۸ / ۱۱:۳۲ ق٫ظ

    ممنون از مطالبتون ..مفید بود

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان