В тази статия ще научим различни начини за добавяне на нули (0s) отпред в Excel.
Сценарий:
Добавяне на нула пред номера в Excel. Excel по подразбиране не приема нули пред номера в клетката. Опитвали ли сте някога да въведете някои данни като 000123 в Excel? Вероятно бързо ще забележите, че Excel автоматично ще премахне водещите нули от входящия номер. Това може да бъде наистина досадно, ако искате тези водещи нули в данните и не знаете как да накарате Excel да ги запази. За щастие има доста начини да добавите номера си с нули в началото. В тази статия ще обясня различни начини за добавяне или запазване на тези водещи нули към вашите числа.
Различни функции за добавяне на нула (0) отпред
Както знаем, функцията TEXT е най -използваната функция за добавяне на 0 отпред, но тук ще обясня още начини за добавяне на нули (0s) към числата.
- TEXT функция
- Функция НАДЯСНО
- BASE функция
- Добавете 0 в стойностите на обобщената таблица
Форматиране като текст
Едно от решенията е преобразуването на формата на числото в текст, защото ако въведем 00xyz, Excel разглежда тази стойност като текст и запазва 0 отпред. Има различни начини за преобразуване на числова клетка в текстов формат.
- Използване на персонализиран формат
- Използване на функцията ТЕКСТ
- Използване на Apostrophe (') в началото на стойността при въвеждане
Персонализиран формат
Нека разберем тези методи един по един. Използването на персонализиран формат за промяна на формата е най -лесният начин да промените вече съществуващите числа в обхвата. Просто изберете диапазона и Използвайте прекия път Ctrl + 1 или изберете Още числов формат от падащия списък в раздела Начало, показващ Общи.
Променете формата от Общ на Текстов.
- Изберете диапазона от клетки, в които искате да въведете водещи нули.
- Отидете на раздела Начало.
- В секцията Числа щракнете върху падащото меню Форматиране.
- Изберете Текст от опциите за формат.
Сега, ако се опитате да въведете числа с водещи нули, те няма да изчезнат, защото се въвеждат като текстови стойности вместо числа. Потребителите на Excel могат да добавят персонализирано форматиране към форматиране на числа с водещи нули.
Те обаче ще изглеждат само с водещи нули. Основните данни няма да бъдат променени в текст с добавените нули.
Добавете персонализиран формат, за да покажете водещи нули.
- Изберете диапазона от клетки, към които искате да добавите водещи нули, и отворете диалоговия прозорец Форматиране на клетки.
- Щракнете с десния бутон и изберете Форматиране на клетки.
- Използвайте клавишната комбинация Ctrl + 1.
- Отидете в раздела Число.
- Изберете Персонализиран от опциите за категория.
- Добавете нов персонализиран формат във въвеждане тип. Ако искате общият брой цифри, включително всички водещи нули, да бъде 6, добавете 000000 като персонализиран формат.
- Натиснете бутона OK.
ТЕКСТ функция
Функцията TEXT ще ви позволи да приложите персонализирано форматиране към всякакви данни за числа, които вече са в електронната ви таблица.
= ТЕКСТ (Стойност, Формат)
- Стойност - Това е стойността, която искате да преобразувате в текст и да приложите форматиране.
- Формат - Това е форматирането, което да се приложи.
= ТЕКСТ (B3, "000000")
Ако искате да добавите нули към число в клетка В3, така че общият брой цифри да е 6, тогава можете да използвате горната формула.
Използване на Apostrophe (') в началото
Можете да принудите Excel да въведе число като текст, като използвате водещ апостроф.
Това означава, че ще можете да запазите тези нули отпред, докато въвеждате данните си.
Този метод е бърз и лесен при въвеждане на данни. Просто въведете знак 'преди числа. Това ще каже на Excel, че данните са предназначени да бъдат текст, а не число.
Когато натиснете Enter, водещите нули ще останат видими в работния лист. „Няма да се вижда в работния лист, но все още е там и може да се види в лентата с формули, когато курсорът на активната клетка е върху клетката.
ПРАВА функция
Друг начин да поставите нулите си напред с формула е използването на функцията НАДЯСНО. Можете да обедините низ от нули към числото и след това да отрежете екстрите с помощта на функцията НАДЯСНО.
Функцията RIGHT ще извлече най -дясните N знака от текстова стойност.
= НАДЯСНО (Текст, [Номер])
- Текст - Това е текстът, от който искате да извлечете знаци.
- Номер (По избор)- Това е броят на знаците за извличане от текста. Ако този аргумент не е въведен, ще бъде извлечен само първият знак.
= НАДЯСНО ("000000" & B3, 6)
Горната формула ще обедини няколко нули до началото на число в клетка В3, след което ще върне най -десните 6 знака, което води до някои водещи нули.
БАЗОВА функция
Ще забележите, че тази статия описва 9 начина за добавяне на водещи нули, но моят видеоклип в YouTube показва само 8 начина.
Това е така, защото не знаех, че можете да използвате функцията BASE за добавяне на водещи нули, докато някой не го спомене в коментарите към видеоклипа.
Функцията BASE ви позволява да преобразувате число в текстово представяне с дадена основа.
Числата, които обикновено използвате, са основа 10, но можете да използвате тази функция, за да преобразувате число от база 10 в представяне на база 2 (двоично).
= BASE (номер, база, [минимална дължина])
- Число - Това е числото, което искате да преобразувате в текстова стойност в друга база.
- База - Това е основата, в която искате да конвертирате стойността.
- Минимална дължина (По избор) - Това е минималната дължина на символите на преобразуваната текстова стойност.
Използвайте формулата
| = БАЗА (B3,10,6) |
Горната формула ще преобразува число в клетка B3 в база 10 (това вече е число на база 10, но това ще го запази като основа 10) и ще го преобразува в текстова стойност с поне 6 знака. Ако преобразуваната стойност е по -малка от 6 знака, тя ще запълни стойността с нули, за да получи минимум 6 знака.
Определено готина нова употреба за функция, която иначе никога не използвам.
Добавете нула (0) само пред числа, а не върху текстови стойности
Тук ще разгледаме сценария, който обхваща както текстови, така и числови стойности в същия списък. Сега Excel смята, че всяка клетка е различна по отношение на формата. Сега как да уведомим Excel, че ако клетката има числова стойност, добавете 0 отпред или иначе, ако клетката има текст или друга стойност, оставете я такава, каквато е.
За това ще използваме комбинация от две функции IF и ISNUMBER функция. Тук имаме някои ценности, които да изпробваме.
Общата формула продължава като
= IF (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Тук
cell_ref: препратка към клетка на съответната клетка
Използвайте формулата в клетката
| = IF (ISNUMBER (F3), "0" & F3, F3) |
Обяснение: Функцията ISNUMBER проверява клетката за номер. Функцията IF проверява дали клетката има номер, след това добавя нула пред числото иначе просто връща стойността на клетката.
Както можете да видите на горното изображение, нулата (0) се добавя пред числото. Нека проверим дали тази функция работи на други стойности. Копирайте формулата в други клетки, изберете клетките, като вземете първата клетка, където вече е приложена формулата, използвайте клавишната комбинация Ctrl+D
той ще запълни стойността с нули, за да получи минимум една нула пред знаците. Определено готина нова употреба за функция, която иначе никога не използвам.
Изчислена колона на Power Pivot
Има и друга опция за добавяне на нули в обобщената таблица. Можете да ги добавите в изчислена колона в Power Pivot. По този начин можете да използвате новата колона в областта Филтър, Редове или Колона на обобщена таблица.
= FORMAT (числа [номер], "000000")
В добавката Power Pivot можете да добавите нова колона и да използвате функцията FORMAT, за да създадете водещи нули в колоната с формула, подобна по-горе. Изчислена колона изчислява стойност за всеки ред, така че няма нужда да обгръщате функцията вътре в клетка. Сега можете да използвате тази нова колона в областта на редовете на обобщена таблица, както всяка друга колона с данни.
Резюме
Може да бъде разочароващо да видите как всичките ви нули изчезват, когато не знаем защо се случва или как да го предотвратим. Както можете да видите, има много начини да се уверите, че всички тези нули остават във вашите данни. Каквото и да е вашето изискване, със сигурност има добро решение за вас.
Ето всички бележки за наблюдение, използващи формулата в Excel
Бележки:
- Формулата може да се използва както за текстове, така и за числа.
- Текстът за намиране и замяна трябва да бъде сортиран в таблица. Тъй като използваме индекси за намиране и замяна, уверете се, че текстът „find“ има същия индекс като текста „replace“. По -добре е да имаме двата списъка един до друг, както имаме във втория пример.
- Общата формула има две функции SUBSTITUTE. Ще ви трябват толкова заместващи функции, колкото трябва да направите.
- заместващите символи помагат при извличането на стойност от поднизовете.
- Функцията поддържа логически оператори като,, = но те се използват с двойни кавички ( " ) с аргументи. Но ако използвате препратка към клетка за критериите, кавичките могат да бъдат игнорирани.
- Аргументът за критерии трябва да използва кавички (") за текстове (като" Gillen ") или числа (като"> 50 ").
Надявам се, че тази статия за Различни начини за добавяне на нули (0s) отпред в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Excel REPLACE vs SUBSTITUTE функция: Функциите REPLACE и SUBSTITUTE са най -неразбраните функции. За да намерим и заменим даден текст, използваме функцията SUBSTITUTE. Където REPLACE се използва за замяна на редица знаци в низ.
Заменете текст от края на низ, започвайки от променлива позиция: За да заменим текст от края на низ, използваме функцията REPLACE. Функцията REPLACE използва позицията на текста в низ за замяна.
Как да проверите дали низ съдържа един от многото текстове в Excel: За да проверим дали низ съдържа някой от множество текстове, използваме тази формула. Използваме функцията SUM, за да обобщим всички съвпадения и след това изпълняваме логика, за да проверим дали низът съдържа някой от множеството низове.
Преброяване на клетки, които съдържат определен текст: Една проста функция COUNTIF ще направи магията. За да преброим броя на множество клетки, които съдържат даден низ, използваме заместващия оператор с функцията COUNTIF.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнявайте задачите си в Excel. Тези преки пътища ще ви помогнат да увеличите ефективността на работата си в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.