三招玩透Ｅｘｃｅｌ２００７多條件統(tǒng)計(jì)等

三招玩透Ｅｘｃｅｌ２００７多條件統(tǒng)計(jì) 陳桂鑫

在日常辦公工作中對多條件統(tǒng)計(jì)的要求是比較常見的，比如要統(tǒng)計(jì)員工記錄表中50歲以內(nèi)、工齡超過20年的高級工程師人數(shù)，或是在合同記錄中統(tǒng)計(jì)出各月份各個(gè)項(xiàng)目經(jīng)理承接的合同總金額等等。在Excel 2007中有三種方法可以輕松完成這類多條件統(tǒng)計(jì)的工作。

條件求和

Excel中的條件求和功能可以方便地按多種條件求和，不過在Excel2007中此功能默認(rèn)并沒有安裝，需要先安裝加載才能使用。下面以統(tǒng)計(jì)合同記錄中項(xiàng)目經(jīng)理陳經(jīng)理3月承接的合同總額為例進(jìn)行介紹。

安裝加載項(xiàng)

打開Excel2007，單擊左上角Office按鈕，在菜單中單擊“Excel選項(xiàng)”按鈕打開Excel選項(xiàng)窗口。在Excel選項(xiàng)窗口左側(cè)單擊“加載項(xiàng)”，然后單擊下面的“轉(zhuǎn)到…”按鈕打開“加載項(xiàng)”窗口，在其中單擊選中“條件求和向?qū)А睆?fù)選項(xiàng)。確定后會提示此功能尚未安裝，再確定按提示完成安裝即可(此時(shí)可能需要插入Office 2007安裝光盤)。

使用條件求和

用Excel2007打開要統(tǒng)計(jì)的“合同記錄”工作表，切換到 “公式”選項(xiàng)卡，單擊右側(cè)的“條件求和”打開條件求和向?qū)Т翱凇Ｔ谳斎肟蛑休斎虢y(tǒng)計(jì)數(shù)據(jù)所在區(qū)域“合同記錄!$A1:$F119” (注：你可以直接拖動(dòng)選中相應(yīng)區(qū)域，輸入框中會自動(dòng)顯示區(qū)域代碼)。

設(shè)置條件

單擊“下一步”，設(shè)置求和列為“合同金額”，第一個(gè)條件為“項(xiàng)目經(jīng)理”=“陳經(jīng)理”，然后單擊“添加條件”按鈕添加到列表中。同樣再添加“簽定日期>＝2005-3-1”和“簽定日期<=2005-3-31”兩個(gè)條件。

保存結(jié)果

單擊“下一步”，選中“選擇復(fù)制公式及條件”單選項(xiàng)，再點(diǎn)下一步。輸入存放條件“陳經(jīng)理”的單元格為“合同記錄!$I$1”。按向?qū)崾景褩l件“2005-3-1”存放在“合同記錄!$G$2”，條件“2005-3-31”存放在“合同記錄!$H$2”，求和結(jié)果則存放在“合同記錄!$I$2”。單擊完成結(jié)束求和。OK，現(xiàn)在陳經(jīng)理在3月份的合同金額總和已經(jīng)出來了，就在12單元格。

同時(shí)我們還得到了一個(gè)類似于查詢系統(tǒng)的東西。只要把11中的陳經(jīng)理改成黃經(jīng)理就可以在12中得到黃經(jīng)理在3月份的合同總金額，同理，只要修改G2、H2單元格的日期就可以查詢到其它月份的合同總金額了。

數(shù)組公式

上面的條件求和的基本原理其實(shí)也就是使用一個(gè)數(shù)組公式而已，不過那個(gè)數(shù)組公式比較死板，一次只能得到一個(gè)求和數(shù)據(jù)。我們大可自己編輯數(shù)組公式以達(dá)到更好的統(tǒng)計(jì)效果，一次性把所有項(xiàng)目經(jīng)理各月份的合同總額全部算出來。

打開保存記錄的“合同記錄”工作表所在的文件，新建一個(gè)工作表，在其中按統(tǒng)計(jì)的條件建立一個(gè)表格。

在B2單元格輸入公式=SUM(IF(合同記錄!$D:$D=$A2，IFfTEXT(合同記錄!$F:$F，“YY年MM月”)=B$1，合同記錄!$E:$E，0)，0))，公式輸入后按“Ctrl+Shift+Enter”組合鍵確認(rèn)轉(zhuǎn)換成數(shù)組公式，此時(shí)公式兩邊會出現(xiàn)大括號“{}”。馬上可以在B2單元格中看到陳經(jīng)理2005年1月的所有合同總金額。選中B2單元格，拖動(dòng)填充柄向下復(fù)制填充到B5，選中B2:B5拖動(dòng)填充柄向右復(fù)制填充到M5單元格，即可得到表中所有項(xiàng)目經(jīng)理的匯總數(shù)據(jù)。

提示

復(fù)制填充后大量的數(shù)組重算需要一段時(shí)間，此時(shí)Excel處于無響應(yīng)狀態(tài)。請耐心等候，可別以為是死機(jī)了。

公式表示，對滿足D列的單元格=$A2且從F列提取的年月值等于B$1的E列單元格進(jìn)行求和。在此對處于A列的項(xiàng)目經(jīng)理、處于1行的年月，分別對A列、1行在前面加$進(jìn)行絕對引用，以限制數(shù)組公式復(fù)制后的行列號。此外，若需要統(tǒng)計(jì)的是個(gè)數(shù)，只要把輸入的公式改成=SUM(IF(合同記錄!$D:$D=$A2，IF(TEXT(合同記錄!$F:$F，\"YY年MM月\")=B$1，1，0)，0))即可，此公式和原公式的區(qū)別在于對符合條件的項(xiàng)目返回1參與求和，而不是返回E列的單元格內(nèi)容參與求和。

數(shù)據(jù)透視表

數(shù)據(jù)透視表早在Excel2003前就有了，不過那時(shí)算是比較復(fù)雜的一項(xiàng)功能吧。在Excel2007中數(shù)據(jù)透視表得到了較大的簡化。

準(zhǔn)備工作

打開“合同記錄”工作表，在右側(cè)增加一列G列，輸入列標(biāo)題為“簽定年月”，在G2輸入公式=TEXT(F2，\"YY年MM月\")，選中G2雙擊填充柄把這個(gè)公式向下填充到最后一個(gè)數(shù)據(jù)行。這樣就可以在G列顯示各項(xiàng)記錄的簽定年月以便后面的統(tǒng)計(jì)。

創(chuàng)建數(shù)據(jù)透視表

在合同記錄表中選中統(tǒng)計(jì)數(shù)據(jù)所在區(qū)域A:G，單擊“插入”選項(xiàng)卡下的“數(shù)據(jù)透視表”圖標(biāo)打開“創(chuàng)建數(shù)據(jù)透視表”窗口，在窗口的“表／區(qū)域”輸入框中會自動(dòng)顯示數(shù)據(jù)所在區(qū)域。按默認(rèn)設(shè)置直接單擊“下一步”按鈕就會新建一個(gè)工作表，并打開“數(shù)據(jù)透視表字段列表”窗格。

選擇統(tǒng)計(jì)條件

在“數(shù)據(jù)透視表字段列表”窗格中把“合同金額”字段拖動(dòng)到“∑數(shù)值”下的列表框中，把“項(xiàng)目經(jīng)理”拖動(dòng)到“行標(biāo)簽”下，把“簽定年月”拖動(dòng)到“列標(biāo)簽”下。在新建的工作表中就會顯示出匯總結(jié)果，不過默認(rèn)是計(jì)數(shù)。在統(tǒng)計(jì)數(shù)據(jù)區(qū)(B5:M9)范圍內(nèi)右擊選擇“數(shù)據(jù)匯總依據(jù)／求和”，就可以看到和第二種方法一樣的統(tǒng)計(jì)結(jié)果了。

修改合同列表時(shí)，只要在此匯總表的數(shù)據(jù)區(qū)右擊選擇刷新，即可看到修改后的新匯總結(jié)果。通過單擊項(xiàng)目經(jīng)理所在的A4或簽定年月的B3單元格后的下拉按鈕，從彈出列表中選擇，還可自由設(shè)置要顯示的統(tǒng)計(jì)項(xiàng)目。直接雙擊各匯總數(shù)據(jù)所在單元格則可在新建工作表中顯示該數(shù)據(jù)匯總的所有記錄項(xiàng)明細(xì)。

巧用Excel把打印機(jī)當(dāng)印碼機(jī) cgx85

公司最近在進(jìn)行資質(zhì)申報(bào)，大批量的復(fù)印資料必須逐一編制頁碼，逐一用印碼機(jī)手工蓋上頁碼實(shí)在不是輕松的工作，也不夠整齊。突然想到如果使用打印機(jī)直接打開大量帶頁碼的空白頁面，那不就可以在資料上自動(dòng)編印頁碼了嗎?還可以順便算出資料的總頁數(shù)。至于想要打印大量的空白頁，最方便的莫過于Excel了。

打開Excel，按“Ctrl+↓”鍵定位到最后一行的單元格，按空格鍵輸入一個(gè)空格。然后單擊菜單“文件／頁面設(shè)置”，切換到“頁眉頁腳”選項(xiàng)卡，在此選擇一種有頁碼的頁腳，或者單擊“自定義頁腳”自己設(shè)置一個(gè)適當(dāng)?shù)捻摯a。接下來把要打印頁碼的文件按順序整理好放入打印機(jī)，單擊菜單“文件／打印”就可以自動(dòng)按順序編印上頁碼了。最后設(shè)置一下要打印的頁數(shù)，如果不知道頁數(shù)，不設(shè)置而直接打印也是可以的啦，只是打印完所有文件頁后得關(guān)掉打印機(jī)才能中斷打印。

按默認(rèn)設(shè)置這樣大概可以打印1300多頁，如果文件頁數(shù)超過這個(gè)數(shù)量，只要選中整個(gè)工作表，把行高適當(dāng)調(diào)大就可以增加大量的頁數(shù)啦!