сервер. Учебнометодическое пособие по выполнению пратических работ тольятти 2018 2 содержание
 Скачать 7.66 Mb.
|
|
Задание 4. Создание сводной таблицы. Выполним анализ данных и консолидацию на основе сводных таблиц Создаем таблицу на новом листе (рис. 11.8): Рисунок 11.8 Выделяем область таблицы и выбираем пункты меню Вставка-Сводная таблица (рис. 11.9). Рисунок 11.9 80 В окне мастера сводной таблицы выбираем На новый лист и нажимаем на ОК. В окне макета сводной таблицы перетаскиваем поля Компания, Год, Выручка соответственно в области Названия столбцов, Название строк и Значения (рис. 1.10). Рисунок 11.10 Получаем сводную таблицу вида (рис. 1.11). Рисунок 11.11 Задание для самостоятельной работы. Для данной таблицы создайте сводную диаграмму. Составить отчет по проделанной работе. 81 Практическая работа № 12. Создание отчетов сводной таблицы Задание 1. Создание отчета сводной таблицы, связанного с реляционной базой данных Создайте сводную таблицу и диаграмму для анализа деятельности предприятия. В качестве внешнего источника данных используйте запрос из реляционной базы данных Access Поставка товаров. Схема базы данных приведена на рис. 1. Схема имеет вид снежинки. При использовании схемы «снежинка» многомерный куб представляется в хранилище таблицей фактов, с которой связаны все таблицы измерений. Измерения в таблице фактов представлены кодами. Данные для каждого измерения берутся из таблицы-справочника, в котором коду соответствует значение измерения. Схема снежинка обеспечивает возможность работы с иерархическими уровнями, определяющими степень детализации данных. На схеме таблица ОТГРУЗКА содержит факты о количестве отгруженного товара по накладной (КОЛ_ОТГР) и о стоимости этого товара (СУММА_ОТГР), остальные таблицы – значения измерений. Создайте многотабличный запрос на выборку полей, необходимых для выполнения анализа (рис. 2). Для образования уровней иерархии по дате дополните запрос вычисляемыми полями Месяц и Год (рис. 3). Рис. 1. Схема базы данных Поставка товаров 82 Рис. 2. Запрос к базе данных Поставка товаров в режиме конструктора В запросе, показанном на рис. 5, использованы все таблицы подсхемы и выбраны поля, которые представляют интерес при анализе выполнения договорных обязательств предприятия. Дополните запрос вычисляемыми полями, как показано на рис. 6. Рис. 3. Вычисляемые поля для определения месяца и года по указанной дате Сохраните запрос под именем Массив для анализа. Результатом выполнения запроса будет многомерный массив, представленный в виде ненормализованной таблицы с многочисленными повторяющимися значениями в полях. Откройте Excel. Создайте отчет сводной таблицы. 1. Определите для открытой пустой книги Excel в качестве внешнего источника данных для отчета сводной таблицы базу данных Access Поставка товаров. Для этого на ленте Данные в группе Получить внешние данные выберите тип источника данных из Access и в открывшемся окне Выбор источника данных конкретное местоположение базы данных Поставка товаров. Выполните команду Открыть. Откроется окно Выделить таблицу, где представлены все таблицы и запросы базы данных. Выделите запрос Массив для анализа и нажмите ОК. Выберите способ представления данных в книге Отчет сводной таблицы и адрес размещения сводной таблицы. Нажмите ОК, чтобы начать импорт данных источника. Примечание. Если окно Выделить таблицу не открывается, вероятно открыта подключаемая база данных. Закройте ее. 2. На ленте Работа со сводными таблицами|Параметры начните создание отчета сводной таблицы. Для этого переместите поля исходной таблицы из Списка полей сводной таблицы (рис. 4) в области сводной таблицы, как показано на рис. 5. Измените имена областей сводной таблицы: фильтра, Σ значений, строк и столбцов. 83 Рис. 4. Заполнение областей сводной таблицы полями из запроса базы данных Рис. 5. Отчет сводной таблицы, связанный с запросом 3. Создайте сводную диаграмму на основе данных сводной таблицы, для этого выполните команду Сводная диаграмма на ленте Работа со сводными таблицами|Параметры или воспользуйтесь командами ленты Вставка. Воспользовавшись командой Переместить диаграмму на ленте Работа со сводными диаграммами|Конструктор разместите диаграмму на отдельном листе. Сводная диаграмма, соответствующая выбранным в сводной таблице значениям в фильтрах Год и Месяц. 84 Рис. 6. Сводная диаграмма, соответствующая сводной таблице на рис. 8 4. Разверните и сверните отчет сводной таблицы и диаграммы, используя кнопки Развернуть/свернуть для столбца Наименование покупателя или команды контекстного меню данного элемента. 5. На новом листе повторно создайте отчет сводной таблицы, выбрав тот же источник данных (или скопируйте ранее созданный отчет). Перестройте отчет в соответствии с рис. 7. Для сравнения данных по годам и месяцам переместите их в область строк или столбцов (рис. 7). Наименование товара поместите в фильтр. В область значений – поле СУММА_ОТГР. Рис. 7. Сводная таблица для сравнительного анализа данных по годам и месяцам Задание 2. Создать отчет сводной таблицы и диаграммы для оценки долга предприятия по договорам Выполните анализ стоимости долга предприятия (недопоставок) в стоимостном выражении в разрезе покупателей, договоров по всем или некоторым месяцам года с вычислением итогов. В качестве источника данных используйте запрос базы данных Поставка товаров. Для получения сведений о долгах предприятия необходимо подсчитать: 1) суммарную стоимость товаров, указанных в договорах, нарастающим итогом; 2) суммарную стоимость товаров, отгруженных в соответствии с договорами, нарастающим итогом; 85 3) получить разность между первой и второй суммами. На рис. 8, 9, 10 представлены запросы, необходимые для решения сформулированных задач. Результатом выполнения запросов будет многомерный массив с именем Недоплата, представленный в виде ненормализованной таблицы. Рис. 8. Запрос Стоимость договоров к базе данных Поставка товаров 86 Рис. 9. Запрос Стоимость отгрузки по договорам к базе данных Поставка товаров Рис. 10. Запрос вычисления долга по договорам в стоимостном выражении На рис. 10, 11 представлен запрос для подсчета помесячного долга предприятия покупателям в стоимостном выражении нарастающим итогом. 87 Рис. 11. Вычисляемые поля запроса вычисления долга по договорам На рис. 12, 13 показан один из вариантов отчета сводной таблицы и сводной диаграммы, которые построены на основе запроса Недоплата базы данных Поставка товаров. Рис. 12. Отчет сводной таблицы с данными о долгах фирмы по договорам с покупателями Рис. 13. Сводная диаграмма, соответствующая сводной таблице на рис. 15 На основе последнего запроса (рис. 13) предложите другие варианты анализа данных и создайте отчеты сводной таблицы и соответствующие им диаграммы. Выполните изменения в базе данных и, обновив отчет сводной таблицы, убедитесь, что работаете с новыми данными. Сохраните обновленный массив данных для последующей работы в Excel. 88 Практическая работа №13. Способы сравнения таблиц в Excel Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы: сравнение списков, находящихся на одном листе; сравнение таблиц, расположенных на разных листах; сравнение табличных диапазонов в разных файлах. Именно исходя из этой классификации, прежде всего, подбираются методы сравнения, а также определяются конкретные действия и алгоритмы для выполнения задачи. Например, при проведении сравнения в разных книгах требуется одновременно открыть два файла Excel. Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру. Способ 1: простая формула Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе. Итак, имеем две простые таблицы со списками работников предприятия и их окладами. Нужно сравнить списки сотрудников и выявить несоответствия между столбцами, в которых размещены фамилии. 89 1. Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа: =A2=D2 Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой. 2. Щелкаем по клавише Enter, чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА», что означает совпадение данных. 90 3. Теперь нам нужно провести аналогичную операцию и с остальными ячейками обеих таблиц в тех колонках, которые мы сравниваем. Но можно просто провести копирование формулы, что позволит существенно сэкономить время. Особенно данный фактор важен при сравнивании списков с большим количеством строк. Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА». При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах. 91 4. Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат «ЛОЖЬ». По всем остальным строчкам, как видим, формула сравнения выдала показатель «ИСТИНА». 92 5. Кроме того, существует возможность с помощью специальной формулы подсчитать количество несовпадений. Для этого выделяем тот элемент листа, куда оно будет выводиться. Затем щелкаем по значку «Вставить функцию». 93 6. В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ. Щелкаем по кнопке «OK». 94 7. Активируется окно аргументов функции СУММПРОИЗВ, главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой: =СУММПРОИЗВ(массив1;массив2;…) Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент. Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<>) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-». В нашем случае получилось такое выражение: --(A2:A7<>D2:D7) Щелкаем по кнопке «OK». 8. Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1», то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0». 95 Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Но в этом случае желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при внесении формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид: =B2=Лист2!B2 96 То есть, как видим, перед координатами данных, которые расположены на других листах, отличных от того, где выводится результат сравнения, указывается номер листа и восклицательный знак. Способ 2: выделение групп ячеек Сравнение можно произвести при помощи инструмента выделения групп ячеек. С его помощью также можно сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе. 1. Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…». 97 2. После этого запускается окно выделения групп ячеек. Устанавливаем переключатель в позицию «Выделить по строкам». Жмем по кнопке «OK». 3. Как видим, после этого несовпадающие значения строк будут подсвечены отличающимся оттенком. Кроме того, как можно судить из содержимого строки формул, программа сделает активной одну из ячеек, находящуюся в указанных не совпавших строках. 98 Способ 3: условное форматирование Произвести сравнение можно, применив метод условного форматирования. Как и в предыдущем способе, сравниваемые области должны находиться на одном рабочем листе Excel и быть синхронизированными между собой. 1. Прежде всего, выбираем, какую табличную область будем считать основной, а в какой искать отличия. Последнее давайте будем делать во второй таблице. Поэтому выделяем список работников, находящийся в ней. Переместившись на вкладку «Главная», щелкаем по кнопке «Условное форматирование», которая имеет месторасположение на ленте в блоке «Стили». Из выпадающего списка переходим по пункту «Управление правилами». 99 2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило». 3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки»записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид: =$A2<>$D2 100 Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке «Формат…». 4. Активируется окно «Формат ячеек». Идем во вкладку «Заливка». Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку «OK». 5. Вернувшись в окно создания правила форматирования, жмем на кнопку «OK». 101 6. После автоматического перемещения в окно «Диспетчера правил» щелкаем по кнопке «OK» и в нем. 7. Теперь во второй таблице элементы, которые имеют данные, несовпадающие с соответствующими значениями первой табличной области, будут выделены выбранным цветом. 102 Существует ещё один способ применения условного форматирования для выполнения поставленной задачи. Как и предыдущие варианты, он требует расположения обоих сравниваемых областей на одном листе, но в отличие от ранее описанных способов, условие синхронизации или сортировки данных не будет являться обязательным, что выгодно отличает данный вариант от ранее описанных. 1. Производим выделение областей, которые нужно сравнить. 103 2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения». 104 3. Запускается окно настройки выделения повторяющихся значений. Если вы все сделали правильно, то в данном окне остается только нажать на кнопку «OK». Хотя при желании в соответствующем поле данного окошка можно выбрать другой цвет выделения. 4. После того, как мы произведем указанное действие, все повторяющиеся элементы будут выделены выбранным цветом. Те элементы, которые не совпадают, останутся окрашенными в свой изначальный цвет (по умолчанию белый). Таким образом, можно сразу визуально увидеть, в чем отличие между массивами. 105 При желании можно, наоборот, окрасить несовпадающие элементы, а те показатели, которые совпадают, оставить с заливкой прежним цветом. При этом алгоритм действий практически тот же, но в окне настройки выделения повторяющихся значений в первом поле вместо параметра «Повторяющиеся» следует выбрать параметр «Уникальные». После этого нажать на кнопку «OK». Таким образом, будут выделены именно те показатели, которые не совпадают. 106 Способ 4: комплексная формула Также сравнить данные можно при помощи сложной формулы, основой которой является функция СЧЁТЕСЛИ. С помощью данного инструмента можно произвести подсчет того, сколько каждый элемент из выбранного столбца второй таблицы повторяется в первой. Оператор |