Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 2
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ
РОССИЙСКОЙ ФЕДЕРАЦИИ
Государственное образовательное учреждение
высшего профессионального образования
«Юго-Западный государственный университет»
УТВЕРЖДАЮ
Первый проректор-
проректор по учебной работе
______________________Е.А.Кудряшов
«___»______________2011 г.
Методические указания к лабораторным работам по курсам «Информатика и математика» и «Математика и информатика»
КУРСК 2011
Составитель Е.И.Аникина
УДК 681.3
Рецензент
Кандидат технических наук, доцент кафедры информатики и прикладной математики Н.Н. Бочанова
Обработка данных в Excel [Текст]: методические указания к лабораторным работам по курсам «Информатика и математика» и «Математика и информатика»/Юго-Зап. гос. ун-т; сост. Е.И.Аникина. Курск, 2011. 26 с.: Ил.17.
Содержат описание основных понятий электронных таблиц, описание основ технологии обработки данных средствами популярного пакета Microsoft Excel, примеры решения практических задач, а также задания и рекомендации для выполнения лабораторных работ по курсу «Информатика и математика» для студентов специальности «Юриспруденция» и по курсу «Математика и информатика» для студентов специальности «Сервис и туризм».
Текст печатается в авторской редакции
Подписано в печать . Формат 60х84 1/16. Печать офсетная
Усл. печ. л. 1,7 . Уч.-изд. л.1,6 . Тираж 100 экз. Заказ . Бесплатно
Юго-Западный государственный университет
305040, Курск, ул.50 лет Октября, 94.
ВВЕДЕНИЕ
Для представления данных в наглядном и удобном для человека виде используют таблицы. Компьютер позволяет представлять их в электронной форме, а это дает возможность не только отображать, но и обрабатывать данные. Класс специальных программ, используемых для этой цели, называется электронными таблицами.
Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчет по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки таблицы приводит к пересчету значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.
Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без проведения расчетов вручную или специального программирования.
Наиболее широкое применение электронные таблицы нашли в экономических и бухгалтерских расчетах, но и в научно-технических задачах электронные таблицы можно использовать эффективно, например для:
• проведения однотипных расчетов над большими наборами данных;
• автоматизации итоговых вычислений;
• обработки результатов экспериментов;
• проведения поиска оптимальных значений параметров;
• подготовки табличных документов;
• построения диаграмм и графиков по имеющимся данным.
Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа Microsoft Excel.
Основные понятия Microsoft Excel
На рисунке 1 показано рабочее окно программы Microsoft Excel. Основные элементы управления (меню, панели инструментов) аналогичны соответствующим элементам окна Microsoft Word. Работа с этими элементами выполняется по правилам, общим для любого приложения Microsoft Office.
Рис. 1. Рабочее окно программы Microsoft Excel.
В строке заголовка отображается название программы (Microsoft Excel) и имя документа. Документ Excel называется Рабочей книгой. При сохранении файлу по умолчанию присваивается имя Книга1, расширение имени xls.
Книга состоит из рабочих листов. Каждый рабочий лист имеет имя, которое отображается на его ярлыке. По умолчанию листы называются: Лист1, Лист2 и т.д. Для активизации листа нужно щелкнуть на его ярлыке.
Рабочая область каждого листа представляет собой таблицу. Столбцы таблицы озаглавлены латинскими буквами и, далее, двухбуквенными сочетаниями. Всего в рабочем листе – 256 столбцов (от А до IV). Строки нумеруются от 1 до 65536.
Активная ячейка. Одна из ячеек всегда является активной (текущей) и выделяется рамкой активной ячейки. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке.
Сделать активной другую ячейку (переместить курсорную рамку) можно несколькими способами:
Ячейки и их адресация. На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки (адрес ячейки) состоит из имени столбца и номера строки, на пересечении которых она расположена, например: А1 или D234. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках.
Большинство адресов в формулах записываются в относительной форме - например, С3 (столбец)(строка) .
Относительными адресами называются адреса, которые при копировании в составе формулы в другую ячейку автоматически изменяются . При копировании формулы с относительным адресом (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)
В большинстве случаев это очень удобно, но иногда этого не требуется. Некоторые адреса в формулах записываются в абсолютной форме - например, $С$3.
Абсолютными называются адреса, которые при копировании в составе формулы в другую ячейку не изменяются. Абсолютные ссылки используются в формулах тогда, когда нежелательно автоматическое изменение ссылки при копировании.
Диапазон ячеек. На данные, расположенные в соседних ячейках, можно ссылаться в формулах как на единое целое. Такую группу ячеек называют диапазоном. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в левом верхнем и правом нижнем углах прямоугольника, например:
А1 :С15.
Типы данных. Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число или формула, а также оставаться пустой. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные рассматриваются как текст.
Признаком формулы является знак равенства «=».
Лабораторная работа №1
Рис. 2. Таблица с результатами сессии
Ввод и форматирование данных.Технология работы
Введите заголовок «Результаты … » в ячейку А1. Поскольку соседние ячейки свободны, заголовок займет столько места, сколько нужно.
Расположение текста в ячейке в несколько строк. В ячейки строки 2 надо ввести заголовки столбцов. Обратите внимание на то, что в таблице на рис. 2 часть заголовков занимают внутри соответствующих ячеек несколько строк.
Сначала надо увеличить высоту строки 2. Для этого установите курсор на горизонтальную разделительную линию между строками 2 и 3 (при этом курсор примет вид двунаправленной стрелки). Нажмите левую клавишу мыши и, не отпуская ее, сместите разделительную линию вниз.
Теперь надо изменить формат ячеек строки 2 так, чтобы появилась возможность вводить текст в несколько строк. Выделите строку заголовков (щелкните мышью на номере строки 2) и выполните команду ФОРМАТЯЧЕЙКИВЫРАВНИВАНИЕПЕРЕНОСИТЬ ПО СЛОВАМ.
Введите в ячейки строки 2 соответствующий текст. Ввод в каждую ячейку заканчивайте нажатием клавиши ENTER.
Ввод данных в таблицу. Введите данные в таблицу. После ввода данных в ячейку нажимайте Enter. Чтобы отредактировать уже введенные в ячейку данные, выполните двойной щелчок мыши на ячейке.
Форматирование таблицы. Выделите всю таблицу и выровняйте данные в ячейках по центру . Можно использовать значок на панели инструментов или команду ФОРМАТ ЯЧЕЙКИ ВЫРАВНИВАНИЕ ПО ЦЕНТРУ.
Задайте жирный шрифт для заголовков столбцов.
Сохранение результатов работы на диске. Сохраните таблицу в рабочем каталоге своей группы с помощью команды ФАЙЛ → СОХРАНИТЬ.
Задание 2
Вычислить значения средних баллов каждого студента за сессию и средних баллов группы по каждому из предметов.
Вычисление средних значений. Технология работы
Средний балл студента за сессию определяется как среднее арифметическое значение оценок данного студента. Для вычисления среднего арифметического значения воспользуемся встроенной функцией СРЗНАЧ.
В ячейку F3 надо ввести формулу:
= СРЗНАЧ(B3;C3;D3;E3)
Обратите внимание на то, что имя функции состоит из русских букв, а адреса ячеек включают в себя английские буквы.
Нажмите клавишу Enter. Появится результат вычисления по формуле.
Копирование формулы. Формулы для вычисления средних баллов остальных студентов будут иметь такой же вид, отличаясь друг от друга только номерами строк в адресах ячеек. Скопируйте полученную формулу в остальные ячейки столбца F. Для этого щелкните мышью на ячейке F3 с формулой. В правом нижнем углу рамки ячейки появится маленький черный квадрат. Установите на нем куpсоp мыши. При этом курсор изменит форму на черный крестик. Нажмите на левую кнопку мыши и, удерживая ее, растяните рамку, чтобы захватить остальные ячейки столбца F.
Средний балл группы по предмету определяется как среднее арифметическое оценок всех студентов.
В ячейку В17 введите знак равенства, имя функции и открывающую круглую скобку
=СРЗНАЧ(
Затем при нажатой левой клавише проведите мышью по ячейкам В3 – В16. В формуле появится обозначение диапазона ячеек В3:В16. Нажмите Enter для получения результата вычислений. Скопируйте полученную формулу в остальные ячейки строки 16.
Задание 3
На основе таблицы на рис.2 построить таблицу, показанную на рис.3.
Рис.3. Ведомость для начисления и выплаты стипендии
Установите курсор в ячейку А19 и наберите название таблицы «Ведомость для начисления и выплаты стипендии».
В ячейку В20 введите текст Фамилия студента. Расположите этот текст в 2 строки, как было описано выше. В ячейку С20 введите текст Стипендия.
Копирование данных из одной таблицы в другую. Технология работы
Фамилии студентов надо скопировать из таблицы с результатами сессии. Для этого сделайте следующее.
1) С помощью мыши выделите ячейки с адресами от А3 до А15.
2) Нажмите правую кнопку мыши и в появившемся контекстном
меню выберите команду Копировать.
Задание 4
Организовать автоматическое начисление стипендии студентам группы, исходя из следующего условия. Студенту начисляется стипендия, если его средний балл за сессию не ниже 3,5. В противном случае стипендия не начисляется.
Использование логической функции ЕСЛИ. Технология работы
Для определения размера стипендии воспользуемся логической функцией ЕСЛИ
ЕСЛИ ( условие; значение_если_истина; значение_если_ ложь).
В нашей задаче размер стипендии не является раз и навсегда фиксированной величиной, он периодически меняется в соответствии с изменениями в российском законодательстве. Для того, чтобы не менять формулы для начисления стипендии при подобных изменениях, целесообразно ввести текущее значение обычной стипендии в отдельную ячейку, например, Е22, а в формулах использовать ссылки на данную ячейку. Чтобы при копировании формулы этот адрес оставался в неизменном виде,
используем абсолютный адрес ячейки $Е$22.
В ячейку С21 введите формулу
=ЕСЛИ(F3>=3,5 ; $E$22; 0)
и нажмите клавишу ENTER.
Скопируйте формулу в остальные ячейки столбца С.
Задание 5
Вычислить суммарный размер стипендии группы.
Автосуммирование. Технология работы
Для определения суммарного размера стипендии группы воспользуемся функцией автосуммирования.
Установите курсор в ячейке С34 и на панели инструментов щелкните мышью по кнопке с изображением знака суммы . В ячейке появится формула
=СУММ (С21:С33)
Нажмите ENTER для получения результата. Так как размер стипендии выражается в рублях, установите соответстующий формат ячейки. Выделите ячейку С35 и выполните команду ФОРМАТ – Ячейки – Число – Денежный.
Задание 6
Сохраните полученную таблицу в папке вашей группы и на резервном носителе ( CD/RW, флэш). Данная таблица используется для выполнения следующей лабораторной работы №2.
Лабораторная работа №2
Исходные данные - файл (рабочая книга), полученный в результате выполнения лабораторной работы №1.
Построить гистограмму средних баллов студентов за сессию.
Построение гистограммы .Технология работы
Построение любой диаграммы начинается с выделения с помощью мыши ячеек, содержащих исходные данные для диаграммы.
Выделение ячеек с исходными данными. Исходные данные для гистограммы средних баллов – это фамилии студентов (ячейки А3:А15) и значения соответствующих средних баллов (ячейки F3:F15). Данные ячейки образуют несмежные области таблицы. Для того, чтобы выделить эти несмежные области, сначала проведите мышью по ячейкам А3:А15, затем нажмите клавишу Ctrl и, удерживая ее, проведите по ячейкам F3:F15. Должен получиться результат как на рисунке 4.
Вызов Мастера диаграмм. Найдите на панели инстpументов кнопку (Мастер диаграмм) и щелкните на ней мышью. Откpоется окно диалога Мастеpа диагpамм (рис.5).
Мастер автоматически предлагает тип диагpаммы «Гистограмма». Нажмите кнопку «Далее» для перехода к следующему шагу.
Рис.4. Таблица с выделенными исходными данными для построения гистограммы
Рис.5. Окно Мастера диаграмм
Появится окно, представленное на рис.6. Если исходные данные были выделены правильно, у вас получится такая же диаграмма.
Рис.6. Предварительный вид диаграммы
Нажмите кнопку «Далее» для перехода к следующему шагу.
В появившемся окне найдите поле «Название диаграммы» и введите в него текст «Средние баллы студентов группы ЮР-91».
В поле «Ось Х» введите текст «Средний балл».
Щелкните мышью по полю «Ряд 1» и нажмите клавишу Del (Delete) для удаления этой надписи с поля диаграммы.
Диаграмма приобрела законченный вид. Нажмите кнопку «ГОТОВО». Должна получиться диаграмма, подобная изображенной на рис.7.
Рис. 7. Гистограмма средних баллов студентов
Построить гистограмму средних баллов группы по предметам.
Технология работы
Исходные данные для гистограммы средних баллов по предметам – это ячейки с названиями предметов (B2, C2, D2, E2) и ячейки со значениями соответствующих средних баллов (B17, C17, D17, E17). Выделите эти ячейки, пользуясь описанной выше технологией. Вызовите Мастер диаграмм и выполните действия, аналогичные описанным выше. В результате у вас должна получиться следующая диаграмма (рисунок 8).
Рис.8. Гистограмма средних баллов по предметам
Рис.9. Таблица для подсчёта количества оценок «отлично», «хорошо», «удовлетворительно» и «неудовлетворительно»
Подсчет количества значений, удовлетворяющих заданному условию. Технология работы
Для определения количества оценок воспользуемся статистической функцией
СЧЁТЕСЛИ (диапазон ячеек ; критерий)
Диапазон ячеек задает область таблицы, в которой подсчитывается число значений, удовлетворяющих заданному условию (критерию). Например, для определения количества оценок «отлично» по предмету «Правоохранительные органы» в ячейку В42 введите формулу : =СЧЁТЕСЛИ(B3:B16 ; 5) и нажмите клавишу Enter.
Введите самостоятельно аналогичные формулы в остальные незаполненные ячейки таблицы. При этом имейте в виду, что формулы копировать не получится, так как формулы в одной строке будут содержать разные значения критериев (надо подсчитать, соответственно, количество «четверок», «троек» и т.д. по одному предмету), а формулы в разных строках должны проверять значения из разных столбцов первой таблицы (оценки по соответствующим предметам).
Построить круговую диаграмму оценок на экзамене по дисциплине «Правоохранительные органы».
Построение круговой диаграммы. Технология работы
Круговая диаграмма используется для графического представления процентного соотношения данных, которые в сумме образуют 100%. Для того, чтобы показать графически процентное соотношение оценок студентов по дисциплине «Правоохранительные органы», надо выделить ячейки с заголовком данной таблицы (B41, C41, D41, E41), затем нажать клавишу Ctrl, и удерживая ее, выделить ячейки с количеством оценок по этой дисциплине (B42, C42, D42, E42).
Вызовите Мастер диаграмм. В окне Мастера диаграмм «,Шаг 1 из 4: Тип диаграммы» выберите в списке названий тип диаграммы «Круговая» и любое изображение круговой диаграммы. На шаге 2 просто нажмите кнопку «Далее». На шаге 3 выберите вкладку «Заголовки» и в поле «Название диаграммы» введите текст «Правоохранительные органы». В том же окне перейдите на вкладку «Подписи данных» и поставьте флажок в пункте «Доли». Нажмите кнопку «Готово». Должна получиться диаграмма как на рис. 10.
Рис. 10. Круговая диаграмма оценок на экзамене по дисциплине «Правоохранительные органы».
Построить круговые диаграммы оценок на экзаменах по остальным по дисциплинам
Лабораторная работа №3
Задание
Получите у преподавателя условия задач для решения. Составьте формулы для решения задач из вашего варианта задания. Проверьте работу формул на компьютере.
ПРИМЕРЫ РЕШЕНИЯ ЗАДАЧ
Задача 1
50 студентов приняли участие в олимпиаде по информатике. Сколько участников набрали от 75 до 100 баллов?
Решение
Исходные данные для решения данной задачи представляются в виде таблицы (рис.11).
Рис.11. Исходные данные для решения задачи 1
Задача заключается в подсчете количества значений, удовлетворяющих заданному условию (больше 75 и меньше 100).
В лабораторной работе №2 при подсчёте количества оценок «5», «4», «3», «2» мы использовали функцию СЧЁТЕСЛИ. В данном случае непосредственное использование этой функции невозможно, так как не допускает записи критерия в виде двойного неравенства.
Предлагаем следующий способ решения задачи. Сначала «пометим» единичками тех участников олимпиады, которые набрали интересующее нас число баллов. Для этого произведём промежуточные вычисления в столбце Е так, как это показано на рис. 12.
Рис. 12. Промежуточные вычисления для задачи 1
Теперь интересующих нас участников олимпиады можно пересчитать, если подсчитать количество единиц в ячейках столбца Е. Это можно сделать, по крайней мере, одним из двух способов :
1) вычислить сумму значений в столбце Е с помощью функции автосуммирования СУММ;
2) подсчитать количество единиц в столбце Е с помощью функции СЧЁТЕСЛИ.
Второй вариант окончательного решения задачи показан на рисунке 13.
Рис.13. Окончательные расчёты для задачи 1
Задача 2
В магазине действует система скидок: 5% - пенсионерам и 13% - всем покупателям 13-го числа каждого месяца. Вычислить для всех товаров цены с учетом соответствующих скидок.
Решение
Исходные данные для решения данной задачи представляются в виде таблицы на рисунке 14.
Рис. 14. Исходные данные для задачи 2
Цена со скидкой рассчитывается как разница между начальной ценой и размером скидки.
Расчеты производятся по несложным формулам, показанным на рисунке 15.
Рис. 15. Расчет цен со скидками
Задача 3
В магазине одежды цена на товар, который не был реализован в течение 90 дней, снижается вдвое. Организовать автоматический пересчет цен на текущий день.
Решение
Исходные данные для решения данной задачи представляются в виде таблицы (рис.16).
Рис.16. Исходные данные для задачи 3
Для того, чтобы не исправлять расчётные формулы каждый раз при изменении текущей даты, сегодняшнюю дату вводим в ячейку Е4, и будем на нее ссылаться.
Цена на сегодняшний день зависит от разности дат (даты поступления в продажу и сегодняшней даты). EXCEL позволяет выполнять над датами операцию вычитания (используется встроенный календарь). Для того, чтобы при копировании формулы в ячейки столбца сохранить ссылку на одну и ту же ячейку Е4, используем её абсолютный адрес ($E$4). Таблица с формулой приведена на рис. 3.5.
Копируем формулу в остальные ячейки столбца D и получаем результаты расчетов.
Рис.17. Формула для задачи 2
Рис.18. Результаты расчетов для задачи 2
Задачи для самостоятельного решения
Задача 1
Известны результаты студенческой олимпиады по информатике. Сколько участников олимпиады набрали больше 60-ти баллов?
Задача 2
В магазине, торгующем одеждой, цена на товар, который не был реализован в течение 90 дней, снижается на 25%. Организовать автоматический пересчет цен на текущий день.
Задача 3
Известны данные о начислении стипендии студентам группы. Определить количество студентов, которые не получают стипендию.
Задача 4
При выполнении срочного заказа в ателье стоимость работ увеличивается на 15%. Срочным считается выполнение заказа не больше, чем за 3 дня. Организовать автоматическое вычисление фактической стоимости заказов в зависимости от срока выполнения.
Задача 5
Научная литература в библиотеке университета выдается читателям не более чем на 30 дней. При несвоевременном возврате книг взимается штраф в размере 10 рублей за каждый просроченный день. Организовать автоматическую проверку сроков возврата книг и начисление штрафа.
Задача 6
Известны значения среднесуточной температуры воздуха по дням месяца. Сколько было дней с положительной среднесуточной температурой?
Задача 7
При перевыполнении работником предприятия плана по обработке деталей на станке в конце месяца сверхплановые детали оплачиваются дополнительно (по 150 рублей за штуку). Организовать автоматическое начисление дополнительной оплаты сверхплановых деталей.
Контрольные вопросы