Учебная работа. Курсовая работа: Использование EXCEL в практических расчетах

(Пока оценок нет)

Загрузка...

Учебная работа. Курсовая работа: Использование EXCEL в практических расчетах

Оглавление

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

2. Постановка задачки курсовой работы………………………………………… 4

3. Выбор варианта………………………………………………………………. 4

4. Задание на выполнение………………………………………………………. 5

5. Выполнение задания…………………………………………………………. 7

5.1. Задание 1……………………………………………………………… 7

5.2. Задание 2……………………………………………………………… 11

5.3. Задание 3……………………………………………………………… 12

5.4. Задание 4……………………………………………………………… 13

5.5. Задание 5……………………………………………………………… 14

5.6. Задание 6……………………………………………………………… 15

5.7. Задание 7……………………………………………………………… 16

6. Использованная литература………………………………………………….. 18

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

Исследование операционной системы Windows, компонент MSWord и Excelи получение практических способностей работы с современными информационными технологиями. Получение представления о формировании табличной базы данных и о способностях при работе с ней на примере базы данных в MSExcel.

2. Постановка задачки курсовой работы

• В MSExcel сделать базу данных табличного типа для 2-ух объектов
(компаний, типов оборудования, конструкций и т.д.).

• Отсортировать базу данных согласно задания.

• Высчитать требуемые характеристики.

• Спрогнозировать нрав конфигурации объёма реализации оборудования на
следующие 6 месяцев.

• Сделать визитную карточку хоть каким графическим средством для
Windows.

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

Для выбора варианта задания нужно высчитать код MNβ, а потом по подходящим таблицам задания произвести выбор начальных данных. Номер зачетной книги – 093072.

М = 2, N = 7, β = 9.

M=2

1 . Принтеры — струйные и матричные
Тип
Стоимость(у.е.)

Epson StylusColor440 — струйный, цветной, 4стр/мин, А-4,720 dpi
Epson
137

Epson StylusColor640 — струйный,пветной,4стр/мин,А-4, 1440×720 dpi
Epson
195

Epson StylusColor740 — струйный,цветной, 4стр/мин,А-4,1440х720 dpi
Epson
258

Epson LQ-100 — матричный, 24 иглы, формат А4
Epson
137

Epson FX-1170 — матричный,широкий,9игл,380 символов/мин.,24 кбайт
Epson
315

Epson FX-LQ-100+ — матричный, узенький, 24 иглы
Epson
134

2. Принтеры — лазерные и струйные

HP Laser Jet 1 100 — лазерный, 600dpi, А4, 2Mb RAM, 8стр/мин
Hew Paskard
398

HP Laser Jet 1 100A — лазерный,600dpi, А4,2Мb RАМ,8стр/мин,сканер
Hew Paskard
546

HP Laser Jet 2100 — лазерный,! 200dpi, А4,копир,сканер
Hew Paskard
749

HP Desk Jet 420C — струйный, формат A4,600dpi
Hew Paskard
100

HP Desk Jet 695C — струйный, цветной, фотопечать, формат А4
Hew Paskard
132

HP Desk Jet 6 IOC — струйный, цветной, формат А4
Hew Paskard
120

HP Desk Jet 880C — струйный, цветной, 8 стр/мин, формат А4
Hew Paskard
300

HP Desk Jet 895Cxi — струйный, цветной RET2, 8 стр/мин, А4
Hew Paskard
356

4. Задание на выполнение

4.1. Сформировать на Листе 1 базу данных (табл.1) для 2-х видов оборудования в виде единой таблицы, начинающейся с 10 строчки:

• Столбцы А, В и С заполнить согласно данного варианта М.

• Данные ячеек столбца D получить методом роста содержимого со
ответствующих ячеек столбца С (Стоимость у.е.) на величину случайного числа,
которое задается в спектре от 0,1*N до 0,5*N, ( при N=0 стоимость увеличи
вается на величину в спектре от 0 до 1)*.

• Приобретенные данные столбца D скопировать в примыкающий столбец Е, используя команду
и флаг

• Столбец D скрыть.

• информацию о курсе 1 у.е. в рублях указать в ячейках А1-А2. (Курс определяется на момент выполнения задания). Присвоить ячейке А2 имя «Курс».

• В столбце F (Стоимость в руб.) высчитать стоимость оборудования в рублях.

• Отсортировать базу данных по возрастанию цены оборудования
для чётного р и по убыванию — для нечётного р.

4.2. Используя функции Excel, по данным табл. 1. найти минимальную и наивысшую стоимость оборудования в рублях, среднее

Таблица 2

Наименование объекта

Стоимость оборудования (руб.)

МИН
МАКС
СРЗНАЧ
количество

Объект 1

Объект 2

4.3. Используя функции работы с базой данных, найти и вывести стоимость и заглавие оборудования 1-го вида по условию К (выбирается из табл. 3).

Таблица 3

β
Параметр К ( по цены в руб.)

9
Оборудование, имеющее меньшее отклонение от среднего значения цены объектов 2-го вида

Следует исключить совпадение цены по условию К с минимальным и наибольшим значениями объектов 1-го вида, для этого следует при выбирании объектов сделать аспект для функции выбора.

4.4. Провести анализ нрава конфигурации объёма и цены помесячной реализации оборудования в границах 6 месяцев, предыдущих моменту наполнения базы данных.

Для этого на Листе 3 составить новейшую базу данных (табл. 4) по трем наименованиям оборудования: с малой и наибольшей стоимостью объекта 1, также по условию К.

Таблица 4

Месяц

Наименование оборудования по
Наименование оборудования по МАКС
Наименование оборудования по условию. К

Объём продаж (шт.)

Стоимость

(руб.)

Объём продаж (шт.)

Стоимость

(руб.)

Объём продаж (шт.)

Стоимость

(руб.)

мес. год

Столбец «объём продаж» табл. 4 заполняется по месяцам полугодия методом копирования из вспомогательного столбца лишь значений вариантных чисел. Случайные числа берутся в спектре NM — 1NM для оборудования с наибольшей стоимостью, в спектре NM — 2NM — для оборудования со стоимостью по условию К и в спектре NM — 3NM — для оборудования с малой стоимостью. Цены оборудования, необходимые для вычисления данных столбца «стоимость», берутся из предыдущих вычислений.

4.6. Используя данные табл. 4, выстроить диаграмму для 2-ух видов оборудования, избранных из табл. 5.

Таблица 5

Вариант М
Виды оборудования

0, 3, 6, 9
По наибольшей и малой цены

1,4,7
По наибольшей цены и по условию К

2,5,8
По малой цены и по условию К

Вид диаграммы выбирается из суждений наглядности представляемой инфы. На диаграмме отразить ее заглавие, заглавие осей, легенду, надпись (наименование оборудования). Диаграмму расположить под табл. 4.

4.7. Для всякого из видов оборудования спрогнозировать в табл. 4 размер помесячной его реализации за 6 следующих месяцев.

Для прогноза употреблять функцию ТЕНДЕНЦИЯ(), POCT(), ПРОГРЕССИЯ для различных видов оборудования.

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

4.8. Используя данные табл. 4 и надлежащие функции базы данных, решить задачку, приведённую в табл. 6.

Таблица 6.

β
Разыскиваемый параметр

9
Подсчитать суммарную стоимость оборудования, избранного по максимальной цены, за месяцы, в которые объём продаж был наименее 0,5* 1NM.

4.9. Используя данные табл. 4, вывести при помощи соответственной функции БД месяц с большей суммой реализации для оборудования по условию К.

4.10. Оформить работу в текстовом микропроцессоре Word.

4.11. Используя приложения MSOffice, сделать визитную карточку с обязательной вставкой графического объекта. Отдельные элементы визитной карточки должны быть сгруппированы в единый объект.

4.12. Используя способности MSWord, вставить оглавление.

5. Выполнение задания

5.1
. Задание 1

Сформируем базу на листе 1:

Рис.1. Сделанная база.

Для расчета розничной цены а условных единицах в стобце D строчки 11 пишем формулу =C11+СЛЧИС()*(3,5-0,7)+0,7, при вычислении результата в ячейке D11 цепляемся курсором мыши за правый нижний угол ячейки D11 и протаскиваем вниз до строчки 24. Таковым образом в столбце D автоматом формируются формулы для посчета розничной цены по любому товару. На рис.2 представлена таблица с раскрытыми формулами.

Рис.2 база с раскрытыми формулами.

Дальше для отображения цены с точность до 2 знака опосля запятой, выделяем ячеки D11 – D24, нажимем правой клавишей мыши, в контекстном меню выберем формат ячеек. В показавшейся форме избираем во вкладке «Число» числовой формат, а поле «Число десятичных символов» ставим 2 (см.Рис.4)

Для сотворения столбца Fвыделяем ячейки D11 – D24, в контекстном меню копируем их. Выделяем ячейку F11, в контекстном меню избираем «Особая вставка», покажется форма представленная на рис.3. Там избираем «Значения» и жмем «ОК». Столбец F заполняется.

Рис. 3. Форма Особая вставка.

Рис.4 Выбор точности отображения чисел.

Аналогично, описанному чуть повыше, изменяем формат отображения чисел до второго знака опосля запятой. Для того, чтоб скрыть столбец D, ставим курсор на столбец D и в контекстном меню избираем «Скрыть». Добавляем границы ячеек и лицезреем итог (см. Рис.6.). В ячейке A1 пишем слово «курс», в A2 – стоимость одной условной единицы в рублях, при этом переименовываем ячейку A2 в «курс» (Рис.5.).

Рис.5. Присвоение ячейки имени «курс».

Рис.6. База со сокрытым столбцом D и сделанным столбцом E.

В столбце F – розничная стоимость принтеров в рублях. В ячейке F11 пишем формулу =E11*курс и протягиваем до строчки 24. Потом изменив точность отображения до второго знака, и отсортируем по убыванию цены (Рис.7.). В заголовках столбцов запишем их наименования.

Дальше на втором листе по данным табл. 1. определим минимальную и наивысшую стоимость оборудования в рублях, среднее количество объектов рассчитаем по формуле =СЧЁТЕСЛИ(Лист1!B11:B24;A5), где Лист1!B11:B24 – спектр подсчитываемых частей, А5 – ссылается на количество значений в спектре, данном в первом аргументе функции, совпадающих со значением , данным вторым аргументом функции.

Рис.7. Готовая таблица.

5.2. Задание 2

Рис.8. Расчет наибольшей, малой и средней цены с расчетом количества объектов.

Таблица 2. с формулами.

Наименование объекта
Стоимость оборудования (руб)

МИН
МАКС
СРЗНАЧ
количество

Epson

=МИН(Лист1!F15;

Лист1!F17;

Лист1!F18;

Лист1!F19;

Лист1!F20;

Лист1!F21)

=МАКС(Лист1!F15;

Лист1!F17;

Лист1!F18;

Лист1!F19;

Лист1!F20;

Лист1!F21)

=СРЗНАЧ(

Лист1!F15;

Лист1!F17;

Лист1!F18;

Лист1!F19;

Лист1!F20;

Лист1!F21)

=СЧЁТЕСЛИ(

Лист1!B11:B24;

A5)

Hew Paskard

=МИН(Лист1!F11;

Лист1!F12;

Лист1!F13;

Лист1!F14;

Лист1!F16;

Лист1!F22;

Лист1!F23;

Лист1!F24)

=МАКС(Лист1!F11;

Лист1!F12;

Лист1!F13;

Лист1!F14;

Лист1!F16;

Лист1!F22;

Лист1!F23;

Лист1!F24)

=СРЗНАЧ(

Лист1!F11;

Лист1!F12;

Лист1!F13;

Лист1!F14;

Лист1!F16;

Лист1!F22;

Лист1!F23;

Лист1!F24)

=СЧЁТЕСЛИ(

Лист1!B11:B24;

A6)

5.3. Задание 3

Параметр К – оборудование имеющее меньшее отклонение от среднего значения цены объектов второго вида.

Для выполнения 3-го задания используем формулы:

БИЗВЛЕЧЬ(база_данных; поле; аспект), где:

База_данных — это интервал ячеек, формирующих перечень либо базу данных. база данных представляет собой перечень связанных данных, в каком строчки данных являются записями, а столбцы — полями. Верхняя строчка перечня содержит наименования всех столбцов.

Поле описывает столбец, применяемый функцией. аргумент «поле» быть может задан как текст с заглавием столбца в двойных кавычках, к примеру «Возраст» либо «Сбор» в приведенном ниже примере базы данных, либо как число, задающее положение столбца в перечне: 1 — для первого поля, 2 — для второго поля и так дальше.

Аспект — это интервал ячеек, который содержит задаваемые условия. Хоть какой интервал, который содержит по последней мере одно заглавие столбца и по последней мере одну ячейку под заглавием столбца с условием, быть может применен как аргумент аспект БДФункции.

ДМИН(база_данных;поле;аспект)

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

Рис. 9. Выполненное 3 задание с использованными формулами.

5.4. Задание 4

Для выполнения задания 4 на 3-ем листе книжки Excel сделаем таблицу 7.4 из задания. Заполним её наименованиями оборудования с малой, наибольшей стоимостью и оборудованием определенным в прошлом задании. Заполним 1-ый столбец – по месяцам. В столбцы, показывающие размер продаж с января по июнь, запишем формулы:

малая стоимость — =ОКРУГЛ((СЛЧИС()*(172-72)+72);0);

наибольшая стоимость — =ОКРУГЛ((СЛЧИС()*(272-72)+72);0);

по аспекту К — =ОКРУГЛ((СЛЧИС()*(372-72)+72);0).

В столбцах цены пишем формулы:

малая стоимость — =B3*Лист1!$F$24;

наибольшая стоимость — =D3*Лист1!$F$11;

по аспекту К — =F3*Лист1!$F$15.

В столбцах размеров продаж с июля по декабрь:

малая стоимость — =ОКРУГЛ(РОСТ(B$3:B8;A$3:A8;A9;1);0);

наибольшая стоимость — =ОКРУГЛ(ТЕНДЕНЦИЯ(D$3:D8;A$3:A8;A9;1);0);

по аспекту К – используем автозаполнение (протащим формулу в ячейке F8 вниз).

Итог изображен на рис.10.

Рис. 10. Таблица задания 4.

5.5.Задание 5

По данным задания 4 строим диаграмму продаж по оборудованию с малой стоимостью и по аспекту К.

Рис.11. Диаграмма продаж за 6 месяцев.

5.6. Задание 6

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

Выводы:

• как видно из диаграммы (Рис. 12) оборудование по выбранное по аспекту К по сопоставлению малой стоимостью продаётся в большем объёме:

• законконфигурации цены оборудования HPDeskJet 420C – полиномиальный, а EpsonFX-1170 – скользящее среднее (2 линейный фильтр).

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

Рис.12. Диаграмма конфигурации цены продаж с аппроксимацией.

5.7. Задание 7

Рассчитаем «суммарную стоимость оборудования, избранного по максимальной цены, за месяцы, в которые объём продаж был наименее 0,5*172=86.». Для этого используем функцию базы данных БДСУММ() и аспект «О.прод.HPLaserJet 2100 < 86».

В вольную ячейку, B18 скопируем содержимое ячейки D2 «О.прод.МАКС», а в ячейку B
19
занесём условие «<86». В другую свободную ячейку, к примеру B
20
, введём функцию =БДСУММ(A2:G14;D2;B18:B19).

Рис.13. Рассчет по заданию 7

Для вывода месяца реализации самого драгоценного оборудования по условию К
используем функцию базы данных БИЗВЛЕЧЬ() и аспект «Стоим. по условию К (руб.)».

В вольную ячейку, к примеру, A24скопируем содержимое ячейки G2 «Стоим. по условию К (руб.)», а в ячейку A25занесём условие «=МАКС (G3:G14)». В другую вольную ячейку, к примеру A26, введём функцию = БИЗВЛЕЧЬ (A2:G14;А2;A24:A25).