טיפ ליצירת רשימות תלויות באקסל

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

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

תחום לימוד וקורסים של תחום רלוונטי

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

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

שלב ראשון: הכנת הרשימות בעמודות

כדאי שתהיה כותרת לכל עמודה.

clip_image002

שלב שני: מתן שם לכל טווח

לתאים A2:A4 השם "עיר"

לתאים B2:B5 השם "ירושלים"

לתאים C2:C7 השם "תל_אביב" (שימו לב! שם חייב להיות מילה אחת לכן יש קו תחתי בין 2 המילים , וגם בטווח עיר יש קו תחתי)

לתאים D2:D4 השם רמת_גן.

כיצד לתת שם ראינו בטיפ שמות באקסל? בשביל מה ואיך

שלב שלישי : יצירת תאים בהם ניתן לבחור עיר בעמודה F

לפי הטיפ איך יוצרים באקסל תאים בהם אפשר רק לבחור נתון מרשימה

שלב רביעי : בעמודה G יצירת תאים בהם ניתן לבחור רחוב מתוך רחובות העיר שנבחרה בעמודה F

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

clip_image004

כל פעם שנבחר עיר בעמודה F בעמודה G נוכל לבחור מתוך הרחובות של אותה העיר.

clip_image006

clip_image008

clip_image010

ב ה צ ל ח ה !


תגובות

טיפ ליצירת רשימות תלויות באקסל — 54 תגובות

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

  2. היי מיכל
    אני שמח לקבל ממך את הטיפים ולומד מהם כול הזמן דברים חדשים.
    אבל הטיפ האחרון ליצירת רשימות תלויות באקסל לא מצליח לי.
    עד השלב השלישי (יצירת תאים בהם ניתן לבחור עיר בעמודה F) הצליח לי הכול
    אבל השלב הרביעי (בעמודה G יצירת תאים בהם ניתן לבחור רחוב מתוך רחובות העיר שנבחרה) לא מצליח ומסתדר לי עם פונקציית INDIRECT
    הודה לך מאוד עם תסבירי לי יותר בפירוט איזה נתונים צריך להכניס לפונקצייה זו.

    בתודה מראש…..עומר נאור

  3. מה ההבדל בין הפונקציה indirect לבין סימן = שבתוך תא שמפנה לתא אחר?

  4. מדהים, ואני חשבתי שאני מכיר אקסל, בעזרת ההסבר והדוגמא הצלחתי בקלות לבצע.
    תודה רבה !!

    • היי גל,
      אפשר להעתיק .
      תבדוק ב INDIRECT שכתובת התא ללא קיבוע שורה.
      עדכן בבקשה שהצלחת

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

  6. קודם כל תודה 🙂
    דבר שני
    אשמח אם תעזרו לי.
    עשיתי רשימות רבות ב 24 שעות האחרונות ועכשיו ברשימה פשוטה
    משום מה אני מקבל #ref וא #value! תלוי בערך שנבחר ברשימת מקור
    ברשימת המקור יש בחירה בין רכיבים לעבודה.
    כאשר אני מבצע פונקציית Indirect פשוטה אני מקבל את התוצאה הנ"ל
    לצורך העניין הפונקציה היא =INDIRECT(A16)
    הפונקציה האמיתית היא אימות רשימה
    =if(Indirect(A16)="עבודה",Indirect(A16),INDIRECT(A16&"_"&$B$5&"_"&B6))
    אבל לאחר פירוק כל גורמי המשוואה דווקא חלק זה יוצר בעיה.

  7. היי מיכל.
    בבקשה עזרתך:

    הכנתי 2 רשימות, רשימה אחת עם כל המספרים השלמים בין 0 ל 100, ורשימה שניה גם היא עם כל המספרים השלמים בין 0 ל 100 (המספרים רשומים בעמודה אחת, כל מספר במשבצת עוקבת בסדר עולה)
    אני רוצה לעשות התניה כזו:
    אני רוצה לבחור מספר מבין המספרים ברשימה הראשונה, ואז אני רוצה שהאפשרויות ברשימה השנייה יופיעו כאפשרות לבחירה רק המספרים הגדולים מהמספר שבחרתי ברשימה הראשונה

    למשל, אם בחרתי ברשימה הראשונה את הספרה "1", אז ברשימה השניה יופיעו כאפשרות לבחירה כל המספרים בין 2 ל 100. אם בחרתי ברשימה הראשונה את הספרה "56", אז ברשימה השנייה יופיעו כאפשרות לבחירה כל המספרים בין 57 ל 100.

    הלוואי שזה אפשרי…

    תודה!!

    • היי תומר,
      במקום הרשימה השניה (שלא ניתן לעשות באקסל)
      ניתן להגדיר תנאי במותאם אישית
      לדוגמא:
      =AND(G13>D13,G13<=100) יצטרכו להקליד מספר אבל יוכלו רק מספר גדול מהתא באותה שורה של רשימה 1 וקטן מ 100 בהצלחה!

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

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

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

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

  11. היי מיכל,
    אני מנסה ליצור רשימת בחירה כמו שהסברת. הרשימה הראשונה יוצאת סבבה:)
    ברשימה התלויה, ברגע שאני משתמשת בפונקציה =indirect והתא הרלוונטי, המחשב מקפיץ לי הודעה "ערכו הנוכחי של המקור הוא שגיאה"
    מה ניתן לעשות?

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

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

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

  13. היי
    רציתי לשאול
    הבנתי שיש אפשרות באקסל להוציא ראשי תיבות ממשפט
    עד כה מה שהצלחתי זה לחלץ ראשי תיבות ממשפט בן 3 מילים ולא יותר ע"י הפונקציה הבאה:
    =LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")

    אשמח אם יעזרו לי לחלץ ראשי תיבות ממשפטים ארוכים
    תודה יהודית

    • יהודית שלום ,
      הנוסחה שצרפת מתאימה רק עד ראשי תיבות מ 3 מילים,
      ברגע שאת רוצה משפט ארוך ואת לא יודעת כמה מקסימום מילים יהיה לך,
      ניתן לעשות באמצעות מאקרו אקסל VBA
      אם את רוצה , שלחי לי אימייל ואשלח לך תוסף לפונקציה מתאימה.
      [email protected]

  14. היי מיכל, אהבתי מאד.
    אם אני רוצה לערוך רשימות תלויות מרובות (נניח שאחרי רחוב היה מספר בית, ואז משפחה)
    האם שי דרך?

  15. שבוע טוב!
    יש לי טבלה.
    עמודה A יצרן רכב
    עמודה B סוג הרכב
    עמודה C דגם הרכב

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

    איך ניתן לעשות?

    תודה.

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

  16. אשמח לדעת עם יש אפשרות לעשות שאני מתחיל לכתוב שם והוא ישלים אותיט

  17. האם יש אפשרות להפוך תא עם טקסט (שם + שם משפחה) לראשי תיבות?

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

  18. האם ניתן שבתא A2 אעשה רשימת נתונים וברגע שאני בוחר נתון מסוים בתא B2 אקבל רשימת נתונים שאקבע
    וברגע שאבחר ב A2 נתון אחר אראה רשימה אחרת ב B2

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

  19. לא מצליח ליצור רשימה נפתחת מתוך רשימה נפתחת התלוי במה שבחרתי
    לפי' הדוג' ערים ורחובות
    אחרי שכתבתי שורה עם ערים ועוד שורה עם רחובות לפי כל עיר A1 בוחר עיר הצלחתי מתור רשימה אבל B1 שיתן רשימה לפי מה שבחרתי ב A1 לא מצליח

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

  20. תודה רבה! בדיוק הייתי זקוק לכך בעבודה.
    שאלה קטנה…כאשר אני חוזר לרשימה של העיר ומחליף עיר אז עדייה ברשימת הרחובות נשאר שם הרחוב הקודם.
    האם אפשר שבבחירה בערים שדה הרחובות יתנקה?

    תודה

להגיב על מיכל לבטל

האימייל לא יוצג באתר. שדות החובה מסומנים *