Лабораторный практикум. Табличный процессор MS Excel

Лабораторный практикум. Табличный процессор MS Excel

 

 

Методические указания к лабораторным работам по дисциплинам

ИНФОРМАТИКА

 

ИНФОРМАТИКА И ПРОГРАММИРОВАНИЕ

 

для всех направлений и специальностей

(квалификация (степень) «бакалавр»)

 

Уфа 2012

 

УДК 378.147.88:004.45

ББК 74.58:32.81

М54

 

Рекомендовано к изданию методической комиссией факультета информационных технологий и управления (протокол №__ от «__» _______ 2012 г.)

 

 

Составитель: доцент Филосова Е.И.

доцент Исламова Г.Г.

ст. преп. Иванова Г.Р.

 

 

Рецензент: к.т.н., доц. Л.П. Фандрова

 

Ответственный за выпуск: заведующий кафедрой Информатики и информационных технологий, к.х.н., доц. А.С. Беляева

 

 

Оглавление

ВВЕДЕНИЕ
Лабораторная работа №1 Создание электронных таблиц
Лабораторная работа №2 Вычислительные возможности Excel. Встроенные функции
Лабораторная работа №3 Управление данными в Excel
Лабораторная работа №4 Графические возможности Excel. Создание диаграмм
Лабораторная работа №5 Связывание электронных таблиц
Библиографический список
Приложение А
Приложение Б
Приложение В
Приложение Г
Приложение Д

ВВЕДЕНИЕ

Программа Microsoft Excel представляет собой табличный процессор, предназначенный для подготовки и обработки электронных таблиц.

Данный лабораторный практикум предназначен для изучения и применения возможностей программы MS Excel, в частности, создания электронных таблиц, работы со списками и встроенными функциями, создания диаграмм и связанных таблиц.

Требования к организации рабочего места

Лабораторный практикум выполняется в компьютерном классе. Рабочее место должно быть оборудовано персональным компьютером с процессором не ниже Pentium 500 МГц, ОЗУ 256 Мб. В качестве программного обеспечения должен быть установлен табличный процессор Microsoft Excel 2007.

Лабораторная работа №1

«Создание электронных таблиц»

Цель работы: Освоить правила и приемы создания и вычисления электронных таблиц, созданных в табличном процессоре Excel. Научиться:

ü создавать табличные документы, с данными различных форматов;

ü рассчитывать необходимые параметры.

Теоретические положения

Основные понятия Excel

Для запуска Excel нужно выбрать команду Пуск ► Все программы ► Microsoft Office ► Microsoft Excel 2007, либо дважды щелкнуть на значке Microsoft Excel, если он находится на рабочем столе. Для выхода из программы необходимо воспользоваться кнопкой Office и выбрать команду Выход из Excel, или кнопкой .

Программа Microsoft Excel представляет собой табличный процессор (ТП), предназначенный для подготовки и обработки электронных таблиц (ЭТ).

На рисунке 1.1 представлено прикладное окно Excel.

Рисунок 1.1 Прикладное окно Excel 2007

 

Содержание работы

2.1 Запустить программу Microsoft Excel.

2.2 На Листе 1 создать таблицу Накладная, произвести вычисления и оформить ее согласно примеру 1.

2.3 На втором листе рабочей книги сформировать таблицу из Приложения А по указанию преподавателя, выполнить все необходимые расчеты, оформить ее, сохранить файл.

2.4Выполнить задания для самостоятельной работы на Листе 3.

2.5 Ответить на контрольные вопросы.

2.6 Составить отчет о проделанной работе, который должен содержать название работы, постановку задачи исследования, сведения о последовательности выполнения заданий с результатами и ответы на контрольные вопросы, указанные преподавателем.

 

Методика выполнения работы

Рассмотрим основные возможности создания таблиц на примерах.

Пример 1 Расчет стоимости компьютерной техники по накладной.

Запуск программы Microsoft Excel

Создание таблицы

Оформление заголовка таблицы и названий столбцов

На листе 1 рабочей книги Excel создайте таблицу Накладная (рисунок 1.8). Для оформления заголовка таблицы установите курсор в ячейку A1 и введите текст, например Дата. Ввод текста заканчивается нажатием клавиши Enter. В ячейку А2 введите текст: Биржевой курс. Для появления в клетке В1 текущей даты следует ввести функцию =Сегодня() или воспользоваться для ее выбора категорией Дата и время Мастера функций. В ячейке В2 введите соответствующее числовое значение.

Рисунок 1.6 Пример таблицы расчета стоимости компьютерной техники

 

В ячейке В4 установите размер шрифта 16, начертание – полужирный и введите заголовок Накладная.

Для формирования заголовков столбцовустановите курсор поочередно в ячейки A6, В6, С6, D6, E6 и введите тексты заголовков столбцов. Чтобы текст заголовков располагался равномерно, как показано на рисунке 1.8, увеличьте ширину столбцов А, С, D и E с помощью мыши.

Оформление таблицы

Для более наглядного представления данных в созданной таблице ее следует отформатировать.

В начале работы заголовок был размещен в ячейке В4 – отцентрируйте его по ширине таблицы. Для этого нужно выделите диапазон ячеек заголовка (А4:Е4) и нажмите пиктограмму (Объединить и поместить в центре). Измените размер и начертание шрифта у заголовков столбцов и исходных данных. Для выбора линий очерчивания каждой ячейки и таблицы в целом воспользуйтесь опциями диалогового окна Формат ячейки вкладки Граница. Результат сравните с рисунком 1.8.

Переименуйте лист с таблицей с помощью контекстного меню листа, дав ему соответствующее по смыслу название, например, Накладная.

Вопросы для самоконтроля знаний

4.1 Что такое электронная таблица?

4.2 Что такое ячейка? Как задается адрес ячейки?

4.3 Что такое диапазон ячеек? Как обозначается диапазон ячеек?

4.4 Какие способы выделения диапазона ячеек используются в ЭТ?

4.5 Какие типы адресации ячеек используются в ЭТ?

4.6 Чем отличается относительный адрес от абсолютного?

4.7 Как вводить формулы в ячейку? Что отображается в ячейке, содержащей формулу?

4.9 Какие существуют типы данных?

4.10 Что такое формат данных и как он устанавливается?

4.11 Назовите приемы автоматического заполнения в Excel.

4.12 Как изменить размер ячейки? Как скопировать, переместить и удалить содержимое ячейки?

4.13 Как отредактировать содержимое ячейки?

4.14 Как сделать обрамление группы ячеек?

4.15 Какие операции можно производить с листами рабочей книги?

5 Задания и вопросы для самостоятельной работы

5.1 Задания на оформление таблиц

Согласно выбранному варианту получить на экране следующие фрагменты:

5.1.1

5.1.2

5.1.3 5.1.4

5.1.5

5.1.6

5.1.7 5.1.8

Задания на абсолютные и относительные ссылки

5.2.1 В ячейке С8 записана формула: =В9+19. Ее скопировали в ячейку С9. Какой вид будет иметь формула в ячейке С9?

5.2.2 В ячейке Е8 записана формула: =В12*5. Ее скопировали в ячейку F8. Какой вид будет иметь формула в ячейке F8?

5.2.3 В ячейке С8 записана формула: =А7+1. Ее скопировали в ячейку D8. Какой вид будет иметь формула в ячейке D8?

5.2.4 В ячейке А10 записана формула: =С12/5. Ее скопировали в ячейку А9. Какой вид будет иметь формула в ячейке А9?

5.2.5 В ячейке Е5 записана формула: =Е6*1,5. Ее скопировали в ячейку Е4. Какой вид будет иметь формула в ячейке Е4?

5.2.6 В ячейке В6 записана формула: =А$6+$В1. Ее скопировали в ячейку В7. Какой вид будет иметь формула в ячейке В7?

5.2.7 В ячейке ЕЗ записана формула: =$B10-F$5. Ее скопировали в ячейку Е6. Какой вид будет иметь формула в ячейке Е6?

5.2.8 В ячейке Е7 записана формула: =С$10*$В10. Ее скопировали в ячейку ЕЗ. Какой вид будет иметь формула в ячейке ЕЗ?

5.2.9 В ячейке D10 записана формула: =$Е9+А$6. Ее скопировали в ячейку D7. Какой вид будет иметь формула в ячейке D7?

5.2.10 В ячейке В6 записана формула: =А$6+$В10. Ее скопировали в ячейку D9. Какой вид будет иметь формула в ячейке D9?

5.2.11 В ячейке D3 записана формула: =$B3-F$5. Ее скопировали в ячейку Н5. Какой вид будет иметь формула в ячейке Н5?

5.2.12 В ячейке G6 записана формула: =Е$6+$В10. Ее скопировали в ячейку Е4. Какой вид будет иметь формула в ячейке Е4?

5.2.13 В ячейке ЕЗ записана формула: =$B10-F$5. Ее скопировали в ячейку D2. Какой вид будет иметь формула в ячейке D2?

5.2.14 В ячейке Н6 записана формула: =F6+$G$6. Ее скопировали в ячейку Н4. Какой вид будет иметь формула в ячейке Н4?

5.2.15 В ячейке D8 записана формула: =$B$8+F8. Ее скопировали в ячейку D10. Какой вид будет иметь формула в ячейке D10?

5.2.16 В ячейке D5 записана формула: =$D$4+C4. Ее скопировали в ячейку F7. Какой вид будет иметь формула в ячейке F7?

5.2.17 В ячейке Н6 записана формула: =F6+$G$6. Ее скопировали в ячейку D9. Какой вид будет иметь формула в ячейке D9?

5.2.18 В ячейке А7 записана формула: =В$6/$А$2. Ее скопировали в ячейку С7. Какой вид будет иметь формула в ячейке С7?

5.2.19 В ячейке А7 записана формула: =D$4/$А5. Ее скопировали в ячейку D2. Какой вид будет иметь формула в ячейке D2?

5.2.20 В ячейке А7 записана формула: =$C6/D$3. Ее скопировали в ячейку B7. Какой вид будет иметь формула в ячейке B7?

Задание на ввод и копирование формул

5.3.1 При копировании формулы из ячейки А2 в ячейки В2 и А3 были занесены формулы =В1+6 и =А2+6 соответственно. Что было записано в ячейке А2 и почему?

5.3.2 При копировании формулы из ячейки C3 в ячейки В3 и C4 были занесены формулы =A2-2 и =B3-2 соответственно. Что было записано в ячейке C3 и почему?

5.3.3 При копировании формулы из ячейки B3 в ячейки C3 и B2 были занесены формулы =B2*2 и =A1*2 соответственно. Что было записано в ячейке B3 и почему?

5.3.4 При копировании формулы из ячейки C4 в ячейки B4 и C3 были занесены формулы =C3-1 и =D2-1 соответственно. Что было записано в ячейке C4 и почему?

5.3.5 При копировании формулы из ячейки C4 в ячейки E4 и C5 были занесены формулы =D3*3 и =B4*3 соответственно. Что было записано в ячейке C4 и почему?

5.3.6 При копировании формулы из ячейки B3 в ячейки D3 и B2 были занесены формулы =C2+2 и =A1+2 соответственно. Что было записано в ячейке B3 и почему?

5.3.7 При копировании формулы из ячейки E4 в ячейки C4 и С5 были занесены формулы =D3*5 и =D4*5 соответственно. Что было записано в ячейке E4 и почему?

5.3.8 При копировании формулы из ячейки D4 в ячейки B4 и D3 были занесены формулы =C4+8 и =E3+8 соответственно. Что было записано в ячейке D4 и почему?

5.3.9 При копировании формулы из ячейки B4 в ячейки B2 и E4 были занесены формулы =C1-11 и =F3-11 соответственно. Что было записано в ячейке B4 и почему?

5.3.10 При копировании формулы из ячейки A4 в ячейки D4 и A6 были занесены формулы =E3*3 и =B5*3 соответственно. Что было записано в ячейке A4 и почему?

5.3.11 При копировании формулы из ячейки B3 в ячейку C5 в последнюю была занесена формула =$D$5*5. Что было записано в ячейке B3 и почему?

5.3.12 При копировании формулы из ячейки B4 в ячейку C3 в последнюю была занесена формула =$B$3-3. Что было записано в ячейке B4 и почему?

5.3.13 При копировании формулы из ячейки D2 в ячейку B1 в последнюю была занесена формула =$A$1-1. Что было записано в ячейке D2 и почему?

5.3.14 При копировании формулы из ячейки E7 в ячейку B1 в последнюю была занесена формула =$D$1+1. Что было записано в ячейке E7 и почему?

5.3.15 При копировании формулы из ячейки B3 в ячейку C4 в последнюю была занесена формула =D$4-4. Что было записано в ячейке B3 и почему?

5.3.16 При копировании формулы из ячейки B4 в ячейку C3 в последнюю была занесена формула =D$5*2. Что было записано в ячейке B4 и почему?

5.3.17 При копировании формулы из ячейки D3 в ячейку C4 в последнюю была занесена формула =B$2-10. Что было записано в ячейке D3 и почему?

5.3.18 При копировании формулы из ячейки C6 в ячейку B5 в последнюю была занесена формула =A$5+5. Что было записано в ячейке C6 и почему?

5.3.19 При копировании формулы из ячейки B4 в ячейку D2 в последнюю была занесена формула =$D3-E4. Что было записано в ячейке B4 и почему?

5.3.20 При копировании формулы из ячейки С4 в ячейку B2 в последнюю была занесена формула =C5*$A2. Что было записано в ячейке C4 и почему?

Лабораторная работа №2

«Вычислительные возможности. Встроенные функции Excel»

Цель работы:знакомство со стандартными функциями Excel. Научиться:

ü работать со встроенными функциями Excel.

Теоретические положения

Основные понятия о функциях

Функция – это заранее определенная формула, которая оперирует с одним или несколькими значениями и возвращает значение (или значения).

Microsoft Excel имеет более 300 встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции, такие как СУММ, ФАКТР и SIN, являются эквивалентами длинных математических формул, которые вы можете создать сами. Другие функции, такие как ЕСЛИ и ВПР, в виде формул реализовать невозможно.

Функции состоят из 2-х частей: имени функции и списка аргументов, который может состоять из одного или нескольких аргументов. Имя функции, как, например, СУММ или СРЗНАЧ описывают операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. Аргумент функции заключен в круглые скобки. Некоторые функции, такие как ПИ() и ИСТИНА(), не имеют аргументов.

При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа.

Функция СУММ используется чаще всех остальных. Чтобы облегчить доступ к ней, на стандартной панели инструментов имеется специальная кнопка Сумма .

Функцию можно ввести непосредственно в ячейку или воспользоваться инструментом Мастер функций, который позволяет произвести вычисление по шагам.

Перед вызовом Мастера функций необходимо выделить ячейку, где должен появиться результат вычисления. Затем кликнуть по кнопке Вставка функции , или развернув список доступных функций инструмента Сумма выбрать Другие функции. Также для работы с функциями и формулами может использоваться группа Библиотека функций вкладки Формулы на ленте.

Рисунок 2.1 Вкладка Формулы

По щелчку запускается Мастер функций. Его работа состоит из 2 шагов. На первом шаге (рисунок 2.2) выбирается категория функции и ее название.

Рисунок 2.2 Первый шаг Мастера функций

На втором шаге (рисунок 2.3) устанавливаются аргументы функции. Число аргументов у различных функций может быть различно. Они водятся в соответствующих полях ввода аргументов, причем у каждого поля имеется пояснение, какой аргумент ожидается. Аргументы могут быть введены непосредственно в поле ввода либо указаны в самой таблице, тогда аргументы проставляются автоматически и их можно редактировать.

Рисунок 2.3 Второй шаг Мастера функций

 

Содержание работы

2.1 Запустить программу Microsoft Excel.

2.2 В рабочей книге Excel на Листе 1 создать таблицу для расчета функций согласно примеру 1.

2.3На Листах 2 и 3 решить задачи согласно примерам 2 и 3 соответственно. Файл сохранить.

2.4 Выполнить заданиядля самостоятельной работы (по заданным таблицам рассчитать значения функций).

2.5 По указанию преподавателя выполнитьзадания для самостоятельной работы из Приложения В на табулирование аналогично примеру 3.

2.6 Ответить на контрольные вопросы.

2.7 Составить отчет о проделанной работе, который должен содержать название работы, постановку задачи исследования, сведения о последовательности выполнения заданий с результатами и ответы на контрольные вопросы, указанные преподавателем.

Методика выполнения работы

Пример 1 Задача обработки результатов олимпиады по информатике

Рассмотрим основные возможности расчета функций на примере 1.

3.2.1 Создание таблицы

На Листе 1 рабочей книге создайте следующую таблицу:

Рисунок 2.7 Таблица результатов олимпиады по информатике

Заполните ФИО, факультеты и количество баллов 10 участников, исходя из того, что максимальное количество баллов за первую задачу – 30, за вторую – 40, за третью – 50.

Расчет значений функций

Подсчитайте в ячейке F4 суммарное число баллов, набранное каждым участником олимпиады, используя функцию СУММ.

Вычислите отклонение от максимально возможного количества баллов. Затем – процент решенных задач.

С помощью функции РАНГ определите место каждого участника. РАНГ – это статистическая функция, возвращающая порядковый номер числа в списке. Для этого, установите курсор в ячейку I4, запустите, Мастер функций и выберите функцию РАНГ. При заполнении аргументов функции в поле Число укажите ячейку с суммой баллов первого участника (F4), в поле Ссылка укажите весь диапазон суммарных баллов (F$4:F$14), поле Порядок оставьте пустым.

Посчитайте минимальный, максимальный и средний баллы по количеству баллов и сумме, набранных участниками олимпиады, используя соответственно функции МИН(), МАКС(), и СРЗНАЧ().

Используя функцию СЧЕТЕСЛИ(), вычислите количество студентов вашего факультета, участвовавших в олимпиаде. В поле ввода Диапазон укажите названия всех факультетов, в поле Критерий ведите название вашего факультета.

Пример 2 Задача по уценке товаров, хранящихся на складе

Рассмотрим возможности расчета функций на примере 2. Пусть на складе хранятся товары. Известна их цена и срок, который они хранятся на складе. Необходимо вполовину уценить товары, хранящиеся более года.

Создание таблицы

На Листе 2 создайте таблицу с названием таблицы Ведомость уценки товаров на складе. Озаглавьте столбцы Название, Цена (руб.), Срок хранения (мес.), Цена после уценки. Введите названия 6 товаров, их цену и срок хранения на складе.

Расчет значений функции

Цену после уценки рассчитать с использованием функции ЕСЛИ. Установить курсор в ячейку D3, выберите функцию ЕСЛИ. Заполните аргументы функции:

Лог_выражение C3>12

Значение_если_истина B3/2

Значение_если_ложь B3

Полученную таблицу отформатируйте.

Пример 3 Задача табулирования значения функции

Рассмотрим возможности табулирования значения функции на примере 3. Вычислите значение функции на отрезке [0,3; 3,7] c шагом 0,2.

Создание таблицы

На Листе 3 разместите столбец со значениями х от 0,3 до 3,7. Создайте его, используя прием автоматического заполнения.

Лабораторная работа №3

