巧用Excel實(shí)現(xiàn)自動(dòng)出題
一、自動(dòng)出題
用Excel自動(dòng)出題，就能實(shí)現(xiàn)自動(dòng)出題、自動(dòng)更新題目的目的。具體操作方法也非常簡(jiǎn)單。為了便于管理，先建立一個(gè)名稱(chēng)為乘法的文件夾，在這個(gè)文件夾里新建一個(gè)名稱(chēng)為出題的Excel文件（如圖1所示）。A列、C列的函數(shù)公式是：“=INT（RAND（）*（9-1）+1）”。公式意義是隨機(jī)返回一個(gè) 1至9的整數(shù)，每次打開(kāi)或關(guān)閉此文件，都會(huì)重新隨機(jī)產(chǎn)生新的整數(shù)。
 圖一
此文件使用方法也很簡(jiǎn)單：主要就是打開(kāi)再關(guān)閉文件，關(guān)閉時(shí)出現(xiàn)“文件‘出題。xls’已被修改，是否保存其修改的內(nèi)容？”提示框，單擊[是]按鈕即可。
二、自動(dòng)判斷
用Excel自動(dòng)判斷，就可以根據(jù)輸入的數(shù)值，自動(dòng)判斷結(jié)果的正誤。具體操作方法也非常簡(jiǎn)單。在乘法文件夾里再新建一個(gè)名稱(chēng)為練習(xí)的Excel文件（如圖2所示）。
 圖二 1. 在文件中，第1行、B列、D列輸入文字即可。
2. A2單元格的函數(shù)公式是“=F:\乘法\[出題。xls]Sheet1!$A$1”。其實(shí)我們想實(shí)現(xiàn)的是練習(xí)文件的A2單元格等于出題文件A1單元格的內(nèi)容，如果直接打公式可能比較麻煩，使用鼠標(biāo)操作即可。同時(shí)打開(kāi)出題和練習(xí)這兩文件，先選中練習(xí)的A2單元格，按編輯公式欄的“=”，再切換到出題文件，選中A1單元格即可。其中公式里的“F:\乘法\[出題。xls]Sheet1!”表示的就是一個(gè)相對(duì)引用，會(huì)根據(jù)文件的存放位置自動(dòng)變化，這里表示剛才建立的乘法文件夾是在F盤(pán)的根目錄。根據(jù)相同的方法做完A列的A2至A11和C列的C2至C11單元格公式。
3. E列由練習(xí)人自己根據(jù)題目填寫(xiě)答案。
4. F2單元格的公式是“=IF（ISBLANK（E2），此題還沒(méi)做!，IF（E2=（A2*C2），√，×））”。用ISBLANK判斷做題人是否答題（如果E2單元格是空白單元格，就判定為沒(méi)有答題；否則就判定為已經(jīng)答題），沒(méi)有答題就顯示“此題還沒(méi)做！”；已經(jīng)答題的，就用IF進(jìn)行判斷答案是否正確，如果答案正確就顯示“√”，答案錯(cuò)誤就顯示“×”，F(xiàn)3到F11單元格的公式使用填充柄向下拉即可。
此文件使用方法也很簡(jiǎn)單：打開(kāi)文件，會(huì)出現(xiàn)“當(dāng)前所要打開(kāi)的文檔含有到其他文檔的鏈接，是否要使用其他工作簿中的改動(dòng)更新當(dāng)前工作簿？”提示框，選擇 [是]按鈕即可以開(kāi)始答題。需要變換題目時(shí)，打開(kāi)出題文件，保存后關(guān)閉，即可重新開(kāi)始。為了使練習(xí)文件里的E列不留下答題的痕跡，可以將此文件設(shè)置為只讀或關(guān)閉此文件時(shí)，不要保存，使得E列保持空白。
Excel實(shí)現(xiàn)隨機(jī)出題
以出20以?xún)?nèi)的隨機(jī)加法題為例。
1. 新建一個(gè)工作表，按住Ctrl不放，分別單擊A1和C1，將其選中，在其右鍵菜單中選擇“設(shè)置單元格格式”，單擊“數(shù)字”選項(xiàng)卡，選擇“數(shù)值”，將“小數(shù)位數(shù)”右邊的數(shù)字縮小為“0”后，單擊[確定]按鈕。
2. 分別在單元格A1中輸入公式“=rand()*20”，B1中輸入“+”，C1中輸入公式“=rand()*(20-A1)”，D1中輸入“=”。
3. 選中A1 1，向下拖動(dòng)其右下角的填充柄至D25，一列20以?xún)?nèi)的隨機(jī)加法題就完成了。
4. 選中單元格A125，將選定區(qū)域“復(fù)制”，在單元格F1位置“粘貼”，即可完成多列隨機(jī)出題。
5. 若要重新生成隨機(jī)題，只需拖動(dòng)任意一個(gè)空白單元格右下角的填充柄即可。
Excel巧制自測(cè)練習(xí)卷
[編者] 前面我們介紹了《Excel常用函數(shù)的功能和使用方法》，現(xiàn)在我們學(xué)以致用，介紹一系列用這些函數(shù)實(shí)現(xiàn)的數(shù)據(jù)統(tǒng)計(jì)實(shí)例解析。今天我們介紹一個(gè)教師學(xué)生都方便使用的電子自測(cè)練習(xí)卷。
教師的一個(gè)主要工作就是批改學(xué)生的作業(yè)，由于學(xué)生的作業(yè)量很大，教師的批改工作量非常大。隨著家庭電腦的不斷普通，我們可以用Excel等軟件制作電子練習(xí)卷，讓學(xué)生上機(jī)解答，讓電腦來(lái)自動(dòng)批改。
完成功能：我們用Excel制作這樣一份電子練習(xí)卷，選擇題5題，每題正確答案可能有1—2個(gè)，完成正確得10分，如果有2個(gè)正確答案，學(xué)生選擇了1個(gè)且正確，得4分，其它均不得分；判斷題5題，判斷正確得10分，反之不得分。
注：這樣制作的電子練習(xí)卷，一般僅支持選擇、判斷、填空等客觀性試題。
制作方法：
1、啟動(dòng)Excel2003（其它版本請(qǐng)大家仿照操作），按照練習(xí)卷的格式我要求，制作好一張空白表格。
2、將題號(hào)、題目、標(biāo)準(zhǔn)答案等內(nèi)容輸入到相應(yīng)的單元格中（效果如圖1）。
注意：在單元格中輸入文本，如果需要換行，可以通過(guò)下面兩種方法來(lái)實(shí)現(xiàn)：
①選中需要換行的單元格，執(zhí)行“格式、單元格”命令，打開(kāi)“單元格格式”對(duì)話框（如圖2），切換到“對(duì)齊”標(biāo)簽下，選中“自動(dòng)換行”選項(xiàng)，確定返回即可。經(jīng)過(guò)這樣的設(shè)置后，如果單元格中填入超過(guò)列寬的內(nèi)容，系統(tǒng)會(huì)自動(dòng)換行；如果調(diào)整列寬，系統(tǒng)會(huì)自動(dòng)調(diào)整單元格中各行的字符量。
�、谌绻朐谀硞�(gè)字符處強(qiáng)制換行，請(qǐng)將光標(biāo)定在相應(yīng)字符的右則，然后按下“Alt+Enter”組合鍵即可。
讓Excel給你報(bào)賬
許多人經(jīng)常要用Excel來(lái)統(tǒng)計(jì)一些數(shù)據(jù)，統(tǒng)計(jì)完成后還要對(duì)數(shù)據(jù)進(jìn)行校對(duì)，但是這么多數(shù)據(jù)怎樣才能不出差錯(cuò)地進(jìn)行校對(duì)呢？其實(shí)我們可以用一下Excel自帶的“文本到語(yǔ)音”功能，讓軟件通過(guò)發(fā)聲自動(dòng)給你報(bào)賬。
打開(kāi)Excel，點(diǎn)擊“工具→語(yǔ)音→顯示文本到語(yǔ)音工具欄”，由于這個(gè)功能在安裝Excel的時(shí)候不是默認(rèn)安裝的，所以在第一次使用時(shí)會(huì)提示插入Office 安裝光盤(pán)來(lái)安裝這個(gè)功能。
安裝完成后打開(kāi)“文本到語(yǔ)音”工具欄，先在數(shù)據(jù)文件中用鼠標(biāo)選擇要朗讀的第一個(gè)數(shù)據(jù)，接著根據(jù)數(shù)據(jù)的排列情況來(lái)選擇是“按行”還是“按列”來(lái)進(jìn)行朗讀，設(shè)置完成后點(diǎn)擊工具欄最左面的“朗讀單元格”按鈕就可以了。在朗讀的時(shí)候被朗讀到的單元格會(huì)以選中狀態(tài)顯示，而且還可以流利地朗讀中文，英文是一個(gè)字母一個(gè)字母朗讀的，這樣就可以校對(duì)英文單詞的拼寫(xiě)是否正確（圖1）。
該功能還可以一邊輸入數(shù)據(jù)一邊來(lái)進(jìn)行語(yǔ)音校對(duì)，只要點(diǎn)擊一下工具欄最右面的 “按回車(chē)開(kāi)始朗讀” 按鈕，使其為選中狀態(tài)，這樣在完成一個(gè)單元格的輸入后按回車(chē)，Excel就會(huì)自動(dòng)來(lái)朗讀這個(gè)單元格中的內(nèi)容。
一般朗讀默認(rèn)的是男聲的，如果你聽(tīng)膩了，可以通過(guò)系統(tǒng)里的設(shè)置把男聲改為女聲，打開(kāi)控制面板中的“語(yǔ)音”項(xiàng)，在里面的“語(yǔ)音選擇”來(lái)選擇一下語(yǔ)音，然后點(diǎn)擊下面的“預(yù)覽聲音”就可以聽(tīng)效果了，而且還可以設(shè)置語(yǔ)音的朗讀速度，完成后點(diǎn)擊“確定”按鈕就可以了（圖2）。
讓Excel的格式自動(dòng)變化
作為會(huì)計(jì)人員，經(jīng)常要對(duì)一些金額進(jìn)行控制。比如預(yù)算、開(kāi)支等。但怎么讓表格自動(dòng)計(jì)算并提醒你，那些項(xiàng)目快要超出預(yù)算或己經(jīng)超支呢？
或是，你經(jīng)常要交總結(jié)材料給老板，對(duì)于表格上的一些項(xiàng)目，需突出顯示它們的區(qū)別，以便引起老板注意，你是否每次都是不辭勞苦的對(duì)些單元格格式進(jìn)行反復(fù)設(shè)置？
其實(shí)，利用Excel的條件格式，可讓你一勞永逸。請(qǐng)看下面的一個(gè)例子（圖一）。
 圖一
在圖一中，筆者的目地是要對(duì)每天的開(kāi)支進(jìn)行控制。于是用SUMIF函數(shù)對(duì)在另一張工作表中的日常開(kāi)支明細(xì)進(jìn)行匯總。對(duì)于匯總后的結(jié)果，筆者利用條件格式，對(duì)每天不同的開(kāi)支數(shù)量用不同的格式顯示，如果日開(kāi)支等于0，則將字符顏色設(shè)成跟底色一樣，以免看到0；如開(kāi)支小于50元，用缺省格式，如果在50-100之間，則用黃底紅字，如超過(guò)100元以上，則用紅底黑字顯示，以示警告。（具體的設(shè)置情況如圖二。）  圖二
這樣，電腦根據(jù)不同的匯總結(jié)果，便顯示不同的效果，筆者一眼看上去，便知哪天又嚴(yán)重超支了。（雖然如此，筆者仍是無(wú)法降低開(kāi)支，該花的還是要花… *o*）。
條件格式是支持拖拉填充的，所以，筆者只要設(shè)好B1單元格的公式及條件格式，然后往下一拖即成。簡(jiǎn)單快速。
條件格式的設(shè)置方法為：
1. 選中要設(shè)置條件格式的單元格；
2. 選擇菜單“格式”/“條件格式…”，系統(tǒng)彈出“條件格式”設(shè)置對(duì)話框。見(jiàn)下圖三。在缺省情況下，對(duì)話框只顯示一種情況，如大家要求對(duì)多種情況進(jìn)行判斷并作相應(yīng)格式設(shè)置，可按“添加”按鈕。最多可有三種邏輯判斷（參見(jiàn)圖二），因?yàn)檫�存在一種以上三種情況都不是的情形，所以，真正來(lái)說(shuō)，它可以設(shè)置四種狀態(tài)下的格式。如上面的例子中，開(kāi)支在0-50元的情況便是系統(tǒng)需進(jìn)行的第四種判斷；
 2004-5-31 15:43:04
圖三
3. 系統(tǒng)預(yù)設(shè)為對(duì)該單元格的值進(jìn)行判斷，根據(jù)實(shí)際情況可選擇大于、介于、小于等，然后在其后面的輸入框中輸入條件。在條件輸入框中可以直接內(nèi)容也可以輸入單元格名稱(chēng)參照，以讓系統(tǒng)對(duì)該單元格的實(shí)際內(nèi)容進(jìn)行比較。另外，點(diǎn)擊“單元格數(shù)值”可選擇判斷條件為公式。當(dāng)選擇條件為公式時(shí)，其后的邏輯判斷框變成公式輸入框。請(qǐng)注意：在輸入公式時(shí)，請(qǐng)用“=”號(hào)開(kāi)頭。對(duì)于公式，系統(tǒng)按公式返回的值是否為“真”進(jìn)行邏輯判斷。
4. 設(shè)好條件后，按“格式…”按鈕，便進(jìn)入格式設(shè)置畫(huà)面。（參見(jiàn)圖四）根據(jù)自己的喜好，選擇相應(yīng)的格式。然后按“確定”返回。這時(shí)可在對(duì)話框中間看到格式預(yù)覽。  圖四
5. 依次設(shè)置其他條件格式，最后按“確定”返回編輯窗口，條件格式設(shè)置完畢。這時(shí)你在該單元格輸入不同的內(nèi)容，其格式便會(huì)隨著你定義的條件而自動(dòng)變化。
6. 大家可利用格式刷將條件格式快速套用到其它地方。
條件格式的刪除
如需要對(duì)己設(shè)置的條件格式進(jìn)行刪除，可用下面的方法進(jìn)行刪除：
1. 選擇要?jiǎng)h除條件格式的單元格；
2. 選擇菜單“格式”/“條件格式…”，在系統(tǒng)彈出“條件格式”設(shè)置對(duì)話框中按“刪除”按鈕。
或者
1. 選擇沒(méi)有設(shè)置條件格式的單元格；
2. 按下工具列的格式刷，對(duì)要?jiǎng)h除條件格式的單元格進(jìn)行格式復(fù)制。
如要?jiǎng)h除工作表中全部或部分相同條件格式，可先按“Ctrl+G”，之后在對(duì)話框中按“定位條件”，系統(tǒng)顯示定位條件對(duì)話框（參見(jiàn)圖五），在該框中，選擇“條件格式”，之后，根據(jù)需要選擇“全部”或“相同”，再按“確定”，回到編輯窗口，這時(shí)，所有設(shè)置了條件格式的單元格都被選中，再按上面所講的第一種刪除方法刪除即可。
 圖五
自動(dòng)工作日計(jì)算
工作量當(dāng)然得以“工作日”的數(shù)量去安排，但每個(gè)月的天數(shù)不同，而且周六日也不盡相同。如果想準(zhǔn)確地知道一個(gè)月有多少個(gè)工作日，可以請(qǐng)Excel來(lái)幫忙！
小知識(shí)
標(biāo)準(zhǔn)工作日是指法律規(guī)定的各企業(yè)、事業(yè)、機(jī)關(guān)、團(tuán)體等單位在正常情況下普遍實(shí)行的工作日。通俗的說(shuō)，就是指除了國(guó)家法定節(jié)假日（雙休日、元旦、五一、國(guó)慶、春節(jié)等）之外正常工作的日期。
任務(wù)分析
因?yàn)槊吭碌目偺鞌?shù)和雙休日的分布是不同的，而且春節(jié)是農(nóng)歷的假期，簡(jiǎn)單地使用Excel函數(shù)去計(jì)算是滿(mǎn)足不了要求的。我們先使用Excel“分析工具庫(kù)”中的networkdays()函數(shù)計(jì)算出“準(zhǔn)工作日”，再考慮其中的特殊農(nóng)歷假日。這樣就能準(zhǔn)確的算出“工作日”的天數(shù)。
有請(qǐng) networkdays()函數(shù)
通常情況下，在Excel中是找不到這個(gè)函數(shù)的。別著急，跟我來(lái)。單擊“工具→加載宏”，在彈出的“加載宏”對(duì)話框中復(fù)選“分析工具庫(kù)”，再單擊“確定”按鈕。將Office的安裝光盤(pán)放入光驅(qū)，按提示即可安裝成功，如圖1。
外來(lái)的和尚念不好經(jīng)
networkdays()函數(shù)的語(yǔ)法為：networkdays(Start_date，End_date，Holidays)。其中Start_date表示開(kāi)始日期，End_date為終止日期，Holidays表示一個(gè)或者多個(gè)特定假日序列，可以采用單元格引用的方式。
如圖2，B8單元格公式“=networkdays(B2,B3,B4:B6)”結(jié)果為20。事實(shí)上，2004年的10月2日和3日是星期六和星期天，那么按照我們的習(xí)慣就會(huì)通過(guò)調(diào)休的方式，最終得到10月份只有18天上班，而不是networkdays()函數(shù)計(jì)算出的20天。
特殊假日的處理
雖然說(shuō)直接應(yīng)用networkdays()函數(shù)并不能得到正確的工作日天數(shù)，但我們還是可以通過(guò)對(duì)此函數(shù)的變化來(lái)得到。圖3是我們的范例，演示了如何一步一步逼近我們的目標(biāo)。
第一步：將A列定義為月份。選中A列，在右鍵菜單中選擇“設(shè)定單元格格式”，在“數(shù)字”標(biāo)簽中選擇“分類(lèi)”為“自定義”，“類(lèi)型”中輸入“yyyy-mm”格式，單擊“確定”按鈕退出。
第二步：設(shè)計(jì)B列公式。先直接用networkdays()函數(shù)計(jì)算只考慮本月除去雙休日后的天數(shù)。雖然可直接使用該函數(shù)，但需要知道每月的開(kāi)始日期和終止日期。開(kāi)始日期當(dāng)然是每月的1日，用“date(year(A2),month(A2),1)”表示就OK了。結(jié)束日期應(yīng)當(dāng)是每月的最后一天，這就有些難度了，究竟是30日、31日還是28日、29日？需要一個(gè)復(fù)雜的判斷關(guān)系。這里我們采用了一個(gè)變通的辦法，就是當(dāng)月的最后一天其實(shí)就是下月的第一天再減去1天，所以我們可以用“date(year(A2),month(A2)+1,1)-1”來(lái)表示。于是B2的公式就有了：“=networkdays(date(year(A2),month(A2),1),date(year(A2),month(A2)+1,1)-1)”。
第三步：計(jì)算除了春節(jié)以外的雙休日。我們知道國(guó)家規(guī)定的公眾假日一共10天，除了春節(jié)的3天是每年變化的外，其余元旦1天，勞動(dòng)節(jié)3天，國(guó)慶節(jié)3天都是固定的。因此我們可以用IF函數(shù)對(duì)月份是否是1月、5月和10月做判斷，再來(lái)減去相應(yīng)的公眾假日天數(shù)，就得到了不考慮春節(jié)以外的工作日。于是C2=if(month(A2)=5,B2-3,if(month(A2)=5,B2-3,if(month(A2)=1,B2-1,B2)))或者使用or()函數(shù)就是C2=if(or(month(A2)=5,month(A2)=10),B4-3,if(month(A2)=1,B4-1,B4))。
第四步：考慮春節(jié)的問(wèn)題。根據(jù)常識(shí)，春節(jié)的三天只會(huì)在1月份或者2月份出現(xiàn)，也就是說(shuō)最后的判斷只正對(duì)1、2月份。通過(guò)萬(wàn)年歷查詢(xún)得到2000——2010這十年的春節(jié)分布，巧的是正好沒(méi)有春節(jié)三天跨月份的年份，也就是說(shuō)春節(jié)三天不是落在1月份就是落在2月份，如圖4所示，這給我們應(yīng)用IF函數(shù)判斷帶來(lái)了方便。通過(guò)對(duì)年份和月份的判斷，減去相應(yīng)的春節(jié)天數(shù)，就得到了真正意義上的工作日。因此在D2中應(yīng)該輸入=IF(AND(OR(YEAR(A2)=2001,YEAR(A2)=2004,YEAR(A2)=2006,YEAR(A2)=2009),MONTH(A2)=1),C2-3,IF(AND(OR(YEAR(A2)=2000,YEAR(A2)=2002,YEAR(A2)=2003,YEAR(A2)=2005,YEAR(A2)=2007,YEAR(A2)=2008,YEAR(A2)=2010),MONTH(A2)=2),C2-3,C2))。
第五步：隱藏過(guò)渡列。選中“B:C”這兩列，在右鍵菜單中選擇“隱藏”，將中間用于計(jì)算方便所使用的過(guò)渡列B和列C隱藏即可。
選中區(qū)域B22，按住填充柄向下拖動(dòng)填充后，只要在A列任一單元格輸入月份，就可以在D列得到該月相應(yīng)的天數(shù)，是不是很方便？
讓多頁(yè)表自動(dòng)生成表頭
當(dāng)我們制作的表格有好多頁(yè)的時(shí)候（如成績(jī)統(tǒng)計(jì)表等），為便于觀看，最好是每一頁(yè)都添加一個(gè)相同的表頭。如果表格文件頁(yè)數(shù)很少，通過(guò)手工的方法就可以很容易地實(shí)現(xiàn)。但當(dāng)一個(gè)表格文件多達(dá)數(shù)十頁(yè)的時(shí)候，再一個(gè)一個(gè)地逐頁(yè)添加，不僅費(fèi)事，還容易出錯(cuò)。那么，有沒(méi)有辦法讓系統(tǒng)自動(dòng)生成呢？
1)． 在Word 2003中
打開(kāi)Word 2003表格文檔，選中第一頁(yè)表的表頭，然后點(diǎn)擊“表格→標(biāo)題行重復(fù)”。假如表頭有兩行內(nèi)容，選中開(kāi)頭兩行，這樣，在后面的每一頁(yè)，都會(huì)將這兩行的內(nèi)容作為表頭。
2)． 在Excel 2003中
（1）打開(kāi)Excel表格文檔，點(diǎn)擊“文件→頁(yè)面設(shè)置”。
�。�2）在調(diào)出的“頁(yè)面設(shè)置”對(duì)話框中，選擇“工作表”選項(xiàng)卡，在“頂端標(biāo)題行”欄中輸入表頭行號(hào)，其中，“$1”表示第1行，“$1 2”表示第1、2行。本例中設(shè)置為“$12”，打印出來(lái)的每頁(yè)表格便都有同第1、2兩行同樣內(nèi)容的表頭。
3)． 在金山文字2003中
�。�1）打開(kāi)金山文字2003表格文檔，選中表格，然后點(diǎn)擊“表格→表格屬性→表格外觀”。
�。�2）在調(diào)出的“表格外觀”屬性窗口上，選擇“外觀設(shè)定”為“行列式1”，設(shè)置“以標(biāo)題行重復(fù)出現(xiàn)行數(shù)”，本例設(shè)置為“2”，即在每一頁(yè)表格都自動(dòng)生成與第1、2行內(nèi)容相同的表頭。
4)． 在金山表格2003中
（1）打開(kāi)金山表格2003，點(diǎn)擊“文件→頁(yè)面設(shè)置”。
�。�2）在調(diào)出的“頁(yè)面設(shè)置”選單中，選擇“工作表”選項(xiàng)卡，在“頂端標(biāo)題行”輸入表頭行數(shù)，本例為“2”，即在打印出來(lái)的每一頁(yè)表格上，都自動(dòng)生成與第1、2行內(nèi)容相同的兩行表頭。如果表格的表頭在左邊，可以在“左端標(biāo)題列”欄中輸入表頭列數(shù)。
Excel輕松實(shí)現(xiàn)自動(dòng)換行
Excel處理數(shù)據(jù)之便捷眾人皆知，可在其單元格內(nèi)換行就略顯不便，不知你是否也遇到過(guò)此類(lèi)問(wèn)題？通過(guò)摸索，以下四法便能輕松實(shí)現(xiàn)單元格內(nèi)的自動(dòng)換行。
1. 輸入數(shù)據(jù)隨時(shí)換行
用戶(hù)若要在輸入數(shù)據(jù)時(shí)換行，只要通過(guò)Alt+Enter組合鍵即可輕松實(shí)現(xiàn)。此方法同樣可使已輸入內(nèi)容的單元格在光標(biāo)所在處換行。
2. 單元格區(qū)域內(nèi)換行
將某個(gè)長(zhǎng)行轉(zhuǎn)成段落并在指定區(qū)域內(nèi)換行。例如：A10內(nèi)容很長(zhǎng)，欲將其顯示在A列至C列之內(nèi)，步驟是：選定區(qū)域A10：C12（先選A10），選擇“編輯→填充→內(nèi)容重排”，A10內(nèi)容就會(huì)分布在A10：C12區(qū)域中。此法特別適合用于表格內(nèi)的注釋。
3. 調(diào)整單元格格式換行
選定單元格，選擇“格式→單元格”，在彈出的對(duì)話框中單擊“對(duì)齊”，選中“自動(dòng)換行”復(fù)選框，單擊[確定]按鈕即可。
4. 文本框的巧用
單擊“視圖”菜單，在“工具欄”命令中，選中“繪圖”工具欄，單擊該工具欄的“文本框”，為了保證文本框的邊界與工作表網(wǎng)格線重合，需按住Alt鍵的同時(shí)插入文本框，然后，就可以在文本框中任意輸入內(nèi)容了。
通向Excel智能化的N條捷徑
智能判斷Ｅｘｃｅｌ重復(fù)數(shù)據(jù)
在Excel工作表中，若要判斷各個(gè)單元格中的數(shù)據(jù)是否重復(fù)并統(tǒng)計(jì)出重復(fù)的行號(hào)，可使用函數(shù)令其自動(dòng)執(zhí)行操作。假設(shè)，要統(tǒng)計(jì)顯示于A1～A12單元格中的數(shù)據(jù)。首先，在B1單元格中輸入代碼：
=IF(COUNTIF($A$1A$13,VLOOKUP(A1,A2:$A$13,1,0))>1,CONCATENATE("重復(fù)行號(hào):",MATCH(A1,A2:A$13,0)+ROW(A1)),"")
而后，在B1單元格中會(huì)顯示與A1數(shù)據(jù)重復(fù)的行號(hào)。下面，選擇區(qū)域B1～B12，點(diǎn)擊菜單欄“編輯”→“填充”→“序列”，在彈出對(duì)話框中查看“類(lèi)型”項(xiàng)目，在此選擇“自動(dòng)填充”，其余選項(xiàng)保持默認(rèn)設(shè)置。確認(rèn)操作后，B2～B12之間的重復(fù)行號(hào)均會(huì)自動(dòng)填充（如圖1）。
快捷搜索相同的數(shù)據(jù)
在Excel中，若要從眾多的數(shù)據(jù)快速查找到相同的一組數(shù)據(jù)，可利用其篩選功能。先選擇數(shù)據(jù)列的最上方的單元格，再點(diǎn)擊菜單欄“數(shù)據(jù)→篩選→自動(dòng)篩選”。而后，即可在該單元格上看到一個(gè)向下箭頭按鈕，點(diǎn)擊它，在彈出菜單中選擇欲查找的相同數(shù)據(jù)即可（如圖2）。
自動(dòng)填寫(xiě)ＩＰ地址
如果需要在Excel中輸入在大量本公司局域網(wǎng)的IP地址，人工操作是非常耗時(shí)的，這種情況下我們可使用以下方法使其自動(dòng)完成輸入。首先，在數(shù)據(jù)列的第一行和第二行輸入IP地址。然后，選擇這兩個(gè)已輸入IP地址的單元格，點(diǎn)擊工具欄上的“復(fù)制”按鈕。此后，會(huì)看到該區(qū)域又被添加為虛線邊框，把鼠標(biāo)置于該區(qū)域的右下角，會(huì)看到一個(gè)十字形光標(biāo)，點(diǎn)擊拖拽該光標(biāo)至需輸入IP地址的結(jié)束位置。放開(kāi)鼠標(biāo)后，將看到所有IP地址均按規(guī)則自動(dòng)填充完畢（如圖3）。
提示：按照用戶(hù)的習(xí)慣性思維，往往會(huì)考慮使用Excel的“自動(dòng)填充”實(shí)現(xiàn)這一效果，但可惜在本例中此項(xiàng)功能并不奏效。
自動(dòng)計(jì)算銷(xiāo)售金額
在如圖4所示的表單中，若要計(jì)算出某個(gè)員工一個(gè)月內(nèi)的銷(xiāo)售金額，可以使用篩選再求和的方法，也可以使用函數(shù)計(jì)算。方法1：定位于“人員”單元格，點(diǎn)擊菜單欄“數(shù)據(jù)→篩選→自動(dòng)篩選”，而后點(diǎn)擊新增的該單元格向下箭頭按鈕，在彈出菜單醒≡褚桓鮒付ǖ男彰 似淥 嗽保 笱≡袼 邢 勱鴝鈧械氖 藎 俚慊鞴ぞ呃干系摹白遠(yuǎn) 蠛汀卑磁ゼ純苫竦眉撲憬峁 ７椒?：定位于某人一月匯總銷(xiāo)售金額輸入位置的單元格后，輸入公式代碼：
=SUM((B列始行數(shù):B列結(jié)束行數(shù)="目標(biāo)員工姓名")×(D列始行數(shù):D列結(jié)束行數(shù)="1")×(C列始行數(shù):C列結(jié)束行數(shù)))再按“Ctrl+Shift+回車(chē)”確認(rèn)公式即可。
智能化判斷數(shù)據(jù)有效性
１． 自動(dòng)彈出說(shuō)明提示
對(duì)于需要注釋說(shuō)明的單元格數(shù)據(jù)，我們可為其添加提示功能，以幫助用戶(hù)能夠更明晰地了解當(dāng)前內(nèi)容。在選定Excel表格中的指定單元格后，點(diǎn)擊菜單欄“數(shù)據(jù)→有效性”，而后在彈出窗口中切換到“輸入信息”標(biāo)簽頁(yè)，在此輸入選定單元格時(shí)顯示的標(biāo)題及信息（如圖5）。
２． 自動(dòng)彈出錯(cuò)誤提示
我們還可以設(shè)置在Excel中當(dāng)輸入不符合條件的數(shù)據(jù)時(shí)自動(dòng)提示。按以上方法調(diào)出“數(shù)據(jù)有效性”窗口后，切換到“出錯(cuò)警告”對(duì)話框，在此可選擇出錯(cuò)警告的圖標(biāo)樣式與標(biāo)題及錯(cuò)誤信息。
３． 限制輸入數(shù)據(jù)的條件
默認(rèn)狀態(tài)下，在Excel中可以輸入任何數(shù)據(jù)，而你若是想讓指定單元格的數(shù)據(jù)只能是日期、時(shí)間或是有大小、長(zhǎng)度等限制，即可使用Excel有效性功能。在“數(shù)據(jù)有效性”窗口中，查看“設(shè)置”標(biāo)簽頁(yè)，在此點(diǎn)擊“允許”下拉列表，在此選擇允許輸入數(shù)據(jù)的條件，例如“整數(shù)”、“小數(shù)”、“序列”、“文本長(zhǎng)度”等，而后再按需進(jìn)一步詳細(xì)設(shè)置。
4.自動(dòng)添加表頭
有些用戶(hù)在打印多頁(yè)Excel表格時(shí)發(fā)現(xiàn)，僅第一頁(yè)有表頭，而第二頁(yè)以后就看不到表頭，只能手工添加，其實(shí)只需要調(diào)節(jié)一下打印設(shè)置即可。在Excel中點(diǎn)擊菜單欄“文件→頁(yè)面設(shè)置”，而后在彈出窗口中切換到“工作表”標(biāo)簽頁(yè)，在此查看“打印標(biāo)題”項(xiàng)目，在“頂端標(biāo)題行”中輸入每頁(yè)打印的表頭內(nèi)容，再預(yù)覽一下效果。
隔行數(shù)值自動(dòng)求和
有時(shí)出于特殊需要，得把一個(gè)Excel表格中的單數(shù)行數(shù)值及雙數(shù)行數(shù)值分別相加（即隔行數(shù)值相加），統(tǒng)計(jì)出最終結(jié)果５ 碭裉 ぃ 止ぜ撲閌翟謔翹 氖保 聳本托枰 罷乙桓黿菥�。首淆x 詰鼻氨碭袷 檔撓也嗵砑右渙�。染忬� 詰諞恍瀉偷詼 械ピ 衲謔淙氬煌 淖址 ㄈ鏰、b），再使其自動(dòng)填充整列。下面，點(diǎn)擊菜單欄“數(shù)據(jù)→篩選→自動(dòng)篩選”，此后在添加列中分別選擇那兩個(gè)不同的字符，即可篩選出單行與雙行的數(shù)值。選中這些數(shù)值后，點(diǎn)擊工具欄上的“自動(dòng)求和”按鈕既可。
讓Office 2003能自動(dòng)輸入序列號(hào)
office 2003是微軟公司推出的新一代團(tuán)隊(duì)協(xié)作及辦公處理軟件。和微軟的其他產(chǎn)品一樣，在安裝時(shí)它會(huì)要求用戶(hù)對(duì)照著光盤(pán)用戶(hù)手冊(cè)一個(gè)字符一個(gè)字符地輸入序列號(hào)。其實(shí)，我們只要對(duì)office 2003的安裝程序稍加改造，就可以讓它在安裝時(shí)自動(dòng)輸入序列號(hào)，免去重復(fù)輸入的麻煩。
先將office 2003安裝光盤(pán)中的內(nèi)容全部復(fù)制到硬盤(pán)的任意目錄下，如F:\ office2003。然后打開(kāi)資源管理器，進(jìn)入F:\office2003\office\PRO\FILES\SETUP文件夾，在右側(cè)窗口中用鼠標(biāo)右鍵單擊Setup.ini文件，在彈出的右鍵菜單中選擇“屬性”命令，打開(kāi)“屬性”對(duì)話框，將“只讀”復(fù)選框前的復(fù)選框清空，再單擊“確定”按鈕。
接下來(lái)用記事本程序打開(kāi)Setup.ini文件，找到“[Options]”小節(jié)，在其下找到“USERNAME=Customer”一行，這里定義的是用戶(hù)在安裝時(shí)輸入的用戶(hù)名，將其改為“USERNAME=你自己的名字”即可；“COMPANYNAME=”定義的是在安裝時(shí)用戶(hù)輸入的單位名稱(chēng)，將其改為正確的名稱(chēng)即可。接下來(lái)在其下面添加上一個(gè)空行，輸入“PIDKEY=”，其中，“PIDKEY=”后面，就是光盤(pán)用戶(hù)手冊(cè)中提供的序列號(hào)，序列號(hào)要連續(xù)輸入，不需要用“-”進(jìn)行連接。輸入完畢、檢查無(wú)誤后，保存文件并退出。
現(xiàn)在將“Setup.ini”文件改回成“只讀”屬性，然后將該文件夾的內(nèi)容刻錄成光盤(pán)，以后再需要安裝office 2003時(shí)，只需雙擊該光盤(pán)“Pro”文件夾下的“Setup.exe”文件，office 2003就會(huì)自動(dòng)打開(kāi)一個(gè)已經(jīng)填好了序列號(hào)的界面要求你確認(rèn)，單擊“下一步”后會(huì)出現(xiàn)一個(gè)填好了用戶(hù)名、單位名稱(chēng)的界面，你只需再單擊“下一步”按鈕即可，非常方便吧！
給數(shù)據(jù)添加上單位
用Excel制表，經(jīng)常需要給單元格中的數(shù)據(jù)添加上單位，例如單位全年財(cái)政預(yù)算報(bào)表，就需要給單元格數(shù)據(jù)添加上單位“元”。遇到這種情況，很多朋友都是手工添加單位符號(hào)的，甚是煩瑣。在Excel報(bào)表中，我們是實(shí)現(xiàn)自動(dòng)為單無(wú)格數(shù)據(jù)添加單位的。
選中所有需要添加單位的單元格，然后單擊“格式/單元格”，在彈出的“單元格格式”對(duì)話框中，切換到“數(shù)字”選項(xiàng)卡，在“分類(lèi)”列表框中點(diǎn)選“自定義”，在“類(lèi)型”列表框中選擇自己需要的數(shù)據(jù)格式，如“0.00”，最后在“類(lèi)型”文本框中數(shù)據(jù)格式的后面添加中需要的數(shù)量單位，如“元”，完成后點(diǎn)擊“確定”按鈕。
好了，現(xiàn)在在Excel中一一錄入數(shù)據(jù)，Excel就會(huì)為各單元格自動(dòng)添加上數(shù)量單位了，可謂是一勞永逸，十分方便。
用Excel制作自動(dòng)評(píng)分計(jì)算表
[文章導(dǎo)讀] Excel自動(dòng)評(píng)分計(jì)算表功能：去掉最高分和最低分后，求出平均分，根據(jù)平均分的高低排定選手的名次……
Excel自動(dòng)評(píng)分計(jì)算表功能： 參加比賽的選手為20人，評(píng)委9人，去掉1個(gè)最高分和1個(gè)最低分后，求出平均分，然后根據(jù)平均分的高低排定選手的名次。
本實(shí)例所涉及到的函數(shù)有：
SUM(Number1,Number2……)
MAX(number1,number2……)
MIN(number1,number2……)
RANK(Number,ref,order)
一、評(píng)委評(píng)分表的制作
1、啟動(dòng)Excel2003，新建一空白工作簿。
2、在Sheet1工作表中，仿照如圖1所示的樣式，制作一份空白表格。
 3、執(zhí)行“文件→保存”命令（或按“Ctrl+S”組合鍵），打開(kāi)“另存為”對(duì)話框（如圖2）。
 4、單擊工具條上“工具”按鈕，在隨后出現(xiàn)的下拉列表（參見(jiàn)圖2）中，選擇“常規(guī)選項(xiàng)”，打開(kāi)“保存選項(xiàng)”對(duì)話框（如圖3），設(shè)置好“打開(kāi)權(quán)限密碼”后，確定返回。  注意：①密碼需要重新確認(rèn)輸入一次。②此處只需要設(shè)置“打開(kāi)權(quán)限密碼”，如果設(shè)置了“修改權(quán)限密碼”，則評(píng)委在保存評(píng)分時(shí)，必須提供密碼，反而造成不須要的麻煩。
5、然后取名（如1.xls）保存。
6、再執(zhí)行“文件→另存為”命令，再次打開(kāi)“另存為”對(duì)話框，然后仿照上面的操作重新設(shè)置一個(gè)密碼后，另取一個(gè)名稱(chēng)（如2.xls）保存一下。
7、重復(fù)第6步的操作，按照評(píng)委數(shù)目，制作好多份工作表（此處為9份）。
用Excel巧妙設(shè)置定時(shí)提醒 小余在交易中心工作，經(jīng)常要打電話通知相應(yīng)的評(píng)委參加工程評(píng)標(biāo)工作，有時(shí)工作一忙，就忘記通知而影響正常開(kāi)標(biāo)。 由于他每天都用Excel為登記相應(yīng)的數(shù)據(jù)，于是，我就用Excel為他設(shè)置了一個(gè)定時(shí)提醒的功能。文章末尾提供原文件供大家下載參考。 1、啟動(dòng)Excel，新建一文檔，取名保存（如“提醒.xls”）。
2、按“Alt+F11”組合鍵進(jìn)入VBA編輯狀態(tài)（如圖1）。
�。▓D片較大，請(qǐng)拉動(dòng)滾動(dòng)條觀看）
3、在左側(cè)“工程資源管理器”窗口中，選中“VBAProject(提醒.xls)”選項(xiàng)，執(zhí)行“插入模塊”命令，插入一個(gè)新模塊（模塊1，參見(jiàn)圖1）。
4、將下述代碼輸入到右側(cè)的編輯區(qū)域中：
Sub auto_open() '設(shè)置一個(gè)自動(dòng)運(yùn)行的宏——auto_open
Application.OnTime TimeValue("08:30:00"), "tx" '設(shè)置提醒時(shí)間——08:30:00（可以根據(jù)需要修改），設(shè)置調(diào)用提醒宏——tx。
Application.OnTime TimeValue("09:30:00"), "tx" '再設(shè)置提醒一次。
End Sub
Sub tx() '設(shè)置一個(gè)顯示提醒消息的宏（名稱(chēng)要與上述自動(dòng)運(yùn)行宏中調(diào)用的名稱(chēng)一致）。
msg = MsgBox("通知張三、李四、王五評(píng)委參加下午評(píng)標(biāo)！", vbInformation, "趕快打電話") '設(shè)置提醒對(duì)話框中的消息和標(biāo)題（前面是消息內(nèi)容，后面是對(duì)話框標(biāo)題，請(qǐng)根據(jù)需要輸入）。
End Sub
注意：①上述代碼中，英文單引號(hào)及其后面的字符是對(duì)代碼的解釋?zhuān)趯?shí)際輸入時(shí)可以不需要（參見(jiàn)圖1）。②如果設(shè)置多個(gè)提醒宏（tx1、tx2……），并在自動(dòng)運(yùn)行宏中分別設(shè)置調(diào)用的時(shí)間，可以實(shí)現(xiàn)多個(gè)消息的定時(shí)提醒。
5、輸入完成后，退出VBA編輯狀態(tài)，保存工作簿文檔。
6、以后，一上班，就啟動(dòng)Excel，打開(kāi)上述工作簿文檔。設(shè)定時(shí)間一到，系統(tǒng)即刻彈出如圖2所示的提示框，這下不會(huì)忘記了吧。原文件下載
用Excel實(shí)現(xiàn)定時(shí)提醒 如果您從事設(shè)備管理工作，有近千臺(tái)機(jī)械設(shè)備需要定期進(jìn)行精度檢測(cè)，那么，就得每天翻閱“設(shè)備鑒定臺(tái)賬”來(lái)尋找“到期”的設(shè)備——實(shí)在是太麻煩了！用Excel建立一本“設(shè)備鑒定臺(tái)賬”是不是方便得多？方法是：用Excel的IF函數(shù)嵌套TODAY函數(shù)來(lái)實(shí)現(xiàn)設(shè)備“到期”自動(dòng)提醒。
首先，運(yùn)行Excel，將“工作簿”的名稱(chēng)命名為“設(shè)備鑒定臺(tái)賬”，輸入各設(shè)備的詳細(xì)信息、上次鑒定日期及到期日期（日期的輸入格式應(yīng)為“年-月-日”，如：2003-10-21，如圖1）。
圖1
然后，選中圖1所示“提示欄”下的F2單元格，點(diǎn)擊插入菜單下的函數(shù)命令，在“插入函數(shù)”對(duì)話框中選擇“邏輯”函數(shù)類(lèi)中的IF函數(shù)，點(diǎn)擊[確定]按鈕，就會(huì)彈出“函數(shù)參數(shù)”對(duì)話框，分別在Logical_test行中輸入E2=TODAY()、value_if_true行中輸入“到期”、Value_if_false行中輸入“" "”(如圖2)，并點(diǎn)擊[確定]按鈕。這里需要說(shuō)明的是：輸入的 "" 是英文輸入狀態(tài)下的雙引號(hào)，是Excel定義顯示值為字符串時(shí)的標(biāo)識(shí)符號(hào)，即IF函數(shù)在執(zhí)行完真假判斷后顯示此雙引號(hào)中的內(nèi)容。為了醒目，可在“單元格屬性”中將F2單元格的字體顏色設(shè)置為紅色。
圖2
最后，拖動(dòng)“填充柄”，填充F列以下單元格即可。
我們知道Excel的IF函數(shù)是一個(gè)“條件函數(shù)”，它的語(yǔ)法是“IF(logical_test,value_if_true,value_if_false)”，具體地說(shuō)就是：如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真，則執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個(gè)參數(shù)Value_if_false的結(jié)果；Excel的TODAY函數(shù)［語(yǔ)法是TODAY()］是返回當(dāng)前系統(tǒng)日期的函數(shù)。中4-5-31 15:43:04
實(shí)際上，本文所應(yīng)用的IF函數(shù)語(yǔ)句為IF(E2=TODAY(),"到期","")，解釋為：如果E2單元格中的日期正好是TODAY函數(shù)返回的日期，則在F2單元格中顯示“到期”，否則就不顯示，TODAY函數(shù)返回的日期則正好是系統(tǒng)當(dāng)天的日期。
Excel的到期提醒功能就是這樣實(shí)現(xiàn)的。
Excel 控制重算的方式
計(jì)算是指進(jìn)行公式運(yùn)算，然后在包含此公式的單元格中以數(shù)值形式顯示出運(yùn)算結(jié)果的過(guò)程。在一般情況下，當(dāng)在單元格中輸入一個(gè)公式時(shí)，如果相關(guān)的單元格已經(jīng)準(zhǔn)備好數(shù)據(jù)，則Excel將自動(dòng)計(jì)算出結(jié)果，并顯示在單元格中；如果相關(guān)的單元格沒(méi)有準(zhǔn)備好數(shù)據(jù)，則Excel將在單元格中顯示“0”。
實(shí)際上，可以將工作表設(shè)定為“人工重算”模式，以便在輸入工作表時(shí)，提高數(shù)據(jù)的輸入速度。設(shè)置手動(dòng)計(jì)算的步驟如下：
（1）執(zhí)行“工具”菜單中的“選項(xiàng)”命令，出現(xiàn)選項(xiàng)對(duì)話框。
（2）單擊“重新計(jì)算”選項(xiàng)卡，出現(xiàn)如圖7-10所示的對(duì)話框。
（3）選定其中的“人工重算”，按下“確定”按鈕。
|
關(guān)注樓主