Автоматичне друкування цінників за допомогою MS Office 2007
Цю статтю я почну з фрагмента одного з листів, що надійшли до редакції: «Шановний «Б & К»! Я працюю на підприємстві, в якого є чимала мережа роздрібної торгівлі. Ціни на товари періодично змінюються. І кожного разу після такої зміни ми коригуємо прайс-лист, потім друкуємо нові цінники, щоб передати їх на всі торговельні точки. Підкажіть, як можна автоматизувати цей процес? У нашій бухгалтерії ми використовуємо програми MS Office версії 2007…
». Думаю, зрозуміло, що в цій статті йтиметься про формування серійних документів. Тільки цього разу поговоримо не про вітальну розсилку, а про практичне бухгалтерське завдання.Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
На шпальтах нашого видання ми вже порушували тему роботи з серійними документами (або так званими розсилками). Наприклад, докладну статтю про роботу з розсилками в MS Office 2007 можна знайти в «Б & К», 2011, № 4 (100). Нагадаю: тоді йшлося про організацію вітальної розсилки, коли є текст поздоровлення в форматі програми 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 стаємо на ліву верхню комірку таблиці;
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 .
