Обработка данных в excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ

РОССИЙСКОЙ ФЕДЕРАЦИИ

 

Государственное образовательное учреждение

высшего профессионального образования

«Юго-Западный государственный университет»

 

 

Кафедра информатики и прикладной математики

 

УТВЕРЖДАЮ

Первый проректор-

проректор по учебной работе

______________________Е.А.Кудряшов

«___»______________2011 г.

 

 

Обработка данных в excel

Методические указания к лабораторным работам по курсам «Информатика и математика» и «Математика и информатика»   КУРСК 2011

ВВЕДЕНИЕ

 

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

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

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

Наиболее широкое применение электронные таблицы нашли в экономических и бухгалтерских расчетах, но и в научно-технических задачах электронные таблицы можно использовать эффективно, например для:

• проведения однотипных расчетов над большими наборами данных;

• автоматизации итоговых вычислений;

• обработки результатов экспериментов;

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

• подготовки табличных документов;

• построения диаграмм и графиков по имеющимся данным.

Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа 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 играет роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке.

Сделать активной другую ячейку (переместить курсорную рамку) можно несколькими способами:

• щелкнуть мышью в нужной ячейке;
• использовать навигационные клавиши - стрелки;
• нажать клавишу < Enter>.

 

Ячейки и их адресация.На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки (адрес ячейки) состоит из имени столбца и номера строки, на пересечении которых она расположена, например: А1 или D234. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках.

Большинство адресов в формулах записываются в относительной форме - например, С3 (столбец)(строка) .

Относительнымиадресами называются адреса, которые при копировании в составе формулы в другую ячейку автоматически изменяются . При копировании формулы с относительным адресом (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)

В большинстве случаев это очень удобно, но иногда этого не требуется. Некоторые адреса в формулах записываются в абсолютной форме - например, $С$3.

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

 

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

А1 :С15.

 

Типы данных.Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число или формула, а также оставаться пустой. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные рассматриваются как текст.

Признаком формулыявляется знак равенства «=».

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

ВВОД И ФОРМАТИРОВАНИЕ ДАННЫХ. Вычисления по формулам

 

Задание 1

 

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

 

 

 

Рис. 2. Таблица с результатами сессии

Ввод и форматирование данных.Технология работы

Введите заголовок «Результаты … » в ячейку А1. Поскольку соседние ячейки свободны, заголовок займет столько места, сколько нужно.   Расположение текста в ячейке в несколько строк. В ячейки строки 2 надо ввести заголовки столбцов. Обратите внимание на…

Форматирование таблицы. Выделите всю таблицу и выровняйте данные в ячейках по центру . Можно использовать значок на панели инструментов или команду ФОРМАТ а ЯЧЕЙКИ аВЫРАВНИВАНИЕ а ПО ЦЕНТРУ.

Задайте жирный шрифт для заголовков столбцов.

 

Сохранение результатов работы на диске. Сохраните таблицу в рабочем каталоге своей группы с помощью команды ФАЙЛ → СОХРАНИТЬ.

 

Задание 2

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

 

Вычисление средних значений. Технология работы

В ячейку F3 надо ввести формулу: = СРЗНАЧ(B3;C3;D3;E3) Обратите внимание на то, чтоимя функции состоит из русских букв, а адреса ячеек включают в себя английские буквы.

Задание 3

На основе таблицы на рис.2 построить таблицу, показанную на рис.3.

 

 

Рис.3. Ведомость для начисления и выплаты стипендии

 

Установите курсор в ячейку А19 и наберите название таблицы «Ведомость для начисления и выплаты стипендии».

В ячейку В20 введите текст Фамилия студента. Расположите этот текст в 2 строки, как было описано выше. В ячейку С20 введите текст Стипендия.

Копирование данных из одной таблицы в другую. Технология работы

 

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

1) С помощью мыши выделите ячейки с адресами от А3 до А15.

2) Нажмите правую кнопку мыши и в появившемся контекстном

меню выберите команду Копировать.

3) Установите курсор в ячейку А21, нажмите правую кнопку мыши и в появившемся меню выберите команду ВСТАВИТЬ.

Задание 4

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

 

Использование логической функции ЕСЛИ. Технология работы

Для определения размера стипендии воспользуемся логической функцией ЕСЛИ

 

ЕСЛИ ( условие; значение_если_истина; значение_если_ ложь).

 

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

используем абсолютный адрес ячейки $Е$22.

В ячейку С21 введите формулу

 

=ЕСЛИ(F3>=3,5 ; $E$22; 0)

 

и нажмите клавишу ENTER.

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

Задание 5

Вычислить суммарный размер стипендии группы.

 

Автосуммирование. Технология работы

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

Установите курсор в ячейке С34 и на панели инструментов щелкните мышью по кнопке с изображением знака суммы . В ячейке появится формула

 

=СУММ (С21:С33)

 

Нажмите ENTER для получения результата. Так как размер стипендии выражается в рублях, установите соответстующий формат ячейки. Выделите ячейку С35 и выполните команду ФОРМАТ – Ячейки – Число – Денежный.

 

Задание 6

Сохраните полученную таблицу в папке вашей группы и на резервном носителе ( CD/RW, флэш). Данная таблица используется для выполнения следующей лабораторной работы №2.

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

РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ EXCEL. Построение диаграмм

 

Исходные данные - файл (рабочая книга), полученный в результате выполнения лабораторной работы №1.

 

Задание 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. Гистограмма средних баллов студентов

 

Задание 2

 

Построить гистограмму средних баллов группы по предметам.

 

Технология работы

Исходные данные для гистограммы средних баллов по предметам – это ячейки с названиями предметов (B2, C2, D2, E2) и ячейки со значениями соответствующих средних баллов (B17, C17, D17, E17). Выделите эти ячейки, пользуясь описанной выше технологией. Вызовите Мастер диаграмм и выполните действия, аналогичные описанным выше. В результате у вас должна получиться следующая диаграмма (рисунок 8).

 

 

Рис.8. Гистограмма средних баллов по предметам

 

Задание 3

Определить, сколько оценок «отлично», «хорошо», «удовлетворительно» и «неудовлетворительно» получили студенты группы на экзаменах по каждому из предметов.

На том же рабочем листе постройте таблицу, аналогичную таблице, представленной на рис.9.

 

Рис.9. Таблица для подсчёта количества оценок «отлично», «хорошо», «удовлетворительно» и «неудовлетворительно»

Подсчет количества значений, удовлетворяющих заданному условию. Технология работы

 

Для определения количества оценок воспользуемся статистической функцией

 

СЧЁТЕСЛИ (диапазон ячеек ; критерий)

Диапазон ячеек задает область таблицы, в которой подсчитывается число значений, удовлетворяющих заданному условию (критерию). Например, для определения количества оценок «отлично» по предмету «Правоохранительные органы» в ячейку В42 введите формулу : =СЧЁТЕСЛИ(B3:B16 ; 5)и нажмите клавишуEnter.

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

 

Задание 4

 

Построить круговую диаграмму оценок на экзамене по дисциплине «Правоохранительные органы».

 

Построение круговой диаграммы. Технология работы

Круговая диаграмма используется для графического представления процентного соотношения данных, которые в сумме образуют 100%. Для того, чтобы показать графически процентное соотношение оценок студентов по дисциплине «Правоохранительные органы», надо выделить ячейки с заголовком данной таблицы (B41, C41, D41, E41), затем нажать клавишу Ctrl,и удерживая ее, выделить ячейки с количеством оценок по этой дисциплине (B42, C42, D42, E42).

Вызовите Мастер диаграмм. В окне Мастера диаграмм «,Шаг 1 из 4: Тип диаграммы» выберите в списке названий тип диаграммы «Круговая» и любое изображение круговой диаграммы. На шаге 2 просто нажмите кнопку «Далее». На шаге 3 выберите вкладку «Заголовки» и в поле «Название диаграммы» введите текст «Правоохранительные органы». В том же окне перейдите на вкладку «Подписи данных» и поставьте флажок в пункте «Доли». Нажмите кнопку «Готово». Должна получиться диаграмма как на рис. 10.

 

 

 

Рис. 10. Круговая диаграмма оценок на экзамене по дисциплине «Правоохранительные органы».

 

Задание 5

 

Построить круговые диаграммы оценок на экзаменах по остальным по дисциплинам

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

РЕШЕНИЕ ПРИКЛАДНЫХ ЗАДАЧ С ПОМОЩЬЮ EXCEL

 

Задание

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

 

ПРИМЕРЫ РЕШЕНИЯ ЗАДАЧ

Задача 1

50 студентов приняли участие в олимпиаде по информатике. Сколько участников набрали от 75 до 100 баллов?

Решение

   

Задача 2

 

В магазине действует система скидок: 5% - пенсионерам и 13% - всем покупателям 13-го числа каждого месяца. Вычислить для всех товаров цены с учетом соответствующих скидок.

 

Решение

 

Исходные данные для решения данной задачи представляются в виде таблицы на рисунке 14.

 

 

Рис. 14. Исходные данные для задачи 2

Цена со скидкой рассчитывается как разница между начальной ценой и размером скидки.

Расчеты производятся по несложным формулам, показанным на рисунке 15.

 

 

Рис. 15. Расчет цен со скидками

 

Задача 3

 

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

Решение

Исходные данные для решения данной задачи представляются в виде таблицы (рис.16).  

Задачи для самостоятельного решения

 

Задача 1

 

Известны результаты студенческой олимпиады по информатике. Сколько участников олимпиады набрали больше 60-ти баллов?

 

 

Задача 2

 

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

 

 

Задача 3

 

Известны данные о начислении стипендии студентам группы. Определить количество студентов, которые не получают стипендию.

 

 

Задача 4

 

При выполнении срочного заказа в ателье стоимость работ увеличивается на 15%. Срочным считается выполнение заказа не больше, чем за 3 дня. Организовать автоматическое вычисление фактической стоимости заказов в зависимости от срока выполнения.

 

 

 

Задача 5

 

Научная литература в библиотеке университета выдается читателям не более чем на 30 дней. При несвоевременном возврате книг взимается штраф в размере 10 рублей за каждый просроченный день. Организовать автоматическую проверку сроков возврата книг и начисление штрафа.

 

 

 

Задача 6

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

 

 

 

Задача 7

При перевыполнении работником предприятия плана по обработке деталей на станке в конце месяца сверхплановые детали оплачиваются дополнительно (по 150 рублей за штуку). Организовать автоматическое начисление дополнительной оплаты сверхплановых деталей.

 

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

1. Что такое относительный адрес ячейки?

2. Что такое абсолютный адрес ячейки?

3. Что такое диапазон ячеек?

4. В каких случаях используют абсолютные адреса ячеек?

5. Как изменить высоту строки электронной таблицы?

6. Как изменить ширину столбца электронной таблицы?

7. Как в ячейке таблицы расположить текст в несколько строк?

8. Какая функция используется для вычисления среднего арифметического значения содержимого ячеек?

9. Как скопировать формулу из одной ячейки в другую?

10. Как вставить в ячейку функцию автосуммирования?

11. Какая функция используется для проверки логического условия?

12. Какая функция используется для подсчета количество значений, удовлетворяющих заданному условию?

13. Как построить гистограмму?

14. Какие данные можно представить в виде круговой диаграммы?

15. Как построить круговую диаграмму?