Сортировка, фильтрация и поиск

Сортировка, фильтрация и поиск

Очень часто Excel используется для создания списков, каждая строка которых содержит информацию, относящуюся к одному объекту. Во всех примерах, рассмотренных в данном разделе, фигурировали списки. Обратимся к рис. 22. Представленный в нем прайс-лист является типичным списком. В списке имеется «шапка» (заголовки столбцов) и столбцы, содержащие однотипные данные в соответствии с заголовком. В свою очередь каждая строка представляет собой характеристику объекта, название которого, как правило, присутствует в первом столбце таблицы.

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

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

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

Рис. 34. Окно задания параметров сортировки

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

Рис. 35. Задание порогового значения для фильтрации данных

Поиск и замена данных в ячейках Excel осуществляется по таким же принципам, что и поиск текстовых фрагментов Word. Чтобы открыть окно поиска и замены, нажмите сочетание клавиш +F или нажмите кнопку Найти и выделить в группе Редактирование и задействуйте команду Найти. Откроется уже знакомое вам окно, на вкладках которого можно вводить условия для поиска и замены данных.

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

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

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

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

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

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

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

Как отсортировать лист?

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

1. Нажмите Вид и наведите курсор мыши на Закрепить. Выберите 1 строку в появившемся меню.
2. Строка заголовка закреплена. Решите, какой столбец будет отсортирован, затем щелкните ячейку в столбце.
3. Нажмите Данные и выберите Сортировать лист по столбцу, A-Я (по возрастанию) или Сортировать лист по столбцу, Я-A (по убыванию). В нашем примере мы выберем Сортировать лист по столбцу, A-Я.
4. Лист будет отсортирован по вашему выбору.

Как отсортировать диапазон?

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

1. Выберите диапазон ячеек, который вы хотите отсортировать. В нашем примере мы выберем диапазон ячеек G3: H9.
2. Нажмите Данные и выберите Сортировка диапазона из выпадающего меню.
3. Откроется диалоговое окно «Сортировка». Выберите нужный столбец, по которому вы хотите отсортировать.
4. Выберите возрастание или убывание. В нашем примере мы выберем нисходящий (Я-A). Затем нажмите Сортировка.
5. Диапазон будет отсортирован по вашему выбору (в нашем примере данные отсортированы в порядке убывания в соответствии со столбцом «Количество»).

Как создать фильтр?

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

• Нажмите Вид и наведите курсор мыши на Закрепить. Выберите 1 строку в появившемся меню.
• Нажмите на любую ячейку, которая содержит данные.
• Нажмите кнопку «Фильтр».
• Стрелка раскрывающегося списка появляется в заголовке каждого столбца.
• Нажмите стрелку раскрывающегося списка для столбца, который вы хотите отфильтровать. В нашем примере мы будем фильтровать столбец B для просмотра только определенных типов оборудования.
• Нажмите Очистить, чтобы удалить все галочки.
• Выберите данные, которые вы хотите отфильтровать, затем нажмите OK. В этом примере мы выберем «Ноутбук» и «Проектор» для просмотра только этих типов оборудования.
• Данные будут отфильтрованы, временно скрывая любой контент, который не соответствует критериям. В нашем примере видны только ноутбуки и проекторы.

Применение нескольких фильтров

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

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

Если вы работаете с другими людьми на листе, вы можете создать свой фильтр. Создание фильтра позволяет фильтровать данные, не влияя на представление данных другими людьми; это влияет только на ваш собственный взгляд. Это также позволяет называть фильтры и сохранять несколько. Вы можете создать фильтр, щелкнув стрелку раскрывающегося списка рядом с кнопкой «Фильтр».

Сортировка и фильтр в Excel на примере базы данных клиентов

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

Работа в Excel c фильтром и сортировкой

Чтобы выполнить сортировку Excel можно воспользоваться несколькими простыми способами. Сначала рассмотрим самый простой.

1. Заполните таблицу как на рисунке:
2. Перейдите на любую ячейку столбца F.
3. Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Сортировка от А до Я».

1. Перейдите на любую ячейку таблицы с данными.
2. Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр»
3. Щелкните по выпадающему списку опций в заголовке «Город» таблицы и выберите опцию «Сортировка от А до Я».

Первый способ более простой, но он может выполнить сортировку только по одному столбцу (критерию). Для сортировки по нескольким столбцам следует использовать «Способ 2».

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

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

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

Отсортируем базу данных клиентов в соответствии с двумя критериями:

1. Наименование городов в алфавитном порядке.
2. Возраст клиентов от младших до старших.

Выполним сортировку по отдельным столбцам таблицы:

1. Перейдите курсором клавиатуры на любую ячейку таблицы и выберите инструмент: «Данные»-«Сортировка и фильтр»-«Сортировка».
2. В появившемся диалоговом окне укажите параметры сортировки так, как указано ниже на рисунке и нажмите на кнопку «Добавить уровень».
3. Заполните параметры второго уровня как ниже на рисунке и нажмите ОК.

В результате таблица Excel отсортирована по нескольким критериям.

Чем выше уровень параметров сортировки, тем выше его приоритет.

Примечание. В Excel начиная с версии 2010-го года, количество уровней может быть столько, сколько столбцов в таблице. В старших версиях Excel разрешалось только 3 уровня.

Как сделать фильтр в Excel по столбцам

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

С данной таблицы нужно выбрать всех клиентов в возрасте до 30-ти лет проживающих в городах Москва и Санкт-Петербург.

1. Снова перейдите на любую ячейку таблицы базы данных клиентов и выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр».
2. Щелкните по выпадающему списку столбца «Город» и отметьте галочками только Москву и Санкт-Петербург. И нажмите ОК.
3. Щелкните по выпадающему списку столбца «Возраст» и выберите опцию: «Числовые фильтры»-«Настраиваемый фильтр».
4. Заполните поля в окне «Пользовательский автофильтр» как указано на рисунке и нажмите ОК.

Как видно в столбце «№п/п» отсутствуют некоторые номера, что подтверждает о фильтрации данных в таблице. Так же обратите внимание, как изменились кнопки выпадающих списков на столбцах C и F. Так как они содержать настройки их Excel отметил значком воронки. Теперь если нам нужно вернуть исходный вид базы данных о клиентах мы знаем, по каким столбцам нужно изменить настройки фильтрации. Но еще быстрее сбросить фильтр можно выбрав по новой инструмент «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр».

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

В языках баз данных (SQL и т.п.) строки таблиц считаются рядами, а столбцы – полями. Названия полей записаны в самой первой строке (заголовок таблицы) и эта строка не является рядом базы данных. Все инструменты для работы с электронными таблицами требуют организованной структуры для заполняемых данных. Поэтому не стоит хаотично заполнять ячейки листа. А придерживаться простой табличной структуры: столбцы должны иметь заголовки, а строки должны заполняться неразрывно. Только тогда мы имеем возможность обрабатывать данные в Excel с максимальной эффективностью.

Как сортировать и фильтровать данные

Вы можете сортировать данные в алфавитном или числовом порядке, а также скрывать информацию с помощью фильтров.

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

Как отсортировать данные в алфавитном или числовом порядке

1. Откройте файл в Google Таблицах на компьютере.
2. Выделите ячейки, которые нужно упорядочить.
3. Если на листе есть строка заголовка, закрепите ее.
4. Нажмите ДанныеСортировать диапазонРасширенные настройки сортировки диапазонов.
5. Если у столбцов есть заголовки, установите флажок Данные со строкой заголовка.
6. Укажите столбец, в котором вы хотите выполнить сортировку в первую очередь, и выберите нужный порядок.
7. При необходимости нажмите Добавить ещё один столбец для сортировки.
8. Нажмите Сортировать.

Как отсортировать данные на всем листе

1. Откройте файл в Google Таблицах на компьютере.
2. В верхней страницы нажмите правой кнопкой мыши на букву, относящуюся к нужному столбцу.
3. Выберите Сортировать лист А–Я или Сортировать лист Я–А.

Сортировка по цвету

1. Откройте файл в Google Таблицах на компьютере.
2. Выделите диапазон ячеек.
3. Выберите ДанныеСоздать фильтр.
4. Чтобы увидеть доступные фильтры, нажмите на значок фильтра в первой строке диапазона.
   • Сортировать по цвету: выберите, по какому цвету текста или заливки нужно отфильтровать или отсортировать данные. Ячейки с указанным вами цветом переместятся в верхнюю часть диапазона. Сортировку можно выполнять по условным, но не по альтернативным цветам.
5. Если фильтр вам больше не нужен, нажмите ДанныеУдалить фильтр.

Как фильтровать данные

Внимание! Добавленный фильтр будут видеть все пользователи, которым доступна таблица. Изменить его смогут все, кому разрешено редактировать таблицу.

1. Откройте файл в Google Таблицах на компьютере.
2. Выделите диапазон ячеек.
3. Выберите ДанныеСоздать фильтр.
4. Чтобы увидеть доступные фильтры, нажмите на значок фильтра в первой строке диапазона.
   • Фильтровать по условию: выберите условия из списка или введите собственные.
   • Фильтровать по значению: чтобы скрыть элемент, снимите флажок рядом с ним и нажмите «ОК».
   • Поиск: введите запрос в окне поиска.
   • Фильтровать по цвету: выберите, по какому цвету текста или заливки нужно отфильтровать данные. Сортировку можно выполнять по условным, но не по альтернативным цветам.
5. Если фильтр вам больше не нужен, нажмите ДанныеУдалить фильтр.

Как создать, сохранить, удалить или передать режим фильтрации

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

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

1. Откройте файл в Google Таблицах на компьютере.
2. Выберите ДанныеФильтрыСоздать новый фильтр.
3. Отсортируйте и отфильтруйте данные.
4. Чтобы выйти из режима фильтрации, в правом верхнем углу экрана нажмите на значок «Закрыть» .
5. Заданные настройки сохранятся автоматически.

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

Если вам нужно удалить все фильтры, откройте каждый режим фильтрации, а затем нажмите на значок настроек Удалить.

Примечание. Изменять порядок режимов фильтрации нельзя.

Как посмотреть существующий режим фильтрации

Внимание! Применять несколько режимов фильтрации одновременно нельзя.

1. Откройте файл в Google Таблицах на компьютере.
2. Нажмите ДанныеФильтры.
3. Выберите режим фильтрации.
4. Он будет применен к таблице.
5. Чтобы выйти из режима фильтрации, в правом верхнем углу экрана нажмите на значок «Закрыть» .

Как сохранить фильтр в качестве режима фильтрации

1. Откройте файл в Google Таблицах на компьютере.
2. Примените фильтр.
3. Выберите ДанныеФильтрыСохранить как фильтр.

Как переименовать режим фильтрации

1. Откройте файл в Google Таблицах на компьютере.
2. Нажмите ДанныеФильтры.
3. Выберите режим фильтрации.
4. В левом верхнем углу таблицы рядом с пунктом «Название» нажмите на название фильтра и укажите новое название.
5. Нажмите клавишу «Ввод».

1. On your computer, open a spreadsheet in Google Sheets.
2. Apply the filter view .
3. Copy the URL.
4. Share the filter view link.

Дополнительные сведения о фильтрах и режимах фильтрации

Фильтры и режимы фильтрации обеспечивают более удобную работу с данными таблицы.