کیم یک کاربرگ دارد که سه ستون در آن دارد. ستون A شامل نام خانوادگی یک شخص، ستون B شامل نام و ستون C شامل شماره تلفن فرد است. اگر کیم نام و نام خانوادگی افراد را بشناسد (مثلاً آنها به ترتیب در سلول های F1 و F2 هستند)، از خود می پرسد که از چه نوع فرمول جستجو برای بازگرداندن شماره تلفن اولین نفری که با نام و نام خانوادگی مطابقت دارد استفاده می کند.
در واقع چندین فرمول مختلف وجود دارد که می توانید برای تعیین شماره تلفن از آنها استفاده کنید. بیشتر رویکردها شامل استفاده از فرمول های آرایه است که همیشه با استفاده از Ctrl+Shift+Enter در یک سلول وارد می شوند . برای سهولت درک فرمول ها، بهتر است با محدوده های نامگذاری شده کار کنید. به عنوان مثال، نام های زیر را تنظیم کنید:
- همه نام‌های خانوادگی را در ستون A انتخاب کنید و نامی مانند LNames به آنها بدهید.
- همه نام‌ها را در ستون B انتخاب کنید و نامی مانند FNames به آنها بدهید.
- همه شماره‌های تلفن را در ستون C انتخاب کنید و نامی مانند Phones برای آنها انتخاب کنید.
در اینجا انواع فرمول های آرایه وجود دارد که می توانید برای پیدا کردن شماره تلفن از آنها استفاده کنید:
=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)
=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))
=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))
=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)
علاوه بر این، می توانید یک فرمول آرایه ای بسازید که بر تابع ROW تکیه دارد، همانطور که در اینجا نشان داده شده است:
=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))
=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))
اگر از این رویکرد استفاده می‌کنید (با تکیه بر تابع ROW)، ممکن است لازم باشد بخش "-1" از فرمول‌ها را تنظیم کنید تا تعداد ردیف‌هایی که قبل از داده‌های واقعی شما ظاهر می‌شوند را منعکس کنید. در این مثال، سطر به اندازه یک کاهش می یابد زیرا اولین سطر جدول داده از سرصفحه های هر ستون تشکیل شده است. خود داده در ردیف 2 شروع می شود.
توجه داشته باشید که تمام فرمول‌هایی که تاکنون استفاده شده‌اند به ترکیب نام با نام خانوادگی برای مقایسه متکی هستند. این در برخی موارد شانس مواجهه با "اثبات کاذب" را دارد. به عنوان مثال، فرض کنید که دو تا از نام هایی که در داده های خود دارید، Thom Astonfield و Thomas Tonfield هستند. از آنجایی که مورد حروف در نام ها در این فرمول ها مهم نیست، وقتی نام و نام خانوادگی این افراد را ترکیب می کنید، دقیقاً یکسان هستند. بنابراین، اگر به دنبال شماره تلفن توماس تونفیلد هستید و نام او در لیست بعد از تام استونفیلد ظاهر می شود، همیشه به جای توماس شماره تلفن تامز را دریافت خواهید کرد.
برای دور زدن این مشکل احتمالی، ممکن است بخواهید نوعی جداکننده بین نام و نام خانوادگی قرار دهید. با استفاده از یکی از فرمول های قبلی در نکته، تنها کاری که باید انجام دهید این است که تنظیماتی را انجام دهید، همانطور که در اینجا نشان داده شده است:
=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))
اضافه کردن دو نقطه بین نام و نام خانوادگی به عنوان یک جداکننده عمل می کند و احتمال مثبت کاذب را از بین می برد.
همچنین باید توجه داشته باشید که اگر سلول های F1 و F2 حاوی نامی باشند که اصلاً در داده ها ظاهر نشود، اطلاعاتی که از فرمول دریافت می کنید اشتباه خواهد بود. بسته به تنوع عملکرد مورد استفاده، ممکن است یک شرط خطای واقعی (مانند #N/A یا #REF) را برگردانید یا ممکن است داده‌های اشتباه ساده را برگردانید. در مواردی که یک شرط خطا برگردانده می‌شود، ممکن است بخواهید فرمول خود را طوری تنظیم کنید که احتمال پیدا نکردن یک مطابقت را در نظر بگیرد، به این ترتیب:
=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))),
"no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))
هنگام استفاده از این نوع فرمول ها مواردی وجود دارد که باید در نظر داشته باشید. اول و مهمتر از همه، قابل اعتماد بودن اطلاعاتی که به دست می آورید تا حد زیادی به کیفیت اطلاعات موجود در لیست داده های شما بستگی دارد. اگر داده‌های شما دارای غلط املایی باشد، حاوی موارد خالی باشد، به ترتیب عجیبی مرتب شده باشد، یا چندین ورودی برای یک شخص وجود داشته باشد، می‌تواند بر آنچه فرمول برمی‌گرداند تأثیر بگذارد.
اگر در مورد کیفیت داده های خود خیلی مطمئن نیستید، ممکن است بخواهید به جای یک فرمول، به سادگی از قابلیت های فیلتر اکسل استفاده کنید. یک AutoFilter اعمال کنید، و می توانید از دو ستون اول داده های خود برای انتخاب نام و نام خانوادگی استفاده کنید. سپس هر شماره تلفنی را برای فردی که انتخاب کرده اید برمی گرداند. انجام آن بسیار ساده است و انتخاب داده های مورد نیاز را آسان می کند.
در نهایت، باید متوجه شوید که روش‌های دیگری نیز وجود دارد که می‌توانید برای رسیدگی به این مشکل استفاده کنید. برای مثال، می‌توانید فرمولی ایجاد کنید که از تابع DGET استفاده می‌کند، اما انجام این کار مستلزم افزودن یک جدول معیارهای کوچک به کاربرگ یا کتاب کار شما است. از آنجایی که کیم مشخص کرد که نمی‌تواند نتایج میانی را در یک کاربرگ اضافه کند، تصمیمی در تحریریه گرفته شد که تابع DGET را به‌عنوان راه‌حل شامل نشود زیرا نیاز به اضافه کردن جدول معیارها دارد.
علاوه بر این، اگر با استفاده از ماکرو راحت هستید، می توانید یک تابع تعریف شده توسط کاربر ایجاد کنید که داده ها را بررسی کرده و شماره تلفن درخواستی را برمی گرداند. مزیت استفاده از چنین رویکردی این است که انعطاف پذیری بیشتری را در پردازش اطلاعاتی که تابع واقعاً برمی گرداند به شما ارائه می دهد.
