excel如何获取指定区域中每行首次出现指定值的位置
发布时间:2023-09-25 06:18:53
给定一个任意大小的单元格区域,其每个单元格中的数据要么是0要么是1,并且每行至少有一个1,要求使用一个公式返回一个数组,该数组由区域每行中第一次出现1的相对列位置组成。例如下图1所示的单元格区域A1:E10,要求返回数组{2;1;1;2;1;5;1;4;1;3}。
图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)
猜你喜欢
- 使用Win10系统想要将两块磁盘组成跨区卷的话,如果计算机用户名或者磁盘名字不是英文的话,会显示包名称无效,没办法组成。我们只能把计算机用户
- 我们使用电脑的时候可以去更改它的电源设置,有三个不同的模式可以切换,切换到不同的模式中可以获得不同的性能。有用户想要去切换到高性能模式但是发
- 我们在使用word办公软件时,会经常碰到一些word资料文档里面可能会有水印的存在。那么,我们该如何去除这些word水印呢?其实,去掉wor
- 在很多的文档中,我们都会发现,编辑者会在需要引起大家注意的文字上,带上圈。如果需要你去编辑这样一种带圈的字符,你会吗?相信很多的朋友会说回答
- “重复键入”按钮和“恢复键入”按钮位于Word2010文档窗口“快速访问工具栏”的相同位置。“重复键入”按钮和“恢复键入”按钮位于Word2
- 有时候在打开word2010时会出现问题,显示“无法打开文件,因为内容有错误”,进一步打开详细信息,显示“元素结束标记中的名称必须与开始标记
- 如何全面保护office文档安全的六种方法结合 如何全面保护office文档安全的六种方法介绍: 一、设置保存密码使用密码对文档进行保护,是
- 当我们创建完成word图表后,默认情况下会为图表自动添加一个图例内容,以方便用户对图表中不同数据系列表示的内容有所了解。对于word图表中的
- 在用户使用win10时候,会经常遇到一些文件或者软件操作需要trustedinstaller权限的问题,因为trustedinstaller
- excel作为表格软件来说是为了存储更多的数据,有时候我们的数据属于 * 就要进行加密,为了防止工作表中的数据在与他人共享浏览时被修改,可
- word里面没有楷体GB2312是因为系统中没有楷体GB2312字体。解决方法:百度搜索下载楷体GB2312字体,下载完成后,复制到系统字体
- 第1步,打开Word2013文档窗口,切换到“设计”选项卡。第2步,在“页面背景”分组中单击“页面颜色”按钮,并在打开的页面颜色面板中选择“
- 本文将教你如何在Excel中比较不同的数据。这些数据既可以是同一电子表格中的两列数据,也可以是两个不同的Excel文件中的数据。步骤方法&n
- 以下代码为通过VBA代码来设置Word字体的各种格式。WApp.Selection.Font.NameFarEast = "华文中
- 1、我们打开excel软件,点击插入菜单,找到函数,然后搜索count函数,函数下面有关于count函数的一个简单的介绍,计算包含数字以及包
- 昨天用了5个小时辛辛苦苦修改好的文档,今天居然打不开了,一打开就停止响应…讲真三年级之后再也没见过这么伤心的小白了拯救小白,如何解决Word
- 现在的人们都已经离不开word软件了,这是因为word软件可以更好的解决文档的编辑问题,提升了用户的编辑速度,也避免了很多的麻烦,所以wor
- 在工作中我们常会需要插入一些图形来制作流程图,今天小编为大家介绍一下如何在excel2010中插入图形形状。首先在菜单“插入”中点击“形状”
- 有部分用户会遇到Win10专业版游戏进不去的情况,面对这一情形时应该如何解决呢?下面就来看看小编精心准备的Win10专业版游戏都进不去解决方
- pdf作为一款办公软件,在使用的时候需要设置规定的大小,大家知道怎么去自定义页面的大小吗?一起来看看下面的教程吧pdf中如果想要自定义页面大