基于函数的Excel软件的应用技巧研究

Research on application skills of Excel software based on functions

通过实例介绍了如何利用Excel函数功能解决工程项目进展、行政助理统计、经费开支对账的几点技巧。文中首先明确了Microsoft Office Excel 2003在数据统计方面的几点限制,进而介绍了对项目进度数据的信息汇总、处理、统计和分析技巧;在行政管理工作中,利用VLOOKUP函数设定参数用以解决数据筛选、排序问题,对统计报表进行数据透视表的操作,实现行与列的换位统计汇总,从而构建不同统计意义的报表;在电算化会计业务中,使用"查找引用(VLOOKUP)"函数功能,将财务记账数据转化为Excel表格数据,形成易看易懂的"交互式统计报表",方便各位职工和下属单独自行核对,轻松、快速地完成对账结算。

Through some examples, this paper introduces some techniques to use Excel function to solve project progress, administrative assistant statistics and some skills of expenditure reconciliation. The paper first tells some limitations of Microsoft Office Excel 2003 in terms of data statistics, and then introduces the project schedule data information aggregation, processing, statistical and analytical skills. In administrative work, VLOOKUP function is used to set parameters to solve the problem of data filtering and sorting. The statistical form is operated with data pivot table to realize the row and column transposition statistical summary so as to build different statistical statements. In computerized accounting business, by using VLOOKUP function, the financial accounting data can be turned into Excel spreadsheet data and form“interactive statistical statement”which is easy to read and understand and is convenient for the fellow employees and subordinates to check alone and complete the reconciliation and settlement easily and rapidly.