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

[amir_ts]

سلام

ارجاع سه بعدی برای جمع سلول های مشابه در صفحات مختلف

زمانی که میخواهیم چندین خانه از صفحات مختلف که ساختار یکسانی دارند را جمع کنیم از فرمول =sum(start:end!cells) استفاده میکنیم.که در آن
Start نام صفحه نخست
End نام صفحه پایانی
cells آدرس سلول های مورد نظر هست.
مثلا فرمول

sum(sheet1:sheet5!a1:a3)  = 

مجموع خانه ها 1 تا 3 از صفحات 1 تا 5 رو محاسبه میکنه.
حالا میتونیم با تابع indirect اون رو تغیر بدیم که با تغیر نام صفحه مجموع سلول های مشخص شده اون صفحه رو حساب کنه.
=

SUM(INDIRECT("'"&F11&"'!a1:a(("3 

F11 نامه صفحه میباشد.
فرض کنیم میخواهیم نام صفحات رو هم تغیر بدیم و از خانه دیگه آدرس دهی کنیم به طور معمول فرمول به این شکل میشه :

Sum(indirect(“’”&C27&”:”&D27&”’!a1:a3”)) 

=
که C27 نام صفحه اول و D27 نام صفحه آخر هست .
اما این فرمول علی رغم ظاهر بدون اشکال، خطای ref میده!!!چرا؟
علت اینجا است که تابع INDIRECT در ارجاع سه بعدی (D reference.3) درست عمل نمیکنه و خطای REF تولید میکنه.
برای رفع این مشکل میتونیم از تکنیکی که حاصل ترکیب توابع SUMPRODUCT,SUMIF,INDIRECT,INDEX,MATCH استفاده کنیم.
=

SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX(sheet_name;MATCH($C$27;sheet_name;0)):INDEX(sheet_name;MATCH($D$27;sheet_name;0))&"'!"&$C$28&":"&$D$28);">0”)) 

Sheet _name نام لیست اسامی کاربرگ ها میباشد.
C27 نام کاربرگ ابتدایی ،D27 نام کاربرگ انتهایی C28 نام سلول ابتدایی و D28 نام سلول انتهایی میباشد.

فرمول بالا برای تمامی کاربرگ ها که نام آنها اعم از عدد و متن باشند عمل میکنه.اگر چنانچه نام کاربرگ ها به عدد و مرتب باشند، بدون استفاده از تابعINDEX و MATCH و با استفاده از تابع ROW میتوان فرمول رو کوتاه تر کرد:

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT($D$11&":"&$E$11))&"'!"&$D$12&":"&$E$12);">0")) 

که D11 نام صفحه اول و E11 صفحه آخر وD12 آدرس شروع محدوده و خانه E12 آدرس پایانی محدوده جمع کردن هست .

موفق باشید.

فایل ها ضمیمه گردید.

[ghazaee]

سلام از مطلب ارسالی بسیار خشنود شدم ولی هنوز درک نکردم نیاز به مثال های بیشتری است برای من بسیار کاربردی است ولی هنوز درکی نکرده ام اگه میشه بیشتر تشریح کنید استفاده از بقیه توابع بصورت سه بعدی فوق العاده است