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

ستون 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) }


🆔️ www.tgoop.com/ExcelArray

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

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

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

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

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


🆔️ www.tgoop.com/ExcelArray

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

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


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

MAX , MIN , AVERAGE

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

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

= AGGREGATE ( 9, 2, D2 : D9)

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

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

🆔️ www.tgoop.com/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


🆔️ www.tgoop.com/ExcelArray

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


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


🆔️ www.tgoop.com/ExcelArray

372

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

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

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

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


🆔️ www.tgoop.com/ExcelArray

373

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



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

فرمولهایی که عمل شمارش "Count" و جمع "Sum" را انجام میدهند.

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



=Rows(Data)*Columns(Data)


شمارش سلولهای تهی

=CountBlank(Data)


=CountBlank(A:A)

سلولهای خالی در کاربرگی به نام Sheet1


=CountBlank(Sheet1!1:1,048,576)


شمارش سلولهای غیر تهی

=CountA(Data)


شمارش سلولهای عددی

=Count(Data)

سلولهایی شامل مقادیر تاریخ و ساعت نیز عددی محسوب می شوند.

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

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


شمارش سلولهای غیر متنی

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


شمارش مقادیر منطقی

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


شمارش مقادیر خطا در یک دامنه

سه تابع زیر به کاربر کمک میکند

ISERROR
اگر سلول حاوی یکی از مقادیر خطاهای زیر باشد
#N/A
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
مقدار TRUE را برمیگرداند.

ISERR

اگر سلول حاوی هر مقدار خطا به جز N/A# باشد، مقدار TRUE را برمیگرداند.


ISNA

اگر سلول حاوی مقدار خطای N/A# باشد، مقدار TRUE را برمیگرداند.

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

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

اگر بخواهید انواع خاصی از مقادیر خطا را شمارش کنید از تابع COUNTIF استفاده کنید.

=COUNTIF(DATA,"#DIV/0!")

فرمولهای پیشرفته شمارشی

شمارش سلولها با استفاده از تابع COUNTIF


تعداد سلولهایی که حاوی عدد 12 هستند

=COUNTIF(DATA,12)

تعداد سلولهایی که حاوی یک مقدار منفی هستند

=COUNTIF(DATA,"<0")


تعداد سلولهایی که مساوی صفر نیستند

=COUNTIF(DATA,"<>0")


تعداد سلولهایی که حاوی مقدار عددی بزرگتر از 5 هستند

=COUNTIF(DATA,">5")

تعداد سلولهایی که محتویات آنها مساوی با محتویات سلول A1 است

=COUNTIF(DATA,A1)


تعداد سلولهایی که محتویات آنها بزرگتر از مقدار سلول A1 است

=COUNTIF(DATA,">"&A1)


تعداد سلولهایی که حاوی مقدار متنی هستند

=COUNTIF(DATA,"*")


تعداد سلولهایی که دقیقا حاوی 3 کاراکتر هستند

=COUNTIF(DATA,"???")


تعداد سلولهایی که فقط حاوی کلمه EXCEL هستند (صرف نظر از بزرگ و کوچک بودن حروف)

=COUNTIF(DATA,"EXCEL")


تعداد سلولهایی که در جایی از متن آنها، کلمه Iran قرار دارد
=COUNTIF(DATA,"*Iran*")

تعداد سلولهایی که حاوی متنی هستند که با حرف A شروع میشوند، صرف نظر از بزرگ و کوچک بودن حروف
=COUNTIF(DATA,"A*")

تعداد سلولهایی که حاوی تاریخ فعلی هستند

=COUNTIF(DATA,TODAY())


تعداد سلولهایی که حاوی مقدار عددی بزرگتر از مقدار میانگین مقادیر هستند

=COUNTIF(DATA,">"&AVERAGE(DATA))


تعداد سلولهایی که حاوی مقادیر 3 واحد بیشتر از انحراف استاندارد نسبت به مقدار میانگین هستند


=COUNTIF(DATA,">"&AVERAGE(DATA)+STDEV(DATA)*3)

تعداد سلولهایی که حاوی مقادیر عددی 3 یا 3- هستند

=COUNTIF(DATA,3)+COUNTIF(DATA,-3)


تعداد سلولهایی که حاوی عبارت منطقی TRUE هستند.

=COUNTIF(DATA,TRUE)


تعداد سلولهایی که حاوی مقادیر منطقی (TRUE یا FALSE) هستند.

=COUNTIF(DATA,TRUE)+COUNTIF(DATA,FALSE)

تعداد سلولهایی که حاوی مقدار خطای N/A# هستند
=COUNTIF(DATA,"#N/A")

303

مرتب کردن مجموعه DATA به صورت صعودی در محدوده ای دیگر


=LARGE(DATA,ROW(INDIRECT("1:"&ROWS(DATA))))

در انتها کلیدهای {کنترل + شیفت + اینتر}

{ Ctrl + Shift + Enter }

با هم زده شود.


لازم به ذکر است هنگام درج فرمول باید به اندازه ردیف های Data همان تعداد ردیف در ستون دیگر انتخاب شود و در فرمول بار عملیات نوشته شود.

شمارش سلولهای یک دامنه که طول مشخصی دارند.
مثلا دامنه Data

=SUMPRODUCT(N(LEN(DATA)=9))

هایپرلینک
=HYPERLINK(" #' " & Sheet Name & " ' !" A1(آدرس درج شده) , "نام دلخواه")


=HYPERLINK("#'"&J1&"'!"&A1,"GO")

Hyperlink pictures

جواب خیلی از سوالات رو اینجا بگیرید.👇👇



👉 🆔️ www.tgoop.com/ExcelArray 👈



تکنیک‌های حرفه ای اکسل که تا حالا جای دیگه ندیدی.👇👇


👉 🆔️ www.tgoop.com/ExcelArray 👈

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

{=SUM(IFERROR(A1:A5,0))}

✅ ble.ir/join/OGM0ZmIwZj


✅ ble.ir/ers1400


👌 با کلیک بانک همیشه یک فدم جلوتر باشید.