Забележка
Microsoft Access не поддържа импортиране на данни на Excel с приложен етикет за чувствителност. Като заобиколно решение можете да премахнете етикета, преди да импортирате, и да приложите отново етикета, след като импортирате. За повече информация вижте "Прилагане на етикети за чувствителност към вашите файлове и имейл в Office".
Тази статия ви показва как да преместите вашите данни от Excel в Access и да преобразувате вашите данни в релационни таблици, така че да можете да използвате Microsoft Excel и Access заедно. За да обобщим – Access е най-подходящ за събиране, съхраняване, изпълнение на заявки и споделяне на данни, а Excel е най-подходящ за изчисляване, анализиране и визуализиране на данни.
В две статии – "Използване на Access или Excel за управление на вашите данни " и "Най-важните 10 причини да използвате Access с Excel" – се обсъжда коя програма е най-подходяща за определена задача и как да използвате Excel и Access заедно, за да създадете практично решение.
Когато премествате данни от Excel в Access, има три основни стъпки към процеса.
Забележка
За информация за моделирането на данни и релациите в Access вж. "Основи на проектирането на бази данни".
Стъпка 1: Импортиране на данни от Excel в Access
Импортирането на данни е операция, която може да премине много по-гладко, ако отделите известно време за подготовка и почистване на данните. Импортирането на данни е като преместване в ново жилище. Ако изчистите и организирате притежанията си, преди да се преместите, установяването в новия си дом е много по-лесно.
Изчистете данните, преди да импортирате
Преди да импортирате данни в Access, в Excel е добра идея да:
- Преобразуване на клетки, които съдържат неатомни данни (т.е. множество стойности в една клетка), в няколко колони. Например клетка в колона "Умения", която съдържа множество стойности на умения, като например "C# програмиране", "VBA програмиране" и "Уеб проектиране", трябва да бъде разбита в отделни колони, всяка от които съдържа само една стойност на умение.
- Използвайте командата TRIM, за да премахнете начални, крайни и множество вградени интервали.
- Премахване на непечатаемите знаци.
- Намиране и коригиране на правописни и пунктуационни грешки.
- Премахване на дублиращи се редове или дублиращи се полета.
- Уверете се, че колоните с данни не съдържат смесени формати, особено числа, форматирани като текст, или дати, форматирани като числа.
За повече информация вижте следните помощни теми за Excel:
- Десетте най-добри начина да изчистите данните си
- Филтриране за уникални стойности или премахване на дублиращи се стойности
- Преобразуване на числа, записани като текст, в числа
- Преобразуване на дати, съхранени като текст, в дати
Забележка
Ако вашите нужди от почистване на данни са сложни или нямате времето или ресурсите да автоматизирате процеса сами, може да помислите за използването на друг доставчик. За повече информация потърсете "софтуер за почистване на данни" или "качество на данните" от любимата си търсачка във вашия уеб браузър.
Избор на най-добрия тип данни при импортиране
По време на операцията за импортиране в Access искате да направите добър избор, така че да получавате малко (ако има такива) грешки при преобразуване, които ще изискват ръчна намеса. Следващата таблица обобщава как се конвертират числовите формати на Excel и типовете данни на Access, когато импортирате данни от Excel в Access, и предлага някои съвети за най-добрите типове данни за избор в съветника за импортиране на електронни таблици.
| Формат на числата в Excel | Тип на данни на Access | Коментари | Най-добра практика |
|---|---|---|---|
| Text | Текст, паметна бележка | Типът данни на Access "Текст" съхранява буквено-цифрови данни до 255 знака. Типът данни "Паметна бележка на Access съхранява буквено-цифрови данни" до 65 535 знака. | Изберете "Паметна бележка ", за да избегнете отрязване на данни. |
| Число, процент, дроб, научен | "число" | Access има един тип данни "Число", който варира в зависимост от свойството "Размер на полето" (байт, цяло, дълго цяло, единично, двойно, десетично). | Изберете Double , за да избегнете грешки при конвертиране на данни. |
| Дата | Дата | Access и Excel използват един и същ сериен номер на дата за съхраняване на датите. В Access диапазонът от дати е по-голям: от -657 434 (1 януари 100 г. сл.Хр.) до 2 958 465 (31 декември 9999 г.). Тъй като Access не разпознава системата за датиране от 1904 (използвана в Excel за Macintosh), трябва да преобразувате датите или в Excel, или в Access, за да избегнете объркване. За повече информация вж. "Промяна на системата на датиране, формата или интерпретацията на годината в двуцифрен формат" и "Импортиране или свързване към данни в работна книга на Excel". |
Изберете "Дата". |
| Час | Time | И Access, и Excel съхраняват стойностите за време, като използват един и същ тип данни. | Изберете "Час", което обикновено е настройката по подразбиране. |
| Валута, счетоводство | Валута | В Access типът данни "Валута" съхранява данните като 8-байтови числа с точност до четири цифри след десетичния знак и се използва за съхраняване на финансови данни и предотвратяване на закръгляване на стойности. | Изберете "Валута", което обикновено е настройката по подразбиране. |
| булев | Да/Не | Access използва -1 за всички стойности "Да" и 0 за всички стойности "Не", докато Excel използва 1 за всички стойности TRUE и 0 за всички стойности FALSE. | Изберете "Да/Не", което автоматично преобразува базовите стойности. |
| Хипервръзка | Хипервръзка | Хипервръзките в Excel и Access съдържат URL или уеб адрес, върху който можете да щракнете и да го следвате. | Изберете "Хипервръзка", в противен случай Access може да използва текстов тип данни по подразбиране. |
След като данните са в Access, можете да изтриете данните на Excel. Не забравяйте първо да архивирате оригиналната работна книга на Excel, преди да я изтриете.
За повече информация вж. помощната тема за Access Импортиране или свързване към данни в работна книга на Excel.
Лесно добавяне на данни към автоматично
Често срещан проблем на потребителите на Excel е добавянето на данни с еднакви колони в един голям работен лист. Може например да имате решение за проследяване на активи, което е започнало в Excel, но сега е нараснало и включва файлове от много работни групи и отдели. Тези данни могат да бъдат в различни работни листове и работни книги или в текстови файлове, които са канали за данни от други системи. Няма команда за потребителски интерфейс или лесен начин за добавяне на подобни данни в Excel.
Най-доброто решение е да използвате Access, където можете лесно да импортирате и добавяте данни в една таблица с помощта на съветника за импортиране на електронни таблици. Освен това можете да добавяте много данни в една таблица. Можете да запишете операциите за импортиране, да ги добавите като планирани задачи на Microsoft Outlook и дори да използвате макроси, за да автоматизирате процеса.
Стъпка 2: Нормализиране на данните с помощта на съветника за анализ на таблици
На пръв поглед преминаването през процеса на нормализиране на вашите данни може да изглежда обезкуражаваща задача. За щастие, нормализирането на таблиците в Access е процес, който е много по-лесен благодарение на съветника за анализ на таблици.
1. Плъзгане на избрани колони в нова таблица и автоматично създаване на релации
2. Използване на командите за бутони за преименуване на таблица, добавяне на първичен ключ, превръщане на съществуваща колона в първичен ключ и отмяна на последното действие
Можете да използвате този съветник, за да направите следното:
- Преобразувайте таблица в набор от по-малки таблици и автоматично създайте релация между таблиците с основен и чужд ключ.
- Добавете първичен ключ към съществуващо поле, което съдържа уникални стойности, или създайте ново поле ID, което използва типа данни AutoNumber.
- Автоматично създавайте релации за поддържане на целостта на връзките с каскадни актуализации. Каскадни изтривания не се добавят автоматично, за да се предотврати случайно изтриване на данни, но лесно можете да добавите каскадни изтривания по-късно.
- Потърсете в новите таблици излишни или дублирани данни (например един и същ клиент с два различни телефонни номера) и ги актуализирайте според желанието.
- Архивирайте оригиналната таблица и я преименувайте, като добавите "_OLD" към името й. След това създавате заявка, която възстановява първоначалната таблица, с първоначалното име на таблицата, така че всички съществуващи формуляри или отчети, базирани на първоначалната таблица, да работят с новата структура на таблицата.
За повече информация вж. "Нормализиране на вашите данни с помощта на анализатора на таблици".
Стъпка 3: Свързване към данни на Access от Excel
След като данните са нормализирани в Access и е създадена заявка или таблица, които възстановяват първоначалните данни, е просто да се свържете към данните на Access от Excel. Вашите данни сега са в Access като външен източник на данни и следователно могат да бъдат свързани към работната книга чрез връзка за данни, която е контейнер с информация, използвана за намиране, влизане в и достъп до външния източник на данни. Информацията за връзка се съхранява в работната книга и може да се съхранява във файл за връзка, като например файл за връзка с данни на Office (.odc файл с разширение на име на файл) или файл с име на източник на данни (разширение .dsn). След като се свържете с външни данни, можете също автоматично да обновявате (или актуализирате) вашата работна книга на Excel от Access всеки път, когато данните се актуализират в Access.
За повече информация вижте Импортиране на данни от външни източници на данни (Power Query).
Вкарайте данните си в Access
Този раздел ще ви преведе през следните етапи на нормализиране на вашите данни: Разбиване на стойностите в колоните "Продавач" и "Адрес" на най-атомарните им части, разделяне на свързаните обекти в собствени таблици, копиране и поставяне на тези таблици от Excel в Access, създаване на ключови релации между новосъздадените таблици на Access и създаване и изпълнение на проста заявка в Access за връщане на информация.
Примерни данни в ненормализиран вид
Следващият работен лист съдържа неатомарни стойности в колоната "Продавач" и колоната "Адрес". И двете колони трябва да са разделени на две или повече отделни колони. Този работен лист съдържа също информация за продавачи, продукти, клиенти и поръчки. Тази информация трябва да бъде разделена допълнително по предмет в отделни таблици.
| Продавач | ИД на поръчка | Дата на поръчка | ИД на продукт | Кол. | Цена | Име на клиента | Address | Телефон |
|---|---|---|---|---|---|---|---|---|
| Ли, Йейл | 2349 | 3/4/09 | С-789 | 3 | $7.00 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Ли, Йейл | 2349 | 3/4/09 | C-795 | 6 | 9,75 щ.д. | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Адамс, Елън | 2350 | 3/4/09 | A-2275 | 2 | 16,75 щ.д. | Adventure Works | 1025 Колумбия Съркъл Къркланд, WA 98234 | 425-555-0185 |
| Адамс, Елън | 2350 | 3/4/09 | F-198 | 6 | 5,25 щ.д. | Adventure Works | 1025 Колумбия Съркъл Къркланд, WA 98234 | 425-555-0185 |
| Адамс, Елън | 2350 | 3/4/09 | B-205 | 1 | 4,50 щ.д. | Adventure Works | 1025 Колумбия Съркъл Къркланд, WA 98234 | 425-555-0185 |
| Ханс, Джим | 2351 | 3/4/09 | C-795 | 6 | 9,75 щ.д. | Contoso, Ltd. | 2302 Harvard Ave Bellevue, WA 98227 | 425-555-0222 |
| Ханс, Джим | 2352 | 3/5/09 | A-2275 | 2 | 16,75 щ.д. | Adventure Works | 1025 Колумбия Съркъл Къркланд, WA 98234 | 425-555-0185 |
| Ханс, Джим | 2352 | 3/5/09 | D-4420 | 3 | $7.25 | Adventure Works | 1025 Колумбия Съркъл Къркланд, WA 98234 | 425-555-0185 |
| Кох, Рийд | 2353 | 3/7/09 | A-2275 | 6 | 16,75 щ.д. | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Кох, Рийд | 2353 | 3/7/09 | С-789 | 5 | $7.00 | Fourth Coffee | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
Информация в най-малките й части: атомни данни
Работейки с данните в този пример, можете да използвате командата "Текст в колона " в Excel, за да разделите "атомарните" части на клетка (като например адрес, град, държава и пощенски код) в отделни колони.
Следващата таблица показва новите колони в същия работен лист, след като са били разделени, за да направят всички стойности атомарни. Обърнете внимание, че информацията в колоната "Продавач" е разделена на колони "Фамилно име" и "Собствено име", а информацията в колоната "Адрес" е разделена на колони "Улица", "Град", "Щат" и "Пощенски код". Тези данни са в "първа нормална форма".
| Фамилно име | Собствено име | Улица и номер | Град | Щат | Пощенски код |
|---|---|---|---|---|---|
| Li | Йейл | ул. Харвард Авеню 2302 | Белвю | WA | 98227 |
| Кирилов | Елън | 1025 Колумбийски кръг | Къркланд | WA | 98234 |
| Филипов | Найден | ул. Харвард Авеню 2302 | Белвю | WA | 98227 |
| Кох | Рийд | ул. Корнел Стрийт Редмънд 7007 | Редмънд | WA | 98199 |
Разделяне на данни по организирани теми в Excel
Следващите няколко таблици с примерни данни показват същата информация от работния лист на Excel, след като той е разделен на таблици за продавачи, продукти, клиенти и поръчки. Дизайнът на таблицата не е окончателен, но е на прав път.
Таблицата "Продавачи" съдържа само информация за персонала по продажбите. Обърнете внимание, че всеки запис има уникален ИД (ИД на продавач). Стойността на "ИД на продавача" ще се използва в таблицата "Поръчки", за да свържете поръчките към продавачите.
| Продавачи | ||
|---|---|---|
| ИД на продавача | Фамилно име | Собствено име |
| 101 | Li | Йейл |
| 103 | Кирилов | Елън |
| 105 | Филипов | Найден |
| 107 | Кох | Рийд |
Таблицата "Продукти" съдържа само информация за продуктите. Обърнете внимание, че всеки запис има уникален ИД (ИД на продукта). Стойността за ИД на продукт ще се използва за свързване на информацията за продукта към таблицата "Подробни данни за поръчките".
| Продукти | |
|---|---|
| ИД на продукт | Цена |
| A-2275 | 16.75 |
| B-205 | 4.50 |
| С-789 | 7.00 |
| C-795 | 9.75 |
| D-4420 | 7.25 |
| F-198 | 5.25 |
Таблицата "Клиенти" съдържа само информация за клиентите. Обърнете внимание, че всеки запис има уникален ИД (ИД на клиент). Стойността за идентификатора на клиента ще се използва за свързване на информацията за клиенти към таблицата "Поръчки".
| Клиенти | ||||||
|---|---|---|---|---|---|---|
| ИД на клиента | Име | Улица и номер | Град | Щат | Пощенски код | Телефон |
| 1001 | Contoso, Ltd. | ул. Харвард Авеню 2302 | Белвю | WA | 98227 | 425-555-0222 |
| 1003 | Adventure Works | 1025 Колумбийски кръг | Къркланд | WA | 98234 | 425-555-0185 |
| 1005 | Fourth Coffee | ул. Корнел 7007 | Редмънд | WA | 98199 | 425-555-0201 |
Таблицата "Поръчки" съдържа информация за поръчки, продавачи, клиенти и продукти. Обърнете внимание, че всеки запис има уникален ИД (ИД на поръчка). Част от информацията в тази таблица трябва да бъде разделена в допълнителна таблица, която съдържа подробности за поръчките, така че таблицата "Поръчки" да съдържа само четири колони – уникалния ИД на поръчката, датата на поръчката, ИД на продавача и ИД на клиента. Показаната тук таблица все още не е разделена в таблицата "Подробни данни за поръчки".
| Поръчки | |||||
|---|---|---|---|---|---|
| ИД на поръчка | Дата на поръчка | ИД на продавача | ИД на клиента | ИД на продукт | Кол. |
| 2349 | 3/4/09 | 101 | 1005 | С-789 | 3 |
| 2349 | 3/4/09 | 101 | 1005 | C-795 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | A-2275 | 2 |
| 2350 | 3/4/09 | 103 | 1003 | F-198 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | B-205 | 1 |
| 2351 | 3/4/09 | 105 | 1001 | C-795 | 6 |
| 2352 | 3/5/09 | 105 | 1003 | A-2275 | 2 |
| 2352 | 3/5/09 | 105 | 1003 | D-4420 | 3 |
| 2353 | 3/7/09 | 107 | 1005 | A-2275 | 6 |
| 2353 | 3/7/09 | 107 | 1005 | С-789 | 5 |
Подробните данни за поръчките, като например ИД и количество на продукта, се преместват извън таблицата "Поръчки" и се съхраняват в таблица, наречена "Подробни данни за поръчки". Имайте предвид, че има 9 поръчки, така че има смисъл в тази таблица да има 9 записа. Обърнете внимание, че таблицата "Поръчки" има уникален ИД (ИД на поръчка), към който ще има препратка от таблицата "Подробни данни за поръчки".
Окончателният проект на таблицата "Поръчки" трябва да изглежда подобно на следния:
| Поръчки | |||
|---|---|---|---|
| ИД на поръчка | Дата на поръчка | ИД на продавача | ИД на клиента |
| 2349 | 3/4/09 | 101 | 1005 |
| 2350 | 3/4/09 | 103 | 1003 |
| 2351 | 3/4/09 | 105 | 1001 |
| 2352 | 3/5/09 | 105 | 1003 |
| 2353 | 3/7/09 | 107 | 1005 |
Таблицата "Подробно за поръчките" не съдържа колони, които да изискват уникални стойности (т.е. няма първичен ключ), така че е добре някои или всички колони да съдържат "излишни" данни. Обаче никои два записа в тази таблица не трябва да са напълно идентични (това правило важи за всяка таблица в база данни). В тази таблица трябва да има 17 записа – всеки от тях съответства на продукт в отделна поръчка. Например в поръчка 2349 три продукта C-789 съставляват една от двете части на цялата поръчка.
Следователно таблицата "Подробно за поръчките" трябва да изглежда по следния начин:
| Подробни данни за поръчката | ||
|---|---|---|
| ИД на поръчка | ИД на продукт | Кол. |
| 2349 | С-789 | 3 |
| 2349 | C-795 | 6 |
| 2350 | A-2275 | 2 |
| 2350 | F-198 | 6 |
| 2350 | B-205 | 1 |
| 2351 | C-795 | 6 |
| 2352 | A-2275 | 2 |
| 2352 | D-4420 | 3 |
| 2353 | A-2275 | 6 |
| 2353 | С-789 | 5 |
Копиране и поставяне на данни от Excel в Access
Сега, след като информацията за продавачите, клиентите, продуктите, поръчките и подробностите за поръчките е разбита в отделни теми в Excel, можете да копирате тези данни директно в Access, където те ще станат таблици.
Създаване на релации между таблиците на Access и изпълнение на заявка
След като сте преместили данните си в Access, можете да създавате релации между таблиците и след това да създавате заявки, за да връщате информация за различни теми. Можете например да създадете заявка, която връща ИД на поръчката и имената на продавачите за поръчките, въведени между 05.03.09 и 08.03.09.
Освен това можете да създавате формуляри и отчети, за да улесните въвеждането на данни и анализа на продажбите.
Имате нужда от още помощ?
Винаги можете да попитате експерт в техническата общност за Excel или да получите поддръжка в общностите.