Лабораторная работа № 4
.1.pdfЛАБОРАТОРНАЯ РАБОТА № 4
Табличный процессор MS Excel (Часть 1)
Цель работы: получение навыков работы с табличными процессорами (расчет схемы платежей по кредиту).
Задания:
1)Создайте новый документ MS Excel в личной папке.
2)Откройте созданный документ. Откройте диалоговое окно: «Параметры Excel» > «Формулы» > раздел «Работа с формулами», убедитесь что снята галочка: «Стиль ссылок R1C1».
3)Зайдите в «Свойства документа». Заполните свойства документа:
4)Перейдите во вкладку «Прочие». Создайте новое свойство: Название – Версия, тип – число, Значение – 1.
5)Переименуйте Лист1 в "Исходные данные". Изменить цвет ярлычка на зеленый.
6)Переименуйте Лист2 в "Схема платежей". Изменить цвет ярлычка на красный.
7)Удалите ЛистЗ.
8)В ячейку А1 введите информацию о банке, выдающем кредит, например - "Очень добрый банк".
9)В ячейку А2 введите информацию о названии кредита, например - "Кредит с хорошими условиями".
10)Переход между ячейками можно осуществлять с помощью курсора (нажатие курсорных клавиш приводит к перемещению в соответствующую соседнюю ячейку, комбинация Ctrl+курсорная клавиша, перемещает в следующую не пустую ячейку), с помощью
Страница 1 из 6
мыши (одиночное нажатие на ячейку перемещает выделение на нее, двойное нажатие на границу ячейки перемещает курсор на следующую не пустую ячейку в соответствующем направлении). Если Вам необходимо перейти в ячейку с известным номером пользуйтесь полем Имя (на рисунке ниже) – в ней нужно написать имя ячейки. Если Вы правильно выполнили пункт 2 текущей работы, стиль именования ячеек будет в виде "ЛатинскиеБуквыАрабскиеЦифры" (все столбцы будут именоваться комбинацией букв, строки - комбинацией цифр), если нет – стиль именования будет "RномерСномер" (после R (row – строка) – номер строки, после С (column – колонка) номер столбца).
11)Выделение диапазонов ячеек производится также несколькими способами. С помощью клавиатуры – переходите в ячейку, соответствующую одному из углов диапазона. Нажимаете Shift и с помощью курсорных клавиш расширяете диапазон. В поле Имя будет выводиться размер выделенной области. С помощью мыши – нажимаете на ячейку, соответствующую одному из углов диапазона и не отпуская левую кнопку мыши растягиваете выделение. Для выделения диапазона также можно использовать поле Имя - диапазон задается в виде "НачальнаяЯчейка двоеточие КонечнаяЯчейка".
12)Ввод значений и формул в ячейку можно производить несколькими способами. На листе – при двойном щелчке на ячейке. В Строке формул – она располагается справа от поля Имя. При выделении ячейки на листе в Строке формул отображается содержимое ячейки (значение или формула). Для введения функции в ячейке можно напрямую писать имя в ячейке, либо нажать на кнопку fx слева от Строки формул.
13)Выделите диапазон ячеек А1:А2.
14)Откройте окно Формат ячеек из контекстного меню выделенной области.
15)Во вкладке Число измените формат на Текстовый.
Страница 2 из 6
16)Во вкладке Шрифт измените начертание шрифта на Полужирный.
17)В ячейке В4 напишите "Условия банка".
18)В ячейке В5 напишите "наличие 2-НДФЛ".
19)Перейдите в ячейку В6.
20)Откройте окно Данные – Проверка данных, измените параметры как показано на рисунке ниже:
21)В этом случае в ячейке появится выпадающее меню с фиксированным перечнем параметров "да"и"нет".
22)В ячейкеВ7напишите "Сумма кредита".
Страница 3 из 6
23)В ячейкеВ8введите суммукредита.
24)В окне "Формат ячеек…" сделайте изменения как показано на рисунке ниже:
25) Заполнитеследующиеячейкикакпоказанонарисункениже:
Страница 4 из 6
26)Формат ячейки В10 – числовой, ячейки В12 – дата, ячейки В14 – денежный, ячейки В16, В18 – числовой.
27)При вводе в ячейке знака равно "=" в начале, ячейка становится вычисляемой. В ней можно ссылаться на значения других ячеек и применять функции Excel. Например, в ячейке В18 вычисляется процент начисляемый банком в день на основании информации введенной в
ячейке В16.
28)Измените значение в ячейке В14 на формулу =В8/В10.
29)В ячейкеВ20напишите"Необходимые действия".
30)ОзнакомьтесьвПомощисфункциейЕСЛИ.
31)Используя команду ЕСЛИ, в ячейке В21 вывести "необходимо взять справку 2-НДФЛ в бухгалтерии" при выборе в ячейке В6 – "да", вывести "не нужно ходить в бухгалтерию", при выборе "нет" в ячейке В6.
32)На листе Схема платежей в ячейках В2, С2, D2, Е2 напишите: Дата платежа, Сумма платежа, Проценты, Остаток.
33)В столбце Дата платежа проставьте даты начиная с 1 месяца от даты начала кредитования с шагом в 1 месяц. Для этого можно воспользоваться функцией автозаполнения: в двух соседних ячейках столбца запишите даты 15.02.2009 и 15.03.2009. Выделите их. Справа внизу выделенного диапазона Вы увидите черный квадратик. Захватив его и растягивая рамку вниз, Excel будет автоматически заполнять диапазон значениями с шагом в 1 месяц. Также можно использовать команду «Главная» > «Заполнить» > «Прогрессия…».
34)Мы будем подразумевать, что срок кредитования не может превышать 60 месяцев, поэтому заполнять будем строки с 3 по 63.
35)В столбце С будем вводить суммы платежа. Например, Вы собираетесь гасить кредит равными долями. Необходимо заполнить все ячейки в диапазоне СЗ:С63 одним числовым значением.
Страница 5 из 6
Сделать это можно несколькими способами. Первый – ввести значение в ячейку СЗ. Выделить диапазон С4:С63 и выполнить команду Правка – Вставить (Ctrl+C). Второй – ввести значение в ячейку СЗ, выделить диапазон СЗ:С63 и выполнить команду Правка – Заполнить – Вниз (Ctrl+D). Третий – ввести значение в ячейку СЗ, захватить черный квадратик в правом нижнем углу и растянуть рамку. При изменении размеров рамки диапазон может заполняться копиями либо прогрессией, для переключениярежимовнеобходимо нажать клавишу Ctrl.
36)Запишите формулы в диапазон D3:E4 как показано на рисунках ниже. Обратите внимание, что можно ссылаться на ячейки других страниц, при этом название страницы записывается в одинарных кавычках и перед ячейкой ставится восклицательный знак. Имена ячеек с других страниц можно указывать щелчком мыши (в режиме редактирования содержимого ячейки можно переходитьна необходимую страницу, либо вдругую книгу).
37)Если в ячейке написана формула, при растягивании рамки имена ячеек будут изменяться, подчиняясь арифметической прогрессии. Для абсолютной адресации ячеек (чтобы при копировании ссылка оставалась неизменной) перед именем столбца или строки (или и там и там) ставят знак $. На рисунке выше, таким образом, зафиксировано значение процента в день в ячейке D4.
38)Продолжите вычисления процентов в месяц и остатка по кредиту в столбцах D и Е. Чтобы избежать появления отрицательных значений в столбце D используйте условие: если остаток по кредиту в предыдущем месяце меньше, либо равен нулю – значение в ячейке равно нулю. Чтобы избежать появления отрицательных значений в столбце Е используйте условие: если разница между остатком по кредиту в предыдущем месяце и платежом в текущем месяце меньше нуля – значение в ячейке равно нулю.
39)На листе «Исходные данные» рассчитайте сумму выплаченных процентов за все время пользования кредитом с использованием функции СУММ.
Страница 6 из 6