آموزش تصویری و قدم به قدم ابزار Consolidate در اکسل- یکپارچه سازی داده ها

ابزار Consolidate در اکسل چیست؟

از ابزار consolidate در اکسل برای خلاصه کردن و گزارش نتایج از کاربرگ‌های جداگانه استفاده می کنیم، شما می‌توانید داده‌ها را از همه شیت ها در یک کاربرگ اصلی ادغام کنید. برگه‌ها یا شیت ها می‌توانند در همان فایل یا در فایل های دیگر دیگر باشند. شما این داده ها را تجمیع می کنید تا بتوانید برای گزارشات خود از آنها استفاده کنید. کاربرد اصلی ابزار consolidate تجمیع همین اطلاعات است.

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

کاربرد ابزار Consolidate در اکسل چیست؟

همان طور که گفتیم از این ابزار برای تجمیع اطلاعات چند شیت استفاده می شود یعنی اگر شما داده های هر ماه را در یک شیت وارد کردید و حالا می خواهید که جمع تمام ماه ها را در یک شیت دیگر داشته باشید از این ابزار استفاده میکنید.

چرا وقتی تابع Sum در اکسل را داریم باید از این ابزار استفاده کنیم؟

طبق مطالب قبلی آموزش اکسل ، استفاده از تابع Sum خیلی راحت تر است و میتوانید چند شیت را با هم تجمیع کنید. مثلا اگر نام شیت های شما از A تا H هست. با فرمول زیر می توانید جمع تمام شیت ها را داشته باشید

Sum(A:H!B:B)=

مشکل Sum برای تجمیع چیست؟

مشکل sum در اکسل اینجاست که ترتیب ستون ها ممکن است در همه شیت ها یکسان نباشد و آن وقت دیگر تابع Sum کارساز نبوده و نمیتوان برای این مورد از آن استفاده کرد.

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

پس برای یکسان سازی داده ها در اکسل باید چه کرد؟

ابزار Consolidate برای یکسان سازی و یکپارچه کردن اطلاعات در اکسل طراحی شده است. حالا اطلاعات ممکن است مرتب باشند یا نامرتب
این ابزار میتواند بدون اینکه ترتیب ستون ها در شیت های مختلف یکی باشند آنها را از روی لیبل ستون و یا نام ستون یافته و با هم تجمیع کند. از مسیر زیر، میتوانیم به این ابزار دسترسی داشته باشیم.

بعد از کلیک روی Consolidate فرم این ابزار باز میشود.

در قسمت Reference هر بار یکی از شیت ها و محدوده داده ها را انتخاب میکنیم و دکمه Add را میزنیم.

گزینه های پایین فرم Consolidate برای چیست؟

اگر هیچ کدام از گزینه ها را نزنید مثل همان Sum عمل می کند و برای شیت هایی مناسب است که ترتیب سطر و ستون هایشان مثل هم هستند.

Top row:

اگر این تیک را بزنید لیبل ها یا سر ستون ها را جست و جو کرده، تطبیق میدهد و در نهایت مجموع آنها را محاسبه میکند

Right column:

اگر این تیک را هم بزنید هر محصول را جست و جو میکند و در هر ردیفی که محصول را پیدا کند، مقدار آن را لحاظ میکند

Create links to source data:

این گزینه ریز جدول تمام شیت ها را به شیت گزارش منتقل میکند و زیر هر ستون جمع جدول نمایش داده می شود، همچنین به داده های اصلی لینک داده و با تغییر داده ها، نتایج بلافاصله تغییر میکند.

می بینید با اینکه ترتیب ستون ها یکی نبود ولی همه داده ها را پیدا کرده و جمع گرفت. این حالت Offline هست و با تغییر داده شیت، این اعداد تغییری نمیکنند و با هر تغییر باید یک بار گزارش را رفرش کنید.

گزارش Consolidate در اکسل به صورت آنلاین یا داینامیک به چه صورت است؟

همان طور که در بالا دیدید گزارش، در حالت عادی آفلاین بوده و برای اینکه داینامیک باشد باید تیک Create links to source data را بزنید.

در این حالت اگر تغییری در اعداد شیت ها اعمال شود، اینجا هم تغییر می کند و به صورت Online میباشد.