دیو در حال تلاش برای ایجاد فرمول SUMIFS است که از سال به عنوان یک معیار و از عدد هفته به عنوان معیار دیگر استفاده می کند. به عنوان مثال، این به او اجازه می دهد تا تمام مقادیر یک هفته خاص در سال گذشته را جمع کند. معیار شماره هفته باید بر اساس هر شماره هفته ای باشد که تاریخ فعلی در آن باشد. بنابراین، اگر امروز در هفته 3 باشد، فرمول باید شامل هفته های 1، 2 و 3 از هر سالی باشد که او مشخص می کند. دیو مطمئن است که راهی برای انجام این کار وجود دارد، اما او نمی تواند راه درستی برای بیان آن در SUMIFS بیابد.
با استفاده از تابع کاربرگ SUMIFS، درک اینکه تابع دقیقاً به چه پارامترهایی نیاز دارد بسیار مهم است. هنگام کار با دو معیار (همانطور که دیو می خواهد انجام دهد - عدد سال و هفته)، نحو فرمول مبتنی بر SUMIFS به این صورت خواهد بود:
=SUMIFS(values_to_sum, years_to_compare, year_criterion,
weeks_to_compare, week_criterion)
در توصیف دیوز، آنچه او دارد یک سری تاریخ و یک سری ارزش های مربوط به آن تاریخ ها است. پارامتر values_to_sum بدیهی است که از آن مقادیری است که دیو دارد، اما years_to_compare و week_to_compare در داده های او وجود ندارند. بنابراین، آنها باید در ستون های کمکی ایجاد شوند.
فرض کنید که ستون A حاوی تاریخ های اصلی Daves و ستون B حاوی مقادیر مرتبط با آن تاریخ ها است. در ستون C می توانید اولین ستون کمکی را با استفاده از این فرمول در سلول C2 ایجاد کنید:
=YEAR(A2)
این فرض را بر این می گذارد که سطر 1 حاوی سرفصل هایی برای ستون های داده شما باشد. فرمول ستون کمکی دوم در سلول D2 قرار می گیرد:
=WEEKNUM(A2)
این فرمول ها را به گونه ای کپی کنید تا برای هر تاریخی که در ستون A نشان داده می شود، یک عدد سال و هفته مرتبط وجود داشته باشد. با قرار دادن این ستون های کمکی، تمام داده های لازم برای کار با تابع SUMIFS که قبلا توضیح داده شد را دارید. چیزی که هنوز ندارید نشانگر این است که کدام سال را می خواهید از مقادیر استخراج کنید. این به راحتی با قرار دادن یک سال در سلول F2 برطرف می شود. اکنون فرمول درخواستی به صورت زیر ظاهر می شود:
=SUMIFS(B:B,C:C,F2,D:D,"
اگر این فرمول را با مثال نحوی که قبلا ارائه شده مقایسه کنید، متوجه خواهید شد که مقادیر_to_sum (B:B)، years_to_compare (C:C)، سال_معیار (F2)، week_to_compare (D:D) و در نهایت هفته_معیار این آخرین عنصر است که باید کمی توضیح داده شود. به نظر می رسد این است:
"
از آنجایی که تابع WEEKNUM هر عدد هفته مناسب برای تاریخ امروز (در این مورد) را برمی گرداند، ممکن است چیزی شبیه "7" را برگرداند. بنابراین، week_criterion به شکل زیر در می آید:
"
این بدان معناست که SUMIFS فقط اعداد هفته را در داده ها در نظر می گیرد که کمتر یا مساوی 7 هستند.
ممکن است تعجب کنید که آیا می توانید از شر ستون های کمکی خلاص شوید. شما نمی توانید این کار را با SUMIFS انجام دهید. دلیل آن این است که تابع انتظار دارد محدوده های داده ای را برای مقایسه انجام دهد و آن محدوده داده ها به عنوان نتیجه فرمول های ستون های C و D محاسبه می شوند.
به عنوان آخرین هشدار برای استفاده از این فرمول، باید دقیقاً بدانید که چگونه می خواهید تعداد هفته خود را محاسبه کنید. در مثال های این نکته، از ساده ترین نسخه تابع WEEKNUM هم در ستون D و هم در فرمول SUMIFS استفاده شده است. پارامترهایی وجود دارد که می توان با WEEKNUM برای تنظیم نحوه عملکرد آن استفاده کرد. در برخی موارد، ممکن است بخواهید به جای آن یک عدد هفته ISO را محاسبه کنید. اطلاعات بیشتر در مورد شماره های هفته WEEKNUM و ISO را می توانید در این نکات پیدا کنید:
http://excelribbon.tips.net/T007804
http://excelribbon.tips.net/T007847
