Сортировка листов в excel по алфавиту
Как отсортировать данные сразу на нескольких листах?
В Excel мы можем быстро и легко сортировать данные на листе на основе определенного столбца, но пробовали ли вы когда-нибудь сортировать данные на нескольких листах? Чтобы отсортировать их по одному, потребуется много времени, в этой статье я представлю простой способ решения.
Сортировка данных на нескольких листах одновременно с кодом VBA
Чтобы отсортировать данные по одному столбцу на всех листах книги, вам может помочь следующий код VBA.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: Сортировка данных на нескольких листах одновременно:
3, Затем нажмите F5 ключ для запуска этого кода, все листы с одинаковым форматированием были отсортированы по столбцу E каждого листа сразу в порядке убывания.
Внимание: В приведенном выше коде А: Ж диапазон данных, который вы хотите отсортировать, E это буква столбца, по которой вы хотите отсортировать.
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . -->
- Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Как я могу заставить это работать для нескольких столбцов. Я пытаюсь отсортировать некоторые по столбцу K, а некоторые по столбцу M, и я знаю точные номера листов. Мой столбец M (когда я использую приведенный выше код) сортируется только по K, а не по M, поэтому он не работает.
Привет, Люси,
Чтобы отсортировать все листы, исключая строку заголовка, примените приведенный ниже код vba: (Примечание: измените ссылки на ячейки в соответствии с вашими потребностями)
Sub SortAllSheets ()
'Обновить Extendoffice
Dim WS как рабочий лист
Dim xIntR как целое число
ActiveSheet.Range("A1:F1").Выбрать
On Error Resume Next
Приложение.ScreenUpdating = False
Для каждого WS в рабочих листах
xIntR = Intersect(WS.UsedRange, WS.Range("A:F")).Rows.Count
WS.Range("A2:F" & xIntR).Sort Key1:=WS.Range("A2:A" & xIntR), Order1:=xlDescending
Следующий WS
Application.ScreenUpdating = True
End Sub
Пожалуйста, попробуйте, надеюсь, это поможет вам!
В надежде, что ты увидишь это. Это прекрасно работает, но включает ячейки с формулой, но без данных, создавая пробелы на вкладках и путая данные в строках. Мне нужно сохранить эти ячейки на моих вкладках, поскольку они ищут имена, добавленные на «главную вкладку». Я хочу, чтобы она сортировала только ячейки с фактическим именем - быстрое решение .
Я хочу разделить данные на несколько рабочих книг, а затем добавить значение в один из столбцов.
Я подготовил код для разделения данных в книгах. но мне нужна помощь в добавлении суммы в один из столбцов
Это, похоже, не сработало для меня. У меня есть рабочая книга с 12 листами, упорядоченными по месяцам года, каждый лист содержит соответствующие данные о продуктах компаний и т. Д. Моя цель - отсортировать и отфильтровать все листы в рабочей книге. Я хотел бы иметь один лист, который будет действовать как родительский лист (например, первый месяц года), на котором я буду выполнять функцию фильтрации или сортировки (например, сортировка A -> Z), и автоматически другие листы будут следовать тому же сортировать или фильтровать. Например, при использовании листа с надписью «Январь», если, например, я выбираю столбец «D», который включает дни в месяце, который я хочу, в «возрастающем» порядке - при актуализации сортировки A -> Z на листе «Январь» , я хочу, чтобы это повлияло на остальные все листы в последовательности (февраль-декабрь) со всеми днями месяца, отсортированными по A -> Z. Любые изменения, сделанные на листе «Январь», поскольку они относятся к сортировке и фильтрации, должны влияет на баланс листов в рабочей тетради.
Как отсортировать листы в алфавитном / буквенно-цифровом порядке в Excel?
Обычно вы можете отсортировать или упорядочить порядок вкладок рабочего листа в Excel, перетащив вкладки листа на панель вкладок листа. Но для того, чтобы сделать это с несколькими листами, вы можете рассмотреть следующие хитрые способы быстрой сортировки листов в алфавитном / буквенно-цифровом порядке в большой книге.
Quickly sort all worksheers in alphabetical/alphanumeric order in Excel:
The Sort Sheets utility of Kutools for Excel can help you quickly sorting all worksheets in alphabetical or alphanumeric order in Excel with only several clicks. Download the full feature 60-day free trail of Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Сортировка листов в алфавитном / буквенно-цифровом порядке с кодом VBA
В центре поддержки Майкрософт есть макрос для сортировки листов по альфа-каналу. Мы можем применить его, выполнив следующие шаги:
1. Удерживая нажатой ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, а затем вставьте следующий макрос в Окно модуля.
VBA: сортировка листов в алфавитном / буквенно-цифровом порядке
3. нажмите F5 ключ для запуска этого макроса. В следующем окне подсказки щелкните Да, все листы будут отсортированы по возрастанию в алфавитном порядке; и нажмите Нет, все листы будут отсортированы по убыванию в алфавитном порядке.
Сортировка листов в алфавитном / буквенно-цифровом порядке с помощью Kutools for Excel
Если вы не знакомы с макросами или предпочитаете другие способы, вы можете попробовать Kutools for Excel. Kutools for Excel's Сортировать листы инструмент может легко сортировать все листы.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Кутулс Плюс > Рабочий лист > Сортировать листы. Смотрите скриншот:
2. В Сортировать листы В диалоговом окне выберите один тип сортировки, который вам нужен на правой панели, например Альфа-сортировка, Буквенно-цифровая сортировка, а затем нажмите OK кнопка. Смотрите скриншот:
Затем все листы сортируются на основе указанного типа сортировки. Смотрите скриншот:
Kutools for Excel's Сортировать листы инструмент может быстро переупорядочить все листы в активной книге. Он поддерживает несколько типов сортировки, в том числе Альфа-сортировка, Буквенно-цифровая сортировка, Цвет Сортировка и Обратный. Кроме того, вы также можете перемещать листы вверх / вниз и сбрасывать сортировку.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Предположим, у вас есть диапазон данных, и теперь вы хотите отсортировать каждую строку в алфавитном порядке и индивидуально, как показано на скриншоте ниже, как вы можете решить эту проблему? В этой статье я расскажу о некоторых методах и приемах быстрого выполнения этой работы в Excel.
Сортируйте каждую строку в алфавитном порядке одну за другой с помощью функции сортировки
Чтобы отсортировать каждую строку в алфавитном порядке и индивидуально, вы можете применить функцию сортировки и выбрать опцию «Сортировать слева направо», а затем отсортировать их по одной.
1. Выберите первую строку, которую вы хотите отсортировать по алфавиту, и нажмите Данные > Сортировать, а во всплывающем Предупреждение о сортировке диалог, проверьте Продолжить с текущим параметром выбора и щелкните Сортировать кнопка. Смотрите скриншот:
2. Затем в Сортировать диалоговое окно, нажмите Доступные опции , чтобы открыть Параметры сортировки диалог и проверьте Сортировать слева направо. Смотрите скриншот:
3. Затем нажмите OK , чтобы вернуться к Сортировать диалоговом окне и выберите номер строки, которую вы хотите отсортировать первой в Строка раскрывающийся список, а затем укажите Сортировать по и заказ как вам нужно. Смотрите скриншот:
4. Нажмите OK. Вы увидите, что первая выбранная строка отсортирована от наименьшей к наибольшей. Смотрите скриншот:
5. Повторите шаги 1–4 для сортировки других строк одну за другой. И теперь каждая строка отсортирована по алфавиту и индивидуально.
Быстро сортируйте каждую строку по алфавиту с помощью формул
Если нужно отсортировать сотни строк по отдельности, вышеуказанный метод будет напрасной тратой времени. Здесь я представлю вам несколько формул, с помощью которых вы сможете быстро отсортировать каждую строку индивидуально от наименьшей к наибольшей или наоборот.
1. Выберите пустую ячейку рядом со строками, которые вы хотите отсортировать, например F2, введите эту формулу = МАЛЕНЬКИЙ ($ B2: $ D2,1) , и нажмите Enter ключ. Смотрите скриншот:
2. Затем перейдите к следующей ячейке, G2, введите эту формулу = МАЛЕНЬКИЙ (B2: D2,2) и нажмите Enter ключ. Смотрите скриншот:
3. В ячейке M2 введите формулу = МАЛЕНЬКИЙ ($ B2: $ D2,3) и нажмите Enter key, теперь первая строка отсортирована от наименьшей к наибольшей. Смотрите скриншот:
Наконечник: в приведенных выше формулах B2: D2 - это ячейки столбца в строке, которую вы хотите отсортировать, а 1, 2,3 обозначают первый наименьший, второй наименьший, третий наименьший, вы можете изменить их по своему усмотрению.
4. Выберите ячейки F2: H2 (ячейки формулы) и перетащите дескриптор автозаполнения на ячейки, которые необходимо использовать для сортировки соответствующих ячеек столбца. Смотрите скриншот:
Теперь каждая строка отсортирована индивидуально, вы можете скопировать ячейки формулы и вставить их как значения вместо исходного значения в диапазон данных, если вам нужно.
При работе с данными в Excel может понадобиться их упорядочить. Например, в большой организации нужен список сотрудников в алфавитном порядке фамилий, а также списки их в порядке убывания или возрастания стажа работы, возраста или размера зарплаты. Для решения этой задачи не требуется вводить данные несколько раз. С помощью механизма сортировки в Excel легко отсортировать имеющиеся данные в нужном порядке.
Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.
Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.
Сортировка по одному критерию
- В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
- На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].
Отметим, что буквы на этой кнопке указывают только на направление сортировки, а вид кнопки остается один и тот же и при текстовых, и при числовых данных.
Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.
Многоуровневая сортировка
- Выделить одну ячейку из сортируемого массива данных.
Если диапазоне данных имеются пустые столбцы или строкой, то Excel автоматически воспринимает их как границы сортируемого массива данных. В таком случае следует выделить все данные, подлежащие сортировке.
- На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
- Последовательно задать уровни сортировки (определяемые именем столбца).
Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:
- Имя столбца для сортировки.
- Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
- Порядок сортировки (по убыванию или по возрастанию).
Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.
Сортировка по форматированию
Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.
Пошаговый порядок действий:
- Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
- На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
- В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
- В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
- Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
- При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.
Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.
В Excel есть кнопки, отвечающие за быструю сортировку выделенного массива данных. Их использование станет оптимальным в тех ситуациях, когда нужно обработать ячейки всего один раз, предварительно выделив необходимые.
-
Зажмите левую кнопку мыши и выделите все значения, которые далее будут подвергаться сортировке.
Если вы еще не решили, хотите сортировать только указанный диапазон или нужно захватывать соседние ячейки, проверьте каждый вариант, отменяя его нажатием горячей клавиши Ctrl + Z. Так проще определиться с изменениями, происходящими в таблице.
Способ 2: Настраиваемая сортировка
Настраиваемая сортировка позволяет более гибко выстроить расположение элементов в таблице, учитывая несколько уровней и разных диапазонов данных. Для ее создания используется специальное меню, которое и возьмем во внимание далее.
-
Рекомендуем сразу выделить всю таблицу, если помимо сортировки по алфавиту вы желаете добавить еще несколько уровней.
Способ 3: Формула сортировки
Недостатки предыдущих методов состоят в том, что они сортируют только единоразово, а динамически при внесении изменений таблица не меняется. Если вас не устраивает такой вариант, понадобится вручную создать формулу сортировки, которая при добавлении или удалении элементов автоматически пересчитает их и поставит в нужном порядке. Формул будет несколько, поскольку пока разработчики не добавили специальную функцию, которая позволяла бы обойтись без применения вспомогательных подсчетов. Весь дальнейший процесс состоит из нескольких этапов для правильного понимания принципа сортировки по алфавиту.
Шаг 1: Создание вспомогательной формулы
Основная задача – создать вспомогательную формулу, которая анализирует слова в ячейках и определяет их порядковый номер в будущем отсортированном по алфавиту списке. Это происходит при сравнении встроенными алгоритмами Excel, работающими по принципу анализа кодировки. Детально разбирать работу этой формулы не будем, только покажем ее создание.
-
Для работы с будущими вычислениями понадобится создать из ячеек группу, для чего их необходимо выделить и в специально отведенном поле сверху задать новое название.
Шаг 2: Создание формулы сортировки
Вспомогательная формула готова и работает правильно, поэтому можно приступить к созданию основной функции, которая и будет заниматься сортировкой благодаря уже существующему автоматическому определителю позиций.
-
В новой ячейке начните вводить =ПОИСКПОЗ(СТРОКА(A1) . Эта формула отвечает за поиск позиции строки, из-за чего и следует указать аргумент "A1".
Для упрощения понимания отдельно предоставляем полную формулу:
=(ИНДЕКС(Фрукты;ПОИСКПОЗ(СТРОКА(A1);номер_слова;0))) , вам же останется только отредактировать ее под свои цели и растянуть на необходимый диапазон ячеек.
Шаг 3: Модернизации формулы для повторных названий
Единственный недостаток созданной только что формулы – некорректная ее работа при наличии повторных названий, что вы можете заметить на представленном далее скриншоте. Это связано с тем, что вспомогательная функция не в состоянии правильно обработать повторяющиеся слова, поэтому ее придется немного усовершенствовать, если вы хотите использовать в списке повторы.
-
Откройте вспомогательную формулу и уберите знак " СЧЁТЕСЛИ работал только при условии равенства.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Читайте также: