Excel操作與技巧

1

2022-9-21

目錄

操作與技巧

函數與公式

數據透視表與圖表

表格潤色

操作與技巧

• 排序
• 篩選
• 查找與替換
• 定位
• 選擇性粘貼
• 數據有效性
• 分列
• 效率加倍的快捷鍵

操作與技巧

• 幾種典型的排序方法：
• 自定義序列法：如按數值高低進行排序
• 對排序選項進行設定：如對單元格填充顔色排序，（需注意：對帶公式的單元格進行排序）
• 篩選的類型：
• 按文本特征篩選、按數字特征篩選、按日期特征篩選、按字體顔色或單元格顔色篩選
• 單元格格式：
• 單元格的格式有：數字格式、對齊、字體、邊框、填充、保護 6種屬性。

4

2022-9-21

幻燈片5

操作與技巧

• 選擇性粘貼：
• 區别數值、公式、格式等
• 不同粘貼方式。
• 乘、除、轉置等高效方式。

操作與技巧

• 快捷鍵：
• 快捷鍵有很多，熟練掌握自己
• 常用的快捷鍵，可有效提高操
• 作表單速度。

操作與技巧

• 數據有效性的作用
• 限制單元格輸入或圈識無效數據
• 做單元格下拉條

操作與技巧

分列

函數與公式

Excel 運算及其含義、語法

• 什麼是公式，什麼是函數
• 函數基礎
• 常用函數應用

函數與公式

Excel 運算及其含義、語法

函數與公式

什麼是公式，什麼是函數

公式：公式必須以“=”開頭,即在Excel單元格中,凡以等号開頭的

輸入數據都被認為是公式,通過運算符号将數據、函數等元素按一定

順序連接在一起,從而實現對工作表中的數據執行計算的等式。

例如：C1=A1 A2 A3 A4 B1 B2 B3 B4

函數：簡而言之,函數就是預先定義好了的公式,比如上例中的公式

可以用函數C1=SUM(A1:B4)代替。函數不僅可以簡化公式,而且具有

僅用運算符連接的一般公式所不能替代的功能,如查找引用,邏輯判斷等。

函數與公式

函數基礎

• 單元格的相對引用和絕對引用
• A1
• $A1 鎖定第A列
• A$1 鎖定第一行
• $A$1 鎖定第A列，第一行
• 函數的語法
• 由函數名 括号 參數組成，
• 例 求和函數：sum（number1,number2, ...)
• TRUE VS FALSE
• Excel的邏輯值隻有兩個： True、False
• True False 與運算符進行各種計算式，需牢記True=1、False=0

函數與公式

一、文本函數

• upper
• lower
• replace
• clean
• trim
• concat
• text
• textjion
• 二、統計函數
• Max/Min
• Count是最常用的對數字類單元格計數的函數
• Countif（次數、區間判斷）
• Counta（計算非空單元格）對文本類單元格進行計數的函數

函數與公式

三、數字函數

• Sumif
• Sumproduct返回對應的數組或區域的乘積之和，語法形式為SUMPRODUCT(array1, [array2], [array3], ...)
• 四、數字函數
• If/Ifs；IF（條件，表達式1，表達式2），其中，表達式可以是數字、函數、單元和或單元格區域
• Or/And
• if、or、and 雙條件篩選

函數與公式

五、數據源的引用

• 相對引用、絕對引用、混合引用
• 跨表引用
• 六、查找和引用函數
• lookup：模糊查找、多條件查找、代替IF函數的多層嵌套
• vlookup：精确和模糊（簡稱或關鍵詞）查找、多條件匹配
• 自動生成大批量序号/編碼
• 統計遊泳成績的金、銀、銅牌

函數與公式

函數與公式

常見問題解決

1、問題：設置了公式後，結果不能實時更新。

解決思路：檢查系統設置的重算方法是否為“自動重算”。

方法：進入“公式”菜單下的“計算選項”頁面，設置“自動”即可。

2、問題：用SUM求和後，發現總值不對。

解決思路：檢查數據是否全為數值型。

方法：數值型通常自動靠右，文本型通常自動靠左（左上角會出現綠三角标志）。

選中相關單元格，右擊“選擇”設置單元格格式“菜單下的”數字“頁面，設置成”數值“型，

将文本型值重新手動輸入即可。

3、問題：設置了公式後，全表複制使用，但結果值不對。

解決思路，檢查确認公式是絕對引用還是相對引用，會引發錯誤。

方法：絕對引用和相對引用的快捷切換鍵是F4。

4、問題：頁面固定，使用滾動條無法翻頁。

解決思路：取消窗口凍結。

方法：在”視圖“菜單下面選擇”凍結窗口“下拉菜單選擇”

取消凍結窗口“。

5、問題：雙擊鼠标自動往下填充數值時，數據不能按想法出現遞增。

解決思路：組合Ctrl鍵即可。

方法：在雙擊鼠标的同事按下Ctrl鍵，可實現數據遞增。

數據透視表與圖表

表格潤色

表格潤色

• 潤色小技巧二
• 報表名稱（在表格擡頭，通常字号略大）
• 表頭（在表格主體的頂端與側端，通常可用顔色着重标注）
• 各字段數據值（如有文字内容應相對簡略，避免視覺疲勞）
• 報表需要：
• 合适的列寬和行高（鼠标放置需求列或行線的最末端，雙擊即可）
• 當表格過長或過寬時，應使用“凍結窗口”功能。
• 潤色小技巧一
• 超鍊接：在excel制作超鍊接和PPT類似；可用文字、圖片、圖形标識。
• 條件格式（字體顔色、數據條）
• 表格樣式

12022-9-21操作與技巧函數與公式數據透視表與圖表表格潤色

1234目錄22022-9-21Ø 排序Ø 篩選Ø 查找與替換Ø 定位Ø 選擇性粘貼Ø 數據有效性Ø 分列Ø 效率加倍的快捷鍵32022-9-211操作與技巧Ø幾種典型的排序方法：ü自定義序列法：如按數值高低進行排序ü對排序選項進行設定：如對單元格填充顔色排序，（需注意：對帶公式的單元格進行排序）Ø篩選的類型：按文本特征篩選、按數字特征篩選、按日期特征篩選、按字體顔色或單元格顔色篩選Ø單元格格式：單元格的格式有：數字格式、對齊、字體、邊框、填充、保護 6種屬性。42022-9-211操作與技巧Ø選擇性粘貼：區别數值、公式、格式等不同粘貼方式。Ø乘、除、轉置等高效方式。52022-9-211操作與技巧Ø快捷鍵：快捷鍵有很多，熟練掌握自己常用的快捷鍵，可有效提高操作表單速度。

62022-9-211操作與技巧Ø數據有效性的作用ü限制單元格輸入或圈識無效數據ü做單元格下拉條72022-9-211操作與技巧分列82022-9-211操作與技巧2函數與公式2ØExcel 運算及其含義、語法Ø什麼是公式，什麼是函數Ø函數基礎Ø常用函數應用92022-9-21運算符号運算功能優先級（）括号1-負号2%百分号3Λ乘方4*與/乘、除法5 與-加、減法6&文本連接7=、<、>、>=、<=、<>等于、小于、大于、小于等于、大于等于、不等于8ØExcel 運算及其含義、語法102022-9-21函數與公式2公式：公式必須以“=”開頭,即在Excel單元格中,凡以等号開頭的輸入數據都被認為是公式,通過運算符号将數據、函數等元素按一定順序連接在一起,從而實現對工作表中的數據執行計算的等式。

例如：C1=A1 A2 A3 A4 B1 B2 B3 B4Ø什麼是公式，什麼是函數函數：簡而言之,函數就是預先定義好了的公式,比如上例中的公式可以用函數C1=SUM(A1:B4)代替。函數不僅可以簡化公式,而且具有僅用運算符連接的一般公式所不能替代的功能,如查找引用,邏輯判斷等。

112022-9-21函數與公式2ü函數的語法由函數名 括号 參數組成，例 求和函數：sum（number1,number2, ...)Ø函數基礎üTRUE VS FALSEExcel的邏輯值隻有兩個： True、FalseTrue False 與運算符進行各種計算式，需牢記True=1、False=0ü單元格的相對引用和絕對引用A1$A1 鎖定第A列A$1 鎖定第一行$A$1 鎖定第A列，第一行122022-9-21函數與公式22022-9-2113函數與公式2一、文本函數üupperülowerüreplaceücleanütrimüconcatütextütextjion二、統計函數üMax/MinüCount是最常用的對數字類單元格計數的函數üCountif（次數、區間判斷）üCounta（計算非空單元格）對文本類單元格進行計數的函數

2022-9-2114三、數字函數üSumifüSumproduct返回對應的數組或區域的乘積之和，語法形式為SUMPRODUCT(array1, [array2], [array3], ...)函數與公式2四、數字函數üIf/Ifs；IF（條件，表達式1，表達式2），其中，表達式可以是數字、函數、單元和或單元格區域üOr/Andüif、or、and 雙條件篩選

2022-9-2115六、查找和引用函數ülookup：模糊查找、多條件查找、代替IF函數的多層嵌套üvlookup：精确和模糊（簡稱或關鍵詞）查找、多條件匹配ü自動生成大批量序号/編碼ü統計遊泳成績的金、銀、銅牌函數與公式2五、數據源的引用ü相對引用、絕對引用、混合引用ü跨表引用2022-9-2116顯示結果存在問題#####單元格所在列不夠寬，或使用了負的日期時間#VALUE在需要輸入數值或邏輯值的單元格輸入文本#REF!引用了無效的單元格#N/A引用的單元格内沒有可以使用的數值#NUM參數不對#NAME?含有EXCEL不能識别的名字或字符#DIV/0!除數為0函數與公式

22022-9-2117常見問題解決1、問題：設置了公式後，結果不能實時更新。 解決思路：檢查系統設置的重算方法是否為“自動重算”。 方法：進入“公式”菜單下的“計算選項”頁面，設置“自動”即可。2、問題：用SUM求和後，發現總值不對。 解決思路：檢查數據是否全為數值型。 方法：數值型通常自動靠右，文本型通常自動靠左（左上角會出現綠三角标志）。

選中相關單元格，右擊“選擇”設置單元格格式“菜單下的”數字“頁面，設置成”數值“型，将文本型值重新手動輸入即可。3、問題：設置了公式後，全表複制使用，但結果值不對。 解決思路，檢查确認公式是絕對引用還是相對引用，會引發錯誤。 方法：絕對引用和相對引用的快捷切換鍵是F4。4、問題：頁面固定，使用滾動條無法翻頁。 解決思路：取消窗口凍結。 方法：在”視圖“菜單下面選擇”凍結窗口“下拉菜單選擇”取消凍結窗口“。

5、問題：雙擊鼠标自動往下填充數值時，數據不能按想法出現遞增。 解決思路：組合Ctrl鍵即可。 方法：在雙擊鼠标的同事按下Ctrl鍵，可實現數據遞增。函數與公式2數據透視表與圖表

3182022-9-21表格潤色4192022-9-21202022-9-21潤色小技巧二報表名稱（在表格擡頭，通常字号略大）ü表頭（在表格主體的頂端與側端，通常可用顔色着重标注）ü各字段數據值（如有文字内容應相對簡略，避免視覺疲勞）報表需要：ü合适的列寬和行高（鼠标放置需求列或行線的最末端，雙擊即可）ü當表格過長或過寬時，應使用“凍結窗口”功能。潤色小技巧一ü超鍊接：在excel制作超鍊接和PPT類似；可用文字、圖片、圖形标識。ü條件格式（字體顔色、數據條）ü表格樣式表格潤色4212022-9-21THANKS

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!