רשימת חברים היא משימה טיפוסית לעסק קטן: ספור אנשים שעומדים בתנאי, חשב ממוצע לקבוצה, מדוד זמן מתאריך התחלה וסמן את מי שצריך תשומת לב. המיומנויות הן COUNTIFS, AVERAGEIF, חישוב תאריכים ו-IF עם AND או OR.
תכנן לפני שאתה כותב
כתוב כל שאלה כ"כמה", "איזה ממוצע" או "אילו". "כמה" היא ספירה, "איזה ממוצע" הוא AVERAGEIF, ו"אילו" הוא עמודת סימון שנבנית עם IF. החלט אם כל התנאים חייבים להתקיים (AND) או רק אחד מהם (OR). הבלבול ביניהם היא השגיאה הנפוצה ביותר.
ספירה עם תנאים
COUNTIFS לוקחת זוגות: טווח ובדיקה. כל זוג חייב להתקיים כדי שהשורה תיספר. בדיקה עם השוואה נכתבת במירכאות, כמו ">=4". כשמשתמשים בתא, מחברים אותו עם &: =COUNTIFS(B2:B30,">="&F1).
AVERAGEIF פועלת כמו SUMIF, אבל מחזירה את הממוצע של השורות התואמות. אם אף שורה לא מתאימה, היא מחזירה #DIV/0!, רמז לכך שטקסט הקריטריונים שלך שונה מהנתונים, למשל עם רווח מיותר.
תאריכים הם מספרים
Excel שומר תאריך כמספר ימים, לכן חיסור של שני תאריכים נותן את מספר הימים ביניהם. אם תאריך ההתחלה נמצא ב-D2, אז =TODAY()-D2 הוא מספר הימים שעברו. אם התוצאה נראית כמו תאריך, שנה את פורמט התא למספר או לכללי.
מכיוון ש-TODAY משתנה מדי יום, גם התשובות שלך משתנות. לתשובה קבועה במקרה בוחן, השתמש בתאריך שנתון בשאלה, בתא משלו.
דוגמה קטנה מדומה
כדי לתרגל, דמיין רשימה של 10 כרטיסי ספרייה. בעמודה A שם החבר, בעמודה B מספר ההשאלות השנה, בעמודה C סוג הכרטיס (Child או Adult) ובעמודה D התאריך שבו הונפק. אפשר לשאול כמה מבוגרים השאילו לפחות חמישה ספרים, מהו הממוצע של ההשאלות בכרטיסי Child, ואילו כרטיסים בני יותר משנתיים ולא בשימוש.
שים לב איך כל שאלה ממופה לכלי: ספירה עם שני תנאים, ממוצע עם תנאי אחד, וסימון שדורש השוואת תאריך והשוואת השאלות. כתוב את המיפוי הזה על נייר לפני שמתחילים, ואז בדוק כל תוצאה מול ספירה ידנית מהירה של השורות הראשונות.
סימונים ובדיקות
נוסחת סימון צריכה לקרוא כמו הכלל: IF (המבחן הראשון וגם המבחן השני) אז תווית אחת, אחרת תווית אחרת. העתק אותה לכל הרשימה, ואז ספור את התוויות עם COUNTIF והשווה לספירה שביצעת בדרך אחרת. התוויות צריכות להסתכם למספר החברים.
- העתק נוסחאות למטה רק אחרי שהטווחים משתמשים ב-$ במקום שבו הם צריכים להישאר קבועים.
- בדוק גבולות: האם "לפחות 4" כולל חבר עם בדיוק 4?
- סרוק תאים ריקים וטקסט בעמודות מספרים לפני שאתה סומך על ספירה.