Учебное пособие: Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1с-бухгалтерия» для дисциплин «Организация и методика аудита», «1с-бухгалтерия» (для студентов спец.

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ УКРАИНЫ

ХАРЬКОВСКАЯ НАЦИОНАЛЬНАЯ АКАДЕМИЯ ГОРОДСКОГО

ХОЗЯЙСТВА

В печать

разрешаю

Первый проректор

Г. В.Стадник

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

к выполнению лабораторной работы

«Формирование отчета о структуре продаж по данным программы «1С-Бухгалтерия»

для дисциплин «Организация и методика аудита », «1С-Бухгалтерия »

(для студентов спец. 7.050106 «Учет и аудит», иностранных студентов и системы дистанционного образования)

ХАРЬКОВ—ХНАГХ— 2005

Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1С-Бухгалтерия» для дисциплин «Организация и методика аудита », «1С-Бухгалтерия » для студентов спец. 7.050106 «Учет и аудит», иностранных студентов и системы дистанционного. Сост.: Карпенко Н.Ю., Гордиенко Н.И., Рябченко И.Н. — Харьков: ХНАГХ, 2005. — 36 с.

Составители: Н.Ю. Карпенко,

Н.И. Гордиенко,

И.Н. Рябченко.

Рецензент: канд. экон. наук, доцент В.О. Костюк

Рекомендовано кафедрами «Информационных технологий», «Учета и аудита», протокол № 6 от «28» января 2005 г.

1. Цель работы

На практике часто возникает вопрос, какие товары, продукцию, работы или услуги приобретает конкретный покупатель. Такая информация может понадобиться, например, для построения очень важного отчета по структуре продаж или развернутого акта сверки.

К сожалению, получить эти данные в большинстве бухгалтерских программ, в частности — в «1С: Бухгалтерия 7.7 для Украины» (далее — «1С: Бухгалтерия» или «1С») непросто. Причина кроется не столько в несовершенстве программы, сколько в особенностях национальной системы бухгалтерского учета. При реализации товара задолженность контрагента отражается проводкой Дт36 – Кт70. В программе «1С: Бухгалтерия» это делает документ «Расходная накладная». Себестоимость товаров списывается проводкой Дт90–Кт28, себестоимость готовой продукции — проводкой Дт90–Кт26 и т.п. В бухгалтерских программах (в том числе и в программе «1С: Бухгалтерия») аналитика обычно является свойством счета, а не свойством проводки . Это является методологической ошибкой учета. Поясним сказанное на примере программы «1С: Бухгалтерия».

Аналитический учет в «1С» организован через списки, именуемые субконто. Если субконто подключено к некоторому счету, то при формировании проводок программа обязательно предложит выбрать один из элементов списка и сохранит его в проводке . То есть, база данных проводок формируется по заранее предопределенной структуре аналитического учета. В программе «1С: Бухгалтерия» субконто «Контрагенты» (список контрагентов) подключено к счетам 36, 63 и аналогичным. Субконто «ТМЦ» (номенклатура товаров) используется при ведении аналитического учета по счетам 20, 22, 26, 28 и аналогичным. При такой структуре на основании проводок можно определить обороты только между теми субконто, которые подключены к счетам одной и той же проводки . В программе «1С: Бухгалтерия» такую информацию сможет предоставить отчет «Обороты между субконто». Этот отчет может показать информацию о структуре закупок, поскольку и ТМЦ и поставщики участвуют в проводках вида Дт20 – Кт63 или Дт28 – Кт63. Но проводок, где корреспондируются одновременно и ТМЦ и купившие их организации, в базе «1С» нет [1] . Поэтому информацию о структуре продаж стандартным отчетом «Обороты между субконто» получить невозможно.

Решить задачу можно с помощью Excel. В лабораторной работе мы научимся строить отчеты такого типа по данным программы «1С: Бухгалтерия», используя в качестве инструмента расчетов MS Excel. Также мы познакомимся с использованием функций просмотра и деловой графики в практике бухгалтерского учета и аудита, а именно:

4 освоим использование текстовых функций;

4 изучим функции просмотра Excel;

4 проиллюстрируем применение сводных таблиц;

4 познакомимся с возможностями деловой графики Excel и ее применением для обработки данных в бухгалтерском учете и аудите

2. Содержание работы

Лабораторная работа состоит из следующих этапов.

1. Формирование индивидуальной базы данных о деятельности предприятия в программе «1С: Бухгалтерия».

2. Импорт базы данных в формат MS Excel.

3. Разработка и реализация модели для получения отчета о структуре продаж.

3. Методика выполнения работы

3.1. Подготовка исходных данных

Рассмотрим построение отчета по продажам покупных товаров отечественным покупателям. Эти операции регистрируются документом «Расходная накладная», который формирует в базе «1С» такие проводки:

№ п/п	Дебет проводки		Кредит проводки		Описание проводки
	Счет	Субконто	Счет	Субконто
1	ВД	Контрагенты	ВД	Контрагенты	Регистрация валового дохода (если продажа – первое событие)
		Заказы		Заказы
		Валовые доходы/расходы		Валовые доходы/расходы
2	361	*Контрагенты*	702	Виды деятельности	Отгружен товар покупателю
		Заказы		Места хранения
		-		-
3	702	Виды деятельности	6415	Налоги и отчисления	Начислены обязательства по НДС
		-		-
		-		-
4	902	Виды деятельности	281	Места хранения	Списана балансовая стоимость товаров
		-		*ТМЦ*
		-		Партии

Нас интересуют проводки вида Дт361 – Кт702 (вторая строка таблицы), в дебете которой участвуют субконто «Контрагенты» и «Заказы», и проводки Дт902 – Кт281 (четвертая строка таблицы), по кредиту которой раскрывается информация о местах хранения, наименованиях ТМЦ и их партиях. Отметим, что если отгрузка товаров не будет являться первым событием, документ не будет создавать проводку Дт «ВР» – Кт «ВР», а в третьей строке таблицы вместо проводки Дт702 – Кт6415 будет проводка Дт702 – Кт643. Но интересующие нас проводки будут созданы в любом случае. Отчет будем строить на примере предприятия ООО «Добро», демонстрационная база которого входит в состав поставки программы «1С».

Вызываем стандартный отчет из меню «Сервис4Отчет по проводкам», в параметрах указываем нужную корреспонденцию счетов: Д361–К702 (рис. 1). Результаты отчета сохраним в формате файла Excel с именем 361-702.xls. Проделаем то же самое для проводок Д902–К281, отчет сохраним с именем 902-281.xls. Отчеты можно строить за произвольный период времени . В примере отчет был построен за четыре месяца с 1 декабря 2002 г. по 31 марта 2003 г. Первая колонка в полученных отчетах — это дата записи проводки в базу данных, вторая колонка — наименование и номер документа, эту проводку создавшего.

Заметим, что если проводки были созданы одним и тем же документом, записи в колонках «Документ» соответствующих строк двух таблиц совпадают. Это – ключ к построению отчета по продажам , который позволит нам сопоставить информацию о покупателе (таблица 361-702.xls) и о закупленных им товарах (таблица 902-281.xls).

Один покупатель обычно покупает несколько товаров. Поэтому одной строке из таблицы покупателей будут соответствовать несколько строк из таблицы товаров (отношение «один ко многим»). В таблице 902-281.xls больше строк, чем в таблице 361-702.xls, и простым объединением двух таблиц мы соответствие не отыщем. Отыскать его нам поможет функция просмотра ВПР() из арсенала Excel. Но прежде чем применить ее, преобразуем полученные отчеты в форму, удобную для дальнейшей обработки.

Рис. 1— Получение отчета по проводкам в программе «1С: Бухгалтерия»

3.2. Преобразование исходных данных

Программы семейства «1С» сохраняют отчеты в виде XLS-файлов формата Microsoft Excel 5.0/95. При их открытии оказывается установленным стиль ссылок R1C1. Это оправдано при разработке макросов. При обычной работе с таблицей лучше применять стиль ссылок А1, когда адрес ячейки состоит не из номеров строки (R1) и столбца (C1), а из буквенного обозначения столбца (A) и номера строки (1). Для переключения стиля входим в «Сервис4Параметры», открываем закладку «Общие» и снямаем флажок с пункта «Стиль ссылок R1C1».

В книгах 361-702.xls и 902-281.xls пока по одному листу, но в процессе работы мы добавим еще несколько. Листать книги удобнее всего с помощью ярлычков листов. Перейдем на закладку «Вид » окна установки параметров книги, отметим флажком пункт «ярлычки листов » и щелкнем по кнопке «ОК ». В левом нижнем углу окна Excel (рис. 2) появится вертикальная полоска – маркер разделения области ярлычков листов. Но сам ярлычок листа виден не будет, так как область для отображения ярлычков листов имеет нулевую ширину . Подведем к маркеру курсор мыши. Когда он примет вид двух вертикальных полосок со стрелками, нажмем левую кнопку и, удерживая ее, передвинем маркер вправо (расширим область отображения ярлычков листов).

Рис. 2 — Отображение ярлычков листов

Удалим из таблиц первые четыре строки с заголовком отчета и последнюю строку с итоговыми данными. Столбцы таблицы, озаглавленные «Дебет» и «Кредит» содержат одни и те же номера счетов для всех строк. Два последних столбца таблицы – «Валюта» и «Вал.Сумма». Все эти колонки не содержат полезной информации, их можно смело удалить из обеих таблиц.

В столбце «Содержание » таблиц 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 после обработки Мастером текстов

3.3. Синтез ключа

Итак, у нас есть две таблицы. В таблице 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)) .

Синтез ключа – простой, но эффективный прием. Его применение нередко помогает решить проблемы, связанные с поиском, сортировкой, подведением промежуточных итогов или построением сводной таблицы, когда в исходной базе Excel нет подходящих данных.

3.4. Объединение таблиц по общему ключу

Таблица 902-281.xls содержит больше информации, чем таблица 361-702.xls. Ее мы будем использовать в качестве основной таблицы, а таблицу 361-702.xls — в качестве вспомогательной . В основной таблице есть данные о товарах. Если дополнить ее информацией о покупателях этих товаров, получится замечательная заготовка для анализа структуры продаж. Для построения такой заготовки нужно:

4 для каждой строки основной таблицы отыскать запись с аналогичным ключом во вспомогательной таблице;

4 дополнить основную таблицу данными из столбцов «Сумма », «Покупатель » и «Заказ ».

Иными словами, нам нужно объединить основную и вспомогательную таблицы по признаку совпадения ключей. Такую работу как нельзя лучше выполнит функция ВПР . Наименование функции ВПР происходит от «вертикальный просмотр». Параметрами функции являются: искомое значение, блок таблицы (массив ячеек) и смещение. Функция находит заданное значение в крайнем левом столбце таблицы и возвращает результат из той же строки с указанным смещением по столбцам.

Синтаксис функции ВПР:

ВПР(искомое_значение;Блок;номер_столбца;интервальный_просмотр), где:

4 искомое_значение – это значение, которое нужно отыскать в первом (крайнем левом) столбце массива Блок; в нашем примере — это значение ключа;

4 Блок - это массив ячеек с искомой информацией; в нашем случае — это вспомогательная таблица 361-702.xls;

4 номер_столбца – это номер столбца массива, из которого нужно скопировать результат. Ключ находится в первом столбце таблицы 361-702.xls. Данные мы будем копировать из четвертого, пятого и шестого столбцов;

4 интервальный_просмотр – аргумент, задающий критерий строгости проверки соответствия искомого значения ключу

Если параметру «интервальный просмотр» присвоить значение ЛОЖЬ (как в нашем примере), функция ВПР будет искать точное соответствие ключа искомому значению, но будет работать медленнее. При поиске точного соответствия сортировать таблицу не требуется. Если присвоить этому параметру значение ИСТИНА (или не указывать аргумент), функция будет искать приближенное [3] соответствие. В последнем случае таблица должна быть отсортирована по возрастанию значений ключевого столбца. Алгоритм быстрого поиска функции ВПР, на несортированных данных работает неправильно.

Дополним таблицу 902-281.xls столбцами «Сум. прод. », «Покупатель » и «Заказ ». Откроем в Excel обе таблицы, командой «Окно4Расположить» зададим расположение окон — сверху вниз. Сложные формулы удобно вводить с помощью Мастера функций. Перейдем к ячейке I2 таблицы товаров и вызовем Мастера щелчком по кнопке f_x . В раскрывшемся списке находим категорию «Ссылки и массивы», выбираем функцию ВПР.

На втором шаге Мастера функций (рис. 7) укажем, что во вспомогательной таблице нужно искать значение из ячейки A2 основной таблицы. Информация для просмотра содержится на листе Sheet1 таблицы 361-702.xls. Она открыта в нижнем окне Excel. Щелкаем мышью по заголовку столбцов этого окна и с помощью клавиш со стрелками (мышь не работает!) перейдем к ячейке A2. Удерживая нажатой клавишу Shift, выделим весь диапазон с данными этой таблицы.

В поле «Номер_индекса_столбца » введем цифру 4, в поле «Диапазон_просмотра » - текст «ЛОЖЬ »[4] . Функция найдет в таблице 361-702.xls строку с точно совпадающим ключом и вернет значение из четвертого столбца: 3600 грн., сравните его со значением ячейки D2 на рис. 7. Завершая работу Мастера, щелкнем по кнопке «ОК» .

Формулы для следующих двух столбцов таблицы практически идентичны, изменится только номер индекса столбца, с 4-го на 5-й и 6-й соответственно. Здесь уместно вспомнить, что Формула в Excel – это обычный текст, составленный по определенным правилам . Чтобы не вызывать Мастер функций повторно, скопируем формулу из ячейки I2 в J2 и K2 как текст (через буфер обмена) и исправим в текстах формул номера столбцов. По строкам формулы скопируем обычным для Excel способом. Результат показан на рис. 8. В шестой, восьмой и девятой строках таблицы функция ВПР вернула значение #Н/Д (нет данных). Это значит, что она не смогла найти соответствующий ключ в таблице 361-702.xls. Запись в шестой строке таблицы создана документом «Возвратная накладная». Товар этот документ списывает проводкой Дт902–Кт281 (красным сторно), а задолженность покупателя – проводкой Дт704-Кт361.

В восьмой и девятой строках таблицы зарегистрированы проводки документов «Расходная накладная», но покупателем в них была иностранная фирма. Задолженность иностранного покупателя регистрируется проводкой в дебет субсчета 362, а не 361. Мы отбирали из «1С» исключительно проводки Дт361-Кт702. Поэтому ключей, соответствующих этим строкам не нашлось. Поскольку нам нужно проанализировать структуру продаж (а не возвратов) отечественным (а не зарубежным) покупателям, просто удалим эти строки из таблицы.

Совет

В больших таблицах строки со значениями #Н/Д удобно искать и удалять с помощью автофильтра выполните команду «Данные4Фильтр4Автофильтр», из выпадающего меню автофильтра в колонке «Сум.прод.» выберите #Н/Д. Будут показаны все ячейки, для которых функция просмотра не нашла информацию.

Взглянем внимательно на формулы в нашей таблице. Мастер формул поставил в них ссылки на рабочую книгу 361-702.xls (записаны в квадратных скобках). Это означает, что при открытии файла 902-281.xls, Excel автоматически откроет файл 361-702.xls и обновит связи с ним. Это не страшно, но со временем изрядно надоедает. Чтобы избежать такой ситуации, скопируем таблицу 902-281.xls в буфер обмена и вставим на рабочий лист как значения при помощи режима специальной вставки .

3.5. Другие функции просмотра

У функции ВПР есть «двойник» - функция ГПР ( «горизонтальный просмотр»). Разница между ними в том, что функция ВПР работает со столбцами, а ГПР – со строками. То есть функция ГПР позволяет получить значение из строки с указанным номером, отыскав соответствие искомого значения и ключа в верхней строке диапазона. И синтаксис, и особенности их применения обеих функций одинаковы. Еще одна функция просмотра так и называется: ПРОСМОТР. Она имеет две синтаксические формы: вектор и массив[5] . Вектор в Excel — это диапазон ячеек, который содержит только одну строку или один столбец. Векторы можно задавать непосредственно в функции, записывая их через точку с запятой или в фигурных скобках. Например {1; 2; 3} или {«один»; «два»; «три»}. Синтаксис векторной формы: =ПРОСМОТР(значение;вектор;вектор_результатов), где:

4 значение – то значение, которое нужно отыскать;

4 вектор – диапазон, в котором выполняется поиск;

4 вектор_результатов – строка или столбец, из которого функция берет ячейку, соответствующую по порядку найденной в диапазоне поиска.

Просматриваемый вектор обязательно должен быть отсортирован по возрастанию. Это очень важно: алгоритм быстрого поиска работает правильно только на отсортированных по возрастанию данных. В векторе {5; 2; 3} функция не найдет значение 5. В функции ПРОСМОТР нельзя выполнить поиск на строгое соответствие, она всегда ищет приближенное соответствие. В этом ее недостаток – если точного соответствия не нашлось, функция вернет близкое значение, а не значение #Н/Д. В нашем примере важно отыскать совпадение ключей, поэтому мы использовали функцию ВПР и задали аргументу интервальный_просмотр значение ЛОЖЬ. Зато просматриваемый вектор и вектор результатов функции ПРОСМОТР могут быть расположены на листе как угодно. Например, просматриваемый вектор может быть столбцом, вектор результатов – строкой, и наоборот. Можно даже задать один или оба вектора непосредственно в формуле. Главное, чтобы совпадали размерности векторов, то есть, чтобы векторы содержали одинаковое количество значений.

3.6. Анализ структуры продаж по сводным отчетам

Для анализа структуры продаж мы воспользуемся сводными таблицами. Для этого достаточно пройти всего четыре шага. Установив курсор в любую заполненную ячейку таблицы 902-281.xls (см. рис. 8), выполним команду «Данные4Сводная таблица». В открывшемся окне Мастера сводных таблиц проверим, что переключатель источника данных для сводной таблицы установлен в строке «в списке или базе данных Microsoft Excel» и щелкнем «Далее». Если перед вызовом Мастера поместить курсор в любую заполненную ячейку исходной таблицы, границы диапазона данных для построения сводной таблицы Excel определит автоматически[6] .

На втором шаге Мастера нужно подтвердить выбор диапазона, щелкнув по кнопке «Далее». Поскольку активная ячейка установлена в области базы данных, сводный отчет Excel будет формировать на отдельном листе. Этот лист Excel добавит автоматически.

На третьем шаге Мастер сводных таблиц предложит создать форму отчета сводной таблицы. Ее можно построить непосредственно на рабочем листе, мы рекомендуем делать это в специальной форме. Для этого нажимаем кнопку «Макет» и переходим к режиму формирования структуры сводного отчета. В окне макета представлена будущая структура сводной таблицы. В левой части расположена диаграмма будущей таблицы, в правой — в виде кнопок все поля (заголовки столбцов) базы данных. Перетащим мышью поле «Товар » в область «Столбец », поле «Покупатель » — в область «Строка », поле «Кол-во » — в область «Данные », как показано на рис. 9.

Выполним двойной щелчок левой кнопкой мыши на поле «Кол-во » в области данных. Так мы раскроем окно операций над этим полем: сумма, количество значений, среднее, максимум, минимум (всего 11 вариантов). Нужно убедиться, что для этого поля выбрана операция суммирования. Кроме того, в области Имя можно изменить заголовок, который Excel поставит в сводном отчете. Озаглавим поле «Кол.,шт.» и нажмем ОК. Окончательный вид сводного отчета показан на рис. 10.

Рис. 9— Формирование структуры отчета сводной таблицы

Рис. 10 — Сводный отчет о структуре продаж

3.7. Использование деловой графики для анализа структуры продаж

Для иллюстрации полученных результатов удобно использовать деловую графику Excel. Рассмотрим этот процесс более подробно. В Excel встроены 14 стандартных и 20 нестандартных типов диаграмм. Каждому типу соответствует несколько способов его визуального представления. Для быстрого построения диаграмм служит Мастер диаграмм. Он предполагает построение диаграммы за четыре шага, но в принципе достаточно пройти всего один. Все, что требуется — определить диапазон данных и выбрать тип графика. На остальных шагах уточняются только параметры диаграммы. Покажем работу с Мастером на примере.

Установив курсор в область данных сводной таблицы, выполним команду «Вставка4Диаграмма». Откроется окно Мастера диаграмм (рис. 11). Из перечня графиков в левой части окна Мастера выберем пункт «Гистограмма». В правой части окна выберем вид гистограммы - объемный вариант гистограммы с накоплением. Щелкнем мышью по выбранному виду, и фон его окна сменится со светлого на темный.

Рис. 11 — Определение типа диаграммы

Для стандартных типов диаграмм доступен режим быстрого просмотра результатов. Если нажать и удерживать левую кнопку мыши на кнопке «Просмотр результата » в нижней части окна Мастера, вместо панели «Вид» откроется панель «Образец» (рис. 12). В этой панели в уменьшенном масштабе будет показан пример диаграммы, построенной на данных таблицы пользователя. Если настройки по умолчанию вас устраивают — щелкните мышью по кнопке «Готово», и вы получите готовый график. Мы пройдем четыре шага Мастера до конца, поэтому щелкнем по кнопке «Далее».

На втором шаге можно уточнить диапазон данных для построения диаграммы. Как и при построении сводной таблицы, если перед вызовом Мастера диаграмм поместить курсор в заполненную данными область листа, по умолчанию будет выделена вся эта область, вместе с итоговыми значениями.

Рис. 12 — Окно просмотра образца диаграммы

Колонка диаграммы с общим итогом по продажам нам не нужна, поэтому мы выделим диапазон ячеек A2:D4 (с заголовками строк и столбцов, без итогов). Excel преобразует относительный адрес диапазона в абсолютный и добавит имя листа: =Лист1!$A$2:$D$4, — ведь диаграмму можно размещать как на текущем, так и на новом листе книги. Образец диаграммы показан на верхней панели окна Мастера (рис. 13).

В этом же окне можно уточнить, как располагаются ряды данных: в строках или в столбцах. Информация о покупателях содержится в строках нашей сводной таблицы. В диаграмме на рис. 13 два ряда данных — для покупателей «Дельта» и «Подарок».

Рис. 13 — Определения диапазона данных диаграммы

Можно указать, что ряды данных расположены в столбцах. Тогда в столбцах диаграммы будут отражены данные по товарам, а количество столбцов будет соответствовать количеству покупателей (рис. 14). На закладке «Ряд» этого окна Мастера можно уточнить состав отображаемых рядов, их наименования и диапазоны выводимых значений или нажать «Готово». Щелкнем по кнопке «Далее».

Рис. 14 — Изменение характера расположения рядов данных

На третьем шаге можно видоизменить внешний вид диаграммы. На закладке «Заголовки» этого окна (рис. 15) введем наименование диаграммы и подписи к ее осям. Введенные надписи немедленно появятся на образце нашего графика.

На остальных закладках этого окна можно уточнить другие параметры оформления графика. Например, на закладке «Легенда» можно указать место расположения легенды рядов: справа от диаграммы (рис. 16), вверху, внизу и т.д.

На последнем, четвертом шаге (рис. 16) выбираем место расположения диаграммы: на новом листе книги Excel или на имеющемся (текущем) листе. Наша сводная таблица небольшая, диаграмма вполне поместится рядом с ней[7] . Щелкнем по кнопке «Готово », и диаграмма будет вставлена в текущий лист (рис. 17).

Рис. 15 — Определение заголовков диаграммы

Рис. 16 — Выбор местоположения диаграммы

Рис. 17 — Окончательный вид диаграммы о структуре продаж

На рис. 17 видно, что диаграмма заключена в рамку, и в окне Excel появилась панель инструментов «Диаграммы ». Подведите курсор мыши к диаграмме. Когда курсор примет вид крестика со стрелками, диаграмму можно будет перетащить в другое место листа. Если подвести курсор к границе окна диаграммы, он примет вид двойной стрелки. Перетаскивая границу окна, при нажатой левой кнопке мыши, можно изменить масштаб диаграммы. С помощью панели инструментов «Диаграммы» можно выбрать другой тип графика, изменить ряды данных, шрифты подписей и прочее. Кнопкой Delete можно удалить диаграмму. Не бойтесь экспериментировать: любое изменение, и даже удаление диаграммы не затрагивает исходные данные, на которых она построена. Диаграмму всегда можно будет построить заново.

3.8. Организация вычислений в сводном отчете

3.8.1. Изменение структуры сводного отчета

С помощью построенной таблицы можно проанализировать продажи в самых различных аспектах: по датам, покупателям, партиям, вначале датам, затем по накладным… Главное — не переусердствовать в выборе вариантов анализа. Здравый смысл должен сдерживать буйство фантазии. Для перестройки таблицы необязательно каждый раз строить ее заново[8] . Группировку данных можно менять, перетаскивая мышью заголовки полей. Например, перестроим сводную таблицу, показанную на рис. 10 так, чтобы покупатели располагались по столбцам, а купленные ими товары — по строкам. Нажмем левую кнопку мыши на поле «Покупатель» (рис. 18и перетащим его в верхнюю часть окна Excel, в область строк. Когда курсор мыши примет вид стрелки с фрагментом ячеек таблицы, отпустим кнопку. Перетаскиваемое поле «приклеится» к полю «Товар». Выделим и перетащим мышью поле «Товар» к левому краю окна Excel, столбцы сводной таблицы поменяются местами со строками. Два взмаха мышью – и сводная таблица транспонирована.

Сводная таблица — это внутренний объект Excel, который занимает определенное (часто довольно большое) место в оперативной памяти и на жестком диске. Мы преобразовываем не весь объект «сводная таблица», а только отчет сводной таблицы , своего рода «верхушку айсберга». Перетаскивая мышью поля, группируя и разгруппировывая строки и столбцы, мы всего лишь изменяем способ представления уже рассчитанных данных. При этом сам объект «сводная таблица» не изменяется и заново не пересчитывается. Если сводная таблица большая, а компьютер недостаточно мощный, перестройка сводной таблицы выполняется долго. В этом случае нужно пользоваться Мастером сводных таблиц. Он позволяет разделить процессы проектирования структуры и построения отчета сводной таблицы. Компьютеру не придется перестраивать отчет «на лету» при каждом перетаскивании поля.

Рис. 18 Изменение макета сводного отчета

Если щелкнуть правой кнопкой мыши в области сводной таблицы и выбрать из выпадающего меню пункт «Мастер…», откроется знакомое окно Мастера сводных таблиц. Причем сразу на третьем шаге (подразумевается, что диапазон исходных данных уже определен). Как вы помните, на третьем шаге Мастер предоставляет диаграмму сводной таблицы, и список всех доступных полей. Диаграмма будет заполнена полями, в соответствии со структурой сводной таблицы.

Перестроим диаграмму сводной таблицы: перетащим поле «Товар» из области столбцов в область строк, поле «Покупатель» в область столбцов (рис. 19 Перед полем «Товар» вставим поле «Дата», тогда строки отчета будут группироваться сначала по датам, а в пределах каждой даты — по товарам. Щелкнем по кнопке «Готово», и отчет сводной таблицы будет перестроен в соответствии с изменившимися планами (рис. 20).

Рис. 19 — Перегруппировка сводного отчета по датам и товарам

Рис. 20 — Результат перегруппировки сводного отчета

3.8.2. Внедрение вычисляемых полей в сводный отчет

В нашей таблице есть информация о сумме продаж и о себестоимости отгруженных по каждой накладной товаров. Проанализируем, какую прибыль принесла каждая конкретная сделка. С каким покупателем работать выгодно, а с каким – нет? Для этого в сводной таблице нужно выполнить дополнительные вычисления. Чтобы это сделать, в сводную таблицу нужно добавить вычисляемые поля или вычисляемые элементы полей[9] . Заметим, что вычислительные возможности сводной таблицы с дополнительными полями ограничены. В формулах для вычисляемых полей нельзя ссылаться на ячейки со сводными (итоговыми) данными, как в обычной таблице Excel. Можно ссылаться только на поля и элементы полей. Еще одно ограничение: способ расчета вычисляемого поля только один — суммирование .

Покажем, как анализировать прибыль от сделок графическим методом. Перестроим сводную таблицу так, чтобы по ней можно было построить наглядный график, отображающий выгодность каждой сделки. Себестоимость товаров в таблице-источнике приведена без НДС, а суммы продаж – с НДС. Чтобы данные о расходах и доходах по сделке были визуально сопоставимы, приведем их к одному масштабу, добавив к себестоимости товаров НДС. Для этого введем в сводную таблицу вычисляемое поле.

Щелкаем правой кнопкой мыши в области сводной таблицы и выбираем из контекстного меню команду «Формулы4Вычисляемое поле»[10] . В открывшемся окне вводим имя нового поля «Сумма с НДС» и формулу для его вычисления =Сумма*1,2. Чтобы избежать опечаток, имена полей удобно выбирать из списка в окне «Поля» и переносить их в формулу с помощью кнопки «Добавить поле» (рис.21).

Перестроим отчет сводной таблицы с помощью Мастера (рис. 22). Поле «Покупатель» перенесем в область страниц, поле «Ключ» — в область строк. В область данных поместим вычисляемое поле «Сумма с НДС» (способ расчета – суммирование) и поле «Сум.прод» (способ расчета — минимальное значение ). Сумма продажи повторяется в каждой строке таблицы-источника.

Во всех строках с одинаковым ключом, т.е. в пределах одной накладной, эта сумма одинакова. Расчет поля «Сум.прод» суммированием не годится, результат будет завышен в несколько раз. Корректное значение выручки от продажи можно получить, рассчитав среднее, минимум или максимум по этому полю. Ведь и среднее, и минимум, и максимум от нескольких одинаковых чисел равны этому числу. Построим на данных сводной таблицы обычную гистограмму (рис. 23). На ней прекрасно видно, что продажа по первой накладной была самой выгодной, по второй накладной товары были проданы с убытком.

Поле «Покупатель» в макете сводной таблицы было вынесено в область страниц. Щелкнув мышью по треугольнику в ячейке B2 (обведено на рисунке) , можно выбрать из выпадающего списка конкретного покупателя и получить по нему таблицу и диаграмму. На рис. 24 показаны таблица и диаграмма, построенные по всем покупателям. Для ее построения из выпадающего списка был выбран пункт «(Все)».

Рис. 21 — Добавление вычисляемого поля в сводную таблицу

Рис. 22 — Макет сводного отчета с вычисляемым полем

Рис. 23 — Гистограмма продаж по отчету с вычисляемыми полями

Оценка дохода «на глазок» (графическим методом) не всегда приемлема. Ранее было сказано, что для вычисляемого поля доступен только один способ расчета — суммирование. Поле «Сум.прод» содержит лишние данные, и способ расчета суммированием для него не подходит. Если убрать повторяющиеся цифры из этого поля в таблице-источнике, так чтобы можно было использовать способ его расчета суммированием, в формулах сводной таблицы можно будет ссылаться на это поле без ограничений.

Перейдем к листу с таблицей-источником и введем в столбец L новый заголовок «Сум.прод2». Наша таблица-источник упорядочена по полю «Ключ» (если нет —отсортируйте ее по этому полю). Для исключения повторяющихся значений суммы продажи в пределах накладной, запишите в ячейку L2 формулу: =ЕСЛИ(И(A1=A2;I1=I2);0;I2).

Эта формула означает: ЕСЛИ ключи предыдущей (A1) и текущей (A2) строк совпадают И совпадают значения столбцов «Сум.прод» текущей и предыдущей строк (I1=I2), в текущую ячейку записываем ноль. Иначе – копируем в ячейку значение из столбца «Сум.прод» текущей строки. Размножив эту формулу во все остальные строки столбца «Сум.прод2», получим новое поле. В нем для каждого значения ключа есть только одно значение суммы продажи (рис. 24).

Построим на основании измененной таблицы-источника сводную таблицу. В область страниц перетащим поле «Покупатель», в область строк – поля «Дата» и «Документ». В область данных поместим поле «Сум.прод2», способ расчета – суммирование.

Добавим в таблицу два вычисляемых поля: «Наценка грн.» и «Наценка %». В поле «Наценка грн.» рассчитаем наценку в гривнях по формуле =Сум.прод2/1,2–Сумма (то есть сумма продаж без НДС минус себестоимость проданных товаров). В поле «Наценка %» используем формулу =ЕСЛИ(Сумма< >0;'Наценка грн'/Сумма;0). Если значение поля «Сумма» не равно нулю (на ноль делить нельзя!), вычислим отношение полей «Наценка грн.» и «Сумма», иначе запишем ноль.

Рис. 24 — Результат генерации ключа для определения наценки

В таблице на рис. 25 назначим полю «Сум.прод2» денежный формат, полю «Наценка %» — процентный. Поле «Наценка грн.» удалим, чтобы таблица лучше читалась. Щелкнув правой кнопкой мыши в любой ячейке столбца «Данные» с текстом «Сумма по полю Наценка грн.» выделим все ячейки, относящиеся к этому полю, и выполним команду «Удалить» из выпадающего меню. Значения полей «Наценка %», формула для которых ссылается на удаляемое поле «Наценка грн.», при этом не изменятся. Мы удалили поле из отчета сводной таблицы, а не из самого объекта «сводная таблица». Формулы в других вычисляемых полях могут ссылаться и на невидимое поле. Чтобы удалить вычисляемое поле из объекта «сводная таблица», нужно пользоваться кнопкой «Удалить» окна вставки вычисляемого поля (см. рис. 21).

Рис. 25 — Сводная таблица для определения наценки

Список литературы

1. Горнаев А. ЕХСЕL, VBA, ІNTERNET в зкономике и финансах.— СПб, 2001, — 816 с.

2. Воробьев В.В. Microsoft Excel 2000: Пособие для начинающих.— К., 2000, — 36 с.

3. Майер П. Microsoft Office 97. Торгово-издательское бюро. BHV, — Киев, 1998, — 467 с.

4. Карпенко М.Ю. Методичні вказівки до виконання економічних розрахунків засобами Мisrosoft Excel з курсу "Прикладні задачі менеджменту на ПК" ( розділ "Фінансові функції та оптимізація"), — Харків: ХДАМГ, 2000, — 16 с.

5. Использование Microsoft Office (Word; Excel; MS Access). — К., 1996, — 478 с.

6. Борковский А.В. Англо-русский словарь по программированию и информатики (с толкованиями), — М.: Рус.яз., 1989.—335 с.

Содержание

1. Цель работы....................................................................................................... 3

2. Содержание работы.................................................................................... 4

3. Методика выполнения работы........................................................ 4

3.1. Подготовка исходных данных............................................................. 4

3.2. Преобразование исходных данных.................................................... 6

3.3. Синтез ключа............................................................................................... 11

3.4. Объединение таблиц по общему ключу.......................................... 14

3.5. Другие функции просмотра.................................................................. 18

3.6. Анализ структуры продаж по сводным отчетам....................... 19

3.7. Использование деловой графики для анализа структуры продаж 21

3.8. Организация вычислений в сводном отчете................................ 27

3.8.1. Изменение структуры сводного отчета....................................... 27

3.8.2. Внедрение вычисляемых полей в сводный отчет........................ 29

Список литературы...................................................................................... 35

Учебное издание

Составители: Николай Юрьевич КАРПЕНКО,

Наталия Ивановна ГОРДИЕНКО,

Игорь Николаевич РЯБЧЕНКО.

Редактор: Н.З. Алябьев

Корректор: З.И. Зайцева

План 2005, поз. 156

Подп. в печать ______ Формат 60х84 1/16 Бумага офисная

Печать на ризографе. Усл.-печ. л. 1,5 Уч.-изд. л. 36

Зак. № _____ Тираж 50 экз.

Сектор оперативной полиграфии ИВЦ ХНАГХ, 61002, Харьков, ХНАГХ, ул. Революции, 12

[1] Такая информация есть в базе документов, а не проводок. Для ее обработки нужно использовать специальный отчет по документам.

[2] Справедливо, если в свойствах документа «Расходная накладная» установлен параметр автоматической нумерации, обычно такой параметр установлен.

[3] Приближенное соответствие – ближайший меньший элемент из первого столбца массива к искомому значению. Например, если в столбце поиска - цифры 1, 2 и 3, а искомое значение 2.999, то будет выбрано 2, а не ближайшее значение 3. Если искомое значение равно 0, т.е. меньше наименьшего значения столбца, функция выдаст сообщение #Н/Д .

[4] Если в окне Мастера функций сказано наоборот, не обращайте внимания.

[5] Массив – диапазон ячеек прямоугольной формы. Синтаксическая форма «массив» функции ПРОСМОТР оставлена для совместимости, пользоваться ею не рекомендуется. Функцию ПРОСМОТР в этой форме заменяют функции ВПР и ГПР.

[6] Исходная база данных при этом не должна содержать пустых строк!

[7] В последних версиях Excel диаграмма по сводным таблицам строится автоматически на новом листе. Чтобы построить диаграмму на текущем листе нужно преобразовать сводную таблицу в обычный блок ячеек и указать его в качестве источника данных.

[8] Если запустить Мастер сводных таблиц на уже использованном наборе исходных данных, Excel сообщит, что новая таблица и существующая используют один источник данных и предложит построить новую таблицу на основе существующей. Нет смысла отказываться от такого предложения: это приведет к дублированию данных в памяти компьютера.

[9] Поле сводной таблицы – это категория данных, полученная из соответствующего поля таблицы-источника. Элементы поля – это набор значений, которые поле содержит. В нашем примере столбец «Товары» — поле, а «Косметический набор», «Крем для лица» и «Сумка женская» — элементы этого поля.

[10] В MS Excel 2002 и выше добавление вычисляемых полей выполняется через панель инструментов сводных таблиц . Для этого нужно установить активную ячейку в область таблицы, нажать на панели инструментов кнопку «Сводная таблица» и в раскрывшемся меню выбрать пункт Формулы4Вычисляемое поле.