Как получить максимальное или минимальное значение только из видимых ячеек в Excel?
Мы можем использовать функции МАКС и МИН для быстрого получения максимального или минимального значения в диапазоне Excel. Но знаете ли вы, как найти максимальное или минимальное значение, игнорируя скрытые ячейки? А что, если нужно применить условное форматирование только к видимым ячейкам, чтобы выделить максимальное или минимальное значение? Следующие решения значительно упростят вам работу:
- Получение максимального/минимального значения только из видимых ячеек с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
- Получение максимального/минимального значения только из видимых ячеек с помощью Использовать условное форматирование
- Получение максимального/минимального значения только из видимых ячеек в каждой строке/столбце с помощью Kutools для Excel
Получение максимального/минимального значения только из видимых ячеек с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
С помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel можно получить максимальное или минимальное значение только из видимых ячеек.
Выберите пустую ячейку, в которую нужно поместить максимальное значение только из видимых ячеек, введите формулу =SUBTOTAL(104,C2:C19) и нажмите клавишу Enter. После этого вы получите максимальное значение только из видимых ячеек. См. снимок экрана:
Примечания:
(1) В приведённой выше формуле C2:C19 — это диапазон, в котором вы хотите найти максимальное значение только среди видимых ячеек.
(2) Чтобы получить минимальное значение только из видимых ячеек, используйте следующую формулу:=SUBTOTAL(105,C2:C19).
Суммирование/подсчёт/усреднение только видимых ячеек в Ограниченный диапазон с игнорированием скрытых или отфильтрованных ячеек/строк/столбцов
Обычные функции СУММ, СЧЁТ и СРЗНАЧ учитывают все ячейки в ограниченном диапазоне — независимо от того, скрыты они или отфильтрованы. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может суммировать, подсчитывать или усреднять значения, игнорируя только скрытые строки. А вот функции Kutools для Excel: СУММВИДИМЫЕ, СЧЁТВИДИМЫЕ и СРЗНАЧВИДИМЫЕ позволяют легко вычислить сумму, количество или среднее значение в ограниченном диапазоне, игнорируя любые скрытые ячейки, строки или столбцы.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Получение максимального/минимального значения только из видимых ячеек с помощью Использовать условное форматирование
Этот метод использует функцию Использовать условное форматирование для быстрого выделения максимального и минимального значений только среди видимых ячеек в отфильтрованном списке.
1. Выделите диапазон (без заголовков), в котором нужно найти максимальное или минимальное значение только среди видимых ячеек, и нажмите Главная > Использовать условное форматирование > Создать правило. См. снимок экрана:
2. В открывшемся диалоговом окне «Создание правила форматирования» выполните следующие действия:
(1) В поле Выбор типа правила выберите вариант Использовать формулу для определения форматируемых ячеек;
(2) В поле Форматировать значения, для которых формула принимает значение ИСТИНА введите формулу =$C2=AGGREGATE(4,7,$C$2:$C$22) (C2:C22 — отфильтрованный столбец, в котором требуется найти максимальное значение только среди видимых ячеек, а C2 — первая ячейка этого столбца);
Примечание: чтобы найти минимальное значение только среди видимых ячеек, используйте формулу =$C2=AGGREGATE(5,7,$C$2:$C$22).
(3) Нажмите кнопку Формат.
3. В открывшемся диалоговом окне «Установить формат ячейки» задайте нужный формат и нажмите кнопку ОК.
В нашем случае цвет выделения был выбран на вкладке Заливка. См. снимок экрана:
4. Теперь вернитесь в диалоговое окно «Создание правила форматирования» и нажмите кнопку ОК, чтобы применить условное форматирование.
Теперь при фильтрации в ограниченном диапазоне строка с максимальным (или минимальным) значением будет выделяться автоматически. См. снимки экрана:
Примечание: Этот метод работает в Excel 2010 и более поздних версиях.
Получение максимального/минимального значения только из видимых ячеек в каждой строке/столбце с помощью Kutools для Excel
Этот метод использует утилиту Kutools для Excel: Выбрать максимальные/минимальные ячейки, чтобы быстро и легко находить максимальные и минимальные значения только среди видимых ячеек в каждой строке или столбце.
1. Выделите диапазон с отфильтрованными или скрытыми строками и столбцами, затем нажмите Alt+;, чтобы выбрать только видимые ячейки. После этого нажмите Ctrl+C, чтобы скопировать их, и, наконец, выделите целевой диапазон и нажмите Ctrl+V, чтобы вставить только видимые строки и столбцы. См. снимок экрана:
2. Оставаясь в выделенном новом диапазоне, нажмите Kutools > Выделить > Выбрать максимальные/минимальные ячейки. См. снимок экрана:
3. В открывшемся диалоговом окне Выбрать максимальные/минимальные ячейки выполните следующие действия:
(1) В разделе Перейти к установите флажок напротив нужного варианта: Минимальное значение или Максимальное значение. В нашем случае установлен флажок напротив Максимальное значение;
(2) В разделе Основа укажите масштаб, в котором нужно найти макс./минимальное значение. В нашем случае установлен флажок напротив Вся строка;
(3) В разделе Выделить установите флажок напротив нужного варианта: Первая ячейка или Все ячейки. В нашем случае установлен флажок напротив Все ячейки.
(4) Нажмите кнопку ОК.
4. Появится ещё одно диалоговое окно Выбрать максимальные/минимальные ячейки, в котором будет указано количество найденных и выделенных ячеек. Просто нажмите кнопку ОК, чтобы закрыть его. После этого вы увидите, что максимальные значения в каждой строке найдены и выделены. См. снимки экрана:
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Демонстрация: Получение макс./Минимальное значение в видимом диапазоне/строках/столбцах
Связанные статьи:
Суммирование только отфильтрованных или видимых ячеек в Excel
Подсчёт с игнорированием скрытых ячеек/строк/столбцов в Excel
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек