EXCEL應(yīng)用及技巧.ppt

1、2020/7/29,讓工作更輕松,EXCEL應(yīng)用及技巧,2020/7/29,工作即是娛樂(lè),徐豪杰,商 用 銷(xiāo) 售 部,2020/7/29,工作即是娛樂(lè),前言,在進(jìn)入正題之前，先輕松一下，聽(tīng)一個(gè)真實(shí)的故事：有一個(gè)老會(huì)計(jì)，一直都是手工做賬、手工制表，有一天公司終于給財(cái)務(wù)部配上了電腦，老會(huì)計(jì)也學(xué)會(huì)了用Excel電子表格工具制表，用上Excel制表后，老會(huì)計(jì)感慨：“Excel電子表格真是好，以前制一張表格要?jiǎng)澃胩?，劃錯(cuò)了一個(gè)地方還很不好修改，現(xiàn)在方便多了”。,2020/7/29,工作即是娛樂(lè),前言,有一天，他發(fā)現(xiàn)他的下屬制表從來(lái)不去一一加減數(shù)字，老會(huì)計(jì)每次復(fù)核時(shí)用算盤(pán)敲半天，都沒(méi)發(fā)現(xiàn)其計(jì)算錯(cuò)誤，問(wèn)其原

2、因才發(fā)現(xiàn)Excel還有自動(dòng)求和的功能，不禁驚嘆：“??？！Excel還有這個(gè)功能？！我每次都用算盤(pán)敲出結(jié)果再填上去！”聽(tīng)完這個(gè)故事你是否會(huì)心一笑？你是否也曾驚呼：??？！Excel還有這個(gè)功能？！,2020/7/29,工作即是娛樂(lè),前言,Excel有太多的功能未曾為我們所認(rèn)識(shí)，更未曾被我們使用，我們已學(xué)會(huì)使用的功能也許不到Excel功能的10%。在EXCEL中可以這么說(shuō),”只有想不到,沒(méi)有做不到.”當(dāng)然這樣說(shuō)是太絕對(duì)啦,但99%的事,只要你敢想,就可能完成.,2020/7/29,工作即是娛樂(lè),前言,本次講課，不是引導(dǎo)你系統(tǒng)地學(xué)習(xí)Excel，僅是只鱗片甲、浮光掠影地介紹一些常用的功能或非常實(shí)用的技巧

3、。只要你每天學(xué)習(xí)一點(diǎn)點(diǎn)，就可以更快地掌握Excel的基本功能和學(xué)到不少非常實(shí)用的技巧。要想系統(tǒng)地學(xué)習(xí)Excel，本人認(rèn)為最好的方法就是：沒(méi)事就多看看Excel的聯(lián)機(jī)幫助、多作作練習(xí)題.,2020/7/29,工作即是娛樂(lè),EXCEL操作中的12大不良習(xí)慣,1、報(bào)表文件名稱(chēng)、報(bào)表題目、表頭 不完整或語(yǔ)意不確切,2、報(bào)表布局、數(shù)據(jù)項(xiàng)目順序不合理； 鉤稽關(guān)系不明確甚至錯(cuò)誤；,3、數(shù)據(jù)缺失、漏填漏報(bào)、項(xiàng)目用字 每次不能保持一致；,不良習(xí)慣,2020/7/29,工作即是娛樂(lè),EXCEL操作中的12大不良習(xí)慣,4、為了美化格式或由于輸入手誤， 字串中間、前后使用空格；,5、為求得格式好看，大量使用“合并 居

4、中”功能，結(jié)果可能是“以形害質(zhì)”；,6、不論是文字?jǐn)?shù)據(jù)還是數(shù)字?jǐn)?shù)據(jù)一 概“居中對(duì)齊”；,不良習(xí)慣,2020/7/29,工作即是娛樂(lè),EXCEL操作中的12大不良習(xí)慣,7、文字和數(shù)字的字體、大小和格式 不合適、不統(tǒng)一；,8、行列的寬、高不統(tǒng)一、不適合， 或是過(guò)寬，或是過(guò)窄，以至數(shù)字不 能正確顯示；,9、字體或行列使用多種顏色， 且搭配失當(dāng)、斑駁雜亂；,不良習(xí)慣,2020/7/29,工作即是娛樂(lè),EXCEL操作中的12大不良習(xí)慣,10、時(shí)間項(xiàng)目不按照EXCEL系統(tǒng)的 標(biāo)準(zhǔn)格式填寫(xiě)，且未能保持統(tǒng)一；,11、標(biāo)點(diǎn)和符號(hào)全角、半角不加區(qū) 分混合使用（如左右括號(hào)的輸入； “-”、“”、“”不分）；,12、

5、公式計(jì)算結(jié)果如為錯(cuò)誤值符號(hào)， 一概保留，不加以清除；使用鏈接 公式后，不斷開(kāi)鏈接將其轉(zhuǎn)換成數(shù)值,不良習(xí)慣,2020/7/29,工作即是娛樂(lè),EXCEL 操作分類(lèi),基 礎(chǔ) 操 作,函 數(shù) 操 作,圖 表 操 作,VBA 編 程,2020/7/29,工作即是娛樂(lè),基 礎(chǔ) 操 作,基礎(chǔ),單元格格式,復(fù)制類(lèi),定 義,分類(lèi)匯總,條件格式,數(shù)據(jù)透視表,排 序,選 項(xiàng) 應(yīng) 用,數(shù)據(jù)有效性,高 級(jí) 篩 選,2020/7/29,工作即是娛樂(lè),快捷鍵,真正的Excel高手不只是擅長(zhǎng)函數(shù)、數(shù)組和VBA，也擅長(zhǎng)于鍵盤(pán)和鼠標(biāo)操作，因?yàn)檫@是基本功。善用快捷鍵和鼠標(biāo)不但可以大大提高操作效率，還可以唬到Excel菜鳥(niǎo)，菜鳥(niǎo)一

6、見(jiàn)你運(yùn)指如飛，鼠標(biāo)狂點(diǎn)，三兩下就完成了他要五六下才能搞定的作業(yè)，恨不得拜你為師，夢(mèng)想著成為Excel高手。不用數(shù)組、VBA這等高精尖武器就可騙到一個(gè)徒弟，何樂(lè)而不為呢，哈哈0。學(xué)習(xí)Excel，就從如何最大限度地發(fā)揮鍵盤(pán)和鼠標(biāo)的作用開(kāi)始。,2020/7/29,工作即是娛樂(lè),鍵盤(pán)高手使用快捷鍵,“懶人用鼠標(biāo)，高手用鍵盤(pán)”,大凡高手從來(lái)不依賴(lài)鼠標(biāo),他們更偏愛(ài)鍵盤(pán)。在輸入數(shù)據(jù)時(shí)盡量少用鼠標(biāo)多用鍵盤(pán)快捷鍵將提高你的工作效率。,Ctrl+箭頭鍵 移動(dòng)到當(dāng)前數(shù)據(jù)區(qū)域的邊緣 Ctrl+Home移動(dòng)到工作表的開(kāi)頭 Alt + =（等號(hào)） 用 SUM 函數(shù)插入“自動(dòng)求和”公式 F4 或 Ctrl+Y 重復(fù)上一次操

7、作,2020/7/29,工作即是娛樂(lè),懶人懶到底使用鼠標(biāo),什么？“懶人用鼠標(biāo)，高手用鍵盤(pán)”？讓那些高手用鍵盤(pán)去吧，懶人的口號(hào)是：要懶就懶到底，能用鼠標(biāo)的地方就不用鍵盤(pán)。但是，懶也要懶得專(zhuān)業(yè)，懶得有技術(shù)含量，懶就要懶出境界來(lái)。以下是使用鼠標(biāo)的常用技能：,設(shè)定合適行高列寬 如果某單元格未完全顯示，雙擊該列列標(biāo)題的右邊界，可以設(shè)置為“最適合的列寬”。同理，雙擊某行行標(biāo)題的下邊界，可將此行設(shè)置為“最適合的行高”。,2020/7/29,工作即是娛樂(lè),懶人懶到底使用鼠標(biāo),2020/7/29,工作即是娛樂(lè),雙劍合璧鼠標(biāo)鍵盤(pán)組合,2020/7/29,工作即是娛樂(lè),雙劍合璧鼠標(biāo)鍵盤(pán)組合,2020/7/29,工作

8、即是娛樂(lè),數(shù)據(jù)輸入技巧,2020/7/29,工作即是娛樂(lè),復(fù)制類(lèi)操作,復(fù)制操作,CTRL+C與CTRL+V,CTRL+R與CTRL+D,CTRL+回車(chē),左鍵下拉與右鍵下拉,選擇性粘貼,2020/7/29,工作即是娛樂(lè),選擇性粘貼,選擇性粘貼的功能很強(qiáng)大,對(duì)于我們實(shí)際工作中的幫助也很大,主要功能除了數(shù)值與格式的復(fù)制外,還能進(jìn)行對(duì)公式,超鏈接等的取消,格式的轉(zhuǎn)換,數(shù)據(jù)的行列轉(zhuǎn)換,跳行計(jì)算,原始行列計(jì)算等等,2020/7/29,工作即是娛樂(lè),定位,定位就是Excel替你選定符合你所設(shè)定位條件的單元格。此功能的快捷捷為Ctrl+G，打開(kāi)定位對(duì)話(huà)框后單擊“定位條件”按鈕可選擇包含特殊字符或條目的對(duì)象、批

9、注或單元格。這是一個(gè)很實(shí)用的功能，大家一般都不太用，即使是經(jīng)常使用Excel的朋友也不常用。下面介紹一下它的一些常用選項(xiàng)：,2020/7/29,工作即是娛樂(lè),定義名稱(chēng),名稱(chēng)的定義是EXCEL的一基礎(chǔ)的技能，可是，如果你掌握了，它將給你帶來(lái)非常實(shí)惠的妙處！ 建議使用簡(jiǎn)單易記的名稱(chēng)，不可使用類(lèi)似A1的名稱(chēng)，因?yàn)樗鼤?huì)和單元格的引用混淆。還有很多無(wú)效的名稱(chēng)，系統(tǒng)會(huì)自動(dòng)提示你。 引用位置：可以是工作表中的任意單元格，可以是公式，也可以是文本。,2020/7/29,工作即是娛樂(lè),定義名稱(chēng)的妙處,2020/7/29,工作即是娛樂(lè),定義名稱(chēng)的妙處,2020/7/29,工作即是娛樂(lè),排序,超過(guò)三字段排序 按行排

10、序 按顏色排序 按特定順序排序 按筆劃排序 隨機(jī)排序,2020/7/29,工作即是娛樂(lè),高級(jí)篩選,大家對(duì)于自動(dòng)篩選基本都很非常熟悉,而對(duì)于高級(jí)篩選可能就比較陌生或很少用,其實(shí)高級(jí)篩選對(duì)于數(shù)據(jù)的篩選和數(shù)據(jù)導(dǎo)出的功能很實(shí)用,可以完成較復(fù)雜的數(shù)據(jù)篩選,2020/7/29,工作即是娛樂(lè),奇妙的自定義格式,通過(guò)單元格格式設(shè)置我們可以對(duì)單元格的格式進(jìn)行改變，達(dá)到我們所希望的格式。 數(shù)字格式是單元格格式中最有用的功能之一，專(zhuān)門(mén)對(duì)單元格數(shù)值進(jìn)行格式化。 無(wú)論為單元格應(yīng)用了何種數(shù)字格式，都只會(huì)改變單元格的顯示形式，而不會(huì)改變單元存儲(chǔ)的真正內(nèi)容。反之，用戶(hù)在工作表上看到的單元格內(nèi)容，并不一定是其真正的內(nèi)容，而可能

11、是原始內(nèi)容經(jīng)過(guò)各種變化后的一種表現(xiàn)形式。如果我們需要在改變格式的同時(shí)也改變實(shí)際內(nèi)容，需要借助函數(shù)TEXT等來(lái)實(shí)現(xiàn)。,2020/7/29,工作即是娛樂(lè),條件格式,簡(jiǎn)單概述: 利用EXCEL的條件格式功能,用戶(hù)可以為自己 的數(shù)據(jù)區(qū)域設(shè)置精美而又宜于閱讀的格式.這種設(shè) 置效果不同于單元格格式,它是完全動(dòng)態(tài)的,無(wú)論在 數(shù)據(jù)區(qū)域中增加或刪除行,列,格式都會(huì)自動(dòng)進(jìn)行相 應(yīng)地調(diào)整,操持原有的風(fēng)格. 主要功能: 自動(dòng)標(biāo)色,表格線(xiàn).重要數(shù)據(jù)的提醒,比較等,2020/7/29,工作即是娛樂(lè),神奇的數(shù)據(jù)有效性,概述: 我們常常會(huì)編制很多表格需要下面部門(mén)或其他人來(lái)填寫(xiě)，但報(bào)上來(lái)的表格總不能達(dá)到要求，即使寫(xiě)了很詳細(xì)的說(shuō)

12、明，總是有這么一部分人填寫(xiě)的內(nèi)容出現(xiàn)概念性錯(cuò)誤，也許數(shù)據(jù)有效性就是一種很好控制方法，防止大部分錯(cuò)誤，引導(dǎo)填寫(xiě)部門(mén)按照自己的要求來(lái)填寫(xiě)。 功能： 對(duì)單元格進(jìn)行始初化，并對(duì)單元格的輸入內(nèi)容進(jìn)行限制。簡(jiǎn)化輸入過(guò)程等,2020/7/29,工作即是娛樂(lè),分類(lèi)匯總,主要功能按字段進(jìn)行數(shù)據(jù)的匯總,也就是我們常說(shuō)的小計(jì)與合計(jì),對(duì)于分類(lèi)匯總需要注意的是進(jìn)行分類(lèi)匯總前要對(duì)數(shù)據(jù)源先進(jìn)行排序. EXCEL自帶的組及分級(jí)顯示可做為分類(lèi)匯總的補(bǔ)充對(duì)自動(dòng)分類(lèi)匯總后的數(shù)據(jù)進(jìn)行手工調(diào)整或增加刪除數(shù)據(jù). 二者配合使用可以得到最佳的方案或效果.,2020/7/29,工作即是娛樂(lè),數(shù)據(jù)透視表,數(shù)據(jù)透視表是EXCEL功能為我們提供的現(xiàn)

13、有最強(qiáng)大的數(shù)據(jù)匯總,分析工具,通過(guò)數(shù)據(jù)透視表我們可以輕松進(jìn)行各種數(shù)據(jù)的統(tǒng)計(jì)分析,其附帶的分析圖對(duì)數(shù)據(jù)的對(duì)比更是一目了然,而且其計(jì)算速度非常的快,基本無(wú)延遲.對(duì)其操作不會(huì)對(duì)數(shù)據(jù)源造成影響. 建議大家應(yīng)能熟練應(yīng)用,這對(duì)大家平時(shí)的工作會(huì)有很多的幫助.,2020/7/29,工作即是娛樂(lè),控件基礎(chǔ)知識(shí),我們通過(guò)對(duì)控件(在EXCEL工作表中實(shí)際叫窗體)的操作,達(dá)到對(duì)公式或其它項(xiàng)目的更改,比如同個(gè)條件,不同標(biāo)的項(xiàng)目進(jìn)行顯示等,2020/7/29,工作即是娛樂(lè),選項(xiàng)的應(yīng)用,2020/7/29,工作即是娛樂(lè),函 數(shù) 操 作,普通公式,數(shù)組公式,易失性函數(shù),分類(lèi) 日 期 函 數(shù) 信 息 函 數(shù) 邏 輯 函 數(shù) 查

14、 找 函 數(shù) 統(tǒng) 計(jì) 函 數(shù),分類(lèi) 文 本 函 數(shù) 數(shù) 據(jù) 函 數(shù) 工 程 函 數(shù) 財(cái) 務(wù) 函 數(shù) 數(shù) 學(xué) 函 數(shù),函數(shù)分類(lèi),多條件匯總,2020/7/29,工作即是娛樂(lè),函數(shù)基礎(chǔ)知識(shí),公式是對(duì)工作表中數(shù)值執(zhí)行計(jì)算的等式,函數(shù)則是一些預(yù)先編寫(xiě)的,按特定順序或結(jié)構(gòu)執(zhí)行計(jì)算的特殊公式.根據(jù)應(yīng)用領(lǐng)域的不同,EXCEL函數(shù)一般可分為:邏輯,信息,日期,時(shí)間,數(shù)學(xué)與三角計(jì)算,統(tǒng)計(jì)查找與引用,數(shù)據(jù)庫(kù),文本,財(cái)務(wù),工程等類(lèi)別,此外還有EXCEL4.0宏表函數(shù),擴(kuò)展函數(shù)和外部函數(shù)等.,2020/7/29,工作即是娛樂(lè),函數(shù)基礎(chǔ)知識(shí),許多朋友在碰到較復(fù)雜的函數(shù)公式,尤其是函數(shù)嵌套公式時(shí),往往不知從何讀起,從何理

15、解,其實(shí)只要掌握了函數(shù)公式的結(jié)構(gòu)等基本知識(shí),就可以像”庖丁解?！币粯影压竭M(jìn)行分段解讀(“F9鍵”公式分段計(jì)算鍵) 函數(shù)公式是以”=“號(hào)開(kāi)始,后面依次是函數(shù)名稱(chēng),左括號(hào),以半角逗號(hào)分隔的參數(shù)和右括號(hào) =IF(A10,SUM(B1:G1),”),2020/7/29,工作即是娛樂(lè),函數(shù)基礎(chǔ)知識(shí),函數(shù)的參數(shù)可以是數(shù)字,文本,邏輯值數(shù)組,錯(cuò)誤值或單元格引用,指定的參數(shù)都必須為有效的參數(shù).參數(shù)也可以是常量,公式或其他函數(shù).有些函數(shù)不帶參數(shù),比如NOW(),TODAY(),RNGD()等.當(dāng)函數(shù)的參數(shù)也是函數(shù)時(shí),我們稱(chēng)之為函數(shù)的嵌套. =IF(A10,SUM(B1:G1),”),2020/7/29,工作即

16、是娛樂(lè),函數(shù)基礎(chǔ)知識(shí),理解技巧: 在函數(shù)公式中,最左邊的等號(hào)使表達(dá)式成為公式.括號(hào)用于包含參數(shù),括號(hào)的配對(duì)讓一個(gè)函數(shù)成為完整個(gè)體.參數(shù)以逗號(hào)來(lái)分隔,逗號(hào)的分隔讓每個(gè)參數(shù)成為完整個(gè)體.逗號(hào)就像是函數(shù)公式的關(guān)節(jié).由此入手,就可以清楚地解讀公式啦,當(dāng)然使用F9理解公式是必不可少的.,2020/7/29,工作即是娛樂(lè),=IF(ROUND(A11,2)0,無(wú)效數(shù)值,IF(ROUND(A11,2)=0,零,IF(ROUND(A11,2)1,TEXT(INT(ROUND(A11,2),dbnum2)&元)&IF(INT(ROUND(A11,2)*10)-INT(ROUND(A11,2)*10=0,IF(IN

17、T(ROUND(A11,2)*(INT(ROUND(A11,2)*100)-INT(ROUND(A11,2)*10)*10)=0,零),TEXT(INT(ROUND(A11,2)*10)-INT(ROUND(A11,2)*10,dbnum2)&角)&IF(INT(ROUND(A11,2)*100)-INT(ROUND(A11,2)*10)*10)=0,整,TEXT(INT(ROUND(A11,2)*100)-INT(ROUND(A11,2)*10)*10),dbnum2)&分),來(lái)個(gè)復(fù)雜點(diǎn)函數(shù),2020/7/29,工作即是娛樂(lè),如何避免錯(cuò)誤信息,在Excel中輸入公式后，有時(shí)不能正確地計(jì)算出結(jié)

18、果，并在單元格內(nèi)顯示一個(gè)錯(cuò)誤信息，這些錯(cuò)誤的產(chǎn)生，有的是因公式本身產(chǎn)生的，有的不是。下面就介紹一下幾種常見(jiàn)的錯(cuò)誤信息，并提出避免出錯(cuò)的辦法。,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值類(lèi)型,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值：,輸入到單元格中的數(shù)據(jù)太長(zhǎng)或單元格公式所產(chǎn)生的結(jié)果太大，使結(jié)果在單元格中顯示不下?；蚴侨掌诤蜁r(shí)間格式的單元格做減法，出現(xiàn)了負(fù)值。,增加列的寬度，使結(jié)果能夠完全顯示。如果是由日期或時(shí)間相減產(chǎn)生了負(fù)值引起的，可以改變單元格的格式，比如改為文本格式，結(jié)果為負(fù)的時(shí)間量,含義：,辦法：,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值： DIV/0!,試圖除以0。這個(gè)錯(cuò)誤的產(chǎn)生通常有下

19、面幾種情況：除數(shù)為0、在公式中除數(shù)使用了空單元格或是包含零值單元格的單元格引用,修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值,含義：,辦法：,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值：VALUE!,輸入引用文本項(xiàng)的數(shù)學(xué)公式。如果使用了不正確的參數(shù)或運(yùn)算符，或者當(dāng)執(zhí)行自動(dòng)更正公式功能時(shí)不能更正公式，都將產(chǎn)生錯(cuò)誤信息VALUE!。,這時(shí)應(yīng)確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如，單元格C4中有一個(gè)數(shù)字或邏輯值，而單元格D4包含文本，則在計(jì)算公式=C4D4時(shí)，系統(tǒng)不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類(lèi)型，因而返回錯(cuò)誤值VALUE!。,含義：,辦法：,2020/

20、7/29,工作即是娛樂(lè),錯(cuò)誤值： N/A,無(wú)信息可用于所要執(zhí)行的計(jì)算。在建立模型時(shí)，用戶(hù)可以在單元格中輸入#N/A，以表明正在等待數(shù)據(jù)。任何引用含有#N/A值的單元格都將返回#N/A,在等待數(shù)據(jù)的單元格內(nèi)填充上數(shù)據(jù)。,含義：,辦法：,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值：REF!,刪除了被公式引用的單元格范圍。,恢復(fù)被引用的單元格范圍，或是重新設(shè)定引用范圍。,含義：,辦法：,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值：NAME?,在公式中使用了Excel所不能識(shí)別的文本，比如可能是輸錯(cuò)了名稱(chēng)，或是輸入了一個(gè)已刪除的名稱(chēng)，如果沒(méi)有將文字串括在雙引號(hào)中，也會(huì)產(chǎn)生此錯(cuò)誤值,如果是使用了不存在的名稱(chēng)

21、而產(chǎn)生這類(lèi)錯(cuò)誤，應(yīng)確認(rèn)使用的名稱(chēng)確實(shí)存在；如果是名稱(chēng)，函數(shù)名拼寫(xiě)錯(cuò)誤應(yīng)就改正過(guò)來(lái)；將文字串括在雙引號(hào)中；確認(rèn)公式中使用的所有區(qū)域引用都使用了冒號(hào)（：）。例如：SUM（C1：C10）。 注意將公式中的文本括在雙引號(hào)中,含義：,辦法：,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值： NUM!,提供了無(wú)效的參數(shù)給工作表函數(shù)，或是公式的結(jié)果太大或太小而無(wú)法在工作表中表示。,確認(rèn)函數(shù)中使用的參數(shù)類(lèi)型正確。如果是公式結(jié)果太大或太小，就要修改公式，使其結(jié)果在-110307和110307之間。,含義：,辦法：,2020/7/29,工作即是娛樂(lè),錯(cuò)誤值： NULL！,在公式中的兩個(gè)范圍之間插入一個(gè)空格以表示交叉點(diǎn)，

22、但這兩個(gè)范圍沒(méi)有公共單元格。比如輸入：“=SUM(A1:A10 C1:C10)”，就會(huì)產(chǎn)生這種情況。,解決辦法： 取消兩個(gè)范圍之間的空格。上式可改為“=SUM(A1:A10 ,C1:C10)”,含義：,辦法：,2020/7/29,工作即是娛樂(lè),函 數(shù) 操 作 _ 區(qū) 域 的 引 用,普通引用,普通引用,相對(duì)引用,相對(duì)引用,絕對(duì)引用,絕對(duì)引用,完全絕對(duì)引用,完全絕對(duì)引用,A1,$A1,$A$1,indirect,普通,完全絕對(duì),絕對(duì),相對(duì),2020/7/29,工作即是娛樂(lè),不同引用類(lèi)型的區(qū)別,2020/7/29,工作即是娛樂(lè),快速切換相對(duì)引用,絕對(duì)引用,混合引用,相對(duì),絕對(duì),混合引用大大方便了用

23、戶(hù)復(fù)制公式的操作,不必逐個(gè)輸入公式,一般一個(gè)匯總表格或同一個(gè)要求下的公式我們只需編輯一個(gè)公式,通過(guò)對(duì)引用的控制解決問(wèn)題.但是,使用絕對(duì)引用 和混合引用時(shí)需要手工在相應(yīng)位置輸入美元符號(hào),不太方便,事實(shí)上,EXCEL提供了快捷鍵F4,可在編輯欄對(duì)引用單元格的引用類(lèi)型進(jìn)行快速切換. 如果你能很好的控制單元格的引用,那么你的EXCEL水平就會(huì)有很大的提升.,2020/7/29,工作即是娛樂(lè),邏輯關(guān)系運(yùn)算,在實(shí)際應(yīng)用中,邏輯值可以直接參與函數(shù)公式計(jì)算,通?？吹饺缫韵聦?shí)例的解法. 例1:查找B1在A1:A10數(shù)據(jù)區(qū)域中是否存在的公式為: 例2:統(tǒng)計(jì)A1:A10數(shù)據(jù)區(qū)域中大于零的數(shù)值個(gè)數(shù)的公式 為什么公式1

24、中IF的條件數(shù)據(jù)不用”countif(A1:A10,B1)0” 而公式2中為什么要在sum函數(shù)中使用”*1”呢?,=if(countif(A1:A10,b1),”存在”,”),=sum(A1:A100)*1),2020/7/29,工作即是娛樂(lè),邏輯關(guān)系運(yùn)算(邏輯值與數(shù)值的關(guān)系),在EXCEL中,邏輯值只有TRUE和FALSE兩個(gè),它們與數(shù)值的關(guān)系為: 這兩條準(zhǔn)則非常重要,在EXCEL函數(shù)公式的簡(jiǎn)化及計(jì)算中用途非常廣泛.如果你能很好的處理邏輯值,那么你的EXCEL水平將有質(zhì)的提升.,(1). 在數(shù)值運(yùn)算中,TRUE=1,FALSE=0,(1). 在邏輯判斷中, 0=FALSE,所有非0數(shù)值=TR

25、UE.,2020/7/29,工作即是娛樂(lè),邏輯關(guān)系運(yùn)算,為解釋剛才2個(gè)例子疑惑,先來(lái)了解以下邏輯值之間的運(yùn)算結(jié)果:,2020/7/29,工作即是娛樂(lè),邏輯關(guān)系運(yùn)算,了解了以上的運(yùn)算后再來(lái)看公式1.由于COUNTIF函數(shù)統(tǒng)計(jì)結(jié)果只能為非負(fù)數(shù)(結(jié)果=0),那么如果B1存在,COUNTIF函數(shù)結(jié)果必定0,否則結(jié)果=0,所以就不用在添加”0”進(jìn)行判斷. 而對(duì)于公式2.如果SUM函數(shù)的參數(shù)是數(shù)組,而且這個(gè)數(shù)組是由邏輯值組成,那么要對(duì)這些邏輯值求和,就必須先進(jìn)行運(yùn)算(*1或+0),SUM才函數(shù)才能正確求和.,=if(countif(A1:A10,b1),”存在”,”),=sum(A1:A100)*1),2

26、020/7/29,工作即是娛樂(lè),養(yǎng)成良好的邏輯分析習(xí)慣,如果有兩科以上不及格(60分為及格)均要補(bǔ)考 如果只有一科不及格,而這一科如果又低于40分,要補(bǔ)考 如果只有一科不及格,但該科達(dá)到40分,允許不補(bǔ)考 為鼓勵(lì)學(xué)生晝學(xué)好各科,如果只有一科不及格,且在40分以下,但若有4科在70分以上,也不可以不補(bǔ)考.,根據(jù)學(xué)校的成績(jī)規(guī)定來(lái)判斷學(xué)生是否需要補(bǔ)考,2020/7/29,工作即是娛樂(lè),函數(shù)操作_取交叉值,取數(shù),Match(),Offset(),Hlookup(),Lookup(),Index(),Vlookup(),2020/7/29,工作即是娛樂(lè),函 數(shù) 操 作 _ 條 件 匯 總,2020/7/

27、29,工作即是娛樂(lè),單條件匯總 SUMIF()函數(shù),根據(jù)指定條件對(duì)若干單元格求和。 語(yǔ)法 SUMIF(range,criteria,sum_range) Microsoft Excel 還提供了其他一些函數(shù)，它們可根據(jù)條件來(lái)分析數(shù)據(jù)。例如，如果要計(jì)算單元格區(qū)域內(nèi)某個(gè)文本字符串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 函數(shù)。如果要讓公式根據(jù)某一條件返回兩個(gè)數(shù)值中的某一值（例如，根據(jù)指定銷(xiāo)售額返回銷(xiāo)售紅利），則可使用 IF 函數(shù)。,2020/7/29,工作即是娛樂(lè),多條件匯總SUMPRODUCT()函數(shù),在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。 語(yǔ)法 SUMPRODUCT(a

28、rray1,array2,array3, .) Array1, array2, array3, . 為 2 到 30 個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。,2020/7/29,工作即是娛樂(lè),數(shù)組公式,公式(多重計(jì)算)相當(dāng)于A1*B1,A2*B2A5*B5,最后用SUM進(jìn)行合計(jì). 在EXCEL幫助中關(guān)于數(shù)組公式的說(shuō)明是:”對(duì)一組或多組值執(zhí)行多重計(jì)算,并返回一個(gè)或多個(gè)結(jié)果,數(shù)組公式括于大括號(hào)中,按CTRL+SHIFT+ENTER可以輸入數(shù)組公式.”,=SUM(A1:A5*B1*B5),2020/7/29,工作即是娛樂(lè),數(shù)組公式優(yōu)缺點(diǎn),缺點(diǎn),可讀性差。新手不易理解。 許多情況下速度略慢 參與操作的

29、幾個(gè)參數(shù)必須具有相同的尺寸，這可能導(dǎo)致一些并不需要計(jì)算的單元格也參與計(jì)算。特別是在多條件求和的數(shù)組公式中。,優(yōu)點(diǎn),簡(jiǎn)潔。一個(gè)公式就可以代替多行或多列的普通公式 功能強(qiáng)大。 可減小文件體積（但不一定減小內(nèi)存占用） 可以有一定的保護(hù)作用。多單元格數(shù)組公式必須選定全部公式所在單元格才能修改，一定程度上防止了新手誤操作。,2020/7/29,工作即是娛樂(lè),函數(shù)的多維立體應(yīng)用,在很多情況下，我們常要跨表操作、匯總，比如我們按月建立12個(gè)月數(shù)據(jù)，常會(huì)放到12個(gè)工作表上，或者按型號(hào)建立N個(gè)工作表做為基礎(chǔ)數(shù)據(jù)，但我們只希望在一個(gè)工作表上調(diào)用想要的分表數(shù)據(jù)或匯總N個(gè)分表的數(shù)據(jù)，其實(shí)我們可以通過(guò)函數(shù)很方便的立體調(diào)

30、用數(shù)據(jù)，想要哪個(gè)表數(shù)據(jù)就要哪個(gè)表的數(shù)據(jù)，函數(shù)Indirect()做為區(qū)域連接是必不可少.窗體做為控件也是一種很好的手段。,2020/7/29,工作即是娛樂(lè),認(rèn)識(shí)宏表函數(shù),2020/7/29,工作即是娛樂(lè),取得工作表名,GET.DOCUMENT(type_num,name_text)常用的參數(shù)有(1,76,88),GET.CELL(type_num,reference)常用的參數(shù)有(24,63等),GET.FORMULA(reference),EVALUATE(formula_text),取單元格屬性值,取得表達(dá)式,算式及計(jì)算結(jié)果,認(rèn)識(shí)宏表函數(shù),2020/7/29,工作即是娛樂(lè),函 數(shù) 操 作

31、_ 易失性函數(shù),NOW,COUNTIF,RAND,OFFSET,易失性函數(shù),使用這些函數(shù)后， 會(huì)引發(fā)工作表的重新計(jì)算,INDIRECT,2020/7/29,工作即是娛樂(lè),易失性函數(shù)優(yōu)缺點(diǎn),優(yōu)點(diǎn): 功能強(qiáng)大，有些復(fù)雜的計(jì)算不得不用易失性函數(shù) ,利用易失性函數(shù)的易失性做為某些特殊函數(shù)的輔助參數(shù),易失性函數(shù),缺點(diǎn): 計(jì)算復(fù)雜，如果使用過(guò)多當(dāng)有單元格的數(shù)據(jù)改變時(shí)，需要重新計(jì)算，有時(shí)等待時(shí)間很長(zhǎng)。,2020/7/29,工作即是娛樂(lè),使用“高效函數(shù)”,剛才我們介紹過(guò)易失性函數(shù)，如果工作簿中過(guò)多的使用這類(lèi)函數(shù)，則一旦用戶(hù)更新任何一個(gè)單元格，這些函數(shù)都將重新計(jì)算，從而會(huì)降低整個(gè)工作簿的性能。 COUNTIF函

32、數(shù)做為條件統(tǒng)計(jì)中使用非常廣泛，但如果在工作表大量使用該函數(shù)，每當(dāng)對(duì)工作表進(jìn)行操作時(shí)，系統(tǒng)都會(huì)自動(dòng)重新計(jì)算，這也凸顯了這個(gè)函數(shù)的缺點(diǎn)。因此，在編寫(xiě)公式時(shí)必須注意，晝選擇一些“高效率”函數(shù)來(lái)進(jìn)行編寫(xiě)。,2020/7/29,工作即是娛樂(lè),浮點(diǎn)運(yùn)算,困惑,實(shí)例,結(jié)論,解決,讓我們一起進(jìn)入EXCEL看看1+1是否真的不等于2,可以用ROUND等函數(shù)進(jìn)行調(diào)整,在EXCEL中運(yùn)算中1+1并不是完全等于2,什么我會(huì)這么說(shuō)呢,在EXCEL的加減運(yùn)算中結(jié)果是等于或非常接近于正確值,2020/7/29,工作即是娛樂(lè),圖表前言,一圖勝千言,一個(gè)設(shè)計(jì)嚴(yán)謹(jǐn),制作精美的圖表,能夠使報(bào)告中枯燥的數(shù)字變得感性和直觀,從而更有說(shuō)

33、服力. EXCEL在此方面的功能非常強(qiáng)大,利用它可以快速創(chuàng)建常見(jiàn)的多種圖表,通過(guò)適當(dāng)?shù)脑O(shè)置與DIY可以讓您創(chuàng)建出覺(jué)得不可能完成的圖表.,2020/7/29,工作即是娛樂(lè),圖表操作,如何做出高難度的漂亮圖表呢？,DIY你的圖表,通用技巧,精 典 圖,其 它 圖,動(dòng) 態(tài) 圖,2020/7/29,工作即是娛樂(lè),圖表通用技巧(去除分類(lèi)軸上的空白日期),去除分類(lèi)軸上的空白日期在EXCEL圖表中使用日期系統(tǒng)列作為分類(lèi)軸的標(biāo)志時(shí)，如果日期系列是不連續(xù)的，但是由于圖表的分類(lèi) 依然按連續(xù)的日期顯示，會(huì)導(dǎo)致在柱形圖中形成沒(méi)有柱體的“缺口”,2020/7/29,工作即是娛樂(lè),圖表通用技巧(隱藏接近于零的數(shù)據(jù)標(biāo)簽),

34、在制作餅圖時(shí)，經(jīng)常因?yàn)閿?shù)據(jù)百分比相差懸殊，或某個(gè)數(shù)據(jù)接近于0，而不能顯示其相應(yīng)的色塊，只顯示一個(gè)“0%”的數(shù)據(jù)標(biāo)志。當(dāng)然，可以直接刪除這個(gè)“0%”數(shù)據(jù)標(biāo)志，但是數(shù)據(jù)變更時(shí)，所有數(shù)據(jù)標(biāo)志會(huì)重新生成，有沒(méi)有一勞永逸的辦法呢？有，可以通過(guò)自定義數(shù)據(jù)格式的方法來(lái)隱藏這樣的數(shù)據(jù)標(biāo)志,2020/7/29,工作即是娛樂(lè),圖表通用技巧(在圖表中處理負(fù)值),上一技巧利用自定義數(shù)據(jù)格式的方法隱藏了接近于0的數(shù)據(jù)標(biāo)志，本技巧將繼續(xù)用自定義數(shù)字格式的方法，用不同的顏色來(lái)顯示不同區(qū)間的數(shù)據(jù)標(biāo)志。 比如要把數(shù)據(jù)標(biāo)志設(shè)為：負(fù)值為紅色，大于100的數(shù)為黃色，其它的數(shù)據(jù)不變?yōu)槟J(rèn)色。,2020/7/29,工作即是娛樂(lè),圖表通用技巧(快速設(shè)置圖表字體),在實(shí)際工作中，經(jīng)過(guò)多次設(shè)置，圖表上不同區(qū)域的字體大小與顏色會(huì)變得比較雜亂，如下圖所示，標(biāo)題，繪圖區(qū)，軸標(biāo)志與圖例等處的字體各不相同 ，影響了美觀。在這種情況下，如果希望各區(qū)域的字體、顏色統(tǒng)一，我們不必逐一設(shè)置，可以一次進(jìn)行調(diào)整.,2020/7/29,工作即是娛樂(lè),圖表通用技巧(在圖表中使用對(duì)數(shù)刻度),在制作圖時(shí)，如果數(shù)值軸