מילוי אוטומטי חכם למטה וימינה

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

Imagine: the open space office of the representative office of an international FMCG company, huge as a football field. Chic design, expensive office equipment, dress code, expats cooing in the corners – that’s all 🙂 In one of the meeting rooms, I start a two-day advanced training on the then current version of Excel 2003 for 15 key employees of the economic department, along with their leader. We get acquainted, I ask them about business tasks, problems, I ask them to show several typical work files. They show the kilometer length of unloading from SAP, the sheets of reports that they make on this, etc. Well, it’s a familiar thing – I mentally figure out topics and timing, adjust to the audience. Out of the corner of my eye, I notice how one of the participants, demonstrating a piece of his report, patiently pulls the cell with the formula down by the black cross in the lower right corner for several thousand lines, then skips the end of the table on the fly, pulls it back, etc. Unable to stand it, I interrupt him curling the mouse around the screen and show a double-click on the black cross, explaining about auto-completion down to the stop. 

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

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

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

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

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

בואו ננסה לתקן את החסרונות האלה עם מאקרו פשוט.

לחץ על קיצור המקלדת שמאלה Alt + F11 או כפתור Visual Basic כרטיסייה מפתחים (מפתח). הוסף מודול ריק חדש דרך התפריט הכנס - מודול והעתק לשם את הטקסט של פקודות המאקרו האלה:

Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion אם rng.Cells.Count > 1 אז n = rng.Cells(1).Row + rng.Rows. ספירה - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues ​​End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion אם rng.Cells.Count > 1 אז n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1,n), הקלד: =xlFillValues ​​End If End Sub  

פקודות מאקרו כאלה:

• יכול למלא לא רק למטה (SmartFillDown), אלא גם ימינה (SmartFillRight)
• אל תקלקל את הפורמט של התאים למטה או מימין - רק הנוסחה (הערך) מועתקת
• מתעלמים מתאים ריקים סמוכים וההעתקה מתרחשת בדיוק לסוף הטבלה, ולא לפער הקרוב ביותר בנתונים או בתא התפוס הראשון.

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

יופי.

PS חלק מהבעיה בהעתקת נוסחאות לסוף הטבלה נפתרה באקסל 2007 עם הופעת "טבלאות חכמות". נכון, הם לא תמיד מתאימים ולא בכל מקום. ומימין, Excel מעולם לא למד להעתיק לבד.

• מהן פקודות מאקרו, כיצד להשתמש בהן, היכן ניתן להשיג קוד Visual Basic ואיפה להדביק אותו.
• טבלאות חכמות באקסל 2007-2013
• העתק נוסחאות ללא שינוי קישור