Данные:
Я мог бы использовать некоторую помощь, придумав формулу соответствия индекса, основанную на 2 условных элементах. Для прикрепленного набора данных мне нужно настроить формулу (и сделать ее повторяемой для других магазинов и будущих дат), которая получает номер данных на основе конкретной даты (в нашем случае 11/9/15), и для Магазин № 414. В этом примере я хочу, чтобы ответом был "132". Надеюсь, что это имеет смысл.
Любая помощь будет оценена!
Вот два решения, соответствующие моим предложениям в комментариях к вопросу. Для обоих из них я предполагаю, что A1:C30 содержит данные из вопроса.
Использование функции базы данных
Первые решения используют функции базы данных Excel. Все функции базы данных обрабатывают диапазон ячеек как базу данных, где каждая строка является записью, а каждый столбец - полем. Первая строка содержит имена столбцов. Функции базы данных также принимают другой диапазон ячеек в качестве критериев поиска, где первая строка - это имена столбцов, а вторая строка - фактические критерии. Учитывая это, в E1:F2 (или где угодно, но это то, куда я положил это для этих примеров), поместите:
E F
1 Store # Date
2 414 11/9/15
Это критерии. Затем в E4 (или где-либо еще) положить =DGET(A1:C30,"Data",E1:F2) . При этом используется функция базы данных DGET для поиска значения столбца с учетом базы данных (A1:C30), имени столбца ("Data") и критериев (E1:F2). В этом случае это приведет к 132 . Изменение содержимого F2 на 11/2/15 приведет к изменению значения DGET на 55 и т.д.
Это, пожалуй, самый чистый способ, потому что его легко расширить, если у вас есть дополнительные столбцы в данных и критерии, которые вы хотите использовать для соответствия этим столбцам. Вы также можете повторно использовать части критериев для выполнения других задач. Например, =DSUM(A1:C30,"Data",E1:E2) суммирует все значения данных для хранилища 414, =DSUM(A1:C30,"Data",F1:F2) суммирует все значения данных для 11/9/15 и т.д. Он также не подразумевает порядка сортировки столбцов. Недостатком является то, что если вы не часто используете функции базы данных (как я :-)), вам придется перечитывать справку о них каждый раз, когда вы их используете (как я сделал для этого :-)) так что, возможно, это не так легко обслуживать.
Использование формул индексации и поиска
Второй способ объединяет некоторые формулы поиска в Excel.
Настройте рабочий лист так же, как и выше, данные из вопроса в A1:C30 и критерии в E1:F2. Обратите внимание, что в этом случае мы будем использовать только E2 и F2, но вы можете оставить E1 и F1 в качестве меток относительно того, что находится в E2:F2.
Затем в E4 положите:
=VLOOKUP(F2,INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1),2)
Разбивая это, изнутри:
MATCH(E2,A:A,0)
Это находит первое совпадение (0) в первом столбце (A:A), которое соответствует # магазина в E2 . Он возвращает относительную позицию, и в этом случае, поскольку весь первый столбец является поисковым массивом, позиция будет номером строки первого вхождения store # в E2. С данными примера, если вы поместите это в ячейку отдельно, она будет иметь значение 7 .
INDEX(B:B,MATCH(E2,A:A,0),1)
Это создает ссылку на ячейку во втором столбце (B:B) в строке, определенной с помощью MATCH и столбца 1 . Если вы поместите это в отдельную ячейку, значением будет значение указанной ячейки, которое в данном примере будет значением из B7 или 11/10/14 .
MATCH(E2,A:A,1)
Это находит последнее совпадение (1) в первом столбце, которое соответствует store #. Если поместить это в ячейку само по себе, значение будет 25 .
INDEX(C:C,MATCH(E2,A:A,1),1)
Это создает ссылку на ячейку в третьем столбце (C:C) в строке, определенной с помощью MATCH и столбца 1 . Если вы поместите это в отдельную ячейку, значением будет значение указанной ячейки, которое в данном примере будет значением из C25 или 132 .
INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1)
Это объединяет две формулы INDEX для создания ссылки для поиска по дате. С данными примера это будет B7:C25 (если вы поместите его в ячейку отдельно, вы получите # #VALUE! потому что это приводит к более чем одному значению. Если вы поместите его в ячейку как формулу массива, вы получите значение в верхней левой ячейке (11/10/14).
=VLOOKUP(F2,INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1),2)
Это объединяет все это. Он использует VLOOKUP для поиска даты из F2 в диапазоне ячеек, созданных двумя формулами INDEX (B7:C25), и для возврата второго столбца (последние 2) в соответствующей строке.
Вуаля!