Программа Microsoft Excel: сортировка и фильтрация данных - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

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

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

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

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

Сортировка списков

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

Сделайте небольшой список для тренировки.

Нажмите кнопку “Сортировка и фильтр” на панели “Редактирование” ленты “Главная”.

Выберите “Сортировка от А до Я”. Наш список будет отсортирован по первому столбцу, т.е. по полю ФИО.

Если надо отсортировать список по нескольким полям, то для этого предназначен пункт “Настраиваемая сортировка..”.

Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки “Добавить уровень”.

В итоге список будет отсортирован, согласно установленным параметрам сложной сортировки.

Если надо отсортировать поле нестандартным способом, то для этого предназначен пункт меню “Настраиваемый список..” выпадающего списка “Порядок”.

Перемещать уровни сортировки можно при помощи кнопок “Вверх” и “Вниз”.

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

Фильтрация списков

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

Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.

Для применения автофильтра нажмите ту же кнопку, что и при сортировке – “Сортировка и фильтр” и выберите пункт “Фильтр” (конечно же, перед этим должен быть выделен диапазон ячеек).

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

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

Для формирования более сложных условий отбора предназначен пункт “Текстовые фильтры” или “Числовые фильтры”. В окне “Пользовательский автофильтр” необходимо настроить окончательные условия фильтрации.

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

Для этого надо сделать следующее.

Скопируйте и вставьте на свободное место шапку списка.

В соответствующем поле (полях) задайте критерии фильтрации.

Выделите основной список.

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

На той же панели нажмите кнопку “Дополнительно”.

В появившемся окне “Расширенный фильтр” задайте необходимые диапазоны ячеек.

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

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

В начало страницы

В начало страницы

Программа 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».

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

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

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

Учебное занятие по теме «MS Excel. Фильтрация (выборка) данных из списка»

ТЕХНОЛОГИЧЕСКАЯ КАРТА УЧЕБНОГО ЗАНЯТИЯ
по МДК 01.05 «Компьютерная обработка документов»

ФИО преподавателя Кравчук Елена Владимировна

Тема учебного занятия: MS Excel. Фильтрация (выборка) данных из списка

Цели учебного занятия:

дать представление о возможностях табличного процессора MS Excel в области сортировка и фильтрации больших объемов данных;

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

сформировать навыки сортировки, фильтрации и поиска данных в программе MS Excel.

2. Развивающая: Способствовать развитию логического мышления, развитию памяти, внимательности, наблюдательности. Способствовать развитию умения сравнивать, обобщать, анализировать.

3. Воспитательная: воспитывать позитивное отношение к учебной деятельности.

Тип учебного занятия: комбинированный урок

Форма обучения: индивидуальная, групповая, фронтальная.

Методы обучения: словесный, наглядный, репродуктивный, практический.

Средства обучения: ПК, проектор, раздаточный материал.

Развиваемые компетенции: ОК 1,2,4,5, ПК 1.4 – 1.6

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

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

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

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

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

ПК 1.4. Организовывать рабочее место секретаря и руководителя.

ПК 1.5. Оформлять и регистрировать организационно-распорядительные документы, контролировать сроки их исполнения.

ПК 1.6. Обрабатывать входящие и исходящие документы, систематизировать их, составлять номенклатуру дел и формировать документы в дела.

ХОД УЧЕБНОГО ЗАНЯТИЯ

Содержание и структура

Развиваемые профессиональные
и общие компетенции

Подготовка студентов к работе на УЗ

Приветствует студентов, настраивает на работу. ТБ

Приветствуют преподавателя, настраиваются на работу.

Объявление темы урока

Сообщает тему урока

Предлагает сформулировать цели урока

Формулируют цели урока

Откройте программу MS Excel. Вспомним основные принципы работы

Открывают программу. Отвечают на вопросы

Объяснение нового материала

Объясняет новый материал.

Демонстрирует технологию работы

Отвечает на вопросы студентов

Записывают новые понятия, алгоритм выполнения технологических операций

Задают вопросы по технологии работы

Раздает методический материал

Выполняют практическую работу

Подходит к студентам, следит за ходом выполнения работы

Выполняют практическую работу

Учащиеся дают оценку деятельности по ее результатам

Задание для внеаудиторной работы

Задает задание для внеаудиторной работы. Прощается.

Записывают задание. Прощаются

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

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

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

Картинка ребуса на экране

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

