جمع اعداد یک محدوده حاوی خطا
شاید به این موضوع در آموزش اکسل مقدماتی برخورده باشید که اگر به هر تابعی در اکسل یک خطا به عنوان ورودی بدهیم ، خروجی آن تابع خطا خواهد شد .
شاید برای شما هم این اتفاق افتاده باشد که بخواهید یک محدوده را که حاوی اعداد می باشد جمع بزنید اما با خطا روبرو می شوید ، برای رفع این مشکل می توان از فرمول ها به طریقه زیر بهره گرفت .
نکته : پس از نوشتن فرمول به جای اینتر ، Ctrl + Shift+ Enter را همزمان بزنید
SUM(IFERROR(D2:D5;””))
در ضمن تابع IFFERROR که در بالا استفاده شده در اکسل 2007 و بالاتر موجود است اگر از اکسل 2003 استفاده می کنید باید از فرمول زیر استفاده نمایید .(حتما در انتها کلید های Ctrl+Shift+Enter را بزنید)
SUM(IF(ISERROR(D2:D5),””,D2:D5))
برای جمع اعداد یک محدوده در اکسل که حاوی خطا است، بهتر است از تابع SUMIF یا SUMIFS استفاده کنید. این توابع به شما اجازه می دهند تا تعدادی شرط را بر اساس محدوده مشخص شده بررسی کرده و سپس مجموع مقادیر متناظر را برگردانید.
به عنوان مثال، فرض کنید که شما می خواهید مجموع اعداد موجود در محدوده A1:A10 را محاسبه کنید، اما این محدوده شامل خطاهایی مانند #DIV/0! یا #VALUE! است. در این صورت، می توانید از تابع SUMIF به صورت زیر استفاده کنید:
SUMIF(A1:A10,”<>#DIV/0!”,A1:A10)
قرار هست این تابع، تمام اعدادی را که در محدوده A1:A10 قرار دارند ولی مقدار آنها برابر با #DIV/0! نیست، انتخاب کند و سپس مجموع این اعداد را بر می گرداند.
در صورتی که شما نیاز دارید تا بر اساس چندین شرط بررسی کنید، می توانید از تابع SUMIFS استفاده کنید. برای مثال، اگر شما می خواهید مجموع اعداد موجود در محدوده A1:A10 را محاسبه کنید، اما فقط برای سلول هایی که مقدار آنها بین 1 و 10 است، از تابع SUMIFS به صورت زیر استفاده کنید:
SUMIFS(A1:A10,A1:A10,”>1″,A1:A10,”<10″)
این تابع، تمام اعدادی را که در محدوده A1:A10 قرار دارند ولی مقدار آنها بین 1 و 10 نیست، انتخاب نمی کند و سپس مجموع اعداد انتخاب شده را بر می گرداند.
در صورتی که در فهم این مطلب با مشکل مواجه شدید در قسمت نظرات مطرح کنید .
همچنین اگر روش های دیگری برای اینکار مد نظرتان است در قسمت نظرات با دیگران به اشتراک بگذارید.
13 Comments
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
با تشکر از مطالب مفیدتون
سلام.
من تا حالا از این تابع استفاده نکرده بودم . اما ظاهرا خیلی کاربردی و مفیده.
ممنون
ضمن تشکر ازایده ای که دراین سایت بکاربرده اید تا علم خودرا که بازحمت یادگرفته اید براحتی دراختیاردیگران بگذارید من ازمنظر شمارا تحسین میکنم بنده درمالیات گیری مشکلی دارم که متاسفانه 5یا 6تا کتاب اکسل خواندم ولی این موضوع راپیدانکردم وآن این است البته این فرمول درستش چگونه است ؟ ولی غلطتش ایطوری است .
if a>100(a-100)*10%,and ifa<=100,endif
منظورم :اگرحاوی سلول aبزرگتراز100باشد aرامنهای 100بکن مانده رادر100ضربکن واگرaکوچکتراز100باشدکاری انجام نده اگرممکن است این فرمول راکه درحقوق و دستمزد کاربرد زیادی دارد برایم بنویسید مرا مدیون ابدی خود کرده اید با احترام
سلام.
این فرمول کاملا ناقص است فایل خود را ارسال کنید تا بر اساس نمونه ای که میخواهید فرمول آن را برایتان داخل فایلتان بنویسم.
موفق باشید.
زمانی که ردیفی در اکسل درست میکنم و شماره گذاری میکنم هنگامی که از یک ستون دیگر فیلتر میگیرم معمولا شماره ردیف ها قاطی میشود از چه فرمولی میتونم کاری کنم که هنگام اضافه کردن ستون های دیگر شماره ردیفم به همان ترتیب قبل باشد.مثلا 1 2 3 4 5 بعد از اضافه کردن ستون دیگر ممکن بشه 1 3 5 .میخواهم همان 1 2 3 به صورت اتوماتیک درج شود مثل اکسس را بدهد؟
مسلما وقتی فیلتر میکنید ردیف بهم میخورد و اصولش هم همینه که بهم بخوره چون ردیف ها جابجا میشن.
اما اگه بخواین در کل ردیفتون به صورت فرمول باشه میتونید از تابع Row استفاده کنید.
در مورد فیلتر کردن یک فرمولی وجود داره. Subtotal
اگه میخواهید مثلاً تو سلول a2 شماره 1 قرار بگیره و زیر اون به ترتیب ردیف افزایش پیدا کنه از فرمول زیر استفاده کنید.
(subtotal(3,b$1,b1
متها این فرمول به شرطی جواب میده که ستون b برای همه فیلدها پر باشه
ببخشید من از دستور IFERROR هم استفاده کردم نشد.
داده هات رو دیدم در قالب متن هستند. اگه همینه که برام فرستادی و فرمول نداره ستون رو انتخاب کن و تب Data و گزینه text to column و گزینه finish رو بزن از حالت متن به عدد تبدیل بشن . بعد با استفاده از روش گفته شده به راحتی به جواب میرسی. اگرم فرمول هستند که اول تبدیل به داده کن اونا رو بعد این کارو انجام بده.
با سلام و خسته نباشید خدمت شما دوست عزیز
در یک جدول اکسل در هنگام جمع زدن چند عدد در یک ستون باهم جواب با جواب واقعی اختلاف جزئی دارد!!!!
عدد واقعی 17,535,441 و عددی که اکسل نشان میدهد 17,535,435 که اختلاف میشه 6
آیا این مشکل به خاطر خود اکسل هست و کس دیگه ایی هم این مشکل رو داشته؟ ( همچنین فرمول جمع اعداد به صورت سلول به سلول با هم جمع میشوند)
اعداد حاوی چند رقم بعد از ممیز است که پنهانه.
با تابع roud اعداد رو رند کنید درست میشه.
با سلام.مدیر محترم یه سوال خیلی مهم دارم.ممنون میشم جواب بدید بهم اگه امکان داره جواب رو ایمیل کنید برام.
سوال:من یه اکسل قرارداد درست کردم که مشخصات نمایندگی هامونو وارد کردمکه شامل نام و نام خانوادگی،شماره همراه، آدرس،تاریخ عقد قرارداد هستش.با توجه به اینکه قراردادهامون یکساله است میخوام وقتی قرارداد نماینده ای تموم میشه یه اخطاری بهم بده.مثلا اسمش روقرمز کنه یا تاریخش روقرمز کنه.همچین چیزی امکان داره؟؟ممنون میشم راهنمایی ام کنید خیلی برام مهمه این موضوع
سلام.
بله امکان داره
البته اگه تاریخهاتون شمسی هست اول باید افزونه توابع فارسی رو نصب کنید.بعد یک شرط بنویسید که برمبنای تاریخ قرارداد و تاریخ جاری اگر یک سال شد پیامی بهتون بده.
شرحش از گفتن در اینجا خارجه.
فایلتون رو بفرستید فرصتی پیش بیاد براتون انجام میدم.