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

Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

Международный конкурс по экологии «Экология России»

Доступно для всех учеников 1-11 классов и дошкольников

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]Это связано с тем, что вспомогательная функция не в состоянии правильно обработать повторяющиеся слова, поэтому ее придется немного усовершенствовать, если вы хотите использовать в списке повторы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Чтобы работать с определенной областью ячеек и данных в программе Microsoft Excel, для начала нужно ее выделить мышью, чтобы область затемнилась. Теперь на панели инструментов необходимо выбрать «А Я сортировка и фильтры» и далее выбирать конкретные условия сортировки.

Как сортировать по алфавиту в Эксель — Ваша компьютерная помощь

В Гугл таблицах встроенной такой функции нет и для ее использования необходимо скачать еще одно браузерное расширение “Сортировка диапазона +”. Это дополнение позволит сортировать данные по цвету заливки ячейки и по цвету текста.

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

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

Но что, если вы хотите отсортировать данные в Excel по фамилии ?

Хотя это не так просто, это все же можно сделать (многое также зависит от того, как структурированы данные имен).

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

В этом руководстве по Excel я покажу вам, как отсортировать столбец с именами на основе фамилии.

Извлечь и отсортировать по фамилии с помощью функции Поиска и Замены

Первый шаг к сортировке по фамилии — занести фамилию в отдельный столбец.

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

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

Ниже приведены шаги для сортировки по фамилии:

Вышеупомянутые шаги сохранят фамилию и удалят все, что перед ней. Это хорошо работает, даже если у вас есть отчество или префиксы (например, Mr. или Ms).

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

Вышеупомянутые шаги сортируют весь выбранный набор данных по фамилии.

После этого вы можете удалить столбец с фамилией.

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

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

Хотя показанный выше метод (с использованием поиска и замены) — это то, что я предпочитаю получать все фамилии и сортировать на их основе, одним из его ограничений является то, что результирующие данные статичны.

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

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

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

Ниже приведена формула, которая извлечет фамилию из полного имени:

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

Функция FIND (НАЙТИ) используется для получения позиции символа пробела. Затем это значение вычитается из общей длины имени, чтобы получить общее количество символов в фамилии.

Это значение затем используется в функции RIGHT (ВПРАВО) для получения фамилии.

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

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

Но что, если у вас есть отчество? Или перед именем может стоять приветствие (например, г-н или г-жа).

В таком случае вам нужно использовать следующую формулу:

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

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

Использование текста в столбцы

Текст в столбцы — это снова простой и легкий способ разбить ячейки в Excel.

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

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

Ниже приведены шаги по использованию текста в столбец для сортировки по фамилии:

Получив результат, вы можете отсортировать его по фамилии.

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

Использование Flash Fill

Еще один быстрый способ получить фамилии — использовать функцию Flash Fill.

Flash Fill был представлен в Excel 2013 и помогает манипулировать данными путем выявления шаблонов. Чтобы это сработало, вам нужно пару раз показать ожидаемый результат Flash Fill.

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

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

Ниже приведены шаги по использованию Flash Fill для получения фамилии и последующей сортировки по ней:

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

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

В таких случаях следует ввести ожидаемый результат еще в одну или две ячейки, а затем выполнить шаги 4-7.

Когда у вас есть все фамилии в столбце, вы можете отсортировать данные на основе этих фамилий.

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

[expert_bq id=»1570″]Если первая строка не содержит заголовков, Excel использует букву столбца, такую как столбец D или столбец E, как выбор в колонка вариант Сортировать диалоговое окно. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если результаты сортировки по дате не выглядят так, как ожидалось, данные в столбце, содержащем ключ сортировки, могут содержать даты или время, хранящиеся в виде текстовых данных, а не как числа (даты и время — это только данные с форматированными числами).

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

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

Как отсортировать данные в Excel с помощью VBA (пошаговое руководство)

В Excel уже есть несколько способов быстрой сортировки данных.

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

Тогда зачем вам знать, как это сделать с помощью VBA?

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

Вы можете создать макрос, который сделает все это за вас одним щелчком мыши. Это сэкономит вам много времени и усилий каждый раз, когда вы будете это делать.

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

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

Понимание метода Range.Sort в Excel VBA

При сортировке с использованием VBA вам необходимо использовать в своем коде метод Range.Sort.

«Диапазон» — это данные, которые вы пытаетесь отсортировать. Например, если вы сортируете данные в формате A1: A10, тогда «Диапазон» будет иметь значение «Диапазон» («A1: A10»).

Вы также можете создать именованный диапазон и использовать его вместо ссылок на ячейки. Например, если я создаю именованный диапазон «DataRange» для ячеек A1: A10, я также могу использовать Range («DataRange»).

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

• Ключ — здесь нужно указать столбец, который нужно отсортировать. Например, если вы хотите отсортировать столбец A, вам нужно использовать ключ: = Range («A1»)
• Заказ — здесь вы указываете, хотите ли вы сортировку по возрастанию или по убыванию. Например, если вам нужна сортировка по возрастанию, вы должны использовать Order: = xlAscending
• Заголовок — здесь вы указываете, есть ли у вашего набора данных заголовки или нет. Если у него есть заголовки, сортировка начинается со второй строки набора данных, иначе она начинается с первой строки. Чтобы указать, что у ваших данных есть заголовки, вы будете использовать Header: = xlYes

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

Теперь давайте посмотрим, как использовать метод Range.Sort в VBA для сортировки данных в Excel.

Сортировка одного столбца без заголовка

Предположим, у вас есть один столбец без заголовка (как показано ниже).

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

Обратите внимание, что я указал диапазон данных вручную как Range («A1: A12»).

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

Обратите внимание, что вместо Range («A1: A12») я использовал Range («A1», Range («A1»). End (xlDown)).

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

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

Теперь позвольте мне быстро объяснить параметры, используемые в приведенных выше примерах:

• Key1: = Range («A1») — указывает A1, чтобы код знал, какой столбец сортировать.
• Order1: = xlAscending — Указан порядок как xlAscending. Если вы хотите, чтобы он располагался в порядке убывания, используйте xlDescending.
• Заголовок: = xlNo — указывает, что заголовков нет. Это также значение по умолчанию. Таким образом, даже если вы опустите это, ваши данные будут отсортированы, учитывая, что у них нет заголовков.

Не знаете, где разместить этот код VBA и как запустить макрос? Прочтите это руководство!

Сортировка одного столбца с заголовком

В предыдущем примере у набора данных не было заголовка.

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

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

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

Обратите внимание, что я создал именованный диапазон — DataRange и использовал этот именованный диапазон в коде.

Сортировка нескольких столбцов с заголовками

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

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

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

Вот код, который сортирует несколько столбцов за один раз.

В приведенном выше примере данные сначала сортируются по коду состояния (столбец A). Затем в данных кода состояния он снова сортируется по магазину (столбец B). Этот порядок определяется кодом, в котором вы его упоминаете.

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

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

Ниже приведен код, который позволит вам это сделать:

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

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

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

Обратите внимание, что двойной щелчок — это триггер, позволяющий Excel запускать указанный код. Эти триггеры, такие как двойной щелчок, открытие книги, добавление нового рабочего листа, изменение ячейки и т. Д., Называются событиями и могут использоваться для запуска макросов в Excel. Вы можете узнать больше о событиях Excel VBA здесь.

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

А что, если вы хотите отсортировать первые два столбца («Штат» и «Магазин») в порядке возрастания, а столбец «Продажи» — в порядке убывания.

В приведенном выше коде он проверяет, является ли ячейка, по которой дважды щелкнули, заголовком Продажи или нет. Если да, то он присваивает значение xlDescending переменной SortOrder, иначе делает его xlAscending.

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

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

• Изменил имя нового листа на «BackEnd».
• В ячейке B2 введите символ стрелки (для этого перейдите во вкладку «Вставить» и выберите параметр «Символ»).
• Скопируйте и вставьте заголовки из набора данных в ячейку A3: C3 на листе «Backend».
• Используйте следующую функцию в ячейке A4: AC4:
• Остальные ячейки будут автоматически заполнены кодом VBA, если вы дважды щелкните заголовки, чтобы отсортировать столбец.

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

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

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

[expert_bq id=»1570″]После того, как у вас есть разделенные элементы в отдельных столбцах, вы можете использовать столбец с фамилией, чтобы расположить данные в алфавитном порядке. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Вы также можете создать именованный диапазон и использовать его вместо ссылок на ячейки. Например, если я создаю именованный диапазон «DataRange» для ячеек A1: A10, я также могу использовать Range («DataRange»).

Как отсортировать данные в Excel с помощью VBA (пошаговое руководство)

• Ключ — здесь нужно указать столбец, который нужно отсортировать. Например, если вы хотите отсортировать столбец A, вам нужно использовать ключ: = Range («A1»)
• Заказ — здесь вы указываете, хотите ли вы сортировку по возрастанию или по убыванию. Например, если вам нужна сортировка по возрастанию, вы должны использовать Order: = xlAscending
• Заголовок — здесь вы указываете, есть ли у вашего набора данных заголовки или нет. Если у него есть заголовки, сортировка начинается со второй строки набора данных, иначе она начинается с первой строки. Чтобы указать, что у ваших данных есть заголовки, вы будете использовать Header: = xlYes

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