השוואה בין שני טבלאות

יש לנו שתי טבלאות (לדוגמה, הגרסה הישנה והחדשה של המחירון), שעלינו להשוות ולמצוא במהירות את ההבדלים:

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

לכל משימה באקסל, כמעט תמיד יש יותר מפתרון אחד (בדרך כלל 4-5). לבעיה שלנו, ניתן להשתמש בגישות רבות ושונות:

• פונקציה VPR (VLOOKUP) - חפש שמות מוצרים מהמחירון החדש במחיר הישן והצג את המחיר הישן ליד החדש, ולאחר מכן תפס את ההבדלים
• למזג שתי רשימות לאחת ולאחר מכן לבנות טבלת ציר המבוססת עליה, שבה ההבדלים יהיו גלויים בבירור
• השתמש בתוסף Power Query עבור Excel

בואו ניקח את כולם לפי הסדר.

שיטה 1. השוואת טבלאות עם הפונקציה VLOOKUP

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

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

המוצרים האלה, שנגדם התבררה שגיאת #N/A, אינם ברשימה הישנה, ​​כלומר נוספו. גם שינויים במחירים נראים בבירור.

Pros שיטה זו: פשוטה וברורה, "קלאסיקה של הז'אנר", כמו שאומרים. עובד בכל גרסה של אקסל.

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

שיטה 2: השוואת טבלאות באמצעות ציר

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

כעת, בהתבסס על הטבלה שנוצרה, ניצור סיכום דרך הוספה - PivotTable (הוסף - טבלת ציר). בוא נזרוק שדה המוצר לאזור של קווים, שדה מחיר לאזור עמודה ולשדה ЦENA לתוך הטווח:

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

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

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

Pros: גישה זו מהירה בסדר גודל עם טבלאות גדולות מאשר VLOOKUP. 

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

שיטה 3: השוואת טבלאות עם Power Query

Power Query הוא תוסף חינמי עבור Microsoft Excel המאפשר לך לטעון נתונים לתוך Excel כמעט מכל מקור ולאחר מכן להפוך את הנתונים הללו בכל דרך רצויה. ב-Excel 2016, תוספת זו כבר מובנית כברירת מחדל בכרטיסייה נתונים (נתונים), ועבור Excel 2010-2013 אתה צריך להוריד אותו בנפרד מאתר מיקרוסופט ולהתקין אותו - קבל כרטיסייה חדשה שאילתת כוח.

לפני טעינת המחירונים שלנו ל-Power Query, תחילה יש להמיר אותם לטבלאות חכמות. כדי לעשות זאת, בחר את הטווח עם הנתונים ולחץ על השילוב במקלדת Ctrl+T או בחר את הכרטיסייה ברצועת הכלים בית - עיצוב כטבלה (בית - עיצוב כטבלה). ניתן לתקן את שמות הטבלאות שנוצרו בלשונית בנאי (אני אשאיר את התקן לוח 1 и לוח 2, שמתקבלים כברירת מחדל).

טען את המחיר הישן ב- Power Query באמצעות הכפתור מתוך טבלה/טווח (מטבלה/טווח) מהכרטיסייה נתונים (תַאֲרִיך) או מהכרטיסייה שאילתת כוח (תלוי בגרסת האקסל). לאחר הטעינה, נחזור לאקסל מ-Power Query עם הפקודה סגור וטען - סגור וטען פנימה... (סגור וטען - סגור וטען ל...):

... ובחלון שיופיע אז בחר פשוט צור קשר (חיבור בלבד).

חזור על אותו הדבר עם המחירון החדש. 

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

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

לאחר לחיצה על OK אמורה להופיע טבלה של שלוש עמודות, כאשר בעמודה השלישית אתה צריך להרחיב את התוכן של טבלאות מקוננות באמצעות החץ הכפול בכותרת:

כתוצאה מכך, אנו מקבלים מיזוג נתונים משתי הטבלאות:

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

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

נשאר ללחוץ עליו OK והעלה את הדוח שנוצר לאקסל באמצעות אותו כפתור לסגור ולהוריד (סגור וטען) כרטיסייה עמוד הבית (בית):

יופי.

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

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

חסרונות: דורש התקנה של תוסף Power Query (ב-Excel 2010-2013) או Excel 2016. אין לשנות את שמות העמודות בנתוני המקור, אחרת נקבל את השגיאה "עמודה כזו וכזו לא נמצאה!" כאשר מנסים לעדכן את השאילתה.

• כיצד לאסוף נתונים מכל קבצי Excel בתיקייה נתונה באמצעות Power Query
• כיצד למצוא התאמות בין שתי רשימות באקסל
• מיזוג שתי רשימות ללא כפילויות