在 Excel 中，Text函數(shù)用于把數(shù)值或日期按指定格式轉(zhuǎn)換為文本。當(dāng)把數(shù)值轉(zhuǎn)為文本時(shí)，可保留小數(shù)或取整，加上千位分隔符、貨幣符號(hào)和百分號(hào)，還可把數(shù)值用科學(xué)記數(shù)法表示；當(dāng)把日期時(shí)間轉(zhuǎn)為文本時(shí)，可定義年月日時(shí)分秒顯示幾位，也可用相應(yīng)的英文單詞或其縮寫表示。

在Text函數(shù)的格式中，可以帶條件，并且既可帶一個(gè)條件又可帶多個(gè)條件。另外，在數(shù)值和格式參數(shù)中都可以使用數(shù)組，例如在數(shù)值使用 {0,1}、{1,-1}、{-1,1} 等，這種情況常與 Value 和 VlookUp函數(shù)組合使用查找指定值。

一、Excel Text函數(shù)的語(yǔ)法

1、表達(dá)式：TEXT(Value, Format_Text)

中文表達(dá)式：TEXT(數(shù)值, 格式)

2、說(shuō)明：

（1）小數(shù)位和整數(shù)位的格式

A、占位符 0 與 # 的區(qū)別（一個(gè)保留 0，另一個(gè)舍棄 0）。當(dāng)保留指定小數(shù)位數(shù)（如保留兩位小數(shù)）時(shí)，如果格式中小數(shù)點(diǎn)右邊為 0，例如 #.00，當(dāng)數(shù)值沒(méi)有兩位小數(shù)時(shí)，在末尾會(huì)顯示 0，如 5.8 保留兩位小數(shù)變?yōu)?5.80；如果格式中小數(shù)點(diǎn)右邊為 #，例如 #.##（或 0.##），當(dāng)數(shù)值沒(méi)有兩位小數(shù)時(shí)，在末尾不會(huì)顯示 0，如 5.8 保留兩位小數(shù)變?yōu)?5.8。

B、占位符 ? 用于補(bǔ)空格。如果要求兩個(gè)數(shù)位不同的小數(shù)的小數(shù)點(diǎn)對(duì)齊，可以使用 ? 補(bǔ)空格；例如要求 5.8 與 68.48 的小數(shù)點(diǎn)對(duì)齊，可以把格式定義為 0.0?。

C、小數(shù)點(diǎn)左邊的 0 不顯示格式的定義。如果要求小數(shù)點(diǎn)左邊的 0 不顯示，可以把格式定義為 #.00，例如 0.25 會(huì)變?yōu)?.25。

（2）千位分隔符格式

千位分隔符共有三種格式，第一種為 #,###，表示每三位加一個(gè)千位分隔符（逗號(hào)）；第二種為“#,”，表示省略千位分隔符后的數(shù)字；第三種為“0.0,”，表示右起第一個(gè)千位分隔符后的數(shù)字用小數(shù)表示并四舍五入。

（3）日期時(shí)間格式

A、日期中年的格式有兩種，一種為 yy（僅顯示年份后兩位），另一種為 yyyy（顯示四位年份）。日期中月格式共有五種，一種為 m（省略前導(dǎo) 0），另一種為 mm（顯示前導(dǎo) 0），還有三種為用月份的英文單詞或其縮寫表示。日期中日格式共有四種，一種為 d（省略前導(dǎo) 0），另一種為 dd（顯示前導(dǎo) 0），還有兩種為用周一到周日的英文單詞或其縮寫表示。

B、時(shí)分秒的格式都有三種，并且格式表示方法也一樣；例如：小時(shí)的格式分別為 h（省略前導(dǎo) 0）、[h]（返回小時(shí)數(shù)超過(guò) 24 的時(shí)間） 和 hh（顯示前導(dǎo) 0）。

（4）貨幣符號(hào)格式

如果要把貨幣符號(hào)顯示到數(shù)字前，可以在格式中添加相應(yīng)的貨幣符號(hào)；例如：在要數(shù)字前顯示元（¥），可以把格式定義為“¥#.00”；¥ 可以用快捷鍵 Alt + 0165（小鍵盤上的數(shù)字）輸入，具體輸入方法及其它貨幣符號(hào)的輸入方法，請(qǐng)看下文的實(shí)例。

（5）百分號(hào)格式

數(shù)字如果要用百分號(hào)（%）表示，可以在格式中加百分號(hào)；例如把格式定義為 0.00% 或 0%。

（6）科學(xué)記數(shù)法格式

科學(xué)記數(shù)法的格式可以為“0.0E + 0”、“0.0E + 00”或“#.0E + 0”，E（或 e）表示以 10 為底，它右邊的數(shù)值表示小數(shù)點(diǎn)往左移動(dòng)的位數(shù)。

二、Excel Text函數(shù)的使用方法及實(shí)例

（一）用占位符 0 和 # 保留兩位小數(shù)的區(qū)別實(shí)例

1、雙擊 B1 單元格，把公式 =TEXT(A1,"0.00") 復(fù)制到 B1，按回車，返回保留兩位小數(shù)的結(jié)果 15.85；再把公式 =TEXT(A1,"#.##") 復(fù)制到 B2，按回車，同樣返回 15.85；雙擊 A1，把 15.846 改為 15.8，單擊 B1，B1 中的數(shù)值變?yōu)?15.80，B2 中的數(shù)值變?yōu)?15.8；操作過(guò)程步驟，如圖1所示：

圖1

2、公式說(shuō)明：

A、公式 =TEXT(A1,"0.00") 中，A1 為要保留兩位小數(shù)的文本，0.00 為格式，公式的意思是：把 A1 中數(shù)值保留兩位小數(shù)。

B、公式 =TEXT(A1,"#.##") 的格式為 #.##，也是把 A1 中的數(shù)值保留兩位小數(shù)；它與格式 0.00 的異同點(diǎn)為：當(dāng)小數(shù)點(diǎn)后有兩位數(shù)時(shí)，它們都保留兩位；當(dāng)小數(shù)點(diǎn)后只有一個(gè)位數(shù)時(shí)，格式 0.00 會(huì)補(bǔ) 0 ，而格式 #.## 會(huì)省略 0。

（二）用占位符 ? 補(bǔ)空格的實(shí)例

1、假如要使數(shù)字位數(shù)不同的小數(shù) 3.8 與 23.85 的小數(shù)點(diǎn)對(duì)齊。雙擊 A1 單元格，把公式 =TEXT(A1,"0.0?") 復(fù)制到 A1，按回車，返回 3.8；雙擊 B2，把公式 =TEXT(A2,"0.0?") 復(fù)制到 B2，按回車，返回 23.35，并且 B1 與 B2 中的數(shù)值中的小數(shù)點(diǎn)對(duì)齊；操作過(guò)程步驟，如圖2所示：

圖2

2、公式說(shuō)明：

A、公式 =TEXT(A1,"0.0?") 與 =TEXT(A2,"0.0?") 中的格式同為 0.0?，格式中的半角問(wèn)號(hào)（?）表示補(bǔ)空格，即在 A1 的 3.8 前后分別補(bǔ)一個(gè)空格以與 A2 的 23.85 同數(shù)位，從而實(shí)現(xiàn)小數(shù)點(diǎn)對(duì)齊。

（三）不顯示小數(shù)點(diǎn)左邊 0 的實(shí)例

1、雙擊 B1 單元格，把公式 =TEXT(A1,"#.00") 復(fù)制到 B1，按回車，返回 .38；雙擊 A1，把 0.38 改為 2.38，B1 中的數(shù)值變?yōu)?2.38；操作過(guò)程步驟，如圖3所示：

圖3

2、公式說(shuō)明：

從演示可知，當(dāng)小數(shù)點(diǎn)左邊的數(shù)小于 1 時(shí)，格式 #.00 返回省略小數(shù)點(diǎn)左邊 0 的結(jié)果；當(dāng)小數(shù)點(diǎn)左邊的值大于等于 1，格式返回保留小數(shù)點(diǎn)左邊數(shù)值的結(jié)果。

（四）小數(shù)顯示為分?jǐn)?shù)的實(shí)例

1、假如要把 2.5 顯示為分?jǐn)?shù)。雙擊 B1 單元格，把公式 =TEXT(A1,"# 0/0") 復(fù)制到 B1，按回車，返回 2 1/2；再次雙擊 B1，把格式 "# 0/0" 改為 "# ?/?"，按回車，同樣返回 2 1/2；操作過(guò)程步驟，如圖4所示：

圖4

2、公式說(shuō)明：

當(dāng)把小數(shù)顯示為分?jǐn)?shù)時(shí)，可以用格式 "# 0/0" 或 "# ?/?"，也就是分子與分母既可以用 0 又可以用 ?。另外，如果分子或分母有多位，可以用多個(gè) 0（或 ?），例如把 2.334 顯示為分?jǐn)?shù)，可以用格式 "# ???/???"。

（五）數(shù)值顯示為千位分隔符形式的實(shí)例

1、假如要給 2380000 加上分隔逗號(hào)。雙擊 B1 單元格，把公式 =TEXT(A1,"#,###") 復(fù)制到 B1，按回車，返回 2,380,000；雙擊 B1，把格式 "#,###" 改為 "#,"，按回車，返回 2380；雙擊 B1，把 "#," 改為 "#.#,"，按回車，返回 2380.，雙擊 B1，把 "#.#," 改為 "#.#,,"，按回車，返回 2.4；再次雙擊 B1，把 "#.#,," 改為 "0.0,,"，同樣返回 2.4；操作過(guò)程步驟，如圖5所示：

圖5

2、公式說(shuō)明：

A、格式 "#,###" 表示從數(shù)值的個(gè)位起每隔三位顯示一個(gè)千位分隔符（逗 ,）；格式 "#," 表示省略個(gè)位到百位的數(shù)字并進(jìn)行四舍五入，如 2380505 會(huì)變?yōu)?2381。

B、格式 "#.#," 表示省略個(gè)位到百位并四舍五入保留一位小數(shù)；如演示中的 2380000 變?yōu)?2380.（小數(shù)點(diǎn)右邊為 # 會(huì)省略 0，上面已經(jīng)介紹過(guò)）；格式 "#.#,," 表示省略個(gè)位到十萬(wàn)位的數(shù)值并四舍五入保留一位小數(shù)，如演示中的 2380000 變?yōu)?2.4；格式 "0.0,," 與 "#.#,," 相同。

（六）日期格式的實(shí)例

1、雙擊 B1 單元格，把公式 =TEXT(A1,"yy-m-d") 復(fù)制到 B1，按回車，返回 19-1-25；雙擊 B2，把公式 =TEXT(A1,"yyyy-mm-dd") 復(fù)制到 B2，按回車，返回 2019-01-25；雙擊 B3，把公式 =TEXT(A1,"yyyy-mmm-ddd") 復(fù)制到 B3，按回車，返回 2019-Jan-Fri；雙擊 B4，把公式 =TEXT(A1,"yyyy-mmmm-dddd") 復(fù)制到 B4，按回車，返回 2019-January-Friday；操作過(guò)程步驟，如圖6所示：

圖6

2、公式說(shuō)明：

A、格式 "yy-m-d" 中，yy 表示年顯示兩位，m 和 d 表示月和日都顯示一位；"yyyy-mm-dd" 中，yyyy 表示年顯示四位，mm 和 dd 表示月和日都顯示兩位，若為單個(gè)數(shù)字，則用 0 補(bǔ)充。

B、格式 "yyyy-mmm-ddd" 中，mmm 表示月用月份的英文單詞縮寫顯示，ddd 表示日用周一至周日的英文單詞縮寫顯示，如演示中返回 2019-Jan-Fri，Jan 是一月英文單詞的縮寫，F(xiàn)ri 是星期五英文單詞的縮寫。

C、格式 "yyyy-mmmm-dddd" 中，mmmm 表示月用月份的英文單詞顯示，日用周一至周日的英文單詞顯示，如演示中返回 2019-January-Friday，January 是一月的英文單詞，F(xiàn)riday 是星期五英文單詞。

3、如果要在日期中顯示中文的年月日，公式可以這樣寫：=TEXT(A1,"yyyy年mm月dd日")，也就是說(shuō)日期格式中的間隔符（如 -）可以自定義。

（七）時(shí)間格式的實(shí)例

1、雙擊 B1 單元格，把公式 =TEXT(A1,"h:m:s") 復(fù)制到 B1，按回車，返回 15:6:9；雙擊 B2，把 =TEXT(A1,"hh:mm:ss") 復(fù)制到 B2，按回車，返回 15:06:09；雙擊 A4，輸入 23:66，雙擊 B4，把公式 =TEXT(A4,"[h]:mm") 復(fù)制到 B4，按回車，返回 24:06；雙擊 B5，把公式 =TEXT(A5,"[m]:ss") 復(fù)制到 B5，按回車，返回 119:06；雙擊 B6，把公式 =TEXT(A6,"[s].00") 復(fù)制到 B6，按回車，返回 7146.50；操作過(guò)程步驟，如圖7所示：

圖7

2、公式說(shuō)明：

A、格式 "h:m:s" 表示時(shí)分秒只顯示一位；"hh:mm:ss" 表示時(shí)分秒都顯示兩位，若只有一位，則用 0 補(bǔ)充。

B、格式 "[h]:mm" 中的 [h] 表示以小時(shí)為單位顯示時(shí)間，它能返回小時(shí)數(shù)超過(guò) 24 的時(shí)間，如演示中的 23:66（23時(shí)66分）返回 24:06（24小時(shí)06分）；"[m]:ss" 中的 [m] 表示以分鐘為單位顯示時(shí)間，它能返回分鐘數(shù)超過(guò) 60 的時(shí)間，如演示中的 1:59:06 返回 119:06（119分鐘06分）；"[s].00" 中的 [s] 表示以秒為單位顯示時(shí)間，它能返回秒數(shù)超過(guò) 60 的時(shí)間，如演示中的 01:59:06.5 返回 7146.50（7146秒50毫秒）。

3、如果時(shí)間要用上午（AM）或下午（PM）表示，公式可以這樣寫：=TEXT(A7,"hh:mm AM/PM")。

（八）在數(shù)值前添加貨幣符號(hào)的實(shí)例

1、假如要給“價(jià)格”列添加元符號(hào)（¥）。雙擊 E2 單元格，輸入公式 =TEXT(C2,"¥0.0")，按回車，返回 ¥5.0；選中 E2，把鼠標(biāo)移到 E2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)榧犹?hào)后，雙擊左鍵，則剩余價(jià)格也都加上 ¥；操作過(guò)程步驟，如圖8所示：

圖8

2、公式說(shuō)明：

A、元符號(hào)（¥）的輸入方法，按住 Alt，再按小鍵盤上的 0165，輸完數(shù)字后放開 Alt；注意：一定要關(guān)閉中文輸入法，且不是按 Shift 關(guān)閉，而是把中文輸入法退出，如右鍵中文輸入法，然后選擇“退出或關(guān)閉”；否則將無(wú)法輸入 ¥。

B、其它貨幣符號(hào)的輸入快捷鍵為：美元 $（Shift + 4），美分 ¢（Alt + 0162），英鎊 £（Alt + 0163），歐元 €（Alt + 0128）；需要按住 Alt 的，數(shù)字都要從小鍵盤輸入，輸入方法輸入元符號(hào)（¥）一樣。

（九）顯示百分號(hào)（%）的實(shí)例

1、雙擊 B1 單元格，把公式 =TEXT(A1,"0.00%") 復(fù)制到 B1，按回車，返回 36.54%；選中 B1，把鼠標(biāo)移到 B1 右下角的單元格填充柄上，鼠標(biāo)變?yōu)榧犹?hào)后，雙擊左鍵，則剩余數(shù)值也加上 %；操作過(guò)程步驟，如圖9所示：

圖9

2、公式說(shuō)明：

公式 =TEXT(A1,"0.00%") 中格式為 "0.00%"，意思是把數(shù)值保留兩位小數(shù)且加上百分號(hào)；從演示可知，無(wú)論是小數(shù)還是整數(shù)都擴(kuò)大了 100 倍并加上百分號(hào)且都保留了兩位小數(shù)。

（十）用科學(xué)記數(shù)法表示的實(shí)例

1、雙擊 B1 單元格，把公式 =TEXT(A1,"0.0E+0") 復(fù)制到 B1，按回車，返回 5.8E+9；雙擊 B2，把同一公式復(fù)制到 B2，在 0.0E+0 后輸入一個(gè) 0，按回車，返回 5.8E+09；雙擊 B3，把公式 =TEXT(A3,"0.0E+0") 復(fù)制到 B3，按回車，返回 5.7E+8；雙擊 B4，把 B3 中的公式 復(fù)制到 B4，再把小數(shù)點(diǎn)前的 0 改為 #，按回車，也返回5.7E+8；操作過(guò)程步驟，如圖10所示：

圖10

2、公式說(shuō)明：

A、公式 =TEXT(A1,"0.0E+0") 與 =TEXT(A1,"0.0E+00") 區(qū)別在于格式中加號(hào)（+）后少一個(gè) 0 與多一個(gè) 0，其實(shí)就是定義指數(shù)不足兩位時(shí)是否顯示前導(dǎo) 0。

B、公式 =TEXT(A3,"0.0E+0") 與 =TEXT(A3,"#.0E+0") 返回一樣的結(jié)果，而它們的區(qū)別為：前者小數(shù)點(diǎn)前用 0 表示，后者小數(shù)點(diǎn)前用 # 表示，說(shuō)明格式中，小數(shù)前既可用 0 又可用 #。

C、從對(duì) A1 和 A3 返回的結(jié)果可知，在用科學(xué)記數(shù)法表示時(shí)，會(huì)自動(dòng)四舍五入。

三、Excel Text函數(shù)的擴(kuò)展應(yīng)用實(shí)例

（一）用占位符 # 和 * 把數(shù)字轉(zhuǎn)為文本并取整

1、假如要把銷量轉(zhuǎn)文本并取整。雙擊 E2 單元格，把公式 =TEXT(D2,"#*,") 復(fù)制到 E2，按回車，返回 2686；用雙擊單元格填充柄的方法把其它數(shù)值轉(zhuǎn)為文本并取整；操作過(guò)程步驟，如圖11所示：

圖11

2、公式 =TEXT(D2,"#*,") 說(shuō)明：

格式 "#*," 中的 # 表示數(shù)字，* 表示任意多個(gè)字符，“,”是千位分隔符，"#*," 表示把所有數(shù)字轉(zhuǎn)為文本且僅保留整數(shù)并進(jìn)行四舍五入。

（二）格式帶條件

1、假如要求把銷量大于 0 的顯示銷量，銷量等于 0 或?yàn)榭盏娘@示 0。雙擊 E2 單元格，把公式 =TEXT(D2,"[>"&$D$4&"]0") 復(fù)制到 E2，按回車，返回 892；用雙擊單元格填充柄的方法返回剩余的結(jié)果；再雙擊 F2，把同一公式復(fù)制到 F2，然后在格式后輸入“;零”，按回，也返回 892，把鼠標(biāo)移到 F2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)榧犹?hào)后，按住左鍵并往下拖，則返回剩余單元格的結(jié)果；操作過(guò)程步驟，如圖12所示：

圖12

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

A、$D$4 為 0，$ 表示對(duì)列和行的絕對(duì)引用，以確保往下拖時(shí)，D4 不會(huì)變?yōu)?D5、D6 等。

B、則格式 "[>"&D4&"]0" 變?yōu)?"[>0]0"，[>0] 為條件，0 為滿足條件時(shí)顯示的值，且格式中的 0 為占位符而不是指 0 本身。

C、則公式變?yōu)?=TEXT(D2,"[>0]0")，意思是，如果 D2 大于 0，顯示占位符 0（即 D2），否則默認(rèn)顯示 0（若再定義一個(gè)值，則顯示該值），這一點(diǎn)可以從公式 =TEXT(D2,"[>"&$D$4&"]0;零") 得到印證，當(dāng)公式在 F2 時(shí)， D2 大于 0，它返回 D2 中的值 892；當(dāng)公式在 F4 時(shí)，D4 為 0，它返回“零”。

D、另外，格式 "[>0]!0" 在后一個(gè) 0 前加 ! 后，意思恰好與 "[>0]0" 相反，意思是，如果 D2 大于 0，不顯占位符 0，而顯示 0。

（三）兩種正負(fù)數(shù)、0、空單元格與文本格式的比較

1、假如要求正數(shù)保留一位小數(shù)、負(fù)數(shù)顯空、0 和空單元格顯示 0、文本顯示 0 或其本身。雙擊 B2 單元格，把公式 =TEXT(A2,"0.0;;0;!0") 復(fù)制到 B2，按回車，返回 2.0；選中 B2, 把鼠標(biāo)移到 B2 右下角的單元格填充柄上，鼠標(biāo)變粗體紅色加號(hào)后，雙擊左鍵，則返回剩余單元格的結(jié)果；雙擊 C2，把同一公式復(fù)制到 C2，把公式中“;!0" ”刪除，按回車，同樣返回 2.0，也用往下拖的方法返回其它結(jié)果；操作過(guò)程步驟，如圖13所示：

圖13

2、公式說(shuō)明：

A、格式 "0.0;;0;!0" 共定義的四種格式，第一種“0.0;”表示把正數(shù)保留一位小數(shù)；第二種“;”表示把負(fù)數(shù)顯示為空文本；第三種“0;”表示把 0 和空單元格返回 0；第四種“!0”表示把文本轉(zhuǎn)為 0，這一點(diǎn)從公式 =TEXT(A7,"0.0;;0;!0") 與 =TEXT(A7,"0.0;;0") 對(duì) A7 的返回值可知，有“!0”的返回 0，無(wú)“!0”返回 excel。

B、另外，如果數(shù)值中沒(méi)有文本，可以不用 “!0”，直接用公式 =TEXT(A7,"0.0;;0") 即可。

（四）條件范圍格式

1、假如要求銷量大于等于 3000 與小于 2000 的顯示數(shù)值，其它的顯示空文本。雙擊 E2 單元格，把公式 =TEXT(D2,"[>=3000]0;[<2000]0;;") 復(fù)制到 E2，按回車，返回空文本；用雙擊單元格填充柄的方法返回剩余數(shù)值的結(jié)果；雙擊 F2，把公式 =TEXT(D2,"[>=3000]高;[<2000]低;中") 復(fù)制到 F2，按回車，返回“中”，再雙擊 F2 的單元格填充柄返回其它結(jié)果；操作過(guò)程步驟，如圖14所示：

圖14

2、公式說(shuō)明：

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

B、=TEXT(D2,"[>=3000]高;[<2000]低;中") 與 =TEXT(D2,"[>=3000]0;[<2000]0;;") 一樣，只不過(guò)用顯示文字來(lái)代替數(shù)值。

提示：如果要顯示特殊符號(hào)（如：占位符 0、#、*、!、@、E、e、/）需要加半角感嘆號(hào)（!），否則會(huì)返回?cái)?shù)值或值錯(cuò)誤 #VALUE!，演示如圖15所示：

圖15

（五）數(shù)值為數(shù)組 {0,1} 或 {-1,1}

（1）格式為兩個(gè)單值

1、雙擊 F2 單元格，把公式 =TEXT({0,1},D2&";"&E2) 復(fù)制到 F2，按回車，返回 80；雙擊 F2，把 0 改為 1，按回車，返回 81；選中 F2，把鼠標(biāo)移到 F2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)?+ 后，按住左鍵并拖到 F3，F(xiàn)3 返回 82；雙擊 F2，把左邊的 1 改為 5，按回車，返回 85；雙擊 F2，把 5 改為 1，1 改為 -1，按回車，返回 81；雙擊 F2，把 1 改為 -1，-1 改為 1，按回車，返回 892；再次雙擊 F2，把 E2 改為 B2，按回車，返回“粉紅襯衫”；操作過(guò)程步驟，如圖16所示：

圖16

2、公式說(shuō)明：

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

A、公式中，{0,1} 為數(shù)值參數(shù)，它是一個(gè)只有兩個(gè)元素的數(shù)組；D2&";"&E2 為格式參數(shù)，它由 D2、";" 和 E2 連接而成；數(shù)組 {0,1} 中的 0 是占位符而不是 0 本身。

B、公式為什么只返回 D2（即 80）而沒(méi)有返回 E2？首先，在 {0,1} 中，0 與 D2 對(duì)應(yīng)，1 也與 D2 對(duì)應(yīng)，因?yàn)閿?shù)組中的元素大于 0 時(shí)都與 D2 對(duì)應(yīng)，只有元素小于 0 才與 E2 對(duì)應(yīng)，因此沒(méi)有返回 E2；而 0 又是占位符，因此返回 80；其次，由于要返回一個(gè)數(shù)組需把 =TEXT({0,1},D2&";"&E2) 放在引用的函數(shù)中，否則只返回第一個(gè)值，所以沒(méi)有返回與 1 對(duì)應(yīng)的值，具體見下文的“格式為數(shù)組”。

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

A、同樣數(shù)組 {1,1} 中，兩個(gè) 1 都與 D2 對(duì)應(yīng)，也只返回 D2，但為什么返回 81，而不是返回 80？當(dāng) D2 為數(shù)值型且 D2 個(gè)位為 0 時(shí)，{1,1}中的 1 會(huì)與 D2 相加，否則{1,1}中的 1 不會(huì)與 D2 相加，因此返回 81，這一點(diǎn)可以從演示中的把 F2 往下拖到 F3 得到印證，因?yàn)?D3（82）并未返回 83。

B、另外，當(dāng) D2 為文本時(shí)，若 0 在數(shù)字的右邊，則 1 會(huì)替換 0，如 130 變 131；若 0 在數(shù)字的左邊，1 也會(huì)替換 0，如 013 變 113；若數(shù)字左右兩邊都有 0，則 1 只替換右邊的 0，如 0130 變 0131；演示如圖17所示：

圖17

C、當(dāng)數(shù)組 {5,1} 中第一個(gè)元素為 5 時(shí)，D2 同樣會(huì)加上 5，其它的以此類推。

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

A、數(shù)組 {1,-1} 中，1 與 D2 對(duì)應(yīng)，-1 與 E2 對(duì)應(yīng)；而 {-1,1} 中，-1 也與 E2 對(duì)應(yīng)，1 與 D2 對(duì)應(yīng)，由于 -1 作為數(shù)組的第一個(gè)元素，因此返回 E2（即 892）。

B、提示：如果 E2 為 890，則 890 同樣會(huì)加 1 而變?yōu)?891，由此可知，當(dāng) -1 為數(shù)組第一個(gè)元素時(shí)，D2 會(huì)與 E2 調(diào)換位置，并且會(huì)用 -1 的絕對(duì)值 與 E2 相加，若想進(jìn)一步了解，可以看下文的“格式為數(shù)組”；如果 E2 為文本，則與 D2 為文本的情況相同。

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

格式中的 B2 為文本，返回值為文本（即“粉紅襯衫”），說(shuō)明格式中引用的單元格為文本時(shí)，Text函數(shù)返回其自身。

（2）格式為數(shù)組

1、假如要查找價(jià)格為 198 對(duì)應(yīng)的銷量。雙擊 F2 單元格，把公式 =IFERROR(VLOOKUP(198,--TEXT({0,1},D2:D8&";"&E2:E8),2),"") 復(fù)制到 F2，按 Ctrl + Shift + 回車，返回 198；雙擊 F3，把公式 =IFERROR(VLOOKUP(198,--TEXT({1,-1},D2:D8&";"&E2:E8),2),"") 復(fù)制到 F3，按 Ctrl + Shift + 回車，返回 781；雙擊 F4，把公式 =IFERROR(VLOOKUP(198,--TEXT({-1,1},D2:D8&";"&E2:E8),1),"") 復(fù)制到 F4，按 Ctrl + Shift + 回車，返回空文本；操作過(guò)程步驟，如圖18所示：

圖18

2、公式說(shuō)明：

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

A、D2:D8 以數(shù)組形式返回 D2 至 D8 中的值，即返回 {"80";"82";"88";"90";"89";"80";"198"}; E2:E8 與 D2:D8 是一個(gè)意思，它返回{"892";"762";"850";"982";"700";"528";"780"}。

B、D2:D8&";"&E2:E8 變?yōu)?{"80";"82";"88";"90";"89";"80";"198"}&";"&{"892";"762";"850";"982";"700";"528";"780"}，公式執(zhí)行時(shí)，第一次分別從兩個(gè)數(shù)組中取出第一個(gè)元素并把它們連接起來(lái)，即 "80;892"；第二次分別從兩個(gè)數(shù)組中取出第二個(gè)元素，同樣把它們連接起來(lái)，即 "82;762"；其它的以此類推，最后返回 {"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"}。

