Excel的常用技巧及應(yīng)用

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

在表名處點(diǎn)右鍵，彈出窗口里有此功能刪除表

同上添加表

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

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

在表名處點(diǎn)右鍵，彈出窗口里有此功能主要功能與操作（對單元格的操作）單元格內(nèi)容對齊方式（上中下，左中右）文字方向字體、字體大小和顏色單元格顏色和底紋數(shù)字的類型（數(shù)值、文本、貨幣、%、中文大寫等）強(qiáng)制分行輸入 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年版可以插入單元格、行和列主要功能與操作（排序和篩選）排序的方式

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

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

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

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

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

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

如選定罰1罰2，插入單元格→下移主要功能與操作（多頁打印表頭）表示第1行和第2行為標(biāo)題主要功能與操作（取值）直接等號取值同表取值（=C2,取C2單元格的值）同工作簿不同表取值（=sheet2!c2,取表2C2單元格的值）取另一個EXCEL活動文檔的值（=路徑+工作表取值）函數(shù)取值（后面介紹）常用函數(shù)（1/10）求和函數(shù)SUM() 無條件求和格式：SUM(以逗號間隔的單元格或數(shù)值，或陣列)陣列是用冒號連接對角線頭尾元素表示，如b2:f6可以進(jìn)行加減乘除混合運(yùn)算。SUMIF() 單條件求和格式：SUMIF(range,criteria,sum_range)用途：對滿足條件的單元格進(jìn)行求和運(yùn)算。條件區(qū)域求和區(qū)域常用函數(shù)（2/10）乘函數(shù)product()格式：product(number1,…numberX)例如，product(2,3,5),則其值為30常用函數(shù)（3/10）條件函數(shù)If()格式：if(條件判斷，為真時的值，為假時的值)可以多重嵌套。應(yīng)用：成績評價。=IF(B3<3,”不及格”,IF(B3<4,”及格”,IF(B3<5,”良”,”優(yōu)”)))常用函數(shù)（4/10）文本轉(zhuǎn)化為地址函數(shù)Indirect()例如，indirect(“e5”),則結(jié)果為E5單元格的值應(yīng)用場合：使用下拉方式填充單元格時，可以保持公式中單元格恒定不變?？捎糜谝霉ぷ鞅∑渌砘蚱渌顒游募碇械膯卧竦闹?。常用函數(shù)（5/10）指定窗體取值Index()格式index(數(shù)組，行號（數(shù)組中），列號（數(shù)組中）)多個數(shù)組的格式Index（（數(shù)組1，數(shù)組2…），行號，列號，數(shù)組號）相當(dāng)于數(shù)學(xué)中的平移新坐標(biāo)系相當(dāng)于新坐標(biāo)系的x坐標(biāo)相當(dāng)于新坐標(biāo)系的y坐標(biāo)常用函數(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）條件計(jì)數(shù)函數(shù)格式countif(陣列，條件)Countif(陣列，”*”),返回包含文本內(nèi)容的單元格數(shù)量…“>80”,返回大于80的單元格數(shù)量…‘’???‘’，返回包含3個字符的單元格數(shù)量…“<>”，統(tǒng)計(jì)不為空的單元格數(shù)量…today(),返回當(dāng)前日期的數(shù)量…“*GOOD*”，返回單詞含GOOD的單元格數(shù)量常用函數(shù)（9/10）查找字符的位置Find(要查找的字符，字符串)返回目標(biāo)字符的字符位置例如：FIND(“中國”,“我愛中國”)，結(jié)果為3常用函數(shù)（10/10）連接字符串函數(shù)Concatenate(字符串1，字符串2，…)直接用&符號連接應(yīng)用財(cái)務(wù)自動計(jì)算與核算統(tǒng)計(jì)分配提取數(shù)字查收查引第一作者排序論文標(biāo)題排序委托人提供的論文與檢索結(jié)果比較多次檢索結(jié)果之間的比較自動計(jì)算收費(fèi)

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

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