Лабораторный практикум по информатике. Практикум по дисциплине Информатика Уфа 2012 3 Лабораторный практикум по дисциплине Информатика Уфимск гос авиац техн унт Сост. В. В. Мартынов, Е. Н. Прошин, Ю. В
 Скачать 8.39 Mb.
|
|
Консолидация поле Ссылка предназначено для ввода одного за другим диапазонов консолидации, которые кнопкой <Добавить> переносятся в поле Список Диапазонов. Назначение кнопки <Обзор>аналогичноее применению в других программах. Если от предыдущей консолидации в списке остались диапазоны, которые не соответствуют текущей задаче, их можно удалить. Флажки подписи верхней строки и значения левого столбца предназначены для вывода в результирующей таблице заголовков столбцов и строк исходных диапазонов. Консолидация позволяет вести расчет итоговых функций по данным только одной таблицы, если в ней значения левого столбца, т.е. заголовки строк повторяются, и активен флажок значения левого столбца. 198 Рис. 1 Пример окна «Консолидация» Если флажок Создавать связи с исходными данными не включен, итоговые результаты консолидации выводятся в виде обычных числовых значений. В противном случае, они представляют собой сгруппированные списки, где кнопка «+» позволяет развернуть консолидируемые элементы (рис. 4), просмотреть через строку формул адреса исходных данных, а также тип примененной итоговой функции. Рис. 2 Пример консолидации данных по расположению 199 Рис. 3 Пример консолидации данных по категориям Рис. 4 Консолидация с сохранением связей с исходными данными 3.2. Сводные таблицы и сводные диаграммы Для всестороннего и эффективного анализа данных больших таблиц в Excel используются т.н. сводные таблицы (СТ). Главные достоинства СТ - это представление больших объемов информации в концентрированном и удобном для анализа виде, широкие возможности для группировки данных, а так же возможность получения промежуточных и общих итогов, которые помещаются в таблицу автоматически. 200 3.2.1. Создание сводных таблиц Для построения и модификации СТ используется инструмент «Сводные таблицы», вызываемый с помощью пункта «Сводная таблица» на панели «Вставка». После этого появится диалоговое окно (см. рис.5), в котором нужно выбрать: Источник данных Место для размещения сводной таблицы. Источником данных может быть таблица или диапазон существующей книги EXCEL либо внешний источник данных. В первом случае полное имя интервала задается в виде: [имя_ книги]имя_листа!интервал ; Если СТ строится в той же книге, где находится исходная таблица, то имя книги указывать не обязательно. В зависимости от выбранного вида источника изменяются последующие этапы работы по созданию СТ. Ниже указывается, куда размещать сводную таблицу: на существующий лист (в этом случае нужно выбрать диапазон или первую ячейку диапазона) либо на новый лист. Рис. 5 Диалоговой окно при создании сводной таблицы На следующем шаге создается структура СТ и определяются ее функции. Список полей представлен на рис. 6 и состоит из областей:названия строк, названия столбцов, фильтр отчета и значения. Слева отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы. Размещение поля в определенную область, как показано на рис.1, выполняется путем его 201 “перетаскивания” при нажатой левой кнопки мыши. Чтобы удалить поле из области, его перетаскивают за пределы. Удаление поля приведет к скрытию в СТ всех зависимых от него величин, но не повлияет на исходные данные. Каждая область, куда размещаются поля исходной таблицы, имеет свое назначение, определяющее внешний вид СТ и ее функции. Названия строк - поля этой области формируют заголовки строк СТ; если таких полей несколько, то они размещаются в макете сверху вниз, обеспечивая группирование данных СТ по иерархии полей, где для каждого элемента внешнего поля, элементы внутреннего поля повторяются. Названия столбцов- поля в этой области формируют заголовки столбцов СТ; если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных СТ по иерархии полей. Фильтр отчета - поля в этой области выступают в качестве фильтров и позволяют просматривать независимо друг от друга данные СТ, соответствующие разным значениям поля, помещенного в эту область; в области страница может быть размещено несколько полей, между которыми устанавливается иерархическая связь - сверху вниз. 202 Значения - обязательно определяемая область для размещения полей, по которым подводятся итоги, согласно выбранной итоговой функции; размещаемые здесь поля могут быть произвольных типов. В сводных таблицах можно вычислить и представить различные итоговые функции: Сумма, Количество, Максимум, Среднее и другие. Кроме того, для каждого поля этой области можно задать одно из 9 дополнительных вычислений, которые задают способ представления чисел (например, вместо абсолютных значений вывод процентной величины этих значений по отношению к некоторому итогу). По умолчанию в качестве итоговой функции используется сумма, но можно использовать и другую функцию (количество, максимум, среднее). Для этого нужно в области Значения, щелкнуть по перевернутому треугольнику одного из выбранного поля и в контекстном меню выбрать «Параметры полей значений» (см. рис. 7) Рис. 6 Список полей сводной таблицы 203 Рис. 7 Выбор параметров полей значений сводной таблицы Далее выбрать соответствующую итоговую функцию (см. рис 8). Рис. 8 Настройка параметров полей значений сводной таблицы: Выбор операции 204 Результат настройки списка полей представлен на рис.9. Рис. 9 Пример простой сводной таблицы Можно использовать несколько названий строк, например: порода и площадь учёта. В этом случае сведения по каждой породе будут подразделяться на варианты площадей учета (рис. 10). 205 Рис. 10 Пример сводной таблицы с 2 полями в названии строк 3.2.2. Фильтрация сводной таблицы Основной способ фильтрации – фильтрация по названию строк. Для этого нужно выделить ячейку с названием строки, после чего щелкнуть по перевернутому треугольнику в поле «Название строк». Причём список возможных значений зависит от того, ячейка со значением из какого поля выделена, например: в рассматриваемом примере: если выделить ячейку с надписью «дуб», то при фильтрации появятся все варианты пород деревьев: дуб, ель, клен, липа; если же выделить ячейку с площадью учета, то появится список со всеми вариантами площадей учета (см. рис. 11). 206 Рис. 11 Фильтрация по названию строк (Площадь учета) Другой способ фильтрации – использовать фильтр отчёта. Фильтр отчета располагается над сводной таблицей. В рассматриваемом примере, это фильтр по полю «Возраст». При его использовании появится список всех вариантов возрастов (см. рис 12). 207 Рис. 12 Пример использования фильтра отчёта по полю «Возраст» 3.2.3. Редактирование сводной таблицы Редактировать СТ можно, как в режиме просмотра, так и вернувшись, в режим создания таблицы. Изменению в таблице могут подвергаться структура (добавление новых полей, удаление существующих, изменение местонахождения поля), тип используемой функции и дополнительные вычисления. Закрыв список полей сводной таблицы, можно снова открыть его. Для этого нужно щелкнуть сводную таблицу правой кнопкой мыши и выбрать команду «Показать список полей». Кроме того, можно нажать кнопку Список полей на ленте (Работа со сводными таблицами, вкладка Параметры, группа Показать для сводной таблицы). 3.2.4. Сводные диаграммы Сводная диаграмма – инструмент, позволяющий графически отображать значения сводной таблицы. Чтобы построить сводную диаграмму на основе существующей сводной таблицы, достаточно щелкнуть по сводной таблице и на панели «Работ со сводными таблицами» выбрать пункт «Сводная диаграмма». Получится диаграмма, как на рис. 13. 208 Рис. 13 Пример сводной диаграммы на основе сводной таблицы Список полей сводной диаграммы в-целом соотвествует списку полей сводной таблицы. Разница в том, что названия столбцов именуются полями легенды, а названия строк – полями осей (см. рис 14). 209 Рис. 14 Список полей сводной диаграммы (вместо названия столбцов и названия строк поля легенды и поля осей) При использовании инструмента «Сводная диаграмма» на панели «Вставка» можно сразу построить и сводную диаграмму, и сводную таблицу. 4. Описание оборудования и используемых программных комплексов При выполнении лабораторной работы необходим специализированный компьютерный класс с минимальными системными требованиями компьютеров: – Процессор – Intel Pentium III; – ОЗУ – 256 Mb; – видеокарта – 32 Mb. Требуемое программное обеспечение: – Операционная система Microsoft Windows, пакет прикладных программ Microsoft Office 2010. 210 5. Краткое руководство по эксплуатации оборудования При использовании оборудования необходимо: – соблюдать общие правила нахождения в учебных лабораториях, работы с компьютером и использования программных средств; – привести в порядок одежду; – осмотреть рабочее место, убрать все мешающие работе предметы; – визуально проверить правильность подключения ПЭВМ к электросети. 6. Задания Задание 1. Консолидации данных 1.Фирма «Рога и копыта» закупила для своих подразделений мониторы и принтеры. Общие результаты покупки представлены в двух таблицах. Наименование товара Тип Модель Цена Кол- во Общая стоимость Монитор 17'' Viewsonic E70 225 25 Монитор 17'' Viewsonic E71 244 20 Монитор 19'' Viewsonic E95 361 10 Монитор 19'' Samsung 900I FT 421 10 Наименование товара Тип Модель Цена Кол-во Общая стоимость Принтер Лазерный Epson EPL- 5800L 302 8 Принтер Лазерный Epson EPL- N1600 869 3 Принтер Лазерный HPLJ-1200 367 5 Принтер Лазерный HPLJ-1220 480 4 Принтер Лазерный Epson Stilus C20SX 68 20 Принтер Лазерный Epson Stilus C40UX 76 12 Принтер Лазерный HP DJ-930S 132 10 Принтер Лазерный HP DJ-959S 144 8 211 Перенести данные каждой таблицы на отдельный лист (значения в графе Общая стоимость определить по соответствующей формуле). Вариант 1. На следующем листе этой же рабочей книги составить отчет о закупке техники, в котором должно быть указано общее количество и общая стоимость всех мониторов и общее количество и общая стоимость всех принтеров. Вариант 2. На следующем листе этой же рабочей книги составить отчет о закупке техники, в котором должна быть указана средняя цена мониторов и средняя цена принтеров. Вариант 3. На следующем листе этой же рабочей книги составить отчет о продаваемой технике, в котором должно быть указано количество моделей мониторов и количество моделей принтеров каждого типа (некоторые столбцы на обоих листах можете удалить либо переместить, значения можете написать более развернуто, например в поле «Тип»: «Монитор 17''»). Вариант 4. На следующем листе этой же рабочей книги составить отчет о закупке техники, в котором должна быть указано максимальная цена мониторов и принтеров каждого типа (некоторые столбцы на обоих листах можете удалить либо переместить, значения можете написать более развернуто, например в поле «Тип»: «Монитор 17''»). Вариант 5. В столбец «тип» на обоих листах добавьте наименование товара (например, монитор 17'', монитор 19'', лазерный принтер). На следующем листе этой же рабочей книги составить отчет о закупке техники, в котором должна быть указаны общая стоимость и общее количество всех мониторов каждого типа и общая стоимость и общее количество всех принтеров каждого типа (Подсказка: при 212 выделении диапазонов не выделяйте левый столбец с наименованием товара). 2. В двух таблицах представлены сведения о ряде стран. Полушарие Земли Часть света Страна Площадь, тыс.кв.км Население, тыс.чел Плотность населения, чел/ кв.км Западное Африка Гвинея 246 5290 Западное Африка Либерия 111 22200 Западное Африка Сенегал 196 6600 Западное Юж.Америка Бразилия 8512 135560 Западное Юж.Америка Перу 12285 19700 Западное Юж.Америка Чили 757 12470 Западное Юж.Америка Уругвай 76 2947 Полушарие Земли Часть света Страна Площадь, тыс.кв.км Население, тыс.чел Плотность населения, чел/ кв.км Восточное Европа Дания 44,5 5111 Восточное Европа Швеция 450 8359 Восточное Азия Вьетнам 331,7 60863 Восточное Азия Монголия 1566,5 1866 Восточное Азия Япония 372 120030 Перенести данные каждой таблицы на отдельный лист (значения в графе Плотность населения определить по соответствующей формуле). Вариант 6. На следующем листе этой же рабочей книги получите сводные сведения по данным обеих таблиц: общую площадь и общее число жителей в перечисленных странах для каждого полушария Земли. Вариант 7. На следующем листе этой же рабочей книги получите сводные сведения по данным обеих таблиц: минимальной площади страны и минимальном числе жителей страны для каждого полушария Земли. 213 Вариант 8. На следующем листе этой же рабочей книги получите сведения по числу стран в каждой части света (некоторые столбцы на обоих листах можете удалить либо переместить). 3. В таблицах представлены сведения о двух группах геометрических фигур: прямоугольных треугольниках и прямоугольниках. Номер фигуры Вид фигуры Катет 1 Катет 2 Площадь 1 Треугольник 12 12 2 Треугольник 3 3 3 Треугольник 10 10 4 Треугольник 2,5 6 5 Треугольник 14 10,5 6 Треугольник 16 18 7 Треугольник 5 1 Номер фигуры Вид фигуры Сторона 1 Сторона 2 Площадь 1 Прямоугольник 2 10 2 Прямоугольник 7 309,5 3 Прямоугольник 9 9,5 4 Прямоугольник 4,5 4,5 5 Прямоугольник 1 1 Перенести данные каждой таблицы на отдельный лист (значения в графе Площадь фигуры определить по соответствующей формуле). Вариант 9. На следующем листе этой же рабочей книги получите сводные сведения по данным обеих таблиц: среднюю площадь всех прямоугольных треугольников и среднюю площадь всех прямоугольников. Вариант 10. На следующем листе этой же рабочей книги получите сводные сведения по данным обеих таблиц: максимальную длину катета 1 214 всех прямоугольных треугольников и максимальную длину стороны 1 всех прямоугольников. Вариант 11. На следующем листе этой же рабочей книги получите сводные сведения по данным обеих таблиц: максимальную площадь прямоугольника и максимальную площадь треугольника. 4. В таблицах представлены сведения о трех акционерах фирмы «Купи – продай». № № пп Фамилия Выпус к акций Вид акций Кол- во Номинальн ая стоимость акции, руб. Общая стоимост ь, руб. 1 Сидиром ов Выпус к 1 Привилегирован ная 2 5000 2 Сидиром ов Выпус к 1 Обыкновенная 10 500 3 Сидиром ов Выпус к 2 Привилегирован ная 1 5000 4 Сидиром ов Выпус к 2 Обыкновенная 12 1000 № № пп Фамилия Выпуск акций Вид акций Кол -во Номинальна я стоимость акции, руб. Общая стоимост ь, руб. 1 Мониторо в Выпуск 1 Привилегированн ая 5 5000 2 Мониторо в Выпуск 1 Обыкновенная 20 1000 3 Мониторо в Выпуск 2 Привилегированн ая 3 5000 4 Мониторо в Выпуск 2 Обыкновенная 12 1000 № № пп Фамилия Выпуск акций Вид акций Кол- во Номинальн ая стоимость акции, руб. Общая стоимост ь, руб. 1 Дискетски й Выпуск 1 Привилегированн ая 2 5000 2 Дискетски Выпуск Обыкновенная 15 1000 215 й 1 3 Дискетски й Выпуск 2 Привилегированн ая 3 5000 4 Дискетски й Выпуск 2 Обыкновенная 10 1000 Перенести данные каждой таблицы на отдельный лист (значения в графе Общая стоимость определить по соответствующей формуле). Вариант 12. На следующем листе этой же рабочей книги получите сведения по данным трех таблиц: общее количество акций и их номинальную и общую стоимость для каждого акционера. Вариант 13. На следующем листе этой же рабочей книги получите сводные сведения по данным трех таблиц: среднее количество акций и их среднюю номинальную стоимость для каждого акционера. Вариант 14. На следующем листе этой же рабочей книги получите сводные сведения по данным трех таблиц: максимальное количество акций и их максимальную номинальную стоимость для каждого акционера. Вариант 15. На следующем листе этой же рабочей книги получите сводные сведения по данным трех таблиц: минимальное количество акций и их минимальную номинальную стоимость для каждого акционера. Задание 2. Формирование сводной таблицы и сводной диаграммы Используя представленные ниже данные, согласно варианту постройте сводную таблицу и сводную диаграмму. 1. В таблице представлены сведения о ряде геометрических фигур: прямоугольных треугольниках и прямоугольниках. Номер фигур ы Виды фигуры Типы фигуры Сторона1 /Катет 1 Сторона 2 /Катет2 Площад ь фигуры 216 1 Треугольник Равнобедренный 12 12 2 Треугольник Равнобедренный 3 3 3 Треугольник Равнобедренный 10 10 4 Треугольник Неравнобедренны й 2,5 6 5 Треугольник Неравнобедренны й 14 10,5 6 Треугольник Неравнобедренны й 16 18 7 Треугольник Неравнобедренны й 5 1 8 Прямоугольни к Не квадрат 2 10 9 Прямоугольни к Не квадрат 7 3 10 Прямоугольни к Не квадрат 9 9,5 11 Прямоугольни к Квадрат 4,5 4,5 12 Прямоугольни к Квадрат 1 1 Перенести эти данные на лист электронной таблицы (значения в графе |