Excel для решения бизнес задач
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
Видеолекции для
профессионалов
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
5.1. Моделирование как метод познания
Моделирование — исследование каких-либо явлений, процессов или систем объектов путем построения и изучения их моделей; использование моделей для определения или уточнения характеристик и рационализации способов построения вновь конструируемых объектов. На идее моделирования базируется любой метод научного исследования — как теоретический (при котором используются различного рода знаковые, абстрактные модели), так и экспериментальный (использующий предметные модели).
В исследуемой сложной системе выделяются наиболее существенные признаки, свойства, связи, а несущественные, с точки зрения исследователя, признаки, свойства, связи не учитываются. Такой подход позволяет построить упрощенную модель сложной системы и приступить к ее изучению. Модель, в широком смысле слова, любой аналог (изображение, описание, схема, чертеж, график, план, карта и т. п.) какого-либо объекта, процесса или явления, используемый для изучения или исследования.
На рисунке 3.1. в виде представлены этапы моделирования, которые, в зависимости от возраста и рода деятельности может применять человек в процессе познания мира и практической деятельности.
Рисунок 3.1. - Этапы познания мира
Прикладное моделирование. В процессе своей повседневной деятельности люди используют различные модели. Появились целые отрасли, в которых моделирование играет ведущую роль. Художники-модельеры конструируют одежду. Дизайнеры создают проекты, предназначенные для формирования эстетических и функциональных качеств окружающего пространства или предметов. Внедрение компьютерных технологий многократно умножило созидательный потенциал творческих людей, позволяя оперативно решать задачи, на которые в недавнем прошлом уходили значительные временные ресурсы.
Научное моделирование призвано подтвердить или опровергнуть научную гипотезу. Гипотеза часто выступает как теоретическое утверждение, которое на данный момент времени еще не доказано экспериментально или имеющимися средствами не может быть доказано в принципе. В научном моделировании можно выделить два базовых направления: материальное моделирование и абстрактное моделирование. Материальное (предметное или физическое) моделирование в качестве моделей использует материальные предметы, абстрактное — математические модели, мысленный эксперимент, образное мышление.
Физическое (экспериментальное) моделирование — замена изучения некоторого объекта или явления экспериментальным исследованием его модели, имеющей ту же физическую природу.
Математическое моделирование — замена изучения некоторого объекта или явления теоретическим исследованием его модели, в основу которой положены подтвержденные практикой теоретические законы.
Модели по области использования классифицируются на:
игровые модели (стратегические, ролевые, имитационные - симуляторы, тренажеры, спортивные);
учебные модели (наглядные пособия, имитационные – тренажеры, обучающие программы);
научно-технические модели (модели объектов, явлений, связей, модели технологических процессов, имитационные - испытательные стенды).
По способу представления модели классифицируются на:
материальные модели (детские игрушки, наглядные пособия, экспериментальные лабораторные установки и модели);
информационные (абстрактные) модели (вербальные [1] , знаковые - книги, карты, схемы, рисунки, компьютерное моделирование).
В процессе моделирования на языке программирования Microsoft Excel можно выделить несколько этапов: постановка задачи; формализация; составление алгоритма; программирование; тестирование; отладка; оформление; прогнозирование.
В зависимости от сложности поставленной задачи некоторые этапы моделирования могут менять очередность, объединяться или исключаться, в силу отсутствия в них необходимости и/или очевидности решения.
Постановка задачи
1. Ознакомление с условием задачи.
2. Сбор необходимых дополнительных сведений.
3. Определение необходимости использования универсальных констант.
4. Перевод величин в единую систему измерений, например, СИ, СГС.
5. Определение математических или физических законов, описывающих явления, изображаемые в условии задачи.
3.2. Пример моделирования в среде Microsoft Excel
Построить компьютерную модель электрических нагрузок двухкомнатной квартиры. При превышении суммарных нагрузок более 5 000 Вт выдавать сигнал опасности.
Как показывает статистика возникновения пожаров, более половины пожаров возникает из-за неисправности в электрооборудовании и короткого замыкания в проводке. Плавкие предохранители, которые применяются в большинстве электрических приборов, не являются панацеей. Специалисты указывают на два существенных недостатка плавких предохранителей:
1. Инертность (замедленное срабатывание). Пока плавкий предохранитель перегорит, объект охраны уже сгорел.
2. Статистический разброс параметров. Плавкий предохранитель, промаркированный на определенный номинал, может выдержать превышение нагрузки в 15–20 раз. Если рабочая нагрузка в 3–5 раз превышает номинальную, то плавкий предохранитель не сгорит, а сгорит объект охраны.
Снизить пожарную опасность объектов можно только внедрением в систему охраны электронных предохранителей, которые отличаются повышенным быстродействием и способны отключить объект охраны при превышении нагрузки на 0,1% и менее.
Дополнительные сведения задачи — список электроприборов обычной двухкомнатной квартиры. В таблице 3.1. представлен список электрических приборов. Нумерация приборов произведена в соответствии с номером строки таблицы (2, 3, … 15).
Понятие бизнес-аналитики достаточно обширно и нередко трактуется по-разному.
С научной точки зрения бизнес-анализ — это процесс преобразования различных данных в управленческую информацию, позволяющую повысить конкурентоспособность компании на рынке и увеличить ее эффективность. Из этого определения понятно, что формирование качественной бизнес-аналитики требует не только консолидации данных из различных источников, но и высокой оперативности как поступления необходимой информации, так и ее обработки.
На рынке программных продуктов много хороших аналитических программ, которые позволяют анализировать деятельность компании. Но большинство из них получают информацию из учетных баз данных, что, конечно снижает оперативность проводимого анализа: пока первичный документ не будет отражен бухгалтерией в учетной программе, он не попадет в аналитическую обработку. Еще один недостаток аналитических программ — использование типового функционала, который невозможно изменить под потребности компании без привлечения программистов и дополнительных затрат на их работу.
Именно поэтому для экономического (т. е. постфактного) анализа работы компании широко применяются специализированные программы, а вот для целей бизнес-анализа на практике предпочитают пользоваться всем известным табличным редактором Excel. И для этого есть веские основания, которые мы и рассмотрим.
Основные функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании
Прежде всего отметим такой положительный момент в использовании Excel, как практически постоянное расширение его функционала для целей бизнес-анализа. С каждой новой версией разработчики добавляют новые функции, позволяющие консолидировать данные из различных источников и оперативно обрабатывать их.
Кроме того, пользователь может сам легко перенастроить существующие или разработать новые отчеты в Excel, не привлекая специалистов по программированию.
Сводные таблицы позволяют отбирать данные из нескольких таблиц, находящихся в разных файлах Excel, производить с ними вычисления и выводить полученные результаты в отдельную сводную таблицу. Далее с помощью полей настройки из данных одной сводной таблицы можно конструировать различные варианты отчетов для анализа.
Создать сводную таблицу может любой пользователь. Для этого в меню функций Вставка следует выбрать параметр Сводная таблица. Но для успешной работы со сводной таблицей как инструментом бизнес-анализа потребуются определенные навыки.
Работают со сводными таблицами из вкладки меню функции «Анализ» (рис. 1). На этой вкладке также настраиваются параметры сводной таблицы и источники данных (откуда берется информация).
На вкладке Конструктор пользователь может выбрать форматы и стили создаваемой сводной таблицы, а также макет отчета по ее данным.
Обратите внимание!
Преимуществом использования сводных таблиц в целях бизнес-анализа, кроме многовариантности компоновки включенных в них данных, является и возможность обновления данных сводной таблицы в случае изменения информации исходных таблиц.
Функция ВПР табличного редактора Excel помогает консолидировать данные для бизнес-анализа тем пользователям, которые недостаточно хорошо знают функционал сводных таблиц.
Это, пожалуй, одна из самых востребованных функций табличного редактора для пользователей, которые занимаются бизнес-анализом. Ее смысл в том, чтобы автоматически переносить данные из одной или нескольких разных таблиц в другую таблицу. Соответственно, когда в исходных таблицах меняются данные, то с помощью функции ВПР эти данные обновляются и в конечной таблице.
К сведению
По сравнению со сводной таблицей возможностей у функции ВПР гораздо меньше, зато, во-первых, с ней могут работать все пользователи Excel, а во-вторых, в случае необходимости перенастроить данные бизнес-отчетов, созданных на основе ВПР, получится гораздо быстрее и проще, чем если бы отчеты были созданы на основе сводных таблиц.
Для начала работы с функцией ВПР ставим курсор в выбранную ячейку конечной таблицы и с помощью мастера функций выбираем значение ВПР — откроется диалоговое окно, в котором можно выбрать параметры исходной таблицы (рис. 2).
Для наглядности приведем еще пример локального применения функции ВПР при решении задачи построения оперативного бизнес-отчета из собственной практики.
Функция ЕСЛИ, предусмотренная функционалом Excel, также популярна у бизнес-аналитиков, но она применяется чаще всего не при анализе информации, а при построении различного рода прогнозов и сценариев результатов деятельности компании. Суть функции в том, что в заданной ячейке выводится один результат при выполнении определенного условия и другой — при невыполнении этого условия.
А. А. Гребенников,
финансовый директор ООО «Ипатовский пивзавод»
Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 10, 2019.
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Решение задач оптимизации в Excel
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
- Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
- Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
- Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
- Количество изделий нам пока неизвестно. Это переменные.
- В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
- Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
- Цель – найти максимально возможную прибыль. Это ячейка С14.
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Решение финансовых задач в Excel
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
- Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
- Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
- Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
- Тип – 0.
- БС – сумма, которую мы хотим получить в конце срока вклада.
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.
Решение эконометрики в Excel
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
Решение логических задач в Excel
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
- Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
- Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
- Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».
Решение математических задач в Excel
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
- Делаем таблицу со значениями матрицы А.
- Выделяем на этом же листе область для обратной матрицы.
- Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
- В поле аргумента «Массив» вписываем диапазон матрицы А.
- Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.
Возможности табличного процессора Excel широко применяются в экономике. С помощью офисной программы можно обрабатывать и анализировать данные, составлять отчеты, бизнес-модели, прогнозировать, определять ценность клиентов и т.д.
В рамках данной статьи рассмотрим использование функции ВПР для решения экономических задач.
Описание и синтаксис функции
ВПР – функция просмотра. Формула находит нужное значение в пределах заданного диапазона. Поиск ведется в вертикальном направлении и начинается в первом столбце рабочей области.
Аргумент «Интервальный просмотр» необязательный. Если указано значение «ИСТИНА» или аргумент опущен, то функция возвращает точное или приблизительное совпадение (меньше искомого, наибольшее в диапазоне).
Для правильной работы функции значения в первом столбце нужно отсортировать по возрастанию.
ВПР в Excel и примеры по экономике
Составим формулу для подбора стоимости в зависимости от даты реализации продукта.
Изменения стоимостного показателя во времени представлены в таблице вида:
Нужно найти, сколько стоил продукт в следующие даты.
Назовем исходную таблицу с данными «Стоимость». В первую ячейку колонки «Цена» введем формулу: =ВПР(B8;Стоимость;2). Размножим на весь столбец.
Функция вертикального просмотра сопоставляет даты из первого столбца с датами таблицы «Стоимость». Для дат между 01.01.2015 и 01.04.2015 формула останавливает поиск на 01.01.2015 и возвращает значение из второго столбца той же строки. То есть 87. И так прорабатывается каждая дата.
Составим формулу для нахождения имени должника с максимальной задолженностью.
В таблице – список должников с данными о задолженности и дате окончания договора займа:
Чтобы решить задачу, применим следующую схему:
- Для нахождения максимальной задолженности используем функцию МАКС (=МАКС(B2:B10)). Аргумент – столбец с суммой долга.
- Так как функция вертикально просматривает крайний левый столбец диапазона (а суммы находятся во втором столбце), добавим в исходную таблицу столбец с нумерацией.
- Чтобы найти номер предприятия с максимальной задолженностью, применим функцию ПОИСКПОЗ (=ПОИСКПОЗ(C12;C2:C10;0)). Тип сопоставления – 0, т.к. к столбцу с долгами не применялась сортировка.
- Чтобы вывести имя должника, применим функцию: =ВПР(D12;Должники;2).
Сделаем из трех формул одну: =ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); Должники;2). Она нам выдаст тот же результат.
Функция ВПР в экономических расчетах полезна, когда нужно извлечь определенное значение из огромного диапазона данных. Причем это значение нужно найти по другому значению (цену по идентификатору продукта, налоговую ставку по уровню дохода и т.п.).
Использование Excel в экономике не ограничивается функциями просмотра. Табличный процессор предлагает пользователю возможности и побогаче.
Бизнес в Excel запись закреплена
Коллеги, приглашаю вас завтра на бесплатный онлайн мастер-класс "WOW отчет" от академии презентаций Bonnie&Slide и школы визуализации данных Excellent!
Улучшим скилы в PowerPoint и EXCEL!
Разберём:
Показать полностью.
- 10 ошибок в оформлении диаграмм и таблиц в Excel и PowerPoint;
- Как понятно и красиво визуализировать данные. Разбор в формате до/после;
- Практика в PowerPoint и Excel;
- Розыгрыш наборов для визуализации данных.
Ведущие:
- я, Ольга Базалева, основательница школы визуализации данных EXCELLENT
- Светлана Фирсова (сама Bonnie), основательница Академией убойных презентаций BONNIE&SLIDE
Віталій Корнута
Ecли вac интеpеcyют - кoмплекcнaя экcпеpтизa бизнеca в интеpеcax coбcтвенникa;
cлoжные неcтpyктypиpoвaнные пpoекты в oблacти финaнcoв, нaлoгoв и yпpaвления бизнеcoм;
coпpoвoждение инвеcтициoнныx пpoектoв – oт идеи дo пoдпиcaния юpидичеcкиx дoкyментoв;
нaлoгoвoе кoнcyльтиpoвaние, в тoм чиcле: кoнcyльтиpoвaние пo вoпpocaм иcчиcления и yплaты нaлoгoв, пpименения льгoт, пиcьменные зaключения пo cлoжным вoпpocaм нaлoгooблoжения; oценкa нaлoгoвыx pиcкoв пpедпpиятия; oценкa веpoятнocти включения пpедпpиятия в плaн выездныx нaлoгoвыx пpoвеpoк - oбpaщaйтеcь к нaм и вы пoлyчите выcoкoпpoфеccиoнaльнyю пoддеpжкy c гapaнтией пoлoжительнoгo pезyльтaтa
07.39.08
Бизнес в Excel запись закреплена
Знали ли вы, что в Excel можно наложить диаграмму на изображение?
Учись сегодня, создай что-то практичное и успешное завтра.
Опытный VBA-Разработчик, автоматизирую работу в Excel, Макросы, пользовательские функции, надстройки, разработка программных комплексов различного уровня сложности.
Пишите ТехЗадание, я оцениваю, если устраивает разрабатываю и присылаю, оплата только после проверки работы, и только если программа работает и работает так как нужно заказчику.
Бизнес в Excel запись закреплена
Изучите простой, но полезный инструмент, который обязательно должен быть на вашем вооружении при работе с комплексными документами Excel.
Читайте также: