Тема 2: Табличные процессоры

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

 

Тема: «Создание электронных таблиц в среде Excel».

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

Отрабатываемые вопросы:

1. Создание таблицы и обработка в них данных.

2. Форматирование ячеек.

Организационно-методические указания

1. Запустить Excel, используя соответствующую пиктограмму на Рабочем столе и ознакомиться с ленточным интерфейсом Excel 2007.

2. Ввести текст таблицы, представленной на рис.1. Для создания таблицы рекомендуется при необходимости использовать «Справку Excel» для поиска подсказок по вводу данных и редактированию во время ввода.

3. Ввести в созданную таблицу строку Итого и подсчитать в ней результаты значений по столбцам, используя следующие способы:

· ввести значения формул и адресов влияющих ячеек с клавиатуры, например: в ячейку B7 ввести с клавиатуры формулу = B4+B5+B6 и т.д.;

· выбрать щелчками мыши необходимые для суммирования ячейки и вводя знаки “=“ и “+” с клавиатуры, где это необходимо, с подтверждением ввода формулы клавишей Enter;

· суммировать итог по столбцам по отдельности, выделяя каждый столбец и используя кнопку Автоматическая сумма;

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

4. Изменить ширину и высоту ячейки для полного помещения в ней текста, используйте перенос по словам.

5. Изменить вид и размер шрифта в ячейке.

6. Изменить расположение данных в ячейке относительно ее границ.

7. Отцентрировать заголовки и подзаголовки таблицы относительно ее ширины.

8. Изменить цвет символов и фона в ячейке.

 

Рис.1. Таблица исходных данных

9. Добавить обрамление ячейки, блока ячеек или всей таблицы.

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

 

Рис.2.Образец таблицы

 

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

Самостоятельно аналогичным образом определите в таблице Зависимые ячейки.

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

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

 

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

 

Тема: «Редактирование и форматирование электронной таблицы Excel».

Цель занятия: знакомство с новыми возможностями электронных таблиц Excel.

Отрабатываемые вопросы:

1.Использование различных приемов вычислений.

2.Форматирование таблиц.

3.Отображение формул, создание зависимостей и примечаний.

Организационно-методические указания

1.Создать на трех листах нового документа таблицу, приведенную на рис.1, введя данные в диапазон В4:F8, присвоить листам имена "Таб1", "Таб2", "Таб3".

2.Научиться использовать различные приемы заполнения ячеек формулами.

2.1.В диапазон G4:G8 записать формулу для вычисления общей суммы нагрузки по всем видам проводимых занятий (в часах занятий).

2.2.Составить формулу для вычисления суммарной нагрузки по лекциям в ячейке B10. Скопировать данную формулу в диапазон ячеек C10:F10.

 

Рис.1. Таблица «Учебная нагрузка»

2.3.Составить формулу для вычисления общей нагрузки для всех групп всех видов занятий по строкам в ячейку G9 и записать формулу для суммирования нагрузки по столбцам в ячейку G10.

2.4.Составить формулу для вычисления в ячейке H4 процентного содержания нагрузки для группы ЕС61-63 от общей суммы часов, полученной в ячейке G10. Затем скопировать данную формулу в диапазоне H5:H8, используя режимавтозаполнения ячеек таблицы.

2.5.Записать формулу в ячейку В11 для вычисления процентного содержания лекционной нагрузки от общей суммы часов, полученной в ячейке G10 и скопировать данную формулу в диапазоне ячеек C11:F11.

3.Используя кнопку Автосумма на вкладке Формула, определить среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксировать полученные результаты в выбранных ячейках таблицы.

4.Отформатировать полученную выше таблицу по образцу, представленному на рис.2, обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках (H4:H8 и В11:F11).

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

4.2.Отформатировать таблицу, пользуясь функцией автоформатирование.

5.Пользуясь кнопкой «Влияющие ячейки» на вкладке Формулы в подгруппе Зависимости формул, определить влияющие и зависимые ячейки для ячейки G9.

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

 

 

Рис.2. Образец таблицы «Учебная нагрузка»

 

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

8.Сохранить документ и показать результаты преподавателю.

 

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

Тема: «Вычисления в Excel с использованием Мастера Функций»

Цель занятия: приобрести практические навыки в определении абсолютных и относительных адресов ячеек, использовании Мастера Функций.

Отрабатываемые вопросы:

1. Мастер функций.

2. Абсолютные и относительные адреса.

3. Использование имен в вычислениях.

Организационно-методические указания

Запустить Exсel и открыть файл с электронной таблицей, предложенный преподавателем или используйте таблицу, созданную в Лабораторной работе №1: «Выручка от реализации готовой продукции», приведенную на Рис.1.

1. Подсчитать Итого по столбцам, используя следующие возможности Excel:

· с помощью кнопки Автосумма;

· используя Мастер Функций, вызываемый из вкладки Формулы (Вставить функцию f_x).

2. Подсчитать в правом свободном столбце суммы по строкам с помощью указанных в п.1 приемов.

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

 

 

Рис.1. Таблица «Выручка от реализации готовой продукции»

 

4. Повторить действия п.3, назначив ячейке с итоговой суммой имя, например: Всего. Назначить имя ячейке можно следующим образом. Выделите ячейку, в которой нужно присвоить имя и щелкните мышкой по кнопке Присвоить имя на вкладке Формулы. Затем введите имя, которое должно использоваться для ссылки на выбранную ячейку. После выполнения «Enter» имя будет присвоено.

5. Полученные результаты также представьте в процентном формате.

В качестве примера можно использовать таблицу “Выручка от реализации готовой продукции”, приведенную ранее или использовать любую другую подобную таблицу.

6.Подсчитать в крайнем правом столбце среднее значение по строке, для чего:

· выделить ячейку для ввода формулы и запустить Мастер Функций;

· ознакомиться с видами функций, предлагаемых в окне диалога, с помощью линеек прокрутки;

· в первом диалоговом окне Мастера Функций выбрать в категории Статистические функцию СРЗНАЧ;

· во втором диалоговом окне МастераФункций задать аргументы для вычисления среднего значения по строке;

· скопировать формулы в оставшиеся ячейки данного столбца.

Рекомендуется в качестве примера использовать таблицу “Выручка от реализации готовой продукции”.

7.Изучение относительных и абсолютных ссылок (адресов).

7.1.Постройте таблицу, приведенную на рис.2. «Копирование формулы с относительной ссылкой». Наблюдайте при копировании формулы в ячейке C1 автоматическое изменение адресов в формулах: «=A1+В1» на «=A2+В2» и «=А3+С3» соответственно. Это соответствует относительной ссылке.

7.2.Постройте таблицу, приведенную на рис.3 «Копирование формулы с абсолютной ссылкой», создайте формулу вычисления процента покупок за январь: =В2/В5 и выполните копирование формулы в другие ячейки.

Обратите внимание на сформированные ошибки (#ДЕЛ/0!). Скорректируйте формулу, используя абсолютные ссылки путем добавления символа $ перед адресом, и выполните копирование опять (при появлении вопросов вызовете «Справку», набрав «Абсолютная ссылка»).

 

Рис. 2. Копирование формулы с относительной ссылкой

 

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

 

 

Рис.3. Копирование формулы с абсолютной ссылкой

 

8.Самостоятельно выполнить задание с использованием абсолютной и относительной ссылками.

9.Сохранить файл рабочей книги в вашем рабочем каталоге и выйти из Excel.

 

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

Тема: «Способы оформления таблиц и защитаданных в таблицах»

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

Отрабатываемые вопросы:

1. Форматирование и оформление текста и данных.

2. Защитаданных.

Организационно-методические указания

1.Форматирование текста.

1.1.Выравнивание текста.Создать на первом рабочем листе таблицу по приведенному образцу на рис.1 и сохранить ее в файле для дальнейшего использования в работе. Скопировать на второй лист содержимое первого листа.

 

 

Рис. 1. Образец исходной таблицы

 

Отформатировать тексты таблицы по образцу, приведенному на рис.2. На этом примере научиться выравнивать текст всеми доступными способами используя вкладку Главная, группу Выравнивание, кнопку Ориентация .

1.2. Подстроить параметры таблицы (ширину столбцов и высоту строк) так, чтобы внешний вид таблицы соответствовал рис.2. Для выравнивания использовать вкладку Главная, инструменты группы Выравнивание.

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

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

 

 

Рис.2. Шрифтовое форматирование таблицы

1.4.Рисование рамок (обрамление). Для оформления таблицы (рис.3) использовать кнопку Все границы группы Шрифт (обрамление ячеек).

1.5.Выполнить "раскраску" таблиц ("Заливка" и "Узор"). Для выделения данных в таблице использовать различные варианты оформления из группы "Заливка" и "Узор".

2.Форматирование числовых данных.

2.1.Просмотреть все варианты форматирования чисел, предлагаемые табличным процессором Excel, используя группу Число вкладки Главная.

2.2.На третьем рабочем листе создать таблицу, приведенную на рис.4. Отформатировать числовые данные с использованием числовых форматов, как показано на рисунке. Столбец F заполнить формулами, вычисляющими отношение значений столбца E ("Продано шт.") к общей сумме, записанной в клетке E15.

 

Рис.3. Обрамление таблицы

 

3.Защита данных.

Защитить заголовки строк и столбцов таблицы, приведенной на рис.4 и оставить возможным изменение некоторых данных таблицы, выбранных самостоятельно. Для защиты используйте на вкладке Рецензирование кнопку Защитить лист .

 

Рис.4. Форматирование числовых данных в таблице

Показать результаты преподавателю.

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

Тема: «Изучение типовых функций табличного процессора»

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

Отрабатываемые вопросы:

1. Математические и статистическиефункции.

2. Логическиефункции.

Организационно-методические указания.

1.Научиться пользоваться математическими и статистическими функциями.

1.1.Создайте таблицу, приведенную на рис.1.

1.2.Ввести в столбец «B» функции, указанные в столбце «А» и сравнить полученные результаты с данными, приведенными в столбце «В» на рис.1.

1.3.Проанализировать результаты и сохранить созданную таблицу в книге.

 

 

Рис.1. Выполнение математических и статистических функций

 

2.Изучение использования логических функций.

Функция ЕСЛИ используется при проверке условий для значений и формул. Данная функция возвращает Значение 1, если заданное условие при вычислении дает значение ИСТИНА и возвращает Значение 2, если условие соответствует значению ЛОЖЬ.

Синтаксис функции следующий:

 

ЕСЛИ(лог_выражение; значение 1_если_истина; значение 2_если_ложь)

 

Лог_выражение - любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100- логическое выражение. Если значение в ячейке A10 равно 100, то это выражение принимает значение ИСТИНА, а в противном случае - значение ЛОЖЬ. Этот аргумент может использоваться в любом операторе сравнения (см. “Справку”). Выполните данный пример:

ЕСЛИ(А10=100; «Правда»; «Неправильно»)

Измените значение в ячейке А10 на другое, посмотрите результат и объясните причину изменений.

2.1.Ввести таблицу, приведенную на рис.2.

2.2.В ячейку С2 введите формулу для вычисления значения Скидка, используя функцию ЕСЛИ и скопируйте формулу в диапазон ячеек (С3:С6):

· если стоимость товара <2000, то скидка составляет 5% от стоимости товара;

· в противном случае - 10%.

 

Рис.2. Реализация функции ЕСЛИ

 

2.3.В ячейкуD2 введите формулу, определяющую Налог, и скопируйтеформулу в ячейки(D3:D6):

· если разность между Стоимостью в ячейке В2и Скидкой >5000, то налог составит 5% от этой разности;

· в противном случае - 2%.

2.4.В ячейку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 ввести формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.

2.5.Введите в клетки Е8:E10 три имени: (Олег, Миша, Игорь), а в клетки F8:F10 занести даты их рождений. В клетку E4 ввести одно из упомянутых имен.

Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполнить следующие действия:

проанализировав имя в клетке Е4, записать в клетку С12 функцию ЕСЛИ, обеспечивающую:

· вывод даты рождения, взятой из соответствующей клетки;

· если же введено неподходящее имя, записать текст сообщения: "нет такого имени".

3.Использование функции СУММЕСЛИ. Для выполнения суммирования ячеек диапазона, удовлетворяющих заданным условиям, следует использовать функцию СУММЕСЛИ, имеющую следующий синтаксис:

 

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования), где:

 

· диапазон - диапазон адресов вычисляемых ячеек;

· критерий - критерий в виде числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 24, ">22";

· диапазон_суммирования - фактические ячейки для суммирования.

Ячейки в областидиапазон_суммирования суммируются, если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.

Для получения формулы, возвращающей в зависимости от выполнения условия одно из двух значений, например вознаграждение по указанному объему продаж, используйте функцию ЕСЛИ. На рис. 3 представлен пример суммирования ставок комиссионных закупаемого оборудования, значения которых превышают 12000.

 

 

Рис. 3.Использование функции СУММЕСЛИ

 

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

 

 

Рис. 4. Суммирование с использованием функции СУММЕСЛИ

 

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

Тема: « Сортировка и фильтрация данных, построение сводных таблиц».

Цель занятия: получить практические навыки обработки данных, представленных списками и познакомиться с возможностью анализа данных с помощью сводных таблиц.

Отрабатываемые вопросы:

1. Сортировка и фильтрация данных.

2. Сводные таблицы.

Организационно-методические указания

1.Загрузить Excel и ввести в окно рабочего листа 1 текст таблицы (рис.1), представленной ниже.

 

 

Рис.1. Сортировка данных

 

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

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

2.1.Сортировка текстовых значений. Выберите столбец с текстовыми данными, поместив курсорв одну из его ячеек. На вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр

 

Выполните одно из следующих действий:

· Для сортировки алфавитно-цифровых символов по возрастанию выберите вариант Сортировка от А до Я.

· Для сортировки алфавитно-цифровых символов по убыванию выберите вариант Сортировка от Я до А.

Обратите внимание на перемещение данных в других столбцах таблицы.

2.2.Сортировка чисел.Выберите столбец с числовыми данными и выполните одно из следующих действий:

· для сортировки чисел по возрастанию выберите вариант Сортировка от минимального к максимальному;

· для сортировки чисел по убыванию выберите вариант Сортировка от максимального к минимальному.

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

Выделите все данные в таблице, на вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр, а затем выберите в списке пункт Фильтр.

 

Щелкните кнопку рядом с заголовком столбца Город, в качестве условия выберете СПб и посмотрите на результат фильтрации. Затем аналогичные действия повторите для столбца Агент и оцените работу некоторых из агентов данного списка. Как видно, в отфильтрованных данных отображаются только строки, соответствующие выбраннымусловиям(Условие. Ограничение, заданное для отбора записей, включаемых в результирующий набор записей запроса или фильтра.).

3.Построение сводных таблиц.Создать на рабочем листе 3Сводную таблицу, анализирующую данные в неотсортированном списке нижеприведенной таблицы «Бюджет учебного времени по видам обучения».

Для создания сводной таблицы на вкладке Вставка в группе Таблицы выберите раздел Сводная таблица, а затем пункт Сводная таблица.

 

На экран будет выведено диалоговое окно Создание сводной таблицы.

Выделите таблицу и новый или текущий лист для создаваемой сводной таблицы и нажмите ОК.

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

Например, перетащите поле Предмет в Название строк, Кол-во часов в неделю -вполе Значения, а поля Курс и Вид обучения – в поле Названия столбцов.

A. Создайте сводную таблицу, содержащую:

· в строках - данные по полю Курс;

· в столбцах - данные по полю Вид обучения;

· обрабатываются данные по полю Количество часов в неделю.

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

B. Создайте другую сводную таблицу, содержащую:

· в строках - данные по полю Предмет;

· в столбцах - данные по полю Курс;

· обрабатываются данные по полю Количество часов в неделю;

Выполните вышеуказанные действия над полученной сводной таблицей.

A. Построить гистограмму для полученных сводных таблиц. Для построения выделите сводную таблицу и используя вкладку Вставка – Работа со сводными диаграммами– Гистограмма выберите самостоятельно вид гистограммы. Используя вкладку Макет оформите гистограмму(название гистограммы, осей и т.п.).

4.Сохранить созданныематериалы.

Таблица: Бюджет учебного времени по видам обучения

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

 

Тема: «Вычисление функций одной переменной в Excel»

Цель занятия: получить практические навыки вычисления функций одной переменной у=f(x)и построения их графиков в Excel

Отрабатываемые вопросы:

1.Создание формул для вычисления функций одной переменной.

2.Построение графиков функций.

 

Организационно – методическиеуказания

В Excel удобно осуществлять построение различных функций на плоскости и поверхностей в пространстве.

1 .Построение прямой. Рассмотрим построение данной функции в Excel на примере уравнения у=2х+1 в диапазоне: хÎ[0; 3] с шагом х=0,25. Решение з адачи включаетследующие этапы.

1.1. Ввод данных. Для этого значения аргумента Х и функции У следует представить в таблице, первый столбец которой будет заполнен значениями Х,а второй – функцией У. Для этого в ячейку А1 вводим заголовок Аргумент, а в ячейку В1 – заголовок Прямая.

В ячейку А2 вводится первое значение аргумента 0, а в ячейку A3 вводится второе значение аргумента с учетом шага построения (0,25). Затем, выделив блок ячеек А2:АЗ, автозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А14).

Далее вводим уравнение прямой: в ячейку В2 вводим формулу: =2*A2+1, з атем копируем эту формулу в ячейки В2:В14. В результате должна быть получена следующая таблица исходных данных и результатов (рис.1.).

 

 

Рис.1.Построение прямой

 

1.2. Построение графика функции. Выделите диаграмму и, используя вкладку Вставка ® График, постройтеграфик функции у=2х+1 (рис.2.).

 

Рис.2. График прямой

1.3. Используя в кладку Макет, выполните самостоятельно оформление полученного графика (название графика и осей, размещение легенды и т.п.).

2. Решение уравнения второго порядка. Примерами уравнений второго порядка являются: парабола, гипербола, окружность, эллипс и другие. В качестве примера рассмотрим построение параболы вида: у =х² в диапазоне хÎ[–3; +3] с шагом х=0,5.

2.1. Ввод данных.В ячейку А2 вводится первое значение аргумента (-3), в ячейку A3 вводится второе значение аргумента (–2,5), а затем, выделив блок ячеек А2:АЗ, автозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А14).

Далее в ячейку В2 вводим уравнение =А2*А2, а затем копируем эту формулу в диапазон В2:В14. В результате должна быть получена следующая таблица исходных данных.

2.2.Построение графика функции. Используя вкладку Вставка® ДИАГРАММА, постройтеграфик функции у=х^2 (РИС.4).

Используя в кладку Макет, выполните самостоятельно оформление полученного графика (название графика и осей, размещение легенды и т.п.).

 

Рис.3. Построение параболы

 

Рис.4.График параболы

 

 

Рис.5. График гиперболы

 

3. Построение гиперболы. В простейшем случае уравнение гиперболы имеет вид y=k/x. Задача построения гиперболы аналогична построению параболы.

Рассмотрим построение гиперболы y=1/x в диапазоне хÎ[0,1: 10,1] с шагом х=0,5.Выполните все вышеуказанные действия самостоятельно (рис.5).