Сортування за кольором у MS Excel 2003

Сортування за кольором у 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. Інакше кажучи, він відсортує записи з урахуванням кольору заливки, зазначеного для комірок у першій колонці вихідної бази даних.