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

Содержание

Использование электронной таблицы как базы данных. Сортировка и фильтрация данных в Microsoft Excel 97

//monax.ru/order/ — рефераты на заказ (более 800 авторов в 230 городах СНГ).

Министерство образования и науки Украины

Николаевская общеобразовательная школа № 3

Реферат

по информатике

на тему:

Использование электронной таблицы как базы данных. Сортировка и фильтрация данных в Microsoft Excel 97

ученица 11-А класса

Воронова Елена

Проверила

Девяткова В.Н.

2002Использование электронной таблицы как базы данных. Сортировка и

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

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

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

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

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

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

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

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

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

При фильтрации базы отображаются только записи, обладающие нужными свойствами. Простейшее средство фильтрации – автофильтр. Он запускается командой Данные > Фильтр > Автофильтр.

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

Варианты фильтрации записываются обычно с помощью простых или составных условий. Составные условия формируются с помощью логических выражений и логических операции И или ИЛИ.

Записи, не удовлетворяющие условию фильтрации, не отображаются.

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

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

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

Команда Данные > Фильтр > Отобразить все позволяет отобразить все записи. Чтобы отменить использование автофильтра, надо повторно дать команду Данные > Фильтр > Автофильтр.

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

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

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

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

Фильтры позволяют отобразить пять типов критериев

• Отобразить все. Отображаются все записи в поле

• Расширенный фильтр. Открывается диалоговое окно Расширенный фильтр, позволяющее создать критерии с использованием И либо ИЛИ.

• Точные значения. Отображаются только те записи, которые содержат точные значения в заданных полях.

• Пустые. Отображаются все записи с пустыми полями.

• Непустые. Отображаются все записи с непустыми полями.

При использовании Расширенного фильтранеобходимо сначала определить (создать) три области

 интервал списка — область базы данных, которая подлежит фильтрации;

 интервал критериев — область, где задаются критерии фильтрации;

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

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

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

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

Имена полей во всех интервалах должны точно совпадать.

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

Источник: //globuss24.ru/doc/ispolzovanie-elektronnoi-tablicy-kak-bazy-dannyh-sortirovka-i-filtracia-dannyh-v-microsoft-excel-97

Подготовила

ученица 11-А класса

Воронова Елена

Проверила

2002

фильтрация данных в MicrosoftExcel 97

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

· остальные ряды базы данных должны содержать записи, которые не являются пустыми рядами;

· информация по полям (столбцам) должна быть однородной, т. е. только циифры или только текст;

Сортировка – это упорядочение данных по возрастанию или по убыванию. Проще всего произвести такую сортировку, выбрав одну из ячеек и щелкнув на кнопке Сортировка по возрастанию или Сортировка по убыванию

Параметры сортировки задают командой Данные > Сортировка . При этом открывается диалоговое окно Сортировка диапазона

Данные > Фильтр > Автофильтр.

Варианты фильтрации записываются обычно с помощью простых или составных условий. Составные условия формируются с помощью логических выражений и логических операции И или ИЛИ.

Записи, не удовлетворяющие условию фильтрации, не отображаются.

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

Команда Данные > Фильтр > Отобразить все> Фильтр > Автофильтр.

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

Фильтры позволяют отобразить пять типов критериев

• Отобразить все. Отображаются все записи в поле

•Расширенный фильтр. Открывается диалоговое окно Расширенный фильтр , позволяющее создать критерии с использованием И либо ИЛИ.

• Точные значения. Отображаются только те записи, которые содержат точные значения в заданных полях.

• Пустые. Отображаются все записи с пустыми полями.

• Непустые. Отображаются все записи с непустыми полями.

При использовании необходимо сначала определить (создать) три области

· интервал списка – область базы данных, которая подлежит фильтрации;

· интервал критериев – область, где задаются критерии фильтрации;

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

Имена полей во всех интервалах должны точно совпадать.

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

Источник: //referat-lib.ru/view/referat-computer/68/67363.htm

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

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

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

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

Автофильтр

Включение автофильтра:

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

Фильтрация записей:

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

Выбрать условие фильтрации.

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

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

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

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

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

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

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

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

Срезы

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

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

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

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

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

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

Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
В группе Сервис [Tools] (или на вкладке Вставка [Insert] в группе Фильтры [Filters]) выбрать кнопку Вставить срез [Insert Slicer] .

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

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

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

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

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

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

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

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

Вначале надо скопировать шапку таблицы. Построить таблицу условий отбора данных можно либо на активном листе, либо на другом. Предпочтительнее на другом листе, иначе после фильтрации эти условия или их часть могут быть скрыты.
Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие «И»), а в разных строках – как условие выбора («ИЛИ»).
В качестве условия может быть совпадение значения, которое заносится в ячейку, или сравнение с заданным в ячейке значением с помощью знаков .
Если один столбец должен удовлетворять двум условиям, его заголовок нужно повторить еще раз и записать в этом столбце второе условие.
На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и выбрать команду Дополнительно [Advansed].

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

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

Источник: //micro-solution.ru/excel/ranges/filter

Урок информатики на тему «Сортировка и фильтрация данных (Microsoft Excel)»

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

Дильдебаева Валентина Сергеевнаучитель информатикивторой категорииКГУ «Средняя школа аула Токсан би»района имени Габита Мусрепова СКО

Урок информатики в 8 классе

Цель урока: Ознакомление учащихся с сортировкой и фильтрацией данных, обучение основным приёмам работы с сортировкой и фильтрацией данных в MS Excel.

Задачи:

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

Тип урока: объяснительно-демонстрационный с элементами практикума

Оборудование: интерактивная доска, компьютер, ноутбуки, ПО MS Excel, раздаточный материал.

I. Организационный момент

Взаимное приветствие

— Здравствуйте, ребята! Я рада вас видеть и надеюсь на плодотворное сотрудничество. Наш класс — единая команда, единая цепь, скрепленная знаниями. Каждый ученик — звено одной цепи.

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

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

II. Актуализация знаний учащихся

Сегодня на уроке мы продолжаем работать с электронными таблицами MS Excel.

Сейчас я вам предлагаю ответить на несколько вопросов и выполнить задания на компьютере по пройденной теме на прошлом уроке «Базы данных в MS Excel».

A) Мини блиц-опрос

1. Что называется базой данных?

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

2. Что такое поле?

Строка таблицы — запись базы данных.

3. Что такое запись?

Столбец таблицы — поле базы данных.

Б) Практическая работа

— Создайте базу данных «Водохранилища Казахстана» по предложенному образцу.

— Сохраните базу данных в папке Мои документы под названием «Водохранилища Казахстана».

Учитель контролирует выполнение заданий.

III. Изучение нового материала

Переходим к изучению новой темы. Откройте тетради и запишите тему урока «Сортировка и фильтрация данных».

1. Сортировка данных

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

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

Сортировка — это упорядочение данных по возрастанию или убыванию.

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

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

Тип сортировки:
по возрастанию — цифры сортируются по возрастанию, текст — в алфавитном порядке;
по убыванию — сортировка в обратном порядке;

Щелкнуть по кнопке ОК.

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

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

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

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

В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр.

Для фильтрации данных необходимо:

Выделить одну любую ячейку базы данных;
Дать команду Данные-Фильтр;
Нажать кнопку со стрелкой в столбце и выбрать нужное условие;
В возникшем списке указать условие и записать значение;
Щелкнуть по кнопке ОК.

3. Обучающая практическая работа

Откройте базу данных «Водохранилища Казахстана».
Выполните сортировку по полю Водохранилище (в алфавитном порядке). Результат сортировки скопируйте на Лист 2.
Выполните сортировку по полю Площадь по убыванию.
Переименуйте Лист 2, назовите его — Сортировка. Для этого:

указатель мыши установите на ярлычке Лист 2;правой кнопкой мыши вызовите контекстное меню;

выполните команду Переименовать.

Выполните команду Данные-Фильтр (на полях появились кнопки).
Нажмите на кнопку в поле Год ввода в эксплуатацию. Выберите пункт Числовые фильтры.
В диалоговом окне задайте критерий: Год ввода в эксплуатацию>1967.
Остались водохранилища Капшагайское, Сергеевское, Вячеславское.
Нажмите на кнопку в поле Год ввода в эксплуатацию. Выберите пункт Выделить все и нажмите Ok.

V. Проверочная самостоятельная работа на усвоение нового материала

Уровень, А

1. Откройте базу данных «Крупнейшие озера Казахстана».

2. Отсортируйте данные таблицы «Крупнейшие озера Казахстана» по названию озер (по убыванию). Назовите первые три озера.1) Шалкар2) Шагалалытениз

3) Уялы

3. Отсортируйте данные таблицы «Крупнейшие озера Казахстана» по увеличению высоты над уровнем моря. Назовите первые три озера.1) Шалкар2) Индер

3) Теке

4. В таблице «Крупнейшие озера Казахстана» выберите озера, у которых максимальная глубина более 10 м.1) Аральское море2) Балкаш3) Алаколь4) Маркаколь

5) Шалкар

5. В таблице «Крупнейшие озера Казахстана» выберите озера, у которых высота над уровнем моря менее 50 м.1) Аральское море2) Теке3) Шалкар4) Кызылкак

5) Индер

Уровень В

1. Откройте базу данных «Реки Казахстана».

2. Таблицу «Реки Казахстана» упорядочите по возрастанию площади бассейна, если у некоторых рек совпадает площадь бассейна, упорядочите их по алфавиту. Назовите первые шесть рек.1) Аксу2) Шарын3) Лепси4) Оба5) Калмаккырган

6) Тундык

3. Таблицу «Реки Казахстана» упорядочите по убыванию длины рек в Казахстане, если у некоторых рек совпадает длина в Казахстане, упорядочите их по алфавиту. Назовите первые три реки.1) Ертис2) Есиль

3) Сырдария

4. В таблице «Реки Казахстана» определите, сколько рек с площадью бассейна от 50 до 100 тыс.кв.км.1) Жайык2) Нура3) Иле4) Шу5) Сарысу6) Талас
Количество записей — 6

5. В таблице «Реки Казахстана» определите, сколько рек с общей длиной от 1000 до 2000.1) Тобыл2) Иле3) Шу

Количество записей — 3

VI. Подведение итогов урока

Выставление и комментирование оценок.

VII. Домашнее задание:

внимательно прочитать стр. 116 — 119

Литература

1. Назаренко Е.Ф. Информационные технологии — Астана: Фолиант, 2007.

Источник: //www.azbyka.kz/urok-informatiki-na-temu-sortirovka-i-filtraciya-dannyh-microsoft-excel

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

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

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

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

Скачать последнюю версию Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтр

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

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

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

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

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

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

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

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

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

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

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

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

Урок: Как использовать автофильтр в Microsoft Excel

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

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

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

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

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

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

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

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

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

Урок: Как создать таблицу в Microsoft Excel

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

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

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

Помогла ли вам эта статья?

ДА НЕТ

Источник: //lumpics.ru/sorting-and-filtering-data-in-excel/

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

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

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

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

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

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

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

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

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

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

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

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

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

Слишком сложно? Тогда закажите нам выполнение курсовой работы по информационным технологиям!

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

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

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

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

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

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

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

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

Вариант 1(при помощи строки состояния):

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

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

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

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

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

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

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

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

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

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

Самая последняя ячейка этого столбца и будет являться максимальной ценой:

При помощи расширенного фильтра сделаем выборку продукции, количество которых больше 10, а их цвет равен белому.

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

• Нажимаем «ОК»: