آموزش تابع NORMSINV در اکسل

معرفی تابع NORMSINV

تابع NORMSINV در اکسل «معکوس توزیع نرمال استاندارد» را برمی‌گرداند. یعنی اگر به آن یک احتمال (بین 0 و 1) بدهید، خروجی آن مقدار z است که در توزیع نرمال استاندارد (با میانگین 0 و انحراف معیار 1) دقیقاً همان احتمال را در سمت چپ خود دارد.

به زبان ساده‌تر: اگر بگویید «می‌خواهم عددی را پیدا کنم که 95٪ داده‌های نرمال استاندارد پایین‌تر از آن هستند»، NORMSINV همان عدد را (که معمولاً به آن z-score می‌گویند) به شما می‌دهد.

کاربرد رایج: پیدا کردن مقادیر بحرانی در آمار (مثل z برای سطح اطمینان 90٪، 95٪، 99٪)، کنترل کیفیت، تعیین آستانه‌ها، و تبدیل احتمال به مقدار استاندارد.

مثال خیلی ساده: مقدار z برای صدک 95٪ (یعنی 0.95) حدوداً 1.645 است.

=NORMSINV(0.95)

کاربردهای اصلی تابع NORMSINV

• محاسبه مقدار بحرانی z برای سطح اطمینان (Confidence Level)
• به‌دست آوردن صدک‌ها (Quantiles/Percentiles) در توزیع نرمال استاندارد
• تبدیل احتمال (p-value یا cumulative probability) به z-score
• کنترل کیفیت و تعیین حدود هشدار (Threshold) بر اساس احتمال
• مدیریت ریسک و سناریونویسی (مثلاً VaR در حالت ساده و آموزشی)
• تولید داده‌های شبیه‌سازی‌شده نرمال (در ترکیب با RAND و توابع دیگر)

ساختار (Syntax)

=NORMSINV(probability)

=NORMSINV(احتمال)

آرگومان‌ها

probability (احتمال) / مقدار تجمعی (Cumulative Probability)

عدد احتمال باید بین 0 و 1 باشد (0 < probability < 1). این مقدار نشان می‌دهد چند درصد از مساحت زیر نمودار نرمال استاندارد در سمت چپ z قرار می‌گیرد.

مثال‌های ساده و پایه

مثال 1: پیدا کردن z برای صدک 95٪

فرض کنید در سلول A1 مقدار 0.95 را وارد کرده‌اید و می‌خواهید z متناظر را به دست آورید.

=NORMSINV(A1)

نتیجه حدوداً 1.64485 می‌شود. یعنی 95٪ داده‌های نرمال استاندارد کمتر از 1.64485 هستند.

مثال 2: صدک 2.5٪ (دم چپ برای آزمون دوطرفه)

برای آزمون‌های آماری دوطرفه در سطح 5٪، معمولاً از 2.5٪ و 97.5٪ استفاده می‌شود.

=NORMSINV(0.025)

نتیجه حدوداً -1.95996 است (تقریباً -1.96).

مثال 3: گرفتن z از روی درصد واردشده توسط کاربر

اگر کاربر در A1 عدد 95 را به معنی 95٪ وارد کند، باید آن را به 0.95 تبدیل کنید.

=NORMSINV(A1/100)

با A1=95، نتیجه همان حدود 1.64485 خواهد شد.

مثال‌های کاربردی و واقعی

مثال 1: محاسبه آستانه (Threshold) برای یک نمره با میانگین و انحراف معیار مشخص

فرض کنید نمره‌ها نرمال هستند با میانگین 70 و انحراف معیار 10. می‌خواهید حدی را پیدا کنید که 90٪ افراد پایین‌تر از آن هستند (صدک 90٪).

در A1 مقدار 0.9، در B1 میانگین 70 و در C1 انحراف معیار 10 را بگذارید. ابتدا z را با NORMSINV می‌گیریم و سپس به مقیاس واقعی تبدیل می‌کنیم: x = mean + z*stdev

=B1+NORMSINV(A1)*C1

خروجی عددی است که حدود 90٪ افراد نمره‌ای کمتر از آن دارند.

مثال 2: استفاده با AND و OR برای کنترل معتبر بودن احتمال

اگر احتمال در A1 وارد می‌شود، بهتر است قبل از محاسبه بررسی کنید بین 0 و 1 باشد (نه 0 و نه 1). در غیر این صورت خطا می‌گیرید.

=IF(AND(A1>0;A1<1);NORMSINV(A1);"احتمال باید بین 0 و 1 باشد")

مثال 3: تولید عدد نرمال استاندارد از RAND (شبیه‌سازی ساده)

تابع RAND عددی بین 0 و 1 می‌دهد. اگر آن را به NORMSINV بدهید، خروجی یک عدد با توزیع نرمال استاندارد (تقریباً) می‌شود.

=NORMSINV(RAND())

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

مثال 4: ترکیب با COUNTIF برای پیدا کردن صدک از داده‌های واقعی

فرض کنید نمره‌ها در بازه A2:A101 هستند. می‌خواهید آستانه‌ای بسازید که تقریباً 80٪ داده‌ها پایین‌تر از آن باشند، با این فرض که داده‌ها نرمال‌اند. ابتدا میانگین و انحراف معیار را می‌گیرید و سپس صدک 80٪ را محاسبه می‌کنید. بعد می‌توانید بررسی کنید چند داده واقعاً زیر آن افتاده‌اند.

=AVERAGE(A2:A101)+NORMSINV(0.8)*STDEV.S(A2:A101)

فرض کنید این خروجی در D1 است، حالا تعداد مقادیر کمتر یا مساوی آستانه:

=COUNTIF(A2:A101;"<="&D1)

مثال 5: ترکیب با XLOOKUP برای تعیین سطح ریسک از روی z

فرض کنید بعد از محاسبه z می‌خواهید بر اساس بازه‌ها، برچسب بدهید (کم/متوسط/زیاد). جدول راهنما در F2:G5:

F2=-999 ، G2=کم

F3=1 ، G3=متوسط

F4=2 ، G4=زیاد

حالا z در E1 است. (XLOOKUP با حالت approximate match وقتی آرگومان match_mode برابر 1 باشد)

=XLOOKUP(E1;F2:F4;G2:G4;;1)

ترکیب تابع NORMSINV با فرمول‌های دیگر

• ترکیب با IF برای مدیریت خطا و کنترل ورودی

  =IF(AND(A1>0;A1<1);NORMSINV(A1);NA())

• ترکیب با RAND برای شبیه‌سازی متغیر نرمال استاندارد

  =NORMSINV(RAND())

• تبدیل z به مقیاس واقعی با میانگین و انحراف معیار (تولید نرمال با پارامتر دلخواه)

  =Mean+NORMSINV(RAND())*StDev

• ترکیب با AVERAGE و STDEV.S برای ساخت آستانه از روی داده‌های نمونه

  =AVERAGE(A2:A101)+NORMSINV(0.95)*STDEV.S(A2:A101)

• ترکیب با OR برای پیام خطای کاربرپسند

  =IF(OR(A1=1);"مقدار باید بین 0 و 1 باشد";NORMSINV(A1))

خطاهای رایج و روش رفع آنها

1) #NUM!

علت: مقدار probability برابر 0 یا 1 است، یا خارج از بازه مجاز (کمتر یا مساوی 0، یا بزرگتر یا مساوی 1).

راه‌حل: مطمئن شوید ورودی دقیقاً بین 0 و 1 باشد. اگر درصد وارد می‌کنید، بر 100 تقسیم کنید. همچنین می‌توانید با IF و AND ورودی را کنترل کنید.

2) #VALUE!

علت: آرگومان احتمال عددی نیست (مثلاً متن، سلول خالیِ نامناسب، یا رشته‌ای که به عدد تبدیل نمی‌شود).

راه‌حل: فرمت سلول را بررسی کنید، مقدار را عددی وارد کنید، یا از VALUE برای تبدیل متن عددی استفاده کنید.

3) نتیجه غیرمنتظره به خاطر اشتباه گرفتن “احتمال تجمعی” با “سطح معناداری”

علت: کاربر به جای probability، مقدار alpha را مستقیم می‌گذارد (مثلاً 0.05) در حالی که دنبال z سمت راست 5٪ است.

راه‌حل: اگر z برای دم راست می‌خواهید، از 1-alpha استفاده کنید. مثال: برای 5٪ دم راست، probability باید 0.95 باشد.

=NORMSINV(1-0.05)

4) اشتباه در جداکننده آرگومان‌ها

علت: در اکسل فارسی معمولاً جداکننده ; است، اما در منابع انگلیسی , می‌بینید.

راه‌حل: در سیستم‌های فارسی/اروپایی با تنظیمات رایج، از ; استفاده کنید. (NORMSINV فقط یک آرگومان دارد، اما این نکته در ترکیب‌ها مهم می‌شود.)

نکات حرفه‌ای و ترفندهای مهم

• بهتر است از NORM.S.INV استفاده کنید: در اکسل‌های جدید، تابع رسمی‌تر و جایگزین NORMSINV، تابع NORM.S.INV است. NORMSINV ممکن است به عنوان تابع سازگاری (Compatibility) باقی مانده باشد.
• برای آزمون دوطرفه: اگر سطح خطا α دارید، معمولاً به دو طرف تقسیم می‌شود: α/2 و 1-α/2.
• خوانایی بهتر: برای فرمول‌های ترکیبی، احتمال را در یک سلول جدا (مثلاً A1) نگه دارید و نام‌گذاری (Name Manager) انجام دهید تا فرمول‌ها قابل فهم‌تر شوند.
• کنترل ورودی: با Data Validation می‌توانید ورودی را بین 0 و 1 محدود کنید تا خطای #NUM! کمتر شود.
• محاسبات تکرارشونده: فرمول‌هایی مثل NORMSINV(RAND()) فرار (volatile) هستند چون RAND فرار است؛ در فایل‌های سنگین ممکن است باعث کندی شود.

تفاوت تابع NORMSINV با توابع مشابه

• NORMSINV در برابر NORM.S.INV

  NORMSINV همان منطق NORM.S.INV را دارد (معکوس توزیع نرمال استاندارد). تفاوت اصلی در نام‌گذاری و وضعیت پشتیبانی است؛ در نسخه‌های جدید معمولاً NORM.S.INV توصیه می‌شود.

• NORMSINV در برابر NORMINV (یا NORM.INV)

  NORMSINV فقط برای نرمال استاندارد (میانگین 0، انحراف معیار 1) است. اما NORM.INV برای هر میانگین و انحراف معیار کار می‌کند.

• NORMSINV در برابر NORMSDIST (یا NORM.S.DIST)

  NORMSINV «احتمال → z» است (معکوس). اما NORM.S.DIST «z → احتمال» است.

سازگاری با نسخه‌های مختلف اکسل

• تابع NORMSINV در بسیاری از نسخه‌های اکسل وجود دارد، اما در اکسل‌های جدید معمولاً به عنوان تابع سازگاری شناخته می‌شود.
• در Microsoft 365 و Excel 2010 به بعد، پیشنهاد می‌شود از NORM.S.INV استفاده کنید تا فایل شما با استانداردهای جدید و مستندات رسمی هم‌خوان‌تر باشد.
• اگر فایل را برای کاربران با نسخه‌های مختلف می‌فرستید، هر دو معمولاً کار می‌کنند؛ اما اگر جایی با عدم شناسایی مواجه شدید، جایگزینی با NORM.S.INV را تست کنید.

سؤالات پرتکرار درباره تابع NORMSINV

1) آیا NORMSINV همان z-score را می‌دهد؟

بله، خروجی NORMSINV همان مقدار z در توزیع نرمال استاندارد است که احتمال تجمعی داده‌شده را می‌سازد.

2) برای 95٪ سطح اطمینان، چه عددی باید وارد کنم؟

اگر منظور شما صدک 95٪ (یک‌طرفه) است، 0.95. اگر آزمون دوطرفه 95٪ است، معمولاً 0.975 و 0.025 استفاده می‌شود.

3) چرا با 0 یا 1 خطا می‌گیرم؟

چون در توزیع نرمال، رسیدن به احتمال دقیقاً 0 یا 1 به مقدار z بی‌نهایت نیاز دارد؛ اکسل با #NUM! خطا می‌دهد.

4) چطور z را به نمره واقعی تبدیل کنم؟

اگر میانگین و انحراف معیار را دارید: مقدار واقعی = میانگین + z × انحراف معیار.

5) آیا می‌توانم به جای NORMSINV از NORM.INV استفاده کنم؟

اگر داده شما نرمال با میانگین و انحراف معیار مشخص است، بله بهتر است مستقیم از NORM.INV استفاده کنید. اما اگر فقط z استاندارد می‌خواهید، NORMSINV یا NORM.S.INV مناسب‌تر است.

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع NORMSINV ابزار اصلی برای تبدیل «احتمال تجمعی» به «مقدار z» در توزیع نرمال استاندارد است. با آن می‌توانید صدک‌ها، مقادیر بحرانی، آستانه‌های آماری و شبیه‌سازی‌های ساده را انجام دهید. برای استفاده حرفه‌ای‌تر، ورودی‌ها را کنترل کنید و در نسخه‌های جدید اکسل، استفاده از NORM.S.INV را هم در نظر داشته باشید.

پیشنهاد یادگیری بعدی: اگر این تابع را یاد گرفتید، سراغ این‌ها بروید: NORM.S.DIST (محاسبه احتمال از روی z)، NORM.INV (معکوس نرمال با میانگین/انحراف معیار دلخواه)، و همچنین ترکیب‌های آماری با AVERAGE و STDEV.S برای تحلیل داده‌های واقعی.