Лабораторный практикум по курсу «Информатика» (стр. 3 )

В документе Сортировка и выборка.xls скопировать рабочий лист 1 на все остальные рабочие листы (со 2 по 7).

С помощью контекстного меню переименовать лист 1 в лист Сортировка.

На листе Сортировка скрыть две последние строки таблицы с помощью контекстного меню, выделить всю таблицу (строки с 1 по 16) и скопировать их ниже на этом же листе еще 4 раза.

Сортировка данных в электронных таблицах производится с помощью команд меню Данные à Сортировка.

На листе Сортировка во втором экземпляре таблицы осуществить сортировку по данным столбца Плотность населения (по убыванию).

В третьем экземпляре таблицы расположить страны по алфавиту.

В четвертом экземпляре таблицы провести сортировку по данным последнего столбца (%).

В последнем экземпляре таблицы расположить страны по численности населения, а затем в этом же экземпляре отсортируйте данные по первому столбцу (по номерам).

Проанализировать полученные результаты.

Сохранить работу.

2. Фильтрация данных с использованием автофильтра

Фильтрация (выборка) данных позволяет отобразить в таблице только те строки, содержимое ячеек которых отвечает заданному условию (или нескольким условиям). Эта операция может выполняться с помощью автофильтра или расширенного фильтра.

Для выполнения фильтрации данных с помощью автофильтра нужно:

1.  установить курсор внутри таблицы;

2.  ввести команду Данные à Фильтр à Автофильтр;

3.  щелчком мыши по кнопке со стрелкой раскрыть список столбца, по которому будет производиться выборка;

4.  выбрать строку «условие» и задать критерии выборки.

Для воссоздания исходной таблицы необходимо щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку «все» или осуществить команду Данные à Фильтр à Отобразить все.

Для отмены режима фильтрации нужно установить курсор внутри таблицы и снова ввести команду Данные à Фильтр à Автофильтр (убрать переключатель).

Произвести фильтрацию записей таблицы на листах 2-5 документа Сортировка и выборка.xls согласно следующим критериям:

·  На листе 2 выбрать страны с площадью более 5 000 тыс. км2.

·  На листе 3 – страны с населением меньше 150 млн. чел.

·  На листе 4 – страны с плотностью населения от 100 до 300 чел/км2.

·  На листе 5 – страны, население которых составляет более 2% от всего населения Земли.

На листе 2 возобновить исходный вариант таблицы и отменить режим фильтрации.

Сохранить работу.

3. Фильтрация данных с использованием расширенного фильтра

Расширенный фильтр позволяет реализовывать более сложную выборку данных электронной таблицы с заданием нескольких условий.

Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню Данные à Фильтр à Расширенный фильтр.

В документе Сортировка и выборка.xls переименовать лист 6 в лист Расширенный.

На этом же листе (под таблицей) создать рамку для текстового поля.

Найти в справочной системе Excel раздел Фильтры, расширенные, Фильтрация списка с помощью расширенного фильтра и скопировать содержимое этого раздела в текстовое поле. Отредактировать размер текстового поля и текст справки.

Изучить технологию выборки данных с использованием расширенного фильтра.

На листе Расширенный выбрать из таблицы страны, начинающиеся с буквы «К» и имеющие численность населения более 1 млрд. чел.

Предъявить преподавателю результаты работы:

·  Сохраненный в своей папке файл Сортировка и выборка.xls;

·  Лист с отсортированными данными;

·  Листы с выборками из таблицы.

Закрыть все окна.

Сохранить работу.

Контрольные вопросы к лабораторной работе № 3

1.  Какими способами можно отсортировать данные электронной таблицы?

2.  Какими способами можно выполнить фильтрацию (выборку) данных в электронной таблице?

3.  Как выполнить фильтрацию данных в электронной таблице с помощью Расширенного фильтра?

Лабораторная работа №4. Создание и редактирование диаграмм

Цель работы.

Ознакомление с приемами работы по созданию и редактированию диаграмм.

1. Построение круговой диаграммы

Пример 1.

Переименовать лист 7 документа Сортировка и выборка.xls в лист Диаграммы (см. Таблицу 2).

На листе Диаграммы выделить в таблице столбцы с названиями стран и численностью населения.

Построить круговую диаграмму по данным выделенных столбцов.

Изменить тип диаграммы. Восстановить круговую диаграмму.

Построить различные гистограммы по данным остальных столбцов.

2. Построение объемной диаграммы

Пример 2.

На основе проведенных расчетов (см. Таблицу 3) создайте новую таблицу по приведенному ниже образцу и с помощью команды меню Вставка à Диаграмма постройте объемную гистограмму (рис.1).

	«Февраль 2008»	«Сегодняшняя дата»
Курс $,руб.	26,2
Выручка, тыс. руб.
Доход, тыс. руб.

Рис.1. Доходы фирмы «Юпитер»

Пример 3.

Активизируйте лист 2. Присвойте листу 2 имя Динамика.

Выделите весь лист и очистите его командой ПРАВКА à Очистить à Все.

Постройте диаграмму на основе созданной в лабораторной работе № 2 таблицы (см. Таблицу 4), используя кнопку Мастер диаграмм. Тип диаграммы выберите по своему вкусу (пример смотри на рис. 2.) Переименуйте файл Сортировка и выборка.xls в Диаграммы.xls .

Подвергните анализу содержание построенной диаграммы. Поменяйте тип диаграммы.

Почему в этом случае не следует использовать круговую диаграмму?

Для редактирования диаграммы нужно выделить ее щелчком мыши, а затем использовать команды контекстного меню или меню ФОРМАТ.

Рис. 2. Динамика изменения численности населения Земли

Аналогично можно отредактировать и отдельные элементы диаграмм.

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

Разверните панель Рисование встроенного графического редактора MS Office. Сопоставьте с панелью рисования в Word.

Работа с графическим редактором Excel практически не отличается от рисования в Word.

С помощью инструмента Надпись сделайте заголовок диаграммы.

Нарисуйте какую-либо автофигуру и поэкспериментируйте с тенью, объемом и окрашиванием рисованных объектов.

С помощью команды меню СЕРВИС à Параметры à Вид скройте и снова восстановите сетку на рабочем листе.

Сохранить работу. Результаты работы:

·  Сохраненные в своей папке файл Диаграммы.xls;

·  Таблицы с выполненными в ней расчетами;

·  Построенные диаграммы.

Контрольные вопросы к лабораторной работе № 4

1.  Как построить в электронной таблице диаграмму?

2.  Как отредактировать построенную диаграмму?

3.  Как убрать с экрана изображение сетки?

процентная ставка, В2 – срок возврата вклада, а символ : ^ - это оператор «возведение в степень».

Сумма возврата вклада вычисляется в ячейке В5 по формуле: =В1*В4.

Введите формулы в ячейки В4 и В5.

Скопируйте созданную таблицу на этот же лист, а также на листы 2 и 3.

Сохраните работу.

Введите команду СЕРВИСà Подбор параметра и в первой копии таблицы на листе Подбор рассчитайте процентную ставку при которой сумма возврата вклада будет составлять 8 000 рублей.

Во второй копии таблицы рассчитайте на этом же листе срок вклада при котором сумма возврата вклада будет составлять 8 000 рублей.

Сохраните работу.

2. Использование надстройки Поиск решения

Программа Поиск решения позволяет получить результат при изменении значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения.

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

Введите команду СЕРВИСàНастройки и в диалоговом окне Настройки подключите настройку Поиск решения – установите соответствующий переключатель.

Сделайте активным лист 2 и назовите его Поиск.

Создайте на этом же листе еще одну копию таблицы.

В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата вклада будет составлять 8 000 рублей. Для этого выполните следующие действия:

1. Введите команду СЕРВИСà Поиск решения и в диалоговом окне Поиск решения установите следующие параметры:

- адрес целевой ячейки - $В$5 – сумма возврата вклада;

- подбираемое для целевой ячейки значение – 8 000 р.;

- в поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки.

Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значениями целевой ячейки В5 равной В4*В1, так как входят в формулу расчета коэффициента увеличения вклада В4=(1+В3)^В2.

2. Введите ограничения для ячейки со сроком вклада – цел – целое число лет.

3. Нажмите кнопку Выполнить.

В диалоговом окне Результаты поиска решения установите:

- Сохранить найденное решение;

- Тип отчета – результаты.

Активизируйте рабочий лист с результатами поиска решения и скопируйте результаты на рабочий лист Поиск.

Проанализируйте полученные результаты.

Во второй копии таблицы на листе Поиск еще раз выполните операцию Поиск решения, установив следующие параметры:

- адрес и значение целевой ячейки – сумма возврата вклада 8 000 рублей;

- в поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки;

- добавьте ограничения для ячейки с величиной процентной ставки: <= 7%.

Сохраните результаты поиска решения в виде сценария под именем Поиск и восстановите в таблице исходные значения.

Введите команду СЕРВИСà Сценарии и с помощью диалогового окна под названием Диспетчер сценариев для второй копии таблицы добавьте новый сценарий под именем Поиск 1, в котором установите значение для ячейки со сроком вклада 10 лет, а для ячейки с процентной ставкой – 10%.

Выведите сценарий Поиск 1 и создайте отчет по сценариям в виде структуры. Проанализируйте полученные результаты.

Проанализируйте результат и сохраните работу.

Предъявите преподавателю результаты работы:

- сохраните в своей папке файл Подбор.xls,

- результаты подбора параметров и поиска решения;

- отчет по сценарию.

Контрольные вопросы к лабораторной работе № 5

1.  Какие средства условного анализа имеются в Excel?

2.  Каково назначение инструмента Подбор параметра?

3.  Каково назначение надстройки Поиск решения? Опишите технологию выполнения этой операции.

4.  Для чего в Excel используют сценарии? Как создать сценарий?

Лабораторная работа №6. Обобщение данных. Создание таблицы подстановки. Подведение итогов

Цель работы:

1.  Создание таблиц подстановки с одной или двумя переменными.

2.  Освоение операции автоматического подведения итогов. Работа со структурой электронной таблицы.

3.  Выполнение вычислений и построение диаграмм на основе итоговых данных.

1. Создание таблицы подстановки с одной переменной

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

Excel может создавать таблицы подстановки следующих типов:

- таблицы подстановки с одной переменной и с одной или несколькими формулами;

- таблицы подстановки с двумя переменными.

С помощью таблиц подстановки также можно выполнить анализ примера с помещением вклада, т. е. определить влияние изменения:

1) процентной ставки на сумму возврата вклада;

2) процентной ставки на коэффициент увеличения вклада;

3) величины процентной ставки и изменения срока вклада на сумму возврата вклада.

Для решения первых двух задач используйте таблицы подстановки с одной переменной.

Откройте файл Подбор.xls (см. лабораторную работу №5) и активизируйте лист 3, присвойте ему имя Таблицы подстановки.

Перед началом таблицы вставьте две пустые строчки.

Сделайте на этом же листе еще две копии таблицы с пятью пустыми строками перед каждым экземпляром таблицы.

В качестве переменной используйте процентную ставку (ячейка ввода В5), которая может принимать значения от 3 до 10% . Введите эти значения в столбец D согласно приведенному образцу (Таблица 6).

Таблица 6. Данные для таблицы подстановки с одной переменной

	A	B	C	D	E
1				Процент	Сумма возврата
2					=B3*B6
3	Размер вклада	5000руб.		3%
4	Срок вклада	5		4%
5	Процентная ставка	5%		5%
6	Коэфф. увеличения	1,28		6%
7	Сумма возврата	6381руб.		7%
8				8%
9				9%
10				10%

В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы вклада: =В3*В6.

Выделите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.

Введите команды ДАННЫЕ à Таблица подстановки и в диалоговом окне в поле Подставлять значения по строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) - $B$5.

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

