סינון עמודות אופקי באקסל

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

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

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

שיטה 1. פונקציית FILTER חדשה

אם אתה בגרסה החדשה של Excel 2021 או מנוי ל-Excel 365, אתה יכול לנצל את התכונה החדשה שהוצגה סנן (לְסַנֵן), שיכול לסנן את נתוני המקור לא רק לפי שורות, אלא גם לפי עמודות. כדי לעבוד, פונקציה זו דורשת שורת מערך חד-ממדית אופקית עזר, שבה כל ערך (TRUE או FALSE) קובע אם נציג או, להיפך, מסתיר את העמודה הבאה בטבלה.

בואו נוסיף את השורה הבאה מעל הטבלה שלנו ונכתוב בה את הסטטוס של כל עמודה:

• נניח שתמיד נרצה להציג את העמודה הראשונה והאחרונה (כותרות וסיכומים), אז עבורן בתא הראשון והאחרון של המערך אנחנו מגדירים את הערך = TRUE.
• עבור העמודות הנותרות, התוכן של התאים המתאימים יהיה נוסחה שבודקת את המצב שאנו צריכים באמצעות פונקציות И (ו) or OR (OR). לדוגמה, שהסך הכולל הוא בטווח שבין 300 ל-500.

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

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

שיטה 2. טבלת ציר במקום הרגילה

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

• יש שורת כותרת "נכונה" בת שורה אחת ללא תאים ריקים ומוזגים - אחרת זה לא יעבוד לבנות טבלת ציר;
• אין להכיל כפילויות בתוויות של שורות ועמודות - הן "יתמוטטו" בסיכום לרשימה של ערכים ייחודיים בלבד;
• מכילים רק מספרים בטווח הערכים (בצומת שורות ועמודות), כי טבלת הציר בהחלט תחיל עליהם איזושהי פונקציית צבירה (סכום, ממוצע וכו') וזה לא יעבוד עם הטקסט

אם כל התנאים הללו מתקיימים, אז על מנת לבנות טבלת ציר שנראית כמו הטבלה המקורית שלנו, יהיה צורך להרחיב אותה (המקורית) מהצלבה לשטוחה (מנורמל). והדרך הקלה ביותר לעשות זאת היא באמצעות תוסף Power Query, כלי רב עוצמה לשינוי נתונים המובנה ב-Excel מאז 2016. 

אלו הם:

1. בואו נמיר את הטבלה לפקודה דינמית "חכמה". בית - עיצוב כטבלה (בית - עיצוב כטבלה).
2. טוען אל Power Query עם הפקודה נתונים - מטבלה / טווח (נתונים - מטבלה / טווח).
3. אנחנו מסננים את השורה עם הסכומים (לסיכום יהיו סיכומים משלו).
4. לחץ לחיצה ימנית על כותרת העמודה הראשונה ובחר בטל כיווץ של עמודות אחרות (ביטול ציר עמודות אחרות). כל העמודות שלא נבחרו מומרות לשניים - שם העובד וערך המחוון שלו.
5. סינון העמודה עם הסכומים שנכנסו לעמודה תְכוּנָה.
6. אנו בונים טבלת ציר לפי הטבלה השטוחה (המנורמלת) שהתקבלה עם הפקודה בית - סגור וטען - סגור וטען פנימה... (בית - סגור וטען - סגור וטען ל...).

עכשיו אתה יכול להשתמש ביכולת לסנן עמודות הזמינות בטבלאות ציר - הסימנים הרגילים לפני השמות והפריטים מסנני חתימה (מסנני תווית) or מסננים לפי ערך (מסנני ערך):

וכמובן, בעת שינוי הנתונים, תצטרכו לעדכן את השאילתה שלנו ואת הסיכום עם קיצור מקלדת Ctrl+אחר+F5 או צוות נתונים - רענן הכל (נתונים - רענן הכל).

שיטה 3. מאקרו ב-VBA

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

נניח שברצוננו לסנן עמודות תוך כדי תנועה שבהן שם המנהל בכותרת הטבלה עונה על המסכה המצוינת בתא הצהוב A4, למשל, מתחיל באות "A" (כלומר, קבלו "אנה" ו"ארתור" " כתוצאה). 

כמו בשיטה הראשונה, אנו מיישמים תחילה שורת טווח עזר, כאשר בכל תא הקריטריון שלנו ייבדק על ידי נוסחה והערכים הלוגיים TRUE או FALSE יוצגו עבור עמודות גלויות ומוסתרות, בהתאמה:

אז בואו נוסיף מאקרו פשוט. לחץ לחיצה ימנית על לשונית הגיליון ובחר בפקודה מָקוֹר (קוד מקור). העתק והדבק את קוד ה-VBA הבא בחלון שנפתח:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Every cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

ההיגיון שלה הוא כדלקמן:

• באופן כללי, זהו מטפל באירועים גליון עבודה_שינוי, כלומר מאקרו זה יפעל אוטומטית בכל שינוי בתא כלשהו בגיליון הנוכחי.
• ההתייחסות לתא שהשתנה תמיד תהיה במשתנה יעד.
• ראשית, אנו בודקים שהמשתמש שינה בדיוק את התא עם הקריטריון (A4) - זה נעשה על ידי המפעיל if.
• ואז מתחיל המחזור לכל אחד… לחזור על תאים אפורים (D2:O2) עם ערכי מחוון TRUE / FALSE לכל עמודה.
• אם הערך של התא האפור הבא הוא TRUE (true), אז העמודה לא מוסתרת, אחרת נסתיר אותה (מאפיין מוּסתָר).

•  פונקציות מערך דינמי מ-Office 365: FILTER, SORT ו-UNIC
• טבלת ציר עם כותרת מרובת שורות באמצעות Power Query
• מהן פקודות מאקרו, כיצד ליצור אותן ולהשתמש בהן