استیو شماره تلفن هایی مانند 3035551212 در ستون A دارد و دوست دارد بتواند به سه رقم اول (کد منطقه) نگاه کند و در ستون B، وضعیتی که آن کد منطقه با آن مرتبط است، برگردد. او در مورد بهترین راه برای انجام این کار تعجب می کند.
اکسل دارای توابع زیادی است که وقتی می خواهید داده ها را دستکاری کنید، زندگی را آسان تر می کند. در این مورد، استفاده از تابع VLOOKUP برای تطبیق کد منطقه با وضعیت مربوطه ساده است.
قبل از اعمال یک تابع برای بازیابی اطلاعات مورد نظر خود، باید یک جدول داده ساده ایجاد کنید که حاوی داده هایی است که می خواهید بازیابی شود. در این جدول، شما باید کدهای منطقه و حالت‌ها را در ستون‌های خود در کنار هم، در کاربرگ خود، مرتب‌سازی شده بر اساس کد منطقه داشته باشید. برای مثال، می‌توانید کدهای منطقه را در ستون F و حالت‌های آن کدهای منطقه را در ستون G قرار دهید. کدهای منطقه و وضعیت‌ها را می‌توانید در تعدادی از وب‌سایت‌ها پیدا کنید، یا در صورت تمایل می‌توانید جدول خود را ایجاد کنید.
هنگامی که داده ها را در دو ستون قرار دادید، آن ستون ها را انتخاب کنید و یک نام برای محدوده انتخاب شده ایجاد کنید. (نحوه ایجاد یک محدوده نامگذاری شده در شماره های دیگر ExcelTips توضیح داده شده است. ) برای مثال، ممکن است محدوده را چیزی شبیه StateCodes نامگذاری کنید. این نامگذاری، اگرچه به شدت ضروری نیست، استفاده از فرمول جستجو را آسان تر می کند.
با فرض اینکه شماره تلفن در سلول A1 است و می خواهید نام وضعیت در ستون کنار شماره تلفن بازگردانده شود، در سلول B1 باید وارد کنید:
=VLOOKUP(VALUE(LEFT(A1,3)),StateCodes,2,FALSE)
توابع VALUE و LEFT فقط برای بیرون کشیدن سه کاراکتر اول از شماره تلفن استفاده می شوند. سپس در فرمول VLOOKUP برای یافتن کد منطقه در جدول StateCodes استفاده می شود. اکسل نام ایالتی را برمی گرداند که با کد منطقه مطابقت دارد.
روش دیگری که می توانید کد منطقه را بیرون بیاورید (که برای جستجو ضروری است) استفاده از تابع FLOOR است، همانطور که در اینجا نشان داده شده است:
=VLOOKUP(FLOOR(A1/10000000,1),StateCodes,2,FALSE)
توجه داشته باشید که این روش مستلزم آن است که شماره تلفن به عنوان یک عدد ذخیره شود، بنابراین می توان آن را بر 10,000,000 تقسیم کرد.
رویکردهایی که در اینجا مورد بحث قرار می‌گیرند عالی عمل می‌کنند، مشروط بر اینکه شماره تلفن شما همیشه در قالب مشخص شده باشد (3035551212). اگر شماره تلفن شما فرمت متفاوتی دارد - شاید از پرانتز و خط تیره استفاده کند - این فرمول کار نمی کند و باید تنظیم شود تا مشخص شود کد منطقه واقعاً در کجای شماره تلفن است. اگر شماره‌های تلفنی دارید که در یک قالب واحد نیستند، همه شرط‌بندی‌ها غیرفعال هستند و کار جستجو بسیار بسیار سخت‌تر می‌شود.
