Лабораторная работа №2.4

Тема: Табличный процессор MS Excel

Цель: получение навыков работы с табличными процессорами (расчет схемы платежей по кредиту).

Задание:

1)Создайте новый документ MS Excel на Рабочем столе или в личной папке.

2)Откройте созданный документ. Откройте диалоговое окно: Сервис – Параметры, вкладка Общие и убедитесь, что снята галочка Стиль ссылок R1C1.

3)Зайдите в Свойства документа. Заполните свойства документа:

4)Перейдите во вкладку Прочие. Создайте новое свойство: Название – Версия, тип – число, Значение – 0,1.

5)Переименуйте Лист1 в "Исходные данные". Изменить цвет ярлычка на зеленый.

6)Переименуйте Лист2 в "Схема платежей". Изменить цвет ярлычка на красный.

7)Удалите Лист3.

8)В ячейку А1 введите информацию о банке, выдающем кредит, например – "Очень добрый банк".

9)В ячейку А2 введите информацию о названии кредита, например – "Кредит с хорошими условиями".

10)Переход между ячейками можно осуществлять с помощью курсора (нажатие курсорных клавиш приводит к перемещению в соответствующую соседнюю ячейку, комбинация Ctrl+курсорная клавиша, перемещает в следующую не пустую ячейку), с помощью мыши (одиночное нажатие на ячейку перемещает выделение на нее, двойное нажатие на границу ячейки перемещает курсор на следующую не пустую ячейку в соответствующем направлении). Если Вам необходимо перейти в ячейку с известным номером пользуйтесь полем Имя (на рисунке ниже) – в ней нужно написать имя ячейки. Если Вы правильно

11

выполнили пункт 2 текущей работы, стиль именования ячеек будет в виде "ЛатинскиеБуквыАрабскиеЦифры" (все столбцы будут именоваться комбинацией букв, строки – комбинацией цифр), если нет – стиль именования будет "RномерCномер" (после R (row – строка) – номер строки, после C (column – колонка) номер столбца).

11)Выделение диапазонов ячеек производится также несколькими способами. С помощью клавиатуры – переходите в ячейку, соответствующую одному из углов диапазона. Нажимаете Shift и с помощью курсорных клавиш расширяете диапазон. В поле Имя будет выводиться размер выделенной области. С помощью мыши – нажимаете на ячейку, соответствующую одному из углов диапазона и не отпуская левую кнопку мыши растягиваете выделение. Для выделения диапазона также можно использовать поле Имя – диапазон задается в виде "НачальнаяЯчейка двоеточие КонечнаяЯчейка".

12)Ввод значений и формул в ячейку можно производить несколькими способами. На листе – при двойном щелчке на ячейке. В Строке формул – она располагается справа от поля Имя. При выделении ячейки на листе в Строке формул отображается содержимое ячейки (значение или формула). Для введения функции в ячейке можно напрямую писать имя в ячейке, либо нажать на кнопку fx справа от Строки формул.

13)Выделите диапазон ячеек A1:A2.

14)Откройте окно Формат ячеек из контекстного меню выделенной области.

15)Во вкладке Число измените формат на Текстовый.

16)Во вкладке Шрифт измените начертание шрифта на Полужирный.

17)В ячейке В4 напишите "Условия банка"

18)В ячейке В5 напишите "наличие 2-НДФЛ"

19)Перейдите в ячейку В6.

20)Откройте окно Данные – Проверка, измените параметры как показано на рисунке ниже

12

21)В этом случае в ячейке появится выпадающее меню с фиксированным перечнем параметров "да" и "нет".

22)В ячейке В7 напишите "Сумма кредита".

23)В ячейке В8 введите сумму кредита.

24)В окне "Формат ячеек…" сделайте изменения как показано на рисунке ниже

25) Заполните следующие ячейки как показано на рисунке ниже.

13

26)Формат ячейки В10 – числовой, ячейки В12 – дата, ячейки В14 – денежный, ячейки В16, В18

– числовой.

27)При вводе в ячейке знака равно "=" в начале ячейка становится вычисляемой. В ней можно ссылаться на значения других ячеек и применять функции Excel. Например, в ячейке В18 вычисляется процент начисляемый банком в день на основании информации введенной в

ячейке В16.

28)Измените значение в ячейке В14 на формулу =В8/В10

29)В ячейке В20 напишите "Необходимые действия"

30)Ознакомьтесь с помощью по функции ЕСЛИ.

31)Используя команду ЕСЛИ, в ячейке В21 вывести "необходимо взять справку 2-НДФЛ в бухгалтерии" при выборе в ячейке В6 – "да", вывести "не нужно ходить в бухгалтерию", при выборе "нет" в ячейке В6.

32)На листе Схема платежей в ячейках В2, C2, D2, E2 напишите: Дата платежа, Сумма платежа, Проценты, Остаток

33)В столбце Дата платежа проставьте даты начиная с 1 месяца от даты начала кредитования с шагом в 1 месяц. Для этого можно воспользоваться функцией автозаполнения: в двух соседних ячейках столбца запишите даты 15.02.2009 и 15.03.2009. Выделите их. Справа внизу выделенного диапазона Вы увидите черный квадратик. Захватив его и растягивая рамку вниз, Excel будет автоматически заполнять диапазон значениями с шагом в 1 месяц. Также можно использовать команду Правка – Заполнить – Прогрессия.

34)Мы будем подразумевать, что срок кредитования не может превышать 60 месяцев, поэтому заполнять будем строки с 3 по 63.

35)В столбце C будем вводить суммы платежа. К примеру, Вы собираетесь гасить кредит равными долями. Необходимо заполнить все ячейки в диапазоне C3:C63 одним числовым значением. Сделать это можно несколькими способами. Первый – ввести значение в ячейку С3. Выделить диапазон С4:С63 и выполнить команду Правка – Вставить (Ctrl+C). Второй – ввести значение в ячейку С3, выделить диапазон С3:С63 и выполнить команду Правка – Заполнить – Вниз (Ctrl+D). Третий – ввести значение в ячейку С3, захватить черный квадратик в правом нижнем углу и растянуть рамку. При изменении размеров рамки диапазон может заполняться копиями либо прогрессией, для переключения режимов необходимо нажать клавишу Ctrl.

36)Запишите формулы в диапазон D3:E4 как показано на рисунке ниже. Обратите внимание, что можно ссылаться на ячейки других страниц, при этом название страницы записывается в

14

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

37)Если в ячейке написана формула, при растягивании рамки имена ячеек будут изменяться, подчиняясь арифметической прогрессии. Для абсолютной адресации ячеек (чтобы при копировании ссылка оставалась неизменной) перед именем столбца или строки (или и там и там) ставят знак $. На рисунке выше таким образом зафиксировано значение процента в день в ячейке D4.

38)Продолжите вычисления процентов в месяц и остатка по кредиту в столбцах D и E. Чтобы избежать появления отрицательных значений в столбце D используйте условие: если остаток по кредиту в предыдущем месяце меньше, либо равен нулю – значение в ячейке равно нулю. Чтобы избежать появления отрицательных значений в столбце E используйте условие: если разница между остатком по кредиту в предыдущем месяце и платежом в текущем месяце меньше нуля – значение в ячейке равно нулю.

39)На листе Исходные данные посчитайте сумму выплаченных процентов за все время пользования кредитом с использованием функции СУММ.

15