Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Извлечение данных из двух таблиц по условию - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение данных из двух таблиц по условию (Формулы/Formulas)
Извлечение данных из двух таблиц по условию
CrazyNice Дата: Четверг, 20.07.2017, 14:04 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Доброго дня!

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

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

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

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

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

P.S.: офис 2016 стоит
К сообщению приложен файл: ___.xlsx(16Kb)
 
Ответить
СообщениеДоброго дня!

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

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

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

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

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

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

Автор - CrazyNice
Дата добавления - 20.07.2017 в 14:04
_Boroda_ Дата: Четверг, 20.07.2017, 14:08 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11547
Репутация: 4754 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Таблицы 1 и 2 в ширину увеличиваются?

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


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

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

Автор - _Boroda_
Дата добавления - 20.07.2017 в 14:08
CrazyNice Дата: Четверг, 20.07.2017, 14:13 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Нет, таблица 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1015
Репутация: 255 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Вот этого не понял
мероприятие 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(17Kb)
 
Ответить
СообщениеДобрый день.
Вот этого не понял
мероприятие 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))

Автор - sboy
Дата добавления - 20.07.2017 в 14:53
_Boroda_ Дата: Четверг, 20.07.2017, 15:14 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11547
Репутация: 4754 ±
Замечаний: 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(17Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДля удобства сделал все на одном листе
И таблицу соответствия мероприятий обязательно
Тогда
Код
=ИНДЕКС(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);)))

Автор - _Boroda_
Дата добавления - 20.07.2017 в 15:14
CrazyNice Дата: Четверг, 20.07.2017, 16:33 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - CrazyNice
Дата добавления - 20.07.2017 в 16:33
CrazyNice Дата: Четверг, 20.07.2017, 16:56 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - CrazyNice
Дата добавления - 20.07.2017 в 16:56
_Boroda_ Дата: Четверг, 20.07.2017, 17:21 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11547
Репутация: 4754 ±
Замечаний: 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))
К сообщению приложен файл: -14-2.xlsx(17Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПо идее так и должно быть. Теоретически. Но на практике ...
Вот такой вариант тогда
Код
=ИНДЕКС(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))

Автор - _Boroda_
Дата добавления - 20.07.2017 в 17:21
CrazyNice Дата: Четверг, 20.07.2017, 17:30 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 0 ±
Замечаний: 0% ±

Да, считает. Спасибо большое еще раз!
 
Ответить
СообщениеДа, считает. Спасибо большое еще раз!

Автор - CrazyNice
Дата добавления - 20.07.2017 в 17:30
_Boroda_ Дата: Четверг, 20.07.2017, 17:34 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11547
Репутация: 4754 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Можно еще так - выделите всю первую таблицу, нежмите Контрл h, в Найти - знак процента, в Заменить на - знак процента, жмете Заменить всё


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМожно еще так - выделите всю первую таблицу, нежмите Контрл h, в Найти - знак процента, в Заменить на - знак процента, жмете Заменить всё

Автор - _Boroda_
Дата добавления - 20.07.2017 в 17:34
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение данных из двух таблиц по условию (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!