Метод пропорциональных частей Excel

Обновлено: 21.11.2024

Как использовать функции Excel для отображения соотношения, например 4:3 или 8:5. Чтобы рассчитать соотношение в Excel, вы можете использовать функцию НОД или функции ТЕКСТ и ПОДСТАВИТЬ.

Видео: расчет коэффициента в Excel

Чтобы вычислить отношение между двумя числами в Excel, вы можете использовать функцию НОД (наибольший общий делитель) или использовать функции ТЕКСТ и ПОДСТАВИТЬ. Посмотрите это видео, чтобы узнать, как использовать эти формулы для расчета коэффициентов в Excel.

Письменные инструкции находятся под видео.

Вычислить отношение с помощью НОД

В этом примере есть список размеров экрана, первая ширина – 400 – в ячейке B4, а первая высота – 300 – в ячейке C4.

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

ПРИМЕЧАНИЕ. В Excel 2003 и более ранних версиях необходимо установить пакет инструментов анализа, чтобы использовать функцию НОД.

Протестируйте функцию GCD

Чтобы увидеть наибольшее число, которое делится поровну на числа в ячейках B4 и C4, введите эту формулу в ячейку D4:

Результат равен 100.

Создайте формулу соотношения

Чтобы рассчитать отношение, ширина будет разделена на НОД, а высота будет разделена на НОД. Между этими двумя числами будет стоять двоеточие.

Чтобы увидеть соотношение, введите эту формулу в ячейку E4:

Результат 4:3 — соотношение для этих размеров экрана.

Соотношение с TEXT и SUBSTITUTE

Еще один способ расчета коэффициента — с помощью функций ТЕКСТ и ПОДСТАВИТЬ — эти функции работают во всех версиях Excel без необходимости установки пакета Analysis Takepak.

В этом примере есть список размеров экрана, первая ширина – 400 – в ячейке B4, а первая высота – 300 – в ячейке C4.

Чтобы рассчитать соотношение для каждого экрана, формула разделит ширину на высоту и отформатирует результат в виде дроби. Затем косая черта будет заменена двоеточием, чтобы создать соотношение.

Протестируйте функцию ТЕКСТ

Чтобы увидеть результат в виде дроби, введите эту формулу в ячейку D4:

Результат 4/3.

Создайте формулу соотношения

Чтобы рассчитать соотношение, ширина будет разделена на высоту и отформатирована как дробь. Двоеточие заменит косую черту.

Если абсолютное число недостаточно точно описывает сценарий, пропорции помогают заполнить пробел, описывая число по отношению к общему количеству. Например, знание того, что 30 заказов были предназначены для конкретной модели продаж, относительно бессмысленно без знания общего количества заказов. Без этой жизненно важной информации вы не знаете, на долю этой модели приходится весь объем продаж или лишь незначительное меньшинство. Однако если вы знаете, что 0,35%, или 35% заказов приходится на эту модель, то вы лучше понимаете ее популярность. Если у вас есть эти данные в Excel 2013, вы можете легко подсчитать количество заказов на этот продукт, общее количество заказов в целом и их долю.

Используйте функцию «CountIf» в пустой ячейке, чтобы подсчитать количество записей, содержащих определенный термин. Используйте формат «=CountIf(диапазон, критерии)» и поместите критерии в кавычки, если только вы не ссылаетесь на другую ячейку, содержащую текстовые или числовые критерии. Диапазон может указывать определенный диапазон ячеек, например «B1:B15», или весь столбец, например «B:B». Последний вариант хорош, если столбец содержит только те данные, которые вы хотите найти. Например, для поиска в столбце B слова «Продукт A» введите следующую формулу в ячейку C1 (без кавычек здесь и далее): =CountIf(B:B», «Product A»)

Используйте функцию «CountA» в другой ячейке, чтобы подсчитать количество непустых ячеек, чтобы использовать их как итог. Используйте формат «=CountA(диапазон)» и используйте тот же диапазон, что и в предыдущей формуле. Например, введите «=CountA(B:B)» в ячейку C2.

Поделите первый результат на второй, чтобы вычислить десятичную пропорцию. Продолжая пример, введите «=C1/C2» в ячейку C3, чтобы рассчитать долю общего объема продаж, относящуюся к продукту A.

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

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

Пример 1. Исправьте таблицу в диапазоне A5:D7 на следующем рисунке так, чтобы сумма каждой из строк была указанной суммой строки, а сумма каждого столбца была указанной суммой столбца.

Рисунок 1. Начало процедуры итеративной пропорциональной подгонки

На рисунке 1 диапазон A5:D7 содержит исходные табличные значения. Диапазон E5:E7 содержит целевые итоги строки, а диапазон A8:D8 содержит целевые итоги столбца.

Для того чтобы процедура работала, сумма итоговых значений целевой строки должна равняться сумме итоговых значений целевого столбца. Это действительно так для примера 1. Общий итог (ячейка E8) содержит значение 60, которое является суммой итоговых сумм целевой строки, а также итоговых сумм целевого столбца; т. е. SUM(E5:E7) = SUM(A8:D8) = 60. Мы также предполагаем, что все записи таблицы, а также целевые итоги строк/столбцов положительны.

Мы начинаем процедуру (шаг 0), помещая начальные значения таблицы в диапазон G5:J7, но на этот раз мы включаем реальные итоги строк и столбцов (на рисунке 1 это называется начальным числом).

Шаг 1 процедуры состоит из двух частей. В первой части значения в таблице корректируются пропорционально, чтобы итоги строк равнялись целевым значениям (Корректировка строк). Например. формула в ячейке A12: =G5*E5/K5.

Во второй части значения в таблице корректируются пропорционально, чтобы итоговые значения столбца равнялись целевым значениям (Корректировка столбца). Например. формула в ячейке G12: =A12*A8/A15.

Теперь эта же процедура повторяется снова и снова, пока сумма и итоги по столбцу не станут достаточно близкими к целевым значениям. В примере 1 это достигается за 8 шагов, как видно на рисунке 2. Фактически, после 8 шагов итоговые значения строк и столбцов в точности равны целевым значениям, поэтому любые дальнейшие итерации будут давать точно такие же значения таблицы.< /p>

Рисунок 2. Процедура итеративной пропорциональной подгонки для примера 1

Конечный результат процедуры показан в правом нижнем углу рисунка 2.

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

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

Рисунок 3. Процедура итеративной пропорциональной подгонки для примера 2

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

Трехсторонние таблицы непредвиденных обстоятельств

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

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

Рисунок 4 содержит таблицу 3 × 2 × 4; т. е. 3 строки, 2 столбца и 4 слоя (мы используем термин слой для третьего измерения). Область синего цвета (диапазон C5:F10) содержит данные для таблицы.

Области, выделенные серым цветом, содержат маргинальные итоги для любых двух измерений: G5:G10 содержит итоги для строк × столбцов, C13:F15 содержит итоги для строк × слоев, а C17:F18 содержит итоги для столбцов × слоев. Так, например, ячейка E14 содержит итог по 2-й строке и 3-му слою, поэтому имеет значение 10. Его можно вычислить в Excel по формуле СУММЕСЛИ(A5:A10,A14,E5:E10).< /p>

Области, выделенные розовым цветом, содержат предельные итоги для любого отдельного измерения: C11:F11 содержит итоги для слоев, G13:G15 содержит итоги для строк, а G17:G18 содержит итоги для столбцов. Например. F11 содержит итоги для 4-го слоя и может быть рассчитан по формуле СУММ(F5:F10). Точно так же G14 содержит итоги для 2-й строки и может быть рассчитан по формуле СУММ(C14:F14).

Наконец, ячейка G11 (заштрихована зеленым) содержит общую сумму, то есть сумму всех значений в таблице. Это можно указать в Excel с помощью любой из следующих формул: СУММ(C5:F10), СУММ(G5:G10) или СУММ(C11:F11).

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

Пример 3. Измените трехфакторную таблицу на рис. 4 (повторяется в левой части рис. 5) так, чтобы ее итоговые значения по строкам, столбцам и слоям соответствовали описанию трехфакторной таблицы в правой части рис. 5. .

Рисунок 5. Спецификация целевых предельных сумм

По сути, мы пытаемся вставить данные из таблицы слева в таблицу справа, сохраняя при этом все итоги для таблицы справа. Теперь мы покажем процедуру IPF для достижения этой цели (см. рис. 6).

Рисунок 6. Шаги 0 и 1 процедуры IPF для примера 3

Шаг 1 процедуры состоит из трех частей. В первой части значения в таблице корректируются пропорционально, чтобы итоговые значения в строке и столбце соответствовали целевым значениям (корректировка строки/столбца). Например. формула в ячейке F25: =V7*G7/W7.

Во второй части значения в таблице корректируются пропорционально, чтобы итоговые значения строки и слоя соответствовали целевым значениям (Корректировка строки/слоя). Например. формула в ячейке L28: =D28*D16/D34.

В третьей части значения в таблице корректируются пропорционально, чтобы итоговые значения столбца и слоя соответствовали целевым значениям (корректировка столбца/слоя). Например. формула в ячейке U29: =M29*E19/M37.

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

Рисунок 7. Решение примера 3

Функции реальной статистики. Ресурсный пакет Real Statistics предоставляет следующие функции обработки массивов для реализации процедур IPF.

IPFP2(R1): выводит результаты алгоритма IPFP для двусторонних таблиц непредвиденных обстоятельств. R1 содержит входные данные и целевые итоги строк и столбцов в формате диапазона A5:E8 на рис. 1. Если R1 представляет собой диапазон m+1 × n+1 тогда выход представляет собой диапазон m × n.

IPFP3(R1, R2): выводит результаты алгоритма IPFP для трехсторонних таблиц непредвиденных обстоятельств. R1 содержит входные данные, отформатированные в диапазоне A5:F10 на рисунке 5. R2 содержит цели, отформатированные в диапазоне K5:N10 на этом рисунке. Формат вывода такой же, как и у диапазона R1.

Наблюдение: для примера 1 результат формулы =IPFP2(B5:F8) показан в диапазоне H5:K7 на рисунке 8.

Рисунок 8. Функция IPFP2

Для примера 3 выходные данные формулы =IPFP3(A5:F10, K5:N10) показаны в диапазоне R5:W10 на рис. 9. Ни один из итогов не включен в диапазоны ввода или вывода.

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

И линейная диаграмма, и диаграмма с областями показывают тенденцию с течением времени.

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

Например, в приведенном ниже случае вы можете использовать линейную диаграмму (вместо диаграммы с областями)

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

Например, предположим, что у меня есть три линейки принтеров (A, B и C).

И я хочу посмотреть, как росли продажи за последние несколько лет, а также какая линейка принтеров внесла вклад в общий объем продаж. Лучше использовать диаграмму с областями.

На приведенной выше диаграмме видно, что принтер B (обозначен оранжевым цветом) вносит максимальный вклад в общий объем продаж.

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

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

В Excel доступно три встроенных типа диаграмм с областями:

  1. Диаграмма с областями
  2. Диаграмма с областями с накоплением
  3. Диаграмма с областями со 100% накоплением

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

Это руководство охватывает:

Создание диаграммы с областями

Это похоже на линейную диаграмму с цветами.

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

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

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

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

В таком случае больше подходит диаграмма с областями с накоплением или диаграмма с областями с накоплением на 100 % (описано далее в этом руководстве).

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

Например, предположим, что у вас есть набор данных, как показано ниже (где я показал общие продажи компании и продажи принтеров):

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

Вот шаги для создания диаграммы с областями в Excel:

Это даст вам диаграмму с областями, как показано ниже:

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

В то же время он также наглядно показывает долю продаж принтеров в общем объеме продаж.

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

Создание диаграммы с областями с накоплением

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

Предположим, у вас есть приведенный ниже набор данных, в котором показаны продажи трех разных товаров (в тысячах):

Вот шаги для создания диаграммы с областями в Excel с этими данными:

Это даст вам диаграмму с областями с накоплением, как показано ниже:

В приведенной выше диаграмме вы получаете следующую информацию:

  1. Общее значение за любой год (например, 1o5 в 2017 году и 130 – в 2018 году).
  2. Общая тенденция, а также тенденция, связанная с продуктом.
  3. Визуальное представление доли продаж каждого продукта в общем объеме продаж. Например, вы можете визуально увидеть и сделать вывод, что продажи проекторов внесли наибольший вклад в общие продажи, а белые доски — в наименьшую.

Создание диаграммы с областями со стопроцентным накоплением

Диаграмма этого типа похожа на диаграмму с областями с накоплением, но с одним отличием: все значения по оси Y равны 100%.

Как показано ниже:

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

Вот шаги, чтобы создать 100%-ную диаграмму с областями с накоплением в Excel:

В приведенной выше диаграмме вы получаете следующую информацию:

  1. Общая тенденция доли каждой линейки продуктов. Например, мы видим, что вклад принтеров в общий объем продаж увеличился в 2018 году, но затем снизился в 2019 году.
  2. Процентный вклад каждой линейки продуктов в общий объем продаж. Например, вы можете визуально сделать вывод, что в 2017 году доля проекторов в общем объеме продаж компании составила примерно 40 %.

Примечание. На этой диаграмме показана тенденция доли общего объема продаж, а НЕ тенденция их абсолютного значения. Например, если доля продаж принтеров в 2018 году выросла, это не обязательно означает, что продажи принтеров выросли. Это просто означает, что его вклад увеличился. Таким образом, если продажи принтеров снизятся на 10 %, а продажи всех других продуктовых линеек снизятся на 30 %, общий вклад продаж принтеров автоматически увеличится (несмотря на снижение собственных продаж).

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