Сортировка данных в Excel 2007

Сортировка данных в Excel 2007/2010/2013/2016. Как отсортировать данные в Excel по строкам и по столбцам?

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

Типы сортируемых данных и порядок сортировки

Сортировка числовых значений в Excel

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

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

Сортировка текстовых значений в Excel

«Сортировка от А до Я» — сортировка данных по возрастанию;

«Сортировка от Я до А» — сортировка данных по убыванию.

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

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

Можно отсортировать текстовые данные с учетом регистра. Для этого необходимо в параметрах сортировки установить флажок в поле «Учитывать регистр».

Обычно буквы верхнего регистра имеют меньшие номера, чем буквы нижнего регистра.

Сортировка значений даты и времени

«Сортировка от старых к новым» — это сортировка значений даты и времени от самого раннего значения к самому позднему.

«Сортировка от новых к старым» — это сортировка значений даты и времени от самого позднего значения к самому раннему.

Сортировка форматов

В Microsoft Excel 2007 и выше предусмотрена сортировка по форматированию. Этот способ сортировки используется в том случае, если диапазон ячеек отформатирован с приминением цвета заливки ячеек, цвета шрифта или набора значков. Цвета заливок и шрифтов в Excel имеют свои коды, именно эти коды и используются при сортировке форматов.

Сортировка по настраиваемому списку

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

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

Сортировка по столбцу

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

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

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

Многоуровневая сортировка

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

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

Кроме всего прочего при сортировке можно учитывать, либо не учитывать регистр.

Надстройка для сортировки данных в Excel

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

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

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. выбирать диапазон данных для сортировки;

3. сортировать числовые и текстовые значения, значения даты и времени;

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

5. устанавливать порядок сортировки по возрастанию и убыванию.

Источник:
http://macros-vba.ru/nadstrojki/excel/175-sortirovka-dannyh-v-excel-kak-otsortirovat-dannye-v-excel

Сортированный список в EXCEL (ЧИСЛОвые значения)

22 января 2013 г.

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

Сортированный список может быть полезен при создании Выпадающего (раскрывающегося) списка . Некоторые функции для нормальной работы также требуют сортированный список: ПРОСМОТР() , ВПР() , ПОИСКПОЗ() .

Сортировка числового списка через меню

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

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

Сортировка формулами (динамическая сортировка)

Если список содержит только числовые значения, то его сортировку можно сделать с помощью функций НАИМЕНЬШИЙ() и СТРОКА() (см. файл примера ).

Для сортировки от минимального к максимальному используем формулу (введите ее в ячейку B 4 и скопируйте вниз, например, Маркером заполнения ): =ЕСЛИОШИБКА(НАИМЕНЬШИЙ($A$4:$A$21;СТРОКА()-СТРОКА($B$3));»»).

Функция НАИМЕНЬШИЙ() сортирует исходный список и выводит, в соответствии со значением второго аргумента, элементы списка. Формула СТРОКА()-СТРОКА($B$3) задает порядковые числа (1, 2, 3, 4. ) для второго аргумента функции НАИМЕНЬШИЙ() .

Если исходный диапазон охватывает пустые ячейки, то необходима функция подавления ошибки ЕСЛИОШИБКА() . После заполнения незаполненных ячеек, входящих в диапазон А4:А21 , список в столбце В будет отсортирован автоматически.

Примечание . Функция ЕСЛИОШИБКА() появилась начиная с версии MS EXCEL 2007. О том как ее заменить для более ранних версий см. Функция ЕСЛИОШИБКА() в MS EXCEL .

Абсолютную ссылку на диапазон исходного списка $A$4:$A$21 можно заменить ссылкой на Динамический диапазон , границы которого будут изменяться от количества введенных значений (см. файл примера ).

СОВЕТ: Решение задачи о сортировке списка с текстовыми значениями приведено в одноименной статье Сортировка списка с текстовыми значениями .

Для сортировки от максимального к минимальному используйте функцию НАИБОЛЬШИЙ() .

Также для сортировки числовых списков можно использовать функции ПРОЦЕНТИЛЬ.ВКЛ() и ПЕРСЕНТИЛЬ() (см. файл примера ).

Источник:
http://excel2.ru/articles/sortirovannyy-spisok-v-ms-excel-chislovye-znacheniya

Как отсортировать данные в таблицах Excel

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

Для поиска нужной информации применяют специальные фильтры

Сортировка по указанным числовым значениям

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

Читайте также  Факторный анализ в excel как сделать

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

Фильтр по возрастанию и убыванию

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

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

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

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

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

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

Сортировка по иным параметрам

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

Установка фильтра по дате и формату

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

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

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

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

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

Если что-то пошло не так, нужно знать, как отменить сортировку в Excel, воспользовавшись одним из двух несложных действий. Можно просто нажимать несколько раз две одновременно зажатые клавиши (Ctrl + Z) до тех пор, пока таблица не примет первоначальный вид. Если же действий было совершено много, проще отменить сортировку вторым способом, закрыв таблицу без сохранения, после чего вновь её открыть и приступить заново к работе.

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

Источник:
http://nastroyvse.ru/programs/review/sortirovka-dannyx-excel.html

Сортировка данных в Excel 2007/2010/2013/2016. Как отсортировать данные в Excel по строкам и по столбцам?

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

Типы сортируемых данных и порядок сортировки

Сортировка числовых значений в Excel

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

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

Сортировка текстовых значений в Excel

«Сортировка от А до Я» — сортировка данных по возрастанию;

«Сортировка от Я до А» — сортировка данных по убыванию.

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

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

Можно отсортировать текстовые данные с учетом регистра. Для этого необходимо в параметрах сортировки установить флажок в поле «Учитывать регистр».

Обычно буквы верхнего регистра имеют меньшие номера, чем буквы нижнего регистра.

Сортировка значений даты и времени

«Сортировка от старых к новым» — это сортировка значений даты и времени от самого раннего значения к самому позднему.

«Сортировка от новых к старым» — это сортировка значений даты и времени от самого позднего значения к самому раннему.

Сортировка форматов

В Microsoft Excel 2007 и выше предусмотрена сортировка по форматированию. Этот способ сортировки используется в том случае, если диапазон ячеек отформатирован с приминением цвета заливки ячеек, цвета шрифта или набора значков. Цвета заливок и шрифтов в Excel имеют свои коды, именно эти коды и используются при сортировке форматов.

Сортировка по настраиваемому списку

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

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

Сортировка по столбцу

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

Читайте также  Как сделать плюс в excel?

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

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

Многоуровневая сортировка

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

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

Кроме всего прочего при сортировке можно учитывать, либо не учитывать регистр.

Надстройка для сортировки данных в Excel

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

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

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. выбирать диапазон данных для сортировки;

3. сортировать числовые и текстовые значения, значения даты и времени;

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

5. устанавливать порядок сортировки по возрастанию и убыванию.

Источник:
http://macros-vba.ru/nadstrojki/excel/175-sortirovka-dannyh-v-excel-kak-otsortirovat-dannye-v-excel

Как ранжировать числовые данные в Excel?

Научимся ранжировать числовые данные в Excel с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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

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

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

Начнем с самого простого и доступного варианта — сортировки.

Мы уже частично разбирали как можно структурировать данные с помощью фильтра и сортировки.
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная -> Редактирование -> Сортировка и фильтр, а далее указать по какому критерию нужно произвести сортировку.

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


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

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

Ранжирование данных

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

Функция РАНГ в Excel

Синтаксис и описание функции:

РАНГ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке.

  • Число(обязательный аргумент) — число для которого вычисляется ранг;
  • Ссылка(обязательный аргумент) — массив или ссылка на массив чисел;
  • Порядок(необязательный аргумент) — способ упорядочения. Если аргумент равен 0 или не указан, то значение 1 присваивается максимальному элементу в списке (условно говоря, сортируем по убыванию), в ином случае значение 1 присваивается минимальному элементу (сортируем по возрастанию).

Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР, а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.

Функции РАНГ.РВ и РАНГ.СР в Excel

Синтаксис и описание функций:

РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.

РАНГ.СР(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается средний ранг.

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


Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.

В случае с РАНГ.РВ равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки и Мультиварки соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.

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

Автоматическая сортировка

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

Например, это можно сделать с помощью функции ВПР, или комбинации ИНДЕКС и ПОИСКПОЗ, однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:


В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции СТРОКА или СТОЛБЕЦ, поделенные на заведомо большую величину.

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


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

Спасибо за внимание!
Если у вас остались вопросы — пишите в комментариях.

Источник:
http://tutorexcel.ru/funktsii/kak-ranzhirovat-chislovye-dannye-v-excel/

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

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

Как отсортировать отфильтровать данные в таблице Excel

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

Рассмотрим разные варианты сортировки данных.

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

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

Рассмотрим в качестве примера сортировку сотрудников по фамилиям в алфавитном порядке:

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

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

  • Откройте в верхней части табличного редактора вкладку «Главная», если таковая не была открыта по умолчанию.
  • Здесь нужно обратить внимание на блок инструментов «Редактирование». Нажмите на кнопку «Сортировка и фильтр».
  • В нашем случае из выпавшего списка выбирайте «Сортировка от А до Я».
  • Если требуется сортировка в обратном порядке, то выбирайте из выпавшего меню пункт «Сортировка от Я до А».
  • Стоит отметить некоторые особенности работы этого инструмента сортировки:

    • Пункты настроек «От А до Я» и «От Я до А» указываются в том случае, если в ячейках в первую очередь идёт текстовая информация;
    • Если в ячейках в первую очередь расположены числовые значения, то пункты меняются на «От минимальному к максимальному» и «От максимального к минимальному»;
    • Если в ячейках присутствуют даты, то пункты будут изменены на «Сортировка от старых к новым» и «Сортировка от новых к старым».
    Читайте также  Функция СЧЕТ в Excel

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

    Вариант 2: Настраиваемая сортировка

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

    Рассмотрим, как можно реализовать задуманное:

    1. Снова выделите нужные диапазоны данных и нажмите по инструменту «Сортировка и фильтрация».
    2. В контекстном меню перейдите в «Настраиваемая сортировка».
    3. Будет открыто окошко настройки сортировок. Там обязательно нужно установить галочку у «Мои данные содержат заголовки». Не нужно её ставить только в том случае, если столбцы с информацией не имеют заголовков, типа «Дата», «Пол», «Имя» и т.д.
    4. Обратите внимание на поле «Столбец». Туда надо написать наименование столбца, по которому и происходит сортировка. Так как мы сортируем данные в первую очередь по именам, то прописываем там «Имя».
    5. Поле «Сортировка» отвечает за тип контента, по которому и будет сортироваться информация. Здесь есть четыре варианта:
      • Значения;
      • Цвет ячейки;
      • Цвет шрифта;
      • Значок ячейки.
    6. В нашем случае будем использовать первый вариант, который и так стоит там по умолчанию. В большинстве случаев он походит, поэтому менять его необязательно.
    7. В поле «Порядок» нужно указать значение «От А до Я».

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

    1. В верхней левой части окна нажмите на кнопку «Добавить уровень».
    2. Появится ещё один набор полей, который во многом похож на предыдущий. Однако в данном случае сортировка происходит по столбцу «Дата». Его наименование и нужно прописать в соответствующее поле.
    3. В поле «Сортировка» также оставляете «Значения».
    4. В поле «Порядок» на сей раз нужно прописать «От старых к новым», так как у нас присутствуют в выбранном столбце даты.
    5. На этом процесс сортировки можно считать завершённым. Жмите «Ок», чтобы подтвердить настройки.

    После проделывания подобных манипуляций всё будет сортировано в нужном нам формате – в алфавитном порядке, а при совпадении имён вверх будут попадать те, что были добавлены в таблицу раньше (если верить столбцу «Дата»).

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

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

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

    Вариант 3: Настройка фильтрации

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

    Использование данной функции происходит по следующей инструкции:

    1. Использование этой функции возможно, если выбрать любую ячейку в таблице. Лучше, конечно, выбирать заголовок столбца.
    2. После этого нажмите по кнопке «Сортировка и фильтр». Из контекстного меню нужно выбрать «Фильтр». Также его можно вызвать, воспользовавшись сочетанием клавиш Ctrl+Shift+L.
    3. Напротив всех столбцов с заголовка появились иконки в виде квадрата со стрелками. Кликните по кнопке того столбца, который вам нужно отсортировать.
    4. Откроется всплывающее окошко с настройками. Для примера оставим данные только какого-то одного работника, а остальных скроем. Чтобы это реализовать, снимите галочки с ненужных элементов.
    5. Закончив с выделением нужных сотрудников, жмите на «Ок».

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

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

    Если требуется отменить фильтры или внести в них какие-либо изменения, то просто проделайте следующее:

    1. Напротив названия нужного столбца нажмите по иконке фильтра. Наличие таковой означает, что к данному столбцу уже применён фильтр.
    2. Здесь можно внести изменения в настройки, например, выбрать другую дату и/или другого сотрудника. После нажатия на «Ок» перед вами будут отображены данные указанного сотрудника.
    3. Если вам требуется совсем удалить фильтр, жмите на кнопку «Удалить фильтр с (название столбца)».
    4. При условии, что в таблице много фильтров, сбрасывать каждый вручную будут слишком долго по времени. В качестве альтернативы просто нажмите по кнопке «Сортировка и фильтр», а там выберите пункт «Очистить».

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

    Вариант 4: Настройка «Умной таблицы»

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

    Чтобы создать «умную таблицу», нужно проделать следующие действия:

    1. Выделите всю область с данными, которую собираетесь преобразовать в таблицу такого типа.
    2. Во вкладке «Главная» воспользуйтесь пунктом «Форматировать как таблицу». Этот инструмент расположен в блоке «Стили».
    3. Из предложенных вариантов цветового оформления таблицы можете выбрать любой. Это никак не повлияет на функционал, а только на внешний вид.
    4. После проделанных действий должно открыться окошко, в котором можно поменять координаты таблицы. Если до этого вы делали выделение, то ничего в этом окошке дополнительно настраивать не требуется. Однако, если вы при выделении допустили где-то ошибку, то её можно будет легко исправить в этом окне.
    5. После применения стиля вы можете видеть, что таблица внешне сильно преобразилась в соответствии с тем стилем, что был выбран ранее. Для дальнейшей работы откройте вкладку «Вставка».
    6. Там нужно будет воспользоваться кнопкой «Таблица».
    7. Снова откроется окошко для указания координат. Здесь ничего менять не нужно, просто жмите на «Ок».
    8. После проделанных процедур у столбцов таблицы должны появится иконки фильтров, как это было в способе, где описывалось создание фильтров.
    9. При нажатии на одну из таковых иконок появляется интерфейс «Сортировка и фильтр». Здесь функционал и интерфейс то же не будет отличаться от того, который рассматривался в способе с созданием фильтров для ячеек.

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

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

    Источник:
    http://public-pc.com/sortirovka-i-filtracziya-dannyh-v-microsoft-excel/