第4章公式與函數的高級應用Excel辦公應用高級教程目錄Contents4.1

什么是數組公式4.2

文本函數的應用4.7

疑難解答4.3

查找與引用函數的應用4.4

日期與時間函數的應用4.6

實戰演練：制作員工檔案信息表4.5

邏輯函數的應用4.1.1理解數組在函數與公式應用中，數組是指按一行、一列或多行多列排列的一組數據元素的集合。數據元素可以是數值、文本、日期、邏輯值和錯誤值。數組可以分為常量數組、區域數組、內存數組和命名數組。1.常量數組常量數組是指直接在公式中寫入數組元素，并用大括號{}在首尾進行標識的字符串表達式。其不依賴單元格區域，可以直接參與公式的計算。常量數組的組成元素只能為常量元素、不能是函數、公式或單元格引用。常量元素中不可以包含美元符號、逗號、圓括號和百分號。一維縱向常量數組的各元素用半角分號“;”間隔。一維橫向常量數組的各元素用半角逗號“,”間隔。4.1.1理解數組2.區域數組如果在公式或函數參數中引用工作表的某個單元格區域，且其中函數參數不是單元格引用或區域類型，也不是向量時，Excel會自動將該區域引用轉換成由區域中各單元格的值構成的數組，可稱之為區域數組。3.內存數組內存數組是指某一公式通過計算，在內存中臨時返回多個結果值構成的數組。而該公式的計算結果，不必存儲到單元格區域中，便可作為一個整體直接嵌套其他公式中繼續參與計算。該公式本身則稱為內存數組公式。內存數組與區域數組的主要區別在于，區域數組通過引用而非通過公式計算獲得，并且區域數組依賴于引用的單元格區域，而非獨立存在于內存中。4.1.1理解數組4.命名數組命名數組是指，使用命名公式定義的一個常量數組、區域數組或內存數組。該名稱可在公式中作為數組來調用。在數據有效性和條件格式的自定義公式中，不接受常量數組，但可以將其命名后，直接調用名稱進行運算。4.1.2

認識數組公式數組公式是指區別于普通公式，并以按下【Ctrl+Shift+Enter】組合鍵來完成編輯的特殊公式。作為標識，Excel會自動在編輯欄中給數組公式的首尾加上大括號“{}”。數組公式的實質是單元格公式的一種書寫形式。4.1.3

創建數組公式利用數組公式可以同時計算一組或多組數據，并返回一個或多個計算結果。快速計算出“銷售金額”或“銷售總額”數據通過使用數組公式，可以快速計算“銷售金額”或“銷售總額”，下面將介紹具體的操作方法。（1）選擇E2:E11單元格區域，在“編輯欄”中輸入公式“=C2:C11*D2:D11”。（2）按【Ctrl+Shift+Enter】組合鍵，即可計算出所有商品的銷售金額。（3）選擇D12單元格，輸入公式“=SUM(C2:C11*D2:D11)”。（4）按【Ctrl+Shift+Enter】組合鍵，即可計算出“銷售總額”。4.1.4

數組公式的編輯在Excel中，對數組公式進行編輯時，有以下限制。不能單獨改變公式區域某一部分單元格的內容。不能單獨移動公式區域的某一部分單元格。不能單獨刪除公式區域的某一部分單元格。不能在公式區域插入新的單元格。如果需要修改數組公式，則選擇公式區域。按【F2】鍵進入編輯模式。修改公式內容后，按【Ctrl+Shift+Enter】組合鍵，結束編輯。目錄Contents4.1

什么是數組公式4.2

文本函數的應用4.7

疑難解答4.3

查找與引用函數的應用4.4

日期與時間函數的應用4.6

實戰演練：制作員工檔案信息表4.5

邏輯函數的應用4.2.1

LEN函數和LENB函數LEN函數用于返回文本字符串的字符數。其語法格式為：=LEN(text)參數說明：text為必需參數，表示要查找其長度的文本，空格將作為字符進行計數。字符串LENB函數用于返回文本字符串的字節數。其語法格式為：=LENB(text)參數說明：text為要查找其長度的文本。空格將作為字符進行計數。字符串4.2.1

LEN函數和LENB函數計算“總價”數據表格中的“數量”列中同時包含數量和單位，要想計算“總價”，可以使用LEN函數、LENB函數等。（1）選擇D2單元格，輸入公式“=C2*LEFT(B2,LEN(B2)-(LENB(B2)-LEN(B2)))”。（2）按【Enter】鍵確認，即可計算出“總價”，并將公式向下填充。4.2.2

LEFT函數和FIND函數LEFT函數用于從字符串的左側開始提取指定個數的字符。其語法格式為：參數說明：Text：為要提取字符的字符串。num_chars：為LEFT提取的字符數。如果忽略，為1。4.2.2

LEFT函數和FIND函數FIND函數用于返回一個字符串出現在另一個字符串中的起始位置。其語法格式為：參數說明：find_text：必需參數。是要查找的文本。within_text：必需參數。是包含要查找文本的文本。start_num：可選參數。指定開始進行查找的字符。within_text中的首字符是編號為1的字符。如果省略start_num，則假定其值為1。4.2.2

LEFT函數和FIND函數從地址信息中提取省份用戶可以通過LEFT函數和FIND函數嵌套使用，從地址中提取省份，下面將介紹具體的操作方法。（1）選擇B2單元格，輸入公式“=LEFT(A2,FIND("省",A2))”。（2）按【Enter】鍵確認，即可提取出省份，并將公式向下填充。4.2.3

MID函數和TEXT函數MID函數用于從任意位置提取指定數量的字符。其語法格式為：參數說明：Text:準備從中提取字符串的文本字符串。start_num：準備提取的第一個字符的位置。num_chars：指定所要提取的字符串長度。4.2.3

MID函數和TEXT函數TEXT函數用于將數值轉換為指定格式的文本。其語法格式為：參數說明：Value：為數值、計算結果為數字值的公式，或對包含數字值的單元格的引用。Format_text：為“設置單元格格式”對話框中“數字”選項卡上“分類”框中的文本形式的數字格式。4.2.3

MID函數和TEXT函數從身份證號碼中提取出生日期用戶可以通過TEXT函數和MID函數嵌套使用，從身份證號碼中提取出生日期，下面將介紹具體的操作方法。（1）選擇D2單元格，輸入公式“=TEXT(MID(C2,7,8),"0000-00-00")”。（1）按【Enter】鍵確認，即可從身份證號碼中提取出生日期，并將公式向下填充。4.2.4

REPLACE函數REPLACE函數用于將一個字符串中的部分字符用另一個字符串替換。其語法格式為：參數說明：old_text：要進行字符替換的文本。start_num：要替換為new_text的字符在old_text中的位置。num_chars：要從old_text中替換的字符個數。new_text：用來對old_text中指定字符串進行替換的字符串。4.2.4

REPLACE函數規范產品型號內容在“產品型號”中，有的“TRH”后面添加了“00”，有的沒有添加，現在需要規范“產品型號”，使其統一在“TRH”后面添加“00”。（1）選擇B2單元格，輸入公式“=IF(MID(A2,4,2)="00",A2,REPLACE(A2,4,,"00"))”。（2）按【Enter】鍵確認，即可在“TRH”后面添加“00”，然后將公式向下填充。4.2.5

SUBSTITUTE函數SUBSTITUTE函數用于用新字符替換字符串中的部分字符。其語法格式為：參數說明：text:必需參數。需要替換其中字符的文本，或對含有文本的單元格的引用。old_text：必需參數。需要替換的文本。new_text：必需參數。用于替換old_text的文本。Instance_num：可選參數。為一數值，用來指定以new_text替換第幾次出現的old_text。如果指定了instance_num，則只有滿足要求的old_text被替換；如果省略，則將用new_text替換text中出現的所有old_text。4.2.5

SUBSTITUTE函數刪除電話號碼中的空格“電話號碼”中添加了2個空格，下面將介紹如何使用SUBSTITUTE函數，對電話號碼進行調整。（1）選擇B2單元格，輸入公式“=SUBSTITUTE(SUBSTITUTE(A2,"","-",1),"","")”。（2）按【Enter】鍵確認，然后將公式向下填充，即可對“電話號碼”進行調整。4.2.6

CONCAT函數CONCAT函數用于連接列表或文本字符串區域。其語法格式為：參數說明：text1,[text2],…：是要與單個文本字符串連接的1到254個文本字符串或區域。4.2.6

CONCAT函數快速合并文本信息用戶可以使用CONCAT函數，將年級、學院代碼、專業代碼等組合，快速生成“學號”，下面將介紹具體的操作方法。（1）選擇E2單元格，輸入公式“=CONCAT(A2:D2)”。（2）按【Enter】鍵確認，即可快速生成“學號”，并將公式向下填充。目錄Contents4.1

什么是數組公式4.2

文本函數的應用4.7

疑難解答4.3

查找與引用函數的應用4.4

日期與時間函數的應用4.6

實戰演練：制作員工檔案信息表4.5

邏輯函數的應用4.3.1

VLOOKUP函數和COLUMN函數VLOOKUP函數用于查找指定的數值，并返回當前行中指定列處的數值。其語法格式為：參數說明：Lookup_value：為需要在數據表第一列中進行查找的數值。Lookup_value可以為數值、引用或文本字符串。當vlookup函數第一參數省略查找值時，表示用0查找。Table_array：為需要在其中查找數據的數據表。使用對區域或區域名稱的引用。col_index_num：為table_array中查找數據的數據列序號。col_index_num為1時，返回table_array第一列的數值，col_index_num為2時，返回table_array第二列的數值，以此類推。Range_lookup：為一邏輯值，指明函數VLOOKUP查找時是精確匹配，還是近似匹配。如果為FALSE或0，則返回精確匹配。如果range_lookup為TRUE或1，函數VLOOKUP將查找近似匹配值，如果找不到精確匹配值，則返回小于lookup_value的最大數值。4.3.1

VLOOKUP函數和COLUMN函數COLUMN函數用于返回引用的列號。其語法格式為：參數說明：Reference為需要得到其列號的單元格或單元格區域。如果省略reference，則假定為是對函數COLUMN所在單元格的引用。如果reference為一個單元格區域，并且函數COLUMN作為水平數組輸入，則函數COLUMN將reference中的列標以水平數組的形式返回。4.3.1

VLOOKUP函數和COLUMN函數快速查詢員工信息用戶可以將VLOOKUP函數和COLUMN函數嵌套使用，查詢員工信息，下面將介紹具體的操作方法。（1）選擇G2單元格，輸入公式“=VLOOKUP($F2,$A$2:$D$9,COLUMN(B:B),FALSE)”

。（2）按【Enter】鍵確認，并將公式向右填充，即可查詢出員工信息。4.3.2

MATCH函數和INDEX函數MATCH函數用于返回指定方式下與指定數值匹配的元素的相應位置。其語法格式為：參數說明：lookup_value：為查找值。其參數可以為值（數字、文本或邏輯值）或對數字、文本或邏輯值的單元格引用。lookup_array：為在1行或1列指定查找值的連續單元格區域。match_type：為指定檢索查找值的方法。4.3.2

MATCH函數和INDEX函數INDEX函數用于返回指定行列交叉處引用的單元格。其語法格式為：參數說明：Reference：必需參數。對一個或多個單元格區域的引用。如果為引用輸入一個不連續的區域，必須將其用括號括起來。如果引用中的每個區域只包含一行或一列，則相應的參數row_num或column_num分別為可選項。Row_num：必需參數。引用中某行的行號，函數從該行返回一個引用。Column_num：可選參數。引用中某列的列標，函數從該列返回一個引用。Area_num：可選參數。選擇引用中的一個區域，以從中返回row_num和column_num的交叉區域。選中或輸入的第一個區域序號為1，第二個為2，依此類推。如果省略area_num，則函數INDEX使用區域1。4.3.2

MATCH函數和INDEX函數根據書號檢索出書名信息用戶可以將MATCH函數和INDEX函數嵌套使用，根據書號，檢索書名，下面將介紹具體的操作方法。（1）選擇B13單元格，輸入公式“=INDEX(A2:A10,MATCH(B12,B2:B10,0))”。（2）按【Enter】鍵確認，即可根據書號，檢索出對應的書名。4.3.3

INDIRECT函數和ROW函數INDIRECT函數用于返回由文本字符串指定的引用。其語法格式為：參數說明：Ref_text：為對單元格的引用，此單元格可以包含A1-樣式的引用、R1C1-樣式的引用、定義為引用的名稱或對文本字符串單元格的引用。如果ref_text不是合法的單元格的引用，函數INDIRECT返回錯誤值#REF!或#NAME?。a1：為一邏輯值，指明包含在單元格ref_text中的引用的類型。如果a1為TRUE或省略，ref_text被解釋為A1-樣式的引用。如果a1為FALSE，ref_text被解釋為R1C1-樣式的引用。4.3.3

INDIRECT函數和ROW函數ROW函數用于返回引用的行號。其語法格式為：參數說明：Reference：為需要得到其行號的單元格或單元格區域。如果省略reference，則假定是對函數ROW所在單元格的引用。如果reference為一個單元格區域，并且函數ROW作為垂直數組輸入，則函數ROW將reference的行號以垂直數組的形式返回。4.3.3

INDIRECT函數和ROW函數合并多張工作表中的數據用戶通過將INDIRECT函數和ROW函數嵌套使用，可以合并多張工作表中的數據，下面將介紹具體的操作方法。（1）首先打開并查看“1月”、“2月”、“3月”3個工作表，工作表中的商品名稱相同，并且排列順序一致。（2）打開“總表”工作表，選擇B2單元格，輸入公式“=INDIRECT(B$1&"!B"&ROW())”。（3）按【Enter】鍵確認，即可引用1月的“華為手機”的銷量，并將公式向右填充。（4）選擇B2:D2單元格區域，將光標移至區域右下角，向下拖動鼠標，填充公式即可。目錄Contents4.1

什么是數組公式4.2

文本函數的應用4.7

疑難解答4.3

查找與引用函數的應用4.4

日期與時間函數的應用4.6

實戰演練：制作員工檔案信息表4.5

邏輯函數的應用4.4.1

YEAR函數和TODAY函數YEAR函數用于返回某個日期對應的年份。其語法格式為：參數說明：Serial_number：為一個日期值，其中包含要查找的年份。日期有多種輸入方式：帶引號的文本串（例如"2021/05/30"）、系列數（例如，如果使用1900日期系統則35825表示1998年1月30日）或其他公式或函數的結果。4.4.1

YEAR函數和TODAY函數TODAY函數用于返回當前日期。其語法格式為：計算出公司成立的周年數假設公司成立時間為2001年，要想計算公司成立多少周年，可以將YEAR函數和TODAY函數嵌套使用。（1）選擇B1單元格，輸入公式“=YEAR(TODAY())-2001”。（2）按【Enter】鍵確認，即可計算出公司成立多少周年。4.4.2

DATE函數、YEAR函數和MONTH函數DATE函數用于求以年、月、日表示的日期的序列號。其語法格式為：參數說明：Year：參數的值可以包含一到四位數字。默認情況下，MicrosoftExcelforWindows將使用1900日期系統。Month：一個正整數或負整數，表示一年中從1月至12月的各個月。如果所輸入的月份大于12，將從指定年份的一月份開始往上加算。Day：一個正整數或負整數，表示一月中從1日到31日的各天。如果day大于該月份的最大天數，則將從指定月份的第一天開始往上累加。4.4.2

DATE函數、YEAR函數和MONTH函數YEAR函數用于返回某個日期對應的年份。其語法格式為：參數說明：Serial_number：為一個日期值，其中包含要查找的年份。日期有多種輸入方式：帶引號的文本串（例如"2021/05/30"）、系列數（例如，如果使用1900日期系統則35825表示1998年1月30日）或其他公式或函數的結果。4.4.2

DATE函數、YEAR函數和MONTH函數MONTH函數用于提取日期中的月份。其語法格式為：參數說明：serial_number：表示一個日期值，其中包含要查找的月份。日期有多種輸入方式，例如"2021/05/30"。4.4.2

DATE函數、YEAR函數和MONTH函數計算指定月份第1天的日期值每月的第一天都是1號，如果要得到指定日期所在月第一天對應的日期值，可以將DATE函數、YEAR函數和MONTH函數嵌套使用。（1）選擇B2單元格，輸入公式“=DATE(YEAR(A2),MONTH(A2),1)”。（2）按【Enter】鍵確認，即可計算出結果，然后將公式向下填充，計算其他日期。4.4.3

DATEDIF函數DATEDIF函數用于用指定的單位計算起始日和結束日之間的天數。其語法格式為：參數說明：start_date：為一個代表開始日期的日期。日期值有多種輸入方式：帶引號的文本字符串（例如"2021/5/30"）、序列號或其他公式或函數的結果。end_date：用于表示時間段的最后一個（即結束）日期的日期。Unit：要返回的信息類型。4.4.3

DATEDIF函數計算出工作的天數和月數用戶可以使用DATEDIF函數，計算員工的工作天數和月數，下面將介紹具體的操作方法。（1）選擇D2單元格，輸入公式“=DATEDIF(B2,C2,"D")”。（2）按【Enter】鍵確認，即可計算出“工作天數”，并將公式向下填充。（3）選擇E2單元格，輸入公式“=DATEDIF(B2,C2,"M")”,按【Enter】鍵確認，即可計算出“工作月數”，并將公式向下填充。4.4.4

HOUR函數和MINUTE函數HOUR函數用于返回時間值的小時數。其語法格式為：參數說明：Serial_number：時間值，其中包含要查找的小時數。時間值有多種輸入方式：帶引號的文本字符串（例如"5:45PM"）、十進制數（例如0.78125表示6:45PM）或其他公式或函數的結果。4.4.4

HOUR函數和MINUTE函數MINUTE函數用于返回時間值的分鐘數。其語法格式為：參數說明：Serial_number：一個時間值，其中包含要查找的分鐘。時間值有多種輸入方式：帶引號的文本字符串（例如"7:25PM"）、十進制數（例如0.78125表示6:45PM）或其他公式或函數的結果。4.4.4

HOUR函數和MINUTE函數計算員工一天的工作時間用戶通過使用HOUR函數和MINUTE函數，可以計算員工的工作時間，下面將介紹具體的操作方法。（1）選擇E2單元格，輸入公式“=HOUR(C2)+MINUTE(C2)/60-HOUR(B2)-MINUTE(B2)/60-D2+24*(C2<B2)”。（2）按【Enter】鍵確認，即可計算出“工作時間”，并將公式向下填充。4.4.5

WEEKDAY函數和NOW函數嵌套WEEKDAY函數用于返回指定日期對應的星期數。其語法格式為：參數說明：serial_number：是要返回日期數的日期。應使用DATE函數輸入日期，或者將日期作為其他公式或函數的結果輸入。Return_type：用于確定返回值類型的數字。4.4.5

WEEKDAY函數和NOW函數嵌套NOW函數用于返回當前日期和時間。其語法格式為：計算出當前星期數假設當前日期為2021年8月12日，用戶可以將WEEKDAY函數和NOW函數嵌套使用，計算今天是星期幾。（1）選擇A2單元格，輸入公式“=WEEKDAY(NOW(),2)”。（2）按【Enter】鍵確認，即可計算出今天是星期幾。目錄Contents4.1

什么是數組公式4.2

文本函數的應用4.7

疑難解答4.3

查找與引用函數的應用4.4

日期與時間函數的應用4.6

實戰演練：制作員工檔案信息表4.5

邏輯函數的應用4.5.1

IF函數和AND函數IF函數用于執行真假值判斷，根據邏輯測試值返回不同的結果。其語法格式為：參數說明：Logical_test：表示計算結果為TRUE或FALSE的任意值或表達式。Value_if_true：表示logical_test為TRUE時返回的值。Value_if_false：表示logical_test為FALSE時返回的值。4.5.1

IF函數和AND函數AND函數用于判定指定的多個條件是否全部成立。其語法格式為：參數說明：Logical1,logical2,...是1到255個結果為TRUE或FALSE的檢測條件，檢測內容可以是邏輯值、數組或引用。所有參數的邏輯值為真時，返回TRUE；只要有一個參數的邏輯值為假，即返回FALSE。4.5.1

IF函數和AND函數判斷產品是否合格通過將IF函數和AND函數嵌套使用，可以判斷產品是否合格，下面將介紹具體的操作方法。（1）選擇C2單元格，輸入公式“=IF(AND(B2>100,B2<300),"合格","不合格")”

。（2）按【Enter】鍵確認，即可判斷出是否合格，并將公式向下填充。4.5.2

IF函數和OR函數OR函數用于判定指定的多個條件式中是否有一個以上成立。其語法格式為：參數說明：logical1,logical2,...是1到255個結果是TRUE或FALSE的檢測條件。在其參數組中，任何一個參數邏輯值為TRUE，即返回TRUE；所有參數的邏輯值為FALSE，才返回FALSE。4.5.2

IF函數和OR函數根據考核分判斷是否有獎勵通過將IF函數和OR函數嵌套使用，可以判斷是否有獎勵，下面將介紹具體的操作方法。（1）選擇D2單元格，輸入公式“=IF(OR(B2>80,C2>80),"有","沒有")”。（2）按【Enter】鍵確認，即可判斷出是否有獎勵，并將公式向下填充。4.5.3

IFS函數IFS函數用于檢查是否滿足一個或多個條件，且返回符合第一個TRUE條件的值。其語法格式為：參數說明：logical_test1：必需參數，計算結果為TRUE或FALSE的條件。value_if_true1：必需參數，當logical_test1的計算結果為TRUE時要返回的結果。logical_test2：可選參數，計算結果為TRUE或FALSE的條件。value_if_true2：可選參數，當logical_test2的計算結果為TRUE時要返回的結果。4.5.3

IFS函數根據“總分”判斷“考核結果”用戶可以使用IFS函數，根據“總分”判斷“考核結果”，下面將介紹具體的操作方法。（1）