04 جولای تابع AVERAGEIFS اکسل و میانگین گیری چند شرطی
در آموزش های قبلی با میانگین گیری شرطی با استفاده از تابع AVERAGEIF اکسل آشنا شدیم. محدودیتی که در تابع AVERAGEIF وجود داره اینه که نمیشه بیش از یک شرط رو برای میانگین گیری تعیین کرد. برای اینکه با این محدودیت روبرو نباشیم میتونیم ازتابع AVERAGEIFS اکسل استفاده کنیم که تابع کامل تری به نسبت تابع AVERAGEIF هست و امکان تعیین چندین شرط برای میانگین گیری در اون وجود داره. در ادامه با این تابع به طور کامل آشنا خواهیم شد:
قاعده کلی تابع AVERAGEIFS اکسل:
=AVERAGEIFS(شرط2[اختیاری],محدوده2[اختیاری],شرط1,محدوده1,محدوده میانگین گیری,…)
- محدوده میانگین گیری: این پارامتر محدوده ای رو که میخوایم از سلول های اون میانگین بگیریم مشخص میکنه.
- محدوده1: این پارامتر محدوده ای که قراره اولین شرطمون رو در اون چک کنیم مشخص میکنه. هر سلول این محدوده متناظر با یک سلول محدوده میانگین گیری هست و تعداد سلولهای موجود در این محدوده باید با تعداد سلولهای موجود در محدوده میانگین گیری برابر باشه. به عنوان مثال اگر ما برای پارامتر محدوده میانگین گیری مقدار A1:A10 رو تعیین کردیم میتونیم برای محدوده1 B2:B11 رو تعیین کنیم چون تعداد سلولهای این محدوده هم دقیقاً برابر 10 سلول هست، اما نمیتونیم A2:A12 رو تعیین کنیم چون تعداد سلول های اون از محدوده A1:A10 بیشتره.
- شرط1: این پارامتر مشخص میکنه که سلول های موجود در محدوده1 باید واجد چه شرطی باشند. این پارامتر میتونه یک عدد مثل 10، یک متن مثل قطعه 1 یا یک عبارت مثل “0=” باشه. در صورتی که این پارامتر عبارت باشه باید حتما بین دو علامت “ نوشته بشه.
- محدوده2: این پارامتر محدوده ای رو تعیین میکنه که میخوایم شرط دوم خودمون رو در اون چک کنیم. تعیین این پارامتر اختیاری هست. در صورتی که از این پارامتر استفاده نکنیم در واقع تابع AVERAGEIFS اکسل ما تبدیل به تابع AVERAGEIF میشه
- شرط2: این پارامتر مشخص میکنه که سلول های موجود در محدوده2 باید در چه شرطی صدق کنند.
- محدوده3، شرط3 و …: در صورتی که بخوایم بیش از یک دو شرط رو تعیین کنیم میتونیم.
فقط سلول هایی از محدوده میانگیری در محاسبه میانگین لحاظ میشند که سلول متناظر اونها در محدوده1 واجد شرط 1 باشه، سلول متناظر اونها در محدوده2 واجد عبارت شرطی 2 باشه باشه و الی آخر.
مثال استفاده از تابع AVERAGEIFS اکسل:
جدول زیر رو در اکسل خودتون وارد کنید:
| سال | نام محصول | مشتری | میزان خرید |
| 1391 | لپ تاپ نوع1 | حسن زاده | 29 |
| 1392 | لپ تاپ نوع1 | حسن زاده | 24 |
| 1391 | لپ تاپ نوع1 | حسن زاده | 18 |
| 1392 | لپ تاپ نوع2 | حسن زاده | 47 |
| 1391 | لپ تاپ نوع3 | حسن زاده | 29 |
| 1392 | لپ تاپ نوع3 | علوی | 18 |
| 1392 | لپ تاپ نوع3 | علوی | 17 |
| 1391 | لپ تاپ نوع3 | علوی | 12 |
| 1391 | لپ تاپ نوع1 | تقوی | 37 |
| 1391 | لپ تاپ نوع2 | تقوی | 49 |
| 1392 | لپ تاپ نوع1 | تقوی | 22 |
| 1392 | لپ تاپ نوع1 | تقوی | 14 |
میخوایم با استفاده از تابع AVERAGEIFS اکسل میانگین خرید هر فاکتور سال 1391 آقای حسن زاده رو محاسبه کنیم.
1. تعیین محدوده میانگین گیری:
خب ازونجایی که ما قراره میانگین میزان خرید مشتری رو حساب کنیم و میزان خرید هر مشتری هم در ستون چهارم قرار داره پارامتر محدوده میانگین گیری ما برابر با ستون چهارم اکسل یعنی D:D خواهد بود.
2. تعیین محدوده1:
یکی از شرط ها این هست که مشتری آقای حسن زاده باشه. نام مشتریان در ستون سوم قرار داره. پس اولین محدوده ما C:C هست.
3. تعیین شرط1:
چون شرط ما اینه که نام مشتری آقای حسن زاده باشه عبارت “حسن زاده” رو به عنوان شرط1 و پارامتر سوم تابع AVERAGEIFS اکسل استفاده میکنیم.
. تعیین محدوده2:
تنها اینکه نام مشتری آقای حسن زاده باشه کافی نیست چون ما میانگین خرید آقای حسن زاده رو در سال 1391 میخوایم. پس باید محدوده دومی رو هم برای چک کردن سال در تابع AVERAGEIFS اکسل خودمون تعریف کنیم. همونطور که مشخصه سال خرید در اولین ستون جدول قرار داره پس دومین محدوده ما A:A هست.
3. تعیین شرط2:
برای چک کردن اینکه صرفاً خریدهای آقای حسن زاده در سال 1391 در محاسبه میانگین لحاظ بشه کافیه 1391 رو به عنوان شرط2 و پارامتر پنجم تابع AVERAGEIFS اکسل استفاده میکنیم.
در پایان تابع AVERAGEIFS اکل ما به شکل زیر کامل میشه:
=AVERAGEIFS(D:D;C:C;”حسن زاده”;A:A;1391)
اگر این فرمول در یکی از سلول های اکسل بنویسیم عبارت 25 به عنوان نتیجه تابع در سلول نمایش داده خواهد شد که میانگین خرید آقای حسن زاده در سال 1391 هست.
دانلود فایل مثال تابع AVERAGEIFS اکسل:
[private role=specialsubscriber]AVERAGEIFS.sample.worksheet.ir[/private]
تمرین تابع AVERAGEIFS اکسل:
[private role=specialsubscriber]با استفاده از تابع AVERAGEIFS اکسل، برای مثال بالا میانگین خرید آقای حسن زاده رو در سال 1392 برای لپتاپ نوع 1 محاسبه کنید.[/private]
karimi
Posted at 14:02h, 23 میباسلام، من میخوام توی یک سری زمانی میانگین بگیرم، ولی به صورت چهارتا چهارتا. مثلا 1تا 4، 5تا8، 9تا12 و…
برای این موضوع راه حلی دارید راهنمایی بفرمایید؟
ممنون میشم اگر پاسخ بدید.
ZHALEH AHMADIIRAEI
Posted at 21:46h, 16 نوامبرسلام دوست گرامی. من میخوام میانگین یک ستون دیتا در حدود 40هزازرتاست رو درروزهای مختلف سالهای مختلف بگیرم از چه دستوری باید استفاده کنم؟
یاسین اسماعیل پور
Posted at 15:44h, 18 نوامبرسلام دوست عزیز.
برای اینکار بهتون پیشنهاد میکنم از ابزار pivot table استفاده کنید. اینجا لینک هاش رو براتون میزارم:
آشنایی با ابزار Pivot Table اکسل – بخش اول
آشنایی با ابزار PivotTable اکسل – بخش دوم
اردشیر
Posted at 23:35h, 22 میسلام.
میخوام میانگین اولین دو عدد در یک ستون رو بگیرم.
به شرط اینکه اون دو عدد مثلا از یک مقداری بیشتر باشه(مثلا 50 بیشتر باشه)
ممنون
10
20
30
51
40
100
60
200
saeed
Posted at 08:59h, 13 میسلام و عرض ادب
من چندتا امتحان گرفتم، یکی از 8 نمره، یکی از 10 نمره و یکی از 12 نمره
حالا میخوام اکسل این سه نمره با هم جمع بزنه و میانگین از 20 نمره بهم بده؟
عیسی حقیری
Posted at 02:17h, 25 مارسدر میانگین نمرات بعضی دروس یک نمره ای هستند (مانند انضباط پایانی ندارد)و بعضی از دروس دو نمره ای هستند
میانگین چگونه بگیرم
کارگر
Posted at 22:46h, 29 اکتبرسلام شبتون بخیر
من یک سری داده برای میزان درامد دارم که به صورت طبقه بندی هست (زیر دو میلیون، 2 تا 4 میلیون ، 4 تا 6 میلیون و …) چطور میتونم میانگین و انحراف معیارش رو توس اکسل بگیرم؟
ممنون
mohammad
Posted at 07:58h, 27 ژوئنسلام
ممنون بخاطر مطالب و آموزش حرفه‌ای تون.
من یه جدول دارم که دو ستون نوع شغل(رسمی / قراردادی / پیمانکاری) و سن رو داره حالا من میخوام به تفکیک میانگین هر نوع شغلی رو بگیرم و میانگین سن رسمی و قراردادی رو هم با هم بگیرم.
یعنی خروجی م اینها رو داشته باشم:
1. میانگین سن برای نوع شغلی رسمی = XX
2. میانگین سن برای نوع شغلی قراردادی= XX
3. میانگین سن برای نوع شغلی پیمانکاری= XX
4. میانگین سن برای نوع شغلی رسمی و قراردادی = XX
،mohammad
Posted at 21:09h, 10 آوریلسلام وقت بخیر. میخواستم میانگین یک ستون رو حساب کنم با این تفاوت که برای هر ردیف که میخوام فقط با اعداد بالای اون ردیف میانگین رو حساب کنه .
یاسین اسماعیل پور
Posted at 23:41h, 10 آوریلسلام محمد جان. برای اینکار باید از ترکیب مطلق و نسبی استفاده کنید. فرض کنید مقادیرتون در ستون A قرار دارند. فرمول زیر رو در b1 بنویسید و تا انتها کپی کنید:
=average($A$1:A1)
محمد
Posted at 15:16h, 14 مارسسلام دوست عزیز
دوست عزیز من یه 12 سلول رو میخوام avreage بگیرم به شرطی که اعداد بزرگتر از صفر باشد .
یعنی اگر تو 3 سلول اعدادبزرگتر از صفر بود فقط میانگین این سه عدد را محاسبه کند
ممنون میشم راهنمایی بفرمایید.
یاسین اسماعیل پور
Posted at 17:03h, 09 آوریلسلام. برای فرض کنید این 12 سلول توی A1 تا A12 باشند. برای اینکار از فرمول زیر استفاده کنید:
=Averageif(A1:A12,”>3″,A1:A12)
sama
Posted at 12:56h, 21 جولایسلام،در تکمیل صحبت های ایشون اگر در یک ردیف مثلا همین 12 سلول همه مقدار 0 داشته باشد و بخواهیم به جای نشان دادن خطای Div /0 به ما عدد صفر 0 را در پاسخ نشان دهد چه باید کرد؟چون فرمول تعمیم داده میشه
sama
Posted at 09:22h, 02 آگوستممکنه لطفا پاسخ بدیذ
محمد
Posted at 14:58h, 04 دسامبرسلام و احترام.
نحوه محاسبه مجموع پنج عدد نزدیک به میانگین در یک بازه عددی به چه صورت است؟
ممنون از سایت فوق العاده شما.
ebrahimi
Posted at 12:51h, 04 اکتبرباسلام
لطفا راهنمایی فرمایید چنانچه یک مبلغی را برای یک نوع جنس با قیمتهای مختلف خریداری میشه را در یک ستون نمایش داده شود بطورمثال :
Aمبلغ خرید
Bقیمت خرید
Cتعدادخریداری شده
Dمیانگین قیمت خرید
وهمچنانکه خریدها ادامه داشته باشه میانگین نیز محاسبه شود ونیازی به اینکه هربار برای بحث میانگین فرمول تعریف شود نباشه
هانیه
Posted at 01:33h, 13 سپتامبرباعرض سلام میشه لطفا راهنماییم کنید من سلول های باشماره تکراری زیاددارم میخوام سلول های تکراری رو شماره گذاری کنه مثلا سلول 1 عدد 1 سلول 2 عدد 2و… جلوش بیفته و سلولی که شماره تکراری نداره 1 باشه ممنون
سالار
Posted at 12:02h, 26 ژوئنمیشه به زبان ساده بفرمایید منظور از این فرمول چیه”
AVERAGEIFS( ‘[950801b.xlsx]1’!$J:$J, ‘[950801b.xlsx]1’!$A:$A, B$1, ‘[950801b.xlsx]1’!$J:$J, “> 15”, ‘[950801b.xlsx]1’!$J:$J, “< 50" )
یاسین اسماعیل پور
Posted at 17:36h, 30 ژوئنسلام. این فرمول داره معدل مقادیر موجود در ستون J رو میگیره با این شرط که فقط اعداد بزرگتر از 15 وکوچکتر از پنجاه در معدل گیری لحاظ بشن و یه شرط دیگه روی ستون A که چون کل شیتتون رو ندارم نمیدونم دقیقا چیه
mehran
Posted at 23:29h, 22 میبا سلام
من 30000تا داده دارم و میخوام در یک ستون برای هر 20 داده یک میانگین بدست بیارم چکار باید بکنم. فرمولشو چجوری باید بنویسم؟
لطفا راهنمایی کنید
یاسین اسماعیل پور
Posted at 10:56h, 23 میسلام. برای اینکار باید از ترکیب دو تابع offset و row استفاده کنید. فرض کنید مقادیرتون در ستون a قرار دارند. حالا فرمول زیر رو در b1 بنویسید و کپی کنید تا پایین:
=AVERAGE(OFFSET($A$1:$A$20,(ROW()-1)*20,0))
میترا
Posted at 10:55h, 12 میسلام
اگر در یک کارنامه بخواهیم بر اساس یک کد ، معدل اعداد متناسب را به دست بیاوریم ، چه فرمولی باید بنویسیم .
برای توضیح بیشتر:
کد های استانی موجود است . در یک ستون
در ستون دیگری امتیازاتی ثبت شده
ما باید بتوانیم کد استان را جستجو کنیم و بر اساس آن معدل امتیازات را بدست آوریم .
ممنونم اگر راهنمایی فرمایید .
مجید
Posted at 09:30h, 08 نوامبرباسلام
روش میانگین گیری اعداد نمونه زیر چگونه است
65%
65.2%
118%
118.2%
یاسین اسماعیل پور
Posted at 15:04h, 10 نوامبرسلام
فرض کنید این اعداد در سلول های a1 تا a4 قراردارند. فرمول شما میشه:
=average(a1:a4)
Homa
Posted at 13:59h, 15 آگوستسلام
من یک سری داده برای 40 سال دارم که به صورت روزانه هست. حالا میخوام میانگین بودجه روزانه ام رو (که مقادیرش در ستون B هست)، نه برای تمام روزهای این بازه بلکه فقط برای ماه مشخصی در این 40 سال داشته باشم.(تاریخ ها ستون A هست)
چه‌طور باید شرط بنویسم که میانگین مقادیری که میخوام رو فقط در بازه‌ی این ماه حساب کنه برام.
ممنون. لطف می‌کنید اگر پاسخ دهید.
حسین
Posted at 07:30h, 15 آگوستسلام. برای تعریف از پیش تعیین شده از میانگین و قرار دادن در hyperlink از ستون داده هایی که هر سل خودش میانگین یک ردیف دیگر است و تعداد آنها بین 50 تا 500 متغیر است، از AVERAGE A استفاده میکنم اما خروجی آن عدد نيست. چکار کنم؟
rezchro99
Posted at 11:41h, 16 جولایسلام
مرسی از سایت عالیتون . واقعا اطلاعات خیلی مفیدی رو کسب کردیم.
فقط یه سوال : عضویت ویژه چگونه میتونیم باشیم ؟
سپاس.
محمد
Posted at 14:54h, 04 دسامبرسلام و احترام.
نحوه محاسبه پنج عدد نزدیک به میانگین در یک بازه عددی چگونه است؟
ممنون از سایت فوق العاده تون.
سینا
Posted at 16:39h, 01 نوامبرسلام . اگر یک سری متغیر کیفی داشته باشیم ( مثلا وضعیت جوی: ابری ، برفی ، آفتابی و بارانی ) و به هر کدام یک عدد نسبت دهیم (1و2و3و4) و بخواهیم برای آنها میانگین و انحراف معیار بگیرم ( 1 ابری و 0 در غیر این صورت )و به همین ترتیب برای گروه های دیگر ( 2 برفی 0 در غیر این صورت ) باید در اکسل از چه مراحلی استفاده کرد
محمد صالحی
Posted at 13:20h, 06 آگوستبرای میانگین تاریخ شمسی، بعد از تبدیل کردن number به Date، کافیه مثلاً 1395 رو بکنید 1995، یعنی به 3 رو تبدیل به 9 کنید، بعد از تابع AVERAGE، میانگین سل های مورد نیاز تون رو حساب کنید.
جواب مورد نظر تون به دست میاد و بعدش فقط نیاز هست که 1995 جواب رو دوباره برگردونید به 1395.
همین 🙂
پرهام
Posted at 22:05h, 23 مارسدرود بر شما
من با استفاده از AVEREGEIFS تونستم مسئلمو پیش ببرم مطابق فرمول زیر:
=AVERAGEIFS(E2:E745,G2:G745,”7″,H2:H745,”90″)
ولی مشکل اینجاست که اگر عدد 7 وجود نداشته باشه این رو #DIV/0! نشون میده. امکانی هست که هرجا عدد 7 و یا عدد 90 وجود نداشت عدد صفر رو نشون بده بجای این پیغام: #DIV/0!
ممنون میشم راهنمائیم بفرمائید..
hesam
Posted at 13:10h, 16 نوامبرiferor
یاسین اسماعیل پور
Posted at 15:29h, 18 نوامبرمرسی حسام جان
محمد
Posted at 00:24h, 28 فوریهسلام میخواستم میانگین یه سری نمرات رو بدست بیارم بدون محاسبه بزرگترین و کمترین نمره فرمولش و نحوه محاسبش چطوریه مرسی
eliiiiii
Posted at 13:41h, 28 ژانویهسلام میشه نمونه فایل به نمودار بردن نمرات یک کلاس را در محیط اکسل برای دانلود بزارید. یا راهنمایی کنید چگونه میشه این کار رو انجام داد.
ali
Posted at 19:41h, 02 دسامبرسلام دوستان
من 73225 دیتا دارم که نمودارش خیلی پهن و بد شکل میشه یه راه معرفی کنید بهم که بتونم نمودارمو مثل خط بکشم.
ممنون
tahere
Posted at 10:20h, 22 نوامبرسلام
من یک سری داده دارم میخوام میانگین گیری کنم که بیشترین و کمترین مقدار در نظر گرفته شود؟
Zohreh
Posted at 10:59h, 24 اکتبرسلام من میخواستم میانگین حقوق پرداختی حسابداران یک شرکت رو با تابع AVEREGEIFSحساب کنم میشه کمکم کنید
یاسین اسماعیل پور
Posted at 14:01h, 31 اکتبرسلام. من که کامل نحوه استفاده از تابع averageifs رو توضیح دادم. مشکلتون کجاست؟ لطفا جزئیات بیشتر بگید تا بتونم کمک کنم
sepehr
Posted at 14:50h, 17 سپتامبرسلام
من میخام دو ستون رو به هم ربط بدم هرکاری هم کردم جواب نداد میتونین راهنماییم کنین؟
مثال:
ستون A شرح کالایه
ستون B کد کالایه
حالا در شیت دومم یک جدول دارم که میخام وقتی کد کالا رو میزنم شرح کالا بیاد که نمیاد…
یاسین اسماعیل پور
Posted at 00:34h, 19 سپتامبرسلام
برای اینکار میتونید از تابع vlookup استفاده کنید
afsan
Posted at 17:38h, 12 سپتامبرمرسی واقعا عالیییییییییی
احسان
Posted at 11:36h, 23 آگوستسلام وقتتون بخير ي سوال در مورد average داشتم ، فرض كنيم يه سري نمره داريم كه از 10 تا 20 هستن ميخوايم با استفاده از AVERAGE بگيم كه نمراتي كه از 10 پايين تر هستن و اكسل بنويسه رد و از 10 بالاترو بنويسه قبول
نمره نام
15 علي
12 رضا
9 محمد
14 ياسين
20 عباس
8 حسين
مر
Posted at 15:54h, 25 دسامبراین خب میانگینه مگه ؟!
SHIVA
Posted at 20:15h, 09 ژوئنسلام.
یک سری داده دارم که میخوام بین این داده هابعنوان مثال میانگین داده های بین 70تا 100 رو بدست بیارم،چه فرمولی باید براش بنویسم؟
ممنون میشم زودتر کمکم کنین. با تشکر
یاسین اسماعیل پور
Posted at 20:33h, 09 ژوئنسلام شیوا جان. از همین تابه averageifs میتونی استفاده کنی:
=averageifs(a1:a1000;a1:a1000;”>=70″;a1:a1000;”<=100")
ehsan
Posted at 09:48h, 06 ژوئنبا عرض سلام و خدا قوت ….
من یک انتگرال دارم که دو تا مقدار اولیه آلفا و بتا را به عنوان ورودی های انتگرال میگیره(منظور کران بالا و پایین انتگرال) و انتگرال رو حساب میکنه ….. اصلا میشه انتگرال نوشت؟؟؟؟؟؟؟؟؟؟؟؟
ممنون میشم پاسخ دهـــــــــــــــــــــــــــــــــــــید . . .
آموزش ماکرو نویسی در اینجا
Posted at 13:32h, 21 میسلام ، جناب صالحی – راهش خیلی ساده است ، اگه فرض کنیم که اعداتون ، در ستون A و در ردیفهای 10 تا 100 باشه ، کافیه فرمول زیر را کپی کرده و با آن کار کنید . موفق باشید – خانیکی
=AVERAGEIF(A10:A100,”>0″)
0919-302-6452
salehi1354
Posted at 11:03h, 21 میچنانچه بخواهیم میانگین اعداد در یک ستون که بیشتر از 0 است را محاسبه نماییم چگونه فرمول آن را مینوسیم
با تشکر