У Кайла есть рабочий лист, который состоит из трех столбцов данных: номер детали, количество и длина. Длина обозначается в дюймах с кавычками, чтобы показать, что она указана в дюймах (например, 30, 54 или 100). Кайлу нужно отсортировать данные от самой короткой до самой длинной, но Excel сортирует длину как текст, таким образом, что 100 «предшествует 30». Он задается вопросом, есть ли способ заставить Excel сортировать текстовую информацию, как если бы это были числа, чтобы 30 «правильно было перед 100», не избавляясь от кавычек.

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

идет до 30 «, потому что 1 идет перед 3 в текстовой сортировке.

Однако есть вещи, которые вы можете сделать. Например, если вы используете формулу для генерации значений в столбце Длина, вы можете изменить формулу так, чтобы она при необходимости «дополняла» длины нулями.

При таком подходе у вас не будет таких длин, как 30 «, 54» или 100 «, а вместо этого будет 030 «, 054» и 100 «. Пока все длины используют одинаковое количество цифр, сортировка будет выполняться правильно.

Вы также можете добавить вспомогательный столбец справа от Столбец длины и во вспомогательном столбце поместите числовые значения того, что находится в ячейке слева. Итак, если ваши первые данные находятся в ячейке C2, в ячейку D2 (вспомогательный столбец) вы можете ввести следующее:

=VALUE(SUBSTITUTE(C2,"""",""))

Четыре кавычки необходимы в качестве второго параметра функции ПОДСТАВИТЬ, чтобы избавиться от кавычек i n C2. В результате D2 содержит числовое значение того, что было в ячейке C2. Скопируйте формулу вниз, насколько это необходимо, а затем используйте столбец D для сортировки. После сортировки вы можете даже скрыть столбец D, если хотите, или сделать его настолько узким, насколько хотите.

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

  1. Откройте диалоговое окно «Параметры Excel». (В Excel 2007 нажмите кнопку «Office», а затем выберите «Параметры Excel. В Excel 2010 или более поздних версиях откройте вкладку« Файл »на ленте и нажмите кнопку« Параметры ».)

  2. Если вы используете Excel 2007, убедитесь, что в левой части диалогового окна выбрано «Популярные». Если вы используете Excel 2010 или более позднюю версию, щелкните «Дополнительно», а затем прокрутите список до конца параметров, пока не увидите область «Общие».

  3. Щелкните Изменить настраиваемые списки. Excel отображает диалоговое окно «Настраиваемые списки» и скрывает диалоговое окно «Параметры Excel». (См. Рис. 1.)

  4. Выберите NEW LIST в списке Custom Lists.

  5. В разделе «Элементы списка» диалогового окна начните вводить элементы в списке в том порядке, в котором они должны отображаться. Например, если у вас есть только 15 возможных значений длины, введите все значения длины в их правильном порядке, обязательно добавляя кавычки после каждой длины. Нажимайте Enter в конце каждой добавляемой длины.

  6. Когда вы закончите, нажмите кнопку «Добавить».

  7. Щелкните OK, чтобы закрыть диалоговое окно Custom Lists. Снова появляется диалоговое окно «Параметры Excel».

  8. Нажмите ОК, чтобы закрыть диалоговое окно Параметры Excel.

После определения настраиваемого списка вы можете использовать диалоговое окно «Сортировка» и указать, что вы хотите выполнить настраиваемую сортировку. Выберите свой новый список, и это то, что Excel будет использовать при расположении строк на листе. Опять же, этот подход действительно работает, только если у вас ограниченное количество длин и вы заранее знаете, какой они будут.

Однако, возможно, лучшее решение — убрать кавычки.

(Да, я знаю …​ Кайл сказал, что не хочет избавляться от них, но потерпите меня на мгновение.) Если все в столбце Длина указано в дюймах, вы можете избавиться от явной цитаты отметки и создайте собственный формат, в котором они будут отображаться. Избавиться от кавычек легко — просто используйте «Найти и заменить», чтобы удалить их. (Найдите кавычки и ничего не замените.) Затем создайте собственный формат следующим образом:

  1. Выделите все ячейки, содержащие длины. (При желании вы можете выбрать весь столбец.)

  2. Откройте вкладку «Главная» на ленте.

  3. Щелкните маленький значок в правом нижнем углу группы номеров.

Excel отображает диалоговое окно «Формат ячеек» с выбранной вкладкой «Число».

  1. В списке категорий выберите Custom. Диалоговое окно изменится, и вы сможете ввести собственный формат. (См. Рис. 2.)

  2. В поле «Тип» введите следующее: 0.0 \ «. Нажмите» ОК «.

Теперь вы должны увидеть все ячейки (выбранные на шаге 1), отображаемые со знаком кавычек в конце. Это формат указанный на шаге 5, который указывает Excel включать кавычки после любого числа. Он также дает указание Excel отображать одну цифру после десятичной точки. (Вы можете изменить формат, чтобы он отображал другое количество цифр, если хотите. Если вы просто хотите, чтобы число было видимым, тогда используйте этот настраиваемый формат:

#\"

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

Опять же, маршрут настраиваемого формата отлично работает, если все в столбце Длина отображается в дюймах. Если это не так ‘ t все в дюймах, то вы можете переместить индикатор размера (кавычки, апостроф и т. д.) в столбец D и удалить его из столбец C. Сделайте столбец D настолько узким, насколько это необходимо для отображения индикатора измерения, а затем отсортируйте по столбцу C.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3917) применим к Microsoft Excel 2007, 2010, 2013 и 2016.