Группировать и сортировать строки таблицы

Question

У меня есть таблица, где столбец A содержит уникальное значение в каждой ячейке. Столбец B либо пуст, либо содержит число.

   |    A    | B 
---+---------+---
 1 | Gamma   | 1 
 2 | Echo    | 5 
 3 | Alpha   |   
 4 | Foxtrot | 3 
 5 | Bravo   |   
 6 | Charlie | 1 
 7 | Delta   |   

Я хотел бы отсортировать таблицу так, чтобы строки с непустым значением в столбце B отображались раньше строк с пустым в этом столбце. Строки с непустыми значениями в B также должны быть отсортированы в алфавитном порядке по столбцу A Порядок сортировки других строк не имеет значения.

Результат должен выглядеть примерно так:

   |    A    | B 
---+---------+---
 1 | Charlie | 1 
 2 | Echo    | 5 
 3 | Foxtrot | 3 
 4 | Gamma   | 1 
 5 | Alpha   |   
 6 | Bravo   |   
 7 | Delta   |   

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

Answer 1

Там нет на самом деле нет-скрытия-строк, не-фильтрации, одного решения -sort. Для этого нужно просто добавить формулу условного форматирования в столбец B:

Выберите B2:B8 , убедившись, что B2 является активной ячейкой, и добавьте новое правило условного форматирования формулы. Измените цвет шрифта правила на любой другой, кроме « Automatic и введите в качестве формулы следующее:

=B2=""

Обратите внимание, что хотя отображается строка заголовка, она не обязательна. (См. Примечания внизу моего другого ответа для получения дополнительной информации.)

Теперь все, что вам нужно сделать, чтобы получить желаемый результат, это двухуровневая сортировка:

1. Сортировка по колонке B на Font Color по заказу Automatic On Top
2. Сортировать по столбцу A по Values упорядоченным от A to Z

Тот же эффект может быть достигнут при использовании цвета заливки вместо цвета шрифта для правила условного форматирования и сортировки по Cell Color . (Используя цвет шрифта, проще не делать видимых изменений для данных. Я использовал красный, чтобы это было очевидно на скриншоте, но лучший цвет для общего случая - черный.)

---

Обратите внимание, что сортировку можно получить либо через меню « Data → « Sort & Filter → « Sort , либо с помощью комбинации клавиш Alt+A+S , либо из любого раскрывающегося списка внутри ячейки, когда включен режим фильтрации (Alt+A+T) (при условии, конечно, есть строка заголовка, пустая или нет, так как в противном случае сортировка не может быть гарантирована для правильной работы):

---

По сути, сортировка по цвету добавляет функцию группировки при сортировке, аналогичную той, что доступна в SQL.

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

Answer 2

Вот решение без скрытия строк, без фильтрации, без вспомогательных столбцов, без условного форматирования, с двумя сортировками, с тремя шагами, о которых я упоминал в комментарии к самоответу ОП. Было подтверждено, что работает в Excel 2007.

Шаг 1

Сортировать по столбцу B по Values упорядоченным по Smallest to Largest

Обратите внимание, что хотя отображается строка заголовка, она не обязательна. (См. Примечания для получения дополнительной информации.)

Шаг 2

Выделите верхнюю часть таблицы до последней строки с непустым значением в столбце B Для примера таблицы это будет A1:C5 .

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

• Ctrl+Вниз ;
• Ctrl+вправо столько раз, сколько требуется для перехода к крайнему правому столбцу таблицы (или просто удерживайте его, чтобы добраться до правого края листа);
• (Ctrl+Left, если вы выходите за пределы и заканчиваете в начале следующей таблицы или в крайнем правом столбце листа);
• Ctrl+Shift+Home ;

Если справа от таблицы нет других данных, вместо этого можно использовать эту альтернативную последовательность:

• Ctrl+Вниз ;
• Ctrl+Shift+Up ;
• Shift+пробел

Шаг 3

Сортировать по столбцу A по Values упорядоченным от A to Z:

---

Заметки:

• Сортировка на шаге 1 может быть выполнена либо с помощью пункта меню « Data → « Sort & Filter → « Sort (или его сочетания клавиш, соответствующие Alt+A+S ), либо из раскрывающегося списка внутри ячейки, когда включен режим фильтрации (при наличии заголовка). грести, конечно).
• Сортировка по шагу 3 может быть выполнена только через пункт меню.
• Этот метод гарантированно работает, даже если данные таблицы начинаются со строки 1 и не имеют строки заголовка. Только два моих решения (плюс ОП, конечно) будут работать в этом случае. Фактически, эти решения являются единственными, которые будут работать или даже могут работать для таблицы строки 1 без заголовка, если первая ячейка столбца B пустая.
• Он также будет работать независимо от состояния режима фильтра. (Если, конечно, таблица не содержит заголовков, начинается в первой строке и по какой-то причине включен режим фильтра. В этом случае выключите его и выключите уже!)

Answer 3

Кажется, что нет способа делать то, что я хочу, строго через сортировку, но вот что работает для меня:

1. Сортировать строки по столбцу B от малого к большому

2. Скрыть все строки, которые не имеют значения в столбце B

3. Сортировать строки по столбцу А по алфавиту

Answer 4

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

Это не просто сортировка, как вы просили, она использует фильтр. Но вы можете просто "выбрать" всю таблицу (собственно, включение режима фильтра сделает это за вас), выполнить шаги и вуаля. Также довольно легко "конвертировать" его в макрос. Что касается других решений, это решение не требует, чтобы вы скрывали (или "отменяли") нежелательные строки вручную. Также вам не нужно вводить дополнительную формулу (посредством условного форматирования столбца A или B или путем добавления другого столбца).

Во всяком случае здесь это идет:

Включите фильтр:

• выберите любую ячейку в своем списке и включите режим фильтра (на ленте вы найдете ее на вкладке "Данные", она должна называться "Фильтр"). Вы увидите маленький треугольник в первом ряду каждого столбца, который вы используете

  (примечание: если у вас нет заголовков столбцов, просто добавьте пустую строку в самом начале и выберите все используемые столбцы вручную, в данном случае A и B, вместо "просто" выбора ячейки в списке)

Переместите строки без значения B в конец списка:

• сортировать столбец B в порядке возрастания (нажав небольшую треугольную кнопку в первом ряду столбца B и выбрав сортировку по значению по возрастанию)

Сортировать колонку А и отключить / удалить фильтр

• отфильтровать строки с пустыми ячейками в столбце B (снова щелкнув по треугольной кнопке и сняв отметку со значения "пусто" в нижней части списка)
• сортировать столбец A в порядке возрастания (аналогично сортировке столбца B)
• отключить расширенную фильтрацию (так же, как вы ее включили), это автоматически удалит фильтр для пустых ячеек в столбце B

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

(обратите внимание, что скрытие строк вручную отличается от использования фильтра для "скрытия" их)