Oc-windows.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Вылазит страница опаньки в Chrome: причины и решение

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

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

Автофильтр

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В меню Правка следует выбрать команду Найти или нажать комбинацию клавиш [Ctrl] + [F] (рис. 11.27). В открывшемся окне Найти и заменить на вкладке Найти в поле Найти нужно ввести текст или число, которые требуется найти, или выбрать одну из последних строк поиска в поле со списком.

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

Для поиска на текущем листе или во всей книге можно выб- рать в поле Искать вариант на листе или в книге.

После нажатия кнопки Найти все будет выдан список всех со- впадений с искомой строкой, при выделении элементов в кото-

ром соответствующая ячейка станет активной.

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

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

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

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

При использовании комaна,ы Данные/Фильтр/Автофильтр спра- ва от подписей столбцов в фильтруемом списке появляются стрелки автофильтра.

Читать еще:  Не запускается Виндовс 7 после обновления

Программа Excel обозначает отфильтрованные элементы голу- бым цветом.

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

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

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

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

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

Для удаления одного или нескольких условий следует восполь- зоваться командой Удалить.

Сортировка данных. В программе Excel сортировка данных осуществляется по выделенному полю. Кнопкипозволяют

сортировать столбцы по строкам в порядке возрастания (А—>Я) или убывания (Я->А). Сортировку данных также можно произво- дить командой Сортировка из меню Данные. При сортировке дан- ных изменяется порядок записей в списке.

Как настроить фильтр в Excel 2003?

Переходим на вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно».

С помощью инструмента пользователь может:

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

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

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

  1. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.
  2. Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать значения фильтра. Примечание.

Как задать несколько условий для одного столбца списка с помощью расширенного фильтра?

Для использования расширенного фильтра выполните следующие действия.

  1. Скопируйте из списка заголовки фильтруемых столбцов в первую пустую строку диапазона условий отбора.
  2. Введите в строки под заголовками столбцов требуемые критерии отбора. …
  3. Укажите ячейку в списке и дайте команду Данные => Фильтр => Расширенный фильтр.

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

Отфильтруйте несколько столбцов одновременно с помощью Kutools for Excel

  1. Нажмите Предприятие > Суперфильтр, см. снимок экрана:
  2. В появившемся диалоговом окне Super Filter: (1.) …
  3. После завершения критериев нажмите Фильтр кнопку, и данные были отфильтрованы одновременно по нескольким критериям столбца, см. снимок экрана:

Как в Excel сделать фильтр с выпадающим списком?

Создание раскрывающегося списка в Excel

  1. Выберите ячейки, в которой должен отображаться список.
  2. На ленте на вкладке «Данные» щелкните «Проверка данных».
  3. На вкладке «Параметры» в поле «Тип данных» выберите пункт «Список».
  4. Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.

Сколько условий можно наложить на один столбец с помощью команды Автофильтр?

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

Какие имеются возможности Excel для анализа экономической информации?

  • Решение математических задач (вычисления с большими объемами данных, нахождение значений функций, решение уравнений).
  • Построение графиков, диаграмм, работа с матрицами.
  • Сортировка, фильтрация данных по определенному критерию.
  • Проведение статистического анализа, основных операций с базами данных.
Читать еще:  Lightworks скачать бесплатно русская версия

Что значит фильтр со значением первые 10?

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

Какие два способа фильтрации существуют в Microsoft Excel?

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

Фильтр и сортировка ячеек по цвету в Excel

Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?

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

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

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

  • Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
  • Применение пользовательских функций.

Стандартный фильтр и сортировка по цвету в Excel

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

Таблица

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Стандартный фильтр

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

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

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

Сортирование диапазона по синей заливке

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

Сортирование диапазона по красной заливке

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Параметры настраиваемой сортировки

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

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Ссылка на основную публикацию
Adblock
detector
×
×