Excel學(xué)習(xí)筆記

1、1. 固定錄入,選定單元格之后按住ctrl在鼠標左鍵點擊單元格。2. 關(guān)于引用拖動的簡化，可以不須用鼠標放在右下角后拖動，只需要將鼠標放在右下角呈可拖動狀態(tài)時雙擊鼠標即可。3. 多個單元格錄入同內(nèi)容，先用ctrl選定要錄入的單元格在最后點擊的一個單元格內(nèi)輸入內(nèi)容后按ctrl+回車。4. 重復(fù)上一次設(shè)置（操作），選定區(qū)域后按F4。5. 重復(fù)上一單元格（只是內(nèi)容），在英文狀態(tài)下是ctrl+（單引號，雙引號那個鍵）。6. 插入當(dāng)下日期，ctrl+；。7. 分數(shù)的錄入，一種方法是修改單元格格式，另一種是用帶分數(shù)輸入如輸1/2,就輸0 (之間有空格)1/2。8. 粘貼（內(nèi)容、格式、一次快速粘貼），復(fù)制后

2、在開始菜單下調(diào)出剪貼板（alt+E+B），然后選擇區(qū)域再點擊剪貼板內(nèi)的內(nèi)容就可以了。粘貼可以是值可以是格式或者都有。另外，復(fù)制（后選定區(qū)域）+enter也可（只能復(fù)制一次）。9. 智能填充（內(nèi)容、數(shù)據(jù)、格式、公式），在輸入1回車輸入2后鼠標點擊右下角標志拖拉就可以自動填充（內(nèi)容、數(shù)據(jù)）。也可以ctrl+拖拉，一般自動加1，也可采用序列規(guī)定步長值和最終值不用去拖拉。其他的比如星期也一樣。也可用拖拉之后點右下角的標志選擇填充內(nèi)容還是格式（注意邊框的去除可用此法）等。還有公式的填充，在一個單元格(E1)內(nèi)設(shè)置了比如D1+C1的公式，只要向下拖動就可以自動填充公式。10. 調(diào)節(jié)錄入的方向(即回車后移動

3、方向),選擇”文件”“高級”的第一個就是。11. 自動調(diào)節(jié)單元格的尺寸，若是列之間則雙擊最上如DE間分隔線。另外還可以在行號列號處右擊可設(shè)置行高和列寬。12. 合并單元格與拆分單元格是相反的，按鈕一樣。13. 在單元格內(nèi)自動換行，在設(shè)置單元格格式內(nèi)設(shè)置。14. 上標下標，水分子的輸入，選定2后在設(shè)置單元格格式內(nèi)定為下標即可。15. 關(guān)于單元格斜線與文字輸入，斜線在設(shè)置單元格內(nèi)，斜線上下的文字則是先把文字輸進去再強行換行（alt+enter）然后用空格調(diào)整位置。16. 找出符合條件的單元格，選定區(qū)域后（那一列），在條件格式中設(shè)置，讓其用顏色區(qū)分。17. 查找和替換，ctrl+F。18. 行列的增

4、加刪除，選定區(qū)域（行列）右擊插入便可。19. 行列的隱藏與取消隱藏，選定行列然后右擊操作即可。20. 工作簿之間的復(fù)制可以直接復(fù)制粘貼也可以采用ctrl+alt+鼠標放在邊緣線上托到要粘貼的工作簿。21. 工作簿的重命名、增加、刪除、添加顏色。22. 工作簿在excel文件之間的移動。先打開你要移到的excel文件然后點擊原來的工作簿右鍵移動或復(fù)制然后選擇就可以了。注意,選中建立副本就是復(fù)制。23. 凍結(jié)窗格在“視圖”里面。24. 設(shè)置密碼在“審閱”里。25. 求和求平均值，除了可以輸入公式外還可以采用“選定區(qū)域（要多出一格，用于求和的值的輸出）”點開始面板的“自動求和”然后有幾個可選擇。還可

5、使用函數(shù)求值，如求和采用sum(c3:h3)26. 函數(shù)，sum、average（平均值）、count（計數(shù)函數(shù)）、max、min、product（乘積函數(shù)）。27. 排序，選定區(qū)域(要包括關(guān)鍵字,比如排序語文成績則要包括科目這一欄)在開始選項下點排序自定義排序關(guān)鍵字然后根據(jù)需要去選擇。Rand()函數(shù)產(chǎn)生隨機數(shù)len()函數(shù)測字數(shù)打了“l(fā)en（”之后點擊所在列再打“）”注意按行還是按列排序。28. 數(shù)據(jù)有效性，就是規(guī)定輸入內(nèi)容的范圍.性別選擇，在數(shù)據(jù)選項里找到數(shù)據(jù)有效性設(shè)置序列男,女。注意這兩個字之間的逗號是英文狀態(tài)下的逗號。29. 數(shù)據(jù)篩選。自動篩選：選定區(qū)域（不要包括合并單元格）-排序和

6、篩選篩選-（在表格上點擊）三角形標志數(shù)字篩選（日期篩選、文本篩選）-然后自己添加條件。高級篩選：在數(shù)據(jù)選項卡里面，點高級篩選-選定列表區(qū)域與條件區(qū)域（如政治 >90其中條件>90必須與原表格一致即政治下面是分數(shù)此處的90也要在其下面）便可。這是兩者都要滿足，滿足其一便可。30. 分類匯總：前提是要先排序，根據(jù)公司名稱匯總則要根據(jù)公司名稱排序后再匯總。在數(shù)據(jù)選項卡下，就是可以根據(jù)日期把同一天的比如提貨量求總和。31. 數(shù)據(jù)統(tǒng)計比如排序、分類匯總等選定區(qū)域時不要把表格標題（表格上合并單元格那一欄）選定。你好32. 創(chuàng)建數(shù)據(jù)圖表：選定區(qū)域插入選項卡選擇圖表類型。當(dāng)需要在圖表中寫入標題時可

7、采用插入文本框的方法。圖表框可調(diào)內(nèi)圖表也可調(diào)大小?？勺孕懈淖儓D表各種性狀。33. 輸入一串很長數(shù)據(jù)如輸身份證時：在英文狀態(tài)下先輸入單引號再輸數(shù)字就自動轉(zhuǎn)為文本格式而不是科學(xué)計數(shù)法。34. 輸入日期、時間、分數(shù)：日期用 - 或 / 。時間用：隔開。輸入當(dāng)前時間ctrl+shift+；分數(shù)，前加空格。35. Tab鍵，作用是往右跳，若事先選定了區(qū)域則按tab鍵跳到右邊時之后又跳到下一行的左邊。36. Alt鍵加小鍵盤的一些數(shù)字組合可輸入平方等特殊符號。ALT+小鍵盤153=ALT+小鍵盤169=©ALT+小鍵盤174=®ALT+小鍵盤177=±ALT+小鍵盤178=&

8、#178;ALT+小鍵盤179=³ALT+小鍵盤188=¼ALT+小鍵盤189=½ALT+小鍵盤128=ALT 163= £ALT 176= °     ALT 137=ALT 165= ¥ALT 181= µALT 223= ßALT 216= ØALT 215= ×Alt+41420=37. 自定義單元格格式的輸入：如每輸入數(shù)字比如20后面就自動添加一個“歲”字。要小數(shù)點則也要自己規(guī)定格式”歲”00”小時”00”分鐘”輸入2035時就顯示20

9、小時35分鐘38. 高級查找輸入*代表任何多個字符，？代表任何一個字符。常用函數(shù)1. if函數(shù)：=if(F14>5,”優(yōu)秀”，”不優(yōu)秀”) 注意引號必須為英文狀態(tài)下的引號。If的嵌套調(diào)用=if(F14>E14,”中”，if(F14<E14,”美”，”平”) 用函數(shù)時可用F9查看每一步計算情況（選定運行F9）。2. 數(shù)組：橫向用逗號，縱向用分號，分號之間的是同一行的元素。一位數(shù)組、二位數(shù)組、三位數(shù)組、三維數(shù)組。3. 運算內(nèi)存數(shù)組計算時要ctrl+shift+enter。4. 數(shù)組運算時是行與列相加時是先 行的每一個元素與列第一個元素分別相加，然后是與列第二個元素分別相加依次類推

10、。若是行與行或列與列則是一一對應(yīng)進行計算。計算后外面還有如sum函數(shù)則是把每一個元素相加。5. 計數(shù)：sum（if（B4：B9>80，1） 大于80的有多少個。Count（if（B4：B9&C4:C9=”優(yōu)優(yōu)”，）逗號一定要有。Sum（if（B30：B35>2000,B30:B35,0）意思是把B30到B35中大于2000的元素相加，其中在運算中是把不大于2000的元素置零。6. 引用：相對引用A3，絕對引用$A$3固定引用A3格的內(nèi)容?；旌弦肁$3或$A3，第一個是固定3，第二個是固定A。切換用F4。7. 引用的應(yīng)用-九九乘法表：=IF(B$1<=$A2,B$1&

11、amp;"X"&$A2&"="&B$1*$A2,"")注意連字符&的使用。8. 邏輯:or(或)、and(且)、not(非)。其值為兩種false和true。=And(9>0,4>3)其值為true。Not(2>0)為false。Or(9>0,4<3)為true。9. True和false與1和0的轉(zhuǎn)換：就是將其進行數(shù)學(xué)運算然后就以0和1的形式表示出來。第一個表示添兩個負號若非0就等于1，其他的同理。10. 計數(shù)：sum(-(條件、數(shù)組)滿足條件的都變?yōu)?然后用sum函數(shù)將

12、數(shù)組元素加起來。=SUM(-(A28:A33&B28:B33="良優(yōu)")+SUM(-(A28:A33&B28:B33="優(yōu)良")意思是將優(yōu)良或者良優(yōu)的進行計數(shù)。11. 注意or函數(shù)后只產(chǎn)生一個元素，true和false或者0和1。12. 必要時用“+”號代替。如10的計數(shù)=SUM(-(A28:A33&B28:B33="優(yōu)良")+(-(A28:A33&B28:B33="良優(yōu)")13. And可以用*來代替，or可以用+來代替。作用作用主要體現(xiàn)在數(shù)組上，多個元素的情況，and與or不支持

13、數(shù)組。1*1仍等于1，但對于有一個為真則為真，注意在用+代替or在進行數(shù)組運算時對應(yīng)元素都不為0則+后就變?yōu)?，要用if將其還原為1。如下=SUM(IF(FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)+(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE),1)14. If函數(shù)中的簡寫：if（表達式，成立，不成立），若其中的成立或者不成立時的地方不填寫東西但是兩個逗號都有則表示不管表達式如何其值都為0，即逗號后面不寫東西就表示為0。若后一個逗號不寫則表示當(dāng)條件不成立時其值為false。False不參與數(shù)學(xué)運算。注意if函數(shù)的嵌套調(diào)用if（表達式，值，if（

14、表達式，值）15. Is類判斷函數(shù)：16. 文本（text）與數(shù)字（number）相乘產(chǎn)生錯誤值，為文本此時要將其去掉可使用iserr（）與if函數(shù)的結(jié)合使用。計算已定的水果的平均值。IF(ISERR(B19:B23*C19:C23),"",B19:B23*C19:C23)17. -A36:E44表示把A36:E44內(nèi)的所有內(nèi)容添上負號，若給文本添負號就會出錯。18. Count與Sum，前者只對數(shù)字不管是正是負是0都會記數(shù)但不會對文本計數(shù)。Sum只是將數(shù)字相加。19. MIN、MAX函數(shù):參數(shù)可以是數(shù)字或文本,min(number1、number2、)但是文本不能轉(zhuǎn)換為數(shù)

15、字或參數(shù)是錯誤時會出現(xiàn)錯誤。如果邏輯值與文本不能忽略計算則用mina、maxa。20. 上限下限：設(shè)參數(shù)為a,則設(shè)a的上限為100，則為min(100,a).意思就是取的值不會超過100.同理max(50,a)表示下限，就是取的值不小于50.21. 求滿足幾個條件的情況的值：要求求滿足2011年6月的最大最小值，采用連字符&第一列與第二列連看是否是20116 然后會得出一個false與true ，true與后面的一列值相乘就是后面的值。多條件（且）的情況本質(zhì)上就是連字符&與乘*、summaxmin還有引用$A50:$A180的綜合應(yīng)用或者把減法也使用上。22. SUMPRODU

16、CT（）：將兩數(shù)組對應(yīng)元素相乘再相加。要求維數(shù)要相同否則出錯，對非數(shù)值型元素作0處理。此處不像*，*是對true作1，對false作0處理。23. Sumif（）：SUMIF（條件判斷的區(qū)域，條件，滿足條件的要相加的值區(qū)域）。（區(qū)域與區(qū)域）運算原則:第一個區(qū)域與第二個區(qū)域(sum_range個元素滿足條件時就取第二個區(qū)域的第N個元素，若有多個滿足時就全部相加。注意：如果條件中（criteria）為數(shù)組則運算是先判斷第一個區(qū)域中滿足條件中第一個元素的有哪些對應(yīng)取第二個區(qū)域的元素相加然后再求滿足條件中第二個元素的值依此類推，條件為數(shù)組則sumif計算出來的也是一個數(shù)組。第三個參數(shù)其區(qū)域與第一個參數(shù)

17、（必須為區(qū)域，不可使用連字符判斷，即如果要滿足幾個條件就是“且”則用此函數(shù)沒法完成）為同一區(qū)域時可以不寫且其前面的逗號也不要寫。第二個條件判斷要用”條件”表示，雙引號不能少，如”>100”。如果條件中要求滿足兩種或幾種（或）的可能都可以的話可以采用數(shù)組的形式，意思就是“或”。例如求花生與梨子的總量sum(SUMIF(A43:A51,"花生","梨子",B43:B51)。注意：此處SUMIF求出的是一個數(shù)組即花生和梨子的數(shù)量花生數(shù)量，梨子數(shù)量所以后面要用sum求和。24.通配符：*與？ *表示任意多個字，？表示任意一個字。SUMIF(A16:A23,

18、"張*",B16:B23)25.關(guān)鍵點：關(guān)于引用拖動的問題，注意其中開始定義張*右邊的單元格=SUMIF(A16:A23,D17,B16:B23)之后往下拖動求其他單元格，由于此處引用中字母未變但是對應(yīng)的數(shù)字因為向下拖動而會改變。所以要=SUMIF(A$16:A$23,D17,B$16:B$23)固定數(shù)字不讓其向下走（此處拖到*三*處選定的區(qū)域就是從劉萬龍開始的然后直到張九如下面一格）。注意其中的變與不變問題。26.數(shù)組之間運算問題：要求連桿瓦的銷量采用sumif（）第一個參數(shù)選擇紅色框選區(qū)域，第三個參數(shù)選擇黃色框選區(qū)域，若求金額則要從金額處開始框選，這樣才能對應(yīng)上要求的量

19、。首先是1處連桿瓦對應(yīng)銷量，當(dāng)?shù)?0處后移到1處的銷量但不是連桿瓦就跳過一直到五月的連桿瓦再繼續(xù)對應(yīng)求值。對于乘法也是一樣總是第一個框選的第一個元素對應(yīng)于第二個框選的第一個元素如此下去。27.計數(shù)類函數(shù)：count（求數(shù)字個數(shù)）、counta（求非空個數(shù)，空格也是存在的會計數(shù)）、countblank（求空值個數(shù)）后兩個是相反的。27.count（）會將0也計數(shù)，一定要注意這點。具體使用中可應(yīng)用1去除得到的那個邏輯值，如果邏輯值是true則值為1否則出錯，而出錯的count函數(shù)是不會計算在內(nèi)的。28.COUNTIF（區(qū)域，條件：可以是數(shù)字、表達式、文本）：條件一定要用雙引號。計數(shù)時要滿足比如：大

20、于1500小于2000的可采用減法用計算出的大于1500的減去大于2000的。算出區(qū)域中不同種類的個數(shù)，即職務(wù)的個數(shù)：=countif(A24:A32,A24:A32)（注意要是其中有空單元格則會出現(xiàn)0，即空單元格不管出現(xiàn)幾次都是用0表示看后面的綠色）就是區(qū)域與條件都選中同一區(qū)域，意思就是開始看后面的條件指向文員再到區(qū)域中看有幾個文員發(fā)現(xiàn)有2個然后出現(xiàn)一個二，再到主管是一個再到業(yè)務(wù)員是5個，再到文員是2個，總的就是形成一個數(shù)組：2 1 5 2 5 5 1 5 5（如果有空單元格則會出現(xiàn)像：2 0 3 0 4 2 3 0 4 4 3 這樣的情況就不可像后面這么處理）就是數(shù)字為幾就出現(xiàn)幾次。如果用

21、1作為被除數(shù)則除了以后再相加得到的就是職務(wù)的個數(shù)，因為對于2就變成1/2 有兩個就是1 對于5也是一樣。處理有空單元格的情況：=SUM(IF(ISNUMBER(1/COUNTIF(A37:A45,A37:A45),1/COUNTIF(A37:A45,A37:A45)先把為0 的去掉然后求和?；蛘呦扰袛嗍欠駷榭瞻芽沼每沾?SUM(IF(ISBLANK(A37:A45),"",1/COUNTIF(A37:A45,A37:A45)29.countif(區(qū)域,條件)前面是一個區(qū)域后面也可以是區(qū)域,如果都是區(qū)域如與結(jié)果就是一個數(shù)組3;4;3;5.注意存放結(jié)果的不再是一個單元格而是如

22、后面區(qū)域一樣四個單元格即輸入函數(shù)的時候是選擇四個單元格再輸入函數(shù).完全重復(fù)的問題可以使用此法先比較第一個然后第二個然后第三個等形成數(shù)組(第一行與所有區(qū)域比較,比較出一個數(shù)組2,4,3,1然后第二行第三行等).如果全部都大于1則有重復(fù)否則沒有.30.關(guān)于中式排名:利用種類的個數(shù)的方法.=SUM(B$6:B$14>=B6)*1/COUNTIF(B$6:B$14,B$6:B$14)美式排名:=SUM(B$6:B$14>B6)*1)+131.關(guān)于表格中等級采用數(shù)字或者分數(shù)代替的問題：（還可采用查找的方法，具體用什么方法看情況）計算評分結(jié)果是根據(jù)等級找到分數(shù)乘以對應(yīng)的所占比例。要用分數(shù)代替等

23、級。=SUM(SUMIF(A$27:A$30,B$20:D$24,B$27:B$30)*(A$20:A$24=A20)*B$19:D$19)=SUM(10,4,10;10,8,8;10,8,8;6,10,10;6,6,8*TRUE;FALSE;FALSE;FALSE;FALSE*0.5,0.3,0.2)注意：每一個分號之前對應(yīng)相乘。即true與10,4,10相乘，后面的都為0，然后再與后面所占比例逗號對應(yīng)相乘。簡而言之就是分號對分號逗號對逗號。32.vlookup（）：第一個參數(shù)表示要查找的對象此處為編號，第二個為區(qū)域此處為表格區(qū)域第8行開始，第三個參數(shù)為要查找的對象的相關(guān)的東西所在列此處為第

24、三列性別，第四列為查找方式為0（也可不寫0代替）表示精確查找，另外1表示近似查找。注意表格中兩種情況省些最后一個參數(shù)的情況。注意：要查找的東西（前面圖中表編號）必須是選定區(qū)域的第一列否則會出錯。（查找NED005，選定的區(qū)域也必須是從編號開始的。如果查找陳青則選區(qū)域時從名字開始就行。）33.關(guān)于表格中因一個變量引發(fā)其他發(fā)生變化的情況：如下圖圖中變量是p處41行，對于姓名處O列=IF(SUM(G$42:G$50=$P$41)*1)>0,VLOOKUP($P$41,$G$42:$M$50,2,0),"")紅色$P$41是不變化的，然后2處是根據(jù)需要查詢的列定的。如年齡就是

25、5。其他一樣推。抓住變量，變量在因變量中體現(xiàn)就影響因變量的值的變化。34.以工種或類別為區(qū)別依據(jù)表格填入數(shù)據(jù)(數(shù)據(jù)只與工種或類別有關(guān))。采用精確查找的方法。如fx內(nèi)的函數(shù)。35.vlookup的模糊用法用于找相近的東西（注意：是我們要查的東西比在查找的區(qū)域中的要大。比如，查20，然后找到有19和21則取19,。在下表中查4600則取4500一欄，如果是4400則取1500一欄）。模糊查找一定要用升序（在哪查找哪就是升序）36.采用查找vlookup自制稅后工資計算器：=J44-IF(J44>3500,(J44-3500)*VLOOKUP(J44-3500),G$44:I$50,2)-VL

26、OOKUP(J44-3500),G$44:I$50,3),0)37.列的對換：在查找中要查找的內(nèi)容必須為第一列顯示的為其之后的列。若不是可使用if對換。=IF(1,0,E10:E18,D10:D18)日期時間函數(shù)1. 每個日期都對應(yīng)一個數(shù)字，但數(shù)字并不都對應(yīng)一個時間。1代表日期中的一天。數(shù)字與日期的轉(zhuǎn)換是ctrl+shift+與ctrl+shift+#基本知識：前面三個是從總?cè)掌谥刑崛「鱾€值，第四個是將各分的合并。Date(年，月，日)里面參數(shù)可用具體數(shù)字代替(直接用數(shù)字就行不用引號)。eomonth(指定的日期，第一個參數(shù)可以直接用年月日輸入但要用引號，如果有引用有輸入則：eomonth(A

27、19&”-2-1”)注意連字符與引號。Eomonth(“2012-2-1”,0)向前向后推月正數(shù)表向后負數(shù)表向前)可在設(shè)置單元格格式內(nèi)指定其格式為只顯示最后天數(shù)（月最后那天）或者再使用day()函數(shù)然后格式設(shè)置為常規(guī)便可（day取出的是“日”但可能會以日期顯示即1代表1900年一月一號，此時可用ctrl+shift+轉(zhuǎn)換即可。）。2. 日期相減得到的是日（天）數(shù)但有時會自動轉(zhuǎn)為數(shù)字對應(yīng)日期的形式一定要特別注意。1代表1900年一月一日然后會顯示1900-1-1或者只顯示1。1940/6/19代表14781但有時會顯示19.3. 注意：day()取出年月日中的“日”，此數(shù)常“數(shù)字-日期”

28、轉(zhuǎn)換，用ctrl+shift+與ctrl+shift+#切換。Eomonth( , )取出所求月份最后一天的日期“年-月-日”可用設(shè)置單元格格式或用day()函數(shù)轉(zhuǎn)換。4. 求出所在日期所處季度：將月份提取乘10再將其轉(zhuǎn)換為日期提取月份就是季度。5. 判斷平年閏年只需判斷2月的天數(shù)即可。Datevalue(A13) 其中A13（字符串形式日期）內(nèi)容是'2008-9-8注意數(shù)字前面的單引號表示這是字符串，使用此函數(shù)可將其轉(zhuǎn)化為真正意義上的日期（序列數(shù)字形式）2008-9-8。Edate（ 參數(shù)1 ， 參數(shù)2）edate()內(nèi)的參數(shù)與eomonth內(nèi)的參數(shù)一樣，第一個參數(shù)是日期第二個是向前或者向后推幾個月。0表示就是本月。Weekday（參數(shù)1，參數(shù)2）第一個參數(shù)為日期，第二個參數(shù)為類型根據(jù)所在國家選取，美國取1，中國取2。6. =DATEDIF("2001-1-3",B16,"y")算時間差（工齡）7. 時間函數(shù)=TIME(B15,C15,D15)如果要將一個時間比如11:30:00加上69分鐘可以=("0"&":69")+A26。