《excel表格使用技巧.ppt》由會(huì)員分享，可在線閱讀，更多相關(guān)《excel表格使用技巧.ppt（24頁珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

Excel應(yīng)用技巧及精講案例 郁郁2011年3月3日 1 怎樣能夠快速的插入工資表的表頭在每個(gè)人的信息欄 在表頭下位置插入N行 復(fù)制表頭 再在第一列插入空列 在A列輸入單數(shù)列1 3 5 至表頭末 再在原來的人名前輸入雙數(shù)列2 4 6 用排序的方法 按A列 從小到大排序 就可得到有表頭的工資表了 2 建立分類下拉列表填充項(xiàng) 做財(cái)務(wù)報(bào)表時(shí) 我們常常要將資產(chǎn)的 關(guān)鍵字 和 地點(diǎn) 輸入到表格中 為了保持名稱的一致性 利用 數(shù)據(jù)有效性 功能建了一個(gè)分類下拉列表填充項(xiàng) 1 建立一個(gè)資產(chǎn)的 關(guān)鍵字 和 地點(diǎn) 數(shù)據(jù)庫 選中A列 關(guān)鍵字 名稱所在列 在 名稱 欄內(nèi) 輸入 服務(wù)器設(shè)備 字符后 按 回車 鍵進(jìn)行確認(rèn) 仿照上面的操作 將B C 列分別命名為 終端軟件 軟件 第一節(jié)典型案例 2 切換到Sheet1中 選中需要輸入 關(guān)鍵字 的列 如C列 執(zhí)行 數(shù)據(jù) 有效性 命令 打開 數(shù)據(jù)有效性 對(duì)話框 在 設(shè)置 標(biāo)簽中 單擊 允許 右側(cè)的下拉按鈕 選中 序列 選項(xiàng) 在下面的 來源 方框中 輸入 工業(yè)企業(yè)輸入 服務(wù)器設(shè)備 終端軟件 軟件 序列 各元素之間用英文逗號(hào)隔開 確定退出 3 數(shù)據(jù)圖表的拍照 特別是目標(biāo)位置的格式編排也必須反映出來的時(shí)候 再使用連接數(shù)據(jù)的方式就行不通了 Excel早為我們準(zhǔn)備了 照相機(jī) 你只要把希望反映出來的那部分內(nèi)容 照 下來 然后把 照片 粘貼到其他的頁面即可 A 準(zhǔn)備 照相機(jī) 1 打開Excel的 工具 菜單 選擇 自定義 對(duì)話框 2 單擊 命令 選項(xiàng)卡 在 類別 中選擇 工具 在右邊 命令 列表中找到 攝影 并且將其拖到Excel頁面工具欄的任意位置 這一步很多人并不熟悉 一定要自己試一試 在Excel2007中打開 工具欄 在 不在功能區(qū)中的命令 中找到 照相機(jī) 點(diǎn) 添加 即可使用 B 給目標(biāo) 拍照 假設(shè)我們要讓Sheet2中的部分內(nèi)容自動(dòng)出現(xiàn)在Sheet1中 1 拖動(dòng)鼠標(biāo)并且選擇Sheet2中需要 拍照 的內(nèi)容 這一步相當(dāng)重要 一定要選準(zhǔn) 不選或者選錯(cuò) 那你的 照片 就會(huì)啼笑皆非了 2 用鼠標(biāo)單擊工具欄上準(zhǔn)備好的 照相機(jī) 按鈕 于是這個(gè)選定的區(qū)域就被 拍 了下來 C 粘貼 照片 1 打開Sheet1工作表 2 在需要顯示 照片 的位置上單擊鼠標(biāo)左鍵 被 拍攝 的 照片 就立即粘貼過來了 在Sheet2中調(diào)整 照片 的各種格式 粘貼到Sheet1中的內(nèi)容同步發(fā)生變化 而且因?yàn)椴迦氲牡拇_是一幅自動(dòng)更新的圖像文件 所以 圖片 工具欄對(duì)這個(gè)照片也是有效的 照片還可以自由的旋轉(zhuǎn) 放縮 拖移 D 取消拍照粘貼功能如果拍照完畢 此時(shí)想取消照片粘貼功能 只要再次用鼠標(biāo)單擊工具欄上的 照相機(jī) 按鈕 剛才 拍 下的照片則被取消 不會(huì)再被粘貼 員工面試成功后 公司HR需要給每位員工發(fā)放一份入職通知書 其中包括員工姓名 薪資 職級(jí)等內(nèi)容 如果數(shù)量少的話大家往往是采用手工填寫或者打印 若是人數(shù)較多的話 工作量非常大 實(shí)際上利用Excel的一些函數(shù)以及功能 并可以實(shí)現(xiàn)批量打印通知書 下面就以某公司的員工入職通知書打印為例 來介紹具體實(shí)現(xiàn)方法 員工入職信息表 通常一份標(biāo)準(zhǔn)的入職通知書 一般都需要包括員工姓名 薪資 職級(jí) 日期 因此在打印通知書之前 必須將這些資料整理好 4 Excel的函數(shù)實(shí)現(xiàn)入職通知書的批量打印 設(shè)計(jì)通知書模板新建一個(gè)工作表 將其命名為 批量聘用函 然后在此表的A1 H7單元格區(qū)域中 根據(jù)自己公司的要求 輸入入職通知書的必要內(nèi)容 如編號(hào) 標(biāo)題 開頭語 表格以及員工簽名的位置 圖3 由于要實(shí)現(xiàn)批量發(fā)送 必須在此入職函模板中添加一個(gè)通編號(hào)選擇單元格 這樣就能夠通過這個(gè)單元格中的不同序號(hào) 從而能夠顯示不同的人員的入職函 在G1單元格中輸入 編號(hào) 然后確定H1單元格為通知書序號(hào)選項(xiàng)單元格 同時(shí)為了將前面入職信息明細(xì)中每個(gè)員工的數(shù)據(jù)自動(dòng)引用過來 在B3中輸入公式 VLOOKUP G1 erf A2 K11 3 0 在C6中輸入公式 VLOOKUP B 3 erf C2 K11 3 0 并依次類推 在G1單元格中輸入1時(shí) 就會(huì)在通知書模板中顯示第1個(gè)入職函 而2時(shí) 則顯示第2個(gè)入職函 需要注意的是 VLOOKUP等函數(shù) 其作用為根據(jù)G1單元格中的數(shù)值 而自動(dòng)引用入職信息表明細(xì)對(duì)應(yīng)的員工姓名 職級(jí) 薪資等信息 5 如何設(shè)置Excel公式讓數(shù)據(jù)不再重復(fù)我們?cè)谑褂肊xcel2007編輯工作表格時(shí)經(jīng)常要輸入大量數(shù)據(jù) 如何設(shè)置才能使Excel公式讓數(shù)據(jù)不再重復(fù) 有時(shí)要求輸入的這些數(shù)據(jù)是不允許重復(fù)的 例如常見的身份證號(hào)碼等 一旦錯(cuò)誤輸入查找起來那可是相當(dāng)麻煩的一件事 對(duì)于這個(gè)問題 其實(shí)我們可以自定義公式 讓Excel對(duì)重復(fù)數(shù)據(jù)能夠自動(dòng)彈出警告信息 以便及時(shí)提醒操作人員 Step1 運(yùn)行Excel2007程序并新建一個(gè)工作表格 然后按下 Ctrl A 組合鍵全選該表格 Step2 單擊 數(shù)據(jù) 菜單 然后在 數(shù)據(jù)工具 功能區(qū)域中選擇 數(shù)據(jù)有效性 選項(xiàng) 接下來在隨即彈出的 數(shù)據(jù)有效性 對(duì)話框中選擇 設(shè)置 選項(xiàng)卡 在 允許 下拉列表框中選擇 自定義 標(biāo)簽 在 公式 下面的文本框中輸入 COUNTIF 1 65535 A1 1 不含雙引號(hào) 這里的 1 65535 表示對(duì)全工作表范圍進(jìn)行重復(fù)檢查 如果你需要檢查的只是某一特定的單元格范圍 可以根據(jù)實(shí)際情況重新進(jìn)行調(diào)整 但必須使用絕對(duì)方式 Step3 切換到 出錯(cuò)警告 選項(xiàng)卡 在這里程序已經(jīng)自動(dòng)勾選了 輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告 的復(fù)選框 接下來將 樣式 設(shè)置為 停止 選項(xiàng) 然后在右側(cè)的 標(biāo)題 和 錯(cuò)誤信息 兩個(gè)文本框中可以自行輸入相關(guān)的內(nèi)容 具體的警告信息等等 然后單擊 確定 按鈕即可 經(jīng)過設(shè)置之后 今后重復(fù)輸入相關(guān)數(shù)據(jù)時(shí) 程序會(huì)自動(dòng)彈出一個(gè)對(duì)話框 提示 有重復(fù)數(shù)據(jù)出現(xiàn) 提供 重試 取消 幫助 三個(gè)選項(xiàng) 選擇 重試 則對(duì)現(xiàn)有數(shù)據(jù)進(jìn)行檢查校核 選擇 取消 可以重新輸入 6 自動(dòng)切換輸入法在一弛工作表中 去返是既無數(shù)據(jù) 又有文字 這樣在輸入時(shí)就須要往返在中英文之間重復(fù)切換輸入法 無比麻煩 如果你要輸入的貨色很有法則性 比如這一列全是單詞 下一列齊是漢語說明 你可以用以下方式真現(xiàn)自動(dòng)切換 辦法是 1 選中要輸入英文的列 單擊 數(shù)據(jù) 菜單 選擇 有效性 命令 在彈出的 數(shù)占有效性 對(duì)話框中 選中 輸入法模式 選項(xiàng)卡 在 模式 框中選擇 封閉 英文模式 命令 單擊 斷定 按鈕 2 選中要輸入漢字的列 在 有效數(shù)據(jù) 對(duì)話框中 單擊 IME模式 選項(xiàng)卡 在 模式 框中選擇 打啟 命令 單擊 肯定 按鈕 這樣 當(dāng)光標(biāo)在前一列時(shí) 可以輸入英文 在下一列時(shí) 直接可以輸入中文 從而實(shí)現(xiàn)了中英文輸入方法之間的自動(dòng)切換 第二節(jié) Excel函數(shù)和統(tǒng)計(jì)特訓(xùn)絕對(duì)引用和相對(duì)引用在絕對(duì)與相對(duì)單元引用之間切換當(dāng)你在Excel中創(chuàng)建一個(gè)公式時(shí) 該公式可以使用相對(duì)單元引用 即相對(duì)于公式所在的位置引用單元 也可以使用絕對(duì)單元引用 引用特定位置上的單元 公式還可以混合使用相對(duì)單元和絕對(duì)單元 絕對(duì)引用由 后跟符號(hào)表示 例如 B 1是對(duì)第一行B列的絕對(duì)引用 借助公式工作時(shí) 通過使用下面這個(gè)捷徑 你可以輕松地將行和列的引用從相對(duì)引用改變到絕對(duì)引用 反之亦然 操作方法是 選中包含公式的單元格 在公式欄中選擇你想要改變的引用 按下F4切換 一 使用邏輯函數(shù)1 使用IF函數(shù)實(shí)現(xiàn)邏輯判斷 IF logical test value if true value if false Logical test計(jì)算結(jié)果為TRUE或FALSE的任何數(shù)值或表達(dá)式 Value if true是Logical test為TRUE時(shí)函數(shù)的返回值 如果logical test為TRUE并且省略了value if true 則返回TRUE 而且Value if true可以是一個(gè)表達(dá)式 Value if false是Logical test為FALSE時(shí)函數(shù)的返回值 如果logical test為FALSE并且省略value if false 則返回FALSE Value if false也可以是一個(gè)表達(dá)式 一個(gè)常用的例子 就是老師在統(tǒng)計(jì)學(xué)生成就時(shí) 盼望輸入60以下的分?jǐn)?shù)時(shí) 能顯示為 不及格 輸入60以上的分?jǐn)?shù)時(shí) 顯示為 合格 這樣的后果 利用IF函數(shù)可以很方就地實(shí)現(xiàn) 假設(shè)成績(jī)?cè)贏2單元格中 斷定結(jié)果在A3單元格中 那么在A3單元格中輸入公式 if A20 SUM A1 A5 0 此式就利用了嵌套函數(shù) 意念是 當(dāng)A1至A5的和大于0時(shí) 返回這個(gè)值 如果小于0 那么就返回0 還有一點(diǎn)要提示你注意 以上的符號(hào)均為半角 而且IF與括號(hào)之間也不能有空格 二 字符串函數(shù)1 隨意抽取字符串任意位置的內(nèi)容除了直接輸入外 從已存在的單元格內(nèi)容中提取特定字符輸入 絕對(duì)是一種省時(shí)又省事的方法 特別是對(duì)一些樣式雷同的信息更是如此 比如員工名單 籍貫等信息 如果我們想快速從A4單元格中提取稱謂的話 最好使用 RIGHT 源數(shù)據(jù)格 提取的字符數(shù) 函數(shù) 它表示 從A4單元格最右側(cè)的字符開始提取2個(gè)字符 輸入到此位置 當(dāng)然 如果你想提取姓名的話 則要使用 LEFT 源數(shù)據(jù)格 提取的字符數(shù) 函數(shù)了 還有一種情況 我們不從左右兩端開始 而是直接從數(shù)據(jù)中間提取幾個(gè)字符 比如我們要想從A5單元格中提取 武漢 兩個(gè)字時(shí) 就只須在目標(biāo)單元格中輸入 MID A5 4 2 就可以了 意思是 在A5單元格中提取第4個(gè)字符后的兩個(gè)字符 也就是第4和第5兩個(gè)字 舉例 從身份證自動(dòng)抽取出生年月日 c 文字和數(shù)字的相互轉(zhuǎn)換在Excel2002中 至少提供了三種有關(guān)文本大小寫轉(zhuǎn)換的函數(shù) 它們分別是 UPPER 源數(shù)據(jù)格 將文本全部轉(zhuǎn)換為大寫 LOWER 源數(shù)據(jù)格 將文本全部轉(zhuǎn)換成小寫 PROPER 源數(shù)據(jù)格 將文本轉(zhuǎn)換成 適當(dāng) 的大小寫 如讓每個(gè)單詞的首字母為大寫等 三 查找函數(shù)1 使用VLOOKUPVLOOKUP 函數(shù)的格式如下 VLOOKUP lookup value table array col index num range lookup 函數(shù)的參數(shù)中除了col index num表示table array中待返回的匹配值的列序號(hào)外 其他參數(shù)的意義和HLOOKUP 相同 四 合并函數(shù)使用 CONCATENATE text1 text2 Text1 text2 為1到30個(gè)將要合并成單個(gè)文本項(xiàng)的文本項(xiàng) 這些文本項(xiàng)可以為文本字符串 數(shù)字或?qū)蝹€(gè)單元格的引用 說明 也可以用 和號(hào) 運(yùn)算符代替函數(shù)CONCATENATE實(shí)現(xiàn)文本項(xiàng)的合并 五 常用統(tǒng)計(jì)函數(shù)常用統(tǒng)計(jì)函數(shù)簡(jiǎn)介 SUMIF公式 條件求和公式 SUMIF公式是根據(jù)指定條件對(duì)若干單元格求和的公式 比如 倉庫在打元龍大料單時(shí) 需要將圖號(hào)相同的調(diào)撥數(shù)加起來算總的調(diào)撥數(shù) 又如有時(shí)需要將每一種零件的當(dāng)月發(fā)出數(shù)累加匯總 此時(shí) SUMIF公式就非常有用 SUMIF公式的一般格式如下 SUMIF 用來判斷的單元格區(qū)域 判斷的條件 用來求和的單元格區(qū)域 2 數(shù)據(jù)透視分類匯總 數(shù)據(jù)透視表是一種交互式的表 可以進(jìn)行某些計(jì)算 如求和與計(jì)數(shù)等 所進(jìn)行的計(jì)算與數(shù)據(jù)在數(shù)據(jù)透視表中的排列有關(guān) 之所以稱為數(shù)據(jù)透視表 是因?yàn)榭梢詣?dòng)態(tài)地改變它們的版面布置 以便按照不同方式分析數(shù)據(jù) 也可以重新安排行號(hào) 列標(biāo)和頁字段 每一次改變版面布置時(shí) 數(shù)據(jù)透視表會(huì)立即按照新的布置重新計(jì)算數(shù)據(jù) 另外 如果原始數(shù)據(jù)發(fā)生更改 則可以更新數(shù)據(jù)透視表 六 在Excel中自定義函數(shù)Excel函數(shù)雖然豐富 但并不能滿足我們的所有需要 我們可以自定義一個(gè)函數(shù) 來完成一些特定的運(yùn)算 下面 我們就來自定義一個(gè)計(jì)算梯形面積的函數(shù) 1 執(zhí)行 工具 宏 VisualBasic編輯器 菜單命令 或按 Alt F11 快捷鍵 打開VisualBasic編輯窗口 2 在窗口中 執(zhí)行 插入 模塊 菜單命令 插入一個(gè)新的模塊 模塊1 3 在右邊的 代碼窗口 中輸入以下代碼 FunctionV a b h V h a b 2EndFunction4 關(guān)閉窗口 自定義函數(shù)完成 以后可以像使用內(nèi)置函數(shù)一樣使用自定義函數(shù) 提示 用上面方法自定義的函數(shù)通常只能在相應(yīng)的工作簿中使用 七 數(shù)據(jù)表的安全性1 如何保護(hù)公式不被誤修改1 選定全表 點(diǎn) 格式 單元格 保護(hù) 取消 鎖定 前面的對(duì)勾 點(diǎn) 確定 2 選中你要保護(hù)的含有公式的單元格 點(diǎn) 格式 單元格 保護(hù) 在 鎖定 前面打上對(duì)勾 點(diǎn) 確定 點(diǎn) 工具 保護(hù) 保護(hù)工作表 輸入兩次密碼 點(diǎn)兩次 確定 2 如何設(shè)置文檔為只讀 到文件夾無法刪除 方法如下 1 首先要把該文件夾所在的盤轉(zhuǎn)換成NTFS格式 在CMD里輸入convertX fs ntfs回車即可 2 進(jìn)入CMD窗口找到該文件所在目錄執(zhí)行命令 cacls文件夾名子 p計(jì)算機(jī)用戶名就是進(jìn)入系統(tǒng)輸入的那個(gè)用戶名 N回車這個(gè)文件夾沒有人能訪問了 就不要說刪除了 你想用的時(shí)候只要cacls文件夾名子 p計(jì)算機(jī)用戶名就是進(jìn)入系統(tǒng)輸入的那個(gè)用戶名 F回車對(duì)了就是把N換成F 就是建立一個(gè)別人既不能進(jìn)入又不能刪除的文件夾 第一步 在運(yùn)行中輸入cmd 回車 打開命令行窗口第二步 在命令行窗口中切換到想要建立文件夾的硬盤分區(qū) 如D盤第三步 輸入MD123 回車 注意文件夾名后有2個(gè)小數(shù)點(diǎn) 那么 如果自己想刪除或者進(jìn)入這個(gè)文件夾 又應(yīng)該如何操作呢 同樣也很簡(jiǎn)單 如果想刪除 在命令行窗口中輸入rd123 回車 即可刪除 如果想進(jìn)入 在命令行窗口中輸入startd 123 注意這里一定要是文件夾的絕對(duì)路徑 否則無法打開即可打開此文件夾 八 如何減少重復(fù)勞動(dòng) 我們?cè)趯?shí)際應(yīng)用Excel時(shí) 經(jīng)常遇到有些操作重復(fù)應(yīng)用 如定義上下標(biāo)等 為了減少重復(fù)勞動(dòng) 我們可以把一些常用到的操作定義成宏 其方法是 選取 工具 菜單中的 宏 命令 執(zhí)行 記錄新宏 記錄好后按 停止 按鈕即可 也可以用VBA編程定義宏 宏是一個(gè)指令集 宏 指一系列EXCEL能夠執(zhí)行的VBA語句 用來告知EXCEL來完成用戶指定的動(dòng)作 宏相似于計(jì)算機(jī)程序 然而它是完整運(yùn)行于EXCEL之中的 我們可以使用宏來完成單調(diào)的 頻繁的反復(fù)性工作 宏完成動(dòng)作的速度比用戶大家做要快得多 例如 我們可以創(chuàng)建一個(gè)宏 用來在工作表的每一行上輸入一組日期 并在每一單元格內(nèi)居中對(duì)齊日期 然后對(duì)此行應(yīng)用邊框格式 我們還可以創(chuàng)建一個(gè)宏 在 頁面設(shè)置 對(duì)話框中指定打印設(shè)置并打印文檔 如果只是用 錄制宏 的方法 基本就沒有什么難的 只是把一些操作象用錄音機(jī)一樣錄下來 到用的時(shí)候 只要履行這個(gè)宏 體系就會(huì)把那操作再執(zhí)行一遍 下面給出了宏的應(yīng)用場(chǎng)所 只要用 錄制宏 就可以助你完成義務(wù) 而不需要編程 如果想對(duì)所錄制的宏再進(jìn)行編輯 就要有必定的VBA常識(shí)了