公式大全(国外英语资料).docVIP

公式大全(国外英语资料) Identity cards repeat: =IF (COUNTIF (G:G, G3, *), 1, duplicate, unique.) Age: =DATEDIF (TEXT (MID) (C1,7,6+ (LEN (C1) =18) *2), #-00-00) *1, 2010-7-1, y Men and women: =IF (ISBLANK (C60), , IF (C60), IF (MOD (RIGHT (LEFT (C60,17)), 2), male, female), ()) Year of birth: =MID (A1,7,8) Minimum assurance test: =index (v:v, match (D2, v:v, 0)) Queries and reference functions for Excel function applications Before introducing the query and the reference function, lets look at the knowledge about the reference. 1, the role of reference The role of referencing in Excel is to identify the cell or cell area on the worksheet and indicate 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 the same cell value in multiple formulas. You can also refer to the same workbook, cells from different worksheets, cells from different workbooks, and even data from other applications. 2, the meaning of reference For reference, you need to know the following meanings: External references -- references to cells in different workbooks are referred to as external references. Remote reference - referencing data in other programs is referred to as remote reference. Relative references - when creating formulas, references to cells or cell regions are usually relative to the cells that contain formulas. Absolute reference - if you do not want the Excel to adjust the reference when copying the formula, use absolute reference. That is, adding dollar symbols such as $C$1. 3. The method of quotation There are two ways to refer to a reference, namely, A1 and R1C1 reference styles. (1) reference to style one (default) --A1 The reference style of A1 is the default reference type of Excel. This type refers to the alphabet flag column (from A to IV, a total of 256 columns) and the number sign line (from 1 to 65536). These letters and numbers are called row and column headers. To reference cells, enter column