在研究數組公式前,先要明確什麽是數組?
數組嘛,可以理解為若幹相同數據的組合
比如{1,2,3,4}是數值組合在壹起,{"c";"o";"l";"a"}是字符的組合,這種只有1行的數組是橫向數組,1列的是垂直的數組,他們是壹維數組。多行多列的是二維數組。
那什麽是數組公式呢?
Excel裏會看到帶大括號的公式{},就是數組公式,但這個大括號不是手動加上去的,而是輸入公式後同時按下ctrl+shift+enter後自動帶的。比如下圖將數組1和數組2相加。
[圖片上傳失敗...(image-2443dd-1619015560800)]
理解並且創建了數組以後,要思考如何計算數組,數組公式當然也就是計算數組的公式。
比如要求總銷售額,等於單價乘以銷售數量,求壹個產品的銷售額,=C2*D2
如果要求很多產品的銷售額,當然,可以直接把公式往下拖,但我們也可以換個思路,就是用數組公式。
選擇F2:F11區域,輸入=C2:C11*D2:D11,然後同時按下ctrl+shift+enter鍵
這裏,用數組公式的好處是
另外,數組公式求總計和用sumproduct公式是壹個原理,但和sum就很不壹樣了,比用sum方便許多。
通過上面的栗子,我們知道了數組計算的過程:
我們可以再系統地理解壹下數組的計算
對數組1{1,2,3,4}乘以2,是對數組中的每個元素都乘以2,首先選中A5:D5區域,然後輸入=A2:D2*2 ,同時按下ctrl+shift+enter鍵完成數組運算,結果如圖得到{2,4,6,8}
第壹小節說,數組有壹維和二維之分,並且還有方向的區別。
- 同方向壹維數組的計算
兩個橫向的壹維數組的計算,就是元素對應相加即可。
數組1+數組2,選中A8:D8區域,輸入=A2:D2+A5:D5,同時按下ctrl+shift+enter鍵完成數組運算,結果如圖得到{6,8,10,12}
元素不對應怎麽辦?
如下圖,數組1是4個元素,數組2是5個元素,數組1缺了壹個元素和數組2的9相加,如果還硬要選擇A11:E11區域的話,系統就只能用缺失值代替了。
- 不同方向的壹維數組的計算
壹個橫向的X列壹維數組和壹個垂直的Y行壹維數組計算的結果是,壹個X列Y行的二維數組。
比如數組3是含有4個元素4列,數組4是含有3個元素的3行,他們相加,結果是壹個3行4列的二維數組。
其中的原理是這樣的,數組3可以看成3行4列的壹個二維數組,數組4也可以看成是壹個3行4列的二維數組,將其中的元素壹壹對應相加就得到了數組3數組4的結果。
壹個壹維數組和壹個二維數組計算
有了2.2的不同方向的壹維數組計算的原理,我們其實明白了,數組間的計算首先還是要把它們 調到同壹個頻道上 ,比如數組4和數組5相加,可以把數組4看成是壹個3行2列的二維數組再和同緯度的數組5相加,就可以壹壹對應了。
二維數組間的計算,就更好理解了,直接兩兩對應計算即可,對應不上的,就用缺失值填充。
如數組5和數組6相加
Excel已經有很多公式了,為什麽我們還要學數組公式呢?數組公式可以解決什麽問題呢?
相比於普通公式,數組公式可以:
使用逗號分隔各個項,將創建水平數組(壹行)。
選擇A1:C1單元格,輸入={1,2,3},然後同時按下ctrl+shift+enter鍵,Excel 使用大括號 ({ }) 將公式括在選定區域的每個單元格中,得到A1=1,A2=2,A3=3的結果。
使用分號分隔項,將創建垂直數組(壹列)。
當對包含錯誤值(例如 #N/A)的區域求和時,SUM 函數不起作用。那麽如何對包含錯誤的區域求和或求平均、計數等壹系列數值運算的操作呢?就可以用數組公式。
如下圖單價中包含壹個錯誤值#N/A,直接用average函數是計算不出平均值的,輸入=AVERAGE(IF(ISERROR(D2:D11),"",D2:D11)),同時按下ctrl+shift+enter鍵完成數組運算。
ISERROR(D2:D11)是判斷D2:D11區域有無錯誤值,有的話返回TRUE,沒有返回FALSE
IF(ISERROR(D2:D11),"",D2:D11),IF函數再來判斷,有錯誤值時(也就是為TRUE時)返回壹個空值“”,沒有錯誤值(FALSE)時返回自己所在的區域。這樣錯誤值就被空值“”避免了,也就可以計算了。
比如上節內容:
Excel裏關於if的9個函數,如何指定條件求和、計數、平均等
裏最後提到的用數組公式計算不同條件下數值的標準差。
輸入=STDEV(IF(A:A=D2,B:B,"")),然後ctrl+shift+enter 壹起按,這個數組公式的意思是,如果A列的值等於D2單元格,那麽值區域就等於D2單元格對應的值,否則就為空,接著對值區域求標準差。如果用dstdev公式還需要自己構建條件區域,數組公式就方便壹點。
還可以計算深圳和廣州的平均分,輸入公式=AVERAGE(IF(A:A=D2,B:B,"")),再同時按下ctrl+shift+enter鍵,得到的結果和我們用averageif算的是壹樣的,這個數組公式的意思是,如果D列的值等於D2單元格深圳,那麽值區域就是深圳對應的得分,否則就為空,然後再對值區域求平均。
看到這,妳學會用數組公式了嗎?
任何疑問歡迎交流:data_cola
可樂的數據分析之路