Как связать строки в excel для сортировки

Обновлено: 23.04.2024

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

Как сделать сортировку в Excel по нескольким столбцам

Инструмент сортировки в Excel всегда под рукой. Например, настраиваемую сортировку можно включить на закладке: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Настраиваемая сортировка» или на закладке: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».

Допустим у нас имеется таблица, которую нужно сортировать по нескольким столбцам:

Чтобы отсортировать диапазон ячеек по нескольким условиям следует:

Перейдите на любую ячейку диапазона, который содержит данные и включите настраиваемую сортировку одним из выше описанных способов. Например, так:
В появившемся окне активируйте галочкой опцию «Мои данные содержат заголовки».
Откройте выпадающий список «Сортировать по:» и выберите опцию «Дата». Во второй секции оставляем значение выпадающего списка по умолчанию «Значения», а в третьей «Порядок» выбираем опцию «От старых к новым».
Кликните на кнопке «Добавить уровень». В первой секции выбираем опцию «Город», во второй «Значение», в третьей «От А до Я».
Еще раз добавьте новый уровень (новое условие сортировки). На новом уровне в первом выпадающем списке выбираем опцию «Продавец», а остальные оставляем без изменений – «Значение» и «От А до Я».
Снова нажмите на кнопку «Добавить уровень». В первой секции указываем «Сумма», вторая секция – без изменений, а в секции «Порядок» должно быть значение «По возрастанию». И нажите ОК.

Список отсортирован по 4 условиям:

Самая старая дата – 20.07.2017.
В этой дате название города в алфавитном порядке – Екатеринбург.
Далее в этой дате в этом городе первый продавец по алфавиту – Луналика.
Наименьшая сумма продаж Луналики в городе Екатеринбург за 20.07.2017 – 47 817,00₽.

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

Благодаря им можно упорядочить порядок выполнения условий в нижней области уровней. Порядок положений уровней существенно влияет на конечный результат сортировки по нескольким условиям. Если условия мало чем отличаются между собой можно воспользоваться кнопкой «Копировать уровень». В результате под активным (выделенным) уровнем создается новый с такими же параметрами критериев, где пользователю остается лишь внести небольшие корректировки.

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

Внимание! Каждый раз при вызове инструмента «Настраиваемая сортировка» следует обращать внимание включена или отключена опция «Мои данные содержат заголовки» (в правом верхнем углу диалогового окна инструмента). Если заголовков на самом деле нет, а данная опция включена это негативно отразиться на правильности сортировки данных, так как значения в первой строке не примут участия и не будут учитываться. Программа воспримет такие значения за названия заголовков.

Другие способы сортировки списков в Excel

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

Перейдите курсором на любую ячейку в область данных и выберите инструмент: «ВСТАВКА»-«Таблица» или нажмите комбинацию горячих клавиш CTRL+T.
В появившемся окне «Создание таблицы» автоматически заполниться поле ввода ссылкой на диапазон ячеек. При необходимости измените его на свой адрес. Если же после вызова диалогового окна поле ввода «Укажите расположения данных таблицы:» оказалось пустым, то заполните его ссылкой на необходимый диапазон.
Активируйте галочкой опцию «Таблица с заголовками» и нажмите на кнопку ОК.

Список преобразован в таблицу Excel.

Полезный совет! Выпадающие списки меню автофильтра, имеют такие же опции сортировки по возрастанию и убыванию:

Можно не создавать таблицу а просто перейти курсором в любую ячейку списка данных и включить автофильтр: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».

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

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

Порядок сортировки в Excel

Существует два способа открыть меню сортировки:

Часто используемые методы сортировки представлены одной кнопкой на панели задач:

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

Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.

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

Сортировка по цвету ячейки и по шрифту

Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.

Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:

Выделяем столбец – правая кнопка мыши – «Сортировка».
Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
Соглашаемся «автоматически расширить диапазон».

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

В открывшемся окне вводим необходимые параметры:

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

По такому же принципу сортируются данные по шрифту.

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

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

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

Сортировка строк в Excel

По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:

Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.

Случайная сортировка в Excel

Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.

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

Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.

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

Динамическая сортировка таблицы в MS Excel

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

Есть набор простых чисел, которые нужно отсортировать по возрастанию.
Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

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

Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон "фрукты" еще одно значение "помело" и проверим:

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

Как отсортировать связанные данные и сохранить формулы в Excel?

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

Сортировка связанных данных и сохранение формул с помощью Kutools for Excel

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

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

Выберите ячейку формулы, выберите формулу в строке формул и нажмите F4 Клавиша изменения ссылки на абсолютную ссылку.

Функции: Если в одной ячейке несколько ссылок, вам нужно изменить ссылки одну за другой, выбрав и нажав F4 в строке формул.

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

Теперь формулы сохраняются при сортировке.

Сортировка связанных данных и сохранение формул с помощью Kutools for Excel

Изменение ссылок по очереди занимает много времени, но с Преобразовать ссылки полезность в Kutools for Excel, вы можете быстро изменить все ссылки в диапазоне абсолютных ссылок по мере необходимости.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

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

2. в Преобразование ссылок на формулы диалог, проверьте К абсолютному вариант. Смотрите скриншот:

3. Нажмите Ok. Затем весь выбор был преобразован в абсолютные ссылки.

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

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

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

1. Выберите первую строку, которую вы хотите отсортировать по алфавиту, и нажмите Данные > Сортировать, а во всплывающем Предупреждение о сортировке диалог, проверьте Продолжить с текущим параметром выбора и щелкните Сортировать кнопка. Смотрите скриншот:

2. Затем в Сортировать диалоговое окно, нажмите Доступные опции , чтобы открыть Параметры сортировки диалог и проверьте Сортировать слева направо. Смотрите скриншот:

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

4. Нажмите OK. Вы увидите, что первая выбранная строка отсортирована от наименьшей к наибольшей. Смотрите скриншот:

5. Повторите шаги 1–4 для сортировки других строк одну за другой. И теперь каждая строка отсортирована по алфавиту и индивидуально.

Быстро сортируйте каждую строку по алфавиту с помощью формул

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

1. Выберите пустую ячейку рядом со строками, которые вы хотите отсортировать, например F2, введите эту формулу = МАЛЕНЬКИЙ ($ B2: $ D2,1) , и нажмите Enter ключ. Смотрите скриншот:

2. Затем перейдите к следующей ячейке, G2, введите эту формулу = МАЛЕНЬКИЙ (B2: D2,2) и нажмите Enter ключ. Смотрите скриншот:

3. В ячейке M2 введите формулу = МАЛЕНЬКИЙ ($ B2: $ D2,3) и нажмите Enter key, теперь первая строка отсортирована от наименьшей к наибольшей. Смотрите скриншот:

Наконечник: в приведенных выше формулах B2: D2 - это ячейки столбца в строке, которую вы хотите отсортировать, а 1, 2,3 обозначают первый наименьший, второй наименьший, третий наименьший, вы можете изменить их по своему усмотрению.

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

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

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

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

Выделение данных для сортировки

Выделите диапазон табличных данных, например A1:L5 (несколько строк и столбцов) или C1:C80 (один столбец). Этот диапазон может включать и заголовки столбцов.

Быстрая сортировка

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

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

Чтобы выполнить сортировку по убыванию (от Я до А или от наибольшего числа к наименьшему), нажмите кнопку .

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

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

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

Нажмите на вкладке Данные в группе Сортировка и фильтрация кнопку Сортировка. Откроется диалоговое окно "Сортировка".

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

Выберите в списке Сортировка пункт Значения, Цвет ячейки, Цвет шрифта или Значок ячейки.

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

Читайте также: