课程: Excel: Power Query?(Get & Transform)
今天就学习课程吧!
今天就开通帐号,24,700 门业界名师课程任您挑!
Approximate match equivalent of VLOOKUP: Conditional column - Microsoft Excel教程
课程: Excel: Power Query?(Get & Transform)
Approximate match equivalent of VLOOKUP: Conditional column
The outer joins in Power Query can sometimes look like what we use VLOOKUP or XLOOKUP for. However, VLOOKUP and XLOOKUP give us components for when we don't want to do an exact match but we want to do approximate matches or put our data into categories or tiers. In this example, we have tests and then we have contaminant counts. We want to get these categorized. So the 15 for Test 1, that falls between 10 and 25 so that should get a satisfactory next to it. The 71 in Test 2, that should get a pass because it's between 25 and 75. Let's do this in Power Query by using conditional columns. Here we go. Cursor in the data set. Let's go to data and get data from Table/Range. I want to add a column, conditional column. Call this result. If the contaminant count is less than 10, then excellent. Now, add a clause. We're going to add a number of clauses. All right. So contaminant count is less than 25, then satisfactory. If the contaminant count is less than 75, then pass. If the contaminant…
随堂练习,边学边练
下载课堂讲义。学练结合,紧跟进度,轻松巩固知识。
内容
-
-
-
-
-
-
-
-
-
-
-
(已锁定)
Overview of joins in Power Query6 分钟 18 秒
-
(已锁定)
Walk through all six joins13 分钟 29 秒
-
(已锁定)
Joins: Left or right2 分钟 31 秒
-
(已锁定)
Outer join versus XLOOKUP2 分钟 41 秒
-
(已锁定)
Merge with multiple fields3 分钟 24 秒
-
(已锁定)
Approximate match equivalent of VLOOKUP: Binning5 分钟 47 秒
-
(已锁定)
Approximate match equivalent of VLOOKUP: Conditional column3 分钟 4 秒
-
(已锁定)
Cross Join3 分钟 17 秒
-
(已锁定)
-
-
-
-