Use Excel to do personnel statistics

Whether it is a university, a business or a government agency, it is essential to complete a wide range of personnel statistics and production roster each year. Database software entry thresholds for SQL Server, Access, and so on are high and are not suitable for use by ordinary workers. The author found in the continuous exploration of the use of Excel array formula for the management of personnel roster, the clever method of statistics, is described below.

This article will make the university teachers at the end of the statistical Register, report examples to introduce the specific operation. We also require that the report be updated automatically when an employee record is added or deleted.

I. Preparation of data

1. (a) The existing teacher roster is exported from the database, stored as an Excel form, adapted to the roster requirements and made into a roster of persons;

2. Select insert → name → definition in the menu to define the meaning of each column, so that we can refer directly to the collection of the column in the formula. For example: the "unit" reference position is "= list!" $A $: $A $344 "represents a set of cells within this range of row 2nd to line 344 in the list, which we call units. Define the name we need and save the worksheet.

3. Select the Insert → worksheet option, insert a new worksheet, and change the name to "College statistics." Set up the header and the necessary statistic columns in the form. All preparations are complete and we will fill in the table below.

Second, the statistical combat

1. First, the statistics of the total number of colleges. In the College 1 total number of columns filled in the formula "=coun TIF (Unit," College 1 ")" (excluding quotes, the same below). COUNTIF can calculate the number of cells in a range that meet a given condition. The standard format is COUNTIF (Range,criteria) and range is the range of cells that satisfies the criteria. Criteria is a condition that determines which cells will be counted, in the form of numbers, expressions, cell references, or text. COUNTIF (Unit, "College 1") means the total number of cells in the statistical Unit collection that have the contents of "College 1".

2. Enter "=sum (unit =" College 1 ") * (gender =" female ") in the female column of the College 1)", when finished, press the "Ctrl+shift+enter" key to generate the array formula {=sum (unit = "College 1") * (gender = "Female")}, The calculation structure is also automatically displayed in the cell. The formula means the total number of cells calculated as "College 1" and gender as "female". "*" stands for "and" relationship.

And so on, after the College 1 cell, fill in the following formula:

Ph. D: =sum ((unit = "College 1") * (Academic degree = "ph. D"))

Master: =sum (unit = "College 1") * (Academic degree = "master"))

Postgraduate ratio: not part of the statistical content, as long as the use of formula calculation (PhD + Master)/Total number can be.

After completing all the College 1 formulas, the copy function is used to copy all the formulas of the college 1 to the 2 Formula section of the College. Using the replacement function, the College 2 of all formula cells in the content of "College 1" replaced by "College 2", you can get the College 2 formula. Can be reused, fill out other cells, and finally use the summary function to complete the total column. In this way, a statistical report is generated.

3. Array formulas are different from array operations, array formulas usually end with "Ctrl+shift+enter", and when you cannot get results directly from a worksheet function, the array formula is particularly important, it can produce multiple values or manipulate a set of values, or you can generate more complex formulas.

For example: Ask the College 1 Chiang Kai-shek senior officers have a doctorate and gender is female, the number of people under 35 years of age, we can write as:

=sum (unit = "College 1") * (Academic degree = "ph. D") * (gender = "female") * (age

Third, summary

The use of Excel array formula to make the roster and statistical reports greatly reduce the difficulty of statistics, but also facilitate the sharing of data. The use of data protection to protect the formula cells to form a template, distributed to other people, users can only understand the simple Excel editing knowledge to use. With the completion of this year's statistical work, a new Year's statistical roster will automatically be formed as soon as the new data is replicated to the roster next year. Greatly simplifies the troubles caused by year-end statistics.