Автоматическая печать ценников при помощи MS Office 2007
Эту статью я начну с фрагмента одного из писем, поступивших на почту редакции: «Уважаемый «Б & К»! Я работаю на предприятии, у которого имеется достаточно большая сеть для розничной торговли. Цены на товары периодически изменяются. И каждый раз после такого изменения мы корректируем прайс-лист, затем печатаем новые ценники, чтобы передать их на все торговые точки. Подскажите, как можно автоматизировать этот процесс? В нашей бухгалтерии мы используем программы MS Office версии 2007…» Думаю понятно, что речь в нашей статье пойдет о формировании серийных документов. Только на это раз мы поговорим не о поздравительной рассылке, а о практической бухгалтерской задаче.
Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
На страницах нашего издания мы уже поднимали тему работы с серийными документами (или так называемыми рассылками). Например, подробную статью о работе с рассылками в MS Office 2007 можно найти в «Б & К», 2011, № 4. Напомню, что тогда речь шла об организации поздравительной рассылки. Когда есть текст поздравления в формате программы MS Word, адресная книга в Outlook или MS Excel. А наша задача — сформировать итоговый документ, в котором будут собраны персональные поздравления для каждого получателя из адресной книги. Работа с ценниками во многом похожа на эту задачу. Но в ней есть своя специфика. В чем она состоит, мы сейчас и разберемся.
Исходные данные
Прежде чем приступить к решению задачи, нужно понять, чем мы располагаем. Итак, по порядку. У нас есть прайс-лист, подготовленный в программе Excel. В качестве примера я использовал документ, фрагмент которого показан на рис. 1. Разумеется, в вашем случае форма прайс-листа может отличаться, но принципы подготовки данных и организация рассылки останутся неизменными. И я уверен, что применить их в каждом конкретном случае не составит труда.
Итак, в прайс-листе на рис. 1 есть заголовок (шапка с общими сведениями об организации) и табличная часть. Розничные цены в табличной части (колонка «
Розн.») введены вручную. Мелкооптовая, оптовая и специальная цены рассчитаны по формулам как розничная цена минус соответствующая скидка. Размер скидок указан в ячейках «D9:D11». Формулы для определения мелкооптовой, оптовой и специальной цен в ячейках «D14», «E14», «F14» выглядят так: «=C14*(1-$D$9)», «=C14*(1-$D$10)», «=C14*(1-$D$11)». Эти формулы скопированы на всю высоту табличной части документа.Таким образом, вся нужная нам информация (наименования товаров и цены) в документе есть. Но к сожалению, в таком виде наш прайс-лист нельзя использовать для организации рассылки — мешает шапка таблицы. Нам нужен не просто документ, а база данных, где собрана информация о ценах и товарах. То есть прежде чем работать с рассылками, мы должны на отдельном листе сформировать такую базу и затем воспользоваться ею. Сразу отмечу, что никакой проблемы или неудобства здесь нет. Такая работа должна быть проделана всего один раз. А затем вы просто пополняете базу и пользуетесь ей по мере необходимости.
Относительно документа на рис. 1 я предлагаю поступить так. Выделить табличную часть документа и перенести ее на отдельный лист. В самом документе «Прайс-лист» проставить ссылки на ячейки в базе данных. С этого момента все корректировки цен мы будем делать на листе с базой данных. Кстати, это еще удобнее, чем оперировать документом, где есть громоздкая шапка… Благодаря ссылкам все данные об изменении цен из базы будут автоматически попадать в основной документ. Его мы будем использовать для распечатки прайс-листов. А для печати ценников мы сможем задействовать базу данных. Делаем так:
1) открываем документ «
Прайс», изображенный на рис. 1;2) нажимаем «
Shift+F11» (добавляем новый рабочий лист). Можно воспользоваться специальной кнопкой «Вставить лист» в области ярлычков MS Excel 2007. Или щелкнуть правой кнопкой на любом ярлычке и выбрать из контекстного меню «Вставить…», а затем «Лист»;3) щелкаем правой кнопкой мыши на ярлычке нового листа, из контекстного меню выбираем «
Переименовать». Станет доступным для редактирования название листа;4) вводим новое имя, например «
БазаЦен», и нажимаем «Enter»;5) переходим на лист «
Прайс» (рис. 1);6) выделяем блок с табличной частью. В документе на рис. 1 это блок «
A14:F25»;7) нажимаем «
Ctrl+X» (вырезаем табличную часть в буфер обмена);8) переходим на лист «
Прайс», становимся на ячейку «A1»;9) нажимаем «
Ctrl+V» (вставить из буфера). Результат нашей работы (лист «БазаЦен») показан на рис. 2. 
Важно! В данном случае копирование ячеек на лист «БазаЦен» недопустимо — это приведет к ошибке. Здесь ситуация такова. Данные на лист «БазаЦен» мы вставляем с первой строки (ячейка «A1»). На листе «Прайс» эти формулы расположены начиная с четырнадцатой строки (рис. 1). Причем они ссылаются на значения скидок, которые расположены выше (ячейки «D9:D11» листа «Прайс»). При копировании формул на лист «БазаЦен» Excel откорректирует в них адреса. И тогда получится, что ссылки на размер скидки будут указывать за пределы листа «БазаЦен», а формулы для расчета цен вернут сообщение об ошибке. Если же мы будем перемещать данные с листа на лист (использовать функцию «Вырезать»), правила преобразования адресов сработают по-другому. В этом случае в формулах на листе «БазаЦен» Excel проставит ссылки на ячейки листа «Прайс». И все будет работать правильно.
Посмотрим, как это выглядит на самом деле:
1) делаем активным лист «БазаЦен»;
2) щелкаем на ячейке «D2». В строке формул видим выражение «=C2*(1-Прайс!$D$9)». В этой формуле появилась ссылка на ячейку «D9» листа «Прайс». Благодаря такой ссылке формула работает правильно.
Теперь наша задача — значения в табличной части листа «Прайс» заменить ссылками на соответствующие ячейки из «БазаЦен». В принципе это можно сделать обычным способом — написать одну формулу со ссылкой и скопировать ее на всю область таблицы. Но мы поступим иначе — воспользуемся инструментом специальной вставки. Делаем так:
1) на листе «БазаЦен» выделяем блок с базой данных (на рис. 2 это фрагмент «A1:F12»);
2) нажимаем «Ctrl+С» (копировать фрагмент в буфер обмена);
3) переходим на лист «Прайс», становимся на ячейку «A13»;
4) вызываем меню «Главная». В группе «Буфер обмена» щелкаем на значке выпадающего списка иконки «Вставить». Откроется список, как на рис. 3;
5) из этого списка выбираем «Вставить связь». На листе «Прайс» появится табличная часть документа. Внешне документ остался таким же, как на рис. 1, но содержимое его изменилось.
Щелкаем левой кнопкой мыши на ячейке «D2» листа «Прайс». В строке формул видим выражение: «=БазаЦен!D2». Это значение мелкооптовой цены для товара «Блокнот», взятое из ячейки «D2» листа «БазаЦен». В данном случае инструмент «Вставить связь» себя оправдал — мы не стали вводить и копировать формулы вручную, Excel сделал это автоматически.
Совет Чтобы заполнить часть рабочего листа ссылками на ячейки другого листа, используйте вставку из буфера в режиме «Вставить связь». При работе с большими объемами данных это существенно сэкономит ваше время. Еще большую экономию вы получите, создавая таким способом ссылки между разными рабочими книгами.
Прайс практически готов к работе. Нам остается дополнить его двумя колонками, которые понадобятся при печати ценников. Здесь ситуация такова. На ценнике часто пишут значение в виде суммы в гривнях и копейках, разделенных символом «-» (дефис). Например, цена «5 грн. 70 коп.» может быть представлена в виде «5-70». То есть для формирования ценников нам нужно получить отдельно целую часть цены в гривнях и остаток в копейках. Решать такую задачу в программе Word неудобно. Мы воспользуемся Excel. Делаем так:
1) переходим на лист «БазаЦен»;
2) в ячейку «G1» пишем заголовок «РознГрн»;
3) в ячейку «G2» вводим формулу «=ЦЕЛОЕ(C2)» и копируем ее на всю высоту базы данных. Эта формула определит часть розничной цены в гривнях;
4) в ячейку «H1» вводим заголовок «РознКоп»;
5) в «H2» вводим формулу «=ЕСЛИ((ОКРУГЛ(C2;2)-ЦЕЛОЕ(C2))* 100<9;"0"&ОКРУГЛ((ОКРУГЛ (C2;2)-ЦЕЛОЕ(C2))*100;0);""&ОКРУГЛ((ОКРУГЛ(C2;2)-ЦЕЛОЕ(C2))*100;0))». Эта формула покажет остаток цены в копейках. Причем если этот остаток меньше девяти, то формула добавит к значению слева символ «0». Это сделано для того, чтобы для цены «5 грн. 5 коп.» получить значение «5–05», а не «5-5». Так результат будет более наглядным. Окончательный вид листа «БазаЦен» после наших преобразований показан на рис. 4. Теперь все готово для организации рассылки.
Создаем рассылку
База данных для организации рассылки у нас есть. Настало время создать документ в программе Word, на котором будут сформированы ценники. Этот документ имеет одну особенность. Размер одного ценника обычно невелик. На одной странице вполне поместятся один-два десятка таких элементов. Поэтому документ с ценниками имеет смысл оформить в виде таблицы из нескольких строк и колонок, а в каждой ячейке таблицы сформировать ценник для одного конкретного товара. Иными словами, в каждой ячейке таблицы мы должны поместить информацию из одной записи листа «
БазаЦен». Весь документ будет представлять собой набор ценников для каждого товара. Чтобы размножить эти ценники для нескольких торговых точек, достаточно напечатать документ несколько раз. На мой взгляд, такая организация документа вполне естественна и очень удобна. Остается реализовать ее на практике.Среди инструментов слияния в программе Word 2007 для описанной задачи лучше всего приспособлено средство печати наклеек. При этом все, что нам нужно сделать, — это определить размер одной наклейки, связать документ с источником данных и выполнить слияние. Программа Word автоматически создаст таблицу и поместит в нее информацию из базы данных. Делаем так:
1) вызываем программу Word, создаем новый документ (комбинация «
Ctrl+N»);2) переходим к меню «
Рассылки» (рис. 5); 
3) в группе «Начать слияние» щелкаем на одноименной иконке «Начать слияние». Откроется список, как на рис. 6;
4) из этого списка выбираем вариант «Наклейки…». Откроется окно «Параметры наклейки», изображенное на рис. 7;
5) в этом окне параметр «Принтер» ставим в положение «Печать по страницам». Теперь укажем размеры наклейки. Их можно выбрать из списка «Поставщик наклеек:». Но мы введем эти параметры вручную;
6) в окне «Параметры наклейки» щелкаем на кнопке «Настройка…» (рис. 7). Откроется окно, изображенное на рис. 8;
7) в этом окне вводим параметры, как показано на рисунке: высота наклейки 5 см, ширина 3 см, число по вертикали 6, число по горизонтали 5 и т. д. (рис. 8);
8) нажимаем кнопку «ОК»;
9) в окне «Параметры наклейки» тоже нажимаем кнопку «ОК». На листе Word появится таблица из пяти строк и шести колонок. Каждая ячейки таблицы имеет размер 3х5 см;
10) щелкаем на иконке «Выбрать получателей» (рис. 5). Откроется меню, как на рис. 9;
11) из предложенного списка выбираем вариант «Использовать существующий список…». Откроется меню Проводника, которое называется «Выбор источника данных» (рис. 10);
12) в этом окне находим папку с файлом «Прайс» и выбираем ее. Появится промежуточное окно с перечнем листов в рабочей книге «Прайс» (рис. 10);
13) выбираем лист «БазаЦен» и нажимаем «ОК»;
14) в документе Word cтановимся на левую верхнюю ячейку таблицы;
15) в группе «Составление документа и вставка полей» щелкаем на иконке «Вставить поле слияния» (рис. 5);
16) из открывшегося списка (рис. 11) выбираем «Наименование». В ячейке таблицы появится надпись с названием этого поля (рис. 12);
17) нажимаем «Enter», чтобы перейти на другую строку в ячейке таблицы;
18) опять щелкаем на иконке «Вставить поле слияния» и добавляем в таблицу элемент «РознГрн»;
19) не переходя на новую строку, печатаем символ «-» (дефис) за ним вставляем поле «РознКоп». После наших действий первая ячейка документа выглядит, как показано на рис. 12;
20) в группе иконок «Составление документа и вставка полей» нажимаем кнопку «Обновить наклейки». Word заполнит таблицу данными из базы «БазаЦен». Но в настоящий момент в документе реальных значений мы не видим — вместо них отображаются имена полей;
21) в группе «Просмотр результатов» (рис. 5) щелкаем на одноименной иконке «Просмотр результатов». Документ примет вид, как на рис. 13;
22) в первой ячейке таблицы выделяем поле с названием товара;
23) переходим в меню «Главная», выбираем для названия шрифт «Calibri» размером «14 пт»;
24) выделяем поле с ценой товара, присваиваем шрифт «Calibri» размером «20 пт»;
Совет При создании макета рассылки в какой-то момент времени поля в документе могут исчезнуть. Иногда это происходит, например, при изменении формата полей или при их перемещении. Ничего страшного в этом нет, информация остается на месте. Чтобы снова появились поля, нужно щелкнуть на иконке «Изменить список получателей» (группа «Начать слияние» меню «Рассылки»). В появившемся окне для настройки списка получателей просто нажимаем «ОК». Word обновит документ, и все данные снова появятся на экране.
25) щелкаем на иконке «Найти и объединить»;
26) из предложенного списка выбираем «Изменить отдельные документы…». Программа Word выполнит слияние и заполнит таблицу ценников значениями из листа «БазаЦен».
Документ с рассылкой готов. Он представляет собой таблицу Word из пяти строк и шести колонок. В каждой ячейке этой таблицы расположен один ценник. Все, что остается сделать, — отправить документ на печать.
Для большей наглядности и простоты понимания цены в примере приведены без учета НДС, поэтому описанное в статье решение без каких-либо изменений подходит для плательщиков единого налога по ставке 10 % и частных предпринимателей, не являющихся плательщиками НДС. В следующем номере мы отдельно расскажем, какие дополнения нужно внести плательщикам НДС, чтобы ценники печатались в соответствии с требованиями законодательства.
Удачной работы! Жду ваших вопросов, предложений и замечаний на
bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
