2016銷售部內部培訓資料 朱EXCEL及其應用 EXCEL 大綱 EXCEL能做什么 為什么要學 EXCEL基礎概念數(shù)據(jù)操作公式與函數(shù)數(shù)據(jù)分析圖表制作 EXCEL 能做什么 數(shù)據(jù)共享 程序開發(fā)簡單動畫 協(xié)同辦公提高效率 潛力無限 圖表演示 統(tǒng)計 做報告 做演示 輔助計算 分析 算產量 造工資 制作表格 表單 做對賬單 EXCEL 內容構成 EXCEL圖表 帶時間軸的成因分布 看比例 EXCEL圖表 帶時間軸的成因分布 看數(shù)量 EXCEL圖表 退貨的成因和趨勢分析 EXCEL圖表 退貨成因分布餅圖 EXCEL圖表 銷售欠款折線圖 EXCEL圖表 銷售回款欠款指數(shù)散點圖 EXCEL圖表 典型條形圖應用 EXCEL圖表 高級條形圖 EXCEL圖表 高級雙軸曲線圖 EXCEL圖表 雙色柱形圖 EXCEL圖表 瀑布圖 EXCEL圖表 繪圖區(qū)分割 EXCEL圖表 甘特圖 EXCEL圖表 各種常用圖形的應用 餅圖 成分相對關系 條形圖 項目相對關系 柱形圖 時間序列相對關系 強調數(shù)量的級別 折線圖 時間序列相對關系 強調角度的運動和圖像的變換 趨勢 散點圖 頻率相對關系 雷達圖 艾爾尼爾森審核 ABB審核其它圖 如甘特圖 魚骨圖 控制圖 環(huán)形圖等等 EXCEL 日常表單的改進 回款表的改進 發(fā)貨記錄表的改進 對賬任務表的改進 記賬測算表 生產任務統(tǒng)計表的改進 產品規(guī)格數(shù)據(jù)表的改進 圖紙自動生成器 考勤表的改進 生產排程與調度 EXCEL 老司機與菜鳥 EXCEL 表格外觀設計的不良習慣 看過上面的表格 大家作何感想呢 標題 居中 不居中 表頭 使用多行標題 斜線標題字體 標題使用了花哨的華文彩云字體 單位名稱使用了輕佻的華文行楷 數(shù)據(jù)區(qū)域字體使用的楷體 而且數(shù)據(jù)區(qū)域字體不統(tǒng)一字號 不統(tǒng)一 數(shù)據(jù)區(qū)域存在多種字號邊框 所有單元格都加邊框 邊框格式不統(tǒng)一 殘缺不全 無關的單元格也設置了邊框 EXCEL 表格外觀設計的不良習慣 底色 單元格底色五顏六色對齊 對齊格式不統(tǒng)一列寬 部分列的列寬不夠 數(shù)字顯示不完整行高 高度不夠 未適當留白 數(shù)字填滿單元格整行整列設格式處處想強調 到處加底色 批注未隱藏網格線未隱藏 EXCEL 何為漂亮表格 布局合理 結構清晰 簡潔干凈 美觀大方漂亮表格幾大要素 字體 字號 顏色 字體顏色 填充顏色 邊框 下劃線 間距 對齊及縮進 EXCEL 何為漂亮表格 如何做到布局合理注意數(shù)據(jù)的邏輯性 相關或同類的數(shù)據(jù)應排列在一起重要的字段或記錄應靠前考慮數(shù)據(jù)記錄有無排序要求注意版式的選擇垂直 水平 當橫向一屏顯示不全時 則考慮縱向排列 以盡量不橫向翻屏為原則 另特別注意 標題的位置 居中否 合計行列位置 EXCEL 何為漂亮表格 如何做到結構清晰歸類 將同一類別的記錄歸組在一起間距 根據(jù)實際需要拉取合適行距 列距邊框 不是所有表格都必須用邊框 應根據(jù)不同層級選擇邊框要點 1 邊框是用來結構化表格 體現(xiàn)層級的 2 盡量使用開放式表格 3 用邊框線條的粗細來區(qū)分層級 明細級數(shù)據(jù)時可不用邊框 EXCEL 何為漂亮表格 字體數(shù)據(jù)區(qū)域與標題使用不同字體漢字建議使用宋體和微軟雅黑字體數(shù)字建議使用Arial ArialNarrow或TimesNewRoman字體以下是各字體的比較 EXCEL 字體效果展示 EXCEL 何為漂亮表格 字號標題建議使用22 26號字體表頭建議使用12 16號字體數(shù)據(jù)正文建議使用10 11號字體批注使用系統(tǒng)默認字體即可 另批注建議隱藏底色底色不能太暗也不能太亮顏色與字體顏色相協(xié)調底色種類不易過多 EXCEL 何為漂亮表格 行距 列距根據(jù)實際表格需要調整行距 列距調整原則 以一個單元格能顯示完當前數(shù)據(jù)為優(yōu)先參考 另需兼顧表格打印預覽效果 如遇數(shù)據(jù)實在太長 可嘗試自動換行或自動縮小字體 EXCEL 何為漂亮表格 如何做到表格美觀和諧就是美字體協(xié)調顏色協(xié)調版面干凈盡量少用批注刪除表格之外單元格的內容和格式備注置于表尾 EXCEL 表格美化應考慮的問題 考慮表格用途考慮是否打印考慮標題是否寫明重點考慮報表使用者的偏好形成自己的風格 EXCEL 表格美化之普通型 EXCEL 表格美化之清爽型 EXCEL 表格美化之簡約商務型 EXCEL 表格美化之典雅商務型 EXCEL 窗口 工作表標簽 列標 活動單元格 行號 工具欄 標題欄 任務窗格 菜單欄 繪圖工具欄 名稱框 公式編輯框 EXCEL 工作簿與工作表 工作薄 是一個Excel文件 擴展名為 xls 由工作表組成 最多可存放255個工作表 默認工作簿名為Book1 其中包含三個工作表Sheet1 Sheet2和Sheet3 工作表 是以列和行的形式組織和存放數(shù)據(jù)的表格 由單元格組成 每一個工作表都用一個工作表標簽來標識 工作簿與工作表的關系可以理解為一本書與書中各頁的關系 EXCEL 工作表的操作 1 插入工作表2 選擇和選取工作表3 重命名工作表4 移動 復制工作表5 刪除工作表6 隱藏工作表7 保護工作表8 拆分工作表9 凍結工作表 EXCEL 行和列 行和列 一個工作表一共有256列及65536行 列標 用字母序列表示 從A到IV 行號 用阿拉伯數(shù)字表示 從1到65536 EXCEL 單元格 單元格 行和列的相交點 是工作表的最小單位 可以是字符串 數(shù)據(jù) 公式等不同類型的內容 最多可容納32000個字符 單元格的位置 單元格根據(jù)其所處的列標和行號來命名 列標在前 行號在后 比如 A5 C9 活動單元格 每個工作表中只有一個活動單元格 活動單元格被粗邊框包圍著 填充柄 活動單元格的右下角有一個小黑色方塊 稱為填充柄 EXCEL 自動填充示例 EXCEL 單元格格式 單元格格式 除對齊 字體 邊框等類似于WORD的格式外 數(shù)字 格式需特別注意 EXCEL 單元格格式之數(shù)值型 數(shù)值型 可以用于算術計算的數(shù)據(jù) 包括常說的 數(shù)字 日期型 時間性和邏輯值 類型為1 默認為右對齊 限制 Excel只能精確15位有效數(shù)字 超過部分以0表示 如果是小數(shù)則直接去掉數(shù)值過大或者過小 Excel會用科學計數(shù)法表示 字母E 單元格可以輸入的最值 a最大的數(shù)值 9 99999999999999E 307b最大的正數(shù) 1 79769313486231E 308c最小的正數(shù) 2 229E 308d最大的負數(shù) 2 250738585073E 308e最小的負數(shù) 1 79769313486231E 308 EXCEL 單元格格式之日期和時間型 日期和時間型 日期和時間以一種特殊的數(shù)值形式存儲 實際上 被叫做序列值 該值大于等于0 小于等于2958466 所以 實際上 日期和時間的函數(shù)類型為1 限制 不能輸入小于系統(tǒng)默認值的日期 1111年11月11日 這樣將不會被識別為日期 允許的最大的時間為9999 59 59 9999說明 Excel對于時間的計算基本單位為天 將1小時算成1 24天 輸入時間的方法建議為 2016 10 1314 15 16 EXCEL 單元格格式之文本型 文本型 常說的文本和文本型的數(shù)值 函數(shù)類型為2 默認為左對齊 限制 不能參與計算 但是可以比較大小 說明 快遞單號 身份證號碼 稅號等應該設置為文本型 EXCEL 單元格格式之文本型 貨幣格式 數(shù)值前添加貨幣符號 會計格式 對齊小數(shù)點和貨幣符號 自定義 多用于特殊的日期時間等 mm 月 dd 日 和m d EXCEL 單元格輸入注意事項 輸入字符型數(shù)字 在數(shù)字前加單引號 身份證號 電話號碼加零 輸入日期 要按 年 月 日 年 月 日 的格式輸入時間 要按 時 分am pm 格式 時間和字母之間要空格 當然也有其他輸入格式 時 分 秒時 分時 分 秒am pm Ctrl 輸入當前日期Ctrl shift 輸入當前時間數(shù)據(jù)寬度 數(shù)值超過11位 自動以科學計數(shù)法輸入 例如 輸入 123456789012 系統(tǒng)顯示 1 23457E 11 輸入數(shù)值時可以用逗號 分節(jié)如 7 357 542 輸入純小數(shù)值 以 開頭 數(shù)值前自動補 0 負數(shù)前面輸入負號或 數(shù)值 正號省略數(shù)字以 結尾 則按百分數(shù)處理輸入分數(shù)時前面加0和空格 如 01 4 EXCEL 公式和函數(shù) 公式是對工作表中的數(shù)值執(zhí)行計算的等式 可以用于返回信息 操作單元格內容以及測試條件等 公式始終以等號 開頭 后面跟一個表達式 表達式是常量 引用 函數(shù)和運算符的組合 其計算結果為單個值 1 常量2 引用3 函數(shù)4 運算符5 公式示例 54 B4 SUM A2 A5 件 EXCEL 公式和函數(shù)之運算符 常用運算符包括算術運算符 比較運算符 文本連接運算符和引用運算符 常用算術運算符有 加號 減號 減號 星號 正斜杠 百分號 脫字號 比較運算符有 等號 大于號 大于等于號 不等號 文本連接運算符有 引用運算符有 冒號 逗號 空格 運算符優(yōu)先級1 空格 和 引用運算符 區(qū)域引用 交叉引用 聯(lián)合引用2 算術運算符 負號3 算術運算符 百分比4 算術運算符 乘方5 和 算術運算符 乘和除6 和 算術運算符 加和減7 文本運算符 文本連接8 比較運算符 比較兩個值 EXCEL 公式和函數(shù)之單元格引用 相對引用 公式中的相對單元格引用 如A1 是基于包含公式和單元格引用的單元格的相對位置 如果公式所在單元格的位置改變 引用也將隨之改變 如果多行或多列地復制或填充公式 引用會自動調整 默認情況下 新公式使用相對引用 絕對引用 公式中的絕對單元格引用 如 A 1 總是在特定位置引用單元格 如果公式所在單元格的位置改變 絕對引用將保持不變 如果多行或多列地復制或填充公式 絕對引用將不作調整 默認情況下 新公式使用相對引用 可能需要將它們轉換為絕對引用 例如 如果將某個源單元格中的絕對引用復制或填充到目標單元格中 則這兩個單元格中的絕對引用相同 混合引用 混合引用具有絕對列和相對行或絕對行和相對列 絕對引用列采用 A1 B1等形式 絕對引用行采用A 1 B 1等形式 如果公式所在單元格的位置改變 則相對引用將改變 而絕對引用則保持不變 如果多行或多列地復制或填充公式 相對引用將自動調整 而絕對引用則不作調整 若要在相對引用 絕對引用和混合引用之間快速切換 可在單元格或編輯欄中選擇公式包含的單元格引用 然后按F4鍵 例如 按F4可在B2 B 2 B 2和 B2之間快速切換 EXCEL 公式和函數(shù)之單元格引用 三維引用 三維引用包含單元格或區(qū)域引用 前面加上感嘆號 和工作表名稱的范圍 三維引用的語法格式如下 工作表名稱 單元格地址外部引用 外部引用也稱為鏈接 是對另一個Excel工作簿中的工作表的單元格或單元格區(qū)域的引用 也可以是對另一個工作簿中的已定義名稱的引用 通過創(chuàng)建外部引用 可以引用另一個工作簿中的單元格的內容 既可以引用特定的單元格區(qū)域或為單元格區(qū)域定義的名稱 也可以為外部引用定義名稱 名稱是代表單元格 單元格區(qū)域 公式或常量值的單詞或字符串 包含對其他工作簿的外部引用的公式具有兩種顯示方式 具體取決于源工作簿 為公式提供數(shù)據(jù)的工作簿 是打開還是關閉的 當源工作簿打開時 外部引用包含用方括號括起的工作簿的名稱 然后是工作表名稱和感嘆號 接著是公式要計算的單元格 例如 SUM Budget xlsx Annual C10 C25 當源工作簿未打開時 外部引用包括完整路徑 例如 SUM C Reports Budget xlsx Annual C10 C25 EXCEL 公式錯誤值與解決方法 EXCEL 數(shù)組公式 數(shù)組公式的建立方法1 選中需要保存數(shù)組公式結果的單元格或單元格區(qū)域 2 輸入公式的內容 3 按Ctrl Shift Enter鍵 4 在公式中用數(shù)組作為參數(shù)時 所有的數(shù)組必須是同維的 如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配 Excel會自動擴展該參數(shù) EXCEL 數(shù)組應用 某商場秋季進行換季服裝大降價 所有服裝都在打折 打折的比例隨時都在調整 計算打折后的新價格 如圖所示 EXCEL 名稱 名稱的意義在Excel中 同一個工作簿中的名稱是共享的 在一個工作表中定義的名稱 可以被另一個工作表的公式引用 這使公式具有更強大的功能 它能夠利用單元格的名稱在不同的工作表中查找到正確的單元格或單元格區(qū)域 名稱給單元格的引用帶來了許多方便 因為它使人們不必記住單元格在工作表中的引用位置 用名稱就能找到它 EXCEL 名稱應用 通過名稱查找蔬菜單價 指定A2 B12最左邊為名稱 EXCEL 公式和函數(shù)之函數(shù)的分類 加載宏和自動化函數(shù) 用于加載宏 自定義函數(shù)等 多維數(shù)據(jù)集函數(shù) 用于聯(lián)機分析處理 OLAP 數(shù)據(jù)庫的函數(shù) 數(shù)據(jù)庫函數(shù) 按照特定條件對現(xiàn)有數(shù)據(jù)進行分析的函數(shù) 日期和時間函數(shù) 分析并處理日期和時間 工程函數(shù) 用于工程分析的函數(shù) 財務函數(shù) 進行財務運算的函數(shù) 信息函數(shù) 用于確定存儲在單元格中的數(shù)據(jù)類型的函數(shù) 邏輯函數(shù) 用于判斷真假值或復合檢驗的函數(shù) 查找和引用函數(shù) 在現(xiàn)有數(shù)據(jù)中查找特定數(shù)值和單元格的引用函數(shù) 數(shù)學和三角函數(shù) 對現(xiàn)有數(shù)據(jù)進行數(shù)字取整 求和 求平均值以及復雜的函數(shù)運算 統(tǒng)計函數(shù) 對當前數(shù)據(jù)區(qū)域進行統(tǒng)計分析 文本函數(shù) 對字符串進行提取 轉換的函數(shù) EXCEL 錯誤信息函數(shù) EXCEL 數(shù)學函數(shù)之取整函數(shù) INT Number 用途 將任意實數(shù)向下取整為最接近的整數(shù) 語法 INT 實數(shù) 參數(shù) Number為需要處理的任意一個實數(shù) 實例 如果A1 16 24 A2 28 389 則公式 INT A1 返回16 INT A2 返回 29 生活應用 有實發(fā)工資金額 還有100元 50元 10元 1元 在發(fā)現(xiàn)金工資時我要用到分錢 比方說某人工資5573元 那么100元我們要發(fā)多少張 50元多少張 10元多少張 5元多少張 1元多少張呢 EXCEL 數(shù)學函數(shù)之取整函數(shù) ROUND Number Num digits 用途 按指定位數(shù)四舍五入某個數(shù)字 語法 ROUND 需四舍五入的數(shù)字 保留的小數(shù)位 注意 如果保留的小數(shù)位大于0 則四舍五入到指定的小數(shù)位 如果等于0 則四舍五入到最接近的整數(shù) 如果小于0 則在小數(shù)點左側按指定位數(shù)四舍五入 實例 如果A1 65 25 則公式 ROUND A1 1 返回65 3 ROUND 82 149 2 返回82 15 ROUND 21 5 1 返回20 Round A1 0 返回65 EXCEL 數(shù)學函數(shù)之取整函數(shù) ROUNDUP Number Num digits 用途 按指定位數(shù)向上舍入某個數(shù)字 語法 ROUNDUP 需向上舍入的數(shù)字 保留的小數(shù)位 其它用法如ROUND 函數(shù) 典型應用于計算箱數(shù) 人數(shù) 天數(shù)等 ROUNDDOWN Number Num digits 用途 按指定位數(shù)向下舍入某個數(shù)字 EXCEL 數(shù)學函數(shù)之求和函數(shù) SUM Number1 Number2 SUMIF Range Criteria Sum range 對range單元格區(qū)域中的數(shù)據(jù)進行Criteria條件檢查 然后對滿足條件行的sum range同行進行求和 SUMIFS sum range range1 criteria1 range2 criteria2 其中range1 range2 是計算關聯(lián)條件的1至127個區(qū)域 Criteria1 criteria2 是數(shù)字 表達式 單元格引用或文本形式的1至127個條件 用于定義要對哪些單元格求和 這些區(qū)域與條件是對應的 即Criteria1是用于range1區(qū)域的條件 criteria2是用于range2的條件 以此類推 Sum range是求和區(qū)域 EXCEL SUMIF演示 H7 H19 E E D E E A C B C A D A B M7 A 在函數(shù)中 自動轉換成數(shù)組形式 即 A A A A A A A A A A A A A E E D E E A C B C A D A B A A A A A A A A A A A A A 0 0 0 0 0 1 0 0 0 1 0 1 0 1 按次序逐對進行比較 2 相符 TRUE 為1 不相符 FALSE 為0 第 步得出的結果 再進行下一步運算 需要求A組的段取時間累計 怎么做呢 EXCEL SUMIF演示 結果 0 0 0 0 0 0 50 0 0 0 0 50 0 0 33 0 1 1 10 1 0 EXCEL 以數(shù)組的角度理解函數(shù)應用 SUMIF Range Criteria Sum Range Range 判斷區(qū)域 一維數(shù)組Criteria 條件 可以是固定值Sum Range 求和區(qū)域 一維數(shù)組運算步驟及注意點 1 Range與Criteria進行邏輯運算 得出結果 2 結果 與Sum Range進行相乘運算3 Range與Sum Range的范圍大小必須一致 EXCEL SUMIF與SUMIFS應用演示 1 在J3單元格中輸入公式 SUMIF D 3 D 10 I3 E 3 E 10 2 將此公式向下復制到J4 J5 J6 1 在K3單元格輸入公式 SUMIF D 3 D 10 I3 G 3 G 10 2 將此公式向下復制到K4 K5 K6 在J10單元格中輸入統(tǒng)計李本成銷售的彩電總數(shù)量的計算公式 SUMIFS E 3 E 13 D 3 D 13 I10 A 3 A 13 J 9 2 將此公式向下角復制到L13 EXCEL SUM函數(shù)中引用問題 如果參數(shù)為數(shù)組或引用 那么只有其中的數(shù)字被計算 數(shù)組或引用中的空白單元格 邏輯值 文本或錯誤值將被忽略 例如 設A1的值為 9 A2為true 則公式SUM A1 A2 20 的計算結果為20 而不是30 因為本公式中包括兩個引用A1 A2 而A1的值為文本 A2的值為邏輯值 它們在計算時被忽略 最終就只有一個數(shù)值20參與運算 EXCEL 函數(shù)之多條件乘積求和函數(shù) SUMPRODUCT array1 array2 array3 Array1 array2 array3 為2到30個數(shù)組 其相應元素需要進行相乘并求和 本身支持數(shù)據(jù)間運算 條件 求和區(qū)域 都是一維數(shù)組 必須是單行 列 而不能是多行 列 非數(shù)值型的數(shù)組元素按0處理 各數(shù)組維度必須相同 否則返回錯誤 區(qū)域計算要求 計算A B C三列對應數(shù)據(jù)乘積的和 公式 SUMPRODUCT A2 A6 B2 B6 C2 C6 計算方式 A2 B2 C2 A3 B3 C3 A4 B4 C4 A5 B5 C5 A6 B6 C6即三個區(qū)域A2 A6 B2 B6 C2 C6同行數(shù)據(jù)積的和 用于多條件求和 對于計算符合某一個條件的數(shù)據(jù)求和 可以用SUMIF函數(shù)來解決 如果要計算符合2個以上條件的數(shù)據(jù)求和 用SUMIF函數(shù)就不能夠完成了 這就可以用函數(shù)SUMPRODUCT Sumproduct A2 A8 E2 B2 8 F2 C2 C8 Sumproduct A2 A8 E2 B2 8 F2 C2 C8 條件間的相隔符 星號 可以理解為 并且 既N個條件都符合的情況下 對求和區(qū)域進行累加 按此推理 也可以求 或者 的情況 函數(shù)中用 表示 或者 或者 的邏輯運算次序要低于 并且 請留意此點 EXCEL 函數(shù)之多條件乘積求和函數(shù) 條件1 條件2 求和區(qū)域 條件一返回結果 0 0 1 0 1 0 1 條件二返回結果 1 1 0 0 0 0 1 相乘 相乘的結果為 0 0 0 0 0 0 1 求和區(qū)域 1200 800 1500 250 520 400 800 0 0 0 0 0 0 800 EXCEL 函數(shù)之多條件乘積求和函數(shù) 條件1 條件2 求和區(qū)域 0 0 0 0 0 0 800 條件1 條件2 求和區(qū)域相乘后的結果 最后求和 SUM 0 0 0 0 0 0 800 800 EXCEL 函數(shù)之多條件乘積求和函數(shù) Sumproduct 條件1 條件2 條件1結果 0 1 0 1 1 0 1 1 0 1 條件2結果 0 1 0 0 1 1 1 0 1 0 相乘后結果 0 1 0 0 1 0 1 0 0 0 并且 同時為1時才是1 其它都是0或者 同時為0時才是0 其它都是1 最后求和 Sum 0 1 0 0 1 0 1 0 0 0 3 少了 求和區(qū)域 EXCEL 函數(shù)之多條件乘積求和函數(shù) EXCEL 函數(shù)之分類匯總求和函數(shù) SUBTOTAL function num ref1 ref2 Function num為1到11 包含隱藏值 或101到111 忽略隱藏值 之間的數(shù)字 指定使用何種函數(shù)在列表中進行分類匯總計算 ref1 refn參數(shù)為要對其進行分類匯總計算的第1至29個命名區(qū)域或引用 必須是對單元格區(qū)域的引用 一般使用該類 EXCEL 函數(shù)之分類匯總求和函數(shù) 1 主要用途 對有篩選的數(shù)據(jù)區(qū)域進行求和 求個數(shù) 求最大值 求最小值 求平均值等 篩選后 求和函數(shù)的差別 Sum函數(shù)在求和時 永遠只能求整個區(qū)域的值 遇到有篩選時 缺陷就出現(xiàn) 此時 需要用Subtotal函數(shù)替代 才可避免求和錯誤 其它求個數(shù) 最大值 最小值等 同樣道理 EXCEL 平均值函數(shù) 平均值函數(shù)AVERAGE AVERAGEAAVERAGE n1 n2 n255 AVERAGEA n1 n2 n255 其中 n1 n2 n255是要計算平均值的參數(shù) 該函數(shù)最多允許有255個參數(shù) 參數(shù)可以是數(shù)字 或者是涉及數(shù)字的名稱 數(shù)組或引用 兩函數(shù)都是求參數(shù)的平均值 AverAge不對文本和邏輯值類數(shù)據(jù)進行計算 AverageA要對文本和邏輯函數(shù)進行平均值計算 文本被視為0 邏輯值true被視為1 false被視為0 空文本 也作為0計算 EXCEL 條件平均值函數(shù) EXCEL2007提供以下兩個帶條件求平均值函數(shù) AVERAGEIF average range range1 criteria1 AVERAGEIFS average range range1 criteria1 range2 criteria2 其中average range是要計算平均值的一個或多個單元格 其中包括數(shù)字或包含數(shù)字的名稱 數(shù)組或引用 range1 range2 是計算關聯(lián)條件的1至127個區(qū)域 Criteria1是用于range1的條件 criteria2是用于range2的條件 它可以是數(shù)字 表達式 單元格引用或文本形式的1至127個條件 用于定義要對哪些單元格求平均值 EXCEL 日期函數(shù) TODAY 返回當前系統(tǒng)設定的日期 DATEDIF start date end date unit Start date為一個日期 它代表時間段內的第一個日期或起始日期 End date為一個日期 它代表時間段內的最后一個日期或結束日期 Unit為所需信息的返回類型 注 結束日期必須大于起始日期可以應用于交貨期 工作進度等計算 EXCEL 邏輯函數(shù) IF函數(shù) IF Logical test Value if true Value if false AND函數(shù) AND Logical1 Logical2 OR函數(shù) OR Logical1 Logical2 NOT函數(shù) NOT Logical EXCEL 查找函數(shù)LOOKUP Lookup函數(shù)向量型查找 在一列或一行中查找某個值 語法 lookup value lookup vector result vector Lookup函數(shù)數(shù)組型查找語法 lookup lookup value array Lookup函數(shù)與其它查找函數(shù)相比特點有 1 lookup函數(shù)沒有精確匹配 如果沒有相等的值則取小于look value最大的值 2 vlookup或hlookup可以指定列或行取值 而lookup第二個語法總是選擇行或列的最后一個數(shù)值 3 向量型查找時自動忽略錯誤值 4 lookup函數(shù)必須look vector或array按升序排序 5 look vector和result vector范圍必須一致 EXCEL LOOKUP的幾種經典應用 利用特性二查找 查找A列中最后一個數(shù)值 LOOKUP 9E307 A A 利用特性三查找 查找E2 E13區(qū)域中與A列相應單元格匹配的值C2 IF ISERROR LOOKUP 1 0 FIND A2 E 2 E 13 E 2 E 13 TRUE LOOKUP 1 0 FIND A2 E 2 E 13 E 2 E 13 EXCEL 查找函數(shù)LOOKUP LOOKUP函數(shù)也可以利用數(shù)組的運算進行查找 LOOKUP函數(shù)的最大局限在于不能精確查找和匹配 EXCEL 查找函數(shù)LOOKUP LOOKUP函數(shù)也可以利用數(shù)組的運算進行反向查找 EXCEL 查找函數(shù)LOOKUP 最常用途 對評分結果進行多級別的分類 在供應商評價 員工評價工作中經常使用 較之一般的IF函數(shù)簡潔明了 且效率高 條件范圍 函數(shù)運算時 檢驗目標值A2是否處在X的各個范圍 是返回1 否返回0 形成一個數(shù)組 EXCEL 查找函數(shù)VLOOKUP Vlookup lookup value table array col index num range lookup Lookup value 條件值 是值 非區(qū)域 比如A1 而非A1 A100 即使A1 A100 也只選擇左上角值A1Table array 搜索的區(qū)域 范圍 是一個多維數(shù)組 如A1 D100之類Col index num 需要返回的值所在列 從搜索范圍的起始列開始算Range lookup 匹配方式 一般使用精確匹配 即0或FALSE例子 1 Vlookup D2 A1 C100 2 0 2 Vlookup 848K15293 A1 C100 2 0 3 Vlookup D2 E2 A1 C100 2 0 解釋 1 根據(jù)D2單元格的值 在A1 C100中搜索 并返回從A列算起 第2列 即B列 的值 2 根據(jù) 848K15293 在A1 C100中搜索 并返回從A列算起 第2列 即B列 的值 3 根據(jù)D2與E2單元格合并后的值 在A1 C100中搜索 并返回從A列算起 第2列 即B列 的值 如果在查找區(qū)域table array含有多個與lookup value相等的值 即含有重復項 只能返回首個lookup value值所對應的結果如果table array中沒有與lookup value相等的值 函數(shù)將返回錯誤值 N A EXCEL VLOOKUP函數(shù)中通配符的使用 E3 VLOOKUP E3 A 3 B 10 2 0 還有SearchCountifHlookupMatchSumif函數(shù)也支持通配符 EXCEL 數(shù)組區(qū)域位置轉換 正常情況下 VLOOKUP函數(shù)只能從左往右查找 要實現(xiàn)從右往左查找 需要通過一定的方法重新構建第二個參數(shù) 查找范圍 的區(qū)域 公式中CHOOSE 2 1 B 2 B 11 C 2 C 11 是最關鍵的組成部分 CHOOSE中使用了數(shù)組 返回的結果也會是一個數(shù)組 在此用2和1把CHOOSE中的區(qū)域1 B 2 B 11 區(qū)域2 C 2 C 11 進行了位置交換 最終形成了 457橋組合螺母 31030704E 車架總成 TU3421WW280 這樣一個區(qū)域 EXCEL 輔助列和COLUMN函數(shù)的使用 F2 IF ISERR VLOOKUP E2 COLUMN A1 A C 3 0 TRUE VLOOKUP E2 COLUMN A1 A C 3 0 EXCEL 查找函數(shù)MATCH Match lookup value lookup array match type Lookup value 是值 非區(qū)域 例如A1 而非A1 A8 根據(jù)這個值 進行逐一比對 Lookup array 是一個區(qū)域 一維數(shù)組 只能是一行或一列 不能多行多列 例如 A1 A100 或A1 G1 而不能A1 D10Match type 匹配方式 精確匹配 模糊匹配 一般使用精確匹配 用數(shù)值0表示 函數(shù)解釋 在lookup array中 查找與lookup value相符的值 并返回它所在的行 列數(shù) 如果lookup array存在兩個或以上的lookup value值 函數(shù)只會返回首個lookup value的所在行 列數(shù) MATCH函數(shù)用于在指定區(qū)域內按指定方式查詢與指定內容所匹配的單元格位置 LOOKUP函數(shù)用于在指定區(qū)域內查詢指定內容多對應的匹配區(qū)域內單元格的內容 EXCEL 查找與引用函數(shù)OFFSET OFFSET reference rows cols height width OFFSET 參考單元格 移動的行數(shù) 移動的列數(shù) 所要引用的行數(shù) 所要引用的列數(shù) EXCEL 統(tǒng)計個數(shù)的函數(shù) COUNT x1 x2 x30 COUNTA x1 x2 x30 COUNTBLANK range Count統(tǒng)計數(shù)字的個數(shù)CountA統(tǒng)計數(shù)字和文本的個數(shù)CountBlank統(tǒng)計空白單元格的個數(shù) EXCEL 統(tǒng)計個數(shù)的函數(shù) EXCEL 統(tǒng)計個數(shù)的函數(shù) COUNTIF函數(shù)用法 COUNTIF range criteria 功能 統(tǒng)計出range中滿足條件criteria的數(shù)據(jù)個數(shù) Excel2007還提供了一個多條件統(tǒng)計函數(shù)COUNTIFS 它可以一次對多個不同區(qū)域進行不同條件的計數(shù) 其用法如下 COUNTIF range1 criteria1 range2 criteria2 range127 criteria127 EXCEL 統(tǒng)計個數(shù)的函數(shù) 有學生成績表如圖所示 統(tǒng)計其中總分220分以下的人數(shù) 統(tǒng)計總分200分以上 物理80分以下 地理70分以下 化學65分以下的人數(shù) 統(tǒng)計姓王的同學人數(shù) EXCEL 函數(shù)之統(tǒng)計函數(shù) RANK number ref order 函數(shù)返回一個數(shù)字在一組數(shù)中的排位 即位次LARGE array k 函數(shù)返回數(shù)據(jù)集中第k個最大值SMALL array k 返回數(shù)據(jù)集中第k個最小值MEDIAN number1 number2 函數(shù)計算出給定數(shù)據(jù)的中值MODE number1 number2 函數(shù)返回一組數(shù)中出現(xiàn)次數(shù)最多的數(shù) EXCEL 函數(shù)之統(tǒng)計函數(shù) 有學生成績表如圖中A1 F10區(qū)域所示 計算各學生成績的名稱 百分比排位 第3名和倒數(shù)第3名總分 中間成績 出現(xiàn)次數(shù)最多的分數(shù)及在38 總分位置的分數(shù)大致是多少等數(shù)據(jù) 在G3中輸入公式 RANK F3 F 3 F 10 向下復制此公式可計算名次 在H3中輸入公式 PERCENTRANK F 3 F 10 F3 2 向下復制此公式可計算名次 LARGE F3 F10 3 SMALL F3 F10 3 MEDIAN F3 F10 MODE C3 E10 PERCENTILE F3 F10 0 38 EXCEL 常用文本函數(shù) 比較字符串EXACT Text1 Text2 刪除文本中的空格TRIM Text 計算字符串長度LEN Text 查找字符串FIND Find text Within text Start num SEARCH Find text Within text Start num 從文本中取子串LEFT Text Num chars RIGHT Text Num chars MID Text Start num Num chars 替換文本REPLACE Old text Start num Num chars New text 數(shù)字轉換為文本TEXT Value Format text EXCEL 排序 排序 使工作表中數(shù)據(jù)按照某一列或某些列內容的順序來排