Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1с-бухгалтерия» для дисциплин «Организация и методика аудита», «1с-бухгалтерия» (для (стр. 3 из 6)
Синтез ключа – простой, но эффективный прием. Его применение нередко помогает решить проблемы, связанные с поиском, сортировкой, подведением промежуточных итогов или построением сводной таблицы, когда в исходной базе 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 таблицы товаров и вызовем Мастера щелчком по кнопке fx. В раскрывшемся списке находим категорию «Ссылки и массивы», выбираем функцию ВПР.
На втором шаге Мастера функций (рис. 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 в буфер обмена и вставим на рабочий лист как значения при помощи режима специальной вставки.
У функции ВПР есть «двойник» - функция ГПР ( «горизонтальный просмотр»). Разница между ними в том, что функция ВПР работает со столбцами, а ГПР – со строками. То есть функция ГПР позволяет получить значение из строки с указанным номером, отыскав соответствие искомого значения и ключа в верхней строке диапазона. И синтаксис, и особенности их применения обеих функций одинаковы. Еще одна функция просмотра так и называется: ПРОСМОТР. Она имеет две синтаксические формы: вектор и массив[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.