☰

复杂的日期查找

tag-icon • tag-icon microsoft-excel date vlookup
复杂的日期查找

我发现这，这非常接近，但我不知道 VB，所以我尝试使用香草配方来做到这一点。

第一张工作表包含一系列失业率表格：每个美国州 1 个。每个表格都以年份作为行标签、月份作为列标签，每个单元格中显示失业率数据。这是 BLS 网站的标准格式。我为每个表格添加了一列，与州缩写相对应。

第二张表有大量事件，每起事件占一行。列是有关事件的各种数据，包括州缩写和日期。我想制作一个 vlookup（或任何更合适/更有效的程序），从第一张工作表中提取每起事件（总共 150k+）相应日期的相应州失业率。

我猜我需要重新组织第一个工作表中的表格，以便每个月/年组合都是一行，或者使用“匹配”和“如果”等的组合。有点懒，但我已经好几年没怎么用过 Excel 了，所以我想我应该从这里开始。我非常感谢大家的意见。

编辑：

因此，我尝试嵌套多个查找，但这不是实现此目的的智能方法（公式在第二个基于事件的工作表中）。

最内层/第一次查找：使用事件工作表中的状态：在第一个工作表中找到仅适用于该状态的所有适用年份（按行搜索后，我返回一系列行？？状态是一列，年份也是。月份是一行（第一列标题）。

中间/第二次查找：使用事件工作表中的年份：在上面返回的年份范围内（行的子集）查找包含月份一月至十二月的精确行（请记住月份是列，即列标签）以匹配适当的州-年份对。

最外层查找：使用事件工作表中的月份：在第二次查找返回的月份范围内查找，精确月份（此时，理想情况下这将是精确的失业率）。

编辑于：只是想尽可能清楚地说明：

工作表 1）失业率：

State  Year   Jan   Feb  Mar  Apr  etc
AK     1991    3.5  4.3    5  6.1   x       
AK     1992    3.1  4.1    x    x   x
TX     1991      x    x    x    x   x
TX     1992      x    x    x    x   x
VA     1993      x    x    x    x   x 
VA     1994      x    x    x    x   x

工作表 2）事件：

Exact Calendar Date  Year  State    xxx   xxx     etc  (unemp rate)
xx/xx/xxxx           xxxx     xx      x     x     x      ?????
xx/xx/xxxx           xxxx     xx      x     x     x      ?????

=hlookup(month(b2),vlookup(c2,(vlookup(av2,[in first worksheet with unemp. rates, all states, in alphabetical order and first column]A2:a1174,[years in worksheet 1 that apply to this state, range is all years, but nested lookup means it will only look at the ones with the correct state abbreviation, right?]b2:b1174),[in the unemp rate worksheet, for the 1 row year/state combination that is returned, search among all months]$c$1:$n$1),*****)

尝试在此提供注释以使上述内容更加清晰：

b2 = month in incident worksheet (#2 in my original post)
c2 = year in incident worksheet 
av2=state abbreviation in incident worksheet

所以这只是众多问题中的一个。从概念上讲，此时我想让最后一个 hlookup 函数在适当的年份/州行中按月搜索，并返回我想要的确切单元格。但是，它变得如此复杂，我不知道如何引用最后一个返回的值。

感谢您的帮助！如果我可以提供更多说明，请告诉我。

答案1

由于所有费率都是数字，因此您可以使用SUMIFS来获取具有多个条件的查询。剩下唯一要做的就是获取正确的月份，您可以使用INDEX和来获取MATCH：

=SUMIFS(INDEX('Unemp. Rates'!C:N, 0, MATCH(TEXT(B2,"mmm"),'Unemp. Rates'!$C$1:$N$1,0)), 'Unemp. Rates'!A:A, AV2, 'Unemp. Rates'!B:B, B2)

从最内层到最外层进行评估：

  • TEXT(B2,"mmm")：以文本形式给出 B2 中日期的月份。假设月份是 1 月。Jan这里的结果为。

  • MATCH("Jan",'Unemp. Rates'!$C$1:$N$1,0)：给出月份所在的数字"Jan"。如果是Jan，则您会得到，1因为它是第一个单元格。

  • INDEX('Unemp. Rates'!C:N, 0, 1)：返回从 开始的第 1 列，C:N即C:C。0表示所有行，并且1是之前获得的值。如果是Feb，则会有2，并且列会是D:D。

  • SUMIFS(C:C, 'Unemp. Rates'!A:A, AV2, 'Unemp. Rates'!B:B, B2)：返回 C 列值的总和，前提是 A 列中的值与 AV2 值（州）匹配，B 列中的值与 B2 值（年份）匹配。

我不希望你有多个处于同一状态的行和同一年，因此实际上没有发生“SUM”。

相关内容