excel - Excel - 与复杂的索引/匹配/匹配查找作斗争
问题描述
我有一个有 9 列和大约 6000 行的表。每行都有一个价格作为最后一列。其中一些价格在它们应该是一个值时是 0.00。
在另一个工作表中,我有大约 3700 行的“原始”表。我需要的价格在这些行中。但是,原始表格在行内水平显示价格,每个行旁边都有一个具有特定 gal 范围的单元格。基本上,我拥有的表对于每个位置/gal 范围/价格组合都有唯一的行,原始表在单个位置行中依次具有所有 gal 范围/价格
例如,原始表中的一行如下所示:
... / 1-2000 / 2.8383 / 2001-4000 / 2.5382 / ...
在我的新表中,它们看起来像这样:
... / 1-2000 / 2.8383
... / 20001-4000 / 2.5382
etc
我的新表和原表中的一切都是一样的,除了那些加仑的范围和价格。
我想要做的是使用数组多个条件索引/匹配(基于我的新表和原始表中的 3 个单元格)来查找行,找到与 gals 范围匹配的值,然后取价格在那个 gal 范围单元格的右侧。
下面仔细看看我构建的公式:
INDEX(old!$A$2:$Q$3755,MATCH(1,(A29=old!$A$2:$A$3755)*(F29=old!$F$2:$F$3755)*(G29=old!$G$2:$G3755),1),MATCH(H29,old!$J$2:$Q$3755,1)+1)
第一个标准索引/匹配部分效果很好......我索引表并匹配以查找行。如果我只是为 Col 输入一个数字(例如,1、2、3),它将完美地从相应的单元格返回值。但是,我似乎无法使 Col 匹配部分工作......我不断收到 REF 和 N/A 错误。
进行双向搜索有什么技巧吗?似乎只需在行中找到该值并在 if... 之后获取下一个单元格应该是一件简单的事情?
这里的一个问题是我正在寻找的 gal 范围值不是唯一的……至少有 20 个其他参考具有相同的范围(例如,“1-2000”)。有没有办法将 col 匹配限制为我通过行匹配找到的行?
任何帮助是极大的赞赏。
谢谢,瑞克
解决方案
解决这种情况的一种方法是使用#powerquery
.
请参阅本文以了解如何在您的 Excel 版本上使用Power Query 。它在Excel 2010 Professional Plus 及更高版本中可用。我的演示是使用Excel 2016。
步骤是:
- 将旧数据加载/添加到 Power Query 编辑器。我的样本数据只有一行,但数千行都是一样的;
- 使用选项卡下的合并列功能
Transform
将前7列与分隔符合并,例如分号;
; - 为每对和使用相同的分隔符重复合并列。如果你做得正确,你应该有如下内容:
GALLONS
TOTAL PRICES
;
- 使用选项卡下的Unpivot Columns功能
Transform
将所有合并的列unpivotGALLONS;TOTAL PRICE
,然后删除该Attribute
列;
- 使用选项卡下的拆分列功能
Transform
按分隔符拆分每一列;
。如果你做得正确,你应该有如下内容:
- 制作范围列的重复列
GALLONS
(这是上面屏幕截图中的倒数第二列),然后GALLONS
用分隔符拆分原始范围列-
。那么你应该有:
- 根据需要重命名列标题;
- 关闭并将新表加载到新工作表(默认情况下),或者您可以更改默认设置并为新表创建连接并将其加载到工作簿中的所需位置。
第二个表是输出表,您可以从这个新表执行INDEX+MATCH,这应该比旧表容易得多。如果数据相同但只是结构不同,那么您可以只使用输出表,而不必担心查找缺失的价格。
我已经在我的源表中添加了一条测试行,这是通过单击按钮刷新的输出:
以下是后台电源查询M码,仅供参考。所有步骤都使用编辑器的内置功能执行,这非常简单。
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"IATA", Int64.Type}, {"ST", type text}, {"FUELER", type text}, {"UPDATED", type datetime}, {"RESTRICTIONS", type text}, {"BASEF UEL", type text}, {"NOTES", type any}, {"GALLONS1", type text}, {"TOTAL PRICES1", type text}, {"GALLONS2", type text}, {"TOTAL PRICES2", type text}, {"GALLONS3", type text}, {"TOTAL PRICES3", type text}, {"GALLONS4", type text}, {"TOTAL PRICES4", type text}, {"GALLONS5", type text}, {"TOTAL PRICES5", type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"IATA", type text}, {"UPDATED", type text}, {"NOTES", type text}}, "en-AU"),{"IATA", "ST", "FUELER", "UPDATED", "RESTRICTIONS", "BASEF UEL", "NOTES"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"GALLONS1", "TOTAL PRICES1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.1"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"GALLONS2", "TOTAL PRICES2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
#"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"GALLONS3", "TOTAL PRICES3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.3"),
#"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"GALLONS4", "TOTAL PRICES4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.4"),
#"Merged Columns5" = Table.CombineColumns(#"Merged Columns4",{"GALLONS5", "TOTAL PRICES5"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.5"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns5", {"Merged"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type datetime}, {"Merged.5", type text}, {"Merged.6", type text}, {"Merged.7", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Value.1", "Value.1 - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Value.1 - Copy", "Value.1", "Value.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Reordered Columns", "Value.1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1.1", "Value.1.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Value.1.1", Int64.Type}, {"Value.1.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged.1", "IATA"}, {"Merged.2", "ST"}, {"Merged.3", "FUELER"}, {"Merged.4", "UPDATED"}, {"Merged.5", "RESTRICTIONS"}, {"Merged.6", "BASEF UEL"}, {"Merged.7", "NOTES"}, {"Value.1 - Copy", "GALLONS"}, {"Value.1.1", "Min Fuel"}, {"Value.1.2", "Max Fuel"}, {"Value.2", "TOTAL PRICE"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns",{{"UPDATED", type date}})
in
#"Changed Type4"
推荐阅读
- mongodb - 使用 MongoDB 事务的要求
- javascript - getTime 是否返回日-月-年值?
- python - 无法在 Tensorflow 2 代码中转换 tf.contrib.layers.embed_sequence
- git - 如何使用`git`切换分支?
- jquery - Jquery-ui Datepicker 未在 Rails 视图中显示,出现错误 Uncaught TypeError: $(...).datepicker is not a function
- reactjs - 如何在不更新状态的情况下显示模态
- python - Flask:使应用程序状态持续存在于选项卡上
- r - 取消嵌套 tibble 列:使用 dplyr v1.0.0 进行“宽”数据摘要
- spring-boot - Java Spring 启动应用程序无法执行 java.lang.IllegalStateException: Annotation @EnableCircuitBreaker found
- django - Django Admin:将某些员工用户限制为他们自己组中的数据库记录