Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 3444|回复: 4

[已解决]公式求助

[复制链接]
发表于 2014-2-17 16:56 | 显示全部楼层 |阅读模式
8学分
根据“原始表格”中“身份号码”栏
用户公式让“统计表格中”黄色部分自动生成

最佳答案
2014-2-19 06:22
C23=SUM(IF(LEFT(RIGHT(原始表格!H3:H421,18),6)="500101",1))
D23=SUM(IF((LEFT(RIGHT(原始表格!H3:H421,18),3)="500")*(LEFT(RIGHT(原始表格!H3:H421,18),6)<>"500101"),1,))
G23=SUM((LEFT(原始表格!$B3:$B421,6)=E20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
H23=SUM((LEFT(原始表格!$B3:$B421,6)=E20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),3)="500")*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
K23=SUM((LEFT(原始表格!$B3:$B421,6)=I20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)="500101"))
L23=SUM((LEFT(原始表格!$B3:$B421,6)=I20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),3)="500")*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
O23=SUM((LEFT(原始表格!$B3:$B421,6)=M20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)="500101"))
P23=SUM((LEFT(原始表格!$B3:$B421,6)=I20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),3)="500")*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))

公式求助.rar

23 KB, 下载次数: 22

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2014-2-17 17:43 | 显示全部楼层
公式求助.rar (23.54 KB, 下载次数: 21)
回复

使用道具 举报

 楼主| 发表于 2014-2-18 09:13 | 显示全部楼层
liyh67 发表于 2014-2-17 17:43

感觉你做的公式错误,不是500开头的总共只有不到10个,而你的结果是300多个
回复

使用道具 举报

发表于 2014-2-18 12:45 | 显示全部楼层
360截图20140218124423625.jpg
身份号码前六位是500101的,不是"不是500开头"
回复

使用道具 举报

发表于 2014-2-19 06:22 | 显示全部楼层    本楼为最佳答案   
C23=SUM(IF(LEFT(RIGHT(原始表格!H3:H421,18),6)="500101",1))
D23=SUM(IF((LEFT(RIGHT(原始表格!H3:H421,18),3)="500")*(LEFT(RIGHT(原始表格!H3:H421,18),6)<>"500101"),1,))
G23=SUM((LEFT(原始表格!$B3:$B421,6)=E20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
H23=SUM((LEFT(原始表格!$B3:$B421,6)=E20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),3)="500")*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
K23=SUM((LEFT(原始表格!$B3:$B421,6)=I20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)="500101"))
L23=SUM((LEFT(原始表格!$B3:$B421,6)=I20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),3)="500")*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
O23=SUM((LEFT(原始表格!$B3:$B421,6)=M20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)="500101"))
P23=SUM((LEFT(原始表格!$B3:$B421,6)=I20)*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),3)="500")*(LEFT(RIGHT(原始表格!$H$3:$H$421,18),6)<>"500101"))
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 12:57 , Processed in 0.429936 second(s), 8 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表