Excel common function Literacy Practical instruction _ office automation

Source: Internet
Author: User
Tags vlookup function

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:

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.

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:

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:

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.