Опорный конспект лекции (стр. 1 )

Опорный конспект лекции

ФФСО ПГУ 7.18.2/05

Министерство образования и науки Республики Казахстан

Павлодарский государственный университет им. С. Торайгырова

Кафедра информатики и информационных систем

ОПОРНЫЙ КОНСПЕКТ ЛЕКЦИИ

по дисциплине Программные средства экономических расчетов

для студентов специальности 050703 «Информационные системы»

Павлодар

Лист утверждения опорного конспекта лекции

Ф СО ПГУ 7.18.1/05

УТВЕРЖДАЮ

Декан факультета ФМиИТ

___________ С. К.Тлеукенов «___»________________200_г.

Составитель: Преподаватель С

Кафедра информатики и информационных систем

Опорный конспект лекции

по дисциплине _____Программные средства экономических расчетов

для студентов специальности _050703____Информационные системы

Рекомендована на заседании кафедры от «___»____________200_г. Протокол №_____.

Заведующий кафедрой _________________________

Одобрена методическим советом факультета __________________ «_____»______________200_г. Протокол №____

Председатель МС_________________________

Тема №1 Использование встроенных функции MS Excel. Автоматизация типовых расчетов в среде EXCEL. (1ч)

Программа Excel входит в пакет Microsoft Office и предназначена для подготовки и обработки электорнных таблиц под управлением Windows.

Документом (т. е. объектом обработки) Excel является файл с произвольным именем и расширением. XLS. В терминах Excel такой файл называется рабочей книгой. В каждом файле XLS может размещаться от 1 до 255 электронных таблиц, каждая из которых называется рабочим листом.

Электронная таблица Excel состоит из 16384 ,а (Excel) строк и 256 столбцов, размещенных в памяти компьютера. Строки пронумерованны целыми числами от 1 до 16384

(или от 1 до 65536), а столбцы обозначены буквами латинского алфавита A, B, C, …Z, AA, AB,…IV. Структурный основной элемент Excel - ячейка- пересечение столбцов и строк. В любую ячейку можно ввести текст, число или формулу для расчета производной информации.

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

= А1+А2*3/А3. Если нужно указать ссылку на ячейку из другого листа данной рабочей книги, то пишется Баланс! А1. В качестве аргументов могут выступать числа, адреса ячеек, формулы, или функции. Например, СУММ(А1,МАКС(А2,А3)). Абсолютная адресация и относительная адресация ячеек. Абсолютной адресацией ячеек служит для того чтобы при перемещении курсора адрес конкретной(нужной) ячейки не изменяется. Для этого используют знак $, например, =СУММ($A$1:$A$2). Для удобства можно присвоить имя ячейке. Для этого нужно выделить ячейку затем выбрать ВСТАВКА - ИМЯ-ПРИСВОИТЬ. Имя не должно превышать 255 символов, между слов не должно быть пробелов, дефиса. Имя начинается с символа или символа подчеркивания. Большинство функции вводятся с помощью мастера функции.

Для задания формулы можно использовать различные технические приемы. При этом необходимо помнить, что формула должна всегда начинаться со знака “=”. Формула может содержать знаки арифметических операций: “+” , “-”, “*”(умножить), “/”(разделить), круглые скобки, функции, порядок вычисления которых соответствует принятому в математике.

Технический прием 1:

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

Технический прием 2:

ввести знак “=”, а затем вводить адреса ячеек с помощью щелчка на ячейке, адрес которой должен стоять в формуле и так же соединять их знаками арифметических операций.

Для вычисления суммы ячеек можно использовать клавишу , а затем выделить с помощью мыши диапазон ячеек, подлежащих суммированию. Если необходимо выделить диапазоны несмежных ячеек, необходимо нажать при выделении клавишу [Ctrl].

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

Механизм защиты ячеек от нежелательных изменений

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

Для того, чтобы защитить отдельный лист, следует выбрать команду “Защита” из главного меню “Сервис”, а затем команду “Защитить лист”(для защиты книги — выбрать команду “Защитить книгу”). При установке защиты можно задать пароль. Снять защиту с листа (книги) можно с помощью меню “Сервис” команды “Защита” и “Снять защиту листа”(“Снять защиту книги”).

Если необходимо защитить только ряд ячеек листа, то необходимо прежде, чем выполнять какие-либо действия четко представить, что Excel может защищать только лист целиком. Следовательно, прежде, чем выполнять защиту всего листа с данными, необходимо выполнить снятие защиты с тех ячеек, которые предназначены для хранения изменяющейся информации. Для этого необходимо выделить те ячейки, в которых не нужно защищать информацию, затем выбрать меню “Формат”, затем — команду “Ячейки”, а в открывшемся диалоговом окне выбрать вкладку “Защита” и убрать флажок из окошка “Защищаемая ячейка”. При установке защиты на весь рабочий лист или книгу выделенные ячейки не будут защищены от изменений. Например, необходимо вычислить с помощью Excel годовую и помесячную амортизацию трактора марки ДТ-75 М, первоначальная стоимость которого 280000 тенге, срок службы 6 лет.. Для этого необходимо составить следующую таблицу:

	A	B	C	D	Е
1	Первоначальная стоимость	Годы	Норма амортизации	Амортизация за год	Амортизация за месяц
2	280000	1-й	6/21	=A2*C2	=A2*C2/12
3		2-й	5/21	=A2*C3	=A2*C3/12
4		3-й	4/21	=A2*C3	=A2*C4/12
5		4-й	3/21	=A2*C4	=A2*C5/12
6		5-й	2/21	=A2*C4	=A2*C6/12
7		6-й	1/21	=A2*C4	=A2*C7/12

Пример. Объект — трактор, первоначальная стоимость 260000 тенге, срок службы 5 лет, норма амортизации 20%. При расчете регрессивным методом начисления амортизации, который состоит в том, что ее начисление производится не от первоначальной, а от балансовой (остаточной) стоимости объекта и при ликвидации объекта неамортизированная часть стоимости объекта выражает ликвидационную стоимость(стоимость металлолома, годных запчастей и др. материалов), нужно составить следующую таблицу.

	А	B	C	D	E
1	Годы	Стоимость трактора: 1-й год - первоначальная 2-й - 5-й - балансовая	Норма амортизации (%)	Сумма амортизации за год	Сумма амортизации за месяц
2	1-й	260000	20	=B2*C2/100	D2/12
3	2-й	=B2-D2	20	=B3*C2/100	D3/12
4	3-й	=B3-D3	20	=B4*C2/100	D4/12
5	4-й	=B4-D4	20	=B5*C2/100	D5/12
6	5-й	=B5-D5	20	=B6*C2/100	D6/12
7	Итого	=B6-D6		=СУММ(D2:D6)

Следует обратить внимание, что вычисление итоговой амортизации за 5 лет эксплуатации трактора производится с помощью функции Excel СУММ, которая помещается в клетку D7. Вызов этой функции можно выполнить двумя способами :

- с помощью клавиши и указания диапазона суммирования,

- с помощью вызова Мастера формул, с выбором из предлагаемого списка функций функции СУММ и указания диапазона суммирования.

Для отчисления амортизации в Excel предусмотрено ряд функций, с помощью которых можно рассчитать амортизацию применяемыми на территории бывшего СССР методами: АМР и АМГД.

Синтаксис АМР следующий:

АМР(стоимость, остаток, период).

Результат:

В качестве результата возвращается величина амортизации имущества за один период.

Аргументы:

стоимость - начальная стоимость имущества;

остаток - остаточная стоимость в конце амортизации (ликвидационная стоимость);

период - количество периодов, на протяжении которых имущество амортизируется.

Функция вычисляет амортизацию равномерным методом. Для того, чтобы вычислить амортизационные отчисления за год на объект стоимостью 280000 тенге, при этом срок его эксплуатации будет составлять 5 лет, а ликвидационной стоимости не предусмотрено, необходимо записать

АМР(280000;0;5), в результате в той клетке, где помещена данная функция, должно появиться число 56000. Если необходимо вычислить помесячную амортизацию, то в качестве периода вводится 60 (число месяцев за 5 лет).

Синтаксис АМГД следующий:

АМГД(стоимость, остаток, время эксплуатации, период).

Результат:

В качестве результата возвращается величина амортизации имущества для указанного периода.

Аргументы:

стоимость - начальная стоимость имущества;

остаток - остаточная стоимость в конце амортизации (ликвидационная стоимость);

время эксплуатации - количество периодов, на протяжении которых имущество амортизируется

период - промежуток времени, величина которого используется в качестве единицы измерения времени эксплуатации (как правило, год).

Функция вычисляет амортизацию методом кумулятивных чисел. Для того, чтобы вычислить амортизационные отчисления за год на объект стоимостью 280000 тенге, при этом срок его эксплуатации будет составлять 6 лет, а ликвидационной стоимости не предусмотрено, следует записать для первого года эксплуатации АМГД(280000;0;6;1), в результате в той клетке, где помещена данная функция, должно появиться число 80000, для второго года эксплуатации - АМГД(280000;0;6;2), для третьего года эксплуатации - АМГД(280000;0;6;3) и так далее. Если необходимо вычислить помесячную амортизацию, то в полученное значение амортизации делится на 12 (число месяцев за год).

280000	1	80 000,00
	2	66 666,67
	3	53 333,33
	4	40 000,00
	5	26 666,67
	6	13 333,33

Метод уменьшающегося остатка (регрессивный метод начисления амортизации) можно вычислить, применив функцию АМР следующим образом:

A	B		C
1	=	260000	АМР(В1;0;5)=	52 000,00.
2	В1-С1=	208000	АМР(В2;0;5)=	41 600,00.
3	В2-С2=	166400	АМР(В3;0;5)=	33 280,00.
4	В3-С3=	133120	АМР(В4;0;5)=	26 624,00.
5	В4-С4=	106496	АМР(В5;0;5)=	21 299,20.
ликвидная стоимость	В5-С5=	85197

См. литература: [4], стр.;