ساخت صورت جریان نقدی در اکسل
ساخت صورت جریان نقدی در اکسل : چرا باید جریان نقدی کسب‌وکار را کنترل کنیم؟...
آموزش حسابداری در اکسل
آموزش تابع SUMIF و SUMIFS در اکسل برای جمع‌بندی حساب‌ها
فهرست مطالب در این مقاله:
آموزش تابع SUMIF و SUMIFS در اکسل : در کارهای روزمره حسابداری، بارها لازم می‌شود مجموع فروش یک مشتری، هزینه‌های یک ماه، پرداخت‌های یک حساب یا مبالغ مربوط به یک گروه خاص را محاسبه کنیم. اگر بخواهیم هر بار اطلاعات را فیلتر کنیم و اعداد را دستی جمع بزنیم، هم زمان زیادی صرف می‌شود و هم احتمال خطا افزایش پیدا می‌کند.
توابع SUMIF و SUMIFS این کار را ساده‌تر می‌کنند. با استفاده از این دو تابع می‌توانید جمع اعداد را بر اساس یک شرط یا چند شرط به‌صورت خودکار محاسبه کنید. در این آموزش، با مثال‌های واقعی و قابل استفاده در حسابداری یاد می‌گیرید چگونه این فرمول‌ها را بنویسید، تفاوت آن‌ها چیست و چطور خطاهای رایج را برطرف کنید.
تابع SUMIF در اکسل چیست؟
تابع SUMIF برای جمع‌کردن اعدادی استفاده می‌شود که یک شرط مشخص را دارند. برای مثال، اگر فهرستی از فروش مشتریان دارید، می‌توانید فقط فروش‌های مربوط به مشتری «شرکت آریا» را جمع کنید.
ساختار تابع به شکل زیر است:
=SUMIF(range, criteria, [sum_range])معنی آرگومان‌ها:
range: محدوده‌ای که اکسل باید شرط را در آن بررسی کند.criteria: شرط موردنظر، مانند نام مشتری، نام حساب یا یک مقایسه عددی.sum_range: محدوده اعدادی که باید جمع شوند. این آرگومان اختیاری است؛ اگر حذف شود، اکسل خود محدوده اول را جمع می‌کند.
مثال اول: جمع فروش یک مشتری
فرض کنید اطلاعات فروش در اکسل به این شکل ثبت شده است:
| سلول | مشتری | مبلغ فروش |
|---|---|---|
| ردیف 2 | شرکت آریا | 5,000,000 |
| ردیف 3 | فروشگاه سپهر | 3,000,000 |
| ردیف 4 | شرکت آریا | 2,500,000 |
| ردیف 5 | فروشگاه سپهر | 1,500,000 |
| ردیف 6 | شرکت آریا | 4,000,000 |
در این مثال، نام مشتری در ستون A و مبلغ فروش در ستون B قرار دارد. برای محاسبه مجموع فروش شرکت آریا، فرمول زیر را وارد کنید:
=SUMIF(A2:A6,"شرکت آریا",B2:B6)نتیجه: ۱۱,۵۰۰,۰۰۰
اکسل نام شرکت آریا را در محدوده A2:A6 پیدا می‌کند و مبالغ متناظر در ستون B را جمع می‌زند.
نکته: در این مثال فرض شده است که نام مشتری در ستون A و مبلغ فروش در ستون B قرار دارد. اگر ساختار فایل شما متفاوت است، آدرس محدوده‌ها را متناسب با فایل خود تغییر دهید.
مثال دوم: جمع هزینه‌های بیشتر از یک مبلغ مشخص
فرض کنید مبلغ هزینه‌ها در محدوده B2:B100 ثبت شده و می‌خواهید فقط هزینه‌های بیشتر از ۱,۰۰۰,۰۰۰ را جمع کنید:
=SUMIF(B2:B100,">1000000")چون محدوده بررسی و محدوده جمع یکسان است، نیازی به آرگومان سوم نیست.
برای جمع هزینه‌های مساوی یا بیشتر از ۱,۰۰۰,۰۰۰ نیز می‌توانید بنویسید:
=SUMIF(B2:B100,">=1000000")مثال سوم: استفاده از سلول به‌عنوان شرط
بهتر است شرط را در یک سلول جداگانه قرار دهید تا بتوانید گزارش را به‌راحتی تغییر دهید.
برای مثال، نام مشتری را در سلول E2 بنویسید و از فرمول زیر استفاده کنید:
=SUMIF(A2:A100,E2,B2:B100)اکنون با تغییر نام مشتری در سلول E2، نتیجه فرمول نیز تغییر می‌کند. این روش برای ساخت گزارش‌های مالی و فرم‌های خلاصه بسیار کاربردی است.
تابع SUMIFS در اکسل چیست؟
تابع SUMIFS زمانی کاربرد دارد که بخواهید اعداد را بر اساس چند شرط به‌صورت هم‌زمان جمع کنید.
برای نمونه، ممکن است بخواهید فروش‌های مربوط به مشتری «شرکت آریا» را فقط در ماه مهر محاسبه کنید. در این حالت، هم نام مشتری و هم ماه باید با شرایط شما مطابقت داشته باشند.
ساختار تابع:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)تفاوت مهم ساختار این تابع با SUMIF این است که در SUMIFS، محدوده جمع در ابتدای فرمول قرار می‌گیرد.
sum_range: محدوده اعدادی که باید جمع شوند.criteria_range1: محدوده بررسی شرط اول.criteria1: شرط اول.criteria_range2وcriteria2: محدوده و شرط دوم؛ می‌توانید شرط‌های بیشتری نیز اضافه کنید.
مثال چهارم: جمع فروش یک مشتری در یک ماه مشخص
فرض کنید اطلاعات زیر را دارید:
| ستون A: مشتری | ستون B: ماه | ستون C: مبلغ فروش |
|---|---|---|
| شرکت آریا | مهر | 5,000,000 |
| شرکت آریا | آبان | 3,000,000 |
| فروشگاه سپهر | مهر | 2,000,000 |
| شرکت آریا | مهر | 4,000,000 |
| فروشگاه سپهر | آبان | 1,500,000 |
برای محاسبه فروش شرکت آریا در ماه مهر از این فرمول استفاده کنید:
=SUMIFS(C2:C6,A2:A6,"شرکت آریا",B2:B6,"مهر")نتیجه: ۹,۰۰۰,۰۰۰
در این فرمول، اکسل تنها ردیف‌هایی را جمع می‌کند که هم نام مشتری «شرکت آریا» و هم ماه «مهر» را داشته باشند.
مثال پنجم: جمع هزینه‌های یک واحد در یک ماه
فرض کنید:
- ستون
A: نام واحد سازمانی - ستون
B: ماه - ستون
C: مبلغ هزینه
برای جمع هزینه‌های واحد «اداری» در ماه «مهر» بنویسید:
=SUMIFS(C2:C100,A2:A100,"اداری",B2:B100,"مهر")اگر نام واحد را در سلول E2 و ماه را در سلول F2 قرار دهید، فرمول انعطاف‌پذیرتر می‌شود:
=SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2)این روش برای تهیه گزارش ماهانه هزینه‌ها، بودجه واحدها و بررسی عملکرد بخش‌های مختلف کسب‌وکار مناسب است.
مقایسه SUMIF و SUMIFS
| ویژگی | SUMIF | SUMIFS |
|---|---|---|
| تعداد شرط | یک شرط | یک یا چند شرط |
| کاربرد نمونه | جمع فروش یک مشتری | جمع فروش یک مشتری در یک ماه |
| ترتیب آرگومان‌ها | محدوده شرط، شرط، محدوده جمع | محدوده جمع، سپس محدوده‌ها و شرط‌ها |
| مناسب برای | محاسبات ساده شرطی | گزارش‌های چندشرطی |
| امکان استفاده از سلول به‌عنوان شرط | بله | بله |
اگر فقط یک شرط دارید، SUMIF معمولاً خواناتر است. اگر گزارش شما به چند شرط نیاز دارد، SUMIFS انتخاب مناسب‌تری خواهد بود. همچنین می‌توانید از SUMIFS با یک شرط نیز استفاده کنید؛ مهم این است که ترتیب آرگومان‌های آن را درست رعایت کنید.
آموزش استفاده از شرط‌های مختلف در SUMIF و SUMIFS
۱. جمع اعداد بزرگ‌تر یا کوچک‌تر از یک مقدار
برای جمع مبالغ بزرگ‌تر از ۵۰۰,۰۰۰:
=SUMIF(B2:B100,">500000",B2:B100)برای جمع مبالغ کوچک‌تر از ۵۰۰,۰۰۰:
=SUMIF(B2:B100,"<500000",B2:B100)۲. استفاده از شرط‌های متنی
برای جمع مبالغ مربوط به حساب «هزینه اجاره»:
=SUMIF(A2:A100,"هزینه اجاره",B2:B100)متن شرط باید با داده‌های موجود در فایل هماهنگ باشد. فاصله‌های اضافی یا تفاوت در شیوه نوشتن نام‌ها ممکن است باعث شود نتیجه مورد انتظار را دریافت نکنید.
۳. استفاده از علامت‌های جایگزین در متن
اگر نام مشتری با عبارت «شرکت» شروع می‌شود و می‌خواهید مبالغ همه این مشتریان را جمع کنید:
=SUMIF(A2:A100,"شرکت*",B2:B100)علامت * نشان‌دهنده هر تعداد نویسه است. علامت ? نیز برای جایگزینی یک نویسه به کار می‌رود.
۴. جمع مبالغ در بازه تاریخ
فرض کنید تاریخ تراکنش‌ها در ستون A و مبلغ آن‌ها در ستون B قرار دارد. برای جمع مبالغ از ابتدای مهر تا قبل از ابتدای آبان، از فرمول زیر استفاده کنید:
=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,9,23),A2:A100,"<"&DATE(2026,10,23))این تاریخ‌ها صرفاً نمونه‌اند و بازه دقیق مهر به سال و تقویم مورد استفاده شما بستگی دارد. در این مثال، بازه با دو شرط تعریف شده است: تاریخ مساوی یا بعد از شروع بازه و تاریخ قبل از شروع بازه بعدی.
اگر تاریخ شروع در سلول E2 و تاریخ شروع ماه بعد در سلول F2 باشد، می‌توانید فرمول را این‌طور بنویسید:
=SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2)نکته مهم: مقادیر ستون تاریخ باید تاریخ واقعی اکسل باشند، نه متن‌هایی که فقط ظاهر تاریخ دارند. در فایل‌هایی با تاریخ شمسی یا قالب‌های سفارشی، قبل از استفاده از شرط تاریخ، نحوه ذخیره‌سازی تاریخ‌ها را بررسی کنید.
نکات مهم برای استفاده حسابداری از SUMIF و SUMIFS
جمع فروش بر اساس مشتری
با ثبت نام مشتری در یک سلول و استفاده از SUMIF، می‌توانید مجموع فروش هر مشتری را محاسبه کنید. اگر اطلاعات را در جدول استاندارد اکسل (Table) نگهداری کنید، توسعه گزارش با اضافه‌شدن ردیف‌های جدید آسان‌تر می‌شود.
جمع هزینه بر اساس سرفصل حساب
اگر ستون اول نام سرفصل و ستون دوم مبلغ هزینه باشد، می‌توانید هزینه هر سرفصل را جداگانه جمع کنید. این روش برای خلاصه‌سازی هزینه‌های اداری، حمل‌ونقل، اجاره و سایر هزینه‌ها مفید است.
گزارش‌گیری بر اساس مشتری و بازه زمانی
با SUMIFS می‌توانید مجموع فروش یا دریافت‌ها را بر اساس مشتری و تاریخ بررسی کنید. برای گزارش دقیق، مطمئن شوید که تاریخ‌های شروع و پایان به‌درستی تعریف شده‌اند و ردیف‌های تکراری یا تراکنش‌های برگشتی را نیز مطابق رویه حسابداری خود مدیریت می‌کنید.
استفاده از محدوده‌های هم‌اندازه
در SUMIF و به‌ویژه SUMIFS، محدوده‌های شرط و جمع را به‌درستی انتخاب کنید. بهتر است محدوده‌ها از تعداد ردیف یکسانی برخوردار باشند تا نتیجه قابل پیش‌بینی باشد.
خطاهای رایج در SUMIF و SUMIFS و روش رفع آن‌ها
چرا نتیجه فرمول صفر می‌شود؟
دلایل احتمالی:
- شرط با متن موجود در سلول‌ها مطابقت ندارد.
- نام‌ها دارای فاصله اضافی یا تفاوت در نگارش هستند.
- مبالغ به‌جای عدد، به‌صورت متن ذخیره شده‌اند.
- محدوده شرط یا محدوده جمع اشتباه انتخاب شده است.
ابتدا چند سلول نمونه را بررسی کنید و مطمئن شوید که شرط دقیقاً با داده‌ها تطابق دارد.
چرا SUMIFS جمع اشتباه نشان می‌دهد؟
ممکن است محدوده جمع را در ابتدای فرمول قرار نداده باشید یا محدوده‌های شرط را اشتباه انتخاب کرده باشید. ساختار صحیح را دوباره بررسی کنید:
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)چرا شرط تاریخ نتیجه نمی‌دهد؟
اگر تاریخ‌ها متنی باشند، اکسل ممکن است آن‌ها را به‌عنوان تاریخ واقعی تشخیص ندهد. همچنین تاریخ‌های شمسی که به‌صورت متن وارد شده‌اند، لزوماً مانند تاریخ‌های استاندارد اکسل قابل مقایسه نیستند.
چرا با اضافه‌شدن اطلاعات جدید، جمع تغییر نمی‌کند؟
اگر محدوده فرمول را تا ردیف ۱۰۰ تعریف کرده باشید، اطلاعاتی که در ردیف‌های بعدی ثبت می‌شوند داخل محاسبه قرار نمی‌گیرند. محدوده را گسترش دهید یا داده‌ها را به Table تبدیل کنید تا نگهداری گزارش ساده‌تر شود.
آیا ترتیب شرط‌ها در SUMIFS مهم است؟
ترتیب جفت‌های محدوده و شرط باید درست باشد؛ یعنی هر محدوده باید بلافاصله با شرط مربوط به خودش همراه شود. اما در صورتی که جفت‌ها را صحیح جابه‌جا کنید، معمولاً ترتیب قرارگیری شرط‌ها نتیجه را تغییر نمی‌دهد.
پرسش‌های متداول درباره SUMIF و SUMIFS
تفاوت اصلی SUMIF و SUMIFS چیست؟
SUMIF برای جمع‌کردن اعداد بر اساس یک شرط طراحی شده است. SUMIFS امکان جمع‌کردن بر اساس چند شرط هم‌زمان را فراهم می‌کند.
آیا می‌توان در SUMIF از شرط عددی استفاده کرد؟
بله. برای نمونه، فرمول =SUMIF(B2:B100,">1000000") مجموع مقادیر بزرگ‌تر از یک میلیون را در همان محدوده محاسبه می‌کند.
آیا SUMIFS می‌تواند سه یا چهار شرط داشته باشد؟
بله. می‌توانید چند جفت محدوده و شرط اضافه کنید؛ برای مثال مشتری، ماه، نوع تراکنش و وضعیت پرداخت را هم‌زمان در نظر بگیرید.
آیا SUMIF و SUMIFS به بزرگی و کوچکی حروف انگلیسی حساس‌اند؟
خیر، این توابع در تطبیق متن به‌صورت معمول به بزرگی و کوچکی حروف انگلیسی حساس نیستند. با این حال، فاصله‌های اضافی و تفاوت واقعی متن‌ها می‌تواند روی نتیجه اثر بگذارد.
اگر بخواهم فروش یک مشتری را فقط در یک ماه محاسبه کنم، کدام تابع مناسب است؟
اگر اطلاعات مشتری و ماه در ستون‌های جداگانه ثبت شده‌اند، SUMIFS گزینه مناسبی است؛ زیرا باید هم شرط مشتری و هم شرط ماه برقرار باشند.
جمع‌بندی
توابع SUMIF و SUMIFS از ابزارهای کاربردی اکسل برای جمع‌بندی اطلاعات مالی هستند. با SUMIF می‌توانید مجموع مبالغ دارای یک شرط را به دست آورید و با SUMIFS گزارش‌های چندشرطی مانند فروش هر مشتری در یک ماه یا هزینه‌های یک واحد در یک بازه زمانی را تهیه کنید.
برای استفاده مطمئن از این توابع، محدوده‌ها را درست انتخاب کنید، نوع داده‌ها را بررسی کنید و در گزارش‌های قابل توسعه از سلول‌های شرط یا جدول‌های اکسل استفاده کنید. با همین چند اصل می‌توانید بسیاری از محاسبات تکراری حسابداری را سریع‌تر و دقیق‌تر انجام دهید.
