百變大咖——TEXT
如果說TRIM、CLEAN、RIGHT、LEFT和MID函式是文字的變形專家(只能對資料進行外觀上的改變),那麼TEXT函式則稱得上不折不扣的化妝大師、美顏專家。
它可以根據資料的特點,按照我們預設的格式,改造字串的樣式,比如:日期轉成星期、數字新增千分位、位數不同的編號統一成相同的編號、數字自動新增備註……
Text函式的基本語法
TEXT 函式將數值轉換為按指定數字格式表示的文字。它可透過格式程式碼向數字應用格式,進而更改數字的顯示方式。
TEXT(value,轉換後的格式)
引數:
value:可以是數值、計算結果為數字值的公式或對包含數字值的單元格的引用。
轉換後的格式:可以是字串、包含字串的單元格引用、名稱或公式。
作用:將指定單元格的內容轉換成指定的格式。
下面透過一個案例說明TEXT函式的好用之處
將日期變為星期
比如有如下的表格,現要將日期中週六日的提取出來,如何操作?
你會怎麼做呢?有個小夥伴說:使用自定義格式轉換成星期的形勢,然後再進行篩選就可以啦!
結果:
進行篩選時,發現並沒有出現我們想要的星期,還是月份。因為雖然設定了格式,只是顯示結果展示改變了,但是實際的內容還是日期。
這時候我們使用TEXT函式會更加方便,但是我們需要增加一列進行篩選。在日期後面新增一列。然後輸入=TEXT(B2,“aaaa”)回車,發現星期列都變成星期的格式了,此時對新增的星期列進行篩選。
附上一個對日期時間格式話的表格,上面使用到的aaaa表示中文星期幾的全稱,如果是aaa則是中文星期幾的簡稱,也可以是dddd表示英文星期幾的全程,那有沒有ddd呢?大家可以試一下
靈活的佔位符
什麼是佔位符?形象地說,佔位符就是佔據字元位置的符號,常用的數字佔位符有0、#、?等,上一節中出現的y、m、d是日期佔位符的種類。毫不誇張地說,只有理解了各種佔位符的作用,才能真正選好TEXT函式的格式程式碼。
而佔位符的使用我們在前面的自定義格式中已經給大家介紹過。比如“0”和“#”的區別在於,一個會在需要的時候補“0”,一個不會。
如果數字是23。12,使用TEXT函式進行轉換TEXT(12323。12,“0。000”),結果會是什麼呢?
上圖中第三個示例,加了一個逗號“,”可以將數字轉換成英文形式,給數字每隔3位新增一個分隔符。
當然還可以使用條件格式,條件格式分成四部分。
真正的大咖是可以應對任何情況的,正所謂“萬變不離其宗”。前面介紹的TEXT函式的第二引數的四個區段,分別對應正數、負數、零和文字,這樣還是有侷限性。事實上,TEXT函 數允許自定義各區段可適用的條件,這就使得能選擇的條件不侷限於正數、負數、零和文字四個固定的條件。
比如上面銷售表中顯示盈虧的。要根據是正數還是負數判斷盈虧。使用的就是IF函式的功能(=TEXT(I2,“盈利0。0元;虧損0。0元;平”
當然還可以對小時工的上班情況求總時長,有或者透過身份證判斷性別的男女。但是這些不能單獨靠TEXT一個函式完成,需要其他函式一起來工作才可以。
是非分明:邏輯函式
其實我們前面給大家分享過Python的條件語句,在Excel中也存在“是非”和“條件”的關係判斷。是非判斷很簡單就是真假的判斷。
Excel中有:
IF(判斷條件,結果1,結果2)
判斷條件為True的時候返回結果1,判斷條件為False的時候返回結果2。
FERROR(value, value_if_error)
為遮蔽錯誤值而生的一個函式,它通常巢狀在其他函式或者公式的外面,一旦出現錯誤值可以將錯誤轉化成我們希望的數值。
比如判斷合同是否到期,如果到期給出提醒,如果沒有到期則顯示剩餘天數
我們使用IF函式完成則為:=IF(B2-TODAY()
但是如果是多條件的,則要考慮使用IF的巢狀完成。比如考試成績優良可差的評比
則可以使用公式:=IF(E7E7,“可”,IF(270>E7,“良”,“優”)))
當然如果涉及在某個區間的時候,我們就可以透過AND或者OR完成多個條件的定義。比如如果語文數學英語三門科目都超過88分,即可評為優秀學生。這個公式如何寫呢?
=IF(AND(B2>88,C2>88,D2>88),“優秀學生”,“加油”)
還可以考慮結合or函式使用。
比如:我們需要使用
IF
和
OR
計算銷售人員是否有資格獲得佣金
IFERROR函式功能:如果公式的計算結果錯誤,則返回指定的 值;否則返回公式的結果。它有兩個引數,ྲ如圖
計算每天的銷量我們平常使用銷量/售賣天數就可以計算得出,但是如果在售賣天數中出現0或者空值,則會出現#DIV/0!的資料,說明有錯,此時我們使用IFERROR就可以很好的解決這個問題。
智慧計算:計算型別函式
Excel強大的資料處理能力還體現在它的數學計算和資料統計方面。工作中需要用到計算的情況非常多,彙總成績、計算工時、費用 花銷、統計人數。。。。。這時候如果還按計算器的話,簡直像回到了原始社會。
本次就給大家介紹下:
SUM函式
SUMIF函式
PRODUCT函式
SUMPRODUCT函式
ROUND函式
案例1:
針對下面的資料
1。 求每天平均銷量並四捨五入取整
2。 求洗衣機的銷量總和
參考公式:
=ROUND([@銷量]/[@售賣天數],0) 或者 =ROUND(C2/D2,0)
=SUMIF(B2:B11,“洗衣機”,C2:C11)
案例2:
以下圖成績表為例,求數學考試分數在80分以上同學總分
參考公式:
=SUMIF(C2:C11,“>80”)
統計分析型函式
AVERAGE函式
AVERAGEIF函式
MODE函式
COUNT函式
COUNTIF函式
MAX函式
RANK函式
案例1:
針對下表
1。 求語文、數學、英語的平均成績
2。 求語文參加考試的的人數
3。 求參加考試的男生人數
4。 總分最高分
5。 按照總分進行排位
參考公式:
=AVERAGE(B2:B11)
=AVERAGE(C2:C11)
=AVERAGE(D2:D11)
=COUNT(B2:B11) 注意如果裡面有中文則不被統計
在姓名列後面增加性別列,=COUNTIF(B2:B11,“男”)
=MAX(F2:F11)
=RANK(F2,F2:F11,0)
案例2:
完成表格中空白部分的內容
參考公式:
=SUM(B2:G2) 總銷量
=ROUND(AVERAGE(B2:G2),0) 月均銷量
=RANK(H2,
H2:
H8) 月均銷量排名
=MAX(H2:H8) 最高總銷量
=MIN(H2:H8) 最低總銷量
如果要計算月銷的範圍個數如何實現呢?可以使用FREQUENCY函式或者IFS函式
1。 選擇單元格區域B12:B15。
2。 在編輯欄中輸入公式“=FREQUENCY(I2:I8,E12:E15)”
3。 按下【Ctrl+Shift+Enter】鍵,得到不同月均銷量段的分店數。
最後結果:
寫在最後
▲長按掃碼預約試聽課程