Training for Excel.
One. Processing of data
1. Current date: Ctrl +;
2. Existing text: alt+↓
3. Text after automatic appearance: Format Cells + custom:general "Person"
4. The amount is displayed in different units: Excel represents the amount, comma is the number of hidden digits: #, "K"//#,##,, "M"
5. Regular numbers: Left-drag, right-drag
6. Numeric operations: Paste with numbers. Can do a quick operation. Value, do not change format.
7. Special's Choice paste Link.
8. Transpose in special paste, transpose.
9. Format Related: Format brush, double-click the format brush, you can always brush, end can use "ESC" end.
Both. Function
1. Accurate query, VLOOKUP, accurate is write 0, column ordinal (automatic change, three functions):
A. Regularity change: Formula changes in the right copy process (column, columns): =vlookup (A3
B. Regular changes: rows (row). The row is a number and the column is a letter.
C. Note that the cell is locked: $-F4. $ is used to lock the. F4 cycle.
D. Note the format of the cell: Fill without formating.
E. Position (auto-positioning): Match//(match value, match range, 0 = "exact positioning", 1 = "Approximate (find small value)" < write-only value, interval minimum; the whole column must be in ascending order >). column, header row, select only one row. The VLOOKUP column must be the same as the match column.
F. The current range of VLOOKUP can only be selected from the first column selected.
H. Limitations of VLOOKUP: The query column must be the starting column.
2. If function
A. IF (and (conditions, conditions)). And and or are functions, not operators.
3. Calculation type
A. Sum function.
B. Sumproduct, multi-column totals (multiply and add)
C. Non-hidden part summation. SUBTotal.
Ctrl+9 is a hidden row (plus shift to expand it), Ctrl+0 is a hidden column.
D. Sum by specific conditions. Sumifs.
Conditions are written in the formula and need to be hit "", double quotes.
Judging symbol:>,<,=,<> (not equal to)
E. sumifs, its own default condition is and, repeating is the condition range. If it is or two sums.
F. Average (averageifs), calculation (COUNTIFS)
Note: Count only counts values for value. CountA is a function that can be counted (valid data, non-empty), part of the data type.
G. Top->large (<>,number).
H. Index function. There is no requirement for the scope of the query.
Query scope (common requirement): the header row must not be included.
INDEX Case: =index ($A $: $J $20,match ($O $33, $D $ $: $D $20,0), MATCH (p$32, $A $: $J $1,0)) locates the row first, and locates the column.
M. IFERROR, judging whether the result of the function is wrong.
Day 2
Look at the table to no after you cross the table.
The name can represent the address. Define, in Namebox is used in the formula. F3 is the paste name shortcut key.
Update name: In the formula bar, go in to change Namemanager.
Quick copy:
A. Select (without the mouse), direct input, Ctrl+enter is the whole block area input.
B. Write it first, in the selection, F2, in the Ctrl+enter is the whole block of input.
C. F2 refers to activating the cell (and double-clicking is the same). refers to editing.
D. When selected, F5 Special, you can select blanks.
E. Awesome: F5, Ctrl+enter. You can add more formulas.
F. Custom sorting. Sort Button! Own custom. Custom cities You can also drag the list loop like this. Input and Import/
G. Advanced filtering: (more complex conditions) first to construct a conditional range (title + specific condition), and (peers), or (different lines)
Pivot table:
A. Four areas: Row/column VALUE report Filter
B. Classification adjustment: GROUP Field//Expand field//Collapse field//subtotals//can act on different data types.
C. Group selection: Personalized grouping. Text Grouping!
D. Calculation field add Result: 1. Use the formula to get the result of the calculation. (Add results within the report). In field, Items, sets this control. The calculated field. A logical class can be written as a formula.
You can create, modify, and delete such calculation methods.
E. Specific calculation results.
Type of result: percent (3).
1> percentage. (Right-click Show as) values are displayed. First, drag the field you want to calculate into the report/use a specific option in the tool to get the result.
2> growth ratio. Value is displayed by default by column. //
3> cumulative ratio.
Chain/year
Show results outside of sum, drag in, right-click to select Function.
Report Filter:
Report Layout:default:Compact form//
Excel Training Notes
