Производительность

Советы по работе с текстом и текстовыми функциями в Excel

Советы по работе с текстом и текстовыми функциями в Excel

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

Откройте БЕСПЛАТНУЮ шпаргалку «Essential Excel Formulas» прямо сейчас!

Это подпишет вас на нашу рассылку

Введите адрес электронной почты

Сегодня мы рассмотрим различные способы работы с текстом в таблицах Excel. Мы обсудим несколько различных функций для работы с текстом. Всякий раз, когда вы используете функцию, всегда начинайте ее со знака равенства (=).

Обернуть текст в ячейку

Когда вы вводите текст в ячейку, которая шире ячейки, по умолчанию текст проходит за правую границу ячейки. Но текст легко переносится по ширине столбца. Обтекание текстом корректируется автоматически при изменении ширины ячейки.

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

текстовые функции Excel - нажмите кнопку «Обтекание текстом»

Текст переносится в ячейку, и высота строки автоматически подстраивается под размер текста.

текстовые функции Excel - текст автоматически переносится в ячейку

Если высота строки не регулируется автоматически, строка может быть установлена ​​на определенную высоту.

Чтобы автоматически подогнать высоту строки к тексту, выберите ячейку. Затем нажмите « Формат» в разделе « Ячейки » на вкладке « Главная » и выберите « Автоматическая установка высоты строки» .

Если вы хотите, чтобы строка была на определенной высоте, вы можете изменить ее, чтобы убедиться, что обернутый текст подходит. Выберите « Высота строки» в меню « Формат» . Затем введите высоту строки в диалоговом окне « Высота строки » и нажмите « ОК» .

текстовые функции Excel - AutoFit Row Height

Вы также можете перетащить нижнюю границу строки вниз, пока все строки текста не поместятся в ячейке.

Введите разрыв строки в ячейке

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

Дважды щелкните ячейку, чтобы редактировать текст, или нажмите F2 . Щелкните мышью в той части текста, где вы хотите вставить разрыв строки. Затем нажмите Alt + Enter .

текстовые функции Excel - введите разрыв строки в ячейке

Высота строки корректируется по размеру текста, если для параметра « Формат» задано значение « Высота строки для автоподбора» в разделе « Ячейки » на вкладке « Главная ».

текстовые функции Excel - результат ручного переноса строки

Подсчет клеток, содержащих любой текст

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

Общей формой функции COUNTIF для подсчета любого количества текстовых символов является:

=COUNTIF(cellrange,"*") 

Cellrange представляет любой диапазон ячеек, таких как B2: B9. Звездочка между кавычками — это подстановочный знак, представляющий любое соответствующее количество текстовых символов. Есть несколько моментов, которые следует помнить о том, что считать текстовыми символами:

  • Логические значения TRUE и FALSE не считаются как текст.
  • Числа, введенные в виде текста, учитываются в подстановочном знаке (*).
  • Пустая ячейка, которая начинается с апострофа (‘), считается.

Например, чтобы подсчитать количество ячеек, содержащих текст в диапазоне ячеек A2: G9 на следующем листе, мы вводим «= COUNTIF («. Затем, чтобы ввести диапазон ячеек, мы выбираем ячейки, которые мы хотим включить в число ,

Функция COUNTIF не чувствительна к регистру.

текстовые функции Excel - выбор диапазона для функции COUNTIF

Затем мы вводим запятую (,) и подстановочный знак (*) в двойных кавычках.

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

Текстовые функции Excel - Подсчет ячеек с любым текстом

Подсчет клеток, содержащих определенный текст

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

Универсальная функция для подсчета вхождений определенной строки текстовых символов:

 =COUNTIF(cellrange,"txt") 

Как и в предыдущем разделе, диапазон ячеек представляет любой диапазон ячеек, таких как B2: B9. Мы помещаем строку текстовых символов, которые мы хотим найти между двойными кавычками.

Например, чтобы подсчитать количество ячеек, содержащих «Карандаш» в диапазоне ячеек A2: G9 на следующем листе, мы вводим следующую функцию:

 =COUNTIF(A2:G9,"Pencil") 

Это находит все ячейки, содержащие только слово «Карандаш» без другого текста в ячейке. Поскольку функция COUNTIF не чувствительна к регистру, она найдет все ячейки, содержащие «Карандаш» или «Карандаш».

Текстовые функции Excel - Подсчет ячеек с определенным текстом

Функция COUNTIFS позволяет подсчитывать ячейки с текстом, но исключать ячейки с определенными текстовыми символами.

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

 =COUNTIFS(A2:G9,"*",A2:G9,"<>Pencil") 

Для функции COUNTIFS вы сначала указываете диапазон и текст, который хотите найти. Затем дайте ему тот же диапазон снова и текст, который вы хотите исключить. «<>» Используется для исключения любого последующего текста.

Текстовые функции Excel - Подсчет ячеек с текстом, кроме определенного текста

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

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

 =COUNTIF(A2:G9,"*j*") 

Опять же, поскольку функция COUNTIF не чувствительна к регистру, будут учитываться ячейки, содержащие «j» или «J».

Текстовые функции Excel - Подсчет ячеек с определенным текстом в любой позиции

Преобразовать текст в числа

Если у вас много ячеек, содержащих числа, хранящиеся в виде текста, есть несколько способов преобразовать текст в числа.

Вы можете сказать, отформатировано ли число как текст, когда выровнено по левому краю в ячейке, а не по правому краю. Кроме того, если число было принудительно отформатировано как текст с использованием апострофа (‘) в начале, в левом верхнем углу ячейки будет зеленый треугольник.

Для преобразования текста в числа можно использовать параметр « Преобразовать в число», функцию « Текст в столбцы» или « Специальную вставку» . Мы обсудим каждый из этих методов в нашей статье о извлечении чисел и текста в Excel.

текстовые функции Excel - выберите Преобразовать в число в столбце

Преобразовать число в текст

Могут быть случаи, когда вы хотите сохранить числа в виде текста. Возможно, вы выполняете действие с диапазоном ячеек, и есть определенные ячейки, которые вы не хотите читать как числа, даже если они есть.

Ввод апострофа (‘) в начале числа превращает его в текст. Но если у вас много ячеек с номерами, которые вы хотите преобразовать в текст, вы можете использовать функцию TEXT.

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

 =TEXT(B2,"0") 

Вы даете функции ссылку на ячейку для конвертируемого числа, а затем формат числа, который вы хотите. Мы просто конвертируем в число без специального форматирования (например, не валюта или дата). Поэтому мы используем «0» (ноль).

Использование функции автозаполнения автозаполнение автозаполнение чтобы скопировать функцию TEXT для остальных ячеек. Числа становятся текстовыми и выровнены по левому краю.

Вы можете скопировать и вставить преобразованные значения в исходный столбец. Выберите ячейки, содержащие функцию TEXT, и нажмите Ctrl + C, чтобы скопировать их. Выберите первую ячейку в исходном столбце. На вкладке « Главная » нажмите стрелку на кнопке « Вставить» и перейдите к « Специальная вставка»> «Значения» .

Примеры различных форматов текста, доступных для использования в функции TEXT на сайте поддержки Microsoft .

текстовые функции Excel - Использование функции TEXT

Преобразовать текст в дату

Вы когда-нибудь получали рабочую книгу от кого-то другого, в котором они вводили даты в виде текста, чисел или в формате, не распознаваемом как даты? Вы можете конвертировать текст в даты, используя функцию DATE.

Вот общий формат функции DATE:

 =DATE(year,month,day) 

Для года, месяца и дня мы будем использовать строковые функции LEFT, MID и RIGHT, чтобы извлечь соответствующие части текста или числа, которое мы хотим преобразовать. Мы объясним четыре примера на изображении ниже.

Чтобы преобразовать «20171024» в ячейке C2 в дату, мы использовали функцию ВЛЕВО, чтобы извлечь первые четыре символа года (2017). Затем мы использовали функцию MID для извлечения двух символов, начиная с пятой позиции, как месяц (10). Наконец, мы использовали функцию RIGHT, чтобы извлечь последние два символа как день (24).

 =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)) 

