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

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

Ну что ж , последний рывок. Судя по произошедшему с нашим файлом в прошлых статьях, теперь придется жениться на нём нам надо его оптимизировать и защитить.

На случай, если Вы потеряли свой результат прошлых манипуляций, я предлагаю свой вариант. Скачивайте и присоединяйтесь.

Ценники

Что не так в этом файле? Мне не нравится, что приходится каждый раз, когда хочется поменять артикул в каком-нибудь ценнике, идти на лист «Прайс», выбирать там какой-нибудь артикул, копировать его, возвращаться на лист «Ценники» и вставлять в зелёную ячейку. Много суеты. Надо проще. Сейчас бы организуем

Выбор из списка

Для начала идём в лист «Прайс» и выделяем столбец артикулов. Можно, конечно, тыкнуть по заголовку столбца «A», но мы поступим тоньше. Выделите ячейку A2. Затем придавите клавишу CTRL, затем SHIFT, и нажмите один раз клавишу со стрелкой вниз. Excel Вам выделит ровно все заполненные ячейки столбца «A» (Артикул).

Теперь идём во вкладку Формулы и тыкаем по кнопке «Присвоить имя». Затем пишем в строке «Имя:» название выделенного диапазона. Пусть будет «Артикулы». ОК.

Мы только что обозначили диапазон всех артикулов одним словом. Скоро это нам пригодится.

Возвращаемся на лист «Ценники». Встаём на первую зелёную ячейку. Выполняем:

Во вкладке «Данные» ищем кнопку «Проверка данных». Давим. Появляется окошко, в котором надо обратить внимание на вкладку «Параметры» и список «Тип данных». Разворачиваем список и выбираем «Список».

Затем встаём в графу «Источник» и нажимаем функциональную клавишу F3. Появится список именованных диапазонов. У нас он всего один  «Артикулы». Выбираем и подтверждаем ОК.

Если увидели окошко таким как на иллюстрации — давите ОК. Наслаждаемся 20 секунд. Теперь не надо будет бегать на лист «Прайс» за очередным артикулом. Он будет подтягиваться в этот список автоматически. Стоит лишь развернуть список при помощи стрелочки, что стоит чуть правее зелёной клеточки.

Всё ближе к идеалу. Но надо сделать еще несколько шагов. Неплохо бы размножить этот эффект, чтобы не повторять его много раз. Самый простой способ — скопировать эту зелёную клетку и вставить её несколько раз на другие злёные клетки нашего листа. Вместе со всей информацией — цифрами, цветом и пр. — перейдет и свойство разворачиваться и выбирать артикулы из списка.

Вроде бы всё закончено. Пора отдавать в работу. Ан нет. Рановато. Предсказываю: через 15 минут после работы (а, может, и через 5) файл вернется к Вам с испорченными формулами.

Как бы так сделать, чтобы формулы, которые мы прописывали и копировали в прошлом сеансе работы, не могли испортиться? Но чтобы в зелёных клеточках мы могли выбирать артикулы. Для этого

Защищаем лист

Включать защиту еще рано. Если мы это сделаем сейчас, то запретим вообще все действия во всех клетках листа. Предварительно надо бы показать компьютеру, в каких клетках можно работать (в зелёных!). Для этого правой кнопкой мыши тыкаем по первой зелёной клеточке и выбираем команду «Формат ячеек». Там идём во вкладку «Защита» и убираем в ней галку около слов «Защищаемая ячейка». ОК.

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

Теперь можно защищать. Идём во вкладку «Рецензирование». Ищем кнопку «Защитить лист». Давим. На галочки можно не обращать внимания. Пароль желательно задать. (Однажды я пренебрег этим и пришлось ехать через весь город, чтобы исправить испорченную формулу потому что «мы тыкнули, оно написало, куда пойти, чтобы снять защиту, мы пошли и сняли, а потом оно само испортилось»). Пароль лучше не забывать. 🙂 В своём файле я использовал пароль «1».

Ура. Теперь, если попытаться что-либо сделать в незелёной клетке, то Excel наругается непонятными словами и не даст ничего испортить. А в зелёной клетке позволит лишь выбирать значение из списка, поскольку всё остальное будет тоже запрещено предыдущей командой.

Но можно сделать ещё круче (куда ещё?). Снимаем защиту: вкладка «Рецензирование» и давим кнопку «Снять защиту листа». Снова перед нами окошечко защиты. Убираем первую галочку со словами «выделение заблокированных ячеек». Давим ОК. Теперь можно «путешествовать» только по зелёным ячейкам и менять в них значениях. В другие ячейки даже встать не получится. 🙂

Осталась косметика. Но она тоже важна. Правда, дамы? 🙂

На время снимаем защиту (не забудьте потом поставить снова). Выделяем первый пустой столбик правее нашего материала. У меня это столбец J. Затем давим CTRL+SHIFT+стрелка вправо. Выделяются все столбцы до самого последнего. Кстати, он помечен буковками «XFD». Правой кнопкой мыши тыкаем по выделенному диапазону и скрываем эти столбцы. Аналогично выделяем все лишние строки внизу ( CTRL+SHIFT+стрелка вниз). Выясняется, что последняя строка Excel — 1048576-я. Скрываем эти строки. Теперь наш проект ограничен только ячейками, в которых есть текст.

И ещё заходим во вкладку «Вид» и убираем галочку около слова «Сетка». Нам уже не нужны границы ячеек и их можно убрать, чтобы не отвлекать внимание нашего пользователя.

Ну вроде совсем хорошо стало. Но лучше не торопиться с передачей файла пользователю. Он же может… удалить лист «Прайс». Или, что еще хуже, залезть в него и подправить цены в свою пользу. Не допустим!

Запрещаем действия с листами

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

Чтобы не дать ему испортить нашу работу, скрываем лист «Прайс», а затем во вкладке «Рецензирование» жмём кнопку «Защитить книгу». Галочки не трогаем. Пароль обязательно задаём. У меня по традиции «1».

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

Всё. Поздравляю. Мы закончили свой триптих. Если что-то пошло не так, то скачивайте мой файлик и используйте его как образец.

Ценник (образец)

Пишите в комментариях о своих успехах, задавайте вопросы, надеюсь, я был Вам полезен.

Ваш, Олег Видякин.

Рекомендации по обучению: Модуль E2