电子表格公式大全（The spreadsheet formula is complete）.docVIP

电子表格公式大全（The spreadsheet formula is complete） Duplicate id: = IF (COUNTIF (G: G, G3 *) 1, repeat, unique) Age: = DATEDIF (TEXT (mids (C1, 7, 6 + (LEN (C1) = 18) * 2), # 00-00 -) * 1, 2010-7-1, y) Men and women: = the IF (ISBLANK (C60), , the IF (C60 , the IF (MOD (RIGHT (LEFT) (C60, 17), 2), male, female),)) Date of birth: = MID (A1, 7, 8) Low protection detection: = index (v: v, match (D2, v: v, 0)) The query and reference function for Excel functions Before introducing the query and referencing functions, lets take a look at the reference. 1. The role of reference The role of reference in Excel is to identify the cell or cell area on the worksheet and specify the location of the data used in the formula. By reference, you can use data from different parts of the worksheet in the formula, or use values of the same cell in multiple formulas. You can also refer to the data in cells of the same workbook, cells in different workbooks, or even other applications. 2. The meaning of reference The following are the implications of the reference: External references - the references to cells in different workbooks are called external references. Remote references - references to data in other programs are referred to as remote references. Relative reference - when creating a formula, the reference of the cell or cell area is usually relative to the cell that contains the formula. Absolute reference - if you do not want Excel to adjust the reference when copying the formula, use an absolute reference. Enter the dollar sign, such as $C $1. 3. Means of reference There are two ways to refer to references, namely A1 and R1C1 reference styles. (1) reference style 1 (default) - A1 The reference style of A1 is the default reference type for Excel. This type refers to the letter flag column (from A to IV, A total of 256 columns) and A number flag line (from 1 to 65536). These letters and Numbers are called rows and column headings. If you want to refer to a cell, type the column