داده های در اکسل ممکن است به صورت های متفاوتی وجود داشته باشند. ابزار Consolidate در اکسل برای یکپارچه کردن یک سری جداول همسان مورد استفاده قرار میگیرد. تصور کنید شما یک فایل اکسل دارید که دارای چهار شیت میباشد و در هر شیت گزارش فروش کالاهای یک شرکت در هر فصل قرار داده شده است. ابزار Consolidate در اکسل به کاربران این امکان را میدهد که بتوان خلاصه ای از گزارشات فصلی را در یک جدول مشاهد کرد. در ادامه ی این مقاله با این ابزار آشنا میشویم.
ابزار Consolidate در اکسل تنها در مواردی کاربرد دارد که چندین جدول یکسان قرار است در یک جدول خلاصه شوند. در ادامه کار با این ابزار توسط یک مثال آموزش داده میشود.
معرفی ابزار Consolidate در اکسل
ابزار Consolidate در اکسل در تب Data و در گروه Data Tools قرار گرفته است. شکل زیر آیکون Consolidate را نشان میدهد.
شکل زیر خلاصه ای از مبحث بررسی ابزار Consolidate در اکسل را نشان میدهد:
کار با ابزار Consolidate در اکسل
کار با ابزار Consolidate در اکسل را همراه یک مثال یاد میگیریم، به جدول زیر توجه کنید. در این جدول (ستون اول نام کالا، ستون دوم تعداد فروخته شده و ستون های سوم و چهارم، پنجم و ششم به ترتیب قیمت واحد، قیمت کل، درصد تخفیف و مبلغ دریافتی میباشد) چند قلم کالای فروخته شده از یک شرکت لیست شده است. در فایل اکسل نمونه، پنج شیت وجود دارد، چهار شیت هریک دارای یک جدول مانند جدول زیر میباشند که مربوط به آمار فروش در یک فصل هستند و در شیت پنجم میخواهیم با استفاده از ابزار Consolidate در اکسل خلاصه ای از آمار فروش سالیانه شرکت را استخراج نماییم.
برای دانلود فایل نمونه Consolidate در اکسل بر روی این خط کلیک نمایید.
برای تهیه گزارش فروش سالیانه شرکت بالا توسط ابزار Consolidate در اکسل، ابتدا یک شیت خالی ایجاد میکنیم و یک سلول را انتخاب مینماییم، سپس بر روی ابزار Consolidate کلیک میکنیم. پنجره Consolidate در اکسل در زیر نشان داده شده است، در این پنجره و در منوی Function تصمیم میگیریم که چه نوع عملیاتی باید بر روی داده های عددی انجام گیرد، میتوان هر یک از عملیات های SUM، COUNT، AVERAGE، MAX، MIN، PRODUCT، COUNT NUMBERS، STDDEV، STDDEVP، VAR، VARP را بر روی داده های عددی انجام داد.
برای آشنایی با توابع فوق لطفاً مقالات “توابع ریاضی در اکسل” و “توابع آماری در اکسل” را مطالعه نمایید.
برای اضافه کردن جداول گزارشات فصلی، بر روی دکمه Reference در قسمت Reference کلیک نمایید و محدوده مورد نظر را انتخاب کنید، پس از انتخاب محدوده ی مورد نظر بر روی دکمه Add کلیک کنید تا آن محدوده در قسمت All References اضافه گردد. (برای این مثال ابتدا محدوده ی جدول در شیت یک را انتخاب کنید، آن را Add نمایید و سپس محدوده ی جدول در شیت دو را انتخاب و Add کنید و به همین ترتیب چهار جدول مربوط به فصول را از چهار شیت متفاوت انتخاب و Add نمایید.)
برای دانلود فایل نمونه Consolidate در اکسل بر روی این خط کلیک نمایید.
- برای حذف یک محدوده از قسمت All References آن محدوده را انتخاب و بر روی دکمه ی Delete کلیک نمایید.
در شکل بالا، چهار محدوده ار فایل اکسل نمونه انتخاب شده اند و عملیات SUM قرار است بر روی آنها صورت گیرد. اگر جداول انتخاب شده، مانند جداول این نمونه، دارای عنوان سطر (در ستون سمت چپ) وعنوان ستون (در سطر اول) باشند گزینه های Top Row و Left Column را تیک میزنیم.
اگر گزینه ی Create Link to Source Data را تیک بزنیم، با تغییر داده ها در هریک از جداول مرجع (References)، اطلاعات خروجی ابزار Consolidate نیز تغییر خواهند کرد و جدول خروجی به صورت خودکار به روز رسانی خواهد شد.
پس پایان مراحل فوق و کلیک کردن بر روی دکمه Ok، جدول نهایی رسم خواهد شد. لطفاً فایل نمونه را دانلو کرده و موارد بالا را تمرین نمایید. همچنین میتوانید با استفاده از ابزار Consolidate در اکسل بر روی تنها یک ستون از جدول بالا چندین عملیات انجام دهید.
برای دانلود فایل نمونه Consolidate در اکسل بر روی این خط کلیک نمایید.
سلام
مطلبی که ارائه شده بسیار آموزنده و کاربردی است، و از لطف شما بسیار ممنونم.
فقط سئوالم این است که اگر در مثال بالا بخواهیم مثلا ستون “قیمت واحد” یا “درصد تخفیف” چهار فصل فروش جمع نشوند، چطور باید آنها را استثنا کنیم.
بابت راهنمایی شما سپاسگزارم
سلام، خواهش میکنم، نمیتوانید استثنا تعریف کنید. راه حل اینست که ستونی که میخواهید جمع کنید را تنها و جداگانه در Consolidate قرار دهید و باقی ستون ها را کپی نمایید.
سلام وخسته نباشید خیلی راحت وروان توضیح دادید من قبلا یک علامت مساوی میذاشتم بعد همه شیتها را جمع میزدم ولی با این توضیح شما کارم خیلی راحتتره ممنون یک دنیا
سلام، خواهش میکنم، موفق باشید.
با سلام ببخشید اگر ترتیب کالا ها به هم بخوره ایا بازم متوجه میشه ؟؟
سلام، خیر
باسلام و احترام
در صورتی که جدول Consolidate که نمونه گذاشتید در ۴ شیت مختلف اگر ردیف ها در شیت ها جاهای مختلف باشد چطور
مثلا در ردیف اول shompoo در یک شیت در A1 است در شیت دوم A3 در شیت سوم A8 در شیت چهارم در A16 باشد به صورت پراکنده چطور می شود گفت تمام SHAMPOO ها را جستجو در ۴ شیت و با هم جمع بزن
باسپاس
سلام، برای این مورد که شما فرمودید نباید از ابزار Consolidate استفاده کرد. باید از توابع Lookup & Reference مانند VLOOKUP یا INDEX استفاده کنید. موفق باشید.
سلام و مرسی بابت توضیحات کاملتون
یه سوال دارم اگه ما در یک اکسل چند شیتی جداولی داشته باشیم مثلا از خرید اقلام مختلف در ماههای متفاوت که ترتیبی یکسان نداشته باشند راهی وجود داره که بشه فرمولی نوشت که اکسل موارد مشابه در شیتها رو پیدا و مقادیر رو جمع کنه؟
سلام، بله، از توابع جستجو و مرجع مانند تابع INDEX میتوانید استفاده کنید یا OFFSET و یا موارد مشابه، مقالات مرتبط را در سایت مطالعه نمایید.
عالی بود ممنون از لطف تون
عالی بود .
سلام خیلی عالی بود خیلی مختصر و مفید و کاربردی
سلام، متشکرم، موفق باشید.