Для решения каких задач удобен Microsoft Excel?

Задачи, решаемые в Excel, бывают разных типов: построение таблиц, построение графиков, автоматические вычисления, связь данных или комбинации разных инструментов Excel для решения поставленной задачи.

Пользователи Excel также бывают разных типов: 1) продвинутые — те, кто пользуется Excel регулярно и постоянно расширяет свои знания об этой программе; 2) среднего уровня — те, кто пользуется Excel регулярно, но решает стандартный набор задач; 3)начинающие — те, кто только учится пользоваться Excel или использует его от случая к случаю; 4) эксперты по Excel, использующие в своей работе язык программирования VBA и знающие приемы решения задач, неизвестные первым трем группам пользователей.

Пользователям начального и среднего уровня для решения повседневных рабочих задач нет смысла глубоко изучать Excel. Время, затраченное на его изучение, стоит больше, чем эффект, который они получат. Например, менеджеру для презентации проекта требуется решение такой задачи, как создание графика затрат на реализацию проекта. Для ее решения Excel подходит как нельзя лучше. Но если такой проект случается раз в год, то не имеет смысла тратить время на подробное изучение Excel. Лучше отдать такую задачу на аутсорсинг. Продвинутый пользователь или эксперт решит ее быстро, качественно и сравнительно недорого.

Или другая задача для решения в Excel — Вы хотели бы планировать семейный бюджет, но не готовы покупать для этого специальную программу, а Excel уже установлен на Вашем компьютере.

Или Вы — индивидуальный предприниматель, и хотели бы вести учет доходов и расходов предприятия, но не знаете, как сделать удобную форму в Excel для учета доходов и расходов.

Проект vExcele.ru предлагает Вам готовые решения конкретных задач из практики руководителей компаний, менеджеров, логистов, бухгалтеров. А также выкладывает готовые решения по применению Excel в ведении учета доходов и расходов, планировании семейного бюджета, расчете условий для принятия финансовых решений.

Готовые решения задач в Excel:

Решение задач для бизнеса:

Расчет точки безубыточности (Macros) График отпусков сотрудников Учет доходов и расходов ИП или предприятия (Macros) Рабочий график (график смен) Анализ денежных потоков Бланк заказа в Excel

Решение задач для офиса:

Найти сокращения (аббревиатуры) в тексте — проверка 100 страниц текста за 10 минут(Macros) Как уменьшить склад? Расчет точки заказа товара (Macros) Построить график в Excel 2003 автоматически (Macros) Калькулятор пропорций (Macros) График платежей для договора — автоматический пересчет сумм С НДС, без НДС, % от суммы предоплаты. (Macros) Построить график Excel 2007-2010 автоматически (Macros) Как включить макросы? Функция ВПР пошагово

Решение задач для семейного бюджета:

«Мой бюджет». Ведение семейного бюджета в Excel (Macros) «Мой бюджет». Ведение семейного бюджета в OpenOffice Рассчитать сумму кредита с учетом доходов семьи Сколько стоит праздничный стол?

Внимание!
Файлы, отмеченные словом (Macros), содержат макросы для автоматических вычислений.
Для работы в таких файлах при открытии включайте макросы!

Таким образом, программа Microsoft Excel походит для решения многих задач управленческого учета, ведения бюджета, визуализации данных для проектов и т.д. Вы можете ломать голову над решением какой-то задачи и не подозревать, что в Excel есть готовое решение в виде формулы или функции.

Лучшие решения по мнению автора:

Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.

Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.

Решение задач оптимизации в Excel

Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации используются следующие команды:

• Подбор параметров («Данные» — «Работа с данными» — «Анализ «что-если»» — «Подбор параметра») – находит значения, которые обеспечат нужный результат.
• Поиск решения (надстройка Microsoft Excel; «Данные» — «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
• Диспетчер сценариев («Данные» — «Работа с данными» — «Анализ «что-если»» — «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» — 250 рублей. «3» — 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.



Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

Заполнение аргументов:

1. Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
2. Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
3. Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
4. Тип – 0.
5. БС – сумма, которую мы хотим получить в конце срока вклада.

Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.

Решение эконометрики в Excel

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

Дано 2 диапазона значений:

Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).

Решение логических задач в Excel

В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =,

Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.

1. Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
2. Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
3. Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

1. Делаем таблицу со значениями матрицы А.
2. Выделяем на этом же листе область для обратной матрицы.
3. Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
4. В поле аргумента «Массив» вписываем диапазон матрицы А.
5. Нажимаем одновременно Shift+Ctrl+Enter — это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

Целевая аудитория:

Финансовые менеджеры и финансовые директора , главные бухгалтера, экономисты, сотрудники финансовых отделов и финансово-экономических служб, финансовые аналитики и все, кто работает в 1 С с финансовой информацией.

Зачем бухгалтеру и финансовому менеджеру программа Excel :

Каждому главному бухгалтеру, работающему с программой 1С приходится делать для руководителя предприятия подробные отчеты об исполнении бюджетов за минувшие периоды. Главные бухгалтера и другие финансовые специалисты предоставляют такие отчеты, составляя план-фактный анализ в программе Excel. Огромный массив информации, сравнение запланированных и фактических показателей бюджета, данные из разных источников — все это крайне сложно, если не владеть сециальными инструментами программы Excel .

Для подготовки управленческих отчетов, для финансового анализа и построения прогнозов, данные приходится собирать из разных источников. Данные об активах и обязательствах, доходах и расходах из бухгалтерских систем (SAP, Axapta, 1С и др.). Данные о курсах валют, биржевых котировках, прайс-листы конкурентов или поставщиков – в Интернете. Другая информация – из текстовых файлов, файлов Excel и баз данных. Чтобы собрать все эти данные в одном документе, необходимо выполнить тысячи копирований и вставок. Excel позволяет автоматически связать разнородные данные из разных источников.

По окончании данного курса слушатели научатся создавать формулы: логические, финансовые, статистические, а также динамические диапазоны. Использовать аналитический потенциал сводных таблиц, в том числе и создание формул в сводных таблицах. Значительное внимание уделено расчету финансовых коэффициентов и экономических показателей. Отрабатываются приемы определения безубыточного объема продаж.

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

Для финансовых директоров бухгалтеров, которые делают финансовый анализ предприятия наиболее интересным будет сквозная задача по аналитике в Excel : построение финансовой модели компании и прогнозирование, которому они могут научиться на семинаре в рамках курса «Финансовый анализ » (в зачет 40 час. очно 21 час).

Правильное построение аналитической модели – основа успешной работы финансовых работников в Excel.

Результаты обучения:

Слушатели:

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

Cмогут организовывать файлы так, чтобы их нельзя было «случайно порушить».Узнают набор малоизвестных, но полезных формул и функций Excel.

Смогут строить интерактивные сводные таблицы, иллюстрированные диаграммами и микрографиками.

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

Точно анализировать финансовую информацию и превращать ее в необходимые расчеты.

Программа обучения

Инструменты программы Excel . Работа c информацией в (3 час)

Ввод и форматирование данных. Создание пользовательских списков. Импорт данных. Создание пользовательских форматов. Условное форматирование. Формулы. Различные виды ссылок и их применение в формулах. Проверка формул. Функции. Финансовые функции. Массивы и их применение в расчетах. Работа со списками и базами данных. Создание и ведение списков. Многоуровневая сортировка.

2. Создание сводных таблиц : объединение данных из разных источников в одном файле (3 час)
Приемы импорта данных из текстовых документов или со страниц Интернет, формулы подстановки. Преобразование «обычных» таблиц Excel в базы подходящие для построения сводных таблиц Создание сводных таблиц на основе баз данных. Организация данных в Excel. Сверка разных данных, удаление дубликатов. Создание саморасширяющихся баз данных. Подведение итогов. Консолидация. Правильная подготовка исходных данных для сводных таблиц. Создание сводных таблиц; Группировка данных в сводных таблицах;

2. Выгрузка из 1С в Excel. Анализ финансовой информации в Excel (3 час)

Обработка выгрузки отчета из 1С. Удаление дубликатов.Эффективное использование формул ДВССЫЛ, ВПР, Если ошибка.Строка. Работа со Сводной таблицей. Правильное применение к полученным данным фильтра. Все возможности формулы «Если». Заполнение данными с помощью групповой обработки и ссылок на конкретную ячейку.

п.п.3 и 4 изучаются на примере решения сквозной задачи

3. Приобретение навыков работы в MS Excel для подготовки финансовой отчетности . ( 5 час)
Построение отчетов нарастающим итогом, Создание собственных формул прямо в сводной таблице, простые: план минус факт и сложные с использованием формул «если», «выбор». Интерактивные диаграммы и таблицы. Возможности построения микродиаграмм в ячейках. Приемы визуализации данных в сводной таблице. Ограничения сводных отчетов и о том, как их преодолеть. Макетирование отчетов. Вид и дизайн отчета сводной таблицы. Перекрашивание, добавление пустых строк, разлиновка строк и столбцов, удаление лишней информации. Фильтры и группировки данных. «Срезы» .

4.Финансовое моделирование и прогнозирование. (6 час)
Методы построения финансово-экономической модели компании. Функции прогнозирования в Excel. Техники выполнения сценарного анализа для построения оптимистичных и пессимистичных вариантов бюджета. Приемы подбора параметров (например, при ответе на вопрос: какой объем продаж должен быть, чтобы получить прибыль в два раза больше, чем запланировано).

Ближайшая дата обучения в группе

Стоимость обучения

Ведет семинар практикующий финансовый директор, консультант по управленческим технологиям и учетным системам, кандидат экономических наук, доцент, аккредитованный преподаватель ИПБ России с опытом проведения курсов и семинаров с 2002 года.

По окончании обучения аттестованные профессиональные бухгалтера получат сертификаты о зачете 40 часов в счет ежегодного повышения квалификации аттестованных профессиональных бухгалтеров НП ИПБ России

Каждый слушатель, кромке сертификата ИПБР , получает именной ламинированный сертификат о прохождении курса и купон на 20%-ную скидку на прохождение иностранного языка в группе.

При записи on line на обучение Вы становитесь участником розыгрыша «Приз месяца»

Статья «Первые шаги финдиректора в новой компании»

Задача 7.

Постановка задачи.

Ссуда размером 1 млн. руб. выдана под 13% годовых сроком на 3 года; проценты начисляются ежеквартально. Определить величину общих выплат по займу за второй год.

Алгоритм решения задачи.

Предположим, что ссуда погашается равными платежами в конце каждого расчетного периода. Тогда для расчета суммы выплаты задолженности за второй год применим функцию ОБЩДОХОД. Аргументы функции: Кол_пер = 3*4 = 12 кварталов (общее число расчетных периодов); Ставка = 13%/4 (процентная ставка за расчетный период – квартал); Нз = 1000000; Нач_период = 5 и Кон_период = 8 (второй год платежа по ссуде – это период с 5 по 8 квартал); Тип = 0.

= ОБЩДОХОД (13%/4; 12; 1000000; 5; 8; 0) = — 331522,23 руб.

Иллюстрация решения задачи представлена на рис. 4.20.

Рис. 4.20. Фрагмент окна с использованием функции ОБЩДОХОД

Задача 8.

Постановка задачи.

Банком выдан кредит в 500 тыс. руб. под 10% годовых сроком на 3 года. Кредит должен быть погашен равными долями, выплачиваемыми в конце каждого года. Разработать план погашения кредита, представив его в виде следующей таблицы:

Номер периода

Баланс на конец периода

Основной долг

Проценты

Накопленный долг

Накопленный процент

Алгоритм решения задачи.

Введем исходные данные задачи в ячейки электронной таблицы и определим структуру таблицы плана погашения кредита. Расчет числовых значений выполним с помощью функций Excel. Для получения возможности автозаполнения (копирования) формул, введенных для первого периода плана, на другие периоды, укажем абсолютные ссылки на исходные данные. Иллюстрация решения задачи с указанием примечаний со значениями формул вычислений для 3-го периода приведена на рис. 4.21.

Рис. 4.21. Фрагмент окна с таблицей плана погашения кредита

Приведем также формулы с непосредственным заданием значений аргументов при вычислении плановых данных для 1-го периода: