问题描述
使用我以前的帖子
1. PHPSpreadsheet生成错误错误编号" INDEX()函数的参数组:给定5个，期望值介于1到4之间"
2. Excel-获取列的前5个数据及其匹配的标题，但会产生重复
With my previous posts
1. PHPSpreadsheet generates an error "Wrong number of arguments for INDEX() function: 5 given, between 1 and 4 expected"
2. Excel - Getting the Top 5 data of a column and their matching title but produces duplicates
我发现PHP的PHPSpreadsheet库尚未允许使用AGGREGATE()和复杂的公式/函数，但我迫切需要它们的功能
I have found out that the PHPSpreadsheet library for PHP is yet to allow the usage of the AGGREGATE() and complicated formulas/functions but I'm in dire need of their functionalities
回头，我的Excel中有2列(由我的由CodeIgniter和Laravel制作的Web应用程序生成)
Going back, I have 2 columns in my Excel (produced by my web applications made from CodeIgniter and Laravel)
问题是，文章计数列(在右侧)包含2个 54 值，该值应该属于2个不同的出版物(在左侧)，但使用公式=INDEX(E$4:E$38,MATCH(M4,J$4:J$38,0))时，它只会获取第一个匹配的出版物.
The problem is, the Article Count column (on the right) contains 2 values of 54 which is supposed to belong to 2 different Publications (on the left) but with the use of the formula =INDEX(E$4:E$38,MATCH(M4,J$4:J$38,0)) it just fetches the 1st matched Publication.
输出应如下所示:
The output should look like this:
原始表格:
The original Table:
我的问题是，Excel中什么是正确的函数或代码，以便我可以检索匹配数据的 SECOND 发布?我的目标是文章计数为 54 的那些出版物，但我想针对的是第二个，即字母 D ，不使用Excel的 Aggregate()功能
My question is, what would be the right function or code in Excel so I could retrieve the SECOND Publication of my matched data?I'm aiming to target those Publications that has the Article Count of 54, but I want to aim the SECOND ONE which is the letter D WITHOUT using the Aggregate() function of Excel
这是我使用的代码
1)=LARGE(J4:J38,1)-J4:J38是我的原始数据范围，我正在使用它来按降序获得5个最高数字
2)=INDEX(E4:E38,MATCH(M4,J4:J38,0))-我正在使用它来检索与文章计数
Here are my used codes
1) =LARGE(J4:J38,1) - J4:J38 is my range of raw data, I am using this to get the 5 highest numbers in descending order
2) =INDEX(E4:E38,MATCH(M4,J4:J38,0)) - I'm using this to retrieve the Publication Names that matched the Article Count
推荐答案
在聊天中交流之后，我们得到了这个正确的公式:
After communicating in chat, we got this correct formula:
=INDEX(E$2:E$38,IF(M4=M3,MATCH(L3,E$2:E$38,0),0)+MATCH(M4,OFFSET(J$2,IF(M4=M3,MATCH(L3,E$2:E$38,0),0),0,COUNT(J$2:J$38)-IF(M4=M3,MATCH(L3,E$2:E$38,0),0),1),0))
这是如何工作的:如果当前发布计数与上一个相同，则此IF(M4=M3,MATCH(L3,E$2:E$38,0),0)返回上一行的发布标题在titles数组(E)中的位置.我们将此数字称为X.我们不使用J2:J38作为结果，而是使用J(2 + X):J38.此技巧是通过使用offset剪切上一行已使用的上一节来完成的.这样，在重复发布计数时，已经提到的标题就会被忽略.
How this works:This IF(M4=M3,MATCH(L3,E$2:E$38,0),0) returns the position of the previous row's publication title in the titles array (E), in case the current publication count is the same with the previous one. Let's call this number X. Instead of using J2:J38 for the results, we use J(2+X):J38. This trick is done by using offset to cut off the previous section, already used by the previous row. This way, on repeating publication counts the already mentioned titles get ignored.
这篇关于Excel-从您的对应数据中获取第二个或第n个匹配的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！