用ecel制作一份萬年歷

用EXCEL制作一份萬年歷

今天我們介紹用Excel制作萬年歷的方法。這個(gè)萬年歷可以顯示當(dāng)月的月歷，還可以

隨意查閱任何日期所屬的月歷，非常方便。如果你愿意，還可以讓它在特殊的日子里顯示不

同的提醒文字，一起來試試吧！

本文所涉及到的函數(shù)有：

1、AND(logicall,logical2,...)

2^DATE(year,month,day)

3、DAY(serialnumber)

4、IF(Logical,Value_if^true,Value_if^false)

5、INT(number)

6、MONTH(serial_number)

7、NOW()

8、OR(logicall,logical2,...)

1、啟動(dòng)EXCEL2003,新建一個(gè)工作表，取名保存(如萬年歷.xls),并在相應(yīng)的單元

格中，輸入如圖1所示的文本。

2、同時(shí)選中Bl、Cl、D1單元格，按“格式”工具欄上的“合并及居中“按鈕，將其合并

成一個(gè)單元格，并輸入公式：=TODAY（）o

選中B1（合并后的）單元格，執(zhí)行“格式一單元格“命令，打開“單元格格式”對(duì)話框（如

圖2），在“數(shù)字”標(biāo)簽中的“分類”下面選中“日期”選項(xiàng)，再在右側(cè)“類型”下面選中“二。。一年

三月十四日“選項(xiàng)，“確定”退出，將日期設(shè)置成中文形式。

單元格格式0?

注意：TODAY()函數(shù)用于提取當(dāng)前系統(tǒng)日期，請將系統(tǒng)日期?定要調(diào)整準(zhǔn)確喲。

3、選中F1單元格，輸入公式：=IF(WEEKDAYS1,2尸7,"日",WEEKDAY(B1,2))：選

中H1單元格，輸入公式：=NOW()o

選中FI單元格，打開“單元格格式”對(duì)話框，在“數(shù)字”標(biāo)簽中的“分類”下面選中“特殊”

選項(xiàng)，再在右側(cè)“類型”下面選中“中文小寫數(shù)字”選項(xiàng)，“確定”退出，將“星期數(shù)”設(shè)置成中文

小寫形式;選中H1單元格，打開“單元格格式”對(duì)話框，在“數(shù)字”標(biāo)簽中的“分類”下面選中“時(shí)

間”選項(xiàng)，再在右側(cè)“類型”下面選中一款時(shí)間格式，“確定”退出。

注意：①上述前面一個(gè)公式的含義是：如果(IF)當(dāng)前日期(B1)是星期“7”

(WEEKDAY(B1,2)=7),則在Fl單元格中顯示“日”，否則，直接顯示出星期的數(shù)值

(WEEKDAY(B1,2)).

②上述第二個(gè)函數(shù)(NOW())用于提取當(dāng)前系統(tǒng)日期和時(shí)間，也請將系統(tǒng)日期和時(shí)

間調(diào)整準(zhǔn)確。

4、在II、12單元格分別輸入1900、1901,然后同時(shí)選中II、12單元格，用“填充柄”

向下拖拉至1151單元格，輸入1900—2050年份序列。

同樣的方法，在JI至J12單元格中輸入1—12月份序列。

5、選中D13單元格，執(zhí)行“數(shù)據(jù)一有效性”命令，打開“數(shù)據(jù)有效性”對(duì)話框（如圖3）,

按“允許”右側(cè)的下拉按鈕，選中“序列”選項(xiàng)，在“來源”下面的方框輸入：=$1$1:$1$151,“確

定”退出。

同樣的操作，將F15單元格數(shù)據(jù)有效性設(shè)置為“=$J$1:$J$12”序列。

注意：經(jīng)過這樣的設(shè)置以后，當(dāng)我們選中D15（或F15）單元格時(shí)，在單元格右側(cè)出

現(xiàn)一個(gè)下拉按鈕，按此下拉按鈕，即可選擇年份（或月份）數(shù)值，快速輸入需要查詢的年、

月值。

6、選中A2單元格（不一定非得是A2喲），輸入公式：

=IF(F13=2,IF(0R(D13/400=INT(D13/400),AND(D13/4=INT(D13/4),D13/100olNT(D13/100)))

,29,28),IF(OR(F13=4,F13=6,F13=9,F13=11),30,31)),用于獲取查詢“月份”所對(duì)應(yīng)的天數(shù)(28、

29、30、31)。

注意：上述函數(shù)的含義是：如果查詢“月份”為“2月”(F13=2)時(shí)，并且“年份”數(shù)能被

400整除[D13/400=INT(D13/400)],或者(OR)“年份”能被4整除，但不能被100整除

[AND(D13/4=INT(D13/4),D13/100<>INT(D13/100))],則該月為29天(也就是我們通常所說

的“閏年”)，否則為28天。如果“月份”不是2月,但是“4、6、9、11”月,則該月為30天。

其他月份天數(shù)為31天。

7、選中B2單元格，輸入公式：=IF(WEEKDAY(DATE($D$13,$F$13,1),2)=B3,1,0)?再

次選中B2單元格，用“填充柄”將上述公式復(fù)制到C2—H2單元格中。

注意：①上述B2公式的含義是：如果“查詢年月”的第1天是星期“7”

(WEEKDAY(DATE)($DS13,$F$13,1),2)=B3)時(shí)，在該單元格顯示“1”，反之顯示“0”)，為

“查詢年月”獲取一個(gè)對(duì)照值，為下面制作月歷做準(zhǔn)備。

②上述C2—H2單元條中公式的含義與B2相似。

③在用拖拉法復(fù)制公式時(shí)，公式“絕對(duì)引用”的單元格(加了“$”號(hào)的，如“$D$13”等)

不會(huì)發(fā)生改變，而''相對(duì)引用''的單元格(沒有加“$”號(hào)的，如“B3”等)，則會(huì)智能化地發(fā)生

變化，例如在E2單元格中，“B3”變成了“E3”，整個(gè)公式成為：

=IF(WEEKDAY(DATE($D$13,$F$13,1),2)=E3,1,0)。

8、選中B6單元格，輸入公式：=IF(B2=1,1,0),選中B7單元格，輸入公式：=H6+1。

用“填充柄”將B7單元格中的公式復(fù)制到B8、B9單元格中。

分別選中BIO、B11單元格，輸入公式：=IF(H9>=A2,0,H9+l)和

=1F(H10>=A2,0,IF(H10>0,H10+1,0))。

選中C6單元格，輸入公式：=IF(B6>0,B6+l,IF(C2=l,l,0))o用“填充柄”將C6單元格中

的公式復(fù)制到D6—H6單元格中。

選中C7單元格，輸入公式：=B7+1。用“填充柄'’將C7單元格中的公式復(fù)制到C8、C9

單元格中。同時(shí)選中C7-C9單元格，用“填充柄”將其中的公式復(fù)制到D7—H9單元格中。

選中CIO單元格，輸入公式:=IF(B11>=$A$2,O,IF(B11>O,B11+1,IF(C6=1,1,0)))。用“填

充柄”將C10單元格中的公式復(fù)制到DIO—H10單元格和C11單元格中。

至此，整個(gè)萬年歷(其實(shí)沒有萬年，只有從1900—2050的151年)制作完成。

下面，我們一起來將其裝飾一下。

9、選中相應(yīng)的單元格，利用工具欄上的相應(yīng)按鈕，設(shè)置好字體、字號(hào)、字符顏色等。

選中相應(yīng)的單元格，打開“單元格格式”為話框，在“對(duì)齊”標(biāo)簽下，設(shè)置好單元格中文

本的對(duì)齊方式(通常情況下，垂直對(duì)齊可以一次性設(shè)置為“居中”，水平“對(duì)齊”根據(jù)具體情況

設(shè)置)。

同時(shí)選中I列和J列，右擊鼠標(biāo)，選“隱藏”選項(xiàng)，將相應(yīng)的列隱藏起來，使得界面更加

友好。用同樣的方法，將第2和第3行也隱藏起來。

10、選中B5—H11單元格區(qū)域，打開“單元格格式”對(duì)話框，進(jìn)入“邊框”標(biāo)簽，選擇好“顏

色、樣式”，并“預(yù)置”好邊框范圍，然后“確定''退出，為月歷加上邊框。

11、執(zhí)行“工具-?選項(xiàng)''命令，打開“選項(xiàng)”對(duì)話框(如圖4),在“視圖”標(biāo)簽下(通常是

默認(rèn)標(biāo)簽)，清除“零值”和“網(wǎng)格線”復(fù)選框中的“V”號(hào)，“確定”退出，讓“零值”和“網(wǎng)格線”

不顯示出來。

選項(xiàng)0B

12＞將B14—H14和B15—H15單元格分別合并成一個(gè)單元格，并在B14和B15單元

格中輸入公式：=IF(AND(MONTH(DD=1,DAY①1)=1),"新的新氣象！加油呀！

",IF(AND(MONTH(D1)=3,DAY(D1尸8),"向女同胞們致敬！

",IF(AND(MONTH(D1)=5,DAY(D1)=1),"勞動(dòng)最光榮

"JF(AND(MONTH(D1)=5,DAY(D1尸4),"青年是祖國的棟梁

",IF(AND(MONTH(D1)=6,DAY(D1)=1),"原天下所有的兒童永遠(yuǎn)快樂”,0)))))和

=IF(AND(MONTH(DD=7,DAY(D1)=1),"黨的恩情永不忘

",IF(AND(MONTH(D1)=8,DAY(D1)=1),"提高警惕，保衛(wèi)祖國！

",IF(AND(MONTH(D1)=9,DAY(D1)=10),"老師,您辛苦了!

",IF(AND(MONTH(D1)=10,DAY(D1)=1),”祝我們偉大的祖國繁榮富強(qiáng)”,0))))。

設(shè)置好B14和B15單元格的字體、字號(hào)、字符顏色。

注意：上述公式的含義是：如果當(dāng)前日期逢到相關(guān)的節(jié)II（如“元旦”等），則在B14

或B15單元格顯示出相應(yīng)的祝福語言（如“新的新氣象！加油呀！"，參見圖5）。

二OO四為一月一日，就?北京時(shí)間20時(shí)35分

查詢年月2003年12月

新年新名或！加油田！⑤

由于IF函數(shù)只能嵌套7層，而節(jié)日數(shù)量超過7個(gè)（我們這里給出了9個(gè)），因此，我

們用兩個(gè)單元格來顯示。13、執(zhí)行“格式-工作表-背景”命令，打開“工作表背景”對(duì)

話框（如圖6）,選擇一張合適的圖片后，按“插入”按鈕，將其襯于工作表文字下面。

14、在按

住“Ctrl”鍵的同時(shí)，單擊D13和F13單元格，同時(shí)選中兩個(gè)單元格，開“單元格格式”對(duì)話框,

切換“保護(hù)”標(biāo)簽（如圖7）,清除“鎖定”前面復(fù)選框中的“V”號(hào)，“確定”退出。

15、執(zhí)行“工具-保護(hù)―保護(hù)工作表”命令，打開“保護(hù)工作表”對(duì)話框（如圖8）,兩次

輸入密碼后，確定退出。

保護(hù)工作表

0保護(hù)工作表及鎖定的單元格內(nèi)容（C）

取消工作表保護(hù)時(shí)使用的密碼化）:

選定

正

兀

單

設(shè)置

格

列

設(shè)置

式

式

行

設(shè)置

列

插入

行

插入

插入

超

刪除

列

（8）1彝，J取消

注意：經(jīng)過這樣的設(shè)置后，整個(gè)工作表中除了D13和F13單元格中的內(nèi)容可以改變外,

其它單元格中的內(nèi)容均不能改變，保證了萬年歷的使用可靠性。

在VB中操縱Excel

一.Excel對(duì)象模塊

Application

對(duì)象|集合

-----Assistant|--Addins(Addin)

-----AutoComect|——OLEDBErrors

-----Debug|-----CommandBars(CommandBar)

一一VBE|-----Dialogs(Dialog)

-----WorksheetFunction|-----RecentFilds(RecentFile)

-----DefaultWcbOptions|-----Windows(Window)

-----LanguageSettings|-----Workbooks(Workbook)

-----Filesearch|------Names(name)

-----AnswerWizardI------ODBCErrors

最上層的Application是指整個(gè)應(yīng)用程序，其中最常用到的Workbooks代表活頁簿集合，在

其后的括號(hào)內(nèi)的Workbook是指一個(gè)工作簿。

在VB中要打開Excel,首先要引用MicrosoftExcel9.0(或8.0)ObjectLobrary0

打開的步驟是：(1)定義兩個(gè)變量，數(shù)據(jù)類型指定為ExceLApplication.Excel.Workbook.

(2)激活Excel應(yīng)用程序。(3)打開工作簿(我們假設(shè)在當(dāng)前路徑下有一個(gè)工作簿students.xls)o

代碼如下：

OptionExplicit

PublicappExcelAsExceLApplication

PublicwbExcelAsExcel.Workbook

PrivateSubSetup_Excel_object()

SetappExcel=CreateObject("exceI.application")

SetwbExcel=appExcel.Workbooks.Opcn(App.Path&H\studcnts.xls")

appExcel.Visible=True'使對(duì)象可見

EndSub

二.工作表對(duì)象Worksheet

Workbooks對(duì)象有一個(gè)集合對(duì)象---工作表Worksheets,,用來放工作表相關(guān)的資料。

Workbooks(workbook)

對(duì)象集合

|--HTMLProjcet|——Publishobjects

|-RoutingSlip|-???DocumentProperties

|——Areas|—Worksheets

|--WebOptions|--Names

|——VBProject|——CustomViews

|--CommandBars

|—PivotCachcs

|--Windows

|——Styles

|--Charts

調(diào)用方法是聲明一個(gè)工作表變量，用For-Each循環(huán)方式可以讀取集合對(duì)象Workbooks

里的所有工作表名。

代碼如下：

DimtempAsExcel.Workshcet

ForEachtempInwbExcel.Worksheets'wbExcel是上面代碼中聲明的Workbook

FORM1.Combo1.Additemtemp.Name

Next

三.讀取工作表某個(gè)選取范圍的內(nèi)容。

要讀取工作表某個(gè)范圍單元格，可先定義?個(gè)Excel范圍對(duì)象變量，然后使用Rows或

Columns函數(shù)指定某行或某列的范圍，范圍單元格(Cells)地址以目前被選取范圍為基準(zhǔn)。

具體方法如下：

(1)設(shè)置工作表對(duì)象變量操作哪個(gè)工作表；

(2)選取工作表的某行或某列；

(3)在上面的選取范圍內(nèi)讀取某個(gè)單元格內(nèi)容。

代碼如下：

DimTempsheetAsExcel.Worksheet'定義Excel工作表

DimTempRangeAsExcel.Range'定義Excel工作表范圍變量

，第一步：選取工作表Sheetl

SetTempsheet=appExcel.Worksheets("sheetl")

，第二步：選取第二行(以這行為第一行)

SetTempRange=Tempsheet.Rows(2)

'第三步：讀取范圍內(nèi)第一行.第二列的單元格

Text1.Text=TempRange.Cells(1,2)

四.其他

用TcmpRange.Find("").Column方法可以查找目前范圍內(nèi)第一行的第一個(gè)空白單元格

如何實(shí)現(xiàn)VB與EXCEL的無縫連接

2003-02-17,?吳剛?,yesky

VB是常用的應(yīng)用軟件開發(fā)工具之一，由于VB的報(bào)表功能有限，而且一但報(bào)表格式發(fā)

生變化，就得相應(yīng)修改程序，給應(yīng)用軟件的維護(hù)工作帶來極大的不便。因此有很多程序員現(xiàn)

在已經(jīng)充分利用EXECL的強(qiáng)大報(bào)表功來實(shí)現(xiàn)報(bào)表功能。但由于VB與EXCEL由于分別屬

于不同的應(yīng)用系統(tǒng)，如何把它們有機(jī)地結(jié)合在一起，是一個(gè)值得我們研究的課題。

一、VB讀寫EXCEL表：

VB本身提自動(dòng)化功能可以讀寫EXCEL表，其方法如下：

1、在工程中引用MicrosoftExcel類型庫：

從"工程"菜單中選擇"引用"欄；選擇MicrosoftExcel9.0ObjectLibrary(EXCEL2000),

然后選擇"確定"。表示在工程中要引用EXCEL類型庫。

2、在通用對(duì)象的聲明過程中定義EXCEL對(duì)象：

DimxlAppAsExcel.Application

DimxlBookAsExcel.WorkBook

DimxlSheetAsExcel.Worksheet

3、在程序中操作EXCEL表常用命令：

SetxlApp=CreateObject("Excel.Application")'創(chuàng)建EXCEL對(duì)象

SetxlBook=xlApp.Workbooks.Opcn("文件名")，打開已經(jīng)存在的EXCEL工件簿文件

xlApp.Visible=True，設(shè)置EXCEL對(duì)象可見(或不可見)

SetxlSheet=xlBook.Worksheets("表名")"設(shè)置活動(dòng)工作表

xlSheet.Cells(row,col)=值，給單元格(row,col)賦值

xlSheet.PrintOut'打印工作表

xlBook.Close(True)'關(guān)閉工作簿

xlApp.Quit，結(jié)束EXCEL對(duì)象

SetxlApp=Nothing'釋放xlApp對(duì)象

xlBook.RunAutoMacros(xlAutoOpcn)運(yùn)行EXCEL啟動(dòng)宏

xlBook.RunAutoMacros(xlAutoClose)'運(yùn)行EXCEL關(guān)閉宏

4、在運(yùn)用以上VB命令操作EXCEL表時(shí)，除非設(shè)置EXCEL對(duì)象不可見，否則VB程

序可繼續(xù)執(zhí)行其它操作，也能夠關(guān)閉EXCEL,同時(shí)也可對(duì)EXCEL進(jìn)行操作。但在EXCEL

操作過程中關(guān)閉EXCEL對(duì)象時(shí)，VB程序無法知道，如果此時(shí)使用EXCEL對(duì)象，則VB程

序會(huì)產(chǎn)生自動(dòng)化錯(cuò)誤。形成VB程序無法完全控制EXCEL的狀況，使得VB與EXCEL脫

節(jié)。

二、EXCEL的宏功能：

EXCEL提供一個(gè)VisualBasic編輯器，打開VisualBasic編輯器，其中有一工程屬性窗

口，點(diǎn)擊右鍵菜單的"插入模塊"，則增加?個(gè)"模塊1",在此模塊中可以運(yùn)用VisualBasic

語言編寫函數(shù)和過程并稱之為宏。其中，EXCEL有兩個(gè)自動(dòng)宏：?個(gè)是啟動(dòng)宏(Sub

Auto_Open()),另一個(gè)是關(guān)閉宏(SubAuto_Close())。它們的特性是：當(dāng)用EXCEL打含有

啟動(dòng)宏的工簿時(shí)，就會(huì)自動(dòng)運(yùn)行啟動(dòng)宏，同理，當(dāng)關(guān)閉含有關(guān)閉宏的工作簿時(shí)就會(huì)自動(dòng)運(yùn)行

關(guān)閉宏。但是通過VB的自動(dòng)化功能來調(diào)用EXCEL工作表時(shí)，啟動(dòng)宏和關(guān)閉宏不會(huì)自動(dòng)運(yùn)

行，而需要在VB中通過命令xlBook.RunAutoMacros(xlAutoOpen)^flxlBook.RunAutoMacros

(xlAutoClose)來運(yùn)行啟動(dòng)宏和關(guān)閉宏。

三、VB與EXCEL的相互勾通：

充分利用EXCEL的啟動(dòng)宏和關(guān)閉宏，可以實(shí)現(xiàn)VB與EXCEL的相互勾通，其方法如

下：

在EXCEL的啟動(dòng)宏中加入一段程序，其功能是在磁盤中寫入一個(gè)標(biāo)志文件，同時(shí)在關(guān)

閉宏中加入一段刪除此標(biāo)志文件的程序。VB程序在執(zhí)行時(shí)通過判斷此標(biāo)志文件存在與否來

判斷EXCEL是否打開，如果此標(biāo)志文件存在，表明EXCEL對(duì)象正在運(yùn)行，應(yīng)該禁止其它

程序的運(yùn)行。如果此標(biāo)志文件不存在，表明EXCEL對(duì)象已被用戶關(guān)閉，此時(shí)如果要使用

EXCEL對(duì)象運(yùn)行，必須重新創(chuàng)建EXCEL對(duì)象。

四、舉例：

1、在VB中，建立一個(gè)FORM,在其上放置兩個(gè)命令按鈕，將Command1的Caption

屬性改為EXCEL,Command?的Caption屬性改為End。然后在其中輸入如下程序：

DimxlAppAsExcel.Application'定義EXCEL類

DimxlBookAsExceLWorkbook，定義工件簿類

DimxlshcetAsExcel.Worksheet'定義工作表類

PrivateSubCommandl_Click()'打開EXCEL過程

IfDir("D:\temp\excel.bz")=""Then'判斷EXCEL是否打開

SetxlApp=CreateObject("Excel.Application")'創(chuàng)建EXCEL應(yīng)用類

xlApp.Visible=True'設(shè)置EXCEL可見

SetxlBook=xlApp.Workbooks.Open("D:\temp\bb.xls")'打開EXCEL工作簿

Setxlsheet-xlBook.Worksheets(l)'JjJFEXCEL工作表

xlsheet.Activate'激活工作表

xlsheetCellsQ,l)="abc"給單元格1行駛列賦值

xlBook.RunAutoMacros(xlAutoOpen)運(yùn)行EXCEL中的啟動(dòng)宏

Else

MsgBox("EXCEL已打開”)

EndIf

EndSub

PrivateSubCommand2_C1ick()

IfDir(nD:\temp\excel.bzn)on"Then'由VB關(guān)閉EXCEL

xlBook.RunAutoMacros(xlAutoClose)，執(zhí)行EXCEL關(guān)閉宏

xlBook.Close(True/關(guān)閉EXCEL工作簿

xlApp.Quit'關(guān)閉EXCEL

EndIf

SetxlApp=Nothing'釋放EXCEL對(duì)象

End

EndSub

2、在D盤根目錄上建立一個(gè)名為Temp的子目錄，在Temp目錄下建立一個(gè)名為”bb.xls”

的EXCEL文件。

3、在”bb.xls”中打開VisualBasic編輯器，在工程窗口中點(diǎn)鼠標(biāo)鍵選擇插入模塊，在模

塊中輸入入下程序存盤：

Subauto_open()

Open"d:\temp\excel.bz"ForOutputAs#1'寫標(biāo)志文件

Close#1

EndSub

Subauto_close()

Kill"d:\temp\excel.bz”'刪除標(biāo)志文件

EndSub

4、運(yùn)行VB程序，點(diǎn)擊EXCEL按鈕可以打開EXCEL系統(tǒng)，打開EXCEL系統(tǒng)后，VB

程序和EXCEL分別屬兩個(gè)不同的應(yīng)用系統(tǒng)，均可同時(shí)進(jìn)行操作，由于系統(tǒng)加了判斷，因此

在VB程序中重復(fù)點(diǎn)擊EXCEL按鈕時(shí)會(huì)提示EXCEL已打開。如果在EXCEL中關(guān)閉EXCEL

后再點(diǎn)EXCEL按鈕，則會(huì)重新打開EXCEL。而無論EXCEL打開與否，通過VB程序均可

關(guān)閉EXCEL。這樣就實(shí)現(xiàn)了VB與EXCEL的無縫連接。

Excel制作獎(jiǎng)金計(jì)算表

hlip:icch.Jdvip.coni2006年08月16日03:52:06陳秀峰

源碼下載

某公司規(guī)定：一個(gè)月獎(jiǎng)金基數(shù)為300元，病假1天扣15元，事假一天扣30元，曠工

一天扣60元，扣完為止。使用這個(gè)獎(jiǎng)金計(jì)算表時(shí)，只要將員工的出勤情況記錄在表中，該

員工的獎(jiǎng)金將自動(dòng)計(jì)算出來，兼有考勤和計(jì)算獎(jiǎng)金兩種功能。自動(dòng)統(tǒng)計(jì)表做好以后還可以保

存成模板，以便以后使用。

本文所涉及到的Excel函數(shù)有：

1、COUNTIF(Range,Criteria)

2、MONTH(serial_number)

3、TODAY()

Al，今曉風(fēng)公司考勤及獎(jiǎng)金表

ABCDI：F(；HJAYAZBABBIBCBDBE^FIBGIBHIBIIBJIBKBLIBM

2月份：12月

3序號(hào)姓名1日2日3日4日25日26日27日28日29日30031B獎(jiǎng)金額

4TJ

1丁1QQBQQQQJJJBBBBBB

5QQ一QQQQ210.0

丁QTJ

22QQQQQJJJQQ

十0.0

633QQQQGGGGTJJJGGGGGG

T--J

丁0.0

QQQQ-QTJJJQQQQQ

44QQQJQ

丁225.0

55QQQQQQQ-QTJJJQQQQQQ

-J300.0

注意：圖中符號(hào)的含義是：B表示病假，S表示事假，G表示曠工，Q表示出勤，J表

示法定休息日。

1、啟動(dòng)Excel2003,按照圖1所示的樣式，制作一張二維表格，并填入相關(guān)文本。

2、將A1至BM1單元格合并成一個(gè)單元格后，輸入表格標(biāo)題(如“曉風(fēng)公司考勤及獎(jiǎng)

金表”)，并設(shè)置好字體、字號(hào)等。

3、將C2和D2單元格合并后，輸入公式：=MONTH(TODAY())&"月"，用于顯示出當(dāng)

前的月份。

注意：在上述單元格中直接輸入月份數(shù)值也是可行的。

4、選中BM4單元格，輸入公式：

=IF(300-COUNTIF(C4:BL4,"B")*7.5-COUNTIF(C4:BL4,"S")*15-COUNTIF(C4:BL4,"G")*30>

0,300-COUNTIF(C4:BL4,"B")*7.5-COUNTIF(C4:BL4,"S")*15-COUNTIF(C4:BL4,"G")*30,0),

確認(rèn)，計(jì)算出第一位員工的當(dāng)月獎(jiǎng)金額。

注意:上述公式的含義是：用“COUNTIF(C4:BL4,"B")、COUNTIF(C4:BL4,"S").

COUNTIF(C4:BL4,"G")”分別用于統(tǒng)計(jì)每位員工的病假、事假、曠工的次數(shù)，再分別乘以“7.5、

15、30"(每“半天”為一次)計(jì)算出所要扣去的獎(jiǎng)勵(lì)額，再用基數(shù)“300”減去上述扣去的金額。

如果(IF)差大于“0”，則顯示差(即員工的獎(jiǎng)金額)，否則顯示“0”(扣完為止)。

5、再次選中BM4單元格，用“填充柄”將上述公式復(fù)制到下面的單元格中，用于計(jì)算

其它員工的當(dāng)月獎(jiǎng)勵(lì)額。

注意：具體復(fù)制的數(shù)量，請根據(jù)員工的實(shí)際數(shù)目確定。

6、清空表格中所填寫的數(shù)據(jù)，執(zhí)行“文件一保存”命令，打開“另存為”對(duì)話框(如圖2),

按“保存類型''右邊的下拉按鈕，選中“模板(*.xlt)”選項(xiàng)，在文件名方框中輸入一名稱(如

“獎(jiǎng)金表.xlt”)，單擊“保存”按鈕，將制作好的表格文檔保存為模板。

另存為

7、以后，每個(gè)月第一天，啟動(dòng)Excel,執(zhí)行“文件一新建”命令，展開“新建工作簿”任

務(wù)窗格（如圖3）.

點(diǎn)擊其中的“本機(jī)上的模板”選項(xiàng)，打開“模板”對(duì)話框（如圖4）,選中“獎(jiǎng)金表”模板文

件，單擊“確定”按鈕即可新建一個(gè)空的“獎(jiǎng)金表1”工作簿文檔。

選中C2單元格（即保存月份的單元格），將鼠標(biāo)移到編輯欄中，然后按一下F9功能

鍵，將公式（=MONTH（TODAY（））&"月"）轉(zhuǎn)換為具體的值（如“12月”），最后將文檔保存

一下。

8、以后就可以用這個(gè)計(jì)算表逐口對(duì)員工進(jìn)行考勤，到了月底就可以自動(dòng)計(jì)算出獎(jiǎng)金數(shù)據(jù)了。

Excel應(yīng)用實(shí)例：輕松畫出復(fù)雜的函數(shù)曲線

http:42006年08月16日03:47:25

目標(biāo)：教您輕松畫好一條復(fù)雜的函數(shù)曲線。

實(shí)例：給出了一個(gè)函數(shù)式所對(duì)應(yīng)的曲線的例子。

難點(diǎn)分析:

一些教師會(huì)遇到畫函數(shù)曲線的問題吧!如果想快速準(zhǔn)確地繪制一條函數(shù)曲線，可以借助

EXCEL的圖表功能，它能使您畫的曲線既標(biāo)準(zhǔn)又漂亮。您一定會(huì)問，是不是很難學(xué)呀？其

實(shí)一點(diǎn)兒也不難，不信您就跟我試一試。

以繪制y=|lg（6+x人3）|的曲線為例，其方法如下：

1）自變量的輸入

在某張空白的工作表中，先輸入函數(shù)的自變量：在A列的A1格輸入“X="，表明這是

自變量。

Excel-Bookl-Ii5|x|

再在A列的A2及以后的格內(nèi)逐次從小到大輸入自變量的各個(gè)值；實(shí)際輸入的時(shí)候，

通常應(yīng)用等差數(shù)列輸入法，先輸入前二個(gè)值，定出自變量中數(shù)與數(shù)之間的步長，然后選中

A2和A3兩個(gè)單元格，使這二項(xiàng)變成一個(gè)帶黑色邊框的矩形，再用鼠標(biāo)指向這黑色矩形的

右下角的小方塊“?“，當(dāng)光標(biāo)變成“+”字型后，按住鼠標(biāo)拖動(dòng)光標(biāo)到適當(dāng)?shù)奈恢?，就完成?/p>

變量的輸入。

2）輸入函數(shù)式

在B列的B1格輸入函數(shù)式的一般書面表達(dá)形式，y=|lg（6+x八3）|?

BlMicrosoftExcel-Bookl

在B2格輸入JABS（LOGIO（6+A2八3））”，B2格內(nèi)馬上得出了計(jì)算的結(jié)果。這時(shí),

再選中B2格，讓光標(biāo)指向B2矩形右下角的“?”，當(dāng)光標(biāo)變成“+”時(shí)按住光標(biāo)沿B列拖動(dòng)到

適當(dāng)?shù)奈恢眉赐瓿珊瘮?shù)值的計(jì)算。

E3MicrosoftExcel-Bookl

胤文件也）編輯如視圖9插入d）格式@）工具復(fù)）數(shù)據(jù)也）窗口也）-|g|x|

□自m昌喳工%外延日?||宋體?■2

B2,I==ABS（L0G10（6+A2：3））.

3）繪制曲線

點(diǎn)擊工具欄上的“圖表向?qū)А卑粹o鯉］選擇“X,Y散點(diǎn)圖”，然后在出現(xiàn)的“X,Y散點(diǎn)

圖”類型中選擇“無數(shù)據(jù)點(diǎn)平滑線散點(diǎn)圖”。此時(shí)可察看即將繪制的函數(shù)圖像，發(fā)現(xiàn)并不是我

們所要的函數(shù)曲線。

12006年08月16日03:47:25

圖表向?qū)?4步驟之1-圖表類型

標(biāo)準(zhǔn)類型I自定義類型］

51表類型里）：_______________子圖表類型?：

柱形圖

圖

條形

圖

折線

?飾圖

底

XY散點(diǎn)圖

面積圖

?

回環(huán)圖

。

圖

雷達(dá)

圖

圖

?曲面

圖

氣泡

?:

無數(shù)據(jù)點(diǎn)平滑線散點(diǎn)圖按下不放可查看示例9|

②I取消|____________|下一步》|完成①

單擊“下一步”按鈕，選中“數(shù)據(jù)產(chǎn)生在列”項(xiàng)，給出數(shù)據(jù)區(qū)域。

單擊“下一步”按鈕。

單擊“下一步”按鈕，單擊“完成”按鈕。這時(shí)曲線就在我們面前了。

4）相關(guān)問題

需要注意：如何確定自變量的初始值，數(shù)據(jù)點(diǎn)之間的步長是多少，這是要根據(jù)函數(shù)的

具體特點(diǎn)來判斷，這也是對(duì)使用者能力的檢驗(yàn)。如果想很快查到函數(shù)的極值或看出其發(fā)展趨

勢，給出的數(shù)據(jù)點(diǎn)也不?定非得是等差的，可以根據(jù)需要任意給定。

從簡單的三角函數(shù)到復(fù)雜的對(duì)數(shù)、指數(shù)函數(shù)，都可以用EXCEL畫出曲線。如果用得到,

您還可以利用EXCEL來完成行列式、矩陣的各種計(jì)算，進(jìn)行簡單的積分運(yùn)算，利用迭代求

函數(shù)值（如x0=x"+4,可用迭代方法求x值），等等，凡是涉及計(jì)算方面的事，找EXCEL

來幫忙，它一定會(huì)給您一個(gè)滿意的答案。

舉一反三：

講解了EXCEL繪制各種函數(shù)曲線的方法，相信您對(duì)EXCEL已經(jīng)有了很多認(rèn)識(shí)和理解。

但EXCEL的功能遠(yuǎn)不只這些，這里只是給您一個(gè)思路，在實(shí)際工作中，如果遇到更復(fù)雜的

情況，您只要結(jié)合上面的例子，對(duì)相應(yīng)的公式進(jìn)行調(diào)整和擴(kuò)展，就一定能完成任務(wù)。單擊“常

用''工具欄上的“粘貼函數(shù)”按鈕史J,在“粘貼函數(shù)”對(duì)話框中可以查到很多函數(shù)，點(diǎn)擊其中的

一個(gè)函數(shù)，在下面就有函數(shù)的名稱和解釋，相信您?定會(huì)用的，并且會(huì)建立更美觀、更實(shí)用

的工作表。

在Excel中將人民幣金額小寫轉(zhuǎn)成大寫

Excel中要將人民幣小寫金額轉(zhuǎn)換成大寫格式，將自定義格式類型中的“G/通用格式”改

為“G/通用格式“元””來實(shí)現(xiàn)。但在轉(zhuǎn)換小數(shù)時(shí)卻出現(xiàn)了問題，比如￥6,050.09只能轉(zhuǎn)換為“陸

仔零伍拾.零玖圓，，。那怎么解決這一先天不足呢？

方法有以下兩種(以下均對(duì)B1單元格轉(zhuǎn)換，假設(shè)B1通過函數(shù)ROUND。四舍五入取得

二位小數(shù)的小寫金額數(shù)，其值為￥6,050.09)。

一、在三個(gè)連續(xù)的同行單元格中轉(zhuǎn)換

1.B2中輸入公式“=IF(Bl<0,"金額為負(fù)無效"，INT(Bl))”，計(jì)算結(jié)果為￥6,050.00,

然后點(diǎn)擊“格式一單元格一數(shù)字一特殊一中文大寫數(shù)字一確定”，B2顯示“陸仟零伍拾”，再

點(diǎn)擊“格式一單元格一數(shù)字一自定義”，將"類型”編輯框中的“G/通用格式”修改為：(“人民

幣”)G/通用格式“元”，此時(shí)B2顯示：“(人民幣)陸仟零伍拾元”。

2.C2中輸入“=IF(BI<0,INT(B1lO)-INT(Bl)10)”,同樣C2改為：G/通用格式

"角”，C2顯示：“零角”。

3.D2中輸入“=IF(Bl<0,INT(B1100)-INT(Bl10)10)”,同樣D2改為：G/通

用格式“分”，D2顯示：“玖分”。

這樣在連續(xù)的三個(gè)同行單元格中實(shí)現(xiàn)了大寫金額格式的轉(zhuǎn)換，如圖1。為了使轉(zhuǎn)換符合

財(cái)務(wù)格式，B2應(yīng)右對(duì)齊，C2列寬無間隔，且不設(shè)左右邊框線，D2要左對(duì)齊。

AB1CD

合計(jì)(小寫)￥6,050.09

合計(jì)(大寫)(A民幣)陸仟零伍拾元零角玖分

圖1顯示大寫金額

二、用Excel提供的函數(shù)轉(zhuǎn)換

個(gè)位為0,則不顯示；不為0,顯示“XXX元”；不為0、且角位和分位均為0,顯示“XXX

元整“；元角分位均為0,則顯示“零元整

小數(shù)點(diǎn)后面第一位為0、且元位或分位有一為0,則不顯示；為0、且元位和分位均不

為0,則顯示“零”；不為0,顯示“X角”。

小數(shù)點(diǎn)后面第二位為0,則顯示“整”；不為0,顯示“X分”。

根據(jù)以上思路在B2中輸入公式：

"金額為負(fù)無效"，IF(OR(B1=0,Bl=""),"(人民幣)零元整"))；

"(人民幣)",TEXT(INT(B1),"(人民幣)G/通用格式")&&"元

")&&IF(INT(B1lO)-INT(Bl)10=0)；

IF(INT(B1)(INT(B11OO)-INT(B110)10)=0,""，"零")，

TEXT(INT(B1lO)-INT(Bl)10,"")&&"^")&&IF((INT(B11OO)-INT(B110)10)=0,"

整"，TEXT((INT(B11OO)-INT(B1*1O)*1O),"")&&"分")))”。

確認(rèn)后B2顯示：“(人民幣)陸仟零伍拾元零玖分”。以后使用時(shí)將公式復(fù)制到有關(guān)單元

格中，若轉(zhuǎn)換的對(duì)象B1發(fā)生了變化，可點(diǎn)擊“編輯一替換”將B1替換掉。兩種轉(zhuǎn)換測試結(jié)

果如圖2所示。

C7二］?=IF(A7<0,”,IWT(A7*10)-IHT(A7)*而

~Bc~nr~

大寫

小雪

第一憐方法第二、三齡方法

（A民幣）氐佰景為谷元伍的區(qū)分（A民幣）原佰活拾圣元伍角乩分

￥213.60（A民幣）氐佰景拾生元伍的零分（A民市）盛0壹的邕元伍角整

￥213.00（A民幣）氮佰量拾餐元W的零分（A民幣）源佰置拾叁元整

￥213.06（A艮幣）嬴佰量拾叁元零角降分（人艮幣）原值?結(jié)生元零總分

Y-213.56金旗為煲無效［1金廈為負(fù)歡

￥0.50（人艮幣）標(biāo)伍角零分（A民幣）伍由4

￥0.56（A民幣）零元伍角后分（A民幣）伍前降分

￥0.00（人艮幣）零元零角零分（人民所）零元整

2（人民甫）零元零角零分（人艮市）新察

圖2兩種方式對(duì)話結(jié)果

Excel函數(shù)應(yīng)用教程：函數(shù)的輸入方法

http:2006年08月16日03:21:01

Excel公式輸入其實(shí)可以歸結(jié)為函數(shù)輸入的問題。

（1）“插入函數(shù)”對(duì)話框

“插入函數(shù)”對(duì)話框是Excel輸入公式的重要工具，以公式

“=SUM（Sheet2!Al:A6,Sheet3!B2:B9）”為例，Excel輸入該公式的具體過程是：

首先選中存放計(jì)算結(jié)果（即需要應(yīng)用公式）的單元格，單擊編輯欄（或工具欄）中的“fx”按

鈕，則表示公式開始的“=”出現(xiàn)在單元格和編輯欄，然后在打開的“插入函數(shù)''對(duì)話框中的“選

擇函數(shù)”列表找到“SUM”函數(shù)。如果你需要的函數(shù)不在里面，可以打開“或選擇類別”卜拉列

表進(jìn)行選擇。最后單擊“確定”按鈕，打開“函數(shù)參數(shù)”對(duì)話框。

對(duì)SUM函數(shù)而言，它可以使用從numberl開始直到number30共30個(gè)參數(shù)。對(duì)上面

的公式來說，首先應(yīng)當(dāng)把光標(biāo)放在對(duì)話框的“numberl”框中，單擊工作簿中的“Sheet2!”工作

表標(biāo)簽，“Sheet2!”即可自動(dòng)進(jìn)入其中，接著鼠標(biāo)拖動(dòng)選中你要引用的區(qū)域即可。接著用鼠標(biāo)

單擊對(duì)話框的“number2”框，單擊工作簿中的“Sheet3!”工作表標(biāo)簽，其名稱“Sheet3!”即可自

動(dòng)進(jìn)入其中，再按相同方法選擇要引用的單元格區(qū)域即可。

上述方法的最大優(yōu)點(diǎn)就是引用的區(qū)域很準(zhǔn)確，特別是三維引用時(shí)不容易發(fā)生工作表或

工作簿名稱輸入錯(cuò)誤的問題。

（2）編輯欄輸入

如果你要套用某個(gè)現(xiàn)成公式，或者輸入一些嵌套關(guān)系復(fù)雜的公式，利用編輯欄輸入更

加快捷。

首先選中存放計(jì)算結(jié)果的單元格;鼠標(biāo)單擊Excel編輯欄，按照公式的組成順序依次輸

入各個(gè)部分，公式輸入完畢后，單擊編輯欄中的“輸入”（即'7"）按鈕（或回車）即可。

手工輸入時(shí)同樣可以采取上面介紹的方法引用區(qū)域，以公式

“=SUM（Sheet2!Al:A6,Sheet3!B2:B9）”為例，你可以先在編輯欄中輸入“=SUM（）”，然后將光

標(biāo)插入括號(hào)中間，再按上面介紹的方法操作就可以引用輸入公式了。但是分隔引用之間的逗

號(hào)必須用手工輸入，而不能像“插入函數(shù)”對(duì)話框那樣自動(dòng)添加。

Excel函數(shù)應(yīng)用教程：函數(shù)的參數(shù)

http,icch.ddvip.coni2006年08月16日03:20:19

函數(shù)右邊括號(hào)中的部分稱為參數(shù)，假如一個(gè)函數(shù)可以使用多個(gè)參數(shù)，那么參數(shù)與參數(shù)

之間使用半角逗號(hào)進(jìn)行分隔。

參數(shù)可以是常量（數(shù)字和文本）、邏輯值（例如TRUE或FALSE）、數(shù)組、錯(cuò)誤值（例如#N/A）

或單元格引用（例如E1:H1）,甚至可以是另一個(gè)或幾個(gè)函數(shù)等。參數(shù)的類型和位置必須滿足

函數(shù)語法的要求，否則將返回錯(cuò)誤信息。

(1)常量

常量是直接輸入到單元格或公式中的數(shù)字或文本，或由名稱所代表的數(shù)字或文本值，

例如數(shù)字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式計(jì)算出的

結(jié)果都不是常量，因?yàn)橹灰降膮?shù)發(fā)生了變化，它自身或計(jì)算出來的結(jié)果就會(huì)發(fā)生變化。

(2)邏輯值

邏輯值是比較特殊的一類參數(shù)，它只有TRUE(真)或FALSE(假)兩種類型。例如在公式

“=IF(A3=0,"",A2/A3)”中，“A3=0”就是?個(gè)可以返回TRUE(真)或FALSE(假)兩種結(jié)果的參

數(shù)。當(dāng)“A3=0”為TRUE(真)時(shí)在公式所在單元格中填入“0”，否則在單元格中填入“A2/A3”的

計(jì)算結(jié)果。

(3)數(shù)組

數(shù)組用于可產(chǎn)生多個(gè)結(jié)果，或可以對(duì)存放在行和列中的一組參數(shù)進(jìn)行計(jì)算的公式。

Excel中有常量和區(qū)域兩類數(shù)組。前者放在“{}”(按下Ctrl+Shifl+Enter組合鍵自動(dòng)生成)內(nèi)部,

而且內(nèi)部各列的數(shù)值要用逗號(hào)",”隔開，各行的數(shù)值要用分號(hào)”;“隔開。假如你要表示第1行

中的56、78、89和第2行中的90、76、80,就應(yīng)該建立一個(gè)2行3列的常量數(shù)組

“{56,78,89;90,76,80}。

區(qū)域數(shù)組是一個(gè)矩形的單元格區(qū)域，該區(qū)域中的單元格共用一個(gè)公式。例如公式

“=TREND(B1:B3,A1:A3)”作為數(shù)組公式使用時(shí)，它所引用的矩形單元格區(qū)域“B1:B3,A1:A3”

就是一個(gè)區(qū)域數(shù)組。

(4)錯(cuò)誤值

使用錯(cuò)誤值作為參數(shù)的主要是信息函數(shù)，例如“ERROR.TYPE”函數(shù)就是以錯(cuò)誤值作為

參數(shù)。它的語法為“ERROR.TYPE(error_val)”，如果其中的參數(shù)是#NUM!,則返回?cái)?shù)值“6”。

(5)單元格引用

單元格引用是函數(shù)中最常見的參數(shù)，引用的目的在于標(biāo)識(shí)工作表單元格或單元格區(qū)域,

并指明公式或函數(shù)所使用的數(shù)據(jù)的位置，便于它們使用工作表各處的數(shù)據(jù)，或者在多個(gè)函數(shù)

中使用同一個(gè)單元格的數(shù)據(jù)。還可以引用同一工作簿不同工作表的單元格，甚至引用其他工

作簿中的數(shù)據(jù)。

根據(jù)公式所在單元格的位置發(fā)生變化時(shí)，單元格引用的變化情況，我們可以引用分為

相對(duì)引用、絕對(duì)引用和混合引用三種類型。以存放在F2單元格中的公式“=SUM(A2:E2)”為

例，當(dāng)公式山F2單元格復(fù)制到F3單元格以后，公式中的引用也會(huì)變化為“=SUM(A3:E3)”。

若公式自F列向下繼續(xù)復(fù)制，“行標(biāo)”每增加1行，公式中的行標(biāo)也自動(dòng)加1。

如果上述公式改為“=SUM($A$3:$E$3)”，則無論公式復(fù)制到何處，其引用的位置始終

是“A3:E3”區(qū)域。

混合引用有“絕對(duì)列和相對(duì)行”，或是“絕對(duì)行和相對(duì)列''兩種形式。前者如

“=SUM($A3:$E3)”,后者如“=SUM(A$3:E$3)”。

上面的幾個(gè)實(shí)例引用的都是同一工作表中的數(shù)據(jù)，如果要分析同一工作簿中多張工作

表上的數(shù)據(jù)，就要使用三維引用。假如公式放在工作表Sheetl的C6單元格，要引用工作表

Sheet2的“A1:A6”和Sheet3的“B2:B9”區(qū)域進(jìn)行求和運(yùn)算，則公式中的引用形式為

“=SUM(Sheet2!Al:A6,Sheet3!B2:B9)”。也就是說三維引用中不僅包含單元格或區(qū)域引用，還

要在前面加上帶"!”的工作表名稱。

假如你要引用的數(shù)據(jù)來自另一個(gè)工作簿，如工作簿Bookl中的SUM函數(shù)要絕對(duì)引用

工作簿Book2中的數(shù)據(jù)，其公式為“=SUM([Book2]Sheetl!SASI:SAS8,[Book2]Sheet2!SB

SI:SBS9)”，也就是在原來單元格引用的前面加上“[Book2]Sheetl!”。放在中括號(hào)里面的是

工作簿名稱，帶”!”的則是其中的工作表名稱。即是跨工作簿引用單元格或區(qū)域時(shí)，引用對(duì)

象的前面必須用“!”作為工作表分隔符，再用中括號(hào)作為工作簿分隔符。不過三維引用的要

受到較多的限制，例如不能使用數(shù)組公式等。

提示:上面介紹的是Excel默認(rèn)的引用方式，稱為“A1引用樣式”。如果你要計(jì)算處在“宏”

內(nèi)的行和列，必須使用“R1C1引用樣式”。在這種引用樣式中，Excel使用“R”加“行標(biāo)”和“C”

加“列標(biāo)”的方法指示單元格位置。啟用或關(guān)閉R1C1引用樣式必須單擊“工具t選項(xiàng)”菜單命

令，打開對(duì)話框的“常規(guī)”選項(xiàng)卡，選中或清除“設(shè)置”下的“R1C1引用樣式”選項(xiàng)。由于這種

引用樣式很少使用，限于篇幅本文不做進(jìn)?步介紹。

(6)嵌套函數(shù)

除了上面介紹的情況外，函數(shù)也可以是嵌套的，即一個(gè)函數(shù)是另一個(gè)函數(shù)的參數(shù)，例

如“=IF(OR(RIGHTB(E2,1)="「',RIGHTB(E2,1)="3",R1GHTB(E2,1)="5",RIGHTB(E2,1)="7",

RIGHTB(E2