תוכן
בעת שימוש ב-Microsoft Excel, לעתים קרובות המשתמש אינו יודע מראש כמה מידע יהיה בטבלה כתוצאה מכך. לכן, אנחנו לא מבינים בכל המצבים איזה טווח צריך לכסות. אחרי הכל, קבוצה של תאים היא מושג משתנה. כדי להיפטר מבעיה זו, יש צורך להפוך את יצירת הטווח לאוטומטית כך שתתבסס אך ורק על כמות הנתונים שהזין המשתמש.
שינוי אוטומטי של טווחי תאים באקסל
היתרון של טווחים אוטומטיים באקסל הוא שהם מקלים הרבה על השימוש בנוסחאות. בנוסף, הם מאפשרים לפשט משמעותית את הניתוח של נתונים מורכבים המכילים מספר רב של נוסחאות, הכוללות פונקציות רבות. אתה יכול לתת לטווח הזה שם, ואז הוא יתעדכן אוטומטית בהתאם לנתונים שהוא מכיל.
כיצד לבצע שינוי טווח אוטומטי באקסל
נניח שאתה משקיע שצריך להשקיע בחפץ כלשהו. כתוצאה מכך, אנו רוצים לקבל מידע על כמה אתה יכול להרוויח בסך הכל במשך כל הזמן שהכסף יעבוד עבור הפרויקט הזה. עם זאת, על מנת לקבל מידע זה, אנו צריכים לעקוב באופן קבוע כמה רווח הכולל האובייקט הזה מביא לנו. בצע את אותו דוח כמו בצילום מסך זה.
במבט ראשון, הפתרון ברור: צריך רק לסכם את כל הטור. אם יופיעו בו ערכים, הסכום יעודכן באופן עצמאי. אבל לשיטה זו יש חסרונות רבים:
- אם הבעיה נפתרת בדרך זו, לא ניתן יהיה להשתמש בתאים הכלולים בעמודה B למטרות אחרות.
- טבלה כזו תצרוך הרבה זיכרון RAM, מה שלא יאפשר להשתמש במסמך במחשבים חלשים.
לפיכך, יש צורך לפתור בעיה זו באמצעות שמות דינמיים. כדי ליצור אותם, עליך לבצע את רצף הפעולות הבא:
- עבור ללשונית "נוסחאות", הממוקמת בתפריט הראשי. יהיה קטע "שמות מוגדרים", שבו יש כפתור "הקצה שם", עליו עלינו ללחוץ.
- לאחר מכן, תופיע תיבת דו-שיח בה עליך למלא את השדות כפי שמוצג בצילום המסך. חשוב לציין שעלינו ליישם את הפונקציה = תזוזה יחד עם הפונקציה לבדוקכדי ליצור טווח עדכון אוטומטי.
- לאחר מכן עלינו להשתמש בפונקציה SUM, שמשתמש בטווח הדינמי שלנו כטיעון.
לאחר השלמת השלבים הללו, נוכל לראות כיצד הכיסוי של התאים השייכים לטווח "הכנסה" מתעדכן כאשר אנו מוסיפים לשם אלמנטים חדשים.
פונקציית OFFSET באקסל
בואו נסתכל על הפונקציות שרשמנו בשדה "טווח" קודם לכן. שימוש בפונקציה השלכה אנו יכולים לקבוע את כמות הטווח בהתחשב בכמה תאים בעמודה B ממולאים. הארגומנטים של הפונקציה הם כדלקמן:
- התחל תא. באמצעות ארגומנט זה, המשתמש יכול לציין איזה תא בטווח ייחשב משמאל למעלה. זה ידווח למטה וימינה.
- טווח מוסט לפי שורות. באמצעות טווח זה, אנו מגדירים את מספר התאים שבאמצעותם היסט אמור להתרחש מהתא השמאלי העליון של הטווח. אתה יכול להשתמש לא רק בערכים חיוביים, אלא באפס ומינוס. במקרה זה, ייתכן שהעקירה לא תתרחש כלל, או שהיא תתבצע בכיוון ההפוך.
- טווח מוסט לפי עמודות. פרמטר זה דומה לקודמו, רק שהוא מאפשר לך להגדיר את מידת ההזזה האופקית של הטווח. כאן אתה יכול גם להשתמש בערכים אפס ושליליים.
- כמות הטווח בגובה. למעשה, הכותרת של הטיעון הזה מבהירה לנו מה פירושו. זהו מספר התאים שבהם הטווח אמור להגדיל.
- ערך הטווח ברוחב. הטיעון דומה לקודם, רק שהוא נוגע לעמודים.
אינך צריך לציין את שני הטיעונים האחרונים אם אין צורך בכך. במקרה זה, ערך הטווח יהיה רק תא אחד. לדוגמה, אם אתה מציין את הנוסחה =OFFSET(A1;0;0), נוסחה זו תתייחס לאותו תא כמו זה בארגומנט הראשון. אם ההיסט האנכי מוגדר ל-2 יחידות, אז במקרה זה התא יפנה לתא A3. כעת נתאר בפירוט מה משמעות הפונקציה לבדוק.
פונקציית COUNT באקסל
באמצעות הפונקציה לבדוק אנו קובעים כמה תאים בעמודה B מילאנו בסך הכל. כלומר, באמצעות שתי פונקציות, אנו קובעים כמה תאים בטווח ממולאים, ועל סמך המידע המתקבל, קובעים את גודל הטווח. לכן, הנוסחה הסופית תהיה הבאה: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
בואו נסתכל כיצד להבין נכון את העיקרון של נוסחה זו. הטיעון הראשון מצביע על המקום שבו מתחיל הטווח הדינמי שלנו. במקרה שלנו, זה תא B2. לפרמטרים נוספים יש אפס קואורדינטות. זה מצביע על כך שאיננו זקוקים לקיזוזים ביחס לתא השמאלי העליון. כל מה שאנחנו ממלאים הוא הגודל האנכי של הטווח, שבו השתמשנו בפונקציה לבדוק, הקובע את מספר התאים המכילים נתונים מסוימים. הפרמטר הרביעי שמילאנו הוא היחידה. לפיכך אנו מראים שהרוחב הכולל של הטווח צריך להיות עמודה אחת.
לפיכך, באמצעות הפונקציה לבדוק המשתמש יכול להשתמש בזיכרון בצורה יעילה ככל האפשר על ידי טעינת רק את התאים המכילים כמה ערכים. בהתאם לכך, לא יהיו שגיאות נוספות בעבודה הקשורות לביצועים לקויים של המחשב עליו יעבוד הגיליון האלקטרוני.
בהתאם לכך, כדי לקבוע את גודל הטווח בהתאם למספר העמודות, עליך לבצע רצף פעולות דומה, רק במקרה זה עליך לציין את היחידה בפרמטר השלישי, ואת הנוסחה ברביעי. לבדוק.
אנו רואים שבעזרת נוסחאות אקסל, לא רק ניתן לבצע אוטומציה של חישובים מתמטיים. זו רק טיפה בים, אבל למעשה הם מאפשרים לך לבצע אוטומציה של כמעט כל פעולה שעולה על דעתך.
תרשימים דינמיים באקסל
אז, בשלב האחרון, הצלחנו ליצור טווח דינמי, שגודלו תלוי לחלוטין בכמות התאים המלאים שהוא מכיל. כעת, בהתבסס על הנתונים הללו, תוכל ליצור תרשימים דינמיים שישתנו אוטומטית ברגע שהמשתמש יבצע שינויים כלשהם או יוסיף עמודה או שורה נוספת. רצף הפעולות במקרה זה הוא כדלקמן:
- אנו בוחרים את הטווח שלנו, ולאחר מכן אנו מכניסים תרשים מסוג "היסטוגרמה עם קיבוץ". אתה יכול למצוא פריט זה בקטע "הוספה" בקטע "תרשימים - היסטוגרמה".
- אנו מבצעים לחיצת עכבר שמאלית על עמודה אקראית של ההיסטוגרמה, ולאחר מכן תוצג הפונקציה =SERIES() בשורת הפונקציה. בצילום המסך ניתן לראות את הנוסחה המפורטת.
- לאחר מכן, יש לבצע כמה שינויים בנוסחה. אתה צריך להחליף את הטווח אחרי "Sheet1!" לשם הטווח. זה יביא לפונקציה הבאה: =ROW(Sheet1!$B$1;;Sheet1!income;1)
- כעת נותר להוסיף רשומה חדשה לדוח כדי לבדוק אם התרשים מתעדכן אוטומטית או לא.
בואו נסתכל על הדיאגרמה שלנו.
בואו נסכם איך עשינו את זה. בשלב הקודם יצרנו טווח דינמי שגודלו תלוי בכמה אלמנטים הוא מכיל. לשם כך השתמשנו בשילוב של פונקציות לבדוק и השלכה. הכרנו את הטווח הזה בשם, ואז השתמשנו בהתייחסות לשם הזה בתור הטווח של ההיסטוגרמה שלנו. לא כל כך חשוב באיזה טווח ספציפי לבחור כמקור נתונים בשלב הראשון. העיקר להחליף אותו בשם הטווח מאוחר יותר. בדרך זו אתה יכול לחסוך הרבה זיכרון RAM.
טווחים בעלי שם ושימושיהם
כעת נדבר בפירוט רב יותר כיצד ליצור נכון טווחים בעלי שם ולהשתמש בהם לביצוע המשימות המוגדרות עבור משתמש Excel.
כברירת מחדל, אנו משתמשים בכתובות סלולריות רגילות כדי לחסוך זמן. זה שימושי כאשר אתה צריך לכתוב טווח פעם אחת או יותר. אם צריך להשתמש בו כל הזמן או שהוא צריך להיות אדפטיבי, אז יש להשתמש בטווחים בעלי שם. הם הופכים יצירת נוסחאות להרבה יותר קלות, ולא יהיה למשתמש כל כך קשה לנתח נוסחאות מורכבות הכוללות מספר רב של פונקציות. נתאר כמה מהשלבים הכרוכים ביצירת טווחים דינמיים.
הכל מתחיל במתן שם לתא. כדי לעשות זאת, פשוט בחר אותו, ולאחר מכן כתוב את השם שאנו צריכים בשדה של השם שלו. חשוב שיהיה קל לזכור. יש כמה הגבלות שיש לקחת בחשבון בעת מתן שמות:
- האורך המרבי הוא 255 תווים. זה מספיק כדי להקצות שם שהלב שלך חפץ בו.
- השם לא יכול להכיל רווחים. לכן, אם הוא מכיל מספר מילים, אז אפשר להפריד ביניהן באמצעות תו הקו התחתון.
אם בהמשך גיליונות אחרים של קובץ זה עלינו להציג ערך זה או ליישם אותו כדי לבצע חישובים נוספים, אין צורך לעבור לגיליון הראשון. אתה יכול פשוט לרשום את השם של תא הטווח הזה.
השלב הבא הוא יצירת טווח בעל שם. ההליך הוא בעצם אותו דבר. ראשית עליך לבחור טווח, ולאחר מכן לציין את שמו. לאחר מכן, ניתן להשתמש בשם זה בכל פעולות הנתונים האחרות ב- Excel. לדוגמה, טווחים בעלי שם משמשים לעתים קרובות להגדרת סכום הערכים.
בנוסף, ניתן ליצור טווח בעל שם באמצעות לשונית נוסחאות באמצעות הכלי Set Name. לאחר שנבחר בו, יופיע חלון בו עלינו לבחור שם לטווח שלנו, וכן לציין את האזור אליו הוא ימשך באופן ידני. ניתן גם לציין היכן טווח זה יפעל: בתוך גיליון בודד או לאורך הספר.
אם כבר נוצר טווח שמות, אז כדי להשתמש בו, יש שירות מיוחד שנקרא מנהל שמות. זה מאפשר לא רק לערוך או להוסיף שמות חדשים, אלא גם למחוק אותם אם אין בהם עוד צורך.
יש לזכור שכאשר משתמשים בטווחים בעלי שם בנוסחאות, אז לאחר מחיקתם, הנוסחאות לא יוחלפו אוטומטית בערכים הנכונים. לכן, עלולות להתרחש שגיאות. לכן, לפני מחיקת טווח בעל שם, עליך לוודא שהוא אינו בשימוש באף אחת מהנוסחאות.
דרך נוספת ליצור טווח בעל שם היא לקבל אותו מטבלה. לשם כך, יש כלי מיוחד שנקרא "צור מתוך בחירה". כפי שאנו מבינים, כדי להשתמש בו, תחילה עליך לבחור את הטווח שאנו נערוך, ולאחר מכן להגדיר את המקום בו יש לנו את הכותרות. כתוצאה מכך, בהתבסס על נתונים אלה, Excel יעבד אוטומטית את כל הנתונים, וכותרות יוקצו אוטומטית.
אם הכותרת מכילה מספר מילים, Excel יפריד ביניהן אוטומטית עם קו תחתון.
לפיכך, הבנו כיצד ליצור טווחים בעלי שם דינמי וכיצד הם מאפשרים לך לבצע אוטומציה של עבודה עם כמויות גדולות של נתונים. כפי שאתה יכול לראות, מספיק להשתמש במספר פונקציות ובכלי התוכנית המובנים בפונקציונליות. אין שום דבר מסובך בכלל, למרות שזה עשוי להיראות כך למתחילים במבט ראשון.