excel-计数与求和.ppt

第七章计数与求和技术主讲 刘彬 叼里启峰躁译玩街涕褐沸胰娃将存激檀淫钙悼屈陛隋匆吊照灌栖座出浓劝excel计数与求和excel计数与求和 工作表单元格计数和求和一般情况下 一个计数公式可以返回满足具体筛选条件的确定范围的单元格数据 EXCEL97以后 Microsoft推出了一项功能 名为autoCalculate 这项功能可以在状态栏中显示选中范围的信息 利用此功能可以进行求和 平均值 计数等等 马骋遗窘渗值麓廷彭执需希识侄嫉闲倔夫勺淑跑跃汤蛾龚炬慈俱哎臆镰灼excel计数与求和excel计数与求和 7 3基本的计数公式 7 3 1统计单元格汇总数量如果要统计一个范围内的单元格总数 可以使用如下公式 ROWS 数据引用区域 COLUMNS 数据引用区域 7 3 2统计空单元格的数量 countblank 数据引用区域 7 3 3统计非空单元格的数量 countA 数据引用区域 堤邑绑钧兜时淹季脖椎窑励耐屹蹋角上斗伦文唐囚尾矣陶聘堕偏铲吃伴涵excel计数与求和excel计数与求和 7 3 4数字单元格计数如果要单统计范围中的数字单元格 可以使用下列公式 count data 注意 包含日期或时间的单元格被视为数字单元格 包含逻辑值的单元格不能认为是数字单元格 7 3 5非文本单元格计数 sum if isnontext data 1 7 3 6文本单元格计数 sum if istext data 1 痈帕绩盐临耐卵独染赢篓候咋芬么均堤淄裳馆台姓供试午浙荧隧班栏泳状excel计数与求和excel计数与求和 常见的计数与求和函数 关烬倪霜扯撩衡瑞儡午惠保媳狞待颓迟嫁芜豹诧冗私平闭压欧召沤咐躲砖excel计数与求和excel计数与求和 常见的计数与求和函数 芒晦葵冲冗耸澳酵瘸照滴荫抒此骡章岩愤宾渊芦鼠葡瓢皱帖仙筑度纵逸忽excel计数与求和excel计数与求和 Count函数 COUNT value1 value2 计算包含数字的单元格及参数列表中数字的个数 注解如果参数为数字 日期 逻辑值或者代表数字的文本 例如 用引号引起的数字 如 1 则将被计算在内 如果参数为数组或引用 则只计算数组或引用中数字的个数 不会计算数组或引用中的空单元格 逻辑值 文本或错误值 CountA函数为统计非空单元格的个数 涂窍粪龄火长悬槐爹面作迫撇内苑疙宴抖莹旨箕所谨凡淀碰兑砧伪闸讹啸excel计数与求和excel计数与求和 举例 COUNT A1 A7 3 COUNT A4 A7 2 COUNT A1 A7 TRUE 3 COUNTA A1 A7 6 COUNTBLACK A1 A7 1 群阀学害妆慢卉侈翱锚司示衅落蝶凳粉鸡瞳睛磐拯铲芦郴帮锯窘润股钦寓excel计数与求和excel计数与求和 COUNTIF函数 countif 数据区域 条件表达式 返回包含值12的单元格数量 Countif range 12 返回包含负值的单元格数量 Countif range average range 返回包含3或 3值的单元格数量 countif range 3 countif range 3 并相饥怎秩频苦贵章闰旨诚孩榴吟慢砌豫峨熄惊银淡扎擅梅栅茶占拷铅饱excel计数与求和excel计数与求和 举例 肿侍纬彤圃酿蜡井诚瓜炼惭询瘩肯眉差籽滤飞陕逝诺介崭俯言儒女蓉搀艇excel计数与求和excel计数与求和 实例 假设想让A列中重复的数据用颜色突出显示 先选中A列点菜单 格式 条件格式弹出 条件格式 对话框对话框 条件1 的左侧 选 公式 后面的输入框中 输入公式 countif A A A1 1点下面的 格式 按钮 为条件为真的单元格设置你想要的格 点击确定结束 驳腺讽受浑咬七踪辛援呵卵犯袄奄捐荣骄伶乾亲膝嗽恶谍箱众陷魏励狭镶excel计数与求和excel计数与求和 补充知识 利用数据有效性设置不重复数据录入选择范围数据 有效性 设置 自定义输入公式 使用COUNTIF 函数例如 COUNTIF A B A1 1表示在A B两列中录入不重复数据 通常情况下 可以使用此函数进行唯一值的数据录入 比如身份证号码等等 俩满洁法戊柒热修嘲否演玄嗅瓢竭椒溪融车耐镶织豹绷妈衍儿译禾缔丹惟excel计数与求和excel计数与求和 补充知识 利用数据有效性设计动态更新的数据列表思路 利用列表 countif函数及数据有效性完成 举例 见模拟数据表 动态更新列表 每孕谐乞摄箔兽帚纱讨锈仇鬼桔瞅界阵隋芬鸥伏兹渝蹿纳镑讲宏功湖寅详excel计数与求和excel计数与求和 DCOUNT database field criteria 功能 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目 Database 构成数据清单或数据库的单元格区域 Field 指定函数所使用的数据列 数据清单中的数据列必须在第一行具有标志项 Field可以是文本 即两端带引号的标志项 如 产量 此外 Field也可以是代表数据清单中数据列位置的数字 1表示第一列 2表示第二列 等等 Criteria 为一组包含指定条件的单元格区域 它至少包含一个列标志和列标志下方用于设定条件的单元格 如果省略 函数DCOUNT返回数据库中满足条件criteria的所有记录数 举例 揪缝椎默仕筐誊中虽摧胰犁若卯涸糠反痴趴苫贼后腥操抽画询扎邮珐兔椰excel计数与求和excel计数与求和 注意事项 1 单列上具有多个条件 如果对于某一列具有两个或多个筛选条件 那么可直接在各行中从上到下依次键入各个条件 例如下面的条件区域显示销售人员列中包含李明 张奇或程西的行 伯氏魄帜稠厅对植畜参构郡画施翟耪艾修巫侧削保六狭谩厚寄异蛙干腕乖excel计数与求和excel计数与求和 2 多列上具有单个条件若要在两列或多列中查找满足单个条件的数据 请在条件区域的同一行中输入所有条件 例如 下面的条件区域将显示所有在类型列中包含农产品 在销售人员列中包含李明且销售额 100的数据行 喊芝图练雅丝该嚷纠乾衡狠孩袁聚征市魏租瘸牵镑嗽悄别财画褐跃香拷掖excel计数与求和excel计数与求和 3 某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据 请在条件区域的不同行中输入条件 例如 下面的条件区域将显示所有在类型列中包含日光灯 在销售人员列中包含李明或销售额 80的行 灵抒壤聚膛鸥世帚酪骋朴稍共具挣泻俄闽郁吾滤缩牵又京获噶术但蒙帚祟excel计数与求和excel计数与求和 4 满足多列条件若要找到满足多组条件 每一组条件都包含针对多列的条件 的数据行 请在各行中键入条件 例如 下面的条件区域将显示所有在销售人员列中包含李明且销售额 100的行 同时也显示程龙销售商的销售额 90的行 呛绸谣杏吻暴益执浑捡鳃察蹲坞懒涕像伎栅探荤措颊鼻逗挡款港讣毯虎登excel计数与求和excel计数与求和 使用多筛选条件进行计数 1 使用and筛选条件常见的例子是使用公式统计符合一定值范围的数字的数量 例如 统计出包含值大于0小于12的单元格数量 countif range 0 countif range 12 可否使用如下公式 countif range 0 countif range 12 不能该公式计算的是区域中所有单元格的个数乘2 嘶件儡焊病岂押觅琶森酚菌舍摧绞惫柔矗工孜磅磁毫嚣溪枉傀赴月惧嫩咱excel计数与求和excel计数与求和 另外一种计算多条件公式的方法 使用数组公式 sum range 0 range 12 注意数组公式最后要以ctrl shift enter结束 And多条件举例 准汞锻较楷到例探员曳太榆鸣呆烁茁滋砾篡尉胃殴姻枉圣辛梧则蕾说咎鞋excel计数与求和excel计数与求和 要求统计分公司工资大于3000的女性员工数量 选定A2 A10 定义名称bm选定E2 E10 定义名称gz选定D2 D10 定义名称xb选定F2单元格输入数组公式 SUM bm 分公司 xb 女 gz 3000 电槛晴芳烙盟栖沃烦馆赁染朴判赵旨寥但穷锦乙辗诽茧熏鞍拱送叠兢灌抉excel计数与求和excel计数与求和 补充 数组公式 指可以在数组的一项或多项上执行多个计算的公式 数组公式可以返回多个结果 也可返回一个结果 位于多个单元格中的数组公式称为多单元格公式 位于单个单元格中的数组公式称为单个单元格公式 数组公式以Ctrl Shift Enter结束输入 不能人工输入大括号 否则系统会视为一个文本字符串 数组模拟数据表 遣隘眠帜铱析堂氮瑰祟烹不瓦仲陆什定省炉话遁荐筷侮腋邢仔疥村里搪哉excel计数与求和excel计数与求和 注意事项 1 每次输入或编辑数组公式都要以Ctrl Shift Enter结束2 使用多单元格公式时 还需遵循以下原则 输入公式前必须选择保存结果的单元格区域 不能更改数组公式中单个单元格的内容 可以移动或删除整个数组公式 但无法移动或删除其部分内容 不能向多单元格数组公式中插入空白单元格或删除其中的单元格 首蛙太施裂属茂愈员痛帽份误愉吞码玩红鲍崇卿餐沂畸烟杂茹餐开宇锅军excel计数与求和excel计数与求和 数组常量简介 可以通过输入一系列项然后手动用大括号 将该系列项括起来创建数组常量 类似于 1 2 3 4 5 使用逗号分隔各项 将创建水平数组 一行 使用分号分隔各项 将创建垂直数组 一列 创建水平常量例如创建新的工作簿 选择单元格A1到E1 在编辑栏中输入下面的公式 1 2 3 4 5 然后按Ctrl Shift Enter结束 搀剁冤噪除汝翠暇趋雍坯叮岁砾构倪京俐挥叉怒淋郴具贾菇暴廉谁铂庶骑excel计数与求和excel计数与求和 数组常量简介 从现有值创建数组常量在数组工作表上 选择单元格区域 按F2切换到编辑模式 按F9将单元格引用转换为值 Excel将这些值转换为数组常量 按Ctrl Shift Enter输入数组常量作为数组公式 蛮愧缄宝耶糖掠掌犁绒暗珊屎化满仓期矾依居疟刘荆拾高索决蟹籍兢皿泅excel计数与求和excel计数与求和 应用 参考模拟数据表 数组应用 假设sheet1中有如下数据 伯羊鹃农卡答五暂黔侦寒高嚣唁捉边怯训缝猩第藕消仑逛撩针树詹踩枕揣excel计数与求和excel计数与求和 应用 查找区域中的n个最大值 选中对应n个单元格 输入如下公式 LARGE A1 A50 ROW INDIRECT 1 n 备注 使用ROW函数创建连续的整数数组 读取行号 例如选中A1 A5 输入数组公式 row 1 5 结果会显示1 2 3 4 5 但是如果在数组第一行上插入一行 就会变成2 3 4 5 6 因此我们使用indirect函数纠正 ROW INDIRECT 1 10 INDIRECT函数使用文本字符串作为参数 这是区域1 10由双引号括起的原因 当插入行或移动数组公式时 Excel不会调整文本值 因此 此ROW函数总是生成所需的整数数组 公式分析 从内层的括号开始向外计算 INDIRECT函数返回一组文本值 在这种情况下 为值1到n ROW函数依次生成包含n个单元格的纵栏式数组 LARGE函数使用单元格A1 A50中的值 并且它计算n次 每次都对应于ROW函数返回的每个引用 彼悍俞码叫瘦鳃绎赂霉余挽遇箕湍镁耐估潭嗽狠鄙础键级蔗金访趟君客冰excel计数与求和excel计数与求和 补充 PHONETIC 函数 我们在连接字符串时常使用 连接符或CONCATENATE函数 但是遇到字符串较多的情况下往往这两个办法都比较复杂 甚至无法实现 问题 连接A1 A100所有字符串 方法1 A1 A2 A3 A100或者 CONCATENATE A1 A2 A100 方法2 PHONETIC A1 A100 PHONETIC函数可以对除纯数据 日文外的所有字符进行连接 对于连续区域直接使用即可 对于在同一工作表中的不连续区域 可用定义名称的方法连接 奥诞桃锤砒胺山蓑强诵领综获裙毅蛾仟篱野宏粹登样腿蜗咖嫌拇疚因则匡excel计数与求和excel计数与求和 数组常量简介 可以通过输入一系列项然后手动用大括号 将该系列项括起来创建数组常量 类似于 1 2 3 4 5 使用逗号分隔各项 将创建水平数组 一行 使用分号分隔各项 将创建垂直数组 一列