:: Przegląd tematu - Advanced Excel tips (useful give my possession)

hegrfvsd

10, how to avoid the error message

enter the formula in Excel, sometimes not correctly calculated results, and in the cell displays an error message, the production of these errors, some due to the formula itself, and some are not. Here's introduce some common error messages, and proposed ways to avoid mistakes.

1) :####

mean error value: Enter the data into a cell or cell formula is too long the result is too large, so that the results show no less than in the cell. Date and time format, or the cell subtraction, there has been negative.

solution: increase the width of the column so that the results can be fully displayed. If it is subtracted from the date or time had caused negative , you can change the format of the cell, such as to text format, the result is a negative amount of time.

2) error value: # DIV / 0!

meaning: trying to divide by 0 . This error is usually associated with the following situations: the divisor is 0, the divisor used in the formula or the empty cells contain a zero value cell reference cells.

solution: Modify the cell references , or used as a divisor input cell value is not zero.

3) error value: # VALUE!

meaning: the text-input mathematical formula item. If you use incorrect parameters or operator, or when performing an automatic correction function can not correct the formula for the formula, will produce an error message # VALUE!.

solution: At this time the formula or function should be required to confirm the operator or parameters correctly, and that a formula references a cell contains a valid value. For example, cell C4 has a numeric or logical values, cell D4 contains the text, then the formula = C4 + D4, the system can not convert the text to the correct data type, which returns the error value # VALUE!.

4) error value: # REF!

meaning: delete the formula referenced by the range of cells.

The solution: restore the referenced cell range, or re-set the reference range.

5) error value: # N / A

meaning: no information can be used to run the calculation. In modeling, the user can enter in a cell # N / A, to show that is waiting for data. containing any reference to # N / A value of the cell will return # N / A.

The solution: wait for the data in the cell is filled with data.

6) error value: # NAME?

meaning: the formula used in Excel can not recognize the text, such as possible lose the wrong name, or enter a name deleted, if not the literal string enclosed in double quotes, will produce the error value

solution: If you are using the name does not exist arising from such errors should be sure to use the name does exist; if the name, function name, spelling mistakes should be corrected; the text string enclosed in double quotes; sure all areas used in the formula are used in reference to a colon (: ). For example: SUM (C1: C10). Note that the formula in the text enclosed in double quotes.

7) error value: # NUM! meaning: to provide an invalid argument to the worksheet function, or is the result of the formula is too large or too small to be expressed in the worksheet.

solution: Make sure the function parameter type used is correct. If the result is too big or too small for the formula, we should modify the formula, it results in -1 × 10307 and between 1 × 10307.

Super

error value: # NULL! meaning: in the formula to insert a space between the two areas to represent the intersection, but These two cells, there is no public. For example enter: \. the style can be changed to \to tell EXCEL to complete the action specified by the user. macro is similar to a computer program, but it is being fully operational in EXCEL, we can use a macro to complete the tedious, often repetitive work. macro to complete the action faster than the user's own done much faster. For example, we can create a macro for each row of the worksheet, enter a date and in each cell within the center-aligned date and format of this border-line application. We also You can create a macro, in the \do not know when to look for a macro to help. In fact, you may rest assured bold to use, if you only use the \time, as long as the implementation of this macro, the system will put the operation and run it again.

macro given below applications, as long as the \If you want the recorded macro and then edit it, we should have some knowledge of VBA.

* set a table for each job requires a fixed form of the header;

* set the cell has its own style into a form;

* are fixed for each print page setup;

* frequently or repeatedly enter some fixed content, such as Address lined form, list of other;

* create a formatted table;

* insert a thin sheet or other.

be noted that, EXCEL Macros and WORD macros some differences, for the recording operation, it will remember the coordinates of the cell (ie, all references are absolute), so in relation to the operation of position, be extra attention. If the phase with a relative reference, to learn from Offset methods,ugg bailey botton triplet, such as the following statement: ActiveCell.Offset (1,0). range (\little, if you really use it, you will find it to have more content and more flexible application methods.

1. Editing skills
2. The merger of the cell contents
3. Conditions indicate
4 . custom format
5. Drawing Function Image
6. custom functions
7. Matrix
8. automatically switch the input method
9. batch delete empty lines
10 . how to avoid the error message
11. macro use
12. the icon of the application of skills
share friends

Of:%% # # boats has been shared 1 time Comments (0) Copy this link to report share

9, bulk delete blank lines

sometimes we need to delete Excel workbook in the blank line, the general practice is to to find out one by one blank line, and then delete it. If the number of rows in the table a lot of work, this is very inconvenient. We can use the \Insert a new blank line, then press Ctrl + A keys to select the entire worksheet, right-click \have suffered a drop-down list box, in a typical drop-down list box, select the column \br> In all cases the data have been selected, click the \Data \one of the blank cells, while the other columns of data and blank cells are not affected, you can copy the first column, paste it to a blank worksheet, according to the above method to delete all blank lines, and then Copy this column and paste it into the original position of the corresponding worksheet.

3, conditions indicate

we know, the use of If the function can be achieved in accordance with the conditions of display. A common example is when the teachers in student achievement statistics, want to enter scores below 60, can appear as \This effect, the use of IF functions can be easily achieved. Assumption results in the A2 cell, determine the results in cell A3. Then in cell A3 enter the formula: = if (A2 0, SUM (A1: A5), 0) this style on the use of a nested function, meaning that, when A1 to A5 and large at 0, returns this value if less than 0, it returns 0. Another point to draw your attention: The above symbols are the half-angle, and the IF with no spaces between brackets.

12, the chart application skills

Excel offers 14 standard chart types, each with a variety of combinations and transformations. Among the many chart types, select a chart that better? according to the different data and different requirements, you can choose different types of chart. The choice of the chart with the data in the form of relevant, then we consider the effectiveness and sense of aesthetics. Here are some common rules.

area chart: shows the magnitude of change over time. When Several parts are unchanged, while the sum of those parts you are interested, they are particularly useful. area chart that you see changes in the various parts separately, but also see the overall change.

bar: formed by a series of horizontal bars. makes a point on the timeline, two or more items is comparable relative size. For example: it can compare each quarter, three products in the sales of any kind. Article charts in each of the worksheet is a single data point or more. because it is just the column of the rows and columns are transferred over, so are sometimes used interchangeably.

cylindrical Figure: composed by a series of vertical bars, usually a period of time to compare two or more items in the relative size. For example: quarterly or annual sales of different products compare several projects in different sectors in the funding allocation, each year The number of such types of information. bar chart type is used widely, many people are using the chart from its beginning.

Line: is used to show trends over time. For example: data over time is an increasing trend, and the other in a downward trend over time, we can line chart, to predict the future. For example: speed - time curve, thrust - fuel consumption curve, lift coefficient - Mach number curve pressure - temperature curve, the fatigue strength - rotation curve, the power costs of transfusion - transmission distance curve, using a line chart can be expressed more generally in engineering applications, if one of the data in several ways, line chart where there are several different lines, such as five athletes in the process of change in velocity meters, there are five line, compared to each other, can also add a trend line to predict the speed.

stock chart : It is with three data series line chart is used to display a given time period of the subject shares of high, low and close. by the highest, lowest drawing lines between data points forming vertical lines, while the axis representatives on the closing price of a small scale. stock chart used for finance, commerce, etc., used to describe commodity prices, currency exchange rates and the temperature, pressure measurement, of course, describe the stock price is the best at the.

pie chart: comparison of several data used for the formation of the sum of its percentage of the value of the most useful. the representative of the total pie, each with a wedge or slice the number of representatives. For example: total sales of different products that percentage of total sales, the units of the total funding for the proportion of funding, collection of books, how many of each type and so on. expressions pie chart although only one data column, but for expressing more clearly understood, but also easy to learn , and therefore in practice more than used. If you want multiple series of data, you can use the ring diagram.

radar: How to display data or other data changes at the center. each category coordinates from the center of radiation. from the same series of data lines connected with. You can use radar to map the sequence of several interrelated, it is easy to make a visual contrast. For example: You have three with five same parts of the machine, the radar can draw on each machine for each component wear.

XY scatter: show pairs of numbers and the trends they represent the relationship between . For each number on a number is drawn in the X axis, while the other was drawn in the Y axis. had two points in the vertical axis, intersecting at a mark on the chart. when a large number of such be drawn, the emergence of a graph. Scatter the important role of the function can be used to draw curves, from simple trigonometric, exponential, logarithmic functions to more complex hybrid function, can use it to draw quickly and accurately the curve, so in teaching, often used in scientific computing.

There are other types of charts, such as Cylinder, Cone, Pyramid, just a variation of bar and column charts with no outstanding features, but with a relatively small, this stop here. here is to note: This is only general application of the chart, sometimes a set of data can be used to express a variety of charts, then should be selected according to specific circumstances. For some charts, if a data sequence drawn into a cylinder, while the other is drawn into a line chart or area chart, the chart will look better.

in EXCE in L allows the creation of such a combination of charts, but if you want to create an EXCEL chart type combination is not allowed, such as a two-dimensional map can not be the same mix with a three-dimensional chart, this combination is not possible, the system will display an error message. how to build mixed-chart it? click the \Do these forms are still not satisfied within the definition to click the \

1, editing skills

1 editing skills

(1) fraction of the input

If the direct input of \January 5 \

(2) sequence \quotation marks), then enter \

(3) the date of the input

If you want to enter the \If you want to enter the current date, click \

(4) fill streak

workbook if you want to join in the beautiful horizontal stripes, can use the alignment of the filled function. First filled in a cell of \\
Figure 1

(5) multiple sheets to enter the same content in several worksheets

enter the same data in the same location, you can select a worksheet, and then hold down the Ctrl key, then click the window to the bottom left corner of the Sheet1, Sheet2 ...... to directly select the input the same content multiple sheets, then in any one of them to enter these same worksheet data, then the data will automatically appear in the table selected among other work. After input finished, press the Ctrl key on the keyboard, then use the left mouse button click on the selected multiple worksheets, work table to remove these links, or in a form input data will then appear in the election the other work table.

(6) cells do not populate the same data continuously

select a cell, hold down the Ctrl key, right click the other cells, it will select all those cells the. Enter data in the edit area, then hold down the Ctrl key while you tap Enter, in all the selected cells have emerged in the data.

(7) to display the formula in cell

data in the table if the majority of work is generated by a formula and want to quickly know the formula for each cell in the form of, for editing , you can do: Use the left mouse button click on \\At this time the division of labor in each cell that come. If you want to restore the display of calculated results to re-set \
Figure 2

( Super

Select the text using Ctrl + *

a work table, if there are many data tables, you can select the table by a cell, and then press Ctrl + * key to select the entire table. Ctrl + * the selected region: According to the selected cell to the surrounding radiation data involved are the largest regional cell. So that we can easily and accurately select the data table, and effective method to avoid dragging large range of cells selected screen Luangun phenomenon.
(9) quickly remove the contents of the cell

If you want to delete the contents of the cell content and its format and comments, can not simply be applied to the cell selected, then press the Delete key way to go. To completely remove the cells, using the following method: you want to remove the selected cell or range of cells; click the \

4,

Excel custom format by default in a lot of useful data formats, and can basically meet the requirements of use, but some special requirements, such as highlighted in some important data or information, set the display conditions, we must use a custom format function to complete. Excel custom format using the following general model: a positive number format, negative format, zero format, text format, in this general model, including three numbers and a text segment segment: use of data greater than zero positive number format; less than zero data using a negative format; zero data using a zero format; enter the body cells to use text format. We can also use test, add descriptive text and color to expand the use of custom format general application of the model.

(1) use of color in a custom format to a paragraph set the color, simply add the paragraph with enclosed in square brackets, color name or color number. Excel recognize the color is named: [black], [Red], [White], [Blue], [Green], [blue] and [magenta]. Excel is also recognition Press [Color X] specified color, where X is a number between 1-56, representing 56 kinds of colors (Figure 5).
Figure 5

(2) add description text After the input digital data to automatically add text, using a custom format: \Excel numeric data input position relative to add text.

(3) create a conditional format of six logic symbols can be used to design a conditional format: \), \> = \The corresponding figures using the format specified in paragraph, paragraph 3 of the remaining numbers to use format. If the test contains only one condition, they have different situations according to specific analysis.

custom format of the generic model is equivalent to the following formula : [>; 0] is the number of formats; [

another example, assume that the ongoing settlement of accounts, you want to use the blue more than $ 50,000 account balance, negative values shown in brackets in red, the rest use the default value color display, you can create the following format: \0.00_) \used in the preparation of the budget, need converted from grams kilograms, tons, then you can define the following format: \_k_m \
In addition, we can use a custom format to achieve the purpose of hiding the input data, such as the format \Hide all the input values. custom format only changes the appearance of the display of the data, the data does not change the value, that does not affect the calculation of data. flexible use of a good custom format function, will give a great deal of practical work convenient.

Advanced Excel tips (useful give my possession)

6, Although the custom function

already a large number of built-in Excel function,Actually, not really what Sanlu milk powder., but sometimes may also encounter some computational functions can be used without the case. If a company uses a special mathematical formula to calculate the discount products buyers If there is a function to calculate how much more convenient? said the following about how to create such a custom function.

custom functions, also called user-defined functions, Excel is the most innovative and attractive features one of the following Visual Basic we create a function module. In the following example,ugg classic tall sale, we give each person the amount by a factor, if it is working meals at work, and beat the tickets; if it is working overtime meal hit half of the amount; if it is a rest day to dinner, hit Jiuzhe. First, open the \\* tatol ElseIf rr = \> Then close the editor, as long as we enter in the appropriate column rrr (F2, B2), then the amount of money even after the kick off out (Figure 10).
Figure 10

7, Matrix

Excel's powerful computing capabilities, not only capable of simple arithmetic, can also be an array, matrix calculation.

(1 ) the definition of arrays and matrices

matrix is not a number, but an array. In Excel, the array of domain occupied by a unit, the unit field with large brackets, such as {A1: C3}, in order and the general unit Domain A1: C3 phase difference. set the domain when the first selected cell, press Shift + Ctrl + Enter key to automatically generate large parentheses, an array of fields to be confirmed.

a cell is a variable, a unit of the domain can be as a set of variables. In order to calculate the convenience, a set of variables to an array of the best name. For example A = {A1: C3}, B = {E1: G3} and so on. array name setup steps are: an array of fields selected , click the \enter the name on the line.

matrix function is Excel-specific modules for matrix calculations. use the \The \calculate all the matrix elements corresponding to the product of and.
Figure 11

(2) the basic calculation of matrix computation and matrix array

very different calculations, such as the following example, A and B are defined array, as the two arrays are 3 × 3, the output is 3 × 3 unit cells. calculated results when the first output of the selected matrix domain of 3 × 3 of the range of cells, and then enter the formula. If you enter \\and \. \br> In a worksheet, the data is often both, there are text, so that input back and forth in the need to repeatedly switch between English and Chinese input method is very troublesome. If you want to enter something great regularity such as this column is all words, all Chinese to explain the next column, you can use the following method for automatic switching. is:

(1) Select the column to type in English, click \Select the \Click \\English automatically switch between input methods.

2, the combined cell contents
http://www.hongen.com/pc/oa/excel2k/ex2k1302.htm
needed, sometimes I wish the B list and C list the contents of the merger, if the number of rows less, can directly use the \

solution is: In the C line, insert a blank line (if the D column is not content to operate directly in the D column), in D1, enter \, C, and the two columns. Select cell D1, the cell with the mouse to point the bottom right corner of the small box \B series and C series combined. Then do not just put the first B and C column remove column, the first column of the results of D should copy it, and then \Time you delete B, C, D columns.

The following is a \With AutoCAD drawing, some people like the coordinate point is stored in EXCEL, called when drawing curves in these parameters. Stored data format is \merged into C column, enter in C1: = A1 &\All contents of the combined (see Figure 3-4).

merge the contents of different cells, there is a way is to use CONCATENATE function, the function of certain text string into a string, the specific operation for the \. For example, assume that the investigation in a river ecosystem in the table, B2 contains \, \
Figure 3 Figure 4

5, drawing functions for teaching image

friends will encounter problems drawn function curve now! If you want to draw quickly and accurately a function curve, a chart can make use of EXCEL functions, it can make you draw the curve of both standard and beautiful. You have to ask, is not hard to learn it? fact, this is not difficult, can be very simple, do not believe you told me to try.

to draw the y = | lg (6 + x ^ 3) | of the curve, for example, the method is as follows: in a blank worksheet, enter the function of self- variables: A column in the cell A1 enter \Arithmetic input method, enter the first two values, given the number of variables and the number from the step between, and then select the two cells A2 and A3, so that the two into a rectangle with a black border, and then This is a black rectangle with the mouse to point to the lower right corner of the small box \input function type: B1 cells in column B enter the general functional form of written expression, y = | lg (6 + x ^ 3) |; in cell B2 enter the \, B2 Genei Ma calculated on the results obtained. At this time, and then select cell B2, so that the cursor point to the lower right corner of the rectangle B2 \place to complete the calculation of function values.
Figure 7

drawn curve: Click the toolbar \7), then in the \\Note: How to determine the initial value of the independent variables, the step between data points is the number, this is according to the specific characteristics to determine the function, which is the ability of the test users. If you want to function extremely quickly found value or see its development trend, the data presented do not necessarily have to be a point arithmetic, can be arbitrarily given.

from simple to complex logarithmic trigonometric functions, exponential functions, are EXCEL can be used to draw curves. If available, you can also use EXCEL to complete the determinant, the matrix of the various calculations, a simple integral operation, the use of iteration of a function value (such as x ^ 2 = x ^ 7 +4 , can be iterative method to solve x value), and so on, all things related to computing, to find EXCEL to help, it will give you a satisfactory answer.