Excel скрывает строки по условию
Обновлено: 20.11.2024
Excel поддерживает условное форматирование, которое позволяет изменять цвет и другие атрибуты ячейки в зависимости от содержимого ячейки. К сожалению, нет способа легко скрыть строки на основе значения конкретной ячейки в строке. Однако вы можете добиться того же эффекта, используя макрос для анализа ячейки и соответствующей регулировки высоты строки. Следующий макрос проверит определенную ячейку в первых 100 строках рабочего листа, а затем скроет строку, если значение в ячейке меньше 5.
Можно изменить макрос, чтобы он проверял другую начальную строку, конечную строку и столбец, просто изменив первые три переменные, установленные в макросе. Вы также можете легко изменить значение, которое проверяется в For . Следующий цикл.
Обратите внимание, что этот макрос не показывает строки, а просто скрывает их. Если вы проверяете содержимое ячейки, которое может измениться, вы можете немного изменить макрос, чтобы он либо скрывал, либо отображал строку по мере необходимости. Следующий вариант сделает свое дело:
Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию. Нажмите здесь, чтобы открыть эту специальную страницу в новой вкладке браузера.
ExcelTips – это экономичное обучение работе с Microsoft Excel. Этот совет (1940) относится к Microsoft Excel 97, 2000, 2002 и 2003.
Биография автора
На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.
БОЛЬШЕ ОТ АЛЛЕНА
Защитите свои шаблоны документов
Если вы хотите защитить свои шаблоны от случайных изменений, лучше всего использовать Windows вместо Word. .
Суммирование в строке состояния больше недоступно
При выборе диапазона ячеек Excel обычно отображает сумму выбранных ячеек в строке состояния. Если сумма .
Вертикальное выравнивание встроенной графики
Word позволяет вставлять графику двумя способами: встроенным или плавающим. Если вы используете встроенную графику, вы можете .
Полное руководство по VBA Visual Basic для приложений (VBA) — это язык, используемый для написания макросов во всех программах Office. Это полное руководство показывает как профессионалам, так и новичкам, как освоить VBA, чтобы настроить весь пакет Office под свои нужды. Ознакомьтесь с Освоение VBA для Office 2010 сегодня!
Дополнительные советы Excel (меню)
Добавление теней к ячейкам
Хотите привлечь внимание к тому, что находится в ячейке? Что может быть лучше, чем добавить тень к этой ячейке! Вот как это сделать.
Управление интерпретацией процентов в Excel
При вводе данных на лист Excel пытается выяснить, как лучше всего отобразить вашу запись на экране. Когда это .
Сохранение пользовательских форматов
Хотя реализация пользовательских форматов в Excel не очень надежна, вы все же можете добиться потрясающих результатов.
Подписаться
БЕСПЛАТНАЯ СЛУЖБА. Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению эффективности. Введите свой адрес и нажмите "Подписаться".
Комментарии
Привет всем, это отличный маленький макрос (особенно для тех, у кого ограниченный опыт. Мой вариант использования немного отличается, поэтому я надеюсь, что смогу получить здесь помощь. Этот макрос работает, если все строки, которые вы хотите скрыть находятся один за другим. В моей электронной таблице есть несколько групп строк, которые не следуют друг за другом. Мне нужно определить несколько «диапазонов» или строк, а затем скрыть их на основе значения ячейки для каждой строки. Аналогично этому макросу, но для нескольких групп строк.
Кроме того, я хочу скрыть несколько текстовых строк "заголовка", если строки в этой группе полностью скрыты. Нет необходимости показывать строку заголовка, если содержимое не отображается (поскольку оно было скрыто с помощью макроса, описанного выше). Может кто-нибудь предложить несколько предложений? Спасибо!
Ни одна из переменных в макросах этого совета не объявлена, что является плохой практикой.
Вместо того, чтобы определять начальную строку, конечную строку и контрольный столбец в макросе, вы можете просто выбрать строки в контрольном столбце и запустить этот крошечный макрос:
Sub HURows()
Затемнить ячейку как диапазон
Для каждой ячейки в выделении
' измените <> 4 на нужное вам, т.е. 4
Следующая ячейка
Конец подписки
Неважно! Я разобрался.
Что, если начало и конец строки изменятся, если пользователь вставит или удалит строку над тем местом, где должен начинаться код. Можете ли вы назвать строку, чтобы код работал? Как изменится код?
Привет, Ванесса
Скорее всего, это можно сделать
В VBA такие вещи обычно можно сделать разными способами .
( Но это немного похоже на вопрос, можете ли вы сократить длину строки. Если вы хотите, чтобы макрос делал то, что вы хотите, вам нужно было бы более конкретно указать, что именно вы хотите сделать, какие строки , какие столбцы , как должен запускаться макрос, автоматически при записи ячейки или по вашему выбору для его запуска… и т. д.….. и т. д.)
Алан (Эльстон)
можно ли заставить Excel просматривать строку и скрывать ее только в том случае, если все строки для определенных столбцов пусты?
Это мой сценарий:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 5 Then
If Target.Value = "Yes" Then
Application.Sheets("FAR"). Выберите
Application.Rows ("14"). Выберите
Application.Selection.EntireRow.Hidden = False
Application.Sheets ("Дополнения"). Выберите
Application.Rows ("6"). Выберите
/>Application.Selection.EntireRow.Hidden = False
ElseIf Target.Value = "No" Then
Application.Sheets("FAR").Select
Application.Rows("14") .Select
Application.Selection.EntireRow.Hidden = True
Application.Sheets("Дополнения").Select
Application.Rows("6").Select
Application.Selection .EntireRow.Hidden = True
Конец, если
Конец, если
Конец подпункта
Как сделать так, чтобы это продолжалось для следующих 19 строк?
Идея состоит в том, чтобы:
скрыть строку 14 на вкладке FAR и строку 7 на вкладке "Добавления", если значение в строке 6 "Добавления на вкладке" равно "НЕТ", и оставить непокрытым, если да
скрыть строку 15 на вкладке FAR и ряд 8 в дополнениях к вкладкам, если значение в ряду 7 дополнений к вкладкам равно "НЕТ", и оставить открытым, если да
скрыть ряд 16 во вкладке FAR и ряд 9 в дополнениях к вкладкам, если значение в ряду 8 дополнений к вкладкам равно " НЕТ" и оставьте открытым, если да
и так 20 раз
Пожалуйста, помогите мне с повторным циклом этого скрипта
Большое спасибо за формулировку.
Здравствуйте,
это очень полезно, мне нужно написать макрос, который скрывает всю строку, если ячейка в указанном столбце красная или бежевая. Если ячейка не окрашена, мне нужно, чтобы маркер взял значение из другого столбца и скопировал его в эту ячейку.
по сути, столбец для анализа с цветами - это Q, макрос должен проверить, имеют ли ячейки в Q эти два конкретных цвета, если да, то вся строка должна быть скрыта. Если ячейки в Q не содержат этих двух цветов, мне нужен макрос для копирования значения в ячейке с тем же номером, но из строки C в ячейку Q.
Я новичок в VBA, возможно ли это? Я знаю, что цветовой индекс 19 и 3.
Спасибо, Алан,
Я видел это, но не знал, применимо ли это, так как я не очень привык к написанию пользовательских макросов. Я буду исследовать это. Спасибо, что сообщили мне, что это правильный путь.
@Karen
Посмотрите на мой ответ Райанне несколькими комментариями ниже. Это может натолкнуть вас на некоторые идеи.
В повседневном использовании нам нужно скрывать строки на основе значений ячеек в Excel, чтобы иметь дело с огромным набором данных. Обычно набор данных Excel содержит множество строк. И в большинстве случаев утомительно просматривать весь набор данных, чтобы отсортировать записи по значениям вызовов. Однако, используя функции фильтра Excel, условного форматирования, а также формулы и макросы VBA, мы можем скрыть строки на основе значения ячейки в Excel. В этой статье мы продемонстрируем ранее упомянутые способы скрытия строк на основе значения ячейки в Excel.
Допустим, у нас есть набор данных о продажах, состоящий из столбцов Дата заказа, Регион, Продукт и Количество. . Мы хотим использовать любое значение ячейки в столбце, чтобы скрыть строки.
Загрузить книгу Excel
5 простых способов скрыть строки на основе значения ячейки в Excel
Способ 1: скрыть строки на основе значения ячейки с помощью функции фильтра
На вкладке «Главная» Excel предлагает функцию «Фильтр» в разделе «Редактирование». Мы можем использовать любое желаемое значение ячейки для фильтрации записей, в результате чего строки будут скрыты.
Шаг 1. На рабочем листе перейдите на вкладку "Главная" > выберите "Сортировка и фильтр" (в разделе "Редактирование") > выберите "Фильтр" (в параметрах сортировки и фильтрации).
При выборе параметра "Фильтр" в заголовке каждого столбца отображается значок "Фильтр".
Шаг 2. Нажмите любой значок фильтра в заголовках столбцов (например, регион).
Шаг 3. После нажатия значка «Фильтр» появится окно команды «Фильтр». Отключил все элементы (т.е., Запад), чтобы скрыть соответствующие строки из набора данных.
Нажмите "ОК".
Через мгновение Excel скроет непомеченные записи (т. е. Запад) из набора данных и оставит все остальные записи, чтобы они отображались, как показано на рисунке ниже.
Вы можете снять отметку с любой записи, чтобы скрыть строки из любого столбца, используя значок фильтра заголовка. Для лучшего представления мы демонстрируем метод с несколькими строками и столбцами. Вы можете использовать этот метод для любого количества столбцов и скрыть как можно больше строк в соответствии с вашим типом данных.
Метод 2: использование формулы, а затем фильтрация для скрытия строк на основе значения ячейки
В предыдущем методе мы использовали функцию фильтра, чтобы скрыть строки на основе значений ячеек. Что, если мы хотим, чтобы формула указывала на то, соответствуют ли наши записи нашим критериям или нет? Затем отфильтруйте строки по указанию.
В этом методе мы используем функцию ЕСЛИ, чтобы вставить пользовательскую строку (т. е. скрыть), чтобы указать на невозможность выполнить наше требование. Затем с помощью функции «Фильтр» мы скрываем строки, содержащие в себе строку «Скрыть».
Шаг 1. Введите следующую формулу во все ячейки (например, F5), расположенные рядом с необработанным набором данных.
E5 Шаг 2. Нажмите клавишу ВВОД и перетащите маркер заполнения на пользовательскую строку в ячейках, как показано на рисунке ниже.
Шаг 3. Выполните шаги с 1 по 3 метода 1, чтобы открыть окно команды «Фильтр». В поле команды снимите флажок «Скрыть» и нажмите «ОК».
Фильтрация записей без выбора значения «Скрыть» скрывает все строки, содержащие значение «Скрыть» в своих записях.
Вы можете создать любую пользовательскую строку, чтобы применить к ней фильтр. Этот метод удобен для выполнения поиска по критериям (это может быть значение или текст) в наборе данных.
Способ 3. Применение условного форматирования для скрытия строк на основе значения ячейки в Excel
Функция условного форматирования Excel также может скрывать содержимое строки в зависимости от значения ячейки. Однако более ранние методы скрывают всю строку таким образом, что кажется, будто их не существует, а этот метод скрывает только содержимое строки.
Шаг 1. Перейдите на вкладку "Главная" > выберите "Условное форматирование" > выберите "Новое правило" (из вариантов).
Шаг 2. Появится окно «Новое правило форматирования». В окне Новое правило форматирования
Выберите Использовать формулу, чтобы определить, какую ячейку форматировать, как Выберите тип правила.
Напишите следующую формулу в разделе "Редактировать описание правила".
Нажмите «Формат».
Шаг 3. Появится окно «Формат ячеек». В окне «Формат ячеек»
Выберите раздел «Число» > «Выбрать пользовательский» (в разделе «Категория») > введите 3 точки с запятой (например, ;;;) в разделе «Тип».
Нажмите "ОК".
Шаг 4. Нажав кнопку «ОК», Excel вернет вас в диалоговое окно «Новое правило форматирования». Еще раз нажмите OK.
Выполнение шагов с 1 по 4 приводит к скрытию всего содержимого, которое соответствует West в столбце C.
Вы можете назначить любой текст или значение в формуле, которую мы вставляем, чтобы применить условное форматирование. Если вы хотите скрыть только содержимое, а не строки, этот метод очень удобен в использовании. Кроме того, вы можете удалить правила условного форматирования из параметров в любое время, когда вам нужен необработанный набор данных, ничего не изменяя.
Способ 4. Скрытие строк на основе значения ячейки с помощью макроса VBA в Excel
VBA — это мощный инструмент для достижения любых результатов, основанных на условиях. В этом методе мы используем код макроса VBA, чтобы скрыть строки из определенного столбца, накладывая определенное условие.
Для этого метода мы преобразуем наш набор данных в практический, как показано на снимке экрана ниже. И хотите скрыть строки в зависимости от значения столбца (например, региона), равного значению ячейки (например, Восток).
Шаг 1. Нажмите сочетание клавиш ALT+F11, чтобы открыть окно Microsoft Visual Basic. В окне наведите указатель мыши на панель инструментов > выберите «Вставить» > «Выбрать модуль».
Шаг 2. Вставьте следующий код макроса в модуль и нажмите F5, чтобы запустить макрос.
Код макроса назначает начальную (т. е. 2), конечную (т. е. 15) строку и номер столбца (т. е. 2, региональный столбец). Номер столбца указывает, в каком столбце макрос соответствует заданному значению (т. е. Восток). Затем функция VBA IF скрывает все строки, кроме значения «Восток», существующего в строках данного столбца (т. е. столбца «Регион»).
Шаг 3. Выполнение кода макроса скрывает все строки, кроме строк, содержащих Восток в ячейках, как показано на рисунке ниже.
Метод 5: скрытие строк на основе значения ячейки в режиме реального времени с помощью макроса VBA
Используя код макроса VBA в методе 4, мы скрываем строки окончательного или законченного набора данных. Но может быть сценарий, когда нам нужно скрывать строки в режиме реального времени, поддерживая определенное условие. В этом случае мы можем использовать закрытый макрос, чтобы справиться с таким сценарием.
Для приведенного ниже набора данных мы хотим выполнить операцию, в которой мы просто пишем критерии, а Excel автоматически скрывает строки в режиме реального времени.
Шаг 1. После открытия Microsoft Visual Basic (всего нажав клавиши ALT+F11) дважды щелкните соответствующий лист (например, Лист3) в разделе VBAProject.
Шаг 2. Выберите «Рабочий лист» в окне кода листа, как показано на снимке экрана ниже.
Шаг 3. Появится приватная подписка.
Шаг 4. Аналогично предыдущему коду макроса вставьте следующий код макроса в окно кода листа.
Записанный код макроса назначает начальную (т. е. 2), конечную (т. е. 15) строку и столбец (т. е. 2) номера. Затем он накладывает условие, что он скрывает значения, равные ячейке A18, в столбце 2. Функция VBA IF создает код частного макроса для скрытия строк в реальном времени после ввода любого значения в ячейку A18.
Шаг 5. Нажмите F5, чтобы запустить макрос, а затем вернуться к рабочему листу. Попробуйте ввести все, что существует в столбце 2, и нажмите клавишу ВВОД.
Нажатие клавиши ввода после ввода любого текста (например, "Восток" или "Запад") скрывает соответствующий текст, содержащий строки из набора данных. Вы можете использовать любой текст или значение назначенного столбца, чтобы скрыть строки в наборе данных.
Заключение
В этой статье мы покажем, как скрыть строки на основе значения ячейки в Excel. Функции фильтра и условного форматирования Excel удобны для скрытия строк. Однако функция условного форматирования просто скрывает содержимое строк. Макросы VBA предлагают эффективные способы скрытия строк в соответствии с вашим типом данных. Надеюсь, что эти описанные методы сделают работу так, как вы хотите. Комментарий, если у вас есть дополнительные вопросы или есть что добавить.
Зарегистрированный пользователь Дата присоединения 03-04-2012 Расположение Макао MS-Off Ver Excel 2007 Сообщений 32
Условное форматирование — скрытие строки.
Я видел несколько решений, как использовать условное форматирование для изменения внешнего вида ячейки, но возможно ли, чтобы строка исчезала (удалялась или скрывалась) при выполнении условий?
Я создаю лист задач, и когда элемент будет выполнен, пользователь отметит его флажком, добавив дату, и он появится в списке на 2 недели, а затем будет скрыт или удален. Я знаю, что могу использовать функцию CELL для определения строки, но не знаю, как сделать ее скрытой.
Администратор Регистрация Дата 29.12.2011 Местоположение Duncansville, PA USA MS-Off Ver Excel 2000/3/7/10/13/16 Сообщений 51 725
CF изменяет только внешний вид ячейки/диапазона ячеек, но не саму ячейку. Поэтому я думаю, что лучшее, что вы могли бы сделать, используя CF, — это установить белый цвет для шрифта и фона, что, по сути, делает их невидимыми.
В качестве альтернативы можно использовать вспомогательный столбец и фильтры. Добавьте формулу во вспомогательный столбец, которая проверяет ваши критерии, и когда она выполняется, она показывает X (или 1 или что угодно). Затем вы можете отфильтровать таблицу на основе этого вспомогательного столбца, чтобы скрыть указанные строки
Зарегистрированный пользователь Дата присоединения 03-04-2012 Расположение Макао MS-Off Ver Excel 2007 Сообщений 32
Спасибо за совет, но у меня есть дополнительный вопрос по условному форматированию. Я понимаю, как заставить ячейку изменить свой внешний вид, если значение соответствует условиям, но у меня возникают трудности с тем, как изменить значение строки, если одна ячейка соответствует этим условиям.
Мой пример — иметь фильтр данных, ограничивающий ввод «x» или «o». При использовании шрифта Windings это выглядит как флажок или пустое поле. Если этот флажок установлен, я хочу, чтобы заливка была светло-серого цвета. Единственный способ, который я вижу, это сделать уникальное условие для каждой строки. Есть ли в Excel проверка каждой строки, и если ячейка в столбце A имеет значение «x», сделать заливку серой в столбцах B, C и D?
Администратор Регистрация Дата 29.12.2011 Местоположение Duncansville, PA USA MS-Off Ver Excel 2000/3/7/10/13/16 Сообщений 51 725
Тот факт, что вы применяете к ячейке другой шрифт, опять же, не меняет содержимого этой ячейки. Шрифт — это формат, и он косметический.
Чтобы применить CF ко всей строке строки, если выполняется определенное условие — допустим, у вас есть диапазон данных A2: E10 (строка 1 — заголовки), и у вас есть условие, которое должно выполняться в C2: C10.
1. выделите диапазон, к которому вы хотите применить условное форматирование (A2:E10)
2. на главной вкладке стили выберите CF
3. выберите новое правило, выберите использовать формулу
4. введите =$C2="X" формат заполнить СЕРЫЙ
Зарегистрированный пользователь Дата присоединения 03-04-2012 Расположение Макао MS-Off Ver Excel 2007 Сообщений 32
Спасибо, все работает. Я просто не совсем понимаю синтаксис. Используя условную формулу =$C2="x", я бы ожидал, что весь диапазон будет отформатирован серой заливкой. Я пытался вставить что-то вроде =C2:C10="x". Живи и учись.
Администратор Регистрация Дата 29.12.2011 Местоположение Duncansville, PA USA MS-Off Ver Excel 2000/3/7/10/13/16 Сообщений 51 725
Нет, CF работает по ячейкам и по строкам. Таким образом, фиксируя (абсолютизируя) все ячейки в столбце C - это то, что делает $C - CF затем смотрит,
если C2 соответствует правилу, применяет правило ко всем ячейкам A:E в строке 2,
если C3 соответствует правилу, примените правило ко всем ячейкам A:E в строке 3,
если C4 соответствует правилу, примените правило ко всем ячейкам A:E в строке 4
В этом посте рассказывается, как скрыть строки на основе значения ячейки в Excel. Как скрыть строки на основе значения ячейки с помощью кода макроса VBA в Excel 2013/2016.
Скрыть строки на основе значения ячейки с помощью функции фильтра
Предположим, что у вас есть список данных в диапазоне A1:B6, и вы хотите скрыть строку, если значение столбца B меньше или больше определенного значения. Вы можете использовать функцию фильтра для фильтрации чисел и скрытия строк на основе значения ячейки. Вот шаги:
Скрыть строки на основе значения ячейки с помощью макроса VBA
Вы также можете использовать макрос Excel VBA, чтобы добиться того же результата, скрывая строки на основе значения ячейки. Просто выполните следующие действия:
В этом посте рассказывается, как найти и заменить сразу несколько значений с помощью макроса VBA или формулы в Excel. Как сделать множественный поиск и замену в Excel. Предположим, что у вас есть несколько ячеек, содержащих .
В этом посте показано, как разрешить комментарии на защищенном листе в Excel. Вы можете легко вставлять комментарии в ячейки обычного рабочего листа в Excel, но если вы хотите вставить комментарий в рабочий лист, который .
В этом посте показано, как переключиться с нижнего регистра на верхний регистр в Excel. и я собираюсь показать вам два разных способа преобразования текста в верхний регистр с помощью формулы или макроса VBA в Excel 2013, Excel.
В этом посте показано, как скрыть чередующиеся строки или столбцы в Excel или как скрыть каждую третью, четвертую, пятую строку или столбец в Excel. Если вы хотите скрыть каждую вторую строку на текущем листе, как .
В этом посте показано, как использовать макрос VBA для сохранения файла Excel и перезаписи любого существующего файла без запроса, чтобы вы получили маленькое окно с сообщением, что файл уже существует do .
В этом посте рассказывается, как подсчитать количество ячеек, содержащих нечетные или четные числа в диапазоне ячеек, с помощью формулы в Excel 2013/2016. Как подсчитать ячейки, содержащие нечетные числа, с помощью .
В этом посте рассказывается, как подсчитать количество ячеек, содержащих отрицательные числа в диапазоне ячеек, с помощью формулы в Excel 2013/2016. Вы можете подсчитать количество отрицательных чисел в своих данных с помощью простых функций.
В этом сообщении вы узнаете, как подсчитать количество ячеек, которые не пусты или не пусты, в ячейках заданного диапазона, используя формулу в Excel 2013/2016. Как подсчитать количество ячеек, которые не являются пустыми в определенном диапазоне .
В этом посте рассказывается, как подсчитать количество ячеек меньше определенного числового значения в ячейках заданного диапазона, используя формулу в Excel 2013/2016. Как подсчитать ячейки, которые меньше определенного .
Читайте также: