21.01.13

Приветствую всех сотрудников «Б & К»! В своей работе я использую программу MS Excel 2003. В связи с этим у меня вопрос. Подскажите, можно ли в этой программе отсортировать таблицу не по значению, а по формату ячеек? Например, отсортировать данные так, чтобы сгруппировать вначале все строки, отмеченные красным цветом, потом — синим и т. д.? Думаю, что такая задача будет интересна многим читателям. Мне, например, такой возможности в MS Excel очень не хватает.

Забегая вперед скажу, что среди стандартных возможностей MS Excel 2003 сортировка по цвету не предусмотрена. Тем не менее, задача эта выполнима. И мы сейчас в этом убедимся. Но вначале пару слов о том, что имеется в виду и для чего это нужно.

С желанием отсортировать данные по цветам я столкнулся приблизительно в 2000 — 2001 году, работая заместителем главного бухгалтера одной крупной компании. Характер задач, которые мне приходилось решать практически ежедневно, был связан с достаточно нетривиальной обработкой баз данных. Причем базы эти были немаленькие… Понятное дело, что в процессе работы с данными я делал пометки. Проблемные моменты выделял одним цветом, внесенные изменения — другим и т. д. В какой-то момент передо мной неизбежно вставала одна и та же задача: как в отформатированной таблице выделить записи синего цвета? Или собрать вместе все изменения, которые я пометил желтым? Более того. Подобная задача возникала так часто, что мы с главбухом умудрились состряпать письмо в группу разработки Microsoft с предложением дополнить Excel такой удобной возможностью! Понятное дело, что реакции на это телодвижение не последовало. Но в один прекрасный момент все стало на свои места. Оказалось, что для решения проблемы нужна самая малость — создать пользовательскую функцию размером буквально в три строки. И сейчас я предлагаю посмотреть, как это сделать.

Для примера воспользуемся базой данных, фрагмент которой показан на рис. 1. В этой базе собраны сведения о кассовых операциях за сентябрь 2012 года. В исходной базе шесть полей: «Дата» — дата регистрации хозяйственной операции; «СчД», «СчК» — счет дебета и кредита поводки; «Д», «К» — сумма по дебету и кредиту; «Контрагент» — название контрагента. Отдельные записи в базе выделены цветом. Например, группа операций, где фигурирует сотрудник «Ильченко И.Е.», отмечена желтым фоном. Записи о сотруднике «Рудь Н.И.» выделены зеленым и т. д. Теперь наша задача — упорядочить таблицу, используя в качестве признака сортировки цвет заливки. В результате получится, что записи о каждом сотруднике будут собраны в один блок, анализировать их будет намного проще.

Стандартных способов сортировать данные с учетом форматирования в Excel 2003 нет. Поэтому нам понадобится создать функцию, которая вернет в качестве результата индекс (номер) цвета для заданной ячейки. Значениями этой функции мы заполним отдельную колонку в исходной таблице. После чего у нас появится возможность упорядочить данные по этой колонке (т. е. — по цветам) при помощи стандартных инструментов сортировки. Начнем с пользовательской функции. Делаем так:

1. Открываем рабочую книгу MS Excel. Вызываем меню «Сервис → Макрос → Редактор Visual Basic» (в некоторых версиях MS Office можно воспользоваться комбинацией «Alt+F11»). Откроется окно редактора «Visual Basic for application», изображенное на рис. 2.

2. В этом окне вызываем меню «Insert → Module» (вставить модуль). Откроется область для ввода текста программы.

3. Печатаем текст модуля, который выглядит так:

Public Function ColorCeil(Cell As Range)

ColorCeil = Cell.Interior.ColorIndex

End Function

4. Закрываем окно «Visual Basic», возвращаемся в рабочую книгу Excel с базой данных (рис. 1).

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

1. Становимся в свободную колонку на рабочем листе. В базе на рис. 1 я выбрал столбец «G».

2. В ячейку «G1» печатаем заголовок колонки (на рис. 1 это текст «Пр»).

3. Переходим на ячейку «G2».

4. Вызываем меню «Вставка → Функция…». Откроется окно Мастера функций, изображенное на рис. 3.

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

6. Из этого списка выбираем «ColorCeil». Откроется окно для ввода параметров функции (рис. 4).

7. Оставаясь в области для ввода параметров, щелкаем на ячейке «A2», — мы будем сортировать строки, используя цвет заливки ячеек в первой колонке таблицы.

8. В окне настройки параметров нажимаем «ОК».

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

10. Выделяем базу данных.

11. Вызываем меню «Данные → Сортировка…». Откроется окно настройки параметров, как на рис. 5.

12. Щелкаем на значке выпадающего списка «Сортировать по». Из предложенных вариантов выбираем «Пр».

13. Устанавливаем переключатель направления сортировки (на рис. 5 он имеет значение «по возрастанию»).

14. В окне настройки параметров сортировки нажимаем «ОК». Excel отсортирует базу данных по значениям в колонке «Пр», как показано на рис. 6. Иными словами, он отсортирует записи с учетом цвета заливки, который указан для ячеек в первой колонке исходной базы данных.

Важно! Excel не считает изменение цвета редактированием ячейки и поэтому не обновляет значения на рабочем листе. Как следствие, после изменения цвета заливки результат функции «ColorCeil» автоматически обновляться не будет. Это можно проделать вручную, воспользовавшись комбинацией «Ctrl+Alt+F9». Однако на результат сортировки такая ситуация не влияет — в данном случае обновление функции Excel делаем своевременно.

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

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

1. Открываем таблицу, изображенную на рис. 1.

2. Становимся на свободную колонку. Пусть это будет столбец «H» (напомню, что в колонке «G» у нас находится функция для определения цвета заливки).

3. В ячейку «H1» вводим название колонки, например, «Раб».

4. В ячейку «H2» вводим число «1». В ячейку «H3» вводим значение «2».

5. Выделяем на рабочем листе блок «H2:H3».

6. Ставим указатель мышки на прямоугольный маркер в правом нижнем углу выделенного блока.

7. Когда курсор изменит свою форму, удерживаем левую кнопку мышки и растягиваем блок на всю высоту таблицы. Excel проанализирует выделенные ячейки и заполнит колонку «H» элементами арифметической прогрессии с шагом «1». В результате этих действий в ячейки колонки «H» будут записаны значения «1», «2», «3» и т. д. Это и есть номера записей в исходной базе данных.

Теперь в любой момент можно отсортировать таблицу по колонке «Раб», и строки станут в том порядке, как они были выстроены в исходной базе данных.

И последнее. На первый взгляд, заполнить колонку «Раб» можно при помощи формул. Например, ввести в «H2» значение «1», в «H3» написать формулу «=H2+1» и скопировать ее вниз до конца таблицы. На самом деле это не так. При сортировке базы данных будет нарушена адресация ячеек. А в результате вместо значений формулы вернут сообщение об ошибке. Поэтому заполнение рабочей колонки копированием (в режиме прогрессии) в данном случае принципиально.

На сегодня все. Удачной работы! Жду ваших вопросов, замечаний и предложений на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum.

Тисни «Нравится» і отримуй новини раніше за всіх!↓

Тисни «Нравится» і отримуй новини раніше за всіх!

Помітили помилку? Виділіть її та натисніть Ctrl+Enter, щоб повідомити нас про це

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

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

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

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

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

• Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
• Применение пользовательских функций.

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

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

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

С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

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

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

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

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Public Function ColorFill(MyCell As Range)     ColorFill = MyCell.Interior.ColorIndex End Function

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

Вернемся в Excel и применим новую функцию ColorFill — либо непосредственно введем формулу в ячейку, либо вызовем ее с помощью мастера функций (выбрав из категории Определенные пользователем).
В дополнительном столбце прописываем код заливки ячейки:

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

Функция цвета текста ячейки на VBA

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

Public Function ColorFont(MyCell As Range)     ColorFont = MyCell.Font.ColorIndex End Function

Функция ColorFont в качестве значения возвращает числовой код цвета шрифта ячейки и принцип ее применения аналогичен примеру рассмотренному выше.

Замечания

К сожалению, при работе с пользовательскими функциями ColorFill и ColorFont есть несколько подводных камней:

• Они не работают с ячейками, в которых заливка определяется условным форматированием;
• При изменении раскраски ячейки в Excel формулы автоматически не пересчитываются, в связи с этим пересчет нужно сделать самостоятельно (Shift + F9 для пересчета формул только на активном листе, F9 — для всей книги).

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Создатели Excel решили, начиная от 2007-ой версии ввести возможность сортировки данных по цвету. Для этого послужило поводом большая потребность пользователей предыдущих версий, упорядочивать данные в такой способ. Раньше реализовать сортировку данных относительно цвета можно было только с помощью создания макроса VBA. Создавалась пользовательская функция и вводилась как формула под соответствующим столбцом, по которому нужно было выполнить сортировку. Теперь такие задачи можно выполнять значительно проще и эффективнее.

Сортировка по цвету ячеек

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

Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:

1. Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
2. Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
3. Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
4. Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.

Ожидаемый результат изображен ниже на рисунке:

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

Фильтр по цвету ячеек

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

1. Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
2. Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
3. Из всплывающего подменю выберите зеленый цвет.

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

Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:

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

Николай КАРПЕНКО, канд. техн. наук, доцент каф. прикладной математики и информационных технологий Харьковской национальной академии городского хозяйстваСортировка по цвету в MS Excel 2003
Б&К, 2012, № 22(142), с. 23 Приветствую всех сотрудников «Б & К»! В своей работе я использую программу MS Excel 2003. В связи с этим у меня вопрос. Подскажите, можно ли в этой программе отсортировать таблицу не по значению, а по формату ячеек? Например, отсортировать данные так, чтобы сгруппировать вначале все строки, отмеченные красным цветом, потом — синим и т. д.? Думаю, что такая задача будет интересна многим читателям. Мне, например, такой возможности в MS Excel очень не хватает. Спасибо.

Владимир Славин, главный бухгалтер, г. Харьков