- •Пособие для практических занятий
- •Задание 1. Построение простой электронной таблицы
- •Задание 2. Форматирование таблицы
- •Возможности автоформатирования
- •Задание 3. Использование статистических и математических функций
- •Задача 1. Рассчитать выручку от проката машин за 4 часа, 1 день, одну неделю
- •Задача 2. Определить выручку от продажи товаров на лотках и тенденцию роста доходов
- •Задача 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога.
- •Задание 5. Подбор параметра
- •Задание 6. Поиск решения. Уменьшение затрат на перевозку грузов
- •В качестве исходных данных для консолидации используем следующую таблицу (рис. 10). Данные по колонкам в и с вводятся с клавиатуры , а по колонке d считаются по формуле.
- •Задание 8. Сводные таблицы
- •Задание 9. Списки
- •Контрольные задания по теме «Табличный процессор excel»
- •Задание 2. Расчет возвратной суммы кредита
- •Задание 3. Консолидация по видам выпуска гко
- •Задание 4. Оптимизируемые модели
- •Задание 5. Расчет доходности учтенных векселей
- •Задание 6. Конвертация валюты
- •Литература
- •Сборник финансовых задач для табличного процессора Excel
- •603950, Нижний Новгород, пр. Гагарина, 23.
ФЕДЕРАЛЬНОЕ
АГЕНСТВО ПО ОБРАЗОВАНИЮ
Государственное
образовательное учреждение высшего
профессионального
образования
«Нижегородский
государственный университет
им. Н.И. Лобачевского»
Сборник
финансовых задач
для
табличного процессора EXCEL
Рекомендовано
методической комиссией финансового
факультета
для студентов всех специальностей и
форм обучения
Нижний Новгород
2006
УДК 004.912(075)
ББК 973.2я7
С-17
Самойлова
К.И. Сборник
финансовых задач для табличного
процессора EXCEL:
Пособие для практических занятий -
Нижний Новгород: Издательство
Нижегородского госуниверситета, 2006. –
36 с.
Рецензент:
Хворенков С.Г., к.э.н., доцент каф.
экономической информатики экономического
факультета ННГУ
В
сборнике финансовых задач приводится
9 заданий, которые рассматривают создание
таблиц по таким, часто используемым в
практике работы специалистов темам,
как консолидация данных, создание
сводных таблиц, подбор оптимального
решения задачи, создание и обработка
базы данных средствами Excel.
Для
проверки знаний студентов приводятся
контрольные задания по вышеперечисленным
темам, а также задание с использованием
информации из сети Internet.
Пособие рекомендуется
для студентов всех специальностей и
форм обучения финансового факультета
ННГУ им. Н.И. Лобачевского.
УДК 004.912(075)
ББК 973.2я7 ©
Нижегородский государственный
университет
им. Н.И. Лобачевского,
2006. СОДЕРЖАНИЕ
Задание 1. Построение
простой электронной таблицы 4
Задание 2.
Форматирование таблицы 6
Задание 3.
Использование статистических и
математических функций 7
Задание 4. Ввод и
обработка данных в формате
ДАТА –
ВРЕМЯ 11 Задание
5. Подбор параметра 13
Задание
6. Поиск решения 15
Задание
7. Консолидация данных 18
Задание
8. Сводные таблицы 20
Задание
9. Списки 22
Контрольные
задания по теме
«Табличный
процессор EXCEL»
Задание
1. Анализ дебиторской задолженности 26
Задание
2. Расчет возвратной суммы кредита 27
Задание
3. Консолидация по видам выпуска ГКО 28
Задание
4. Оптимизируемые модели 31
Задание
5. Расчет доходности учтенных векселей 32
Задание
6. Конвертация валют 35
Создайте
свою папку. Запустите Excel. Начиная
с клетки А1, создайте электронную
таблицу по образцу (рис.1.). Сначала
заполните строки 1,2 и колонку А. Все
заголовки и фамилии вводите с первой
позиции клетки. Колонку «Зарплата»
заполните значениями в пределах от
7000 руб. до 20000 руб.
Рис.
1.
При заполнении
клеток C3, D3 используются формулы:
C3 =B3*C$2 D3 =B3-C3
Диапазоны
клеток C3:C9
и D3:D9
заполняются путем копирования
соответствующих формул. Для этого
нужно выделить блок ячеек и вызвать
операцию Правка
– Заполнить – Вниз
или с использованием мыши путем
«протаскивания». Просмотрите
формулы для всех сотрудников. Обратите
внимание на автоматическое изменение
некоторых адресов ячеек. Адрес какой
ячейки не изменился? Почему? Обязательно
найдите ответы на эти вопросы; при
необходимости обратитесь к преподавателю. Клетка
В11 рассчитывается по формуле
=СУММ(В3:В10).
В клетки С11, D11
эта формула копируется. Сохраните
заполненную таблицу в своей папке под
именем ZP1.XLS. Поменяйте
у нескольких сотрудников заработную
плату. Для редактирования содержимого
ячеек используйте клавишу F2
или дважды щелкните мышкой на
соответствующей ячейке. Обратите
внимание на изменение вычисляемых
ячеек. Установите
налог 15%. Сравните полученные итоговые
данные с предыдущими значениями. Вставьте
новые графы «Премия» и «Всего начислено»
после графы «Зарплата». Самостоятельно
задайте формулы для их вычисления,
исходя из того, что премия составляет
40% от зарплаты, а «Всего начислено» –
это «Зарплата» + «Премия». Отредактируйте
все остальные формулы, руководствуясь
задачей. Удалите
одну строку из таблицы (сотрудник
уволен). Проверьте формулы итоговой
строки, обратите внимание на изменение
диапазонов в формулах. Дополните
таблицу еще тремя строками, включив
их между, например, 5 и 6 строками (приняты
три новых сотрудника). Заполните эти
строки. Фамилии и зарплату введите,
формулы – скопируйте. Вставьте
перед колонкой «Налог» еще две колонки
«Пенсионный фонд» и «Налогооблагаемая
база». Установите, что в пенсионный
фонд удерживается в размере 1% от
начисленной зарплаты и премии. Отчисления
в пенсионный фонд не входят в
налогооблагаемую базу, то есть
«Налогооблагаемая база» вычисляется
как «Зарплата» + «Премия» – «Пенсионный
фонд». Внесите все необходимые изменения
в формулы. Измените
алгоритм расчета подоходного налога
с учетом прогрессивной шкалы
налогообложения. Если налогооблагаемая
база меньше определенной величины
(например, 1200),то принимается ставка
12%, если больше – 20%. Формула должна
использовать функцию ЕСЛИ.
Как изменится формула, если шкала будет
задана так: до 12000 – 12%, от 12000 до 25000 –
20%, больше 25000 – 25%? Проанализируйте
полученные результаты. Вставьте
перед колонкой «Фамилия» новую графу
«Табельный номер» и заполните ее
значениями: 100,101,102 и т.д. (Правка
– Заполнить – Прогрессия). Под
строкой «Итого» вставьте две строки
для вычисления среднего и максимального
значения начисленной и выданной
зарплаты. Для этого воспользуйтесь
встроенными статистическими функциями
МАКС
и СРЗНАЧ,
которые можно вызвать при помощи
Мастера
функций (fx). Задайте
следующие имена для диапазонов ячеек
(Вставка
– Имя – Присвоить):
Зарплата – для
столбца с начисленными зарплатами;
Премия
– для столбца с премиями;
Налог – для
столбца с налогами;
Пенсионный фонд
– для столбца с отчислениями в пенсионный
фонд. Выделите
всю таблицу и выполните команду Вставка
– Имя – Применить.
Укажите Применить
все имена из списка. Проверьте изменения
в формулах. Вместо
адресов ячеек в формулах должны
появиться имена диапазонов, например
формулы в столбце Всего Начислено
должны иметь вид =Зарплата
+ Премия. Сохраните
таблицу в своей папке под именем
ZP1.XLS.
Откройте
таблицу ZP1.XLS,
созданную в предыдущей работе. Установите
с помощью мыши ширину колонок с учетом
возможных их значений и ширины
заголовков. Отцентрируйте
названия колонок и значения в строках
шапки таблицы. Для этого выделите блок
и щелкните на инструменте «центрировать». Установите
формат графы С
– целое число, а всех остальных граф
– дробные числа с двумя разрядами
после запятой. Если при этом ширина
некоторых граф окажется недостаточной
– измените ее. Вставьте
строку с заголовком таблицы «Ведомость
начисления заработной платы» самой
первой, разместите ее, начиная с колонки
А.
Проследите, что происходит при этом с
формулами. Они остались правильными? Вставьте
второй строку «за январь 2004г.» Выделите
блок, состоящий из строк 1 и 2, шириной
от графы А
до последней графы таблицы. Отцентрируйте
заголовок в пределах этого блока
(использовать инструмент, на котором
нанесена буква «а»
со стрелками слева и справа). Установите
для заголовка жирный шрифт размером
12 пунктов. Выделите
«шапку» таблицы. Установите для нее
шрифт размером 14
пунктов. При необходимости измените
ширину некоторых граф. Для
ячеек «Табельный номер», «Всего
начислено», «Пенсионный фонд» задайте
формат вывода текстов в несколько
строк (Формат
– Ячейка – вкладка Выравнивание –
Переносить по словам). Запишите
таблицу под новым именем ZP3.XLS. Отмените
сетку на экране (Сервис
– Параметры – вкладка Вид
) и сбросьте флажок у элемента “сетка”. Расчертите
таблицу горизонтальными и вертикальными
линиями. Используйте жирные и тонкие
линии. Измените
цвета символов итоговой строки и
заголовка. Измените цвета шапки и графы
«Оплатить», например, сделайте ее
светло серой. Выведите
вашу таблицу на экран в режиме
предварительного просмотра и измените
масштаб таблицы.
Сохраните
таблицу под прежним именем (ZP3.XLS)
Для изменения
внешнего вида ячеек рабочего листа
можно также использовать автоформатирование:
Формат
– Автоформат;
Команда Автоформат
позволяет существенно экономить время.
В них заранее определены наборы
параметров, содержащие форматы чисел,
шрифты, типы выравнивания, рамки, узоры,
а также ширину столбцов и высоту строк. Примените
автоформатирование к своей таблице.
Сохраните отформатированную таблицу
в своей папке под именем ZP4.XLS.
Составьте
таблицу следующего вида (рис.2.). Введите
в таблицу заголовок. Рис.
2.
Заполните
ячейку В4. Затем протащите мышь вправо
до ячейки F4
включительно (указатель мыши необходимо
навести на ячейку В4 таким образом,
чтобы он принял форму черного крестика).
Отпустите левую кнопку мыши. Клетки
автоматически заполнятся названиями
месяцев. Заполните
остальные ячейки согласно рис.2.
Выделите
ячейки B5:G5.
Выполните щелчок на кнопке Автосумма
на панели инструментов Стандартная. Скопируйте
формулу из ячейки G5
в ячейки G6:G7. Выделите
ячейки В5:В8. Выполните щелчок по кнопке
Автосумма. Скопируйте
формулу из ячейки В8 в ячейки С8:G8.
Расчет
процента
Установите
курсор на ячейку Н5. Щелкните на кнопке
Процент
на панели форматирование для задания
формата ячейки. Наберите
формулу =G5/G8
и, не нажимая клавишу Enter,
нажмите клавишу F4.
Формула примет вид =G5/$G$8,
то есть относительный адрес G8
заменится на абсолютный $G$8
(не меняющийся при копировании формулы
в другие ячейки). Нажмите
клавишу Enter.
Появится величина в процентах. Скопируйте
формулу из ячейки Н5 в ячейки Н5:Н8
Имена
ячеек Присвойте
ячейке G8
имя Всего : утановите курсор на ячейку
G8,
выполните команду Вставка
- Имя – Присвоить,
введите имя ячейки Всего, щелкните по
кнопке ОК. Очистите
ячейки Н5:Н8 для расчета процентов по
другой формуле. Введите
имя ячейки в формулу расчета процента:
установите курсор на ячейку Н5 и введите
формулу =G5/Всего,
скопируйте формулу в Н6:Н8.
Контроль
взаимосвязи ячеек при расчетах Выведите
панель инструментов Зависимости.
Для этого выполните команду Сервис
– Зависимости – Панель зависимостей. Установите
курсор на ячейку G8.
Щелкните по кнопке Влияющие
ячейки.
Щелкните по кнопке Зависимые
ячейки.
Влияющие
ячейки – это ячейки, на которые ссылается
формула в текущей ячейке. Зависимые
ячейки – это ячейки, содержащие формулы,
которые ссылаются на текущую ячейку. Удалите
все стрелки, щелкнув по кнопке Убрать
все стрелки
на панели Зависимости. Рабочему
листу, на котором находится таблица,
присвойте имя Задача 1. Следующему
листу присвойте имя Задача 2 (если в
рабочей книге только 1 лист, щелкните
правой кнопкой по ярлычку этого листа
и выполните команду Добавить
– Лист).
На
листе Задача 2 составить таблицу
следующей формы (рис. 3.). Установите
курсор на ячейку Е6. Введите формулу :
=СУММ(D4:D6).
Рис.
3.
Установите
курсор на ячейку Е9. Введите формулу,
используя Мастер
функций:
щелкните на кнопке Вставка
функции на
панели инструментов Стандартная,
в поле Категория
выбрать Математическая,
в поле Функция
щелкните по имени функции СУММ,
нажмите на кнопку ОК.
В появившемся диалоговом окне в поле
Число 1
введите диапазон суммируемых чисел
D7:D9,
щелкните на кнопке ОК. Мастер
функций можно вызвать также при
одновременном нажатии клавиш SHIFT
и F3.
Для получения пояснения по функции
щелкните по кнопке Справка
(кнопка со знаком вопроса в левом нижнем
углу диалогового окна) Самостоятельно
подсчитайте сумму в ячейке Е12
любым способом.
Функции СРЗНАЧ,
МАКС,
МИН
В
ячейку Е14 введите формулу =СРЗНАЧ(Е6;Е9;Е12). В
ячейку D15
введите формулу =МАКС(D4:D12). В
ячейку D16
введите формулу =МИН(D4:D12). Очистите
ячейки Е14, D15,
D16
и введите формулы функций через Мастер
функций.
Функция РАНГ
Функция РАНГ
определяет ранг (номер) элемента в общей
стоимости. В
ячейку С4 введите формулу
=РАНГ($D4;$D$4:$D$12),
где D4
– содержит число, для которого
определяется ранг, а D4:D12
– массив чисел, среди которого
определяется ранг. Скопировать
формулу в ячейки С5:С12.
Функция ТЕНДЕНЦИЯ Выполнить
подготовительные операции: в ячейки
В20:В22 введите соответственно значения
из ячеек Е12, Е9, Е6, в ячейки С20:С24 введите
годы: 2003 – 2007. В
ячейку В23 введите формулу
=ТЕНДЕНЦИЯ(В20:В22;С20:С22;
С23). Скопируйте
формулу из ячейки В23 в ячейку В24. Задайте
в ячейках В23:В24 формат целых чисел. Для
этого следует: выделить нужные ячейки;
выполнить команду Формат
– Ячейки;
выбрать вкладку Число;
выбрать категорию Числовой. Сохраните
таблицу. Третьему листу рабочей книги
присвойте имя Задача 3.
Составить
таблицу (на листе Задача 3) следующей
формы
(рис. 4.)
Рис. 4.
Функция ЕСЛИ Введите
в таблицу заглавие, шапку, цифровые и
текстовые данные. В
ячейку Е4 введите формулу
=ЕСЛИ(И(В4=10;С4>18);D4*0,1;0).
Формула означает, что если код города
равен 10 и возраст старше 18 лет, то сумма
налога определяется умножением дохода
на величину налога. В противном случае
сумма налога равна 0. Скопируйте формулу
из ячейки Е4 в Е5:Е6. Задание
4. Ввод и обработка данных в формате
ДАТА - ВРЕМЯ
В ячейки с А4 по
F4
введите следующие данные («шапку»
новой таблицы)
Порядко-
вый
номер Фамилия Дата
рождения Возраст Юбилей Премия
Рис. 5.
Столбец Порядковый
номер
заполните числами с1 по 14, используя
автозаполнение.
Столбец Фамилия
заполните фамилиями из пользовательского
списка (табл. 1.)
Столбец Дата
рождения
также заполните данными из следующей
таблицы.
В ячейках D5
: D18
создайте формулу для начисления
возраста студентов, используя функцию
СЕГОДНЯ
и математическую функцию ЦЕЛОЕ,
то есть в
ячейке D5
нужно создать формулу:
=ЦЕЛОЕ((СЕГОДНЯ()-С5)/365)
В ячейках столбца
Юбилей
должен содержаться либо текст Юбилей,
либо символ
«-» в зависимости от того, какое число
содержится в соседней ячейке слева.
Если число кратное 5, будем считать
возраст юбилейным. Формула имеет вид:
=ЕСЛИ(ОСТАТ(D5;5)=0;«Юбилей»;«-»)
С помощью условного
форматирования
(меню Формат) ячейки с текстом Юбилей
оформите шрифтом курсив полужирный,
синего
цвета.
Заполните ячейки
F5
: F18
в соответствии со следующим правилом:
премия начисляется только тем студентам,
у которых юбилей. Сумма премии равна
50$. Примените любой из финансовых
долларовых форматов для оформления
ячеек F5
: F18.
Используя функцию
СЧЕТЕСЛИ,
вычислите, сколько студентов-юбиляров,
студентов моложе 24 лет, студентов
старше 25 лет.
Отформатируйте
таблицу: добавьте заголовок, внешние
и внутренние границы к ячейкам, заливку.
Отсортируйте
таблицу по фамилиям.
Таблица
1.
Порядковый
номер Фамилия Дата
рождения Возраст Юбилей Премия 1 Соловьев 01.01.1980 26 - - 2 Игнатов 31.03.1981 24 - - 3 Петров 04.04.1983 22 - - 4 Новоселов 30.12.1982 23 - - 5 Шустов 07.07.1983 22 - - 6 Потапова 06.10.1980 25 Юбилей $50 7 Сидоров 13.06.1983 22 - - 8 Ситников 12.04.1982 23 - - 9 Зорина 23.06.1983 22 - - 10 Колосова 24.06.1983 22 - - 11 Амосова 25.07.1983 22 - - 12 Горин 25.08.1984 21 - - 13 Иванова 13.06.1983 22 - - 14 Волков 15.07.1982 23 - -
Количество
юбиляров 1
Количество
студентов моложе 24 лет 11
Количество
студентов старше 25 лет 1
На основе
представленных данных требуется
рассчитать рентабельность кафе. Исходные
данные (на рисунке выделены обычным
шрифтом (не полужирным)): Таблицу
стоимости продуктов в расчете на 1 кг; Раскладку
продуктов по блюдам в расчете на 1
порцию; Цену
одного обеда; Количество
людей; Количество
рабочих дней в месяце. Формулировка
задания Подготовить
лист по образцу (рис. 6).
Рис.
6.
Добавить
формулы для расчета промежуточных
данных и требуемых результатов (ячейки
с формулами выделены полужирным.). При
вводе формул считать, что: Стоимость
продуктов
в таблицах “1 блюдо”, “2 блюдо”,
“3
блюдо” можно определить как произведение
количества продукта в блюде на стоимость
этого продукта в таблице “Стоимость
продуктов”. Пример: для “1 блюда”
формула выглядит так: =G3*C3,
ее можно копировать во все ячейки
столбца данной таблицы. Итоговая
стоимость
каждого блюда определяется как сумма
чисел в соответствующем столбце. Стоимость
1-го обеда
складывается из итоговых стоимостей
1-го, 2-го и 3-го блюд. Доход
(за месяц) получается произведением
цены 1-го обеда на количество людей и
количество дней работы. Себестоимость
продуктов (за
месяц) получается произведением
стоимости 1-го обеда на количество
людей и количество дней работы. Итого
расходы
рассчитываются путем сложения
себестоимости продуктов, зарплаты
персонала, аренды помещения и прочих
расходов. Прибыль
определяется как разность между доходом
и итоговыми расходами. Рентабельность
продукции
находится как отношение прибыли к
итоговым расходам. Руководствуясь
полученными данными, решить с помощью
Мастера
подбора параметра
следующие задачи анализа рентабельности
(ответы сформировать в отдельной
таблице произвольной формы): Какова
должна быть назначена цена обеда, чтобы
рентабельность составила 20%? Какое
количество людей должно питаться в
кафе, чтобы прибыль увеличилась на
5000 рублей? Какова
должна быть цена на отдельные виды
продуктов, чтобы рентабельность
составила 15%, 20%?
ПОСТАНОВКА ЗАДАЧИ
Допустим, что ваша
фирма занимается переработкой мяса на
нескольких заводах, расположенных в
разных районах Москвы. Мясо поставляется
объединениями фермеров со складов,
расположенных в нескольких городах
Московской области. Стоимость мяса
одинаковая, однако перевозка со склада
на завод зависит от расстояния и
отличается для каждого склада и завода.
Потребность завода в мясе различна, и
запасы на каждом складе ограничены.
Требуется определить: с какого склада,
на какой завод поставлять, сколько мяса
для минимизации общих затрат на
перевозку.
ПОРЯДОК ВЫПОЛНЕНИЯ:
Создайте на листе
Транспортные
расходы
таблицу.
С этой целью:
Рис.7.
В
ячейку А1 введите текст «Оптимизация
транспортных потоков». В
ячейку В2 введите текст «Потребители->». В
ячейки C2:F2
введите названия мясоперерабатывающих
заводов. В
ячейку А3 введите текст «Поставщики». В
ячейки А4:А8 названия складов. Установите
курсор в ячейку В4 и нажмите кнопку
Автосумма, после чего выделите ячейки
с С4 по F4.
В строке формул появится формула
=СУММ(С4:F4).
Нажмите кнопку
,
расположенную слева в строке формул,
и формула будет введена. Скопируйте
содержимое ячейки В4 в ячейки В5:В8. Выделите
ячейки с С4 до F8.
Ведите цифру 1 и нажмите кнопку . Нажмите
комбинацию клавиш CTRL+D
(автозаполнение столбцов в выделенной
области), а затем нажмите CTRL+R
(автозаполнение строк в выделенной
области). Все выделенные ячейки будут
заполнены единицами. Установите формат
ячеек выделенной области Числовой. В
ячейку В9 введите текст «Факт->». В
ячейку С9 введите формулу =СУММ(С4:С8).
Скопируйте формулу в ячейки D9:F9.
Подготовка первой
части таблицы закончена. Каждое значение
в ячейках на пересечении столбца
конкретного завода и строки склада
означает количество тонн, поставляемых
в месяц с этого склада на данный завод.
В нижней строке суммируется общее
количество мяса, поставляемого на
определенный завод, во втором столбце
суммируется общее количество закупленного
у конкретного склада мяса.
Введите требуемые
объемы поставок и цены поставок. С этой
целью: Введите
в ячейку В10 текст «Запросы->». В десятой
строке вводятся значения потребляемого
каждым из заводов мяса в тоннах. В
следующие ячейки введите соответственно:
В11 300
С10 240 В12 240 D10 115 В13 170 E10 280 В14 120 F10 370 В15 320
Выделите
ячейки с А4 по А8. Нажмите клавишу CTRL
и, не отпуская ее, подведите курсор
мыши к краю выделенного интервала,
нажмите левую клавишу мыши и двигайте
мышь. Появится серый прямоугольник
размером с выделенную область.
Расположите его в ячейки с А11 по А15,
затем отпустите клавишу мыши и клавишу
CTRL.
Названия
складов будут скопированы. В
ячейки второго столбца занесите объемы
месячных запасов на различных складах
в тоннах соответственно. В
ячейки с С11 по F15
занесите стоимость перевозки тонны
мяса с конкретного склада на конкретный
завод. Для этого введите в ячейки с С11
по F15
следующие данные:
47000
41500
45000
32650
39000
32300
38000
41000
23650
27300
21000
18000
19500
19400
9000
24000
39000
36000
27500
44000
В
ячейку А16 введите текст «Всего». В
ячейку С16 введите формулу
=С4*С11+С5*С12+С6*С13+
С7*С14+С8*С15.
В ячейке С4 находится
количество мяса, перевозимого со склада
в Наро-Фоминске на завод в Лужниках, а
в ячейке С11 – цена перевозки тонны
груза по этому маршруту. Соответственно,
первое слагаемое в формуле означает
полную стоимость перевозок по данному
маршруту. Вся же формула вычисляет
полную стоимость перевозок мяса на
завод в Лужниках. Скопируйте
формулу из ячейки С16 в ячейки D16:F16. В
ячейку В16 введите формулу =СУММ(С16:
F16).
В данной ячейке будет вычисляться
общая стоимость перевозки мяса. В
ячейку А18 введите текст “Всего на
перевозки требуется”, а в ячейку Е18 –
“млн.руб.”. Для
вычисления суммы в миллионах в ячейку
D18
введите формулу =В16/1000000.
Выполните
форматирование таблицы в соответствии
с рис. 7.
Скопируйте лист
Транспортные
расходы
(для возможного восстановления начального
вида таблицы) на лист2 и переименуйте
скопированный лист, дав ему название
Поиск решения.
Выполните поиск
решения (Сервис
– Поиск решения)
с целью определения минимальных затрат
на перевозки при соблюдении следующих
условий (рис. 8). Объем
поставок с конкретного склада должен
быть меньше или равен запасам на складе. Объем
перевозок не должен быть отрицательным. Запросы
заводов должны быть выполнены полностью.
Перевыполнение поставок допустимо, а
недовыполнение – нет.
Сохраните результаты
поиска решения. Проверьте правильность
полученных результатов (рис. 9).
Рис.
8.
Рис.
9. Результат поиска решения.
Задание
7. Консолидация данных
Консолидация
– это процедура получения итоговых
данных по одной или нескольким категориям.
Для выполнения консолидации необходимо
иметь одну или несколько исходных
областей данных. Данные в этих областях
должны быть единообразно организованы.
Области данных должны представлять
собой блоки строк или столбцов с
заголовками.
Рис. 10.
Порядок выполнения
консолидации для таблицы.
Выделите
ячейку, определяющую положение итоговой
таблицы, которая будет соответствовать
левому верхнему углу
( F4
).
Выберите
меню Данные
– Консолидация.
В
окне Консолидация
в списке Функция
выберите функцию Сумма.
Установите
курсор в поле Ссылка.
Выделите первую область в исходной
таблице (A4:D8).
Нажмите
кнопку Добавить.
Повторить
пункт 4 для диапазонов A10:D14,
A16:D19.
Установите
флажок в поле В
левом столбце
и нажмите кнопку ОК.
В результате
должна получиться следующая таблица
(рис.11).
Рис. 11.
Основной недостаток
таблицы: не производится автоматический
пересчет в итоговой таблице при изменении
данных в исходной таблице.
Мастер сводных
таблиц позволяет использовать еще один
способ обобщения табличных данных.
Одна из особенностей этого мастера
заключается в том, что он удовлетворительно
работает только с однородными табличными
данными, поэтому исходную таблицу нужно
преобразовать к виду (см. табл. 12). Для
этого скопируйте исходную таблицу из
предыдущего задания на лист 2 и проведите
преобразования.
Рис. 12.
Для построения
сводной таблицы необходимо выполнить
следующие действия.
Вызовите
Мастера
сводных таблиц
(меню Данные
– Сводная
таблица).
Откроется окно – Первый
шаг. Поставьте
переключатель, указывающий источник
данных на В
списке.
Щелкнуть по кнопке Далее>.
Вторым
шагом указать диапазон, в котором
содержатся исходные данные, то есть
выделить ячейки с А1 по Е12, кн. Далее>.
Рис. 13.
Третьим
шагом нужно определить, как будет
выглядеть новая сводная таблица.
Переместите мышью кнопку Точка
в область Страница,
кнопку Наименование
в область Строка,
кнопку Цена
в область Столбец,
а кнопку Сумма
в область Данные.
Щелкнуть Далее>
(рис. 13).
Четвертым
шагом в поле Поместить
таблицу в …
поставить переключатель на строку
Существующий
лист,
щелкнуть по ярлыку листа 3 и по ячейке
А1. Щелкнуть по кнопке Готово.
Открывая кнопку,
находящуюся в клетке В1 (рис.14), можно
выводить на экран информацию как по
отдельному объекту, так и сводную по
всем объектам.
Рис. 14. Сводная
таблица
В
MS
Excel
в качестве базы данных можно использовать
список. При выполнении обычных операций
с данными, например при поиске, сортировке
или обработке данных, списки автоматически
распознаются как базы данных. Перечисленные
ниже элементы списков учитываются при
организации данных:
Столбцы списков
становятся полями базы данных.
Заголовки столбцов
становятся именами полей базы данных
Каждая строка
списка преобразуется в запись данных.
Создайте таблицу
в соответствии с заданным образцом
(табл. 2).
Таблица 2
Дата
Расход
Сумма
Получатель
01.06.03
Накладные расходы $600
ЗАО БИН
02.06.03
Накладные расходы $321
ТОО Надежда
04.06.03
Материалы $16000
АО Престиж
05.06.03
Зарплата $2000
Васильева М.Ф.
05.06.03
Зарплата $2540
Казаков С.С.
05.06.03
Зарплата $1890
Иванов И.И.
30.06.03
Накладные расходы $1000
АО ИНВЕСТ
04.07.03
Накладные расходы $600
ЗАО БИН
04.07.03
Накладные расходы $440
ТОО Надежда
04.07.03
Материалы $13200
АО Оргсинтез
05.07.03
Зарплата $2000
Васильева М.Ф.
05.07.03
Зарплата $2540
Казаков С.С.
05.07.03
Зарплата $1890
Иванов И.И.
31.07.03
Накладные расходы $1000
АО ИНВЕСТ
04.08.03
Накладные расходы $600
ЗАО БИН
05.08.03
Зарплата $2000
Васильева М.Ф.
05.08.03
Зарплата $2540
Казаков С.С.
05.08.03
Зарплата $1890
Иванов И.И.
04.09.03
Накладные расходы $311
ТОО Надежда
05.09.03
Зарплата $2000
Васильева М.Ф.
05.09.03
Зарплата $2540
Казаков С.С.
05.09.03
Зарплата $1890
Иванов И.И.
Используя
Форму данных,
добавьте
в список данные об АО Престиж: «30.06.03,
Материалы, $800, АО Престиж». Для вывода
формы на экран щелкните на любой из
ячеек заглавной строки и выберите
команду Данные
Форма
(рис.15.)
Используя
Форму данных
и кнопку Критерии,
просмотрите информацию о Казакове и
измените сумму зарплаты за 05.09.03 на
$2800.
Используя
Форму данных,
просмотрите
все данные списка о расходах на
материалы, превышающих $12000.
Рис. 15.
Отобразите
все данные списка по АО ИНВЕСТ, используя
Данные
Фильтр
Автофильтр.
Используя
Автофильтр,
отобразите все данные списка по
накладным расходам, а затем только за
июнь (рис. 16).
Рис. 16.
По
каждому расходу подведите итог по полю
Сумма, воспользовавшись командой
Данные
Итоги,
предварительно отменив Автофильтр
и отсортировав данные списка по полю
Расход.
Присвойте имя
(например, имя Список) диапазону ячеек,
содержащему все данные списка (включая
заглавную строку).
Вставьте перед
диапазоном со списком 11 пустых строк.
В ячейки A1:D1
скопируйте шапку таблицы.
Используя
Расширенный
фильтр
отобразите все данные списка по
зарплате, используя для области
критериев ячейки A1:D2. Изменив область
критериев, отобразите все данные списка
по накладным расходам.
Внесите
изменения в область критериев, добавив
в ее шапку еще одну ячейку с названием
Сумма,
так, чтобы отобразились накладные
расходы только от $500 до $900 (рис. 17).
Рис. 17.
Используя
Расширенный
фильтр,
поместите
в любую пустую область рабочего листа
все данные списка о накладных расходах
и зарплате за июль, предварительно
изменив область критериев, либо создав
новую.
Используя
Расширенный
фильтр
и новую область критериев, поместите
данные списка только по зарплате за
июнь и июль, указав дату, сумму и
получателя. Для этого предварительно
создайте шапку новой таблицы (3 ячейки).
Измените область
критериев, оставив в качестве критерия
только вид расхода – зарплату.
Для заданного
критерия отбора вычислите общую сумму:
В
ячейку В6 введите формулу расчета
суммы, используя Мастер
функции БДСУММ.
Для задания диапазона базы данных
укажите имя соответствующего диапазона
(Список), для задания поля укажите с
помощью мыши ячейку с названием поля
Сумма,
для задания области критериев также
воспользуйтесь мышью;
В ячейку В5 введите
текст «Сумма по заданному критерию».
Используя
функцию БСЧЕТ,
подсчитайте в ячейке С6 количество
выданных зарплат. Имя поля, указываемого
в окне Мастера
функций,
оставьте прежним. В ячейку С5 введите
текст «Количество».
18.Теперь
можете сходить покурить, расслабиться,
попить чайку в столовке
Задание
1. Анализ дебиторской задолженности
0.Спросить
преподавателя :”А контрольные надо
делать?”
Построить таблицы
1 и 2 по приведенным ниже формам
(рис.
18, рис. 19). Названия месяцев в таблицах
1 и 2 ввести, используя формат даты.
Разместить каждую таблицу на отдельном
листе рабочей книги, используя
одновременное выделение двух листов.
Присвоить каждому
листу короткое имя, соответствующее
названию таблицы в сокращенном виде.
В таблице 1
рассчитать значение строки «Итого».
В таблице 2
рассчитать значения граф 3 – 8. Графы
3 – 7 рассчитать по данным таблицы 1,
причем формулу расчета процента оплаты
ввести один раз, а далее скопировать
ее по строкам и столбцам.
Выполнить
обрамление всей таблицы, убрать сетку.
Рис. 18.
По данным таблицы
2 построить пять круговых диаграмм,
отражающих анализ оплаты по каждому
месяцу отгрузки с заголовком, легендой
и подписями долей.
Рис. 19.
Убрать рамки у
легенды и диаграммы.
Ввести в нижний
колонтитул индекс группы, свою фамилию,
имя, дату и имя файла.
Документ сохранить,
организовать просмотр перед печатью.
В
приложении Word
создать документ с заголовком диаграммы,
поместить в него построенную диаграмму.
Документ
сохранить и организовать просмотр
перед печатью.
Построить таблицу
по приведенной ниже форме (рис. 20).
Рис.
20. Расчет возвратной суммы кредита
Рассчитать
сумму возврата кредита (гр. 6) при
условии: если дата возврата фактическая
не превышает договорную, то сумма
возврата увеличивается на 40% от суммы
кредита (гр. 3), в противном случае сумма
возврата увеличивается на 40% плюс 1% за
каждый просроченный день. В результате
графа 6 будет иметь вид (рис. 21):
Ввести название
таблицы, а в строку между названием
таблицы и ее шапкой ввести текущее
значение даты и времени.
Выполнить
обрамление всей таблицы, убрать сетку.
Используя
Мастер
рисования
выделить тенью графу 2.
По данным граф
1, 3 и 6 таблицы построить гистограмму
с легендой, заголовком, названием осей.
Убрать рамки у
легенды и диаграммы.
Рис. 21.
Ввести в нижний
колонтитул номер группы, свою фамилию,
имя, дату и имя файла.
Документ сохранить,
организовать просмотр перед печатью.
В
приложении Word
создать документ, поместить в него
построенную диаграмму. Документ
сохранить и организовать просмотр
перед печатью.
Создать
рабочую книгу из трех листов. Присвоить
первому листу имя «Январь», второму –
«Февраль», третьему – «Итого». Выделить
все листы (удерживая CTRL,
щелкнуть мышкой по ярлычкам листов) и
ввести шапку таблицы. Снять
выделение листов и ввести названия
каждой таблицы.
№ выпуска
ГКО Эмиссия
(млрд.
руб.) Выручка
(млрд.
руб.) Погашено
(млрд.
руб.) Доходы
бюджета
(млрд.руб.) Средняя
взвешен
ная
цена 21000RMFS 979,69 662,95 433,90
21000RMFS 1998,00 1276,40 1250,70
22000RMFS 2440,89 1409,89 1296,50
23000RMFS 278,53 197,45
22000RMFS 162,50 118,23 54,64
ИТОГО:
Рис.
22.
Заполнить
данными таблицу 1 (рис. 22) на листе
«Январь» и таблицу 2 (рис. 23) – на листе
«Февраль».
№ выпуска
ГКО Эмиссия
(млрд.
руб.) Выручка
(млрд.
руб.) Погашено
(млрд.
руб.) Доходы
бюджета
(млрд.руб.) Средняя
взвешен
ная
цена 21000RMFS 1385,83 911,78 903,10
21000RMFS 50,00 36,64
22000RMFS 320,00 224,61 98,75
23000RMFS 143,07 56,71
22000RMFS 38,76 33,14 18,43
23000RMFS 66,65 52,17 32,17
21000RMFS 150,00 60,82
23000RMFS 54,53 47,18
21000RMFS 45,18 35,72 14,22
22000RMFS 73,77 30,10
ИТОГО:
Рис.
23.
Выполнить
все необходимые расчеты следующим
образом:
Графа
«Доходы бюджета» = графа «Выручка» –
графа «Погашено»; Графа
«Средняя взвешенная цена» = графа
«Выручка»/графа «Эмиссия» * 100. Рассчитать
суммы итогов за январь и февраль методом
автосуммирования. Перейти
на лист «Итого», ввести заголовок
«Итоговая таблица». Для
получения итогов по каждой ценной
бумаге выполнить консолидацию
по категориям (кроме столбца F),
в качестве имен используются значения
левого столбца (рис. 25).
Рассчитать
итоговую среднюю цену по каждому виду
ценных бумаг путем консолидации данных
и выбрав функцию Среднее (рис. 24). Рассчитать
итоги по соответствующим графам листа
«Итого» (рис. 26).
Рис.
24.
Рис.
25.
Рис. 26.
Задача определения
структуры продукции в условиях уменьшения
прибыли.
Целью данной
задачи является определение более
прибыльной структуры продукции.
Компания производит
телевизоры, стерео и акустические
системы, используя общий склад источников
питания, громкоговорителей и т.д.
Количество деталей ограничено. Нужно
определить наиболее
выгодное соотношение производимых
продуктов.
При этом необходимо учитывать, что
прибыль в расчете на единицу продукции
уменьшается при росте объема производства,
так как необходимы дополнительные
затраты для обеспечения сбыта. В задаче
учтен фактор уменьшения прибыли при
росте объема.
Создать
исходную таблицу в точном соответствии
с заданными адресами (рис. 27).
Рис.
27.
В
выделенные ячейки ввести и скопировать
следующие формулы:
в яч. С4 ввести:
=$D$2*D4+$E$2*E4+$F$2*F4
в
яч. D10
ввести: =75*МАКС(D2;0)^$H$8
в
яч. Е10 ввести: =50*МАКС(Е2;0)^$H$8
в
яч. F10
ввести: =35*МАКС(F2;0)^$H$8
в
яч. D11
ввести: =СУММ(D10:F10),
где:
- числа 75,
50, 35 –
единичная прибыль на разные виды
продукции;
- в ячейке Н8 стоит коэффициент,
учитывающий фактор уменьшения прибыли
при росте объема производства (что
делает задачу нелинейной). Для
решения задачи в меню Сервис
выбрать Поиск
решения. В
окне Поиск
решения в
поле Установить
целевую ячейку задать
$D$11.
Выберите переключатель Максимальное
значение.
В поле Изменяя
ячейки
выберите $D$2:$F$2. Далее
следует задать ограничения по
задаче.
Щелкните по кнопке Добавить
и наберите первое ограничение:
$C$4:$C$8<=$B$4:$B$8
( то есть количество используемых
деталей не должно превышать наличное). Щелкните
по кнопке Добавить
и наберите второе ограничение:
$D$2:$F$2>=0 Нажмите
кнопку Выполнить.
Программа начнет искать оптимальное
значение и выдаст окно Результаты
поиска решения.
После ознакомления с результатами
можно восстановить исходное значение,
поставив переключатель в соответствующее
окно. Скопируйте
исходную таблицу на лист 2 и лист 3. В
таблице на листе 2 снова запустите
Поиск решения
и в результирующем окне поставьте
переключатель на Сохранить
найденное значение.
В таблице на листе 3 в окне Поиск
решения
задайте найти в целевой ячейке не
максимальное значение, а, например,
12000.
Сохраните найденное значение.
Построить
две таблицы по приведенным ниже формам
(рис. 28).
В
выделенных областях второй таблицы
произвести расчеты по формулам.
Ячейки
из графы Дней
до погашения
рассчитываются по формуле:
=ДНЕЙ360(«Дата
обращения в банк»;«Срок до погашения»)
Ячейки
из графы Цена
векселя
рассчитываются по формуле:
=«Номинал
векселя»*(1-«Дней до погашения»*«Учетный
процент» /360)
Ячейки
из графы Дисконт
рассчитываются по формуле:
=«Номинал
векселя»-«Цена векселя»
Ячейки
из графы Доходность
рассчитываются по формуле:
=«Дисконт»*360/«Дней
до погашения»/«Цена векселя»
Графу
Цена векселя
пересчитать по логической формуле и
скопировать вниз по столбцу.
Задать соответствующим
графам Денежный формат, Процентный
формат, формат Даты. Согласно образцу
расчертить таблицу.
Построить
диаграмму, наиболее наглядно отражающую
полученные результаты.
Таблица
учетных ставок
Дней
до погашения Учетный
процент
До
90 дн.
36%
От
90 до 120 дн.
40%
От
120 до 150 дн.
44%
От
150 до 180 дн.
68%
Реестр
учтенных векселей
Номинал векселя
Срок до погашения
Дата обращения
в банк
Дней до погаше-ния
Цена векселя
Дисконт
Доходность в %
годовых
1 500,00р.
По
предъявлению
01.01.98
21.12.97
10
1 485,00р.
15,00р.
36,36%
2 000,00р.
По
предъявлению
10.02.98
19.10.97
111
1 753,33р.
246,67р.
45,63%
4 500,00р.
По
предъявлению
20.03.98
14.11.97
126
3 807,00р.
693,00р.
52,01%
6 000,00р.
По
предъявлению
10.04.98
19.11.97
141
4 966,00р.
1 034,00р.
53,16%
9 000,00р.
По
предъявлению
15.05.98
04.12.97
161
6 263,00р.
2 737,00р.
97,72%
Итого
18 274,33р.
4 725,67р.
Рис. 28.
Руководство фирмы
поручило вам через Центральный банк
России перевести деньги в банки
нескольких стран и конвертировать их
в иностранную валюту, которую могли бы
оперативно использовать представители
вашей фирмы. Для каждого из банков
выделена сумма в 1
000 000 рублей.
Необходимо создать отчет о проведенных
операциях, указав в нем итоговые суммы
в иностранной валюте для каждой страны.
Для упрощения
расчетов размер комиссионных принимается
одинаковым для всех валют и равным 1%.
Порядок выполнения
работы.
Войдите
в Internet
и наберите следующий адрес
Web-страницы:
http://www.rbc.ru
После
завершения поиска появится Web-страница
со сведениями, взятыми из международной
информационной системы и подготовленными
РИА «РосБизнесКонсалтинг».
Войдите
в раздел <Курсы
валют ЦБ России>,
найдите и выпишите (или скопируйте)
данные о курсах валют для своего
варианта.
Создайте
отчет в EXCEL,
заполнив графы необходимыми данными
и формулами и отформатировав надлежащим
образом таблицу.
Варианты заданий
(соответствие
вида валюты номеру строки в таблице 3.
1 вариант - строки
9,6,10
2 вариант - строки
3,4,5
3 вариант - строки
1,2,7
4 вариант - строки
8,1,2
5 вариант - строки
4,5,6
6 вариант - строки
3,7,8
7 вариант - строки
9,10,1 Таблица
3
№ строки Ведущие
валюты мира
Условные
обозначения валют
1
Австралийский
доллар AUD
2
Канадский доллар CAD
3
Французский
франк FRF
4
Немецкая марка DEM
5
Итальянская
лира ITL
6
Японская иена JPY
7
Нидерландский
гульден NLG
8
Швейцарский
франк CHF
9
Британский фунт
стерлингов GBP
10
Американский
доллар USD
По
окончании выполнения таблицы следует
проверить полученные результаты при
помощи Конвертора валют. Для этого на
вышеуказанном сайте найти раздел
Инструменты
и открыть окно Конвертор
валют.
Укажите исходные суммы для каждой
страны и размер комиссионных, взимаемых
банками. Сравнить полученные результаты
с расчетами в вашей таблице.
Информатика и
информационные технологии. Учебное
пособие / Под ред. Романовой Ю.Д. – М:
Изд-во Эксмо, 2005. – 544с.
Практикум по
экономической информатике. Учебное
пособие. Часть 1 / Под ред. Шуремова Е.Л.
– М: Изд-во «Перспектива», 2000. – 300с.
Гончаров
А. Excel
7.0 в примерах. СПб: Питер, 1996. – 256с.
Клавдия
Ивановна Самойлова
Пособие
для практических занятий
Государственное
образовательное учреждение
высшего
профессионального образования
«Нижегородский государственный
университет им. Н.И. Лобачевского».
Подписано
в печать . Формат 60х84 1/16.
Бумага
офсетная. Печать офсетная. Гарнитура
Таймс.
Усл. печ. л. 2,0. Уч.-изд.л. 2,3.
Заказ
№ Тираж 300 экз.
Отпечатано в
типографии Нижегородского университета
им.
Н.И. Лобачевского
603600, г. Нижний
Новгород, ул. Большая Покровская,
37
Лицензия ПД №18-0099 от 14.05.01
1
36Пособие для практических занятий
Задание 1. Построение простой электронной таблицы
Задание 2. Форматирование таблицы
Возможности автоформатирования
Задание 3. Использование статистических и математических функций
Задача 1. Рассчитать выручку от проката машин за 4 часа, 1 день, одну неделю
Задача 2. Определить выручку от продажи товаров на лотках и тенденцию роста доходов
Задача 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога.
Задание 5. Подбор параметра
Задание 6. Поиск решения. Уменьшение затрат на перевозку грузов
В качестве исходных данных для консолидации используем следующую таблицу (рис. 10). Данные по колонкам в и с вводятся с клавиатуры , а по колонке d считаются по формуле.
Задание 8. Сводные таблицы
Задание 9. Списки
Контрольные задания по теме «Табличный процессор excel»
Задание 2. Расчет возвратной суммы кредита
Задание 3. Консолидация по видам выпуска гко
Задание 4. Оптимизируемые модели
Задание 5. Расчет доходности учтенных векселей
Задание 6. Конвертация валюты
Литература
Сборник финансовых задач для табличного процессора Excel
603950, Нижний Новгород, пр. Гагарина, 23.
2
35
3
34
4
33
5
32
6
31
7
30
8
29
9
28
10
27
11
26
12
25
13
24
14
23
15
22
16
21
17
20
18
19