Excel common function Literacy Practical instruction _ office automation

1. Logical Judgment

Logical judgments are not used in many functions, and if, and, or three are enough to handle day-to-day work.

The IF function can be used to convert values, such as 1 and 0 to OK and ng:

=if (a1=1, "OK", "NG")

combinations with and and or can determine multiple conditions, such as whether it is a weekend:

=if (OR (Weekday (A1) =0, Weekday (A1) =6), "Weekend", "weekday")

2. count, COUNTA, COUNTIF of statistics

The count and CountA statistics objects are different, CountA counts the number of all non-empty cells (including the cells that are wrong), and count counts only the cells that look like numbers.

Countif can add search criteria, which can be used to do statistics. Such as

=countif (f:f, "OK")   statistics number of OK in F column
=countif (f:f, "ng")   statistics of NG in F column

3. Sum of Sum

This function is simply no longer simple:

=sum (a10:a254)     sums the range of a10~a254

No more. Although there are hundreds of Excel functions, these are the ones that are commonly used. At best, add several other informational functions, such as date, year, MONTH, days, now, weekday, numeric calculation floor, INT, MOD, ROUND, Char, left, right, MID (see Help for specific use), almost all of the day-to-day applications can be met.

Here are a few examples to illustrate the application of these functions.

1. test Cases . The table for a generic test case would be this:

	A	B	C	D	E	F
1	Number	Category	Test content	Confirm Content	Results	Test time
2	1	Interface	Click the New button	Create a new document	Ok	8/27
3	2	Interface	Click the Save button	Save document	Ok	8/27
3	2	Interface	Click the Save As button	Open Save dialog box	NG	8/27

Then the statistics of OK and ng are separated by the number of

=countif (e:e, "OK")   statistics of OK number
=countif (e:e, "ng")   statistics NG number

If the test case is divided into worksheets, you can add a statistical worksheet to the front and use sum to find out the status of all use cases.

2. Calendar . This calendar is used on project progress management and is similar in format to the horizontal calendar below.

August
1	2	3	4	5	6	7	8	9	10	11
Three	Four	Five	Six	Day	One	Two	Three	Four	Five	Six

You can enter the following format:

	A	B	C	D	E	F	G
1	8/1	=a1+1	=b1+1	...	...	...	...
2	=month (A1)	=if (MONTH (B1) =month (A1), "", MONTH (B1)	...	...	...	...	...
3	=day (A1)	=day (B1)	...	...	...	...	...
4	=mid ("Day 123456", Weekday (A1), 1)	...	...	...	...	...	...

Then hide the first line.

2008-2-14 Update to answer the question of the reader Nini, explain the use of the VLOOKUP function.

VLOOKUP is used to find information, or rather, to "translate" information. For example, the employee list has employee number and name correspondence, then give a list of employee numbers, you can through the VLOOKUP to "translate" into a list of names. Let's take a look at an example:

	A	B	C	D
1	Ranking	Name	Ranking	Name
2	1	Cohe Evil	3	=vlookup (C2, $A $: $B $8,2)
3	2	Chu	3	=vlookup (C3, $A $: $B $8,2)
4	3	Han Bao	4	=vlookup (C4, $A $: $B $8,2)
5	4	Nanchiren	7	=vlookup (C5, $A $: $B $8,2)
6	5	Zhang Asseng
7	6	All blond
8	7	Hanxiaoying

The $a$2 of this table: $B the area of the $ $ as reference data (original correspondence), c as the original data to be translated, d as the result of translation. Concrete results you can actually put in Excel to perform.

VLOOKUP has three parameters, respectively, as follows:

VLOOKUP (raw data, reference data, search results in the column position in the reference data)

Excel searches the first column of the reference data for the original data (in the example above, in $a$2: $B the first column of the $ $ $a$2: Search the original data C2 in the $A $), and when found, returns the third parameter-the column position corresponding to the data (in the previous example, in $a$2: $A $ After finding the equivalent of 3 in 8, it returns the 2nd column corresponding to 3-Han Bao, which is the third parameter, the 2nd column (C2).

So what if you can't find it? In fact, VLOOKUP also has the fourth argument, the value is true or FALSE, and the default is true. When the value is TRUE, VLOOKUP returns a result that is not greater than the minimum value of the original data, such as =vlookup (2.5, $A $: $B $8,2,true) returns "Chu" (Chu's "2" is the maximum value not greater than 2.5). When the value is False, VLOOKUP returns #n/a.

Usually when you are working with strings, you will most likely want to return an error or empty string when you cannot find them. At this point, you can add the fourth parameter false to the VLOOKUP, and then use the ISNA function to convert the #n/a to an empty string. Such as:

[D2]=vlookup (C2, $A $: $B $8,2,false)
[D3]=if (ISNA (D2), "", D2)