Excel Formula & Operation Skills

Input & reference

1. Drag a cellArrow(When dragging, press Ctrl to fill the string in sequence)

2. Enter the date (CTRL +;) and current time (CTRL + Shift + ;)

3. Enter a number as a string (enter a 'before entering a number ')

3. Change multiple cells to the same content (press Ctrl to select multiple target cells, enter the content, and press Ctrl + enter to end the input)

4. Selective pasting

5. relative reference & absolute reference & hybrid reference ($, You can use F4. you can use F4)

Note: When the relative reference is dragged or cut, the directed cell changes.

Formulas & Functions

Note: excel2010 Functions

1. sum (range) // if the sum range is across sheet, you need to add the sheet name in front! Cell range, for example:Sheet1! C2: C11

 

2. Average (range) // calculate the average value

 

3. INT (value) // integer

4. Round (numeric value, number of digits after the decimal point) // rounding

= Round (2.15, 1) // rounds 2.15 To a decimal place, and the result is 2.2.
= Round (2.149, 1) // round 2.149 to a decimal place and the result is 2.1
= Round (-1.475, 2) // round-1.475 to two decimal places and the result is-1.48
= Round (21.5,-1) // round 21.5 to the left of the decimal point and the result is 20

5. rounddown (numeric value, number of digits after the decimal point) // directly Truncation

= Rounddown (2.15, 1) // truncate 2.15 To a decimal place and the result is 2.1.
= Rounddown (2.149, 1) // truncates 2.149 directly to a decimal place and returns 2.1
= Rounddown (-1.475, 2) // truncates-1.475 directly to two decimal places and returns-1.47

6. Roundup (numeric value, number of digits after the decimal point) // carry directly

= Roundup (2.15, 1) // carry 2.15 directly to a decimal place and the result is 2.2
= Roundup (2.149, 1) // carry 2.149 directly to a decimal place and the result is 2.2
= Roundup (-1.475, 2) // carry-1.475 directly to two decimal places and the result is-1.48

7. Mod (numeric value, modulus) // Modulus

8. Count (range) // count object is a number

 

9. counta (range) // count the count objects of counta as non-empty cells

10. Max (range) // calculate the maximum value

11. Min (range) // calculates the minimum value

12. Product (range) // concatenation

13. sumif (condition range, condition, sum range) sumifs (sum range, condition range 1, condition 1, condition range 2, condition 2) // condition summation

14. countif (condition range, condition) countifs (condition range 1, condition 1, condition range 2, condition 2) // count the number of conditions

15. Now () // current date and time

 

16. Today () // today's date

17. date (year, month, day) // Date of Generation

 

18. Year (date) month (date) Day (date) // calculate the year, month, and day from the date

19. datedif (start date, end date, unit) // This function calculates the difference between two days

= Datedif ("2000-1-1", "2010-7-8", "Y") // 10
= Datedif ("2000-1-1", "2010-7-8", "M") // 126
= Datedif ("2000-1-1", "2010-7-8", "D") // 3841
= Datedif ("2000-1-1", "2010-7-8", "MD") // 7
= Datedif ("2000-1-1", "2010-7-8", "ym") // 6
= Datedif ("2000-1-1", "2010-7-8", "YD") // 189

20. Len (text) // evaluate the length of the text

 

21. Left (text, number of truncated digits) // obtain the text of the specified length on the left of the text string

 

22. Right (text, number of captured digits) // obtain the text of the specified length on the right of the text string

23. mid (text, start digit, cut digit) // obtain the text in the specified area of the text string

24. Upper (text) // converts English characters into uppercase letters

25. Lower (text) // converts English characters to lowercase letters

26. Proper (text) // converts the first character of an English character to uppercase, and the other part to lowercase.

27. Text (value, format) // output text content in the specified format

= Text (now (), "YYYY") // 2013
= Text (now (), "[dbnum1] yyyy-mm-dd") // May 29, Year 13
= Text (now (), "[dbnum2] yyyy-mm-dd") // the log of Wu Yue, yyyy-mm-dd.
= Text ("0123456789", "[dbnum2] 0000000000") // zero errors

Format description:

(:) Time Separator
(/) Date Separator

D. display the day with no leading zero (1-31)
Dd displays the day with a leading zero number (01-31)
Ddd is abbreviated to Sun-sat)
The full name of dddd indicates Sunday-Saturday)
M Displays the month (1-12) with no leading zero ). If M is directly following h or HH, it will be displayed in minutes instead of months.
MM displays the month (01-12) with a leading zero ). If M is directly following h or HH, it will be displayed in minutes instead of months.
Mmm stands for month (Jan-Dec) in short)
Mmmm stands for month (Januar-December)

Y or YY represents year (00-99) by two digits)
YYY or yyyy indicates the year in four digits (0000-9999)
H displays the hour with no leading zero (0-23)
HH displays the hour with a leading zero number (00-23)
M Displays the score (0-59) after H or HH with no leading zero.
MM displays the minute (00-59) after H or HH with a leading zero number.
S: The second (0-59) must be displayed after H or HH with no leading zero.
SS displays the second (00-59) with a leading zero number after H or HH.
AM/PM is used with the uppercase am symbol for 12 hours before noon, and with the uppercase PM for 12 hours after noon.
AM/PM is used with the lowercase am symbol 12 hours before noon, and with the lowercase PM 12 hours after noon.
A/P is used with the uppercase a symbol 12 hours before noon, and with the uppercase P symbol 12 hours after noon.
A/P is used with the lowercase a symbol 12 hours before noon, and with the lowercase P symbol 12 hours after noon.

(0) Digit placeholder. Display a digit or zero
(#) Digit placeholder. Display a number or nothing
(.) Decimal place placeholder
(%) Percent symbol placeholder
(,) Sub-digit placeholder
(E-e +) Scientific format
@ Character placeholder. Show characters or spaces
& Character placeholders. Show characters or nothing

28. numberstring (numeric, type) // convert a positive integer to uppercase

= Numberstring (1234567890,1) // 1.2 billion 34,567,890
= Numberstring (1234567890,2) // you can pick up hundreds of millions of Alibaba Cloud Resources
= Numberstring (1234567890,3) // May 26, 1234

29. and/or (condition 1, condition 2,..., condition 30), not (condition 1) // condition Logic

30. & // character connection

31. Indirect (reference region path text) // return the reference specified by the text string

= Indirect ("master table! $ D $2: $ d $1000 ") // reference the $ d $2: $ d $1000 region of the primary table named sheet.

32. search (text to be searched, text string, [start Index]) Find (text to be searched, text string, [start Index]) // return the location index of the text to be searched (index starts from 1)

= Search ("C", "Excel and access") // return 3
= Find ("C", "Excel and access") // return 3

Differences:
A. the find function is case sensitive, while the search function is case insensitive.
= Search ("e", "Excel and access") // return 1
= Find ("e", "Excel and access") // return 4

B. The search function supports wildcards, but the find function does not.
= Search ("ce? "," Excel and access ") // return 3
= Find ("ce? "," Excel and access ") // The error" # Value! "is returned !"

Others

1. wildcard support (Search & replace range: can be the current sheet and the entire work thin file; the object can be values, formulas, and annotations)

? // Represents a character
* // Multiple characters
Note: search? ,*,~ Use ~? ,~ *,~~

2. Data Validity

3. Save the file as a CSV file (a comma-separated text file)

4. Import external data

5. Freeze

6. sort and filter

7. Charts

8. Shortcut Keys

CTRL + Page down // The Next sheet
CTRL + page up // previous sheet

F4 // repeat the previous command or operation (if possible)
ESC // cancel the input in the cell or edit bar
Enter // complete the cell input from the cell or edit bar, and (default) Select the following Cell
Tab // complete the cell input and move a cell to the right of the worksheet
Alt + enter // you can create a new row in the same cell.

Home [end] // move to the beginning/end of the row
CTRL + home [end] // you can move it to the beginning or end of the worksheet.
CTRL + Shift + home [end] // you can extend the selected range of cells to the beginning/end of a worksheet.
CTRL + A // select the entire worksheet. If the worksheet contains data, press Ctrl + A to select the current region,
Press Ctrl + A again to select the current Region and Its summarized rows. Press Ctrl + A for the third time to select the entire worksheet.

Direction key // move a cell up, down, left, or right in the worksheet
CTRL + arrow keys // move to the edge of the current data area in the worksheet.
Shift + arrow keys // expand the selected range of a cell.
CTRL + Shift + arrow keys // extend the selected range of cells to the last non-empty cell in the column or row of the active cell,
Or, if the next cell is empty, the selected range is extended to the next non-empty cell.