在Excel中具有多个条件的条件格式设置(自定义格式) [英] Conditional formatting(custom format) with multiple conditions for a number in Excel

问题描述

我想根据单元格中的值将数字动态地格式化为M(百万)，B(十亿)或K(千).我试过了(但这不起作用):

I want to format a number as M(million), B(Billion) or K(thousand) dynamically based on the value in the cell. I tried (but this does not work):

[>1000000000]0,,,"B";[>1000000]0,,"M";[>1000]0,"K";0"

如果我给出任何两个条件，则它可以工作，例如:

If I give any two conditions it works, eg:

[>1000000000]0,,,"B";[>1000000]0,,"M";0  

或

 [>1000000]0,,"M";[>1000]0,"K";0 )

引用: https://www.sumproduct.com/thought/multiple- number-formatting.html

推荐答案

本文(第三个示例)提到格式语句中仅允许两个条件:

This article (third example) mentions that only two conditions in the formatting statement are allowed:

自定义数字格式最多可以指定两个条件.这是因为自定义数字格式只允许使用四个部分，而保留两个部分.第四部分始终指定文本格式，而另一部分则需要详细说明其他所有内容"(数字方式)的格式.

Custom number formats allow up to two conditions to be specified. This is because only four sections are allowed for custom number formatting and two are reserved. The fourth section always specifies text formatting and one other section is required to detail how ‘everything else’ (numerically) will be formatted.

并且如Excel 2010帮助中所述:

And as mentioned in the Excel 2010 help:

数字格式最多可以包含四部分代码，用分号分隔.这些代码部分按此顺序定义了正数，负数，零值和文本的格式.

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

作为中间解决方案，您可以使用我编写的以下VBA函数:

As an intermediate solution you could use the following VBA function I have written:

Function FormatNumber(val As Variant) As String
If IsNumeric(val) Then
    Dim NumVal As String
    NumVal = ""

    If val > 1000000000 Then
        NumVal = Str(val / 1000000000#) & "B"
    ElseIf val > 1000000# Then
        NumVal = Str(val / 1000000#) & "M"
    ElseIf val > 1000# Then
        NumVal = Str(val / 1000#) & "K"
    Else
        NumVal = Str(val)
    End If

    FormatNumber = NumVal
Else
    FormatNumber = val
End If
End Function

这将导致:

第一列是原始编号，第二列是您建议的编号格式，第三列是VBA功能FormatNumber.请注意，FormatNumber的结果是字符串，因此您不能使用它们来计算.

First column is the original number, 2nd with the number formatting you proposed, and third with the VBA function FormatNumber. Note that the results of FormatNumber are Strings, so you cannot use them to calculate.

这篇关于在Excel中具有多个条件的条件格式设置(自定义格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！