ЛабРабота к модулю 2. Лабораторная работа Базы данных
 Скачать 324 Kb.
|
|
Лабораторная работа № 6. Базы данных – 3 Цель работы: осуществление анализа данных с помощью консолидации данных. Консолидация данных. С помощью консолидации данных можно объединить данные из одной или более областей-источников и выводить их в таблице в области назначения. Область-источник – это интервалы, содержащие данные, которые нужно консолидировать. Эти области-источники могут находится на одном рабочем листе, на нескольких рабочих листах или в разных рабочих книгах. Область назначения – это интервал, который будет содержать консолидированные данные. Она может находится на том же рабочем листе, что и данные источников или на другом рабочем листе, или в другой рабочей книге. В качестве области-источника будем использовать рабочую книгу Фирма, составленную ранее.  Рис. 6.1 Составим аналогичные таблицы еще для двух городов Харькова и Одессы. Для этого следует отредактировать соответствующим образом содержимое ячейки A1. Таблицы представлены на рис. 6.2, 6.3 соответственно.  Рис. 6.2  Рис. 6.3 Перейдем в четвертый рабочий лист и поместим указатель ячейки на крайнюю левую ячейку таблицы, которая будет содержать консолидированные данные – ячейку A3. Поскольку номер и название товара остаются неизменными и не должны быть подвергнуты операции сложения, то можно скопировать эту часть таблицы из рабочего листа, например, Киев. Для этого выделим в рабочем листе Киев диапазон ячеек A3 – B14, выберем команду Копировать меню Правка и перейдем в четвертый рабочий лист (присвоим ему имя Итог), поместим указатель ячейки на ячейку A3 и выберем команду Вставка меню Правка. Скопированный фрагмент будет вставлен в новом месте. Для того, чтобы приступить к операции консолидации данных поместим указатель ячейки на ячейку C3 и выберем в меню Данные команду Консолидация. На экране появится диалоговое окно, представленное на рис. 6.4.  Рис 6.4 В списке Функция следует выбрать операцию, которая будет выполняться над консолидированными данными. Поскольку предлагаемая программой операция сложения (элемент Сумма) подходит для нашей цели, перейдем к полю ввода Ссылка. Именно в нем следует указать диапазоны ячеек, данные их которых должны быть подвергнуты процессу консолидации. Диапазон ячеек проще всего можно указать, используя мышь. Поместим курсор ввода в поле Ссылка и выполним щелчок мышью на ярлычке листа Киев, затем выделим диапазон ячеек D3:F14 и нажмем в диалоговом окне Консолидация кнопку добавить. Указанный диапазон ячеек тотчас же будет представлен в поле Список диапазонов. Нажатием кнопки добавить можно включить в область консолидации, нажатием кнопки Удалить ненужный диапазон может быть из этой области удален (перед нажатием кнопки ненужный диапазон следует выделить в поле Список диапазонов). После включения в область консолидации первого диапазона ячеек можно приступить к выполнению аналогичной операции над другими диапазонами. С помощью щелчка мышью на ярлычке перейдем в рабочий лист с данными для следующего филиала. Excel автоматически выделит тот же диапазон ячеек, что и в предыдущем листе. Нажмем кнопку добавить и аналогичным образом укажем в поле Список диапазонов диапазоны других рабочих листов файла, данные из которых должны быть консолидированы.  Рис. 6.5 Если верхняя строка и/или левый столбец содержат заголовки столбцов или строк, которые необходимо скопировать в итоговую таблицу, следует включить соответствующие опции в группе Использовать метки. Поскольку в нашем примере верхняя строка содержит заголовки столбцов, то следует активизировать опцию В верхней строке. Если между данными консолидированной таблицы и исходными данными должна быть установлена динамическая связь, то следует активизировать опцию Создать связи с исходными данными. Вследствие активизации этой опции при изменении данных в исходном диапазоне будут изменяться значения и в итоговой таблице. Кнопка Обзор используется для выбора файла, который содержит консолидируемые данные. После включения всех необходимых диапазонов, а также задания желаемых параметров консолидации нажимается кнопка ОК для начала выполнения операции. Результат выполнения операции представлен на рис. 6.5. В ячейку A1 введем название таблицы Итоговые данные. Для более наглядного представления данных в столбце F приведем значения долей отдельных товаров в общем объеме продаж. Для этого зададим в ячейке F9 формулу: = E9/$E$48 и скопируем ее в остальные строки столбца F (вплоть до ячейки F48) предварительно сформатированного процентным стилем.  Рис. 6.7 Необычные адреса ячеек консолидированной таблицы объясняются тем, что при консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа. Структурирование (создание структуры) документа позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы представлены символы структуры. Цифрами обозначены уровни структуры (в нашем примере – 1 и 2). Нажатие кнопки со знаком плюс позволит расшифровать данные высшего уровня структуры. Этого же можно добиться путем исполнения команды Структура/Показать детали меню Данные. На рис. 6.6 показана расшифровка структуры для строк 7, 11 и 15. Скрыть детали можно посредством щелчка на кнопке с изображением минуса или путем выбора команды Структура/Скрыть детали меню Данные. Применение функции консолидации имеет смысл при подведении итогов по нескольким структурным подразделениям, данные о которых хранятся в отдельных файлах. |