10 פונקציות חיוניות של Microsoft Excel לניתוח נתונים

1. לשרשר

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

בדוגמה למעלה, רציתי שהחודש והמכירות ביחד בעמודה אחת. לשם כך, השתמשתי בנוסחה =CONCATENATE(A2, B2) בתא C2 כדי לקבל 700 $ של ינואר כתוצאה מכך.

נוּסחָה: =CONCATENATE(תאים שברצונך לשלב)

2. LEN

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

בדוגמה למעלה, רציתי לספור את הנתונים של מספר הצפיות שאני מקבל בכל חודש. לשם כך, השתמשתי בנוסחה =LEN(C2) בתא D2 כדי לקבל 5 כתוצאה מכך.

נוּסחָה: =LEN(תָא)

3. VLOOKUP

=VLOOKUP היא כנראה אחת הפונקציות המוכרות ביותר עבור כל מי שמכיר את ניתוח הנתונים. אתה יכול להשתמש בו כדי להתאים נתונים מטבלה עם ערך קלט. הפונקציה מציעה שני מצבי התאמה - מדויק ומשוער, אשר נשלט על ידי טווח הבדיקה. אם תגדיר את הטווח ל-FALSE, הוא יחפש התאמה מדויקת, אך אם תגדיר אותו ל-TRUE, הוא יחפש התאמה משוערת.

בדוגמה למעלה, רציתי לחפש את מספר הצפיות בחודש מסוים. לשם כך השתמשתי בנוסחה =VLOOKUP("Jun", A2:C13, 3) בתא G4 וקיבלתי 74992 כתוצאה מכך. כאן, "Jun" הוא ערך החיפוש, A2:C13 הוא מערך הטבלה שבו אני מחפש את "Jun" ו-3 הוא מספר העמודה שבה הנוסחה תמצא את התצוגות המתאימות עבור יוני.

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

נוּסחָה: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

4. INDEX/MATCH

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

בדוגמה למעלה, רציתי לחפש את מספר הצפיות בינואר. לשם כך השתמשתי בנוסחה =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). כאן, A2:C13 הוא עמודת הנתונים שאני רוצה שהנוסחה תחזיר, "Jan" הוא הערך שאני רוצה להתאים, A2:A13 הוא הערך עמודה שבה הנוסחה תמצא את "יאן" וה-0 מסמל שאני רוצה שהנוסחה תמצא התאמה מדויקת ל- ערך.

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

כעת אם אינך רוצה להקפיד על שם החודש, תוכל להחליף אותו במספר הסלולרי. אז נוכל להחליף את "Jan" בנוסחה שהוזכרה למעלה ב-F3 או A2 כדי לקבל את אותה תוצאה.

נוּסחָה: =INDEX(עמודה של הנתונים שברצונך להחזיר, MATCH(נקודת נתונים משותפת שאתה מנסה להתאים, עמודה של מקור הנתונים האחר שיש לו את נקודת הנתונים המשותפת, 0))

5. MINIFS/MAXIFS

=MINIFS ו-=MAXIFS דומים מאוד לפונקציות =MIN ו-=MAX, פרט לעובדה שהם מאפשרים לך לקחת את קבוצת הערכים המינימלית/המקסימלית ולהתאים אותם גם לפי קריטריונים מסוימים. אז בעצם, הפונקציה מחפשת את ערכי המינימום/המקסימום ומתאימה אותם לקריטריוני קלט.

בדוגמה למעלה, רציתי למצוא את הציונים המינימליים על סמך מינו של התלמיד. לשם כך השתמשתי בנוסחה =MINIFS (C2:C10, B2:B10, "M") וקיבלתי את התוצאה 27. כאן C2:C10 היא העמודה שבה הנוסחה תחפש את הציונים, B2:B10 היא עמודה שבה הנוסחה תחפש את הקריטריונים (המין), ו-"M" הוא הקריטריונים.

באופן דומה, עבור ציונים מקסימליים, השתמשתי בנוסחה =MAXIFS(C2:C10, B2:B10, "M") וקיבלתי את התוצאה 100.

נוסחה עבור MINIFS: =MINIFS(min_range, criteria_range1, criteria1,...)

נוסחה עבור MAXIFS: =MAXIFS(max_range, criteria_range1, criteria1,...)

6. AVERAGEIFS

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

בדוגמה זו, רציתי למצוא את הציון הממוצע על סמך מגדר התלמידים. לשם כך השתמשתי בנוסחה, =AVERAGEIFS(C2:C10, B2:B10, "M") וקיבלתי 56.8 כתוצאה מכך. כאן, C2:C10 הוא הטווח שבו הנוסחה תחפש את הממוצע, B2:B10 הוא טווח הקריטריונים, ו-"M" הוא הקריטריונים.

נוּסחָה: =AVERAGEIFS(טווח_ממוצע, טווח_קריטריונים1, קריטריון1,...)

7. COUNTIFS

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

בדוגמה זו, רציתי למצוא את מספר הסטודנטים או הסטודנטיות שקיבלו ציוני עובר (כלומר >=40). לשם כך השתמשתי בנוסחה =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). כאן, B2:B10 הוא הטווח שבו הנוסחה תחפש את הקריטריונים הראשונים (מגדר), "M" הוא הקריטריונים הראשונים, C2:C10 הוא הטווח שבו הנוסחה תחפש את הקריטריונים השניים (סימונים), ו-">=40" הוא השני קריטריונים.

נוּסחָה: =COUNTIFS(טווח_קריטריונים1, קריטריונים1,...)

8. SUMPRODUCT

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

בדוגמה זו, רציתי למצוא את הסכום הכולל של כל המוצרים שנמכרו. לשם כך השתמשתי בנוסחה =SUMPRODUCT(B2:B8, C2:C8). כאן, B2:B8 הוא המערך הראשון (כמות המוצרים הנמכרים) ו-C2:C8 הוא המערך השני (המחיר של כל מוצר). לאחר מכן, הנוסחה מכפילה את הכמות של כל מוצר שנמכר עם המחיר שלו ואז מוסיפה את כל זה כדי לספק את סך המכירות.

נוּסחָה: =SUMPRODUCT(מערך1, [מערך2], [מערך3],...)

9. מְטוּפָּח

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

בדוגמה זו, רציתי להסיר את כל הרווחים הנוספים בין המילים Mouse ו-pad ב-A7. בשביל זה השתמשתי בנוסחה =TRIM(A7).

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

נוּסחָה: =TRIM(טֶקסט)

10. מצא/חפש

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

בדוגמה זו, רציתי למצוא את מספר הפעמים ש'Gui' הופיע בתוך Guiding Tech שעבורן השתמשתי בנוסחה =FIND(A2, B2), שהביאה את התוצאה 1. עכשיו אם אני רוצה למצוא את מספר הפעמים ש'gui' הופיע ב-Guiding Tech במקום זאת, אצטרך להשתמש בנוסחת =SEARCH כי היא לא תלוית רישיות.

נוסחה לחיפוש: =FIND(find_text, inside_text, [start_num])

נוסחה לחיפוש: =SEARCH(find_text, inside_text, [start_num])