Практическое использование возможностей MS Word и Excel
19
Министерство РФ по связи и информатизации
Поволжская государственная университет телекоммуникаций и информатики
ИНФОРМАТИКА И ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА
КУРСОВАЯ РАБОТА ПО ИНФОРМАТИКЕ
«ПРАКТИЧЕСКОЕ ИСПОЛЬЗОВАНИЕ ВОЗМОЖНОСТЕЙ 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) цифр зачётной книжки с отбрасыванием разряда десятков. Таким образов для номера зачетной книжки 093460 код MNв имеет вид 066.
3. Описание выполнение курсовой работы
На основе исходных данных создана база данных A10:G2 по двум типам оборудования: Pentium и Intel. Начальные строки оставляются для ввода дополнительной информации, например, для создания таблицы критериев. Исходная база данных представлена в таблице 1.
Таблица 1
|
| A | B | C | |
10 | 1. Компьютеры Pentium с поддержкой MMX | Тип | Цена(у.е.) | |
11 | AMDK6II300MHz/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 264 | |
12 | AMDK6II333MHz/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 287 | |
13 | AMDK6II350MHz/ACORP5ALI61/DIMM16MbSDRAM10ns | Pentium | 286 | |
14 | AMDK6II400MHz/ACORP5ALI61/DIMM16MbSDRAM10ns | Pentium | 324 | |
15 | CyrixMII300GP/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 250 | |
16 | CyrixMII333GP/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 257 | |
17 | Pentium200MHzINTELMMX/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 263 | |
18 | Pentium233MHzINTELMMX/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 266 | |
20 | Компьютеры на процессоре Intel | Тип | Цена(у.е.) | |
21 | IntelCeleron300MHzbox/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 295 | |
22 | IntelCeleron333MHz/ACORP6LX87/DIMM16MbSDRAM10ns | Intel | 314 | |
23 | IntelCeleron400MHz/ACORP6LX87/DIMM16MbSDRAM10ns | Intel | 341 | |
24 | Pentium300MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 349 | |
25 | IntelCeleron366MHz/ACORP6LX87/DIMM16MbSDRAM10ns | Intel | 335 | |
26 | Pentium333MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 390 | |
27 | Pentium350MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 446 | |
|
Первые три столбца (А, В, С) заполнены соответствующими данными согласно заданного варианта. Для определения розничной цены (столбец 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
|
| A | B | C | D | E | |
1 | Курс | | | | | |
2 | 29,17 | | | | | |
3 | | | | | | |
… | | | | | | |
10 | Компьютеры Pentium с поддержкой MMX | Тип | Цена(у.е.) | Розн. цена (у.е.) | Цена (руб) | |
11 | CyrixMII300GP/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 250 | 150 | 4 375,50 | |
12 | CyrixMII333GP/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 257 | 154,2 | 4 498,01 | |
13 | Pentium200MHzINTELMMX/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 263 | 157,8 | 4 603,03 | |
14 | AMDK6II300MHz/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 264 | 158,4 | 4 620,53 | |
15 | Pentium233MHzINTELMMX/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 266 | 159,6 | 4 655,53 | |
16 | AMDK6II350MHz/ACORP5ALI61/DIMM16MbSDRAM10ns | Pentium | 286 | 171,6 | 5 005,57 | |
17 | AMDK6II333MHz/ZIDATX98/DIMM16MbSDRAM10ns | Pentium | 287 | 172,2 | 5 023,07 | |
18 | AMDK6II400MHz/ACORP5ALI61/DIMM16MbSDRAM10ns | Pentium | 324 | 194,4 | 5 670,65 | |
19 | Компьютеры на процессоре Intel | Тип | Цена(у.е.) | Розн. цена (у.е.) | Цена (руб) | |
20 | IntelCeleron300MHzbox/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 295 | 177 | 5 163,09 | |
21 | IntelCeleron333MHz/ACORP6LX87/DIMM16MbSDRAM10ns | Intel | 314 | 188,4 | 5 495,63 | |
22 | IntelCeleron400MHz/ACORP6LX87/DIMM16MbSDRAM10ns | Intel | 341 | 204,6 | 5 968,18 | |
23 | Pentium300MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 349 | 209,4 | 6 108,20 | |
24 | IntelCeleron366MHz/ACORP6LX87/DIMM16MbSDRAM10ns | Intel | 335 | 213 | 6 213,21 | |
25 | Pentium333MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 390 | 234 | 6 825,78 | |
26 | Pentium350MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 446 | 267,6 | 7 805,89 | |
27 | Pentium400MHz/ZIDABX98/DIMM16MbSDRAM10ns | Intel | 458 | 274,8 | 8 015,92 | |
| | | | | =En*A$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
|
| A | B | C | D | E | |
1 | Наименование объекта | Цена оборудования (руб) | Количество (шт) | |
2 | | МИН | МАКС | СРЗНАЧ | | |
3 | Процессоры Pentium | 4375,5 | 5670,65 | 4806,49 | 8 | |
4 | Процессоры Intel | 5163,09 | 8015,92 | 6449,49 | 8 | |
|
Таблица 4
|
| A | B | C | D | E | |
1 | Наименование объекта | Цена оборудования (руб) | Количество (шт) | |
2 | | МИН | МАКС | СРЗНАЧ | | |
3 | Процессоры Pentium | =МИН (Лист1!F11:F18) | =МАКС (Лист1!F11:F18) | =СРЗНАЧ (Лист1!F11:F18) | =СЧЁТ (Лист1!F11:F18) | |
4 | Процессоры Intel | =МИН (Лист1!F21:F27) | =МАКС (Лист1!F21:F27) | =СРЗНАЧ (Лист1!F21:F27) | =СЧЁТ (Лист1!F21:F27) | |
|
Используя соответствующие функции базы данных, определяются стоимость и название оборудования по условию K. Согласно варианту по условию K необходимо найти оборудование, имеющее наибольшее отклонение от максимального значения стоимости объектов 2-го вида. Для этого добавляется столбец G к основной базе данных, в который вносится абсолютное значение разности «Цена (руб)» и «СРЗНАЧ», и определяется его минимум. Ячейке G10 присвоено имя |Ц-по К|. Значения данного столбца считаются по формуле =ABS(Fn-Лист2!C$4), где n - номер строки, Лист2!C$4 - абсолютный адрес по строке средней цены на товар второго типа (Intel). Для исключения появления отрицательных значений введена функция абсолютной величины числа ABS(). Данная база данных с формулами приведена в таблице 5.
Таблица 5
|
| A | B | C | D | E | D | |
10 | Компьютеры Pentium с поддержкой MMX | Тип | Цена (у.е.) | Розн. цена (у.е.) | Цена (руб) | |Ц-по К| | |
11 | CyrixMII300GP | Pentium | 250 | 150 | 4 375,50 | 3 640,42 | |
12 | CyrixMII333GP | Pentium | 257 | 154,2 | 4 498,01 | 3 517,90 | |
13 | Pentium200MHz | Pentium | 263 | 157,8 | 4 603,03 | 3 412,89 | |
14 | AMDK6II300MHz | Pentium | 264 | 158,4 | 4 620,53 | 3 395,39 | |
15 | Pentium233MHz | Pentium | 266 | 159,6 | 4 655,53 | 3 360,38 | |
16 | AMDK6II350MHz | Pentium | 286 | 171,6 | 5 005,57 | 3 010,34 | |
17 | AMDK6II333MHz | Pentium | 287 | 172,2 | 5 023,07 | 2 992,84 | |
18 | AMDK6II400MHz | Pentium | 324 | 194,4 | 5 670,65 | 2 345,27 | |
19 | Компьютеры на процессоре Intel | Тип | Цена (у.е.) | Розн. цена (у.е.) | Цена (руб) | |Ц-по К| | |
20 | IntelCeleron300MH | Intel | 295 | 177 | 5 163,09 | 2 852,83 | |
21 | IntelCeleron333MHz | Intel | 314 | 188,4 | 5 495,63 | 2 520,29 | |
22 | IntelCeleron400MHz | Intel | 341 | 204,6 | 5 968,18 | 2 047,73 | |
23 | Pentium300MHz | Intel | 349 | 209,4 | 6 108,20 | 1 907,72 | |
24 | IntelCeleron366MHz | Intel | 335 | 213 | 6 213,21 | 1 802,71 | |
25 | Pentium333MHz | Intel | 390 | 234 | 6 825,78 | 1 190,14 | |
26 | Pentium350MHz | Intel | 446 | 267,6 | 7 805,89 | 210,02 | |
27 | Pentium400MHz | Intel | 458 | 274,8 | 8 015,92 | 0,00 | |
| | | | | =E11*A$2 | =ABS(Fn-Лист2!C$4) | |
|
Название оборудования и его цена определяется с помощью функции для работы с базами данных БИЗВЛЕЧЬ. Для определения оборудования, имеющего наибольшее отклонение от максимального значения стоимости объектов 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
|
| B | C | D | E | F | G | |
1 | По критерию K | Критерии | |
2 | Процессор Pentium | Цена | Цена (руб) | Цена (руб) | Тип | |Ц-по К| | |
3 | AMDK6II333MHz | 5023,074 | >4375,5 | <5670,648 | Pentium | 2 992,84 | |
|
Таблица 7
|
| B | C | D | E | F | G | |
1 | По критерию K | Критерии | |
2 | Процессор Pentium | =F10 | =F10 | =F10 | =B10 | =G10 | |
3 | =БИЗВЛЕЧЬ (A10:G27;A10;E2:H3) | =БИЗВЛЕЧЬ (A10:G27;F10;E2:H3) | >4375,5 | <5670,648 | =B11 | =ДМИН (A10:G27;G10;E2:G3) | |
|
Чтобы проанализировать характер изменения объема продажи оборудования на Листе 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
|
| I | J | |
1 | МИН | МАКС | |
2 | Цена (руб) | Цена (руб) | |
3 | 4375,5 | 5670,65 | |
|
Таблица 9
|
| I | J | |
1 | МИН | МАКС | |
2 | =F10 | =F10 | |
3 | =ДМИН(A10:G27;F10;G2:G3) | =ДМАКС(A10:G27;F10;G2:G3) | |
|
Наименование оборудования извлекается функцией БИЗВЛЕЧЬ с указанием критериев._БИЗВЛЕЧЬ(Лист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
|
| A | B | C | D | E | F | G | |
1 | | CyrixMII300GP | AMDK6II400MHz | AMDK6II333MHz | |
| | =БИЗВЛЕЧЬ(Лист1!A10:G18;Лист1!A10;Лист1!I2:I3) | =БИЗВЛЕЧЬ(Лист1!A10:G18;Лист1!A10;Лист1!J2:J3) | =БИЗВЛЕЧЬ(Лист1!A10:G18;Лист1!A10;Лист1!H2:H3) | |
2 | Месяц | Объём продаж по МИН (шт) | Стоимость по МИН (руб) | Объём продаж по МАКС (шт) | Стоимость по МАКС (руб) | Объём продаж по условию К (шт) | Стоимость по условию К (руб) | |
3 | Ноябрь 2009 | 225 | 984 487,50 | 150 | 850 597,20 | 180 | 904 153,32 | |
4 | Декабрь 2009 | 195 | 853 222,50 | 69 | 391 274,71 | 75 | 376 730,55 | |
5 | Январь 2010 | 93 | 406 921,50 | 96 | 544 382,21 | 235 | 1 180 422,39 | |
6 | Февраль 2010 | 255 | 1 115 752,50 | 149 | 844 926,55 | 226 | 1 135 214,72 | |
7 | Март 2010 | 69 | 301 909,50 | 103 | 584 076,74 | 102 | 512 353,55 | |
8 | Апрель 2010 | 292 | 1 277 646,00 | 68 | 385 604,06 | 182 | 914 199,47 | |
| | | =БИЗВЛЕЧЬ (Лист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 | |
|
Используя данные таблицы 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
|
| A | B | C | D | E | F | G | |
1 | | CyrixMII300GP | AMDK6II400MHz | AMDK6II333MHz | |
| | =БИЗВЛЕЧЬ(Лист1!A10:G18;Лист1!A10;Лист1!I2:I3) | =БИЗВЛЕЧЬ(Лист1!A10:G18;Лист1!A10;Лист1!J2:J3) | =БИЗВЛЕЧЬ(Лист1!A10:G18;Лист1!A10;Лист1!H2:H3) | |
2 | Месяц | Объём продаж по МИН (шт) | Стоимость по МИН (руб) | Объём продаж по МАКС (шт) | Стоимость по МАКС (руб) | Объём продаж по условию К (шт) | Стоимость по условию К (руб) | |
3 | Ноябрь 2009 | 225 | 984 487,50 | 150 | 850 597,20 | 180 | 904 153,32 | |
4 | Декабрь 2009 | 195 | 853 222,50 | 69 | 391 274,71 | 75 | 376 730,55 | |
5 | Январь 2010 | 93 | 406 921,50 | 96 | 544 382,21 | 235 | 1 180 422,39 | |
6 | Февраль 2010 | 255 | 1 115 752,50 | 149 | 844 926,55 | 226 | 1 135 214,72 | |
7 | Март 2010 | 69 | 301 909,50 | 103 | 584 076,74 | 102 | 512 353,55 | |
8 | Апрель 2010 | 292 | 1 277 646,00 | 68 | 385 604,06 | 182 | 914 199,47 | |
9 | Май 2010 | 154 | 674 582,55 | 81 | 458 201,65 | 175 | 878 368,21 | |
10 | Июнь 2010 | 173 | 756 075,44 | 88 | 496 990,03 | 177 | 890 136,55 | |
11 | Июль 2010 | 209 | 914 530,27 | 69 | 393 851,37 | 180 | 901 904,90 | |
12 | Август 2010 | 198 | 864 862,81 | 50 | 282 498,93 | 182 | 913 673,24 | |
13 | Сентябрь 2010 | 261 | 1 140 591,95 | 51 | 288 051,24 | 184 | 925 441,59 | |
14 | Октябрь 2010 | 217 | 951 035,47 | 48 | 272 458,79 | 187 | 937 209,93 | |
15 | | Рост | Тенденция | Арифметическая прогрессия | |
| | =РОСТ(B3:B8;A3:A8;A9;1) | =БИЗВЛЕЧЬ(Лист1!A$10:G$18;Лист1!F$10;Лист1!I$2:I$3)*B9 | =ТЕНДЕНЦИЯ(D3:D8;A3:A8;A9;1) | =БИЗВЛЕЧЬ (Лист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 | |
|
По результатам полученной базы данных с помощью «Мастера диаграмм» построена диаграмма «Суммарная стоимость продаж компьютеров Pentium по минимальной стоимости и по условию K», приведенная на рисунке 2. На диаграмме, кроме того, отображаются соответствующие линии тренда, аппроксимирующие зависимость стоимости для выбранного типа компьютеров CyrixMII333GP и AMDK6II400MHz. При построении диаграммы использовался тип «Гистограмма». В качестве рядов диаграммы выбраны диапазоны ячеек С3:С14 (CyrixMII333GP) и F3:F14 (AMDK6II400MHz), в качестве категорий выбраны ячейки A3:A14 (месяцы). Надпись диаграммы «Компьютеры Pentium» выполнена путем ссылки на ячейку A10 Листа 1.
Для компьютера с наименьшей стоимостью AMDK6II400MHz выбрана полиноминальная линия тренда, для процессора, выбранного по условию K, CyrixMII333GP, - 2-х линейный фильтр, для данного тренда выведены уравнение y = 2E-07x6 - 0,038x5 + 3910,x4 - 2E+08x3 + 6E+12x2 - 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), используем функцию базы данных БИЗВЛЕЧЬ() и критерий «Стоимость по условию К (руб) 1180422,39». Для этого в ячейку, L2 скопируем содержимое ячейки G2, а в ячейке L3 введем критерий =МАКС (G3:G14). В ячейке L4, используя формулу =БИЗВЛЕЧЬ(A2:G14;A2;L2:L3) получим необходимый месяц.
Таблица 12
|
| L | M | |
2 | Объём продаж по условию К (шт) | Стоимость по условию К (руб) | |
3 | >160 | 1 180 422,39 | |
4 | < 260 | Январь 2010 | |
5 | 10241617,99 | |
|
Таблица 12
|
| L | M | |
2 | =F2 | =G2 | |
3 | < 260 | 1 180 422739 | |
4 | =БДСУММ(A2:G14;G2;L2:L3) | =БИЗВЛЕЧЬ(A2:G14;A2;M2:M3) | |
|
Заключение
В ходе выполнения данной курсовой работы были изучены компоненты MS Word и Excel. Получены знания о формировании табличной базы данных и о возможностях при работе с ней на примере базы данных в Microsoft Excel. Произведены расчеты с помощью соответствующих формул в табличном процессоре Microsoft Excel.
Список используемой литературы
Информатика. Базовый курс / Симонович и др. - СПб: «Питер», 2000.
Берлинер Э.М., Глазырин Б.Э., Глазырина И.Б. Офис от Microsoft.- М.: ABF, 1997.
Дж. Кокс и др. Microsoft Excel 97. Краткий курс. Пособие ускоренного обучения - СПБ.: Питер, 1998.
Электронно-методическое пособие «Word 97».
Электронно-методическое пособие «Excel 97».
Конспект лекций по дисциплине «Информатика».
Алексеев А.П., Камышенков Г.Е. Использование ЭВМ для математических расчетов. Самара: ПГАТИ, 1998.