我知道条件格式化的方法。我想反向测试。
简单来说，我想要做的就是：
if ((Text_Align(A1)='left',"L","R")
或者
if ((Background_Color(A1)="Pink", "Red dominates the background", "Just another blah background")
if ((Fontweight(A1)="Bold", "That was a strong statement", "A cell filled by a bean counter")
到目前为止，我还没有找到一个函数能够达到Text_Align的相同效果——也就是测试单元格格式的值。
这种可能性存在吗？
您可以使用CELL函数获取有关单元格格式等信息，例如：
=CELL("format", H2)
这个函数支持多种格式定义之外的其他参数，具体可参考此处。
但是，如果该函数的选项不能返回您需要的结果，则可能需要使用Bathsheba的答案中提到的VBA。
Cell 函数返回对齐方式的？ - undefined您可以使用VBA来完成此操作。以下是一些原型代码:
Public Function test(ByVal rng As Range)
Application.Volatile
Select Case rng.HorizontalAlignment
Case xlLeft
test = "L"
Case xlRight
test = "R"
Case Else
test = "?"
End Select
End Function
Application.Volatile。这将函数设置为volatile。它告诉VBA函数返回值不仅依赖于输入单元格的值：格式更改不会触发重新计算。在更改一些格式后，请使用F9重新计算工作簿。除了使用VBA宏之外，还可以使用内置的XL4宏，你可以在这里找到更多信息。其中有一个来自XL4宏的函数叫做Get.Cell函数，用于返回单元格的许多方面。
注意：你不需要下载上述文件以使其工作，只需按照下面的说明进行操作即可，但该文件包含了更多的函数和信息。此方法仍然无法在仅更改单元格格式时更新，因此与运行易失性宏相比并不更准确，尽管它省去了编写所有宏的需要。它将在每次工作表重新计算时更新，就像易失性子/宏/函数一样。
当你下载该文件时，将会对函数内部的所有内容进行更详细的解释。
但是问题是你无法从工作表中调用Get.Cell，但可以从命名区域中调用它。因此，你必须按照以下方式使用它
=GET.CELL(8,OFFSET(ACTIVE.CELL(),0,-1))现在，无论您在哪个单元格中键入=Text_Align，它都会返回该单元格的左侧一个单元格的信息。您可以修改上述公式以引用任何您需要的参考。
因此，如果我在单元格B1中键入=Text_Align，它将返回有关A1的水平对齐方式的信息。它将返回以下选项：
1 = General
2 = Left
3 = Center
4 = Right
5 = Fill
6 = Justify
7 = Center across cells
Returns information about the formatting, location, or contents of a cell. Use GET.CELL in a macro whose behavior is determined by the status of a particular cell.
Syntax
GET.CELL(type_num, reference)
Type_num is a number that specifies what type of cell information you want. The following list shows the possible values of type_num and the corresponding results.
Type_num Returns
8 Number indicating the cell's horizontal alignment:
1 = General
2 = Left
3 = Center
4 = Right
5 = Fill
6 = Justify
7 = Center across cells
13 Number from 0 to 18, indicating the pattern of the selected cell as displayed in the Patterns tab of the Format Cells dialog box, which appears when you choose the Cells command from the Format menu. If no pattern is selected, returns 0.
18 Name of font, as text.
19 Size of font, in points.
20 If all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.
21 If all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.
22 If all the characters in the cell, or only the first character, are underlined, returns TRUE; otherwise, returns FALSE.
23 If all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.
24 Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.
25 If all the characters in the cell, or only the first character, are outlined, returns TRUE; otherwise, returns FALSE. Outline font format is not supported by Microsoft Excel for Windows.
26 If all the characters in the cell, or only the first character, are shadowed, returns TRUE; otherwise, returns FALSE. Shadow font format is not supported by Microsoft Excel for Windows.
38 Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0.
39 Shade background color as a number in the range 1 to 56. If color is automatic, returns 0.
40 Style of the cell, as text.
42 The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell. May be a negative number if the window is scrolled beyond the cell.
43 The vertical distance, measured in points, from the top edge of the active window to the top edge of the cell. May be a negative number if the window is scrolled beyond the cell.
44 The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell. May be a negative number if the window is scrolled beyond the cell.
45 The vertical distance, measured in points, from the top edge of the active window to the bottom edge of the cell. May be a negative number if the window is scrolled beyond the cell.
46 If the cell contains a text note, returns TRUE; otherwise, returns FALSE.
48 If the cells contains a formula, returns TRUE; if a constant, returns FALSE.
50 Number indicating the cell's vertical alignment:
1 = Top
2 = Center
3 = Bottom
4 = Justified
51 Number indicating the cell's vertical orientation:
0 = Horizontal
1 = Vertical
2 = Upward
3 = Downward
57 Returns TRUE if all the characters in the cell, or only the first character, are formatted with a superscript font; otherwise, returns FALSE.
58 Returns the font style as text of all the characters in the cell, or only the first character as displayed in the Font tab of the Format Cells dialog box: for example, "Bold Italic".
59 Returns the number for the underline style:
1 = none
2 = single
3 = double
4 = single accounting
5 = double accounting
60 Returns TRUE if all the characters in the cell, or only the first characrter, are formatted with a subscript font; otherwise, it returns FALSE.
63 Returns the fill (background) color of the cell.
64 Returns the pattern (foreground) color of the cell.
65 Returns TRUE if the Add Indent alignment option is on (Far East versions of Microsoft Excel only); otherwise, it returns FALSE.
注意事项
Reference is a cell or a range of cells from which you want information.
If reference is a range of cells, the cell in the upper-left corner of the first range in reference is used.
If reference is omitted, the active cell is assumed.
此方法仍无法在仅更改单元格格式时更新，因此与运行易失性宏相比并不更准确，尽管它确实节省了编写所有宏的时间。它将在每次工作表重新计算时更新，就像易失性子/宏/函数一样。 我之前的评论是关于如果您打算使用选择更改事件来重新计算工作表，使用这些内置函数至少可以节省您自己编写宏的时间。 - undefined你可以这样做： =CELL("prefix",A1)
根据文档，prefix会给你返回：
"prefix" 与单元格的“标签前缀”相对应的文本值。如果单元格包含左对齐文本，则返回单引号（'），如果单元格包含右对齐文本，则返回双引号（"），如果单元格包含居中对齐文本，则返回插入符号（^），如果单元格包含填充对齐文本，则返回反斜杠（\），如果单元格包含其他内容，则返回空文本（""）。
所以你的答案是：
=IF(CELL("prefix",A1)=CHAR(34),"R",IF(CELL("prefix",A1)="'","L","?"))