hg5596.com [AI excel] What are the Excel skills you must know to do financial work? How much do you have?

[AI excel] What are the Excel skills you must know to do financial work? How much do you have?

Public number: Xiao Ai's work report Source: Time: 2020-01-08 19:00:23

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:

Head teacher Martin: WeChat sailor2018 QQ249917164

Xiaoai Finance and Tax Customer Service: WeChat xiaoai220225 QQ732622764

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)

Spider pond