בעת ביצוע משימות מסוימות ב- Excel לפעמים צריך להתמודד עם כמה שולחנות כי הם קשורים גם זה לזה. כלומר, הנתונים מטבלה אחת מתקדמים לאחרים והערכים בכל השולחנות הקשורים מחוברים כאשר הם משתנים.
שולחנות קשורים נוח מאוד לשימוש כדי להתמודד עם כמות גדולה של מידע. מניחים את כל המידע בטבלה אחת, חוץ מזה, אם זה לא הומוגני, לא נוח מאוד. קשה לעבוד עם חפצים כאלה ולחפש אותם. הבעיה שצוינה תוכננה רק לחיסול טבלאות קשורות, המידע בין אשר מופץ, אך במקביל הוא מחובר. טבלאות קשורות עשויות להיות לא רק בתוך גיליון אחד או ספר אחד, אלא גם להיות ממוקם בספרים נפרדים (קבצים). שתי האפשרויות האחרונות בפועל משמשים לעתים קרובות ביותר, שכן המטרה של טכנולוגיה זו היא רק כדי להתרחק הצטברות של נתונים, ואת ההנאה שלהם על דף אחד לא לפתור ביסודו. בואו נלמד כיצד ליצור וכיצד לעבוד עם סוג כזה של ניהול נתונים.
יצירת שולחנות קשורים
קודם כל, בואו להתמקד בשאלה, שבו ניתן ליצור קשר בין שולחנות שונים.שיטה 1: טבלאות ישירות
הדרך הקלה ביותר לאגד נתונים היא שימוש בנוסחאות שבהן יש הפניות לשולחנות אחרים. זה נקרא מחייב ישיר. שיטה זו היא אינטואיטיבית, שכן כאשר היא נקשרת כמעט בדיוק כמו יצירת הפניות לנתונים במערך שולחן אחד.
בואו נראה איך הדוגמה אתה יכול ליצור תקשורת על ידי מחייב ישיר. יש לנו שני שולחנות על שני גיליונות. על אותו שולחן, המשכורת מחושבת באמצעות הנוסחה על ידי הכפלת שיעורי העובדים עבור מקדם יחיד.
על הסדין השני יש טווח שולחן שבו יש רשימה של עובדים עם שכרם. רשימת העובדים בשני המקרים מוצג בסדר אחד.
יש צורך להפוך את הנתונים על ההימורים מן הסדין השני להדק לתוך התאים המתאימים של הראשון.
- על הסדין הראשון, אנו מקצים את התא הראשון של הטור "בית". שמנו את זה את השלט "=". לאחר מכן, לחץ על התווית "גיליון 2", אשר ממוקם בחלק השמאלי של ממשק Excel מעל שורת המצב.
- יש תנועה באזור השני של המסמך. לחץ על התא הראשון בעמודה "בית". לאחר מכן לחץ על הלחצן Enter במקלדת כדי להזין את הנתונים לתא שבו הוקם "שוויון" הותקן בעבר.
- אז יש מעבר אוטומטי לגליון הראשון. כפי שאנו יכולים לראות, הערך של העובד הראשון מן השולחן השני הוא משך לתוך התא המתאים. על ידי התקנת הסמן על תא המכיל הימור, אנו רואים כי הנוסחה הרגילה משמשת להצגת נתונים על המסך. אבל מול הקואורדינטות של התא, מאיפה הנתונים הם פלט, יש ביטוי "list2!", אשר מציין את שם אזור המסמך שבו הם נמצאים. הנוסחה הכללית במקרה שלנו נראית כך:
= List2! B2
- עכשיו אתה צריך להעביר נתונים על שיעורי כל עובדים אחרים של הארגון. כמובן, זה יכול להיעשות באותו אופן שבו אנו מילא את המשימה עבור העובד הראשון, אבל בהתחשב כי שני רשימות העובדים ממוקמים באותו סדר, המשימה יכולה להיות פשוטה משמעותית מואצת על ידי החלטתו. זה יכול להיעשות על ידי העתקת הנוסחה לטווח למטה. בשל העובדה כי ההתייחסויות ל- Excel הם יחסית, בעת העתקת הערכים שלהם, משמרת הערכים נותרו כי אנחנו צריכים. נוהל העתק עצמו יכול להתבצע באמצעות סמן מילוי.
אז, שמנו את הסמן לאזור הנכון התחתון של האלמנט עם הנוסחה. לאחר מכן, הסמן חייב להמיר את סמן מילוי בצורת צלב שחור. אנו מבצעים את מהדק של לחצן העכבר השמאלי וממשיכים את הסמן למספר הטור.
- כל הנתונים מתוך עמודה דומה על גיליון 2 נמשכו לטבלה על גיליון 1. כאשר הנתונים משתנים על גיליון 2, הם ישתנו באופן אוטומטי על הראשון.
שיטה 2: שימוש באינדקס הסורגים של המפעילים - חיפוש
אבל מה לעשות אם רשימת העובדים במערכי טבלה אינה ממוקמת באותו סדר? במקרה זה, כאמור מוקדם יותר, אחת האפשרויות היא להתקין את הקשר בין כל אחד מהתאים אלה שיש לשויך באופן ידני. אבל זה מתאים למעט שולחנות קטנים. עבור טווחים מסיביים, אפשרות זו תהיה הטובה ביותר לקחת הרבה זמן ביישום, ובגרוע - בפועל זה יהיה בדרך כלל לא מציאותי. אבל בעיה זו ניתן לפתור באמצעות חבורה של מפעילי אינדקס - חיפוש. בואו נראה איך זה יכול להיעשות על ידי turning את הנתונים בלוחות שבהם השיחה היתה בשיטה הקודמת.
- אנו מדגישים את האלמנט הראשון בעמודה "בית". עבור אל אשף הפונקציות על ידי לחיצה על סמל "הכנס פונקציה".
- באשף של פונקציות בקבוצה "קישורים ומערכים" אנו מוצאים ולהקצות את השם "אינדקס".
- למפעיל זה שתי צורות: טופס לעבודה עם מערכים ועיון. במקרה שלנו, האפשרות הראשונה נדרשת, לכן בחלון בחירת הטופס הבא שנפתח, בחר אותו ולחץ על הלחצן "אישור".
- מדד הארגומנטים של המפעיל מתחיל לפעול. המשימה של הפונקציה שצוינה היא הפלט של הערך הממוקם בטווח הנבחר בקו המספר שצוין. אינדקס מפעיל פורמולה כללי כזה:
= אינדקס (מערך, number_name; [number_stolbits])
"המערך" הוא ויכוח המכיל את טווח הטווח שממנו נביא מידע במספר השורה שצוינה.
"מספר שורה" הוא ויכוח שהוא מספר קו זה. חשוב לדעת כי מספר הקו צריך להיות מוגדר לא יחסית למסמך כולו, אבל רק יחסית למערך שהוקצו.
"מספר העמודה" הוא ויכוח שהוא אופציונלי. כדי לפתור את המשימה שלנו, לא נשתמש בו, ולכן אין צורך לתאר את זה בנפרד.
שמנו את הסמן בתחום "מערך". לאחר מכן, עבור אל גיליון 2, והחזקת כפתור העכבר השמאלי, בחר את כל התוכן של "שיעור" עמודה.
- לאחר הקואורדינטות מוצגות בחלון המפעיל, שמנו את הסמן בשדה "מספר השורה". אנו נסוג את הטענה הזו באמצעות מפעיל החיפוש. לכן, לחץ על משולש הממוקם בצד שמאל של מחרוזת הפונקציה. רשימת מפעילים חדשים נפתחת. אם אתה מוצא את השם "חברת חיפוש" ביניהם, אתה יכול ללחוץ על זה. במקרה הנגדי, לחץ על הנקודה האחרונה של הרשימה - "פונקציות אחרות ...".
- חלון אשף סטנדרטי מתחיל. עבור אליו באותה קבוצה "קישורים ומערכים". הפעם ברשימה, בחר את הפריט "חברת חיפוש". בצע לחץ על הלחצן "אישור".
- ההפעלה של הטיעונים של טיעוני מפעיל החיפוש מבוצעת. הפונקציה שצוינה נועדה לפלט את מספר הערך במערך מסוים לפי שמה. זה בזכות תכונה זו אנו לחשב את מספר מחרוזת של ערך מסוים עבור פונקציית הפונקציה. תחביר של לוח החיפוש מוצג:
= לוח חיפוש (Search_name, Viewing__NASHIVE;
"הרצוי" הוא ויכוח המכיל את השם או כתובתו של התא של טווח צד שלישי שבו הוא ממוקם. זוהי המיקום של שם זה בטווח היעד צריך להיות מחושב. במקרה שלנו, תפקידו של הטיעון הראשון יופנה לתאים על גיליון 1, שבו נמצאים העובדים.
"מערך רשימה" הוא ויכוח, שהוא התייחסות למערך, המבצעת את החיפוש אחר הערך שצוין כדי לקבוע את עמדתו. יהיה לנו תפקיד זה כדי לבצע את הכתובת של "שם" עמודה על גיליון 2.
"סוג ההשוואה" - טענה שהיא אופציונלית, אך בניגוד למפעיל הקודם, יש צורך בטענה אופציונלית זו. הוא מציין כיצד להתאים למפעיל הוא הערך הרצוי עם מערך. טיעון זה יכול להיות אחד משלושה ערכים: -1; 0; 1. למערכים מסולפים, בחר באפשרות "0". אפשרות זו מתאימה למקרה שלנו.
לכן, המשך למלא את שדות החלון. שמנו את הסמן בשדה "ערך נירי", לחץ על העמודה "שם" הראשון "על גיליון 1.
- לאחר הצגת הקואורדינטות, הגדר את הסמן בשדה "רישום מסיבי" ועבור לתווית "גיליון 2", הממוקמת בחלק התחתון של חלון Excel שמעל שורת המצב. קלמנט על לחצן העכבר השמאלי והדגש את הסמן את כל התאים של העמודה "השם".
- לאחר הקואורדינטות שלהם מוצגים בשדה "רישום מסיבי", עבור אל שדה "מיפוי סוג" ולהגדיר את המספר "0" מהמקלדת. אחרי זה, אנחנו שוב חוזרים לשדה "מסתכל דרך המערך". העובדה היא שנבצע העתקת הנוסחה, כפי שעשינו בשיטה הקודמת. תהיה שינוי של כתובות, אבל כאן הקואורדינטות של המערך שנצפו שאנחנו צריכים להבטיח. הוא לא צריך להשתנות. אנו מדגישים את הקואורדינטות עם הסמן ולחץ על מקש הפונקציה F4. כפי שאתה יכול לראות, סימן הדולר הופיע לפני הקואורדינטות, כלומר התייחסות של קרוב משפחה הפך מוחלט. לאחר מכן לחץ על הלחצן "אישור".
- התוצאה מוצגת בתא הראשון של הטור "בית". אבל לפני העתקה, אנחנו צריכים לתקן אזור אחר, כלומר המדד הראשון של הטיעון פונקציה. לשם כך, בחר באלמנט העמודה, המכיל נוסחה ועובר מחרוזת הנוסחה. להקצות את הטענה הראשונה של אינדקס המפעיל (B2: B7) ולחץ על כפתור F4. כפי שניתן לראות, סימן הדולר הופיע ליד הקואורדינטות שנבחרו. לחץ על מקש Enter. באופן כללי, הנוסחה לקחה את הטופס הבא:
= מדד (Sheet2! $ B $ 2: $ B $ 7; לוח חיפוש (גיליון 1! A4, List2! $ $ 2: $ 7 $; 0))
- עכשיו אתה יכול להעתיק באמצעות סמן מילוי. אנחנו קוראים לזה באותו אופן שיש לנו דיברנו מוקדם יותר, ולמתוח עד סוף טווח הטבלאי.
- כפי שניתן לראות, למרות שסדר המיתרים בשני שולחנות קשורים אינו עולה בקנה אחד, בכל זאת, כל הערכים מתחקדים לפי שמות העובדים. זה הושג בזכות השימוש בשילוב של חיפוש האינדקס.
שיטה 4: הוספה מיוחדת
עניבה מערכי שולחן ב- Excel יכול גם להיות באמצעות הכנסה מיוחדת.
- בחר את הערכים שברצונך "להדק" לטבלה אחרת. במקרה שלנו, זהו טווח הטור "הימור" על גיליון 2. לחץ על קטע ייעודי עם לחצן העכבר הימני. ברשימה שנפתחת, בחר את הפריט "העתק". שילוב חלופי הוא שילוב Ktrl + C. לאחר מכן, אנחנו עוברים לסדין 1.
- מעבר לאזור הספר שאתה צריך, להקצות את התאים שבהם יש להדק את הערכים. במקרה שלנו, זהו הטור "הצעה". לחץ על קטע ייעודי עם לחצן העכבר הימני. בתפריט ההקשר בסרגל הכלים "הוספת פרמטרים", לחץ על "הוסף תקשורת" סמל.
יש גם חלופה. הוא, אגב, הוא היחיד לגירסאות ישנות יותר של Excel. בתפריט ההקשר, אנו מביאים את הסמן לפריט "הוספה מיוחדת". בתפריט הנוסף שנפתח, בחר את המיקום עם אותו שם.
- לאחר מכן, נפתח חלון Insert מיוחדים. לחץ על כפתור "הוסף תקשורת" בפינה השמאלית התחתונה של התא.
- כל אפשרות שתבחר, ערכים ממערך טבלה אחד יוכנסו למשנהו. בעת שינוי נתונים במקור, הם גם ישתנו באופן אוטומטי בטווח שהוכנס.
שיעור: הוספה מיוחדת ב- Excel
שיטה 5: תקשורת בין שולחנות במספר ספרים
בנוסף, תוכל לארגן קישור בין שטיחי השולחן בספרים שונים. פעולה זו משתמשת בכלי הוספה מיוחדת. פעולות יהיו דומות לחלוטין לאלה שחשבנו בשיטה הקודמת, אלא שהניווט במהלך הנוסחאות לא יהיה בין אזורים של ספר אחד, אך בין הקבצים. באופן טבעי, יש לפתוח את כל הספרים הקשורים.
- בחר את טווח הנתונים שיועברו לספר אחר. לחץ על לחצן העכבר הימני ובחר מיקום "העתק" בתפריט שנפתח.
- לאחר מכן אנו עוברים לספר שבו יש להכניס נתונים אלה. בחר את הטווח הרצוי. לחץ על לחצן העכבר הימני. בתפריט ההקשר בקבוצה "הוספת הגדרות", בחר את הפריט "הוסף תקשורת".
- לאחר מכן, הערכים יוכנסו. בעת שינוי נתונים בספר המקור, מערך טבלה מתוך ספר העבודה יידק אותם באופן אוטומטי. וזה בכלל לא נחוץ כדי להבטיח כי שני הספרים פתוחים. זה מספיק כדי לפתוח רק חוברת עבודה בלבד, וזה יהיה אוטומטית מנתח נתונים מסמך קשור סגור אם היו שינויים קודמים בו.
אבל יש לציין כי במקרה זה הופרס יופק בצורה של מערך ללא שינוי. כאשר מנסים לשנות כל תא עם הנתונים שהוכנסו, יאוכלו הודעה להודיע על חוסר היכולת לעשות זאת.
שינויים במערך כזה המשויך לספר אחר יכולים רק לשבור את החיבור.
הפסקות כותרת בין שולחנות
לפעמים זה נדרש לשבור את הקשר בין השולחנות. הסיבה לכך יכולה להיות הנ"ל המתוארת כאשר ברצונך לשנות מערך שהוכנס לספר אחר ופשוט את חוסר הרצון של המשתמש כך שהנתונים באותו טבלה מתעדכנים אוטומטית מהאחר.שיטה 1: מעברי תקשורת בין ספרים
כדי לשבור את הקשר בין ספרים בכל התאים, על ידי ביצוע פעולה אחת. במקרה זה, הנתונים בתאים יישארו, אבל הם כבר יהיו סטטיים לא מעודכנים ערכים שאינם תלויים במסמכים אחרים.
- בספר שבו מתוחכדים ערכים מקבצים אחרים, עבור לכרטיסייה נתונים. לחץ על "שנה קישורים", הנמצא על הקלטת בסרגל הכלים "חיבור". יצוין כי אם הספר הנוכחי אינו מכיל חיבורים עם קבצים אחרים, לחצן זה לא פעיל.
- חלון שינוי הקישור הושק. בחר מתוך רשימת ספרים קשורים (אם יש מספר מהם) את הקובץ שבו אנו רוצים לשבור את החיבור. לחץ על הלחצן "לשבור את החיבור".
- חלון מידע נפתח, המספק אזהרה על ההשלכות של פעולות נוספות. אם אתה בטוח שאתה הולך לעשות, לחץ על "הפסקה תקשורת" כפתור.
- לאחר מכן, כל ההתייחסויות לקובץ שצוין במסמך הנוכחי יוחלפו בערכים סטטיים.
שיטה 2: הוספת ערכים
אבל השיטה לעיל מתאימה רק אם אתה צריך לגמרי לשבור את כל הקישורים בין שני הספרים. מה אם אתה צריך לנתק את השולחנות הקשורים בתוך אותו קובץ? אתה יכול לעשות זאת על ידי העתקת הנתונים, ולאחר מכן הוספת אותו מקום כמו ערכים. אגב, שיטה זו ניתן לקרוע בין טווחי נתונים בודדים של ספרים שונים מבלי לשבור קשר משותף בין קבצים. בואו נראה איך שיטה זו פועלת בפועל.
- אנו מדגישים את הטווח שבו אנו רוצים למחוק תקשורת עם טבלה אחרת. לחץ על כפתור העכבר הימני. בתפריט פתוח, בחר את הפריט "העתק". במקום פעולות שצוינו, תוכל לחייג שילוב חלופי של מקשים חמים Ctrl + C.
- לאחר מכן, מבלי להסיר את הבחירה מאותה קטע, שוב לחץ על זה עם לחצן העכבר הימני. הפעם ברשימת הפעולה, לחץ על סמל "ערך", אשר פורסמה בקבוצת Insert Parameters.
- לאחר מכן, כל ההתייחסויות בטווח המסור יוחלפו בערכים סטטיים.
כפי שאתה יכול לראות, Excel יש דרכים וכלים לשייך כמה טבלאות בינם לבין עצמם. במקביל, נתונים טבלאי יכול להיות על גיליונות אחרים ואפילו בספרים שונים. במידת הצורך, חיבור זה יכול להיות שבור בקלות.