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

לסינון האוטומטי יש הרבה חסרונות

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

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

1. הוא ידני לחלוטין. כדי להפעילו, המשתמש חייב ללחוץ על החץ בראש העמודה, לבחור את אפשרות הסינון הרלוונטית ולהזין/לבחור ערכים באופן ידני. השיטה המוצעת בפוסט זה מאפשרת לבצע סינון גם ע"י שימוש בנוסחאות ופרמטרים.

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

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

4. הוא יוצר בעיות עם העתקת/הדבקת נתונים. כאשר מעתיקים נתונים המסוננים ע"י הסינון האוטומטי, הטווח המועתק מקוטע וכולל רק את השורות הגלויות – מצב שלעתים מוביל למצבים בעייתיים בהדבקה מחדש של הנתונים. כאמור, בשיטה המוצעת אין הסתרת שורות.

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

1. נתונים

קליק להגדלה

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

2. טבלת השליפה

קליק להגדלה

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

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

3. טבלת עזר לחישוב עמידה בקריטריונים

נבצע פעם נוספת את השלב הקודם: נעתיק את שורת כותרות העמודות ונדביק אותה ליד הטבלה המקורית. הפעם, נוסיף עמודה נוספת בשם "כל הקריטריונים" אשר תשמש כאינדיקטור לעמידה בכל התנאים.

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

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

חשוב לשים לב שכמות השורות בטבלת העזר זהה לטבלת הנתונים המקורית.

קליק להגדלה

4. שליפת המספרים של שורות הנתונים שעומדות בתנאים

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

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

קליק להגדלה

שימו לב למספר אלמנטים חשובים בנוסחה. עמודה A מכילה את המספרים הסידוריים של טבלת הנתונים המקורית – אלה הערכים שנרצה לשלוף לטבלת התוצאות. תנאי הבדיקה IF מופעל על העמודה המסכמת "כל הקריטריונים". חשוב לשים לב שבנוסחא ROWS($L$8:$L8 רק בחלק הראשון השורות מקובעות עם $, אבל לא בשני. ולסיום, עבור שורות שלא עומדות בקריטריונים יוצג תא ריק – "".

כעט, יש לעמוד על התא עם הנוסחא -> לחיצה על F2 כדי להיכנס למצב עריכה -> לחיצה על Ctrl+Shift+Enter כדי להפוך את הנוסחה לנוסחת מערך. לאחר השלמה מוצלחת של שלב זה, יופיעו סוגריים מסולסלות { } סביב הנוסחה.

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

5. השלמת נתוני העמודות האחרות

עתה נשתמש ב-vlookup ו-match כדי לשלוף את נתוני שאר העמודות לכל שורה רלוונטית.

קליק להגדלה

עבור כל עמודה, נשתמש ב-vlookup כדי לחפש את מספר השורה בעמודה N בטבלת הנתונים המקורית (A8:E107).

כדי שלא נצטרך להתאים נוסחה ייחודית לכל עמודה, נשתמש ב-Match כדי לחפש את כותרת העמודה הנוכחית (O$7) בשורת הכותרות בטבלה המקורית (A7:E7). הנה פוסט שמסביר איך משתמשים נכון ב-Vlookup יחד עם Match
יש לשכפל את הנוסחה לכל התאים של טבלת התוצאות, לכל שאר העמודות.

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

6. עיצוב

שתי המלצות לעיצוב:

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

יש לעיין בדיסקליימר

אתם מוזמנים להוריד את קובץ ההדגמה עם מערכת פעילה ומתפקדת.

בהצלחה!

האם פוסט זה הינו מועיל? נשמח לקבל פידבק בתגובות למטה. תודה!