Сортировать иерархический список строк с пустыми ячейками

[domo22]

Подскажите, пожалуйста, как отсортировать иерархический (древовидный) список строк с пустыми ячейками (сначала по первому столбцу, потом по второму и т.д.).
В приведенном примере сортировать нужно только по столбцам A, B и C, но их может быть от 1 до 6. Пустые ячейки нужно сохранить - т.е. их конечно можно для правильной сортировки заполнить чем-то, но потом надо снова очистить. Знания VBA к сожалению минимальны.

Исходный список:
----A---- ----B---- ----C---- ----D------
2002 год                             высокое 
              квартал 4              среднее 
              квартал 2               в работе 
                          неделя 2    низкое 
                          неделя 1    высокое
              квартал 3               среднее 
                          неделя 2    среднее 
                          неделя 1    низкое 
                          неделя 4    высокое 
2001 год                              среднее 
              квартал 2               низкое 
                          неделя 4    нет данных 
                          неделя 1    около нуля 
2003 год                              высокое 
 
Требуемый список:

2001 год                             среднее 
             квартал 2               низкое 
                         неделя 1    около нуля 
                         неделя 4    нет данных 
2002 год                             высокое 
             квартал 2               в работе 
                        неделя 1    высокое
                        неделя 2    низкое 
             квартал 3               среднее 
                         неделя 1    низкое 
                         неделя 2    среднее 
                         неделя 4    высокое 
             квартал 4               среднее 
2003 год                             высокое

[EducatedFool]

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

Скорее всего, только так и получится отсортировать...
Заполнить ячейки, в принципе, не проблема, очистить их потом - тоже.

Прикрепите файл.

Объясните, как должна осуществляться сортировка:

1 вариант: на листе 2 кнопки - заполнить и очистить. Вы нажимаете кнопку Заполнить, потом вручную сортируете как надо, после чего нажимаете Очистить

2 вариант: на листе одна кнопка - сортировать, при нажатии на которую макрос заполняет пустые поля, сортирует заранее определённым способом, и очищает нужные ячейки.

[domo22]

Прикрепите файл

Base_ex.zip

Объясните, как должна осуществляться сортировка

Конечно, более универсальный вариант предпочтительнее. Таким вроде бы выглядит вариант 1. Но главное, чтобы сортировку можно было сделать по нескольким столбцам: в одном случае - по двум, в другом - по пяти и т.д. и чтоб столбцы эти не очень сложным способом можно было указать. И еще - сортировка должна работать как минимум в Excel 2002-2003 (еще лучше - и для 97 тоже или универсальная).

А может вариант 2 все-таки лучше: указал в самом макросе сортировка прямая или обратная, задал по каким столбцам сортировать, вернулся в Excel, выделил нужный диапазон строк, нажал всего одну кнопку и готово... Но лучше было бы, чтоб и столбцы, по каким сортировать, можно было указывать из Excel, а не Бейсика...

Внимание: при заполнении пустых ячеек вручную данными из вышестоящей ячейки и стандартной сортировке (меню Данные -> Сортировка -> по возрастанию), первая строка каждой группы у меня почему-то становится последней в этой группе, что неприемлемо.

[EducatedFool]

Может, как-то так?

Нажмите сначала зелёную, а потом желтую кнопочку:

[domo22]

Боже мой, программа не только правильно сортирует, она еще и элегантно оформлена! Ну это же надо! Спасибо огромное.

Но все-таки есть несколько вопросов:

1. Пожалуйста, опишите какие действия происходят при нажатии на Зеленую, Желтую и Красную кнопки. Хоть и мало знаний, но все-таки очень интересно.

2. Если галочка "Отображать пустые ячейки" снята, то можно ли чтобы программа в этом случае сохраняла исходное форматирование строк? Или даже в любом случае сохраняла? А то строки и ячейки часто раскрашены в разные цвета и все пропадает. Это возможно подправить?

3. Можно расширить помощь при нажатии на Желтую кнопку? Сейчас "Выполните сортировку через меню Excel", а сделать типа "Выделите нужный диапазон строк, а затем выполните сортировку через меню Excel. Сортировка возможна максимум по ... столбцам".

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

[EducatedFool]

какие действия происходят при нажатии на Зеленую, Желтую и Красную кнопки

Откройте VBA, и посмотрите код.
Там 3 макроса:
Sub ЗаполнитьПустыеЯчейки()
Sub Очистка()
Sub Сортировка()

можно ли чтобы программа в этом случае сохраняла исходное форматирование строк?

Конечно, можно. Достаточно убрать несколько строк.
Раскраска ячеек (фон, шрифт, и полужирное начертание) реализована исключительно для наглядности.

Можно расширить помощь при нажатии на Желтую кнопку?

Можно. Проблема в том, что в некоторые пустые ячейки добавляется единица, что не мешает сортировке по возрастанию. Однако при сортировке по убыванию пустые ячейки надо заполнять не единицами, а строками типа "яяя". То есть макрос перед заполнением пустых ячеек должен знать, какой столбец в каком порядке будет сортироваться.

Сейчас "Выполните сортировку через меню Excel", а сделать типа "Выделите нужный диапазон строк

За это отвечает строка
MsgBox "Выполните сортировку через меню Excel"
Текст можно поставить любой.

Длину всех трех кнопок сделать где-то на четверть меньше

Ну, с этим Вы и сами справитесь.
Выделите кнопку, удерживая клавишу Ctrl, и измените её размер (или перетащите на нужное место).

[domo22]

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

1. Прошу посмотреть исправленный код и внести правку, если нужно.
База-пример-3.zip

2. Правильно ли я понимаю, что сортировка строк возможна только если они находятся начиная с 3-й строки и расположены в 6-ти столбцах и идет она по 4-м столбцам? А иначе надо менять код? Или нет?

3. Из-за слабых знаний Бейсика мне не удалось сделать сортировку по убыванию. Можете помочь и добавить второй ряд кнопок для сортировки по убыванию?

[ing60]

Извините, не туда залил.

[EducatedFool]

Правильно ли я понимаю, что сортировка строк возможна только если они находятся начиная с 3-й строки и расположены в 6-ти столбцах и идет она по 4-м столбцам? А иначе надо менять код? Или нет?

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

К примеру, надо отсортировать таблицу по 4-му столбцу (в данном случае неважно, по возрастанию или по убыванию).
Для этого мы во избежание перемешивания строк вынуждены задать сортировку не только по 4-му столбцу, а по всем столбцам, начиная с первого, и заканчивая 4-м.

И всё бы хорошо, но у меня не установлен Excel 2007... а 2003-я версия не позволяет задать более 3 уровней сортировки:

В Office Excel 2007 с помощью расширенных средств сортировки и фильтрации можно быстро представить данные в листе таким образом, чтобы получить нужные ответы. Например, теперь можно сортировать данные по цветам и по большему, чем 3 (вплоть до 64), количеству уровней. Можно также фильтровать данные по цветам или датам, отображать более 1000 элементов в раскрывающемся списке «Автофильтр», выделять несколько элементов для фильтрации и фильтровать данные в сводных таблицах.

Поэтому я не могу предложить Вам универсального решения задачи средствами Excel 2003.


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

2) Уже после этого выбора пользователь нажимает зелёную кнопку для заполнения таблицы (пустые ячейки в каждом столбце теперь заполняются значениями "1" или "яяя" - в зависимости от режима сортировки в текущем столбце)

3) Нажимать желтую кнопочку для сортировки нет необходимости - поскольку критерии сортировки указаны, она будет выполнена автоматически.

Но, повторюсь, Excel 2007 у меня не установлен, поэтому реализовать и проверить макрос я не могу.



Ну а то, что строго заданы диапазоны ячеек:

Const ДиапазонДляЗаполнения = "a:d"
Const ДиапазонДляСортировки = "a:f"

sh.[3:65000]

так это не проблема (это было сделано лишь в качестве примера)

Макрос легко может определить границы таблицы (если Вы укажете критерии), и, соответственно, осуществлять обработку нужного диапазона.

[domo22]

Спасибо за пояснения, хотя мне еще не все понятно.

И всё бы хорошо, но у меня не установлен Excel 2007... а 2003-я версия не позволяет задать более 3 уровней сортировки:
Поэтому я не могу предложить Вам универсального решения задачи средствами Excel 2003.

Так у меня же тоже excel-2003, об этом написано сразу в первом сообщении. В нем для, например, 5-ти колоночной сортировки сначала надо сортировать по пятому столбцу, затем по четвертому, затем одновременно по первону, второму, третьему.

1) Во второй (пустой) строке листа пользователь для каждого столбца (или только для некоторых столбцов) выбирает режим сортировки неважно, как именно

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

Excel 2007у меня не установлен, поэтому реализовать и проверить макрос я не могу

А и не надо на 2007. На 2003 - более-менее приемлемый вариант (максимум 6 столбцов, обычно 2-4, нельзя 6 - значит 3), такое можно?

Макрос легко может определить границы таблицы (если Вы укажете критерии)

А как эти критерии указывать? В самом макросе, или не трогая макрос а выделяя мышкой, или как?