Функция пропнач в excel как пользоваться
Программа Excel предлагает своим пользователям целых 3 функции для работы с большими и маленькими буквами в тексте (верхний и нижний регистр). Эти текстовые функции делают буквы большими и маленькими или же изменяют только первую букву в слове на большую.
Формулы с текстовыми функциями Excel
Сначала рассмотрим на примере 3 текстовых функции Excel:
- ПРОПИСН – данная текстовая функция изменяет все буквы в слове на прописные, большие.
- СТРОЧН – эта функция преобразует все символы текста в строчные, маленькие буквы.
- ПРОПНАЧ – функция изменяет только первую букву в каждом слове на заглавную, большую.
Как видно в примере на рисунке эти функции в своих аргументах не требуют ничего кроме исходных текстовых данных, которые следует преобразовать в соответствии с требованиями пользователя.
Не смотря на такой широкий выбор функций в Excel еще нужна функция, которая умеет заменить первую букву на заглавную только для первого слова в предложении, а не в каждом слове. Однако для решения данной задачи можно составить свою пользовательскую формулу используя те же и другие текстовые функции Excel:
Чтобы решить эту популярную задачу нужно в формуле использовать дополнительные текстовые функции Excel: ЛЕВСИМВ, ПРАВСИМВ и ДЛСТР.
Принцип действия формулы для замены первой буквы в предложении
Если внимательно присмотреться к синтаксису выше указанной формулы, то легко заменить, что она состоит из двух частей, соединенных между собой оператором &.
В левой части формулы используется дополнительная функция ЛЕВСИМВ:
Задача этой части формулы изменить первую букву на большую в исходной текстовой строке ячейки A1. Благодаря функции ЛЕВСИМВ можно получать определенное количество символов начиная с левой стороны текста. Функция требует заполнить 2 аргумента:
- Текст – ссылка на ячейку с исходным текстом.
- Количесвто_знаков – число возвращаемых символов с левой стороны (с начала) исходного текста.
В данном примере необходимо получить только 1 первый символ из исходной текстовой строки в ячейке A1. Далее полученный символ преобразуется в прописную большую букву верхнего регистра.
Правая часть формулы после оператора & очень похожа по принципу действия на левую часть, только она решает другую задачу. Ее задача – преобразовать все символы текста в маленькие буквы. Но сделать это нужно так чтобы не изменять первую большую букву, за которую отвечает левая часть формулы. В место функции ЛЕВСИМВ в правой части формулы применяется функция ПРАВСИМВ:
Текстовая функция ПРАВСИМВ работает обратно пропорционально функции ЛЕВСИМВ. Так же требует запыления двух аргументов: исходный текст и количество знаков. Но возвращает она определенное число букв, полученных с правой стороны исходного текста. Однако в данном случаи мы в качестве второго аргумента не можем указать фиксированное значение. Ведь нам заранее неизвестно количество символов в исходном тексте. Кроме того, длина разных исходных текстовых строк может отличаться. Поэтому нам необходимо предварительно подсчитать длину строки текста и от полученного числового значения отнять -1, чтобы не изменять первую большую букву в строке. Ведь первая буква обрабатывается левой частью формулы и уже преобразована под требования пользователя. Поэтом на нее недолжна влиять ни одна функция из правой части формулы.
Для автоматического подсчета длины исходного текста используется текстовая функция Excel – ДЛСТР (расшифроваться как длина строки). Данная функция требует для заполнения всего лишь одного аргумента – ссылку на исходный текст. В результате вычисления она возвращает числовое значение, попетому после функции =ДЛСТР(A1) отнимаем -1. Что дает нам возможность не затрагивать первую большую букву правой частью формулы. В результате функция ПРАВСИМВ возвращает текстовую строку без одного первого символа для функции СТРОЧН, которая заменяет все символы текста в маленькие строчные буквы.
В результате соединения обеих частей формулы оператором & мы получаем красивое текстовое предложение, которое как по правилам начинается с первой большой буквы. А все остальные буквы – маленькие аж до конца предложения. В независимости от длины текста используя одну и ту же формулу мы получаем правильный результат.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета 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 Excel Starter 2010 Еще. Меньше
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Для языков с двухбайтовыми наборами знаков (например, катакана) преобразует полноширинные (двухбайтовые) знаки в полуширинные (однобайтовые).
Преобразует число в текст, используя денежный формат ß (БАТ).
Возвращает символ с заданным кодом.
Удаляет из текста все непечатаемые символы.
Возвращает числовой код первого знака в текстовой строке.
Объединяет текст из нескольких диапазонов или строк, но не добавляет разделитель или аргументы IgnoreEmpty.
Объединяет несколько текстовых элементов в один.
DBCS
Для языков с двухбайтовыми наборами знаков (например, катакана) преобразует полуширинные (однобайтовые) знаки в текстовой строке в полноширинные (двухбайтовые).
Преобразует число в текст, используя денежный формат рубля.
Проверяет идентичность двух текстовых значений.
Ищет вхождения одного текстового значения в другом (с учетом регистра).
Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
Возвращают крайние слева знаки текстового значения.
Возвращают количество знаков в текстовой строке.
Преобразует все буквы текста в строчные.
Возвращают заданное число знаков из строки текста, начиная с указанной позиции.
ЧЗНАЧ
Преобразует текст в число независимо от языкового стандарта.
Извлекает фонетические (фуригана) знаки из текстовой строки.
Преобразует первую букву в каждом слове текста в прописную.
Заменяют знаки в тексте.
Повторяет текст заданное число раз.
Возвращают крайние справа знаки текстовой строки.
Ищут вхождения одного текстового значения в другом (без учета регистра).
Заменяет в текстовой строке старый текст новым.
Преобразует аргументы в текст.
Форматирует число и преобразует его в текст.
Объединяет текст из нескольких диапазонов или строк, вставляя между текстовыми значениями указанный разделитель. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны.
Удаляет из текста пробелы.
ЮНИСИМВ
Возвращает символ Юникод, на который ссылается заданное числовое значение.
UNICODE
Возвращает число (кодовую страницу), которая соответствует первому символу текста.
Преобразует все буквы текста в прописные.
Преобразует текстовый аргумент в число.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Простейшие примеры применения функции ПРОПИСН
Раздел функций | Текстовые |
Название на английском | UPPER |
Волатильность | Не волатильная |
Похожие функции | СТРОЧН, ПРОПНАЧ |
Похожий функционал !SEMTools | Изменить регистр букв |
Что делает эта функция?
Эта функция позволяет сделать строчные (маленькие) буквы прописными (заглавными), иными словами, перевести их в верхний регистр. Символы в верхнем регистре и символы, для которых это неприменимо, не изменяются.
Синтаксис
Форматирование
Примеры
Допустим, наша задача — сделать в каждой ячейке первую букву заглавной, а остальные строчными. Исходная таблица дана полностью заглавными буквами.
Функция ПРОПНАЧ не решит задачу, т.к. сделает каждое слово с заглавной буквы.
Поэтому используем составную формулу, поможет в первую очередь функция ПСТР. Она извлечет первую букву по ее позиции, ее сделаем заглавной, а остальные — строчными.
Позицию первой буквы можно получить разными способами. В простейшем случае, допуская, что первый символ в строке всегда буква, поможет не только ПСТР, но и функция ЛЕВСИМВ:
Все буквы кроме первой нам также поможет извлечь ПСТР (здесь 1000 — число, заведомо превышающее возможное количество символов в ячейке):
Ну а итоговая формула будет составлена из вышеуказанных с обработкой функциями СТРОЧН и ПРОПИСН:
Как видно из примера, эта комбинация не всегда дает тот результат, который хотелось бы получить, работая с форматированным текстом (подсвечено желтым)
Ведь фактически она не делает заглавными первые буквы ячеек, а делает заглавным первый символ, который не всегда бывает буквой.
А если в строке несколько предложений и мы хотим сделать заглавной первые буквы в каждом предложении? Второе и далее эта формула не определит и, следовательно, не решит задачу.
Есть ли решение? Да! Его предлагает моя надстройка !SEMTools для Excel. Читайте подробнее тут — изменить регистр букв в Excel.
Примеры применения функции ПРОПНАЧ
Раздел функций | Текстовые |
Название на английском | PROPER |
Волатильность | Не волатильная |
Похожие функции | ПРОПИСН, СТРОЧН |
Схожие функции !SEMTools | Изменить регистр |
Что делает эта функция?
Эта функция преобразует первую букву каждого слова в верхний регистр, а все последующие буквы — в нижний.
Исходный регистр букв при этом не имеет значения и не учитывается.
Разделителем слов считается любой символ, не являющийся буквой, т.е. это может быть не только пробел, но любой знак препинания, цифра или специальный символ.
Синтаксис
Форматирование
Как и все текстовые функции, функция превращает числовые значения в строковый формат числа. Дата и дата-время при этом станут натуральными числами в текстовом формате, проценты будут переведены в десятичную дробь, а финансовые значения потеряют как единицу измерения, так и характерное форматирование.
Это нужно учитывать, если при обработке больших массивов данных в ячейках помимо текстовых значений могут быть числовые.
Строковый формат на выходе легко распознается — результат применения функции в ячейке имеет выравнивание по левому краю, в отличие от чисел, которые имеют выравнивание по правому.
Пример применения. Делаем первое слово в ячейке с заглавной буквы
Как сделать первое слово с заглавной буквы в Excel? Не все слова, а только первое? В этом нам и поможет функция ПРОПНАЧ, но в комбинации с другими.
Алгоритмически задача решается в несколько простых шагов:
- Извлекаем из исходной строки первое слово
- Применяем к нему функцию ПРОПНАЧ
- Извлекаем из исходной строки оставшуюся от первого слова часть (иными словами, нужно удалить первое слово из ячейки и посмотреть на результат).
- Объединяем эти два фрагмента
Первый шаг подробно описан тут в примере 1:
А формула для ячейки A1 будет такой:
Вторым шагом мы применим функцию ПРОПНАЧ к этому выражению:
Третий шаг — тут:
Здесь формула будет такой:
Ну а четвертый шаг — объединение этих выражений — можно совершить с помощью символа амперсанда:
Но, к сожалению, эта формула еще не идеальна и не будет работать, если в ячейке всего одно слово (выдаст ошибку поиск пробела). Поэтому нам придется и ее обернуть функцией ЕСЛИОШИБКА и применить обычную функцию ПРОПНАЧ в случае такой ошибки:
Идеальна ли эта формула для всех случаев? К сожалению, нет, т.к. ей присуща нехорошая особенность функции ПРОПНАЧ — она изменяет регистр невзирая на исходный.
Например, если первое слово является аббревиатурой и все буквы в нем заглавные, функция изменит в аббревиатуре регистр всех букв, кроме первой, на нижний.
Есть ли решение этой проблемы? Да, его предлагает моя надстройка !SEMTools для Excel.
Подробнее о разных операциях и возможностях надстройки смотрите тут — изменить регистр букв в Excel.
Примеры взаимодействия функции ДЛСТР с разными текстовыми и числовыми значениями в ячейках
Раздел функций | Текстовые |
Название на английском | LEN |
Волатильность | Не волатильная |
Похожие функции | СЧЁТЗ |
Что делает функция ДЛСТР?
Функцию часто называют «Длина строки», что не совсем корректно, потому что речь не о строке, а о строковом выражении значения аргумента.
Эта функция позволяет посчитать количество символов, включая пробелы и цифры, в этом самом строковом выражении.
Есть символ, который функция ДЛСТР не учитывает. Это символ апострофа (‘), когда он в начале ячейки. Если в начале ячейки идут два символа апострофа подряд, второй уже учитывается.
Почему так происходит? Дело в том, что апостроф в начале ячейки автоматически воспринимается Excel как служебный символ, переводящий формат ячейки в текстовый.
Синтаксис
Аргумент у функции один, он обязательный. В нем можно указывать как одну ячейку, так и другие вычисления с помощью формул. При использовании в функциях массива аргументом может быть диапазон ячеек.
Форматирование
Если входящее значение не в текстовом формате, оно конвертируется в него.
Ячейки в формате даты воспринимаются как числа, поэтому содержат обычно 5 знаков (если это не даты, близкие к 1900 году).
Процентный формат также конвертируется в натуральное число, например, 2500% = 25 (2 символа, см. таблицу примеров выше).
Примеры применения ДЛСТР
Функция часто используется как вспомогательная в составных формулах, в комбинации с другими текстовыми функциями.
При этом эти комбинации позволяют решать самые необычные задачи.
Пример 1 — посчитать слова
Сколько слов в ячейке? Обычно на 1 больше, чем пробелов между ними. Если пробел один, значит слов — два, если два — то слов три, и так далее.
Если есть вероятность, что пробелов больше 1 между словами, или они есть в начале и конце ячейки, их можно устранить функцией СЖПРОБЕЛЫ.
Пробелы в строке можно посчитать, измерив длину строки в символах:
- с ними,
- без них,
- и вычислив разницу.
Как получить строку без пробелов? Тут поможет функция ПОДСТАВИТЬ.
Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. СЖПРОБЕЛЫ удаляет лишние пробелы, ПОДСТАВИТЬ удаляет их, а ДЛСТР измеряет длину строк:
Пример 2 — посчитать встречаемость фрагмента в тексте
Есть ли в тексте определенный символ? И если есть, то сколько их?
Рассмотрим пару примеров формул.
Есть ли в ячейке лишние пробелы
Формула ниже проверит, есть ли в ячейке лишние пробелы. Это удобно, когда нужно не просто удалить их, а узнать, были ли они вообще:
Формула проверки наличия лишних пробелов в строке
Формула очевидна и довольно проста. Если длина ячейки после удаления лишних пробелов функцией СЖПРОБЕЛЫ равна исходной — значит, их в ней нет, а если наоборот, не равна — есть.
Обратите внимание, что, несмотря на то, что в самой постановке вопроса звучит условие ЕСЛИ, функция ЕСЛИ тут не нужна. Если равенство верное, Excel сам вставит в ячейку результат ИСТИНА, и ЛОЖЬ, если наоборот.
Сколько раз встречается символ или слово в тексте
По некой аналогии с предыдущим примером, мы можем производить не булевое (ДА-НЕТ, ИСТИНА-ЛОЖЬ) сравнение длин строки перед и после удаления символа или фрагмента, а вычислить разницу.
Если мы ищем один символ, это позволит сразу понять его встречаемость.
Для удаления произвольного символа из строки нам понадобится функция ПОДСТАВИТЬ.
Подсчет встречаемости буквы в ячейке
Если же текстовый фрагмент длиной 2 и более символов — нам потребуется еще и поделить полученный результат на его длину:
В данной формуле исходный текст в ячейке A1, а подсчитываемый фрагмент — в ячейке B1.
Подсчет встречаемости текстового фрагмента
Обратите внимание на 5 и 6 строки — поскольку функция «ПОДСТАВИТЬ» чувствительна к регистру, из текста ею не было удалено первое вхождение. Если нужно учитывать вхождения в любом регистре, могут понадобиться функции СТРОЧН или ПРОПИСН.
Подсчитываемый текстовый фрагмент может быть любой длины.
Еще момент — функция может быть непригодна для подсчета коротких слов, т.к. одно слово может быть частью другого.
Пример 3 — извлечь или удалить последнее слово
Зная количество пробелов в тексте, можно заменить последний из них на редкий символ, чтобы потом вычислить его позицию.
С этим нам, как и в предыдущем примере, поможет функция ПОДСТАВИТЬ — она позволяет заменить не только все определенные текстовые фрагменты в строке, но и определенное по порядку вхождение (первое, второе и т.д.).
Что это нам дает? Последний пробел — не что иное, как символ, отделяющий последнее слово от остальной части строки. И по его позиции далее уже нехитрыми манипуляциями удалить или извлечь его.
Подробно пример рассмотрен здесь: Удалить последнее слово в Excel
Пример 4 — удалить первые N символов / первое слово
Нехитрая комбинация функций ПРАВСИМВ, ПОИСК и ДЛСТР позволяет удалить из ячейки первое слово.
- вычисляем позицию первого пробела функцией ПОИСК,
- вычитаем из длины строки (ДЛСТР) это число
- Извлекаем функцией ПРАВСИМВ полученную разницу из исходной строки
Подробнее пример рассмотрен в статье про функцию ПРАВСИМВ.
Если же количество символов уже известно заранее, формула еще проще — нужны только ДЛСТР и ПРАВСИМВ:
Удаляем из строки первые 4 символа функциями ДЛСТР и ПРАВСИМВ
Пример 5 — замена последнего символа в ячейке
Как бы ни звучало очевидно, позиция последнего символа в строке равна ее длине в символах. А поэтому, зная длину строки, можно заменить последний символ в ней, взяв на 1 символ меньше с помощью ЛЕВСИМВ, и конкатенации с нужным текстом через амперсанд:
Или с помощью функции ЗАМЕНИТЬ, которая на вход возьмет длину строки как позицию заменяемого символа:
Пример 6 — в формуле массива
Как посчитать разом все символы во всех ячейках столбца или диапазона?
Здесь поможет формула массива на основе комбинации функции ДЛСТР и функции СУММ. Первая создаст массив значений длины каждой из ячеек диапазона, а вторая просуммирует эти значения:
Функция ДЛСТР в формуле массива
Аналогичным способом функциями МАКС и МИН можно посчитать длины самой длинной и самой короткой строк в диапазоне.
Читайте также: