1、用 EXCEL 制作一份万年历 今天我们介绍用 Excel 制作万年 历的方法。这个万年历可以显示当月的月历，还可以随 意查阅任何日期所属的月历，非常方便。如果你愿意，还可以让它在特殊的日子里显示不同 的提醒文字，一起来试试吧！ 本文所涉及到的函数有： 1、AND (logical1,logical2, .) 2、 DATE (year,month,day) 3、 DAY (serial_number) 4、 IF (Logical,Value_if_true,Value_if_false) 5、 INT (number) 6、 MONTH (serial_number) 7、 NOW ()
2、8、 OR (logical1,logical2, .) 1、启动 EXCEL2003，新建一个工作表，取名保存（如万年历.xls），并在相应的单 元格中，输入如图 1 所示的文本。 2、同时选中 B1、C1、D1 单元格，按 “格式”工具栏上的“合并及居中”按钮，将其合 并成一个单元格，并输入公式：=TODAY （）。 选中 B1（合并后的）单元格，执行 “格式单元格”命令，打开“单元格格式”对话框 （如图 2），在“ 数字” 标签中的 “分类”下面选中“日期”选项，再在右侧 “类型”下面选中“二 一年三月十四日 ”选项，“确定”退出，将日期设置成中文形式。 注意：TODAY （）函数用于提
3、取当前系统日期，请将系统日期一定要调整准确哟。 3、选中 F1 单元格，输入公式：=IF(WEEKDAY(B1,2)=7,“ 日“,WEEKDAY(B1,2)；选 中 H1 单元格，输入公式：=NOW（）。 选中 F1 单元格，打开“单元格格式”对话框，在“数字” 标签中的 “分类” 下面选中“特殊” 选项，再在右侧“ 类型” 下面选中 “中文小写数字”选项，“确定 ”退出，将“星期数”设置成中文 小写形式；选中 H1 单元格，打开 “单元格格式”对话框，在 “数字”标签中的“分类”下面选中 “时间”选项，再在右侧“ 类型 ”下面选中一款时间格式，“确定 ”退出。 注意：上述前面一个公式的含义
4、是：如果（IF）当前日期（B1）是星期“7” （WEEKDAY(B1,2)=7），则在 F1 单元格中显示“日”，否则，直接显示出星期的数值 （WEEKDAY(B1,2)）。 上述第二个函数（NOW （）用于提取当前系统日期和时间，也请将系统日期和 时间调整准确。 4、在 I1、I2 单元格分别输入 1900、1901，然后同时选中 I1、I2 单元格，用“填充柄” 向下拖拉至 I151 单元格，输入 19002050 年份序列。 同样的方法，在 J1 至 J12 单元格中输入 112 月份序列。 5、选中 D13 单元格，执行“数据有效性”命令，打开“数据有效性”对话框（如图 3）， 按“允
5、许”右侧的下拉按钮，选中 “序列”选项，在“来源”下面的方框输入： =$I$1:$I$151，“ 确定” 退出。 同样的操作，将 F15 单元格数据有效性设置为“=$J$1:$J$12”序列。 注意：经过这样的设置以后，当我们选中 D15（或 F15）单元格时，在单元格右侧出 现一个下拉按钮，按此下拉按钮，即可选择年份（或月份）数值，快速输入需要查询的年、 月值。 6、选中 A2 单元格（不一定非得是 A2 哟），输入公式： =IF(F13=2,IF(OR(D13/400=INT(D13/400),AND(D13/4=INT(D13/4),D13/100INT(D13/100),2 9,28)
6、,IF(OR(F13=4,F13=6,F13=9,F13=11),30,31)，用于获取查询 “月份”所对应的天数 （28、29、30、31）。 注意：上述函数的含义是：如果查询“月份”为“2 月”（F13=2）时，并且“年份”数能被 400 整除D13/400=INT(D13/400)，或者（OR）“年份”能被 4 整除，但不能被 100 整除 AND(D13/4=INT(D13/4),D13/100INT(D13/100)，则该月为 29 天（也就是我们通常所 说的“闰年”），否则为 28 天。如果“ 月份”不是 2 月，但是 “4、6、9、11”月，则该月为 30 天。其他月份天数为 3
7、1 天。 7、选中 B2 单元格，输入公式： =IF(WEEKDAY(DATE($D$13,$F$13,1),2)=B3,1,0)。 再次选中 B2 单元格，用“ 填充柄 ”将上述公式复制到 C2H2 单元格中。 注意：上述 B2 公式的含义是：如果 “查询年月”的第 1 天是星期“7” （WEEKDAY(DATE)($D$13,$F$13,1),2)=B3）时，在该单元格显示“1” ，反之显示“0”）， 为“查询年月”获取一个对照值，为下面制作月历做准备。 上述 C2H2 单元条中公式的含义与 B2 相似。 在用拖拉法复制公式时，公式“绝对引用”的单元格（加了“$” 号的，如“$D$13”
8、等） 不会发生改变，而“相对引用”的单元格（没有加“$”号的，如 “B3”等），则会智能化地发生 变化，例如在 E2 单元格中，“B3”变成了“E3”，整个公式成为： =IF(WEEKDAY(DATE($D$13,$F$13,1),2)=E3,1,0)。 8、选中 B6 单元格，输入公式： =IF(B2=1,1,0)。选中 B7 单元格，输入公式： =H6+1。用 “填充柄”将 B7 单元格中的公式复制到 B8、B9 单元格中。 分别选中 B10、B11 单元格，输入公式：=IF(H9=A2,0,H9+1)和 =IF(H10=A2,0,IF(H100,H10+1,0)。 选中 C6 单元格，输
9、入公式： =IF(B60,B6+1,IF(C2=1,1,0)。用“填充柄”将 C6 单元格 中的公式复制到 D6H6 单元格中。 选中 C7 单元格，输入公式： =B7+1。用“ 填充柄”将 C7 单元格中的公式复制到 C8、C9 单元格中。同时选中 C7C9 单元格，用“填充柄”将其中的公式复制到 D7H9 单 元格中。 选中 C10 单元格，输入公式： =IF(B11=$A$2,0,IF(B110,B11+1,IF(C6=1,1,0)。用 “填充柄”将 C10 单元格中的公式复制到 D10H10 单元格和 C11 单元格中。 至此，整个万年历（其实没有万年，只有从 19002050 的 1
10、51 年）制作完成。 下面，我们一起来将其装饰一下。 9、选中相应的单元格，利用工具栏上的相应按钮，设置好字体、字号、字符颜色等。 选中相应的单元格，打开“单元格格式”对话框，在“ 对齐” 标签下，设置好单元格中文 本的对齐方式（通常情况下，垂直对齐可以一次性设置为“居中”，水平“对齐”根据具体情 况设置）。 同时选中 I 列和 J 列，右击鼠标，选 “隐藏”选项，将相应的列隐藏起来，使得界面更 加友好。用同样的方法，将第 2 和第 3 行也隐藏起来。 10、选中 B5H11 单元格区域，打开“单元格格式”对话框，进入“边框”标签，选择好 “颜色、样式”，并“ 预置”好边框范围，然后“确定”退
11、出，为月历加上边框。 11、执行“工具选项” 命令，打开“ 选项”对话框（如图 4），在“ 视图”标签下（通常 是默认标签），清除“零值” 和 “网格线”复选框中的“”号， “确定”退出，让“零值”和“网格 线”不显示出来。 12、将 B14H14 和 B15H15 单元格分别合并成一个单元格，并在 B14 和 B15 单元 格中输入公式：=IF(AND(MONTH(D1)=1,DAY(D1)=1),“新的新气象！加油呀！ “,IF(AND(MONTH(D1)=3,DAY(D1)=8),“向女同胞们致敬！“,IF(AND(MONTH(D1) =5,DAY(D1)=1),“劳动最光荣“,IF(A
12、ND(MONTH(D1)=5,DAY(D1)=4),“青年是祖国的栋梁 “,IF(AND(MONTH(D1)=6,DAY(D1)=1),“原天下所有的儿童永远快乐“,0)和 =IF(AND(MONTH(D1)=7,DAY(D1)=1),“党的恩情永不忘“,IF(AND(MONTH(D1)=8,DAY(D1) =1),“提高警惕，保卫祖国！“,IF(AND(MONTH(D1)=9,DAY(D1)=10),“老师，您辛苦了！ “,IF(AND(MONTH(D1)=10,DAY(D1)=1),“祝我们伟大的祖国繁荣富强“,0)。 设置好 B14 和 B15 单元格的字体、字号、字符颜色。 注意：上述
13、公式的含义是：如果当前日期逢到相关的节日（如“元旦”等），则在 B14 或 B15 单元格显示出相应的祝福语言（如 “新的新气象！加油呀！”，参见图 5）。 由于 IF 函数只能嵌套 7 层，而节日数量超过 7 个（我们这里给出了 9 个），因此， 我们用两个单元格来显示。 13、执行“格式工作表背景”命令，打开“工作表背景” 对话框（如图 6），选择一张合适的图片后，按“插入”按钮，将其衬于工作表文字下面。 14、在按住“Ctrl”键的同时，单击 D13 和 F13 单元格，同时选中两个单元格，开“单元 格格式”对话框，切换“ 保护”标签（如图 7），清除“锁定 ”前面复选框中的“”号，“确
14、定” 退出。 15、执行“工具保护保护工作表”命令，打开“ 保护工作表 ”对话框（如图 8），两 次输入密码后，确定退出。 注意：经过这样的设置后，整个工作表中除了 D13 和 F13 单元格中的内容可以改变 外，其它单元格中的内容均不能改变，保证了万年历的使用可靠性。 在 VB 中操纵 Excel 一Excel 对象模块 Application 对象 | 集合 - |-Assistant |-Addins(Addin) |-AutoComect |-OLEDBErrors |-Debug |-CommandBars(CommandBar) |-VBE |-Dialogs(Dialog) |-
15、WorksheetFunction |-RecentFilds(RecentFile) |-DefaultWebOptions |-Windows(Window) |-LanguageSettings |-Workbooks(Workbook) |-Filesearch |-Names(name) |-AnswerWizard |-ODBCErrors 最上层的 Application 是指整个应用程序，其中最常用到的 Workbooks 代表活页簿集合，在 其后的括号内的 Workbook 是指一个工作簿。 在 VB 中要打开 Excel，首先要引用 Microsoft Excel 9.0(
16、或 8.0）Object Lobrary。 打开的步骤是：（1）定义两个变量，数据类型指定为 Excel.ApplicationExcel.Workbook. (2)激活 Excel 应用程序。 （3）打开工作簿 (我们假设在当前路径下有一个工作簿 students.xls） 。 代码如下： Option Explicit Public appExcel As Excel.Application Public wbExcel As Excel.Workbook Private Sub Setup_Excel_object() Set appExcel = CreateObject(“excel
17、.application“) Set wbExcel = appExcel.Workbooks.Open(App.Path & “students.xls“) appExcel.Visible = True 使对象可见 End Sub 二工作表对象 Worksheet Workbooks 对象有一个集合对象工作表 Worksheets,，用来放工作表相关的资 料。 Workbooks(workbook) 对象 | 集合 - |-HTMLProjcet |-Publishobjects |-RoutingSlip |-DocumentProperties |-Areas |-Worksheets
18、 |-WebOptions |-Names |-VBProject |-CustomViews |-CommandBars |-PivotCaches |-Windows |-Styles |-Charts 调用方法是声明一个工作表变量，用 For-Each 循环方式可以读取集合对象 Workbooks 里的所有工作表名。 代码如下： Dim temp As Excel.Worksheet For Each temp In wbExcel.Worksheets wbExcel 是上面代码中声明的 Workbook FORM1.Combo1.AddItem temp.Name Next 三读取工
19、作表某个选取范围的内容。 要读取工作表某个范围单元格，可先定义一个 Excel 范围对象变量，然后使用 Rows 或 Columns 函数指定某行或某列的范围，范围单元格（Cells)地址以目前被选取范围为基准。 具体方法如下： （1）设置工作表对象变量操作哪个工作表； （2）选取工作表的某行或某列； （3）在上面的选取范围内读取某个单元格内容。 代码如下： Dim Tempsheet As Excel.Worksheet 定义 Excel 工作表 Dim TempRange As Excel.Range 定义 Excel 工作表范围变量 第一步：选取工作表 Sheet1 Set Tempsh
20、eet = appExcel.Worksheets(“sheet1“) 第二步：选取第二行(以这行为第一行） Set TempRange = Tempsheet.Rows(2) 第三步：读取范围内第一行第二列的单元格 Text1.Text = TempRange.Cells(1, 2) 四其他 用 TempRange.Find(“).Column 方法可以查找目前范围内第一行的第一个空白单元格 如何实现 VB 与 EXCEL 的无缝连接 2003-02-17 吴刚yesky VB 是常用的应用软件开发工具之一，由于 VB 的报表功能有限，而且一但报表格式发 生变化，就得相应修改程序，给应用软件
21、的维护工作带来极大的不便。因此有很多程序员 现在已经充分利用 EXECL 的强大报表功来实现报表功能。但由于 VB 与 EXCEL 由于分别 属于不同的应用系统，如何把它们有机地结合在一起，是一个值得我们研究的课题。 一、 VB 读写 EXCEL 表： VB 本身提自动化功能可以读写 EXCEL 表，其方法如下： 1、在工程中引用 Microsoft Excel 类型库： 从“工程“菜单中选择“引用“栏；选择 Microsoft Excel 9.0 Object Library（EXCEL2000） ， 然后选择“确定 “。表示在工程中要引用 EXCEL 类型库。 2、在通用对象的声明过程中定
22、义 EXCEL 对象： Dim xlApp As Excel.Application Dim xlBook As Excel.WorkBook Dim xlSheet As Excel.Worksheet 3、在程序中操作 EXCEL 表常用命令： Set xlApp = CreateObject(“Excel.Application“) 创建 EXCEL 对象 Set xlBook = xlApp.Workbooks.Open(“文件名“) 打开已经存在的 EXCEL 工件簿文件 xlApp.Visible = True 设置 EXCEL 对象可见（或不可见） Set xlSheet = x
23、lBook.Worksheets(“表名“) 设置活动工作表 xlSheet.Cells(row, col) =值 给单元格（row,col）赋值 xlSheet.PrintOut 打印工作表 xlBook.Close (True) 关闭工作簿 xlApp.Quit 结束 EXCEL 对象 Set xlApp = Nothing 释放 xlApp 对象 xlBook.RunAutoMacros (xlAutoOpen) 运行 EXCEL 启动宏 xlBook.RunAutoMacros (xlAutoClose) 运行 EXCEL 关闭宏 4、在运用以上 VB 命令操作 EXCEL 表时，除非
24、设置 EXCEL 对象不可见，否则 VB 程序可继续执行其它操作，也能够关闭 EXCEL，同时也可对 EXCEL 进行操作。但在 EXCEL 操作过程中关闭 EXCEL 对象时，VB 程序无法知道，如果此时使用 EXCEL 对象， 则 VB 程序会产生自动化错误。形成 VB 程序无法完全控制 EXCEL 的状况，使得 VB 与 EXCEL 脱节。 二、 EXCEL 的宏功能： EXCEL 提供一个 Visual Basic 编辑器，打开 Visual Basic 编辑器，其中有一工程属性 窗口，点击右键菜单的“插入模块 “，则增加一个“模块 1“，在此模块中可以运用 Visual Basic
25、语言编写函数和过程并称之为宏。其中，EXCEL 有两个自动宏：一个是启动宏（Sub Auto_Open()） ，另一个是关闭宏（Sub Auto_Close()） 。它们的特性是：当用 EXCEL 打含有 启动宏的工簿时，就会自动运行启动宏，同理，当关闭含有关闭宏的工作簿时就会自动运 行关闭宏。但是通过 VB 的自动化功能来调用 EXCEL 工作表时，启动宏和关闭宏不会自 动运行，而需要在 VB 中通过命令 xlBook.RunAutoMacros (xlAutoOpen)和 xlBook.RunAutoMacros (xlAutoClose) 来运行启动宏和关闭宏。 三、 VB 与 EXCE
26、L 的相互勾通： 充分利用 EXCEL 的启动宏和关闭宏，可以实现 VB 与 EXCEL 的相互勾通，其方法如 下： 在 EXCEL 的启动宏中加入一段程序，其功能是在磁盘中写入一个标志文件，同时在 关闭宏中加入一段删除此标志文件的程序。VB 程序在执行时通过判断此标志文件存在与 否来判断 EXCEL 是否打开，如果此标志文件存在，表明 EXCEL 对象正在运行，应该禁止 其它程序的运行。如果此标志文件不存在，表明 EXCEL 对象已被用户关闭，此时如果要 使用 EXCEL 对象运行，必须重新创建 EXCEL 对象。 四、举例： 1、在 VB 中，建立一个 FORM，在其上放置两个命令按钮，将
27、 ommand1 的 Caption 属性改为 EXCEL，Command2 的 Caption 属性改为 End。然后在其中输入如下程序： Dim xlApp As Excel.Application 定义 EXCEL 类 Dim xlBook As Excel.Workbook 定义工件簿类 Dim xlsheet As Excel.Worksheet 定义工作表类 Private Sub Command1_Click() 打开 EXCEL 过程 If Dir(“D:tempexcel.bz“) = “ Then 判断 EXCEL 是否打开 Set xlApp = CreateObject
28、(“Excel.Application“) 创建 EXCEL 应用类 xlApp.Visible = True 设置 EXCEL 可见 Set xlBook = xlApp.Workbooks.Open(“D:tempbb.xls“) 打开 EXCEL 工作簿 Set xlsheet = xlBook.Worksheets(1) 打开 EXCEL 工作表 xlsheet.Activate 激活工作表 xlsheet.Cells(1, 1) = “abc“ 给单元格 1 行驶列赋值 xlBook.RunAutoMacros (xlAutoOpen) 运行 EXCEL 中的启动宏 Else Msg
29、Box (“EXCEL 已打开 “) End If End Sub Private Sub Command2_Click() If Dir(“D:tempexcel.bz“) “ Then 由 VB 关闭 EXCEL xlBook.RunAutoMacros (xlAutoClose) 执行 EXCEL 关闭宏 xlBook.Close (True) 关闭 EXCEL 工作簿 xlApp.Quit 关闭 EXCEL End If Set xlApp = Nothing 释放 EXCEL 对象 End End Sub 2、在盘根目录上建立一个名为 Temp 的子目录，在 Temp 目录下建立一个
30、名为 “bb.xls“的 EXCEL 文件。 3、在“bb.xls“ 中打开 Visual Basic 编辑器，在工程窗口中点鼠标键选择插入模块，在模 块中输入入下程序存盘： Sub auto_open() Open “d:tempexcel.bz“ For Output As #1 写标志文件 Close #1 End Sub Sub auto_close() Kill “d:tempexcel.bz“ 删除标志文件 End Sub 4、运行 VB 程序，点击 EXCEL 按钮可以打开 EXCEL 系统，打开 EXCEL 系统后， VB 程序和 EXCEL 分别属两个不同的应用系统，均可同时
31、进行操作，由于系统加了判断， 因此在 VB 程序中重复点击 EXCEL 按钮时会提示 EXCEL 已打开。如果在 EXCEL 中关闭 EXCEL 后再点 EXCEL 按钮，则会重新打开 EXCEL。而无论 EXCEL 打开与否，通过 VB 程序均可关闭 EXCEL。这样就实现了 VB 与 EXCEL 的无缝连接。 Excel 制作奖金计算表 2006 年 08 月 16 日 03:52:06 陈秀峰 源码下载 某公司规定：一个月奖金基数为 300 元，病假 1 天扣 15 元，事假一天扣 30 元，旷工 一天扣 60 元，扣完为止。使用这个奖金计算表时，只要将员工的出勤情况记录在表中，该 员工
32、的奖金将自动计算出来，兼有考勤和计算奖金两种功能。自动统计表做好以后还可以 保存成模板，以便以后使用。 本文所涉及到的 Excel 函数有： 1、 COUNTIF(Range,Criteria) 2、 MONTH(serial_number) 3、TODAY() 注意：图中符号的含义是：B 表示病假，S 表示事假，G 表示旷工，Q 表示出勤，J 表示法定休息日。 1、启动 Excel2003，按照图 1 所示的样式，制作一张二维表格，并填入相关文本。 2、将 A1 至 BM1 单元格合并成一个单元格后，输入表格标题（如“晓风公司考勤及奖 金表”），并设置好字体、字号等。 3、将 C2 和 D2
33、 单元格合并后，输入公式：=MONTH(TODAY()&“月“，用于显示出 当前的月份。 注意：在上述单元格中直接输入月份数值也是可行的。 4、选中 BM4 单元格，输入公式：=IF(300-COUNTIF(C4:BL4,“B“)*7.5- COUNTIF(C4:BL4,“S“)*15-COUNTIF(C4:BL4,“G“)*300,300-COUNTIF(C4:BL4,“B“)*7.5- COUNTIF(C4:BL4,“S“)*15-COUNTIF(C4:BL4,“G“)*30,0)，确认，计算出第一位员工的当月 奖金额。 注意：上述公式的含义是：用“COUNTIF(C4:BL4,“B“)、
34、COUNTIF(C4:BL4,“S“)、 COUNTIF(C4:BL4,“G“)”分别用于统计每位员工的病假、事假、旷工的次数，再分别乘以 “7.5、15、30”（每“ 半天”为一次）计算出所要扣去的奖励额，再用基数“300”减去上述扣去 的金额。如果（IF）差大于“0”，则显示差（即员工的奖金额），否则显示 “0”（扣完为止） 。 5、再次选中 BM4 单元格，用“填充柄”将上述公式复制到下面的单元格中，用于计算 其它员工的当月奖励额。 注意：具体复制的数量，请根据员工的实际数目确定。 6、清空表格中所填写的数据，执行“文件保存”命令，打开“另存为”对话框（如图 2），按“保存类型 ”右边的
35、下拉按钮，选中“ 模板（*.xlt）”选项，在文件名方框中输入一名 称（如“奖金表 .xlt”），单击“保存”按钮，将制作好的表格文档保存为模板。 7、以后，每个月第一天，启动 Excel，执行“文件新建” 命令，展开“新建工作簿”任 务窗格（如图 3）。 点击其中的“ 本机上的模板”选项，打开“ 模板”对话框（如图 4），选中“ 奖金表”模板文 件，单击“确定 ”按钮即可新建一个空的“ 奖金表 1”工作簿文档。 选中 C2 单元格（即保存月份的单元格），将鼠标移到编辑栏中，然后按一下 F9 功能 键，将公式（=MONTH(TODAY()&“月“）转换为具体的值（如“12 月”），最后将文档保 存一下。 8、以后就可以用这个计算表逐日对员工进行考勤，到了月底就可以自动计算出奖金数据了。
