Как в excel выбрать большее значение из двух ячеек
Если вы еще не Excel в Интернете, скоро вы увидите, что это не просто сетка для ввода чисел в столбцах или строках. Да, с помощью Excel в Интернете можно найти итоги для столбца или строки чисел, но вы также можете вычислять платежи по ипотеке, решать математические или инженерные задачи или находить лучшие сценарии в зависимости от переменных чисел, которые вы подключали.
Excel в Интернете делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак "плюс" или "минус") и функции, которые значительно расширяют возможности формулы.
Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.
Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:
Ниже приведены примеры формул, которые можно использовать на листах.
=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.
=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.
=СЕГОДНЯ() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.
=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.
Элементы формулы
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.
2. Ссылки. A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
4. Операторы. Оператор ^ ("крышка") применяется для возведения числа в степень, а оператор * ("звездочка") — для умножения.
Использование констант в формулах
Использование операторов в формулах
Операторы определяют операции, которые необходимо выполнить над элементами формулы. Вычисления выполняются в стандартном порядке (соответствующем основным правилам арифметики), однако его можно изменить с помощью скобок.
Типы операторов
Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Арифметические операторы
Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение, вычитание, умножение, деление или объединение чисел. Результатом операций являются числа. Арифметические операторы приведены ниже.
Арифметический оператор
Возведение в степень
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
Меньше или равно
Текстовый оператор конкатенации
Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.
Текстовый оператор
Соединение или объединение последовательностей знаков в одну последовательность
Выражение «Северный»&«ветер» дает результат «Северный ветер».
Операторы ссылок
Для определения ссылок на диапазоны ячеек можно использовать операторы, указанные ниже.
Оператор ссылки
Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку.
Оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов.
Порядок выполнения Excel в Интернете формулах
В некоторых случаях порядок вычисления может повлиять на возвращаемое формулой значение, поэтому для получения нужных результатов важно понимать стандартный порядок вычислений и знать, как можно его изменить.
Порядок вычислений
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет операторов
Если в одной формуле несколько операторов, Excel в Интернете выполняет операции в том порядке, который показан в таблице ниже. Если формула содержит операторы с одинаковым приоритетом, например операторы деления и умножения, Excel в Интернете эти операторы оцениваются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.
Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.
Использование функций и вложенных функций в формулах
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.
Синтаксис функций
Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.
1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.
2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.
4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. При вводе функции в формулу в диалоговом окне Вставить функцию отображаются имя функции, все ее аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " ocpSection" role="region" aria-label="Вложенные функции">
Вложенные функции
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 50 используется вложенная функция СРЗНАЧ.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает Excel в Интернете, где искать значения или данные, которые вы хотите использовать в формуле. С помощью ссылок в одной формуле можно использовать данные, которые находятся в разных частях листа, а также значение одной ячейки в нескольких формулах. Вы также можете задавать ссылки на ячейки разных листов одной книги либо на ячейки из других книг. Ссылки на ячейки других книг называются связями или внешними ссылками.
Стиль ссылок A1
Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.
Ячейка или диапазон
Использование
Ячейка на пересечении столбца A и строки 10
Диапазон ячеек: столбец А, строки 10-20.
Диапазон ячеек: строка 15, столбцы B-E
Все ячейки в строке 5
Все ячейки в строках с 5 по 10
Все ячейки в столбце H
Все ячейки в столбцах с H по J
Диапазон ячеек: столбцы А-E, строки 10-20
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.
Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.
Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.
Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.
Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.
Стиль ссылок R1C1
Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.
относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
Относительная ссылка на строку, расположенную выше текущей ячейки
Абсолютная ссылка на текущую строку
При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку "Автоумма"), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.
Использование имен в формулах
Можно создавать определенные имена для представления ячеек, диапазонов ячеек, формул, констант и Excel в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
Как выбрать меньшее или большее значение двух ячеек в Excel?
Предположим, есть два списка чисел, и вы хотите быстро выбрать меньшее или большее из них, что вы можете сделать? В этой статье представлены несколько простых формул, которые помогут вам быстро это сделать.
Выберите меньшее или большее значение двух ячеек с формулами
Чтобы выбрать меньшее или большее значение между двумя ячейками в Excel, сделайте следующее.
Выберите меньшее значение между двумя ячейками
Синтаксис
аргументы
- Number1: Это может быть число, ссылка на число или диапазон, содержащий числовые значения.
- Number2 (Необязательно): это может быть число, ссылка на число или диапазон, содержащий числовые значения.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=MIN(A2,B2)
2. Затем перетащите Ручка заполнения вниз, чтобы получить все более низкие значения.
Ноты:
- В формуле A2 и B2 - это ячейки, из которых вы выберете меньшее значение;
- Эта формула также может помочь: =IF(A2
Выберите большее значение между двумя ячейками
Синтаксис
аргументы
- Номер 1: Это может быть число, ссылка на число или диапазон, содержащий числовые значения.
- Number2 (Необязательно): это может быть число, ссылка на число или диапазон, содержащий числовые значения.
1. Выберите черную ячейку, скопируйте в нее одну из формул ниже и нажмите Enter ключ. А затем перетащите Ручка заполнения вниз, чтобы получить все более высокие значения.
=MAX(A2,B2)
=IF(A2>B2,A2,B2)
Статьи по теме
Найдите максимальное или минимальное значение на основе критериев в Excel
Предположим, у вас есть диапазон данных, столбец A содержит названия продуктов, а столбец B - объемы заказа. Теперь вы хотите найти максимальную стоимость заказа продукта KTE. Как в Excel извлечь максимальное или минимальное значение на основе одного или нескольких критериев? Методы, описанные в этом руководстве, могут оказать вам услугу.
Найдите максимальное или минимальное значение в группе в Excel
Как правило, поиск максимального или минимального значения в диапазоне может быть легким для большинства пользователей Excel, но как насчет поиска максимального или минимального значения в каждой группе? В этом руководстве рассказывается о приемах поиска максимального или минимального значения в группе в Excel.
Вычислить среднее значение без максимальных и минимальных значений в Excel
Как мы все знаем, обычная функция Average будет получать среднее значение всех выбранных ячеек в Excel. Но здесь вы хотите рассчитать среднее значение, исключая самые высокие и самые низкие значения из выбранных чисел, как вы могли бы решить эту проблему? Пожалуйста, попробуйте методы, описанные в этом руководстве.
Предположим, у вас есть диапазон данных, включая положительные и отрицательные значения, и здесь я расскажу о некоторых способах найти наименьшее положительное значение (которое больше 0) в Excel.
Найдите минимальное значение больше нуля с помощью формулы
Найдите и выберите минимальное значение больше нуля с помощью Kutools for Excel
Найдите и выберите минимальное значение больше нуля с помощью формулы
В Excel вы можете использовать формулу массива для поиска наименьших положительных значений.
Выберите a пустую ячейку и введите в нее эту формулу = MIN (IF (A1: E10> 0, A1: E10)) и введите Shift + Ctrl + Enter , чтобы получить наименьшее положительное значение в указанный диапазон данных.
Совет. В формуле A1: E10 — это диапазон данных, из которого вы хотите найти наименьшее положительное значение.
Найдите минимальное значение больше нуля с помощью Kutools for Excel
Если вы плохо запоминаете формулы, вы можете использовать Kutools for Excel утилиту Select Specific Cells и Выберите утилиту «Ячейки с максимальным и минимальным значением , чтобы найти и выбрать минимальное положительное значение.
После бесплатной установки Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных и нажмите Kutools > Выбрать > Выбрать определенные ячейки . См. Снимок экрана:
2. Затем в диалоговом окне Выбрать определенные ячейки установите флажок Ячейка в разделе Тип выбора , выберите Меньше, чем в разделе Определенный тип и введите 0 в следующее поле. Нажмите ОК > ОК , чтобы закрыть диалоговое окно. Затем выбираются все отрицательные числа. см. снимки экрана ниже:
3. Нажмите клавишу Delete , чтобы удалить все выбранные отрицательные значения. См. Снимок экрана:
4. Затем выберите положительные значения и нажмите Kutools > Выбрать > Выбрать ячейки с максимальным и максимальным значением . См. Снимок экрана:
5. Затем в диалоговом окне Выбрать ячейки с максимальным и минимальным значением выберите тип ячейки, в которой вы хотите выполнить поиск, и отметьте параметр Минимальное значение и выберите, чтобы найти все ячейки или только первая появляющаяся. Затем нажмите ОК . См. Снимок экрана:
С помощью Выбрать конкретный Ячейки , вы можете выбирать целые строки или столбцы на основе определенных значений. Нажмите здесь, чтобы узнать больше о выборе определенных ячеек.
С помощью Выбрать ячейки с максимальным и минимальным значением вы можете выбрать минимальное или максимальное значение в ячейках формулы , ячейки значений или ячейки формулы и значения, и вы можете выбрать первую отображаемую ячейку или все минимальные или максимальные ячейки. Нажмите здесь, чтобы узнать больше о Kutools for Excel, выберите ячейки с максимальным и минимальным значением.
Например, у меня есть следующие два столбца, столбец A — это некоторые проекты, а столбец B — соответствующие имена. И здесь у меня есть несколько случайных проектов в столбце D, теперь я хочу вернуть соответствующие имена из столбца B на основе проектов в столбце D. Как вы могли сравнить два столбца A и D и вернуть относительные значения из столбца B в Excel?
Сравнить два столбца и вернуть значение из третьего столбца с функцией ВПР
Сравните два столбца и верните значение из третьего столбца с помощью функций ИНДЕКС и ПОИСКПОЗ
Vlookup для нескольких столбцов и верните соответствующие значения с помощью функций ИНДЕКС и ПОИСКПОЗ
Сравните два столбца и возвращаемое значение из третьего столбца с полезной функцией
Сравнить два столбца и вернуть значение из третьего столбца с помощью функции ВПР
Функция ВПР может помочь вам сравнить два столбца и извлечь соответствует ng значений из третьего столбца, сделайте следующее:
1 . Введите любую из двух формул ниже в пустую ячейку, кроме сравниваемого столбца, E2 для этого экземпляра:
= ЕСЛИОШИБКА (ВПР (D2, $ A $ 2: $ B $ 16,2, FALSE), «») (если значение не найдено, пустая ячейка отображается)
Примечание. В приведенных выше формулах: D2 — это ячейка критериев, на основе которой вы хотите вернуть значение, — это столбец, содержащий критерии для сравнения, A2: B16 диапазон данных, который вы хотите использовать.
2 . Затем нажмите клавишу Enter , чтобы получить первое соответствующее значение, а затем выберите ячейку формулы и перетащите маркер заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, и все соответствующие значения были возвращается сразу, см. снимок экрана:
Сравните два столбца и возвращаемое значение из третьего столбца с помощью функций ИНДЕКС и ПОИСКПОЗ
В Excel функции ИНДЕКС и ПОИСКПОЗ также могут помочь вам решить эту задачу, сделайте следующее:
1 . Введите любую из двух формул ниже в пустую ячейку, в которую вы хотите вернуть результат:
= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 16, ПОИСКПОЗ (D2, $ A $ 2: $ A $ 16,0)), «») (если значение не найдено, отображается пустая ячейка)
Примечание. В приведенных выше формулах: D2 — это значение, относительное значение которого вы хотите вернуть, — это список, содержащий нужное вам значение. чтобы вернуться, — это столбец, который вы ищете.
2 . Затем нажмите клавишу Enter , чтобы получить первое соответствующее значение, а затем выберите ячейку формулы и скопируйте в остальные ячейки, которые вам нужны, и все соответствующие значения будут возвращены, см. Снимок экрана:
Если вам интересно в функции ВПР в Excel, Kutools for Excel Super LOOKUP поддерживает несколько мощных формул Vlookup, вы можете быстро выполните функцию Vlookup, не запоминая никаких формул. Нажмите, чтобы загрузить Kutools for Excel!
Vlookup несколько столбцов и возврат соответствующих значений с помощью функций ИНДЕКС и ПОИСКПОЗ
Иногда у вас может быть диапазон данных, содержащий три столбца, теперь вы хотите найти в таблице соответствие двум значениям критериев, если оба эти значения совпадают, он вернет данные из третьего столбца C.
Для выполнения этой работы примените следующую формулу:
Примечание. В приведенных выше формулах: E2 , F2 — это ячейки критериев, которые вы хотите вернуть. значение, основанное на, — это столбец, содержащий значения, которые вы хотите вернуть, A2: A16 , — это те столбцы, которые вам нужны.
Затем нажмите Ctrl + Shift + Enter клавиши вместе, чтобы получить первый результат, см. снимок экрана
А затем скопируйте и заполните этот массив в другие ячейки, и вы получите следующий результат:
Сравните два столбца и возвращаемое значение из третьего столбца с полезной функцией
Kutools for Excel ‘s Найдите значение в списке также может помочь вам вернуть соответствующие данные из другого диапазона данных.
Примечание. Чтобы применить это Найдите значение в списке , во-первых, вы должны загрузить Kutools for Excel , а затем быстро и легко примените эту функцию.
После установки Kutools for Excel сделайте следующее:
1 . Щелкните ячейку, в которую вы хотите поместить совпадающий результат.
2 . Затем нажмите Kutools > Помощник по формулам > Помощник по формулам , см. снимок экрана:
3 . В Помощнике по формулам диалоговое окно, выполните следующие операции:
- В раскрывающемся списке Тип формулы выберите параметр Поиск ;
- Затем выберите Искать значение в списке в поле списка Выбрать формулу ;
- А затем в Ввод аргументов , выберите диапазон данных, ячейку критериев и столбец, из которых вы хотите вернуть совпадающее значение отдельно.
. Затем нажмите ОК , и будут возвращены первые совпавшие данные на основе определенного значения. Вам просто нужно перетащить маркер заполнения, чтобы применить эту формулу к другим нужным ячейкам, см. Снимок экрана:
Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!
Функции МАКС и МИН прекрасно подходят для поиска наибольшего и наименышего значения в диапазоне данных. Но если нужно найти несколько максимальных или минимальных значений для этой задачи Excel предлагает несколько интересных функций: НАИБОЛЬШИЙ, НАИМЕНЬШИЙ, РАНГ и РАНГ.СР.
Пример выборки нескольких наибольших значений в Excel
Ниже на рисунке представлены результаты турниру по боулингу. Участники в турнирной таблице должны быть отсортированы по возрастанию по имени. Сложно проверить, который из них является победителем. Допустим нам необходимо узнать, которые участники заняли первые 3 места и какие их результаты. Ниже указанная формула, которая возвращает третье наибольшее число со списка результатов:
В формуле использованной для поиска имени участника используется комбинация функций ИНДЕКС и ПИСКПОЗ:
Функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ используются для поиска порядкового наибольшего и наименьшего значения в списке данных. В первом аргументе функции НАИБОЛЬШИЙ, как и в функции МАКС указывается ссылка на исходный диапазон просматриваемых значений. Однако отличие между ними заключается в том, что функция НАИБОЛЬШИЙ имеет еще один обязательный для заполнения аргумент «К» в котором можно указать порядковый номер возвращаемого наибольшего значения (второе наибольшее, третье… и т.д.).
Если исходный диапазон данных содержит одинаковые максимальные значения, тогда функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращает тот самый результат для порядковых максимальных и минимальных значений (K и K+1). Например, если бы два участника получили одинаковый результат по 588 пунктов каждый, тогда формулы =НАИБОЛЬШИЙ($B$2:$B$13;1) и =НАИБОЛЬШИЙ($B$2:$B$13;2) возвращали бы одно и тоже число 558.
В ячейке E2 используется функция СТРОКА(A1) определяющая значение аргумента «К». Функция строка возвращает номер строки листа для ячейки, указанной в аргументе – в данном примере строка №1. Как второй аргумент функции НАИБОЛЬШИЙ можно было бы просто указать число 1, однако благодаря функции СТРОКА(A1) номер строки листа будет увеличиваться автоматически во время копирования формулы в очередные ячейки в низ от начальной. Ссылка на ячейку A1 является относительной, когда потом формула будет скопирована в ячейку E3, функция будет уже иметь следующий вид: СТРОКА(A2). С таким аргументом функция уже будет возвращать значение 2, а функция НАИБОЛЬШИЙ в ячейке E3 возвратит второе наибольшее значение из столбца «Результат» в исходном списке данных.
Формула ранжирования в Excel для выборки наибольших значений
В данном примере принципиальным является использование функции НАИБОЛЬШИЙ, так как мы анализируем результаты игры в боулинг. А в боулинге чем больше число пунктов, тем лучше результат. Если же бы мы анализировали другой вид спорта где вместо балов был список результатов времени в турнирной таблице, то в таком случае победителем будет тот участник, у которого наименьшее время. Турнирная таблица с временными результатами изображена ниже на рисунке. Таблица содержит дополнительный столбец с номером места каждого участника, а в нем использована формула следующая:
Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.
В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.
Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:
Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:
Функция РАНГ.СР для ранжирования списка с повторяющимися значениями
В программе Excel версии 2010 и старше появились две новые функции ранжирования значений: РАНГ.РВ и РАНГ.СР, в которых по-разному решается проблема с одинаковыми значениями в диапазоне ячеек. Формула РАНГ.РВ – это аналог функции РАНГ, она возвращает тот же самый результат при таких же условиях с теми же самыми одинаковыми ячейками. А вот функция РАНГ.СР возвращает средний результат вычисления для всех одинаковых значений.
Допустим в таблице находятся результаты четырех участников с одинаковым вторым наименьшим показателем времени 21:38:
Для наилучшего времени в данной таблице функция РАНГ.СР естественно возвращает номер 1, а для четырех последующих наилучших показателей времени возвращено дробное значение 3,5. Эти четыре участника одновременно занимают места: 2, 3, 4 и 5, в итоге все места разделены между ними поровну, поэтому каждый участник получил по 3,5 балла. То есть 2+3+4+5/4=3,5.
Читайте также: