Формула наименьший в Excel

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

Подсчет количества знаков в диапазоне ячеек

На рисунке ниже представлена формула, которая подсчитывает количество знаков, включая пробелы, в диапазоне A1:A10.

Применение формул массива в Excel

В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функция СУММ — суммирует эти значения.

Наибольшие и наименьшие значения диапазона в Excel

Следующая формула возвращает 3 наибольших значения диапазона A1:D6

Применение формул массива в Excel

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

Применение формул массива в Excel

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

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

Применение формул массива в Excel

Данная формула сравнивает соответствующие значения двух диапазонов. Если они равны, функция ЕСЛИ возвращает ноль, а если не равны — единицу. В итоге получается массив, который состоит из нулей и единиц. Затем функция СУММ суммирует значения данного массива и возвращает результат.

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

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

Применение формул массива в Excel

Транспонировать массив в Excel — значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками.

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

Применение формул массива в Excel

Если изменить форматирование в диапазоне D4:D8, то становится видно, что значения в этих ячейках не округлены, а всего лишь визуально отформатированы. Соответственно, мы не можем быть уверенны в том, что сумма в ячейке D9 является точной.

Применение формул массива в Excel

В Excel существует, как минимум, два способа исправить эту погрешность.

  1. Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:
    Применение формул массива в Excel
  2. Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.
    Применение формул массива в Excel

Теперь мы можем быть уверенными в том, что сумма в ячейке D9 соответствует действительности.

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

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

Применение формул массива в Excel

Пусть в ячейке G3 мы будем задавать фамилию продавца, тогда формула массива будет выглядеть следующим образом:

Применение формул массива в Excel

В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет — ЛОЖЬ. В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция. Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:

Применение формул массива в Excel

Если массив содержит логические значения, то функции МАКС и МИН их игнорируют.

Применение формул массива в Excel

Данная формула позволяет вывести 5 наибольших продаж указанного продавца:

Применение формул массива в Excel

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

  • Знакомство с формулами массива в Excel
  • Многоячеечные формулы массива в Excel
  • Одноячеечные формулы массива в Excel
  • Массивы констант в Excel
  • Редактирование формул массива в Excel
  • Подходы к редактированию формул массива в Excel

Урок подготовлен для Вас командой сайта office-guru.ru Автор: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel

Поделиться:
Нет комментариев

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть