Excel function usage

This topic starts with some little-known skills in Excel2002 to appreciate the unique style of Excel.

First, 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 names consistent, we use the "data validity" function to build a category drop-down list to fill in the entries.

1. In Sheet2, fill the enterprise name in different columns according to categories (such as "industrial enterprise", "commercial enterprise" and "individual enterprise"). ) Establish enterprise name database. 2. Select column A (where the name of "industrial enterprise" is located), enter the character of "industrial enterprise" in the "Name" column, and press Enter to confirm. Imitate the above operation 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 "Order" option, and in the "Source" box below, enter the order of "Industrial Enterprise", "Commercial Enterprise" and "Individual Enterprise" (the elements are separated by English commas), and confirm to quit. Select the column (such as column D) where the enterprise name needs to be entered, and then open the data validity 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 (such as D4), click the drop-down button, select the required enterprise name from the list of enterprise names in the corresponding category, and fill it in this cell. Tip: If you don't need to print the column of "Enterprise Category" when printing the report in the future, you can select the column, right-click and select the "Hide" option to hide the column.

Second, create a new "General Document" menu.

Create a new "Frequently Used Documents" menu on the menu bar, and add frequently used workbook documents to it for easy calling 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 Documents") in the "Naming" box of the pop-up menu.

2. Select an item under the category (such as insert option) and an item under the command on the right (such as hyperlink option), drag it to the new menu (common document), and name it 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) from the common document menu, right-click and select the option of Assign Hyperlink → Open in the pop-up shortcut menu 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 to hyperlink the menu item with its corresponding workbook document.

4. When you need to open the workbook document in the common document menu in the future, just expand the common document 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.

Third, let different types of data be displayed in different colors

In the payroll, if the total wages of more than 2000 yuan are displayed in red, the total wages of more than 1500 yuan are displayed in blue, the total wages of less than 1000 yuan are displayed in brown, and the rest are displayed in black, we can set it like this.

1. Open the payroll workbook, select the payroll column, and execute the format → conditional format command to open the conditional format dialog box. Click the drop-down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the following box. Click the Format button to open the cell format dialog box and set the font color to red.

2. Press the "Add" button, and other conditions are set as above (1500 or above, 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. Is the payroll data displayed in different colors according to your requirements?

Fourth, making "professional symbols" 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? Such as "fuhao 1" and save the macro in the personal macro workbook, and then "OK" to start recording. Select the "relative references" button on the "Record Macro" toolbar, then enter the required special symbol in the cell, and then click the "Stop" button on the "Record Macro" toolbar to complete macro recording. Imitate the above operation and record the input "macros" of other special symbols one by one.

2. Open the custom dialog box, click the New button in the toolbar tab, and the new toolbar dialog box will pop up. 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 any number of special symbol buttons).

3. Select one of the "custom buttons" and name it after the 1 point 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 cells like a normal toolbar in the future.

5. Use View Manager to save multiple printed pages.

Some worksheets often need to print different areas, so use the View Manager.

1. Open the worksheet that needs 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 (such as uploading a report), and 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 form in the future, open the View Manager, select the name of the form to be printed, and click the "Display" button, and the worksheet will be displayed immediately according to the preset interface. Simply set the typesetting, press the "print" button on the toolbar, and everything will be OK.

Sixth, let the data sort on demand.

What if you 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.). ) In the box under Input Series, 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) that needs to be hidden, execute the command format → cell, open the cell format dialog box, select the custom option under category in the number tab, and then enter "; ; ;" (English three semicolons).

2. Switch to the Protection tab, select the Hide option, and then press the OK button to exit.

3. Execute the "Tools → Protection → Protection Sheet" command, open the "Protection Sheet" dialog box, set the password, and click "OK" to return. After this 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 avoid others 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 range of cells that need to input Chinese, execute the data → validity command, open the data validity dialog box, switch to the tab of input method mode, press the drop-down button on the right side of the mode, select the open option, and click OK to exit. The Chinese input method (Chinese input methodNo. 1 in the input method list) will automatically open when any cell in the cell range that needs Chinese input is selected in the future, and will automatically close when other cells are selected.

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 to open the AutoCorrect dialog box.

2. Enter "pcw" in the box below "Replace" (it can also be other characters, and "pcw" is lowercase), enter "Computer Newspaper" in the box below "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"? At this time, you can ignore the situation of "pcw" and just 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 to complete the customization function.

In the future, you can use custom functions just like built-in functions. Tip: The functions customized by the above methods can usually only be used in the corresponding workbook.

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 picture under the whole worksheet. 2. While holding down the Ctrl key, drag the cells (regions) that don't need to be lined with 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 cell on the left has the line of the picture, but the selected cell (area) has no line of the picture (in fact, the picture is 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: = b1&; C 1。 D 1 .

2. Select cell D 1 again, and use "fill handle" to copy the above formula to the cell under column D, and the contents of columns B, C and D will be merged into the corresponding cell of column E. ..

3. Select column E, perform copy operation, then select column F, execute Edit → Paste Special command, open the Paste Special dialog box, select numerical option, and press OK to copy the contents of column E (not formula) to column F. ..

4. Delete columns B, C, D and E to complete the merger.

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, there will be errors in the formula. Therefore, the third step must be taken to convert the formula into a constant "value".

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 "$" in front of 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 bits with "#" at the end of the number in cell A2, we only need to change the formula to "= (A2&; Rept ("# "， 16-len(A2))"； If we want to use "#" in cell A3 from the left to 16, we should change it to "= rept ("#",16-len (a3))&; a3”； In addition, if you want to use "#" to fill the value in A4 from both sides, you need to change it to "= rept ("#", 8-len (A4)/2)&; A4 & ampREPT(″# ″) 8-LEN(A4)/2)"； If you are not professional enough and want to add "$" symbol at the top of A5 cell number, change it to "= (text (A5," $ #, # # 0.00 "(&; Rept ("# ",16-len (text (A5," $ #, # # 0.00 ")) will definitely 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 create a histogram composed of plain text directly 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 make an annual balance sheet first, 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 (d3

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 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 Then we can use the following expression to help: "= if (len (a1) = 0,0, len (trim (a1))-len (substitute (trim (a1),", ")+. And use the "TRIM" function to delete the spaces between characters, and then calculate the digit difference between this string and the original string to get the number of "spaces". 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 automatically.

When finished, we open the Tools menu again and click Euro Conversion. A separate window dedicated to the conversion between the euro and the currencies of EU member countries will appear. 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 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, so that all the converted currency values are in the Euro style.

26. Make a table super search engine.

As we know, the biggest difference between Excel and Word forms is that Excel brings all the contents (including static text) filled in the form into the database. We can use "function query" to locate the target data accurately, just like a search engine in a web page. For example, in the table shown, scores of various subjects of several students are entered into cells from A 1 to F7. In cells A8 to A 13, we have established a "function query" area. Our assumption is that when we enter any student's name in the cell to the right of "Enter Student's Name", that is, in cell C8, the student's subject score will be automatically displayed in the following cell. The specific implementation method is as follows: position the cursor in cell C9, and then click the inserted function option. In the pop-up window as shown in figure 18, select the "VLOOKUP" function and click "OK". In the pop-up function parameter window, we set "Lookup_value" (the value to be searched in the first column of the data table) to "C8" (that is, search for the name we filled in the C8 cell); "Table_array" (referring to the range of data search) is "A2∶B6" (that is, searching among all students' Chinese scores); "Col_vindex_num" (referring to the serial number of the value to be searched in the table) is "2" (that is, the value is in the second column); "Range_lookup" (indicating whether an exact match is required) is "FALSE" (indicating that it is not required. If so, it is "true"). Press "OK" after setting. Back to the table at this time, click cell C9, and we see that the command behavior "=VLOOKUP(C8, a2: B6,2, FALSE)" is displayed in the "fx" area. Copy the command line and enter "=VLOOKUP(C8, a2: C6, 3, false)" in cells C 10, C 1 2 and C 13 respectively; " =VLOOKUP(C8，A2∶D6，4，FALSE)"； " =VLOOKUP(C8，A2∶E6，5，FALSE)"； "=VLOOKUP(C8, A2∶F6, 6, FALSE)" (its parameter meaning is the same as that in C9, so it is not repeated here). Next, let's test the effectiveness of the "VLOOKUP" function. Try to enter the student's name in the "C8" cell, such as "Zhao Yao". After entering the car, we will find that the students' entrance scores will be automatically displayed in the cells of the following subjects.

Twenty-seven, Excel worksheet outline preparation

