自动填充其他工作表中的数据 [英] Automatically fill data from another sheet
问题描述
我想自动填写工作表A ，其值为 Excel 2013中的工作表B 。我的数据在同一工作簿中有两张。
I would like to auto-fill Sheet A with values from Sheet B in Excel 2013. My data are in two sheets in the same workbook.
=========== Sheet 1 =========== =========== Sheet 2 ===========
location year val1 val2 location year val1 val2
USA.VT 1999 USA.VT 1999 6 3
USA.VT 2000 USA.VT 2000 3 2
USA.VT 2001 USA.VT 2001 4 1
USA.VT 2002 USA.VT 2002 9 5
USA.NH 1999 USA.NH 1999 3 6
USA.NH 2000 USA.NH 2002 12 56
USA.NH 2001 USA.ME 1999 3 16
USA.NH 2002 USA.ME 2002 4 5
USA.ME 1999
USA.ME 2000
USA.ME 2001
USA.ME 2002
我想用一些函数或公式来自动填充表1基于表2中的值，根据： location ，年和列（ val1 或 val2 ）。不匹配将为零填充。
I would like to use some function or formula to automatically populate Sheet 1 based on the values in Sheet 2 according to: location, year, and the column (val1 or val2). Non-matches would be zero-filled.
这将导致以下结果：
=========== Sheet 1 ===========
location year val1 val2
USA.VT 1999 6 3
USA.VT 2000 3 2
USA.VT 2001 4 1
USA.VT 2002 9 5
USA.NH 1999 3 6
USA.NH 2000 0 0
USA.NH 2001 0 0
USA.NH 2002 12 56
USA.ME 1999 3 16
USA.ME 2000 0 0
USA.ME 2001 0 0
USA.ME 2002 4 5
我尝试过VLOOKUP，INDEX和MATCH，但我没有运气。
I have tried VLOOKUP, INDEX, and MATCH, but I'm having no luck.
任何帮助都将不胜感激！
Any help would be greatly appreciated!
推荐答案
将此数组公式放在C2中：
Put this Array formula in C2:
=IFERROR(INDEX(Sheet2!C$2:C$9,MATCH($A2&$B2,Sheet2!$A$2:$A$9&Sheet2!$B$2:$B$9,0)),0)
作为一个数组formu你必须用Ctrl-Shift-Enter确认退出编辑模式而不是回车。
Being an array formula you must confirm with Ctrl-Shift-Enter to exit the edit mode instead of Enter.
然后复制一列并向下。
图片不准确，因为我把它放在一张纸上。
The picture is not exact because I left it on one sheet.
这篇关于自动填充其他工作表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
