excel表格获取指定区域中每行首次出现指定值的位置
发布时间:2023-04-03 09:50:59
给定一个任意大小的单元格区域,其每个单元格中的数据要么是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)
小结
这里只列出了10组公式,原文中还有很多公式,有兴趣的朋友可以花点时间研究这些公式,打磨自已的公式技能。
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- win10系统有很多强大的功能,吸引不少网友下载安装使用,其中也有很多实用的小工具,比如说win10计算器工具。有网友还不清楚win10计算
- 有时候需要在每页上都显示表头,这样好查看数据,下面我们来看一下excel如何设置打印表头简介有时候需要在每页上都显示表头,这样好查看数据,下
- excel表格中怎么制作环比增减的箭头?excel表格中的数据,为了能更清楚的显示对比,可以设置数据增减箭头,该怎么设置呢?下面我们就来看看
- 20H2是微软公司于2020年10月最新推出的Win10最新系统,那么这个系统对比之前的2004、1904到底有什么不一样的地方?有哪些提升
- 1、设置页面大小先选择Word“文件”→“页面设置”命令,打开Word“页面设置”对话框,选择“纸张”标签,并选择“纸型”为“自定义大小”,
- 在word中通常根据排版的需要,有时要恰当的设置段落间距,具体怎么做呢?工具/原料电脑,word文字处理系统方法/步骤打开word,进入工作
- 俗话说人靠衣装,而美丽的图片,如果架上边框的话,会更加地完美。在Word2007文档中,我们知道有边框和底纹的设置,而在图片中,也为用户提供
- 有可能表格中有其他默认的格式,所以没办法自动按顺序填充数字如果你想拖动复制时是按顺序排列,只需按下列步骤操作即可:1.选中单元格,等到单元格
- 在我们日常使用的win10系统计算机中,应该会有很多用户遇到过计算机启动快、变灰的情况。我们该怎么办?下面的小编将为您带来win10计算机快
- 问题一 在Word档 中插入公式打开Word文档,将光标移到需要插入数学公式的位置,点击菜单栏中的MathType按钮,选择需要插入的公式类
- word转换pdf怎么转换?很多伙伴还不知道其实可以直接另存为PDF的。1、Word转换PDF在文档中点击文件-另存为,选择要存储的文件位置
- 针对word2003版本的,其操作如下图:“视图”→“工具栏”→“窗体”。 弹出如下图的小工具栏,看到了吗,最后一
- 不少从事文档编辑工作的用户都会使用word,那么word文档页边边距怎么设置呢?对于新手来说熟练使用Word还是有一定难度,怎么办?下面小编
- 在使用MathType编辑公式时会有很多符号的输入,除了平常的四则运算符号之外还会有很多数学符号,比如不等号。那么MathType不等号怎么
- 1.打开Word文档发现图片上的文本框的效果并不是很好。 2.选中文本框,接着点击工具栏的“格式”,然后将填充选为
- word2010插入的公式怎么更改字体?word中插入的公式默认是Cambria Math字体,但是,论文一般要求Times New Rom
- 接着为大家提供具体的操作步骤:1、我们本次的例子就是人事部经常会在月底计算员工的出勤情况,通过考勤机的软件导出类似如下图的数据,我们要对其进
- 昨天苹果推送了iOS 14.4的首个Beta测试版,然而刚刚过了没多久,借助checkra1n,一些用户已经成功对该版本实现越狱操作。如图所
- Word文档是一款广受欢迎的办公软件,隶属于Microsoft office,专业用于编辑和处理文本信息。在Word文档中如果有需要我们可以
- Word2007是一款流行的文档编辑软件。对于学生与白领来说,它是一款不可或缺的工具。但是笔者经过观察发现,目前很多用户对这款软件的功能并不