![如何提取 9 中 5 位数字匹配的余数](https://linux22.com/image/1429140/%E5%A6%82%E4%BD%95%E6%8F%90%E5%8F%96%209%20%E4%B8%AD%205%20%E4%BD%8D%E6%95%B0%E5%AD%97%E5%8C%B9%E9%85%8D%E7%9A%84%E4%BD%99%E6%95%B0.png)
我正在尝试使用 Excel 2003 提取搜索的“剩余部分”(不匹配的部分)。我尝试使用各种函数组合,但似乎没有特定的函数可以返回通过与记录的另一部分匹配找到的记录的不匹配部分。
以下是我正在尝试做的事情的说明。我有两个列表。 清单A有 2000 条记录。每条记录都是一个由 9 个单元格组成的数组,每个单元格包含 A 至 I 列中的一位数字。这些数字始终按升序排列。因此数据如下所示:
[A] [B] [C] [D] [E] [F] [G] [H] [I]
1 2 2 3 4 5 7 7 9
2 4 5 5 6 6 8 9 9
. . . etc.
清单B有 30 条记录。这些是在列表 A 记录中搜索的值。每条记录都是一个由 5 个单元格组成的数组,每个单元格包含 L 到 P 列中的单个数字。这些数字始终按升序排列。因此,这些数据如下所示:
[L] [M] [N] [O] [P]
1 1 3 4 5
2 3 5 7 9
1 2 3 4 5
. . . etc.
在列表 A 中搜索列表 B 中的每个记录。如果列表 B 记录中的所有五位数字与列表 A 记录中的五位数字相匹配,则为匹配。因此,在此示例中,第一个列表 B 记录与列表 A 中的任何记录都不匹配。第二个和第三个列表 B 记录都与第一个列表 A 记录匹配,并且没有列表 B 记录与第二个列表 A 记录匹配。
当存在匹配时,“余数”是列表 A 记录中不属于匹配的另外四位数字。对于此示例中的两个匹配,将如下所示:
List A record: 1 2 2 3 4 5 7 7 9
List B record: 2 3 5 7 9
Remainder: 1 2 4 7
List A record: 1 2 2 3 4 5 7 7 9
List B record: 1 2 3 4 5
Remainder: 2 7 7 9
因此第一个列表 A 记录的结果是:1247 2779
而第二个列表 A 记录的结果为空白。
B 列表记录中的值可能会发生变化,因此解决方案需要“通用”,以便可以在不改变公式的情况下更新结果。
我尝试基于函数来COUNT
计算列表 A 中每个项目中每个数字 0-9 的频率,然后使用IF
和AND
函数(指定每个数字需要哪些以及多少个才有资格匹配)来告诉我列表 B 中的哪些项目与列表 A 匹配。我尝试使用其他函数,但未能以我想要的方式提取余数。
这个问题类似于如何在 MS Excel 2003 中提取比赛提醒,但数字模式不同。已经找到了该问题的解决方案,但很难弄清楚如何修改答案以适应该问题。我不时会遇到类似的问题,所以我希望得到一个答案,其中包含一个我可以遵循的流程,以便为其他类似问题开发类似的解决方案。
答案1
我开始根据上一个问题中的方法创建答案。然后我意识到你走在正确的轨道上。由于一切都是按升序排列的,因此你只需根据每个数字的计数来回答即可。该REPT
函数将根据您需要的字符重复次数构建一个字符串。余数所需的每个数字的数量是列表 A 记录中的数量减去列表 B 记录中的数量。因此,对于单个匹配,假设 A 记录数字在 A1:I1 中,而 B 记录数字在 L1:P1 中。余数将是:
=REPT(1,COUNTIF(A1:I1,1)-COUNTIF(L1:P1,1))&
REPT(2,COUNTIF(A1:I1,2)-COUNTIF(L1:P1,2))&
REPT(3,COUNTIF(A1:I1,3)-COUNTIF(L1:P1,3))&
REPT(4,COUNTIF(A1:I1,4)-COUNTIF(L1:P1,4))&
REPT(5,COUNTIF(A1:I1,5)-COUNTIF(L1:P1,5))&
REPT(6,COUNTIF(A1:I1,6)-COUNTIF(L1:P1,6))&
REPT(7,COUNTIF(A1:I1,7)-COUNTIF(L1:P1,7))&
REPT(8,COUNTIF(A1:I1,8)-COUNTIF(L1:P1,8))&
REPT(9,COUNTIF(A1:I1,9)-COUNTIF(L1:P1,9))
这都是一个公式。我把它分成几行,这样各个部分就对齐了,你就能看清模式了。如果没有匹配,就会产生错误,稍后可以处理。
您想要生成所有比赛结果的报告。因此我们可以建立一个结构来实现这一点。
假设列表 A 在 A1:I2000 中,列表 B 在 L1:P30 中。在 R1:AV2002 中创建一个表。该表将是一种收集所有 B 记录与所有 A 记录的比较结果的机制。它还将提供一个索引来表示每个列表中的行号,以便您可以使用通用公式和间接寻址来执行匹配。将表设置为如下所示:
[R] [S] [T] [U] ... [AV]
[1] <=======List B Row===============>
[2] List A Row 1 2 3 ... 30
[3] 1
[4] 2
[5] 3
...
表格中的每个单元格都将反映一条 B 记录和一条 A 记录之间的匹配。实际上,在 R 列中输入列表 A 行号作为标签(从第 3 行开始),在 S2:AV2 中输入列表 B 行号作为列标题。这些数字将用于指向正确的记录。
在答案前面提供的公式中,A 和 B 记录的行号是硬编码的。对于此表,我们需要一个通用公式,可以使用行和列标签来引用每个单元格要使用的正确记录。因此,我们根据标签将行号替换为间接地址。每个引用都会A1:I1
被替换为:
INDIRECT("$A"&$R3):INDIRECT("$I"&$R3)
并且每个引用都L1:P1
被替换为:
INDIRECT("$L"&S$2):INDIRECT("$P"&S$2)
观察这些替换中 $ 锚点的位置。单元格 S3 将如下所示:
=REPT(1,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),1)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),1))&
REPT(2,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),2)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),2))&
REPT(3,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),3)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),3))&
REPT(4,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),4)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),4))&
REPT(5,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),5)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),5))&
REPT(6,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),6)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),6))&
REPT(7,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),7)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),7))&
REPT(8,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),8)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),8))&
REPT(9,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),9)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),9))
再次强调,这都是一个公式,为了便于阅读,将其分成多行。一旦 S3 正确,请复制此公式以填充表格中的所有单元格。通过以下方法,您将知道它是否正确:对列表 A 记录的引用对于表格同一行中的所有单元格都是相同的,并且$R3
当您向下移动表格时,将增加(表格的第二行,这将是$R4
)。对列表 B 记录的引用在您向下移动一列时将是相同的。S$2
当您穿过表格的一行时,将更改列字母(表格的第二列,这将是T$2
)。作为验证,T3 应如下所示:
=REPT(1,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),1)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),1))&
REPT(2,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),2)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),2))&
REPT(3,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),3)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),3))&
REPT(4,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),4)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),4))&
REPT(5,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),5)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),5))&
REPT(6,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),6)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),6))&
REPT(7,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),7)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),7))&
REPT(8,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),8)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),8))&
REPT(9,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),9)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),9))
表格中的每个单元格都将反映 B 记录和 A 记录之间的匹配情况。它将包含“余数”或不匹配的错误。您想要获取每个列表 A 记录的结果摘要。表格的每一行代表一个列表 A 记录。结果可以汇总在表格末尾(列 AW),也可以汇总在列表 A 数据的右侧(列 J)。第一个摘要的公式为:
=IF(ISERROR(S3),"",S3&" ")&IF(ISERROR(T3),"",T3&" ")&IF(ISERROR(U3),"",U3&" ")& ... &IF(ISERROR(AV3),"",AV3)
这里不会显示所有 30 个词,而是只显示前三个和最后一个。按照相同的模式添加其余的词。它通过连接每个匹配的结果来构建结果字符串。如果单元格中有值,它会在下一个值之前添加一个空格。如果您想要不同的分隔符,请将空格更改为其他内容,例如逗号空格。将此公式复制到列表 A 中所有行的摘要列中。
答案2
如果你已经有了匹配的答案,那么NOT(...)
很可能会得到不匹配的结果。