Следующий пример, «2102018» в ячейке C3, находится в другом порядке. Мы по-прежнему используем строковые функции, но в другом порядке. Мы использовали функцию RIGHT, чтобы извлечь последние четыре символа за год (2018). В этом случае месяц — только одна цифра, поэтому мы использовали функцию ВЛЕВО, чтобы извлечь первый символ в качестве месяца (2). Наконец, мы использовали функцию MID для извлечения двух символов, начиная со второй позиции как день (10).

 =DATE(RIGHT(C3,4),LEFT(C3,1),MID(C3,2,2)) 

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

 =DATE(RIGHT(C4,4),MID(C4,4,2),LEFT(C4,2)) 

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

 =DATE(RIGHT(C5,4),LEFT(C5,2),MID(C5,4,2)) 

текстовые функции Excel - конвертировать текст в даты

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

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

Объединить текст из нескольких ячеек

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

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

Для этого мы используем функцию CONCATENATE помогут «Конкатенировать» просто означает «объединять» или «объединять». Эта функция позволяет объединять текст из разных ячеек в одну ячейку. Вы также можете добавить любой другой текст к тексту из других ячеек.

Чтобы объединить Фамилию и Имя в одной строке в столбце Полное имя , мы используем функцию CONCATENATE следующим образом:

 =CONCATENATE(B2," ",A2) 

Дайте функции CONCATENATE текст, который нужно объединить в том порядке, в котором вы хотите, чтобы он сложился. Таким образом, мы дали функции Имя (B2), пробел в двойных кавычках (”“), затем Фамилия (A2).

Мы также можем создать адрес электронной почты таким же образом. Мы используем Имя (B2), Фамилию (A2), а затем оставшуюся часть адреса электронной почты (@ email.com) в двойных кавычках.

 =CONCATENATE(B2,A2,"@email.com") 

Всегда помещайте любой конкретный текст в двойные кавычки, но не ставьте кавычки вокруг ссылок на ячейки.

текстовые функции Excel - объединяют текст из нескольких ячеек

Разделяйте текст на несколько ячеек

У вас есть несколько ячеек со смешанным форматом контента, которые вы хотите разделить? Например, если у вас есть ячейка, содержащая «14 бутербродов с индейкой», вы можете разделить ее на число (14) и текст (бутерброды с индейкой). Таким образом, вы можете использовать число в функциях и формулах.

Чтобы получить число из «14 бутербродов с индейкой», мы используем функцию LEFT.

 =LEFT(B2,SEARCH(" ",B2, 1)) 

Сначала мы даем функции ссылку на ячейку для текста, из которого мы хотим извлечь число (B2). Затем мы используем функцию ПОИСК, чтобы найти первый пробел после первого символа в строке.

Чтобы получить текст из «14 бутербродов с индейкой», мы используем правую строковую функцию.

 =RIGHT(B2,LEN(B2)-SEARCH(" ", B2, 1)) 

Сначала мы даем функции RIGHT ссылку на ячейку, из которой мы хотим извлечь текст (B2). Затем мы используем функции LEN и SEARCH, чтобы определить, сколько символов справа мы хотим получить. Мы вычитаем количество символов из первого пробела после первого символа в строке до конца строки из общей длины строки.

текстовые функции Excel - разделение текста на несколько ячеек

Подробнее о работе с текстовыми функциями в Excel

Иногда в таблице, над которой вы работаете, слишком много текста. Это поможет вам упростить это.

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

Похожие посты
Производительность

Excel против Google Sheets: какой из них лучше для вас?

Производительность

Как улучшить управление документами с помощью свойств в Microsoft Office

ИнтернетПроизводительность

5 инструментов для извлечения изображений из файлов PDF

AndroidПроизводительность

Adobe Reader X добавляет защищенный режим для пользователей Windows, Android получает новые функции [Новости]