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

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

چرا محاسبه میانگین مهم است؟

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

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

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

محاسبه دستی میانگین

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

=SUM(A1:A5)/COUNT(A1:A5)

در این فرمول:

• تابع SUM برای جمع‌کردن مقادیر عددی در سلول‌های مشخص‌شده استفاده می‌شود.
• تابع COUNT تعداد سلول‌هایی را که فقط حاوی اعداد هستند، می‌شمارد.

در مثال زیر، میانگین سن همه مشتریان از ردیف ۲ تا ردیف ۱۲ با استفاده از فرمول فوق محاسبه شده است:

=SUM(B2:B12)/COUNT(B2:B12)

استفاده از تابع AVERAGE

تابع AVERAGE رایج‌ترین و ساده‌ترین روش برای محاسبه میانگین در اکسل است. این تابع به‌طور خودکار میانگین مقادیر عددی را محاسبه می‌کند:

= AVERAGE(number1, [number2], …)

در فرمول فوق:

• Number1 (ضروری): محدوده، مراجع سلولی یا اولین عددی را که می‌خواهیم میانگین آن محاسبه شود، مشخص می‌کند.
• Number2 (اختیاری): اعداد، مراجع سلولی یا محدوده‌های اضافی که می‌خواهیم میانگین آن‌ها محاسبه شود.

نکات مهم در مورد تابع AVERAGE:

• این تابع می‌تواند حداکثر ۲۵۵ آرگومان را بپذیرد.
• سلول‌های خالی، متنی یا منطقی (TRUE/FALSE) را نادیده می‌گیرد.
• سلول‌های حاوی عدد صفر را در محاسبات لحاظ می‌کند.
• در صورت وجود خطا یا متنی که قابل‌تبدیل به عدد نباشد، پیام خطا نمایش می‌دهد.

همان مثال قبل را برای محاسبه میانگین سن همه مشتریان از ردیف ۲ تا ۱۲ درنظر بگیرد. این بار با استفاده از فرمول آن را محاسبه می‌کنیم:

=AVERAGE(B2:B12)

اگر یک ردیف حاوی مقدار غیرعددی باشد، نادیده گرفته می‌شود:

در اینجا، میانگین ردیف‌های B2 تا B6 گرفته شده است؛ اما B5 به دلیل داشتن داده غیرعددی در میانگین لحاظ نشده است.

استفاده از تابع AVERAGEA

تابع AVERAGEA برای محاسبه میانگین تمام سلول‌های غیرخالی استفاده می‌شود. تفاوت اصلی آن با AVERAGE این است که مقادیر منطقی TRUE را به‌عنوان ۱ و FALSE را به‌عنوان ۰ درنظر می‌گیرد. سینتکس این تابع به‌صورت زیر است:

=AVERAGEA(value1, [value2], …)

Value1 الزامی است، مقادیر بعدی اختیاری هستند.

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

می‌توانید ببینید که True در ستون B با ۱ جایگزین می‌شود و مقدار میانگین ۷.۲ به دست می‌آید. در ستون C، False با ۰ جایگزین می‌شود و بنابراین مقدار میانگین = ۹+۹+۹+۰+۸ / ۵ = ۷ به دست می‌آید.

در ادامه بخوانید: آموزش رفع ارور #### در اکسل

استفاده از تابع AVERAGEIF

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

=AVERAGEIF(range, criteria, [average_range])

در این فرمول:

• Range (ضروری): محدوده‌ای از سلول‌ها را که باید براساس شرط داده‌شده آزمایش شود، مشخص می‌کند.
• Criteria (ضروری): شرطی است که برای تعیین سلول‌هایی که باید میانگین شوند، استفاده می‌شود. شرط مشخص‌شده می‌تواند به‌صورت یک عدد، مقدار متنی، عبارت منطقی یا مرجع سلولی باشد، مانند ۵ یا “>5” یا “گربه” یا A
• Average_range (اختیاری): مجموعه‌ای از سلول‌هایی است که می‌خواهیم میانگین آن‌ها محاسبه شود. اگر مشخص نشود، از محدوده برای محاسبه میانگین استفاده می‌شود.

به نکات زیر توجه کنید:

• AVERAGEIF سلول خالی را در average_range نادیده می‌گیرد.
• AVERAGEIF سلول‌هایی را در محدوده که حاوی TRUE یا FALSE هستند، نادیده می‌گیرد.
• اگر محدوده خالی یا مقدار متنی باشد، AVERAGEIF مقدار خطای #DIV0! را برمی‌گرداند.
• اگر سلولی در شرط خالی باشد، AVERAGEIF مقدار سلول را به‌عنوان مقدار ۰ درنظر می‌گیرد.

به مثال زیر دقت کنید:

=AVERAGEIF(A2:A6, “Apple”, B2:B6)

این فرمول میانگین مقادیر در محدوده B2:B6 را فقط برای ردیف‌هایی که مقدار متناظر در A2:A6 کلمه “Apple” است، محاسبه می‌کند. بنابراین در محدوده A2: A6، هر جا که سیب آمده است، قیمت آن را برای محاسبه میانگین درنظر می‌گیرد.

استفاده از تابع AVERAGEIFS

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

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

در فرمول فوق:

• average_range (ضروری): محدوده سلول‌هایی است که می‌خواهید میانگین آن‌ها محاسبه شود.
• criteria1_range1, criteria2_range2, … criteria1_range2 (ضروری): محدوده‌ای که شرط مرتبط با آن اعمال می‌شود.
• criteria1, criteria2, … criteria1 (ضروری): شرطی که برای استفاده در محدوده ۱ و شرط ۲ برای استفاده در محدوده ۲ و به‌همین‌ترتیب استفاده می‌شود.

مثال زیر را برای محاسبه میانگین قیمت محصولی به نام “Apple” با قیمت بیشتر از ۹۹ درنظر بگیرید:

=AVERAGEIFS(B2:B6, A2:A6, “Apple”, B2:B6, “>99”)

در اینجا دو شرط وجود دارد:

• شرط اول: محدوده A2:A6 و شرط “Apple”، ۳ ردیف با این شرط مطابقت دارند.
• شرط دوم: محدوده B2:B6 و شرط “>99” و ۳ ردیف با نام محصول سیب و قیمت بیشتر از ۹۹ وجود دارد.

بنابراین میانگین برابر است با: (۱۰۰ + ۱۲۰ + ۲۰۰) / ۳ = ۱۴۰.

محاسبه میانگین در اکسل با شرایط منطقی OR

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

برای مثال، برای آزمایش اینکه آیا A1 حاوی “x” یا “y” است، از =OR (A1=”x”, A1=”y”) استفاده کنید.

به‌عنوان‌مثال، برای مشاهده این‌که محصول در یک ردیف خاص سیب یا پرتقال است، می‌توانید:

در اینجا می‌بینید که محصول در محدوده A2 تا A6 با نام سیب یا پرتقال نتیجه true را در ستون B2 تا B6 ایجاد می‌کند. اکنون می‌توانید از این زیر نتیجه به‌عنوان بخشی از تابع AVERAGEIFS استفاده کنید.

برای محاسبه میانگین قیمت محصولی به نام سیب یا پرتقال با قیمت بیشتر از ۹۹ از روش زیر استفاده می‌شود:

=AVERAGEIFS(C2:C6,B2:B6,”True”,C2:C6,”>99″)

همان‌طور که مشاهده می‌کنید، برای اضافه‌کردن OR با AVERAGEIFS، یک ستون جدید اضافه می‌کنیم که نتیجه پرس‌وجوی OR، یعنی true یا false را می‌گیرد. براساس این پاسخ، میانگین را با تطبیق True برای محدوده B2:B6 محاسبه می‌کند.

بنابراین میانگین برابر با (۱۰۰ + ۱۲۰ + ۲۰۰) / ۳ = ۱۴۰ می‌شود.

در مثال فوق، فقط آن قیمت‌هایی درنظر گرفته شد که برای محصول سیب یا پرتقال بیشتر از ۹۹ بودند.

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

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

چند نکته کاربردی وجود دارد که با دانستن آن‌ها می‌توانید راحت‌تر و مؤثرتر میانگین را در اکسل محاسبه کنید:

• استفاده از نام‌گذاری محدوده‌ها: برای افزایش خوانایی و کاهش خطا در فرمول‌ها می‌توانید به محدوده‌های سلول نام اختصاص دهید. مثلاً به‌جای A1:A10 می‌توانید از نامی ‌مانند “Sales_Data” استفاده کنید.
• مدیریت داده‌های نامعتبر: قبل‌از محاسبه میانگین در اکسل، اطمینان حاصل کنید که داده‌های شما تمیز و معتبر هستند. برای این کار می‌توانید از توابعی مانند ISNUMBER استفاده کنید. در وب‌سایت ablebits.com در مورد این تابع چنین گفته شده است:

تابع ISNUMBER در اکسل بررسی می کند که آیا یک سلول دارای مقدار عددی است یا خیر.

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

=SUMPRODUCT(A1:A5, B1:B5) / SUM(B1:B5)

در این فرمول، A1:A5 مقادیر اصلی و B1:B5 وزن‌های متناظر هستند.

• مقایسه میانگین گروه‌های مختلف: با استفاده از توابع پیشرفته‌تر مانند AVERAGEIF و AVERAGEIFS می‌توانید میانگین گروه‌های مختلف را محاسبه و مقایسه کنید. این کار برای تحلیل عملکرد بخش‌های مختلف یک سازمان یا مقایسه محصولات مختلف بسیار مفید است.
• محاسبه میانگین با حذف مقادیر حدی:گاهی اوقات مقادیر بسیار بزرگ یا کوچک می‌توانند میانگین را منحرف کنند. برای رفع این مشکل می‌توانید از ترکیبی از توابع SMALL، LARGE و AVERAGE استفاده کنید تا میانگین را بدون درنظرگرفتن چند مقدار حدی محاسبه کنید.
• استفاده از میانگین در فرمول‌های شرطی: می‌توانید از میانگین در قالب فرمول‌های شرطی استفاده کنید. مثلاً می‌توانید سلول‌هایی را که مقدارشان بالاتر از میانگین است، انتخاب کنید:

=A1>AVERAGE($A$1:$A$10)

این فرمول را در قالب فرمت شرطی برای محدوده مورد نظر اعمال کنید.

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

از کجا می‌توانید صفر تا صد اکسل را یاد بگیرید؟

اگر می‌خواهید مهارت‌های اکسل خود را به سطحی حرفه‌ای ارتقا دهید و در دنیای رقابتی امروز یک قدم جلوتر باشید، دوره جامع آموزش اکسل مقدماتی تا پیشرفته ۲۴ وب لرن، دقیقاً همان چیزی است که به آن نیاز دارید!

در این دوره آموزشی، ۱۸ ویدیو با کیفیت بالا با بیش‌از ۹ ساعت محتوای آموزشی غنی در اختیار شما قرار می‌گیرد که در آن‌ها از مفاهیم پایه تا تکنیک‌های پیشرفته، همه چیز را در مورد اکسل خواهید آموخت. پس‌از خرید دوره، برای همیشه به تمام محتواها و به‌روزرسانی‌های آینده دسترسی خواهید داشت و خیالتان بابت به‌روزماندن راحت است. همچنین هر سؤال یا مشکلی که داشته باشید، تیم پشتیبانی ۲۴ وب لرن آماده کمک به شماست.

پس همین حالا از صفحه محصول دوره اکسل ۲۴ وب لرن دیدن کنید و اولین قدم را برای تبدیل‌شدن به یک متخصص اکسل بردارید.

در ادامه بخوانید: فرمول نویسی در اکسل

سوالات متداول

آیا می‌توانم از تابع AVERAGE برای محاسبه میانگین تاریخ‌ها استفاده کنم؟

بله، تابع AVERAGE قابلیت محاسبه میانگین تاریخ‌ها را دارد. اکسل تاریخ‌ها را به‌صورت اعداد ذخیره می‌کند؛ بنابراین می‌توانید میانگین آن‌ها را محاسبه کنید. نتیجه یک عدد خواهد بود که می‌توانید با استفاده از فرمت سلول به تاریخ تبدیل کنید.

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

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

=AVERAGEIF(A1:A10, “<>0”)

این فرمول میانگین مقادیر غیرصفر در محدوده A1:A10 را محاسبه می‌کند.

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

بله، می‌توانید از ترکیب توابع GEOMEAN یا PRODUCT و POWER برای محاسبه میانگین هندسی استفاده کنید.

سخن پایانی

محاسبه میانگین در اکسل یک مهارت ضروری برای هر کسی است که با داده‌ها کار می‌کند. با تسلط بر روش‌های مختلف محاسبه میانگین، از توابع پایه مانند SUM و COUNT گرفته تا توابع پیشرفته‌تر مانند AVERAGEIFS، می‌توانید داده‌های خود را به‌طور موثرتری تحلیل و خلاصه کنید. درک و به‌کارگیری این تکنیک‌ها به شما امکان می‌دهد قابلیت‌های تحلیل داده خود را افزایش دهید و تصمیمات بهتری براساس داده‌ها بگیرید.

منابع

Geeksforgeeks