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

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

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

תהליך יצירת רשימה

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

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

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

   

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

   

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

   

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

מבוסס על נתונים מהרשימה

נניח שיש לנו טבלה המתארת ​​את סוגי הפירות השונים.

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

1. בחר את התא השמור לרשימה העתידית.
2. מצא את הכרטיסייה נתונים ברצועת הכלים. שם אנו לוחצים על "אמת נתונים".
   

   

3. מצא את הפריט "סוג נתונים" והחלף את הערך ל"רשימה".
   

   

4. בשדה המציין את האפשרות "מקור", הזן את הטווח הרצוי. שימו לב שיש לציין הפניות מוחלטות כדי שבעת העתקת הרשימה המידע לא ישתנה.
   

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

עם רישום נתונים ידני

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

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

1. לחץ על התא לרשימה.
2. פתחו את "נתונים" ושם מצאו את הקטע "בדיקת נתונים" המוכר לנו.
   

   

3. שוב, בחר בסוג "רשימה".
   

   

4. כאן אתה צריך להזין "כן; לא" כמקור. אנו רואים שהמידע מוזן באופן ידני באמצעות נקודה-פסיק לצורך ספירה.

לאחר לחיצה על אישור, יש לנו את התוצאה הבאה.

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

יצירת רשימה נפתחת באמצעות הפונקציה OFFSET

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

בואו נפתח את הגיליון.

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

1. בחר את תא העניין שבו ברצונך למקם את הרשימה העתידית.
2. פתח את הכרטיסייה "נתונים" ואת החלון "אימות נתונים" ברצף.
   

   

3. הגדר "רשימה". זה נעשה באותו אופן כמו הדוגמאות הקודמות. לבסוף, נעשה שימוש בנוסחה הבאה: =OFFSET(A$2$;0;0;5). נזין אותו במקום שבו מצוינים התאים שישמשו כארגומנט.

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

התחביר לכך הוא:

=OFFSET(reference,line_offset,column_offset,[height],[width])

אנו רואים שלפונקציה זו יש 5 ארגומנטים. ראשית, ניתנת כתובת התא הראשונה שיש לקזז. שני הארגומנטים הבאים מציינים כמה שורות ועמודות לקזז. אם כבר מדברים עלינו, הארגומנט Height הוא 5 כי הוא מייצג את גובה הרשימה. 

רשימה נפתחת ב-Excel עם החלפת נתונים (+ באמצעות הפונקציה OFFSET)

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

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

1. בחר את התא המעניין.
2. הרחב את הכרטיסייה "נתונים" ולחץ על "אימות נתונים".
3. בחלון שנפתח, בחר שוב בפריט "רשימה" וציין את הנוסחה הבאה כמקור הנתונים: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. לחץ על אישור.

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

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

רשימה נפתחת עם נתונים מגיליון אחר או מקובץ אקסל

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

1. הפעל את התא שבו אנו ממקמים את הרשימה.
2. אנחנו פותחים את החלון שאנחנו כבר מכירים. באותו מקום בו ציינו בעבר מקורות לטווחים אחרים, מצוינת נוסחה בפורמט =INDIRECT("[List1.xlsx]Sheet1!$A$1:$A$9"). באופן טבעי, במקום List1 ו-Sheet1, אתה יכול להוסיף את שמות הספרים והגיליון שלך, בהתאמה. 

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

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

יצירת תפריטים נפתחים תלויים

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

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

1. צור רשימה ראשונה עם שמות טווחים.
   

   

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

   

3. צור רשימה 2 בהתאם לסוג הצמח שהאדם בחר. לחילופין, אם תציין עצים ברשימה הראשונה, המידע ברשימה השנייה יהיה "אלון, קרן קרן, ערמון" ומעבר לכך. יש צורך לרשום את הנוסחה במקום הקלט של מקור הנתונים =INDIRECT(E3). E3 – תא המכיל את שם הטווח 1.=INDIRECT(E3). E3 – תא עם שם הרשימה 1.

עכשיו הכל מוכן.

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

לפעמים לא ניתן לתת עדיפות לערך אחד בלבד, ולכן יש לבחור יותר מאחד. לאחר מכן עליך להוסיף מאקרו לקוד העמוד. שימוש בשילוב המקשים Alt + F11 פותח את עורך Visual Basic. והקוד מוכנס שם.

גיליון עבודה משנה פרטי (ByVal Target As Range)

    על שגיאה קורות הבא

    If Not Intersect(Target, Range(«Е2:Е9»)) Is Nothing And Target.Cells.Count = 1 אז

        Application.EnableEvents = False

        אם לן (Target.Offset (0, 1)) = 0 אז

            Target.Offset (0, 1) = יעד

        אחר

            Target.End (xlToRight) .Offset (0, 1) = יעד

        סוף אם

        Target.ClearContents

        Application.EnableEvents = נכון

    סוף אם

End Sub 

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

גיליון עבודה משנה פרטי (ByVal Target As Range)

    על שגיאה קורות הבא

    If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 אז

        Application.EnableEvents = False

        אם לן (Target.Offset (1, 0)) = 0 אז

            Target.Offset (1, 0) = יעד

        אחר

            Target.End (xlDown) .Offset (1, 0) = יעד

        סוף אם

        Target.ClearContents

        Application.EnableEvents = נכון

    סוף אם

End Sub

ולבסוף, הקוד הזה משמש לכתיבה בתא אחד.

גיליון עבודה משנה פרטי (ByVal Target As Range)

    על שגיאה קורות הבא

    If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 אז

        Application.EnableEvents = False

        newVal = יעד

        יישום.בטל

        oldval = יעד

        אם לן (oldval) <> 0 ו-oldval <> newVal אז

            Target = Target & «,» & newVal

        אחר

            יעד = newVal

        סוף אם

        אם Len (newVal) = 0 אז Target.ClearContents

        Application.EnableEvents = נכון

    סוף אם

End Sub

טווחים ניתנים לעריכה.

איך עושים רשימה נפתחת עם חיפוש?

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

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

רשימה נפתחת עם החלפת נתונים אוטומטית

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

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

   

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

   

   

לאחר מכן, עליך לאשר טווח זה על ידי לחיצה על כפתור "אישור".

אנו בוחרים את הטבלה המתקבלת ונותנים לה שם דרך שדה הקלט הממוקם על גבי עמודה A.

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

1. בחר את התא שבו ממוקמת הרשימה.
2. פתח את תיבת הדו-שיח אימות נתונים.
   

   

3. הגדרנו את סוג הנתונים ל"רשימה", וכערכים אנו נותנים את שם הטבלה דרך הסימן =.
   

   

   

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

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

כיצד להעתיק רשימה נפתחת?

כדי להעתיק, זה מספיק כדי להשתמש בשילוב המקשים Ctrl + C ו-Ctrl + V. אז הרשימה הנפתחת תועתק יחד עם העיצוב. כדי להסיר עיצוב, עליך להשתמש בהדבקה מיוחדת (בתפריט ההקשר, אפשרות זו מופיעה לאחר העתקת הרשימה), שם מוגדרת האפשרות "תנאים על ערכים".

בחר את כל התאים המכילים רשימה נפתחת

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

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