Макрос Excel VBA для сортировки данных. Мне не нравится, как регистратор макросов создает дополнительный код для сортировки. Сортировка в Excel VBA должна быть простой. Одна строка кода со столбцом для сортировки, в какую сторону (по возрастанию или по убыванию) и есть ли заголовок.
Смотреть видео
- Регистратор макросов Excel плохо справляется с сортировкой записей.
- При условии, что ваши данные могут быть выбраны с помощью Ctrl + * (известный как Текущая область)
- При условии, что вы не сортируете по цвету, значку или более трех уровней
- Используйте старый метод Range (). CurrentRegion.Sort в Excel
Стенограмма видео
Изучите Excel из подкаста MrExcel, эпизод 2093: Сортировка с помощью VBA
Привет, добро пожаловать обратно в netcast, я Билл Джелен. Сегодняшний вопрос от Джеймса из Хантсвилла. Джеймс, я использовал регистратор макросов для записи действия по сортировке данных. Допустим, Джеймсу нужно было отсортировать эти данные по секторам и клиентам. Итак, вы попадаете на вкладку «Просмотр», «Макросы», «Записать новый макрос», «Сортировка отчетов», «Сочетание клавиш Ctrl» - я набираю там Shift + S и нажимаю «ОК». Хорошо, так что отсюда мы делаем: Данные, Сортировка, и мы хотим сказать, что мы хотим Сортировка по Сектору, а затем Добавить Уровень и Сортировку по Клиенту, и нажмите ОК. Внизу в левом нижнем углу нажимаем Остановить запись. Хорошо, вот оно. Кажется, сработало, правда?
Но вот проблема: завтра у вас будет больше данных, или меньше данных, или что угодно. И этот записанный макрос просто ужасен. Давайте посмотрим, я нажму Alt + F8 и посмотрю на SortMyReport, отредактирую это. Хорошо, это все, что они записали, так что SortFields.Clear, а затем они установили новую сортировку с помощью SortFields.Add, и они жестко запрограммированы, и есть 568 строк и все такое.
Теперь, перебирая в прежние времена, мне было действительно очень легко. Хорошо, а затем в Excel 2007 они добавили «Сортировка по значку», «Сортировка по цвету», «Сортировка по цвету шрифта», возможность иметь 15 уровней сортировки, и все это стало действительно безумным. Итак, я больше не использую записанный макрос. Я просто придерживаюсь старой школы.
Теперь я вернусь в Excel. Вот правила для этой старой сортировки, хорошо. Заголовок над каждым столбцом: этот заголовок должен быть в одной строке, а не в двух. Если у вас есть титулы наверху, и это нормально, чтобы они были там. Вам нужна совершенно пустая строка между вашими заголовками и первым заголовком. Если у вас есть заметки с правой стороны: ваша жена звонит со списком продуктов: «Эй, дорогая, остановись по дороге домой. Возьми молоко, яйца и водку ». Между вашими данными и этим должен быть полностью пустой столбец. И если внизу есть стандартные примечания, убедитесь, что между последним битом данных и этими примечаниями есть совершенно пустая строка.
Вся моя цель состоит в том, чтобы мы могли перейти к любой одной ячейке: в верхней левой ячейке эти данные и нажмите Ctrl + *, и она выберет данные для сортировки. Теперь я собираюсь нажать Ctrl +. это приведет нас к этому углу, а затем Ctrl +. перенесет нас в правый нижний угол, Ctrl +. переносит нас в нижний левый угол. Хорошо, если Ctrl + * правильно выберет ваши данные, тогда все в порядке. Если вы поместите свой список покупок в столбец H, и мы увидим, что подошли сюда и Ctrl + *, что ж, теперь мы сортируем список покупок как часть дела, и ваш список покупок будет испорчен. Или мы отменим: если этой строки нет, теперь мы нажимаем Ctrl + *, видите, мы … теперь они будут невежественны, потому что у них больше нет заголовков, хорошо?
Итак, если вы собираетесь использовать мой код, убедитесь, что все эти правила верны: без сортировки моего цвета, без сортировки моего значка, 3 или менее уровней сортировки. Отменить, хорошо. Итак, вот что мы знаем: мы знаем, что каждый день наши данные будут начинаться с A5. Если мы не знаем, сколько строк или сколько - хорошо, даже сколько столбцов у нас может быть. Я не могу представить ситуацию, когда столбцы меняются местами, но, безусловно, количество строк изменится. Итак, Alt + F11, мы просто начнем с этой ячейки в верхнем левом углу. Итак, Range, в моем случае это «A5» .CurrentRegion. Текущий регион - это потрясающее свойство здания, в котором говорится, что мы собираемся нажать Ctrl + Shift + *, и все, что там есть, будет отсортировано. И делаем .Sort. .Сортировать, хорошо.
Вот в чем дело. Если вы хотите сделать одноуровневую сортировку, это просто: Key1: =. : = и мы просто говорим, что это будет Range - Ой, я забыл, что это такое. Это был Сектор, а где Сектор? Сектор находится в столбце C. Итак, C5 в моем случае, Range («C5»), а затем Order1: = xlAscending. Я нажал там клавишу со стрелкой вниз, а затем Tab. Хорошо, я мог бы продолжать движение вправо, но я не собираюсь этого делать. Я собираюсь перейти на новую строку, поэтому пробел, подчеркивание, чтобы перейти на новую строку, продолжить эту строку кода, хорошо? И если у меня есть сортировка второго уровня: Key2: =, и в этом случае я хочу отсортировать по клиенту, который находится в столбце D, поэтому D5. А затем Order2: xlAscending. Прекрасный.
У меня нет сортировки третьего уровня, но если бы у вас был, это был бы Key3, а затем Order3. А следующий, который вам нужно сделать, это заголовок, хорошо? Итак, Header: = xl Думаю, именно здесь у вас чертовски много проблем. И поэтому мы собираемся сказать xl Да, определенно в качестве заголовка. Даже в старые времена средство записи макросов использовало xlGuess. Ненавижу угадывать Excel.
Вот и все. Одна строка кода - это все, что вам нужно сделать, и он будет работать с большим количеством строк, меньше строк. Это красивая, красивая вещь. Хорошо, мы вернемся сюда в Excel. Ctrl + Shift + S по-прежнему назначен. Это сейчас - если вы только что переключились на VBA и набираете это самостоятельно, вы можете перейти к Alt + F8, найти имя вашего макроса, нажать `` Параметры '' и ввести там Ctrl + Shift + S, или мы даже можем назначить его Сочетание клавиш здесь, на панели быстрого доступа. Щелкните правой кнопкой мыши, выберите «Настроить панель быстрого доступа», где я выбираю один из наших макросов. У меня есть макрос под названием SortMyReport, я нажимаю «Добавить» - мне не нравится эта маленькая блок-схема. Мы изменим это, и я хотел бы, чтобы там была какая-то ситуация от А до Я, но, конечно, нет. Может, тот стрелок, кто знает, кто знает, просто выберет что угодно.Волшебный шар-восьмерка, не знаю. Я собираюсь выбрать здесь этого человечка, нажмите ОК, нажмите ОК. Хорошо, теперь наши данные отсортированы по дате, я выбираю - и не имеет значения, что я выбираю. Он всегда будет возвращаться и отсортировать его из A5, я нажимаю на маленького человечка, и мои данные теперь сортируются по секторам, внутри секторов, по клиентам. Отлично работает, хорошо?
Так что, если вы поклонник Macro Recorder, мои сердечные пожелания вам всего наилучшего. Но в наши дни код Macro Recorder для сортировки-сортировки в VBA намного проще; чтобы просто вернуться, просто используйте по существу эту, одну строку кода.
Что ж, обычно это то место, где я пытаюсь уговорить вас купить эту книгу, но сегодня я думаю, вам стоит взглянуть на эту книгу: Excel 2016 VBA и макросы от Трейси и меня. Вау! Проверь это. Я не знал, что есть версия на другом языке. Мы поможем вам полностью подняться по кривой обучения макросам от записи вашего первого макроса до необходимого кода.
Что ж, простое подведение итогов на сегодня: средство записи макросов Excel плохо справляется с записью, сортировкой: при условии, что ваши данные могут быть выбраны с помощью Ctrl + *, который известен как текущий регион, при условии, что вы не сортируете по цвету или значку или более трех уровней, просто используйте старый метод Range (). CurrentRegion.Sort в VBA для сортировки.
Я хочу поблагодарить Джеймса за этот вопрос. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2093.xlsm
