比较Excel中的两列,插入移动关联数据的空白行 [英] Comparing two columns in excel, inserting blank rows moving associated data
问题描述
我有一大批数据需要与列匹配。具体来说,数据目前看起来像:
列1列2列3
1 1 aaaa
2 3 bbbb
3 4 cccc
4
理想情况下,看起来像:
列1列2列3
1 1 aaaa
2
3 3 bbbb
4 4 cccc
我看过不同的比较列1和第2列(类似于这个),但是没有找到一个很好的方式来)a)插入第1列和第2列不匹配的空格,b)也附加列3中的数据。
任何帮助将不胜感激!谢谢!
如果你想/可以直接使用Excel:
在单元格中ColD表示这个公式:
= IFERROR(MATCH(A2; $ B $ 2:$ B $ 8; )
在ColE的单元格中,放这个公式:
= IF(D2"; INDEX($ B $ 2:$ C $ 8; D2; 1);)
在ColF的单元格中,使用以下公式:
= IF(D2<"; INDEX($ B $ 2:$ C $ 8; D2; 2);)
/ pre>
复制并粘贴所有单元格。
好的,这是它是自动更新的...或者完成复制和粘贴的价值。I have a large set of data in excel that needs to be matched by column. Specifically, the data currently looks like:
Column 1 Column 2 Column 3 1 1 aaaa 2 3 bbbb 3 4 cccc 4
Ideally, I want the data to look like:
Column 1 Column 2 Column 3 1 1 aaaa 2 3 3 bbbb 4 4 cccc
I've looked at different ways of comparing Column 1 and Column 2 (similar to this), but haven't found a good way to a) insert the blank space where column 1 and 2 don't match and b) also attach the data in column 3.
Any help would be greatly appreciated! Thanks!
解决方案If you want / can use directly Excel:
In the cells of Col "D" put this formula:
=IFERROR(MATCH(A2;$B$2:$B$8;);"")
In the cells of Col "E" put this formula:
=IF(D2<>"";INDEX($B$2:$C$8;D2;1);"")
In the cells of Col "F" put this formula:
=IF(D2<>"";INDEX($B$2:$C$8;D2;2);"")
Copy and paste for all the cells. The good think it's that it's autoupdated... Or when finish copy and paste with value.
这篇关于比较Excel中的两列,插入移动关联数据的空白行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!