Vvmebel.com

Новости с мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как сортировать таблицу в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Один из самых распространенных вопросов у начинающих пользователей «Excel», так называемых «чайников» — это вопрос: «Как выполнить сортировку в программе «Excel» по…(убыванию, возрастанию, алфавиту и т.д.)

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

Читать еще:  Применение функции если в excel примеры

Рассмотрим, каким образом можно сортировать данные в столбцах «Excel».

Первый способ.

    Необходимо выделить сортируемые ячейки.

Выделить ячейки
Установить на них фильтр при помощи одноименной кнопки «Фильтр» в разделе «Данные» панели быстрого доступа («Ленты»).

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

Сортировать данные

  • В появившемся контекстном меню выбрать тип сортировки: по убыванию (от Я до А), по возрастанию (от А до Я).
  • Если ячейки имеют текстовые значения, они будут отсортированы по алфавиту от «А» до «Я» или, наоборот, от «Я» до «А», при выборе соответствующего пункта меню.
  • Второй способ.

    Данные можно отсортировать при помощи специальной кнопки на панели.

    Для этого нужно выполнить следующие действия.

      Выделить сортируемый диапазон.

    Выделить ячейки
    Кликнуть левой кнопкой мыши по кнопке «Сортировка и фильтр» на панели во вкладке «Главная»

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

    Результат_отсортированные данные

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

    Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) — по текстовому столбцу.

    Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй — числовой Объем Продаж (см. файл примера ).

    Задача1 (Сортировка таблицы по числовому столбцу)

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

    Для наглядности величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Также желтым выделены повторяющиеся значения.

    Примечание : Задача сортировки отдельного столбца (списка) решена в статьях Сортированный список (ТЕКСТовые значения) и Сортированный список (ЧИСЛОвые значения) .

    Решение1

    Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:

    • Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения) );
    • Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

    Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью Функция ВПР() в MS EXCEL ), то этот подход не годится (названия Фруктов будут выведены неправильно).

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

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

    В столбцах D и E разместим таблицу, которая будет динамически сортироваться,

    В ячейке Е7 запишем зубодробительную формулу массива :

    =ИНДЕКС(Продажи; ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ( —(СЧЁТЕСЛИ(Продажи;»

    Сортировка в Экселе

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

    Первый способ сортировки – использовать контекстное меню автофильтра (как включить автофильтр – я рассказал в этой статье):

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

    Второй способ, как сортировать в Excel – с помощью команд ленты:

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

    В зависимости от типа данных в столбце, программа предложит вам разные варианты. Например: по возрастанию, от А до Я, от старых к новым и т.п.

    В Эксель сортировка по дате идентична числовой, т.к. дата здесь – тоже число, а точнее – порядковый номер. У меня даже есть статья об этом, почитайте!

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

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

    1. Кликнем в любой ячейке таблицы
    2. Жмем на ленте Главная – Редактирование – Сортировка и фильтр – Настраиваемая сортировка
    3. В открывшемся окне выберем:
      • Сортировать по столбцу «Город»
      • Сортировка по значению
      • Порядок – от А до Я
    4. Теперь нужно настроить сортировку сумм продаж. Кликаем « Добавить уровень »
    5. Настраиваем:
      • Сортировать по столбцу «Продано»
      • Сортируем по значению
      • Порядок – по убыванию
    6. Нажимаем Ок

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

    Сортировка по цвету в Excel

    Можно менять порядок строк в зависимости от заливки, или цвета текста. Давайте попробуем:

    1. Клик в любой ячейке таблицы
    2. На ленте Главная – Редактирование – Сортировка и фильтр – Настраиваемая сортировка
    3. Параметры для операции:
      • Сортировать по столбцу «Город» (или другому с заливкой)
      • Сортировка по цвету ячейки
      • Порядок – выбираем тот цвет, который нужно переместить
    4. Если в таблице несколько цветов – можно нажать « Добавить уровень » и дополнить сортировкой по остальным цветам. Вот так:
    5. Нажимаем Ок и видим результат. Строчки выстроились по цветам в той последовательности, в которой вы указали на предыдущем этапе. Отлично!
    Читать еще:  Как разделить строки в таблице excel

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

    Сортировка в обратном порядке

    Иногда нужно список «перевернуть». Вот как это можно сделать:

    1. В новом столбце пронумеруйте строки с помощью автозаполнения
    2. В шапке подпишите эту колонку. Например, «Порядок»
    3. Отсортируйте весь диапазон по этому столбцу «По убыванию»
    4. Удалите или очистите колонку «Порядок». Готово.

    Сортировать в случайном порядке

    И так тоже можно. Методика похожа на предыдущую

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

    Это всё, что я хотел рассказать о том, как сделать сортировку в Excel . Пишите комментарии с вопросами, будем разбираться вместе. Если материал вам помог – поделитесь им с друзьями!

    Как сортировать таблицу в Excel, если в ней есть ссылки на ячейки?

    У меня есть таблица данных в excel на листе 1, которая ссылается на различные ячейки во многих других листах. Когда я пытаюсь сортировать или фильтровать лист, ссылки меняются при перемещении ячейки. Однако я не хочу вручную входить в каждую ячейку и вставлять знаки $ везде, потому что некоторые ссылки непрерывны, и я могу захотеть автоматически заполнить позже.

    например, один из моих столбцов данных использует лист 2, столбец B 23:28, лист 2, столбец C 1:15 и некоторые другие. Этот раздел столбца, который использует столбец B листа 2, находится в одном месте перед сортировкой, как и каждый из других разделов. Если я вставлю знаки$, я больше не смогу вставлять пустые строки и использовать автозаполнение для новых данных.

    В общем, я хотел бы, чтобы мои ссылки на ячейки были статическими без использования знаков$, но только для сортировки/фильтрации. Мой текущий обходной путь включает копирование значений таблицы, вставку в пустой лист и сортировку. Я надеюсь, что лучший способ сделать это.

    21 ответов

    Я уверен, что это можно решить с помощью

    для меня это работало как ниже —

    У меня были ссылки на имя листа в Формуле для того же листа. Когда я удалил текущее имя листа из формулы и отсортировал его, он работал правильно.

    даже с абсолютными ссылками сортировка не обрабатывает ссылки правильно. Относительные ссылки делаются, чтобы указать на то же относительное смещение от нового местоположения строки (что, очевидно, неправильно, потому что другие строки не находятся в том же относительном положении), а абсолютные ссылки не изменяются (потому что сортировка опускает шаг перевода абсолютных ссылок после каждой перестановки строки). Единственный способ сделать это-вручную переместить строки (преобразовав ссылки в Абсолют) один за другим. Затем Excel выполняет необходимый перевод ссылок. Сортировка Excel недостаточна, поскольку она этого не делает.

    У вас есть пара вариантов:

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

    (2) вы также можете попробовать использовать именованные диапазоны, если вы используете несколько общих диапазонов в своих формулах. Вы можете определить ‘Лист2!В23:28’ а ‘Range1 и ссылка ‘Range1’ в ваших формулах. В этом случае сортировка, очевидно, не повлияет на указанный диапазон, поскольку он определен в другом месте.

    У меня были проблемы с этим сам и нашел лучшее и простое решение.

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

    надеюсь, вы найдете, что это работает. С уважением.

    мы также Боремся с той же проблемой.

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

    здесь, пожалуйста, найдите образец макроса

    У меня была такая же проблема; у меня был мастер-лист, который был резюме информации о других листах в моей книге.

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

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

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

    очень простой способ-сохранить проблемные столбцы из отфильтрованных данных, но добавить столбец в отфильтрованный раздел со ссылками на ячейки (просто «=»ячейка»»).

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

    попробуйте этот метод:

    1. вставьте столбец слева от ячеек для сортировки.
    2. предположим, что cols B: G должны быть отсортированы на основе B
    3. копировать col B
    4. вставить значения в col a
    5. выделить cols B: G
    6. заменить = знаки на # signs
    7. сортировка cols A:G на основе a
    8. выделить cols B: G
    9. заменить # signs на = signs
    10. удалить col А.
    11. вуаля!
    Читать еще:  Как ответить на видеозвонок в скайпе

    Мне нужно было сортировать ячейки со ссылками и действительно нужно было избегать вставки значений для работы.. «Поворотный стол» сделал свое дело.

    1. подготовьте таблицы со ссылками.
    2. выберите таблицу (со ссылками) и вставьте сводную таблицу
    3. в сводной таблице выберите необходимые фильтры, чтобы сводная таблица выглядела как исходная таблица (при необходимости).
    4. сортировка / фильтрация данных по мере необходимости.

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

    надеюсь, это поможет. Андрей!—1—>

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

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

    влияние на формулу после сортировки такое же, как копирование. Сортировка не перемещает содержимое строк, она копирует их. Формула может (или не может в зависимости от abs/относительных ссылок) использовать новые данные, как и скопированная формула.

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

    и мое примечание, которое я испытал на практике:

    вышеуказанный пользователь говорит правильно, но на самом деле он имеет некоторое исключение: части формулы столбцов, содержащие имя листа (например, sheet1!А1) рассматриваются как абсолютные ссылки (несмотря на копирование это изменяет ссылки, если они относительны ) таким образом, часть формулы будет скопирована без изменения ссылок относительно изменения места формулы Это включает в себя текущие ячейки листа, адресованные полностью как: sheet1!A2 и будет рассматриваться как абсолютные ссылки(только для сортировки) Я протестировал это из excel 2010, и я не думаю, что эта проблема будет решена в других версиях. Решение состоит в том, чтобы скопировать и пропустить специальное значение в другом месте, а затем использовать сортировку.

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

    самый простой способ-использовать функцию OFFSET. Таким образом, для исходного примера формула будет: = смещение (c2, 0, -1)*1,33 =»используя текущую ячейку (c2) в качестве опорной точки, получите содержимое ячейки в той же строке, но один столбец слева (b2) и умножьте его на 1,33″

    Я использовал «сохранить как», чтобы скопировать лист в новый файл в текстовом формате msdos. Это удаляет формулы, заменяя содержимое ячейки только вычисленными значениями. Затем откройте новый файл с разделителями табуляции и отсортируйте после определения столбцов. Мне нужно было отсортировать вычисленные значения по связанной текстовой строке (destination) для журнала пробега, чтобы я мог суммировать пробег для каждого назначения.

    дата начала окончания расстояния назначения

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

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

    другое решение -: (1) скопируйте всю таблицу —вставить как ссылку в той же таблице, отныне работайте только над «связанной таблицей» (2) скопируйте столбец со значениями для сортировки и вставьте значения как раз рядом со столом для сортировки (3) Выберите таблицу и замените all =, например #, это изменит ссылку в статическом тексте (4) сортировка таблицы по вставленным значениям (5) Замените все # на=, ссылки возвращаются Готово! Он идет довольно быстро при использовании ярлыков excel

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

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

    EX: я нахожусь в книге сегодня, и я ссылаюсь на данные для торгового представителя на другой странице.

    Если мои критерии (имя представителя) сегодня!C1 (или $C1, $c$1), и я пытаюсь сортировка, лист не распознает действие. Но если вы удалите имя, ссылающееся на страницу, на которой вы находитесь (избыточная ссылка действительно), эта проблема должна прекратиться.

    Итак, SUMIFS («продажи»!С1, «Продажи»!А1, Сегодня!C1) теперь будет SUMIFS («продажи»!С1, «Продажи»!A1, C1)

    Если кто-то может просветить, почему это работает именно так, это было бы замечательно.

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

    исправлено для меня!

    простое решение-скопируйте из excel и вставьте в Google листы. Он копирует все ваши ссылочные ячейки как абсолютные ячейки, чтобы вы могли начать с нуля. Затем отсортируйте и загрузите обратно как сетку excel и переформатируйте ее для своих нужд.

    Ссылка на основную публикацию
    Adblock
    detector