Tips for using Excel workbooks! Very practical

Tips for using workbooks! Very practical!

Advanced Excel skills   1. Editing skills (1) Score Input If you enter "1/5" directly, the system will change it to "January 5". Solution: first enter "0", then enter a space, and then enter the score "1/5 ". (2) input of the sequence "001" If you enter "001" directly, the system automatically determines that 001 is data 1. Solution: first enter "'" (single quotation marks in Spanish), and then enter "001 ". (3) Date input If you want to enter "April 5", enter "4/5" and press Enter. To enter the current date, press Ctrl +. (4) Fill stripe To add beautiful horizontal stripes to a workbook, you can use the Fill function in alignment mode. Enter "*" or "~" in a cell. And then click the cell, drag the mouse to the right, select a number of horizontal cells, click the "format" menu, select the "cell" command, in the pop-up "cell format" menu, select the alignment tab, select "fill" from the horizontal alignment drop-down list, and click "OK" (1 ). Figure 1 (5) enter the same content in multiple worksheets. When entering the same data in several worksheets at the same position, you can select a worksheet, press Ctrl, and then click sheet1, sheet2 ...... to directly select multiple worksheets with the same content, and then enter the same data in any worksheet. The data will automatically appear in other selected worksheets. After entering the worksheet, press Ctrl on the keyboard again, and then left-click the selected worksheet to unassociate the worksheet, otherwise, the input data in a form appears in the selected worksheet. (6) The same data is filled with Discontinuous cells. Select a cell, press Ctrl, and click other cells. All these cells are selected. Enter data in the editing area, press Ctrl, and press enter to display the data in all selected cells. (7) display formulas in Cells If most of the data in a worksheet is generated by a formula and you want to quickly know the formula form in each cell for editing and modification, you can do this: click the "Tools" menu with the left mouse button, select the "options" command. In the "options" dialog box, click the "View" tab, and set the "formula" item in the "Window Options" column to be valid, click OK (2 ). The division of labor in each cell is displayed. If you want to restore the display of the formula calculation result, set the "formula" item in the "Window Options" column to invalid. Figure 2 (8) use Ctrl + * to select text If a worksheet contains many data tables, you can select a cell in the table and press Ctrl + * to select the entire table. CTRL + * the selected area is the maximum area of data cells involved in radiating from the selected cell to the surrounding area. In this way, we can easily and accurately select a data table, and effectively avoid scrolling the screen when a large cell area is selected by dragging the mouse. (9) Clear cell content quickly If you want to delete the content in the cell and its format and annotation, you cannot simply select the cell and press the delete key. To completely clear a cell, use the following method: select the cell or cell range to be cleared; click the "all" command in the "clear" item in the "edit" menu, these cells are restored. 2. Merge cell content Http://www.hongen.com/pc/oa/excel2k/ex2k1302.htm If you want to merge the content of Column B and column C as needed, you can directly use cut and paste to complete the operation if the number of rows is small, however, if there are tens of thousands of lines, you cannot do this. Solution: insert an empty column after row C (if column D has no content, directly operate on column D), and enter "= B1 & C1" in column D1 ", the content of column d1 is the sum of Column B and column C. Select cells D1 and point to the small box "■" in the lower-right corner of the cell. When the cursor changes to "+", hold down the cursor and drag it down to the end row to be merged, then, column B and column C are merged. In this case, do not delete column B and column C. Copy the result of column D first, and then paste the data to an empty column by using the "paste selectively" command. Then, the data in columns B, C, and D is deleted. The following is an example of "&" practical application. When drawing with AutoCAD, some people like to store coordinate points in Excel and call these parameters when drawing a curve. The data format is "X, Y". First, enter the coordinate value in Excel, put the X coordinate value in column A, and put the Y coordinate value in column B, then, use "&" to merge column A and column B into column C and enter: = A1 & "," & B1 in column C1. In this case, the data format in column C1 meets the requirements, drag the C1 cell down to merge all the contents of column A and column B (3-4 ). To merge the content of different cells, you can also use the concatenate function to merge several text strings into one string. The specific operation is "= concatenate (B1, c1 )". For example, if B2 contains "species", B3 contains "river trout", and B7 contains a total of 45 in a river ecosystem survey Worksheet: enter "= concatenate (" results of this river ecosystem Survey: ", B2," ", B3," is ", B7,"/km. ")" Calculation result: the river ecology survey results: the number of fish species in the river is 45 per kilometer. Figure 3 Figure 4 3. Conditional display We know that the IF function can be used to display data according to conditions. A common example is that when a teacher wants to enter a score below 60 when calculating the student's score, the score is displayed as "fail"; when the score is over 60, displayed as "pass ". This effect can be easily achieved using the IF function. Assume that the score is in cell A2 and the result is in cell A3. Then, enter the formula = If (A2 <60, "fail", "pass") in cell A3, and you can also nest the IF function or other functions in the IF function. For example, if you enter: = If (A2 <60, "fail", if (A2 <= 90, "pass", "excellent ")) the score is divided into three levels. If the input is = If (A2 <60, "difference", if (A2 <= 70, "medium", if (A2 <90, "good", "excellent "))) the score is divided into four levels. For another example, the formula: = If (sum (A1: A5> 0, sum (A1: A5), 0) uses nested functions, if the sum between A1 and A5 is greater than 0, this value is returned. If it is smaller than 0, 0 is returned. Note that the above symbols are halfwidth characters and there cannot be spaces between If and parentheses. 4. Custom format In Excel, many useful data formats are preset, which can basically meet the usage requirements. However, some special requirements, such as displaying important data or information and setting display conditions, are imposed, you must use the custom format function. The custom format of Excel uses the following general model: positive number format, negative number format, zero format, text format. In this general model, it contains three number fields and a text segment: data greater than zero uses the positive number format; Data smaller than zero uses the negative number format; Data equal to zero uses the zero format; text format is used for the input cell body. We can also use conditional testing to add the description text and color to extend the application of a general model in a custom format. (1) To set the color in a segment of the custom format, you only need to add the color name or color number enclosed by square brackets to the segment. The colors recognized by Excel are [Black], [Red], [White], [Blue], [Green], [Blue], and [foreign Red]. Excel also recognizes the color specified by [color x], where X is a number between 1 and 56, representing 56 colors (5 ). Figure 5 (2) to add a description text, you must add the text automatically after entering the numeric data. The custom format is "text content" @. You must add the text automatically before entering the numeric data, the custom format is @ "text content ". The @ symbol position determines the position of the numeric data input in Excel relative to the text added. (3) You can use six logical symbols to create a condition format:> (greater than),> = (greater than or equal to), <(less), <= (less than or equal to), = (equal to), <> (not equal to), if you think these symbols are not easy to remember, simply use ">" or "> =. Because the custom format contains a maximum of three fields, Excel requires that only two conditional tests can be included in the first two numeric segments. Numbers that meet a certain test condition use the format specified in the corresponding segment, the remaining numbers are in the 3rd segment format. If only one conditional test is included, it is analyzed based on different situations. The general model in the custom format is equivalent to the following formula: [>; 0] positive number format; [<; 0] negative number format; zero format; text format. The following is an example: select a column, click the "cell" command in the "format" menu, and select the "Number" tab in the pop-up dialog box, select "Custom" in the "category" list, and enter "positive number:" ($ #,## 0.00) in the "type" text box. "negative number: "($ #, #0.00);" zero ";" text: "@", click OK to complete format settings. If "12" is entered, "positive number :( $12.00)" is displayed in the cell. If "-0.3" is entered ", "negative number ($0.30)" is displayed in the cell. If "0" is entered, "zero" is displayed in the cell. If the text "this is a book" is input ", the "text: this is a book" is displayed in the cell ". If you change the content in the custom format, "[Red]" positive number: "($ #,## 0.00); [Blue]" negative number: "($ #, #0.00); [yellow] "0"; "text:" @ ", then the positive, negative, and zero colors are displayed. If you enter "[Blue]; [Red]; [yellow]; [Green]", the positive, negative, and zero-sum colors (6) are displayed ). Figure 6 For another example, if you want to use blue to show accounts with a balance of more than $50,000, the negative value is displayed in red and the remaining values are displayed in the default color, you can create the following format: "[Blue] [> 50000] $ #, #0.00 _); [Red] [<0] ($ #,## 0.00 ); $ ##,## 0.00 _) "using conditional operators can also be used as a powerful auxiliary method for zooming numeric values. For example, if the unit produces several products, each product only needs a few grams of a compound, and thousands of such products are produced in a day. Therefore, the budget needs to be converted from grams to kilograms and tons. In this case, the following format can be defined: "[> 999999] #, #0, _ m" ton ""; [> 999] #, _ K_m "kg "; # _ k "Grams", you can see that using the condition format, the combination of thousands of operators and the even interval indicator, you can improve the readability and efficiency of the worksheet without increasing the number of formulas. In addition, we can also use custom formats to hide input data. For example, the format ";##; 0" only displays negative numbers and zeros, and the input positive numbers are not displayed; the format ";" hides all input values. The custom format only changes the display appearance of the data, and does not change the value of the data. That is to say, data computing is not affected. Flexible use of the custom format function will bring great convenience to the actual work. 5. Draw a ghost image Friends who are doing teaching work will surely encounter the problem of drawing function curves! If you want to quickly and accurately draw a function curve, you can use the Excel chart function to make the curve you draw standard and beautiful. You will certainly ask, isn't it difficult to learn? In fact, this is not difficult at all. It can be said that it is very simple. If you don't believe it, just give me a try. Take the plot of Y = | lg (6 + x ^ 3) | as an example. The method is as follows: In a blank worksheet, enter the independent variable of the function: enter "x =" in the A1 lattice of column A, indicating that this is an independent variable. Then, enter the values of the independent variables from small to large in column A2 and the subsequent cells; in actual input, we usually use the input method of the equals difference series. First, we enter the first two values to determine the step between numbers in the independent variables, and then select cells A2 and A3, turn the two items into a rectangle with a black border, and then point the cursor to the small square in the lower right corner of the black rectangle "■". When the cursor turns to "+, hold down the mouse and drag the cursor to the appropriate position to complete the input of the independent variable. Input Function formula: A general written expression of the input function formula in B1 of Column B, y = | lg (6 + x ^ 3) |; enter "= ABS (log10 (6 + A2 ^ 3)" in the B2. The calculation result is obtained immediately in the B2. Then, select the B2 frame and point the cursor to "■" in the lower-right corner of the B2 rectangle ", when the cursor changes to "+", press and hold the cursor and drag it along Column B to the appropriate position to complete function value calculation. Figure 7 Draw a curve: click the "chart wizard" button on the toolbar, select "x, y scatter chart" (7), and then in the "X, select "numerous data Point Smoothing Line scatter chart" in the Y scatter chart type. You can view the function image to be drawn and find that it is not the function curve we want. Click "Next, select the "data generated in columns" item and give the data area. Then the curve is in front of us (8 ). Figure 8 Note: how to determine the initial value of the independent variable and the step size between data points is determined based on the specific characteristics of the function, which is also a test of user capability. If you want to quickly find the extreme values of a function or see its development trend, the given data points do not have to be of an equal deviation. You can specify them as needed. You can use Excel to draw curves from simple trigonometric functions to complex logarithm and exponent functions. If you use it, you can also use Excel to complete various computations of the determinant and matrix, and perform simple integral operations, use iteration to evaluate the function value (for example, x ^ 2 = x ^ 7 + 4, and use iteration methods to evaluate the value of X). For any computing problems, contact excel, it will certainly give you a satisfactory answer. 6. User-Defined Functions Although there are a large number of built-in functions in Excel, sometimes some computing functions are not available. If a company uses a special mathematical formula to calculate the discount of product buyers, isn't it more convenient if there is a function to calculate? The following describes how to create such a UDF. Custom functions, also known as user-defined functions, are one of the most creative and attractive Excel functions. Next we will create a function in the Visual Basic module. In the example below, we will multiply the amount of each person by a factor. If it is a work meal at work, it will be off; if it is a work meal during overtime, it will be off; if you have a meal on a day off, off. Open the Tools menu, click Visual Basic Editor in the macro command, enter the Visual Basic editing environment, right-click the current table in the project-vbaobject column, select "insert"-"module", and create the following function rrr in the right bar,CodeFunction RRR (tatol, RR) if RR = "work" then RRR = 0.6 * tatol elseif RR = "overtime" then RRR = 0.5 * tatol elseif RR = "Day Off" then RRR = 0.9 * tatol end if end Function (9 ). Figure 9 Close the editor. As long as we enter RRR (F2, B2) in the corresponding column, the amount after the discount is calculated (10 ). Figure 10 7. Matrix computing The powerful computing function of Excel can not only perform simple arithmetic operations, but also calculate arrays and matrices. (1) Definition of arrays and Matrices A matrix is not a number, but an array. In Excel, an array occupies a unit field, which is represented by braces, for example, {A1: C3}, to distinguish it from an ordinary Unit Field A1: C3. When setting, select the Unit Field and press SHIFT + Ctrl + enter. The large arc will be automatically generated, and the array field can be confirmed. A cell is a variable, and a unit field can be considered as a group of variables. For ease of calculation, it is best to give an array name to a group of variables. For example, a = {A1: C3} and B = {E1: G3. To set the array name, select an array field, click the Insert menu, select the define command in the name item, enter the array name, and click OK. The simpler naming method is: select an array field, click the Name box, and enter the name directly. A matrix function is a specialized module for Matrix computing in Excel. Use the "insert"-"function" command to open the "paste function" dialog box (11), and select "Mathematical and trigonometric functions" in the function Category column. Commonly Used matrix functions in the right sidebar include: mdeterm -- calculate the determinant of a matrix; minverse -- calculate the inverse matrix of a matrix; mmult -- calculate the product of two matrices; sumproduct -- calculate the sum of the product of all elements corresponding to the matrix. Figure 11 (2) Basic matrix calculation Array computing is very different from matrix computing. For example, in the following example, both A and B are defined arrays, because both arrays are 3 × 3, the output result is also 3*3 cells. During calculation, select the output field of the matrix calculation result, which is a 3 × 3 cell area, and enter the formula. If you enter "= a + B" or "= A-B", the calculation result is the addition or subtraction of the array's corresponding items. Input "= a * B" to multiply array A and B, input "= A/B" to indicate array a except array B. If you want to perform matrix calculation, you need to use the corresponding matrix function. Matrix addition, subtraction and array addition and subtraction expression is the same, is also "= a + B" and "= A-B", indicating that the Matrix Multiplication can input "= mmult (A, B) ", while matrix Division is the inverse matrix of matrix A multiplied by B, so the formula is" = mmult (A, minverse (B ))". After the formula is entered, press SHIFT + Ctrl + enter to obtain the calculation result. For more complex matrix computing, you can use step-by-step computing. 8. Automatic Input Method In a worksheet, there are usually both data and text. In this way, you need to switch the input method between Chinese and English repeatedly during input, which is very troublesome. If you want to input something regular, such as this column is full of words and the next column is full of Chinese explanations, you can use the following methods to achieve automatic switch. The method is: (1) Select the columns to enter in English, click the "data" menu, and select "validity... "Command. In the pop-up" Data Validity "dialog box, select the" input method mode "tab and select" Disable (English mode) "in the" Mode "box, click OK (12 ). Figure 12 (2) Select the columns for which you want to enter Chinese characters. In the "valid data" dialog box, click the "IME mode" tab and select the "open" command in the "Mode" box, click OK. In this way, when the cursor is in the previous column, you can enter English, and in the next column, you can directly enter Chinese, thus enabling automatic switching between Chinese and English input methods. 9. Batch Delete empty rows Sometimes we need to delete the blank rows in the Excel worksheet. Generally, we need to find the empty rows one by one and then delete them. This is inconvenient if there are many rows in the worksheet. We can use the "Automatic filtering" function to locate all empty rows and then delete them at one time. Practice: Insert a new blank row in the table, press Ctrl + A, select the entire worksheet, and click the "data" menu with the mouse, select the "Automatic filtering" command in the "filter" item. A drop-down list box is displayed at the top of each column. Select "blank" in the drop-down list box of a typical column until no data is visible on the page (13 ). Figure 13 When all data is selected, click the edit menu, select the Delete line command, and then click OK. At this time, all empty rows have been deleted, and then click the "data" menu, select the "Automatic filtering" command in the "filter" item, and all the data in the worksheet will be restored. Insert an empty row to avoid deleting the first row of data. If you want to delete only the blank cells in a column, but the data in other columns and the blank cells are not affected, you can copy this column and paste it on the blank worksheet, delete all empty rows in the preceding method, copy and paste the column to the corresponding position of the original worksheet. 10. How to avoid error messages After entering a formula in Excel, you may not be able to calculate the result correctly and display an error message in the cell. Some errors are generated by the formula itself, and others are not. The following describes several common error messages and proposes methods to avoid errors. 1) error value :#### Meaning: the data entered into the cell is too long or the result produced by the cell formula is too large, so that the results cannot be displayed in the cell. Or the cells in the date and time formats are subtracted, and a negative value is displayed. Solution: Increase the column width so that the results are fully displayed. If it is caused by a negative value generated by the date or time subtraction, you can change the format of the cell, for example, to the text format, and the result is a negative amount of time. 2) error value: # DIV/0! Meaning: try to divide by 0. This error usually occurs in the following situations: Division 0, Division using an empty cell in the formula, or cell reference containing a zero-Value cell. Solution: Modify the cell reference, or enter a non-zero value in the cell used as the divisor. 3) error value: # value! Meaning: enter a mathematical formula for referencing a text item. If incorrect parameters or operators are used, or the formula cannot be corrected when the auto-correction formula function is executed, an error message # value is generated !. Solution: Make sure that the operators or parameters required by the formula or function are correct and that the cells referenced by the formula contain valid values. For example, if cell C4 contains a number or a logical value while cell D4 contains text, the system cannot convert the text to the correct data type when the formula is C4 + D4, therefore, an error value # Value! is returned !. 4) error value: # ref! Meaning: the range of cells referenced by the formula is deleted. Solution: Restore the referenced cell range or reset the reference range. 5) error value: # N/ Meaning: no information is available for calculation. When creating a model, you can enter # N/A in the cell to indicate that the data is waiting. Any cell that references a value containing # N/A returns # N/. Solution: Fill in the data in the cell where the data is waiting. 6) error value: # NAME? Meaning: words that cannot be recognized by Excel are used in the formula. For example, a wrong name or a deleted name may be entered. If a text string is not enclosed in double quotation marks, this error value is also generated. Solution: If a nonexistent name is used and this type of error occurs, check that the name actually exists. If the name is used, correct the spelling of the function name; enclose text strings in double quotation marks. Make sure that all area references used in the formula use colons (:). For example, sum (C1: C10 ). Note that the text in the formula is enclosed in double quotation marks. 7) error value: # num! Meaning: Invalid parameters are provided to the worksheet function, or the formula result is too large or too small to be expressed in the worksheet. Solution: The parameter type used in the confirmation letter is correct. If the formula result is too large or too small, you must modify the formula so that the result is between-1 × 10307 and 1 × 10307. 8) error value: # null! Meaning: Insert a space between two ranges in the formula to indicate the intersection, but the two ranges do not have public cells. For example, input "= sum (A1: A10 C1: C10)" will produce this situation. Solution: cancel the space between two ranges. The preceding formula can be changed to "= sum (A1: A10, C1: C10 )" 11. Macro Application A macro is an instruction set used to tell EXCEL to complete user-specified actions. Macros are similar to computersProgramBut it is completely running in Excel. We can use macros to do boring and frequent repetitive work. Macro operations are much faster than you do. For example, we can create a macro to input a group of dates on each row of the worksheet, align the dates in the center of each cell, and apply the border format to this row. You can also create a macro to specify print settings and print documents in the "page settings" dialog box. Due to the influence of the macro virus and fear of programming, many people do not dare to use the macro, or do not know when to seek help from the macro. In fact, even if you use the recording macro method with peace of mind, there is no difficulty at all, just recording some operations like using a recorder, when the macro is executed, the system will execute the operation again. The following describes the application of macros. You only need to use "Recording macros" to complete the task without programming. If you want to edit the macro you have recorded, you must have some VBA knowledge. * Set a Fixed Header required for each worksheet; * Set cells to a style; * Fixed page settings for each print; * Enter fixed content frequently or repeatedly, such as the formatted company address and personnel list; * Create A formatted table; * Insert a worksheet or a workbook. It should be noted that the Macros in Excel are different from those in word. For recording operations, it will remember the coordinates of cells (that is, all references are absolute ), pay special attention to location-related operations. If relative references are used, you can use the offset method, such as the following statement: activecell. offset (1, 0 ). range ("A1 "). the Application of select macro is very wide. The above mentioned is only a little bit. If it is used, you will find that it has richer content and more flexible application methods. 12. Chart application skills Excel provides 14 standard chart types, each with multiple combinations and transformations. Which of the following chart types is better? You can select different types of charts based on different data and usage requirements. The choice of charts is mainly related to the form of data, followed by the perception effect and aesthetics. The following describes some common rules. Area Chart: displays the amplitude of changes within a period of time. It is especially useful when several parts are changing and you are interested in the sum of those parts. The area chart shows the changes of each part and the overall changes. Bar Chart: consists of a series of horizontal bars. Make the Relative Dimensions of two or more projects on the timeline comparable. For example, it can compare the sales volume of any of the three products in each quarter. Each entry in a bar chart is a separate data point or number on a worksheet. It is called with the rows and columns of the column chart, so it can be used interchangeably. Column Layout: it consists of a series of vertical bars, which are usually used to compare the relative sizes of two or more projects in a period of time. For example, the sales volume of different products is compared on a quarterly or yearly basis, the fund distribution of different departments in several projects, and the number of various materials each year. A bar chart is a type of chart that is widely used. Many people use charts from it. Line chart: used to display trends over a period of time. For example, data is increasing over a period of time, while data is declining over another period of time. We can use a line chart to make predictions for the future. For example: Speed-time curve, thrust-fuel consumption curve, lift coefficient-Mach number curve, pressure-temperature curve, fatigue strength-rotation curve, transfer power cost-transmission distance curve, etc, line charts can be used for representation. Generally, there are many applications in the project. If one of the data has several situations, there are several different lines in the line chart, for example, if the speed of five athletes changes within meters, there are five broken lines, which can be compared with each other, or the speed can be predicted by adding a trend line. Share price chart: a line chart with three data sequences. It is used to display the highest price, lowest price, and closing price of a stock subject within a given period of time. A vertical line is drawn between the highest and lowest data points, and a small scale on the axis represents the closing price. Stock price charts are mostly used in finance, commerce, and other industries to describe commodity prices, currency exchange rates, temperature, and pressure measurements. Of course, it is the best way to describe the stock price. Pie Chart: It is most useful when used to compare the percentages of several pieces of data in their total forms. The entire pie represents the sum, and each number is represented by a wedge or slice. For example, it indicates the percentage of sales of different products in total sales, the proportion of funds of each unit in total, and the proportion of each category in Collected Books. Although the pie chart can only express one data column, it is much used in actual work because it is clear and easy to learn and use. If you want data of multiple series, you can use a circular chart. Radar chart: shows how data changes by center point or other data. The coordinate values of each category radiate from the center. Data from the same sequence is connected to the same line. You can use radar charts to draw several internal correlated sequences, making it easy to make visual comparisons. For example, if you have three machines with five identical components, you can draw a wear volume for each component on the radar chart. XY scatter chart: shows the relationship between paired numbers and the trends they represent. For each number pair, one number is drawn on the X axis, and the other is drawn on the Y axis. A vertical line is formed after two points, and the intersection is marked on the chart. When a large number of such pairs are drawn, a graph is displayed. A scatter chart can be used to draw function curves. From simple trigonometric functions, exponential functions, logarithm functions to more complex mixed functions, it can be used to quickly and accurately draw curves, therefore, it is often used in teaching and scientific computing. There are other types of charts, such as a cylindrical chart, a cone chart, and a pyramid chart, which only change from a bar chart and a column chart. They have no outstanding features and are rarely used, I will not go into detail here. It should be noted that the above is only the general application of charts. Sometimes, a group of data can be displayed in multiple charts. At that time, you need to choose based on the specific situation. For some charts, if one data sequence is drawn into a column and the other is drawn into a line chart or area chart, the chart looks better. In exce, l allows this to create a composite chart, but if you want to create a composite chart type that is not allowed in Excel, for example, it is impossible to mix a two-dimensional chart with a three-dimensional chart, this combination is impossible, and the system will display an error message. How to Create a hybrid chart? Click the "chart wizard" button and select the "custom type" tab. There are also 20 chart types (14) and different combinations. If you are not satisfied with the internal definitions, click the "Custom" Option Button to define a new chart type according to the wizard.