ПОБЛЕМЫ С СОРТИРОВКОЙ И ФИЛЬТРАЦИЕЙ
Сортировка по значениям в нескольких столбцах

Проблема
Отсортировать данные в одном столбце совсем просто — достаточно щелчком выделить любую ячейку сортируемого столбца и щелкнуть на кнопке "По возрастанию" или "По убыванию" панели инструментов. Но взгляните на этот лист (рис. 1). Здесь данные о продажах по отделам (аксессуары, машины, сервис) — для кого-то такое представление достаточно удобно, но оно затрудняет получение информации о ежедневном состоянии дел. Как вывести данные о продажах по дням?

Рис. 1. Данные о продажах упорядочены по отделам, а мы хотим упорядочить их по дням. Это означает, что лист необходимо отсортировать по нескольким столбцам

Решение
В сущности, задача сводится к сортировке списка данных по значениям нескольких столбцов. Выполните команду Данные->Сортировка и при помощи элементов управления диалогового окна Сортировка (прежде всего раскрывающихся списков) определите очередность полей и порядок сортировки (по возрастанию, по убыванию). На рис. 2 показано, как выглядит список после сортировки по полям День (по возрастанию) и Отдел (по возрастанию).

Рис. 2. В отсортированном списке данные о продажах группируются по дням

Что такое список данных?

Списком данных называется набор данных на листе Excel, в котором каждому столбцу сопоставлено уникальное имя, а каждая строка представляет совокупность данных (например, данные о продажах за определенные дни). Список данных не может содержать пустых строк и столбцов, между строкой заголовков и данными также не должно быть пустых строк, а ячейки рядом со списками не должны содержать лишних данных. Таким образом, справа от списка данных должен находиться минимум один пустой столбец, слева — минимум один пустой столбец или край листа, а снизу — минимум одна пустая строка. В действительности список данных имеет много общего с таблицей базы данных. Типичный список данных показан на рис. 2.

Сортировка данных в пользовательском порядке
Проблема
Excel ограничивает меня сортировкой данных в алфавитном или числовом порядке, а это не всегда удобно. Мой начальник считает продажу машин самым важным делом, на второе место ставит их обслуживание, на третье — продажу аксессуаров. Эти приоритеты должны отражаться в рабочих листах: сначала «машины», за ними «сервис» (если не продавать машины, то и обслуживать будет нечего) и на третьем месте «аксессуары». Однако Excel предлагает отсортировать список отделов в алфавитном порядке либо по возрастанию (аксессуары, машины, сервис), либо по убыванию (сервис, машины, аксессуары). Мне приходится тратить много времени на перестановку строк в соответствии с требованиями начальника. Нельзя ли заставить Excel сортировать названия отделов в определенном мною порядке? Это сэкономило бы мне несколько часов работы каждый месяц.

Решение
Возможно, следующий совет позволит вам тратить свое рабочее время на продажу автомобилей, а не на сортировку данных. Фокус заключается в том, чтобы создать пользовательский список значений, который будет использоваться Excel как основа для последующей сортировки. Начнем с создания списка.

1.Введите сортируемые значения в группе смежных ячеек одной строки или столбца в том порядке, в котором должна производиться сортировка. В нашем примере значения Машины, Сервис и Аксессуары вводятся в ячейках A1, A2 и A3 соответственно.

2. Выделите ячейки с только что введенными значениями, выполните команду Сервис->Параметры и перейдите на вкладку Списки.

3. Убедитесь в том, что выделенный вами диапазон отображается в поле Импорт списка из ячеек.

4. Щелкните сначала на кнопке Импорт, затем — на кнопке OK.

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

1. Щелкните на любой ячейке списка данных и нажмите клавиши Ctrl+Shift+8, чтобы выделить весь список.

2. Выполните команду Данные->Сортировка. Раскройте список "Сортировать по" и выберите столбец, который должен быть отсортирован в порядке пользовательского списка.

3. Щелкните на кнопке Параметры. В окне Параметры сортировки раскройте список Сортировка по первому ключу, выберите свой пользовательский список и щелкните на кнопке OK.

4. Если потребуется, задайте дополнительные критерии сортировки. Щелкните на кнопке OK и, вернувшись к окну Сортировка, снова щелкните на кнопке OK, чтобы отсортировать данные.

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

Решение
Чтобы отсортировать строку значений, выделите ее, выполните команду Данные->Сортировка, щелкните на кнопке Параметры, в группе Сортировать установите переключатель Строки диапазона и щелкните на кнопке OK. Раскройте список "Сортировать по", выделите сортируемую строку, выберите способ сортировки (По возрастанию или По убыванию) и щелкните на кнопке OK.

Фильтрация данных
Проблема
Я работаю с огромными листами. Так, один из моих листов имеет всего 3 столбца в ширину и содержит более 1000 строк. Пример показан на рис. 3.

Рис. 3. Работать с таким объемом данных не слишком удобно

В данный момент меня интересуют только данные по агенту с первым идентификационным номером (SalesID=1). И только. И ничего более. Как убрать все остальные записи?

Решение
Чтобы на листе отображались только те строки, которые содержат конкретное значение в некотором поле, щелкните на любой ячейке списка данных и выполните команду Данные->Фильтр->Автофильтр. В первой ячейке каждого столбца списка появляется кнопка со стрелкой (а значения в этих ячейках скрываются). Кнопки предназначены для фильтрации данных; если щелкнуть на любой из них, на экране появляется меню с командами фильтрации. Щелкните на значении, которое будет выбрано в качестве фильтра. Все строки, в которых этот столбец не содержит выбранного значения, скрываются. Чтобы изменить способ фильтрации результатов, снова щелкните на кнопке со стрелкой и выберите в списке новый критерий. При наличии активного фильтра кнопка со стрелкой окрашивается в синий цвет.

Включая режим автофильтра, будьте внимательны и не щелкните на заголовке столбца (сером маркере с обозначением столбца — A, B, C и т. д.). В противном случае после выполнения команды Данные->Фильтр->Автофильтр Excel добавит кнопку фильтрации только в выделенный столбец.

Чтобы отключить все фильтры, выполните команду Данные->Фильтр->Отобразить все. Чтобы полностью отключить режим фильтрации и убрать кнопки со стрелками, выполните команду Данные->Фильтр->Автофильтр повторно. В Excel 2003 средства фильтрации включены в новый механизм работы со списками.

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

Решение
Кнопка со стрелкой в верхней строке списка данных открывает меню команд фильтрации. Одна из команд меню, Первые 10, вызывает диалоговое окно "Наложение условия по списку" (рис. 4). При помощи элементов управления этого окна можно определить, какие значения следует отобрать из списка (наибольшие или наименьшие) и их количество. Также можно указать, что представляет число в первом списке, — количество записей или их процент. Например, если ввести в левом списке число 10 и выбрать в крайнем правом списке % от количества элементов, Excel отберет верхние 10 % значений данного столбца.

Рис. 4. Поиск наибольших и наименьших элементов списка
с использованием автофильтрации

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

Решение
Чтобы отфильтровать данные по выбранному критерию, выполните команду Данные->Фильтр->Автофильтр, щелкните на кнопке со стрелкой над столбцом, к которому применяется критерий, и выберите в меню команду Условие. На экране появится диалоговое окно Пользовательский автофильтр (рис. 5).

Рис. 5. Фильтровать данные по значениям
нескольких столбцов? Легко!

Элементы управления диалогового окна Пользовательский автофильтр позволяют определить два критерия, по которым будет производиться фильтрация данных. Левый верхний раскрывающийся список содержит условные операторы (меньше, равно, больше или равно и т. д.), применяемые к данным, а в правом верхнем списке перечислены значения, взятые из столбца (при желании вы можете определить собственное значение). Например, чтобы найти все часы, в которых объем продаж превысил 2500 долларов, выберите пункт больше в левом верхнем списке и введите значение 2500 в поле правого верхнего списка.

Чтобы определить более сложный критерий, используйте вторую группу списков в диалоговом окне Пользовательский автофильтр, выбрав требуемую логическую операцию с помощью переключателя И или ИЛИ. Например, чтобы найти все часы с объемом продаж более 2500 долларов или менее 500 долларов, установите переключатель ИЛИ, выберите пункт меньше в левом нижнем списке и введите значение 500 в поле правого нижнего списка.

Пользовательский автофильтр фильтрует значения только из одного столбца, но вы можете использовать дополнительный фильтр для уточнения результатов. Например, чтобы ограничить результаты поиска отделом аксессуаров, щелкните на кнопке со стрелкой в столбце Отдел, выберите в списке пункт Аксессуары и затем примените пользовательский фильтр.

Поиск дубликатов в списке данных
Проблема
Имеется электронная таблица с 8500 адресами, которые будут использоваться в предстоящей рассылке. Я должен сократить список до 5000 адресов, чтобы не превысить бюджет. Многие из 8500 адресов повторяются, но Excel упорно отказывается найти дубликаты. Знакомый порекомендовал мне экспортировать данные в Access и выполнить там запрос на поиск дубликатов. Пожалуйста, не за­ставляйте меня осваивать Access!

Решение
Хотя поиск дубликатов как таковой требует применения макросов VBA, вы можете воспользоваться диалоговым окном Расширенный фильтр (рис. 4.6) и скопировать список данных в другое место, исключив из него все дубликаты.

Рис. 6. Хотя диалоговое окно Расширенный фильтр вполне тривиально,
оно может быть весьма полезным

Копирование уникальных записей списка данных выполняется так.

1. Выполните команду Данные->Фильтр->Расширенный фильтр.

2. Установите переключатель Скопировать результат в другое место.

3. Щелкните на кнопке свертки справа от поля Исходный диапазон, выделите фильтруемые ячейки и нажмите клавишу Enter.

4. Щелкните на кнопке свертки справа от поля Поместить результат в диапазон, затем щелкните на ячейке в левом верхнем углу диапазона, в котором будет находиться отфильтрованный список (я рекомендую выбрать ячейку A1 на пустом листе). Нажмите клавишу Enter.

5. Установите флажок Только уникальные записи и щелкните на кнопке OK.

Две записи считаются дубликатами только при полном совпадении, включая все знаки препинания. Если список не сократился до нужной величины и вы подозреваете, что в нем остались дубликаты, попробуйте отсортировать список адресов по фамилии, имени и почтовому индексу — это упростит поиск.

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

Решение
Чтобы скопировать в буфер обмена только видимые ячейки, необходимо разместить на панели инструментов кнопку Выделить видимые ячейки. Это делается так: выполните команду Сервис->Настройка, перейдите на вкладку Команды, выберите в списке Категории пункт Правка и прокрутите список Команды в правой части окна. Найдите в нем кнопку Выделить видимые ячейки и перетащите на любую панель инструментов. Чтобы скопировать и вставить только видимые ячейки, выполните следующие действия.

1. Отфильтруйте список данных и выделите видимые ячейки.

2. Щелкните на кнопке Выделить видимые ячейки.

3. Выполните команду Правка->Копировать.

4. Выполните команду Правка->Вставить.

Составление сводок по видимым ячейкам
Проблема
Фильтры удобны, но я не могу понять: как написать формулу, которая бы обрабатывала только видимые ячейки листа? Допустим, я работаю с данными о продажах. Мне нужно просуммировать объемы продаж по каждому представителю, но я не нашел способ просуммировать ячейки каждого представителя без создания формулы со ссылками на конкретные ячейки. Я могу отфильтровать список данных, чтобы в листе отображались ячейки, относящиеся только к одному торговому представителю. Нельзя ли создать формулу, которая бы суммировала только эти ячейки?

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

1. Щелкните на любой ячейке списка данных.

2. Выполните команду Данные->Фильтр->Автофильтр.

3. Щелкните на кнопке со стрелкой в столбце SalesID и выберите код представителя.

4. Щелкните на ячейке столбца Продажи, находящейся под данными.

5. Щелкните на кнопке Автосумма на стандартной панели инструментов Excel и нажмите клавишу Enter.

Обычно кнопка Автосумма позволяет создать простую формулу суммирования, которая складывает данные всех ячеек выделенного диапазона и помещает результат ниже последней ячейки. Но для ячейки, находящейся под фильтрованным столбцом данных, кнопка Автосумма создает формулу промежуточного итога. В чем разница? Если создать формулу промежуточного итога под отфильтрованным списком, формула вычисляет сумму (среднее арифметическое, минимум, максимум и т. д.) только для ячеек, отображающихся в отфильтрованном списке.

Формула промежуточного итога имеет такой синтаксис:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(функция;диапазон)

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

Таблица. Функции обобщения данных в формулах промежуточного итога

Номер Функция

1 СРЗНАЧ (среднее арифметическое видимых ячеек)

2 СЧЕТ (количество видимых ячеек)

3 СЧЕТЗ (количество видимых ячеек, содержащих значения)

4 МАКС (максимальное значение в видимых ячейках)

5 МИН (минимальное значение в видимых ячейках)

6 ПРОИЗВЕД (произведение значений в видимых ячейках)

7 СТАНДОТКЛОН (стандартное отклонение значений в видимых ячейках)

8 СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности)

9 СУММ (математическая сумма значений в видимых ячейках)

10 ДИСП (дисперсия значений в видимых ячейках)

11 ДИСПР (дисперсия по общей совокупности)

---

Автор: К. Фрай
Источник: книга "Хитрости Excel"
Оглавление>>