Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сортировка макросом в excel

Канал в Telegram

Вы здесь

Сортировка листов в Excel с помощью макроса

В этом уроке разберем один из способов сортировки листов по алфавиту.

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

В MS Excel нет встроенного средства для такой сортировки, создадим простой макрос для упорядочивания листов.

Добавим в нашу рабочую книгу лист и в первом столбце этого листа запишем все названия листов данной книги. Отсортируем диапазон с именами листов, переместим листы согласно сортировке и готово!

Теперь подробнее разберем используемые методы в алгоритме​

синтаксис Worksheets.Add (Before, After, Count, Type) — добавление листов в книгу Excel

After— указывает лист, после которого будет добавлен рабочий лист

​​Before— указывает лист, перед которым будет добавлен рабочий лист

Count — количество добавляемых листов, по умолчанию 1

Type — тип рабочего листа, по умолчанию xlWorkSheet

​Если Before и After опущены, то по умолчанию лист размещается после активного листа

​синтаксис Worksheets.Move (Before|After) — перемещение рабочего листа в другое место рабочей книги Excel

After— указывает лист, после которого будет перемещен рабочий лист

​​Before— указывает лист, перед которым будет перемещен рабочий лист

​Одновременно может быть указан только один аргумент (After или Before)

Воспользуемся встроенной возможностью Excel – сортировкой диапазона.

ДиапазонТаблицы.Sort ([Key1], [Order1 As XlSortOrder = xlAscending], [Key2], [Type], [Order2 As XlSortOrder = xlAscending], [Key3], [Order3 As XlSortOrder = xlAscending], [Header As XlYesNoGuess = xlNo], [OrderCustom], [MatchCase], [Orientation As XlSortOrientation = xlSortRows], [SortMethod As XlSortMethod = xlPinYin], [DataOption1 As XlSortDataOption = xlSortNormal], [DataOption2 As XlSortDataOption = xlSortNormal], [DataOption3 As XlSortDataOption = xlSortNormal])

ДиапазонТаблицы — диапазон Range для сортировки

Key1 — первое упорядочиваемое поле

Order1 — порядок сортировки, xlAscending- по возрастанию, xlDescending- по убыванию

Header — заголовок (xlNo — отсутствует, xlYes — есть, xlGuess — Excel определяет сам)

OrderCustom — пользовательский порядок сортировки, по умолчанию Normal

MatchCase — True или False (учитывается или нет регистр)

Orientation — направление сортировки, xlSortRows — по строкам, xlSortColumns — по столбцам

Диапазон до сортировки

Диапазон после сортировки

Application.DisplayAlerts = False отключение оповещений

Без этой команды при выполнении макроса перед удалением листа появится сообщение

Сортировка диапазона

Процедура для сортировки диапазона по любому количеству ключевых полей (строк или столбцов).

sh — лист, на котором происходит сортировка. Если это текущий лист, то укажите ActiveSheet.
fisrt_row, first_col — координаты первой ячейки (в верхнем левом углу диапазона)
last_row, last_col — координаты последней ячейки (в нижнем правом углу диапазона)

Header — содержит ли диапазон заголовки? False/True
Column — сортировать по столбцам (True) или по строкам (False)?

fields — массив, содержащий номера столбцов или строк (в стиле нумерации R1C1) по которым происходит сортировка. Порядок следования в массиве отражает приоритет ключевого поля — сначала сортируется по первому указанному в массиве полю, потом по второму, затем по третьему и т.д. Игнорируются нули и нечисловые элементы, а также ключевые поля не содержащиеся в диапазоне. Дробные числа приводятся к целым.
Значения полей можно указывать отрицательными! В этом случае номер ключевого поля — модуль элемента, данное поле будет сортироваться по убыванию.

Теперь покажу, как это чудовище применяется. Допустим, есть диапазон 15 на 15, заполненный в случайном порядке нулями и единицами. Нужно отсортировать его в первую очередь по первому столбцу диапазона, потом по второму, потом по третьему и так до самого последнего столбца.

Чтобы было нагляднее, добавим строку и столбец с вертикальной и горизонтальной нумерациями. Теперь будет легко отследить, как именно отсортировался массив. Они не будут ключевыми полями, но при этом войдут в сортируемый диапазон.

Отсортируем по столбцам. Для этого заполним массив числами от 2 до 16 (номера столбцов на листе) и передадим этот массив в функцию. Укажем что диапазон с заголовками — таковые находятся в строке с нумерацией столбцов.

Как видите, в первом столбце диапазона (не забывайте, что первый столбец диапазона это не первый столбец листа) нули и единицы упорядочены. Это в свою очередь разбивает весь диапазон на два горизонтальных поддиапазона: обратите внимание в первой колонке на нули со 2-й строки по 6-ю и на единицы с 7-й по 16-ю. Оба поддиапазона фактически сортируются по следующему столбцу, в их пределах в следующем столбце тоже наблюдаем сортировку по нулям и единицам. Это в свою очередь разбивает поддиапазоны на ещё более мелкие поддиапазоны, которые сортируются по следующему столбцу и так далее. При этом по нумерации строк диапазона можно проверить, что в строках элементы находятся на своих местах.

Читать еще:  Основные типы данных в excel

Также стоит отметить, что хотя мы честно применили сортировку по всем 15 столбцам, на самом деле применилось только первые 7 или 8 из них. В дальнейшем сортировка по более старшим колонкам не имела значения, потому что там сортируемые поддиапазоны состояли всего из одной строки.

Отсортируем по строкам. Нам нужно отследить как перемешались столбцы, поэтому строку с нумерацией колонок мы как содержащую заголовки не указываем. А чтобы нам не путалась нумерация строк, мы не станем вносить первую колонку в сортируемый диапазон.

Те столбцы, которые нужно сортировать по убыванию, мы заносили в массив ключевиков со знаком минус. Если внимательно проверите результат, то увидите что с каждой следующей колонкой направление сортировки меняется на противоположное.

Сортировка макросом в excel

На этом шаге мы рассмотрим перечень окончательных действий по созданию указанного приложения .

Вернемся на рабочий лист ЖурналРегистрацииКУ .

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

Оставим за пользователем только выбор копируемой строки, а все остальное поручите выполнять Excel в автоматическом режиме при нажатии на созданную кнопку Копирование и вставка выделенной строки (рисунок 1), которой назначьте следующий записываемый макрос с именем ВставкаСтроки .

Рис.1. Левая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ

Для записи макроса выполните следующую последовательность действий:

  • перед записью макроса выделите выбранную строку, например, строку 9;
  • выполните процедуру начала записи макроса, после чего задайте команду копирования, например, нажатием на кнопку Копирование на стандартной панели инструментов. Содержимое строки 9 будет помещено в буфер обмена;
  • в связи с тем, что процедура автоматического определения и выделения первой пустой строки списка еще не изучена, выделите самую последнюю строку списка. Согласно ранее определенным договоренностям — это строка 200. Но чтобы добраться до нее, нужно выполнить ряд действий, например, перемещений на несколько экранов монитора вниз. Не затрудняйте себя, а выделите, например, строку 14 и произведите вставку из буфера обмена, например, нажатием на кнопку Вставка на стандартной панели инструментов;
  • выделите строки от строки вставки (14) до первой строки после заголовка списка (4) и нажмите на кнопку Сортировка по возрастанию на стандартной панели управления. По умолчанию Excel произведет сортировку по данным столбца А . Список расположится в порядке возрастания порядковых номеров и содержимое скопированной строки 9 будет находиться в конце списка;
  • остановите запись макроса.

Назначьте макрос кнопке и выделив одну из заполненных строк списка журнала регистрации, нажатием на созданную кнопку, проверьте правильность выполнения заданной последовательности ваших действий.

Исследование кода VBA при записи макроса ВставкаСтроки

После записи макроса не откладывайте на потом исследование записанного кода VBA , а открыв Редактор Visual Basic просмотрите записанные действия и при необходимости отредактируйте код VBA . Механически сгенерированный код показан на рисунке 2.

Рис.2. Механически сгенерированный код VBA

Между ключевыми словами начала и окончания выполнения подпрограммы находится восемь строк кода. По очереди рассмотрим каждую строку. Первая строка:

Обратите на точку, находящемуся между текстом кода. Она называется операция точка , и предназначена для разделения названия свойства и названия метода в этой строке.

Обратите внимание на три рассмотренные строки в совокупности. Они предназначены для выполнения одной операции — копирования выделенной строки с целью вставки ее содержимого в другую строку. Но ту же операцию может выполнить всего одна строка, в которой в качестве аргумента метода Copy указано место вставки содержимого скопированного диапазона:

При подобном редактировании кода макроса нет необходимости даже соблюдать интервал (пробелы) между операторами Copy и Rows . После окончания редактирования, установка интервалов между операторами при отсутствии ошибок выполняется Редактором Microsoft Visual Basic автоматически.

Следующую строку Excel генерирует также самостоятельно:

Следующая выполняемая операция (сортировка) состоит из двух строк:

В одной строке Редактора может находиться до 1024 символов. Но если все 1024 символа разместить в одной строке, то это затруднит ее чтение. Для переноса кода строки в следующую, используется символ подчеркивания (_), перед которым обязательно должен находиться пробел.

Excel позволяет выполнять операцию сортировки с использованием от одного до трех ключей и для сортировки диапазона с помощью VBA используется метод Sort . В этой команде, выполняющей операцию сортировки, находятся следующие операторы:

  • Selection — выделенная область рабочего листа, значения которой будут сортироваться;
  • Sort — имя метода (сортировка);
  • Key1:=Range(«A14») — произвести сортировку выделенного диапазона по столбцу А , где аргумент Key1 указывает, что это первый ключ сортировки. Как вы знаете, Excel позволяет производить последнюю сортировку выделенного диапазона по трем полям с помощью диалогового окна Сортировка диапазона , которое вызывается с помощью команды Данные | Сортировка ;
  • Order1 — указывает на порядок сортировки диапазона по первому ключу. Аргумент xlAscending — произвести сортировку по возрастанию. При сортировке по убыванию присваивается значение xlDescending ;
  • Header — аргумент, с помощью которого определяется: сортировать ли первую строку выделенного диапазона. При механической записи макроса этому аргументу присвоено значение xlGuess . В этом случае Excel самостоятельно определяет, является ли первая строка строкой заголовка и нужно ли ее сортировать. Для того чтобы указать, что первая строка является строкой заголовка, и ее сортировать не требуется, присвойте этому аргументу значение xlYes . Если присвоить значение xlNo , то в этом случае первая строка сортируется;
  • OrderCustom — целое число, которое указывает порядок сортировки, указанный пользователем на вкладке Списки диалогового окна Параметры , выводимое командой Сервис| Параметры . По умолчанию присваивается значение 1;
  • MatchCase — указывает различать (True) или нет (False) при сортировке заглавные и строчные буквы;
  • Orientation — указывает как проводить сортировку: по строкам (xlTopToBottom) или столбцам (xlLeftToRight) .

Как видно из записанного кода для присвоения методам значений применяются знаки := (двоеточие и равно).

Полученный в результате редактирования код макроса показан на рисунке 3, а остальной текст кода можно удалить.

Рис.3. Отредактированный код VBA подпрограммы вставки в конец списка содержимого выделенной строки

За счет минимизации текста подпрограммы было увеличено его быстродействие. Ведь для Excel нужно было прочитать ранее 8 строк, по каждой из которых он должен был предпринять какие-то действия, что значительно дольше, чем 3.

Полный текст этого приложения можно взять здесь.

Со следующего шага мы начнем рассматривать автоматизацию рабочего процесса по формированию и учету кассовых документов .

Сортировка данных в Excel по строкам и столбцам с помощью формул

Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.

Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.

Порядок сортировки в Excel

Существует два способа открыть меню сортировки:

  1. Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
  2. Открыть вкладку «Данные» — диалоговое окно «Сортировка».

Часто используемые методы сортировки представлены одной кнопкой на панели задач:

Сортировка таблицы по отдельному столбцу:

  1. Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
  2. Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.

Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.

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

Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.

Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:

  1. Выделяем столбец – правая кнопка мыши – «Сортировка».
  2. Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
  3. Соглашаемся «автоматически расширить диапазон».

Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».

В открывшемся окне вводим необходимые параметры:

Здесь можно выбрать порядок представления разных по цвету ячеек.

По такому же принципу сортируются данные по шрифту.

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

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

  1. Открываем меню «Настраиваемая сортировка». Назначаем первый критерий.
  2. Нажимаем кнопку «Добавить уровень».
  3. Появляются окошки для введения данных следующего условия сортировки. Заполняем их.

Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.

Сортировка строк в Excel

По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:

  1. В диалоговом окне «Настраиваемой сортировки» нажать кнопку «Параметры».
  2. В открывшемся меню выбрать «Столбцы диапазона».
  3. Нажать ОК. В окне «Сортировки» появятся поля для заполнения условий по строкам.

Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.

Случайная сортировка в Excel

Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.

Например, нужно расположить в случайном порядке набор неких чисел.

Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.

Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.

Динамическая сортировка таблицы в MS Excel

Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.

  1. Есть набор простых чисел, которые нужно отсортировать по возрастанию.
  2. Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
  3. Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

Для динамической сортировки текстовых значений понадобятся формулы массива.

  1. Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
  2. Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
  3. В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
  4. Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон «фрукты» еще одно значение «помело» и проверим:

Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.

Ссылка на основную публикацию
Adblock
detector