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

Цель:

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

Задание:

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

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

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

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

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

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

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

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

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

10) Переход между ячейками можно осуществлять с помощью курсора (нажатие курсорных клавиш приводит к перемещению в соответствующую соседнюю ячейку, комбинация Ctrl+курсорная клавиша, перемещает в следующую не пустую ячейку), с помощью мыши (одиночное нажатие на ячейку перемещает выделение на нее, двойное нажатие на границу ячейки перемещает курсор на следующую не пустую ячейку в соответствующем направлении). Если Вам необходимо перейти в ячейку с известным номером пользуйтесь полем Имя (на рисунке ниже) – в ней нужно написать имя ячейки. Если Вы правильно выполнили пункт 2 текущей работы, стиль именования ячеек будет в виде "ЛатинскиеБуквыАрабскиеЦифры" (все столбцы будут именоваться комбинацией букв, строки - комбинацией цифр), если нет – стиль именования будет "RномерСномер" (после R (row – строка) – номер строки, после С (column – колонка) номер столбца).

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

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

13) Выделите диапазон ячеек А1:А2.

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

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

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

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

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

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

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

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

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

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

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

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

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 одним числовым значением. Сделать это можно несколькими способами. Первый – ввести значение в ячейку СЗ. Выделить диапазон С4:С63 и выполнить команду Правка – Вставить (Ctrl+C). Второй – ввести значение в ячейку СЗ, выделить диапазон СЗ:С63 и выполнить команду Правка – Заполнить – Вниз (Ctrl+D). Третий – ввести значение в ячейку СЗ, захватить черный квадратик в правом нижнем углу и растянуть рамку. При изменении размеров рамки диапазон может заполняться копиями либо прогрессией, для переключения режимов необходимо нажать клавишу Ctrl.

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

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

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

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