Countif函数从入门到精通.xls

一一 求求各各种种类类型型单单元元格格的的个个数数 123 I Love You Excel真空单元格 jfkd假空单元格 中国文本型单元格 大家好My God数字型单元格 同志有志者事竟成 恨苍天 22 56 立体 188 Home473 二二 求求 A85 A92 金逐流金世遗金世遗5 金庸金庸金庸6 木婉青金逐流金逐流2 古龙梁羽生梁羽生8 卧龙生令弧冲令弧冲1 梁羽生木婉青木婉青4 令弧冲卧龙生卧龙生3 Taxis T OFFSET A 85 RIGHT LARGE COUNTIF A 85 A 92 A 85 A 92 10 5 ROW A 85 A 92 ROW A 85 ROW OFFSET A 1 ROWS A 85 A 92 5 800004 LARGE D85 D92 10 5 ROW A 85 A 92 ROW A 85 ROW OFFSET A 1 ROWS A 85 A 92 700000 60000200004 RIGHT A96 A103 5 50000100000 40000600002 30000700001 20000300006 10000500007 00003 00005 公式解析 用COUNTIF A 85 A 92 A 85 A 92 生成D85 D92的数组再排序 10 5是因为行号最大65536不超过100000 用 一个足够大的数使行号大小不会影响排序结果 再用right 5 取出各行应该得到的偏移量 最后用T Offset 得到内存数组 八八 用用countif countif 的的三三维维引引用用取取得得不不重重复复值值示示例例 数据区域一般用法内存数组 甲甲甲0 25 1 COUNTIF data5 data5 乙乙乙1 丙丙丙1 丁丁丁0 5 戊戊戊0 5 己己己0 5 庚庚庚0 5 辛辛辛1 壬壬壬1 癸癸癸1 甲 N A0 25 甲 N A0 25 甲 N A0 25 丁 N A0 5 戊 N A0 5 己 N A0 5 庚 N A0 5 1 10 0 SUM D115 D131 数据区域三维用法一般用法行数 甲111 乙112 丙113 丁114 戊115 己116 庚117 辛118 壬119 癸1110 甲2211 甲3312 甲4413 丁2214 戊2215 己2216 庚2217 x x T OFFSET A 115 SMALL IF COUNTIF OFFSET A 115 ROW data5 ROW A 114 data5 1 ROW data5 ROW INDIRECT 1 SUM 1 COUNTIF data5 data5 ROW A 115 公式解析 用SUM 1 COUNTIF data5 data5 求数据区域中不重复值个数 D115 D132 用COUNTIF OFFSET A 115 ROW data5 ROW A 114 data5 1判断单元格内容是否重复 重复的结果 1 其结果就是 COUNTIF A 135 A135 A135 公式下拖 详细见B135 C151 以本题为例 ROW data5 ROW A 114 产生1到17的一个垂直数组 D135 D151 用small if 取得不重复值的行号 offset 的行偏移量 如果countif 1 即不重复 就取行号 否则就false small 函数忽略逻辑值和文本值 最后再用T Offset 生成内存数组 九九 用用countif countif 进进行行多多表表汇汇总总计计数数示示例例 姓名出现次数 张三12 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A167 李四10 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A168 王五11 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A169 周六12 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A170 郑七10 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A171 李八10 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A172 一月张三周六郑七李八 二月张三李四王五周六郑七 三月张三李四王五周六郑七 四月张三王五周六郑七李八 五月张三李四王五周六李八 六月张三李四王五周六郑七 七月张三李四王五周六郑七 八月张三李四王五周六郑七 九月张三李四王五周六郑七 十月张三李四王五周六郑七 十一月张三李四王五周六郑七 十二月张三李四王五周六 公式解析 用TEXT ROW INDIRECT 1 12 dbnum1 d月 返回十二个月的工作表名 A174 A185所示 TEXT ROW INDIRECT 1 12 dbnum1 d月 中加上INDIRECT 1 12 是为了使1 12行有删除或插入行操作时不影响公式的计算结果 为了便于说明 我把A2 A100改为A2 A10 并转换为列来说明 如B174 J185所示 然后对每一行 实际是每个月表的A2 A100 分别计数 最后用sum 求和 如K173 P186所示 GvntwGvntw 2005 4 202005 4 20 真空单元格 指什么内容也没有的单元格 假空单元格 指0字符的空文本 一般是由网上下载来的或公式得来的 姑且称之为假空 所所求求类类型型单单元元格格结结果果公公式式 真空单元格2 2 COUNTIF data1 真空 假空单元格5 5 COUNTIF data1 相当于countblank 函数 非真空单元格2 20 0 COUNTIF data1 相当于counta 函数 文本型单元格1 14 4 COUNTIF data1 假空单元格也是文本型单元格 区域内所有单元格2 22 2 COUNTIF data1 数字型单元格6 6 COUNT data1 不是countif 这里顺便点一下 大于508 8 COUNTIF data2 50 等于504 4 COUNTIF data2 50 小于501 18 8 COUNTIF data2 50 小于或等于502 22 2 COUNTIF data2 E 21 等于E5单元格的值4 4 COUNTIF data2 E 21 小于E5单元格的值1 18 8 COUNTIF data2 E 21 小于或等于E5单元格的值2 22 2 COUNTIF data2 604 4 SUM COUNTIF INDIRECT a46 a48 b48 b50 c47 c48 60 三个区域中 703 3 SUM COUNTIF INDIRECT a46 a48 b48 b50 c47 c48 70 三个区域中 604 4 SUM COUNTIF INDIRECT a46 a48 b48 b50 c47 c48 10并且 10 15 1 1 10并且 10 15 1 1 10并且 10 15 1 1 10并且10 15 1 1 班级 D68 成绩大于 E686 6 COUNTIF OFFSET C67 MATCH D68 A68 A81 COUNTIF A68 A81 D68 E68 用offset 生成每个班级的成绩区域 如E74 E80 再用countif 计数 OFFSET C67 MATCH D68 A68 A81 COUNTIF A68 A81 D68 COUNTIF A85 A92 A85 A92 T OFFSET A 85 RIGHT LARGE COUNTIF A 85 A 92 A 85 A 92 10 5 ROW A 85 A 92 ROW A 85 ROW OFFSET A 1 ROWS A 85 A 92 5 LARGE D85 D92 10 5 ROW A 85 A 92 ROW A 85 ROW OFFSET A 1 ROWS A 85 A 92 用COUNTIF A 85 A 92 A 85 A 92 生成D85 D92的数组再排序 10 5是因为行号最大65536不超过100000 用 一个足够大的数使行号大小不会影响排序结果 1 COUNTIF data5 data5 SUM D115 D131 张三 1 李八1 李八1 1 1 李八1 李八1 1 李八1 李八1 李八1 1 1 12 2 T OFFSET A 115 SMALL IF COUNTIF OFFSET A 115 ROW data5 ROW A 114 data5 1 ROW data5 ROW INDIRECT 1 SUM 1 COUNTIF data5 data5 ROW A 115 用SUM 1 COUNTIF data5 data5 求数据区域中不重复值个数 D115 D132 用COUNTIF OFFSET A 115 ROW data5 ROW A 114 data5 1判断单元格内容是否重复 重复的结果 1 其结果就是 COUNTIF A 135 A135 A135 公式下拖 详细见B135 C151 以本题为例 ROW data5 ROW A 114 产生1到17的一个垂直数组 D135 D151 用small if 取得不重复值的行号 offset 的行偏移量 如果countif 1 即不重复 就取行号 否则就false small 函数忽略逻辑值和文本值 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A167 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A168 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A169 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A170 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A171 SUM COUNTIF INDIRECT TEXT ROW INDIRECT 1 12 dbnum1 d月 A2 A100 A172 用TEXT ROW INDIRECT 1 12 dbnum1 d月 返回十二个月的工作表名 A174 A185所示 TEXT ROW INDIRECT 1 12 dbnum1 d月 中加上INDIRECT 1 12 是为了使1 12行有删除或插入行操作时不影响公式的计算结果 为了便于说明 我把A2 A100改为A2 A10 并转换为列来说明 如B174 J185所示 然后对每一行 实际是每个月表的A2 A100 分别计数 最后用sum 求和 如K173 P186所示 5 20 假空单元格 指0字符的空文本 一般是由网上下载来的或公式得来的 姑且称之为假空 相当于countblank 函数 假空单元格也是文本型单元格 不是countif 这里顺便点一下 SUM COUNTIF INDIRECT a46 a48 b48 b50 c47 c48 60 SUM COUNTIF INDIRECT a46 a48 b48 b50 c47 c48 70 T OFFSET A 85 RIGHT LARGE COUNTIF A 85 A 92 A 85 A 92 10 5 ROW A 85 A 92 ROW A 85 ROW OFFSET A 1 ROWS A 85 A 92 5 SUM COUNTIF INDIRECT a46 a48 b48 b50 c47 c48 E68 李四王五周六郑七李八 111 11111 11111 1111 1111 11111 11111 1111 11111 11111 11111 111 1 10 01 11 11 12 21 10 01 10 0 T OFFSET A 115 SMALL IF COUNTIF OFFSET A 115 ROW data5 ROW A 114 data5 1 ROW data5 ROW INDIRECT 1 SUM 1 COUNTIF data5 data5 ROW A 115 用small if 取得不重复值的行号 offset 的行偏移量