Excel как выделить ячейки в фильтре - Word и Excel - помощь в работе с программами

#Руководства

5 авг 2022
0

Как из сотен строк отобразить только необходимые? Как отфильтровать таблицу сразу по нескольким условиям и столбцам? Разбираемся на примерах.

Иллюстрация: Meery Mary для Skillbox Media

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

Фильтры в Excel — инструмент, с помощью которого из большого объёма информации выбирают и показывают только нужную в данный момент. После фильтрации в таблице отображаются данные, которые соответствуют условиям пользователя. Данные, которые им не соответствуют, скрыты.

В статье разберёмся:

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

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

Фильтры в Excel работают по тому же принципу. Пользователь выбирает параметры данных, которые ему нужно отобразить, — и Excel убирает из таблицы всё лишнее.

Разберёмся, как это сделать.

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

Таблица, в которой хранятся данные о продажах автосалона
Скриншот: Excel / Skillbox Media

Допустим, нужно показать продажи только одного менеджера — Соколова П. Воспользуемся фильтрацией.

Шаг 1. Выделяем ячейку внутри таблицы — не обязательно ячейку столбца «Менеджер», любую.

Чтобы настроить фильтрацию, выбираем любую ячейку таблицы
Скриншот: Excel / Skillbox Media

Шаг 2. На вкладке «Главная» нажимаем кнопку «Сортировка и фильтр».

Нажимаем кнопку «Сортировка и фильтр»
Скриншот: Excel / Skillbox Media

Шаг 3. В появившемся меню выбираем пункт «Фильтр».

Жмём сюда, чтобы применить фильтрацию ко всем столбцам таблицы
Скриншот: Excel / Skillbox Media

То же самое можно сделать через кнопку «Фильтр» на вкладке «Данные».

Второй способ установить фильтрацию
Скриншот: Excel / Skillbox Media

Шаг 4. В каждой ячейке шапки таблицы появились кнопки со стрелками — нажимаем на кнопку столбца, который нужно отфильтровать. В нашем случае это столбец «Менеджер».

Нажимаем кнопку столбца, который нужно отфильтровать, — появляется меню фильтра
Скриншот: Excel / Skillbox Media

Шаг 5. В появившемся меню флажком выбираем данные, которые нужно оставить в таблице, — в нашем случае данные менеджера Соколова П., — и нажимаем кнопку «Применить фильтр».

Выбираем менеджера, данные о продажах которого нужно показать в таблице
Скриншот: Excel / Skillbox Media

Готово — таблица показывает данные о продажах только одного менеджера. На кнопке со стрелкой появился дополнительный значок. Он означает, что в этом столбце настроена фильтрация.

Результат фильтрации по одному параметру
Скриншот: Excel / Skillbox Media

Чтобы ещё уменьшить количество отображаемых в таблице данных, можно применять несколько фильтров одновременно. При этом как фильтр можно задавать не только точное значение ячеек, но и условие, которому отфильтрованные ячейки должны соответствовать.

Разберём на примере.

Выше мы уже отфильтровали таблицу по одному параметру — оставили в ней продажи только менеджера Соколова П. Добавим второй параметр — среди продаж Соколова П. покажем автомобили дороже 1,5 млн рублей.

Шаг 1. Открываем меню фильтра для столбца «Цена, руб.» и нажимаем на параметр «Выберите».

Открываем меню фильтра столбца «Цена, руб.» и жмём сюда
Скриншот: Excel / Skillbox Media

Шаг 2. Выбираем критерий, которому должны соответствовать отфильтрованные ячейки.

В нашем случае нужно показать автомобили дороже 1,5 млн рублей — выбираем критерий «Больше».

Выбираем критерий фильтрации
Скриншот: Excel / Skillbox Media

Шаг 3. Дополняем условие фильтрации — в нашем случае «Больше 1500000» — и нажимаем «Применить фильтр».

Дополняем условие фильтрации
Скриншот: Excel / Skillbox Media

Готово — фильтрация сработала по двум параметрам. Теперь таблица показывает только те проданные менеджером авто, цена которых была выше 1,5 млн рублей.

Результат фильтрации по двум параметрам
Скриншот: Excel / Skillbox Media

Расширенный фильтр позволяет фильтровать таблицу по сложным критериям сразу в нескольких столбцах.

Это можно сделать способом, который мы описали выше: поочерёдно установить несколько стандартных фильтров или фильтров с условиями пользователя. Но в случае с объёмными таблицами этот способ может быть неудобным и трудозатратным. Для экономии времени применяют расширенный фильтр.

Принцип работы расширенного фильтра следующий:

Копируют шапку исходной таблицы и создают отдельную таблицу для условий фильтрации.
Вводят условия.
Запускают фильтрацию.

Разберём на примере. Отфильтруем отчётность автосалона по трём критериям:

менеджер — Шолохов Г.;
год выпуска автомобиля — 2019-й или раньше;
цена — до 2 млн рублей.

Шаг 1. Создаём таблицу для условий фильтрации — для этого копируем шапку исходной таблицы и вставляем её выше.

Важное условие — между таблицей с условиями и исходной таблицей обязательно должна быть пустая строка.

Создаём таблицу для условий фильтрации
Скриншот: Excel / Skillbox Media

Шаг 2. В созданной таблице вводим критерии фильтрации:

«Год выпуска» → <=2019.
«Цена, руб.» → <2000000.
«Менеджер» → Шолохов Г.

В таблице с условиями вводим критерии фильтрации
Скриншот: Excel / Skillbox Media

Шаг 3. Выделяем любую ячейку исходной таблицы и на вкладке «Данные» нажимаем кнопку «Дополнительно».

Выделяем ячейку исходной таблицы и нажимаем на «Дополнительно», чтобы вызвать меню расширенного фильтра
Скриншот: Excel / Skillbox Media

Шаг 4. В появившемся окне заполняем параметры расширенного фильтра:

Выбираем, где отобразятся результаты фильтрации: в исходной таблице или в другом месте. В нашем случае выберем первый вариант — «Фильтровать список на месте».
Диапазон списка — диапазон таблицы, для которой нужно применить фильтр. Он заполнен автоматически, для этого мы выделяли ячейку исходной таблицы перед тем, как вызвать меню.

Эти параметры меню выставлены программой автоматически
Скриншот: Excel / Skillbox Media

Диапазон условий — диапазон таблицы с условиями фильтрации. Ставим курсор в пустое окно параметра и выделяем диапазон: шапку таблицы и строку с критериями. Данные диапазона автоматически появляются в окне параметров расширенного фильтра.

Выделяем диапазон таблицы с условиями
Скриншот: Excel / Skillbox Media

Шаг 5. Нажимаем «ОК» в меню расширенного фильтра.

Готово — исходная таблица отфильтрована по трём заданным параметрам.

Итог работы расширенного фильтра
Скриншот: Excel / Skillbox Media

Отменить фильтрацию можно тремя способами:

1. Вызвать меню отфильтрованного столбца и нажать на кнопку «Очистить фильтр».

Первый способ снять фильтрацию
Скриншот: Excel / Skillbox Media

2. Нажать на кнопку «Сортировка и фильтр» на вкладке «Главная». Затем — либо снять галочку напротив пункта «Фильтр», либо нажать «Очистить фильтр».

Второй способ снять фильтрацию
Скриншот: Excel / Skillbox Media

3. Нажать на кнопку «Очистить» на вкладке «Данные».

Третий способ снять фильтрацию
Скриншот: Excel / Skillbox Media

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Источник

С помощью автофилеров или встроенных операторов сравнения, таких как «больше» и «10 лучших» в Excel вы можете показать нужные данные и скрыть остальные. После фильтрации данных в диапазоне ячеек или таблице вы можете повторно использовать фильтр, чтобы получить последние результаты, или очистить фильтр, чтобы повторно отфильтровать все данные.

Используйте фильтры, чтобы временно скрывать некоторые данные в таблице и видеть только те, которые вы хотите.

Фильтрация диапазона данных

Выберите любую ячейку в диапазоне данных.
Выберите Фильтр> данных.
Выберите стрелку в столбце .
Выберите Текстовые фильтры или Числовое фильтры, а затем выберите сравнение, например Между.
Введите условия фильтрации и нажмите кнопку ОК.

Фильтрация данных в таблице

При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.

Выберите стрелку в столбца, который вы хотите отфильтровать.
Сберем (Выберите все) и выберем поля, которые вы хотите отобрать.
Нажмите кнопку ОК.

Стрелка загона столбца меняется на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.

Статьи по теме

Excel Учебный курс. Фильтрация данных в таблице

Рекомендации и примеры для сортировки и фильтрации данных по цвету

Фильтрация данных в сводной таблице

Использование расширенных условий фильтрации

Удаление фильтра

Отфильтрованные данные отображают только строки, которые соответствуют условия и скрывают строки, которые отображать не нужно. Фильтруя данные, вы можете копировать, находить, изменять, форматирование, диаграммы и печатать подмножество отфильтрованных данных, не меняя их оголовье и не перемещая.

Вы также можете отфильтровать несколько столбцов. Фильтры являются аддитивно, то есть каждый дополнительный фильтр основан на текущем фильтре и дополнительно уменьшает подмножество данных.

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

Два типа фильтров

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

Повторное присвоение фильтра

Чтобы определить, применяется ли фильтр, обратите внимание на значок в заголовке столбца:

Стрелка вниз означает, что фильтрация включена, но не применена.

Если наведите курсор на заголовок столбца с включенной фильтрацией, но не примененной, на экране появляется подсказка «(Отображает все)».
Кнопка Фильтр означает, что применяется фильтр.

Когда вы наводите курсор на заголовок отфильтрованного столбца, на подсказке отображается примененный к этому столбец фильтр, например «Равно красному цвету ячейки» или «Больше 150».

При повторном его повторном анализе отображаются разные результаты по следующим причинам:

Данные были добавлены, изменены или удалены в диапазон ячеек или столбец таблицы.
значения, возвращаемые формулой, изменились, и лист был пересчитан.

Не смешивать типы данных

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

Фильтрация данных в таблице

Выделите данные, которые нужно отфильтровать. На вкладке Главная нажмите кнопку Форматировать как таблицу и выберите команду Форматировать как таблицу.
В диалоговом окне Создание таблицы можно выбрать, есть ли в таблице заглавные таблицы.
- Выберите Таблица с заголовками, чтобы преобразовать верхнюю строку в заголовки таблицы. Данные в этой строке не будут фильтроваться.
- Не выбирайте этот пункт, если вы хотите Excel в Интернете в таблицу под данными таблицы, которые можно переименовать.
Нажмите кнопку ОК.
Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтрации.

Фильтрация диапазона данных

Если вы не хотите форматирование данных в виде таблицы, вы также можете применить фильтры к диапазону данных.

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

Параметры фильтрации для таблиц или диапазонов

Можно применить общий фильтр, выбрав пункт Фильтр, или настраиваемый фильтр, зависящий от типа данных. Например, при фильтрации чисел отображается пункт Числовые фильтры, для дат отображается пункт Фильтры по дате, а для текста — Текстовые фильтры. Применяя общий фильтр, вы можете выбрать для отображения нужные данные из списка существующих, как показано на рисунке:

Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.

В этом примере, чтобы отобрать регионы, в которых сумма продаж за март была меньше 6000, можно применить настраиваемый фильтр:

Вот как это сделать.

Щелкните стрелку фильтра в ячейке со словом «Март», выберите пункт Числовые фильтры и условие Меньше и введите значение 6000.
Нажмите кнопку ОК.

Excel в Интернете применяет фильтр и отображает только регионы с продажами ниже 6000 рублей.

Аналогичным образом можно применить фильтры по дате и текстовые фильтры.

Очистка фильтра из столбца

Нажмите кнопку Фильтр рядом с заголовком столбца и выберите очистить фильтр <«Имя столбца»>.

Удаление всех фильтров из таблицы или диапазона

Выберите любую ячейку в таблице или диапазоне и на вкладке Данные нажмите кнопку Фильтр.

При этом фильтры будут удаляться из всех столбцов таблицы или диапазона и вывести все данные.

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

Примечания:

Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.

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

Примечания:

Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Вместо фильтрации можно использовать условное форматирование, которое позволяет четко выделить верхние или нижние числовые значения среди данных.

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

В диапазоне ячеек или столбце таблицы щелкните ячейку с определенным цветом, цветом шрифта или значком, по которому вы хотите выполнить фильтрацию.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр во всплывающем меню По цвету щелкните Цвет ячейки, Цвет шрифта или Значок ячейки и выберите цвет.

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

Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В области (Выделить все) прокрутите список вниз и установите флажок (Пустые).
Примечания:
- Фильтры можно применить только к одному диапазону ячеек на листе за раз.
- Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
- В окне фильтра отображаются только первые 10 000 уникальных записей списка.

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

В разделе Фильтр щелкните Выберите один из вариантов и во всплывающем меню выполните одно из указанных ниже действий.

Цель фильтрации диапазона	Операция
Строки с определенным текстом	Содержит или Равно.
Строки, не содержащие определенный текст	Не содержит или Не равно.

В поле рядом с всплывающим меню введите текст, которое хотите использовать.

В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.

Задача	Операция
Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий	И.
Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий	Или.

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

Условие фильтрации	Операция
Начало строки текста	Начинается с.
Окончание строки текста	Заканчивается на.
Ячейки, которые содержат текст, но не начинаются с букв	Не начинаются с.
Ячейки, которые содержат текст, но не оканчиваются буквами	Не заканчиваются.

В поле рядом с всплывающим меню введите текст, которое хотите использовать.

В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.

Задача	Операция
Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий	И.
Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий	Или.

При создании условий можно использовать подстановочные знаки.

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

Например, чтобы в результате фильтрации найти оба слова «год» и «гид», введите г?д.

Выполните одно из указанных ниже действий.

Используемый знак	Чтобы найти
? (вопросительный знак)	Любой символ Пример: условию «стро?а» соответствуют результаты «строфа» и «строка»
Звездочка (*)	Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
Тильда (~)	Вопросительный знак или звездочка Например, там~? находит «там?»

Используемый знак

Чтобы найти

? (вопросительный знак)

Любой символ

Пример: условию «стро?а» соответствуют результаты «строфа» и «строка»

Звездочка (*)

Любое количество символов

Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»

Тильда (~)

Вопросительный знак или звездочка

Например, там~? находит «там?»

Выполните одно из указанных ниже действий.

Задача	Действие
Удаление определенных условий фильтрации	Щелкните стрелку столбце, который содержит фильтр, и выберите очистить фильтр.
Удаление всех фильтров, примененных к диапазону или таблице	Выделите столбцы диапазона или таблицы, к которым применяются фильтры, а затем на вкладке Данные щелкните Фильтр.
Удаление или повторное применение стрелок фильтра в диапазоне или таблице	Выделите столбцы диапазона или таблицы, к которым применяются фильтры, а затем на вкладке Данные щелкните Фильтр.

При фильтрации данных появляются только данные, которые соответствуют вашим условиям. Данные, которые не соответствуют этому критерию, скрыты. После фильтрации данных можно копировать, находить, изменять, форматирование, диаграммы и печатать подмножество отфильтрованных данных.

Таблица с примененным фильтром «4 самых верхних элементов»

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

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

Фильтры скрывают лишние данные. Таким образом можно сосредоточиться на том, что вы хотите видеть. С другой стороны, при сортировке данных их порядок будет меняться. Дополнительные сведения о сортировке см. в списке сортировки данных.

При фильтрации рассмотрите следующие рекомендации:

В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Можно отфильтровать несколько столбцов. Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.

Примечание: При использовании поиска найти для поиска отфильтрованных данных поиск ведется только по отображаемой информации; данные, которые не отображаются, не поиск не ведется. Чтобы найти все данные, очистка всех фильтров.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Выделение ячеек после фильрации

junior200891

Дата: Воскресенье, 29.06.2014, 17:01 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Excel 2007

После применения фильтра к таблицы не возможно выделить значение ячеек следующих не друг за другом, тоесть выделить ячейку B45 и B46 можно а вот при выделении ячейки B48 выделяются все данные ячеек

Ответить

_Boroda_

Дата: Воскресенье, 29.06.2014, 19:58 |
Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Выделите отфильтрованные данные (можно встать в какую-то ячейку диапазона и нажать Контрл+Шифт+8 или вручную необходимый диапазон) и
1. нажмите Альт+Шифт+4 на русской раскладке — выделенными окажутся только видимые ячейки в выделенном диапазоне.
2. Главная — Найти и выделить — Выделение группы ячеек — поставить точку на «Только видимые ячейки».
3. Контрл+h — Выделить — далее см. п.2.
4. вывести на панель бы строго доступа кнопку «Выделить видимые ячейки» (я у себя на компьютерах так и сделал).

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

junior200891

Дата: Воскресенье, 29.06.2014, 23:56 |
Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Excel 2007

Не так объяснил, посмотрите прикрепленный файл

Ответить

krosav4ig

Дата: Понедельник, 30.06.2014, 00:44 |
Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 2346

Репутация:
989
±

Замечаний:
0% ±

Excel 2007,2010,2013

junior200891, это из-за того, что у вас объединенные ячейки

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

junior200891

Дата: Понедельник, 30.06.2014, 20:20 |
Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Excel 2007

В том то и дело что нет объединенных ячеек если выделяемая область находится последовательно тоесть A1,A2,A3 то выделяются только они, но если попытаться выделить A1,A2,A3,A5 то выделяются и все столбцы до A5 (где есть значения B,C,D). Файл к сожалению не могу скинуть потому что там конфиденциальная информация, но на другом ПК таких проблем нету.

Ответить

Источник

На чтение 5 мин Опубликовано 12.01.2021

В Microsoft Office Excel, начиная с версии 2007 года, появилась возможность сортировки и фильтрации ячеек табличного массива по цвету. Такая функция позволяет быстрее ориентироваться в таблице, повышает ее презентабельность и эстетичность. В данной статье будут рассмотрены основные способы фильтрации информации в Excel по цвету.

Содержание

Особенности фильтрации по цвету
Как отфильтровать данные по цвету с помощью встроенной в Excel опции
Как в Excel отсортировать данные по нескольким цветам
Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции
Заключение

Особенности фильтрации по цвету

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

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

Как отфильтровать данные по цвету с помощью встроенной в Excel опции

Алгоритм фильтрации информации по цвету в табличном массиве Excel подразделяется на следующие этапы:

Выделить требуемый диапазон ячеек левой клавишей манипулятора и переместиться во вкладку «Главная», находящуюся в верхней панели инструментов программы.
В появившейся области в подразделе Редактирование необходимо найти кнопку «Сортировка и фильтр» и развернуть ее, нажав на стрелочку внизу.

Варианты сортировки и фильтрации табличных данных в Excel

В отобразившемся меню нажать по строчке «Фильтр».

В окне выбора нужно нажать на кнопку «Фильтр»

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

Появившееся стрелочки в заголовки столбцов таблицы после добавления фильтра

После нажатия по стрелке в названии столбика отобразится похожее меню, в котором надо щелкнуть по строке Фильтр по цвету. Раскроется дополнительная вкладка с двумя доступными функциями: «Фильтр по цвету ячейки» и «Фильтр по цвету шрифта».

Варианты фильтрации в Эксель. Здесь можно выбрать любой цвет, который нужно расположить на верху таблицы

В разделе «Фильтр по цвету ячейки» выбрать оттенок, по которому нужно отфильтровать исходную таблицу, нажав на него ЛКМ.
Проверить результат. После проделывания вышеуказанных манипуляций в таблице останутся только ячейки с указанным ранее цветом. Остальные элементы пропадут, а табличка сократится.

Внешний вид таблички, преобразившейся после фильтрации данных в ней

Отфильтровать данные в массиве Excel можно вручную, удалив строки и столбцы с ненужными цветами. Однако пользователю придется потратить на выполнение этого процесса дополнительное время.

Если выбрать нужный оттенок в разделе «Фильтр по цвету шрифта», то в таблице останутся только строчки, текст шрифта в которых прописан выбранным цветом.

Обратите внимание! В Microsoft Office Excel у функции фильтрации по цвету есть существенный недостаток. Пользователь может выбрать только один оттенок, по которому будет производиться фильтрация табличного массива. Указать несколько цветов сразу невозможно.

Как в Excel отсортировать данные по нескольким цветам

С сортировкой по цветам в Экселе обычно проблем не возникает. Она выполняется аналогичным образом:

По аналогии с предыдущим пунктом добавить фильтр к табличному массиву.
Щелкнуть по стрелочке, появившейся в названии столбца, и в выпадающем меню выбрать пункт «Сортировка по цвету».

Выбор сортировки по цвету

Указать требуемый тип сортировки, к примеру выбрать нужный оттенок в колонке «Сортировка по цвету ячейки».
После выполнения предыдущих манипуляций строчки таблицы с выбранным ранее оттенком будут располагаться на первом месте массива по порядку. Также можно отсортировать и остальные цвета.

Финальный результат сортировки ячеек по цвету в табличном массиве

Дополнительная информация! Отсортировать данные в табличке также можно с помощью функции «Настраиваемая сортировка», добавив несколько уровней по цветам.

Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции

Чтобы в Microsoft Office Excel выбрать фильтр для отображения сразу нескольких цветов в таблице, необходимо создать дополнительный параметров с оттенком заливки. По созданному оттенку данные в дальнейшем и будут фильтроваться. Пользовательская функция в Эксель создается по следующей инструкции:

Зайти в раздел «Разработчик», который находится сверху главного меню программы.
В открывшейся области вкладки кликнуть по кнопке «Visual Basic».
Откроется встроенный в программу редактор, в котором потребуется создать новый модуль и прописать код.

Код программы с двумя функциями. Первая определяет цвет заливки элемента, а вторая отвечает за цвет внутри ячейки

Чтобы применить созданную функцию, необходимо:

Вернуться на рабочий лист Excel и создать два новый столбца рядом с исходной таблицей. Их можно назвать «Цвет ячейки» и «Цвет текста» соответственно.

Созданные вспомогательные столбцы

В первом столбце написать формулу «= ColorFill()». В скобках указывается аргумент. Нужно кликнуть по ячейке с любым цветом в табличке.

Формула в столбце «Цвет ячейки»

Во втором столбике указать тот же самый аргумент, но только с функцией «=ColorFont()».

Формула в столбце «Цвет текста»

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

Получившееся данные после растягивания формулы

Добавить фильтр в табличный массив по рассмотренной выше схеме. Данные будут отсортированы по цвету.

Важно! Сортировка в Excel с помощью пользовательской функции выполняется аналогичным образом.

Заключение

Таким образом, в MS Excel можно быстро отфильтровать исходный табличный массив по цвету ячеек различными методами. Основные способы фильтрации и сортировки, которыми рекомендуется пользоваться при выполнении поставленной задачи, были рассмотрены выше.

Оцените качество статьи. Нам важно ваше мнение:

Источник

Как скопировать только видимые ячейки в Excel

Смотрите также текст в ячейке скрытых ячеек. том, чтобы определить,«;;;»

появятся, но если этого недостаточно и

на ленте или пункт отображения скрытых элементов раньше, дважды щелкните

Редактирование.Важно:

если их много. ниже или наОтбор данных в
По умолчанию Excel копирует Excel по столбцам».Теперь, не убирая с помощью какой. вы хотите вообще нужно произвести полную контекстного меню.
«Скрыть или отобразить» в первую очередь нужное имя ссылки» нажмите кнопкуПримечание: Чтобы выделить именованные ячейки
Как вставить данные другом листе, т.д. Excel провели с как видимые, так
mary_mary выделения ячеек, в технологии они былиОчень хорошо, если вы удалить фильтрацию, то

разгруппировку, то сначала

Строки и столбцы можно, который находится в
зависит от того,
на ячейку в

Найти и выделить

office-guru.ru

Копирование в Excel отфильтрованных данных.

Текущая выделенная ячейка останется и диапазоны, необходимо в отфильтрованные строки, Если вставим в помощью фильтра или и скрытые ячейки.: Как можно в первой ячейке выделенного спрятаны. Потом, исходя помните, каково было нужно нажать на
выделите соответствующие столбцы также спрятать, используя группе каким образом они
 списке
и нажмите кнопку выделенной вместе с сначала определить их смотрите в статье
строки, где стоит сортировки. Теперь их
Однако можно скопировать excel 2003 скопировать столбца пишем цифру
из этого, применять изначальное форматирование ячеек. кнопку
или строки. Затем, группировку, когда они«Видимость» были скрыты. ЗачастуюПерейтиПерейти
ячейками, указанными в имена на листе. «Вставить в видимые фильтр, то вставятся
 нужно распечатать или только видимые ячейки. данные из одной «1» и нажимаем один из тех В таком случае«Фильтр» находясь во вкладке собираются в отдельные. Далее в появившемся эти способы используют.. Можно также нажать поле Сведения об именовании строки в Excel». отфильтрованные данные и  перенести в другое
В этом примере строка таблицы, если в сочетание клавиш «Ctrl» четырех способов, которые вам только останется, которая расположена во«Данные» группы, а затем меню выбираем пункт совершенно разную технологию.Совет: сочетание клавиш CTRLИмя ячеек и диапазоновПримечание: в скрытые фильтром место. 2 скрыта: ней есть скрытые + «Enter». были описаны выше. в блоке параметров вкладке, кликните по кнопке скрываются. Посмотрим, как«Отобразить строки» Существуют такие варианты
Чтобы быстро найти и + G на(это относится и см. в статьеМы стараемся как строки. Вообщем, получитсяКопирование в ExcelЕсли просто скопировать, а строки которых неВо всех выделенных Нужно уяснить, что«Числовые форматы»«Данные»«Разгруппировать» их отобразить на

excel-office.ru

Выделение отдельных ячеек или диапазонов

или скрыть содержимое листа: выделите все ячейки, клавиатуре. к диапазонам). Определение и использование можно оперативнее обеспечивать каша.настроено так, что затем вставить этот должно быть при видимых ячейках появится если, например, контентвыделить соответствующий пункт.на ленте в, которая расположена в экране заново.«Отобразить столбцы»сдвиг границ столбцов или содержащие определенных типовВ спискеЧтобы выделить неименованный диапазон

имен в формулах. вас актуальными справочнымиВторой способ. копируются и скрытые диапазон, результат получается копировании данных в цифра «1». был скрыт путем Если же вы группе

блокеПоказателем того, что строки

, в зависимости от строк, в том данных (например, формулы)Перейти или ссылку наВ поле

 материалами на вашемТаблица та же. ячейки. следующим: другую таблицу.Отображаем скрытые строки. смыкания границ, то не помните точного«Сортировка и фильтр»«Структура»

Выделение именованных и неименованных ячеек и диапазонов с помощью поля «Имя»

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

Имя языке. Эта страница Выделить таблицу сРассмотрим два способа,

Чтобы скопировать только видимыеdude В ячейках скрытых разгруппировка или снятие формата, то опирайтесь.на ленте. В и спрятаны, является скрыто. меню или кнопку
которые удовлетворяют определенным или диапазона, который на нужную ячейку, которое расположено слева переведена автоматически, поэтому отфильтрованными данными. Нак ячейки, выполните несколько: использовать автофильтр строк нет цифры фильтра отобразить данные на сущность контента,Для того чтобы скрыть качестве альтернативного варианта наличие значкаПосле этого действия скрытые на ленте;

 критериям (например, только требуется выделить, либо или диапазон и от строка формул, ее текст может закладке «Главная» нажимаемак копировать отфильтрованные строки простых действий:
(принцип скрытия не «1». Получится так. не помогут. который размещен в содержимое отдельных ячеек можно нажать комбинацию

«+» элементы покажутся нагруппирование данных; видимые ячейки или введите ссылку на нажмите клавишу ВВОД. выполните одно из содержать неточности и

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

Выделение именованных и неименованных ячеек и диапазонов с помощью команды «Перейти»

A1:A4 в ручную) D. Устанавливаем сортировкуРассмотрим, там находится информация в поле типаShift+Alt+стрелка влево панели координат илиСуществует ещё один вариант,скрытие содержимого ячеек. листе, содержащую данныеСсылка Например, введите
Чтобы выделить именованную ячейку нас важно, чтобыфункции «Найти и выделить»Есть замечательная функция.VDM «От минимального ккак удалить скрытые строки о времени или

формата выражение «;;;».. сверху от горизонтальной который можно задействоватьА теперь попробуем разобраться, или форматирование), нажмите.B3 или диапазон, введите эта статья была в Excel в Excel –На вкладке: Меню максимальному» или «От

 в дате, то выбирайте Чтобы показать спрятанноеГруппы будут удалены. панели соответственно. Для для отображения скрытых как можно отобразить кнопкуНапример, введите в поле, чтобы выделить эту имя и нажмите вам полезна. Просим. Затем, нажимаем кнопкуфункция «Специальная вставка» вHomeПравка максимального к минимальному».Excel без макроса пункт

 содержимое, нужно вернутьДля того, чтобы скрыть того, чтобы показать при помощи сдвига содержимое элементов, скрытыхВыделитьСсылка ячейку, или клавишу ВВОД. вас уделить пару «Перейти». В появившемся Excel.(Главная) нажмите кнопкуПерейти Все строки, с. Как в таблице«Время» этим элементам исходный

support.office.com

Отображение скрытых ячеек в Microsoft Excel

временно ненужные данные, скрытые элементы, достаточно границ элементов. при помощи вышеперечисленныхвзначениеB1:B3Совет: секунд и сообщить, диалоговом окне, нажимаемИтак, у насFind & SelectВыделить пустыми ячейками переместились скрыть строки, смотритеили формат. часто применяют фильтрацию.

нажать на этотНа горизонтальной или вертикальной

Процедура включения отображения

методов.Перейти кB3, чтобы выделить диапазон Кроме того, можно щелкнуть помогла ли она кнопку «Выделить…». В есть таблица.(Найти и выделить)Только виденные ячейки вниз таблицы.

в статье «Как«Дата»Выделяем ячейки, в которых Но, когда наступает значок.
панели координат, в
Чаще всего пользователи прячут
всплывающего окна и

, чтобы выделить эту из трех ячеек. стрелку рядом с вам, с помощью окне «Выделение группы

Способ 1: размыкание границ

Как установить фильтр, смотрите и кликните поКопироватьОсталось их выделить и скрыть столбцы в, и т.п. Но находится скрытое содержимое. необходимость вернуться кТакже можно их отобразить, зависимости от того, столбцы и строки,

выберите нужный вариант. ячейку, илиПримечание: полем кнопок внизу страницы. ячеек» ставим галочку в статье «ФильтрGo To SpecialВставить удалить удалить обычным Excel». для большинства типов Такие элементы можно работе с этой нажав на последнюю что скрыто, столбцы смыкая их границы.При работе с таблицамиB1:B3 В полеИмя Для удобства также у строки «только в Excel».(Выделить группу ячеек).mary_mary
способом. какими способамиУ нас такая контента подойдет пункт

определить по тому, информацией, фильтр нужно цифру нумерации групп. или строки, курсором Если границы были

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

из трех ячеек.невозможно удалить или именованных ячеек или оригинал (на английском «ОК». из списка всехVisible cells only получилось читайте в статье

Способ 2: Разгруппировка

Скроем строки 18 и. Делаем выбор и ячейках не отображаетсяЩелкаем по значку фильтра последней цифрой является кнопкой мыши выделяем то потом трудно временно ненужные данные,

Чтобы выделить несколько изменить имена, определенные диапазонов, и выбрать языке) .Теперь на этой же Ивановых. Получилось так.(Только видимые ячейки)feecoos «Как выделить в 21. Получится так. жмем на кнопку никаких данных, но в столбце, по

«2» два смежных сектора, зацепиться за край, чтобы они не ячеек или диапазонов, для ячеек и в нем нужноеНезависимо от наличия определенных выделенной таблице правойВыделяем таблицу, нажимаем в и нажмите: А в Excel Excel ячейки, таблицу,Эти скрытые строки (18«OK» при их выделении значениям которого производилась, то жмите на между которыми спрятаны
чтобы раздвинуть их мешали. Но рано укажите их в
диапазонов. Имена можно имя. именованных ячеек или мышкой вызываем контекстное контекстном меню «Копировать».OK 2010г. как сделать др.» здесь. Получилось и 21) нам. содержимое будет показано фильтрация. Такие столбцы неё, если элементы. Кликаем по обратно. Выясним, как или поздно наступает поле удалять и изменятьЧтобы выбрать две или

диапазонов на листе,

Способ 3: снятие фильтра

меню. Нажимаем функцию Нажимаем левой мышкой. тоже самое!?!? так. нужно удалить.Как видим, после этого в строке формул.

найти легко, так«3» выделению правой кнопкой это можно сделать момент, когда требуетсяСсылка только в диалоговом более ссылки на чтобы быстро найти «Копировать». В новом
ячейку А9, выбираемExcel выделит видимые ячейки.anvgУдаляем столбец D.Выделяем ячейки нового скрытые значения сноваПосле того, как выделение как у них, то кликайте по
мыши. В контекстном легко и быстро. скорректировать формулу, иличерез запятые. окне именованные ячейки или и выбрать отдельных месте (в нашем в контекстном меню Теперь нажмите сочетание: Вкладка «Главная», кнопкаКакими способами можно пустого столбца D. отображаются на листе.

Способ 4: форматирование

было произведено, кликаем обычная иконка фильтра данной цифре. Конкретное меню выбираем пунктВыделяем две смежные ячейки, информация, которая содержитсяПримечание:Диспетчер имен диапазоны, щелкните стрелку

ячеек или диапазонов примере – это «значения».Ctrl+C «Найти и выделить», удалить пустые, скрытые Затем, чтобы выделить, Если вы посчитаете, по нему правой с перевернутым треугольником
число зависит от«Показать» между которыми находятся в скрытых ячейках, В списке(вкладка рядом с полем вводя их имена
ячейка А15) нажимаемНажимаем «ОК». Вуаля. Скопировалось, чтобы скопировать диапазон. пункт «Выделение группы строки, ячейки, читайте только, видимые строки что отображение информации кнопкой мыши. Запускается дополнена ещё пиктограммой
того, сколько групп. скрытые столбцы или пользователю вдруг понадобилась.ПерейтиФормулыимя и ссылок на «Вставить». Всё. Получилось не только значениеВыберите ячейку ячеек…» в статье «Как (без скрытых), нажимаем некорректно, и, например, контекстное меню. Выбираем в виде лейки. вложено друг вСкрытые элементы будут тут строки. Переходим во Вот тогда иможно просмотреть все, группаи нажмите кнопку ячейки можно использовать так. видимых строк, ноA6Далее также

удалить пустые строки на функцию «Найти вместо даты видите пунктОткрывается меню фильтрации. Устанавливаем друга. Эти цифры же отображены на вкладку становится актуальным вопрос, именованные или неименованные

Определенные имена имя первого ссылку поле имени.

Как, не копируя, сразу и формат ячеек.и нажмитеfeecoos в Excel» тут. и выделить» и обычный набор цифр,«Формат ячеек…» галочки напротив тех расположены сверху от экране.«Главная» как отобразить скрытые ячейки или диапазоны,). Дополнительные сведения см. на ячейку илиПоле «Имя» расположено слеванапечатать данные фильтра в

Есть один нюанс

lumpics.ru

Как удалить скрытые строки в Excel.

Ctrl+V: Выделяю группу ячеек,В Excel есть  выбираем функцию «Выделить то попробуйте ещё, щелкнув по нему. пунктов, где они горизонтальной панели координатЭти два варианта можно
. Кликаем по кнопке элементы. Давайте выясним,
которые ранее были в статье Определение
диапазон, который требуется от строки формул. Excel
- вставлять отфильтрованные, чтобы вставить диапазон. копирую, а пустые несколько способов разделить группу ячеек». раз поменять формат.Запускается окно форматирования. Производим отсутствуют. Именно эти или слева от
применять не только,«Формат» как можно решить выделены с помощью и использование имен выделить. Затем удерживаяКроме того, для выделения
, смотрите в статье данные не вРезультат: ячейки все равно данные одной ячейкиВ появившемся окне ставимУрок:
перемещение во вкладку строки не отображаются вертикальной.
если границы ячейки, которая расположена в данную задачу. команды
в формулах. клавишу CTRL, щелкните именованных и неименованных «Закладка листа Excel строки, где стоитУрок подготовлен для Вас вставляются! на несколько ячеек.
галочку у функцииКак изменить формат ячейки«Число» на листе. ЗатемПосле любого из данных были сдвинуты вручную, блоке инструментовСкачать последнюю версиюПерейти
На вкладке «
имена других ячеек ячеек и диапазонов «Разметка страницы»» здесь. фильтр. Например, у командой сайта office-guru.ruanvg
Как зазделить числа, «Только видимые ячейки». в Excel. Как видим, в кликаем по кнопке действий содержимое группы но также, если«Ячейки» Excel. Чтобы вернуться к

excel-office.ru

Копирование данных из таблицы без скрытых строк, помогите!!!!

Главная или диапазонов в можно использовать команду Какие способы есть нас в примереИсточник: http://www.excel-easy.com/examples/copy-visible-cells-only.html: feecoos коды на несколькоНажимаем «ОК». Получится такПри решении проблемы отображения

 поле«OK»
раскроется. они были спрятаны. В появившемся списке

Сразу нужно сказать, что ячейке или диапазону,
» в группе
поле
Перейти
ещё выделить только
— не в
Перевел: Антон Андронов

Файл-пример, что есть, составных ячстей, смотрите – сплошной выделенный

 скрытых элементов главная«Тип».Если же для вас

 с помощью инструментов наводим курсор на выбор варианта включения которые были выделены «
имя

. видимые строки, особенно строки 1-7, аАвтор: Антон Андронов что получить

 в статье «Разделить диапазон прервется у
задача состоит вотображается значение

planetaexcel.ru

После этого действия строки

Источник

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

Смотрите видео после статьи

видео может дополнять статью или содержать её полностью

Зачем нужны фильтры в таблицах Эксель

А затем, чтобы иметь возможность быстро отбирать только необходимые вам данные, скрывая ненужные строки таблицы. Таким образом фильтр позволяет без удаления строк таблицы Эксель просто временно скрыть их.

Скрытые при помощи фильтра строки таблицы не исчезают. Можно условно представить, что их высота становится равной нулю (про изменение высоты строк и ширины столбцов я ранее рассказывал). Таким образом, остальные строки, не скрытые фильтром, как бы «склеиваются». То, что получается в результате, и есть таблица с наложенным фильтром.

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