Інформація по темі "" | Публікації по тегу
21.01.13
11513 0
Друкувати
Обране

Сортировка по цвету в 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.

env_icon Наш сайт корисний для вас?

Отримуйте першими актуальні новини! env_icon

Підписатися на найактуальнішу розсилку для бухгалтера бюджетної установи

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



Коментарі
(0)
Яка вдача - ви можете стати першим, хто прокоментує цей матеріал.
Залишити коментар:
Ваше ім`я
Коментарі
Шановні колеги!
Довгі роки ми наполегливо працювали, щоб вчасно підставляти плече підтримки всім українським бухгалтерам бюджетного сектору. Ми завжди розуміли, що їм нізвідки чекати допомоги й опори, тому розвивали новинний і консультативний портал власними силами та засобами. За що ви повсякчас віддячуєте нам своєю довірою та прихильністю. Ми дуже цінуємо це!

Та настають складні часи в усьому світі – й дедалі важче стає підтримувати гідний рівень роботи найбільшого в Україні порталу за свій кошт, частково покриваючи витрати завдяки рекламі. Цього вкрай замало!

Buhgalter.com.ua – це не лише стрічка новин із тематичними розділами та корисною інформацією. По той бік монітора щоденно стоять на варті роботи порталу 14 фахівців та чимале технічне забезпечення для безперешкодного функціонування сайту.

Ми сформували максимально комфортну ціну, яка б не «била по кишені», – 30 грн на місяць або 1 грн на день. Передплатники видань «Бюджетна бухгалтерія» та «Оплата праці», як і раніше, продовжують користуватися порталом безкоштовно та без реклами.

Розуміємо, для вас це стало несподіванкою, але в сьогоднішніх умовах ми не можемо діяти інакше. Цілком посильна сума, що дорівнює чашці кави у кав’ярні, підтримає життя вашого улюбленого ресурсу. За це ми віддячимо вам завжди цікавою й важливою інформацією та новими ідеями. Далі буде тільки краще!

Залишайтеся з нами – і давайте підтримувати один одного!

Ваші колеги – команда buhgalter.com.ua
30
грн/
місяц
Це приблизно усього 1 грн/день
Ви можете скасувати в будь-який момент
ic-acc-pay
Для того, чтоб распечатать текст необходимо авторизоваться или зарегистрироваться
Закрити
icon-block
Заважає реклама?
Ви маєте можливість її відключити всього за 30 грн