Вопрос или проблема
У нас есть большая таблица Excel с множеством ароматов. Мы используем это для импорта на наш сайт. Многие из них – это один и тот же аромат, но в бутылках разных размеров.
Основной столбец, который мы используем для сортировки, содержит название аромата, за которым следует размер в мл. Это приводит к строке с числом ближе к концу, но не совсем на конце: Fragrance 1 30ml
В Excel 2016, если для конкретного аромата есть бутылка 30ml и 50ml, то они в порядке, потому что первый различающийся символ, “3” и “5”, сортируются правильно. Но, конечно, когда есть бутылки 100ml или 200ml, они сортируются выше бутылок 30ml и 50ml, что делает порядок неправильным. Поскольку сайт импортирует данные напрямую из этой таблицы, нам нужно, чтобы Excel сортировал это правильно, сначала по имени, а затем по увеличивающемуся числу.
Есть ли способ отсортировать их так, чтобы сначала шли самые маленькие (даже если мне нужно добавить формулы в новые столбцы рядом с ним)?
Имейте в виду, что есть много столбцов с другой информацией о каждом продукте, поэтому нужно, чтобы можно было фильтровать, чтобы сохранить другие атрибуты в соответствии с тем же продуктом.
Пример того, как это выглядит:
| Бутылка |
|---|
| Fragrance 1 100ml |
| Fragrance 1 200ml |
| Fragrance 1 30ml |
| Fragrance 1 50ml |
Вам нужно извлечь числа и отсортировать по ним. В данном случае число находится между последним пробелом и “ml”. Так что здесь мы можем использовать формулу:
=SORTBY(A2:A5,--TEXTBEFORE(TEXTAFTER(A2:A5," ",-1),"ml"))
В версии 2016 вы можете использовать вспомогательные столбцы. В первом (столбец G на скриншоте) мы разместили фрагмент, обозначающий базовое имя, во втором (столбец L) – объем упаковки (число). Я использовал формулы:
столбец G: =LEFT(A9,SEARCH("ml",A9)-LEN(L9)-2)
столбец L: =--RIGHT(SUBSTITUTE(LEFT(A9,SEARCH("ml",A9)-1)," ",REPT(" ",10)),10)
Выберите все данные и выполните стандартную сортировку по двум критериям: первый – это имя из столбца G, второй – это число из столбца L.
Вспомогательные столбцы можно скрыть.
Ответ или решение
Вопрос упорядочивания данных в Excel, особенно когда дело касается строк, содержащих числовые значения, может быть довольно сложным. В данном случае мы имеем дело с названиями ароматов, в которых размер бутылки указан в миллилитрах, например, "Fragrance 1 30ml". Это создает определенные трудности при сортировке, так как Excel отсортирует строки в алфавитном порядке, игнорируя числовое значение.
Проблема сортировки строк с числовыми значениями
Проблема заключается в том, что Excel сортирует строки независимо от числовых значений. Поэтому строки "Fragrance 1 100ml" и "Fragrance 1 30ml" будут отсортированы в алфавитном порядке, и "100ml" будет в верхней части списка, в то время как фактический порядок по размеру бутылки выглядит иначе.
Решение через вспомогательные столбцы
Поскольку вы используете Excel 2016, где нет функции SORTBY, вам необходимо создать вспомогательные столбцы, которые помогут разбить строку на составляющие: имя аромата и размер бутылки.
-
Создание вспомогательных столбцов:
- В первом вспомогательном столбце (например, столбец G) мы будем извлекать название аромата.
- Во втором вспомогательном столбце (например, столбец L) мы извлечем числовую часть (размер бутылки).
-
Формулы:
-
В ячейке
G1используйте следующую формулу, чтобы получить название аромата:=LEFT(A1,SEARCH("ml",A1)-LEN(L1)-2)Эта формула находит подстроку до текста "ml".
-
В ячейке
L1используйте:=--RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("ml",A1)-1)," ",REPT(" ",10)),10)Эта формула извлекает числовую часть, используя замену пробелов, чтобы выделить числовые значения.
-
-
Сортировка:
После того, как вы вставили формулы для всех строк, вы можете выбрать весь диапазон данных и выполнить сортировку по двум критериям:- Первая сортировка — по стулбцу
G(название аромата). - Вторая сортировка — по столбцу
L(размер бутылки).
- Первая сортировка — по стулбцу
Утверждение о значении
Для удобства использования вспомогательные столбцы можно скрыть после сортировки. Это позволит вам сохранить структуру данных для веб-импорта, при этом обеспечивая правильную сортировку по размеру бутылок.
Каждый раз, когда вы будете обновлять или добавлять данные, вам просто нужно будет убедиться, что формулы для вспомогательных столбцов копируются в новые строки. Это решение дает вам возможность поддерживать порядок и целостность данных, необходимые для успешного импорта на ваш веб-сайт.
Работа с данными в Excel может быть сложной, но с подходящими инструментами и методами, такими как вспомогательные столбцы, вы можете эффективно решать подобные проблемы сортировки.