כיצד לספור ערכים ייחודיים ב- Excel

כיצד לספור ערכים ייחודיים ב- Excel

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





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



הסר נתונים כפולים מעמודה

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





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

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



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

קשור: כיצד לסנן ב- Excel להצגת הנתונים שאתה רוצה



ספירת ערכים ייחודיים בעזרת נוסחת Excel

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

אם אתה רק מחפש את התשובה, השתמש בנוסחה זו, והחליף כל מופע של A2: A13 בתאים שבהם ברצונך להשתמש:



{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

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

הסברת פונקציית מערך

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

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

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

היכרות עם פונקציית FREQUENCY

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

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

שימוש בפונקציית MATCH

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

  • איזה ערך אתה מחפש?
  • איזה מערך נתונים אתה בודק?
  • האם אתה מחפש ערכים גבוהים, נמוכים או שווים לערך היעד?

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

הפעלת חלונות XP על חלונות 10

בדוגמה למעלה, אנו מחפשים ברשימה שלנו (A2: A13) את Tiah Gallagher (A2) ואנחנו רוצים התאמה מדויקת. ה 0 בשדה האחרון מציין שהוא חייב להיות התאמה מדויקת. התוצאה שלנו אומרת לנו היכן ברשימה מופיע השם הראשון. במקרה זה, זה היה השם הפרטי, כך שהתוצאה היא 1.

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

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

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

אתה יכול לבדוק זאת בעצמך. אם תשנה את הטווח הראשון ל- A3: A13, התוצאה תשתנה ל 2. הסיבה לכך היא ששמה של עילייה הוא השני ברשימה וערך זה נשמר ראשון במערך כעת. אם תשנה את הטווח הראשון ל- A7: A13, תקבל שוב 1 כיוון שמו של Tiah מופיע לראשונה במיקום הראשון של מערך הנתונים שאנו בודקים.

קָשׁוּר: נוסחאות Excel שיעזרו לך לפתור בעיות בחיים האמיתיים

שימוש בפונקציית FREQUENCY

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

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

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

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

שימוש בפונקציית IF

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

נוכל ליצור פונקציה IF שמשנה את כל הערכים מעל האפס ל -1 ואז כל הערכים יהיו שווים ל- 1.

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

שימוש בפונקציית SUM

אנחנו בקטע האחרון! השלב האחרון הוא SUM המערך.

עטפו את הפונקציה הקודמת בפונקציית SUM. גָמוּר! אז הנוסחה הסופית שלנו היא:

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

ספירת רשומות ייחודיות ב- Excel

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

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

לַחֲלוֹק לַחֲלוֹק צִיוּץ אימייל כיצד להעתיק נוסחאות ב- Microsoft Excel

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

קרא הבא נושאים קשורים
  • פִּריוֹן
  • גיליון אלקטרוני
  • Microsoft Excel
  • ניתוח נתונים
על הסופר ג'ניפר סיטון(פורסמו 21 מאמרים)

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

עוד מאת ג'ניפר סיטון

הירשם לניוזלטר שלנו

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

לחצו כאן להרשמה