編按：

Excel中如何按支付類型匯總交易金額和交易筆數(shù)？這是一道經(jīng)管人員的Excel面試題。從流水賬中統(tǒng)計支付情況是經(jīng)營管理人員，財務(wù)人員常要做的事。今天就來教大家用SUMPRODUCT函數(shù)來解決這些問題吧！

面試題提供了基礎(chǔ)表，數(shù)據(jù)包含兩部分：交易明細和支付類型對照表。

考題位于匯總表中，包括3個必答題和1個附加題。答題限制不能修改表格結(jié)構(gòu)，不能用輔助列。

面試題的第一題要求匯總每小時的交易金額和金額占比。

第二題要求按支付類型匯總交易金額和交易筆數(shù)，以及它們的交易占比。

第三題按訂單號統(tǒng)計每位收銀員收銀筆數(shù)及金額。

附加題要求統(tǒng)計每位收銀員每小時內(nèi)的收銀筆數(shù)。

實際上只要精通SUMPRODUCT函數(shù)，再輔以一些基礎(chǔ)函數(shù)，解決這套題還是綽綽有余的。

申明一下，每道題的解決方法都不唯一，以下給出的思路都是以SUMPRODUCT函數(shù)為主導(dǎo)的。

第一題

按小時統(tǒng)計，涉及到三組公式，交易金額、金額占比和合計。

A列提供的是一個時間區(qū)間而不是一個小時數(shù)，基礎(chǔ)表中的時間也不是一個小時數(shù)。在不使用輔助列的情況下，用SUMPRODUCT函數(shù)非常合適。

公式為：=SUMPRODUCT((HOUR(基礎(chǔ)表!$A$2:$A$406)=ROW(A10))*基礎(chǔ)表!$E$2:$E$406)

公式中的HOUR(基礎(chǔ)表!$A$2:$A$406)部分根據(jù)基礎(chǔ)表的A列得到小時數(shù)，然后和ROW(A10)進行比較。下拉公式，通過ROW(A10)可得到10、11、12這樣的小時數(shù)序列。也可以用LEFT(A7,2)*1，其中的差異大家可以自己體會一下。

金額占比使用公式=B7/$B$21下拉，合計使用公式=SUM(B7:B20)右拉，這兩組公式非?；A(chǔ)，就不贅述了。

第二題

按支付類型統(tǒng)計交易金額和交易筆數(shù)。
在整套題中難度最大，涉及四組公式，交易金額、交易筆數(shù)、占比和合計。

匯總表里的支付類型是文字描述，而基礎(chǔ)表中的支付類型是字母代碼。必須通過基礎(chǔ)表中的對照表做轉(zhuǎn)換才能進行統(tǒng)計。
在不使用輔助列的情況下，可借助INDEX-MATCH組合幫助SUMPRODUCT完成統(tǒng)計。

交易金額的公式為：

=SUMPRODUCT((基礎(chǔ)表!$D$2:$D$406=INDEX(基礎(chǔ)表!$I$2:$I$7,MATCH(A25,基礎(chǔ)表!$J$2:$J$7,)))*基礎(chǔ)表!$E$2:$E$406)

公式為：=SUMPRODUCT((基礎(chǔ)表!$D$2:$D$406=INDEX(基礎(chǔ)表!$I$2:$I$7,MATCH(A25,基礎(chǔ)表!$J$2:$J$7,)))*1)

最后的*1是為了將邏輯值轉(zhuǎn)化為數(shù)字，方便統(tǒng)計。

占比和合計沒什么難度，不啰嗦了。

對SUMPRODUCT函數(shù)一知半解的朋友可能會感覺比較蒙圈，建議點下方鏈接去補補課。

SUMPRODUCT函數(shù)才是Excel的求和函數(shù)之王

加了*的 SUMPRODUCT函數(shù)無所不能

第三題

是整套題里最最容易的，一個單條件計數(shù)，一個單條件求和，使用COUNTIF和SUMIF就能搞定，留給大家自己練習吧。

有興趣的朋友還是可以用SUMPRODUCT函數(shù)解決這一題的，歡迎留言分享你的公式。

第四題

前三題本質(zhì)上都是單條件統(tǒng)計，只是條件的設(shè)置有不同的難度。附加題是唯一的一個多條件統(tǒng)計題，按照收銀員代碼和小時數(shù)兩個條件統(tǒng)計收銀筆數(shù)。

公式為：

=SUMPRODUCT((HOUR(基礎(chǔ)表!$A$2:$A$406)=ROW(A10))*(基礎(chǔ)表!$C$2:$C$406=B$47)*基礎(chǔ)表!$E$2:$E$406)

如果對前兩個題目徹底理解的話，這一題完全沒難度。

這套Excel面試題，如果允許使用輔助列，或者可以隨意修改表格結(jié)構(gòu)的話，很多人都可以搞定。但是在不允許的情況下全部使用公式完成，還是很考驗函數(shù)基本功的。

經(jīng)過這次測試，你認為自己是否算熟練應(yīng)用Excel呢？

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

COUNTIF和AVERAGEIF函數(shù)的6種使用場景，簡單又實用！

IF函數(shù)經(jīng)典案例：判斷一個單元格內(nèi)是否包含指定關(guān)鍵詞

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者老菜鳥；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。