Думи с неправилен правопис, ненужни крайни интервали, нежелани представки, неправилен регистър на буквите и непечатаеми знаци правят лошо първо впечатление. И това дори не е пълен списък на начините, по които вашите данни може да се замърсят. Запретнете ръкави. Време е за основно пролетно почистване на вашите работни листове с Microsoft Excel.
Не винаги имате контрол над формата и типа на данните, които импортирате от външен източник на данни, като например база данни, текстов файл или уеб страница. Преди да можете да анализирате данните, често се налага първо да ги почистите. За щастие, Excel има много функции, които могат да ви помогнат да преобразувате данните в точния формат, който искате. Понякога задачата е проста и има конкретна функция, която върши работата вместо вас. Можете например лесно да използвате програмата за проверка на правописа, за да изчистите думи с грешен правопис в колоните, които съдържат коментари или описания. Или, ако искате да премахнете дублиращи се редове, можете бързо да го направите с помощта на диалоговия прозорец Премахване на дубликати.
В други случаи може да се наложи да обработите една или няколко колони с помощта на формула, за да преобразувате импортираните стойности в нови стойности. Ако например искате да премахнете крайните интервали, можете да създадете нова колона, за да изчистите данните, като използвате формула, запълните надолу новата колона, преобразувате формулите на тази нова колона в стойности и след това премахнете първоначалната колона.
Основните стъпки за почистване на данните са следните:
-
Импортирайте данните от външен източник на данни.
-
Създайте резервно копие на оригиналните данни в отделна работна книга.
-
Уверете се, че данните са в табличен формат от редове и колони, като: във всяка колона има сходни данни, всички колони и редове са видими и няма празни редове в диапазона. За най-добри резултати използвайте таблица на Excel.
-
Извършвате първо задачите, които не изискват обработка на колони, като например проверка на правописа или използване на диалоговия прозорец Търсене и заместване.
-
След това извършете задачите, които изискват обработка на колони. Основните стъпки за обработка на колона са:
-
Вмъкнете нова колона (B) до първоначалната колона (A), която трябва да бъде изчистена.
-
Добавете формула, която ще преобразува данните, в началото на новата колона (B).
-
Запълнете надолу формулата в новата колона (B). В таблица на Excel се създава автоматично изчисляема колона със запълнени надолу стойности.
-
Изберете новата колона (B), копирайте я и след това я поставете като стойности в новата колона (B).
-
Премахнете първоначалната колона (A), което преобразува новата колона от B в А.
-
За периодично изчистване на същия източник на данни помислете да не създадете макрос или да не напишете код за автоматизиране на целия процес. Има също и редица външни добавки, написани от други доставчици, изброени в раздела Други доставчици, които можете да използвате, ако нямате времето или ресурсите да автоматизирате процеса сами.
|
Още информация |
Описание |
|---|---|
|
Показва как да използвате командата Запълване. |
|
|
Създаване и форматиране на таблици Преоразмеряване на таблица чрез добавяне или премахване на редове и колони Използване на изчисляеми колони в таблица на Excel |
Показват как да създадете таблица на Excel и как да добавите или изтриете колони или изчисляеми колони. |
|
Показва няколко начина за автоматизиране на повтарящи се задачи с помощта на макрос. |
Можете да използвате програма за проверка на правописа не само за откриване на думи с неправилен правопис, но и за намиране на стойности, които не са използвани по еднакъв начин, например имена на продукти или фирми, като добавите тези стойности в потребителски речник.
|
Още информация |
Описание |
|---|---|
|
Показва как да коригирате думи с неправилен правопис в работен лист. |
|
|
Използване на потребителски речници за добавяне на думи в програмата за проверка на правописа |
Обяснява как да използвате потребителски речници. |
Дублиращите се редове са често срещан проблем при импортиране на данни. Добра идея е първо да филтрирате за уникални стойности, за да се уверите, че резултатите са каквито ги искате, преди да премахнете дублиращите се стойности.
|
Още информация |
Описание |
|---|---|
|
Филтриране за уникални стойности или премахване на дублиращи се стойности |
Показва две тясно свързани процедури: как да филтрирате за уникални редове и как да премахвате дублиращи се редове. |
Може да искате да премахнете общ начален низ, например етикет, последван от двоеточие и интервал, или суфикс, например вмъкната фраза в края на низа, която е остаряла или ненужна. Можете да направите това, като намерите отделни случаи на този текст и след това ги заместите с отсъствие на текст или друг текст.
|
Още информация |
Описание |
|---|---|
|
Проверка дали клетка съдържа текст (без разпознаване на малки и главни букви) Проверка дали клетка съдържа текст (с разпознаване на малки и главни букви) |
Показват как да използвате командата Намиране и няколко функции за откриване на текст. |
|
Показва как да използвате командата Заместване и няколко функции за премахване на текст. |
|
|
Показват как да използвате диалоговите прозорци Търсене и Заместване. |
|
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
Това са функциите, които можете да използвате, за да извършвате различни задачи за обработка на низове, като например намиране и заместване на подниз в низ, извличане на части от низ или определяне на дължината на низ. |
Понякога текстът се импортира в разнородна форма, особено когато става въпрос за регистъра на буквите. С помощта на една или повече от трите функции за регистър можете да преобразувате текст в малки букви (например имейл адреси), главни букви (например продуктови кодове) или подходящ регистър (например заглавия на книги или имена).
|
Още информация |
Описание |
|---|---|
|
Показва как да използвате трите функции за регистър. |
|
|
Преобразува всички главни букви от текстов низ в малки букви. |
|
|
Преобразува в главна първата буква в текстов низ и всички други букви в текста, които следват след всеки знак, различен от буква. Преобразува всички други букви в малки букви. |
|
|
Преобразува текст в главни букви. |
Понякога текстовите стойности съдържат начални, крайни или множество вградени интервали (стойности 32 и 160 от набора знаци на Unicode) или непечатаеми знаци (стойности от 0 до 31, 127, 129, 141, 143, 144 и 157 от набора знаци на Unicode). Тези знаци могат понякога да доведат до неочаквани резултати при сортиране, филтриране или търсене. Във външния източник на данни например потребителите може да допуснат печатни грешки, добавяйки по невнимание допълнителни интервали, или импортираните текстови данни от външни източници може да съдържат непечатаеми знаци, които са вградени в текста. Тъй като тези знаци не се забелязват лесно, неочакваните резултати може да са трудни за разбиране. За да премахнете тези нежелани знаци, можете да използвате комбинация от функциите TRIM, CLEAN и SUBSTITUTE.
|
Още информация |
Описание |
|---|---|
|
Връща числовия код на първия знак в текстов низ. |
|
|
Премахва първите 32 непечатаеми знака в 7-битовия ASCII код (стойности от 0 до 31) от текста. |
|
|
Премахва 7-битовия ASCII знак за интервал (стойност 32) от текста. |
|
|
Можете да използвате функцията SUBSTITUTE, за да заместите Unicode знаците с по-висока стойност (стойности 127, 129, 141, 143, 144, 157 и 160) със 7-битовите ASCII знаци, за които са предназначени функциите TRIM и CLEAN. |
Има два основни проблема с числата, поради които може да се наложи да изчистите данните: числото случайно е импортирано като текст и знакът минус трябва да бъде променен според стандарта за вашата организация.
|
Още информация |
Описание |
|---|---|
|
Показва как да преобразувате числа, форматирани и съхранени в клетките като текст и поради това потенциално водещи до проблеми при изчисленията или до объркване на реда на сортиране, във формат на число. |
|
|
Преобразува число в текстов формат и добавя символ за валута. |
|
|
Преобразува стойност в текст в определен числов формат. |
|
|
Закръглява число до зададения брой знаци след десетичната запетая, форматира числото в десетичен формат с помощта на запетая и интервали и връща резултата като текст. |
|
|
Преобразува в число текстов низ, представящ число. |
Понеже има толкова много различни формати за дата и понеже тези формати могат да бъдат объркани с номерирани кодове на части или други низове, които съдържат наклонени черти или тирета, датите и часовете често трябва да бъдат преобразувани и преформатирани.
|
Още информация |
Описание |
|---|---|
|
Промяна на системата на датиране, формата или интерпретацията на годината в двуцифрен формат |
Описва как работи системата на датиране в Office Excel. |
|
Показва как да преобразувате различни единици за час. |
|
|
Показва как да преобразувате дати, форматирани и съхранени в клетките като текст и поради това потенциално водещи до проблеми при изчисленията или до объркване на реда на сортиране, във формат на дата. |
|
|
Връща последователно серийно число, което представлява определена дата. Ако форматът на клетката е бил "Общи" преди въвеждането на функцията, резултатът се форматира като дата. |
|
|
Преобразува дата, представена от текст, в пореден номер. |
|
|
Връща десетичното число за зададен час. Ако форматът на клетката е бил "Общ" преди въвеждането на функцията, резултатът се форматира като дата. |
|
|
Връща десетичното число за часа, представен от текстов низ. Десетичното число е стойност в диапазона от 0 (нула) до 0,99999999, представяща часа от 0:00:00 ч. до 23:59:59 ч. |
Често срещана задача след импортирането на данни от външен източник е или обединяване на две или повече колони в една, или разделяне на една колона в две или повече колони. Може например да искате да разделите колона, която съдържа пълно име, на собствено и на фамилно име. Или може да искате да разделите колона, която съдържа поле за адрес, на отделни колони за улица, град, област и пощенски код. Обратното също може да е вярно. Може да искате да обедините колони за собствено и за фамилно име в колона за пълно име или да комбинирате отделни колони за адрес в една колона. Други често срещани стойности, които може да изискват обединяване в една колона или разделяне на няколко колони, включват кодове на продукти, пътища към файлове и IP адреси.
|
Още информация |
Описание |
|---|---|
|
Обединяване на собствени и фамилни имена Обединяване на текст и числа Обединяване на текст с дата или час Обединяване на две или повече колони с помощта на функция |
Показват типични примери за обединяване на стойности от две или повече колони. |
|
Разделяне на текст в различни колони със съветника за преобразуване на текст в колони |
Показва как да използвате този съветник, за да разделите колони въз основа на различни често срещани разделители. |
|
Показва как да използвате функциите LEFT, MID, RIGHT, SEARCH и LEN, за да разделите колона за име на две или повече колони. |
|
|
Показва как да използвате функцията CONCATENATE, оператора & (амперсанд) и съветника за преобразуване на текст в колони. |
|
|
Показва как да използвате командите Обединяване на клетки, Обедини по редове и Обедини и центрирай. |
|
|
Обединява два или повече текстови низа в един текстов низ. |
Повечето функциите за анализ и форматиране в Office Excel предполагат, че данните съществуват в една-единствена плоска, двуизмерна таблица. Понякога може да искате да превърнете редовете в колони, а колоните – в редове. В други случаи данните дори не са структурирани в табличен формат и имате нужда от начин да преобразувате данни от нетабличен в табличен формат.
|
Още информация |
Описание |
|---|---|
|
Връща вертикален диапазон от клетки като хоризонтален диапазон, или обратното. |
|
Понякога администраторите на бази данни използват Office Excel, за да откриват и коригират грешки в съпоставянето при съединяване на две или повече таблици. Това може да включва съгласуване на две таблици от различни работни листове за преглед например на всички записи от двете таблици или за сравняване на таблиците и откриване на редове, които не съвпадат.
|
Още информация |
Описание |
|---|---|
|
Показва често използвани начини за търсене на данни с помощта на функциите за справка. |
|
|
Връща стойност от диапазон, обхващащ един ред или една колона, или от масив. Синтаксисът на функцията LOOKUP има две форми: за вектор и за масив. |
|
|
Търси стойност в най-горния ред на таблица или масив от стойности и след това връща стойност в същата колона от посочен от вас ред в таблицата или масива. |
|
|
Търси стойност в първата колона на масив от таблица и връща стойност в същия ред от друга колона в масива от таблицата. |
|
|
Връща стойност или препратката към стойност от таблица или диапазон. Функцията INDEX има две форми: за масив и за препратка. |
|
|
Връща относителната позиция на елемент в масив, който отговаря на указана стойност в указан ред. Използвайте MATCH вместо една от функциите LOOKUP, когато ви трябва позицията на елемент в диапазон, а не самият елемент. |
|
|
Връща препратка към диапазон, който отстои на зададен брой на редове и колони от клетка или диапазон от клетки. Върнатата препратка може да бъде единична клетка или диапазон от клетки. Можете да зададете броя на върнатите редове и колони. |
По-долу ще видите частичен списък на други доставчици, които предлагат продукти за изчистване на данни по различни начини.
Забележка: Microsoft не предоставя поддръжка за продукти на други доставчици.
|
Доставчик |
Продукт |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
