Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1с-бухгалтерия» для дисциплин «Организация и методика аудита», «1с-бухгалтерия» (для (стр. 2 из 6)
Удалим из таблиц первые четыре строки с заголовком отчета и последнюю строку с итоговыми данными. Столбцы таблицы, озаглавленные «Дебет» и «Кредит» содержат одни и те же номера счетов для всех строк. Два последних столбца таблицы – «Валюта» и «Вал.Сумма». Все эти колонки не содержат полезной информации, их можно смело удалить из обеих таблиц.
В столбце «Содержание» таблиц 361-702.xls и 902-281.xls записан текст. Он занимает несколько строк. Первая строка — комментарий к проводке, который автоматически создается породившим проводку документом в программе «1С: Бухгалтерия». В следующих строках по порядку перечислены все субконто, подключенные к счетам дебета и кредита проводки соответственно. В таблице 902-281.xls это субконто «Виды деятельности», подключенное к субсчету 902 и субконто «Места хранения», «ТМЦ» и «Партии», подключенные к субсчету 281. В таблице 361-702.xls это субконто «Контрагенты» и «Заказы», подключенные к субсчету 361 из дебета проводки и субконто «Виды деятельности», в разрезе которых ведется аналитический учет на субсчете 702 из кредита проводки.
Такое представление текста неудобно: для использования инструментов обработки данных Excel все строки таблицы должны иметь одинаковую структуру и описывать один объект. Чтобы преобразовать наш отчет в базу данных нужно текст из колонки «Содержание» расположить в соседних колонках. При больших размерах таблицы эта операция очень трудоемка, однако Excel предлагает эффективный инструмент ее решения.
На самом деле текст в колонке «Содержание» представляет собой одну строку. Отдельные элементы этой строки разделены непечатным символом с кодом 010 (символ перевода каретки). Это означает, что разделить текст в колонке «Содержание» на несколько столбцов можно Мастером текстов. Нужно только указать ему символ-разделитель с ANSI-кодом 010. Для того чтобы ввести этот символ с клавиатуры, нужно нажать клавишу Alt, удерживать ее и набрать на цифровой клавиатуре код символа (в нашем случае — цифры 010).
Итак, последовательность действий по разбиению текста будет такой:
4 в таблице 902-281.xls справа от колонки «Содержание» добавляем несколько колонок (по количеству субконто); в нашем случае их будет 5, в конце концов лишние потом можно удалить;
4 в столбце «Содержание» удалим лишние пробелы для этого обратимся к команде Правка4Заменить, в поле Найти вводим два пробела, в поле Заменить на вводим один пробел, нажимаем кнопку Заменить все;
4 выделяем столбец «Содержание», вызываем Мастер текстов командой «Данные4Текст по столбцам»;
4 в первом окне Мастера установим переключатель в положение «с разделителями», нажимаем кнопку «Далее»;
4 на втором шаге отметим флажком, что символ-разделитель Другой; код символа-разделителя вводим в расположенном справа поле, для чего набираем Alt+010;
4 на третьем шаге указываем Мастеру текстов, какие из полученных столбцов нужно пропустить и выбираем формат данных для оставшихся столбцов; в таблице нам не нужны первые два столбца (комментарии к проводке и субконто «Виды деятельности», подключенного к субсчету 902 из дебета проводки); последние три столбца описывают аналитику субсчета 281 из кредита проводки: Место хранения, ТМЦ и партия, назначим для этих столбцов текстовый формат (рис. 3);
4 указываем в поле «Поместить в» координаты левого верхнего угла свободного диапазона таблицы и щелкнем по кнопке «Готово».
В таблице появятся новые столбцы F, G и Н, в которые разнесен текст из столбца «Содержание». Озаглавим их «Склад», «Товар» и «Партия» соответственно. Лишние колонки удалим. Чтобы таблица выглядела компактнее выполнить следующее:
4 выделим все ячейки листа;
4 щелкаем в области таблицы правой кнопкой мыши, выбираем из контекстного меню «Формат ячеек», на закладке «Выравнивание» убираем флажок «переносить по словам»;
4 последовательно выполняем команды «Формат 4Строка4 Автоподбор высоты» и «Формат4Столбец4Автоподбор ширины»;
4 удаляем столбец «Содержание». нужные данные из него уже перенесены в созданные столбцы «Склад», «Товар» и «Партия».
В результате таблица 902-281.xls должна выглядеть, как на рис. 4.
Рис. 3 — Выбор колонок в окне мастера текстов
Выполним аналогичные действия над таблицей 361-702.xls, с небольшими поправками. По субсчетам 361 и 702 количественный учет не ведется, вместе со столбцами «Дебет», «Кредит», «Валюта», «Сум.Вал» удалим и столбец «Кол-во». Из этой таблицы мы будем извлекать сведения о покупателях. Аналитический учет покупателей ведется на субсчете 361 в разрезе контрагентов и заказов. Текст из ячеек столбца «Содержание» этой таблицы мы разнесем не по трем, а по двум дополнительным столбцам, озаглавив их «Покупатель» и «Заказ». Преобразованная таблица 361-702.xls показана на рис. 5.
Рис. 4 — Форма таблицы 903-281.xls после обработки Мастером текстов
Рис. 5 — Форма таблицы 361-702.xls после обработки Мастером текстов
Итак, у нас есть две таблицы. В таблице 903-281.xls имеется информация о наименованиях ТМЦ, в таблице 361-702.xls — о наименованиях контрагентов и объемах реализации. Наша цель — связать эти таблицы в единый отчет, получив сведения о контрагентах и объемах реализованных им товаров по каждому виду ТМЦ. Такая задача является типичной для пользователей баз данных. Речь идет о связывании набора таблиц по общему ключу (признаку) с последующим формированием отчета через систему SQL-запросов. Мы ограничимся средствами электронной таблицы Excel.
Для начала внимательно изучим структуру данных и найдем признак, по которому можно объединить информацию из двух таблиц. Бросается в глаза тот факт, что в пределах календарного года каждому документу «Расходная накладная» соответствует уникальный номер. Он присваивается программой «1С: Бухгалтерия», обычно документы нумеруются автоматически в пределах календарного года. Программа проверяет уникальность номера даже при его исправлении «вручную»[2]. По окончании года нумерация документов начнется заново. Поэтому ячейки в столбце «Документ» обеих таблиц имеют уникальные значения для каждого документа в пределах года. На рис.5 можно заметить, что ячейки B2 и B3 имеют одинаковый текст «Расх. накл. РН-0000001». Записи во второй строке таблицы относятся к первой (и единственной) расходной накладной за 2002 год, в третьей строке — к первой расходной накладной за 2003 год. Если использовать в качестве ключа для поиска столбец «Документ» наших таблиц, отчет по структуре продаж будет формироваться неправильно: Excel не различит записи, сделанные документами за разные годы, но с одинаковыми номерами. Мы же хотим выполнять анализ структуры продаж за произвольный период времени. Для этого нужно в обе таблицы ввести дополнительную колонку и построить в ней уникальный ключ для поиска в базе данных. Синтезировать такой ключ легко: нужно объединить номер документа и дату его создания.
Итак, добавим в таблицы 361-702.xls и 902-281.xls по одному столбцу. Столбец должен располагаться первым слева, это важно для работы функций поиска в базе данных. Щелкаем правой кнопкой мыши по заголовку столбца «A», выбираем из контекстного меню «Добавить ячейки». Новый столбец озаглавим «Ключ» (в базе данных все колонки должны иметь имена!). В ячейку A2 таблиц 361-702.xls и 902-281.xls введем формулу =B2 & " - " & СЖПРОБЕЛЫ(C2) и скопируем ее во все ячейки нового столбца (рис. 6).
 |
Рис. 6 — Таблица с ключевым полем (колонка «Ключ»)
В этой формуле использованы две стандартные функции Excel для работы с текстом: оператор «&» и функция СЖПРОБЕЛЫ. Название функции СЖПРОБЕЛЫ — сокращение от слов «сжать пробелы». Параметром функции является текстовая строка или адрес ячейки, в которой записан текст. Диапазон ячеек в качестве аргумента указывать нельзя. Функция отбрасывает пробелы в конце текстовой строки. Внутри текста она заменяет несколько пробелов одним. В нашей формуле функция убирает все лишние пробелы из текста «Расх. накл. РН-0000001», расположенного в ячейке C2 (текст в этой ячейке изначально содержал 20 пробелов после номера накладной). Функцию СЖПРОБЕЛЫ часто используют для придания тексту красивого внешнего вида, или когда хотят немного сократить размер файла Excel за счет удаления лишних пробелов.
Текстовый оператор «&» (амперсант) эквивалент арифметического оператора. Оператор «+» складывает числа, оператор «&» сцепляет (соединяет) строки символов.Строки символов в формуле нужно взять в кавычки. Например, если в ячейке записать формулу = "Дебет - " & "Кредит ", то в результате появится текст «Дебет - Кредит». Конечно, нет смысла вводить слова «Дебет - Кредит» описанным способом, можно просто ввести этот текст в ячейку. Но оператор «&» позволяет сцепить текст из нескольких разных ячеек со строками символов в той же формуле. В формуле = B2& " - "&СЖПРОБЕЛЫ(C2) мы сцепляем дату из ячейки B2 с символами «пробел»-«дефис»-пробел» и с результатом выполнения функции СЖПРОБЕЛЫ(С2). В результате будет сформирован уникальный ключ для работы с записями базы данных.
Замечание
Оператор «&», можно заменить функцией СЦЕПИТЬ (Текст1; Текст2; … Текст30). Аргументами этой функции могут быть до тридцати строк, чисел или ссылок на ячейки. Если использовать функцию СЦЕПИТЬ в нашем примере, то в ячейку А2 нужно записать формулу = СЦЕПИТЬ (B2; " - "; СЖПРОБЕЛЫ(C2)).