excel中vlookup
This article is for all excel wizards, conversant with majority of excel features and functions but have not realised the potential of Power Query for data extraction and transformation automations and ease of use.
本文适用于所有excel向导，已熟悉大多数excel特性和功能，但尚未意识到Power Query在数据提取和转换自动化以及易用性方面的潜力。
Power Query in itself is a powerful tool and needs mutiple articles to cover the features; however, this article will provide a brief and explain the use of Power Query to overcome the limitations of VLOOKUP, one of the most used function in excel. VLookup helps in deriving/sourcing the value from another table or sheet arranged vertically for lookup values in the current table. As an excel user, the first activity before performing any kind of analysis is — Data preparation and VLookup helps in sourcing the relevant data for analysis.
Power Query本身是一个功能强大的工具，并且需要多篇文章来介绍这些功能。 但是，本文将简要介绍并解释如何使用Power Query克服VLOOKUP的限制，VLOOKUP是excel中最常用的功能之一。 VLookup有助于从垂直排列的另一个表或工作表中获取/获取值，以在当前表中查找值。 作为excel用户，执行任何类型的分析之前的第一个活动是-数据准备和VLookup帮助提供相关数据以进行分析。
However, this function has its own challenges and limitations which can be easily resolved with Power Query or in other words Advanced VLookup can be performed using Power Query. But, lets start with the basics first!!
但是，此功能有其自身的挑战和局限性，可以通过Power Query轻松解决，换句话说，可以使用Power Query执行Advanced VLookup。 但是，让我们先从基础开始！！
What is Power Query and how it can help
什么是Power Query及其如何提供帮助
Power Query is a data preparation and transformation engine inbuilt in Microsoft Excel.With Power Query, the data extracion/sourcing and transformation tasks can be done with increased efficiency and can be automated without using any codes or functions, with just few clicks. Now, coming back to the issue in hand which is ‘how to overcome limitations of Vlookup’.
Power Query是Microsoft Excel中内置的数据准备和转换引擎，借助Power Query，数据提取/采购和转换任务可以提高效率，并且无需单击任何代码或功能即可自动执行。 现在，回到手头的问题，即“如何克服Vlookup的局限性”。
Vlookup局限性 (Vlookup Limitations)
Scenario 1: Vlookup can only look to the right-In the illustration below, there are two tables -Lookup Table(yellow) and Employee Salary Table(Red). In the Employee_Salary_Table, we need to obtain Name (First Name and Last Name) of the employee from the Lookup table on the basis of lookup values in Employee ID column in Employee_Salary_Table. This seems quite easy; however, VLookup can not be used in this case as the Name values (First Name and Last name) are stored in the columns to the left of Employee ID in the Lookup Table. This may see bizarre but Vlookup function can only be used to obtain values stored in the columns which are to the right of the matching columns in the Lookup Table.
方案1：Vlookup只能向右看-在下图中，有两个表-查找表(黄色)和员工薪水表(红色)。 在Employee_Salary_Table中，我们需要根据Employee_Salary_Table中Employee ID列中的查找值从Lookup表中获取员工的名字(名字和姓氏)。 这似乎很容易。 但是，在这种情况下，不能使用VLookup，因为名称值(名字和姓氏)存储在查找表中员工ID左侧的列中。 这可能看起来很奇怪，但是Vlookup函数只能用于获取存储在“查找表”中匹配列右侧的列中的值。
Scenario 2: VLookup finds the first match in the Lookup table and return the corresponding value- In the image below, if details of the employees (First name, Last name and Employee ID) working in the departments specified in the Department Table is derived using VLookup functio,we would only obtain details of the employee first matched on the basis of Department. Vlookup would fail to provide details of all employees working in the department specified in the Department column in Department Table.
方案2： VLookup在“查找”表中找到第一个匹配项并返回相应的值-在下图中，如果使用“部门表”中指定的部门工作的雇员的详细信息(名字，姓氏和雇员ID)是使用VLookup功能，我们将仅根据部门获取首先匹配的员工的详细信息。 Vlookup将无法提供在“部门表”的“部门”列中指定的部门工作的所有员工的详细信息。
Scenario 3: VLookup can only look for one Lookup Column not mutliple lookup column combinations
方案3：VLookup只能查找一个查找列，而不能查找多个查找列组合
In the above illustration, if we use VLookup to retrive value of Employee ID in the Employee_Table from the Lookup Table on the basis of First name, the employee ID for the employee with the same first name (E.g: Carol in the Employee_Table would be retruned as A001) would be returned as same; irrespective of their different last name. To get the correct result in this case, the lookup value should be a combination of First and Last name. However VLookup can only match values on the basis of one lookup column. We can use a workaround by concatinating the First and Last name but that would increase the complexity in large data and increase of processing time.
在上图中，如果我们使用VLookup根据名字从查找表中检索Employee_Table中Employee ID的值，则具有相同名字的员工的雇员ID(例如：Employee_Table中的Carol与A001相同)将返回； 不管他们的姓氏不同。 为了在这种情况下获得正确的结果，查找值应该是名字和姓氏的组合。 但是，VLookup只能根据一个查询列匹配值。 我们可以通过混用名字和姓氏来使用变通办法，但这会增加大数据的复杂性并增加处理时间。
Power Query to resolve these limitations:
使用Power Query解决这些限制：
Power Query can easily overcome these challenges of VLookup and offers much more flexibility in data sourcing/extraction. I would describe more Power Query features in my future articles but lets focus here on performing VLookup using Power Query!
Power Query可以轻松克服VLookup的这些挑战，并在数据源/提取方面提供更大的灵活性。 我将在以后的文章中介绍更多的Power Query功能，但在这里让我们重点介绍如何使用Power Query执行VLookup！
VLookup is essentially merging of data from two Tables/Sheets. Let’s see it through an example and take the scenario 1 where First name and Last name are to derived in the Employee_Salary_Table from the Lookup table. The process of merging these two tables would be as follows:
VLookup本质上是合并来自两个表/表的数据。 让我们通过一个示例进行查看，并采用场景1，其中，“姓氏”和“姓氏”将在Lookup表的Employee_Salary_Table中派生。 合并这两个表的过程如下：
- As Power Query works with Queries , first we need to convert both these tables into queries one by one. 当Power Query与Queries一起使用时，首先我们需要将这两个表一张一张地转换成查询。
- We can start with any of the table. Here, I have started with Employee_Salary_Table, select it and click on Data tab> In the ‘Get & Transform Data section’, select the ‘From Table/Range’ icon as shown in the image below. This will open up the ‘Power Query Editor window’ and the table would automatically be converted to a Query. Here, any transformation required to the table data can also be done. Click Close & Load(Choose Create Connection Only). 我们可以从任何表格开始。 在这里，我从Employee_Salary_Table开始，选择它并单击Data选项卡>在“ Get＆Transform Data”部分中，选择“ From Table / Range”图标，如下图所示。 这将打开“ Power Query Editor窗口”，并且表格将自动转换为Query。 在这里，还可以对表数据进行任何所需的转换。 单击关闭并加载(仅选择创建连接)。
3. Repeat the process with Lookup Table. Now we have two queries for these two tables (visible in the Queries & Connections Pane). From the ‘Get Data’, dropdown > select Combine Queries>Merge.
3.使用“查找表”重复该过程。 现在，我们对这两个表有两个查询(在“查询和连接”窗格中可见)。 从“获取数据”下拉菜单中，选择“合并查询”>“合并”。
4. Selecting ‘Merge’ would open up the Merge window as shown below. Choose the Queries one by one from the dropdown. It is better to select the table in which data to be sourced /derived as first. Choose the join kind, which by default would be Left Outer Join. Also select the column to be matched in both the tables . Here, I have selected ‘Employee ID’ in both the tables.
4.选择“合并”将打开“合并”窗口，如下所示。 从下拉列表中一一选择查询。 最好首先选择要在其中导出/导出数据的表。 选择联接类型，默认情况下为“左外部联接”。 同时在两个表中选择要匹配的列。 在这里，我在两个表中都选择了“雇员ID”。
5. The joins work like in SQL and can be chosen as per your requirement from the available options — Left Outer Join, Right Outer Join, Full Outer Join, Inner Join, Left Anti and Right Anti.
5.联接的工作方式类似于SQL，可以根据需要从可用选项中选择-左外部联接，右外部联接，完全外部联接，内部联接，左反和右反。
6. On clicking ‘OK’ , the tables would be merged. Select the icon highlighed in the image below in the Lookup_Table header to select the columns that needs to be derived/sourced from Lookup Table. In our case, it is First name and Last Name. We can choose as many columns as required from the Lookup Table without the need to apply VLookup function multiple times.
6.单击“确定”，将合并表。 选择下图在Lookup_Table标头中高亮显示的图标，以选择需要从Lookup Table派生/派生的列。 在我们的例子中，它是名字和姓氏。 我们可以从“查找表”中选择所需的任意列，而无需多次应用VLookup函数。
7. After Column selection in the Lookup_table, click Close& Load. The merged data will be opened in another sheet in the current workbook.
7.在Lookup_table中选择Column之后，单击Close＆Load。 合并的数据将在当前工作簿的另一个工作表中打开。
8. The process explained here is applied for merging of tables in Power Query Editor and can be done as per the user requirement.
8.此处说明的过程适用于Power Query Editor中的表合并，并且可以根据用户要求完成。
Resolving other two limitations
解决其他两个限制
Case 2: To resolve the issue in scenario 2 in the VLookup limitation section, follow the same process in Power Query with only one change. Select Join Kind as ‘Right Outer’ which means select all values from the second table — ‘Lookup table’ in our case.
情况2：若要解决方案2在VLookup限制部分中的问题，请执行Power Query中的相同过程，而只需进行一次更改。 选择Join Kind作为“ Right Outer”，这意味着从第二个表中选择所有值，在本例中为“ Lookup table”。
Case 3: To resolve the issue in scenario 3 in the VLookup limitation section, follow the same process by selecting the columns — First Name and Last name in both the tables. The result would be generated by comparing the combination of values in First name and Last name column.
情况3：若要解决方案3中“ VLookup限制”部分中的问题，请选择两个表中的“名字”和“姓氏”列，以执行相同的过程。 通过比较“姓氏”和“姓氏”列中值的组合来生成结果。
Conclusion:
结论：
Power Query is a powerful and amazing tool that can automate the common data sourcing and transformation tasks in excel with increased efficiency and singificantly reduce the time spent on data preparation. It is also important to mention here, that all applied sourcing and transformation steps are recorded in Power Query and can be refreshed for new data in the tables. It is my recommendation to all excel users to learn and use Power Query as it is a powerful tool and significantly easy to learn.
Power Query是一个功能强大且令人赞叹的工具，可以提高效率自动执行excel中的常见数据源和转换任务，并显着减少花费在数据准备上的时间。 在此还必须提及的是，所有应用的采购和转换步骤都记录在Power Query中，并且可以刷新以获取表中的新数据。 我建议所有excel用户学习和使用Power Query，因为它是一个功能强大的工具，非常容易学习。
Therefore, continue on your learning path and realise the true power of Power Query!!
因此，继续您的学习之路，实现Power Query的真正力量！！
excel中vlookup
7572
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
