Как в excel в ячейке: ЯЧЕЙКА (функция ЯЧЕЙКА) — Служба поддержки Майкрософт

Содержание

Ввод данных вручную в ячейки листа

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

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

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


  1. Выберите ячейку на листе.

  2. Введите числа или текст, которые вы хотите ввести, а затем нажмите ввод или TAB.

    Чтобы ввести данные в новую строку в ячейке, введите разрыв строки, нажав ALT+ВВОД.


  1. org/ListItem»>

    На вкладке Файл выберите пункт Параметры.

    Только Excel 2007: нажмите кнопку Microsoft Office и выберите Excel параметры.

  2. В меню Расширенные параметры нужно выбрать пункт Параметры правки, и затем установить флажок Автоматическая вставка десятичной запятой.

  3. В поле Число знаков после запятой введите положительное число для количества цифр справа от запятой или отрицательное число для количества цифр слева от запятой.

    Например, если ввести 3 в поле Число знаков после запятой, а затем — 2834 в ячейке, то значение будет равно 2,834. Если ввести –3 в поле Число знаков после запятой, а затем — 283, то значение будет равно 283000.

  4. Выберите ячейку на листе и введите нужное число.

    Изменение параметра Фиксированный десятичный формат не влияет на уже введенные данные.

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


  1. Выберите ячейку на листе.

  2. Введите дату или время в следующем виде.

    • org/ListItem»>

      При вводе даты используйте в качестве разделителя точку или дефис, например 09.05.2002 или 5-сен-2002.

    • Для ввода времени суток в 12-часовом формате введите букву a или p, отделенную пробелом от значения времени, например 9:00 p. В противном случае значение будет введено как время в первой половине суток.

      Чтобы ввести текущую дату и время, нажмите клавиши CTRL+SHIFT+; (точка с запятой).

  • Чтобы ввести дату или время, которые останутся текущими при повторном открытии листа, можно воспользоваться функциями СЕГОДНЯ и ТДАТА.

  • org/ListItem»>

    При вводе даты или времени в ячейку они отображаются либо в формате по умолчанию компьютера, либо в формате, примененном к ячейке перед вводом даты или времени. Формат даты или времени по умолчанию зависит от параметров даты и времени в диалоговом окне Язык и региональные стандарты (панель управления, Часы, язык и регион). Если изменить эти параметры на компьютере, то значения даты или времени, которые не были отформатированы с помощью команды Формат ячеек, будут отображаться в соответствии с этими параметрами.

  • Чтобы применить формат даты или времени по умолчанию, щелкните ячейку с датой или временем и нажмите клавиши CTRL+SHIFT+# или CTRL+SHIFT+@.


  1. org/ListItem»>

    Выделите ячейки, в которые необходимо ввести одинаковые данные. Они не обязательно должны быть смежными.

  2. Введите данные в активной ячейке и нажмите клавиши CTRL+ВВОД.

    Можно также ввести одинаковые данные в несколько ячеек с помощью маркер заполнения , чтобы автоматически заполнить данными ячейки листа.

    Дополнительные сведения см. в статье Автоматическое заполнение ячеек листа данными.


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

  1. org/ListItem»>

    Щелкните ярлычок первого листа, содержимое которого требуется изменить. Затем, удерживая нажатой клавишу CTRL, щелкните ярлычки других листов, с которыми следует синхронизировать данные.

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

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

  3. В активной ячейке введите новые данные или измените существующие, а затем нажмите клавишу ВВОД или TAB, чтобы переместить выделение в следующую ячейку.

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

  4. Повторите предыдущее действие до завершения ввода или редактирования данных.

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

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

    1. На вкладке Вид в группе Окно щелкните Новое окно.

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

    3. Повторите шаги 1 и 2 для каждого листа, который требуется просмотреть.

    4. На вкладке Вид в группе Окно щелкните пункт Упорядочить все, а затем выберите нужный вариант.

    5. Для просмотра листов только в активной книге установите в диалоговом окне Расположение окон флажок Только окна текущей книги.


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


Изменение направления клавиши ВВОД

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

Нажатие клавиши ВВОД перемещает курсор на одну ячейку вниз, а нажатие клавиши TAB — на одну ячейку вправо. Направление перемещения для клавиши TAB изменить нельзя, но для клавиши ВВОД — можно. Изменение этого параметра влияет на весь лист, все другие открытые листы и книги, а также новые книги.

  1. На вкладке Файл нажмите кнопку Параметры.

    Только Excel 2007: нажмите кнопку Microsoft Office и выберите Excel параметры.

  2. В группе Дополнительно в разделе Параметры правки установите флажок Переход к другой ячейке после нажатия клавиши ВВОД и выберите нужное направление в списке Направление.


Изменение ширины столбца

Иногда в ячейке может отображаться строка вида #####. Это может происходить, если ячейка содержит число или дату, а ширина столбца не позволяет отобразить все необходимые символы. Допустим, например, что столбец с форматом даты «дд.мм.гггг» содержит дату 31.12.2015. Однако ширины столбца хватает только для показа шести символов. В ячейке будет отображена строка #####. Чтобы увидеть все содержимое ячейки с текущим форматом, необходимо увеличить ширину столбца.

  1. Щелкните ячейку, для которой требуется изменить ширину столбца.

  2. На вкладке Главная в группе Ячейки нажмите кнопку Формат.

  3. Выделите пункт Размер ячейки и выполните одно из следующих действий.

    • Чтобы подогнать размер ячейки под текст, выберите команду Автоподбор ширины столбца.

    • Чтобы увеличить ширину столбца, выберите команду Ширина и в поле Ширина столбца введите нужное значение.

Примечание: Также ширину столбца можно увеличить, изменив формат столбца или отдельной ячейки. Например, можно изменить формат даты, чтобы отображался только день и месяц (формат «дд.мм»), например 31.12, или представить число в экспоненциальном формате, например 4E+08.


Перенос текста в ячейке

Путем переноса текста в ячейке можно отобразить несколько строк текста. Перенос текста в ячейке не влияет на другие ячейки.

  1. Щелкните ячейку, в которой требуется выполнить перенос текста.

  2. На вкладке Главная в группе Выравнивание выберите пункт Переносить текст.

Примечание: Если текст представляет собой одно длинное слово, знаки не будут перенесены; чтобы был виден весь текст, можно расширить столбец или уменьшить размер шрифта. Если после включения переноса текста виден не весь текст, возможно, потребуется изменить высоту строки. На вкладке Главная в группе Ячейки нажмите кнопку Формат и выберите в разделе Размер ячейки команду Автоподбор высоты строки.

Дополнительные сведения о переносе текста см. в разделе Перенос текста в ячейке.


Изменение формата числа

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

После ввода чисел в ячейку можно изменить формат их отображения.

  1. Выберите ячейку, в которой содержатся нужные числа.

  2. org/ListItem»>

    На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовой формат и выберите нужный формат.

    Чтобы выбрать формат из списка доступных, нажмите кнопку Дополнительные числовые форматы и выберите нужный формат из списка Числовые форматы.


Форматирование чисел в виде текста

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

  1. Выберите пустую ячейку.

  2. На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовой формат и выберите пункт Текстовый.

  3. Введите нужные числа в отформатированной ячейке.

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

Дополнительные сведения


Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.


Исправление ошибки ##### — Служба поддержки Майкрософт


Excel

Формулы и функции

Ошибки



Ошибки

Исправление ошибки #####

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. ..Меньше

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

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

Также попробуйте сделать следующее.

  • Чтобы уменьшить содержимое ячейки, нажмите кнопку Главная > рядом с кнопкой Выравнивание ,а затем в диалоговом окне Формат ячеек выберите нужный размер.

  • Если число содержит слишком много знаков после запятой, щелкните Главная > Уменьшить разрядность.

  • Если даты слишком длинные, щелкните Главная, затем — стрелку рядом с элементом Формат чисел и выберите Краткий формат даты.


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

  • При использовании системы дат 1900 убедитесь, что дата и время заданы положительными значениями.


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

  • Для ячеек с отрицательными результатами в формате даты или времени выберите другой числовой формат.

Как использовать регулярные выражения (Regex) в Microsoft Excel как в ячейке, так и в циклах

Регулярные выражения используются для сопоставления с образцом.

Для использования в Excel выполните следующие действия:

Шаг 1 : Добавьте ссылку VBA на «Регулярные выражения Microsoft VBScript 5.5»

  • Выберите вкладку «Разработчик» (у меня нет этой вкладки, что мне делать?)
  • Выберите значок «Visual Basic» в разделе ленты «Код»
  • В окне «Microsoft Visual Basic для приложений» выберите «Инструменты» в верхнем меню.
  • Выберите «Ссылки»
  • Установите флажок рядом с пунктом «Регулярные выражения Microsoft VBScript 5.5», чтобы включить его в книгу.
  • Нажмите «ОК»

Шаг 2 : Определите шаблон

Основные определения:

- Диапазон.

  • a-z соответствует строчным буквам от a до z
  • Напр. 0-5 соответствует любому числу от 0 до 5

[] Совпадение ровно с одним из объектов внутри этих скобок.

  • [a] соответствует букве a
  • Напр. [abc] соответствует одной букве, которая может быть a, b или c
  • Напр. [a-z] соответствует любой строчной букве алфавита.

() Группирует разные совпадения для целей возврата. См. примеры ниже.

{} Множитель для повторных копий шаблона, определенного перед ним.

  • [a]{2} соответствует двум последовательным строчным буквам a: аа
  • Напр. [a]{1,3} соответствует как минимум одной и максимум трем строчным буквам a , aa , aaa

+ Совпадение хотя бы с одним или несколькими шаблонами, определенными перед ним.

  • a+ будет соответствовать последовательным a a , aa , aaa и так далее

? Совпадение с нулем или с одним из шаблонов, определенных перед ним.

  • Напр. Образец может присутствовать или отсутствовать, но может быть сопоставлен только один раз.
  • Напр. [а-я]? соответствует пустой строке или любой строчной букве.

* Совпадение с нулевым или более шаблоном, определенным перед ним.

  • Подстановочный знак для шаблона, который может присутствовать или отсутствовать.
  • Напр. [a-z]* соответствует пустой строке или строке строчных букв.

. Соответствует любому символу, кроме символа новой строки 9\r\t\n\f] Любой непробельный символ
\n [\n] Новая строка

Пример 1 : Выполнить как макрос

