![自动将数据从 Sheet 1 填充至 Sheet 2](https://linux22.com/image/1586821/%E8%87%AA%E5%8A%A8%E5%B0%86%E6%95%B0%E6%8D%AE%E4%BB%8E%20Sheet%201%20%E5%A1%AB%E5%85%85%E8%87%B3%20Sheet%202.png)
答案1
因为我知道您只想查看“员工”及其“经理”,所以我为他们创建了一些公式。我不确定如何让它与“总经理”一起工作。
(注意:Sheet1
使用您实际的第一张工作表名称进行更改。如果您的工作表有空格,请使用单引号,例如'1st sheet'
)
B3
(Enter输入公式):
="Manager"
C3
(Ctrl+ Shift+Enter进入大批公式):
=IFERROR(INDEX(Sheet1!C:C,MATCH(1,(Sheet1!B:B=Sheet1!$B3)*(Sheet1!D:D=$G3),0)),"")
D3
(Enter输入公式):
=IFERROR(Sheet1!$G3,"")
E3
(Enter输入公式):
="Employee"
F3
(Ctrl+ Shift+Enter进入大批公式):
=IFERROR(
INDEX(Sheet1!$C:$C,
MATCH(1,(1=INDIRECT("Sheet1!"&ADDRESS(1,MATCH($A3,Sheet1!$5:$5,0))
&":"&ADDRESS(1048576,MATCH($A3,Sheet1!$5:$5,0))))
*(Sheet1!$B:$B=Sheet1!$E3),0)),"")
G3
(Ctrl+ Shift+Enter进入大批公式):
=IFERROR(
INDEX(Sheet1!$D:$D,
MATCH(1,(1=INDIRECT("Sheet1!"&ADDRESS(1,MATCH($A3,Sheet1!$5:$5,0))
&":"&ADDRESS(1048576,MATCH($A3,Sheet1!$5:$5,0))))
*(Sheet1!$B:$B=Sheet1!$E3),0)),"")