5.6 青年學科帶頭人初選表

案例背景
    根據(jù)上級主管部門的指派，學校經(jīng)常要根據(jù)不同要求上報有關全校教師隊伍的整體結構情況的統(tǒng)計報表，有的時候不僅需要統(tǒng)計數(shù)字，還需要查看具體是那些教師滿足要求，比如說教育局在2007年春季布置評選市、區(qū)級青年學科帶頭人，要求年齡條件為：男性教師在40歲以下，女性教師在35歲以下（截止到2007年8月31日以前，男性教師不超過40歲，女性教師不超過35歲）；要求學歷條件在本科以上，情況基本相同的學歷高的優(yōu)先考慮，職稱要求為“一級”或“高級”教師，其中，市級青年學科帶頭人要求具有高級職稱。這時候負責這項工作的同志就需要在全校教師情況登記表中篩選中滿足基本條件的教師作為候選教師參加初選。前面已經(jīng)學習了數(shù)據(jù)的自動篩選，自動篩選可以實現(xiàn)在同一字段內(nèi)的“與”和“或”的篩選，在不同字段間只能實現(xiàn)“與”的篩選，不能實現(xiàn)“或”的篩選，那么如何進行類似本案例中的多條件篩選呢？本案例以某中學根據(jù)教育局的部署對本校全體教師按照基本條件進行初選為例，應用Excel高級篩選技術來實現(xiàn)上述的多條件篩選。
關鍵技術點
    要實現(xiàn)本案例中的功能，學員應該掌握以下EXCEL技術點。
    ●基礎知識 數(shù)字的日期格式
    ●函數(shù)應用 YEAR函數(shù)，MONTH函數(shù) ，IF函數(shù) ，
    ●綜述 高級篩選
最終效果展示

5.6.1創(chuàng)建“市級青年學科帶頭人初選表”
Step 1創(chuàng)建工作簿、重命名工作表
創(chuàng)建工作簿“學科帶頭人初選表.xls”，然后將工作表“Sheet2“和”Sheet1“重命名為“區(qū)級“和”市級“。

Step 2輸入表格字段名
單擊“市級”工作表標簽，在單元格區(qū)域A1:H1輸入字段名：“編號“，”性別“，”出生年月日“，”職稱“，”現(xiàn)學歷“，”學科“，”參評年齡“。

Step 3輸入教師原始信息
在單元格區(qū)域A2:G58輸入全校教師的原始信息。

Step4由教師“出生年月日“換算“參評年齡“
①選中單元格H2，在編輯欄輸入以下公式，然后按鍵確認，即可換算出第一個教師的“參評年齡“。
“=IF(MONTH(D2)<9,2007-YEAR(D2),2007-YEAR(D2)-1) “

②雙擊單元格H2右下角的填充柄即可完成其余56名教師 “參評年齡“的換算。

Step5修飾美化教師原始信息表
①選中單元格區(qū)域A1:H58，適當調(diào)整列寬，設置表格邊框，文字居中對齊，字段名區(qū)域自動換行并添加灰色底紋。
②選中單元格區(qū)域D2:D58，按組合鍵彈出“單元格格式”對話框，切換到“數(shù)字”選項卡，在“分類”選項框中選擇“日期”，在“類別”選項框中選擇“*2001-3-14”

③單擊“確定”按鈕。

Step6輸入“高級篩選“條件
①選中單元格區(qū)域E60:H62，首先為該區(qū)域設置邊框及文本居中對齊，然后在E60:H60輸入字段名“性別”，“職稱”，“現(xiàn)學歷”和“參評年齡”；
②在單元格區(qū)域E61:H61輸入男教師參評市級青年學科帶頭人條件：“男”，“高級”，“<>"大專"”，“<=40”；

③在單元格區(qū)域E62:H62輸入女教師參評市級青年學科帶頭人條件：“女”，“高級”，“<>"大專"”，“<=35”；

Step7高級篩選
①選中單元格區(qū)域A1:H58內(nèi)任意單元格如D54，單擊菜單“數(shù)據(jù)”→“篩選”→“高級篩選”,彈出“高級篩選”對話框。

②在“高級篩選”對話框，“方式”列表框中選擇“將篩選結果復制到其他位置”，單擊“列表區(qū)域”框右側(cè)按鈕，用鼠標選擇單元格區(qū)域”$A$1: $H$58”, 單擊“條件區(qū)域”框右側(cè)按鈕，用鼠標選擇單元格區(qū)域$E$60: $H$62 , 單擊“復制到”框右側(cè)按鈕，用鼠標選擇單元格A64。

③單擊“確定”按鈕，即可篩選出符合市級青年學科帶頭人基本條件的教師初選名單

④在單元格A63中輸入標題“市級青年學科帶頭人初選表”，選中單元格區(qū)域A63:H63，單擊常用工具欄“合并及居中”。
5.6.2創(chuàng)建“區(qū)級青年學科帶頭人初選表”
Step 1輸入教師原始信息
選中A1:H58單元格區(qū)域，按組合鍵復制，單擊“區(qū)級”工作表標簽，單擊單元格A1，按組合鍵粘貼，然后適當調(diào)整列寬。

Step 2輸入高級篩選條件
①選中單元格區(qū)域E60:H64，首先為該區(qū)域設置邊框及文本居中對齊，然后在單元格區(qū)域E60:H60陸續(xù)輸入字段名“性別”，“職稱”，“現(xiàn)學歷”和“參評年齡”；
②在單元格區(qū)域E61:H61和E62:H62，分別輸入男教師參評市級青年學科帶頭人條件：“男”，“高級”，“<>"大專"”，“<=40”和“男”，“一級”，“<>"大專"”，“<=40”。

③在單元格區(qū)域E63:H63和E64:H64，輸入女教師參評市級青年學科帶頭人條件：“女”，“高級”，“<>"大專"”，“<=40”和“女”，“一級”，“<>"大專"”，“<=40”。

Step 3
參照6.2.1篩選市級青年學科帶頭人的過程通過高級篩選得到“區(qū)級青年學科帶頭人”的初選名單

至此通過使用“高級篩選”技術進行初選，符合市級和區(qū)級青年學科帶頭人基本條件的教師名單全部已經(jīng)列出，學?？筛鶕?jù)這些教師的能力，業(yè)績等因素綜合考慮選出本校最合適的人選推薦到區(qū)里參評。

關鍵知識點講解：
1.YEAR函數(shù)
函數(shù)名稱：YEAR
主要功能：返回某日期對應的年份。返回值為 1900 到 9999 之間的整數(shù)。
使用格式：YEAR(serial_number)
參數(shù)說明：
Serial_number    為一個日期值，其中包含要查找年份的日期。應使用 DATE 函數(shù)來輸入日期，或者將日期作為其他公式或函數(shù)的結果輸入。例如，使用 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本的形式輸入，則會出現(xiàn)問題。
函數(shù)說明
Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一個默認日期系統(tǒng)。
由 YEAR、MONTH 和 DAY 函數(shù)返回的值是 Gregorian 值，不管所提供的日期值是怎樣的顯示格式。例如，如果所提供的日期值顯示格式是 Hijri，YEAR、MONTH 和 DAY 函數(shù)返回的值將是對應 Gregorian 日期的值。
應用示例

2.MONTH函數(shù)
函數(shù)名稱：MONTH
主要功能：返回以序列號表示的日期中的月份。月份是介于 1（一月）到 12（十二月）之間的整數(shù)。
使用格式：MONTH(serial_number)
參數(shù)說明：
Serial_number    表示一個日期值，其中包含要查找的月份。應使用 DATE 函數(shù)來輸入日期，或者將日期作為其他公式或函數(shù)的結果輸入。例如，使用 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本的形式輸入，則會出現(xiàn)問題。
函數(shù)說明
Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一個默認日期系統(tǒng)。
由 YEAR、MONTH 和 DAY 函數(shù)返回的值為公歷值，而不用考慮所給日期值的顯示格式。例如，如果所給日期值的顯示格式為 Hijri，則 YEAR、MONTH 和 DAY 函數(shù)的返回值將是與等價的公歷日期相關的值。
應用示例

3.DAY函數(shù)
函數(shù)名稱：DAY
主要功能：返回以序列號表示的某日期的天數(shù)，用整數(shù) 1 到 31 表示。
使用格式：DAY(serial_number)
參數(shù)說明：
Serial_number    為要查找的那一天的日期。應使用 DATE 函數(shù)來輸入日期，或者將日期作為其他公式或函數(shù)的結果輸入。例如，可使用函數(shù) DATE(2008,5,23) 輸入日期 2008 年 5 月 23 日。如果日期以文本的形式輸入，則會出現(xiàn)問題。
函數(shù)說明
Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一個默認日期系統(tǒng)。
不論日期值以何種格式顯示，YEAR，MONTH 和 DAY 函數(shù)返回的值都是 Gregorian 值。例如，如果日期的顯示格式是回歷，則 YEAR、MONTH 和 DAY 函數(shù)返回的值將是與相同的 Gregorian 日期相關聯(lián)的值。
應用示例：

高級篩選：
使用高級條件篩選
    1.在可用作條件區(qū)域的區(qū)域上方插入至少三個空白行。條件區(qū)域必須具有列標簽。請確保在條件值與區(qū)域之間至少留了一個空白行。
外觀示例

    2.在列標志下面的一行中，鍵入所要匹配的條件。
條件示例
單列上具有多個條件
    如果對于某一列具有兩個或多個篩選條件，那么可直接在各行中從上到下依次鍵入各個條件。例如，下面的條件區(qū)域顯示“職稱”列中包含“高級”或“一級”的行。

多列上具有單個條件
    若要在兩列或多列中查找滿足單個條件的數(shù)據(jù)，請在條件區(qū)域的同一行中輸入所有條件。例如，下面的條件區(qū)域?qū)@示所有在“性別”列中包含“男”、在“職稱”列中包含“高級”在“現(xiàn)學歷”列中包含“高級”，在“參評年齡”小于等于 40 的數(shù)據(jù)行。

某一列或另一列上具有單個條件
    若要找到滿足一列條件或另一列條件的數(shù)據(jù)，請在條件區(qū)域的不同行中輸入條件。例如，下面的條件區(qū)域?qū)@示所有在“性別”列中為“男”、或“職稱”列為“高級”或“參評年齡”<于 40的行。（準確的說，在同一行不同字段的條件是“與（AND）”的關系；不在同一行的條件是“或（OR）”的關系），根據(jù)下面的高級篩選條件篩選出來的結果是男性初級教師或具有高級職稱的教師或年齡不足40歲的教師

兩列上具有兩組條件之一
    若要找到滿足兩組條件（每一組條件都包含針對多列的條件）之一的數(shù)據(jù)行，請在各行中鍵入條件。例如，下面的條件區(qū)域?qū)@示所有在“性別”列中包含“男”且“職稱”是“初級“或“高級”的教師。

一列有兩組以上條件
    若要找到滿足兩組以上條件的行，請用相同的列標包括多列。例如，下面條件區(qū)域顯示年齡介于 30 和 40 之間以及年齡超過55的教師。

    可以將公式的計算結果作為條件使用。用公式創(chuàng)建條件時，不要將列標簽作為條件標簽使用；應該將條件標簽置空，或者使用區(qū)域中的非列標簽。例如，下面的條件區(qū)域顯示在列 C 中，其值大于單元格區(qū)域 C7:C10 平均值的行。

注釋
    ●用作條件的公式必須使用相對引用來引用列標簽（例如，“銷售”），或者引用第一個記錄的對應字段。公式中的其他所有引用都必須為絕對引用，并且公式的計算結果必須為 TRUE 或 FALSE。在本公式示例中，“C7”引用區(qū)域中第一個記錄（行 7）的字段（列 C）。
    ●可在公式中使用列標簽來代替相對單元格引用或區(qū)域名稱。當 Microsoft Excel 在包含條件的單元格中顯示錯誤值 #NAME? 或 #VALUE! 時，可忽略這些錯誤，因為它們不影響區(qū)域的篩選。
    ●Microsoft Excel 在計算數(shù)據(jù)時不區(qū)分大小寫。
所有以該文本開始的項都將被篩選。例如，如果您鍵入文本“Dav”作為條件，Microsoft Excel 將查找“Davolio”、“David”和“Davis”。如果只匹配指定的文本，可鍵入下面的公式，其中“text”是需要查找的文本。 =''=text''
如果要查找某些字符相同但其他字符不一定相同的文本值，則可使用通配符。
通配符
    以下通配符可作為篩選以及查找和替換內(nèi)容時的比較條件。

3.單擊區(qū)域中的單元格。
    4.在“數(shù)據(jù)”菜單上，指向“篩選”，再單擊“高級篩選”。
    5.若要通過隱藏不符合條件的數(shù)據(jù)行來篩選區(qū)域，請單擊“在原有區(qū)域顯示篩選結果”。
若要通過將符合條件的數(shù)據(jù)行復制到工作表的其他位置來篩選區(qū)域，請單擊“將篩選結果復制到其他位置”，然后在“復制到”編輯框中單擊鼠標左鍵，再單擊要在該處粘貼行的區(qū)域的左上角。
    6.在“條件區(qū)域”編輯框中，輸入條件區(qū)域的引用，并包括條件標志。
如果要在選擇條件區(qū)域時暫時將“高級篩選”對話框移走，請單擊“壓縮對話框”。
    7.若要更改篩選數(shù)據(jù)的方式，可更改條件區(qū)域中的值，并再次篩選數(shù)據(jù)。
提示
    ●您可以將某個區(qū)域命名為“Criteria”，此時“條件區(qū)域”框中就會自動出現(xiàn)對該區(qū)域的引用。您也可以將要篩選的數(shù)據(jù)區(qū)域命名為“Database”，并將要粘貼行的區(qū)域命名為“Extract”，這樣，這些區(qū)域就會相應地自動出現(xiàn)在“數(shù)據(jù)區(qū)域”和“復制到”框中。
    ●將篩選所得的行復制到其他位置時，可以指定要復制的列。在篩選前，請將所需列的列標復制到粘貼區(qū)域的首行。而當篩選時，請在“復制到”框中輸入對被復制列標的引用。這樣，復制的行中將只包含已復制過列標的列。
案例公式解析:
    因為“參評年齡“截止到2007年8月31日計算，因此由出生年月日換算出” 參評年齡“的公式設計是這樣考慮的，如果一個教師出生的月份1-8月之間，就用2007減去該教師的出生年，否則就用2007減去該教師的出生年，然后再減去1年。單元格H2中的公式為
“=IF(MONTH(D2)<9,2007-YEAR(D2),2007-YEAR(D2)-1) “
單元格D2中的日期為“1953-12-12“，公式化簡為
=IF（12<9,2007-YEAR(1953),2007-YEAR(1953)-1) “
公式繼續(xù)化簡為
“=IF（false,54,53) “
單元格H2返回“53”。