ЛАБОРАТОРНАЯ РАБОТА. СПИСКИ MS EXCEL

ЛАБОРАТОРНАЯ РАБОТА. СПИСКИ MS EXCEL

Цель работы: Изучение возможностей пакета MS Excel при работе со списками. Приобретение навыков создания и обработки списков.

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

Список состоит из трех основных структурных элементов (рис. 1):

· Записи - предметная строка таблицы, которая содержит данные об одном объекте. Каждая запись должна содержать полное описание конкретного экземпляра данных.

· Поля - это отдельные категории (столбцы), по которым упорядочена информации в списке (отдельный столбец - отдельная категория). В поле содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.

· Описание полей - первая строка заголовков. Заголовки - это метки соответствующих полей. Excel использует их при сортировке, поиске, выдаче отчетов по спискам. Рекомендуется выделять заглавную строку, отформатировав ее иначе, чем остальные данные. Не следует вставлять пустую строку между заглавной строкой и прочими строками списка, т.к. некоторые инструменты, например, автофильтр корректно работают только с непрерывными диапазонами.

Рис. 1

Задания:

Задание 1. Создать таблицу реализации картриджей для принтеров со следующими полями:

· Наименование товара.

· Тип.

· Дата.

· Поставщик.

· Закупочная цена, руб.

· Отпускная цена, руб. (наценка 30% от закупочной цены).

· Транспортные расходы, руб/шт. (1% от отпускной цены).

· Количество, шт.

· Прибыль, руб. {(Отпускная цена – Закупочная цена – Удельные транспортные расходы)× Количество}

Ввести данные для 3 поставщиков и 5 типов картриджей.

Задание 2. Используя команду автоформат оформить таблицу в удобном для пользователя виде.

Задание 3. Отсортировать данные в алфавитном порядке по Поставщику, затем по Типу картриджа, после - по Количеству проданных картриджей в порядке убывания.

Задание 4. Используя «автофильтр» показать только те картриджи Xerox, у которых Количество продаж меньше 15 (не пользуются спросом).

Задание 5. Используя «Расширенный фильтр» показать только те картриджи, у которых закупочная цена меньше 1500 руб., а количество продаж больше среднего значения. Результат скопировать на новый лист.

Задание 6. Подвести итоги по поставщикам картриджей по количеству проданных картриджей и по прибыли.

Задание 1: Создание списка

1. Открыть MS Excel. Сохранить новую рабочую книгу под именем Список в папке Мои документы.

2. Сформировать строку описания полей на Лист1 (рис. 2).

Рис. 2

3. С помощью кнопок панелей Выравнивание и Шрифт (вкладка Главная) назначить формат по образцу (рис. 3).

Рис. 3

4. Заполнить двадцать две записи таблицы по полям Наименование товара, Тип, Дата, Поставщик, используя маркер автозаполнения (рис. 4).

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

Рис. 4

5. Заполнить поле Закупочная цена, руб., используя алгоритм:

a) Диапазону назначить формат Денежный с точностью до сотых долей (рис. 5).

Рис. 5

b) В ячейку Е2 ввести формулу =СЛУЧМЕЖДУ(800; 3000) (при отсутствии функции СЛУЧМЕЖДУ набрать формулу =ОКРУГЛ(СЛЧИС()*2200+800; 0)) и скопировать ее в нижележащие ячейки с помощью маркера автозаполнения (двойной щелчок ЛКМ по маркеру автозаполнения).

ВАЖНО: Функция СЛЧИС() возвращает равномерно распределенное псевдослучайное вещественное число, большее либо равное 0 и меньшее 1. Функция СЛУЧМЕЖДУ(нижн_граница; верхн_граница) возвращает псевдослучайное равномерно распределенное число между двумя заданными числами. При каждом вычислении рабочего листа возвращается новое случайное число. Поэтому необходимо заменить формулу полученным значением, как указано ниже. Значения, полученные в рассматриваемом примере, не будут совпадать со значениями студента.

c) Скопировать диапазон Е2: Е23 в буфер и вставить в ячейку Е2 с помощью инструмента Специальная вставка и флага Значения (рис. 6, рис. 7).

Рис. 6

Рис. 7

6. Заполнить поле Отпускная цена, руб. с помощью формулы =ОКРВВЕРХ(E2*130%; 1). Формулу скопировать в нижележащие ячейки с помощью маркера автозаполнения (двойной щелчок ЛКМ по маркеру автозаполнения). Назначить на значения формат Денежный с точностью до сотых долей.

7. Заполнить поле Транспортные расходы, руб/шт.:

a) В ячейку G2 ввести формулу =ЦЕЛОЕ(F2*1%) и скопировать ее в нижележащие ячейки с помощью маркера автозаполнения (двойной щелчок ЛКМ по маркеру автозаполнения).

b) Выделить диапазон G2: G23.

c) Вызвать диалоговое окно Формат ячеек. На вкладке Число выбрать пункт (все форматы). В списке типов выбрать формат 0, а в поле ввода Тип набрать 0[$ руб/шт] (рис. 8).

Рис. 8

8. Заполнить поле Количество, шт., используя алгоритм:

a) Диапазону назначить пользовательский формат шт. с помощью пункта (все форматы) инструмента Формат ячеек.

b) В ячейку H2 ввести формулу =СЛУЧМЕЖДУ(5; 25) (при отсутствии функции СЛУЧМЕЖДУ набрать формулу =ОКРУГЛ(СЛЧИС()*20+5; 0)) и скопировать ее в нижележащие ячейки с помощью маркера автозаполнения (двойной щелчок ЛКМ по маркеру автозаполнения).

c) Скопировать диапазон H2: H23 в буфер и вставить в ячейку H2 с помощью инструмента Специальная вставка и флага Значения.

9. Заполнить поле Прибыль, руб. по формуле =(F2-E2-G2)*H2 {(Отпускная цена – Закупочная цена – Удельные транспортные расходы)·Количество}.

Формулу скопировать в нижележащие ячейки с помощью маркера автозаполнения (двойной щелчок ЛКМ по маркеру автозаполнения). Диапазону назначить формат Денежный с точностью до сотых долей.

Выполнить процедуру сохранения результатов для отчета:

a) Задать масштаб листа и ширины столбцов так, чтобы весь список отображался на экране.

b) Нажать клавишу PrintScreen (PrtScr) на клавиатуре.

c) Открыть новый документ MS Word.

d) Выполнить команду Вставить из буфера.

e) Выполнить обрезку картинки до необходимых размеров с помощью команды Обрезка .

Задание 2: Форматирование списка

MS Excel 2003. Применение автоформата:

Автоматические форматы Excel - это заранее определенные сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки.

1. Выделить диапазон ячеек, которые необходимо форматировать (можно просто поместить курсор внутрь таблицы данных).

2. В меню Формат выберите команду Автоформат.

3. В окне диалога Автоформат нажмите кнопку Параметры, чтобы отобразить область Изменить (рис. 9).

Рис. 9

4. Выбрать подходящий автоформат (например, Объемный 2). В области Изменить снять флаги формат чисел, шрифт, выравнивание и нажать кнопку Ок (рис. 10).

Рис. 10

Задание 4: Фильтрация записей списка (найти картриджи Xerox, которые не пользуются спросом)

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

1. Поместить курсор в любую ячейку списка. Открыть вкладку Данные и щелкнуть на кнопке Фильтр. Справа от названия каждого столбца появятся небольшие стрелки, указывающие вниз. (Если еще раз щелкнуть на кнопке Фильтр, то эти стрелки исчезнут.) Они указывают, что функция Автофильтр программы MS Excel включена (рис. 14).

Рис. 14

2. Щелкнуть на стрелке, расположенной рядом с полем Тип, чтобы отобразить раскрывающийся список. Программа анализирует все содержимое столбца и вводит его в виде списка, где каждый элемент столбца отмечен флажком. В этом списке сбросить все флажки, кроме Xerox. Нажать OK.

3. Щелкнуть на стрелке, расположенной рядом с полем Количество. В раскрывающемся списке фильтра выбрать команду Числовые фильтры. Откроется подменю с доступными параметрами. В этом подменю выбрать условия для фильтрации данных столбца – меньше.

4. В окне пользовательского фильтра в поле ввода задать фильтр меньше 15. Нажать ОК.

Задание 5: Использование расширенного фильтра для дополнительной фильтрации записей списка (найти картриджи, у которых закупочная цена меньше 1500 руб., а количество продаж больше среднего значения)

1. На свободную зону рабочего листа со списком скопировать имена полей Закупочная цена, руб. и Количество, шт. Ввести дополнительное поле условия – среднее арифметическое.

2. Заполнить условия (рис. 15).

Закупочная цена, руб.< 1500 И Количество, шт.> среднее значение

Рис. 15

3. В окне открытого листа выделить диапазон ячеек списка.

2. Перейти к вкладке Данные и в группе Сортировка и фильтрация выбрать пункт Дополнительно.

3. В окне Расширенный фильтр в группе Обработка выбрать Копировать результат в другое место.

4. В графе Исходный диапазон проверить ссылку на диапазон списка.

5. В графе Диапазон условий выбрать в книге диапазон условий.

6. В графе Поместить результат в диапазон выбрать ячейку начала отфильтрованного списка на свободной зоне (рис. 16).

Рис. 16

7. Закройте окно кнопкой ОК.

Задание 6: Подведение итогов по поставщикам картриджей по количеству проданных картриджей и по прибыли.

1. Выполнить сортировку списка по полю Поставщик. 2. Выбрать на вкладке Данные команду Промежуточные итоги. 3. В окне Промежуточные итоги в графе При каждом изменении в: выбрать поле Поставщик; в графе Операция выбрать Сумма; в графе Добавить итоги по: поставить флаги у полей Количество и Прибыль (рис. 19). 4. Закрыть окно Промежуточные итоги кнопкой ОК. В результате внешний вид таблицы изменится, справа появятся символы для отображения структуры (рис. 20). На рис. 21 структура второго уровня свернута, что позволяет увидеть окончательный результат.

Рис. 19

Рис. 20

Рис. 21