查看: 1359|回复: 3
我现在在做个表格，总共有两列（如下面），我现在需要设置函数公式寻找指定数值倒数第二次出现所对应的数据（比如小明这个数值倒数第二次出现，所对应的数值——应该是220），求助高手大师，怎么设置？
A列& &&&B列
小明& & 200
小李& & 100
小李& & 200
小明& & 100
小张& & 200
小王& & 100
小明& & 220
小明& & 170
阅读权限27UID668910函数1 关最后登录经验87664 点在线时间3612 小时VBA0 关分享记录好友技巧0 关相册主题段位0 段金币15605 个积分87664精华0帖子日志注册时间
excel专家, 积分 87664, 距离下一级还需 12336 积分
金币15605 个积分87664帖子
D1为查找条件，E1单元格复制以下公式，三键回车(公式复制后,点一下公式编辑栏的任意位置,先按住 ctrl、shift 两个键,然后敲enter键)；
=INDEX(B1:B8,LARGE(IF(A1:A8=D1,ROW(1:8)),2))
小明<font color="#0小明<font color="#0小李<font color="#0小李<font color="#0小明<font color="#0小张<font color="#0小王<font color="#0小明<font color="#0小明<font color="#0
阅读权限5UID624564函数0 关最后登录经验764 点在线时间92 小时VBA0 关分享记录好友技巧0 关相册主题段位0 段金币612 个积分764精华0帖子日志注册时间
【2014函中】A13:cgzxwwf
小学2年级, 积分 764, 距离下一级还需 136 积分
金币612 个积分764帖子
赞同2楼，但是他的名字里有的人名只出现了一次，我稍做了改动，只有一次的就显示这一次的
=INDEX(B1:B8,LARGE(IF(F1=A1:A8,ROW(B1:B8),&&),IF(COUNTIF(A1:A8,F1)&=2,2,1)))
15:30 上传
点击文件名下载附件
7.85 KB, 下载次数: 8
问题解决了，多谢两位大师！
最佳答案累计大于500
Powered by在Excel中按指定的重复次数填充数据到一列
您现在的位置：&&>>&&>>&&>>&正文
在Excel中按指定的重复次数填充数据到一列
作者： 文章来源： 点击数： 更新时间： 10:02:45
&&& 在工作中有时需要将Excel某列中的数据按指定的次数依次重复填充到另一列。例如在下图的示例中，要将A列的编号按B列对应次数重复填充到D列，即首先将第一个编号“WOY756”在D列中填充2次，接着将第二个编号“QLG752”填充1次，将第三个编号“RWR880”填充5次，……。
&&& 可用数组公式或VBA来实现这样的填充，示例文件下载地址：。
&&& 在D2单元格输入下面的数组公式后按Ctrl+Shift+Enter结束（下同），然后拖动填充柄向下填充公式，直到最后一个编号按指定的次数全部出现为止。
&&& 公式1：
&&& =OFFSET(A$1,MIN(IF(COUNTIF($D$1:D1,A$2:A$21)&B$2:B$21,ROW(A$1:A$20))),)
&&& 说明：COUNTIF函数统计公式所在单元格以上区域中所产生的各个“编号”数量，将其与B列对应次数进行对比，如果未达到B列对应次数，则返回“编号”相对A1单元格的偏移量，否则返回“FALSE”，这将得到一个包含“FALSE”和偏移量的数组。MIN函数取得最小值，即“编号”的偏移量，最后用OFFSET函数返回“编号”。
&&& 公式2：
&&& =INDEX(A$1:A$21,SMALL(IF($B$2:$B$21&=COLUMN($A:$J),ROW($B$2:$B$21)),ROW(A1)))
&&& 说明：“IF($B$2:$B$21&=COLUMN($A:$J),ROW($B$2:$B$21))”产生一个20行10列由FALSE和数字组成的数组（本例A列共有20个数据，最大重复次数为10），其中的数字为各“编号”对应的行号，且各数字的数量与B列的重复次数相同。如16行“BXQ763”对应的“重复次数”为3，数组中即包含3个“16”。“COLUMN($A:$J)”需根据B列的最大值进行修改，例如“重复次数”中最大值为26，则改为“COLUMN($A:$Z)”。
&&& 公式3：
&&& =INDEX(A$2:A$21,MATCH(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$1,,,ROW($1:$21))),1))
&&& 说明：MATCH函数的第二个参数“SUBTOTAL(9,OFFSET(B$1,,,ROW($1:$21)))”，在本例中产生一个对B列数字进行累加的内存数组“{0;2;3;8;10;13;22;29;31;37;44;46;56;59;69;72;75;85;94;97;99}”，第三个参数为“1”，MATCH函数会查找小于或等于第一个参数的最大值，并返回其在数组中的相对位置，即A2:A21区域中的相对行号。例如对于D17单元格，MATCH函数的第一个参数“ROW(A16)-1”返回15，数组中小于等于15的最大值为13，13处于数组中的第6个位置，MATCH函数返回6，公式返回A2:A21区域中的第6行数据，即编号“JGN347”。
&&& 如果数据量较大，用下面的VBA代码较为快捷。按Alt+F11，打开VBA编辑器，在代码窗口中粘贴下列代码并运行即可。
Sub 按指定次数重复数据()Dim Rng, Arr()Dim i As Integer, j As Integer, k&& As IntegerDim LastRow As Integer, Total As IntegerLastRow = [A65536].End(xlUp).RowTotal = Application.WorksheetFunction.Sum(Range("B2:B" & LastRow))Rng = Range("A1:B" & LastRow)ReDim Arr(1 To Total, 1 To 1)For i = 2 To UBound(Rng, 1)&&& For j = 1 To Rng(i, 2)&&&&&&& k = k + 1&&&&&&& Arr(k, 1) = Rng(i, 1)&&& NextNextRange("D2").Resize(k, 1).Value = ArrEnd Sub
上一篇文章：
下一篇文章：
&其他网友还在看：
　网友评论：（只显示最新10条。评论内容只代表网友观点，与本站立场无关！）
::发表评论::
评论内容：
请遵守及中华人民共和国其他各项有关法律法规。
严禁发表危害国家安全、损害国家利益、破坏民族团结、破坏国家宗教政策、破坏社会稳定、侮辱、诽谤、教唆、淫秽等内容的评论 。
用户需对自己在使用本站服务过程中的行为承担法律责任（直接或间接导致的）。
评论需要审核通过后才可见，本站管理员有权保留或删除评论内容。
评论内容只代表网友个人观点，与本网站立场无关。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Excel技巧天地 E-mail:123.118.75.*
先把表选中，点击任务栏上的数据，点击分类汇总，分类字段为“姓名”，汇总方式&#034;求和“，选定汇总”工资额“，可以试试看
您的举报已经提交成功，我们将尽快处理，谢谢！
二楼的公式是竖向的，选中A列整列，点数据--数据有效性--自定义，输入公式：=COUNTIF(A:A,A1)&2 .已验证,答的好
一楼的公式也可以用,是横向的...
大家还关注
(window.slotbydup=window.slotbydup || []).push({
id: '2081942',
container: s,
size: '1000,60',
display: 'inlay-fix'查看: 460|回复: 5
如何取区域中某个项目第N次出现的数值？
阅读权限10
在线时间 小时
& & & & & & & &
如何取区域中某个项目第N次出现的数值？
16:33 上传
点击文件名下载附件
7.52 KB, 下载次数: 22
阅读权限70
在线时间 小时
& & & & & & & &
=IFERROR(INDEX(明细流水!B:B,SMALL(IF(明细流水!$A$2:$A$11=$A2,ROW($2:$11)),ROW(A1))),&&)复制代码B2数组公式
阅读权限95
在线时间 小时
=INDEX(明细流水!B:B,SMALL(IF(明细流水!$A$2:$A$100=$A2,ROW($2:$100),4^8),ROW(A1)))复制代码数组公式。
下拉未排错。
阅读权限70
在线时间 小时
(11.16 KB, 下载次数: 1)
16:53 上传
点击文件名下载附件
数据透视表轻松搞定
阅读权限30
在线时间 小时
& & & & & & & &
=IF((ROW()-1)&COUNTIF(明细流水!$A$2:$A$11,项目1!$A$2),&&,INDEX(明细流水!B:B,SMALL(IF(明细流水!$A$2:$A$11=项目1!$A$2,ROW($A$2:$A$11)),ROW(A3))))复制代码
阅读权限95
在线时间 小时
可以参考此贴
关于省略工作表名定义名称的使用！
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师查看: 11822|回复: 21
如何统计一列中某个数据连续出现N次的情况出现的次数
阅读权限10
在线时间 小时
有如下若干列数据：
excel.gif (17.35 KB, 下载次数: 40)
18:19 上传
在任一列中，从第1行到最后一行，0和非0数据交替出现，有时候0会连续出现2次，或连续出现3次、4次、5次……不等。非0数据表示0隔了多少行没有出现。数据量比较大，可能有几千行数据
问题：某列数据中，从第1行到最后一行，0最多连续出现几次？
　　　连续出现1次的情况有多少回？连续出现2次的情况有多少回？连续出现3次的情况有多少回？……连续出现10次以上的情况有多少回？
　　　如何通过函数来实现这个统计功能呢，研究了很久也没能实现，请教高人相助
阅读权限50
在线时间 小时
上个附件。
用 FREQUENCY 函数。
阅读权限10
在线时间 小时
多谢楼上回复，excel文件如下：
(28.02 KB, 下载次数: 271)
18:30 上传
点击文件名下载附件
阅读权限50
在线时间 小时
看下附件，希望对你能有帮助。
[ 本帖最后由 smupsu 于
18:56 编辑 ]
18:56 上传
点击文件名下载附件
30.76 KB, 下载次数: 430
阅读权限50
在线时间 小时
& & & & & & & &
上表的附件只统计出了，MAX状态，就是说最大连续。
连续出现2次的情况有多少回？连续出现3次的情况有多少回？……连续出现10次以上的情况有多少回？
这些用SMALL返回。
阅读权限10
在线时间 小时
[em07] 你研究彩票的 是不
阅读权限10
在线时间 小时
原帖由 smupsu 于
18:58 发表
上表的附件只统计出了，MAX状态，就是说最大连续。
这些用SMALL返回。
感谢您指点，我先研究研究，有不明白的再向您请教
阅读权限10
在线时间 小时
原帖由 jiayou1220 于
19:02 发表
[em07] 你研究彩票的 是不
呵呵，看来是碰到同好了，这确实是彩票数据分析中的遗漏和惯性分析
阅读权限50
在线时间 小时
我更新了附件，现在可以看出 数字0的各连续情况
19:31 上传
点击文件名下载附件
31.51 KB, 下载次数: 621
阅读权限10
在线时间 小时
原帖由 smupsu 于
19:31 发表
我更新了附件，现在可以看出 数字0的各连续情况
汗颜，基础太差，研究了两天，终于基本弄明白了兄台的公式，不过有一点：如果加上统计数字0的“1次连续”，也就是单独出现一次的次数，这公式似乎统计的就并不准了？
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师}