Millennium Tongzhou vitality north stream
Queen of the Doctor
1, excel's quick access toolbar:
My quick access toolbar from left to right is mainly "Save", "New", "Undo", "Restore", "Ascending Sort", "Descending Sort", "Print Preview", "Insert Pivot Table", "Middle after merging", "format brush", "paste special as value", "filter", etc., this comparison basis. 2.Custom phrase settings for Sogou input method
It has nothing to do with excel, but the effect is outstanding. If it weren't for this feature, I would have given up a bunch of ads for Sogou input method. . 3. Share some common shortcut keys that I have arranged for the beginners around me. Although it helps to increase the popularity of reaching out, this question is really on time and I can't help answering it.
(1 ) F key function keyboard area
F4: Very practical, the function is to repeat the last operation. For example, to change multiple discontinuous cells to fill yellow, you can select the first cell, set the fill yellow, and then select the remaining cells in turn, each time you press F4.
Shift + F11: Insert a blank worksheet. Note that compared with Ctrl + N, the former new worksheet is sheet1, and the latter new workbook is book1.
(2 ) Numeric key area
Ctrl + 1: Format cells
Ctrl + 2: Bold (same as Ctrl + B)
Ctrl + 3: Tilt
Ctrl + 4: Underline
(3 ) Symbol key area
Alt + Plus: Sum. You can select the content you want to sum and press the shortcut key to get the result, or you can press the shortcut key first and then select the content you want to sum.
Alt + semicolon: locate visible cells, which is equivalent to pressing Ctrl + G, and select "visible cells" in "positioning conditions". When modifying the table in the filtering state, first select the content, press Alt + ";", and then operate to ensure that only the visible cells are operated without affecting the part that is not filtered out. Specifically, if you need to delete the male salary in the salary table, filter the gender as "male", delete the filtered content, and the direct operation may cause the middle gender "female" to be deleted. You need to select the filter with the mouse. After the content comes out, press Alt + ";" and delete the line.
Ctrl + minus: delete, select a row or column, press this shortcut to directly delete the row or column. If you press the shortcut key after selecting a cell, it prompts "Move the right cell to the left" or "Move the lower cell up".
Ctrl + double quotes: Clear cell contents (delete faster).
Ctrl + Semicolon: Change the current cell content to the current date, the format is 2014-XX-XX.
Ctrl + "↑" "↓" "←" "→": the cursor moves to the top / bottom / left / right cell, if the cell has data, it moves to the edge of the data; if the cell has no data, it moves to Stop at the edge of the entire worksheet or encounter other data.
Ctrl + Shift + "↑" "↓" "←" "→": Same as above, but the cell on the cursor movement path will be selected.
(4 ) Letter key area
Ctrl + Z: Undo
Ctrl + X: Cut
Ctrl + C: Copy
Ctrl + V: Paste
Ctrl + B: bold (same as Ctrl + 2)
Ctrl + I: tilt (same as Ctrl + 3)
Ctrl + U: underline (same as Ctrl + 4)
Ctrl + N: New workbook
Ctrl + A: Select all, select all content if cell has content, select entire worksheet if cell is blank
Ctrl + S: Save
Ctrl + D: Paste special, which is equivalent to holding down the solid cross in the lower right corner of the cell to pull down (Ctrl + R is right)
Ctrl + F: Find / Replace (Ctrl + H Replace)
Ctrl + G: Position
Ctrl + K: Insert hyperlink
Ctrl + P: print
Ctrl + W: Close the current workbook
(5 ) Windows key
Win + E: Open "My Computer", very useful to improve the speed of finding and opening files
Win + D: Show desktop, equivalent to minimize all
Win + L: Lock the computer
Win + X: Manage power, connectivity, brightness, etc.
(6 ) Other keyboard areas
Ctrl + PageUp / PageDown: move to previous / next sheet
PageUp / PageDown: scroll up / down a worksheet
Alt + PageUp / PageDown: Scroll the worksheet one page left / right
Ctrl + Home: Move the cursor to the upper-left corner of the worksheet
Ctrl + End: the cursor moves to the bottom right of the data in the worksheet
Ctrl + Enter: Paste in bulk. Select most cells, enter a value or formula, and press this shortcut key, all cells will be filled.
(7 ) Date function
① date function: = date (A1, B1, C1), which can be used to convert the month, day, etc. distributed in different cells to date format. If = date (2014,12,31), "2014-12-31" is displayed
② datedif function: = datedif (A1, today (), "y")
God Zhao Yun
= datedif (A1, today (), "m")
= datedif (A1, today (), "d")
Find the difference between two dates, which can be used to calculate age, working age, etc.
③ month function, day function, returns the month or day of a certain date
4.Combination of shortcut keys and functions
(1) Batch filling of continuous cells
Drag a cell value or formula to most of the cells below. I usually see people dragging or double-clicking the solid cross in the lower right corner of the cell, but if there are many rows, dragging is too slow. Double-clicking You cannot determine whether there are line breaks in the middle.
Example: For 10,000 rows of data, column A is the serial number, column B is the formula, B1 = A1 * 2, and column B needs to be filled. My approach is as follows:
① Mouse point in any cell in column A, press ctrl + “ ↓ ”, the cursor moves to A10,000
② The cursor moves one space to the right to reach B10,000
③Press ctrl + shift + “↑”, the cursor selects the area from B1 to B10,000
④Press ctrl + D to automatically fill
The advantage of this is that you can judge whether there are line breaks in the two columns A and B by the way, and it is relatively intuitive. If it is successfully completed, the data in both columns is complete.
(2) Batch filling of non-continuous cells
The example picture is a detailed ledger exported by the Kingdee system. Some data has tens of thousands or hundreds of thousands of rows, and the content to be filled in each subject is different. It cannot be dragged to fill at one time. Assuming there are 50,000 rows of data, my approach is:
①Move the mouse to any cell in column C, press ctrl + “ ↓ ”, the cursor moves to C50,000
②Click C50,000 with the mouse, and drag to B50,000 while holding it, so that
Note that you have to drag from column C to column B, and vice versa. Because column C is a continuous cell, you can proceed to the next step
③Press ctrl + shift + “↑”, the cursor selects the area from B1 to C50,000
④ Press ctrl + G and select "Positioning condition" as "null value"
After clicking OK, all the null values in column B are located:
⑤ Enter "=" directly at this time, the formula will appear in the first empty value, cell B3, and enter "= B2", which is equal to the previous cell
⑥ Press ctrl + enter, each empty value previously positioned is filled with the formula "equal to the previous cell"
Note: If the computer is not afraid of the card, you can directly drag from column C to column A in the second step, and fill the two columns A and B together.
PS: If even column C is discontinuous, you can insert a column to the left of column A and fill it all up. Then you can move to the bottom row to operate upwards.
(3) Convert date in text format to date format
I don't know if there is a more concise way, my method is as follows:
(4) Create a primary index for the table
Still taking the exported detailed account as an example, after screening the sales expenses, I found that there are several abnormal expenses. I want to see the complete voucher. However, the detailed ledger is arranged by subject, and the same entry is scattered among several subjects. The usual practice is to filter the voucher number and then the month to locate a voucher. If a column of data is added as month + voucher number, it can be used as the main index for filtering vouchers.
As shown in column B:
(5) Two-way link to determine whether two columns of data are mapped one by one
Example of two lists of people in different orders, check to see if they are the same
After linking the two columns of work numbers in the figure, it can be seen that work number 1 is a subset of work number 2, but 100014 of work number 2 is not included in work number 1. ( ☆ Note: The formula is wrong, F: F on the left should be changed to D: D, F2 on the right should be changed to D2, I will not delete the picture. )
Advanced application: Determine whether the credit accruals payable to employees' salaries correspond to the debits of costs and expenses (note that the data are summarized by "month + voucher number")
The source network of this article, the copyright belongs to the original author
Students who have enrolled in the 2017 Xiao Ai Intermediate Insurance Class please contact:
Xiao Aibao's passing class is suitable for candidates who have a poor foundation, are usually busy, have poor self-discipline, and are not sure about the exam. Through 4 rounds of learning method + supervised learning + monthly assessment, they help candidates pass the intermediate
Little Ai to Work (xiaoaicoco)