Excel не учитывать регистр
Функция ВПР и другие подобные ей функции поиска имеют один недостаток – они не могут различать верхний и нижний регистр символов (большие и маленькие буквы). Данный недостаток может оказаться весьма раздражающим, а иногда существенно усложняющим для определенного рода задач. Если поставленная перед вами задача в 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 учитывал при поиске (Вертикальным ПРосмотром) регистр символов, вам нужно построить для этого правильную формулу.
Как заставить функцию ВПР различать большие и маленькие буквы?
Рассмотрим пример. Сначала ознакомимся с исходными данными:
Наш расчет начнем с функции СОВПАД, которая сравнивает две текстовые строки (с учетом регистра символов) и в зависимости от результата сравнения возвращает логическое значение ИСТИНА или ЛОЖЬ. В нашем примере нам нужно как один из аргументов функции СОВПАД указать текст, который мы ищем, а вторым аргументом - ссылку на столбец, в котором будем искать. Формула следующая:
При таких данных, функция СОВПАД возвращает массив логических значений ИСТИНА и ЛОЖЬ. ИСТИНА будет в тех ячейках, где текстовые строки совпали, в остальных ячейках - ЛОЖЬ. Схематически данный массив выглядит так:
Поэтому функция СОВПАД будет возвращать значение ЛОЖЬ даже если ее выполнить в массиве. Временно добавим функцию ИЛИ, которая возвращает значение ИСТИНА если хотя бы одно значение в массиве является истинным. Временная формула будет выглядеть так:
Обратите внимание! Данная формула должна выполняться в массиве. Поэтому для ее ввода нажимаем не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter.
Теперь, на основе выше указанной таблицы, нам нужно создать элемент, который мы будем искать. Относительно простым решением будет перемножение его в массиве с номерами строк данных. Все что вам нужно сделать, это использовать функцию СТРОКА с заданным диапазоном столбца, в котором будет осуществляться поиск. Его результат должен умножить на массив, возвращаемый функцией СОВПАД.
В результате будет таблица с нулями или номерами строк, там, где Excel нашел идентичные текстовые строки.
Нас интересует максимальное значение в этой таблице, поэтому необходимо наши промежуточные вычисления вставляем в функцию МАКС:
Нам удалось найти значение, которое мы будем искать, используя функцию ВПР.
Функция ВЫБОР и ВПР для вертикального поиска значений таблицы
Теперь Вам нужно построить массив, в которой функция будет осуществлять поиск. Для этого Вам понадобится функция ВЫБОР, которая по умолчанию возвращает выбранный аргумент на основании очередности, то есть если Вы введете 1 в качестве первого аргумента, функция ВЫБОР вернет следующий первый аргумент, если Вы введете 2, она вернет следующий второй аргумент и т.д.
Только нам необходимо чтобы функция ВЫБОР возвращала два аргумента одновременно. Чтобы получить такой эффект, Вы должны ввести первый аргумент в виде массива данных: <1\2>. Для такого аргумента функция ВЫБОР возвращает как первый, так и второй выбранный аргумент.
К счастью, функция ВЫБОР не имеет проблем с возвратом диапазонов данных, поэтому нашим первым аргументом будут номера строк в столбце, в котором мы ищем (СТРОКА($A$2:$A$11)), а вторым аргументом будет столбец, из которого мы хотим извлечь информацию ($B$2:$B$11). Поскольку функция ВПР возвращается значения из одного столбца, даже если Ваши исходные данные содержат больше столбцов, достаточно указать в аргументах функции ВЫБОР только два столбца:
- Первый столбец с номерами строк.
- Второй столбец, из которого Вы хотите получить значение.
Самое сложное позади. Все, что вам нужно сделать, это еще ввести два последних аргумента функции ВПР:
- Значение 2, потому что мы ищем значение из второго столбца.
- Значение 0, потому что мы хотим выполнить поиск по принципу точного соответствия.
Таким образом, вся формула будет иметь следующий вид:
Помните, что это формула массива, потому что мы функции Excel в качестве аргумента даем массив данных там, где она ожидает одно значение. Поэтому Вы должны "одобрить" фигурными скобками эту формулу нажатием комбинации клавиш Ctrl + Shift + Enter.
Функция ПРОПИСН в Excel предназначена для смены регистра символов в текстовой строке (от прописных к строчным) и возвращает текстовую строку, в которой все символы являются строчными.
Данная функция удобна в использовании, когда требуется быстро поменять регистр символов в имеющейся строке текста.
Как сделать верхний регистр для символов текста в Excel
Пример 1. В таблице содержатся числовые данные в римской форме записи. При этом для использован нижний регистр символов. Преобразовать символы в верхний регистр.
Вид таблицы данных:
Используем формулу, чтобы заполнить сразу все ячейки:
- A2:A7 – диапазон ячеек с римскими числами в нижнем регистре.
Если требуется получить универсальную формулу, которая определяет, содержат ли ячейки текстовые представления римских чисел, можно добавить проверочное условие:
С помощью функции ЕСЛИ проверим возможность преобразования римского числа к арабскому. Если ЕОШ вернет значение ИСТИНА (есть ошибка), будет выведено соответствующее пояснение, иначе функция ПРОПИСН выполнит преобразование регистра символов.
Добавим несколько значений в таблицу и выведем результаты в соседнем столбце:
Как указать день недели большой буквой в Excel?
Пример 2. В таблице Excel содержится столбец с датами. В соседнем столбце вывести первый символ названия дня недели, соответствующий каждой дате. Символ должен быть в верхнем регистре.
Вид таблицы данных:
Выделим ячейки B2:B7 и запишем следующую формулу массива:
Функция ТЕКСТ принимает данные формата Дата и преобразует их в текстовое представление наименования дня недели. Код "DDD" значит формат сокращения названий дней недели до двух букв (например, «понедельник» - пн и т.д.). Все символы преобразуется в верхний регистр с помощью функции ПРОПИСН.
Для преобразования дат в трехсимвольный стандарт сокращения названий дней недели можно воспользоваться другой формулой:
Как выделить часть текста большими буквами формулой в Excel
Пример 3. В таблице Excel содержится столбец, в ячейках которого содержатся текстовые строки. В соседнем столбце записать те же строки с выделенным первым словом символами верхнего регистра.
Вид таблицы данных:
Чтобы не усложнять выражение не будем использовать формулу массива. Для заполнения ячейки B1 искомым значением введем следующую формулу:
Символ амперсант «&» соединяет две подстроки в одну строку. Первый аргумент данной функции – результат выполнения функции ПРОПИСН, которая получает в качестве аргумента значение, определенное функцией ЛЕВСИМВ - часть исходной строки, ограниченной числом символов, определенных с помощью функции НАЙТИ (до первого пробела – “ “). Второй аргумент указан в виде функции ПРАВСИМВ, возвращающей часть исходной строки, начинающейся с символа, номер которого определен как разница между общим количеством символов в строке (функция ДЛСТР) и номером первого пробела (функция НАЙТИ).
Для получения оставшихся значений растянем записанную формулу вниз на требуемое число ячеек:
Как отсортировать данные в Excel с учетом регистра?
В Excel вы можете сортировать значения в порядке убывания или возрастания, но хотели ли вы когда-нибудь сортировать данные с учетом регистра? Фактически, с помощью функции сортировки Excel вы можете сортировать данные с учетом регистра, но ее порядок сортировки может быть не тем ответом, который вам нужен. Иногда вам нужно отсортировать все прописные буквы вверху и следовать за строчными, как показано на снимке экрана 1, но иногда вам нужно отсортировать порядок, например, a, A, b, B… как показано на снимке экрана 2. Теперь я расскажу вам, как быстро решить эти задачи в Excel.
Сортировка данных с учетом регистра с помощью столбца справки (все прописные буквы вверху и следуют за строчными)
Сортировка данных с учетом регистра с помощью Определенной функции (все заглавные буквы вверху и следуют за строчными)
Сортировка данных с учетом регистра с помощью Kutools for Excel (сортировка данных в таком порядке: a, A, b, B, c. C…)
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 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Автора Расширенная сортировка вы можете сортировать данные по дням недели, частоте, почтовому домену, абсолютному значению, длине текста и так далее.
Как выполнить ВПР с учетом регистра или без учета регистра в Excel?
Предположим, у вас есть ряд данных, как показано на скриншоте ниже, и теперь вы хотите узнать цену предмета va-6. С помощью общей формулы поиска вы получите нечувствительность к цене первых совпавших данных. В этом руководстве мы расскажем о методах ВПР, чувствительных к регистру или нечувствительных к регистру в Excel.
Поиск без учета регистра с помощью Kutools for Excel
Поиск без учета регистра с формулой ВПР
Для ВПР значения, основанного на другом значении без учета регистра, вам понадобится всего лишь одна формула ВПР.
Выберите пустую ячейку, в которую будет помещено найденное значение, и введите эту формулу = ВПР (F1; $ A $ 2: $ C $ 7,3; FALSE) в него и нажмите Enter ключ для получения первых совпавших данных.
Наконечник: В формуле F1 - это значение, которое вы хотите найти, A2: C7 - это диапазон таблицы, а 3 - номер столбца, в котором вы хотите найти совпадающие данные, вы можете изменить их в соответствии с вашими потребностями.
Поиск без учета регистра с помощью Kutools for Excel
Если у вас есть Kutools для Excel яn установлен, вы можете использовать Найдите значение в списке функция для быстрого и легкого поиска значения без учета регистра.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите пустую ячейку, в которую вы поместите математические данные, и нажмите Кутулс > Формула Помощник > Формула Помощник. Смотрите скриншот:
2. в Формула Помощник диалоговое окно, выберите Ищите ценность в списке из Выберите формулу раздел, затем перейдите в правый раздел, чтобы указать диапазон таблицы, значение поиска и столбец, к которому вы хотите вернуться. Смотрите скриншот:
3. Нажмите Ok, и теперь данные о совпадении были возвращены.
Поиск с учетом регистра с помощью SUMPRODUCT
Если вы хотите найти значение с учетом регистра, вы можете использовать функцию СУММПРОИЗВ.
Выберите пустую ячейку, в которую вы поместите данные соответствия, и введите эту формулу =SUMPRODUCT((EXACT($A$2:$A$7,$F$1)*($C$2:$C$7))) и нажмите Enter ключ для получения данных соответствия. Смотрите скриншот:
Наконечник: В формуле $ A $ 2: $ A $ 7 - это столбец, в котором вы найдете значение поиска, F1 - это значение, которое вы хотите найти, $ C $ 2: $ C $ 7 - это столбец, в котором вы хотите найти совпадающие данные. Вы можете изменить их по своему усмотрению.
Поиск с учетом регистра с помощью функции, определяемой пользователем
На самом деле, если вы хотите использовать функцию, определяемую пользователем, вот она, которая справится с этой задачей.
1. Нажмите F11 + Альт ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте ниже VBA в новый Модули окно.
VBA: поиск значения с учетом регистра
3. Сохраните код, вернитесь на активный рабочий лист и выберите пустую ячейку, в которую вы поместите данные соответствия, введите эту формулу. = CaseVLook (F1; A1: C7,3) в него и нажмите Enter ключ для получения результата. Смотрите скриншот:
Наконечник: В формуле F1 - это значение, которое вы хотите найти, A2: C7 - это диапазон таблицы, а 3 - номер столбца, в котором вы хотите найти совпадающие данные, вы можете изменить их в соответствии с вашими потребностями.
Читайте также: