- Добавете водещи нули, като преобразувате формата в текст
- Добавете водещи нули, като използвате персонализирано форматиране на числа
- Добавете водещи нули, като използвате функцията TEXT
- Добавете водещи нули, като използвате функциите REPT и LEN
- Добавете водещи нули, като използвате персонализирана функция (VBA)
Има ситуации, когато трябва да добавите водещи нули към набор от данни в Excel. Това може да се случи, ако поддържате записи в Excel, като например идентификационни номера на служители или идентификационни номера на транзакции.
Например, може да искате да получите последователен поглед във вашия набор от данни, както е показано по -долу:
В този урок ще научите различни начини за добавяне на водещи нули в Excel:
- Преобразуване на формат в текст
- Използване на персонализирано форматиране на номера
- Използване на функция за текст
- Използване на функциите REPT/LEN
- Използване на VBA
Всеки от тези методи има някои достойнства и недостатъци (обхванати във всеки раздел).
Нека да видим как работи всяка от тези.
Добавете водещи нули, като преобразувате формата в текст
Кога да използвате: Когато имате малък набор от цифрови данни и планирате да направите това редактиране ръчно.
Да предположим, че имате идентификационни номера на служители от маркетинговия отдел, както е показано по -долу, и искате да направите тези идентификационни номера последователни, като добавите водещи нули.
Така че се опитвате да промените идентификатора, като въведете водещи нули (00001 вместо 1).
Но за ваше учудване, Excel го преобразува обратно в 1.
Това се случва, тъй като Excel разбира, че 00001 и 1 са едни и същи числа и трябва да следват същите правила за показване.
Колкото и разочароващо да е за вас, Excel има своите причини.
Така че, за да свършите работата, без да огъвате правилата на Excel, ще трябва да се възползвате от факта, че това правило не важи за форматирането на текст.
Ето какво трябва да направите:
- Изберете клетките, в които искате ръчно да добавите водещи нули.
- Отидете на Начало → Група номера и изберете Текст от падащото меню.
Това е!
Сега, когато въвеждате водещи нули ръчно, Excel би се съгласил с готовност.
Внимание: Когато конвертирате формата в текст, някои функции на Excel няма да работят правилно. Например функцията SUM/COUNT ще игнорира клетката, тъй като е в текстов формат.
Добавете водещи нули, като използвате персонализирано форматиране на числа
Кога да се използва: Когато имате числов набор от данни и искате резултатът да е числов (не текстов).
Когато показвате число в определен формат, това не променя основната стойност на номера. Например, мога да покажа числото 1000 като 1000 или 1000 или 1000,00 или 001000 или 26-09-1902 (четните дати са числа в задната част на Excel).
При всички различни начини за показване на номера стойността на номера никога не се променя. Променя се само начинът на показване.
За да добавим водещи нули, можем да го форматираме, за да го покажем по този начин, докато основната стойност ще остане непроменена.
Ето стъпките за използване на тази техника за добавяне на водещи нули в Excel:
- Изберете клетките, в които искате да добавите водещи нули.
- Отидете в Начало → Група номера и кликнете върху диалоговия панел за стартиране (малка наклонена стрелка в долния десен ъгъл). Това ще отвори диалоговия прозорец Форматиране на клетки. Като алтернатива можете също да използвате клавишната комбинация: Control + 1.
- В диалоговия прозорец Форматиране на клетки, в раздела Номер, изберете Персонализиран в списъка с категории.
- В полето Тип въведете 00000
- Щракнете върху OK.
Това винаги ще показва всички числа като пет цифри, където водещите 0 се добавят автоматично, ако числото е по -малко от 5 цифри. Така 10 ще стане 00010, а 100 ще стане 00100.
В този случай сме използвали шест нули, но ако вашите данни имат числа с повече цифри, тогава трябва да използвате формата съответно.
Забележка: Тази техника ще работи само за числов набор от данни. В случай, че имате идентификационни номера на служители като A1, A2, A3 и т.н., те са текстови и няма да се променят, когато приложите персонализирания формат, както е показано по -горе.
Добавете водещи нули, като използвате функцията TEXT
Кога да използвате: Когато искате резултатът да бъде текст.
TEXT функцията ви позволява да промените стойността на желания формат.
Например, ако искате 1 да се показва като 001, можете да използвате функцията TEXT за това.
Не забравяйте обаче, че функцията TEXT ще промени формата и ще го направи TEXT. Това означава, че когато направите 1 като 001, Excel третира новия резултат като текст с три знака (точно като abc или xyz).
Ето как да добавите водещи нули с помощта на функцията TEXT:
- Ако имате числата в колона A (да речем от A2: A100), след това изберете B2: B100 и въведете следната формула:
= ТЕКСТ (A2, ”00000 ″) - Натиснете Control + Enter, за да приложите формулата към всички избрани клетки.
Това ще покаже всички числа като пет цифри, където водещите 0 се добавят автоматично, ако числото е по -малко от 5 цифри.
Едно от предимствата на преобразуването на данни в текст е, че вече можете да ги използвате във формули за търсене като VLOOKUP или INDEX/MATCH, за да извлечете данните за служител, използвайки неговия/нейния идентификационен номер на служител.
Забележка: Тази техника ще работи само за числов набор от данни. В случай, че имате идентификационни номера на служители като A1, A2, A3 и така нататък, това са текст и няма да се променят, когато приложите персонализирания формат, както е показано по -горе.
Добавете водещи нули, като използвате функциите REPT и LEN
Кога да използвате: Когато имате набор от данни, който е цифров/буквено -цифров и искате резултатът да бъде текст.
Недостатъкът на използването на функцията TEXT беше, че тя ще работи само с числови данни. Но в случай, че имате буквено -цифров набор от данни (да речем A1, A2, A3 и т.н.), тогава функцията TEXT ще се провали.
В такива случаи комбинацията от REPT и LEN функция върши работа.
Ето как да го направите:
- Ако имате числата в колона A (да речем от A2: A100), след това изберете B2: B100 и въведете следната формула:
= REPT (0,5-LEN (A2)) & A2 - Натиснете Control + Enter, за да приложите формулата към всички избрани клетки.
Това ще направи всички числа/низове по 5 знака с водещи нули, където е необходимо.
Ето как работи тази формула:
- LEN (A2) дава дължината на низ/числа в клетката.
- = REPT (0,5-LEN (A2)) ще даде числото 0, което трябва да се добави. Тук използвах 5 във формулата, тъй като това беше максималната дължина на низ/числа в моя набор от данни. Можете да промените това според вашите данни.
- = REPT (0,5-LEN (A2)) & A2 просто ще добави броя на нулите към стойността на клетката. Например, ако стойността в клетката е 123, това ще върне 00123.
Добавете водещи нули, като използвате персонализирана функция (VBA)
Ако добавянето на водещи нули в Excel е нещо, което трябва да правите доста често, използването на персонализирана функция е добра идея.
Ето кода на VBA, който ще създаде проста функция за добавяне на водещи нули:
'Код от Sumit Bansal от http://trumpexcel.com Функция AddLeadingZeroes (ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len (ref) For i = 1 To Length If i <= StrLen Тогава Резултат = Резултат и Мид (ref, i, 1) Друг резултат = "0" & Резултат Край Ако Следващ i AddLeadingZeroes = Функция Краен резултат
Просто добавете този код към прозореца с код на модула и ще можете да го използвате, както всяка друга функция на работен лист.
Или създайте добавка за нея и ще можете да я споделите с колегите си.
