כיצד להשתמש COUNTIFS, SUMIFS, AVERAGEIFS ב - Excel

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

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

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

Excel COUNT, SUM ו- AVERAGE

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

= COUNT (E2: E16)

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

= SUM (E2: E16)

לבסוף, כדי לגלות את המכירות הממוצע שעשינו עבור כל הטלפונים, אנחנו יכולים להשתמש ממוצע An University you

= ממוצע (E2: E16)

התוצאה צריכה להיות כדלקמן:

COUNT, SUM ו- AVERAGE נוסחאות יפעלו רק עבור רשומות שבהן ערך התא הוא בתבנית מספר. כל רשומה בטווח הנוסחה (כלומר. E2: E16 בדוגמה זו) לא בתבנית המספר תתעלם.

לכן, ודא שכל התאים בתוך COUNT, SUM ו- AVERAGE נוסחו כולם בפורמט מספר, לא טקסט. נסה להשתמש באותה נוסחה, אבל עם E: E כמו טווח במקום E2: E16. זה יחזיר את אותה תוצאה כמו קודם, כי זה מתעלם את הכותרת (כלומר. מחיר מבצע), אשר בפורמט טקסט.

עכשיו, מה אם אנחנו רוצים לדעת מספר המכירות, הסכום הכולל של המכירות ואת הסכום הממוצע של המכירות לכל טלפון, רק עבור אלה שנמכרו בארה"ב? זה המקום שבו COUNTIFS, SUMIFS ו- AVERAGEIFS ממלאים תפקיד חשוב. שימו לב לנוסחה הבאה:

COUNTIFS

התפלגות פורמולה:

1. = COUNTIFS ( - ה "49 מציין את תחילתה של נוסחה בתא COUNTIFS הוא החלק הראשון של הפונקציה Excel שבו אנו משתמשים.
2. D2: D16 - מתייחס לטווח הנתונים כדי לבדוק אם הוא עונה על הקריטריונים שייכללו בנוסחת הספירה.
3. "ארה"ב" - קריטריונים לחיפוש בטווח הנתונים שצוין (D2: D16)
4. ) - סוגר סוגר המציין את קצה הנוסחה.

הנוסחה מחזירה 6 שהוא מספר המכירות למוצרים שנשלחו מהמחסן בארה ב ".

SUMIFS

התפלגות פורמולה:

1. = SUMIFS ( - ה "49 מציין את תחילת הנוסחה שוב.
2. E2: E16 - מתייחס למגוון של נתונים שאנחנו רוצים לסכם, כלומר מחיר המכירה בדוגמה שלנו.
3. D2: D16 - מתייחס לטווח הנתונים כדי לבדוק אם הוא עונה על הקריטריונים שייכללו בסכום הכולל.
4. "ארה"ב" - קריטריונים לחיפוש בטווח הנתונים שצוין (D2: D16)
5. ) - סוגר סוגר המציין את קצה הנוסחה.

הנוסחה מראה $ 6,050 סך המכירות שבוצעו עבור מוצרים שנשלחו מהמחסן בארה ב ".

AVERAGEIFS

התפלגות פורמולה:

1. = AVERAGEIFS ( - ה "49 מצביעים על תחילת הנוסחה.
2. E2: E16 - מתייחס לטווח של נתונים שאנחנו רוצים בממוצע. בדוגמה זו, אנחנו רוצים לקבל את כמות המכירות הממוצעת עבור כל הטלפונים הנמכרים בארה"ב.
3. D2: D16 - מתייחס לטווח הנתונים כדי לבדוק אם הוא עונה על הקריטריונים שייכללו בנוסחה הממוצעת.
4. "ארה"ב" - קריטריונים לחיפוש בטווח הנתונים שצוין
5. ) - סוגר סוגר המציין את קצוות הנוסחה.

הנוסחה מראה שאנחנו מכרו את המוצר מסביב $ 1,008 לכל טלפון בארה"ב.

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

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

= COUNTIFS (D2: D16, "ארה"ב", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "ארה"ב", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "ארה"ב", B2: B16, "Samsung")

תוכלו להבחין כי גם ל- Excel יש COUNTIF, SUMIF ו AVERAGEIF נוסחאות ללא הסיומת "S". אלה משמשים דומים COUNTIFS, SUMIFS ו AVERAGEIFS. עם זאת, אלה ללא סיומת "S" בנוסחה יש הגבלה של רק מתן קריטריון אחד לכל נוסחה.

כמו התחביר הוא קצת שונה, אני ממליץ להשתמש COUNTIFS, SUMIFS ו AVERAGEIFS רק כאשר ניתן להשתמש בו לקריטריונים אחד או יותר, במידת הצורך. תהנה!