3 простых формулы для расчета диапазона значений в Excel

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

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

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

1. Формулы минимума и максимума

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

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

  • В ячейке B15 введите «= MIN (C2: C13)».
  • В ячейке B16 введите «= MAX (C2: C13)».

Теперь у вас есть быстрый отчет о наименьшем количестве проданных устройств в штате (102 планшета в Айове) и о наиболее проданных (450 ноутбуков в Иллинойсе). р>

2. Формулы верхнего k и нижнего k

Предположим, вас интересует больше данных — не только самый дешевый товар, но и три самых низких. Вы можете найти их с помощью функции НАИМЕНЬШИЙ (). Для использования SMALL () вам потребуются два параметра:

  1. Тот же диапазон или список значений, что и для MIN ().

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

  1. Значение k , что является желаемой позицией снизу списка. Если вы ищете наименьшее значение, тогда k = 1. Чтобы найти второе наименьшее значение, k = 2 и т. Д..

Чтобы найти три наибольших значения, используйте функцию НАИБОЛЬШИЙ () с теми же параметрами, что и НАИМЕНЬШИЙ ().

Обратите внимание, что SMALL () с k = 1 дает точно такой же результат, что и MIN ( ). Аналогично, LARGE () с k = 1 дает то же, что и MAX ().

3. Формулы условного минимума и максимума

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

Excel предоставляет СУММЕСЛИ (), СЧЁТЕСЛИ () и другие полезные условные формулы. К сожалению, здесь нет MINIF () или MAXIF (), но вы можете создать тот же эффект с помощью немного более сложного метода, называемого формулой массива. Формула массива оценивает диапазон ячеек, а не одну ячейку.

Обычно формула IF () проверяет значение истинности отдельной ячейки, но в качестве формулы массива мы можем заставить ее вычислить каждая ячейка в диапазоне. При использовании формулы массива вы получите сообщение об ошибке, если просто нажмете клавишу ввода — #VALUE !. Не забудьте нажать CTRL, SHIFT и ENTER после завершения формулы массива.

Давайте найдем минимальное значение для настольных компьютеров. Сначала введите соответствующее значение (рабочий стол) в ячейку, с которой вы сравниваете свою функцию. Если вы ищете значения рабочего стола, введите «рабочий стол» в B18. Приведенная ниже формула сравнивает эту ссылку на ячейку с тестируемым диапазоном. Вложите инструкции MIN () и IF () следующим образом: «= MIN (IF (B2: B13 = B18, C2: C13))» и нажмите — — .

Три формулы в строках 18, 19 и 20 вычисляют минимальное количество проданные настольные компьютеры, ноутбуки и планшеты. Вы можете написать идентичные формулы с помощью MAX (), чтобы найти наибольшее количество каждого проданного продукта.

Дальнейшие действия

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



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

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

Важно: чтобы преобразовать в диапазон, вы для начала должна быть таблица Excel. Дополнительные сведения см. В разделе Создание или удаление таблицы Excel.

Windows macOS Web
  1. Щелкните в любом месте таблицы и затем перейдите к Инструменты для таблиц > Дизайн на ленте.

  2. В В группе Инструменты щелкните Преобразовать в диапазон .

    -ИЛИ-

    Щелкните правой кнопкой мыши таблицу, затем в контекстном меню нажмите Таблица > Преобразовать в диапазон .

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

  1. Щелкните в любом месте таблицы, а затем щелкните Таблица вкладка.

  2. Щелкните Преобразовать в диапазон .

  3. Нажмите Да , чтобы подтвердить действие.

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

Щелкните таблицу правой кнопкой мыши, затем в контекстном меню выберите Таблица > Преобразовать в диапазон .

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

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новый функция или улучшение в Excel User Voice.

См. также

Видео: создание и форматирование таблиц

Оцените статью
logicle.ru
Добавить комментарий