Смекни!
smekni.com
Информатика

Использование EXCEL в практических расчетах (стр. 2 из 2)

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

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

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

Далее на втором листе по данным табл. 1. определим минималь­ную и максимальную цену оборудования в рублях, среднее значение и ко­личество оборудования по сравниваемым объектам. Результаты получен­ных значений представлены на рис.8. Минимальное значение находится с помощью встроенной функции «МИН(число1;число2; ….), где число1, число2 и т.д. набор значений среди которых надо найти наименьшее значение. Функцией МАКС(число1;число2; …) – находим максимальное значение. Среднее значение найдем с помощью функции СРЗНАЧ(число1;число2; …). В поле «Наименование объекта» запишем типы объектов «Epson» и HewPakard». Количество объектов рассчитаем по формуле =СЧЁТЕСЛИ(Лист1!B11:B24;A5), где Лист1!B11:B24 – диапазон подсчитываемых элементов, А5 – ссылается на значение в ячейке A5 «Epson». Эта функция подсчитывает количество значений в диапазоне, заданном в первом аргументе функции, совпадающих со значением , заданным вторым аргументом функции.

Рис.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 — для второго поля и так далее.

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

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

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

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

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

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

5.4. Задание 4

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

минимальная цена - =ОКРУГЛ((СЛЧИС()*(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

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

Выводы:

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

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

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

Рис.12. Диаграмма изменения стоимости продаж с аппроксимацией.

5.7. Задание 7

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

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

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

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

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

Рис.14. Рассчет месяца с самым высоким уровнем продаж, для оборудования с условием К.

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

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

2. ЗАДАНИЯ И МЕТОДИЧЕСКИЕ УКАЗАНИЯ к курсовой работе по дисциплине «ИНФОРМАТИКА»; КАДАКОВ Д.А, СИРАНТ О.В., СТЕФАНОВА И.А., ; Самара 2004 г.

3. Справка MicrosoftExcel 2003.

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

 
назад
1
2
НАПИШИТЕ НАМ
Copyright © Smekni.com. All rigths reserved.