Weak current College】 【EXCEL tips, life is enough for you to enjoy (1) --- Power By】 【China power house network.

<br> <br> Convert columns to rows or rows into columns <br> Suppose you have a text column: <br> Dim sum <br> Meat <br> Beverage <br> Special products <br> <br> You want .to change it to a line, as follows: <br> Dim sum meat beverage-products <br> Or you might want to change the row of data in each column. <br> Once a to move the data manually can be very tedious .. To save time, you can use the "Paste" command to quickly add a column of data transfer for row data, and vice versa. <br> In the rows and columns to move data between <br> 1. in a column / .row or columns / rows in the replication data. <br> 2. Paste the copied data, right-click the first destination cell (want to paste the data into rows or columns of the first cells), and then click the "Paste" .. <br> 3. in the "Paste Special" dialog box, select the "transpose", and then click OK. <br> "Transpose" check box located at the corner of the dialog box: <br> <br> From .your choice of the first cell, Excel pastes the data into a row or column. <br> Comments <br> Columns and rows can overlap. For example, if you select values in column c, and try to paste it into the c columns .overlap line, Excel displays an error message. You want to paste the rows or columns, the target area must be in original value area. <br> If more columns in the copy and paste data to Excel from top leftmost column begins to drop data .. For example, suppose you from worksheet columns a and b to copy data from another table row 1 began to paste the data into it. Excel adds A column of data in row 1, column b of the data in row 2, and so on .. <br> If you do not see the "Paste" command, make sure that you right-click the first destination cell. You can also edit "menu," find the order. If you still don't see the command, make .sure that you are using Excel2000 or later <br> Conditional Sum. <br> 1. single conditional sum: statistical C1 company construction projects with a total construction area, and place the result in the E18 cell, as long as we enter the E18 .cell formula "= SUMIF (D2: D17," C1 ", E2: E17)" that is, complete this survey. <br> Note: If the Excel function is not too familiar with, in the cells directly into the formula, we .can use the "insert function" command (or press the toolbar's "paste function" command button), select the function you need, according to the instructions to complete the formula input. <br> 2. multiple conditional sum: statistical C2 company construction .quality rating to "qualified" project with a total construction area, and place the result in the E19 cell, we use the "conditional sum" feature to implement: <br> ① "Tools → Options → Conditional Sum Wizard" command (without this .command option, you can use the "add" to add this command option), in the pop-up dialog box, press the lower band "-" button (the dialog box into a similar tool of Windows Forms floating on the desktop), .using the mouse to select D1: I17 area, and press on the right side of the window with a red arrow button (the recovery status dialog box). <br> ② Press "next" in the pop-up dialog box, press the ."add columns" to the right of the drop-down button to highlight the "building area" item, and then press the "conditions for columns, operators, and comparison values" to the right of the drop-down button, then select " .construction units, "" = "(default), the" C2 company "option, then press the" Add condition "button. Repeat the above operation, the column will be" conditions, the operator, a comparison value "set to" quality .grade, "" = "," qualified ", and press the" Add condition "button. <br> ③ twice to click" next "in the pop-up dialog box, press the lower band" - "button, use the mouse .to select the E19 cell, and press the right side of the window with a red arrow buttons. <br> ④ Press "Finish" button, at this point eligible summary results automatically and accurately displays the cell at the E19. <br> Note .: this is actually an array formula has been entered, we can also direct the E19 cells enter the formula: = SUM (IF (D2: D17 = "C2", IF (I2: I17 = "qualified", E2: E17)) .), and then hold down Ctrl + Shift keys (key!!!) and press the Enter key, you can achieve the above objective of multiple conditional sum. <br> Excel2003 function complete Handbook <br> <br> First, the function .application Foundation <br> <br> 1. functions and formulas <br> (1) what is the function <br> Excel functions that is predefined, perform a calculation, analysis and processing data task special formula. To add the sum function are .commonly used as an example, it's syntax is "SUM (number1, number2, ...)". The" SUM "as the name of the function, a function only a name, it determines the function and purpose. The function name followed .immediately by an opening parenthesis followed by a comma-separated list of content, called parameters with a closing parenthesis to end represents the function ends. <br> Parameter is a function of most complex components, it regulates the function of objects, order, or .structure. Allows the user to a cell or range for processing, such as analysis of deposit interest, determine performance ranking, calculations, trigonometric, etc. <br> The source of the function, Excel functions can be divided into the built-in functions .and extension functions. The former just start Excel, users will be able to use them; whereas the latter must be by clicking on the "Tools" menu → add-ins command to load before you can use as a built-in function. < .br> (2) what is the formula <br> Functions and formulas are differentiated and contact each other. If the formerAre Excel predefined special formula, which was designed by the user on the worksheet for calculating and processing of the formula. With the formula ."= SUM (E1: H1) * A1 + 26", for example, it should equal sign "=" start, its internal can include functions, references, operators, and constants. On the type of "SUM (E1: H1) ."is a function," A1 "is in cell a1 reference (use the stored data)," 26 "is a constant, the" * "and" + "is the arithmetic operators (addition, comparison operators, text, and reference operators .). <br> If the function you want to appear in the formula, it must have two components, one for the function name preceded by an equal sign and the other is the function itself. <br> <br> 2. the function .of parameters <br> Function right part in brackets is called a parameter, if a function you can use multiple parameters, parameters and arguments between half-width comma. Parameter can be a constant (numeric and text), logical values (such as TRUE .or FALSE), array, error values (such as # N / A), or cell references (eg E1: H1), or even another one or several functions, etc. Parameter of the type and location must meet the requirements of function syntax ., otherwise an error message is returned. <br> (1) constant <br> Constants are entered directly into the cell or the formula for the number or text, or by the name representations of numbers or text values, such as digital "2890.56 .", date" 2003-8-19 "and the text" Dawn "are constants. But the formula or calculated by the formula result is not a constant, because as long as the formula parameter is changed, it itself, or the results will .change. <br> (2) logical values <br> Logical value is rather special kind of parameter, which only TRUE (true) or FALSE (false) in two types. For example, in the formula "= IF (A3 = 0 ., "", A2/A3) "," A3 = 0 "is a can return true (true) or FALSE (false) argument of the two results. When the" A3 = 0 "to true (true) when the cell in .the formula with "0", fill in the cells A2/A3 "result". <br> (3) of the array <br> The array used to produce more than one result, or you can store in a row and column in .a group of arguments that are calculated by the formula. Excel has a constant and regional class array. The former is placed in the "" (press Ctrl + Shift + Enter key combination auto-generated), and internal numerical columns with a comma- .separated by ",", the numeric value you want to use semicolon ";" Separated. If you would like to express the 1 line of 56, 78, 89 and line 2, 76, 80, 90, should establish a 2 rows of .3 columns of constant array "{56, 78, 90, 89; 76, 80}. <br> Area array is a rectangular range of cells, the cells in a region with a formula. For example, the formula" = TREND ( .B1: B3, A1: A3) "when used as an array formula, it refers to a rectangular range of cells B1: B3", A1: A3 "is a regional array. <br> (4) error value <br> Use .the error value as parameters are primarily information functions, such as "ERROR.TYPE" function is the error value as a parameter. Its syntax is "ERROR.TYPE (error_val)", if the parameter is the # NUM!, the return value .of the "6". <br> (5) a cell reference <br> Cell reference is the function of the most common parameters, the reference is intended to identify the worksheet cell or range of cells, formula, or function and specifying the .location of the data makes it easy for them to use the data worksheet, or multiple functions use the same cell data. You can also refer to the same workbook to a different worksheet cell, or references to other workbooks. <br> According to the .formula in the cell's location is changed, the cell reference changes, we can reference consists of a relative reference, absolute, and mixed references three types. To be stored in the formula in cell F2 "= SUM (A2: E2)", for .example, when a formula from the cell to F2 F3 cell, the formula references will change to "= SUM (A3: E3)". If formula since F column down to continue replication, "row" per row, the row or the formula .1 automatically. <br> If the above formula to "= SUM ($ A $ 3: $ E $ 3)", regardless of where to copy the formula, the reference location is always the "A3: E3" area. <br .> Mixed references are "absolute column and relative row", or "absolute row and relative column" two forms. The former as "= SUM ($ A3: $ E3)", the latter as "= SUM (A $ 3: E .$ 3). "<br> The above referenced several instances of the same data in a worksheet, if you want to analyze the same workbook more data on the worksheet, use a 3-d reference. If the formula is placed in the Sheet1 .C6 cells, to refer to a worksheet Sheet2 "A1: A6" and "Sheet3 B2: B9," areas of summations, the references in the formula in the form "= SUM (Sheet2! A1: A6, B2: B9 Sheet3!) .". That is not only in a 3-d reference that contains the cell or range reference, but also added with"! "The name of the worksheet. <br> If you want to refer to data from another workbook, such as the .workbook Book1 in the sum function to absolute references Book2 workbook, the data in the formula "= SUM ([Book2] Sheet1! SAS1: SAS8, [Book2] Sheet2! SBS1: SBS9)" in the original cell references with "[Book2] .Sheet1! ". Placed in square brackets inside the workbook name with"! "Is the name of the worksheet. That is cross referenced workbook cell or range, the references object must be preceded by"! "The separator as a work table, and .then use the brackets as delimiters for the workbook. However a 3-d reference to be more restricted, for example, you cannot use array formulas, and so on. <br> Tip: described above is a reference to the Excel default, known .as "a1 reference style". If you want to calculate at the "macro" rows and columns, you must use the "R1C1 reference style". In this reference style, Excel uses the "R" and "row" and "C" .and the "column labels" method indicates that the cell location. Enable or disable the R1C1 reference style must click "Tools → Options" menu command that opens a dialog box in the "General" tab, select or clear the "settings" under " .R1C1 reference style "option. Because of this reference style is rarely used, limited to the length of this article does not make further introduction. <br> (6) nested functions <br> In addition to the above, the function can also be .nested, that is, a function is a function of parameters, such as "= IF (OR (RIGHTB (E2, 1) =" 1 ", RIGHTB (E2, 1) =" 3 ", RIGHTB (E2, 1) = ."5", RIGHTB (E2, 1) = "7", RIGHTB (E2, 1) = "9"), "male", "female") ". Which of the if function in the formula uses a nested RIGHTB function ., which returns the results as IF logical judgement. <br> (7) the name and logo <br> In order to more visually identifies the cell or range of cells, we can give them a name, resulting in a formula or function in .a direct reference. For example "B2: B46" area located at the student's physics, the formula for the average score is "= AVERAGE (B2: B46)". In B2: B46 area named after the "physical score", the formula .can become "= AVERAGE (physical score)," so that the formula becomes more intuitive. <br> To a cell or range name: select a named cell or range of cells, mouse click the formula bar at the top of the "name ."box, enter the name of a carriage return. Or you can select a named cell or range of cells, click the name of the" insert → → define "menu command in the open" definition name "dialog box, enter the name to .determine. If you want to delete has named ranges, you can follow the same method to open the "define name" dialog box, select the name you want to delete to delete it. <br> Since most of the Excel worksheet with the " .column labels ". For example, a result table of the first row usually with the" serial number "," name "and" math "and" physical "and the" column labels "(also called fields), if you click the" .Tools → Options "menu command in the open dialog box, click the" recalculate "tab, select the option" workbook "option group logo" to accept the formula, the formula is "option can be a direct reference to the" column labels " .. For example "B2: B46" area housed students' physical, B1 cell have entered the "physical", the average score for physical formulas that can be written as "= AVERAGE (physical)". <br> To note is that creating .a good name can be any sheet references, and references do not need to be added in front of the name of the sheet name (and this is the main advantage of using the name), so the name reference is an absolute reference. But the .formula refers to the "column labels" time limit, it can only be used in the current data column below the reference, cannot span worksheet references, but refer to the "column labels" formula under certain conditions, can be replicated. In essence, .the name and logo are all cell references. Because they are not the text, use the name and logo will not be able to add a quotation mark. <br> <br> 3. the function input method <br> On the Excel formula ., the function is one of the main components, so the formula input can be attributed to the function input. <br> (1) the "insert function" dialog box <br> The "insert function" dialog enter a formula in Excel is .an important tool for the formula "= SUM (Sheet2! A1: A6, B2: B9 Sheet3!)", for example, Excel input process of the formula is: <br> First select the hold calculation result (ie the need to apply .a formula) of cells, click the formula bar (or toolbar) in the "fx" button, the formula "=" appears in the cell and the formula bar, and then open the "insert function" dialog box, select the "find ."function" list "function SUM. If you do not need to be inside of a function, you can open" or select a category "drop-down list to select it. Finally click" OK "button to open" function parameters "dialog .box. <br> The SUM function, it can be used from the beginning until the number1 number30 total 30 parameters. On the above formulas, the first should put the cursor in the dialog box "number1" box, click workbook "Sheet2!" .The worksheet tab, "Sheet2!" You can automatically enter it, then drag the mouse to select the area you want to reference. Then use the mouse to click the dialog box "number2" box, click workbook "Sheet3!" The worksheet tab ., the name "Sheet3!" You can automatically enter it, and then press the same method to select the cell range reference. <br> The above method of the biggest advantage is the reference area is very accurate, especially 3-d references not .prone to worksheet or workbook name entry error. <br> (2) Edit bar input <br> If you want to borrow a ready-made formulas, or enter some nested relations complex formula, using the formula bar, enter moreShortcut. <br .> First select the store the results of a; the Edit Bar mouse click Excel, according to the formula of the parts in the order entry, enter a formula, click the Edit Bar "input" (ie "√") button (or return .). <br> You can also manually enter the take method reference described above, with the formula "= SUM (Sheet2! A1: A6, B2: B9 Sheet3!)", for example, you can be the first in the formula bar ., enter "= SUM ()," and then move the cursor to the insert brackets, then by the method described above to enter the formula references. But the comma between the separate references must be entered manually, but cannot "insert function" dialog .box that automatically added. <br>.