Извлечение данных из двух таблиц по условию

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение данных из двух таблиц по условию (Формулы/Formulas)

CrazyNice

Дата: Четверг, 20.07.2017, 14:04 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 45

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016 RUS

Доброго дня!

Помогите, пожалуйста с формулой.
Есть 2 таблицы:
1 таблица: бренды и %
2 таблица: мероприятия и %

% в одной таблице соотносятся с % другой таблице.

Нужно притянуть % из табл2. Мероприятие задано в строках, а вот номер столбца нужно найти по условию из табл1.

Прикладываю файл с описанием.

Буду очень-очень благодарна за оперативный ответ.

P.S.: офис 2016 стоит

К сообщению приложен файл: ___.xlsx (16.0 Kb)

Ответить

_Boroda_

Дата: Четверг, 20.07.2017, 14:08 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Таблицы 1 и 2 в ширину увеличиваются?

И еще раз как-то иначе попробуйте объсянить, что-то не очень понятно, если честно

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

CrazyNice

Дата: Четверг, 20.07.2017, 14:13 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 45

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016 RUS

Нет, таблица 1 и таблица 2 не меняются и находятся на листе строго одна под другой.

У меня есть бренд и скидка. Я сравниваю значение скидки по бренду с максимальным (таблица 1 колонка AD), если значение скидки больше максимума, то беру максимум, если меньше, то оставляю значение скидки.
В примере для бренда 2 скидка 25% > максимального (23,8%), значит я беру 23,8%. В примере для бренда 5 скидка 10% < максимальной, значит оставляю 10%. делаю всё это простым впр.

А дальше мне нужно найти значение из таблицы 2 на пересечении мероприятия (по строке) и значения %, которое я нашла по табл1. Точнее у меня либо максимальное значение из последнего столбца, либо ближайшее значение к скидке.
В примере: для бренда 2 значение максимальное, значит из второй таблицы нужно значение из последнего столбца по строке мероприятия (мероприятие А = мероприятию 1).
Во втором случае значение осталось 10% и номер столбца будет: нужно в таблице 1 в строке для бренда 5 найти ближайшее (в идеале в меньшую сторону) значение к 10%, т.е. должно найтись 9,8% (колонка Q, ячейка Q7). Номер столбца = соответственно номеру столбца Q.
И далее из второй таблице найти значение на пересечение мероприятия Б (допустим мероприятие Б=мероприятию 1 тоже) в столбце Q.

А мероприятия в свою очередь:
мероприятие 1= мероприятие А= мероприятие Б,
мероприятие 2 = мероприятие С= мероприятие D,
и еще таких 2 пары
+ 8 мероприятий, которые попадают в графу "остальные"

Раньше таблица была меньше, и формула прописывалась через кучу если, бренды писались вручную 9формула на пол листа А4 была), теперь брендов стало много, и прописать вручную почти невозможно(

Сообщение отредактировал CrazyNice - Четверг, 20.07.2017, 14:29

Ответить

СообщениеНет, таблица 1 и таблица 2 не меняются и находятся на листе строго одна под другой.

У меня есть бренд и скидка. Я сравниваю значение скидки по бренду с максимальным (таблица 1 колонка AD), если значение скидки больше максимума, то беру максимум, если меньше, то оставляю значение скидки.
В примере для бренда 2 скидка 25% > максимального (23,8%), значит я беру 23,8%. В примере для бренда 5 скидка 10% < максимальной, значит оставляю 10%. делаю всё это простым впр.

А дальше мне нужно найти значение из таблицы 2 на пересечении мероприятия (по строке) и значения %, которое я нашла по табл1. Точнее у меня либо максимальное значение из последнего столбца, либо ближайшее значение к скидке.
В примере: для бренда 2 значение максимальное, значит из второй таблицы нужно значение из последнего столбца по строке мероприятия (мероприятие А = мероприятию 1).
Во втором случае значение осталось 10% и номер столбца будет: нужно в таблице 1 в строке для бренда 5 найти ближайшее (в идеале в меньшую сторону) значение к 10%, т.е. должно найтись 9,8% (колонка Q, ячейка Q7). Номер столбца = соответственно номеру столбца Q.
И далее из второй таблице найти значение на пересечение мероприятия Б (допустим мероприятие Б=мероприятию 1 тоже) в столбце Q.

А мероприятия в свою очередь:
мероприятие 1= мероприятие А= мероприятие Б,
мероприятие 2 = мероприятие С= мероприятие D,
и еще таких 2 пары
+ 8 мероприятий, которые попадают в графу "остальные"

Раньше таблица была меньше, и формула прописывалась через кучу если, бренды писались вручную 9формула на пол листа А4 была), теперь брендов стало много, и прописать вручную почти невозможно(

Автор - CrazyNice
Дата добавления - 20.07.2017 в 14:13

sboy

Дата: Четверг, 20.07.2017, 14:53 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Добрый день.
Вот этого не понял

Цитата CrazyNice, 20.07.2017 в 14:13, в сообщении № 3 ()

мероприятие 1= мероприятие А= мероприятие Б,
мероприятие 2 = мероприятие С= мероприятие D,
и еще таких 2 пары

Если мероприятия в обоих таблицах будут одинаково называться + остальные, то формула вот такая

Код

=ИНДЕКС('шкала приростов'!$B$19:$AD$24;ЕСЛИОШИБКА(ПОИСКПОЗ(A2;'шкала приростов'!$A$19:$A$23;);6);ПОИСКПОЗ(C2;ИНДЕКС('шкала приростов'!$B$3:$AD$16;ЕСЛИОШИБКА(ПОИСКПОЗ(B2;'шкала приростов'!$A$3:$A$15;);14);0);1))

К сообщению приложен файл: 6486411.xlsx (16.7 Kb)

Яндекс: 410016850021169

Ответить

_Boroda_

Дата: Четверг, 20.07.2017, 15:14 | Сообщение № 5

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Для удобства сделал все на одном листе
И таблицу соответствия мероприятий обязательно
Тогда

Код

=ИНДЕКС(B$19:AD$24;ЕСЛИОШИБКА(ПОИСКПОЗ(ВПР(A33;J$33:K$42;2;);A$19:A$23;);6);ПОИСКПОЗ(C33;ИНДЕКС(B$3:AD$16;ЕСЛИОШИБКА(ПОИСКПОЗ(B33;A$3:A$15;);14);)))

К сообщению приложен файл: -14-1.xlsx (16.8 Kb)

Ответить

CrazyNice

Дата: Четверг, 20.07.2017, 16:33 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 45

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016 RUS

Вы - волшебники!!!! спасибо огромное, всё работает! hands

Ответить

CrazyNice

Дата: Четверг, 20.07.2017, 16:56 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 45

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016 RUS

А можно еще сделать уточнение, чтобы если в первой строке находится точное совпадение, бралось точное совпадение?
В смысле, что сейчас, если брать, например, остальные бренды, остальные мероприятия и скидку 10%, то формула возвращает 34,3%, потому что из табл1 берет не 10%, а соседнее меньшее 9,6%.
Можно, чтобы если есть точное совпадение, то бралось точное значение, а если приблизительное, то ближайшее меньшее?

Ответить

_Boroda_

Дата: Четверг, 20.07.2017, 17:21 | Сообщение № 8

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

По идее так и должно быть. Теоретически. Но на практике ...
Вот такой вариант тогда

Код

=ИНДЕКС(B$19:AD$24;ЕСЛИОШИБКА(ПОИСКПОЗ(ВПР(A33;J$33:K$42;2;);A$19:A$23;);6);СЧЁТЕСЛИ(ИНДЕКС(B$3:AD$16;ЕСЛИОШИБКА(ПОИСКПОЗ(B33;A$3:A$15;);14););"<="&C33))