Переоценка основных средств производства

На первом листе рассчитайте суммарную балансовую стоимость по всем цехам с помощью функции СУММЕСЛИ.

Рассчитайте количество объектов, балансовая стоимость которых <= 500 млн. руб., с помощью функции СЧЕТЕСЛИ.

Постройте гистограмму для балансовой, остаточной и восстановительной (полной) стоимостей для всех объектов основного вида. Выведите на графике значения максимальной балансовой, остаточной и восстановительной стоимостей, а также легенду и название графика Переоценка основных средств производства. (На отдельном листе).

Покажите работу преподавателю.

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

Сортировка записей, фильтры, промежуточные итоги,

сводные таблицы

1. Откройте таблицу Учет(сотрудники) – папка "Справка".

2. Произведите сортировку записей по фамилиям (по алфавиту). Вернитесь к исходной таблице (отмените сортировку).

3. Создайте три копии листа Соискатель, назовите листы Автофильтр, Расширенный фильтр и Итоги.

4. На листе Автофильтр с помощью автофильтра (Главная-Редактирование-Сортировка и фильтр - Фильтр) выберите всех сотрудников, имеющих высшее. образов.

5. Среди отобранных сотрудников выберите сотрудников моложе 40 лет.

6. На листе Расширенный фильтр с помощью расширенного фильтра (Данные – Сортировка и фильтр - Дополнительно - Расширенный фильтр) выведите всех сотрудников 1 отдела, имеющих разряд выше 13-го, в таблицу с заголовками: Фамилия, Разряд, Зарплата.

7. С помощью расширенного фильтра выберите экономистов, получивших зарплату больше 12000, в таблицу Фамилия, Отдел, Разряд, Отработано часов, Зарплата.

8. На листе Итоги подведите промежуточные итоги общей суммы отработанного времени и заработной платы по отделам.

9. Создайте сводную таблицу (Вставка – группа Таблицы - Сводная таблица) для анализа общей суммы заработной платы и отработанного времени по всем отделам и разрядам.

10. Создайте сводную таблицу для анализа средней, максимальной и минимальной заработной платы по должностям.

11. Создайте сводную таблицу для анализа средней оплаты за час по отделам и образованию

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

Консолидация данных

Консолидация данных необходима для объединения данных из таблиц, находящихся в разных местах (в т. ч., на разных листах и в разных книгах). См. краткую справку по работе с программой в начале Практикума.

1. Переименуйте Лист 2 в Магазин №1, Лист 3 в Магазин №2, лист 4 в Магазин №3.

2. На листе Магазин №1 создайте таблицу по образцу:

Товар	Продано	Выручка
Компьютер	4	70000
Принтер	3	12000
Сканер	2	4000
Web-камера	5	2500
USB-флэш	10	8000

3. Для остальных магазинов создайте аналогичные таблицы, но с измененными числами (на других листах).

4. Выберите Данные – Работа с данными – Консолидация (средняя иконка в правой части группы).

5. В раскрывающемся списке Функция выберите ту функцию, которую вы хотите использовать для объединения (по умолчанию это Сумма).

6. Выберите диапазон ячеек с числами в таблице на Листе Магазин №1 и нажмите Добавить. Аналогично и для других двух таблиц.

7. Удостоверьтесь, , что в разделе Использовать в качестве имен отключены опции Подписи верхней строки и Значение левого столбца.

8. Если вы хотите связать результат объединения с источником данных (для автоматического обновления результата), включите переключатель Создавать связи с исходными данными.

9. Поставьте курсор в свободное место для вывода результата консолидации и нажмите ОК.

10. Проверьте результат консолидации.

11. Проделайте аналогичную работу для функции Среднее и Максимум, проверьте результат.

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

Задачи по работе с финансовыми функциями

1. Определите, какая сума окажется на счете, если вклад размером 900 тыс.руб. положен под 9% годовых на 19 лет, а проценты начисляются ежеквартально.

2. Какая сумма должна быть выплачена, если шесть лет назад была выдана ссуда 1500 тыс.руб. под 15% годовых с ежемесячным начислением процентов?

3. Взносы на сберегательный счет составляют 200 тыс.руб. в начале каждого года. Определите, сколько будет на счете через семь лет при ставке процента 10%?

4. Сколько лет потребуется, чтобы платежи размером 1 млн.руб. в конце каждого года достигали значения 10,897 млн.руб., если ставка процента 14,5%?

5. Предполагается, что ссуда размером 5000 тыс.руб. погашается ежемесячными платежами по 141,7 тыс.руб. Рассчитайте, через сколько лет произойдет погашение, если годовая ставка процента 16%.

6. Рассчитайте годовую ставку процента по вкладу размером 100 тыс.руб., если за 13 лет эта сумма возросла до 1 млн. руб. при ежеквартальном начислении процентов.

7. Фонд размером 21 млн.руб. был сформирован за два года за счет отчислений по 770 тыс.руб. в начале каждого месяца. Определите годовую ставку процента.

8. Заем в 980 тыс.руб. погашается равномерными периодическими платежами по 100 тыс.руб. каждые полгода в течение семи лет. Определите годовую ставку процента.

9. Какую сумму необходимо положить на депозит под 16,5% годовых, чтобы получить через три года 44 млн.руб. при полугодовом начислении процентов?

10. Определите текущую стоимость обязательных ежемесячных платежей размером 120 тыс.руб. в течение четырех лет, если годовая процентная ставка 14%.

11. Какую сумму необходимо ежемесячно вносить на счет, чтобы через три года получить 10 млн.руб., если годовая процентная ставка 18,6%?

12. Определите ежемесячные выплаты по займу в 10 млн.руб., взятому на семь месяцев под 9% годовых.

13. На сберегательный счет вносятся обязательные ежемесячные платежи по 200 тыс.руб. Рассчитайте, какая сумма окажется на счете через четыре года при ставке процента 13,5% годовых.

14. Рассчитайте, через сколько лет обязательные ежемесячные платежи размером 150 тыс.руб. принесут доход в 10 млн.руб. при ставке процента 13,5 % годовых.

15. Рассчитайте годовую ставку процента по вкладу размером 950 тыс.руб., если через пять лет размер вклада составит 5 млн.руб. Как изменится ставка процента, если срок вклада увеличить до 10 лет?

16. Рассчитайте, через сколько месяцев вклад размером 500 тыс.руб. достигнет величины 1 млн.руб. при ежемесячном начислении процентов и ставке процента 38% годовых.

17. Рассчитайте процентную ставку для трехлетнего займа размером 5 млн.руб. с ежеквартальным погашением по 800 тыс.руб.

18. Рассчитайте, какую сумму надо положить на депозит, чтобы через четыре года она выросла до 20000 тыс.руб. при норме процента 9% годовых.

19. Определите текущую стоимость обычных ежемесячных платежей размером 50 тыс.руб. в течение двух лет при ставке процента 18% годовых.

20. Рассчитайте текущую стоимость вклада, который через три года составит 15000 тыс.руб. при ставке процента 20% годовых.

21. Определите платежи по процентам по пятилетнему займу размером 16 млн.руб., выданному под 22% годовых, за двенадцатый месяц, если проценты начисляются ежемесячно.

22. Определите основные платежи по займу в 11100 тыс.руб., выданному на три года под 21% годовых, за третий год.

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

Создание базы данных «Отель»

(использование EXCEL для создания базы данных;

функции ВПР, ЕОШИБКА)

1. Переименуйте: Лист1- БД Отель;Лист2 - Вспомогательные таблицы;

Лист3 – Архив.

2. На листе «Вспомогательные таблицы» создайте следующие таблицы, учитывая соглашения:

• в графы, не выделенные серым цветом, введите исходные данные;

• диапазону ячеек A2:A5 присвойте имя Типы_номеров, диапазону ячеек E2:E5 – Вид_пансиона (Формулы - Диспетчер имен - Присвоить имя );

• выделенную серым цветом графу заполните с помощью списка (Данные - Работа с данными – Проверка данных; закладка Параметры, далее Тип данных - Список; Источник - Типы_номеров.)

После выполнения этого задания таблица на листе «Вспомогательные таблицы» будет выглядеть так, как на рисунке:

Первая цифра номера комнаты – это этаж. На 3-м и 4-м этажах размещение типов номеров аналогично второму этажу.

Для того чтобы проставить цену номеров, воспользуйтесь функцией ВПР(категория Ссылки и массивы), аргументами которой являются:

Искомое_значение – тип номера (В8),

Табл_массив – таблица, в которой ведется поиск ($A$2:$B$5),

Номер_индекса_столбца – номер столбца в таблице, где находится стоимость ( у нас - 2),

Диапазон просмотра –0.

Графа «Занятость» заполняется по формуле:

ЕСЛИ(ЕОШИБКА(ВПР(А8;’БД Отель’!$C$2:$C$35;1;0));0;1). Для ввода этой формулы вызываете функцию ЕСЛИ, затем сразу функцию ЕОШИБКА(категория Проверка свойств и значений), затем функцию ВПР (контролируете строку формул). Задаете аргументы для функции ВПР, затем щелкаете по строке формул и заканчиваете вводить формулу.

Размножьте формулу на 50 строк. В результате выполнения ячейки будут заполнены нулями.

Введите формулу для расчета Итого занято.

3. На листе «БД Отель» наберите заголовки столбцов

4. В графу «Тип номера» введите формулу, выводящую тип номера в зависимости от номера комнаты:

ЕСЛИ(С2>19;ВПР(С2;’Вспомогательные таблицы’!$A$8:$B$37;2;0);” ”).

Размножьте эту формулу на 50 строк.

5. Аналогично составьте и введите формулу для вывода цены номера в день в зависимости от типа номера. Размножьте формулу на 50 строк.

6. Вид пансиона оформите как поле со списком (ДанныеПроверка, далее Тип данных  Список, затем Источник Вид_пансиона), размножьте формулу на 50 строк.

7. Для вывода цены пансиона в день используйте формулу:

ЕСЛИ(F2<>””;ВПР(F2;‘Вспомогательные таблицы’!$E$3:$F$5;2;0);” “), размножьте формулу на 50 строк.

8. Введите формулу для расчета оплаты за день:

Если «Вид пансиона» <> “”, то «Цена номера в день» + «Цена пансиона», иначе «Цена номера в день». Размножьте формулу на 50 строк.

9. Скопируйте заголовки столбцов с листа «БД Отель» на лист «Архив».

10. В столбец I введите заголовок «Дата выезда», в столбец J введите заголовок «Количество дней», в столбец K введите заголовок «Общая стоимость».

11. Введите формулу для выдачи даты выезда ЕСЛИ(С2>19;СЕГОДНЯ();” “) (СЕГОДНЯ() – это функция), установите для столбца формат ДАТА, размножьте формулу на 50 строк.

12. Введите формулу для расчета количества дней: если номер комнаты>19, то «Дата выезда» - «Дата заезда» + 1, иначе пусто. Размножьте формулу на 50 строк.

13. Введите формулу для расчета общей стоимости:

Если номер комнаты>19, то «Оплата за номер в день» * «Количество дней», иначе пусто. Размножьте формулу на 50 строк.

14. Проверьте работу формул на листе «БД Отель». Для этого введите не менее 10 записей о клиентах с разными датами заезда и другими исходными данными.

15. Проверьте работу формул на листе «Архив». Для этого оформите выезд из отеля двух клиентов, учитывая следующие соглашения.

На лист «Архив» заносятся данные о клиентах, которые выезжают из отеля. Для того чтобы перенести данные о клиенте с листа «БД Отель» на лист «Архив», выполняются следующие действия:

• Выделяется вся запись и копируется в буфер обмена.

• На листе «Архив» активизируется ячейка в столбце А в пустой строке.

• Выполняется вставка из буфера обмена.

• На листе «БД Отель» удаляется строка, в которой была запись о выехавшем клиенте.

17. На листе «Вспомогательные таблицы» с помощью Автофильтра найдите список свободных номеров.

18. На листе «БД Отель» научитесь сортировать записи по датам и номерам комнат.

19. Создайте копию листа «БД Отель» с новым именем «Итоги». Подведите промежуточные и общие итоги среднего значения оплаты по разным типам номеров.

20. Создайте копию листа «БД Отель» с новым именем «Критерии». С помощью расширенного фильтра подготовьте списки клиентов (фамилия, вид пансиона, оплата за номер), проживающих в номерах разного типа: люксе, 1-местном, 2-местном.

21. По «БД Отель» постройте сводную таблицу для анализа спроса на разные виды пансиона клиентами, проживающими в номерах различного типа. Постройте диаграмму по созданной сводной таблице.

22. Продемонстрируйте работу базы данных.

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

Экономическое обоснование получения кредита