הערה
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# programming", "תיכנות VBA" ו"עיצוב אתרים" כדי להפריד בין עמודות שכל אחת מהן מכילה ערך כישורים אחד בלבד.
- השתמש בפקודה TRIM כדי להסיר רווחים מובילים, נגררים ומרווחים מוטבעים מרובים.
- הסרת תווים שאינם מודפסים.
- איתור ותיקון שגיאות איות ופיסוק.
- הסר שורות כפולות או שדות כפולים.
- ודא שעמודות נתונים אינן מכילות תבניות מעורבות, במיוחד מספרים המעוצבים כטקסט או כתאריכים המעוצבים כהמספרים.
לקבלת מידע נוסף, עיין בנושאי העזרה הבאים של Excel:
- 10 הדרכים המובילות לניקוי הנתונים
- סינון ערכים ייחודיים או הסרת ערכים כפולים
- המרת מספרים המאוחסנים כטקסט למספרים
- המרה של תאריכים המאוחסנים כטקסט לתאריכים
הערה
אם צרכי ניקוי הנתונים שלך מורכבים, או שאין לך זמן או משאבים להפוך את התהליך לאוטומטי בעצמך, מומלץ להשתמש בספק חיצוני. לקבלת מידע נוסף, חפש אחר "תוכנה לניקוי נתונים" או "איכות נתונים" על-ידי מנוע החיפוש המועדף עליך בדפדפן האינטרנט.
בחר את סוג הנתונים הטוב ביותר בעת הייבוא
במהלך פעולת הייבוא ב- Access, ברצונך לבצע בחירות טובות כדי לקבל שגיאות המרה (אם קיימות) שידרשו התערבות ידנית. הטבלה הבאה מסכמת את האופן שבו תבניות מספר של Excel וסוגי נתונים של Access מומרים בעת ייבוא נתונים מ- Excel ל- Access, ומציעה כמה עצות לגבי סוגי הנתונים הטובים ביותר לבחר באשף ייבוא גליונות אלקטרוניים.
| תבנית מספר של Excel | סוג הנתונים ב- Access | הערות | שיטת עבודה מומלצת |
|---|---|---|---|
| Text | טקסט, תזכיר | סוג הנתונים טקסט של Access מאחסן נתונים אלפאנומריים עד 255 תווים. סוג הנתונים תזכיר Access מאחסן נתונים אלפאנומריים עד 65,535 תווים. | בחר תזכיר כדי להימנע מחיתוך של נתונים. |
| Number, Percentage, Fraction, Scientific | Number | Access כולל סוג נתונים אחד של מספר המשתנה בהתאם למאפיין גודל שדה (בית, מספר שלם, מספר שלם ארוך, יחיד, כפול, עשרוני). | בחר כפול כדי להימנע משגיאות בהמרת נתונים. |
| תאריך | תאריך | Access ו- Excel משתמשים באותו מספר תאריך סידורי לאחסון תאריכים. ב- Access, טווח התאריכים גדול יותר: מ- -657,434 (1 בינואר, 100 ל- 2,958,465 (31 בדצמבר 9999 A.D.). מאחר ש- Access אינו מזהה את מערכת התאריכים 1904 (המשמשת ב- Excel עבור Macintosh), עליך להמיר את התאריכים ב- Excel או ב- Access כדי למנוע בלבול. לקבלת מידע נוסף, ראה שינוי מערכת התאריכים, התבנית או פענוח השנה הדו-ספרתית וייבוא או קישור לנתונים בחוברת עבודה של Excel. |
בחר תאריך. |
| זמן | שעה | Access ו- Excel מאחסנים ערכי זמן באמצעות אותו סוג נתונים. | בחר שעה, שהיא בדרך כלל ברירת המחדל. |
| מטבע, חשבונאות | מטבע | ב- Access, סוג הנתונים 'מטבע' מאחסן נתונים כמספרים של 8 בתים בדיוק עד ארבעה מקומות עשרוניים, והוא משמש לאחסון נתונים פיננסיים ולמניעת עיגול ערכים. | בחר מטבע, שהיא בדרך כלל ברירת המחדל. |
| בוליאני | כן/לא | Access משתמש ב- -1 עבור כל ערכי Yes ו- 0 עבור כל ערכי No, בעוד ש- Excel משתמש ב- 1 עבור כל ערכי TRUE ו- 0 עבור כל ערכי FALSE. | בחר כן/לא, אשר ממירה באופן אוטומטי ערכים המשמשים כערך תחתון. |
| היפר-קישור | היפר-קישור | היפר-קישור ב- Excel ו- Access מכיל כתובת URL או כתובת אינטרנט שניתן ללחוץ עליה ולעקוב אחריה. | בחר היפר-קישור, אחרת Access עשוי להשתמש בסוג הנתונים 'טקסט' כברירת מחדל. |
לאחר שהנתונים קיימים ב- Access, באפשרותך למחוק את הנתונים של Excel. אל תשכח לגבות תחילה את חוברת העבודה המקורית של Excel לפני מחיקתה.
לקבלת מידע נוסף, עיין בנושא העזרה של Access ייבוא נתונים או קישור לנתונים בחוברת עבודה של Excel.
צירוף נתונים באופן אוטומטי בדרך הנוחה
בעיה נפוצה שבה משתמשי Excel מצרף נתונים עם אותן עמודות לגליון עבודה גדול אחד. לדוגמה, ייתכן שיש לך פתרון מעקב אחר נכסים שהתחילה ב- Excel, אך כעת גדל לכלול קבצים מקבוצות עבודה ומחלקות רבות. נתונים אלה עשויים להיות בגליונות עבודה ובחוברות עבודה שונים, או בקובצי טקסט שהם הזנות נתונים ממערכות אחרות. אין פקודת ממשק משתמש או דרך קלה לצרף נתונים דומים ב- Excel.
הפתרון הטוב ביותר הוא להשתמש ב- Access, שבו ניתן לייבא ולהוסיף בקלות נתונים לטבלה אחת באמצעות אשף ייבוא גליונות אלקטרוניים. יתר על כן, באפשרותך לצרף נתונים רבים לטבלה אחת. באפשרותך לשמור את פעולות הייבוא, להוסיף אותן כמשימות מתוזמנות של Microsoft Outlook ואף להשתמש בפקודות מאקרו כדי להפוך את התהליך לאוטומטי.
שלב 2: נרמול הנתונים באמצעות אשף מנתח הטבלאות
במבט ראשון, התהליך של נרמול הנתונים עשוי להיראות משימה מרתיע. למרבה המזל, נרמול טבלאות ב- Access הוא תהליך קל הרבה יותר, הודות לאשף מנתח הטבלאות.
1.1.1.1 גרור עמודות נבחרות לטבלה חדשה וצור באופן אוטומטי קשרי גומלין
2.2.2.2 שימוש בפקודות לחצן כדי לשנות שם של טבלה, להוסיף מפתח ראשי, להפוך עמודה קיימת למפתח ראשי ולבטל את הפעולה האחרונה
באפשרותך להשתמש באשף זה כדי לבצע את הפעולות הבאות:
- המר טבלה לערכה של טבלאות קטנות יותר וצור באופן אוטומטי קשר גומלין של מפתח ראשי וזרים בין הטבלאות.
- הוסף מפתח ראשי לשדה קיים המכיל ערכים ייחודיים, או צור שדה מזהה חדש המשתמש בסוג הנתונים 'מספור אוטומטי'.
- צור באופן אוטומטי קשרי גומלין כדי לאכוף שלמות הקשרים עם עדכונים מדורגים. מחיקות בהתאם להירארכיית הקשרים אינן מתווספות באופן אוטומטי כדי למנוע מחיקה בטעות של נתונים, אך ניתן להוסיף בקלות מחיקות בהתאם להירארכיית הקשרים במועד מאוחר יותר.
- חפש בטבלאות חדשות נתונים מיותרים או כפולים (כגון אותו לקוח עם שני מספרי טלפון שונים) ועדכן אותם לפי הדרישות.
- גבה את הטבלה המקורית ושנה את שמה על-ידי _OLD "_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 קורנל סנט רדמונד, WA 98199 | 425-555-0201 |
| Li, Yale | 2349 | 3/4/09 | C-795 | 6 | תותים תותים | קפה הארבעה | 7007 קורנל סנט רדמונד, WA 98199 | 425-555-0201 |
| אדמס, אלן | 2350 | 3/4/09 | לוח מקשים 2275 | 2 | תותים תותים | Adventure Works | 1025 מעגל קולומביה קירקלנד, WA 98234 | 425-555-0185 |
| אדמס, אלן | 2350 | 3/4/09 | 198 F-198 | 6 | 5 דולר ארה"ב | Adventure Works | 1025 מעגל קולומביה קירקלנד, WA 98234 | 425-555-0185 |
| אדמס, אלן | 2350 | 3/4/09 | יום שני 205 | 1 | 4.50 דולר ארה"ב | Adventure Works | 1025 מעגל קולומביה קירקלנד, WA 98234 | 425-555-0185 |
| Hance, Jim | 2351 | 3/4/09 | C-795 | 6 | תותים תותים | קונטוסו בע”מ | 2302 Harvard Ave Bellevue, WA 98227 | 425-555-0222 |
| Hance, Jim | 2352 | 3/5/09 | לוח מקשים 2275 | 2 | תותים תותים | Adventure Works | 1025 מעגל קולומביה קירקלנד, WA 98234 | 425-555-0185 |
| Hance, Jim | 2352 | 3/5/09 | D-4420 | 3 | 200 $ לתאריך 2016 | Adventure Works | 1025 מעגל קולומביה קירקלנד, WA 98234 | 425-555-0185 |
| Koch, Reed | 2353 | 3/7/09 | לוח מקשים 2275 | 6 | תותים תותים | קפה הארבעה | 7007 קורנל סנט רדמונד, WA 98199 | 425-555-0201 |
| Koch, Reed | 2353 | 3/7/09 | C-789 | 5 | 7.00 $ | קפה הארבעה | 7007 קורנל סנט רדמונד, WA 98199 | 425-555-0201 |
מידע בחלקים קטן ביותר: נתונים אטומים
עבודה עם הנתונים בדוגמה זו, באפשרותך להשתמש בפקודה טקסט לעמודה ב- Excel כדי להפריד בין חלקי התא ה"אטומי" (כגון כתובת רחוב, עיר, מדינה ומיקוד) לעמודות נפרדות.
הטבלה הבאה מציגה את העמודות החדשות באותו גליון עבודה לאחר פיצולן כדי להפוך את כל הערכים לאוטומיים. שים לב שהמידע בעמודה Salesperson פוצל לעמודות 'שם משפחה' ו'שם פרטי', והמידע בעמודה 'כתובת' פוצל לעמודות 'כתובת רחוב', 'עיר', 'מדינה' ו'מיקוד'. נתונים אלה מופיעים ב"צורה רגילה ראשונה".
| Last Name | First Name | כתובת רחוב | City | State | מיקוד |
|---|---|---|---|---|---|
| Li | ייל | 2302 Harvard Ave | חיפה | WA | 98227 |
| Adams | אלן | עיגול קולומביה 1025 | Kirkland | WA | 98234 |
| Hance | שי | 2302 Harvard Ave | חיפה | WA | 98227 |
| קוך | ריד | 7007 קורנל סנט רדמונד | Redmond | WA | 98199 |
מנתק נתונים לנושאים מאורגנים ב- Excel
מספר טבלאות של נתונים לדוגמה שמופיעים לאחר מכן מציגות את אותו מידע מתוך גליון העבודה של Excel לאחר פיצולו לטבלאות עבור אנשי מכירות, מוצרים, לקוחות והזמנות. עיצוב הטבלה אינו סופי, אך הוא נמצא במסלול הנכון.
הטבלה Salespersons מכילה רק מידע אודות צוות המכירות. שים לב של כל רשומה יש מזהה ייחודי (מזהה SalesPerson). הערך SalesPerson ID ישמש בטבלה Orders כדי לחבר הזמנות אל אנשי מכירות.
| אנשי מכירות | ||
|---|---|---|
| מזהה איש מכירות | Last Name | First Name |
| 101 | Li | ייל |
| 103 | Adams | אלן |
| 105 | Hance | שי |
| 107 | קוך | ריד |
הטבלה Products מכילה מידע אודות מוצרים בלבד. שים לב של כל רשומה יש מזהה ייחודי (מזהה מוצר). הערך 'מזהה מוצר' ישמש לחיבור פרטי מוצר לטבלה Order Details.
| מוצרים | |
|---|---|
| מזהה מוצר | מחיר |
| לוח מקשים 2275 | 16.75 |
| יום שני 205 | 4.50 |
| C-789 | 7.00 |
| C-795 | 9.75 |
| D-4420 | 7.25 |
| 198 F-198 | 5.25 |
הטבלה 'לקוחות' מכילה רק מידע אודות לקוחות. שים לב של כל רשומה יש מזהה ייחודי (מזהה לקוח). ערך מזהה הלקוח ישמש לחיבור פרטי לקוח לטבלה Orders.
| Customers | ||||||
|---|---|---|---|---|---|---|
| מזהה צרכן | שם | כתובת רחוב | City | State | מיקוד | טלפון |
| 1001 | קונטוסו בע”מ | 2302 Harvard Ave | חיפה | WA | 98227 | 425-555-0222 |
| 1003 | Adventure Works | עיגול קולומביה 1025 | Kirkland | WA | 98234 | 425-555-0185 |
| 1005 | קפה הארבעה | קורנל 7007 | Redmond | WA | 98199 | 425-555-0201 |
הטבלה Orders מכילה מידע אודות הזמנות, אנשי מכירות, לקוחות ומוצרים. שים לב של כל רשומה יש מזהה ייחודי (מזהה הזמנה). יש לפצל חלק מהמידע בטבלה זו לטבלה נוספת המכילה פרטי הזמנה כך שהטבלה Orders מכילה ארבע עמודות בלבד - מזהה ההזמנה הייחודי, תאריך ההזמנה, מזהה איש המכירות ומזהה הלקוח. הטבלה המוצגת כאן עדיין לא פוצצלה לטבלה Order Details.
| הזמנות | |||||
|---|---|---|---|---|---|
| Order ID | תאריך הזמנה | מזהה איש מכירות | מזהה לקוח | מזהה מוצר | כמות |
| 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 | 198 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 |
פרטי ההזמנה, כגון מזהה המוצר והכמות מועברים מהטבלה Orders, המאוחסנים בטבלה בשם Order Details. זכור שיש 9 הזמנות, ולכן הגיוני שיש 9 רשומות בטבלה זו. שים לב שלטבלה Orders יש מזהה ייחודי (מזהה הזמנה), שאליו תפנה הטבלה Order Details.
העיצוב הסופי של הטבלה Orders אמור להיראות כך:
| הזמנות | |||
|---|---|---|---|
| Order ID | תאריך הזמנה | מזהה איש מכירות | מזהה לקוח |
| 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 | 198 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 Tech Community או לקבל תמיכה בקהילות.