Макрос в следующем примере проверяет значение в ячейке A1 , чтобы определить, являются ли первые 1 или 2 символа цифрами. Если это так, они удаляются, а остальная часть строки отображается. Если нет, то появится окно с сообщением о том, что совпадений не найдено. Ячейка A1 значения 12abc вернут abc , значение 1abc 9[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput как строка
Dim Myrange As Range

Установите Myrange = ActiveSheet.Range("A1")

Если стрПаттерн <> "" Тогда
strInput = Myrange.Value

С регулярным выражением
.Глобал = Истина
.Многострочный = Истина
.Игнорекасе = Ложь
.Шаблон = стрПаттерн
Конец с

Если regEx.Test(strInput) Тогда
MsgBox (regEx.Replace (strInput, strReplace))
Еще
MsgBox ("Не соответствует")
Конец, если
Конец, если
Конец сабвуфера
9[0-9]{1,3}"

Если стрПаттерн <> "" Тогда
strInput = Myrange.Value
улЗаменить = ""

С регулярным выражением
. Глобал = Истина
.Многострочный = Истина
.Игнорекасе = Ложь
.Шаблон = стрПаттерн
Конец с

Если regEx.test(strInput) Тогда
simpleCellRegex = regEx.Replace (strInput, strReplace)
Еще
simpleCellRegex = "Не соответствует"
Конец, если
Конец, если
Конечная функция

Поместите свои строки ("12abc") в ячейку 9[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput как строка
Dim Myrange As Range

Установите Myrange = ActiveSheet.Range("A1:A5")

Для каждой ячейки в Myrange
Если стрПаттерн <> "" Тогда
strInput = ячейка. Значение

С регулярным выражением
.Глобал = Истина
.Многострочный = Истина
.Игнорекасе = Ложь
.Шаблон = стрПаттерн
Конец с

Если regEx.Test(strInput) Тогда
MsgBox (regEx. Replace (strInput, strReplace))
Еще
MsgBox ("Не соответствует")
Конец, если
Конец, если
Следующий
Конец сабвуфера

Пример 4 : Разделение различных шаблонов

Этот пример перебирает диапазон ( A1 , A2 и A3 ) и ищет строку, начинающуюся с трех цифр, за которой следует один буквенный символ, а затем 4 цифры. Вывод разделяет совпадения шаблона на соседние ячейки с помощью () . $1 представляет первый шаблон, соответствующий первому набору () .

9[0-9]{3})([a-zA-Z])([0-9]{4})"

Если стрПаттерн <> "" Тогда
стрИнпут = C.Value

С регулярным выражением
.Глобал = Истина
.Многострочный = Истина
.Игнорекасе = Ложь
.Шаблон = стрПаттерн
Конец с

Если regEx.test(strInput) Тогда
C. Offset(0, 1) = regEx.Replace(strInput, "$1")
C.Offset(0, 2) = regEx.Replace(strInput, "$2")
C.Offset(0, 3) = regEx.Replace(strInput, "$3")
Еще
C.Offset(0, 1) = "(Не соответствует)"
Конец, если
Конец, если
Следующий
Конец сабвуфера

Результаты:

Дополнительные примеры шаблонов 

 Объяснение шаблона String Regex
a1aaa [a-zA-Z][0-9][a-zA-Z]{3} Одна буква, одна цифра, три буквы
a1aaa [a-zA-Z]?[0-9][a-zA-Z]{3} Может иметь или не иметь предшествующий буквенный символ
a1aaa [a-zA-Z][0-9][a-zA-Z]{0,3} Одна буква, одна цифра, от 0 до 3 букв
a1aaa [a-zA-Z][0-9][a-zA-Z]* Одна буква, одна цифра, за которой следует любое количество буквенных символов
 \<\/[a-zA-Z][0-9]\> Точный символ, не являющийся словом, за исключением любой одиночной буквы, за которой следует любая одиночная цифра

Как пользоваться ОПЕРАТОРОМ LIKE

  • Главная
  • Функции
  • Формулы
  • Функции

    Основы Excel
    Таблица Excel
    Расширенный фильтр
    Проверка данных
    Выпадающие списки
    Именованные диапазоны
    Решатель

  • Карты
  • Условное форматирование
  • Сводная таблица
  • VBA

    Функции VBA
    Методы
    Характеристики
    Заявления
    Макросы
    Пользовательские функции
    Файлы и папки
    Флажки

  • Архив

    Все статьи отсортированы по дате
    Категории
    Товары
    Продвинутый курс Excel

  • Контакт

    Обо мне
    Блоги я прочитал

Автор: Оскар Кронквист Последнее обновление статьи: 14 апреля 2021 г.

Оператор LIKE позволяет сопоставлять строку с шаблоном с помощью Excel VBA. На изображении выше показан макрос, использующий оператор LIKE для сопоставления строки с заданным шаблоном.

Шаблон построен на определенных символах, которые я продемонстрирую ниже.

Синтаксис функции Excel VBA

результат  =  строка  подобно шаблон

Аргументы

3 4 результат

Обязательно. Любой номер.
строка Обязательно. Строка.
узор Обязательно. Строка, соответствующая требуемым символам шаблона, описанным ниже.
Информация на этой веб-странице
  1. Какие символы можно использовать в качестве шаблона?
    1. Как сделать оператор LIKE нечувствительным к регистру?
    2. Что возвращает оператор LIKE?
  2. Сравнение значения ячейки с образцом
    1. Как использовать вопросительный знак (?)
    2. Как использовать звездочку (*)
    3. Как использовать знак номера или хэштег (#)
    4. Комбинирование символов шаблона
    5. Как пользоваться кронштейнами
  3. Поиск шаблона регулярного выражения и извлечение совпадающих значений (UDF)
  4. Поиск шаблона регулярного выражения и возвращаемых значений в соседнем столбце (UDF)
  5. Извлечение слов, соответствующих шаблону регулярного выражения, из диапазона ячеек (UDF)
  6. Куда поставить код?
  7. Получить файл Excel

1.

Какие символы можно использовать в качестве шаблона в операторе LIKE?

Следующие символы специально разработаны, чтобы помочь вам в построении шаблона:

Символ Описание Текст
? знак вопроса Соответствует любому одиночному символу.
* звездочка Совпадает с нулем или более символов.
# число или решетка Любая одиночная цифра.

A1A* — Вы также можете использовать строку в сочетании с указанными выше символами для создания шаблона. Это соответствует строке, начинающейся с A1A, или равно A1A. Звездочка также соответствует нулевым символам.

Символы Описание Текст
[абв] кронштейны Символы, заключенные в квадратные скобки, позволяют найти любой одиночный символ в строке.
[!abc] восклицательный знак Восклицательный знак (!) соответствует любому одиночному символу, отсутствующему в строке.
[А-Я] дефис Дефис позволяет указать диапазон символов.

Наверх

1.1 Как сделать оператор LIKE нечувствительным к регистру?

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

Значение по умолчанию: Опция сравнения двоичных файлов. Используйте Опция сравнения текста , чтобы сделать сравнение нечувствительным к регистру, но поместите код в отдельный модуль, чтобы другие макросы/функции не затрагивались.

Настройка Описание
Опция сравнения двоичных файлов По умолчанию.
Текст сравнения опций Вычисления без учета регистра.

Чтобы узнать больше, прочитайте эту статью: Заявление о сравнении вариантов

В начало

1.2 Что возвращает оператор LIKE?

На изображении выше показан макрос в редакторе Visual Basic, который возвращает TRUE или FALSE в зависимости от того, соответствует шаблон строке или нет.

Шаблон *1* соответствует 552513256, а приведенный выше макрос показывает окно сообщения, содержащее значение True.

результат  =  строка  Like  шаблон

Оператор НРАВИТСЯ возвращает логическое значение, ИСТИНА или ЛОЖЬ, в зависимости от того, соответствует шаблон или нет. Вы можете сохранить логическое значение в переменной, строка выше сохраняет логическое значение в переменной result .

В начало

2. Сравните значение ячейки с образцом

Эта простая определяемая пользователем функция (UDF) позволяет указать шаблон и сравнить его со значением ячейки. Если есть совпадение, функция возвращает ИСТИНА. Если нет, ЛОЖЬ. Я собираюсь использовать эту UDF в приведенных ниже примерах.

 'Имя пользовательской функции
'Параметр c объявленного типа данных Диапазон
'Параметр ptrn объявил тип данных String
Функция Compare(c As Range, pttrn As String) As Boolean

'Вычислить строку в переменной c с шаблоном, сохраненным в переменной ptrn
'Вернуть результат пользовательской функции
Сравнить = c Нравится ptrn
Конечная функция

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

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

В начало

2.1 Как использовать знак вопроса (?)

На рисунке выше показана определяемая пользователем функция, которую мы создали в разделе 2. Она берет строку из столбца B и сравнивает ее с образцом в столбец D. В столбец E возвращается логическое значение True или False.

Синтаксис UDF: Сравнить( строка , шаблон )

Вопросительный знак (?) соответствует любому одиночному символу.

Формула в ячейке E6:

=Сравнить(B6, D6)

Значение в ячейке B6 ABC соответствует A?C, указанному в ячейке D6, ИСТИНА возвращается в ячейку E6.

Формула в ячейке E7:

=Сравнить(B7, D7)

Значение в ячейке B7 ABCD не соответствует шаблону A?D. BC — это два символа, вопросительный знак соответствует любому одиночному символу. FALSE возвращается в ячейке E3.

Формула в ячейке E8:

=Сравнить(B8, D8)

Значение в ячейке B8 ABCD соответствует шаблону, указанному в ячейке D8, ?BC?. TRUE возвращается в ячейку E8.

В начало

2.2 Как использовать символ звездочки (*)

На изображении выше показана определяемая пользователем функция (UDF), описанная в разделе 2. Она оценивает соответствие шаблона строке с помощью оператора LIKE. Если это так, возвращает True. Если нет, Ложь.

UDF вводится в ячейку E9, Е10 и Е11. Первый аргумент в пользовательской функции сравнения — это ссылка на ячейку строки, а второй аргумент — ссылка на ячейку шаблона.

Начнем с формулы в ячейке E9:

=Сравнить(B9, D9)

Шаблон говорит вам, что первые три символа должны быть AAA, а затем * (звездочка) соответствует нулю или более символов. AAAC соответствует шаблону AAA*, и UDF возвращает TRUE в ячейке E9.

Формула в ячейке E10:

=Сравнить(B10, D10)

aaa* не соответствует шаблону AAAC. ааа не равно ААА. Оператор LIKE чувствителен к регистру, если вы не измените настройки на Option Compare Text.

Формула в ячейке E11:

=Сравнить(B10, D10)

(*) соответствует нулю или более символов, DDC23E соответствует DD*E.

В начало

2.3 Как использовать знак номера или символ хэштега (#)

На изображении выше показаны шаблоны в столбце D и строки в столбце B, символ хэштега # соответствует одной цифре. Чтобы сопоставить несколько цифр, используйте несколько #.

Формула в ячейке E3:

=Сравнить(B3, D3)

Строка 123 в ячейке B3 соответствует шаблону 12#, в ячейке E3 возвращается значение TRUE.

Формула в ячейке E4:

=Сравнить(B4, D4)

Строка 123 в ячейке B4 не соответствует шаблону 1# в ячейке D4, символ хэштега соответствует только одной цифре.

Формула в ячейке E5:

=Сравнить(B5, D5)

Строка 123 в ячейке B5 соответствует шаблону в ячейке D5 #2#.

Наверх

2.4 Комбинирование символов шаблона

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

Формула в ячейке E12:

=Сравнить(B12, D12)

Шаблон *##?? в ячейке D12 соответствует строке AA23BB в ячейке B12, формула возвращает True в ячейке E12. Звездочка соответствует 0 (ноль) любому количеству символов, хэштег соответствует любой отдельной цифре.

Обратите внимание, что в шаблоне два хэштега. ? вопросительный знак соответствует любому одиночному символу.

Формула в ячейке E13:

=Сравнить(B13, D13)

Строка в ячейке B13 AA23BB не соответствует шаблону *##? указано в ячейке D13. После цифр должен быть один символ, в строке после цифр два символа.

Формула в ячейке E14:

=Сравнить(B13, D13)

Строка AA23BB в ячейке B14 соответствует шаблону в ячейке D14 *##*. Звездочка соответствует 0 (ноль) любому количеству символов, хэштеги соответствуют двум одиночным цифрам, а последний символ шаблона — звездочка.

В начало

2.5 Как использовать скобки с оператором LIKE

Скобки соответствуют любому указанному вами символу. Дефис позволяет сравнивать диапазон букв, однако они должны быть отсортированы от A до Z. [A-C] — допустимый диапазон, а [C-A] — недопустимый.

Формула в ячейке E15:

=Сравнить(B15, D15)

Формула в ячейке E15 оценивает строку ABCD как шаблон [A]* и возвращает ИСТИНА. Первый символ в строке должен быть A или a, количество оставшихся символов может быть нулевым или любым числом длины.

Формула в ячейке E16:

=Сравнить(B16, D16)

Формула в ячейке E16 оценивает строку ABCD как шаблон [A] и возвращает ЛОЖЬ. Строка должна состоять только из одного символа, и этот символ должен быть A или a.

Формула в ячейке E17:

=Сравнить(B17, D17)

Формула в ячейке E17 сравнивает строку ABCD с образцом [!A]* и возвращает ЛОЖЬ. Первый символ в строке должен быть любым, кроме символа A, а количество оставшихся символов может быть 0 (ноль) или любое число длины.

Формула в ячейке E18:

=Сравнить(B18, D18)

Формула в ячейке E18 сравнивает строку C22R с шаблоном [A-Z]##? и возвращает ИСТИНА. Первым символом в строке должна быть буква от A до Z, затем любые две цифры и, наконец, вопросительный знак, соответствующий любому отдельному символу.

Формула в ячейке E19:

=Сравнить(B19, D19)

Формула в ячейке E19 сравнивает строку C22R с образцом [A-Z]##[A-Z] и возвращает значение TRUE. Строка начинается с любой буквы от A до Z, затем с любых двух цифр и, наконец, с любой буквы от A до Z.

Формула в ячейке E20:

=Сравнить(B20, D20)

Формула в ячейке E20 сравнивает строку C222 с образцом [A-Z]##[A-Z] и возвращает ЛОЖЬ. Строка начинается с любой буквы от A до Z, затем с любых двух цифр и, наконец, с любой буквы от A до Z. Строка содержит цифру в качестве последнего символа, который не является совпадением.

В начало

3. Поиск шаблона регулярного выражения в столбце и получение совпадающих значений (UDF)

Следующая определяемая пользователем функция позволяет извлекать значения ячеек с помощью оператора LIKE и шаблона, указанного на листе. .

Формула вводится в ячейку D6 как формула массива, она возвращает несколько значений в ячейки ниже, если шаблон соответствует нескольким значениям.

Используемый пример шаблона находится в ячейке D3 «?B?». Знак вопроса соответствует одному символу, функция UDF возвращает все значения, содержащие три буквы, а средняя буква — B.

Формула массива в ячейке D6:D9:

=SearchPattern(B3:B16, D3)

Как ввести формула массива

 'Имя Пользовательская функция
Функция SearchPattern(c как диапазон, ptrn как строка)

'Измерение переменных и объявление типов данных
Dim d как строка

'Перебрать каждую ячейку в c
Для каждой ячейки в c

'Проверить, соответствует ли строка ptrn, если да, то объединить значение ячейки и запятую с переменной d
Если ячейка похожа на ptrn, тогда d = d & cell & ","

'Продолжить со следующей ячейки
Следующая ячейка

'Разбить строку в переменной d, используя символ запятой, затем транспонировать массив и вернуть значения в UDF SearchPattern
SearchPattern = Application.Transpose(Split(d, ","))
Конечная функция

Куда поставить код?

В начало

4. Поиск шаблона и возврат значений в соседнем столбце (UDF)

Эта определяемая пользователем функция позволяет искать шаблон в столбце и возвращать соответствующее значение в другом столбце.

Пользовательская функция возвращает значение из столбца C, если соответствующее значение в столбце B той же строки соответствует шаблону регулярного выражения, указанному в ячейке E3.

Формула массива в ячейке E6:E9:

=SearchCol(B3:B16, C3:C16, E3)

Как ввести формулу массива

 'Имя определяемой пользователем функции (UDF)
Функция SearchCol(b как диапазон, c как диапазон, pttrn как строка)

'Измерение переменных и объявление типов данных
Dim a As Long, d as String

'Подсчет ячеек в диапазоне ячеек b
a = b.Cells.CountLarge

'For... Следующий оператор, означающий повторение строк между временами
Для i = 1 К а

'Проверить, соответствует ли ячейка шаблону, если да, добавьте c и запятую к переменной d
Если b.Cells(i) Like ptrn Тогда d = d & c.Cells(i) & ","
Далее я

'Разделить значения в переменной d и вернуть массив на рабочий лист
SearchCol = Application.Transpose(Split(d, ","))
Конечная функция

Куда поставить код?

В начало

5.

Извлечение слов, соответствующих шаблону регулярного выражения, из диапазона ячеек (UDF)

На изображении выше показана определяемая пользователем функция (UDF), которая извлекает слова, соответствующие заданному шаблону регулярного выражения. Это означает, что UDF может извлекать несколько слов из одной и той же ячейки.

Вы можете использовать эту UDF для извлечения телефонных номеров, почтовых индексов, адресов электронной почты, html-кода или чего угодно из ячеек, содержащих много данных.

Формула массива в ячейке D5:

=ExtractWords(B3:B10,E2)

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

5.1 Как ввести формулу массива

  1. Выберите диапазон ячеек, который вы хотите использовать.
  2. Нажмите левую кнопку мыши в строке формул.
  3. Вставьте формулу массива в строку формул.
  4. Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
  5. Нажмите Enter один раз.
  6. Отпустить все клавиши.

Ваша формула теперь является формулой массива, вы узнаете формулы массива по начальным и конечным фигурным скобкам в строке формул. {= формула_массива }

Не вводите эти символы самостоятельно, они появляются автоматически.

5.2 Код VBA 

 'Имя определяемой пользователем функции (UDF)
Функция ExtractWords(c как диапазон, ptrn как строка)

'Перебрать каждую ячейку в диапазоне ячеек c
Для каждой ячейки в c

    'Разбить содержимое ячейки на массив, используя пробел в качестве символа-разделителя
    Arr = Разделить (ячейка, "")

    'Перебрать каждое значение в массиве Arr
    Для каждого в записи

        'Проверить, соответствует ли строка в переменной a шаблону в переменной pttrn
        Если Нравится ptrn Тогда

            'Добавить строку a и запятую к переменной d
            д = д & а & ​​","
        Конец, если
    Следующий
    
Следующая ячейка

'Разделить переменную d, используя запятую в качестве символа-разделителя, и вернуть массив в UDF
ExtractWords = Application. Transpose (Разделить (d, ","))

Конечная функция

6. Куда вводить код?

  1. Нажмите Alt+F11, чтобы открыть редактор Visual Basic (VBE).
  2. Нажмите мышью на «Вставить» в верхнем меню, см. изображение выше.
  3. Появится всплывающее меню. Нажмите левой кнопкой мыши на «Модуль», чтобы вставить модуль в вашу книгу.
  4. Скопируйте код VBA.
  5. Вставить в окно кода.
  6. Вернуться в Excel.

Обратите внимание: сохраните книгу с расширением файла *.xlsm (книга с поддержкой макросов), чтобы прикрепить код. Этот шаг важен.

Знаете ли вы?

Во многих функциях Excel можно использовать знак вопроса (?) и звездочку (*). Функция СЧЁТЕСЛИ в ячейке C2, показанная на изображении выше, подсчитывает ячейки в диапазоне ячеек A2:A15, используя шаблон в ячейке B2.

Если вам нужно использовать еще более сложные шаблоны, Excel позволяет использовать регулярные выражения, см. эту тему:
Как использовать регулярные выражения (Regex) в Microsoft Excel как в ячейке, так и в циклах

К началу

Получить файл Excel

LIKE-operatorv3. xlsm

К началу

Категория регулярных выражений

Подсчет совпадающих строк с использованием регулярных выражений
В этой статье демонстрируется определяемая пользователем функция (UDF), которая подсчитывает, сколько раз строка встречается в значении ячейки […]

Извлечение ссылок на ячейки из формулы
В этой статье демонстрируется определяемая пользователем функция, которая позволяет извлекать ссылки на ячейки из формулы в заданном […]

Программное извлечение значений из ThingSpeak
  На рисунке выше показаны дешевые китайские модули IoT: датчик температуры и esp8266-01 (25 мм x 15 мм) Thingspeak […]

Функции в этой статье

Категории Excel

Домашняя страница

Последние обновленные статьи.

Функции Excel

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

Формулы Excel

Более 1300 формул, организованных в подкатегории.

Таблицы Excel

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

Расширенный фильтр

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

Проверка данных

Позволяет контролировать, что пользователь может вводить в ячейку. Это позволяет вам указать условия и показать собственное сообщение, если введенные данные недействительны.

Раскрывающийся список

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

Именованные диапазоны

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

Excel Solver

Excel Solver — это бесплатная надстройка, которая использует целевые ячейки, ограничения, основанные на формулах на листе, для выполнения анализа «что, если» и других проблем принятия решений, таких как перестановки и комбинации.

Диаграммы

Функция Excel, позволяющая визуализировать данные в виде графика.

Условное форматирование

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

Сводные таблицы

Позволяет быстро суммировать большие объемы данных в удобной для пользователя форме. Эта мощная функция Excel позволяет эффективно анализировать, упорядочивать и классифицировать важные данные.

VBA

VBA означает Visual Basic для приложений и представляет собой язык программирования, разработанный Microsoft. Он позволяет автоматизировать трудоемкие задачи и создавать пользовательские функции.

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