Программа Microsoft Excel: сортировка и фильтрация данных

Фильтрация данных в Excel

В Excel предусмотрено три типа фильтров:

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

Автофильтр

  1. Выделить одну ячейку из диапазона данных.
  2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
  3. Щелкнуть по кнопке Фильтр [Filter] .

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

Варианты фильтрации данных

  • Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
  • Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
  • Можно воспользоваться строкой быстрого поиска
  • Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:
  1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
  2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
  3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
  4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

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

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

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

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

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

    Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

  1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

  1. Выделить срез.
  2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

  1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Расширенный фильтр

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

Задание условий фильтрации

  1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

  1. Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
  2. Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
  3. Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
  4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Программа Microsoft Excel: сортировка и фильтрация данных

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

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

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

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

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

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

Для того, чтобы выполнить сортировку в обратном порядке, в том же меню выбираем кнопку Сортировка от Я до А».

Список перестраивается в обратном порядке.

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

Настраиваемая сортировка

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

А, что делать, если мы хотим отсортировать имена по алфавиту, но например, при совпадении имени сделать так, чтобы данные располагались по дате? Для этого, а также для использования некоторых других возможностей, все в том же меню «Сортировка и фильтр», нам нужно перейти в пункт «Настраиваемая сортировка…».

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

В поле «Столбец» указываем наименование столбца, по которому будет выполняться сортировка. В нашем случае, это столбец «Имя». В поле «Сортировка» указывается, по какому именно типу контента будет производиться сортировка. Существует четыре варианта:

  • Значения;
  • Цвет ячейки;
  • Цвет шрифта;
  • Значок ячейки.

Но, в подавляющем большинстве случаев, используется пункт «Значения». Он и выставлен по умолчанию. В нашем случае, мы тоже будем использовать именно этот пункт.

В графе «Порядок» нам нужно указать, в каком порядке будут располагаться данные: «От А до Я» или наоборот. Выбираем значение «От А до Я».

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

Появляется ещё один набор полей, который следует заполнить уже для сортировки по другому столбцу. В нашем случае, по столбцу «Дата». Так как в данных ячеек установлен формат даты, то в поле «Порядок» мы устанавливаем значения не «От А до Я», а «От старых к новым», или «От новых к старым».

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

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

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

В открывшемся окне параметров сортировки, переводим переключатель из позиции «Строки диапазона» в позицию «Столбцы диапазона». Жмем на кнопку «OK».

Теперь, по аналогии с предыдущим примером, можно вписывать данные для сортировки. Вводим данные, и жмем на кнопку «OK».

Как видим, после этого, столбцы поменялись местами, согласно введенным параметрам.

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

Фильтр

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

Чтобы воспользоваться данной функцией, становимся на любую ячейку в таблице (а желательно в шапке), опять жмем на кнопку «Сортировка и фильтр» в блоке инструментов «Редактирование». Но, на этот раз в появившемся меню выбираем пункт «Фильтр». Можно также вместо этих действий просто нажать сочетание клавиш Ctrl+Shift+L.

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

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

Когда процедура выполнена, жмем на кнопку «OK».

Как видим, в таблице остались только строки с именем работника Николаева.

Усложним задачу, и оставим в таблице только данные, которые относятся к Николаеву за III квартал 2016 года. Для этого, кликаем по значку в ячейке «Дата». В открывшемся списке, снимаем галочки с месяцев «Май», «Июнь» и «Октябрь», так как они не относятся к третьему кварталу, и жмем на кнопку «OK».

Читайте также  Построение линии тренда в Microsoft Excel

Как видим, остались только нужные нам данные.

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

Если же вы хотите сбросить фильтр в целом по таблице, тогда нужно нажать кнопку «Сортировка и фильтр» на ленте, и выбрать пункт «Очистить».

Если нужно полностью удалить фильтр, то, как и при его запуске, в этом же меню следует выбрать пункт «Фильтр», или набрать сочетание клавиш на клавиатуре Ctrl+Shift+L.

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

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

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

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

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

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

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

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

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

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

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

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

Тема: Сортировка и фильтрация данных в электронной таблице. Условное форматирование.

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

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

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

Тип урока: усвоение новых знаний, формирование умений и навыков.

Оборудование: доска, компьютер, программное обеспечение.

Организационный момент. Приветствие, проверка присутствующих. Объяснение хода урока.

Какие возможности вы уже рассмотрели?

Какие арифметические действия есть в экселе, как они записываются.

Какие существуют правила записи ( = , скобки, не само значение, а адрес ячейки)

Типы данных (общий, числовой, текстовый, денежный, финансовый, дата, время, процентный)

Математические ( ABS , COS , ACOS , LOG , LN , EXP , ОКРУГЛ, КОРЕНЬ, НОД, НОК, СТЕПЕНЬ и т.д.)

Логические функции (И,ИЛИ, ЛОЖЬ, ИСТИНА, НЕ, ЕСЛИ, ЕСЛИОШИБКА)

Размер листа 1 048 576 строк и 16 384 столбца

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

Сортировка данных может проводится по возрастанию (от наименьшего к наибольшему) или по убыванию (от наибольшего к наименьшему).

В Excel 2007 сортировка данных по возрастанию выполняется по определенным правилам:

— символы упорядочиваются в порядке размещения их кодов в кодовой таблице Unicode .

— числа и даты упорядочиваются от наименьшего значения к наибольшему.

— тексты, сначала по первым символам, при их совпадении со вторыми и т.д.

— логическое значения ЛОЖЬ размещается перед значением ИСТИНА.

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

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

Для выполнения сортировки необходимо выделить диапазон ячеек (чтобы выделить диапазон ячеек необходимо щелкнуть ЛКМ на начальную ячейку диапазона и не отпуская ЛКМ растянуть на необходимые ячейки), к которым мы хотим применить сортировку, далее существует несколько способов сортировки:

Вкладка Данные в группе Сортировка и фильтр

Вкладка Главная – Редактирование – Сортировка и фильтр

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

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

Фильтрация – это выбор данных в ячейках электронной таблицы, которые соответствуют определенным условиям.

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

— Главная – Редактирование – Сортировка и фильтр – Фильтр

— Данные – Сортировка и фильтр – Фильтр

После чего около правой границы каждой ячейки первой строки появятся кнопки открытия списка. В этих списках находятся:

— команды сортировки данных по значениям данного столбца

— команда Фильтр по цвету

— команда Снять фильтр с

— флажок Выделить все

-флажки для значений в ячейках данного столбца.

— команда открытия меню команд для установки условий фильтрации:

Числовые фильтры ( команды: =, !=, >, >=, после чего открывается Пользовательский автофильтр в котором можно установить условие фильтрации: простое или составное из двух простых , соединенные логическими операциями И или ИЛИ.

Текстовые фильтры ( если в столбце текстовые данные)

Фильтры по дате ( если в столбце даты)

— Команда Первые 10 открывает окно Наложение условий по списку , в котором можно

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

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

Чтобы отменить фильтрацию , можно выполнить:

— Данные – Сортировка и фильтр – Очистить

— Главная – Редактирование – Сортировка и фильтр – Очистить

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

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

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

Вкладка Данные – Сортировка и фильтр – Дополнительно в окне Расширенный фильтр выбрать область расположения результата фильтрации, ввести адреса Исходного диапазона и Диапазон условий .

Практическая часть. Сортировка и фильтрация. xlsx .

Итоги урока. Домашнее задание.

Цели урока:

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

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

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

Тип урока: усвоение новых знаний, формирование умений и навыков.

Оборудование: доска, компьютер, программное обеспечение.

Ход урока:

  1. Организационный момент. Приветствие, проверка присутствующих. Объяснение хода урока.
  2. Актуализация.

Какие возможности вы уже рассмотрели?

Какие арифметические действия есть в экселе, как они записываются.

Какие существуют правила записи ( = , скобки, не само значение, а адрес ячейки)

Типы данных (общий, числовой, текстовый, денежный, финансовый, дата, время, процентный)

Математические (ABS, COS, ACOS, LOG, LN, EXP, ОКРУГЛ, КОРЕНЬ, НОД, НОК, СТЕПЕНЬ и т.д.)

Логические функции (И,ИЛИ, ЛОЖЬ, ИСТИНА, НЕ, ЕСЛИ, ЕСЛИОШИБКА)

Размер листа 1 048 576 строк и 16 384 столбца

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

Сортировка данных может проводится по возрастанию (от наименьшего к наибольшему) или по убыванию (от наибольшего к наименьшему).

В Excel 2007 сортировка данных по возрастанию выполняется по определенным правилам:

— символы упорядочиваются в порядке размещения их кодов в кодовой таблицеUnicode.

— числа и даты упорядочиваются от наименьшего значения к наибольшему.

— тексты, сначала по первым символам, при их совпадении со вторыми и т.д.

— логическое значения ЛОЖЬ размещается перед значением ИСТИНА.

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

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

Для выполнения сортировки необходимо выделить диапазон ячеек (чтобы выделить диапазон ячеек необходимо щелкнуть ЛКМ на начальную ячейку диапазона и не отпуская ЛКМ растянуть на необходимые ячейки), к которым мы хотим применить сортировку, далее существует несколько способов сортировки:

Вкладка Данные в группе Сортировка и фильтр

Вкладка Главная – Редактирование – Сортировка и фильтр

— ПКМ – Сортировка

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

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

Читайте также  Расчет коэффициента детерминации в Microsoft Excel

Фильтрация – это выбор данных в ячейках электронной таблицы, которые соответствуют определенным условиям.

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

— Главная – Редактирование – Сортировка и фильтр – Фильтр

— Данные – Сортировка и фильтр – Фильтр

После чего около правой границы каждой ячейки первой строки появятся кнопки открытия списка. В этих списках находятся:

— команды сортировки данных по значениям данного столбца

— команда Фильтр по цвету

— команда Снять фильтр с

— флажок Выделить все

-флажки для значений в ячейках данного столбца.

— команда открытия меню команд для установки условий фильтрации:

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

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

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

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

Рассмотрим разные варианты сортировки данных.

Вариант 1: Простая сортировка данных

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

Рассмотрим в качестве примера сортировку сотрудников по фамилиям в алфавитном порядке:

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

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

Стоит отметить некоторые особенности работы этого инструмента сортировки:

  • Пункты настроек «От А до Я» и «От Я до А» указываются в том случае, если в ячейках в первую очередь идёт текстовая информация;
  • Если в ячейках в первую очередь расположены числовые значения, то пункты меняются на «От минимальному к максимальному» и «От максимального к минимальному»;
  • Если в ячейках присутствуют даты, то пункты будут изменены на «Сортировка от старых к новым» и «Сортировка от новых к старым».

Это была самая простая сортировка информации в Excel. Однако существует возможность настроить и более продвинутое распределение.

Вариант 2: Настраиваемая сортировка

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

Рассмотрим, как можно реализовать задуманное:

  1. Снова выделите нужные диапазоны данных и нажмите по инструменту «Сортировка и фильтрация».
  2. В контекстном меню перейдите в «Настраиваемая сортировка».
  3. Будет открыто окошко настройки сортировок. Там обязательно нужно установить галочку у «Мои данные содержат заголовки». Не нужно её ставить только в том случае, если столбцы с информацией не имеют заголовков, типа «Дата», «Пол», «Имя» и т.д.
  4. Обратите внимание на поле «Столбец». Туда надо написать наименование столбца, по которому и происходит сортировка. Так как мы сортируем данные в первую очередь по именам, то прописываем там «Имя».
  5. Поле «Сортировка» отвечает за тип контента, по которому и будет сортироваться информация. Здесь есть четыре варианта:
    • Значения;
    • Цвет ячейки;
    • Цвет шрифта;
    • Значок ячейки.
  6. В нашем случае будем использовать первый вариант, который и так стоит там по умолчанию. В большинстве случаев он походит, поэтому менять его необязательно.
  7. В поле «Порядок» нужно указать значение «От А до Я».

Таким образом вы настроили сортировку по алфавиту, как мы это делали в предыдущей инструкции. Однако в текущей ситуации таблицу нужно сортировать не только по алфавиту, но и по значению дат, если имена будут попадаться одинаковые имена. Это настраивается с помощью добавления ещё одного условия:

  1. В верхней левой части окна нажмите на кнопку «Добавить уровень».
  2. Появится ещё один набор полей, который во многом похож на предыдущий. Однако в данном случае сортировка происходит по столбцу «Дата». Его наименование и нужно прописать в соответствующее поле.
  3. В поле «Сортировка» также оставляете «Значения».
  4. В поле «Порядок» на сей раз нужно прописать «От старых к новым», так как у нас присутствуют в выбранном столбце даты.
  5. На этом процесс сортировки можно считать завершённым. Жмите «Ок», чтобы подтвердить настройки.

После проделывания подобных манипуляций всё будет сортировано в нужном нам формате – в алфавитном порядке, а при совпадении имён вверх будут попадать те, что были добавлены в таблицу раньше (если верить столбцу «Дата»).

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

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

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

Вариант 3: Настройка фильтрации

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

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

  1. Использование этой функции возможно, если выбрать любую ячейку в таблице. Лучше, конечно, выбирать заголовок столбца.
  2. После этого нажмите по кнопке «Сортировка и фильтр». Из контекстного меню нужно выбрать «Фильтр». Также его можно вызвать, воспользовавшись сочетанием клавиш Ctrl+Shift+L.
  3. Напротив всех столбцов с заголовка появились иконки в виде квадрата со стрелками. Кликните по кнопке того столбца, который вам нужно отсортировать.
  4. Откроется всплывающее окошко с настройками. Для примера оставим данные только какого-то одного работника, а остальных скроем. Чтобы это реализовать, снимите галочки с ненужных элементов.
  5. Закончив с выделением нужных сотрудников, жмите на «Ок».

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

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

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

  1. Напротив названия нужного столбца нажмите по иконке фильтра. Наличие таковой означает, что к данному столбцу уже применён фильтр.
  2. Здесь можно внести изменения в настройки, например, выбрать другую дату и/или другого сотрудника. После нажатия на «Ок» перед вами будут отображены данные указанного сотрудника.
  3. Если вам требуется совсем удалить фильтр, жмите на кнопку «Удалить фильтр с (название столбца)».
  4. При условии, что в таблице много фильтров, сбрасывать каждый вручную будут слишком долго по времени. В качестве альтернативы просто нажмите по кнопке «Сортировка и фильтр», а там выберите пункт «Очистить».

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

Вариант 4: Настройка «Умной таблицы»

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

Чтобы создать «умную таблицу», нужно проделать следующие действия:

  1. Выделите всю область с данными, которую собираетесь преобразовать в таблицу такого типа.
  2. Во вкладке «Главная» воспользуйтесь пунктом «Форматировать как таблицу». Этот инструмент расположен в блоке «Стили».
  3. Из предложенных вариантов цветового оформления таблицы можете выбрать любой. Это никак не повлияет на функционал, а только на внешний вид.
  4. После проделанных действий должно открыться окошко, в котором можно поменять координаты таблицы. Если до этого вы делали выделение, то ничего в этом окошке дополнительно настраивать не требуется. Однако, если вы при выделении допустили где-то ошибку, то её можно будет легко исправить в этом окне.
  5. После применения стиля вы можете видеть, что таблица внешне сильно преобразилась в соответствии с тем стилем, что был выбран ранее. Для дальнейшей работы откройте вкладку «Вставка».
  6. Там нужно будет воспользоваться кнопкой «Таблица».
  7. Снова откроется окошко для указания координат. Здесь ничего менять не нужно, просто жмите на «Ок».
  8. После проделанных процедур у столбцов таблицы должны появится иконки фильтров, как это было в способе, где описывалось создание фильтров.
  9. При нажатии на одну из таковых иконок появляется интерфейс «Сортировка и фильтр». Здесь функционал и интерфейс то же не будет отличаться от того, который рассматривался в способе с созданием фильтров для ячеек.

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

Читайте также  Нумерация столбцов в Microsoft Excel

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

Сортировка и фильтр в 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 с максимальной эффективностью.

Функция сортировки и фильтрации в Excel

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

Проведем сортировку данных нашей базы данных «Сотовые телефоны» по двум параметрам: «Цвет» и «Количество».

Алгоритм действий во время сортировки

• копируем исходные данные на лист «Сортировка»;
• на вкладке «Данные» выбираем кнопку «Сортировка», в результате чего появляется окно «Сортировка»;
• в этом окне есть ряд кнопок: «Добавить уровень», «Удалить уровень», «Копировать», «Параметры»;
• нажимаем на кнопку «Параметры» и появляется окно «Параметры сортировки», где включаем переключатель «Строки диапазона» и нажимаем на кнопку «ОК»:

• нажимаем «Добавить уровень», появляется строка с именем (слева) «Сортировать по», и рядом поле со списком с именем «Сортировать по»;
• щелкаем кнопкой мыши по стрелке правой части поля, выплывает список всех имен полей нашей базы данных:

Фильтрация. Автофильтр.

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

Алгоритм использования автофильтра:

  • скопируем исходные данные на новый лист и дадим ему имя «Фильтрация»;
  • установим курсор внутри таблицы;
  • в вкладках «Главная», группа «Редактирование», кнопка «Сортировка и фильтр», команда «Фильтр»;
  • около имени каждого поля таблицы появляются кнопки ;
  • при нажатии на кнопку, появляется меню, состоящее из 3х разделов: 1.сортировка (еще один способ сортировки); 2.фильтрация (где есть ряд команд, позволяющие фильтровать по дате, по тексту, по числовым данным);

Позволяет сразу удалить данные из списка, который фигурирует:

• выбрав ту или иную команду (числовой, текстовый, фильтр по дате) справа получаем еще одно меню, состоящее из множества команд, которые можно использовать в зависимости от условия задачи.
В нашей базе данных «Сотовые телефоны» установим автофильтр по следующим значениям: «ос», где выбираем «Android», и количество ядер процессора 4:

Найти среднее значение цены.

В отфильтрованной базе данных «Сотовые телефоны» по параметрам «ос» и «количество ядер процессора», найдем среднее значение цены.
Для этого можно воспользоваться «Строкой состояния».
Алгоритм действий:
• выделяем столбец «Цена»;
• щелкаем строку состояния правой кнопкой мыши;
• выбираем нужные параметры.
Так же можно использовать второй способ, воспользовавшись функцией «Среднего значения».
Алгоритм действий для «Среднего значения»:
• в отфильтрованных данных по параметрам «Наименование» и «Цена» выделяем столбец «Цена»;
• вкладка «Главная», группа «Редактирование», кнопка «Сумма» , в появившемся контекстном меню выбираем «Среднее»;
• под столбцом «Цена» отображается среднее значение цены.
Вариант 1(при помощи строки состояния):

Вариант 2 (при помощи функции «Среднее значение»):

Задание 5. Отфильтровать список по критериям.
Отфильтруем список по следующим критериям:
Место отгрузки: зал;
Фотокамера: 8Mpx;
В полученной таблице отсортируем по возрастанию цены и определим ее общую стоимость; ее количество и среднюю цену. Все результаты занесем в таблицу.

Цена ниже средней.

Для выполнения данного задания используем также «Фильтрацию».
Алгоритм действий:
• скопируем исходные данные на новый лист и дадим ему имя «Фильтрация»;
• установим курсор внутри таблицы;
• в вкладках «Главная», группа «Редактирование», кнопка «Сортировка и фильтр», команда «Фильтр»;
• около имени каждого поля таблицы появляются кнопки ;
• щелкаем мышью по кнопке в столбце «Место отгрузки», при нажатии на кнопку, появляется меню, состоящее из 3х разделов: 1.сортировка (еще один способ сортировки); 2.фильтрация (где есть ряд команд, позволяющие фильтровать по дате, по тексту, по числовым данным); 3.позволяет сразу удалить данные из списка, который фигурирует;
• для фильтрования продукции, отпускаемой только со склада, используем фильтр:

• для фильтрации продукции, цена которой ниже средней, используем «числовой фильтр», «цена ниже средней»:

Далее покажем самую дорогую продукцию. Для этого:

• выделяем столбец «Цена»;
• во вкладках «Данные», группа «Сортировка и фильтр», кнопка «Сортировка от А до Я»;
• появляется окно «Обнаружены данные вне указанного диапазона», переключаем на «автоматически расширить выделенный диапазон».
Самая последняя ячейка этого столбца и будет являться максимальной ценой:

При помощи расширенного фильтра сделаем выборку продукции, количество которых больше 10, а их цвет равен белому.
Алгоритм действий:
• Копируем исходную базу данных на новый лист и дадим ей имя «Расширенный фильтр»;
• Выше таблицы указываем искомые параметры;
• Устанавливаем курсор внутри таблицы;
• Вкладка «Данные», группа «Сортировка и фильтр», команда «Дополнительно»;
• Появляется меню расширенного фильтра и автоматически выделяется наша база данных;
• Переключатель на «Фильтровать список на месте», «Исходный диапазон» $A$5:$L$27 (наша база данных),;
• Ставим курсор на «Диапазон условий» и указываем мышью по ячейкам с условием фильтрации;
• Нажимаем «ОК»:

Результат применения расширенного фильтра:

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