Foreversoft.ru

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

Функция len в excel на русском

Примеры функции ДЛСТР для подсчета количества символов в Excel

Функция ДЛСТР выполняет возвращение количество знаков в текстовой строке. Иными словами, автоматически определяет длину строки, автоматически подсчитав количество символов, которые содержит исходная строка.

Описание принципа работы функции ФИШЕР в Excel

Чаще всего данная функция используется в связке с другими функциями, но бывают и исключения. При работе с данной функцией необходимо задать длину текста. Функция ДЛСТР возвращает количество знаков с учетом пробелов. Важным моментом является тот факт, что данная функция может быть доступна не на всех языках.

Рассмотрим применение данной функции на конкретных примерах.

Пример 1. Используя программу Excel, определить длину фразы «Добрый день, класс. Я ваш новый учитель.».

Для решения данной задачи открываем Excel, в произвольной ячейке вводим фразу, длину которой необходимо определить, дальше выбираем функцию ДЛСТР. В качестве текста выбираем ячейку с исходной фразой и контролируем полученный результат (см. рисунок 1).

Рисунок 1 – Пример расчетов.

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

Формула с текстовыми функциями ДЛСТР ПРАВСИМВ и ПОИСК

Пример 2. Имеется строка, содержащая следующую имя файла с его расширением: «Изменение.xlsx». Необходимо произвести отделение начальной части строки с именем файла (до точки) без расширения .xlsx.

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

  • ПРАВСИМВ – функция, которая возвращает заданное число последних знаков текстовой строки;
  • ПОИСК – функция, находящая первое вхождение одной текстовой строки в другой и возвращающая начальную позицию найденной строки.

Полученные результаты проиллюстрированы на рисунке 2.

Рисунок 2 – Результат выведения.

Логическая формула для функции ДЛСТР в условном форматировании

Пример 3. Среди имеющегося набора текстовых данных в таблице Excel необходимо осуществить выделение тех ячеек, количество символов в которых превышает 12.

Исходные данные приведены в таблице 1:

Исходная строка
Добрый день, класс. Я ваш новый ученик
Добрый день, класс.
Добрый день
Я ваш учитель
Я ваш

Решение данной задачи производится путем создания правила условного форматирования. На вкладке «Главная» в блоке инструментов «Стили» выбираем «Условное форматирование», в выпадающем меню указываем на опцию «Создать правило» (вид окна показан на рисунке 3).

Рисунок 3 – Вид окна «Создать правило».

В окне в блоке «Выберите тип правила» выбираем «Использовать формулу для определения форматируемых ячеек», в следующем поле вводим формулу: =ДЛСТР(A2)>12, после чего нажимаем кнопку формат и задаем необходимый нам формат выбранных полей. Ориентировочный вид после заполнения данного окна показан выше на рисунке.

После этого нажимаем на кнопку «Ок» и переходим в окно «Диспетчер правил условного форматирования» (рисунок 4).

Рисунок 4 – Вид окна «Диспетчер правил условного форматирования».

В столбце «Применяется к» задаем необходимый нам диапазон ячеек с исходными данными таблицы и нажимаем кнопку «Ок». Полученный результат приведен на рисунке 5.

Рисунок 5 – Окончательный результат.

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

Примеры функции ДЛСТР для подсчета количества символов в Excel

Функция ДЛСТР выполняет возвращение количество знаков в текстовой строке. Иными словами, автоматически определяет длину строки, автоматически подсчитав количество символов, которые содержит исходная строка.

Описание принципа работы функции ФИШЕР в Excel

Чаще всего данная функция используется в связке с другими функциями, но бывают и исключения. При работе с данной функцией необходимо задать длину текста. Функция ДЛСТР возвращает количество знаков с учетом пробелов. Важным моментом является тот факт, что данная функция может быть доступна не на всех языках.

Рассмотрим применение данной функции на конкретных примерах.

Пример 1. Используя программу Excel, определить длину фразы «Добрый день, класс. Я ваш новый учитель.».

Для решения данной задачи открываем Excel, в произвольной ячейке вводим фразу, длину которой необходимо определить, дальше выбираем функцию ДЛСТР. В качестве текста выбираем ячейку с исходной фразой и контролируем полученный результат (см. рисунок 1).

Читать еще:  Value в excel

Рисунок 1 – Пример расчетов.

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

Формула с текстовыми функциями ДЛСТР ПРАВСИМВ и ПОИСК

Пример 2. Имеется строка, содержащая следующую имя файла с его расширением: «Изменение.xlsx». Необходимо произвести отделение начальной части строки с именем файла (до точки) без расширения .xlsx.

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

  • ПРАВСИМВ – функция, которая возвращает заданное число последних знаков текстовой строки;
  • ПОИСК – функция, находящая первое вхождение одной текстовой строки в другой и возвращающая начальную позицию найденной строки.

Полученные результаты проиллюстрированы на рисунке 2.

Рисунок 2 – Результат выведения.

Логическая формула для функции ДЛСТР в условном форматировании

Пример 3. Среди имеющегося набора текстовых данных в таблице Excel необходимо осуществить выделение тех ячеек, количество символов в которых превышает 12.

Исходные данные приведены в таблице 1:

Исходная строка
Добрый день, класс. Я ваш новый ученик
Добрый день, класс.
Добрый день
Я ваш учитель
Я ваш

Решение данной задачи производится путем создания правила условного форматирования. На вкладке «Главная» в блоке инструментов «Стили» выбираем «Условное форматирование», в выпадающем меню указываем на опцию «Создать правило» (вид окна показан на рисунке 3).

Рисунок 3 – Вид окна «Создать правило».

В окне в блоке «Выберите тип правила» выбираем «Использовать формулу для определения форматируемых ячеек», в следующем поле вводим формулу: =ДЛСТР(A2)>12, после чего нажимаем кнопку формат и задаем необходимый нам формат выбранных полей. Ориентировочный вид после заполнения данного окна показан выше на рисунке.

После этого нажимаем на кнопку «Ок» и переходим в окно «Диспетчер правил условного форматирования» (рисунок 4).

Рисунок 4 – Вид окна «Диспетчер правил условного форматирования».

В столбце «Применяется к» задаем необходимый нам диапазон ячеек с исходными данными таблицы и нажимаем кнопку «Ок». Полученный результат приведен на рисунке 5.

Рисунок 5 – Окончательный результат.

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

Функции VBA для работы с текстом

  • ASC ()— эта функция позволяет вернуть числовой код для переданного символа. Например, ASC( «D» ) вернет 68. Эту функцию удобно использовать для того, чтобы определить следующую или предыдущую букву. Обычно она используется вместе с функцией Chr(), которая производит обратную операцию — возвращает символ по переданному его числовому коду.Варианты этой функции — AscB() и AscW():
    • AscB () — возвращает только первый байт числового кода для символа.
    • AscW () — возвращает код для символа в кодировке Unicode
  • Chr () — возвращает символ по его числовому коду. Может использоваться в паре с функцией Asc(), но чаще всего её применяют, когда нужно вывести служебный символ (например кавычки — » ), т.к. кавычки просто так в VBA-коде не ввести(нужно ставить двойные). Я обычно именно эту функцию и использую.

    Dim sWord As String sWord = Chr(34) & «Слово в кавычках» & Chr(34)

    Есть варианты этой функции — ChrB() и ChrW(). Работают аналогично таким же вариантам для функции Asc().
    InStr () и InStrRev ()— одна из самых популярных функций. Позволяет обнаружить в теле строковой переменной символ или последовательность символов и вернуть их позицию. Если последовательность не обнаружена, то возвращается 0.

    Dim sStr As String sStr = «w» If InStr(1, «Hello, World!», sStr, vbTextCompare) > 0 Then MsgBox «Искомое слово присутствует!» Else MsgBox «Искомое слово отсутствует!» End If

    Разница функций в том, что InStr() ищет указанное слово от начала строки, а InStrRev() с конца строки
    Left (), Right (), Mid ()— возможность взять указанное вами количество символов из существующей строковой переменной слева, справа или из середины соответственно.

    Dim sStr As String sStr = «Hello, World!» MsgBox Mid(sStr, 1, 5)

    Dim sStr As String sStr = «Hello, World!» MsgBox Replace(sStr, «Hello», «Bay»)

    Dim sStr As String sStr = «Hello, World!» MsgBox StrConv(«Hello, World!», vbUpperCase)

    В качестве второго параметра параметра могут применяться константы:

    • vbUpperCase: Преобразует все текстовые символы в ВЕРХНИЙ РЕГИСТР
    • vbLowerCase: Преобразует все текстовые символы в нижний регистр
    • vbProperCase: Переводит первый символ каждого слова в Верхний Регистр
    • *vbWide: Преобразует символы строки из однобайтовых в двухбайтовые
    • *vbNarrow: Преобразует символы строки из двухбайтовых в однобайтовые
    • **vbKatakana: Преобразует символы Hiragana в символы Katakana
    • **vbHiragana: Преобразует символы Katakana в символы Hiragana
    • ***vbUnicode: Преобразует строку в Юникод с помощью кодовой страницы системы по умолчанию
    • ***vbFromUnicode: Преобразует строку из Юникод в кодовую страницу системы по умолчанию

    * применимо для локализацией Дальнего востока
    ** применимо только для Японии
    *** не поддерживается операционными системами под управлением Macintosh

  • StrReverse () — «перевернуть» строку, разместив ее символы в обратном порядке. Функция работает только начиная от Excel 2000 и выше. Пример использования функции, а так же иные методы переворачивания слова можно посмотреть в этой статье: Как перевернуть слово?
  • Tab () — еще одна функция, которая используется для форматирования вывода на консоль. Размножает символы табуляции в том количестве, в котором вы укажете. Если никакое количество не указано, просто вставляет символ табуляции. Для вставки символа табуляции в строковое значение можно также использовать константу vbTab.
  • String () — позволяет получить строку из указанного количества символов (которые опять-таки указываются Вами). Обычно используются для форматирования вывода совместно с функцией Len().
  • Склеивание текста по условию

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

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

    Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию — аналог функции СУММЕСЛИ (SUMIF) , но для текста.

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

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

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

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

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

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

    Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы — Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:

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

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

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

    • звездочка (*) — обозначает любое количество любых символов (в т.ч. и их отсутствие)
    • вопросительный знак (?) — обозначает один любой символ
    • решетка (#) — обозначает одну любую цифру (0-9)

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

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

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

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

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

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

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

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

    Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в «умную». Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная — Форматировать как таблицу (Home — Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):

    Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data — From Table) :

    В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке — Все строки (All Rows) :

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

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

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

    Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку — Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная — Закрыть и загрузить (Home — Close and load) :

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

    Ссылка на основную публикацию
    Adblock
    detector