Как рассчитать соотношение в Excel
Обновлено: 21.11.2024
В этом посте показано, как рассчитать и отобразить коэффициенты в Excel.
Предположим, вы хотите показать соотношение менеджеров и членов команды или соотношение менеджеров и всех сотрудников.
Нам также необходимо убедиться, что отношение уменьшено до самого простого значения.
Давайте проверим.
Для нашего начального примера мы определили список из 3 менеджеров и 7 членов команды.
Если бы нам нужно было записать соотношение менеджеров и членов команды, выраженное устно, мы бы написали что-то вроде «3 к 7».
Это означает, что на каждые 3 менеджера приходится 7 членов команды.
Более распространенный способ выразить это соотношение — использовать двоеточие ( : ) вместо слова "к".
Мы не хотим писать это статически; мы хотим, чтобы отношение менялось в зависимости от того, что мы поместили в ячейки B3 и B4.
На первый взгляд это может показаться не чем иным, как простой конкатенацией содержимого ячеек.
Можно написать формулу, в которой говорится: "Возьмите содержимое ячейки B3 и соедините (т.е. соедините с) его двоеточием, а затем соедините с ним содержимое ячейки B4".< /p>
Это просто, но как насчет следующего соотношения?
Технически ответ правильный, но большинство людей предпочитают, чтобы результат был сведен к простейшей форме. Другими словами, вместо того, чтобы писать 10:50, мы предпочли бы показать 1:5.
Нам нужно найти наибольший общий множитель и разделить каждое значение на этот множитель.
В приведенном выше примере с использованием 10:50 наибольший общий делитель между 10 и 50 равен 10.
Если мы разделим каждое значение на 10, результат будет 1:5.
Чтобы обнаружить этот делитель, мы будем использовать функцию Excel НОД.
Функция НОД (или наибольший общий делитель) возвращает наибольший общий делитель двух или более целых чисел. Наибольший общий делитель – это наибольшее целое число, на которое без остатка делятся и число1, и число2.
Синтаксис функции следующий:
GCD( число1, [число2], … ) требуется только число1
Вы можете перечислить до 255 значений, и все нецелочисленные значения будут усечены.
В нашем примере мы могли бы написать формулы следующим образом.
Поскольку наши числа находятся рядом друг с другом, любая из этих формул будет работать.
Чтобы уменьшить отношение на этот результат НОД, мы делим каждую часть отношения, используя приведенную выше формулу НОД.
Основы Excel для реального мира — полный курс
Изучите Excel с нуля или заполните пробелы.
Упаковано с заданиями и файлами (Excel 2016, 2019, Office 365).
Одно дело, когда значения соотношения жестко запрограммированы в ячейках, но предположим, что вам нужно вычислить количество менеджеров и членов команды из списка сотрудников?
Наш список сотрудников представляет собой правильную таблицу Excel под названием «Сотрудники».
Мы можем определить количество менеджеров и членов команды в таблице "Сотрудники", используя функцию СЧЁТЕСЛИМН.
Поскольку мы используем правильную таблицу Excel, добавление или удаление строк из таблицы "Сотрудники" будет автоматически отображаться функциями СЧЁТЕСЛИМН для определения новых отношений.
Мы могли бы объединить все промежуточные вычисления в расчет коэффициента, чтобы выполнить это за один шаг…
… но, как видите, это может немного запутать.
Это та формула, которую специалисты по Excel хвастаются своим друзьям на «встречах», но также и та формула, от которой обычные пользователи бегут от крика в состоянии паники.
С точки зрения производительности мы выполняем одни и те же вычисления несколько раз; каждый СЧЁТЕСЛИМН для определения количества Менеджеров выполняется 3 раза и то же самое для СЧЁТЕСЛИМН для определения Членов Команды.
Именно здесь функция LET может придать вашим вычислениям элегантность и скорость.
Если вы не знакомы с новой функцией LET в Excel, ознакомьтесь с этим постом и видео, демонстрирующим эту замечательную новую функцию.
Новая функция LET в Excel (упрощение формул)
Чтобы использовать функцию LET для создания этого отношения, мы начинаем с запуска функции LET.
Затем мы создаем имя для формулы, функции или ссылки, которую хотим использовать повторно.
Поскольку мы вычисляем левую и правую стороны соотношения, мы будем вызывать формулы, определяющие эти значения «Левая_сторона» и «Правая_сторона».
Чтобы определить «Левую_сторону», мы объявляем имя ссылки («Левая_сторона») и формулу, которая будет выполняться при вызове («СЧЁТЕСЛИМН(Сотрудники[Роль], A3)»)
Теперь мы делаем то же самое для определения имени и формулы для «Правой_стороны» коэффициента.
Наконец, мы напишем формулу, которая будет использовать эти именованные формулы для расчета коэффициента. В этой формуле будут использоваться результаты "Левая_сторона" и "Правая_сторона" вместе с формулами НОД из предыдущих версий.
Если это поможет, вот как формула выглядит на панели формул Excel (примечание: я использую сочетание клавиш Alt-Enter, чтобы встроить возврат каретки в ячейку, чтобы формулу было легче читать.)
Может показаться, что работы больше, чем раньше (только немного больше работы), но окупаемость заключается в том, что каждую формулу СЧЁТЕСЛИМН нужно вычислять только один раз. Затем результаты повторно используются в формуле всякий раз, когда это требуется.
«Скорость деформации, мистер Скотт.
Если вы относитесь к тому типу пользователей, которые хотят выжать из своих формул каждую каплю производительности (и ваша цель — гарантия занятости), вы можете продвинуть функцию LET на шаг вперед. и создайте именованную ссылку для части формулы, относящейся к НОД.
Функция НОД используется для вычисления наибольшего общего знаменателя между двумя или более значениями. Мы можем использовать GCD, чтобы затем создать наши коэффициенты.
Чтобы использовать функцию GCD, вам может потребоваться включить пакет инструментов анализа данных. Чтобы проверить, нужно ли вам включить пакет инструментов анализа данных, введите =gcd( в ячейку Excel. Если вы видите, что появляются входные данные функции GCD:
Вы готовы начать. В противном случае выполните следующие действия:
Для Mac
• Нажмите «Инструменты». (Находится в самом верху, где у вас есть «Файл» и «Редактировать».)
• Нажмите «Надстройки Excel».
• Должна появиться небольшая всплывающая вкладка. Убедитесь, что выбран пакет инструментов анализа.
• Выберите ОК.
• Пакет инструментов теперь должен быть доступен на вкладке «Данные» под заголовком «Анализ данных». Если вы его не видите, возможно, вам придется перезагрузить страницу и проверить еще раз.
Для ПК
• Нажмите кнопку «Файл»
• Найдите и выберите «Параметры». (Он расположен в нижней части.)
• С левой стороны нажмите «Надстройки».
• В разделе «Неактивные приложения» найдите и выберите «Пакет анализа»
• Нажмите «Перейти».
• Новое окно. Должен появиться заголовок «Надстройки». Выберите пакет инструментов анализа.
• Нажмите кнопку "ОК".
• Пакет инструментов теперь должен быть доступен на вкладке "Данные" под заголовком "Анализ данных".
Использование функции НОД
Функция GCD чрезвычайно проста в использовании. Просто введите =GCD(, а затем числа, для которых вы хотите найти наибольший общий делитель (разделенные запятыми).
=GCD(Число 1, Число 2)
Например, наша первая строка будет рассчитана как: =GCD(B3,C3) с ответом 5.
После того, как вы поймете, как работает НОД, вы сможете использовать эту функцию, чтобы найти отношения, разделив каждое из двух чисел на их НОД и разделив ответы двоеточием «:». Имейте в виду, что ответ будет текстовым значением. .
Формула соотношения:
= Num1/GCD(Num1, Num2) & “:” & Num2/GCD(Num1, Num2)
Эта сложная формула в основном говорит:
= (Количество раз, когда число 1 содержит НОД) & «:» & (Сколько раз число 2 содержит НОД)
В нашем примере формула будет записана так:
В результате получается:
=100/5 & ":" & 15/5
= 20 & ":" & 3
= 20:3
Вычислить соотношение — метод текста и подстановки
Еще один метод расчета коэффициентов – метод текста и подстановки. Этот метод не требует установки пакета анализа.
Функция «Текст» позволяет отображать ответ в виде текстового значения. В нашем случае число 1 будет делиться на число 2, но вместо десятичного значения ответ будет отображаться в виде дроби, например 5/4 или 9/2.
Формула дроби:
Чтобы отобразить дробь в желаемом формате отношения, вам придется заменить косую черту двоеточием. Это потребует использования функции замены.
Соотношение. В простой математике отношение или сравнение между двумя другими числами называется отношением. Соотношения обычно записываются как «:», например, чтобы показать связь между двумя числами. Вы можете изучить такое расширенное руководство по Excel в нашем расширенном курсе по панели инструментов Excel.
Если на столе 2 красных и 3 синих шара, вы можете записать их соотношение как [2:3]. К сожалению, не существует систематического способа расчета коэффициента, но есть простой способ сделать то же самое. Вы можете воспользоваться приведенными ниже рекомендациями для расчета коэффициента в Excel.
Как рассчитать коэффициент в Excel.
Формула расчета отношения: чтобы рассчитать соотношение в Excel, магазин 1 будет разделен на НОД, а магазин 2 будет разделен на НОД. Вы можете поставить двоеточие между этими двумя числами. Пример. Чтобы увидеть соотношение, введите эту формулу в ячейку E2 = B2/GCD(B2,C2)&”:”&C2/GCD(B2,C2).
Рассчитать соотношение товаров между двумя магазинами
Шаг 1. Создайте таблицу, как показано на рисунке выше. В этой таблице показано количество товаров в магазине двух магазинов. Мы собираемся рассчитать соотношение каждого продукта между этими двумя магазинами. Столбец D предназначен для расчета НОД, а столбец E — для соотношения.
Что такое НОД?
Функция НОД используется для вычисления наибольшего общего знаменателя между двумя или более значениями. Мы можем использовать GCD, чтобы затем создать наши отношения. Функция НОД чрезвычайно проста в использовании. Просто введите =GCD(, а затем числа, для которых вы хотите найти наибольший общий делитель (разделенные запятыми).
Формула: =НОД(Число 1, Число 2)
После того, как вы поймете, как работает НОД, вы сможете использовать эту функцию, чтобы найти отношения, разделив каждое из двух чисел на их НОД и разделив ответы двоеточием «:». Следовательно, теперь мы готовы к тому, как рассчитать коэффициент.
Сначала нам нужно рассчитать НОД в столбце D, затем мы рассчитаем отношение в столбце Е, используя НОД.
Шаг 2: В ячейке D2 введите эту формулу для расчета НОД между B2 и C2. =НОД(B2,C2)
Шаг 3. Теперь у нас есть результат 5 для ячеек B2 и C2. Итак, теперь скопируйте эту формулу в другие ячейки столбца с помощью ручки заполнения, перетащив ее ниже, в другие ячейки столбца D. Шаг 4: Теперь у нас есть все значения НОД всех строк в столбце D. Теперь давайте рассчитаем соотношение.
Формула: = Num1/GCD(Num1, Num2) & “:” & Num2/GCD(Num1, Num2)
Эта сложная формула в основном говорит:
= (Количество раз, когда число 1 содержит НОД) & “:” & (Сколько раз число 2 содержит НОД)
В нашем примере формула будет записана так:
=B2/GCD(B2,C2) & ”:” & C2/GCD(B2,C2)
или
=B2/D2 &”:”& C2/D2
(Поскольку мы уже вычислили НОД (B2,C2) в ячейке D2.
Итак, мы можем сказать, что D2= GCD(B2,C2))
Результаты: = 100/5 & ":" & 15/5 = 20 & ":" & 3 = 20:3
Шаг 5. В ячейке E2 введите следующую формулу для расчета коэффициента:
Нажмите ВВОД. Теперь у нас есть соотношение между 100 и 15 как 20:3 в ячейке E2.
Шаг 6. Скопируйте формулу отношения в другую ячейку столбца E. Наконец, мы получили соотношение для всей таблицы. Итак, мы успешно научились считать коэффициент в excel.
Отношение — это один из распространенных методов сравнения двух значений друг с другом. И это может помочь вам сравнить различия между этими двумя значениями.
Даже в повседневной жизни мы используем слово ОТНОШЕНИЕ для определения сравнения двух разных вещей.
Что касается Excel, у нас нет какой-либо конкретной функции, которая могла бы помочь нам рассчитать соотношение.
Но вы можете рассчитать его с помощью некоторых пользовательских формул. Итак, сегодня в этом посте я хочу показать вам, как рассчитать коэффициенты, используя 4 разных способа.
Давайте начнем и обязательно загрузите этот образец файла отсюда, чтобы следовать дальше.
1. Вычислить соотношение с помощью простого метода деления
Этот метод можно использовать, когда большее значение делится на меньшее. В приведенном ниже примере у нас есть значения 10 и 2, где 10 делится на 2.
=A2/B2&”:”&”1″
вот как работает эта формула...
В этой формуле мы разделили 10 на 2, получив взамен 5. Итак, теперь у нас есть 5 вместо 10 при делении на 2. А с другой стороны, мы использовали 1 вместо 2.
2. Функция НОД для расчета отношения в Excel
Как я уже сказал, в Excel нет единой функции для расчета коэффициента. Но функция НОД достаточно близка.
Это может помочь вам получить общий номинал для обоих значений, а затем, используя небольшую конкатенацию, вы можете рассчитать отношение.
=A2/GCD(A2,B2)&”:”&B2/GCD(A2,B2)
вот как работает эта формула...
Возможно, вам это кажется сложным, но на самом деле все просто. Позвольте мне объяснить это в двух частях.
Как я уже сказал, НОД может дать вам общий знаменатель для обоих значений. Так что здесь вы должны использовать НОД в обеих частях формулы, чтобы получить общий знаменатель.
После этого вам нужно разделить оба значения с этим общим знаменателем. И, наконец, небольшая конкатенация, чтобы соединить оба значения с помощью двоеточия.
3. ПОДСТАВИТЬ и ТЕКСТ для расчета коэффициента
Сочетание двух замечательных функций.
Да, вы можете использовать функции ПОДСТАВИТЬ и ТЕКСТ для расчета соотношения. Этот метод работает как шарм, как и функция GCD. Здесь у нас есть приведенные ниже значения для расчета коэффициента.
вот как работает эта формула...
Эта формула также работает в двух разных рабочих частях с ТЕКСТ и ПОДСТАВИТЬ.
Во-первых, вы должны использовать текстовую функцию, чтобы разделить оба числа, а затем отформатировать возвращаемое значение в дробном формате.
Во-вторых, разделив оба полученных значения на 10, вы преобразуете это значение в дробную часть и получите 10/1.
В-третьих, замените косую черту двоеточием. И у нас есть заменяющая функция для этого.
4. Вычислить отношение с функцией округления
Использование функции округления для расчета коэффициента также является полезным методом. Это особенно полезно, когда вы хотите рассчитать отношения с десятичными дробями для точного сравнения.
Здесь у нас есть значения, в которых большее значение не делится с меньшим значением. Таким образом, в этой ситуации вместо того, чтобы использовать их как есть, вы можете разделить их и показать окончательное соотношение с десятичными дробями.
=КРУГЛЫЙ(A2/B2,1)&”:”&1
А вот и отношение с десятичными дробями.
вот как работает эта формула...
Вы можете разделить эту формулу на две разные части, чтобы понять ее.
Прежде всего, вы должны использовать функцию округления, чтобы разделить большее значение на меньшее и получить результат с одним десятичным знаком.
Во-вторых, вы должны использовать двоеточие и "1" в конце.
Пример файла
Заключение
Как я уже сказал, соотношение — это полезный метод сравнения двух значений друг с другом. И теперь у вас есть разные методы расчета в вашем любимом приложении, да, в Excel.
Все методы, которые мы использовали выше, могут помочь вам рассчитать коэффициенты в различных ситуациях с различными типами значений.
Надеюсь, это поможет вам в вашей работе. А теперь скажи мне одну вещь. Есть ли у вас какой-либо другой метод расчета коэффициента? Пожалуйста, поделитесь со мной в разделе комментариев, я буду рад услышать от вас.
И не забудьте поделиться этим советом с друзьями. Уверен, они это оценят.
Читайте также: