常用函数举例.doc

6、COUNTIF函数函数名称：COUNTIF主要功能：统计某个单元格区域中符合指定条件的单元格数目。使用格式：COUNTIF(Range,Criteria)参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表达式。应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,=80)，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。特别提醒：允许引用的单元格区域中有空白单元格出现。13、INDEX函数函数名称：INDEX主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。使用格式：INDEX(array,row_num,column_num)参数说明：Array代表单元格区域或数组常量；Row_num表示指定的行序号（如果省略row_num，则必须有 column_num）；Column_num表示指定的列序号（如果省略column_num，则必须有 row_num）。应用举例：如图3所示，在F8单元格中输入公式：=INDEX(A1:D11,4,3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格（即C4）中的内容。特别提醒：此处的行序号参数（row_num）和列序号参数（column_num）是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。14、INT函数函数名称：INT主要功能：将数值向下取整为最接近的整数。使用格式：INT(number)参数说明：number表示需要取整的数值或包含数值的引用单元格。应用举例：输入公式：=INT(18.89)，确认后显示出18。特别提醒：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为-19。15、ISERROR函数函数名称：ISERROR主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。使用格式：ISERROR(value)参数说明：Value表示需要测试的值或表达式。应用举例：输入公式：=ISERROR(A35/B35)，确认以后，如果B35单元格为空或“0”，则A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。特别提醒：此函数通常与IF函数配套使用，如果将上述公式修改为：=IF(ISERROR(A35/B35),A35/B35)，如果B35为空或“0”，则相应的单元格显示为空，反之显示A35/B35的结果。17、LEN函数函数名称：LEN主要功能：统计文本字符串中字符数目。使用格式：LEN(text)参数说明：text表示要统计的文本字符串。应用举例：假定A41单元格中保存了“我今年28岁”的字符串，我们在C40单元格中输入公式：=LEN(A40)，确认后即显示出统计结果“6”。特别提醒：LEN要统计时，无论中全角字符，还是半角字符，每个字符均计为“1”；与之相对应的一个函数LENB，在统计时半角字符计为“1”，全角字符计为“2”。19、MAX函数函数名称：MAX主要功能：求出一组数中的最大值。使用格式：MAX(number1,number2)参数说明：number1,number2代表需要求最大值的数值或引用单元格（区域），参数不超过30个。应用举例：输入公式：=MAX(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最大值。特别提醒：如果参数中有文本或逻辑值，则忽略。20、MID函数函数名称：MID主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：MID(text,start_num,num_chars)参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。应用举例：假定A47单元格中保存了“我喜欢天极网”的字符串，我们在C47单元格中输入公式：=MID(A47,4,3)，确认后即显示出“天极网”的字符。特别提醒：公式中各参数间，要用英文状态下的逗号“,”隔开。21、MIN函数函数名称：MIN主要功能：求出一组数中的最小值。使用格式：MIN(number1,number2)参数说明：number1,number2代表需要求最小值的数值或引用单元格（区域），参数不超过30个。应用举例：输入公式：=MIN(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最小值。特别提醒：如果参数中有文本或逻辑值，则忽略。24、NOW函数函数名称：NOW主要功能：给出当前系统日期和时间。使用格式：NOW()参数说明：该函数不需要参数。应用举例：输入公式：=NOW()，确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。特别提醒：显示出来的日期和时间格式，可以通过单元格格式进行重新设置。25、OR函数函数名称：OR主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。使用格式：OR(logical1,logical2, .)参数说明：Logical1,Logical2,Logical3：表示待测试的条件值或表达式，最多这30个。应用举例：在C62单元格输入公式：=OR(A62=60,B62=60)，确认。如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数值都小于60。特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。26、RANK函数函数名称：RANK主要功能：返回某一数值在一列数值中的相对于其他数值的排位。使用格式：RANK（Number,ref,order）参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。特别提醒：在上述公式中，我们让Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式（增加了一个“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学语文成绩的排名统计。30、SUMIF函数函数名称：SUMIF主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF（Range,Criteria,Sum_Range）参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：如图7所示，在D64单元格中输入公式：=SUMIF(C2:C63,男,D2:D63)，确认后即可求出“男”生的语文成绩和。特别提醒：如果把上述公式修改为：=SUMIF(C2:C63,女,D2:D63)，即可求出“女”生的语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（男、女）中。VALUE函数函数名称：VALUE主要功能：将一个代表数值的文本型字符串转换为数值型。使用格式：VALUE(text)参数说明：text代表需要转换文本型字符串数值。应用举例：如果B74单元格中是通过LEFT等函数截取的文本型字符串，我们在C74单元格中输入公式：=VALUE(B74)，确认后，即可将其转换为数值型。特别提醒：如果文本型数值不经过上述转换，在用函数处理这些数值时，常常返回错误34、VLOOKUP函数函数名称：VLOOKUP主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。应用举例：参见图7，我们在D65单元格中输入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，确认后，只要在B65单元格中输入一个学生的姓名（如丁48），D65单元格中即刻显示出该学生的语言成绩。特别提醒：Lookup_value参见必须在Table_array区域的首列中；如果忽略Range_lookup参数，则Table_array的首列必须进行排序；在此函数的向导中，有关Range_lookup参数的用法是错误的。5计数函数COUNT语法：COUNT（value1，value2，）。参数：value1，value2是包含或引用各类数据的130个参数。注意：COUNT函数计数时数字、日期或文本表示的数字会参与计数，错误值或其他无法转换成数字的文字被忽略。如果参数是一个数组或引用，那么只有数组或引用中的数字参与计数；其中的空白单元格、逻辑值、文字或错误值均被忽略。应用实例：及格率统计假如C1:G42存放着42名学生的考试成绩，在一个空白单元格内输入公式“=COUNTIF（C1:C42，=60）/COUNTA（C1:C42）”回车，即可计算出该列成绩的及格率（即分数为60及以上的人数占总人数的百分比）12条件计数函数COUNTIF语法：COUNTIF（range，criteria）。参数：range为需要统计的符合条件的单元格区域；criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本（如36、160和男等）。条件中的数字可以直接写入，表达式和文本必须加引号。应用实例：男女职工人数统计假设 A1:A58区域内存放着员工的性别，则公式“=COUNTIF（A1:A58，女）”统计其中的女职工数量，“=COUNTIF（A1:A58，男）”统计其中的男职工数量。COUNTIF 函数还可以统计优秀或及格成绩的数量，假如C1：G42存放着42名学生的考试成绩，则公式“=COUNTIF（C2：G2，=85）”可以计算出其中高于等于85分的成绩数目。如将公式改为“=COUNTIF（C2：G2，=60）”，则可以计算出及格分数的个数。1 统计一列数据（a列）中出现相同次数的数据的个数=SUMPRODUCT(-(COUNTIF($A$1:$A$10,$A$1:$A$10)=COLUMN(A:A)/COLUMN(A:A)输入到b1，向右拖拉，依次能够得到出现1、2、3次等等的数据统计。这里，用COLUMN(A:A)来控制统计的次数，以便实现向右拖拉时可以自动增加。2 统计一列数据中数字出现的频率比如1234，和4567，其中在出现一次一栏中显示123567等几个数字。需借助辅助列，在e:l行里面输入公式=MID($A2,COLUMN(A:A),1)拖拉到所有区域，最大可以统计8位数据。然后下面为主公式。=SUMPRODUCT(LARGE(IF(FREQUENCY(LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10),LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10)=0,0,LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10),ROW(1:10)*10(ROW(1:10)-1)上面公式输入到出现一次的栏里面，然后向右拖拉，一次可以显示出现两次、三次，等等3 将“001”“001-107”数字前后都加上2，变成“003-109”=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,000)&-&TEXT(RIGHT(A1,3)+2,000)如果有8位数字，则需要进行为数判断，使用find()函数4 excel 转置自动填充的问题1) A列为a、b、c，b列为1、2、3，c列为11、22、33，d列为111、222、333，e列为aa、bb、cc效果为A111111Aa依此类推。=OFFSET(Sheet2!$A$1,INT(ROW()-1)/5),MOD(ROW()-1,5)然后向下拖拉2) A列为a、b、c、d、e、f、g、h、i，b列为1、2、3、4、5、6、7、8、9效果为：abc123def456ghi789公式=OFFSET(Sheet3!$A$1,COLUMN(A:A)-1+3*INT(ROW(1:1)-1)/2),MOD(ROW(1:1)-1),2)先向右拖拉3列，再向下拖拉5 excel计算分别满足多个条件的单元格数例一：有四列数据，如A B C D，A与B是对应的，C与D是对应的要求当C=A时，D=B列中“X”的个数 问题补充：B列有“X”，有的没有，只用统计X的个数！不是显示“X” 比如A B C D1 X 12 X 23 31 42 X 51 X 2 X3 X现在要D中显示“1”对应的“X”有多少个我的回答: D1 =COUNTIFS(B:B,=X,A:A,C1)此公式的缺点是只能用在office2007以上的版本。别人的回答:D1=SUMPRODUCT($A$1:$A$100=C1)*($B$1:$B$100=X)例二：计算出A列姓名如张三，在B列状态如所有显示“完成”的行项目数.用countifs。A2为张三。C2=COUNTIFS(A:A,A2,B:B,完成)用sumproduct函数：=SUMPRODUCT(a2:a100=张三)*(b2:b100=完成)解析：countifs是多条件统计，第一个参数是范围一，第二个参数是条件一，第三个参数是范围二，第四个参数是条件二，一次类推，可以多条件选择统计。此函数在2003版本中没有。Sumproduct函数是返回对应的数组元素的成绩的和，即1，2，34，5，6，结果是1*4+2*5+3*6=32。这里用来多条件统计的原理是，把每个条件作为乘积的因子，函数用数组来处理，和对应区域中的每个单元格对比，如果条件成立时，返回true，如果不成立返回false，这样得到一对数组false，true，falsetrue，true，false，接着进行计算false*true+ truetrue+false*false=0+1+0,因为两个逻辑函数相乘，只有当两个都是true的时候乘积为1，否则都为0，依此计算出来的结果，就是这些条件都满足的单元格的行数。因此上述两行的数组元素不但要维数相等，而且一定要对应。即选择范围是要在行间对应，否则求出的值就会出错。如条件一从A1到A10，条件二从B2到B11，虽然也是维数相同的数组，但是得数不正确，因为错行，它会把A1和B2相比较。上述公式因为是直接定义的数组，所以输入公式时不用单独应用数组公式。6 SumifSumif是对符合条件地单元格求和，第一个参数为需要符合条件的单元格区域范围，第二个参数是条件，第三个参数是实际需要计算的单元格区域，按与第一参数相等的区域进行计算。这里需要注意的是，参数一满足条件后会返回区域里面单元格所在的行数，求和时，按照参数三里面区域对应行数进行。如参数一区域选的A3:A10，参数三是B4到B11，假如a4，a5符合条件，那么将返回的行数是2、3，而不是4、5，然后，将对b5、b6进行求和，而不是b4、b5。=SUMIF(B:B,条件,A:A)本公式的意义是，当b列中有单元格等于“条件”两个字时，计算对应a列单元格的和。其中条件参数可以使用通配符，*，？，实现模糊查找。7 隐藏函数1) Numberstring(value,type)，阿拉伯数字转换成中文数字，type为1时，转换为小写中文数字（带千百万等等），2转换为大写中文数字（带千百万等），3转换为小写中文数字，不含千百万。2) Datedif(start_date,end_date,unit)，计算时间差，第一参数为开始时间，第二参数为结束时间，unit为返回的类型，”y”、”m”、”d”分别为年、月、日，这些参数返回的是总体差值，比如2008年1月1日到2009年1月1日，返回年数为整年数1年，返回天数是整个期间的总天数366天。”yd”、”md”这两个参数分别返回除去年或年月的天数，既不包括其中的年数或年月数所得到的天数。”ym”指出去年数的整月份数，多