EXCEL公式与函数教学提纲.doc

EXCEL公式与函数教学提纲目录、预备知识1一、字符处理函数的应用3二、数学与统计函数5三、条件函数的应用9四、逻辑函数的应用：AND、OR、NOT函数14五、多条件统计函数的应用16六、排序函数的应用20、预备知识打印设置缩放打印问题页边距调整如何每页打印标题删除空格清除格式排序与筛选公式与函数的应用公式输入小技巧：(本人总结)1、运算结果填入的单元格，要将格式设置为常规或数字，如果是文本，则会将公式本身（而不是运算结果）填入单元格。2、复制粘贴公式，不要连同=号一起复制粘贴，只复制公式，选中运算单元格，将光标定位公式输入框，键入=号，再粘贴公式；然后点击确认。相对引用与绝对引用Excel函数应用之查询与引用函数1、引用的作用在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。2、引用的含义关于引用需要了解如下几种情况的含义：外部引用-不同工作簿中的单元格的引用称为外部引用。远程引用-引用其它程序中的数据称为远程引用。相对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。如C 1 - 列行绝对引用-如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。3、引用的表示方法关于引用有两种表示的方法，即A1和R1C1引用样式。（1）引用样式（默认）-A1A1的引用样式是Excel的默认引用类型。这种类型引用字母标志列（从A到IV，共256列）和数字标志行（从1到65536）。这些字母和数字被称为行和列标题。如果要引用单元格，请顺序输入列字母和行数字。例如，C25引用了列C和行25交叉处的单元格。如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用，如A20:C35。在了解了引用的概念后，我们来看看Excel提供的查询与引用函数。查询与引用函数可以用来在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用。Excel中一共提供了ADDRESS、AREAS、CHOOSE、COLUMN、COLUMNS、HLOOKUP、HYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、ROW、ROWS、TRANSPOSE、VLOOKUP16个查询与引用函数。下面，笔者将分组介绍一下这些函数的使用方法及简单应用。学习函数前，介绍 数据类型：文本（字符）型、数值型、日期型、逻辑型=sum（a3:a9）=60+INT(RAND()*39)BA,取介于AB之间的随机整数: A+Int(rand()*(B-A)一、字符处理函数的应用1.LEFT 也应用于：LEFTBLEFT 基于所指定的字符数返回文本字符串中的第一个或前几个字符。LEFTB 此函数用于双字节字符。语法LEFT(text,num_chars)示例 (LEFT)如果您将示例复制到空白工作表中，可能会更易于理解该示例。示例 (LEFTB)LEFTB( ,4) 等于“ ”2.RIGHT请参阅也应用于：RIGHTBRIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。RIGHTB 根据所指定的字符数返回文本字符串中最后一个或多个字符。此函数用于双字节字符。语法RIGHT(text,num_chars)RIGHTB(text,num_bytes)Text 是包含要提取字符的文本字符串。Num_chars 指定希望 RIGHT 提取的字符数。Num_bytes 指定希望 RIGHTB 根据字节所提取的字符数。说明Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。 如果忽略 num_chars，则假定其为 1。 示例 (RIGHT)3.MID请参阅也应用于：MIDBMID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。此函数用于双字节字符。语法MID(text,start_num,num_chars)MIDB(text,start_num,num_bytes)Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars 指定希望 MID 从文本中返回字符的个数。Num_bytes 指定希望 MIDB 从文本中返回字符的个数（按字节）。说明如果 start_num 大于文本长度，则 MID 返回空文本 ()。 如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。 如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。 如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。如果 num_bytes 是负数，则 MIDB 返回错误值 #VALUE!。=MIDB(,1,6) 等于“”4.CONCATENATE 将几个文本字符串合并为一个文本字符串。concatenate英 knktineit 美 knkt()net vt. 把 (一系列事件、事情等)联系起来 adj. 连接的，联系在一起的 语法：CONCATENATE (text1,text2,.)Text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。说明：也可以用 &（和号）运算符代替函数 CONCATENATE 实现文本项的合并。示例：如果您将示例复制到空白工作表中，可能会更易于理解该示例。操作方法=CONCATENATE(MID(G3,7,4), 年, MID(G3,11,2), 月, MID(G3,13,2), 日)等效：字符连接符 & 的应用=MID(G2,7,4)&年&MID(G2,11,2)&月&MID(G2,13,2)&日5.SUBSTITUTE请参阅在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数 REPLACE。语法SUBSTITUTE(text,old_text,new_text,instance_num)Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。Old_text 为需要替换的旧文本。New_text 用于替换 old_text 的文本。Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。示例如果您将示例复制到空白工作表中，可能会更易于理解该示例。二、数学与统计函数1.rand() 返回01之间的随机数=60+INT(RAND()*39)BA,取介于AB之间的随机整数: A+Int(rand()*(B-A)2.MOD 返回两数相除的余数。结果的正负号与除数相同。语法MOD(number,divisor)Number 为被除数。Divisor 为除数。说明如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!。函数 MOD 可以借用函数 INT 来表示:MOD(n, d) = n - d*INT(n/d)可以利用 if 函数、mod函数、mid函数，从身份证号中提取性别信息=IF(MOD(MID(D2,17,1),2)=1,男,女)3.abs()返回数字的绝对值。绝对值没有符号。语法: ABS(number)Number 需要计算其绝对值的实数。4.SQRT返回正平方根。语法:SQRT(number)Number 要计算平方根的数。说明:如果参数 Number 为负值，函数 SQRT 返回错误值 #NUM!。5. CEILING将参数 Number 向上舍入（沿绝对值增大的方向）为最接近的 significance 的倍数。例如，如果您不愿意使用像“分”这样的零钱，而所要购买的商品价格为 $4.42，可以用公式 =CEILING(4.42,0.1) 将价格向上舍入为以“角”表示。语法:CEILING(number,significance)Number 要四舍五入的数值。Significance 是需要四舍五入的乘数。说明如果参数为非数值型，CEILING 返回错误值 #VALUE!。 无论数字符号如何，都按远离 0 的方向向上舍入。如果数字已经为 Significance 的倍数，则不进行舍入。 如果 Number 和 Significance 符号不同，CEILING 返回错误值 #NUM!。 123456AB公式说明（结果）=CEILING(2.5, 1)将 2.5 向上舍入到最接近的 1 的倍数 (3)=CEILING(-2.5, -2)将 -2.5 向上舍入到最接近的 -2 的倍数 (-4)=CEILING(-2.5, 2)返回错误值，因为 -2.5 和 2 的符号不同 (#NUM!)=CEILING(1.5, 0.1)将 1.5 向上舍入到最接近的 0.1 的倍数 (1.5)=CEILING(0.234, 0.01)将 0.234 向上舍入到最接近的 0.01 的倍数 (0.24)6. FLOOR将参数 Number 沿绝对值减小的方向向下舍入，使其等于最接近的 significance 的倍数。语法:FLOOR(number,significance)Number 所要四舍五入的数值。Significance 基数。说明如果任一参数为非数值参数，则 FLOOR 将返回错误值 #VALUE!。 如果 number 和 significance 符号相反，则函数 FLOOR 将返回错误值 #NUM!。 不论 number 的正负号如何，舍入时参数的绝对值都将减小。如果 number 恰好是 significance 的倍数，则无需进行任何舍入处理。 示例如果您将示例复制到空白工作表中，可能会更易于理解该示例。 123456AB公式说明（结果）=FLOOR(2.5, 1)将 2.5 沿绝对值减小的方向向下舍入，使其等于最接近的 1 的倍数 (2)=FLOOR(-2.5, -2)将 -2.5 沿绝对值减小的方向向下舍入，使其等于最接近的 -2 的倍数 (-2)=FLOOR(-2.5, 2)返回错误值，因为 -2.5 和 2 的符号不同 (#NUM!)=FLOOR(1.5, 0.1)将 1.5 沿绝对值减小的方向向下舍入，使其等于最接近的 0.1 的倍数 (1.5)=FLOOR(0.234, 0.01)将 0.234 沿绝对值减小的方向向下舍入，使其等于最接近的 0.01 的倍数 (0.23)7. ROUND返回某个数字按指定位数取整后的数字。语法:ROUND(number,num_digits)Number 需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。说明如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。 12345AB公式说明（结果）=ROUND(2.15, 1)将 2.15 四舍五入到一个小数位 (2.2)=ROUND(2.149, 1)将 2.149 四舍五入到一个小数位 (2.1)=ROUND(-1.475, 2)将 -1.475 四舍五入到两小数位 (-1.48)=ROUND(21.5, -1)将 21.5 四舍五入到小数点左侧一位 (20)ROUNDDOWN靠近零值，向下（绝对值减小的方向）舍入数字。语法:ROUNDDOWN(number,num_digits)Number 为需要向下舍入的任意实数。Num_digits 四舍五入后的数字的位数。说明函数 ROUNDDOWN 和函数 ROUND 功能相似，不同之处在于函数 ROUNDDOWN 总是向下舍入数字。 如果 num_digits 大于 0，则向下舍入到指定的小数位。 如果 num_digits 等于 0，则向下舍入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧向下进行舍入。 123456AB公式说明（结果）=ROUNDDOWN(3.2, 0)将 3.2 向下舍入，小数位为 0 (3)=ROUNDDOWN(76.9,0)将 76.9 向下舍入，小数位为 0 (76)=ROUNDDOWN(3.14159, 3)将 3.14159 向下舍入，保留三位小数 (3.141)=ROUNDDOWN(-3.14159, 1)将 -3.14159 向下舍入，保留一位小数 (-3.1)=ROUNDDOWN(31415.92654, -2)将 31415.92654 向下舍入到小数点左侧两位 (31400)ROUNDUP远离零值，向上舍入数字。语法:ROUNDUP(number,num_digits)Number 为需要向上舍入的任意实数。Num_digits 四舍五入后的数字的位数。说明函数 ROUNDUP 和函数 ROUND 功能相似，不同之处在于函数 ROUNDUP 总是向上舍入数字。 如果 num_digits 大于 0，则向上舍入到指定的小数位。如果 num_digits 等于 0，则向上舍入到最接近的整数。如果 num_digits 小于 0，则在小数点左侧向上进行舍入。 123456AB公式说明（结果）=ROUNDUP(3.2,0)将 3.2 向上舍入，小数位为 0 (4)=ROUNDUP(76.9,0)将 76.9 向上舍入，小数位为 0 (77)=ROUNDUP(3.14159, 3)将 3.14159 向上舍入，保留三位小数 (3.142)=ROUNDUP(-3.14159, 1)将 -3.14159 向上舍入，保留一位小数 (-3.2)=ROUNDUP(31415.92654, -2)将 31415.92654向上舍入到小数点左侧两位 (31500)8. COUNT返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。语法:COUNT(value1,value2,.)Value1, value2, . 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。说明函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数 COUNTA。 =COUNT(A2:A8)计算上列数据中包含数字的单元格的个数 (3)=COUNT(A5:A8)计算上列数据的最后 4 行中包含数字的单元格的个数 (2)=COUNT(A2:A8,2)计算上列数据中包含数字的单元格以及包含数值 2 的单元格的个数 (4)9.AVERAGE返回参数的平均值（算术平均值）。语法:AVERAGE(number1,number2,.)Number1, number2, . 为需要计算平均值的 1 到 30 个参数。说明参数可以是数字，或者是包含数字的名称、数组或引用。 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。当对单元格中的数值求平均值时，应牢记空白单元格与含零值单元格的区别，尤其在“选项”对话框中的“视图”选项卡上已经清除了“零值”复选框的条件下，空白单元格不计算在内，但计算零值。若要查看“选项”对话框，单击“工具”菜单中的“选项”。公式说明（结果）=AVERAGE(A2:A6)上面数字的平均值 (11)=AVERAGE(A2:A6, 5)上面数字与 5 的平均值 (10)10. SUM返回某一单元格区域中所有数字之和。语法:SUM(number1,number2, .)Number1, number2, . 为 1 到 30 个需要求和的参数。说明直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。 如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。请参阅下面的示例三。 如果参数为错误值或为不能转换成数字的文本，将会导致错误。 123456A数据-515305TRUE公式说明（结果）=SUM(3, 2)将 3 和 2 相加 (5)=SUM(5, 15, TRUE)将5、15 和 1 相加，因为文本值被转换为数字，逻辑值 TRUE 被转换成数字 1 (21)=SUM(A2:A4)将此列中前三个数相加 (40)=SUM(A2:A4, 15)将此列中前三个数之和与 15 相加 (55)=SUM(A5,A6, 2)将上面最后两行中的值之和与 2 相加。因为引用非数值的值不被转换，故忽略上列中的数值 (2)三、条件函数的应用1.IF函数的应用IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。它的语法：IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符。Value_if_true显示在logical_test为TRUE时返回的值，Value_if_true也可以是其他公式。Value_if_falselogical_test为FALSE时返回的值。Value_if_false也可以是其他公式。简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。IF函数可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。Excel还提供了可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。=IF(MOD(MID(G2,17,1),2)=1,男,女)=IF(G360,IF(D390,优秀,合格),不合格)IF()在排序方面的应用在M2单元格直接输入1，在M3单元格输入公式: =IF(J3=J2,M2,M2+1) 接着把鼠标指针指向M3单元格右下角，当鼠标指针变成黑色实线加号时，按住左键向下拖动将公式向下复制到该字段尾后放手(如图)。=IF(J3=J2,M2,M2+1)语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND(F1190)，实际上可以仅写F1190），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。2. Countif 函数的应用 根据条件计算值在了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。语法形式为:COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32、apples。1、成绩表教学测试示例:条件统计=COUNTIF(E2:E43,300) 统计分数高于300分的同学数;=COUNTIF(C2:C43,=女) 统计性别为女的人数;2、销售业绩表销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）图9原始数据表按销售人员汇总表如图10所示图10销售人员汇总表如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。（1）订单数-用COUNTIF计算销售人员的订单数。以销售人员ANNIE的订单数公式为例。公式：=COUNTIF($C$2:$C$13,A17)语法解释为计算单元格A17(即销售人员ANNIE)在销售人员清单$C$2:$C$13的范围内（即图9所示的原始数据表）出现的次数。这个出现的次数即可认为是该销售人员ANNIE的订单数。（2）订单总额-用SUMIF汇总每个销售人员的销售额。以销售人员ANNIE的订单总额公式为例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在销售人员清单$C$2:$C$13中检查单元格A17中的文本（即销售人员ANNIE），然后计算订单金额列（$B$2:$B$13）中相应量的和。这个相应量的和就是销售人员ANNIE的订单总额。（3）销售奖金-用IF根据订单总额决定每次销售应获得的奖金。假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：=IF(C1732 或 apples。Sum_range 是需要求和的实际单元格。说明 只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。 如果忽略了 sum_range，则对区域中的单元格求和。 Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用 IF 函数。示例:如果您将示例复制到空白工作表中，可能会更易于理解该示例。12345AB属性值佣金100,0007,000200,00014,000300,00021,000400,00028,000公式说明（结果）=SUMIF(A2:A5,160,000,B2:B5)属性值超过 160,000 的佣金的和 (63,000)四、逻辑函数的应用：AND、OR、NOT函数用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。这三个函数都用来返回参数逻辑值。详细介绍见下：1.AND函数所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为:AND(logical1,logical2,.)，其中Logical1,logical2,.表示待检测的1到30个条件值，各条件值可能为TRUE，可能为FALSE。参数必须是逻辑值，或者包含逻辑值的数组或引用。举例说明：1、 在B2单元格中输入数字50，在C2中写公式=AND(B230,B2=60,E3=60,F3=60,G3=60,H3=60,I3=60,J3=60)图1AND函数示例12、如果B1-B3单元格中的值为TRUE、FALSE、TRUE，显然三个参数并不都为真，所以在B4单元格中的公式=AND(B1:B3)等于FALSE图2AND函数示例22.OR函数OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。比如，上面的示例2，如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE图3OR函数示例3.NOT函数NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.比如NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。五、多条件统计函数的应用SUMPRODUCT在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法: SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。说明数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 1234ABCDArray 1Array 1Array 2Array 2342786671953公式说明（结果）=SUMPRODUCT(A2:B4, C2:D4)两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。(156)说明上例所返回的乘积之和，与以数组形式输入的公式 SUM（A2:B4*C2:D4) 的计算结果相同。使用数组公式可以为类似于 SUMPRODUCT 函数的计算提供更通用的解法。例如，使用公式 =SUM(A2:B42) 并按 Ctrl+Shift+Enter 可以计算 A2:B4 中所有元素的平方和。有一个大明细，需要用到的字段有地区、客户名称。每个客户有多条销售记录。我现在要统计出每个地区有几个客户（重复客户算一个）。有什么便捷的好办法？谢谢！满意回答 A1:A20为区域,中间不能为空的记录,=SUM(1/COUNTIF(A1:A20,A1:A20),数组公式,CTRL+SHIFT+ENTER结束这个方法没有进行分组啊。我需要统计出上海多少个客户，广州多少个客户，深圳多少个客户。难道要一个一个地区去设置这样的公式？有没有办法一次就全算出来啦？ 回答A列为地区名(上海、广州、深圳）,B列为客户名称上海的客户数为：=SUMPRODUCT(A1:A12=上海)*(1/COUNTIF(B1:B12,B1:B12)广州的客户数为：=SUMPRODUCT(A1:A12=广州)*(1/COUNTIF(B1:B12,B1:B12)深圳的客户数为：=SUMPRODUCT(A1:A12=深圳)*(1/COUNTIF(B1:B12,B1:B12)1:12为你数据区域，视你数据的多少来改数值。Excel多条件求和 & SUMPRODUCT函数用法详解 日常工作中，我们经常要用到多条件求和，方法有多种，第一类：使用基本功能来实现。主要有：筛选、分类汇总、数据透视表、多条件求和向导；第二类：使用公式来实现方法。主要有：使用SUM函数编写的数组公式、联用SUMIF和辅助列（将多条件变为单条件）、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本)，方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了，与SUMPRODUCT函数的作用相比实在不匹配，为了更好地掌握该函数，特将其整理如下。龙逸凡注：欢迎转贴，但请注明作者及出处。一、基本用法在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。公式：=SUMPRODUCT(A2:B4, C2：D4)ABCD1Array 1Array 1Array 2Array 2234273866741953公式解释：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。计算结果为156二、扩展用法1、使用SUMPRODUCT进行多条件计数语法：SUMPRODUCT(条件1)*(条件2)*(条件3)* (条件n)作用：统计同时满足条件1、条件2到条件n的记录的个数。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)公式解释：统计性别为男性且职称为中级职称的职工的人数统计成绩600分以上且专业为护理的人数=SUMPRODUCT(L2:L101=600)*(Q2:Q101=护理)* L2:L101)统计成绩600分以上、专业为护理且生源地为安徽省合肥市辖区的人数=SUMPRODUCT(L2:L101=600)*(Q2:Q101=护理)*(L2:L101=安徽省合肥市辖区)第二种方法：=AND(L2=600,Q2=护理)=COUNTIF(S2:S101,TRUE)2、使用SUMPRODUCT进行多条件求和语法：SUMPRODUCT(条件1)*(条件2)* (条件3) *(条件n)*某区域)作用：汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*C2:C10)公式解释：统计性别为男性且职称为中级职称的职工的工资总和（假设C列为工资）三、注意事项1、数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 如:公式中出现 A2:A10, B2:B10, C2:C10。2、SUMPRODUCT函数将非数值型的数组元素作为 0 处理。3、在SUMPRODUCT中，2003及以下版本不支持整列（行）引用，必须指明范围，不可在SUMPRODUCT函数使用A:A、B:B，Excel2007及以上版本可以整列（列）引用，但并不建议如此使用，公式计算速度慢。4、SUMPRODUCT函数不支持“*”和“？”通配符SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“？”等通配符，要实现此功能可以用变通的方法，如使用LEFT、RIGHT、ISNUMBER(FIND()或ISNUMBER(SEARCH()等函数来实现通配符的功能。如：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*(LEFT(D2: D10,1)=龙)*C2:C10)=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*(ISNUMBER(FIND(龙逸凡,D2: D10)*C2:C10)注：以上公式假设D列为职工姓名。ISNUMBER(FIND()、ISNUMBER(SEARCH()作用是实现“*”的通配功能，只是前者区分大小写，后者不区分大小写。5、SUMPRODUCT函数多条件求和时使用“，”和“*”的区别：当拟求和的区域中无文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值 #VALUE!，而使用“，”时SUMPRODUCT函数会将非数值型的数组元素作为 0 处理，故不会报错。 也就是说：公式1：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*C2:C10)公式2：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称),C2:C10)当C2:C10中全为数值时，两者计算结果一样，当C2:C10中有文本时公式1会返回错误值 #VALUE!，而公式2会返回忽略文本以后的结果。四、网友们的精彩实例1、求指定区域的奇数列的数值之和=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)2、求指定区域的偶数行的数值之和 =SUMPRODUCT(MOD(ROW(A1:A22),2)-1)*A1:A22)*(-1)3、求指定行中列号能被4整除的列的数值之和=SUMPRODUCT(MOD(COLUMN(A1: P1),4)=0)*A1: P1)4、.求某数值列前三名分