Функцията TEXT в Excel е сред малко познатите и почти невлизащи в употреба. А не трябва да е така! Тя позволява на потребителите да преобразуват числа и дати във форматирани текстови низове. В тази статия ще видим синтаксиса на функцията TEXT и няколко примера за нейното използване в различни случаи.
Синтаксис на функцията TEXT
Той е доволно прост:
=TEXT(стойност;форматен_текст)
Аргументът „стойност“ е числото или датата, които трябва да се преобразуват в текст. Аргументът „форматен_текст“ е кодът, който определя как ще изглежда текстовият низ. Аргументът форматен_текст може да включва комбинация от текст и форматиращи кодове, като например „dd/mm/yyyy“ за дати или „0.00“ за числа.
Като много е важно да се уточни, че функцията TEXT в Excel не записва числата с текст, а превръща числата в текст, който представлява поредица от цифри. Тоест ако имаме в клетка А1 числото 1000, резултатът от формулата:
= TEXT(A1;”0”)
Няма да бъде думата „хиляда“, а отново 1000, но този път то ще бъде текст, а не число. Защо понякога се налага да правим тази гимнастика ще видим със следващите примери
Пример1: Големи числа, които не са числа, а кодове
Типичен пример са телефонните номера. Виждате списък с няколко хипотетични телефонни номера. Всеки от тях е едно много голямо число, което в Ексел изглежда по странен начин.
Има купища ситуации, в които от някаква система е изваден такъв списък с телефонни номера. Например Търговският отдел е поискал от ИТ отдела да му даде списък с номерата на клиентите, които не са поръчвали в последните 6 месеца. После тези номера се зареждат в друга система, за да получат въпросните клиенти съобщение, което им предлага отстъпка, ако направят поръчка в следващите 10 дни.
Но втората система иска номерата да са с формат текст, а не число. Ако си задавате въпроса „Защо ИТ не направят скрипт, който автоматично да прехвърля номерата от едната система в другата?“. Той може да има хиляди отговори – защото ИТ не искат/много са заети, защото това се прави за пръв път, колкото да се пробва, защото доставчикът на едната система (или и на двете) иска няколко хиляди евро да даде тази възможност, защото няма време, защото … изберете си.
Ето затова много хора в търговските/маркетинг отдели се борят с големите числа като ги превръщат в текст по следния начин: Отиват на първото число, кликат два пъти с мишката на него (или дават с F2) и удрят Enter
След това повтарят процедурата със следващото, и по-следващото и така нататък до края. Ако са хиляда номера горе-долу за половин час се оправя. Чувал съм, че за такива дейности се ползват стажанти. Както и да е.
На долната картинка можете да видите как бързо, лесно и елегантно се решава въпросът с функцията TEXT в Excel:
Има вариант дори да форматираме числата като телефонен номер:
Пример2: Добавяне на нули (leading zeros)
Долната картинка илюстрира този казус.
Имаме две таблици, всяка съдържаща различна информация за едни и същи продукти. В първата са цените, във втората – датите на последна поръчка. И двете таблици имат код на продукта, но в лявата той е под формата на текст. Всеки код е от пет символа и ако цифрите са по-малко от пет, добавен е съответен брой нули. Във втората таблица кодовете са просто числа.
Въпросът е как при това положение, чрез VLOOKUP, да прехвърлим цените от едната таблица във втората. Решението е показано на долната картинка:
С помощта на функцията TEXT в Excel превръщаме числата в подходящия низ от символи.
Пример3: Автоматизиране на текст
Има един много лесен трик, който се използва за да се направят графики и отчети по-ефектни. Съвсем опростен пример по-долу:
Следим някакъв показател колко процента е от бюджета. В случая 78%. За по-красиво, можем да го изпишем с думи.
А пък, ако си поиграем малко с формулите можем да го направим и да е автоматично, така че следващия месец като обновим стойността на Actual да се промени и текстът автоматично. На теория долната формула трябва да ни даде желания резултат
На практика обаче – получаваме това:
Причината е, че Excel комбинира (concatenate-ва) текст със стойността на изчислението. А всички числови стойности в Excel се представят като дробни числа. Ето защо имаме нужда от функцията TEXT, за да укажем на Excel в какъв формат искаме да ни представи числото.
Пример4: Извеждане на месец или ден от седмицата от дата
Много често при анализ на данни се налага от датата в някаква колона да определим месеца или деня от седмицата, за да групираме по тях. Примерно при анализ на продажбите да видим как се движат те по месеци или ден от седмицата.
Опростен пример виждаме на долната картинка.
Масовата практика, в този случай е да се използват функциите MONTH, съответно WEEKDAY. Но те връщат като резултат число (1 за Януари/Понеделник, 2 за Февруари/Вторник и т.н.). Затова допълнително се прибягва до VLOOKUP, INDEX, CHOOSE или IF, за да се изпише с думи името на месеца/деня от седмицата.
Но има и по-елегантен начин с помощта на функцията TEXT в Excel:
Месец
Ден от седмицата
Тук е важно да се уточни, че ТЕХТ ще върне името на месеца или деня от седмицата на езика, който е зададен за основен в регионалните настройки на Windows. Ако е английски ще имаме January, February и т.н. Ако е на български ще бъде Януари, Февруари и т.н.
Всичко казано до тук в два реда
Функцията TEXT в Excel превръща число в текстов низ. Видяхме четири примера, при които това е от голяма полза. Но със сигурност има много други.