I have these 2 tables in two different sheets in excel. They both have common tabs but the only one I am concerned about is the student ID. What I would like to do is make changes to the student ID in the first table and have it reflected in the second tables student ID for data consistency. I am working in excel 2010 and everything I have researched is for later versions of excel. I have tried the special link technique but because these are 2 tables it does not work. The special link option does not appear. I am not sure if I need a script to do this or if excel 2010 has a built in way to do this.
我在excel中有两个表格。它们都有共同的选项卡，但我唯一关心的是学生ID，我想做的是对第一个表中的学生ID进行修改，并将其反映到第二个表中，以保证数据的一致性。我在excel2010工作，我所研究的一切都是针对excel的后续版本。我尝试过这种特殊的链接技术，但是因为它们是2个表，所以它不起作用。特殊链接选项没有出现。我不确定我是否需要一个脚本来完成这个任务，或者excel 2010是否有一个内置的脚本来完成这个任务。
First Table:
第一个表:
Student ID Last name Initial Age Program
STF348-245 Another L. 21 Drafting
STF348-246 Different R. 19 Science
STF348-247 Name G. 18 Arts
STF348-248 Going L. 23 Nursing
STF348-249 Up M. 37 Science
STF348-250 And J. 20 Arts
STF348-251 Down F. 26 Business
STF348-252 Different S. 22 Arts
STF348-253 Different W. 20 Nursing
STF348-254 Different L. 19 Drafting
Second Table:
第二个表:
Student ID Last name Initial Age Program
STF348-245 Another L. 21 Drafting
STF348-246 Different R. 19 Science
STF348-247 Name G. 18 Arts
STF348-248 Going L. 23 Nursing
STF348-249 Up M. 37 Science
STF348-250 And J. 20 Arts
STF348-251 Down F. 26 Business
STF348-252 Different S. 22 Arts
STF348-253 Different W. 20 Nursing
STF348-254 Different L. 19 Drafting
1 个解决方案
#1
1
With the exception of inserting or appending new rows, this can be accomplished using the VLOOKUP function only, assuming the Student ID field is a unique identifier.
除了插入或附加新行之外，这只能使用VLOOKUP函数来完成，假设Student ID字段是唯一标识符。
In the "Program" field of Table2, put:
在表2的“Program”字段中，输入:
=VLOOKUP([@[Student ID]],Table1,5,False))
Copy/Drag the formula down. Now any changes to Program on Table1 will be reflected in Table2.
复制/阻力公式。Table1上对程序的任何修改都会在Table2中反映出来。
Follow the same procedure for other columns, simply using the appropriate header name as the first argument to the function, and making sure to also change the column Index (5 in the above example).
对其他列执行相同的过程，只需使用适当的标题名称作为函数的第一个参数，并确保更改列索引(上面示例中的5)。
NB: This assumes the "first" table is named "Table1" -- if not, modify the formula accordingly.
NB:假设“第一个”表被命名为“表1”——如果不是，则相应地修改公式。
If you want to preserve the tables as strict duplicates of one another, including order, then you don't even need VLOOKUP. In Table2, just do:
如果希望将表保存为彼此的严格副本(包括order)，那么甚至不需要VLOOKUP。在表二,只做:
Student ID | Student Name | Last Name
=Table1[@[Student ID]] | =Table1[@[Student Name]] | =Table1[@[Last Name]]
#1
1
With the exception of inserting or appending new rows, this can be accomplished using the VLOOKUP function only, assuming the Student ID field is a unique identifier.
除了插入或附加新行之外，这只能使用VLOOKUP函数来完成，假设Student ID字段是唯一标识符。
In the "Program" field of Table2, put:
在表2的“Program”字段中，输入:
=VLOOKUP([@[Student ID]],Table1,5,False))
Copy/Drag the formula down. Now any changes to Program on Table1 will be reflected in Table2.
复制/阻力公式。Table1上对程序的任何修改都会在Table2中反映出来。
Follow the same procedure for other columns, simply using the appropriate header name as the first argument to the function, and making sure to also change the column Index (5 in the above example).
对其他列执行相同的过程，只需使用适当的标题名称作为函数的第一个参数，并确保更改列索引(上面示例中的5)。
NB: This assumes the "first" table is named "Table1" -- if not, modify the formula accordingly.
NB:假设“第一个”表被命名为“表1”——如果不是，则相应地修改公式。
If you want to preserve the tables as strict duplicates of one another, including order, then you don't even need VLOOKUP. In Table2, just do:
如果希望将表保存为彼此的严格副本(包括order)，那么甚至不需要VLOOKUP。在表二,只做:
Student ID | Student Name | Last Name
=Table1[@[Student ID]] | =Table1[@[Student Name]] | =Table1[@[Last Name]]