用Excel开发MIS的方法-中英文.doc

用Excel开发MIS的方法目前国内管理信息系统(MIS)开发研制一般采用人们熟悉的dBaseX、Foxbase或Foxpro等,这里将简要介绍如何用Excel开发MIS。一般来说,一套MIS主要包括信息输入、信息处理、信息输出三大组成部分,用Excel处理这些部分均显得游刃有余。Excel是Windows环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性。它同时具有电子数据表、图表和数据库的功能,具有极强的分析性能、报表制作工具和丰富的统计图表。笔者曾用中文Excel 5.0 for Windows为一企业开发研制了一套物资管理、财会管理、营销管理和人事管理的MIS,充分感受到其强大功能和方便性。下面就信息输入、信息处理、信息输出界面设计、代码设计和数据保护等方面的问题谈谈如何使用Excel。一、信息输入最常用的信息输入方法有三种,一是在工作表(WorkSheets)的单元格(Cells)中直接输入文字和数据(重复数据可由填充把柄拖拉而得);二是采用输入框(InputBox)输入少量数据;三是自行设计对话窗体(Forms),在其上可加入标签框、文本框、列表框、分组框、选择框、滚动条和命令按钮等,然后设计其对象格式(如属性、保护、控制项、对齐、字体和图案等)。当然,Windows提供的剪裁板进行数据的复制或粘贴也不失为一种常用的方法。此外,Excel还提供了强大的文件转换功能,可将Txt文件、dBase、Lotus 1-2-3文件、QuattroPro文件、MS Work文件、SYLK文件、DIF文件等直接转换为Excel工作簿文件,这就为数据的共享、快速录入及转换提供了极大的方便。二、信息处理按行业的不同,信息处理的要求有较大的差别,常用的信息处理包括数据的修改、查询、检索、排序和统计汇总。在dBase中,这些工作一般需要编程设计,而在Excel中,几乎所有的工作均可由Excel本身提供的功能轻松地完成。最常用的是右端数据菜单中的各项功能记录单选项提供了数据库记录查询、输入、修改或删除的简单方法;排序选项提供了数据库记录同时按三个字段的排序方法,可以按行或按列、以升序或降序、考虑或不考虑大写来进行排序;筛选选项提供了隐藏除符合指定条件外的所有数据的方法,为数据库同类记录的查询和重点显示提供了方便;分类汇总选项提供了同类数据的汇总及统计方法;数据透视表选项可以迅速使一个复杂的表格变得容易阅读和理解,它可以方便地概括出感兴趣的字段,从不同的角度透视出求和值、计数值、平均值、最大值、最小值、乘积值、标准偏差、总体标准偏差、变异值、总体变异值。表格设计是MIS的重点和难点之一,然而在Excel中,表格设计却易如反掌。Excel的工作簿(WorkBooks)是一个三维电子表格,一个工作簿由若干个工作表构成,每个工作表本身就是一张表格,它最多可达16384行256列,足以满足表格设计的需要。表格中每一行的高度和每一列的宽度均可随意调节,多余的行和列可以隐藏起来,表格中的每个单元格可有不同的字体、字号、边框线、前景色、数据格式、对齐方式和保护方式,在每个单元格中还可插入不同的文字附注和声音附注。此外,在每个工作表中可任意绘制直线、曲线、矩形、椭圆、箭头和各种窗体控件(Controls),并且还可插入其他Windows应用程序的图片和对象,支持DDL和OLE。Excel的这些功能为灵活设计各种复杂的表格提供了极大的方便。统计图表是各类MIS的特色之一,能否设计出美观易懂的图表从一个方面反映了MIS生命力的强弱。Excel的作图能力特别强大,操作使用方便,它可从工作表中灵活地提取所需的数据,快速生成二维或三维的条形图、柱形图、饼图、环形图、散列图、曲面图、雷达图等。三、信息输出Excel具有所见即所得的功能,凡是在工作表上能显示出来的内容(文字、数据、图形、图表)均可打印出来,超宽表格可以自动分页打印;其打印选项十分丰富,完全不需要自己编写另外的打印驱动代码。四、界面制作在Excel中,MIS的界面既可采用对话窗体进行设计，也可在工作表上直接进行设计,窗体的各种控件可随意放置在工作表上。在录制或编写了宏代码后,还可根据自己的需要任意修改Excel的菜单、工具箱以及提示行显示信息,而隐藏Excel本身的菜单系统,使MIS仿佛脱离了Excel处于单独运行之中。为了在进入Windows的同时就进入MIS界面,可在Windows的初始化文件WIN.INI中加入LOAD=C:Excel5Excel.EXE /E,在C:Excel5XLSTART子目录中加入MIS的界面控制程序,这样将在启动Windows的同时直接进入Excel环境并启动MIS的操作界面,而不显示Excel的启动封面;Auto-Open是含有特殊含义的子程序名称,它可在打开工作簿的同时运行Auto-Open中的语句。五、代码设计对于简单的MIS,一般不需要进行代码设计,用Excel本身提供的各项功能就可满足要求。除具有通用的工作表函数外,Excel还提供了丰富的日期和时间函数、财务分析函数、统计分析函数和假设分析函数。例如在B1单元格输入公式=AVERAGE(A1:A9),就可直接求出从A1到A9中各单元数据的平均值。然而对于比较复杂的MIS,则必须使用Excel的编程语言Visual Basic for Application (VBA,也就是Excel的宏)。VBA直接嵌入Excel的工作簿中,其语法规则非常类似于Visual Basic,比较容易掌握。VBA包含丰富的各种函数、方法、对象、属性和语句。理解并逐步掌握VBA中数量庞大的各类方法、对象和属性是在Excel平台上进行二次开发的关键技术。Excel的所有菜单操作均可通过VBA编程实现,此外VBA还可以实现Excel本身不具备的功能,如API函数的调用。利用Excel提供的录制宏功能是初步学习VBA的简捷方法。熟悉VBA之后,就可自己修改宏并编写新的宏。需要说明的是,中文Excel 5.0的宏支持汉字过程名和变量名,这就为程序的阅读和维护带来了便利。例如以下的代码片段是正确的1: Sub求和计算()2: Dim 被加数,加数,求和数3: 被加数=100: 加数=3504: 求和数=被加数+加数5: MsgBox 被加数 & 与 & 加数 & 的和等于 & 求和数6: End Sub以上程序的运行结果如下图所示图六、数据保护MIS的数据保护能力是用户和软件开发人员共同关心的问题。用dBase开发的MIS,往往需要为设计数据库的保密功能而绞尽脑汁。然而在Excel中,您不必编写任何程序代码就可达到数据保护的目的。Excel为用户提供了多级保护手段,可以自行选择是否保护每个单元格或每个控件,是否保护某张工作表,是否保护每个工作簿,是否需要输入打开工作簿的口令。一般而言,打开工作簿的口令由用户设定,其他保护功能由程序设计人员设定,一经设置了保护功能,任何人(包括程序设计者)在不知道口令的情况下均无法越权使用。Using Excel MIS development methods At present domestic Management Information System (MIS) developed by people familiar with the general dBaseX, Foxbase or Foxpro, here is a brief on how to use Excel development of MIS. Generally speaking, a set of MIS primarily include the information input, information processing, information output three major components of the use Excel to deal with these parts seem to cope. Excel under the Windows environment is an electronic forms software, gives users unprecedented super functions and ease of use. It also has electronic data tables, charts and database functions, which are very analytical performance, financial statements, production tools and rich statistical tables. The author has used Chinese Excel 5.0 for Windows for enterprises to develop a set of materials management, accounting management, and sales management and personnel management Justification of MIS, fully aware of its power and convenience. Below the information input, information processing, information output interface design, code design and data protection, and other issues on how to use Excel. First, the input information The most common information input in three ways, in the First Working Table (WorkSheets) cells (Cells) directly enter text and data (data duplication can be derived from filling handle tractors); Second, it has the input box (InputBox) a small amount of input data; Third, it is designed dialogue form (Forms), which may be included in the labels on its box, text box, list box, group box, select box, and rolling and command buttons, and so on, and then designing their object formats (such as attributes , protection, control, alignment, fonts and patterns, etc.). Of course, the Windows tailoring plate copy or paste data also may be a commonly used method. In addition, Excel also offers a powerful document conversion functions can be Txt documents, dBase, Lotus 1-2-3 documents, QuattroPro documents, MS Work document, SYLK documents, DIF files directly into Excel workbook document, which for the sharing of data, and rapid entry and conversion offer great convenience. Second, information processing According to the different sectors, the information processing requirements of large differences, common information processing including data changes, query, retrieval, sorting, and statistical summary. In dBase, these generally require programming design, and in Excel, almost all the work can be provided by the functions of Excel itself easily completed. Right is the most commonly used data in the various functions of the menu: Records option provides a database record for the importation, modification or deletion of a simple method; sort option at the same time provide a database records by name the sort of approach can be shown by the row or, in ascending or descending order, consider or not consider capital to sort; screening option provides hidden in addition to meeting all the conditions specified in the methods used, Similar records database query and display focus on providing a convenient classified summary option provides similar data aggregation and statistical methods; pivot table Options can quickly make a complex forms easier to read and understand, it can be conveniently summarized interested in the field, from different angles Focus a summation value, or value, average, maximum, minimum, the product value, standard deviation, the overall standard deviation, variance values, the overall variation value. Form design is the focus of MIS and difficult one, but in Excel, forms design is easy. Excels workbook (WorkBooks) is a three-dimensional spreadsheet, a workbook consists of a number of a table, each table is itself a form it up to a maximum of 16,384 to 256, enough to meet the needs of forms design. Form each row height and the width of each column can be adjustable, extra rows and columns can be hidden, in the form of each cell may have different font, size, the frame line, Jianjingse, data formats, - ways and means to protect in each cell can be inserted in the text notes and different voices Note. In addition, each table is free to draw straight lines, curves, rectangular, oval, arrow and All form controls (Controls), and can be inserted into other Windows applications images and objects, and OLE support DDL. Excel functions of these various complex flexible forms design provides great convenience. Statistical tables is one of the characteristics of various types of MIS, can design a handsome easy-to-understand charts from a vitality is reflected in the strength of MIS. Excels drawing ability, particularly powerful, easy-to-use operation, it can work in the table from the flexibility to extract the required data, and rapid generation of two-dimensional or three-dimensional bar charts, column chart, Bingtu, circular diagram, hash map , surface charts, radar and other plans. Third, information output Excel with WYSIWYG functionality, all in the workplace can be displayed on the table of contents (text, data, graphs, charts) can be printed, wide forms can be automatically printed page; its print options is very rich, and completely Also do not need to prepare their own code of Printer Driver. Fourth, the production interface In Excel, MIS can use the interface design dialogue form, it may also work directly on the design table, a form of control can easily be placed in the work table. In the macro recording or writing code, according to their own needs can be arbitrarily modified Excel menu, as well as tips toolbox to display information, and hide its own Excel menu system, which seems divorced from the MIS Excel in a separate operation. In order to enter the Windows interface on the same access to MIS, in the Windows initialization file WIN.INI adding LOAD = C: Excel5 Excel.EXE / E, C: Excel5 XLSTART subdirectories into MIS interface control procedures, which would start in the Windows environment at the same time directly into Excel and MIS started the operation of the interface, rather than cover the start-up display Excel; Auto-Open contain special meaning of the subroutine name, it can open the workbook at the same time running Auto - Open in the statement. 5, code design For simple MIS, the need for general code design, Excel itself will meet the functional requirements. In addition to the work of a generic form, function, Excel also provides a wealth of the date and time functions, financial analysis function, statistical analysis functions and assumptions analysis functions. For example, in cell B1 enter the formula = AVERAGE (A1: A9), can be obtained directly from A1 to the A9 in the averaged data for each cell. But for the more complex MIS, we must use Excel programming language - Visual Basic for Application (VBA and Excel is the macro). Excel VBA directly embedded in the workbook, grammar rules very similar to Visual Basic are relatively easy to grasp. VBA contains rich variety of functions, methods, objects, attributes and expressions. VBA understand and gradually got in a huge number of various methods, and attributes of objects in the Excel platform, the development of key technology second. Excels menu operation can be adopted by all VBA Programming, and VBA can also achieve Excel itself does not have features such as API function calls. Using Excel to provide recording- VBA function is preliminary study of the fast-track approach. Familiar with VBA, and Acer will be prepared to amend its new Acer. Must be explained that the Chinese Excel 5.0 Macro support Chinese characters and the process of the variable name, which is the process of reading and maintenance is made easier. For example, the fol