Практическая работа № 3

Тема: Анализ и обработка данных электронной таблицы

Цель: Научиться использовать возможности Excel для анализа таблиц с помощью операций сортировки и
фильтра

В Excel существует широкий набор средств анализа и обобщения данных в ЭТ. Доступ к этим средствам осуществляется с помощью команд меню окна Данные. Рассмотрим некоторые из них на примере Книги продаж.

Задание 1. Сортировка данных электронной таблицы

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

1. Создайте Книгу продаж

2. Переименуйте лucm1 - Продажа.

3. Введите формулу для расчета Выручки
(Цена * Количество) и скопируйте ее во все ячейки столбца.

4. Переместить/скопировать созданную таблицу на новый лист и назовите его Сортировка. Для этого:

− Щелкните правой кнопкой мыши по листу Продажа и в открывшемся контекстном меню выберите команду Переместить/скопировать;

− Далее щелкните по вкладке Создать копию;

− Затем переименуйте лист, дав имя Сортировка.

5. Выполните сортировку по столбцу Торговый агент по алфавиту. Для этого:

− установите курсор в любую ячейку таблицы и выполните команду Данные → Сортировка.

6. В диалоговом окне «Сортировка диапазона» установите Сортировать по полю «Торговый агент», по возрастанию;

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

Задание 2. Фильтрация (выборка) данных из таблицы

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

1. Переместить/скопировать таблицу с листа Продажа на новый лист и назовите его Фильтр;

2. Выберите из таблицы данные, относящиеся к торговому агенту Иванову. Для этого:

− установите курсор в шапку таблицы и выполните команду Данные, Фильтр, Автофильтр. В каждом столбце появятся кнопки списка;

− в столбце Торговый агент, нажмите кнопку, из списка агентов выберите фамилию Иванов.

3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные → Фильтр → Автофильтр.

4. Выберите из таблицы данные, используя критерий — для 195 филиала получить сведения о доставке по железной дороге. Выполните задание самостоятельно.

Задание 3. Автоматическое подведение итогов

Автоматическое подведение итогов - это удобный способ быстрого обобщения и анализа данных в электронных таблицах.

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

2. Подведите итоги работы каждого торгового агента. Для этого:

− Проведите сортировку записей таблицы по Торговым агентам (по алфавиту);

− Установите курсор в шапку таблицы и выполните команду Данные → Итоги;

− В окне Промежуточные итоги выберите - Торговый агент, Операция - сумма,

− В окне Добавить итоги выберите Количество и Выручка.

3. Самостоятельно подведите итоги по работе филиалов.

Задание 4. Фиксация заголовков таблицы

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

1. Переместить/скопировать таблицу с листа Продажа на новый лист и назовите его Фиксация заголовков;

2. Зафиксируйте шапку таблицы. Для этого:

− Выделите ячейку, через левый угол которой должна пройти граница закрепления (ячейка с фамилией Белкин С.А.);

− Выполните команду Вид → Закрепить области;

− С помощью «бегунка» пролистайте строки - шапка должна оставаться на месте.

3. Отмените фиксацию заголовка. Дня этого выполните команду Вид → Снять закрепление областей.

4. Зафиксируйте шапку таблицы и столбец с Торговыми агентами. Для этого:

5. Выделите ячейку, через левый угол которой должна пройти граница закрепления (ячейка с номером филиала 195).

6. Сохранить работу в своей папке с именем «Excel3/ В работе должны быть 5 листов: Продажа, Сортировка, Фильтр, Итоги, Фиксация заголовков.

Задание 5. Применение абсолютных ссылок

Ссылки в Excel бывают 3-х типов:

¾ Относительные ссылки (пример: A1);

¾ Абсолютные ссылки (пример: $A$1);

¾ Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

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

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной.

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

2. Перед таблицей вставить 3 строки.

3. Ввести Курс доллара в одну из ячеек – 32,53.

4. После столбца Выручка ввести столбец Выручка в $.

5. Используя Курс доллара подсчитать Выручку в $.