Работа с табличным процессором MS Excel.

Освоение приемов работы с электронными таблицами

Практическое занятие 3.

Выполнение расчетов, сортировка и фильтрация данных
в  программе Excel

Цель работы:

1. Выполнение расчетов в электронной таблице

2. Освоение средств сортировки и фильтрации данных.

1 Создание таблицы и выполнение расчетов

1.1    В документе Tabl.xls сделайте активным лист Таблица. Выделите и очистите лист.  Создайте приведенную на рис. 3.1 таблицу.

Рис. 3.1. Площадь и население стран мира в 2008 г.

1.2            Различные способы вычисления суммы:

1.2.1     Вычислите сумму в столбце Площадь, используя кнопку f  Вставка функции и функцию СУММ.

1.2.2 Вычислите сумму в столбце Население, используя кнопку SАвтосумма.

1.3    Для каждой страны вычислите:

•      Плотность населения, чел/км²;

•      Долю (в %) от всего населения Земли.

При вычислении долей (в %) используйте формулу, содержащую в качестве делителя абсолютный адрес ячейки с числом, обозначающим количество населения Земли, - 6 765 000:

 $адрес столбца $адрес строки, например, =D2/$D$13.

1.4    В файле Adres.doc находится информация об адресации ячеек. Скопируйте файл Adres.doc в свою папку и внимательно ознакомьтесь с его содержанием.

1.5.   Используя кнопку Вставка функции и функцию СРЗНАЧ, в ячейке Е13 вычислите среднюю плотность населения.

1.6.   Подсчитайте, сколько процентов населения Земли составляет население всех стран, приведенных в таблице.I

1.7.   С помощью кнопки уменьшите разрядность вычисленных значений и установите точность - 1 десятичный знак после запятой.

1.8.   С помощью команды меню ФОРМАТ – Строка – Скрыть скройте (временно удалите из таблицы) две последние строки.

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

1.10. В исходном экземпляре таблицы выделите две строки, находящиеся выше и ниже удаленных строк, и восстановите скрытые строки с помощью команды меню ФОРМАТ – Строка – Отобразить.

1.11. Отформатируйте исходный экземпляр таблицы, используя команды меню ФОРМАТ – Ячейки и кнопки панели инструментов Границы и Цвет заливки.

1.12.Отформатируйте второй экземпляр таблицы с помощью команд меню ФОРМАТ – Автоформат.

1.13. Сохраните работу в своей папке.

1.14. На листе Таблица рабочей книги Tabl.xls выделите строки с 1-й по 14-ю (исходный экземпляр таблицы) и скопируйте их в буфер обмена.

2. Сортировка данных

2.1.   Создайте новую рабочую книгу, содержащую 7 рабочих листов. Добавить рабочие листы можно с помощью команды меню ВСТАВКА–Лист.

2.2.   Вставьте таблицу, находящуюся в буфере обмена, на рабочий лист 1 (с активной ячейкой А1).

2.3    Сохраните рабочую книгу в своей папке в виде файла с именем Сортировка и выборка.х1s.

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

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

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

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

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

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

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

Получился исходный вариант таблицы?

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

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

3. Фильтрация (выборка) данных с использованием автофильтра

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

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

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

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

•     На листе 4 - выберите страны с плотностью населения от
100до З00 чел./км².

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

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

4.Контрольные вопросы к занятиям 1-3.

1. Каково назначение и основные функциональные возможности табличного процессора Excel?

2. Укажите основные элементы окна Excel и их функциональное назначение.

3. Укажите элементы строки формул и их назначение.

4. какая информация отображается в строке состояния программы Excel?

5. Какие операции можно выполнять с рабочими листами Excel?

6. Что такое ссылка в Excel?   Как вводятся ссылки?

7. Что такое относи тельный адрес, абсолютный адрес,  как они используются?

8. Как ввести в ячейку формулу?

9. Как выделить в таблице смежные и несмежные ячейки, диапазоны ячеек?

10. Как скопировать и как переместить ячейку, блок ячеек, рабочий лист?

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

12. Как объединить ячейки? Как отменить объединение ячеек?

13. Как разбить текст в ячейке на несколько строк?

14. Как изменить ширину столбцов и высоту строк в таблице?

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

16. Для чего используется фильтрация данных?

5.Материал к следующему занятию

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

[1- 423-438,416-425, 487-489,491-493] [2- 2- 203, 206]

6. Дополнение. Интерфейс программы Excel – 2007.

6.1    Пиктограмма команды Автосумма в программе Excel – 2007 располагается на вкладке Главная в группе Редактирование. Заметьте, что из связанного с этой пиктограммой списка можно выбрать и другие команды: Среднее, Число, Максимум, Минимум, а так же перейди в стандартный диалог вставки  функции в ячейку.

6.2    Пиктограмма команды Вставить функцию,  а так же пиктограммы команд для выбора функций из определенной группы функций, находятся на вкладке Формулы в группе Библиотека функций.

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

6.4    Для скрытия или отображения строк или столбцов в таблице  следует выполнить следующую последовательность действий: на вкладке Главная в группе Ячейка выбрать список Формат, в этом списке выбрать в разделе Видимость пункт Скрыть или отобразить и из вложенного меню выбрать требуемую команду.

6.5    Пиктограммы инструментов Границы, Цвет заливки, Цвет текста находятся на вкладке Главная в группе Шрифт.

6.6    В программе Excel – 2007 команда меню Формат – Автоформат отсутствует. Вместо нее можно воспользоваться командой Форматировать как таблицу в группе Стили на вкладке Главная, выбрать требуемое оформление таблицы и, если в ней не требуется подведение итогов и другие подобные операции, а контекстном меню таблицы выбрать Таблица – Преобразовать в диапазон.

6.7    Команды сортировки в Excel – 2007 могут вызываться из контекстного меню таблицы, из списка Сортировка и фильтр группы Редактирования на вкладке Главная или пиктограммами Сортировка из группы Сортировка и фильтр на вкладке Данные.

6.8    Фильтрация Excel – 2007 может быть вызвана из контекстного меню таблицы или пиктограммой Фильтр из группы Сортировка и фильтр на вкладке Данные.