Как сделать автоматическую сортировку в excel при добавлении данных

Сортировка данных сводной таблицы в Excel 2013

Сортировка данных, находящихся в области строк и столбцов сводной таблицы, по умолчанию выполняется в порядке возрастания (рис. 1а) либо с применением пользовательских списков сортировки. [1] Далеко не всегда это устраивает пользователя. Например, когда хочется отобразить заказчиков с наибольшим доходом в верхней части списка (рис. 1б). Если в сводной таблице применяется сортировка по возрастанию (убыванию), следует создать правило, контролирующее порядок сортировки по полю. Причем это правило (в отношении этого поля) будет применяться даже после добавления новых полей в сводную таблицу (рис. 1в).

Рис. 1. Сортировка по полю Заказчик: (а) по умолчанию – от А до Я; (б) в порядке уменьшения дохода; (в) порядок сортировки по полю Заказчик не изменился при добавлении поля Сектор

Скачать заметку в формате Word или pdf, примеры в формате Excel

Сортировка заказчиков в порядке убывания дохода

Чтобы отсортировать строки сводной таблицы в порядке убывания дохода, выберите любую ячейку столбца Сумма по полю Доход, например, Е4 (но не заголовок), и щелкните на значке ЯА, находящемся на вкладке Данные (рис. 2). Подобная сортировка напоминает стандартную, но это лишь внешнее сходство. При выполнении сортировки сводной таблицы Excel создает правило, которое будет работать и после внесения дополнительных изменений в сводную таблицу.

Рис. 2. Создание правила сортировки в порядке уменьшения дохода

На примере сводной таблицы, находящейся в столбцах G:I (рис. 1в), видно, что произойдет после добавления нового внешнего поля строки Сектор. Сводная таблица продолжает сортировать данные в порядке убывания дохода внутри каждого сектора. Например, в секторе Производство на первом месте находится компания General Motors с доходом 750 163 доллара. За ней следует компания Ford с доходом 622 794 доллара. Если даже удалить поле Заказчик из сводной таблицы, выполнить дополнительные настройки и вернуть это поле обратно, но уже в область столбцов, Excel запомнит сортировку заказчиков в порядке уменьшения дохода.

Чтобы в сводной таблице, находящейся в столбцах G:I (рис. 1в), секторы также были отсортированы в порядке убывания дохода, можно пойти одним из трех способов:

• Выделите ячейку G4, щелкните правой кнопкой мыши и выберите Свернуть всёполе, чтобы скрыть все элементы, которые относятся к заказчику. После того как на экране будут отображаться лишь одни секторы, выделите ячейку I4 и щелкните на значке ЯА на вкладке Данные для выполнения сортировки по убыванию. Таким образом, будет создано правило сортировки для поля Сектор. Повторно выделите ячейку G4, щелкните правой кнопкой мыши и выберите Развернуть всёполе.
• Временно удалите поле Заказчик из сводной таблицы, отсортируйте таблицу по убыванию дохода (методом, который был описан для рис. 2), а потом вновь верните поле Заказчик.
• Воспользуйтесь возможностями команды Дополнительныепараметрысортировки (я пользуюсь именно этим методом). Чтобы вызвать команду: (а) выделите ячейку G4, щелкните правой кнопкой мыши и выберите Сортировка → Дополнительныепараметрысортировки (рис. 3) или (б) кликните на значке треугольника в поле Сектор, а затем выберите пункт Дополнительныепараметрысортировки (рис. 4). В обоих случаях откроется окно Сортировка (рис. 5). Установите переключатель в положение по убыванию и выберите строку Сумма по полю Доход.

Рис. 3. Вызов команды Дополнительные параметры сортировки правой кнопкой мыши

Рис. 4. Вызов команды Дополнительные параметры сортировки с помощью меню Сортировка и фильтры поля Сектор

Рис. 5. Настройка параметров в окне Сектор

В левом нижнем углу диалогового окна Сортировка находится кнопка Дополнительно… После щелчка на этой кнопке на экране появится диалоговое окно Дополнительные параметры сортировки. В этом окне можно: (а) задать пользовательский список, который будет использоваться для сортировки по первому ключу (подробнее см. ниже); (б) вместо столбца Общий итог в качестве базового столбца сортировки выбрать другой столбец.

Например, для сводной таблицы, изображенной на рис. 6 можно задать сортировку не по общему доходу, а по доходу от продажи одного вида товаров, например, Устройств (обратите внимание, что заказчики отсортированы не по столбцу F, а по столбцу С).

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

Чтобы выполнить такую сортировку:

1. Раскройте список Заказчик, находящийся в ячейке А4.
2. Выберите параметр Дополнительные параметры сортировки.
3. В диалоговом окне Сортировка (Заказчик) щелкните на кнопке Дополнительно…
4. В диалоговом окне Дополнительные параметры сортировки (Заказчик) выберите раздел Порядок сортировки и установите переключатель Значения в выделенном столбце.
5. Щелкните в поле ссылки, а затем выберите ячейку С5. Обратите внимание на то, что нужно щелкнуть в одной из ячеек значений Устройство, поскольку на заголовке Устройство в ячейке С4 щелкнуть невозможно.
6. Чтобы завершить установку параметров дважды кликните ОK.

Не пугайтесь, описание этого пошагового алгоритма приведено, скорее, в обучающих целях. Начиная с Excel 2013 сортировка данных сводной таблицы существенно упростилась. Теперь кнопки ЯА и АЯ на вкладке Данные используют интеллектуальные алгоритмы сортировки. При попытке выполнить сортировку с помощью этих кнопок программа попытается предугадать намерения пользователя, основываясь на том, какая ячейка была выделена перед нажатием кнопки сортировки (рис. 7):

• А1, С1, D1, Е1, F1, F2, А30, F30 – не доступны
• А2:А29 – расположит по алфавиту имена заказчиков в столбце А
• В1, В2, С2, D2, E2 – расположит по алфавиту названия товаров в строке 2
• В30, С30, D30, E30 – расположит по убыванию (возрастанию) суммы дохода в строке 30
• по возрастанию (убыванию) продаж В3:В29 – модулей, С3:С29 – устройств, D3:D29 – деталей, Е3:Е29 – препаратов, F3:F29 – итого.

Рис. 7. Интеллектуальные возможности сортировки данных сводной таблицы

Сортировка вручную

Обратите внимание на то, что в диалоговом окне Сортировка (см. рис. 5) можно вручную определить правила сортировки данных. Но сортировка сводной таблицы вручную также выполняется другим, весьма необычным способом. В отчете сводной таблицы на рис. 8а показана последовательность категорий товаров, отсортированных в алфавитном порядке: Деталь, Модуль, Препарат и Устройство. Обратите внимание на то, что объем проданных товаров, относящихся к категории Деталь, не наибольший. И вряд ли стоит эту категорию отображать первой. Установите указатель мыши в ячейке Е4 и введите слово Деталь. Стоит лишь нажать клавишу Enter, как Excel определит, что вы решили переместить колонку Деталь в последний столбец таблицы. Все числовые значения, относящиеся к этой категории товаров, переместятся из столбца В в столбец Е. Значения, относящиеся к другим категориям товаров, сместятся влево. Подобное поведение выглядит нелогичным и присуще лишь сводным таблицам Excel. Обычный набор данных Excel переупорядочить таким образом не удастся. На рис. 8б показана сводная таблица после перемещения заголовка нового столбца в ячейку Е4.

Рис. 8. Сортировка вручную: (а) категории товаров отсортированы по алфавиту, (б) категория Деталь размещена последней

Любители мыши могут просто перетаскивать заголовки требуемых колонок (или отдельные строки). Щелкните в области заголовка столбца и удерживайте указатель мыши над границей диапазона выделенных ячеек до тех пор, пока он не приобретет вид четырехнаправленной стрелки. Начинайте перетаскивать ячейку в выбранное место; появится указатель в виде жирной линии и засечками. Как только вы отпустите кнопку мыши, числовые значения тут же переместятся в новую колонку. Учтите, что при использовании ручной сортировки товары, добавляемые в источник данных, добавляются в конец списка. Это связано с тем, что программа Excel не знает, куда именно нужно добавить новый регион.

Сортировка данных согласно пользовательским спискам

Еще одно решение проблемы, связанной с настройкой последовательности представления полей, заключается в создании пользовательских списков. С помощью подобного списка будут сортироваться сводные таблицы, создаваемые в дальнейшем. По умолчанию Excelсодержит четыре пользовательских списка: для дней недели, месяцев года и сокращенных названий дней недели и месяцев года. Программа сортирует названия дней недели в естественной последовательности, начиная с Пн и кончая Вс (а не по алфавиту).

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

1. В свободной от данных области рабочего листа введите названия категорий товаров в последовательности, которая соответствует создаваемому пользовательскому списку. В каждой ячейке вводите по одному названию, а названия располагайте в одном столбце (рис. 9).
2. Выделите полученный список названий категорий товаров (ячейки А10:А13).
3. Выберите вкладку ленты Файл и в нижней части панели навигации, отображенной в окне слева, щелкните на кнопке Параметры для открытия диалогового окна Параметры Excel.
4. Выберите категорию Дополнительно, перейдите в раздел Общие и щелкните на кнопке Изменить списки.
5. В диалоговом окне Списки адрес диапазона, содержащего предварительно выделенный список названий, отображается в поле Импорт списка из ячеек (рис. 10). Щелкните на кнопке Импорт, чтобы сформировать новый список категорий товаров на основе указанных данных. Новый список добавляется в нижнюю часть области Списки.
6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Списки. Щелкните еще раз на кнопке ОК для закрытия диалогового окна Параметры Excel.

Рис. 9. Заготовка для создания пользовательского списка

Рис. 10. Окно Списки

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

Рис. 11. Теперь все сводные таблицы будут сортироваться в соответствии с новым списком

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

1. Раскройте список поля Товар и выберите параметр Дополнительные параметры сортировки.
2. В диалоговом окне Сортировка (Товар) выберите кнопку по возрастанию (от А до Я) по полю, а в раскрывающемся списке выберите Товар.
3. Щелкните на кнопке Дополнительно…
4. В диалоговом окне Дополнительные параметры сортировки (Товар) отмените установку флажка Автосортировка.
5. Раскройте список Сортировка по первому ключу и выберите список, включающий названия категорий товара (рис. 12).
6. Дважды щелкните на кнопке ОК.

Рис. 12. Выбор сортировки в соответствии с пользовательским списком

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

Автоматическая сортировка строк в Excel

Добрый день! Интересен вот какой вопрос. Как сделать автоматическую сортировку, которая сортирует строки по настраиваемому списку, чтобы из (рис. 1) получалось (рис. 2). Получается сортировка должна идти по дате, но к этой дате так же привязаны столбцы «Название сайта» и «Ссылка сайта»?

Лучшие ответы ( 2 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Автоматическая сортировка двух строк с константой!
Добрый день, знатоки программирования! Исходник: excel2010 Срочно необходима помощь в.

Автоматическая сортировка
Excel 2007. По столбцу проведена сортировка. При изменении или добавлении значения ячейки в этом.

Автоматическая сортировка
Приветствую уважаемы форумчане, сам с Execl 2010 знаком плохо.Помогите пожалуйста сделать или.

Автоматическая сортировка
У меня есть столбец A с данными, который постоянно пополняется. Как мне сделать так, чтобы в.

3496 / 1880 / 390
Регистрация: 13.12.2016
Сообщений: 6,404
Записей в блоге: 5

Millerov, выделите эти три столбца (A B C не участвуют я так понял) и нажмите фильтр «лейку» из меню данные
После этого таблица «поумнеет» и будет сортировать как надо

Регистрация: 18.12.2019
Сообщений: 8

Сообщение от АЕ

выделите эти три столбца (A B C не участвуют я так понял) и нажмите фильтр «лейку» из меню данные
После этого таблица «поумнеет» и будет сортировать как надо

Если это все было так просто, я не спрашивал бы тут)
У меня такая таблица на 10000+ строк, и там не по 5 материалов)
Приходится пользоваться хоткеем, что доставляет неудобства и слишком затратно по времени выходит

3496 / 1880 / 390
Регистрация: 13.12.2016
Сообщений: 6,404
Записей в блоге: 5

Millerov, предложенное мной может работать на 100 000 строк и пару сот столбцов. Просто косность мышления не позволяет проверить?

Регистрация: 18.12.2019
Сообщений: 8
Попробовал это сделать, и вся структура моей таблицы нарушается, возможно я вас не понял
3496 / 1880 / 390
Регистрация: 13.12.2016
Сообщений: 6,404
Записей в блоге: 5

Сообщение от Millerov

возможно я вас не понял

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

Регистрация: 18.12.2019
Сообщений: 8

Мне надо, чтобы он сортировал, грубо говоря ЦАО отдельно, а ВАО отдельно, но применяя этот фильтр, у меня выходит так, что все идёт под одну гребенку, и получается ЦАО с ВАО вперемешку

3496 / 1880 / 390
Регистрация: 13.12.2016
Сообщений: 6,404
Записей в блоге: 5

Сообщение от Millerov

Мне надо, чтобы он сортировал, грубо говоря ЦАО отдельно, а ВАО отдельно

ЦАО это одна ячейка, а для умной таблицы должна быть вся заполнена данными и ВАО тоже
Тогда (после заполнения данными) сортируете по дате, потом по округу и будет как надо

Добавлено через 1 минуту
хотя изначально вы говорили о 3-х столбцах.
и я говорил

Сообщение от АЕ

(A B C не участвуют я так понял)
Добавлено через 56 секунд
положите файл с примером — так проще будет
Регистрация: 18.12.2019
Сообщений: 8
3496 / 1880 / 390
Регистрация: 13.12.2016
Сообщений: 6,404
Записей в блоге: 5

Millerov, трудно продемонстрировать сортировку в примере, где все одинаковое.
Однако я отсортировал по номеру публикации в порядке возрастания дат публикации
Для облегчения ввода данных показал как можно использовать проверку данных для двух столбцов
Справочник на отдельном листе

Вложения

Копия Книга2-1.xlsx (12.2 Кб, 5 просмотров)

Регистрация: 18.12.2019
Сообщений: 8

Сообщение от АЕ

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

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

3496 / 1880 / 390
Регистрация: 13.12.2016
Сообщений: 6,404
Записей в блоге: 5

Сообщение от Millerov

вообще не то, что я имел ввиду

Я понять не смог. может кто другой поймет.
Научитесь формулировать

Добавлено через 41 минуту
Millerov, ваш вопрос как чемодан без ручки. Вроде простой и ответ простой, но в руках непонимающего порождает только новые «чемоданы»

4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624

Сообщение было отмечено Millerov как решение

Решение

Millerov, Если ориентироваться на скриншот, то в Вашем примере нужно фильтровать сначала тему, а затем даты. Но предварительно в темах, нужно заполнить все пустые ячейки данными предыдущей ячейки.

Вложения

Sample_for_Millerov.xls (38.0 Кб, 3 просмотров)

Регистрация: 18.12.2019
Сообщений: 8

Сообщение от pashulka

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

Благодарю! Это то, что надо как раз
4133 / 2237 / 940
Регистрация: 01.12.2010
Сообщений: 4,624

Сообщение было отмечено Millerov как решение

Решение

Millerov, Тогда, предварительно заполнить ячейки, можно примерно так :

Выделить столбец, F5 или CTRL+G
Кнопка Выделить, переключатель Пустые ячейки и ok.
Ввести =адрес_первой_ячейки_с_данными и завершить ввод CTRL+ENTER
Затем выделить все заполненные ячейки(но можно и весь столбец целиком), CTRL+C
Кликнуть правой кнопкой мышки и в контекстном меню выбрать Специальная вставка, затем переключатель напротив Значения и ok.

P.S. До специальной вставки можно добраться и другими способами, но тут главное понять алгоритм.

Как сделать автоматическую сортировку в excel при добавлении данных

Доброго времени суток!
Как сделать, чтобы при добавлении новой фамилии происходила автоматическая сортировка при нажатии клавиши enter от А до Я в колонке В строка 2?
Читал в архиве много, но там более сложные задачи))) Просмотрел весь ютуб, и тоже не нашел ответа.)))

Заранее премного благодарен

Изменено: Karen-Sochi — 25.03.2017 21:14:44
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 21:27:57
В модуль нужного листа

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns(2)) Is Nothing Then On Error Resume Next Application.ScreenUpdating = False Application.EnableEvents = False With Me.Sort .SortFields.Clear .SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End If Application.EnableEvents = True Application.ScreenUpdating = True End Sub

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 21:32:53
В «теле» уже есть 10 модулей, пара листов в книге. Нужно создать новый модуль?.
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 21:49:30
Правой кнопкой мыши по ярлыку нужного листа — Исходный текст. Откроется как раз модуль нужного листа

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 22:10:22

Все получилось, спасибо)))
Но нарисовалась следующая проблема: Вместе с этим столбцом(В) есть еще три взаимосвязанных столбца:
Фамилия(В) Звание(С) Рейтинг(D) Нас. пункт(E)., которые привязаны к конкретной фамилии.
При нажатии enter вновь написанная ячейка благополучно перескакивает на свое место,
и все остальные данные сбиваются. Как при перемещении связать эти столбцы?

Изменено: Karen-Sochi — 25.03.2017 22:10:52
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 22:12:23

Цитата
Karen-Sochi написал: нарисовалась следующая проблема

Вы бы ‘нарисовали’ лучше файл-пример, в котором-бы показали Как есть — Как надо.

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 22:29:37
С автоматической нумерацией разобрался, столбец А меня не интересует
Прикрепленные файлы

• Таблица на корректировку.xlsx (11.61 КБ)

Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 22:34:57

В коде выше (сообщение # 2), строку 9 исправьте так
.SetRange Range(«B2: E » & Cells(Rows.Count, 2).End(xlUp).Row)

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 22:43:24

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

Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 22:46:50

Цитата
Karen-Sochi написал: пропишите в высланном файле

Вы сами не можете поменять ОДНУ букву в коде? Обратите внимание что E-латинская!

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 22:50:21

Я поменял, в моей таблице не работает
Ну не совсем я неумеха)))
Могу дать доступ на тайм вьювер, можете проверить

Пользователь
Сообщений: 4093 Регистрация: 01.01.1970
25.03.2017 22:52:31

Цитата
Karen-Sochi написал: Я поменял, в моей таблице не работает

Возможно Е русской раскладки, а надо англ.
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 22:54:46
Прикрепленные файлы

• Таблица на корректировку.xlsm (16.76 КБ)

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 22:55:58

Именно в английской поменял.
Перепроверил два раза.
И еще, при переходе для заполнения на другую ячейку
строка сразу перемещается на свое место

Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 23:00:26

Наверное тяжелый случай)))
Есть электронка или скайп?
В этой таблице работает, а ТАМ где мне нужно не работает)))

Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 23:02:19

Цитата
Karen-Sochi написал: строка сразу перемещается на свое место

Так а Вы что просили?

Цитата
Karen-Sochi написал: чтобы при добавлении новой фамилии происходила автоматическая сортировка при нажатии клавиши enter

Ровно это макрос и делает

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 23:08:58

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

Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
25.03.2017 23:13:33
В коде, букву E замените на букву крайнего правого столбца Вашей таблицы

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 23:16:59
Крайний правый столбец Е
В файле еще есть много макросов, возможно они каким-либо образом влияют ,
Пользователь
Сообщений: 40 Регистрация: 07.02.2017
25.03.2017 23:19:19
Спасибо, Вы мне очень помогли)))))))
Все получилось как надо.
Пользователь
Сообщений: 6 Регистрация: 25.03.2017
25.03.2017 23:53:50

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

Пользователь
Сообщений: 40 Регистрация: 04.01.2014
27.07.2017 15:51:55

Уважаемые эксперты! Большая просьба. Помогите пожалуйста с данным макросом.
Что делать, если нужно на одном листе сортировать два диапазона строк?
У меня почему-то не получается адаптировать макрос под эту потребность.
Нужна сортировка, к примеру диапазонов E12:CO68 и E70:CO212

Прикрепленные файлы

• 1812.xls (61.5 КБ)

Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
27.07.2017 16:05:37
А какой ключевой столбец для сортировки?

Согласие есть продукт при полном непротивлении сторон.

Контакты, благодарности
Пользователь
Сообщений: 40 Регистрация: 04.01.2014
27.07.2017 16:11:28
Ой, ключевой столбец D.
Но даже при условии, что в макросе поменяла, он все-равно не работает
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
27.07.2017 16:46:04
Скрытый текст

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns(4)) Is Nothing Then Dim iRow1&, iRow2& Dim Rng1 As Range, Rng2 As Range, iKey As Range, sRng As Range Application.ScreenUpdating = False Application.EnableEvents = False On Error Resume Next iRow1 = Columns("D").Find("Канцелярські товари").Row iRow2 = Columns("D").Find("Господарські товари").Row Set Rng1 = Range(Cells(iRow1 + 1, 4), Cells(iRow2 - 1, 93)) Set Rng2 = Range(Cells(iRow2 + 1, 4), Cells(Cells(Rows.Count, "C").End(xlUp).Row - 1, 93)) If Not Intersect(Target, Rng1) Is Nothing Then Set iKey = Cells(iRow1 + 1, 4) Set sRng = Rng1 ElseIf Not Intersect(Target, Rng2) Is Nothing Then Set iKey = Cells(iRow2 + 1, 4) Set sRng = Rng2 End If With Me.Sort .SortFields.Clear .SortFields.Add Key:=iKey, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange sRng .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End If Application.EnableEvents = True Application.ScreenUpdating = True End Sub

Прикрепленные файлы

• Сортировка двух диапазонов.xls (62 КБ)

Изменено: Sanja — 27.07.2017 16:49:34

Согласие есть продукт при полном непротивлении сторон.

Кейс 5.
Сортировка данных в таблицах

Когда Вы добавляете в таблицу много информации, организация контента в ней становится очень важным параметром.

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

Упорядочивание информации в таблицах с помощью сортировки.

Скачать Кейс 5

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

Имеем таблицу с данными о сотрудниках компании, их должности и времени работы:

Отсортируем таблицу по стажу работы от большего к меньшему:

Стандартный вариант сортировки

Программа предлагает два варианта:
— отсортировать всю таблицу по выделенному столбцу
— или отсортировать только выделенный столбец, а таблицу оставить без изменений.
Выбор очевиден. Нажимаем на «Сортировка» и получаем отсортированную таблиц.

Аналогичным образом работает и сортировка от большего к меньшему:

Выделяем столбец «С» с периодом работы Переходим на вкладку «Данные» панели управления и выбираем сортировку по убыванию

Перед нами появится следующее диалоговое окно:

Настраиваемая сортировка

Отсортируем таблицу по возрастанию должности.

Выделяем нашу таблицу
Нажимаем на «Сортировка» во вкладке меню «Данные»:

Перед нами открывается диалоговое окно:

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

• Столбец

Справка о настраиваемой сортировке

• Порядок

• Сортировка

Продолжение: Настраиваемая сортировка

В списке поля Столбец выбираем Должность:

В списке поля Сортировка выбираем Значения:

В списке поля Сортировка выбираем Настраиваемый список:

Перед нами появляется диалоговое окно настройки списка:

В этом окне можно выбрать из предложенных или создать собственную последовательность отображения списка
Введем в окно Элементы списка последовательность отображения должностей через запятую и нажимаем кнопку Добавить

Мы создали правило для сортировки. Нажимаем кнопку ОК и видим изменения в таблице.

А теперь создадим еще одно правило и отсортируем таблицу внутри каждой должности по периоду работы в компании

• Выделяем таблицу
• Переходим в сортировку
• Добавляем уровень
• Выбираем столбец для сортировки «Период работы в компании»
• Выбираем сортировку по значениям
• И в поле порядок выбираем «По убыванию» для сортировки от большего к меньшему
• Нажимаем «ОК» и наблюдаем изменения в таблице:

Файл с решенным кейсом — Кейс 5 (решенный)

Хочешь закрепить материал?

Реши задачу самостоятельно!

Имеются данные о расходах компании за два месяца:

Имеется информация о расходах компании.
На основном листе в файле с заданием в ячейке «А4» организован список для выбора месяца. Используя значение этой ячейки как критерий, необходимо вывести сумму расходов компании.

Необходимо отобразить данные таблицы в следующем порядке:

1. Вверху должны отображаться данные последнего месяца, все месяца позднее должны идти по порядку вниз таблицы.
2. Внутри каждого месяца статьи бюджета нужно отображать в следующей последовательности: расходы на рекламу, оплата труда, обслуживание оргтехники, прочие расходы, аренда.
3. Внутри каждой статьи бюджета суммы должны отображаться от большего к меньшему.

Для сортировки по месяцам необходимо сгруппировать данные. Воспользуйтесь функцией «МЕСЯЦ».

В рамках задания нужно создать три уровня сортировки.