Excel公式教程 (2016-07-24 11:15:19)
轉載
▼
標籤： excel公式教程 簡化公式 數據類型自動轉換分類： Excel公式教程-原理篇
算術運算符的運算項，數據類型要求是數字值；
文本運算符的運算項，數據類型要求是文本值；
引用運算符的運算項，數據類型要求是單元格引用；
函數的參數，大多數都要求是特定的數據類型。
運算符的運算項、函數的參數，如果數據類型不符合要求時，Excel有時候能夠對其自動轉換。巧妙地利用數據類型自動轉換的功能，可以簡化Excel公式。
一、當需要數字值的時候，如何利用數據類型自動轉換功能簡化公式。
1、將空單元格、空參數作0使用。例如：
=TIME(12,0,0) 公式可以簡化為：=TIME(12,,) 空參數作0使用。
=IF(A1<0,0,A1) 公式可以簡化為：=IF(A1<0,,A1)
2、將文本型數字轉成數字值，將文本型日期時間轉成序列數。例如：
=1+VALUE("￥4,000.00") 公式可以簡化為：=1+"￥4,000.00"
文本型數字"￥4,000.00"能夠自動轉換為數字值4000，無需使用VALUE函數。
=TODAY()-DATEVALUE("2016-7-1") 公式可以簡化為：=TODAY()-"2016-7-1"
文本型日期"2016-7-1"能夠自動轉換為日期序列數，無需使用DATEVALUE函數。
輸入這個公式後，如果單元格顯示為日期格式，可把單元格格式改為常規。
同樣地：
=MONTH(DATEVALUE("Aug"&1)) 公式可以簡化為：=MONTH("Aug"&1)
3、將邏輯值TRUE轉成1，FALSE轉成0。例如：
=B1+IF(A2>0,1,0) 公式可以簡化為：=B1+(A2>0)
公式中A2>0返回邏輯值TRUE或FALSE，然後轉成數字值1或0。簡化後的公式不需要使用任何函數！
同樣道理，以下公式
=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,)))+IF(A1="D",4,IF(A1="E",5,IF(A1="F",6,)))
可以簡化為：
=(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4+(A1="E")*5+(A1="F")*6
4、如果單元格A1是空單元格、文本型數字、文本型日期時間或邏輯值，以下式子都可以把A1轉換成數字值：--A1、A1+0、A1-0、A1*1、A1/1、A1^1。
二、當需要文本值的時候，如何利用數據類型自動轉換功能簡化公式。
1、將空單元格、空參數作空文本使用。例如：
=REPLACE(A1,1,4,"") 公式可以簡化為：=REPLACE(A1,1,4,) 空參數作空文本使用。
需要注意的是，如果C3是空單元格，=C3返回0，而=C3&""返回空文本。
2、將數字值、邏輯值轉成文本，將日期、時間轉成文本型序列數。例如：
="滿分："&100 返回文本“滿分：100”
假設單元格A1是文本“下班時間”，B1是時間12:00:00，
=A1&B1 返回文本"下班時間0.5"
如果想返回"下班時間12:00"，可改為以下公式：
=A1&TEXT(B1,"hh:mm")
三、當需要邏輯值的時候，如何利用數據類型自動轉換功能簡化公式。
1、將空單元格、空參數作FALSE使用。例如：
=VLOOKUP(A2,C:F,2,FALSE) 公式可以簡化為：=VLOOKUP(A2,C:F,2,) 空參數作FALSE使用。
如果A1是空單元格，=NOT(A1)返回TRUE，空單元格作FALSE使用。
2、將數字0轉成FALSE，非0數字轉成TRUE。例如：
=OR(0)返回FALSE，=OR(1)、=OR(3)都返回TRUE。
假設單元格A1為數字值，以下公式，當A1為0返回0，當A1不為0返回1：
=--OR(A1)
該公式首先用OR函數把數字0轉為FALSE，其他數字轉為TRUE；然後通過算術運算--，把邏輯值FALSE轉為0，TRUE轉為1。
3、將文本型邏輯值轉成邏輯值。
四、當函數參數需要數組的時候，自動進行數組擴展和數組運算，無需按三鍵。
例如：
=SUMPRODUCT((A1:A5>0)*A1:A5) 可自動進行數組運算而無需按Ctrl+Shift+Enter。
這種情況，同樣適於使用數組或區域引用為參數的LOOKUP、INDEX等函數：
五、運用數據類型自動轉換功能需要注意的地方。
1、如果運算項或函數參數無法轉換成所需的數據類型，則返回錯誤值#VALUE!。
2、有些函數參數是不能缺省的，但可以是空參數。比如TIME(12,,)。
3、有些函數參數缺省時與空參數代表不同的值。
比如：
VLOOKUP函數的第四個參數，缺省值是TRUE（近似匹配），空參數則是FALSE（精確匹配）。
IF函數的第三個參數，缺省值是FALSE，空參數則是0。
4、IF和CHOOSE函數可以返回任意的數值類型，如果返回值是一個引用，而當它們與引用運算符相連的時候，就返回一個引用，其他情況下返回引用裡的值。
例如，如果單元格A1、A2、A3的值分別是1、2、3，則以下公式
=IF(A3=0,A2,A3)
IF函數返回A3單元格的值3，而以下公式
=SUM(A1:IF(A3=0,A2,A3))
IF函數返回引用A3給聯合運算符，變成=SUM(A1:A3)，然後SUM對A1到A3區域求和。
