amir_ts
2015/11/22, 14:01
سلام
ارجاع سه بعدی برای جمع سلول های مشابه در صفحات مختلف
زمانی که میخواهیم چندین خانه از صفحات مختلف که ساختار یکسانی دارند را جمع کنیم از فرمول =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 آدرس پایانی محدوده جمع کردن هست .
موفق باشید.
فایل ها ضمیمه گردید.
ارجاع سه بعدی برای جمع سلول های مشابه در صفحات مختلف
زمانی که میخواهیم چندین خانه از صفحات مختلف که ساختار یکسانی دارند را جمع کنیم از فرمول =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 آدرس پایانی محدوده جمع کردن هست .
موفق باشید.
فایل ها ضمیمه گردید.