Двумерный поиск в Excel 2010

У меня есть данные, похожие на это

Таблицы содержат более одного заголовка метки строки (метка строки с накоплением):

                     |20%     |30%  |
|25/01/11   |buy     |1       |1.1  |
|           |sell    |0.8     |0.9  |
|27/01/11   |buy     |1.02    |1.03 |
|           |sell    |1.1     |1.2  |
|01/02/11   |buy     |1.05    |1.07 |

Я пытаюсь использовать формулу поиска для получения данных на основе даты и типа транзакции: скажите, каков был множитель на 30% ставки на продажу 27/01/11 Любая помощь будет мне очень признательна!

УПД

должно быть решение этой проблемы без добавления уникального столбца для поиска... В Excel 2000 был способ сделать это, используя формулы естественного языка, как вы можете видеть из этой статьи в методе 2, пример 2 - support.microsoft.com/kb/275170

УПД 2

Это вообще возможно? даты будут не только иметь пробелы, но и будут пропускать один или два дня... как в примере

УПД 3

введите здесь описание изображения

Есть проблемы с решениями, опубликованными до сих пор:

если дата поиска 26/01/2011, формула вернет N/A (в идеальном решении она должна вернуть ближайшее совпадение (округленное до предыдущей даты) - т. е. если дата поиска недоступна в столбце A, то должна быть возвращена предыдущая ближайшая дата)

заголовок строки с накоплением означает, что у меня есть строки, организованные в такие группы (дата и тип являются заголовками строк) введите описание изображения здесь


excel lookup excel-formula
person Aleksey Napolskih    schedule 08.01.2012    source источник
comment
всегда ли есть одна покупка и одна продажа для каждой даты в этом порядке?   -  person barry houdini    schedule 08.01.2012
comment
должно быть решение этой проблемы без добавления уникального столбца для поиска... В Excel 2000 был способ сделать это, используя формулы естественного языка, как вы можете видеть из этой статьи в методе 2, пример 2 - support.microsoft.com/kb/275170 ...   -  person Aleksey Napolskih    schedule 09.01.2012
comment
Опубликовано как минимум два решения, которые не требуют уникального столбца и точно работают с данными в вашем примере. Пожалуйста, примите один из этих ответов или отредактируйте свой вопрос, чтобы уточнить, почему эти решения не работают для ваших фактических данных.   -  person Rachel Hettinger    schedule 10.01.2012
comment
извините, я, должно быть, описал свою проблему нечетко. основной пост обновлен.   -  person Aleksey Napolskih    schedule 10.01.2012

Ответы (4)


arrow_upward
4
arrow_downward

Если F1 содержит требуемую дату, F2 %, F3 "купить" или "продать"

=INDEX($C:$D,MATCH(F1,$A:$A,0)+IF(F3="sell",1,0),MATCH(F2,$C$1:$D$1,0))

учитывает все три фактора

ИЗМЕНИТЬ

на основе вашего редактирования, чтобы получить то, что вы хотите, вам нужно всего лишь изменить параметр точного соответствия с 0 на 1. Из файла справки Excel

Match_Type: 1 или опущено ПОИСКПОЗ находит наибольшее значение, которое меньше или равно искомому_значению. Значения в аргументе lookup_array должны располагаться в порядке возрастания.

поэтому формула становится

=INDEX($C:$D,MATCH(F1,$A:$A,1)+IF(F3="sell",1,0),MATCH(F2,$C$1:$D$1,0))

Кроме того, если вы хотите сделать то же самое со значениями %, сделайте то же самое и с другими MATCH.

=INDEX($C:$D,MATCH(F1,$A:$A,1)+IF(F3="sell",1,0),MATCH(F2,$C$1:$D$1,1))
person chris neilsen    schedule 08.01.2012
comment
Мне это нравится.... +1, предполагает, конечно, что каждая дата будет продаваться после покупки. - person barry houdini; 08.01.2012
comment
красиво и элегантно, НО в моем случае A:A содержит пробелы, т.е. таблица содержит более одного заголовка метки строки (метка строки с накоплением), и поэтому, если я запрошу 25/01/11 как дату, она вернет N/A - person Aleksey Napolskih; 09.01.2012
comment
@chrisneilson +1; Алексей Напольских: Это решение точно работает с таблицей, как показано в вашем вопросе. Возможно, вы можете опубликовать снимок экрана с вашими данными, чтобы мы могли увидеть точную схему. - person Rachel Hettinger; 10.01.2012
comment
Я не понимаю вашей проблемы. Учитывая, что мой ПОИСКПОЗ использует точное совпадение (параметр 0), любые пробелы не будут иметь значения. Также даты в датах A:A (порядковые номера дат отформатированы как даты или строки? Какими бы они ни были, убедитесь, что значение, которое вы указали в F2, одинаково. Что вы подразумеваете под меткой строки с накоплением - person chris neilsen; 10.01.2012
comment
извините, я, должно быть, описал свою проблему нечетко. основной пост обновлен. - person Aleksey Napolskih; 10.01.2012

arrow_upward
1
arrow_downward

Предполагая, что ваши данные начинаются в столбце A, а дата поиска находится в ячейке F1, попробуйте следующее:

=INDEX(D:D, MATCH(F1,A:A,0) + 1, 1)

Это работает путем поиска строки, соответствующей дате (MATCH), смещения ее на 1, чтобы получить строку продажи, и использования функции INDEX для извлечения значения из столбца D.

person Rachel Hettinger    schedule 08.01.2012

arrow_upward
0
arrow_downward

Учтите, что у вас есть значения, начинающиеся с A1. Дата 25-01-2011 будет на A2, 27/01 будет на A4 и т. д. 20% будет на C1 и 30% будет на D1

Поместите дату поиска, которую вы хотите, в F1, ищите процент, который вы хотите, в G1 (20 или 30%), ищите тип, который вы хотите, в H1 (купить или продать)

Введите приведенную ниже формулу в I1. У вас есть результат.

=INDEX(C2:D6,IF(H1="buy",MATCH(F1,A2:A6,0),IF(H1="sell",MATCH(F1,A2:A6,0)+1)),MATCH(G1,C1:D1,0))
person Suresh    schedule 08.01.2012
comment
Спасибо, но это не работает, так как дата поиска будет иметь пробелы... т. е. запрос 25/01/11 в качестве даты вернет N/A - person Aleksey Napolskih; 09.01.2012
comment
@AlekseyNapolskih У вас есть пробелы в датах? 25.01.11, вот так, в колонке А? В этом проблема? - person Suresh; 10.01.2012
comment
извините, я, должно быть, описал свою проблему нечетко. основной пост обновлен. - person Aleksey Napolskih; 10.01.2012

arrow_upward
0
arrow_downward

В таких ситуациях я считаю полезным включить «ключевой» столбец слева от данных, что значительно упрощает формулы. Вставьте столбец слева от ваших данных и укажите комбинацию даты и покупки/продажи (A2 = B2 & "_" & C2):

                                   |20%     |30%  |
25/01/11_buy  |25/01/11   |buy     |1       |1.1  |
25/01/11_sell |           |sell    |0.8     |0.9  |
27/01/11_buy  |27/01/11   |buy     |1.02    |1.03 |
27/01/11_sell |           |sell    |1.1     |1.2  |
01/02/11_buy  |01/02/11   |buy     |1.05    |1.07 |

Затем просто выполните vlookup + match:

=VLOOKUP({needed date and type}, A1:E6, MATCH({needed %}, A1:E1,0),0)

Преимущество этого подхода в том, что он сохраняет формулы простыми и легко читаемыми.

person vasek1    schedule 09.01.2012
comment
спасибо, но это последнее средство. У меня есть огромная таблица поиска, которая будет обновляться (копировать и вставлять) пользователем, поэтому мне нужно решение, которое не потребует изменения данной структуры... - person Aleksey Napolskih; 09.01.2012