محاسبه میانگین در اکسل – ۱۰ روش کاربردی + فرمول و دستور

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

اگر به تازگی به کار با داده‌ها مشغول شده‌اید و عمدتا با اکسل سر و کار دارید، می‌توانید با مطالعه مطلب «چگونه اکسل یاد بگیریم؟ – راهنمای یادگیری آسان و سریع» مسیر درستی را برای یادگیری این نرم‌افزار بسیار کاربردی، برای خود ترسیم کنید.

میانگین چیست؟

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

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

فرمول تابع Average در اکسل

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

1=AVERAGE(number1, [number2],...)

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

همان‌طور که گفتیم، این تابع می‌تواند تا 255 عدد، آرایه، ارجاع سلولی و موارد مشابه را بپذیرد، اما فقط وارد کردن یک آرگومان در آن ضروری است. اما معمولا اگر قصد محاسبه میانگین در اکسل را داشته باشید، حداقل دو عدد در اختیار دارید. در این شرایط ابتدا عمل جمع در اکسل روی این دو مقدار انجام می‌شود و عدد به دست آمده، بر دو تقسیم خواهد شد.

بیایید با مثال‌های ساده، این تابع را بررسی کنیم. در فرمول زیر، Average را برای محاسبه میانگین سه عدد 100، 95 و 80 مورد استفاده قرار داده‌ایم.

1=AVERAGE(100,95,80)

در ادامه از ارجاع سلولی برای محاسبه میانگین در اکسل استفاده کرده‌ایم:

1=AVERAGE(B2:B5)

می‌توانید این فرمول را در سلول نتیجه یا داخل کادر مربوط به فرمول بنویسید. همچنین می توانید محدوده سلولی مورد نظر را انتخاب و شروع به نوشتن فرمول کنید. برای این کار نیز باید تمام اعداد را از اول تا انتهای محدوده داده، انتخاب کرده باشید.

برای محاسبه میانگین سلول‌های غیر هم‌جوار (سلول‌هایی که کنار یکدیگر نیستند)، کافی‌ست هنگام انتخاب سلول‌ها دکمه Ctrl یا Command (برای کاربران مک) را فشار دهید و نگه دارید.

همچنین می‌توانید از ارجاع سلولی استفاده کنید. در این شرایط، نام سلول‌های غیر هم‌جوار باید به کمک علامت کاما (,) از هم جدا شوند، مانند فرمول زیر:‌

1=AVERAGE(B2:B5,B8)

ملاحظات مهم راجع به تابع Average

بهتر است زمانی که تابع Average را برای محاسبه میانگین در اکسل به کار می‌گیرید، موارد زیر را در ذهن داشته باشید:

• مقادیر متنی و سلول‌های خالی، در این تابع نادیده گرفته می‌شوند.: به عنوان مثال، وجود کلمه «غایب» در سلول ‌‌B6 در مثال پایین، سبب می‌شود تابع Average این سلول را به طور کلی نادیده بگیرد. به این ترتیب، میانگین محاسبه شده در سلول I6 تنها سلول‌های C2 تا H8 را شامل می‌شود و عدد جمع به جای 7 بر 6 تقسیم شده است.

• مقادیر صفر، حساب می‌شوند: مقادیر صفر، در جمع اعداد برای محاسبه میانگین، حساب می‌شوند و در نهایت مقدار میانگین را پایین می‌آورند. چون عملا تاثیری در مقدار جمع نداشته‌اند اما در تقسیم محاسبه می‌شوند. توجه کنید که معدل دانش‌آموز شماره 10 با معدل دانش‌آموز شماره 5 بسیار متفاوت است، در حالی که نمراتی مشابه یکدیگر دارند. به همین دلیل می‌توانید برای اطمینان از درستی داده‌ها از Data Validation در اکسل استفاده کنید.

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

برای حل مشکلی که بالاتر به آن اشاره شد، از تابع AVERAGEA استفاده می‌کنیم که تمام مقادیر، از جمله متن را در محاسبه دخیل می‌کند. فرمول این تابع برای محاسبه میانگین در اکسل به شکل زیر است:‌

1=AVERAGEA(value1, [value2],...)

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

تابع AVERAGEA مقادیر متنی را به عنوان صفر در نظر می‌گیرد. در حالی که مقدار منطقی TRUE برابر 1 ارزیابی می‌شود. مقدار منطقی FALSE برابر 0 است.

می‌توانید تفاوت استفاده از AVERAGE و AVERAGEA را در تصویر زیر ببینید:

با استفاده از تابع AVERAGEA معدل دو دانش‌آموز شماره 10 و 5، بسیار به هم نزدیک‌تر شده است. همچنین کافی است میانگین یک سلول را به دست بیاورید بعد به کمک Flash Fill در اکسل، آن را به سایر سلول‌ها بسط دهید.

تابع AVERAGEIF برای محاسبه میانگین در اکسل

تابع AVERAGEIF برای محاسبه میانگین اعدادی است که معیار یا شرط خاصی را برآورده می‌کنند. به کمک این تابع، اکسل در بین محدوده داده‌ها جستجو می‌کند تا سلول‌هایی را بیاید که مطابق شرط هستند. سپس میانگین این اعداد را محاسبه خواهد کرد. فرمول تابع AVERAGEIF مانند زیر است:

1=AVERAGEIF(range,criteria,[average_range])

• Range یا محدوده، مکانی است که می‌خواهیم شرط را در آن جستجو کنیم.
• Critria شرط یا معیاری است که بر اساس آن جستجو می‌کنیم.
• Average_range نیز آرگومان اختیاری این فرمول است. یعنی محدوده‌ای که داده‌های مورد نیاز برای محاسبه میانگین، در آنجا قرار دارند. اگر این آرگومان را وارد نکنیم، مقدار موجود در آرگومان Range به جای آن در نظر گرفته خواهد شد.

مثال اول برای تابع AVERAGEIF

در این مثال لیست تعداد سبد میوه‌های موجود در یک فروشگاه را داریم. می‌خواهیم به کمک تابع AVERAGEIF سلول‌هایی با نام «سیب» را در ستون A استخراج و میانگین تعداد سبدهای موجود را در ستون B حساب کنیم. فرمول مناسب برای این کار را در تصویر زیر می‌بینید.

مثال دوم برای تابع AVERAGEIF

معیار یا شرطی که برای محاسبه میانگین در اکسل با AVERAGEIF در نظر می‌گیریم، می‌تواند یک عملگر منطقی نیز باشد. مثلا فرمول زیر حاوی یک عملگر منطقی به عنوان شرط است:‌

1=AVERAGEIF(B4:H4,"<>0")

این فرمول، میانگین اعداد محدوده B4:H4 را حساب می‌کند، به شرطی که اعداد صفر نباشند. یعنی هر عددی به غیر از صفر را در میانگین حساب خواهد کرد. توجه داشته باشید که در این فرمول آرگومان سوم که اختیاری است، حذف شده، بنابراین از سلول‌های موجود در آرگومان محدوده برای محاسبه میانگین استفاده شده است.

از آنجا که در فرمول مذکور، اعداد صفر در میانگین محاسبه نمی‌شوند، تفاوت بین معدل دو دانش‌آموز شماره 10 و 5، مانند تصویر زیر می‌شود:

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

تابع MEDIAN برای محاسبه میانگین در اکسل

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

• مطالب پیشنهادی برای مطالعه: 
  • پنهان کردن خطاها در اکسل — به زبان ساده + فیلم آموزشی رایگان
  • حذف ردیف های خالی در اکسل | به زبان ساده (+ دانلود فیلم آموزش رایگان)
  • تقسیم در اکسل — از صفر تا صد
  • آدرس نسبی و مطلق سلول در فرمول اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)
  • جمع شرطی در اکسل – آموزش تابع و فرمول به زبان ساده

برای مثال، مجموعه اعداد زیر را در نظر بگیرید، می‌توانید از ویژگی تولید عدد تصادفی در اکسل نیز برای ایجاد چند عدد استفاده کنید:

18، 25، 22، 19، 19، 100، 18، 16، 15، 21، 14، 1، 22

محاسبه میانگین حسابی این اعداد، ما را به عدد 23.8 می‌رساند.

با این حال اگر نگاه دیگری به اعداد بیندازیم، سخت می‌شود باور کرد که 23.8، میانگین آن‌ها باشد. درواقع عدد 100 یک عدد پرت است که مجموع این عددها را افزایش داده.

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

1=MEDIAN(number1, [number2], …)

استفاده از ارجاعات سلولی و محدوده داده‌ها نیز به جای عدد قابل قبول است. به عنوان مثال مجموعه داده زیر را در نظر می‌گیریم:

100، 25، 22، 22، 21، 19، 19، 18، 18، 16، 15، 14، 0

13 عدد در این مجموعه وجود دارد، یعنی هفتمین عدد، وسط آن‌ها است. پس میانگین این مجموعه عدد، 19 است.

اگر تعداد اعداد یک مجموعه زوج باشد، وسط آن‌ها به کمک جمع دو عدد وسطی و تقسیم آن‌ها بر دو به دست می‌آید. به این ترتیب، وسط مجموعه اعداد «15، 14، 11، 9، 9، 7» می‌شود: (9+11)/2=10  .

تابع MEDIAN سلول‌های حاوی متن، مقادیر منطقی و سلول‌های خالی را نادیده می‌گیرد.

تابع MODE برای محاسبه میانگین در اکسل

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

سه نوع تابع MODE در اکسل وجود دارد، فرمول تابع کلاسیک را در ادامه می‌بینیم:

1=MODE(number1, [number2],...)

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

البته ممکن است در برخی موقعیت‌ها، بیشتر از یک عدد برای MODE داشته باشیم. مثلا لیست زیر را در نظر بگیرید:

100، 25، 22، 22، 21، 19، 19، 18، 18، 16، 15، 14، 1

سه عدد 18، 19 و 22 هر کدام دو بار تکرار شده‌اند. کدام یک از آن‌ها MODE محسوب می‌شود؟ مایکروسافت، در این شرایط، اولین عددی که بیشتر تکرار شده است را به عنوان MODE در نظر می‌گیرد که در این مثال عدد 18 است.

اگر همین اعداد به طور برعکس چیده شده بودند، عدد 22 به عنوان MODE در نظر گرفته می‌شد.

همچنین در شرایطی که اعداد به صورت تصادفی در کنار یکدیگر قرار بگیرند، اکسل بر اساس اینکه کدام عدد قبل‌تر بوده است، MODE را انتخاب می‌کند. برای مثال، لیست زیر را در نظر بگیرید:

18، 25، 22، 19، 100، 18، 16، 15، 21، 14، 0، 22، 19

تابع MODE عدد 19 را نشان خواهد داد.

تابع MODE.MULT

این تابع راه‌حلی برای مشکل بالا است. MODE.MULT این امکان را به ما می‌دهد که احتمال وجود بیش از یک MODE را در گروه پیش‌بینی کرده و در نظر بگیریم. فرمول این تابع به شکل زیر نوشته می‌شود:

1=MODE.MULT(number1, [number2],...)

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel

کلیک کنید

از آنجا که MODE.MULT یک تابع آرایه است، برای استفاده از آن باید مراحل زیر را طی کنیم:

• ابتدا یک محدوده عمودی به عنوان خروجی انتخاب می‌کنیم.
• سپس فرمول MODE.MULT را می‌نویسیم.
• در نهایت برای نمایش نتیجه نهایی، ترکیب دکمه‌های Control + Shift + Enter را فشار می‌دهیم.

فشردن همزمان این سه دکمه باعث می‌شود تا اکسل دو علامت قلاب ({}) دور فرمول قرار دهد و نتیجه را برابر با سلول‌های انتخاب شده در مرحله اول برگرداند. اگر بیشتر از یک MODE وجود داشته باشد، آن‌ها به صورت عمودی در سلول‌های نتیجه نشان داده خواهند شد. همچنین این تابع در شرایط زیر، خطای N/A# را نشان می‌دهد:

