Excel查询与多个查询 [英] Excel Lookup with multiple queries

问题描述

我有一个问题，我可能没有正确的思考。但是我有一个很长的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& {Array of Employee_Name2}

Begin_Date2< = Date_Worked1> End_Date2

返回员工姓名

一旦我有员工姓名，我可以做一个sumifs =（），并获得他们工作的总时间没有问题。

我已经尝试了一些索引匹配功能的组合，使用ctrl-shift-enter ...并且无法弄清楚它。任何帮助将不胜感激。

解决方案

这样做的一种方法是首先在条目右侧创建一个附加列在您尝试从employee中拉出的工作表中： = ROW（）

然后可以使用数组你想要实现的方式来拉取相应的匹配行：

{= SUM（（project_name1 = projectname2）*（employeename1& > employeename2）*（begindate< = date_worked1）*（date_worked1> end_date2）*（match_column））}

然后可以使用在索引中返回match_column条目，如您所述，以检索相应的条目。

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.

解决方案

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.

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