比较单元格中逗号分隔的数字 [英] Comparing comma separated numbers in cells

问题描述

我试图将Reachability Set列中的数字与Antecedent Set列同一行中的数字进行比较，并返回Intersection Set列相应单元格中的公用值.

I am trying to compare the numbers in the Reachability Set column with the numbers in the same row of the Antecedent Set column and return the common values in the corresponding cells of the Intersection Set column.

屏幕截图:

Screenshot:

推荐答案

在Excel 2016(但不是Excel 2013)中，可以使用以下输入数组的公式.

In Excel 2016 (but NOT Excel 2013), you can use the following array-entered formula.

=TEXTJOIN(",",TRUE,IFERROR(1/(1/(ISNUMBER(FIND(","&TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99))&",",","&A2&","))))*TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99)),""))

seq_99 是一个命名公式

Refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

要输入数组公式，请在单元格中输入公式后，在按下 enter 的同时按住 ctrl + shift 进行确认.如果操作正确，Excel会在公式周围放置大括号{...}.

To enter an array formula, after entering the formula in the cell, confirm by holding down ctrl + shift while hitting enter. If you do it correctly, Excel will place braces {...} around the formula.

这篇关于比较单元格中逗号分隔的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！