Практические работы

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

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

В ячейку А1ввести название ведомости.

В ячейки A4:F4 ввести названия полей ведомости:

Формулы для расчетов:

ОС = БС - ИО

ВПС = БС * К

ВОС = ОС * К

где к — коэффициент, равный:

3,3 — если БС меньше либо равен 650 млн руб.;

4,2 — если БС больше 650 млн руб., но меньше 1000 млн руб.;

5,1 — если БС равен 1000 млн руб. или более.

Для формирования автоматических расчетов используйте следующие формулы:

для ячейки D5: =В5-С5

для ячейки Е5: =В5*ЕСЛИ(В5<=650;3,3;ЕСЛИ(И(В5>б50;В5<1000);4,2;5,1))

для ячейки F5: =D5*ЕСЛИ(В5<=650;3.3; ЕСЛИ(И(В5>650;В5<1000);4,2;5.1))

Результирующую строку итого получить использованием, например, для ячейки В12 формулы:

=СУММ(В5:В11), либо следует выделить диапазон ячеек B12:F12 и воспользоваться возможностью автосуммирования (нажать кнопку Автосумма на панели инструментов).

Отформатировать полученные в таблице результаты, а также название ведомости.

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

https://studfile.net/preview/2966961/

Практическая работа Различные формулы для расчетов

1. Сформировать и заполнить отчетную ведомость работы сети магазинов по форме:

Рекомендации по созданию ведомости

2. В ячейку А1ввести название ведомости.
3. В ячейки АЗ:НЗ ввести названия полей ведомости.
4. Основные формулы для вычислений, которые копируются для аналогичных вычислений по строкам.

Формулы для расчета

5. Отформатировать полученную ведомость.

Ведомость работы сети магазинов

https://studfile.net/preview/2966961/ это находится здесь

Отчеты в EXCEL

https://excel2.ru/articles/otchety-v-ms-excel 

history 21 января 2013 г.

Группы статей

• АНАЛИЗ ТАБЛИЦ И СВОДНЫЕ ТАБЛИЦЫ
• ИМЕНА
• РАСШИРЕННЫЙ ФИЛЬТР
• ТАБЛИЦЫ В ФОРМАТЕ EXCEL 2007

file_download Файл примера

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 ( Вставка/ Таблицы/ Таблица ), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье Сводные таблицы .

В таблице имеются столбцы:

• Товар – наименование партии товара, например, «Апельсины»;
• Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен откорректируем имя таблицы на « Исходная_таблица » (см. ФАЙЛ ПРИМЕРА ).

С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции СУММЕСЛИ() , однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений ), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра . Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

• Перейдите на лист с исходной таблицей;
• Вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
• Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место; 
• в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи .

• Скопируйте полученный список на лист, в котором будет размещен отчет;
• Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

=СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1 ) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel .

Также можно легко подсчитать количество партий каждого Товара:

=СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах. Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530). Скопируйте полученный вертикальный диапазон в Буфер обмена и транспонируйте его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B 8 введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8; Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8 , в Регионе из ячейки В7 . Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8; Исходная_Таблица[Продажи];C$7)

Ссылки, согласно правил относительной адресации , теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки B8 , в Буфер обмена , затем вставить ее в диапазон С8: G 8 , нажав CTRL + V . В ячейки ниже формулу можно скопировать Маркером заполнения .

Отчет №3 Фильтрация Товаров по прибыльности

Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.

Создадим Выпадающий (раскрывающийся) список на основе Проверки данных со следующими значениями: (Все); Да; Нет . Если будет выбрано значение фильтра (Все) , то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра « Да» , то будут учтены только прибыльные партии Товаров, если будет выбрано « Нет» , то только убыточные.

Суммарные продажи подсчитаем следующей формулой массива : =СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5="(Все)";1;(Исходная_Таблица[Прибыль]=$B$5))* Исходная_Таблица[Продажи])

После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL + SHIFT + ENTER .

Количество партий по каждой группе Товара, в зависимости от прибыльности, можно подсчитать аналогичной формулой.

=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5="(Все)";1;(Исходная_Таблица[Прибыль]=$B$5)))

Так будет выглядеть отчет о продажах по Группам Товаров, принесших прибыль.

Выбрав в фильтре значение Нет (в ячейке B 5 ), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце B .

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА() , которую нужно ввести как формулу массива :

=ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)

Для ввода формулы выделите диапазон С6:С12 , затем в Строке формул введите вышеуказанную формулу и нажмите CTRL + SHIFT + ENTER .

Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ() : =СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней]<=A7))

Отчет №5 Статистика поставок Товаров

Теперь подготовим отчет о поставках Товаров за месяц. Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы: =МИН(Исходная_Таблица[Дата поставки])

Создадим перечень дат - первых дней месяцев , начиная с самой ранней даты поставки. Для этого воспользуемся формулой: =КОНМЕСЯЦА($C$5;-1)+1

В результате получим перечень дат - первых дней месяцев:

Применив соответствующий формат ячеек, изменим отображение дат:

Формула для подсчета количества поставленных партий Товаров за месяц:

=СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* (Исходная_Таблица[Дата поставки]<="" p="" style="box-sizing: border-box;">

Теперь добавим строки для подсчета суммарного количества партий по каждому году. Для этого немного изменим таблицу, выделив в отдельный столбец год, в который осуществлялась поставка, с помощью функции ГОД() .

Теперь для вывода промежуточных итогов по годам создадим структуру через пункт меню Данные/ Структура/ Промежуточные итоги :

• Выделите любую ячейку модифицированной таблицы;
• Вызовите окно Промежуточные итоги через пункт меню Данные/ Структура/ Промежуточные итоги ;
• Заполните поля как показано на рисунке:

После нажатия ОК, таблица будет изменена следующим образом:

Будут созданы промежуточные итоги по годам. Нажатием маленьких кнопочек в левом верхнем углу листа можно управлять отображением данных в таблице.

Резюме:

Отчеты, аналогичные созданным, можно сделать, естественно, с помощью Сводных таблиц или с применением Фильтра к исходной таблице или с помощью других функций БДСУММ() , БИЗВЛЕЧЬ() , БСЧЁТ() и др. Выбор подхода зависит конкретной ситуации.

file_download Файл примера

Практическая работа «Условная функция»

Создайте электронную таблицу по образцу:

1. Сформировать на рабочем листе ведомость. В ячейке А2 название ведомости «Расчет заработной платы работников научно-проектного отдела "Альфа"

2. В ячейки АЗ:КЗ ввести названия полей ведомости.

3. По столбцам F –K вычислите значения.

1. По столбцу К (коэффициент за стаж работы) используйте условие задачи:

Коэффициент К присваивается из расчета:

0,1 — отработано до 5 лет включительно, 0,2— от 5 до 10 лет включительно, 0,25 — от 10 до 15 лет включительно, 0,3 — свыше 15 лет.

Используйте функцию ЕСЛИ().

Формула для ячейки F4:

=ЕСЛИ(Е4<=5;0,1;ЕСЛИ(И(Е4>5;Е4<=10);0,2;ЕСЛИ(И(Е4>10; Е4<=15);0,25;0,3)))

2. По столбцу Надбавка за стаж — денежный эквивалент за стаж работы.

Формула для ячейки G4: =D4*F4

По столбцу G4 используйте формулу: =тарифная ставка * К

3. По столбцу Итого— тарифная ставка с учетом стажа.

Формула для ячейки Н4: =D4+G4

По столбцу Н4 используйте формулу: = тарифная ставка + надбавка за стаж

Формат числа для ячейки – Денежный, разделитель групп разрядов

4. По столбцу Процент налога — учитывает, что:

2% — начисление (по итого) составляет до 7000 р. включительно, 10%— более 7000 р. до 10 000 р. включительно, 20%— более 10 000 р. до 25 000 р. включительно, 35%— превышающие 25 000 р.

Формула для ячейки I4:

=ЕСЛИ(Н4<=7000;0,02;ЕСЛИ(И(Н4>7000;Н4<=10000);0,1;ЕСЛИ(И(Н4>10000;Н4<=25000);0,2;0,35)))

Формат числа для ячейки I4— Процентный.

5. По столбцу Удержать — денежный эквивалент налогов.

Формула для ячейки J4: =Н4*I4  

По столбцу J4 используйте формулу: =Итого*Процент налога

Формат числа для ячейки J4 – Денежный, разделитель групп разрядов.

6. По столбцу Выплата Итого - Удержать

Формула для ячейки К4: =H4-J4

7. По строке Н19 Всего выдаче: сумма.

Формула для Н19: =СУММ(H4:H18).

Автозаполнение остальных ячеек.

Ведомость по расчету заработной платы

https://studfile.net/preview/2966961/  - полная версия практической  работы.

Практическая работа Финансовые функции. Ипотека

Финансовые функции MS Excel предназначены для вычисления базовых величин, необходимых при проведении сложных расчетов.

Задание 1. Определить величину основного платежа за четвертый год, если выдана ссуда размером 1 000000000 сроком на 5 лет под 12% годовых.

Ход работы: Для основных платежей по займу, который погашается равными платежами в конце или начале каждого расчетного периода, в MS Excel используется функция:  ОСПЛТ (Ставка, Период, Кпер, Пс, Бс)

Для данной задачи функция СППЛТ имеет вид:

ОСПЛТ(12%, 4, 5, 1000000000)

В ячейку В8 вводится формула:   = ОСПЛТ(В5;В6;В4;ВЗ)

Задание 2. Рассчитать 20-летнюю ипотечную ссуду ей ставкой 10% годовых при начальном взносе 25% и ежемесячной (ежегодной) выплате.

Ход работы: Для вычисления величины постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке используется функция ПЛТ (в ранних версиях — функция ППЛАТ):

ПЛТ(Ставка; Кпер; Бс; Пс; Тип)

Для данной задачи функция ПЛТ имеет вид:

ПЛТ(10%/12; 20*12; -(350000* (1-25%)))— ежемесячные выплаты.

Задание 3. Определить, какая сумма окажется на счете, если 52 000 руб. положены на 20 лет под 11% годовых. Проценты начисляются ежемесячно.

Ход работы: Для расчета будущей стоимости единой суммы вклада используются сложные проценты, а расчетная формула основана на функции БС (в ранних версиях — функция БЗ):   БС(Ставка; Кпер; Плт; Пс; Тип)

Для данной задачи функция БС примет вид:  БС(11%/12;20*12; ,-52000)

Введите формулу в ячейку В8.

Задание 4. Облигация номиналом 200 000 руб. выпущена на 7 лет. Предусматривается следующий порядок начисления процентов: в первый год— 11%, последующие три года— по 16%, в оставшиеся

три года— по 20%. Рассчитать будущую (наращенную) стоимость облигации по сложной процентной ставке.

Ход работы: Для расчета наращенной стоимости облигации по сложной процентной ставке используется функция: БЗРАСПИС(Первичное; План)

Для данной задачи функция принимает вид:

БЗРАСПИС(200000; {11%; 16%; 16%; 16%; 20%; 20%; 20%))

Расчет наращенной стоимости облигации по сложной процентной ставке произвести в ячейке В15:

https://studfile.net/preview/2966961/  задания взяты здесь, Орский гуманитарно-технологический институт (филиал ОГУ), Финансовые функции.

https://molotokrus.ru/annuitetnyy-platezh-sposob-rascheta/ - статья про расчет аннуитета

Практическая работа Финансовые функции. Ипотека

Финансовые функции MS Excel предназначены для вычисления базовых величин, необходимых при проведении сложных расчетов.

Задание 1. Определить величину основного платежа за четвертый год, если выдана ссуда размером 1 000000000 сроком на 5 лет под 12% годовых.

Решение

Для основных платежей по займу, который погашается равными платежами в конце или начале каждого расчетного периода, в MS Excel XP используется функция:

ОСПЛТ (Ставка, Период, Кпер, Пс, Бс)

(в более ранних версиях MSExcelэта функция называлась ОСНПЛАТ) В нашем случае функция СППЛТ имеет вид: ОСПЛТ(12%, 4, 5, 1000000000)

Ввод данных и расчеты производятся в соответствии с рис. 4.4.

Рис. 11. Расчет основных платежей по займу

В ячейку В8вводится формула:

ОСПЛТ(В5;В6;В4;ВЗ)

Задание 2. Рассчитать 20-летнюю ипотечную ссуду ей ставкой 10% годовых при начальном взносе 25% и ежемесячной (ежегодной) выплате.

Решение

Для вычисления величины постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке используется функция ПЛТ (в ранних версиях — функция ППЛАТ):

ПЛТ(Ставка; Кпер; Бс; Пс; Тип)

В нашем случае функция ПЛТ имеет вид:

ПЛТ(10%/12; 20*12; -(350000* (1-25%)))— ежемесячные выплаты;

ПЛТ(10%; 20; -(350000* (1-25%)))—ежегодные выплаты.

Решение задачи приведено на рис. 12 и 13.

Рис. 12. Расчет ипотечной ссуды

Рис. 13. Формулы для расчета ипотечной суды

Задание 3. Определить, какая сумма окажется на счете, если 52 000 руб. положены на 20 лет под 11% годовых. Проценты начисляются ежемесячно.

Решение

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

БС(Ставка; Кпер; Плт; Пс; Тип)

Для нашей задачи функция БС примет вид:

БС(11%/12;20*12; ,-52000)

Решение задачи приведено на рис. 14, а формула для ячейки В26:

=БС(B22/B23;B24*B23;;-B21)

Рис. 14. Расчет будущей стоимости вклада

Задание 4. Облигация номиналом 200 000 руб. выпущена на 7 лет. Предусматривается следующий порядок начисления процентов: в первый год— 11%, последующие три года— по 16%, в оставшиеся

три года— по 20%. Рассчитать будущую (наращенную) стоимость облигации по сложной процентной ставке.

Решение

Для расчета наращенной стоимости облигации по сложной процентной ставке используется функция:

БЗРАСПИС(Первичное; План)

Для нашей задачи функция принимает вид:

БЗРАСПИС(200000; {11%; 16%; 16%; 16%; 20%; 20%; 20%))

Решение приведено на рис. 15, а формула для расчета в ячейке В42:

=БЗРАСПИС(В30;В34:В40)

Аналогичным образом можно использовать встроенные функции MS Excel и для других финансовых расчетов.

Рис. 15 Расчет наращенной стоимости облигации по сложной процентной ставке

https://studfile.net/preview/2966961/  задания взяты здесь, Орский гуманитарно-технологический институт (филиал ОГУ), Финансовые функции.

https://molotokrus.ru/annuitetnyy-platezh-sposob-rascheta/ - статья про расчет аннуитета

Входной мониторинг Вариант 1