- •Оглавление
- •Введение
- •3.2.Элементы окна программы и окна рабочей книги Файл Element
- •1. Открыть файл Element.
- •2. Найти на экране все элементы окна, которые обозначены на рис.1.
- •3. Записать, в таблицу на рабочем листе "Элементы" их названии и выучить назначение каждого элемента, попробовать его в действии.
- •3.3. Операции по изменению структуры рабочей книги Файл Rabbook 1
- •3 А д а н и е 1. Добавить новый рабочий лист в открытую рабочую книгу "Rabbook1", дли этого:
- •3 А д я н и е 5. Вызвать на экран список всех рабочих листов, для этого:
- •3 А д а н и е 7. Переименовать лист "Лист 18", присвоив ему в качестве нового имени слово "Февраль", для этою:
- •3 А д а н и е 12. Выделить все листы рабочей книги, для этого:
- •3 А д а н и е 13. Отменить выделение всех листов:
- •3 А д а н и е 15. Выделить несколько рабочих листов не например листы Лист 1, Лист 3, Лист 5.
- •Файл Rabbook2
- •3.4. Способы перемещения активной клеточки по рабочему листу Файл Move
- •3 А д а н и е. На рабочем листе с именем "Поле" найти ячейку, в которую записано контрольное слово.
- •3.5. Правила ввода данных в ячейки таблицы Файл Vvod
- •3.6 . Правила копированиям размножения данных Файл Сору
- •3.7. Способы поиска и устранения ошибок при организации вычислений Файл Error
- •3.8. Форма тиров ание я чеек и данных, которые они содержа т Файл Ramka
- •1. Открыть файл Ramka.
- •2. Создать таблицу, образец которой приведен ниже.
- •3.9. Создание собственных списков автозаполнения Файл Spisok
- •3.10. Добавление примечаний к ячейке электронной таблицы
- •3.11. Использование рациональных способов ? копирования и размножения данных при составлении стандартных таблиц Файл Calendar
- •3 А д а и и е 1. Создать заготовку для набора карманных календарей.
- •3 А д а н и е 3. На листе "Задание" создать три календари используя шаблон, подготовленный на листе "Пример".
- •3.12. Раздельное копирование значений, формул, примечаний и форматов ячейки Файл Spets
- •1. Числа, выделенные жёлтым цветом, увеличить и 3 раза,
- •2. К числам, выделенным зелёным цветом, прибавить 43.
- •3. Числа, выделенные синим цветом, уменьшить в 5 раз.
- •3 А д а н и е 3. Открыть рабочий лист "Пример 3".
- •3.13.Относительные и абсолютные ссылки на ячейки Файл Есо
- •Стиль ссылок а1
- •3Адание1. Открыть лист "Пример", заполнить пустые ячейки в шести таблицах.
- •3Адание3. Заполнить пустые клетки колонки l.
- •3Адание4. Открыть рабочий лист "Прайс". Заполнить пустые клетки таблицы результатами вычислений.
- •Файл Kvadrat 1
- •Файл Kvadrat 2
- •3.14. Команды пункта меню данные: "текст по столбцам", "сортировка", "итоги" Файл Sort
- •3Адание.Открыть рабочий лист "Список". Убедиться, что список в колонке в неудобен для оценки деятельности работников
- •1. Подсчитать среднегодовую температуру для всех городов с точностью до шестого знака после запятой.
- •2. Расположить названия городов мира по возрастанию среднегодовых температур.
- •Файл Маpk
- •Выделить одновременно: на рабочем листе "Задание" строку № 10, столбец н, блок ячеек а16:в19, блок ячеек f13:g16 и на рабочем листе "Маркировка" блок ячеек но32001:11032008.
- •Ввести во все выделенные ячейки число 7.
- •На листе "Задание" в ячейке в12 подсчитать сумму всех введённых чисел.
- •3.15. Вычисления с спользованием циклических ссылок Файл Itog
- •3.16. Построение графиков и поверхностей Файл Diagrama
- •Открыть рабочий лист "Задание 1".
- •По данным, приведённым в таблице на рабочем листе "Пример", построить графи со следующими параметрами:
- •3.17. Построение гистограмм с накоплением Файл Term
- •3.18. Создание и применение макросов Файл Makros
- •2.3. Проверить работу макроса ЧислаНомергруппы:
- •3Адание4. Записать макрос для последовательного введения в ячейки рабочего листа цветных ячеек.
- •3Адание5. Написать макрос для введения в ячейки рабочего листа таблицы с данными.
- •5.2. Проверить работу макроса БланкНомергруппы.
- •3Адание6.
- •6.1. Записать макрос для полной очистки рабочего листа "Макрос" - всего, что появится после выполнения четырёх ваших макросов.
- •3Адание7.Создать кнопку для запуска макроса ЧислаНомергруппы.
- •3Адание8. Создать кнопки для запуска всех макросов на рабочем листе "Макросы".
- •3Адание9.
- •3Адание10.
- •3Адание11.
- •3.19. Подпрограмма мастер функций Файл Master
- •3Адание1. Открыть рабочий лист "Пример". И ячейку f3 записать функцию для определения порядкового номера дня недели, дата которого записана в ячейку f4.
- •3Адание2. В ячейке f6 определить сумму взноса в месяц (грн) для погашения взятого кредита в размере 1 млн под месячную ставку, равную 0,75%, при ежемесячных платежах в течение 12 месяцев.
- •3Адание3.1. В ячейку f11 записать, число, которое будет Медианой - числом одновременно больше одной половины чисел и меньше второй половины из блока ячеек н12:f12.
- •3Адание3.2. В ячейке f14 подсчитать число перестановок из 1000 по 3.
- •3Адание3.4. (Контрольное). Открыть рабочий лист "Слово". Сосчитать, сколько раз каждое из слов записано в блок ячеек в16:135.
- •3Адание4.1. Открыть рабочий лист "Пример". В ячейку f16 записать количество символов, которое содержится в следующей фразе:
- •3Адание4.2. Открыть рабочий лист "Сцепить". В колонке f объединить данные колонок с,d,e так, как показано в следующей таблице:
- •3Адание6.3. (Контрольное). Подсчитать суммы зарплат работников 3-го отдела.
- •3.20. Математическая функция суммпроизв Файл Sumproiz
- •1.1. Подсчитать выторг каждого продавца. Результаты поместить в строке 15.
- •1.2. Подсчитать пять сумм:
- •3.21. Команда "частичная сумма"
- •1. Сервис V Мастер V Частичная сумма.
- •3.22. Логическая функция если Файл Nalog
- •Файл Footboll
- •3.23. Условное форматирование ячеек Файл Uslov
- •3Адание 1. На листе "Задание" сделайте так, чтобы:
- •3,24. Статистическая функция "счёту Файл Probel
- •3.25. Статистическая функция "частота" Файл Chast
- •3.26. Матема тическая функция мумнож
- •Файл Matriza
- •3.27. Функциягпр(впр)изкатегории "ссылки и ма ссивы"
- •3.28. Работа с базами данных Файл Fillr
- •3.29. Сводные таблицы Файл Svod
- •Порядок построения "Сводной таблицы"
- •Последовательность действий при выполнении задания:
- •2. Выбрать Формулы → Вычисляемое поле.
- •2. Выбрать Параметры поля.
- •3.30. Команда "подборпараметра" Файл Podbor
- •3.31. Финансовые функции Файл Finans
- •3 А д а н и с 4. На рабочем листе "Пример пз" разобрать решение следующей задачи:
- •3.32. Финансовые функции для расчета амортизации Файл Amort
- •3 А д а н и е 1. На рабочем листе "Расчёт" подготовлены
- •3.33. Команда "поиск решения"
- •Файл Фарби
- •Файл Gruz
- •Файл Kuxni
- •Файл grafik
- •Файл Capital
- •Файл Trans
- •Файл Nabor
- •Часть 1
- •Часть 2
2. Выбрать Формулы → Вычисляемое поле.
3. Ввести имя поля Сумма.
4. Набрать формулу: =Продано*Цсна розн. (Сомножители в
формулу вставлять при помощи двойного щелчка по названию соответствующего поля.)
5. Добавить.
6. ОК.
Задание 2.5. Преобразовать сводную таблицу, чтобы она приобрела вид:
Для любою кола выводить наименование конфеты, даты продаж, долю (в процентах) дневного количества продаж от общего количества проданных конфет выбранною наименования.
Порядок вставки в сводную таблицу дополнительных вычислений;
1. Щёлкнуть правой кнолкой мыши внутри сводной таблицы.
2. Выбрать Параметры поля.
3. Нажать мышью кнопку "Дополнительно»".
4. В окне Дополнительные вычисления выбрать Доля от общей суммы.
5. ОК. Подсказка:
1. Щёлкнуть правой кнопкой мыши внутри сводной таблицы!
2. Выбрать Параметры поля.
3. Нажать мышью кнопку "Дополнительно»".
4. В окне Дополнительные вычисления выбрать Доля от, общей суммы.
5. ОК.
Задание 2.6. Для всех сводных таблиц построить диаграммы, которые расположить на отдельных листах. Листам присвоить имена в соответствии с номерами заданий.
3.30. Команда "подборпараметра" Файл Podbor
Команда Подбор параметра - одно из средств Excel,
которое подбирает значение в изменяемой ячейке таблицы так, чтобы число в целевой ячейке стало равным заданному. Испо- \ льзование этой функции даёт возможность быстро и правильно : решать задачи, которые обычно решаются методом "проб и . ошибок". Для иллюстрации этого утверждения в файле podbor подготовлен пример, который вначале решается методом "проб и ошибок", а затем - с использованием команды Подбор параметра.
Задание!. Открыть рабочий лист "Пример". Подобрать в ячейке СЮ такое количество месяцев, чтобы в ячейке С12 появилось число - 900,00 грн.
|
B |
C |
9 |
Сумма кредита |
100 000 грн. |
10 |
Количество месяцев платежей |
120 |
11 |
Месячная процентная ставка |
1,0000% |
12 |
Сумма ежемесячных выплат каждый месяц |
-884,75 грн. |
В ячейку C12 записана формула = ПЛАТ(C11;C10C9),
Расчёт по которой даёт значение ежемесячных выплат = -884,75 грн.
РЕШЕНИЕ МЕТОДОМ "ПРОБ И ОШИБОК"
1. Последовательно ввести в ячейку СЮ новые значения количества месяцев.
2. В ячейке С12 посмотреть соответствующую сумму выплат. Через несколько шагов подбора становится очевидно, что это требует достаточно много времени.
Задание!. Открыть лист "Подбор". Подобрать в ячейке СЮ такое количество месяцев, чтобы в ячейке С12 появилось число -900,00 грн.
РЕШЕНИЕ ПРИ ПОМОЩИ КОМАНДЫ" ПОДБОР ПАРАМЕТРА "
1. В главном меню выбрать Сервис v Подбор параметра.
2. В появившемся диалоговом окне заполнить окошки сле-
дующим образом:
установить в ячейке С12, значение -900,00.
3. Нажать ОК.
Программа найдет искомое значение 117,8422 и выведет его в ячейке СЮ, а в ячейке С12 появится значение -900,00 грн.
Безусловно, решение вторым способом предпочтительней и мы в дальнейшем будем использовать его для решения практических задач.
Задание 3. На рабочем листе "Выплаты 1" найти решение одиннадцати примеров с использованием команды Подбор параметра.
Задан и е 4. На рабочем листе "Выплаты 2" в двух таблицах найти количество платежей для выплаты кредита при ежемесячных платежах 900 грн.
Обратить внимание на разные формулы задания ветчины процентной ставки: в первом случае задана величина месячной процентной ставки, во втором — величина годовой процентной ставки.
Поскольку формула для функции ПЛТ составлена для ежемесячных платежей, то все остальные параметры должны быть приведены в соответствие с этим требованием.
Формула для первого случая - месячная процентная ставка
=ППЛАТ(С10;С9;С8).
Формула для второго случая - годовая процентная ставка =ПЛАТ(С15/12;С14;С13).
Задание 5. На рабочем листе "Задачи" решить 14 задач, условия которых приведены ниже.
Задачи с первой по одиннадцатую включительно построены на использовании функции ППЛАТ. В формулу для этой функции входят четыре параметра: сумма взятого кредита, величина процентной ставки, количество платежей и величина ежемесячных выплат для погашения кредита. В задачах всегда известны величины ежемесячных выплат и два из трёх параметров. Программе "поручается" подобрать значение третьего параметра.
Решение задач можно разбить на несколько этапов:
• заполнение расчётной таблицы данными, взятыми из условия задачи;
• ввод в одну из ячеек формулы функции ППЛАТ;
• вызов команды Подбор параметра;
• ввод в диалоговое окно адресов ячеек;
• получение результата.
Ниже приведены условия задач- Даны решения типовых задач первой, третьей, двенадцатой и тринадцатой. Решение остальных
задач предлагается найти самостоятельно.
3 а д а ч а 1. Фирма может выплачивать ежемесячно по 10 000 грн в течение трех лет. Какой кредит можно взять при месячной процентной ставке 1%?
Решение.
1. В ячейку 17 введём величину месячной процентной ставки 1%.
2. В ячейку 16 введём количество платежей 36 (три года т.е. 36месяцев).
3. В ячейку 18 введем формулу = ППЛАТ(C7;C6;С5).
4. Сделаем активной ячейку 18, откроем окно команды Подбор
параметра, первое окно уже будет заполнено адресом ячейки 18, во второе окно введём сумму ежемесячных платежей обязательно со знаком "минус", поскольку по условию задачи эта сумма - 10 000 грн - отдастся, в третье окно введём адрес ячейки - 15, куда должна быть записана искомая сумма кредита.
Ответ: 301 075,00 грн.
3 а д а ч а 2. Предприниматель желает взять кредит на 20 лет в размере 200
004) 1рн. н имеет возможность выплачивать по 6000 грн. Какая месячная процентная ставка может ею устроить? Ответ: 2,998%.
3 а д а ч а 3. Сколько месяцев предпринимателю придётся выплачивать кредит в 100 000 грн при ежемесячных платежах в размере 1200 грн, если банк установил годовую процентную ставку 6%?
Решение:
1. В ячейку 117 введём величину годовой процентной ставки 6%.
2. В ячейку 116 введём количество платежей 1 (введём произво-
льное число, так как >та ячейка всегда должка иметь ненулевое значение, поскольку деление на ноль невозможно).
3. В ячейку 118 введем формулу =ППЛАТ(117/12;I16;I15). Адрес ячейки, в которую введена величина годовой процентной ставки, подслеп на 12 для перевода ее в месячную процентную ставку.
4. Сделаем активной ячейку 118, откроем окно команды Под-
бор параметра, первое окно уже будет заполнено адресом ячейки 118, во второе окно введём сумму ежемесячных платежей обязательно со знаком минус, так как мы эту сумму -(1192,26) по условию задачи отдаем, в третье окно введём адрес ячейки 116, куда должно быть записано искомое число количества платежей. Ответ: 109 мес.
3 а д а ч я 4. Сколько платежей в год предприниматель должен будет сделать, чтобы погасить кредит в 10 000 грн за 3 года при разовой выплате 500 грн, если годовая процентная ставка 6%?
О т в е т: 22 платежа.
3 а д а ч а 5. Банк может дать кредит магазину для закупки товаров сроком на один год. Директор магазина обязуется выплатить кредит во второй половине года по 1000 грн. в месяц, если месячная процентная ставка будет равна 0,02%. Какова сумма кредита?
Ответ: 5 995,00 грн.
Задача 6. У магазина есть возможность выплачивать ежемесячно 5000 грн в течение 10 лет. Какой кредит можно взять при месячной процентной сгавке 0,1%?
Ответ: 565 132,00грн.
3 а д а ч а 7. Фирма желает взять кредит на 15 лет в размере 450 000 грн и имеет возможность выплачнвать ежемесячно по 5296,97 грн. Какая годовая процентная ставка может се устроить?
Ответ: 0,97%.
3 ад а ч а 8. Сколько месяцев необходимо платить, чтобы выплатить кредит в 10 000 грн при ежемесячных платежах в размере 399,89 грн, если банк установил годовую ставку 3%?
Ответ: 393 мсс.
3 а д а ч а 9. Сколько месяцев предприниматель будет выплачивать кредит в 50 000 грн при месячной выплате 851,25 грн, если месячная процентная ставка равна 0,07%?
Ответ: 60мес.
3 а д а ч а 10. Банк может дать кредит магазину для закупки товаров и хочет получить деньги назад через три года. Директор магазина обязуется выплатить кредит вовремя по 1000 грн в месяц, если месячная процентная ставка будет равна 0,02%.
Какая сумма кредита может быть выдана банком?
Ответ: 35 801 грн.
3 а д а ч а 11. У магазина есть возможность выплачивать 3 года ежемесячно по 1000,01 грн. Какой размер кредита можно пзя i ь при годовой процентной ставке 5%?
Ответ: 33 366,00 грн.
Задача 12. Определить будущую стоимость облигации номиналом 300 грн, выпущенной на 5 лет, учитывая следующий порядок начислении процентов: первый и второй годы по 13,5%, третий и четвёртый - по 15%, питый год 20%.
Р е ш е н и е: В любом месте рабочего листа создать расчётную таблицу, заполнить ее данными из условия задачи.
|
G |
H |
61 |
Номинал |
300 |
62 |
1-й год |
13,5% |
63 |
2-й год |
13,5% |
64 |
3-й год |
15% |
65 |
4-й год |
15% |
66 |
5-й год |
20% |
67 |
Итог |
|
В ячейку Н67 записать формулу для финансовой функции БЗРАПИС, предназначенной для таких расчетов. Лучше всего сделать это с помощью Мастера функций: вызвать диалоговое окно функции БЗРАПИС, в первое окно ввести адрес ячейки с первичным значением Н61, во второе окно "план" - адрес блока ячеек Н62:Н66, в результате в ячейке Н67 окажется формула
=БЗРАСПИС(Н61;Н62:Н66). Расчёт по ней даст результат 613,32 грн. Ответ: 613,32 грн.