Excel - 查找值并复制到不同的工作表中 [英] Excel - find a value and copy into different sheets

问题描述

我需要一些帮助我正在努力制作一本标记书。
我有一本工作簿，有一张标有Master的表格，其中包含学生A2中的唯一参考编号，他们的名字为B2 / C2，然后是他们在第D2，E2，F2和G2栏中学习的四门科目。然后，我还为每个单独的科目分别设置了单页。

I need some help with a formula i am trying to make for a mark book. I have a work book, with a sheet labelled "Master" that contains students unique reference numbers in col A2, their names B2/C2 and then the four subjects they study in col D2,E2,F2 and G2. I also then have separate sheets for each individual subject.

我想在主表上查找主题（在四列中），如果数学是学生参加其中一个专栏的主题，我希望将该行中相应的学生姓名和参考文献复制并粘贴到数学表中，以便输入有关该科目/学生和班级的更多信息。

I would like to look up the subjects on the master sheet (in the four columns) and if "Maths" is a subject a student takes in one of those columns, i would like the corresponding student name and reference from that row to be copied and pasted into the sheet "Maths" so further information can be entered regarding that subject/student and class.

我尝试过使用IF和MATCH公式，但似乎变得非常复杂，因为我有400多名学生，每个人都有四个科目，而且科目可以出现在任何人身上四个主题栏中。

I have tried working with a IF and MATCH formula but it seems to get quite complex, as I have over 400 students who each have four subjects, and the subjects can appear in anyone of the four subject columns.

有任何建议吗？

TIA！

推荐答案

有两种可能的解决方案，最简单的是：

There are two possible solutions, the easiest is:

在数学表中，插入：

[cell B2 ]

=IF(Sheet1!D2="Math";Sheet1!B2;IF(Sheet1!E2="Math";Sheet1!B2;IF(Sheet1!F2="Math";Sheet1!B2;IF(Sheet1!G2="Math";Sheet1!B2;""))))

[cell C2 ]

=IF(Sheet1!D2="Math";Sheet1!C2;IF(Sheet1!E2="Math";Sheet1!C2;IF(Sheet1!F2="Math";Sheet1!C2;IF(Sheet1!G2="Math";Sheet1!B2;""))))

然后将它们拖到文档中......

And drag them down trough the document ...

以下是我做的样本测试：

Here is a sample test I made:

学生表：

-------------
1001    Toni    Almeida     Math        Portuguese  Programming     Another
1002    David   P           Portuguese  French      Programming     Another
1003    Neil    C           Math        Portuguese  Programming     Another
1004    James   H           Portuguese  French      Programming     Another
-------------

结果（数学表）：

-------------
1001    Toni    Almeida 
1002    
1003    Neil    C   
1004    
-------------

---

您还可以创建查询：

---

You can also create a query:

注意：这至少在 Google电子表格，我没有MS Office在Excel中对此进行测试。

Note: This works at least in Google Spreadsheet, I don't have MS Office to test this in Excel.

=query(B2:G5;"select B,C where D='Math'OR E='Math' OR F='Math' OR G='Math'";1)

结果：

-------------
1001    Toni    Almeida 
1003    Neil    C   
-------------

这篇关于Excel - 查找值并复制到不同的工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！