استفاده از اکسل برای ایجاد پایگاه داده های کوچک غیرعادی نیست. به عنوان مثال، ممکن است فهرستی از اعضای باشگاه پرورش دهندگان سگ پودل خود را در اکسل نگه دارید، یا ممکن است از آن برای حفظ فهرستی از مخاطبین فروش فعال خود استفاده کنید. در این موارد، ممکن است تعجب کنید که چگونه می توانید تعداد رکوردهایی را که بیش از یک معیار را برآورده می کنند، به دست آورید.
بیایید بگوییم که شما در حال تجزیه و تحلیل لیست عضویت خود هستید و می‌خواهید تعداد رکوردهایی را تعیین کنید که در آن ستون جنسیت حاوی "F" و ستون شهر حاوی یک شهر خاص است، مانند "Norwood". این، البته، مفید خواهد بود، زیرا به این سوال مهم پاسخ می دهد که چند نفر از اعضای زن گروه شما در نوروود زندگی می کنند.
اکسل شامل تعدادی توابع کاربرگ است که برای تعیین تعداد رکوردها در یک لیست مفید هستند. اینکه چگونه می‌توانید در موقعیتی که دو معیار باید رعایت شوند، استفاده کنید، ممکن است فوراً مشخص نباشد. بیایید شش راه خاص را بررسی کنیم که می توانید به هدف مورد نظر اعضای زن از نوروود برسید. (فرض کنید که ستون C ستون جنسیت و ستون F ستون شهر باشد.)
اولین راه برای حل مشکل از طریق استفاده از تابع COUNTIFS است. اگر ستون جنسیت شما ستون C و ستون شهر شما ستون F است، می توانید از فرمول زیر استفاده کنید:
=COUNTIFS(C1:C500,"F",F1:F500,"Norwood")
در ستون جنسیت (C) برای هر سلول حاوی "F" و ستون شهر (F) برای هر سلول حاوی "Norwood" نگاه می کند. نتیجه تعداد رکوردهایی است که هر دو معیار را برآورده می کند.
روش دوم استفاده از تابع DCOUNTA است. این تابع به شما امکان می دهد مجموعه ای از معیارها را تعریف کنید و از آن معیارها به عنوان مبنایی برای تجزیه و تحلیل لیست داده ها استفاده کنید. مانند تمام توابع داده در اکسل، DCOUNTA بر سه پارامتر متکی است: محدوده داده، ستون مورد استفاده در مقایسه ها و محدوده معیار. برای استفاده از تابع، یک جدول معیارها را در یک ناحیه استفاده نشده از کاربرگ خود تنظیم کنید. به عنوان مثال، می توانید موارد زیر را در سلول های AA1 تا AB2 تنظیم کنید: (شکل 1 را ببینید.)
شکل 1. جدول معیارهای تابع DCOUNTA.
سپس، با فرض اینکه جدول داده های اصلی شما در سلول های A1:K500 (بدیهی است یک باشگاه بزرگ پرورش دهندگان سگ پودل) باشد، می توانید از موارد زیر برای تعیین تعداد استفاده کنید:
=DCOUNTA(A1:K500,1,AA1:AB2)
نتیجه شمارشی است که با معیارهایی که در AA1:AB2 مشخص کرده‌اید مطابقت دارد. همچنین توجه داشته باشید که نام هایی که در AA1 و AB1 استفاده کرده اید باید دقیقاً با برچسب هایی که در رکوردهای جدول خود استفاده کرده اید مطابقت داشته باشد. وقتی آنها این کار را انجام می دهند، محتویات ستون جنسیت (ستون C) باید "F" و محتوای ستون شهر (ستون F) باید "Norwood" باشد تا رکورد به تعداد اضافه شود.
راه حل سوم استفاده از فرمول آرایه برای برگرداندن یک پاسخ است. فرمول آرایه به طور جالبی از تابع SUM و کمی حساب بولی برای تعیین اینکه آیا یک رکورد باید شمارش شود استفاده می کند. موارد زیر را در نظر بگیرید:
=SUM((C2:C500="F")*(F2:F500="Norwood"))
به سادگی فرمول فوق را در یک سلول تایپ کنید و سپس با فشار دادن Ctrl+Shift+Enter آن را به پایان برسانید . با این کار اکسل متوجه می شود که شما یک فرمول آرایه را وارد می کنید. این فرمول به این دلیل کار می کند که محتویات هر ردیف در آرایه را به نوبه خود با توجه به معیارهای مشخص شده در فرمول مقایسه می کند. ابتدا محتویات ستون C را با "F" مقایسه می کند. اگر مطابقت داشته باشد، مقایسه True را برمی‌گرداند که مقدار عددی 1 است. سپس محتویات ستون F با "Norwood" مقایسه می‌شود. اگر این مقایسه درست باشد، 1 برگردانده می شود. بنابراین، 1 * 1 برابر است با 1، و این به SUM آرایه اضافه می شود. اگر هر یک از مقایسه ها False باشد، مقدار عددی 0 برگردانده می شود و 1 * 0 برابر با 0 است (همانطور که 0 * 0 و 0 * 1 است)، که بر SUM در حال اجرا تأثیری ندارد.
روش چهارم و نزدیک به هم استفاده از تابع SUMPRODUCT است، اما نه در فرمول آرایه. شما به سادگی می توانید از موارد زیر در هر سلولی که می خواهید بدانید آیا این دو معیار برآورده شده اند یا خیر استفاده کنید:
SUMPRODUCT((C2:C500="F")*(F2:F500="Norwood"))
به یاد داشته باشید، این یک فرمول آرایه نیست، بنابراین لازم نیست Ctrl+Shift+Enter را فشار دهید . این فرمول دوباره از طریق جادوی ریاضی بولی کار می کند.
پنجمین راه حل ممکن، که کمی دستی تر از مواردی است که قبلاً مورد بحث قرار گرفت، استفاده از ویژگی AutoFilter به همراه یک جمع فرعی است. با فرض اینکه رکوردهای داده شما در A1:K500 هستند، با برچسب های ستون در ردیف 1، این مراحل را دنبال کنید:
- هر سلولی را در جدول داده انتخاب کنید.
- تب Data روبان را نمایش دهید.
- روی ابزار Filter در گروه Sort & Filter کلیک کنید. اکسل باید نشانگرهای کشویی AutoFilter را در کنار هر برچسب ستون در ردیف 1 نمایش دهد.
- با استفاده از نشانگر کشویی برای ستون جنسیت (ستون C)، F را انتخاب کنید. لیست شما به طور خودکار فیلتر می شود تا فقط اعضای زن نمایش داده شود.
- با استفاده از نشانگر کشویی ستون شهر (ستون F)، نوروود را انتخاب کنید. لیست شما به طور خودکار نمایش داده می شود تا فقط اعضای زن ساکن در نوروود را نمایش دهد.
- در پایین جدول داده ها (ردیف 501)، در هر ستون دلخواه، فرمول زیر را وارد کنید:
=SUBTOTAL(3,C2:C500)
این فرمول باعث می شود که تابع SUBTOTAL تابع COUNTA را برای استخراج یک جمع فرعی اعمال کند. به عبارت دیگر، تعداد تمام رکوردهایی که توسط فیلتر نمایش داده می شوند را برمی گرداند. این تعداد مورد نظر است.
رویکرد ششم این است که از جادوگر جمع شرطی برای ارائه فرمولی برای شما استفاده کنید. (جادوگر جمع شرطی به عنوان یک افزونه اکسل برای اکسل 2007 و نسخه‌های قبلی در دسترس است؛ به طور پیش‌فرض در اکثر سیستم‌ها فعال است. از Excel 2010 حذف شد.) برای استفاده از جادوگر جمع شرطی این مراحل را دنبال کنید:
شکل 2. جادوگر جمع شرطی.
- سلولی را در جایی از داده هایی که می خواهید تجزیه و تحلیل کنید انتخاب کنید.
- تب Formulas روبان را نمایش دهید.
- در گروه Solutions (سمت راست روبان) روی Conditional Sum کلیک کنید. اکسل اولین مرحله از ویزارد جمع شرطی را نمایش می دهد. دامنه کامل داده های شما باید قبلاً در کادر محاوره ای نشان داده شود. (شکل 2 را ببینید.)
- روی Next کلیک کنید. اکسل مرحله بعدی ویزارد را نمایش می دهد.
- در لیست کشویی Column to Sum، جنسیت را انتخاب کنید.
- در ستون کشویی، جنسیت را انتخاب کنید.
- در کشویی Is، علامت مساوی را انتخاب کنید.
- در منوی کشویی This Value، F را انتخاب کنید.
- روی Add کلیک کنید. شرایطی که مشخص کردید اکنون در کادر محاوره ای ظاهر می شود.
- در ستون کشویی، شهر را انتخاب کنید.
- در کشویی Is، علامت مساوی را انتخاب کنید.
- در منوی کشویی This Value، Norwood را انتخاب کنید.
- روی Add کلیک کنید. شرط دوم اکنون در کادر محاوره ای ظاهر می شود.
- روی Next کلیک کنید. اکسل مرحله سوم ویزارد را نمایش می دهد.
- گزینه Copy Just the Formula To a Single Cell را انتخاب کنید.
- روی Next کلیک کنید. اکسل مرحله چهارم (و آخرین) ویزارد را نمایش می دهد.
- در کاربرگ، روی سلولی که می‌خواهید حاوی نتیجه فرمول شما باشد، کلیک کنید.
- بر روی Finish کلیک کنید.
نتیجه یک فرمول مناسب برای شرایطی است که در سلولی که در مرحله 1 انتخاب کرده اید، مشخص شده است.
بدون شک راه حل های ممکن دیگری وجود دارد که می توانید از آنها برای تعیین تعداد رکوردها استفاده کنید. با این حال، اینها "انتخاب زیادی" هستند و به شما امکان می دهند پاسخ را سریع و آسان تعیین کنید.
