Экономия времени с помощью текстовых операций в Excel

  = ЗНАК ([текст]) 

  = КОД ([текст]) 

В этом случае [текст] является символом или строкой. И если вам нужна ссылка на Unicode вместо имени по умолчанию на вашем компьютере, вы будете использовать UNICODE (опять же, если у вас Excel 2013 или более поздняя версия).

Функции анализа текста

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

Функция ЛЕН

LEN — очень простая функция: она возвращает длину строки. Так что, если вам нужно посчитать количество букв в куче разных ячеек, это путь. Вот синтаксис:

  = LEN ([текст]) 

Аргумент [text] — это ячейка или ячейки, которые вы хотите сосчитать. Ниже вы можете видеть, что при использовании функции LEN в ячейке, содержащей название города «Остин», возвращается 6. При использовании в названии города «Саут-Бенд» возвращается 10. Пробел считается как символ с LEN , так что имейте это в виду, если вы используете его для подсчета количества букв в данной ячейке.

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

Функция НАЙТИ

Вы можете задаться вопросом, зачем вам использовать функцию FIND, если вы можете просто использовать CTRL + F или Edit> Find. Ответ заключается в специфике поиска с помощью этой функции; вместо поиска по всему документу вы можете выбрать, с какого символа каждой строки начинается поиск. Синтаксис поможет прояснить это запутанное определение:

  = НАЙТИ ([find_text], [inside_text], [start_num]) 

[find_text] это строка, которую вы ищете. [inside_text] — это ячейка или ячейки, в которых Excel будет искать этот текст, а [start_num] — первый символ, на который он будет смотреть. Важно отметить, что эта функция чувствительна к регистру. Давайте возьмем пример.

Я обновил пример данных, чтобы идентификационный номер каждого учащегося представлял собой шестизначную буквенно-цифровую последовательность, каждая из которых начинается с одной цифры, M — «мужской», последовательность из двух букв, обозначающая уровень успеваемости учащегося. (HP для высокого, SP для стандартного, LP для низкого и UP / XP для неизвестного), и окончательная последовательность из двух чисел. Давайте использовать FIND, чтобы выделить каждого ученика с высокими показателями. Вот синтаксис, который мы будем использовать:

  = НАЙТИ ("HP", A2, 3) 

Это скажет нам, появится ли HP после третьего символа ячейки. Применительно ко всем ячейкам в столбце ID можно сразу увидеть, был ли ученик высокопроизводительным или нет (обратите внимание, что 3, возвращаемая функцией, является символом, при котором обнаруживается HP). FIND может быть лучше использован, если у вас есть более широкий спектр последовательностей, но вы поняли идею.

Как и в случае LEN и LENB, FINDB используется для той же цели, что и FIND, только с двухбайтовыми наборами символов. Это важно из-за спецификации определенного персонажа. Если вы используете DBCS и указали четвертый символ с помощью FIND, поиск начнется со второго символа. FINDB решает проблему.

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

ТОЧНАЯ функция

Если вам нужно сравнить два значения, чтобы увидеть, совпадают ли они, EXACT — это функция, которая вам нужна. Когда вы поставите EXACT с двумя строками, он вернет TRUE, если они абсолютно одинаковы, и FALSE, если они разные. Поскольку EXACT чувствителен к регистру, он вернет FALSE, если вы передадите ему строки, которые читают «Test» и «test». Вот синтаксис для EXACT:

  = ТОЧНО ([текст1], [текст2]) 

Оба аргумента довольно очевидны; это строки, которые вы хотели бы сравнить. В нашей таблице мы будем использовать их для сравнения двух баллов SAT. Я добавил вторую строку и назвал ее «Сообщено». Теперь мы пройдемся по электронной таблице с помощью EXACT и посмотрим, чем отчетная оценка отличается от официальной оценки, используя следующий синтаксис:

  = EXACT (G2, F2), 

Повторение этой формулы для каждой строки в столбце дает нам это:

Функции преобразования текста

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

Функция ТЕКСТ

TEXT преобразует числовые данные в текст и позволяет форматировать их определенным образом; это может быть полезно, например, если вы планируете использовать данные Excel в документе Word . Давайте посмотрим на синтаксис, а затем посмотрим, как вы можете его использовать:

  = ТЕКСТ ([текст], [формат]) 

Аргумент [format] позволяет вам выбрать способ отображения числа в тексте. Существует ряд различных операторов, которые вы можете использовать для форматирования текста, но здесь мы остановимся на простых (подробности см. На странице справки Microsoft Office на TEXT ). TEXT часто используется для преобразования денежных величин, поэтому мы начнем с этого.

Я добавил колонку под названием «Обучение», которая содержит номер для каждого студента. Мы отформатируем это число в строку, которая выглядит немного больше, чем мы привыкли читать денежные значения. Вот синтаксис, который мы будем использовать:

  = TEXT (G2, "$ #, ###") 

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

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

ФИКСИРОВАННАЯ Функция

Подобно TEXT, функция FIXED принимает ввод и форматирует его как текст; тем не менее, FIXED специализируется на преобразовании чисел в текст и дает вам несколько опций для форматирования и округления вывода. Вот синтаксис:

  = ИСПРАВЛЕНО ([число], [десятичные дроби], [без_комад]) 

Аргумент [число] содержит ссылку на ячейку, которую вы хотите преобразовать в текст. [десятичные числа] — это необязательный аргумент, который позволяет вам выбрать количество десятичных знаков, которые будут сохранены в преобразовании. Если это 3, вы получите число, как 13.482. Если вы используете отрицательное число для десятичных чисел, Excel округляет число. Мы рассмотрим это в примере ниже. [no_commas], если установлено значение TRUE, исключит запятые из окончательного значения.

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

  = ИСПРАВЛЕНО (G2, -3) 

Применительно к строке мы получаем ряд округленных значений обучения:

Функция VALUE

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

  = ЗНАЧЕНИЕ ([текст]) 

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

ДОЛЛАР Функция

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

  = ДОЛЛАР ([текст], [десятичные дроби]) 

Если вы оставите аргумент [decimals] пустым, по умолчанию он будет равен 2. Если вы добавите отрицательное число для аргумента [decimals], число будет округлено слева от десятичного числа.

Функция ASC

Помните наше обсуждение одно- и двухбайтовых символов? Вот как вы конвертируете между ними. В частности, эта функция преобразует двухбайтовые символы полной ширины в однобайтовые символы полуширины. Это может быть использовано для экономии места в вашей электронной таблице. Вот синтаксис:

  = ASC ([текст]) 

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

Функция JIS

Конечно, если вы можете конвертировать одним способом, вы также можете конвертировать обратно другим способом. JIS преобразует символы шириной в половину в символы полной ширины. Как и ASC, синтаксис очень прост:

  = JIS ([текст]) 

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

Функции редактирования текста

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

Функции UPPER, LOWER и PROPER

Это все очень простые для понимания функции. UPPER делает текст заглавными, LOWER — строчными, а PROPER — прописными буквами первой буквы в каждом слове, оставляя остальные буквы строчными. Здесь нет необходимости в примере, поэтому я просто дам вам синтаксис:

  = Верхний / нижний / НАДЛЕЖАЩЕЕ ([текст]) 

Выберите ячейку или диапазон ячеек, в которых находится ваш текст для аргумента [text], и вы готовы к работе.

ЧИСТАЯ функция

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

  = ОЧИСТКА ([текст]) 

Аргумент [text] — это просто расположение текста, который вы хотите очистить. В электронной таблице примера я добавил несколько непечатаемых символов к именам в столбце А, от которых нужно избавиться (в строке 2 есть один, который подталкивает имя вправо, и символ ошибки в строке 3) , Я использовал функцию CLEAN для переноса текста в столбец G без этих символов:

Теперь столбец G содержит имена без непечатных символов. Эта команда не только полезна для текста; это часто может помочь вам, если числа портят и другие ваши формулы; специальные персонажи могут нанести ущерб вычислениям. Это важно при преобразовании Word в Excel. , хотя.

Функция TRIM

В то время как CLEAN избавляется от непечатаемых символов, TRIM избавляется от лишних пробелов в начале или конце текстовой строки, которые могут оказаться у вас, если вы скопируете текст из Word или обычного текстового документа и в итоге получите что-то как «Дата последующего наблюдения». , , чтобы превратить его в «дату продолжения», просто используйте этот синтаксис:

  = TRIM ([текст]) 

Когда вы используете его, вы увидите результаты, аналогичные тем, когда вы используете CLEAN.

Функции замены текста

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

Функция ЗАМЕНИТЕЛЯ

Если вы работаете с большим количеством текста, иногда вам нужно будет внести некоторые серьезные изменения, такие как замена одной строки текста на другую. Может быть, вы поняли, что месяц в строке счетов неверен. Или что вы ввели чье-то имя неправильно. В любом случае, иногда вам нужно заменить строку. Вот для чего ЗАМЕНА. Вот синтаксис:

  = ЗАМЕНА ([текст], [старый_текст], [новый_текст], [экземпляр]) 

Аргумент [text] содержит расположение ячеек, в которых вы хотите выполнить замену, а [old_text] и [new_text] довольно понятны. [экземпляр] позволяет указать конкретный экземпляр старого текста для замены. Поэтому, если вы хотите заменить только третий экземпляр старого текста, вы должны ввести «3» для этого аргумента. ЗАМЕНИТЕ скопирует все остальные значения (см. Ниже).

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

  = ЗАМЕНА (D28, "Гонулулу", "Гонолулу") 

И вот что происходит, когда мы запускаем эту функцию:

Перетащив формулу в окружающие ячейки, вы увидите, что все ячейки из столбца D были скопированы, кроме тех, которые содержали орфографическую ошибку «Honululu», которая была заменена на правильное написание.

Функция ЗАМЕНА

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

  = REPLACE ([old_text], [start_num], [num_chars], [new_text]) 

[old_text] — это место, где вы указываете ячейки, в которых вы хотите заменить текст. [start_num] — это первый символ, который вы хотите заменить, а [num_chars] — это количество символов, которое будет заменено. Мы увидим, как это работает через мгновение. [new_text], конечно, это новый текст, который будет вставлен в ячейки — это также может быть ссылка на ячейку, что может быть весьма полезным.

Давайте посмотрим на пример. В нашей электронной таблице идентификаторы учеников имеют последовательности HP, SP, LP, UP и XP. Мы хотим избавиться от них и изменить их все на NP, что займет много времени, используя SUBSTITUTE или Find and Replace. Вот синтаксис, который мы будем использовать:

  = ЗАМЕНА (A2, 3, 2, "NP") 

Применительно ко всей колонке, вот что мы получаем:

Все двухбуквенные последовательности из столбца A были заменены на «NP» в столбце G.

Функции обрезки текста

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

Функция CONCATENATE

Это тот, который я использовал довольно много раз сам. Когда у вас есть две ячейки, которые нужно сложить вместе, CONCATENATE — ваша функция. Вот синтаксис:

  = CONCATENATE ([text1], [text2], [text3] ...) 

Что делает сцепление настолько полезным, так это то, что аргументы [text] могут быть простым текстом, таким как «Аризона», или ссылками на ячейки, такими как «A31». Вы можете даже смешать их. Это может сэкономить вам огромное количество времени, когда вам нужно объединить два столбца текста, например, если вам нужно создать столбец «Полное имя» из столбцов «Имя» и «Фамилия». Вот синтаксис, который мы будем использовать для этого:

  = CONCATENATE (A2, "", B2) 

Обратите внимание, что вторым аргументом является пустое пространство (напечатанное как quotation-mark-space-qoutation-mark). Без этого имена будут объединены напрямую, без пробелов между именами и фамилиями. Давайте посмотрим, что произойдет, когда мы запустим эту команду и используем автозаполнение в оставшейся части столбца:

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

  = A2 & "" & B2 

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

  = E2 & "," & F2 & ", USA" 

Это берет ячейки с названиями городов и штатов и объединяет их с «США», чтобы получить полный адрес, как показано ниже.

Левые и правые функции

Часто вы хотите работать только с первым (или последним) несколькими символами текстовой строки. LEFT и RIGHT позволяют вам сделать это, возвращая только определенное количество символов, начиная с левого или самого правого символа в строке. Вот синтаксис:

  = ВЛЕВО / ВПРАВО ([text], [num_chars]) 

[text], конечно, является исходным текстом, а [num_chars] является количеством символов, которое вы хотите вернуть. Давайте рассмотрим пример, когда вы можете захотеть сделать это. Допустим, вы импортировали несколько адресов, каждый из которых содержит как аббревиатуру штата, так и страну. Мы можем использовать LEFT, чтобы получить только сокращения, используя этот синтаксис:

  = ВЛЕВО (E2, 2) 

Вот как это выглядит применительно к нашей таблице:

Если бы аббревиатура пришла после штата, мы бы использовали ПРАВО таким же образом.

Функция MID

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

  = MID ([текст], [start_num], [num_chars]) 

[start_num] — первый символ, который будет возвращен. Это означает, что если вы хотите, чтобы первый символ в строке был включен в результат функции, это будет «1.» [num_chars] — это количество символов после начального символа, который будет возвращен. Мы сделаем небольшую очистку текста с этим. В примере таблицы теперь у нас есть заголовки, добавленные к фамилиям, но мы хотели бы вырезать их так, чтобы фамилия «Mr. Мартин »будет возвращен как« Мартин ». Вот синтаксис:

  = MID (A2, 5, 15) 

Мы будем использовать «5» в качестве начального символа, потому что первая буква имени человека является пятым символом («Мистер» занимает четыре пробела). Функция вернет следующие 15 букв, которых должно быть достаточно, чтобы не отрезать последнюю часть чьего-либо имени. Вот результат в Excel:

По своему опыту, я считаю MID наиболее полезным, когда вы комбинируете его с другими функциями. Допустим, что в эту таблицу вместо мужчин были включены только женщины, которые могли иметь либо «мисс», либо «миссис» для своих титулов. Что бы мы тогда делали? Вы можете комбинировать MID с IF, чтобы получить имя независимо от названия:

  = IF (ВЛЕВО (A2, 3) = "Mrs", MID (A2, 6, 16), MID (A2, 5, 15) 

Я позволю вам выяснить, как именно эта формула работает своим волшебством (вам может понадобиться просмотреть булевы операторы ).

Функция REPT

Если вам нужно взять строку и повторить ее несколько раз, и вы не хотите вводить ее снова и снова, REPT может помочь. Дайте REPT строку («abc») и число (3) раз, которое вы хотите повторить, и Excel выдаст вам именно то, что вы просили («abcabcabc»). Вот очень простой синтаксис:

  = REPT ([текст], [число]) 

[текст], очевидно, является базовой строкой; [число] — это количество раз, которое вы хотите повторить. Хотя я еще не использовал эту функцию, я уверен, что кто-то там мог бы использовать ее для чего-то. Мы будем использовать пример, который, хотя он и не совсем полезен, может показать вам потенциал этой функции. Мы собираемся объединить REPT с «&», чтобы создать что-то новое. Вот синтаксис:

  = "* <---" & REPT ("*", 9) & "---> *" 

Результат показан ниже:

Пример из реального мира

Чтобы дать вам представление о том, как вы можете использовать текстовую функцию в реальном мире, я приведу пример того, как я комбинировал MID с несколькими условными выражениями в моей собственной работе. Для получения степени последипломного психолога я провел исследование, в котором участники должны были нажать на одну из двух кнопок, и координаты этого нажатия были записаны. Кнопка слева на экране была помечена буквой A, а кнопка справа была помечена буквой B. Каждое испытание имело правильный ответ, и каждый участник провел 100 испытаний.

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

Правильный ответ для каждого испытания указан в столбце D, а координаты щелчка указаны в столбцах F и G (они отформатированы как текст, что усложняет задачу). Когда я начал, я просто прошел и сделал анализ вручную; если в столбце D указано «optiona», а значение в столбце F отрицательное, я бы ввел 0 (для «неправильно»). Если бы оно было положительным, я бы ввел 1. Противоположное было бы верно, если бы столбец D читал «optionb».

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

  = IF (D3 = "optiona", IF (MID (F3,2,1) = "-", 1,0), IF (MID (F3,2,1) = "-", 0,1)) 

Давайте разберемся с этим. Начиная с первого оператора IF, мы имеем следующее: «если ячейка D3 говорит« optiona », то [first условная]; если нет, то [второе условное]. »Первое условное выражение говорит следующее:« если второй символ ячейки F3 является дефисом, верните true; если нет, верните false. »Третий говорит:« если второй символ ячейки F3 является дефисом, верните false; если нет, верните истину.

Это может занять некоторое время, чтобы обернуть голову вокруг этого, но это должно стать ясно. Вкратце, эта формула проверяет, говорит ли D3 «optiona»; если это так, а второй символ F3 является дефисом, функция возвращает «true». Если D3 содержит «optiona», а второй символ F3 не является дефисом, она возвращает «false». Если D3 не делает содержат «optiona», а второй символ F3 является дефисом, он возвращает «false». Если D3 не говорит «optiona», а второй символ F2 не является дефисом, он возвращает «true».

Вот как выглядит электронная таблица при запуске формулы:

Теперь столбец «Оценка» содержит 1 для каждого испытания, на которое участник ответил правильно, и 0 для каждого испытания, на которое он ответил неверно. Оттуда легко суммировать значения, чтобы увидеть, сколько они получили правильно.

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

Excel Text Mastery

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

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

Дайте нам знать, как вы использовали текстовые операции в Excel! Какое самое сложное преобразование вы сделали?