صفحات گسترده بودجه ابزارهای قدرتمندی برای مدیریت امور مالی شما هستند، اما با همه داده ها و محاسبات موجود، می توانند به سرعت بسیار زیاد شوند. خوشبختانه، Google Sheets طیف وسیعی از فرمولها را ارائه میدهد که میتواند فرآیند بودجهبندی شما را سادهتر کند. این فرمول ها به شما کمک می کند هزینه ها را پیگیری کنید، درآمد را مدیریت کنید و اهداف مالی خود را حفظ کنید.
لینک های سریع
- AVERAGEIF
- SUMIF
- COUNTIF
- IFS
- TEXT
- غیر مستقیم
- فیلتر
- XLOOKUP
صفحات گسترده بودجه ابزارهای قدرتمندی برای مدیریت امور مالی شما هستند، اما با همه داده ها و محاسبات موجود، می توانند به سرعت بسیار زیاد شوند. خوشبختانه، Google Sheets طیف وسیعی از فرمولها را ارائه میدهد که میتواند فرآیند بودجهبندی شما را سادهتر کند. این فرمول ها به شما کمک می کند هزینه ها را پیگیری کنید، درآمد را مدیریت کنید و اهداف مالی خود را حفظ کنید.
1 AVERAGEIF
تابع AVERAGEIF به شما کمک می کند میانگین گروهی از اعداد را در محدوده سلولی که معیارهای خاصی را دارند محاسبه کنید. سینتکس این تابع به صورت زیر است:
=AVERAGEIF(range, criterion, [average_range])
در جایی که محدوده محدوده سلول هایی است که می خواهید ارزیابی کنید، معیار شرطی است که باید رعایت شود، و [میانگین_محدوده] محدوده سلول ها تا میانگین است.
محاسبه مثال AVERAGEIF
فرض کنید تصمیم گرفتهاید یک صفحهگسترده بودجهبندی ایجاد کنید که هزینههای مختلف را همراه با تاریخهای مربوطه آنها، مانند مثال زیر، ردیابی میکند.
برای یافتن میانگین مبلغی که برای خواربار خرج میکنید، جایی که «مواد غذایی» در ستون A و مقدار آن در ستون B ذکر شده است، از فرمول استفاده کنید:
=AVERAGEIF(A:A, "Groceries", B:B)
بر اساس داده های نمونه، میانگین مبلغی که برای خواربار مصرف می شود 150 دلار است.
2 SUMIF
SUMIF به شما امکان می دهد مقادیر را در محدوده ای جمع کنید که معیارهای خاصی را برآورده می کند. سینتکس تابع SUMIF به صورت زیر است:
=SUMIF(range, criterion, [sum_range])
در جایی که محدوده، محدوده سلولهایی است که باید ارزیابی شوند، معیار شرطی است که باید برآورده شود، و [sum_range] محدوده سلولها برای جمع است.
محاسبه مثال SUMIF
اگر میخواهید هزینههای خواربار خود را جمع کنید، از فرمول استفاده کنید:
=SUMIF(A:A, "Groceries", B:B)
در این مورد، بر اساس داده های نمونه، کل مبلغ صرف شده برای خواربار 450 دلار است.
3 COUNTIF
با COUNTIF، میتوانید تعداد سلولها را در محدودهای که معیارهای خاصی را برآورده میکند، بشمارید. این امر ردیابی دفعات هزینه های خاص را آسان تر می کند.
سینتکس تابع COUNTIF به صورت زیر است:
=COUNTIF(range, criterion)
جایی که محدوده، محدوده سلول هایی است که باید شمارش شوند، و معیار، شرطی است که باید رعایت شود.
COUNTIF محاسبه مثال
برای شمارش تعداد دفعاتی که برای خرید مواد غذایی خریده اید، از فرمول استفاده کنید:
=COUNTIF(A:A, "Groceries")
بر اساس داده های نمونه، فرمول 3 را برمی گرداند، به این معنی که من سه بار خواربارفروشی خریدم. اگر می خواهید چندین معیار را وارد کنید، می توانید از تابع COUNTIFS استفاده کنید.
4 IFS
IFS یک عملکرد پیشرفته تر است که به شما امکان می دهد چندین شرایط را آزمایش کنید. برای دسته بندی هزینه ها بر اساس معیارهای مختلف مفید است.
سینتکس تابع IFS به صورت زیر است:
=IFS(condition1, value_if_true1, [condition2, value_if_true2], ...)
شرط 1 اولین شرط برای ارزیابی است و value_if_true1 نتیجه در صورتی است که آن شرط درست باشد. می توانید شرایط بیشتر و نتایج مربوطه را اضافه کنید.
محاسبه مثال IFS
اگر می خواهید هزینه های خود را بر اساس مبالغ طبقه بندی کنید، از فرمول استفاده کنید:
=IFS(B2<50, "Low", B2<100, "Medium", B2>=100, "High")
این فرمول هر هزینه را بر اساس مقدار آن به عنوان کم، متوسط یا زیاد برچسب گذاری می کند. به عنوان مثال، همانطور که در تصویر زیر نشان داده شده است، خرید خواربار فروشی 150 دلاری به عنوان بالا طبقه بندی می شود.
5 متن
تابع TEXT اعداد را به صورت متن قالب بندی می کند، که برای نمایش اعداد به روشی قابل خواندن (مانند نشان دادن واحد پول یا درصد) مفید است.
سینتکس تابع TEXT به صورت زیر است:
=TEXT(value, format_text)
جایی که مقدار، عددی است که میخواهید قالببندی کنید، و format_text قالب مورد نظر است (مانند واحد پول یا درصد).
TEXT محاسبه مثال
برای نمایش یک عدد به عنوان ارز، از فرمول استفاده کنید:
=TEXT(B2, "$#,##0.00")
از آنجایی که سلول B2 حاوی 150 است، فرمول TEXT آن را به صورت 150.00 دلار نمایش می دهد تا روشن شود که ارزش سلول نشان دهنده پول است.
اگر ترجیح می دهید از فرمول ها برای قالب بندی اعداد استفاده نکنید، روش های جایگزینی برای آن وجود دارد
اعداد اعشاری را در اکسل تنظیم کنید
.
6 غیر مستقیم
تابع INDIRECT به شما این امکان را می دهد که با تبدیل یک رشته متنی به مرجع سلولی به سلول ها به صورت پویا ارجاع دهید. این به شما امکان میدهد فرمولهای خود را بر اساس ورودیهای مختلف، مانند برگهها یا محدودههای مختلف در صفحهگسترده خود، بهروزرسانی کنید.
نحو تابع INDIRECT به صورت زیر است:
=INDIRECT(ref_text, [a1])
جایی که ref_text مرجعی است که به عنوان متن ارائه می شود، و [a1] یک آرگومان اختیاری است که مشخص می کند آیا مرجع باید از سبک A1 (TRUE) یا سبک R1C1 (FALSE) استفاده کند. به طور پیش فرض، [a1] روی سبک A1 (TRUE) تنظیم شده است.
INDIRECT مثال محاسبه
فرض کنید یک مرجع به یک سلول در قالب متن دارید (به عنوان مثال، “B2”)، و می خواهید آن را به یک مرجع سلول واقعی تبدیل کنید. برای این کار از فرمول استفاده کنید:
=INDIRECT("B2")
این فرمول مقدار سلول B2 را برمیگرداند که 100 دلار است.
اکنون، فرض کنید چندین برگه در کتاب کار بودجه خود دارید که هر کدام یک ماه متفاوت را نشان می دهند (به عنوان مثال، “ژانویه”، “فوریه”، “مارس”). به عنوان مثال، در زیر تصویری از داده های نمونه برای هزینه های ماهانه در ماه مارس نشان داده شده است:
مبلغ کل در سلول B7 نشان داده شده است و 1775 دلار است.
فرض کنید میخواهید یک برگه خلاصه بسازید که ماهها را در ستون A نمایش دهد و به صورت پویا کل هزینههای هر ماه را جمعآوری کند و آنها را در ستون B بنویسد. در زیر نمونهای از شکل ظاهری برگه آمده است.
حال، فرض کنید میخواهید به صورت پویا کل هزینه را از سلول B7 در برگه مارس برداشته و در برگه خلاصه خود نمایش دهید. در اینجا نحوه استفاده از تابع INDIRECT برای این کار آمده است:
=INDIRECT("'" & A4 & "'!B7")
در این مثال، A4 به سلولی در برگه خلاصه شما اشاره دارد که حاوی نام برگهای است که میخواهید دادههای خود را از آن بیرون بکشید (به عنوان مثال، “مارس”)، و فرمول به صورت پویا به سلول B7 از برگه مارس اشاره میکند که شامل کل است. هزینه های ماه علامت علامت (&) برای الحاق یا پیوستن رشته های متنی به یکدیگر استفاده می شود.
در این مورد، نقل قول واحد (‘) برای نام برگه ها را با فاصله، نام برگه از A4 و ‘!B7’ ترکیب می کند، جایی که علامت تعجب (!) نام برگه را از مرجع سلول جدا می کند.
اسکرین شات نشان می دهد که مجموع هزینه های ماهانه مارس با استفاده از فرمول INDIRECT به 1775 دلار رسیده است. این مربوط به کل هزینه های ماهانه در خود برگه مارس است. جالب توجه است، تغییر مقدار در A4 به “فوریه” به طور خودکار مرجع را به برگه فوریه به روز می کند.
7 فیلتر
عملکرد FILTER به شما امکان می دهد طیف وسیعی از داده ها را بر اساس شرایط خاص فیلتر کنید و جداسازی هزینه ها یا دسته بندی های خاص را برای شما آسان می کند.
نحو تابع FILTER به صورت زیر است:
=FILTER(range, condition1, [condition2], ...)
جایی که محدوده، دادهای است که میخواهید فیلتر کنید، و شرط ۱ و شرط ۲ شرایطی هستند که دادهها باید رعایت کنند.
FILTER محاسبه مثال
در اینجا نحوه استفاده از تابع FILTER برای فیلتر کردن فقط هزینه های مواد غذایی با استفاده از داده های نمونه ما آمده است:
=FILTER(A:B, A:A="Groceries")
فرمول را در یک ستون جدید وارد کنید. در مثالم، من آن را در سلول E2 در ستون E وارد کردم. فرمول فقط ردیف هایی را که ستون A حاوی “مواد غذایی” است، برمی گرداند، که به شما امکان می دهد بدون حواس پرتی روی هزینه های مواد غذایی خود تمرکز کنید.
8 XLOOKUP
XLOOKUP یک تابع همه کاره است که یک محدوده مشخص را برای یک مسابقه جستجو می کند و مقدار مربوطه را برمی گرداند. برای جستجوی هزینه ها یا دسته بندی ها عالی است.
سینتکس تابع XLOOKUP به صورت زیر است:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
در جایی که lookup_value مقداری است که باید جستجو کرد، lookup_array محدوده ای برای جستجو است، return_array محدوده ای است که نتیجه را از آن برمی گرداند. پارامترهای اختیاری به تعیین اینکه در صورت یافتن هیچ مطابقتی چه کاری باید انجام شود و نحوه رسیدگی به معیارهای تطبیق کمک می کند.
محاسبه مثال XLOOKUP
فرض کنید شما یک صفحه گسترده بودجه دارید که هزینه های مختلف را بر اساس دسته بندی در ستون A، میزان خرج شده در ستون B و نام اقلام در ستون C را دنبال می کند. اکنون، می خواهید تعیین کنید که چقدر برای یک کالای خوارباری خاص، مانند سبزیجات، هزینه کرده اید.
در اینجا تابع XLOOKUP است که می تواند در این مورد به شما کمک کند:
=XLOOKUP("Vegetables", C:C, B:B)
در این مثال، فرمول 150 دلار را برگرداند، مبلغی که به طور خاص برای سبزیجات صرف شده است
اکنون، تمام فرمولهایی که پوشش دادهایم میتوانند فرآیند بودجهبندی شما را متحول کنند و مدیریت مؤثر امور مالی شما را آسانتر کنند. امروز آنها را امتحان کنید و کشف کنید که چگونه می توانند وظایف بودجه بندی شما را ساده کنند.