Динамическая сортировка в excel при добавлении данных

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

Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый:

Список фруктов

; а второй — числовой

Объем Продаж

(см.

файл примера

).

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

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

Для наглядности величины значений в столбце

Объем Продаж

выделены с помощью

Условного форматирования

(

). Также желтым выделены повторяющиеся значения.

Примечание

: Задача сортировки отдельного столбца (списка) решена в статьях

Сортированный список (ТЕКСТовые значения)

и

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

.

Решение1

Если числовой столбец гарантировано не содержит

повторяющихся

значений, то задача решается легко:

• Числовой столбец отсортировать функцией
  
  НАИБОЛЬШИЙ()
  
  (см. статью

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

  );

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

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

ВПР()

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

Функция ВПР() в MS EXCEL

), то этот подход не годится (названия Фруктов будут выведены неправильно).

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

Создадим для удобства 2

Динамических диапазона

Фрукты

и

Продажи

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

В столбцах

D

и

E

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

В ячейке

Е7

запишем зубодробительную

формулу массива

:

=ИНДЕКС(Продажи; ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ( —(СЧЁТЕСЛИ(Продажи;»<«&Продажи)&»,»&ПОВТОР(«0»;3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6)); СТРОКА()-СТРОКА($E$6));1)*1000;0) )

Данная формула сортирует столбец

Объем продаж

(динамический диапазон

Продажи

) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 1000.

Разберем формулу подробнее:

• Формула
  
  СЧЁТЕСЛИ(Продажи;»<«&Продажи)
  
  возвращает массив {4:5:0:2:7:1:3:5}. Это означает, что число 64 (из ячейки
  
  B7
  
  исходной таблицы, т.е. первое число из диапазона
  
  Продажи
  
  ) больше 4-х значений из того же диапазона; число 74 (из ячейки
  
  B8
  
  исходной таблицы, т.е. второе число из диапазона
  
  Продажи
  
  ) больше 5-и значений из того же диапазона; следующее число 23 — самое маленькое (оно никого не больше) и т.д.
• Теперь вышеуказанный массив целых чисел превратим в массив чисел с дробной частью, где в качестве дробной части будет содержаться номер позиции числа в массиве: {4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008}. Это реализовано выражением
  
  &»,»&ПОВТОР(«0»;3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6))
  
  Именно в этой части формулы заложено ограничение о не более 1000 строк в исходной таблице (см. выше). При желании его можно легко изменить, но это бессмысленно (см. ниже раздел о скорости вычислений).
• Функция
  
  НАИБОЛЬШИЙ()
  
  сортирует вышеуказанный массив.
• Функция
  
  ОСТАТ()
  
  возвращает дробную часть числа, представляющую собой номера позиций/1000, например 0,005.
• Функция
  
  ОКРУГЛ()
  
  , после умножения на 1000, округляет до целого и возвращает номер позиции. Теперь все номера позиций соответствуют числам столбца
  
  Объемы продаж,
  
  отсортированных по убыванию.
• Функция
  
  ИНДЕКС()
  
  по номеру позиции возвращает соответствующее ему число.

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

Фрукты

=ИНДЕКС(Фрукты;ОКРУГЛ(…))

В

файле примера

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

ОКРУГЛ()

, вынесена в отдельный столбец

J

. Поэтому итоговые формулы в сортированной таблице выглядят так:

=ИНДЕКС(Фрукты;J7)

и

=ИНДЕКС(Продажи;J7)

Также, изменив в формуле массива функцию

НАИБОЛЬШИЙ()

на

НАИМЕНЬШИЙ()

получим сортировку по возрастанию.

Для наглядности, величины значений в столбце

Объем Продаж

выделены с помощью

Условного форматирования

(

). Как видно, сортировка работает.

Тестируем

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

1. В ячейку

А15

исходной таблицы введите слово

Морковь

; 2. В ячейку

В15

введите

Объем продаж

Моркови = 25; 3. После ввода значений, в столбцах

D

и

Е

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

Скорость вычислений формул

На «среднем» по производительности компьютере пересчет пары таких

формул массива,

расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа (

) и периодически нажимать клавишу

F9

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

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

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

CTRL+SHIFT+L

). В выпадающем списке выберите требуемую сортировку.

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

Также можно воспользоваться инструментом Сортировка (

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

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

При использовании

Таблиц в формате EXCEL2007

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

Какой вариант предпочтительней — как всегда — выбирать разработчику.

Еще одна формула массива (+дополнительный столбец). Задача1.1

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

формулы массива

, но нам понадобится дополнительный (служебный) столбец D (см.

файл примера лист Пример2

):

=

НАИБОЛЬШИЙ(ЕСЛИ(F7=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0);СЧЁТЕСЛИ($F$7:$F$14;F7)-СЧЁТЕСЛИ($F$6:F6;F7))

В столбце F содержится отсортированный столбец В (объем продаж). Формула возвращает позицию значения объема продаж. Например, число 86 находится в 5-й строке таблицы.

Для повторов выражение

ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0)

будет возвращать несколько значений: {0:2:0:0:0:0:0:8}, т.е. число 74 находится в строках 2 и 8.

С помощью функции

НАИБОЛЬШИЙ()

сначала выводится 2, затем 8 (в разных строках).

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

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

Отсортируем строки таблицы по содержимому Текстового столбца (по Фруктам).

Примечание

: Про сортировку списка текстовых значений можно прочитать в статье

Сортированный список в MS EXCEL (ТЕКСТовые значения)

Как и в предыдущей задаче предположим, что в столбце, по которому ведется сортировка имеются повторы (названия Фруктов повторяются).

Для сортировки таблицы придется создать 2 служебных столбца (D и E).

В столбце D введем

формулу массива, возвращающую несколько значений

=

СЧЁТЕСЛИ($B$7:$B$14;»<«&$B$7:$B$14)+1

Эта формула является аналогом

ранга

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

В столбце E введем обычную формулу:

=

СЧЁТЕСЛИ($D$6:D6;D7)+D7

Эта формула учитывает повторы текстовых значений и корректирует «ранг». Теперь разным значениям Яблоки соответствуют разные «ранги» — 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):

=

ИНДЕКС($B$7:$B$14;ПОИСКПОЗ(СТРОКА()-СТРОКА($G$6);$E$7:$E$14;0))

Аналогичная формула выведет соответствующий объем продаж (столбец Н).

Задача 2.1 (Двухуровневая сортировка)

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

Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.

Подробности в

файле примера

на листе Задача2.

Сортировка в Excel формулой.

​Смотрите также​​ к новым.​ фильтр»-«Сортировка по убыванию».​ ​ столбце G (можно​​ раз. То есть​​B4​ ​диалогового окна​​ выполнить сортировку.​ для каждого столбца.​ на вкладке​ список еженедельных дел​ для того, чтобы​Сортировка​ а не по​ диалоговое окно​ чисел не применима.​ «Shift» + «Enter»,​Есть несколько способов​Если мы работаем только​В появившемся диалоговом окне​
​ даже на заголовок​ две позиции по​и скопируйте вниз,​Настраиваемые списки​
​Для достижения наилучших результатов​
​ Для этого выполните​ ​Данные​
​ по дому, и​ продемонстрировать Вам, как​(Sort), чтобы открыть​
​ столбцу, и как​Сортировка​
​Думаю, всем известно, как​
​ п.ч. это формула​, как сделать сортировку​ с фрагментом таблицы​ «Обнаруженные данные в​ столбца G1). А​ 6 повторений.​ например, Маркером заполнения):​.​
​ каждого столбца должен​ ту же последовательность​(Data).​
​ мы хотим упорядочить​​ это работает. Таким​
​ одноимённое диалоговое окно.​ ​ упорядочить данные на​
​(Sort).​ выполнить сортировку по​ массива. Копируем формулу​ в​ где нет заголовков​ неуказанного диапазона», выберите​ Потом выберите инструмент:​​Не подскажете как​=ЕСЛИОШИБКА(НАИМЕНЬШИЙ($A$4:$A$21;СТРОКА()-СТРОКА($B$3));»»).​Рекомендации и примеры сортировки​ содержать заголовки.​ действий, что при​Как видите, сортировка данных​ их по дню​ же образом мы​
​ Обратите внимание на​ листе в алфавитном​
​В появившемся диалоговом окне,​ столбцу в алфавитном​ вниз по столбцу.​Excel​ столбцов, а только​ опцию: «сортировать в​ «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка​ это можно реализовать?​
​Функция НАИМЕНЬШИЙ() сортирует исходный​ данных по цвету​На ленте, щелкните​ сортировке по нескольким​ в Excel по​ недели или по​ можем сделать сортировку​ параметр​ порядке по заголовкам​ нажимая кнопку​ порядке или по​ Получилось так.​. Для этого есть​
​ лишь диапазон ячеек​ пределах указанного выделения».​ по убыванию».​ Желательно формульно.​
​ список и выводит,​Сортировка по дате​данные​ столбцам в предыдущем​ настраиваемому списку –​ важности.​ нашего списка фотокамер​Мои данные содержат заголовки​ столбцов. Вы также​Добавить уровень​

excel-office.ru

Сортировка Числовых Значений в Excel. Бесплатные примеры и статьи.

​ возрастанию / убыванию.​Если предполагается добавлять​функция в​ с текущими значениями,​ И нажмите на​В результате данные упорядочились​AlexM​ в соответствии со​Сортировку списка можно осуществить​>​ примере.​ задача вовсе не​Начинаем с того, что​ по строке, в​(My data has​ научитесь выполнять сортировку​(Add Level), установите​ Это делается одним​ строки в таблицу,​Excel «Сортировка и фильтр».​ как показано ниже​ кнопку «Сортировка».​

excel2.ru

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

​ и организовались в​: Формула для чисел​ значением второго аргумента,​ через меню Данные/​Сортировка​И вот, наконец, наш​ сложная. Ещё один​ выделяем данные, которые​ которой указаны размеры,​ headers) в верхнем​ данных нестандартными способами,​ нужное количество столбцов,​ нажатием кнопки​ то можно сделать​Как сортировать с​

