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

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

(1 час)

Тема: «Использование электронных таблиц для хранения и обработки данных»

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

План

1) Изучить основные возможности табличного процессора Excel

2) Изучить способы работы с данными в Excel

3) Выполнить создание, сохранение, оформление, редактирование, с использованием встроенных функций в табличном процессоре Excel

4) Ответить на контрольные вопросы

Краткие сведения

Электронная таблица – компьютерный эквивалент обычной таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты, даты, формулы, числа. Главное достоинство электронной таблицы - это возможность мгновенного пересчета всех данных, связанных формульными зависимостями при изменении значения любого операнда.

Для управления электронной таблицей используется специальный комплекс программ – табличный процессор, например Microsoft Excel.

Основные объекты обработки информации – электронные таблицы – размещаются табличным процессором в самостоятельных окнах, и открытие или закрытие этих таблиц есть, по сути, открытие или закрытие окон, в которых они размещены. Табличный процессор дает возможность открывать одновременно несколько окон, организуя тем самым «многооконный режим» работы. Существуют специальные команды, позволяющие изменять взаимное расположение и размеры окон на экране. Окно, в котором в настоящий момент мы работаем, называются текущими (активными).

Файл Microsoft Excel называется рабочей книгой. Рабочая книга состоит из рабочих листов, имена которых (Лист1, Лист2, …) выведены на ярлыках в нижней части окна рабочей книги. Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги. Все листы рабочей книги сохраняются в одном файле. Термин «рабочая книга» не является стандартным. Так, например табличный процессор Framework вместо него использует понятие Frame (рамка).

Рабочий лист представляет собой таблицу, состоящую из 256 столбцов и 65536 строк. Столбцы именуются латинскими буквами, а строки – цифрами. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы (для табличного процессора Excel от 1 до 65536 строк на листе). Имена столбцов – это буквы латинского алфавита сначала от A до Z, затем от AA до AZ, BA до BZ, …, IV. Ячейка – область, определяемая пересечением столбца и строки электронной таблицы. Каждая ячейка таблицы имеет адрес, который состоит из имени строки и имени столбца. Например, если ячейка находится в столбце F и строке 7, то она имеет адрес F7.

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

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

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

Для выделения нескольких листов необходимо нажать клавишу Ctrl и, не отпуская ее, щелкать на ярлыках листов.

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

Чтобы отредактировать данные в ячейке, необходимо:

a сделать ячейку активной и нажать клавишу F2 или дважды щелкнуть в ячейке мышью;

a в ячейке появится текстовый курсор, который можно передвигать клавишами управления курсором;

a отредактировать данные;

a выйти из режима редактирования клавишей Enter.

Внимание! Перед выполнением любой команды Microsoft Excel следует завершить работу с ячейкой, т. е. выйти из режима ввода или редактирования.

Для отмены последней операции над данными необходимо в меню Правка выбрать команду Отменить или щелкнуть кнопку (Откат). Чтобы вернуть последнюю отмененную операцию, следует в меню Правка выбрать команду Повторить или щелкнуть кнопку (Накат).

Для вставки строк или столбцов необходимо:

a выделить строки или столбцы, на месте которых будут вставлены новые;

a в меню Вставка выбрать пункт Строки или Столбцы.

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

Для удаления содержимого ячеек без удаления их самих следует:

a выделить группу ячеек, которые необходимо очистить;

a выбрать пункт Очистить из меню Правка, а затем один из пунктов: Все – очистить все; Форматы – устанавливается формат ячеек по умолчанию; Содержимое – очистка только содержимого ячеек; Примечание – очистка ячеек от примечаний.

Для удаления только содержимого ячеек также можно выделить их и нажать клавишу Delete.

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

Для копирования содержимого ячеек необходимо:

a выделить ячейки, содержимое которых следует скопировать;

a вызвать команду Копировать меню Правка или щелкнуть кнопку (вокруг группы ячеек появится пунктирная линия, указывающая, что фрагмент помещен в буфер);

a выделить ячейки, в которые следует вставить данные;

a вызвать команду Вставить меню Правка или щелкнуть кнопку .

Фрагмент из буфера скопируется в указанное место.

Для перемещения содержимого ячеек необходимо:

a выделить ячейки, содержимое которых необходимо переместить;

a вызвать команду Вырезать меню Правка или щелкнуть кнопку ;

a выделить левую верхнюю ячейку области, в которую необходимо вставить фрагмент;

a вызвать команду Вставить меню Правка или щелкнуть кнопку .

Фрагмент из буфера будет вставлен в указанное место.

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

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

Числовые данные не могут содержать алфавитных специальных символов, поскольку с ними производятся математические операции. Единственными исключениями являются десятичная запятая и знак числа, стоящи перед ним. Например, числовые данные: 45; -135; 0,435

Даты. Этот тип данных обеспечивает выполнение таких функций, как добавление к дате числа (пересчет даты вперед и назад) или вычисление разности двух дат (длительности периода). Даты имеют внутренний и внешний формат. Внешний формат используется для ввода и отображения дат. Наиболее употребительны следующие виды внешних форматов дат: ДД-МММ-ГГ (04-Янв-95); МММ-ДД-ГГ (Янв-04-95); ДД-МММ (04 - Янв)

Работа с базами данных

С помощью Microsoft Excel можно создавать и обрабатывать базы данных. База данных в Microsoft Excel – таблица, состоящая из однотипных записей (строк). Столбцы таблицы являются полями записи в базе данных. Под имена полей выделяется первая строка базы данных. Если выделить ячейку в таблице и выбрать одну из команд обработки баз данных в меню Данные, Microsoft Excel автоматически определяет и обрабатывает всю таблицу. Данные, расположенные в столбцах и строках рабочего листа, обрабатываются как набор полей, которые образуют записи.

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

В поле списка Сортировать по выбирается поле, по которому будут отсортированы данные, и тип сортировки: по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке, логические выражения – ложь предшествует истина; по убыванию – сортировка в обратном порядке.

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

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов (рис. 2). С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты.

Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр (рис. 3). В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать одно из значений. В нижнем правом списке можно выбрать другой оператор, и в поле по левую сторону – значение. Когда включен переключатель и, то будут выводиться только записи, удовлетворяющие оба условия. При включенном переключателе или будут выводиться записи, удовлетворяющие одному из условий.

Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.

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

Задания к практической работе

Задание 1. Создать таблицу Погода (рис. 1).

Порядок работы

1. Запустите табличный процессор Microsoft Excel (Пуск – Программы – 0ffiсе_2000 – Miсrosoft Excel) 2. Таблицу сохранить на D:\ Студенты\ Ваша группа\ ВАША ФАМИЛИЯ под именем файла ВАША ФАМИЛИЯ_№ ПРАКТИЧЕСКОЙ РАБОТЫ (например, Иванов 13). 3. На вкладке лист1 напечатать Ф. И. О., группа (в ячейке А1), № практической работы (в ячейке D2), тема практической работы (в ячейке А3), цель практической работы (в ячейке А5). 4. Перейдите на Лист 2, щелкнув мышью по ярлыку Лист 2, при этом откроется новый пустой лист рабочей книги. На вкладке лист2 выполнить задание 1 (исходные данные представлены на рис. 1).

Рис. 1. Исходные данные для задания 1

5. Установите курсор на ячейку С1. Введите заголовок таблицы «Погода». Введите названия столбцов таблицы ‑ «Дата» (ячейка А2), «Осадки» (ячейка B2), «Температура, » (ячейка C2), «Давление, мм. рт. ст.» (ячейка D2), «Влажность, %» (ячейка E2). Изменение ширины столбцов производите перемещением мышью в строке имен столбцов (А, В, С и т. д.).

6. Для оформления шапки таблицы выделите строку, содержащую шапку таблицы (строка 2), выберите горизонтальное и вертикальное выравнивание — «по центру» и задайте перенос по словам командой Формат/Ячейки/вкладка Выравнивание/флажок Переносить по словам.

7. Заполните таблицу исходными данными согласно заданию 1. Для ввода ряда значений даты наберите первую дату 15.03.2011 и произведите автокопирование до даты 31.03.2011 (прихватите левой кнопкой мыши за маркер автозаполнения, расположенный в правом нижнем углу ячейки, и протащите его вниз). Формат даты задается командой Формат/Ячейки/ вкладка Число/указать Тип даты/Ok

8. Произведите обрамление таблицы. Для этого выделите блок ячеек таблицы А2:Е19. Откройте окно Обрамление таблиц командой Формат/ Ячейки/вкладка Границы. Для внутренних линий выберите тонкую, а для контура — более толстую непрерывную линию. Макет отображает конечный вид форматирования обрамления, поэтому кнопку ОК нажмите, когда вид обрамления на макете полностью вас удовлетворит.

Задание 2. Выполнить сортировку по возрастанию данных из столбцов Температура и Влажность. Выполнить сортировку по убыванию данных из столбцов Температура и Давление.

Порядок работы

1. Выполнить копирование таблицы со вкладки лист2 на вкладку лист3.

2. На таблице со вкладки лист3 выполнить сортировку по возрастанию данных из столбца Температура. Для этого установить курсор на заголовок столбца Температура и нажать кнопку Сортировка по возрастанию.

3. Вставить новый лист (лист 4) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист4.

4. На таблице со вкладки лист4 выполнить сортировку по возрастанию данных из столбца Влажность. Для этого установить курсор на заголовок столбца Влажность и нажать кнопку Сортировка по возрастанию.

5. Вставить новый лист (лист 5) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист5.

6. На таблице со вкладки лист5 выполнить сортировку по убыванию данных из столбца Температура. Для этого установить курсор на заголовок столбца Температура и нажать кнопку Сортировка по убыванию.

7. Вставить новый лист (лист 6) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист6.

8. На таблице со вкладки лист6 выполнить сортировку по убыванию данных из столбца Давление. Для этого установить курсор на заголовок столбца Давление и нажать кнопку Сортировка по убыванию.

Задание 3. Выполнить поиск с использованием автофильтра:

a) облачных дней;

b) дней с влажностью 100%;

c) дней с давлением больше 750 мм. рт. ст.;

d) облачных дней с температурой больше или равной

Порядок работы

Вставить новый лист (лист 7) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист7. Включите автофильтр командой Данные – Фильтр – Автофильтр (рис. 2). Для поиска облачных дней откройте список Осадки и выберите Облачно. Вставить новый лист (лист 8) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист8. Включите автофильтр командой Данные – Фильтр – Автофильтр. Для поиска дней с влажностью 100% откройте список Влажность и выберите 100. Вставить новый лист (лист 9) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист9. Включите автофильтр командой Данные – Фильтр – Автофильтр.

Рис. 2. Команда Автофильтр устанавливает кнопки скрытых списков

9. Для поиска дней с давлением больше 750 мм. рт. ст. откройте список Давление, выберите Условие. Затем открыв список условий выберите больше и в следующем поле введите 750, нажмите Ok (рис. 3).

10. Вставить новый лист (лист 10) командой Вставка-Лист и перетащить вкладку на последнее место. Выполнить копирование таблицы со вкладки лист2 на вкладку лист10. 11. Включите автофильтр командой Данные – Фильтр – Автофильтр 12. Для поиска облачных дней с температурой больше или равной 10откройте список Температура, выберите Условие. Затем, открыв список условий, выберите больше или равно и в следующем поле введите 10, нажмите Ok.

Рис. 3. Условие поиска дней с давлением больше 750 мм. рт. ст.

Сохраните рабочую книгу (Файл-Сохранить), закройте документ (Файл-Закрыть) и закройте приложение (Файл-Выход)

Контрольные вопросы

Основная часть

Что такое электронная таблица? Приведите пример электронной таблицы? Что представляет собой табличный процессор? Приведите примеры табличного процессора? Что является основным объектом обработки информации в табличном процессоре? Что представляет собой рабочая книга? Сколько строк содержит один лист рабочей книги Excel? Как они нумеруются? Сколько столбцов содержит один лист рабочей книги Excel? Как они обозначаются? Что представляет собой ячейка? Из чего состоит адрес ячейки? Как определить активную ячейку? Как выделить группу смежных ячеек? Как выделить несколько несмежных групп ячеек? Как выделить строку (строки)? Как выделить столбец (столбцы)? Опишите процесс ввода данных в ячейку. Опишите процесс редактирования данных в ячейке. Как выполнить откат? Как выполнить накат? Как выполнить удаление содержимого ячейки? Опишите процесс копирования (перемещения) данных из одной ячейки в другую. Какие данные пользователь может ввести в ячейку электронной таблицы? Как ввести символьные данные в ячейку? Какие требования предъявляются к числовым данным? Какие форматы дат наиболее употребимы? Из чего состоит база данных в табличном процессоре Excel? Каково назначение операции сортировка? Опишите признаки сортировки по возрастанию и по убыванию? Как выполнить автокопирование дат? Как работает Автофильтр? Как вернуть все данные таблицы (которые были скрыты во время фильтрации)? Как выполнить горизонтальное и вертикальное выравнивание содержимого ячейки? Как выполнить перенос по столбцам в ячейке? Как установить границы таблицы?

Дополнительная часть

35. Почему некоторые команды в табличном процессоре Excel могут не выполняться (будут погашены)?

36. Каково назначение команды откат?

37. Каково назначение команды накат?

38. Как работает расширенный фильтр?