Perhaps you have done in Excel financial statements had hundreds, maybe you have to use Excel functions to achieve complex operations over a thousand times, maybe you think that Excel is better than this, even nothing new. However, we repeated countless times daily in handy to use only one per cent of Excel all skills. From Excel, the topic of some little-known tips to start a trip on the Excel Different customs.
one, so that different types of data using different colors
in payroll, if you want more than equal to the total wages of 2,000 yuan to greater than or equal to the total wages of 1,500 yuan,
1. Open the Click the second box on the right of the drop-down button, select the Click
2. press the
3. set up is complete, press the
it look payroll, payroll data is not according to your request to display a different color.
Second, a classification drop-down list filled with items
we often want to enter the name of the enterprise to the table in order to maintain the name of consistency, the use of
1. on Sheet2 in the name of the business by category (such as
2. Select A column (
follow the above operation, the B, C ... ... column named If column C), the implementation of In the Enterprise
then need to enter the corporate name of the selected column (for example, column D), then open the C1), OK to exit.
4. Select any cell in column C (eg, C4), click the right drop-down button, select the appropriate Then select the corresponding cell in column D cell (eg D4), click the drop-down button to the appropriate category business name from the list, select the desired company name fill the cell.
Tip: When printing a report in the future, if you do not need to print
Third, the establishment of a
1. the space in the toolbar right-click, choose In the
click
2. and then in the (common document), and follow the above named action it (such as
repeat the above operation, add a few more than the name of the document list.
3. select the distribution Hyperlink By pressing the
repeat the above operation, the menu item and its corresponding workbook hyperlink documents together.
4. After the need to open the
Tip: Although we will
Fourth, the production of
1. execute Select the .
follow the above operation, one by one good other special symbols to record the input
2. open the A work area in the toolbar.
switch to the How many buttons).
3. Select one of the
4. Right-click a button named after, in the subsequent pop-up shortcut menu, select out.
Repeat this step, the macro button and the corresponding link.
5. close the
five, use the
1. Open the worksheet to print, use the mouse to the need to print the row (or column) marked drag, select them and then right-click in the shortcut menu that appears, select will need to print the row (or column) hidden.
2. implement the
3. the hidden rows (or columns) are displayed, and repeat the above operation,
4. After the need to print a form, open the , layout look, click the toolbar's
six, so that data on demand
If you want to sort according to department where employees sort the names both of these departments are not in alphabetical order of the information, nor Alphabetical, how do? can be self- define the sort sequence.
1. implementation the team, a workshop, two workshops, three workshops,
2. Select the one of the drop-down button,[url=http://www.uggclearanceuks.com/][b]title=UGG UK[/b][/url], select the just-defined sequence, according to two
seven, the data is completely hidden part of
worksheet cell contents do not want to enabling visitors to access, it had to be hidden.
1. selected to hide the contents of the cell (area), the implementation of the
2. and then switch to the
3. execute
After this set after the contents of the cell is no longer displayed, is to use Excel's features can not be allowed to borrow transparent.
Tip:
eight, so that in the English input method appeared
intelligent editing table, some cells to enter the English, and some cells to enter the Chinese, it is inconvenient to repeatedly switch the input method, why not set and let the input method intelligently adjust it?
selected range of cells need to enter the Chinese, the implementation of tab, press the
need to enter the Chinese after the election in any range of cells in a cell, the Chinese input method (IME list first a Chinese input method) automatically opens when you select the other cells, Chinese input method automatically off.
nine, so that
1. execute
2. In the then click
3. If you need to enter the text later, just enter
ten, custom functions in Excel
Excel function, although abundant, but it does not meet all our needs. We can customize a function to perform certain operations. Here, we have from the definition of a trapezoid area calculation function:
1. Execute
2. In the window, execute the
3. in the right defined function to complete.
built-in functions can later be the same as using a custom function.
Tip: Use the above method defined in the corresponding function is usually only used in the workbook.
XI header following pictures
lining add to the background of the sheet, is lining the entire worksheet below, can only head in the table below lining it?
1. implementation Worksheet → Background
2. While holding down the Ctrl key while using the mouse in the picture without lining cells (area) in drag, and select those cells (area).
3. Click After this set after leaving the cell lining on the picture below, and the selected cell (area) there is no lining pictures below (in fact, is the picture to be
Tip? lining in the cell below the picture is not supported for printing.
twelve, with a hyphen get it (here assumed that the B, C, D columns into one column in).
1. in the D column, insert two empty columns (E, F column), then enter the formula in cell D1: = B1 & C1 & D1.
2. D1 cells again selected, use the
3. Select column E, the implementation of , press the
4. to B, C, D, E column deleted, completion of the merger work.
Tip: Complete Steps 1 and 2 operation, the combined effect has been achieved, but at this time if you delete B, C, D column, the formula error. Therefore, to carry out step 3, the formula into the same
raw score of
friends often ask, For this consideration, I am here gives a simple way with the function implementation.
student achievement is assumed here to save the Sheet1 worksheet range of cells A1 to G64, of which the first act title, the name of the first two acts of discipline.
1. Switch to Sheet2 worksheet, select cell A1, enter the formula: = IF (MOD (ROW (), 3) = 0, , sheet1! Aū, INDEX (sheet1! $ A: $ G, INT (((ROW () +4) / 3) +1), COLUMN ())))。
2. Again select cell A1 , use the > So far, the results of the basic shape, the following simple modification look.
3. adjust the row height and column width, the same selected range of cells A1 to G2 (the first a student performance of the region), according to The following operation).
4. also selected range of cells A1 to G3, click on Add to all of the results of the border.
press the What more appropriate function. Excel Please enter requirements (such as the associated help file, you can quickly determine the required function.
fifth, while viewing a different worksheet in multiple cells of data
Sometimes we edit a worksheet (Sheet1), the need View other worksheet (Sheet2, Sheet3 ... ...) the contents of a cell, you can use Excel's .
later, no matter what the worksheet, simply open the > In previous versions of Excel 2002, add a border to the operation of a range of cells is too much trouble, Excel 2002 which features a new expansion.
Click pull button, then the pop-up drop-down list, select drop-down button, select a border style, and then in the range of cells you need to add border drag, you can quickly draw the corresponding range of cells on the border.
Tip: ① If you draw the wrong frame, does not matter,[url=http://www.uggoutletmallonline.com/][b]title=UGG Outlet[/b][/url], Select the tool bar on the Color >
XVII. control of a specific cell type text length
Can you imagine when you enter the four digits of the cell was filled with a digit, or enter text in the cell number you have entered time, Excel will automatically determine, instant analysis and pop-up warning that nice ah! to achieve this function, Excel, is also not difficult.
example, we register the cursor to a cell, in order to enter the convenience of a unified and calculation, we hope the card warning year. style time distance
We know that each time you open Excel, the software is always turned on by default multiple sheets. In addition to this we can see that Excel has a strong single form of processing power, more suitable for multiple interrelated tables coordination To coordinate the association, of course, first you need to synchronize the input and therefore, in many cases, will need more than one table simultaneously in the same cell type the same content.
So how to set the table for editing? First We click on the first sheet tab name ). At this point, we see the name of the Excel title bar to a What we found, change the format of how to do it? First, we have to change the data format of the first table, and then click , Excel will pop-up nine, change case of text
In Excel, the form processing and data computing support to provide the most powerful formula is not, nor is the database, but the function in Excel functions do not think only for numbers, in fact, as long as written into the contents of the table, Excel has a special function of its editor, such as changing the text of the case.
in Excel 2002, provide at least three functions to convert the text of the case and they are: UPPER (source data grid), case, as to the first letter of each word is capitalized, etc. For example, in a form of cell A1, enter a lowercase the results obtained after the vehicle will be will be The easy way, especially for some of the information especially similar style, such as employee lists, place of origin and other information.
quickly if we want to extract the cells from A4 title, it is best to use , the number of characters to extract) grid, the number of characters to extract) words, it only in the target cell type and 5 of the word.
twenty-first, into the base words from English into ordinal base into ordinal word is a more complex problem because it does not have a very fixed pattern: most of the digital into ordinal number is used in the Moreover, function, just write a formula that can be easily converted. do not believe, see: IF (OR (VALUE (RIGHT (A2)) = {1,2,3,}, CHOOSE (RIGHT (A2), Despite the long list, but the meaning is very clear: ① If the number is to base words and ordinal conversion was so easy and quick to achieve.
XXII, filled with a special symbol digit
and financial dealings of the people know, the book fills in when there is a convention Safety Fill Law The basic format is formula to should be changed to REPT (head with # # 0.00 worksheet consisting of plain text to create the histogram. Its principle is very simple, it is smart to use a special symbol repetition, according to a specified cell results show the comparative effectiveness of different lengths.
example, we first annual production of a balance sheet, and then The results have been ROUND (D3 * 100,0 )),> XXIV calculate the total cell number of words
Sometimes, we may have a number of characters in a cell of interest, the cells need to calculate the total number of words To solve this problem, in addition to using the SUBSTITUTE > is to use Finally, the number of spaces + 1, we arrive at the number of characters in the cell has.
XXV, the euro conversion
This is a new tool in Excel 2002 if you choose to install the Excel 2002 is the default, then it may not be in the with other functions of Excel window, we can set the currency conversion by mouse EU member states a list of other currencies, the parity of course, in order to display the euro is more professional, we can also click on the Excel toolbar, the >
Related articles：
没有评论:
发表评论