صفحه‌گسترده

چیت‌شیت Excel

فرمول‌ها و قابلیت‌های ضروری Excel برای تحلیل، پاک‌سازی و ارائه داده‌های جدولی. فرمول‌ها را با ارجاع‌های روشن بسازید، خطا و مقدار خالی را آگاهانه مدیریت کنید و برای فرمول‌های چندشرطی نسخهٔ Excel مورد استفاده را در نظر بگیرید. برای فایل‌های جدید XLOOKUP و توابع آرایهٔ پویا را در صورت پشتیبانی نسخه انتخاب کنید؛ در فایل‌های قدیمی سازگاری فرمول را بررسی کنید. از این صفحه به‌عنوان مرجع سریع استفاده کنید و مثال‌ها و نکته‌ها را با نسخهٔ ابزار، نیاز و شرایط واقعی پروژهٔ خود تطبیق دهید.

NOVIN SKILLS / DIGITAL STUDIO

IF و چند شرط

IF شرط را ارزیابی می‌کند؛ برای چند شرط از AND و OR استفاده کنید.

1=IF(AND(B2>=60,C2="فعال"),"قبول","بررسی")2=IFS(B2>=90,"عالی",B2>=60,"قبول",TRUE,"مردود")

SUMIFS و COUNTIFS

محدودهٔ معیارها باید با محدودهٔ مقادیر جمع‌شونده هم‌اندازه باشد.

1=SUMIFS(D:D,A:A,"تهران",B:B,">="&DATE(2026,1,1))2=COUNTIFS(C:C,"فعال",D:D,">0")

توابع متن و تاریخ

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

1=TRIM(CLEAN(A2))2=TEXTBEFORE(A2," ")3=DATE(YEAR(B2),MONTH(B2),1)

XLOOKUP با مقدار جایگزین

XLOOKUP ستون جست‌وجو و ستون نتیجه را مستقل می‌گیرد و برای مقدار ناموجود خروجی می‌دهد.

1=XLOOKUP(E2,A:A,C:C,"یافت نشد",0)

INDEX و MATCH

ترکیب سازگار برای lookup در نسخه‌هایی که XLOOKUP ندارند.

1=INDEX(C:C,MATCH(E2,A:A,0))

FILTER و UNIQUE

نتیجهٔ آرایهٔ پویا در سلول‌های مجاور spill می‌شود؛ محل spill را خالی نگه دارید.

1=SORT(UNIQUE(FILTER(A2:A100,B2:B100="فعال","")))

ارجاع نسبی، مطلق و ترکیبی

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

1A1    relative2$A$1  absolute3$A1   fixed column4A$1   fixed row

Structured reference در Table

نام ستون و Table با اضافه‌شدن ردیف‌ها در فرمول باقی می‌ماند.

1=[@Quantity]*[@UnitPrice]2=SUM(Sales[Total])

PivotTable و به‌روزرسانی

ستون‌های منبع را به Rows، Columns، Values و Filters بکشید و پس از تغییر منبع Refresh کنید.

1Insert > PivotTable2Rows:       Category3Columns:    Month4Values:     Sum of Revenue5Filters:    Region6Refresh:    PivotTable Analyze > Refresh

برای استفاده در پروژه

نکات کلیدی

۱
جمع و میانگین: =SUM(B2:B20) و =AVERAGE(B2:B20)
۲
شرط: =IF(C2>=60,"قبول","نیازمند بررسی")
۳
جمع شرطی: =SUMIF(A:A,"تهران",B:B)؛ برای چند شرط SUMIFS را به‌کار ببرید.
۴
شمارش شرطی: =COUNTIF(A:A,"فعال") و برای چند شرط COUNTIFS
۵
جست‌وجوی مدرن: =XLOOKUP(E2,A:A,B:B,"یافت نشد")
۶
مدیریت خطا: =IFERROR(formula,"")؛ علت خطا را در تحلیل حذف نکنید.
۷
ارجاع ثابت: $A$1؛ کلید F4 نوع ارجاع سلول را تغییر می‌دهد.
۸
پاک‌سازی متن: از TRIM، CLEAN، SUBSTITUTE و TEXT استفاده کنید.
۹
داده را به Table تبدیل کنید تا فیلتر، گسترش فرمول و ارجاع ساخت‌یافته داشته باشید.
۱۰
برای خلاصه‌سازی PivotTable بسازید و پیش از اشتراک‌گذاری داده و دسترسی را بررسی کنید.
۱۱
دادهٔ خام را از گزارش جدا نگه دارید و هر ستون را به یک نوع داده و معنای مشخص اختصاص دهید.
۱۲
برای ورودی‌های محدود از Data Validation استفاده کنید و فرمول را با مقدار خالی، خطا و مرز عددی آزمایش کنید.