Я нашел это, что довольно близко, но я не знаю VB, поэтому я пытаюсь сделать это с помощью ванильной формулы.

Первая рабочая таблица содержит ряд таблиц уровня безработицы: 1 на каждый штат США. В каждой таблице представлены данные с указанием года в качестве метки строки, месяца в качестве метки столбца и уровня безработицы в каждой ячейке. Это стандартный формат с сайта BLS. Я добавил столбец для каждой таблицы, соответствующей сокращенному состоянию.

Вторая таблица имеет большое количество инцидентов, каждый инцидент является строкой. Столбцы представляют собой различные данные об инциденте, включая сокращение штата и дату. Я хочу сделать vlookup (или что-то более подходящее / эффективное), которое вытягивает соответствующую безработицу штата на соответствующую дату для каждого инцидента (всего 150 000+) с первого листа.

Я предполагаю, что мне нужно будет реорганизовать таблицы в первом листе так, чтобы каждая комбинация месяц / год была строкой, или использовать некоторую комбинацию "сопоставить" и "если" и т.п. Немного ленивый, но прошло много лет с тех пор, как я много играл с Excel, поэтому я решил начать здесь. Я действительно ценю чей-либо вклад.

Редактировать:

Поэтому я пытаюсь вложить несколько поисков, что не может быть разумным способом сделать это (формула 2, рабочая таблица на основе инцидентов).

Внутренний / первый поиск: использовать состояние в листе инцидента: найти все применимые годы в 1-м листе, которые относятся только к этому состоянию (я возвращаю диапазон строк после поиска по строке ?? состояние - это столбец, как и год. месяц - это строка (заголовок первого столбца).

Средний / второй поиск: использовать год в листе инцидента: найти в возвращаемом диапазоне лет сверху (подмножество строк) точную строку, содержащую месяц январь-декабрь (помните, что месяцы - это столбцы, то есть метки столбцов) для соответствующего State- Летняя пара.

Самый внешний поиск: использовать месяц в листе происшествий: найти в возвращенном диапазоне месяцев от второго поиска, точный месяц (на данный момент, в идеале, это будет точный уровень безработицы).

Отредактировано в: просто пытаюсь сделать это как можно яснее:

Рабочий лист 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 ответ1

5

Поскольку все цены являются числами, вы можете использовать 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. Допустим, месяц январь. 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» не происходит.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .