Практическое использование возможностей MS Word и Excel

Министерство РФ по связи и информатизации Поволжская государственная университет телекоммуникаций и информатики ИНФОРМАТИКА И ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА КУРСОВАЯ РАБОТА ПО ИНФОРМАТИКЕ

«ПРАКТИЧЕСКОЕ ИСПОЛЬЗОВАНИЕ ВОЗМОЖНОСТЕЙ MS WORD И EXCEL»

Самара

2010 г.

1. Цели и задачи курсовой работы

2. Выбор варианта

3. Описание выполнения курсовой работы Заключение Список используемой литературы

1. Цели и задачи курсовой работы

Целью данной курсовой работы является изучение операционной системы Windows, компонентов MS Word и Excel и получение практических навыков работы с современными информационными технологиями. Получение представления о формировании табличной базы данных и о возможностях при работе с ней на примере базы данных в MS Excel.

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

— в MS Excel создать базу данных табличного типа для двух объектов (фирм, типов оборудования, конструкций и т. д.);

— отсортировать базу данных согласно задания;

— рассчитать требуемые показатели;

— спрогнозировать характер изменения объёма продажи оборудования на последующие шесть месяцев;

2. Выбор варианта

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

Два сравниваемых объекта выбраны по M — последней цифре зачётной книжки и данным таблицы, приведённой в ПРИЛОЖЕНИИ 1 методических указаний к выполнению курсовой работы. Стоимость анализируемого оборудования выбрана с учётом предпоследней цифры N зачётной книжки студента. Обработка базы данных произведена по цифрам M, N и в, где в — это сумма последней (M) и предпоследней (N) цифр зачётной книжки с отбрасыванием разряда десятков. Таким образов для номера зачетной книжки 93 460 код MNв имеет вид 066.

3. Описание выполнение курсовой работы

На основе исходных данных создана база данных A10: G2 по двум типам оборудования: Pentium и Intel. Начальные строки оставляются для ввода дополнительной информации, например, для создания таблицы критериев. Исходная база данных представлена в таблице 1.

Таблица 1

Первые три столбца (А, В, С) заполнены соответствующими данными согласно заданного варианта. Для определения розничной цены (столбец D) используется генератор случайных чисел. Случайное число в диапазоне от 0 до 1 получается с помощью функции. Для получения случайного числа в пределах от p до q используется формула. Согласно заданию величина случайного числа задается в диапазоне от 0,1*N до 0,5*N. Соответственно, данные столбца D рассчитаны по формуле, где n — номер строки. Затем содержимое столбца D скопировано в соседний столбец E, используя команду «специальная вставка» и флаг «значения», после чего столбец D скрывается одноименной командой. Ячейке E10 присвоено имя «Розн. цена (у.е.)»

В ячейку А2 введено значение курса 1 у.е. в рублях, который используется при расчёте стоимости аппаратуры в рублях (столбец F). Ячейке А2 присвоено имя «Курс». Стоимость оборудования по рыночной цене в рублях рассчитывается по формуле =En*A$ 2, где n — номер строки. Ячейке A$ 2 присвоен абсолютный адрес по номеру строки. Полученные данные в столбцах E и F округлены с точностью до центов и копеек соответственно командой «Формат ячеек» с выбором числового формата «Денежный», число знаков после запятой выставлено равным двум.

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

Таблица 2

Используя статистические функции, определены минимальная, максимальная цена оборудования, среднее значение и количество оборудования для двух типов товаров. Для получения этих данных на листе 2 рабочей книги Excel построена таблица. Ячейке A1 присвоено имя «Наименование объекта». Ячейкам A3 и A4 присвоено значение ячеек с наименованием оборудования A10 и A19 соответственно. Ячейкам B1, B2, C2, D2, E1 назначено имя «Цена оборудования (руб)», «МИН», «МАКС», «СРЗНАЧ», «Количество (шт)» соответственно. Диапазоны ячеек A1: A2, B1: D1 и E1: E2 объединены в одну командой «Объединение ячеек».

Минимальная цена оборудования определяется с помощью функции МИН () по формуле =МИН (Лист1!F11:F18) для первого типа оборудования (Pentium MMX) и =МИН (Лист1!F21:F27) для второго (Intel), где «Лист1!F11:F18» и «Лист1!F21:F27» — диапазоны цен на соответствующий тип оборудования. Максимальная цена определяется с использованием функции МАКС () по формулам =МАКС (Лист1!F11:F18) и =МАКС (Лист1!F21:F27), среднее значение — =СРЗНАЧ (Лист1!F11:F18) и =СРЗНАЧ (Лист1!F21:F27). Количество оборудования считается по формулам =СЧЁТ (Лист1!F11:F18) и =СЧЁТ (Лист1!F21:F27). Результаты полученных значений приведены в таблице 3. Данная база данных с отображением хода решения показана в таблице 4.

Таблица 3

Таблица 4

Используя соответствующие функции базы данных, определяются стоимость и название оборудования по условию K. Согласно варианту по условию K необходимо найти оборудование, имеющее наибольшее отклонение от максимального значения стоимости объектов 2-го вида. Для этого добавляется столбец G к основной базе данных, в который вносится абсолютное значение разности «Цена (руб)» и «СРЗНАЧ», и определяется его минимум. Ячейке G10 присвоено имя |Ц-по К|. Значения данного столбца считаются по формуле =ABS (Fn-Лист2!C$ 4), где n — номер строки, Лист2!C$4 — абсолютный адрес по строке средней цены на товар второго типа (Intel). Для исключения появления отрицательных значений введена функция абсолютной величины числа ABS (). Данная база данных с формулами приведена в таблице 5.

Таблица 5

Название оборудования и его цена определяется с помощью функции для работы с базами данных БИЗВЛЕЧЬ. Для определения оборудования, имеющего наибольшее отклонение от максимального значения стоимости объектов 2-го вида составляется таблица критериев для выбора из базы данных. Первая и вторая строка критерия содержит имя поля критерия, третья — значение, по которому идет выбор. Ячейкам B1, E1 присвоено имя «По критерию K», «Критерии». B2, C2 — непосредственно ячейки выборки, им назначено имя «Процессор Pentium», «Цена». Диапазоны ячеек B1: C1 и D1: H1 объединены в одну ячейку командой «Объединение ячеек»

В ячейках E3 и F3 указан критерий, исключающий возможное совпадение стоимости по условию К с минимальным и максимальным значениями объектов 1-го вида. E2 и F2 присвоено значение F10. Так как необходимо производить выборку только с оборудованием второго вида, ячейкам G2 и G3 присваивается содержимое B10 и B11. Для определения минимальной разницы между средней ценой на оборудование первого вида и розничной ценой, ячейке H2 назначено имя столбца G базы данных. Выборка осуществляется посредством функции возврата минимальных значений базы данных ДМИН () по формуле =ДМИН (A10:G27;G10;E2:G3), где A10:G27 — диапазон базы данных, G10 — заголовок столбца базы данных, в котором внесены абсолютные значения разности «Цена (руб)» и «СРЗНАЧ», E2:G3 — критерии выборки. В результате вычислений в ячейке H3 получено значение с минимальным отклонением от средней цены, составляющее 2992,84.

Выбор товара, удовлетворяющему критерию K и его розничной цены осуществляются по формулам =БИЗВЛЕЧЬ (A10:G27;A10;E2:H3) и =БИЗВЛЕЧЬ (A10:G27;F10;E2:H3), где A10:G27 — диапазон базы данных, A10 и F10 — заголовок столбца базы данных, в котором внесены наименование продукта и цена в рублях соответственно, E2:H3 — критерии выборки. Таким образом, товаром, удовлетворяющим критерию K, является компьютер AMDK6II333MHz/ZIDATX98/DIMM16MbSDRAM10ns. Цена оборудования составляет 5023,074 руб. Таблица критериев с вычислениями и формулами представлены в таблицах 6 и 7 соответственно.

Таблица 6

Таблица 7

Чтобы проанализировать характер изменения объема продажи оборудования на Листе 3 составлена новая база данных, в которой отражены три наименования оборудования: с минимальной, максимальной стоимостью и по условию К. Наименование оборудования извлечены из таблицы 1 в соответствующие ячейки (B1, D1, F1) новой базы данных. Для этой цели в таблице с критериями введены 2 дополнительных столбца I и J, являющиеся условиями для выбора минимальной и максимальной цен на процессоры Pentium соответственно.

Ячейкам I2 и J2 присваивается значение F10. Вычисление максимальной и минимальной цены на оборудование производится посредством функция возврата минимального и максимального значения базы данных по заданным критериям ДМИН () и ДМАКС () по формулам =ДМИН (A10:G27;F10;G2:G3) и =ДМАКС (A10:G27;F10;G2:G3). Критерием в данном случае является только тип оборудования. Таблица критериев с вычисленными значениями и формулами, по которым проводились вычисления, приведена в таблицах 8 и 9.

Таблица 8

Таблица 9

Наименование оборудования извлекается функцией БИЗВЛЕЧЬ с указанием критериев._БИЗВЛЕЧЬ (Лист1!A10:G18;Лист1!A10;Лист1!I2:I3) =БИЗВЛЕЧЬ (Лист1!A10:G18;Лист1!A10;Лист1!J2:J3), =БИЗВЛЕЧЬ (Лист1!A10:G18;Лист1!A10;Лист1!H2:H3) — формулы выборки оборудования по минимальной, максимальной цене и по условию K. Соответственно в ячейках B1, D1, F1 следующие значения «CyrixMII300GP/ZIDATX98/DIMM16MbSDRAM10ns», «AMDK6II400MHz/ACORP5ALI61/DIMM16MbSDRAM10ns» и «AMDK6II333MHz/ZIDATX98/DIMM16MbSDRAM10ns». Диапазоны ячеек B1: C2, D1: E2, F1: G1 объединены командой «Объединение ячеек».

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

B ячейки H2: H7, I2: I7 и J2: J7 занесены случайные числа с учетом задания, которые соответствуют объёмам продаж оборудования по требуемой стоимости. Случайные числа берутся в диапазоне NM:1NM для оборудования с максимальной стоимостью, в диапазоне NM:2NM — для оборудования со стоимостью по условию К и в диапазоне NM:3NM — для оборудования с минимальной стоимостью. Так для исходного варианта, где M=0, N=6:

— в ячейки H2: H7 заносятся случайные числа от 06 до 306;

— в ячейки I2: I7 — 06−206;

— в ячейки J2: J7 — 06−106.

Для получения целочисленных значений используется функция ОКРУГЛ для введенных случайных чисел =ОКРУГЛ (СЛЧИСЛ (), 0).Таким образом формулы для определения случайного числа для оборудования с минимальной и максимальной стоимостью, а также по условию К будут иметь вид =ОКРУГЛ ((СЛЧИС ()*(360−260)+60);0), =ОКРУГЛ ((СЛЧИС ()*(160−60)+60);0) и =ОКРУГЛ ((СЛЧИС ()*(260−60)+60);0) соответственно.

Полученные данные столбцов H, J и I скопированы в соответствующие ячейки столбца «Объёмы продаж (шт.)» В3: В8, D3: D8 и F3: F8, используя команду «специальная вставка» и флаг «значения». Столбцы H, J и I скрываются командой «Скрыть». Стоимость продажи трёх видов процессоров Pentium рассчитывается исходя из объёма его продажи и стоимости за единицу оборудования. Стоимость оборудования извлекается из основной базы данных на Листе 1 функцией БИЗВЛЕЧЬ. В итоге стоимость оборудования по объему продаж за месяц определяется по формуле =БИЗВЛЕЧЬ (Лист1!A$ 10:G$ 18;Лист1!F$ 10;Лист1!I$ 2:I$ 3)*Bn — для оборудования с минимальной стоимостью, =БИЗВЛЕЧЬ (Лист1!A$ 10:G$ 18;Лист1!F$ 10;Лист1!J$ 2:J$ 3)*Dn — для оборудования с максимальной стоимостью, =БИЗВЛЕЧЬ (Лист1!A$ 10:G$ 18;Лист1!F$ 10;Лист1!H$ 2:H$ 3)*Fn — для оборудования, выбранного по условию K, где n— номер строки. При вводе формул адреса ячеек указываются с абсолютным по строке адресом. Полученная база данных с вычисленными значениями и формулами решения приведена в таблице 10.

Таблица 10

Используя данные таблицы 4 и «Мастер диаграмм», построена диаграмма «Объём продаж компьютеров Pentium по максимальной стоимости и по условию K» продажи процессоров CyrixMII333GP и AMDK6II400MHz (оборудование c максимальной стоимостью и по условию K) за предшествующие 6 месяцев (с ноября 2009 года по апрель 2010 года).

На диаграмме отражены ее название, название осей, легенда, надпись (наименование оборудования). При построении диаграммы использовался тип «График». В качестве рядов диаграммы выбраны диапазоны ячеек B3: B8 (CyrixMII333GP) и F3: F8 (AMDK6II400MHz), в качестве категорий выбраны ячейки A3: A8 (месяцы). Надпись диаграммы «Компьютеры Pentium» выполнена путем ссылки на ячейку A10 Листа 1. Диаграмма показана на рисунке 1.

Рисунок 1 — Диаграмма «Объём продаж компьютеров Pentium по максимальной стоимости и по условию K»

Прогноз продажи соответствующего оборудования за 6 последующих месяцев отображаются в ячейках В9: В14, D9: D14 и F9: F14.

Для прогноза продажи процессоров CyrixMII300GP используется функция РОСТ, процессоров AMDK6II400MHz — функция ТЕНДЕНЦИЯ и процессоов AMDK6II333MHz — Арифметическая прогрессия. Для этого в ячейку В9 вводится формула =РОСТ (B3:B8;A3:A8;A9;1) с последующим заполнением всех ячеек столбца В.

Прогноз с помощью функции ТЕНДЕНЦИЯ ячеек D9: D14 осуществляется аналогично. Прогноз продажи оборудования (ячеек F9: F14) функцией ПРОГРЕССИЯ производится с помощью команды «Автозаполнение». Результат вычисления отражен в таблице 10.

Таблица 11

По результатам полученной базы данных с помощью «Мастера диаграмм» построена диаграмма «Суммарная стоимость продаж компьютеров Pentium по минимальной стоимости и по условию K», приведенная на рисунке 2. На диаграмме, кроме того, отображаются соответствующие линии тренда, аппроксимирующие зависимость стоимости для выбранного типа компьютеров CyrixMII333GP и AMDK6II400MHz. При построении диаграммы использовался тип «Гистограмма». В качестве рядов диаграммы выбраны диапазоны ячеек С3: С14 (CyrixMII333GP) и F3: F14 (AMDK6II400MHz), в качестве категорий выбраны ячейки A3: A14 (месяцы). Надпись диаграммы «Компьютеры Pentium» выполнена путем ссылки на ячейку A10 Листа 1.

Для компьютера с наименьшей стоимостью AMDK6II400MHz выбрана полиноминальная линия тренда, для процессора, выбранного по условию K, CyrixMII333GP, — 2-х линейный фильтр, для данного тренда выведены уравнение y = 2E-07×6 — 0,038×5 + 3910, x4 — 2E+08×3 + 6E+12×2 — 1E+17x + 7E+20 и величина достоверности аппроксимации RІ = 0,845.

Рисунок 2- Диаграмма «Суммарная стоимость продаж компьютеров Pentium по максимальной стоимости и по условию K»

По результатам данной диаграммы можно сделать следующие выводы:

— компьютеры, выбранные по условию K, CyrixMII333GP продаются в большем объеме, в сравнении с процессорами с минимальной стоимостью AMDK6II400MHz;

— закон изменения стоимости оборудования AMDK6II400MHz — полиномиальный CyrixMII333GP — скользящее среднее (2 линейный фильтр);

— коэффициент аппроксимации R2 близок к единице, что указывает на высокую степень достоверности выбранного закона.

Рассчитаем суммарную стоимость оборудования, выбранного по условию K (таблица 11, 12), т. е. для компьютеров CyrixMII333GP, за те месяцы, в которые объем продаж оборудования не превышает 1NM (согласно варианту, не превышает 260), воспользовавшись функцией базы данных БДСУММ (). Для этого на Листе 3 в ячейки K2 и K3 занесем критерий «Объём продаж по условию K (шт) <260». В свободную ячейку, например K2 скопируем содержимое ячейки F2 «Объём продаж по условию K (шт)», в ячейку K3 занесём условие «<260». В другую свободную ячейку, например K4, введём функцию =БДСУММ (A2:G14;C2;L2:L3)

Для вывода месяца, с наибольшей суммой продажи оборудования, выбранного по условию К (таблица 12, 13), используем функцию базы данных БИЗВЛЕЧЬ () и критерий «Стоимость по условию К (руб) 1 180 422,39». Для этого в ячейку, L2 скопируем содержимое ячейки G2, а в ячейке L3 введем критерий =МАКС (G3:G14). В ячейке L4, используя формулу =БИЗВЛЕЧЬ (A2:G14;A2;L2:L3) получим необходимый месяц.

Таблица 12

Таблица 12

Заключение

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

Список используемой литературы

Информатика. Базовый курс / Симонович и др. — СПб: «Питер», 2000.

Берлинер Э.М., Глазырин Б. Э., Глазырина И. Б. Офис от Microsoft.- М.: ABF, 1997.

Дж. Кокс и др. Microsoft Excel 97. Краткий курс. Пособие ускоренного обучения — СПБ.: Питер, 1998.

Электронно-методическое пособие «Word 97».

Электронно-методическое пособие «Excel 97».

Конспект лекций по дисциплине «Информатика».

Алексеев А.П., Камышенков Г. Е. Использование ЭВМ для математических расчетов. Самара: ПГАТИ, 1998.