Сортировка строк по значению в excel

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

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

Чтобы в Microsoft Exel упорядочить по алфавиту, необходимо выделить нужные вам ячейки и на панели инструментов нажать quot;Сортировка и фильтрquot;. И затем выбираете как вам нужно сортировать текст от А до Я или наоборот от Я до А.

Чтобы работать с определенной областью ячеек и данных в программе Microsoft Excel, для начала нужно ее выделить мышью, чтобы область затемнилась. Теперь на панели инструментов необходимо выбрать quot;А Я сортировка и фильтрыquot; и далее выбирать конкретные условия сортировки.

Списки в таблице Эксель Exel можно упорядочить по алфавиту от А до Я и наоборот: от Я до А. Для начала кликните по заголовку стобца — так он весь разом выделится. Затем нажимаем на кнопку quot;Сортировка и фильтрquot; вверху. Выбираем нужную сортировку. Аналогично поступаем, если столбцов несколько.

Если нужно просто упорядочить данные по алфавиту, то самый простой способ выбрать Фильтр quot;Сортировка от А до Яquot;, при этом данные сами упорядочатся.

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

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

Для того что бы упорядочить по алфавиту список в Excel, необходимо выделить нужный столбец и нажать кнопку quot;АЯ сортировка и фильтрquot;, которая находится на панели управления, вкладка Главная, как показано на картинке и выбрать необходимую сортировку — от А до Я или наоборот от Я до А

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

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

2) нажать кнопку quot;Сортировка и фильтрquot; (обычно находится справа, имеет вид воронки с буквам А Я),

3) в сплывшем меню выбираем quot;Сортировка от А до Яquot;.

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

Сортировка по имени в таблице Exel от Microsoft довольно простая процедура.

Выделяем желаемый столбец. Жмем на панели инструментов вверху справа кнопку quot;Сортировка и фильтрquot;. Кликаем по выпавшей строке меню quot;Сортировка от А до Яquot;. вот и вся задача.

Упорядочить по алфавиту в Excel очень просто, нужно только воспользоваться кнопкой quot;Сортировка по возрастаниюquot;. Она выглядит как изображение двух букв quot;Аquot; и quot;Яquot; со стрелочкой сбоку.

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

Иногда после нажатия этой кнопки выскакивает диалоговое окно, которое спрашивает, что делать с соседними столбцами: или оставлять там все, как есть, или переместить эти данные в соответствии изменения расположения по алфавиту . Вам только нужно выбрать пригодный для вас вариант. Это как правило опция: quot;Автоматически расширить выделенный диапазонquot;.

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

Как в экселе разместить по алфавиту. Как сортировать по алфавиту в excel — сортировка по алфавиту в excel
В данном материале мы расскажем о том, как сделать список по алфавиту в табличном процессоре Excel. Материал будет полезен для пользователей всех современных версий этой программы, среди которых Excel 2007, 2010, 2013 и 2016.

Абсолютно разные дроби

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

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

Самый удобный, а потому и наиболее распространенный метод поиска НОК состоит в разложении на простые множители. Каждое целое число можно разложить на простые множители единственным способом с точностью до порядка расположения сомножителей. К примеру, число 30 можно разложить на 2 × 3 × 5, а число 20 на 2 × 2 × 5. Наименьшее общее кратное для этих чисел представляет собой число, которое состоит из общих для этих чисел неделимых множителей. Для данной пары это 2 × 2 × 3 × 5 = 60.

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

Сортировать по строкам, чтобы изменить порядок столбцов в Excel

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

One reason for sorting by row is to match the column order between different tables of data. With the columns in the same left to right order, it is easier to compare records or to copy and move data between the tables.

Customize the Column Order

Very seldom, however, is getting the columns in the correct order a straightforward task due to the limitations of the ascending and descending sort order options for values. Usually, it is necessary to use a custom sort order, and Excel includes options for sorting by cell or font color or by conditional formatting icons.

The easiest way of telling Excel the order of columns is to add a row above or below the data table containing numbers that indicate the order of columns from left to right. Sorting by rows then becomes a simple matter of sorting the columns smallest to largest by the row containing the numbers.

Once the sort is done, the added row of numbers can easily be deleted.

Sort by Rows Example

In the data sample used for this series on Excel sort options, the Student ID column has always been first on the left, followed by Name and then Age.

In this instance, as shown in the image above, numbers have been added to the columns to prepare the worksheet to reorder the columns so that the Program column is first on the left followed by Month Start, ​Name, Age, and Student ID.

Here’s how to change the column order:

