Сортировка по цвету в MS Excel 2003
 | Приветствую всех сотрудников «Б & К»! В своей работе я использую программу 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 .
