Ребята, всем привет! 👋 Продолжаем серию уроков посвященных функциям Excel. Так как помимо анализа числовых данных, так же встречаются и задачи по обработке текстовых данных, то сегодня в уроке рассмотрим 11 основных текстовых функции применяемых в Excel.
1. ФУНКЦИЯ СЦЕПИТЬ()
Объединить данные из нескольких ячеек в одну, можно двумя способами: используя функцию СЦЕПИТЬ или оператор сцепки &:
📚 Синтаксис функции:
- СЦЕПИТЬ(Текст1;Текст2;) – объединение несколько текстовых строк в одну.
- CONCATENATE(Text1;Text2;)
📝 ПРИМЕР: Объединить для каждого сотрудника Фамилию Имя Отчество.
=СЦЕПИТЬ(A2;" ";B2;" ";C2)- – объединение фамилии (ячейка А2) с именем (ячейка В2) и отчеством (ячейка С2) с разделителями – пробелами " ".
⚠ Альтернативный способ объединения текстовых ячеек с разделителями пробелами " " по формуле: =A2&" "&B2&" "&C2.
2. ФУНКЦИЯ ОБЪЕДИНИТЬ()
Если нужно объединить данные с каким-то одним разделителем, то в Excel 2019 появилась функция ОБЪЕДИНИТЬ.
📚 Синтаксис функции:
- ОБЪЕДИНИТЬ(Разделитель;Пропускать_пустые;Текст1;[Текст2];…) – объединение списка или диапазона строк текста с помощью разделителя.
- TEXTJOIN(Delimeter;Ignore_empty;Text1;[Text2];…)
Описание аргументов:
👉 Разделитель [Delimeter] – символ или строка, которые необходимо вставлять между всеми текстовыми аргументами.
👉 Пропускать пустые [Ignore_empty] – выбор что делать с пустыми ячейками: ИСТИНА [TRUE] – пропускаются, ЛОЖЬ [FALSE] - присутствуют.
👉 Текст1 [Text1] – объединяемые текстовые строки или диапазоны (от 1 до 252).
В условиях предыдущего примера формула будет иметь вид:
=ОБЪЕДИНИТЬ(" ";ИСТИНА;A2:C2)
3. ФУНКЦИЯ СЦЕП()
В 2019 версии Excel для объединения нескольких текстовых строк без какого-либо разделителя, можно использовать функцию СЦЕП.
📚 Синтаксис функции:
- СЦЕП(Текст1;) – объединение несколько текстовых строк в одну.
- CONCAT(Text1;)
Например, =СЦЕП(A2:C2) – объединит данных серии (ячейки А2 и В2) и номера (ячейка С2).
4. ФУНКЦИЯ СЖПРОБЕЛЫ()
📚 Синтаксис функции:
- СЖПРОБЕЛЫ(Текст) – удаляет из текста лишние пробелы, кроме одиночных между словами.
- TRIM(Text)
📝 ПРИМЕР: Удалить лишние пробелы
Решение: =СЖПРОБЕЛЫ($A2&" "&$B2&" "&$C2)
🔔 К сожалению в Excel нет команды изменения регистра для текстовых данных, НО существуют функции, которые помогают решить эту проблему. Это функции:
- ПРОПИСН()
- СТРОЧН()
- ПРОПНАЧ()
5. ФУНКЦИЯ ПРОПИСН()
📚 Синтаксис функции:
- ПРОПИСН(Текст) – делает все буквы в строке текста прописными.
- UPPER(Text)
📝 Например, =ПРОПИСН($A2&" "&$B2&" "&$C2)
6. ФУНКЦИЯ СТРОЧН()
📚 Синтаксис функции:
- СТРОЧН(Текст) – делает все буквы в строке текста строчными.
- LOWER(Text)
📝 Например, =СТРОЧН($A2&" "&$B2&" "&$C2)
7. ФУНКЦИЯ ПРОПНАЧ()
📚 Синтаксис функции:
- ПРОПНАЧ(Текст) – делает прописную первую букву в каждом слове текста, преобразуя все другие буквы в строчные.
- PROPER(Text)
📝 Например, =ПРОПНАЧ($A2&" "&$B2&" "&$C2)
🔔 При необходимости можно из ячейки извлечь необходимые символы. В зависимости от расположения, используются функции:
- ЛЕВСИМВ
- ПРАВСИМВ
- ПСТР
8. ФУНКЦИЯ ЛЕВСИМВ()
📚 Синтаксис функции:
- ЛЕВСИМВ(Текст;Количество_знаков) – извлекает указанное количество символов с начала строки текста.
- LEFT(Text; Num_chars)
📝 Например, =ЛЕВСИМВ(B2;1)
Применение данной функции поможет при необходимости указать Фамилию и инициалы сотрудника:
9. ФУНКЦИЯ ПРАВСИМВ()
📚 Синтаксис функции:
- ПРАВСИМВ(Текст;Количество_знаков) – извлекает указанное количество символов с конца строки текста.
- RIGHT(Text;Num_chars)
📝 Например, =ПРАВСИМВ(ГОД(G2);4)
10. ФУНКЦИЯ ПСТР ()
📚 Синтаксис функции:
- ПСТР(Текст;Начальная_позиция;Количество_знаков) – возвращает заданное число символов из строки текста, начиная с указанной позиции.
- MID(Text;Start_num;Num_chars)
📝 Например, =ПСТР(E2;7;5)
🔔 Применение функции ПСТР позволяет решать многие задачи.
📝 Например, разделение Фамилии Имени и Отчества по столбцам:
=ПСТР(A2;1;НАЙТИ(" ";A2;1)-1)
=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-НАЙТИ(" ";A2;1))
=ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)+1;50)
11. ФУНКЦИЯ ЗНАЧЕН ()
Результатом обработки текстовых функций являются данные с текстовым типом данных.
⚠ Существует 3 способа преобразовать текстовый аргумент в числовой:
- воспользоваться функцией ЗНАЧЕН()
- умножить полученный результат на 1
- в начале формулы поставить --
📚 Синтаксис функции:
- ЗНАЧЕН(Текст) – преобразует текстовый аргумент в число.
- VALUE(Text)
📝 Например, =ЗНАЧЕН((ПСТР(E2;3;3)))
Мгновенное заполнение.
В завершении урока хочу упомянуть о еще одной функции Excel - это Мгновенное заполнение.
В большинстве случаев задачи можно решить с помощью инструмента Мгновенное заполнение.
Мгновенное заполнение поможет там, где нужно быстро выполнить простую операцию с текстом без дальнейшей связи с исходными данными. Например, быстро объединить данные из нескольких ячеек, получить нужное количество символов, изменить регистр текста.
Впервые инструмент появился в 2013 версии.
📝 Как использовать функцию Мгновенное заполнение:
- Ввести в первую ячейку текст-шаблон для заполнения (например, Иванов И.И.)
- Выделить ячейку содержащую текст-шаблон
- На вкладке Данные [Data], в группе в группе Работа с данными [Data
- Tools] выбрать Мгновенное заполнение [Flash Fill]
Получаем такой результат. Остается только "Принять предложение":
На этом сегодня все. Продолжение следует...
В следующих уроках более подробно рассмотрим:
☑ функции для работы с датами
А так же:
☑ Создание условия с использованием формулы
☑ Защита ячеек, листов и рабочих книг Excel
☑ Установка ограничений на ввод данных
☑ Поиск неверных данных и др.
#excel #функции excel #текстовые функции excel #мгновенное заполнение excel #обучение excel #фишки excel #решение excel #вопросы excel #примеры excel