活用excel解決日常辦公難題,挖掘常用軟件功能

1、活用excel解決日常辦公難題，挖掘常用軟件功能區(qū)縣： 河北區(qū) 學(xué)校： 教師進(jìn)修學(xué)校 學(xué)科： 信息技術(shù) 姓名： 司志宏 活用excel解決日常辦公難題，挖掘常用軟件功能摘要：微軟的office系列辦公軟件在我們?nèi)粘９ぷ髦锌梢哉f是應(yīng)用廣泛、必不可少。其中的excel更是我們?cè)谥谱鞅砀窈吞幚砗?jiǎn)單數(shù)據(jù)時(shí)得心應(yīng)手的工具。作為一名信息技術(shù)教師，我們對(duì)各種常用軟件都要能夠深挖其功能來為我們服務(wù)。本文主要就excel中函數(shù)與宏功能的使用做一介紹來解決我們?nèi)粘９ぷ髦杏龅降母鞣N難題。關(guān)鍵字：挖掘功能、excel、函數(shù)、宏 微軟office系列中的excel作為我們?nèi)粘９ぷ髦兴褂玫降谋砀裰谱鬈浖梢哉f是使用簡(jiǎn)單

2、、功能強(qiáng)大、應(yīng)用廣泛，對(duì)我們來說是必不可少的。能用、會(huì)用、用好是我們教師在日常工作中使用的三個(gè)階段，對(duì)我們信息技術(shù)教師來說，把它用好才是我們需要考慮的問題。excel是一個(gè)表格處理軟件，在處理數(shù)量較多的數(shù)據(jù)時(shí)我們也可以把它看作一個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)應(yīng)用軟件。表格中的每一行就是一條記錄，每一列就是一個(gè)字段，它是一個(gè)可以隨時(shí)輸入、修改、刪除字段和記錄的簡(jiǎn)單數(shù)據(jù)庫(kù)軟件。在日常工作中，我們更多的是使用它身為表格處理軟件的一面，如制作表格、格式的修訂、簡(jiǎn)單的排序篩選分類匯總功能、通過表格中的數(shù)據(jù)生成指定樣式圖表、使用自動(dòng)套用格式功能為表格生成指定的樣式等。以上這些功能大部分都可以說是針對(duì)表格處理方面的，對(duì)表格

3、中的數(shù)據(jù)進(jìn)行各種處理的功能較少，只有簡(jiǎn)單的排序篩選分類匯總功能可以說是對(duì)數(shù)據(jù)進(jìn)行了一些指定方式的簡(jiǎn)單處理。但是在我們的工作生活中有很多東西不能夠通過簡(jiǎn)單的排序篩選分類匯總來解決，或者有一些大量但是要求格式相近的excel文檔需要處理，我們?cè)撛趺崔k？這則需要我們對(duì)excel軟件的功能進(jìn)行挖掘，挖掘出深藏于表面之下的某些功能來解決以上的難題。本文就excel中“函數(shù)”與“宏”功能做一些簡(jiǎn)單的闡述與介紹，來解決一些實(shí)際工作生活中的難題。一、函數(shù)功能在excel中有一種功能叫做“函數(shù)”，它是使用excel中指定的對(duì)話框或者在單元格中輸入指定的內(nèi)容以調(diào)用excel的一些命令實(shí)現(xiàn)一些操作。例如常見的在ex

4、cel常用工具欄上有一個(gè)符號(hào)，我們直接點(diǎn)擊符號(hào)它可以自動(dòng)為此單元格左邊或者上面一系列單元格進(jìn)行求和計(jì)算，其實(shí)就是調(diào)用了excel中的求和函數(shù)“sum”，為指定單元格進(jìn)行求和運(yùn)算。如果我們要是點(diǎn)擊中的下箭頭，會(huì)彈出一個(gè)菜單，內(nèi)容包括“求和”、“平均數(shù)”、“計(jì)數(shù)”等功能，最下方則有一個(gè)“其他函數(shù)”的菜單，通過點(diǎn)擊“其他函數(shù)”菜單，我們進(jìn)入到excel中“插入函數(shù)”功能的對(duì)話框中。這里面excel為我們建立了包括“常用”、“財(cái)務(wù)”、“數(shù)學(xué)與三角函數(shù)”、“統(tǒng)計(jì)”、“文本”等諸多的函數(shù)分類方式以利于我們對(duì)函數(shù)進(jìn)行查詢與使用，而每一個(gè)函數(shù)excel也為我們建立了說明性的文字以介紹其使用方式與功能。選擇好函

5、數(shù)并點(diǎn)擊“確定”之后我們進(jìn)入到相對(duì)應(yīng)的“函數(shù)參數(shù)”對(duì)話框，它為我們提供了函數(shù)相應(yīng)參數(shù)的輸入框和說明以利于我們使用。下面我就一些日常工作生活中遇到的問題分別介紹一些excel中常用函數(shù)看看是怎樣用這些函數(shù)來解決這些問題的。1、為一指定的值班表或類似的表格中查找是否有重復(fù)人員或統(tǒng)計(jì)人員的重復(fù)次數(shù)有時(shí)我們制作一個(gè)值班表并在其中填入人員名單確定值班人員時(shí)會(huì)出現(xiàn)一個(gè)問題：如何確定值班表中的每一個(gè)人員是不是都只值一次班，或者說大家的工作量都想盡，不出現(xiàn)一個(gè)人值3次班而另外一個(gè)人只值一次班的現(xiàn)象？而通過一個(gè)人眼睛觀察很難確定每一個(gè)人的值班次數(shù)，這時(shí)我們使用excel中“countif函數(shù)”就能很好的解決這個(gè)

6、問題。例如：有x年x班學(xué)生名冊(cè)表與2011年9-12月值日表各一份表1如何確定表中的每一名同學(xué)值日的次數(shù)基本平均，這通過使用眼睛觀看并計(jì)數(shù)顯然并不現(xiàn)實(shí)，這是使用excel函數(shù)來解決這個(gè)問題就很簡(jiǎn)單而且直觀。在表1“姓名”右邊插入一列，然后在“c3”格插入函數(shù)“=countif(sheet2!$a:$f,b3)”，此函數(shù)的含義是讓“c3”格現(xiàn)實(shí)的內(nèi)容為統(tǒng)計(jì)表“sheet2”中“a”列-“f”列中等與“b3”格內(nèi)容的格的數(shù)量，一下就統(tǒng)計(jì)了“b3”格中吳云高值日的次數(shù)為3次，然后通過拖動(dòng)“拖動(dòng)柄”計(jì)算每一名同學(xué)值日的次數(shù)。然后我們會(huì)發(fā) 表2現(xiàn)有的同學(xué)值日的次數(shù)很多、有的較少，我們?cè)偻ㄟ^調(diào)整協(xié)調(diào)安排每

7、名同學(xué)值日的次數(shù)達(dá)到比較公平。countif函數(shù)是統(tǒng)計(jì)指定范圍內(nèi)符合條件的單元格的數(shù)量，參數(shù)有兩個(gè)：第一個(gè)是指定的范圍如“a1:b3”，它是指從左上角為a1到右下角為b3的方形區(qū)域，“a:f”表示a列到f列，可以在函數(shù)的指定范圍中添加“$”符號(hào)表示為絕對(duì)引用，被添加“$”符號(hào)的行號(hào)或者列號(hào)不隨單元格位置的變化而變化；第二個(gè)是符合的條件如“b3”就是說“單元格的內(nèi)容要等與b3格的內(nèi)容”，如果寫的是“5”就是說“單元格內(nèi)的數(shù)值要大于5”。這個(gè)函數(shù)使用起來很簡(jiǎn)單也很方便，它很好的為我們統(tǒng)計(jì)指定區(qū)域內(nèi)符合條件的單元格數(shù)目，為我們做統(tǒng)計(jì)提供很好的支持。2、根據(jù)條件返回指定的內(nèi)容如判定學(xué)生某一門成績(jī)是否及

8、格或優(yōu)秀也許某一名學(xué)生的某一科成績(jī)很好判斷是否及格與優(yōu)秀，但是一個(gè)班的學(xué)生數(shù)門課程綜合在一起數(shù)百個(gè)數(shù)據(jù)就不是那么好看了，我們可以通過excel中的“if”函數(shù)來判斷一名學(xué)生的某一門科目是否及格或優(yōu)秀，然后通過拖動(dòng)的方式判定全班同學(xué)所有科目是否及格或優(yōu)秀。例在表3中是一個(gè)班期末考試的成績(jī)，學(xué)生有40名，科目有9科，想要說簡(jiǎn)單的查看每一個(gè)學(xué)生考試成績(jī)的及格優(yōu)秀與否不那么簡(jiǎn)單，我們可以通過使用“if”函數(shù)來判斷每一名學(xué)生的每一科成績(jī)是否及格或優(yōu)秀，并在最后使用“countif”統(tǒng)計(jì)每一名學(xué)生所有成績(jī)的大概情況。表3我們?cè)诒砀裼疫呄鄳?yīng)的位置添加標(biāo)題“數(shù)學(xué)及格情況”、“語文及格情況”等，然后在“數(shù)學(xué)情況

9、”下面插入函數(shù)“=if(c3=90,優(yōu)秀,)”，此函數(shù)表示“c3格的數(shù)值是否大于等與90，是的話顯示優(yōu)秀，不是的話不顯示任何內(nèi)容”。之后我們發(fā)現(xiàn)一個(gè)學(xué)生的成績(jī)可能是“優(yōu)秀”、“及格”、“不及格”，它們之間并不沖突，不可能即“優(yōu)秀”又“不及格”，所以是否“優(yōu)秀”與“及格”可以在一個(gè)格內(nèi)表示出來，我們可以通過函數(shù)“=if(c3=90,優(yōu)秀,及格)”。此函數(shù)表示“c3格的數(shù)值是否小于60，是的話顯示不及格，不是的話再看是否大于等與90，是的話顯示優(yōu)秀，不是的話顯示及格”。我們可以通過一個(gè)單元格就能顯示出學(xué)生一門考試成績(jī)的好壞。在最右邊的單元格中我們可以使用函數(shù)“=countif(l3:t3,不及格)

10、&門成績(jī)不及格&countif(l3:t3,優(yōu)秀)&門成績(jī)優(yōu)秀”來顯示學(xué)生的考試成績(jī)大概情況，有幾門成績(jī)不及格、有幾門成績(jī)優(yōu)秀。if函數(shù)的使用需要有一定的技巧性，它的三個(gè)參數(shù)第一個(gè)是“判斷的條件”，第二個(gè)是如果判斷對(duì)的話怎么樣，第三個(gè)是不對(duì)的話怎么樣。制定好判斷的條件是寫好if函數(shù)的關(guān)鍵。而if函數(shù)的嵌套則是靈活運(yùn)用if函數(shù)的體現(xiàn)。表43、為學(xué)生總成績(jī)排名在excel中可以使用sum函數(shù)為學(xué)生成績(jī)求和計(jì)算總成績(jī)，然后使用排序?yàn)閷W(xué)生總成績(jī)，然后為每一個(gè)學(xué)生的總成績(jī)添加序號(hào)。我們有更簡(jiǎn)便的方式。我們可以在計(jì)算總成績(jī)后，使用“rank”函數(shù)為總成績(jī)排名。繼續(xù)表4，我們?cè)诒砀褡钣疫吿砑印翱偝煽?jī)”和“總

11、成績(jī)排名”，在“總成績(jī)”下面的單元格中添加函數(shù)“=rank(v3,$v$3:$v$42)”并拖動(dòng)到全班同學(xué)就能夠顯示出每一個(gè)學(xué)生的總成績(jī)?cè)谌粗械呐琶?。?rank函數(shù)包括三個(gè)參數(shù)：第一個(gè)參數(shù)為需要被排名的單元格，如某一學(xué)生的總成績(jī)；第二個(gè)參數(shù)為排名的區(qū)域，如全班同學(xué)總成績(jī)所在的區(qū)域如v3:v42；第三個(gè)參數(shù)為排序的方式，省略或者為“0”都表示為降序，其他數(shù)字表示為升序。其中需要注意的是第二個(gè)參數(shù)的表示一般來說都需要使用絕對(duì)應(yīng)用，也就是需要在區(qū)域表示的單元格添加“$”符號(hào)，如,“$v$3:$v$42”，否則在使用拖動(dòng)功能時(shí)排名的區(qū)域也會(huì)隨著拖動(dòng)而變化，其結(jié)果就是越往后的學(xué)生的排名可能越靠前，最

12、后一個(gè)學(xué)生的排名肯定是“1”，因?yàn)榕琶膮^(qū)域內(nèi)只有他一個(gè)學(xué)生。4、將多個(gè)單元格的內(nèi)容連接起來有的時(shí)候我們需要將多個(gè)單元格的內(nèi)容合并到一個(gè)單元格中顯示，這是個(gè)問題。將其他單元格粘貼到別的單元格會(huì)覆蓋其內(nèi)容，雙擊單元格后復(fù)制其中的內(nèi)容再雙擊目標(biāo)單元格選定指定位置粘貼倒是可以解決這個(gè)問題，但是如果需要處理的單元格內(nèi)容很多就很麻煩而且需要很多時(shí)間來處理。怎樣解決這個(gè)問題？excel中提供了文本類函數(shù)“concatenate”，它可以將多個(gè)個(gè)參數(shù)的內(nèi)容連接起來顯示。例如，在表4中統(tǒng)計(jì)學(xué)生不及格與優(yōu)秀科目數(shù)量的時(shí)候，函數(shù)的書寫也許到不到“=countif(l3:t3,不及格)&門成績(jī)不及格&countif

13、(l3:t3,優(yōu)秀)&門成績(jī)優(yōu)秀”這樣直接書寫出來，只能用統(tǒng)計(jì)出“=countif(l3:t3,不及格)”統(tǒng)計(jì)出不及格科目的數(shù)量，然后在旁邊的單元格寫好“門成績(jī)不及格”。這樣使用函數(shù)則比較簡(jiǎn)單但是看起來很別扭。如果在u3、v3、w3、x3單元格中分別是“不及格科目數(shù)量”、“門成績(jī)不及格”、“優(yōu)秀科目數(shù)量”、“門成績(jī)優(yōu)秀”我們可以用函數(shù)“concatenate”將其連接起來，在“y3”單元格寫入“=concatenate（u3,v3,w3,x3）”，再將前幾列單元格隱藏也可以得到如表4一樣的效果。5、其他函數(shù)的使用除去if、countif、sum等這樣最常用的函數(shù)之外，還有一些函數(shù)是我們?cè)谑褂胑

14、xcel時(shí)經(jīng)常用到的。比如year函數(shù)，它是一個(gè)返回參數(shù)內(nèi)日期的年份的函數(shù)。我們有時(shí)候在統(tǒng)計(jì)學(xué)生年齡的時(shí)候可能只有學(xué)生的出生日期而沒有具體的年齡，我們可以進(jìn)行下面的操作來得到學(xué)生的年齡。如單元格“c3”中存放的是學(xué)生的出生日期“1998-4-2”，我們可以使用函數(shù)“=2013-year(c3)”得到學(xué)生在2013年的年齡為15歲。比如mid函數(shù)，它是一個(gè)返回指定字符串從左邊的第一個(gè)參數(shù)數(shù)值開始第二個(gè)參數(shù)數(shù)值個(gè)數(shù)的字符。依舊如上一個(gè)例子，有時(shí)候我們只有學(xué)生的身份證號(hào)的數(shù)據(jù)，連出生日期的數(shù)據(jù)都沒有，這是我們需要使用mid函數(shù)來從身份證號(hào)數(shù)據(jù)中提取出生年份或者出生年月日。如單元格“c3”存放的是學(xué)生

15、的身份證號(hào)，我們可以使用函數(shù)“=mid(c3,7,4)”來確定學(xué)生的出生年份，也可以使用函數(shù)“=mid(c3,7,4)&”-”&mid(c3,11,2)&”-”&mid(c3,13,2)”來形成學(xué)生的出生日期。比如len函數(shù)，它可以返回參數(shù)文本字符串的字符個(gè)數(shù)。如我們?cè)谑褂胢id函數(shù)從身份證號(hào)中提取出生日期是可能會(huì)有這樣的問題，有些人的身份證號(hào)可能是15位，這該如何解決？難道要篩選選出15位身份證號(hào)的人然后分別操作么？其實(shí)不用我們可以通過if函數(shù)判斷其身份證號(hào)位數(shù)然后分別寫好函數(shù)來解決。如上例中我們使用函數(shù)“=if(len(c3)=18, mid(c3,

16、7,4)&”-”&mid(c3,11,2)&”-”&mid(c3,13,2), “19”&mid(c3,7,2)&”-”&mid(c3,9,2)&”-”&mid(c3,11,2)”來解決這個(gè)問題。len(c3)=18是if函數(shù)的判斷條件，看一下c3個(gè)中的身份證號(hào)位數(shù)是不是18位，是的話之執(zhí)行剛才那個(gè)函數(shù)得到出生日期，如果不是18位那么先寫一個(gè)“19”然后連接身份證號(hào)中的第7、8兩位寫好年份再寫好月和日得到出生日期。二、宏的使用在office系列軟件中有一個(gè)通用的功能叫做“宏”，我們可以把它理解為一個(gè)自定義的批處理程序。在office系列中的各個(gè)軟件如word、powerpoint、excel、

17、access中都有“宏”的身影，不過在每個(gè)軟件中的宏都不太一樣。例如在word中更多的是對(duì)格式方面進(jìn)行規(guī)劃和設(shè)計(jì)，如對(duì)全篇文字進(jìn)行格式上的調(diào)整、頁(yè)面的設(shè)計(jì)等。而在excel中不僅包括對(duì)格式方面的調(diào)整與設(shè)計(jì)，對(duì)每一個(gè)單元格的內(nèi)容都可以設(shè)定與調(diào)整，甚至可以將一個(gè)復(fù)雜的函數(shù)設(shè)計(jì)成宏來使用。在excel中宏是這樣來生成并使用的：點(diǎn)擊工具宏錄制新宏為宏命名，設(shè)定快捷鍵，并選擇保存位置設(shè)定宏中的各種操作停止宏的錄制保持宏所存儲(chǔ)位置文件的打開狀態(tài)，可以隨時(shí)使用宏一般來說我們?cè)趀xcel中使用宏主要包括以下三種情況：1、為指定的區(qū)域規(guī)定樣式我們可以通過“宏”為指定的單元格設(shè)計(jì)樣式，如設(shè)計(jì)字體、字號(hào)、顏色、底色

18、、邊框、行寬列寬等，然后可以在不同的文件中使用宏中設(shè)計(jì)好的樣式為每一個(gè)文件規(guī)范格式。比如說我們?cè)O(shè)計(jì)一個(gè)宏，快捷鍵為“ctrl+e”，將a1:f16的單元格選中指定字體為“宋體”，字號(hào)為“20”，顏色為“紅色”，停止宏的錄制。我們打開其他的excel文件，使用快捷鍵“ctrl+e”，excel會(huì)自動(dòng)的將a1:f16的單元格中的文字字體改為“宋體”，字號(hào)改為“20”，顏色改為“紅色”。2、為文檔在指定位置添加相同的內(nèi)容如上面的例子一般，我們也可以使用宏功能為一批excel文檔添加相同的內(nèi)容。比如說我們?cè)僭O(shè)計(jì)一個(gè)宏，快捷鍵為“ctrl+r”，選擇第一行然后插入一行，將a1:f1合并并居中，輸入文字“xx中學(xué)期末考試成績(jī)單”，停止宏的錄制。之后我們可以打開其他文檔使用快捷鍵ctrl+r”，就能為此文檔上方插入一行合并并居中a1:f1單元格并輸入“xx小學(xué)期末考試成績(jī)單”。3、錄制指定的函數(shù)，通過快捷鍵調(diào)用宏快捷執(zhí)行函數(shù)我們可以把這種情況使用快捷