Abc и xyz анализ в excel: таблицы, примеры расчетов, формулы

Вспоминаем OLAP

Да, сегодня Excel по-прежнему самый популярный инструмент для работы с информацией в мире. А Сводная таблица, это то что используют миллионы пользователей каждый день. А раньше было еще больше. Если вы работали с данными в крупной компании десять лет назад вы наверняка слышали про технологию OLAP кубов от Microsoft и других вендоров, которые создаются поверх реляционных SQL баз, и позволяют получать результаты обработки миллионов строк данных за секунды. Самым популярным способом работы с OLAP кубами была и есть сводная таблица Excel. К слову OLAP по прежнему очень распространен в корпоративном мире, это все так же часть Microsoft SQL Server, однако имеет ряд ограничений по объемам и скорости обработки и все больше уступает рынок облачным аналитическим хранилищам.

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

Kогда Microsoft выводил на рынок данную технологию был опубликован открытый протокол для работы с OLAP базами — XMLA (XML для аналитики). Именно этот протокол и использует Excel когда подключается к OLAP серверу. Все работает примерно так:

3D-карты

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
Выделите диапазон данных для анализа.
На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

Студенческий т-тест в Excel

T-критерий является одним из самых основных статистических тестов, и его легко вычислить в Excel с помощью Toolpak. Нажмите на Анализ данных и прокрутите вниз, пока не увидите параметры t-теста.

У вас есть три варианта:

t-тест: два парных образца для средних следует использовать, когда ваши измерения или наблюдения были спарены. Используйте это, когда вы делали два измерения одних и тех же предметов, например, измеряли артериальное давление до и после вмешательства.
t-тест: две выборки, предполагающие равные отклонения следует использовать, когда ваши измерения независимы (что обычно означает, что они были сделаны на двух разных предметных группах). Мы обсудим часть «равных отклонений» через минуту.
t-тест: две выборки, предполагающие неравномерное отклонение также для независимых измерений, но используется, когда ваши отклонения неравны.

Чтобы проверить, равны ли отклонения двух ваших выборок, вам нужно выполнить F-тест. найти F-тест с двумя образцами для отклонений в списке инструментов для анализа выберите его и нажмите Хорошо.

Введите два набора данных в поля ввода диапазона. Оставьте альфа-значение на уровне 0,05, если у вас нет оснований для его изменения — если вы не знаете, что это значит, просто оставьте. Наконец, нажмите Хорошо.

Excel выдаст вам результаты на новом листе (если вы не выбрали Диапазон выхода и ячейка в вашем текущем листе):

Вы смотрите на P-значение здесь. Если это меньше 0,05, у вас есть неравные отклонения. Таким образом, чтобы запустить t-тест, вы должны использовать опцию неравных отклонений.

Чтобы запустить t-тест, выберите соответствующий тест в окне Инструменты анализа и выберите оба набора данных таким же образом, как вы делали для F-теста. Оставьте значение альфа на 0,05 и нажмите Хорошо.

Результаты включают в себя все, что вам нужно сообщить для t-теста: средние значения, степени свободы (df), t-статистику и P-значения для одно- и двусторонних тестов. Если значение P составляет менее 0,05, два образца значительно различаются.

Если вы не уверены, следует ли использовать одно- или двусторонний критерий Стьюдента, ознакомьтесь с этим объяснением в UCLA.

Лист «Точка безубыточности»

Задаете:

Постоянные затраты
Пременные затраты
Цену
Пределы измененения объема реализации: максимальный и минимальный
объем реализации (выпуска) продукции.

Вы сможете рассчитать точку безубыточности и увидеть на графике как меняются доход и затраты с изменением объемов. Кроме того, для каждого объема реализации вы можете расчитать и увидеть на графике:

Суммарные переменные затраты
Общие затраты
Доход
Маржинальный доход
Прибыль
Операционный рычаг
Запас финансовой прочности

Данные для расчета:

Результаты расчета:

На графиках:

Чувствуете? Ввели всего 5 чисел и получили таблицу финансовых показателей и три графика.

Вид графика можно настраивать.

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

В верхней части колонок с рассчитанными показателями есть ячейки с управляющими символами. Если в этой ячейке есть любой символ (напр., буква) — соответствующий показатель рисуется на графике. Цвет символа определяет цвет линии на графике. «Жирность» символа определяет толщину линии.

Например, на приведенном выше рисунке в колонке Постоянные затраты стоит символ «y» красного цвета, шрифт жирный (bold). На графике будет изображена красная толстая линия постоянных затрат. Линии переменных затрат на графике не будет. Ячейка B21 (обведена зеленым) задает цвет фона графика, изменяя цвет фона (заливки) ячейки можно изменять цвет фона графика.

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

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

Графики можно скопировать и вставить в отчет, доклад, дипломную работу, курсовую, реферат.

4. Практическая часть

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

4.2
Контрольный пример

4.3
Блок-схема алгоритма
решения задачи

4.4
Текст программы
на языке Pascal

4.5
Результат выполнения
программы на контрольном
примере

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

Ехсеl можно использовать как для решения
простых задач учета, так и для составления
различных бланков, деловой графики и
даже полного баланса фирмы. Однако область
применения программы не ограничивается
только сферой деловой жизни. Благодаря
мощным математическим и инженерным функциям
с помощью Ехсеl можно решить множество
задач также в области естественных и
технических наук.
В
своей курсовой работе, которая состоит
из трёх теоретических и одной практической
частей, я рассмотрела возможности и преимущества
табличного процессора Excel.
В
первой части я рассказываю, что
такое табличные процессоры, для
чего они предназначены и каковы
основные возможности Excel. Во второй
части описала программу Excel, рассматривая
интерфейс табличного процессора, и кратко
описала возможности Ехсе1 7.0 и возможность
обмена данными с другими приложениями.
А в третьем разделе рассмотрела, как применяются
электронные таблицы в финансово-экономичес ой
сфере. Здесь описала графические возможности,
статистическую обработку данных и прогнозирование,
финансово-экономич ские расчёты, а также
финансовые функции для расчётов по кредитам,
займам, оценку инвестиций и функции для
расчётов по ценным бумагам.
Заключение
работы – это выполнение практического
задания – решение задачи на программе
Turbo Pascal 7.0.

СПИСОК
ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

1.
Дубина А.Г., Орлова С.С.,
Шубина И.Ю. Excel для экономистов и менеджеров.
Экономические расчеты и оптимизационное
моделирование в среде Excel. – Питер, 2004
– 304 с.
2.
Макарова Н.В. Информатика. — СПб: Питер,
1999. — 368 с.
3.
Могилев А.В, Пак Н.И.,
Хеннер Е.К. Информатика: Учебное пособие
для студентов – М.: «Академия» 2001.
– 816 с.
4.
Семакин И., Залогова
Л., Русаков С., Шестакова
Л. Информатика: учебник по базовому
курсу. — М.: Лаборатория Базовых Знаний,
1998.- 163–176с.
5.
Угринович Н. Информатика и информационные
технологии. Учебное пособие для общеобразовательных
учреждений. — М.: БИНОМ, 2001.-311–328с.
6.
Фигурнов В.Э. «IBM PC для пользователя»
– М.:ИНФРА, 1998.-680с.
7.
Шафрин Ю.А. Информационные технологии.
— М.: Лаборатория Базовых Знаний, 1998. —
704 с.
8. http://marklv.narod.ru book/et.htm
9. http://comp-science.na od.ru/Bilet/bilet11.htm
10. http://www.metodichka.ne /?itemid=40&catid= %FA
11. http://www.metod-kopilka ru/page-2-2-11.html#st1
12. http://urist.fatal.ru/ ook/Glava6/Glava6.htm
13. http://eco.sutd.ru/Stu y/Informat/Office/Excel html
14. http://svisloch2-pns.b .ru/index1.htm
15. http://www.online-academ .ru/demo/excel/urok1/teo /teor1.htm

ПРИЛОЖЕНИЕ
1

ПРИЛОЖЕНИЕ
2

и т.д……………..

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем вышеперечисленным критериям, прост, потому что функция СЧЁТЕСЛИМН предназначена для выполнения этой работы.

Но что, если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий истинно, то есть использовать логику ИЛИ? В принципе, это можно сделать двумя способами: 1) путем добавления нескольких формул СЧЁТЕСЛИ или 2) с помощью комбинации СУММ + СЧЁТЕСЛИ с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Считаем заказы со статусом «Отменено» и «Ожидание». Для этого вы можете просто написать 2 обычные формулы СЧЁТЕСЛИ и затем сложить результаты:

Если вам нужно оценить более одного параметра выбора, используйте COUNTPIIF.

Чтобы узнать количество «отмененных» и «ожидающих» заказов на клубнику, используйте эту опцию:

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Введите СЧЁТЕСЛИМН в функцию СУММ, например:

СУММ (СЧЁТЕСЛИМН (диапазон; {«условие1»; «условие2»; «условие3»;…}))

В нашей таблице с примерами подсчета заказов со статусом «Отменено» или «В ожидании» расчет будет выглядеть так:

= СУММ (СЧЁТЕСЛИМН (E2: E11; {«Отменено», «В ожидании»}))

Массив означает, что мы сначала ищем все отмененные ордера, затем отложенные ордера. В результате получается двузначная матрица итогов. А затем функция СУММ складывает их.

Точно так же вы можете использовать две или более пары диапазон / условие. Чтобы рассчитать количество отмененных или отложенных заказов на клубнику, используйте это выражение:

Загрузка пакета анализа в Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

Для удобства также приводим ссылку на оригинал (на английском языке)

Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

Если вы используете Excel 2007, нажмите кнопку Microsoft Office , а затем – кнопку Параметры Excel.

В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.

В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.

Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку » Пакет анализа — VBA » таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа — VBA .

Примечание: Пакет анализа недоступен для Excel для Mac 2011. Дополнительные сведения о том, как найти пакет анализа в Excel для Mac 2011, я не вижу.

Чтобы загрузить пакет анализа в Excel для Mac, выполните указанные ниже действия.

В меню Сервис выберите пункт надстройки Excel.

В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Если надстройка Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы найти ее.

Если появляется сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да , чтобы установить его.

Выйдите из приложения Excel и перезапустите его.

Теперь на вкладке Данные доступна команда Анализ данных.

Я не могу найти пакет анализа в Excel для Mac 2011

Существуют несколько сторонних надстроек, которые предоставляют функции пакета анализа для Excel 2011.

Вариант 1. Скачайте статистическое программное обеспечение надстройки КСЛСТАТ для Mac и используйте его в Excel 2011. КСЛСТАТ содержит более 200 основных и расширенных статистических средств, включающих все функции пакета анализа.

Выберите версию КСЛСТАТ, соответствующую операционной системе Mac OS, и загрузите ее.

Откройте файл Excel, содержащий данные, и щелкните значок КСЛСТАТ, чтобы открыть панель инструментов КСЛСТАТ.

В течение 30 дней вы получите доступ ко всем функциям КСЛСТАТ. По истечении 30 дней вы сможете использовать бесплатную версию, включающую функции пакета анализа, или заказать одно из более полных решений КСЛСТАТ.

Вариант 2. Скачайте Статплус: Mac LE бесплатно из Аналистсофт, а затем используйте Статплус: Mac LE с Excel 2011.

Вы можете использовать Статплус: Mac LE для выполнения многих функций, которые ранее были доступны в пакетах анализа, таких как регрессия, гистограммы, анализ вариации (Двухфакторный дисперсионный обработки) и t-тесты.

Перейдите на веб-сайт аналистсофти следуйте инструкциям на странице загрузки.

После загрузки и установки Статплус: Mac LE откройте книгу, содержащую данные, которые нужно проанализировать.

Откройте Статплус: Mac LE. Эти функции находятся в меню Статплус: Mac LE.

В Excel 2011 не входит Справка для Кслстат или Статплус: Mac LE. Справка по Кслстат предоставляется кслстат. Справка для Статплус: Mac LE предоставляется Аналистсофт.

Корпорация Майкрософт не предоставляет поддержку ни для каких продуктов.

Инструменты анализа Excel

Один из самых убедительных анализов данных — «что, если». Находится: «Данные» — «Работа с данными» — «А если».

Инструменты анализа «что, если»:

«Подбор параметров». Он используется, когда пользователю известен результат формулы, но неизвестен ввод этого результата.
«Таблица данных». Он используется в ситуациях, когда вы хотите отобразить влияние значений переменных в формулах в таблице.
Менеджер сценариев. Используется для формирования, изменения и сохранения различных наборов входных данных и результатов расчетов по группе формул.
«Найти решение.» Это надстройка для Excel. Помогает найти лучшее решение конкретной проблемы.

Практический пример использования «Что, если» для поиска лучших скидок в таблице данных.

Другие инструменты для анализа данных:

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

Анализ

Как только вы добавите (неважно как) сводную таблицу, вы увидите новую вкладку «Анализ» на панели инструментов. Он содержит множество различных инструментов и функций

Рассмотрим каждый из них подробнее.

Сводная таблица

Нажав кнопку, отмеченную на скриншоте, вы можете сделать следующее:

переименовать;

откройте окно настроек.

В окне настроек вы увидите много интересного.

Активное поле

С помощью этого инструмента вы можете сделать следующее:

Сначала нужно выделить ячейку. Затем нажмите кнопку «Активное поле». В появившемся меню нажмите на пункт «Параметры поля».

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

Кроме того, вы можете настроить числовой формат. Для этого нажмите на соответствующую кнопку.

Откроется окно «Формат ячеек».

Здесь вы можете указать, в какой форме вы хотите отобразить результат анализа информации.

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. В этом случае ничего сложного делать не нужно.

Выберите столбец. Затем нажмите кнопку «Вставить фрагмент».
В появившемся окне в качестве примера выберите одно из предложенных полей (в дальнейшем их можно будет выбирать в неограниченном количестве). После выбора чего-либо сразу активируется кнопка «ОК». Нажмите здесь.

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

Вы можете нажать на любой из пунктов. Сразу после этого изменятся все значения в поле суммы.

Таким образом, можно будет выбрать любой период времени.

В любой момент все может вернуться в первоначальный вид. Для этого нажмите на иконку в правом верхнем углу этого окна.

В данном случае мы смогли отсортировать отчет по месяцам, потому что у нас было соответствующее поле. Но есть более мощный инструмент для работы с датами.

Вставить временную шкалу

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

В качестве примера создадим небольшую таблицу с несколькими датами.

Затем вам нужно будет построить сводную таблицу.

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

Затем нас просят выбрать диапазон значений.

Для этого просто выделите всю таблицу.

Сразу после этого адрес будет автоматически заменен. Здесь все очень просто, потому что рассчитано на дураков. Нажмите кнопку OK, чтобы завершить сборку».

Редактор Excel предложит нам только один вариант, так как таблица очень простая (для примера больше и не нужно).

Попробуйте еще раз щелкнуть значок «Вставить временную шкалу» (на вкладке «Анализ»).

На этот раз ошибок не будет. Вам будет предложено выбрать поле для сортировки. Установите флажок и нажмите кнопку ОК».

Благодаря этому появится окно, в котором можно выбрать нужную дату с помощью ползунка.

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

Обновить

Если вы внесли какие-либо изменения в исходные данные и по какой-то причине они не отображаются в сводной таблице, вы всегда можете обновить их вручную. Для этого достаточно нажать соответствующую кнопку на панели инструментов.

Источник данных

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

Для этого нажмите на значок «Источник данных». Затем выберите одноименный пункт меню.

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

С помощью этого инструмента вы можете:

почистить стол;
выделять;
подвинь это.

Вычисления

Если расчеты в таблице недостаточны или не соответствуют вашим потребностям, вы всегда можете внести свои изменения. Нажав на иконку этого инструмента, вы увидите следующие опции.

это включает:

вычисляемое поле;

вычисляемый объект;

порядок расчета (добавленные формулы отображаются в списке);

показать формулы (без информации, так как нет добавленных формул).

Показать

С помощью этого инструмента вы можете настроить внешний вид рабочей области редактора.

Благодаря этому вы сможете:

настроить отображение боковой панели со списком полей;

включить или отключить кнопки плюс/минус;

настроить отображение заголовков полей.

Какие вопросы и задачи по Excel популярны на собеседованиях?

Педагоги наших курсов отличаются тем, что не «распыляются» на работу в школах и ВУЗах, где учебная программа далека от практики, а имеют многолетний стаж корпоративного обучения Excel именно организаций. Это даёт ценную информацию о том, какие функции программы чаще всего применяются фирмами, и соответственно на знание чего они будут делать акцент в ходе собеседования. Приводим ТОП 10 таких вопросов на знание Excel в порядке убывания популярности.

ВПР – функция позволяет решить задачи сравнения данных из разных таблиц и подгрузки информации из нескольких таблиц в одну.
Сводные таблицы – используются для отображения данных в более понятном виде, для выборки данных по критериям и для получения итогов.
СЦЕП, СЦЕПИТЬ – применяют для объединения данных из разных ячеек в одну. Например, выгрузка из 1С: Бухгалтерии размещает данные в одной ячейке и возникает задача разбить информацию по столбцам, а затем соединить то, что нужно.
СУММЕСЛИ, СУММЕСЛИМН – эти функции суммируют не всё данные, а то, что удовлетворяет критерию.
СЧЁТЕСЛИ, СЧЁТЕСЛИМН – позволяют подсчитать количество ячеек, соответствующих заданным критериям формулы.
Консолидация – инструмент позволяет собрать разрозненные однотипные данные в одну таблицу.
ЕСЛИОШИБКА – функция позволяет скрыть ошибочные данные и успешно пройти собеседование.
Промежуточные итоги – часто задействуют инструмент для получения итоговых сумм.
Условное форматирование – возможность применить требуемое форматирование только к данным, соответствующим тем или иным критериям.
ЕСЛИ – логическая функция проверки выполнения условия, использующаяся при решении «продвинутых» производственных задач.

Аспекты проблемы анализа, классификация методов

Аспекты проблемы анализа. Вся проблема аналитической подготовки принятия решений имеет три аспекта:

•сбор и хранение необходимой для принятия решений информации;

•собственно анализ, в том числе оперативный и интеллектуальный;

•подготовка результатов оперативного и интеллектуального анализа для эффективного их восприятия потребителями и принятия на её основе адекватных решений.

Аспект, касающийся сбора и хранения информации с сопутствующей доработкой, оформился в концепцию информационных хранилищ ( Data Warehouse ).

В связи с большим объёмом и сложностью аспект проблемы собственно анализа имеет два направления — оперативный анализ данных (информации), широко распространена англоязычная аббревиатура названия — On — Line Analytical Processing — OLAP. Основной задачей оперативного или OLAP -анализа является быстрое (в пределах секунд) извлечение необходимой аналитику или ЛПР для обоснования или принятия решения информации.

Интеллектуальный анализ информации – имеет также широко распространённое в русской специальной литературе англоязычное название Data mining. Предназначен для фундаментального исследования проблем в той или иной предметной области. Требования по времени менее жёстки, но используются более сложные методики. Ставятся, как правило, задачи и получают результаты стратегического значения.

Жёстких границ между OLAP и интеллектуальным анализом нет, но при решении сложных задач приходится использовать весьма мощные специальные программные средства или, как говорят, инструменты.

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

Рассмотрим систему признаков, характеризующих методы анализа. Их можно сгруппировать:

По целям — это:

— оценка состояния и результатов деятельности предприятия;

— постоянный контроль рациональности ведения хозяйственной деятельности, выявление резервов для обеспечения выполнения поставленных задач;

— прогнозирование хода внутренних процессов на предприятии и внешних факторов, влияющих на его деятельность.

По временному фактору анализ разделяют на:

— использующий прошлую информацию, отражённую в документации и на различных носителях и содержащуюся в информационной системе — анализ фактов;

— на базе как прошлой, так и обращённой в будущее, то есть прогнозной информации — анализ событий и отклонений;

— анализ будущей информации — по существу оценка бюджетов и планов, их альтернатив.

По масштабности решаемых или обслуживаемых задач:

— стратегический, сюда можно отнести оценку эффективности целей, долгосрочные прогнозы, исторические оценки процессов и явлений и т.д.;

— оперативный — это оценка текущего состояния, выявление узких мест и отклонений;

— система раннего предупреждения.

По предметным областям:

— в маркетинге;

— производственной или основной деятельности;

— в логистике;

— обеспечении ресурсами;

— финансовой;

— в сфере инвестиций и инноваций.

По методам различают:

— сравнительный по подразделениям, предприятиям, регионам, временным периодам и т.д.;

— анализ отклонений;

— функционально-стоимостный;

— анализ цепочки создания стоимости и конкурентный анализ по Портеру;

— анализполейбизнеса (Profit Impact of Market Strategies — PIMS);

— бенчмаркинг (Beanchmarking );

— интеллектуальный анализ (Data mining).

Подбор параметра

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

Как использовать Подбор параметра (пример 1):

Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).
На вкладке Данные выберите команду Анализ “что если”, а затем в выпадающем меню нажмите Подбор параметра.
Появится диалоговое окно с тремя полями:
- Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.
- Значение – требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.
- Изменяя значение ячейки – ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании.
Выполнив все шаги, нажмите ОК.
Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК.
Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

Выделите ячейку, значение которой необходимо изменить. В нашем случае мы выделим ячейку B4.
На вкладке Данные выберите команду Анализ “что если”, а затем в выпадающем меню нажмите Подбор параметра.
Появится диалоговое окно с тремя полями:
- Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.
- Значение – требуемый результат. Мы введем 500, поскольку допустимо потратить $500.
- Изменяя значение ячейки – ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.
Выполнив все пункты, нажмите ОК.
Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK.
Результат появится в указанной ячейке. В нашем случае Подбор параметра вычислил результат 18,62. Поскольку мы считаем количество гостей, то наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей в большую сторону, мы превысим заданный бюджет, значит, остановимся на 18-ти гостях.

Как видно из предыдущего примера, бывают ситуации, которые требуют целое число в качестве результата. Если Подбор параметра выдает десятичное значение, необходимо округлить его в большую или меньшую сторону в зависимости от ситуации.

Система бизнес анализа на основе диаграмм в Excel

Готовые значения используем для построения наглядной диаграммы. Цель – проанализировать скорость погашения ДЗ и КЗ на основе параметров оборачиваемости. Исходные данные берем из таблиц результатов на листах покупатели и поставщики.

Для покупателей ранжируем периода погашения долга следующим образом: 30-60-90-120-180-260 дн. Для поставщиков: 30-60-90-120-180-360. Отдельно заполняем диаграмму для сомнительного долга, под который создан резерв. Это период оборота 180-360 дн. и свыше 360 дн

Обратите внимание, размер сомнительного долга необходимо исключить из общей величины ДЗ. Во всех случаях используем формулу: СУММЕСЛИ

При заполнении диаграммы для поставщиков, вычитаем долг предыдущего периода из общей КЗ. Например, нужно получить сумму долга со сроком 60-90 дней, нужно из общей суммы задолженности

Что же мы получили в итоге на диаграмме?

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

Выводы для нашего конкретного примера:

Хотя дебиторский долг превышает кредиторскую задолженность, в периоде 31-60 дней возникает дефицит денег в размере 48 млн. руб.

Как проверить свои знания Excel и спать спокойно?

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

Онлайн тестирование

Вариант для тех, кто желает получить быструю сводку своих знаний

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

Интервью с консультантом

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