Как сортировать в Excel и сохранять строки вместе: 4 простых метода

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

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

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

Оглавление

• Как сортировать, сохраняя строки вместе
• 3 способа доступа к командам сортировки
  • Вкладка «Главная»
  • Вкладка «Данные»
  • Щелкните правой кнопкой мыши
• Как сортировать, выбирая несколько столбцов, чтобы сохранить строки вместе
• Как сортировать с помощью Excel, чтобы сохранить строки вместе
• Как использовать функцию сортировки, чтобы сохранить строки вместе
  • Сортировка и фильтрация вместе
• Как использовать настраиваемые списки для сортировки и хранения строк вместе
  • Как создать пользовательский список
  • Как использовать список
• 3 совета по работе с типами данных
• 4 совета по эффективной сортировке
• Последние мысли

Как сортировать, сохраняя строки вместе

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

Предположим, у вас есть данные об инвентаризации одежды, где тип предмета находится в столбце A, а цвет — в столбце B. Это данные:

• Футболка, Красный

• Джинсы, Синий

• Толстовка, Зеленый

• Футболка, Фиолетовый

• Толстовка, Черный

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

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

Вот результаты неправильного метода сортировки:

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

3 способа доступа к командам сортировки

Самый распространенный способ сортировки в Excel — использование команды «Сортировка». Доступ к этой функции можно получить тремя способами:

• Вкладка «Главная»

• Вкладка «Данные»

• Щелкните правой кнопкой мыши

Вкладка «Главная»

1. Перейдите на вкладку Главная.

2. Разверните раскрывающееся меню «Сортировка и фильтрация» в группе «Редактирование».

3. Выберите вариант восходящего или нисходящего порядка.

Excel предполагает, что у вас есть строка заголовка с этой опцией.

Вкладка «Данные»

1. Перейдите на вкладку Данные.

2. Нажмите кнопку «Сортировка» в группе «Сортировка и фильтр».

3. Выберите параметры сортировки в диалоговом окне сортировки.

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

Щелкните правой кнопкой мыши

1. Выберите данные, которые вы хотите отсортировать.

2. Щелкните правой кнопкой мыши.

3. Выберите «Сортировать» в раскрывающемся меню (внизу).

Как сортировать, выбирая несколько столбцов, чтобы сохранить строки вместе

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

Чтобы использовать этот метод, вы выберете все задействованные данные перед использованием команды «Сортировка». Это означает, что вы не просто выбираете один столбец.

В приведенном выше примере данных вы должны либо выбрать весь рабочий лист, либо конкретный диапазон данных ячеек от A1 до B6, либо два столбца A и B.

Следуй этим шагам:

1. Выберите все связанные ячейки (или Ctrl-A для всего).

2. Перейдите на вкладку Главная.

3. Разверните раскрывающийся список «Сортировка и фильтр».

4. Выберите «Сортировать от А до Я».

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

Если у вас есть строка заголовка, она также будет распознана.

Как сортировать с помощью Excel, чтобы сохранить строки вместе

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

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

Следуй этим шагам:

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

2. Перейдите на вкладку Главная.

3. Разверните раскрывающийся список «Сортировка и фильтр».

4. Выберите «Сортировать от А до Я» (или по вашему усмотрению).

5. В появившемся окне «Предупреждение о сортировке» выберите «Расширить выбор».

Обратите внимание, что Excel обнаружил наличие столбцов, смежных с выбранным вами. В этом случае Excel спросит вас, хотите ли вы включить несколько столбцов.

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

Как использовать функцию сортировки, чтобы сохранить строки вместе

Функция СОРТИРОВКА в Excel автоматически объединяет строки при сортировке.

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

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

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

Синтаксис функции SORT таков:

=СОРТИРОВАТЬ(массив, [индекс_сортировки], [порядок_сортировки], [столбец_или_строка]

• массив: диапазон ячеек для сортировки.

• sort_index: столбец для сортировки.

• sort_order: TRUE для возрастания (по умолчанию) и FALSE для убывания.

• column_or_row: FALSE для столбцов (по умолчанию) и TRUE для строк.

Допустим, у вас есть данные в ячейках с A1 по B6 с заголовком. Вы хотите отсортировать эти данные по значениям в столбце A в порядке возрастания. Используйте эту формулу:

=СОРТИРОВАТЬ(A2:B6)

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

Сортировка и фильтрация вместе

Функция ФИЛЬТР в Excel используется для фильтрации диапазона данных на основе определенных критериев , а не для сортировки данных. Однако вы можете комбинировать функцию FILTER с функцией SORT для фильтрации и сортировки данных, сохраняя при этом строки вместе.

Функция ФИЛЬТР также является функцией динамического массива (как и СОРТИРОВКА). Как я упоминал в предыдущем разделе, Excel заполнит другие ячейки, необходимые для отображения всего диапазона отфильтрованных данных.

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

1. Отфильтруйте строки в столбце B (цвет).

2. Отсортируйте отфильтрованные строки по столбцу A (название элемента).

Это синтаксис функции ФИЛЬТР:

=ФИЛЬТР(массив, критерии, [if_empty])

• массив: диапазон ячеек для сортировки.

• критерии: по чему фильтровать.

• if_empty: что возвращать, если совпадения нет.

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

=СОРТИРОВАТЬ(ФИЛЬТР(A2:B6,B2:B6 = "Зеленый"), 1)

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

Как использовать настраиваемые списки для сортировки и хранения строк вместе

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

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

Заказ по индивидуальному списку состоит из двух этапов:

1. Создайте список.

2. Используйте список в качестве последовательности заказов.

Как создать пользовательский список

1. Перейдите на вкладку «Файл».

2. Выберите Параметры.

3. Нажмите «Дополнительно».

4. Прокрутите вниз до раздела Общие.

5. Нажмите «Редактировать пользовательский список», чтобы открыть диалоговое окно «Пользовательские списки».

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

На этом рисунке показан список цветов, введенных в интерфейс.

Как использовать список

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

1. Выберите столбец, по которому вы хотите выполнить сортировку.

2. Перейдите на вкладку Данные.

3. Нажмите кнопку «Сортировка» в группе «Сортировка и фильтр».

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

5. Разверните раскрывающийся список под заголовком «Заказ».

6. Выберите «Пользовательский список».

7. Выберите свой список.

8. Нажмите ОК дважды.

Эта инфографика показывает последовательность:

3 совета по работе с типами данных

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

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

2. При сортировке по дням или месяцам убедитесь, что даты указаны в единообразном формате (например, ММ/ДД/ГГГГ).

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

4 совета по эффективной сортировке

Вот наши лучшие советы по эффективной сортировке:

1. Если в ваших данных есть скрытые столбцы или скрытые строки, отобразите их перед сортировкой.

2. Полезно использовать функцию Freeze Panes при сортировке длинных данных.

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

4. Помните, что пустые строки будут перемещены в конец при сортировке, т.е. удалены.

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

Последние мысли

Сортировка — одна из самых распространенных задач при работе с электронной таблицей Excel.

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

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