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


Лабораторная работа № 3Выполнение расчетов, построение диаграмм,
прогнозирование в электронных таблицах MS EXCEL.Цель работы
Изучение основных приемов работы с таблицами, формулами. Получение практических навыков по созданию, редактированию и оформлению таблиц, расчетов, построения диаграмм в электронных таблицах. (MS EXCEL)
Подготовка к работе
По указанной литературе изучить приёмы работы с формулами, функциями, форматирование и редактирование данных.
Задания на выполнение
I Ввод и редактирование данных в таблице.
Цель: Изучить базовые команды работы с данными таблицы ("Копировать", "Вырезать", "Вставить", "Очистить"). Ознакомиться с приемами конструирования таблиц (команды "Удаление" и "Вставка").
Создать книгу и сохранить ее под именем Группа _ фамилия _
Лист 1 переименовать в «Магазин».
Создать таблицу, как показано на рис. 3.1.

Рис. 3.1 – Таблица – «Магазины»
Просчитать следующие поля: «Цена одного телефона», «Цена одного телефона с налогом», «Сумма с продаж включая налог», применив необходимые формулы. Примените команды копирования в необходимом диапазоне. Просмотрите, как модифицируются адреса ячеек при их копировании. Для этого необходимо перейти к окну «Зависимости формул» «Показать формулы» (Рис.3.2).

Рис.3.2 – Вкладка формулы
Модифицировать созданную таблицу
Скопировать таблицу на лист 2. Переименовать лист 2 в «Модификацию».
В таблице название столбца «Квартал» поменяем на «Месяц» и заполним его автозаполнением. Добавим поле «Всего» и просчитаем его.

Рис.3.3 – Модифицированная таблица
В новой таблице (см. Рис.3.3) вставить 2 строчки с месяцами ноябрь, декабрь. Заполнить их данными самостоятельно. Для клеток B3, B4, В6 создать примечания, поясняющие, какие именно изделия (размер, сорт, цвет) приведены в таблице. Просмотреть, каким образом отмечаются ячейки, имеющие примечания.
Просмотреть логические связи между ячейками.
Установить курсор в ячейку В15. Просмотреть, какие ячейки "зависят" от нее. Для этого обратиться к меню " Формулы Зависимости формул ". Сделать активной ячейку 4 и с помощью этого же меню просмотреть, какие ячейки "влияют" на нее (рис.3.4).

Рис. 3.4 – Установка зависимостей между формулами
Отобразить Панель Зависимостей (рис. 3.4) через меню "Формулы Зависимости формул", познакомиться с ее составом и провести исследование логических связей между ячейками.
II Форматирование текстовых и числовых данных
Цель: Знакомство с методами оформления таблиц.
Форматирование числовых данных.
Просмотреть все варианты форматирования чисел, предлагаемые табличным процессором Excel , используя мню "Формат ячейки"-"Число".
Создадим лист 3, переименуем его в «форматирование».
На лист 3 копируем таблицу со 2 листа полученную с помощью модификации. Добавить столбцы с названием «Магазин» и «Доля», удалить столбцы «Деньги руб.» и «Цена одного телефона с налогом» и Поля «Налог с продажи», «Сумма с продаж включая налог» (рис.3.5).
Клетки D16 и E16 заполнить формулами суммирования вышестоящих данных. В столбце F – определить долю каждого магазина в общей сумме продаж: отношение значений столбца D ("Продано шт.") к общей сумме и отформатировать (формат числовой до 2 знаков после запятой) см. рис.3.5.

Рис.3.5 – Исходная таблица
Ниже на этом же листе копируем таблицу.
Отформатируем таблицу с помощью команды «Форматировать как таблицу». Перед выполнением Автоформатирования выделить всю область данных и вызвать меню "Форматировать как таблицу". Просмотреть примеры форматирования, выбрать любой формат. В появившемся окне убрать галочку в команде «Таблица с заголовком». У вас получится таблица с верхней строчкой с заголовком «Столбец 1» и т.д. рис. 3.6

Рис.3.6 – Таблица после форматирования.
Для того чтобы эта строка не отображалась, следует убрать галочку из «Строка заголовка» рис.3.7, а заголовок Отчет по продажам подправить вручную.

Рис.3.7
Полученную таблицу отформатировать, применив Команду «Стили ячеек»
Строчку «Отчет по продажам» сделать стиль заголовок 1, к строке с названием столбцов стиль заголовок 2, а строку «Всего» стилем ИТОГО. В результате должна получиться следующая таблица рис.3.8

Рис. 3.8 – Отформатированная таблица
Скопируйте полученную таблицу ниже на этом же листе.
Отформатировать числовые данные с использованием числовых, текстовых, денежных, финансовых, дата форматов, как показано на рис.3.9. Переделать столбец «Месяц» на стандартный формат даты.
Для этого используется окна «Выравнивание», или «Число».
Отформатировать диапазон ячеек F22: F26, как показано на рис.3.9, и скопировать созданный формат в диапазон F27:F33 используя команду «Копировать формат»
Условное форматирование.
Для данных в поле "Продажи" применить условное форматирование, при котором данные, значение которых больше 250 отображаются красным цветом, а числа, между 150 и 200 – синим, числа меньше 150 – заливку синей штриховкой.
Отредактировать формат - удалить условие меньше 150.
Пользовательские форматы.
Вызвать справочную систему и познакомиться со справочным материалом по пользовательским форматам чисел, дат и времени.
Создать пользовательский формат даты, при котором выводятся день недели, месяц и год, записанные полностью (ДДДД, Д ММММ, ГГГГ). Применить формат к ячейке С30.
Отредактировать формат так, чтобы день недели выводился сокращенно - две первые буквы ячейку С31.
Скопировать полученный формат на оставшиеся ячейки.
Создать пользовательский формат, при котором вместо отрицательных значений выводится сообщение "Не может быть!" - красным цветом. Применить для данных в поле "Продажи шт.". Ввести в указанный диапазон несколько отрицательных чисел и проверить результат.
Создать пользовательский формат, с помощью которого можно скрыть данные. Применить его к ячейке C25.
Все форматирование проделываем в одной таблице рис.3.9 форматы месяца и Цена телефона должны быть выполнены, как показано на рис.3.9. Остальное форматирование проделывается самостоятельно.

Рис.3.9 – Пример форматирования
Защита данных.
Закрепить область любой таблицы таким образом, чтобы строки с заголовками оставались на месте.
Защитить заголовки строк и столбцов таблицы, приведенной на рис. 3.9, а также ячейки с формулами. Оставить возможным изменение числовых данных таблицы.
III Использование формул при вычислениях в таблице.Вычисления в табличном процессоре.
Цель: Знакомство с использованием функций табличного процессора. Научиться пользоваться математическими и статистическими функциями.
Переходим на лист 4 Название листа «Вычисления в таблице» Копируем таблицу с листа 3. При необходимости исправляем формулы.
Ввести функции, указанные в клетках столбца А, в соответствии с указанным диапазоном.
Проанализировать результаты, возвращаемые предложенными функциями. (рис.3.10)

Рис.3.10 – Таблица с формулами
IV Использование логических функций
Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий.
Функция ЕСЛИ позволяет определить , выполняется ли указанное условие. Если условие истинно, то значением ячейки будет результат выражения 1, в противном случае выражения 2.
ЕСЛИ(условие; выражение 1; выражение 2)
Совместно с функцией ЕСЛИ используются логические операции И, ИЛИ, НЕ.
Например, = ЕСЛИ(И(Е4<3;H$98>=13); “выиграет”; “проиграет”)
Если значение в ячейке Е4 < 3 и H$98 >= 13, то выводится сообщение выиграет, в противном случае – проиграет.
Переходим на новый лист и назовем его «Логические формулы».
В клетке А3 записать функцию ЕСЛИ, выполняющую следующие действия:
если содержимое клетки А2<100, то записать в клетку А3 содержимое клетки А2, в противном случае записать число 100.
В клетке А4 записать функцию ЕСЛИ, выполняющую следующие действия: если содержимое клетки А2>50 и <100, то записать в клетку А4 содержимое клетки А2; - если содержимое клетки А2>=100, то записать в клетку А4 содержимое клетки B2; - в противном случае - С2.
Для выполнения этого задания рекомендуется сначала проанализировать логическое выражение, изобразить его графически и записать систему неравенств.
Ниже на этом же листе копируем таблицу с третьего листа и форматируем ее по образцу (рис.3.11)

Рис. 3.11 – Исходная таблица
Заполните столбец Е таблицы на рис. 3.11 с помощью логических формул назначьте буквенную категорию Продажи кол-во следуя следующему условию:
А – больше 200, В – от 200 до 150, С – от 150 до 120, D – меньше 120
В столбец F введите формулу, которая возвращает «хорошо», если кол-во продаж имеет категорию А или В и цена телефона не превышает 15000 р., в противном случае «плохо».
V Использование функций РАНГ и ПРЕДСКАЗАНИЕПереходим на новый лист и назовем его «Прогнозирование».

Рис. 3.12 – Таблица для определения ранга и прогнозирования
Использование функции РАНГ.
Используя функцию РАНГ, определить ранги магазинов в зависимости от объема продаж по каждому году и поместить результаты в соответствующие клетки таблицы. Посчитать среднее значение по продажам.
Использование функции ПРЕДСКАЗАНИЕ.
Пользуясь информацией об объемах продаж, спрогнозировать объемы продаж для каждого магазина в 2018 г., пользуясь функцией ПРЕДСКАЗАНИЕ.
VI Построение диаграммЦель: Знакомство с графическим представлением табличных данных и назначением разных типов диаграмм.
На следующем листе «Диаграмма»книги создать таблицу, и к ней диаграммы приведенные на рисунке 3.13.

Рис. 3.13
Научиться создавать и оформлять диаграммы на отдельных листах.
Каждый лист должен иметь имя, соответствующее типу диаграммы, расположенной на нем.
Построить диаграмму с областями.
Построить линейчатую диаграмму.
Построить диаграмму типа график.
Построить круговую диаграмму.
Построить кольцевую диаграмму.
Построить лепестковую диаграмму - "Радар".
Построить объемную круговую диаграмму .Построить объемную гистограмму.
Построить объемную диаграмму с областями .Научиться располагать на одном листе несколько диаграмм.
Создать на рабочем листе "Таблица_Диаграмма" две круговые диаграммы, отображающие показатели кол-во продаж и среднюю розничную цену по магазинам «Техносила», «ПАРКХАУС», «ИМПЕРИЯ».
Расположить их на отдельном листе, как показано на рис. 3.14.

Рис. 3.16
Просмотреть, как будет выглядеть лист с диаграммами на печати (меню "Файл - Предварительный просмотр"). Откорректировать размеры и расположение диаграмм так, чтобы заполнить 3/4 печатного листа.
Вновь вызвать режим "Предварительный просмотр" и познакомиться с командами настройки "Страница…". Просмотреть все четыре вкладки диалогового окна "Параметры страницы"
На вкладке "Страница" установить масштаб печати 150% от натуральной величины. Просмотреть результат и подобрать масштаб так, чтобы заполнить весь печатный лист.
На вкладке "Поля" установить флажки "Центрировать на странице" - Горизонтально и Вертикально.
На вкладке "Колонтитулы" создать верхний колонтитул - Ваша фамилия и имя. Выровнять колонтитул по центру страницы. В нижний колонтитул вставить текущую дату.
Вновь вызвать "Предварительный просмотр" и при необходимости отредактировать оформление листа.
Научиться редактировать диаграммы.
Отредактировать круговую диаграмму, созданную на листе "Диаграмма1", так, как показано на рис. 3.17

Рис. 3.17
Научиться редактировать объемные диаграммы.
Установить параметр "возвышение" для объемной гистограммы (3-D Column), соответствующий взгляду:
"сверху"(угол 90о);
"сбоку"(угол 0о);
"снизу"(угол -90о).
Установить "поворот" диаграммы вокруг оси Z для просмотра:
фронтально расположенных рядов (угол 0о);
под углом в 30о; под углом в 180о;
попытаться повернуть диаграмму с помощью мыши, поместив курсор мыши на один из углов параллелепипеда, описанного вокруг диаграммы после щелчка мыши на стенке диаграммы.
Установить параметр, изменяющий перспективу, равным:
0; 30; 100.
Изменить порядок рядов, представленных в диаграмме.
Контрольные вопросы
Какие основные типы данных используются в электронной таблице?
Пояснить назначение кнопок в строке формул.
Заполнение строк и столбцов константой, списком, формулой?
Какие типы адресов используются в MS Excel? Особенности относительной и абсолютной адресации ячеек? Как установить абсолютный адрес ячейки?
В каких случаях необходимо использовать абсолютный адрес ячейки?
Как вставить формулу в ячейку таблицы?
Какие форматы данных используются в электронных таблицах?
Как создать свой формат?
Как в ячейке отобразить формулу или результаты вычислений?
Как изменить точность отображения числа и результата вычислений?
Какие сообщения об ошибках выдает электронная таблица при вводе или вычислениях данных и функций?
Назовите виды диаграмм и их назначение.
Как копировать, удалить или переместить диаграмму?
Как форматировать текст и числовые данные в диаграмме?
Как защитить книгу, лист, ячейки? Как защитить лист и оставить незащищенные ячейки для ввода данных?

Приложенные файлы

  • docx 19295804
    Размер файла: 1 MB Загрузок: 0

Добавить комментарий