پویا کردن برنامه در اکسل! (RANK,INDIRECT)

از این که چندروزی به جهت مشغله های مختلف مطلبی نشد بذارم عذرخواهی می کنم.

شاید بارها قصد نوشتن برنامه ای را داشته اید که بتواند برای همه قابل استفاده باشد و بیشترین و بهترین بهره وری را برای کاربر ایجاد نماید. اما مشکل عمده ای که وجود دارد محدود بودن دستوارت در اکسل است، از این رو یا باید به سراغ برنامه نویسی بروید و یا... .

اما مایکروسافت به این موضوع نیز اندیشیدی و راهی برای پویاتر کردن برنامه ها در اکسل باز نموده است.

همانند برنامه نویسی به زبان جاوا که شما می توانید در حین اجرای برنامه متغییرهای جدیدی را تعریف کنید و مقدار دهی نمایید، در اکسل نیز دستوری وجود دارد که این امر را برای شما ممکن می سازد. دستور Indirect. برای این که با طرز کار این دستور آشنا شوید، ابتدا دستور Rank را توضیح می دهم.

دستور رنک با گرفتن یک عدد و یک آرایه به شما رتبه ی عدد را در بین نمرات آرایه ی داده شده بازمی گرداند. اشکال این دستور این است که اگر بخواهیم یک نفر را به آرایه (لیست) خود اضافه کنیم، مجبور خواهیم بود دستور همه ی ستون ها را تغییر دهیم. به مثال زیر دقت کنید:

	A	B	C
1	نمره	رتبه	فرمان داخل ستون رتبه
2	18	3	RANK(A2;A$1:A$6)
3	15	4	RANK(A3;A$1:A$6)
4	19	2	RANK(A4;A$1:A$6)
5	20	1	RANK(A5;A$1:A$6)
6	14	5	RANK(A6;A$1:A$6)

بدیهی است با اضافه کردن چند نمره نیاز به تغییر در همه ی دستورات ستون B می باشد:

	A	B	C
1	نمره	رتبه	فرمان داخل ستون رتبه
2	18	3	RANK(A2;A$1:A$10)
3	15	4	RANK(A3;A$1:A$10)
4	19	2	RANK(A4;A$1:A$10)
5	20	1	RANK(A5;A$1:A$10)
6	14	6	RANK(A6;A$1:A$10)
7	12	9	RANK(A7;A$1:A$10)
8	13	8	RANK(A8;A$1:A$10)
9	14	6	RANK(A9;A$1:A$10)
10	15	4	RANK(A10;A$1:A$10)

نکته) استفاده از $ جهت تعمیم صحیح اطلاعات به ردیف های بعد است (به مطلب معجزه ی $ مراجعه کنید).
اما به نظر می رسد می توان عبارت رنج دستور رنک را با توجه به تعداد داده های ورودی به دست آورد، دستور CONCATENATE به منظور چسباندن چند عبارت به هم به کار می آید:
به مثال زیر دقت کنید:

	A	B	C	D	E
1	نمره	رتبه	فرمان داخل ستون رتبه	تعداد	توابع ستون D
2	18	3	RANK(A2;A$1:A$6)	6	COUNT(A:A)-1
3	15	4	RANK(A3;A$1:A$6)	A2:A6	CONCATENATE("A2:A";D2)
4	19	2	RANK(A4;A$1:A$6)
5	20	1	RANK(A5;A$1:A$6)
6	14	5	RANK(A6;A$1:A$6)

همانگونه که در ستون D می بینید، دستور CONCATENATE امکان اضافه کردن دو عبارت "A2:A" و محتوای سلول D2 را به ما داد، نکته ی حائز اهمیت این که سلول D2 متناسب با تعداد اطلاعات وارد شده در ستون A می باشد، از این رو با افزایش تعداد، عبارت لازم برای رنج رنک به دست می آید.
حال زمان آن رسیده تا پویایی در اکسل را بنگرید!
دستور INDIRECT با گرفتن یک عبارت متنی می تواند آن را به رنج (آرایه) واقعی تبدیل کند و به استفاده ی دیگر توابع برساند:

	A	B	C	D	E
1	نمره	رتبه	فرمان داخل ستون رتبه	تعداد	توابع ستون D
2	18	3	RANK(A2;INDIRECT($D$3))	6	COUNT(A:A)-1
3	15	4	RANK(A3;INDIRECT($D$3))	A2:A6	CONCATENATE("A2:A";D2)
4	19	2	RANK(A4;INDIRECT($D$3))
5	20	1	RANK(A5;INDIRECT($D$3))
6	14	5	RANK(A6;INDIRECT($D$3))

حال بدون تغییر در ردیف های قبل می توانیم تا هر تعداد که بخواهیم، به اضافه کردن ردیف بپردازیم و نمرات جدید را به لیست خود بیافزاییم:

	A	B	C	D	E
1	نمره	رتبه	فرمان داخل ستون رتبه	تعداد	توابع ستون D
2	18	3	RANK(A2;INDIRECT($D$3))	10	COUNT(A:A)-1
3	15	4	RANK(A3;INDIRECT($D$3))	A2:A10	CONCATENATE("A2:A";D2)
4	19	2	RANK(A4;INDIRECT($D$3))
5	20	1	RANK(A5;INDIRECT($D$3))
6	14	6	RANK(A6;INDIRECT($D$3))
7	12	9	RANK(A7;INDIRECT($D$3))
8	13	8	RANK(A8;INDIRECT($D$3))
9	14	6	RANK(A9;INDIRECT($D$3))
10	15	4	RANK(A10;INDIRECT($D$3))

اما هنوز به بهترین حالت نرسیدیم، چون قصد ما اینه که تنها با گرفتن اطلاعات، بدون تغییر در باقی موارد (نهایتا تنها با زدن دکمه ای به نام به روز رسانی) بتوانیم تغییرات لازم را دور از چشم کاربر اعمال کنیم و کاربر تنها خروجی مورد نیاز خود را ببیند. برای این منظور ....
بهتره کمی خودتون فکر کنید، تا پست بعدی (البته راه حل های خودتون رو می تونید تو قسمت نظرات بذارید).

ومن الله التوفیق