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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подстановка данных из одной таблицы по условию др таблицы (Формулы/Formulas)
Подстановка данных из одной таблицы по условию др таблицы
VadimVV Дата: Пятница, 24.06.2016, 11:30 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Добрый день.

Существуют различные наименования товаров, реализуемые в разных городах. В зависимости от суммы реализованного товара выплачивается комиссия. Например, если Товар 1 реализован на сумму до 100 руб. включительно, то выплачивается комиссия 50 руб., если от 101 до 200 руб., то комиссия 100 руб., если от 201 руб. до 300 руб., то комиссия 150 руб., если выше 301 руб, то комиссия всё равно 150 руб.

Необходимо рассчитать размер комиссии в зависимости от наименования товара и совокупной итоговой суммы проданного товара.

Товар реализуется в разных городах, но наименования товара всегда идентичное.

Пример прилагаю.
К сообщению приложен файл: 4149620.xlsx (9.1 Kb)
 
Ответить
СообщениеДобрый день.

Существуют различные наименования товаров, реализуемые в разных городах. В зависимости от суммы реализованного товара выплачивается комиссия. Например, если Товар 1 реализован на сумму до 100 руб. включительно, то выплачивается комиссия 50 руб., если от 101 до 200 руб., то комиссия 100 руб., если от 201 руб. до 300 руб., то комиссия 150 руб., если выше 301 руб, то комиссия всё равно 150 руб.

Необходимо рассчитать размер комиссии в зависимости от наименования товара и совокупной итоговой суммы проданного товара.

Товар реализуется в разных городах, но наименования товара всегда идентичное.

Пример прилагаю.
Автор - VadimVV
Дата добавления - 24.06.2016 в 11:30
китин Дата: Пятница, 24.06.2016, 11:51 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
так надо?
Код
=ПРОСМОТР(B2;Лист2!$C$1:$E$1;ИНДЕКС(Лист2!$C$2:$E$7;ПОИСКПОЗ(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2));Лист2!$A$2:$A$7;0);))

а если вы разделите город и наименование товра по разным ячейкам на Лист1 то проще будет
Код
=ПРОСМОТР(B2;Лист2!$C$1:$E$1;ИНДЕКС(Лист2!$C$2:$E$7;ПОИСКПОЗ(A2;Лист2!$A$2:$A$7;0);))
К сообщению приложен файл: 0608781.xlsx (10.2 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Пятница, 24.06.2016, 12:03
 
Ответить
Сообщениетак надо?
Код
=ПРОСМОТР(B2;Лист2!$C$1:$E$1;ИНДЕКС(Лист2!$C$2:$E$7;ПОИСКПОЗ(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2));Лист2!$A$2:$A$7;0);))

а если вы разделите город и наименование товра по разным ячейкам на Лист1 то проще будет
Код
=ПРОСМОТР(B2;Лист2!$C$1:$E$1;ИНДЕКС(Лист2!$C$2:$E$7;ПОИСКПОЗ(A2;Лист2!$A$2:$A$7;0);))
Автор - китин
Дата добавления - 24.06.2016 в 11:51
VadimVV Дата: Пятница, 24.06.2016, 12:13 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Да, только не совсем понятно, каким образом сравнивается итоговая сумма с пороговым значением.
И если итоговая сумма ниже порогового значения, формула выдает ошибку Н/Д. Т.е., если итоговая сумма, например, 50, а пороговое значение минимальное 100, то комиссия получается Н/Д.
 
Ответить
СообщениеДа, только не совсем понятно, каким образом сравнивается итоговая сумма с пороговым значением.
И если итоговая сумма ниже порогового значения, формула выдает ошибку Н/Д. Т.е., если итоговая сумма, например, 50, а пороговое значение минимальное 100, то комиссия получается Н/Д.
Автор - VadimVV
Дата добавления - 24.06.2016 в 12:13
китин Дата: Пятница, 24.06.2016, 12:17 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Мдя обшибочка вышла. ну пока вот такие костыли, а там подумаем :D
Код
=ПРОСМОТР(B2;{0;100;200;300};ИНДЕКС(Лист2!$B$2:$E$7;ПОИСКПОЗ(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2));Лист2!$A$2:$A$7;0);))

можно вот такой вариант попробовать
Код
=ЕСЛИОШИБКА(СУММПРОИЗВ(ИНДЕКС(Лист2!$C$2:$E$7;;ПОИСКПОЗ($B2;Лист2!$C$1:$E$1;1))*(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2))=Лист2!$A$2:$A$7));0)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Пятница, 24.06.2016, 12:27
 
Ответить
СообщениеМдя обшибочка вышла. ну пока вот такие костыли, а там подумаем :D
Код
=ПРОСМОТР(B2;{0;100;200;300};ИНДЕКС(Лист2!$B$2:$E$7;ПОИСКПОЗ(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2));Лист2!$A$2:$A$7;0);))

можно вот такой вариант попробовать
Код
=ЕСЛИОШИБКА(СУММПРОИЗВ(ИНДЕКС(Лист2!$C$2:$E$7;;ПОИСКПОЗ($B2;Лист2!$C$1:$E$1;1))*(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2))=Лист2!$A$2:$A$7));0)
Автор - китин
Дата добавления - 24.06.2016 в 12:17
VadimVV Дата: Пятница, 24.06.2016, 12:40 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Вариация с
Код
=ПРОСМОТР(B2;{0;100;200;300};ИНДЕКС(Лист2!$B$2:$E$7;ПОИСКПОЗ(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2));Лист2!$A$2:$A$7;0);))
интересней, т.к. в списке проданных товаров могут быть те, по которым комиссия не выплачивается и, соответственно, результат формулы напротив такого товара Н/Д. Применяя ЕСЛИОШИБКА, я заменяю Н/Д для таких товаров на "Товара нет в списке", а формула
Код
=ЕСЛИОШИБКА(СУММПРОИЗВ(ИНДЕКС(Лист2!$C$2:$E$7;;ПОИСКПОЗ($B2;Лист2!$C$1:$E$1;1))*(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2))=Лист2!$A$2:$A$7));0)
напротив таких товаров ставит 0, что не совсем верно.
 
Ответить
СообщениеВариация с
Код
=ПРОСМОТР(B2;{0;100;200;300};ИНДЕКС(Лист2!$B$2:$E$7;ПОИСКПОЗ(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2));Лист2!$A$2:$A$7;0);))
интересней, т.к. в списке проданных товаров могут быть те, по которым комиссия не выплачивается и, соответственно, результат формулы напротив такого товара Н/Д. Применяя ЕСЛИОШИБКА, я заменяю Н/Д для таких товаров на "Товара нет в списке", а формула
Код
=ЕСЛИОШИБКА(СУММПРОИЗВ(ИНДЕКС(Лист2!$C$2:$E$7;;ПОИСКПОЗ($B2;Лист2!$C$1:$E$1;1))*(ПСТР(Лист1!$A2;ПОИСК("-";Лист1!$A2)+2;ДЛСТР(Лист1!$A2))=Лист2!$A$2:$A$7));0)
напротив таких товаров ставит 0, что не совсем верно.
Автор - VadimVV
Дата добавления - 24.06.2016 в 12:40
китин Дата: Пятница, 24.06.2016, 12:55 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
а что мешает во втором варианте поставить
Код
ЕСЛИОШИБКА(СУММПРОИЗВ(.......); "Товара нет в списке")
???

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеа что мешает во втором варианте поставить
Код
ЕСЛИОШИБКА(СУММПРОИЗВ(.......); "Товара нет в списке")
???
Автор - китин
Дата добавления - 24.06.2016 в 12:55
VadimVV Дата: Пятница, 24.06.2016, 13:06 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Цитата
а что мешает во втором варианте поставить
тем, что в случае, если Товар есть в списке, но итоговая сумма, например 50, что соответствует пороговому значению 100, а комиссии 0, результат комиссии будет "Товара нет в списке", а не 0.
 
Ответить
Сообщение
Цитата
а что мешает во втором варианте поставить
тем, что в случае, если Товар есть в списке, но итоговая сумма, например 50, что соответствует пороговому значению 100, а комиссии 0, результат комиссии будет "Товара нет в списке", а не 0.
Автор - VadimVV
Дата добавления - 24.06.2016 в 13:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подстановка данных из одной таблицы по условию др таблицы (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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