Как да добавите водещи нули в Excel

Този урок обхваща различните методи за добавяне или запазване на водещи нули, както и премахване на водещи нули в Excel.

Всеки път, когато въвеждате или импортирате числа с една или повече водещи нули, като 000652, Excel автоматично премахва тези нули и само самото число се показва в клетките (652). Това е така, защото водещите нули не са необходими за изчисления и не се броят.

Въпреки това, има моменти, когато тези водещи нули са необходими, например когато въвеждате идентификационни номера, телефонни номера, номера на кредитни карти, продуктови кодове или пощенски кодове и т.н. За щастие Excel ни дава няколко начина да добавяме или запазваме водещите нули в клетките. В тази статия ще ви покажем различните начини за добавяне или запазване на водещи нули и премахване на водещи нули.

Добавяне на водещи нули в Excel

По същество има 2 метода, които можете да използвате, за да добавите водещи нули: едно, форматирайте номера си като „Текст“; второ, използвайте персонализирано форматиране, за да добавите водещи нули. Методът, който искате да използвате, може да зависи от това какво искате да направите с номера.

Може да искате да добавите водеща нула, когато въвеждате уникални идентификационни номера, номера на сметки, социални осигуровки или пощенски кодове и т.н. Но няма да използвате тези числа за изчисления или във функции, така че е най-добре да ги преобразувате числа към текст. Никога не бихте сумирали или усреднявали телефонни номера или номера на сметки.

Има няколко начина, по които можете да добавите или добавите нули преди числата, като ги форматирате като текст:

• Промяна на формата на клетката на Текст
• Добавяне на апостроф (‘)
• Използване на функция TEXT
• Използване на функция REPT/LEN
• Използвайте функция CONCATENATE/оператор амперсанд (&)
• Използване на функцията ДЯСНО

Промяна на формата на клетката в текст

Това е един от най-простите начини да добавите водещи нули към вашите числа. Ако просто ще въвеждате числа и искате да запазите водещите нули, докато пишете, тогава това е методът за вас. Като промените формата на клетката от общ или номер в текст, можете да принудите Excel да третира вашите числа като текстови стойности и всичко, което въведете в клетката, ще остане точно същото. Ето как го правите:

Изберете клетката(ите), в която искате да добавите водещи нули. Отидете в раздела „Начало“, щракнете върху падащото поле „Формат“ в групата Числа и изберете „Текст“ от опциите за формат.

Сега, когато въвеждате числата си, Excel няма да изтрие водеща нула от него.

Може да видите малък зелен триъгълник (индикатор за грешка) в горния ляв ъгъл на клетката и когато изберете тази клетка, тя ще ви покаже предупредителен знак, показващ, че сте съхранили номера като текст.

За да премахнете съобщението за грешка, изберете клетката(ите), щракнете върху предупредителния знак и след това изберете „Игнориране на грешка“ от списъка.

Можете също да въведете телефонни номера с интервал или тире между числата, Excel автоматично ще третира тези номера като текст.

Използване на Leading апостроф (‘)

Друг начин да добавите водещи нули в Excel е да добавите апостроф (‘) в началото на числото. Това ще принуди Excel да въведе число като текст.

Просто въведете апостроф преди числа и натиснете „Enter“. Excel ще остави водещите нули непокътнати, но (‘) няма да се вижда в работния лист, освен ако не изберете клетката.

Използване на текстова функция

Горният метод добавя нули към числата, докато ги въвеждате, но ако вече имате списък с числа и искате да поставите водещи нули пред тях, тогава функцията TEXT е правилният метод за вас. Функцията TEXT ви позволява да конвертирате числа в текстови низове, докато прилагате персонализирано форматиране.

Синтаксисът на функцията TEXT:

= ТЕКСТ (стойност, формат_текст)

Където,

• стойност - Това е числовата стойност, която трябва да преобразувате в текст и да приложите форматиране.
• формат_текст – е форматът, който искате да приложите.

С функцията ТЕКСТ можете да посочите колко цифри трябва да има вашият номер. Например, ако искате числата ви да са дълги 8 цифри, въведете 8 нули във втория аргумент на функцията: „00000000“. Ако имате 6-цифрено число в клетка, тогава функцията ще добави ръчно 2 водещи нули и ако имате 2-цифрени числа като 56, останалите ще бъдат нули (00000056).

Например, за да добавите водещи нули и да направите числата 6-цифрени дълги, използвайте тази формула:

=ТЕКСТ(A2,"000000")

Тъй като имаме 6 нули във втория аргумент на формулата, функцията преобразува числовия низ в текстов низ и добавя 5 водещи нули, за да направи низа дълъг 6 цифри.

Забележка: Моля, не забравяйте да поставите кодовете на формата в двойни кавички във функцията.

Сега можете да приложите същата формула към останалите клетки, като плъзнете манипулатора за запълване. Както можете да видите, функцията преобразува числата в текстове и добавя водещи нули към числата, така че общият брой цифри да е 6.

Функцията TEXT винаги ще връща стойност като текстов низ, а не число, така че няма да можете да ги използвате в аритметичните изчисления, но все пак можете да ги използвате във формули за търсене, като VLOOKUP или INDEX/MATCH, за да извлечете подробностите за продукт, използващ продуктови идентификатори.

Използване на функция CONCATENATE/оператор амперсанд (&)

Ако искате да добавите фиксиран брой водещи нули преди всички числа в колона, можете да използвате функцията CONCATENATE или оператора амперсанд (&).

Синтаксис на функцията CONCATENATE:

=КОНКАТИРАНЕ(текст1, [текст2], ...)

Където,

текст1 - Броят нули, които трябва да се вмъкнат преди числото.

текст2 – Оригиналният номер или препратка към клетка

Синтаксис на оператора Ampersand:

=Стойност_1 и Стойност_2 

Където,

Стойност_1 е водещите нули за вмъкване преди числото, а Стойност_2 е числото.

Например, за да добавите само две нули преди число, използвайте някоя от тази формула:

=КОНКАТИРАНЕ("00",A2)

Първият аргумент е две нули („00“), защото искаме да добавим две нули преди числото в A2 (което е вторият аргумент).

Или,

="00" и A2

Тук първият аргумент е 2 нули, последван от оператор „&“, а вторият аргумент е числото.

Както можете да видите, формулата добавя само две водещи нули към всички числа в колона, независимо колко цифри съдържа числото.

И двете формули обединяват определен брой нули преди оригиналните числа и ги съхраняват като текстови низове.

Използване на функция REPT/LEN

Ако искате да добавите водещи нули към цифрови или буквено-цифрови данни и да преобразувате низа в текст, използвайте функцията REPT. Функцията REPT се използва за повторение на символ(и) определен брой пъти. Тази функция може да се използва и за вмъкване на фиксирани числа от водещи нули преди числото.

=REPT(текст, брой_крата)

Където „text“ е символът, който искаме да повторим (в нашия случай „0“), а аргументът „number_times“ е броят пъти, които искаме да повторим този знак.

Например, за да генерирате пет нули преди числа, формулата ще изглежда така:

=REPT(0,5)&A2

Това, което прави формулата, е да повтаря 5 нули и да присъединява низа от числа в A2 и връща резултата. След това формулата се прилага към клетка B2:B6 с помощта на манипулатора за запълване.

Горната формула добавя фиксиран брой нули преди числото, но общата дължина на числото варира в зависимост от числото.

Ако искате да добавите водещи нули навсякъде, където е необходимо, за да създадете низове с дължина на специфичен символ (с фиксирана дължина), можете да използвате функциите REPT и LEN заедно.

Синтаксис:

=REPT(текст, брой_време-LEN(текст))&клетка

Например, за да добавите нули с префикс към стойността в A2 и да направите низ от 5 знака, опитайте тази формула:

=REPT(0,5-LEN(A2))&A2

Тук „LEN(A2)“ получава общата дължина на низа/числата в клетка A2. „5“ е максималната дължина на низовете/числата, които клетката трябва да има. А частта „REPT(0,5-LEN(A2))“ добавя броя на нулите, като извади дължината на низа в A2 от максималния брой нули (5). След това редица 0 се съединяват преди стойността на A2, за да се получи низ с фиксирана дължина.

Използване на функция DIGHT

Друг начин за добавяне на водещи нули преди низ в Excel е използването на функцията RIGHT.

Функцията RIGHT може да добави няколко нули към началото на число и да извлече най-десните N знака от стойността.

Синтаксис:

= НАДЯСНО (текст, брой_знакове)

• текст е клетката или стойността, от която искате да извлечете знаци.
• брой_символи е броят на знаците за извличане от текста. Ако този аргумент не е даден, тогава ще бъде извлечен само първият знак.

За този метод ние свързваме максималния брой нули с препратка към клетка, която съдържа низа в аргумента „text“.

За да създадете 6-цифрено число въз основа на числовия низ в A с водещи нули, опитайте тази формула:

=ДЯСНО("0000000"&A2,6)

Първият аргумент (текст) на формулата добавя 7 нули към стойността в A2 („0000000“&A2) и след това връща най-десните 7 знака, което води до някои водещи нули.

Добавяне на водещи нули чрез персонализирано форматиране на числа

Ако използвате някой от горните методи, за да поставите водещи нули пред числата, винаги ще получавате текстов низ, а не число. И те няма да бъдат много полезни при изчисления или в числови формули.

Най-добрият начин да добавите водещи нули в Excel е да приложите персонализирано числово форматиране. Ако добавите водещи нули, като добавите персонализиран числов формат към клетката, това не променя стойността на клетката, а само начина, по който се показва. Стойността ще остане като число, а не като текст.

За да промените форматирането на числата на клетките, изпълнете следните стъпки:

Изберете клетката или диапазона от клетки, където искате да показвате водещи нули. След това щракнете с десния бутон навсякъде в избрания диапазон и изберете опцията „Форматиране на клетки“ от контекстното меню. Или натиснете клавишите за бърз достъп Ctrl + 1.

В прозореца Форматиране на клетки отидете на раздела „Номер“ и изберете „По избор“ под опциите за категория.

Въведете броя на нулите в полето „Тип:“, за да посочите общия брой цифри, които искате да покажете в клетка. Например, ако искате номерът да е дълъг 6 цифри, въведете „000000“ като код за персонализиран формат. След това щракнете върху „OK“, за да приложите.

Това ще показва водещи нули пред числата и ако числото е по-малко от 6 цифри, то добавя нула пред него.

Числата ще изглеждат само с водещи нули, докато основната стойност ще остане непроменена. Ако изберете клетка с персонализирано форматиране, тя ще ви покаже оригиналното число в лентата с формули

Има много цифрови заместители, които можете да използвате във вашия персонализиран цифров формат. Но има само два основни заместителя, които можете да използвате за добавяне на водещи нули в числата.

• 0 – Това е символът за място, който показва допълнителни нули. Той показва принудителни цифри 0-9, независимо дали цифрата е от значение за стойността или не. Например, ако въведете 2.5 с кода на формат 000.00, той ще покаже 002.50.
• # – Това е заместителят за цифри, който показва незадължителни цифри и не включва допълнителни нули. Например, ако въведете 123 с код за форматиране 000#, той ще покаже 0123.

Освен това всеки препинателен знак или друг знак, който включите в кода на формата, ще бъде показан такъв, какъвто е. Можете да използвате знаци като тире (-), запетая (,), наклонена черта (/) и т.н.

Например, можете също да форматирате номера като телефонни номера, като използвате персонализиран формат.

Кодът за форматиране в диалоговия прозорец Форматиране на клетки:

Резултатът:

Нека приложим този код за форматиране в следния пример:

##0000

Както можете да видите, „0“ ще добави допълнителни нули, докато „#“ не добавя незначителни нули:

Можете също да използвате предварително дефинирани кодове за формат в секцията „Специални формати“ на диалоговия прозорец Форматиране на клетки за пощенски кодове, телефонни номера и социални осигуровки.

Следващата таблица показва числа с водещи нули, където различни кодове на „Специален“ формат се прилагат към различни колони:

Премахване на водещи нули в Excel

Сега, научихте как да добавяте водещи нули в Excel, нека да видим как да премахнете водещите нули от броя на низовете. Понякога, когато импортирате данни от външен източник, числата може да имат префикс нули и да бъдат форматирани като текст. В такива случаи трябва да премахнете водещите нули и да ги преобразувате обратно в числа, за да можете да ги използвате във формули.

Има различни начини, по които можете да премахнете водещите нули в Excel и ние ще ги видим една по една.

Премахнете водещите нули, като промените форматирането на клетката

Ако водещите нули са добавени чрез персонализирано форматиране на числа, тогава можете лесно да ги премахнете, като промените формата на клетките. Можете да разберете дали клетките ви са персонализирани, като погледнете адресната лента (нулите ще се виждат в клетката, а не в адресната лента).

За да премахнете нулите с префикс, изберете клетките с водещи нули, щракнете върху полето „Формат на число“ и изберете опцията за форматиране „Общо“ или „Число“.

Сега водещите нули са изчезнали:

Изтрийте водещите нули, като преобразувате текста в числа

Ако вашите водещи нули са добавени чрез промяна на формата на клетката или чрез добавяне на апострофи преди числата или автоматично добавени при импортиране на данни, най-лесният начин да ги преобразувате в числа е като използвате опцията за проверка на грешки. Ето как го правите:

Можете да използвате този метод, ако числата ви са подравнени вляво и клетките ви имат малък зелен триъгълник (индикатор за грешка) в горния ляв ъгъл на клетките. Това означава, че числата са форматирани като текст.

Изберете тези клетки и ще видите жълто предупреждение в горната дясна част на селекцията. След това щракнете върху опцията „Преобразуване в число“ от падащото меню.

Вашите нули ще бъдат премахнати и числата ще бъдат преобразувани обратно в числовия формат (подравнени вдясно).

Премахване на водещи нули от Умножете/Делите по 1

Друг лесен и най-добър начин за премахване на водещите е чрез умножение или разделяне на числата с 1. Разделянето или умножаването на стойността не променя стойността, а просто преобразува стойността обратно в число и премахва водещите нули.

За да направите това, въведете формулата в примера по-долу в клетка и натиснете ENTER. Водещите нули ще бъдат премахнати и низът ще бъде преобразуван обратно в число.

След това приложете тази формула към други клетки, като използвате манипулатора за запълване.

Можете да постигнете същите резултати, като използвате командата „Специално поставяне“. Ето как:

Въведете числова стойност „1“ в клетка (да кажем в B2) и копирайте тази стойност.

След това изберете клетките, в които искате да премахнете водещите нули. След това щракнете с десния бутон върху селекцията и след това изберете опцията „Специално поставяне“.

В диалоговия прозорец Специално поставяне, под Операция, изберете опцията „Умножаване“ или „Разделяне“ и щракнете върху „OK“.

Това е всичко, водещите ви нули ще бъдат премахнати, оставяйки низовете като числа.

Премахнете водещите нули с помощта на формули

Друг лесен начин за изтриване на нули с префикс е използването на функцията VALUE. Този метод може да бъде полезен независимо дали вашите водещи нули са добавени чрез използване на друга формула или апостроф или чрез персонализирано форматиране.

=СТОЙНОСТ(A1)

Аргументът на формулата може да бъде стойност или препратка към клетка, която има стойността. Формулата премахва водещите нули и преобразува стойността от текст в число. След това приложете формулата към останалите клетки.

Понякога може да искате да премахнете водещите нули, но искате да запазите числата в текстов формат. В такива случаи трябва да използвате функциите TEXT() и VALUE () заедно по следния начин:

=ТЕКСТ(СТОЙНОСТ(A1),"#")

Функцията VALUE преобразува стойността в A1 в число. Но вторият аргумент „#“ преобразува стойността обратно в текстов формат без допълнителни нули. В резултат на това ще получите числата без водещи нули, но все пак в текстов формат (подравнени вляво).

Премахнете водещите нули с помощта на функцията Текст към колони на Excel

Още един начин за премахване на водещите нули е да използвате функцията Текст към колони на Excel.

Изберете диапазона от клетки, които имат числа с водещи нули.

След това отидете на раздела „Данни“ и кликнете върху бутона „Текст в колони“ в групата Инструменти за данни.

Ще се появи съветникът „Преобразуване на текст в колони“. В стъпка 1 от 3 изберете „Delimited“ и щракнете върху „Напред“.

В стъпка 2 от 3 премахнете отметката от всички разделители и щракнете върху „Напред“.

На последната стъпка оставете опцията за формат на данни за колони като „Общ“ и изберете дестинацията (първата клетка от диапазона), където искате вашите числа без водещи нули. След това щракнете върху „Край“

И ще получите числата с премахнати водещи в отделна колона, както е показано по-долу.

Това е всичко.