|
karnager  Пользователь Сообщений: 17 |
Всем привет. Подскажите плз, как можно отсортировать в Excel столбцы по значениям. На примере идет много 1 1 1 1, затем 2 2 2 2, далее 3 3 3 3 и т.д. Буду очень признателен. Спасибо! |
|
JayBhagavan  Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
#2 16.10.2016 19:10:30
<#0> |
||
|
karnager Пользователь Сообщений: 17 |
Приложил. Спасибо Прикрепленные файлы
|
|
gling  Пользователь Сообщений: 4024 |
Можно доп столбцом попробовать. Прикрепленные файлы
|
|
karnager Пользователь Сообщений: 17 |
Сложновато, так как столбец с цифрами привязан еще и к фамилиям. |
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
=ИНДЕКС($A$2:$A$16;ОТБР((СТРОКА()-СТРОКА($1:$1)-1)/5)+1+3*(ОСТАТ((СТРОКА()-СТРОКА($1:$1)-1);5))) <#0> |
|
karnager Пользователь Сообщений: 17 |
Спасибо, но это не совсем то… Вот скидываю более понятный файл: у меня привязка этих цифр еще к другим столбцам. И мне надо отсортировать 1 2 3 1 2 3 1 2 3 вместе со значениями (и таких 751 строка) |
|
Как вариант сначала использовать Условное форматирование. С помощью него задать заливку ячейкам по цвету (Например, ячейки с «1» будут желтые. Потом через фильтр их будет просто расставить |
|
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
=ИНДЕКС($B$1:$B$26;ОТБР((СТРОКА()-1)/СУММПРОИЗВ(—(ПОИСКПОЗ($B$1:$B$26;$B$1:$B$26;0)=СТРОКА($B$1:$B$26))))+1+СЧЁТЕСЛИ($B$1:$B$26;$B1)*ОСТАТ((СТРОКА()-1);СУММПРОИЗВ(—(ПОИСКПОЗ($B$1:$B$26;$B$1:$B$26;0)=СТРОКА($B$1:$B$26))))) <#0> |
|
gling Пользователь Сообщений: 4024 |
Не понятно чем мешает доп столбец, тем более что его можно скрыть. Прикрепленные файлы
|
|
karnager Пользователь Сообщений: 17 |
#11 16.10.2016 20:20:01
Каким образом? Вы предлагаете 751 цифру цветом отформатировать вручную? |
||
|
_Igor_61  Пользователь Сообщений: 3007 |
Здравствуйте, karnager, |
.|
karnager Пользователь Сообщений: 17 |
#13 16.10.2016 20:23:51
Вы решаете обратную задачу. Я не понимаю вашей идеи — вы берете меняете местами столбцы, но при этом не учитываете другие (с текстом). Я таким образом могу и сам вручную просто от 1 до 3 размножить, а потом еще несколько раз. Остальные столбцы не поменяли сортировку Изменено: karnager — 16.10.2016 20:24:11 |
||
|
_Igor_61 Пользователь Сообщений: 3007 |
#14 16.10.2016 20:24:51
Вот, о чем и был вопрос — 751 цифра или 751 строка? |
||
|
karnager Пользователь Сообщений: 17 |
#15 16.10.2016 20:25:22
Спасибо за развернутый ответ) Но я не понял идею с дополнительными столбцами. Там одинаковое количество — везде по 9. Одну строку забыл. А всего 751 строка и так 10 раз. 1 …. 751 Изменено: karnager — 16.10.2016 20:54:06 |
||
|
karnager Пользователь Сообщений: 17 |
#16 16.10.2016 20:26:29
1 цифра = 1 ячейка = 1 строка) |
||
|
_Igor_61 Пользователь Сообщений: 3007 |
В доп.столбцах можно сделать уникальные значения — связать, напр. «1» с правой или с левой ячейкой, а потом |
|
gling Пользователь Сообщений: 4024 |
Закрасил строки для большего понимания. Для сортировки выделяете весь диапазон 4 столбца, заходите в настраиваемую сортировку, первый уровень сортируете по D второй уровень по B. Вернуть все назад можно если сортировать только по В. Срыть столбец можно нажав [-] над столбцом Е. Может я что не так понял? Прикрепленные файлы
Изменено: gling — 16.10.2016 20:43:59 |
|
karnager Пользователь Сообщений: 17 |
Значения B от А до Я. Тогда будет 1, 10, 11 и т.д. А не по порядку. Или я ошибаюсь? |
|
gling Пользователь Сообщений: 4024 |
Мне не понятно, возвращать назад надо или нет. Если хотите сортировать по столбцу В, после выбора от А до Я выйдет табличка сортировать числа как числа или как текст, выберите нужный вариант. Изменено: gling — 16.10.2016 22:49:25 |
|
_Igor_61 Пользователь Сообщений: 3007 |
Вот, как-то так, вроде все работает. Изменено: _Igor_61 — 16.10.2016 22:47:51 |
|
_Igor_61 Пользователь Сообщений: 3007 |
Сортировка — имеется ввиду столбец «F» в моем примере, формулы не вставлял, т.к. они были в нескольких предыдущих сообщениях, |
|
vikttur Пользователь Сообщений: 47199 |
Предложите кто-нибудь название темы по задаче. Сортировка значений — очень общее понятие |
|
_Igor_61 Пользователь Сообщений: 3007 |
Может, что-то вроде «Распределение числовых значений в определенном столбце в указанном порядке и последующий поиск значений из других столбцов на основе полученных отсортированных» — уф, что-то сильно длинно получается, но что еще придумать — не могу сообразить. |
|
gling Пользователь Сообщений: 4024 |
Может так: «Сортировка таблицы, с учетом групп данных внутри» или «Сортировка групп данных, внутри таблицы» Изменено: gling — 17.10.2016 18:07:02 |
|
vikttur Пользователь Сообщений: 47199 |
#26 17.10.2016 10:40:55 Спасибо. Автору — замечание. |
Сортировка данных — неотъемлемая часть их анализа. Вам может потребоваться расположить имена в списке по алфавиту, составить список складских запасов и отсортировать его по убыванию или упорядочить строки по цветам или значкам. Сортировка данных помогает быстро визуализировать данные и лучше понимать их, упорядочивать и находить необходимую информацию и в итоге принимать более правильные решения.
Сортировать данные можно по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым) в одном или нескольких столбцах. Можно также выполнять сортировку по настраиваемым спискам, которые создаете вы сами (например, списку, состоящему из элементов «Большой», «Средний» и «Маленький»), или по формату, включая цвет ячеек и цвет шрифта, а также по значкам.
Примечания:
-
Для поиска наибольших и наименьших значений в диапазоне ячеек или таблице, например 10 самых высоких оценок или 5 самых низких сумм продаж, можно использовать функцию «Автофильтр» или условное форматирование.
-
Дополнительные сведения см. в статьях Фильтрация данных в таблице или диапазоне Excel и Применение условного форматирования в Excel.
Сортировка текстовых значений
-
Выделите ячейку в столбце, который требуется отсортировать.
-
На вкладке Главная в группе Сортировка и фильтр выполните одно из указанных ниже действий.
-
Чтобы быстро отсортировать значения по возрастанию, нажмите кнопку
(Сортировка от А до Я). -
Чтобы быстро отсортировать значения по убыванию, нажмите кнопку
(Сортировка от Я до А).
-
(Сортировка от А до Я).
(Сортировка от Я до А).Примечания:
Возможные проблемы
-
Убедитесь, что все данные сохранены в текстовом виде. Если столбец, который подлежит сортировке, содержит числа, сохраненные в числовом формате, и числа, сохраненные в текстовом формате, необходимо отформатировать все ячейки либо как числа, либо как текст. В противном случае числа, сохраненные в числовом формате, после сортировки окажутся выше чисел, сохраненных в текстовом формате. Для преобразования всех выбранных данных в текстовый формат нажмите CTRL+1, чтобы открыть диалоговое окно Формат ячеек, откройте вкладку Число и в разделе Категория выберите вариант Общий, Число или Текст.
-
Удалите все начальные пробелы. Иногда импортируемые из других приложений данные могут дополняться начальными пробелами. Прежде чем выполнять сортировку, удалите их из данных. Это можно сделать вручную или с помощью функции СЖПРОБЕЛЫ.
-
Выделите ячейку в столбце, который требуется отсортировать.
-
На вкладке Главная в группе Сортировка и фильтр выполните одно из указанных ниже действий.
-
Для сортировки чисел по возрастанию нажмите кнопку
(Сортировка от минимального к максимальному). -
Для сортировки чисел по убыванию нажмите кнопку
(Сортировка от максимального к минимальному).
-
Примечания:
-
Возможные проблемы
-
Убедитесь, что все числа сохранены в числовом формате. Если результаты отличаются от ожидаемых, возможно, столбец содержит числа, сохраненные в текстовом формате. Например, отрицательные числа, импортируемые из некоторых систем бухгалтерского учета, или числа, содержащие начальный символ апострофа (‘), сохраняются в текстовом виде. Дополнительные сведения см. в разделе Преобразование чисел из текстового формата в числовой.
-
Выделите ячейку в столбце, который требуется отсортировать.
-
На вкладке Главная в группе Сортировка и фильтр выполните одно из указанных ниже действий.
-
Чтобы отсортировать значения по возрастанию, щелкните
(Сортировка от старых к новым). -
Чтобы отсортировать значения от поздних дат или значений времени к ранним, нажмите кнопку
(Сортировка от новых к старым).
-
Примечания:
Возможные проблемы
-
Убедитесь, что все значения даты и времени сохранены в формате даты и времени. Если результаты отличаются от ожидаемых, возможно, столбец содержит значения даты и времени, сохраненные в текстовом формате. Чтобы значения даты и времени сортировались в Excel правильно, они должны быть сохранены как порядковые числа с информацией о дате или времени. Если Excel не может распознать значение как дату или время, оно сохраняется как текст. Дополнительные сведения см. в статье Преобразование дат из текстового формата в формат даты.
-
Для сортировки по дням недели измените формат ячеек таким образом, чтобы в них выводились дни недели. Для сортировки по дням недели независимо от даты преобразуйте их в текст с помощью функции ТЕКСТ. Однако функция ТЕКСТ возвращает текстовое значение, поэтому сортировка будет выполнена по алфавитно-цифровым данным. Дополнительные сведения см. в статье Отображение дат в виде дней недели.
Возможно, вы захотите выполнить сортировку по двум или нескольким столбцам или строкам, чтобы сгруппировать данные с одинаковыми значениями в одном столбце или строке, а затем отсортировать эти группы с одинаковыми значениями по другому столбцу или строке. Например, если есть столбцы «Отдел» и «Сотрудник», можно сначала выполнить сортировку по столбцу «Отдел» (для группировки всех сотрудников по отделам), а затем — по имени (для расположения имен сотрудников каждого отдела в алфавитном порядке). Можно одновременно выполнять сортировку по 64 столбцам.
Примечание: Для получения наилучших результатов в сортируемый диапазон нужно включить заголовки столбцов.
-
Выделите любую ячейку в диапазоне данных.
-
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
-
В диалоговом окне Сортировка в группе Столбец в поле Сортировать по выберите первый столбец, по которому необходимо выполнить сортировку.
-
В поле Сортировка выберите тип сортировки. Выполните одно из следующих действий:
-
Чтобы выполнить сортировку по текстовым значениям, числам или значениям даты и времени, выберите пункт Значения.
-
Чтобы выполнить сортировку по формату, выберите вариант Цвет ячейки, Цвет шрифта или Значок ячейки.
-
-
В разделе Порядок выберите порядок сортировки. Выполните одно из следующих действий.
-
Для текстовых значений выберите От А до Я или От Я до А.
-
Для числовых значений выберите По возрастанию или По убыванию.
-
Для дат и времени выберите От старых к новым или От новых к старым.
-
Чтобы выполнить сортировку по настраиваемому списку, выберите пункт Настраиваемый список.
-
-
Для добавления к сортировке следующего столбца нажмите кнопку Добавить уровень, а затем повторите шаги с третьего по пятый.
-
Чтобы скопировать сортируемый столбец, выделите его и нажмите кнопку Копировать уровень.
-
Чтобы удалить столбец для сортировки, выделите соответствующую запись и нажмите Удалить уровень.
Примечание: В списке необходимо оставить хотя бы одну запись.
-
Чтобы изменить порядок сортировки столбцов, выделите запись нужного столбца и щелкните стрелку Вверх или Вниз рядом с кнопкой Параметры.
Столбцы с более высокой позицией в списке будут отсортированы раньше, чем столбцы с более низкой позицией.
Если диапазон ячеек или столбец таблицы был отформатирован вручную или с помощью условного форматирования с использованием цвета ячеек или цвета шрифта, можно также выполнить сортировку по цветам. Кроме того, можно выполнить сортировку по набору значков, созданных с помощью условного форматирования.
-
Выделите ячейку в столбце, который требуется отсортировать.
-
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
-
В диалоговом окне Сортировка в группе Столбец в поле Сортировать по выберите столбец, по которому необходимо выполнить сортировку.
-
В группе Сортировка, выберите вариант Цвет ячейки, Цвет шрифта или Значок ячейки.
-
В группе Порядок щелкните стрелку рядом с кнопкой, а затем в зависимости от типа формата выберите цвет шрифта, цвет заливки ячейки или значок ячейки.
-
Затем выберите последовательность сортировки. Выполните одно из указанных ниже действий.
-
Чтобы переместить цвет ячейки, цвет шрифта или значок вверх или влево, выберите вариант Сверху для сортировки по столбцу и Слева для сортировки по строке.
-
Чтобы переместить цвет ячейки, цвет шрифта или значок вниз или вправо, выберите вариант Снизу для сортировки по столбцу и Справа для сортировки по строке.
Примечание: Порядка цветов ячеек, цветов шрифта или значков по умолчанию не существует. Вы должны самостоятельно определить порядок для каждой сортировки.
-
-
Чтобы задать следующий цвет ячейки, цвет шрифта или значок для сортировки, нажмите кнопку Добавить уровень и повторите шаги 3–5.
Убедитесь в том, что в поле Затем по выбран тот же столбец и что сделан тот же выбор в поле Порядок.
Повторите эти операции для каждого дополнительного цвета ячеек, шрифта или значка, которые необходимо включить в сортировку.
Для сортировки в порядке, заданном пользователем, можно применять настраиваемые списки. Например, столбец может содержать значения, по которым вы хотите выполнить сортировку, такие как «Высокий», «Средний» и «Низкий». Как настроить сортировку, чтобы сначала отображались значения «Высокий», затем — «Средний», а в конце — «Низкий»? Если отсортировать их в алфавитном порядке (от А до Я), то значения «Высокий» будут отображаться вверху, но за ними окажутся значения «Низкий», а не «Средний». А при сортировке от Я до А значения «Средний» окажутся в самом верху. В действительности значения «Средний» всегда, независимо от порядка сортировки должны отображаться в середине. Вы можете решить эту проблему, создав настраиваемый список.
-
При необходимости создайте настраиваемый список.
-
В диапазоне ячеек введите значения, по которым необходимо выполнить сортировку, в нужном порядке, например в порядке убывания.
-
Выделите диапазон ячеек, в которые были введены значения. В описанном выше примере выделите ячейки A1:A3.
-
Выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки, затем в диалоговом окне Списки нажмите кнопку Импорт и дважды нажмите кнопку ОК.
Примечания:
-
Пользовательские списки можно создавать только на основе значений (текстовых, числовых или значений даты и времени). Создать настраиваемый список на основе формата (цвета ячейки, цвета шрифта или значков) нельзя.
-
Максимальная длина пользовательского списка составляет 255 символов, и первое значение не может начинаться с цифры.
-
-
-
Выделите ячейку в столбце, который требуется отсортировать.
-
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
-
В диалоговом окне Сортировка в группе Столбец в поле Сортировать по или Затем по укажите столбец для сортировки по настраиваемому списку.
-
В поле Порядок выберите пункт Настраиваемый список.
-
В диалоговом окне Списки выберите нужный список. В нашем примере нужно выбрать список Высокий, Средний, Низкий.
-
Нажмите кнопку ОК.
-
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
-
В диалоговом окне Сортировка нажмите кнопку Параметры.
-
В диалоговом окне Параметры сортировки установите флажок Учитывать регистр.
-
Дважды нажмите кнопку ОК.
Как правило, сортировка выполняется сверху вниз, однако значения можно отсортировать слева направо.
Примечание: Таблицы не поддерживают возможность сортировки слева направо. Сначала преобразуйте таблицу в диапазон: выделите в ней любую ячейку и выберите пункты Работа с таблицами > Преобразовать в диапазон.
-
Выделите любую ячейку в сортируемом диапазоне.
-
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
-
В диалоговом окне Сортировка нажмите кнопку Параметры.
-
В диалоговом окне Параметры сортировки в группе Сортировать выберите пункт столбцы диапазона, а затем нажмите кнопку ОК.
-
В группе Строка в поле Сортировка по выберите строку для сортировки. Обычно это строка 1 (если требуется выполнить сортировку по строке заголовка).
Совет: Если строка заголовка содержит текст, а вам нужно отсортировать столбцы по числам, добавьте новую строку над диапазоном данных и укажите в ней числа в нужном порядке.
-
Чтобы выполнить сортировку по значению, выберите один из вариантов в раскрывающемся списке «Порядок».
-
Для текстовых значений выберите вариант От А до Я или От Я до А.
-
Для числовых значений выберите По возрастанию или По убыванию.
-
Для дат и времени выберите От старых к новым или От новых к старым.
-
-
Чтобы выполнить сортировку по цвету ячейки, цвету шрифта или значку, сделайте следующее.
-
В группе Сортировка, выберите вариант Цвет ячейки, Цвет шрифта или Значок ячейки.
-
В списке Порядок выберите цвет ячейки, цвет шрифта или значок, а затем — вариант Слева или Справа.
-
Примечание: При сортировке строк, являющихся частью структуры листа, в Excel сортируются группы наивысшего уровня (уровень 1) таким образом, что порядок расположения строк или столбцов детализации не изменится, даже если они скрыты.
Чтобы выполнить сортировку по части значения в столбце, например части кода (789-WDG-34), фамилии (Регина Покровская) или имени (Покровская Регина), сначала необходимо разбить столбец на две или несколько частей таким образом, чтобы значение, по которому нужно выполнить сортировку, оказалось в собственном столбце. Чтобы разбить значения в ячейке на части, можно воспользоваться текстовыми функциями или мастером текстов. Дополнительные сведения и примеры см. в статьях Разбивка текста по разным ячейкам и Разбивка текста по разным столбцам с помощью функций.
Предупреждение: Вы можете отсортировать значения в диапазоне, который является частью другого диапазона, однако делать это не рекомендуется, так как в результате будет разорвана связь между отсортированным диапазоном и исходными данными. Если отсортировать данные, как показано ниже, выбранные сотрудники окажутся связаны с другими отделами.
К счастью, Excel выдает предупреждение, если обнаруживает подобную попытку:
Если вы не собирались сортировать данные таким образом, выберите вариант автоматически расширить выделенный диапазон, в противном случае — сортировать в пределах указанного выделения.
Если результат не соответствует желаемому, нажмите кнопку Отменить 
.
Примечание: Отсортировать подобным образом значения в таблице нельзя.
Если результаты сортировки данных не соответствуют ожиданиям, сделайте следующее.
Проверьте, не изменились ли значения, возвращаемые формулами Если сортируемые данные содержат одну или несколько формул, значения, возвращаемые ими, при пересчете листа могут измениться. В этом случае примените сортировку повторно, чтобы получить актуальные результаты.
Перед сортировкой отобразите скрытые строки и столбцы При сортировке по столбцам скрытые строки не перемещаются, а при сортировке по строкам не перемещаются скрытые столбцы. Перед сортировкой данных целесообразно отобразить скрытые строки и столбцы.
Проверьте текущий параметр языкового стандарта Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе Региональные параметры или Язык и региональные стандарты задан правильный языковой стандарт. Сведения о том, как изменить параметр языкового стандарта, см. в справке Microsoft Windows.
Вводите заголовки столбцов только в одну строку Если необходимо использовать заголовки из нескольких строк, установите перенос слов в ячейке.
Включите или отключите строку заголовков Обычно рекомендуется отображать строку заголовков при сортировке по столбцам, так как она облегчает восприятие данных. По умолчанию значение в заголовке не включается в сортировку. Но в некоторых случаях может потребоваться включить или отключить заголовок, чтобы значение в заголовке включалось или не включалось в сортировку. Выполните одно из следующих действий.
-
Чтобы исключить из сортировки первую строку данных (заголовок столбца), на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр, выберите команду Настраиваемая сортировка и установите флажок Мои данные содержат заголовки.
-
Чтобы включить в сортировку первую строку данных (так как она не является заголовком столбца), на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр, выберите команду Настраиваемая сортировка и снимите флажок Мои данные содержат заголовки.
Если данные отформатированы как таблица Excel, можно быстро выполнить их сортировку и фильтрацию с помощью кнопок фильтрации в строке заголовков.
-
Если данные еще не содержатся в таблице, нужно отформатировать их как таблицу. Это приведет к автоматическому добавлению кнопки фильтрации вверху каждого столбца таблицы.
-
Нажмите кнопку фильтрации в верхней части нужного столбца и выберите необходимый порядок сортировки.
-
Чтобы отменить сортировку, нажмите кнопку Отменить на вкладке Главная.
-
Выберите ячейку в столбце, по которому будет выполняться сортировка.
-
Если на листе есть строка заголовков, выберите один заголовок, по которому нужно отсортировать данные, например Население.
-
Если строки заголовков нет, выберите верхнюю ячейку в столбце, по которому нужно выполнить сортировку, например ячейку со значением 634535.
-
-
На вкладке Данные выберите один из способов сортировки:
-
По возрастанию: сортировка от A до Я, от наименьшего значения до наибольшего или от самой ранней даты до самой поздней.
-
По убыванию: сортировка от Я до А, от наибольшего значения до наименьшего или от самой поздней даты до самой ранней.
-
Например, у вас есть таблица со столбцами «Отдел» и «Сотрудник». Можно сначала выполнить сортировку по столбцу «Отдел» для группировки всех сотрудников по отделам, а затем — по имени для расположения имен сотрудников каждого отдела в алфавитном порядке.
Выберите любую ячейку в диапазоне данных.
-
На вкладке Данные в группе Сортировка и фильтрация нажмите кнопку Настраиваемая сортировка.
-
В диалоговом окне Настраиваемая сортировка в группе Столбец в поле Сортировка выберите первый столбец, по которому необходимо выполнить сортировку.
Примечание: Меню Сортировка отключено, так как оно пока не поддерживается. В настоящее время вы можете изменить сортировку в классическом приложении Excel.
-
В разделе Порядок выберите порядок сортировки.
-
По возрастанию: сортировка от A до Я, от наименьшего значения до наибольшего или от самой ранней даты до самой поздней.
-
По убыванию: сортировка от Я до А, от наибольшего значения до наименьшего или от самой поздней даты до самой ранней.
-
-
Чтобы добавить еще один столбец для сортировки, выберите команду Добавить и повторите шаги 5 и 6.
-
Чтобы изменить порядок сортировки столбцов, выделите запись нужного столбца и щелкните стрелку Вверх или Вниз рядом с кнопкой Параметры.
Если диапазон ячеек или столбец таблицы был отформатирован вручную или с помощью условного форматирования с использованием цвета ячеек или цвета шрифта, можно также выполнить сортировку по цветам. Кроме того, можно выполнить сортировку по набору значков, созданных с помощью условного форматирования.
-
Выделите ячейку в столбце, который требуется отсортировать.
-
На вкладке Данные в группе Сортировка и фильтрация нажмите кнопку Настраиваемая сортировка.
-
В диалоговом окне Настраиваемая сортировка в группе Столбцы выберите столбец, по которому необходимо выполнить сортировку.
-
В группе Сортировка выберите вариант Цвет ячейки, Цвет шрифта или Значок условного форматирования.
-
В группе Порядок выберите нужный порядок (отображаемые параметры зависят от используемого формата). Затем выберите цвет ячейки, цвет шрифта или значок ячейки.
-
Далее выберите способ сортировки, перемещая цвет ячейки, цвет шрифта или значок:
Примечание: Порядка цветов ячеек, цветов шрифта или значков по умолчанию не существует. Вы должны самостоятельно определить порядок для каждой сортировки.
-
Перемещение вверх или влево: выберите параметр Сверху для сортировки столбца и Слева для сортировки строки.
-
Перемещение вниз или вправо: выберите параметр Снизу для сортировки столбца и Справа для сортировки строки.
-
-
Чтобы задать следующий цвет ячейки, цвет шрифта или значок для сортировки, нажмите кнопку Добавить уровень и повторите шаги 1–5.
-
Убедитесь, что столбец в поле Затем по и выбор в поле Порядок совпадают.
-
Повторите эти шаги для каждого дополнительного цвета ячейки, цвета шрифта или значка, которые нужно включить в сортировку.
-
На вкладке Данные в группе Сортировка и фильтрация нажмите кнопку Настраиваемая сортировка.
-
В диалоговом окне Настраиваемая сортировка нажмите кнопку Параметры.
-
В меню Параметры установите флажок С учетом регистра.
-
Нажмите кнопку ОК.
Как правило, сортировка выполняется сверху вниз, однако значения можно отсортировать слева направо.
Примечание: Таблицы не поддерживают возможность сортировки слева направо. Сначала преобразуйте таблицу в диапазон: выделите в ней любую ячейку и выберите пункты Работа с таблицами > Преобразовать в диапазон.
-
Выделите любую ячейку в сортируемом диапазоне.
-
На вкладке Данные в группе Сортировка и фильтрация нажмите кнопку Настраиваемая сортировка.
-
В диалоговом окне «Настраиваемая сортировка» нажмите кнопку Параметры.
-
В разделе Ориентация выберите вариант Сортировать слева направо
-
В группе Строка в раскрывающемся списке Сортировка выберите строку для сортировки. Обычно это строка 1 (если требуется выполнить сортировку по строке заголовка).
-
Чтобы выполнить сортировку по значению, выберите один из вариантов в раскрывающемся списке Порядок.
-
Сортировка по возрастанию: сортировка от A до Я, от наименьшего значения до наибольшего или от самой ранней даты до самой поздней.
-
Сортировка по убыванию: сортировка от Я до А, от наибольшего значения до наименьшего или от самой поздней даты до самой ранней.
-
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Для автоматической сортировки данных используйте функции СОРТ и СОРТПО.
Хочу облегчить жизнь тем, кто работает с большими таблицами. Для этого мы сейчас разберемся с понятием группировка строк в excel. Благодаря ему ваши данные примут структурированный вид, вы сможете сворачивать ненужную в настоящий момент информацию, а потом быстро ее находить. Удобно, правда?
Инструкция
Открываем файл excel и приступаем к группировке:
- Выделите нужные строки;
- Откройте вкладку «Данные» в меню сверху;
- Под ним в поле «Структура» найдите команду «Группировать»;
- В появившемся окошке поставьте галочку напротив строк;
Ничего сложного, не так ли?
Объединенные экселем области имеют рядом и/или сверху полоску. Под ней находится знак минуса. Нажмите на него. Сгруппированные ячейки свернулись. Чтобы снова их увидеть, нажмите на плюсик сверху.
Задаем название
Если вы еще не давали название блоку из выделенных строк, сделайте это сейчас. С заголовком вам будет легче находить нужную часть при повторных открытиях документа. Запишите наименование в нижней строке столбца.
Вы скажете, что данное расположение неправильно. И будете правы. Такие настройки придуманы разработчиками, но мы сделаем по-своему:
- Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
- Сначала нажмите на маленький квадратик рядом со словом «Структура».
- В появившемся окне «Расположение итоговых данных» снимите все галочки.
Теперь нам необходимо исправить заданную ранее систематизацию:
Автоматическая структуризация
Ранее мы выполняли группировку вручную вручную. Есть и автоматический способ сделать то же самое. Но в этом случае эксель сам выбирает, какие данные объединить. Стоит отдать должное, он руководствуется логикой. Например, определяет заголовок и ячейки с суммированием, а строки между ними группирует и позволяет их сворачивать.
Благодаря этому таблица не занимает много места.
Правда, не всегда программа структурирует то, что нужно. Тогда приходиться прибегать к ручным настройкам. Однако если вы имеете дело с формулами, то автоматическая группировка вероятнее всего вам подойдет. Чтобы ее применить, команде «Группировать» выберите подпункт «Создание структуры» — готово.
Как отменить группировку, созданную вручную, вы узнали выше. Как это сделать после применения автоматического способа? В той же вкладке «Разгруппировать» нажмите «Очистить структуру».
Как сортировать данные таблицы?
Максимально оптимизировать вашу таблицу поможет такая функция экселя как сортировка данных. Ее можно производить по разным признакам. Я расскажу об основных моментах, которые помогут вам в работе.
Цветовое деление
Вы выделяли некоторые строки, ячейки или текст в них другим цветом? Или только хотели бы так сделать? Тогда этот способ поможет вам быстро их сгруппировать:
- Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
- В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.
- В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
- В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
- Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.
Примечание: чтобы выполнить те же действия с другими значениями и цветами, в диалоговом окне сортировки нажмите «Добавить уровень». После того, как вы сохраните файл, можно выполнить еще раз такое же объединение. Необходимо в тот же поле нажать кнопку «Применить повторно».
Объединение значений
Программа позволяет сгруппировывать таблицу по значению ячейки. Это удобно, когда вам необходимо найти поля с определенными именами, кодами, датами и пр. Чтобы это сделать, выполните первые два действия из предыдущей инструкции, а в третьем пункте вместо цвета выберите «Значение».
В группе «Порядок» есть пункт «Настраиваемый список», нажав на который вы можете воспользоваться предложением сортировки по спискам экселя или настроить собственный. Таким способом можно объединить данные по дням недели, с одинаковыми значениями и пр.
Упрощаем большую таблицу
Excel позволяет применять не одну группировку в таблице. Вы можете создать, к примеру, область с подсчетом годового дохода, еще одну — квартального, а третью — месячного. Всего можно сделать 9 категорий. Это называется многоуровневой группировкой. Как ее создать:
На этом всё, друзья.
Если же вы хотите изучить Excel поглубже, то рекомендую приобрести данный видео курс: « Неизвестный Excel ».
Сортировка данных в Excel
Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.
Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.
Сортировка по одному критерию
- В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
- На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].
- Выбрать нужную кнопку: – сортировка по возрастанию или сортировка по убыванию.
– сортировка по возрастанию или 
сортировка по убыванию.Отметим, что буквы на этой кнопке указывают только на направление сортировки, а вид кнопки остается один и тот же и при текстовых, и при числовых данных.
Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.
Многоуровневая сортировка
- Выделить одну ячейку из сортируемого массива данных.
Если диапазоне данных имеются пустые столбцы или строкой, то Excel автоматически воспринимает их как границы сортируемого массива данных. В таком случае следует выделить все данные, подлежащие сортировке.
- На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
- Последовательно задать уровни сортировки (определяемые именем столбца).
Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:
- Имя столбца для сортировки.
- Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
- Порядок сортировки (по убыванию или по возрастанию).
Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.
Сортировка по форматированию
Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.
Пошаговый порядок действий:
- Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
- На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
- В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
- В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
- Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
- При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.
Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.
Сортировка данных в Excel
Сортировать данные в Excel умеют даже новички. Трудно не заметить сразу три кнопки на ленте.
Но иногда спрашивают, как отсортировать данные в таблице Excel по столбцам, по цвету и т.п. Все эти задачи легко решаются в Excel.
Сортировка в Excel по нескольким столбцам
Сортировку строк в Excel можно осуществить по нескольким столбцам одновременно. В этом случае вначале происходит сортировка по первому указанному столбцу. Затем те строки, где ячейки в отсортированном столбце повторяются, сортируются по второму указанному столбцу и т.д. Таким образом, отчет о продажах можно упорядочить вначале по региону, затем в рамках каждого региона по менеджеру, затем в рамках одного региона и менеджера по группе товаров.
Выделив одну ячейку или всю таблицу, вызываем на ленте команду сортировки. Если нужно, ставим галочку Мои данные содержат заголовки и добавляем необходимое количество уровней.
Сортировка по собственному списку
Большинство пользователей знает, как отсортировать строки в Excel по алфавиту или возрастанию чисел. Но иногда нужно расставить строки в заданном порядке, который не обязательно соответствует алфавиту или возрастающей последовательности. Это может быть иерархия элементов, города и страны в удобной последовательности и т.п. В общем то, что имеет свою логику.
Самый простой (а, значит, достойный внимания) способ заключается в том, чтобы в столбце рядом проставить числа в соответствии с желаемыми порядком строк, а затем осуществить сортировку таблицы по этому столбцу.
Способ хорош, но требует создания дополнительного временного столбца. При частом повторении операции будет отнимать время. Если приходится сортировать одни и те же данные, то можно создать специальный список, на основе которого затем и произойдет сортировка. Это тот же список, который используется в автозаполнении ячеек.
Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки…
Здесь создаем вручную или импортируем список, отсортированный в нужном порядке.
Теперь в окне сортировки в поле Порядок нужно выбрать Настраиваемый список…
И в следующем окне указать нужный список.
Созданным списком можно пользоваться и в других файлах Excel.
Сортировка в Excel по цвету ячейки, шрифта, значку
В настройках сортировки также есть возможность использовать цвет ячейки, шрифта и значок (из условного форматирования). Если для форматирования отдельных ячеек использовать заливку (например, для указания проблемных товаров или клиентов), то затем их легко вывести вверх таблицы с помощью сортировки.
Сортировка по столбцам
Сортировать в Excel можно и по столбцам. Для этого выделяем таблицу вместе с названиями столбцов. Затем в окне сортировки вначале нажимаем Параметры и ставим переключатель на столбцы диапазона.
Последующие настройки обычные: задаем строку (!) и порядок. Единственное, теперь нельзя использовать названия строк (по аналогии с названием столбцов), будут только номера.
Сортировка промежуточных итогов
В Excel есть такой инструмент, как Промежуточные итоги. Вкратце, он нужен для того, чтобы автоматически создавать промежуточные итоги под группой однородных по некоторому признаку ячеек.
Это отличная функция, но как сделать, чтобы вверху были группы с наибольшей суммой? Ручной труд, конечно, в почете, но есть и другой вариант. Достаточно применить обычную сортировку к сгруппированным данным.
Внутреннее содержимое групп не изменится. Общий итог останется внизу.
Сортировка строк в случайном порядке
Интересный случай, когда нужно перемешать строки в случайном порядке. Создаем рядом столбец с функцией случайного числа СЛЧИС. Теперь сортируем таблицу по этому столбцу.
Случайные числа перемешают строки в хаотичном порядке. Не забудьте удалить лишний столбец.
Вот такие замечательные приемы. Если я что-то забыл, напишите, пожалуйста в комментариях.
В этом руководстве я расскажу, как выполнить многоуровневую сортировку в Excel. Вы можете посмотреть видео ниже или прочитать руководство под ним.
При работе с данными в Excel сортировка данных — одна из обычных вещей, которые вам, возможно, придется сделать.
В большинстве случаев вам нужно отсортировать один столбец.
Но в некоторых случаях может потребоваться отсортировать два столбца или более двух столбцов.
Например, в приведенном ниже наборе данных я хочу отсортировать данные по столбцу «Регион», а затем по столбцу «Продажи». Это позволит мне увидеть, какие торговые представители преуспели в каких регионах.
Хотя в Excel легко отсортировать данные по одному столбцу, когда дело доходит до сортировки по двум столбцам, вам необходимо предпринять несколько дополнительных шагов.
В этом руководстве я покажу вам два способа выполнения многоуровневой сортировки данных в Excel (т. Е. Сортировки по двум столбцам).
Диалоговое окно многоуровневой сортировки с использованием
Когда вы сортируете данные с помощью диалогового окна сортировки, вы можете добавить к ним несколько уровней.
Вот шаги для выполнения многоуровневой сортировки с использованием диалогового окна:
- Выберите весь набор данных, который вы хотите отсортировать.
- Щелкните вкладку Данные.
- Щелкните значок сортировки (показанный ниже). Откроется диалоговое окно «Сортировка».
- В диалоговом окне «Сортировка» выберите следующие параметры.
- Сортировать по (столбцу): региону (это первый уровень сортировки)
- Сортировать по: значениям
- Порядок: от А до Я
- Если у ваших данных есть заголовки, убедитесь, что установлен флажок «Мои данные имеют заголовки».
- Щелкните Добавить уровень (это добавит еще один уровень параметров сортировки).
- На втором уровне сортировки сделайте следующие выборки:
- Затем по (Столбец): Продажи
- Сортировать по: значениям
- Порядок: от наибольшего к наименьшему
- Нажмите ОК.
Вышеупомянутые шаги дадут вам результат, как показано ниже. Это сортирует данные сначала по региону, а затем по столбцу продаж. Обратите внимание: поскольку при сортировке столбца «Продажи» сначала сортируется столбец «Регион», столбец «Регион» остается неизменным.
В этом примере я отсортировал данные по двум столбцам. У вас также может быть сортировка по нескольким столбцам. Все, что вам нужно сделать, это добавить эти уровни сортировки и указать детали.
Примечание. Хотя этот метод длиннее и требует нескольких дополнительных шагов (по сравнению с методом множественной сортировки, описанным далее), я рекомендую использовать его, поскольку он менее запутан.
Многоуровневая сортировка с использованием значков сортировки
Не многие люди знают этот способ многоуровневой сортировки данных в Excel.
Этот метод работает так же, с небольшой разницей — вы сначала сортируете второй уровень, а затем переходите к столбцу сортировки первого уровня.
Вот как это сделать:
- Выберите столбец, который вы хотите отсортировать последним (в этом случае сначала выберите данные о продажах — C1: C13).
- Щелкните вкладку «Данные«.
- В группе «Сортировка и фильтр» щелкните значок сортировки от Я до А. Это отсортирует данные о продажах от наибольших к наименьшим.
- Вышеупомянутый шаг вызовет всплывающее диалоговое окно с предупреждением о сортировке. Убедитесь, что установлен флажок «Развернуть выделение». Это обеспечивает сортировку всего набора данных, а не только данных в столбце «Продажи».
- Щелкните Сортировать.
- Выберите столбец «Регион».
- На вкладке «Данные» щелкните значок сортировки от А до Я.
- Убедитесь, что во всплывающем диалоговом окне «Предупреждение о сортировке» выбрано «Развернуть выделение».
- Щелкните Сортировать.
Вышеупомянутые шаги сортируют данные так же, как и в первом методе.
Хотя этот метод работает нормально, я рекомендую использовать метод bo в диалоговом окне сортировки.
Диалоговое окно «Сортировка» делает его менее подверженным ошибкам (поскольку вы можете видеть, какие уровни сортируются в каком порядке).
Кроме того, есть больше способов сортировки данных с помощью диалогового окна. Например, вы можете отсортировать столбец по цвету ячейки / шрифта, а также можете использовать свои собственные критерии сортировки.
Многоуровневая сортировка
Сортировку можно
осуществлять по нескольким столбцам
или строкам, чтобы сгруппировать данные
с одинаковыми значениями в одном столбце
или строке, а затем выполнить сортировку
другого столбца или строки в этих группах
с одинаковыми значениями. Например,
если имеются столбцы «Отдел»
и «Сотрудник«,
сначала можно отсортировать строки по
столбцу «Отдел»
(для группировки всех сотрудников одного
отдела), а затем по фамилиям сотрудников
(для расположения фамилий сотрудников
в каждом отделе в алфавитном порядке).
Для многоуровневой
сортировки списка выполните следующие
действия.
-
Выделите диапазон
ячеек с двумя или более столбцами данных
или убедитесь в том, что активная ячейка
находится в таблице с двумя или более
столбцами. -
На вкладке Данные
в группе Сортировка
и фильтр
нажмите кнопку Сортировка.
Появится диалоговое окно Сортировка. -
В группе Столбец
в поле Сортировать
по выберите
первый столбец, который необходимо
отсортировать. -
В поле Сортировка
выберите тип сортировки. Выполните
одно из указанных ниже действий.
-
Для
сортировки по текстовым, числовым
значениям или значениям даты и времени
выберите пункт Значения. -
Для
сортировки по типам форматирования
выберите пункт Цвет
ячейки, Цвет
шрифта или
Значок
ячейки.
-
В группе Порядок
выберите последовательность, в которой
необходимо осуществлять сортировку.
Выполните одно из указанных ниже
действий.
-
Для
текстовых значений выберите пункт От
А до Я или
От Я до А. -
Для
числовых значений выберите пункт По
возрастанию
или По
убыванию. -
Для
сортировки по датам и времени выберите
пункт От
старых к новым
или От новых
к старым. -
Для
сортировки на основе настраиваемых
списков выберите пункт Настраиваемый
список.
-
Для добавления к
сортировке следующего столбца нажмите
кнопку Добавить
уровень, а
затем повторите шаги с третьего по
пятый. -
Чтобы изменить
порядок сортировки столбцов, выделите
запись нужного столбца и щелкните
стрелку Вверх
или Вниз.
Командная кнопка
Параметры
в окне настраиваемой сортировки выводит
окно Параметры
сортировки,
в котором можно:
-
установить
параметр Учитывать
регистр,
для различия строчных и прописных
символов при сортировке; -
указать,
как будут сортироваться записи списка:
по строкам
(по умолчанию) или по
столбцам.
-
Формирование сводной информации
Excel
содержит средства формирования сводной
информации для проведения анализа
данных. Сводная информация может быть
получена:
-
объединением
данных промежуточных
итогов; -
методом
консолидации; -
формированием
сводных
таблиц.
Промежуточные итоги
Подведение
промежуточных
итогов —
это удобная и наглядная возможность
провести обработку записей списка для
последующего анализа, создания диаграмм
и вывода на печать.
При этом в список
автоматически вставляются дополнительные
строки, отражающие итоговые значения
по выбранным ячейкам. При создании
промежуточных итогов необходимо, чтобы
в списке первая строка содержала название
столбцов, а остальные строки — однотипные
данные. Пустые строки должны отсутствовать,
а данные должны быть отсортированы.
Для создания
промежуточных итогов диапазона данных
необходимо:
-
представить данные
в виде списка; -
отсортировать
записи списка по столбцу, по которому
будут подводиться промежуточные итоги
(командой Сортировка
на вкладке Данные
упорядочить в соответствии со значениями; -
выделить одну
(любую) ячейку списка, либо весь список
целиком, включая строку заголовков; -
на вкладке Данные
в группе Структура
выбрать команду Промежуточный
итог; -
в диалоговом окне
Промежуточные
итоги задать
нужные параметры:
-
В графе При
каждом изменении в
выберите столбец, по которому будут
подводиться итоги. -
В графе Операция
раскройте список и выберите функцию,
по которой будут вычисляться итоги.
Например, «Сумма«,
«Среднее»
и т. д. -
В группе Добавить
итоги по
отметьте столбцы, содержащие значения,
по которым будет подводиться итог. -
Если между итогами
необходим разрыв страницы, то установите
флажок Конец
страницы между группами. -
Чтобы созданные
итоги отображались над значениями,
необходимо отключить пункт Итоги
под данными.
-
Закрыть окно
кнопкой OK.
После выполнения
команды создается структура, в которой
данные разбиты на несколько уровней. С
помощью уровней структуры, расположенных
слева вверху листа с итогами, можно
управлять выводом данных соответствующего
уровня из таблицы на экран, указывая,
выводить данные или скрывать.
Для удаления
промежуточных итогов необходимо в
диалоговом окне Промежуточные
итоги выбрать
кнопку Убрать
все. При
удалении промежуточных итогов из списка
удаляется и структура таблицы.
Соседние файлы в папке Использование Microsoft Excel
- #
- #
31.03.201562.98 Кб81Примеры решения задач (исх. файл).xls