תוכן
ניסוח הבעיה
נניח שיש לנו שולחן כזה שאנחנו צריכים "לרקוד" איתו כל יום:
למי שהשולחן נראה קטן - תכפילו אותו נפשית פי עשרים לפי אזור, הוסיפו עוד כמה בלוקים ועוד שניים תריסר ערים גדולות.
המשימה היא להסיר זמנית מהמסך שורות ועמודות שאינן נחוצות כרגע לעבודה, כלומר,
- להסתיר פרטים לפי חודש, ולהשאיר רק רבעים
- להסתיר סכומים לפי חודשים ורבעונים, ולהשאיר רק את הסכום למשך חצי שנה
- להסתיר ערים מיותרות כרגע (אני עובד במוסקבה - למה לי לראות את סנט פטרבורג?) וכו'.
בחיים האמיתיים, יש ים של דוגמאות לטבלאות כאלה.
שיטה 1: הסתרת שורות ועמודות
השיטה, למען האמת, פרימיטיבית ולא נוחה במיוחד, אבל אפשר לומר עליה שתי מילים. ניתן להסתיר כל שורה או עמודה שנבחרו בעבר בגיליון על ידי לחיצה ימנית על כותרת העמודה או השורה ובחירת הפקודה מתפריט ההקשר הסתר (להתחבא):
לתצוגה הפוכה, בחר בשורות/עמודות סמוכות ובלחיצה ימנית בחר מהתפריט, בהתאמה, תצוגה (הצג).
הבעיה היא שצריך להתמודד עם כל עמודה ושורה בנפרד, וזה לא נוח.
שיטה 2. קיבוץ
אם תבחר מספר שורות או עמודות ולאחר מכן בחר מהתפריט נתונים – קבוצה ומבנה – קבוצה (נתונים - קבוצה ומתאר - קבוצה), אז הם יהיו מוקפים בסוגר מרובע (מקובץ). יתר על כן, ניתן לקנן קבוצות אחת בשנייה (מותרות עד 8 רמות קינון):
דרך נוחה ומהירה יותר היא להשתמש בקיצור מקלדת כדי לקבץ שורות או עמודות שנבחרו מראש. Alt+Shift+חץ ימינה, ועל ביטול קיבוץ Alt+Shift+חץ שמאלה, בהתאמה.
שיטה זו של הסתרת נתונים מיותרים היא הרבה יותר נוחה - אתה יכול ללחוץ על הכפתור עם "+"או"-", או בכפתורים עם רמת קיבוץ מספרית בפינה השמאלית העליונה של הגיליון - אז כל הקבוצות של הרמה הרצויה יכוווצו או ירחבו בבת אחת.
אז, אם הטבלה שלך מכילה שורות או עמודות סיכום עם הפונקציה של סיכום תאים שכנים, כלומר, סיכוי (לא 100% נכון) שאקסל הוא יצור את כל הקבוצות הדרושות בטבלה בתנועה אחת - דרך התפריט נתונים – קבוצה ומבנה – יצירת מבנה (נתונים - קבוצה ומתאר - צור מתאר). למרבה הצער, פונקציה כזו עובדת בצורה מאוד לא צפויה ולפעמים עושה שטויות מוחלטות בטבלאות מורכבות. אבל אתה יכול לנסות.
ב-Excel 2007 ואילך, כל ההנאות הללו נמצאות בכרטיסייה נתונים (תַאֲרִיך) בקבוצה מִבְנֶה (מתווה):
שיטה 3. הסתרת שורות/עמודות מסומנות עם מאקרו
שיטה זו היא אולי המגוונת ביותר. בואו נוסיף שורה ריקה ועמודה ריקה לתחילת הגיליון שלנו ונסמן בכל סמל את השורות והעמודות שברצוננו להסתיר:
עכשיו בואו נפתח את עורך Visual Basic (ALT + F11), הכנס מודול ריק חדש לספר שלנו (תפריט הכנס - מודול) והעתיקו לשם את הטקסט של שתי פקודות מאקרו פשוטות:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False 'השבת את עדכון המסך כדי להאיץ עבור כל תא ב-ActiveSheet.UsedRange.Rows(1).Cells 'חזרה על כל התאים בשורה הראשונה If cell.Value = "x " ואז תא .EntireColumn.Hidden = True 'אם בתא x - הסתר עמודה הבא עבור כל תא ב-ActiveSheet.UsedRange.Columns(1).Cells 'עברו דרך כל התאים של העמודה הראשונה אם cell.Value = "x" ואז cell.EntireRow.Hidden = True 'אם בתא x - הסתר את השורה Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'בטל את כל השורות והעמודות המסתרות Rows.Hidden = False End Sub Show
כפי שאתה יכול לנחש, המאקרו הסתר הסתרות והמאקרו לְהַצִיג - מציג שורות ועמודות עם תווית אחורית. אם תרצה, ניתן להקצות לפקודות מאקרו מקשי קיצור (Alt + F8 ולחצן פרמטרים), או צור לחצנים ישירות על הגיליון כדי להפעיל אותם מהכרטיסייה מפתח – הוספה – כפתור (מפתח - הוספה - לחצן).
שיטה 4. הסתרת שורות/עמודות עם צבע נתון
נניח שבדוגמה לעיל, אנחנו, להיפך, רוצים להסתיר את הסכומים, כלומר שורות סגולות ושחורות ועמודות צהובות וירוקות. אז יהיה צורך לשנות מעט את המאקרו הקודם שלנו על ידי הוספת, במקום בדיקת נוכחות "x", סימון התאמת צבע המילוי לתאים לדוגמה שנבחרו באקראי:
Sub HideByColor() עמום התא כטווח Application.ScreenUpdating = False עבור כל תא ב-ActiveSheet.UsedRange.Rows(2).Cells If cell.Interior.Color = Range("F2").Interior.Color ואז cell.EntireColumn.Hidden = True If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True Next For כל תא ב-ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Range ("D6").Interior.Color Then cell.EntireRow.Hidden = True If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub עם זאת, אסור לנו לשכוח אזהרה אחת: מאקרו זה עובד רק אם התאים של טבלת המקור מולאו בצבע באופן ידני, ולא באמצעות עיצוב מותנה (זו מגבלה של המאפיין Interior.Color). כך, למשל, אם הדגשת אוטומטית את כל העסקאות בטבלה שבהן המספר קטן מ-10 באמצעות עיצוב מותנה:
... ואתה רוצה להסתיר אותם בתנועה אחת, אז המאקרו הקודם יצטרך להיות "סיים". אם יש לך Excel 2010-2013, אז אתה יכול לצאת באמצעות במקום הנכס פנים רכוש DisplayFormat.Interior, שמוציא את צבע התא, ללא קשר לאופן הגדרתו. המאקרו להסתרת הקווים הכחולים עשוי להיראות כך:
Sub HideByConditionalFormattingColor() עמום תא כטווח Application.ScreenUpdating = False עבור כל תא ב-ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color ואז תא .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Sub תא G2 נלקח כדוגמה להשוואת צבעים. לצערי הנכס תצוגה הופיע באקסל רק החל מגרסת 2010, כך שאם יש לך Excel 2007 ומעלה, תצטרך להמציא דרכים אחרות.
- מהו מאקרו, היכן להכניס קוד מאקרו, כיצד להשתמש בהם
- קיבוץ אוטומטי ברשימות מרובות רמות