Как связать таблицу в эксель для сортировки
Сортировка данных – это интегральная часть анализа. Сортировка позволяет быстро отобразить данные для их легкого восприятия и понимания. Сортировка в Excel позволяет организовать и быстро находить нужные значения для принятия наиболее эффективных решений. Программа Excel предоставляет широкие возможности для сортировки. Можно выполнять сортировку данных по нескольким условиям и столбцам и даже на основе цвета заливки ячеек.
Как сделать сортировку в Excel по нескольким столбцам
Инструмент сортировки в Excel всегда под рукой. Например, настраиваемую сортировку можно включить на закладке: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Настраиваемая сортировка» или на закладке: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
Допустим у нас имеется таблица, которую нужно сортировать по нескольким столбцам:
Чтобы отсортировать диапазон ячеек по нескольким условиям следует:
- Перейдите на любую ячейку диапазона, который содержит данные и включите настраиваемую сортировку одним из выше описанных способов. Например, так:
- В появившемся окне активируйте галочкой опцию «Мои данные содержат заголовки».
- Откройте выпадающий список «Сортировать по:» и выберите опцию «Дата». Во второй секции оставляем значение выпадающего списка по умолчанию «Значения», а в третьей «Порядок» выбираем опцию «От старых к новым».
- Кликните на кнопке «Добавить уровень». В первой секции выбираем опцию «Город», во второй «Значение», в третьей «От А до Я».
- Еще раз добавьте новый уровень (новое условие сортировки). На новом уровне в первом выпадающем списке выбираем опцию «Продавец», а остальные оставляем без изменений – «Значение» и «От А до Я».
- Снова нажмите на кнопку «Добавить уровень». В первой секции указываем «Сумма», вторая секция – без изменений, а в секции «Порядок» должно быть значение «По возрастанию». И нажите ОК.
Список отсортирован по 4 условиям:
- Самая старая дата – 20.07.2017.
- В этой дате название города в алфавитном порядке – Екатеринбург.
- Далее в этой дате в этом городе первый продавец по алфавиту – Луналика.
- Наименьшая сумма продаж Луналики в городе Екатеринбург за 20.07.2017 – 47 817,00₽.
Максимальное количество уровней в настраиваемой сортировке может достигать до 64-ох условий. Обратите внимание на несколько дополнительных опций, которые помогают организовать и создавать новые условия в окне настраиваемой сортировки. Слева от кнопки «Параметры» находиться две кнопки со стрелками:
Благодаря им можно упорядочить порядок выполнения условий в нижней области уровней. Порядок положений уровней существенно влияет на конечный результат сортировки по нескольким условиям. Если условия мало чем отличаются между собой можно воспользоваться кнопкой «Копировать уровень». В результате под активным (выделенным) уровнем создается новый с такими же параметрами критериев, где пользователю остается лишь внести небольшие корректировки.
В третьей секции «Порядок» пользователь определяет тип сортировки значений в области одного столбца. Стоит заметить, что Excel автоматически подставляет опции к формату значений, находящихся в столбце. Например, для дат порядок сортировки будет «От старых к новым» или наоборот, а для чисел – «По возрастанию». В связи с этим упорядочиться условия в соответствии с типом исходных значений. Но что если в одном столбце несколько типов значений (например, текст и числа)? Тогда порядок сортировки будет выполняться по тем типам которых больше в данном столбце.
Внимание! Каждый раз при вызове инструмента «Настраиваемая сортировка» следует обращать внимание включена или отключена опция «Мои данные содержат заголовки» (в правом верхнем углу диалогового окна инструмента). Если заголовков на самом деле нет, а данная опция включена это негативно отразиться на правильности сортировки данных, так как значения в первой строке не примут участия и не будут учитываться. Программа воспримет такие значения за названия заголовков.
Другие способы сортировки списков в Excel
Чтобы сортировать список данных, можно его заменить на таблицу. Таким образом сортировка может быть выполнена с помощью выпадающего меню появившихся в заголовках столбцов таблицы. Для этого:
- Перейдите курсором на любую ячейку в область данных и выберите инструмент: «ВСТАВКА»-«Таблица» или нажмите комбинацию горячих клавиш CTRL+T.
- В появившемся окне «Создание таблицы» автоматически заполниться поле ввода ссылкой на диапазон ячеек. При необходимости измените его на свой адрес. Если же после вызова диалогового окна поле ввода «Укажите расположения данных таблицы:» оказалось пустым, то заполните его ссылкой на необходимый диапазон.
- Активируйте галочкой опцию «Таблица с заголовками» и нажмите на кнопку ОК.
Список преобразован в таблицу Excel.
Полезный совет! Выпадающие списки меню автофильтра, имеют такие же опции сортировки по возрастанию и убыванию:
Можно не создавать таблицу а просто перейти курсором в любую ячейку списка данных и включить автофильтр: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.
Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.
Порядок сортировки в Excel
Существует два способа открыть меню сортировки:
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
- Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
- Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Сортировка по цвету ячейки и по шрифту
Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.
Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
- Выделяем столбец – правая кнопка мыши – «Сортировка».
- Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
- Соглашаемся «автоматически расширить диапазон».
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».
В открывшемся окне вводим необходимые параметры:
Здесь можно выбрать порядок представления разных по цвету ячеек.
По такому же принципу сортируются данные по шрифту.
Сортировка в Excel по нескольким столбцам
Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.
Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.
Сортировка строк в Excel
По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:
Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.
Случайная сортировка в Excel
Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.
Например, нужно расположить в случайном порядке набор неких чисел.
Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.
Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.
Динамическая сортировка таблицы в MS Excel
Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
- Есть набор простых чисел, которые нужно отсортировать по возрастанию.
- Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
- Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.
Для динамической сортировки текстовых значений понадобятся формулы массива.
- Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
- Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
- В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
- Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон "фрукты" еще одно значение "помело" и проверим:
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе "Могут потребоваться связи между таблицами" нажмите Создать.
В поле "Связанная таблица" выберите On_Time_Performance, а в поле "Связанный столбец (первичный ключ)" — FlightDate.
В поле "Таблица" выберитеBasicCalendarUS, а в поле "Столбец (чужой)" — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле "Сортировать" выберите MonthInCalendar.
В поле "По" выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
"Могут потребоваться связи между таблицами"
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Для удобства работы с большим массивом данных в таблицах, их постоянно нужно упорядочивать по определенному критерию. Кроме того, для выполнения конкретных целей, иногда весь массив данных не нужен, а только отдельные строки. Поэтому, чтобы не путаться в огромном количестве информации, рациональным решением будет упорядочить данные, и отфильтровать от других результатов. Давайте выясним, как производится сортировка и фильтрация данных в программе Microsoft Excel.
Простая сортировка данных
Сортировка является одним из самых удобных инструментов при работе в программе Microsoft Excel. С помощью неё, можно расположить строчки таблицы в алфавитном порядке, согласно данным, которые находятся в ячейках столбцов.
Сортировку данных в программе Microsoft Excel можно выполнять, воспользовавшись кнопкой «Сортировка и фильтр», которая размещена во вкладке «Главная» на ленте в блоке инструментов «Редактирование». Но, прежде, нам нужно кликнуть по любой ячейке того столбца, по которому мы собираемся выполнить сортировку.
Например, в предложенной ниже таблице следует отсортировать сотрудников по алфавиту. Становимся в любую ячейку столбца «Имя», и жмем на кнопку «Сортировка и фильтр». Чтобы имена упорядочить по алфавиту, из появившегося списка выбираем пункт «Сортировка от А до Я».
Как видим, все данные в таблице разместились, согласно алфавитному списку фамилий.
Для того, чтобы выполнить сортировку в обратном порядке, в том же меню выбираем кнопку Сортировка от Я до А».
Список перестраивается в обратном порядке.
Нужно отметить, что подобный вид сортировки указывается только при текстовом формате данных. Например, при числовом формате указывается сортировка «От минимального к максимальному» (и, наоборот), а при формате даты – «От старых к новым» (и, наоборот).
Настраиваемая сортировка
Но, как видим, при указанных видах сортировки по одному значению, данные, содержащие имена одного и того же человека, выстраиваются внутри диапазона в произвольном порядке.
А, что делать, если мы хотим отсортировать имена по алфавиту, но например, при совпадении имени сделать так, чтобы данные располагались по дате? Для этого, а также для использования некоторых других возможностей, все в том же меню «Сортировка и фильтр», нам нужно перейти в пункт «Настраиваемая сортировка…».
После этого, открывается окно настроек сортировки. Если в вашей таблице есть заголовки, то обратите внимание, чтобы в данном окне обязательно стояла галочка около параметра «Мои данные содержат заголовки».
В поле «Столбец» указываем наименование столбца, по которому будет выполняться сортировка. В нашем случае, это столбец «Имя». В поле «Сортировка» указывается, по какому именно типу контента будет производиться сортировка. Существует четыре варианта:
- Значения;
- Цвет ячейки;
- Цвет шрифта;
- Значок ячейки.
Но, в подавляющем большинстве случаев, используется пункт «Значения». Он и выставлен по умолчанию. В нашем случае, мы тоже будем использовать именно этот пункт.
В графе «Порядок» нам нужно указать, в каком порядке будут располагаться данные: «От А до Я» или наоборот. Выбираем значение «От А до Я».
Итак, мы настроили сортировку по одному из столбцов. Для того, чтобы настроить сортировку по другому столбцу, жмем на кнопку «Добавить уровень».
Появляется ещё один набор полей, который следует заполнить уже для сортировки по другому столбцу. В нашем случае, по столбцу «Дата». Так как в данных ячеек установлен формат даты, то в поле «Порядок» мы устанавливаем значения не «От А до Я», а «От старых к новым», или «От новых к старым».
Таким же образом, в этом окне можно настроить, при необходимости, и сортировку по другим столбцам в порядке приоритета. Когда все настройки выполнены, жмем на кнопку «OK».
Как видим, теперь в нашей таблице все данные отсортированы, в первую очередь, по именам сотрудника, а затем, по датам выплат.
Но, это ещё не все возможности настраиваемой сортировки. При желании, в этом окне можно настроить сортировку не по столбцам, а по строкам. Для этого, кликаем по кнопке «Параметры».
В открывшемся окне параметров сортировки, переводим переключатель из позиции «Строки диапазона» в позицию «Столбцы диапазона». Жмем на кнопку «OK».
Теперь, по аналогии с предыдущим примером, можно вписывать данные для сортировки. Вводим данные, и жмем на кнопку «OK».
Как видим, после этого, столбцы поменялись местами, согласно введенным параметрам.
Конечно, для нашей таблицы, взятой для примера, применение сортировки с изменением места расположения столбцов не несет особенной пользы, но для некоторых других таблиц подобный вид сортировки может быть очень уместным.
Фильтр
Кроме того, в программе Microsoft Excel существует функция фильтра данных. Она позволяет оставить видимыми только те данные, которые вы посчитаете нужными, а остальные скрыть. При необходимости, скрытые данные всегда можно будет вернуть в видимый режим.
Чтобы воспользоваться данной функцией, становимся на любую ячейку в таблице (а желательно в шапке), опять жмем на кнопку «Сортировка и фильтр» в блоке инструментов «Редактирование». Но, на этот раз в появившемся меню выбираем пункт «Фильтр». Можно также вместо этих действий просто нажать сочетание клавиш Ctrl+Shift+L.
Как видим, в ячейках с наименованием всех столбцов появился значок в виде квадрата, в который вписан перевернутый вниз треугольник.
Кликаем по этому значку в том столбце, по данным которого мы собираемся проводить фильтрацию. В нашем случае, мы решили провести фильтрацию по имени. Например, нам нужно оставить данные только работника Николаева. Поэтому, снимаем галочки с имен всех остальных работников.
Когда процедура выполнена, жмем на кнопку «OK».
Как видим, в таблице остались только строки с именем работника Николаева.
Усложним задачу, и оставим в таблице только данные, которые относятся к Николаеву за III квартал 2016 года. Для этого, кликаем по значку в ячейке «Дата». В открывшемся списке, снимаем галочки с месяцев «Май», «Июнь» и «Октябрь», так как они не относятся к третьему кварталу, и жмем на кнопку «OK».
Как видим, остались только нужные нам данные.
Для того, чтобы удалить фильтр по конкретному столбцу, и показать скрытые данные, опять кликаем по значку, расположенному в ячейке с заглавием данного столбца. В раскрывшемся меню кликаем по пункту «Удалить фильтр с…».
Если же вы хотите сбросить фильтр в целом по таблице, тогда нужно нажать кнопку «Сортировка и фильтр» на ленте, и выбрать пункт «Очистить».
Если нужно полностью удалить фильтр, то, как и при его запуске, в этом же меню следует выбрать пункт «Фильтр», или набрать сочетание клавиш на клавиатуре Ctrl+Shift+L.
Кроме того, следует заметить, что после того, как мы включили функцию «Фильтр», то при нажатии на соответствующий значок в ячейках шапки таблицы, в появившемся меню становятся доступны функции сортировки, о которых мы говорили выше: «Сортировка от А до Я», «Сортировка от Я до А», и «Сортировка по цвету».
Умная таблица
Сортировку и фильтр можно также активировать, превратив область данных, с которой вы работаете, в так называемую «умную таблицу».
Существует два способа создания «умной таблицы». Для того, чтобы воспользоваться первым из них, выделяем всю область таблицы, и, находясь во вкладке «Главная», кликаем по кнопке на ленте «Форматировать как таблицу». Данная кнопка находится в блоке инструментов «Стили».
Далее, выбираем один из понравившихся стилей, в открывшемся перечне. На функционал таблицы выбор влиять не будет.
После этого, открывается диалоговое окно, в котором можно изменить координаты таблицы. Но, если вы ранее выделили область правильно, то больше ничего делать не нужно. Главное, обратите внимание, чтобы около параметра «Таблица с заголовками» стояла галочка. Далее, просто нажать на кнопку «OK».
Если вы решили воспользоваться вторым способом, тогда тоже нужно выделить всю область таблицы, но на этот раз перейти во вкладку «Вставка». Находясь тут, на ленте в блоке инструментов «Таблицы», следует нажать на кнопку «Таблица».
После этого, как и в прошлый раз, откроется окно, где можно скорректировать координаты размещения таблицы. Жмем на кнопку «OK».
Независимо от того, каким способом вы воспользуетесь при создании «умной таблицы», в итоге получите таблицу, в ячейках шапки которой уже будет установлены значки фильтров, описанные нами ранее.
При нажатии на этот значок, будут доступны все те же функции, что и при запуске фильтра стандартным способом через кнопку «Сортировка и фильтр».
Как видим, инструменты сортировки и фильтрации, при правильном их использовании, могут значительно облегчить пользователям работу с таблицами. Особенно актуальным вопрос их использования становится в том случае, если в таблице записан очень большой массив данных.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Добрый день!
В моих таблицах мне обязательно необходимо сохранение возможности пользоваться функцией сортировки (от старых к новым). В какой-то момент высоты строки перестает хватать и… я попробовал объединить соседние строки по высоте. Но при объединении двух и более ячеек эта функция сортировки сразу перестает работать! Я всегда считал Эксель гибкой программой, но оказывается в таком важном и довольно простом в моем понимании вопросе Эксель становится динозавром. (
Не спешите вешать уничижительный ярлык в принципе, прежде всего допустите свою некомпетентность.
в описанном Вами случае попробуйте
встать на «тесную» ячейку
по правой кнопке перейти на формат ячеек
на закладке «выравнивание» поставьте галку в «переносить по словам»
подведите курсор к нижней границе порядкового номера номера строки, в которой находится «пока еще тесная» ячейка, так чтобы указатель изменился на крест +.
Двойной щелчок правой кнопкой мыши и аллилуйя!
Вы или не поняли моей проблемы или не поняли что написали, уважаемый вы наш компетентный человече.
для изменения высоты строки используйте команду Формат-> Автовысота строки (тогда высота строки изменится на высоту текста в строке) или Формат -> Высота строки ( тогда можно самому задать нужную высоту строки) и ничего объединять для этого не нужно
Задайте вопрос или оставьте свое мнение Отменить комментарий
Как отсортировать связанные данные и сохранить формулы в Excel?
Если список ячеек имеет несколько формул или связан с другими ячейками на том же листе, связанные данные будут изменены при сортировке ячеек, как показано ниже. В этой статье я расскажу о методах сортировки связанных данных и хранения формул в Excel.
Сортировка связанных данных и сохранение формул с помощью Kutools for Excel
Сортируйте связанные данные и сохраняйте формулы с помощью ярлыков
Чтобы отсортировать связанные данные и сохранить формулы без изменений, вы можете изменить ссылки в формулах на абсолютные ссылки, а затем отсортировать данные. Таким образом, данные сохранят формулы, даже если их порядок изменится.
Выберите ячейку формулы, выберите формулу в строке формул и нажмите F4 Клавиша изменения ссылки на абсолютную ссылку.
Функции: Если в одной ячейке несколько ссылок, вам нужно изменить ссылки одну за другой, выбрав и нажав F4 в строке формул.
Затем поочередно измените ссылки в других ячейках формулы на абсолютные.
Теперь формулы сохраняются при сортировке.
Сортировка связанных данных и сохранение формул с помощью Kutools for Excel
Изменение ссылок по очереди занимает много времени, но с Преобразовать ссылки полезность в Kutools for Excel, вы можете быстро изменить все ссылки в диапазоне абсолютных ссылок по мере необходимости.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите ячейки, ссылку на которые вы хотите изменить, нажмите Кутулс > Подробнее (в группе Формула) > Преобразовать ссылки.
2. в Преобразование ссылок на формулы диалог, проверьте К абсолютному вариант. Смотрите скриншот:
3. Нажмите Ok. Затем весь выбор был преобразован в абсолютные ссылки.
Читайте также: