excel如何获取指定区域中每行首次出现指定值的位置
时间:2023-09-25 06:18:53
给定一个任意大小的单元格区域,其每个单元格中的数据要么是0要么是1,并且每行至少有一个1,要求使用一个公式返回一个数组,该数组由区域每行中第一次出现1的相对列位置组成。例如下图1所示的单元格区域A1:E10,要求返回数组{2;1;1;2;1;5;1;4;1;3}。
![excel如何获取指定区域中每行首次出现指定值的位置](https://img.aspxhome.com/file/2023/7/a142757_0.jpg)
图1
注意,公式中可以使用单元格区域A1:E10,但应该适用于任何其他区域。并且,所返回的数组中的元素对应的是区域内的相对列位置,例如将图1中的区域替换成H1:L10不应该影响公式的结果。
先不看答案,自已动手试一试。
公式
下面列出了各种解决上述问题的公式。
公式1:
=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^(COLUMNS(A1:E10)-ROW(INDIRECT(“1:”&COLUMNS(A1:E10))))),2))
公式2:
=COLUMNS(A1:E1)-INT(LOG(MMULT(A1:E10*2^(COLUMNS(A1:E1)-COLUMN(A1:E1)),TRANSPOSE(A1:E1*0+1)),2))
或:
=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^TRANSPOSE(COLUMNS(A1:E10)-COLUMN(A1:E10)+MIN(COLUMN(A1:E10))-1)),2))
公式3:
=MATCH(1,(OFFSET($A$1,ROW($A$1:$A$10)-1,,,COUNTA($A$1:$E$1))),0)
公式4:
=FIND(1,TEXT(MMULT(A1:E10*10^(COLUMNS(A1:E10)-COLUMN(A1:E10)+MIN(COLUMN(A1:E10))-1),TRANSPOSE(COLUMN(A1:E10)^0)),REPT(0,COLUMNS(A1:E10))))
或:
=FIND(1,TEXT(MMULT(A1:E10,TRANSPOSE(10^(COUNT(A1:E1)-COLUMN(A1:E1)))),REPT(“0”,COUNT(A1:E1))))
或:
=FIND(1,TEXT(MMULT(A1:E10,10^(COLUMNS(A1:E10)-ROW(INDIRECT(“1:”&COLUMNS(A1:E10))))),REPT(0,COLUMNS(A1:E10))))
公式5:
=MOD(SMALL(IF(A1:E10=1,ROW(A1:E10)*10^5+COLUMN(A1:E10)-MIN(COLUMN(A1:E10))+1),SUBTOTAL(9,OFFSET(A1,,,ROW(A1:E10)-MIN(ROW(A1:E10))+1,COLUMNS(A1:E10)))-MMULT(A1:E10,TRANSPOSE(COLUMN(A1:E10)^0))+1),10^5)
或者将数据区域命名为Data:
=MOD(SMALL(IF(Data=1,ROW(Data)*10^COLUMNS(Data)+COLUMN(Data)-MIN(COLUMN(Data))+1),SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))-MMULT(Data,TRANSPOSE(COLUMN(Data)^0))+1),10^COLUMNS(Data))
公式6:
=MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)
或:
=MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)
公式7:
=ROUND(COLUMNS(A1:E1)+1-ROUND(MOD(LARGE(ROWS(A1:E10)-ROW(A1:E10)+1+(10^-7*A1:E10*(COLUMNS(A1:E1)-(COLUMN(A1:E1)-COLUMN(A1)))),1+((ROW(A1:E10)-ROW(A1))*COLUMNS(A1:E10))),1),7)*10^7,0)
公式8:
=-INT(LOG(MMULT(Data,2^-ROW(OFFSET(A1,,,COLUMNS(Data)))),2))
或:
=-INT(LOG(MMULT(Data,TRANSPOSE(2^-COLUMN(Data))),2))
扩展:
1. 每行的第一个正值的位置(数据中没有负值)
=-INT(LOG(MMULT(SIGN(Data),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))
2. 每行的第一个正值的位置(数据中可能有负值)
=-INT(LOG(MMULT(–(Data>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))
3. 每行的第一个非零值的位置
=-INT(LOG(MMULT(–(Data<>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))
4. 每行中指定的数据第一次出现的位置
=IFERROR(-INT(LOG(MMULT(–(Data=”指定数据”),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10)),0)
公式9:
=MATCH(1,INDEX(Data,ROW(Data)-MIN(ROW(Data))+1,),)
公式10:
=–RIGHT(TEXT(MMULT(Data*10^(-COLUMN(Data)+MIN(COLUMN(Data))-1),TRANSPOSE(COLUMN(Data)^0)),”0E+000″),3)
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
电脑中病毒了恢复出厂设置有用吗?
![](https://img.aspxhome.com/file/2023/30/a270641_0s.jpg)
wps如何给工作表格设置保护密码
win10系统提示注册表文件丢失或损坏因此无法加载怎么修复?
![](https://img.aspxhome.com/file/2023/28/a240767_0s.jpg)
Excel中CUMPRINC函数的语法和用法
![](https://img.aspxhome.com/file/2023/4/a155784_0s.png)
电脑开机提示"press F2 for acceleration"的解决方法
![](https://img.aspxhome.com/file/2023/2/a285441_0s.jpg)
win7系统整理磁盘碎片显示错误的方法
![](https://img.aspxhome.com/file/2023/5/a315275_0s.jpg)
5款适用于M1的视频转换器分享
![](https://img.aspxhome.com/file/2023/6/a206366_0s.jpeg)
Win7怎么提高玩植物大战僵尸原版的性能?
![](https://img.aspxhome.com/file/2023/26/a228735_0s.png)
Excel2016工作表该怎么隐藏?
![](https://img.aspxhome.com/file/2023/7/41467_0s.jpg)
如何打开avast静默模式 avast静默模式开启的方法
![](https://img.aspxhome.com/file/2023/8/a336862_0s.jpg)
Excel如何建立可选项目的查询系统
![](https://img.aspxhome.com/file/2023/8/a143008_0s.jpg)
AI实例教程:用AI绘制可爱圣诞老人插图教程
![](https://img.aspxhome.com/file/2023/1/a211491_0s.png)
微软Win10共有七个版本有什么区别? Win7/Win8.1免费升专业版
![](https://img.aspxhome.com/file/2023/28/a250252_0s.jpg)
Mac实用小技巧[3]-Mac怎么创建txt文件?教你设置新建txt的快捷键
![](https://img.aspxhome.com/file/2023/0/a213480_0s.png)
Win10任务栏图标一闪一闪的提示消息怎么解决?
![](https://img.aspxhome.com/file/2023/26/a221400_0s.png)
不是有效的win32应用程序怎么办?
![](https://img.aspxhome.com/file/2023/2/a286370_0s.png)
如何解决MacOS磁盘未能分区的问题?磁盘分区出错解决办法
![](https://img.aspxhome.com/file/2023/3/a210743_0s.png)
万能网卡驱动介绍
![](https://img.aspxhome.com/file/2023/2/a286725_0s.png)
Excel数组公式教程 查找大于指定值的最小值
![](https://img.aspxhome.com/file/2023/6/a143356_0s.png)