آموزش تابع NORM.INV در اکسل

معرفی تابع NORM.INV

تابع NORM.INV در اکسل «معکوس توزیع نرمال» (Normal Distribution) را محاسبه می‌کند. یعنی به‌جای اینکه بپرسیم «احتمال اینکه مقدار از یک عدد کمتر شود چقدر است؟»، برعکس می‌پرسیم: «اگر یک احتمال مشخص (مثل 0.95) داشته باشیم، مقدار متناظر روی محور توزیع نرمال چقدر می‌شود؟»

به زبان ساده: NORM.INV از روی احتمال، مقدار (X) را برمی‌گرداند؛ با فرض اینکه داده‌ها توزیع نرمال با میانگین و انحراف معیار مشخص دارند.

کاربردهای رایج آن شامل محاسبه آستانه‌ها (Threshold)، تعیین حدود کنترل، تعیین نمره خام بر اساس صدک (Percentile) و تحلیل ریسک است.

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

=NORM.INV(0.9;70;10)

خروجی تقریباً 82.82 می‌شود؛ یعنی حدوداً صدک 90 برابر 82.82 است.

کاربردهای اصلی تابع NORM.INV

• محاسبه صدک‌ها (Percentiles) برای داده‌های نرمال (مثلاً صدک 95 یا 99)
• تعیین حد آستانه قبولی/ردی در کنترل کیفیت (Quality Control)
• تعیین حدود کنترل در نمودارهای کنترلی (مثل حدود 3 سیگما)
• تحلیل ریسک و تعیین Value at Risk (VaR) در مدل‌های ساده مالی
• شبیه‌سازی داده‌های نرمال (با ترکیب RAND و NORM.INV)
• تبدیل احتمال (Probability) به مقدار متناظر (Quantile) در مدل‌های آماری

ساختار (Syntax)

ساختار انگلیسی:

=NORM.INV(probability, mean, standard_dev)

ساختار با توضیح فارسی آرگومان‌ها (در اکسل فارسی معمولاً جداکننده «;» است):

=NORM.INV(احتمال;میانگین;انحراف_معیار)

آرگومان‌ها

probability / احتمال

عددی بین 0 و 1 است (مثلاً 0.9 یعنی صدک 90). این مقدار نشان می‌دهد چه سهمی از داده‌ها «کمتر یا مساوی» خروجی خواهند بود.

mean / میانگین

میانگین توزیع نرمال. خروجی حول این مقدار مرکزیت دارد.

standard_dev / انحراف معیار

انحراف معیار توزیع نرمال و باید عدد مثبت باشد. هرچه بزرگ‌تر باشد پراکندگی بیشتر است.

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

مثال 1: محاسبه صدک 95 برای یک فرآیند

فرض کنید زمان پاسخ یک سیستم نرمال با میانگین 120 میلی‌ثانیه و انحراف معیار 15 است. می‌خواهیم صدک 95 را پیدا کنیم.

در اکسل:

• A2 = 0.95
• B2 = 120
• C2 = 15

=NORM.INV(A2;B2;C2)

نتیجه حدوداً 144.67 می‌شود. یعنی 95% زمان‌ها زیر حدود 144.67 میلی‌ثانیه است.

مثال 2: پیدا کردن یک آستانه برای 10% پایین

اگر بخواهید مرز 10% پایین را پیدا کنید (صدک 10):

=NORM.INV(0.1;120;15)

خروجی حدوداً 100.78 می‌شود.

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

مثال 1: تعیین «حد هشدار» در کیفیت (ترکیب با IF)

فرض کنید وزن محصول نرمال با میانگین 500 گرم و انحراف معیار 8 گرم است. می‌خواهیم حدی تعیین کنیم که فقط 1% محصولات پایین‌تر از آن باشند (صدک 1). سپس اگر وزن اندازه‌گیری‌شده کمتر از این حد بود، هشدار بدهیم.

ورودی‌ها:

• B2 = میانگین = 500
• C2 = انحراف معیار = 8
• D2 = وزن اندازه‌گیری شده (مثلاً 485)

محاسبه حد صدک 1:

=NORM.INV(0.01;B2;C2)

هشدار با IF:

=IF(D2<NORM.INV(0.01;B2;C2);"هشدار: خیلی کم";"نرمال")

به این شکل، یک آستانه آماری واقعی دارید، نه یک عدد حدسی.

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

اگر بخواهید داده‌های تصادفی با توزیع نرمال تولید کنید (مثلاً برای تست):

=NORM.INV(RAND();70;10)

اگر خروجی را رُند هم بخواهید (مثلاً 2 رقم اعشار):

=ROUND(NORM.INV(RAND();70;10);2)

مثال 3: استخراج میانگین و انحراف معیار از داده‌ها و سپس محاسبه صدک (ترکیب با AVERAGE و STDEV.S)

فرض کنید نمرات در بازه A2:A101 است و می‌خواهید صدک 90 را بر اساس توزیع نرمال برآورد کنید:

=NORM.INV(0.9;AVERAGE(A2:A101);STDEV.S(A2:A101))

مثال 4: تعیین آستانه بر اساس سطح ریسک انتخابی (ترکیب با XLOOKUP)

فرض کنید در جدول F2:G6 سطح ریسک را به احتمال تبدیل کرده‌اید:

• F2:F6 = سطح ریسک (مثلاً “کم”، “متوسط”، “زیاد”)
• G2:G6 = احتمال متناظر (مثلاً 0.9، 0.95، 0.99)

در B2 سطح ریسک انتخاب شده (مثلاً “زیاد”)، میانگین در C2 و انحراف معیار در D2 است. آستانه را حساب می‌کنیم:

=NORM.INV(XLOOKUP(B2;F2:F6;G2:G6);C2;D2)

ترکیب تابع NORM.INV با فرمول‌های دیگر

• با IF برای هشدار/طبقه‌بندی بر اساس صدک‌ها

  =IF(A2>NORM.INV(0.95;B2;C2);"بالاتر از حد 95%";"عادی")

• با RAND برای تولید داده تصادفی نرمال

  =NORM.INV(RAND();B2;C2)

• با AVERAGE و STDEV.S برای برآورد پارامترها از داده واقعی

  =NORM.INV(0.99;AVERAGE(A2:A100);STDEV.S(A2:A100))

• با AND و OR برای کنترل چند شرط هم‌زمان (مثلاً بازه مجاز بین صدک 5 و 95)

  =IF(AND(A2>=NORM.INV(0.05;B2;C2);A2<=NORM.INV(0.95;B2;C2));"داخل بازه";"خارج از بازه")

• با COUNTIF برای شمارش مقادیری که از یک آستانه صدکی بالاتر/پایین‌ترند

  =COUNTIF(A2:A100;">"&NORM.INV(0.95;AVERAGE(A2:A100);STDEV.S(A2:A100)))

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

1) خطای #NUM! به خاطر probability نامعتبر

اگر probability کمتر یا مساوی 0 یا بزرگ‌تر یا مساوی 1 باشد، اکسل خطای #NUM! می‌دهد. probability باید بین 0 و 1 باشد (مثلاً 0.001، 0.5، 0.95).

2) خطای #NUM! به خاطر standard_dev صفر یا منفی

انحراف معیار باید حتماً مثبت باشد. اگر داده ندارید یا STDEV.S روی داده کم/نامعتبر محاسبه شده، ابتدا ورودی‌ها را بررسی کنید.

3) خطای #VALUE! به خاطر ورودی متنی

اگر یکی از آرگومان‌ها متن باشد (مثلاً “0.9” به شکل متن، یا سلول شامل کاراکتر اضافی)، اکسل #VALUE! می‌دهد. فرمت سلول را Number کنید و مطمئن شوید مقدار واقعاً عددی است.

4) نتیجه «غیرمنتظره» به خاطر استفاده اشتباه از درصدها

گاهی کاربر 90% را به صورت 90 وارد می‌کند، در حالی که اکسل انتظار 0.9 دارد. اگر در سلول 90% وارد کنید، اکسل آن را 0.9 در نظر می‌گیرد؛ اما اگر 90 تایپ کنید، اشتباه است. پس یا 0.9 بنویسید یا 90% را با فرمت درصد وارد کنید.

5) مشکل جداکننده آرگومان‌ها (، یا 😉

در برخی سیستم‌ها جداکننده «;» است و در برخی «,». اگر فرمول خطا می‌دهد، جداکننده را مطابق تنظیمات منطقه‌ای اکسل تغییر دهید.

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

• برای خوانایی، ورودی‌ها را در سلول‌های جدا بگذارید (probability، mean، standard_dev) و فرمول را روی سلول‌ها بنویسید تا هم قابل فهم‌تر شود هم خطا کمتر شود.
• برای جلوگیری از خطا، ورودی probability را کنترل کنید (مثلاً مطمئن شوید بین 0 و 1 است) و سپس NORM.INV را اجرا کنید.
• اگر با داده واقعی کار می‌کنید، معمولاً mean و standard_dev را با AVERAGE و STDEV.S از همان داده‌ها به‌دست بیاورید.
• برای شبیه‌سازی پایدار (عدم تغییر با هر محاسبه)، به جای RAND از یک ستون اعداد ثابت (مثلاً اعداد تصادفی Paste Values) استفاده کنید.
• در تحلیل کنترل کیفیت، به‌جای محاسبه دستی حدود، می‌توانید از NORM.INV برای حد پایین/بالا استفاده کنید (مثل صدک‌های 0.001 و 0.999 یا 0.025 و 0.975).

تفاوت تابع NORM.INV با توابع مشابه

• NORM.INV: معکوس توزیع نرمال با میانگین و انحراف معیار دلخواه (خروجی X از روی احتمال).
• NORM.DIST: خودِ توزیع نرمال را می‌دهد (خروجی احتمال/چگالی از روی X).
• NORM.S.INV: معکوس توزیع نرمال استاندارد (میانگین 0 و انحراف معیار 1). وقتی داده‌ها Z-Score هستند مناسب‌تر است.
• NORM.S.DIST: توزیع نرمال استاندارد (احتمال/چگالی از روی Z).

اگر با Z-Score کار می‌کنید، معمولاً NORM.S.INV ساده‌تر است. اگر میانگین و انحراف معیار واقعی دارید، NORM.INV گزینه درست است.

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

• تابع NORM.INV در نسخه‌های جدید اکسل (Excel 2010 به بعد) وجود دارد.
• در نسخه‌های خیلی قدیمی‌تر، تابع معادل معمولاً NORMINV بوده است. (نام‌های قدیمی در برخی نسخه‌ها برای سازگاری همچنان کار می‌کنند.)
• در Excel برای Microsoft 365 نیز این تابع کاملاً پشتیبانی می‌شود و با توابع جدید به‌خوبی ترکیب می‌شود.

سؤالات پرتکرار درباره تابع NORM.INV

1) آیا probability می‌تواند 0 یا 1 باشد؟

خیر. باید بین 0 و 1 باشد. مقدار 0 یا 1 باعث خطای #NUM! می‌شود.

2) اگر داده‌های من نرمال نباشند، باز هم NORM.INV به درد می‌خورد؟

اگر توزیع داده‌ها نرمال نباشد، خروجی می‌تواند گمراه‌کننده باشد. بهتر است ابتدا نرمال بودن را بررسی کنید یا از روش‌های غیرپارامتری/صدک واقعی استفاده کنید.

3) فرق NORM.INV با PERCENTILE چیست؟

PERCENTILE روی داده‌های واقعی صدک را از خود داده‌ها می‌دهد؛ اما NORM.INV صدک را با فرض نرمال بودن و بر اساس mean و standard_dev محاسبه می‌کند.

4) چطور صدک 95 را وارد کنم: 0.95 یا 95%؟

هر دو درست است، به شرطی که اکسل آن را 0.95 برداشت کند. اگر 95% تایپ کنید معمولاً درست است؛ اگر 95 تایپ کنید اشتباه است.

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

تابع NORM.INV برای وقتی عالی است که بخواهید از روی یک احتمال/صدک، مقدار متناظر را در یک توزیع نرمال با میانگین و انحراف معیار مشخص پیدا کنید. این تابع در کنترل کیفیت، تحلیل ریسک، تعیین آستانه‌های آماری و شبیه‌سازی داده‌ها کاربرد زیادی دارد و وقتی با توابعی مثل IF، RAND، AVERAGE، STDEV.S و XLOOKUP ترکیب شود بسیار قدرتمند می‌شود.

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

• NORM.DIST برای محاسبه احتمال از روی مقدار
• NORM.S.INV و مفهوم Z-Score
• PERCENTILE.INC و QUARTILE برای صدک‌گیری مستقیم از داده واقعی
• روش‌های ساده بررسی نرمال بودن داده‌ها (مثل نمودار هیستوگرام و Q-Q)