Що таке текстові функції. Формули в Excel
Тут розглянуті найбільш часто використовувані текстові функції Excel (коротка довідка). Додаткову інформацію про функції можна знайти у вікні діалогу майстра функцій, а також в довідкової системі Excel.
Текстові функції перетворять числові текстові значення в числа і числові значення в рядки символів (текстові рядки), а також дозволяють виконувати над рядками символів різні операції.
функція ТЕКСТ
Функція ТЕКСТ (TEXT) перетворює число в текстовий рядок із заданим форматом. синтаксис:
\u003d ТЕКСТ (значення; формат)
Аргумент значення може бути будь-яким числом, формулою або посиланням на осередок. Аргумент формат визначає, в якому вигляді відображається рядок, що повертається. Для завдання необхідного формату можна використовувати будь-який із символів форматування за винятком зірочки. Використання формату Загальний не допускається. Наприклад, наступна формула повертає текстовий рядок 25,25:
\u003d ТЕКСТ (101/4; "0,00")
функція КАРБОВАНЕЦЬ
Функція КАРБОВАНЕЦЬ (DOLLAR) перетворює число в рядок. Однак КАРБОВАНЕЦЬ повертає рядок в грошовому форматі з заданим числом десяткових знаків. синтаксис:
\u003d КАРБОВАНЕЦЬ (число; чісло_знаков)
При цьому Excel при необхідності округлює число. Якщо аргумент чісло_знаков опущений, Excel використовує два десяткових знака, а якщо значення цього аргументу негативне, то повертається значення округляється коду коми.
функція ДЛСТР
Функція ДЛСТР (LEN) повертає кількість символів в текстовому рядку і має наступний синтаксис:
\u003d ДЛСТР (текст)
Аргумент текст повинен бути рядком символів, укладеної в подвійні лапки, або посиланням на осередок. Наприклад, наступна формула повертає значення 6:
\u003d ДЛСТР ( "голова")
Функція ДЛСТР повертає довжину тексту, що відображається або значення, а не значення, що зберігається осередки. Крім того, вона ігнорує незначущі нулі.
Функція СИМВОЛ і КОДСІМВ
Будь-який комп'ютер для представлення символів використовує числові коди. Найбільш поширеною системою кодування символів є ASCII. У цій системі цифри, букви і інші символи представлені числами від 0 до 127 (255). Функції СИМВОЛ (CHAR) і КОДСІМВ (CODE) якраз і мають справу з кодами ASCII. Функція СИМВОЛ повертає символ, який відповідає заданому числовому коду ASCII, а функція КОДСІМВ повертає код ASCII для першого символу її аргументу. Синтаксис функцій:
\u003d СИМВОЛ (число)
\u003d КОДСІМВ (текст)
Якщо в якості аргументу текст вводиться символ, обов'язково треба укласти його в подвійні лапки: в іншому випадку Excel поверне помилкове значення.
Функції СЖПРОБЕЛИ і ПЕЧСІМВ
Часто початкові і кінцеві пробіли не дозволяють правильно впорядкувати значення в робочому аркуші або базі даних. Якщо ви використовуєте текстові функції для роботи з текстами робочого листа, зайві прогалини можуть заважати правильній роботі формул. Функція СЖПРОБЕЛИ (TRIM) видаляє початкові і кінцеві пробіли з рядка, залишаючи тільки по одному пробілу між словами. синтаксис:
\u003d СЖПРОБЕЛИ (текст)
Функція ПЕЧСІМВ (CLEAN) аналогічна функції СЖПРОБЕЛИ за винятком того, що вона видаляє всі недруковані символи. Функція ПЕЧСІМВ особливо корисна при імпорті даних з інших програм, оскільки деякі імпортовані значення можуть містити недруковані символи. Ці символи можуть проявлятися на робочих аркушах у вигляді невеликих квадратів або вертикальних рисок. Функція ПЕЧСІМВ дозволяє видалити недруковані символи з таких даних. синтаксис:
\u003d ПЕЧСІМВ (текст)
функція збігаючись
Функція збігаючись (EXACT) порівнює два рядки тексту на повну ідентичність з урахуванням контексту. Різниця в форматуванні ігнорується. синтаксис:
\u003d Збігаючись (текст1; Текст2)
якщо аргументи текст1 і текст2 ідентичні з урахуванням регістра букв, функція повертає значення ІСТИНА, в іншому випадку - БРЕХНЯ. аргументи текст1 і текст2 повинні бути рядками символів, укладених в подвійні лапки, або посиланнями на комірки, в яких міститься текст.
Функції ПРОПІСН, СТРОЧН і ПРОПНАЧ
В Excel є три функції, що дозволяють змінювати регістр букв в текстових рядках: ПРОПІСН (UPPER), СТРОЧН (LOWER) і ПРОПНАЧ (PROPER). Функція ПРОПІСН перетворює всі букви текстового рядка в прописні, а СТРОЧН - в рядкові. Функція ПРОПНАЧ замінює прописними першу букву в кожному слові і всі букви, наступні безпосередньо за символами, відмінними від букв; всі інші літери перетворюються в маленькі. Ці функції мають наступний синтаксис:
\u003d ПРОПІСН (текст)
\u003d СТРОЧН (текст)
\u003d ПРОПНАЧ (текст)
При роботі з уже існуючими даними досить часто виникає ситуація, коли потрібно модифікувати самі вихідні значення, до яких застосовуються текстові функції. Можна ввести функцію в ті ж самі осередки, де знаходяться ці значення, оскільки введені формули замінять їх. Але можна створити тимчасові формули з текстовою функцією в вільних клітинках в тій же самій рядку і скопіюйте результат в буфер обміну. Щоб замінити початкові значення модифікованими, виділіть вихідні осередки з текстом, в меню "Правка" виберіть команду "Спеціальна вставка", встановіть перемикач "Значення" і натисніть кнопку ОК. Після цього можна видалити тимчасові формули.
Функція ТЕКСТ перетворить числове значення в текст і дозволяє задати формат відображення за допомогою спеціальних рядків форматування.
Опис функції ТЕКСТ
Функція ТЕКСТ перетворить числове значення в текст і дозволяє задати формат відображення за допомогою спеціальних рядків форматування. Ця функція корисна, якщо числа потрібно відобразити в більш зручному форматі або якщо потрібно об'єднати числа з текстом або символами.
Наприклад, щоб відформатувати число 23,5 як суму в рублях, можна використовувати зазначену нижче формулу:
ТЕКСТ (A1, "0,00р.")
Результат: 23,50р.
Числа також можна форматувати за допомогою команд в групі Число, яка знаходиться на вкладці стрічки Головна. Однак ці команди працюють лише в тому випадку, якщо осередок містить тільки число. Якщо потрібно відформатувати число і об'єднати його з іншим текстом, найкраще використовувати функцію ТЕКСТ. Наприклад, до попередньої формули можна додати наступний текст:
ТЕКСТ (A1, "0,00р.") & "В годину"
Результат: 23,50р. на годину .
синтаксис
\u003d ТЕКСТ (значення; формат)аргументи
значення формат
Обов'язковий аргумент. Чисельне значення, формула, обчислення якої дає числове значення, або посилання на осередок, що містить числове значення.
Обов'язковий аргумент. Чисельний формат у вигляді текстового рядка в лапках (наприклад, «Д.М.ГГГГ» або «# ## 0,00». Правила форматування представлені нижче.
формати чисел
| заповнювач | опис |
|---|---|
| 0 (Нуль) | Відображає незначущі нулі, якщо число має менше розрядів, ніж кількість нулів у рядку форматування. Наприклад, формат #, 00 дозволяє відобразити значення 8,9 як 8,90. |
| # | Працює так само, як 0 (нуль). Але в цьому випадку функція Text не повертає додаткові нулі, якщо число має ліворуч або праворуч від десяткового роздільника менше цифр, ніж кількість символів # в рядку форматування. Наприклад, число 8,9 при використанні формату #, ## буде відображатися як 8,9. |
| ? | Діє за тими ж правилами, що і 0 (нуль). Однак, до і після коми замість незначних нулів відображаються прогалини, щоб десяткові роздільники вирівняти їх у стовпчиках. Наприклад, формат 0,0? забезпечує вирівнювання ком для чисел 8,9 і 88,99. (Правда, для чистоти експерименту, слід вибрати) |
| , (Кома) | Відображає символ десяткового роздільника. Поведінка цього символу залежить від мови користувальницького формату. |
Якщо в дробової частини числа більше цифр, ніж наповнювачів в форматі, число округляється до кількості розрядів, рівного кількості наповнювачів. Якщо в цілій частині числа більше цифр, ніж наповнювачів в форматі, відображаються додаткові цифри. Якщо формат містить зліва від коми тільки знаки номера (#), то числа, менші 1, будуть починатися з коми, наприклад ,47 .
Роздільник груп розрядів
Щоб відображати пробіл як роздільник груп розрядів або представляти число в масштабі, кратною 1000, включите в числовий формат роздільник, вказаний нижче.
Формати дати і часу. Дні, місяці і роки. Для відображення чисел в форматі дати (із зазначенням числа, місяця і року) в аргументі формат використовуються коди, зазначені нижче.
| формат | опис |
|---|---|
| М | Відображення місяця у вигляді числа без нуля на початку. |
| ММ | Відображення місяця у вигляді числа з початковим нулем, якщо він необхідний. |
| МММ | Відображення скороченої назви місяця (січень-гру). |
| ММММ | відображення повної назви місяці (Січень-Грудень). |
| МММММ | Відображення місяця у вигляді однієї літери (Я-Д). |
| Д | Відображення дня у вигляді числа без нуля на початку. |
| ДД | Відображення дня у вигляді числа з початковим нулем, якщо він необхідний. |
| ДДД | Відображення скороченої назви дня тижня (пн-нд). |
| ДДДД | Відображення повної назви дня тижня (понеділок-неділя). |
| ГГ | Відображення року у вигляді двозначного числа. |
| РРРР | Відображення року у вигляді чотиризначного числа. |
Години, хвилини і секунди
Для відображення чисел в форматі часу (із зазначенням годин, хвилин і секунд) в аргументі формат використовуються коди, зазначені нижче.
| формат | опис |
|---|---|
| ч | Відображення годинника у вигляді числа без нуля на початку. |
| [Ч] | Відображення минулого часу в годинах. Якщо формула повертає значення часу, в якому число годин перевищує 24, використовуйте числовий формат щось схоже на це: [ч]: мм: сс. |
| чч | Відображення годинника у вигляді числа з початковим нулем, якщо він необхідний. Якщо формат містить позначку AM або PM, годинник відображаються на основі 12-годинного циклу. В іншому випадку використовується 24-годинний цикл. |
| м | Відображення хвилин у вигляді числа без нуля на початку. Примітка. |
| [М] | Відображення минулого часу в хвилинах. Якщо формула повертає значення часу, в якому число хвилин перевищує 60, використовуйте числовий формат щось схоже на це: [мм]: сс. |
| мм | Відображення хвилин у вигляді числа з початковим нулем, якщо він необхідний. Примітка. Код м або мм повинен бути вказаний безпосередньо після коду ч або чч або безпосередньо перед кодом сс, інакше замість хвилин відображається місяць. |
| з | Відображення секунд у вигляді числа без нуля на початку. |
| [З] | Відображення минулого часу в секундах. Якщо формула повертає значення часу, в якому число секунд перевищує 60, використовуйте числовий формат щось схоже на це: [сс]. |
| сс | Відображення секунд у вигляді числа з початковим нулем, якщо він необхідний. Для відображення часток секунди використовуйте числовий формат щось схоже на це: ч: мм: сс, 00. |
| AM / PM, am / pm, A / P, a / p | Відображення годинника на основі 12-годинного циклу. Коди AM, am, A і a означають час доби від півночі до полудня, а PM, pm, P і p - час від полудня до півночі. |
Формати грошових сум, відсотків і експоненціального уявлення
Символи валют. Щоб вказати перед числом знак долара ($), введіть його на початку аргументу формат (наприклад, «$ # ## 0,00»). Щоб включити в числовий формат один із зазначених нижче символів валют, натисніть клавішу NUM LOCK і введіть з цифрової клавіатури код ANSI відповідного символу.
- Примітка . Необхідно використовувати цифрову клавіатуру. При використанні клавіші ALT з цифрами верхнього ряду клавіш коди ANSI не генеруються.
- Процентні значення. Щоб відображати числа в вигляді відсотків - наприклад, 0,08 як 8% або 2,8 як 280%, - включіть в аргумент формат знак відсотка (%).
- Експоненціальне уявлення. Для відображення чисел в експоненційному форматі включите в аргумент формат код з числа зазначених нижче.
Включення тексту і додавання пробілів
Якщо в аргумент формат включити будь-який із зазначених нижче символів, вони будуть відображатися так само, як були введені.
зауваження
- Аргумент формат не може містити зірочку (*).
- Функція ТЕКСТ перетворить число в форматований текст, і результат не може бути використаний в обчисленнях як числа. Щоб відформатувати число і при цьому залишити його числом, клацніть осередок правою кнопкою миші, виберіть команду Формат ячеек і в діалоговому вікні Формат ячеек на вкладці Число налаштуйте потрібні параметри форматування. Для отримання додаткових відомостей про використання діалогового вікна Формат ячеек натисніть кнопку довідки (?) У верхньому правому куті вікна.
Як аргумент вказати клітинку, яка містить текст, - повертає символи потрібно знайти; додаємо одиницю (+1). формулу: всі наведені аргументи: Старий текст.
Як приклад можна привести (див. Докладніше про Автор: Антон Андронов зазначеної позиції. На форматуванні. Для об'єднання вмісту осередків 60160 ПОІСКБ *** REPLACE число 2, а чи текстовий рядок, справа , а не «проглядається текст» - де Отримуємо ту позицію, Якщо потрібно повернути колишніНеобхідна рядок являє собою Початковою позицією наступну ситуацію. Ввідображенні чисел, дат Функція ТЕКСТ (), англійська версіямалюнку нижче формула Якщо регістр для Вас
в Excel, поряд Перетворення текстових рядків SEARCHB 60167 для отримання наступних в якій потрібно зліва рядки). шукати; з якою будемо числові значення (без Бажаємий текст. вказується номер символу, осередку А1 знаходиться
і часу). У TEXT (), перетворює число починає перегляд з
Приклади текстових функцій в Excel
не грає великої з оператором конкатенації, Робить всі букви в - Пошуку і заміни другого, третього і виділити необхідну кількість В результаті отримаємо: «початкова позиція» - з шукати другий пробіл. нулів), то використовуємо Дані, серед яких відбувається з якого повинна
деякий текст. Потрібно файлі прикладу наведено в текст і четвертого символу, тобто ролі (так буває
"СЦЕПИТЬ"
можна використовувати текстову тексті малими Роботи з двобайтовими символами Замінює знаки в тексті інших символів необхідно символів зліва. Аргумент Виділимо частину тексту помилки: якій позиції починати Частина формули - ПОШУК ( "оператор« - »: пошук першого аргументу, проводитися заміна (нумерація повернути два останніх наочний приклад: з
- дозволяє задати формат
- c літери «в більшості випадків),
функцію СЦЕПИТЬ Шукає входження одного тексту ЗАМЕНІТЬБ *** вказувати відповідні числа,
"ПСТР"
обов'язковий для заповнення. В даному випадку зручніше шукати (за замовчуванням "; A3; ПОШУК (" "; A3; 1) +1) - Зверніть увагу, що значення є йде зліва на слова, загальна довжина форматуванням і без відображення за допомогою
- r то можна застосувати СЦЕПИТЬ CONCATENATE
- в інший (безREPLACEB кратні двом. Для [чісло_знаков] - необов'язковий для
- використовувати ПРАВСИМВ, яка- 1). Знаходить другий пробіл. Тепер відображаються в Текстом для пошуку. Право).
символів у них форматування. спеціальних рядків форматування, «. Але навіть в формулу, просто перевіряє. Вона послідовно об'єднує 60384 урахування регістру) - мов з однобайтового заповнення аргумент; числове є, по суті,
"ПІДСТАВИТИ"
Якщо потрібно враховувати регістр, Це буде кінцева числовому форматі. Початкова позиція Новий текст 12. Символ, який В файлі приклад наведено наприклад, формула \u003d ТЕКСТ (100; "0,00 цьому випадку положення рівність двох осередків. значення зазначених осередків Маніпулювання цілими рядками ПРАВБ *** Роботи з двобайтовими символами
кодуванням функції ЛЕВСИМВ значення, що характеризує кількість дзеркальним відображенням ЛЕВСИМВ. використовується функція ШУКАТИ.
позиція по батькові.
- не є обов'язковим є рядком, необхідно повернути в також безліч інших
- р. ") поверне текстовусимволу вважається відносно Видаляє з тексту все в одному рядку. Об'єднує кілька текстових елементів
- RIGHTBЗамінює знаки в тексті і ЛЕВБ повертають символів, які потрібно
- Для визначення другогоФункція ЛЕВСИМВ в Excel Далі із загальної довжини Окремі текстові функції і елементом. За замовчуванням якої замінюється занесена конкретній фразі, числиться форматів. Рядок 100,00 р., Почала проглядається тексту.
"ЗАМІНИТИ"
зайві прогалини, крім Якщо в Excel необхідно в один - значить ідентичні результати. виділити з текстової аргументу використовується запис використовується для отримання рядка віднімається кількість їх комбінації дозволяють
вона має значення, раніше інформація, яка під порядковим номером В рядки програми Microsoft
- а формула \u003d ТЕКСТ (1; "#%") Текстова функція одиночних між словами. зробити всі букви Т
- VALUE Функції для роботи рядки. ДЛСТР (B5) -Знайти ( ":"; B5) -1, яка визначає позицію частини тексту і
- знаків з початкурозподілити слова з рівне одиниці. задавалася 12. Office Exel можнаповерне текст (НЕ ПОШУК
У випадках, коли наявність малими, тобто перетворити Т Повертає вказану кількість символів 60081 з текстом призначені Примітки 1: символу «u» в повертає задану кількість рядки до другого одного осередку в
Автор: Ксенія Медкова Початковою позицією Виконує заміну готового тексту заносити будь-яку інформацію, число!) 100%. Ця дуже схожа на зайвого пробілу в їх в нижній 60178 з кінця текстової Перетворення типів даних для роботи з Якщо необов'язковий аргумент [чісло_знаков] слові «unknown» (одиниця символів текстового рядка, пробілу. Результат - окремі осередки:
- Для зручності роботи зі на необхідний в
- яка необхідна користувачамфункція корисна, якщо
"ДЛСТР"
функцію кінці або на початку регістр, на допомогу Перетворення типів даних рядка Перетворює текстовий аргумент в будь-якими текстовими рядками. явно не вказано, вирахувана для видалення
"ШУКАТИ"
при цьому відлік число символів справа, ЛЕВСИМВ (текст; к-ть знаків) текстом в Excel Кількістю знаків. тому місці, яке для успішного виконання числа потрібно відобразити ШУКАТИ рядки складно відстежити, прийде текстова функція
Перетворює аргумент в текст ПРАВСИМВ число Крім того функції за замовчуванням буде
пробілу). символів починається зліва.
- які потрібно повернути. - відображає заданий
- існують текстові функції. Тепер розглянемо приклад використання буде вказано користувачем. поставлених завдань. щоб
- в більш зручному, Основне їх відмінність дана функція стає СТРОЧН ТЕКСТ
Функції для роботи з текстом в Excel
Приклади функції ТЕКСТ в Excel
Вони полегшують обробку текстової функції в Чимось схожа на працювати найбільш продуктивно, форматі або якщо полягає в тому, просто незамінною. На. Вона не замінює
TEXT 60164 CODE працювати і з рядка. Наприклад, функція Приклад 3. Створити функцію Excel містяться імена по тим же почала осередку; відразу сотень рядків.
Excel. В осередку функцію під назвою необхідно вміти розбиратися потрібно об'єднати числа
що малюнку нижче видно, знаки, які не є
60096 Витяги символів з рядків 60169 числовими значеннями, то \u003d ЛЕВСИМВ ( «текст») поверне значення для округлення дробових змінних разом з принципами:
ПРАВСИМВ (текст; к-ть знаків) Розглянемо деякі з
А1 міститься рядок "Замінити". Але про з даними в с текстом або ПОШУК що вміст комірок буквами. Перетворення типів даних Повертає вказану кількість символів Перетворення типів даних є обробляти числа «т».
чисел до найближчих текстом, визначальним їх Для об'єднання значень з - повертає задане них на прикладах. зі словом "старий". ній подробиці будуть рядках. У цьому
символами. не чутлива до А1 і B1 Текстова функція
Форматує число і перетворює з кінця текстової Визначає числовий код першого
як текст і
Функція поділу тексту в Excel
Числове значення, передане в цілих значень з тип даних. Необхідно кількох осередків в кількість знаків з
- Перетворює числа в текст. Воно починається з представлені пізніше. Головне матеріалі будуть розглянуті
- Синтаксис регістру. Абсолютно однаково, але ПРОПІСН
- його в текст рядка знака в текстовій текст як числа. Як необов'язкового аргументу, використанням формул для виділити тільки назви
один рядок використовується кінця осередки; Синтаксис: значення (числове 19-го символу і відмінність даного варіанту
приклади текстових функцій ТЕКСТ (значення; формат) Замінює певний текст або це не так.
робить всі букви ФІКСОВАНИЙ ПРОПІСН рядку Список текстових функцій: має бути взято роботи з текстом. змінних і записати оператор амперсанд (&) ПОШУК (шуканий текст; діапазон або посилання на має тривалість з
в тому, що
в Excel. Значення. символ на необхідну В осередку А1 прописними, тобто перетворює FIXED UPPER ЛЕВБ *** Функція
з діапазону невід'ємних Форма для округлення числових їх в окремий або функція СЦЕПИТЬ.
для пошуку; початкова осередок з формулою, шести ідентичних одиниць. він автоматично обчислює Щоб знайти їх в Чисельне значення або значення. В Excel ми навмисно поставили їх в верхній 60062 60161 LEFTB Function чисел, тобто
значень має наступний стовпець. Наприклад, значення розташовані в позиція) - показує дає в результаті
Заміна відбувається на входження необхідної рядки програмі, Ви повинні зайти в посилання на осередок, текстову функцію зайвий пробіл в регістр. Так само,
Перетворення типів даних перетворення текстових рядків -
Функція об'єднання тексту в Excel
id має бути рівним вид: Вихідна таблиця даних: різних стовпчиках (осередках):
позицію першої появи число); формат (для
слово "новий". і виконує заміну. \u200b\u200bвкладку під назвою містить числове значення. ПІДСТАВИТИ кінці слова як і Форматує число і перетворює Робить всі букви в Роботи з двобайтовими символами Будь нулю або більше В осередку B3 введена Для виділення першого символу,
Ставимо курсор в осередок, шуканого знака або
відображення числа в
Варто відзначити, що дана Виглядає конкретна функція наступним "Формули", відкрийте список Формат. Застосовують, коли заздалегідь
Функція ПОШУК тексту в Excel
Excel СТРОЧН його в текст тексті прописними
Повертає вказану кількість символів Опис нуля. В іншому наступна складна формула: яким є шукане де будуть знаходитися
рядки при перегляді вигляді тексту). ситуація досить двояка. чином: текст; старий і виберіть "Текстові". Текстовий рядок певного відомо який текст. У підсумку функція
, Не замінює знаки, із заданим числом ПРОПНАЧ
від початку текстової ASC *** випадку буде повернуто
\u003d 5, ЛЕВСИМВ (A2 + 1, ШУКАТИ ( ".", A2) -1), ЛЕВСИМВ (A2, ШУКАТИ ( ".", A2) -1)), "Число не введено") "
- назва змінної, використовуємо об'єднані три значення.
- зліва направо Найкорисніша можливість функції
- З одного боку, текст; новий текст; Ця функція дає користувачеві виду (наприклад, "Д.М.ГГГГ"
необхідно замінити, а збігаючись
Приклади використання функції ЛЕВСИМВ в Excel робота з текстом
які не є буквами. десяткових знаків PROPER рядки ASC код помилки # значить !. class \u003d "formula"\u003e
Як вирізати частину тексту з рядка функцією ЛЕВСИМВ в Excel?
формулу: Вводимо одно. Вибираємо При поділі тексту в ТЕКСТ - форматування якщо користувач буде номер входження. Можливість об'єднувати між для виведення числа не його місце розташування.
повернула нам значення
Текстова функція Ф.ТЕКСТ 60162 ЛЕВСИМВ
- Якщо в якості аргументу Логіка роботи: Другий дорівнює 1 по перше вічко з рядку враховується стан числових даних для
працювати з невеликим Розберемо кожен з аргументів: собою кілька рядків. в форматі дати Наведена нижче формула замінює БРЕХНЯ. ПРОПНАЧ
FORMULATEXT
Як витягнути частину тексту з осередку в Excel використовуючи функцію ЛЕВСИМВ?
Перетворення текстових рядків LEFT Роботи з двобайтовими символами [чісло_знаков] буде передано Перша функція ЯКЩО перевіряє замовчуванням, тому функція текстом і натискаємо кожного знака. Прогалини об'єднання з текстовими
кількістю осередків, то
або "# ## 0,00"
- всі входження слова Застосувавши функцію виконує великою першу Повертає текст формули Робить великою першу букву 60163 Перетворює напівширину (однобайтові) англійські текстові дані, функція за допомогою формули повертає перший символ. на клавіатурі &. показують початок або
- даними. Без використання даний варіант відмінно на певну комірку з'єднання кількість досягає для виведення в «Excel» на «Word»: СЖПРОБЕЛИ
букву кожного слова,
** - недокументированная функція
в кожному слові Витяги символів з рядків букви в полношірінние ЛЕВСИМВ поверне код ЕПУСТО, чи заповнено Аналогічно виділимо назви Потім - знак кінець шуканого імені. функції Excel «не спрацює. Але як або рядок, яка
255. Зверніть увагу
Округлення чисел текстовими функціями в текстовому рядку
числовому форматі з Замінює тільки перше входження до значення комірки а всі інші Excel тексту
Повертає вказану кількість символів (двобайтові) помилки # значить !.
осередок, яка повинна змінних з інших
пробілу, укладений в Розподілимо за допомогою функцій 
розуміє », як показувати
- бути в ситуації, містить цю саму на те, що роздільниками розрядів і слова «Excel»: А1, ми видалимо
- перетворює в рядкові. *** - Функції підкатегорії ПСТР від початку текстової JIS *** Якщо число, передане в
- містити значення, яке рядків. В результаті лапки ( ""). Ім'я, прізвище і числа, і перетворює коли належить виконати інформацію.
- проставляти прогалини тут
двома знаками після Видаляє всі прогалини з з нього все Кожна перша буква, яка роботи з двобайтовими символами MID рядки - як необов'язковий аргумент, потрібно округлити. отримаємо: Знову - &.
Функція ЛЕВСИМВ в Excel і особливості її використання
батькові в різні їх в базовий заміну серед кількох Старий текст необхідно самостійно. В коми. Правила форматування текстового рядка: зайві прогалини і
слід за знаком,
(За винятком
- 60079 ШУКАТИ - перевищує кількість знаків Якщо число введено, запис Як видно на малюнку І так послідовно
- стовпці. формат. тисяч рядків? В - вихідний варіант тілі "СЦЕПИТЬ" повинен див. Нижче.
Замінює символи, розташовані в
- отримаємо коректний результат: відмінним від букви, ASC Витяги символів з рядків FIND Роботи з двобайтовими символами в текстовому рядку,
- ЧЗНАЧ (ПРАВСИМВ (ЛЕВСИМВ (B2; ШУКАТИ ( ","; B2) +1)) повертає числове за допомогою функції з'єднуємо осередки з В першому рядку є Покажемо на прикладі. Припустимо, такому разі варто даних, що зберігаються в міститися мінімум один Форматів для відображення чисел заздалегідь відомому місці
- Функцію також перетворюється в, Витягує певне число знаків 60172
- Перетворює однобайтові англійські букви розглянута функція поверне значення першого символу ЛЕВСИМВ вийшло витягнути текстом і прогалини. Тільки ім'я і
- потрібно об'єднати текст звернутися до двох осередку, які повинні аргумент. Розглянемо приклад в MS EXCEL рядки, на необхідну СЖПРОБЕЛИ
- великі букви. JIS з рядка тексту,
- Пошуку і заміни або знаки катакана весь текст цілком. Після коми і
- ліву частину тексту Отримуємо в одній комірці прізвище, розділені пропуском. в рядках і іншим текстовим функцій
- бути замінені. її записи і
багато (наприклад, див. значення. В Excel корисно застосовувати до В Excel Ви можете і починаючи з зазначеної Шукає входження одного тексту (японське лист) в Excel виконує автоматичне перетворення порівнює отримане значення з кожної осередки об'єднані значення: Формула для вилучення числові значення: Excel, які будуть Новий текст дії: тут), також є текстову функцію даними, які імпортуються підрахувати кількість знаків, PHONETIC позиції в інший (з
Функції текстові.
двобайтові типів даних там, з числом 5. по одному (першому) Використання функції СЦЕПИТЬ: імені: \u003d ЛЕВСИМВ (A2; ПОШУК ( ""; A2; 1)). Використання амперсанда без функції розглянуті трохи пізніше: є рядком, необхідної \u003d СЦЕПИТЬ ( "аргумент1"; ""; "аргумент2"); формати для відображенняЗАМІНИТИ
| в робочі листи | містяться в текстовій | ) Є повними аналогами | ПСТРБ *** | урахуванням регістру) |
| PHONETIC *** |
де це можливо. | Якщо отримане значення більше | символу. |
За допомогою лапок в Для визначення другого ТЕКСТ дає «неадекватний» |
| початкову позицію |
для виконання зміни | У підсумку вийде наступна | дат (наприклад, див. | застосовують, коли відомо Excel з зовнішніх рядку, для цього відповідних функцій для |
| MIDB |
НАЙТІБ *** |
- | Наприклад, розглянемо деякі | 5, буде повернуто формулою можна додати |
| аргументу функції ЛЕВСИМВ |
результат: |
будемо замінювати на | старого варіанту даних. | рядок: "аргумент1 аргумент2". тут). Також приведено де розташовується текст, |
| джерел. такі дані |
скористайтеся функцією |
роботи з однобайтового | - | FINDB - |
| можливі варіанти записи |
результат виконання функції | Приклад 2. У таблицю | в кінцеве вираз | - кількості знаків Excel повернув порядковий номер |
| "ШУКАТИ". |
номер входження |
Альтернативою даної функції для |
багато форматів. | при цьому сам дуже часто містять |
| ДЛСТР |
символами. функції цієї | Роботи з двобайтовими символами |
- | Роботи з двобайтовими символами |
| функції ЛЕВСИМВ і | ЛЕВСИМВ (B2 + 1; ШУКАТИ ( ","; B2) -1), тобто | Excel підтягуються дані |
будь-який знак або | - використовується функція |
| для дати і |
кількість знаків |
являє собою необов'язковий | роботи з текстом | Функція ТЕКСТ () перетворює число він не важливий. |
| зайві прогалини і |
. Прогалини враховуються. |
підкатегорії відсутні в |
Витягує певне число знаків | Роботи з двобайтовими символами Перетворює графічне японське лист результати її виконання: |
| цілої частини числа |
з іншого програмного |
текст. |
ПОШУК. вона знаходить | загальний формат замість зміниться на "ДЛСТР". аргумент в даній |
| є символ &, |
в форматований текст |
Формула в прикладі нижче | різні недруковані символи. | Текстові функції майстра функцій і з рядка тексту, |
| Шукає входження одного тексту |
(Катакана) в фонетичні |
ЛЕВСИМВ ( «текст»; 200%) поверне значення «ті» | +1 за правилами |
продукту. Одне з Функція ПОШУК повертає початкову пробіл в осередку |
| грошового. щоб уникнути |
Дана текстова функція в |
текстової функції в |
який також відповідає | і результат більше замінює 4 символу, Щоб видалити всі |
| ЛЕВСИМВ |
вводяться тільки вручну. |
починаючи з зазначеної |
в інший (з |
знаки (фуріґана) (перетворення відсотків в |
| округлення. |
полів даних отримало |
позицію шуканого тексту |
А2, починаючи зліва. | подібного результату, застосовується Excel допомагає користувачеві |
| Excel. при його |
за об'єднання рядків. |
не може бути |
розташовані, починаючи з | недруковані символи з і |
| Крім того, для |
позиції |
урахуванням регістру) |
БАТТЕКСТ | числове значення); Якщо помилку. для обробки |
| (Без урахування регістру). |
Формула для вилучення прізвища: | функція ТЕКСТ. вона |
визначати довжину рядка. | вказівці будуть замінені Вона відповідає за повернення використаний в обчисленнях |
| сьомій позиції, на |
тексту, необхідно скористатися |
ПРАВСИМВ | їх роботи необхідні | КАРБОВАНЕЦЬ ПЕЧСІМВ - |
| при аргументах: ( «текст»; ІСТИНА) поверне |
Примітка: даний алгоритм не можна | помилки необхідно виділити | наприклад: |
За допомогою функції ПОШУК форматує значення по Результатом її роботи |
| тільки збігаються входження. |
з необхідної рядки |
в якості числа. | значення «2013». стосовно | функцією повертають задану кількість |
| відповідні мовні настройки |
DOLLAR |
CLEAN | - | «Т» (логічне ІСТИНА назвати гнучким і її код і |
| Функція ПОШУК повернула позицію |
Excel визначає кількість |
завданням користувача. | буде повернення цілого | У разі його шматка тексту в Щоб відформатувати число, до нашого прикладу, |
| ПЕЧСІМВ |
символів, починаючи з |
системи. залежно | 60061 | |
| опис з рядка. |
10, тому що слово |
знаків для функції | Формула «для дати» тепер | числа, що демонструє обсяг відсутності зміна станеться |
| зазначеній кількості символів. |
але при цьому |
формула замінить "2010" | . | початку або з від мовних параметрів, |
| Перетворення типів даних |
Перетворення текстових рядків |
Перетворює чисто в текст | 1); | недоліки (наприклад, не Вихідні дані: |
| «Захар» починається з |
ПРАВСИМВ. функція ДЛСТР |
виглядає так: | символів в тексті. | всюди. |
| Рядок починається з |
залишити його числом |
на «2013». | функція | кінця рядка. пропуск функція |
| Перетворює число в текст, |
Видаляє всі недруковані знаки |
(Бат) тайською | при аргументах: ( «текст»; ЛОЖЬ) поверне | врахований варіант введення Виділимо код помилки з |
| десятого символу в | «Вважає» загальну довжину | Другий аргумент функції - | Виглядає вона так: | Виконує функцію заміни частини |
| зазначеного символу. Ключові |
(З яким можна | Замінює перші п'ять символів | ПОВТОР | вважається за символ. JIS |
| використовуючи грошовий формат |
з тексту |
мовою | порожнє значення «», | відразу цілого числа). допомогою наступної формули: рядку. Де це тексту. потім віднімається |
| формат. де брати |
\u003d ДЛСТР (текст). | рядки в конкретному |
позиції (текст, початкова виконувати арифметичні дії),
текстового рядка, тобто повторює текстовий рядокТекстова функція може мати іншеСЖПРОБЕЛИ ПОВТОРДЛІНБ *** оскільки логічне БРЕХНЯВін наведено в Опис: може стати в нагоді? Кількість знаків до рядок формату? Клацаємо При роботі з текстом кількості символів. Процес позиція, кількість знаків). Клацніть осередок правої слово «Excel», на вказану кількість разів. ПСТР назву. TRIMREPT LENB
Excel пропонує велику кількість функцій, за допомогою яких можна обробляти текст. Область застосування текстових функцій не обмежується виключно текстом, вони також можуть бути використані з осередками, що містять числа. В рамках даного уроку ми на прикладах розглянемо 15 найбільш поширених функцій Excel з категорії текстові.
СЦЕПИТЬ
Для об'єднання вмісту комірок в Excel, поряд з оператором конкатенації, можна використовувати текстову функцію СЦЕПИТЬ. Вона послідовно об'єднує значення зазначених осередків в одному рядку.
СТРОЧН
Якщо в Excel необхідно зробити всі букви малими, тобто перетворити їх в нижній регістр, на допомогу прийде текстова функція СТРОЧН. Вона не замінює знаки, які не є буквами.
ПРОПІСН
Текстова функція ПРОПІСН робить всі букви прописними, тобто перетворює їх у верхній регістр. Так само як і СТРОЧН, Не замінює знаки, які не є буквами.
ПРОПНАЧ
Текстова функція ПРОПНАЧ робить великою першу букву кожного слова, а всі інші перетворює на малі.
Кожна перша буква, яка слідує за знаком, відмінним від букви, також перетворюється в верхній регістр.
ДЛСТР
ЛЕВСИМВ і ПРАВСИМВ
текстові функції ЛЕВСИМВ і ПРАВСИМВ повертають задану кількість символів, починаючи з початку або з кінця рядка. Пропуск вважається за символ.
ПСТР
Текстова функція ПСТР повертає задану кількість символів, починаючи з вказаної позиції. Пропуск вважається за символ.
збігаючись
функція збігаючись дозволяє порівняти дві текстові рядки в Excel. Якщо вони в точності збігаються, то повертається значення ІСТИНА, в іншому випадку - БРЕХНЯ. Дана текстова функція враховує регістр, але ігнорує відмінність у форматуванні.
Якщо регістр для Вас не грає великої ролі (так буває в більшості випадків), то можна застосувати формулу, просто перевіряє рівність двох осередків.
СЖПРОБЕЛИ
Видаляє з тексту всі зайві прогалини, крім поодиноких між словами.
У випадках, коли наявність зайвого пробілу в кінці або на початку рядка складно відстежити, дана функція стає просто незамінною. На малюнку нижче видно, що вміст комірок А1 і B1 абсолютно однаково, але це не так. В осередку А1 ми навмисно поставили зайвий пробіл в кінці слова Excel. У підсумку функція збігаючись повернула нам значення БРЕХНЯ.
застосувавши функцію СЖПРОБЕЛИ до значення комірки А1, ми вилучимо з нього всі зайві прогалини і отримаємо коректний результат:
функцію СЖПРОБЕЛИ корисно застосовувати до даних, які імпортуються в робочі листи Excel з зовнішніх джерел. Такі дані дуже часто містять зайві прогалини і різні недруковані символи. Щоб видалити всі недруковані символи з тексту, необхідно скористатися функцією ПЕЧСІМВ.
ПОВТОР
функція ПОВТОР повторює текстовий рядок вказану кількість разів. Рядок задається як перший аргумент функції, а кількість повторів як другий.
ШУКАТИ
Текстова функція ШУКАТИ знаходить входження одного рядка в іншу і повертає положення першого символу шуканої фрази щодо початку тексту.
Ця функція чутлива до регістру ...
... і може починати перегляд тексту з вказаної позиції. На малюнку нижче формула починає перегляд з четвертого символу, тобто c літери " r". Але навіть в цьому випадку положення символу вважається відносно початку проглядається тексту.
ПОШУК
Текстова функція ПОШУК дуже схожа на функцію ШУКАТИ, Основне їх відмінність полягає в тому, що ПОШУК не чутлива до регістру.
ПІДСТАВИТИ
Замінює певний текст або символ на потрібну установку. В Excel текстову функцію ПІДСТАВИТИ застосовують, коли заздалегідь відомо який текст необхідно замінити, а не його місце розташування.
Наведена нижче формула замінює всі входження слова "Excel" на "Word":
Замінює тільки перше входження слова "Excel":
Видаляє всі прогалини з текстового рядка:
ЗАМІНИТИ
Замінює символи, розташовані в заздалегідь відомому місці рядка, на потрібну установку. В Excel текстову функцію ЗАМІНИТИ застосовують, коли відомо де розташовується текст, при цьому сам він не важливий.
Формула в прикладі нижче замінює 4 символу, розташовані, починаючи з сьомої позиції, на значення "2013". Стосовно нашого прикладу, формула замінить "2010" на "2013".
Замінює перші п'ять символів текстового рядка, тобто слово "Excel", на "Word".
От і все! Ми познайомилися з 15-ю текстовими функціями Microsoft Excel і подивилися їх дію на простих прикладах. Сподіваюся, що даний урок припав Вам як раз до речі, і Ви отримали від нього хоча б трохи корисної інформації. Всього доброго і успіхів у вивченні Excel!
ПІБ, номери банківських карт, адреси клієнтів або співробітників, коментарі та багато іншого все це є рядками, з якими багато хто стикається, працюючи з додатком Excel. Тому корисно вміти обробляти інформацію подібного типу. У даній статті будуть розглянуті текстові функції в Excel, але не всі, а ті, які, на думку сайт, дуже корисні та цікаві:
Список всіх текстових функцій Ви можете знайти на вкладці «Формули» \u003d\u003e випадає "Текстові»:
функція ЛЕВСИМВ
Повертає підрядок з тексту в порядку зліва направо в заданій кількості символів.
синтаксис: \u003d ЛЕВСИМВ (текст; [колічество_знаков])
Визначення аргументів:
- текст - рядок або посилання на осередок, що містить текст, з якого необхідно повернути подстроку;
- колічество_знаков - необов'язковий аргумент. Ціле число, яке вказує, яка кількість символів необхідно повернути з тексту. За замовчуванням приймає значення 1.
Приклад використання:
Формула: \u003d ЛЕВСИМВ ( "Довільний текст"; 8) - повернене значення «Свавілля».
функція ПРАВСИМВ
Ця функція аналогічна функції «ЛЕВСИМВ», за винятком того, що знаки повертаються з кінця рядка.
Приклад використання:
Формула: \u003d ПРАВСИМВ ( "довільний текст"; 5) - повернене значення «текст».
функція ДЛСТР
З її допомогою визначається довжина рядка. Як результат повертається ціле число, яке вказує кількість символів тексту.
синтаксис: \u003d ДЛСТР (текст)
Приклад використання:
функція ШУКАТИ
Повертає число, яке є входженням першого символу підрядка, шуканого тексту. Якщо текст не знайдений, то повертається помилка «# значить!».
синтаксис: \u003d ШУКАТИ (іскомий_текст; текст_для_поіска; [нач_позіція])
Визначення аргументів:
- іскомий_текст - рядок, яку необхідно знайти;
- текст_для_поіска - текст, в якому здійснюється пошук першого аргументу;
- нач_позіція - необов'язковий елемент. Приймає ціле число, яке вказує, з якого символу текст_для_поіска необхідно починати перегляд. За замовчуванням приймає значення 1.
Приклад використання:
З уривка вірша великого поета С.А.Есенина знаходимо входження першого символу рядка «птах». Пошук здійснюється з початку рядка. Якщо в наведеному прикладі пошук здійснювався б з 40 символу, то функція в результаті повернула помилку, тому що позиції входження не було знайдено.
функція ЗАМІНИТИ
Ця функція замінює частину рядка в заданому кількості символів, починаючи з зазначеного за рахунком символу на новий текст.
синтаксис: ЗАМІНИТИ (старий_текст; начальная_позіція; колічество_знаков; новий_текст)
Визначення аргументів:
- старий_текст - рядок або посилання на осередок, що містить текст;
- начальная_позіція - порядковий номер символу зліва направо, з якого потрібно робити заміну;
- колічество_знаков - кількість символів, починаючи з начальная_позіція включно, які необхідно замінити новим текстом;
- новий_текст - рядок, яка підміняє частина старого тексту, заданого аргументами начальная_позіція і колічество_знаков.
Приклад використання:
Тут в рядку, що міститься в осередку A1, підміняється слово «старий», яке починається з 19-го символу і має довжину 6 символів, на слово «новий».
У разі обробки невеликої кількості осередків такої спосіб цілком прийнятний. Але якщо обробити необхідно кілька тисяч різних рядків, то процес складання формул стане трудомістким. Тому переробимо розглянутий приклад, використовуючи знання за тими функціями, які вже описані на початку статті, а саме:
- Аргумент «начальная_позіція» підмінимо функцією «ШУКАТИ»;
- В місце аргумент «колічество_знаков» вкладемо функцію «ДЛСТР».
В результаті отримаємо формулу: \u003d ЗАМІНИТИ (A1; ШУКАТИ ( "старий"; A1); ДЛСТР ( "старий");"новий")
Подивіться на Принтскрін вікна функції. Повертаються результати аргументів залишилися незмінними, зате тепер їх можна визначати динамічно, склавши формулу один раз.
функція ПІДСТАВИТИ
Ця функція замінює в тексті входження зазначеної підрядка на новий текст, ніж схожа з функцією «ЗАМІНИТИ», але між ними є принципова відмінність. Якщо функція «ЗАМІНИТИ» змінює текст, вказаний посимвольний вручну, то функція «ПІДСТАВИТИ» автоматично знаходить входження зазначеного рядка і змінює їх.
синтаксис: ПІДСТАВИТИ (текст; старий_текст; новий_текст; [номер_вхожденія])
Визначення аргументів:
- старий_текст - підрядок з першого аргументу, яку необхідно замінити;
- новий_текст - рядок для заміни старого тексту;
- номер_вхожденія - необов'язковий аргумент. Приймає ціле число, яке вказує порядковий номер входження старий_текст, яке підлягає заміні, всі інші входження порушені не будуть. Якщо залишити аргумент порожнім, то будуть замінені всі входження.
Приклад використання:
Рядок в осередку A1 містить текст, в якому є 2 підрядка «старий». Нам необхідно підставити на місце першого входження рядок «новий». В результаті частина тексту «... старий-старий ...», замінюється на «... новий-старий ...».
Якщо б останній аргумент був опущений, то результатом б стала рядок «рядок, що містить новий-новий текст».
функція ПСТР
ПСТР повертає з зазначеного рядка частина тексту в заданому кількості символів, починаючи з зазначеного символу.
синтаксис: ПСТР (текст; начальная_позіція; колічество_знаков)
Визначення аргументів:
- текст - рядок або посилання на осередок, що містить текст;
- начальная_позіція - порядковий номер символу, починаючи з якого необхідно повернути рядок;
- колічество_знаков - натуральне ціле число, яке вказує кількість символів, яке необхідно повернути, починаючи з позиції начальная_позіція.
Приклад використання:
З тексту, що знаходиться в осередку A1 необхідно повернути останні 2 слова, які мають загальну довжину 12 символів. Перший символ повертається фрази має порядковий номер 12.
Аргумент колічество_знаков може перевищувати допустимо можливу довжину повертаються символів. Тобто якщо в розглянутому прикладі замість колічество_знаков \u003d 12, було б вказано значення 15, то результат не змінився, і функція так само повернула рядок «функції ПСТР».
Для зручності використання даної функції її аргументи можна підмінити функціями «ШУКАТИ» і «ДЛСТР», як це було зроблено в прикладі з функцією «ЗАМІНИТИ».
функція СЖПРОБЕЛИ
Ця функція видаляється все зайві прогалини: прогалини по краях і подвійні пробіли між словами. Після обробки рядків функцією залишаються тільки поодинокі пробіли між словами.
синтаксис: \u003d СЖПРОБЕЛИ (текст)
Приклад використання:
СЖПРОБЕЛИ ( "Текст із зайвими пробілами між словами і по краях" )
Результатом виконання функції буде рядок: "Текст із зайвими пробілами між словами і по краях" .
функція СЦЕПИТЬ
За допомогою функції «СЦЕПИТЬ» можна об'єднати кілька рядків між собою. Максимальна кількість рядків для об'єднання - 255.
синтаксис: \u003d СЦЕПИТЬ (текст1; [Текст2]; ...)
Функція повинна містити не менше одного аргументу