(ответ: табличный процессор Microsoft Excel).

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

(ответ: сортировка и фильтрация данных).

2. Итак, запишем тему урока с тетрадь

Практическая работа № 15.

Тема: Табличный процессор Microsoft Excel: сортировка и фильтрация данных.

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

Фильтрация данных – это выбор данных, соответствующих какому-либо условию, критерию. Фильтрация в MS Excel выполняется из вкладки Данные раздел Сортировка и фильтр двумя способами:

▪ с помощью команды Фильтр;

▪ с помощью команды Дополнительно (Расширенный фильтр).

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

Список критериев для каждого столбца содержит следующие варианты критериев:

все – выбираются все записи поля;

первые 10 – появляется окно Наложение условия по списку, в котором выбираются количество выводимых записей (наибольших или наименьших) в элементах списка или процентах от количества элементов;

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

условие – нажать кнопку списка, выбрать Текстовые фильтры Настраиваемый фильтр – пользователь формирует условие отбора в окне Пользовательский автофильтр (равно, больше, …И, ИЛИ…) и записи фильтруются по этому условию.

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

Расширенный фильтр задаётся командой Данные Дополнительно в окне

При этом используется два типа критериев для фильтрации записей:

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

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

1) Сначала формируется диапазон условия (область критерия) в отдельном блоке ячеек листа MS Excel – вне таблицы БД, на свободном месте Листа.

2) Затем командой Данные Дополнительно в окне Расширенный фильтр выполняется фильтрация записей списка по сформированному вне таблицы множественному критерию.

3. Открываем программу MS Excel.

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

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

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

Рис. 1. База данных (список) MS Excel

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

Читайте также:  Создание базы данных в Microsoft Excel

MS Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям.

1. Список обязательно должен содержать строку заголовков.

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

3. В списке не должно быть пустых строк.

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

На рабочем листе выделяют следующие области.

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

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

3. Диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка. Этот диапазон должен быть расположен на том же листе, что и список.

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

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

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

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

ФамилияРостВесВозрастСтрана
ВоробьевРоссия
ВоронинУкраина
ГалкинУкраина
СиницынУкраина
СорокинРоссия
ЧижовРоссия

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

В меню “Главная” нужно найти пункт “Сортировка и фильтр” и выбрать в ней “Настраиваемая сортировка“. В открывшемся окне мы будем задавать параметры которые будут использоваться при сортировке:

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

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

Если посмотреть варианты сортировки, то в Excel 2010 они довольно впечатляют, сортировать можно не только по какому либо значению, но и даже по цвету (!). На мой взгляд полезность такой фичи весьма сомнительна. После нажатия кнопки ОК, эксель отсортировал строки следующим образом:

Сортировка это конечно здорово но как выбрать из громадного объема данных только те которые мы хотим видеть? Конечно, если нам надо посмотреть человека например с минимальным весом, то просто сортируем и смотрим конец (или начало) таблицы. А что если нам нужно выбрать к примеру людей с возрастом от 21 до 30? Или только русских, или только высоких и худых ?

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

Что бы применить такой фильтр нужно нажать “Главная” ->”Сортировка и фильтр” -> “Фильтр“. Как и в случае с сортировкой не забываем выделять не только данные но и заголовок. Сразу после выбора этого пункта меню, в заголовке каждого столбца у нас появятся галочки. Нажимая на них можно давать фильтрацию для конкретного столбца. Применим фильтр который отсеет людей ниже 180 сантиметров. Щелкаем по галочке и выбираем там Числовые фильтры -> Больше или равно.

В открывшемся окне вводим что нас интересуют люди от 180 и выше. После нажатия кнопки ОК фильтр начнет действовать и из таблицы исчезнут некоторые строки и остануться только такие:

ФамилияРостВесВозрастСтрана
ГалкинУкраина
СорокинРоссия
ЧижовРоссия

Теперь из оставшихся людей выберем тех кто весит менее 100 кг. Для этого применим второй фильтр, но уже для столбца “Вес”. Действовать в этом случае нужно аналогичным образом, только в числовых фильтрах нужно выбирать “Меньше” и вписать число 100 в поле открывшегося окна. После этой манипуляции было бы логично предположить, что останется одна единственная строчка с Сорокиным:

ФамилияРостВесВозрастСтрана
СорокинРоссия

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

Сортировка и фильтрация – порядок действий

Откройте в Microsoft Excel электронную таблицу, данные которой требуют сортировки.

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

Сокращенный вариант этого списка можно вызвать и через меню табличного редактора – для этого используйте кнопку «Сортировка» в группе команд «Редактирование» на вкладке «Главная». В этом случае список будет содержать только команды упорядочивания по возрастанию и убыванию.

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

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

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

Нажмите кнопку OK, и Excel отсортирует данные по заданной схеме.

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

Цель работы: научиться создавать базы данных, выполнять

сортировку и фильтрацию данных

Содержание работы:

1 Создание списка – базы данных

2 Сортировка данных в списке

3 Фильтрация данных в списке

Общие сведения

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

База данных (БД) – это поименованная совокупность данных, имеющая одно или несколько приложений. Например, список группы является БД студентов. Её приложение для деканата – это список студентов, обучающихся в академической группы № …, приложение для военкомата – список призывников, для телефонной компании – список абонентов и т.д.

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

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

Читайте также:  Создание формул в программе Microsoft Excel

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

Для работы с БД в Excel используются формы вывода данных (команда ДанныеФорма), можно создавать запросы на данные из созданного списка и из внешних БД (команда ДанныеИмпорт внешних данныхСоздать запрос), обновлять данные через Internet и др.

Создание списка – базы данных

После запуска приложения Excel нужно ввести таблицу БД (рис. 1). Поскольку имя каждого поля должно занимать одну ячейку, нужно выделить строку для заголовка таблицы, затем в меню ФорматЯчейки…, в окне Формат ячеек вкладкаВыравнивание установить параметры:

▪ по горизонтали: по значению

▪ по вертикали: по верхнему краю

▪ отображение: переносить по словам (если Имя поля – длинное)

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

Рисунок 1 Список Экзаменационная ведомость

Сортировка данных в списке

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

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

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

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

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

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

Проведём сортировку списка “Экзаменационная ведомость” по полям Код преподавателя, ОценкаиФамилия И.О.

Рисунок 2 Окна Сортировка диапазона и Параметры сортировки

Результат сортировки показан на рис. 3, где столбецЕ отсортирован по возрастанию, в столбце D по возрастанию отсортированы одинаковые записи столбца Е, а в столбце В – по алфавиту одинаковые значения в столбце D.

Рисунок 3 Результат сортировки данных

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

задать сортировку с учётом или без учёта регистра, по столбцам или по

строкам, обычный или выбранный из списка. Этот список можно создать самостоятельно с помощью команды СервисПараметры, вкладка Списки.

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

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

▪ с помощью команды Автофильтр и

▪ с помощью команды Расширенный фильтр.

Автофильтр

Автофильтрация позволяет выбрать критерий отбора из предлагаемых в приложении для каждого столбца отдельно. Фильтрация поля “Оценка” по условию “4” оставляет от исходного списка только записи с оценкой “4” (рис. 4)

Рисунок 4 Список студентов, получивших отметку 4

Дальнейшая фильтрация по другому полю выполняется для уже отфильтрованных данных, например, автофильтр по коду преподавателя 1 приведёт к следующему результату (рис. 5):

Рисунок 5 Список студентов, получивших отметку 4

у преподавателя с кодом 1

Список критериев для каждого столбца содержит следующие варианты критериев:

▪ все – выбираются все записи поля;

▪ первые 10 – появляется окно Наложение условия по списку, в котором выбираются количество выводимых записей (наибольших или наименьших) в элементах списка или процентах от количества элементов;

▪ значения – отфильтровываются только записи, содержащие в данном

столбце указанное значение;

▪ условие – пользователь формирует условие отбора в окне Пользовательский автофильтр (равно, больше, …И, ИЛИ…) и записи фильтруются по этому условию.

Отмена результата фильтрации (для поля) выполняется либо повторной фильтрацией по критерию все, либо – для всего списка – повторным вводом команды ДанныеАвтофильтр.

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

Расширенный фильтр задаётся командойДанные Фильтр Расширенный фильтр. При этом используется два типа критериев для фильтрации записей:

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

Особенностью расширенного фильтра является то, чтоотбор данных производится в 2 этапа:

1 Формируется диапазон условия (область критерия) в отдельном блоке ячеек листа Excel.

2 Фильтрация записей списка по сформированному множественному критерию.

Последнее изменение этой страницы: 2016-04-19; Нарушение авторского права страницы

MS 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].
Ссылка на основную публикацию
Adblock
detector