۱۰ تابع پرکاربرد اکسل که هر کارمندی باید بلد باشد (با مثال)
نویسنده: آموزشگاه فنی حرفه‌ای آزاد آراهوش
اگر فقط ده تابع اکسل یاد بگیرید، بیشتر کارهای اداری روزمره را می‌توانید خودتان انجام دهید: جمع و میانگین گرفتن، شرط گذاشتن، شمردن، پیدا کردن قیمت یک کالا از لیست و جلوگیری از خطاهای زشت داخل جدول. در این مقاله همین ده تابع را با یک جدول فروش نمونه توضیح می‌دهیم: هر تابع چه کار می‌کند، فرمولش چه شکلی است، یک مثال واقعی دارد و کدام اشتباه را بیشتر می‌کنند. مطلب برای کارمندان، دانشجویان و کسانی نوشته شده که در درچه اصفهان دوره ICDL یا اکسل می‌گذرانند. فرمول‌ها را داخل خود اکسل امتحان کنید؛ خواندن تنها کافی نیست.
خلاصه: ده تابع در یک نگاه
- SUM: جمع اعداد
- AVERAGE: میانگین
- COUNT و COUNTA: شمردن خانه‌های عددی و خانه‌های پر
- IF: تصمیم‌گیری با شرط
- SUMIF و SUMIFS: جمع با یک یا چند شرط
- COUNTIF: شمردن با شرط
- VLOOKUP: پیدا کردن مقدار از یک لیست
- XLOOKUP: نسخه جدیدتر و راحت‌تر VLOOKUP (در همه نسخه‌ها نیست)
- IFERROR: جایگزین کردن خطا با یک پیام مرتب
- ROUND: گرد کردن عدد
جدول نمونه‌ای که با آن کار می‌کنیم
فرض کنید یک فروشگاه کوچک دارید و فایل اکسلتان ستون‌های زیر را دارد (از ردیف ۲ تا ۱۱ داده‌ها نوشته شده است):
- ستون A: نام محصول (مثلاً کتاب، دفتر، خودکار)
- ستون B: نام فروشنده
- ستون C: تعداد فروخته‌شده
- ستون D: قیمت واحد
- ستون E: مبلغ، که با فرمول =C2*D2 حساب می‌شود
در ستون‌های H و I هم یک لیست قیمت کوچک (نام محصول و قیمت) از ردیف ۲ تا ۶ گذاشته‌ایم که برای توابع جست‌وجو لازم می‌شود. اگر جدول شما شکل دیگری دارد، نام ستون‌ها را در فرمول‌ها عوض کنید.
چهار نکته قبل از نوشتن فرمول
- هر فرمول با علامت = شروع می‌شود. بدون آن اکسل فرمول را فقط به‌صورت متن می‌بیند.
- جداکننده بین ورودی‌ها به تنظیمات سیستم بستگی دارد. در بعضی کامپیوترها باید کاما (,) بگذارید و در بعضی نقطه‌ویرگول (;). صفحه‌های رسمی مایکروسافت هم برای زبان‌های مختلف همین تفاوت را نشان می‌دهند. اگر فرمولی که کپی کرده‌اید خطا داد، اول جداکننده را عوض کنید.
- اعداد فرمول‌ها را انگلیسی بنویسید. حتی اگر داده‌هایتان فارسی است، داخل فرمول از ارقام انگلیسی استفاده کنید.
- علامت دلار ($) مرجع را ثابت می‌کند. وقتی فرمول را به ردیف‌های پایین‌تر می‌کشید، $H$2:$I$6 همان محدوده می‌ماند و جابه‌جا نمی‌شود. این یکی از رایج‌ترین دلیل‌های خراب شدن فرمول‌های جست‌وجو است.
۱. تابع SUM؛ جمع کردن
تابع SUM اعداد یک محدوده را جمع می‌کند. برای جمع کل فروش:
=SUM(E2:E11)
می‌توانید محدوده‌های جدا را هم بدهید، مثلاً =SUM(E2:E5, E8:E11). بیشتر اشتباهات از اینجاست که عددهای داخل جدول به‌صورت متن ذخیره شده‌اند، مثلاً از جای دیگری کپی شده‌اند. در این حالت SUM آن‌ها را جمع نمی‌زند و جمع کل کمتر از واقعیت درمی‌آید. اگر عدد کنار خانه چپ‌چین (یا با یک مثلث سبز کوچک) نمایش داده شد، احتمالاً متن است.
۲. تابع AVERAGE؛ میانگین
=AVERAGE(E2:E11)
میانگین مبلغ فروش هر ردیف را می‌دهد. نکته مهم این است که خانه‌های خالی در میانگین نادیده گرفته می‌شوند، اما خانه‌ای که عدد صفر دارد حساب می‌شود. پس اگر برای یک فروش هنوز مبلغی ثبت نشده، آن را خالی بگذارید نه صفر، وگرنه میانگین پایین می‌آید.
۳. توابع COUNT و COUNTA؛ شمردن
COUNT فقط خانه‌هایی را می‌شمارد که عدد دارند و COUNTA هر خانه‌ای را که خالی نیست:
=COUNT(C2:C11) =COUNTA(A2:A11)
یعنی اولی تعداد ردیف‌هایی است که تعداد فروش‌شان ثبت شده و دومی تعداد ردیف‌هایی که نام محصول دارند. اگر این دو عدد با هم فرق کردند، یعنی یک جایی داده ناقص است؛ همین را می‌شود به‌عنوان یک چک ساده استفاده کرد.
۴. تابع IF؛ تصمیم‌گیری
IF یک شرط را بررسی می‌کند و بسته به درست یا غلط بودنش، یکی از دو جواب را می‌دهد:
=IF(C2>=10, "عمده", "خرده")
اگر تعداد فروش ۱۰ یا بیشتر باشد، عبارت «عمده» و در غیر این صورت «خرده» نوشته می‌شود. متن‌ها همیشه داخل گیومه می‌روند. برای چند حالت می‌توانید IF را داخل IF دیگری بگذارید، اما از سه چهار لایه بیشتر نروید؛ فرمول‌های خیلی تو در تو را بعداً هیچ‌کس، حتی خودتان، نمی‌تواند بخواند.
۵. توابع SUMIF و SUMIFS؛ جمع با شرط
اگر بخواهید فقط فروش یک فروشنده را جمع کنید:
=SUMIF(B2:B11, "علی", E2:E11)
یعنی «در ستون B دنبال علی بگرد و مبلغ‌های متناظرش را از ستون E جمع کن». برای چند شرط هم‌زمان از SUMIFS استفاده کنید، که ترتیبش فرق دارد و ستون جمع در ابتدا می‌آید:
=SUMIFS(E2:E11, B2:B11, "علی", A2:A11, "کتاب")
این فرمول فقط مبلغ کتاب‌هایی را جمع می‌کند که علی فروخته است. هر وقت دیدید ترتیب ورودی‌ها گیج‌کننده است، یادتان باشد SUMIF اول محدوده شرط را می‌گیرد و SUMIFS اول محدوده جمع را.
۶. تابع COUNTIF؛ شمردن با شرط
=COUNTIF(B2:B11, "علی")
تعداد ردیف‌هایی را می‌دهد که فروشنده‌شان علی است. برای شرط عددی هم می‌شود نوشت =COUNTIF(C2:C11, ">=10")، یعنی تعداد فروش‌هایی که حداقل ۱۰ عدد بوده‌اند. شرط عددی را باید داخل گیومه و همراه عملگر بنویسید.
۷. تابع VLOOKUP؛ پیدا کردن از یک لیست
VLOOKUP یک مقدار را در ستون اول یک جدول پیدا می‌کند و از ستون دیگر همان سطر جواب می‌آورد. اگر لیست قیمت در H2:I6 باشد، برای پیدا کردن قیمت محصول ردیف ۲:
=VLOOKUP(A2, $H$2:$I$6, 2, FALSE)
چهار ورودی دارد: چه چیزی را بگردم، در کدام جدول، جواب در چندمین ستون جدول است، و آخری که FALSE یعنی جست‌وجوی دقیق. اگر آخری را فراموش کنید و TRUE یا هیچ چیز ننویسید، اکسل جست‌وجوی تقریبی می‌کند و گاهی جواب اشتباهی می‌دهد. نکته دوم این است که VLOOKUP فقط از چپ به راست نگاه می‌کند؛ یعنی ستونی که دنبالش می‌گردید باید اولین ستون جدول باشد.
۸. تابع XLOOKUP؛ نسخه جدیدتر
=XLOOKUP(A2, $H$2:$H$6, $I$2:$I$6, "یافت نشد")
به‌جای شماره ستون، دو محدوده می‌دهید: کجا بگردم و جواب را از کجا بیاورم. ورودی چهارم هم پیامی است که اگر چیزی پیدا نشد نشان داده می‌شود. طبق توضیحات رسمی مایکروسافت، XLOOKUP می‌تواند به چپ هم نگاه کند و در VLOOKUP این ممکن نیست. اما یک محدودیت جدی دارد: طبق همان صفحه، XLOOKUP در اکسل ۲۰۱۶ و ۲۰۱۹ وجود ندارد و در اکسل ۲۰۲۱ و ۲۰۲۴ و نسخه‌های ۳۶۵ هست. اگر فایل را برای کسی می‌فرستید که نسخه قدیمی‌تر دارد، با VLOOKUP امن‌تر هستید.
۹. تابع IFERROR؛ خطاها را مرتب کنید
وقتی VLOOKUP چیزی پیدا نکند، خطای #N/A می‌دهد. IFERROR جای آن خطا یک مقدار مرتب می‌گذارد:
=IFERROR(VLOOKUP(A2, $H$2:$I$6, 2, FALSE), "نامشخص")
یک هشدار: IFERROR هر نوع خطایی را پنهان می‌کند، حتی خطای ناشی از اشتباه خودتان. پس اول فرمول را بدون IFERROR بنویسید و مطمئن شوید درست کار می‌کند، بعد روی آن بگذارید.
۱۰. تابع ROUND؛ گرد کردن
فرض کنید برای مشتریان ۱۰ درصد تخفیف می‌دهید و می‌خواهید مبلغ بعد از تخفیف عدد صحیح باشد:
=ROUND(E2*0.9, 0)
ورودی دوم تعداد رقم اعشار است؛ صفر یعنی عدد صحیح. نکته‌ای که ROUND را از «فقط نمایش کمتر اعشار» جدا می‌کند این است که ROUND خود عدد را عوض می‌کند، در حالی که تنظیم فرمت خانه فقط ظاهر را تغییر می‌دهد و عدد اصلی با اعشارش در محاسبه‌های بعدی هنوز هست.
چند ترکیب که کارتان را راحت می‌کند
بعد از ده تابع بالا، این ترکیب‌ها بیشترین استفاده را دارند:
- IFERROR + VLOOKUP: برای قیمت‌گذاری خودکار بدون خطای #N/A.
- SUMIFS + شرط تاریخ یا شرط دوم: برای گزارش ماهانه یا فروش هر فروشنده در هر محصول.
- IF + COUNTIF: مثلاً بنویسید «اگر نام این مشتری بیش از یک بار تکرار شده بود، پیام بده»؛ برای پیدا کردن تکراری‌ها.
اگر ورودی‌های یک فرمول زیاد شد، آن را به چند ستون کمکی بشکنید. فرمولی که سه ستون کمکی دارد از یک فرمول غول‌پیکر بهتر است؛ چون خطایابی‌اش ساده‌تر است.
اشتباه‌های رایج که وقت می‌گیرند
- عدد متنی: عددی که به‌صورت متن ذخیره شده در جمع و میانگین حساب نمی‌شود.
- فراموش کردن ثابت کردن محدوده: جدول جست‌وجو را باید با علامت $ قفل کنید.
- جست‌وجوی تقریبی در VLOOKUP: آخرین ورودی را همیشه FALSE بگذارید، مگر اینکه عمداً بخواهید.
- فاصله یا نویسه اضافی در نام‌ها: «علی» با «علی » (با فاصله پایان) برای اکسل دو چیز متفاوت است و فرمول‌های شرطی را به‌هم می‌ریزد.
- استفاده از XLOOKUP در فایلی که قرار است دست نسخه قدیمی‌تر بیفتد.
- پنهان کردن خطا با IFERROR قبل از اینکه فرمول را درست کنید.
یک تمرین سه‌مرحله‌ای
برای اینکه این توابع در ذهنتان بنشینند، همین جدول فروش را بسازید و سه کار انجام دهید:
- مبلغ هر ردیف را با فرمول ضرب حساب کنید و جمع، میانگین و تعداد ردیف‌ها را بگیرید.
- ستون «نوع فروش» را با IF بسازید، و بعد جمع فروش هر فروشنده را با SUMIF بنویسید.
- لیست قیمت را در ستون‌های H و I بگذارید و قیمت هر محصول را یک بار با VLOOKUP و یک بار با XLOOKUP بیاورید؛ بعد IFERROR را اضافه کنید.
اگر این سه مرحله را بدون نگاه کردن به مقاله انجام دادید، ده تابع را یاد گرفته‌اید.
اکسل در ICDL و مسیر شغلی
اکسل یکی از هفت مهارت ICDL جامع است. اگر دنبال مدرک رسمی هستید، مدرک ICDL چیست و چه کاربردی دارد؟ و ICDL درجه ۱ و ۲ چه شد؟ را بخوانید و بعد بررسی کنید مدرک را از کجا بگیرید؛ برای اطمینان از رسمی بودن مدرک هم راهنمای مدرک فنی و حرفه‌ای چیست و چطور استعلام کنیم؟ را ببینید. اگر نمی‌دانید اکسل در چه مسیر شغلی‌ای به کارتان می‌آید، نقشه راه ورود به دنیای IT و کامپیوتر پاسخ این سؤال است. و وقتی اکسل برای کارتان کم آمد، آموزش پایتون از صفر راه بعدی برای خودکار کردن کارهای تکراری است.
آموزش اکسل و ICDL در درچه با آراهوش
آموزشگاه فنی حرفه‌ای آزاد آراهوش در درچه با مجوز رسمی سازمان آموزش فنی و حرفه‌ای کشور فعالیت می‌کند و دوره‌هایش همراه با گواهینامه رسمی فنی و حرفه‌ای است؛ مدرک، مثل هر مرکز دیگری، بعد از قبولی در آزمون سازمان صادر می‌شود. می‌توانید درباره آکادمی آراهوش بخوانید، دوره‌های آموزش کامپیوتر آراهوش را ببینید و برای رفتن به آموزشگاه آدرس آموزشگاه کامپیوتر آراهوش در اصفهان را باز کنید. مقالات دیگر ما هم در مقالات آموزشی آراهوش هستند.
- آدرس: اصفهان، درچه، خیابان آیت‌الله درچه‌ای، قبل از کوچه ۶، آموزشگاه آراهوش
- تلفن: ۳۳۷۶۳۳۷۷
- موبایل: ۰۹۹۱۳۰۵۹۲۴۲
- تلگرام، اینستاگرام و ایتا: @arahoosh
برای اطلاع از زمان شروع و هزینه دوره‌ها با ما تماس بگیرید.
سؤالات متداول
بهترین راه یادگیری فرمول‌نویسی اکسل چیست؟ روی یک جدول واقعی کار کنید، نه فقط مثال‌های کتاب. جدول خودتان، مثلاً هزینه‌های ماهانه یا فهرست کارهای اداری، بهترین زمینه تمرین است.
فرمولی که از اینترنت کپی کردم خطا می‌دهد. چرا؟ معمولاً یکی از سه دلیل دارد: جداکننده کاما یا نقطه‌ویرگول با تنظیمات سیستم شما نمی‌خواند، گیومه‌ها درست کپی نشده‌اند، یا نام ستون و محدوده مال جدول شماست نه جدول مثال.
VLOOKUP یا XLOOKUP؟ اگر نسخه اکسل شما XLOOKUP دارد (۲۰۲۱ به بعد و ۳۶۵)، معمولاً راحت‌تر است. اگر فایل باید با نسخه ۲۰۱۶ یا ۲۰۱۹ هم باز شود، VLOOKUP امن‌تر است.
چرا SUM جمع درست نمی‌زند؟ احتمالاً بعضی اعداد به‌صورت متن ذخیره شده‌اند. آن‌ها را به عدد تبدیل کنید و دوباره امتحان کنید.
اکسل در ICDL چقدر مهم است؟ اکسل یکی از هفت مهارت ICDL جامع است. سرفصل دقیق آزمون را از اطلاعیه رسمی یا مرکز آموزشی بگیرید.
در درچه کجا اکسل یاد بگیرم؟ آموزشگاه آراهوش در درچه دوره ICDL دارد که اکسل جزو آن است. از صفحه دوره‌ها شروع کنید یا تماس بگیرید.
چقدر طول می‌کشد تا اکسل را جدی یاد بگیرم؟ عدد قطعی نداریم؛ به تمرین و هدف شما بستگی دارد. مدت دوره و سرفصل را از مرکز مورد نظر بپرسید.
از کجا بفهمم اکسل به کارم می‌آید؟ اگر در کارتان با فهرست‌ها، لیست قیمت، حقوق یا گزارش فروش سروکار دارید، بله. برای سنجیدن سطح خودتان با آراهوش در درچه تماس بگیرید.
جمع‌بندی
ده تابع SUM، AVERAGE، COUNT و COUNTA، IF، SUMIF و SUMIFS، COUNTIF، VLOOKUP، XLOOKUP، IFERROR و ROUND بیشتر نیاز اکسل کارمند معمولی را پوشش می‌دهند. مهم‌تر از حفظ کردن فرمول‌ها این است که بدانید هر تابع چه سؤالی را جواب می‌دهد. قبل از اینکه فایلی را به کسی بفرستید، نسخه اکسل او را در نظر بگیرید، و قبل از اینکه خطایی را پنهان کنید، مطمئن شوید علتش را فهمیده‌اید. اگر دنبال کلاس اکسل و ICDL در درچه هستید، با آراهوش تماس بگیرید: ۳۳۷۶۳۳۷۷ یا ۰۹۹۱۳۰۵۹۲۴۲.
منابع
- مایکروسافت: تابع XLOOKUP: نحوه کار، مقایسه با VLOOKUP و نسخه‌هایی که XLOOKUP را دارند.
- مایکروسافت: فهرست توابع اکسل: مرجع رسمی همه توابع و نسخه‌ای که هر تابع در آن معرفی شده است.