excel公式（Excel公式）.docVIP

excel公式（Excel公式） 1, sex =IF (MOD (LEFT (RIGHT (C3 (LEN (C3) =18) +1)), 2), male and female) 2, the date of birth (=IF LEN (C3) =18, MID (C3,7,4) MID (C3,11,2) / / MID (C3,13,2), 19 MID (C3,7,2) MID (C3,9,2) / / MID (C3,11,2)) 3, age (TODAY), =YEARFRAC (E3) 4, length of =CONCATENATE (DATEDIF (G3, TODAY), (Y), year, DATEDIF (G3, TODAY), (YM), month) 5 months, =IF (DAY (NOW) (=DAY) (G3, 0), -1 (+) YEAR ((NOW)) -YEAR (G3)) *12+MONTH (NOW) (-MONTH) (G3) 6, the deadline for service =CONCATENATE (DATEDIF (G3, J3, y), year, DATEDIF (G3, J3, YM), month) 7, the deadline for service month number =IF (DAY (J3) =DAY (G3), 0 (-1) + YEAR (J3) -YEAR (G3)) *12+MONTH (J3) -MONTH (G3) 8, the labor contract expires time: =DATE (YEAR (a day) + MONTH (signed, signed on), DAY (a day) -1) 9, the probation period 7 days in advance to remind: =IF (DATEDIF (TODAY), the trial (the expiration time, d =7 ), the probation period is coming to an end, ) 10 men and women, statistics = male COUNTIF (D2:D600, male ) 11, to cut the whole =CEILING (need to cut whole number, cut the whole multiples) 12, the personal income tax (=IF B2=800,0, IF (B2=1500, *0.05, IF (B2-800) (B2=2000700*0.05+ (B2-1500) *0.08700*0.05+500*0.08+ (B2-2000) *0.2))) 13, the conditions for =SUMIF ($C$3:$C$12, sales, $F$3:$F$12) $C$3:$C$12 to provide the logical judgment range according to the sales as the judgment condition that only the statistics department in the $C$3:$C$12 region as cell sales, $F$3:$F$12 is the summation of the actual range. 14, four to five homes (number = ROUND, num_digits) Number is going for the four to five homes in number; num_digits is the hope to get the number of the number of digits after the decimal point. The 15 generation =IF, wages (MOD (ROW), =0 (3), IF (MOD (ROW), =1 (3), T$1 INDEX (payroll, payroll!! ($A:$AB, INT (ROW) (+4), COLUMN (/3))))) The A AB salary list number 16, for the regional type in the number of {=SUM (1/COUNTIF (B2:B25, B2:B25))} In the duplicate names or duplicate name