Как отсортировать таблицу. Установка режима обтекания текстом. Сортировка по цвету ячейки и по шрифту

Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) - по текстовому столбцу.

Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй - числовой Объем Продаж (см. файл примера ).

Задача1 (Сортировка таблицы по числовому столбцу)

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

Для наглядности величины значений в столбце Объем Продаж выделены с помощью (). Также желтым выделены повторяющиеся значения.

Примечание : Задача сортировки отдельного столбца (списка) решена в статьях и .

Решение1

Если числовой столбец гарантировано не содержит значений, то задача решается легко:

• Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью );
• Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

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

Поэтому механизм сортировки придется реализовывать по другому.

ИНДЕКС(Продажи;
ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(
--(СЧЁТЕСЛИ(Продажи;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
СТРОКА()-СТРОКА($E$6));1)*1000;0)
)

Данная формула сортирует столбец Объем продаж (динамический диапазон Продажи ) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 1000.

Разберем формулу подробнее:

• Формула СЧЁТЕСЛИ(Продажи;"<"&Продажи) возвращает массив {4:5:0:2:7:1:3:5}. Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 - самое маленькое (оно никого не больше) и т.д.
• Теперь вышеуказанный массив целых чисел превратим в массив чисел с дробной частью, где в качестве дробной части будет содержаться номер позиции числа в массиве: {4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008}. Это реализовано выражением &","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6)) Именно в этой части формулы заложено ограничение о не более 1000 строк в исходной таблице (см. выше). При желании его можно легко изменить, но это бессмысленно (см. ниже раздел о скорости вычислений).
• Функция НАИБОЛЬШИЙ() сортирует вышеуказанный массив.
• Функция ОСТАТ() возвращает дробную часть числа, представляющую собой номера позиций/1000, например 0,005.
• Функция ОКРУГЛ() , после умножения на 1000, округляет до целого и возвращает номер позиции. Теперь все номера позиций соответствуют числам столбца Объемы продаж, отсортированных по убыванию.
• Функция ИНДЕКС() по номеру позиции возвращает соответствующее ему число.

Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(...))

В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)

Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.

Для наглядности, величины значений в столбце Объем Продаж выделены с помощью (Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.

Тестируем

Теперь добавим новую строку в исходную таблицу. В динамически сортируемых таблицах мы должны получить соответствующую сортировку.

1. В ячейку А15 исходной таблицы введите слово Морковь ;
2. В ячейку В15 введите Объем продаж Моркови = 25;
3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица;
4. В сортированной таблице новая строка будет отображена предпоследней.

Скорость вычислений формул

На "среднем" по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа (Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).

Альтернативные подходы к сортировке таблиц

Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.

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

Также можно воспользоваться инструментом Сортировка (Данные/ Сортировка и Фильтр/ Сортировка ). Для этого нужно выделить все значения исходной таблицы не включая заголовок, вызвать инструмент Сортировка, выбрать столбец, по которому требуется сортировать и вариант сортировки.

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

Как и в предыдущей задаче предположим, что в столбце, по которому ведется сортировка имеются повторы (названия Фруктов повторяются).

Для сортировки таблицы придется создать 2 служебных столбца (D и E).

=СЧЁТЕСЛИ($B$7:$B$14;"<"&$B$7:$B$14)+1

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

В столбце E введем обычную формулу:

=СЧЁТЕСЛИ($D$6:D6;D7)+D7

Эта формула учитывает повторы текстовых значений и корректирует "ранг". Теперь разным значениям Яблоки соответствуют разные "ранги" - 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):

=ИНДЕКС($B$7:$B$14;ПОИСКПОЗ(СТРОКА()-СТРОКА($G$6);$E$7:$E$14;0))

Аналогичная формула выведет соответствующий объем продаж (столбец Н).

Задача 2.1 (Двухуровневая сортировка)

Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.

Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.

Подробности в файле примера на листе Задача2.

Сортировка данных – это одна из наиболее важных команд для организации таблицы. Целью сортировки часто является упорядочивание записей на основе выбранного критерия. Часто сортировку используют, если данные в таблице набирались постепенно, и каждая запись производилась просто на одну строку ниже. Найти информацию в такой таблице очень трудно, применение сортировки помогает упорядочить данные.

Для сортировки данных в таблице сделайте следующее.

1. Выберите столбцы для сортировки или установите курсор в таблице, если нужно отсортировать столбец полностью.

2. Перейдите на вкладку Работа с таблицами -> Макет, нажмите кнопку Данные и щелкните на кнопке Сортировка (рис. 5.27).

Рис. 5.27. Меню кнопки Данные

Примечание

При разрешении экрана больше 1024 x 768 группа Данные будет видна полностью и можно будет сразу щелкать на кнопке Сортировка (рис. 5.28).

Рис. 5.28. Группа Данные при разрешении монитора больше 1024 x 768

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

Рис. 5.29. Окно Сортировка

4. Выберите тип данных сортировки: текст, число или дата.

5. Установите тип сортировки: по возрастанию или по убыванию.

6. При необходимости установите параметры для вторичной сортировки по второму и третьему столбцам.

7. Если нужно применить сортировку, при которой будет учитываться регистр букв, нажмите кнопку Параметры и установите флажок учитывать регистр в области Параметры сортировки (рис. 5.30).

Рис. 5.30. Окно Параметры сортировки

8. Нажмите кнопку OK, чтобы подтвердить выбранные параметры сортировки.

Совет

Если у вас возникли вопросы, которые касаются рассмотренных в этой главе тем, обратитесь к разд. 11.3 – возможно, вы найдете ответ на интересующий вас вопрос.

Обзор: Сортировка

Word выполняет сортировку абзацев текста и строк/столбцов - таблиц.

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

Программа Word распознает таблицы автоматически.

В таблице Word различает сортировку по строкам (абзацам, записям) и по столбцам (полям):

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

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

Для каждой процедуры сортировки можно задать до трех клю­чей. Сначала выполняется сортировка по первому ключу, затем - по второму и наконец по третьему. Программа Word различает несколько типов сортируемой ин­формации: данные типа Текст включают буквы и цифры (даты рассматриваются как числа), данные типа Число включают только цифры (другие символы игнорируются), а данные типа Дата - корректно сформатированные значения дат. В качестве порядка сортировки выбирается либо По возрас­танию (А...Я; 0...9; более ранняя дата... более поздняя дата), либо По убыванию (Я...А; 9...0; более поздняя дата... более ранняя дата). Из соображений безопасности рекомендуется сохранить текст перед выполнением сортировки, даже несмотря на то, что операцию сортировки можно отменить обычным путем.

Процедура. Сортировка по строкам (сортировка списков)

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

2. Выполните команду Таблица/Сортировка текста или Табли­ца/Сортировка. Открывается диалоговое окно.

3. Задайте первый ключ сортировки.

В первом списке для представленных в форме таблицы дан­ных можно выбрать поле (столбец), по содержимому кото­рого будет выполняться сортировка. Например, Поле 3 сор­тирует по третьему столбцу.

В поле Тип следует выбрать тип сортируемой информации.

Определите порядок сортировки: По возрастанию или По убыванию.

4. Нажмите командную кнопку Параметры и в группе Раздели­тель полей выберите нужную опцию (выбор возможен, если сортируется текст, т.е. список, а не таблица Word).

Опцию Символ табуляции следует активизировать, если отдельные элементы строки (поля) отделены один от дру­гого символами табуляции.

Опцию Точка с запятой следует активизировать, если от­дельные элементы строки (поля) отделены один от другого точками с запятой.

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

Посредством параметра С учетом регистра можно заста­вить Word отсортировать слова, записанные прописными буквами, от слов, записанных строчными буквами, и вывес­ти первые перед вторыми.

Параметр Только столбцы позволяет выполнить сортиров­ку только отдельных столбцов - сортировку по столбцам.

Закройте диалоговое окно Параметры сортировки нажатием кнопки ОК. При необходимости задайте и другие ключи сор­тировки. Если не все элементы заданного в качестве первого ключа столбца различаются один от другого, то для выполне­ния полной иерархической сортировки следует использовать несколько ключей. В группе Список выберите необходимое значение опции. Если первая строка (запись, абзац) из выделенных использу­ется в качестве заголовка таблицы (заголовка списка запи­сей), то ее можно исключить из сортировки, выбрав опцию Со строкой заголовка. Нажмите кнопку ОК или клавишу , чтобы запустить про­цесс сортировки. Если полученный результат вас не удовле­творит, вы сможете отменить операцию сортировки, как и любые другие действия, с помощью команды Правка/Отме­нить.

Процедура. Сортировка по столбцам

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

Выделите нужное поле (поля) в списке записей с помощью ++ или путем перетаскивания мыши при нажатой клавише и левой кнопке мыши. В таблице выделите подлежащие сортировке столбцы таблицы. Одновременно может выполняться сортировка нескольких соседних столбцов (полей), однако при этом следует проследить за тем, чтобы было выделено все содержимое всех сортируемых столбцов (полей). Выполните команду Таблица/Сортировка текста или Табли­ца/Сортировка. Открывается диалоговое окно. Определите первый ключ сортировки и щелкните на кнопке Параметры. На экран выводится диалоговое окно Парамет­ры сортировки. Установите флажок параметра Только столбцы. При необходимости установите и другие параметры и под­твердите установку нажатием кнопки ОК. Word возвращается в предыдущее диалоговое окно. Нажмите кнопку ОК или клавишу , чтобы запустить про­цесс сортировки.

В Word можно провести сортировку как текста, списка, так и сортировку данных в таблице Word . Рассмотрим, как в Word сортировать по алфавиту , сортировать списки, строки в таблице Word.
Сортировка Word по алфавиту .
Например, у нас такой список. Сделаем сортировку списка в Word .
Нам нужно отсортировать его по алфавиту. На закладке «Главная» в разделе «Абзац» нажимаем на кнопку функции «Сортировка» (на изображении обведена красным цветом).
В появившемся диалоговом окне ставим галочку «По возрастанию», если нужно, чтобы список начинался с буквы А.
Нажимаем «ОК». Получился такой список.
Сортировка в таблице Word .
Эту сортировку можно применить в таблице Word. Какими способами создать таблицу в Word, смотрите в статье "Как сделать таблицу в Word ". Выделяем столбец таблицы. Диалоговое окно сортировки заполнили так.
Получилась такая таблица. Здесь отсортировали по убыванию – от большего к меньшему, от последнего к первому.
По такому же принципу сортируют числа. У нас такая таблица с числами.
Выделяем столбец в таблице. Нажимаем на кнопку «Сортировка» на закладке «Главная». В появившемся окне ставим галочку у слов «По возрастанию». Нажимаем «ОК». Получилось так.
На первый взгляд сложно выделить в таблице Word ячейку, столбец, не смежные ячейки, диапазоны. Но, узнав приемы быстрого выделения в таблице Word, будет легче, быстрее и понятнее работать. Смотрите о различных приемах выделения ячеек в таблице Word в статье "