000818.ru

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

Как найти наиболее часто встречающийся текст с критериями в Excel?

Текстовые функции Excel

Имена, номера кредитных карт, адреса клиентов или сотрудников, комментарии и т.д. — это строки, с которыми многие сталкиваются при работе с приложением Excel. Поэтому полезно уметь обрабатывать информацию такого типа. В этой статье мы рассмотрим текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, являются наиболее полезными и интересными:

  1. ЛЕВСИМВ;
  2. ПРАВСИМВ;
  3. ДЛСТР;
  4. НАЙТИ;
  5. ЗАМЕНИТЬ;
  6. ПОДСТАВИТЬ;
  7. ПСТР;
  8. СЖПРОБЕЛЫ;
  9. СЦЕПИТЬ.

Список текстовых функций доступен на закладке «Формулы» => выпадающий список «Текст»:

Список текстовых функций Excel

Примеры функций И, ИЛИ

Также при использовании в Экселе функции ЕСЛИ есть возможность соединить ее с простыми операторами сравнения (вроде И, ИЛИ). Например, можно написать такое условие: =ЕСЛИ(И(A1<=5; A1>3); «проходит», «не проходит»). Получается следующее условие: если оценка студента меньше или равна 5 и больше 3, то будет показан комментарий «проходит», в противном случае — «не проходит» (проходят только те студенты, у которых в оценках стоят четверки и пятерки).

Более сложный пример использования функции ЕСЛИ - с использованием условий И либо ИЛИ

Более сложный пример использования функции ЕСЛИ — с использованием условий И либо ИЛИ

Следующее условие с примером оператора ИЛИ: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). Получается, что если число в ячейке А1 равняется 5 или 10, то программа выдаст 100, в противном случае — 0.

Кроме того, эти простые операторы можно использовать и для решения более сложных задач. Например, необходимо отобрать из базы данных должников, которые задолжали более 10 000 рублей и не платили более 6 месяцев. Если программа находит такого человека, она выдает критерий «проблемный заемщик».

Допустим, в ячейке A1 находится информация о сроке задолженности (в месяцах), а в поле B1 — сумма задолженности. Тогда формула будет выглядеть следующим образом: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный заемщик»; «»). Если будет найден человек, который подходит под указанное условие, то программа напишет напротив его фамилии комментарий «проблемный заемщик», в противном случае ячейка останется пустой.

Читайте так же:
Как найти повторяющиеся значения в двух столбцах в Excel?

Если один из параметров считается критическим, то можно составить формулу следующего вида: =If(OR(A1>=6; B1>10000); «критическая ситуация»; «»). Если программа найдет совпадение хотя бы по одному параметру (либо срок, либо сумма задолженности), то пользователь увидит сообщение о том, что ситуация является критической. Разница между предыдущей формулой и текущей в том, что сообщение «Проблемный заемщик» выдавалось только при выполнении обоих условий.

Получение последней позиции символа с помощью пользовательской функции (VBA)

Приведенная выше формула красива и хорошо работает, но она немного сложна.

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

Используем тот же набор URL (как показано ниже):

В данном случае я создал функцию LastPosition, которая возвращает последнее положение символа (здесь это слэш).

Вот формула, которая поможет это сделать:

Он несколько проще, чем тот, который мы использовали выше.

Вот, как это работает:

  • LastPosition, наша настраиваемая функция, возвращает позицию косой черты. Эта функция принимает два аргумента — ссылку на ячейку с URL и символ, положение которого мы хотим найти.
  • Далее функция RIGHT возвращает все символы после косой черты.

Вот код VBA для этой функции:

Для работы этой функции необходимо использовать редактор V B. После этого вы можете использовать ее так же, как и любую другую функцию Excel.

Вот как вставить этот код в VB сервер:

Вот шаги по вводу этого кода в редактор VB:

  1. Перейдите на вкладку Разработчик.
  2. Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
  3. На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
  4. Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для вашей книги.
  5. Скопируйте и вставьте код в окно модуля.
Читайте так же:
Как отключить двойной щелчок по границе ячейки в Excel?

Формула теперь добавлена на все листы книги.

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

Кроме того, вам могут понравиться следующие пособия по Excel:

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

= ПРОСМОТР( 2 ; 1 / ПОИСК ( вещи ; А1 ); вещи )

Формула, основанная на функциях SURVEY и SEARCH, может проверить ячейку на соответствие списку из нескольких элементов и вернуть последнее найденное в списке совпадение. Если найдено несколько совпадений, формула возвращает последнее совпадение в списке.

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

В примере формула находится в позиции C5 :

Текстовые функции Microsoft Excel

Сначала рассмотрим основные текстовые функции, используемые в приведенных ниже примерах. Excel использует русские названия для функций, в отличие от своего ближайшего аналога LibreOffice.Calc. Формат, в котором записана текстовая функция, и то, что она делает, показано ниже.

Задача 1. Объединение текстовых строк

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

Решение. Для реализации нашего решения будет использоваться CHAIN, поскольку оно довольно простое.

Запишите формулу =CREATE(A1;»»;B1;»»;B1;»»;C1) в ячейку D1. Можно воспользоваться мастером функций.

Теперь вам следует скопировать его на весь диапазон, требуемый в столбце D.

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

Посмотрите на рисунок ниже. Результат преобразования находится в столбце D.

текстовые функции excel описание и примеры

Мастер в виде ссылки

Задача 2. Разделение текстовых строк

В итоге получилось, что их имена и фамилии, а также отчества оказались в одном столбце. Разделите данные по столбцам.

Читайте так же:
Как объединить данные из нескольких листов / книг?

Решение. Проблема является более сложной и требует дополнительных текстовых функций.

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

=ЛЕВСИМВ(A1;НАЙТИ(» «;A1))

На основе функции Find, в этой формуле выходные символы определяются путем нахождения первого пробела в строке.

текстовые формулы в excel

Строка формулы при разделении имени

Чтобы ввести имя в ячейку С1, введите следующую формулу

=ПСТР(A1;НАЙТИ(» «;A1)+1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1)

Если вы посмотрите на входной синтаксис этой функции, вы получите

  1. Выражение Find(» «;A1)+1 отвечает за нахождение местоположения первого пробела в текстовой строке. Однако, чтобы получить позицию первой буквы имени, добавляется эттер.
  2. Для определения количества символов в имени используется более сложная конструкция SEARCH(» «;A1;Find(» «;A1)+1)-Find(» «;A1)-1. Количество символов определяется как разница между позициями пробелов, разделяющих имя. Чтобы исключить сами пробелы из числа найденных символов, добавьте 1 к исходной позиции, а затем вычтите 1 из результата.

Отчество фамилии получается в ячейке D1 с помощью более сложной формулы

=ПСТР(A1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1;ДЛСТР(A1)-ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1)

В этом случае количество знаков в отчестве определяется как разница между общим количеством знаков (TLSTR) и появлением второго пробела.

Функции ПОИСК и ПОИСК выполняют одно и то же действие из-за разницы в регистрах символов. Можно обойтись только одной из них.

Задача 3. Укорачивание текстовых строк

Измените список сотрудников. Поместите инициалы и фамилии в одну колонку.

Решение. В зависимости от начального состояния списка возможны два варианта.

Вариант 1. Один столбец содержит все входные данные. Имена разделяются одним пробелом.

Запишите следующую формулу

=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1));ПСТР(A1;НАЙТИ(» «;A1);2);».»;ПСТР(A1;НАЙТИ(» «;A1; НАЙТИ(» «;A1)+1);2);».»)

пример использования текстовых функций

В одном столбце преобразуйте имя и отчество в инициалы.

Вариант 2: исходные данные находятся в разных столбцах.

Формула перевода

Читайте так же:
Как округлить до ближайшего четного или нечетного числа в Excel?

=СЦЕПИТЬ(A1;» «;ЛЕВСИМВ(B1);».»;ЛЕВСИМВ(C1);».»)

пример использования текстовых функций в Excel

Преобразование имени и отчества в инициалы (исходные данные в разных колонках)

Если у вас есть примеры использования текстовых функций в Excel, поделитесь ими с нами в комментариях. Ознакомьтесь с этой статьей Excel о построении графиков функций. Распространите информацию о статье, поделившись ею с друзьями. Кнопки социальных сетей ниже.

Дорогой читатель! Вы посмотрели статью до конца.Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали или откройте содержание блога.

ОЧЕНЬ ВАЖНО! Оцени лайком или дизлайком статью!Блог твой компьютер лайкБлог твой компьютер дизлайк

7. Функция ПОДСТАВИТЬ — Приводит ключевую фразу к модифицированному широкому соответствию Google AdWords

Если вам нужно преобразовать фразы широкого соответствия в модифицированное широкое соответствие AdWords, то используйте формулу «+»&SEND(SPACE(A1);»»; «+»»), где (A1) — ячейка, которую нужно модифицировать.

formuly-excel-11

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

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