ЛАБОРАТОРНАЯ РАБОТА 4 Тема- ОРГАНИЗАЦИЯ РАБОЧИХ КНИГ
Работа добавлена на сайт samzan.net:
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
ЛАБОРАТОРНАЯ РАБОТА №4
Тема: ОРГАНИЗАЦИЯ РАБОЧИХ КНИГ.
СОРТИРОВКА И ОБРАБОТКА СПИСКОВ
1. ЦЕЛЬ РАБОТЫ
Научиться:
- копировать и перемещать листы рабочей книги;
- использовать фильтрацию для просмотра данных;
- сортировать данные по определенным критериям.
2. ОБЪЁМ ЛАБОРАТОРНОЙ РАБОТЫ И ПОРЯДОК
ЕЁ ВЫПОЛНЕНИЯ
Данная лабораторная работа выполняется в течение двух часов. Выполнение работы идет одновременно с изучением теоретической части. Для закрепления материала необходимо выполнить упражнения для самостоятельной работы и сохранить в указанной преподавателем папке.
3. ТЕОРЕТИЧЕСКАЯ ЧАСТЬ.
ТЕХНОЛОГИЯ ВЫПОЛНЕНИЯ РАБОТЫ
Упражнение 1
- Создайте новую рабочую книгу. В этой рабочей книге присвойте первому листу имя Факсы и введите данные, изображенные на рисунке 3.1.
- Введите сначала данные столбцов Цена и Количество, а значения в столбцах Стоимость и Сумма получите с помощью формул: Стоимость = Цена*1,3 и Сумма = Количество*Цена.
- Примените к таблице автоформат Список 3. Установите для таблицы размер шрифта 16.
- Переименуйте лист на Факсы.
- Сохраните рабочую книгу в указанное преподавателем место с именем Оргтехника.
- Не закрывая книгу Оргтехника, создайте новую книгу и переименуйте в ней первый лист на Ксероксы.
- Заполните лист Ксероксы данными с рисунка 3.2.
- Оформите созданную таблицу также, как и таблицу Факсы.
- Сохраните созданную книгу под именем Урок4. Таким образом, вы создали два новых файла рабочих книг Оргтехника и Урок4.
3.1. Управление рабочими книгами
При создании рабочей книги в Microsoft Excel задавать сразу точный порядок страниц не обязательно. Страницы рабочей книги всегда можно позднее добавить или удалить, скопировать или переместить.
Рисунок 3.1 – Данные по факсам
Рисунок 3.2 – Данные по ксероксам
3.1.1. Копирование рабочих листов
Копируя лист, вы создаете абсолютно идентичную копию, которая может быть помешена в эту же или в другую рабочую книгу.
Упражнение 2
В этом упражнении вы скопируете лист Факсы в файл Урок4.
- Выполните команду Вид – Окно – Перейти в другое окно. В меню кнопки Перейти в другое окно щелкните на имени Оргтехника. Так вы перейдете в рабочую книгу Оргтехника.
- На вкладку Моя группу добавьте кнопку Переместить или скопировать лист. Вкладка Моя группа будет выглядеть так, как показано на рисунке 3.3.
Рисунок 3.3 – Вкладка Моя группа
- Убедитесь, что в файле Оргтехника активным является лист Факсы, и выполните команду Моя группа – Переместить или скопировать лист, или в контекстном меню листа выберите команду Переместить или скопировать... Откроется диалоговое окно Переместить или скопировать...
- Установите флажок в поле Создать копию. Если этот флажок не установлен, то текущий лист будет не скопирован, а перемещен в рабочую книгу Урок4 и удален из файла Оргтехника.
- В поле Переместить выбранные листы в книгу: выберите книгу Урок4.xlsx. Строка Урок4.xlsx выделена (рисунок 3.4).
- В окне списка перед листом: выделите строку Ксероксы. Так вы поместите копию листа Факсы слева от листа Ксероксы рабочей книги Урок4.
- Щелкните на кнопке ОК. Теперь копия листа Факсы помещена перед листом Ксероксы рабочей книги Урок4.
Рисунок 3.4 – Диалоговое окно Переместить или скопировать
Упражнение 3
В этом упражнении вы скопируете лист в пределах рабочей книги.
- Сделайте активной рабочую книгу Урок4.
- Щелкните на ярлычке листа Ксероксы, а затем нажмите и не отпускайте <Ctrl>. Нажмите левую кнопку мыши. Указатель мыши примет форму маленькою листка со знаком плюс.
- Не отпуская левую кнопку мыши, передвигайте указатель мыши, пока он не окажется слева от ярлычка листа Факсы. При перемещении указателя мыши между ярлычками листов появляется маленький треугольник, который указывает место, куда будет помещена копия листа, если вы отпустите кнопку мыши. Этот указатель должен быть слева от ярлычка листа Факсы.
- Отпустите кнопку мыши, а затем <Ctrl>. Лист Ксероксы скопирован и помещен слева от листа Факсы. При этом ему автоматически присвоено имя Ксероксы (2).
- Щелкните дважды на ярлычке листа Ксероксы (2). Имя листа выделится. Введите Полный ассортимент и нажмите <Enter>. Листу будет присвоено новое имя.
- Выделите D1, наберите Полный ассортимент и нажмите <Enter>.
- Выделите ячейку C8 и наберите Информация о товарах.
- Щелкните на ярлычке листа Факсы. Выделите диапазон ячеек С10:Н19.
- Выполните команду Главная – Буфер обмена – Копировать.
- Щелкните на ярлычке листа Полный ассортимент. Сделайте активной ячейку С20. Выполните команду Главная – Буфер обмена – Копировать – Вставить. Вы объединили содержимое двух таблиц на рабочем листе Полный ассортимент (рисунок 3.5).
Рисунок 3.5 – Рабочий лист Полный ассортимент
3.1.2. Перемещение рабочих листов
Точно так же, как вы копируете листы в пределах одной книги или в другие книги, можно перемещать рабочие листы с одного места на другое, не создавая копий. Для этого в диалоговом окне Переместить или скопировать... не нужно устанавливать флажок в поле Создать копию.
3.2. Закрепление областей на листе
При прокручивании листа, содержащего большие таблицы, заголовки строк и столбцов исчезают из вида. Так легко потерять ориентацию на листе. В Microsoft Excel эта проблема решается с помощью такой возможности, как закрепление областей.
Чтобы закрепить области рабочего листа, необходимо использовать команду Вид – Окно – Закрепить области – Закрепить области. Закрепленные строки и столбцы останутся видимыми во время прокрутки. Для отмены закрепления областей необходимо использовать команду Вид – Окно – Закрепить области – Снять закрепление областей.
Закрепленными (не перемещаются при прокручивании листа) становятся строка выше выделенной ячейки (строки), а столбец левее выделенной ячейки (столбца).
Упражнение 4
В этом упражнении вы научитесь закреплять области на рабочем листе.
- Сделайте активным лист Полный ассортимент рабочей книги Урок4.
- Выделите строку 10. Выполните команду Вид – Окно – Закрепить области – Закрепить области.
- Прокрутите лист. Обратите внимание на заголовки столбцов.
- Отмените закрепление областей, выполнив команду Вид – Окно – Закрепить области – Снять закрепление областей.
3.3. Фильтрация списков
3.3.1. Автофильтр
В Microsoft Excel данные хранятся в виде списка. При работе со списками часто приходится искать определенную информацию, необходимую в данный момент, а также сортировать данные по определенному признаку Microsoft Excel имеет два мощных инструмента для поиска и вывода на экран специфических данных из общего списка – это фильтрация и сортировка. С их помощью легко выбирать нужные данные на рабочем листе.
В Microsoft Excel быстро находить нужную информацию предоставляет возможность команда Автофильтр.
Упражнение 5
В этом упражнении вы научитесь готовить список к фильтрации, выбирать критерии отбора и фильтровать список.
- На листе Полный ассортимент рабочей книги Урок4 в таблицу Информация о товарах перед столбцом Модель вставьте новый столбец под заголовком Товар и заполните его соответствующими названиями вида оргтехники: ксерокс, факс (рисунок 3.6).
- Оформите новое поле в едином с таблицей формате.
- На активном листе выделите ячейку С9.
- Выполните команду Данные – Сортировка и фильтр – Фильтр . В заголовках каждого столбца данных появятся кнопки автофильтра со стрелочками вниз (рисунок 3.7).
- Щелкните на кнопке автофильтра в ячейке Е9. Это заголовок Название. В появившемся окне списка для выбора критерия фильтра необходимо выбрать нужный критерий (ключ) фильтрации. Критерии фильтрации находятся в списке в алфавитном порядке, так как данные в столбце – текстовые. В дополнение к ним имеются еще несколько ключей фильтрации (рисунок 3.8).
Рисунок 3.6 – В таблицу Полный ассортимент добавлено поле Товар
Рисунок 3.7 – Таблица Информация о товарах с Автофильтром
Рисунок 3.8 – Список для выбора критериев фильтра
- Для выбора данных о товаре с названием Персональный в критериях фильтрации снимите флажок (Выделить все) и установите флажок Персональный. Щелкните по кнопке ОК. В таблице останутся только строки с данными о товаре с названием Персональный. Все остальные записи таблицы не удалены, а лишь временно скрыты. Порядковые номера строк в списке теперь не являются последовательными (рисунок 3.9).
Рисунок 3.9 – Фильтр по столбцу Название
Упражнение 6
В этом упражнении вы составите список персональных ксероксов, а затем вернете все записи.
- Щелкните на кнопке автофильтра в ячейке С9.
- В критериях фильтрации снимите флажок (Выделить все) и установите флажок Ксерокс. В таблице остался список персональных ксероксов.
- Чтобы снова вывести на экран полный список, нужно в столбцах, по которым выполнялась фильтрация, щелкнуть по кнопке и установить флажок (Выделить все). Список восстановлен.
Упражнение 7
В этом упражнении вы составите список наиболее доходных видов товаров.
- В столбце Сумма щелкните на кнопке автофильтра, в списке критериев выберите Числовые фильтры – Первые 10...
- Появится диалоговое окно Наложение условия по списку.
- Убедитесь, что параметры окна в группе Вывести установлены как наибольших 10 элементов списка и щелкните на кнопке ОК. В таблице останется список товаров, значения в поле Сумма которых составляет десять наибольших в полном списке величин (рисунок 3.10).
- Чтобы быстро восстановить полный список и отменить автофильтр, щелкните по кнопке на ленте.
Рисунок 3.10 – Фильтр по столбцу Сумма
Иногда требуется найти информацию, удовлетворяющую критериям, не входящим в список Автофильтра.
Упражнение 8
В этом упражнении вы зададите свои критерии поиска данных. Например, вам нужно найти категории товаров, доход от продажи которых не превышает 1 миллиона рублей.
- Выполните команду Данные – Сортировка и фильтр – Фильтр.
- В списке для выбора критериев фильтра столбца Сумма выберите Числовые фильтры – Настраиваемый фильтр…
- На экране появится диалоговое окно Пользовательский автофильтр. Настройте его как показано на рисунке 3.11. В список будут включены записи, в которых содержимое столбца Сумма меньше или равно 1000000.
- Нажмите ОК. Результат сравните с рисунком 3.12.
- Восстановите полный список товаров.
Рисунок 3.11 – Диалоговое окно Пользовательский автофильтр
Рисунок 3.12 – Результат применения Пользовательского автофильтра
Для выбора условия можно использовать операторы, такие как равно (=), больше чем (>), меньше чем (<), а также операторы нестрогих неравенств.
Упражнение 9
В этом упражнении вы составите список ксероксов и факсов с названиями Профессиональный и Профессиональный Плюс.
- Выполните команду Данные – Сортировка и фильтр – Фильтр.
- Щелкните по кнопке автофильтра в столбце Название.
- С помощью Пользовательского автофильтра установите следующие условия фильтрации:
- убедитесь, что в группе Название в верхнем поле операторов находится слово равно;
- щелкните на стрелке соседнего поля и выделите строку Профессиональный;
- включите опцию ИЛИ;
- щелкните на стрелке нижнею поля операторов и выделите равно;
- щелкните на стрелке нижнего поля критериев и выделите строку Профессиональный Плюс (рисунок 3.13).
- Щелкните на кнопке ОК.
- Восстановите полный список товаров.
Рисунок 3.13 – Результат применения Пользовательского автофильтра по двум критериям
3.3.2. Расширенная фильтрация
Часто для обработки списка бывает вполне достаточно автофильтра. Но если необходимо выполнить операцию, выходящую за рамки возможностей автофильтра, то придется прибегнуть к помощи средств расширенной фильтрации. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять больше подготовительных действий.
Прежде чем использовать средство расширенной фильтрации, необходимо задать диапазон критериев. Диапазон критериев – это специально отведенная область рабочего листа, отвечающая определенным требованиям:
- она должна состоять, но крайней мере, из двух строк, в первую из которых необходимо скопировать все или некоторые названия столбцов;
- остальные строки должны содержать критерии фильтрации.
Упражнение 10
В этом упражнении вы выполните расширенный фильтр с данными таблицы Полный ассортимент. Отфильтрованный список должен отвечать следующим требованиям: в поле Товар содержится значение Ксерокс и в поле Название – значение Деловой или в поле Товар содержится значение Факс и при этом значение в поле Цена превышает 3000.
- Укажем диапазон критериев в диапазоне ячеек G2:I4 (рисунок 3.14).
Рисунок 3.14 – Диапазон критериев для расширенного фильтра
Внимание! При заполнении диапазона критериев копируйте название полей и данные из исходной таблицы.
- Чтобы выполнить фильтрацию, выделите любую ячейку таблицы и выполните команду Данные – Сортировка и фильтр – Дополнительно.
- Появится диалоговое окно Расширенный фильтр. Убедитесь, что в поле Исходный диапазон задан диапазон таблицы $C$9:$I$29.
- Щелкните в поле Диапазон условий. Выделите мышью диапазон ячеек G2:I4, в которых задан диапазон критериев. При необходимости диалоговое окно переместите в другое место или сверните на время, чтобы выделить нужный диапазон (рисунок 3.15).
- Щелкните на кнопке ОК. В таблице остались данные, удовлетворяющие критериям отбора. Это пример фильтрации, которая не может быть выполнена с помощью автофильтра (рисунок 3.16).
- Отобразите на экране всю таблицу. Отмените Автофильтр.
Рисунок 3.15 – Расширенный фильтр
Рисунок 3.16 – Результат применения расширенного фильтра
Элементы, которые указываются в диапазоне критериев, могут быть текстовые или числовые. В таблице ниже приведены операторы сравнения, которые могут быть использованы в текстовых или числовых критериях, а также примеры строковых критериев.
|
=
|
Равно
|
|
>
|
Больше
|
|
<
|
Меньше
|
|
>=
|
Больше либо равно
|
|
<=
|
Меньше либо равно
|
|
<>
|
Не равно
|
|
>K
|
Слова, начинающиеся с букв от Л до Я
|
|
<>C
|
Все слова, кроме начинающихся с буквы С
|
|
=«Факс»
|
Все слова Факс
|
|
П*
|
Все слова, начинающиеся с буквы П
|
|
С*С
|
Слова, начинающиеся с буквы С и заканчивающиеся буквой С
|
3.4. Сортировка данных
Прежде чем вывести на экран или напечатать данные списка в определенном порядке, необходимо произвести сортировку данных.
Сортировка – это изменение порядка строк в таблице.
3.4.1. Сортировка по одному критерию
Чтобы быстро отсортировать список в порядке возрастания, переместите табличный курсор в начало столбца (не выделяйте столбец!), по которому нужно выполнить сортировку. Затем щелкните по кнопке (сортировка по возрастанию или в алфавитном порядке) на вкладке Данные.
Кнопка (сортировка по убыванию или в обратном алфавитном порядке) работает таким же образом, но список сортируется в порядке убывания.
Упражнение 11
В этом упражнении вы отсортируете таблицу Информация о товарах по виду товара в обратном алфавитном порядке.
- Выделите ячейку D9.
- Щелкните по кнопке . Список отсортирован.
3.4.2. Сортировка по нескольким критериям
Иногда приходится использовать более одного критерия сортировки. Например, сейчас данные в списке сгруппированы по виду товара, а названия товара расположены в произвольном порядке. Теперь вы можете в группах товаров Факс и Ксерокс произвести сортировку названия товара но возрастанию или по убыванию.
Упражнение 12
В этом упражнении вы проведете сортировку по виду и названию оргтехники.
- Выделите любую ячейку в пределах таблицы.
- Выполните команду Данные – Сортировка и фильтр – Сортировка. На экране появится диалоговое окно Сортировка.
- Выберите в строке Сортировать по в списке Столбец – Товар, в списке Сортировка – Значения и в списке Порядок – От Я до А.
- Щелкните по кнопке Добавить уровень. В диалоговом окне появится новая строка Затем по.
- Выберите в строке Затем по в списке Столбец – Название, в списке Сортировка – Значения и в списке Порядок – От А до Я (рисунок 3.17).
- Щелкните на кнопке ОК. Теперь в таблице группа Факс расположена перед группой Ксерокс, а внутри групп названия отсортированы в алфавитном порядке по возрастанию (рисунок 3.18).
- Сохраните результаты работы и закройте файл.
Рисунок 3.17 – Диалоговое окно Сортировка
Рисунок 3.18 – Результат сортировки по двум критериям
4. УПРАЖНЕНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ
- Создайте новую рабочую книгу с именем Урок4_1.
- На первом листе создайте новую таблицу по продаже ксероксов (рисунок 4.1).
Рисунок 4.1 – Исходная таблица
- Столбец Выручка заполните с помощью формулы Выручка = Количество*Цена.
- Оформите таблицу автоформатом Список3.
- Переименуйте Лист1 на Данные по ксероксам.
- Отсортируйте данные по фамилиям торговых агентов по алфавиту, а затем по номеру филиала.
- С помощью автофильтра выберите из таблицы заказы, доставка по которым будет осуществляться самолетом. Верните все записи.
- Выведите на экран записи 261 филиала и с ценой модели более 4000. Верните все записи.
- Выполните расширенный фильтр по следующим условиям:
|
Филиал
|
Выручка
|
Выручка
|
Доставка
|
|
195
|
>1000
|
<10000
|
АВИА
|
|
261
|
>10000
|
|
Ж/Д
|
- Сохраните файл.
5. КОНТРОЛЬНЫЕ ВОПРОСЫ
- Как скопировать/переместить рабочий лист в пределах одной рабочей книги?
- Как скопировать/переместить рабочий лист из одной рабочей книги в другую?
- Как закрепить область ячеек на рабочем листе?
- Объясните назначение каждого критерия фильтрации.
- Какие подготовительные действия необходимо выполнить при использовании расширенного фильтра?
- Как выполнить сортировку по нескольким полям?