☰

更新

tag-icon • tag-icon microsoft-excel worksheet-function
更新

基本上，我的公式完全按照我现在需要的方式工作。但是，仍然需要手动输入，这比我想要的要多。虽然手动输入很少，但如果我能完全消除手动输入，那就更好了。

我目前的公式是=IFERROR(INDEX(RawData[value], MATCH(1, (RawData[plot_id]=$A2)*(RawData[class]=1)*(RawData[metric]=LEFT($B1,FIND("(",$B1)-1)), 0)),0)。这可以在测试组织选项卡如下图所示。

当我将每个公式移动到新列时，我仍然必须手动更改其类别编号（每列搜索的类别编号是该列标题中的括号编号：（1）、（2）、（5））。我尝试用 替换RawData[class]=1，RawData[class]=MID(B1, SEARCH("(", B1)+1, SEARCH(")", B1) - SEARCH("(", B1) -1)但它会破坏公式，只会给我错误结果 0。

=MID(B1, SEARCH("(", B1)+1, SEARCH(")", B1) - SEARCH("(", B1) -1)但是，如果我在单元格中单独使用相同的 MID ( ) 函数，我就会得到我想要的结果：列标题单元格中括号之间的数字（可以在前面提到的屏幕截图的单元格 B4 中看到）。

我很茫然，因为它允许我在搜索 RawData[metric] 条件时成功使用 LEFT 函数。

知道这里发生了什么事或我怎样才能让它工作吗？

原始数据表（通用数据）

度量 班级 绘图ID 价值
area_mn 1 1 0.720177968
area_mn 1 2 0.005311753
area_mn 2 1 0.011488774
area_mn 2 2 0.435016987
编辑 5 1 0.1856209

截图以供参考

原始数据图像

组织数据表（通用数据，显示所需结果）

绘图ID 面积（1） 面积（2） 编辑(5)
1 0.720177968 0.01148877 0.720177968
2 0.005311753 0.435016987 0

截图以供参考 测试组织标签图像2

答案1

这似乎是部分数据类型不兼容的问题RawData[class]=1。

假设class是RawData一个数字（无法知道，因为您没有包含任何数据）。当您1自己编写时，Excel 会理解它是一个数字并使用它进行比较。当您使用公式MID()从字符串 中提取该数字时area_mn(1)，您会得到一个字符串1。该数字1不等于字符串1，因此比较失败。

如果将该MID()函数包装在INT()公式中，则字符串将转换为整数（数字），并且比较可以再次进行。

以下是一个例子：

数据表

ID 面积（1） 面积（2） 面积（5） 编辑(1) 编辑(2) 编辑(5)
1
2
3

查找表

ID 价值
1 100
2 200
3 300
4 400
5 500

如果您在数据表中使用以下公式，您将得到#N/A：

=INDEX(lookup[#Data], MATCH(MID(B$1,SEARCH("(",B$1)+1,SEARCH(")",B$1)-SEARCH("(",B$1)-1),lookup[[id]:[id]], 0), 2)

但是如果你先将查找到的值转换为整数，它就可以工作：

=INDEX(lookup[#Data], MATCH(INT(MID(B$1,SEARCH("(",B$1)+1,SEARCH(")",B$1)-SEARCH("(",B$1)-1)),lookup[[id]:[id]], 0), 2)

答案2

您的表头似乎是 RawData 表中的 [metric] 和 [class] 值的组合。您可以使用单个分隔符（例如“.”或“，”或“；”）来简化操作，而不是同时使用左括号和右括号，尤其是当您的 [class] 值只有一位数时。这样，您只需使用=VALUE(RIGHT(B$1,1))或=INT(RIGHT(B$1,1))或即可=--RIGHT(B$1,1)提取数字。例如：

绘图ID 区域_mn.1 区域_mn.2 区域_mn.5
1
2
3

使用这种方式格式化的标题，以下公式将在单元格 B2 中起作用：

=IFERROR(INDEX(RawData[value], MATCH(1, (RawData[plot_id]=$A2)*(RawData[class]=VALUE(RIGHT(B$1,1)))*(RawData[metric]=LEFT(B$1,FIND(".",B$1)-1)), 0)), 0)

请注意上面使用的绝对单元格引用 $A2（绝对列）和 B$1（绝对行）。在您最初的尝试中，您错误地在标题行中使用了 $B1（绝对列），这就是为什么它无法正确向下和横向复制的原因。

答案3

更新

今天早上摆弄了一会儿之后，我无法使用我得到的确切答案使公式正确工作——当我在表格的其余部分填写单元格时，我仍然不断得到错误的数据，但你们所有人都非常有帮助，我感谢你们的时间和帮助。

对于最终的公式，我想出了=IFERROR(INDEX(RawData[[value]:[value]], MATCH(1, (RawData[[plot_id]:[plot_id]]=$A2)*(RawData[[class]:[class]]=-MID(B$1, SEARCH("(", B$1), 3))*(RawData[[metric]:[metric]]=LEFT(B$1,FIND("(",B$1)-1)), 0)),0)。

@MGonet 感谢您的评论，这有助于减少公式中拉取类号的部分。这个东西已经够长了。哈哈。

补充说明：当我自动填充表格中的数据时，我发现表格列发生了变化。我没有意识到他们会这样做，我以为指定表格列始终是绝对引用 — — 今天我才知道并非如此。所以我更正了这些引用，使它们成为绝对引用。

之后，我基本上从这里采纳了所有建议和意见（例如，纠正我输入的绝对单元格引用错误的地方、使用更简洁的函数替代方案等），再加上我自己的额外研究和反复试验，最终得到了一个完全符合我需要的公式。

真的，非常感谢您抽出时间！

相关内容