Как исправить, что Excel неправильно сортирует числа

Как

КАдам Дэвидсон

Опубликовано4 июля 2023 г.

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

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

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

Как проверить количество символов в каждой ячейке

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

скопировать и вставить значения из внешнего источника, такого как данные, найденные в Интернете.

Существует быстрый и простой способ проверить, есть ли какие-либо дополнительные символы, скрытые рядом с вашими номерами, с помощью функции ДЛСТР.

Чтобы проверить количество символов в каждой ячейке с помощью функции ДЛСТР:

1. Нажмите на пустую ячейку в той же строке, что и первая ячейка данных, которые вы хотите проверить.
2. Тип =ДЛСТР(
   
   
3. Выберите первую ячейку в ваших данных.
   
4. Тип ) и нажмите Входить.
   
5. Формула рассчитает количество символов в вашей ячейке.
6. Чтобы применить формулу ко всем вашим данным, нажмите и удерживайте маркер перетаскивания в правом нижнем углу ячейки.
   
7. Перетащите вниз к нижней части ваших данных.
   
8. Отпустите курсор, и формула заполнится автоматически.
9. Сравните каждое значение с количеством цифр, которое вы видите в соответствующей ячейке.
10. Если результат вашей формулы больше, чем количество цифр в вашей ячейке, вы знаете, что в ячейке есть дополнительные символы, которые не видны. В приведенном ниже примере ячейка показывает одну цифру, но LEN говорит нам, что в ячейке два символа.
    
11. Следуйте инструкциям в разделах ниже, чтобы удалить их.

Удалить непечатаемые символы

Если ваши ячейки содержат непечатаемые символы, Excel будет пытаться их правильно отсортировать. Если вы попробовали метод, описанный выше, и количество символов в вашей ячейке слишком велико, вам необходимо удалить все непечатаемые символы перед сортировкой данных.

К счастью, есть функция Excel предназначен именно для этого.

Чтобы удалить непечатаемые символы в Excel:

1. Найдите пустую ячейку в той же строке, что и первая запись ваших данных.
2. Тип =ЧИСТЫЙ(
   
   
3. Щелкните первую ячейку ваших данных.
   
4. Тип ) и нажмите Входить.
   
5. Ваша новая ячейка теперь должна содержать только число, а все непечатаемые символы удалены.
6. Чтобы применить формулу ко всем ячейкам, нажмите и удерживайте маркер перетаскивания в правом нижнем углу ячейки.
   
7. Перетащите вниз все ячейки, в которых вы хотите, чтобы формула отображалась.
8. Во всех этих ячейках теперь должны быть удалены непечатаемые символы.
   
9. Вы можете проверить, что дополнительные символы были удалены, используя функцию ДЛСТР, как описано выше.
10. Скопируйте только что созданный столбец.
11. Щелкните правой кнопкой мыши первую ячейку исходных данных и выберите Специальная вставка > Вставить значения.
    
12. Теперь вы можете удалить столбец, который вы создали, чтобы очистить свои ячейки.
13. Попробуйте еще раз отсортировать данные, чтобы увидеть, исправила ли это ситуацию.
14. Если данные по-прежнему не сортируются правильно, вполне вероятно, что некоторые из ваших чисел все еще отформатированы как текст. Ознакомьтесь с разделом ниже, чтобы исправить это.

Удалить пробелы

Функция CLEAN удалит многие непечатаемые символы, но не все. Функция удаляет только первые 32 непечатаемых символа в коде ASCII (0-31). Если непечатаемый символ в вашей ячейке находится за пределами этого диапазона, CLEAN не удалит его.

К сожалению, любой пустой пробел, который является наиболее распространенным непечатаемым символом в электронных таблицах Excel, имеет код ASCII 32 и поэтому не удаляется CLEAN. Чтобы избавиться от лишних пробелов, нам нужно использовать функцию TRIM.

Чтобы удалить пробелы из чисел с помощью TRIM:

1. Выберите пустую ячейку в той же строке, что и первое число в списке, который вы пытаетесь отсортировать.
2. Тип =ОТРЕЗАТЬ(
   
   
3. Выберите верхнюю ячейку ваших данных.
   
4. Тип ) и нажмите Входить.
   
5. Любые пустые места теперь должны быть удалены из ячейки.
6. Примените формулу к оставшимся ячейкам, нажав и удерживая маркер перетаскивания в правом нижнем углу ячейки.
   
7. Перетащите вниз все ячейки, в которые вы хотите скопировать формулу.
8. Теперь из всех ваших ячеек должны быть удалены пробелы.
   
9. Скопируйте эти новые ячейки.
10. Щелкните правой кнопкой мыши верхнюю ячейку исходных данных и выберите Специальная вставка > Вставить значения.
    
11. Вы можете удалить свой новый столбец, так как он вам больше не понадобится.
12. Попробуйте еще раз отсортировать данные. Если это все еще не работает, некоторые ячейки могут быть отформатированы как текст, поэтому вам нужно попробовать выполнить следующие действия.

Убедитесь, что ячейки не отформатированы как текст

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

Сначала вам нужно преобразовать ячейки в числа.

Чтобы преобразовать ячейки, отформатированные как текст, в числа:

1. Если вы выполнили описанные выше шаги, а в углу некоторых ячеек есть маленькие зеленые треугольники, это указывает на ошибку Excel.
   
2. Выделите все ячейки и Предупреждение появится символ.
   
3. Нажмите на Предупреждение символ и выберите Преобразовать в число.
   
4. Если вы не видите зеленых треугольников в своих ячейках, введите 1 в пустой ячейке, а затем скопируйте эту ячейку.
   
5. Выделите все свои данные, затем щелкните правой кнопкой мыши и выберите Специальная вставка.
   
6. Выбирать Умножить и нажмите ХОРОШО.
   
7. Теперь ваши ячейки должны быть числами.

Использование одной формулы для исправления неправильной сортировки чисел в Excel

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

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

1. Щелкните пустую ячейку в той же строке, что и ваше верхнее значение.
2. Тип =ЗНАЧ(ОТРЕЗАТЬ(ОЧИСТИТЬ(
   
   
3. Выберите верхнюю ячейку ваших данных.
   
4. Тип ))) и нажмите Входить.
   
5. Нажмите и удерживайте маркер перетаскивания вашей ячейки.
   
6. Перетащите вниз по оставшимся ячейкам.
7. Отпустите, и другие ячейки заполнятся автоматически.
   
8. Скопируйте ячейки, затем щелкните правой кнопкой мыши первую ячейку исходных данных.
9. Выбирать Специальная вставка > Вставить значения.
   
10. Попробуйте отсортировать новые ячейки; теперь они должны сортироваться, как ожидалось.

Устранение распространенных проблем с Excel

Знание того, как исправить некорректную сортировку чисел в Excel, может сэкономить вам часы разочарований. Однако даже описанные выше шаги не будут работать во всех случаях. Если ваша ячейка содержит непечатаемый символ, который не удаляется с помощью CLEAN, ваши данные могут по-прежнему не сортироваться должным образом. Если это так, и больше ничего не работает, используйте LEN, чтобы найти проблемные ячейки, а затем просто удалите всю ячейку и введите значение вручную. Это может быть не элегантно, но это должно работать!

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