Ячейкам 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-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

Таблица 12

Заключение

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

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

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

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

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

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

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

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

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

Страницы: 1, 2, 3