Excel_2010

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

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

7.2.1. Простая сортировка данных

Для быстрой сортировки данных списка по некоторому столбцу выполните следующие шаги:

1.Установите курсор внутри сортируемого столбца.

2.На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка по

возрастанию (Sort Ascending) или Сортировка по убыванию (Sort Descending) .

Кнопки сортировки доступны также и на вкладке Главная в группе Редактирование.

Для отмены сортировки, нажмите кнопку Отменить (Undo) на панели быстрого доступа.

7.2.2.Сложная сортировка данных

Если необходимо отсортировать таблицу по нескольким столбцам, проделайте следующие

шаги:

1.Выделите ячейку внутри сортируемого списка.

2.На вкладке Данные (Data) в группе Сортировка и фильтр нажмите кнопку Сортировка

. MS Excel отобразит ОД Сортировка (Sort) (Рисунок 7.1).

Рисунок 7.1. ОД Сортировка

3.В группе Столбец в поле Сортировать по выберите заголовок столбца, по которому будет производиться сортировка.

4.В поле Сортировка выберите тип сортировки:

Для сортировки по значению выберите пункт Значения.

Для сортировки по цвету ячейки выберите пункт Цвет ячейки.

Для сортировки по цвету шрифта выберите пункт Цвет шрифта.

Для сортировки по набору значков выберите пункт Значок ячейки.

120

5.В группе Порядок щелкните стрелку рядом с кнопкой, а затем в зависимости от типа формата выберите цвет ячейки, цвет шрифта или значок ячейки.

6.В группе Порядок выберите последовательность, в которой необходимо осуществлять сортировку:

Чтобы отсортировать ячейки на основании их значений выберите вариант – От А до Я

или От Я до А (для символьных данных); По возрастанию или По убыванию (для числовых данных); От новых к старым или От старых к новым (для значений даты);

Для сортировки ячеек на основе пользовательских списков (например, для сортировки по названиям месяцев года) выберите Настраиваемый список. В открывшемся окне Список выберите список и нажмите ОК.

Чтобы отсортировать на основе цвета ячейки, цвета шрифта или значка ячейки выберите пункт Сверху или Снизу;

7.Для задания сортировки по следующему столбцу нажмите кнопку Добавить уровень, а затем повторите шаги с третьего по шестой.

8.Для удаления сортировки по некоторому столбцу, выделите его запись и нажмите кнопку

Удалить уровень.

 В списке необходимо оставить хотя бы один столбец. 9. Нажмите OK.

Столбцы с более высокой позицией в списке будут отсортированы раньше, чем столбцы с более низкой позицией.

Для повторного применения сортировки после изменения данных на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Применить или нажмите комбинацию клавиш [Ctrl]+[Alt]+[L] на клавиатуре.

Максимальное число столбцов для сортировки — 64.

7.2.3.Настройка параметров сортировки

Вы можете осуществлять сортировку данных не только по столбцам, но и по строкам Таблицы. Для этого:

1.Выделите ячейку внутри сортируемого списка.

2.На вкладке Данные (Data) в группе Сортировка и фильтр нажмите кнопку Сортировка

. MS Excel отобразит ОД Сортировка (Sort) (Рисунок 7.1).

3.В окне диалога Сортировка нажмите кнопку Параметры. Откроется окно диалога

Параметры сортировки (Рисунок 7.2).

4.Для сортировки данных с учетом регистра установите флажок Учитывать регистр.

5.В группе Сортировать установите требуемый переключатель:

Для сортировки данных по столбцам Таблицы установите переключатель строки

диапазона;

Для сортировки данных по строкам Таблицы установите переключатель столбцы

диапазона.

121

Рисунок 7.2. Установка параметров сортировки

7.2.4.Независимая сортировка одного столбца в диапазоне ячеек

1.Выделите столбец в диапазоне ячеек, содержащем два или более столбцов.

2.На вкладке Главная в группе Редактирование выберите пункт Сортировка и фильтр, а

затем выберите одну из команд для сортировки. Откроется окно Обнаружены данные вне указанного диапазона (Рисунок 7.3).

3.Установите переключатель сортировать в пределах указанного выделения.

4.Нажмите кнопку Сортировка.

 Если результат не соответствует желаемому, нажмите кнопку Отменить .

Рисунок 7.3. Окно диалога Обнаружены данные вне указанного диапазона

Будьте осторожны при использовании этого средства. Сортировка по одному столбцу диапазона может привести к нежелательным результатам, например, перемещению ячеек в этом столбце относительно других ячеек строки.

В Таблице осуществлять эту процедуру нельзя.

7.3.Фильтрация данных

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

Строки, отобранные при фильтрации в MS Excel, можно редактировать, форматировать и выводить на печать, можно на их основе также создавать диаграммы, не изменяя порядок строк и не перемещая их.

Для фильтрации данных в MS Excel используется 2 типа фильтров: Автофильтр и Расширенный фильтр. При использовании Автофильтра фильтрация осуществляется

122

непосредственно на исходных данных. При использовании Расширенного фильтра вы имеете возможность разместить результаты фильтрации в отдельной области.

При использовании диалогового окна Найти для поиска в отфильтрованных данных поиск выполняется только в выведенных на экран данных; скрытые данные при поиске не используются.

7.3.1.Фильтрация данных с помощью Автофильтра

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

Простая фильтрация данных в списке

Для отбора данных из списка с помощью фильтра выполните следующие шаги:

1.Выделите любую ячейку внутри списка.

2.На вкладке Данные (Data) в группе Сортировка и фильтр нажмите кнопку Фильтр. В

заголовках столбцов появятся кнопки Автофильтра обозначающие, что фильтрация включена, но не применена.

3.Нажмите кнопку Автофильтра (Рисунок 7.4), в столбце, по которому вы хотите фильтровать данные.

4.Установите флажки возле тех элементов данных, в соответствии с которыми вы хотите

произвести фильтрацию.

Кнопка Автофильтра для

поля Производитель

Рисунок 7.4. Использование Автофильтра

Кнопка Фильтр означает, что фильтр применен.

После выполнения фильтрации, вы также можете отфильтровать и результат, если необходимо.

Чтобы выполнить отбор пустых ячеек, в меню Автофильтр в верхней части списка значений снимите флажок (Выделить все), а в нижней части установите флажок (Пустые).

Фильтрация с использованием критериев

Для выполнения фильтрации на основе некоторых условий выполните следующие действия:

1.Выделите любую ячейку внутри списка.

2.На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.

3.Нажмите на кнопку Автофильтра возле заголовка того столбца, по которому вы хотите произвести фильтрацию списка.

4.Выполните одно из следующих действий:

123

Если в столбце содержатся текстовые данные, выберите команду Текстовые фильтры (Рисунок 7.5, а).

Если в столбце содержатся числа, выберите команду Числовые фильтры (Рисунок 7.5,

б).

Если столбец содержит даты, выберите команду Фильтры по дате (Рисунок 7.6).

(а)

(б)

Рисунок 7.5. Команды фильтрации Автофильтра

Для получения наилучших результатов не смешивайте в одном столбце данные разных типов (например, текст и числа или числа и даты), поскольку для каждого столбца доступен только один тип команд фильтрации.

Если столбец содержит данные разных типов, Автофильтр будет отображать команду фильтрации для того типа, который встречается чаще. Например, если столбец содержит четыре текстовых значения и девять — значений дат, отображается команда Фильтры по дате (Рисунок 7.6).

Рисунок 7.6. Автофильтр для столбца, содержащего смешанные данные

124

5.Выберите подходящий критерий фильтрации (Заканчивается на..; содержит…; равно…; не равно… и т.п.).

MS Excel отобразит ОД Пользовательский автофильтр (Custom AutoFilter) (Рисунок 7.7)

Рисунок 7.7. Настройка пользовательского Автофильтра

6.Нажмите кнопку списка в левом верхнем поле и выберите оператор сравнения, на основе которого будет производиться отбор данных, удовлетворяющих критерию.

7.В поле справа от поля оператора сравнения выполните одно из следующих действий:

Введите значение, которое вы хотите использовать при сравнении.

Нажмите на стрелку, и выберите значение из списка, которое вы хотите использовать при сравнении.

При создании Пользовательского автофильтра вы можете использовать знак вопроса «?» для обозначения одного любого знака и знак «*» для обозначения последовательности

любых знаков.

8.Если необходимо повторите шаги 5-6 для задания второго условия отбора данных и выполните одно из следующих действий:

Выберите И (AND) — если вы хотите, чтобы оба критерия сравнения применялись.

Выберите ИЛИ (OR) — если вы хотите применить отдельные критерии сравнения.

9.Нажмите OK. MS Excel отобразит результат фильтрации данных на листе (Рисунок 7.8).

Рисунок 7.8. Результат фильтрации данных с использованием Критериев

Чтобы повторно применить фильтр после изменения данных, выделите ячейку в диапазоне или таблице, а затем на вкладке Данные в группе Сортировка и фильтр

нажмите кнопку Применить повторно.

Все фильтры дат основаны на григорианском летоисчислении.

Финансовый год и финансовый квартал всегда начинаются в январе календарного года.

125

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

Фильтрация по цвету ячеек, цвету шрифта или набору значков

Если диапазон ячеек отформатирован вручную или с помощью условного форматирования по цвету ячеек или шрифта, его можно фильтровать по этим цветам. Можно также выполнять фильтрацию по набору значков, созданному с помощью условного форматирования.

1.Выделите диапазон ячеек, содержащих данные, отформатированные по цвету ячеек, цвету шрифта или набору значков.

2.На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.

3.Щелкните стрелку рядом с заголовком столбца.

4.Выберите значение Фильтр по цвету, а затем в зависимости от типа форматирования выберите вариант Фильтр по цвету ячейки, Фильтр по цвету шрифта или Фильтр по значку ячейки (Рисунок 7.9).

5.В зависимости от типа формата выберите цвет ячейки, цвет шрифта или значок ячейки.

Рисунок 7.9. Фильтрация по цвету

Отмена фильтрации

Для отображения всех данных списка необходимо:

1.Нажмите на кнопку Автофильтра рядом с заголовком столбца, по которому производилась фильтрация .

2.Из выпадающего списка выберите Удалить фильтр с «имя столбца» (Рисунок 7.10).

Рисунок 7.10. Отмена фильтрации

126

Если вы использовали фильтрацию больше чем по одному столбцу для отмены фильтрации на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Очистить.

7.3.2.Фильтрация с применением расширенного фильтра

В большинстве случаев стандартной фильтрации вполне достаточно. Однако если вы столкнулись с ее ограничениями, вам может потребоваться расширенная фильтрация. Расширенная фильтрация намного более гибкая, чем стандартная, однако требует дополнительной подготовительной работы. Расширенную фильтрацию можно использовать в базах данных рабочего листа и в Таблицах.

Перед использованием расширенного фильтра списка необходимо создать Диапазон условий, соответствующих определенным требованиям. Диапазон условий содержит информацию, которую приложение Excel использует для фильтрации. Данный диапазон должен состоять как минимум из двух строк, первая из которых содержит те заголовки полей списка, по которым вы хотите проводить фильтрацию; другие строки состоят из условий, определенных пользователем. Диапазон условий может находиться в любом месте рабочего листа или даже на отдельном рабочем листе.

При необходимости отобразить результат фильтрации в отдельной области на текущем листе желательно также подготовить Диапазон результата.

Создание Диапазона условий

Для организации Диапазона условий выполните следующие действия:

1.В свободную строку вне таблицы скопируйте заголовки тех столбцов, на данные которых будут наложены ограничения (заголовки несмежных столбцов могут оказаться рядом);

2.Под каждым из заголовков задайте условия отбора данных (Рисунок 7.11).

Условия могут представлять собой данные или элементы данных, они могут содержать операторы сравнения (больше или меньше), либо могут быть формулами, например, формулой, которая фильтрует все значения в столбце, превышающие среднее значение.

Условия могут содержать подстановочные знаки «?» или «*».

Условия, находящиеся в одной строке MS Excel рассматривается, как объединенные оператором И.

Условия, находящиеся в разных строках MS Excel рассматривается, как объединенные оператором ИЛИ.

Диапазон Исходный условий

список

Рисунок 7.11. Подготовка диапазона условий

127

Создание Диапазона результата

Для организации Диапазона результата выполните следующие действия:

1.В свободную строку вне таблицы скопируйте полностью строку заголовков вашей Таблицы

(Рисунок 7.12).

Диапазон

результата

Рисунок 7.12. Подготовка диапазона результата

Диапазон результата может находиться только на том же рабочем листе, на котором находится исходный диапазон.

Использование Расширенного фильтра

Для выполнения фильтрации с помощью Расширенного фильтра выполните следующие действия:

1.Выделите любую ячейку внутри списка

2.На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Откроется окно диалога Расширенный фильтр (Рисунок 7.13).

Рисунок 7.13. Задание параметров Расширенного фильтра

3.В окне диалога Расширенный фильтр в секции Обработка выполните одно из следующих действий:

Чтобы результаты фильтрации отображались непосредственно в списке, скрывая строки, не удовлетворяющие критериям отбора, установите переключатель

фильтровать список на месте;

Если результат отбора должен быть помещен в Диапазон результата, установите переключатель скопировать результат в другое место.

4.В поле Исходный диапазон укажите диапазон фильтруемых ячеек (Список или Таблицу). Диапазон должен содержать все ячейки списка с учетом ячеек заголовков столбцов.

128

5.В поле Диапазон условий укажите диапазон сформированных ранее условий: он должен содержать все ячейки диапазона условий отбора с учетом ячеек заголовков столбцов.

6.В случае размещения результатов фильтрации в Диапазоне результата убедитесь, что в поле Поместить результат в диапазон содержатся все ячейки Диапазона результата.

7.Нажмите кнопку ОК для выполнения фильтрации. В списке будут отображены записи, удовлетворяющие заданным критериям.

7.4.Группировка данных

7.4.1. Группировка данных

Иногда приходится работать с огромными таблицами, состоящими из сотен строк и/или столбцов, содержащих данные, которые логически могут быть объединены в группы. Для удобства работы с такими таблицами в MS Excel используется функция группировки данных, позволяющая создавать многоуровневую структуру. Можно создавать многоуровневую структуру из строк, столбцов или из строк и столбцов одновременно.

Существует два способа структурирования данных на рабочем листе – автоматически и вручную:

Для автоматического структурирования данных рабочего листа, в них должны содержаться итоговые строки (или столбцы), которые расположены рядом с подробными данными. Эти итоговые строки (или столбцы) должны включать итоговые формулы со ссылками на ячейки, находящиеся внутри диапазона данных (Рисунок 7.14).

Рисунок 7.14. Диапазон, содержащий данные для автоматического структурирования

Для структурирования рабочего листа вручную особых требований к структуре данных не применяется.

129