مارتی یک کاربرگ بزرگ دارد که فهرستی از همه کارمندان (گذشته و حال) شرکتش را نشان می دهد. کاربرگ اطلاعات مختلف در مورد هر کارمند، مانند نام، آدرس، بخش، جنسیت، وضعیت و غیره را دنبال می کند. مارتی اغلب داده ها را برای رفع نیازهای خود فیلتر می کند. او روشی را می خواهد که درصد کارمندان مرد و درصد زن را مشخص کند که بر اساس آن ردیف ها پس از فیلتر نمایش داده شوند. تابع SUBTOTAL می تواند بر روی یک ستون فیلتر شده برای ارائه شمارش های مختلف کار کند، اما به او اجازه نمی دهد که تعداد را بر اساس محتویات ("M" یا "F") ستون فیلتر شده تعیین کند.
یک روش استفاده از PivotTable برای تعیین درصدها است. استفاده از PivotTables نسبتاً آسان است، به ویژه برای پاسخ به یک سؤال واحد مانند این. با این حال، آنها خیلی عالی نیستند، اما به شما اجازه می دهند اطلاعات جزئی کارمندان خود را ببینید—شما می توانید پاسخ کلی به سؤال مرد/زن را ببینید، اما در عین حال نمی توانید جزئیات آن کارمندان را ببینید. بنابراین، من می خواهم روی استفاده از فرمول های مستقیم در پاسخ به مسئله مارتی تمرکز کنم.
ایجاد فرمولی برای بدست آوردن درصدهای مورد نظر دشوارتر از آن چیزی است که در ابتدا به نظر می رسد. به عنوان مثال، تعیین تعداد زمانی که تمام سوابق کارمند قابل مشاهده است آسان است. به عنوان مثال، می توانید به سادگی از چیزی شبیه به این استفاده کنید، با فرض اینکه جنسیت در ستون C قرار دارد، تا مشخص کنید که چند درصد از سوابق مربوط به کارمندان مرد است:
=COUNTIF(C:C,"M")/COUNTA(C:C)-1
مشکل این است که اگر رکوردها را با ستونی متفاوت از ستون C فیلتر کنید. به عنوان مثال، ممکن است آن را با هر آنچه در ستون وضعیت است فیلتر کنید. فرمول بالا همچنان درصدی را بر اساس تمام رکوردهای ستون جنسیت به شما می دهد، نه فقط مواردی که در حال حاضر به دلیل فیلتر قابل مشاهده هستند.
ممکن است در این مرحله فکر کنید که تابع SUBTOTAL ممکن است کار کند، اما مارتی متوجه شد که این کار را نمی کند. مجدداً، اگر جنسیت در ستون C است، می توانید موارد زیر را در پایین ستون C قرار دهید:
=SUBTOTAL(103,C2:C9999)/COUNTA(C2:C9999)
اگرچه این برای نیازهای مورد نظر کار نمی کند. مشکل اصلی این است که تابع SUBTOTAL تعداد رکوردهای قابل مشاهده را به شما می دهد، اما هیچ تشخیصی بین اینکه آیا آن رکوردها حاوی "M" یا "F" در ستون C هستند وجود ندارد. مسئله ثانویه این است که COUNTA همه رکوردها را می شمارد، نه اینکه فقط آنهایی که نمایش داده می شوند بنابراین، فرمول درصدی از رکوردهای نمایش داده شده حاوی "M" یا "F" را به شما نمی دهد، بلکه درصدی از تعداد رکوردهای نمایش داده شده از تعداد کل رکوردها را به شما می دهد.
در صورت تمایل می توانید از ستون کمکی استفاده کنید. فقط یک ستون اضافه کنید تا وضعیت "M" یا "F" هر کارمند را به صورت 1 یا 0 نشان دهد. این کار را می توان با یک فرمول ساده انجام داد، مانند این:
=IF(C2="M",1,0)
این فرمول مجدداً فرض می کند که ستون جنسیت C است. با فرض اینکه این فرمول در ستون X (ستون کمکی شما) قرار می گیرد، می توانید از دو تابع SUBTOTAL به این شکل استفاده کنید:
=SUBTOTAL(109,X2:X9999)/SUBTOTAL(103,C2:C9999)
اولین استفاده از SUBTOTAL تعداد ردیف هایی را که حاوی "M" هستند و استفاده دوم از SUBTOTAL تعداد کل ردیف های قابل مشاهده را به شما می دهد. نتیجه فرمول درصدی از کارگران مرد است که سوابق آنها قابل مشاهده است. با کم کردن نتیجه مرد از 1 می توانید درصد کارگران زن را تعیین کنید.
اگر نمی توانید از ستون کمکی استفاده کنید (یا ترجیح می دهید از ستون کمکی استفاده نکنید)، دور زدن کاستی های رویکردهایی که قبلاً مورد بحث قرار گرفت، کمی دشوارتر است. به فرمولی نیاز دارد که کمی پیچیده تر است. فرمول زیر کار خواهد کرد، اما من فرمول واحد را به چهار خط تقسیم کردم تا توضیح آن کمی آسان تر شود.
=SUMPRODUCT(SUBTOTAL(103,OFFSET(C2:C9999,
ROW(C2:C9999)-MIN(ROW(C2:C9999)),,1)),
ISNUMBER(SEARCH("M",C2:C9999))+0)
/SUBTOTAL(103,C2:C9999)
من قصد دارم در مورد نحوه عملکرد این فرمول با جزئیات کامل صحبت کنم، پس کمی با من صبر کنید - این مدتی طول می کشد. اجازه دهید ابتدا نگاهی به این بخش از فرمول بیندازیم که انتهای ردیف اول و کل ردیف دوم را در بر می گیرد:
OFFSET(C2:C9999,ROW(C2:C9999)-MIN(ROW(C2:C9999)),,1)
استفاده از تابع OFFSET در اینجا بر 3 پارامتر متکی است. اولی مربوط به یک نقطه شروع برای محاسبه مرجعی است که برگردانده می شود (در این مورد نقطه شروع C2:C9999 است). پارامتر دوم تعداد ردیف هایی است که باید از ابتدای آن محدوده تعریف شده در پارامتر اول جابجا شوند. در این حالت، ردیف هایی که باید افست شوند با کم کردن کمترین تعداد ردیف در محدوده (که همیشه مقدار 2 را برمی گرداند) از ردیف واقعی در حال تجزیه و تحلیل تعریف می شود. بنابراین، به عنوان مثال، اگر ردیف مورد تجزیه و تحلیل ردیف 10 باشد، پس از تفریق عدد 2 (ردیف شروع) از آن یک افست 8 ردیف از ابتدای محدوده مشخص شده در پارامتر اول به ما می دهد.
پارامتر سوم خالی است، بنابراین به طور پیش فرض روی 0 است. این تعداد ستون هایی است که باید از ستون اول در محدوده مشخص شده در پارامتر اول افست شوند. در نهایت، چهارمین پارامتر عدد 1 است که نشان می دهد شما می خواهید OFFSET محدوده ای را برگرداند که تنها 1 سلول ارتفاع دارد.
نکته اصلی این است که کل این بخش از فرمول گنجانده شده است به طوری که یک مرجع به یک سلول در ستون مورد تجزیه و تحلیل برمی گرداند. برای توضیح در این فرمول، اجازه دهید آنچه را که برگردانده شده است "SingleCell" بنامیم. با وصل کردن آن به فرمول اصلی، به این می رسیم:
=SUMPRODUCT(SUBTOTAL(103,SingleCell),
ISNUMBER(SEARCH("M",C2:C9999))+0)
/SUBTOTAL(103,C2:C9999)
سپس اولین تابع SUBTOTAL نتیجه COUNTA (که با مقدار 3 که برای پارامتر اول استفاده می شود نشان داده می شود) را برای سلول تک برمی گرداند. اثر این است که SUBTOTAL بسته به اینکه سلول قابل مشاهده باشد یا نه، 0 یا 1 را برمی گرداند. (اگر سلول از نتایج فیلتر شود، 0 برگردانده می شود. اگر فیلتر نشده باشد - قابل مشاهده است - 1 برگردانده می شود.)
بخش بعدی فرمول به توابع ISNUMBER و SEARCH متکی است. این بخش از کد بسته به اینکه سلول دارای حرف "M" باشد یا خیر، 0 یا 1 را برمی گرداند. آنچه در نهایت به آن می رسید چیزی است که به این خلاصه می شود:
=SUMPRODUCT(1,0)/SUBTOTAL(103,C2:C9999)
در مورد این تک ردیف، SUMPRODUCT 0 را برمی گرداند، به این معنی که ردیف در شمارش کلی "حساب نمی شود". از آنجایی که SUMPRODUCT یک تابع مبتنی بر آرایه است، یک محصول را بر اساس ضرب هر ردیف در محدوده اصلی به صورت جداگانه محاسبه می کند. بنابراین، تعداد تمام ردیف هایی را تعیین می کند که دو شرط را برآورده می کنند: ردیف قابل مشاهده است و ردیف حاوی حرف "M" است.
این در نهایت بر نتیجه تابع SUBTOTAL نهایی تقسیم می شود که نتیجه COUNTA تعداد ردیف های قابل مشاهده است. نتیجه نهایی درصدی از تعداد سطرهای قابل مشاهده است که کاراکتر "M" در ستون C قابل مشاهده است - نتیجه دقیقی که مارتی می خواست.
برای به دست آوردن درصد ماده ها در ردیف های قابل مشاهده، تنها کاری که باید انجام دهید این است که آنچه را که جستجو می شود تغییر دهید: در فرمول "M" را به "F" تغییر دهید، و شما خوب خواهید بود.
هر چند یک هشدار وجود دارد که باید به آن اشاره کرد. تابع SEARCH بین حروف بزرگ و کوچک تفاوتی قائل نمی شود. بنابراین، اگر از "Male" به جای "M" و "Female" به جای "F" در ستون جنسیت خود (ستون C) استفاده کنید، جستجوی "Male" در فرمول با هر ردیف مطابقت دارد زیرا سلول هایی که حاوی " Female" شامل کاراکترهای "مرد" خواهد بود. بنابراین، بهتر است از "M" و "F" استفاده کنید، یا اگر باید از "مذکر" و "مونث" استفاده کنید، فقط از "مونث" در فرمول استفاده کنید و درصد رکوردهای مرد را برابر با 1 منهای محاسبه کنید. درصد زن
