Before we start:
在座的各位小伙伴有沒有為Excel各類函數(shù)頭疼過呢？
有沒有想學(xué)習(xí)制作Excel表格，迅速提升自己能力，結(jié)果發(fā)現(xiàn)不知從何下手的無力感呢？
小先生發(fā)現(xiàn)，現(xiàn)在很多公眾號都打著免費送課程的幌子，讓圈主們轉(zhuǎn)載一波又一波的推送，結(jié)果并沒有什么用。
于是今天小先生為大家整理了一波Excel的基礎(chǔ)函數(shù)，絕對干貨滿滿，趕緊收藏起來吧！
需要用時，立馬就能派上用場！
一個工作簿中有許多工作表，
如何快速整理出一個目錄工作表
1）用宏3.0取出各工作表的名稱，方法：
Ctrl+F3 出現(xiàn)自定義名稱對話框，取名為X，在“引用位置”框中輸入：
=MID(GET.WORKBOOK(1),FIND(']',GET.WORKBOOK(1))+1,100)
確定
2）用HYPERLINK函數(shù)批量插入連接，方法在目錄工作表（一般為第一個sheet）的A2單元格輸入公式：
=HYPERLINK('#''&INDEX(X,ROW())&''!A1',INDEX(X,ROW()))
將公式向下填充，直到出錯為止，目錄就生成了
如何判斷單元格里是否包含指定文本
假定對A1單元格進行判斷有無“指定文本”，以下任一公式均可：
=IF(COUNTIF(A1,'*'&'指定文本'&'*')=1,'有','無')
=IF(ISERROR(FIND('指定文本',A1,1)),'無','有')
求某一區(qū)域內(nèi)不重復(fù)的數(shù)據(jù)個數(shù)
例如求A1：A100范圍內(nèi)不重復(fù)數(shù)據(jù)的個數(shù)，某個數(shù)重復(fù)多次出現(xiàn)只算一個。有兩種計算方法：
一是利用數(shù)組公式：
=SUM(1/COUNTIF(A1:A100,A1:A100))
輸入完公式后按Ctrl+Shift+Enter鍵，讓它自動加上數(shù)組公式符號'{ }'
二是利用乘積求函數(shù)：
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
巧用函數(shù)組合進行多條件的計數(shù)統(tǒng)計
例：第一行為表頭，A列是“姓名”，B列是“班級”，C列是“語文成績”，D列是“錄取結(jié)果”，現(xiàn)在要統(tǒng)計“班級”為“二”，“語文成績”大于等于104，“錄取結(jié)果”為“重本”的人數(shù)，統(tǒng)計結(jié)果存放在本工作表的其他列。
公式如下：
=SUM(IF((B2:B9999='二')*(C2:C9999>=104)*(D2:D9999='重本'),1,0))
輸入完公式后按Ctrl+Shift+Enter鍵，讓它自動加上數(shù)組公式符號“{ }”
EXCEL中怎樣批量地處理按行排序
假定有大量的數(shù)據(jù)（數(shù)值），需要將每一行按從大到小排序，如何操作？
由于按行排序和按列排序都是只能有一個主關(guān)鍵字，主關(guān)鍵字相同時才能按次關(guān)鍵字排序。所以，這一問題不能用排序來解決。解決方法如下：
1、假定你的數(shù)據(jù)在A至E列，請在F1單元格輸入公式：
=LARGE($A1:$E1,COLUMN(A1))
用填充柄將公式向右向下復(fù)制到相應(yīng)范圍。
你的原有數(shù)據(jù)將按行從大到小排序出現(xiàn)在F至J列。如有需要可用“選擇性粘貼/數(shù)值”復(fù)制到其他地方。
注：第1步的公式可根據(jù)你的實際情況（數(shù)據(jù)范圍）作相應(yīng)的修改。如果要從小到大排序，公式改為：=SMALL($A1:$E1,COLUMN(A1))
在EXCEL中如何把B列與A列不同之處標識出來
1）如果是要求A、B兩列的同一行數(shù)據(jù)相比較：
假定第一行為表頭，單擊A2單元格，點“格式”—>“條件格式”，將條件設(shè)為：
“單元格數(shù)值”“不等于”=B2
點“格式”—>“字體”—>“顏色”，選中紅色，點兩次“確定”
用格式刷將A2單元格的條件格式向下復(fù)制。
B列可參照此方法設(shè)置。
2）如果是A列與B列整體比較（即相同數(shù)據(jù)不在同一行）：
假定第一行為表頭，單擊A2單元格，點“格式”—>“條件格式”，將條件設(shè)為：
“公式”=COUNTIF($B:$B,$A2)=0
點“格式”—>“字體”—>“顏色”，選中紅色，點擊兩次確定。
用格式刷將A2單元格的條件格式向下復(fù)制。
B列可參照此方法設(shè)置。
按以上方法設(shè)置后，A、B列均有的數(shù)據(jù)不著色，A列有B列無或者A列無B列有的數(shù)據(jù)標記為紅色字體。
EXCEL中如何控制每列數(shù)據(jù)的長度并避免重復(fù)錄入
1）用數(shù)據(jù)有效性定義數(shù)據(jù)長度。
用鼠標選定你要輸入的數(shù)據(jù)范圍，點“數(shù)據(jù)”—>“有效性”—>“設(shè)置”，“有效性條件”設(shè)成“允許”“文本長度”“等于”“5”（具體條件可以根據(jù)你的需要改變）
還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法等，定義好后點“確定”
2）用條件格式避免重復(fù)
選定A列，點“格式”—>“條件格式”，將條件設(shè)成“公式=COUNTIF($A:$A,$A1)>1',點“格式”—>“字體”—>“顏色”，選擇紅色后點擊兩次“確定”
這樣設(shè)定好后你輸入數(shù)據(jù)如果長度不對會有提示，如果數(shù)據(jù)重復(fù)字體將會變成紅色。
EXCEL中當(dāng)某一單元格符合特定條件，
如何在另一單元格顯示特定的顏色
比如：
A1>1時，C1顯示紅色
0<A1<1時，C1顯示綠色
A1<0時，C1顯示黃色
方法如下：
1、單擊C1單元格，點“格式”—>“條件格式”，
條件1設(shè)為：
公式=A1=1
2、點“格式”—>“字體”—>“顏色”，點擊紅色后點“確定”
條件2設(shè)為：
公式=AND(A1>0,A1<1)
3、點“格式”—>“字體”—>'顏色“，點擊綠色后點擊“確定”
條件3設(shè)為：
公式=A1<0
點“格式”—>“字體”—>“顏色”，點擊黃色后點擊“確定”
4、三個條件設(shè)定好后，點“確定”即出
后續(xù)會不斷更新干貨類知識
聯(lián)系客服
