 |
Задачи для самостоятельного решения
|
|
|
|
Задача 1. Для ячеек диапазона А1:А10 обеспечьте ввод только текста.
Задача 2. Для ячеек диапазона В1:В10 обеспечьте ввод чисел таких, что значение в каждой следующей ячейке должно быть больше, чем в предыдущей.
Задача 3. Для ячеек диапазона С1:С10 обеспечьте ввод только уникальных значений (вводить повторяющиеся значения нельзя).
Задача 4. Для ячеек диапазона D1:D10 обеспечьте ввод только текста, начинающегося с буквы «а».
Задача 5. Для ячеек диапазона Е1:Е10 обеспечьте ввод только четных чисел.
Задача 6. Для ячеек диапазона F1:F10 обеспечьте ввод значений из списка, заданного в команде.
Задача 7. Для ячеек диапазона G1:G10 обеспечьте ввод значений из списка, заданного на другом листе.
Задача 8. Введите данные в ячейки А1:G10, в том числе и не удовлетворяющие условиям. Выполните проверку введенных данных, найдите ошибки. Научитесь находить ячейки, имеющие условия на значения.
Задача 9. Научитесь находить зависимые ячейки, влияющие ячейки, пошагово вычислять формулы.
Работа с внешними данными
Получение данных из текстовых файлов (импорт текстового файла)
1. Щелкните ячейку, в которую требуется поместить данные из текстового файла. Чтобы внешние данные не заменили имеющиеся данные, ниже и правее выбранной ячейки не должно быть никаких данных.
2. В меню Данные укажите на пункт Импорт внешних данных и выберите команду Импортировать данные.
3. В поле Тип файлов выберите Текстовые файлы.
4. В списке Папка найдите и дважды щелкните текстовый файл, который требуется импортировать в качестве диапазона внешних данных.
5. Следуйте инструкциям мастера текстов для указания способа разбиения текста на столбцы.
6. Чтобы задать форматирование и разметку импортируемых данных, нажмите в диалоговом окне Импорт данных кнопку Свойства.
|
|
|
7. В диалоговом окне Импорт данных выполните одно из следующих действий:
o чтобы поместить данные в выбранную ячейку, выберите вариант Имеющийся лист;
o чтобы поместить данные на новый лист, выберите вариант Новый лист. Microsoft Excel добавит в книгу новый лист и автоматически поместит диапазон внешних данных в левый верхний угол нового листа.
Задачи для самостоятельного решения
Задача 1.
§ Создайте список (базу данных) в текстовом формате, используя стандартную программу Блокнот, как показано на Рис. 1.
§ Данные в столбцах разделяйте знаками табуляции.
§ Документ назовите Сотрудники.
§ Создайте новую книгу Excel. Импортируйте в нее данные из текстового файла Сотрудники.
Рис. 1 Текстовая база данных в окне программы Блокнот
Задача 2. Откройте приложение MS Word. Введите данные по образцу. Данные в столбцах разделяйте знаками табуляции. Документ назовите Задача1. Сохраните документ в формате «Обычный текст».
Создайте новую книгу Excel. Импортируйте в нее данные из текстового файла Задача1.
Встроенные функции
Логические
Задача 1. В ячейки А1 и В1 введены координаты точки А(-1;1). В ячейку А2 записать функцию, которая будет определять, попадает точка в фигуру (Рис. 2) или нет.
Рис. 2
Задача 2. В ячейки А4, В4, С4 введите некоторые числа. Присвойте ячейкам А4, В4, С4 имена а, b, c. Введите в ячейки А5, А6 и т.д. логические формулы, которые возвращают значение ИСТИНА тогда и только тогда, когда
a) каждое из чисел а, b, c является положительным;
b) хотя бы одно из чисел а, b, c является положительным;
c) только одно из чисел а, b, c является положительным;
d) ни одно из чисел а, b, c не является положительным;
e) хотя бы одно из чисел а, b, c не является положительным.
Финансовые
Основными финансовыми функциями Excel являются:
§ ПС(ставка; кпер; плт; бс; тип)
|
|
|
§ БС(ставка; кпер; плт; пс; тип)
§ ПЛТ(ставка; кпер; пс; бс;тип)
§ СТАВКА(кпер; плт; пс; бс; тип; предположение)
§ КПЕР(ставка; плт; пс; бс; тип)
Обязательные аргументы выделены полужирным шрифтом. Смысл аргументов этих функций следующий.
Ставка – это процентная ставка за период. Например, если получена ссуда под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12, или 0,83%.
Кпер – это общее число периодов выплат. Например, если получена ссуда на 4 года и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов.
Плт (платеж) – это выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов.
Пс (приведенная стоимость) – это текущая стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если вы помещаете деньги в банк, то эта сумма представляет начальную сумму или текущее количество денег, которое вы вложили. Если вы занимаете некоторую сумму, то эта сумма представляет приведенное или текущее значение займа. Текущее значение может быть положительным или отрицательным. Если аргумент пс опущен, то он полагается равным 0.
Бс (будущая стоимость) – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (например, бс для займа равно 0). Например, если предполагается накопить 100 000 руб. в течение 5 лет, то 100 000 руб. это и есть будущая стоимость.
Тип — это число 0 или 1, обозначающее, должна ли производиться выплата в начале периода (1) или же в конце периода (0 или отсутствует значение).
Предположение ‑ предполагаемая величина ставки. Если значение опущено, то оно полагается равным 10%. Если функция СТАВКА не сходится, попробуйте подставить различные значения для предположения. СТАВКА обычно сходится, если величина предположения находится между числами 0 и 1.
При применении функции убедитесь, что для аргументов ставка, кпер и плт используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то ставка должна быть 12%/12, а кпер должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то ставка должна быть 12%, а кпер должно быть 4.
|
|
|
Учтите также, что все аргументы, означающие деньги, которые платятся (например, депозитные вклады), представляются отрицательными числами. Деньги, которые получены (например, дивиденды), представляются положительными числами.
Функция ПС (ставка; кпер; плт; бс; тип) возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат.
Пример 1. У клиента на депозитном счету $1690,24, положенные под 1% ежемесячно. Счет открыт 12 месяцев назад. Каков начальный вклад?
=ПС(1%;12;0;1690,24;0) возвращает -$1500.
В данном случае регулярных выплат нет, поэтому аргумент плт равен нулю и тип аргумента неважен. Так как $1690,24 уже есть на счету, то аргумент бс положительный, а подсчитанное начальное значение отрицательное.
Функция БС(ставка;кпер;плт;пс;тип) используется для расчета будущего значения вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Пример 2. Клиент в течение 5 лет в начале каждого года делает вклады в банк в размере 500 руб. Годовая ставка по выбранному виду вклада равна 10%. Первоначальный взнос 1000 руб. Рассчитать будущее значение вклада.
=БС(10%; 5; -500; -1000; 1) равняется 4 968,32 р.
Пример 3. Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% или на 6 месяцев под 110%. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на шесть месяцев?
=БС(100%*(3/12);2;;-1000;1), равняется 1 562,50р.
=БС(110%*(6/12);1;;-1000;1), равняется 1 550,00р.
Функция КПЕР(ставка; плт; пс; бс; тип) возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.
Пример 4. Ссуда 10 000 руб, выданная под 12% годовых погашается ежемесячно платежами по 500 руб в начале каждого месяца. Рассчитайте срок погашения ссуды.
= КПЕР(12%/12; -500; 10000; 0; 1), равняется 22,18 (месяца).
|
|
|
Пример 5. За какой срок в годах сумма, равная 75 000 долл., достигнет 200 000 долл. при 15% -ой годовой ставке?
= КПЕР(15%; 0; -75000; 200000; 0), равняется 7, 02 (лет).
Функция СТАВКА(кпер; плт; пс;бс;тип) возвращает процентную ставку за один период при выплате ренты. Функция СТАВКА вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция СТАВКА возвращает значение ошибки #ЧИСЛО!.
Пример 6. Чтобы определить процентную ставку для четырехлетнего займа в 8000 р. с ежемесячной выплатой в 200 р. можно использовать следующую формулу:
=СТАВКА(4*12; -200; 8000) равняется 0,77 процентов
Это удельная (месячная) процентная ставка, так как период равен месяцу. Годовая процентная ставка составит 0,77%*12, что равняется 9,24 процентам.
Функция ПЛТ (ставка; кпер; пс; бс;тип) вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.
Пример 7. Следующая формула возвращает ежемесячные выплаты по займу в 10 000 руб. и годовой процентной ставке 8 %, которые можно выплачивать в течение 10 месяцев:
=ПЛТ(8%/12; 10; 10000) равняется -1037,03 р.
Если выплаты должны делаться в начале периода, то выплата для того же займа составит:
=ПЛТ(8%/12; 10; 10000; 0; 1) равняется -1030,16 р.
Пример 8. Следующая формула возвращает сумму, которую необходимо выплачивать вам каждый месяц, если вы дали взаймы 5000 руб. под 12 процентов годовых и хотите получить назад деньги за пять месяцев:
=ПЛТ(12%/12; 5; -5000) равняется 1030,20 р.
Пример 9. Функцию ПЛТ можно использовать для расчета платежей не только в случае ссуд. Например, если требуется накопить 50 000 р. за 18 лет, накапливая постоянную сумму каждый месяц, с помощью этой функции можно определить размер откладываемых сумм. Если предположить, что удастся обеспечить 6% годовых на накопления, можно использовать функцию ПЛТ, чтобы определить, сколько нужно откладывать каждый месяц.
=ПЛТ(6%/12; 18*12; 0; 50000) равняется -129,08 р.
При ежемесячной выплате 129,08 р. с 6 % накоплением в течение 18 лет вы получите 50000 р.
|
|
|
