英文:
subtracting values between columns both ways
问题
我需要找到在第一列而不在第二列中的值，反之亦然。可以看起来像这样：取第一列中的第一行，查看第二列中是否有相同的数字，如果有，则在第三列中写入0（减法），如果没有，则写入要查找的数字或错误，不要紧。这应该适用于双向（某些数字可能在第二列中，但不在第一列中，这些我也需要找到）。所以可能会有2个公式在2个列中，一个从第一列搜索到第二列，另一个从第二列搜索到第一列。例如，如果第一列中有某个值两次，而第二列中只有一次，那么对于第一个数字应该显示0，对于第二个数字应该显示错误或要查找的数字。
数据集看起来像这样：
| Col1 | Col2. |
|---|---|
| 42646 | |
| 55 | 42646 |
| 77 | |
| 33 | |
| 25 | 77 |
| Col3 | Col4 |
|---|---|
| 0 | |
| 55 | 0 |
| 0 | |
| 33(或错误，NA等) | |
| 25 | 0 |
我尝试过使用VLOOKUP，但没有成功。
英文:
I need to find those values that are in column 1 and not in column 2 and vica versa. It can look like this: take fist row in the first column and look if there is same number in the second column if so then on the third column write 0 (substraction) and if there won't be the same number then write searched number or error, doesn't matter. This should work both ways (some numbers can be in col2 but not in col1, those i need to find aswell). So probably there would be 2 formulas in 2 columns. one searching from col1 to col2, and same for col2 to col1. And if there for example in col1 would be twice some value and in col2 just once, than it should show for the first number 0 and for second number error or searched number.
Dataset looks like this:
| Col1 | Col2. |
|---|---|
| 42646 | |
| 55 | 42646 |
| 77 | |
| 33 | |
| 25 | 77 |
| Col3 | Col4 |
|---|---|
| 0 | |
| 55 | 0 |
| 0 | |
| 33(or error,NA etc) | |
| 25 | 0 |
I have tried vlook up, but wasn't sucesfull.
答案1
得分: 2
这是你要找的内容。你可以在 Col3 中使用以下公式：
=IF(A2:A6="", "", IF(ISNA(XMATCH(A2:A6, B2:B6)), A2:A6, 0))
以及在 Col4 中使用以下公式：
=IF(B2:B6="", "", IF(ISNA(XMATCH(B2:B6, A2:A6)), B2:B6, 0))
这两个公式如果找到值（包括空值），则返回 0，否则返回缺失的值。
你可以使用 HSTACK 将它们合并在一起：
=HSTACK(IF(A2:A6="", "", IF(ISNA(XMATCH(A2:A6, B2:B6)), A2:A6, 0)),
IF(B2:B6="", "", IF(ISNA(XMATCH(B2:B6, A2:A6)), B2:B6, 0))
或者使用 LET 来避免重复：
=LET(A, A2:A6, B, B2:B6, HSTACK(IF(A="", "", IF(ISNA(XMATCH(A, B)), A, 0)),
IF(B="", "", IF(ISNA(XMATCH(B, A)), B, 0)))
这是输出：
你也可以使用 XLOOKUP，但公式会更长，因为需要前三个输入参数：
=IF(ISNA(XLOOKUP(A2:A6, B2:B6, A2:A6)), A2:A6, 0)
英文:
I guess this is what you are looking for. You can use for Col3:
=IF(A2:A6="", "",IF(ISNA(XMATCH(A2:A6,B2:B6)),A2:A6,0))
and for Col4:
=IF(B2:B6="", "", IF(ISNA(XMATCH(B2:B6,A2:A6)),B2:B6,0))
Both formulas returns 0 if the value was found (including blanks), otherwise the missing value.
You can put all together using HSTACK:
= HSTACK(IF(A2:A6="", "",IF(ISNA(XMATCH(A2:A6,B2:B6)),A2:A6,0)),
IF(B2:B6="", "", IF(ISNA(XMATCH(B2:B6,A2:A6)),B2:B6,0)))
Or using LET to avoid repetitions.
= LET(A, A2:A6, B, B2:B6, HSTACK(IF(A="","",IF(ISNA(XMATCH(A,B)),A,0)),
IF(B="", "", IF(ISNA(XMATCH(B,A)),B,0))))
Here is the output:
You can use XLOOKUP too, but the formula is longer, because the first three input arguments are required:
=IF(ISNA(XLOOKUP(A2:A6,B2:B6, A2:A6)),A2:A6,0)
答案2
得分: 0
抱歉，我无法提供代码的翻译。以下是您要求的文本的翻译部分：
"Your requirements make this a little tricky, but try:"
"您的要求使这有点棘手，但可以尝试："
"Formula in C1:"
"C1单元格中的公式："
"Formula in D1:"
"D1单元格中的公式："
英文:
A shame you haven't added a sample in your data that would show what you meant with:
> "And if there for example in col1 would be twice some value and in col2 just once, than it should show for the first number 0 and for second number error or searched number."
Your requirements make this a little tricky, but try:
Formula in C1:
=IF(A1="","",IF(COUNTIF(B:B,A1)-COUNTIF(A$1:A1,A1)<0,A1,0))
Formula in D1:
=IF(B1="","",IF(COUNTIF(A:A,B1)-COUNTIF(B$1:B1,B1)<0,B1,0))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论