צפה בוידאו - מלא למטה ב- Excel
הרבה פעמים, אתה עלול להיתקל במערך נתונים שבו רק תא אחד מלא בנתונים והתאים שמתחתיו ריקים עד לערך הבא.
משהו כפי שמוצג להלן:
בעוד שפורמט זה פועל עבור אנשים מסוימים, הבעיה עם נתונים מסוג זה היא שאינך יכול להשתמש בהם ליצירת טבלאות ציר או להשתמש בהם בחישובים.
ולזה יש תיקון קל!
במדריך זה, אראה לך כיצד לבצע במהירות מלאו תאים ב- Excel עד לערך המלא הבא.
אתה יכול לעשות זאת בקלות באמצעות טכניקת תיבת דו-שיח מיוחדת פשוטה של Go-To, VBA או Power Query.
אז בואו נתחיל!
שיטה 1 - מילוי באמצעות עבור אל Special + Formula
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה למלא נתונים בעמודה A ובעמודה B.
בעמודה B, המטרה היא למלא את 'מדפסת' עד לתא הריק האחרון שמתחתיו, ולאחר מכן כאשר 'סורק' מתחיל, ולאחר מכן למלא את 'סורק' בתאים למטה עד שהתאים ריקים.
מתחת לשלבי השימוש, עבור אל מיוחד כדי לבחור את כל התאים הריקים ומלא באמצעות נוסחה פשוטה:
- בחר את הנתונים שבהם ברצונך למלא (A1: D21 בדוגמה שלנו)
- עבור לכרטיסייה 'דף הבית'
- בקבוצת העריכה, לחץ על הסמל 'מצא ובחר' (פעולה זו תציג עוד כמה אפשרויות בתפריט הנפתח)
- לחץ על האפשרות 'עבור אל'
- בתיבת הדו-שיח ניגש, לחץ על הלחצן מיוחד. פעולה זו תפתח את תיבת הדו -שיח 'עבור למיוחד'
- בתיבת הדו -שיח מעבר למיוחד לחץ על החסר
- לחץ על אישור
השלבים שלעיל יבחרו את כל התאים הריקים במערך הנתונים (כפי שמוצג להלן).
בתאים הריקים שנבחרו, תבחין שתא אחד קל יותר משאר הבחירות. תא זה הוא התא הפעיל בו נזין נוסחה.
אל תדאג היכן התא נמצא בבחירה, שכן השיטה שלנו תפעל בכל מקרה.
כעת, להלן השלבים למילוי הנתונים בתאים הריקים שנבחרו:
- הקש על המקש שווה (=) במקלדת. זה יכניס תו שווה לתא הפעיל
- הקש על מקש החץ למעלה. זה יכניס את הפניה לתא של התא מעל התא הפעיל. לדוגמה, במקרה שלנו, B3 הוא התא הפעיל וכאשר אנו מבצעים את שני השלבים הללו, הוא נכנס = B2 בתא
- החזק את מקש השליטה ולחץ על מקש Enter
השלבים שלעיל יכניסו אוטומטית את כל התאים הריקים עם הערך שמעליהם.
למרות שזה עשוי להיראות כמו יותר מדי שלבים, אך ברגע שתבין את השיטה הזו, תוכל למלא נתונים במהירות ב- Excel תוך מספר שניות.
כעת, יש עוד שני דברים שאתה צריך לדאוג להם בעת שימוש בשיטה זו.
המרת נוסחאות לערכים
הראשון הוא לוודא שאתה ממיר נוסחאות לערכים (כך שיהיו לך ערכים סטטיים ודברים לא יתבלבלו במקרה שתשנה נתונים בעתיד).
להלן סרטון שיראה לך כיצד להמיר נוסחאות במהירות לערכים ב- Excel.
שנה את תבנית התאריך
במקרה שאתה משתמש בתאריכים בנתונים שלך (כפי שאני בנתונים לדוגמה), תבחין כי הערכים הממולאים בעמודה תאריך הם מספרים ולא תאריכים.
אם הפלט בעמודה תאריך הוא בפורמט התאריך הרצוי, אתה טוב ואינך צריך לעשות שום דבר אחר.
אך אם אלה אינם בפורמט התאריך הנכון, עליך לבצע התאמה קלה. אמנם יש לך את הערכים הנכונים, אך רק עליך לשנות את הפורמט כך שיוצג כתאריך בתא.
להלן השלבים לביצוע פעולה זו:
- בחר בעמודה המכילה את התאריכים
- לחץ על הכרטיסייה דף הבית
- בקבוצה מספר, לחץ על התפריט הנפתח עיצוב ולאחר מכן בחר את תבנית התאריך.
אם אתה צריך לבצע מילוי זה מדי פעם, אני ממליץ לך להשתמש בטכניקה מיוחדת זו של נוסחאות נוסחה.
למרות שיש לו כמה שלבים, זה פשוט ונותן לך את התוצאה ממש במערך הנתונים שלך.
אבל למקרה שתצטרך לעשות זאת לעתים קרובות למדי, אז אני מציע שתסתכל על ה- VBA ועל שיטות ה- Power Query שיפורטו בהמשך.
שיטה 2 - מילוי באמצעות קוד VBA פשוט
אתה יכול להשתמש בקוד מאקרו פשוט של VBA כדי למלא במהירות תאים ב- Excel עד לערך הריק האחרון.
אתה רק צריך להוסיף את קוד VBA לקובץ פעם אחת ולאחר מכן תוכל להשתמש בקוד בקלות במספר פעמים באותו חוברת עבודה או אפילו בחוברות עבודה רבות במערכת שלך.
להלן קוד ה- VBA שיעבור בכל תא בבחירה וימלא את כל התאים הריקים:
'קוד VBA זה נוצר על ידי Sumit Bansal מ- TrumpExcel.com Sub FillDown () עבור כל תא שנבחר אם cell = "" ואז cell.FillDown מסתיים אם הקצה הבא Sub
הקוד לעיל משתמש בלולאת For לעבור בכל תא בבחירה.
בתוך הלולאה For השתמשתי בתנאי If-Then הבודק אם התא ריק או לא.
במקרה שהתא ריק, הוא מתמלא בערך מהתא שלמעלה, ואם הוא אינו ריק, אז לולאת ה- for פשוט מתעלמת מהתא ועוברת לתא הבא.
עכשיו שיש לך את קוד VBA, תן לי להראות לך היכן לשים את הקוד הזה ב- Excel:
- בחר את הנתונים שברצונך למלא
- לחץ על הכרטיסייה מפתחים
- לחץ על הסמל של Visual Basic (או שאתה יכול להשתמש בקיצור המקשים ALT + F11). פעולה זו תפתח את עורך Visual Basic ב- Excel
- בעורך Visual Basic, משמאל, יהיה לך סייר הפרויקטים. אם אינך רואה אותו, לחץ על האפשרות 'תצוגה' בתפריט ולאחר מכן לחץ על סייר הפרוייקטים
- במקרה שיש לך מספר חוברות עבודה פתוחות של Excel, סייר הפרויקט יראה לך את כל שמות חוברות העבודה. אתר את שם חוברת העבודה שבה יש לך את הנתונים.
- לחץ לחיצה ימנית על כל אחד מהאובייקטים בחוברת העבודה, עבור אל הוספה ולאחר מכן לחץ על מודול. זה יוסיף מודול חדש עבור חוברת העבודה
- לחץ פעמיים על 'המודול' שהכנסת בשלב זה למעלה. הוא יפתח את חלון הקוד לאותו מודול
- העתק והדבק את קוד ה- VBA בחלון הקוד
- מקם את הסמן בכל מקום בקוד ולאחר מכן הפעל את קוד המאקרו על ידי לחיצה על הכפתור הירוק בסרגל הכלים או באמצעות קיצור המקשים F5
השלבים שלעיל יריצו את קוד VBA והנתונים שלך יתמלאו.
אם ברצונך להשתמש שוב בקוד VBA זה בעתיד, עליך לשמור קובץ זה כחוברת עבודה מאקרו המאפשרת מאקרו (עם סיומת .XLSM)
דבר נוסף שאתה יכול לעשות הוא להוסיף את המאקרו הזה לסרגל הכלים לגישה מהירה, שהוא תמיד גלוי ותהיה לך גישה למאקרו זה בלחיצה אחת (בחוברת העבודה שבה יש לך את הקוד במגמה האחורית).
לכן, בפעם הבאה שיש לך נתונים שבהם עליך למלא, כל שעליך לעשות הוא לבצע את הבחירה ולאחר מכן ללחוץ על כפתור המאקרו בסרגל הכלים לגישה מהירה.
תוכל גם להוסיף מאקרו זה לחוברת העבודה של המאקרו האישי ולאחר מכן להשתמש בו בכל חוברת עבודה במערכת שלך.
לחץ כאן לקריאה נוספת אודות חוברות עבודה מאקרו אישיות וכיצד להוסיף להן קוד.
שיטה 3 - מילוי באמצעות שאילתת חשמל
ל- Power Query יש פונקציונליות מובנית המאפשרת לך למלא בלחיצה אחת.
מומלץ להשתמש ב- Power Query כאשר אתה משתמש בו בכל זאת כדי להפוך את הנתונים שלך או לשלב נתונים מגליונות עבודה מרובים או מחוברות עבודה מרובות.
כחלק מזרימת העבודה הקיימת שלך, תוכל להשתמש באפשרות המילוי ב- Power Query כדי למלא במהירות את התאים הריקים.
כדי להשתמש ב- Power Query, מומלץ שהנתונים שלך יהיו בפורמט טבלת Excel. אם אינך יכול להמיר את הנתונים שלך לטבלת Excel, יהיה עליך ליצור טווח עם שם עבור הנתונים ולאחר מכן להשתמש בטווח זה בשם ב- Power Query.
להלן יש לי את ערכת הנתונים שהסבתי כבר לטבלת אקסל. תוכל לעשות זאת על ידי בחירת מערך הנתונים, מעבר ללשונית הוספה ולאחר מכן לחיצה על סמל הטבלה.
מתחת לשלבים לשימוש ב- Power Query למילוי נתונים עד הערך הבא:
- בחר תא כלשהו במערך הנתונים
- לחץ על הכרטיסייה נתונים
- בקבוצה נתוני Get & Transform, לחץ על 'מתוך גיליון'. פעולה זו תפתח את עורך Power Query. שים לב שהתאים הריקים יציגו את הערך 'null' ב- Power Query
- בעורך Power Query, בחר את העמודות שעבורן ברצונך למלא. לשם כך, החזק את מקש Ctrl ולאחר מכן לחץ על כותרת העמודות שברצונך לבחור. בדוגמה שלנו, זה יהיה עמודות התאריך והמוצר.
- לחץ לחיצה ימנית על כל אחת מהכותרות שנבחרו
- עבור לאפשרות 'מילוי' ולאחר מכן לחץ על 'למטה'. זה ימלא את הנתונים בכל התאים הריקים
- לחץ על הכרטיסייה קובץ ולאחר מכן לחץ על 'סגור וטען'. פעולה זו תוסיף גליון עבודה חדש בחוברת העבודה עם הטבלה המתקבלת
למרות ששיטה זו עשויה להישמע קצת מוגזמת, יתרון גדול בשימוש ב- Power Query הוא שהיא מאפשרת לך לעדכן במהירות את הנתונים שהתקבלו במקרה שהנתונים המקוריים שלך ישתנו.
לדוגמה, אם אתה מוסיף רשומות נוספות לנתונים המקוריים, אינך צריך לבצע את כל האמור לעיל שוב. אתה יכול פשוט ללחוץ לחיצה ימנית על הטבלה המתקבלת ולאחר מכן ללחוץ על רענן.
למרות ששיטת Power Query פועלת היטב, עדיף להשתמש בה כאשר אתה כבר משתמש ב- Power Query בתהליך העבודה שלך.
אם יש לך רק מערך נתונים שבו אתה רוצה למלא את התאים הריקים, שימוש נוח יותר בשיטה Go To או בשיטת VBA (מכוסה למעלה).
אז אלו שלוש דרכים פשוטות שתוכל להשתמש בהן מלאו תאים ריקים עד לערך הבא ב- Excel.
אני מקווה שמצאת הדרכה שימושית.
