EXCEL操作技巧培訓(xùn)課件

前言EXCEL操作技巧分享集團(tuán)人力資源

XXXX年XX月課程目錄前言操作與技巧公式與函數(shù)數(shù)據(jù)透視表圖表總結(jié)123456700010203040506072前言——EXCEL有些什么VBA編程VBA編程透視表透視表函數(shù)、圖表函數(shù)、圖表基本操作基本操作3前言——如何成為EXCEL高手No.1積極的心態(tài)“興趣是最好的老師、壓力是前進(jìn)的動力”No.2正確的學(xué)習(xí)方法循序漸進(jìn)善用資源，學(xué)以致用多閱讀多實(shí)踐No.3持之以恒的努力，每天進(jìn)步一點(diǎn)點(diǎn)4前言——關(guān)于本次培訓(xùn)重點(diǎn)掌握EXCEL基本操作技巧，及解決問題的思路構(gòu)成理論+案例講解說明采用Excel2007進(jìn)行講解5課程目錄前言操作技巧公式與函數(shù)數(shù)據(jù)透視表圖表總結(jié)12345670001020304050607操作技巧查找與替換條件格式選擇性粘貼自動篩選，高級篩選數(shù)據(jù)有效性批量填充“相同內(nèi)容”效率加倍的快捷鍵6操作技巧——查找與替換開始→查找和選擇→查找/替換查找：Ctrl+F替換：Ctrl+H查找與替換的高級設(shè)置（字體、顏色…)專業(yè)技術(shù)職稱性別民族出生年月文化程度參加工作時間上年績效等級會計員男漢1978-10本科1996-10A經(jīng)濟(jì)師男漢1971-08本科~a助會師女漢1974-08本科1992-11b*女漢1971-05大專1989-12b助經(jīng)師女漢1962-09大專~b*?男漢??高中1979-10A經(jīng)濟(jì)員女蒙古1963-04大專1983-12b助經(jīng)師男漢1963-09大專1980-11B查找與替換的范圍可以選擇按工作表或工作薄的查找查找方式，可選擇按行或按列查找7操作技巧——條件格式開始→條件格式條件格式常用于自動標(biāo)記出錯誤、異常、值得注意的數(shù)據(jù)根據(jù)單元格數(shù)值設(shè)定條件格式根據(jù)公式設(shè)定條件格式條件限制選擇條件如大于、小于、介于等設(shè)置滿足條件時顯示的格式8操作技巧——選擇性粘貼開始→粘貼→選擇性粘貼選擇性粘貼可根據(jù)數(shù)據(jù)運(yùn)用的需要實(shí)現(xiàn)“部分”粘貼功能9專業(yè)技術(shù)職稱性別民族出生年月文化程度參加工作時間月基本工資經(jīng)濟(jì)師男漢1971-08本科1992-12

4,282.00經(jīng)濟(jì)員男苗1973-10本科1994-08

2,097.00助經(jīng)師男漢1963-09大專1980-11

2,340.00

女漢1971-09本科1989-12

4,196.00操作技巧——自動篩選數(shù)據(jù)→篩選按顏色篩選按文本（數(shù)字）篩選專業(yè)技術(shù)職稱性別民族出生年月文化程度參加工作時間月基本工資會計員男漢1978-10本科1996-10

1,966.00經(jīng)濟(jì)師男漢1971-08本科1992-12

4,282.00經(jīng)濟(jì)員男苗1973-10本科1994-08

2,097.00經(jīng)濟(jì)師女漢1955-10高中1979-12

6,428.00助會師女漢1974-08本科1992-11

5,284.00助經(jīng)師男漢1963-09大專1980-11

2,340.00

女漢1971-09本科1989-12

4,196.00助會師男漢1966-03大專1985-03

8,685.00

女漢1967-01本科1989-12

8,635.00篩選結(jié)果10操作技巧——高級篩選數(shù)據(jù)→篩選→高級篩選篩選結(jié)果部門崗位職務(wù)專業(yè)技術(shù)職稱性別民族出生年月文化程度參加工作時間月基本工資財務(wù)部科員會計員男漢1978-10本科1996-10

1,966.00科技部總經(jīng)理經(jīng)濟(jì)師男漢1971-08本科1992-12

4,282.00財務(wù)部總經(jīng)理經(jīng)濟(jì)員男苗1973-10本科1994-08

2,097.00辦公室總經(jīng)理經(jīng)濟(jì)師女漢1955-10高中1979-12

6,428.00財務(wù)部科員助會師女漢1974-08本科1992-11

5,284.00財務(wù)部科員

女漢1971-05大專1989-12

9,753.00辦公室科員助經(jīng)師女漢1962-09大專1978-12

5,368.00科技部科員

男漢1961-06高中1979-10

4,288.00科技部科員經(jīng)濟(jì)員女蒙古1963-04大專1983-12

8,079.00財務(wù)部總經(jīng)理助經(jīng)師男漢1963-09大專1980-11

2,340.00科技部總經(jīng)理

女漢1971-09本科1989-12

4,196.00財務(wù)部科員助會師男漢1966-03大專1985-03

8,685.00辦公室科員

女漢1967-01本科1989-12

8,635.00條件1條件2部門文化程度財務(wù)部本科部門崗位職務(wù)專業(yè)技術(shù)職稱性別民族出生年月文化程度參加工作時間月基本工資財務(wù)部科員會計員男漢1978-10本科1996-10

1,966.00財務(wù)部總經(jīng)理經(jīng)濟(jì)員男苗1973-10本科1994-08

2,097.00財務(wù)部科員助會師女漢1974-08本科1992-11

5,284.00財務(wù)部科員助會師女漢1977-02本科1999-10

3,832.0011操作技巧——防止重復(fù)錄入12選取要防止重復(fù)錄入的單元格區(qū)域，數(shù)據(jù)-有效性-自定義-公式：如果重復(fù)錄入，會提示錯誤并清除錄入內(nèi)容操作技巧——制作下拉菜單13例：如下圖所示，要求在銷售員一列設(shè)置可以選取的下拉菜單。設(shè)置步驟：步驟1：選取銷售員一列需要設(shè)置下拉菜單的單元格區(qū)域（這一步不能少），打開數(shù)據(jù)有效性窗口（excel2003版數(shù)據(jù)菜單-有效性，excel2007和2010版本數(shù)據(jù)選項(xiàng)卡-數(shù)據(jù)有效性-數(shù)據(jù)有效性），在窗口中的“設(shè)置”選項(xiàng)卡里選“序列”。步驟2：在來源輸入框里我們需要設(shè)置下拉菜單里要顯示的內(nèi)容，有兩種設(shè)置方法。1直接輸入法。在來源后的框里輸入用“,”（英文逗號）連接的字符串：張一，吳漢青，劉能，將文勝，李大民2引用單元格的內(nèi)容法。如果銷售員在單元格B4:B8區(qū)域里，在“來源”后輸入或點(diǎn)框最后的折疊按鈕選這個區(qū)域分析：在excel里制作下拉菜單有好幾種方法，我們這里是介紹用數(shù)據(jù)有效性設(shè)置下拉菜單，操作技巧——制作二級聯(lián)動下拉菜單14例：如下圖所示，在手機(jī)列輸入或選取蘋果，型號下拉里會顯示所有蘋果手機(jī)的所有型號，如果手機(jī)列輸入三星，在型號列下拉菜單顯示所有三星的型號。手機(jī)列選取三星大家應(yīng)該明白什么是二級下拉聯(lián)動菜單了，還等什么，我們一起開始制作吧。步驟1：設(shè)置數(shù)據(jù)源區(qū)域。就是把手機(jī)名稱和型號整理成如下圖格式備用，存放的位置隨意。操作技巧——制作二級聯(lián)動下拉菜單步驟3：設(shè)置數(shù)據(jù)有效性。選取型號列，打開數(shù)據(jù)有效性窗口，在來源中輸入=indirect(D5)進(jìn)行如下設(shè)置后，二級聯(lián)動菜單設(shè)置完成。步驟2：批量定義名稱。選取手機(jī)名稱和型號區(qū)域后，打開指定名稱窗口（excel2003版里，插入菜單-名稱-指定，07和10版公式選項(xiàng)卡-定義的名稱組-根據(jù)所選內(nèi)容創(chuàng)建），選取窗口上的“首行”復(fù)選框。如下圖所示。操作技巧——表格只能填寫不能修改操作步驟步驟1：按ctrl鍵，選取所有黃色的區(qū)域，然后按ctrl+1（數(shù)字1）打開“單元格格式”窗口，在鎖定選項(xiàng)卡中，去掉“鎖定”前面的勾選。步驟2：保護(hù)工作表。excel2003版工具菜單-保護(hù)-保護(hù)工作表。excel2010版審閱選項(xiàng)卡-保護(hù)工作表。

按上述步驟操作后，你試著修改黃色區(qū)域單元格：ok。你試著在黃色之外的區(qū)域修改或插入行/列，就會彈出如下圖所示的提示。

操作技巧——批量填充“相同內(nèi)容”的問題在單元格區(qū)域中批量填充“相同內(nèi)容”，這里通常要涉及Excel中定位功能的使用。定位的目的在于選中要填充的單元格。選中全表，按下Ctrl+G--定位條件--空值--單擊確定，在單元格編輯欄上輸入“沒數(shù)據(jù)”，按住Ctrl然后再按Enter,這時候我們就可以看到所有的空白單元格都被填上了“沒數(shù)據(jù)”。請看下面的演示：操作技巧——效率加倍的快捷鍵在單元格中導(dǎo)航的快捷鍵

選擇單元格／選擇單元格區(qū)域

上、下、左、右移動箭頭方向鍵選擇單元格區(qū)域Shift+方向鍵Ctrl+Shift+方向鍵移動到右側(cè)單元格Tab鍵選擇單元格所在的整行Shift+空格鍵（可能需要關(guān)閉輸入法）移動到左側(cè)單元格Shift+Tab鍵選擇單元格所在的整列Ctrl+空格鍵（可能需要關(guān)閉輸入法^^）移動到下方單元格回車鍵選擇單元格所在的區(qū)域Ctrl+Shift+*(同Ctrl+A)移動到上方單元格Shift+回車鍵隱藏單元格所在的整行Ctrl+9移動到單元格區(qū)域的邊緣Ctrl+箭頭方向鍵取消隱藏單元格所在的整行Ctrl+Shift+9擴(kuò)展選取單元格區(qū)域Ctrl+Shift+箭頭方向鍵隱藏單元格所在的整列Ctrl+0達(dá)到工作表的開始Ctrl+Home鍵取消隱藏單元格所在的整列Ctrl+Shift+0達(dá)到工作表的末尾Ctrl+End鍵插入單元格Ctrl+Shift++到達(dá)某行的開始Home鍵刪除單元格Ctrl+-全選活動單元格區(qū)域Ctrl+A快速輸入/編輯/格式操作

標(biāo)準(zhǔn)操作

在選中的單元格區(qū)域中一次性輸入Ctrl+回車鍵保存Ctrl+S重復(fù)上一次操作F4鍵打開Ctrl+O輸入當(dāng)前日期Ctrl+;(分號)打印Ctrl+P輸入當(dāng)前時間Ctrl+Shift+;(分號)新建工作表Ctrl+N依據(jù)首行內(nèi)容向下填充Ctrl+D鍵復(fù)制Ctrl+C依據(jù)首列內(nèi)容向右填充Ctrl+R鍵剪切Ctrl+X篩選/取消篩選Ctrl+Shift+L鍵粘貼Ctrl+V編輯單元格F2鍵在單元格中換行Alt+回車鍵工作簿/工作表導(dǎo)航

設(shè)置單元格格式Ctrl+1工作簿導(dǎo)航ATL+Tab\Ctrl+Tab查找/替換對話框Ctrl+F，Ctrl+H工作表導(dǎo)航Ctrl+PageUP/Down定位Ctrl+G（F5鍵)18課程目錄前言操作技巧公式與函數(shù)數(shù)據(jù)透視表圖表總結(jié)12345670001020304050607公式與函數(shù)常用運(yùn)算符介紹認(rèn)識公式、函數(shù)單元格引用邏輯判斷邏輯函數(shù)數(shù)據(jù)匯總統(tǒng)計類函數(shù)查找引用類函數(shù)19公式與函數(shù)——常用運(yùn)算符介紹類型符號說明實(shí)例結(jié)果算數(shù)運(yùn)算符-算術(shù)運(yùn)算符：負(fù)號=8*-5=-40-40%算術(shù)運(yùn)算符：百分號=60*5%=33^算術(shù)運(yùn)算符：乘冪=3^2=99=16^(1/2)=44*和/算術(shù)運(yùn)算符：乘和除=3*2/4=1.51.5+和-算術(shù)運(yùn)算符：加和減=3+2-5=00比較運(yùn)算符=,<>比較運(yùn)算符：等于、不等于、大于、小于、大于等于和小于等于=(A1=A2)判斷A1與A2相等FALSE>,<=(B1<>"ABC")判斷B1不等于"ABC"TRUE>=,<==(A10>=5)判斷A10大于等于5TRUE文本連接符&文本運(yùn)算符：連接文本="jim"&"Zhang"返回"JimZhang"jimZhang引用:區(qū)域運(yùn)算符：冒號=SUM(A1:B10)引用冒號兩邊所引用的單元格為左上角和右下角之間的所有單元格組成的矩形區(qū)域210空格交叉運(yùn)算符：單個空格=SUM(A1:B5A4:D9)引用A1:B5與A4:D9的交叉區(qū)域，公式相當(dāng)于=SUM(A4:B5)34,聯(lián)合運(yùn)算符：逗號=AVERAGE(A1:A5,B6:B10)引用A1:A5和B6:B10兩個不連續(xù)的單元格區(qū)域10.520公式與函數(shù)——常見的錯誤信息序號錯誤信息原因1#DIV/0!當(dāng)數(shù)字除以零(0)時，會出現(xiàn)此錯誤2#N/A當(dāng)數(shù)值對函數(shù)或公式不可用時，將出現(xiàn)此錯誤3#NULL!如果指定兩個并不相交的區(qū)域的交點(diǎn)，將出現(xiàn)此錯誤。引用之間的交叉運(yùn)算符為空格。4#NAME?當(dāng)MicrosoftOfficeExcel不識別公式中的文本時，會出現(xiàn)此錯誤5#NUM!公式或函數(shù)中使用了無效的數(shù)值，則會出現(xiàn)此錯誤6#REF!當(dāng)單元格引用無效時，會出現(xiàn)此錯誤7#VALUE!使用錯誤的參數(shù)或運(yùn)算對象類型，會出現(xiàn)此錯誤21公式與函數(shù)——公式？函數(shù)？

公式：是以等號“=”開頭，通過使用運(yùn)算符將數(shù)據(jù)、函數(shù)等元素按一定順序連接在一起，從而實(shí)現(xiàn)對工作表中的數(shù)據(jù)執(zhí)行計算的等式。例如C1=A1+A2+A3+A4+B1+B2+B3+B4函數(shù)：簡而言之，函數(shù)就是預(yù)先定義好了的公式。比如上例中的公式就可以用函數(shù)C1=SUM(A1:B4)代替。

函數(shù)不僅可以簡化公式，而且具有僅用運(yùn)算符連接的一般公式所不能替代的功能。例如查找引用，邏輯判斷等等。22公式與函數(shù)——單元格引用1.相對引用當(dāng)把公式復(fù)制到其它單元格中時，行或列的引用會改變。2.絕對引用當(dāng)把公式復(fù)制到其它單元格中時，行和列的引用不會改變。絕對引用是在列字母和行數(shù)字之前都加上美元符號”$”,如$A$4,$C$63.混合引用

混合引用是指在一個單元格引用中,既有絕對引用,也有相對引用，在行號前面加了一個$符號，行號就固定不變了，而列標(biāo)前面沒有加$符號，列標(biāo)就會發(fā)生變化.

例:行變列不變:$A4

列變行不變:A$44.單元格引用快捷鍵：F423如果有興趣，可以練習(xí)一下看看，這樣會更有助于對引用方式的理解哦。公式與函數(shù)——邏輯判斷Excel的邏輯值只有2個：TRUEFALSETRUEFALSE是函數(shù)的關(guān)鍵，可以這么說：任何函數(shù)都是在解決TRUE還是FALSE的問題TRUEFALSE與運(yùn)算符進(jìn)行各種計算時，請牢記：TRUE=1FALSE=0，請務(wù)必牢記24公式與函數(shù)——邏輯函數(shù)（IF、AND、OR）25考核處罰考核等級優(yōu)秀員工900A優(yōu)502D800B600D701C950S優(yōu)A7B7C7=IF(A2>=95,"S",IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","D"))))=IF(AND(OR(C2="S",C2="A"),B2=0),"優(yōu)","")>=95分—S,90～95—A,80～90—B,70～80—C,其他分—D考核等級為S或A,且處罰為0的為優(yōu)秀員工公式與函數(shù)——數(shù)據(jù)匯總統(tǒng)計類函數(shù)Sum函數(shù)解析26案例1:匯總多個表格中相同單元格的數(shù)據(jù)（sum)公式：=SUM(‘*’!D3)*就是通配符，代表著任意字符，用在!前面就表示除當(dāng)前外的工作表公式與函數(shù)——數(shù)據(jù)匯總統(tǒng)計類函數(shù)Sumif函數(shù)解析27案例2:如何匯總某品名的銷售數(shù)量（sumif)單條件匯總品名銷售量公式鐵觀音225=SUMIF(B3:B26,F3,D3:D26)品名銷售量公式

*茶*493=SUMIF(B3:B26,F7,D3:D26)品名銷售量公式

???茶134=SUMIF(B3:B26,F11,D3)公式語法：=sumif(條件區(qū)域，條件，求和區(qū)域）28

數(shù)據(jù)計數(shù)類函數(shù)，關(guān)鍵詞:count,,countif,countifs公式與函數(shù)——數(shù)據(jù)匯總統(tǒng)計類函數(shù)1、COUNTCOUNT是個數(shù)據(jù)控，只要看到阿拉伯?dāng)?shù)字，不管它是數(shù)值、日期還是時間，它就開始計數(shù)（統(tǒng)計有日期和數(shù)值的單元格個數(shù)）2、COUNTA(COUNT+A)COUNTA,是個直腸子，看見單元格里有東西，它就開始數(shù)數(shù)（統(tǒng)計所有非空單元格個數(shù)）3、COUNTIF(COUNT+IF)COUNTIF,只要符合規(guī)定它就開始計數(shù)，（統(tǒng)計滿足條件的單元格個數(shù)）4、COUNTIFS(COUNT+IF+S)COUNTIFS，統(tǒng)計滿足多個條件的單元格個數(shù)5、COUNTBLANKCOUNTBLANK，統(tǒng)計空白單元格的個數(shù)29

數(shù)據(jù)計數(shù)類函數(shù)，關(guān)鍵詞:count,案例解析公式與函數(shù)——數(shù)據(jù)匯總統(tǒng)計類函數(shù)部門姓名工號性別年齡6月份銷售額(萬元)營銷一部A2男352000B3女301500營銷二部C6女332500D10男281600營銷三部E15女402100F30男191800A2:A7C2:C7D2:D7E2:E7F2:F7部門數(shù)量總?cè)藬?shù)男性員工數(shù)平均年齡最高營業(yè)額363312500=COUNTA(A2:A7)=COUNT(C2:C7)=COUNTIF(D2:D7,"男")=AVERAGE(E2:E7)=MAX(F2:F7)A2:A7非空值計數(shù)C:C7計數(shù)D2:D7值為"男"的計數(shù)求平均年齡求最高銷售額30

數(shù)據(jù)計數(shù)類函數(shù)，關(guān)鍵詞:SUBTOTAL,案例解析公式與函數(shù)——數(shù)據(jù)匯總統(tǒng)計類函數(shù)語法subtotal(參數(shù)1，區(qū)域1，區(qū)域2。。。區(qū)域n)

subtotal的強(qiáng)大之處在于參數(shù)1，參數(shù)1不同的值會有不同的計算方式，下面列舉常用的幾個值參數(shù)1同等函數(shù)意義1average求算術(shù)平均值2count

數(shù)值計數(shù)3counta

非空單元格計數(shù)4

max最大值5

min最小值6

product求乘積9sum求和101average求算術(shù)平均值102count數(shù)值計數(shù)103counta非空單元格計數(shù)104

max最大值105

min最小值106product求乘積109sum求和1-9表示區(qū)域中如果有的行被隱藏了，那么隱藏的行還是參與計算的。101-109表示區(qū)域中隱藏的行不參與計算?！咀ⅰ恐皇请[藏行會有區(qū)別，隱藏列不會有計算區(qū)別的。公式與函數(shù)——查找引用類函數(shù)VLOOKUP函數(shù)解析（辦公室函數(shù)使用率最高的函數(shù)之一）31主要用途：例如：按員工號查找某位員工的姓氏，或通過查找員工的姓氏查找該員工的電話號碼。下圖為某公司的員工信息表，有N多行數(shù)據(jù)，我們需要找出某員工的詳細(xì)記錄。函數(shù)解析：需要查找的姓名記錄在單元格F5中，然后在G5中寫公式：

=VLOOKUP($F$5,$B$1:$D$10,2,0)由于員工部門對于員工信息表在第2列（以姓名所在列為1，向右數(shù)），故第三個參數(shù)為2。因?yàn)槲覀兿胍_的找到“天竺僧”的部門，即第四個參數(shù)采用精確查找的查找方式，所以需要寫為False，而在Excel中，F(xiàn)alse的邏輯值為0，因此可以簡寫為0。采用上述的公式，我們就很容易的查到天竺僧是的部門是生產(chǎn)部。如果需要了解該員工的詳細(xì)記錄的話，可以繼續(xù)在其他單元格里書寫公式，當(dāng)然第三個參數(shù)會有變化，比如職務(wù)在第3列就應(yīng)該寫成3公式與函數(shù)——查找引用類函數(shù)HLOOKUP函數(shù)解析32績效等級SAB+BB-CD直接支持人員+職級為E級及以上人員21.51.2510.750.50間接支持人員1.81.41.210.750.60員工類型工號績效等級等級系數(shù)直接支持人員+職級為E級及以上人員3A1.5=HLOOKUP(C7,$B$1:$H$3,2,0)間接支持人員6A1.4=HLOOKUP(C8,$B$1:$H$3,3,0)B1:H3在區(qū)域B1:H3查找C7值對應(yīng)列第2行的數(shù)據(jù)；在區(qū)域B1:H3查找C8值對應(yīng)列第2行的數(shù)據(jù)C7C8公式與函數(shù)——查找引用類函數(shù)INDEX函數(shù)解析33INDEX數(shù)組形式：獲得目標(biāo)單元格或區(qū)域的引用=index(array,row_num,[col_num])引用形式：獲得目標(biāo)單元格引用=index(reference,row_num,[col_num],[area_num])數(shù)組形式行、列參數(shù)為0或省略時，公式以數(shù)組形式結(jié)束公式與函數(shù)——查找引用類函數(shù)MATCH函數(shù)解析34公式與函數(shù)——查找引用類函數(shù)函數(shù)組合（利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值）351、反向查找【例】如下圖所示，要求根據(jù)產(chǎn)品名稱，查找編號。分析：先利用Match函數(shù)根據(jù)產(chǎn)品名稱在C列查找位置=MATCH(B13,C5:C10,0)再用Index函數(shù)根據(jù)查找到的位置從B列取值。完整的公式即為：=INDEX(B5:B10,MATCH(B13,C5:C10,0))2、雙向查找【例】如下圖所示，要求根據(jù)月份和費(fèi)用項(xiàng)目，查找金額分析：先用MATCH函數(shù)查找3月在第一行中的位置=MATCH(B10,$A$2:$A$6,0)再用MATCH函數(shù)查找費(fèi)用項(xiàng)目在A列的位置=

MATCH(A10,$B$1:$G$1,0)最后用INDEX根據(jù)行數(shù)和列數(shù)提取數(shù)值INDEX(區(qū)域,行數(shù),列數(shù)）=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))公式與函數(shù)——查找引用類函數(shù)函數(shù)組合（利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值）363、多條件查找【例】如下圖所示，要求根據(jù)入庫時間和產(chǎn)品名稱，查找入庫單價。分析：由于match的第二個參數(shù)可以支持合并后的數(shù)組所以可以直接進(jìn)行合并查找：=MATCH(C32&C33,B25:B30&C25:C30,0)查找到后再用INDEX取值=INDEX(D25:D30,MATCH(C32&C33,B25:B30&C25:C30,0))由于公式中含有數(shù)組運(yùn)算(一組數(shù)同另一組數(shù)同時運(yùn)算)，所以公式需要按ctrl+shift+enter三鍵完成輸入

公式與函數(shù)——多條件判斷1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1、多條件區(qū)間判斷【例1】按銷售量計算計成比率。2、多條件組合判斷【例2】如果金額小于500并且B列為“未到期”則返回補(bǔ)款，否則為空=IF(AND(A2<500,B2="未到期"),"補(bǔ)款","")說明：兩個條件同時成立用AND,任一個成立用OR函數(shù)。公式與函數(shù)——多條件求和1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

業(yè)務(wù)區(qū)域發(fā)生額（萬元）結(jié)算方式一、計算區(qū)域?yàn)槿A南區(qū)的發(fā)生額

華北區(qū)50現(xiàn)金結(jié)果公式公式寫法西南區(qū)60轉(zhuǎn)賬361=SUMIF(A:A,"華南區(qū)",B:B)=SUMIF(條件區(qū)域,條件,求和區(qū)域)華南區(qū)45匯款華東區(qū)80匯款二、追加一個條件，計算業(yè)務(wù)區(qū)域?yàn)椤叭A南區(qū)”、結(jié)算方式為“轉(zhuǎn)賬”的發(fā)生額西南區(qū)60匯款結(jié)果公式公式寫法華南區(qū)91轉(zhuǎn)賬196=SUMIFS(B:B,A:A,"華南區(qū)",C:C,"轉(zhuǎn)賬")=SUMIFS(求和的區(qū)域,條件區(qū)域一,條件一,條件區(qū)域二,條件二….)華東區(qū)80匯款196=SUMPRODUCT((A:A="華南區(qū)")*(C:C="轉(zhuǎn)賬"),B:B)=SUMPRODUCT(條件一*條件二*……條件N,求和區(qū)域)西南區(qū)51現(xiàn)金華南區(qū)40轉(zhuǎn)賬三、算業(yè)務(wù)區(qū)域?yàn)椤叭A南區(qū)”、結(jié)算方式為“轉(zhuǎn)賬”的業(yè)務(wù)發(fā)生筆數(shù)。華東區(qū)52匯款結(jié)果公式公式寫法華北區(qū)11轉(zhuǎn)賬3=COUNTIFS(A:A,"華南區(qū)",C:C,"轉(zhuǎn)賬")=COUNTIFS(條件區(qū)域一,條件一,條件區(qū)域二,條件二……)西南區(qū)46匯款3=SUMPRODUCT((A:A="華南區(qū)")*(C:C="轉(zhuǎn)賬"))=SUMPRODUCT(條件一*條件二*……條件N)華南區(qū)75現(xiàn)金西南區(qū)90轉(zhuǎn)賬華南區(qū)45匯款華東區(qū)88匯款西南區(qū)55匯款華南區(qū)65轉(zhuǎn)賬華東區(qū)78現(xiàn)金公式與函數(shù)——多條件計數(shù)1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

【例】根據(jù)下圖，統(tǒng)計公司1人事部有多少人=COUNTIFS(A2:A6,"公司1",B2:B6,"人事部")公式與函數(shù)——字符串處理公式1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1、多單元格字符串合并公式：c2=PHONETIC(A2:A7)說明：Phonetic函數(shù)只能對字符型內(nèi)容合并，數(shù)字不可以。2、截取除后3位之外的部分公式：=LEFT(D1,LEN(D1)-3)說明：LEN計算出總長度,LEFT從左邊截總長度-3個3、截取-前的部分公式:B2=Left(A1,FIND("-",A1)-1)

說明：用FIND函數(shù)查找位置，用LEFT截取。

公式與函數(shù)——日期計算公式1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

1.兩個范圍相乘并求和

=SUMPRODUCT（范圍1，范圍2）

=SUMPRODUCT（A1：A50,D1：D50）A1*D1+A2*D2A50:D50

2.多條件求和

=SUMPRODUCT（（范圍1=條件1）*（范圍2=條件2）*求和范圍）最后一個可以不用括號的

3.綜合用法條件相乘

=SUMPRODUCT（（范圍1=條件1）*范圍2，范圍3）范圍1與范圍2可以是同一范圍。

范圍及條件是可多達(dá)30個的。通常也只會用到3個左右。這個函數(shù)與SUMIF有相似之處，但更強(qiáng)大。

Datedif:兩日期相隔的年、月、天數(shù)計算

A1是開始日期（2011-12-1），B1是結(jié)束日期(2013-6-10)。計算：

相隔多少天？=datedif(A1,B1,"d")結(jié)果：557相隔多少月?

=datedif(A1,B1,"m")結(jié)果：18相隔多少年?=datedif(A1,B1,"Y")結(jié)果：1不考慮年相隔多少月？=datedif(A1,B1,"Ym")結(jié)果：6不考慮年相隔多少天？=datedif(A1,B1,"YD")結(jié)