Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как проверить, начинается ли ячейка или заканчивается определенным символом в Excel?

Формула суммы ячеек в Excel

Сумма, если ячейки содержат звездочку

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «*».

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.

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

). Тильда считает, что Excel должен воспринимать следующий символ буквально.

В этом случае мы используем «

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

Альтернативное использование SUMSLIMN

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

Поиск нестрогого соответствия символов

Бывают случаи, когда пользователь не знает точной комбинации символов для поиска, что затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, аббревиатуры и т.д., что вносит дополнительную путаницу и делает поиск практически невозможным. Может произойти и обратная ситуация: заданной комбинации ячеек соответствует слишком много ячеек, и цель поиска снова не достигается (кому нужно 100500+ найденных ячеек? ).

Для решения этих проблем очень хорошо подходят подстановочные знаки, которые сообщают Excel о сомнительных местах. Различные символы могут быть скрыты под подстановочными знаками, и Excel видит только их относительное положение в поисковой фразе. Существует два таких знака подстановки: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «? (символ «?» означает неизвестный символ).

Читайте так же:
Как разрешить ввод только буквенно-цифровых символов в Excel?

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер — символ «?». Скрыть можно только один неизвестный символ. В этом случае Excel найдет все ячейки, содержащие шаблон 106, 116, 126, 136 и т.д. А если вы укажете 1? с ?6, Excel будет искать ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более строгие ограничения на поиск, при котором учитывается количество пропущенных символов (равное количеству вопросов «?»).

Читайте так же:
Как ранжировать значения по группам в Excel?

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

Примеры задач, решаемых с помощью регулярных выражений

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

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

Склеивание текста по условию

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

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

склеивание (сцепка) текста по условию

Иными словами, нам нужен инструмент, который будет конкатенировать (объединять) текст по условию, аналог функции SUMIF, но для текста.

Способ 0. Формулой

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

Читайте так же:
Как преобразовать диапазон в таблицу или наоборот в Excel?

Сцепка текста по условию формулой

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

Отбор строк

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

Способ 1. Макрофункция склейки по одному условию

Если исходный список не отсортирован по компаниям, простая формула выше не будет работать, но вы можете легко обойти это с помощью небольшой пользовательской функции в VBA. Откройте редактор Visual Basic, нажав Alt+F11 или воспользовавшись кнопкой Visual Basic на вкладке Developer. В открывшемся окне вставьте новый пустой модуль через меню Insert — Module и скопируйте туда текст нашей функции:

Если теперь вы вернетесь в Microsoft Excel, то найдете нашу функцию MergeIf в категории User Defined в списке функций (кнопка fx на панели формул или вкладка Formula — Insert Function) . Аргументы функции следующие:

функция сцепить если выполняется условие

Способ 2. Сцепить текст по неточному условию

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

склейка по приблизительному условию

Поддерживаются стандартные символы подстановки:

  • Звездочка (*) — указывает на любое количество символов (включая отсутствие символов)
  • Знак вопроса (?) — указывает на один символ
  • Галочка (#) — указывает на один знак (0-9)

По умолчанию оператор Like чувствителен к регистру, то есть он понимает, что, например, «Orion» и «oRiOn» — это разные компании. Чтобы не учитывать регистр, вы можете добавить строку Option Compare Text в начале модуля в редакторе Visual Basic, что сделает Like нечувствительным к регистру.

Читайте так же:
Как преобразовать текст в функцию столбца в Excel?

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

  • ?1## ?777RUS — выборка всех номерных знаков в регионе 777, начинающихся с 1
  • LLC* — все компании, название которых начинается с LLC
  • ##7## — все товары с пятизначным цифровым кодом, где третьей цифрой является 7
  • . — все пятибуквенные имена и т.д.

Способ 3. Макрофункция склейки текста по двум условиям

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

Он будет использоваться точно так же — вам просто нужно будет предоставить больше аргументов:

склейка по нескольким условиям

Способ 4. Группировка и склейка в Power Query

Для решения проблемы без изучения VBA можно использовать бесплатную надстройку Power Query. Его можно загрузить для Excel 2010-2013 здесь, а для Excel 2016 он встроен по умолчанию. Последовательность шагов следующая:

Power Query не умеет работать с обычными таблицами, поэтому первым шагом будет преобразование нашей таблицы в интеллектуальную таблицу. Для этого выделите его и нажмите Ctrl + T или выберите вкладку Главная — Формат в таблицу. На появившейся вкладке Layout можно дать имя таблице (я оставил стандартное Table1):

Умная таблица

Вот надстройка Power Query, загруженная с нашей таблицей. В Excel 2016 щелкните Данные — Из таблицы: Если у вас Excel 2010 или 2013, нажмите Power Query — Из таблицы:

Загрузка в Power Query

В открывшемся окне редактора запросов выберите столбец Company, щелкнув по заголовку, и нажмите кнопку Group by в верхней части. Введите имя нового столбца и тип операции в группировке — Все строки :

Читайте так же:
Как разделить адреса электронной почты на имя пользователя и домен в Excel?

Группировка в Power Query

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

Содержимое таблиц группировки

В следующем столбце мы будем использовать операцию разделения запятой, чтобы объединить столбцы Address (Адрес) в каждой из мини-таблиц. На вкладке Добавить столбец выберите Пользовательский столбец (Add column — Custom column) и в появившемся окне введите имя нового столбца и формулу для связи на языке M программы Power Query:

Пользовательский столбец с функцией склейки

Значения М-функции чувствительны к регистру (в отличие от Excel). После нажатия кнопки OK мы получим новый столбец со склеенными адресами:

Результат

Осталось только удалить ненужный столбец TableAddresses (щелкните правой кнопкой мыши на заголовке — Remove column) и загрузить результаты в рабочий лист, нажав на вкладке Home — Close and load (Закрыть и загрузить):

Выгрузка результатов на лист

Важное замечание: В отличие от предыдущих методов (функций), таблицы Power Query не обновляются автоматически. Если в будущем в исходные данные будут внесены изменения, щелкните правой кнопкой мыши в любом месте таблицы результатов и выберите Обновить.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector