2. Press the "Add" button and set other conditions as above (1500 or more, and the font is set to "blue"; Less than 1000, and the font is set to "brown").
3. After setting, press the "OK" button.
Look at the payroll. Are payroll data displayed in different colors according to your requirements?
Secondly, create a category drop-down list to fill in the project.
We often need to enter the company name into the form. In order to keep the name consistent, we use the "data validity" function to build a category drop-down list to fill in the project.
1. In Sheet2, enter the enterprise name in different columns according to the category (such as "industrial enterprise", "commercial enterprise" and "individual enterprise", etc. ) Establish enterprise name database.
2. Select column A (the column where the name of "industrial enterprise" is located), enter the character of "industrial enterprise" in the column of "Name", and then press Enter to confirm.
Imitate the above operations, and name columns B and C as "commercial enterprises" and "individual enterprises" respectively.
3. Switch to Sheet 1, select the column (such as column C) where the enterprise category needs to be entered, and execute the data → validity command to open the data validity dialog box. In the Settings tab, click the drop-down button to the right of Allow, select the Sequence option, and enter the sequence of Industrial Enterprise, Commercial Enterprise and Personal Enterprise (separated by English commas) in the Source box below and confirm to quit.
Select the column (such as column D) where you need to enter the enterprise name, and then open the Data Validation dialog box. After selecting the series option, enter the formula = indirect (C 1) in the source box and confirm to exit.
4. Select any cell in column C (such as C4), click the drop-down button on the right, and select the corresponding "enterprise category" to fill in the cell. Then select the cell corresponding to this cell in column D (for example, D4), click the drop-down button to select the required enterprise name from the enterprise name list of the corresponding category, and fill it in this cell.
Tip: If you don't need to print the Enterprise Category column when printing the report in the future, you can select the column, right-click it and choose the "Hide" option to hide it.
Third, establish a new "General Document" menu.
Create a new "Common Documents" menu on the menu bar, and add frequently used workbook documents to it so that it can be called at any time.
1. Click the right mouse button in the blank space of the toolbar and select the Customize option to open the Customize dialog box. In the Commands tab, select the new menu item under Category, and then drag the new menu item under Commands to the menu bar.
Press the "Change Selection" button and enter a name (such as "Common Document") in the "Naming" box of the pop-up menu.
2. Select an item under Category (such as Insert Options) and an item under Commands on the right (such as Hyperlink Options), drag them into the New menu (Common Documents), and name them as above (such as Payroll) to establish the first workbook document list name.
Repeat the above operation and add more document list names.
3. Select a menu item (such as payroll) in the common document menu, right-click and select the option of Assign Hyperlink → Open in the shortcut menu that pops up to open the Assign Hyperlink dialog box. By pressing the drop-down button to the right of "Look in", find the corresponding workbook folder (such as "Salary". Xls "and so on. ) and select a workbook document.
Repeat the above operation and hyperlink the menu item with its corresponding workbook document.
4. When you need to open a workbook document in the Common Documents menu in the future, just expand the Common Documents menu and click the corresponding option.
Tip: Although we dragged the Hyperlink option to the Common Documents menu, it did not affect the function of the Hyperlink menu item in the Insert menu and the Insert Hyperlink button on the Standard toolbar.
Fourth, make a "professional symbol" toolbar.
When editing professional tables, we often need to input some special professional symbols. In order to facilitate input, we can make our own "professional symbols" toolbar.
1. Execute the command "Tools → Macros → Record New Macros", open the "Record New Macros" dialog box, and enter the macro name? Like "Fu Hao 1"? And save the macro in your personal macro workbook, and then click OK to start recording. Select the "relative references" button on the "Record Macros" toolbar, then enter the required special symbol in the cell, and then click the "Stop" button on the "Record Macros" toolbar to finish macro recording.
Imitate the above operation and record the input "macros" of other special symbols one by one.
2. Open the custom dialog box and click the New button in the toolbar tab to open the New Toolbar dialog box. Enter the name -Professional symbol, and a toolbar will appear in the workspace.
Switch to the Commands tab, select Macros under Category, and then drag the Custom Button item under Commands to the professional symbol bar (drag as many buttons as there are special symbols).
3. Select one of the "custom buttons" and name it after the 1 dot of the second secret skill.
4. Right-click a naming button, select the "Specify Macro" option in the pop-up shortcut menu, open the "Specify Macro" dialog box, select the corresponding macro (such as fuhao 1), and confirm to quit.
Repeat this step to link the button with the corresponding macro.
5. Close the Customize dialog box, and you can use the Professional Symbol toolbar to quickly enter professional symbols in subsequent cells, just like a normal toolbar.
5. Use View Manager to save multiple printed pages.
Some worksheets often need to print different areas, so please use the View Manager.
1. Open the worksheet to be printed, drag the rows (or columns) that don't need to be printed with the mouse, select them and click the right mouse button. In the shortcut menu that appears, select the Hide option to hide the rows (or columns) that don't need to be printed.
2. Execute Command View → View Manager, open the View Manager dialog box, click Add to open the Add View dialog box, enter a name (for example, upload a report), and then click OK.
3. Show the hidden rows (or columns) and repeat the above operations to "add" other print views.
4. When you need to print a table in the future, open the View Manager, select the name of the table to be printed, and click the "Display" button, and the worksheet will be displayed immediately according to the preset interface. Simply set up and typeset, and then press the "print" button on the toolbar, and everything will be fine.
Sixth, let the data sort on demand.
What should I do if I want to sort employees by department, and the information about the names of these departments is not sorted in pinyin order or stroke order? You can sort by using a custom sequence.
1. Execute the "Format → Options" command, open the "Options" dialog box, enter the "Custom Series" tab, and enter the series sorted by department (such as "Agency, Team, Workshop 1, Workshop 2, Workshop 3", etc.). ) and click Add and OK to exit.
2. Select any cell in the "Department" column, execute the "Data → Sort" command, open the "Sort" dialog box, click the "Options" button to pop up the "Sort Options" dialog box, press the drop-down button in it, select the series just defined, and press the "OK" button twice to return. All data will be sorted as required.
Seven, completely hide the data.
The contents of some cells in the worksheet don't want to be seen by visitors, so we must hide them.
1. Select the cell (range) whose contents need to be hidden, execute the Format → Cell command, open the Cell Format dialog box, select the Custom option under Category in the Numbers tab, and then enter; ; ; "(three semicolons in English).
2. Switch to the Protection tab, select the Hide option, and then press the OK button to exit.
3. Execute the "Tools → Protection → Protection Table" command, open the "Protection Table" dialog box, set the password, and click "OK" to return.
After setting, the contents of the above cells are no longer displayed, and even the transparent function of Excel cannot make them visible.
Tip: Under the "Protection" tab, please do not clear the "∨" sign in the check box before "Lock" to prevent others from deleting your hidden data.
Eight, let Chinese and English input methods appear intelligently.
When editing a table, some cells need to enter English and some cells need to enter Chinese. It is really inconvenient to switch input methods repeatedly. Why not set it up and let the input method adjust intelligently?
Select the cell range where Chinese needs to be input, execute the data → validity command, open the data validity dialog box, switch to the input method mode tab, press the drop-down button on the right side of the mode, select the open option, and click OK to exit.
Chinese input method (Chinese input methodNo. 1 in the input method list) will be automatically turned on when you select any cell in the cell range that needs Chinese input in the future, and automatically turned off when you select other cells.
Nine, let AutoCorrect input a unified text.
Do you often worry about entering some fixed text, such as computer newspaper? Then look down.
1. Execute Tools → AutoCorrect command to open the AutoCorrect dialog box.
2. Enter "pcw" in the box under "Replace" (it can also be other characters, and "pcw" is lowercase), enter "Computer Newspaper" in the box under "Replace with", and then click "Add" and "OK" buttons.
3. If you need to enter the above text in the future, just enter the character "pcw"? Can you ignore the situation of "pcw" at this time? , and then confirm it.
X. customizing functions in Excel
Excel is rich in functions, but it can't meet all our needs. We can customize a function to perform some specific operations. Next, we will define a function to calculate the trapezoidal area:
1. Execute the menu command Tools → Macros →Visual Basic Editor (or press the shortcut key Alt+F 1 1) to open the Visual Basic editing window.
2. In the window, execute the "Insert → Module" menu command to insert a new module-Module 1.
3. Enter the following code in the code window on the right:
Function V(a, b, h)V = h *(a+b)/2 end function
4. Close the window and complete the customization function.
In the future, you can use custom functions just like built-in functions.
Tip: Functions customized by the above methods can usually only be used in the corresponding workbooks.
XI。 Insert a picture under the header.
The background added to the worksheet is arranged in a row under the whole worksheet. Can it only be ranked below the header?
1. Execute the command Format → Worksheet → Background to open the Worksheet Background dialog box. After selecting the picture to use as the background, press the Insert button to arrange the pictures under the whole worksheet.
2. While holding down the Ctrl key, drag the cells (regions) that do not need to arrange pictures with the mouse and select them at the same time.
3. Press the drop-down button to the right of Fill Color on the Formatting toolbar, and then select White in the color palette that appears. After this setting, the left cell will arrange pictures, but the selected cell (area) does not arrange pictures (in fact, the pictures are covered with "white").
Hint? Printing pictures under cells is not supported.
Twelve, use the hyphen "&"to merge the text.
If we want to combine the contents of multiple columns into one column, we can do it with a small hyphen "&"without using a function (assuming that columns B, C and D are combined into one column).
1. Insert two empty columns (column E and column F) after column D, and then enter the formula in cell D 1: = B 1&; c 1 & amp； D 1。
2. Select the cell D 1 again, and use "fill handle" to copy the above formula into the cell below column D. The contents of columns B, C and D will be merged into the corresponding cell of column E. ..
3. Select column E, perform the copy operation, then select column F, execute the edit → Paste Special command, open the Paste Special dialog box, select the number option, and then press OK to copy the contents of column E (not the formula) to column F. ..
4. Delete columns B, C, D and E to complete the merge.
Tip: After completing the operations of steps 1 and 2, the merging effect has been achieved. However, if columns B, C and D are deleted at this time, the formula will be wrong. Therefore, the third step must be performed to convert the formula into a constant "value".
Student achievement column
Many friends often ask the question "how to print the report card". Many people use the method of recording macros or VBA to realize it, which is very difficult for beginners. To this end, I give a simple implementation method with functions here.
Here, it is assumed that students' grades are saved in the cell area from A 1 to G64 in the worksheet 1, where1is the behavior title and the name of the second behavior discipline.
1. switch to Sheet2 worksheet, select cell A 1, and enter the formula: = IF(MOD(ROW (), 3)= 0, 〆〆, IF(0 MOD? ROW（），3（= 1，sheet 1！ A, index (sheet 1! $A:$G，INT（（ROW（）+4）+ 1），COLUMN（））。
2. Select the cell A 1 again and copy the above formula into the cells B 1 to G 1 with "fill handle"; Then, select cells A 1 to G 1 at the same time, and use fill handle to copy the above formula into cells A2 to G 185.
At this point, the scoring bar is basically formed, and the following is a simple modification.
3. After adjusting the row height and column width, select cells A 1 to G2( 1 student's score column area) at the same time, press the drop-down button to the right of "Borders" in the formatting toolbar, and select the "All Borders" option in the border list that appears later to add borders to the selected area (if you don't need borders, you don't need to perform this step and the following steps.
4. Select cells A 1 to G3 at the same time, click the "Format Brush" button on the standard toolbar, then hold down the left mouse button and drag from A4 to G 186 to add borders to all score columns.
Press the "Print" button to print the report card.
14.Excel helps you choose a function.
When using functions to process data, we often don't know which function is suitable. Excel's "search function" function can help you narrow down the scope and select the appropriate function.
Execute the "Insert → Function" command, open the "Insert Function" dialog box, enter the requirements (such as "Count") in the box below "Search Function", and then click the "Go" button. The function related to "Count" will be selected immediately and displayed in the list box under "Select Function". Combined with viewing the relevant help files, you can quickly determine the required functions.
15. View data in multiple cells of different worksheets at the same time.
Sometimes, when we edit a worksheet (Sheet 1), we need to check the contents of cells in other worksheets (Sheet2, Sheet 3 ...), which can be achieved by using the "monitoring window" function of Excel.
Execute Command View → Toolbar → Monitoring Window, open the monitoring window, click the "Add Monitoring" button, expand the "Add Monitoring Point" dialog box, select the cell to view with the mouse, and then click the "Add" button. Repeat the above operation and add other "monitoring points".
In the future, no matter which worksheet you are in, you can view the data and related information in the cells of all monitoring points as long as you open the monitoring window.
Sixteen, quickly draw a border for the cell.
Before Excel 2002, it was very troublesome to add a border to a cell area, and Excel 2002 completely extended this function.
Click the drop-down button on the right side of the top border of the formatting toolbar, and select the option of drawing borders from the pop-up drop-down list, or execute Command View → Toolbar → BorderExpand BorderToolbar.
Click the drop-down button on the far left of the toolbar, select the border style, and then drag in the cell area where you need to add a border to quickly draw a border for the corresponding cell area.
Tip: ① If you draw the wrong border, it doesn't matter. Select the Erase Border button on the toolbar, and then drag it to the wrong border to clear it. ② If you need to draw a border with different colors, you can press the "Line Color" button on the right side of the toolbar, select the desired color in the palette that pops up later, and then draw the border. ③ This function can also draw diagonal lines in cells.
Seventeen, control the length of the text input in a specific cell.
Can you imagine that Excel can automatically judge, analyze and pop up a warning when you fill in two digits in the cell where four digits are entered, or when you enter numbers in the cell where words are entered? Excel is not difficult to realize this function.
For example, we put the cursor in the cell where "year" is registered. In order to unify the input and facilitate the calculation, we hope that the "year" will be represented by four digits. So, we can click the "Validity" option in the "Data" menu. Select Text Length from the Allow drop-down menu of Validity Condition in the Settings card. Then select "Equal to" in the data drop-down menu with a length of "4". At the same time, we came to the error warning card again, set the error warning displayed when invalid data was input to stop, and filled in the "Title" and "Error Message" columns with "Illegal Text Input!" And "Please enter a four-digit year." Words.
Obviously, if someone doesn't enter four digits in this cell, Excel will pop up a warning dialog box and tell you the reason for the error. You can't continue to enter until you enter the correct value of Style. Isn't it amazing? In fact, in Excel's judgment of data validity, there are many special data formats to choose from, such as text type, sequence size and time distance. If you are interested, why not make your own decision and design a test standard to make your Excel show its unique brilliance?
18. Fill fixed cells of multiple tables in groups.
We know that every time you open Excel, the software always opens multiple worksheets by default. From this, we can see that Excel is more suitable for coordinating work in multiple interrelated tables besides its powerful processing ability of a single table. Of course, in order to coordinate the association, you need to synchronize the input first. Therefore, in many cases, it is necessary to enter the same content in the same cell of multiple tables at the same time.
So how do you edit tables in groups? First, we click the tab name of the first worksheet "Sheet 1", then hold down the Shift key and click the tab name of the last table "Sheet3" (if the tables we want to associate are not together, we can hold down the Ctrl key and click). At this point, we see that the word "workgroup" appears in the title bar of Excel, and we can edit the workgroup. Write something in a cell that needs to enter multiple tables at once. We found that all the tables in the "Workgroup" displayed the corresponding contents in the same position.
However, only synchronous input is not enough. For example, what should we do if we need to change the format of data in the same location in multiple tables? First, we need to change the data format of the first table, then click the Fill option in the edit menu, and then select Go to the same worksheet in the submenu. At this time, Excel will pop up the dialog box of Fill Group Worksheet, where we select Format and click OK, and the data formats of all tables in the same group will change.
Nineteen, change the case of the text
In Excel, the most powerful support for table processing and data manipulation is not formula or database, but function. Don't think that functions in Excel are only for numbers. In fact, Excel has a special function to edit anything in the table. For example, change the case of text.
In Excel 2002, there are at least three functions about text case conversion. They are: "=UPPER", which converts all texts to uppercase; "=LOWER" to convert all text to lowercase; "= prop", which converts the text into "appropriate" case, such as capitalizing the first letter of each word. For example, we enter lowercase "excel" in cell A 1 of the table, and then enter "= UPPER(a 1)" in the target cell, and the result after entering the carriage return will be "EXCEL". Similarly, if we enter "Mr.Weiwei" in cell A3 and then enter "= PROPER(A3)" in the target cell, then the result we get will be "mr.weiwei".
Twenty, extract specific characters from the string.
Besides direct input, extracting specific characters from existing cell contents is definitely a time-saving and trouble-saving method, especially for some information with the same style, such as employee list, hometown and so on.
If we want to extract the title from A4 cell quickly, we'd better use the function "=RIGHT (the number of characters extracted from the source cell)", which means "extract 2 characters from the rightmost characters in A4 cell" and enter it in this position. Of course, if you want to extract the name, you must use the "=LEFT (source cell, number of characters extracted)" function. In another case, we don't start from the left and right ends, but directly extract a few characters from the data. For example, if we want to extract the word "Wuhan" from A5 cell, we just need to enter "= mid (A5,4,2)" in the target cell. It means: extract two characters after the fourth character in A5 cell, namely the fourth and fifth words.
Twenty-one, the conversion of cardinal words into ordinal numbers and the conversion of English cardinal words into ordinal numbers is a complex problem. Because it doesn't have a very fixed pattern: most numbers use the suffix "th" when they become ordinal numbers, but most numbers ending in "1", "2" and "3" end in "st", "nd" and "rd" respectively. Moreover, the three numbers "1 1", "12" and "13" are different, but they still end with "th". Therefore, it seems complicated to implement. In fact, as long as we clear our minds and find the correct function, we can easily convert it by writing formulas. If you don't believe me, please read: "= A2 &；; If (or (value (right (A2,2)) = {1,12, 13}), the first, if (or (value (right)) = {/kloc-. But its meaning is clear: ① If the number ends with "1 1", 12 "and" 13 ",add the suffix" th "; ② If the principle of 1 is invalid, please check the last number and use "st" at the end of "1", "nd" at the end of "2" and "rd" at the end of "3"; ③ If the principle of 1 and 2 is invalid, use "th". Therefore, the conversion between cardinal words and ordinal numbers is so easy and fast.
Twenty-two, complete the numbers with special symbols.
Everyone who has dealt with finance knows that there is a conventional "safe form filling method" when filling out forms, that is, filling in the blanks in the amount, or adding symbols such as "$" before the amount data. In fact, there is a similar input method in Excel, which is the "REPT" function. Its basic format is "=REPT ("special symbol ",filled with numbers)".
For example, if we want to fill 16 digits with "#" at the end of the digits in cell A2, we only need to change the formula to "= (A2&; REPT（〃#〃， 16-LEN（A2））“； If we want to fill the number in the A3 cell from "#" on the left to 16, we should change it to "= rept(〃#〃,16-len (a3))&; a3“； In addition, if we want to use "#" to fill the values in A4 from both sides, we need to change it to "= rept(〃#〃, 8-len (A4)/2)&; A4 & ampREPT（〃#〃）8-LEN（A4）/2“； If you are not professional enough, if you want to add a "$" symbol at the top of A5 cell number, please change it to: "=(text(A5, 〃 $ #, # # 0.00 〃 (&; Rept(〃#〃, 16-len(text(a5, 〃 $ #, # # 0.00 〃)) will certainly meet your requirements.
Twenty-three, create a text histogram
In addition to repeated input, another derivative application of the "REPT" function is that you can directly create a histogram composed of plain text in the worksheet. Its principle is also very simple, that is, using the intelligent repetition of special symbols, different comparison effects are displayed in the specified cells according to the calculation results.
For example, we first make the annual balance sheet, and then use "E column" as the display area of "within budget" months in the histogram and "G column" as the display area of "over budget" months in the histogram. Then, according to the numerical value of the existing result "D column" in the table, it is represented by the "N" character in the font "Wingdings". The specific steps are as follows:
Write the formula "= if(d30, rept(〃n〃, round (d3 *100,0)), 〃) in the E3 cell, and drag the fill handle to G 14. We can see that a plain text histogram without Excel chart function has been displayed, which is convenient, intuitive and simple.
Twenty-four, calculate the total number of words in the cell.
Sometimes, we may be interested in the number of characters in a cell and need to calculate the total number of words in the cell. To solve this problem, in addition to using the "replacement" function of virtual computing, we should also use the "pruning" function to delete spaces. For example, "How many words?" Now enter it in cell A 1 Words, then we can use the following expressions to help:
“= IF（LEN（a 1）= 0，0，LEN（TRIM（a 1））-LEN（SUBSTITUTE（TRIM（a 1），?，?，?）+ 1）
The meaning of this formula is to create a new string with the function of "SUBSTITUTE" and delete the spaces between characters with the function of "TRIM", then calculate the digit difference between this string and the original string to get the number of "spaces", and finally add+1 to the number of spaces to get the number of characters in the cell.
Twenty-five, about the conversion of the euro
This is a new tool in Excel 2002. If you choose the default method when installing Excel 2002, you may not find it in the Tools menu. However, we can first select the add-in in the Tools menu, and then select the Euro Tool option in the pop-up window. After confirmation, Excel 2002 will be installed by itself.
When finished, we open the tools menu again and click Euro Conversion, and an independent window will appear, which is dedicated to the conversion between the euro and the currencies of EU member States. Like other function windows in Excel, we can use the mouse to set the "source area" and "target area" for currency conversion, and then select different currencies before and after conversion. Shows the price comparison list of "100 Euro" converted into other currencies of EU member states. Of course, in order to make the display of the euro more professional, we can also click the "Euro" button on the Excel toolbar to make all the converted currency values adopt the Euro style.
Where is the location?