Tim یک کاربرگ دارد که سلول B1 حاوی فرمول =SUM(A1:A7) است. او می خواهد این فرمول را کپی کند و دامنه را 7 ردیف افزایش دهد، به طوری که سلول B2 حاوی فرمول =SUM(A8:A14)، سلول B3 حاوی =SUM(A15:A21) و غیره باشد. مشکل این است که هنگامی که او آن را کپی می کند، هر "پایان" محدوده فقط با 1 افزایش می یابد، جایی که باید 7 افزایش یابد تا نیاز او برآورده شود. او تعجب می کند که چگونه می تواند اکسل را وادار کند که افزایش مناسب را انجام دهد.
شما نمی توانید اکسل را وادار کنید تا با استفاده از کپی و پیست، افزایش مناسب را انجام دهد. آن را انجام نمی دهد. دلیل آن ساده است - مواقعی وجود دارد که افزایش 1 از دیدگاه فرمولی منطقی است. از آنجایی که اکسل نمی تواند ذهن شما را بخواند (حداقل تا نسخه بعدی:>))، این فرض را ایجاد می کند که فقط باید 1 افزایش یابد.
راه حل این است که فرمول خود را تغییر دهید. با استفاده از چند تابع کاربرگ می توانید از اکسل بخواهید محدوده مورد نظر را برای جمع بندی بسازد. مثال زیر را از فرمولی در نظر بگیرید که مجموع مورد نظر را ارائه می دهد:
=SUM(INDIRECT("A" & (ROW()-1)*7+1 & ":A" & (ROW()-1)*7+7))
اگر این فرمول را در سلول B1 قرار دهید، کار می کند زیرا به شماره ردیف (که توسط تابع ROW برگردانده می شود) ردیفی که فرمول در آن قرار دارد نگاهی می اندازد. از آنجایی که در ردیف 1 قرار دارد، فرمول به این صورت توسط اکسل ارزیابی می شود:
=SUM(INDIRECT("A" & (ROW()-1)*7+1 & ":A" & (ROW()-1)*7+7))
=SUM(INDIRECT("A" & (1-1)*7+1 & ":A" & (1-1)*7+7))
=SUM(INDIRECT("A" & 0*7+1 & ":A" & 0*7+7))
=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))
=SUM(INDIRECT("A" & 1 & ":A" & 7))
=SUM(INDIRECT("A1:A7"))
=SUM(A1:A7)
آنچه در B1 به دست می آورید، مجموع مورد نظر شما است. (تابع INDIRECT از مقدار در رشته استفاده می کند که گویی یک محدوده واقعی است، که همان چیزی است که شما می خواهید.) وقتی فرمول را در پایین ستون کپی می کنید، با افزایش تعداد ردیف، فرمول افزایش های مناسب 7 را در هر دو طرف ارائه می دهد. از محدوده
تغییرات دیگری در این تکنیک وجود دارد که می توانید از آنها استفاده کنید. تنها تفاوت این است که تغییرات از توابع مختلف کاربرگ برای انجام یک کار استفاده می کنند. به عنوان مثال، تغییر زیر همچنان از تابع ROW استفاده می کند، اما در نهایت برای محاسبه محدوده مورد نظر به تابع OFFSET متکی است:
=SUM(OFFSET(A1,((ROW()-1)*6),0):OFFSET(A7,((ROW()-1)*6),0))
یک رویکرد کوتاه‌تر که از OFFSET استفاده می‌کند به شرح زیر است:
=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))
صرف نظر از رویکرد، احتمالاً می توانید بگویید که ایده این است که فرمولی ارائه کنید که از ردیفی که در آن فرمول ظاهر می شود استفاده می کند تا محدوده مورد نظر شما را ایجاد کند. هر یک از مثال‌های تا کنون فرض می‌کند که شما در سلول B1 شروع می‌کنید. اگر می‌خواهید در سلول B2 شروع کنید، باید فرمول‌ها را تغییر دهید تا هر ردیفی را که از آن شروع می‌کنید حساب کنید. اگر در سلول B2 شروع می‌کردید، برای اینکه فقط ایده‌ای درباره نحوه کار این کار به شما بدهم، سه فرمول ارائه‌شده در این نکته به روش‌های زیر اصلاح می‌شوند:
=SUM(INDIRECT("A" & (ROW()-2)*7+2 & ":A" & (ROW()-2)*7+8))
=SUM(OFFSET(A2,((ROW()-2)*6),0):OFFSET(A8,((ROW()-2)*6),0))
=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))
از مکان دیگری شروع کنید، و باید تغییرات بیشتری در فرمولی که برای استفاده انتخاب می کنید انجام دهید.
