דף הבית > אקסל מתקדם, טיפים וטריקים > חיפוש וסינון למקצוענים – תחליף אפקטיבי לסינון האוטומטי באקסל

חיפוש וסינון למקצוענים – תחליף אפקטיבי לסינון האוטומטי באקסל

Featured_Pic

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

 

 

 

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

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

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

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

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

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

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

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

 

1. נתונים

1

קליק להגדלה

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

 

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

2

קליק להגדלה

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

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

 

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

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

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

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

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

3

קליק להגדלה

 

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

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

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

4

קליק להגדלה

 

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

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

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

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

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

5

קליק להגדלה

 

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

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

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

 

6. עיצוב

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

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

 

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

 

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

 

בהצלחה!

 

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

  1. אהרון
    7 ספטמבר, 2016 מתוך 14:32 | #1

    היי- רציתי רק לשאול -איך ניתן להרחיב את אינדקס הבחירה ל-1000 תאים במקום 100?

  2. אהרון
    7 ספטמבר, 2016 מתוך 14:33 | #2

    תיקון :היי- רציתי רק לשאול -איך ניתן להרחיב את אינדקס הבחירה ל-1000 שורות במקום 100?

    • admin
      20 ספטמבר, 2016 מתוך 08:20 | #3

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

      בברכה,
      -אנליסטים אנונימיים

  3. איתי
    22 ספטמבר, 2016 מתוך 16:33 | #4

    היי – יש לי קובץ אקסל המכיל מספר טבלאות. כיצד ניתן לסנן ערך מסויים לכל הטבלאות כאשר תוצאות הסינון יוצגו פר טבלה ולא יאחד את הטבלאות לטבלה אחת?

    • admin
      12 מרץ, 2017 מתוך 09:36 | #5

      היי – יש לי קובץ אקסל המכיל מספר טבלאות. כיצד ניתן לסנן ערך מסויים לכל הטבלאות כאשר תוצאות הסינון יוצגו פר טבלה ולא יאחד את הטבלאות לטבלה אחת?

      הי.

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

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

  4. עינב
    1 נובמבר, 2016 מתוך 15:13 | #6

    הי,
    שאלה…
    איזו נוסחה קיימת כדי לזהות אם תא מכיל נוסחה?
    בנוסף, האם ניתן להשתמש בעיצוב מותנה כדי לדעת אם תא מכיל נוסחה?

    לא מעוניינת להשתמש בהצג נוסחאות שנמצא בסרגל הכלים.

    תודה רבה!!

    • admin
      12 מרץ, 2017 מתוך 09:28 | #7

      הי,
      שאלה…
      איזו נוסחה קיימת כדי לזהות אם תא מכיל נוסחה?
      בנוסף, האם ניתן להשתמש בעיצוב מותנה כדי לדעת אם תא מכיל נוסחה?

      לא מעוניינת להשתמש בהצג נוסחאות שנמצא בסרגל הכלים.

      תודה רבה!!

      אפשר להשתמש ב-showformula, כמו כאן: show_formula.JPG
      בשביל עיצוב מותנה, אפשר להשתמש ב-if בשילוב עם iserror: אם משתמשים ב-showformula על תא שאינו מכיל נוסחה, תוחזר הודעת שגיאה.

      בברכה,
      -יבגני

  5. אבי
    28 יוני, 2017 מתוך 13:16 | #8

    ב"ה
    שאלה:
    כיצד אני מחפש בטבלת אקסל את התו "?" (סימן שאלה) כתו ולא כתו גלובאלי ?

    • admin
      1 יולי, 2017 מתוך 18:13 | #9

      הי.

      תבחר בחיפוש מדויק:
      חיפוש מדויק של תו

  6. תמיר
    26 יולי, 2017 מתוך 22:53 | #10

    לגבי המאמר "חיפוש וסינון למקצוענים – תחליף אפקטיבי לסינון האוטומטי באקסל". בסינון הגדרת ערך בודד למשל "כהן". אם אני מעוניין לסנן את הטבלה לפי טווח (למשל השאר את השורות בהן מספר תעודת הזהות גבוה מ-500000000 ונמוך מ700000000) מה אפשר לעשות?
    תודה.

  7. אשל
    7 אוגוסט, 2019 מתוך 03:33 | #11

    @admin
    מנסה להרחיב את הטבלה אבל קצת מסתבך. אפשר אולי הסבר עממי יותר איזו נוסחא צריך לשנות?

  1. 18 ינואר, 2015 מתוך 11:52 | #1