השתמש ב- INDEX וב- MATCH עבור שאילתות מסד נתונים פשוטות ב- Excel

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

VLOOKUP (אנכי) ו HLOOKUP (אופקי) היו הפונקציות היחידות הזמינות לשאילתה בסיס נתונים עבור מידע ספציפי. לדוגמה, תוכל לבצע שאילתה כדי למצוא ולחלץ את כל הרשומות שהכילו מכירות של יותר מ -1,000 דולר אך פחות מ -5,000 דולר - אך רק על קבצים שטוחים (רק מטריצת מסד נתונים אחת).

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

[קריאה נוספת: המחשב החדש שלך צריך אלה 15 תוכניות חינם, מעולה]

אינדקס, התאמה והתאמה לאינדקס

ב- Excel, הפונקציה INDEX מחזירה פריט ממיקום מסוים (ברשימה, בטבלה, במסד הנתונים).

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

התחביר עבור הפונקציה INDEX הוא: INDEX (array, row_num, [column_num]). המערך הוא טווח התאים שאתה עובד איתם. Row_num הוא, כמובן, מספר השורה בטווח שמכיל את הנתונים שאתה מחפש. Column_num הוא מספר העמודה בטווח המכיל את הנתונים שאתה מחפש. הנוסחה INDEX אינה מזהה אותיות עמודות, לכן עליך להשתמש במספרים (ספירה משמאל).

התחביר עבור הפונקציה MATCH הוא: MATCH (lookup_value, lookup_array, [match_type]). ה- lookup_value הוא המספר או הטקסט שאתה מחפש, שיכול להיות ערך, ערך לוגי או הפניה לתא. Lookup_array הוא טווח של תאים אתה עובד עם. Match_type קובע את הפונקציה MATCH - כלומר, התאמה מדויקת או התאמה הקרובה. INDEX פונקציה

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

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

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

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

3. לאחר מכן, הזן פסיק כדי להפריד את הארגומנטים (כלומר, את נוסחאות נפרד) ולאחר מכן להזין את מספר השורה ואת פסיק, ואחריו את מספר העמודה (כן , זה חייב להיות מספר ולא את המכתב טור רגיל) ואת סוגר ימין (או פשוט ללחוץ על Enter ולתת Excel להוסיף את סוגר הסוף בשבילך) הנוסחה המלאה נראית כך: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

הערה: מספור השורות מתחיל במספר הראשון בטווח, ולא במספר הראשון בגיליון האלקטרוני.לדוגמה, ערב n למרות הספינה פיראט Cavalleria הוא על שורת Excel 16, זה למעשה שורה 15 בנוסחה שלנו, כי טווח שלנו מתחיל ב A2 ועובר I16. אם A2 הוא שורה 1, אז A16 הוא שורה 15)4. שים לב שסוג הספינה שנורינגטון חיפש הוא ספינת מלחמה.

JD Sartain

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

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

2. בכרטיסייה נוסחאות, בחר

הגדר שם

מהקבוצה 'שמות מוגדרים'

3. בתיבת הדו-שיח קופץ, הזן שם עבור הטווח בתיבה שדה שם.

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

7. אם אתה רוצה לוודא כי הטווח שלך, אכן, נשמר ב- Excel, נסה את הבדיקה הקטנה: הקש Ctrl + G (הפקודה GoTo). בחר

ספינות

בתיבת הדו-שיח GoTo, ולאחר מכן לחץ על אישור , ו- Excel מדגיש מחדש את הטווח A2: I16.

כיצד להגדיר ולשמור טווח C. INDEX עם SUM & ממוצע נוסחאות נורינגטון מעריך את יכולות הקרב של הצי. תחילה הוא רוצה לדעת כמה תותחים יש לפיראטים, מספר התותחים הממוצע לכל ספינה, ואת המספר הכולל של אנשי הצוות שמאיישים את כל ספינות הפיראטים האלה. הוא נכנס לנוסחאות הבאות: 1. = SUM (INDEX (ספינות, 8)) שווה 334, המספר הכולל של תותחים, ו 2. = AVERAGE (INDEX (ספינות, 8)) שווה 22.27, או כ 22.27 תותחים לספינה. = SUM (INDEX (ספינות, 7)) שווה 2350, המספר הכולל של כל הצוות על כל הספינות.

מדוע יש פסיק, רווח, פסיק בין ספינות לבין מספר 8, ומה המשמעות של מספרים אלה? אוניות הן טווח (ואחריו פסיק), ארגומנט שורה ריק (או רווח) כי Norrington רוצה את כל השורות, ואת 8 מייצג את העמודה 8 מעל (שהוא עמודה H, תותחים). , למה לא רק להזין את SUM ו / או נוסחאות AVERAGE בתחתית עמודות אלה? בגיליון האלקטרוני הזעיר הזה, כן, זה יהיה קל באותה מידה. אבל אם הגיליון האלקטרוני מכיל 5000 שורות ו -300 עמודות, מומלץ להשתמש ב- INDEX

JD Sartain

03 נוסחאות INDEX באמצעות SUM ו- AVERAGE.

לאחר שהטווח נקרא בשם, Norrington יכול לפתוח גיליון אלקטרוני ריק אותו ספר עבודה ולכתוב את השאילתות שלו (נוסחאות) בעמודה B (המציגות את התוצאות במקום את הנוסחאות) בתיאור המגדיר שאילתות אלה בעמודה A. (הערה: עמודה C מציגה את הנוסחאות הקיימות בעמודה B).

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

JD Sartain

04 מידע על ספינת פיראטים של קומודור ג'יימס נורינגטון.

D. INDEX MATCH עם מקס

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

1. = MAX (INDEX (ספינות, 7)) שווה ל 300, המספר הגבוה ביותר של פיראטים על אחת הספינות

2. = INDEX ($ A $ 2: $ 16 $, MATCH (מקס (ספינות), $ G $ 2: G $ 16, 0)) שווה את רויאל ג'יימס, הספינה עם הפיראטים ביותר על הספינה

3. = $ F $ 2: $ F $ 16, MATCH (מקס (ספינות), $ G $ 2: G $ 16, 0)) שווה לסטר בנט, קפטן של ג'יימס רויאל עם צוות פיראט של 300

JD Sartain

השתמש ב- INDEX-MATCH וב- MAX כדי לאחזר מידע ספציפי ממסד הנתונים שלך