یکپارچه کردن داده ها در اکسل
ابزار Consolidate در اکسل
یکی از مسائلی که افراد در حین کار با فایل های اکسل با اون سر و کار دارن، اینه که چطور میشه داده های چند شیت که ساختار یکسانی دارند رو یکپارچه و جمع بندی کرد. در این مقاله میخوایم روش یکپارچه کردن داده ها رو با استفاده از ابزار consolidate در اکسل تشریح کنیم.یکپارچه کردن (Consolidate) داده های چندین شیت در یک شیت جداگانهسریع ترین راه برای یکپارچه کردن داده ها در اکسل (در یک یا چندین ورکبوک) استفاده از ابزار Consolidate است. این ابزار از ابزارهای اصلی نرم افزار اکسل است و نیازی به افزونه نداره. پس براحتی میتونیم از این ابزار استفاده کنیم.در مثالی که در ادامه میخوایم روش کار کنیم، فایلی داریم که حاوی چندین شیت از گزارش فروش فصلی است. یعنی هر شیت برای یک فصل هست و گزارش فروش مربوط به سه ماه هر فصل در هر شیت وجود داره. حالا میخوایم همه این اطلاعات رو در یک شیت خلاصه کنیم که بتونیم ببینیم وضعیت کلی فروش به چه صورت است. همونطور که در شکل ۱ نمایش داده شده، سه شیت به نام های فروش تابستان، فروش پاییز و فروش زمستان موجود است.
شکل ۱- داده های مربوط به فروش هر فصل به تفکیک ماه
برای این کار مراحل زیر رو انجام میدیم:گام اول: داده ها رو مرتب میکنیم. قبل از استفاده از ابزار Consolidate در اکسل باید از موارد زیر اطمینان حاصل کنیم:- داده ای که میخوایم یکپارچه کنیم باید روی شیت جداگانه ای باشه. نباید روی شیتی که یکپارچه سازی رو پیاده می کنیم، هیچگونه داده دیگه ای وجود داشته باشه.
- همه شیت ها ساختار یکسان داشته باشن و عنوان های ستون ها/ردیفها (در صورت وجود) در شیت ها مشابه باشه.
نکته:
مثلا کلمه “کیک” در دو شیت وجود داره، باید هر دو عین هم نوشته شده باشه و “ی” عربی و فارسی نباشه. یا space اضافه نداشته باشه. چون این موارد از نظر محتوایی تفاوت ایجاد میکنن.
- هیچ ردیف یا ستون خالی (بین محدوده انتخابی) وجود نداشته باشه.
شکل ۲- انتخاب گزینه Consolidate
نکته:
بهتره که یکپارچه سازی داده ها درون یک شیت خالی انجام بشه. اگر شیت نهایی شما از قبل حاوی داده است، مطمئن بشین که فضای خالی کافی برای داده های یکپارچه سازی شده وجود داشته باشه.
گام سوم: در پنجره باز شده، تنظیمات زیر رو اعمال میکنیم:- در قسمت Function تابعی که میخوایم برای یکپارچه سازی انجام بشه رو انتخاب میکنیم (مثل: Count , Sum , Average , Min , Max و …). چون میخوایم جمع فروش رو حساب کنیم، در اینجا تابع Sum رو انتخاب میکنیم.
- در قسمت Reference روی آیکون Collapse Dialog کلیک میکنیم و محدوده مورد نظر رو از شیت اول انتخاب میکنیم. سپس روی دکمه Add کلیک میکنیم تا محدوده به قسمت All References اضافه بشه. بعد روی شیت بعدی کلیک میکنیم و محدوده مورد نظر رو انتخاب کرده و Add میزنیم. این کار رو برای داده های دیگه در بقیه شیت ها هم انجام میدیم.
شکل3- انتخاب داده ها برای یکپارچه سازی توسط ابزار Consolidate در اکسل
گام چهارم: در همون پنجره شکل ۳، میتونیم هر کدوم از گزینه های Top Row/Left Column رو انتخاب کنیم. اگر بخوایم اسم ستون و/یا ردیف داده های اولیه در شیت نهایی هم نمایش داده بشه، گزینه Top Row و/یا Left column رو انتخاب میکنیم. (در این مثال، منظور ما از این کلمات، عبارات کیک، بستنی و … و فروش مهر و …)با این کار، عنوان ردیف و ستون اول برای اکسل شناخته شده است و حتی اگر این عنوان ها جابجا باشند، به درستی تشخیص داده میشن. مثلا اگر کلمه کیک در یک شیت، ردیف اول باشه و در شیت بعدی ردیف دوم، این ابزار، این جابجایی رو تشخیص میده و عملیات جمع (تابع انتخابی) روی داده های مرتبط انجام میشه.بعد از زدن OK، مطابق شکل ۴ جمع فروش در همه ماه ها و به تفکیک محصولات در شیت انتخابی، نمایش داده میشه.
شکل ۴- داده های یکپارچه شده توسط Consolidate در اکسل
اگر بخوایم کاری کنیم که داده ها به منبع اصلی متصل باشن یعنی وقتی داده ها در شیت های مرجع رو تغییر میدیم، بطور خودکار داده های یکپارچه سازی شده هم بروزرسانی بشن، میتونیم گزینه Create links to source data رو که در شکل ۳ نشان داده شده است انتخاب کنیم. با این کار، مطابق شکل ۵ اکسل یک لینک به شیت اولیه درست میکنه و تابع انتخابی (مثلا Sum) رو وارد سلول میکنه. (در حالت قبلی فقط نتیجه تابع انتخابی در شیت تجمیع شده نمایش داده میشد، با این کار، تابع مورد نظر نمایش داده میشه).
شکل ۵ – یکپارچه کردن داده ها با ابزار Consolidate در اکسل
همچنین ریز داده ها رو بصورت یک گروه نمایش میده. بصورتی که اگر زبانه سرگروه (علامت +) رو بزنیم، گروه باز شده و زیرگروه آن که همون داده های مرجع در شیت های مربوطه هستن نمایش داده میشه. (شکل ۶)
شکل ۶- نمایش داده های زیرمجموعه هر دسته با استفاده از Consolidate در اکسل
نکته:
در صورتی که تیک Left Column رو زده باشیم، حتی اگر مثلا در یکی از شیت ها محصول کیک وجود نداشته باشه، باز هم محاسبات به درستی انجام میشه چون ابزار، عنوان ردیف ها رو بصورت هوشمندانه شناخته و تفکیک میکنه. دقت داشته باشید اگر داده ها فقط سر ستون دارن، Top Row، اگر فقط سر ردیف دارن، Left Column و اگر هر دو رو دارن، هر دو رو تیک میزنیم.
همونطور که دیدیم، ابزار Consolidating در تجمیع و یکپارچه کردن داده ها از چندین شیت میتونه خیلی کاربردی باشه. اما این ابزار محدودیت هایی هم داره. مثلا اینکه فقط با داده های عددی کار میکنه و متن ها رو نمیتونه تجمیع کنه. همینطور تعداد توابعی که میتونیم برای انجام محاسبات استفاده کنیم، محدود به همون تعداد توابع موجود در Function هست.اما گاهی اوقات شرایط و خواسته ما به گونه ای است که نمیشه از این ابزار استفاده کرد. در این رابطه یک افزونه (Add Ins) به نام RDB Merge وجود داره که تا حد خیلی خوبی، کار یکپارچه سازی رو بصورت پیشرفته تر انجام میده. اگر این افزونه هم نیاز ما رو برطرف نکنه، خودمون باید اقدام به کدنویسی کنیم و خواسته خودمون رو به زبان VBA تبدیل کنیم.همچنین یک راه دیگه برای انجام این کار استفاده از Power Query هست که پیشنهاد میکنیم مقاله ترکیب جداول در اکسل با استفاده از Power Query هم بخونید.
سلام وقت بخیر
من دو سوال دارم از خدمتتون
۱ من یک سری مشتری دارم که هرکدوم تعدادی فاکتور دارند من میخوام یک فرمولی در اکسل بهش بدم که بیاد فاکتور های هر مشتری رو با هم جمع کنه و توی ستون جلو جمع رو بزاره
توجه داشته باشید شاید من ۴۰۰ تا مشتری داشته باشم که هر کدوم تعداد فاکتور متفاوت داشته باشند
۲ مسعله دوم منیک سری مشتری دارم با کد میخوام کد مشتریها را از یک ردیف برداره و دقیقا جلو خود اون مشتری در ردیف دیگه بزاره
ممنون میشم راهنمایی بفرمایید
درود بر شما
sumif بصورت شرطی جمع میزنه
مسئله دوم هم نامفهومه
سلام من یه سایت دارم که داده هامو از فایل های شرکت های مختلف موجودی رفرانس عکس (ستون های مختلف )ولی تو بخش رفرانس تکراری میباشد من میخوام یک فایل ترکیبی از این ها بدست بیارم که میخوام تکراری نباشه ادغام بشه با هم لطفا راهنماییم میکنید ؟
درود
سوال مبهمه
ساختار فایل هم باید مشخص باشه
سلام.
فرض کنیم ۲۰ ردیف داده داریم و هر روز داره به تعداد داده های ما اضافه می شه. مثلا محصول تحویلی به انبار. بنابراین کدهای تکراری داخل اش زیاده. قاعدتا با ابزار advanced filter می شه در مورد داده ها یکپارچه سازی کرد ولی هر بار که بخوام گزارش بگیرم باید این ابزار رو بروز رسانی کنم . چه پیشنهادی دارید که از طریق فرمول نویسی بشه این کار رو انجام داد؟
درود
اگر منظورتون از یکپارچه سازی، جستجوی موارد تکراری هست، این مقاله رو بخویند
با پیوت هم میتونید
با ضبط ماکرو از advance filter هم میتونید
با سلام
می خام در ستون اول که دارای مقادیر تکراری دارد را تجمیع نموده و ضمنا مقادیر (کلمه text) در ستون دوم که متفاوت میباشد را در باهم در کنار هم قرار داده و جلوی هر یک از مقادیر تجمیع شده قرار دهد. ممنون میشم اگر راهنمائی فرمائید. با تشکر
۴۱۱ علی علی محمد حسن
۴۱۱ محمد
۴۱۱ حسن
۴۱۲ بابک بابک رضا
۴۱۲ رضا
درود بر شما
برای تفکیک داده ها روش های مختلفی وجوود داره
text to column
flash fill
تفکیک کنید و بعد تکراری ها رو حذف کنید
سلام – مثل اینکه نتونستم منظورم را برسونم – من میخوام تکراریهای ستون اول حذف بشده و در ستون دوم تجمیع ستون دوم نمایش داده بشه چون text میباشد با pivotTable نمیشه تجمیش کرد . آیا راهی هست لطفا راهنمائی فرمائید.
۹۸۰۰۲۲۷۲ بوش میل موجگیرجلو
۹۸۰۰۲۲۷۲ ضربه گیر دسته موتوربالا راست
۹۸۰۰۲۲۷۲ دسته موتوربالاراست
۹۸۰۰۳۳۱۳ میل لنگ
۹۸۰۰۳۳۱۳ پمپ آب طرح BPS (شش پره)
درود بر شما
اگر منظور از تجمیع اینه که یک کد بیاد و جلوش متن های مربوطه د رکنار هم نمایش داده بشن، میتونید از فرمول زیر استفاده کنید:
=TEXTJOIN("-",TRUE,IF($A$1:$A$5=I1,$B$1:$B$5,""))دقت کنید که فرمول آرایه ای هست
نکته:
۱- این تابع از توابع ۲۰۱۹ هست
۲- اول یک لیست یونیک از کدها ایجاد کنید بعد این فرمول رو جلوی هر کد بنویسید و درگ کنید