ФГБОУ ВПО
«Уфимский государственный авиационный технический университет»
Кафедра Информатики
| Решение задач с использованием MS Office |
| Текстовый процессор MS Word |
| и электронная таблица MS Excel |
ПОЯСНИТЕЛЬНАЯ ЗАПИСКА
| к контрольной работе по | информатике |
| 1308.401001.000ПЗ |
| (обозначение документа) |
| Группа | Фамилия И., О. | Подпись | Дата | Оценка | ||
| Студент | ||||||
| Консультант | ||||||
| Принял |
Уфа 2018 г.
ФГБОУ ВПО «Уфимский государственный авиационный технический университет»
Рецензия
на курсовую (контрольную) работу по информатике студента гр. факультета
(фамилия, имя, отчество)
Тема работы: Решение задач с использованием MS Office
Вариант №: 1
Рецензент
(должность, ученая степень, фамилия, имя, отчество)
Замечания
По результатам решения задач:
По содержанию и оформлению пояснить записки:
«_____» ___________ 20____ г. ____________________________
(подпись)
Содержание
Введение.............................................................................................................. 4
1. Задание 1......................................................................................................... 5
2. Задание 2......................................................................................................... 8
3. Задание 3....................................................................................................... 11
3.1. Создание списка..................................................................................... 11
3.2. Сортировка списка................................................................................. 13
3.3. Установка фильтрации........................................................................... 14
3.4. Подведение промежуточных итогов..................................................... 16
3.5. Подведение общих итогов..................................................................... 16
3.6. Создание сводной таблицы.................................................................... 17
3.7. Отображение итоговых данных в виде диаграммы............................. 19
Заключение........................................................................................................ 20
Литература........................................................................................................ 21
Введение
За последние двадцать лет значительно возрос объём и оборот информации во всех сферах жизнедеятельности человека: экономической, финансовой, политической, духовной. И процесс накопления, обработки и использования знаний постоянно ускоряется. В связи с этим возникает необходимость использования автоматических средств, позволяющих эффективно хранить, обрабатывать и распределять накопленные данные.
В настоящее время, как индивидуальные пользователи, так и все предприятия испытывают настоятельную потребность в обработке информации с использованием современных компьютерных технологий, расширении аналитических работ, связанных с разработкой перспектив развития, комплексной оценкой эффективности применения различных форм хозяйствования, своевременной выработкой оперативных управленческих решений.
Системы обработки информации с использованием современных компьютерных технологий предназначены для обработки числовых, строковых, графических и других данных, характеризующих различные производственно-экономические, финансовые и статистические явления и объекты, и составления соответствующих управленческих документов и информационно-аналитических материалов.
В данной работе будет подробно рассмотрен вопрос о работе на персональном компьютере с использованием современных компьютерных технологий, основные приемы обработки информации в табличном процессоре Excel, который входит в состав офисного пакета Microsoft Office.
Задание 1
Для заданного числового ряда найти:
а) сумму (произведение) членов числового ряда при заранее известном их количестве;
б) сумму (произведение) членов числового ряда для заданной точности вычислений (считается, что заданная точность достигнута, когда абсолютное значение разности между двумя соседними слагаемыми (сомножителями) меньше заданной точности;
в) погрешности вычисления суммы (произведения) членов числового ряда с заданной точностью (абсолютную и относительную) по отношению к вычисленной сумме (произведению) при заранее известном количестве членов числового ряда.
Таблица 1
| № задания | Сумма (произведение) | Заданная точность |
| 1. | 
| 0,0001 |
Решение
На листе 1 рабочей книги Excel составлена таблица, по образцу, показанному в методичке, макет которой представлен на рис.1
Рис.1 Макет листа 1 рабочей книги Excel
С помощью команды «Переименовать» контекстного меню первый лист переименован в «Задание1»
В макет таблицы введены расчетные формулы (рис.2)
Рис.2 Таблица с расчетными формулами
После проведения расчетов построен график функции с помощью команды «Вставка-Диаграмма» главного меню Excel, а также введена формула к заданию с помощью редактора формул MS Equation. График составлен по данным столбца «Сумма членов ряда».
Для автоматического выделения ячейки, в которой достигнута требуемая точность решения использовалось условное форматирование.
Чтобы изменить формат ячейки на основании ее содержимого, выполните следующие действия.
1. Выделите форматируемые ячейки и выполните команду Формат – Условное форматирование. На экране появится диалоговое окно Условное форматирование, показанное на рис. 3.
2. Откройте второй раскрывающийся список и выберите оператор сравнения (между, меньше, больше и т. д.), который должен использоваться для проверки данных. Затем введите в третьем и четвертом полях ассоциированные значения, используемые при проверке данных (четвертое поле появляется не всегда — оно зависит от выбранного логического оператора).
3. Щелкните на кнопке Формат и сообщите Excel, как должны форматироваться ячейки с нужными значениями. Вы можете указать цвет и начертание шрифта, выбрать режим подчеркивания или перечеркивания. Завершив настройку, щелкните на кнопке ОК.
4. Щелкните на кнопке ОК, чтобы применить формат, или на кнопке А также, чтобы определить до двух дополнительных правил для ячейки. Например, можно определить еще один логический оператор [1, с.58-59].
Параметры условного форматирования показаны на рис.3
Рис.3 Параметры условного форматирования
Результат решения показан на рис.4
Рис.4 Результат решения
Задание 2
Решить задачу на обработку табличных данных, выполнив необходимые расчеты, а также построив графики (диаграммы).
| № задания | Содержание задания |
| 1. | Составить таблицу, содержащую информацию о стоимости пяти наименований товара в течение первой половины года. В таблицу включить данные на 1-е число каждого месяца. Определить: − средние и минимальные в течение полугода значения стоимости товара. Построить линейчатую диаграмму изменения стоимости товаров в течение полугода. − общую стоимость товаров по месяцам, процент стоимости каждого товара по месяцам по отношению к его средней стоимости за полгода. − количество месяцев, в которых каждого из товаров не было в продаже. |
Решение
На листе 2 рабочей книги MS Excel составим таблицу, показанную на рисунке 5, предварительно переименовав лист и присвоив ему имя «Задание 2».
Рис.5 Обработка табличных данных
В ячейку A1 введем название таблицы – «Месяцы», объединим ячейки интервала B2:F1, введем товар, выровняем текст по центру, изменим размер и начертание шрифта.
Первый столбец таблицы (столбец A) – «1-е числа месяца», в столбцах B2.C2.D2.E2.F2 вводим наименование товара.
В ячейке А9 введем средняя стоимость, ячейке B3-F8 введем стоимость товара, в ячейку B9 введем формулу =СРЗНАЧ(B3:B8),,скопируем формулу в ячейки С9;E9;D9;F9.В ячейку А9 введем мин.стоимость, в ячейку В10 введем формулу =МИН(B3:D8),скопируем в ячейки C10;D10;E10;F10.В ячейку А11 введем Общая стоимость, в ячейку В11 вводим формулу=СУММ(B3:B8) копируем ее в интервал С11-F11. В ячейки A12-A17 введем процент стоимости каждого товара в месяце, в ячейку В12 введем формулу =B3/$B$9 для расчета процента стоимости каждого товара по месяцам по отношению к его средней стоимости за полгода. Скопируем формулу в диапазон ячеек B12;F17. В ячейку А18 введем количество месяцев без продаж, в ячейку В18 введем формулу =СЧЁТЕСЛИ(B3:B8;"=не было в продаже") для расчета месяцев, в которых не было продаж. Скопируем формулу в ячейки С18;D18;E18;F18.
Отформатируем таблицу, используя различные типы линий для обозначения границ, как показано на рисунке 5.
Построим на этом же листе (лист «Задание 2») Линейчатую диаграмму изменения стоимости товаров в течение полугода.
Несвязанные интервалы можно выделить, используя манипулятор «мышь» при нажатой клавише «CTRL». После выделения интервалов запустим мастер диаграмм, и, выполняя шаги мастера, сформируем диаграмму, как показано на рисунке 6.
Установим защиту на ячейки, содержащие формулы, название таблицы, а также на ячейки заголовков таблиц (на рисунке 5 область исходных данных, в которой ячейки не должны быть защищены, выделена сплошной двойной линией). Выделим интервал ячеек и отменим блокировку ячеек (меню «Формат/ячейки, вкладка «Защита», убрать ключ «Защищаемая ячейка»). Защитим лист рабочей книги от изменений (меню «Сервис/Защита/Защитить лист»).
Рис.6 Линейчатая диаграмма изменения стоимости товаров в течении полугода
Задание 3
Описать технологию работы со списками в электронной таблице MS Excel (создание списка, сортировка и фильтрация данных, подведение промежуточных и общих итогов, создание структуры данных, создание сводной таблицы и диаграммы) в пояснительной записке, оформленной в текстовом редакторе MS Word. В книге MS Excel привести примеры, демонстрирующие технологию работы со списками. Рисунки таблиц со списками (экранные формы таблиц MS Excel) включить в текст пояснительной записки.
Создание списка
Рекомендации по созданию списка на листе книги.
В Microsoft Excel имеется набор функций, облегчающих обработку и анализ данных в списке.
Список – это набор строк листа, содержащий однородные данные, например база данных счетов или набор адресов и телефонов клиентов. Первая строка в этом списке содержит заголовки столбцов.
Чтобы использовать эти функции, введите данные в список в соответствии с приведенными ниже рекомендациями.
ОРГАНИЗАЦИЯ СПИСКА
· Используйте только один список на листе. Избегайте создания более чем одного списка на листе. Некоторые возможности управления списком, например, фильтрация могут быть использованы только для одного списка.
· Помещайте подобные объекты в один столбец. Спроектируйте список таким образом, чтобы все строки содержали подобные объекты в одном столбце.
· Держите список отдельно. Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Microsoft Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений.
· Располагайте важные данные сверху или снизу от списка. Избегайте размещения важных данных слева или справа от списка; данные могут быть скрыты при фильтрации списка.
· Отображение строк и столбцов. Перед внесением изменений в список убедитесь в том, что все скрытые строки и столбцы отображены. Если строки или столбцы списка скрыты, данные могут быть непреднамеренно удалены.
ФОРМАТ СПИСКА
· Использование форматированных подписей столбцов. Создайте подписи столбцов в первой строке списка. Microsoft Excel использует подписи при создании отчетов, поиске и оформлении данных. Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных. Перед вводом подписей столбцов ячейкам должен быть присвоен текстовый формат.
· Использование границ ячеек. Для отделения подписей от данных следует использовать границы ячеек, а не пустые строки или прерывистые линии.
· Отсутствие пустых строк и столбцов. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.
· Отсутствие начальных и конечных пробелов. Дополнительные пробелы в начале и конце ячейки влияют на поиск и сортировку. Вместо ввода пробелов рекомендуется использовать сдвиг текста внутри ячейки.
· Расширенный список форматов и формул. При добавлении новых строк в конец списка Microsoft Excel использует согласованные форматы и формулы. Чтобы это произошло, три из пяти предыдущих ячеек должны иметь одинаковый формат. Для создания формулы в новой строке все предыдущие формулы должны совпадать.
Созданный список (рисунок 7) имеет 6 полей (столбцов) и 12 строк (11 записей и заголовок). Столбцы – различных типов: числовые, текстовые, процентные.
Рис.7 Исходные данные задания 3
Сортировка списка
Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Кроме этого, список можно сортировать по строкам. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Существует два порядка сортировки: по возрастанию и по убыванию.
Работа со списками.
Сортировка данных: выполнена командой Данные/Сортировка по видам изделий и затем по возрастанию затрат на производство. Параметры сортировки показаны на рис. 8. Результат на рисунке 9.
Рис.8 Параметры сортировки
Рис.9 Результат сортировки
Установка фильтрации
Фильтруя список можно отображать только те строки, которые удовлетворяют заданным условиям отбора. Например, список покупок, сделанных покупателями, можно отфильтровать, так что на экран будут выведены имена только тех покупателей, которые совершили покупки больше чем на 5000 долларов. В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр (команды Данные/Фильтр/Автофильтр и Данные/Фильтр/Расширенный фильтр).
При выполнении команды Данные/Фильтр/Автофильтр у каждого названия столбца появляется раскрывающийся список значений, который используется для задания условий фильтрации. Чтобы отфильтровать строки, содержащие определенное значение, необходимо открыть раскрывающийся список и выбрать необходимые значения. Чтобы применить операторы сравнения необходимо выбрать пункт Условие. В условии можно использовать операторы И (ИЛИ)
Чтобы отфильтровать список с помощью Расширенного фильтра, столбцы списка должны иметь заголовки. На листе также должно быть не менее трех пустых строк выше списка. Эти строки будут использованы в качестве диапазона условий отбора.
В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение.
Чтобы задать для отдельного столбца три или более условий отбора, введите условия в ячейки, расположенные в смежных строках.
Чтобы наложить условия отбора не несколько столбцов одновременно, необходимо ввести условия в ячейки, расположенные в одной строке диапазона условий.
Чтобы выбрать строки, удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, необходимо ввести условия в ячейки, расположенные в разных строках диапазона условий.
Чтобы наложить сложное условие отбора, необходимо ввести его составные части в отдельные строки диапазона условий.
В условии фильтрации можно использовать возвращаемое формулой значение. При задании формул в условиях нельзя использовать в качестве заголовка условия заголовки столбцов списка. Необходимо ввести заголовок, который не является заголовком столбца списка или оставить заголовок условия незаполненным.
Фильтрация данных:
Выполнена командой Данные/Фильтр/Автофильтр по столбцу «Вид продукции» со значением «начинается с с » (рисунок 10). Результат на рис.11
Рис.10 Настройка параметров фильтрации
Рис.11 Результат фильтрации
Фильтрация с помощью интервала критериев для команды «Расширенный фильтр»: Данные/Фильтр/Расширенный фильтр. Интервал критериев задается пользователем.
Результат фильтрации с помощью интервала критериев «Объем продаж, шт. >54» и «Прибыль, руб. >4000» (рисунок 12)
Рис.12 Результат фильтрации
Подведение промежуточных итогов
Microsoft Excel может автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Microsoft Excel изменяет разметку списка, что позволяет отображать и скрывать строки каждого промежуточного итога.
Перед тем как вставить промежуточные итоги, необходимо отсортировать список, чтобы сгруппировать строки, по которым нужно подвести итоги. После этого можно подсчитать промежуточные итоги любого столбца, содержащего числа.
Если данные не организованы в виде списка или если требуется подвести один промежуточный итог, вместо автоматических промежуточных итогов можно использовать Автосумму 
.
ВЫЧИСЛЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ
Для вычисления значений промежуточных итогов используется итоговая функция (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.), например СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены в списке с помощью нескольких типов вычислений одновременно.
ПРИМЕЧАНИЕ: Автоматические вычисления – значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении подробных данных.
Подведение общих итогов
Общие итоги подводятся с помощью подробных данных, а не с помощью значений промежуточных итогов. Например, итоговая функция СРЗНАЧ возвращает среднее значение для всех строк списка, а не для промежуточных итоговых значений.
Создание итоговых отчетов. При добавлении в список промежуточных итогов разметка списка изменяется таким образом, что становится видна его структура. Щелкая знаки структуры 
, 
и 
, можно создать итоговый отчет, скрыв подробности и отобразив только итоги.
Перед подведением итогов по заданным элементам (столбцам) списка (столбец «Вид продукции») сначала провести сортировку. Затем выбрать в меню «Данные» команду «Итоги»;
В списке «При каждом изменении» выбрать поле (Вид продукции), по значениям которого нужно сгруппировать строки;
В списке «Использовать функцию» («Операция») выбрать функцию (Сумма), при помощи которой подводились итоги.
В группе флажков Добавить итоги указать поле (Объем продаж, Прибыль, Затраты на производство), по которым вычислялись итоги. Результат на рис.13
Рис.13 Результат подведения итогов