Excel的常用技巧及應用

Excel的常用技巧及應用廖祥春2013-03-16主要內容概述主要功能與操作常用函數應用概述記錄排序歸類可視化計算、統計與分析初識EXCEL菜單欄剪貼板、字體、對齊方式單元格—插入、刪除編輯—排序、查找、選擇；有關計算圓形的OFFICE圖標（WORD,PPT,EXCEL）新建、打開、保存（另存）、打印（預覽）檢查文檔（是否有隱藏信息），加密文檔主要功能與操作（對表的操作）對表的操作重命名

在表名處點右鍵，彈出窗口里有此功能刪除表

同上添加表

表的下方有添加表的圖標，也可以使用shift+f11多表選取

按住CTRL選取多個表選取全部表

在表名處點右鍵，彈出窗口里有此功能主要功能與操作（對單元格的操作）單元格內容對齊方式（上中下，左中右）文字方向字體、字體大小和顏色單元格顏色和底紋數字的類型（數值、文本、貨幣、%、中文大寫等）強制分行輸入 Alt+Enter長數值顯示

先輸入半角單引號

身份證主要功能與操作（下拉）下拉填充單元格內容單元格為常量或文本

自動填充相同內容單元格為變量或函數

按行或按列依次加1遞增例如：文本與數字混合情況，若C1單元格為A1，則C1單元格下拉或右拉依次得到A1 A2A3……若為公式，則上例取a1,a2,a3……單元格的值。主要功能與操作（下拉舉例）等差下拉選定2個單元格，然后下拉。結果為按兩單元格的差值遞增的值例如，a1為2，a2為5，則下拉為8,11,14……

等比下拉2152841181416主要功能與操作（插入）不同的版本界面不同2007年版可以插入單元格、行和列主要功能與操作（排序和篩選）排序的方式

升序和降序排序的關聯

需要選定有關聯的區域篩選

條件（等于，不等于，含）主要功能與操作（行列互換）用復制粘貼的方式實現選擇性粘貼轉置主要功能與操作（下拉菜單）在某單元格中可以建立下拉菜單數據→（數據）有效性→設置中選序列來源中輸入或選擇下拉列表的內容位置可以直接輸入A9：A11主要功能與操作（表頭斜線）表格斜線的添加在單元格設置的邊框選項卡中添加斜線斜線單元格表頭項目的添加先輸入右三角形中要填入的名稱，后輸入左三角形上要填入的名稱；內容布局格式頂端對齊自動換行或強制換行空格調整主要功能與操作（表頭固定）固定行表頭

選中待固定行的下一行，點視圖中的凍結窗體。固定列標題

選中待固定列的下一列，點視圖中的凍結窗體。行列固定

選中固定待固定行列的下一行與下一列的交叉單元格，點凍結窗體。主要功能與操作（行數不齊表頭）方法一：草圖法先畫草圖，在需要合并單元格的方塊中標出合并的行列數方法二：自然順序法先依次將細項標題輸入選定需歸類的細項，執行插入單元格操作

如選定罰1罰2，插入單元格→下移主要功能與操作（多頁打印表頭）表示第1行和第2行為標題主要功能與操作（取值）直接等號取值同表取值（=C2,取C2單元格的值）同工作簿不同表取值（=sheet2!c2,取表2C2單元格的值）取另一個EXCEL活動文檔的值（=路徑+工作表取值）函數取值（后面介紹）常用函數（1/10）求和函數SUM() 無條件求和格式：SUM(以逗號間隔的單元格或數值，或陣列)陣列是用冒號連接對角線頭尾元素表示，如b2:f6可以進行加減乘除混合運算。SUMIF() 單條件求和格式：SUMIF(range,criteria,sum_range)用途：對滿足條件的單元格進行求和運算。條件區域求和區域常用函數（2/10）乘函數product()格式：product(number1,…numberX)例如，product(2,3,5),則其值為30常用函數（3/10）條件函數If()格式：if(條件判斷，為真時的值，為假時的值)可以多重嵌套。應用：成績評價。=IF(B3<3,”不及格”,IF(B3<4,”及格”,IF(B3<5,”良”,”優”)))常用函數（4/10）文本轉化為地址函數Indirect()例如，indirect(“e5”),則結果為E5單元格的值應用場合：使用下拉方式填充單元格時，可以保持公式中單元格恒定不變。可用于引用工作薄其他表或其他活動文件表中的單元格的值。常用函數（5/10）指定窗體取值Index()格式index(數組，行號（數組中），列號（數組中）)多個數組的格式Index（（數組1，數組2…），行號，列號，數組號）相當于數學中的平移新坐標系相當于新坐標系的x坐標相當于新坐標系的y坐標常用函數（6/10）取字符串函數Left(文本,個數)在字串中左取指定數量的字符Right(文本,個數)在字串中右取指定數量的字符MID(文本，開始位置，個數)

從某位置處取指定數量的字符常用函數（7/10）測字符串長度函數Len(文本)

獲得字符串的字符數量Lenb(文本)

獲得字符串的字節數量常用函數（8/10）條件計數函數格式countif(陣列，條件)Countif(陣列，”*”),返回包含文本內容的單元格數量…“>80”,返回大于80的單元格數量…‘’???‘’，返回包含3個字符的單元格數量…“<>”，統計不為空的單元格數量…today(),返回當前日期的數量…“*GOOD*”，返回單詞含GOOD的單元格數量常用函數（9/10）查找字符的位置Find(要查找的字符，字符串)返回目標字符的字符位置例如：FIND(“中國”,“我愛中國”)，結果為3常用函數（10/10）連接字符串函數Concatenate(字符串1，字符串2，…)直接用&符號連接應用財務自動計算與核算統計分配提取數字查收查引第一作者排序論文標題排序委托人提供的論文與檢索結果比較多次檢索結果之間的比較自動計算收費

（條件函數、求和函數與乘函數的組合）條件區分校內外特點便于分類統計特點自動計算打印工資條如果已經有了某一單位的工資表，如何快速把工資明細名稱表頭插入到每一個的工資中呢？插入空行一個一個粘貼嗎？批量插入空行--方法1輔助順序列+排序在數據區域的右邊空白區下拉插入一列下拉一個序列復制再粘貼該序列批量插入空行--方法2輔助帶空格的兩列+定位空格+插入空行批量粘貼表頭1.復制表頭（例如工作表）2.定位空行（條件定位）3.粘貼（Ctrl+v）應用（提取數字）數字和漢字組成的字符的提取（左取+長度）

不能含字母，例如下表中B3提取有問題。=LEFT(A1,2*LEN(A1)-LENB(A1))EI檢索結果提取標題原始記錄獲取題名行號=indirect(“a”&b1)方法1函數提取法方法2標號排序法序號刪除用mid()函數刪除標題中的序號EI輸出結果中，標題帶序號，如2.刪除需要用到的函數：右取值函數字符串長度函數查找函數Right(a1,len(a1)-find(“.”,a1))如果“點”后面有2空格（例如EI），需要少取2個字符刪除序號舉例帶點序號的刪除右取函數+查找定位函數+字符計數函數Right(c1,len(c1)-find(“.”,c1))帶空格序號的刪除同上，用空格“”代替“.”純數字序號刪除Mid(c1,2,40),分別在第一個2位數序號和第一個3位數序號處對公式進行修改，