Excel的常用技巧及應用

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

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

同上添加表

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

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

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

先輸入半角單引號

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

自動填充相同內(nèi)容單元格為變量或函數(shù)

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

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

升序和降序排序的關聯(lián)

需要選定有關聯(lián)的區(qū)域篩選

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

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

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

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

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

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

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

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

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

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