МИНИСТЕРСТВО ОБРАЗОВАНИя И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
«РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ им. А.Н. Косыгина (ТЕХНОЛОГИИ. ДИЗАЙН. ИСКУССТВО)»
(ФГБОУ ВО «РГУ им. А.Н. Косыгина»)
Для направления подготовки 29.03.02 Технологии и проектирование текстильных изделий |
Теория алгоритмов и методы обработки данных при проектировании текстильных материалов
Методические указания к лабораторным работам
Составитель: Сухарев В.В.
Москва
РГУ им. А.Н. Косыгина 2018
УДК 003.023
С 91
С91 Теория алгоритмов и методы обработки данных при проектировании текстильных материалов: методические указания к лабораторным работам. /Сост. Сухарев В.В. – М.:РГУ им. А.Н. Косыгина, 2018. – 32 с.
Рецензенты:
Монахов В.И. – канд. техн. наук, проф., ФГБОУ ВО «РГУ им. А.Н. Косыгина»;
Стрельников Б.А. – канд. техн. наук, доц., ФГБОУ ВО «РГУ им. А.Н. Косыгина»
Методические указания предназначены для обучающихся по направлению подготовки 29.03.02 Технологии и проектирование текстильных изделий при изучении дисциплины «Информационные технологии при проектировании текстильных изделий».
В методических указаниях рассматриваются практические аспекты обработки электронных таблиц Microsoft Excel, используемые в учебном процессе.
Методические указания включают рассмотрение наиболее часто встречающихся задач и проработку отдельных вопросов работы с электронными таблицами.
УДК 003.023
Подготовлено к печати на кафедре Автоматизированные системы обработки информации и управления
Печатается в авторской редакции
© РГУ им. А.Н. Косыгина, 2018 | |
© Сухарев В.В., 2018 |
Расчет значения суммы без НДС при указанных сумме с включенной НДС и ставки НДС
Постановка задачи:
Требуется определить значение суммы НДС, если указана ставка НДС и сумма с НДС. Формула должна быть общая для всех строк колонки.
Пример таблицы приведен на рис. 1
A | B | C | |
1 | Сумма без НДС | Ставка НДС,% | Сумма с НДС |
2 | 1000 | 18 | Х |
3 | 2500 | 10 | Х |
4 | 7000 | 18 | Х |
Рисунок 1
При обратном расчете для первой строки Х плюс указанное в столбце «Ставка НДС, %» значение должно быть равно значению, приведенному в столбе «Сумма с НДС».
Для решения задачи нужно определить формулу, по которой будет рассчитываться значение Х
Выразим сумму с НДС с использованием Х и ставки процента, при этом сумму с НДС обозначим через S. В качестве примера рассмотрим расчет для первой строки
X + 18%X = S
Переведя значение величины процентов в доли единицы, получим
Х + 18/100 * Х = S
Х*(1 + 18/100) = S
Откуда Х = S / (1+18/100)
Теперь подставим в формулу не конкретное значение величины процентов, а общее, выразив его через Р
Х = S / (1+Р/100)
Запишем данную формулу в ячейку С2, подставляя вместо буквенных обозначений переменных адреса соответствующих ячеек
= А2/(1+В2/100)
При копировании формулы в нижние ячейки таблица примет вид, указанных на рисунке 2:
A | B | C | |
1 | Сумма без НДС | Ставка НДС,% | Сумма с НДС |
2 | 1000 | 18 | 847,46 |
3 | 2500 | 10 | 2 272,73 |
4 | 7000 | 18 | 5 932,20 |
Рисунок 2
Расчет сложных процентов (процент на процент)
Требуется при расчете процентов по банковскому вкладу с капитализацией процентов. В этом случае проценты, начисленные за истекший период, прибавляются к сумме вклада и дальнейшее начисление ведется уже на увеличенную сумму.
Данный способ является альтернативным к способу начисления процентов в конце суммы вклада, при котором заданная величина процентов прибавляется к итоговой сумме по истечению периода вклада и вычисление которых не вызывает затруднений.
Для решения данной задачи сперва необходимо определить, в каком виде требуется получить решение. Возможны следующие варианты:
- таблица с промежуточными результатами за каждый промежуточный период
- конечная цифра без промежуточных результатов.
Конечно, независимо от выбранного способа конечный результат должен совпадать.
Рассмотрим сначала первый вариант, с выводом промежуточных результатов.
При проведении расчетов нужно учитывать, что процентная ставка указывается обычно в процентах за год, в начисления процентов происходят ежемесячно, поэтому начисленная сумма должна делиться на 12 (число месяцев в году). Кроме того, начисления за каждый новый период должны осуществляться не на стартовую сумму, а на ту сумму, которая получилась после начисления процентов в прошлом периоде.
Итоговая таблица будет иметь вид, представленный на рисунке 3.
A | B | C | D | |
1 | Вычисление с использованием таблицы | |||
2 | № периода | Начальная сумма | Начисленная сумма | Конечная сумма |
3 | 1 | 10000,00 | 83,33 | 10083,33 |
4 | 2 | 10083,33 | 84,03 | 10167,36 |
5 | 3 | 10167,36 | 84,73 | 10252,09 |
6 | 4 | 10252,09 | 85,43 | 10337,52 |
7 | 5 | 10337,52 | 86,15 | 10423,67 |
8 | 6 | 10423,67 | 86,86 | 10510,53 |
9 | 7 | 10510,53 | 87,59 | 10598,12 |
10 | 8 | 10598,12 | 88,32 | 10686,44 |
11 | 9 | 10686,44 | 89,05 | 10775,49 |
12 | 10 | 10775,49 | 89,80 | 10865,29 |
13 | 11 | 10865,29 | 90,54 | 10955,83 |
14 | 12 | 10955,83 | 91,30 | 11047,13 |
15 | ||||
16 | Ставка, % | 10 |
Рисунок 3
Исходными данными являются начальная сумма (значение ячейки В3), а также процентная ставка, значение ячейки С16
По завершении 12-го периода сумма с начисленными процентами будет составлять 11047. 13 руб.
Рассмотрим формулы, с помощью которых получен данный результат.
Ячейка С3: = B3*C$17%/12
Ячейка D3: = B3 + C3
Ячейка B4: = D3
После записи формул они копируются по строки 14.
Аналогом формулы в ячейке B3 является запись B3*C$17/100/12
Знак «$» в данном случае обозначает абсолютную адресацию. Без его использования при копировании формулы в столбце С номер строки для ставки будет также увеличиваться, что приведет к ошибкам при вычислении.
Другим способом вычисления конечной суммы является использование формулы S = P*(1+ i)^N, где
S – сумма с начисленными процентами
P – исходная сумма
i – процентная ставка за период
N –количество периодов
Подставив в данную формулу наши значения, получим
= B3*(1+C17%/12)^12 = 11047,13
Такой же результат можно получить, использовав функцию СТЕПЕНЬ: = СТЕПЕНЬ(1+C17%/12;12)
Также для проведения данных вычислений подходит функция БС(), позволяющая определить будущую стоимость инвестиций. Изначально она предназначена для расчетов при условии постоянной процентной ставке и аннуитетных (равных) платежах, но обнулив третий параметр, ее можно использовать для расчета сложных процентов.
= - БС(С17%/12;12;;10000;0)
При использовании формул для расчета суммы с начисленными процентами применение абсолютной адресации является избыточным. Нужно обратить внимание, что функция БС() дает обратный результат и для получения положительного значения перед функцией следует использовать знак «-».
Решение системы линейных уравнений
Пусть есть система линейных уравнений (рис. 20).
Рисунок 20
Решим эту систему с использованием типовых возможностей Microsoft Excel двумя способами.
Первый способ – метод обратной матрицы
Запишем систему уравнений в Excel
Матрица коэффициентов | Свободные члены | |||
2 | 3 | 4 | 58 | |
3 | 4 | 2 | 59 | |
4 | 2 | 3 | 54 |
Рисунок 21
Решение системы линейных уравнений возможно только в том случае, если определитель матрицы не равен нулю. Поэтому первоначально рассчитаем определитель матрицы.
Для этого используем функцию МОПРЕД().
В свободной ячейке записывается функция МОПРЕД(), в качестве параметра функции передается диапазон ячеек матрицы коэффициентов (рис. 21).
В нашем случае определитель матрицы равен -27, поэтому решение системы уравнений возможно.
Далее для решения необходимо определить обратную матрицу. Для это используется функция МОБР(). Чтобы вычислить обратную матрицу, нужно
- выделить область ячеек, в которую будет помещен результат. Выделенная область должна совпадать по размерам с исходной матрицей;
- в строке формул записать функцию МОБР(диапазон), в качестве аргумента функции передается диапазон ячеек с исходной матрицей;
- нажать CTRL+SHIFT+ENTER
Чтобы решить систему уравнений, нужно умножить обратную матрицу на столбец свободных членов.
Для перемножения используется функция МУМНОЖ(). Функция имеет два аргумента. В качестве первого передается диапазон ячеек с обратной матрицей, в качестве второго – диапазон ячеек со свободными членами.
Порядок осуществления вычислений:
- выделить диапазон ячеек для ответов (в нашем случае – три вертикальные ячейки);
- в строке формул записать функцию МУМНОЖ(Матрица;Столбец)
- нажать CTRL+SHIFT+ENTER
Внимание! Если при вычислении обратной матрицы и умножения нажать ENTER – вычисления будут произведены только для одной ячейки!
Если все сделано правильно, то в столбец ответов будут подставлены результаты 5,8,6. Соответственно, X = 5, Y = 8, Z = 6.
Общий вид задания и решения приведен на рисунке 22
Рисунок 22
Использованные функции с примерами записи для контрольного примера
Вычисление определителя: = МОПРЕД(B5:D7)
Вычисление обратной матрицы: =МОБР(B5:D7)
Умножение матрицы на столбец: =МУМНОЖ(B15:D17;F5:F7)
Второй способ решения системы линейных уравнений – использование метода Крамера.
Для того, чтобы проверить, не является ли матрица вырожденной и что указанная система уравнений имеет только одно решение, найдем определитель матрицы.
Так как исходные данные у нас те же, то определитель матрицы у нас уже рассчитан.
Далее, согласно методу Крамера, столбцы матрицы поочередно меняются на столбец свободных членов и для каждой полученной матрицы рассчитывается определитель. Вычисления приведены на рисунках 23-25.
|
|
|
Рисунки 23- 25
Решения уравнений рассчитываются как отношения определителей полученных матриц к определителю исходной матрицы
В нашем случае получается
X = - 135 / (-27) = 5
Y = - 216 / (-27) = 8
Z = - 162 / (-27) = 6