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

[nayyeri1982]

با سلام
در یک شیت، چندین ردیف داریم با سه ستون
سلولهای ستون سوم حاوی فرمول جمع دو ستون اول و دوم هستند.
در سلولی مجزا هم فرمول میانگین کلیه سلولهای ستون سوم درج شده. ممکن است همه سطرها تکمیل نشود. لذا سطرهای خالی عدد صفر را نشان می دهد.
می خواهیم ملاک میانگین گرفتن فقط سطرهای "پر" باشد" و بصورت خودکار از سطرهای خالی (صفر) صرفنظر کند.

[~M*E*H*D*I~]

RE: میانگین گرفتن از یک بازه بدون احتساب سلولهای خالی

باید از تابع aggregate استفاده کنید

[nayyeri1982]

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

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

یادآوری: سلولهایی که صفر شده اند عددهایی هستند که بخاطر خالی بودن ستون یک و دو صفر شده اند(سلول حاصلجمع دو ستون 1و2 است)، نه بخاطر اینکه در آن سلول چیزی نوشته نشده....[hr]
اگر سلولهایی خالی باشند این تابع جواب می دهد، ولی اگر عدد صفر وجود داشته باشد صرفنظ نمی کند. یعنی در بین کدهاش چنین چیزی پیدا نکردم که در مورد سلولهایی که ارزش صفر دارند چنین اقدامی انجام دهد... راهی بنظرتان نمی رسد؟

یادآوری: سلولهایی که صفر شده اند عددهایی هستند که بخاطر خالی بودن ستون یک و دو صفر شده اند(سلول حاصلجمع دو ستون 1و2 است)، نه بخاطر اینکه در آن سلول چیزی نوشته نشده....[hr]
چطوری می تونم فایل به این پستها اتچ کنم؟
[hr]
لیست کدهای Ggregate رو از کجا می تونم پیدا کنم؟[hr]
لیست کدهای Ggregate رو از کجا می تونم پیدا کنم؟

[hamidreza313]

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

با سلام خدمت شما و با اجازه استاد وطن پرست
با این شرایط فایل شما ، به نظرم بهتر است که کار را از ریشه درست کنیم یعنی شما در سلول جمع خود دستور زیر را بنویسید ، تا وقتی که حاصل جمع صفر بود هیچ چیزی را نشان ندهد :
اگر فرض کنیم داده های شما در سل a1 و b1 باشد آنگاه شما باید در سل c1 بنویسید :

=IF(SUM(A1:B1)=0;"";SUM(A1:B1)) 

امتحان کنید اگه مشکلی بود باز هم درخدمت هستیم.
ضمنا برای ارسال فایلتان بهتر است از سایتهای آپلود استفاده کنید و آدرس فایل را در پست بگذارید .

[1q2w3e4rr]

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

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

=AVERAGE(IF(A1:A8>0,A1:A8)) 

البته به جای وارد کردن فرمول با دکمه enter باید از ترکیب ctrl+shift+enter استفاده کنید.

[nayyeri1982]

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

ممنون از نظرات دوستان
از نظراتتون ایده گرفتم و خیلی برام جالب بود که روشهای مختلفی برای حل یه چنین مسئله ای وجود داشته باشه.
البته بنده چون می خواستم ماهی گیری بیاموزم! سوالمو اینجوری مطرح کردم... ولی چون این ستونهایی که حاصلجمع هستند و سلولی که میانگین می گیرد خودشان فرمولهایی از قسمتهای مختلف شیت هستند، نتونستم استفاده کنم...
بقول hamidreza313 گرامی کار را باید ریشه ای حل کرد، ولی ریشه ای تر سلولهایی که ایشون شرط روش گذاشتن...
فایل رو چجوری می تونم براتون بذارم روی تاپیک تا در مورد جزئیات سلولهای عملی توضیح بدم؟

راستی دو روشی که hamidreza313 , 1q2w3e4rr گفتن یه اروری میده و فرمول را قبول نمی کنه و عدد 0 را هایلایت می کنه... نمی دونم چرا؟
می تونم از اون هم عکس بگیرم و براتون بذارم. فقط یه نفر بگه چه جوری فایل اتچ کنم روی تاپیک ؟

[~M*E*H*D*I~]

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

برای این کار راه های خیلی زیادی هست

اولین و بهترین استفاده از

AVERAGEIF(A1:A8;">0";A1:A8)

دومین راه

=SUM(A1:A8)/COUNTIF(A1:A8;">0")

روش های دیگه هم هست که درواقع بهینه نیست طولانیه ولی جواب میده

[hamidreza313]

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

ممنون از استاد
واقعا جوابقانع كننده اي بود . :heart:

[1q2w3e4rr]

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

یعنی هیچکدوم از فرمول ها رو قبول نمیکنه!!!

فکر میکنم از شیوه نگارش فرمول ها باشه....
شیوه نگارش فرول ها در اکسل به دو صورت انجام میشه(البته تا اونحایی که میدونم) من دو نمونه رو هم براتون میزارم

=AVERAGE(IF(A1:A8>0,A1:A8)) 

=AVERAGE(IF(A1:A8>0;A1:A8)) 

در اولی از "," استفاده شده و در دومی از ";"
قبلا میدونستم از کجا میشه تعیین کرد که از کدوم میخواین استفاده کنید ولی الان یادم رفته...دوستان اگه لطف کنن راهنمایی کنن ممنون میشم ، واسم سوال شده!!!

[nayyeri1982]

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

دارم با فایل کلنجار می رم. ایشالا اگه حل شد لینکشو براتون می ذارم تا روشهای بهینه شما را هم یاد بگیرم

[nayyeri1982]

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

ببخشید یه سوال
من می خوام توی تابع IF یه شرط بذارم که اگه سلول خالی آنگاه فلان کار را بکن. یا اگر فلان متن را در سلول بود آنگاه فلان کار را بکن.
دقیقا مثل همین نمونه هایی که دوستان در بالا برای صفر بودن یا نبودن سلول پیشنهاد دادند ولی اینبار عددی نیست. متنی است . چه کار باید بکنم؟
IF (A1<>0,B1=A1)=

[1q2w3e4rr]

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

برای نشون دادن سلول خالی از "" اسفاده کنید. مثال:

=IF(A1="",B1=A1) 

در صورتی بخواهید متنی رو در فرمول داشته باشید هم از همین روش استفاده میشود. مثال:

=IF(A1="ok",B1=A1) 

[nayyeri1982]

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

http://www.sharefile.ir/uploads/1356318955.xlsx
لطفا به این فایل یه نگاهی بندازید.

[1q2w3e4rr]

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

مشکل این بود که اگر سلول قبل خالی میشد ، عددی نمیتونستید باهاش جمع بزنید برای همین اختار میداد ، من شرط رو براتون عوض کردم تا درست بشه...

فایل شما

[hamidreza313]

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

با سلام خدمت دوستان
بايد در تكميل صحبتهاي ارائه شده و فايل دوست عزيز 1q2w3e4rr بگم كه :
آقاي نيري عزيز هرگاه در فايلي چنين اروري ديدي بايد در ابتداي تمام فرمول هاي به كار رفته در سلول از فرمول iferror استفاده كنيد به عنوان مثال براي فايل شما در ستون امتياز نهائي بايد بنويسيد :

=IFERROR(IF($D$1+R10=0;" ";$D$1+R1);"") 

به اين ترتيب ديگر سلول هاي خالي شما هرگز ارور نخواهد داد .