Економія часу за допомогою текстових операцій Excel

Microsoft Excel є основним додатком для всіх, хто повинен працювати з великою кількістю цифр, від студентів до бухгалтерів. Але його корисність виходить за рамки великих баз даних; він також може робити багато хороших речей з текстом. Наведені нижче функції допоможуть вам аналізувати, редагувати, перетворювати та іншим чином вносити зміни в текст, а також заощадити вам багато годин нудної та повторюваної роботи.

Відкрийте БЕЗКОШТОВНУ шпаргалку «Essential Excel Oneulas» прямо зараз!

Це підпише вас на нашу розсилку

Введіть адресу електронної пошти

[] [] [] [] розблокування

Прочитайте нашу політику конфіденційності

Навігація: неруйнівне редагування  Символи половинної і повної ширини  Функції персонажа  Функції аналізу тексту  Функції перетворення тексту

Неруйнівне редагування

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

Хоча ви можете продовжувати додавати стовпчики і рядки в свою гігантську таблицю, що постійно розширюється, один із способів скористатися цим - зберегти вихідну електронну таблицю на першому аркуші в документі і наступні відредаговані копії на інших аркушах. Таким чином, незалежно від того, скільки змін ви робите, у вас завжди будуть вихідні дані, з якими ви працюєте.

Символи половинної і повної ширини

Деякі з цих функцій посилаються на одно- і двобайтові набори символів, і, перш ніж ми почнемо, буде корисно з'ясувати, що це таке. У деяких мовах, таких як китайська, японська та корейська, кожен символ (або кілька символів) матиме дві можливості для відображення: один кодується в два байти (відомий як символ повної ширини), а інший - в один байт (напівширина). Ви можете побачити різницю в цих символах тут:

Як бачите, двобайтові символи більше за розміром і часто легше читаються. Однак у деяких обчислювальних ситуаціях потрібен один або інший з цих типів кодування. Якщо ви не знаєте, що це означає або чому вам потрібно про це турбуватися, дуже ймовірно, що вам не доведеться про це думати. Однак, якщо ви це зробите, у наступних розділах є функції, що відносяться конкретно до символів половинної і повної ширини.

Функції персонажа

Нечасто ви працюєте з окремими символами в Excel, але такі ситуації іноді виникають. І коли вони це зроблять, вам потрібно знати ці функції.

Функції CHAR і UNICHAR

CHAR приймає номер символу і повертає відповідний символ; наприклад, якщо у вас є список номерів символів, CHAR допоможе вам перетворити їх на символи, з якими ви більш звикли мати справу. Синтаксис досить простий:

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

[текст] може приймати форму посилання на комірку або символ; тому = CHAR (B7) і = CHAR (84) обидва працюють. Зауважте, що під час використання CHAR буде використано кодування, встановлене на вашому комп "ютері; тому ваш = CHAR (84) може відрізнятися від мого (особливо якщо ви працюєте на комп'ютері з Windows, оскільки я використовую Excel для Mac).

Якщо число, в яке ви конвертуєте, є номером символу Юнікода, і ви використовуєте Excel 2013, вам необхідно використовувати функцію UNICHAR. Попередні версії Excel не мають цієї функції.

Функції CODE та UNICODE

Як і слід було очікувати, CODE і UNICODE роблять повну протилежність функціям CHAR і UNICHAR: вони беруть символ і повертають номер для вибраного вами кодування (або його встановлено на типовому комп'ютері). Важливо пам'ятати, що якщо ви запустите цю функцію для рядка, що містить більше одного символу, вона поверне посилання на символ тільки для першого символу в рядку. Синтаксис дуже схожий:

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

У цьому випадку [текст] є символом або рядком. І якщо вам потрібне посилання на 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. Давайте подивимося на синтаксис, а потім подивимося, як ви можете його використовувати:

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

Аргумент [1916 at] дозволяє вам вибрати спосіб відображення числа в тексті. Існує ряд різних операторів, які ви можете використовувати для форматування тексту, але тут ми зупинимося на простих (подробиці див. на сторінці довідки 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 були скопійовані, крім тих, які містили орфографічну помилку «Honulululu», яка була замінена на правильне написання.

Функція ЗАМІНА

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). Без цього назви будуть об'єднані безпосередньо, без пробілів між іменами і прізвищами. Давайте подивимося, що станеться, коли ми запустимо цю команду і використовуємо автозаповнення в частині стовпця:

Тепер у нас є колонка з повним ім'ям кожного. Ви можете легко використовувати цю команду для об'єднання кодів міста