使用 Vlookup 或 IndexMatch 中的增量列从大型 excel 数据表中提取数据而没有零 [英] Pulling data from big excel datatable with incremental column in Vlookup or IndexMatch without zeros

问题描述

我尝试为上次导入日期和上一个导入日期提取大约 160 个指标的数据，以便我可以自动执行一些操作.

I am trying for a while to pull data for around 160 metrics for the last imported date and previous one so I can automate some stuff.

这是我的数据表的样子:

This is how my datatable looks like:

我使用 MAX 函数从表格中获取最新日期，而前一个日期则是从最后一个日期开始计算 7 天.

I get the latest date from the table with a MAX function and the previous one with substantiating 7 days from the last one.

我想在不显示任何零的情况下一直获取这两个日期的指标，直到数据表的末尾(在条目为空的地方留空)

I would like to get the metrics for these two dates all the way to the end of the datatable without showing any zeroes (just leave blank where the entry is blank)

问题是: 1-数据被格式化为数据表，因为很多图表是自动从中馈送的.由于它是一个数据表，它不会从 vlookup 的正常单元格引用中提取正确的值.2-因为它是一个数据表，所以很难在 vlookup 的列中强制执行 +1 增量，这样我就不必手动输入 165 个公式.3-我真的很想要一个公式，当条目为空白时不给出零但也返回空白.

Problems are: 1-the data is formatted as datatable because a lot of graphs are feeding from it automatically. Since its a datatable it doesnt pull the right value from normal cell references of vlookup. 2-since its a datatable its hard to enforce +1 increment in columns for vlookup so that I dont have to manually type in 165 formulas. 3-I would really like a formula not to give zero when the entry is blank but also return blank.

我试过了:

    =VLOOKUP($C$3,$C$13:$FH$200,D:D, FALSE)
    =VLOOKUP($C$3,Table1[#All],2, FALSE)
    =INDEX(Table1[Date],MATCH($C$2,Table1[BOADPMOUS106]))

以及它们的许多变体.它们都不能很好地工作或能够被拉到桌子的尽头.当然，它们返回 0 并带有空白条目.

and a lot of variations of those. None of them works well or was able to be pulled to the end of the table. And of course they return 0 with blank entries.

有没有人有解决这个问题的想法?

Is anyone having an idea to solve this?

推荐答案

如果您要返回基于文本的结果，您可以将零长度字符串附加到 VLOOKUP 功能.

If you are returning a text based result, you can append a zero-length string to the VLOOKUP function.

=VLOOKUP($C$3,Table1[#All],2, FALSE)&""

这不会改变返回的字符串，但当返回值为空时不会显示零.

This will not alter the string returned but will not show a zero when the return value would be blank.

如果您要返回数字或日期，则必须检查返回值是否为空.

If you are returning numbers or dates then you have to check if the return value is blank.

=IF(LEN(VLOOKUP($C$3,Table1[#All],2, FALSE)), VLOOKUP($C$3,Table1[#All],2, FALSE), "")

不幸的是，这是一个双重查找，就像我们在 IFERROR 函数 出现了，但到目前为止，还没有本机工作表 IFBLANK 函数.

Unfortunately, this is a double lookup much as we had to do to check for errors before the IFERROR function came along but so far, there is no native worksheet IFBLANK function.

可以轻松编写 UDF IFBLANK.

A UDF IFBLANK could be easily written.

这篇关于使用 Vlookup 或 IndexMatch 中的增量列从大型 excel 数据表中提取数据而没有零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！