Like the outline view of Word, the function of Excel is mainly used to deal with the problem that it is difficult to display key items on the same screen when the worksheet is particularly large. If there are multiple names in a table, but the data types are comparable, then we can first select the data area with the mouse, and then click the "Classification and Summary" option in the "Data" menu. And select the data category to summarize in the "Selected Summary Item" area of the pop-up menu. Finally, as shown in figure 19, isn't the table much smaller now? Of course, if you want to see more details, just click the "+"button on the left side of the form.

Twenty-eight, insert "icon"

Although there are more than 50 "chart" styles in 14 category that support Excel, the conventional chart representation method is still boring and unimaginative for complex data relations. Therefore, the function of "icon" has been added in the latest version of Excel 2002. Although the chart window of the Insert menu has only six styles, it plays an important role in explaining the structure between data. For example, to display the hierarchical relationship of data, you can select "Organization Chart"; To express the circulation process of funds, you can choose "circulation chart"; Of course, "venn diagram" can be selected to illustrate the overlapping nature of various data. You see, as shown in Figure 20, venn diagram is so beautiful. You can also right-click the icon to display the icon toolbar. Set up a "graphic style library" at will, or even add a few more rings.

Twenty-nine, familiar with Excel's "From Text to Pronunciation"

This is a striking new function in Excel 2002. Is to make office software not only write and calculate, but also read and speak. Although many softwares have been devoted to machine reading of texts, Microsoft Office is incomparable to other similar softwares in terms of the softness of voice and the cadence of intonation. According to the default installation of Office, you may not find it in the menu of Excel. After we confirm that the "Text to Speech" option is installed, you can see the "Speech" item in Excel's tools menu. As shown in Figure 2 1, open a table, select its contents at will, and then click the "Read Cell" button on the "Text to Speech" toolbar. At this time, a magnetic voice began to read word by word. It is worth mentioning that when you encounter punctuation, Excel will automatically pause for a while and then re-read, which is completely different from other software. I think this is more natural. It should also be noted that if you want to adjust the speed of voice reading in Excel 2002, you can double-click the "Voice" icon in the control panel, and then adjust it on the "Text to Speech" card.

Thirty, the wonderful use of "photography" function in Excel

This function is very different. It seems to be incompatible with the "orthodox" functions such as calculation, statistics and sorting, because its function is actually-"grabbing the screen"! Moreover, it is not a "scratch" like the "PrintScreen" button, but allows you to choose through the mouse and "catch your point". It is not easy to find this function, so we click the "Customize" option in the Excel tools menu. Click Tools in the command card category, find the Photography button in the command bar, and then drag it anywhere on the toolbar. If we want to "photograph" a certain part of the table, we only need to select them first (Figure 23), and then press the "photograph" button, and the selected area will be "photographed". Then move the mouse to the place where "photo" needs to be displayed (of course, it can be another form), and press the "photo" button again, and the "photo" just taken will be pasted immediately. Of course, with "Photos" comes the "Pictures" toolbar. Obviously, Excel regards it as a picture, and we can rotate and zoom at will. However, please be careful: this is no ordinary photo! You can try to change the table data that we "shot"-refresh the "photo", and the data in the "photo" is actually updated synchronously!

Click Options in the Tools menu of Excel 2002, select the "Zoom with Smart Mouse" check box in the General tab, click OK, and then let's see what functions the mouse wheel has now:

In the default "smart mouse" state, by rolling the mouse wheel up and down, the table in the workspace will be enlarged or reduced by 15%, and the worksheet will turn up and down as usual only if we hold down the Ctrl key and then roll the mouse wheel. In addition, if we use the "hierarchical display" function of Excel, we can control the display or hiding of items at all levels when pressing Shift to scroll the mouse wheel. Of course, there are more special functions that you need to explore slowly in practice.

Thirty-five, Excel 2002 "monitoring" window application

If you create a large spreadsheet with data linked to other workbooks, the Monitor Window in Excel can help you a lot. Through it, you can easily see how worksheets, cells and formula functions affect the current data when they change. On the Tools menu, click the Formula Approval submenu, and then click the Show Monitoring Window button. Right-click the cell we want to track and select Add Observer from the shortcut menu. At this point, the cells monitored by Excel and their formulas appear in the Monitoring Window list. In the future, as long as we double-click the item in the monitoring window, the monitored cell will come uninvited.

Tip: When cells pointing to other workbooks are monitored, they can only be displayed in the list in the Monitoring Window when all the referenced workbooks are open.