具有多个查询的 Excel 查找
我有一个问题，我可能没有正确思考。但我有一个很长的 Excel 文件，我从其他地方提取了以下几列：
Project_Name1、Employee_Name1、Date_Worked1、Hours_Worked1
在另一张表中，我有这些列
Project_Name2、Employee_Name2、Begin_Date2、End_Date2、Hours_Worked2
第二张表填充了数据，并且工作得很好。 然而，事实证明我有一些我不知道名字的员工也在从事同一个项目。我需要找出员工的姓名，然后将他们在给定时间段内的工作小时数相加。 因此，我需要使用三个条件进行查找：
Project_Name1 = Project_Name2
Employee_Name1 <> {Employee_Name2 数组}
Begin_Date2 <= Date_Worked1 > End_Date2
返回员工姓名。
一旦我有了员工姓名，我就可以执行 sumifs=() 并获得他们的总工作时间，没有问题。
我已经尝试了多种索引匹配功能的组合，使用 ctrl-shift-enter... 但无法弄清楚它。任何帮助将不胜感激。
I have a question that I a may not be thinking correctly about. But I have an a long excel file that I pull from somewhere else with the following columns:
Project_Name1, Employee_Name1, Date_Worked1, Hours_Worked1
In another sheet I have these columns
Project_Name2, Employee_Name2, Begin_Date2, End_Date2, Hours_Worked2
This second sheet is filled with data, and works just fine.
However, it turns out that I have some employee names that I do not know that are also working on the same project. I need to figure out the names of the employees and then sum the number of hours they worked for a given period.
So I need a lookup with three criteria:
Project_Name1 = Project_Name2
Employee_Name1 <> {Array of Employee_Name2}
Begin_Date2 <= Date_Worked1 > End_Date2
Returning Employee name.
Once I have the employee name, I can do a sumifs=() and get the total hours they worked no problem.
I have tried a number of combinations of Index Match functions, using ctrl-shift-enter... and have not been able to figure out it. Any help would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您所说的操作非常复杂，并且有点超出了 Excel 默认设计的功能。但是，您可以使用一些解决方法来尝试获取您正在查找的信息。
http:// /office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx
What you're talking about doing is extremely complicated and a little bit past what Excel was designed to do by default. However, there are a few workarounds that you can use to attempt to get the information that you're looking for.
http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx
一种方法是首先在您尝试从中提取员工姓名的工作表条目右侧创建一个附加列：
=ROW()然后您可以使用像您这样的数组公式正在尝试实现拉取相应的“匹配”行：
{=SUM((project_name1=projectname2)*(employeename1<>employeename2)*(begindate<=date_worked1)*(date_worked1>end_date2)*(match_column))}然后您可以使用此返回的 match_column正如您所描述的那样，在索引中输入条目以检索适当的条目。
One way to do this would be to first create an additional column to the right of entries on the sheet you're trying to pull employee_name from:
=ROW()You could then use an array formula like you were trying to implement to pull the corresponding 'match' row:
{=SUM((project_name1=projectname2)*(employeename1<>employeename2)*(begindate<=date_worked1)*(date_worked1>end_date2)*(match_column))}You could then use this returned match_column entry within the index as you described to retrieve the appropriate entries.