Excel Text函数用法的18个实例，含文本日期、条件范围格式与数组{0,1}/{1,

在 Excel 中，Text函数用于把数值或日期按指定格式转换为文本。当把数值转为文本时，可保留小数或取整，加上千位分隔符、货币符号和百分号，还可把数值用科学记数法表示；当把日期时间转为文本时，可定义年月日时分秒显示几位，也可用相应的英文单词或其缩写表示。

在Text函数的格式中，可以带条件，并且既可带一个条件又可带多个条件。另外，在数值和格式参数中都可以使用数组，例如在数值使用 {0,1}、{1,-1}、{-1,1} 等，这种情况常与 Value 和 VlookUp函数组合使用查找指定值。

一、Excel Text函数的语法

1、表达式：TEXT(Value, Format_Text)

中文表达式：TEXT(数值, 格式)

2、说明：

（1）小数位和整数位的格式

A、占位符 0 与 # 的区别（一个保留 0，另一个舍弃 0）。当保留指定小数位数（如保留两位小数）时，如果格式中小数点右边为 0，例如 #.00，当数值没有两位小数时，在末尾会显示 0，如 5.8 保留两位小数变为 5.80；如果格式中小数点右边为 #，例如 #.##（或 0.##），当数值没有两位小数时，在末尾不会显示 0，如 5.8 保留两位小数变为 5.8。

B、占位符 ? 用于补空格。如果要求两个数位不同的小数的小数点对齐，可以使用 ? 补空格；例如要求 5.8 与 68.48 的小数点对齐，可以把格式定义为 0.0?。

C、小数点左边的 0 不显示格式的定义。如果要求小数点左边的 0 不显示，可以把格式定义为 #.00，例如 0.25 会变为 .25。

（2）千位分隔符格式

千位分隔符共有三种格式，第一种为 #,###，表示每三位加一个千位分隔符（逗号）；第二种为“#,”，表示省略千位分隔符后的数字；第三种为“0.0,”，表示右起第一个千位分隔符后的数字用小数表示并四舍五入。

（3）日期时间格式

A、日期中年的格式有两种，一种为 yy（仅显示年份后两位），另一种为 yyyy（显示四位年份）。日期中月格式共有五种，一种为 m（省略前导 0），另一种为 mm（显示前导 0），还有三种为用月份的英文单词或其缩写表示。日期中日格式共有四种，一种为 d（省略前导 0），另一种为 dd（显示前导 0），还有两种为用周一到周日的英文单词或其缩写表示。

B、时分秒的格式都有三种，并且格式表示方法也一样；例如：小时的格式分别为 h（省略前导 0）、[h]（返回小时数超过 24 的时间） 和 hh（显示前导 0）。

（4）货币符号格式

如果要把货币符号显示到数字前，可以在格式中添加相应的货币符号；例如：在要数字前显示元（¥），可以把格式定义为“¥#.00”；¥ 可以用快捷键 Alt + 0165（小键盘上的数字）输入，具体输入方法及其它货币符号的输入方法，请看下文的实例。

（5）百分号格式

数字如果要用百分号（%）表示，可以在格式中加百分号；例如把格式定义为 0.00% 或 0%。

（6）科学记数法格式

科学记数法的格式可以为“0.0E + 0”、“0.0E + 00”或“#.0E + 0”，E（或 e）表示以 10 为底，它右边的数值表示小数点往左移动的位数。

二、Excel Text函数的使用方法及实例

（一）用占位符 0 和 # 保留两位小数的区别实例

1、双击 B1 单元格，把公式 =TEXT(A1,"0.00") 复制到 B1，按回车，返回保留两位小数的结果 15.85；再把公式 =TEXT(A1,"#.##") 复制到 B2，按回车，同样返回 15.85；双击 A1，把 15.846 改为 15.8，单击 B1，B1 中的数值变为 15.80，B2 中的数值变为 15.8；操作过程步骤，如图1所示：

图1

2、公式说明：

A、公式 =TEXT(A1,"0.00") 中，A1 为要保留两位小数的文本，0.00 为格式，公式的意思是：把 A1 中数值保留两位小数。

B、公式 =TEXT(A1,"#.##") 的格式为 #.##，也是把 A1 中的数值保留两位小数；它与格式 0.00 的异同点为：当小数点后有两位数时，它们都保留两位；当小数点后只有一个位数时，格式 0.00 会补 0 ，而格式 #.## 会省略 0。

（二）用占位符 ? 补空格的实例

1、假如要使数字位数不同的小数 3.8 与 23.85 的小数点对齐。双击 A1 单元格，把公式 =TEXT(A1,"0.0?") 复制到 A1，按回车，返回 3.8；双击 B2，把公式 =TEXT(A2,"0.0?") 复制到 B2，按回车，返回 23.35，并且 B1 与 B2 中的数值中的小数点对齐；操作过程步骤，如图2所示：

图2

2、公式说明：

A、公式 =TEXT(A1,"0.0?") 与 =TEXT(A2,"0.0?") 中的格式同为 0.0?，格式中的半角问号（?）表示补空格，即在 A1 的 3.8 前后分别补一个空格以与 A2 的 23.85 同数位，从而实现小数点对齐。

（三）不显示小数点左边 0 的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"#.00") 复制到 B1，按回车，返回 .38；双击 A1，把 0.38 改为 2.38，B1 中的数值变为 2.38；操作过程步骤，如图3所示：

图3

2、公式说明：

从演示可知，当小数点左边的数小于 1 时，格式 #.00 返回省略小数点左边 0 的结果；当小数点左边的值大于等于 1，格式返回保留小数点左边数值的结果。

（四）小数显示为分数的实例

1、假如要把 2.5 显示为分数。双击 B1 单元格，把公式 =TEXT(A1,"# 0/0") 复制到 B1，按回车，返回 2 1/2；再次双击 B1，把格式 "# 0/0" 改为 "# ?/?"，按回车，同样返回 2 1/2；操作过程步骤，如图4所示：

图4

2、公式说明：

当把小数显示为分数时，可以用格式 "# 0/0" 或 "# ?/?"，也就是分子与分母既可以用 0 又可以用 ?。另外，如果分子或分母有多位，可以用多个 0（或 ?），例如把 2.334 显示为分数，可以用格式 "# ???/???"。

（五）数值显示为千位分隔符形式的实例

1、假如要给 2380000 加上分隔逗号。双击 B1 单元格，把公式 =TEXT(A1,"#,###") 复制到 B1，按回车，返回 2,380,000；双击 B1，把格式 "#,###" 改为 "#,"，按回车，返回 2380；双击 B1，把 "#," 改为 "#.#,"，按回车，返回 2380.，双击 B1，把 "#.#," 改为 "#.#,,"，按回车，返回 2.4；再次双击 B1，把 "#.#,," 改为 "0.0,,"，同样返回 2.4；操作过程步骤，如图5所示：

图5

2、公式说明：

A、格式 "#,###" 表示从数值的个位起每隔三位显示一个千位分隔符（逗 ,）；格式 "#," 表示省略个位到百位的数字并进行四舍五入，如 2380505 会变为 2381。

B、格式 "#.#," 表示省略个位到百位并四舍五入保留一位小数；如演示中的 2380000 变为 2380.（小数点右边为 # 会省略 0，上面已经介绍过）；格式 "#.#,," 表示省略个位到十万位的数值并四舍五入保留一位小数，如演示中的 2380000 变为 2.4；格式 "0.0,," 与 "#.#,," 相同。

（六）日期格式的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"yy-m-d") 复制到 B1，按回车，返回 19-1-25；双击 B2，把公式 =TEXT(A1,"yyyy-mm-dd") 复制到 B2，按回车，返回 2019-01-25；双击 B3，把公式 =TEXT(A1,"yyyy-mmm-ddd") 复制到 B3，按回车，返回 2019-Jan-Fri；双击 B4，把公式 =TEXT(A1,"yyyy-mmmm-dddd") 复制到 B4，按回车，返回 2019-January-Friday；操作过程步骤，如图6所示：

图6

2、公式说明：

A、格式 "yy-m-d" 中，yy 表示年显示两位，m 和 d 表示月和日都显示一位；"yyyy-mm-dd" 中，yyyy 表示年显示四位，mm 和 dd 表示月和日都显示两位，若为单个数字，则用 0 补充。

B、格式 "yyyy-mmm-ddd" 中，mmm 表示月用月份的英文单词缩写显示，ddd 表示日用周一至周日的英文单词缩写显示，如演示中返回 2019-Jan-Fri，Jan 是一月英文单词的缩写，Fri 是星期五英文单词的缩写。

C、格式 "yyyy-mmmm-dddd" 中，mmmm 表示月用月份的英文单词显示，日用周一至周日的英文单词显示，如演示中返回 2019-January-Friday，January 是一月的英文单词，Friday 是星期五英文单词。

3、如果要在日期中显示中文的年月日，公式可以这样写：=TEXT(A1,"yyyy年mm月dd日")，也就是说日期格式中的间隔符（如 -）可以自定义。

（七）时间格式的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"h:m:s") 复制到 B1，按回车，返回 15:6:9；双击 B2，把 =TEXT(A1,"hh:mm:ss") 复制到 B2，按回车，返回 15:06:09；双击 A4，输入 23:66，双击 B4，把公式 =TEXT(A4,"[h]:mm") 复制到 B4，按回车，返回 24:06；双击 B5，把公式 =TEXT(A5,"[m]:ss") 复制到 B5，按回车，返回 119:06；双击 B6，把公式 =TEXT(A6,"[s].00") 复制到 B6，按回车，返回 7146.50；操作过程步骤，如图7所示：

图7

2、公式说明：

A、格式 "h:m:s" 表示时分秒只显示一位；"hh:mm:ss" 表示时分秒都显示两位，若只有一位，则用 0 补充。

B、格式 "[h]:mm" 中的 [h] 表示以小时为单位显示时间，它能返回小时数超过 24 的时间，如演示中的 23:66（23时66分）返回 24:06（24小时06分）；"[m]:ss" 中的 [m] 表示以分钟为单位显示时间，它能返回分钟数超过 60 的时间，如演示中的 1:59:06 返回 119:06（119分钟06分）；"[s].00" 中的 [s] 表示以秒为单位显示时间，它能返回秒数超过 60 的时间，如演示中的 01:59:06.5 返回 7146.50（7146秒50毫秒）。

3、如果时间要用上午（AM）或下午（PM）表示，公式可以这样写：=TEXT(A7,"hh:mm AM/PM")。

（八）在数值前添加货币符号的实例

1、假如要给“价格”列添加元符号（¥）。双击 E2 单元格，输入公式 =TEXT(C2,"¥0.0")，按回车，返回 ¥5.0；选中 E2，把鼠标移到 E2 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则剩余价格也都加上 ¥；操作过程步骤，如图8所示：

图8

2、公式说明：

A、元符号（¥）的输入方法，按住 Alt，再按小键盘上的 0165，输完数字后放开 Alt；注意：一定要关闭中文输入法，且不是按 Shift 关闭，而是把中文输入法退出，如右键中文输入法，然后选择“退出或关闭”；否则将无法输入 ¥。

B、其它货币符号的输入快捷键为：美元 $（Shift + 4），美分 ¢（Alt + 0162），英镑 £（Alt + 0163），欧元 €（Alt + 0128）；需要按住 Alt 的，数字都要从小键盘输入，输入方法输入元符号（¥）一样。

（九）显示百分号（%）的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"0.00%") 复制到 B1，按回车，返回 36.54%；选中 B1，把鼠标移到 B1 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则剩余数值也加上 %；操作过程步骤，如图9所示：

图9

2、公式说明：

公式 =TEXT(A1,"0.00%") 中格式为 "0.00%"，意思是把数值保留两位小数且加上百分号；从演示可知，无论是小数还是整数都扩大了 100 倍并加上百分号且都保留了两位小数。

（十）用科学记数法表示的实例

1、双击 B1 单元格，把公式 =TEXT(A1,"0.0E+0") 复制到 B1，按回车，返回 5.8E+9；双击 B2，把同一公式复制到 B2，在 0.0E+0 后输入一个 0，按回车，返回 5.8E+09；双击 B3，把公式 =TEXT(A3,"0.0E+0") 复制到 B3，按回车，返回 5.7E+8；双击 B4，把 B3 中的公式 复制到 B4，再把小数点前的 0 改为 #，按回车，也返回5.7E+8；操作过程步骤，如图10所示：

图10

2、公式说明：

A、公式 =TEXT(A1,"0.0E+0") 与 =TEXT(A1,"0.0E+00") 区别在于格式中加号（+）后少一个 0 与多一个 0，其实就是定义指数不足两位时是否显示前导 0。

B、公式 =TEXT(A3,"0.0E+0") 与 =TEXT(A3,"#.0E+0") 返回一样的结果，而它们的区别为：前者小数点前用 0 表示，后者小数点前用 # 表示，说明格式中，小数前既可用 0 又可用 #。

C、从对 A1 和 A3 返回的结果可知，在用科学记数法表示时，会自动四舍五入。

三、Excel Text函数的扩展应用实例

（一）用占位符 # 和 * 把数字转为文本并取整

1、假如要把销量转文本并取整。双击 E2 单元格，把公式 =TEXT(D2,"#*,") 复制到 E2，按回车，返回 2686；用双击单元格填充柄的方法把其它数值转为文本并取整；操作过程步骤，如图11所示：

图11

2、公式 =TEXT(D2,"#*,") 说明：

格式 "#*," 中的 # 表示数字，* 表示任意多个字符，“,”是千位分隔符，"#*," 表示把所有数字转为文本且仅保留整数并进行四舍五入。

（二）格式带条件

1、假如要求把销量大于 0 的显示销量，销量等于 0 或为空的显示 0。双击 E2 单元格，把公式 =TEXT(D2,"[>"&$D$4&"]0") 复制到 E2，按回车，返回 892；用双击单元格填充柄的方法返回剩余的结果；再双击 F2，把同一公式复制到 F2，然后在格式后输入“;零”，按回，也返回 892，把鼠标移到 F2 右下角的单元格填充柄上，鼠标变为加号后，按住左键并往下拖，则返回剩余单元格的结果；操作过程步骤，如图12所示：

图12

2、公式 =TEXT(D2,"[>"&$D$4&"]0") 说明：

A、$D$4 为 0，$ 表示对列和行的绝对引用，以确保往下拖时，D4 不会变为 D5、D6 等。

B、则格式 "[>"&D4&"]0" 变为 "[>0]0"，[>0] 为条件，0 为满足条件时显示的值，且格式中的 0 为占位符而不是指 0 本身。

C、则公式变为 =TEXT(D2,"[>0]0")，意思是，如果 D2 大于 0，显示占位符 0（即 D2），否则默认显示 0（若再定义一个值，则显示该值），这一点可以从公式 =TEXT(D2,"[>"&$D$4&"]0;零") 得到印证，当公式在 F2 时， D2 大于 0，它返回 D2 中的值 892；当公式在 F4 时，D4 为 0，它返回“零”。

D、另外，格式 "[>0]!0" 在后一个 0 前加 ! 后，意思恰好与 "[>0]0" 相反，意思是，如果 D2 大于 0，不显占位符 0，而显示 0。

（三）两种正负数、0、空单元格与文本格式的比较

1、假如要求正数保留一位小数、负数显空、0 和空单元格显示 0、文本显示 0 或其本身。双击 B2 单元格，把公式 =TEXT(A2,"0.0;;0;!0") 复制到 B2，按回车，返回 2.0；选中 B2, 把鼠标移到 B2 右下角的单元格填充柄上，鼠标变粗体红色加号后，双击左键，则返回剩余单元格的结果；双击 C2，把同一公式复制到 C2，把公式中“;!0" ”删除，按回车，同样返回 2.0，也用往下拖的方法返回其它结果；操作过程步骤，如图13所示：

图13

2、公式说明：

A、格式 "0.0;;0;!0" 共定义的四种格式，第一种“0.0;”表示把正数保留一位小数；第二种“;”表示把负数显示为空文本；第三种“0;”表示把 0 和空单元格返回 0；第四种“!0”表示把文本转为 0，这一点从公式 =TEXT(A7,"0.0;;0;!0") 与 =TEXT(A7,"0.0;;0") 对 A7 的返回值可知，有“!0”的返回 0，无“!0”返回 excel。

B、另外，如果数值中没有文本，可以不用 “!0”，直接用公式 =TEXT(A7,"0.0;;0") 即可。

（四）条件范围格式

1、假如要求销量大于等于 3000 与小于 2000 的显示数值，其它的显示空文本。双击 E2 单元格，把公式 =TEXT(D2,"[>=3000]0;[<2000]0;;") 复制到 E2，按回车，返回空文本；用双击单元格填充柄的方法返回剩余数值的结果；双击 F2，把公式 =TEXT(D2,"[>=3000]高;[<2000]低;中") 复制到 F2，按回车，返回“中”，再双击 F2 的单元格填充柄返回其它结果；操作过程步骤，如图14所示：

图14

2、公式说明：

A、公式 =TEXT(D2,"[>=3000]0;[<2000]0;;") 中，格式由四部分组成，“[>=3000]0;”表示大于等于 3000 的显示数值，0 为占位符；“[<2000]0;”表示小于 2000 的显示数值；“;”表示 2000 到 3000 的显示空文本。

B、=TEXT(D2,"[>=3000]高;[<2000]低;中") 与 =TEXT(D2,"[>=3000]0;[<2000]0;;") 一样，只不过用显示文字来代替数值。

提示：如果要显示特殊符号（如：占位符 0、#、*、!、@、E、e、/）需要加半角感叹号（!），否则会返回数值或值错误 #VALUE!，演示如图15所示：

图15

（五）数值为数组 {0,1} 或 {-1,1}

（1）格式为两个单值

1、双击 F2 单元格，把公式 =TEXT({0,1},D2&";"&E2) 复制到 F2，按回车，返回 80；双击 F2，把 0 改为 1，按回车，返回 81；选中 F2，把鼠标移到 F2 右下角的单元格填充柄上，鼠标变为 + 后，按住左键并拖到 F3，F3 返回 82；双击 F2，把左边的 1 改为 5，按回车，返回 85；双击 F2，把 5 改为 1，1 改为 -1，按回车，返回 81；双击 F2，把 1 改为 -1，-1 改为 1，按回车，返回 892；再次双击 F2，把 E2 改为 B2，按回车，返回“粉红衬衫”；操作过程步骤，如图16所示：

图16

2、公式说明：

（A）=TEXT({0,1},D2&";"&E2)

A、公式中，{0,1} 为数值参数，它是一个只有两个元素的数组；D2&";"&E2 为格式参数，它由 D2、";" 和 E2 连接而成；数组 {0,1} 中的 0 是占位符而不是 0 本身。

B、公式为什么只返回 D2（即 80）而没有返回 E2？首先，在 {0,1} 中，0 与 D2 对应，1 也与 D2 对应，因为数组中的元素大于 0 时都与 D2 对应，只有元素小于 0 才与 E2 对应，因此没有返回 E2；而 0 又是占位符，因此返回 80；其次，由于要返回一个数组需把 =TEXT({0,1},D2&";"&E2) 放在引用的函数中，否则只返回第一个值，所以没有返回与 1 对应的值，具体见下文的“格式为数组”。

（B）=TEXT({1,1},D2&";"&E2) 与 =TEXT({5,1},D2&";"&E2)

A、同样数组 {1,1} 中，两个 1 都与 D2 对应，也只返回 D2，但为什么返回 81，而不是返回 80？当 D2 为数值型且 D2 个位为 0 时，{1,1}中的 1 会与 D2 相加，否则{1,1}中的 1 不会与 D2 相加，因此返回 81，这一点可以从演示中的把 F2 往下拖到 F3 得到印证，因为 D3（82）并未返回 83。

B、另外，当 D2 为文本时，若 0 在数字的右边，则 1 会替换 0，如 130 变 131；若 0 在数字的左边，1 也会替换 0，如 013 变 113；若数字左右两边都有 0，则 1 只替换右边的 0，如 0130 变 0131；演示如图17所示：

图17

C、当数组 {5,1} 中第一个元素为 5 时，D2 同样会加上 5，其它的以此类推。

（C）=TEXT({1,-1},D2&";"&E2) 与 =TEXT({-1,1},D2&";"&E2)

A、数组 {1,-1} 中，1 与 D2 对应，-1 与 E2 对应；而 {-1,1} 中，-1 也与 E2 对应，1 与 D2 对应，由于 -1 作为数组的第一个元素，因此返回 E2（即 892）。

B、提示：如果 E2 为 890，则 890 同样会加 1 而变为 891，由此可知，当 -1 为数组第一个元素时，D2 会与 E2 调换位置，并且会用 -1 的绝对值 与 E2 相加，若想进一步了解，可以看下文的“格式为数组”；如果 E2 为文本，则与 D2 为文本的情况相同。

（D）=TEXT({-1,1},D2&";"&B2)

格式中的 B2 为文本，返回值为文本（即“粉红衬衫”），说明格式中引用的单元格为文本时，Text函数返回其自身。

（2）格式为数组

1、假如要查找价格为 198 对应的销量。双击 F2 单元格，把公式 =IFERROR(VLOOKUP(198,--TEXT({0,1},D2:D8&";"&E2:E8),2),"") 复制到 F2，按 Ctrl + Shift + 回车，返回 198；双击 F3，把公式 =IFERROR(VLOOKUP(198,--TEXT({1,-1},D2:D8&";"&E2:E8),2),"") 复制到 F3，按 Ctrl + Shift + 回车，返回 781；双击 F4，把公式 =IFERROR(VLOOKUP(198,--TEXT({-1,1},D2:D8&";"&E2:E8),1),"") 复制到 F4，按 Ctrl + Shift + 回车，返回空文本；操作过程步骤，如图18所示：

图18

2、公式说明：

（A）=IFERROR(VLOOKUP(198,--TEXT({0,1},D2:D8&";"&E2:E8),2),"")

A、D2:D8 以数组形式返回 D2 至 D8 中的值，即返回 {"80";"82";"88";"90";"89";"80";"198"}; E2:E8 与 D2:D8 是一个意思，它返回{"892";"762";"850";"982";"700";"528";"780"}。

B、D2:D8&";"&E2:E8 变为 {"80";"82";"88";"90";"89";"80";"198"}&";"&{"892";"762";"850";"982";"700";"528";"780"}，公式执行时，第一次分别从两个数组中取出第一个元素并把它们连接起来，即 "80;892"；第二次分别从两个数组中取出第二个元素，同样把它们连接起来，即 "82;762"；其它的以此类推，最后返回 {"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"}。

C、则 TEXT({0,1},D2:D8&";"&E2:E8) 变为 TEXT({0,1},{"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"})：

第一次执行：从格式数组取出第一个元素 "80;892"

首先从 {0,1} 取出 0，由于{0,1}中的元素大于 0 只返回格式数组每个元素左边的元素（如只返回第一个元素 "80;892" 中 80，而不返回 892）；又因为{0,1}中第一个元素 0 为占位符，因此返回 80。

其次，从 {0,1} 取出 1，因为 1 也与 80 对应，又因为{0,1}中的元素大于 0 且格式数组个位为 0 时，返回{0,1} 中的值 + 格式数组中的值，所以返回 80 + 1，即返回 81。

第二次执行：从格式数组取出第二个元素 "82;762"

首先从 {0,1} 取出 0，同理返回 82。

其次，从 {0,1} 取出 1，又因为 82 的个位不是 0，所以不加 1，因此返回 82。

其它的以此类推，最后返回 {"80","81";"82","82";"88","88";"90","91";"89","89";"80","81";"198","198"}。

D、则 --TEXT({0,1},D2:D8&";"&E2:E8),2) 变为 --{"80","81";"82","82";"88","88";"90","91";"89","89";"80","81";"198","198"}，接着把数组中的元素由文本转为数值，-- 相当Value函数，作用是把文本转为数值，最后返回 {80,81;82,82;88,88;90,91;89,89;80,81;198,198}。

E、则公式变为 =IFERROR(VLOOKUP(198,{80,81;82,82;88,88;90,91;89,89;80,81;198,198},2),"")，进一步计算，用 VLookUp 在数组第一列（逗号 , 左边的列）查找 198，在最后一行找到，然后返回与 198 在第二列对应的值 198。

F、IfError函数的作用为：如果 VLookUp 返回正确的值，IfError 返回该值，否则 IfError 返回空。

（B）=IFERROR(VLOOKUP(198,--TEXT({1,-1},D2:D8&";"&E2:E8),2),"")

公式与上面的公式是一个意思，只是数组 {1,-1} 不同，下面只分析该数组：

A、由上面的分析可知，D2:D8&";"&E2:E8 返回 {"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"}。

B、则 TEXT({1,-1},D2:D8&";"&E2:E8) 变为 TEXT({1,-1},{"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"})

第一次执行：从格式数组中取第一元素 "80;892"

首先从{1,-1} 中取出 1，由于 1 对应 80，因此返回 80 + 1，即返回 81；其次从{1,-1} 中取出 -1，由于 -1 对应 892，因此返回 892。

第二次执行：从格式数组中取第二元素 "82;762"

首先从{1,-1} 中取出 1，由于 1 对应 82，因此返回 82；其次从{1,-1} 中取出 -1，由于 -1 对应 762，因此返回 762。

其它的以此类推，最后返回 {"81","892";"82","762";"88","851";"90","982";"89","701";"81","528";"198","781"}。

（C）=IFERROR(VLOOKUP(198,--TEXT({-1,1},D2:D8&";"&E2:E8),1),"")（格式{1,-1}与{-1,1}的区别）

由上面分析可知，TEXT({-1,1},D2:D8&";"&E2:E8) 变为
TEXT({-1,1},{"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"})。

第一次执行：从格式数组中取第一元素 "80;892"

首先从{-1,1}中取出 -1，由于 -1 对应 892，因此返回 892；其次从{-1,1}中取出 1，由于 1 对应 80，因此返回 80 + 1，即返回 81。

第二次执行：从格式数组中取第二元素 "82;762"

首先从{-1,1}中取出 -1，由于 -1 对应 762，因此返回 762；其次从{-1,1}中取出 1，由于 1 对应 82，因此返回 82。

其它的以此类推，最后返回 {"892","81";"762","82";"851","88";"982","91";"701","89";"528","81";"781","198"}。

从以上分析可知，格式{1,-1}与{-1,1}的区别为：当 -1 在右边时，格式数组每个元素的右边值返回到右边，相当于 if{0,1}；当 -1 在左边时，格式数组每个元素右边值返回到左边，相当于 if{1,0}；有关 if{1,0}，请参数《Excel VLookUp怎么用If或If{0,1}实现两个或三个条件的多条件查找》一文。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。