Лаболаторная работа 3

Лабораторная работа № 3

Тема 5: Прикладное программное обеспечение. Табличный процессор Excel

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

Цель работы: Сформировать представление об основных этапах создания таблицы на рабочем листе Excel и выработать навыки создания таблиц: формирование заголовка и шапки таблицы, ввод формул, ввод и форматирование исходных данных, обрамление таблицы, защита данных, сохранение и печать таблицы

Содержание

Внимание. Команды меню Данные работают корректно, когда шапка таблицы расположена в одной строке (если бы колонки таблицы были пронумерованы, то для работы команды Данные можно было бы использовать нумерацию в качестве названия колонок). При работе с командой Данные Excel рассматривает таблицу как базу данных, в которой строки являются записями, а столбцы - полями. Для дальнейшей работы преобразуем на новом листе таблицу 1.2. к виду:

Команды меню Данные многофункциональны. Рассмотрим эти возможности, разбив их по темам.

 

1.1 Сортировка табличных данных

Пример 1. Отсортировать таблицу (рис 3.1) по столбцам Группа и Номер зачетки.

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

1) Выделить диапазон таблицы, включая шапку (А3:F15).

2) Выполнить команду Данные-Сортировка.

3) В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать из списка имя столбца - Группа, а в поле Затем по - Номер зачетки, установив переключатели для обоих столбцов - По возрастанию.

4) Нажать кнопку [ОК].

1.2. Использование Автофильтра Пример 2. Выбрать из таблицы, представленной на рис.3.1, тех студентов, средний балл которых >=4.

Технология фильтрации (выбора) данных из таблицы с помощью Автофильтра

1) Выделить диапазон таблицы, включая шапку (A3:F15).

2) Выполнить команду Данные-Фильтр-Автофильтр. Ячейки с названиями полей превратятся в раскрывающиеся списки.

3) Раскрыть список, в столбце Средний балл студента.

4) Выбрать в раскрывающемся списке Автофильтра команду Условие. В окне Пользовательский автофильтр в левом верхнем раскрывающемся диалоговом окне выбрать операцию Больше или равно, в правом - выбрать или ввести значение "4".

5) Нажать кнопку [ОК].

6) Для отмены условия выбрать в раскрывающемся списке, выделенном голубым цветом, команду ВСЕ.

1.3. Работа с расширенным фильтром

Пример 3. Выбрать из таблицы 1.1., созданной на листе "Список", записи со студентами группы ДКХ, у которых Вид оплаты =1. Результат поместить в отдельный выходной блок, разместив его ниже исходной таблицы.

Технология фильтрации табличных данных с помощью Расширенного фильтра

1. На листе "Список" сформировать диапазон условий (A18:B19), отделив его от исходного диапазона (A4:D16), хотя бы одним пустым столбцом или строкой. Чтобы создать диапазон условий необходимо скопировать заголовки полей исходного диапазона, которые будут ключевыми при отборе записей (для нашего случая - это Группа и Вид оплаты), и заполнить строки критериев:

 В ячейку A19 ввести критерий *Х;

 в ячейку B19 ввести 1.

2. Сформировать выходной диапазон, скопировав заголовки полей исходной таблицы в диапазон D18:G18, отделив его пустой строкой или столбцом от диапазона исходной таблицы и диапазона условий (см. рис. 3.2.).

3. Установить курсор в любую ячейку исходного диапазона.

4. Выполнить команду Данные-Фильтр-Расширенный фильтр. В диалоговом окне Расширенный фильтр выполнить следующие действия:

 Установить один из переключателей в группе Обработка. Выберем Скопировать результат в другое место - исходная таблица не изменится, а отобранные записи будут помещены в выходной диапазон.

 В поле Исходный диапазон ввести ссылку на диапазон содержащий исходную таблицу (A4:D16). Ввод ссылок во всех полях данного диалогового окна легче и лучше всего осуществлять путем протаскивания указателя мыши по нужному диапазону.

 В поле Диапазон условий ввести ссылку на диапазон условий (A18:B19).

 В поле Поместить результат в диапазон установить ссылку на выходной диапазон (D18:G26), так как выбран переключатель Скопировать результат в другое место. Рекомендуется, выделяя выходной диапазон, захватить достаточное количество пустых строк для размещения в них отобранных данных.

 Необходимо установить флажок Только уникальные записи, чтобы одинаковые записи не повторялись (будет выводиться только первая из всех удовлетворяющих критерию одинаковых записей), Нажать кнопку [ОК].

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

При необходимости выбора из таблицы 1.1 или всех студентов группы ДКХ, или тех студентов, у которых Вид оплаты =1 (независимо от группы), диапазон условий будет иметь вид:

При каждом выполнении команды Расширенный фильтр Excel просматривает полный список, а не текущее множество ранее отфильтрованных строк. Вследствие этого не обязательно использовать команду Данные-Фильтр-Отобразить все перед изменением фильтра.

 

Пример 5. Изменить в таблице "Список студентов ФЭУТ 1-го курса", представленной на листе Список", фамилию студентки Ефремовой на новую - Приходько, полученную после замужества.

Технология редактирования данных с помощью формы

1. Выделить таблицу вместе с шапкой (A4:D16).

2. Подать команду Данные - Форма. Откроется форма данных.

3. Нажать кнопку [Критерии].

4. Произойдет очистка полей и замена некоторых кнопок в форме.

5. Перейти к полю Фамилия, которое будет участвовать в определении критерия и ввести критерий: Ефремова Д.А.

6. Нажать кнопку [Далее] или [Назад], чтобы перейти к записи, удовлетворяющей введенному критерию.

7. Ввести новую фамилию и инициалы: Приходько Д.А.

8. Нажать кнопку [Закрыть].

 

1.5. Подведение итогов

Пример 6. Рассчитать средние баллы по всем дисциплинам, каждой из учебных групп (на примере таблицы, представленной на рис.3.1)

Технология подведения частных и общих итогов

1. Выделить диапазон таблицы, включая шапку (A3:F15).

2. Выполнить команду Данные-Итоги. В диалоговом окне Промежуточные итоги:

 В поле При каждом изменении в: из раскрывающегося списка выбрать Группа.;

 в поле Операция из раскрывающегося списка выбрать Среднее;

 в поле Добавить итоги по: установить флажки: Математика, Информатика, Философия;

 установить флажок Итоги под данными

3. Нажать кнопку [ОК].

4. Округлить полученные итоги до двух десятичных знаков с помощью команды Формат-Ячейки.

Результат работы команды Итоги представлен на рис. 3.3.

В результате подсчитаны средние баллы по группам и общий средний балл. Если с экрана убрать детали, нажав кнопку с изображением цифры "2"(второй уровень итогов), расположенную левее нумерации строк, то на экране останутся только полученные итоги (см. рис. 3.4).

Рис. 3.4. Результат показа второго уровня итогов.

Для получения развернутой информации по группе следует нажать кнопку с изображением знака "+" для соответствующей группы; для свертывания - кнопку с изображением знака "-".