轉(zhuǎn)自：Youtube - PaPaYa
練習(xí)課件：素材
【全套】Excel零基礎(chǔ)入門進(jìn)階到函數(shù)狼电，Excel自學(xué)教程從小白到高手超詳細(xì)實(shí)操教程
Excel??
單元格使用技巧:
顯示今日日期Alt+;
一列中持續(xù)寫入重復(fù)內(nèi)容可Alt+箭頭向下
選中想重復(fù)一列貨幣格式符號(hào)用格式刷
單元格線條:開始→繪制邊框網(wǎng)格→下拉選擇 （清楚線條按shift） Esc退出畫筆
數(shù)值運(yùn)算
=A1*B1（輸入=點(diǎn)擊運(yùn)算的單元格*重復(fù)一遍后按回車）
一列/范圍想加:＝SUM(M1,M2) 或按ctrl選中特定數(shù)字
分割視窗
視圖→選擇要凍結(jié)的部分下一格后→凍結(jié)窗格
對(duì)比月份同理選擇拆分即可
資料排序
開始→排序和篩選（自定義排序方式）
資料篩選
開始→排序和篩選→篩選（取消√選擇要顯示的物品）（文本篩選）
格式化表格
插入→左上角表格→出現(xiàn)設(shè)計(jì)→表格樣式&插入切片器
設(shè)定格式化表格的條件（顏色標(biāo)記）
開始→條件格式（整列全選）
工作表設(shè)定&合并
底部加號(hào)→增加表格
復(fù)制工作表→按住ctrl拖動(dòng)鼠標(biāo)左鍵
移動(dòng)表格→右鍵
多表想加→數(shù)據(jù)→合并計(jì)算
圖表制作
圖表
數(shù)字右鍵可更改
更換背景圖→柱形圖油漆桶→圖片或材料填滿
打印分頁設(shè)定
視圖→分頁預(yù)覽→拖動(dòng)虛線→選擇整行后加分隔符
頁面布局→每頁打印標(biāo)題
特定行列框選→打印區(qū)域
頁首頁尾&表格加水印
視圖→左上角整頁模式
顯示頁碼:設(shè)計(jì)→左上角頁碼&總頁數(shù)（斜杠隔開）
增加logo:設(shè)計(jì)→圖片→設(shè)定圖片格式
浮水印:新增一張空白表→插入→右上角文字→藝術(shù)字→復(fù)制到word/ppt另存圖片→插入圖片（調(diào)整高度可用Enter）
函數(shù)
加總:常用→右上角自動(dòng)加總（=SUM（C3:C6） 其中:即為引數(shù)）
大小:＝LARGE/SMALL（C3:C6,數(shù)字）
邏輯函數(shù)IF
點(diǎn)擊自設(shè)函數(shù)IF→選擇判斷單元格例如81（C7）→Logical_test:C7＞＝60→Value_if_true:及格→
Value_if_false:不及格
多個(gè)判斷同理IFS→Logical_test:C7＞＝90
→Value_if_true:A...Logical_test2:XXX
注意:不可70＞XXX＞80
單元格顯示顏色:開始→條件格式→突出顯示單元格規(guī)則→等于→設(shè)置顏色
VLOOKUP函數(shù)&絕對(duì)參照設(shè)定
選擇該函數(shù)→lookup_value:指定單元格即關(guān)鍵字→table_array:框選范圍單元→clo_index_num:該行某個(gè):2（欄數(shù)）
range_lookup:False? 注意:只可遞增查詢
絕對(duì)參照:在公式上按F4鎖定（N/A）
注意:下拉顯示其余需鎖定關(guān)鍵字和查詢范圍
同時(shí)修改對(duì)應(yīng)欄數(shù)
IFERROR函數(shù)&資料驗(yàn)證
IFERROR（要檢查的公式鲁捏，公式錯(cuò)誤的訊息）
不重復(fù)顯示只留下一個(gè)錯(cuò)誤訊息，其余為引號(hào)即可
空白學(xué)號(hào)對(duì)應(yīng)空白資料:
IF（C3＝“”，“”，IFERROR（VLOOKUP...），“查無此人”）
自定義驗(yàn)證:資料→資料驗(yàn)證
COUNTIFS&SUMIFS 函數(shù)應(yīng)用
＝COUNTA（）
＝COUNTIF（資料范圍，條件）（文字的話加“”）
嗯
＝COUNTIFS（C2:C14,F2,D2:D14,“>1000”）
其中“>1000” 可以寫成“>”&G2?
＝COUNTIFS（C2:C14,G2,D2:D14,H2）
其中H2即為日期A2新增所屬月份欄
＝MONTH（A2）即截取日期（5/2）內(nèi)月份（5）
＝SUMIFS（E2:E14,C2:C14,G2,D2:D14,H2）
定義名稱&INDIRECT函數(shù)
公式→中間定義名稱
＝SUM（金額）
數(shù)據(jù)→資料驗(yàn)證→設(shè)置下拉框
公式→根據(jù)所選內(nèi)容創(chuàng)建
然后資料驗(yàn)證→設(shè)定來源→＝INDIRECT（＄B＄3）
小技巧
將內(nèi)容擠在同一個(gè)列單元格分離出來→數(shù)據(jù)→分列→分隔符號(hào)→勾選空格
姓氏分離→同理固定寬度→將分割線放在姓氏中間
ctrl+shift+空格→全選資料單元格
ctrl+shift→選擇行單元格? ↓選擇列單元格
單元格左上角方塊→全選整頁單元格
ctrl+/ctrl－增加減少一行單元格
ctrl+拖動(dòng)鼠標(biāo)→復(fù)制一行單元格
shift+拖動(dòng)鼠標(biāo)→插入到兩行單元格之間
數(shù)據(jù)→刪除重復(fù)值
轉(zhuǎn)置表格→右鍵復(fù)制→選擇性粘貼→轉(zhuǎn)置
資料同時(shí)加上數(shù)值→例如數(shù)值加5→復(fù)制5→框選區(qū)域單元格選擇性粘貼→運(yùn)算選加
ctrl+～→顯示公式
數(shù)據(jù)→模擬分析→選擇指定單元格→單變量求解
表格對(duì)角線→開始→繪制邊框
（鼠標(biāo)右鍵→設(shè)置單元格格式→邊框）（同一單元格換行→Alt+enter）
注解→右鍵輸入批注（插入圖片注解→先刪除內(nèi)容再右鍵設(shè)置批注格式）
表格數(shù)值格式
百分之1/2 前加0和空格 否則顯示為日期
要顯示001而不是1→‘單引號(hào)加數(shù)字（或開始→文本）
開始→數(shù)字→自定義→加#即為整數(shù)（手機(jī)號(hào)前為0也則輸入000－0000000）
@“等”代表儲(chǔ)存格內(nèi)文字
*重復(fù)指定符號(hào)@*.
＄??? 100而不是＄100可＄*空格0 （其中0代表數(shù)值）
,代表“千”例如＄0,“K” 顯示 33K
0.00正值,（0.00負(fù)值）,0.00零值殴泰，@文字
_代表留白即希望格式對(duì)齊
顏色區(qū)分即前面［藍(lán)色］0.00_）（也可以指定［＞90］為藍(lán)色）
;代表隱藏
時(shí)間格式&工齡與工時(shí)計(jì)算
[DBNum1]1變?yōu)橐? aaaa 星期幾
超過預(yù)設(shè)單元格雙擊左鍵拉開
ctrl+;（固定）/=TODAY()（實(shí)時(shí)更新）顯示當(dāng)前日期
ctrl+shift+;/=NOW()（實(shí)時(shí)更新）顯示當(dāng)前時(shí)間
hh:mm:ss秒
［h］:mm加［］代表累計(jì)已過小時(shí)
=TODAY()-H3(就職日期)
=DATEDIF(開始，結(jié)束浮驳，計(jì)算單位)
=DATEDIF(H3,TODAY(),“y”)
MD兩日之間天數(shù)忽略月年
YM兩日之間月數(shù)忽略日年
YD兩日之間天數(shù)忽略年數(shù)
計(jì)算輪班天數(shù):
=NETWORKDAYS(L3,M3,P3:P4) 其中P3:P4為休假
進(jìn)階:=NETWORKDAYS.INLT(L3,M3,1,P3:P4)
周末參數(shù)取值:
1:星期六日
2:星期日一
3:星期一二
4:星期二三
5:星期三四
6:星期四五
7:星期五六
11:僅星期日
12:僅星期一
13:僅星期二
14:僅星期三
15:僅星期四
16:僅星期五
17:僅星期六
計(jì)算成績(jī)排名:即號(hào)碼不變悍汛，成績(jī)排序
=RANK.EQ(D3(指定),D3:D10(比較范圍)，排序方式)
注意:使用上面公式拖動(dòng)+下拉重復(fù)使用公式至会，需應(yīng)用絕對(duì)參照(全選范圍后按F4)离咐，否則指定和比較范圍同時(shí)下移一位 例如:D4,D4:D10
如果排名按升序則在比較范圍后,加非0的數(shù)，降序0或不填
用LEFT函數(shù)提取表格中的文字資料:
Part.1
=LEFT(資料位置奋献，抓取字?jǐn)?shù))
同理RIGHT一樣
=MID(資料來源健霹，開始位置旺上，抓取字?jǐn)?shù))
=FIND(“要搜尋的文字”瓶蚂，資料來源,搜尋起點(diǎn)（可選）)
=LEN(全選資料統(tǒng)計(jì)字?jǐn)?shù))
Part.2
混合作用抓取內(nèi)容:
抓取羽絨外套:
eg.羽絨外套－女－M
=LEFT(資料位置，抓取字?jǐn)?shù))
???????????????????????????????????????? ↓
抓取字?jǐn)?shù)填FIND(“要搜尋的文字”宣吱，資料來源)
例:=LEFT(B3,FIND(“-”,B3)－1)
抓取性別:
=MID(資料位置窃这，開始位置，抓取字?jǐn)?shù))
???????????????????????????????????????? ↓
開始位置填FIND(“要搜尋的文字”征候，資料來源)
=MID(B3,FIND(“－”,B3)+1,1)
抓取M:
????????????????? FIND(“要搜尋的文字”杭攻，資料來源)
??????????????????????????????????????????????????????????????????? ↓
FIND(“要搜尋的文字”，資料來源疤坝，搜尋起點(diǎn))
RIGHT(B3,LEN(B3)-FIND(“－”,B3,FIND(“－”,B3)+1))
INDEX&MATCH－VLOOKUP:
HLOOKUP(被查詢值兆解，查詢的范圍，要傳回的行數(shù))
取得順位:INDEX(列/行范圍跑揉，第幾位)
進(jìn)階INDEX(資料范圍锅睛，列數(shù)，行數(shù)) 確定數(shù)為框選后末端角的數(shù)
查找順位:MATCH(查找的對(duì)象历谍，查找的范圍(單行或列)现拒，比對(duì)方式(0為完全符號(hào)))
INDEX(范圍，行數(shù))
???????????????????????????? ↑
MATCH(對(duì)象望侈，范圍印蔬，大致符號(hào))
指定兩個(gè)單元格:創(chuàng)建資料驗(yàn)證，下拉選擇菜單
查找對(duì)象即選指定單元格
當(dāng)選擇菜單某個(gè)內(nèi)容INDEX中行或列相應(yīng)跟著變化
INDEX(C3:F10,MATCH(I2,D3:D10,0),MATCH(H3,C2:F2,0))
保護(hù)表格:
右鍵→儲(chǔ)存格格式→保護(hù)→取消勾選鎖定
審閱→保護(hù)工作表/多表保護(hù)/部分允許編輯
加密表格文件:①檔案→咨詢→保護(hù)活頁簿→以密碼加密
②另存脱衙，右下角(保存健旁邊)工具→一般選項(xiàng)
重復(fù)檢查:
例如:空白格上輸入:=A2&B2&C2&D2然后下拉重復(fù)→設(shè)定格式化→醒目提示→重復(fù)的值? 混合 COUNTIF:框選范圍→設(shè)定格式化→新增規(guī)則→使用公式→=COUNTIF(輔助列資料范圍侥猬，指定＄去掉即＄E2)>1→格式→填滿標(biāo)簽→隱藏輔助列
移除重復(fù):數(shù)據(jù)→數(shù)據(jù)工具→移除重復(fù)項(xiàng)
資料重復(fù)驗(yàn)證:數(shù)據(jù)→數(shù)據(jù)驗(yàn)證→設(shè)定→自訂→=COUNTIF(A:A,A1)=1? 同時(shí)可以增加錯(cuò)誤提示
隨機(jī)函數(shù):
=RANDBETWEEN(最小值例驹，最大值)
=INDEX(C3:C14,RANDBETWEEN(1,12))
INDEX(資料范圍，順位)
按F9重新抽取
=CHOOSE(RANDBETWEEN(1,2),“A卷”退唠，“B卷”)
分組:RAND不重復(fù)
排名函數(shù):RANK(自己眠饮，比較的對(duì)象) 注意:F4鎖定
=CHOOSE（ROUNDUP(RANK(D3,＄D＄3:＄D＄14)/6,0),“A組”，“B組”）
組別不變動(dòng)→全選→右鍵選擇性貼上→值
進(jìn)度追蹤表:
視圖→隱藏網(wǎng)格線→開始→下框線→繪制框線格式→線條顏色→ESC
空白區(qū)→自訂功能區(qū)→開發(fā)工具→表單控件（√）→鼠標(biāo)左鍵填滿→右鍵→設(shè)置控件格式→控制→指定單元格顯示→重復(fù)一列→開始→條件格式→圖示集→套用IF(判斷問題(A4)铜邮，yes(1),no(0))→條件格式→選中單元格→管理規(guī)則→編輯規(guī)則→類型選數(shù)字&只顯示圖示→進(jìn)度
今天日期=TODAY()
進(jìn)度:=IF（A4=TRUE,1,IF（＄C＄3>=G3,0,win+;叫出emoji）注意:“符號(hào)”）
布林邏輯:
類比IF: IF（E3>=1000,TRUE,FALSE）
=E3>=1000
=C3=“有”
=AND（D3>=5,E3>=1000）
=OR（C3=“有”,E3>=1000）
=OR（C3=“有”,AND(E3>=1000,D3>=5)
=IF（OR（C3=“有”,AND(E3>=1000,D3>=5)）,“可勝利”仪召，“條件不符”）
復(fù)制OR（C3=“有”,AND(E3>=1000,D3>=5)）→框選范圍單元格→條件格式→新增規(guī)則→使用公式→=OR（＄C3=“有”,AND(＄E3>=1000,＄D3>=5)） →格式→填滿顏色→符合條件小時(shí)色彩
SUMPRODUCT:數(shù)量×單價(jià)
=SUMPRODUCT（C3:C8,D3:D8）
加權(quán)平均:=SUMPRODUCT（C3:C6,D3:D6）/SUM（D3:D6）
=SUMPRODUCT（--（C3:C9=“有”））或=SUMPRODUCT（（C3:C9=“有”）*1）
=SUMPRODUCT（（C3:C9=C11）*（D3:D9=D11）*可選）
內(nèi)測(cè)工具——簡(jiǎn)單公式:? 普通版本下列函數(shù)不可用
插入→表格框選→=SUMIF(表1[列2]，指定#松蒜，表1[列4])? 注意:加#可選中整列扔茅，新增時(shí)不用下拉粘貼
FILTER(資料范圍，篩選條件)
百分比:=C5/SUM(＄C＄5:＄C＄12)
網(wǎng)頁表格抓取:
復(fù)制網(wǎng)頁地址→數(shù)據(jù)→自網(wǎng)站
