Почему команда сортировки в Excel не работает как следует?

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

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

   

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” на Листе 1 не нужен в формуле, но Excel иногда добавляет его автоматически.

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

Примечание 2: Использую Excel 365 – настольное приложение. (Я никогда не сталкивался с этой проблемой в более ранних версиях.)