ورونیکا می داند که چگونه از تابع AVERAGE برای تعیین میانگین طیفی از مقادیر استفاده کند. با این حال، او مایل است میانگین را بر اساس مقادیر غیر صفر در محدوده تعیین کند.
تابع کاربرگ که برای این منظور مناسب است، استفاده از AVERAGEIF است. می توانید از این روش استفاده کنید:
=AVERAGEIF(A1:A50,">0")
این تابع فقط شامل سلول هایی می شود که مقادیر بزرگتر از صفر دارند. اگر می‌خواهید سلول‌های خالی را نیز حذف کنید، باید از تابع AVERAGEIFS استفاده کنید. این تابع با AVERAGEIF تفاوت دارد زیرا به شما امکان می دهد چندین معیار را مشخص کنید که نشان می دهد کدام سلول ها را میانگین بگیرید.
=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>","")
البته، اگر می‌خواهید به مشکل "مدرسه قدیمی" (بدون استفاده از AVERAGEIF یا AVERAGEIFS) نزدیک شوید، چندین راه وجود دارد که می‌توانید ادامه دهید. اولین مورد این است که به یاد داشته باشید که چگونه میانگین محاسبه می شود. به عنوان مجموع طیفی از مقادیر تقسیم بر تعداد موارد در محدوده تعریف می شود. بنابراین، با اطمینان از اینکه مخرج (عددی که بر آن تقسیم می‌کنید) هیچ مقدار صفر را شامل نمی‌شود، می‌توانید میانگین حذفی را محاسبه کنید. برای مثال:
=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")
این رویکرد از تابع COUNTIF برای تعیین تعداد سلول‌هایی در محدوده (A1:A50) که حاوی صفر نیستند استفاده می‌کند. اگر این محدوده نه تنها شامل صفر، بلکه سلول‌های خالی نیز می‌شود، و نمی‌خواهید سلول‌های خالی در نتیجه مشخص شوند، باید از فرمول پیچیده‌تری استفاده کنید:
=SUM(A1:A50)/(COUNTIF(A1:A50,"<>0")-COUNTBLANK(A1:A50)-
(COUNTA(A1:A50)-COUNT(A1:A50)))
تابع COUNTIF سلول هایی را می شمارد که به صراحت 0 را ارزیابی نمی کنند، اما سلول های خالی و متنی را شمارش می کند. عبارت COUNTBLANK سلول‌های خالی را تنظیم می‌کند و تفاوت بین COUNTA و COUNT تعداد کل سلول‌های حاوی متن را تنظیم می‌کند.
البته می توانید از فرمول آرایه نیز برای انجام محاسبات خود استفاده کنید:
=AVERAGE(IF(A1:A50<>0,A1:A50))
به یاد داشته باشید که فرمول های آرایه باید با استفاده از ترکیب Ctrl+Shift+Enter وارد شوند . این فرمول آرایه همچنین موارد خالی یا سلول های حاوی متن را حذف می کند.
در مجموع استفاده از توابع AVERAGEIF یا AVERAGEIFS آسان تر است. چه زمانی نمی خواهید از آنها استفاده کنید؟ هنگامی که باید کتاب کار خود را با کاربران نسخه های اکسل قبل از اکسل 2007 به اشتراک بگذارید. (این زمانی است که توابع به اکسل اضافه شدند.)
