Вы можете отсортировать список в алфавитном, числовом или хронологическом порядке не более чем по трем полям. Для каждого столбца можно задать возрастающий и убывающий порядок сортировки.
Возрастающий порядок сортировки:
Числа будут отсортированы в порядке от наименьшего отрицательного до наибольшего положительного числа.
Значения даты и времени будут отсортированы в порядке от наиболее раннего до наиболее позднего значения.
Текст будет отсортирован по алфавиту. При этом сначала будут расположены заданные в качестве текста числовые значения.
При сортировке в возрастающем порядке логических значений сначала будет отображено значение ЛОЖЬ, а затем – значение ИСТИНА.
Значения ошибки будут отсортированы в том порядке, в котором они были обнаружены (с точки зрения сортировки все они равны).
Пустые ячейки будут отображены в конце отсортированного списка.
Убывающий порядок сортировки:
В случае убывающего порядка сортировки Excel сортирует записи в обратном описанному выше порядке. Только пустые ячейки по-прежнему будут отображены в конце списка.
Пользовательский порядок сортировки:
Вместо возрастающего и убывающего порядка сортировки Вы можете применять также пользовательский порядок сортировки, определяемый заданным пользовательским списком.
Сортировка списка
Для сортировки списка поместите указатель ячейки внутри списка и выполните команду Данные – Сортировка.
Excel автоматически выделит список и выведет на экран диалоговое окно “Сортировка диапазона” в котором нужно указать параметры сортировки.
Вы можете задать сортировку не более чем по трем полям списка и указать для каждого поля свой порядок сортировки.
Excel автоматически распознает имена полей, если формат ячеек, содержащих имена, отличается от формата ячеек с данными.
Диалоговое окно “Сортировка диапазона”.
Если выполненное программой выделение диапазона не совсем корректно, установите переключатель внизу диалогового окна в нужное положение (Идентифицировать поля по “подписям (первая строка диапазона)” или же “обозначениям столбцов листа”).
Заданные в диалоговом окне “Сортировка” диапазона и “Параметры сортировки” параметры будут сохранены и отображены в диалоговом окне при следующем его открытии.
Вы можете также задать сортировку столбцов диапазона вместо строк. Для этого нажмите в диалоговом окне “Сортировка диапазона” кнопку “Параметры” и в диалоговом окне “Параметры сортировки” в группе “Сортировать” установите переключатель в положение “Столбцы диапазона”.
26. Фильтрация данных в Excel.
Фильтрация данных в списке позволяет отобразить только те записи, которые соответствуют заданным условиям.
Автофильтр
При применении автофильтра имена полей будут дополнены раскрывающимися списками, из которых можно выбрать нужное значение поля или задать пользовательский критерий.
Вставка автофильтра
1. Поместите указатель ячейки внутри списка.
2. В подменю Данные – Фильтр выберите команду “Автофильтр”. Рядом с именами полей будут отображены кнопки со стрелками, нажав которые, можно открыть список.
3. Откройте список для поля, значение которого хотите использовать в качестве фильтра (критерия отбора). В списке будут приведены значения ячеек выбранного поля.
4. Выберите из списка нужный элемент. На экране будут отображены только те записи, которые соответствуют заданному фильтру.
5. Выберите при необходимости из списка другого поля нужный элемент. На экране будут отображены только те записи, которые соответствуют всем заданным условиям фильтрации (условия отдельных полей объединяются с помощью логической операции “И”).
Кнопки открытия автофильтров полей, для которых заданы условия фильтрации, окрашены синим цветом.
Если перед выполнением команды “Автофильтр” Вы выделили один или несколько столбцов, то раскрывающиеся списки будут добавлены только соответствующим полям.
Чтобы снова отобразить на экране все записи списка, выполните команду “Отобразить все” из подменю Данные – Фильтр.
Критерий фильтрации для отдельного поля можно убрать, выбрав в списке автофильтра этого поля элемент “Все”.
Чтобы деактивировать функцию автофильтра (удалить раскрывающиеся списки), выберите повторно команду Данные – Фильтр – Автофильтр.
Применение пользовательского автофильтра
Применяя пользовательский автофильтр, Вы можете соединить с помощью логических операторов отдельные условия отбора записей.
Вставьте в список автофильтр, выбрав команду Данные – Фильтр – Автофильтр.
Откройте список автофильтра для нужного поля и выберите в нем элемент (Условие).
В открывшемся диалоговом окне “Пользовательский автофильтр” (Рис. 6.3.27.) укажите первый критерий.
Выберите логический оператор, объединяющий первый и второй критерии.
Диалоговое окно “Пользовательский автофильтр”.
Вы можете задать для отдельного поля в пользовательском автофильтре один или два критерия. В последнем случае их можно объединить логическим оператором “И” либо “ИЛИ”.
Задайте второй критерий.
Нажмите кнопку “OK”. Excel отфильтрует записи в соответствии с указанными критериями.
Расширенный фильтр
Для задания сложных условий фильтрации данных списка Excel предоставляет в помощь пользователю так называемый расширенный фильтр.
Диапазон критериев
Критерии можно задать в любом свободном месте рабочего листа. В диапазоне критериев Вы можете вводить и сочетать два типа критериев:
Простые критерии: программа сравнит содержимое полей с заданным критерием (аналогично применению автофильтра).
Вычисленные критерии: в этом случае можно указывать также возвращаемые формулой значения, которые не представлены в списке.
При указании простых критериев сначала в диапазоне критериев следует указать имена полей, причем можно ввести только те имена полей, для которых задаются критерии.
В строке (строках), расположенной непосредственно под строкой с именами полей, следует задать критерии. При задании критериев Excel не различает регистр. Вы можете использовать при задании критериев символы подстановки? и *.
Все критерии, заданные в одной строке, должны выполняться одновременно (соответствует логическому оператору “И”). Чтобы задать соединение критериев оператором “ИЛИ”, укажите критерии в различных строках.
Применение расширенного фильтра
После создания диапазона критериев можно запустить расширенный фильтр и подвергнуть данные списка фильтрации.
1. Поместите указатель ячейки внутри списка. В этом случае Excel автоматически распознает диапазон списка и представит ссылку на диапазон в диалоговом окне.
2. Выполните команду Данные – Фильтр – Расширенный фильтр. Поместите курсор ввода в поле “Диапазон условий” и выделите соответствующий диапазон в рабочем листе.
3. Закройте диалоговое окно нажатием кнопки “ОК”. На экране теперь будут отображены записи, удовлетворяющие заданным критериям.
Вы можете применить в рабочем листе только один расширенный фильтр.
Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне “Расширенный фильтр” установите флажок параметра “Только уникальные записи”.
Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, выполните команду Данные – Фильтр – Отобразить все.
Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.
Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.
Порядок сортировки в Excel
Существует два способа открыть меню сортировки:
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Сортировка по цвету ячейки и по шрифту
Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.
Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».
В открывшемся окне вводим необходимые параметры:
Здесь можно выбрать порядок представления разных по цвету ячеек.
По такому же принципу сортируются данные по шрифту.
Сортировка в Excel по нескольким столбцам
Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.
Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.
Сортировка строк в Excel
По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:
Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.
Случайная сортировка в Excel
Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.
Например, нужно расположить в случайном порядке набор неких чисел.
Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.
Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.
Динамическая сортировка таблицы в MS Excel
Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.
Для динамической сортировки текстовых значений понадобятся формулы массива.
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.
В большинстве случаев работы с данными они организованны вертикально, поэтому когда подразумевается обработка имеется ввиду именно вертикальная обработка данных, включая сортировку. Т.е. если нужно отсортировать числа по возрастанию, подразумевается, что вверху списка будут числа с минимальными значениями, а внизу списка с максимальными. Именно с такой “вертикальной” организацией данных и связана чрезвычайная популярность (относительно той же ).
В этом материале мы не рассматриваем стандартную “вертикальную” сортировку, ей уделено достаточно внимания как на просторах интернета, так и в нашем . В курсе мы рассмотрели сортировку по одному, нескольким критериям, а также, сортировку с учетом атрибутов ячеек.
Но, все-таки, как отсортировать ячейки Excel по горизонтали ? Для горизонтальной сортировки в Excel припасено несколько вариантов, которые дополняют друг друга:
- С помощью стандартного механизма настраиваемой сортировки: Вкладка “Главная”/ группа “Редактирование”/ команда “Сортировка и фильтр”/ “Настраиваемая сортировка”. Далее, в параметрах выбираем сортировку по столбцам диапазона.
Плюсы такого варианта сортировки в том что можно сортировать Любой тип информации (как числа, так и текст). Минус такого варианта заключается в том, что если нужно отсортировать большое количество данных, придется каждую отдельную строку настраивать отдельно.
- С помощью функций или . Данный способ решает проблему большого количества данных по вертикали, умело оперируя , можно запросто сделать горизонтальную сортировку для большого количества данных. Но у этого способа есть один существенный недостаток, который, кстати, отсутствует в первом варианте, – это возможность сортировки только числовой информации. Да, к сожалению, для текста не подойдет.
- С помощью механизма . Мы уже неоднократно использовали данный инструмент при решении конкурсных заданий ExcelWorldChamp и . На первый взгляд решение может показаться громоздким, однако, по мере освоения Power Query, можно привыкнуть. А, если вы планируете заниматься обработкой данных в Excel, то без использования данного инструмента сейчас просто нельзя.
Используем горизонтальную сортировку с помощью Power Query
Для совершения каких либо манипуляций в Power Query данные необходимо предварительно загрузить. На вкладке данные, в зависимости от типа источника данных пользуемся соответствующей командой. В нашем, простом случае, – это “Из таблицы/диапазона”.
В самом окне редактора Power Query переходим на вкладку “Добавление столбца”, команда “Настраиваемый столбец”. В диалоговом окне настройки столбца вводим функцию List.Sort(), в качестве аргументов передаем столбцы. По умолчанию функция сортируем по возрастанию, поэтому этот опциональный параметр нужно обязательно указывать, если сортировка нужна в убывающем порядке.
List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]}, Order.Ascending)
что равноценно
List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]})
а вот формула для убывающего порядка
List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]}, Order.Descending)
Поскольку результатом получается элемент типа список, нужно определиться с дальнейшими действиями сортировки, если нужно получить столбец со значениями, выбираем опцию “Развернуть в новые строки”, однако, если идет сортировка для большого количества данных, лучше воспользоваться опцией “Извлечь значения…”. При извлечении значений все они извлекаются в одну ячейку с определенным разделителем (ну или без). Если потом возникнет необходимость разнести значения по ячейкам сделать это можно несколькими способами, мы их рассмотрели в данном , однако, лучшим вариантом будет воспользоваться командой “Разделить столбец” вкладки “Главная” в самом Power Query.
Сортировка является очень важной функцией в Excel. Бывалые юзеры используют ее по полной, что касается новичков, то, возможно, не все догадываются о том, сколько теряют возможностей. Если говорить вкратце, то данная функция позволяет отсортировать данные в таблице по какому-то определенному критерию.
Дело в том, что в больших таблицах используется очень много данных, в которых можно и запутаться. Чтобы этого не произошло, логично расположить всю информацию в нужном вам порядке, так сказать, разложить по полочкам. Также при помощи сортировки очень удобно . Предлагаю обсудить вопрос того, как отсортировать данные в таблице excel, более детально.
Виды сортировки
Начну я, пожалуй, с того, что существует различные критерии, согласно которым можно отсортировать данные. В частности, выделяют следующие:
- Сортировка от А до Я. С помощью этого вида можно отсортировать данные по нарастанию.
- Сортировка от Я до А означает, соответственно, сортировку данных по убыванию.
- Сортировка по месяцам и дням недели.
- Сортировка по форматированию. Эта разновидность доступна в версии Excel 2007 и выше. Такая вариация сортировки актуальна в том случае если диапазон ячеек отформатирован с применением цвета шрифта, заливки ячеек или же набора значков. Так как цвет шрифта и заливки имеет свой собственный код, то именно его программа и использует при сортировке форматов.
Как отсортировать данные
Что касается того, как отсортировать информацию в таблице Excel, то дело обстоит следующим образом. Вам необходимо выделить таблицу и во вкладке «Данные» нажать кнопку «Сортировка». Далее откроется новое окно, в котором нужно задать столбец сортировки, а также выбрать порядок сортировки. Если вам не нужно сортировать данные по нарастанию или убыванию, жмите «Настраиваемый список».
Теперь открылось новое диалоговое окошко, в котором вы можете выбрать список дней недели или месяцев (а именно по этим параметрам пользователь чаще всего располагает данные).
Если же нужного списка в перечне не оказалось, то вы можете создать свой. Для этого кликните строчку «Новый список» и введите вручную элементы предполагаемого списка, затем жмите «Добавить». Обратите внимание, что созданный вами список будет сохранен, и в последующие разы вы сможете повторно им воспользоваться.
Как видите, данная функция позволяет сортировать не только по простеньким критериям, как, например, по алфавиту, по возрастанию цифр и т.д., но также и задавать свои критерии для сортировки. В частности, можно использовать воинские звания, должности, какие-то определенные товары, ценность и важность которых определяете исключительно вы и т.п.
Видео в помощь
Предположим, что мы подготовили отчет по продажам и финансовым результатам по каналам продаж и менеджерам следующего вида:
Глаза разбегаются, правда? Для того, чтобы было легче читать эту таблицу, определимся с тем, какая информация для нас существенна, а какая нет.
Сразу отметим 2 важных момента.
1. Как правило, таблицы такого рода создаются с помощью формул. До проведения сортировки нужно обязательно убрать все формулы из всех ячеек , иначе провести операцию просто не удастся, а если и удастся, то все значения изменятся, т.к. формулы будут ссылаться на совершенно другие ячейки, чем должны.
2. Если в дальнейшем нам понадобится убрать сортировку и вернуться к прежнему виду таблицы, следует позаботиться об этом на данном этапе. Пронумеруем строки первоначальной таблицы на случай, если захотим вернуться к ней, и тогда у нас будет возможность отсортировать строки по нумерации.
Как убрать формулы из всех ячеек листа
Для того, чтобы одновременно убрать все формулы нужно:
1. Выделить всю область листа, для чего необходимо нажать левой клавишей мыши на самый верхний левый угол серого поля:
2. Скопировать выделенную область (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Копировать»)
3. Воспользоваться специальной вставкой (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Специальная вставка» -> «значения»)
Ну, а теперь можно удалить или скрыть лишние столбцы, не боясь изменения отчета.
Прежде чем приступить непосредственно к сортировке, нужно проверить, чтобы сортируемая таблица не содержала объединенных ячеек в шапке или другом месте . В таком случае Excel будет ругаться и писать: «Для этого требуется, чтобы ячейки имели одинаковый размер».
Если Вы не намерены снимать объединение ячеек шапки и менять таблицу, можно перенести сортируемый столбик в левый крайний ряд и отсортировать его с помощью кнопочек сортировки.
Например, мы хотим отсортировать таблицу по убыванию выручки. Выделяем сортируемые строки по серому полю без шапки и нажимаем на «Сортировку по убыванию»:
ВНИМАНИЕ: Это касается только объединенных ячеек в шапке . Если внутри таблицы есть объединенные ячейки, Excel не будет сортировать такие строки. Придется снимать объединение.
Если же таблица не содержит объединенных ячеек, можно провести сортировку непосредственно внутри таблицы без переноса сортируемого столбца.
Для этого выделяем всю область таблицы вместе с шапкой, находим в главном меню «Данные» и выбираем «Сортировку»:
В выпавшем окне Excel предложит сделать сортировку по всем показателям, перечисленным в шапке. Мы выберем «Чистую прибыль»:
Если же мы совместим данный вид сортировки с сортировкой через автофильтр, мы сможем отсортировать данные по отделам, менеджерам и клиентам. Добавим столбец для сортировки и отсортируем данные по менеджерам:
Как видим, в результате у нас получился удобный отчет, который очень наглядно демонстрирует, кто из менеджеров приносит больше всего прибыли, а кто работает с нерентабельными клиентами.