Excel · मराठी आवृत्ती
19.2 Function Cheat Sheet
Function name आणि syntax Englishमध्येच वापरा. खाली exampleचा अर्थ मराठीत दिला आहे. “All” म्हणजे या sourceचा Excel2016+ baseline; सर्व ऐतिहासिक Excel versions नव्हेत. Actual platform/build तपासा.
| गट | Functions | Syntax — संक्षिप्त | उपयोग / काळजी | Source version label |
|---|---|---|---|---|
| Math | SUM / AVERAGE / MIN / MAX | =SUM(range) | Mini sampleची Amount बेरीज2,870. | All |
| Math | ROUND / ROUNDUP / ROUNDDOWN | =ROUND(n, digits) | 11.875 एका decimalला11.9. | All |
| Math | SUMPRODUCT | =SUMPRODUCT(arr1, arr2) | Qty-weighted average; order-average वेगळा. | All |
| Math | SUBTOTAL / AGGREGATE | =SUBTOTAL(109, range) | Visible rowsची Sum; hidden/error options समजून घ्या. | All / 2010+ |
| Conditional | SUMIF / SUMIFS | =SUMIFS(sum, rng1, crit1, …) | Pune + Delivered sales324. | All |
| Conditional | COUNTIF / COUNTIFS | =COUNTIFS(rng1, crit1, …) | Delivered rows7; distinct count नाही. | All |
| Conditional | AVERAGEIFS | =AVERAGEIFS(avg, rng1, crit1) | Criteriaवर average; no match हाताळा. | All |
| Conditional | MINIFS / MAXIFS | =MAXIFS(max, rng, crit) | उदा.Nashikची सर्वात मोठी Amount. | 2019+ |
| Logical | IF / AND / OR / NOT | =IF(test, yes, no) | Conditionनुसार label. | All |
| Logical | IFS / SWITCH | =IFS(t1, v1, t2, v2, TRUE, v3) | क्रमाने threshold outcomes किंवा exact cases. | 2019+ |
| Logical | IFERROR / IFNA | =IFERROR(x, alt) | IFNA फक्त #N/A; blanket errors लपवू नका. | All |
| Lookup | VLOOKUP / HLOOKUP | =VLOOKUP(val, table, col, FALSE) | Store keyवर details. | All |
| Lookup | INDEX + MATCH | =INDEX(ret, MATCH(val, look, 0)) | Return column डावीकडेही असू शकतो. | All |
| Lookup | XLOOKUP / XMATCH | =XLOOKUP(val, look, ret, "Not found") | Platform lookup; exact default. | M365 / 2021+ |
| Lookup | CHOOSE / OFFSET / INDIRECT | =INDIRECT("Pune!G2") | INDIRECT/OFFSET volatile; CHOOSE तसाच volatile नाही. | All |
| Text | LEFT / RIGHT / MID / LEN | =LEFT(text, n) | BLK- prefix किंवा text length. | All |
| Text | FIND / SEARCH / SUBSTITUTE | =SUBSTITUTE(text, old, new) | Rs.सारखा substring replace. | All |
| Text | TRIM / CLEAN / PROPER / UPPER / LOWER | =PROPER(TRIM(x)) | Spaces/case clean; NBSP वेगळा हाताळा. | All |
| Text | TEXT / VALUE | =TEXT(date, "dd-mm-yyyy") | Display text आणि numeric conversion वेगळे. | All |
| Text | CONCAT / TEXTJOIN | =TEXTJOIN(", ", TRUE, range) | Area values separatorसह जोडा. | 2019+ |
| Text | TEXTBEFORE / TEXTAFTER / TEXTSPLIT | =TEXTAFTER(email, "@") | Email domain किंवा delimiter split. | M365 / 2024 |
| Date | TODAY / NOW | =TODAY() | Current date/time; recalculationवर बदलतात. | All |
| Date | DATE / YEAR / MONTH / DAY | =DATE(2026,3,14) | Date components; invalid day roll-over तपासा. | All |
| Date | EOMONTH / EDATE | =EOMONTH(date, 0) | महिन्याचा शेवट किंवा month shift. | All |
| Date | WEEKDAY / NETWORKDAYS / DATEDIF | =WEEKDAY(date, 2) | Weekday2 modeमध्ये Monday1,Saturday6; holidays/workday logic तपासा. | All |
| Dynamic array | UNIQUE / SORT / SORTBY / FILTER | =FILTER(tbl, cond, "None") | Filter/sort/unique result spill. | M365 / 2021+ |
| Dynamic array | SEQUENCE / LET | =LET(x, expr, calc) | Sequence बनवा किंवा repeated expressionला नाव. | M365 / 2021+ |
| Dynamic array | TAKE / DROP / CHOOSECOLS / VSTACK / HSTACK | =TAKE(array, 5) | Arrays stack/slice; आधी schema/order तपासा. | M365 / 2024 |
| Dynamic array | LAMBDA | =LAMBDA(x, calc)(value) | स्वतःचं function; input validation जोडा. | M365 / 2024 |
| Statistics | MEDIAN / STDEV.S / QUARTILE.INC / PERCENTILE.INC | =QUARTILE.INC(range, 1) | Distribution/outlier candidate checks. | All |
| Statistics | RANK.EQ / LARGE / SMALL | =LARGE(range, 1) | Rank/large/small values; tiesची policy ठरवा. | All |
| Pivot | GETPIVOTDATA | =GETPIVOTDATA("Sum of Amount", A3, "City", "Pune") | Pivotमधला field/item result; filters लागू. | All |
Version आणि formula checks
M365/2021+ आणि M365/2024 labelsमधला फरक लक्षात घ्या. TEXTAFTER,TAKE,VSTACK,LAMBDAसारखे newer functions प्रत्येक Excel2021मध्ये नसतात. #NAME?/_xlfn दिसल्यास availability तपासा; fallback म्हणून INDEX-MATCH, helper columns किंवा Power Query योग्य ठरू शकतात.
Syntaxमधले range/arr/crit हे placeholders आहेत. प्रत्यक्ष cell references द्या. Localeनुसार formula argument separator commaऐवजी semicolon असू शकतो. Source tableमधली simple examples full validation नाहीत.