Excel 交叉表查找公式 [英] Excel Cross Sheet Lookup Formula

问题描述

我目前使用 Excel 已达到极限，可以使用一些提示.我有一个用户列表和他们所在的团队以及一个事件编号.我希望能够交叉引用团队计划参加的事件编号，并将该条目填充到单独的字段中.然后细分每个办公室在每次活动中的用户数量.我将在下面举一个例子，因为它可能更容易.

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.

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

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>​

工作表 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>​

我正在尝试编写一个公式，用于在工作表 1 中查找团队，然后找到他们正在进行的事件，然后在工作表 2 的事件列中填充该值.从那里我可以计算出如何每个办公室都有很多人参加每个活动.

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.

推荐答案

试试这个公式:

=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屋！