آموزش فرمول نویسی در اکسل: راهنمای کامل از صفر

در این مقاله فرمول نویسی در اکسل را از مفاهیم پایه تا توابع پیشرفته مانند SUM، IF و VLOOKUP یاد می‌گیرید. با مثال‌های عملی، آدرس‌دهی سلول‌ها و رفع خطاهای رایج، سرعت و دقت کار خود را در اکسل افزایش دهید.

پیشخوانک را به منابع منتخب گوگل اضافه کنید افزودن به منابع گوگل
تلگرام واتساپ

برای فرمول‌نویسی در اکسل، توی یک سلول علامت «=» بذارین، فرمول رو بنویسین و Enter رو بزنین. برای جمع دو سلول، کافیه =A1+B1 رو وارد کنین. اکسل ضرب رو قبل از جمع انجام می‌ده. دور بخشی که می‌خواین اول حساب کنین، پرانتز بذارین.

وقتی فرمول رو یک ردیف پایین‌تر کپی می‌کنین، ارجاع نسبی A2 به A3 تغییر می‌کنه. اگه می‌خواین همیشه نرخ تخفیف رو از یک سلول بگیرین، از ارجاع مطلق $B$1 استفاده کنین. توی ارجاع ترکیبی A$1 هم فقط شمارهٔ ردیف ثابته.

برای پیدا کردن موجودی یک کالا، کدش رو با XLOOKUP توی جدول جست‌وجو کنین. این تابع به‌طور پیش‌فرض دنبال تطابق دقیق می‌گرده. می‌تونین موجودی رو حتی از ستونی برگردونین که سمت چپ ستون کد کالاست. قبل از فرستادن فایل، پشتیبانی از XLOOKUP رو توی نسخهٔ اکسل گیرنده بررسی کنین.

با FILTER می‌تونین فقط سفارش‌های ارسال‌نشده رو از یک جدول بیرون بکشین. اکسل نتیجه رو از سلول فرمول به سلول‌های مجاور گسترش می‌ده، به شرطی که جا خالی باشه. با تغییر وضعیت سفارش‌ها، اکسل فهرست رو هم به‌روز می‌کنه.

وقتی چند جای یک فرمول به نتیجهٔ یک محاسبه نیاز دارین، با LET به اون نتیجه اسم بدین. بعد می‌تونین همون اسم رو توی بخش‌های مختلف فرمول به کار ببرین. با LAMBDA هم می‌تونین بدون VBA تابع دلخواهتون رو بسازین و توی همون فایل دوباره ازش استفاده کنین.

نکات کلیدی این مقاله:

  • ارجاع سلولی با علامت $ مشخص کنین کدوم بخش آدرس سلول هنگام کپی ثابت بمونه.
  • XLOOKUP کد کالا رو پیدا کنین و موجودی رو از ستون دلخواه برگردونین.
  • FILTER ردیف‌هایی رو که شرط شما رو دارن، با یک فرمول نمایش بدین.

فرمول در اکسل چیست و چه کاربردی دارد؟

فرمول در اکسل چیست و چه کاربردی دارد؟

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

فرمول چه فرقی با عدد ساده دارد؟

اگه عدد ۱۰۰ را مستقیم تایپ کنین، اکسل فقط یک مقدار ثابت ذخیره می‌کنه. اما وقتی بنویسین =A1*2، اکسل هر بار مقدار A1 را می‌خونه و دوباره حساب می‌کنه.

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

کاربردهای روزمره فرمول‌ها

  • محاسبه مبلغ فاکتور، تخفیف و مالیات
  • جمع هزینه‌های ماهانه خانه یا فروشگاه
  • میانگین‌گیری نمره یا فروش
  • پیدا کردن قیمت یک کالا از میان صدها ردیف
  • شمارش سفارش‌های هر شهر

فرمول و تابع یکی هستند؟

نه کاملاً. فرمول کل عبارت است، مثل =SUM(A1:A5)*2. تابع یک ابزار آماده داخل فرمول است، مثل SUM. هر فرمول می‌تونه چند تابع، عملگر و ارجاع سلولی داشته باشه.

در ادامه از ساده‌ترین فرمول شروع می‌کنیم و قدم‌به‌قدم به XLOOKUP، آرایه‌های پویا، LET و LAMBDA می‌رسیم. آخر مقاله هم با یک جدول فروش، همه را تمرین می‌کنیم.

نمایش یک فرمول ساده در نوار فرمول اکسل و نتیجه آن در سلول
فرمول در نوار فرمول دیده می‌شود و نتیجه در سلول

چطور اولین فرمول را در اکسل بنویسیم؟

چطور اولین فرمول را در اکسل بنویسیم؟

برای نوشتن فرمول، سلول خالی را انتخاب کنین و اول علامت مساوی بزنین. بعد عدد، آدرس سلول یا تابع را بنویسین و Enter بزنین. اکسل نتیجه را همان لحظه نشون می‌ده. مثلاً =B2*C2 قیمت را در تعداد ضرب می‌کنه.

مراحل نوشتن فرمول

  1. روی سلولی که نتیجه باید آنجا بیاد کلیک کنین.
  2. علامت = را تایپ کنین.
  3. روی سلول اول کلیک کنین تا آدرسش وارد بشه.
  4. عملگر مثل * یا + بزنین.
  5. سلول بعدی را انتخاب کنین و Enter بزنین.

مثال تخفیف

فرض کنین قیمت هر کالا در B2 برابر ۵۶٬۰۰۰ تومان، تعداد در C2 برابر ۲ و درصد تخفیف در D2 برابر ۵٪ است. ستون‌ها را اینطور پر کنین:

  • جمع کل: =B2*C2 می‌شه ۱۱۲٬۰۰۰ تومان
  • مبلغ تخفیف: =B2*C2*D2 می‌شه ۵٬۶۰۰ تومان
  • مبلغ قابل پرداخت: =B2*C2-E2 می‌شه ۱۰۶٬۴۰۰ تومان

سلول D2 را حتماً با فرمت درصد تنظیم کنین. اگه ۵ را بدون فرمت درصد بنویسین، اکسل آن را ۵ برابر حساب می‌کنه، نه پنج صدم.

اولویت عملگرها

اکسل اول پرانتز، بعد توان، بعد ضرب و تقسیم و در آخر جمع و تفریق را حساب می‌کنه. پس =2+3*4 برابر ۱۴ است، نه ۲۰. هر جا مطمئن نبودین، پرانتز بذارین.

اگه کدهای شما با صفر شروع می‌شن و صفر پاک می‌شه، راهنمای نمایش صفر قبل از عدد در اکسل مشکل را حل می‌کنه.

ارجاع نسبی، مطلق و مختلط هنگام کپی فرمول چه فرقی دارند؟

ارجاع نسبی، مطلق و مختلط هنگام کپی فرمول چه فرقی دارند؟

ارجاع نسبی هنگام کپی تغییر می‌کنه، ارجاع مطلق ثابت می‌مونه و ارجاع مختلط فقط ستون یا ردیف را قفل می‌کنه. علامت دلار قفل را ایجاد می‌کنه. A1 نسبی، $A$1 مطلق و $A1 مختلط است.

ارجاع نسبی

اگه در D2 بنویسین =B2*C2 و آن را به D3 بکشین، فرمول خودش می‌شه =B3*C3. برای جدول‌های ردیفی همین رفتار دقیقاً چیزی است که می‌خواین.

ارجاع مطلق

فرض کنین نرخ مالیات نمونه ۱۰٪ را در G1 نوشتین. فرمول =E2*$G$1 را هر جا کپی کنین، همیشه به G1 اشاره می‌کنه. بدون دلار، ردیف دوم به G2 می‌ره و جواب غلط می‌ده.

ارجاع مختلط

در $A1 ستون A قفل است ولی ردیف آزاد. در A$1 برعکس است. این حالت برای جدول ضرب و جدول‌های دوبعدی خیلی کاربرد داره.

میان‌بر F4

موقع ویرایش فرمول، مکان‌نما را روی آدرس ببرین و F4 بزنین. اکسل به‌ترتیب بین چهار حالت A1، $A$1، A$1 و $A1 جابه‌جا می‌شه. در بعضی لپ‌تاپ‌ها باید Fn را هم همراهش بزنین.

نوعنمونههنگام کپی
نسبیA1ستون و ردیف تغییر می‌کنند
مطلق$A$1هیچ‌کدام تغییر نمی‌کنند
مختلط$A1فقط ردیف تغییر می‌کند

جمع، میانگین و شمارش با توابع پایه اکسل

جمع، میانگین و شمارش با توابع پایه اکسل

برای جمع از SUM، برای میانگین از AVERAGE و برای شمارش عددها از COUNT استفاده کنین. هر سه یک محدوده سلولی می‌گیرن، مثل =SUM(E2:E7). کمترین و بیشترین مقدار را هم با MIN و MAX پیدا می‌کنین.

توابع پرکاربرد

  • SUM: جمع عددها
  • AVERAGE: میانگین
  • COUNT: شمارش سلول‌های عددی
  • COUNTA: شمارش سلول‌های غیرخالی، شامل متن
  • MIN و MAX: کمترین و بیشترین

یک اشتباه رایج با SUM

نوشتن =SUM(A2+B2) کار می‌کنه، ولی معنا نداره. خود علامت جمع کار را انجام می‌ده. شکل درست =SUM(A2:B2) یا =SUM(A2,B2) است.

اگه فقط دو سلول را جمع می‌کنین، =A2+B2 کافیه. برای تفریق هم از منفی استفاده کنین. توضیح کامل‌تر در آموزش جمع و تفریق در اکسل آمده.

نتیجه روی داده نمونه

فرض کنین مبلغ شش فروش در E2 تا E7 باشد: ۶۰، ۱۰۰، ۶۰، ۲۰، ۳۰ و ۸ میلیون تومان. نتیجه‌ها این می‌شه:

  • جمع کل: ۲۷۸ میلیون تومان
  • میانگین: حدود ۴۶٫۳ میلیون تومان
  • بیشترین فروش: ۱۰۰ میلیون تومان
  • کمترین فروش: ۸ میلیون تومان
  • تعداد فروش‌ها با COUNT: ۶

میان‌بر Alt و علامت مساوی در ویندوز، SUM را خودکار زیر ستون می‌ذاره. فقط یک بار محدوده پیشنهادی را چک کنین.

چطور در اکسل شرط بگذاریم و با یک یا چند شرط جمع بزنیم یا بشماریم؟

چطور در اکسل شرط بگذاریم و با یک یا چند شرط جمع بزنیم یا بشماریم؟

برای شرط ساده از IF استفاده کنین. برای جمع و شمارش با یک شرط SUMIF و COUNTIF را بزنین. وقتی چند شرط دارین، SUMIFS و COUNTIFS لازمه. فرق اصلی اینه که SUMIF فقط یک شرط می‌پذیره.

تابع IF

ساختارش سه بخش داره: شرط، نتیجه درست و نتیجه نادرست. مثلاً =IF(E2>=50000000,"عمده","خرد") فروش‌های بالای ۵۰ میلیون تومان را عمده برچسب می‌زنه. متن‌ها همیشه داخل گیومه می‌رن.

SUMIF و COUNTIF

نحو SUMIF این است: SUMIF(range, criteria, [sum_range]). در جدول نمونه، فروش تهران این‌طور حساب می‌شه:

=SUMIF(B2:B7,"تهران",E2:E7) برابر ۱۶۸ میلیون تومان است. =COUNTIF(B2:B7,"تهران") هم عدد ۳ را برمی‌گردونه.

SUMIFS و COUNTIFS

در SUMIFS اول محدوده جمع می‌آد، بعد جفت‌های محدوده و شرط. دقت کنین ترتیبش با SUMIF فرق داره:

=SUMIFS(E2:E7,A2:A7,"موبایل",B2:B7,"تهران") برابر ۱۰۰ میلیون تومان است. =COUNTIFS(A2:A7,"موبایل",B2:B7,"تهران") عدد ۱ را می‌ده.

شرط‌های عددی

  • بزرگ‌تر از: ">50000000"
  • مساوی یا کمتر: "<=20000000"
  • نامساوی: "<>تهران"

اگه شرط را در یک سلول نوشتین، مثل F1، ارجاع بدین: =SUMIF(B2:B7,F1,E2:E7). این‌طوری با عوض کردن F1، گزارش هم عوض می‌شه.

جست‌وجو در جدول با XLOOKUP و تفاوت آن با VLOOKUP

جست‌وجو در جدول با XLOOKUP و تفاوت آن با VLOOKUP

XLOOKUP یک مقدار را در یک ستون پیدا می‌کنه و مقدار هم‌ردیفش را از ستون دیگر برمی‌گردونه. برخلاف VLOOKUP، هم چپ‌به‌راست و هم راست‌به‌چپ کار می‌کنه و به‌صورت پیش‌فرض تطابق دقیق دارد. در اکسل ۲۰۲۱ و Microsoft 365 در دسترس است.

ساختار تابع

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

سه آرگومان اول کافی است: چه چیزی را، کجا و از کدام ستون برگردان. بقیه اختیاری هستند.

یک مثال عملی

فرض کنین در F2 نام «هدفون» را نوشتین. فرمول زیر مبلغ اولین فروش هدفون را برمی‌گردونه:

=XLOOKUP(F2,A2:A7,E2:E7,"یافت نشد")

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

تفاوت با VLOOKUP

  • VLOOKUP فقط از چپ به راست جست‌وجو می‌کنه.
  • VLOOKUP عدد ستون می‌خواد و با اضافه شدن ستون خراب می‌شه.
  • XLOOKUP مستقیم ستون نتیجه را می‌گیره.
  • XLOOKUP پیام جایگزین برای حالت پیدا نشدن داره.

اگه فایل را برای کسی با اکسل ۲۰۱۹ یا قدیمی‌تر می‌فرستین، VLOOKUP هنوز امن‌تره. در بخش سازگاری دوباره بهش برمی‌گردیم.

فیلتر، مرتب‌سازی و خلاصه‌کردن داده‌ها با آرایه‌های پویا

فیلتر، مرتب‌سازی و خلاصه‌کردن داده‌ها با آرایه‌های پویا

آرایه‌های پویا با یک فرمول، چندین سلول را پر می‌کنن. FILTER ردیف‌ها را فیلتر می‌کنه، SORT مرتب می‌کنه و UNIQUE مقدارهای یکتا را می‌ده. نتیجه در سلول‌های مجاور «سرریز» می‌شه و با تغییر داده‌ها خودکار به‌روز می‌شه.

توابع اصلی

  • =FILTER(A2:E7,B2:B7="تهران"): فقط ردیف‌های تهران
  • =SORT(A2:E7,5,-1): مرتب‌سازی نزولی بر اساس ستون پنجم
  • =UNIQUE(B2:B7): فهرست شهرهای یکتا

می‌تونین ترکیبشون کنین. =SORT(FILTER(A2:E7,B2:B7="تهران"),5,-1) فروش‌های تهران را از بیشترین به کمترین نشون می‌ده.

حذف موارد تکراری

برای حذف تکراری‌ها یا از ابزار Remove Duplicates در تب Data استفاده کنین یا از UNIQUE. با یک فرمول ساده معمولی نمی‌شه این کار را کرد.

توابع جدیدتر

TAKE، DROP و CHOOSECOLS ردیف‌ها و ستون‌ها را برش می‌زنن. GROUPBY و PIVOTBY هم خلاصه‌سازی شبیه PivotTable را با فرمول انجام می‌دن. مثلاً =GROUPBY(B2:B7,E2:E7,SUM) جمع فروش هر شهر را می‌ده.

دسترسی این توابع به نسخه و کانال به‌روزرسانی بستگی داره. قبل از استفاده، در اکسل خودتون تایپشون کنین و ببینین پیشنهاد می‌شن یا نه.

خطای SPILL

اگه جلوی محدوده سرریز سلول پر باشه، خطای #SPILL! می‌گیرین. سلول‌های مسیر را خالی کنین. اگه داده‌هاتون را در قالب جدول اکسل نگه می‌دارین، ارجاع‌ها خودکار با رشد داده‌ها بزرگ می‌شن.

خواناتر کردن فرمول‌ها با LET و ساخت تابع سفارشی با LAMBDA

خواناتر کردن فرمول‌ها با LET و ساخت تابع سفارشی با LAMBDA

LET به بخش‌های یک فرمول نام می‌ده تا یک محاسبه را فقط یک بار بنویسین و بخونین. LAMBDA هم اجازه می‌ده فرمول خودتون را مثل تابع جدید صدا بزنین، آن هم بدون نوشتن VBA. هر دو فرمول‌های طولانی را کوتاه‌تر می‌کنن.

نحو LET

اول جفت‌های نام و مقدار می‌آن و آخر خود محاسبه. مثال:

=LET(total,C2*D2,total-total*0.05)

اینجا total یک بار حساب می‌شه و دو بار استفاده می‌شه. نام‌ها را انگلیسی و بدون فاصله انتخاب کنین تا کمتر خطا بگیرین.

ساخت تابع با LAMBDA

فرض کنین مبلغ را با مالیات نمونه ۱۰٪ زیاد می‌کنین و این کار را بارها تکرار می‌کنین. می‌تونین یک تابع بسازین.

  1. از تب Formulas گزینه Name Manager را باز کنین.
  2. روی New بزنین و نام را مثلاً WITHTAX بذارین.
  3. در کادر Refers to بنویسین: =LAMBDA(price,rate,price*(1+rate))
  4. تایید کنین و در سلول بنویسین: =WITHTAX(E2,0.1)

برای ۶۰ میلیون تومان، نتیجه ۶۶ میلیون تومان می‌شه.

چه موقع استفاده کنیم؟

  • یک محاسبه چند بار در یک فرمول تکرار شده.
  • فرمول بیش از یک خط شده و خوندنش سخته.
  • همکارها هم باید همین منطق را استفاده کنن.

یادتون باشه LET و LAMBDA در نسخه‌های قدیمی اکسل کار نمی‌کنن. اگه فایل را برای دیگران می‌فرستین، حتماً سازگاری را بسنجین.

تابع COPILOT در اکسل چه کاربردها و محدودیت‌هایی دارد؟

تابع COPILOT در اکسل چه کاربردها و محدودیت‌هایی دارد؟

تابع COPILOT هوش مصنوعی را مستقیم از یک سلول صدا می‌زنه. شما یک دستور متنی و در صورت نیاز یک محدوده داده می‌دین و نتیجه در سلول می‌آد. این تابع به مجوز Copilot نیاز داره و برای کارهای معنایی مناسبه، نه محاسبات مالی دقیق.

کاربردهای مناسب

  • دسته‌بندی نظرهای مشتری به مثبت، منفی و خنثی
  • خلاصه کردن متن‌های طولانی در یک خط
  • پیشنهاد برچسب برای ردیف‌های بدون دسته
  • ایده‌پردازی اولیه برای نام محصول

مثال: =COPILOT("Classify sentiment", A2:A20) برای هر نظر یک برچسب برمی‌گردونه.

محدودیت‌های مهم

COPILOT فقط به پرامپت و داده‌ای که شما بهش می‌دین دسترسی داره. به بقیه فایل نگاه نمی‌کنه. خروجی هم قطعی نیست و با هر بار محاسبه ممکنه کمی فرق کنه.

  • برای جمع، مالیات و حقوق از SUM و فرمول‌های عادی استفاده کنین.
  • خروجی را قبل از استفاده بازبینی کنین.
  • داده‌های حساس مشتری را بی‌ملاحظه به آن ندین.
  • سقف تعداد فراخوانی و دسترسی به مجوز و نسخه بستگی داره.

سازگاری

فایل‌هایی که از COPILOT استفاده می‌کنن، در نسخه‌های قدیمی اکسل خطا می‌دن. جزئیات انتشار و محدودیت‌ها را پیش از استفاده کاری در صفحه پشتیبانی مایکروسافت چک کنین، چون این قابلیت هنوز در حال تغییره.

رفع خطاهای رایج فرمول و مشکل جداکننده‌ها در اکسل

رفع خطاهای رایج فرمول و مشکل جداکننده‌ها در اکسل

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

خطاهای رایج

  • #VALUE!: نوع داده نادرست، مثلاً جمع عدد با متن
  • #DIV/0!: تقسیم بر صفر یا سلول خالی
  • #NAME?: غلط املایی در نام تابع، یا تابع ناشناخته
  • #REF!: ارجاع به سلولی که حذف شده
  • #N/A: مقدار جست‌وجو پیدا نشده

برای رفع تفصیلی خطای اول، راهنمای رفع خطای VALUE در اکسل را ببینین. برای پنهان کردن خطای مورد انتظار هم از IFERROR کمک بگیرین.

مشکل جداکننده‌ها

در بعضی سیستم‌ها =SUM(A1,B1) کار می‌کنه و در بعضی دیگر باید =SUM(A1;B1) بنویسین. این رفتار از تنظیمات Region در ویندوز می‌آد.

  1. اول جداکننده دیگر را امتحان کنین.
  2. در ویندوز Region settings را باز کنین.
  3. به Additional settings بروین و List separator را ببینین.
  4. اکسل را ببندین و دوباره باز کنین.

کپی کردن فرمول از وب هم گاهی نقل‌قول‌های هوشمند یا رقم‌های فارسی را می‌آره. اگه عددها متن شدن، راهنمای فارسی کردن اعداد در اکسل کمک می‌کنه.

چطور سازگاری فرمول‌ها را با نسخه‌های مختلف اکسل بررسی کنیم؟

چطور سازگاری فرمول‌ها را با نسخه‌های مختلف اکسل بررسی کنیم؟

برای بررسی سازگاری، از مسیر File، Info، Check for Issues و Check Compatibility استفاده کنین. این ابزار مشکلات فایل در نسخه‌های قدیمی را فهرست می‌کنه. بهترین راه این است که بدونین هر تابع از چه نسخه‌ای شروع شده.

کدام توابع حساس‌ترند؟

  • XLOOKUP: اکسل ۲۰۲۱ و Microsoft 365
  • FILTER، SORT و UNIQUE: همین نسخه‌های جدید
  • LET و LAMBDA: نسخه‌های جدید
  • COPILOT: نیازمند مجوز Copilot
  • SUM، IF و SUMIFS: تقریباً همه نسخه‌ها

اگه همکارتون اکسل ۲۰۱۹ یا قدیمی‌تر داره، فرمول‌های جدید در فایل شما با خطا یا با پیشوند _xlfn نمایش داده می‌شن.

راهکارهای عملی

  1. قبل از ارسال، نسخه اکسل گیرنده را بپرسین.
  2. Check Compatibility را اجرا کنین.
  3. برای گیرنده قدیمی، فرمول‌های جدید را با VLOOKUP و SUMIFS جایگزین کنین.
  4. یک نسخه از نتیجه را با Paste Special و گزینه Values بسازین.
  5. فایل را روی یک سیستم قدیمی تست کنین.

نسخه پشتیبان فراموش نشه

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

اگه ارسال فرمول ضروری نیست، فایل را به‌صورت PDF یا مقدار ثابت بدین. این‌طوری هیچ مشکل نسخه‌ای پیش نمی‌آد.

تمرین پایانی با جدول فروش و بررسی نتیجه فرمول‌ها

تمرین پایانی با جدول فروش و بررسی نتیجه فرمول‌ها

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

داده‌های نمونه

ستون‌ها: A محصول، B شهر، C تعداد، D قیمت واحد (تومان) و E مبلغ. ردیف‌ها از ۲ تا ۷ به این صورت‌اند:

  • لپ‌تاپ، تهران، ۲ عدد، ۳۰ میلیون
  • موبایل، تهران، ۵ عدد، ۲۰ میلیون
  • موبایل، مشهد، ۳ عدد، ۲۰ میلیون
  • هدفون، اصفهان، ۱۰ عدد، ۲ میلیون
  • لپ‌تاپ، مشهد، ۱ عدد، ۳۰ میلیون
  • هدفون، تهران، ۴ عدد، ۲ میلیون

مراحل تمرین

  1. در E2 بنویسین =C2*D2 و تا E7 بکشین.
  2. جمع کل با =SUM(E2:E7) باید ۲۷۸ میلیون تومان بشه.
  3. فروش تهران با SUMIF باید ۱۶۸ میلیون تومان بشه.
  4. موبایل‌های تهران با SUMIFS باید ۱۰۰ میلیون تومان بشه.
  5. تعداد فروش‌های تهران با COUNTIF باید ۳ بشه.
  6. با FILTER فقط ردیف‌های تهران را استخراج کنین.

بررسی نهایی

اگه عددی فرق داشت، در تب Formulas گزینه Show Formulas را بزنین. بعد با Evaluate Formula قدم‌به‌قدم ببینین کجا خراب شده. معمولاً مشکل از محدوده یا جداکننده است.

در آخر، از ستون شهر و مبلغ نمودار اکسل بسازین. اگه جهت نمودار یا صفحه چپ‌چین بود، راست‌چین کردن شیت و نمودار مشکل را حل می‌کنه.

جدول فروش نمونه با فرمول‌های جمع و جمع شرطی در اکسل
جدول فروش نمونه و نتیجه فرمول‌های تمرین

نام‌گذاری محدوده‌ها و جدول‌های اکسل برای فرمول‌های خوانا

فرمولی مثل =SUM(D2:D500) تا وقتی خودتان آن را نوشته‌اید قابل‌فهم است. چند ماه بعد یا برای همکارتان معلوم نیست ستون D چه چیزی را نگه می‌دارد. نام‌گذاری راه ساده‌ای برای حل این مشکل است.

محدوده را انتخاب کنید، نامی مثل فروش_ماهانه را در کادر Name Box بنویسید و Enter بزنید. از آن پس می‌توانید به‌جای آدرس سلول‌ها بنویسید =SUM(فروش_ماهانه) و فرمول خودش توضیح می‌دهد چه می‌کند.

نام‌ها قواعد ساده‌ای دارند. نباید فاصله داشته باشند، نباید شبیه آدرس سلول مثل A1 باشند و بهتر است با حرف شروع شوند. برای ویرایش یا حذف نام‌ها از Name Manager در تب Formulas یا میانبر Ctrl+F3 استفاده کنید.

اگر نامی را اشتباه تایپ کنید یا حذف شده باشد، اکسل خطای #NAME? برمی‌گرداند. پس بعد از هر تغییر در نام‌ها، فرمول‌های وابسته را یک بار بررسی کنید.

قدم بعدی تبدیل داده به Table است. بازه داده را انتخاب کنید و Ctrl+T بزنید. جدول با اضافه‌شدن هر ردیف جدید خودکار بزرگ می‌شود. فرمول‌هایی که به آن ارجاع دارند، ردیف تازه را هم می‌بینند و لازم نیست محدوده را دستی اصلاح کنید.

فرمول یک ستون محاسبه‌شده هم به‌طور خودکار تا آخر ستون پر می‌شود. همین ویژگی از خطای رایج جاافتادن چند ردیف آخر جلوگیری می‌کند.

در جدول به‌جای آدرس سلول از ارجاع ساختاریافته استفاده می‌شود. اگر جدول را فروش نامیده باشید، =SUM(فروش[مبلغ]) مجموع ستون مبلغ را حساب می‌کند. داخل خود جدول هم می‌توانید بنویسید =[@تعداد]*[@قیمت] تا هر ردیف با داده همان ردیف محاسبه شود.

نام جدول را از تب Table Design عوض کنید تا بامعنی باشد، چون نام پیش‌فرض مثل Table1 در فرمول‌ها چیزی را روشن نمی‌کند.

در عمل، برای داده‌هایی که مرتب اضافه می‌شوند جدول بسازید. برای مقدارهای ثابت مثل نرخ مالیات هم یک نام تکی مثل نرخ_مالیات تعریف کنید. به این ترتیب تغییر نرخ فقط در یک سلول انجام می‌شود و همه فرمول‌ها به‌روز می‌شوند.

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

پاک‌سازی و ترکیب متن با TRIM، SUBSTITUTE و TEXTJOIN

داده‌هایی که از فرم اینترنتی، نرم‌افزار حسابداری یا فایل CSV وارد اکسل می‌شوند معمولاً تمیز نیستند. فاصله‌های اضافه، حروف عربی به‌جای فارسی و عددهای ذخیره‌شده به‌شکل متن باعث می‌شوند جست‌وجو و جمع‌زدن خراب شود. توابع متنی این مشکل‌ها را بدون ویرایش دستی حل می‌کنند.

اصل کار این است که فرمول را در یک ستون کمکی بنویسید و بعد از اطمینان از نتیجه، آن را به مقدار تبدیل کنید.

تابع TRIM فاصله‌های ابتدا و انتهای متن را حذف می‌کند و چند فاصله پشت‌سرهم را به یکی تبدیل می‌کند. تابع LEN طول متن را برمی‌گرداند و برای پیدا کردن نویسه‌های نامرئی مفید است.

اگر LEN برای یک کد ملی ده‌رقمی عدد یازده نشان دهد، احتمالاً یک نویسه پنهان در آن هست. توابع LEFT، RIGHT و MID هم بخشی از متن را از ابتدا، انتها یا وسط جدا می‌کنند.

تابع SUBSTITUTE جایگزینی دقیق انجام می‌دهد و سه ورودی می‌گیرد: متن اصلی، متنی که باید پیدا شود و متنی که جایگزین آن می‌شود. در داده‌های فارسی پرکاربردترین استفاده‌اش تبدیل «ي» عربی به «ی» و «ك» عربی به «ک» است.

اگر یک نام با این دو نویسه متفاوت نوشته شده باشد، COUNTIF و XLOOKUP آن‌ها را یکی نمی‌دانند و نتیجه ناقص می‌شود. با تو در تو نوشتن چند SUBSTITUTE می‌توانید چند اصلاح را یک‌جا انجام دهید.

برای چسباندن متن‌ها، تابع قدیمی CONCATENATE جای خود را به CONCAT و TEXTJOIN داده است. TEXTJOIN سه چیز می‌گیرد: جداکننده، گزینه TRUE یا FALSE برای نادیده‌گرفتن سلول‌های خالی، و محدوده‌ای که باید به هم بچسبد.

با آن می‌توانید نام، نام خانوادگی و شهر را در یک سلول بنویسید و ویرگول اضافه هم نداشته باشید. این تابع در نسخه‌های قدیمی‌تر از اکسل ۲۰۱۹ ممکن است موجود نباشد.

در Microsoft 365 تابع TEXTSPLIT هم هست. این تابع متن را بر اساس یک جداکننده به چند ستون یا ردیف می‌شکند و خروجی‌اش مثل آرایه‌های پویا در سلول‌های کناری سرریز می‌شود. اگر عددی به‌شکل متن ذخیره شده باشد، تابع VALUE آن را به عدد واقعی تبدیل می‌کند.

پیش از ارسال فایل برای دیگران، ستون‌های کمکی را با Paste Special و گزینه Values به مقدار ثابت تبدیل کنید.

محاسبه سررسید، سن و روزهای کاری با توابع تاریخ

اکسل هر تاریخ را به‌صورت یک عدد سریال ذخیره می‌کند. عدد ۱ معادل اول ژانویه ۱۹۰۰ است و با هر روز یک واحد اضافه می‌شود. به همین دلیل می‌توانید دو تاریخ را از هم کم کنید و تعداد روز بین آن‌ها را بگیرید.

اگر نتیجه این تفریق به‌شکل یک تاریخ عجیب نمایش داده شد، قالب سلول را به General یا Number تغییر دهید تا عدد واقعی دیده شود.

تابع TODAY تاریخ امروز و NOW تاریخ و ساعت فعلی را برمی‌گرداند و با هر محاسبه دوباره به‌روز می‌شود. تابع DATE سه ورودی سال، ماه و روز می‌گیرد و یک تاریخ معتبر می‌سازد.

توابع YEAR، MONTH و DAY کار برعکس را می‌کنند و هر بخش را از تاریخ جدا می‌کنند. با ترکیب این‌ها می‌توانید سررسید یا اول سال مالی را بدون تایپ دستی بسازید.

تابع EDATE تاریخ را به تعداد مشخصی ماه جلو یا عقب می‌برد و EOMONTH آخرین روز ماه را می‌دهد. برای جدول اقساط، EDATE با تاریخ شروع و عدد ۱ سررسید ماه بعد را حساب می‌کند. تابع DATEDIF هم اختلاف دو تاریخ را به سال، ماه یا روز می‌دهد.

این تابع در فهرست پیشنهادی اکسل نمایش داده نمی‌شود، اما کار می‌کند و برای محاسبه سن یا سابقه کار مفید است.

برای شمارش روزهای کاری، NETWORKDAYS تعداد روزهای بین دو تاریخ را بدون شنبه و یکشنبه می‌شمارد. چون آخر هفته در ایران متفاوت است، از NETWORKDAYS.INTL استفاده کنید. در آن کد ۷ یعنی فقط جمعه تعطیل است.

آخرین ورودی تابع فهرست تعطیلات رسمی را می‌گیرد تا آن روزها هم از شمارش کنار بروند.

نکته مهم درباره تقویم است. اکسل به‌طور پیش‌فرض تقویم شمسی ندارد و توابع بالا روی تاریخ میلادی کار می‌کنند. اگر تاریخ شمسی را به‌شکل متن مثل ۱۴۰۵/۰۵/۱۲ وارد کنید، اکسل آن را تاریخ نمی‌شناسد و محاسبه خطا می‌دهد.

راه معمول، ستون کمکی با تاریخ میلادی یا استفاده از یک افزونه تبدیل تاریخ است. پیش از اعتماد به نتیجه، چند نمونه را دستی بررسی کنید.

برجسته‌سازی ردیف‌ها و محدودکردن ورودی با فرمول

فرمول فقط برای محاسبه نیست و می‌تواند ظاهر جدول و ورودی‌های مجاز را هم کنترل کند. در قالب‌بندی شرطی و اعتبارسنجی داده، یک فرمول منطقی می‌نویسید که نتیجه‌اش درست یا نادرست است. اگر درست باشد، رنگ اعمال می‌شود یا ورودی پذیرفته می‌شود.

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

برای رنگی‌کردن کل ردیف، محدوده جدول را انتخاب کنید. در تب Home گزینه Conditional Formatting و بعد New Rule و Use a formula را بزنید. فرمول را برای اولین سلول محدوده انتخابی بنویسید. مثلاً =$D2<TODAY() ردیف‌هایی را که تاریخ سررسیدشان گذشته نشان می‌دهد.

علامت دلار جلوی ستون باعث می‌شود قاعده برای همه ستون‌های همان ردیف یکسان بماند.

ترکیب چند شرط هم ممکن است. اگر ستون E مبلغ پرداخت‌شده باشد، فرمول =AND($D2<TODAY(),$E2=0) فقط سررسیدهای گذشته و پرداخت‌نشده را رنگی می‌کند. برای پیدا کردن داده تکراری می‌توانید از =COUNTIF($A$2:$A$500,$A2)>1 استفاده کنید. این روش برای یافتن فاکتور یا کد ملی تکراری در فهرست‌های بلند خیلی سریع‌تر از مرور چشمی است.

اعتبارسنجی از تب Data و گزینه Data Validation در دسترس است. در حالت Custom می‌توانید فرمول بنویسید. مثلاً =COUNTIF($A$2:$A$500,A2)=1 جلوی ورود شماره فاکتور تکراری را می‌گیرد. برای فهرست کشویی، منبع را نام یک محدوده بگذارید تا با تغییر فهرست، منو هم به‌روز شود.

در Microsoft 365 می‌توان به خروجی UNIQUE با ارجاع سرریز مثل F2# هم اشاره کرد.

یک محدودیت را فراموش نکنید: اعتبارسنجی جلوی چسباندن داده را نمی‌گیرد و داده چسبانده‌شده می‌تواند قاعده را دور بزند. برای پیدا کردن این موارد از گزینه Circle Invalid Data در همان منو استفاده کنید. قاعده‌های قالب‌بندی شرطی زیاد هم فایل را کند می‌کنند.

از Manage Rules قاعده‌های تکراری را پاک کنید و پیام خطای اعتبارسنجی را به فارسی و روشن بنویسید.

ردیابی و ارزیابی گام‌به‌گام فرمول‌های پیچیده

وقتی فرمول جواب غیرمنتظره می‌دهد، حدس‌زدن بدترین راه است. اکسل ابزارهایی دارد که نشان می‌دهند فرمول از کدام سلول‌ها داده می‌گیرد و هر بخش آن چه مقداری دارد. این ابزارها در تب Formulas و بخش Formula Auditing هستند.

برای فایل‌هایی که دیگران نوشته‌اند و توضیحی ندارند، همین ابزارها سریع‌ترین راه فهمیدن منطق محاسبه‌اند.

گزینه Trace Precedents با فلش نشان می‌دهد فرمول انتخاب‌شده به کدام سلول‌ها وابسته است. Trace Dependents برعکس، سلول‌هایی را نشان می‌دهد که از این سلول استفاده می‌کنند. پیش از حذف یا جابه‌جایی یک ستون، وابسته‌ها را ببینید تا خطای #REF! ایجاد نشود.

با Remove Arrows می‌توانید فلش‌ها را پاک کنید. این روش در فایل‌های چندبرگی هم کار می‌کند.

ابزار Evaluate Formula فرمول را قدم‌به‌قدم اجرا می‌کند و با هر کلیک یک بخش را با مقدارش جایگزین می‌کند. روش سریع‌تر این است که داخل نوار فرمول بخشی از آن را انتخاب کنید و F9 بزنید تا مقدار همان بخش دیده شود. بعد حتماً Esc بزنید.

اگر Enter بزنید، آن بخش برای همیشه به عدد ثابت تبدیل می‌شود و فرمول اصلی از بین می‌رود.

میانبر Ctrl+` که معمولاً کنار عدد ۱ قرار دارد، به‌جای نتیجه، خود فرمول‌ها را در سلول‌ها نشان می‌دهد. این حالت برای پیدا کردن عدد دستی لابه‌لای فرمول‌ها مفید است. Watch Window مقدار چند سلول کلیدی را در یک پنجره شناور نگه می‌دارد، حتی اگر در برگه دیگری باشند.

Error Checking هم فرمول‌های ناسازگار با ستون کناری را علامت می‌زند.

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

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

کاهش کندی فایل‌های سنگین با انتخاب درست فرمول

فایل‌های اکسل با ده‌ها هزار ردیف فرمول گاهی با هر تغییر کوچک چند ثانیه قفل می‌شوند. دلیل معمولاً حجم محاسبه‌های غیرضروری است، نه ضعف سیستم. اکسل فقط سلول‌هایی را دوباره حساب می‌کند که به سلول تغییرکرده وابسته باشند.

این قاعده درباره فرمول‌های ناپایدار صدق نمی‌کند و همین‌ها اغلب منبع اصلی کندی هستند.

توابع ناپایدار (Volatile) مثل TODAY، NOW، RAND، OFFSET و INDIRECT در هر محاسبه دوباره اجرا می‌شوند و سلول‌های وابسته به خودشان را هم به محاسبه وادار می‌کنند. چند عدد از آن‌ها مشکلی ایجاد نمی‌کند، اما هزاران OFFSET در یک گزارش بزرگ فایل را کند می‌کند.

به‌جای OFFSET از INDEX یا ارجاع جدول استفاده کنید. تاریخ امروز را هم فقط در یک سلول بنویسید و بقیه فرمول‌ها به آن ارجاع دهند.

ارجاع به کل ستون مثل A:A در SUMIFS و COUNTIFS معمولاً مشکلی ایجاد نمی‌کند. در SUMPRODUCT و فرمول‌های آرایه‌ای اما ممکن است میلیون‌ها سلول خالی هم خوانده شوند. محدوده را به اندازه داده نگه دارید یا از جدول استفاده کنید تا خودش با داده بزرگ شود.

اگر یک زیرمحاسبه چند بار در فرمول تکرار می‌شود، با LET آن را یک بار حساب کنید.

در مسیر Formulas و Calculation Options گزینه Manual محاسبه خودکار را خاموش می‌کند و با F9 محاسبه انجام می‌شود. Shift+F9 فقط برگه فعال را محاسبه می‌کند. این حالت هنگام تغییر دسته‌ای داده‌ها مفید است، اما فراموش‌کردنش خطرناک است، چون اعداد کهنه را نشان می‌دهد.

بعد از پایان کار، حالت را به Automatic برگردانید و یک بار فایل را کامل محاسبه کنید.

داده‌های نهایی را که دیگر تغییر نمی‌کنند با Paste Special و گزینه Values به عدد ثابت تبدیل کنید. ذخیره در قالب xlsb معمولاً حجم و زمان بازشدن فایل را کم می‌کند، ولی سازگاری آن را با ابزارهای دیگر بررسی کنید.

حذف قالب‌بندی بی‌استفاده و ردیف‌های خالی انتهای برگه هم به سبک‌شدن فایل کمک می‌کند. بعد از هر بهینه‌سازی، زمان بازشدن را مقایسه کنید.

مینا قاسمی
مینا قاسمی

کارشناس آموزش و دانشگاه

مینا قاسمی کارشناس آموزش عالی است و حوزه مدرسه تا دانشگاه را پوشش می‌دهد. او درباره کنکور، ثبت‌نام‌های تحصیلی، بورسیه و سامانه‌های آموزشی راهنماهای دقیق و به‌روز می‌نویسد.

دانشگاه کنکور بورسیه آموزش آنلاین
مشاهده همه مقالات

دیدگاه‌ها

نظرات شما پس از بررسی منتشر خواهد شد. اطلاعات تماس محفوظ می‌ماند.

هنوز دیدگاهی ثبت نشده. اولین نفری باشید!