Содержание
- Фильтр и сортировка ячеек по цвету в Excel
- Как в Excel фильтровать ячейки по цвету заливки
- Расширенный фильтр
- Стандартный фильтр и сортировка по цвету в Excel
- Сортировка по цвету в Excel
- Как отменить сортировку после сохранения документа Эксель
- Порядок сортировки в Excel
- Обычная (простая) сортировка
- Сортировка строк по столбцу в Excel
- Срезы
- Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции
- Фильтр и сортировка по цвету ячеек в Excel
- Пользовательская сортировка в Excel
- Применение расширенного фильтра
- Сортировка и фильтр по цвету с помощью функций
- Отфильтровать столбец по цвету в Экселе (Как отфильтровать столбец таблицы по цвету)
- Стандартный фильтр и сортировка по цвету в Excel
- Сортировка в Excel по цвету заливки ячейки
Фильтр и сортировка ячеек по цвету в Excel
Рассмотрим основные способы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.
Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?
При проведении анализа данных сортировка и фильтрация помогают структурировать, упорядочивать данные или производить поиск. К примеру, ячейки или диапазоны данных можно окрашивать в различные цвета с целью выделения конкретных данных, которые в последствии можно дополнительно проанализировать.
При этом фильтрация позволяет показать только те данные, которые удовлетворяют заданным критериям, и соответственно, скрыть те данные, которые не подходят по условию. Сортировка же показывает все данные, однако упорядочивает их согласно заданным критериям.
В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:
- Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
- Применение пользовательских функций.
Как в Excel фильтровать ячейки по цвету заливки
Если нужно отфильтровать ячейки в заданном столбце по цвету заливки, используйте инструмент Фильтр по цвету (Filter by Color), доступный в Excel 2010 и 2013.
Для работы этого инструмента существует ограничение – возможно отфильтровать одновременно только один цвет. Если нужно настроить фильтр для данных по двум или более цветам, выполните следующие шаги:
- Создайте вспомогательный столбец в конце таблицы или рядом со столбцом, в котором будете настраивать фильтр. Мы назовём его Filter by color.
- Во вторую ячейку только что добавленного столбца введите формулу:
Здесь F – это столбец, содержащий разукрашенные ячейки, и по которому нужно настроить фильтр, а GetCellColor() – пользовательская функция со следующим кодом VBA:
- Скопируйте эту формулу во все ячейки столбца Filter by color.
- Примените автофильтр Excel и затем выберите нужные цвета по их кодам в выпадающем меню автофильтра.
В результате получится вот такая таблица, где показаны строки только с двумя выбранными цветами заливки:
На этом, пожалуй, всё на сегодня, благодарю за внимание!
Расширенный фильтр
Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.
Задание условий фильтрации
- В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте или скопировать результат в другое место .
- Указать Исходный диапазон , выделяя исходную таблицу вместе с заголовками столбцов.
- Указать Диапазон условий , отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
- Указать при необходимости место с результатами в поле Поместить результат в диапазон , отметив курсором ячейку диапазона для размещения результатов фильтрации.
- Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи .
Стандартный фильтр и сортировка по цвету в Excel
Предположим, что у нас имеется таблица с наименованиями и выручкой по различным продуктам, где определенные категории дополнительно выделены цветом заливки ячейки и текста:
Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:
Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.
С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.
Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):
В результате синие ячейки расположились вверху таблицы, однако остальные идут вразнобой и не упорядочены. На втором шаге повторно произведем сортирование данных, но на этот раз выберем в качестве критерия сортировки красную заливку:
Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:
Сортировка по цвету в Excel
наихудшие показатели по только по значениям, указываем следующие параметры:
- следует их аккуратноВ разделе
- На ленте, щелкните языке. Эта страница семейные дела – возможность выстроить необходимый что значения не В таблице приведенДля этого выделяем столбецНажмите
- Excel 2007 и более функция фильтра по
- ReDim ar(UBound(arr) - -фильтруете-> незалитые ячейки чистой прибыли, которые а даже по «Столбец» – Чистая разъединить, так как
- Общиеданные переведена автоматически, поэтому голубым, праздники – список штатными средствами, содержат начальных пробелов,
- пример сортировки чисел таблицы «Стоимость, руб.»ОК поздние версии позволяют цвету, но фильтра
1): ReDim ar1(UBound(arr) скрываете имеют наихудшие показатели. цветам шрифта или прибыль; «Сортировка» –
- для сортировки такойнажмите кнопку> ее текст может бежево-желтым. И выстроим
- бывает необходимость выполнить так как в 10, 11, 100,
и заходим на
. сортировать данные по только по ОДНОМУ — 1)
Michael_S
office-guru.ru>
Как отменить сортировку после сохранения документа Эксель
Когда работа Excel сохранена, и пользователь закрыл документ, то все данные из буфера обмена автоматически удаляются. Это означает, что кнопка «Отменить» не будет работать при следующем запуске файла, и убрать сортировку таблицы таким способом не получится. В сложившейся ситуации опытные специалисты рекомендуют сделать ряд простых шагов по алгоритму:
- Запустить файл Excel, убедиться, что предыдущая работа сохранена и отображается на рабочем листе.
- Кликнуть правой клавишей мышки по названию самого первого столбца в табличке.
- В контекстном окошке щелкнуть по строчке «Вставить». После подобного действия в таблице создастся вспомогательный столбец.
- В каждой строке вспомогательного столбика нужно задать порядковый номер для последующих столбцов. К примеру, от 1 до 5 в зависимости от количества ячеек.
Внешний вид созданного вспомогательного столбца перед первым столбиком в табличном массиве
- Теперь надо отсортировать данные в табличном массиве любым удобным способом. О том, как это сделать, было рассказано выше.
- Сохранить документ и закрыть его.
Сохранение документа Excel. Простой алгоритм действий, показанный на одном скриншоте
- Еще раз запустить файл Microsoft Office Excel и отсортировать вспомогательный столбец по возрастанию, выделив его полностью и выбрав из списка вкладки «Сортировка и фильтр» соответствующий вариант.
- В итоге вся таблица должна отсортироваться как вспомогательный столбик, т.е. принять первоначальный вид.
- Теперь можно удалить первый столбец во избежание путаницы и сохранять документ.
Отсортировать данные в табличке Эксель можно и вручную, проведя определенные расчеты, меняя значения в столбцах и строчках между собой. Однако данный процесс отнимает много времени у пользователя. Проще применить встроенный в программное обеспечение инструмент, предназначенный для выполнения поставленной задачи. Кроме того, нужные параметры можно отсортировать по цвету и размерам ячеек.
Сортировка данных в таблице цвету. Для выполнения поставленной задачи понадобится функция вспомогательной сортировки
Порядок сортировки в Excel
Существует два способа открыть меню сортировки:
- Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
- Открыть вкладку «Данные» – диалоговое окно «Сортировка».
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
- Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
- Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Обычная (простая) сортировка
Эта опция называется простой, потому что ее несложно использовать даже новичкам. В результате сортировки информация автоматически организуется в установленном порядке. Например, можно составить строки таблицы по алфавиту или упорядочить числовые данные от мелких к крупным.
Быстрая организация данных в таблице возможна благодаря набору инструментов «Сортировка и фильтр». Эта кнопка находится на вкладке «Главная», в правой ее части. Опции сортировки соответствуют формату выбранных ячеек.
- Проверим работу инструментов в действии. Для этого откроем существующую таблицу с большим количеством записей, выберем ячейку в столбце текстового формата и откроем меню сортировки и фильтра. Появятся две опции. Представим, что нужно рассортировать список имен по алфавиту. Нужно кликнуть по кнопке «Сортировка от А до Я». Записи распределятся в указанном порядке.
1
- Попробуем развернуть таблицу в обратную сторону – от конца к началу. Вновь необходимо открыть меню сортировки, теперь выбираем функцию «Сортировка от Я до А».
2
- Рассортировать числа тоже возможно, но опции для этого появляются только после выбора ячейки числового формата. Кликнем по одной из таких ячеек и откроем «Сортировку и фильтр». В меню появятся новые функции – «По возрастанию» и наоборот. Для дат заготовлены опции сортировки «От старых к новым» и в обратную сторону.
Сортировка строк по столбцу в Excel
Ниже на рисунке представлена выборка информации из базы данных по сотрудникам фирмы:
Допустим нам необходимо отсортировать таблицу относительно столбца «Премия» по убыванию сумм. Для решения этой задачи воспользуемся самыми простыми (базовыми) инструментами сортировки в Excel.
Перейдите курсором клавиатуры на любую ячейку в столбце G (можно даже на заголовок столбца G1). А Потом выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».
В результате данные упорядочились и организовались в определенную структуру:
Как оказалось, наибольшую премию получил сотрудник «Евгений Пруткий».
Внимание! Список, который будет сортирован не должен содержать объединенных ячеек. Примечание
Инструмент для базовой сортировки диапазонов доступен так же на закладке: «ДАННЫЕ»-«Сортировка по возрастанию/по убыванию»
Примечание. Инструмент для базовой сортировки диапазонов доступен так же на закладке: «ДАННЫЕ»-«Сортировка по возрастанию/по убыванию».
И встроен в выпадающие меню заголовков таблиц в режиме автофильтра или умной таблицы:
Так же встроен в контекстное меню, которое появляется по нажатию правой кнопкой мышки:
Как убрать сортировку в Excel
Если таблицу необходимо будет привести в изначальный вид, тогда перед каждой сортировкой создавайте столбец нумерации строк. В данном примере – это столбец А (№п/п). Часто необходимо привести таблицу в изначальный вид после выполнения с ней многочисленных и сложных сортирований данных по разным столбцам, критериям или условиям. Тогда достаточно выполнить сортировку по столбцу с номерами строк, чтобы таблица приняла свой первоначальный вид.
Как в Excel сделать сортировку в столбце
Иногда нужно выполнить независимую сортировку только лишь внутри одного столбца таблицы без привязки к другим столбцам или их изменения. Например, столбец B «Имя». Такая задача решается так же просто:
- Выделите целый столбец B щелкнув левой мышкой по заголовку столбца листа.
- Выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».
- В появившемся диалоговом окне «Обнаруженные данные в неуказанного диапазона», выберите опцию: «сортировать в пределах указанного выделения». И нажмите на кнопку «Сортировка».
Обратите внимание на то, что значения ячеек в остальных столбцах остались без изменений:
Такой способ необходимо применять только как вспомогательный.
Срезы
Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.
Создание срезов
В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.
Для этого нужно выполнить следующие шаги:
-
Выделить в таблице одну ячейку и выбрать вкладку Конструктор .
- В группе Сервис (или на вкладке Вставка в группе Фильтры ) выбрать кнопку Вставить срез .
- В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
Форматирование срезов
- Выделить срез.
- На ленте вкладки Параметры выбрать группу Стили срезов , содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.
- Выбрать кнопку с подходящим стилем форматирования.
Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.
Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции
Чтобы в Microsoft Office Excel выбрать фильтр для отображения сразу нескольких цветов в таблице, необходимо создать дополнительный параметров с оттенком заливки. По созданному оттенку данные в дальнейшем и будут фильтроваться. Пользовательская функция в Эксель создается по следующей инструкции:
- Зайти в раздел «Разработчик», который находится сверху главного меню программы.
- В открывшейся области вкладки кликнуть по кнопке «Visual Basic».
- Откроется встроенный в программу редактор, в котором потребуется создать новый модуль и прописать код.
Код программы с двумя функциями. Первая определяет цвет заливки элемента, а вторая отвечает за цвет внутри ячейки
Чтобы применить созданную функцию, необходимо:
- Вернуться на рабочий лист Excel и создать два новый столбца рядом с исходной таблицей. Их можно назвать «Цвет ячейки» и «Цвет текста» соответственно.
Созданные вспомогательные столбцы
- В первом столбце написать формулу «= ColorFill()». В скобках указывается аргумент. Нужно кликнуть по ячейке с любым цветом в табличке.
Формула в столбце «Цвет ячейки»
- Во втором столбике указать тот же самый аргумент, но только с функцией «=ColorFont()».
Формула в столбце «Цвет текста»
- Растянуть получившиеся значения до конца таблицы, продлив формулу на весь диапазон. Полученные данные отвечают за цвет каждой ячейки в таблице.
Получившееся данные после растягивания формулы
- Добавить фильтр в табличный массив по рассмотренной выше схеме. Данные будут отсортированы по цвету.
Фильтр и сортировка по цвету ячеек в Excel
Как это можно: Для отслеживания сроков ячейки. Для этого такие задачи можно.На вкладке таблицу в заголовки подсказка «(отображается все)». данных. хотите отфильтровать. «больше» и «первыенажмите кнопкуДанные можно быстро фильтроватьПодскажите, как отфильтровать функция фильтра поarr = .Value: Можно добавить сколько исправить? возврата сопроводительных писем достаточно только указать выполнять значительно прощеExcel Online применяет фильтр
Сортировка по цвету ячеек
данные ее столбцов автоматическиКнопка фильтра _з0з_ означает,Примечание:
Снимите флажок 10″ в _з0з_Фильтр на основании визуальных столбец по нескольким цвету, но фильтра
- ReDim ar(UBound(arr) - угодно уровней(добавить цвет)_Boroda_ на столбе отправки соответствующий критерий в
- и эффективнее. и показывает тольконажмите кнопку добавляются элементы управления что фильтр применен. При использовании диалогового окна(выделить все) , чтобы отобразить_з0з_. условий, таких как цветам? только по ОДНОМУ 1): ReDim ar1(UBound(arr)
- -фильтруете-> незалитые ячейки: — Прочитайте Правила стоит условное форматирование. секции «Сортировка» диалоговогоПример данных, которые необходимо
- регионы, в которыхФильтр фильтрацией.Если навести указатель мыши «и установите флажки
нужные данные иЩелкните стрелку
цвет шрифта, цветНапример есть ячейки выбранному цвету. Как — 1) скрываете форума И если в окна настройки условий. отсортировать относительно цвета
Фильтр по цвету ячеек
.Выделите данные, которые нужно на заголовок столбцаПоиск для полей, которые скрыть остальные. Послев столбце с ячейки или наборы желтые, есть красные
- сделать так, чтобы.AutoFilter Field:=1, Operator:=xlFilterNoFillMichael_S- Создайте свою
- этом столбце сделать заливки ячеек изображен 6000 долларов.Можно применить общий фильтр, отфильтровать. На вкладке с фильтром, в
- » для поиска вы хотите вывести.
фильтрации данных в содержимым, которое нужно значков. Можно также и есть зеленые. фильтровался не один
For Each cl: В два этапа тему согласно п.5q фильтр по цвету,Аналогично по отношению к
ниже на рисунке:Аналогичным образом можно применить выбрав пунктГлавная подсказке отображается фильтр, отфильтрованных данных выполняетсяНажмите кнопку диапазоне ячеек или отфильтровать.
exceltable.com>
Пользовательская сортировка в Excel
. или месяцам года. Excel (Столбец А, Excel? Допустим мы Пруткий». произвольном порядке. ВПо умолчанию сортируются данные простую сортировку по имя «Фамилии». содержит значения «Высокий», можете также создаватьOK для собственного порядка которому нужно отсортировать Чтобы их посмотреть,
Выполняем построение ячеек от по выделенному полю,В разделе Кроме того, можно Столбец B, C хотим сортировать сотрудниковВнимание! Список, который будет нашем примере – по столбцам. Как возрастанию/убыванию (алфавиту или
Создание пользовательской сортировки в Excel
В ячейке В1 «Средний» и «Низкий», собственные настраиваемые спискив диалоговом окне сортировки. данные, например «Население». зайдите на вкладку А до Я, или же расширитьОбщие создавать собственные настраиваемые и т.д.). А
- по дате рождения сортирован не должен список фруктов. осуществить сортировку по обратно), то достаточно
- пишем такую формулу. возможно, проще ввести для сортировки поСортировкаВ примере ниже мы
- Если строки заголовков нет, «Данные» и найдите опять же указывая диапазон.нажмите кнопку списки для сортировки при применении всех (от старых к содержать объединенных ячеек.Выделяем столбец и даем строкам в Excel: нажать соответствующую кнопку=ИНДЕКС(Фамилии;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Фамилии;» Для ввода
- непосредственно в поле любым другим параметрам,для того, чтобы хотим отсортировать данные выберите верхнюю ячейку панель инструментов сортировки расширение диапазона операции.
- Выбираем «Расширить», так какИзменить списки по другим характеристика, параметров в сортировку новым) в соответствииПримечание. Инструмент для базовой ему имя «Фрукты».В диалоговом окне «Настраиваемой на панели задач. формулы нажимаем сочетаниеЭлементы списка которые трудно сортировать выполнить пользовательскую сортировку. на листе по
- в столбце, по и фильтрации Excel.При упорядочивании строковых данных мы заинтересованы в. которая не отсортировать будут включены все с каждым городом сортировки диапазонов доступен Для этого в сортировки» нажать кнопку Когда диапазон содержит
- клавиш «Ctrl» +окна по алфавиту (например,Таблица Excel будет отсортирована размеру футболок (столбец которому нужно выполнить По кнопке «Сортировка» убедитесь в том,
- упорядочивании полных записейВ окне в алфавитном порядке строки и ячейки (места рождения). Для так же на
Сортировка в Excel по формату ячейки
поле имен, что «Параметры». более одного столбца, «Shift» + «Enter»,Списки высокий, средний и в требуемом порядке, D). Обычная сортировка сортировку, например ячейку открывается окно выполнения что значения не таблицы, и нажимаемСписки хорошо — например исходного диапазона (фрагмента
- этого: закладке: «ДАННЫЕ»-«Сортировка по находится возле строкиВ открывшемся меню выбрать то Excel открывает
- п.ч. это формула. низкий или S, в нашем случае расставит размеры в
- со значением 452084. операции по нескольким содержат начальных пробелов, «Сортировка». Записи выстроенынажмите кнопку высокий «,» Средний таблицы), что являетсяСначала для данного примера возрастанию/по убыванию». формул вводим нужное «Столбцы диапазона». диалоговое окно вида: массива. Копируем формулуСортировка данных в сводной M, L, XL). — по размеру
- алфавитном порядке, чтоНа вкладке столбцам. так как в в порядке возрастанияИмпорт
- «и» Низкий — правильным результатом в лучше сбросить сортировкуИ встроен в выпадающие нам имя дляНажать ОК. В окнеЧтобы сохранилось соответствие вниз по столбцу. таблице или сводной
Настраиваемые списки позволяют сортировать
office-guru.ru>
Применение расширенного фильтра
Итак, у нас есть таблица с данными, которые требуется отфильтровать.
Для того, чтобы применить инструмент расширенного фильтра нужно выполнить следующие шаги.
Первым делом потребуется создать вторую вспомогательную таблицу с условиями фильтра. Для ее создания необходимо скопировать шапку исходной таблицы и вставить во вспомогательную. Для наглядности поместим дополнительную таблицу сверху, рядом с основной. А также пометим ее, залив другим цветом (это делается для большей наглядности и не обязательно). Вспомогательную таблицу можно поместить абсолютно в любом месте документа, причем, вовсе не обязательно, чтобы она была на том же самом листе, где располагается основная таблица.
Затем приступим к заполнению вспомогательной таблицы данными, которые потребуются для работы. А нужны нам значения из основной таблицы, по которым необходимо отфильтровать данные. В нашем случае, мы хотим отобрать информацию по женскому полу и виду спорта – теннис.
Когда вспомогательная таблица готова, можно приступать к следующему шагу. Поместив курсор на любую ячейку начальной или вспомогательной таблицы, в основном меню программы кликните по вкладке «Данные», выберите из открывшегося блока инструментов “Фильтр” пункт “Дополнительно”.
В результате должно появиться окно с настройками расширенного фильтра.
У данной функции есть два варианта применения: «Скопировать результаты в другое место» и «Фильтровать список на месте».Как можно понять из названий, эти опции отвечают за то, каким образом будет выводится отфильтрованная информация. В первом варианте данные будут выведены отдельно в указанном вами месте документа. Во втором – непосредственно в исходной таблице. Выбираем подходящий вариант (в нашем случае оставляем фильтрацию на месте) и двигаемся дальше.
В поле «Диапазон списка» необходимо указать координаты таблицы (вместе с шапкой). Выполнить это можно, прописав их вручную, либо простым выделением таблицы мышью, щелкнув по небольшой пиктограмме в конце поля для ввода координат
В строке «Диапазон условий» таким же образом указываем координаты вспомогательной таблицы (заголовок и строку с условиями).Хотим обратить внимание на одну немаловажную деталь. Следите за тем, чтобы в выделяемую область не попали пустые ячейки
В противном случае, ничего не получится. По завершении подтвердите указанные координаты нажатием «OK».
В результате проделанных действий в исходной таблице останутся только требуемые нам данные.
Если мы выберем вариант «Скопировать результаты в другое место», то результат будет выведен в указанное нами место, а исходная таблица останется без изменений. При этом в строке «Поместить результат в диапазон» от нас требуется ввести координаты для вывода результата. Можно указать лишь одну ячейку, которая будет самой верхней левой координатой для новой таблицы. В данном случае, выбранная ячейка – A42.
После нажатия кнопки “OK” новая таблица с заданными параметрами фильтрации будет вставлена, начиная с ячейки A42.
Сортировка и фильтр по цвету с помощью функций
Как мы выяснили стандартным способом отфильтровать данные по нескольким цветам не получится, однако это ограничение можно обойти с помощью создания дополнительного параметра с цветом заливки (или текста), по которому далее и будем сортировать или фильтровать данные.
Функция цвета заливки ячейки на VBA
Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:
Visual Basic
Public Function ColorFill(MyCell As Range)
ColorFill = MyCell.Interior.ColorIndex
End Function
1 |
PublicFunctionColorFill(MyCell AsRange) ColorFill=MyCell.Interior.ColorIndex EndFunction |
Функция ColorFill в качестве значения возвращает числовой код цвета заливки ячейки, она очень схожа с функцией, которую мы использовали при подсчете количества и суммирования ячеек по цвету.
Вернемся в Excel и применим новую функцию ColorFill — либо непосредственно введем формулу в ячейку, либо вызовем ее с помощью мастера функций (выбрав из категории Определенные пользователем).
В дополнительном столбце прописываем код заливки ячейки:
Далее мы можем сортировать или применять фильтр к данным уже по новому параметру.
К примеру, для того, чтобы отфильтровать таблицу по красной и зеленой заливке ячеек, в фильтре укажем соответствующие каждой заливке числовые коды:
Функция цвета текста ячейки на VBA
В случае если мы хотим проанализировать ячейки по цвету текста, то нужно будет внести небольшие изменения в код функции:
Visual Basic
Public Function ColorFont(MyCell As Range)
ColorFont = MyCell.Font.ColorIndex
End Function
1 |
PublicFunctionColorFont(MyCell AsRange) ColorFont=MyCell.Font.ColorIndex EndFunction |
Функция ColorFont в качестве значения возвращает числовой код цвета шрифта ячейки и принцип ее применения аналогичен примеру рассмотренному выше.
Отфильтровать столбец по цвету в Экселе (Как отфильтровать столбец таблицы по цвету)
результатов, либо очиститьПо цветуБолее новые версии очки? разной длины иNext принципе, Ваш вариант (можно вручную) в правило в условном тем же диапазоном выполним следующий рядНажмите кнопку фильтрации чисел отображается
.При повторном применении фильтра выполнить поиск по значок фильтр для повторногощелкните
Office 2011 Если и это
появляются разные результаты всем данным, снимитефильтра отображения всех данных.Цвет ячейки
В диапазоне ячеек или не поможет, покажите выборе столбца хочу
1 To UBound(arr) в один заход до 100кб согласно равно фильтр по в предыдущем примере.Щелкните на любую ячейкурядом с заголовкомЧисловые фильтры
Создание таблицы по следующим соображениям. флажок все фильтры._з2з_. Щелкните этот значок,
Для этого: в области диапазона столбца и выберите, для дат отображаетсяможно указать, естьДанные были добавлены, измененыДва типа фильтров чтобы изменить или
Используйте фильтры, чтобы временноЦвет шрифта ячейку с определеннымZ имеющие цвет при 0 To UBound(ar) получится такое проделать? фильтр»-«Сортировка».
., а для текста —Выберите
столбце таблицы. фильтров: по значению Фильтрация данных в видеть только те,и выберите цвет. которому требуется выполнить цвета, иначе фильтр меня два цвета = ar(j) Then Мне сказал что: Всем доброго дня!Serge_007 фильтр»-«Фильтр».Убедитесь, что отмечена галочкойВыделите любую ячейку вТекстовые фильтрыТаблица с заголовкамизначения, возвращаемые формулой, изменились, списка или по таблице которые вы хотите.Использование гистограмм, цветовых шкал фильтрацию. только, имхо, по — розовый и ar1(i — 1) можно.Вопрос следующего содержания:: Фильтр по цветуРаскройте одно из выпадающих опция «Мои данные таблице или диапазоне. Применяя общий фильтр,, чтобы преобразовать верхнюю и лист был критериям. Каждый изРекомендации и примеры дляВыберите любую ячейку в и наборов значковНа вкладке одному… ;( голубой. Как сделать = Empty: Exit200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Макрос4()Как отфильтровать столбец таблицы корректно работает только меню, которые появились содержат заголовки», а и на вкладке вы можете выбрать строку в заголовки пересчитан. этих типов фильтров сортировки и фильтрации диапазоне данных. для выделения данныхДанныеps В «Копилке»
excelworld.ru>
Стандартный фильтр и сортировка по цвету в Excel
Предположим, что у нас имеется таблица с наименованиями и выручкой по различным продуктам, где определенные категории дополнительно выделены цветом заливки ячейки и текста:
ГлавнаяРедактированиеСортировка и фильтрCtrl + Shift + L
С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.
Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):
Настраиваемая сортировкаГлавнаяРедактированиеСортировка и фильтр
Сортировка в Excel по цвету заливки ячейки
Сортировка ячеек в Excel по цвету заливки – это одна из простейших операций, наравне с подсчётом количества ячеек, суммированием и даже фильтрацией. Не потребуются ни код VBA, ни формулы. Мы просто воспользуемся инструментом «Настраиваемая сортировка», который присутствует во всех современных версиях Excel 2013, 2010 и 2007.
- Выделите свою таблицу или диапазон ячеек.
- На вкладке Главная (Home) в разделе Редактирование (Editing) нажмите кнопку Сортировка и фильтр (Sort & Filter) и в появившемся меню выберите Настраиваемая сортировка (Custom Sort).
- В диалоговом окне Сортировка (Sort) настройте параметры слева направо:
- Укажите Столбец (Column), по которому нужно выполнить сортировку (в нашем примере это столбец Delivery);
- В поле Сортировка (Sort On) выберите Цвет ячейки (Cell Color);
- Установите цвет ячеек, которые должны быть вверху;
- В крайнем правом выпадающем списке выберите Сверху (On top).
Чтобы добавить ещё один уровень сортировки с такими же настройками, нажмите кнопку Копировать уровень (Copy Level). Затем в столбце Порядок (Order) выберите второй по порядку цвет. Таким же образом создайте столько уровней сортировки, сколько цветов надо отсортировать в таблице.
Нажмите ОК и проверьте в правильном ли порядке расположились строки с данными.
В нашей таблице вверх переместились строки со значением Past Due (столбец Delivery), за ними следуют строки со значениями Due in и в конце – строки со значением Delivered. Все в точности, как мы настроили в диалоговом окне Сортировка (Sort).
Совет: Если ячейки раскрашены в большое количество различных цветов – не обязательно создавать правило для каждого. Настройте правила сортировки только для тех цветов, которыми отмечены действительно важные данные (в нашем примере это строки со значением Past Due), а все остальные оставьте как есть.
Если требуется отобрать ячейки только одного цвета, то можно воспользоваться ещё более быстрым способом. Кликните по стрелке автофильтра рядом с заголовком столбца, в котором нужно произвести сортировку. В открывшемся меню нажмите Сортировка по цвету (Sort by Color) и выберите, какого цвета ячейки должны оказаться вверху, а какие – внизу. Кстати, выбрав в этом меню пункт Пользовательская сортировка (Custom Sort), как показано на рисунке ниже, можно быстро перейти к диалоговому окну Сортировка (Sort).

Эта тема закрыта для публикации ответов.