Excel十字表查找公式 [英] Excel Cross Sheet Lookup Formula

问题描述

我目前在Excel中达到极限，可以使用几个指针。我有一个用户列表和他们所在的团队以及一个活动号码。我想能够交叉参考团队计划的哪个事件号码，并将该条目填充到单独的字段中。然后分解每个事件的每个办公室的用户数量。我将在下面给出一个例子，因为它可能更容易。

表1 - 列1,2和3中的数字纯粹是该团队中的数字

 < table>< tbody>< tr>< th>团队名称< ; / th>< th> Team< th>< th>< th> 2< th>< th>< / th>< / tr>< tr& ;< td>团队A< / td>< td> 5< / td>< td> < / TD>< TD> < / td>< / td>< / tr>< tr>< td> ;< TD> < / TD>< TD> < / td>< / tr>< tr>< td>团队C< / td>< td> 15< / td>< td> < / TD>< TD> 5℃; / TD>< TD> < / td>< / tr>< / tbody>< / table>  

表2

 < table>< ; tbody>< tr>团队名称< / th>< th>用户名< / th>< th>位置< / th>< th>事件< / th>< / tr> < tr>< td>< / td>< td>< / td>< td> < td>团队A< / td>< td> Rick< / td>< td>格拉斯哥< / td>< td> 3< / td>< / tr>< tr>< td>团队A< / td>< td> Mary< / td>< td>格拉斯哥< / td>< td> 3< / td>< / tr>< tr>< td& td>< td> Sue< / td>< td>伦敦< / td>< td> 1< / td>< / tr>< tr>< td& t; td> Jill< / td>< td> Brighton< / td>< td> 1< / td>< / tr>< tr>< td>团队C< / td>< td> Sally< / td>< td>伦敦< / td>< td> 2< / td>< / tr>< tr>< td>团队C< / td>< td>标记< / td> ;< td> Brighton< / td>< td> 2< / td>< / tr>< tr>< td>团队C< / td>< td> Harry< / td>< td> ;伦敦< / TD>< TD> 2'; / TD>< / TR>< / tbody的>< /表>  

我正在尝试为一个团队编写一个表单1的公式，然后查找他们所处的事件，然后填充该值在表2的事件列中。从那里我可以计算出每个事件中每个办公室的数量。

我希望我已经解释了，如果不是只是让我知道，我会澄清。我还需要考虑一个团队在多个事件上的分裂，但是我可以稍后处理。

解决方案

尝试这个公式：

  = IF（VLOOKUP（A2，Sheet1！$ A $ 2：$ E $ 4,3，FALSE）> 0， Event1，
 IF（VLOOKUP（A2，Sheet1！$ A $ 2：$ E $ 4,4，FALSE）> 0，Event2，Event3））
  

I am reaching my limits with Excel at the moment and could use a few pointers. I have a list of users and the teams they are in as well as an event number. I want to be able to cross reference which event number the teams are scheduled for and populate that entry into a separate field. Then breakdown how many users per office are at each event. I will give an example below as it's probably easier.

Sheet 1 - The numbers in columns 1,2 and 3 are purely the number from that team in that

<table><tbody><tr><th>Team Name</th><th>Number in Team</th><th>1</th><th>2</th><th>3</th></tr><tr><td>Team A</td><td>5</td><td> </td><td> </td><td>5</td></tr><tr><td>Team B</td><td>12</td><td>12</td><td> </td><td> </td></tr><tr><td>Team C</td><td>15</td><td> </td><td>5</td><td> </td></tr></tbody></table>​

Sheet 2

<table><tbody><tr><th>Team Name</th><th>User Name</th><th>Location</th><th>Event</th></tr><tr><td>Team A</td><td>Bob</td><td>London</td><td>3</td></tr><tr><td>Team A</td><td>Rick</td><td>Glasgow</td><td>3</td></tr><tr><td>Team A</td><td>Mary</td><td>Glasgow</td><td>3</td></tr><tr><td>Team B</td><td>Sue</td><td>London</td><td>1</td></tr><tr><td>Team B</td><td>Jill</td><td>Brighton</td><td>1</td></tr><tr><td>Team C</td><td>Sally</td><td>London</td><td>2</td></tr><tr><td>Team C</td><td>Mark</td><td>Brighton</td><td>2</td></tr><tr><td>Team C</td><td>Harry</td><td>London</td><td>2</td></tr></tbody></table>​

I am trying to write a formula that for looks in sheet 1 for a team and then finds which event(s) they are on and then populate that value in the events column of sheet 2. From there I can work out how many per office are being are at each event.

I hope I have explained it OK, if not just let me know and I will clarify. I also need to think about a team being split over multiple events, but I can deal with that later.

解决方案

Try this formula:

=IF(VLOOKUP(A2,Sheet1!$A$2:$E$4,3,FALSE)>0,"Event1",
IF(VLOOKUP(A2,Sheet1!$A$2:$E$4,4,FALSE)>0,"Event2","Event3"))

这篇关于Excel十字表查找公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！