Постоје ситуације у којима морате да додате почетне нуле у скуп података у Екцелу. То може бити случај ако водите евиденцију у Екцелу, као што су ИД -ови запослених или ИД -ови трансакција.
На пример, можда ћете желети да добијете доследан увид у свој скуп података као што је приказано испод:
У овом водичу ћете научити различите начине додавања почетних нула у Екцел:
- Претварање формата у текст
- Коришћење прилагођеног обликовања бројева
- Коришћење функције текста
- Коришћење РЕПТ/ЛЕН функција
- Коришћење ВБА
Свака од ових метода има неке предности и недостатке (обрађене у сваком одељку).
Погледајмо како функционише сваки од ових.
Додајте водеће нуле претварањем формата у текст
Када користити: Када имате мали скуп нумеричких података, а ово уређивање планирате да обавите ручно.
Претпоставимо да имате ИД -ове запослених у маркетиншком одељењу као што је приказано испод и желите да учините ове ИД -ове доследним додавањем почетних нула.
Зато покушајте да промените ид уношењем почетних нула (00001 уместо 1).
Али на ваше чуђење, Екцел га претвара у 1.
То се дешава јер Екцел схвата да су 00001 и 1 исти бројеви и да би требало да поштују иста правила приказа.
Колико год вас ово фрустрирало, Екцел има своје разлоге.
Дакле, да бисте обавили посао без савијања Екцел правила, мораћете да искористите чињеницу да се ово правило не односи на обликовање текста.
Ево шта треба да урадите:
- Одаберите ћелије у које желите ручно додати почетне нуле.
- Идите на Почетна → Група бројева и са падајућег менија изаберите Текст.
То је то!
Сада, када ручно унесете почетне нуле, Екцел ће се лако придржавати.
Опрез: Када претворите формат у текст, неке Екцел функције неће радити исправно. На пример, функција СУМ/ЦОУНТ би занемарила ћелију јер је у текстуалном формату.
Додајте водеће нуле помоћу прилагођеног обликовања бројева
Када користити: Када имате нумерички скуп података и желите да резултат буде нумерички (не текстуални).
Када приказујете број у одређеном формату, то не мења основну вредност броја. На пример, могу да прикажем број 1000 као 1000 или 1000 или 1000,00 или 001000 или 26-09-1902 (чак и датуми су бројеви у позадини у Екцелу).
На све различите начине приказивања броја, вредност броја се никада не мења. Мења се само начин приказа.
Да бисмо додали почетне нуле, можемо га форматирати тако да то прикаже, док би основна вредност остала непромењена.
Ево корака за коришћење ове технике за додавање почетних нула у Екцел:
- Изаберите ћелије у које желите да додате почетне нуле.
- Идите на Почетна → Група бројева и кликните на покретач дијалога (мала нагнута стрелица у доњем десном углу). Ово ће отворити дијалог Формат Целлс. Алтернативно, можете користити и тастерску пречицу: Цонтрол + 1.
- У дијалогу Обликовање ћелија на картици Број изаберите Прилагођено на листи категорија.
- У поље Типе унесите 00000
- Притисните ОК.
Ако то учините, увек ће се сви бројеви приказивати као пет цифара, где се водеће 0 аутоматски додају ако је број мањи од 5 цифара. Тако би 10 постало 00010, а 100 постало 00100.
У овом случају користили смо шест нула, али ако ваши подаци имају бројеве са више цифара, онда морате користити формат у складу с тим.
Напомена: Ова техника би радила само за нумерички скуп података. У случају да имате ИД -ове запослених попут А1, А2, А3 и тако даље, то су текстови и не би се променили када примените прилагођени формат као што је приказано горе.
Додајте водеће нуле помоћу функције ТЕКСТ
Када користити: Када желите да резултат буде текст.
ТЕКСТ функција вам омогућава да промените вредност у жељени формат.
На пример, ако желите да се 1 прикаже као 001, за то можете користити функцију ТЕКСТ.
Међутим, запамтите да би функција ТЕКСТ променила формат и учинила га ТЕКСТ. То значи да када направите 1 као 001, Екцел нови резултат третира као текст са три знака (баш као абц или киз).
Ево како да додате почетне нуле помоћу функције ТЕКСТ:
- Ако имате бројеве у колони А (рецимо из А2: А100), изаберите Б2: Б100 и унесите следећу формулу:
= ТЕКСТ (А2, 00000 ″) - Притисните Цонтрол + Ентер да бисте применили формулу на све изабране ћелије.
Ово ће приказати све бројеве као пет цифара, где се водеће 0 аутоматски додају ако је број мањи од 5 цифара.
Једна од предности претварања података у текст је та што их сада можете користити у формулама за тражење, као што су ВЛООКУП или ИНДЕКС/МАТЦХ за дохваћање података о запосленику користећи његов ИД запосленика.
Напомена: Ова техника би радила само за нумерички скуп података. У случају да имате ИД -ове запослених попут А1, А2, А3 и тако даље, то су текстови и не би се променили када примените прилагођени формат као што је приказано горе.
Додајте водеће нуле помоћу РЕПТ и ЛЕН функција
Када користити: Када имате скуп података који је нумерички/алфанумерички и желите да резултат буде текст.
Недостатак коришћења функције ТЕКСТ био је у томе што би радила само са нумеричким подацима. Али у случају да имате алфанумерички скуп података (рецимо А1, А2, А3 итд.), Функција ТЕКСТ неће успети.
У таквим случајевима комбинација РЕПТ и ЛЕН функције помаже.
Ево како то учинити:
- Ако имате бројеве у колони А (рецимо из А2: А100), изаберите Б2: Б100 и унесите следећу формулу:
= РЕПТ (0,5-ЛЕН (А2)) & А2 - Притисните Цонтрол + Ентер да бисте применили формулу на све изабране ћелије.
Ово би учинило све бројеве/низове дугим 5 знакова са почетним нулама где год је то потребно.
Ево како ова формула функционише:
- ЛЕН (А2) даје дужину низа/бројева у ћелији.
- = РЕПТ (0,5-ЛЕН (А2)) би дао број 0 који треба додати. Овде сам користио 5 у формули јер је то била максимална дужина низа/бројева у мом скупу података. Ово можете променити према својим подацима.
- = РЕПТ (0,5-ЛЕН (А2)) & А2 би једноставно додали број нула вредности ћелије. На пример, ако је вредност у ћелији 123, ово ће вратити 00123.
Додајте водеће нуле помоћу прилагођене функције (ВБА)
Ако морате често додавати почетне нуле у Екцелу, употреба прилагођене функције је добра идеја.
Ево ВБА кода који ће створити једноставну функцију за додавање почетних нула:
'Код би Сумит Бансал са хттп://трумпекцел.цом Функција АддЛеадингЗероес (реф Ас Ранге, Ленгтх Ас Интегер) Дим и Ас Интегер Дим Ресулт Ас Стринг Дим СтрЛен Ас Интегер СтрЛен = Лен (реф) Фор и = 1 То Ленгтх Иф и <= СтрЛен Тхен Ресулт = Ресулт & Мид (реф, и, 1) Елсе Ресулт = "0" & Ресулт Енд Иф Нект и АддЛеадингЗероес = Функција краја резултата
Једноставно додајте овај код у прозор кода модула и моћи ћете да га користите као и сваку другу функцију радног листа.
Или направите додатак за њега и моћи ћете да га делите са колегама.