C、則 TEXT({0,1},D2:D8&";"&E2:E8) 變?yōu)?TEXT({0,1},{"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"})：

第一次執(zhí)行：從格式數(shù)組取出第一個(gè)元素 "80;892"

首先從 {0,1} 取出 0，由于{0,1}中的元素大于 0 只返回格式數(shù)組每個(gè)元素左邊的元素（如只返回第一個(gè)元素 "80;892" 中 80，而不返回 892）；又因?yàn)閧0,1}中第一個(gè)元素 0 為占位符，因此返回 80。

其次，從 {0,1} 取出 1，因?yàn)?1 也與 80 對(duì)應(yīng)，又因?yàn)閧0,1}中的元素大于 0 且格式數(shù)組個(gè)位為 0 時(shí)，返回{0,1} 中的值 + 格式數(shù)組中的值，所以返回 80 + 1，即返回 81。

第二次執(zhí)行：從格式數(shù)組取出第二個(gè)元素 "82;762"

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

其次，從 {0,1} 取出 1，又因?yàn)?82 的個(gè)位不是 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) 變?yōu)?--{"80","81";"82","82";"88","88";"90","91";"89","89";"80","81";"198","198"}，接著把數(shù)組中的元素由文本轉(zhuǎn)為數(shù)值，-- 相當(dāng)Value函數(shù)，作用是把文本轉(zhuǎn)為數(shù)值，最后返回 {80,81;82,82;88,88;90,91;89,89;80,81;198,198}。

E、則公式變?yōu)?=IFERROR(VLOOKUP(198,{80,81;82,82;88,88;90,91;89,89;80,81;198,198},2),"")，進(jìn)一步計(jì)算，用 VLookUp 在數(shù)組第一列（逗號(hào) , 左邊的列）查找 198，在最后一行找到，然后返回與 198 在第二列對(duì)應(yīng)的值 198。

F、IfError函數(shù)的作用為：如果 VLookUp 返回正確的值，IfError 返回該值，否則 IfError 返回空。

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

公式與上面的公式是一個(gè)意思，只是數(shù)組 {1,-1} 不同，下面只分析該數(shù)組：

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) 變?yōu)?TEXT({1,-1},{"80;892";"82;762";"88;850";"90;982";"89;700";"80;528";"198;780"})

第一次執(zhí)行：從格式數(shù)組中取第一元素 "80;892"

首先從{1,-1} 中取出 1，由于 1 對(duì)應(yīng) 80，因此返回 80 + 1，即返回 81；其次從{1,-1} 中取出 -1，由于 -1 對(duì)應(yīng) 892，因此返回 892。

第二次執(zhí)行：從格式數(shù)組中取第二元素 "82;762"

首先從{1,-1} 中取出 1，由于 1 對(duì)應(yīng) 82，因此返回 82；其次從{1,-1} 中取出 -1，由于 -1 對(duì)應(yīng) 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}的區(qū)別）

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

第一次執(zhí)行：從格式數(shù)組中取第一元素 "80;892"

首先從{-1,1}中取出 -1，由于 -1 對(duì)應(yīng) 892，因此返回 892；其次從{-1,1}中取出 1，由于 1 對(duì)應(yīng) 80，因此返回 80 + 1，即返回 81。

第二次執(zhí)行：從格式數(shù)組中取第二元素 "82;762"

首先從{-1,1}中取出 -1，由于 -1 對(duì)應(yīng) 762，因此返回 762；其次從{-1,1}中取出 1，由于 1 對(duì)應(yīng) 82，因此返回 82。

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

從以上分析可知，格式{1,-1}與{-1,1}的區(qū)別為：當(dāng) -1 在右邊時(shí)，格式數(shù)組每個(gè)元素的右邊值返回到右邊，相當(dāng)于 if{0,1}；當(dāng) -1 在左邊時(shí)，格式數(shù)組每個(gè)元素右邊值返回到左邊，相當(dāng)于 if{1,0}；有關(guān) if{1,0}，請(qǐng)參數(shù)《Excel VLookUp怎么用If或If{0,1}實(shí)現(xiàn)兩個(gè)或三個(gè)條件的多條件查找》一文。