1. Insert a blank row above the row containing the field names.
2. In this new row, enter the following numbers left to right starting in column A: 5, 3, 4, 1, 2.
3. Highlight the range to be sorted. In this example, highlight A2 to E13.
4. Select Home.
5. Select Sort & Filter to open the drop-down list.
6. Select Custom Sort to open the Sort dialog box.
7. Select Options to open the Sort Options dialog box.
8. In the Orientation section, select Sort left to right to sort the order of columns left to right in the worksheet.
9. Select OK to close the Sort Options dialog box.
10. With the change in Orientation, the Column heading in the Sort dialog box changes to Row.
11. Select the Sort by down arrow and choose Row 2. This is the row containing the custom numbers.
12. Under the Sort On heading, choose Cell Values.
13. Under the Order heading, choose Smallest to Largest from the drop-down list to sort the numbers in row 2 in ascending order.
14. Select OK to close the dialog box and sort the columns left to right by the numbers in row 2.
15. The order of columns begins with Program followed by Month Start, Name, Age, and Student ID.

Use Excel’s Custom Sort Options to Reorder Columns

While custom sorts are available in the Sort dialog box in Excel, these options are not easy to use when it comes to reordering columns in a worksheet. Options for creating a custom sort order available in the Sort dialog box are to sort the data by cell color, font color, and icon.

Unless each column has already had unique formatting applied, such as different font or cell colors, that formatting needs to be added to individual cells in the same row for each column to be reordered.

For example, to use font color to reorder the columns:

1. Select each field name and change the font color for each. For example, change Program to red, Month Start to green, Name to blue, Age to orange, and Student ID to purple.
2. In the Sort dialog box, select Sort by and choose Row 2.
3. Under the Sort on heading, choose Font Color.
4. Under the Order heading, manually set the order of field names colors to match the desired column order.
5. After sorting, reset the font color for each field name.

Сортировка данных по нескольким столбцам

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

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

Уровни можно добавлять, удалять, копировать, менять местами посредством кнопок «Вверх»-«Вниз».

Это самые простые способы сортировки данных в таблицах. Можно для этих целей применять формулы. Для этих целей подходят встроенные инструменты «НАИМЕНЬШИЙ», «СТРОКА», СЧЕТЕСЛИ».

Способ 3: Формула сортировки

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

Шаг 1: Создание вспомогательной формулы

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

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

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

В новой клетке создадим формулу СЧЁТЕСЛИ, которая считает ячейки, удовлетворяющие условие. В качестве диапазона указывайте созданную только что группу, затем первую ячейку для сравнения. В итоге первоначальный вид формулы таков: =СЧЁТЕСЛИ(Фрукты;A1).

Сейчас результатом этой формулы будет «1», поскольку ее запись не совсем верна для будущих расчетов, поэтому добавьте выражение ” перед номером первой ячейки.

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

Переименуйте диапазон с числами в группу – это понадобится при составлении следующей формулы.

Шаг 2: Создание формулы сортировки

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

1. В новой ячейке начните вводить =ПОИСКПОЗ(СТРОКА(A1). Эта формула отвечает за поиск позиции строки, из-за чего и следует указать аргумент “A1”.

Далее для простоты добавления именных диапазонов перейдите в “Формулы”, разверните меню “Определенные имена” и выберите “Использовать в формуле”.

Добавьте диапазон со вспомогательной формулой и укажите для него тип сопоставления “Точное совпадение” в выпадающем списке, который появится после добавления “;”.

Завершите создание формулы, обернув ее в функцию ИНДЕКС, которая будет работать с массивом названий.

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

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

Для упрощения понимания отдельно предоставляем полную формулу:

=(ИНДЕКС(Фрукты;ПОИСКПОЗ(СТРОКА(A1);номер_слова;0))), вам же останется только отредактировать ее под свои цели и растянуть на необходимый диапазон ячеек.

Шаг 3: Модернизации формулы для повторных названий

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

1. Откройте вспомогательную формулу и уберите знак “, чтобы СЧЁТЕСЛИ работал только при условии равенства.

Добавьте вторую часть – +СЧЁТЕСЛИ($A$1:A1;A1), позволяющую нормально записывать одинаковые слова в последовательном порядке.

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.

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

Сортировака в Excel по алфавиту

В прошлой заметке было подробно разобрано что такое сортировка и какими средствами располагает Excel для сортировки данных.

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

Для сортировки по алфавиту необходимо сделать следующее — выделите любую ячейку (1, см. рис. ниже) того столбца, данные которого вы хотите отсортировать, затем на вкладке Главная щелкните по инструменту Сортировка и фильтр (2). Откроется меню, из которого нужно выбрать направление сортировки (3).

Если необходимо отсортировать данные от А до Я, то выбираем соответствующий инструмент — Сортировка от А до Я. Если же нужен обратный порядок сортировки, то — Сортировка от Я до А.

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

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

Представление рациональных чисел в виде дроби

Когда люди столкнулись с проблемой отделения части от целого, они придумали дроби. Если разделить круглый торт на 4 куска, то каждый кусочек лакомства будет представлять собой 1/4 от целого торта. С введением десятичной системы исчисления 1/4 превратилась в 0,25 и для современных людей такое обозначение четвертой части чего-либо гораздо понятнее. Однако 0,25 можно выразить бесконечным количеством дробей: 1/4, 2/8, 25/100 или 752/3008. Последняя дробь так и вовсе неочевидна и интуитивно непонятно, какое число она собой представляет.

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

Сортировать данные по дням недели или по месяцам в Excel

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

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

На изображении выше были выполнены следующие шаги для сортировки выборки данных в диапазоне от А2 до Е12 по месяцам года:

1. Выделите диапазон ячеек для сортировки.
2. Выберите Дом .
3. Выберите Sort & Filter, чтобы открыть раскрывающийся список.
4. Выберите Custom Sort, чтобы открыть диалоговое окно Sort.
5. Под заголовком столбца выберите « Начало месяца» в раскрывающемся списке, чтобы отсортировать данные по месяцам года.
6. Под заголовком «Сортировать по» выберите « Значения ячеек» . Сортировка основана на фактических данных в таблице.
7. Под заголовком «Порядок» выберите стрелку вниз рядом с параметром «от А до Я» по умолчанию, чтобы открыть раскрывающееся меню.
8. Выберите Custom List, чтобы открыть диалоговое окно Custom Lists.
9. В левом окне диалогового окна выберите январь, февраль, март, апрель .
10. Выберите OK, чтобы подтвердить выбор и вернуться в диалоговое окно «Сортировка».
11. Выбранный список (январь, февраль, март, апрель) отображается под заголовком Заказа.
12.  Выберите ОК, чтобы закрыть диалоговое окно и отсортировать данные по месяцам года.

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

1. Выберите стрелку вниз рядом с отображаемым списком, например, январь, февраль, март, апрель, чтобы открыть раскрывающееся меню.
2. В меню выберите параметр пользовательского списка, который отображается в порядке убывания, например, декабрь, ноябрь, октябрь, сентябрь .
3. Нажмите OK, чтобы отсортировать данные в порядке убывания, используя пользовательский список.

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

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

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

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

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

Пн, Вт, Ср, Чт, Пт, Сб, Вс

Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье

янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек

Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь

Примечание:

Изменить или удалить встроенный список невозможно.

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

Высокое, Среднее, Низкое

Большое, Среднее, Малое

Север, Юг, Восток, Запад

Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель

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

Примечание:

Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.

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

Введение значений напрямую

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

В Excel 2010

и более поздних версиях выберите пунктыФайл >Параметры >Дополнительно >Общие >Изменить списки .

В Excel 2007

нажмитекнопку Microsoft Office и выберите пунктыПараметры Excel >Популярные >Основные параметры работы с Excel >Изменить списки .

Выберите в поле Списки

пунктНОВЫЙ СПИСОК и введите данные в полеЭлементы списка , начиная с первого элемента.

После ввода каждого элемента нажимайте клавишу ВВОД.

Завершив создание списка, нажмите кнопку Добавить

На панели Списки

появятся введенные вами элементы.

Нажмите два раза кнопку ОК

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

Выполните указанные ниже действия.

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

Убедитесь, что ссылка на выделенные значения отображается в окне Списки

в полеИмпорт списка из ячеек , и нажмите кнопкуИмпорт .

На панели Списки

появятся выбранные вами элементы.

«Параметры» > «Дополнительно» > «Общие» > «Изменить списки». Если вы используете Excel 2007, нажмите кнопку Office и выберите «Параметры Excel» > «Популярные» > «Изменить списки».» /> Два раза нажмите кнопку ОК

Примечание:

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

Выполните указанные ниже действия.

По приведенным выше инструкциям откройте диалоговое окно «Списки».

Выделите список, который нужно удалить, в поле Списки

и нажмите кнопкуУдалить .

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

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

впараметрах Excel : его можно выбрать только в столбцеПорядок диалогового окнаСортировка . Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для командыЗаполнить .

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

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