[لیست آموزش توابع]
۷ اشتباه خطرناک در تابع IF که گزارش شما را غیرقابلاعتماد میکند!
تابع IF یکی از مهمترین و پرکاربردترین توابع اکسل است. اما یک اشتباه کوچک در نوشتن شرط میتواند نتیجه کل گزارش را تغییر دهد.
ساختار کلی این تابع به شکل زیر است:=IF(logical_test,value_if_true,val ue_if_false)یعنی:اگر شرط برقرار بود، نتیجه اول و اگر برقرار نبود، نتیجه دوم نمایش داده شود.
فرض کن هدف فروش هر کارشناس ۵۰ میلیون تومان است. اگر مبلغ فروش به هدف رسیده باشد، وضعیت Successful و در غیر این صورت Unsuccessful نمایش داده میشود:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
اگر این تابع نتیجه اشتباهی نمایش میدهد، احتمالاً یکی از هفت خطای زیر را انجام دادهای:
جهت شرط را برعکس نوشتهای!فرض کن میخواهی فروشهای بیشتر یا مساوی ۵۰ میلیون تومان، Successful اعلام شوند.
فرمول اشتباه:=IF(B۲<=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
در این فرمول، فروشهای کمتر یا مساوی ۵۰ میلیون تومان بهاشتباه Successful نمایش داده میشوند.
فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
مفهوم فرمول صحیح:- اگر فروش بیشتر یا مساوی ۵۰ میلیون باشد: Successful- اگر فروش کمتر از ۵۰ میلیون باشد: Unsuccessful
قبل از نوشتن فرمول، شرط را یک بار با زبان ساده برای خودت بخوان.
متن را داخل کوتیشن قرار ندادهای!خروجیهای متنی در فرمولهای اکسل باید داخل علامت کوتیشن " " قرار بگیرند.
فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,Successful,Unsuccessful)
فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
اگر خروجی فرمول متن است، باید آن را داخل کوتیشن قرار بدهی؛ اما برای خروجی عددی معمولاً نیازی به کوتیشن نیست.
عدد را بهصورت متن وارد کردهای!اگر قرار است خروجی تابع در محاسبات بعدی استفاده شود، عدد را داخل کوتیشن قرار نده.
خروجی متنی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"۱","۰")
خروجی عددی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,۱,۰)
در فرمول اول، ۱ و ۰ بهصورت متن برگردانده میشوند؛ اما در فرمول دوم، خروجیها عدد واقعی هستند و میتوان از آنها در محاسبات بعدی استفاده کرد.
ترتیب شرطها در IF تودرتو اشتباه است!فرض کن میخواهی عملکرد فروش را اینگونه دستهبندی کنی:
- فروش ۱۰۰ میلیون تومان و بیشتر: Excellent- فروش ۵۰ میلیون تومان تا کمتر از ۱۰۰ میلیون: Good- فروش کمتر از ۵۰ میلیون تومان: Poor
فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor"))
اگر مبلغ فروش ۱۲۰ میلیون تومان باشد، شرط اول برقرار میشود و اکسل نتیجه Good را نمایش میدهد؛ بنابراین هرگز به شرط مربوط به Excellent نمیرسد.
فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))
اکسل شرطها را از چپ به راست بررسی میکند و پس از رسیدن به اولین شرط درست، ادامه فرمول را بررسی نمیکند.
در دستهبندیهای پلهای، معمولاً باید شرطها را از بزرگترین مقدار به کوچکترین مقدار بنویسی.
پرانتزها را کامل نبستهای!هر تابع IF باید یک پرانتز باز و یک پرانتز بسته داشته باشد. در فرمولهای تودرتو، احتمال فراموشکردن پرانتزها بیشتر است.
فرمول اشتباه:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor")
فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))
در این مثال دو تابع IF داریم. بنابراین در انتهای فرمول نیز باید دو پرانتز بسته وجود داشته باشد.
شرطها را بدون توجه به همپوشانی مرتب کردهای!ممکن است یک مقدار همزمان چند شرط را برقرار کند. برای مثال، عدد ۱۲۰ میلیون هم بزرگتر از ۵۰ میلیون و هم بزرگتر از ۱۰۰ میلیون است.
اکسل سردرگم نمیشود؛ بلکه اولین شرط درست را انتخاب میکند. به همین دلیل ترتیب شرطها اهمیت زیادی دارد.
ترتیب نادرست:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor"))
ترتیب صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))
پس شرط خاصتر یا مقدار بزرگتر را زودتر بررسی کن.
ادامه دارد : مورد ۷ در پست بعدی
۷ اشتباه خطرناک در تابع IF که گزارش شما را غیرقابلاعتماد میکند!تابع IF یکی از مهمترین و پرکاربردترین توابع اکسل است. اما یک اشتباه کوچک در نوشتن شرط میتواند نتیجه کل گزارش را تغییر دهد.
ساختار کلی این تابع به شکل زیر است:=IF(logical_test,value_if_true,val ue_if_false)یعنی:اگر شرط برقرار بود، نتیجه اول و اگر برقرار نبود، نتیجه دوم نمایش داده شود.
فرض کن هدف فروش هر کارشناس ۵۰ میلیون تومان است. اگر مبلغ فروش به هدف رسیده باشد، وضعیت Successful و در غیر این صورت Unsuccessful نمایش داده میشود:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
اگر این تابع نتیجه اشتباهی نمایش میدهد، احتمالاً یکی از هفت خطای زیر را انجام دادهای:
جهت شرط را برعکس نوشتهای!فرض کن میخواهی فروشهای بیشتر یا مساوی ۵۰ میلیون تومان، Successful اعلام شوند. فرمول اشتباه:=IF(B۲<=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")در این فرمول، فروشهای کمتر یا مساوی ۵۰ میلیون تومان بهاشتباه Successful نمایش داده میشوند.
فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")مفهوم فرمول صحیح:- اگر فروش بیشتر یا مساوی ۵۰ میلیون باشد: Successful- اگر فروش کمتر از ۵۰ میلیون باشد: Unsuccessful
قبل از نوشتن فرمول، شرط را یک بار با زبان ساده برای خودت بخوان.
متن را داخل کوتیشن قرار ندادهای!خروجیهای متنی در فرمولهای اکسل باید داخل علامت کوتیشن " " قرار بگیرند. فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,Successful,Unsuccessful) فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")اگر خروجی فرمول متن است، باید آن را داخل کوتیشن قرار بدهی؛ اما برای خروجی عددی معمولاً نیازی به کوتیشن نیست.
عدد را بهصورت متن وارد کردهای!اگر قرار است خروجی تابع در محاسبات بعدی استفاده شود، عدد را داخل کوتیشن قرار نده. خروجی متنی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"۱","۰") خروجی عددی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,۱,۰)در فرمول اول، ۱ و ۰ بهصورت متن برگردانده میشوند؛ اما در فرمول دوم، خروجیها عدد واقعی هستند و میتوان از آنها در محاسبات بعدی استفاده کرد.
ترتیب شرطها در IF تودرتو اشتباه است!فرض کن میخواهی عملکرد فروش را اینگونه دستهبندی کنی:- فروش ۱۰۰ میلیون تومان و بیشتر: Excellent- فروش ۵۰ میلیون تومان تا کمتر از ۱۰۰ میلیون: Good- فروش کمتر از ۵۰ میلیون تومان: Poor
فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor"))اگر مبلغ فروش ۱۲۰ میلیون تومان باشد، شرط اول برقرار میشود و اکسل نتیجه Good را نمایش میدهد؛ بنابراین هرگز به شرط مربوط به Excellent نمیرسد.
فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))اکسل شرطها را از چپ به راست بررسی میکند و پس از رسیدن به اولین شرط درست، ادامه فرمول را بررسی نمیکند.
در دستهبندیهای پلهای، معمولاً باید شرطها را از بزرگترین مقدار به کوچکترین مقدار بنویسی.
پرانتزها را کامل نبستهای!هر تابع IF باید یک پرانتز باز و یک پرانتز بسته داشته باشد. در فرمولهای تودرتو، احتمال فراموشکردن پرانتزها بیشتر است. فرمول اشتباه:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor") فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))در این مثال دو تابع IF داریم. بنابراین در انتهای فرمول نیز باید دو پرانتز بسته وجود داشته باشد.
شرطها را بدون توجه به همپوشانی مرتب کردهای!ممکن است یک مقدار همزمان چند شرط را برقرار کند. برای مثال، عدد ۱۲۰ میلیون هم بزرگتر از ۵۰ میلیون و هم بزرگتر از ۱۰۰ میلیون است.اکسل سردرگم نمیشود؛ بلکه اولین شرط درست را انتخاب میکند. به همین دلیل ترتیب شرطها اهمیت زیادی دارد.
ترتیب نادرست:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor")) ترتیب صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))پس شرط خاصتر یا مقدار بزرگتر را زودتر بررسی کن.
ادامه دارد : مورد ۷ در پست بعدی ۵۱۱۱