Excel сортировка числа с буквами

{quote}{login=}{date=30.08.2012 09:16}{thema=}{post}Не знаете ли вы случайно, какой задать формат столбцу, чтобы номера с буквами выглядели так же, как и с цифрами (сейчас у меня «# ###» — номера без букв выглядят так: 14 534, а с буквами 14534A (но это, конечно, не существенно…){/post}{/quote}  
Текст можно так отформатировать только макросом, при этом лучше использовать моноширинный шрифт, например, Courier New. Код такой:  

  Sub MySort()  

   ‘ —> Константы для настройки (вписать реальные)  
 Const DataTopRow = «A2:BW2» ‘ первая строка с данными  
 Const SortColumn = «A»      ‘ сортируемый столбец  
 ‘ <— Конец констант для настройки  

   Dim c&, cs&, r&, rs&, s$  
 Dim a(), b()  
 Dim LastCell As Range, Rng As Range  

   ‘ Определить диапазон данных  
 With ActiveSheet.Range(DataTopRow)  
   c = .EntireRow.Columns(SortColumn).Column — .Column + 1  
   Set LastCell = .Parent.Cells.SpecialCells(xlCellTypeLastCell)  
   rs = LastCell.Row — .Row + 1  
   If rs < 2 Then MsgBox «Нет данных»: Exit Sub  
   Set Rng = .Resize(rs)  
 End With  

   ‘ Создать в a() числовые значения сортируемого столбца, а в b()-отформатированные  
 a() = Rng.Columns©.Value  
 b() = a()  
 For r = 1 To rs  
   If VarType(a(r, 1)) = vbString Then  
     s = Right$(a(r, 1), 1)  
     a(r, 1) = Val(a(r, 1))  
     If s >= «A» Then  
       b(r, 1) = Format(a(r, 1), «# ###») & s  
       a(r, 1) = a(r, 1) + Asc(s) * 0.001  
     End If  
   End If  
 Next  

   ‘ Отключить перерисовку экрана  
 Application.ScreenUpdating = False  

     ‘ Отформатировать сортируемый столбец, скопировав в него b()  
 With Rng.Columns©  
   .Value = b()  
   .Font.Name = «Courier New» ‘ моноширинный шрифт  
   .HorizontalAlignment = xlRight  
   .NumberFormat = «# ###_.»  
 End With  

     ‘ Скопировать a() правее рабочего диапазона, отсортировать и очистить  
 With Range(Rng, LastCell)  
   cs = .Columns.Count + 1  
   With .Resize(, cs)  
     .Columns(cs).Value = a  
     .Sort .Cells(1, cs), xlAscending, Header:=xlNo  
     .Columns(cs).ClearContents  
   End With  
 End With  

     ‘ Включить перерисовку экрана  
 Application.ScreenUpdating = True  

   ‘ Восстановить UsedRange  
 With Rng.Parent.UsedRange: End With  

  End Sub

Сортировать одновременно числа и текст в таблице Excel.

​Смотрите также​ — Текстовый —​​ становится. оказывается. макросы​ пределами используемых ячеек​s = Right$(a(r,​ прощения за предыдущий​ Sub​
​ 534, а с​ получить анализируя буквы).​ текст в любом​ получить список, где​ выбрать тип сортировки​ изображены буквы «А»​ отмечаем галочкой «Мои​ и целой таблицы:​ адресность в ссылках​ сортировка». В появившемся​Как настроить сортировку в​ Готово​​ могут работать десятые​​ (UsedRange), например, запишем​ 1), 1)​ пост, не обратил​Set Rng =​ буквами 14534A (но​
​Вероятно, лучше код​ случае​ после каждого крупного​Настраиваемый список (Custom List)​ и «Z» со​ данные содержат заголовки​Выделите диапазон значений столбца​ и тогда результаты​ окне нажимаем кнопку​ Excel​
​А затем обычная​ доли секунды и​ в ячейку что-нибудь,​
​a(r, 1) =​ внимания, что файл​ .Resize(rs)​ это, конечно, не​ на RegExp -​например, вставьте «​ агрегата будут идти​:​
​ стрелочкой​ столбцов» и указываем:​ который следует отсортировать,​ их вычислений будут​ «Параметры…» и ставим​по нашим правилам?​ сортировка по возрастанию​ делать при этом​ а затем очистим​ Val(a(r, 1))​ «распух» после экспериментов)​End With​ существенно…)​ это не ко​ » перед цифрами​ его детали:​
​После этого откроется следующее​
​Ma666oT​ «Столбец» – Чистая​ например «Расход» (в​ ошибочны. Во-вторых, после​ галочку у строки​ Обычная соритровка производится​Russel​ гораздо больше и​ ее содержимое, то​If s >=​ЗЫ. Формат ячеек​’ Создать в​Vlad​ мне :(​ и сортируйте​
​Чтобы это реализовать, нужно​ окно, в котором​: не, не, не​
​ прибыль; «Сортировка» –​ данном случаи это​ многократных сортировок можно​ «Учитывать регистр». Нажимаем​ по цифрам, словам.​: Еще вариант, с​ правильнее.​ по Ctrl-End мы​
​ «A» Then​
​ «# ;» замените​ массиве a() числовыне​
​: andre, а у​А может даже​andre532​ временно добавить еще​ можно выбрать нужную​так я уже​ Цвет ячейки; «Порядок»​ диапазон E1:E11).​
​ перетасовать данные таблицы​ «ОК».​ Смотрите статью «Сортировка​ разбивкой на разряды​спасибо форуму -​ уже попадем в​b(r, 1) =​ на Общий.​ значения сортируемого столбца​ Вас в конце​

excel-office.ru

Сортировка Числовых Значений в Excel. Бесплатные примеры и статьи.

​ и стандартными функциями​: Увы, не помогло…​ один столбец к​ нам последовательность месяцев​ пробовал, не получается….вы​ – красный, сверху.​Щелкните правой кнопкой мышки​ так, что уже​В строке «Сортировать по»​ в Excel».​ по 5 для​ раньше я думал,​ эту очищенную (новую​ Format(a(r, 1), «#​Guest​a() = Rng.Columns©.Value​ может быть только​ спецы выкрутятся? :)​ С апострофом впереди​ нашей таблице, в​ или дней недели:​ просто не видели​

excel2.ru

Как сделать сортировку в Excel по возрастанию и по убыванию

​ И нажмите ОК.​ по выделенному столбцу.​ сложно будет вернуться​ указан адрес дополнительного​Но число с​ более удобного просмотра​ что я вумный,​ последнюю) ячейку. Если​ ;») & s​: Вот мне счастье​

​For r =​ одна буква или​Затем по этому​ тоже…​ котором превратить все​Если нужного списка (например,​ мой документ)​Сверху у нас теперь​ В контекстном меню​ к изначальному ее​ столбца.​ точкой (600.1), Excel​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЛЕВСИМВ(A2;5)&» «&ПРАВСИМВ(A2;5)​ а теперь оказывается​ сохранить книгу, то​a(r, 1) =​ привалило-то!​ 1 To rs​ возможно несколько? С​

Сортировка данных в Excel

​ столбцу сортировать.​Guest​ коды в текст​ месяцев, но на​0nega​ наихудшие показатели по​

​ выберите опцию «Сортировка»-«от​ виду. В третьих,​«Сортировка» — выбираем​

1. ​ сортирует не в​Chase​
2. ​ — практически дурак​ последняя ячейка возвращается​ a(r, 1) +​Уважаемые ZVI и​If VarType(a(r, 1))​ одной можно будет​Hugo​: странно​ с помощью функции​

​ английском) нет, то​: вы меня заинтриговали​ чистой прибыли, которые​ минимального к максимальному»​ если таблица содержит​ «Значения». Нажимаем «ОК».​ том порядке, в​: Попробовал, но не​ :)​

​ на свое законное​ Asc(s) * 0.001​ Влад, еще раз​

1. ​ = vbString Then​ просто сделать и​
2. ​: MCH буквы по​Hugo​
3. ​ ТЕКСТ:​ его можно ввести​ … а нельзя​ имеют наихудшие показатели.​Появится диалоговое окно «Обнаруженные​ объединенные ячейки, то​ В появившемся окне​ каком нам нужно.​ выходит, в итоге​Казанский​ место, то же​End If​

​ спасибо!​a(r, 1) =​ точно быстрее 40​

​ алфавиту не сортирует!​

Как в Excel сделать сортировку в столбце

​: С апострофом нормально​Если затем произвести сортировку​ в правое поле,​ ли представить на​

1. ​Примечание. Дальше можно выделить​ данные вне указанного​ следует их аккуратно​ ставим галочку у​ Сначала размещает в​
2. ​ сперва идут только​: Владимир,​ самое обычно происходит​End If​Оба кода очень​
3. ​ Val(a(r, 1))​ сек, а с​ :(​ сортирует, только не​ по этому столбцу,​ выбрав опцию​ суд местных Гуру​ в этой таблице​ диапазона». По умолчанию​ разъединить, так как​ строки «числа и​ столбце все целые​ цифровые значения, а​в данном контексте​

​ при обращении к​Next​

Сортировка по цвету ячейки в Excel

​ быстро работают, почти​End If​ несколькими сложнее…)​Мой алгоритм должен​ нужно добавлять лишних​ то Excel спросит​Новый список (New List)​ кусочек Вашего чуда?​ диапазон A4:F12 и​ там активна опция​ для сортировки такой​ числовые данные в​ числа (600,700), затем​ потом с символами​

1. ​ можно написать просто​ UsedRange.​’ Отключить перерисовку​ мгновенно (кажется код​Next​
2. ​Guest​ помочь :)​ пробелов :)​ о том, как​:​глядишь, и для​
3. ​ повторно выполнить второй​ «автоматически расширять выделенный​ формат является не​ текстовом формате раздельно».​ все числа с​Russel​.UsedRange​Код для проверки:​ экрана​ Влада на пару​’ Скопировать a()​
4. ​: В конце только​MCH​

​Hugo​ ему сортировать числа​В качестве разделителя можно​ себя «изюм «​

1. ​ пункт этого раздела,​ диапазон». Программа пытается​
2. ​ приемлемым.​ Нажимаем «ОК».​ точкой (600.1, 600.2,​: Все работает, формат​, ничего с​Sub Test()​Application.ScreenUpdating = False​ миллисекунд дольше (а​ во вспом. столбец​

​ одна буква: A,B,C,D,E…​: Я макрорекордером записал​: Нет, извиняюсь, не​ и текст:​

​ использовать​ выковыряю​ только указать розовый​ охватить все столбцы​Какими средствами располагает Excel​Получилось так.​ 700.1). А нам​ текстовый выбрали?​ ним не делая.​With Workbooks.Add.Sheets(1)​’ Отформатировать сортируемый​

exceltable.com

как упорядочить цифры по возрастанию

​ может, показалось:)).​​ правее рабочего диапазона,​
​ Причем буквы появляются​ сортировку по одному​ правильно.​
​Если выбрать второй вариант​запятую​DYm00n​ сверху. Таким образом​
​ и выполнить сортировку​
​ для сортировки данных?​
​Теперь дополнительный столбец можно​
​ нужно​
​Chase​
​То есть можно​
​’ Добавим данные​
​ столбец, скопировав в​
​Тему считаю закрытой.​ отсортировать и очистить​
​ по порядку для​ столбцу, а нужно​А вот если​ в этом диалоговом​или клавишу​:​ в первую очередь​

​ как в предыдущем​​ Чтобы дать полный​
​ удалить или скрыть.​-​
​: А есть ли​ использовать конструкцию Sheets(x).UsedRange​ на чистый лист​

​ него b()​​ Всем приятной ночи!​
​Application.ScreenUpdating = False​ каждого номера​
​ было по двум,​ вместо пробела добавить​ окне, то Excel​Enter​

​Ma666oT​​ пойдут ячейки с​ примере. Но в​
​ ответ на этот​
​Как разместить данные​целое число и​ возможность написать порядок​ или ActiveSheet.UsedRange.​.Range(«D2:E5») = «UsedRange»​With Rng.Columns©​ZVI​

​With Range(Rng, LastCell)​​Vlad​
​ вначале по B​ 0 (т.е.0511, 0511A)​ не будет преобразовывать​. Единожды создав такой​

​, при сортировке можно​​ цветом, а после​ этот раз выберите​ вопрос рассмотрим его​ по алфавиту, цифры​ ниже этоже целое​
​ сортировки?​Но в коде​’ Проверим адрес​

​.Value = b()​​: Текст можно так​​cs = .Columns.Count​​: По порядку или​ затем по A​ — то ОК.​ номера крупных агрегатов​

​ пользовательский список, вы​​ выбрать чтобы ТОЛЬКО​ обычные.​ опцию «сортировать в​

​ на конкретных примерах.​​ по возрастанию, уменьшанию,​ число, но с​К примеру: 1,2,3…9,​

CyberForum.ru

Хитрости сортировки

​ листа нельзя написать​ последней ячейки​.Font.Name = «Courier​ отформатировать только макросом,​ + 1​ нет — это​

Случай 1. Сортировка по смыслу, а не по алфавиту

​в примере не​Useful​ в числа и​ сможете им пользоваться и в​ выделенный столбик сортировался​Ma666oT​ пределах указанного диапазона».​Подготовка таблицы для правильной​ используя автоматическую сортировку​ точкой (600, 600.1,​ A, B, C…Z?​ просто Me.UsedRange -​

​Debug.Print 1, .Cells.SpecialCells(xlCellTypeLastCell).Address​ New» ‘ моноширинный​ при этом лучше​With .Resize(, cs)​ неважно. Предлагаю вот​ было различных букв​: try this​ станет сортировать весь​​ других книгах Excel.​ или ВСЕ смежные​​: Здравствуйте!​

​ И нажмите ОК.​ и безопасной сортировки​​ в Excel, смотрите​​ 600.2, 700, 700.1).​​Russel​​ Compile error: Invalid​’ Запишем и​ шрифт​ использовать моноширинный шрифт,​.Columns(cs).Value = a​ такой вариант (красивости​​ для одного и​​=MID(TRIM(I2),1,COUNT(IF(—ISNUMBER(—MID(TRIM(I2),ROW(INDIRECT(«1:»&LEN(I2))),1))*ROW(INDIRECT(«1:»&LEN(I2)))<>0,—ISNUMBER(—MID(TRIM(I2),ROW(INDIRECT(«1:»&LEN(I2))),1))*ROW(INDIRECT(«1:»&LEN(I2))))))​

​ список как текст,​Интересный нюанс состоит в​0nega​Есть проблема: нужно​Столбец отсортирован независимо от​

​ данных:​ в статье «Сортировка​У нас есть​: Поиграйтесь с этим:​ use of property.​ удалим что-нибудь в​​.HorizontalAlignment = xlRight​​ например, Courier New.​

​.Sort .Cells(1, cs),​ от ув. MCH,​​ того же числа,​​andre532​​ что и даст​​ том, что таким​: условия сортировки он​ упорядочить цифры по​ других столбцов таблицы.​

​Выделяем и копируем всю​ в Excel формулой»​ таблица с перечнем​ Сортировка и фильтр​ Приходится писать​ ячейке правее и​.NumberFormat = «#​ Код такой:​ xlAscending, .Cells(1, c),​ надеюсь он не​

• ​ поэтому как то​: Да, там действительно​
• ​ нам нужный результат.​ образом можно сортировать​
• ​ задал еще в​ возрастанию.​
• ​При копировании таблицы на​ таблицу.​ здесь.​ товара, который состоит​
• ​ — порядок —​

Случай 2. Сортировка текста и чисел одновременно

​With Me.UsedRange: End​ ниже данных​ ;_.»​Sub MySort()​ , xlAscending, Header:=xlNo​ возражает:).​ упустил​ много странного… Я​ Вспомогательный столбец потом,​ не тупо по​ первом посте​Экселевский документ редактировался​ отдельный лист мы​На другом чистом листе​Сортировка в таблице​ из комплектов. В​ настраиваемый список… —​ With​.Range(«F10»).Value = 1​End With​’ —> Константы​.Columns(cs).ClearContents​ЗЫ. Если используются​MCH​ скопировал номера из​

​ конечно же, можно​ алфавиту, а по​Ma666oT​ и поэтому цифры​ переносим только ее​

​ (например, Лист2)щелкаем правой​ Excel проводится по​ каждом комплекте (в​ Добавить​или​.Range(«F10»).ClearContents​’ Скопировать a()​ для настройки (вписать​

​End With​ только прописные англ.​: .​ реальной базы, м.б.​ удалить.​ значимости и важности​

​: У меня уже​ идут вот в​ значения с помощью​ кнопкой мышки по​ определенным спискам. Но​ таблице обозначен цифрой)​Pelena​Sheets(Me.Name).UsedRange​’ Адрес последней​ правее рабочего диапазона,​ реальные)​End With​ буквы, в коде​

planetaexcel.ru

Сортировка цифр с буквами

​With Range(Rng, LastCell)​​ «A2:BW2» ‘ первая​

​’ Восстановить UsedRange​​ на 100.​ Громадное спасибо!​ (Там формат «#​ помощью макроса отсортировывать​ месяцы или дни​

​Сортировка — функция Excel до боли​​2​ нам сортировать не​ опцию: «Специальная вставка».​ будут сортироваться данные.​
​ цифрой с точкой).​ не выходит​Chase​

​’ Восстановим правильное​​cs = .Columns.Count​ строка с данными​With Rng.Parent.UsedRange: End​

​Файл удален​​У меня на​

​ ;»)​​ список из серийных​ недели. Например:​ знакомая и привычная почти​3​

​ только по значениям,​​ В параметрах отмечаем​ Как это сделать,​
​ Таблица такая.​Chase​: Добрый день!​ положение последней ячейки,​

​ + 1​​Const SortColumn =​
​ With​

​ департамента, начальник отдела…)​​ есть несколько нестандартных​7​
​ цветам шрифта или​ ОК.​ «Как настроить сортировку​

​ обычным способом, то​​1061000500​

​ ее применения.​​9​ нам нужно еще​ содержит формул, а​
​Отсортируем формулами таблицу, состоящую​А нам нужно, чтобы​

​1470167730​​ учитывались цифры слева​ With​ xlAscending, Header:=xlNo​Dim c&, cs&,​ столбцы после пустых​ZVI​
​Работа макроса занимает​ все-таки как-то макросом​ в конце?​сертификации (TOEFL, ITIL, MCP,​Представим весьма распространенную ситуацию:​6​

​ переносить и форматы​​ только результаты их​

​ из 2-х столбцов.​​ в таблице стоял​1820249870​
​ на право. В​’ Адрес последней​.Columns(cs).ClearContents​ r&, rs&, s$​ колонок…​
​: Такой вариант:​ около 40 сек,​ решить эту проблему?​
​Т.е. есть возникает​ MVP…)​ есть таблица, в​5 и т.д.​ данных. Для этого:​ вычисления. Так же​ Сортировку будем производить​ сначала код комплекта​
​2103403020​ случае появление буквенных​
​ ячейки вернется на​End With​Dim a(), b()​По-видимому сортируется только​Sub MySort()​
​ но это мелочи.​ Чтобы Excel при​ список:​клиентов или товары по важности лично​
​ которой присутствует столбец​ и т.п.​Вернемся к нашей исходной​ разъединены объединенные ячейки.​ по одному из​ (650), под ним​4804324000​ значений, они занимали​ свое место​End With​Dim LastCell As​ непрерывная область, а​’ —> Константы​Насколько я понимаю,​ сортировке понимала, что​

​511​​ для вас (виски,​ с названием месяца​Нужно сделать,что бы​ таблице на Лист1​ Осталось убрать лишний​ столбцов таблицы (решим​ коды комплектующих (650.1,​8628504020​ место соответственно цифрам​Debug.Print 3, .Cells.SpecialCells(xlCellTypeLastCell).Address​

​’ Включить перерисовку​​ Range, Rng As​ в реальной базе​ для настройки (вписать​ мне для его​ впереди — это​

​4512​​ текила, коньяк, вино,​ (январь, февраль, март…) или​ они шли по​ и снова полностью​ текст в заголовках​ 2 задачи: сортировка​ 650.2, 650.4). Затем​
​126032R150​ перед буквой​End With​ экрана​ Range​
​ есть пробелы в​​ реальные)​ работы необходимо превратить​​ цифры и нужно​​4513​

​ это?​​ на копии таблицы​ выполните следующее:​ чтобы при добавлении​
​ текст, а цифры​15T412560V​ строчек, а рядом​спасибо и за​End Sub​ — .Column +​

​ порядку, а затем​​ автомобилей (part number).​ до Я):​: добро времени суток​ (Лист3) и выберите​ охватывает исходная таблица.​ исходную таблицу, сортированная​ как цифры. Нам​5677253WM0​
​Chase​ пример.​: упс…​

planetaexcel.ru

Проблемы сортировки число-буквенных артикулов (Формулы/Formulas)

​: на посошок еще​​Теперь копия таблицы содержит​
​Данные отсортированные по всей​ таблица содержит сложные​ на весь столбец.​Pelena​: Можно просто перевести​ я писал три​: Александр, в коде​ в b()-отформатированные​ 14534A (но это,​

​rs = LastCell.Row​​ так же, как​ которая будет возвращать​
​ шел такой же​ числа (номера крупных​Данные (Data — Sort)​

​ один способ​​ значения и форматы.​ таблице относительно столбца​ формулы и функции,​ В этом, дополнительном​: А отсортировать-то дальше​

​ столбец А в​​ года назад и​ используется SpecialCells(xlCellTypeLastCell) –​

​a() = Rng.Columns©.Value​​ конечно, не существенно…)​ — .Row +​ и с цифрами​ нормальные числа -​

​ номер с дополнительной​​ агрегатов в сборе)​. Откроется диалоговое окно,​2-й способ​ Выполним сортировку по​ «Чистая прибыль».​ то операцию сортировки​ столбце проводим сортировку​ пробовали?​ текстовый формат: Данные​
​ был очень доволен,​ последняя используемая ячейка,​

​b() = a()​​Vlad​ 1​ (сейчас у меня​ целые для «голых»​
​ буквой в конце​

​ и отдельно текст​​ в котором нужно​выделите только цифровой​ цветам:​​ лучше выполнять на​

​ так. Выделяем оба​​Chase​ — Текст по​

​ что за 5-10​​ на которую устанавливается​For r =​: Немного исправил код​
​If rs <​ «# ;» -​

​ чисел и дробные​​Guest​ (номера мелких деталей​ задать поле (столбец)​ столбик и нажмите​Выделите таблицу и выберите​

​Теперь отсортируем только один​​ копии этой таблицы.​ столбца, нажимаем кнопку​: Со второго раза​

​ столбцам — Далее​​ минут они выполняют​
​ курсор по нажатию​
​ 1 To rs​
​ с учетом Ваших​
​ 2 Then MsgBox​
​ номера без букв​
​ для чисел с​
​: нужно, чтобы артикул​
​ с точками):​
​ сортировки и в​
​ «сортировка по возрастанию»​
​ инструмент «Данные»-«Сортировка».​
​ столбец без привязки​
​Во-первых, в формулах и​
​ «Сортировка и фильтр»,​
​ получилось, премного благодарен​
​ — Далее (галки​ то, что другие​ Ctrl-End.​If VarType(a(r, 1))​ пожеланий (работает без​ «Нет данных»: Exit​ выглядят так: 14​ буквами (значение дроби​ воспринимался эксом как​
​А хотелось бы, конечно,​ последнем раскрывающемся списке​ (значок, на котором​В параметрах сортировки снова​ к другим столбцам​

​ функциях может нарушиться​​ выбираем функцию «Настраиваемая​ за помощь!!​

​ поснимать, если есть)​​ делают пол-дня… смешно​Если где-нибудь за​ = vbString Then​

excelworld.ru

​ доп. столбца). Прошу​