Сортировка по дате в excel vba
2 ответа
Сортировка массива по дате, проблема в том, что дата сохраняется в строковом формате, может ли это быть возможно для сортировки массива без изменения даты в объекте NSDate.(Core Data). строковый формат-mm/dd/yyyy
У меня есть какой-то лист с именем, например (CZLON), как показано ниже. Он содержит некоторые данные, импортированные из txt-файлов или созданные вручную: Мне нужно, чтобы эти данные были отсортированы определенным образом: Первый приоритет, по столбцу E (если ячейка содержит текст с S355-1-м или.
неужели ни один из 541000 результатов поиска в Google по сортировке vba excel не вызвал у вас интереса? Проверьте, ведет ли это вас в правильном направлении, пожалуйста, но отрегулируйте диапазон для данных, которые вы хотите отсортировать, проверьте заголовки и т. Д:
Хорошей отправной точкой для написания макроса для выполнения какой-либо задачи, которую вы раньше не кодировали, является простая запись макроса, выполняющего задачу, которую вы хотите выполнить. Поэтому, если я начну с образца набора данных с датой в столбце J, я начну записывать макрос, отсортирую данные по столбцу J, остановлю запись и посмотрю на код. Я понимаю это:
Это вручную кодирует, что я выбрал ячейку J1, а затем показывает мне шаги, которые выполняет функция сортировки. Исходя из этого, я могу сузить круг того, что я на самом деле хочу сделать. Например, выбор J1 не нужен, мне не нужно беспокоиться о .sortmethod и т. Д. Я могу сократить код до примерно следующего:
Все, что я сделал,-это реорганизовал записанный код, изменил несколько жестко закодированных значений на константы и переменные и поместил все это в блок With. Теперь я могу использовать это как дорожную карту, чтобы разместить такую же структуру везде, где она мне нужна.
Вы даже можете сохранить свой процесс сортировки в качестве отдельного подраздела и просто вызывать его, когда это необходимо, передавая ему аргументы, чтобы сообщить ему, где находятся данные, таким образом:
Затем в своем цикле вы скажете что-то вроде:
В настоящее время я пытаюсь самостоятельно обучить себя коду VBA в Excel, но столкнулся с проблемой. Я хочу, чтобы Excel автоматически упорядочивал определенные строки в соответствии с датой, введенной в определенные ячейки. Например, даты будут вводиться только в ячейки E36-E40, и по мере их.
Я пытаюсь написать VBA для фильтрации файла. Я отфильтровал его для полей 5 и 8, но я также хочу, чтобы поле 7 фильтровалось по дате - я хочу, чтобы файл показывал все строки, которые сегодня + 14 дней, и фильтровал rest. спасибо Sub Filter() ' Sheets(CP).Select Range(a3:d & Cells(Rows.Count.
Похожие вопросы:
В настоящее время у меня есть такой массив var arr = [ [A, 04/02/2014], [B, 06/06/2014], etc] WHat я хочу сделать, это отсортировать его по дате, которая находится в формате MMDDYYYY, меняя местами.
Я делаю макрос VBA. В какой-то момент макрос говорит excel изменить текущий автофильтр, чтобы сортировка автофильтра была в столбце Column A , и она должна быть восходящей. Но код VBA, который у.
Я делаю ленту новостей, которая хранит даты в виде строки типа Tuesday 05 June 2012 04:14:44 PM . Я хочу сделать две вещи. Сортировка ленты новостей по дате (от самой новой до самой старой) Если.
Сортировка массива по дате, проблема в том, что дата сохраняется в строковом формате, может ли это быть возможно для сортировки массива без изменения даты в объекте NSDate.(Core Data). строковый.
У меня есть какой-то лист с именем, например (CZLON), как показано ниже. Он содержит некоторые данные, импортированные из txt-файлов или созданные вручную: Мне нужно, чтобы эти данные были.
В настоящее время я пытаюсь самостоятельно обучить себя коду VBA в Excel, но столкнулся с проблемой. Я хочу, чтобы Excel автоматически упорядочивал определенные строки в соответствии с датой.
Я пытаюсь написать VBA для фильтрации файла. Я отфильтровал его для полей 5 и 8, но я также хочу, чтобы поле 7 фильтровалось по дате - я хочу, чтобы файл показывал все строки, которые сегодня + 14.
Я автоматически отсортировал две мои строки, используя VBA в excel. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(C:D)) Is Nothing Then.
Я создаю базу данных для excel. Цель состоит в том, чтобы данные, вводимые в него, автоматически сортировались по цвету, а затем по алфавиту. Я использую следующий VBA для автоматической сортировки.
В настоящее время я пытаюсь собрать макрос для сортировки файлов по папкам на основе имени файла. Я заблокирован в использовании VBA из-за системы, в которой мы находимся. Например, сортировка.
Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:
Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:
Expression – выражение, возвращающее объект Worksheet, например:
Расшифровка кода
1. Expression.Sort – метод Sort объекта Worksheet возвращает объект Sort.
Объект Sort – это объект, представляющий сортировку диапазона данных.2. .SortFields.Clear – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.
Объект SortField содержит все сведения о параметрах сортировки для заданного рабочего листа.3. .SortFields.Add Key, SortOn, Order, DataOption – метод Add объекта SortFields создает и возвращает новый экземпляр объекта SortField с заданными параметрами.
Параметры метода Add объекта SortFields:
Key – обязательный параметр, который задает значение ключа для сортировки. Тип данных – Range. Обычно указывается первая ячейка столбца при сортировке по строкам или первая ячейка строки при сортировке по столбцам. Сортировка диапазона будет осуществлена по данным столбца (строки), первая ячейка которого указана в качестве ключа.
SortOn – необязательный параметр, который задает критерий сортировки (по какому свойству ячеек производится сортировка).
Значения, которые может принимать SortOn:
Константа | Значение | Описание |
---|---|---|
SortOnValues | 0 | сортировка по значению (значение по умолчанию) |
SortOnCellColor | 1 | сортировка по цвету ячейки |
SortOnFontColor | 2 | сортировка по цвету шрифта |
SortOnIcon | 3 | сортировка по иконке* |
* Иконки (значки) могут быть заданы ячейкам при условном форматировании диапазона.
Order – необязательный параметр, задающий порядок сортировки (по возрастанию или по убыванию).
Значения, которые может принимать Order:
Константа | Значение | Описание |
---|---|---|
xlAscending | 1 | сортировка по возрастанию (значение по умолчанию) |
xlDescending | 2 | сортировка по убыванию |
DataOption – необязательный параметр, который задает способ сортировки текста.
Значения, которые может принимать DataOption:
Константа | Значение | Описание |
---|---|---|
xlSortNormal | 0 | числовые и текстовые данные сортируются отдельно (значение по умолчанию) |
xlSortTextAsNumbers | 1 | текстовые данные рассматриваются для сортировки как числовые |
4. .SetRange [Range] – метод SetRange объекта Sort задает диапазон (таблицу), в котором выполняется сортировка.
5. .Header = [xlGuess, xlYes, xlNo] – свойство Header объекта Sort указывает, является ли первая строка таблицы строкой заголовков (шапкой).
Значения, которые может принимать свойство Header:
Константа | Значение | Описание |
---|---|---|
xlGuess | 0 | Excel сам определяет, есть ли строка заголовков |
xlYes | 1 | строка заголовков есть, сортировка ее не затрагивает |
xlNo | 2 | строки заголовков нет (значение по умолчанию) |
6. .MatchCase = [True, False] – свойство MatchCase объекта Sort указывает, как учитывать регистр при сортировке.
Значения, которые может принимать свойство MatchCase:
Константа | Значение | Описание |
---|---|---|
False | 0 | регистр не учитывается (значение по умолчанию) |
True | 1 | сортировка с учетом регистра |
7. .Orientation = [xlTopToBottom, xlLeftToRight] – свойство Orientation объекта Sort задает ориентацию для сортировки.
Значения, которые может принимать свойство Orientation:
Константа | Значение | Описание |
---|---|---|
xlTopToBottom | 1 | сортировка по стокам (значение по умолчанию) |
xlLeftToRight | 2 | сортировка по столбцам |
8. .Apply – метод Apply объекта Sort выполняет сортировку диапазона в соответствии с примененными параметрами.
Примеры сортировки
Таблица для примеров
Сортировка по одному столбцу
Краткая запись кода VBA Excel для сортировки диапазона по первому столбцу с параметрами по умолчанию:
Существуют разные способы сортировки данных в Microsoft Excel. Под вкладкой Excel Data находится значок сортировки, откуда вы можете быстро отсортировать данные и получить результаты. Почему тогда нужно писать сложный код VBA?
Вы должны помнить это каждый раз, коды VBA существуют для автоматизации вещей. Предположим, у вас есть данные, которые время от времени обновляются. Может быть, каждый день, еженедельно, ежемесячно и т. Д., И вам нужны ваши данные определенным образом. В таких случаях можно использовать функцию VBA SORT, которая станет удобным инструментом для вашей справки.
VBA имеет метод Range.Sort для сортировки данных. Где Range указывает диапазон ячеек, которые мы хотим отсортировать в порядке возрастания или убывания.
Синтаксис для Range.Sort приведен ниже:
- Ключ - столбец / диапазон, который нужно отсортировать. Ex. Если вы хотите отсортировать ячейки A1: A10, вы должны упомянуть Range (A1: A10)
- Порядок - это параметр, который позволяет сортировать данные в порядке возрастания или убывания.
- Заголовок - это параметр, который указывает, имеет ли ваш столбец / диапазон заголовки или нет.
Этих трех параметров достаточно для нашей работы. Однако есть некоторые другие параметры, такие как MatchCase, SortMethod и т. Д., Которые вы всегда можете изучить и посмотреть, как они работают.
Как использовать функцию сортировки Excel VBA?
Мы узнаем, как сортировку VBA можно использовать для сортировки столбца без заголовков, столбца с заголовками и нескольких столбцов с примерами в Excel.
Вы можете скачать этот шаблон VBA Sort Excel здесь - Шаблон VBA Sort Excel
Функция сортировки VBA - пример № 1
Сортировка одного столбца без заголовка
Предположим, у вас есть столбец с именами, как показано ниже, и все, что вам нужно, это отсортировать эти данные в алфавитном порядке в порядке возрастания или убывания.
Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.
Шаг 1: Определите новую sup-процедуру в модуле и создайте макрос.
Код:
Шаг 2: Используйте функцию Range.Sort для сортировки этого столбца в порядке возрастания.
Код:
Здесь вы даете диапазон, начиная с ячейки A1 до последней использованной / непустой ячейки (см. Функцию .End (xlDown)) для функции Range.Sort.
Шаг 3: Теперь введите значения аргумента.
Код:
Как мы уже обсуждали ранее, Key, Order и Header являются важными и необходимыми аргументами, которые необходимо предоставить. В качестве начального диапазона столбца мы указали Range («A1»), который нам нужно отсортировать. Порядок предоставляется в порядке возрастания, а заголовок - как нет (что означает, что столбец не имеет заголовка).
Этот код проверяет все непустые ячейки, начиная с A1, а затем сортирует их в порядке возрастания, начиная с ячейки A1.
Шаг 4: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.
Если вы можете сравнить это с изображением в начале этого примера, вы увидите, что имена отсортированы в порядке возрастания.
Функция сортировки VBA - пример № 2
Сортировка одной колонки с заголовком
Предположим, у вас есть столбец с заголовком, как показано ниже. И вы хотели отсортировать этот столбец в порядке возрастания или убывания.
Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.
Шаг 1: Определите новую подпроцедуру в новой модели для хранения макроса.
Код:
Код:
Шаг 3: Используйте Range («A1»). Сортируйте перед приведенной выше строкой кода, чтобы сделать ее функцией сортировки.
Код:
Шаг 4: Введите Key1 как Range («A1») для сортировки данных из ячейки A1, Order1, чтобы отсортировать данные в порядке возрастания или убывания, и Header как « Да», чтобы система знала, что первая строка является заголовком в вашем данные.
Код:
Шаг 5: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.
Здесь данные из примера № 2 данной рабочей книги Excel сортируются в порядке возрастания, учитывая, что они имеют заголовок. Это означает, что при сортировке этих данных первая строка (которая содержит имя Emp ) игнорируется, поскольку она рассматривается как заголовок для этих данных в столбце A.
Вы также можете отсортировать те же данные в порядке убывания алфавитов. Все, что вам нужно сделать, это изменить порядок с возрастания на убывание.
Шаг 6: Измените order1 на нисходящий, чтобы отсортировать данные в порядке убывания.
Код:
Шаг 7: Запустите код и посмотрите вывод, как показано ниже.
Вы можете видеть, что данные отсортированы в порядке убывания.
Функция сортировки VBA - пример № 3
Сортировка нескольких столбцов с заголовками
До сих пор мы рассмотрели, как сортировать данные одного столбца в порядке возрастания или убывания (без заголовка и с заголовком). Что если у вас есть данные, которые нужно отсортировать по нескольким столбцам? Можно ли написать код для того же?
Ответ: «Да, конечно, это можно сделать!»
Предположим, у вас есть данные, указанные ниже:
Вы хотели отсортировать эти данные сначала по Emp Name, а затем по Location. Выполните следующие шаги, чтобы увидеть, как мы можем кодировать его в VBA.
Шаг 1: Определите новую подпроцедуру для добавления макроса в новый модуль.
Код:
Шаг 2: Используйте оператор With… End With, чтобы добавить несколько условий сортировки в одном цикле.
Код:
Шаг 3: Теперь используйте SortFields.Add, чтобы добавить несколько условий сортировки в одном листе.
Код:
Шаг 4: Определите диапазон листов для сортировки и заголовок На следующем шаге.
Код:
Шаг 5: Используйте .Apply, чтобы применить все это под оператором with и закрыть цикл, пишущий End With.
Код:
Шаг 6: Запустите этот код, нажав F5 или кнопку Run вручную, и увидите результат.
В этом коде ActiveSheets.Sort помогает системе идентифицировать лист, по которому должны быть отсортированы данные. SortFields.Add позволяет добавлять два условия сортировки с их порядком (по возрастанию в обоих случаях). SetRange позволяет системе установить диапазон от A1 до C13. Вы также можете увеличить этот диапазон. Операторы Apply позволяют системе применять все изменения, сделанные в цикле With.
Наконец, вы получите данные, которые отсортированы по имени Emp сначала, а затем по местоположению.
То, что нужно запомнить
- Под сортировкой VBA вы можете создавать именованные диапазоны вместо ссылок на ячейки и использовать их. Ex. Если вы создали именованный диапазон для ячейки A1: A10 как «EmpRange», вы можете использовать его в Range.Sort, например Range («EmpRange»).
- Вы можете сортировать данные как по возрастанию, так и по убыванию, как в Excel.
- Если вы не уверены, есть ли у ваших данных заголовок или нет, вы можете использовать xlGuess в разделе заголовка, чтобы система могла угадать, является ли первая строка данных заголовком или нет.
Рекомендуемые статьи
Это было руководство по сортировке Excel VBA. Здесь мы обсудили VBA Sort и как использовать Excel VBA Sort Function вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
В статье разберем 9 видов сортировок, рассмотрим суть этих алгоритмов. Скорости, сложность алгоритмов и практическое их применение оставим за скобками. Задача статьи показать, что одну и туже задачу можно решать различными способами, показать практическое применение языка VBA и помочь начинающим в его освоении.
Подготовительный этап
Перед тем как начинать писать алгоритмы немного подготовимся. Создадим общую константу n для хранения размера массивов. Вставим на лист диаграмму, чтобы отслеживать как все работает. В коде объявим объект нашей диаграммы, на которой будем просматривать ход процесса сортировки. Чтобы не дублировать код в каждом алгоритме сортировки мы будем использовать процедуру инициализации Init().
Чтобы наша диаграмма с результатом не подвисала и обновлялась напишем такую функцию.
В качестве массивов будем использовать диапазон ячеек A1:Y1. Напишем еще одну коротенькую процедуру для перемешивания этого массива, точнее заполнения его числами от 1 до 25 в случайном порядке.
Теперь все готово, давайте писать алгоритмы сортировки.
Сортировка пузырьком
Пузырьковая сортировка (или сортировка простыми обменами) пожалуй самый неэффективный алгоритм сортировки и в тоже время пожалуй самый известный.
Суть алгоритма в прохождении в цикле по всем элементами массива и в попарном сравнении текущего элемента со следующим. Если текущий элемент массива больше (для сортировки по возрастанию и меньше для сортировки по убыванию) чем следующий, то эти два элемента меняются друг с другом местами. Ход алгоритма смотрите на следующей диаграмме.
Вот код сортировки данным алгоритмом на VBA. Еще стоит обратить внимание на переменную Flag она служит индикатором того, что массив досрочно отсортирован и можно заранее выйти из цикла и сократить вычислительные ресурсы.
Далее описана процедура Swap для перестановки ячеек местами. После перестановки ячеек вызывается процедура ChartRefresh обновления диаграммы.
Сортировка перемешиванием
Этот алгоритм является разновидностью пузырьковой сортировки. Также этот алгоритм называют Шейкерной сортировкой или двунаправленной. Основное отличие от обычной сортировки пузырьком в том, что массив сначала просматривается слева направо и максимальный элемент перемещается вправа, а после мы проходим по массиву справа налево (от последнего отсортированного элемента) и наименьший элемент перемещается влево. Вот на графике отчетливо это видно.
Алгоритм немного больше, но по сложности аналогичный, вот его код на VBA.
Сортировка выбором
Тоже достаточно простой алгоритм сортировки. Суть его заключается в поиске минимального значения (максимального для сортировки по убыванию) и обмене найденного значения с первым неотсортированным значением. Т.е. нашли первое минимальное значение, поменяли его с первым элементом, нашли второе минимальное - поменяли со вторым элементом. График получается следующий:
Объединение сортировки пузырьком и сортировки выбором
Можно ускорить алгоритм сортировки пузырьком объединив его с алгоритмом сортировки выбором. Для этого нужно определять минимальный элемент во внутреннем цикле и после каждого прохода по списку обменивать найденный минимальный элемент с первым неотсортированным слева. Таким образом, мы сокращаем в 2 раза число перестановок, но при этом увеличиваем в 2 раза число сравнений.
Код отличается только 2 строчками:
Сортировка вставками
Вот определение сортировки с википедии
Это алгоритм, в котором элементы входной последовательности просматриваются по одному, и каждый новый поступивший элемент размещается в подходящее место среди ранее упорядоченных элементов.
Другими словами мы во внешнем цикле проходим по всем элементам массива, а во внутреннем цикле сравниваем правый элемент с уже отсортированными элементами слева и перемещаем его при необходимости. Вот как это выглядит визуально:
Код тоже думаю окажется для вас достаточно простым.
Гномья сортировка
Визуально отличия от сортировки вставками нет, однако в код совершенно другой так как нет никаких вложенных циклов. Алгоритм в цикле проходит по всем элементам, сравнивая текущий элемент с предыдущим. Если элементы стоят верно переходит к следующему, если нет, меняет их местами и переходит к предыдущему элементу.
Сортировка слиянием
Простые алгоритмы сортировки мы разобрали, теперь давайте рассмотрим более сложные виды сортировок. Хотя главное понять суть алгоритма и его реализация уже не будет казаться сложной.
Суть алгоритма сортировки слиянием состоит в том, чтобы разбить исходный массив на более мелкие массивы, отсортировать каждый по отдельности, а после объединить результаты.
Для этого первоначальный массив разбивается на 2 части пополам (ну или почти пополам если количество нечетное), каждая половинка разбивается еще пополам и так до тех пор, пока мы не получим массивы состоящие из 1 элемента. После прохождения процедуры разбивки на части, слияние каждой части и ее сортировка. Например, массив содержит числа 5 2 1 3 4. Разбиваем его на две части: 5,2,1 и 3,4 . Первую часть 5,2,1 разбиваем еще на две части 5,2 и 1. Далее 5,2 еще на две части 5 и 2. А теперь идем обратно, сортируем и сливаем массивы. Получается 2,5 и 1, объединим дальше - 1,2,5 , последняя итерация отсортирует исходный массив 1 2 3 4 5. При слиянии учитывается тот факт, что массивы уже отсортированы по отдельности, поэтому объединение проходит быстрее.
Вот визуализация работы алгоритма:
Код состоит из двух частей. Первая MergeSort - рекурсивная функция разделения массивов, т.е. эта функция запускает саму себя. Это происходит до тех пор, пока размер массива больше 1, иначе запускается функция MergeSort для каждой из частей.
После того как массивы разобьются запускается функция Merge(left, right), которая сортирует и объединяет массив обратно.
В качестве сортировки и объединения можно использовать различные алгоритмы, например такой. Если кто-то предложит более изящное решение - пишите в комментариях.
Так как функция у нас рекурсивная, то первый ее запуск необходимо сделать из отдельной процедуры, вот так:
Быстрая сортировка
Алгоритм быстрой сортировки - один из самых быстрых и эффективных и часто используется в практике. При этом он достаточно простой.
Суть алгоритма в следующем:
- Выбрать из массива опорный элемент. Например, взять элемент в середине массива (в целом это может быть любой из элементов).
- Сравнить остальные элементы массива с выбранным опорным элементов и разбить массив на 2 части:
- элементы, которые меньше или равны опорному элементу;
- элементы, которые больше опорного.
- Далее пункты 1 и 2 повторяются рекурсивно для каждой части массива, до тех пор пока размер части состоит из более чем 1 элемента.
На визуализации к сожалению что-то разглядеть сложно. Алгоритм достаточно быстро отрабатывает:
Вот код данного алгоритма на VBA.
Запуск рекурсивной функции быстрой сортировки запустим из отдельного метода.
Пирамидальная сортировка
Пирамидальная сортировка или как еще ее называют "Сортировка кучей" использует в своем алгоритме двоичное дерево.
Это такое дерево, для которого выполнены следующие условия:
- Значение в любой вершине не меньше, чем значения её потомков.
- Длина веток дерева не отличается друг от друга более чем на 1 слой.
- Последний слой заполняется слева направо без «дырок».
Вот пример дерева, которое можно найти на википедии:
Это дерево можно представить в виде следующего массива, где для любого элемента A[i] потомками являются элементы A[2i] и A[2i+1].
Т.е. для каждого элемента кучи справедливы следующие условия: A[i] >= A[2i] и A[i] >= A[2i+1].
Алгоритм пирамидальной сортировки состоит из следующих шагов:
- Построение массива в виде двоичного дерева.
- Исключение корня дерева (максимального значения массива) из массива и перенос его в конец последовательности.
- После исключения корня дерево перестраивается и его корень опять отсекается и переносится в конец.
- Так происходит до тех пор, пока вся последовательность не отсортируется.
Вот визуальное отображение выполнения этого алгоритма:
Ниже код пирамидальной сортировки на VBA. Который формирует двоичную кучу и корень этой кучи переносит в конец последовательности. Так происходит n раз.
Читайте также: