استیون از اکسل برای پایگاه داده فیلم شخصی استفاده می کند. در یک ستون او امتیازی را دارد که به هر فیلم در مقیاس یک تا چهار داده می شود که با استفاده از یک، دو، سه یا چهار ستاره تعریف شده است. او می خواهد اطلاعاتی اضافه کند که نشان می دهد چند فیلم * دارد، چند ** فیلم دارد و غیره. استیون فهمیده است که چگونه تعداد * فیلم ها را بشمارد، اما وقتی سعی می کند تعداد فیلم های ** را بشمارد. ستون شکل اشتباه است.
راه های متعددی وجود دارد که می توان این کار را انجام داد، اما ابتدا یک نظر در مورد انتخاب ستاره ها برای هدفی مانند این: در طرح بزرگ چیزها که اکسل است، ستاره اهداف زیادی دارد. بیشتر اوقات در فرمول ها به عنوان نماد ضرب و تقریباً به همان اندازه به عنوان نماد عام در بسیاری از آرگومان های فرمول استفاده می شود. به همین دلیل، استفاده از ستاره برای مقاصد دیگر، به ویژه برای مواردی که می خواهید شمارش کنید، مانند رتبه بندی فیلم، ایده خوبی نیست. شاید بهتر باشد در این مورد به سادگی از اعداد 1 تا 4 برای رتبه بندی استفاده کنید، زیرا کار با اعداد بسیار آسان است و در استفاده از آنها مبهم هستند.
اگر باید از ستاره استفاده کنید، راه های مختلفی وجود دارد که می توانید فرمولی برای انجام شمارش ها جمع آوری کنید. تابع SUMPRODUCT کار را به خوبی انجام می دهد. با فرض قرار گرفتن ستاره ها در ستون C، می توانید از موارد زیر استفاده کنید:
=SUMPRODUCT(--(C:C="*"))
=SUMPRODUCT(--(C:C="**"))
=SUMPRODUCT(--(C:C="***"))
=SUMPRODUCT(--(C:C="****"))
به استفاده از دو علامت منفی در هر یک از این فرمول ها توجه کنید. این کاربرد به طور دقیق تر به عنوان «دبل یوناری» (نژاد، درست است؟) نامیده می شود و برای وادار کردن نتایج True/False به معادل های عددی (1/0) استفاده می شود. این امر ضروری است زیرا فرمولی مانند C:C="*" True یا False را برمی گرداند و SUMPRODUCT به مقادیر عددی نیاز دارد. بدون تبدیل اجباری یوناری دوگانه، تابع SUMPRODUCT هر بار 0 برمی گرداند.
همچنین می توانید از SUMPRODUCT کمی متفاوت برای بررسی طول هر آنچه در ستون C است استفاده کنید. اگر C فقط دارای ستاره باشد، این روش به خوبی کار می کند، اما اگر از چیزی متفاوت از ستاره استفاده کنید نیز کار خواهد کرد:
=SUMPRODUCT(--(LEN(C:C)=1))
=SUMPRODUCT(--(LEN(C:C)=2))
=SUMPRODUCT(--(LEN(C:C)=3))
=SUMPRODUCT(--(LEN(C:C)=4))
شما همچنین می توانید از تابع SUM مستقیم استفاده کنید، اما فرمول های زیر باید با استفاده از Ctrl+Shift+Enter وارد شوند . (آنها فرمول های آرایه ای هستند.)
=SUM(IF(C:C="*",1,0))
=SUM(IF(C:C="**",1,0))
=SUM(IF(C:C="***",1,0))
=SUM(IF(C:C="****",1,0))
توجه داشته باشید که مثال های تا کنون از توابع جمع آوری، به ویژه SUMPRODUCT و SUM استفاده می کنند. این توابع مشکلی که قبلاً در مورد سوء تفاهم ستاره ذکر شد را ندارند. با این حال، هنگام استفاده از توابع شمارش با مشکل مواجه می شوید. به عنوان مثال، موارد زیر نتایج مطلوب را به همراه نخواهد داشت:
=COUNTIF(C:C,"*")
ستاره به عنوان یک کاراکتر عام عمل می کند و با هر چیزی در یک سلول مطابقت دارد. بنابراین، شما در نهایت با تعدادی از سلول های ستون C که حاوی هر چیزی هستند، می شوید. می توانید مشخص کنید که می خواهید با ستاره به عنوان یک کاراکتر تحت اللفظی (به جای علامت عام) رفتار کنید، به این صورت که قبل از آن علامت علامت بزنید:
=COUNTIF(C:C,"~*")
وقتی صحبت از دو ستاره می شود، ممکن است فکر کنید که این کار می کند:
=COUNTIF(C:C,"~**")
این عادت ندارد. اکسل این را به عنوان "یک ستاره تحت اللفظی منفرد به دنبال هر چیزی" تعبیر می کند. به عبارت دیگر، ستاره اول تحت اللفظی است و دومی همچنان یک علامت عام است. هر یک از ستاره ها باید قبل از تار و مد قرار بگیرند، به این ترتیب:
=COUNTIF(C:C,"~*")
=COUNTIF(C:C,"~*~*")
=COUNTIF(C:C,"~*~*~*")
=COUNTIF(C:C,"~*~*~*~*")
در نهایت، اگر می خواهید از استفاده از فرمول ها با هم صرف نظر کنید، می توانید یک PivotTable ایجاد کنید که به ستون رتبه بندی فیلم های شما اشاره می کند. اگر از آن ستون به عنوان یک ردیف در PivotTable استفاده کنید و روش تجمیع را طوری تغییر دهید که محتویات سلول های ستون را شمارش کند، می توانید خلاصه خوبی از تعداد هر نوع رتبه بندی به فیلم های خود دریافت کنید.
