检查单元格包含的每个字母和数字的计数是否与相邻单元格相同

Check a cell contains the same count of each letter and number as an adjacent cell

提问人：Wisp 提问时间：11/17/2022 更新时间：11/17/2022 访问量：93

问：

总结

我想检查一个单元格是否包含与旁边的单元格相同的 a-z 每个字母的编号和 0-9 的数字。顺序无关紧要，但字符数很重要。例如，如果一个单元格包含“flat 1， 32 test road”，而它旁边的单元格包含“32， flat 1， test road”，则它将匹配，因为它们都包含 3 t、2 a、1 l 等）。

示例表

预期输出的示例如下，公式填充在 C 列中：

数据 1 数据 2 火柴？
123测试路 测试路123 火柴
福巴 34 FOO，/，34巴 火柴
HELLOWORLD1 HELLOWORLD2 不匹配
FLAT4,33 街道名称 街道名称 33，单位 4 火柴
12345 12345路 不匹配

工作代码

目前，我通过首先将单元格的内容转换为小写，然后单独检查 a-z 中的每个字母以及语句中 0-9 的每个数字来工作。它有效，但看起来很可怕，毫无疑问，效率低下且资源密集。代码如下：IF(AND)=IF(AND(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"a","")),LEN(A1)-LEN(SUBSTITUTE(A1,"b",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"b","")),LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"c","")),LEN(A1)-LEN(SUBSTITUTE(A1,"d",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"d","")),LEN(A1)-LEN(SUBSTITUTE(A1,"e",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"e","")),LEN(A1)-LEN(SUBSTITUTE(A1,"f",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"f","")),LEN(A1)-LEN(SUBSTITUTE(A1,"g",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"g","")),LEN(A1)-LEN(SUBSTITUTE(A1,"h",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"h","")),LEN(A1)-LEN(SUBSTITUTE(A1,"i",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"i","")),LEN(A1)-LEN(SUBSTITUTE(A1,"j",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"j","")),LEN(A1)-LEN(SUBSTITUTE(A1,"k",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"k","")),LEN(A1)-LEN(SUBSTITUTE(A1,"l",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"l","")),LEN(A1)-LEN(SUBSTITUTE(A1,"m",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"m","")),LEN(A1)-LEN(SUBSTITUTE(A1,"n",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"n","")),LEN(A1)-LEN(SUBSTITUTE(A1,"o",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"o","")),LEN(A1)-LEN(SUBSTITUTE(A1,"p",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"p","")),LEN(A1)-LEN(SUBSTITUTE(A1,"q",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"q","")),LEN(A1)-LEN(SUBSTITUTE(A1,"r",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"r","")),LEN(A1)-LEN(SUBSTITUTE(A1,"s",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"s","")),LEN(A1)-LEN(SUBSTITUTE(A1,"t",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"t","")),LEN(A1)-LEN(SUBSTITUTE(A1,"u",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"u","")),LEN(A1)-LEN(SUBSTITUTE(A1,"v",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"v","")),LEN(A1)-LEN(SUBSTITUTE(A1,"w",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"w","")),LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"x","")),LEN(A1)-LEN(SUBSTITUTE(A1,"y",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"y","")),LEN(A1)-LEN(SUBSTITUTE(A1,"z",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"z","")),LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"0","")),LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"1","")),LEN(A1)-LEN(SUBSTITUTE(A1,"2",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"2","")),LEN(A1)-LEN(SUBSTITUTE(A1,"3",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"3","")),LEN(A1)-LEN(SUBSTITUTE(A1,"4",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"4","")),LEN(A1)-LEN(SUBSTITUTE(A1,"5",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"5","")),LEN(A1)-LEN(SUBSTITUTE(A1,"6",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"6","")),LEN(A1)-LEN(SUBSTITUTE(A1,"7",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"7","")),LEN(A1)-LEN(SUBSTITUTE(A1,"8",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"8","")),LEN(A1)-LEN(SUBSTITUTE(A1,"9",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"9",""))),TRUE,FALSE)

正如你所看到的，这是一个令人眼花缭乱的问题。它可以工作，但我需要将其应用于至少 100,000 行数据，并且我相信它太密集而无法可靠地工作。目前的解决方案是遍历每个字母并确保它们的计数匹配，如果它们都匹配，则返回 TRUE。

结论

我有一个可行的解决方案，它给出了一个需要的示例，但它笨拙且不可靠。我希望有一种更好的方法来利用 Excel 来更有效地完成这项任务。提前感谢您抽出宝贵时间接受采访！

我正在使用 MSO 365，Excel 版本 2202

if-语句 excel-formula 比较

评论

0赞 JvdV 11/17/2022
这需要区分大小写吗？例如：匹配吗？ddDDDd
0赞 Wisp 11/17/2022
感谢您的回复。请最好不要区分大小写。我只是将所有内容都小写，因为替换公式区分大小写，如果一个大写而一个不大写，就会不匹配。

答：

3赞 JvdV 11/17/2022 #1

非常好的问题。这是一个将溢出结果的选项：

enter image description here

公式：C2

=BYROW(A2:B6,LAMBDA(x,COUNTA(UNIQUE(MAP(x,LAMBDA(a,CONCAT(LET(b,MID(a,SEQUENCE(LEN(a)),1),c,CODE(UPPER(b)),SORT(IFERROR(--b,IF((c>64)*(c<91),b,""))))))),1))))=1

总之：

  • BYROW()- 遍历数据集中的每一行;
  • MAP()- 上述循环的每次迭代都会通过此函数传递两个值，以字符拆分每个元素，检查这些元素是否为数字，如果不是，则对照 ASCII 表进行检查，如果两者都不返回空字符串。 这些字符和结果重新组合在一起;CODE()SORT()CONCAT()
  • COUNTA(UNIQUE())- 用于测试上述迭代是否返回两个相同值的组合（不区分大小写）。

评论

0赞 Wisp 11/17/2022
我很欣赏这个回应，但不幸的是（除非我在这里做错了什么），在 MSO 365 版本 2202 中不可用。我无法使用更新的版本。是否有任何替代解决方案可以使用版本 2202？imgur.com/a/FjXKoLo另外 - 再次感谢 JvdV。你总是非常迅速地回答我奇怪而精彩的问题！=BYROW
1赞 JvdV 11/17/2022
@Wisp，你必须解构我的解决方案并测试.不幸的是，一个公式，你必须拖下来。=CONCAT(LET(a,MID(A2,SEQUENCE(LEN(A2)),1),b,CODE(UPPER(a)),SORT(IFERROR(--a,IF((b>64)*(b<91),a,"")))))=CONCAT(LET(a,MID(B2,SEQUENCE(LEN(B2)),1),b,CODE(UPPER(a)),SORT(IFERROR(--a,IF((b>64)*(b<91),a,"")))))
1赞 Wisp 11/18/2022
了不起。非常感谢您的帮助。一旦我的 Excel 版本支持，我肯定会切换到这个！=BYROW
2赞 Tom Sharpe 11/17/2022 #2

@JvdV的回答很好。

我决定编写一个 lambda CharFreq，将字符串拆分为多个字符，并使用 Frequency 生成字符和数字的频率。我的 lambda 在高级公式环境中如下所示：

=LAMBDA(string,
    LET(
        codes, SEARCH(
            MID(
                string,
                SEQUENCE(
                    LEN(
                        string
                    )
                ),
                1
            ),
            "0123456789abcdefghijklmnopqrstuvxyz"
        ),
        fcodes, FILTER(
            codes,
            ISNUMBER(codes)
        ),
        freq, FREQUENCY(
            fcodes,
            SEQUENCE(36)
        ),
        freq
    )
)

然后，我可以比较频率以识别任何差异：

=IF(SUM(--(CharFreq(A2)<>CharFreq(B2)))=0,"Match","No match")

enter image description here

注意

如果其中一个字符串中出现像“*”或“？”这样的通配符，这可能会产生错误的结果 - 可以通过使用lower而不是search的find或替换这些字符来修复。

4赞 Jos Woolley 11/17/2022 #3

替代使用：LEN

=AND(MMULT(LEN(A2:B2)-LEN(SUBSTITUTE(UPPER(A2:B2),MID("0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ",SEQUENCE(36),1),"")),{-1;1})=0)

并复制下来。

评论

0赞 Wisp 11/18/2022
谢谢你的回答。这运行良好，并且在我的 Excel 版本上得到了支持。我很感激你的帮助！

上一个：Excel concat 问题

下一个：在 Excel 中交叉比较两个不同的数据对以发现差异的有效方法是什么？