۷ فرمول کاربردی اکسل که کارتان در دفتر را چند برابر سریع‌تر می‌کند

بیشتر ما اکسل را فقط برای ثبت عدد و ساختن جدول ساده استفاده می‌کنیم؛ در حالی که چند فرمول پایه می‌تواند ساعت‌ها کار دستی را به چند ثانیه تبدیل کند. در این آموزش شهر دیجیتال، ۷ فرمول واقعاً کاربردی اکسل را با مثال قدم‌به‌قدم یاد می‌گیرید؛ فرمول‌هایی که در فروشگاه، دفتر حسابداری، مدرسه و حتی برنامه‌ریزی شخصی هر روز به کارتان می‌آیند.

**نکته مهم:** در اکسل فارسی ممکن است به‌جای ویرگول (,) از نقطه‌ویرگول (;) به‌عنوان جداکننده آرگومان‌ها استفاده شود. اگر فرمول خطا داد، ویرگول‌ها را با نقطه‌ویرگول جایگزین کنید.

۱. SUM و SUMIF؛ جمع ساده و جمع شرطی

پایه‌ترین فرمول اکسل SUM است:

“
=SUM(B2:B20)
“

جمع همه اعداد ستون B از سطر ۲ تا ۲۰. اما وقتی بخواهید فقط فروش «تهران» را جمع کنید، SUMIF به کمک می‌آید:

“
=SUMIF(A2:A20,"تهران",B2:B20)
“

یعنی: در ستون A (شهر) دنبال «تهران» بگرد و فقط فروش‌های مربوط به آن (ستون B) را جمع کن.

قدم‌به‌قدم: روی سلول نتیجه کلیک کنید ← = بنویسید ← فرمول SUMIF را وارد کنید ← اول محدوده شرط (شهرها)، بعد شرط («تهران» داخل گیومه)، بعد محدوده جمع (فروش‌ها) را بدهید ← Enter.

۲. AVERAGE و COUNTIF؛ میانگین و شمارش شرطی

“
=AVERAGE(C2:C20)
“

میانگین نمرات یا فروش‌ها را می‌دهد. برای شمارش تعداد فروش‌های بیشتر از یک میلیون تومان:

“
=COUNTIF(B2:B20,">1000000")
“

COUNTIF می‌شمارد چند سلول شرط شما را دارند. ترکیب آن با AVERAGEIF دقیقاً مثل SUMIF کار می‌کند: =AVERAGEIF(A2:A20,"تهران",B2:B20) میانگین فروش فقط شهر تهران است.

۳. IF؛ تصمیم‌گیری خودکار

فرمول IF مثل یک کارمند دقیق، بر اساس شرط تصمیم می‌گیرد:

“
=IF(B2>=1000000,"عالی","نیاز به تلاش")
“

اگر فروش سلول B2 بیشتر یا مساوی یک میلیون باشد «عالی»، وگرنه «نیاز به تلاش» می‌نویسد. ساختار همیشه همین است: =IF(شرط، نتیجه درست، نتیجه غلط).

مثال کاربردی حقوق: کمیسیون ۱۰٪ برای فروش بالای ۵ میلیون و ۵٪ برای بقیه:

“
=IF(B2>5000000, B2*0.1, B2*0.05)
“

۴. XLOOKUP و VLOOKUP؛ جست‌وجو در جدول

فرض کنید جدول محصولات دارید (کد در ستون A، نام در ستون B، قیمت در ستون C) و می‌خواهید با وارد کردن کد محصول در سلول E2، قیمتش را بیابید. تابع مدرن XLOOKUP این کار را تمیز انجام می‌دهد:

“
=XLOOKUP(E2, A2:A100, C2:C100, "یافت نشد")
“

یعنی: مقدار E2 را در ستون A پیدا کن، قیمت متناظر از ستون C برگردان و اگر نبود بنویس «یافت نشد». برتری بزرگش نسبت به VLOOKUP قدیمی: نیازی نیست ستون جست‌وجو حتماً اول جدول باشد و ستون‌ها را نمی‌شمارد.

**سازگاری:** XLOOKUP فقط در Microsoft 365 و Excel 2021 به بعد (و نسخه آنلاین) هست. اگر اکسل ۲۰۱۶ یا ۲۰۱۹ دارید، از `VLOOKUP` استفاده کنید:
“`
=VLOOKUP(E2, A2:C100, 3, FALSE)
“`
عدد ۳ یعنی «ستون سوم جدول» و FALSE یعنی «تطبیق دقیق».

۵. CONCAT و TEXTJOIN؛ ترکیب متن‌ها

برای ساخت نام کامل از «نام» (A2) و «نام خانوادگی» (B2):

“
=CONCAT(A2," ",B2)
“

اگر چند سلول را با یک جداکننده می‌خواهید به هم بچسبانید، TEXTJOIN تمیزتر است:

“
=TEXTJOIN(" - ", TRUE, C2:C10)
“

این فرمول سلول‌های C2 تا C10 را با « – » به هم می‌چسباند و سلول‌های خالی را نادیده می‌گیرد (آرگومان TRUE یعنی رد کردن خالی‌ها).

۶. TEXT و ROUND؛ قالب‌بندی و گرد کردن اعداد

“
=ROUND(1234567.891, 0)
“

عدد را به نزدیک‌ترین عدد صحیح گرد می‌کند (نتیجه: ۱۲۳۴۵۶۸). عدد دوم تعداد رقم اعشار است؛ اگر ۲ بگذارید دو رقم اعشار نگه می‌دارد.

برای نمایش تاریخ و عدد به شکل دلخواه از TEXT استفاده کنید:

“
=TEXT(TODAY(),"yyyy/mm/dd")
“

تاریخ امروز را به شکل «۱۴۰۵/۰۸/۰۱» نشان می‌دهد. ترکیب TEXT با CONCAT برای ساخت رسید و فاکتور خودکار عالی است.

۷. IFERROR؛ خداحافظی با خطاهای زشت

وقتی XLOOKUP چیزی پیدا نکند، خطای #N/A نشان می‌دهد. IFERROR هر خطایی را با متن دلخواه شما جایگزین می‌کند:

“
=IFERROR(XLOOKUP(E2,A2:A100,C2:C100),"کد محصول اشتباه است")
“

حالا به‌جای خطای ترسناک، یک پیام فارسی واضح می‌بینید. این فرمول را دور هر فرمولی که ممکن است خطا بدهد بپیچید تا فایل‌تان حرفه‌ای به نظر برسد.

مثال عملی: محاسبه کمیسیون فروشندگان در ۲ دقیقه

فرض کنید جدول فروش ماهانه دارید: ستون A نام فروشنده، ستون B مبلغ فروش. می‌خواهید در ستون C کمیسیون هر نفر را حساب کنید (بالای ۱۰ میلیون: ۸٪، بقیه: ۴٪) و در انتها جمع کل کمیسیون‌ها:

1. در C2 بنویسید: =IF(B2>10000000, B2*0.08, B2*0.04) و Enter بزنید.
2. روی گوشه پایین-راست سلول C2 دابل‌کلیک کنید تا فرمول برای همه سطرها کپی شود.
3. در انتهای ستون C بنویسید: =SUM(C2:C50) تا جمع کل کمیسیون‌ها به دست بیاید.
4. اگر خواستید فقط کمیسیون فروشندگان «شعبه مرکزی» (ستون D) را جمع کنید: =SUMIFS(C2:C50, D2:D50, "شعبه مرکزی").

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

جمع‌بندی

با همین ۷ فرمول — SUM و SUMIF، AVERAGE و COUNTIF، IF، XLOOKUP، CONCAT، ROUND و IFERROR — بیش از ۸۰٪ نیازهای روزمره اکسل پوشش داده می‌شود. راز حرفه‌ای شدن، ترکیب همین‌هاست: IF داخل SUMIF، IFERROR دور XLOOKUP و… هرچه بیشتر ترکیب کنید، فایل‌هایتان هوشمندتر می‌شوند.

سوالات متداول

فرمولم خطا می‌دهد ولی همه‌چیز درست به نظر می‌رسد؛ مشکل کجاست؟
اول جداکننده را چک کنید: در اکسل فارسی معمولاً نقطه‌ویرگول (;) است نه ویرگول. دوم، مطمئن شوید متن‌های فارسی داخل گیومه انگلیسی (") باشند.

XLOOKUP روی اکسل من کار نمی‌کند؛ چه کنم؟
XLOOKUP فقط در Microsoft 365، Excel 2021 به بعد و نسخه آنلاین موجود است. در اکسل ۲۰۱۶/۲۰۱۹ از VLOOKUP یا ترکیب INDEX و MATCH استفاده کنید، یا اکسل‌تان را به‌روز کنید.

چرا بعد از تغییر اعداد، نتیجه فرمول عوض نمی‌شود؟
احتمالاً حالت محاسبه روی Manual است. از تب Formulas گزینه Calculation Options را روی Automatic بگذارید.

تفاوت VLOOKUP و XLOOKUP در یک جمله چیست؟
XLOOKUP ساده‌تر است، به‌صورت پیش‌فرض تطبیق دقیق انجام می‌دهد، می‌تواند به چپ هم جست‌وجو کند و پیام «یافت نشد» دلخواه می‌گیرد؛ VLOOKUP فقط به راست جست‌وجو می‌کند و ستون‌ها را باید بشمارید.

برای شروع یادگیری اکسل از کجا شروع کنم؟
از همین ۷ فرمول این مقاله شروع کنید و هر کدام را روی یک فایل واقعی خودتان امتحان کنید. قدم بعدی: قالب‌بندی شرطی (Conditional Formatting) و جدول‌های محوری (PivotTable).