Как сделать соотношение в 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, у нас нет какой-либо конкретной функции, которая могла бы помочь нам рассчитать соотношение.
Но вы можете рассчитать его с помощью некоторых пользовательских формул. Итак, сегодня в этом посте я хочу показать вам, как рассчитать коэффициенты, используя 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.
Все методы, которые мы использовали выше, могут помочь вам рассчитать коэффициенты в различных ситуациях с различными типами значений.
Надеюсь, это поможет вам в вашей работе. А теперь скажи мне одну вещь. Есть ли у вас какой-либо другой метод расчета коэффициента? Пожалуйста, поделитесь со мной в разделе комментариев, я буду рад услышать от вас.
И не забудьте поделиться этим советом с друзьями. Уверен, они это оценят.
Соотношение обычно сравнивает значения. Это помогает нам определить, насколько одно значение меньше или больше другого значения. В математике отношение — это отношение между двумя значениями, показывающее, сколько раз первое значение содержит другое значение.
Это помогает определить сравнения между двумя разными значениями или вещами.
В Excel нет специальной функции для расчета коэффициента. Существует четыре функции для расчета коэффициента в формуле Excel, которые можно использовать в соответствии с требованиями.
Ниже приведены функции для расчета коэффициента в формуле Excel
Как рассчитать коэффициент в Excel?
Давайте узнаем, как рассчитать коэффициент в формуле Excel с помощью нескольких примеров каждой функции.
= значение1/ значение2 &”:“&“1”
Пример простой функции деления
У нас есть данные, в которых есть два значения, и нам нужно будет вычислить отношение двух чисел.
Шаги по вычислению отношения в формуле Excel с помощью функции простого деления показаны ниже:
Формула для функции НОД:
= значение1 / НОД (значение1, значение2) &”:“& значение2 / НОД (значение1, значение2)
Пример функции НОД
У нас есть данные, в которых есть два значения, и нам нужно будет рассчитать отношение двух чисел в Excel.
Этапы расчета отношения в формуле Excel с помощью функции НОД показаны ниже:
Пример SUBSTITUTE и текстовой функции
У нас есть данные, в которых есть два значения, и нам нужно будет вычислить отношение двух чисел.
Шаги по вычислению соотношения с помощью функций ПОДСТАВИТЬ и ТЕКСТ показаны ниже:
Формула для функции округления.
= ОКРУГЛ (значение1/значение2, 1) &“:”& 1
Пример функции округления
У нас есть данные, в которых есть два значения, и нам нужно будет вычислить отношение двух чисел.
Этапы расчета отношения двух чисел с помощью функции ОКРУГЛ показаны ниже:
Что следует помнить при расчете коэффициента в формуле Excel
- Должны быть числовые значения, для которых вы хотите рассчитать соотношение в Excel.
- Оба значения должны быть положительными, а второе не должно быть равно нулю.
- Специальной функции для расчета коэффициента нет; в соответствии с требованием можно использовать любую из функций.
Рекомендуемые статьи
Это руководство по расчету коэффициента в формуле Excel.Здесь мы обсудим, как рассчитать отношение в Excel, используя 1) функцию простого деления 2) функцию НОД 3) функцию замены и текста и 4) функцию округления с примерами и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel –
Читайте также: