לעתים קרובות למדי, למשתמשים Excel יש משימה של השוואת שני שולחנות או רשימות כדי לזהות הבדלים בהם או חסרים פריטים. כל משתמש מתמודד עם משימה זו בדרכו שלו, אבל לעתים קרובות כמות גדולה למדי של זמן הוא בילה על פתרון השאלה שצוין, שכן לא כל הגישות לבעיה זו הם רציונליים. במקביל, ישנם מספר פעולות מוכחות אלגוריתמים שיאפשרו להשוות רשימות או מערכי טבלה בזמן קצר למדי עם מאמץ ניכר מינימלי. בואו נחשב פרטים אפשרויות אלה.
באותו אופן, אתה יכול להשוות את הנתונים בלוחות הנמצאים על גיליונות שונים. אבל במקרה זה, רצוי כי השורות בהם ממוספרים. שאר הליך ההשוואה הוא כמעט בדיוק כפי שתואר לעיל, מלבד העובדה כי בעת ביצוע הנוסחה, יש צורך לעבור בין גיליונות. במקרה שלנו, הביטוי יהיה את הטופס הבא:
= B2 = list2! B2
כלומר, כפי שאנו רואים, מול הקואורדינטות הנתונים, הנמצאים על סדינים אחרים, למעט הוצגו תוצאת ההשוואה, מספר גיליון וסימן קריאה מצוין.
שיטה 2: בחירת קבוצות של תאים
השוואה יכולה להתבצע באמצעות הכלי להפרדת קבוצות של תאים. עם זה, אתה יכול גם להשוות רק רשימות מסונכרנות וזמינו. בנוסף, במקרה זה, רשימות חייב להיות ממוקם ליד זה על גיליון אחד.
- בחר במערכים לעומת השוואה. עבור אל הכרטיסייה "דף הבית". לאחר מכן, לחץ על "חיפוש ובחר", הנמצא על הקלטת בסרגל הכלים הערוך. יש רשימה שבה אתה צריך לבחור את המיקום "בחירה של קבוצת תאים ...".
בנוסף, ניתן להגיע לבחירה של קבוצת התאים בדרך אחרת. אפשרות זו תהיה שימושית במיוחד למשתמשים שיש להם את הגירסה של התוכנית מוקדם יותר מ- Excel 2007, שכן השיטה באמצעות הלחצן 'חיפוש ובחר' אינה תומכת ביישומים אלה. בחר מערכים המעוניינים להשוות ולחץ על מקש F5.
- חלון מעבר קטן מופעל. לחץ על הלחצן "הדגש ..." בפינה השמאלית התחתונה.
- לאחר מכן, לא משנה מה שתי האפשרויות הנ"ל בחרת, חלון הבחירה של קבוצות התא מופעל. התקן את המתג למיקום "הדגש על הקו". לחץ על הלחצן "אישור".
- כפי שאנו יכולים לראות, לאחר מכן, הערכים הלא עקביים של השורות יסומנו עם גוון שונה. בנוסף, כיצד ניתן לשפוט מהתוכן של קו הנוסחה, התוכנית תעשה את אחד הפעיל של התאים בקווים שאינם מצוינים.
שיטה 3: עיצוב מותנה
אתה יכול לעשות השוואה על ידי החלת שיטת עיצוב מותנה. כמו בשיטה הקודמת, לעומת אזורים צריך להיות על גיליון עבודה יחיד Excel ולהיות מסונכרן אחד עם השני.
- קודם כל, אנו בוחרים איזה אזור טבלאי ייחשבו הראשי, ובמה לחפש את ההבדל. אחרון בואו נעשה בטבלה השנייה. לכן, אנו מקצים רשימה של עובדים. מעבר לכרטיסייה "Home", לחץ על כפתור "עיצוב מותנה", שיש לו מיקום על הקלטת בלוק "סגנונות". מהרשימה הנפתחת, עבור פריט "ניהול הכללים".
- חלון השגרה של מנהל הכללים מופעל. אנו לוחצים על כך ללחצן "צור כלל".
- בחלון הפעלת, אנו בוחרים את מיקום "השתמש נוסחה". בשדה "תא פורמט", כתוב נוסחה המכילה את הכתובות של התאים הראשונים של טווחי השוויה, מופרדים על ידי "לא שווה" סימן (). ממש לפני הביטוי הזה הפעם יעמוד את השלט "=". בנוסף, לכל הקואורדינטות העמודה בנוסחה זו, עליך להחיל כתובת מוחלטת. לשם כך, אנו מקצים את הנוסחה עם הסמן ולחץ על מקש F4 שלוש פעמים. כפי שניתן לראות, סימן הדולר הופיע ליד כל כתובות העמודה, כלומר, הפיכת הקישורים למוחלטת. עבור המקרה המסוים שלנו, הנוסחה ייקח את הטופס הבא:
= $ A2 $ D2
אנו מקליטים את הביטוי הזה בשדה לעיל. לאחר מכן, אנחנו לוחצים על "פורמט ...".
- חלון "תאים" מופעל. אנחנו הולכים לשונית "למלא". כאן ברשימת הצבעים לעצור את הבחירה על הצבע, אשר אנו רוצים לצייר אלמנטים אלה שבו הנתונים לא תואם. לחץ על הלחצן "אישור".
- חוזרים לחלון 'חוק העיצוב', לחץ על הלחצן "אישור".
- לאחר העברה אוטומטית לחלון "כללים מנהל", לחץ על כפתור "אישור" ובתוכו.
- עכשיו בטבלה השנייה, אלמנטים בעלי נתונים שאינם עולים בקנה אחד עם הערכים המתאימים של אזור הטבלה הראשון יסומנו בצבע שנבחר.
יש דרך נוספת ליישם עיצוב מותנה כדי לבצע את המשימה. כמו האפשרויות הקודמות, היא מחייבת את המיקום של השנים שהן בהשוואה לגיליון אחד, אך בניגוד לשיטות המתוארות בעבר, תנאי הסנכרון או נתוני מיון לא יהיו חובה, אשר מבדיל אפשרות זו מתואר לעיל.
- אנו מייצרים את הבחירה של אזורים שאתה צריך להשוות.
- אנו מבצעים את המעבר לכרטיסייה בשם "Home". אנו לוחצים על כפתור "עיצוב מותנה". ברשימה המופעלת, בחר את מיקום "הכללים להקצאת התאים". בתפריט הבא, בצע את מיקום "ערכים חוזרים".
- הערכים הכפולים מופעלים. אם אתה נעשה כראוי, ולאחר מכן בחלון זה זה נשאר רק כדי ללחוץ על הלחצן "אישור". אמנם, אם תרצה, בשדה המתאים של חלון זה, תוכל לבחור בצבע אחר של הבחירה.
- לאחר שאנו מייצרים את הפעולה שצוינה, כל האלמנטים החוזרים יסומנו בצבע שנבחר. אלמנטים אלה שאינם חופפים יישארו צבועים בצבע המקורי שלהם (כברירת מחדל לבן). לכן, אתה יכול מיד לראות חזותית כדי לראות מה ההבדל בין מערכים.
אם אתה רוצה, אתה יכול, להיפך, לצייר את האלמנטים לא עקביים, ואת האינדיקטורים התואמים, לעזוב עם מילוי של הצבע הקודם. במקביל, האלגוריתם של הפעולה הוא כמעט אותו דבר, אבל בהגדרות של הערכים הכפולים בתחום הראשון, במקום פרמטר "חוזר", בחר פרמטר "ייחודי". לאחר מכן לחץ על הלחצן "אישור".
לכן, דווקא האינדיקטורים שאינם חופפים.
שיעור: עיצוב מותנה ב- Excel
שיטה 4: נוסחה מקיפה
כמו כן, להשוות נתונים בעזרת נוסחה מורכבת, הבסיס של הפונקציה של מטר. באמצעות כלי זה, תוכל לחשב את האופן שבו כל אלמנט מהעמודה שנבחרה של הטבלה השנייה חוזר על עצמו בחודש הראשון.
מועצות המפעיל מתייחסת לקבוצה הסטטיסטית של הפונקציות. המשימה שלה היא לספור את מספר התאים, את הערכים שבהם לספק את המצב שצוין. התחביר של מפעיל זה יש סוג זה:
= לוחות זמנים (טווח, קריטריון)
הטיעון "טווח" הוא הכתובת של המערך, אשר מחשבת את הערכים המצוינים.
הוויכוח "קריטריונים" מציין את מצב צירוף המקרים. במקרה שלנו, זה יהיה הקואורדינטות של תאים ספציפיים של אזור טבלאי הראשון.
- אנו מקצים את האלמנט הראשון בעמודה הנוספת שבה יחושב מספר ההתאמות. לאחר מכן, לחץ על הסמל "הוסף פונקציה".
- אשף הפונקציות מתחיל. עבור לקטגוריה "סטטיסטית". מצא ברשימה את השם "countecles". לאחר הבחירה שלו, לחץ על כפתור "אישור".
- חלון טיעוני המפעיל התחיל לרוץ. כפי שאנו רואים, שמות השדות בחלון זה תואמים את שמות הטיעונים.
התקן את הסמן בשדה "טווח". לאחר מכן, על ידי החזקת לחצן העכבר השמאלי, להקצות את כל הערכים של הטור עם שמות השולחן השני. כפי שאתה יכול לראות, הקואורדינטות מיד ליפול לתוך השדה שצוין. אבל למטרות שלנו, כתובת זו צריכה להיעשות על ידי מוחלטת. לשם כך, הדגש את הקואורדינטות בשדה ולחץ על מקש F4.
כפי שניתן לראות, הפניה לקחה את הצורה המוחלטת, המאופיינת בנוכחות שלטי דולר.
ואז ללכת לשדה "קריטריון" על ידי התקנת הסמן. לחץ על האלמנט הראשון עם השמות האחרונים בטווח הטבלאי הראשון. במקרה זה, להשאיר את הקישור קרוב משפחה. לאחר שהוא מוצג בשדה, תוכל ללחוץ על הלחצן "אישור".
- התוצאה היא פלט למרכיב הסדין. זה שווה למספר "1". משמעות הדבר היא כי ברשימת השמות של השולחן השני, השם "Grinev V. P", שהוא הראשון ברשימה של מערך הטבלה הראשון, מתרחשת פעם אחת.
- עכשיו אנחנו צריכים ליצור ביטוי דומה עבור כל האלמנטים האחרים של השולחן הראשון. לשם כך, אני אבצע העתקה, באמצעות סמן מילוי, כפי שכבר עשינו בעבר. שמנו את הסמן לחלק הימני התחתון של אלמנט העלים, המכיל את הפונקציה של מטר, ולאחר שהמרת אותו לסמן מילוי, מהדק את לחצן העכבר השמאלי ומשוך את הסמן למטה.
- כפי שאנו יכולים לראות, התוכנית עשה חישוב של צירופי מקרים, השוואה כל תא של הטבלה הראשונה עם נתונים, אשר ממוקמים בטווח השולחן השני. בארבעה מקרים, התוצאה היתה "1", ובשני מקרים - "0". כלומר, התוכנית לא הצליחה למצוא את שני הערכים בטבלה השנייה הזמינים במערך הטבלה הראשון.
כמובן, ביטוי זה על מנת להשוות אינדיקטורים בטבלה, ניתן ליישם את הטופס הקיים, אך ניתן לשפר אותו.
אנו נעשה כי הערכים הקיימים בטבלה השנייה, אך אינם זמינים בראשונה, הוצגו כרשימה נפרדת.
- קודם כל, אנו ממחזרים את הנוסחה שלנו, אבל אנחנו נעשה את זה אחד הטיעונים של המפעיל אם. כדי לעשות זאת, אנו מדגישים את התא הראשון שבו המפעיל הוא המפעיל. במחרוזת הנוסחה מולו, אנו מוסיפים את הביטוי "אם" ללא ציטוטים ופתוח את התושבת. יתר על כן, כך היה לנו קל יותר לעבוד, להקצות את הנוסחאות בשורת הנוסחה ולחץ על סמל "הכנס פונקציה".
- חלון טיעוני הפונקציה נפתח אם. כפי שאתה יכול לראות, החלון הראשון של החלון כבר היה מלא הערכה של המועצה של המפעיל. אבל אנחנו צריכים להוסיף משהו אחר בתחום זה. אנו מגדירים שם את הסמן ואת הביטוי הקיים כבר להוסיף "= 0" ללא ציטוטים.
אחרי זה, ללכת על "המשמעות אם האמת" שדה. כאן נשתמש בפונקציה מקוננת אחרת - קו. הזן את המילה "מחרוזת" ללא ציטוטים, ולאחר מכן פתח את הסוגריים ומציינים את הקואורדינטות של התא הראשון עם שם המשפחה בטבלה השנייה, ולאחר מכן אנו סוגרים בסוגריים. באופן ספציפי, במקרה שלנו "משמעות אם נכון" שדה, הבעה הבאה קרה:
שורה (D2)
עכשיו מחרוזת המפעיל תדווח על פונקציות אם מספר הקו שבו נמצא שם המשפחה הספציפי, ובמקרה כאשר המצב שצוין בתחום הראשון יבוצע, הפונקציה אם מספר זה יוצג. לחץ על הלחצן "אישור".
- כפי שניתן לראות, התוצאה הראשונה מוצגת כ"שקרים ". משמעות הדבר היא כי הערך אינו מספק את תנאי המפעיל אם. כלומר, שם המשפחה הראשון נמצא בשני רשימות.
- באמצעות סמן מילוי, אשר כבר מוכר להעתיק את הביטוי של המפעיל אם על העמודה כולה. כפי שאנו רואים, בשני עמדות הנמצאות בטבלה השנייה, אך לא בתחילת, הנוסחה נושא מספרים.
- אנו נסוגים מאזור הטבלה מימין ומלא את העמודה המספרית לפי הסדר, החל מ -1 מספר המספרים חייבים להתאים למספר השורות בטבלה השנייה. כדי להאיץ את הליך המספור, תוכל גם להשתמש במרקר מילוי.
- לאחר מכן, אנו מדגישים את התא הראשון בצד ימין של הדובר עם המספרים ולחץ על "הכנס פונקציה" סמל.
- אשף נפתח. עבור לקטגוריה "סטטיסטית" ולייצר את השם של "הקטן" שם. לחץ על הלחצן "אישור".
- הפונקציה היא הקטנה ביותר, טיעונים של טיעונים אשר נחשף, נועד למשוך את הערך הקטן ביותר שצוין.
בשדה "מערך", עליך לציין את הקואורדינטות של טווח העמודה האופציונלית "מספר ההתאמות", שהשתנו בעבר באמצעות הפונקציה אם. אנו עושים את כל הקישורים המוחלטת.
בשדה "K", הוא מצוין אשר בחשבון יש להציג את הערך הקטן ביותר. כאן אנו מציינים את הקואורדינטות של התא הראשון של העמודה עם המספור, אשר לאחרונה נוספו. כתובת חופשת יחסית. לחץ על הלחצן "אישור".
- המפעיל מציג את התוצאה - מספר 3. דווקא הקטן ביותר ממספור הקווים הלא עקביים של מערכי הטבלה. באמצעות סמן מילוי, להעתיק את הנוסחה לאף עצמו.
- עכשיו, בידיעה את מספר שורות האלמנטים הבלתי מובנים, אנו יכולים להכניס לתא ואת הערכים שלהם באמצעות פונקציית המדד. בחר את האלמנט הראשון של הגיליון המכיל את הנוסחה היא הקטנה ביותר. לאחר מכן, עבור אל הנוסחאות הקו ולפני שמו של "הקטן" להוסיף את השם "אינדקס" ללא ציטוטים, מיד לפתוח את התושבת ולשים נקודה עם פסיק (;). לאחר מכן אנו מקצים את שם הנוסחה "אינדקס" בקו ולחץ על סמל "פונקציה הדבק".
- לאחר מכן נפתח חלון קטן, שבו יש צורך לקבוע אם תצוגת התייחסות צריכה להיות פונקציה של אינדקס או שתוכננה לעבוד עם מערכים. אנחנו צריכים אפשרות שנייה. הוא מותקן כברירת מחדל, כך שבחלון זה פשוט לחץ על הלחצן "אישור".
- חלון הטענה מפעיל את פונקציית המדד. מפעיל זה נועד פלט ערך הממוקם במערך מסוים בשורה שצוינה.
כפי שאתה יכול לראות, "מספר שורה" שדה כבר מלא את הערכים המשמעות של הפונקציה הקטנה ביותר. מערך קיים שם, ההפרש בין מספור גיליון Excel לבין המספור הפנימי של אזור טבלאי. כפי שאנו רואים, יש לנו רק כובע על ערכי השולחן. משמעות הדבר היא כי ההבדל הוא שורה אחת. לכן, הוסף "-1" ללא ציטוטים בשדה "מספר שורה".
בשדה "מערך", ציין את הכתובת של הערך של ערכי הטבלה השנייה. במקביל, כל הקואורדינטות לעשות מוחלט, כלומר, שמנו את סימן הדולר לפני השיטה המתוארת על ידינו.
לחץ על הלחצן "אישור".
- לאחר נסיגה, התוצאה על המסך הוא מתיחה את הפונקציה באמצעות מילוי מילוי לסוף הטור למטה. כפי שאנו רואים, שני שמות משפחה שנמצאים בטבלה השנייה, אך אינם זמינים בראשונה, מוסרים בטווח נפרד.
שיטה 5: השוואה של מערכים בספרים שונים
בעת השוואת הטווחים בספרים שונים, באפשרותך להשתמש בשיטות הנ"ל, למעט אפשרויות אלה שבהן נדרשת המיקום של אזורי טבלאי על גיליון אחד. התנאי העיקרי לנוהל ההשוואה במקרה זה הוא פתיחת החלונות של שני הקבצים בו זמנית. עבור גרסאות של Excel 2013 ומאוחר יותר, כמו גם עבור גרסאות ל- Excel 2007, אין בעיות עם יישום מצב זה. אבל ב- Excel 2007 ו- Excel 2010 על מנת לפתוח את החלונות בו זמנית, נדרשים מניפולציות נוספים. איך לעשות את זה מספרת בשיעור נפרד.
שיעור: כיצד לפתוח את Excel בחלונות שונים
כפי שאתה יכול לראות, ישנם מספר אפשרויות להשוות טבלאות אחד עם השני. איזה סוג של אפשרות לשימוש תלוי במקום שבו נתוני הטבלה ביחס זה לזה (על גיליון אחד, בספרים שונים, על גיליונות שונים), כמו גם מהמשתמש רוצה להשוות את ההשוואה הזאת על המסך.