شمارش ورودی بین تاریخ ها در حالی که طبقه بندی آنها بر اساس محدوده سنی یک کار مشترک در تجزیه و تحلیل داده ها است. این آموزش از طریق ایجاد یک فرمول اکسل که از آن استفاده می کند ، به شما ارائه می دهد COUNTIFSبا FINDبا LEFTبا RIGHTبا SUMPRODUCTوت TEXTBEFORE توابع برای دستیابی به این هدف.
سناریو
فرض کنید شما یک مجموعه داده از کارمندان با آنها دارید تاریخ تولد وت تاریخ ثبت نام، و شما باید حساب کنید که تعداد زیادی از کارمندان برای یک دوره زمانی معین در یک محدوده سنی خاص قرار می گیرند.
- ستون a: نام کارمند
- ستون B: تاریخ تولد
- ستون C: تاریخ ثبت نام
- ستون D: سن (محاسبه شده)
داده های مثال:
| بوها | شرح | جف | |
|---|---|---|---|
| 1 | کارمند | تاریخ تولد | تاریخ ثبت نام |
| 2 | جان | 03/12/1985 | 15/06/2022 |
| 3 | مریم | 10/08/1990 | 20/07/2022 |
| 4 | الکس | 25/03/2000 | 05/01/2023 |
| 5 | داود | 12/09/1995 | 14/03/2023 |
شما باید کارمندان سالخورده را حساب کنید 20-30 ساله چه کسی بین 1 ژانویه ، 2022 و 31 دسامبر 2023بشر
مرحله 1: سن را محاسبه کنید
برای محاسبه یک کارمند پیری بر اساس تاریخ تولد آنها و تاریخ مرجع ، استفاده کنید:
=DATEDIF(B2, TODAY(), "Y")
| بوها | شرح | جف | د | |
|---|---|---|---|---|
| 1 | کارمند | تاریخ تولد | تاریخ ثبت نام | پیری |
| 2 | جان | 03/12/1985 | 15/06/2022 | =DATEDIF(B2, TODAY(), "Y") |
| 3 | مریم | 10/08/1990 | 20/07/2022 | =DATEDIF(B3, TODAY(), "Y") |
| 4 | الکس | 25/03/2000 | 05/01/2023 | =DATEDIF(B4, TODAY(), "Y") |
| 5 | داود | 12/09/1995 | 14/03/2023 | =DATEDIF(B5, TODAY(), "Y") |
خروجی مانند:
| بوها | شرح | جف | د | |
|---|---|---|---|---|
| 1 | کارمند | تاریخ تولد | تاریخ ثبت نام | پیری |
| 2 | جان | 03/12/1985 | 15/06/2022 | 38 |
| 3 | مریم | 10/08/1990 | 20/07/2022 | 33 |
| 4 | الکس | 25/03/2000 | 05/01/2023 | 23 |
| 5 | داود | 12/09/1995 | 14/03/2023 | 28 |
این سن را بر اساس تاریخ امروز محاسبه می کند. اگر به جای تاریخ امروز یک تاریخ مرجع خاص می خواهید ، جایگزین کنید TODAY() با تاریخ مورد نظر خود
اکنون ، این فرمول را برای محاسبه سن کلیه کارمندان در مجموعه داده ها گسترش دهید.
مرحله 2: کارمندان را بر اساس محدوده سنی و تاریخ ثبت نام حساب کنید
با استفاده از COUNTIFS
در COUNTIFS عملکرد برای شمارش مدخل ها بر اساس معیارهای متعدد بهترین است.
فرمول برای شمارش کارمندان بین 20 تا 30 سال چه کسی بین 1 ژانویه ، 2022 و 31 دسامبر 2023:
=COUNTIFS(D2:D100,">=20",D2:D100,"<=30",C2:C100,">=01/01/2022",C2:C100,"<=31/12/2023")
توضیح:
-
D2:D100, ">=20"→ کارمندان را با افزایش سن شمارش می کند 20 یا بالاتر -
D2:D100, "<=30"→ کارمندان را شمارش می کند 30 یا پایین -
C2:C100, ">=01/01/2022"→ تضمین می کند تاریخ ثبت نام روشن یا بعد از آن باشد 1 ژانویه 2022 -
C2:C100, "<=31/12/2023"→ تضمین می کند تاریخ ثبت نام در زمان یا قبل باشد 31 دسامبر 2023
مرحله 3: استفاده از SUMPRODUCT برای فیلتر پیشرفته
اگر یک رویکرد انعطاف پذیر تر را ترجیح می دهید ، SUMPRODUCT همچنین می توان استفاده کرد:
=SUMPRODUCT((D2:D100>=20)*(D2:D100<=30)*(C2:C100>=DATE(2022,1,1))*(C2:C100<=DATE(2023,12,31)))
این فرمول به طور مشابه کار می کند اما امکان سفارشی سازی بیشتری را فراهم می کند.
مرحله 4: استخراج سن از داده های متن با استفاده از FINDبا LEFTبا RIGHTوت TEXTBEFORE
اگر مجموعه داده های شما به عنوان بخشی از متن وارد شده است (به عنوان مثال ، "John - Age 25" در یک سلول واحد) می توانید سن را با استفاده از آن استخراج کنید FINDبا LEFTوت TEXTBEFORE:
=TEXTBEFORE(RIGHT(A2,LEN(A2)-FIND("-",A2)), " ")
این شماره سن را از قالب متن مانند استخراج می کند "John - Age 25"بشر
سپس می توانید از این سن استخراج شده در خود استفاده کنید COUNTIFS فرمول
پایان
با استفاده از این فرمول های اکسل ، می توانید به طور موثری کارمندانی را که در یک خاص قرار می گیرند حساب کنید دامنه سنی و داشتن در یک محدوده تاریخ خاص ثبت شده استبشر ترکیبی از COUNTIFSبا SUMPRODUCTبا FINDبا LEFTبا RIGHTوت TEXTBEFORE امکان انعطاف پذیری در رسیدگی به داده های ساختاری و بدون ساختار را فراهم می کند. آموزش های جالب تر را در excel24x7.com پیدا کنید
