© Сорокин Б.Б. 2016 г.

Формирование особых цен для особых клиентов

Прошу помогите - задача стоит такая.

Цена на товар1 - 45 руб. для клиента A и B, а 56 руб. для всех остальных клиентов ("Лист1"). Для вывода цены товара1 в накладной заказа для клиента-А при написании накладной нужно, чтобы дополнительно выводились цены на остальные товары (лист "База"), определённые для всех клиентов фирмы.

Какую формулу можно использовать, имея несколько критериев, и чтобы формула возвращала значение ячейки 56 руб. для остальных клиентов.

Файл с примером и вопросом можно скачать здесь.

Итак, смотрим файл примера 13.xls и на «Лист1» видим два листа накладных с одним товаром для двух разных клиентов. При этом цена на товар для клиентов разная. Говоря нормальным языком, клиент А имеет скидку на товар 1.

Особые цены для особого клиента А

Клиент-ООО "ОРЁЛ" такой скидки не имеет. Почему? – это дело фирмы.

Цены для клиента ОРЕЛ

Так же, из вопроса понятно что, для обоих клиентов другие товары могут быть без скидки или со скидкой. На листе «База» видим табличку с наименованием товаров и клиентов.

База клиентов, цен и товаров

Полагаем, что те позиции товара и соответствующие им цены, напротив которых не стоит наименование клиентов, являются общими для всех. Соответственно, где проставлен клиент – индивидуальные цены.

Чтобы выбирать эти позиции в накладную нам надо сформировать уникальный признак каждой позиции. Вариантов формирования много, мы сделаем это на основании существующей таблички см.файл v13_1.xls примера.

Для этого добавим справа еще один столбик (на языке баз данных – «поле»), назовем его «Отбор» и, при помощи функции СЦЕПИТЬ(D3;B3) сформируем уникальный признак.

Уникальный признак клиента и товара

Теперь, что бы список клиентов выглядел нормально без повторов и пробелов его надо сгруппировать при помощи сводной таблицы (лист СТ) и задать область данных «КЛИЕНТЫ» с достаточным запасом т.к. клиенты будут добавляться.

Область данных клиентов

Аналогично создадим область данных «ТОВАРЫ».

Область данных товаров

Теперь мы готовы работать с самой накладной на листе «Накладная».

Для ячейки C2 зададим список значений «КЛИЕНТЫ».

Список значений клиентов

Для ячеек C7 – C30 зададим список значений «ТОВАРЫ».

Список значений товаров

Как это сделать и для чего, описано на этом сайте http://www.myxcel.ru/sp/sp0.htm.

Теперь осталось придумать формулу, которая на основании введенных данных о клиенте и товаре правильно выберет из базы данных цену товара и проставит ее в ячейках E7 – E30.

Формула строится на базе функции ВПР() с использованием логических функций ЕСЛИ(), ЕПУСТО и ЕНД и выглядит так:

=ЕСЛИ(ЕПУСТО(C7);"-"; ЕСЛИ(ЕНД(ВПР(СЦЕПИТЬ($C$2;C7); База_данных;3;ЛОЖЬ)); ВПР(C7; База_данных;3;ЛОЖЬ); ВПР(СЦЕПИТЬ($C$2;C7); База_данных;3;ЛОЖЬ)))

Применение функции ВПР описано здесь http://www.myxcel.ru/vpr.htm.

Логика формулы следующая.

1. ЕСЛИ(ЕПУСТО(C7);"-";

Проверяет наличие товара в ячейке C7, если его нет, в ячейке цены проставляется "-". Если товар есть проверяется следующее условие.

2. ЕСЛИ(ЕНД(ВПР(СЦЕПИТЬ($C$2;C7); База_данных;3;ЛОЖЬ)); ВПР(C7; База_данных;3;ЛОЖЬ); ВПР(СЦЕПИТЬ($C$2;C7); База_данных;3;ЛОЖЬ))

Проверяется наличие в базе данных набранного и сформированного по данным накладной уникального признака клиента и товара и если такой признак есть то функция

ВПР(СЦЕПИТЬ($C$2;C7);База_данных;3;ЛОЖЬ)

возвращает цену для данного признака.

Если указанного признака нет то функция

ВПР(C7; База_данных;3;ЛОЖЬ)

возвращает цену товара без учета клиента, т.е. общую цену для всех клиентов.

Думаю, что возможны и другие варианты, но я придумал так.