Excel 函數(shù)×函數(shù)組合應(yīng)用范例

《Office 2010圖解應(yīng)用大全》張玉良，中國人民大學(xué)新聞學(xué)院畢業(yè)，具有豐富的出版經(jīng)驗(yàn)，工作期間也一直在提供各種Excel技術(shù)咨詢服務(wù)和培訓(xùn)，并在各大論壇為網(wǎng)友解答辦公軟件困惑。2010年下半年創(chuàng)辦知行時(shí)代（北京）文化傳播有限公司，從事圖書的策劃與編寫。近期出版圖書：《Office辦公秘技360招》、《這么玩Excel最給力》、《Office 2010圖解應(yīng)用大全》均有不錯(cuò)的銷量

目 錄

第 1 章 基本操作

1.1 創(chuàng)建與修改公式··············································································2

1.1.1 創(chuàng)建公式················································································ 2

1.1.2 修改公式················································································3

1.2 復(fù)制與隱藏公式··············································································4

1.2.1 復(fù)制公式················································································ 4

1.2.2 隱藏公式················································································ 6

1.3 單元格的引用···············································································7

1.3.1 相對(duì)引用················································································ 7

1.3.2 絕對(duì)引用················································································ 8

1.3.3 混合引用················································································ 9

1.4 應(yīng)用單個(gè)函數(shù)···············································································11

1.4.1 初識(shí)函數(shù)················································································11

1.4.2 插入函數(shù)················································································12

1.5 應(yīng)用多個(gè)函數(shù)···············································································15

1.5.1 嵌套函數(shù)················································································15

1.5.2 組合應(yīng)用函數(shù)··············································································18

1.6 公式錯(cuò)誤的解析··············································································19

1.6.1 錯(cuò)誤值的含義··············································································19

1.6.2 應(yīng)用錯(cuò)誤檢查選項(xiàng)············································································20

1.6.3 公式審核················································································21

第 2 章 輸入與條件格式

001 限定輸入指定范圍的日期·······································································24

應(yīng)用 限定輸入今年元旦至今日的日期 （TODAY 數(shù)據(jù)有效性）

002 限定輸入文本長度且以特定字符開頭···················································27

應(yīng)用 限定產(chǎn)品編號(hào)以“D”開頭且共4位 （LEFT LEN AND 數(shù)據(jù)有效性）

003 避免一列中輸入重復(fù)數(shù)據(jù)·······································································30

應(yīng)用 在員工信息表中輸入重復(fù)工號(hào)時(shí)彈出警告 （COUNTIF 數(shù)據(jù)有效性）

004 生成動(dòng)態(tài)下拉列表進(jìn)行輸入···································································33

應(yīng)用 輸入部門名稱時(shí)通過下拉列表選擇部門 （INDIRECT 數(shù)據(jù)有效性）

005 提示輸入內(nèi)容且輸入正確時(shí)自動(dòng)計(jì)算···················································36

應(yīng)用 提示輸入數(shù)量并自動(dòng)計(jì)算總額 （IF AND ISNONTEXT）

006 給奇數(shù)、偶數(shù)行設(shè)置不同的格式···························································39

應(yīng)用 讓表格內(nèi)容藍(lán)白相間顯示 （MOD ROW 條件格式）

007 設(shè)置工作表中非空單元格格式·······························································42

應(yīng)用 制作成本表后僅顯示表格區(qū)域 （條件公式 條件格式）

008 屏蔽錯(cuò)誤值················································································45

應(yīng)用 將錯(cuò)誤值顯示為空白 （ISERROR 條件格式）

009 比較兩個(gè)工作表檢查輸入錯(cuò)誤·······························································48

應(yīng)用 檢查在兩個(gè)工作表中輸入的公司名稱是否相同 （EXACT IF）

拓展 常用同類函數(shù)···············································································51

第 3 章 統(tǒng)計(jì)分析

010 計(jì)算多個(gè)匯總值的總和···································································2100433B

這書的基礎(chǔ)是一本臺(tái)灣引進(jìn)的日本書，在臺(tái)灣排在網(wǎng)絡(luò)銷售的前列，找到這本書時(shí)，大約排在第12位。本書主要針對(duì)有一定的Excel操作基礎(chǔ)，同時(shí)需要利用多個(gè)函數(shù)解決實(shí)際工作問題的讀者，可以作為常備的參考工具書

詳細(xì)說明函數(shù)的基本概念、單元格與名稱定義、函數(shù)與公式建立，詳細(xì)介紹每個(gè)函數(shù)的參數(shù)用法、數(shù)據(jù)類型與限制，學(xué)習(xí)對(duì)話框與窗體的制作，建立更為彈性的運(yùn)算模式，范例導(dǎo)向精辟解說如何使用函數(shù)式宏建立“自定義函數(shù)”，每章提供函數(shù)速查表，方便讀者查詢，快速上手。匯集334個(gè)工作表函數(shù)，223個(gè)實(shí)用案例，完全適用Excel2007。

《Excel函數(shù)范例完整學(xué)習(xí)應(yīng)用大全》針對(duì)函數(shù)中的自變量，與其所需的數(shù)據(jù)類型與限制詳加說明。同時(shí)附有范例光盤提供完整的操作范例文件，使讀者在閱讀函數(shù)后馬上可以在計(jì)算機(jī)上得到驗(yàn)證。

《Excel函數(shù)范例完整學(xué)習(xí)應(yīng)用大全》共收錄Excel中339個(gè)常用的工作表函數(shù)，共分14章，第、章主要介紹Excel。

本書以技巧的形式，介紹如何快速將自己打造成Excel公式與函數(shù)應(yīng)用高手。全書共10章，分別介紹公式編輯與數(shù)據(jù)源引用技巧、邏輯函數(shù)范例應(yīng)用技巧、日期函數(shù)范例應(yīng)用技巧、數(shù)學(xué)函數(shù)范例應(yīng)用技巧、文本函數(shù)范例應(yīng)用技巧、統(tǒng)計(jì)函數(shù)范例應(yīng)用技巧、財(cái)務(wù)函數(shù)范例應(yīng)用技巧、查找和引用函數(shù)范例應(yīng)用技巧、數(shù)據(jù)庫函數(shù)范例應(yīng)用技巧，以及函數(shù)返回錯(cuò)誤值的解決方法等方面的內(nèi)容。

本書有很強(qiáng)的實(shí)用性和可操作性，非常適合經(jīng)常使用Excel函數(shù)的讀者隨時(shí)查閱。