У Кайла есть рабочий лист, состоящий из трех столбцов данных: номер детали, количество и длина. Длина обозначается в дюймах с кавычками, чтобы показать, что она указана в дюймах (например, 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, «» «», «»))
Четыре кавычки необходимы в качестве второго параметра функции SUBSTITUTE, чтобы избавиться от кавычек в C2. В результате D2 содержит числовое значение того, что было в ячейке C2. Скопируйте формулу вниз, насколько это необходимо, а затем используйте столбец D для сортировки. После сортировки вы можете даже скрыть столбец D, если хотите, или сделать его настолько узким, насколько хотите.
Другой подход, который может хорошо работать, если у вас ограниченное количество длин, – это создание настраиваемого списка а затем используйте этот список для сортировки. Вот как настроить настраиваемый список для вашей длины:
- Отобразите диалоговое окно «Параметры Excel». (В Excel 2007 нажмите кнопку Office, а затем щелкните Параметры Excel. В Excel 2010 или более поздних версиях откройте вкладку Файл на ленте и нажмите Параметры.)
- Если вы используете Excel 2007, убедитесь, что в левой части диалогового окна выбрано «Популярные». Если вы используете Excel 2010 или более позднюю версию, нажмите «Дополнительно», а затем прокрутите список до конца параметров, пока не увидите область «Общие».
- Нажмите «Изменить настраиваемые списки». Excel отображает диалоговое окно “Настраиваемые списки” и скрывает диалоговое окно “Параметры Excel”. (См. Рис. 1.)
- Выберите НОВЫЙ СПИСОК в списке настраиваемых списков.
- В части диалогового окна «Записи списка» начните вводить элементы в своем списке в том порядке, в котором они должны появляться. Например, если у вас есть только 15 возможных длин, введите все длины в их правильном порядке, не забудьте поставить кавычки после каждой длины. Нажимайте Enter в конце каждой добавляемой длины.
- По завершении нажмите кнопку “Добавить”.
- Нажмите “ОК”. , чтобы закрыть диалоговое окно Custom Lists. Снова появляется диалоговое окно параметров Excel.
- Нажмите кнопку ОК, чтобы закрыть диалоговое окно параметров Excel.
Рис. 1. диалоговое окно “Пользовательские списки”.
После определения настраиваемого списка вы можете использовать Диалоговое окно «Сортировка» и укажите, что вы хотите выполнить настраиваемую сортировку. Выберите свой новый список, и это то, что Excel будет использовать при расположении строк на листе. Опять же, этот подход действительно работает только в том случае, если у вас ограниченное количество длин и вы заранее знаете, какой они будут.
Однако, возможно, лучшим решением является удаление кавычек. (Да, я знаю … Кайл сказал, что не хочет избавляться от них, но потерпите меня на мгновение.) Если все в столбце Длина отображается в дюймах, то вы можете избавиться от явной цитаты. отметки и создайте собственный формат, в котором они будут отображаться. Избавиться от кавычек легко – просто используйте команду «Найти и заменить», чтобы удалить их. (Найдите кавычки и ничего не замените.) Затем создайте настраиваемый формат следующим образом:
- Выделите все ячейки, содержащие длины. (При желании можно выделить весь столбец.)
- Отобразить вкладку «Главная» на ленте.
- Щелкните маленький значок в правом нижнем углу окна. Группа номеров. Excel отображает диалоговое окно «Формат ячеек» с выбранной вкладкой «Число».
- В списке «Категория» выберите «Пользовательский». Диалоговое окно изменится, и вы сможете ввести собственный формат. (См. Рис. 2.)
- В поле «Тип» введите следующее: 0,0 “
- Нажмите OK.
Рис. 2. Вкладка «Число» диалогового окна «Формат ячеек».
Теперь вы должны увидеть все ячейки (выбранные на шаге 1), отображаемые с конечной кавычкой. Это формат, указанный на шаге 5. который указывает Excel включать кавычки после любого числа. Он также дает указание Excel отображать одну цифру после десятичной точки. (Вы можете изменить формат, чтобы он отображал другое количество цифр, если хотите. Если вы просто хотите, чтобы число быть обычно видимым, затем используйте этот настраиваемый формат:
# »
Как и в случае с настраиваемым форматом на шаге 5 только что показанных шагов, обратная косая черта необходима, поэтому что Excel знает, что кавычка является частью самого настраиваемого формата.
Опять же, маршрут настраиваемого формата отлично работает, если все в столбце Длина отображается в дюймах. Если это не все в дюймах, вы можете переместить индикатор размера (кавычки, апостроф и т. Д.) В столбец D и удалить его из столбца C. Сделайте столбец D настолько узким, насколько это необходимо для отображения индикатора размера, а затем сортировать по столбцу C.
Источник: