Excel не учитывать регистр
Обновлено: 07.07.2024
В отличие от Word, в Excel нет кнопки смены регистра. Для перевода текста в нижний регистр – например, чтобы вместо «СЕРГЕЙ ИВАНОВ» или «Сергей Иванов» стало «сергей иванов» – необходимо воспользоваться функцией «СТРОЧН» . Преимущество использования функции заключается в том, что вы можете изменить регистр всего столбца текста одновременно. В примере ниже показано, каким образом это сделать.
Вставьте новый столбец возле столбца, содержащего текст, который необходимо преобразовать.Предположим, что новый столбец – это столбец B, а первоначальный столбец – это столбец A, и что ячейка A1 содержит заголовок столбца.
В ячейке B2 введите =LOWER(A2) и нажмите клавишу «ВВОД». Текст в ячейке B2 должен стать строчным.
Заполните этой формулой столбец B.
Теперь выберите преобразованные значения в столбце B, скопируйте их ивставьте как значенияповерх значений в столбце A.
Удалите столбец B, поскольку больше он вам не понадобится.
Верхний регистр
В отличие от Word, в Excel нет кнопки смены регистра. Для перевода текста в верхний регистр – например, чтобы вместо «сергей иванов» или «Сергей Иванов» стало «СЕРГЕЙ ИВАНОВ» – необходимо воспользоваться функцией «ПРОПИСН». Преимущество использования функции заключается в том, что вы можете изменить регистр всего столбца текста одновременно. В примере ниже показано, каким образом это сделать.
Вставьте новый столбец возле столбца, содержащего текст, который необходимо преобразовать.Предположим, что новый столбец – это столбец B, а первоначальный столбец – это столбец A, и что ячейка A1 содержит заголовок столбца.
В ячейке B2 введите =ПРОПИСН(A2) и нажмите клавишу «ВВОД». Текст в ячейке B2 должен стать прописным.
Заполните этой формулой столбец B.
Теперь выберите преобразованные значения в столбце B, скопируйте их ивставьте как значенияповерх значений в столбце A.
Удалите столбец B, поскольку больше он вам не понадобится.
Каждое слово с заглавной буквы
В отличие от Word, в Excel нет кнопки смены регистра. Для преобразования текста таким образом, чтобы все слова в тексте были с заглавной буквы – например, чтобы вместо «Сергей ИВАНОВ» или «СЕРГЕЙ ИВАНОВ» стало «Сергей Иванов» – необходимо воспользоваться функцией «ПРОПНАЧ» Преимущество использования функции заключается в том, что вы можете изменить регистр всего столбца текста одновременно. В примере ниже показано, каким образом это сделать.
Вставьте новый столбец возле столбца, содержащего текст, который необходимо преобразовать.Предположим, что новый столбец – это столбец B, а первоначальный столбец – это столбец A, и что ячейка A1 содержит заголовок столбца.
В ячейке B2 введите =ПРОПНАЧ(A2) и нажмите клавишу «ВВОД». Текст в ячейке B2 должен изменить регистр.
Заполните этой формулой столбец B.
Теперь выберите преобразованные значения в столбце B, скопируйте их ивставьте как значенияповерх значений в столбце A.
Все подстановочные функции Excel из категории Ссылки и массивы (Lookup and Reference) , такие как ВПР (VLOOKUP), ГПР (HLOOKUP) , ПОИСКПОЗ (MATCH) не учитывают регистр символов (т.е. разницу между прописными и строчными) при поиске данных. Таким образом, при использовании, например, функции ВПР для поиска суммы соответствующей клиенту с кодом Smb3 в данной таблице:
. в итоге получим 38, а не 56 - ибо функция не видит разницы между smb3 и Smb3 и выводит первое встретившееся значение из таблицы.
Что же делать, если нужно находить значение точно, с учетом совпадения регистра символов? Ответ - нужна небольшая формула массива вместо ВПР.
Совет: если вы раньше не особо встречались с формулами массива, то очень рекомендую сходить и почитать вот эту статью, чтобы получить о них общее представление. Если тема знакомая, то читаем дальше.
Решение
Формула, которая нам нужна выглядит следующим образом:
Напомню, что в конце ввода этой формулы нужно нажать не привычный Enter , а сочетание Ctrl + Shift + Enter , чтобы ввести ее не как обычную формулу, а как формулу массива. Как видно из примера, эта формула находит правильное значение 56, то есть, фактически, различает строчные и прописные символы в написании кода, в отличие от классической функции ВПР (VLOOKUP).
Как это работает
Чтобы проще и нагляднее объяснить, как именно работает эта формула массива, лучше всего воспользоваться одним из инструментов пошаговой отладки сложных формул в Microsoft Excel - кнопкой Вычислить формулу (Evaluate formula) . Найти её можно на вкладке Формулы (Formulas) в группе Зависимости формул (Formula auditing) .
В открывшемся окне мы увидим формулу из текущей ячейки и кнопку Вычислить (Calculate) , последовательно нажимая на которую, мы будем пошагово рассматривать внутренний механизм расчета нашей формулы:
Итак, поехали - первый этап. Функция СОВПАД (EXACT) проверяет точное совпадение двух текстовых строк с учетом регистра и выдает на выходе ИСТИНА или ЛОЖЬ в зависимости от результата. В нашем случае мы подсунули этой функции не две ячейки для сравнения, как обычно, а массив ячеек (A4:A9), каждая из ячеек которого по очереди будет сравниваться с ячейкой D4, т.е. с нашим поисковым значением. После первого нажатия на кнопку Вычислить мы как раз увидим, как эти данные подставляются в формулу:
После второго нажатия на кнопку Вычислить мы увидим результат сравнения - массив . Видно, что точное совпадение (ИСТИНА) произошло только на пятом элементе массива:
Следующий этап. Функция СТРОКА (ROW) делает совсем простую, по-сути, вещь - выдает номер строки для текущей ячейки. Здесь же мы даем ей в качестве аргумента не одну ячейку, а массив (B4:B9), поэтому получаем на выходе набор номеров строк для каждой ячейки массива, т.е. :
Затем эти два массива попарно умножаются друг на друга, давая нам на выходе массив , т.к. ЛОЖЬ (FALSE) в понимании Excel равносильно нулю, а ИСТИНА (TRUE) - единице:
Чтобы получить порядковый номер строки с нужными нам данными внутри таблицы - вычитаем из номера строки на листе (8) номер строки начала таблицы, который определяет функция СТРОКА(B3):
Таким образом, мы получаем в итоге число 5 - номер строки в нашей таблице, где находится точное совпадение с искомым значением из D4 с учетом регистра. Осталось извлечь данные из нужной ячейки столбца по вычисленному номеру строки. Это делает функция ИНДЕКС (INDEX) , первый аргумент которой - это массив наших значений (B4:B9), а второй - номер строки в этом массиве (5), из которой мы хотим извлечь нужное нам значение:
Фильтровать текст с учетом регистра с помощью фильтра и формул
Фильтр текста с учетом регистра с помощью суперфильтра Kutools for Excel
Фильтровать текст с учетом регистра с помощью фильтра и формул
Как вы фильтруете только уникальный текст с учетом регистра в Excel, говорит только заглавные буквы «TEXT TOOLS»? Мы последуем примеру, о котором говорилось выше, и покажем вам подробное руководство по фильтрации с учетом регистра.
Шаг 1. В пустом столбце, в столбце C, введите формулу = ТОЧНЫЙ (B2; ВЕРХНИЙ (B2)), и нажмите Enter ключ.
Эта формула определит, является ли текстовая строка в ячейке B2 прописной или нет, и вернет Правда только если текстовая строка написана в верхнем регистре.
Шаг 2: выберите столбец C и щелкните значок Главная >> Заполнять >> вниз чтобы скопировать формулу во весь столбец.
Шаг 3: Выберите столбец B и столбец C вместе.
Шаг 4: нажмите Фильтр под Данные меню.
Теперь он дважды фильтрует записи и, наконец, фильтрует уникальные записи с учетом регистра, см. Следующий снимок экрана:
Ноты:
1. Формула =AND(B2<>"",SUMPRODUCT(--(EXACT(B2,$B$1:$B$14)))>1) может помочь вам отфильтровать только записи с Заголовки.
2. Формула = ТОЧНЫЙ (B2; НИЖНИЙ (B2)) может помочь вам отфильтровать только записи с строчные буквы.
Фильтр текста с учетом регистра с помощью суперфильтра Kutools for Excel
Вы можете подумать, что описанный выше метод недостаточно удобен из-за формул, необходимых для восстановления. Однако, если у вас есть Kutools for Excel, вы можете использовать Суперфильтр чтобы быстро отфильтровать ВЕРХНИЙ или нижний регистр.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools Plus > Суперфильтр. Смотрите скриншот:
3. Установите курсор на первое правило и щелкните подчеркивание. Затем выберите столбец, по которому хотите выполнить фильтрацию, и выберите Текстовый формат во втором списке, затем выберите Равно в третьем списке, в четвертом, выберите формат дела, который вы хотите отфильтровать .. См. снимок экрана:
Предположим, у вас есть ряд данных, как показано на скриншоте ниже, и теперь вы хотите узнать цену предмета 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. Выберите пустую ячейку, в которую вы поместите математические данные, и нажмите Kutools > Формула Помощник > Формула Помощник. Смотрите скриншот:
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 + Alt ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модульи вставьте ниже VBA в новый Модуль окно.
VBA: поиск значения с учетом регистра
3. Сохраните код, вернитесь на активный рабочий лист и выберите пустую ячейку, в которую вы поместите данные соответствия, введите эту формулу. = CaseVLook (F1; A1: C7,3) в него и нажмите Enter ключ для получения результата. Смотрите скриншот:
Наконечник: В формуле F1 - это значение, которое вы хотите найти, A2: C7 - это диапазон таблицы, а 3 - номер столбца, в котором вы хотите найти совпадающие данные, вы можете изменить их в соответствии с вашими потребностями.
Читайте также: