Как сделать анализ пресса в Excel

Обновлено: 20.11.2024

ABC-анализ – это популярный метод анализа ресурсов и их классификации. Представьте, что вы обрабатываете запасы на заводе, производящем супердорогие автомобили высокого класса. Для сборки каждой машины требуется несколько деталей (4693, если быть точным). Некоторые из этих деталей очень дорогие (скажем, несколько тысяч долларов за деталь), а другие дешевые (50 центов за деталь). Итак, как сделать так, чтобы ваши усилия по отслеживанию запасов были оптимизированы, чтобы вы тратили меньше времени на 50-центовые детали и тратили больше времени на дорогостоящие?

В этом поможет ABC-анализ.

Мы группируем детали по 3 классам.

Класс A: товары с высокой стоимостью. Очень жесткий контроль и отслеживание.
Класс B: предметы средней стоимости. Жесткий контроль и умеренное отслеживание.
Класс C: недорогие товары. Нет или мало контроля и отслеживания.

Учитывая список элементов (номера деталей, стоимость единицы продукции и количество единиц, необходимых для сборки), как мы автоматически определяем, к какому классу принадлежит каждый элемент?

И как мы создадим из него приведенную ниже диаграмму анализа ABC?

Этому мы и будем учиться. Так что хватайте свой инвентарь и следуйте дальше.
(связано: страница анализа ABC в Википедии)

ABC-анализ с использованием Excel — пошаговое руководство

1. Упорядочивание данных инвентаризации в Excel

Перенесите все данные о запасах (или деталях) в Excel. В ваших данных должно быть как минимум эти столбцы.

После того, как данные будут в Excel, превратите их в таблицу, нажав CTRL+T. Давайте назовем наши данные как inventory. Вы можете задать имя таблицы на вкладке «Дизайн».

2. Вычислить дополнительные столбцы, необходимые для классификации ABC

Теперь самое интересное. Обработка данных инвентаризации с помощью формул. Вкусняшка!

Рейтинг: нам нужно выяснить, каков ранг каждой общей стоимости (в столбце общей стоимости). Для этого мы можем использовать формулу RANK.

=RANK([@[Общая стоимость]],[Общая стоимость],0) сообщит нам ранг каждой общей стоимости.

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

Совокупная стоимость и совокупная стоимость %:

Это аналогичные расчеты (вместо единиц мы рассчитываем стоимость)

Объяснение этих расчетов:

Посмотрите анимацию ниже, чтобы понять, как обрабатываются цифры.

3. Создать диаграмму распределения запасов

Выберите столбцы совокупных единиц и совокупной стоимости в процентах и создайте диаграмму XY. Убедитесь, что совокупные единицы указаны по горизонтальной оси (X), а совокупная стоимость в процентах — по вертикальной оси (Y).

Наша кривая должна выглядеть примерно так.

4. Настройте пороги классификации ABC

Теперь нам нужно решить, каков порог для классов A, B и C.

В большинстве случаев класс A, как правило, составляет 10 % лучших товаров.

Следующие 20 % – класс B

Класс C — это последние 70%.

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

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

Поэтому $O$7:$O$9 содержит пороговые значения.

Рядом с этим диапазоном вычислите дополнительные числа (для построения маркеров и прямоугольников A, B и C) следующим образом:

Проверьте загружаемый файл на наличие точных формул.

5. Добавьте на диаграмму столбцы элементов ABC и % общей стоимости

Добавьте дополнительные данные на диаграмму (щелкнув правой кнопкой мыши на диаграмме, выбрав поле данных и нажав кнопку «Добавить»).

После добавления новой серии убедитесь, что вы отформатировали ее только как маркеры, чтобы получилось что-то вроде этого.

6. Добавьте полосы ошибок к маркерам ABC, чтобы получить прямоугольники

Этот шаг включает в себя добавление планок погрешностей к серии маркеров ABC и их настройку.

В Excel 2013: добавьте планки погрешностей, нажав кнопку + рядом с диаграммой

В более ранних версиях: делайте это с ленты макета

После добавления планок погрешностей настройте их (выберите и нажмите CTRL+1). Установите количество ошибок на «Пользовательский» и выберите расчетные значения ошибок, как показано ниже.

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

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

7. Очистите диаграмму, добавьте метки и заголовки

Здесь можно проявить творческий подход. После некоторой очистки мы можем получить что-то вроде этого.

Загрузить шаблон рабочей книги ABC Inventory Analysis

Нажмите здесь, чтобы загрузить книгу ABC Inventory Analysis. Он содержит образцы данных и диаграммы. Изучите настройки формул и диаграмм, чтобы узнать больше. Или, если вы спешите, замените образцы данных сведениями о вашем инвентаре и получите мгновенные результаты.

Используете ли вы ABC-анализ для отслеживания и контроля запасов?

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

А вы? Используете ли вы такие методы, как ABC-анализ, чтобы сузить список до нескольких наиболее важных элементов? Как вы это делаете в Excel? Делитесь своими советами и опытом в комментариях.

Добавьте в свой инвентарь еще несколько техник

У вас недостаточно навыков работы с Excel? Запаситесь перечисленными ниже вкусностями.

Поделитесь этим советом со своими коллегами

Получите БЕСПЛАТНЫЕ советы по Excel и Power BI

Простые, веселые и полезные электронные письма, раз в неделю.

Учись и будь классным.

44 комментария
Задайте вопрос или скажите что-нибудь.
Теги: abc, расширенные возможности Excel, анализ, аналитические диаграммы, форматирование диаграмм, загрузки, планки погрешностей, ИНДЕКС(), инвентаризация, ПОИСКПОЗ(), формулы Microsoft Excel, диаграммы Парето, точечная диаграмма, скринкасты, средство отслеживания.
Категория: Диаграммы и графики, Изучение Excel

Большое спасибо за визит. Моя цель — сделать вас великолепными в Excel и Power BI. Я делаю это, делясь видео, советами, примерами и загрузками на этом сайте. Здесь более 1000 страниц со всеми функциями Excel, Power BI, Dashboards и VBA. Идите вперед и потратьте несколько минут, чтобы быть УДИВИТЕЛЬНЫМ. Прочтите мою историю • БЕСПЛАТНАЯ книга советов по Excel

От простого к сложному, есть формула на все случаи жизни. Ознакомьтесь со списком прямо сейчас.

Календари, счета-фактуры, трекеры и многое другое. Все бесплатно, весело и фантастически.

Power Query, модель данных, DAX, фильтры, слайсеры, условные форматы и красивые диаграммы. Все здесь.

Все еще думаете о Power BI? Из этого руководства по началу работы вы узнаете, что такое Power BI, как его получить и как создать свой первый отчет с нуля.

БЕСПЛАТНЫЙ шаблон календаря и ежемесячного планировщика на 2022 год для печати

Счастливого Рождества и Нового 2022 года

Как писать сложные формулы Excel (подсказка: это очень похоже на LEGO)

Сортировка значений в стиле таблицы олимпийских медалей [Совет]

Как использовать значения даты и времени в Excel — 10 + 3 совета

Советы по теме

БЕСПЛАТНЫЙ шаблон календаря и ежемесячного планировщика на 2022 год для печати

Как писать сложные формулы Excel (подсказка: это очень похоже на LEGO)

Как использовать значения даты и времени в Excel — 10 + 3 совета

6 основных функций TEXT в Excel с 6 повседневными примерами

Как создать полностью интерактивную информационную панель проекта с помощью Excel — учебное пособие

Извлечение данных из PDF в Excel — пошаговое руководство

44 ответа на «Анализ запасов ABC с использованием Excel»

мне; На самом деле я не храню вещи ABC на полке, вместо этого abc управляется ценностью использования или количеством использования, и применение этого — то, что я использую, чтобы посмотреть на инвентарь, который более показательен для того, что перемещается.

Тем не менее, классная диаграмма, классная техника.

В любом случае числа в этом массиве будут одинаковыми (просто перетасованными). Я использую порядковый номер, так как всегда есть вероятность дублирования ранга.

@Pedo Я не смог сработать ни одним из этих методов. для накопительных единиц. есть идеи, что я делаю неправильно?

Вам также стоит попробовать K-Curve, более утонченную и оптимизируемую? техника

Как бы вы это сделали, если бы вам также пришлось расширить свою диаграмму классификацией XYZ?

Вы бы не стали. Это работает, потому что стоимость — это серия, а стоимость — это то, как он выводит abc.

Сложенные столбцы выглядят более логично.

Какие числа нужно сложить, чтобы получить 36?

Если вы суммируете единицы для частей с рейтингом 1, 2, 3, 4, 5 и 6, вы должны получить 39.

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

c юнитов на первом ранге – 39, а c юнитов на втором ранге – 39 + 22 = 61.

С уважением,
Прасад Д.Н.

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

Спасибо, Прасад.

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

В статье говорится: "Например, ранг первой части (PT1387-w) равен 6. Суммарное количество единиц для нее равно 39. Это означает, что 39 – это общее количество единиц для первых 6 частей".

Это на самом деле описывает, что делает уравнение. Chandoo, вероятно, попросил стажера написать описания, которые привели к ошибочному объяснению.

Спасибо за разъяснение. Я написал статью. Я подумал, что поскольку мы уже использовали слово RANK в предложении, первые 6 частей означают первые 6 ранжированных частей. Поэтому удалил «ранжированную» часть, чтобы сделать ее краткой. Очевидно, это смутило многих читателей.

Отлично, Чанду.

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

<р>1. Я взял возрастную классификацию 70%, 20%, 10% на основе общей суммы, а не на нет. предметов.
2. Я использовал VBA, чтобы найти ABC.
3. Я использовал предел допуска в% возраста, например. 70,01 следует рассматривать как A или 69,9 как A, и после этого, если совокупный %age подскакивает до 72%, остановитесь на 69,9% для A.

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

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

Привет, Виджей. Сделаю через день или два.

Я не использую ABC-анализ, так как не занимаюсь управлением запасами. В то же время мне хотелось бы посмотреть, смогу ли я интерпретировать это в своей работе.

Что я действительно не мог понять, так это часть заключения, я понимаю, что класс A мы тратим сумму XX, которая составляет YY% от общей стоимости, и количество единиц ZZ, но как я буду принимать решение или управляйте этой информацией.

Пожалуйста, пролейте на это больше света.

Я попробую.

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

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

цитируя это утверждение: «Мы группируем детали по 3 классам».

Класс A: Дорогостоящие товары. Очень жесткий контроль и отслеживание.
Класс B: предметы средней стоимости. Жесткий контроль и умеренное отслеживание.
Класс C: недорогие товары. Нет или мало контроля и отслеживания.
По списку элементов (номера деталей, стоимость единицы продукции и количество единиц, необходимых для сборки), как мы автоматически определяем, к какому классу принадлежит каждый элемент?"

Я уверен, что это может относиться к нашей компании

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

поправьте меня, если я ошибаюсь.

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

Я применил все приведенные выше формулы к своим данным, но кривая не выглядит гладкой? Кто-нибудь может помочь?

Я хотел поблагодарить вас за это удивительное, кристально чистое объяснение!
Только что заметил опечатку на скриншоте главы 4 "marke(t)rs" 😉

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

Просто проверяю, для единиц (кумулятивных и %), как это может быть сумма тех, кто меньше ранга 6? Судя по подсчетам вручную, это, кажется, занимает 1 место.

Например, для первой строки у вас есть ранг 6 и 39, если мы используем ваше объяснение, это будет означать, что продано 39 единиц с рангом 1–5. Но если вы посмотрите на ряды ниже, например, на 4-м ранге, то «кумулятивных» юнитов будет 134. Было бы бессмысленно, чтобы было 134 юнита для рангов 1-4, а для рангов 1-6 было бы меньше. единиц в 39.

Я считаю, что правильным объяснением было бы то, что первая строка кумулятивного числа будет единицами для первого ранга, 2-я строка будет кумулятивной суммой рангов 1-2.

Пожалуйста, поправьте меня, если я ошибаюсь.

Очень ценю это, ваши навыки работы с Excel находятся на другом уровне.

Кроме того, число юнитов на каждом уровне неверно, так как общее количество юнитов составляет 82 378, однако в столбце юнитов рядом с группой C (предполагается, что это 70 % от 82 378) оно показывает 82 378.

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

Основой АВС-анализа является знаменитый принцип Парето, гласящий, что 20% усилий дают 80% результата. Преобразованный и детализированный, этот закон был применен при разработке обсуждаемых нами методов.

ABC-анализ в Excel

Метод ABC позволяет отсортировать список значений по трем группам, которые по-разному влияют на конечный результат.

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

Значения в списке после применения метода ABC разбиты на три группы:

A — самый важный из всех (20% дают 80% результатов).
B – средний по важности (30–15%).
C – наименее важный (50%–5%).

Эти значения не являются обязательными. Методы определения границ АВС-групп будут различаться при анализе различных показателей. Но если выявляются существенные отклонения, стоит задуматься, что не так.

Условия использования АВС-анализа:

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

Для некоторых значений можно использовать методологию анализа ABC:

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

Метод ранжирования очень прост. Но обрабатывать большие объемы данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает проведение АВС-анализа.

Общая схема:

Определить для целей анализа. Определить объект (какой анализ) и параметр (по какому принципу будет сортироваться по группам).
Сортируйте параметры по убыванию.
Суммировать в числовые данные (параметры - выручка, сумма долга, объем заказов и т. д.).
Найдите долю каждого параметра в общей сумме.
Вычислить долю совокупного итога для каждого значения списка.
Найдите в списке значение, в котором доля нарастающего итога приближается к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
Найдите в списке значение, в котором доля нарастающего итога близка к 95 % (+ 15 %). Это нижний предел группы B.
Для C – все, что ниже.
Подсчитайте количество значений для каждой категории и общее количество позиций в списке.
Найдите общие доли каждой категории.

АВС-анализ товарного ассортимента в Excel

Формируем учебную таблицу с 2 столбцами и 15 строками. Вставляем названия условных товаров и даты продаж за год (в денежном выражении). Необходимо ранжировать диапазон дохода (какие товары приносят больше прибыли).

Итак, мы закончили ABC-анализ средствами Excel. Дальнейшие действия пользователя – это использование полученных данных на практике.

XYZ-анализ: пример расчета в Excel

Этот метод часто используется в дополнение к ABC-анализу. Комбинированный термин ABC-XYZ-анализ даже встречается в литературе.

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

Коэффициент вариации – относительная мера, не имеющая конкретных единиц измерения. Достаточно информативно. Даже сам по себе. НО! Тенденция, динамика сезонности значительно повышают предсказуемость курса. В результате снижается предсказуемость скорости. Эта ошибка может привести к неправильным решениям. Это огромный минус XYZ-метода. Тем не менее…

Возможны объекты для анализа: объем продаж, количество поставщиков, выручка и т. д. Чаще этот метод используется для определения товаров, на которые есть устойчивый спрос.

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

Проще говоря, ABC-анализ распределяет ресурсы по трем категориям:

А – самое важное
Б – важно
C — менее важный или незначительно важный

Допустим, мы измеряем важность с точки зрения дохода от продукта. Обычно наблюдается, что 10-15% продуктов, предлагаемых организацией, приносят 70-80% общего дохода от продаж. В Excel классифицировать запасы таким образом очень просто. Скажем:

Товары, приносящие 70 % продаж, относятся к категории A.
Товары, приносящие следующие 25 % продаж, относятся к категории B.
А продукты, приносящие остальную часть дохода, которая составляет 5 %, относятся к категории C.

0% – 70% – A
70% – 95% – B
95% – 100% – C

Это то, что нам нужно в этом руководстве после того, как мы поняли, как Excel помогает нам классифицировать продукты на основе заданных данных:

ABC-анализ с использованием Excel

Шаг 1. Откройте Excel и получите указанную выше информацию о классификации в ячейке A1:

0	0,7
0,7	0,95
0,95	1

Шаг 2. Скопируйте следующие данные и вставьте их на лист в ячейку A5

< /tr> < /tr> < /tr> < /tr> < /tr> < /tr> < /tr> < /tr> < /tr> < /tr> < /tr> < /tr>

Продукт	Количество
Sesme – 1	20 000
Сесме – 2	17 000
Сесме – 3	14450
Сесме – 4	12283
Сесме – 5	10441
Сесме – 6	8875
Сесме – 7	7544
Сесме – 8	6412
Сесме – 9	5450
Сесме – 10	4633
Сесме – 11	3938
Сесме – 12	3347
Сесме – 13	2845
Сесме – 14	2418
Сесме – 15	2055
Сесме – 16	1747
Сесме – 17	1485
Сесме – 18	1262
Сесме – 19	1073
Сесме – 20	912
Сесме – 21	775
Сесме – 22	659
Сесме – 23	560
Сесме – 24	476
Сесме – 25	405
Сесме – 26	344
Сесме – 27	292
Сесме – 28	248
Сесме – 29	211
Сесме – 30	179
Сесме – 31	152
Сесме – 32	129
Сесме – 33	110
Сесме – 34	94
Сесме – 35	80
Сесме – 36	68
Сесме – 37	58
Сесме – 38	49
Сесме – 39	42
Сесме – 40	36
Сесме – 41	31
Сесме – 42	26
Сесме – 43	22
Сесме – 44	19
Сесме – 4 5	16
Сесме – 46	14
Сесме – 47	12
Сесме – 48	10
Сесме – 49	9
Сесме – 50	8

Следующая анимация проведет вас через весь процесс:

Шаг 3. Перейдите к ячейке C5 и создайте заголовок Совокупный доход, а в ячейке:

C6 поставить формулу: = B6
C7 поместите формулу: =C6+B7 и перетащите маркер заполнения вниз к ячейке C55 или просто дважды щелкните, чтобы заполнить ячейки вниз.

Шаг 4. Выделите все ячейки от A6 до A55 с помощью мыши или клавиатуры.

Совет. Вы можете сделать это быстро, если у вас активна ячейка A6, нажмите и удерживайте клавиши Shift + Ctrl и нажмите клавишу со стрелкой вниз. И нажмите Ctrl+Backspace, чтобы вернуться к активной ячейке.

Шаг 5. Перейдите на вкладку «Главная» > группа «Стили» > нажмите кнопку раскрывающегося списка «Условное форматирование» > нажмите «Новое правило» > В диалоговом окне нажмите «Использовать формулу», чтобы определить, какие ячейки нужно форматировать > Введите следующую формулу:

=IF(группа C6 Styles > Нажмите кнопку раскрывающегося списка условного форматирования > щелкните новое правило > В диалоговом окне щелкните Использовать формулу, чтобы определить, какие ячейки форматировать > Введите следующую формулу для товаров категории B:

=AND(C6>=$B$1*$C$55,C6 Группа стилей > Нажмите кнопку раскрывающегося списка условного форматирования > щелкните новое правило > В диалоговом окне щелкните использовать формулу, чтобы определить, какие ячейки следует форматировать > Поместите следующее формула для товаров категории C:

Ранее в этом месяце мы узнали, как проводить инвентаризационный анализ ABC в Excel с использованием метода условного форматирования. Это было легко, и мы получили массу удовольствия, так как мы не только поняли, как применять условное форматирование, но и узнали множество других вещей, таких как функция AND() и как управлять различными условиями для получения желаемых результатов.

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

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

ABC-анализ с использованием диаграммы Excel — шаг за шагом

Шаг 1. Загрузите эту учебную книгу Excel. Он содержит основные данные, необходимые для проведения анализа.

Шаг 2: В ячейку C5 поместите заголовок «Накопленная сумма». А в ячейку C6 поместите следующую формулу:

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

Шаг 4. Перейдите к ячейке D5 и поместите заголовок «Класс A», а в ячейке D6 введите следующую формулу и дважды щелкните маркер заполнения:

=IF(C6 =$B$1*$C$55,C6 =$B$2*$C$55,C6 Получение диаграммы!

Наши данные разрознены и неоднородны. Чтобы упростить понимание, я разобью его на шаги, которые выглядят следующим образом:

Шаг 1. Выберите столбец "Продукты", нажмите и удерживайте клавишу CTRL на клавиатуре и выберите также столбец "Накопленная сумма".

Шаг 2. Перейдите на вкладку «Вставка» > в группе «Диаграммы» выберите линейную диаграмму > 2D-линия > Простая линейная диаграмма.

Шаг 3. Щелкните правой кнопкой мыши в любом пустом месте диаграммы > выберите данные. Откроется диалоговое окно выбора источника данных.

Шаг 4. Нажмите кнопку «Добавить». Наведите курсор на поле ввода названия серии и щелкните ячейку H5. Он извлечет название серии из этой ячейки. Удалите что-либо в поле ввода значений серии и выберите весь столбец Stack 1. Нажмите "ОК".

Шаг 5. Нажмите кнопку "Добавить" еще раз и на этот раз вставьте значения класса B, выполнив тот же процесс, что и в шаге 4. Нажмите "ОК" и закройте все диалоговые окна.

Шаг 6. Щелкните правой кнопкой мыши строку и выберите «Форматировать ряд данных». В параметрах серии нажмите на дополнительную ось.

Шаг 7. Щелкните правой кнопкой мыши пустое место на диаграмме и выберите "Выбрать данные". Выберите серию класса A или класса B и нажмите кнопку «Изменить» справа под метками горизонтальной оси категорий и выберите значения столбца «Ось». Нажмите OK, чтобы закрыть.

Шаг 8. Щелкните правой кнопкой мыши в любом месте пустой диаграммы и выберите "Изменить тип диаграммы". Для серий классов A и B выберите тип диаграммы с областями с накоплением и нажмите OK.

Шаг 9. Щелкните правой кнопкой мыши ось и выберите ось формата. В разделе Параметры оси > тип оси > выберите ось даты.

Блин! Ты это видел? 😀

Почти готово. Теперь у нас осталось только несколько косметических лакомых кусочков, и все готово!

Завершение диаграммы ABC-анализа

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

Шаг 2. Удалите дополнительную вертикальную ось справа, щелкнув левой кнопкой мыши и нажав клавишу "Удалить".

Шаг 3. Щелкните правой кнопкой мыши по горизонтальной оси > ось формата. В основном поле ввода введите 4. Это будет отображать только единицы с интервалом 4, что сделает его менее грязным и чистым.

Шаг 4. Выберите «Вставка» > «Группа иллюстраций» > «Раскрывающаяся кнопка фигур» > «Текстовое поле». Нарисуйте его где-нибудь и скопируйте.

Шаг 5. Щелкните правой кнопкой мыши границу диаграммы и выберите "Вставить". Это позволит вставить текстовое поле прямо в область диаграммы, что облегчит нам перемещение диаграммы, поскольку текстовое поле будет перемещаться вместе с ней. Вам нужно три таких текстовых поля. Вы можете нажать и удерживать клавишу CTRL на клавиатуре, а затем щелкнуть левой кнопкой мыши по тексту и перетащить, чтобы легко получить дубликат.

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

Шаг 7. Таким же образом щелкните, выберите среднее текстовое поле и перейдите в строку формул, нажмите знак равенства и щелкните ячейку J10. То же самое для третьего текстового поля, но для этой ячейки — J13.

Фу! Это была непростая задача! Но я надеюсь, что вы многому научились. Для меня это тоже был настоящий подвиг, так как я никогда не использовал диаграмму с областями таким образом. Практика и на всякий случай, если у вас есть какие-либо вопросы, у вас есть раздел комментариев 🙂

Читайте также: