#First

🆔️ t.me/ExcelArray

ساخت آرایه شامل اعداد صحیح متوالی


{=ROW (1:12) }

به صورت بهتر و کاملتر، به طوری که با حذف کردن یا ایجاد ردیف جدید بالای فرمول، توالی اعداد فرقی نکند.


{=ROW (INDIRECT ("1:12")) }

🆔️ t.me/ExcelArray

شمارش کاراکتر های موجود در یک دامنه

{=SUM (LEN (A1:A9)) }


🆔️ t.me/ExcelArray

جمع کردن ۳ مورد از کوچکترین مقادیر موجود در یک دامنه.

مثلا دامنه Data

{=SUM (SMALL (Data, {1,2,3} )) }

🆔️ t.me/ExcelArray

کوچکترین عنصر موجود در دامنه Data

=SMALL (Data, 1)


دومین عنصر کوچک

=SMALL (Data, 2)


سومین عنصر کوچک

=SMALL (Data, 3)


🆔️ t.me/ExcelArray

شمارش سلولهای متنی در یک دامنه

{=SUM (IF (ISTEXT (A1:D5),1,0)) }

فرمول کارآمدتر

{=SUM (ISTEXT (A1:D5)*1) }


در واقع همیشه تساوی زیر برقرار است:

TRUE * 1 = 1

FALSE * 1 = 0

🆔️ t.me/ExcelArray

حذف فرمولهای واسطه

ستون B نمرات آزمون اول ۹ نفر از دانش آموزان

ستون C نمرات آزمون دوم ۹ نفر از دانش آموزان

ستون D اختلاف بین ستون B و ستون C میباشد
D=C-B

میانگین تغییرات در ستون D به شکل زیر محاسبه میشود.
=AVERAGE (D1 : D9)

با بکارگیری فرمول آرایه ای زیر میتوان ستون D را حذف کرد.

{ =AVERAGE ( C1 : C9 - B1 : B9) }

فرمول آرایه ای زیر بزرگترین تغییر را محاسبه میکند.
{ = MAX ( C1 : C9 - B1 : B9) }

فرمول آرایه ای زیر کوچکترین تغییر را محاسبه میکند.
{ = MIN ( C1 : C9 - B1 : B9) }


🆔️ t.me/ExcelArray

استفاده از آرایه به جای ارجاع دامنه

اگر فرمول شما از تابعی استفاده میکند که نیازمند ارجاع دامنه است، می‌توان از یک ثابت آرایه ای به جای ارجاع دامنه استفاده کرد.

= VLOOKUP ( B1, D1 : E3, 2, FALSE)

فرمول آرایه ای

= VLOOKUP ( B1, {1, "One"; 2, "Two"; 3, "Three" }, 2, FALSE)


🆔️ t.me/ExcelArray

جمع مقادیر دامنه ای که حاوی مقادیر خطاست

{ = SUM ( IFERROR (D2 : D9, "") ) }


این تکنیک روی توابع دیگر مانند:

MAX , MIN , AVERAGE

نیز پاسخ میدهد.

روش دیگر استفاده از تابع AGGREGATE است.

= AGGREGATE ( 9, 2, D2 : D9)

آرگومان اول (9)، کد مربوط به تابع SUM و آرگومان دوم (2)، کد مربوط به نادیده گرفتن مقادیر خطاست.

تابع AGGREGATE برای محاسبه میانگین، حداقل، حداکثر و ... نیز میباشد.

🆔️ t.me/ExcelArray

شمارش تعداد مقادیر خطای موجود در یک دامنه

{ = SUM ( ISERROR (DATA) , 1 , 0 )) }

میتوان با حذف آرگومان سوم تابع IF فرمول را به شکل ساده‌تری نوشت، تابع IF در صورتی که شرط تحقق نیافته باشد، یعنی سلول حاوی مقدار خطا نباشد مقدار FALSE را بر می گرداند و اکسل FALSE را به عنوان مقدار صفر در نظر می‌گیرد.

{ = SUM ( IF ( ISERROR (DATA), 1 ) ) }

فرمول فوق را می‌توان حتی به شکل ساده تر هم نوشت:

{ = SUM ( ISERROR (DATA) * 1 ) }

چون تساوی زیر همیشه برقرار هستند.

TRUE * 1 = 1

FALSE * 1 = 0


🆔️ t.me/ExcelArray

جمع کردن n تعداد از بزرگترین مقادیر موجود در یک دامنه


{ = SUM ( LARGE ( DATA, ROW (INDIRECT ( "1 : 9" ) ) ) ) }


🆔️ t.me/ExcelArray

372

محاسبه مقدار میانگین دامنه DATA بدون احتساب مقادیر صفر

{ = AVERAGE ( IF ( DATA <> 0, DATA )) }

همچنین میتوان با فرمول معمولی و غیر آرایه ای زیر هم به نتیجه مشابه دست پیدا کرد.

= SUM (DATA) / COUNTIF (DATA, "<>0")


🆔️ t.me/ExcelArray

373

ActiveWorkbook.Name.Add Name:="Data1", RefersTo:=Sheet1.Range("A1:B5")



ماکرو ایجاد یک محدوده یا رنج به نام Data1