Выбрать 10 максимальных значений эксель
есть 2 колонки значения и характеристика:
хар. 1. 10
хар. 2. 100
хар. 3. 250
хар. 4. 5
хар. 5. 3
хар. 6. 80
хар. 7. 79
Резюме: необходимо три максимальных значения и соответствующие им характеристики из первой колонки.
хар. 3 ? 250 - формула макс. стоит
хар. 2 ? 100 - как поставить следующее не могу найти в справке
хар. 6 ? 80 - ?
Рылась в справке, но пока не нашла решение.
есть 2 колонки значения и характеристика:
хар. 1. 10
хар. 2. 100
хар. 3. 250
хар. 4. 5
хар. 5. 3
хар. 6. 80
хар. 7. 79
Резюме: необходимо три максимальных значения и соответствующие им характеристики из первой колонки.
хар. 3 ? 250 - формула макс. стоит
хар. 2 ? 100 - как поставить следующее не могу найти в справке
хар. 6 ? 80 - ?
Рылась в справке, но пока не нашла решение. tati_2000
хар. 1. 10
хар. 2. 100
хар. 3. 250
хар. 4. 5
хар. 5. 3
хар. 6. 80
хар. 7. 79
Резюме: необходимо три максимальных значения и соответствующие им характеристики из первой колонки.
хар. 3 ? 250 - формула макс. стоит
хар. 2 ? 100 - как поставить следующее не могу найти в справке
хар. 6 ? 80 - ?
Рылась в справке, но пока не нашла решение. Автор - tati_2000
Дата добавления - 17.01.2014 в 11:00
Спасибо, с числами получилось. Сейчас пытаюсь привязать ячейки с соответствующими числам характеристиками
Спасибо, с числами получилось. Сейчас пытаюсь привязать ячейки с соответствующими числам характеристиками tati_2000
Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
В данном случае в СМЕЩ() ширину и высоту диапазона можно не указывать.
ПОИСКПОЗ() (если не находит нужного) и НАИБОЛЬШИЙ() будут лопатить весь столбец. Вам оно надо?
Задавать столбец можно сразу:
В данном случае в СМЕЩ() ширину и высоту диапазона можно не указывать.
ПОИСКПОЗ() (если не находит нужного) и НАИБОЛЬШИЙ() будут лопатить весь столбец. Вам оно надо?
Задавать столбец можно сразу:
Как я понимаю, сделано ради интереса, т.к. функция летуча и предложенный вариант с ИНДЕКС() лучше. vikttur
Как я понимаю, сделано ради интереса, т.к. функция летуча и предложенный вариант с ИНДЕКС() лучше. Автор - vikttur
Дата добавления - 17.01.2014 в 13:17
абсолютно точно.если честно,то это практически первая моя формула со СМЕЩ.ну не получалось раньше.спасибо за совет
абсолютно точно.если честно,то это практически первая моя формула со СМЕЩ.ну не получалось раньше.спасибо за совет китин
абсолютно точно.если честно,то это практически первая моя формула со СМЕЩ.ну не получалось раньше.спасибо за совет Автор - китин
Дата добавления - 17.01.2014 в 13:26
Извиняюсь за формат xlsx , на автомате. Получилось. с индексом, счас со столбцом попробую. Точность значения надо было просто добавить , никак не работало.
Извиняюсь за формат xlsx , на автомате. Получилось. с индексом, счас со столбцом попробую. Точность значения надо было просто добавить , никак не работало.
Спасибо прогромное Автор - tati_2000
Дата добавления - 17.01.2014 в 14:40
Вот доллары как раз давали ошибку, а без нуля не считал. Видимо новый офис синтаксис несколько другой имеет.
Вот доллары как раз давали ошибку, а без нуля не считал. Видимо новый офис синтаксис несколько другой имеет.
Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ в Excel являются противоположными по своему смыслу и используются для определения соответственно наибольшего и наименьшего числового элемента в массиве данных.
Примечание: В Excel массивом является набор данных, представленный в виде единого объекта (например, диапазон ячеек). Массив в Excel может быть принят в качестве аргумента.
Особенности условий в функциях НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращают k-е максимальное и минимальное значения соответственно в выбранном массиве данных. Данные функции применяют для поиска значений, которые занимают определенное относительное положение в множестве данных.
Примечание: для простого поиска наименьшего и наибольшего значений в диапазоне данных принято использовать функции МИН и МАКС, принимающие единственный параметр на вход – диапазон данных. НАИБОЛЬШИЙ и НАИМЕНЬШИЙ предлагают расширенный функционал для поиска 1-го, 2-го… k-го наибольшего/наименьшего значений в массиве.
Обе функции имеют схожий синтаксис, поэтому не будем рассматривать его отдельно для каждой функции. Рассмотрим синтаксис для НАИБОЛЬШИЙ:
- Массив – диапазон либо массив числовых значений, для которого вычисляется k-е наибольшее значение. Является обязательным аргументом.
- K – аргумент, указывающий на позицию в наборе данных или массиве начиная с наименьшего значения. Также является обязательным аргументом функции.
Примеры работы в Excel с функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Пример 1. В конструкторском отделе предприятия работают 8 инженеров. Необходимо определить четвертую наибольшую и наименьшую зарплаты соответственно.
Внесем данные в таблицу:
Для определения наименьшей 4-й зарплаты в отделе введем следующую формулу в ячейку C2:
Аргументами данной функции являются:
- B3:B10 – массив значений заработной платы для всех сотрудников;
- 4 – порядок искомого наименьшего значения в массиве.
Чтобы определить наибольшую 4-ю зарплату вводим формулу в ячейке D2:
Аргументы этой функции соответствуют тем, которые принимала функция НАИМЕНЬШИЙ в рамках данного примера.
Получаем следующие результаты:
То есть, наименьшая и наибольшая четвертые зарплаты в отделе равны 3200 и 4000 денежных единиц соответственно.
Четвертое наименьшее значение в массиве чисел
Пример 2. Для наглядности работы функции определим 1-й, 2-й, 3-й, 4-й и 5-й элементы массива данных, состоящего из пяти элементов. Из полученных результатов составим новую таблицу, произведя таким образом, по сути, сортировку элементов массива по возрастанию.
Внесем данные в таблицу:
Для решения будем использовать функцию НАИМЕНЬШИЙ, находя последовательно наименьшее 1-е, 2-е, … ,5-е значения и занося их в новую таблицу. Для примера рассмотрим процесс нахождение наименьшего 1-го значения. В ячейке C2 введем следующую формулу:
Функция принимает следующие аргументы:
- B2:B6 – диапазон значений исходного массива;
- 1 – порядок искомого наименьшего значения.
Аналогичным способом заполним ячейки C3, C4, C5 и C6, указывая в качестве аргумента k числа 2, 3, 4 и 5 соответственно.
В результате получим:
То есть, нам удалось отсортировать исходный массив и наглядно продемонстрировать работу функции НАИМЕНЬШИЙ.
- Подобным способом можно выполнить обратную сортировку (от большего к меньшему) используя функцию НАИБОЛЬШИЙ;
- Для сортировки лучше использовать другие возможности Excel, данный пример приведен лишь с целью наглядной демонстрации работы.
Формула функций НАИБОЛЬШИЙ с массивом и СУММ
Пример 3. В фирме работают 10 сотрудников, включая генерального директора и его заместителя. Один из сотрудников предположил, что оба руководителя получают в целом больше, чем все остальные сотрудники. Необходимо определить, является это предположение истиной или ложью.
Внесем данные о зарплате сотрудников в таблицу:
Очевидно, что зарплата у любого из двух руководителей больше, чем у любого из остальных сотрудников. Поэтому мы можем использовать функцию НАИБОЛЬШИЙ для поиска значений зарплаты гендиректора и заместителя. Для решения запишем следующую формулу:
Аргументами функции СУММ являются значения, которые вернет функция НАИБОЛЬШИЙ. Последняя принимает следующие аргументы:
- B3:B10 – массив, хранящий данные о зарплатах всех работников фирмы;
- – интервал, соответствующий первому и второму искомым величинам.
Примечание: – вариант записи массивов в Excel. С помощью этой записи было указано о необходимости вернуть первые два наибольших значения из массива B3:B10. Полученные значения будут просуммированы функцией СУММ.
В результате получим сумму зарплат директора и заместителя:
Теперь определим общую сумму зарплат оставшихся работников используя функцию СУММ в ячейке D2.
Визуально видно, что сотрудник оказался прав. Однако используем функционал Excel для отображения результата решения задачи в ячейке D6:
=ЕСЛИ(C3>D3;"Сотрудник оказался прав";"Сотрудник неправ")
Функция ЕСЛИ принимает следующие аргументы:
- C3>D3 – логическое выражение, в котором C3 – суммарная з/п руководителей, D3 – суммарная з/п остальных сотрудников;
- "Сотрудник оказался прав" – текст, который будет отображен в случае, если C3>D3 – истина;
- "Сотрудник неправ" – текст, который отобразится в случае, если C3>D3 – ложь.
То есть, оба руководителя получают больше денег, чем остальные сотрудники вместе взятые.
Функции МАКС и МИН прекрасно подходят для поиска наибольшего и наименышего значения в диапазоне данных. Но если нужно найти несколько максимальных или минимальных значений для этой задачи 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.
В процессе подготовки разного рода ранжирования или анализа показателей успешности, очень удобно если выделить цветом наилучший или наихудший результат. Как выделить максимальное значение в Excel цветом? Здесь существенно поможет условное форматирование. Преимуществом такого подхода является сохранение работоспособности автоматического выделения цветом лучших/худших показателей даже при добавлении или удалении данных из таблицы.
Как выделить цветом максимальное значение в Excel
Для примера возьмем таблицу расходов:
Чтобы молниеносно выделить наибольшие и наименьшие расходы делаем следующее:
- Выделите диапазон с расходами B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- Выберите: «Использовать формулу для определения форматируемых ячеек».
- В поле ввода введите формулу: =МАКС($B$2:$B$10)=B2
- Нажмите на кнопку «Формат», чтобы выделить максимальное значение в столбце Excel. Для этого в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите желаемый цвет заливки для ячеек. Например, зеленый. И нажмите ОК на всех открытых окнах.
В результате мы выделили цветом ячейку с максимальным числовым значением.
Как выбрать минимальное значение в Excel
Чтобы выделить цветом минимальное значение в Excel, повторите весь порядок действий, описанных выше. Только вместо функции МАКС, должна быть функция МИН. Данная функция позволяет выбрать наименьшее значение в таблице Excel. А вместо зеленого цвета заливки выберите красный цвет.
К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»
Финальный эффект применения двух правил условного форматирования для выделения цветом наибольшего и наименьшего значения столбца B (Расходы):
Принцип действий обоих правил одинаковый. Они отличаются только функциями =МАКС() и =МИН(). Более того аргументы функций идентичны. Первая функция =МАКС() ищет в несменяемом диапазоне с абсолютными ссылками $B$2:$B$10 максимальное значение. После функции стоит оператор сравнения с изменяемой текущей ячейкой которая имеет относительную ссылку начиная от B2. Правило условного форматирования читает данную формулу так: если число больше всех, то выделить цветом. Как только совпадает максимальное число (которое возвращает функция МАКС) со значением текущей ячейки, формула возвращает логическое значение ИСТИНА и сразу же применяется формат со соответствующим цветом заливки. Так же срабатывает правило для умного форматирования ячеек столбца и с функцией МИН.
Выделение цветом трех наименьших значений подряд
В разных ситуациях полезно применяется автоматическое выделение цветом трех ячеек с наименьшими значениями. Условное форматирование со соответственными формулами прекрасно справляется с данной задачей. Таблица с данными для примера:
Усложним немного задачу. Ячейка с наименьшим значением должна автоматически получить красный цвет заливки. Следующая ячейка с вторым наименьшим значением получит цвет заливки оранжевый. А третье наименьшее значение – желтый.
Чтобы добиться данного эффекта следует выполнить следующие действия:
- Выделите диапазон ячеек B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
- В поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.
- Не снимая выделения с диапазона B2:B9 сразу же создаем второе правило условного форматирования. Снова выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
- В поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;2)=B2 и нажмите на кнопку «Формат», чтобы задать оранжевый цвет заливки для ячейки. И нажмите ОК на всех окнах.
- Создайте третье правило. А в поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;3)=B2 и нажмите на кнопку «Формат», чтобы задать желтый цвет заливки для ячейки. ОК на всех окнах.
- К одному и тому же диапазону должно быть применено 3 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».
Три наименьшие значения автоматически выделились разными цветами.
Внимание! Если просматриваемый диапазон содержит несколько одинаковых минимальных значений все они будут выделены цветом.
Аналогичным способом можно выделять цветом наибольшие значения. Просто изменить в формуле функцию НАИМЕНЬШИЙ на НАИБОЛЬШИЙ. Например: =НАИБОЛЬШИЙ($B$2:$B$9;3)=B2
Функция наименьший и наибольший в Excel
Функция =НАИМЕНЬШИЙ() ищет в заданном (в первом аргументе) диапазоне очередное наименьшее значение. Очередь указана во втором аргументе функции. То есть если во втором аргументе мы указали число 3, значит функция возвращает третье по очереди наименьшее значение заданного диапазона $B$2:$B$9. Если результат вычисления функции равен текущей ячейке значит к ней присваивается соответственный формат (цвет заливки). Важно отметить что в формуле для просмотра диапазона мы используем абсолютные адреса ссылки, а после оператора сравнения =B2 – относительные, так как значение функции сравнивается с каждой ячейкой выделенного диапазона. А функция НАИБОЛЬШИЙ работает обратно пропорционально.
Полезный совет! Если нет необходимости разбивать 3 наименьших значения на разные цвета, тогда необязательно создавать 3 правила условного форматирования для одного и того же диапазона. Достаточно просто немного изменить формулу добавив всего один символ оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То есть – больше или равно.
Все описанные способы хороши тем, что при изменении значений в ячейках они все равно будут работать автоматически.
Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
Читайте также: