כיצד לסנן נתונים ב - Excel - -

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

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

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

יצירת מסננים פשוטים ב - Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

יצירת מסננים מתקדמים ב- Excel

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

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

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

אז מה בדיוק אנחנו יכולים להקליד לתוך התאים האלה? בסדר, אז בואו נתחיל עם הדוגמה שלנו. אנחנו רק רוצים לראות נתונים מניו אורלינס או Clarksville, אז בואו להקליד אותם לתוך התאים E2 ו- E3.

כאשר אתה מקליד ערכים בשורות שונות, המשמעות היא OR. עכשיו אנחנו רוצים משפחות ניו אורלינס עם יותר משני חברים ובני משפחות Clarksville עם יותר מ 3 חברים. לשם כך, הקלד > 2 ב C2 ו > 3 ב C3.

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

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

בחר הכל מ- A1 ל- E3 ולאחר מכן לחץ שוב על אותו לחצן כדי לחזור אל תיבת הדו-שיח Advanced Filter. לחץ על אישור והנתונים שלך צריכים להיות מסוננים כעת!

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

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

סיכום נתונים מסוננים

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

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

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

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

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

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

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

כאשר אתה לוחץ על Enter, אתה אמור לראות את הערך של 78 הוא זהה בעבר. עם זאת, אם תחיל כעת את המסנן שוב, נראה 11!

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

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

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

ניתן גם לקרוא את המאמר שלי על פונקציות סיכום כדי ללמוד כיצד להשתמש בפונקציה AGGREGATE ופונקציות אחרות כמו MODE, MEDIAN, AVERAGE וכו 'בפירוט רב יותר.

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