مرجع الدوال
كل دالة وتجميع وحساب نافذة متاح في الأعمدة المحسوبة والمقاييس. مُولَّد من محرك الصيغ، لذا فإن التوقيع المعروض هنا هو التوقيع الذي ينفّذه المحرك فعلًا.
يُعرض 70 من أصل 70
الدوال
تُستدعى داخل تعبير.
TOTEXT(value) → textTo textConverts any value to text.TONUMBER(value) → decimalTo numberConverts a value to a number. Empty when it cannot be converted.TOINTEGER(value) → whole numberTo whole numberConverts a value to a whole number. Empty when it cannot be converted.TODATE(value) → dateTo dateConverts a value to a date. Empty when it cannot be converted.YEAR(date) → whole numberYearThe year of a date, as a number.MONTH(date) → whole numberMonthThe month of a date (1–12).DAY(date) → whole numberDayThe day of the month (1–31).DATEDIFF(unit, from, to) → whole numberDate differenceThe number of whole units between two dates.DATEADD(unit, date, amount) → same type as dateDate addShifts a date by a number of units (use a negative amount to go back).TODAY() → dateTodayToday's date.NOW() → date & timeNowThe current date and time.AND(condition…) → true/falseANDTrue when every condition is true.OR(condition…) → true/falseORTrue when at least one condition is true.NOT(condition) → true/falseNOTFlips true to false and false to true.EQUALS(a, b) → true/falseEquals (=)True when both values are the same.NOTEQUALS(a, b) → true/falseNot equal (≠)True when the values differ.GREATERTHAN(a, b) → true/falseGreater than (>)True when the first value is larger.GREATEROREQUAL(a, b) → true/falseGreater or equal (≥)True when the first value is larger or the same.LESSTHAN(a, b) → true/falseLess than (<)True when the first value is smaller.LESSOREQUAL(a, b) → true/falseLess or equal (≤)True when the first value is smaller or the same.COALESCE(value…) → same type as valueFirst non-emptyThe first value that is not empty.NULLIF(value, compareTo) → same type as valueEmpty if equalReturns empty when both values are equal, otherwise the first value.ISNULL(value) → true/falseIs emptyTrue when the value is empty.ISNOTNULL(value) → true/falseIs not emptyTrue when the value is not empty.ADD(a, b) → numberAdd (+)Adds two numbers.SUBTRACT(a, b) → numberSubtract (−)Subtracts the second number from the first.MULTIPLY(a, b) → numberMultiply (×)Multiplies two numbers.DIVIDE(a, b) → decimalDivide (÷)Divides the first number by the second. Returns empty when the divisor is 0.NEGATE(value) → same type as valueNegateFlips the sign of a number.MOD(a, b) → numberModuloThe remainder after dividing the first number by the second.ROUND(value, [decimals]) → decimalRoundRounds a number to the given number of decimal places.FLOOR(value) → whole numberRound downRounds a number down to the nearest whole number.CEILING(value) → whole numberRound upRounds a number up to the nearest whole number.ABS(value) → same type as valueAbsolute valueThe number without its sign.POWER(base, exponent) → decimalPowerRaises a number to the given exponent.SQRT(value) → decimalSquare rootThe square root of a number.CONCAT(text…) → textCombine textJoins several text values into one. Empty values are skipped.UPPER(text) → textUPPERCASEConverts text to uppercase.LOWER(text) → textlowercaseConverts text to lowercase.TRIM(text) → textTrim spacesRemoves spaces from the start and end of text.LENGTH(text) → whole numberText lengthThe number of characters in a text value.SUBSTRING(text, start, [length]) → textSubstringExtracts part of a text value, starting at a position (1 = first character).REPLACE(text, find, replaceWith) → textReplaceReplaces every occurrence of a search text with a replacement.LEFT(text, count) → textLeftThe first N characters of a text value.RIGHT(text, count) → textRightThe last N characters of a text value.CONTAINS(text, search) → true/falseContainsTrue when the text contains the search text.التجميعات
تلخّص عمودًا عبر الصفوف.
SUM(column) → same type as columnSumAdds up every value.AVG(column) → decimalAverageThe mean of the values.MIN(column) → same type as columnMinimumThe smallest value.MAX(column) → same type as columnMaximumThe largest value.COUNT(column) → whole numberCountHow many non-empty values there are.COUNT_DISTINCT(column) → whole numberCount (Distinct)How many different values there are.FIRST(column) → same type as columnFirstThe first value encountered.LAST(column) → same type as columnLastThe last value encountered.MEDIAN(column) → decimalMedianThe middle value.STDDEV(column) → decimalStandard deviationHow spread out the values are.P25(column) → decimal25th percentileA quarter of the values are below this.P75(column) → decimal75th percentileThree quarters of the values are below this.P90(column) → decimal90th percentileNine tenths of the values are below this — useful for spotting outliers.حسابات النوافذ
تُحسب على طول محور المخطط، وتحتاج إلى محور مرتب.
Running total — applied to a measure along the chart's axisRunning totalA cumulative sum along the axis (each point adds all previous points).% of visible total — applied to a measure along the chart's axis% of visible totalEach point as a share of what the chart currently shows (or of its series). Filters change the total.Difference from previous — applied to a measure along the chart's axisDifference from previousHow much each point changed from the point before it.% change from previous — applied to a measure along the chart's axis% change from previousThe percentage change from the previous point — year-over-year on a yearly axis, month-over-month on a monthly one.Moving average — applied to a measure along the chart's axisMoving averageThe average over the last N points, smoothing out short-term noise.Rank — applied to a measure along the chart's axisRankThe position of each point when sorted by value.Year to date — applied to a measure along the chart's axisYear to dateAdds up each point and everything before it in the same year, restarting each January.Quarter to date — applied to a measure along the chart's axisQuarter to dateAdds up each point and everything before it in the same quarter.Month to date — applied to a measure along the chart's axisMonth to dateAdds up each point and everything before it in the same month.Same period last year — applied to a measure along the chart's axisSame period last yearThe value from the matching period one year earlier — blank in the first year, where there is nothing to compare to.% change vs last year — applied to a measure along the chart's axis% change vs last yearHow much each point changed against the matching period one year earlier.