Анализ таблицы примеры этого

Анализ таблицы примеры этого

С помощью средств анализа «что если» в Microsoft Excel можно экспериментировать с различными наборами значений в одной или нескольких формулах для изучения всех возможных результатов.

Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%.

Итак, проиллюстрируем проведение анализа данных «что-если» на примере работы функции ПЛТ, которая вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.

Вызов функции имеет вид: ПЛТ (ставка;кпер;пс;бс;тип)

Ставка — процентная ставка по ссуде.

Кпер — общее число выплат по ссуде.

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

Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, предполагается, что он равен 0 (например, значение «бс» для займа равно 0).

Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

Рассмотрим пример использования функции ПЛТ в Exceel.

Итак, требуется определить ежемесячные выплаты по займу в 20 000 руб., взятому на 16 месяцев под 11% годовых.

Для решения задачи выделяем ячейку на рабочем листе Excel (в нашел случаи ячейка А1) и в строку формул вводим следующее выражение: =ПЛТ(11%/12; 16; 20000) (Рис.1.1)

Рис. 1.1 — Ввод формулы Excel.

Нажав на клавишу Enter , мы получаем величину ежемесячных выплат по ссуде, которая составит -1350 руб. Рис.1.2

Рис. 1.2 – Величина ежемесячной выплаты по ссуде.

При ином значении банковской учетной ставки, следует сделать исправления в ранее введенной функции в Excel.

Другой подход к вычислению функции ПЛТ методом «что если» в Excel проиллюстрирован на Рис. 1.3. Функция ПЛТ определена в ячейке D7, а значения аргументов записаны в ячейках D2, D3 и D4. Для получения значения функции при новых значениях аргумента достаточно внести соответствующие изменения в исходные данные. В этом случаи в строке формул на рис.1.3 мы вводим не конкретное значение аргумента, а ссылку ни соответствующую ячейку.

Рис. 1.3 — Пример расчета Excel, в котором исходные данные в отдельные ячейки

При изменении любых значений на рис.3 результаты расчета автоматически обновляются в разделе Результат расчета.

Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных «Что если» в Exceel.

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

При вычислении различных функций возникает вопрос: «Каким должно быть значение определенного аргумента функции, чтобы функция возвратила заданный результат?».

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

Из расчетной части рис.1.3 видно, что при заданных исходных данных требуется ежемесячно выплачивать по 1350 руб. для погашения займа. Предположим, что по каким-то причинам кредитор имеется возможность выплачивать не более 1200 руб. в месяц. Спрашивается, какую максимальную величину ссуды может он запросить, если все прочие условия сохраняются?

Для решения этой задачи выберем команду Данные > Анализ «что если» > Подбор параметра (рис. 2.1). В верхнем поле этого окна указывается ссылка на ячейку D7, в которой устанавливается желаемый результат (в нашем случае – это -1200 руб). В нижнее поле диалогового окна вставляется ссылка на ячейку, в которой хранится значение искомого параметра, т.е. D4.

Рис. 2.1 — Диалоговое окно Подбор параметра в Excel

При нажатии клавиши ОК мы получим максимальную сумму займа, при условии выплаты ежемесячно 1 200 руб. Рис.2.2

Рис. 2.2 – Максимальная величина займа 17 783 руб.

Вывод: Выполнение анализа «что-если» в Excel обеспечивает достаточно оперативную оценку влияния того или иного аргумента на результат вычисления.

Проведение анализа на основе таблицы подстановки в Excel

Таблицы подстановки для одной переменной.

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

Рассмотрим способ построения так называемой таблицы подстановки для одной переменной, используя приведенный выше пример вычисления функции ПЛТ.

Для построения таблицы подстановки необходимо подготовить исходные данные рис.3.1

Рис. 3.1 – Подготовка исходных данных для построения таблицы подстановки Excel

В ячейке G3 этой таблицы определена точно такая же формула, как и в ячейке D7. Первый столбец таблицы подстановки заполнен значениями аргумента функции ПЛТ, в зависимости от которого требуется проанализировать поведение финансовой функции (в нашем случае от 11 до 15%).

Чтобы получить соответствующие значения функции во втором столбце, нужно выделить диапазон ячеек — F3:G7, и после этого выполнить команду меню Данные > Анализ «что если» > Таблица данных… . В результате появляется диалоговое окно этой команды (рис. 3.2).

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

Рис. 3.2. — Диалоговое окно Таблица подстановки в Excel

После щелчка на кнопке ОК столбец результатов таблицы подстановки будет заполнен (рис. 3.3).

Рис.3.3. Таблица подстановки для одной переменной в Excel

Таблица подстановки для двух переменных в Excel.

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

Поставим задачу проследить характер изменения функции ПЛТ в зависимости от изменения годовой процентной ставки и срока погашения ссуды.

Для начала, подготовить исходные данные на рабочем листе, как это показано на рис. 3.4

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

Рис. 3.4 — Подготовка исходных данных для построения таблицы подстановки Excel

Чтобы получить значения функции в таблице, выделяем диапазон ячеек F2:J7, который содержит исходные значения процентных ставок, исходные значения срока погашения ссуды и расчетную функцию. После этого нужно выполнить команду меню Данные > Анализ «что если» > Таблица подстановки. В результате появится диалоговое окно (рис. 3.5).

Рис. 3.5 Диалоговое окно Excel Таблица подстановки

Это окно служит для задания абсолютных адресов ячеек, на которые ссылается расчетная функция. После щелчка на кнопке ОК столбец результатов таблицы подстановки будет заполнен (рис.3.6).

Рис. 3.6 Расчетные значения таблицы подстановки Excelдля двух переменных

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

Проведение графического анализа в Excel.

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

На рис. 3.7 и 3.8 представлены диаграммы, построенные на базе таблиц подстановки для одной-двух переменных соответственно. Так, для построения диаграммы для двух переменных выделим диапазон ячеек F3:J7 и выберем тип диаграммы «точечная». Затем следует отредактировать полученную диаграмму.

Ежемесячные выплаты по ссуде

Рис. 3.7 Диаграмма excel, построенная на основе диапазона ячеек F3:G7 таблицы подстановки для одной переменной (см. рис. 3.3)

Ежемесячные выплаты по ссуде

Рис. 3.8 — Диаграмма Excel, построенная на базе диапазона ячеек F3:J7 таблицы подстановки для двух переменных (см. рис. 3.6)

Поиск решения в Exceel

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

Читайте также:  Анализ экономики средств массовой информации

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

Характерные особенности задач, для решения которых предназначено данное средство, заключаются в следующем:

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

имеются ограничения, выраженные в виде неравенств;

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

Правильная формулировка ограничений — самая ответственная часть описания модели для поиска решения. Следует особенно внимательно следить за тем, чтобы задавать все объективно существующие ограничения. Неполнота описания ограничений приводит к неправильному решению.

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

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

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

Для пояснения принципа работы средства Поиск решения рассмотрим пример, используя данные таблицы на рис. 4.1.

Рис. 4.1 — Таблица Excel для определения количества товаров, приносящих максимальную прибыль

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

Ячейка (Е7), в которую помещается ответ, называется целевой. Целевая ячейка содержит формулу, результат которой зависит от значений, содержащихся в других ячейках, называемых изменяемыми.

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

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

общее число производимых товаров за отчетный период должно составлять ровно 1000 шт.;

товар С пользуется наименьшим спросом, поэтому, как показал опыт, удается реализовать товар этого вида не более 140 шт.;

на товары вида A, B, D имеются заказы соответственно на 50, 100 и 200 шт., которые необходимо выполнить.

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

Ввести исходные данные, как это показано на рис. 4.1.

  • Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
  • Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
  • Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
  • Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
  • Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.

Результат поиска решения представлен на рис. 4.5.

Рис. 4.2 – Диалоговое окно Поиск решений в Excel

Рис 4.3 – Диалоговое отношение Добавление ограничений Excel

Рис. 4.4. – Введение ограничения Excel

После того как найдем оптимальное решение, мы можем выбрать одну из следующих возможностей:

1) сохранить найденное решение;

2) восстановить исходные значения в изменяемых ячейках;

3) создать отчеты о процедуре поиска решения;

4) щелкнуть на кнопке Сохранить сценарий. Сохраненный сценарий может быть использован в средстве Диспетчер сценариев.

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

Источник



Чтение и анализ таблицы

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

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

— совокупности и единиц наблюдения, формирующих ее;

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

— признаков: количественных или атрибутивных;

— соотношения признаков подлежащего с показателями сказуемого;

— вида таблицы: простая или сложная, а последняя – групповая или комбинационная;

— решаемых задач – анализ структуры, типов явлений или их взаимосвязей.

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

Прежде чем приступать к анализу числовой информации, необходимо проверить ее достоверность и научную обоснованность. Исследователь должен убедиться в достоверности и надежности источника информации данных и критически оценить их цифровые значения. Следует произвести логическую и счетную проверки данных. Логическая проверка состоит в возможности определения конкретных признаков теми или иными числовыми значениями (например, абсурдно, если численность работающих на фирме составила 106,7 человека). Счетная проверка предполагает выборочный расчет отдельных значений признаков по группе, либо итоговых значений строк или граф и т.д.

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

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

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

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

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

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

Таблицы сопряженности

Таблицей сопряженности называется таблица, которая содержит сводную числовую характеристику изучаемой совокупности по двум и более атрибутивным (качественным) признакам или комбинации количественных и атрибутивных признаков.

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

Наиболее простым видом таблиц сопряженности является таблица частот 2 х 2.

Общая схема таблицы частот 2 х 2

В1 В2 Всего
А1 f11 f12 f10
А2 f21 f22 f20
Всего f01 f02 f00

Построение данной таблицы исходит из предложения, что ответы респондентов или анализируемые атрибутивные признаки будут принимать только два значения А1 и А2, В1 и В2. Внутреннее цифровое наполнение таблицы представляют частоты (fij), обладающие одновременно i-м (i = 1,2) значением одного (Ai) и j-м (j = 1,2) значением (Bj) другого качественного признака.

Итоговая графа и срока содержат информацию о количественном распределении совокупности соответственно по А и В атрибутивным признакам.

Для более полного описание и анализа явлений и процессов, характеризующихся атрибутивными признаками, используются таблицы сопряженности большей разномерности: i x j, где i = 1,2, …, к – число вариантов значений (например, ответов респондентов и т.д.) одного признака (например, признака А); j = 1, 2, …, n – число вариантов значений другого признака (В).

Источник

4 техники анализа данных в Microsoft Excel

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

Читайте также:  Цикорий перед анализом крови

1. Сводные таблицы

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

В Telegram-канале «Лайфхакер» только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!

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

Как работать

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

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Выделите диапазон данных для анализа.
  3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  4. Должно появиться диалоговое окно «Создание сводной таблицы».
  5. Настройте отображение данных, которые есть у вас в таблице.

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

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

2. 3D-карты

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

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

Как работать

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

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

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

3. Лист прогнозов

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

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

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

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

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

  1. Откройте таблицу с данными для анализа.
  2. Выделите нужный для анализа диапазон.
  3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

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

Источник

ABC и XYZ анализ в Excel: таблицы, примеры расчетов, формулы

ABC XYZ анализ — удобный метод оценки эффективности работы «жизненно важных» отделов компании: продаж, маркетинга, склада, финансов. Представляем подробную инструкцию, как выполнить ABC и XYZ-анализ в Excel.

Новости бизнеса и подборка кейсов — в вашей почте:

С общими принципами проведения ABC и XYZ-анализа можно ознакомиться здесь. А ниже — пошаговая инструкция, как сделать ABC-анализ в Excel.

Содержание:

4 вопроса до начала ABC-анализа

  1. Цель. Зачем вы проводите исследование? Увеличить выручку компании, исключить возможность упущенной выгоды и т.п.
  2. Результат. Как вы сможете применить полученные значения? Оптимизируем складские запасы, пересмотрим условия договоров и т.п.
  3. Источники данных. Как вы соберете исходные данные: объект и параметр анализа? Объект анализа — перечень товаров, параметр — выручка в количественном и денежном выражении.
  4. Матрица. Какое АВС XYZ процентное распределение закладывать в расчет? Классический вариант на основе принципа Парето: 80% приносят выручки приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, нужно знать специфику работы компании, жизненные циклы и сезональность. Ошибки в матрице могут привести к тому, что в неприбыльной группе С окажутся важные покупатели с редкими закупками.
ABC и XYZ анализ: суть, методика, расшифровка результатов

ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами

Ассортиментный ABC анализ проведем на примере компании по продаже запасный частей для сельскохозяйственной техники.

Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.

Из учетной системы выгружаем данные за 2020 год:

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

Сортировка выручки по убыванию

Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».

В ниспадающем меню выбираем:

Данные — Сортировка — Сортировать по:

  • столбец «Выручка»
  • сортировка «Значения»
  • порядок «По убыванию»

Система выстраивает таблицу по убыванию размера выручки в столбце D.

Доля каждой строки в общем параметре

Определяем долю каждой номенклатуры в выручке:

  • добавляем графу Доля (Е). Формат ячеек процентный;
  • в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
  • протягиваем формулу вниз по всем товарам.

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

Читайте также:  Звуко буквенный анализ лисята

Символ & предупреждает Excel, что формулу нельзя двигать:

  • & перед буквой — по столбцам;
  • & перед цифрой — по строкам.

Перед тем как создавать ABC-таблицу проверьте долю каждого товара в общем значении (выручки, запасах, себестоимости и пр.). Проводить ABC аналитику бессмысленно, если объект распределяется примерно в равных долях. Каждый показатель вносит одинаковый вклад в результат.

Определяем группу

Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.

Руководство утвердило матрицу:

Группа Диапазон
A до 70%
B 70-90%
C 90-100%

В ячейке G2 прописываем формулу =ЕСЛИ(F2<=70%;»A»;ЕСЛИ(F2>=90%;»C»;»В»)) . Протягиваем формулу вниз по всем товарам.

В примере для наглядности проценты заданы цифрами.

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

В столбце G каждой номенклатурной группе присвоен код А, В, С.

В группу А попали товары, которые приносят основную прибыль.

В группу В — продукция компании, на которую нерегулярный спрос.

Группа С — товары, которые зарабатывают только 10% от выручки.

XYZ-анализ в Excel: оценка динамики продаж

XYZ исследование позволит увидеть изменения спроса на продукцию компании.

Выгружаем данные из учётной системы

Создаем таблицу с количеством продаж за 2020 год по каждой товарной группе по каждому кварталу.

Рассчитываем коэффициент вариации

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

Создаем графу Средние продажи. В строку 3 вводим формулу =СРЗНАЧ(B3:E3) и копируем ее для всех товарных позиций.

Создаем графу Стандартное отклонение. Стандартное отклонение / Средние продажи.

В строку 3 вводим формулу =СТАНДОТКЛОН(B3:E3) и копируем ее для всех товарных позиций.

Создаем графу Вариация, %. Вводим формулу:

Столбец Стандартное отклонение / Столбец Средние продажи

XYZ-анализ: таблицы Excel. Пример

Присваиваем значения XZY и соединяем с ABC

Руководство утвердило матрицу XYZ аналитики:

Группа Диапазон
X — постоянный спрос до 15%
Y — изменчивый спрос, сезональность от 15% до 50%
Z — случайный спрос больше 50%

Ранжируем полученные результаты с помощью функции Excel «ЕСЛИ».

В ячейку J3 вводим формулу: =ЕСЛИ(I3<=15%;»X»;ЕСЛИ(I3>=50%;»Z»;»Y»)) . Копируем формулу по всем товарным срокам.

XYZ-анализ в Excel: группы товаров по методу XYZ

Создаем графу Группа по методу ABC. Подтягиваем код группы из таблицы ABC анализа с помощью формулы: =ВПР(A3;ABC!$A$1:$G$12;7;0)

Как настроить формулу ВПР:

Задача функции: по коду товара в исходной таблице найти значение А, В или С и перенести его отчётную таблицу XYZ.

А3 — параметр, по которому ищем значение, например «Товар 6».

ABC!$A$1:$G$12 — ссылка на диапазон исходной таблицы. В ней строго в первом столбце должен быть параметр, по которому ищем значение «Товар 6».

7 — порядковый номер столбца, в котором в исходной находятся значения (коды А, В, С)

— значение ЛОЖЬ. Для Ecxel признак того, что искомый результат должен соответствовать всем 3-м предыдущим условиям.

ABC и XYZ-анализ: таблицы Excel

По каждому товару получаем двойную кодировку ABC и XYZ аналитики.

Для наглядности можно скрепить лва кода по каждому товару.

В столбец L для каждой строки вводим формулу =K&J .

ABC-анализ в сводной таблице Excel

Товары AX — высокоприбыльные позиции, которые формируют 70% выручки. На них стабильный спрос.

Товары CZ — позиции с самым низким спросом. Сюда могут попасть как неликвиды, так и элитные товары с редким спросом. Требуется дополнительная аналитика.

Подробнее о сути, эффективности и недостатках ABC XYZ анализа читайте здесь.

Источник

Анализ данных в Excel с примерами отчетов скачать

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

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

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

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Анализ что-если.

Средства анализа «Что-если»:

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

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

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

  • группировка данных; Гуппировать.
  • консолидация данных (объединение нескольких наборов данных); Консолидация данных.
  • сортировка и фильтрация (изменение порядка строк по заданному параметру); Фильтр и сортировка.
  • работа со сводными таблицами; Сводная таблица.
  • получение промежуточных итогов (часто требуется при работе со списками); Промежуточные итоги.
  • условное форматирование; Условное форматирование.
  • графиками и диаграммами.

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

Сводные таблицы в анализе данных

Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

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

  1. Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
  2. Откроется диалоговое окно «Создание таблицы». Создание таблицы.
  3. Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Конструктор.

Составить отчет можно с помощью «Сводной таблицы».

  1. Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» — «Таблицы» — «Сводная таблица»).
  2. В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
  3. Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета. Мастер сводных таблиц.
  4. Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.

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

Анализ «Что-если» в Excel: «Таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» — «Таблица данных».

  • данные должны находиться в одном столбце или одной строке;
  • формула ссылается на одну входную ячейку.

Процедура создания «Таблицы данных»:

  1. Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше. Создание Таблицы данных.
  2. Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных». Параметры Таблицы данных.
  3. В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.

Анализ предприятия в Excel: примеры

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

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

Источник

On: