Строим карту покупок клиента
 | В последнее время в почту нашей редакции стали поступать письма с вопросами об использовании результатов бухгалтерского учета для оценки хозяйственной деятельности предприятия или определения эффективности отдельных компонент бизнес-процессов. Часть этих вопросов относится к такой важнейшей составляющей, как реализация товаров, работ или услуг. Одним из самых информативных документов для решения этой задачи является так называемая карта покупок клиента. Она позволяет не только проанализировать историю взаимоотношений организации с конкретным контрагентом, но и оценить перспективы их развития. Как построить карту покупок, опираясь на программы «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 | ВД1 | Контрагенты | ВД | Контрагенты | Регистрация дохода (если продажа — первое событие) |
| Заказы | Заказы | ||||
| Валовые доходы/расходы | Валовые доходы/расходы | ||||
| 2 | 361 | Контрагенты | 702 | Виды деятельности | Отгружен товар покупателю |
| Заказы | Места хранения | ||||
| — | — | ||||
| 3 | 702 | Виды деятельности | 6415 | Налоги и отчисления | Начислены обязательства по НДС |
| — | — | ||||
| — | — | ||||
| 4 | 902 | Виды деятельности | 281 | Места хранения | Списана балансовая стоимость товаров |
| — | ТМЦ | ||||
| — | Партии | ||||
1 Мы будем формировать карту покупок за 4 квартал 2010 г., поэтому в проводках использован счет ВД.
Из этого перечня нам нужны две группы проводок:
1) «Дт361 — Кт702» (вторая строка таблицы), у них в дебете есть субконто «Контрагенты» и «Заказы»;
2) «Дт902 — Кт281» (четвертая строка таблицы), по кредиту этих проводок можно отследить данные о местах хранения, наименованиях ТМЦ и партиях.
Объединив эти данные в одну таблицу, мы узнаем, какие товары были проданы и кому конкретно. Осталось понять, как это сделать в программе «1С:Бухгалтерия».
Формируем журнал проводок и загружаем в MS Excel
Из стандартных отчетов программы «1С:Бухгалтерия» для решения нашей задачи лучше всего подходит отчет по проводкам. Чтобы воспользоваться им делаем так.
1. Загружаем программу «1С:Бухгалтерия 7.7», входим в меню «Отчеты → Отчет по проводкам».
2. В параметрах отчета вводим корреспонденцию счетов в виде строки «361,702» и указываем интервал обработки проводок. Я выбрал 4 квартал 2010 г. (рис. 1).
3. Щелкаем на кнопке «Сформировать». Откроется окно с результатами обработки запроса.
4. Не закрывая этого окна, нажимаем «Файл → Сохранить как», появится окно Проводника программы «1С:Бухгалтерия 7.7».
5. В Проводнике выбираем «тип файла:» — «Таблица Excel (*.xls)», в качестве имени файла вводим «361-702.xls». Затем указываем папку для сохранения результата и нажимаем «Enter».
6. Повторим те же действия для корреспонденции проводок «902,281», отчет сохраним в формате Excel с именем «902-281.xls».
7. Открываем созданные файлы в программе MS Excel. Пример исходного отчета с корреспонденцией «902,281» показан на рис. 2.
После экспорта данных из «1С» в программу MS Excel в ней не видны ярлычки листов. А в некоторых случаях изменится и способ адресации ячеек со стандартного на «R1C1». Чтобы устранить эту проблему в MS Excel 2010 делаем так.
1. Вызываем меню «Файл → Параметры».
2. Переходим на закладку «Дополнительно». В группе «Показать параметры для следующей книги» включаем флажок «Показывать ярлычки листов» (рис. 3).
3. Переходим на закладку «Формулы». В группе «Работа с формулами» убираем флажок «Стиль ссылок R1C1».
4. В окне настройки параметров нажимаем «ОК».
Пока что листы не видны на экране. Зато в левом нижнем углу окна MS Excel появилась вертикальная полоска — маркер разделения области с ярлычками листов. Сейчас эта область имеет нулевую ширину. Теперь делаем так.
1. Ставим указатель мышки на разделительную полосу.
2. Когда указатель примет вид двойной полоски со стрелками, нажимаем левую кнопку мышки и, удерживая ее, перемещаем маркер вправо. На экране появятся ярлычки листов. Можно приступать к обработке данных в MS Excel 2010.
Журнал в MS 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» записана информация о наименованиях ТМЦ, которые покупались в 4 квартале 2010 г. На листе «361-702» есть сведения о контрагентах и объемах реализации товаров. Наша задача — связать обе таблицы в единый отчет, получив сведения о контрагентах и объемах реализованных ими товаров по каждому виду ТМЦ. Для этого нам нужен признак, по которому можно объединить информацию из указанных таблиц.
В принципе таким признаком мог бы служить номер документа «Расходная накладная». Его формирует программа «1С:Бухгалтерия». Причем эти документы она нумерует автоматически в пределах календарного года. Но есть одна проблема. Если мы захотим формировать документы за произвольный период, такой подход работать не будет. По окончании года нумерация документов в программе «1С» начинается заново. Поэтому номер накладной в таблице будет не уникальным. И тогда MS Excel не сможет различить записи, сделанные документами за разные годы, но с одинаковыми номерами.
Решить эту проблему можно следующим образом. Добавим в обе таблицы колонку, куда запишем уникальный ключ для поиска в базе данных. Синтезировать такой ключ легко — при помощи формулы, объединив номер документа и дату его создания. Ключ мы вставим в обе таблицы и поместим его в отдельной колонке слева от основной части данных. Последнее обстоятельство очень важно: для работы функций поиска столбец с рабочим ключом должен находиться в базе первым слева. А дальше делаем так.
1. Последовательно открываем листы «361-702» и «902-281». Щелкаем правой кнопкой мышки по заголовку колонки «A», выбираем из контекстного меню «Вставить». Слева на листе появится новая колонка.
2. Становимся на ячейку «A1», вводим заголовок «Ключ» (в базе все колонки должны иметь имена).
3. В ячейку «A2» таблиц «361-702» и «902-281» вводим формулу «=B2&"-"&СЖПРОБЕЛЫ(C2)».
4. Копируем формулу на всю высоту таблицы. Результат показан на рис. 14.
Пару слов о формуле. В ней использованы два инструмента MS 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» содержит данные о покупках за 4 квартал 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), загружаем файл на свой компьютер. Называется этот файл «MS Excelref.zip».
2. Распаковываем архив в отдельную папку. В ней появится файл «MS Excel - Выгрузка произвольных справочников в MS Excel с сохранением структуры.ert». Имя при желании можно изменить.
3. В программе «1С» вызываем «Файл → Открыть» (или «Ctrl+O»). Откроется окно Проводника.
4. В нем указываем путь к файлу «MS Excel - Выгрузка произвольных справочников в MS Excel с сохранением структуры.ert» и нажимаем «Открыть». Отчет появится в списке доступных обработок.
5. Через меню «Файл» вызываем этот отчет. Откроется окно, изображенное на рис. 19. В нем нужно настроить параметры программы.
6. Щелкаем на значке выпадающего списка «Тип Справочника:», выбираем вариант «Номенклатура».
7. Щелкаем на значке «…» (троеточие) справа от списка «Владелец:». Выбираем справочник «Товары».
8. Большинство полей справочника нам не нужны, поэтому нажимам кнопку с изображением символов «=>>» и переносим все поля в область «Виды необрабатываемых полей».
9. В окошке «Виды необрабатываемых полей» выделяем элемент «ПолнНаименование».
10. Нажимаем кнопку «<-», переносим это поле в область «Виды обрабатываемых полей».
11. Нажимаем кнопку «Сформировать». Через некоторое время откроется документ MS Excel. В нем на отдельном листе с именем «Номенклатура» будет перечень товаров из соответствующего справочника «1С».
12. Вначале списка добавляем строку с заголовками и получаем результат, как на рис. 20.
13. Переходим на лист «902-281». Фильтром выбираем одного покупателя. Например, фирму «Стрим».
14. Через буфер обмена копируем эти данные на новый рабочий лист. Этот лист я назвал «Спр». На нем собраны все покупки, которые сделал контрагент «Стрим» в 4 квартале 2010 г.
15. Дважды щелкаем на ярлычке листа «Номенклатура» и вводим для него новое название — «КартаПокупок». Теперь наша задача отметить в ячейках листа «КартаПокупок» те позиции, которые были закуплены фирмой «Стрим» в 4 квартале 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 или на форуме редакции.
