【正文】 經(jīng)典 Excel 表格使用手冊 函數(shù)公式解決篩選難題 某天，有一同事拿著一份成績表來找我尋求幫助。原來這是一份 Excel 格式的全市對口高考成績表，整個工作表中除了處于 A 列的準(zhǔn)考證號字段外，其他字段就是各科的考試成績以及總分成績?，F(xiàn)在的要求是從這份工作表中將本校學(xué)生的成績篩選出來。但現(xiàn)在的問題是當(dāng)初排準(zhǔn)考證號的時候是各個學(xué)校的學(xué)生間隔排列的，只知道本?？忌臏?zhǔn)考證號都是偶數(shù)。因此，只有把所有的偶數(shù)考生成績篩選出來，才有可能最后解決問題。 可是，怎樣才能把所有的偶數(shù)學(xué)生全部篩選出來呢？想了“分類匯總” 、“自動篩選”、“高級篩選”等可能的功能，但都沒有什么結(jié)果。總不能要我一行一行地逐個選取吧？這可是有著一千多考生的工作表呀，這么選的話，豈不把我的眼都要弄瞎了？不行，還得想個好辦法。 對了，既然要根據(jù)準(zhǔn)考證號來選取其中的偶數(shù)，那么是不是可以利用取余數(shù)函數(shù)來解決問題呢？ Excel 中 MOD（ Number， Divisor）函數(shù)可以返回兩數(shù)相除的余數(shù)，而拿準(zhǔn)考證號除 2，所得的余數(shù)不是 0 就是 1 嗎？這樣的話，我們再把余數(shù)為 0 的全部篩選出來，問題不就解決了嗎？ 但仔細看看，問題還有兩個：一 是準(zhǔn)考證號是首位為 0 的 14 位數(shù)，在 Excel 中這樣的數(shù)字運算起來是可能有其他問題出現(xiàn)的 ；第二個問題是準(zhǔn)考證號是文本格式，根本不能直接拿來運算。解決的辦法很快也找到了，第一個問題我們可以使 用 RIGHT 函數(shù)截取最后兩位數(shù)進行運算 ，這對于余數(shù)是沒有任何影響的。 用 RIGHT 函數(shù)截取出來的也是文本，所以，還得使用 VALUE 函數(shù)將它轉(zhuǎn)換成數(shù)字 。這樣，第二個問題也就不是問題了。 辦法有了，實現(xiàn)起來就簡單多了。在工作表的最后（ G 列）添加“識別符”字段。點擊G2 單元格，輸入如下公式： =MOD(VALUE(RIGHT(A3,2)),2)，按下回車 鍵。您看，結(jié)果是不是出來了？選中該單元格，將鼠標(biāo)移到單元格的填充句柄處，向下拖動至最后一行，自動計算出全部結(jié)果。剩下的事情就是在這一列中篩選出所有為 0 結(jié)果，我就不再羅嗦了。 EXCEL 中高級篩選條件的寫法 EXCEL 電子表格軟件的高級篩選是一項功能非常好用的數(shù)據(jù)篩選功能，利用高級篩選，可以從數(shù)據(jù)清單中按照某些復(fù)雜的條件來查找符合條件的記錄，它的操作過程很簡單，但操作的關(guān)鍵是寫好篩選條件，下面就來講一講如何書寫篩選條件。 書寫篩選條件時要先劃分一片條件區(qū)域，條件區(qū)域可以選擇數(shù)據(jù)清單以外的任何空白處， 只要空白的空間足以放下所有條件就可以。寫條件時要遵守的規(guī)則是： 要在條件區(qū)域的第一行寫上條件中用到的字段名，比如要篩選數(shù)據(jù)清單中 “年齡 ”在 30 歲以上， “學(xué)歷 ”為本科的職員，其中 “年齡 ”和 “學(xué)歷 ”是數(shù)據(jù)清單中對應(yīng)列的列名，稱作字段名，那么在條件區(qū)域的第一行一定是寫這兩個列的名稱（字段名），即 “年齡 ”和 “學(xué)歷 ”，而且字段名的一定要寫在同一行。 在字段名行的下方書寫篩選條件，條件的數(shù)據(jù)要和相應(yīng)的字段在同一列，比如上例中年齡為 30 歲，則 “30”這個數(shù)據(jù)要寫在條件區(qū)域中 “年齡 ”所在列，同時 “本科 ”要寫 在條件區(qū)域中 “學(xué)歷 ”所在的列。 那么在寫條件時，一般都同時有多個條件，這些條件是個什么關(guān)系，又怎么在條件區(qū)域中分布呢？ 我們平時所用的邏輯條件，如果有多個，它們之間總是會存在兩種關(guān)系，一種是 “或 ”關(guān)系，一種是 “與 ”關(guān)系。 “或 ”關(guān)系是指幾個條件中只需有一個成立，結(jié)果就會成立的情況，比如一句話 “小張和小王，你們誰來把你們的郵件取回去。 ”，這樣表達的是他們兩個人的郵件要往回取， “去人取 ”是條件， “取回郵件 ”是結(jié)果。這里存在三個條件，一個是 “小張去取 ”，一個是 “小王去取 ”，再一個是 “兩個人都去 ”，那么 在這三個條件中，只要有一個成立，結(jié)果 “取回郵件 ”就會成立，那么這些條件之間的關(guān)系就是 “或 ”關(guān)系，當(dāng)然在考慮這個關(guān)系的時候，我們只要記作 “小張去取或小王去取 ”就可以了，表示兩個條件中，只要有一個成立，結(jié)果就成立，當(dāng)然兩個都成立，結(jié)果更會成立了。 “與 ”關(guān)系是指在幾個條件中，必須這些條件都成立，結(jié)果才成立的情況，比如一件事 “如果明天不下雨也不刮風(fēng)，那我就出門 ”，這里有兩個條件，一個是 “不下雨 ”，一個是 “不刮風(fēng) ”，只有這兩個條件都成立，結(jié)果 “我出門 ”才會有，那么我們就稱這兩個條件為 “與 ”關(guān)系?？梢杂涀?“不下雨 與不刮風(fēng) ”，表示兩個條件要同時成立結(jié)果才會成立。 那么回到高級篩選中，在具體寫條件時，我們要分析好條件之間是與關(guān)系還是或關(guān)系，如果是與關(guān)系，這些條件要寫到同一行中，如是是或關(guān)系，這些條件要寫到不同的行中，也就是說不同行的條件表示或關(guān)系，同行的條件表示與關(guān)系。 說這么多，不知你聽著有什么感覺，拿上面舉的那個例子來說， “年齡在 30 歲以上 ”和 “學(xué)歷是本科 ”是兩個條件，在題目的含義中我們看出這兩個條件要同時成立，是與關(guān)系，所以條件區(qū)域?qū)懛ㄈ缦卤? 年齡 學(xué)歷 30 本科 把兩個條件寫在一行 ，表示他們是與關(guān)系。 這里第一個條件 “30”寫的時候在前面加了一個 “”號，表示在 30 以上，我們平時用的不等關(guān)系有大于、小于等幾種，它們使用的符號如下表所示： 大于 小于 大于等于 = 小于等于 = 不等于 如果是等于關(guān)系，則直接寫值，不需加符號。 另外要注意的是，這些符號必須是英文半角符號，也就是說最好在英文狀態(tài)下輸入，不可以是全角符號，否則 EXCEL 不會認(rèn)識它們。 下面舉幾個具體的實例，看一下篩選條件的寫法： 選擇出語文、數(shù)學(xué)、英語三門 成績中至有少一門不及格的學(xué)生。 分析：三門課程中至少有一門課程不及格，我們可以這樣理解：一位學(xué)生，如果語文不及格就選出來，或者數(shù)學(xué)、或者英語不及格都把他們選出來，那么這樣三個條件，很顯然是或關(guān)系。條件區(qū)域?qū)懛ㄈ缦拢? 語文 數(shù)學(xué) 英語 60 60 60 篩選出語文、數(shù)學(xué)、英語三門課程都及格的學(xué)生。 分析：三門課程都及格，要求語文、數(shù)學(xué)、英語同時在 60 分以上，這些條件是與關(guān)系。條件區(qū)域?qū)懛ㄈ缦拢? 語文 數(shù)學(xué) 英語 =60 =60 =60 篩選出語文、數(shù)學(xué)、英語三門課程中只有一門不及格的學(xué)生。 分析：只有一門不及格，我們可以這樣考慮：如果語文不及格，肯定要數(shù)學(xué)、英語及格，如果數(shù)學(xué)不及格，肯定要語文、英語及格，如果要英語不及格，肯定要語文、數(shù)學(xué)及格。這樣三個大條件，每個大條件中又包含三個小條件，這些條件是個什么關(guān)系呢，我們先分析其中的一個大條件 “語文不及格，數(shù)學(xué)英語及格 ”，這個大條件中有三個小條件，三個小條件要同時成立才符合我們的要求，所以它們之間是與關(guān)系，那么三個條件之間只要有一個成立，該學(xué)生就是只有一門不及格者，所 以它們之間是或關(guān)系，這樣我們寫出條件區(qū)域如下所示： 語文 數(shù)學(xué) 英語 60 =60 =60 =60 60 =60 =60 =60 60 篩選出 “系別 ”為英語系、中文系， “年齡 ”在 20 歲以上的 “籍貫 ”是北京、天津的學(xué)生。 此條件區(qū)域?qū)懛ㄈ缦?，讀者可以自己分析條件的含義。 系別 年齡 籍貫 英語系 20 北京 英語系 20 天津 中文系 20 北京 中文系 20 天津 其實條件寫法并不難，只要你 把所需的問題分解成最小的邏輯條件，把條件之間的邏輯關(guān)系弄清楚，就可以寫出正確的條件區(qū)域。 彈指之間輸入特定文本 Excel讓我們能夠輕松處理各種各樣的表格，大大提高了我們辦公準(zhǔn)備就緒，但有時常常要輸入一些特定的文本（如單位名稱等），有沒有什么快捷的方法來準(zhǔn)確無誤地輸入這些特定文本，其實 Excel提供了多種方法來方便我們實現(xiàn)高效輸入特定文本。 Office 擁有更多強大的剪貼板，我們可以利用這一功能反復(fù)輸入一些特定的文本。首先把要多次輸入的文本復(fù)制到剪貼板，在要使用時就可在右邊的剪貼板窗口 中選取要輸入的文本即可。 Excel工作表中可能要多次輸入相同的文本，尤其是如果要在多處使用一些特殊符號的話，那就非常麻煩，不過以通過 “編輯 ”菜單中的 “查找 ”功能來實現(xiàn)。具體操作方法：首先在要輸入這些符號的單元格中輸入一個代替的字母或單詞（如 WMG 注意：該字母 /單詞不能與其他單元格中的相同），然后直接按 Ctrl+H 快捷鍵來打開 “替換 ”對話框，在 “查找內(nèi)容 ”欄內(nèi)輸入代替的東東 “WMG”，在 “替換為 ”欄內(nèi)輸入 “智創(chuàng)工作室 亦可 ”，再按 “替換全部 ”按鈕即可。 （圖一） OFFICE 提供了 “自動更正 ”功能，通過 “工具 ”菜單中的 “自動更正選項 ”打開自動更正窗口，在 “替換 ”欄內(nèi)填入 “WMG”，在 “替換為 ”欄內(nèi)填入 “智創(chuàng)工作室 亦可 ”，單擊 “添加 ”后按 “確定 ”返回。以后只要在單元格中輸入 “WMG”以及后續(xù)文本或者按 “Enter”鍵后，系統(tǒng)會自動將其更正為 “智創(chuàng)工作室 亦可 ”。 （圖二） ，先按住 Ctrl鍵的同時，用鼠標(biāo)左鍵選定多個不連續(xù)的單元格（或區(qū)域），然后輸入文本，再按下 “Ctrl+Enter”快捷鍵，這樣就可將該文本同時輸入上述選定的單元格中。 對于經(jīng)常需要輸入的一些單位的名稱（如 “婁底市電 廣網(wǎng)絡(luò)公司 ”， “婁底市信息網(wǎng)絡(luò)公司 ”等），通常也可用 Excel提供的 CONCATENATE 函數(shù)（功能：將多個文本字符串合并成一個）來實現(xiàn)這些特定文本的輸入。具體操作方法：假設(shè)在 A1 和 A2 單元格已經(jīng)輸入了 “婁底市 ”和 ”網(wǎng)絡(luò)公司 ”，而我們要在 C1中輸入 “某某市電廣網(wǎng)絡(luò)公司 ”，那就在 C1 單元格中輸入公式： =CONCATENATE(A1,D2,A2)，而以后我們只要在 D2 單元格中輸入 “電廣 ”，則 C1 單元格中將自動填充文本 “婁底市電廣網(wǎng)絡(luò)公司 ”，若在D2 中輸入 “信息 ”則 C1自動填充為： “婁底市信息網(wǎng)絡(luò)公司 ”。 假設(shè)要需要輸入的文本在同一列中前面已經(jīng)輸入過，并且該列中沒有空單元格，我們就可以右擊下面的單元格，選 “選擇列表 ”選項，則上面輸入過的文本以下拉菜單形式出現(xiàn)，用左鍵選中你需要的文本，即可將其快速輸入到選定的單元格中。 （如 “萬元 ”等），這時我們就可通過 “自定義 ”單元格格式的方法來達到自動給數(shù)值添加單位的目的：我們選中需要添加單位的單元格，點擊 “格式 ”菜單中的 “單元格 ”命令即打開了 “單元格格式 ”對話框，切換到 “數(shù)字 ”選項卡片中，選中 “分類 ”列表框中的 “自定義 ”，然后在 “類型 ”欄內(nèi)輸入 “萬元 ”，單擊 “確定 ”按鈕返回，以后在上述單元格中輸入數(shù)值后即會自動添加 “萬元 ”兩字在其后面，不過此格式定義法僅僅能夠適用于數(shù)值。 （圖三） 有了這些快速輸入文本的方法后，工作效率是不是提高啦！ 聽著就行 讓 Excel 報賬 許多人經(jīng) 常要用 Excel來統(tǒng)計一些數(shù)據(jù)，統(tǒng)計完成后還要對數(shù)據(jù)進行校對，但是這么多數(shù)據(jù)怎樣才能不出差錯地進行校對呢？其實我們可以用一下 Excel自帶的 “文本到語音 ”功能，讓軟件通過發(fā)聲自動給你報賬。 打開 Excel，點擊 “工具 → 語音 → 顯示文本到語音工具欄 ”，由于這個功能在安裝 Excel的時候不是默認(rèn)安裝的，所以在第一次使用時會提示插入 Office 安裝光盤來安裝這個功能。 安裝完成后打開 “文本到語音 ”工具欄，先在數(shù)據(jù)文件中用鼠標(biāo)選擇要朗讀的第一個數(shù)據(jù)，接著根據(jù)數(shù)據(jù)的排列情況來選擇是 “按行 ”還是 “按列 ”來進行朗 讀，設(shè)置完成后點擊工具欄最左面的 “朗讀單元格 ”按鈕就可以了。在朗讀的時候被朗讀到的單元格會以選中狀態(tài)顯示，而且還可以流利地朗讀中文，英文是一個字母一個字母朗讀的，這樣就可以校對英文單詞的拼寫是否正確。 （圖 1） 該功能還可以一邊輸入數(shù)據(jù)一邊來進行語音校對，只要點擊一下工具欄最右面的 “按回車開始朗 讀 ” 按鈕，使其為選中狀態(tài)，這樣在完成一個單元格的輸入后按回車， Excel就會自動來朗讀這個單元格中的內(nèi)容。 （圖 2） 一般朗讀默認(rèn)的是男聲的，如果你聽膩了，可以通過系統(tǒng)里的設(shè)置把男聲改為女聲，打開控制面板中的 “語音 ”項，在里面的 “語音選擇 ”來選擇一下語音，然后點擊下面的 “預(yù)覽聲音 ”就可以聽效果了 ，而且還可以設(shè)置語音的朗讀速度，完成后點擊 “確定 ”按鈕就可以了。 將 Excel 的文本數(shù)字轉(zhuǎn)換成數(shù)字 在 Excel中，系統(tǒng)將前面帶有半角單引號的數(shù)字視為文本對待，而且為了某些需要，用戶可以通過 “格式 ”菜單中的 “單元格 ”命令，將數(shù)字設(shè)置為文本格式。然而，現(xiàn)在我們卻需要把工作表中文本格式的數(shù)字轉(zhuǎn)換成數(shù)字，那么以下方法可以一試。 1．一次轉(zhuǎn)換一個單元格 在 “工具 ”菜單上，單擊 “選項 ”，再單擊 “錯誤檢查 ”選項卡（一定要確保選中了 “允許后臺錯誤檢查 ”和 “數(shù)字以文本形