צור מסד נתונים באקסל

כשמזכירים מסדי נתונים (DB), הדבר הראשון שעולה בראש, כמובן, הוא כל מיני מילות באזז כמו SQL, Oracle, 1C, או לפחות Access. כמובן שמדובר בתוכנות חזקות מאוד (ויקר לרוב) שיכולות להפוך את העבודה של חברה גדולה ומורכבת לאוטומטית עם הרבה נתונים. הצרה היא שלפעמים פשוט אין צורך בכוח כזה. העסק שלך אולי קטן ועם תהליכים עסקיים פשוטים יחסית, אבל אתה גם רוצה להפוך אותו לאוטומטי. ולחברות קטנות זה לרוב עניין של הישרדות.

מלכתחילה, בואו ננסח את ה-TOR. ברוב המקרים, מסד נתונים לחשבונאות, למשל, מכירות קלאסיות אמורות להיות מסוגלות:

• לשמור בטבלאות מידע על סחורה (מחיר), עסקאות שהושלמו ולקוחות ולקשר טבלאות אלו זו לזו
• שיהיה נוח טפסי קלט נתונים (עם רשימות נפתחות וכו')
• למלא באופן אוטומטי כמה נתונים טפסים מודפסים (תשלומים, חשבונות וכו')
  
• להנפיק את הדרוש דיווחים לשלוט על כל התהליך העסקי מנקודת מבטו של המנהל

Microsoft Excel יכול להתמודד עם כל זה עם קצת מאמץ. בואו ננסה ליישם את זה.

שלב 1. נתונים ראשוניים בצורה של טבלאות

אנו נאחסן מידע על מוצרים, מכירות ולקוחות בשלוש טבלאות (באותו גיליון או על שונות – זה לא משנה). חשוב מהיסוד להפוך אותם ל"שולחנות חכמים" עם גודל אוטומטי, כדי לא לחשוב על זה בעתיד. זה נעשה עם הפקודה עיצוב כטבלה כרטיסייה עמוד הבית (בית - עיצוב כטבלה). בכרטיסייה שתופיע לאחר מכן בנאי (לְעַצֵב) תן לטבלאות שמות תיאוריים בשדה שם טבלה לשימוש מאוחר יותר:

בסך הכל, אנחנו אמורים לקבל שלושה "טבלאות חכמות":

שימו לב שהטבלאות עשויות להכיל נתוני הבהרה נוספים. אז, למשל, שלנו מחירמכיל מידע נוסף על הקטגוריה (קבוצת מוצרים, אריזה, משקל וכו') של כל מוצר והטבלה לקוח - עיר ואזור (כתובת, TIN, פרטי בנק וכו') של כל אחד מהם.

טבלתי מבצעים ישמש אותנו מאוחר יותר להכנסת עסקאות שהושלמו לתוכו.

שלב 2. צור טופס הזנת נתונים

כמובן שניתן להזין נתוני מכירות ישירות לטבלה הירוקה מבצעים , אבל זה לא תמיד נוח וכרוך בהופעת שגיאות ושגיאות הקלדה עקב "הגורם האנושי". לכן, עדיף ליצור טופס מיוחד להזנת נתונים על גיליון נפרד של משהו כזה:

בתא B3, כדי לקבל את התאריך-שעה הנוכחי המעודכן, השתמש בפונקציה ה-TDATA (עַכשָׁיו). אם אין צורך בזמן, אז במקום זאת ה-TDATA ניתן ליישם את הפונקציה היום (היום).

בתא B11, מצא את המחיר של המוצר הנבחר בעמודה השלישית של הטבלה החכמה מחיר באמצעות הפונקציה VPR (VLOOKUP). אם לא נתקלתם בו בעבר, אז תחילה קראו וצפו בסרטון כאן.

בתא B7, אנו זקוקים לרשימה נפתחת עם מוצרים מהמחירון. בשביל זה אתה יכול להשתמש בפקודה נתונים - אימות נתונים (אימות מידע), ציין כאילוץ רשימה (רשימה) ולאחר מכן הזן בשדה מָקוֹר (מָקוֹר) קישור לעמודה שם מהשולחן החכם שלנו מחיר:

באופן דומה, נוצרת רשימה נפתחת עם לקוחות, אך המקור יהיה צר יותר:

=INDIRECT("לקוחות[לקוח]")

פונקציה עקיף (עקיף) יש צורך, במקרה זה, כי Excel, למרבה הצער, אינו מבין קישורים ישירים לטבלאות חכמות בשדה המקור. אבל אותו קישור "עטוף" בפונקציה עקיף במקביל, זה עובד ברעש גדול (עוד על זה היה במאמר על יצירת רשימות נפתחות עם תוכן).

שלב 3. הוספת מאקרו לכניסת מכירות

לאחר מילוי הטופס, עליך להוסיף את הנתונים שהוזנו לתוכו לסוף הטבלה מבצעים . באמצעות קישורים פשוטים, ניצור שורה שתתווסף ממש מתחת לטופס:

הָהֵן. לתא A20 יהיה קישור ל-=B3, לתא B20 יהיה קישור ל-=B7, וכן הלאה.

כעת נוסיף מאקרו אלמנטרי בן 2 שורות שמעתיק את המחרוזת שנוצרה ומוסיף אותה לטבלת המכירות. כדי לעשות זאת, לחץ על השילוב Alt + F11 או כפתור Visual Basic כרטיסייה מפתחים (מפתח). אם כרטיסייה זו אינה גלויה, הפעל אותה תחילה בהגדרות קובץ – אפשרויות – הגדרת סרט (קובץ - אפשרויות - התאמה אישית של סרט). בחלון עורך Visual Basic שנפתח, הכנס מודול ריק חדש דרך התפריט הכנס - מודול והזן את קוד המאקרו שלנו שם:

Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'העתק את שורת הנתונים מהטופס n = Worksheets("Sales").Range("A100000").End(xlUp) . שורה 'קבע את מספר השורה האחרונה בטבלה. גיליונות עבודה של מכירות("מכירות").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues ​​'הדבק בשורה הריקה הבאה גליונות עבודה("טופס קלט").Range("B5,B7,B9"). טופס המשנה של ClearContents  

כעת אנו יכולים להוסיף כפתור לטופס שלנו כדי להפעיל את המאקרו שנוצר באמצעות הרשימה הנפתחת הַבלָעָה כרטיסייה מפתחים (מפתח - הוספה - לחצן):

לאחר שתצייר אותו, לחיצה ממושכת על לחצן העכבר השמאלי, Excel ישאל אותך איזה מאקרו אתה צריך להקצות לו - בחר את המאקרו שלנו הוסף_למכור. אתה יכול לשנות את הטקסט על כפתור על ידי לחיצה ימנית עליו ובחירת הפקודה שנה טקסט.

כעת, לאחר מילוי הטופס, תוכלו פשוט ללחוץ על הכפתור שלנו, והנתונים שהוזנו יתווספו אוטומטית לטבלה מבצעים , ולאחר מכן הטופס נמחק כדי להיכנס לעסקה חדשה.

שלב 4 קישור טבלאות

לפני בניית הדוח, בואו נקשר את הטבלאות שלנו יחד כך שבהמשך נוכל לחשב במהירות מכירות לפי אזור, לקוח או קטגוריה. בגירסאות ישנות יותר של Excel, הדבר ידרוש שימוש במספר פונקציות. VPR (VLOOKUP) להחלפת מחירים, קטגוריות, לקוחות, ערים וכו' לטבלה מבצעים . זה דורש מאיתנו זמן ומאמץ, וגם "אוכל" משאבי אקסל רבים. החל מ-Excel 2013, ניתן ליישם הכל הרבה יותר פשוט על ידי הגדרת קשרים בין טבלאות.

כדי לעשות זאת, בכרטיסייה נתונים (תַאֲרִיך) קליק יחסים (יחסים). בחלון שמופיע, לחץ על הכפתור צור (חדש) ובחר מתוך הרשימות הנפתחות את הטבלאות ושמות העמודות שלפיהם הם צריכים להיות קשורים:

נקודה חשובה: יש לציין את הטבלאות בסדר הזה, כלומר טבלה מקושרת (מחיר) אסור להכיל בעמודת המפתח (שם) מוצרים משוכפלים, כפי שקורה בטבלה מבצעים . במילים אחרות, הטבלה המשויכת חייבת להיות כזו שבה תחפש נתונים באמצעות VPRאם היה בשימוש.

כמובן, השולחן מחובר בצורה דומה מבצעים עם שולחן לקוח לפי עמודה משותפת לקוח:

לאחר הגדרת הקישורים, ניתן לסגור את החלון לניהול קישורים; אתה לא צריך לחזור על הליך זה.

שלב 5. אנו בונים דוחות באמצעות הסיכום

כעת, כדי לנתח מכירות ולעקוב אחר הדינמיקה של התהליך, בואו ניצור, למשל, סוג של דו"ח באמצעות טבלת ציר. הגדר תא פעיל לטבלה מבצעים ובחר את הכרטיסייה בסרט הוספה - PivotTable (הוסף - טבלת ציר). בחלון שנפתח, Excel ישאל אותנו לגבי מקור הנתונים (כלומר טבלה מבצעים ) ומקום להעלות את הדוח (רצוי בגיליון חדש):

הנקודה החיונית היא שיש צורך להפעיל את תיבת הסימון הוסף נתונים אלה למודל הנתונים (הוסף נתונים למודל הנתונים) בתחתית החלון כדי שאקסל יבין שאנחנו רוצים לבנות דוח לא רק על הטבלה הנוכחית, אלא גם להשתמש בכל הקשרים.

לאחר לחיצה על OK חלונית תופיע בחצי הימני של החלון שדות טבלת צירהיכן ללחוץ על הקישור תעשיותלראות לא רק את הנוכחי, אלא את כל "השולחנות החכמים" שנמצאים בספר בבת אחת. ואז, כמו בטבלת הציר הקלאסית, אתה יכול פשוט לגרור את השדות שאנחנו צריכים מכל טבלאות קשורות לאזור סינון, שורות, סטלובצוב or ערכים - ו-Excel יבנה באופן מיידי כל דוח שאנחנו צריכים על הגיליון:

אל תשכח כי יש לעדכן את טבלת הציר מעת לעת (כאשר נתוני המקור משתנים) על ידי לחיצה ימנית עליה ובחירה בפקודה עדכן ושמור (לְרַעֲנֵן), כי הוא לא יכול לעשות זאת אוטומטית.

כמו כן, על ידי בחירת תא כלשהו בסיכום ולחיצה על הכפתור תרשים ציר (תרשים ציר) כרטיסייה אָנָלִיזָה (אָנָלִיזָה) or פרמטרים (אפשרויות) אתה יכול לדמיין במהירות את התוצאות המחושבות בו.

שלב 6. מלא את פריטי ההדפסה

משימה אופיינית נוספת של כל מאגר מידע היא מילוי אוטומטי של טפסים וטפסים מודפסים שונים (חשבוניות, חשבוניות, מעשים וכו'). כבר כתבתי על אחת הדרכים לעשות זאת. כאן אנו מיישמים, למשל, מילוי הטופס לפי מספר חשבון:

ההנחה היא שבתא C2 המשתמש יזין מספר (מספר שורה בטבלה מבצעים , למעשה), ואז הנתונים שאנו צריכים נמשכים באמצעות הפונקציה המוכרת כבר VPR (VLOOKUP) ותכונות מדד (אינדקס).

• כיצד להשתמש בפונקציית VLOOKUP כדי לחפש ולחפש ערכים
• כיצד להחליף את VLOOKUP בפונקציות INDEX ו-MATCH
• מילוי אוטומטי של טפסים וטפסים עם נתונים מהטבלה
• יצירת דוחות עם טבלאות PivotTables