تجمیع جداول و ترکیب کردن مختلف داده ها

بهم چسباندن اطلاعات جداول یکسان به هم

خیلی وقت ها پیش میاد که داده ها در جداول جداگانه و یا حتی در شیت های جداگانه ای ذخیره میشن و ما میخوایم با تجمیع جداول این داده ها رو کنار هم بیاریم و بعد تحلیل و گزارش گیری انجام بدیم. پس ترکیب جداول و ادغام داده ها با یکدیگر یکی از مسائل مهم و پرکاربرد در اکسل به شمار میاد. این کار رو ما از طریق ابزار power query و دستور append/Merge میتونیم انجام بدیم که در مقاله مرتبط قبلا شرح داده شده.

مقاله مرتبط: ادغام داده ها با استفاده از power query

دراین مقاله میخواهیم این کار رو با تابع انجام بدیم. تابعی که برای این کار استفاده میکنیم تابع Vstack/Hstack هست این تابع در نسخه دسکتاپ اکسل موجود نیست و در اکسل آنلاین و گوگل شیت وجود داره. برای آشنایی با نحوه شروع کار با گوگل شیت حتما مقاله زیر رو مطالعه کنید:

مقاله مرتبط: شروع کار با گوگل شیت

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

تابع Vstack محدوده های انتخابی رو در انتهای هم و بصورت عمودی میچینه و تابع Hstack عینا همین کار رو و بصورت افقی انجام میده. آرگومان های این تابع به صورت زیر است:

Range1: محدوده اول مورد نظر.

[Range2]: محدوده دوم و … (بیش از دو محدوده رو میتونیم با هم ادغام کنیم.)

توجه: هر مثالی برای Vstack میزنیم برای Hstack قابل استفاده است. این تابع هم مثل خیلی از توابع گوگل شیت، نتیجه رو Spill میکنه یعنی کافیه فرمول فقط در یک سلول نوشته بشه تا نتیجه تا جایی که مورد نیازه، Spill بشه. اگر با این موضوع آشنا نیستید حتما مقاله زیر رو بخونید:

مقاله مرتبط: توابع جدید اکسل ۲۰۲۱

ترکیب دو جدول مختلف

مثال ۱: فرض کنید در دو فروشگاه جداول میزان موجودی از هر محصول رو برامون فرستادن. حالا میخوایم این دو جدول رو یکی کنیم و گزارش بگیریم.

برای اینکه همین الان با این مقاله تمرین کنید یک گوگل شیت آماده کردم که میتونی وارد بشی و این کار رو اونجا انجام بدی.

شکل ۱- ترکیب دو محدوده با استفاده از تابع Vstack

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

حالا بریم نواقصی که بنظر میاد رو برطرف کنیم. اولین موضوعی که به چشم میاد اینه که سرستون هر دو جدول، داخل دیتا اومده در حالی که فقط همون اول باید فقط بیاد. برای این موضوع دو تا کار میشه انجام داد. یکی اینکه اول سرستون رو دستی در ردیف اول بنویسیم و زیرش فرمولی بنویسیم که محدوده هاش از ردیف ۲ شروع بشه (بعبارتی سرستون های جدول داخل محدوده های انتخابی قرار نگیره). یک راه دیگه هم اینه که فرمول رو بصورت زیر بنویسیم:

=VSTACK({“محصول“,“فروش“},A2:B6,D2:E6)

در این فرمول دو برچسب مورد نظر رو از طریق یک آرایه افقی مشخص میکنیم و اینطوری اول اون دو برچسب قرار میگیرن و بعد دو محدوده داده (بدون سرردیف).

آپدیت و بروزرسانی

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

=VSTACK(A1:B100,D1:E100)

این تغییر رو روی فرمول ایجاد کنیم و نتیجه رو ببینیم. وقتی این فرمول رو میزنیم، همه ردیف های خالی تا ردیف ۱۰۰ رو هم در نتیجه نهایی نمایش میده. یعنی دو محدوده رو زیر هم قرار میده اما کلی ردیف خالی بینشون قرار میگیره که مطلوب نیست. پس اینجا میتونیم این تابع رو با یک تابع دیگه مثل Filter ترکیب کنیم تا خالی ها رو دیگه نیاره:

=filter(VSTACK(A2:B100,D2:E100),VSTACK(A2:A100,D2:D100)<>“”)

مقاله مرتبط: تابع Filter در اکسل ۲۰۲۱

همونطور که قبلا راجع به تابع Filter صحبت کردیم، دقیقا عملکرد ابزار فیلتر رو داره منتها بصورت داینامیک این کار و میکنه و تغییرات رو درجا اعمال میکنه. فرمول بالا داره دو محدوده ۱۰۰ ردیفی رو در انتهای هم و بصورت عمودی قرار میده و در قسمت شرط مشخص میکنه که ستون اول (محدوده ترکیب شده) باید خالی نباشه. خیلی دقت کنید که قسمت شرط، یک ستون قرار گرفته.

با این فرمول دیگه خیالمون راحته که تا ۱۰۰ ردیف هر چی دیتا اضافه بشه به انتهای داده ها، جدول ترکیب شده هم سریعا آپدیت میشه.

موضوعی که برای این تابع مطرحه اینه که اگر محدوده های ورودی پهنای یکسانی نداشته باشن، این تابع بزرگترین جدول رو در نظر میگیره و برای سلول های خالی در سایر محدوده ها، خطای N/A نمایش نشون میده.

در مثال زیر، اگر یکی از جداول، نام شعبه رو هم وارد کرده باشه، نتیجه بصورت زیر خواهد بود:

شکل ۲- ترکیب داده ها و نمایش NA بجای سلول های خالی در زمان تجمیع جداول

حالا کافیه که این فرمول رو با یک Iferror ترکیب کنیم و بجای خطا مثلا خط تیره نمایش بدیم.

=iferror(filter(VSTACK(A2:C101,E2:F101),VSTACK(A2:A101,E2:E101)<>””),“-“)

حذف داده های تکرای از دیتابیس ادغام شده

فرض کنید میخوایم داده های چند جدول رو ادغام کنیم و یک لیست بدون تکرار درست کنیم. مثلا یک سری نام محصول در جداول مختلف قرار گرفته و میخوایم یک لیست تجمیع شده بدون تکرار بسازیم. برای این کار، کافیه خروجی فرمول Vstack رو بذاریم توی یک تابع Unique. میدونیم که تابع Unique کارش اینه که داده های تکراری رو حذف کنه.

=UNIQUE(VSTACK(A1:A6,C1:C10,E1:E5))

شکل ۳- حذف داده های تکراری از دیتابیس های مرج شده

ترکیب تجمیع جداول از چند شیت

برای اینکه بتونیم داده های موجود در چند شیت رو اینطوری با هم ترکیب کنیم، کافیه محدوده های هر شیت رو انتخاب کنیم.

=VSTACK(‘Vstak-Filter’!D2:E6,‘Vstack-Iferror’!A2:C6)

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

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

=VSTACK(‘Sheet 1:Sheet 3’!A2:C6)

برای نوشتن این فرمول، اول vstack=( رو مینویسیم و روی اولین شیت کلیک کرده و محدوده مورد نظر رو انتخاب میکنیم بعد کلید Shift رو نگه میداریم و روی شیت آخر کلیک میکنیم. با این کار هر شیتی که بین این دو شیت انتخاب باشن، توی فمرول در نظر گرفته میشن. (برای زمانی که شیت ها خیلی زیاده، بسیار مفید و کاربردیه).

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

ویدئو تجمیع جداول و چسباندن اطلاعات در اکسل