Практическая работа №10. Тема: ms excel icon

Практическая работа №10. Тема: ms excel




Скачати 209.73 Kb.
НазваПрактическая работа №10. Тема: ms excel
Дата30.04.2013
Розмір209.73 Kb.
ТипПрактическая работа

- -


Практическая работа № 10.


Тема: MS Excel. Использование основных финансовых и текстовых функций, функций даты и времени.

Цель: Научиться выполнять вычисления с использованием функций.

Время: 60 мин.

Задание: Изучите основные функции Excel по заданию, описанному ниже.


    1. Симонович С.В. Информатика. Базовый курс, стр. 316 – 339

    2. Игорь Пащенко. Excel 2007. Шаг за шагом

    3. Веденеева Е.А. Функции и формулы Excel

    4. Д. М. Златопольский. 1700 заданий по Excel
    Литература:



Последовательность выполнения работы:

  1. Использование основных финансовых функций

  1. Включите компьютер, войдите в систему и запустите табличный процессор Microsoft Excel.

  2. Присвойте первому листу имя «Фин. ф-ции», сразу сохраните рабочую книгу в папке «Мои документы» под именем «пр10» (пр10.xls).

  3. С помощью финансовых функций Excel решите задачу:

Клиент «ПУМБ» открыл депозит «Накопительный» сроком на 18 месяцев, сделав первоначальный вклад 5000 грн. В конце каждого месяца он пополняет депозит на 1000 грн. Банк начисляет ежемесячно сложные проценты по номинальной ставке 14,3% годовых. Какая сумма накопится на счёте к концу срока при сохранении на это время всех указанных условий без изменения? Выгоднее ли будет вклачику, если при том же пополнении банк будет начислять простые проценты по ставке 15,3% ?

^ Результаты расчётов отразите в отчёте.

  • Для решения этой задачи воспользуемся функцией БС (возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки):
    Синтаксис: БС(ставка,кпер,плт,[пс],[тип])


^ Функция БС имеет аргументы, указанные ниже:

Ставка — обязательный аргумент. Процентная ставка за период.

Кпер — обязательный аргумент. Общее количество периодов платежей по аннуитету.

Плт — обязательный аргумент. Выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным.

Пс — обязательный аргумент. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент "пс" опущен, предполагается значение 0. В этом случае аргумент "плт" является обязательным.

Тип — необязательный аргумент. Число 0 или 1, обозначающее срок выплаты. Если аргумент "тип" опущен, предполагается значение 0.

Тип

Выплата

0

В конце периода

1

В начале периода




Данные

Описание

14,3%

Годовая процентная ставка (ставка*12)

17

Количество платежей (кпер)

-1000

Объём платежей (плт)

=-(5000+5000*14,3%/12)

Стоимость на текущий момент (пс)

1

Платежи осуществляются в начале периода (тип)

Формула

Описание (результат)

=БС(A2/12; A3; A4; A5; A6)

Будущая стоимость инвестиции в соответствии с приведенными выше условиями

  • Скопируйте эту таблицу, поясняющую, как применять функцию БС, на лист рабочей книги Excel. ^ Для правильной демонстрации примера необходимо вставить его в ячейку A1 листа.

  • !!! В Excel переключение между режимами «формулы» - «значения» осуществляется сочетанием клавиш CTRL+` (тупое ударение) (CTRL + ~ или CTRL + Ё) или на вкладке Формулы в группе Проверить формулы нажмите кнопку Показывать формулы. Проверьте правильность скопированной формулы, убедитесь, что аргументами функции являются правильные адреса ячеек.

  • Обратите внимание, что «текущий момент» начинается не тогда, когда вкладчик положил деньги в банк, а тогда, когда он начал делать регулярные вложения (к тому времени уже «набежали» проценты на первоначальный взнос) и количество платежей за полтора года будет равно 17-и!

  • ^ Для того, чтобы лучше понять механизм действия формулы, составьте таблицу, поясняющую начисление сложных процентов и рост вклада:


Начисленные за месяц проценты на 5000


Предыдущий остаток + процент за прошлый месяц + 1000



Ежемесячные пополнения
(в конце месяца)



Начальный вклад



Начисленные за месяц проценты на 6059,5833



Месяц

Вклад
(пополнения)


^ Остаток по вкладу

процент

(за месяц)

1

5000

 

59,58333

2

1000

6059,58333

72,21003

3

1000

7131,79337

84,9872

4

1000

8216,78057

97,91664

5

1000

9314,69721

111,0001

6

1000

10425,6973

124,2396

7

1000

11549,9369

137,6367

8

1000

12687,5737

151,1936

9

1000

13838,7672

164,912

10

1000

15003,6792

178,7938

11

1000

16182,4731

192,8411

12

1000

17375,3142

207,0558

13

1000

18582,37

221,4399

14

1000

19803,8099

235,9954

15

1000

21039,8053

250,7243

16

1000

22290,5297

265,6288

17

1000

23556,1585

280,7109

18

1000

24836,8694

295,9727




 

25132,8421

 

В третьем и четвёртом столбце должны быть формулы – введите их самостоятельно (для уровня «Высокий», т.е. на 10 -12 баллов). Когда введёте формулы для третьего месяца, выделите эти 2 ячейки и с помощью маркера автозаполнения протягиванием скопируйте их в оставшиеся ячейки до конца таблицы.




^ Это другой вариант представления механизма сложных процентов с пополнением вклада.

  • Для сравнения ставок воспользуемся функцией ЭФФЕКТ():

Данные

Описание

0,143

Номинальная годовая процентная ставка

17

Количество периодов в году, за которые начисляются сложные проценты

Формула

Описание (результат)

=ЭФФЕКТ(A2;A3)*100%

Фактическая процентная ставка в соответствии с приведенными выше условиями

  1. Какую сумму родители должны каждый месяц, начиная от рождения ребёнка, перечислять на счёт в банке, чтобы к 18-летию ребёнка накопить 100000 гривен? Годовая процентная ставка – 13%.

  • ^ Используйте функцию ПЛТ:

Данные

Описание

0,13

Годовая процентная ставка

18

Предполагаемое число лет хранения сбережений

100000

Требуемый объем сбережений через 18 лет

Формула

Описание (результат)

=-ПЛТ(A2/12; A3*12; 0; A4)

Необходимая сумма месячного платежа для получения 100000 в конце восемнадцатилетнего периода

^ Знак «-» указывает на то, что банк отдаёт эту сумму, а не получает


  1. Использование основных функций «Дата и время»

  1. Составьте таблицу с датой рождения трёх – пяти ваших друзей по образцу:




A

B

C

D

E

F

G

1

п/п

Фамилия И.О.

^ Дата рождения

Год

Месяц

День

День недели

2

1

 

 02.12.1996

 

 

 

 

3

2

 

 15.07.1995

 

 

 

 

4

3

 

 

 

 

 

 




  • Для заполнения значениями столбца Год примените функцию Год() категории «Дата и время».

  • Столбцы «Месяц» и «День» заполняются соответственно с помощью функций Месяц() и День()

  • Определите значения для столба ДЕНЬ НЕДЕЛИ, для этого:

  • введите в ячейку G2 = и щелкните по ячейке C2, нажмите Enter; затем скопируйте введенную формулу во все нижестоящие ячейки (протягиванием, с помощью маркера автозаполнения);

  • выделите диапазон ячеек, содежащий день недели;

  • выполните Формат Ячейки все форматы, в поле «Тип» введите ДДДД, нажмите «Оk».

  1. Измените представление данных в столбце «Месяц» так, чтобы отбражалось название месяца.

  • Используйте текстовую функцию Текст(): =ТЕКСТ(C2;"ММММ")




  1. Использование текстовых функций.

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

СЦЕПИТЬ (текст1; текст2; …) – объединяет несколько текстовых строк в одну.

^ СЖПРОБЕЛЫ (текст) – удаляет из текста лишние пробелы (кроме одиночных пробелов между словами).

ЛЕВСИМВ (текст;кол_зн) – возвращает указанное количество знаков с начала строки текста.

^ НАЙТИ (строка;текст; поз) – возвращает номер позиции первого вхождения строки в текст, начиная с указанной позиции. Нумерация ведется относительно левого символа текста.

ДЛСТР(текст) – возвращает количество знаков в текстовой строке.

ПРАВСИМВ(текст;кол_зн) – возвращает указанное количество знаков с конца строки текста.

^ ПСТР (текст; поз; кол_зн) – возвращает заданное количество знаков из строки текста, начиная с указанной позиции.

ЗАМЕНИТЬ (текст; поз; кол_зн; строка) – заменяет в тексте начиная с указанной позиции заданное количество символов на другую строку.

  1. Есть список студентов группы в следующем виде:

Фамилия

Имя

Отчество

Бабкина

Дарья

Дмитриевна

Воронова

Виктория

Владимировна

Громов

Николай

Игоревич

Кононенко

Александр

Анатолиевич

Захарчук

Давид

Геннадиевич

Погоряная

Алина

Валериевна

Рыжик

Наталия

Васильевна

Середа

Елена

Сергеевна

Шляхто

Олеся

Олеговна

Васильченко

Екатерина

Романовна

Гуджен

Мария

Александровна

Евтихиева

Дарья

Алексеевна

Имашова

Анна

Сергеевна

Ковалева

Анастасия

Павловна

Козел

Алина

Олеговна

Лошакова

Ирина

Тимофеевна

Лощинина

Юлия

Геннадиевна

Олейникова

Светлана

Александровна

Олейник

Владлена

Сергеевна

Пивоварова

Наталия

Евгеньевна

Рабосевич

Сергей

Игоревич

Роянова

Татьяна

Ивановна

Товпыга

Валерия

Александровна

Усатова

Татьяна

Александровна

Хотеева

Надежда

Валериевна

Якубовская

Екатерина

Александровна

Нужно представить список несколько в другом виде, объединив фамилию, имя и отчество в одной ячейке:





A

B

C

1

Бабкина Дарья Дмитриевна

Аеев Андрей Иванович







2









3

Якубовская Екатерина Александровна











  • Эта задача легко решается с помощью одной функции ^ СЦЕПИТЬ. Выделив ячейку E1, с помощью Мастера функций в категории Текстовые выбираем эту функцию и заполняем нужные поля следующим образом

c:\temp\snaghtml7a7cdf.png

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

  • ^ Осталось только растянуть формулу на нужное количество строк. В столбце E список получен.

  • Скопируйте этот лист в эту же рабочую книгу:



  • Если удалить столбцы А:С, то список «разрушится», вместо нужных данных, в столбце появятся ошибочные значения #ССЫЛКА! – мы удалили ячейки, на которые были ссылки в формулах. Чтобы такого не произошло, нужно сначала получить новый список в ячейках в виде значений (а не формул). Для этого нужно выделить столбец E и скопировать его (в буфер). Затем выделить нужный столбец, диапазон или верхнюю ячейку диапазона и в меню Правка выбрать пункт Специальная вставка. В открывшемся окне выбрать радиокнопку Значения и нажать ОК. В выбранном диапазоне формулы заменятся на значения и полученный список может «существовать» совершенно независимо от исходного.

  1. Исходный список представьте в виде списка фамилий с инициалами:.




A

B

C

1

Бабкина Д.Д.

Аеев Андрей Иванович







2









3

Якубовская Е.А.











  • Выполним поставленную задачу последовательно. Сначала в ячейку D1 внесём формулу с функцией, которая выделяет первый символ из имени. Для этого с помощью Мастера функций в категории Текстовые выберем функцию ЛЕВСИМВ (рекомендуется в практической работе и дальше все функции вставлять в формулы с использованием Мастера функций).

c:\temp\snaghtmldb4c40.png

  • После сохранения формулы в ячейке D1 скопируем её в ячейку E1 для выделения первого символа отчества.

  • А затем с помощью функции СЦЕПИТЬ объединим нужные ячейки, пробел и точки.

c:\temp\snaghtmldef432.png

  • Перейти на строки 6, 7, и т.д. поможет ползунок на полосе прокрутки;

  • С помощью копирования и специальной вставки сохраним полученный список в виде значений в нужном диапазоне.

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

Заданный список:

Абраамян Люси Самвеливна

Бойко Елена Владимировна

Галушкина Алина Андреевна

Дьяченко Анастасия Эдуардовна

Ермакова Анна Андреевна

Коляса Светлана Васильевна

Королева Мария Сергеевна

Кривенко Юлия Сергеевна

Куюмджи Анастасия Витальевна

Лопаносова Наталья Владимировна

Митрофанова Александра Станиславовна

Могилко Елена Юрьевна

Никитенко Анастасия Олеговна

Панасюк Анастасия Владимировна

Пантелеева Юлия Вадимовна

Помазан Дарья Александровна

Репринцева Валерия Михаловна

Романюк Алина Александровна

Садчикова Виктория Викторовна

Свищева Анастасия Александровна

Симонова Анна Олеговна

Симонова Марина Владиславовна

Черепицкая Юлия Анатольевна

Черняк Елена Сергеевна

Шило Анна Вадимовна

  • При решении данной задачи будем считать, что между фамилией, именем и отчеством стоят ровно по одному пробелу. Если это не так, сначала приведем исходный список к такой структуре. Чтобы не усложнять задачу, будем выполнять действия последовательно, записывая в отдельные ячейки промежуточные результаты расчетов с использованием различных функций:

  • ^ В ячейку В1 – формулу, позволяющую удалить лишние пробелы:

= СЖПРОБЕЛЫ(А1)

  • В ячейку С1 – формулу, позволяющую найти позицию первого пробела:

^ =НАЙТИ(" ";B1)третий параметр не указан, т.к. ищем первое вхождение пробела от начала строки.

  • В ячейку D1 – формулу, позволяющую найти позицию второго пробела (между именем и отчеством):

^ =НАЙТИ(" ";B1;C1+1) – здесь третий параметр указывает, что поиск ведется со следующего символа от найденного в С1 пробела.

  • В ячейку E1 – формулу для определения общего количества символов в фамилии, имени и отчестве, включая два пробела.

=ДЛСТР(B1)

  • В ячейку F1 – формулу для выделения фамилии. Воспользуемся уже знакомой функцией.

=ЛЕВСИМВ(B1;C1-1)

  • Ячейку G1 пока пропустим.

  • В ячейку H1 – формулу для выделения отчества. Отчество расположено в конце строки, поэтому воспользуемся функцией ПРАВСИМВ, а количество выделяемых символов легко найдем, зная номер позиции пробела перед отчеством и общую длину строки.

=ПРАВСИМВ(B1;E1-D1)

  • В ячейку G1 – формулу для выделения имени. Имя расположено внутри строки между пробелами, позиции которых мы уже вычислили. Для его выделения воспользуемся функцией

=ПСТР(B1;C1+1;D1-C1-1)

  • С помощью копирования и специальной вставки сохраним полученный список в виде значений.




А если вы действительно уже профессионалы в компьютерных технологиях, то задания 7 и 9 выполните за одну-две минуты, даже не применяя формул и функций! В отчёте укажите, как это можно сделать…




Корюков И.В. Методический комплекс по дисциплине «Информатика и компьютерная техника». Практическая работа № 10 (2 курс)

Схожі:

Практическая работа №10. Тема: ms excel iconПрактическая работа № Тема: ms excel
Тема: ms excel. Использование основных математических, статистических и логических функций
Практическая работа №10. Тема: ms excel iconПрактическая работа №17 Тема: с оздание таблицы с получением внешних данных из Интернет. Работа с данными в ms
Тема: Создание таблицы с получением внешних данных из Интернет. Работа с данными в ms excel
Практическая работа №10. Тема: ms excel iconПрактическая работа № Тема: ms excel. Использование основных математических, статистических и логических функций. Цель: Научиться выполнять вычисления с использованием функций
Тема: ms excel. Использование основных математических, статистических и логических функций
Практическая работа №10. Тема: ms excel iconПрактическая работа № Тема: ms excel. Использование основных финансовых и текстовых функций, функций даты и времени. Цель: Научиться выполнять вычисления с использованием функций
Тема: ms excel. Использование основных финансовых и текстовых функций, функций даты и времени
Практическая работа №10. Тема: ms excel iconПрактическая работа №11. Тема: Автоматизация создания однотипных документов с разными реквизитами. Слияние документов Word и Excel
Цель: Научиться создавать и редактировать сложные связанные документы, получить практические навыки в формировании комплектов однотипных...
Практическая работа №10. Тема: ms excel iconЛабораторная работа №9 Работа с данными в ms excel. Запустите программу «Microsoft Excel»
Откройте книгу "Заказы" из папки LabExcel. Если этой книги нет, скопируйте ее из папки Teach диска M
Практическая работа №10. Тема: ms excel iconПрактическая работа «Создаем поздравительную открытку» 2 Практическая работа «Снеговик»
Разработка цор для интерактивной доски с преобразованием статичной графики в объект для свободного перемещения. 22
Практическая работа №10. Тема: ms excel iconПрактическая работа №15. Тема: Работа в среде редактора растровой графики. Знакомство с программой
Тема: Работа в среде редактора растровой графики. Знакомство с программой Adobe Photoshop. Создание простых изображений
Практическая работа №10. Тема: ms excel iconЛабораторная работа №6 Логические функции в ms excel. Запустите программу «Microsoft Excel»
Премия сотрудника вычисляется в зависимости от набранной суммы баллов по такому правилу
Практическая работа №10. Тема: ms excel iconПрактическая работа № Компьютерная графика 5 Практическая работа № Основы работы в графическом редакторе
Методические указания к выполнению заданий по дисциплине (для студентов 1 курса дневной формы обучения бакалавров по направлениям...
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©zavantag.com 2000-2013
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи