התייחסות ייחוס מוחלטת תא, עיצוב

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

בית הספר ניווט

1. למה אתה צריך נוסחאות ותפקידים?
2. הגדרת ויצירת נוסחה
3. התייחסות ייחוס מוחלטת תא, עיצוב
4. פונקציות שימושיות אתה צריך להכיר
5. חיפושים, תרשימים, סטטיסטיקה, לוחות פיבוט

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

מהו Cell Reference?

"הפניה לתא" פירושה התא שאליו מתייחס תא אחר. לדוגמה, אם בתא A1 יש לך = A2. אז A1 מתייחס A2.

נסקור את מה שאמרנו בשיעור 2 על שורות ועמודות, כדי שנוכל לחקור את הפניות לתא.

תאים בגיליון האלקטרוני מכונים לפי שורות ועמודות. עמודות אנכיות ומסומנות באותיות. שורות הן אופקי ו מסומן עם מספרים.

התא הראשון בגיליון הוא A1, כלומר טור A, שורה 1, B3 מתייחס לתא הממוקם בעמודה השנייה, שורה שלישית, וכן הלאה.

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

סוגי הפניות תא

ישנם שלושה סוגים של הפניות תאים.

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

יחסי - התייחסות יחסית פירושה שכתובת התא משתנה בעת העתקה או העברה; כלומר, הפניה לתא היא יחסית למיקומה.

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

התייחסות יחסית

הבה נתייחס לדוגמה הקודמת - נניח שבתא A1 יש לנו נוסחה שפשוט אומרת = A2. זה אומר פלט Excel בתא A1 מה הוא קלט לתוך התא A2. בתא A2 יש לנו הקלדת "A2" כך Excel מציג את הערך "A2" בתא A1.

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

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

ממשיכים עם הדוגמה שלנו, ומסתכלת על הגרפיקה למטה, אם להעתיק את התוכן של תא A1 שני מימין וארבעה למטה יש לך להעביר אותו לתא C5.

העתקנו את התא שני טורים ימינה וארבעה למטה. כלומר, שינינו את התא הוא מתייחס שני על פני ארבעה למטה. A1 = A2 עכשיו הוא C5 = C6. במקום להתייחס A2, עכשיו תא C5 מתייחס לתא C6.

הערך המוצג הוא 0 מכיוון שהתא C6 ריק. בתא C6 אנחנו סוג "אני C6" ועכשיו C5 מציג "אני C6."

דוגמה: נוסחת טקסט

ננסה דוגמה נוספת. זוכרים מהשיעור 2 שבו היינו צריכים לפצל שם מלא לשם פרטי ושם משפחה? מה קורה כאשר אנו מעתיקים נוסחה זו?

כתוב את הנוסחה = RIGHT (A3, LEN (A3) - מצא (",", A3) - 1) או להעתיק את הטקסט לתא C3. אין להעתיק את התא בפועל, רק את הטקסט, להעתיק את הטקסט, אחרת זה יהיה לעדכן את ההפניה.

ניתן לערוך את התוכן של תא בחלק העליון של גיליון אלקטרוני בתיבה שליד המקום שבו כתוב "FX". התיבה ארוכה יותר מתא רחב, ולכן קל יותר לערוך.

עכשיו יש לנו:

שום דבר מסובך, יש לנו רק כתוב נוסחה חדשה לתוך תא C3. עכשיו להעתיק C3 לתאים C2 ו C4. צפה בתוצאות הבאות:

עכשיו יש לנו שמות של אלכסנדר המילטון ותומס ג'פרסון.

השתמש בסמן כדי לסמן תאים C2, C3 ו- C4. הצבע את הסמן לתא B2 והדבק את התוכן. תראו מה קרה - אנחנו מקבלים שגיאה: "#REF." למה זה?

כאשר העתקנו את התאים מהעמודה C לעמודה B הוא עדכן את הפניה עמודה אחת שמאלה = ימין (A2, LEN (A2) - חפש (",", A2) - 1).

זה שינה את כל התייחסות A2 אל הטור משמאל, אבל אין טור בצד שמאל של טור א 'אז המחשב לא יודע למה אתה מתכוון.

הנוסחה החדשה ב- B2 לדוגמה, היא = RIGHT (#REF!, LEN (#REF!) - מצא (",", # REF!) - 1) והתוצאה היא #REF:

העתקת נוסחה למגוון תאים

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

לפי "טווח" אנו מתכוונים יותר מתא אחד. לדוגמה, (C1: C10) פירושו כל התאים מתא C1 לתא C10. אז זה טור של תאים. דוגמה נוספת (A1: AZ1) היא השורה העליונה מעמוד A עד טור AZ.

אם טווח חוצה חמש עמודות ועשר שורות, אתה מציין את הטווח על ידי כתיבת התווית השמאלית העליונה והימנית התחתונה, למשל, A1: E10. זהו אזור מרובע שחוצה שורות ועמודות ולא רק חלק מעמודה או חלק משורה.

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

עכשיו להעתיק את הנוסחה בתא C3 (= B3 + C2) לשאר העמוד כדי לתת איזון פועל עבור התקציב שלנו. Excel מעדכן את הפניה לתא בעת העתקתו. התוצאה מוצגת להלן:

כפי שניתן לראות, כל עדכונים חדשים התא קרוב משפחה אל המיקום החדש, כך תא C4 עדכונים הנוסחה שלה ל = B4 + C3:

עדכונים C5 C5 ל + B5 + C4, וכן הלאה:

הפניות מוחלטות

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

לדוגמה, הזן את הנוסחה = $ A $ 1 בכל תא. ה- $ מול העמודה A לא משנים את העמודה, ה- $ לפני השורה 1 פירושו לא משנה את העמודה בעת העתקה או העברה של התא לתא אחר.

כפי שניתן לראות בדוגמה שלהלן, בתא B1 יש לנו התייחסות יחסית = A1. כאשר אנו מעתיקים את B1 לארבעת התאים שמתחתיו, הפניה היחסית = A1 משתנה לתא שמאלה, ולכן B2 הופכים ל- A2, B3 להיות A3, וכו 'תאים אלה כמובן אין ערך inputed, ולכן הפלט הוא אפס.

עם זאת, אם נשתמש ב- $ 1 $ A1 $, כגון ב- C1 ואנו מעתיקים אותו לארבעת התאים שמתחתיו, ההתייחסות היא מוחלטת, ולכן היא לעולם לא תשתנה והפלט יהיה תמיד שווה לערך בתא A1.

נניח שאתה עוקב אחר העניין שלך, כגון בדוגמה הבאה. הנוסחה ב- C4 = B4 * B1 היא ריבית "ריבית" * "יתרה" = "ריבית לשנה".

עכשיו, שינית את התקציב שלך ושמרת עוד 2,000 $ כדי לקנות קרן נאמנות. נניח שהיא קרן בריבית קבועה והיא משלמת את אותה ריבית. הזן את החשבון החדש ואת היתרה לתוך הגיליון האלקטרוני ולאחר מכן להעתיק את הנוסחה = B4 * B1 מתא C4 לתא C5.

התקציב החדש נראה כך:

קרן הנאמנות החדשה מרוויחה 0 דולר בריבית בשנה, מה שלא יכול להיות נכון, כי הריבית היא בבירור 5 אחוזים.

Excel מדגיש את התאים שאליהם מתייחס הנוסחה. ניתן לראות לעיל כי התייחסות הריבית (B1) מועבר B2 תא ריק. היינו צריכים לעשות את ההתייחסות B1 מוחלטת על ידי כתיבת $ B $ 1 באמצעות סימן דולר כדי לעגן את השורה ואת הפניה העמודה.

לשכתב את החישוב הראשון ב C4 לקרוא = B4 * $ B $ 1 כפי שמוצג להלן:

לאחר מכן להעתיק את הנוסחה מ C4 ל C5. הגיליון האלקטרוני נראה כך:

מאחר שהעתקנו את הנוסחה בתא אחד למטה, כלומר הגדילו את השורה אחת, הנוסחה החדשה היא = B5 * $ B $ 1. הריבית על קרנות הנאמנות מחושבת נכון כעת, משום שהריבית מעוגנת בתא B1.

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

שמות יכולים להתייחס לתא אחד או טווח, וניתן להשתמש בשם בנוסחה, לדוגמה = interest_rate * 8 זה אותו דבר כמו כתיבה = $ B $ 1 * 8.

הפניות מעורבות

הפניות מעורבות הן כאשר או השורה או עמודה מעוגנת.

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

הזן את העלות לכל דונם כמו = $ B2 * C2 בתא D2. אתה אומר שאתה רוצה לעגן את המחיר לכל עמודה פאונד. לאחר מכן העתק את הנוסחה לשורות האחרות באותה עמודה:

עכשיו אתה רוצה לדעת את הערך של המלאי שלך של זרעים. אתה צריך את המחיר לכל לירה ומספר קילוגרמים במלאי לדעת את הערך של המלאי.

אנו מוסיפים שתי עמודות: "פאונד של זרעים במלאי" ולאחר מכן "ערך המלאי". עכשיו, להעתיק את התא D2 ל F4 וציין כי הפניה שורה בחלק הראשון של הנוסחה המקורית ($ B2) מתעדכן בשורה 4 אבל הטור נשאר קבוע כי $ עוגנים אותו "ב"

זוהי הפניה מעורבת כי העמודה מוחלטת והשורה יחסית.

הפניות מעגליות

התייחסות מעגלית היא כאשר הנוסחה מתייחסת לעצמה.

לדוגמה, אתה לא יכול לכתוב C3 = C3 + 1. סוג זה של חישוב נקרא "איטרציה" כלומר זה חוזר על עצמו. Excel אינו תומך ב- iteration מכיוון שהוא מחשב הכל רק פעם אחת.

אם תנסה לעשות זאת על ידי הקלדת SUM (B1: B5) בתא B5:

מסך אזהרה מופיע:

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

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

הפניות לגליונות עבודה אחרים

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

גליונות עבודה כברירת מחדל נקראים Sheet1, Sheet2 וכן הלאה. אתה יוצר אחד חדש על ידי לחיצה על "+" בחלק התחתון של המסך Excel.

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

או שאתה פשוט יכול ללחוץ פעמיים על הכרטיסייה ולשנות את שמו.

התחביר עבור הפניה גליון עבודה הוא = גליון עבודה! תא. ניתן להשתמש בסוג זה של הפניה כאשר אותו ערך משמש בשתי גליונות עבודה, דוגמאות של:

• התאריך של היום
• שער המרת מטבע מדולר לאירו
• כל מה שרלוונטי לכל גליונות העבודה בחוברת העבודה

להלן דוגמה של גליון עבודה "עניין" התייחסות לגליון "הלוואה", תא B1.

אם נתבונן בגליון העבודה "הלוואה", נוכל לראות את ההתייחסות לסכום ההלוואה:

מגיע הבא ...

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

זהו השיעור של היום, בשיעור 4, נדון בכמה פונקציות שימושיות שייתכן ותרצו לדעת לשימוש יומיומי ב- Excel.