3. Лаб. раб.(Создание таблиц и выполнение расчетов). Лабораторная работа 3 Создание таблицы и выполнение расчетов. Сортировка и автофильтр Задание Создание таблицы Создайте документ Lab xls
 Скачать 400.25 Kb.
|
|
Лабораторная работа 3 Создание таблицы и выполнение расчетов. Сортировка и автофильтр Задание 1. Создание таблицы Создайте документ «Lab-3.xls» с помощью контекстного меню Создать или откройте меню Пуск/Все программы или Программы, в зависимости от версии Windows)/ /Microsoft Office/Microsoft Office Excel. Сохраните работу с именем «Lab-3.xls» в папке с вашими документами. Переименуйте Лист 1 в Студенты. Наберите заголовки (в столбцы A—G, строки 1—2) (рис. 3.1). Рис. Таблица Студенты Измените ширину столбцов D, E, F, используя мышь. Ширину столбца можно изменить, перетащив его правую границу между заголовками столбцов, при этом во всплывающей подсказке отображается устанавливаемая ширина столбца (в знаках и пикселях). Выделите мышкой ячейки Dl, El, F1 и объединить их, используя кнопку. в группе Выравнивание на ленте Главная Аналогично объедините ячейки Аи А В и В Си Си. Выделите мышкой блок ячеек A1:G2 и отформатируйте следующим образом а) выполнить центрирование текста по горизонтали и по вертикали (команда ленты Главная в группе Выравнивание б) выделить полужирным, используя соответствующую кнопку на ленте Главная в группе Шрифт Выделите всю созданную таблицу и нажмите кнопку Все границы разворачивающегося меню Границы в группе Шрифт на ленте Главная рис. 3.2). Рис. Кнопка Все границы Введите в ячейки АЗА номера от 1 до 10, используя заполнение числовой прогрессии с шагом 1. Для этого в группе Редактирование на ленте Главная выбрать кнопку Заполнить. В разворачивающемся меню выбрать Прогрессия (рис. 3.3). Введите в столбец В, начиная с третьей строки (ячейки ВЗ, В, В, фамилии десяти студентов (без инициалов) своей группы. Введите даты рождения (день, месяц, год) в столбец С. Введите в столбцы D, E, F оценки. В ячейку G3 занесите формулу = СРЗНАЧ(D3:F3) для вычисления среднего балла. Скопируйте формулу в ячейки G4:G12, используя автозаполнение. Для этого установите курсор на маркер автозаполнения (правый нижний угол) ячейки G3 и протащите его по указанным ячейкам. Выполните редактирование столбца В, добавив инициалы к фамилии разными способами а) выполняя двойной щелчок в редактируемой ячейке б) используя строку формул. Рис. Кнопка Заполнить Задание 2. Создание и редактирование формул в таблице Переименуйте Лист 2 в Таблица 1», щелкнув по ярлычку листа внизу левой кнопкой мыши. Создайте приведенную на рис. 3.4 таблицу, не заполняя ячейки Си, позднее заполните их с помощью вычислений. Вычислите сумму в столбце Площадь, используя кнопку Вставка функции) и функцию СУММ Рис. Таблица для задания 2 Вычислите сумму в столбце Население, используя кнопку (Автосумма на ленте Главная. Раскрывающийся список функций кнопки Автосумма на рис. 3.5. Рис. Кнопка Автосумма Для каждой страны вычислите плотность населения и долю от всего населения Земли (для деления используйте «/» с цифровой клавиатуры, для верхних индексов используйте надстрочное видоизменение шрифта в полном меню Шрифт на ленте Главная — полное меню открывается при выборе маленькой серой кнопки — стрелки внизу справа меню Шрифт. Для вычисления плотности населения каждой из стран (чел/км 2 ), используйте формулу =D2/C2 D14/C14. При вычислении долей в %, используйте формулу, содержащую в качестве делителя абсолютный адрес ячейки с числом, обозначающим количество населения Земли, — 6 091 000. Адрес столбца Адрес строки, например =D2/$D$16. Для того, чтобы получить данные в процентном формате необходимо выделить столбец сданными ив группе Ячейки на ленте Главная выбрать кнопку Формат, выберите Формат ячеек на вкладке Число установить Процентный формат, установив Число десятичных знаков 2 после запятой. Каждая ячейка имеет свой адрес ссылку, который определяется соответствующими столбцом и строкой. Ссылки бывают относительные, абсолютные и смешанные • относительная ссылка имеет вид А, • абсолютная ссылка имеет вид А, • смешанная ссылка имеет вид А (фиксируем столбец) или А (фиксируем строку. Если вы скопируете в другую ячейку формулу, содержащую относительную ссылку, то формула изменится (изменятся адреса связанных с формулой ячеек. Причем Excel сделает это автоматически и не надо заботиться о корректировке формул после копирования ячеек. Однако может возникнуть ситуация, когда ссылка на ячейку меняться не должна (например, при вычислении долей в % используется ячейка D16, которая постоянна) В этом случае необходимо использовать абсолютную ссылку, зафиксировав столбец и (или) строку знаком $. Например, если ссылка выглядит так В, то при автозаполнении все ячейки будут содержать формулу В При копировании формулы со смешанными ссылками будет изменяться только незакрепленная часть адреса (строка или столбец. Автоматизировать ввод знака $ можно нажатием на клавишу F4 на клавиатуре. Используя кнопку Вставка функции и функцию СРЗНАЧ, вычислите в ячейке Е среднюю плотность населения. Подсчитайте, сколько процентов населения Земли составляет население всех стран, приведенных в таблице. С помощью кнопки уменьшите разрядность вычисленных значений столбца F Вот всего населения и установите точность — 2 десятичных знака после запятой. В столбце Плотность населения установите точность — 3 десятичных знака после запятой. Отредактировать уже введенную формулу можно одним из следующих способов • дважды щелкните мышью по ячейке, чтобы непосредственно в ней начать редактирование, • выделите ячейку, нажмите клавишу F2 и редактируйте непосредственно в ячейке, • выделите ячейку и редактируйте ее содержимое в строке ввода. Создайте копию вычисленной таблицы на новом листе. Выделите созданную таблицу и отформатируйте, используя вкладку Главная/раздел Стили/Форматировать как таблицу В поле окна Форматирование таблицы будет автоматически указан диапазон данных, который преобразуется в таблицу. При необходимости можно очистить поле и на листе выделить другой диапазон ячеек сданными, которые оформляются в виде таблицы. Нажмите кнопку ОК. В результате будет создана таблица. В каждый столбец автоматически добавляется значок автофильтра . Автоматически будет отображена контекстная вкладка Работа с таблицами/Конструктор. • Сохраните работу в своей папке. Задание 3. Сортировка данных Лист 3 переименуйте в Нагрузка и создайте таблицу (рис. 3.6). Рис. Таблица расчета нагрузки Отсортируем таблицу по двум признакам первичный — курс (по убыванию, вторичный — вид работы (по алфавиту. Для этого выделим диапазон A5:N13 и выполним Данные/Сортировка. Зададим настройки, как показано в окне Сортировка (рис. 3.7). Рис. Настройка Сортировка данных В результате получим отсортированную таблицу. Скопируйте таблицу, находящуюся на листе Таблица 1». Вставьте таблицу на Лист 4 и выполните сортировку поданным столбцов, выделив диапазон ячеек В : • уровень 1 — плотность населения (по возрастанию • уровень 2 — страна (от А до Я • уровень 3 — население (по убыванию) Главная Редактирование/Сортировка и фильтр/Настраиваемая сортировка Задание 4. Фильтрация (выборка) данных с помощью автофильтра Фильтрация (выборка) данных позволяет отобразить в таблице только те строки, содержимое ячеек которых отвечает заданному условию (или нескольким условиям. Для применения автофильтра необходимо выделить любую клетку внутри фильтруемой таблицы и обратиться к вкладке Главная/Редактирование/Сортировка и фильтр/Фильтр. После обращения в заголовке таблицы должны появиться кнопки для раскрытия списков. Нажатие любой кнопки приводит к раскрытию списка элементов и меню фильтров соответствующего столбца таблицы. Выбранный элемент является критерием фильтрации. Строки таблицы, в которых элементы столбца не совпадают с критерием, будут скрыты, причем за совпавшими сохраняются их прежние порядковые номера. Выбор второго критерия в другом списке приведет к дополнительной фильтрации записей и т.д. Скопируйте таблицу с листа Таблица 1», вставьте три экземпляра на Листы 5, 6 и 7. Произведите фильтрацию записей таблицы на Листах 5—7 согласно следующим критериям • Лист 5 — страны с населением меньше 150 млн. человек • Лист 6 — выберите страны с плотностью населения от 100 до 300 чел/км 2 ; • Лист 7 — страны, население которых составляет более 2% от всего населения Земли. Сохраните работу. Задания для самостоятельной работы 1. На Листы 8, 9 и 10 скопируйте таблицу из документа «Lab-l.xls» Итоги сессии, дополните таблицу необходимыми столбцами Пол, Сумма баллов, Средний балл и выполните вычисления с помощью формул. 2. На Листе 8 произведите сортировку данных • уровень 1 — группа (по возрастанию • уровень 2 — Ф.И.О. (от А до Я • уровень 3 — средний балл (по убыванию. 3. На Листе 9 произведите фильтрацию записей таблицы — выберите студентов со средним баллом от 1 до 3,5. 4. На Листе 10 произведите фильтрацию записей таблицы, выбрав всех девочек на факультете. Сохраните работу. Контрольные вопросы 1. Список функций кнопки Автосумма. 2. Правила редактирования формул. 3. Что такое относительный адрес ив каких случаях он используется 4. Что такое абсолютный адрес ив каких случаях он используется 5. Что такое смешанный адрес ив каких случаях он используется 6. Как создать точную копию отформатированной таблицы с сохранением оформления 7. Как выполнить сортировку по нескольким уровням 8. Что такое автофильтр? 9. Как установить критерии фильтрации 10. Что отображает таблица, к которой применен автофильтр? 11. Что происходит с табличными данными при отмене автофильтра? 12. Как отменить автофильтр? |