Файл: Методические рекомендации по выполнению лабораторно практических работ по теме Обработка табличной информации.pdf

- 40 -
3. Значения, выделенные серым цветом записать в сценарий под именем «БАЗА»
4. Используя средство «Подбор параметра», вычислить: a. При какой минимальной цене следует продавать сахар чтобы не получить убытков (т.е. прибыль после кредита равна 0). b. При какой максимальной цене следует покупать сахар чтобы не получить убытков. c. По какой цене следует продавать сахар, чтобы получить прибыль 15000. d. При каком проценте по кредиту прибыль будет 5000.
5. Важно! Каждый результат вычислений записать как отдельный сценарий с
именем «Задача …» (а,b,c,d вместо …). ПОСЛЕ ВЫПОЛНЕНИЯ КАЖДОГО
ИЗ ЗАДАНИЙ ВОЗВРАЩАТЬСЯ К БАЗОВЫМ ЗНАЧЕНИЯМ (в диспетчере
сценариев D-Click на «база»)
6. Результат представить в виде отчета по сценарию (аналогично ниже следующему).
Структура сценария
БАЗА задача а
Задача b
Задача c
Задача d
Изменяемые:
$B$4 2025 2025 2 052,54 2025 2025
$B$5 105 105 105 105 105
$B$8 10 10 10 10 10
$B$10 250 250 250 250 250
$B$14 12,50%
12,50%
12,50%
12,50%
11,35%
$B$18 2600 2 565,32 2600 2 727,66 2600
Результат:
$B$22 3 204,18 0,00 0,00 15 000,00 5 000,00 7. Время выполнения - 60 минут отставания, на 15 минут - минус балл.

- 41 -
ЛАБОРАТОРНАЯ РАБОТА №7.
«СВОДНЫЕ ТАБЛИЦЫ, ИТОГИ»
Цель: Научиться создавать сводные таблицы и добавлять в таблицу итоги.
Задачи:
1. Изучить способ создание сводных таблиц
2. Изучить средство «сортировка»
3. Изучить средство «итоги»
Теория.
Макет:
Область построения:

- 42 -
СПОСОБ СОЗДАНИЯ СВОДНОЙ ТАБЛИЦЫ:
1. Выделите ячейку таблицы с исходными данными
2. Дабавте сводную таблицу в MS Excel 2003 – Меню ДанныеСводная таблица; MS
Excel 2010 – Вставка Сводная таблица
3. Для MS Excel 2003 откроется мастер построения сводных таблиц (можно сразу нажать «Готово» без уточняющих шагов, тогда результат будет как в MS Excel
2010), для MS Excel 2010 сразу открывается область построения и список полей сводной таблицы
4. Для MS Excel 2003 в мастере на первых 2-х шагах выбирается источники данных, на 3-м шаге - выбор позиции вывода сводной таблицы, настройка параметров и макета таблицы. работа с макетом аналогична работе с областью построения как показано на рисунке выше.
5. Далее из списка полей в область построения перетягиваются поля, необходимые для отображения.
6. ВАЖНО! Одну и туже задачу можно решить 2-мя и более способами,
например, меняя положения строк и столбцов, по этому при построении
сводных таблиц следует учитывать принцип минимизации. Т.е. площадь
получаемой таблицы должна быть минимальной и удобочитаемой.
Каждое поле сводной таблицы можно настраивать
1. Для поля фильтра (поля страниц), полей строк и столбцов – настройка итогов, т.е. какие вычисления производить по изменению в значении поля:
Итоги «автоматические» формируются из содержания поля данных (центр макета)

- 43 -
2. Для поля данных (центр макета), т.е. что делать с данными одного вида, когда есть совпадения по строке и столбцу:
СРЕДСТВО «СОРТИРОВКА»
При использовании средства сортировки данные должны быть выделены или быть частью базы данных. Чтоб преобразовать таблицу в базу данных выберете пункт меню
Данные
Форма(2003). При выборе поля для сортировки переставляются строки всей таблицы.
MS
Excel
2003
– пункт меню
ДанныеСортировка. Число полей по которым можно сортировать ограничено 3-мя.
Для MS Excel 2010 – вкладка Данные
Сортировка. Количество полей не ограничено

- 44 -
СРЕДСТВО ИТОГИ
Средство итоги для обоих офисов выглядит идентично.
Прежде чем использовать средство итоги следует
отсортировать исходные данные по полю (столбцу) на каждое изменение которого будет выводиться итог.
В случае если сортировать необходимо по нескольким полям, то для поля, по которому строиться итог сортировать надо в последнюю очередь.
Доступ к средству итоги в MS Excel 2003 через пункт меню данные Итоги…, для MS Excel 2010 Вкладка
ДанныеПромежуточные итоги.
Результат выполнения
Знак «+» означает раскрыть данные по итогам, «-» - скрыть данные
Цифры 1 2 3 означают соответственно: «Скрыть все данные кроме общего итога», «скрыть данные кроме итогов» и «отображать все данные»

- 45 -
Ход работы.
1. Для выполнения данной работы необходимо сначала ввести исходные данные
(время на ввод данных не учитывается). Если исходные данные предоставлены, то сохранить их копию в свою папку.
2. Дальнейшая работа выполняется в течение 40 минут отставание на 15 минут минус балл.
3. Используя средства «Сводные таблицы» вывести данные о прохождении теста с названием “Основы-ПК\Часть-5-«MS Excel»” с выводом среднего балла по каждому студенту.
4. Используя средства «Сводные таблицы» вывести отчет по прохождению всех тестов пройденных с использованием типа «Экзамен» с выводом среднего балла по каждому тесту.
5. Вывести отчет по прохождению всех тестов конкретно взятым учеником (одним) с промежуточными итогами – средний балл и типом теста - «экзамен».
6. Вывести таблицу по каждому ученику и каждому тесту с общей суммой затраченного времени по каждому тесту.
Для каждого следующего задания создавать копию листа с исходными
данными.
7. Получить промежуточные итоги по каждому тестируемому с выводом средней оценки по каждому тесту и по изменению типа теста.
8. Вывести промежуточные итоги по потраченному времени на все тесты каждым тестируемым.
9. Вывести промежуточные итоги по каждому тесту и изменению типа теста суммируя все время и вес всех вопросов.

- 46 -

- 47 -
ЛАБОРАТОРНАЯ РАБОТА №8.
«УСЛОВНОЕ ФОРМАТИРОВАНИЕ, ФИЛЬТРЫ»
Цель: Научить помечать или скрывать данные по условию
Задачи:
1. Изучить метод условного форматирования «по условию»;
2. Изучить метод условного форматирования «по формуле»;
3. Ознакомиться с дополнительными возможностями условного форматирования в
MS Excel 2010 («Текст содержит…», «Дата», «Повторяющиеся элементы»,
«Гистограммы» «Цветовые шкалы», «набор значков»).
4. Изучить средство «Автофильтр»
5. Изучить средство «Расширенный фильтр»
Теория.
УСЛОВНОЕ ФОРМАТИРОВАНИЕ «ПО УСЛОВИЮ»
Условное форматирование по условию сравнивает значение ячейки с определенным условием.
Для MS Excel 2003 доступно через пункт меню ФорматУсловное форматирование:
Для MS Excel 2003 ячейка может содержать только 3 условия для форматирования.

- 48 -
Для MS Excel 2010 доступно через вкладку ГлавнаяУсловное форматирование правило выделения ячеек или ГлавнаяУсловное форматированиеСоздать правило:
Быстрый доступ к типичным условиям доступен через пункты меню:
Форматирование по условию

- 49 -
В отличие от MS Excel 2003, число условий в новом офисе не ограничено. Также, в новом офисе несколько условий могут накладываться при отображении ячейки.
Пример 3-х различных условных форматов, отображаемых одновременно.
Условное форматирование полезно тем, что настроев условие для одной ячейки можно скопировать форматы на диапазон ячеек воспользовавшись специальной вставкой
(контекстное менюспециальная вставкаформаты).
УСЛОВНОЕ ФОРМАТИРОВАНИЕ «ПО ФОРМУЛЕ»
ПРИМЕЧАНИЕ! Условное форматирование по формуле удобно предварительно подготовить:
1. Выбрать дополнительный столбец(строку) для проверки условий
2. Ввести условное выражение (см. лаб.-раб. №4) в дополнительный столбец и убедится, что стоит значение ИСТИНА напротив необходимых ячеек
3. Скопировать формулу из дополнительной ячейки в поле условий при создании условного форматирования
ВАЖНО! В поле условий не работает стандартная навигация, т.е. стрелки не
перемещают курсор, а выделяют соседние ячейки (из-за чего копирование
условия очевидно удобнее, чем вписывание его самостоятельно).

- 50 -
СРЕДСТВО «АВТОФИЛЬТР»
Доступно для MS Excel 2003 через пункт меню ДанныеФильтрАвтофильтр.
Для MS Excel 2010 через вкладку ДанныеФильтр или ГлавнаяСортировка и фильтрфильтр.
В результате включения автофильтра заголовки данных становятся текстовыми полями с выпадающим списком
Фильтр можно выбрать по условиям (только 2-м).
В MS Excel 2010 существует возможность фильтровать по цвету(маркерам) ячейки, что позволит комбинировать расширенные возможности условного форматирования с автофильтром.
СРЕДСТВО «РАСШИРЕННЫЙ ФИЛЬТР»
Доступно для MS Excel 2003 через пункт меню ДанныеФильтрРасширенный фильтр; Для MS Excel 2010 через вкладку ДанныеДополнительно
Алгоритм работы расширенного фильтра:
Выбор одного из значений, расположенных в выбранном поле (записи с другими значениями будут скрыты).
Задать условия отображения
Отображать все значения (т.е. отключить фильтр)
Выпадающие списки автофильтров

- 51 -
1. Добавьте минимум 3 строки перед таблицей исходных данных
2. Скопируйте заголовок таблицы и одну строку данных в созданные пустые ячейки выше.
3. Удалите строку с данными в дополнительной таблице (это необходимо, чтобы соблюдалась структура данных).
4. Напротив заголовков тех столбцов, которых касаются необходимые условия вписать условия способом, аналогичным определению критериев (см. лаб.раб. №4)
5. Если условия связаны условием «И» (И белые И красные и т.п.) то они должны находиться в одной сроке данных, если условия касаются одного и того же поля, то скопировать заголовок поля в конец дополнительной таблицы и впишите туда условие.
Заголовок 1
ЦВЕТ
Заголовок 3
Заголовок 4
ЦВЕТ
Белый
Красный
6. Если условия связаны условием «ИЛИ» (ИЛИ белые ИЛИ красные и т.п.) то они должны находиться в разных сроках данных
Заголовок 1
ЦВЕТ
Заголовок 3
Заголовок 4
Белый
Красный
7. Выделите любую ячейку основной
таблицы данных и откройте диалог
расширенного фильтра. Если все
сделано верно, то основная таблица
данных будет выделена в разделе
«исходный диапазон»
8. Выделите дополнительную таблицу включая заголовки в раздел «Диапазон условий» и нажмите ОК.

- 52 -
Ход работы.
Для выполнения нижеследующих заданий используются исходные данные из
предыдущей лабораторной работы, каждое задание требует копирования исходных
данных на новый лист.
Условное форматирование
1. Пометить все ячейки в столбце «Тип теста» отличные от «экзамен»
2. Пометить ячейки в столбце «оценка» соответственно: от 0 до 4 – красным; от 4 до
10 – желтым; от 10 до 11 – синим;
3. Пометить все строки таблицы, где в столбце «Вес правильных ответов» значение меньше 20 (используется формула)
Автофильтр
4. Отобразить все строки таблицы, где в столбце «название теста» стоит «охрана труда», а в столбце «тип теста» - экзамен.
5. Отобразить все строки таблицы, где в столбце «стохастический вес» значения выше 8.
6. Отобразить первые 10 самых медленных ответчиков

- 53 -
ЛАБОРАТОРНАЯ РАБОТА №9.
«НАСТРОЙКА MS EXCEL»
Цель: Научиться перенастраивать Excel для пользовательских задач
Задачи:
1. Добавление операций на панель задач
2. Настройка сочетаний клавиш на операцию
3. Защита листа
4. Установка заголовков на листе
5. Свойства печати
Теория.
ДОБАВЛЕНИЕ ОПЕРАЦИЙ НА ПАНЕЛЬ ЗАДАЧ
Вывод дополнительных операций на панель задач для MS Office 2003 доступны через пункт меню ВидПанель инструментовнастойка…. Далее выбираем категорию, команду и переносим пиктограмму команды на любую панель (на примере перенесено
«создание нового листа»)
Для вывод команд на ленту в MS Excel 2010: Вкладка ФайлПараметрыНастройки ленты. Выбрать справа ленту, на которую собираетесь поместить команду, выбрать

- 54 - группу (должна быть создана пользователем), слева в списке команд выбрать команду и нажать «Добавить > > »
НАСТРОЙКА СОЧЕТАНИЙ КЛАВИШ
Создать сочетание клавиш в обоих офисах, можно только записав макрос и назначив ему горячую клавишу. В MS Excel 2003 – пункт меню сервис макросыначать запись, в
MS Excel 2010 – вкладка ВидмакросыЗапись макроса. Далее открываются схожие диалоги:
Первым знаком имени макроса должна быть буква.
Остальные знаки могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не допускаются пробелы; в качестве разделителей слов следует использовать знаки подчеркивания.
В сочетании клавиш может быть только буква
Если этот макрос требуется всегда при работе в MS
Excel, выберите вариант Личная книга макросов

- 55 -
ВАЖНО! Все действия, записанные макросом, будут выполняться и при
следующем выводе. Поэтому следует заранее выполнить те действия, которые не
должны быть записаны. Например, для макроса объединения ячеек по горизонтали
заранее вынесите команду на панель инструментов (в 2010 офисе она уже вынесена)
и выделите часть ячеек.
После выбора настроек макроса начинается запись. Чтобы закончить запись, нажимается кнопка:
Для 2003:
Для 2010
ЗАЩИТА ЛИСТА
Защита листа в обоих офисах аналогичны:
1. Для MS Excel 2003 – Сервис ЗащитаЗащитить лист
2. Для MS Excel 2010 – РецензированиеЗащитить лист
ВАЖНО! Защита листа распространяется на те ячейки, для которых в формате
ячейки на вкладке «Защита» стоит «Защищать ячейку»
Не убирайте маркер с этого пункта (иначе на листе работать ни с одной ячейкой будет нельзя)