תוכן
לעתים קרובות, משתמשים מתמודדים עם הצורך להצמיד תא בנוסחה. לדוגמה, זה מתרחש במצבים שבהם רוצים להעתיק נוסחה, אבל כדי שהקישור לא יזוז למעלה ולמטה באותו מספר תאים כפי שהוא הועתק ממיקומו המקורי.
במקרה זה, אתה יכול לתקן את ההפניה לתא ב- Excel. וזה יכול להיעשות בכמה דרכים בו זמנית. בואו נסתכל מקרוב על איך להשיג מטרה זו.
מהו קישור לאקסל
הסדין מורכב מתאים. כל אחד מהם מכיל מידע ספציפי. תאים אחרים יכולים להשתמש בו בחישובים. אבל איך הם מבינים מאיפה להשיג את הנתונים? זה עוזר להם ליצור קישורים.
כל קישור מציין תא עם אות אחת ומספר אחד. אות מייצגת עמודה ומספר מייצג שורה.
ישנם שלושה סוגי קישורים: מוחלט, יחסי ומעורב. השני מוגדר כברירת מחדל. הפניה מוחלטת היא כזו שיש לה כתובת קבועה של עמודה ועמודה. בהתאם, מעורב הוא זה שבו עמודה נפרדת או שורה קבועה.
השיטה 1
על מנת לשמור את כתובות העמודות והשורות, בצע את השלבים הבאים:
- לחץ על התא המכיל את הנוסחה.
- לחץ על שורת הנוסחאות עבור התא שאנו צריכים.
- לחץ על F4.
כתוצאה מכך, הפניה לתא תשתנה למוחלטת. ניתן לזהות אותו לפי סימן הדולר האופייני. לדוגמה, אם תלחץ על תא B2 ואז תלחץ על F4, הקישור ייראה כך: $B$2.
מה המשמעות של סימן הדולר לפני החלק של הכתובת לתא?
- אם הוא ממוקם לפני אות, זה מציין שהפניית העמודה נשארת זהה, לא משנה לאן הנוסחה הועברה.
- אם סימן הדולר נמצא לפני המספר, זה מציין שהמחרוזת מוצמדת.
השיטה 2
שיטה זו כמעט זהה לקודמתה, רק שאתה צריך ללחוץ על F4 פעמיים. לדוגמה, אם היה לנו תא B2, אז לאחר מכן הוא יהפוך ל-B$2. במילים פשוטות, כך הצלחנו לתקן את הקו. במקרה זה, האות של העמודה תשתנה.
זה מאוד נוח, למשל, בטבלאות שבהן צריך להציג את התוכן של התא השני מלמעלה בתא התחתון. במקום לעשות נוסחה כזו הרבה פעמים, מספיק לתקן את השורה ולתת לעמודה להשתנות.
השיטה 3
זה זהה לחלוטין לשיטה הקודמת, רק שאתה צריך ללחוץ על מקש F4 שלוש פעמים. אז רק ההתייחסות לעמוד תהיה מוחלטת, והשורה תישאר קבועה.
השיטה 4
נניח שיש לנו התייחסות מוחלטת לתא, אבל כאן היה צורך להפוך אותו ליחסי. לשם כך, הקש על מקש F4 כל כך הרבה פעמים עד שאין סימני $ בקישור. אז הוא יהפוך ליחסי, וכאשר תעביר או תעתיק את הנוסחה, גם כתובת העמודה וגם כתובת השורה ישתנו.
הצמדת תאים לטווח גדול
אנו רואים שהשיטות הנ"ל אינן מהוות קושי כלל לביצוע. אבל המשימות הן ספציפיות. ולדוגמה, מה לעשות אם יש לנו כמה עשרות נוסחאות בבת אחת, שהקישורים שבהם צריכים להפוך לאלו מוחלטים.
למרבה הצער, שיטות אקסל סטנדרטיות לא ישיגו מטרה זו. לשם כך, עליך להשתמש בתוסף מיוחד בשם VBA-Excel. הוא מכיל תכונות נוספות רבות המאפשרות לך לבצע משימות נפוצות עם Excel הרבה יותר מהר.
הוא כולל יותר ממאה פונקציות המוגדרות על ידי משתמש ו-25 פקודות מאקרו שונות, והוא גם מתעדכן באופן קבוע. זה מאפשר לך לשפר את העבודה עם כמעט כל היבט:
- תאים.
- מאקרו
- פונקציות מסוגים שונים.
- קישורים ומערכים.
במיוחד, תוספת זו מאפשרת לך לתקן קישורים במספר רב של נוסחאות בבת אחת. לשם כך, עליך לבצע את השלבים הבאים:
- בחר טווח.
- פתח את לשונית VBA-Excel שתופיע לאחר ההתקנה.
- פתח את תפריט "פונקציות", שבו ממוקמת אפשרות "נוסחאות נעילה".
6 - לאחר מכן, תופיע תיבת דו-שיח שבה עליך לציין את הפרמטר הנדרש. תוסף זה מאפשר לך להצמיד עמודה ועמודה בנפרד, ביחד, וגם להסיר הצמדה שכבר קיימת עם חבילה. לאחר בחירת הפרמטר הדרוש באמצעות לחצן הבחירה המתאים, עליך לאשר את הפעולות שלך על ידי לחיצה על "אישור".
דוגמה
בואו ניקח דוגמה כדי להבהיר אותה יותר. נניח שיש לנו מידע שמתאר את עלות הסחורה, הכמות הכוללת שלה והכנסות המכירות. ולפנינו המשימה לגרום לטבלה, על סמך הכמות והעלות, לקבוע אוטומטית כמה כסף הצלחנו להרוויח מבלי לנכות הפסדים.
בדוגמה שלנו, בשביל זה אתה צריך להזין את הנוסחה =B2*C2. זה די פשוט, כפי שאתה יכול לראות. קל מאוד להשתמש בדוגמה שלה כדי לתאר כיצד ניתן לתקן את הכתובת של תא או העמודה או השורה הבודדים שלו.
כמובן, בדוגמה זו, אתה יכול לנסות לגרור את הנוסחה למטה באמצעות סמן המילוי האוטומטי, אבל במקרה זה, התאים ישתנו אוטומטית. אז, בתא D3 תהיה נוסחה נוספת, שבה המספרים יוחלפו, בהתאמה, ב-3. יתרה מכך, על פי הסכימה – D4 – הנוסחה תקבל את הצורה = B4 * C4, D5 – באופן דומה, אבל עם מספר 5 וכן הלאה.
אם יש צורך (ברוב המקרים מסתבר), אז אין בעיות. אבל אם אתה צריך לתקן את הנוסחה בתא אחד כך שהיא לא תשתנה בעת גרירה, זה יהיה קצת יותר קשה.
נניח שאנחנו צריכים לקבוע את ההכנסות בדולר. בואו נשים את זה בתא B7. בואו נהיה קצת נוסטלגיים ונציין את העלות של 35 רובל לדולר. בהתאם לכך, על מנת לקבוע את ההכנסות בדולרים, יש צורך לחלק את הסכום ברובל בשער הדולר.
כך זה נראה בדוגמה שלנו.
אם ננסה, בדומה לגרסה הקודמת, לרשום נוסחה, אז ניכשל. באופן דומה, הנוסחה תשתנה לנוסחה המתאימה. בדוגמה שלנו, זה יהיה כך: =E3*B8. מכאן נוכל לראות. שהחלק הראשון של הנוסחה הפך ל-E3, והצבנו לעצמנו את המשימה הזו, אבל אנחנו לא צריכים לשנות את החלק השני של הנוסחה ל-B8. לכן, עלינו להפוך את ההתייחסות להתייחסות מוחלטת. אתה יכול לעשות זאת מבלי ללחוץ על מקש F4, רק על ידי הוספת סימן דולר.
לאחר שהפכנו את ההתייחסות לתא השני לתא מוחלט, הוא הפך להיות מוגן מפני שינויים. עכשיו אתה יכול לגרור אותו בבטחה באמצעות ידית המילוי האוטומטי. כל הנתונים הקבועים יישארו זהים, ללא קשר למיקום הנוסחה, ונתונים לא מחויבים ישתנו בצורה גמישה. בכל התאים, ההכנסה ברובל המתוארת בשורה זו תחולק באותו שער דולר.
הנוסחה עצמה תיראה כך:
=D2/$B$7
שימו לב! ציינו שני סימני דולר. בדרך זו, אנו מראים לתוכנית שצריך לתקן גם את העמודה וגם את השורה.
הפניות לתאים בפקודות מאקרו
מאקרו הוא תת-שגרה המאפשרת להפוך פעולות לאוטומטיות. בניגוד לפונקציונליות הסטנדרטית של Excel, מאקרו מאפשר לך להגדיר מיד תא ספציפי ולבצע פעולות מסוימות בכמה שורות קוד בלבד. שימושי לעיבוד אצווה של מידע, למשל, אם אין דרך להתקין תוספות (לדוגמה, נעשה שימוש במחשב של חברה, לא במחשב אישי).
ראשית עליך להבין שמושג המפתח של מאקרו הוא אובייקטים שיכולים להכיל אובייקטים אחרים. אובייקט חוברות העבודה אחראי על הספר האלקטרוני (כלומר, המסמך). הוא כולל את האובייקט Sheets, שהוא אוסף של כל הגיליונות של מסמך פתוח.
בהתאם לכך, תאים הם אובייקט Cells. הוא מכיל את כל התאים של גיליון מסוים.
כל אובייקט מוסמך עם ארגומנטים בסוגריים. במקרה של תאים, הם מוזכרים בסדר זה. מספר השורה מופיע ראשון, ואחריו מספר העמודה או האות (שני הפורמטים קבילים).
לדוגמה, שורת קוד המכילה הפניה לתא C5 תיראה כך:
חוברות עבודה("Book2.xlsm").Sheets("List2").Cells(5, 3)
חוברות עבודה("Book2.xlsm").Sheets("List2").Cells(5, "C")
אתה יכול גם לגשת לתא באמצעות אובייקט מסודר. באופן כללי, הכוונה היא לתת הפניה לטווח (שהאלמנטים שלו, אגב, יכולים להיות גם אבסולוטיים או יחסיים), אבל אפשר פשוט לתת שם תא, באותו פורמט כמו במסמך אקסל.
במקרה זה, הקו ייראה כך.
חוברות עבודה("Book2.xlsm").Sheets("List2"). Range("C5")
אולי נראה שהאופציה הזו נוחה יותר, אבל היתרון בשתי האפשרויות הראשונות הוא שאפשר להשתמש במשתנים בסוגריים ולתת קישור שהוא כבר לא מוחלט, אלא משהו כמו יחסי, שיהיה תלוי בתוצאות של את החישובים.
לפיכך, ניתן להשתמש בפקודות מאקרו ביעילות בתוכניות. למעשה, כל ההפניות לתאים או לטווחים כאן יהיו מוחלטות, ולכן גם ניתן לתקן איתם. נכון, זה לא כל כך נוח. השימוש בפקודות מאקרו יכול להיות שימושי בעת כתיבת תוכניות מורכבות עם מספר רב של שלבים באלגוריתם. באופן כללי, הדרך הסטנדרטית להשתמש בהפניות מוחלטות או יחסיות היא הרבה יותר נוחה.
מסקנות
הבנו מהי הפניה לתא, איך היא פועלת, בשביל מה היא מיועדת. הבנו את ההבדל בין הפניות מוחלטות ליחסיות והבנו מה צריך לעשות כדי להפוך סוג אחד לאחר (במילים פשוטות, תקן את הכתובת או בטל את ההצמדה). הבנו איך אתה יכול לעשות את זה מיד עם מספר רב של ערכים. כעת יש לך את הגמישות להשתמש בתכונה זו במצבים הנכונים.