Триптих "Ценник" Часть 2

Сделай сам: Продвинутый ценник (Часть 2). Проектирование и формулирование

Первая часть примера: Сделай сам: Продвинутый ценник (Часть 1). Подготовка прайс-листа

Сходили на склад? Разобрались с дублями?

Ну что ж, можно продолжать.

В этой части мы создадим ценник и свяжем его с прайс-листом. Поехали.

Для начала надо взять файл, на котором мы остановились в прошлый раз. Если Вы его потеряли, то качайте мой.

Бильярд2

В нем надо «Лист1» переименовать в «Прайс», а «Лист2» назвать «Ценники». Справились?

Рисуем

Теперь немного «порисуем» на листе «Ценники». Никак не могу считать себя дизайнером, поэтому набросаю несколько простых действий по оформлению внешнего вида ценника. Если Вы проявите фантазию, то я с удовольствием поделюсь Вашими примерами для всех остальных. Присылайте свои варианты ценников.

А пока делаем так:

В ячейке A1 пишем «Артикул:». Красим ячейку B1 в какой-нибудь неяркий цвет.

Затем в ячейке A3 — «Наименование:». Выделяем диапазон ячеек A4:D4 и:

  • Объединяем ячейки,

  • Красим их, применяя какой-нибудь нетемный оттенок.

В ячейке A6 пишем «Цена:», затем выделяем диапазон B6:D10 и:

  • Объединяем ячейки,

  • Красим их, применяя какой-нибудь нетемный оттенок,

  • Включаем размер текста 36 пт.

И, наконец, в ячейке C12 пишем «Минимум:», а ячейкуD12 красим.

Вот что получилось у меня:

Ценник 2-01.png

Теперь сходим лист «Прайс» и возьмем оттуда какой-нибудь артикул. Я скопировал самый первый «10.901.01.1» для товара «Bear древко "с черным кольцом"» с ценой «105,06». Вставьте этот артикул на листе «Ценники» в ячейку B1.

Ценник 2-02.pngЧтобы зеленый цвет не ушел, надо вставить только значения. Для этого при вставке используйте либо команду «Специальная вставка», либо кнопочку с цифрами «123».

ВэПээРим

Теперь нас ждет «Всемогущий» ВПР — удивительная по эффективности функция, которая экономит уйму времени и без знания которой невозможно представить себе любого пользователя Excel.

Ценник 2-03.png

Полностью согласен с этим высказыванием. ВПР настолько полезна, что ее знание можно считать критерием при приеме на работу. Что написано в 99,5% резюме? «Я владею Excel». Причем это может означать совершенно разное — от факта запуска программы и минутной прогулки по клеточкам до реального многолетнего опыта манипуляции гигабайтами информации.

Как мгновенно определить уровень владения программой? Мне это необходимо в первой беседе с клиентом. Очень часто диалог развивается так:

— Слышал, что Вы хорошо учите работе в Excel. Вот не знаю, стоит ли мне учиться, ведь я Excel, вроде, знаю.

Я в этот момент задаю расслабляющий вопрос:

— Столбики вставляем?

Клиент, как правило, слегка обижается, как же, конечно, что ж его за неграмотного держат (хотя бывают и такие, кто столбики не вставлял ни разу, а считает, что «Экселем владеет» J). Тут надо без промедления задавать второй вопрос:

— Вэпээрите?

Если человек теряет ориентацию и просит повторить неясный термин, то его уровень становится сразу ясен. Если пользователь «столбики вставляет», но не использует ВПР, то его уровень владения программой никак нельзя назвать удовлетворительным. Не может опытный пользователь пройти мимо такой замечательной функции. Но тогда он никакой не «опытный», а совсем наоборот, совсем даже «неопытный».

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

Встаем в клетку A4 и пишем (без кавычек): «=ВПР(». Затем тыкаем мышкой по клетке B1 (это уникальный код, по которому мы определим нужный товар для отображения в ценнике). Ставим «;». Итого у нас получается «=ВПР(B1;».

Теперь идем в лист «Прайс» и выделяем там столбики «A» и «B». Вот так:

Ценник 2-04.png

Так надо выделять, потому что по столбику «Артикул» компьютер определит нужную строку и именно поэтому он должен быть всегда первым, а из столбика «B» компьютер заберет наименование товара. В том, сколько столбиков вправо надо выделять, особой строгости нет. Если Вы выделите больше, чем нужно, это не будет считаться ошибкой. Главное, чтобы в выделенном фрагменте был тот столбец, из которого забираются значения. Ставим «;».

Теперь надо поставить номер столбца, из которого забираем значения. Номер этот отсчитывается, начиная со столбца, по которому определяется нужная строка — «Артикул». У «Наименование» номер 2. Значит, ставим «2». Снова «;».

Осталось только указать «секретный параметр», который я называю «признаком интервального просмотра». Ну никак не могу рассказать, что это такое в этой статье. Ждите объяснения в других. Пока ставим «0» и закрываем скобку.

Что ж, переходим в клетку B6. И снова пишем «=ВПР(», затем надо сослаться на B1, поставить «;», выбрать на листе «Прайс» диапазон A:E, снова поставить «;», поставить «5», снова «;», поставить «секретный параметр «0» и закрыть скобку.

Справитесь с клеткой D12? Я в Вас верю. Это выборка из столбца «Минимальная партия».

Вот готовый ценник:

Ценник 2-05.png

Теперь стоит сходить в «Прайс», скопировать оттуда другой артикул и подставить его в ячейку B1. Данные в «голубых» ячейках мгновенно поменяются. Такая подстановка в 4 раза быстрее ручной и абсолютно не может содержать ошибок, когда берут один артикул, другой наименование, неправильную цену и прочие параметры. Человек — существо ошибающееся. В этом наша человеческая слабость и большая человеческая сила. Ошибка — источник открытий. Но только не в этом примере.

Размножаем

Если Вы подумали, что это все, то глубоко ошибаетесь. Первый сюрприз ждет Вас после распечатки. Из принтера выползет одинокий ценник, и скорее всего захочется «разложить» его по листу, повторив несколько раз.

Казалось бы, что тут такого сложного? Просто выделяем диапазон ячеек A1:D12 и копируем его «Ctrl+C» (или правой кнопкой мыши — копировать). Затем встаем в ячейку F1 и вставляем. Но… что-то пошло не так. У Вас получилось что-то совсем не то.

Ценник 2-06.png

Что же пошло не так? Давайте погружаться в формулы. Встаньте в ячейку F4 и почитайте формулу. Здесь Вы заставляете компьютер искать значение ячейки G1 в справочнике… «Прайс!F:G». Ой, что-то странное. Встанем-ка на ячейку B4. Там Вы ищете значение ячейки B1 в справочнике «Прайс!A:B». Если со сменой искомого значения я соглашусь, то со сменой справочника — никак не могу. Получается, что при копировании Excel смещает все диапазоны в формулах ровно на такое количество столбцов и строк, на которое происходит копирование. Это неправильно! Некоторые параметры не должны смещаться!

Да, такое бывает и для «замораживания» некоторых параметров придуман следующий приём.

Поскольку Excel рожден в США, то именно этот знак — знак «$» был выбран американскими программистами в качестве символа «неизменности». Это моя версия, но она имеет право на существование J.

Мы пометим в первой формуле нужный параметр при помощи этого знака, и он не изменится при копировании.

Для начала отменим наше неудачное копирование или просто удалим информацию со столбцов F:I.

Теперь встаём в клетку A4. Ставим текстовый курсор в строке формул так, чтобы он коснулся адреса диапазона справочника «A:B». Если Вы не знаете, куда тыкнуть мышкой, тыкайте между буквами A и B. Вот так: «A|:B». Теперь ищите на клавиатуре функциональную клавишу F4 и нажимайте её один раз. Должно получиться вместо «A:B» — «$A:$B». Если Вы не поняли фишки с нажатием F4, то просто поставьте в строке формул знак доллара перед буквой A и перед буквой B.

Проделайте также со второй и третьей формулой. Для второй формулы результат должен стать таким. Старый адрес справочника был «Прайс!A:E». А должно стать «Прайс!$A:$E».

Надеюсь, что с «минимальной партией» Вы справитесь сами.

После такого преобразования можно спокойно копировать ценник по листу. Я расположил на листе 8 ценников.

Ценник 2-07.png

Теперь пора и отдохнуть. В следующей статье я покажу, как улучшить выбор артикулов, чтобы не пришлось каждый раз ходить за новым артикулом в «Прайс». Также мы защитим ценник, да и весь файл от неосторожных или вредительских действий оператора.

Пока!!! Побалуйтесь с файлом, но только не испортите его. Если же все-таки что-то случится, то вот Вам мой файлик для образца.

Ценники

Третья часть примера: Сделай сам: Продвинутый ценник (Часть 3). Оптимизация и защита

Опубликовано 20 июня 2018 | Обновлено 22 июня 2018
Excel, Реальная задача, Сделай сам

Возврат к списку