В тази статия ще ви представя едно изключително творческо използване на вероятно най-непотребната функция в Excel, а именно BAHTTEXT. То ще бъде от полза за всички, които искат Excel да изписва числа с думи. А това, изненадващо за мен, е доста често задаван въпрос.
Между другото, доколкото успях да открия, това е единствената статия в света (извън Тайланд), която показва някакво практическо приложение на функцията BAHTTEXT. Не се хваля, аз само описвам казуса. Но ако някой намери или измисли друго практическо приложение на BAHTTEXT – нека ми пише какво е във формата за контакт.
Казусът
Той е на един приятел, счетоводител във фирма, в която като част от работния процес, в Excel файл се изчисляват някакви стойности за материалите, които предлага фирмата. Освен като числа обаче те трябва да бъдат изписани и с думи. Тоест до числото 8.65 м. трябва да пише с думи „Осем метра и шейсет и пет сантиметра“.
До тук добре – числата се получават в резултат от изчисления в Excel файла. Но думите се пишат на ръка. Защото в Excel няма формула, която да изпише числата с думи. Няма, а много хора търсят такава възможност – за фактури, протоколи, сертификати, товарителници. За какво ли не по закон и вътрешнофирмени правила се изисква изписване и с думи (словом).
Като по-напреднал потребител на Excel, счетоводителят бил помолен от шефа да помогне на колегите си в производството и да им „напише някоя умна формула“, която да обръща числата в думи. Това хубаво, но има и някои ограничения – трябва да е лесно за поддържане от потребители с начални познания и не трябва да има VBA, защото често ексел файловете се пращат на клиенти. Някои от тези клиенти имат политика на забрана за външни файлове с макроси. Просто не минават по пощата такива файлове. Пак по същата причина не могат да се използват и модерните динамични формули, защото не се знае дали клиентите ползват Офис 365.
Решението по принцип
В Excel няма вградена функционалност, която да изписва числа с думи. В гугъл и чатГПТ могат да се намерят варианти на VBA скрипт, които постигат такъв резултат. Но вече казахме, че това не е опция.
Ако трябва да се прави с формули подходът е горе-долу следният:
- Предварително се създава таблица за съответствие, която съдържа числа и срещу тях – как се изписват с думи
- Числото се разделя на цяла част и дробна част с помощта на функции като FLOOR, MOD, LEFT, RIGHT и др.
- Извикват се за цялата част и за дробната част поотделно думите, които съответстват на това число (с VLOOKUP, XLOOKUP, IDEX/MATCH или друга подходяща формула)
- Обединяват се думите за цялата част с „метър/метра“ и думите за дробната част със „сантиметър/сантиметри“. Могат да се използват CONCATENATE, TEXTJOIN или просто оператора амперсанд (&). И задължително играят поне няколко IF функции.
Това принципно решение се усложнява значително, колкото по-големи са числата. Защото просто е невъзможно да се създаде таблица за числа по-големи от един милион – Ексел няма толкова редове. А и ще стане много тежък файл. Затова трябва да се помисли, да се заложат проверки колко цифи е цялата част и в зависимост от това да се разделя на единици, десетици, хилядни и т.н. и за всяка от тях да се ползва отделна таблица. Не е лесно и затова все още не съм го направил.
За щастие дължината на материалите, за които става въпрос варира в по-тесни граници, да речем от нула до 20 метра. Това навело нашия герой на мисълта, че може да направи една голяма таблица от нула сантиметра до 20 метра през един сантиметър и срещу всяко число да изпише с думи стойността, след което да се използва съвсем проста VLOOKUP формула (добре де, за майстори INDEX/MATCH).
Като идея – супер. Но от нула до 20 метра са точно 2000 (словом: две хиляди) реда. Кой ще седне да пише две хиляди пъти един сантиметър, два сантиметра, три сантиметра и т.н.?
На помощ идва функцията BAHTTEXT
За щастие колегата счетоводител съвсем случайно няколко дни преди това попаднал на функцията BAHTETXT. Ако не знаете тя прави точно това – превръща числа в думи, само че на … тайландски. Погледнете на тази картинка
В клетка С4 е числото 16.32, а до нея BAHTTEXT връща като текст „Шестнадесет бата тридесет и два сатанга“. За съжаление не на български, а на чист тайландски език (бат е тайландският лев, а сатанги са техните стотинки).
И ето тук идва това, което ChatGPT още не може, но всеки проходил човек го може много добре – да мисли творчески.
„Хубе де, казал си нашият човек, ще го плесна в Word и ще го преведа с вградения преводач. После ще го върна в Excel, ще заменя бат с метър и сатанг със сантиметър и съм готов.“
Речено – сторено. Да видим сега изпълнението, като ще дадем на готово някои идеи, които са били изчистени в процеса, известен още като „проба-грешка“.
Решението с BAHTTEXT
Най-напред се съставя списък с числата от 0.01 до 20. Това може да стане по много начини, но тук е използвана тази формула:
За тези, които се чудят защо аджеба трябва да се закръгля тази проста сметка – така се избягва един бъг в Excel, открит съвсем случайно при работата. Ще бъде изяснено по-нататък. На който не му се влиза в тези детайли – нека просто да приеме, че така е по-добре.
След това се добавя колона с формулата ВАНТТЕХТ, която да превърне числата в тайландски текст.
Сигурно има читатели, които са запознати добре с възможностите на съвременния Excel и знаят, че той също може да превежда текст. Да, точно така. По принцип можем да селектираме всички редове, които искаме да преведем и от Review > Translate да ги преведем директно в Excel:
Обаче на практика, като се копират всичките две хиляди реда получаваме това съобщение:
Дори да опитаме на по-малки партиди да превеждаме, като се копира преводът и се пейстне в Excel, получава се това:
Не е най-удобната таблица за VLOOKUP, нали. Можем да я оправим лесно с Power Query, ама защо да си го причиняваме?
И ако се чудите колко реда попадат в лимита за превод в Excel – някъде около 150.
На помощ идва Word
Копираме всичките 2,000 (словом: две хиляди) реда, отваряме нов файл в Word и там от Paste Special ги пействаме като Unformatted Text.
Между другото клавишната комбинация в Word за Paste Special е ALT+CTRL+V. А то всъщност и в Excel е същата. Не съм сигурен за PowerPoint обаче.
А за тези, които се чудят защо не ги пейстнем направо с CTRL+V – ами това е един трудно научен урок, който ви спестяваме. Ако се пейстнат така данните, пействаме ги като таблица и после при обратното пействане в Excel, имаме известен проблем.
И така – вече имаме текстовете на тайландски в Word. Сега трябва да отидем на Review > Translate > Translate Document.
Именно това е опцията, която липсва в Excel. Иначе в Word също има лимит за избран текст. За цял документ не е ясно какъв е лимитът, със сигурност 2000 реда не са му проблем. И да, пробвах и с 5000 реда – пак работи.
При появата на това меню натискаме Translate.
И готово – текстът е преведен.
Сега трябва да го копираме и да го пейстнем
Обратно в Excel
Остава само да заменим „бат“ с „метър“ и „сатанг“ със „сантиметър“ с помощта на Find/Replace. За съжаление не е толкова просто. Някои от преводите са интересни.
От къде я измисли тази мрежа – иди го разбери. Явно за целите числа има някаква специфична тайландска дума, която преводачът на Майкрософт превежда като „мрежа“.
Сега – малък тест за наблюдателност: забелязахте ли, кое число липсва в горната картинка? Я вижте пак.
Точно така – 4. Няма да ви мъча – и 20 липсва. Причината е … забавна:
Изкуствен интелект, какво да го правиш.
Няма да отделям повече време на всички подобни „неизправности“. Идеята е да не се доверявате много на изкуствения интелект и да си проверите хубаво резултатите. Quality assurance му викат. Разбира се, винаги има вариант да оправите грешката след като ви се обади ядосан шефът или някой клиент, за да ви питат „Абе какви са тия мрежи тука?“
М/у другото, ако тестът за наблюдателност ви се е сторил лесен – пробвайте се да откривате разликите на Жан Лаплас.
Бонус за любознателните
Ако се чудите защо при създаването на поредните числа беше използвана функцияна ROUND. Вижте какво се получава с просто сумиране:
На двете картинки числата са едни и същи. Вляво са форматирани с два знака след десетичната запетая – вдясно с 15.
В някакъв момент без абсолютно никаква причина се получава малка разлика в 14-я знак след запетаята. И така числото 2.29 си е точно толкова, но 2.30 всъщност е 2.299999999999990. Изобщо нямам представа защо това се получава, но ако не се закръгли до два знака след запетаята, BAHTTEXT може да даде грешка.
Като обобщение
Функцията BAHTEXT в Excel е много полезна и изненадващо за мен – много желана от потребителите. За съжаление тя работи само на тайландски, но с малко творчество и помощ от Word, можем да я накараме да работи и за нас.