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

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

כפי שאפשר לראות, התוצאה הסופית היא שיש לי 2 רשימות נפתחות שתלויות אחת בשניה, המכונה גם תיבת בחירה או תיבת רשימה נפתחת.

בואו נראה כיצד עושים את זה בשלוש שלבים פשוטים.

קרדיט: הרעיון לשיטה הזאת - לקוח מסרטון של מרק פרוקטור. יש לו ערוץ YouTube מאוד נפוץ שנקרא Excel off the grid. והנוסחה - היא מעשה ידיו.

3 השלבים לרשימות נפתחות שתלויות אחת בשניה:

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

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

תהליך ההגדרה:

• יש לגשת ללשונית “נוסחאות” .
• יש להשתמש באפשרות “הגדרת שם” Define Name.
• יש לקרוא לשם המוגדר DDL.
  • בשלב זה יש להדביק את הנוסחה הבאה

=LAMBDA(range,[lookup1],[lookup2],[lookup3],[lookup4],[lookup5],[lookup6],[lookup7],[lookup8],[lookup9],[lookup10],
LET(
_s, "%^&&@", 
lookupValue, lookup1 & _s & lookup2 & _s & lookup3 & _s & lookup4 & _s & lookup5 & _s & lookup6 & _s & lookup7 & _s & lookup8 & _s & lookup9 & _s & lookup10,
levelIndex, IFERROR(ROWS(TEXTSPLIT(lookupValue, , _s, TRUE())), 0) + 1, 
lookupArray, BYROW(EXPAND(CHOOSECOLS(range, SEQUENCE(1, levelIndex - 1)), , 10, ""), LAMBDA(row, TEXTJOIN(_s, FALSE, row))),
returnRange, INDEX(range, 0, levelIndex), 
result, IF(ISOMITTED(lookup1) * levelIndex = 1, returnRange, XLOOKUP(lookupValue, lookupArray, returnRange):XLOOKUP(lookupValue, lookupArray, returnRange, , , -1)),
result))

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

שלב שני - יצירת מערך זולג

2 דגשים חשובים:

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

כדי ליצור מזה מערך זולג:

• בחרו איזור רייק
• ובאחד התאים, הקלידו = ואז את שם הטבלה שלכם.

מרגע שהנתונים מהווים מערך זולג, אפשר להשתמש במערך הזולג הזה בתוך הנוסחה שלנו DDL ליצירת הרשימות הנפתחות.

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

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

יצירת הרשימה הנפתחת של המחוזות:
ניצור קודם כל רשימה נפתחת עבור המחוזות:
לצורך כך:

• בתפריט נתונים > נלחץ על אימות נתונים.
• כאן נבחר ב- רשימה
• ובנוסחה, נרשום
  • את הנוסחה =DLL
  • ואז את התא הראשון של המערך הזולג שלנו
  • ובסיום הכתובת נוסיף סימן סולמית #
    המשמעות של הסימן סולמית היא “תזלוג עד הסוף”. זאת אומרת עד היכן שהרשימה נגמרת. (כבר עשיתי גם על זה סרטון על מערכים זולגים)

ברגע שאני אלחץ פה אישור, אז הרשימה הנפתחת הראשונה שלנו מוכנה.

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

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

כאן כבר 2 התיבות עובדות בצורה יפה.

סיכום - רשימות נפתחות תלויות ב-Excel

למדנו כיצד לעשות רשימות נפתחות שתלויות אחת בשניה. בשלב ראשון, הוספנו את הנוסחה DDL אל ה-Excel שלנו. בשלב שני, יצרנו מערך זולג. אבל עם הדגש הבא: הטבלה צריכה להיות ממוינת, בכל הרמות. אבל אם הטבלה שלכם לא ממוינת, אז במקום ליצור מערך זולג באמצעות השיטה שהצגתי, פשוט תשתמשו בנוסחה =DDLSorter - כפי שמוצג למטה. בשלב השלישי, פשוט הגדרנו באקסל רשימת אימות נתונים, שבמסגרתה מאפשרים בחירה מרשימה באמצעות תפריט נפתח בתא, דומה לתיבת רשימה (List Box). ובתוכו, השתמשנו בנוסחה DDL ועם הסימן של הסולמית (#) כדי לציין שמדובר במערך זולג. הסימן של הסולמית, הוא יתרון, כיוון שגם אם בעתיד אנחנו נוסיף פה שורות לטבלה המקורית ונוסיף עוד יישובים, אז הם יתווספו אוטומטית למערך הזולג - וגם הרשימות שלנו יתעדכנו אוטומטית.

נספח - DDL SORTER

למי שלא יכול למיין את הטבלה שלו, אז במקום ליצור מערך זולג ישירות מתוך הטבלה.
נעשה כך (זה תחליף לשלב ה-2 ).

ניצור את הנוסחה DDL SORTER
ואז נשתמש בה על מנת ליצור מערך זולג

יצירת הנוסחה DDL SORTER באקסל

• יש לגשת ללשונית “נוסחאות” .
• יש להשתמש באפשרות “הגדרת שם” Define Name.
• יש לקרוא לשם המוגדר DDLSORTER
  • בשלב זה יש להדביק את הנוסחה הבאה

=LAMBDA(Range,[SortOrder],SORT(Range, SEQUENCE(, COLUMNS(Range)), SortOrder))

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

שימוש בנוסחה DDL SORTER

על מנת להשתמש בנוסחה כדי ליצור מערך זולג - פשוט תעשו כך:

• בחרו איזור רייק
• רשמו =DDLSORTER
• ואז בפרמטר הראשון - את שם הטבלה (הלא ממוינת).
• ובפרמטר השני {1} - המספר 1 מסמן שממיינים בסדר עולה. אם רוצים למיין בסדר יורד - נרשום -1. אם יש כמה עמודות, אפשר לרשום 1 לכל עמודה ולהפריד בינהם באמצעות פסיק.

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

לטובת קהל המחפשים, אלו כל השמות בהן אנשים קוראים ל-“אימות נתונים מתוך רשימה”:
רשימה נפתחת
אימות נתונים
אימות נתונים מתוך רשימה
רשימת אימות נתונים
תיבת בחירה
בחירה מרשימה
תיבת רשימה נפתחת
תפריט נפתח בתא
תיבה משולבת (Combo Box)
תיבת רשימה (List Box)
תיבה
בוקסה.