При этом следует иметь в виду, что все используемые для подстановки формулы должны быть прямо или косвенно связаны с одной и той же ячейкой ввода, в рассматриваемом примере – с ячейкой В5, содержащей значение процентной ставки.

Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: = (1+В5)^В4.

Выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕ à Таблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $B$5.

Проанализируйте полученные результаты.

Обратите внимание на то, что обе формулы связаны с одной и той же ячейкой ввода. Сохраните документ.

2. Создание таблицы подстановки с двумя переменными

Таблицы подстановки с двумя переменными позволяет решить еще одну задачу – определить влияние величины процентной ставки и изменения срока вклада на сумму возврата вклада.

При создании таблицы подстановки с помощью двух переменных формула для определения значений должна находиться в месте пересечения столбца и строки с подставленными значениями.

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

Скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10

Введите формулу расчета суммы возврата вклада в ячейку, находящуюся выше первого подставляемого значения процентной ставки.

В строку правее формулы введите значения подстановки для второй переменной – срока вклада – от 5 до 10 лет.

Выделите диапазон ячеек, содержащих формулу и оба набора значений для подстановки.

Введите команду ДАННЫЕ à Таблица подстановки.

В диалоговом окне Таблица подстановки введите абсолютные адреса двух ячеек ввода.

Ссылку на ячейку ввода для значений подстановки, расположенных в столбце, указывают в поле Подставлять значения по строкам.

Ссылку на ячейку ввода для значений подстановки, расположенных в строке, указывают в поле Подставить значения по столбцам.

Проанализируйте полученные результаты.

В третьем экземпляре исходной таблицы создайте еще одну таблицу постановки с двумя переменными: процентной ставкой и размером вклада, подставляя значения размера вклада от 4 000 до 8 000 р. Сохраните работу.

3. Автоматическое подведение общих и промежуточных итогов. Работа со структурой таблицы

Создайте файл Итоги.xls и создайте в нем 2 новых рабочих листа.

Присвойте новым рабочим листам имена Итоги и Вычисления.

В таблице приведены данные по реализации лицензионного программного обеспечения двумя продавцами фирмы «Юпитер».

Подготовьте таблицу на листе Итоги.

Таблица 7. Таблица с исходными данными.

Введите в ячейку G5 нужную формулу и выполните вычисления в столбце Объем продаж.

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

Для решения этой задачи удобно использовать средства Excel для автоматического подведения общих и промежуточных итогов – команду ДАННЫЕ à Итоги.

Выполните следующую последовательность действий:

1)  отсортируйте данные в таблице по столбцу Продавец;

2)  выделите всю таблицу – диапазон ячеек A4:G22 и введите команду меню ДАННЫЕ à Итоги;

3)  в диалоговом окне Промежуточные итоги из списка При каждом изменении в: выберите столбец Продавец;

4)  из списка Операция выберите функцию сумма;

5)  в списке Добавить итоги по: выберите столбцы, содержащие значения, по которым необходимо подвести итоги, - Количество и Объем продаж;

6)  щелкните по кнопке ОК.

Обратите внимание на появление в таблице новых строк с итоговыми данными.

Выделите итоговые данные полужирным курсивом и размером шрифта 12 пт.

Сохраните работу.

Общие и промежуточные итоги динамически связаны с исходными (детальными) данными, и все изменения в исходных данных будут автоматически отражаться в итоговых результатах.

Внесите какие-либо изменения в ячейки столбца Количество продаж и проанализируйте результат.

При подведении итогов автоматически создается структура таблицы.

На листе Итоги символы структуры отображаются слева от номеров строк (рис. 3).

Рис. 3. Символы структуры

На листе Итоги имеется три уровня структуры -1, 2 и 3.

Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами.

Для того чтобы показать все данные в таблице, нужно щелкнуть по самому большому номеру. Чтобы скрыть детальные данные всех уровней структуры, нужно щелкнуть по номеру 1. Данные уровня 1 скрыты быть не могут.

Линейки уровней показывают, какие группы строк или столбцов входят в каждый уровень структуры.

Исследуйте возможности работы со структурой таблицы на листе Итоги. Скройте все детальные данные и отобразите только результаты подведения итогов.