چند تابع درمورد آدرس دهی داینامیک در اکسل

تابع MATCH: برای بدست آوردن شماره سطر دارنده یک مقدار در یک ستون

مثال کد زیر شماره سطری را در ستون A بر می گرداند که دارای مقدار 5 باشد

=MATCH(5,A:A,0)

تابع ADDRESS:  برای بدست آوردن آدرس یک خانه با مشخص کردن سطر و ستون

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

مثال: کد زیر مقدار $C$2  را بر می گرداند

=ADDRESS(2,3)

تابع INDIRECT: برای بدست آوردن مقدار درون یک آدرس (که بصورت رشته مشخص شده است)

INDIRECT(ref_text,a1)

مثال:  فرض کنید در خانه A5  مقدار 6 قرار دارد و در خانه B6 مقدار 10. کد زیر مقدار 10 را بر می گرداند

= INDIRECT("B"& $A$5)

=SUM(INDIRECT("A1:A10"))

تابع OFFSET: برای بدست آوردن مقدار یک سلول یا مقادیر یک آرایه که در یک موقعیت نسبت به آدرس داده شده قرار دارند

راهنما

OFFSET(reference,rows,cols,height,width)

مثال: دستور زیر مقدار سلول F5 را بر می گرداند

=OFFSET(C3,2,3,1,1)

یعنی مقدار یک سلول که 2 سطر و 3 ستون با سلول C3 فاصله دارد را برگردان

مثال:دستور زیر  حاصلجمع رنج C2:E4  را بر می گرداند

=SUM(OFFSET(C3:E5,-1,0,3,3))

یعنی جمع مقادیر یک رنج 3*3 که یک سطر بالاتر از رنج C3:E5 باشد

------------------------------

چند مثال:

کد زیر میانگین 10 عدد بزرگ در رنج A1:A60 را بر می گرداند (البته توجه داشته باشید که به توجه به اینکه تابع ROW(1:10) یک array formula است به جای فشردن Enter باید Ctrl+Shift+Enter را بفشارید

=AVERAGE(LARGE(A1:A60,ROW(1:10)))

حال اگر سطری بین سطر 1 تا 10 اضافه کنید اکسل بصورت خودکار فرمول را به زیر تبدیل خواهد کرد:

=AVERAGE(LARGE(A1:A60,ROW(1:11)))

 و این یعنی میانگین 11 عدد بزرگ در رنج A1 تا A60.  برای جلوگیری از این مشکل بهتر است فرمول را به صورت زیر بنویسید:

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

مقاله جامعی درباره array formula را می توانید اینجا بخوانید