Команда сортировки Excel совершенно не работает, как ожидалось.

У меня есть таблица в Excel (отформатированная как таблица), которая позволяет мне сортировать, но результаты неверны как минимум по двум причинам:

1. колонка числовых значений, по которой я сортирую от А до Я или от наибольшего к наименьшему, возвращается НЕ в порядке (и, похоже, я не могу найти никакую закономерность к тому, что она делает?!?

   

2. Она “перемешивает” данные другой колонки наугад. (Как и ожидали бы, если бы у вас были NULL или если бы вы не выбрали весь диапазон данных).

• У меня она отформатирована как ТАБЛИЦА.
• У меня нет NULL/пропусков в диапазоне данных.
• Я пробовал выбрать всю таблицу перед сортировкой, а также пробовал не выбирать всю таблицу.
• Я пытался сделать все формулы относительными (либо с $ (как в $A$1), либо с ссылками на колонки таблицы (как в 1-[@[ColumnHeader]]).
• У меня есть довольно сложные формулы, но это маленькая таблица (27 строк на 11 колонок) и все они работают ПОКА, я не пытаюсь СОРТИРОВАТЬ.

По комментариям, пытаюсь поделиться всей таблицей с примером одной из формул в строке формул.
Да, в колонке “% Модель” есть формула, по которой я пытаюсь сортировать, но я пытаюсь сортировать по значениям… разве это невозможно?
вся таблица и одна формула

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

В настройке, которую вы показываете, колонка имени (колонка X) заполнена формулам, ссылающимся на ячейки, используя обычные ссылки, в то время как в других формулах, похоже, вы “религиозно” используете структурированные ссылки, когда это возможно. И в нескольких формул, изображенных на картинках, N41 и N43 играют важную роль.

Смысл сказанного выше в том, что эти ячейки находятся за пределами Таблицы. Еще одним индикатором является существование самой колонки имен. Если колонка A была бы частью Таблицы, то вся эта колонка не была бы необходима для работы в Excel, хотя можно было бы захотеть, чтобы имена периодически повторялись в наборе данных, который охватывает несколько мониторов, и всегда нужно видеть имена. Есть и другие способы сделать это, но это было бы одним из них. Однако, учитывая ссылки на N41 и N43, наличие этой колонки подразумевает, что ваша Таблица начинается с колонки X.

Почему это важно? Ваша выбранная колонка сортировки, AB, имеет формулу, которая извлекает значение из колонки “Фактические значения” каждой строки. В свою очередь, в ней есть формулы, которые обращаются к колонке имен для поиска, который, предположительно, возвращает какое-то числовое значение.

(Кстати, в этой формуле NOT FOUNT используется как значение для возврата, если имя не найдено. Однако без двойных кавычек с обоих концов оно будет интерпретироваться как Именованный диапазон, но с пробелом между словами оно не может быть Именованным диапазоном. Если я ничего не упустил, это нужно исправить, и единственная причина, по которой это не стало проблемой, это то, что, как и в случае с связанной проблемой со всеми этими возможностями “NotTru” в других формулах, данные просто не вызывали использования этой части формулы, поэтому она никогда не вносила свой вклад в дело. Проблема “NotTru”, кстати, заключается в том, что это текст, а не числовое значение, в формулах, которые явно должны иметь числовые входные данные. Так что если это был бы результат IF(), в который оно входит, формулы затем делили бы на… текст… и это привело бы к ошибке.)

Это числовое значение затем используется в формуле вашей колонки % Модель. Так что всё сводится обратно к колонке имен. Поскольку это не структурированные ссылки (предположительно, потому что колонка A не является частью этой Таблицы), Excel выполнит сортировку, и в течение нескольких микросекунд или миллисекунд элементы в этой колонке переместятся вместе со своими строками, как и ожидалось, а затем, прежде чем вы это увидите, Excel корректирует формулы с неструктурированными ссылками (=A7 и так далее), чтобы поддерживать их первоначальный порядок, что приводит к возвращению Таблицы к её виду до сортировки.

Исправление этого будет проблемой.

Один из способов – это изменить порядок поиска, так сказать. Поместите реальные данные имен в колонку имен, а в колонку A поместите формулу, чтобы получить имя этой строки. Конечно, если есть другие данные, которые не отражены в Таблице, они не будут сортироваться вместе с Таблицей, поэтому… проблема.

Другой способ – это обновить структуру. Поместите все данные в Таблицу, а не слева от неё. Колонки с операционными вещами, такими как ячейки N41 и N43, останутся вне Таблицы. Это вполне осуществимо, но может вызвать определенные проблемы в работе, если есть другие пользователи, и им это не нравится… проблема.

Последний вариант, вероятно, действительно единственный путь.

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

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

Представьте, что у вас есть это:

     A       B
1    Данные  Вычисления (формула из колонки B)
2    1       2       =A2*2
3    5       10      =A3*2
4    8       16      =A4*2
5    0       0       =A5*2

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

Например, B4, который равен 16, должен быть внизу. Excel возьмет формулу в B4 (‘=A4*2’) и сделает эквивалент копирования и вставки этой формулы на одну ячейку вниз в B5. И вы знаете, что Excel сделает с такой копией-вставкой; когда он вставляет формулу на одну ячейку вниз, он обновит её на ‘=A5*2’. Это означает, что даже после сортировки, B5 по-прежнему будет равно нулю. По сути, сортировка ничего не сделает.

Способ исправить это – это включить колонку A в ту же сортировку; вы можете сортировать по колонке B, и до тех пор, пока основные данные действительно перемещаются, вы получите то, что хотите. Копирование и вставка B4 И A4 в B5 и A5 переместят 8 вниз в A5, и B5 по-прежнему будет иметь формулу ‘=A5*2’, что и нужно, потому что она теперь равна 16.

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

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

Например – формула ниже на листе 1:

=INDEX('sheet3'!$C$12:$AL$12,MATCH('sheet1'!B7,'sheet3'!$C$3:$AL$3,0))

Проблема для меня заключалась в “sheet1” перед B7. Удаление “sheet1” решило проблему сортировки для меня. “Sheet1” на Sheet1 не нужно в формуле, но Excel иногда добавляет его автоматически.

ПРИМЕЧАНИЕ: Я не сортировал по колонке, в которой содержится вышеуказанная формула, эта формула имела последующие влияния.
ПРИМЕЧАНИЕ2: Использую Excel 365 – десктопное приложение. (Я никогда не видел этой проблемы в более ранних версиях.)