Результирующий вид таблицы
Задание 4:
пользуясь возможностями Excel,
создать таблицу расчета пенсии по
старости.
Пенсия
= мин.зарплата *4
Прибавка
= военный стаж * 30%мин.зарплаты + (трудовой
стаж – 20)*10%мин.зарплаты
Всего
= пенсия + прибавка
Итого
рассчитываем
с помощью автосуммы
Внешний вид рабочего листа
Порядок работы
Переименуйте
Лист 4 в
«Задание 4»Составить
таблицу
по образцу для расчета пенсии для 3
человек.Заполнить
таблицу
формулами для расчета согласно заданным
правилам.Задать
пользовательский формат столбцов
«Стаж» так, чтобы значения выводились
с подписью «лет»:откройте
меню «Формат ячеек», вкладку «Числовой
формат», в которой выберите из списка
«Числовые форматы» строку «Все форматы»в
поле ввода числового формата ввести
строку Основной
“ лет”(пробел
и слово Основной являются
обязательными!!!!!!)
Оформить
рабочий лист согласно
образцу, используя возможности
форматирования ячеек.
Результирующий вид таблицы
Ориентировочные
основы действий (ООД) для практической
работы:
Прочитайте
краткие теоретические сведения по
данной теме.Ответьте
на вопросы для самоконтроля.Сделайте
основные задания для самоподготовки.
Перечень знаний,
навыков и умений, которыми студент
должен овладеть в результате изучения
данной темы:
Производить
редактирование и форматирование таблиц.Использовать
мастер функций для проведения вычислений.
Элементы
окна Microsoft Excel 2010
Как и все окна в
операционной системе Windows 7, окно Microsoft
Excel 2010
(рис.
1) имеет заголовок (вверху окна, где в
середине стоит надпись Microsoft Excel), в
правой
части которого есть стандартные кнопки
управления окном.
Рис.
1. Окно
Microsoft Excel 2010
В левой части
заголовка располагается значок
приложения. Справа от него расположена
Панель быстрого доступа
На ней первоначально
расположены (слева направо) кнопки
Сохранить, Отменить действие, Повторить
действие. Ниже заголовка расположена
лента, как и в Microsoft Word 2010. Названия
вкладок на ленте следующие: Файл, Главная,
Вставка, Разметка страницы, Формулы,
Данные, Рецензирование и Вид. Для того
чтобы увидеть вкладку, надо щелкнуть
по соответствующему названию вкладки
(ярлыку). Первоначально вы видите вкладку
с названием Главная. Напомним, что
вкладка – это полоса с размещенными на
ней управляющими элементами (кнопками,
раскрывающимися списками, полями и т.
д.). Несколько элементов могут быть
объединены в группу. В этом случае они
заключены в рамку, в нижней части которой
находится название группы. Группы Буфер
обмена и Шрифт полностью соответствуют
аналогичным группам в Microsoft Word 2010.
Под
вкладками размещается строка
формул. Все,
что вы вводите в текущую ячейку,
отображается
и в строке формул справа от значка
Возникает
закономерный вопрос: для чего же в двух
местах отображать одно и то же? Есть
случаи, когда это необходимо. Например,
при вводе формулы для расчета значений
в таблице в строке формул вы видите саму
формулу, а в ячейке таблицы – результат
расчета.
В основном рабочем
поле Microsoft Excel 2010 располагается
разлинованный лист.
Над
листом (ниже строки формул) находятся
обозначения столбцов таблицы. Это
латинские буквы в алфавитном порядке.
Слева от листа
расположены обозначения строк. Это
числа по возрастанию.
С
помощью этих обозначений можно определить
текущую ячейку таблицы. Очень
похоже
на обозначения клеток на шахматной
доске. Это необходимо для работы с форму-
лами
и некоторыми другими инструментами
Microsoft Excel 2010.
Текущая ячейка
отмечена прямоугольной рамкой с
квадратиком в правом нижнем углу, как
показано на рис. 2.
Рис.
2. Текущая
ячейка на Листе
Снизу
слева разлинованного листа Microsoft Excel
2010 видны наименования Листов,
с
которыми можно одновременно работать.
Дело в том, что документ, с которым вы
работаете в Microsoft Excel, называется Книга.
В
книгу входят несколько Листов. Вы
самостоятельно можете добавлять Листы
в Книгу или убирать ненужные.
Переходить от
одного Листа к другому можно, щелкнув
по названию Листа. По умолчанию Листы
называются «Лист1», «Лист2», «Лист3» и
т. д., но можно задать и свое название
для любого Листа или для всех Листов
Книги. Для этого достаточно щелкнуть
правой кнопкой мыши по названию Листа,
которое вы хотите изменить, выбрать в
появившемся контекстном меню команду
Переименовать и ввести нужное название.
После этого щелкните по любой ячейке
Листа, и новое название будет установлено.
Изменить масштаб
изображения на Листе можно, перетаскивая
мышью (с нажатой
левой
кнопкой) бегунок
в правом нижнем углу окна Microsoft Excel 2010.
Перетаскивая его ближе к кружку со
знаком минус, вы уменьшаете масштаб
(отдаляете Лист от себя), а перетаскивая
ближе к кружку со знаком плюс, вы
увеличиваете масштаб (приближаете к
себе Лист). В отличие от Microsoft Word 2010
горизонтальная полоса прокрутки в
Microsoft Excel 2007 занимает не всю ширину окна,
а располагается в правом нижнем углу.
Ввод
данных в ячейки
Для ввода любых
данных в ячейки таблицы достаточно
щелчком мыши или клавишами со стрелками
на клавиатуре установить рамку текущей
ячейки на нужную ячейку и набрать данные
на клавиатуре.
Для окончания
ввода и перемещения к другой ячейке,
находящейся ниже, можно
нажать
на клавиатуре клавишу Enter. Для окончания
ввода и перемещения к другой ячейке,
находящейся правее, можно нажать на
клавиатуре клавишу Tab. Никакой
предварительной подготовки для
формирования новой таблицы не нужно.
Просто
вводите в ячейки нужные вам значения.
На одном Листе может располагаться
несколько таблиц. Вы сами, вводя значения
ячеек, определяете их границы.
Сохранение
документа на диске
Как мы уже говорили,
вы работаете с набором Листов, объединенных
в Книгу. Таким
образом,
документ с введенными вами данными,
который вы сохраняете на диске, является
Книгой. В результате сохранения Книги
на диске образуется файл с расширением.
xlsx (версии до Microsoft Excel 2007 сохраняли Книгу
в файлах с расширением. xls). Для сохранения
текущей Книги на диске в любой момент
времени достаточно щелкнуть мышью по
кнопке
При первом сохранении
Книги на экране появится стандартное
окно сохранения
файла,
где можно указать папку, в которой вы
собираетесь хранить файл с Книгой, и
задать имя для файла. Затем щелкните
мышью по кнопке Сохранить.
При
последующих щелчках мыши по кнопке
такое окно появляться не будет, потому
что Microsoft Excel 2010 уже знает, в каком файле
хранится ваша Книга и будет сохранять
последние изменения, сделанные в Книге,
именно в нем.
Если вы желаете
сохранить текущие изменения в Книге в
файле с другим именем,
щелкните
мышью по ярлыку Файл и в появившемся
меню щелкните мышью по пункту
Сохранить
как. При этом откроется уже знакомое
вам стандартное окно сохранения файла.
Можно
сохранить данные Книги и в некоторых
других форматах. Для этого в окне
сохранения
файла в раскрывающемся списке Тип файла
выберите соответствующий формат.
Окончание
работы с Microsoft Excel 2010
Для завершения
работы с Microsoft Excel 2010 щелкните мышью по
стандартной
кнопке
закрытия окна Microsoft Excel 2010 (кнопка с
крестом в верхнем правом углу окна)
или
щелкните мышью по ярлыку Файл и в
появившемся меню щелкните мышью по
пункту
Выход.
Если последние изменения Книги, с которой
вы работали, сохранены в файле, работа
Microsoft Excel 2010 будет завершена и его окно
закроется.
Если же Microsoft Excel
2010 обнаружил, что вы не сохранили
последние изменения в документе, на
экране появится диалоговое окно с
вопросом «Сохранить изменения?» Если
вы щелкните мышью по кнопке Да, изменения
будут сохранены, а Microsoft Excel 2010
завершит
свою работу. Если вы щелкните по кнопке
Нет, изменения не
будут сохранены,
а Microsoft Excel 2010 завершит свою работу. Если
вы щелкните мышью по кнопке Отмена,
можно
будет продолжить редактирование текущей
Книги.
Чтобы продолжить
работу с сохраненной ранее Книгой, надо
открыть ее. Сделать
это
можно, если щелкнуть мышью по ярлыку
Файл, а затем в появившемся меню выбрать
В результате
появится стандартное окно открытия
файла, где нужно найти папку, в
которой
сохранена ваша Книга, щелкнуть мышью
по имени файла с Книгой, а затем – по
Если вы не очень
давно работали в Microsoft Excel 2010 с Книгой,
которую собираетесь редактировать,
можно щелкнуть мышью по ярлыку Файл и
в правой части появившегося меню отыскать
название своей Книги в списке Последние
документы. Для открытия Книги просто
щелкните мышью по ее названию в списке.
Вставка
ячеек, строк и столбцов
Если в уже набранную
часть таблицы нужно вставить новую
ячейку, столбец или
строку,
щелкните мышью по стрелке вниз на кнопке
В
результате появится меню, где нужно
выбрать, что именно вы хотите вставить.
Если выбрать вставку нового столбца,
он вставится слева от столбца, где
расположена
Если выбрать
вставку новой строки, она вставится
выше строки, в которой располо-
жена
текущая ячейка. Если выбрать вставку
новой ячейки, появится диалоговое окно
(рис. 3), где можно выбрать различные
варианты вставки как одиночной ячейки,
так и столбца, и строки.
Рис.
3. Окно
Добавление ячеек
В первых двух
вариантах на место текущей ячейки будет
вставлена одиночная пустая ячейка, а
остальные будут сдвинуты соответственно
вправо или вниз.
Ширину любого
столбца можно менять перетаскиванием
правой его границы. Для
этого
нужно навести указатель на разделительную
линию между латинскими буквами,
обозначающими столбцы (под строкой
формул), так чтобы он принял вид
Затем
с нажатой левой кнопкой мыши перетащите
разделительную линию, увеличивая
или
уменьшая ширину столбца. При этом ширина
остальных столбцов останется неизменной
(рис. 4).
Рис.
4. Изменение
ширины столбца
Высота строк
изменяется аналогичным образом. С
помощью мыши надо перетащить
границу
между клетками с числами для обозначения
строк (рис. 5).
Рис.
5. Изменение
высоты строки
Можно задавать
различные параметры для каждой из ячеек,
например устанавливать обрамление или
менять цвет фона. Однако, если ячеек в
таблице много, очень долго делать одно
и то же действие для каждой ячейки
персонально. В этом случае можно выделить
группу ячеек и выполнить действие для
всех выделенных ячеек сразу. Чтобы
выделить группу ячеек, установите
указатель мыши на ячейку в верхнем левом
углу выделяемой группы, нажмите левую
кнопку мыши и, не отпуская ее, переместите
указатель мыши на ячейку, находящуюся
в правом нижнем углу выделяемой группы
(рис. 6). Отпустите левую кнопку мыши.
Рис.
6. Выделение
группы ячеек
Все ячейки выделенной
группы объединены рамкой, такой же,
какой была рамка
вокруг
текущей ячейки. Можно выделять сразу
несколько групп ячеек. Для этого сначала
выделите первую из групп, как было
описано выше. После этого все следующие
группы выделяйте так же, но нажав и
удерживая клавишу Ctrl на клавиатуре.
Если щелкнуть
мышью по прямоугольнику с латинской
буквой (под строкой формул),
обозначающему
столбец, выделится сразу весь столбец.
Если щелкнуть мышью по прямоугольнику
с числом (в левой части окна Microsoft Excel
2010), обозначающему номер строки, выделится
сразу вся строка. Как мы уже говорили,
над всеми ячейками выделенной группы
можно одновременно проделывать различные
действия, например устанавливать
параметры шрифта, выравнивание текста
и т. д.
Бывают случаи,
когда нужно объединить несколько ячеек,
например, при создании
шапок
таблиц. Microsoft Excel 2010 позволяет это сделать.
При этом должно быть выполнено условие:
заполнена может быть только левая ячейка
выделенного диапазона, а остальные
ячейки должны быть пустыми.
Для
слияния нескольких ячеек в одну надо
выделить их, а затем щелкнуть мышью по
кнопке
на вкладке Главная.
Выделенные ячейки
объединятся в одну, а данные в объединенной
ячейке разместятся с выравниванием по
центру (рис. 7).
Воспользоваться
другими вариантами объединения ячеек,
а также отменить объединение можно,
если щелкнуть по стрелке вниз в правой
части кнопки
и
выбрать соответствующую команду из
появившегося меню.
Для любой одиночной
ячейки и для группы выделенных ячеек
можно установить
выравнивание
содержимого как по горизонтали, так и
по вертикали.
Для
установки нужного вам выравнивания по
горизонтали воспользуйтесь кнопками
на вкладке Главная.
Для
выбора выравнивания по вертикали
предусмотрены кнопки
на вкладке Главная.
Линии, разделяющие
ячейки таблицы, служат для обозначения
границ ячеек и не выводятся при печати
Листа на принтере. Можно сделать все
или часть линий видимыми, причем типы
линий можно задавать различные.
Сделайте текущей
ячейку, обрамление которой нужно
установить, или выделите
группу
ячеек, для которых нужно установить
обрамление.
Щелкните
мышью по стрелке в правой части кнопки
(она расположена на вкладке Главная) и
в появившемся списке выберите нужный
вам вариант обрамления.
Таким образом,
выделив всю таблицу и выбрав обрамление
или выделяя отдельные
части
таблицы и включая отдельно обрамления
для каждой части (в случае таблиц со
сложными шапками), можно при печати
Листа на принтере получить красиво
оформленный документ (рис. 8).
Рис.
8. Обрамление
ячеек таблицы
Вы, наверное,
заметили, что при вводе данных в ячейки
по умолчанию включаются
различные
выравнивания. Если вы вводите в ячейку
только цифры, содержимое автоматически
выравнивается по правому краю. Если в
ячейку введены различные буквы, содержимое
автоматически выравнивается по левому
краю.
Это происходит
потому, что содержимое ячеек можно
классифицировать по типам и в зависимости
от этой классификации обрабатывать
определенным образом. Например, если
группа ячеек столбца содержит числа,
можно подсчитать итоговую сумму этих
чисел. А для этого Microsoft Excel 2010 должен
определить, что в ячейках только числа.
Конечно, если
Microsoft Excel 2010 автоматически выровнял по
правому краю столбец с числами, не
обязательно оставлять именно такое
выравнивание. Просто по этому признаку
мы увидели, что Microsoft Excel 2010 воспринял
введенные нами значения как числа.
Вполне можно после ввода столбца чисел
выделить его и установить любое
выравнивание, которое нам нравится.
Вы можете сами
указать Microsoft Excel 2010, как воспринимать
введенные вами данные. Для этого щелкните
мышью по ячейке, для которой хотите
указать тип данных, или выделите группу
ячеек.
На вкладке Главная
отыщите группу Число, а в ее верхней
части раскрывающийся список (рис. 9).
Рис.
9. Установка
формата данных
Щелкните мышью по
стрелке вниз в правой части раскрывающегося
списка и выбе-
рите
нужный формат, например, как на рис. 10.
Рис.
10. Установка
денежного формата данных
Чтобы подробнее
указать требуемый формат, можно в этом
раскрывающемся списке выбрать пункт
Другие числовые форматы. При этом
откроется диалоговое окно с несколькими
вариантами каждого из предложенных вам
типов данных. Например, в этом окне можно
установить для числовых данных количество
знаков после запятой. По умолчанию
ставятся два знака, но вы можете установить
большую точность (до 30 знаков). Таким
образом, с помощью таблиц в Microsoft Excel
2010 можно с успехом решать и некоторые
инженерные задачи.
Быстро увеличить
или уменьшить количество разрядов после
запятой для числовых
форматов
позволяют кнопки
на вкладке Главная.
Выполнение расчетов
в таблицах Excel
осуществляется при помощи формул.
Формулой
называется введенная в ячейку
последовательность символов, начинающаяся
со знака равенства «=». В эту
последовательность символов могут
входить: константы, адреса ячеек, функции,
операторы.
Результат вычислений
отображается в ячейке, а сама формула
– в строке формул.
Функции используются
для выполнения стандартных вычислений.
Excel
имеет более 400 встроенных функций,
объединенных в 9 групп: финансовые, дата
и время, математические, статистические,
ссылки и массивы, работ м базой данных,
текстовые, логические, проверка свойств
и значений.
Кроме встроенных
функций можно использовать в вычислениях
пользовательские функции, которые
создаются при помощи средств Excel.
Функция ПЛТ вычисляет суммы периодических платежей, необходимых для сведения текущего баланса (пс) к нулю или некоторому другому значению (бс). Синтаксис этой функции следующий: ПЛТ(ставка;клер;пс;бс;тип).
Вычисление платежей по займу
Когда деньги берут в кредит, ключевым вопросом является величина периодических платежей. В предлагаемом примере предполагается, что вы покупаете машину стоимостью 32 тысячи долларов в кредит, и вам нужно вычислить сумму ежемесячных платежей. Вы внесли авансовый платеж размером в 4 тысячи долларов, а дилер предложил воспользоваться кредитом на четыре года с процентной ставкой 2,1% годовых (см рис. 1 и 2): =ПЛТ(,021/12;4*12;28000;0;0).
Рис. 1. Вычисление будущей стоимости вклада и платежей
Рис. 2. Вычисление платежей по займу
Вычисление пенсионных платежей
В некоторые вычисления платежей нужно включать величину будущей стоимости. В следующем примере предположим, что на пенсионном счету у вас имеется 700 тысяч долларов. Теперь предположим, что вам нужно рассчитать снимаемые с этого счета суммы на следующие 20 лет так, чтобы на нем в результате осталось 100 тысяч долларов. Именно такие суммы вы сможете тратить каждый месяц (рис. 11.8): =ПЛТ(,06/12;20*12;-700000;100000;0).
Рис. 3. Вычисление выплат с пенсионного счета
Если вы точно знаете банковский процент на вклад (6%), то сможете снимать с пенсионного счета ежемесячно по 4798,59 долларов в течение 20 лет, и в результате у вас останется еще 100 тысяч долларов.
Функция КПЕР используется для определения количества платежей, необходимых для выплаты займа или накопления на счету определенной суммы. Ее синтаксис следующий: =КПЕР(СТАВКА;ПЛТ;ПС;БС;ТИП).
Количество лет до выхода на пенсию
Если вы знаете, сколько денег вам нужно, чтобы выйти на пенсию, и осуществляете регулярные платежи на пенсионный счет, то можете воспользоваться функцией КПЕР и вычислить возраст, в котором вам можно будет выйти на пенсию.
Предположим, что для выхода на пенсию вам нужно полмиллиона долларов, и вы вносите ежемесячно на счет по сто долларов. Далее предположим, что текущий баланс на пенсионном счету составляет 350 тысяч долларов. Следующая формула вернет количество лет, через которые вы сможете выйти на пенсию: =КПЕР(,1/12;-100;-350000;500000;0).
Предположим, что вы получаете по своим инвестициям 10% годовых; в этом случае функция вернет значение 41,8 месяцев, т.е. три с половиной года. Если вы знаете, сколько денег вам будет достаточно на неделю, и хотите накопить такую сумму, чтобы обеспечить себя на 20 лет, то можете скомбинировать функции КПЕР и ПС, и вы узнаете, через какой срок накопите нужную для пенсии сумму: =КПЕР(,1/12;-100;-350000;ПС(,1/52;20*52;-1000;0;0);0).
Функция ПС используется в качестве аргумента БС; предполагается, что вам каждую неделю нужно снимать в течение 20 лет по тысяче долларов и что вы имеете 10% годовых. При этих предположениях вы сможете выйти на пенсию через 2,4 года. Обе формулы, описанные в настоящем разделе, показаны в работе на рис. 1.
Рис. 1. Использование функции КПЕР для пенсионных расчетов
Досрочное погашение кредита
В прошлые годы множество людей рефинансировали свои закладные на дома, чтобы получить выгоду от падающих процентных ставок. Функцию КПЕР можно использовать для подсчета, насколько меньше платежей придется выполнить в схеме с рефинансированием.
В следующем примере предполагается, что вы имеете закладную на 200 тысяч долларов под 7,5% годовых. Следующие 20 лет вам придется вносить по 1611,16 долларов в месяц. Если рефинансировать в 5,75%, но оставить сумму платежей без изменений, следующая формула поможет подсчитать, на сколько лет раньше вы сможете погасить заем (рис. 2): =(20*12)-КПЕР(,0575/12;ПЛТ(0,075/12;20*12;200000/0);200000;0;0).
В качестве аргумента платежей используется функция ПЛТ, вычисляющая значение 1611,19 — это размер выплат по условиям текущей закладной. Вычтем полученный результат из 240 месяцев (т.е. двадцати лет) и получим, что в схеме с рефинансированием кредит можно будет погасить на 51 месяц раньше.
Рис. 2. Вычисление эффекта досрочного погашения кредита
