הערה
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:
- 10 הדרכים המובילות לניקוי הנתונים
- סינון ערכים ייחודיים או הסרת ערכים כפולים
- המרת מספרים המאוחסנים כטקסט למספרים
- המרה של תאריכים המאוחסנים כטקסט לתאריכים
הערה
אם צרכי ניקוי הנתונים שלך מורכבים, או שאין לך זמן או משאבים להפוך את התהליך לאוטומטי בעצמך, שקול להשתמש בספק חיצוני. לקבלת מידע נוסף, חפש "תוכנת ניקוי נתונים" או "איכות נתונים" על-ידי מנוע החיפוש המועדף עליך בדפדפן האינטרנט שלך.
בחירת סוג הנתונים הטוב ביותר בעת הייבוא
במהלך פעולת הייבוא ב- Access, ברצונך לבצע בחירות טובות כך שתקבל מעט (אם קיימות) שגיאות המרה שיחייבו התערבות ידנית. הטבלה הבאה מסכמת את אופן ההמרה של תבניות המספר של Excel וסוגי הנתונים של Access בעת ייבוא נתונים מ- Excel ל- Access, ומציעה כמה עצות לגבי סוגי הנתונים הטובים ביותר לבחירה באשף ייבוא גיליונות אלקטרוניים.
| תבנית מספר של Excel | סוג הנתונים ב- Access | הערות | שיטת עבודה מומלצת |
|---|---|---|---|
| Text | טקסט, תזכיר | סוג הנתונים 'טקסט' של Access מאחסן נתונים אלפאנומריים באורך של עד 255 תווים. סוג הנתונים 'תזכיר' של Access מאחסן נתונים אלפאנומריים באורך של עד 65,535 תווים. | בחר 'תזכיר ' כדי למנוע חיתוך של נתונים. |
| מספר, אחוז, שבר, מדעי | Number | Access כולל סוג נתונים אחד של מספר המשתנה בהתבסס על מאפיין 'גודל שדה' (בית, מספר שלם, מספר שלם ארוך, יחיד, כפול, עשרוני). | בחר Double כדי למנוע שגיאות בהמרת נתונים. |
| תאריך | תאריך | Access ו- Excel משתמשים שניהם באותו מספר תאריך סידורי לאחסון תאריכים. ב- Access, טווח התאריכים גדול יותר: מ- -657,434 (1 בינואר 100 לספירה) עד 2,958,465 (31 בדצמבר 9999 לספירה). מאחר ש- Access אינו מזהה את מערכת התאריכים משנת 1904 (המשמשת ב- Excel עבור Macintosh), עליך להמיר את התאריכים ב- Excel או ב- Access כדי למנוע בלבול. לקבלת מידע נוסף, ראה שינוי מערכת התאריכים, התבנית או פירוש של שנים דו-ספרתיותוייבוא נתונים או קישור לנתונים בחוברת עבודה של Excel. |
בחר 'תאריך'. |
| זמן | שעה | 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. השתמש בפקודות לחצן כדי לשנות שם של טבלה, להוסיף מפתח ראשי, להפוך עמודה קיימת למפתח ראשי ולבטל את הפעולה האחרונה
באפשרותך להשתמש באשף זה לביצוע הפעולות הבאות:
- המר טבלה לקבוצה של טבלאות קטנות יותר וצור באופן אוטומטי קשר גומלין של מפתח ראשי וזר בין הטבלאות.
- הוסף מפתח ראשי לשדה קיים המכיל ערכים ייחודיים, או צור שדה מזהה חדש שמשתמש בסוג הנתונים 'מספור אוטומטי'.
- צור באופן אוטומטי קשרי גומלין כדי לאכוף שלמות הקשרים באמצעות עדכונים מדורגים. מחיקות מדורגות אינן מתווספות באופן אוטומטי כדי למנוע מחיקה בטעות של נתונים, אך ניתן להוסיף בקלות מחיקות מדורגות במועד מאוחר יותר.
- חפש בטבלאות חדשות נתונים מיותרים או כפולים (כגון אותו לקוח עם שני מספרי טלפון שונים) ועדכן זאת לפי הצורך.
- גבה את הטבלה המקורית ושנה את שמה על-ידי הוספת "_OLD" לשמה. לאחר מכן, צור שאילתה הבונה מחדש את הטבלה המקורית, עם שם הטבלה המקורי, כך שכל הטפסים או הדוחות הקיימים המבוססים על הטבלה המקורית יעבדו עם מבנה הטבלה החדש.
לקבלת מידע נוסף, ראה נורמליזציה של הנתונים באמצעות מנתח הטבלאות.
שלב 3: חיבור לנתוני Access מתוך Excel
לאחר שהנתונים ב- Access מנורמלים ונוצרת שאילתה או טבלה המשחזרת את הנתונים המקוריים, כל מה שצריך לעשות הוא להתחבר לנתוני Access מתוך Excel. הנתונים שלך נמצאים כעת ב- Access כמקור נתונים חיצוני, ולכן ניתן לחבר אותם לחוברת העבודה באמצעות חיבור נתונים, שהוא גורם מכיל של מידע המשמש לאיתור מקור הנתונים החיצוני, לכניסה אליו ולגישה אליו. פרטי החיבור מאוחסנים בחוברת העבודה, וניתן גם לאחסן אותם בקובץ חיבור, כגון קובץ חיבור נתונים של Office (ODC) (סיומת שם הקובץ .odc) או קובץ שם מקור נתונים (סיומת .dsn). לאחר שתתחבר לנתונים חיצוניים, תוכל גם לרענן (או לעדכן) באופן אוטומטי את חוברת העבודה של Excel מ- Access בכל פעם שהנתונים מתעדכנים ב- Access.
לקבלת מידע נוסף, ראה ייבוא נתונים ממקורות נתונים חיצוניים (Power Query).
הכנסת הנתונים שלך ל- Access
סעיף זה מנחה אותך לאורך השלבים הבאים של הנורמליזציה של הנתונים: שבירת ערכים בעמודות Salesperson ו- Address לחלקים האטומיים ביותר שלהם, הפרדת נושאים קשורים לטבלאות משלהם, העתקה והדבקה של טבלאות אלה מ- Excel לתוך Access, יצירת קשרי גומלין מרכזיים בין טבלאות Access החדשות שנוצרו, ויצירה והפעלה של שאילתה פשוטה ב- Access להחזרת מידע.
נתונים לדוגמה בצורה לא מנורמלת
גליון העבודה הבא מכיל ערכים לא-אטומיים בעמודה Salesperson ובעמודה Address. יש לפצל את שתי העמודות לשתי עמודות נפרדות או יותר. גליון עבודה זה מכיל גם מידע אודות אנשי מכירות, מוצרים, לקוחות והזמנות. יש לפצל את המידע הזה עוד יותר, לפי נושאים, לטבלאות נפרדות.
| איש מכירות | Order ID | תאריך הזמנה | מזהה מוצר | כמות | מחיר | שם לקוח | Address | טלפון |
|---|---|---|---|---|---|---|---|---|
| Li, Yale | 2349 | 3/4/09 | C-789 | 3 | $7.00 | קפה הארבעה | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| Li, Yale | 2349 | 3/4/09 | C-795 | 6 | $9.75 | קפה הארבעה | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| אדמס, אלן | 2350 | 3/4/09 | א-2275 | 2 | $16.75 | Adventure Works | 1025 קולומביה סירקל קירקלנד, וושינגטון 98234 | 425-555-0185 |
| אדמס, אלן | 2350 | 3/4/09 | F-198 | 6 | $5.25 | Adventure Works | 1025 קולומביה סירקל קירקלנד, וושינגטון 98234 | 425-555-0185 |
| אדמס, אלן | 2350 | 3/4/09 | בי-205 | 1 | $4.50 | Adventure Works | 1025 קולומביה סירקל קירקלנד, וושינגטון 98234 | 425-555-0185 |
| האנס, ג'ים | 2351 | 3/4/09 | C-795 | 6 | $9.75 | קונטוסו בע”מ | 2302 שדרות הרווארד בלוויו, וושינגטון 98227 | 425-555-0222 |
| האנס, ג'ים | 2352 | 3/5/09 | א-2275 | 2 | $16.75 | Adventure Works | 1025 קולומביה סירקל קירקלנד, וושינגטון 98234 | 425-555-0185 |
| האנס, ג'ים | 2352 | 3/5/09 | D-4420 | 3 | $7.25 | Adventure Works | 1025 קולומביה סירקל קירקלנד, וושינגטון 98234 | 425-555-0185 |
| קוך, ריד | 2353 | 3/7/09 | א-2275 | 6 | $16.75 | קפה הארבעה | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
| קוך, ריד | 2353 | 3/7/09 | C-789 | 5 | $7.00 | קפה הארבעה | 7007 Cornell St Redmond, WA 98199 | 425-555-0201 |
מידע בחלקיו הקטנים ביותר: נתונים אטומיים
בעת עבודה עם הנתונים בדוגמה זו, באפשרותך להשתמש בפקודה 'טקסט לעמודה ' ב- Excel כדי להפריד את החלקים ה"אטומיים" של תא (כגון כתובת רחוב, עיר, מדינה ומיקוד) לעמודות נפרדות.
הטבלה הבאה מציגה את העמודות החדשות באותו גליון עבודה לאחר שפוצלו כדי להפוך את כל הערכים לאטומים. שים לב שהמידע בעמודה 'איש מכירות' פוצל לעמודות 'שם משפחה' ו'שם פרטי', ושהמידע בעמודה 'כתובת' פוצל לעמודות 'כתובת רחוב', 'עיר', 'מדינה' ו'מיקוד'. נתונים אלה נמצאים ב"צורה הנורמלית הראשונה".
| Last Name | First Name | כתובת רחוב | City | State | מיקוד |
|---|---|---|---|---|---|
| Li | ייל | 2302 שדרות הרווארד | חיפה | WA | 98227 |
| Adams | אנונימי | 1025 מעגל קולומביה | Kirkland | WA | 98234 |
| Hance | שי | 2302 שדרות הרווארד | חיפה | WA | 98227 |
| קוך | ריד | 7007 Cornell St, רדמונד | Redmond | WA | 98199 |
חלוקת נתונים לנושאים מאורגנים ב- Excel
הטבלאות הרבות של הנתונים לדוגמה הבאות מציגות את אותו מידע מגליון העבודה של Excel לאחר שהוא פוצל לטבלאות עבור אנשי מכירות, מוצרים, לקוחות והזמנות. עיצוב הטבלה אינו סופי, אך הוא בדרך הנכונה.
הטבלה אנשי מכירות מכילה מידע אודות אנשי מכירות בלבד. שים לב שלכל רשומה יש מזהה ייחודי (SalesPerson ID). הערך SalesPerson ID ישמש בטבלה Orders כדי לקשר את Orders לאנשי מכירות.
| אנשי מכירות | ||
|---|---|---|
| מזהה איש מכירות | Last Name | First Name |
| 101 | Li | ייל |
| 103 | Adams | אנונימי |
| 105 | Hance | שי |
| 107 | קוך | ריד |
הטבלה Products מכילה רק מידע אודות מוצרים. שים לב שלכל רשומה יש מזהה ייחודי (Product ID). הערך Product ID ישמש לקישור פרטי המוצר לטבלה Order Details.
| מוצרים | |
|---|---|
| מזהה מוצר | מחיר |
| א-2275 | 16.75 |
| בי-205 | 4.50 |
| C-789 | 7.00 |
| C-795 | 9.75 |
| D-4420 | 7.25 |
| F-198 | 5.25 |
הטבלה 'לקוחות' מכילה מידע אודות לקוחות בלבד. שים לב שלכל רשומה יש מזהה ייחודי (Customer ID). הערך 'מזהה לקוח' ישמש לחיבור פרטי הלקוח לטבלה 'הזמנות'.
| Customers | ||||||
|---|---|---|---|---|---|---|
| מזהה צרכן | שם | כתובת רחוב | City | State | מיקוד | טלפון |
| 1001 | קונטוסו בע”מ | 2302 שדרות הרווארד | חיפה | WA | 98227 | 425-555-0222 |
| 1003 | Adventure Works | 1025 מעגל קולומביה | Kirkland | WA | 98234 | 425-555-0185 |
| 1005 | קפה הארבעה | רחוב קורנל 7007 | Redmond | WA | 98199 | 425-555-0201 |
הטבלה 'הזמנות' מכילה מידע אודות הזמנות, אנשי מכירות, לקוחות ומוצרים. שים לב שלכל רשומה יש מזהה (מזהה הזמנה) ייחודי. יש לפצל חלק מהמידע בטבלה זו לטבלה נוספת המכילה פרטי הזמנה כך שהטבלה Orders תכיל ארבע עמודות בלבד — מזהה ההזמנה הייחודי, תאריך ההזמנה, מזהה איש המכירות ומזהה הלקוח. הטבלה המוצגת כאן עדיין לא פוצלה לטבלה Order Details.
| הזמנות | |||||
|---|---|---|---|---|---|
| Order ID | תאריך הזמנה | מזהה SalesPerson | מזהה לקוח | מזהה מוצר | כמות |
| 2349 | 3/4/09 | 101 | 1005 | C-789 | 3 |
| 2349 | 3/4/09 | 101 | 1005 | C-795 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | א-2275 | 2 |
| 2350 | 3/4/09 | 103 | 1003 | F-198 | 6 |
| 2350 | 3/4/09 | 103 | 1003 | בי-205 | 1 |
| 2351 | 3/4/09 | 105 | 1001 | C-795 | 6 |
| 2352 | 3/5/09 | 105 | 1003 | א-2275 | 2 |
| 2352 | 3/5/09 | 105 | 1003 | D-4420 | 3 |
| 2353 | 3/7/09 | 107 | 1005 | א-2275 | 6 |
| 2353 | 3/7/09 | 107 | 1005 | C-789 | 5 |
פרטי ההזמנה, כגון מזהה המוצר והכמות מועברים אל מחוץ לטבלה 'הזמנות' ומאוחסנים בטבלה בשם 'פרטי הזמנה'. זכור שישנן 9 הזמנות, כך שזה הגיוני שיש 9 רשומות בטבלה זו. שים לב כי לטבלה Orders יש מזהה ייחודי (מזהה הזמנה), אליו תפנה מהטבלה Order Details.
העיצוב הסופי של הטבלה Orders אמור להיראות כך:
| הזמנות | |||
|---|---|---|---|
| Order ID | תאריך הזמנה | מזהה SalesPerson | מזהה לקוח |
| 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 |
הטבלה Order Details אינה מכילה עמודות הדורשות ערכים ייחודיים (כלומר, אין מפתח ראשי), ולכן אין בעיה אם חלק מהעמודות, או כולן, יכילו נתונים "עודפים". עם זאת, אף שתי רשומות בטבלה זו לא אמורות להיות זהות לחלוטין (כלל זה חל על כל טבלה במסד נתונים). בטבלה זו, אמורות להיות 17 רשומות — כל אחת מהן תואמת למוצר בהזמנה בודדת. לדוגמה, בהזמנה 2349, שלושה מוצרי C-789 מהווים אחד משני חלקי ההזמנה כולה.
לכן, הטבלה Order Details אמורה להיראות כך:
| פרטי הזמנה | ||
|---|---|---|
| מזהה הזמנה | מזהה מוצר | כמות |
| 2349 | C-789 | 3 |
| 2349 | C-795 | 6 |
| 2350 | א-2275 | 2 |
| 2350 | F-198 | 6 |
| 2350 | בי-205 | 1 |
| 2351 | C-795 | 6 |
| 2352 | א-2275 | 2 |
| 2352 | D-4420 | 3 |
| 2353 | א-2275 | 6 |
| 2353 | C-789 | 5 |
העתקה והדבקה של נתונים מ- Excel אל Access
כעת, לאחר שהמידע אודות אנשי מכירות, לקוחות, מוצרים, הזמנות ופרטי הזמנות חולק לנושאים נפרדים ב- Excel, באפשרותך להעתיק נתונים אלה ישירות ל- Access, שם הם יהפכו לטבלאות.
יצירת קשרי גומלין בין טבלאות Access והפעלת שאילתה
לאחר העברת הנתונים ל- Access, באפשרותך ליצור קשרי גומלין בין טבלאות ולאחר מכן ליצור שאילתות כדי להחזיר מידע אודות נושאים שונים. לדוגמה, באפשרותך ליצור שאילתה המחזירה את מזהה ההזמנה ואת שמות אנשי המכירות עבור הזמנות שהוזנו בין 05/03/09 ו- 08/03/09.
בנוסף, באפשרותך ליצור טפסים ודוחות כדי לאפשר הזנת נתונים וניתוח מכירות ביתר קלות.
זקוק לעזרה נוספת?
תמיד תוכל לשאול מומחה בקהילה הטכנולוגית של Excel או לקבל תמיכה בקהילות.