該
但是您總是只有 3 個可以使用的插槽，這意味著您不能將萬億作為第 4 種型別/插槽。僅供參考，第四個插槽存在，但它是為文本保留的。要了解有關 Google 表格中內部格式的更多資訊，請參閱：
extra points if you are interested in how it works...
let's start with virtual array
{{},{}}.SEQUENCE(34, 1, 3, 3)will give us34numbers in1column starting from number3with the step of3numbers:
these will be used as exponents while rising
10on the power^
so our virtual array will be:
next, we insert it as the 2nd argument of
VLOOKUPwhere we checkABSabsolute values (converting negative values into positive) of A column multiplied by*1just in case values of A column are not numeric. viaVLOOKUPwe return the second2column and as the 4th argument, we use approximate mode1
numbers from -999 to 999 will intentionally error out at this point so we could later use
IFNAto "fix" our errors withIF(A:A=IF(,,),, TEXT(A:A, "#.0 "))translated as: if range A:A is truly empty=IF(,,)output nothing, else format A column with provided pattern#.0eg. if cell A5 = empty, the output will be blank cell... if -999 < A5=50 < 999 the output will be 50.0
and the last part:
TEXT(A:A/10^(VLOOKUP(LEN(TEXT(INT(ABS(A:A)), "0"))-1, SEQUENCE(35, 1,, 3), 1, 1)), "#.0")ABS(A:A)to convert negative numbers into positive.INTto remove decimal numbers if any.TEXT(, "0")to convert scientific notations3E 8into regular numbers300000000.LENto count digits.-1to correct for base10 notation.VLOOKUPabove-constructed number inSEQUENCEof35numbers in1column, this time starting from number 0,,with the step of3numbers. return viaVLOOKUPthe first1column (eg. the sequence) in approximate mode1of vlookup. insert this number as exponent when rising the10on power^. and take values in A column and divide it by the above-constructed number10raised on the power^of a specific exponent. and lastly, format it withTEXTas#.0
將丑陋
0.0轉化為美麗，0我們只需使用REGEXREPLACE. andINDEX代替了更長的ARRAYFORMULA.旁注：要洗掉尾隨空格（可以添加漂亮的對齊方式，哈哈）要么從公式中洗掉它們，要么
TRIM在INDEX.
腳本解決方案：
希望有人也會涵蓋這個......
額外的：
- 將數字轉換為純文本字串/單詞
- 將自定義格式的數字轉換為數字/值
uj5u.com熱心網友回復：
- 對于幾乎所有實際目的，我們都可以使用Intl
compact格式來實作此功能。
/** * Utility function needed to map 2D arrays */ function customFunctionRecurse_(array, mainFunc, subFunc, extraArgToMainFunc) { if (Array.isArray(array)) return array.map((e) => mainFunc(e, extraArgToMainFunc)); else return subFunc(array); } /** * Simple custom formating function using Intl * @see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/NumberFormat/NumberFormat * @customfunction * @param {A1:D2} numArr A 2D array * @returns {String[][]}Compact Intl formatted 2D array */ function format(numArr) { const cIntl = new Intl.NumberFormat('en-GB', { notation: 'compact', compactDisplay: 'short', }); return customFunctionRecurse_(numArr, format, (num) => cIntl.format(num)); }- 但是對于極端或自定義格式，我們需要使用自定義腳本：
/** * Formats various numbers according to the provided format * @customfunction * @param {A1:D2} numArr A 2D array * @param {X1:Y2=} formatArr [optional] A format 2D real/virtual array * with base 10 power -> suffix mapping * eg: X1:3 Y1:K represents numbers > 10^3 should have a K suffix * @returns {String[][]} Formatted 2D array */ function customFormat( numArr, formatArr = [ //sample byte => kb formatting [3, 'kb'], [6, 'mb'], [9, 'gb'], [12, 'tb'], ] ) { if ( formatArr[formatArr.length - 1] && formatArr[formatArr.length - 1][0] !== 0 ) { formatArr = formatArr.reverse(); formatArr.push([0, '']); } const addSuffix = (num) => { const anum = Math.abs(num); if (num === 0) return '0.00'; if (anum > 0 && anum < 1) return String(num.toFixed(2)); for (const [exp, suffix] of formatArr) { if (anum >= Math.pow(10, exp)) return `${(num / Math.pow(10, exp)).toFixed(2)}${suffix}`; } }; return customFunctionRecurse_(numArr, customFormat, addSuffix, formatArr); }用法：
=CUSTOMFORMAT(A1:A5,{{3,"k"};{10,"G"}})告訴自定義函式
k用于 numbers>10^3和Gfor10^10插圖：
顯示代碼片段
/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ /** * Utility function needed to map 2D arrays */ function customFunctionRecurse_(array, mainFunc, subFunc, extraArgToMainFunc) { if (Array.isArray(array)) return array.map((e) => mainFunc(e, extraArgToMainFunc)); else return subFunc(array); } /** * Simple custom formating function using Intl * @see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/NumberFormat/NumberFormat * @customfunction * @param {A1:D2} A 2D array * @returns {String[][]}Compact Intl formatted 2D array */ function format(numArr) { const cIntl = new Intl.NumberFormat('en-GB', { notation: 'compact', compactDisplay: 'short', }); return customFunctionRecurse_(numArr, format, (num) => cIntl.format(num)); } /** * Formats various numbers according to the provided format * @customfunction * @param {A1:D2} A 2D array * @param {X1:Y2=} [optional] A format 2D real/virtual array * with base 10 power -> suffix mapping * eg: X1:3 Y1:K represents numbers > 10^3 should have a K suffix * @returns {String[][]} Formatted 2D array */ function customFormat( numArr, formatArr = [ //sample byte => kb formatting [3, 'kb'], [6, 'mb'], [9, 'gb'], [12, 'tb'], ] ) { //console.log({ numArr, formatArr }); if ( formatArr[formatArr.length - 1] && formatArr[formatArr.length - 1][0] !== 0 ) { formatArr = formatArr.reverse(); formatArr.push([0, '']); } const addSuffix = (num) => { const anum = Math.abs(num); if (num === 0) return '0.00'; if (anum > 0 && anum < 1) return String(num.toFixed(2)); for (const [exp, suffix] of formatArr) { if (anum >= Math.pow(10, exp)) return `${(num / Math.pow(10, exp)).toFixed(2)}${suffix}`; } }; return customFunctionRecurse_(numArr, customFormat, addSuffix, formatArr); } console.log( customFormat([ [ 0, 1000, 153, 12883255, 235688235123, 88555552233355888, -86555, 0.8523588055, Math.pow(10, 15), ], ]) );<!-- https://meta.stackoverflow.com/a/375985/ --> <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/346527.html標籤：谷歌应用程序脚本 谷歌表格 字符串格式化 数组公式 数字格式