Не работает сортировка в excel

Обновлено: 22.01.2025

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

Отключайте автоперерасчет на время сортировки. С макросами-эвентами сложнее, но тоже решаемо.

А вообще - не надо сортировать таблицы-источники
Правильный подход - это последовательное наполнение источников данными, а для всяких "выходных красивостей" вы просто должны создавать выборку/отчет/копию данных (и только данных, безо всяких формул внутри полученных таблиц!), и уже их оформлять как требуется.

Отключайте автоперерасчет на время сортировки. С макросами-эвентами сложнее, но тоже решаемо.

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

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

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

Функция ПОИСКПОЗ (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):

= ЕСЛИОШИБКА( ИНДЕКС(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 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Симптомы

Эта операция требует одинакового размера объединенных ячеек.

Причина

Эта проблема может возникнуть при сортировке ряда ячеек, а также при следующих условиях:

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

Обходной путь

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

Общее решение

Выберите весь диапазон, который необходимо сортировать.

В группе Выравнивание на вкладке Главная выберите диалоговое окно Выравнивание.

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

Это может изменить расположение данных в диапазоне.

Пример решения

В таблицу введите следующие данные:

Объединение ячеек A1 и B1, A2 и B2, а также A3 и B3. Для этого выполните следующие действия:

Выберите каждую пару ячеек.

Выберите диалоговое окно Выравнивание в группе Выравнивание на вкладке Главная.

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

Не объединяй ячейки в столбце C.

Выберите ячейки A1:C3, выберите фильтр сортировки & в группе редактирования на вкладке Главная, а затем нажмите настраиваемый сорт.

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

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

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

В этой статье

Проблемы, которые приводят к существенной потере функциональности

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

Что это означает. В Excel 2007 г. или более поздней 2007 г. для сортировки данных можно применить до 64 условий сортировки, но в Excel 97–2003 поддерживается только три состояния сортировки. Чтобы исключить потерю сведений об условиях сортировки в Excel 97–2003, может потребоваться сокращение количества условий сортировки до трех. В Excel 97–2003 вы также можете сортировать данные вручную.

Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном ее повторном открыть в Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

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

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

Что это означает. В Excel 2007 г. или более поздней можно сортировать по настраиваемой списку. Для получения таких же результатов сортировки в Excel 97–2003 можно сгруппировать данные, которые требуется отсортировать, а затем отсортировать их вручную.

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

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

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

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

Проблемы, которые приводят к небольшой потере точности

Книга содержит данные, к которым применен фильтр, не поддерживаемый более ранними версиями Excel. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.

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

Однако все сведения о состоянии фильтра остаются доступными в книге и применяются при повторном ее повторном Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.

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

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

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

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

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

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

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

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

Группировку данных также можно отключить на вкладке "Дополнительные параметры" Excel Параметры. (Файл, параметры).

Читайте также: