Jingzhixue123

In the "Greater than or equal to" option, enter the value "2000" in the subsequent box. Click the "Format" button to open the "Format Cells" dialog box and set the "Color" of "Font" to "Red".

2. Press the "Add" button and follow the above operations to set other conditions (greater than or equal to 1500, set the font to "blue"; less than 1000, set the font to "brown").

3. After the settings are completed, press the "OK" button.

Take a look at the salary table. Is the total salary data displayed in different colors according to your requirements?

2. Create category drop-down list fill items

We often need to enter the name of the company into the form. In order to maintain the consistency of the name, we use the "data validity" function to create a A category drop-down list filler item.

1. In Sheet2, enter the company name into different columns according to categories (such as "industrial enterprise", "commercial enterprise", "individual enterprise", etc.) to establish a company name database.

2. Select column A (the column where the name of "Industrial Enterprise" is located), enter the characters of "Industrial Enterprise" in the "Name" column, and press the "Enter" key to confirm.

Follow the above operation and name columns B, C... as "Commercial Enterprise", "Individual Enterprise"...

3. Switch to Sheet1 and select the fields that need to be entered. For the "Enterprise Category" column (such as column C), 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 in the "Source" box below, enter "Industrial Enterprise", "Commercial Enterprise", "Individual Enterprise"... ...sequence (separate each element with commas), confirm exit.

Then select the column where you need to enter the company name (such as column D), then open the "Data Validity" dialog box, select the "Sequence" option, and enter the formula in the "Source" box: =INDIRECT (C1), confirm 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 in column D (such as D4) corresponding to the cell, click the drop-down button, and then select the required company name from the list of company names in the corresponding category to fill in the cell.

Tip: When printing reports in the future, if you do not need to print the "Enterprise Category" column, you can select the column, right-click the mouse, and select the "Hide" option to hide the column.

3. Create a new "Common Documents" menu

Create a new "Common Documents" menu on the menu bar and add commonly used workbook documents to it for easy calling at any time.

1. Right-click the mouse 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 "Categories", and then drag "New Menu" under "Commands" to the menu bar.

Press the "Change Selection" button and enter a name (such as "Common Documents") in the "Name" box of the pop-up menu.

2. Select an item under "Category" (such as the "Insert" option), select an item under "Command" on the right (such as the "Hyperlink" option), and drag it to In the new menu (Common Documents), name it according to the above operation (such as "Salary Table", etc.), and create the first workbook document list name.

Repeat the above operation and add several more document list names.

3. Select a menu item (such as "Salary Table", etc.) in the "Common Documents" menu, right-click the mouse, and select the "Assign Hyperlink → Open" option in the pop-up shortcut menu. Opens the Assign Hyperlink dialog box. By pressing the drop-down button on the right side of "Search range", locate the corresponding workbook (such as "Salary.xls", etc.) folder and select the workbook document.

Repeat the above operation to hyperlink the menu item and 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 does not affect the "Hyperlink" menu item in the "Insert" menu and the "Hyperlink" menu item on the "Common" toolbar. Insert hyperlink button.

4. Create a "Professional Symbols" Toolbar

When editing professional forms, you often need to enter some special professional symbols. In order to facilitate input, we can make our own " Professional Symbols” toolbar.

1. Execute the "Tools → Macro → Record New Macro" command to open the "Record New Macro" dialog box, enter the macro name? Such as "fuhao1"? and save the macro in the "Personal Macro Workbook" , then "OK" to start recording.

Select the "Relative Reference" button on the "Record Macro" toolbar, then enter the required special symbols into a cell, and then click the "Stop" button on the "Record Macro" toolbar to complete the recording of the macro.

Imitate the above operation and record the input "macro" of other special symbols one by one.

2. Open the "Customize" dialog box, in the "Toolbar" tab, click the "New" button, the "New Toolbar" dialog box will pop up, enter the name - "Professional Symbol", After confirmation, a toolbar will appear in the workspace.

Switch to the "Command" tab, select "Macro" under "Category", and drag the "Custom Button" item under "Command" to the "Professional Symbols" bar (how many special symbols are there? Drag as many buttons as the symbol).

3. Select one of the "custom buttons" and name them according to point 1 of the second tip.

4. Right-click a named button, select the "Specify Macro" option in the shortcut menu that pops up, open the "Specify Macro" dialog box, and select the corresponding macro (such as fuhao1, etc.) , confirm to exit.

Repeat this step to link the button with the corresponding macro.

5. Close the "Customize" dialog box. In the future, you can use the "Professional Symbols" toolbar just like using the ordinary toolbar to quickly enter professional symbols into cells.

5. Use "View Manager" to save multiple print pages

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

1. Open the worksheet that needs to be printed, use the mouse to drag the rows (or columns) that do not need to be printed, select them and then right-click the mouse. In the shortcut menu that appears, select "Hide" ” option to hide rows (or columns) that do not need to be printed.

2. Execute the "View → View Manager" command to open the "View Manager" dialog box, click the "Add" button, the "Add View" dialog box will pop up, enter a name ( (such as "Prepare Report"), click the "OK" button.

3. Display the hidden rows (or columns) and repeat the above operation to "add" other printing views.

4. When you need to print a certain form in the future, open the "View Manager", select the name of the form that needs to be printed, click the "Show" button, and the worksheet will immediately be displayed according to the preset interface. Come out, do some simple settings and layout, press the "Print" button on the toolbar, and everything will be OK.

6. Sort the data as needed

If you want to sort employees by their departments, the information about these department names is neither in pinyin order nor in strokes Sequence, what should I do? You can use a custom sequence to sort.

1. Execute the "Format → Options" command, open the "Options" dialog box, enter the "Custom Sequence" tab, and enter the department sorting sequence in the box below "Input Sequence" (such as "Organization, fleet, first workshop, second workshop, third workshop", etc.), click the "Add" and "OK" buttons to exit.

2. Select any cell in the "Department" column, execute the "Data → Sort" command, open the "Sort" dialog box, click the "Options" button, and the "Sort Options" dialog box will pop up. Press the drop-down button, select the sequence you just customized, press the "OK" button twice to return, and all data will be sorted as required.

7. Completely hide the data

The content in some cells of the worksheet does not want to be viewed by viewers, so it has to be hidden.

1. Select the cell (area) whose content needs to be hidden, execute the "Format→Cell" command, open the "Cell Format" dialog box, and select "Category" under "Number" tab Customize" option, and then enter ";;;" (three semicolons in English) in the box under "Type" on the right.

2. Then switch to the "Protection" tab, select the "Hide" option, and press the "OK" button to exit.

3. Execute the "Tools → Protection → Protect Worksheet" command to open the "Protect Worksheet" dialog box. After setting the password, click "OK" to return.

After such settings, the content in the above cells will no longer be displayed. Even using the transparency function of Excel cannot make it visible.

Tip: Under the "Protect" tab, please do not clear the "∨" sign in the check box in front of "Lock". This will prevent others from deleting your hidden data.

8. Let the Chinese and English input methods appear intelligently

When editing the table, you need to enter English in some cells and Chinese in some cells. Repeat It is really inconvenient to switch input methods, why not set it up so that the input method can be adjusted intelligently?

Select the cell area where Chinese input is required, execute the "Data → Validity" command, and turn on "Data Validation" " dialog box, switch to the "Input Method Mode" tab, press the drop-down button on the right side of "Mode", select the "Open" option, and click "OK" to exit.

In the future, when any cell in the cell range where Chinese input is required is selected, the Chinese input method (the first Chinese input method in the input method list) will automatically open. When other cells are selected, Chinese input method is automatically closed.

9. Let "AutoCorrect" input unified text

Do you often worry about entering certain fixed text, such as "Computer News"? Then go down Take a look.

1. Execute the "Tools → AutoCorrect" command to open the "AutoCorrect" dialog box.

2. Enter "pcw" in the box below "Replace" (it can also be other characters, "pcw" is in lowercase), enter "

3. If you need to enter the above text in the future, just enter the "pcw" character? You can ignore the case of "pcw" at this time, and then confirm it.

10. Customize functions in Excel

Although Excel has rich functions, it cannot meet all our needs. We can customize a function to complete some specific operations. Next, we will define a function to calculate the area of ??a trapezoid:

1. Execute the "Tools → Macro → Visual Basic Editor" menu command (or press the "Alt+F11" shortcut key) to open Visual Basic Edit 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)/2End Function < /p>

4. Close the window and the custom function is completed.

You can later use custom functions just like built-in functions.

Tip: Functions customized with the above method can usually only be used in the corresponding workbook.

11. Place a picture under the table header

The background added to the worksheet is placed under the entire worksheet. Can it only be placed under the table header? < /p>

1. Execute the "Format → Worksheet → Background" command to open the "Worksheet Background" dialog box. After selecting the picture that needs to be used as the background, press the "Insert" button to line the picture with the entire worksheet. under.

2. While holding down the Ctrl key, use the mouse to drag in the cells (regions) that do not need to be lined with pictures, and select these cells (regions) at the same time.

3. Press the drop-down button to the right of "Fill Color" on the "Format" toolbar, and in the "Palette" that appears subsequently, select "White". After such settings, the remaining cells are lined with pictures, but the above-selected cells (regions) are not lined with pictures (in fact, the pictures are covered by "white").

Tips? Pictures lined under cells do not support printing.

12. Use the hyphen "&" to merge text

If we want to merge the contents of multiple columns into one column, there is no need to use a function, a small hyphen "&" can do it (here it is assumed that columns B, C, and D are merged into one column).

1. Insert two empty columns (columns E and F) after column D, and then enter the formula in cell D1: =B1&C1&D1.

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

3. Select column E, perform the "Copy" operation, then select column F, execute the "Edit → Paste Special" command, open the "Paste Special" dialog box, and select the "Value" option , press the "OK" button, and the contents of column E (not the formula) will be copied to column F.

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

Tip: After completing steps 1 and 2, the merge effect has been achieved, but if you delete columns B, C, and D at this time, the formula will be wrong. Therefore, step 3 must be performed to convert the formula into an unchanged "value".

Printing a score strip

Friends often ask questions such as "How to print a score strip". Many people use recording macros or VBA to achieve this, which is very useful for beginners. It is said to be somewhat difficult. For this reason, I give here a simple way to implement it using functions.

It is assumed here that student grades are stored in cells A1 to G64 of Sheet1, with the first row being the title and the second row being the subject name.

1. Switch to the Sheet2 worksheet, select cell A1, and enter the formula: =IF(MOD(ROW(),3)=0,〃〃,IF(0MOD?ROW(),3 (=1,sheet1!Aū,INDEX(sheet1!$A:$G,INT(((ROW()+4)/3)+1),COLUMN()))).

2 .Select cell A1 again and use the "fill handle" to copy the above formula to cells B1 to G1; then, select the cell range A1 to G1 at the same time and use the "fill handle" to copy the above formula to cells A2 to G185.

At this point, the score bar is basically formed. Let’s simply modify it.

3. After adjusting the row height and column width, select the cell range A1 to G2 at the same time. 1 student's grade strip area), press the drop-down button on the right side of "Border" on the "Format" toolbar, and in the subsequent border list, select the "All Borders" option to add a border to the selected area (if not needed Border, you do not need to perform this step and the following operations)

4. Select the cell range A1 to G3 at the same time, click the "Format Paint" button on the "Common" toolbar, and then hold down the left mouse button. Click the button and drag from A4 to the G186 cell area to add borders to all the score bars.

Press the "Print" button to print out the score bars.

14. Excel helps you choose functions

When using functions to process data, you often don’t know which function to use. 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 system will Select the functions related to "Counting" and display them in the list box under "Select Function". Then you can quickly determine the required function by viewing the relevant help files.

, View data in multiple cells in different worksheets at the same time

Sometimes, when we edit a worksheet (Sheet1), we need to view a cell in other worksheets (Sheet2, Sheet3...) You can use the "Monitor Window" function of Excel to realize the content of the grid.

Execute the "View → Toolbar → Monitor Window" command to open the "Monitor Window" and click the "Add Monitor" button. , expand the "Add Monitoring Point" dialog box, use the mouse to select the cell you want to view, and then click the "Add" button to repeat the above operation to add other "Monitoring Points".

In the future, no matter where. In any worksheet, as long as you open the "monitoring window", you can view the data and related information in the cells of all monitored points.

16. Quickly draw borders for cells

In versions prior to Excel 2002, the operation of adding borders to cell areas was troublesome. Excel 2002 has made a new addition to this function. expansion.

Click the drop-down button to the right of "Border" on the "Format" toolbar, and in the drop-down list that pops up, select the "Drawing Border" option, or execute the "View → Toolbar → Border" command , expand the Border toolbar.

Click the drop-down button on the far left of the toolbar, select a border style, and then drag in the cell area where you want to add a border to quickly draw a border for the corresponding cell area.

Tips: ① If you draw the wrong border, it doesn't matter. Select the "Erase Border" button on the toolbar, and then drag the wrong border to clear it. ② If you need to draw borders of different colors, you can first press the "Line Color" button on the right side of the toolbar, select the desired color in the pop-up palette, and then draw the border. ③This function can also draw diagonal slashes in cells.

17. Control the length of input text in a specific cell

Can you imagine that when you enter a four-digit cell but fill in a two-digit number, or How great would it be that when you enter numbers in the cell where you should enter text, Excel can automatically judge, analyze immediately and pop up a warning? It is not difficult for Excel to implement this function.

For example, we position the cursor in a cell that registers "year". For the unification of input and the convenience of calculation, we hope that "year" is represented by a four-digit number. So, we can click on the Validity option of the Data menu. Select "Text Length" from the "Allow" drop-down menu in the "Validity Conditions" of the "Settings" card. Then select "Equals" in the "Data" drop-down menu, and "Length" is "4". At the same time, we come to the "Error Warning" card again, set the "Error Warning Displayed when Entering Invalid Data" to "Stop", and fill in the "Title" and "Error Message" columns with "Illegal input text!" and the words "Please enter a four-digit year."

Obviously, when someone enters something other than a four-digit number in the cell, Excel will pop up a warning dialog box to tell you the cause of the error and wait until you enter the correct "style" ” value before you can continue to enter. Amazing, right? In fact, in Excel's "data validity" judgment, there are many special types of data formats to choose from, such as "text type", "series size", "time distance", if you are interested , why not take it upon yourself to design a testing standard to make your Excel shine differently.

18. Fill fixed cells in 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 in addition to its powerful processing capabilities for a single table, Excel is more suitable for coordinating work in multiple interrelated tables. To coordinate the association, of course, you first need to synchronize the input. Therefore, in many cases, you will need to enter the same content in the same cells of multiple tables at the same time.

So how to edit tables in groups? First, we click the label name "Sheet1" of the first worksheet, then hold down the Shift key and click the label name "Sheet3" of the last worksheet. "(If the tables we want to associate are not together, we can hold down the Ctrl key and click). At this time, we see the word "Workgroup" appearing on the title bar of Excel, and we can edit the workgroup. Just write something in the cells where you need to enter the contents of multiple tables at one time. We found that the corresponding content is displayed in the same position of all tables in the "Working Group".

However, synchronous input is not enough. For example, what should we do if we need to uniformly change the format of data in the same position in multiple tables? First, we have to change the data format of the first table, then click the "Fill" option in the "Edit" menu, and then click Select "To the same group of worksheets" from the submenu. At this time, Excel will pop up the "Fill Grouped Worksheets" dialog box. Here we select the "Format" item and click "OK". The data format at that position in all tables in the same group will be changed.

19. Change the capitalization of text

In Excel, the most powerful support for table processing and data operations is not formulas or databases, but functions. Don't think that the functions in Excel are only for numbers. In fact, as long as the content is written into the table, Excel has special functions for editing it. For example, changing the case of text.

In Excel 2002, at least three functions related to text case conversion are provided. They are: "=UPPER (source data format)", convert all text to uppercase; "=LOWER (source data format)", convert all text to lowercase; "=PROPER (source data format)", convert all text to lowercase Convert to "proper" case, such as capitalizing the first letter of each word, etc. For example, if we enter lowercase "excel" in cell A1 of a table, and then enter "=UPPER(A1)" in the target cell, the result after pressing Enter will be "EXCEL". Similarly, if we enter "mr.weiwei" in cell A3, and then we enter "=PROPER(A3)" in the target cell, the result we get will be "Mr.Weiwei".

20. Extract specific characters from the string

In addition to direct input, extracting specific characters from the existing cell content is definitely a time-saving and trouble-free method. method, especially for information with similar styles, such as employee list, place of origin and other information.

If we want to quickly extract the title from cell A4, it is best to use the "=RIGHT (source data cell, number of characters extracted)" function, which means "from the rightmost character of cell A4 Characters start to extract 2 characters" input to this position. Of course, if you want to extract the name, you have to use the "=LEFT (source data cell, number of characters extracted)" function. In another case, we do not start from the left and right ends, but directly extract a few characters from the middle of the data. For example, if we want to extract the word "Wuhan" from cell A5, we only need to enter "=MID(A5,4,2)" in the target cell. Meaning: Extract the two characters after the 4th character in cell A5, that is, the 4th and 5th characters.

21. Convert cardinal numerals into ordinal numerals Converting English radix numerals into ordinal numerals is a relatively complex problem. Because it does not have a very fixed pattern: most numbers use the "th" suffix when they are turned into ordinal numbers, but generally numbers ending in "1", "2", and "3" have the "th" suffix respectively. ending in "st", "nd" and "rd". Moreover, the three numbers "11", "12" and "13" are different, but they still end with "th". Therefore, it seems complicated to implement. In fact, as long as we clarify our ideas and find the correct function, we only need to write a formula and it can be easily converted.

If you don’t believe it, please read: “=A2&IF(OR(VALUE(RIGHT(A2,2))={11,12,13}),〃th〃,IF(OR(VALUE(RIGHT(A2)))={1,2 ,3,},CHOOSE(RIGHT(A2),〃st〃,〃nd〃,〃rd〃),〃th〃))”. Although this formula is a long list, its meaning is very clear: ① If the number is If the number ends with "11", "12", or "13", add the "th" suffix; ② If the first principle is invalid, check the last number, use "st" if it ends with "1", and use "st" if it ends with "2" Use "nd" and "rd" at the end of "3"; ③ If the first and second principles are invalid, then use "th". Therefore, the conversion of cardinal words and ordinal words is so easy and fast.

22. Use special symbols to complete the digits

Anyone who has dealt with finance knows that there is a conventional "safe filling method" when filling in accounts, that is, fill in the digits with special symbols. Fill in the gaps in the amount, or add a symbol such as "$" in front of the payment data. In fact, there is a similar input method in Excel, that is, the "REPT" function. Its basic format is "=REPT(. "Special symbol", number of filling digits)".

For example, if we want to use the "#" sign at the end of the number in cell A2 to fill it to 16 digits, we only need to change the formula to " =(A2&REPT(〃#〃,16-LEN(A2)))"; if we want to fill the number in cell A3 with "#" from the left to 16 digits, we need to change it to "=REPT (〃#〃,16-LEN(A3)))&A3"; In addition, if we want to use the "#" sign to fill the value in A4 from both sides, we need to change it to "=REPT(〃#〃,8- LEN(A4)/2)&A4&REPT(〃#〃)8-LEN(A4)/2)”; If you are still not professional enough and want to add the “$” sign at the top of the number in cell A5, then change it For: "=(TEXT(A5,〃$#,##0.00〃(&REPT(〃#〃,16-LEN(TEXT(A5,〃$#,##0.00〃))))", it will definitely satisfy you

23. Create text histogram

In addition to repeated input, another derivative application of the "REPT" function is to create a pure text histogram directly in the worksheet. Histogram composed of text. Its principle is also very simple, which is to use the intelligent repetition of special symbols to show comparison effects of different lengths according to the calculation results in the specified cells.

For example, we first create a histogram. Create an annual balance sheet, and then use "Column E" as the display area for "within budget" months in the histogram, and use "Column G" as the display area for "over budget" months in the histogram, and then use the data already in the table. The value of the result "column D" is expressed with the "N" character in the "Wingdings" font. The specific steps are as follows:

Write the formula "=IF(D30,REPT(〃n〃,ROUND(D3*100,0)),〃〃)" in cell E3, and also drag to fill Shank to G14. We see that a plain text histogram without using the Excel chart function has been displayed in front of us, which is convenient, intuitive, simple and clear.

24. Calculate the total number of words in a cell

Sometimes, we may be interested in the number of characters in a certain cell and need to calculate the total number of words in the cell. . To solve this problem, in addition to using the virtual calculation of the "SUBSTITUTE" function, the "TRIM" function must also be used to delete spaces. For example, if the words "how many words?" are entered in cell A1, we can use the following expression to help:

"=IF(LEN(A1)=0,0,LEN( TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),〃,〃,〃〃))+1)”

The meaning of this formula is to first use the “SUBSTITUTE” function to create a new string , and use the "TRIM" function to delete the spaces between the characters, and then calculate the digit difference between this string and the original string to get the number of "spaces", and finally add the number of spaces + 1 to get the characters in the cell quantity.

25. Conversion of Euros

This is a new tool in Excel 2002. If you chose the default method when installing Excel 2002, you probably won't be able to find it in the "Tools" menu. However, we can first select "Add-ins" in the "Tools" menu, and then check the "Euro Tools" option in the pop-up window. After "OK", Excel 2002 will install itself.

After completion, we open the "Tools" menu again, click "Euro Conversion", and an independent window dedicated to currency conversion of the euro and EU member states appears.

Like other function windows in Excel, we can set the "source area" and "target area" of currency conversion through the mouse, and then select different currencies before and after conversion. Shown is a list of price comparisons for converting "100 euros" into other currencies of EU member states. Of course, in order to make the display of euros more professional, we can also click the "Euro" button on the Excel toolbar, so that all converted currency values ??are in euro style.