Excel сортирует только один столбец

@ch98tom 

If you highlight just the column you want to sort, then under sort & Filter in the home tab, hit sort A-Z, then press the «continue with current selection» option. This will only sort the selected data, leaving the data next to it alone.

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

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

Сортировка по одному критерию

1. В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
2. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

1. Выбрать нужную кнопку: – сортировка по возрастанию или сортировка по убыванию.

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

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.

Многоуровневая сортировка

1. Выделить одну ячейку из сортируемого массива данных.

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

1. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
2. Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:

1. Имя столбца для сортировки.
2. Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
3. Порядок сортировки (по убыванию или по возрастанию).

Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.

Сортировка по форматированию

Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.

Пошаговый порядок действий:

1. Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
2. На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
3. В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
4. В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
5. Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
6. При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.

Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.

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

Например, список не сортируется или сортируется некорректно.

В этом случае необходимо проверить формат всех (или «проблемных») ячеек сортируемого столбца.

Формат должен быть одинаковым для всех его ячеек — Общий, Числовой или Текстовый.

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

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

Еще одной причиной может стать пробел в начале слова или числа. Особенно этот момент нужно проверить в случае импортирования данных из других программ.

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

Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».

Вы не даёте заголовки столбцам таблиц

Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

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

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Рекомендуемый формат таблицы выглядит так:

Разнесение информации по разным листам книги «для удобства»

Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.

Информация в комментариях

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

Бардак с форматированием

Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
   
2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

Объединение ячеек

Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.

Ещё больше полезной информации про Excel можно узнать на сайте Дениса.

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

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

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

1. Сортировка данных всего в несколько кликов
2. Установка нескольких, ступенчатых правил сортировки, таких как сортировка по алфавиту по состоянию, а затем по всё вместе.
3. Создать полностью свои настройки сортировки, чтобы отсортировать данные с помощью любого установленного вами правила.

Сортировка данных иногда может казаться опасной; что, если вы отсортируете только один столбец и данные будут смещены? Я покажу вам, как избежать этого. Давайте начнем.

Как сортировать данные в электронной таблице Excel (короткое видео)

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

Читайте дальше пошаговое руководство по сортировке данных в электронных таблицах Excel с использованием простых и передовых методов.

Примеры данных (бесплатная загрузка рабочей книги Excel)

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

1. Простая сортировка в Excel

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

В книге Excel, начните с нажатия на ячейку столбца, который вы хотите отсортировать. Теперь, убедитесь, что вы находитесь на вкладке Главная на ленте Excel’я и найдите кнопку Сортировка и фильтр на самой правой стороне этой панели.

Заметьте, что в вариантах сортировки, вы можете отсортировать текст «А до Я» или «Я до А». Эти простые варианты помогут отсортировать данные в Excel В алфавитном или обратным порядках, в зависимости от того что вы выбрали.

Когда вы сортируете данные в Excel, сортируется вся строка. По сути, выбранный вами столбец будет «ключом», который Excel использует, чтобы решить, как сортировать данные, но каждая строка это запись, которая должна оставаться сгруппированной вместе.

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

Выполнить обычную сортировку на самом деле так просто. Просто кликните по данным, выберите вариант сортировки и Excel перестроит данные в таблице.

Дельный совет: попробуйте также сортировать, щелкнув правой кнопкой мыши внутри столбца и выбрав Сортировка, а затем указать способ сортировки исходных данных.

2. Как НЕ нужно сортировать данные в Excel

Не менее важно узнать, о самом опасном способе сортировки данных в Excel, такой метод может испортить ваши исходные данные.

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

На изображении ниже, Я раскрасил строки, таким образом опасное место с сортировкой только одного столбца данных, будет выделено.

Большая ошибка пользователей Excel заключается в выборе только одного столбца при сортировке и выборе неправильного параметра в следующем окне.

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

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

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

На скриншоте ниже вы можете видеть, насколько проблематичен этот тип сортировки. Так как столбец Amount Billed был отсортирован от наименьшего до наибольшего, все остальные столбцы остались на месте. Это означает, что наши данные больше не верны.

Таким образом, при сортировке данных есть два ключевых «НЕ»:

1. Не начинайте, выделив один столбец в своей электронной таблице.
2. Не используйте вариант сортировать в приделах указанного диапазона, если вы работаете не с одним столбцом, убедитесь, что вы расширили выделенный диапазон.

3. Расширенная сортировка данных Excel.

До сих пор простая сортировка позволяла нам сортировать данные однотипно. Что, если мы хотим два типа данных в нашей сортировке?

Что, если мы хотим…

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

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

Чтобы начать работу, щелкните где-нибудь внутри своих данных и найдите параметр Сортировка и фильтр, а затем выберите Настраиваемая сортировка.

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

В моем случае я выберу Client в раскрывающемся меню и оставлю значение Сортировка  равным Значения, а Порядок — От А до Я. На простом языке это отсортирует электронную таблицу Excel на основе алфавитного порядка.

Теперь давайте нажмем Добавить уровень. Это создаст новую строку в параметрах сортировки и позволит нам добавить второй уровень организации.

Теперь я могу выбрать Amount billed во втором раскрывающемся списке. Комбинация этих двух правил начнется путем сортировки на основе имени клиента, а затем суммы, выставленного счёта за каждый проект.

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

Как только мы нажмем OK, Excel отсортирует таблицу на основе правил, которые мы создали в этом окне.

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

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

Повторение и продолжение обучения

Сортировка это ещё одно умение, которое нужно держать в готовом виде в своей книге Excel. Когда вам нужно повторно сопоставить данные в электронной таблице, слишком много времени можно потратить для вырезания и вставки строк в определенном порядке, поэтому сортировка является обязательной.

Как всегда, вы можете опираться на навыки, которые вы узнали сегодня, погрузившись в более удобные уроки по Excel. Посмотрите ссылки ниже:

• Сортировка данных это мощный навык, но иногда вам может понадобится подвести итог и уяснить наборы данных, поэтому вместо этого используйте сводные таблицы.
• Ниже приведен обзор дополнительных руководств по сортировке, если вы хотите изучить этот навык из другого источника.
• Узнайте, как работать с формулами Excel — Как работать с формулами в Excel: копирование, вставка и автозаполнение.

Как вы сортируете данные? Используете ли вы простые методы или расширенный инструмент сортировки для обеспечения идеальной сортировкт?