终极短自定义数字格式 - K、M、B、T 等、Q、D、Googol [英] ultimate short custom number formatting - K, M, B, T, etc., Q, D, Googol

查看：46
本文介绍了终极短自定义数字格式 - K、M、B、T 等、Q、D、Googol的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有没有办法在谷歌表格中自定义格式可笑的大数字(两种方式都至少达到 10^100):

千>钾百万 >米十亿乙万亿 >吨等等...负千万亿 >问十亿D

通过:

  • 内部自定义数字格式
  • 公式(数组公式 ofc)
  • 类似于

    但你总是只有 3 个可以使用的插槽，这意味着你不能将万亿作为第 4 种类型/插槽.仅供参考，第四个插槽存在，但它是为文本保留的.要了解有关 Google 表格中内部格式的更多信息，请参阅:

    • 如果您对它的工作原理感兴趣，请加分...

      让我们从虚拟数组 {{},{}} 开始.SEQUENCE(34, 1, 3, 3) 将在 1 列中从数字 3 开始给我们 34 个数字3 个数字的步骤:

      这些将在幂 ^

      上升 10 时用作指数

      所以我们的虚拟数组将是:

      接下来，我们将它作为 VLOOKUP 的第二个参数插入，在这里我们检查 A 列乘以 * 的 ABS 绝对值(将负值转换为正值)1 以防万一 A 列的值不是数字.通过 VLOOKUP 我们返回第二个 2 列，作为第四个参数，我们使用近似模式 1

      从 -999 到 999 的数字此时会故意出错，因此我们稍后可以使用 IFNA 来修复"IF(A:A=IF(,,),, TEXT(A:A, "#.0 ")) 的错误翻译为:如果范围 A:A 真的为空 ISBLANK 不输出任何内容，否则格式化具有提供模式的列 #.0 例如.如果单元格 A5 = 空，则输出将为空白单元格...如果 -999

      最后一部分:

      TEXT(A:A/10^(VLOOKUP(LEN(TEXT(INT(ABS(A:A)), 0"))-1,序列(35, 1,, 3), 1, 1)), "#.0")

      ABS(A:A) 将负数转换为正数.INT 删除十进制数字(如果有).TEXT(, "0") 将科学记数法 3E+8 转换为常规数字 300000000.LEN 计算数字.-1 修正 base10 表示法.VLOOKUP 在1列的35个数字的SEQUENCE上面构造的数字，这次从数字0开始,, 以3 个数字为步长.通过 VLOOKUP 返回第一个 1 列(例如序列)在 vlookup 的近似模式 1 中.在 ^ 上增加 10 时，将此数字作为指数插入.并将 A 列中的值除以上面构造的数字 10 的特定指数的幂 ^.最后，使用 TEXT 将其格式化为 #.0

      要将丑陋的0.0 转换成漂亮的0，我们只需使用REGEXREPLACE.并且使用 INDEX 而不是更长的 ARRAYFORMULA.

      旁注:要删除尾随空格(可以添加漂亮的对齐方式，哈哈)要么从公式中删除它们，要么在 INDEX 之后使用 TRIM.

      脚本解决方案:

      永远感谢

      旁注1:本脚本使用前无需授权
      旁注 2:单元格格式需要设置为自动或数字，否则使用强制模式

      额外:

      is there a way how to custom format ridiculously large numbers (at least up to 10^100 in both ways) in google sheets:

      thousands                > K
millions                 > M
billions                 > B
trillions                > T
etc...
negative quadrillions    > Q
decillions               > D

      either via:

      • internal custom number formatting
      • formula (array formula ofc)
      • script similar to this one just extended to cover more ground

                                                                  10000.1     10.0K
                                                                100    100.0 
                                                               1000      1.0K
                                                              10000     10.0K
                                                            -100000   -100.0K
                                                           45646454     45.6M
                                                      5654894844216      5.7T
                                                         4655454544      4.7B
                                                     46546465455511     46.5T
                                                    -46546465455511    -46.5T
4654646545551184854556546454454400000000000000000000000000010000000      4.7U
    
                                                         -1000.9999     -1.0K
                                                          -100.8989   -100.9 
                                                            -20.354    -20.4 
                                                               1.03      1.0 
                                                             22E+32      2.2D

      解决方案

      internal custom number formatting solution:

      sadly, the internal formatting in google sheets is by default able to work with only 3 types of numbers:

      • positive (1, 2, 5, 10, ...)
      • negative (-3, -9, -7, ...)
      • zero (0)

      this can be tweaked to show custom formatting like thousands K, millions M and regular small numbers:

      [>999999]0.0,,"M";[>999]0.0,"K";0

      or only thousands K, millions M, billions B

      [<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

      or only negative thousands K, negative millions M, negative billions B

      [>-999950]0.0,"K";[>-999950000]0.0,,"M";0.0,,,"B"

      or only millions M, billions B, trillions T:

      [<999950000]0.0,"M";[<999950000000]0.0,,"B";0.0,,,"T"

      or only numbers from negative million M to positive million M:

      [>=999950]0.0,,"M";[<=-999950]0.0,,"M";0.0,"K"

      but you always got only 3 slots you can use, meaning that you can't have trillions as the 4th type/slot. fyi, the 4th slot exists, but it's reserved for text. to learn more about internal formatting in google sheets see:

      formula (array formula) solution:

      the formula approach is more versatile... first, you will need to decide on the system/standard you want to use (American, European, Greek, International, Unofficial, etc...):

      after that try:

      =INDEX(REGEXREPLACE(IFNA(TEXT(A:A/10^(VLOOKUP(LEN(TEXT(INT(ABS(A:A)), "0"))-1, 
 SEQUENCE(35, 1,, 3), 1, 1)), "#.0")&VLOOKUP(ABS(A:A)*1, {{10^SEQUENCE(34, 1, 3, 3)}, 
 {"K  "; "M  "; "B  "; "T  "; "Qa "; "Qi "; "Sx "; "Sp "; "O  "; "N  "; "D  "; "Ud "; 
  "Dd "; "Td "; "Qad"; "Qid"; "Sxd"; "Spd"; "Od "; "Nd "; "V  "; "Uv "; "Dv "; "Tv "; 
  "Qav"; "Qiv"; "Sxv"; "Spv"; "Ov "; "Nv "; "Tr "; "Ut "; "Dt "; "Tt "}}, 2, 1), 
 IF(ISBLANK(A:A),, TEXT(A:A, "0.0   "))), "^0\.0   $", "0     "))

      • works with positive numbers
      • works with negative numbers
      • works with zero
      • works with decimal numbers
      • works with numeric values
      • works with plain text numbers
      • works with scientific notations
      • works with blank cells
      • works up to googol 10^104 in both ways

      extra points if you are interested in how it works...

      let's start with virtual array {{},{}}. SEQUENCE(34, 1, 3, 3) will give us 34 numbers in 1 column starting from number 3 with the step of 3 numbers:

      these will be used as exponents while rising 10 on the power ^

      so our virtual array will be:

      next, we insert it as the 2nd argument of VLOOKUP where we check ABS absolute values (converting negative values into positive) of A column multiplied by *1 just in case values of A column are not numeric. via VLOOKUP we return the second 2 column and as the 4th argument, we use approximate mode 1

      numbers from -999 to 999 will intentionally error out at this point so we could later use IFNA to "fix" our errors with IF(A:A=IF(,,),, TEXT(A:A, "#.0 ")) translated as: if range A:A is truly empty ISBLANK output nothing, else format A column with provided pattern #.0 eg. 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. INT to remove decimal numbers if any. TEXT(, "0") to convert scientific notations 3E+8 into regular numbers 300000000. LEN to count digits. -1 to correct for base10 notation. VLOOKUP above-constructed number in SEQUENCE of 35 numbers in 1 column, this time starting from number 0 ,, with the step of 3 numbers. return via VLOOKUP the first 1 column (eg. the sequence) in approximate mode 1 of vlookup. insert this number as exponent when rising the 10 on power ^. and take values in A column and divide it by the above-constructed number 10 raised on the power ^ of a specific exponent. and lastly, format it with TEXT as #.0

      to convert ugly 0.0 into beautiful 0 we just use REGEXREPLACE. and INDEX is used instead of the longer ARRAYFORMULA.

      sidenote: to remove trailing spaces (which are there to add nice alignment lol) either remove them from the formula or use TRIM right after INDEX.

      script solution:

      eternal gratitude to @TheMaster for covering this

      here is a mod of it:

      /**
 * formats various numbers according to the provided short format
 * @customfunction
 * @param {A1:C100} range a 2D array
 * @param {[X1:Y10]} database [optional] a real/virtual 2D array 
 * where the odd column holds exponent of base 10 
 * and the even column contains format suffixes
 * @param {[5]} value [optional] fix suffix to fixed length 
 * by padding spaces (only if the second parameter exists)
 */
// examples:
// =CSF(A1:A)
// =CSF(2:2; X5:Y10)
// =CSF(A1:3; G10:J30)
// =CSF(C:C; X:Y; 2)                        to use custom alignment
// =CSF(C:C; X:Y; 0)                        to remove alignment
// =INDEX(TRIM(CSF(A:A)))                   to remove alignment
// =CSF(B10:D30; {3\ "K"; 4\ "TK"})         for non-english sheets
// =CSF(E5, {2, "deci"; 3, "kilo"})         for english sheets
// =INDEX(IF(ISERR(A:A*1); A:A; CSF(A:A)))  to return non-numbers
// =INDEX(IF((ISERR(A:A*1))+(ISBLANK(A:A)), A:A, CSF(A:A*1)))  enforce mode
function CSF(
  range,
  database = [
    [3,   'K'  ], //Thousand
    [6,   'M'  ], //Million
    [9,   'B'  ], //Billion
    [12,  'T'  ], //Trillion
    [15,  'Qa' ], //Quadrillion
    [18,  'Qi' ], //Quintillion
    [21,  'Sx' ], //Sextillion
    [24,  'Sp' ], //Septillion
    [27,  'O'  ], //Octillion
    [30,  'N'  ], //Nonillion
    [33,  'D'  ], //Decillion
    [36,  'Ud' ], //Undecillion
    [39,  'Dd' ], //Duodecillion
    [42,  'Td' ], //Tredecillion
    [45,  'Qad'], //Quattuordecillion
    [48,  'Qid'], //Quindecillion
    [51,  'Sxd'], //Sexdecillion
    [54,  'Spd'], //Septendecillion
    [57,  'Od' ], //Octodecillion
    [60,  'Nd' ], //Novemdecillion
    [63,  'V'  ], //Vigintillion
    [66,  'Uv' ], //Unvigintillion
    [69,  'Dv' ], //Duovigintillion
    [72,  'Tv' ], //Trevigintillion
    [75,  'Qav'], //Quattuorvigintillion
    [78,  'Qiv'], //Quinvigintillion
    [81,  'Sxv'], //Sexvigintillion
    [84,  'Spv'], //Septenvigintillion
    [87,  'Ov' ], //Octovigintillion
    [90,  'Nv' ], //Novemvigintillion
    [93,  'Tr' ], //Trigintillion
    [96,  'Ut' ], //Untrigintillion
    [99,  'Dt' ], //Duotrigintillion
    [100, 'G'  ], //Googol
    [102, 'Tt' ], //Tretrigintillion or One Hundred Googol
  ],
  value = 3
) {
  if (
    database[database.length - 1] &&
    database[database.length - 1][0] !== 0
  ) {
    database = database.reverse();
    database.push([0, '']);
  }
  const addSuffix = num => {
    const pad3 = (str = '') => str.padEnd(value, ' ');
    const decim = 1              // round to decimal places
    const separ = 0              // separate number and suffix
    const anum = Math.abs(num);
    if (num === 0) 
     return '0' + ' ' + ' '.repeat(separ) + ' '.repeat(decim) + pad3();
    if (anum > 0 && anum < 1) 
     return String(num.toFixed(decim)) + ' '.repeat(separ) + pad3();
    for (const [exp, suffix] of database) {
      if (anum >= Math.pow(10, exp))
        return `${(num / Math.pow(10, exp)).toFixed(decim)
         }${' '.repeat(separ) + pad3(suffix)}`;
    }
  };
  return customFunctionRecurse_(
    range, CSF, addSuffix, database, value, true
  );
}
function customFunctionRecurse_(
  array, mainFunc, subFunc, ...extraArgToMainFunc
) {
  if (Array.isArray(array))
    return array.map(e => mainFunc(e, ...extraArgToMainFunc));
  else return subFunc(array);
}

      sidenote 1: this script does not need to be authorized priorly to usage
      sidenote 2: cell formatting needs to be set to Automatic or Number otherwise use enforce mode

      extra:

      这篇关于终极短自定义数字格式 - K、M、B、T 等、Q、D、Googol的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