Начиная с 2007 версии Excel стала доступна сортировка по форматированию, а именно: по цвету заливки ячейки, по цвету шрифта ячейки и по значку (устанавливается через условное форматирование).
Последовательность действий:
1. Выделить любую ячейку таблицы.
2. На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Сортировка [Sort].
3. Выбрать в поле Столбец [Column] поле, по которому будет производиться сортировка.
4. В поле Сортировка [Sort On] выбрать по какому критерию будет сортировка: цвет ячейки (заливка), цвет шрифта или значок ячейки.
5. В зависимости от критерия сортировки в поле Порядок [Order] выбрать цвет или значок ячейки, а так же схему упорядочения Сверху [On Top] или Снизу [On Bottom].
6. Нажать кнопку Копировать уровень [Copy Level], чтобы быстро задать следующий уровень сортировки, изменив в поле Порядок цвет или значок. Повторить нужное количество раз.
7. ОК.
Результат:
Упорядочение данных в программе возможно по встроенным закономерностям, тем не менее периодически возникают ситуации, когда значения нужно отсортировать не в алфавитном порядке, а к каком-то ином порядке, в соответствии со значимостью элемента в списке.
Отсортируем в данной таблице записи по отделам в нужной нам последовательности:
- Выделить любую ячейку таблицы.
Если исходный диапазон содержит пустые строки и/или столбцы, то необходимо выделить всю таблицу вместе с заголовками.
- На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Сортировка [Sort].
- Выбрать столбец для сортировки.
Для сортировки по дням недели, названиям месяцев или произвольному списку значений в списке поля Порядок выбрать Настраиваемый список.
Либо выбрать существующий список, либо, выбрав НОВЫЙ СПИСОК и ввести элементы списка, разделяя их между собой через запятую или через Enter.
Можно будет выбрать порядок сортировки:
И результат не заставит себя ждать:
Примечание: Если элементы будущего списка располагаются в ячейках, то вводить заново не имеет смысла, можно их импортировать:
- Выбрать Файл [File], Параметры [Option].
- В разделе Дополнительно [Advanced] в группе Общие [General] нажать на кнопку Изменить списки [Edit custom Lists].
- Поставить курсор в поле Импорт списка из ячеек [Import list from cells], выделить ячейки на листе и нажать кнопку Импорт [Import].
- Закрыть все окна, нажатиями на кнопки ОК.
Во всех версиях Excel, начиная с 2007, есть возможность удалить дубликаты (повторяющиеся данные) из таблицы. Данную команду можно применять как к произвольному диапазону ячеек, так и ко всей таблице – в этом случае из таблицы будут удалены полностью строки, которые совпадают с другими (останутся только уникальные записи в таблице).
К сожалению, алгоритм, заложенный в программе не позволяет посмотреть повторяющиеся записи.
Можно воспользоваться следующим алгоритмом действий:
- В таблицу следует добавить столбец нумерации.
- Создать копию таблицы – можно на этом листе, можно сделать и копию всего листа.
- В одной из таблиц удалить дубликаты, для этого выделить любую ячейку таблицы и на вкладке Данные [Data] в группе Работа с данными [Data Tools] выбрать команду Удалить дубликаты [Remove Dublicates]. Необходимо выполнить проверку на совпадение по данным всех столбцов, кроме столбца №:
- С помощью условного форматирования оформим строки-дубликаты.
Для этого нужно выделить диапазон ячеек (в таблице, где не удалялись дубликаты) и на вкладке Главная [Home], в группе Стили [Styles], раскрыв кнопку Условное форматирование [Conditional Formatting], выбрать команду Создать правило [New Rule] и в появившемся окне – Использовать формулу для определения форматируемых ячеек [Use a formula to determine which cells…]. Ввести формулу: =НЕ(ИЛИ($A$2:$A$51=$L2)) или в англоязычном варианте =NOT(OR($A$2:$A$51=$L2)) и задать вариант форматирования.
- Далее по форматированию можно произвести сортировку или фильтрацию, чтобы сформировать список удаленных записей и тем самым увидеть все повторяющиеся записи в таблице.
