用相同ID的值填充Excel中的空白单元格 [英] Fill blank cell in Excel with value with same ID
问题描述
我的excel不太先进,如果可以的话,我需要帮助.我在此专栏文章中有一个出色表现:ID-值1-值2
I'm not so advanced in excel I need help if is it possible to do. I have an excel with this column: ID - Value1 - Value2
我需要更新value1(或value2):-仅当为空白且仅在该行具有相同的ID时
I need to update the value1 (or value2): - only if are blank AND only if the row has the same ID
我举个例子,我有这张桌子:
I make an example, I have this table:
ID Value1 Value2
A1 1 100
A2 2
A3 3
A4 1 200
A5 2 250
我想以此方式填充所有空白单元格
Iwould like to fill all the empty cell in this way
ID Value1 Value2
A1 1 100 200
A2 2 250
A3 3
A4 1 100 200
A5 2 250
谢谢您的帮助.
推荐答案
好,因此您的数据是数字和文本的混合.您可以使用公式生成两个具有所需值的新列.选择两个新的相邻列,分别为D和E,然后在单元格 D2
中的行中输入此 CSE公式:
Ok, so your data is a mixture of numbers and texts. You can generate two new columns with the desired values using formulas. Choose two new, adjacent columns, say D and E, and enter this CSE formula in row say in cell D2
:
D2:
=IFERROR(IF(ISBLANK(B2),INDEX(B$2:B$99,
MATCH(1,($A$2:$A$99=$A2)*(1-ISBLANK(B$2:B$99)),0)),B2), "")
Ctrl Shift Enter
使用 Ctrl Shift Enter 在 D2
中输入公式,然后将其复制/粘贴到列的单元格中D和E(或我所说的任何两个相邻列).
Enter the formula in D2
with CtrlShiftEnter and then copy/paste in the cells of columns D and E (or as I said any two adjacent columns).
这篇关于用相同ID的值填充Excel中的空白单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!