Технологии обработки экономической информации в среде ТП MS Excel

     Содержание 

      Задание 1. Технологии обработки экономической информации в среде ТП MS Excel 

     Стоимость аренды  для каждого клиента рассчитывается с учетом следующих тарифов:

• до 30 дней аренды - 1200 руб./сутки;
• свыше 30 дней - 1000 руб./сутки.

     Определить  количество клиентов, арендующих сейфы, более чем на месяц и вывести  их список (использовать автофильтр).

     На  какой срок банку выгоднее сдавать сейфы в аренду?

     Построить линейчатую диаграмму, отображающую стоимость  аренды по клиентам. 

     Таблица с результатами расчетов представлена на стр. 4.

     При расчетах использованы следующие встроенные функции ТП MS Excel:

СУММ(зн.1, зн.2, …, зн.N);

СЧЕТЕСЛИ(диапазон; критерий);

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования);

ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь);

      Для того, чтобы вывести сообщение, показывающее, на какой срок банку выгоднее сдавать сейфы, можно использовать следующую формулу: 

=ЕСЛИ(D16>C18;"Банку  выгоднее сдавать сейфы на  срок больше месяца";ЕСЛИ(C18>D16;"Банку выгоднее сдавать сейфы на срок не больше месяца"; "Одинаковый доход приносят договора аренды со сроком больше и не больше месяца"))

      Далее показана диаграмма, отражающая величину аренды для клиентов банка.

      Чтобы вывести список клиентов, которые  арендуют сейфы на срок больше месяца, может быть использована команда  Фильтрà Автофильтр из меню Данные. Далее представлен результат применения этой команды, а также показано диалоговое окно, с помощью которого можно выбрать соответствующих клиентов, используя отбор по столбцу Срок аренды, дней.

      Далее показаны формулы, с помощью которых  были проведены расчеты.

       Задание 2. Технологии работы в среде СКМ Maple 

   Вариант 5 

     1. Объем производства чулочно-носочных изделий, млн. пар, предприятиями Республики Беларусь в зависимости от года выпуска можно описать полиномом пятой степени

      , где х – период. 

            Построить кривую изменения объема производства чулочно-носочных изделий, млн. пар, предприятиями Республики Беларусь за период с 1995 по 2005 г.г.

            Определить  предполагаемое количество  пар чулочно-носочных изделий, выпущенных в 1998 и 2005 г.г. 

     Определяем  функцию f:

> y:=-0.0287*x^5+0.9751*x^4-12.007*x^3+63.377*x^2-126.48*x+130;

     Строим  график на интервале 1.. 11 (1995 – 1, … , 2005 – 11):

> plot(y,x=1..11);

     Определяем  функцию с помощью функционального  оператора:

> y:=(x)->-0.0287*x^5+0.9751*x^4-12.007*x^3+63.377*x^2-126.48*x+130;

     Вычисляем значение функции в точке равной 4, что соответствует 1998 году (1995г.–1, 1996г.–2, … , 1998г.–4, … , 2005г.–11):

> ObyomPr_1998:=evalf(y(4),5);

     Вычисляем значение функции в точке равной 11, что соответствует 2005 году: (1995г.–1, 1996г.–2, … , 1998г.–4, … , 2005г.–11):

> ObyomPr_2005:=evalf(y(11),5);

        2. Решить систему уравнений межотраслевого баланса (МОБ)

     По  известным данным о коэффициентах  прямых затрат (а_ij) и конечном продукте (Y) в межотраслевом балансе для трех отраслей (промышленность, строительство, сфера услуг) определить общий выпуск продукции по каждой отрасли (x_ij). 

      Определяем  матрицу коэффициентов прямых затрат:

> A:=matrix([[0.1,0.4,0.5],[0.1,0.5,0.4],[0.2,0.2,0.1]]);

     Определяем  единичную матрицу:

> E:=Matrix(3,3,shape=identity);

     Находим матрицу Е-А:

> K:=evalm(E-A);

     Определяем  вектор-столбец свободных членов:

> B:=vector([40,30,20]);

     Подключаем библиотеку linalg:

> with(linalg): 

     Вычисляем общий выпуск продукции по отраслям:

> Pr:=linsolve(K,B);

     Задаем  количество значащих цифр:

> Pr:=evalf(%,4);

      Итак, в промышленности валовой выпуск будет равен 179.5 млрд. руб., в строительстве он составит 177.1 млрд. руб., а в сфере услуг – 101.5 млрд. руб.  

        3. Построить поверхность

      f= при x = -10..10, t=1..4 

     Определяем  поверхность

> f:=sin(x+t*Pi)/(x+11);

     Задаем  команду для построения поверхности f:

> plot3d(f,x=-10..10,t=1..4); 

     _{Результат:}

        4. Вычислить значение производной первого порядка функции f(x):

     Определяем  функцию f:

> f:=23*x^30+5*x^11+x*y+45*x;

     Вычисляем производную:

> Diff(f,x)=diff(f,x);

     5. Ордината Y развертки средней точки одной из деталей кроя швейного изделия определяется по формуле:

                                при r=1.1

Определить  значение ординаты Y. 

     Определяем  интегрируемую функцию f:

> r:=1.1;

> f:=(r^2/x^2)*sin(1/x);

     Вычисляем значение определенного интеграла  с точностью до 3 значащих цифр:

> Int(f,x=1..2.5)=evalf(int(f,x=1..2.5),3);

      Задание 3. Технологии обработки данных в среде СУБД MS Access и использования языка запросов SQL как средства расширения возможностей СУБД 

     Вариант 5

     Для анализа поставок изделий предприятиями  создать БД, содержащую следующие  данные:

1) «Код изделия»;

2) «Наименование изделия»;

3) «Наименование предприятия»;

4) «План поставок, млн.р.»;

5) «Фактически поставлено, млн.р.»;

6) «Отклонение от плана, %»*.

      В таблицу Справочник включить данные 1 и 2, а в таблицу Сведения – 1 и 3-6. Предусмотреть не менее трех предприятий, каждое из которых реализует не менее четырех наименований изделий. 

     1. Разработаем таблицы, на основании  которых будем создавать базу данных:

     Таблица Справочник

     Таблица Сведения

      2. С помощью  конструктора СУБД MS Access создадим две таблицы: таблицу с именем Справочник и таблицу с именем Сведения как указано на рисунках ниже. Определим типы данных каждого поля. 

      В таблице Справочник:

     поле [Код изделия] определим целым типом,

     поле [Наименование изделия] - символьным типом с размером 100 символов.

     поле [Код изделия] определим ключевым. 

     Рис. 1 - Таблица Справочник в режиме конструктора СУБД ACCESS 

      В таблице Сведения:

     поле  [Код изделия] определим целым типом,

     поле [Наименование предприятия]  - символьным типом с размером 100  символов,

     поля [План поставок, млн руб], [Фактически поставлено, млн руб], [Отклонение от плана, %] - вещественным типом.  

     Рис. 2 - Таблица Сведения в режиме конструктора СУБД ACCESS 

• Команда CREATE TABLE, определяющая структуру таблицы Справочник, на языке SQL ANSI имеет вид:

CREATE TABLE Справочник

([Код  изделия] INT CONSTRAINT Ключ PRIMARY KEY,

[Наименование  изделия] CHAR(100)); 

• Команда CREATE TABLE, определяющая структуру таблицы Сведения, на языке SQL ANSI имеет вид:

CREATE TABLE Сведения

([Код  изделия] INT,

[Наименование  предприятия] CHAR(50), 

[План  поставок, млн р] REAL,

[Фактически  поставлено, млн р] REAL,

[Отклонение от плана, %] REAL); 

      3. В режиме таблицы  СУБД ACCESS заполним таблицы конкретными значениями данных, исходя из их смысла. Поле, помеченное знаком* ([Отклонение от плана, %]), оставим незаполненным. В результате таблицы приобретут вид,  как показано на стр. 10 

• Команда заполнения базы данными INSERT INTO (для двух записей таблицы Справочник), записанная на языке SQL ANSI, имеет вид:

INSERT INTO Справочник  VALUES (1, “Шпроты в масле”);

INSERT INTO Справочник VALUES   (2, “Скумбрия в масле”); 

• Команда заполнения базы данными INSERT INTO (для двух записей таблицы Сведения), записанная на языке SQL ANSI, имеет вид:

INSERT INTO Сведения

([Код  изделия], [Наименование предприятия], [План поставок, млн руб], [Фактически поставлено, млн руб])

VALUES (3, ”ООО Золотая рыбка”, 10, 10);

INSERT INTO Сведения

([Код  изделия], [Наименование предприятия], [План поставок, млн руб], [Фактически поставлено, млн руб])

VALUES (4, ”ЗАО Инфудс ”, 190, 200); 

      4. Для того, чтобы с таблицами можно было работать как с единым целым, свяжем их, пользуясь инструментом Схема данных. Исходя из смысла базы данных, связь должна быть установлена по полю  [Код изделия] таблицы Справочник и полю  [Код изделия] таблицы Сведения (рис. 3). Это связь вида один ко многим, так  как одной записи таблицы Справочник может соответствовать несколько записей таблицы Сведения. 

Рис.3 –  Схема данных 
 

5. Составим  запросы к базе данных и реализуем их в СУБД Access:

      Запрос 1. Рассчитать значение поля [Отклонение от плана, %].  

      Значение  этого поля рассчитывается по формуле: 

[Отклонение  от плана, %] = [Фактически поставлено, млн руб]/

[План  поставок, млн руб]*100-100;

      Это запрос на обновление. Для его реализации необходимо активизировать вкладку Запросы ==> Создать ==> Конструктор==>  Меню Запрос ==> Обновление  ==> SQL. В окне SQL (рис.4) ввести текст запроса: 

Рис.4 –  Окно запроса на обновление 

      Затем выполнить его, нажав соответствующую кнопку на пиктографическом меню. В результате поле [Отклонение от плана, %] таблицы Сведения будет рассчитано в соответствии с введенной формулой (рис. 5). 

Рис.5 –  Таблица Сведения после выполнения запроса на обновление 

      Запрос 2.

      Показать  поставки с перевыполнением плана  более чем на 5%. Упорядочить по росту процента выполнения плана.  

      Текст запроса на SQL:

SELECT [Наименование предприятия], [Наименование изделия],                              

[План  поставок, млн руб], [Фактически поставлено, млн руб],

[Отклонение  от плана, %]

FROM Справочник, Сведения

WHERE Справочник.[Код изделия]=Сведения.[Код изделия]

AND ([Отклонение от плана, %]>5)

ORDER BY [Отклонение от плана, %];

     В результате выполнения запроса получим  таблицу:

      Создаем запрос в конструкторе.

      Запрос 3.

      Показать  поставки с фактической стоимостью товара от 40 до 90 млн. руб., упорядочив по росту стоимости.  

      Текст запроса на SQL:

SELECT [Наименование изделия], [Наименование предприятия],

[Фактически поставлено, млн руб]

FROM Справочник, Сведения

WHERE Справочник.[Код изделия]=Сведения.[Код изделия]

AND ([Фактически поставлено, млн руб] Between 40 And 90)

ORDER BY [Фактически поставлено, млн руб]; 

      В результате выполнения запроса получим  таблицу:

      Создаем запрос в конструкторе:

      Запрос 4.

      Показать  плановую и фактическую стоимость  по поставкам шпрот обществами с ограниченной ответственностью.  

      Текст запроса на SQL:

SELECT [Наименование изделия], [Наименование предприятия],

[План поставок, млн руб], [Фактически поставлено, млн руб]

FROM Справочник, Сведения

WHERE Справочник.[Код изделия] = Сведения.[Код изделия]

AND ([Наименование изделия] Like ("Шпроты*"))

AND ([Наименование предприятия] Like ("ООО*")); 

      В результате выполнения запроса получим  таблицу: