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

Вход

Регистрация

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

 

= Мир MS Excel/Выбор прайса с ценами по условию - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выбор прайса с ценами по условию (Макросы/Sub)
Выбор прайса с ценами по условию
antal10 Дата: Среда, 04.03.2015, 16:56 | Сообщение № 1
Группа: Проверенные
Ранг: Участник
Сообщений: 58
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013
Добрый день уважаемые форумчане!
Который день пытаюсь осилить задачу сам, но видимо не судьба. :'(
Попробую описать суть проблемы, т.е. попытаюсь правильно сформулировать, если получиться. Имеется лист "Форма" с накладной, в ячейках H10:H40 формулой

Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;Прайс!$A$2:$H$36;ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))

выдергивается цена на товар, как видно из формулы, цена берется из листа "Прайс". Но есть еще один лист с прайсом: "Опт". И цены должны браться и из него тоже, но в зависимости от клиента, который внесен в поле Получено на листе "Форма". Т.е. цены в ячейках H10:H40 должны формироваться либо с листа "Прайс" либо с листа "Опт" в зависимости от клиента в ячейке С4. Помогите переписать формулу или как-то по-другому решить задачу, у самого мозгов наверное не хватает. Критерием для выбора прайса, я так думаю может быть колонка "Примечание". Как реализовать это, ума не приложу, но файл по правилам форума прикладую.
К сообщению приложен файл: 7815551.xls (92.5 Kb)


Сообщение отредактировал antal10 - Среда, 04.03.2015, 16:56
 
Ответить
СообщениеДобрый день уважаемые форумчане!
Который день пытаюсь осилить задачу сам, но видимо не судьба. :'(
Попробую описать суть проблемы, т.е. попытаюсь правильно сформулировать, если получиться. Имеется лист "Форма" с накладной, в ячейках H10:H40 формулой

Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;Прайс!$A$2:$H$36;ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))

выдергивается цена на товар, как видно из формулы, цена берется из листа "Прайс". Но есть еще один лист с прайсом: "Опт". И цены должны браться и из него тоже, но в зависимости от клиента, который внесен в поле Получено на листе "Форма". Т.е. цены в ячейках H10:H40 должны формироваться либо с листа "Прайс" либо с листа "Опт" в зависимости от клиента в ячейке С4. Помогите переписать формулу или как-то по-другому решить задачу, у самого мозгов наверное не хватает. Критерием для выбора прайса, я так думаю может быть колонка "Примечание". Как реализовать это, ума не приложу, но файл по правилам форума прикладую.
Автор - antal10
Дата добавления - 04.03.2015 в 16:56
slAvIk159 Дата: Среда, 04.03.2015, 17:18 | Сообщение № 2
Группа: Проверенные
Ранг: Участник
Сообщений: 83
Репутация: 13 ±
Замечаний: 0% ±

Excel 2013
Здраствуйте!
Вот-так?
К сообщению приложен файл: antal10.xls (93.5 Kb)
 
Ответить
СообщениеЗдраствуйте!
Вот-так?
Автор - slAvIk159
Дата добавления - 04.03.2015 в 17:18
_Boroda_ Дата: Среда, 04.03.2015, 17:25 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант
Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="Опт";Опт!$A$2:$H$36;Прайс!$A$2:$H$36);ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))
К сообщению приложен файл: 7815551_1.xls (97.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант
Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="Опт";Опт!$A$2:$H$36;Прайс!$A$2:$H$36);ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))
Автор - _Boroda_
Дата добавления - 04.03.2015 в 17:25
antal10 Дата: Среда, 04.03.2015, 18:27 | Сообщение № 4
Группа: Проверенные
Ранг: Участник
Сообщений: 58
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013
Пытаюсь вникнуть в формулы slAvIk159, _Boroda_, пока темный лес.
Точнее я понимаю смысл каждой функции, и что она в этой формуле выполняет, но до кучи я бы такие формулы точно не собрал.
Думал вообще, что формулами не получиться, вот и выложил вопрос в раздел VBA. Спасибо большое slAvIk159 и _Boroda_ очень помогли. Есть только несколько незначительных вопросов, а именно:

1. Какую из формул лучше выбрать, ведь они обе работают? У _Boroda_ немного покороче, мне она чуть более понятна. Хотя главное чтоб работала.

2. Как поведут себя формулы если на листе "Форма" в ячейке С4, где выбирается клиент, выпадающий список будет формироваться из динамического диапазона и клиентов будет больше ста, а то и двухста. Как тогда будут срабатывать формулы?

3. Если добавиться 3 лист с прайсом, например под названием "VIP", формулу придется переписать? Правильно же я понимаю? Если да, то вижу, что формулу, которую любезно предоставил _Boroda_, проще будет доделать или я ошибаюсь?


Сообщение отредактировал antal10 - Среда, 04.03.2015, 18:28
 
Ответить
СообщениеПытаюсь вникнуть в формулы slAvIk159, _Boroda_, пока темный лес.
Точнее я понимаю смысл каждой функции, и что она в этой формуле выполняет, но до кучи я бы такие формулы точно не собрал.
Думал вообще, что формулами не получиться, вот и выложил вопрос в раздел VBA. Спасибо большое slAvIk159 и _Boroda_ очень помогли. Есть только несколько незначительных вопросов, а именно:

1. Какую из формул лучше выбрать, ведь они обе работают? У _Boroda_ немного покороче, мне она чуть более понятна. Хотя главное чтоб работала.

2. Как поведут себя формулы если на листе "Форма" в ячейке С4, где выбирается клиент, выпадающий список будет формироваться из динамического диапазона и клиентов будет больше ста, а то и двухста. Как тогда будут срабатывать формулы?

3. Если добавиться 3 лист с прайсом, например под названием "VIP", формулу придется переписать? Правильно же я понимаю? Если да, то вижу, что формулу, которую любезно предоставил _Boroda_, проще будет доделать или я ошибаюсь?
Автор - antal10
Дата добавления - 04.03.2015 в 18:27
_Boroda_ Дата: Среда, 04.03.2015, 18:36 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
3. Если добавится ВИП, то вот сюда
Код
ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="Опт";Опт!$A$2:$H$36;Прайс!$A$2:$H$36)
нужно добавить еще ЕСЛИ
Или переписать этот кусок через ВЫБОР, например.
2. Особо на производительность не повлияет
1. У меня простота формулы строится на том, что листы Опт, Прайс ( и какие там еще нужно будет добавить) абсолютно одинаковы за исключением чисел в В4:Н36. Как у slAvIk159 не смотрел.

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение3. Если добавится ВИП, то вот сюда
Код
ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="Опт";Опт!$A$2:$H$36;Прайс!$A$2:$H$36)
нужно добавить еще ЕСЛИ
Или переписать этот кусок через ВЫБОР, например.
2. Особо на производительность не повлияет
1. У меня простота формулы строится на том, что листы Опт, Прайс ( и какие там еще нужно будет добавить) абсолютно одинаковы за исключением чисел в В4:Н36. Как у slAvIk159 не смотрел.
Автор - _Boroda_
Дата добавления - 04.03.2015 в 18:36
antal10 Дата: Среда, 04.03.2015, 18:48 | Сообщение № 6
Группа: Проверенные
Ранг: Участник
Сообщений: 58
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013
Спасибо _Boroda_ за разъяснения, но некоторые ответы не совсем понял. А именно:

Цитата
2. Особо на производительность не повлияет

Меня здесь больше интересовала не производительность, а то, попадут ли в вашу формулу вновь занесенные клиенты, то есть будет ли работать формула, если на листе "Клиенты" появиться много новых клиентов, вплоть до 200 человек. Будут ли браться в расчет клиенты ниже 100-й строки? и

Цитата
абсолютно одинаковы за исключением чисел в В4:Н36

здесь не очень понял. Если вы на счет прайса, то цены будут везде разные, я их еще поменять не успел.

А насчет дописать в формулу еще и VIP, я про это и написал, что в вашей формуле это проще вроде бы сделать, по-крайней мере для меня. А вот на счет функции ВЫБОР, то это для меня – высокая материя. Я с этой функцией еще не знаком. Хотел бы конечно посмотреть вариант, как бы с ней выглядело.

P.S. Теперь понял, за ячейки В4:Н36 этот вопрос отпадает. Не разобрался, что вы имели ввиду абсолютную одинаковость прайсов, за исключением цен.


Сообщение отредактировал antal10 - Среда, 04.03.2015, 18:58
 
Ответить
СообщениеСпасибо _Boroda_ за разъяснения, но некоторые ответы не совсем понял. А именно:

Цитата
2. Особо на производительность не повлияет

Меня здесь больше интересовала не производительность, а то, попадут ли в вашу формулу вновь занесенные клиенты, то есть будет ли работать формула, если на листе "Клиенты" появиться много новых клиентов, вплоть до 200 человек. Будут ли браться в расчет клиенты ниже 100-й строки? и

Цитата
абсолютно одинаковы за исключением чисел в В4:Н36

здесь не очень понял. Если вы на счет прайса, то цены будут везде разные, я их еще поменять не успел.

А насчет дописать в формулу еще и VIP, я про это и написал, что в вашей формуле это проще вроде бы сделать, по-крайней мере для меня. А вот на счет функции ВЫБОР, то это для меня – высокая материя. Я с этой функцией еще не знаком. Хотел бы конечно посмотреть вариант, как бы с ней выглядело.

P.S. Теперь понял, за ячейки В4:Н36 этот вопрос отпадает. Не разобрался, что вы имели ввиду абсолютную одинаковость прайсов, за исключением цен.
Автор - antal10
Дата добавления - 04.03.2015 в 18:48
slAvIk159 Дата: Среда, 04.03.2015, 19:07 | Сообщение № 7
Группа: Проверенные
Ранг: Участник
Сообщений: 83
Репутация: 13 ±
Замечаний: 0% ±

Excel 2013
antal10, моя формула это ваша формула, просто добавил ветвление на два листа

советую использовать формулу которую написал _Boroda_, она и изисканей, и добавлять новые листи легче будет
 
Ответить
Сообщениеantal10, моя формула это ваша формула, просто добавил ветвление на два листа

советую использовать формулу которую написал _Boroda_, она и изисканей, и добавлять новые листи легче будет
Автор - slAvIk159
Дата добавления - 04.03.2015 в 19:07
_Boroda_ Дата: Среда, 04.03.2015, 21:04 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата antal10, 04.03.2015 в 18:48, в сообщении № 6
Будут ли браться в расчет клиенты ниже 100-й строки?

Ну если кусок формулы
Код
ВПР(C$4;Клиенты!A$2:G$99;7;)
до 99-й строки, то как Вы сами думаете, будет учитываться клиенты с 100-й и ниже? Конечно нет. Нужно увеличить диапазон.
Цитата antal10, 04.03.2015 в 18:48, в сообщении № 6
дописать в формулу еще и VIP, ... в вашей формуле это проще вроде бы сделать
Да, просто добавить еще ЕСЛИ
Код
ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="Опт";Опт!$A$2:$H$36;ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="VIP";VIP!$A$2:$H$36;Прайс!$A$2:$H$36))

Цитата antal10, 04.03.2015 в 18:48, в сообщении № 6
Хотел бы конечно посмотреть вариант, как бы с ней выглядело.

Как-то так примерно:
Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;ВЫБОР(ПОИСКПОЗ(ВПР(C$4;Клиенты!A$2:G$99;7;);{"Опт":"Прайс":"VIP"};);Опт!$A$2:$H$36;Прайс!$A$2:$H$36;VIP!$A$2:$H$36);ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))

Можно еще через ДВССЫЛ, но она летучая
Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;ДВССЫЛ(ВПР(C$4;Клиенты!A$2:G$99;7;)&"!$A$2:$H$36");ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата antal10, 04.03.2015 в 18:48, в сообщении № 6
Будут ли браться в расчет клиенты ниже 100-й строки?

Ну если кусок формулы
Код
ВПР(C$4;Клиенты!A$2:G$99;7;)
до 99-й строки, то как Вы сами думаете, будет учитываться клиенты с 100-й и ниже? Конечно нет. Нужно увеличить диапазон.
Цитата antal10, 04.03.2015 в 18:48, в сообщении № 6
дописать в формулу еще и VIP, ... в вашей формуле это проще вроде бы сделать
Да, просто добавить еще ЕСЛИ
Код
ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="Опт";Опт!$A$2:$H$36;ЕСЛИ(ВПР(C$4;Клиенты!A$2:G$99;7;)="VIP";VIP!$A$2:$H$36;Прайс!$A$2:$H$36))

Цитата antal10, 04.03.2015 в 18:48, в сообщении № 6
Хотел бы конечно посмотреть вариант, как бы с ней выглядело.

Как-то так примерно:
Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;ВЫБОР(ПОИСКПОЗ(ВПР(C$4;Клиенты!A$2:G$99;7;);{"Опт":"Прайс":"VIP"};);Опт!$A$2:$H$36;Прайс!$A$2:$H$36;VIP!$A$2:$H$36);ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))

Можно еще через ДВССЫЛ, но она летучая
Код
=ЕСЛИ(И(B10="Тара";F10<>"";G10="–");"–";ЕСЛИ(ИЛИ(B10="";G10="";F10="");"";ГПР(G10;ДВССЫЛ(ВПР(C$4;Клиенты!A$2:G$99;7;)&"!$A$2:$H$36");ПОИСКПОЗ(B10;Прайс!$A$2:$A$36;))))
Автор - _Boroda_
Дата добавления - 04.03.2015 в 21:04
antal10 Дата: Среда, 04.03.2015, 22:15 | Сообщение № 9
Группа: Проверенные
Ранг: Участник
Сообщений: 58
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013
Спасибо _Boroda_ очень интересная последняя формула, хоть и будет пересчитываться при каждом изменении, все же у нее есть плюсы, это можно добавлять листы с ценами и под разными названиями не меняя самой формулы. Попробовал работает при любом добавлении прайса, с любым названием. Что касается формулы с функцией ВЫБОР, то буду разбираться, пока она при добавлении прайса и примечания VIP не срабатывает. Пишет Н\Д. Буду разбираться. Спасибо еще раз всем.
 
Ответить
СообщениеСпасибо _Boroda_ очень интересная последняя формула, хоть и будет пересчитываться при каждом изменении, все же у нее есть плюсы, это можно добавлять листы с ценами и под разными названиями не меняя самой формулы. Попробовал работает при любом добавлении прайса, с любым названием. Что касается формулы с функцией ВЫБОР, то буду разбираться, пока она при добавлении прайса и примечания VIP не срабатывает. Пишет Н\Д. Буду разбираться. Спасибо еще раз всем.
Автор - antal10
Дата добавления - 04.03.2015 в 22:15
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выбор прайса с ценами по условию (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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