• هیچ مقادیر همسانی وجود نداشته باشد.
• هیچ MODE اضافی‌ای در محدوده خروجی وجود نداشته باشد.

تابع MODE.SNGL

این تابع نیز مانند MODE.MULT از نسخه اکسل 2010 به بعد در این برنامه گنجانده شده و فرمول‌ آن به شکل زیر است:‌

1=MODE.SNGL(number1, [number2],...]

تابع MODE.SNGL نیز در تشخیص خروجی درست مانند تابع MODE کلاسیک عمل می‌کند.

استفاده خلاقانه از تابع AVERAGE

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

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

ما می‌توانیم دو تابع AVERAGE و LARGE را با هم ترکیب کنیم تا میانگین را از بین nامین عدد، تعیین کنیم.

تابع LARGE با فرمول زیر، nامین عدد موجود در یک محدوده را حساب می‌کند:

1=LARGE(array, k)

در این فرمول، آرگومان «k» درواقع همان nامین عدد است.

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

برای محاسبه میانگین سه مورد از بزرگ‌ترین اعداد در یک محدوده، باید دو تابع AVERAGE و LARGE را با هم ترکیب کنیم. در نهایت فرمول ما به شکل زیر خواهد شد:

1=AVERAGE(LARGE(array, {1,2,3}))

با گذاشتن قلاب ({}) دور آرگومان k در این فرمول، اکسل اولین، دومین و سومین عدد بزرگ را در این آرایه در نظر خواهد گرفت و سپس تابع AVERAGE، میانگین آن سه را حساب می‌کند.

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

می‌توانیم از همین منطق برای یافتن سه عدد کوچک‌تر در یک محدوده با تابع SMALL استفاده کنیم. فرمولی که در ادامه می‌بینید، میانگین بین سه عدد کوچک‌تر آرایه را حساب می‌کند:‌

1=AVERAGE(SMALL(array, {1,2,3}))

فیلم آموزش کاربرد اکسل در حسابداری و مدیریت در فرادرس

کلیک کنید

پاسخ به چند سوال رایج

در ادامه به چند سوال که در زمینه میانگین‌گیری در اکسل رایج هستند، پاسخ داده‌ایم.

محاسبه میانگین در اکسل بدون صفر چگونه است؟‌

تابع AVERAGE، صفر را در میانگین‌‌گیری حساب می‌کند. وجود صفر نیز سبب پایین آمدن مقدار نهایی میانگین خواهد شد. برای رفع این مشکل می‌توانید از تابع AVERAGEIF به همراه شرط منطقی استفاده کنید. کافی است شرط عدم برابری با صفر (0<>) را در فرمول این تابع قرار دهید.

پیدا کردن وسط اعداد در اکسل چگونه انجام می‌شود‌؟

تابع MEDIAN در اکسل، میانگین را با به دست آوردن وسط مجموعه‌ای از اعداد حساب می‌کند. اگر تعداد عددها فرد باشد، عدد میانی در خروجی این تابع نشان داده می‌شود. اگر هم تعداد عددها زوج باشد، دو عدد وسط با هم جمع و بعد بر دو تقسیم می‌شوند و نتیجه این تقسیم در خروجی نمایش داده خواهد شد.

سخن نهایی

سه روش اصلی برای محاسبه میانگین در اکسل که توابع AVERAGE ،MEDIAN و MODE هستند را در این مقاله با هم بررسی کردیم. استفاده از این توابع ساده است اما باید بر حسب نیاز خود، فرمولی را به کار ببرید که بهترین نتیجه را در بر داشته باشد.

در حالی که تابع AVERGA برای میانگین حسابی استفاده می‌شود، تابع MEDIAN در واقع، وسط یک مجموعه عدد را پیدا می‌کند و MODE برای یافتن عددی است که بیشتر از بقیه تکرار شده باشد. ممکن است در هر شرایطی نیازمند استفاده از هر کدام از این فرمول‌ها باشید. پس بهتر است بدانید روش کار هر کدام به چه شکل است.