Сортировка по полу в эксель

Обновлено: 10.08.2022

Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых - кнопки сортировки на вкладке или в меню Данные (Data - Sort) . Бывают, однако, ситуации, когда сортировку списка нужно делать автоматически, т.е. формулами. Такое может потребоваться, например, при формировании данных для выпадающего списка, при вычислении данных для диаграмм и т.д. Как же "на лету" сортировать список формулой?

Способ 1. Числовые данные

Если список содержит только числовую информацию, то его сортировку можно легко сделать с помощью функций НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW) :

Функция НАИМЕНЬШИЙ (SMALL) выдергивает из массива (столбец А) n-й по счету наименьший элемент. Т.е. НАИМЕНЬШИЙ(A:A;1) - это самое маленькое число из столбца, НАИМЕНЬШИЙ(А:А;2) - второе по счету наименьшее и т.д.

Функция СТРОКА (ROW) выдает порядковый номер строки для указанной ячейки, т.е. СТРОКА(А1)=1, СТРОКА(A2)=2 и т.д. В данном случае она используется просто как генератор последовательности чисел n=1,2,3… для нашего отсортированного списка. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.

Способ 2. Текстовый список и обычные формулы

Если в списке не числа, а текст, то функция НАИМЕНЬШИЙ (SMALL) уже не сработает, поэтому придется пойти другим, чуть более длинным, путем.

Сначала добавим служебный столбец с формулой, где будет вычисляться порядковый номер каждого имени в будущем отсортированном списке с помощью функции СЧЁТЕСЛИ (COUNTIF) :

В английской версии это будет:

Ну, и наконец, осталось просто вытащить из списка имена по их номерам. Для этого можно использовать такую формулу:

Функция ПОИСКПОЗ (MATCH) ищет в столбце В нужный порядковый номер (1, 2, 3 и т.д.) и выдает, по сути, номер строки, где находится это число. Функция ИНДЕКС (INDEX) вытаскивает из столбца А имя по этому номеру строки.

Способ 3. Формула массива

Этот способ представляет собой, по сути, тот же алгоритм расстановки, что и в Cпособе-2, но реализованный формулой массива. Для упрощения формулы диапазону ячеек С1:С10 было дано имя List (выделить ячейки, нажать Ctrl+F3 и кнопку Создать):

В ячейку Е1 копируем нашу формулу:

Или в англоязычной версии:

и нажимаем Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Потом полученную формулу можно скопировать вниз на всю длину списка.

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

Во-первых, диапазон List нужно будет задать динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все имеющиеся значения независимо от их количества. Нажмите Alt+F3 или откройте вкладку Формулы - Диспетчер имен (Formulas - Name Manager) , создайте новое имя и в поле Ссылка (Reference) впишите вот такую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):

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

Типы сортируемых данных и порядок сортировки

Сортировка числовых значений в Excel

Сортировка числовых значений по возрастанию - это такая расстановка значений, при которой значения располагаются от наименьшего к наибольшему (от минимального к максимальному).

Соответственно, сортировка числовых значений по убыванию - это расположение значений от наибольшего к наименьшему (от максимального к минимальному).

Сортировка текстовых значений в Excel

"Сортировка от А до Я" - сортировка данных по возрастанию;

"Сортировка от Я до А" - сортировка данных по убыванию.

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

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

Можно отсортировать текстовые данные с учетом регистра. Для этого необходимо в параметрах сортировки установить флажок в поле "Учитывать регистр".

Обычно буквы верхнего регистра имеют меньшие номера, чем буквы нижнего регистра.

Сортировка значений даты и времени

"Сортировка от старых к новым" - это сортировка значений даты и времени от самого раннего значения к самому позднему.

"Сортировка от новых к старым" - это сортировка значений даты и времени от самого позднего значения к самому раннему.

Сортировка форматов

В Microsoft Excel 2007 и выше предусмотрена сортировка по форматированию. Этот способ сортировки используется в том случае, если диапазон ячеек отформатирован с приминением цвета заливки ячеек, цвета шрифта или набора значков. Цвета заливок и шрифтов в Excel имеют свои коды, именно эти коды и используются при сортировке форматов.

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

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

Параметры сортировки

Сортировка по столбцу

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

Сортировка по строке

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

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

Итак, если производится сортировка по столбцу, то строки меняются местами, если данные сортируются по строке, то местами меняются столбцы.

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

Кроме всего прочего при сортировке можно учитывать, либо не учитывать регистр.

Надстройка для сортировки данных в Excel

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

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

Надстройка позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. выбирать диапазон данных для сортировки;

3. сортировать числовые и текстовые значения, значения даты и времени;

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

Этот пример я решил разобрать, чтобы разобрать комбинацию из формул ЕСЛИ и ПРАВСИМВ.
Разберём тот случай, когда после применения не самой замудрённой формулы в Microsoft Excel, у обычного зрителя возникает ощущение «ВАУ».

Перед нами таблица с ФИО, и наша задача — определить с помощью формулы пол по отчеству.
По традиции можете посмотреть видео, пример для скачивания доступен выше.

Для начала немного теории…
Все мужские отчества заканчиваются на букву «-ч», женские — на «-на».
Нам остаётся просто воспользоваться логической функцией: если отчество заканчивается на букву «-ч», значит пол мужской.
В противном случае, пол — женский.


В ячейку C2 запишем формулу:

«=ЕСЛИ(ПРАВСИМВ(B2;1)=»ч»;»м»;»ж»)»

  • ЕСЛИ() — логическая функция, которая выводит одно значение, когда условие выполняется, и другое значение, если нет;
  • ПРАВСИМВ(B2;1)=»ч» — функция, которая проверяет равенство одного значения с конца строки текста ячейки B2 и буквы «ч»;
  • «м» — значение, которое выводит Excel в случае, когда условие выполняется;
  • «ж» — значение, которое выводит Excel в случае, когда условие не выполняется.


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


Согласитесь, красивое решение.
Просто, одной формулой, которая состоит из функций ЕСЛИ и ПРАВСИМВ, мы так красиво решили задачу.

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

Вам так же доступно видео, возможно кому-то будет так удобнее.

Больше полезных видеоуроков на нашем YouTube канале.
Если у Вас возникли вопросы или просто хотите поделиться мнением, напишите в комментариях к записи.

Классическая задача, с которой периодически сталкивается почти любой пользователь Microsoft Excel: нужно определить пол для каждого человека в списке. Давайте рассмотрим несколько решений для такой задачи.

Вариант 1. Полные ФИО, только "наши"

Начнем, для разогрева, с самого простого случая, когда у нас есть правильно записанные полные ФИО для всех людей в списке. Как легко сообразить, для большинства жителей бывшего СНГ тут сработает простой принцип: "если отчество оканчивается на Ч, то это мужчина, в противном случае - женщина". Реализовать эту логику можно простой формулой:

Определение пола по ФИО

Функция ПРАВСИМВ (RIGHT) извлекает из ФИО один символ справа (последнюю букву отчества), а функция ЕСЛИ (IF) проверяет извлеченный символ и выводит "ж" или "м", в зависимости от результата проверки.

Вариант 2. Полные ФИО, есть "экспаты"

Если в списке есть имена не только русского типа (назовем их "экспаты"), то к приведенной ранее формуле можно добавить еще одну проверку, чтобы отлавливать их тоже:

Определение пола по ФИО если есть экспаты

То есть "м" будет выводиться только если отчество заканчивается на Ч, "ж" - если заканчивается на А. Во всех же остальных случаях ("экспаты") наша формула выдаст три вопросительных знака.

Вариант 3. Неполные или переставленные ФИО, только "наши"

Если в нашем списке отчества есть не у всех (или их нет совсем) или ФИО идет в другом порядке (ИФО, ИФ, ФИ), то придется использовать принципиально другой подход. Создадим таблицу-справочника со всеми женскими именами (я использовал для этого википедию):

Созданную таблицу я преобразовал в "умную" (выделить ее и нажать Ctrl+T), чтобы потом не думать про ее размеры и дополнять справочник новыми именами в любое время. На появившейся вкладке Конструктор (Design) умной таблице лучше дать отдельное имя (например жен), чтобы потом использовать его в формулах:

Имя для умной таблицы

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

Формула массива для определения пола по ФИО

Давайте разберем ее по шагам на примере первого человека:

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

Функция СУММ (SUM) суммирует все числа в получившемся массиве и если получается число больше нуля, то функция ЕСЛИ (IF) выводит "ж", в противном случае "м".

Не забудьте после ввода формулы нажать сочетание клавиш Ctrl+Shift+Enter, т.к. ее нужно ввести как формулу массива.

Вариант 4. Неполные ФИО, есть "экспаты"

Если в списке могут встречаться экспаты или нестандартные имена, которых нет в справочнике, то предыдущая формула будет автоматом относить человека к мужчинам, что не есть хорошо. Поэтому для полной универсальности можно добавить справочник мужских имен и еще одну проверку, как мы уже делали в варианте-2:

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

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

Можете еще есть какие-нибудь варианты ?

Добавлено через 21 минуту
И еще вопрос вдогонку, как в случае, если пол не определен , сделать, чтобы надпись НЕ ОПРЕДЕЛЕН выделялась например красным цветом ?

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

Поиск по имени или фамилии в БД [есть ответ]
Несколько часов потратил на такой момент: Имеется база Access отображаемая через DBGrid. При.


Группировка массива по возрасту, имени или фамилии
Нужно написать функцию, которая будет группировать массив по возрасту, имени или фамилии. Функция.

Определение имени компьютера или пользователя
Как в MS Access 2000 определить имя компьтера или пользователя?

Вывод фамилии, имени и отчества с помощью функции с явно заданными или частично пропущенными параметрами
1 Написать функцию с параметрами по умолчанию в соответствии с вариантом , продемонстрировать.

Что-то типа =ЕЧИСЛО(ПОИСКПОЗ().

Условное форматирование выделит цветом.

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

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

самое простое это сделать поле обязательным к заполнению и получать пол на стадии заполнения строки

Структуры. Вывести фамилии студентов определенного пола
Как сделать, чтобы выходила фамилия студента определенного пола. У меня выходит только слово"FIO".


Изменение введённой фамилии при выборе пола
Всем привет, встала задача организовать изменение введённой фамилии при выборе пола в моей БД: .

Функция определения пола по имени
Здравствуйте. Что-то никак не поддается мне решение данной задачи: «Напишите функцию, которая.

Как отсортировать мужчин и женщин в Excel?

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

Как правило, если отчество заканчивается буквой "ч", это означает, что владелец этого отчества — мужчина, например, Иванович, Петрович, Альбертович. Конечно, возможны и исключения, например, отчество Талгатулы не заканчивается буквой "ч", хотя и является мужским. Для быстрого определения половой принадлежности воспользуемся одной из двух функций.

Определить пол по отчеству

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

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

Представленные функции возвращают "М" либо "Ж" в зависимости от последней буквы в отчестве.

Определить пол по ФИО

Если исходным значением для определения пола является полное ФИО, то предварительно ФИО нужно разделить на части, вычленить из него отчество и далее действовать по аналогии с уже рассмотренным выше примером.

Когда во всем списке Excel пол определен, отсортировать мужчин от женщин при помощи стандартной сортировки Excel не составит никакого труда. Надстройку, которая добавляет рассмотренные функции в категорию "Определенные пользователем" можно скачать бесплатно.

Определение пола по имени в Excel

Функция =ПОЛ(ФИО) возвращает значение М если ФИО содержит мужское имя, или значение Ж, если имя женское. Функция имеет только 1 аргумент:

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

Пример 1

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

Определение пола по имени в Excel

Пример 2

Использование функции ПОЛ для автоматизации договоров. Допустим необходимо автоматизировать обращение к клиенту и в зависимости от пола писать Уважаемый или Уважаемая. Тут нам и поможет наша функция.

Обращение в зависимости от пола

Помимо функции ПОЛ тут для удобства использовались функции ИМЯ и ОТЧЕСТВО. Они также входят в состав надстройки.

Принцип работы функции

Для определения пола, функция проверяет имя и проверяет это имя в нашей базе данных на сервере. Если имя на сервере имеется информация, что имя мужское, то возвращается значение М, если женское — Ж.

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

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

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

Читайте также: