سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

تجمیع جداول
۵/۵ - (۱ امتیاز)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

شکل ۱- ترکیب دو محدوده با استفاده از تابع 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 صحبت کردیم، دقیقا عملکرد ابزار فیلتر رو داره منتها بصورت داینامیک این کار و میکنه و تغییرات رو درجا اعمال میکنه. فرمول بالا داره دو محدوده ۱۰۰ ردیفی رو در انتهای هم و بصورت عمودی قرار میده و در قسمت شرط مشخص میکنه که ستون اول (محدوده ترکیب شده) باید خالی نباشه. خیلی دقت کنید که قسمت شرط، یک ستون قرار گرفته.

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

آپدیت شدن جدول در زمان تجمیع جداول

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

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

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

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

شکل ۲- ترکیب داده ها و نمایش 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 رو نگه میداریم و روی شیت آخر کلیک میکنیم. با این کار هر شیتی که بین این دو شیت انتخاب باشن، توی فمرول در نظر گرفته میشن. (برای زمانی که شیت ها خیلی زیاده، بسیار مفید و کاربردیه).

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

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

در حال بارگذاری پخش کننده...
آواتار
144

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • Farhad ۱۹ اردیبهشت ۱۴۰۳ / ۵:۴۵ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۲۱ اردیبهشت ۱۴۰۳ / ۱۱:۵۰ ق٫ظ

      درود بر شما
      هم میتونید پیوت بزنید روی داده ها
      هم اینکه با استفاده از توابع unique و sumifs محاسبات انجام بدید

  • رضا ۶ اردیبهشت ۱۴۰۳ / ۳:۲۶ ب٫ظ

    سلام
    من یک فایل اکسل دارم که سه تا سطر داره
    علی / ۷/ /
    علی / / ۳ /
    علی / / / ۴
    میخوام بشه
    علی /۷ /۳ /۴
    چیکار باید بکنم.

    • آواتار
      حسنا خاکزاد ۱۵ اردیبهشت ۱۴۰۳ / ۱۲:۴۴ ب٫ظ

      درود بر شما
      یکبار با تابع substitute همه / رو با “” جایگزین کنید
      بعد با توابع متنی مثل mid یا left/right تفکیک کنید و بعد با تابع textjoin به هم بچسبونید

  • علی ۲۴ دی ۱۴۰۲ / ۲:۱۶ ب٫ظ

    باسلام
    من چند تا فایل اکسل دارم که شامل جداول شبیه هم هستن مثلا لیست دانش آموزان هرکلاس درس
    حالا من یک فیل اکسل میخواهم که تمام دانش آموزان مدرسه را در یک فایل و یک شیت نشان دهد
    ۰۹۱۲۰۳۴۴۶۷۲
    ممنون میشم راهنمایی بفرمایید

    • سامان چراغی ۶ بهمن ۱۴۰۲ / ۷:۳۰ ب٫ظ

      درود
      با استفاده از Power Query از مسیر زیر، اطلاعات جداول را از فایل ها مورد نظر استخراج کنید:
      Data>Get Data>From File>From Folder

  • محمدتاجیکی ۸ آبان ۱۴۰۲ / ۸:۲۱ ب٫ظ

    با سلام و وقت بخیر
    ضمن تشکر از اموزش های جذابتون
    من یکی از داشبورد هاتون رو برای مطالعه و الگوی برخی نمودارها دانلود کردم در مورد فروش انواع دارو هست در سالهای ۹۷ و ۹۸ و … و یک داشبوردی طراحی کردید که قطعا از پاویت داده گرفته و دینامیک هست
    تا اینجا شرح ماجرا
    سوال : در یک بخشی در شیت محاسبات، پاویتی دارید که هم شکلش متفاوت هست برای تفکیک میزان فروش انواع داروها در بخش خصوصی و دولتی و هم وقتی مشابه اون رو طراحی میکنم، اطلاعاتش
    نکته اینه که اساسا شما فیلدی بنام خصوصی و دولتی در ان دیتا بانک ندارید. از کجا داده میاره این جدول
    لطفا اگه ممکنه پاسخ بدید که ذهنم رو درگیر کرده

    شماره من ۰۹۱۲۲۸۳۲۴۲۰
    با تشکر

    • آواتار
      حسنا خاکزاد ۹ آبان ۱۴۰۲ / ۵:۵۴ ب٫ظ

      درود بر شما
      گروه بندی شده
      توی گزارش ایجاد شده، لیبل های دلخواه رو انتخاب میکنید و group selection رو میزنید
      از لینک زیر هم میتونید آموزش ساخت این داشبورد رو تهیه کنید
      https://excelpedia.net/product/pivot-table-dashboard/

  • فرزاد ۱۴ خرداد ۱۴۰۲ / ۱۱:۰۲ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۵ خرداد ۱۴۰۲ / ۱۲:۲۲ ب٫ظ

      درود بر شما
      ممنون از همراهی و ابراز محبت شما

    • آواتار
      حسنا خاکزاد ۱۵ خرداد ۱۴۰۲ / ۱۲:۲۲ ب٫ظ

      درود بر شما
      ممنون از همراهی و ابراز محبت شما

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان