Расчет коэффициента обеспеченности запасов и затрат собственными источниками формирования группы предприятий

Содержание
Введение 3
1 Вариант 19 4
2 Описание функций, необходимых для выполнения расчетов 6
2.1 Проверка данных 6
2.2 Функция СРЗНАЧ 6
2.3 Функция СУММ 8
2.4 Расширенный фильтр 8
2.5 Сортировка 9
2.6 Сводные таблицы 10
3 Выполнение задания 12
3.1 Оформление таблицы и выполнение расчетов 12
3.2 Расширенный фильтр 17
3.3 Сортировка 18
3.4 Создание сводной таблицы 19
3.5 Расчет среднего значения 20
3.6 Графический анализ расчетов 20
Заключение 22
Список использованной литературы 23

Для оформления шапки заливкой выделяем диапазон А2:G2, нажимаем кнопку «Заливка» и выбираем по вкусу цвет.3.2 Расширенный фильтрВыбрать предприятия, у которых собственные источники средств ниже максимального значения. Результат поместить в отдельную область рабочего листа.Подготовим диапазон условий отбора. Поскольку максимальное значение в процессе подготовки таблицы не вычислялось, и больше нигде, кроме сортировки, не используется, удобно задать условие отбора в виде формулы, в которой выполняется сравнение первой ячейки столбца «Собств. источники средств» с максимальным значением по тому же столбцу:=C3<МАКС($C$3:$C$8).Эту формулу вводим в ячейку I3. Ссылка на первую ячейку должна быть относительной, ссылка для вычисления максимального значения – абсолютной, кроме того, название столбца условий не должно совпадать с названием соответствующего столбца таблицы, иначе сортировка не будет работать. Поэтому в ячейку I2 вместо «Собств. источники средств» введем «Условие отбора» (Рис. 10).Рис. 10. Условие отбора.Теперь выделяем таблицу полностью, вместе с шапкой (обязательно), но без заголовка и строки «Итого», выбираем «Данные», «Дополнительно», и заполняем диалоговое окно «Расширенный фильтр» (Рис. 11).Рис. 11. Диалоговое окно «Расширенный фильтр».Результаты фильтрации представлены на рисунке ниже (Рис. 12).Рис. 12. Результаты фильтрации.3.3 СортировкаОтсортировать данные таблицы по коду головной фирмы и возрастанию коэффициента обеспеченности.Выполним сортировку на отдельном листе. Для этого щелкнем дважды ЛКМ по названию листа «Лист 2», и изменяем его имя на «Сортировка». Скопируем на этот лист нашу таблицу.Выделяем таблицу, открываем «Данные», раздел «Сортировка и фильтр», «Сортировка» и заполняем параметры сортировки: «Сортировать по» – «Код головной фирмы», «От А до Я». Нажимаем «Добавить уровень, и выбираем «Чистая прибыль», «По возрастанию» (Рис. 13).Рис. 13. Настройка сортировки.Результат представлен на Рис. 14.Рис. 14. Результат сортировки.3.4 Создание сводной таблицыСоздать сводную таблицу для расчета по каждому коду головной фирмы суммы основных средств, а также запасов и затрат.Выделяем таблицу и нажимаем «Вставка», «Сводная таблица.Выбираем размещение таблицы на отдельном листе, изменив его название на «Сводная таблица» описанным выше образом.«Названия строк» – «Код головной фирмы», «Значения» – поля «Основные средства» и «Запасы и затраты».В результате получим сводную таблицу, содержащую информацию о суммарных значениях основных средств и запасов и затрат по каждому коду фирмы (Рис. 15).Рис. 15. Сводная таблица для вычисления суммарных значений основных средств и запасов и затрат по каждому коду фирмы.3.5 Расчет среднего значенияВ ячейке А12 рассчитать средний коэффициент обеспеченности.Для выполнения этой части задания используем функцию СРЗНАЧ и формулу вида:=СРЗНАЧ(G3:G8).3.6 Графический анализ расчетовПостроить смешанный график (две оси У), отражающий собственные оборотные средства и коэффициент обеспеченности. Дать название графику и показать легенду.Выделяем столбцы, содержащие наименование предприятия, собственные оборотные средства и коэффициент обеспеченности. Затем на вкладке «Вставка» в разделе «Диаграммы» выбираем «График».Легенда отобразилась по умолчанию, выделяем ее левой клавишей мыши, затем, щелкнув правой, во всплывающем контекстном меню выбираем «Формат легенды» и настраиваем отображение легенды снизу.После этого выделяем диаграмму щелчком левой клавиши по области диаграммы, и на вкладке «Макет» в разделе «Подписи» добавляем название диаграммы – «График – две оси».В заключение левым щелчком выделяем линию. отражающую коэффициент обеспеченности, щелчком правой клавиши вызываем контекстное меню, в котором выбираем «Формат ряда данных». В разделе «Параметры ряда» выбираем «По вспомогательной оси».Поскольку коэффициент обеспеченности получен умножением значений ряда «Собственные оборотные средства» на постоянное число, в результате построения графика коэффициента по вспомогательной оси графики совместились.Результат представлен на Рис. 16.Рис. 16. Смешанный график.ЗаключениеВ процессе работы над отчетом по практике выполнен анализ задания, описаны (в теоретической части) функции СУММ, СРЗНАЧ и такие средства встроенные средства MSExcel, как проверка данных, фильтрация, сортировка, сводные таблицы и диаграммы, необходимые для выполнения расчетов по заданию.В практической части выполнен расчет коэффициента обеспеченности запасов и затрат собственными источниками формирования по группе предприятий.Список использованной литературыЮдин, М.В.; Куприянова, А.В. Microsoft Excel 2007. Компьютерная шпаргалка; СПб: Наука и техника , 2009. – 332 c.Уокенбах, Дж. Библия пользователя Excel 2010 (+CD); Диалектика – Москва, 2011. – 768 c.Серогодский, В.В. Excel 2007. Эффективный самоучитель + справочник пользователя; СПб: Наука и техника, 2008. – 400 c.Мак-Федрис, Пол. Формулы и функции в Microsoft Excel 2003; М.: Вильямс – Москва, 2006. – 576 c.Бондаренко, С.; Бондаренко, М. Excel 2010. Популярный самоучитель; СПб: Питер – Москва, 2011. – 320 c.Джелен, Билл; Александер, Майкл Сводные таблицы в Microsoft Excel; М.: Вильямс – Москва, 2007. – 320 c.Мюррей, Кэтрин Новые возможности системы Office 2010; М.: ЭКОМ – Москва, 2010. - 256 c.Хабрейкен, Джо Microsoft Office 2010. Все в одном; М.: Вильямс – Москва, 2012. - 864 c.Коцюбинский, А.О.; Грошев, С.В. Excel для бухгалтера в примерах; М.: ГроссМедиа – Москва, 2009. – 304 c.Пикуза, Владимир; Гаращенко, Алесандр Экономические и финансовые расчеты в Excel. Самоучитель; СПб: Питер – Москва, 2010. – 400 c.