כיצד למצוא כפילויות בין שתי עמודות ב-Google Sheets

אתה יכול למצוא ולהדגיש את הערכים הכפולים בין שתי עמודות באמצעות התכונה עיצוב מותנה ב-Google Sheets.

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

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

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

מצא ערכים כפולים בין שתי עמודות באמצעות עיצוב מותנה

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

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

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

לאחר מכן, לחץ על תפריט 'פורמט' בשורת התפריטים ובחר 'עיצוב מותנה'.

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

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

כעת, עליך להזין נוסחה מותאמת אישית בתיבה 'ערך או נוסחה'.

אם בחרת עמודה שלמה (B:B), הזן את נוסחת ה-COUNTIF הבאה בתיבה 'ערך או נוסחה' תחת כללי עיצוב:

=countif($B:$B,$A2)>0

אוֹ,

אם בחרת טווח של תאים בעמודה (נניח מאה תאים, A2:A30), השתמש בנוסחה זו:

=COUNTIF($B$2:$B$30, $A2)>0

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

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

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

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

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

לאחר שבחרת את העיצוב, לחץ על 'סיום' כדי להדגיש את התאים.

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

זה לא מדגיש את הכפילויות, אלא הוא מדגיש את הפריטים שיש להם כפילויות בעמודה B. זה אומר שלכל פריט מודגש צהוב יש כפילויות בעמודה B.

כעת, עלינו להחיל עיצוב מותנה על עמודה B באמצעות אותה נוסחה. כדי לעשות זאת, בחר את העמודה השנייה (B2:B30), עבור לתפריט 'פורמט' ובחר 'עיצוב מותנה'.

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

לאחר מכן, אשר את הטווח (B2:B30) בתיבה 'החל על טווח'.

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

=COUNTIF($A$2:$A$30, $B2)>0

כאן אנו משתמשים בטווח עמודה A ($A$2:$A$30) בארגומנט הראשון ו-'$B2' בארגומנט השני. נוסחה זו תבדוק את ערך התא ב'עמודה B' מול כל תא בעמודה A. אם נמצאה התאמה (כפול), אז העיצוב המותנה יגבה את הפריט הזה ב'עמודה B'

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

זה ידגיש את הפריטים בעמודה B שיש להם כפילויות בעמודה A. כעת, מצאת והדגשת פריטים כפולים בין שתי עמודות.

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

סמן כפילויות בין שתי עמודות באותה שורה

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

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

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

לאחר מכן, הזן את הנוסחה שלהלן בתיבה 'ערך או נוסחה':

=$A2=$B2

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

לאחר מכן, ציין את העיצוב מתוך אפשרויות 'סגנון עיצוב' ולחץ על 'בוצע'.

כפי שניתן לראות, רק השורות שיש להן נתונים תואמים (כפילויות) בין שתי עמודות יודגשו וכל שאר הכפילויות יתעלמו.

סמן שכפול תאים במספר עמודות

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

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

בדוגמה, אנו בוחרים ב-A2:C30.

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

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

=countif($A$2:$C$30,A2)>

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

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

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

לאחר החלת העיצוב המותנה, תוכל לערוך או למחוק את כלל העיצוב המותנה בכל עת שתרצה.

אם ברצונך לערוך את כלל העיצוב המותנה הנוכחי, בחר כל תא עם עיצוב מותנה, עבור אל 'פורמט' בתפריט ובחר 'עיצוב מותנה'.

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

אם ברצונך להוסיף עיצוב מותנה נוסף על הכלל הנוכחי, לחץ על הלחצן 'הוסף עוד כלל'.

ספור את הכפילויות בין שתי עמודות

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

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

=COUNTIF($B$2:$B$30,$A2)

הזן נוסחה זו בתא C2. נוסחה זו סופרת את מספר הפעמים שהערך בתא A2 קיים בעמודה (B2:B30) ומחזירה את הספירה בתא C2.

כאשר תקליד את הנוסחה והקש Enter, תכונת המילוי האוטומטי תופיע, לחץ על 'סימון סימון' כדי למלא את הנוסחה הזו באופן אוטומטי לשאר התאים (C3:C30).

אם תכונת המילוי האוטומטי לא מופיעה, לחץ על הריבוע הכחול בפינה השמאלית התחתונה של תא C2 וגרור אותו למטה כדי להעתיק את הנוסחה בתא C2 לתאים C3:C30.

העמודה 'השוואה 1' (C) תראה לך כעת את מספר הפעמים שכל ערך תואם בעמודה A מופיע בעמודה B. לדוגמה, הערך של A2, או "Franklyn" אינו נמצא בעמודה B, ולכן, הפונקציה COUNTIF מחזירה "0". והערך "Loreta" (A5) נמצא פעמיים בעמודה B, ומכאן שהוא מחזיר "2".

כעת, עלינו לחזור על אותם שלבים כדי למצוא את הספירות הכפולות של עמודה B. לשם כך, הזן את הנוסחה הבאה בתא D2 בעמודה D (השוואה 2):

=COUNTIF($A$2:$A$30,$B2)

בנוסחה זו, החלף את הטווח מ-'$B$2:$B$30' ל-'$A$2:$A$30' ו-'$B2' ל-'$A2'. הפונקציה סופרת את מספר הפעמים שהערך בתא B2 קיים בעמודה A (A2:A30) ומחזירה את הספירה בתא D2.

לאחר מכן, מלא את הנוסחה באופן אוטומטי לשאר התאים (D3:D30) בעמודה D. כעת, 'השוואה 2' תראה לך את מספר הפעמים שכל ערך תואם בעמודה B מופיע בעמודה A. לדוגמה , הערך של B2, או "Stark" נמצא פעמיים בעמודה A, לכן, הפונקציה COUNTIF מחזירה "2".

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

זהו זה.