我的老讀者應該不會完全不知道 text 函數,很多案例中我都介紹過這個函數的應用。
text 主要用來定義格式,雖然語法簡單,但是用法博大精深,因爲格式參數非常多,常學常新。
今天來看兩個應用場景。
案例 1:從下圖 1 的日期列中依次提取出年、月、日,分別用中文和數字兩種形式顯示。
效果如下圖 2 所示。
1. 在 D2 單元格中輸入以下公式 --> 下拉複制公式:
=TEXT(B2,"[DBnum1]yyyy年")
公式釋義:
DBnum1 是一種數值化格式代碼,作用是將數值轉換爲小寫中文;
yyyy:提取出日期中的年份,並且以 4 位字符的形式顯示;
年:在上述年份後面加上中文字符“年”
2. 在 E2 單元格中輸入以下公式 --> 下拉複制公式:
=TEXT(B2,"[DBnum1]m月")
公式釋義:
m:提取出日期中的月份,並以一位字符顯示;當月份爲兩位數時,會自動顯示兩位數的完整月份名稱;
3. 在 E2 單元格中輸入以下公式 --> 下拉複制公式:
=TEXT(B2,"[DBnum1]d日")
公式釋義:
d:提取出日期中的日,以一位字符顯示;超過一位的自動顯示實際位數
1. 在 G2 單元格中輸入以下公式 --> 下拉複制公式:
=TEXT(B2,"[DBnum4]yyyy年")
公式釋義:
DBnum4 是另一種數值化格式代碼,用于將數值轉換爲數字;
公式的其余部分與第一套解決方案中的完全一致
2. 下面分別是“月”和“日”的公式:
=TEXT(B2,"[DBnum4]m月")
=TEXT(B2,"[DBnum4]d日")
計算下圖 1 中實際完成業績較之指標的增幅,效果如下圖 2 所示。
普通情況下,增幅公式如下。
1. 在 D2 單元格中輸入以下公式 --> 下拉複制公式:
=C2/B2-1
2. 選中公式區域 --> 將格式設置爲百分比
如果要把正負號換成個性化的文字,可以這樣設置。
3. 將公式修改如下:
=TEXT(C2/B2-1,"超標 0%;低于 0% 指標;持平")
公式釋義:
原本的公式不變,成爲了 text 函數的第一個參數;
"超標 0%;低于 0% 指標;持平":
第二個參數用 ; 分隔成了三段,分別代表正數、複數和 0 所顯示的格式;
0% 相當于變量;
文本部分會原樣顯示在單元格中