Будуємо карту купівель клієнта
 | Останнім часом до нашої редакції почали надходити листи із запитаннями про використання результатів бухгалтерського обліку для оцінки господарської діяльності підприємства або визначення ефективності окремих компонентів бізнес-процесів. Частина цих запитань стосується такої найважливішої складової, як реалізація товарів, робіт або послуг. Одним з найінформативніших документів для вирішення цього завдання є так звана карта купівель клієнта. Вона дозволяє не лише проаналізувати історію взаємовідносин організації з конкретним контрагентом, а й оцінити перспективи їх розвитку. Як побудувати карту купівель, спираючись на програми «1С:Бухгалтерія 7.7» та MS Excel 2010, які бухгалтерські нюанси потрібно враховувати при виконанні цієї роботи, розглянемо в цій статті. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
По суті, карта купівель — це результат порівняння двох списків: повного переліку товарів та реальних закупівель конкретного клієнта. Цей документ зазвичай організовано у вигляді таблиці. В її рядках перелічено всі найменування товарів, що реалізуються. У колонках таблиці вказуються періоди (місяці, квартали тощо). А в елементах таблиці розташовано обсяги закупівель, які здійснив контрагент за тією чи іншою позицією номенклатури. Таким чином, карта купівель у стислій та наочній формах показує, що було куплено клієнтом, коли та в якому обсязі, а також які товарні позиції його не зацікавили. У реальному житті карта купівель — дуже корисний документ. Я, наприклад, за цією картою оцінював динаміку реалізації товарів. Відділ збуту використовував її для планування графіка поставок. Відділ маркетингу за картою купівель формував пакети пропозицій для клієнтів нашої фірми тощо.
На практиці списки для формування карти купівель можуть бути великими — сотні, тисячі, а часом і десятки тисяч записів. Зрозуміло, що для роботи з ними краще використовувати спеціальні програми, наприклад систему управління базами даних MS Access. Однак Access не є основним бухгалтерським інструментом, і вивчати його для вирішення однієї конкретної проблеми немає жодного сенсу. Тому ми обмежимося звичним та перевіреним MS Excel. До того ж для наших цілей його можливостей більше ніж достатньо. Але спочатку розберемося з головним питанням — де та як отримати інформацію для складання карти купівель?
Вихідні дані для карти купівель
Для створення карти купівель нам потрібно два списки — номенклатуру товарів, що продаються, і дані про купівлі в розрізі контрагентів. Ці списки має бути оформлено як базу даних Excel (перший рядок — заголовки, далі розташовуються самі дані). Почнемо з контрагентів.
ФОРМУЄМО БАЗУ ДАНИХ ПРО КУПІВЛІ
Ці відомості ми можемо отримати із системи автоматизації бухгалтерського обліку, використовуючи декілька стандартних звітів. Я покажу, як це зробити на прикладі програми «1С:Бухгалтерія» (стандартна конфігурація для України). Але спочатку розберемося з бухгалтерським аспектом цього завдання. Отже, ми хочемо знати, які товари (продукцію, роботи чи послуги) придбав конкретний покупець за певний проміжок часу. За допомогою стандартного звіту отримати таку інформацію в більшості бухгалтерських програм нам не вдасться, і ось чому.
АНАЛІЗУЄМО ПРОВОДКИ
При реалізації товару заборгованість контрагента відображається проводкою «Дт36 — Кт70». У програмі «1С:Бухгалтерія 7.7» її формує документ «Расходная накладная». При списанні собівартості товарів він формує групу проводок типу «Дт90 — Кт28», для готової продукції задіяно «Дт90 — Кт26».
У програмі «1С:Бухгалтерія 7.7» аналітичний облік організовано через списки, що називаються субконто. Якщо субконто підключено до певного рахунка, то при формуванні проводок на цьому рахунку програма запропонує вибрати один з елементів відповідного списку. Потім вона збереже цей елемент у проводку. Як наслідок, аналітика у програмі «1С» є властивістю рахунка, а не проводки. І базу проводок у ній побудовано за наперед визначеною структурою аналітичного обліку. У цьому й полягає проблема.
Справа в тому, що у програмі «1С:Бухгалтерія» субконто «Контрагенты» підключено до рахунків «36», «63» та аналогічних. Субконто «Номенклатура» (список товарів) використовують для аналітичного обліку на рахунках «20», «22», «26», «28» тощо. При такій організації за базою проводок ми можемо дізнатися про обороти лише між тими субконто, які підключено до рахунків тієї самої операції. Наприклад, через звіт «Обороты между субконто» можна отримати інформацію про структуру закупівель. Це можливо завдяки тому, що у проводках «Дт20 — Кт63» або «Дт28 — Кт63» задіяно і ТМЦ, і постачальників одночасно. А ось проводки, де є і ТМЦ, і покупці, у базі даних немає. Тому інформацію про структуру продажів зі стандартного звіту «Обороты между субконто» отримати не можна. Нам доведеться одержати кілька різних звітів, потім обробити їх у програмі Excel. Але спочатку розберемося, які проводки формує документ «Расходная накладная» програми «1С:Бухгалтерія 7.7» (їх список наведено в таблиці).
Проводки документа «Расходная накладная»
| № з/п | Дебет проводки | Кредит проводки | Зміст проводки | ||
| Рахунок | Субконто | Рахунок | Субконто | ||
| 1 | ВД* | «Контрагенты» | ВД | «Контрагенты» | Реєстрація доходу (якщо продаж — перша подія) |
| «Заказы» | «Заказы» | ||||
| «Валовые доходы/расходы» | «Валовые доходы/расходы» | ||||
| 2 | 361 | «Контрагенты» | 702 | «Виды деятельности» | Відвантажено товар покупцю |
| «Заказы» | «Места хранения» | ||||
| — | — | ||||
| 3 | 702 | «Виды деятельности» | 6415 | «Налоги и отчисления» | Нараховано зобов’язання з ПДВ |
| — | — | ||||
| — | — | ||||
| 4 | 902 | «Виды деятельности» | 281 | «Места хранения» | Списано балансову вартість товарів |
| — | «ТМЦ» | ||||
| — | «Партии» | ||||
* Ми будемо формувати карту купівель за IV квартал 2010 року, тому у проводках використано рахунок ВД.
Із цього переліку нам потрібні дві групи проводок:
1) «Дт361 — Кт702» (другий рядок таблиці), у них до дебету проводки підключено субконто «Контрагенты» і «Заказы»;
2) «Дт902 — Кт281» (четвертий рядок таблиці), за кредитом цих проводок можна відстежити дані про місця зберігання, найменування ТМЦ та партії.
Об’єднавши ці дані до однієї таблиці, ми дізнаємося, які товари було продано та кому конкретно. Залишилося зрозуміти, як це зробити в програмі «1С:Бухгалтерія».
ФОРМУЄМО ЖУРНАЛ ПРОВОДОК ТА ЗАВАНТАЖУЄМО ДО MS EXCEL
Зі стандартних звітів програми «1С:Бухгалтерія» для вирішення нашого завдання найкраще підходить звіт щодо проводок. Щоб скористатися ним, виконуємо такі дії:
1) завантажуємо програму «1С:Бухгалтерія 7.7», входимо до меню «Отчеты → Отчет по проводкам»;
2) у параметрах звіту вводимо кореспонденцію рахунків у вигляді рядка «361,702» та вказуємо інтервал обробки проводок. Я вибрав IV квартал 2010 року (рис. 1);
3) клацаємо по кнопці «Сформировать». Відкриється вікно з результатами обробки запиту;
4) не закриваючи цього вікна, натискуємо на опцію «Файл → Сохранить как» — з’явиться вікно Провідника програми «1С:Бухгалтерія 7.7»;
5) у Провіднику вибираємо «тип файла:» — «Таблица Excel (*.xls)», як ім’я файлу вводимо «361-702.xls». Потім указуємо папку для збереження результату і натискуємо на «Enter»;
6) повторюємо ті самі дії для кореспонденції проводок «902,281», звіт збережемо у форматі Excel з ім’ям «902-281.xls»;
7) відкриваємо створені файли в програмі Excel. Приклад вихідного звіту з кореспонденцією «902,281» показано на рис. 2.
Після експорту даних з «1С» до програми Excel в ній не видно ярличків листів. А в деяких випадках зміниться і спосіб адресації комірок зі стандартного на «R1C1». Щоб усунути цю проблему, в Excel 2010 виконуємо такі дії:
1) викликаємо меню «Файл → Параметры»;
2) переходимо на закладку «Дополнительно». У групі «Показать параметры для следующей книги» включаємо прапорець «Показывать ярлычки листов» (рис. 3);
3) переходимо на закладку «Формулы». У групі «Работа с формулами» знімаємо прапорець «Стиль ссылок R1C1»;
4) у вікні настройки параметрів натискуємо на «ОК».
Поки що листи не можна побачити на екрані. Зате в лівому нижньому куті вікна Excel з’явилася вертикальна смужка — маркер поділу області з ярличками листів. Зараз ця область має нульову ширину. Виконуємо такі дії:
1) ставимо покажчик миші на розділову смугу;
2) коли покажчик набере вигляду подвійної смужки зі стрілками, натискуємо на ліву кнопку миші та, утримуючи її, переміщаємо маркер праворуч. На екрані з’являться ярлички листів. Можна розпочинати обробку даних в MS Excel 2010.
ЖУРНАЛ В EXCEL ПЕРЕТВОРЮЄМО В БАЗУ ДАНИХ
Звіти щодо проводок із програми «1С» не є базою даних. Із них потрібно прибрати все зайве, а саме шапку та рядок підсумків. Виконуємо такі дії:
1) через буфер обміну переносимо файли до однієї робочої книги. Я розташував дані на двох листах з іменами «361-702» та «902-281»;
2) видаляємо перші п’ять рядків в обох таблицях, залишивши тільки заголовки;
3) видаляємо дві останні колонки таблиці — «Валюта» і «Вал.Сумма». У нашому випадку вони не містять жодної корисної інформації;
4) видаляємо колонку із сумою проводки — нам ця інформація не потрібна. Нас цікавитиме лише кількість;
5) видаляємо останній рядок в обох таблицях, де записано підсумки. Результат нашої роботи для листа «361-702» показано на рис. 4.
Тепер розберемося, що у нас є насправді. Перша колонка в отриманих звітах — це дата проводки. Друга колонка — найменування та номер документа, що створив цю проводку.
Важливо! Якщо проводки створено тим самим документом, записи в колонках «Документ» відповідних рядків у таблицях «361-702» і «902-281» збігаються. Це — ключ до вирішення завдання звіту. Він дозволить об’єднати дані про покупців із таблиці «361-702» з інформацією про куплені товари в таблиці «902-281».
Один покупець зазвичай придбаває кількох товарів. Тому одному рядку з таблиці покупців (лист «361-702») можуть відповідати кілька рядків з таблиці товарів (лист «902-281»). Інакше кажучи, між таблицями діє відношення «один до багатьох». Тому простим об’єднанням двох списків ми відповідність не знайдемо. У цьому питанні нам допоможе функція «ВПР()» з арсеналу MS Excel. Але спочатку перетворимо обидві таблиці у формат бази даних.
Почнемо з колонки «Содержание», в якій записано текст, розбитий на декілька рядків. Перший рядок — коментар до проводки. Потім ідуть усі субконто, підключені до рахунків дебету і кредиту цієї проводки. Наприклад, у таблиці «902-281.xls» — це субконто «Виды деятельности» (підключено до субрахунку «902»), «Места хранения», «ТМЦ» і «Партии» (субрахунок «281»). У таблиці «361-702.xls» — це субконто «Контрагенты» і «Заказы» (субрахунок «361» дебету проводки) та «Виды деятельности», у розрізі яких ведеться аналітичний облік на субрахунку «702» кредиту проводки. Для формату бази даних такий формат не підходить — усі рядки таблиці повинні мати однакову структуру та описувати один об’єкт. Та до того ж і деякі субконто нам не знадобляться, їх можна просто видалити. Для великих таблиць виконати таку роботу вручну дуже складно. Але в нашому випадку завдання спрощується, оскільки окремі частини рядка «Содержание» програма «1С» розділяє спеціальним символом «переведення каретки». Цей символ має внутрішній код «010». Його не можна побачити на екрані, він не виводиться на друк. Єдина його мета — вставити в потрібному місці документа перенесення рядка. Зате ми можемо використовувати символ «010» як роздільник, щоб розбити текст у колонці «Содержание» на кілька стовпців. І все, що нам знадобиться, — Майстер текстів MS Excel. Виконуємо такі дії:
1) у таблиці «902-281.xls» праворуч від стовпця «Содержание» додаємо декілька колонок. У нашому випадку їх буде п’ять (по кількості субконто);
2) у стовпці «Содержание» видаляємо зайві пробіли. Для цього викликаємо меню «Главная», у групі «Редактирование» клацаємо по іконці «Найти и выделить». Відкриється меню, як на рис. 5;
3) із цього меню вибираємо «Заменить». Відкриється вікно «Найти и заменить» (рис. 6);
4) у полі «Найти» вводимо два пробіли, у полі «Заменить» — один пробіл;
5) натискуємо на кнопку «Заменить все»;
6) виділяємо стовпець «Содержание», викликаємо меню «Данные»;
7) у групі «Работа с данными» клацаємо по іконці «Текст по столбцам» (рис. 7). Відкриється вікно роботи з Майстром текстів, як на рис. 7 (унизу);
8) у першому вікні Майстра ставимо перемикач «Укажите формат данных:» у положення «с разделителями». Натискуємо на кнопку «Далее». Вікно набере вигляду, як на рис. 8;
9) уключаємо прапорець, позначаючи, що символ-роздільник — «другой:». У полі для роздільника вводимо «Alt+010»;
Важливо! Щоб увести цей символ «010» з клавіатури, натискуємо на клавішу «Alt» і, утримуючи її, друкуємо на цифровій(!) клавіатурі цифри «010»;
10) клацаємо по кнопці «Далее». Вікно набере вигляду, як на рис. 9;
11) у ньому вказуємо, які колонки потрібно пропустити. Для решти колонок вибираємо формат даних. У таблиці нам не потрібні перші два стовпці (коментарі до проводки і субконто рахунка «902»). Для цих колонок перемикач «Формат данных столбца» ставимо в положення «пропустить столбец». Останні три стовпці описують аналітику субрахунку «281» з кредиту проводки: «Место хранения», «ТМЦ» і «Партия». Для цих колонок перемикач ставимо в положення «текстовый»;
12) у вікні Майстра текстів клацнемо по кнопці «Готово».
Порада При роботі з Майстром текстів утримуйте натисненою клавішу «Shift», щоб виділити кілька сусідніх стовпців. Щоб вибрати несуміжні колонки, утримуйте натисненою клавішу «Ctrl».
У нашій таблиці з’являться нові стовпці «C», «D», «E», у яких міститься текст зі стовпця «Содержание». Назвемо їх «Склад», «Товар» та «Партия» відповідно. Зайві колонки видаляємо. Відформатуємо таблицю, щоб вона мала компактніший вигляді. Для цього виконуємо такі дії:
1) виділяємо всі комірки листа;
2) клацаємо по виділеному правою кнопкою миші. Із контекстного меню вибираємо «Формат ячеек…». Відкриється вікно «Формат ячеек», як на рис. 10;
3) переходимо на закладку «Выравнивание». Знімаємо прапорець «переносить по словам»;
4) у вікні «Формат ячеек» натискуємо на «ОК»;
5) не знімаючи виділення, викликаємо меню «Главная»;
6) у групі «Ячейки» клацаємо по іконці «Формат». Із запропонованого меню вибираємо «Автопобор высоты строки» (рис. 11);
7) знову клацаємо по іконці «Формат». Із меню вибираємо «Автопобор ширины столбца»;
8) видаляємо стовпець «Содержание» — потрібні дані ми перенесли до колонок «Склад», «Товар» і «Партия».
У результаті наших перетворень таблиця на листі «902-281» повинна мати вигляд, як на рис. 12.
Виконаємо аналогічні перетворення над таблицею на листі «361-702». Щоправда, з невеликою відмінністю. Оскільки на субрахунках «361» і «702» кількісний облік не ведеться, разом із колонками «Дебет», «Кредит», «Валюта», «Вал. Сумма» видалимо і стовпець «Кол-во». Із таблиці «361-702» ми отримуватимемо відомості про покупців. Аналітичний облік покупців ведеться на субрахунку 361 у розрізі контрагентів та замовлень. Тому текст стовпця «Содержание» ми рознесемо не по трьох, а по двох додаткових колонках, назвавши їх «Покупатель» та «Заказ». Перетворену таблицю «361-702» показано на рис. 13.
СТВОРЮЄМО РОБОЧИЙ КЛЮЧ
Отже, на поточний момент у нас є дві таблиці. На листі «902-281» записано інформацію про найменування ТМЦ, що придбавалися в IV кварталі 2010 року. На листі «361-702» є відомості про контрагентів та обсяги реалізації товарів. Наше завдання — об’єднати обидві таблиці в єдиний звіт, отримавши відомості про контрагентів та обсяги реалізованих їм товарів щодо кожного виду ТМЦ. Для цього нам потрібна ознака, за якою можна об’єднати інформацію із зазначених таблиць.
У принципі, такою ознакою міг би бути номер документа «Расходная накладная». Його формує програма «1С:Бухгалтерія». Причому ці документи вона нумерує автоматично в межах календарного року. Але є одна проблема. Якщо ми захочемо формувати документи за довільний період, такий підхід не працюватиме. Після закінчення року нумерація документів у програмі «1С» починається наново. Тому номер накладної в таблиці не буде унікальним. І тоді Excel не зможе розрізнити записи, зроблені документами за різні роки, але з однаковими номерами.
Вирішити цю проблему можна таким чином. Додамо до обох таблиць колонку, куди запишемо унікальний ключ для пошуку в базі даних. Синтезувати такий ключ легко — за допомогою формули, об’єднавши номер документа і дату його створення. Ключ ми вставимо до обох таблиць та помістимо його в окремій колонці ліворуч від основної частини даних. Остання обставина дуже важлива — для роботи функцій пошуку стовпець із робочим ключем має знаходитися в базі першим ліворуч. А далі виконуємо такі дії:
1) послідовно відкриваємо листи «361-702» та «902-281». Клацаємо правою кнопкою миші по заголовку колонки «A», вибираємо з контекстного меню «Вставить». Ліворуч на листі з’явиться нова колонка;
2) стаємо на комірку «A1», уводимо заголовок «Ключ» (у базі всі колонки повинні мати імена);
3) до комірки «A2» таблиць «361-702» і «902-281» уводимо формулу «=B2&”-”&СЖПРОБЕЛЫ(C2)»;
4) копіюємо формулу на всю висоту таблиці. Результат показано на рис. 14.
Пару слів про формулу. У ній використано два інструменти Excel для роботи з текстом — оператор «&» та вбудовану функцію «СЖПРОБЕЛЫ()».
Назва функції «СЖПРОБЕЛЫ()» — скорочення від «стиснути пробіли». Параметром функції є текстовий рядок чи адреса комірки, де записано текст. Діапазон комірок як аргумент зазначати не можна.
Функція відкидає пробіли на початку та в кінці текстового рядка. Усередині тексту вона замінює декілька пробілів на один. У нашому випадку функція прибере зайві пробіли з назв документів типу «Расх. накл. РН-0000001» (комірка «C2» та нижче). Особливість програми «1С» полягає в тому, що спочатку текст у цій комірці містив 20 пробілів після номера накладної. Текстовий оператор «&» (амперсанд) об’єднує декілька рядків символів в один. Таким чином, формула «=B2&” - “& СЖПРОБЕЛЫ(C2)» об’єднає дату з комірки «B2» із символом «-» (дефіс) та з результатом виконання функції «СЖПРОБЕЛЫ(С2)». У результаті ми отримаємо унікальний ключ для роботи із записами бази даних.
Порада Оператор «&» можна замінити вбудованою функцією «СЦЕПИТЬ()». Тоді формула в комірці «А2» буде такою: «=СЦЕПИТЬ (B2; " - "; СЖПРОБЕЛЫ(C2))».
Ознака для об’єднання таблиць «361-702» та «902-281» готова. Можна приступати до останнього етапу формування бази даних про купівлі клієнтів.
ОБ’ЄДНУЄМО ДВІ ТАБЛИЦІ ЗА РОБОЧИМ КЛЮЧЕМ
База на листі «902-281» містить більше інформації, ніж таблиця «361-702». Тому лист «902-281» буде основною таблицею, а лист «361-702» — допоміжною. В основній таблиці є дані про товари. Якщо доповнити її інформацією про покупців цих товарів, ми отримаємо заготівку для формування карти купівель. Для цього нам потрібно об’єднати основну й допоміжну таблиці за ознакою збігу ключів. Інакше кажучи, алгоритм наших дій буде таким:
1) для кожного рядка основної таблиці знаходимо запис із таким самим ключем у допоміжній таблиці;
2) переносимо до основної таблиці дані зі стовпців «Покупатель» і «Заказ».
Із таким завданням чудово впорається функція «ВПР()». Вона має такий вигляд: «ВПР(ИскЗнач; Блок;НомКол;ИнтПросм)», де:
— «ИскЗнач» — значення, яке потрібно знайти в першій зліва колонці масиву «Блок». У нас «ИскЗнач» — це ключ;
— «Блок» — масив комірок, де виконуватиметься пошук. У нас це таблиця на листі «361-702»;
— «НомКол» — номер стовпця (зсув) у масиві «Блок», звідки потрібно взяти результат. Ключ знаходиться в першому стовпці бази на листі «361-702». Отже, дані ми копіюватимемо з четвертої, п’ятої та шостої колонок;
— «ИнтПросм» — ознака, що задає критерій порівняння шуканого значення з ключем.
Про параметр «ИнтПросм» варто поговорити докладніше. Якщо його значення дорівнює «ЛОЖЬ», функція «ВПР()» шукатиме точну відповідність ключа шуканому значенню. При цьому працюватиме вона повільніше, зате сортувати допоміжну таблицю за робочим ключем не потрібно. Якщо параметр «ИнтПросм» дорівнює «ИСТИНА» (або не зазначений), функція шукатиме приблизну відповідність. У такому разі допоміжну таблицю має бути відсортовано за збільшенням значень ключового поля, зате пошук буде дуже швидким. Для нашого прикладу параметр «ИнтПросм» повинен мати значення «ЛОЖЬ». Виконуємо такі дії:
1) переходимо на лист «902-281». Доповнюємо його колонками «Покупатель» та «Заказ». У нас це колонки «J» і «K»;
2) стаємо на комірку «J2». Уводимо формулу «=ВПР(A2;'361-702'!$A$2:$G$5;4)»;
3) до комірки «K2» уводимо формулу «=ВПР(A2;'361-702'!$A$2:$G$5;5)»;
4) виділяємо обидві формули та копіюємо їх на всю висоту таблиці.
Щоб уникнути проблем з адресацією, виділяємо всю таблицю. Потім копіюємо її до буфера обміну та через режим спеціальної вставки вставляємо на те саме місце як значення.
У деяких рядках таблиці функція «ВПР()» може повернути значення «#Н/Д» (немає даних). Це означає, що «ВПР()» не знайшла відповідних ключів у допоміжній таблиці. Така ситуація можлива для документів «Поворотна накладна» або для видаткових накладних, де покупцем є іноземна фірма (заборгованість такого покупця реєструється проводкою по дебету субрахунку «362», а не «361»). У будь-якому разі всі записи, де є значення «#Н/Д», із таблиці «902-281» видаляємо. Результат нашої роботи показано на рис. 15.
Порада Рядки зі значеннями «#Н/Д» зручно видаляти за допомогою автофільтра. Для цього клацніть по іконці «Сортировка и фильтр» (меню «Главная», група «Редактирование»). З меню, що відкрилося, виберіть «Фильтр». Потім клацніть по значку списку в колонці «Покупатель» або «Заказ». У настройках фільтра поставте «галочку» біля елемента «#Н/Д» і натисніть на «ОК». На екрані залишаться записи, для яких пошук був неуспішним.
Як бачите, синтез ключа — надзвичайно ефективний прийом обробки даних. Він дозволяє вирішити завдання, не типові для програми MS Excel, наприклад, об’єднати інформацію з різних таблиць, спираючись на загальні ознаки. Використання робочого ключа допоможе виконати складне сортування даних, отримати підсумки або зведений звіт за кількома полями тощо.
ПЕРЕТВОРюєМО ДАНІ У ЗВІТ ЗА МІСЯЦЯМИ
Таблиця «902-281» містить дані про купівлі за IV квартал 2010 року. Причому ці дані зображено з точністю до одного дня (колонка «Дата»). Така деталізація в карті купівель не потрібна — достатньо бачити картину за місяцями. Але зараз таку операцію зробити не вдасться. Нам потрібне ще одне допоміжне поле, де для кожної дати буде написано номер місяця та номер року. Потім за цим полем можна побудувати зведений звіт, в якому дати поставити в колонках, а найменування контрагентів — у рядках. І тоді ми отримаємо остаточний варіант бази для подальшої роботи. Виконуємо такі дії:
1) відкриваємо лист «902-281». Праворуч від колонки «Дата» додаємо стовпець;
2) стаємо на комірку «C1» (початок нової колонки), вводимо заголовок «МесГод»;
3) переходимо на комірку «C2». Уводимо формулу «=МЕСЯЦ(B2)&"-"&ГОД(B2)»;
4) копіюємо формулу вниз на всю таблицю;
5) додаємо новий лист. Стаємо на комірку «A1» цього листа;
6) викликаємо меню «Вставка». У групі «Таблицы» клацаємо по іконці «Сводная таблица». У меню, що з’явилося, вибираємо «Сводная таблица» (рис. 16). Відкриється вікно, як на рис. 17;
7) у ньому вказуємо діапазон даних для формування зведеного звіту. У нашому прикладі — це колонки «A:L» на листі «902-281»;
8) зазначивши діапазон, у вікні «Создание сводной таблицы» натискуємо на «ОК». На робочому листі з’явиться область для зведеного звіту і вікно з переліком полів таблиці «902-281»;
9) формуємо макет звіту: поле «МесГод» переносимо до області колонок, поле «Товар» ставимо до області рядків, поле «Кол.» перетягуємо до області даних зведеної таблиці. Для поля «Кол» вибираємо операцію підсумовування. Результат формування зведеної таблиці показано на рис. 18;
10) виділяємо весь лист зі зведеним звітом;
11) копіюємо його до буфера обміну, вставляємо назад як значення. Заготовка для карти купівель готова.
Із базою купівель ми розібралися. Час переходити до другої частини нашого завдання.
Завантажуємо перелік товарів із програми «1С»
Тепер ми маємо підготувати перелік усіх товарів, що пропонуються на реалізацію. Його можна взяти з прайса чи іншого схожого документа. Але ми згідно з усталеною традицією отримаємо цей список із програми «1С». Для цього нам знадобиться спеціальний звіт-обробка. Але писати його ми не будемо, а звернемося за допомогою до пошукової системи Google. Відразу на першій сторінці запиту щодо експорту довідників з «1С» знаходимо посилання на вільно поширюваний звіт-обробку. Далі виконуємо такі дії:
1) переходимо за знайденим посиланням «http://xn—1-7sbcb9alydpqh.xn—p1ai/1cv77/all/74-exel-import-export » завантажуємо файл на свій комп'ютер. Називається цей файл «excelref.zip»;
2) розпаковуємо архів до окремої папки. У ній з’явиться файл «Excel - Выгрузка произвольных справочников в Excel с сохранением структуры.ert». Ім’я за бажання можна змінити;
3) у програмі «1С» викликаємо «Файл → Открыть» (або «Ctrl+O»). Відкриється вікно Провідника;
4) у ньому вказуємо шлях до файлу «Excel - Выгрузка произвольных справочников в Excel с сохранением структуры.ert» і натискуємо на кнопку «Открыть». Звіт з’явиться у списку доступних обробок;
5) через меню «Файл» викликаємо цей звіт. Відкриється вікно, зображене на рис. 19. У ньому потрібно настроїти параметри програми;
6) клацаємо по значку випадного списку «Тип Справочника:», вибираємо варіант «Номенклатура»;
7) клацаємо по значку «…» (трикрапка) праворуч від списку «Владелец:». Вибираємо довідник «Товары»;
8) більшість полів довідника нам не потрібні, тому натискуємо на кнопку із зображенням символів «=>>» та переносимо всі поля до області «Виды необрабатываемых полей»;
9) у вікні «Виды необрабатываемых полей» виділяємо елемент «ПолнНаименование»;
10) натискуємо на кнопку «<-» — переносимо це поле до області «Виды обрабатываемых полей»;
11) натискуємо на кнопку «Сформировать». Через деякий час відкриється документ Excel. У ньому на окремому листі з ім’ям «Номенклатура» буде перелік товарів з відповідного довідника «1С»;
12) на початку списку додаємо рядок із заголовками та отримуємо результат, як на рис. 20;
13) переходимо на лист «902-281». Фільтром вибираємо одного покупця, наприклад фірму «Стрим»;
14) через буфер обміну копіюємо ці дані на новий робочий лист. Цей лист я назвав «Спр». На нім зібрано всі купівлі, які здійснив контрагент «Стрим» у IV кварталі 2010 року;
15) двічі клацаємо по ярличку листа «Номенклатура» та вводимо для нього нову назву — «КартаПокупок». Тепер наше завдання — позначити в комірках листа «КартаПокупок» ті позиції, які було куплено фірмою «Стрим» у IV кварталі 2010 року.
Формуємо карту купівель
Усе, що нам залишається зробити, це порівняти дві бази: список пропонованих товарів та дані про реалізацію щодо одного конкретного контрагента. Для цього знову звернемося до вбудованої функції «ВПР()». Виконуємо такі дії:
1) переходимо на лист «КартаПокупок»;
2) стаємо на комірку «B2»;
3) уводимо формулу «=ЕСЛИ(ЕОШИБКА(ВПР($A2;Спр!$B$2:$F$9999;2;0));"-" ;ВПР($A2;Спр!$B$2:$F$9999;2;0))»;
4) до комірки «C2» вводимо «=ЕСЛИ(ЕОШИБКА (ВПР($A2;Спр!$B$2:$F$9999;2;0));"-" ;ВПР($A2;Спр!$B$2:$F$9999;3;0))»;
5) у комірці «D2» набираємо «=ЕСЛИ(ЕОШИБКА(ВПР($A2;Спр!$B$2:$F$9999;2;0));"-" ;ВПР ($A2;Спр!$B$2:$F$9999;4;0))». Усі ці формули майже однакові та відрізняються лише зсувом у параметрах «ВПР()» (позначено напівжирним зображенням);
6) виділяємо блок «B2:D2», копіюємо формули на всю висоту таблиці. Результат показано на рис. 21.
Для контролю в колонці «Итог» (стовпець «E», рис. 21) підраховуємо суму за рядками. Формула в комірці «E2» має такий вигляд: «=СУММ(B2:D2)». Такі самі підсумки підраховуємо на листі «Спр». Аналізуючи підсумки, можна вибірково перевірити правильність заповнення карти купівель. Однак повернемося до нашого результату на рис. 21. Перед нами наочний документ, в якому всі купівлі фірми «Стрим» видно як на долоні. Більше того, за картою купівель ми бачимо динаміку реалізації різних товарів покупцю «Стрим». І, що не менш важливо, — символом «-» позначено позиції, які фірма «Стрим» з якоїсь причини ще не купувала. На мій погляд, дуже корисна інформація для всебічної оцінки діяльності будь-якої компанії.
Успішної роботи! Чекаю ваших листів, запитань та пропозицій на bk@id.factor.ua , nictomkar@rambler.ru , my.karpenko@gmail.com або на форумі редакції.
