Excel Common function Literacy

This article is used for literacy, mainly explaining several of the most commonly used functions if, and, or, COUNT, COUNTA, countif and Sum, the students will be able to skip. The application of other functions will be explained gradually in the future.

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),"周末","工作日")

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")　　　统计F列中OK的个数　
=COUNTIF(F:F,"NG")　　　统计F列中NG的个数

3. Sum of sum

This function is simply no longer simple:

=SUM(A10:A254)　　　　　对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")　　　统计OK个数　
=COUNTIF(E:E,"NG")　　　统计NG个数

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.