«Управление данными в Excel»

Цель работы:Освоить правила и приемы работы с таблицами как с базами данных в табличном процессоре Excel. Научиться:

ü сортировать табличные документы;

ü производить поиск и выбирать из них необходимые данные;

ü подводить промежуточные итоги.

Теоретические положения

Основные понятия работы со списками

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

ü данные в пределах одного столбца должны быть однородны (одного типа);

ü столбцы однозначно поименованы;

ü каждая строка уникальна;

ü список не содержит пустых строк.

Строки списка называются записями, а столбцы – полями. Основными видами обработки баз данных являются сортировка с анализом полученных результатов, поиск и извлечение данных.

Содержание работы

2.1 Запустить программу Microsoft Excel.

2.2 На Листе 1 создать таблицу Результаты сдачи экзаменов студентами 1 группы, произвести действия над ней на других листах рабочей книги согласно примеру 1.

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

2.4 Ответить на контрольные вопросы.

2.5 Составить отчет о проделанной работе, который должен содержать название работы, постановку задачи исследования, сведения о последовательности выполнения заданий с результатами и ответы на контрольные вопросы, указанные преподавателем.

 

Методика выполнения работы

Создание таблицы

Переименуйте первый лист рабочей книги Excel в База данных. На этом листе сформируйте таблицу «Результаты сдачи экзаменов». В качестве заголовков столбцов используйте №, Фамилия И.О., Группа, Математика, Физика, Химия, Информатика, Средний балл. Введите фамилии 10 студентов и заполните оценки для каждого. Вычислите средний балл каждого студента за сессию. Для этого можно составить формулу или использовать функцию СРЗНАЧ (диапазон значений) из категории Статистические группы Библиотеки функций на вкладке Формулы.

Рисунок 3.14 Примерный вид исходной таблицы

Скопируйте полученную таблицу на второй и третий листы рабочей книги. Лист 2 переименуйте в Сортировка, Лист 3 – Фильтрация. Все оставшиеся задания проделывать на этих листах.

Сортировка диапазонов

На листе Сортировка сделать еще четыре копии исходной таблицы.

1) Первую таблицу отсортировать в порядке убывания среднего балла.

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

2) Отсортировать вторую копию таблицы по полю Фамилия. Тип сортировки – в алфавитном порядке.

3) Отсортировать третью таблицу по убыванию оценок по информатике

4) Отсортировать четвертую таблицу в порядке убывания оценок по математике, а одинаковые оценки по математике сортировать в порядке убывания оценок по информатике.

Для этого указываем любую ячейку списка данных и кликаем кнопку группы Сортировка и фильтр вкладки Данные. Первый уровень сортировки задаем – математика по убыванию, вторичная сортировка (второй уровень) – информатика по убыванию. После установки всех параметров нажать ОК.

Рисунок 3.14 Окно сортировки

5) Сортировать пятую таблицу по группам, внутри групп – по фамилиям

Рисунок 3.15 Результаты всех сортировок

Фильтрация данных

На листе Фильтрация ниже скопированнойтаблицы сделать еще 5 ее копий.

К каждой таблице примените команду Форматировать как таблицу из группы Стили вкладки Главная. Для таблиц будет выбран стиль оформления и установлены значки раскрывающегося списка фильтра.

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

Для этого необходимо развернуть список фильтра поля Фамилия в первой таблице, выбрать Текстовые фильтры ► Начинается с… и вписать любую букву, например, «А».

2) Во второй таблице выбрать студентов, у которых в фамилии или инициалах встречается определенная буква, например «Л».

3) В третьей таблице получить список студентов, сдавших экзамен по математике на 5.

4) В четвертой таблице показать студентов с тремя наибольшими средними баллами.

5) В пятой таблице выбрать тех студентов, кто учится только на 4 и 5.

6) В шестой таблице получить список студентов, у которых оценка по физике выше средней оценки по физике.

Рисунок 3.17 Результаты применения фильтров

Вопросы для самоконтроля знаний

4.1 Что такое список? Какие условия следует соблюдать при его формировании?

4.2 Что такое сортировка записей и как ее осуществить?

4.3 Как задать порядок вторичной сортировки?

4.4 Как отсортировать только один столбец в таблице?

4.5 Как осуществить сортировку по строкам?

4.6 Различаются ли верхний и нижний регистр при сортировке?

4.7 Как в отсортированном списке подвести промежуточные итоги?

4.8 Что такое фильтрация данных и как ее осуществить?

4.9 Приведите примеры числовых фильтров

4.10 Приведите примеры текстовых фильтров

4.11 Как выбрать 5 наибольших значений списка?

4.12 Какие шаблоны и как можно использовать при выборке данных?

4.13 Какие параметры нужно установить, чтобы данные выбирались при одновременном выполнении двух условий? хотя бы одного из двух условий?

4.14 Как вернуться к первоначальному состоянию таблицы после работы с командой Автофильтр?

4.15 Какие виды итогов можно подвести при работе с автофильтром?

4.16 Опишите формат функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Лабораторная работа №4

«Графические возможности Excel. Создание диаграмм»

Цель работы:получение навыков при построении, редактировании и оформлении диаграмм в табличном процессоре Excel. Научиться:

ü строить диаграммы различных типов и редактировать их;

ü устанавливать на диаграммы элементы оформления;

ü вносить в диаграмму дополнительные объекты и обновлять диаграммы.

Теоретические положения

Основные понятия о диаграммах

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

При создании диаграммы на основе выделенных ячеек использует значения величин с рабочего листа Excel и представляет их на диаграмме в виде элементов, которые могут быть изображены полосами, линиями, столбцами, секторами, точками и т.д. Группы данных, отражающих содержимое ячеек одной строки или столбца на рабочем листе, составляют ряд данных (рисунок 4.1). Каждый ряд данных выделяется на диаграмме уникальным цветом или узором.

Рисунок 4.1 Элементы диаграммы

После создания диаграммы можно усовершенствовать ее, дополняя различными компонентами диаграммы: метками данных, легендой, названиями и т.д. Компоненты диаграмм можно перемещать и их размеры можно изменять, а также форматировать, используя различные узоры, цвета, способы выравнивания текста, шрифты и прочие атрибуты форматирования.

2 Методика выполнения работы. Создание диаграммы на примере графического решения уравнения tg(x)=ln(x)

Создание диаграммы

Создание таблицы

На Листе 1 рабочей книги разместите данные расчетов функций в виде таблицы для построения диаграммы (рисунок 4.20) следующим образом:

ü в первом столбце таблицы (A2:A17) поместите значения Х от 3 до 4,5 с шагом 0,1 (воспользуйтесь приемом автозаполнения – введите первое и второе значения и заполните ряд);

ü во втором столбце рассчитайте значения функции TAN(x);

ü в третьем столбце найдите LN(x).

При вычислении данных функций, значения которых будут во втором и третьем столбцах, целесообразно использовать Мастер функций. Аргументом первой и второй функций будет ячейка A2. Остальные ячейки столбца – приемом автозаполнения.

Построение диаграммы

Для построения диаграммы необходимо, предварительно выделив данные (В2:С17), применить команду Вставка ► Диаграммы. Выберите тип диаграммы – График с маркерами.

Построенную диаграмму нужно дополнить недостающими элементами. При выделенной диаграмме на панели инструментов появляются дополнительные вкладки (Работа с диаграммами: Конструктор, Макет и Формат), необходимые при работе с диаграммами. Для установки заголовка диаграммы примените команду Макет ► Подписи ► Название диаграммы. Подписи по осям: Макет ► Подписи ► Названия осей. Сетка: Макет ► Оси ► Сетка. Для изменения типа диаграммы и данных для построения используется вкладка Конструктор. Установить или убрать легенду и подписи данных можно по команде Макет ► Легенда и Макет ► Подписи данных.

Результат сравните с рисунком 4.21.

Рисунок 4.21 Графическое решение уравнения tg(x)=ln(x)

Решением уравнения будет служить точка пересечения кривых.

Форматирование диаграммы

Команды Конструктор ► Экспресс-макет и Экспресс-стили предлагают изменить макет и стиль построенной диаграммы.

Построения линии тренда

Постройте для созданного графика функции TAN(x) линию тренда и найдите ее аналитическое выражение. Для этого войдите в область графика и отметьте построенную кривую. Примените команду Макет ► Анализ ► Линия тренда.

3 Контрольные вопросы

3.1 С помощью чего можно построить диаграмму в Excel?

3.2 Назовите способы запуска Мастера диаграмм.

3.3 Из каких шагов состоит процесс создания новой диаграммы?

3.4 Как можно выделить несмежные ряды данных?

3.5 Что нужно сделать, чтобы внести изменения в диаграмму?

3.6 Как можно добавить к диаграмме недостающие объекты?

3.7 Какие возможности предоставляет панель рисования?

3.8 Для чего нужны текстовые поля? Как вставить текстовое поле?

3.9 Как добавить новые данные на диаграмму?

3.10 Как удалить готовую диаграмму в Excel?

3.11 Какой из вкладок окна форматирования нужно воспользоваться для изменения: цвета заливки подписи? ориентации текста? размера шрифта? для выбора выравнивания текста? изменения цвета оси? изменения формата числа?

3.12 Вам нужно построить диаграмму уровня заработной платы по отраслям экономики России (топливная, банки, электроэнергетика и т.д.). Какой тип диаграммы вы выберете и почему?

3.13 Вам нужно построить диаграмму соотношения величин прожиточного минимума, минимальной и средней заработной платы в России за последние десять лет. За каждый год у вас должно присутствовать три показателя: минимальная заработная плата, прожиточный минимум и средняя заработная плата. Какой тип диаграммы вы выберите и почему?

3.14 Вам нужно построить диаграмму уровня безработицы в отдельных странах (в % от численности рабочей силы) за последние несколько лет. За каждый год у Вас должны присутствовать показатели по всем выбранным странам. Какой тип диаграмм вы выберите и почему?

4 Задания для самостоятельной работы

Согласно выбранному варианту восстановите таблицу по приведенной диаграмме:

5.1 5.2

5.3

5.4 5.5

5.6

5.7 5.8

5.9

5.10 5.11

5.12

5.13 5.14

5.15

5.16 5.17

5.18

Лабораторная работа №5

«Связывание электронных таблиц»

Цель работы:Освоить правила и приемы связывания и вычисления связанных электронных таблиц, созданных в табличном процессоре Excel. Научиться:

ü создавать табличные документы, содержащие связанные данные на разных рабочих листах;

ü рассчитывать необходимые параметры в связанных таблицах.

Теоретические положения

Основные понятия работы с окнами в Excel

MS Excel обеспечивает все многообразие работы с окнами в среде Windows.

Переход между окнами

Обычно можно перейти в другое окно, активизировав необходимые данные щелчком мыши. Для вызова окна закрытого другими окнами, нужно обратиться к пункту меню Окно, где находится список окон, которые в данный момент доступны. Введя с клавиатуры стоящую перед именем файла цифру или щелкнув мышью на нужной строке меню, вы переводите окно на первый план.

Если в настоящее время работа с открытым окном не ведется, то его можно «спрятать». Это делается с помощью команды меню Вид ►Окно ► Скрыть. Рабочий лист, таким образом, не удаляется, а делается невидимым. Вернуть его обратно можно командой Окно ► Отобразить.

2 Методика выполнения работы. Создание связанных таблиц на примере вычисления прибыли от реализации книжной продукции

Создание связанных таблиц

Создание таблиц

На лист Выручка занесите и оформите первую таблицу (рисунок 5.2).

Для заполнения первого столбца номерами можно использовать прием автоматического заполнения.