یکپارچه کردن داده ها در اکسل

ابزار Consolidate در اکسل

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

شکل ۱- داده های مربوط به فروش هر فصل به تفکیک ماه

برای این کار مراحل زیر رو انجام میدیم:گام اول: داده ها رو مرتب میکنیم. قبل از استفاده از ابزار Consolidate در اکسل باید از موارد زیر اطمینان حاصل کنیم:

• داده ای که میخوایم یکپارچه کنیم باید روی شیت جداگانه ای باشه. نباید روی شیتی که یکپارچه سازی رو پیاده می کنیم، هیچگونه داده دیگه ای وجود داشته باشه.
• همه شیت ها ساختار یکسان داشته باشن و عنوان های ستون ها/ردیفها (در صورت وجود) در شیت ها مشابه باشه.

 

• هیچ ردیف یا ستون خالی (بین محدوده انتخابی) وجود نداشته باشه.

گام دوم: در شیت نهایی (شیتی که قراره داده ها رو تجمیع کنه)، روی سلولی که میخوایم جدول یکپارچه سازی پیاده بشه کلیک میکنیم، از سربرگ Data و بخش Data Tools گزینه Consolidate رو انتخاب میکنیم.

شکل ۲- انتخاب گزینه Consolidate

 گام سوم: در پنجره باز شده، تنظیمات زیر رو اعمال میکنیم:

• در قسمت Function تابعی که میخوایم برای یکپارچه سازی انجام بشه رو انتخاب میکنیم (مثل: Count , Sum , Average , Min , Max و …). چون میخوایم جمع فروش رو حساب کنیم، در اینجا تابع Sum رو انتخاب میکنیم.
• در قسمت Reference روی آیکون Collapse Dialog کلیک میکنیم و محدوده مورد نظر رو از شیت اول انتخاب میکنیم. سپس روی دکمه Add‌ کلیک میکنیم تا محدوده به قسمت All References اضافه بشه. بعد روی شیت بعدی کلیک میکنیم و محدوده مورد نظر رو انتخاب کرده و Add میزنیم. این کار رو برای داده های دیگه در بقیه شیت ها هم انجام میدیم.

اگر بعضی از شیت ها در فایل یا ورک‌بوک های دیگه ای قرار دارند، با گزینه Browse میتونیم اونهارو هم اضافه کنیم.

شکل3- انتخاب داده ها برای یکپارچه سازی توسط ابزار Consolidate در اکسل

 گام چهارم:  در همون پنجره شکل ۳، میتونیم هر کدوم از گزینه های Top Row/Left Column رو انتخاب کنیم. اگر بخوایم اسم ستون و/یا ردیف داده های اولیه در شیت نهایی هم نمایش داده بشه، گزینه Top Row و/یا Left column رو انتخاب میکنیم. (در این مثال، منظور ما از این کلمات، عبارات کیک، بستنی و … و فروش مهر و …)با این کار، عنوان ردیف و ستون اول برای اکسل شناخته شده است و حتی اگر این عنوان ها جابجا باشند، به درستی تشخیص داده میشن. مثلا اگر کلمه کیک در یک شیت، ردیف اول باشه و در شیت بعدی ردیف دوم، این ابزار، این جابجایی رو تشخیص میده و عملیات جمع (تابع انتخابی) روی داده های مرتبط انجام میشه.بعد از زدن OK، مطابق شکل ۴ جمع فروش در همه ماه ها و به تفکیک محصولات در شیت انتخابی، نمایش داده میشه.

شکل ۴- داده های یکپارچه شده توسط Consolidate در اکسل

اگر بخوایم کاری کنیم که داده ها به منبع اصلی متصل باشن یعنی وقتی داده ها در شیت های مرجع رو تغییر میدیم، بطور خودکار داده های یکپارچه سازی شده هم بروزرسانی بشن، میتونیم گزینه Create links to source data رو که در شکل ۳ نشان داده شده است انتخاب کنیم. با این کار، مطابق شکل ۵ اکسل یک لینک به شیت اولیه درست میکنه و تابع انتخابی (مثلا Sum) رو وارد سلول میکنه. (در حالت قبلی فقط نتیجه تابع انتخابی در شیت تجمیع شده نمایش داده میشد، با این کار، تابع مورد نظر نمایش داده میشه).

شکل ۵ – یکپارچه کردن داده ها با ابزار Consolidate در اکسل

همچنین ریز داده ها رو بصورت یک گروه نمایش میده. بصورتی که اگر زبانه سرگروه (علامت +) رو بزنیم، گروه باز شده و زیرگروه آن که همون داده های مرجع در شیت های مربوطه هستن نمایش داده میشه. (شکل ۶)

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

 همونطور که دیدیم، ابزار Consolidating در تجمیع و یکپارچه کردن داده ها از چندین شیت میتونه خیلی کاربردی باشه. اما این ابزار محدودیت هایی هم داره. مثلا اینکه فقط با داده های عددی کار میکنه و متن ها رو نمیتونه تجمیع کنه. همینطور تعداد توابعی که میتونیم برای انجام محاسبات استفاده کنیم، محدود به همون تعداد توابع موجود در Function هست.اما گاهی اوقات شرایط و خواسته ما به گونه ای است که نمیشه از این ابزار استفاده کرد. در این رابطه یک افزونه (Add Ins) به نام RDB Merge وجود داره که تا حد خیلی خوبی، کار یکپارچه سازی رو بصورت پیشرفته تر انجام میده. اگر این افزونه هم نیاز ما رو برطرف نکنه، خودمون باید اقدام به کدنویسی کنیم و خواسته خودمون رو به زبان VBA تبدیل کنیم.همچنین یک راه دیگه برای انجام این کار استفاده از Power Query هست که پیشنهاد میکنیم مقاله ترکیب جداول در اکسل با استفاده از Power Query هم بخونید.

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

در ادامه آموزش ابزار Consolidate در اکسل ، فایل نمونه جهت تمرین بیشتر و افزونه RDB Merge برای دانلود قرار داده شده است.