تابع 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 را می توانید اینجا بخوانید