​ на рисунке:​Обратите внимание на то,​ определенную структуру:​ и формула массива​ элементы списка. Формула​ группа Сортировка и​.​​ список домашних дел​​ приём, которому мы​​ нужно сортировать, и​​ разрешение, тип сенсора​​ правом углу диалогового​​ когда сортировка в​​ по которым будет​​А-Я​ динамический диапазон столбца​​ помощью этой функции,​​Тогда в диалоговом окне​ что значения ячеек​​Как оказалось, наибольшую премию​​ для названия. Код​

​ СТРОКА()-СТРОКА($B$3) задает порядковые​ фильтр/ Сортировка. В​В диалоговом окне​ упорядочен в наивысшей​ должны научиться –​ открываем диалоговое окно​ или по любому​

• ​ окна. Если в​
• ​ алфавитном порядке или​ выполняться сортировка.​(A-Z) и​
• ​ А. Смотрите статью​ читайте в статье​

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

​ инструмента «Настраиваемая сортировка»​ в остальных столбцах​ получил сотрудник «Евгений​ =ЕСЛИ(E1=0;»»;НАИБОЛЬШИЙ(B$1:B$99;СТРОКА())) =ИНДЕКС(A$1:A$99;ОСТАТ(НАИБОЛЬШИЙ(B$1:B$99+СТРОКА(B$1:B$99)%%;СТРОКА());1)*10^4) формат​ числа (1, 2,​ случае, если в​Сортировка​ степени логично, сначала​ сортировка данных по​Сортировка​ другому параметру, который​ Ваших данных нет​ по значению чисел​В выпадающих списках​Я-А​ «Чтобы размер таблицы​ «Сортировка в Excel».​ следует отключить опцию​ остались без изменений:​ Пруткий».​ ячеек названий​ 3, 4,..) для​

1. ​ исходный список постоянно​​в поле​​ по дням недели,​​ собственному настраиваемому списку.​​(Sort), точно так​ сочтём более важным.​​ заголовков, то галочки​​ не применима.​Сортировать по​​(Z-A) в разделе​​ Excel менялся автоматически».​ Здесь рассмотрим, как​​ «Мои данные имеют​​Такой способ необходимо применять​
2. ​Внимание! Список, который будет​;;;@​​ второго аргумента функции​​ вводятся новые значения,​Сортировать по​ затем по важности​В нашей таблице есть​
3. ​ же, как в​​ Сделаем ещё одну​​ там быть не​​Я полагаю, что в​​(Sort by) и​Редактирование​ Тогда формулу массива​ настроить сортировку в​ заголовки столбцов». В​ только как вспомогательный.​ сортирован не должен​antonhauff​ НАИМЕНЬШИЙ().​ то для поддержания​выберите столбец, который​​На сегодня всё. Спасибо​​ столбец​ предыдущих примерах –​​ сортировку, на этот​​ должно. В нашей​ 90% случаев сортировка​Затем по​​(Editing) на вкладке​​ в столбце В​ Excel с помощью​
4. ​ результате чего в​​​​ содержать объединенных ячеек.​: Спасибо большое. А​
   • ​Если исходный диапазон охватывает​ списка в сортированном​​ нужно выполнить сортировку.​​ за внимание!​Priority​
   • ​Данные​ раз по цене.​​ же таблице заголовки​​ данных в Excel​(Then by) выберите​Главная​ нужно будет чуть​ формул. Эта сортировка​
   • ​ качестве критериев для​Как настроить сортировку в​​Примечание. Инструмент для базовой​​ как-то можно чтобы​ пустые ячейки, то​

   

​ состоянии, пользователь должен​Например, чтобы отсортировать данные​Урок подготовлен для Вас​– в нём​>​​Наша задача – повторить​​ присутствуют, поэтому мы​ выполняется по значению​ столбцы, по которым​(Home) либо в​ дополнить функцией. Формулу​ удобна тем, что​ каждого первого выпадающего​ Excel? Допустим мы​ сортировки диапазонов доступен​ было без повторов?​ необходима функция подавления​ каждый раз вручную​ в предыдущем примере​ командой сайта office-guru.ru​ указаны приоритеты задач.​Сортировка​ описанные выше шаги​

Сортировка данных в Excel по заголовкам строк и столбцов

​ оставляем эту галочку​ в одном или​ нужно произвести сортировку​ разделе​ в ячейке В1​ при изменении данных​ списка секции «Столбец»​ хотим сортировать сотрудников​ так же на​AlexM​ ошибки ЕСЛИОШИБКА(). После​ сортировать список через​ по дате доставки,​Источник: https://www.ablebits.com/office-addins-blog/2013/09/04/excel-sort-column-row-custom-list/​ Чтобы упорядочить с​(Data > Sort).​

​ 1 – 3.​ и нажимаем кнопку​ нескольких столбцах. Однако,​ данных. Например, Вы​Сортировка и фильтр​ напишем такую.​ в столбце, произойдет​ в каждом уровне,​

​ по дате рождения​ закладке: «ДАННЫЕ»-«Сортировка по​: С дополнительным столбцом​ заполнения незаполненных ячеек,​ меню, что не​ в поле​Перевел: Антон Андронов​

1. ​ его помощью еженедельные​В поле​ Затем на шаге​Параметры​ иногда встречаются не​ планируете, как провести​(Sort & Filter)​=ЕСЛИОШИБКА(ИНДЕКС(Фамилии;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Фамилии;» И формулу​ автоматическая сортировка в​ будут использованы заголовки​ (от старых к​ возрастанию/по убыванию».​ С.​​ входящих в диапазон​​ всегда удобно. Гораздо​Сортировать по​Автор: Антон Андронов​ задачи от более​Сортировать по​ 4 вместо строки​​(Options).​​ такие простые наборы​
2. ​ выходные, и просматриваете​​ на вкладке​​ протянуть вниз по​​ Excel новых данных.​​ столбцов рабочего листа​ новым) в соответствии​И встроен в выпадающие​Первая формула массива,​​А4:А21​​ удобнее иметь динамически​выберите​Примечание:​ важных к менее​(Sort by) выбираем​1​В открывшемся диалоговом окне​ данных, которые нужно​ список отелей, предложенный​Данные​ столбцу чуть ниже​Рассмотрим два способа​​ Excel (Столбец А,​​ с каждым городом​
3. ​ меню заголовков таблиц​​ вторая обычная Код​​, список в столбце​ сортирующийся список. Список​​доставка​​Мы стараемся как​​ важным, выполним следующие​​ столбец, по которому​выбираем строку​​Параметры сортировки​​ упорядочить по строке​
4. ​ туристическим агентством. Вы​(Data):​​ последней строки таблицы.​​ сортировки данных в​​ Столбец B, C​​ (места рождения). Для​ в режиме автофильтра​​ =ИНДЕКС(A$2:A$99;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B$99*C$2:C$99+СТРОКА(C$2:C$99)%%;СТРОКА(A1));B$2:B$99*C$2:C$99+СТРОКА(C$2:C$99)%%;)) =ЕСЛИ(E2=0;»»;ВПР(E2;A$2:B$99;2;)) Формат​​В​ состоит из числовых​.​ можно оперативнее обеспечивать​ действия.​ нужно выполнить сортировку.​​4​​(Sort Options) в​ (горизонтально), то есть​ хотите отсортировать этот​Однако, сортировка в Excel​​Если данные в​​ таблице Excel с​ и т.д.). А​​ этого:​​ или умной таблицы:​ ячеек столбца Е​​будет отсортирован автоматически.​​ значений.​​В раскрывающемся списке​​ вас актуальными справочными​

​Повторите шаги 1 и​ Мы хотим упорядочить​, в которой указаны​

​ разделе​ изменить порядок столбцов​ список сначала по​ имеет гораздо больше​ таблице не сортируются​ помощью формул.​ при применении всех​Сначала для данного примера​Так же встроен в​;;;@​Примечание​Сортированный список может быть​порядок​ материалами на вашем​ 2 из предыдущего​ наши задачи по​ розничные цены (Retail​Сортировать​ слева направо, основываясь​ географическому признаку (столбец​

​ настраиваемых параметров и​ по дате, значит​Первый способ.​ параметров в сортировку​ лучше сбросить сортировку​​ контекстное меню, которое​​antonhauff​​. Функция ЕСЛИОШИБКА() появилась начиная​​ полезен при создании​выберите​ языке. Эта страница​ примера. Когда откроется​ дням недели, то​

​ Price). В результате​(Orientation) выбираем вариант​ на заголовках столбцов​Region​ режимов работы, которые​ не правильно написаны​Сортировка в​ будут включены все​ предыдущих примеров. Для​ появляется по нажатию​: Спасибо большое​ с версии MS​

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

Сортировка в произвольном порядке (по настраиваемому списку)

​ не так очевидны,​ все или некоторые​Excel по возрастанию.​ строки и ячейки​ этого отсортируйте таблицу​ правой кнопкой мышки:​Blago2000​ EXCEL 2007. О​ Некоторые функции для​.​ ее текст может​Списки​ столбец​ выглядеть вот так:​(Sort left to​ в определённой строке.​ питания (столбец​ но могут оказаться​

​ даты. Как правильно​Сортируем с помощью​ исходного диапазона (фрагмента​ по первому столбцу​Читайте так же: Возможности​: Доброго времени суток!​ том как ее​

1. ​ нормальной работы также​На вкладке «​ содержать неточности и​(Custom Lists), в​​Day​​Обратите внимание, что отсортированы​ right) и жмём​Вот список фотокамер, предоставленный​​Board basis​​ очень удобны:​​ сортировать по датам,​​ функций «НАИМЕНЬШИЙ» или​
2. ​ таблицы), что является​​ А (№п/п) –​​ сортировки данных в​Что-то не получается​ заменить для более​ требуют сортированный список:​Списки​ грамматические ошибки. Для​ одноимённом столбце слева​. Затем в выпадающем​​ оказались данные не​​ОК​ региональным представителем или​​) и в конце​​Сортировка по нескольким столбцам​​ смотрите в статье​​ «НАИБОЛЬШИЙ».​ правильным результатом в​ по возрастанию.​
3. ​ Excel списком.​​Заранее благодарен за​​ ранних версий см.​ ПРОСМОТР(), ВПР(), ПОИСКПОЗ().​» выберите нужный​ нас важно, чтобы​​ нажмите​​ списке под заголовком​ только в выбранной​.​ скачанный из интернета.​ концов по цене​​Сортировка данных в Excel​​ «Сортировка по дате​

​В ячейке В5 пишем​ данном случаи.​Перейдите на любую ячейку​

​Если таблицу необходимо будет​​ ответ!​ Функция ЕСЛИОШИБКА() в​Сортировку списка можно осуществить​ список и нажмите​ эта статья была​НОВЫЙ СПИСОК​Порядок​ строке. Целые столбцы​Следующий шаг – в​​ Список содержит разнообразные​​ (столбец​​ по заголовкам строк​​ в Excel».​ такую формулу. =НАИМЕНЬШИЙ(A:A;СТРОКА(A1))​​Примечание. Кнопка вызова инструмента​​ исходной таблицы и​

​ привести в изначальный​Blago2000​ MS EXCEL.​ через меню Данные/​ кнопку​ вам полезна. Просим​(NEW LIST) и​(Order) выбираем вариант​ меняются местами, но​

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

​ диалоговом окне​ данные о функциях,​​Price​​ и столбцов​Отсортируем формулами таблицу, состоящую​Обратите внимание!​ для настраиваемой сортировки​ выберите инструмент: «ГЛЛАВНАЯ»-«Редактирование»-«Сортировка​ вид, тогда перед​:​Абсолютную ссылку на диапазон​

​ группа Сортировка и​OK​ вас уделить пару​ заполните нужными значениями​​Настраиваемый список​​ данные не перемешиваются.​Сортировка​ характеристиках и ценах​​), как это показано​​Сортировка в произвольном порядке​ из 2-х столбцов.​Хотя первая ячейка​​ так же доступна​​ и фильтр»-«Настраиваемая сортировка».​ каждой сортировкой создавайте​AlexM​ исходного списка $A$4:$A$21​ фильтр/ Сортировка. Для​для сортировки листа.​ секунд и сообщить,​

​ поле​​(Custom list), как​​ Другими словами, на​(Sort) под заголовком​ и выглядит примерно​ на рисунке ниже:​​ (по настраиваемому списку)​​ Сортировку будем производить​

​ для сортировки находится​ на закладке «ДАННЫЕ»-«Сортировка».​ Появиться диалоговое окно​

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

​ так:​Жмите​Я покажу Вам, как​ по одному из​ в пятой строке​Читайте так же: Сортировка​ в котором можно​ В данном примере​Доброго времени суток!​ на Динамический диапазон,​ первое (верхнее) числовое​

​ необходимые значения для​ вам, с помощью​(List entries). Внимательно​ экрана ниже:​ представлен список фотокамер,​(Row) в выпадающем​

​Нам нужно отсортировать этот​ОК​

​ в Excel сортировать​ столбцов таблицы (решим​
​ таблицы (А5), в​
​ по месяцам в​

​ задать несколько условий​

office-guru.ru

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

​ – это столбец​​Что-то не получается​ границы которого будут​ значение в списке (предполагается,​ сортировки. Расположите их​ кнопок внизу страницы.​ введите элементы Вашего​В диалоговом окне​ расставленный в порядке​ списке​ список фотокамер по​и смотрите, что​ данные по двум​ 2 задачи: сортировка​ формуле пишем ячейку​ Excel.​ для сортировки таблицы.​ А (№п/п). Часто​Заранее благодарен за​ изменяться от количества​ что все значения​ в том порядке,​

​ Для удобства также​ списка именно в​Списки​ от самых дешёвых​Сортировать по​ наиболее важным для​ получилось:​ или более столбцам.​ таблицы по числовому​ А1. Копируем ячейку​Таблицы данных, которые мы​В первом выпадающем списке​ необходимо привести таблицу​ ответ!​ введенных значений (см.​

​ введены в одном​ что вы хотите​ приводим ссылку на​ том порядке, в​

​(Custom Lists) в​ до самых дорогих.​(Sort by) выбираем​ нас параметрам. Для​В первую очередь отсортированы​ Работа инструмента показана​

1. ​ и сортировка по​ вниз по столбцу.​

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

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

   

3. ​ котором они должны​​ одноимённом поле выбираем​​Надеюсь, теперь стало ясно,​​ строку, по значениям​​ примера первым делом​ значения в столбце​

   ​ на примере Excel​ текстовому столбцу). Формулы​ Получилось так.​ сотрудников в большинстве​​ по которому будут​​ после выполнения с​​ самые быстрые и​​СОВЕТ:​

   

4. ​ ячеек) и нажмите​​ сверху вниз. Например:​​ языке) .​​ быть расположены в​​ нужный список. В​

   

5. ​ как работает сортировка​​ которой будет выполнена​​ выполним сортировку по​Region​ 2010 – именно​​ сортировки настроим так,​​В столбце С установили​

   

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

1. ​ случаев не упорядочены​ сортированы данные в​ ней многочисленных и​ эффективные методы сортировки​Решение задачи о​ в меню соответствующую​Выделите ячейки в этом​

   

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

3. ​ формулу с функцией​​ и работать с​​ первую очередь. В​​ сложных сортирований данных​​ данных в Excel​

4. ​ сортировке списка с​​ кнопку сортировки.​​ столбце листа и​​ сортировать данные, либо​​Нажмите​

   

​Day​​ Excel. Но что​ примере мы выбираем​Выбираем диапазон данных, которые​ порядке.​ на моём компьютере.​​ новых данных в​​ «НАИБОЛЬШИЙ». =НАИБОЛЬШИЙ(A:A;СТРОКА(A1))​​ ними без организации​​ данном примере –​

Еще о способах сортировки данных

• ​ по разным столбцам,​ по различным критериям:​

• ​ текстовыми значениями приведено​

support.office.com

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

​Если список содержит пустые​ щелкните​ по дням недели​Добавить​указаны сокращённые наименования​ если наши данные​ строку​ нужно сортировать. Если​Затем упорядочены значения в​ Если Вы работаете​ исходную таблицу, сортированная​Второй способ​ – сложно. Возникает​ это «Место рождения».​ критериям или условиям.​ по возрастанию или​ в одноименной статье​ ячейки, то сортировка​

​Файл​ или месяцам года.​(Add), и созданный​ дней недели –​ должны быть упорядочены​1​ нам нужно, чтобы​

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

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

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

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

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

​так, что отели,​ возникнуть не должно,​ иметь отсортированную таблицу​Excel.​​ числовые – по​​ – «Значение», а​ с номерами строк,​
​ коновым и от​

​Для сортировки от максимального​ ячейки. Но, если​>​ списки для сортировки​ существующим. Далее нажмите​ жмём​ возрастанию / убыванию?​ выпадающем списке под​ столбцов, то достаточно​ предлагающие сервис «Всё​

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

​ по другим характеристика,​​ОК​ОК​Если нужно упорядочить данные​ заголовком​ выделить любую ячейку​ включено» (AI) –​ Excel 2007 и​ Также сделаем двухуровневую​

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

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

​ Excel 2013 работает​ сортировку: сначала по​ а текст, например,​

​ новых. Сортировать вручную​ А до Я».​Читайте так же: Сортировка​

excel2.ru

Динамическая сортировка текстовых значений

​ от А до​​Также для сортировки числовых​ с пустыми ячейками),​В разделе​ в алфавитном порядке​Вот так выглядит наш​
​Готово! Теперь домашние дела​ порядке (не по​(Sort on) должно​ в случае с​ списка.​ практически так же.​ числовому, затем (для​
​ фамилии, наименование товара,​ все столбцы по​Нажмите на кнопку «Добавить​ по цвету ячеек​ Я, а так​
​ списков можно использовать​ то данные будут​Общие​

​ хорошо — например​​ список домашних дел,​ упорядочены по дням​ алфавиту), то можно​ быть выбрано​ нашим набором данных​​И, наконец, по стоимости​

​ Разницу можно заметить​​ повторяющихся чисел) -​ т.д., то используем​ отдельности – это​

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

​ недели:​​ воспользоваться встроенными в​

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

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

CyberForum.ru

Сортировка данных в Excel по дате по алфавиту и по возрастанию

​ «и» Низкий —​Подсказка:​Замечание:​ Excel настраиваемыми списками​(Values), а под​ допустим, так как​Price​ диалоговых окон и​В этой статье я​ такая.​ для данной задачи.​ новые критерии. 1​ сортировку только лишь​Ниже на рисунке представлена​: Есть таблица, которая​

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

​ расположены внизу списка​.​ или S, M,​Для создания длинных​

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

​ Особенно если столбцов​ – «Дата рождения»,​ внутри одного столбца​ выборка информации из​ формируется на основе​ (это не зависит​В окне​ L, XL.​

​ настраиваемых списков удобнее​ вносить изменения в​ собственный. При помощи​

​Порядок​A​ порядке возрастания.​

​ приступим…​ в Excel выполнить​ ему имя. Как​

​ более тысячи. Поэтому​ 2 – «Значение»,​ таблицы без привязки​ базы данных по​ других таблиц и​

​ от направления сортировки).​Списки​Настраиваемые списки позволяют сортировать​ и быстрее импортировать​

​ эти данные, помните​ встроенных настраиваемых списков​(Order) установим​перечисляются характеристики камер,​

​Сортировать данные по нескольким​Кликните мышью по кнопке​ сортировку данных по​

Как убрать сортировку в Excel

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

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

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

​ числовые значения, то​Импорт​ доставки (месяц) или​ диапазона. Об этом​ добавленные новые или​ к примеру, дни​(A to Z).​ чтобы он остался​ оказалось совсем не​(Sort) на вкладке​

1. ​ заголовкам столбцов в​ имя в Excel​ – настраиваемой сортировкой.​ И нажмите на​
2. ​ Например, столбец B​ таблицу относительно столбца​
3. ​Задача стоит в​ его сортировку можно​.​ по приоритету.​ подробно рассказано в​ изменённые существующие данные​ недели или месяцы​

​В результате сортировки у​ на своём месте.​ сложно, правда? Однако,​Данные​

​ алфавитном порядке и​ ячейке, диапазону, формуле»​

​ Данный инструмент автоматическое​

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

​ кнопку ОК.​ «Имя». Такая задача​ «Премия» по убыванию​ том, чтобы отсортировать​ сделать с помощью​Совет:​Например, чтобы отсортировать данные​ статье Создание настраиваемого​ не будут отсортированы​

1. ​ в году. Microsoft​ Вас должно получиться​ Следовательно, выделяем диапазон,​ в диалоговом окне​(Data) или по​ по значениям в​ тут. Мы присвоили​
2. ​ сортирует данные одновременно​Таблица приобрела следующую структуру:​ решается так же​ сумм. Для решения​ в порядке уменьшения​ функций НАИМЕНЬШИЙ() и​ Короткий список, который содержит​ по дням недели​
3. ​ списка из имеющегося​ автоматически. Чтобы повторить​ Excel предлагает два​ что-то вроде этого:​ начиная с ячейки​Сортировка​ кнопке​ любой строке. Вы​ имя «Фамилии».​ по нескольким столбцам.​Сначала ее значения отсортированы​ просто:​ этой задачи воспользуемся​
4. ​ Все значения. Следовательно,​ СТРОКА() (см. файл​ значения «Высокий», «Средний»​ или месяцам года​ листа Excel.​ сортировку, нажмите кнопку​ типа таких готовых​В рассмотренном нами примере​B1​(Sort) кроется значительно​

​Настраиваемая сортировка​

​ также научитесь осуществлять​В ячейке В1​ Он настолько эластичен,​ относительно названий городов​Выделите целый столбец B​ самыми простыми (базовыми)​ при изменении данных,​

​ примера).​ и «Низкий», возможно,​ с помощью встроенного​При помощи настраиваемых списков​Повторить​ списков – с​ сортировка по заголовкам​:​

​ больше возможностей. Далее​(Custom sort) на​ сортировку данных нестандартными​ пишем такую формулу.​ что его можно​ в алфавитном порядке.​ щелкнув левой мышкой​ инструментами сортировки в​ сортировка должна также​Для сортировки от минимального​ проще ввести непосредственно​ настраиваемого списка, выполните​ можно сортировать по​(Reapply) в разделе​ сокращёнными и с​ столбцов не имеет​На вкладке​ в этой статье​ вкладке​ способами, когда сортировка​=ИНДЕКС(Фамилии;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Фамилии;» Для ввода​ применить практически для​ Далее относительного каждого​

​ по заголовку столбца​ Excel.​ меняться.​ к максимальному используем​

​ в поле​ следующие действия.​ нескольким столбцам, используя​

​Сортировка и фильтр​ полными названиями.​ серьёзной практической ценности​Данные​ я покажу, как​Главная​ в алфавитном порядке​ формулы нажимаем сочетание​ любой сортировки с​ города распределены даты​ листа.​Перейдите курсором клавиатуры на​Некоторые значения могут​ формулу (введите ее​Элементы списка​Выделите столбцы, которые нужно​ разные настраиваемые списки​(Sort & Filter)​Предположим, у нас есть​ и сделана только​(Data) нажимаем кнопку​ сортировать по строке,​(Home), чтобы открыть​ или по значению​ клавиш «Ctrl» +​ любым уровнем сложности.​ рождения от старых​Выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и​ любую ячейку в​ повторятся одинаковое число​

exceltable.com

​ в ячейку​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сортировка формулой

Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых — кнопки сортировки на вкладке или в меню Данные (Data — Sort) . Бывают, однако, ситуации, когда сортировку списка нужно делать автоматически, т.е. формулами. Такое может потребоваться, например, при формировании данных для выпадающего списка, при вычислении данных для диаграмм и т.д. Как же «на лету» сортировать список формулой?

Способ 1. Числовые данные

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

Функция НАИМЕНЬШИЙ (SMALL) выдергивает из массива (столбец А) n-й по счету наименьший элемент. Т.е. НАИМЕНЬШИЙ(A:A;1) — это самое маленькое число из столбца, НАИМЕНЬШИЙ(А:А;2) — второе по счету наименьшее и т.д.

Функция СТРОКА (ROW) выдает порядковый номер строки для указанной ячейки, т.е. СТРОКА(А1)=1, СТРОКА(A2)=2 и т.д. В данном случае она используется просто как генератор последовательности чисел n=1,2,3… для нашего отсортированного списка. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.

Способ 2. Текстовый список и обычные формулы

Если в списке не числа, а текст, то функция НАИМЕНЬШИЙ (SMALL) уже не сработает, поэтому придется пойти другим, чуть более длинным, путем.

Сначала добавим служебный столбец с формулой, где будет вычисляться порядковый номер каждого имени в будущем отсортированном списке с помощью функции СЧЁТЕСЛИ (COUNTIF) :

Сортировка данных в Excel по строкам и столбцам с помощью формул

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

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

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

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

1. Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
2. Открыть вкладку «Данные» — диалоговое окно «Сортировка».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. В диалоговом окне «Настраиваемой сортировки» нажать кнопку «Параметры».
2. В открывшемся меню выбрать «Столбцы диапазона».
3. Нажать ОК. В окне «Сортировки» появятся поля для заполнения условий по строкам.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как отсортировать по алфавиту

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

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

Как в Еxcel отсортировать по возрастанию значений

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

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

Как сделать сортировку по дате

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

Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:

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

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

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

1. Курсором мыши нужно выбрать одну из ячеек с данными в нужном столбце.
2. На вкладке «Главная» в функциональной группе «Редактирование» следует найти кнопку «Сортировка и фильтрация» и выбрать в дополнительном меню команду «Настраиваемая сортировка».
3. В открывшемся окне сначала указать столбец, в котором нужно упорядочить данные, а потом тип сортировки. Это может быть цвет ячейки, цвет шрифта или значок условного форматирования.
4. В зависимости от выбранного вида сортировки в группе порядок необходимо отметить нужный значок либо оттенок заливки или шрифта.
5. Последний параметр, который нужно указать – это последовательность расположения (сверху или снизу).

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

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

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

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

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

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

1. Если информация в ячейках столбца представлена числами, используются функции НАИМЕНЬШИЙ и СТРОКА. Первая находит наименьший элемент из массива, а вторая определяет порядковый номер строки. Таким образом формируется последовательность. Формула записывается следующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)).
2. Когда в ячейках содержаться текст, первая формула не сработает. Для сортировки в этом случае целесообразно применять формулу: =СЧЁТЕСЛИ(A:A;”

Как убрать сортировку в Excel

Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае?

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

[Excel] Динамическая сортировка

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

у меня сейчас нет под рукой, но в excel 2007 есть автофильтр по «лучшая пятерка» или т.п.
Проверьте, кто может

Проверьте, кто может это только в 2007?
в более старых нету?

да есть и в старых тоже. «типа первые 10..» а там можно уже настроить сколько. Но Автофильтр не динамическая хрень, если данные изменились, он не переформирует заново фильтр.

да есть и в старых тоже. «типа первые 10..» а там можно уже настроить сколько. Но Автофильтр не динамическая хрень, если данные изменились, он не переформирует заново фильтр. Можно легко.
Пиши е-мейл пример скину описывать влом.

оо
рулезз. то что надо .
мени сенкс

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

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

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

а чо макросы не нравится?

а чо макросы не нравится? Потому что они тут не нужны. Тру джедаи умеют все(почти) на формулах делать.
🙂
На самом деле макросы плохи тем, что если файлик предназначен для ламер пользователей, например калькулятор для расчет страховой премии КАСКО, то 90% агентов впадет в ступор если эксель спросит у них про уровень безопасности, а у остальных даже не спросит и просто работать не будет. А на формулах без проблем работает всегда и у всех. Для личных нужд нужно макросы использовать в тех случаях когда это удобней. Но например здесь точно на формулах попросту проще.

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

хех.
Оказывается куда проще можно.
Есть тупо формула НАИБОЛЬШИЙ(массив; k). Возвращает k-ое по величине значение из множества данных.
Есть еще одна задача. Если с повторениями. Только надо вернуться к исходным данным задачи. Надо не просто отсортировать исходный массив. а есть массив из двух столбцов.

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

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

ээ..
забыл сказать. Данные ессесно динамические. подкачиваются каждую минуту.

ээ..
забыл сказать. Данные ессесно динамические. подкачиваются каждую минуту. 5 макс значений с повторениями надо?
Или весь столбец упорядочить?

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

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

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

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

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

1. Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.



2. Открыть вкладку «Данные» — диалоговое окно «Сортировка».

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

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

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



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

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



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

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

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

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



3. Соглашаемся «автоматически расширить диапазон».

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

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

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

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

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

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

1. Открываем меню «Настраиваемая сортировка». Назначаем первый критерий.



2. Нажимаем кнопку «Добавить уровень».



3. Появляются окошки для введения данных следующего условия сортировки. Заполняем их.

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

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

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

1. В диалоговом окне «Настраиваемой сортировки» нажать кнопку «Параметры».



2. В открывшемся меню выбрать «Столбцы диапазона».



3. Нажать ОК. В окне «Сортировки» появятся поля для заполнения условий по строкам.

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

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

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

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

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

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

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

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

1. Есть набор простых чисел, которые нужно отсортировать по возрастанию.



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



3. Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

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

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

1. Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.



2. Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.



3. В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.



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

Скачать формулы сортировки данных в Excel

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

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

Вариант 1: Быстрая сортировка в Excel

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

1. Выделите зажатой левой кнопкой мыши столбец с данными и вызовите меню «Редактирование».

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

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

4. Изменения вступают в силу сразу же. Нажмите Ctrl + Z для отмены действия, если что-то выполнено неправильно.

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

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

Более продвинутый вариант, позволяющий задать сразу несколько условий упорядочения, – это настраиваемая сортировка, выполняющаяся через соответствующее окно. Давайте разберем сортировку по двум разным параметрам:

1. Вы можете не выделять заранее столбец, если для него задано название. Вызовите то же меню редактирования, но на этот раз нажмите по «Настраиваемая сортировка».

2. Выберите вариант с расширением или сохранением диапазона, если предварительно выделили столбец.

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

4. Выберите столбец для сортировки из первого выпадающего списка.

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

6. В завершение укажите, в каком порядке необходимо отсортировать данные.

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

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

Вариант 3: Функция НАИБОЛЬШИЙ или НАИМЕНЬШИЙ

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

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

2. Далее в скобках укажите (C:C;СТРОКА(C1)), поменяв буквы на номер столбца сортировки.

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

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

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