قبلاً در آموزش گام به گام اکسل با روش نوشتن فرمول آشنا شدیم. اگه هدفمون از کار با اکسل، پر کردن جدول و نوشتن فرمولای خیلی ساده باشه، در کنار جمع و تفریق و ضرب و تقسیم باید با تابعی مثل Sum یا حاصل جمع و توابع شرطی به خصوص IF آشنایی داشته باشیم.
موضوع این مطلب، آموزش تصویری کار با اکسل و نوشتن فرمول شرطی هست که کاربرد خیلی زیادی داره. با ساده‌گو همراه باشید تا با چند تا مثال ساده، روش استفاده از تابع IF و IFS و همچنین SUMIF و SUMIFS رو یاد بگیریم.
اگه با نوشتن فرمول تو اکسل آشنا نیستید، برای آموزش سریع اکسل مقاله زیر رو بخونید و بعد ادامه این مطلب رو مطالعه کنید:
فرمول تابع IF و IFS در اکسل
خب مهم ترین تابع شرطی تو اکسل، تابع IF هست. این تابع سه تا آرگومان داره، یعنی داخل پرانتزی که جلوی IF مینویسیم، ۳ تا عبارت داریم، پس فرمولی که تو سلولهای اکسل مینویسیم، این شکلیه:
=IF(A,B,C)
تو این فرمول:
- A عبارت شرط هست.
- B خروجی تابع IF در حالتی که A درست باشه
- C خروجی تابع IF برای حالتی که A غلط هست
پس تابع شرطی IF میتونه دو تا خروجی داشته باشه.
خب تابع IFS هم شبیه IF هست با این تفاوت که به جای یه دونه شرط، میشه چندین شرط رو توش تعریف کرد و بعد از هر شرط، خروجی تابع در حالت درست بودن شرط نوشته میشه. یعنی به شکل زیر:
=IFS(A1, B1, A2, B2, A3, B3 , ...)
شرط و خروجی تابع به صورت جفت جفت هست. یعنی A1 درست باشه، B1 تو سلول چاپ میشه و A2 درست باشه، B2 چاپ میشه. اگه بخوایم همین فرمول رو با IF بنویسم، یه فرمول طولانی میشه که شاید تو پرانتز گذاشتن اشتباه پیش بیاد.
خب بریم سراغ مثال: فرض کنید نمره دانش آموزا یا کارکنان اداره تو یه آزمون رو داریم و اگه نمره بیشتر از ۸۰ باشه، میخوایم یه عبارت مثل مطلوب یا Exceeds Expectations چاپ بشه و اگه زیر ۸۰ بود، میخوایم جمله ای به معنی کمتر از انتظار یا در حد انتظار چاپ بشه. خب فرمول رو برای ردیف ۲ مینویسم:
=IF(B2>80, "Exceeds Expectations", "Meets/Below Expectations")
و بعد سلولی که فرمول داره رو با درگ کردن مربع کوچیکی که گوشه راست و پایین سلول هست به سمت پایین، در سلولهای بعدی کپی میکنیم. نتیجه جدول زیر میشه:
خب یه مثال دیگه رو بررسی کنیم که استفاده از IFS روشن بشه: فرض کنید که دنبال آموزش اکسل برای کارهای حسابداری پیشرفته هستید و میخواید بعد از وارد کردن اطلاعات یه جدول که میزان فروش کارکنان هست، برای هر بازه فروش یه درجه مثل A و B و C یا پلاتینیوم و طلایی و نقره ای مشخص کنید. خب این کار با تابع IFS ساده میشه.
شرط اول رو B2>100000 در نظر میگیریم و برای این حالت، خروجی تابع میشه عبارت پلاتینیوم. اگه این شرط برقرار نبود، تابع IFS میره سراغ شرط بعدی که میشه B2>50000 و اگه این حالت برقرار بود، درجه میشه Gold و اگه این حالت هم برقرار نباشه، شرط سوم بررسی میشه. فرمول ساده و روشنی هست:
=IFS(B2>100000, "Platinum", B2>50000, "Gold", B2>10000, "Silver")
خب این فرمول میشه برای ردیف ۲ که تو سلول C2 وارد کردیم. بعد هم این سلول رو با درگ کردن مربع کوچیک گوشه راست و پایین، تو ردیفای بعدی کپی میکنیم. در نتیجه درجه هر یک از کارکنان چاپ میشه.
دقت کنید که تابع IFS حداکثر ۱۲۷ جفت شرط و خروجی رو قبول میکنه نه بیشتر.
روش کار با تابع Sum و SumIF در اکسل
تابع جمع زدن رو قبلاً در مقاله آموزش مقدماتی فرمول نویسی در اکسل توضیح دادیم. این تابع هر عدد و محتوای سلولی که به عنوان آرگومان بهش داده باشیم رو جمع میزنه و فرمول ساده ای داره:
=SUM(A1, A2, ...)
برای محدوده سلولها هم میشه به جای اشاره به تک تک سلولها، از مفهوم رنج سلول استفاده کرد. مثلاً برای جمع زدن سلول A1 الی A1000 لازم نیست ۱۰۰۰ تا آرگومان به تابع بدید که خیلی وقت میگیره. میتونید بین سلول شروع و پایان، دو نقطه بزارید. یعنی به صورت زیر:
=sum(A1:A1000)
به همین سادگی.
خب تابع SUMIF در اکسل چی کار میکنه؟ این تابع عبارتها رو در صورتی که شرط برقرار باشه، جمع میزنه. تابع جمع زدن چند شرطی هم داریم که اسمش یه حرف S بیشتر داره: SUMIFS و این تابع جمع زدن رو در صورتی انجام میده که چندین شرط برقرار باشه.
شکل کلی تابع SUMIFS تو اکسل به این صورته که آرگومان اول میشه سلولهایی که باید جمع زده بشه و آرگومان دوم میشه سلولهایی که برای بررسی شرط، چک میشن و آرگومان سوم هم خود شرط هست.
=SUMIFS(A , B , C)
خب شاید استفاده از SUMIFS پیچیده به نظر بیاد، با یه مثال روش کار روشن میشه. فرض کنید جدول فروش چند نوع کالا رو دارید و میخواین میزان فروش یک نوع خاص کالا رو محاسبه کنید. خب روشنه که باید جمع زدن شرطی رو استفاده کرد.
نوع کالا تو ستون C نوشته شده و مقدار فروش هم تو ستون D هست:
- خب اولین آرگومان تابع SUMIFS، رنج سلولها برای جمع زدن هست که میشه D2:D17.
- دومین آرگومان هم میشه سلولهایی که باید برای چک کردن شرط، بررسی بشن که میشه نوع کالا یعنی سلولهای C2:C17
- آرگومان سوم خود شرط هست، به عبارتی نوع کالا. نوع کالا عبارت متنی هست پس داخل دابل کوتیشن مینویسم یعنی به صورت "Widget Ultra" نوشته میشه.
لذا فرمول جمع زدن فروش کالایی از نوع Widget Ultra به صورت زیر هست:
=SUMIFS(d2:d17 , c2:c17 , "Widget Ultra")
نتیجه کار رو در تصویر زیر میبینید:
تابع SUMIFS هم مثل IFS حداکثر ۱۲۷ شرط قبول میکنه.
تابع جمع زدن شرطی برای انبارداری و کارهای فروشگاهی خیلی مفیده و در آموزش اکسل برای کارهای اداری معمولاً بهش اشاره میشه.
makeuseofساده‌گو
