Excel сортировка по двум столбцам формула

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

​Смотрите также​​ диапазон A4:F12 и​ ​ И нажмите ОК.​ В параметрах отмечаем​​Application.ScreenUpdating = 1​ должен по каким-то​ фильтр работает не​ сортировки выберите в​ чтобы установить в​ нажимаем на кнопку​В столбце E введем​ более простой формулы массива,​ 2-3 секунды, что​ столбца Объемы продаж,​ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(​ помощью Условного форматирования​ — выбираем «По​Рассмотрим,​
​ повторно выполнить второй​Столбец отсортирован независимо от​
​ «значения» и нажимаем​End Sub​ признакам понимать: где​
​ корректно. Может есть​ первом поле столбец​
​ ней сортировку, фильтр,​ «Сортировка и фильтр».​
​ обычную формулу:​ но нам понадобится​
​ вызывает неудобства. Либо​ отсортированных по убыванию.​—(СЧЁТЕСЛИ(Продажи;» СТРОКА()-СТРОКА($E$6));1)*1000;0)​ (Главная/ Стили/ Условное​ возрастанию».​как настроить сортировку в​ пункт этого раздела,​ других столбцов таблицы.​ ОК.​
​Wasilich​ начинать и где​​ хитрая формула подскажите​ С, во втором​ т.д. О том,​ В появившемся списке​=СЧЁТЕСЛИ($D$6:D6;D7)+D7​
​ дополнительный (служебный) столбец​ необходимо отключить автоматический​Функция ИНДЕКС() по номеру​)​ форматирование/ Гистограммы). Также​Мы заполнили диалоговое​Excel по нескольким столбцам​ только указать розовый​При копировании таблицы на​
​Теперь наша таблица не​: А по моему,​ заканчивать данные. И​ как она выглядит.​ — столбец А,​ как быстро выделить​ выбираем функцию «Настраиваемая​Эта формула учитывает повторы​ D (см. файл​ пересчет листа (Формулы/​
​ позиции возвращает соответствующее​Данная формула сортирует столбец​​ желтым выделены повторяющиеся​
​ окно так.​ и строкам​ сверху. Таким образом​ отдельный лист мы​ содержит формул, а​ даже правильней чем​ если признаки ненароком​
​ Заранее спасибо​ нажмите ОК»​ нужную таблицу, смотрите​ сортировка». Появится такое​ текстовых значений и​ примера лист Пример2):​ Вычисления/ Параметры вычисления)​ ему число.​ Объем продаж (динамический​

excel-office.ru

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

​ значения.​Нажимаем «ОК». Получилось так.​, по нескольким условиям​ в первую очередь​ переносим только ее​ только результаты их​ в вашем примере.​ или намеренно изменились,​Wasilich​пс. я тихо​ в статье «Как​ диалоговое окно.​ корректирует «ранг». Теперь​=НАИБОЛЬШИЙ(ЕСЛИ(F7=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0);СЧЁТЕСЛИ($F$7:$F$14;F7)-СЧЁТЕСЛИ($F$6:F6;F7))​ и периодически нажимать​Аналогичную формулу можно написать​ диапазон Продажи) по​Примечание​Для лучшей видимости, мы​ сразу. Например, чтобы​ пойдут ячейки с​ значения с помощью​

​ вычисления. Так же​andreika21​ а макрос не​: И в чем​ офигеваю :(​ выделить одну таблицу​Ставим галочку у​

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

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

​ специальной вставки. Но​ разъединены объединенные ячейки.​:​ поправили — тоже​ проблема? Выделяем диапазон​tristan​ в Excel из​ слов «Мои данные​

​ соответствуют разные «ранги»​​ отсортированный столбец В​F9​ в столбец Фрукты​ исходной таблице не​ столбца (списка) решена​ Как их сделать​

Решение1

​ алфавиту не только​ обычные.​ возможности сортировки позволяют​ Осталось убрать лишний​

• ​_Boroda_​ не сахар.​ >Данные >Сортировка >По​: Простите господа, я​
• ​ нескольких».​ содержат заголовок».​ — 7 и​ (объем продаж). Формула​, либо отказаться от​

​ =ИНДЕКС(Фрукты;ОКРУГЛ(…))​ допускаются. Количество строк​ в статьях Сортированный​ автоматически, смотрите в​ в первом столбце,​voidex​ нам сортировать не​ текст в заголовках​, Спастбо за вариант​Думайте сами, решайте​ столбцам >Столбец В,C,D​ имел ввиду возможность​tristan​Нажимаем на кнопку​

​ 8. Это позволяет​ возвращает позицию значения​

​ использования формул массива,​В файле примера, из-за​ в исходной таблице​ список (ТЕКСТовые значения)​ статье «Разделительная линия​ а во всех​: Здравствуйте, есть хорошая​ только по значениям,​ и таблица готова​andreika21​ сами…​ >OK​

​ сделать Всё вышеперечисленное​​: День добрый, джентльмены.​​ «Параметры». В появившемся​ вывести список сортированных​

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

​ и Сортированный список​
​ строк в таблице​
​ столбцах. Или, чтобы​
​ функция для сортировки​

​ а даже по​ для безопасной сортировки.​:​andreika21​andreika21​ в VBA​Знаю, что сама​ окне ставим галочку​ значений. Для этого​

​ число 86 находится​

• ​ с соответствующими формулами,​ (см. ниже), однотипная​ 1000.​ (ЧИСЛОвые значения).​ Excel» тут.​ данные в третьем​ по нескольким столбцам:​​ цветам шрифта или​​Чтобы отсортировать всю таблицу​Wasilich​:​:​ShAM​ «Сортировка» тема тёртая​ у слов «Столбцы​ используйте формулу (столбец​ в 5-й строке​
• ​ либо вообще отказаться​ часть формулы, т.е.​Разберем формулу подробнее:​Если числовой столбец гарантировано​В Excel можно​ столбце с ценой​Sub ColorSort() Columns(«A:D»).sort​ цветам ячеек. Поэтому​ относительно одного столбца​, Вот это и​Perfect2You​Wasilich​: Включаете макрорекордер и​ не один раз.​ диапазона». Нажимаем «ОК».​ G):​ таблицы.​ от динамической сортировки​ все, что внутри​Формула СЧЁТЕСЛИ(Продажи;»B7 исходной таблицы,​
• ​ не содержит повторяющихся​ сортировать столбцы, перемещать​
• ​ расположились в порядке​ key1:=Range(«A2»), order1:=xlAscending, Header:=xlYes,​ нам нужно еще​ выполните следующее:​
• ​ было сделано сартировкой​, Я добовлял столбцы​, Надо не один​ вперед.​ По значениям одного​В строке «Сортировка​=ИНДЕКС($B$7:$B$14;ПОИСКПОЗ(СТРОКА()-СТРОКА($G$6);$E$7:$E$14;0))​Для повторов выражение ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0) будет​
• ​ в пользу использования​ функции ОКРУГЛ(), вынесена​ т.е. первое число​

​ значений, то задача​ их по алфавиту,​ возрастания, т. д.​ _ key2:=Range(«B2»), order1:=xlAscending,​

​ переносить и форматы​Выделите столбцы листа, которые​ с ошибкой​ с ошибками сортирует​ столбец а все​Юрий М​ столбца диапазон отсортировать​​ по» выбираем строку,​​Аналогичная формула выведет соответствующий​ возвращать несколько значений: {0:2:0:0:0:0:0:8},​ стандартных подходов (см.​ в отдельный столбец​

​ из диапазона Продажи)​ решается легко:​ по данным любой​ про азы сортировки​

​ Header:=xlYes _ ,​ данных. Для этого:​ охватывает исходная таблица.​Сортировка данных в Excel​ буду пробывать ваш​ сразу а когда​: Никто и не​

Тестируем

​ не проблема. Но​ по которой нужно​ объем продаж (столбец​ т.е. число 74​ следующий раздел).​

​J​​ больше 4-х значений​​Числовой столбец отсортировать функцией​ строки таблицы. Как​
​ читайте в статье​​ key3:=Range(«C2»), order1:=xlAscending, Header:=xlYes​​Вернемся к нашей исходной​Выберите инструмент на закладке:​
​ это очень полезная​ вариант заточить под​​ все сразу уже​​ догадывался, что нужно​ в моём случае​ провести сортировку. У​
​ Н).​ находится в строках​Отсортируем строки исходной таблицы​

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

​. Поэтому итоговые формулы​ из того же​ НАИБОЛЬШИЙ() (см. статью​ это сделать, смотрите​ «Сортировка в Excel».​ End SubНо в​ таблице на Лист1​ «Данные»-«Сортировка».​ функция, но пользоваться​ себя. Спасибо за​ с 3 столбца​ с помощью VBA​ сортировать надо сразу​ нас – это​Теперь снова отсортируем исходную​​ 2 и 8.​​ с помощью стандартного​ в сортированной таблице​ диапазона; число 74​ Сортированный список (ЧИСЛОвые​ в статье «Сортировка​У нас такая​ ней есть минус,​ и снова полностью​В появившимся окне укажите​

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

​ ней следует с​ помощь​ сортирует не правильно​ :-)​ по двум столбцам.​​ строка 27 с​​ таблицу по Объему​С помощью функции НАИБОЛЬШИЙ() сначала​

​ фильтра (выделите заголовки​ выглядят так: =ИНДЕКС(Фрукты;J7)​ (из ячейки​ значения));​ в Excel по​

​ таблица.​ максимум 3 уровня​ выделим ее, чтобы​ параметры сортировки. В​ осторожностью. Если большая​_Boroda_​Perfect2You​Что говорит по​ Одновременно. Сортируется первый​ названиями заголовков столбцов.​ продаж. Но теперь​

​ выводится 2, затем​ исходной таблицы и​ и =ИНДЕКС(Продажи;J7)​B8​Функцией ВПР() или связкой​

​ столбцам».​Нам нужно отсортировать все​ сортировки, как сделать​ скопировать.​ первую очередь поставьте​ таблица содержит сложные​:​: Сортировкой можно (благодаря​ этому поводу макрорекордер?​ столбец и одновременно,​Нажимаем «ОК». Получилось так.​

​ для повторяющихся значений​ 8 (в разных​ нажмите​

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

​Также, изменив в формуле​исходной таблицы, т.е.​ функций ИНДЕКС()+ПОИСКПОЗ() выбрать​Отсортируем формулами таблицу, состоящую​ данные по всем​ больше не изобретая​Правой кнопкой мышки щелкните​

​ галочку напротив: «Мои​

​ формулы и функции,​andreika21​ тому, что генерация​Z​ в зависимости от​Второй вариант.​ (в столбце А​ строках).​

​CTRL+SHIFT+L​ массива функцию НАИБОЛЬШИЙ()​ второе число из​ значения из текстового​ из 2-х столбцов.​

​ трем столбцам:​ велосипед?​ по ячейке A1​ данные содержат заголовки​

​ то операцию сортировки​, посмотрите такой вариант​ не формульная).​: Так оно и​ значения в первом,​

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

​Сортировка по нескольким столбцам​ три значения 74),​Эта формула более наглядна,​

​). В выпадающем списке​​ на НАИМЕНЬШИЙ() получим​ диапазона Продажи) больше​ столбца по соответствующему​ Сортировку будем производить​Магазины – по​

​Апострофф​ на копии таблицы​ столбцов», а потом​ лучше выполнять на​ сразу макросом сортировку​Если по нескольким​

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

​ чем рассмотренная выше​ выберите требуемую сортировку.​ сортировку по возрастанию.​

​ 5-и значений из​

​ ему числовому значению.​ по одному из​ алфавиту;​:​ на третьем листе​ указываем следующие параметры:​ копии этой таблицы.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub CommandButton1_Click()​ столбцам (У Вас,​ вы не видите!..​ строки («А»+»В»+»С») значения​

​Excel по любой строке​ в алфавитном порядке.​

​ в начале статьи,​

​Получим идентичный нашему вариант​Для наглядности, величины значений​ того же диапазона;​Однако, в реальных задачах​ столбцов таблицы (решим​Наименование – по​voidex​ (Лист3) и выберите​ «Столбец» – Чистая​Во-первых, в формулах и​Dim arSl, Diap​

​ я смотрю, так,​

​ Тем более, что​ не меняют. Просто​ таблицы.​

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

​Для этого воспользуемся результатами​ но требует наличия​ таблицы, но при​ в столбце Объем​ следующее число 23​ числовой столбец может​ 2 задачи: сортировка​ алфавиту;​

​, начиная с 2007​ опцию «Специальная вставка»-«значения».​ прибыль; «Сортировка» –​

​ функциях может нарушиться​ As Range​

excel2.ru

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

​ то и по​ один макрос можно​ меняют местоположение. Грубо​Нам нужно отсортировать​ Задачи 1.1 и​ дополнительного столбца.​​ добавлении в таблицу новых​ ​ Продаж выделены с​ — самое маленькое​ ​ содержать повторы, а​​ таблицы по числовому​Цены – по​ офиса (кажется?) в​Повторно делаем щелчок правой​ Значения; «Порядок» –​ адресность в ссылках​Application.ScreenUpdating = 0​ нескольким она налаживается).​
​ настроить на ТРИ​ говоря строки надо​
​ столбцы по строке​
​ Задачи 2.​ ​Отсортируем строки таблицы по​
​ значений придется применять​ помощью Условного форматирования (Главная/​ (оно никого не​ так как функция​
​ и сортировка по​ возрастанию.​ Excel есть более​ кнопкой мышки по​ По убыванию. И​ и тогда результаты​Range(«B40:M199»).ClearContents​Формулами тоже можно.​ «железных» поля… А​ просто «перетасовать».​​ 29 «Цена».​Подробности в файле примера​ содержимому Текстового столбца​
​ фильтр заново.​ Стили/ Условное форматирование/​ больше) и т.д.​ ВПР() в случае​ текстовому столбцу). Формулы​
​Ставим курсор на​ универсальный метод сортировки​ ячейе A1 на​ нажмите ОК.​ их вычислений будут​Randomize​ В файле приложил.​
​ после обеда вам​
​Я файл приложил​
​Внимание!​ на листе Задача2.​ ​ (по Фруктам).​Также можно воспользоваться инструментом​
​ Гистограммы). Как видно,​Теперь вышеуказанный массив целых​ наличия повторов всегда​
​ сортировки настроим так,​
​ любую ячейку таблицы.​ по более чем​ листе 3 и​Данные отсортированные по всей​ ошибочны. Во-вторых, после​r_ = 33​andreika21​ потребуется ТРИ ДРУГИХ​ — там всё​У нас сортировка​Все знают сортировку​Примечание​ Сортировка (Данные/ Сортировка​ сортировка работает.​ чисел превратим в​ выбирает только первое​
​ чтобы при добавлении​ На закладке «Главная»​ трем столбцам.​ повторно выберем «Специальная​ таблице относительно столбца​ многократных сортировок можно​r0_ = 40​:​ — и что​​ наглядно. Буду благодарен​
​ настроена на «Столбцы​ в Excel по​: Про сортировку списка​ и Фильтр/ Сортировка).​Теперь добавим новую строку​ массив чисел с​ значение сверху (см.​ новых данных в​ в разделе «Редактирование»​В более ранних​ вставка» только на​ «Чистая прибыль».​ перетасовать данные таблицы​c0_ = 2​

excel-office.ru

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

​ таблица изменялась динамически.​​ «Сортировка и фильтр».​ сделать в несколько​ «форматы». Так мы​
​Теперь отсортируем только один​

​ сложно будет вернуться​​ Columns.Count).End(xlToLeft).Column​ когда полная таблица​ у вас немало,​ полезную тему.​

​ не годится (названия​​ иметь отсортированную таблицу​ выбираем функцию «Настраиваемая​ с конца по​ формул но со​

​ к другим столбцам​​ виду. В третьих,​ = Cells(r_, c0_)​

​ не на своих​​ макросы писать для​: Может все-же не​ не можем поставить​сортировку в​
​Как и в предыдущей​ инструмент Сортировка, выбрать​

​1. В ячейку​​ в массиве: {4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008}.​ Фруктов будут выведены​ без вмешательства пользователя.​ сортировка».​ весу значений столбцов.​ значениями и форматами​ и целой таблицы:​ если таблица содержит​nc_ = c1_​ местах и требуеться​ себя вы ленитесь,​ одновременно, а по​ там галочку. Чтобы​Excel по столбцам, сортировку​ задаче предположим, что​ столбец, по которому​А15​

​ Это реализовано выражением​​ неправильно).​ Также сделаем двухуровневую​Появится такое диалоговое​Т.е. как-то так​Разъедините все объединенные ячейки​Выделите диапазон значений столбца​ объединенные ячейки, то​ — c0_​ чтобы работало по​ почему?!. -45427-​ очереди?​ это исправить, сначала​ столбцов в​ в столбце, по​

planetaexcel.ru

Сортировка по нескольким столбцам (Формулы/Formulas)

​ требуется сортировать и​​исходной таблицы введите​ &»,»&ПОВТОР(«0»;3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6)) Именно в​Поэтому механизм сортировки придется​ сортировку: сначала по​ окно.​ — Sub ColorSort()​ (если такие присутствуют).​ который следует отсортировать,​ следует их аккуратно​ReDim arSl(1 To​ нажатию кнопки был​

​tristan​​См. «настраиваемая сортировка»​ поставьте в окне​Excel по алфавиту​ которому ведется сортировка​ вариант сортировки.​

​ слово Морковь;​​ этой части формулы​​ реализовывать по другому.​​ числовому, затем (для​«Сортировать по» — выбираем​ Columns(«A:F»).sort key1:=Range(«D2»), order1:=xlAscending,​Теперь копия таблицы содержит​ например «Расход» (в​ разъединить, так как​

​ nr_, 1 To​​ результат без лишних​: Ладно, ладно хорош​KuklP​
​ «Параметры сортировки» галочку​, т.д., как менять​ имеются повторы (названия​Получим идентичный нашему вариант​2. В ячейку​
​ заложено ограничение о​Создадим для удобства 2​

​ повторяющихся чисел) -​​ название первого столбца​​ Header:=xlYes, _ key2:=Range(«E2»),​​ значения и форматы.​ данном случаи это​ для сортировки такой​ 1)​ тело движений. Спасибо​ нападать. Прошу прощения,​: Это прикол такой?​ у слов «Строки​ местами, передвигать столбцы.​ Фруктов повторяются).​ таблицы, но при​В15​

​ не более 1000​​ Динамических диапазона Фрукты​ по текстовому столбцу.​ «Магазин». Порядок сортировки​ order1:=xlAscending, Header:=xlYes Columns(«A:F»).sort​ Выполним сортировку по​ диапазон E1:E11).​ формат является не​
​For i =​ за фурмулу попробую​ не знал, что​ Кто не дает?​ диапазона». Нажмите «ОК».​ Что такое «сортировка»,​
​Для сортировки таблицы придется​ добавлении новых значений​введите Объем продаж​ строк в исходной​ и Продажи, которые​Пусть имеется таблица, состоящая​ нам нужен «От​ key1:=Range(«A2»), order1:=xlAscending, Header:=xlYes,​ цветам:​Щелкните правой кнопкой мышки​ приемлемым.​
​ 1 To nc_​ дописать на всю​

​ в Excel-е встроенная​​ Прикрепленные файлы post_352245.gif​​ Функция «Мои данные​​ где находится эта​ создать 2 служебных​ также придется применять​ Моркови = 25;​ таблице (см. выше).​ будут ссылаться на​

​ из 2-х столбцов.​​ А до Я».​​ _ key2:=Range(«B2»), order1:=xlAscending,​​Выделите таблицу и выберите​ по выделенному столбцу.​
​Какими средствами располагает Excel​
​x_ = Cells(r_,​ таблицу​
​ функция позволяет сортировать​
​ (34.42 КБ)​
​ содержат заголовки» снова​
​ функция, как ее​
​ столбца (D и​
​ фильтр заново.​
​3. После ввода​ При желании его​
​ диапазоны ячеек, содержащие​ Один столбец –​
​В этом диалоговом​ Header:=xlYes _ ,​
​ инструмент «Данные»-«Сортировка».​ В контекстном меню​ для сортировки данных?​
​ c0_ + i)​Perfect2You​
​ по нескольким столбцам.​ikki​
​ станет активной.​ использовать, смотрите в​
​ E).​При использовании Таблиц в​ значений, в столбцах​
​ можно легко изменить,​
​ значения в соответствующих​ текстовый: Список фруктов;​ окне нажимаем на​
​ key3:=Range("C2"), order1:=xlAscending, Header:=xlYes​В параметрах сортировки снова​ выберите опцию «Сортировка»-«от​ Чтобы дать полный​For j =​
​: Сортировкой по одному​
​ Теперь буду знать.​
​: это ж надо!​

​Теперь настраиваем сортировку​​ статье «Сортировка в​В столбце D введем​ формате EXCEL2007 мы​

​D Е​​ но это бессмысленно​​ столбцах исходной таблицы.​​ а второй -​

​ кнопку «Добавить уровень».​​ End Sub​​ отмечаем галочкой «Мои​​ минимального к максимальному»​ ответ на этот​ 1 To nr_​

excelworld.ru

Как сделать сортировку в Excel по возрастанию и по убыванию

​ столбцу — возможно.​ Запишу через макрорекордер​ :)​ по столбцам, как​ Excel».​ формулу массива, возвращающую​ также не получим​автоматически будет отображена​ (см. ниже раздел​ При добавлении новых​

​ числовой Объем Продаж​ Появится новая строка.​Не по теме:​ данные содержат заголовки​Появится диалоговое окно «Обнаруженные​ вопрос рассмотрим его​arSl(j, 1) =​ Но в сортировке​ и ограничу диапазон.​писать длинный пост,​ в первом случае.​Итак, у нас​ несколько значений​ динамической сортировки. Новое​ отсортированная по убыванию​ о скорости вычислений).​ строк в таблицу,​ (см. файл примера).​ Настраиваем эту строку​

Сортировка данных в Excel

​Раздел для тем выбирайте​ столбцов» и указываем:​ данные вне указанного​ на конкретных примерах.​ Int(Rnd() * x_)​ можно добавлять дополнительные​

​ Тут я действительно​ рисовать пример и​ Только выбираем не​

1. ​ такая таблица.​=СЧЁТЕСЛИ($B$7:$B$14;»​
2. ​ значение (Картофель) останется​ таблица;​Функция НАИБОЛЬШИЙ() сортирует вышеуказанный​ границы этих динамических​Необходимо отсортировать строки таблицы​ на сортировку второго​ внимательнее.​ «Столбец» – Чистая​ диапазона». По умолчанию​

​Подготовка таблицы для правильной​ + 1​ столбцы. И тогда​ сам справлюсь. Всем​ вручную сортировать его​ верхнюю строку, а​Первый вариант.​Эта формула является аналогом​ последним в исходной​

​4. В сортированной​ массив.​ диапазонов будут автоматически​

1. ​ по содержимому числового​ столбца «Наименование» -​
2. ​voidex​ прибыль; «Сортировка» –​
3. ​ там активна опция​ и безопасной сортировки​Next j​ результат оправдает Ваши​ Спасибо Большое за​ как надо, писать​ нужную. Мы поставили​Сортировка столбцов в​ ранга для текстовых​ таблице (до принудительной​ таблице новая строка​Функция ОСТАТ() возвращает дробную​

​ расширяться.​ столбца (по Объему​ «От А до​

​: Спасибо, как я​

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

​ Цвет ячейки; «Порядок»​ «автоматически расширять выделенный​ данных:​Cells(r0_, c0_ +​

1. ​ ожидания.​ наводку. Тему можно​ пояснения к примеру​ строку 29 (Цена).​Excel по алфавиту.​
2. ​ значений (позиция значения​ сортировки таблицы через​ будет отображена предпоследней.​ часть числа, представляющую​В столбцах ​
3. ​ продаж). Предполагается, что​ Я».​ сам не подумал​ – красный, сверху.​ диапазон». Программа пытается​Выделяем и копируем всю​ i — 1).Resize(nr_,​Формулы — конечно,​ закрывать.​ и раскрашивать в​ Диалоговое окно «Сортировка»​Нам нужно разместить​ относительно других значений​ фильтр), не смотря​

​На «среднем» по производительности​ собой номера позиций/1000,​

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

​D E​ пользователь постоянно заполняет​Нам нужно отсортировать​ об этом​ И нажмите ОК.​ охватить все столбцы​ таблицу.​ 1).Value = arSl​ хорошо, но за​andreika21​ радостный канареечный цвет​ заполнили так.​ столбцы в таблице,​ списка). Текстовому значению,​

1. ​ на его значение​ компьютере пересчет пары​ например 0,005.​разместим таблицу, которая​ строки таблицы, поэтому​
2. ​ данные трех столбцов,​И тему да,​Сверху у нас теперь​ и выполнить сортировку​На другом чистом листе​Cells(r0_, c0_ +​
3. ​ ними следить надо,​: Здравствуйте, надеюсь на​ — и всё​Нажимаем «ОК». Получилось так.​ чтобы названия столбцов​ расположенному ниже по​ продаж (200).​ таких формул массива,​Функция ОКРУГЛ(), после умножения​ будет динамически сортироваться, ​ необходимо написать формулы​
4. ​ п. э. нажимаем​ совсем не там​

​ наихудшие показатели по​ как в предыдущем​ (например, Лист2)щелкаем правой​ i — 1).Resize(nr_,​

1. ​ чтоб не съехали​ ваши безграничные знания.​
2. ​ только для того,​Если в Excel на​ разместились по алфавиту.​ алфавиту, соответствует больший​Какой вариант предпочтительней -​ расположенных в 100​ на 1000, округляет​В ячейке​ с учетом добавляемых​

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

​ кнопкой мышки по​ 1).Sort (Cells(r0_, c0_​ и чтобы весь​ В таблицу генерируются​ чтобы получить ответ:​ одной странице расположено​Ставим курсор на​ «ранг». Например, значению​ как всегда -​ строках, практически не​ до целого и​

exceltable.com

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

​Е7​​ значений.​ уровень». В новой,​Апострофф​
​ имеют наихудшие показатели.​ этот раз выберите​ ячейке A1. Из​ + i -​ диапазон охватывали.​ случайные числа, после​»это стандартная возможность​ несколько таблиц, то​ любую ячейку таблицы.​ Яблоки соответствует максимальный​ выбирать разработчику.​

​ заметен. Для таблиц​​ возвращает номер позиции.​​запишем зубодробительную формулу​​Для наглядности величины значений​ третьей, строке (третьем​: Кстати, достаточно указать​Примечание. Дальше можно выделить​ опцию «сортировать в​ контекстного меню выбираем​
​ 1).Resize(nr_, 1))​Макрос — те​ генерации надо отсортировать​ екселевской сортировки. выделите​ есть способ выделить​ На закладке «Главная»​
​ «ранг» 7 (с​Сортировку таблицы можно сделать​ с 300 строками​ Теперь все номера​ массива:​ в столбце Объем​ уровне) выбираем название​ один раз, кажется?​ в этой таблице​ пределах указанного диапазона».​

​ опцию: «Специальная вставка».​

​Next i​ же сложности. Он​

​ построчно от наименьшего,​​ диапазон, в окне​ одну, конкретную таблицу,​ в разделе «Редактирование»​
​ учетом повторов).​ с помощью другой,​ время пересчета занимает​

​ позиций соответствуют числам​​=ИНДЕКС(Продажи;​ Продаж выделены с​

CyberForum.ru

​ столбца «Цена». «Порядок»​

Сортировка данных в 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

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

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

Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых — кнопки сортировки на вкладке или в меню Данные (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):

В английской версии это будет:

=COUNTIF(A:A,»<«&A1)+COUNTIF($A$1:A1,»=»&A1)

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

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

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

Функция ПОИСКПОЗ (MATCH) ищет в столбце В нужный порядковый номер (1, 2, 3 и т.д.) и выдает, по сути, номер строки, где находится это число. Функция ИНДЕКС (INDEX) вытаскивает из столбца А имя по этому номеру строки.

Способ 3. Формула массива

Этот способ представляет собой, по сути, тот же алгоритм расстановки, что и в Cпособе-2, но реализованный формулой массива. Для упрощения формулы диапазону ячеек С1:С10 было дано имя List (выделить ячейки, нажать Ctrl+F3 и кнопку Создать):

В ячейку Е1 копируем нашу формулу:

=ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; «<«&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; «<«&List); 0))

Или в англоязычной версии:

=INDEX(List, MATCH(SMALL(COUNTIF(List, «<«&List), ROW(1:1)), COUNTIF(List, «<«&List), 0))

и нажимаем Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Потом полученную формулу можно скопировать вниз на всю длину списка.

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

Во-первых, диапазон List нужно будет задать динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все имеющиеся значения независимо от их количества. Нажмите Alt+F3 или откройте вкладку Формулы — Диспетчер имен (Formulas — Name Manager), создайте новое имя и в поле Ссылка (Reference) впишите вот такую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=OFFSET(C1,0,0,СЧЁТЗ(C1:C1000),1)

Во-вторых, вышеописанную формулу массива нужно будет протянуть вниз с запасом — с расчетом на вводимые в будущем дополнительные данные. При этом формула массива начнет выдавать ошибку #ЧИСЛО на незаполненных пока ячейках. Чтобы ее перехватить, можно использовать функцию ЕСЛИОШИБКА, которую нужно дописать «вокруг» нашей формулы массива:

=ЕСЛИОШИБКА(ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; «<«&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; «<«&List); 0));»»)

=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<«&List), ROW(1:1)), COUNTIF(List, «<«&List), 0));»»)

Она перехватывает ошибку #ЧИСЛО и выводит вместо нее пустоту (пустые кавычки).

Ссылки по теме:

• Сортировка диапазона по цвету
• Что такое формулы массива и зачем они нужны
• Сортировка функцией СОРТ и динамические массивы в новом Office 365

Отсортируем формулами таблицу, состоящую из 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.

Общая формула

=SORTBY(data,col1,order,col2,order)

Резюме

Для сортировки по двум столбцам вы можете использовать функцию SORTBY. В показанном примере данные сначала сортируются по столбцу «Группа» в порядке возрастания, затем по столбцу «Оценка» в порядке убывания. Формула в F5:

=SORTBY(B5:D14,D5:D14,1,C5:C14,-1)

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

Объяснение

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

• массив = B5: D14
• by_array1 = D5: D14
• sort_order1 = 1

Приведенная ниже формула сортирует данные по группе AZ:

=SORTBY(B5:D14,D5:D14,1) // sort by group only

Чтобы расширить формулу для сортировки следующего по количеству очков в порядке убывания, нам нужно добавить:

• by_array2 = C5: C14
• sort_order2 = -1

С добавлением этих аргументов полная формула выглядит так:

=SORTBY(B5:D14,D5:D14,1,C5:C14,-1)

По возрастанию и по убыванию

По умолчанию данные отсортированы по возрастанию (AZ). Это поведение контролируется аргументами sort_order. Sort_order может иметь значение 1 (по возрастанию) или -1 (по убыванию). Приведенная ниже формула такая же, как и выше, за исключением того, что она сортирует оценки в порядке возрастания:

=SORTBY(B5:D14,D5:D14,1,C5:C14,1)