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

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

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

5.2. Задание 2 

 

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

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

 

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) оборудование по выбранное по критерию К по сравнению минимальной стоимостью  продаётся в большем объёме:

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

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

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

5.7. Задание 7 

      Рассчитаем  «суммарную стоимость оборудования, выбранного по максимальной стоимости, за месяцы, в которые объём продаж был менее 0,5*172=86.». Для этого используем функцию базы данных БДСУММ() и критерий «Об.прод.HP Laser Jet 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. Дж. Кокс и  др. Microsoft Excel 97. Краткий курс. Пособие ускоренного обучения - СПБ.: Питер, 1998.

 

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

3.    Справка  Microsoft Excel 2003. 

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