Сортировка и фильтрация данных в excel

Упорядочивание текста

В Excel сортировка столбцов, содержащих текст, выполняется так же, как и операция с числами. Произведем эту команду в колонке уже известной нам таблицы цен на комнаты в отеле «Питание». Выделяем столбец кликом по его заголовку и открываем панель инструментов «Редактирование» вкладки «Главная». Теперь в выпадающем меню вместо команд сортировки по возрастанию и убыванию расположены команды сортировки от А до Я и наоборот.

Выполняем построение ячеек от А до Я, опять же указывая расширение диапазона операции.

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

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

Устранение неполадок с форматами дат в Excel

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

Рассмотрим этот список дат:

Выглядит достаточно просто. Однако, когда вы выбираете данные, нажимаете кнопку «Сортировка и фильтр» и выбираете сортировку от самых старых до самых новых, это не работает! Вы можете получить что-то вроде этого:

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

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

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

Как исправить даты, которые не будут правильно отсортированы в Excel

К счастью, эту проблему легко исправить.

1. Вырежьте столбец дат, выбрав ячейки и нажав Ctrl + x, выбрав «Вырезать» в главном меню или щелкнув правой кнопкой мыши по ячейке (ям) и выбрав «Вырезать».

2. Откройте Блокнот или другой текстовый редактор.
3. Вставьте даты в текстовый редактор.

4. Вернитесь в Excel, выберите столбец, в котором раньше были даты, щелкните правой кнопкой мыши и выберите Формат ячеек.
5. Выберите «Дата» и нажмите «ОК».

6. Теперь вернитесь в текстовый редактор, выберите и скопируйте список дат.

7. Вернитесь в Excel и вставьте даты в столбец, который вы отформатировали на шаге 5.
8. Теперь у вас должна быть возможность сортировать. Выберите столбцы с данными, нажмите кнопку «Сортировка и фильтр» и выберите «Сортировать от старых к новым». Альт! Оно работает!

Упорядочивание по цвету и значкам

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

Для этого заходим в настраиваемую сортировку. Кроме описанного выше способа, можно воспользоваться меню панели «Редактирование» вкладки «Главная». Выбираем из списка команд «Сортировка и фильтр» элемент «Настраиваемая сортировка».

Выбираем поле «Задача», тип «Цвет ячейки» и устанавливаем оранжевый цвет сверху. Чтобы домашние дела находились внизу списка, добавляем еще один уровень и для тех же параметров устанавливаем голубой цвет снизу.

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

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

Первая строка: заголовки столбцов или данные

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

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

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

Если первая строка не содержит заголовков, Excel использует букву столбца, такую ​​как столбец D или столбец E, как выбор в колонка вариант Сортировать диалоговое окно.

Excel использует такую ​​разницу, чтобы определить, является ли первая строка строкой заголовка, и это хорошо подходит для правильного выбора, но это не является непогрешимым. Если Excel делает ошибку, диалоговое окно «Сортировка» содержит флажок — У моих данных есть заголовки — это можно использовать для переопределения этого автоматического выбора.

Фильтры

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

Фильтр — это сохраненный набор условий и значений фильтрации, согласно которому происходит отбор информации для отображения в представлении MS Project. Фильтры содержат условия на значения в столбцах, и поэтому (так же, как и таблицы) делятся на два типа: относящиеся к ресурсам и задачам. Соответственно, применить фильтр можно только к таблице одного типа с ним.

MS Project содержит набор предопределенных фильтров, которые позволяют быстро отбирать из таблиц наиболее необходимые данные. Список этих фильтров с описанием каждого из них можно просмотреть с помощью ссылки Доступные фильтры на странице Полезные страницы справки MS Project 2002 в индексном файле.

Работа с фильтрами осуществляется через подменю Project > Filtered for: (Проект > Фильтр). В пункте Filtered for: (Фильтр) (рис. 4.11, файл 1.mpp) указано название выбранного в данный момент фильтра, а при наведении на него курсора открывается подменю со списком фильтров, которые можно использовать для текущей

таблицы. В подменю отображается неполный список фильтров. Полный список фильтров можно получить, щелкнув на пункте More Filters (Другие фильтры).

Рис. 4.11. Работа с фильтрами осуществляется через подменю Project > Filtered for: (Проект > Фильтр)

Помимо меню для быстрого доступа к полному списку фильтров можно воспользоваться панелью форматирования (рис. 4.12, файл 5.mpp). Как вы можете заметить, первым пунктом в списке (и на панели, и в меню) идет фильтр All Tasks1 (Все задачи), при использовании которого отображаются все записи таблицы. Этот фильтр включен по умолчанию. (При открытой таблице с информацией о ресурсах фильтр будет называться All Resources (Вce ресурсы)).

Рис. 4.12. Панель форматирования позволяет получить быстрый доступ к фильтрам

При выборе любого другого фильтра данные в таблице отбираются в соответствии с условиями фильтрации. Например, если включить фильтр, отбирающий только критические задачи (пункт Critical (Критические задачи)), то число строк в таблице сразу уменьшится (рис. 4.13, файл 5.mpp).

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

Фильтрация может выполняться и в другом режиме, когда строки таблицы, удовлетворяющие критериям, выделяются цветом, а остальные продолжают отображаться. Для того чтобы выделить цветом соответствующие условиям фильтра строки, нужно выбрать команду меню Project > Filtered for > More Filters (Проект > Фильтр > Другие фильтры), а затем в открывшемся диалоговом окне со списком всех фильтров (рис. 4.14) выбрать нужный фильтр и нажать кнопку Highlight (Выделить). Переключатели вверху диалогового окна позволяют переключаться между списками фильтров разных типов.

Выбранный в списке фильтр можно отредактировать, а также скопировать и создать на его основе новый фильтр, нажав соответствующую кнопку диалогового окна. Нельзя изменить лишь три фильтра: All Tasks/All Resources (Все задачи/Все Ресурсы) и Using Resource (Использование ресурса).

Сортировать несколько столбцов данных в Excel

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

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

Пример сортировки по нескольким столбцам

В приведенном ниже примере данные в диапазоне от A2 до E12 сортируются по двум столбцам данных. Данные сначала сортируются по имени, а затем по возрасту.

Чтобы отсортировать несколько столбцов данных:

1. Выделите диапазон ячеек для сортировки. В этом примере выбраны ячейки с A2 по E12.
2. Выберите Дом .
3. Выберите Sort & Filter, чтобы открыть раскрывающийся список.
4. Выберите Custom Sort, чтобы открыть диалоговое окно Sort.
5. Поставьте галочку напротив пункта Мои данные с заголовками .
6. Под заголовком столбца выберите « Сортировать по стрелке вниз» и выберите « Имя» в раскрывающемся списке, чтобы сначала отсортировать данные по столбцу «Имя».
7. Под заголовком «Сортировать по» оставьте значение « Значения ячеек» . Сортировка основана на фактических данных в таблице.
8. Под заголовком «Порядок» выберите стрелку вниз и выберите « Z — A», чтобы отсортировать данные «Имя» в порядке убывания.
9. Выберите Добавить уровень, чтобы добавить второй параметр сортировки.
10. Под заголовком столбца выберите стрелкуЗатем по внизу» и выберите « Возраст», чтобы отсортировать записи с повторяющимися именами по столбцу «Возраст».
11. Под заголовком «Порядок» выберите « От наибольшего к наименьшему» в раскрывающемся списке, чтобы отсортировать данные о возрасте в порядке убывания.
12. Выберите ОК, чтобы закрыть диалоговое окно и отсортировать данные.

В результате определения второго ключа сортировки, показанного в примере ниже, две записи с одинаковыми значениями для поля « Имя» сортируются в порядке убывания с использованием поля « Возраст» . Это приводит к записи для студента Уилсона Дж., 21 год, до записи для Уилсона П., 19 лет.

Первый ряд: заголовки столбцов или данные

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

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

Если первая строка не содержит заголовков, Excel использует букву столбца (например, Столбец D или Столбец E) в качестве вариантов в параметре Столбец диалогового окна Сортировка.

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

Возможное предупреждение о сортировке

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

• Сортируйте все, что похоже на число, как число.
• Сортировка номеров и номеров, сохраненных в виде текста отдельно.

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

Сортировать по дате Пример

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

1. Выделите диапазон сортируемых ячеек.
2. Нажми на Главная вкладку ленты.
3. Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
4. Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
5. Под колонка заголовок в диалоговом окне, выберите Заимствованные из раскрывающегося списка, чтобы сначала отсортировать данные по столбцу Имя.
6. Сортировать по параметр остается установленным для Ценности — поскольку сортировка основана на фактических данных в таблице.
7. Под Порядок сортировки заголовок, выберите От старых к новинкам в раскрывающемся списке, чтобы отсортировать даты
8. Нажмите Хорошо в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.

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

Как пользоваться автофильтром

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

Выбор уникальных значений

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

1. Выделите нужный диапазон клеток.
2. Кликните на кнопку «Сортировка».
3. Выберите инструмент «Фильтр».

4. После этого вы увидите, что около первой ячейки появится символ выпадающего списка.
5. Кликните на него.
6. Благодаря этому появится список уникальных значений. Вам нужно поставить галочки около тех данных, которые должны выводиться на экран.
7. Чтобы сохранить изменения, необходимо нажать на «OK».

В качестве примера оставим все значения, кроме цифры «4».

8. Результат будет следующим.

Числовые фильтры

Кроме этого есть и дополнительные методы выбора данных для отображения. Они позволяют вывести на экран не какие-то определенные значения, а всё, что угодно, лишь бы это соответствовало условию отбора.

Работает это очень просто.

1. Кликаем на иконку около первой клетки.
2. Выбираем пункт «Числовые фильтры».
3. Указываем нужный критерий отбора информации.

4. В качестве примера выберем «больше или равно».

Благодаря этому откроется окно, в котором можно будет указать необходимые условия

Обратите внимание на то, что этот критерий можно расширить. Вы можете использовать сразу две строки для составления логического выражения.
Для сохранения пользовательского автофильтра требуется нажать на кнопку «OK».

6. В качестве примера укажем цифру «3».

7. Сразу после этого вы увидите, что все числа, которые были меньше 3, исчезли из поля видимости.

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

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

1. Кликните на иконку в первой клетке.
2. Выберите пункт «Удалить фильтр из столбца 1». В вашем случае название будет отличаться.
3. Благодаря этому книга примет прежний вид.

Упорядочивание элементов

Помимо скрытия и отображения некоторых элементов, можно использовать обычную сортировку.

1. Нажимаем на указанную иконку.
2. Выбираем нужный вам способ для упорядочивания данных.

3. В результате этого вы увидите, что все данные перемешались согласно нашим указаниям. Мы в качестве примера выбрали вариант «по возрастанию».

Упорядочивание по цвету

Для демонстрации этой возможности нужно будет некоторые клетки выделить каким-нибудь цветом.

Затем порядок действий будет таков:

1. Снова нажимаем на иконку автофильтра.
2. Выбираем пункт «Сортировка по цвету».
3. Затем указываем нужный вам вариант.

4. Для более детальной настройки нужно будет выбрать пункт «Пользовательская сортировка».

5. После этого появится окно, в котором вы сможете:
   • выбрать способ сортировки (по цвету ячейки или шрифта, значению или значку);
   • указать порядок (цвет и положение).

Фильтр по цвету

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

1. Нажимаем на указанную иконку.
2. Выбираем «Фильтр по цвету».
3. Указываем какой-нибудь цвет.

4. В итоге вы увидите, что останутся только те ячейки, которые соответствуют выбранному цвету.

Работа с датами

Автофильтр может работать не только с числами. Этот инструмент отлично справляется и с временными данными. Для этого сначала добавим случайные даты.

Затем нужно будет выполнить несколько простых манипуляций.

1. Выделяем наш диапазон ячеек.
2. Нажимаем на инструмент «Сортировка».
3. Выбираем вариант «Фильтр». В дальнейшем можете использовать горячие клавиши Ctrl+Shift+L.

4. После этого снова кликаем на знакомый нам инструмент.
5. На этот раз вы увидите новый пункт «Фильтр по дате», поскольку наши значения соответствуют этому формату.
6. Кликнув по нему, вы увидите огромный список различных условий фильтрации. Выбирайте что хотите.

Пример создания собственного фильтра

Предположим, что нам нужно создать фильтр, отбирающий задачи стоимостью больше $1000 или длительностью не менее недели. В созданный фильтр (назовем его Фильтр 1) поместим два условия: Cost is greater than $1000 (Стоимость больше $1000) и Duration is greater than or equal to Iw (Длительность больше или равна 1н.). Второе условие должно быть связано с первым с помощью оператора Or (Или). Установим флажки Show related summary rows (Показать относящиеся к задачам фазы) и Show in menu (Показывать в меню). Настройки этого фильтра представлены на рис. 4.16 (файл б.mpp). Теперь применим фильтр, выбрав его в меню Project > Filtered for: (Проект > Фильтр), и взглянем на таблицу (рис. 4.17, файл 6.mрр).

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

Выберем команду меню Project > Filtered for > More Filters (Проект > Фильтр > Другие фильтры), где уже выделен курсором Фильтр 1, и нажмем кнопку Edit (Изменить). В открывшемся диалоговом окне сбросим флажок Show related summary rows (Показывать связанные суммарные строки) и нажмем ОК. Теперь в диалоговом окне со списком фильтров, в которое мы автоматически вернулись, нажмем кнопку Apply (Применить). Однако результат действия модифицированного фильтра остался тем же.

Рис. 4.17. После применения созданного нами Фильтра 1 в таблице отображаются только задачи длительностью более недели и стоимостью более $1000

Проблема в том, что в MS Project фазы считаются видом задач (суммарной задачей). Следовательно, фазы, содержащие задачи, отвечающие условиям фильтрации, в нашем случае тоже удовлетворяют условиям фильтрации (так как стоимость или длительность фазы всегда больше или равна цене или длительности задачи).

Для того чтобы устранить фазы из результатов фильтрации, нужно добавить в фильтр дополнительное условие. Это условие будет использовать поле Summary (Суммарная задача), которое у фаз имеет значение Yes (Да), а у задач — No (Нет). В диалоговом окне определения свойств Фильтра 1 добавим условие Summary equals No (Суммарная задача равна Нет). Поскольку это условие должно выполняться одновременно с первым и вторым условием, а они соединены оператором Or (Или), то для связи нового условия с предыдущими стоит воспользоваться групповой связью условий.

Групповая связь условий — это возможность соединить с помощью логической связи (И или Или) не одно условие с другим, а группу условий с другой группой. Группа условий — это несколько условий, объединенных различными типами связи.

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

Для того чтобы связать наше новое условие с группой из двух предыдущих условий, вставим между ним и вторым условием из группы строку и в ней в поле And/Or (И/Или) выберем подходящее нам условие (И). Такой фильтр настроен на рис. 4.15, а результаты его действия видны на рис. 4.18 (файл 7.mрр).

Рис. 4.18. Модифицированный Фильтр 1 отбирает только задачи, игнорируя фазы проекта

Умная таблица

Сортировку и фильтр можно также активировать, превратив область данных, с которой вы работаете, в так называемую «умную таблицу».

Существует два способа создания «умной таблицы». Для того, чтобы воспользоваться первым из них, выделяем всю область таблицы, и, находясь во вкладке «Главная», кликаем по кнопке на ленте «Форматировать как таблицу». Данная кнопка находится в блоке инструментов «Стили».

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

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

Главное, обратите внимание, чтобы около параметра «Таблица с заголовками» стояла галочка. Далее, просто нажать на кнопку «OK»

Если вы решили воспользоваться вторым способом, тогда тоже нужно выделить всю область таблицы, но на этот раз перейти во вкладку «Вставка». Находясь тут, на ленте в блоке инструментов «Таблицы», следует нажать на кнопку «Таблица».

После этого, как и в прошлый раз, откроется окно, где можно скорректировать координаты размещения таблицы. Жмем на кнопку «OK».

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

При нажатии на этот значок, будут доступны все те же функции, что и при запуске фильтра стандартным способом через кнопку «Сортировка и фильтр».

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

Как отсортировать по алфавиту

Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту — фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах».

Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка.