پنجشنبه, 13 اکتبر 2022

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

واژه Consolidate به معنای یکی کردن می‌باشد. در اکسل از این ابزار برای یکی کردن و ترکیب داده های همسان(هم‌ شکل و هم ساختار) استفاده می‌کنیم . با استفاده از این ابزار می‌توانید برگه‌های خود را در یک فایل اکسل و حتی برگه‌های موجود در چند فایل اکسل را یکی کنید.

هرگاه در چند شیت یا کاربرگ جداولی با فیلدهای مشابه داشته باشیم می توانیم از تمامی آنها در یک کاربرگ مجزا گزارش تهیه کنیم. درنظر داشته باشید این ابزار تنها در مواردی کاربرد دارد که چندین جدول یکسان قرار است در یک جدول خلاصه شوند!

برای درک بهتر، جداول فروش زیر را در نظر بگیرید که هر یک در شیت (کاربرگ) جداگانه قرار دارند:

در این جداول، تعداد فروش ماهانه اجناس در کاربرگ‌های جداگانه ثبت شده‌اند. همانطور که مشاهده می‌کنید برگه‌ها یکسان نیستند. با این حال، با استفاده از ابزار Consolidate می‌توان به سادگی عملیات جمع (Sum) ، میانگین (Average) و … را با توجه به سرتیترهای موجود در هر جدول و کاربرگ انجام داد.

با توجه به این مثال قصد داریم گزارشی از فروش سالیانه ارایه بدهیم. برای این کار در ابتدا یک کاربرگ جدید و در صورت نیاز یک یک کارپوشه(Workbook) جدید ایجاد می‌کنیم:

سپس از تب Data و گروه Data tools ابزار Consolidate رو انتخاب میکنیم:

با انتخاب گزینه Consolidate پنجره Consolidate ظاهر می‌شود:

در این پنجره سه بخش Function (توابع)، Reference (مرجع) ، All references (تمام مراجع) وجود دارد.

در بخش منوی function ، میتوان تابع مورد نظر را انتخاب کرد:

توابع این بخش به همراه آموزش هریک به ترتیب زیر است:

تابع Sum: محاسبه جمع اعداد.
تابع Count: محاسبه شمارش.
تابع Average: محاسبه میانگین اعداد.
تابع Max: استخراج بزرگترین عدد.
تابع Min: استخراج کوچکترین عدد.
تابع Product: محاسبه ضرب اعداد.
تابع Count Numbers: شمارش اعداد.
تابع Stddev: محاسبه انحراف معیار نمونه.
تابع Stddevp: محاسبه انحراف معیار جامعه.
تابع Var: محاسبه واریانس نمونه.
تابع Varp: محاسبه واریانس جامعه.

در این مثال برای گزارش گیری سالانه از تابع Sum استفاده خواهیم نمود. از این رو برای اضافه کردن و یکپارچه کردن داده‌های هر جدول، بر روی دکمه فلش از قسمت Reference کلیک نمایید:

پس از کلیک پنجره انتخاب Rerefence همانند تصویر زیر ظاهر می‌شود:

حال برروی کاربرگ فروش بهار کلیک کرده و سلول‌های مورد نظر را انتخاب کنید:

با فشردن کلید Enter از صفحه‌کلید،مجدد به پنجره ابزار consolidate برمی‌گردیم:

حال با انتخاب دکمه Add مرجع مورد نظر را اضافه می‌کنیم.مشاهده می‌کنید که محدوده انتخابی در قسمت All References اضافه می‌شود:

برای این مثال ابتدا محدوده‌ی جدول در شیت فصل بهار را انتخاب و Add کرده و سپس محدوده جدول در شیت تابستان را انتخاب و Add کردیم و به همین ترتیب چهار جدول مربوط به فصول را از چهار شیت متفاوت انتخاب و Add نمایید.

نتیجه اضافه نمودن هر جدول به شرح تصویر زیر است:

برای حذف محدوده‌ها نیز در بخش All references ، روی محدوده مورد نظر کلیک کرده و گزینه Delete را انتخاب می‌کنیم:

در ادامه گزینه هایTop Row، Right Column و Create links to source data را تیک بزنید:

اگر گزینه های Top row و Left column را تیک نزنید، اکسل مجموع تمامی سلولهایی را که موقعیت یکسانی با یکدیگر دارند محاسبه خواهد کرد . از آنجا که برگه های ما یکسان نیستند، ما از اکسل می خواهیم که سلولهایی را که برچسب (Label) یکسان دارند برای ما جمع بزند. اگر شما گزینه Create links to source data را تیک بزنید، اکسل یک پیوند به داده های اصلی شما می سازد و در نتیجه اگر داده های اصلی شما در فایل های اکسل اصلی تغییر کنند، داده‌های یکی شده (consolidated data) شما بروز رسانی خواهند شد.

گزینه Top Row
- اگر این تیک را بزنید لیبل ها یا سر ستون ها را جست و جو کرده، تطبیق می‌دهد و در نهایت مجموع آنها را محاسبه می‌کند.
گزینه Right Column
- اگر این تیک را هم بزنید هر محصول را جست و جو می‌کند و در هر ردیفی که محصول را پیدا کند، مقدار آن را لحاظ می‌کند.
گزینه Create links to source data
- این گزینه ریز جدول تمام شیت ها را به شیت گزارش منتقل می‌کند و زیر هر ستون جمع جدول نمایش داده می‌شود، همچنین به داده‌های اصلی لینک داده و با تغییر داده‌ها، نتایج بلافاصله تغییر می‌کند.