نسخه جدید افزونه تقویم شمسی منتشر شد!!! جزئیات بیشتر در صفحه این محصول (سابقه افزونه تقویم شمسی) نوشته شده.
سبد خرید
0

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

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

ادغام چند شیت در اکسل

ادغام چند شیت
۳.۴/۵ - (۵ امتیاز)

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

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

برای حل این مسئله راه های متفاوتی وجود داره پاورکوئری، کدنویسی VBA و فرمول نویسی. در این مقاله میخواهیم از فرمول نویسی برای این موضوع استفاده کنیم. در واقع باید فرمولی بنویسیم که بتونه هر شیت رو شناسایی کنه و سلول مورد نظر رو فراخوانی کنه.

تابعی داریم به نام Address که میتونه آدرس سلول مورد نظر ما رو بسازه. این تابع ۵ آرگومان داره که در ادامه معرفی میکنیم:

Row_num: شماره ردیف سلول مورد نظر رو در این آرگومان مشخص میکنیم.

Column_num: شماره ستون سلول مورد نظر رو در این آرگومان مشخص میکنیم.

[abs_num]: در این آروگومان نوع آدرس دهی ($) سلول مورد نظر رو مشخص میکنیم. با توجه به اینکه اختیاری هست این آرگومان اگر تعیین نکنیم، کاملا مطلق یعنی $A$1 در نظر گرفته میشه.

[a1]: در این آرگومان نوع ارجاع به سل رو مشخص میکنیم. A1 یا R1C1 . این نیز با توجه به اختیاری بودن، اگر مشخص نشه، حالت A1 تعیین میشه.

[Sheet_text]: در این آرگومان اسم شیت مورد نظر از سلولی که میخوایم فراخوانی کنیم رو مشخص میکنیم. این آرگومان نیز اختیاریست. پس در صورت تعیین نکردن، آدرس بدون نام شیت مشخص خواهد شد.

حالا که تابع رو معرفی کردیم، ابتدا مسئله شرح بدیم و بعد بریم سراغ راه حل:

شرح مسئله

یک فایل اکسل شامل چندین شیت با ساختار یکسان داریم مطابق شکل ۱:

  • شماره هر فاکتور به عنوان نام همون شیت در نظر گرفته شده است.
  • در هر فاکتور، جمع فروش محصول در سلول I27 محاسبه میشه.
ساختار نمونه فاکتور

شکل ۱- ساختار نمونه فاکتور

حالا میخوایم در شیتی به نام All همه سلول های جمع فاکتور یعنی سلول های I27 در همه شیت ها رو زیر هم لیست کنیم. گفتیم برای این کار از تابع Address باید استفاده کنیم. قبل از این کار، ستونی رو مشخص میکنیم و اسم شماره فاکتورها رو در اون لیست میکنیم. مطابق شکل ۲.

ایجاد ستونی از نام شیت ها

شکل ۲- ایجاد ستونی از نام شیت ها

حالا آرگومان های این تابع رو به شرح زیر مشخص میکنیم:

آرگومان اول: شماره ردیف ۲۷ (از سلول I27)

آرگومان دوم: شماره ستون ۹ (ستون I نهمین ستون اکسل است)

آرگومان سوم و چهارم اختیاریست و همانطور که گفته شد میتونیم حذف کنیم.

آرگومان پنجم: نام شیت رو تعیین میکنیم. (ستون کمکی)

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

ADDRESS(27,COLUMN(I1),,,A2)

خروجی این فرمول آدرس سلول های I27 در شیت های مختلف است. (شکل ۳)

 

 

خروجی تابع Address

شکل ۳- خروجی تابع Address

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

INDIRECT(ADDRESS(27,COLUMN(I1),,,A2))

نکته:
با استفاده از کدنویسی VBA خیلی راحت میشه نام شیت های یک فایل رو لیست کرد. برای این کار میتونید از کد زیر استفاده کنید:

کافیه که این کد رو در محیط VBA و داخل ماژول کپی کنید و کد رو اجرا کنید. اگر با نحوه کپی کردن و اجرای کد آشنا نیستید حتما مقاله آشنایی با ماکرونویسی در اکسل رو مطالعه کنید.

همچنین از پاورکوئری هم میشه استفاده کرد و نام شیت ها رو لیست کرد. برای این کار از مسیر زیر روی گزینه From Workbook کلیک میکنیم:

 Data/ Get & Transform Data/ Get Data/ From File

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

شکل ۴- فراخوانی فایل اکسل مورد نظر برای لیست نام شیت ها

از پنجره باز شده فایل اکسل مورد نظر که میخواهیم لیستی از شیت های اون داشته باشیم رو انتخاب میکنیم. از پنجره باز شده، یکی از شیت ها رو انتخاب کرده و روی گزینه Transform Data کلیک میکنیم.

وارد محیط Query Editor میشیم و حالا باید از قسمت Applied Steps روی گزینه Source کلیک کنیم چون به کل شیت ها نیاز داریم نه فقط یکی. پس روی Source کلیک میکنیم و بقیه مراحل بعدی رو پاک میکنیم (مطابق شکل ۵)

حذف مراحل انجام شده از قسمت Applied Steps و کلیک روی Source

شکل ۵- حذف مراحل انجام شده از قسمت Applied Steps و کلیک روی Source

با این کار در ستون Name اسم شیت های فایل اکسل مورد نظر رو میبینیم. حالا کافیه که از تب Home روی گزینه Close & Load To کلیک کنیم و از پنجره نمایش داده شده، گزینه Table و new worksheet رو انتخاب کرده و ok بزنیم.

حالا در یک شیت، لیستی از همه شیت های موجود در فایل اکسل مورد نظر رو داریم. حالا میتونیم فمرول address که آموزش داده شد رو برای این داده ها ثبت کنیم.

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

همونطور که میبینید ترکیب روش های مختلف اعم از فرمول نویسی ،کدنویسی ،ابزارها و … به افزایش بهره وری و حل بهینه مسائل خیلی کمک میکنه.

میتونید برای درک بهتر این موضوع، ویدئو زیر رو مشاهده کنید و فایل اکسل رو نیز از انتهای مقاله دانلود بفرمایید.

مشاهده ویدئو ادغام چند شیت در اکسل

 

دانلود فایل اکسل ادغام چند شیت

هر سوالی در مورد ادغام چند شیت در اکسل دارید در قالب کامنت در ذیل همین مقاله ثبت کنید.

133

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

دیدگاه کاربران
  • داود ۲۵ مرداد ۱۴۰۳ / ۱۲:۴۱ ب٫ظ

    عالی
    مشکلی داشتم که با مطلب شما حل شد
    تشکر

ارسال دیدگاه

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

توسط
تومان