Сортировка записей в пользовательском порядке
Иногда требуется отсортировать данные не по алфавиту или по числу, а по пользовательскому порядку (настраиваемый или логический порядок). Например, можно отсортировать рабочие дни так, чтобы первым был понедельник, чем сортировка в алфавитном порядке, когда пятница отображается первой. Вы можете сортировать данные в представлении таблицы или запроса либо в представлении формы.
В этой статье
- Сортировка небольшого конечного количества уникальных значений полей
- Сортировка большого количества уникальных значений полей
Сортировка небольшого конечного количества уникальных значений полей
В этой процедуре вы напишем выражение, в качестве ранжируете названия сотрудников с помощью функции IIf.
Сначала создайте таблицу, которая содержит три поля: «Название», «Имя» и «Фамилия». В поле «Название» введите следующие значения для четырех новых записей:
- Inside Sales Разуваев
- Менеджер по продажам
- Sales Representative
- Вице-президент, отдел продаж
В полях «Имя» и «Фамилия» из четырех записей введите нужные имена.
- Теперь откройте таблицу в представлении таблицы.
- На вкладке Главная в группе Сортировка и фильтр нажмите кнопку Дополнительно, а затем в контекстном меню выберите команду Расширенный фильтр.
- Добавьте в сетку нужные поля, такие как «Имя», «Фамилия» и «Название».
- Щелкните правой кнопкой мыши строку поля в первом пустом столбце, выберите «Масштаб»и введите следующее выражение: IIf([Title] = «Vice President, Sales», 1,IIf([Title] = «Sales Manager», 2,IIf([Title] = «Sales Representative», 3,IIf([Title] = «Inside Sales Вуалю», 4, 5)))) Название — это имя поля, которое содержит ранжировали значения. Значения, заключенные в кавычках, являются отдельными значениями, храниммими в поле. После него будет ранж, присвоенный ему. Предыдущее выражение присваивает ранг от 1 до 4 четырем разным названиям и присваивает 5 всем остальным. При выполнении сортировки названия, не упомянутые в этом выражении, имеют одинаковый ранг (5).
- В строке «Сортировка» для столбца, содержащего выражение, щелкните «По возрастанию».
- на вкладке Главная в группе Сортировка и фильтр нажмите кнопку Переключить фильтр. Функция IIf возвращает числвое значение, соответствующее значению в поле «Название». Например, если в поле «Название» имеется значение «Диспетчерпродаж», назначенное число будет 2. Если поле «Название» не указано в качестве аргумента функции (например, «Окружная пособка»), ему назначено число 5. Затем запрос отсортет эти числа по возрастанию.
Сортировка большого количества уникальных значений полей
Если нужно ранжировать большое количество значений, лучший способ отсортировать данные — создать таблицу подпапок.
Предположим, что таблица «Сотрудники» ссылается на более чем 50 разных заголовков. Сначала потребуется создать таблицу подготовить существующую таблицу для ее подготовки. Затем вы связываете две таблицы и создаете запрос.
Создание таблицы подпапок и изменение существующей таблицы
- Создайте новую таблицу подытов, в которую будут храниться заголовки. Привязйте таблицу ктипу данных и задайте для полей тип данных, указанный в скобки, как показано в следующей таблице:
| TitleID (AutoNumber) |
Заголовок (текст) |
| 1 | Вице-президент, отдел продаж |
| 2 | Менеджер по продажам |
| 3 | Sales Representative |
| 4 | Inside Sales Разуваев |
- В конструкторе измените имя поля «Название» на «ИД Заголовка».
- В столбце «Тип данных» измените тип данных на число.
Связать две таблицы
- На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных.
- На вкладке «Конструктор» в группе «Отношения» нажмите кнопку «Добавить таблицы» (или «Показать таблицы в Access 2013 «).
- Выберите tblTitles и Employeesи нажмите кнопку «Добавить», чтобы добавить их в окно «Отношения». Закройте диалоговое окно Добавление таблицы.
- Перетащите поле TitleID из таблицы tblTitles на поле TitleID в таблице Employees. Откроется диалоговое окно «Изменение связей».
- Убедитесь, что имена полей, которые отображаются в двух столбцах, являются titleIDи что в поле «Тип связи» отображается тип «один-к-многим». При необходимости вы можете изменить имена полей.
- Установите флажок Обеспечение целостности данных.
- Нажмите кнопку Создать для создания связи. Диалоговое окно «Изменение связей» закроется и сохранит изменения.
- Закроем вкладку «Отношения».
Создание запроса
- На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
- В диалоговом окне «Добавление таблиц ( добавление таблицы в Access 2013 ) перейдите на вкладку «Таблицы», дважды щелкните «Сотрудники», а затем дважды щелкните tblTitles.
- Добавьте поля «ИД сотрудника», «Фамилия» и «Имя» из таблицы «Сотрудники» и поле «Название» из таблицы tblTitles в таблицу конструктора запросов.
- Переключение в представление таблицы.
У вас большие запросы!
Точнее, от вашего браузера их поступает слишком много, и сервер VK забил тревогу.
Эта страница была загружена по HTTP, вместо безопасного HTTPS, а значит телепортации обратно не будет.
Обратитесь в поддержку сервиса.
Вы отключили сохранение Cookies, а они нужны, чтобы решить проблему.
Почему-то страница не получила всех данных, а без них она не работает.
Обратитесь в поддержку сервиса.
Вы вернётесь на предыдущую страницу через 5 секунд.
Вернуться назад
Создание или удаление настраиваемого списка для сортировки и подстановки данных
Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.
Сведения о настраиваемых списках
Чтобы понять, что представляют собой настраиваемые списки, полезно ознакомиться с принципами их работы и хранения на компьютере.
Сравнение встроенных и настраиваемых списков
В Excel есть указанные ниже встроенные списки дней недели и месяцев года.
Встроенные списки
Пн, Вт, Ср, Чт, Пт, Сб, Вс
Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье
янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек
Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь
Примечание: Изменить или удалить встроенный список невозможно.
Вы также можете создать свой настраиваемый список и использовать его для сортировки или заполнения. Например, чтобы отсортировать или заполнить значения по приведенным ниже спискам, нужен настраиваемый список, так как соответствующего естественного порядка значений не существует.
Настраиваемые списки
Высокое, Среднее, Низкое
Большое, Среднее, Малое
Север, Юг, Восток, Запад
Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель
Настраиваемый список может соответствовать диапазону ячеек, или его можно ввести в диалоговом окне Списки.
Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.
Создание настраиваемого списка
Создать настраиваемый список можно двумя способами. Если список короткий, можно ввести его значения прямо во всплывающем окне. Если список длинный, можно импортировать значения из диапазона ячеек.
Введение значений напрямую
Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.
- В Excel 2010 и более поздних версиях выберите пункты Файл >Параметры >Дополнительно >Общие >Изменить списки.
- В Excel 2007 нажмите кнопку Microsoft Office и выберите пункты Параметры Excel >Популярные >Основные параметры работы с Excel >Изменить списки.
- Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента. После ввода каждого элемента нажимайте клавишу ВВОД.
- Завершив создание списка, нажмите кнопку Добавить. На панели Списки появятся введенные вами элементы.
- Нажмите два раза кнопку ОК.
Создание настраиваемого списка на основе диапазона ячеек
Выполните указанные ниже действия.
- В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно «Списки».
- Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.
- На панели Списки появятся выбранные вами элементы.
- Два раза нажмите кнопку ОК.
Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.
Удаление настраиваемого списка
Выполните указанные ниже действия.
- По приведенным выше инструкциям откройте диалоговое окно «Списки».
- Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.
Хранение настраиваемых списков
Настраиваемые списки добавляются в реестр компьютера, чтобы их можно было использовать в других книгах. Если вы используете настраиваемый список при сортировке данных, он также сохраняется вместе с книгой, поэтому его можно использовать на других компьютерах, в том числе на серверах с службы Excel, для которых может быть опубликована ваша книга.
Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.
При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel во всплывающем окне Списки. Для этого выберите во всплывающем окне Сортировка в столбце Порядок пункт Настраиваемый список, чтобы отобразить всплывающее окно Списки, а затем выделите настраиваемый список и нажмите кнопку Добавить.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Уровни сортировки в Excel
В прошлых уроках мы познакомились с базовыми приемами сортировки и научились применять в Excel пользовательскую сортировку к данным. В этом уроке мы пойдем еще дальше и узнаем, как выполнять множественную сортировку, т.е. состоящую из нескольких уровней.
Когда необходима более точная сортировка данных в Excel, Вы можете добавить к ней множество уровней. Такая возможность позволяет сортировать информацию более чем по одному столбцу. Например, Вы можете отсортировать список проданных автомобилей по маркам, при одинаковых марках по моделям, а если совпадают и модели, то по фамилиям покупателей. На самом деле этот список можно продолжать и дальше, все зависит от необходимой точности сортировки и объема исходных данных.
Добавление уровня сортировки в Excel
В следующем примере мы отсортируем таблицу по Номеру аудитории (столбец А) и по Фамилии (столбец С).
- Выделите любую ячейку в таблице, которую необходимо сортировать. В нашем примере мы выберем ячейку A2.
- Откройте вкладку Данные, затем нажмите команду Сортировка.
- Откроется диалоговое окно Сортировка. Из выпадающего списка выберите столбец, по которому Вы желаете выполнить сортировку. В данном примере мы будем сортировать по столбцу Номер аудитории (столбец А).
- Щелкните Добавить уровень, чтобы добавить еще один столбец для сортировки.
- Из выпадающего списка выберите второй столбец, по которому Вы хотите сортировать таблицу, затем нажмите OK. В нашем примере мы будем сортировать по Фамилии (столбец С).
- Таблица будет отсортирована в соответствии с выбранным порядком. В нашем примере номера аудиторий сортируются по возрастающей, а для каждой аудитории студенты расположены по фамилии в алфавитном порядке.
Если необходимо изменить порядок многоуровневой сортировки в Excel, Вы можете без труда указать столбец, который сортируется в первую очередь. Для этого выделите необходимый столбец, а затем нажмите стрелку вверх или вниз, чтобы изменить приоритет.