Сортировка с учетом регистра эксель
Функция ВПР и другие подобные ей функции поиска имеют один недостаток – они не могут различать верхний и нижний регистр символов (большие и маленькие буквы). Данный недостаток может оказаться весьма раздражающим, а иногда существенно усложняющим для определенного рода задач. Если поставленная перед вами задача в Excel требует учитывать регистр символов в тексте значений, тогда функцию ВПР (и подобные ей) следует заменить формулой.
Как заставить формулу Excel различать большие и маленькие буквы
Допустим, что содержимое исходного значения для поиска находится в ячейке D1, а таблица, по которой будет выполнен поиск, находится в диапазоне A1:B10.
Чтобы найти необходимые значения:
- В ячейку E1 введите следующую формулу:
- После ввода формулы, для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул появятся фигурные скобки < >.
Пример таблицы и работы формулы показано на рисунке:
Как видно теперь в критериях поиска учитывается верхний регистр символов.
Внимание! Если таблица не содержит исходное значение для поиска, тогда формула возвращает пустую ячейку. Если же таблица содержит несколько дубликатов исходного значения, тогда формула возвращает последний дубликат. Это противоположный результат функции ВПР, которая при наличии дубликатов возвращает первый из них.
Принцип действия формулы поиска с учетом регистра
Для поиска значения формула использует функцию =СОВПАД(), которая сравнивает два текста. При этому учитывает верхний регистр символов и возвращает логическое значение ИСТИНА, если тексты значений совпали. Иначе будет возвращено логическое значение ЛОЖЬ. Так как мы используем эту функцию в массиве формул, сравнение значения D1 происходит с каждым значением всех ячеек таблицы в диапазоне A1:A10.
Задача функции =ЕСЛИ() – возвращать постой текст, в случаи когда логическое выражение ИЛИ(СОВПАД(A1:A10;D1)) возвращает значение ЛОЖЬ. Пустой текст формула вернет если функция СОВПАД не найдет ни одного совпадения при сравнении с исходным текстом. Если вместо этого значение будет найдено, то в фрагменте формулы: СОВПАД(A1:A10;D1)*СТРОКА(A1:B10) будет выполнен повторный поиск и в результате в память будет возвращен номер строки, которая содержит найденное значение. Здесь используется тот факт, что во врем выполнения арифметических действий логические значения ИСТИНА и ЛОЖЬ заменяются на числа 1 и 0 – соответственно. Поэтому в случаи, когда в процессе поиска текст найден, будет получено значение соответствующие номеру строки (иначе будет равно 0). Из всех полученных номеров строк функция =МАКС() выбирает наибольший и передает его в качестве аргумента для функции =ИНДЕКС(). Эта функция уже возвращает окончательный результат отображения значения ячейки из столбца B соответственной номеру выбранной строки.
В Excel вы можете сортировать значения в порядке убывания или возрастания, но хотели ли вы когда-нибудь сортировать данные с учетом регистра? Фактически, с помощью функции сортировки Excel вы можете сортировать данные с учетом регистра, но ее порядок сортировки может быть не тем ответом, который вам нужен. Иногда вам нужно отсортировать все прописные буквы вверху и следовать за строчными, как показано на снимке экрана 1, но иногда вам нужно отсортировать порядок, например, a, A, b, B… как показано на снимке экрана 2. Теперь я расскажу вам, как быстро решить эти задачи в Excel.
Screenshot1 Screenshot2
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Сортировка данных с учетом регистра с помощью столбца справки (все прописные буквы вверху и следуют за строчными)
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы отсортировать данные в верхнем регистре, а затем следовать за нижним регистром, вы можете использовать формулу для создания столбца справки, а затем отсортировать столбец справки.
1. Выберите пустую ячейку рядом с вашими данными, затем введите эту формулу. = (КОД (ЛЕВЫЙ (A1))> 90) + 0 в него, затем нажмите Enter , а затем перетащите маркер автозаполнения вниз, чтобы применить эту формулу. Вы можете видеть, что в ячейках отображаются числа 0 и 1, а число 0 указывает, что первый символ - это заглавная буква в соответствующей ячейке, а число 1 указывает, что первый символ не является прописной буквой в соответствующей ячейке. Смотрите скриншоты:
2. Затем оставьте выделенными эти ячейки формулы и нажмите Данные, А затем выберите Сортировать от наименьшего к наибольшему or Сортировать от большего к меньшему как вам нужно. Чтобы отсортировать верхний регистр вверху, выберите Сортировать от наименьшего к наибольшему. Смотрите скриншот:
3. Затем в всплывающем Предупреждение о сортировке диалог, проверьте Расширить выбор вариант. Смотрите скриншот:
4. Нажмите Сортировать. Теперь все прописные буквы находятся вверху, а строчные - внизу.
Вы можете удалить вспомогательный столбец, если он вам больше не нужен.
Сортировка данных с учетом регистра с помощью Определенной функции (все заглавные буквы вверху и следуют за строчными)
Если вам нравится учиться в VBA, вы можете использовать приведенный ниже код VBA, чтобы получить тот же результат, что и вспомогательный столбец.
| Kutools for Excel, с более чем 120 удобные функции Excel, повысить эффективность работы и сэкономить рабочее время. |
1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic для приложений окно, затем щелкните Вставить > Модуль.
2. Затем в новом окне модуля вставьте в него код VBA ниже.
VBA: сортировка данных с учетом регистра.
3. Затем сохраните код и вернитесь к своему рабочему листу, затем выберите пустую ячейку рядом с вашими данными и введите эту формулу. = StrToHex (I1) в него, затем перетащите дескриптор автозаполнения вниз, чтобы применить эту формулу к ячейкам. Смотрите скриншот:
4. Затем вы увидите, что в ячейках есть несколько чисел, и, не снимая выделения с ячеек формулы, перейдите к Данные Вкладка и выберите Сортировать от наименьшего к наибольшему, см. снимок экрана:
5. Затем в появившемся диалоговом окне отметьте Развернуть выделение.
6. Нажмите Сортировать. Теперь все прописные буквы находятся вверху, а строчные - внизу.
Вы можете удалить ячейки формулы по мере необходимости.
Сортировка данных с учетом регистра с помощью Kutools for Excel (сортировка данных в таком порядке: a, A, b, B, c. C…)
Если вы хотите, чтобы данные были отсортированы, как показано на скриншоте ниже, вышеуказанный метод вам не поможет. Однако вы можете использовать Kutools for ExcelАвтора Расширенная сортировка утилита, которая поможет вам быстро выполнить эту задачу.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите данные, которые нужно отсортировать, и нажмите Предприятие > Расширенная сортировка. Смотрите скриншот:
2. в Расширенная сортировка диалоговом окне выберите столбец, который вы хотите отсортировать, и выберите Ценности под Сортировать по список, затем выберите А до Z в заказ список и не забудьте проверить Деликатный случай флажок, если у ваших данных нет заголовка, снимите флажок У моих данных есть заголовки. Смотрите скриншот:
3. Нажмите Ok. Теперь данные отсортированы, как показано на скриншоте ниже.
Работы С Нами Kutools for ExcelАвтора Расширенная сортировка вы можете сортировать данные по дням недели, частоте, почтовому домену, абсолютному значению, длине текста и так далее.
[p.s.]Лен, твою формулу можно немного сократить:
[p.s.]Лен, твою формулу можно немного сократить:
[p.s.]Лен, твою формулу можно немного сократить:
[/p.s.] Автор - Serge_007Дата добавления - 23.03.2014 в 20:23
Всегда стараюсь, чтобы формула была более понятной, а не более короткой
Всегда стараюсь, чтобы формула была более понятной, а не более короткой Pelena
Всегда стараюсь, чтобы формула была более понятной, а не более короткой Автор - Pelena
Дата добавления - 23.03.2014 в 20:31 [offtop]Лен, а что в сокращенной формуле менее понятно чем в исходной? %)[/offtop] [offtop]Лен, а что в сокращенной формуле менее понятно чем в исходной? %)[/offtop] Serge_007 [offtop]Человека, не искушенного в Excel, вот это 0=0 повергнет в ступор :)[/offtop] [offtop]Человека, не искушенного в Excel, вот это 0=0 повергнет в ступор :)[/offtop] Pelena [offtop]ИМХО, если любому человеку, даже далёкому от Excel, задать вопрос "Истинно ли утверждение что ноль равен нулю?", то любой ответит: "Истинно". Не? ;)[/offtop] [offtop]ИМХО, если любому человеку, даже далёкому от Excel, задать вопрос "Истинно ли утверждение что ноль равен нулю?", то любой ответит: "Истинно". Не? ;)[/offtop] Serge_007
[offtop]Серёж, ты не поверишь, но многим людям, очень даже не глупым, даже традиционную функцию ЕСЛИ() в стандартном исполнении, когда сравниваются две ячейки, бывает сложно понять. А уж когда встречается что-либо из логики… Это ДЕЙСТВИТЕЛЬНО трудно! Я это знаю из собственного опыта.
На мой взгляд, форум тем и хорош, что приводятся разные решения, рассчитанные на разный уровень. Есть возможность выбора и есть возможность повышать свой уровень.
А в данном случае справка говорит, что первый аргумент ПОИСКПОЗ() - это искомое значение. То есть ищем мы 0=0. Это ж надо ещё догадаться, что это эквивалентно ИСТИНА [/offtop]
[offtop]Серёж, ты не поверишь, но многим людям, очень даже не глупым, даже традиционную функцию ЕСЛИ() в стандартном исполнении, когда сравниваются две ячейки, бывает сложно понять. А уж когда встречается что-либо из логики… Это ДЕЙСТВИТЕЛЬНО трудно! Я это знаю из собственного опыта.
На мой взгляд, форум тем и хорош, что приводятся разные решения, рассчитанные на разный уровень. Есть возможность выбора и есть возможность повышать свой уровень.
А в данном случае справка говорит, что первый аргумент ПОИСКПОЗ() - это искомое значение. То есть ищем мы 0=0. Это ж надо ещё догадаться, что это эквивалентно ИСТИНА [/offtop] Pelena
На мой взгляд, форум тем и хорош, что приводятся разные решения, рассчитанные на разный уровень. Есть возможность выбора и есть возможность повышать свой уровень.
А в данном случае справка говорит, что первый аргумент ПОИСКПОЗ() - это искомое значение. То есть ищем мы 0=0. Это ж надо ещё догадаться, что это эквивалентно ИСТИНА [/offtop] Автор - Pelena
Дата добавления - 23.03.2014 в 22:10
Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.
Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.
Порядок сортировки в Excel
Существует два способа открыть меню сортировки:
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
- Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
- Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Сортировка по цвету ячейки и по шрифту
Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.
Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
- Выделяем столбец – правая кнопка мыши – «Сортировка».
- Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
- Соглашаемся «автоматически расширить диапазон».
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».
В открывшемся окне вводим необходимые параметры:
Здесь можно выбрать порядок представления разных по цвету ячеек.
По такому же принципу сортируются данные по шрифту.
Сортировка в Excel по нескольким столбцам
Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.
Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.
Сортировка строк в Excel
По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:
Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.
Случайная сортировка в Excel
Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.
Например, нужно расположить в случайном порядке набор неких чисел.
Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.
Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.
Динамическая сортировка таблицы в MS Excel
Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
- Есть набор простых чисел, которые нужно отсортировать по возрастанию.
- Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
- Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.
Для динамической сортировки текстовых значений понадобятся формулы массива.
- Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
- Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
- В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
- Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон "фрукты" еще одно значение "помело" и проверим:
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.
Читайте также: