Excel學習筆記

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

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

3、單元格 (E1)內設置了比如 D1+C1的公式，只要向下學習好資料歡迎下載拖動就可以自動填充公式。10.調節(jié)錄入的方向 (即回車后移動方向 ),選擇 ”文件 ”高“級 ”的第一個就是。11.自動調節(jié)單元格的尺寸，若是列之間則雙擊最上如 DE間分隔線。另外還可以在行號列號處右擊可設置行高和列寬。12.合并單元格與 拆分單元格是相反的，按鈕一樣。13.在單元格內 自動換行 ，在設置單元格格式內設置。14.上標下標，水分子的輸入，選定2 后在設置單元格格式內定為下標即可。15.關于單元格 斜線與文字輸入，斜線在設置單元格內，斜線上下的文字則是先把文字輸進去再強行換行 （alt+enter）然后用空格

4、調整位置。16.找出符合條件的單元格 ，選定區(qū)域后（那一列），在條件格式 中設置，讓其用顏色區(qū)分。17.查找和替換， ctrl+F。18.行列的增加刪除 ，選定區(qū)域（行列）右擊插入便可。19.行列的隱藏與取消隱藏 ，選定行列然后右擊操作即可。20.工作簿之間的復制可以直接復制粘貼也可以采用ctrl+alt+鼠標放在邊緣線上托到要粘貼的工作簿。21.工作簿的重命名、增加、刪除、添加顏色。22.工作簿在 excel文件之間的移動 。先打開你要移到的 excel文件然后點擊原來的工作簿右鍵移動或復制然后選擇就可以了。注意,學習好資料歡迎下載選中建立副本就是復制。23.凍結窗格在“視圖”里面。24.設

5、置密碼在“審閱”里 。25.求和求平均值 ，除了可以輸入 公式外還可以采用“選定區(qū)域（要多出一格，用于求和的值的輸出） ”點開始面板的“自動求和 ”然后有幾個可選擇。還可使用 函數(shù)求值，如求和采用 sum(c3:h3)26.函數(shù)， sum、average（平均值）、 count（計數(shù)函數(shù)）、max、min 、product（乘積函數(shù)）。27.排序，選定區(qū)域 (要包括關鍵字 ,比如排序語文成績則要包括科目這一欄 )在開始選項下點排序 自定義排序 關鍵字然后根據(jù)需要去選擇。 Rand()函數(shù)產生隨機數(shù)len()函數(shù)測字數(shù)打了“ len（”之后點擊所在列再打“）”注意按行還是按列排序。28.數(shù)據(jù)有效

6、性，就是規(guī)定輸入內容的范圍.性別選擇，在數(shù)據(jù)選項里找到數(shù)據(jù)有效性 設置 序列 男,女。注意這兩個字之間的逗號是英文狀態(tài)下的逗號。29.數(shù)據(jù)篩選 。自動篩選 ：選定區(qū)域（不要包括合并單元格）-排序和篩選 篩選 -（在表格上點擊）三角形標志 數(shù)字篩選（日期篩選、文本篩選） -然后自己添加條件。 高級篩選：在數(shù)據(jù)選項卡里面，點高級篩選 -選定列表區(qū)域與條件區(qū)域（如政治>90 其中條件>90 必須與原表格一致即政治下面是分數(shù)此處的 90 也要在其下面）便可。這是兩者都要滿足，學習好資料歡迎下載滿足其一便可。30.分類匯總：前提是要先排序，根據(jù)公司名稱匯總則要根據(jù)公司名稱排序后再匯總。在數(shù)據(jù)

7、選項卡下，就是可以根據(jù)日期把同一天的比如提貨量求總和。31.數(shù)據(jù)統(tǒng)計 比如排序、分類匯總等選定區(qū)域時不要把表格標題（表格上合并單元格那一欄）選定。你好32.創(chuàng)建數(shù)據(jù)圖表 ：選定區(qū)域 插入選項卡 選擇圖表類型。 當需要在圖表中寫入標題時可采用插入文本框的方法。圖表框可調內圖表也可調大小。可自行改變圖表各種性狀。33.輸入一串很長數(shù)據(jù)如輸身份證時：在英文狀態(tài)下先輸入單引號再輸數(shù)字就自動轉為文本格式而不是科學計數(shù)法。34.輸入日期、時間、分數(shù)：日期用 - 或 / 。時間用： 隔開。輸入當前時間 ctrl+shift+； 分數(shù) ，前加空格。35.Tab 鍵，作用是往右跳，若事先選定了區(qū)域則按tab 鍵

8、跳到右邊時之后又跳到下一行的左邊。36.Alt 鍵加小鍵盤的一些數(shù)字組合可輸入平方等特殊符號。ALT+小鍵盤 153=?ALT+小鍵盤 169=?ALT+小鍵盤 174=?ALT+小鍵盤 177=±學習好資料歡迎下載ALT+小鍵盤 178=2ALT+小鍵盤 179=3ALT+小鍵盤 188=?ALT+小鍵盤 189=?ALT+小鍵盤 128=ALT 163= ALT176= °ALT 137=ALT 165= ￥ALT 181= ALT 223= ?ALT 216= ?ALT 215= ×Alt+41420= 37.自定義單元格格式的輸入： 如每輸入數(shù)字比如20

9、后面就自動添加一個“歲”字。規(guī)定格式 00.00”歲”要小數(shù)點則也要自己00”小時 ”00”分鐘 ” 輸入 2035 時就顯示20 小時35 分鐘38.高級查找輸入 * 代表任何多個字符，？代表任何一個字符。學習好資料歡迎下載常用函數(shù)1. if 函數(shù)：=if(F14>5, ”優(yōu)秀”，”不優(yōu)秀” ) 注意引號必須為英文狀態(tài)下的引號。 If 的嵌套調用 =if(F14>E14, ”中”，if(F14<E14, ”美”，”平” )用函數(shù)時可用 F9 查看每一步計算情況（選定運行F9）。2. 數(shù)組：橫向用逗號，縱向用分號，分號之間的是同一行的元素。一位數(shù)組、二位數(shù)組、三位數(shù)組、三維數(shù)

10、組。3.運算內存數(shù)組計算時要 ctrl+shift+enter。4.數(shù)組運算 時是行與列相加時是先行的每一個元素與列第一個元素分別相加，然后是與列第二個元素分別相加依次類推。若是行與行或列與列則是一一對應進行計算。計算后外面還有如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

11、. 引用：相對引用 A3，絕對引用 $A$3固定引用 A3格的內容。混合引用 A$3或$A3，第一個是固定 3，第二個是固定 A。切換用 F4。7. 引用的應用 - 九九乘法表 ：學習好資料歡迎下載=IF(B$1<=$A2,B$1&"X"&$A2&"="&B$1*$A2,"") 注意連字符 &的使用。8. 邏輯 :or( 或) 、and( 且) 、not( 非) 。其值為兩種 false 和 true 。 =And(9>0,4>3) 其值為 true 。Not(2>0)

12、為 false 。Or(9>0,4<3) 為 true 。9. True 和 false 與 1 和 0 的轉換 ：就是將其進行數(shù)學運算然后就以 0 和 1 的形式表示出來。第一個表示添兩個負號若非0就等于 1，其他的同理。10. 計數(shù)：sum(-( 條件、數(shù)組 ) 滿足條件的都變?yōu)?1 然后用 sum函數(shù)將數(shù)組元素加起來。=SUM(-(A28:A33&B28:B33="良優(yōu)")+SUM(-(A28:A33&B28:B33=" 優(yōu)良 ") 意思是將優(yōu)良或者良優(yōu)的進行計數(shù)。11. 注意 or 函數(shù)后只產生一個元素， true 和

13、 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 不支持數(shù)組。 1*1 仍等于 1，但對于有一個為真則為真，注意在用+代替 or 在進行數(shù)組運算時對應元素都不為0 則+后就變?yōu)?2，要用 if將其還原為 1。如下=SUM(IF(FALSE;FALSE;FALSE;

14、TRUE;FALSE;TRUE)+(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE),1)14. If 函數(shù)中的簡寫 ：if （表達式，成立，不成立） ，若其中的成立或者不成立時的地方不填寫東西但是兩個逗號都有則表示不管表達式如何其值都為 0，即逗號后面不寫東西就表示為 0。若后一個逗號不寫則表示當條件不成立時其值為 false 。False 不參與數(shù)學運算。注意 if 函數(shù)的嵌套調用 if （表達式，值， if （表達式，值）15. Is 類判斷函數(shù) ：16. 文本（text ）與數(shù)字（ number）相乘產生錯誤值 ，為文本此時要將其去掉可使用 iserr （）與 if

15、 函數(shù)的結合使用。學習好資料歡迎下載計算已定的水果的平均值。IF(ISERR(B19:B23*C19:C23),"",B19:B23*C19:C23)17. -A36:E44 表示把 A36:E44 內的所有內容添上負號，若給文本添負號就會出錯。18.Count 與 Sum，前者只對數(shù)字不管是正是負是0 都會記數(shù)但不會對文本計數(shù)。 Sum只是將數(shù)字相加。19. MIN、MAX函數(shù) : 參數(shù)可以是數(shù)字或文本 ,min(number1 、number2、 ) 但是文本不能轉換為數(shù)字或參數(shù)是錯誤時會出現(xiàn)錯誤。如果邏輯值與文本不能忽略計算則用mina、maxa。20. 上限下限 ：

16、設參數(shù)為 a, 則設 a 的上限為 100，則為 min(100,a).意思就是取的值不會超過100. 同理 max(50,a) 表示下限，就是取的值不小于 50.學習好資料歡迎下載21. 求滿足幾個條件的情況的值 ：要求求滿足 2011 年 6 月的最大最小值，采用連字符 &第一列與第二列連看是否是 20116 然后會得出一個 false 與 true ，true 與后面的一列值相乘就是后面的值。多條件（且）的情況本質上就是連字符 &與乘 * 、summaxmin 還有引用 $A50:$A180 的綜合應用或者把減法也使用上。22. SUMPRODUCT（）：將兩數(shù)組對應元素

17、相乘再相加。要求維數(shù)要相同否則出錯，對非數(shù)值型元素作 0 處理。此處不像 * ，* 是對 true 作 1，對 false 作 0 處理。23. Sumif（）：SUMIF（條件判斷的區(qū)域，條件，滿足條件的要相加的值區(qū)域）。學習好資料歡迎下載（區(qū)域與區(qū)域）運算原則 : 第一個區(qū)域與第二個區(qū)域 (sum_range) 是對應關系即第一個區(qū)域的第一個元素對應第二個區(qū)域的第一個元素 , 一一對應下去 . 表示第一個區(qū)域中第 N個元素滿足條件時就取第二個區(qū)域的第 N個元素，若有多個滿足時就全部相加。 注意：如果條件中（ criteria ）為數(shù)組則運算是先判斷第一個區(qū)域中滿足條件中第一個元素的有哪些對

18、應取第二個區(qū)域的元素相加然后再求滿足條件中第二個元素的值依此類推，條件為數(shù)組則 sumif 計算出來的也是一個數(shù)組。第三個參數(shù)其區(qū)域與第一個參數(shù)（必須為區(qū)域，不可使用連字符判斷，即如果要滿足幾個條件就是“且”則用此函數(shù)沒法完成）為同一區(qū)域時可以不寫且其前面的逗號也不要寫。第二個條件判斷要用”條件”表示，雙引號不能少，如”>100”。如果條件中要求滿足兩種或幾種（或）的可能都可以的話可以采用數(shù)組的形式，意思就是“或”。例如求花生與梨子的總量sum(SUMIF(A43:A51," 花生 "," 梨子 ",B43:B51)。注意：此處SUMIF求出的是一

19、個數(shù)組即花生和梨子的數(shù)量 花生數(shù)量，梨子數(shù)量 所以后面要用 sum求和。24. 通配符：* 與？ * 表示任意多個字，？表示任意一個字。SUMIF(A16:A23," 張*",B16:B23)25. 關鍵點：關于引用拖動的問題，注意其中學習好資料歡迎下載開始定義張 * 右邊的單元格 =SUMIF(A16:A23,D17,B16:B23)之后往下拖動求其他單元格，由于此處引用中字母未變但是對應的數(shù)字因為向下拖動而會改變。所以要=SUMIF(A$16:A$23,D17,B$16:B$23)固定數(shù)字不讓其向下走（此處拖到 * 三* 處選定的區(qū)域就是從劉萬龍開始的然后直到張九如下面

20、一格）。注意其中的變與不變問題。26.數(shù)組之間運算問題：要求連桿瓦的銷量采用 sumif （）第一個參數(shù)選擇紅色框選區(qū)域，第三個參數(shù)選擇黃色框選區(qū)域， 若求金額則要從金額處開始框選， 這樣才能對應上要求的量。首先是 1 處連桿瓦對應銷量，當?shù)?10 處后移學習好資料歡迎下載到 1 處的銷量但不是連桿瓦就跳過一直到五月的連桿瓦再繼續(xù)對應求值。對于乘法也是一樣總是第一個框選的第一個元素對應于第二個框選的第一個元素如此下去。27. 計數(shù)類函數(shù) ：count （求數(shù)字個數(shù)）、 counta （求非空個數(shù)，空格也是存在的會計數(shù)）、 countblank （求空值個數(shù)）后兩個是相反的。27.count （

21、）會將 0 也計數(shù)，一定要注意這點。具體使用中可應用1去除得到的那個邏輯值，如果邏輯值是true 則值為 1 否則出錯，而出錯的 count 函數(shù)是不會計算在內的。28. COUNTIF（區(qū)域，條件：可以是數(shù)字、表達式、文本） ：條件一定要用雙引號。計數(shù)時要滿足比如： 大于 1500 小于 2000 的可采用減法用計算出的大于 1500 的減去大于 2000 的。算出區(qū)域中不同種類的個數(shù)，即職務的個數(shù)： =countif(A24:A32,A24:A32)（注意要是其中有空單元格則會出現(xiàn) 0，即空單元格不管出現(xiàn)幾次都是用 0 表示看后面的綠色） 就是區(qū)域與條件都選中同一區(qū)域， 意思就是開始看后面

22、的條件指向文員再到區(qū)域中看有幾個文員發(fā)現(xiàn)有 2 個然后出現(xiàn)一個二，再到主管是一個再到業(yè)務員是 5 個，再到文員是 2 個，總的就是形成一個數(shù)組：學習好資料歡迎下載2 1 5 2 5 5 1 5 5（如果有空單元格則會出現(xiàn)像：2 0304 2 3 0 4 4 3這樣的情況就不可像后面這么處理）就是數(shù)字為幾就出現(xiàn)幾次。如果用 1 作為被除數(shù)則除了以后再相加得到的就是職務的個數(shù)，因為對于2 就變成 1/2 有兩個就是 1對于 5 也是一樣。處理有空單元格的情況：=SUM(IF(ISNUMBER(1/COUNTIF(A37:A45,A37:A45),1/COUNTIF(A37:A45,A37:A45)

23、 先把為0 的去掉然后求和。或者先判斷是否為空把空用空代替=SUM(IF(ISBLANK(A37:A45),"",1/COUNTIF(A37:A45,A37:A45)29. countif(區(qū)域 , 條件 ) 前面是一個區(qū)域后面也可以是區(qū)域, 如果都是區(qū)域如與結果就是一個數(shù)組3;4;3;5.注意存放結果的不再是一個單元格而是如后面區(qū)域一樣四個單元格即輸入函數(shù)的時候是選擇四個單元格再輸入函數(shù). 完全重復的問題學習好資料歡迎下載可以使用此法先比較第一個然后第二個然后第三個等形成數(shù)組( 第一行與所有區(qū)域比較 , 比較出一個數(shù)組2,4,3,1然后第二行第三行等 ).如果全部都大于1

24、 則有重復否則沒有 .30. 關于中式排名:利用種類的個數(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. 關于表格中等級采用數(shù)字或者分數(shù) 代替的問題：（還可采用查找的方法，具體用什么方法看情況）學習好資料歡迎下載計算評分結果是根據(jù)等級找到分數(shù)乘以對應的所占比例。要用分數(shù)代替等級。=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

25、;6,10,10;6,6,8*TRUE;FALSE;FALSE;FALSE;FALSE*0.5,0.3,0.2)注意：每一個分號之前對應相乘。即true 與 10,4,10 相乘，后面的都為 0，然后再與后面所占比例逗號對應相乘。簡而言之就是分號對分號逗號對逗號。學習好資料歡迎下載32. vlookup（）：第一個參數(shù)表示要查找的對象此處為編號，第二個為區(qū)域此處為表格區(qū)域第 8 行開始，第三個參數(shù)為要查找的對象的相關的東西所在列此處為第三列性別，第四列為查找方式為0（也可不寫 0 代替）表示精確查找，另外 1 表示近似查找。注意表格中兩種情況省些最后一個參數(shù)的情況。注意：要查找的東西（前面圖中

26、表編號）必須是選定區(qū)域的第一列否則會出錯。（查找NED005，選定的區(qū)域也必須是從編號開始的。如果查找陳青則選區(qū)域時從名字開始就行。）33. 關于表格中因一個變量引發(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ù)需要查詢的列定的。如年齡就是 5。其他一樣推。抓住變量，變量在因變量中體現(xiàn)就影響因變量的值的變化。34. 以工種或類別為區(qū)別依據(jù)表格填入數(shù)據(jù) ( 數(shù)據(jù)只與

27、工種或類別有關)。采用精確查找的方法。如 fx 內的函數(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)-VLOOKUP(J44-3500),G$44:I$50,3),0)

28、37. 列的對換：在查找中要查找的內容必須為第一列顯示的為其之后的列。若不是可使用 if 對換。=IF(1,0,E10:E18,D10:D18)學習好資料歡迎下載日期時間函數(shù)1. 每個日期都對應一個數(shù)字， 但數(shù)字并不都對應一個時間。 1 代表日期中的一天。數(shù)字與日期的轉換是 ctrl+shift+ 與 ctrl+shift+#基本知識：前面三個是從總日期中提取各個值，第四個是將各分的合并。 Date( 年，月，日 ) 里面參數(shù)可用具體數(shù)字代替 ( 直接用數(shù)字就行不用引號 ) 。 eomonth( 指定的日期，第一個參數(shù)可以直接用年月日輸入但要用引號，如果有引用有輸入則：eomonth(A19&

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

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