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