表格大全及使用方法（excel必備知識點）

2023-07-07 22:43:28

工作期間，經常用到excel處理資料，但常常會出現一些字元錯誤，我們不知道出錯的原因，還有一些函式，是我們不經常用到，但用到時，又一時想不起來，只能花時間去問度娘.每當遇到這個時候，都恨自己怎麼不會. 所以利用待業在家的日子，重新複習了一遍常用的基本知識點和常用的函式公式理解，也供大家學習.希望大家在實際應用中可以多練習，一定也會熟能生巧的~

1、條件格式的設定，給特定條件下的值新增顏色

選區範圍——點選條件格式——選擇介於某個範圍或指定值的單元格——設定凸顯的顏色即可

2、自定義格式的設定

表格中出現前幾位數值一致的文字時，可以在自定義格式中設定"12345"00,然後在單元格中輸入1 ，則返回12345601

3、多個關鍵字排序

在資料——排序中設定不同的關鍵詞（主要關鍵詞、次要關鍵詞、查詢順序，升序或降序排列，確認即可。

4、單元格計算常見錯誤

####！指單元格中的資料太長或公式中產生的結果值太大

#DIV/0 指公式中的除數為0 或公式中的除數為空單元格

#NAME？指無法識別的額名稱

#NULL！指不正常的區域運算或不正確的單元格引用

#NUM！指使用了不能接受的引數

#REF！指引用了無效的單元格

#VALUE！指含有一個錯誤型別的引數或運算元

5、函式篇

DAYS360（開始日期，結束日期）指兩個日期之間的相差天數

(1) 數學函式：

ABS（number)返回的是值的絕對值

EXP(number) 指 e的n次冪，e=2,71828182845904 .number指底數e的指數。

POWER(number, power）指返回給定數字的乘冪。number是底數， power指指數。例如：=power(3,2)=9

FACT（number）指計算正定項的階乘，某個數的階乘=1*2*3*....*該數. 如果該數不是整數，則擷取整數；只支援170以下的階乘計算

ROUND(number,num_digits)指進行四捨五入的舍入；

ROUNDDOWN(number,digits)指無條件地退位；（直接捨去位數值，歸0，整百顯示，用法同函式Trunc）

ROUNDUP(number,digits)指無條件地進位。(不考慮後一位數值是否滿足四捨五入的條件）

number 指需要進行舍入的數，num_digits指指定的位數。

num_digits的含義：等於0 ，指舍入到最接近的整數位；大於0，指舍入到指定的小數位；小於0，指舍入到指定的整數位，

例如：

round(2.671,0)=3;round(2.671,1)=2.7;round(2.671,-1)=0 or round(267.1,-1)=260

rounddown(2.671,0)=2;rounddown(2.671,1)=2.6;rounddown(2.671,-1)=0 or rounddown(267.1,-1)=260

roundup(2.671,0)=3;roundup(2.421,1)=2.5

Sumif(range,criteria,sum_range)指在指定條件範圍內，滿足一個條件的前提下，有哪些單元格將被相加。range指條件的取值範圍， criteria指條件，sum_range指求和範圍。

(2)統計函式：

COUNT(value1,value2....)統計引數列表中包含數值資料的單元格個數。（只有數字型別的資料才被計算）

COUNTA(value1,value2....)計算引數列表中非空值單元格的個數。（文字、邏輯值或錯誤值、空字元都會被計算在內）

COUNTBLANK(range)計算指定單元格區域內空白單元格的個數。

COUNTIF(range,criteria)計算區域中滿足給定條件的單元格的個數

LARGE(array,k) 指在陣列區域範圍內，找到排序第K的值（正向排序）

SMALL(array,k)指在陣列區域範圍內，找到排序第k的值（升序排列）

MAX（number1,number2....)一組資料中的最大值.(引數可以是數字、邏輯值、空白單元格或數字的文字形式）

MODE（number1,number2...)一個陣列或資料區域中出現頻次最多的值。（引數可以是數字、名稱、陣列或引用，如果不含有重複值，返回#N/A）

MIN(number1,number2.....)一組陣列資料中的最小值。（引數是數字、邏輯值、空白單元格或數字的文字形式，也可是陣列或引用），與作用相反。

RANK(number,ref,order)指數字在所有數值中排第幾位。

number是等待排位的數字， ref是陣列或者引用，order是0或忽略時，排位是基於對ref降序排列；order不為0時，排位是基於對ref升序排列。

(3)查詢與引用函式

Hlookup(lookup_value,table_array,row_index_num,range_lookup)指在表格或數值陣列的首行查詢指定的數值，並由此返回指定行處與該數值同一行的數值。（用於查詢行、列條件下的資料值）

lookup_value表示在資料表第一行中查詢的數值，可以是數值、引用或文字字串；

table_array指在查詢資料的資料表，可以引用區域或區域名稱；

row_index_num指在引數table_array中需要返回的值的行序號，例如值為1，返回第一行的數值；

range_lookup 指邏輯值， True是近似匹配值，False是精確匹配值

Hyperlink( link_location,friendly_nam e) 指建立一個快捷方式，去開啟儲存在網路伺服器、internet的檔案。

link_location 是指文件的路徑和檔名；friendly_name 指單元格中跳轉的文字值或數字值。

INDEX用於返回表或區域中的值或值的引用。

在陣列形式下的格式：index(array,row_num,column_num) array指單元格區域或陣列常量；row_num是陣列中某行的行序號；column_num 是陣列中某列的列序號

在引用形式下的格式：index(array,row_num,column_num,area_num) area_num指引用中的一個區域，返回該區域中的行號和列號的交叉值

反向查詢引用：INDEX(array,match(lookup_value,lookup_array,match_type)

MATCH函式返回的是與指定數值在當前列中的相應位置。match_type=1或省略， look_array必須按升序排列；=0，look_array按任何順序排列；=-1按降序排列

交叉查詢:vlookup 和match結合使用。

區間查詢：lookup(number, lookup_value)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

首列查詢指定數值，比較值在首行時，使用hlookup

(4)文字函式

CONCATANAT(text1,text2.....)指將幾個文字字串合併為一個文字字串。用法相當於&

FIND和FINDB是用於查詢一個字串在另一個字串中出現的位置。find以字元為單位，findb是以位元組為單位。

Left(text,num_chars)和leftb(text,num_bytes)是基於所指定的字元數返回文字字串中第一個或前幾個字元。left返回的是字元，leftb是以位元組為單位。

TEXT包含要提取字元的文字字串單元格， num_chars指函式left提取的字元數（需要哪幾位）；num_bytes按位元組指定由函式leftb所要提取的字元數。

（一個英文字母(不分大小寫)佔一個位元組的空間，一箇中文漢字佔兩個位元組的空間．

符號：英文標點佔一個位元組，中文標點佔兩個位元組．）

LEN(text)和LENB(text)指返回字串的長度，len以字元為單位， lenb以位元組為單位返回。

LOWER(text)將文字字串中的所有的大寫字母轉化為小寫字母。

UPPER(text)將文字字串中的所有小寫字母轉換成大寫字母。

MID(text,start_num,num_chars)返回文字字串中從指定位置開始的特定數目的字元。以字元為單位的返回。

text指提取字元的文字字串，start_num指文字中要提取的第一個字元的位置， num_chars指定文字函式從文字中返回字元的個數

REPLACE(old_text,start_num,num_chars,new_text)指使用其他的文字字串並根據所指定的字元數替換某文字字串中的部分文字。以字元為單位。

old_text指要替換其他部分字元的文字；start_num要用new_text替換的old_text中字元的位置；

num_chars表示替換的個數；

num_text表示使用者替換old_text中字元的文字。

VALUE指將數字的文字字串轉化成數字。

以上是這兩天系統學習整理的，記錄的有些粗略，但希望是有用的.