Steve có số điện thoại trong cột A, chẳng hạn như 3035551212, và muốn có thể xem ba chữ số đầu tiên (mã vùng) và trả về trong cột B, trạng thái mà mã vùng đó được liên kết. Anh ấy băn khoăn về cách tốt nhất để thực hiện điều này.

Excel có nhiều hàm giúp cuộc sống dễ dàng hơn khi bạn đang cố gắng thao tác dữ liệu. Trong trường hợp này, việc sử dụng hàm VLOOKUP để khớp mã vùng với trạng thái tương ứng rất đơn giản.

Trước khi áp dụng một hàm để truy xuất thông tin bạn muốn, bạn cần tạo một bảng dữ liệu đơn giản chứa dữ liệu bạn muốn truy xuất. Trong bảng này, bạn cần có mã vùng và trạng thái trong các cột riêng của chúng cạnh nhau, trong trang tính của bạn, được sắp xếp theo mã vùng. Ví dụ: bạn có thể đặt mã vùng trong cột F và trạng thái cho các mã vùng đó trong cột G. Mã vùng và trạng thái có thể được tìm thấy trên một số trang web hoặc bạn có thể tạo bảng của riêng mình nếu muốn.

Khi bạn đã có dữ liệu vào hai cột, hãy chọn các cột đó và tạo tên cho phạm vi đã chọn. (Cách bạn tạo một dải ô đã đặt tên đã được đề cập trong các vấn đề khác của ExcelTips.) Ví dụ: bạn có thể đặt tên cho dải ô giống như StateCodes. Việc đặt tên này, mặc dù không hoàn toàn cần thiết, nhưng làm cho việc sử dụng công thức tra cứu dễ dàng hơn.

Giả sử rằng số điện thoại nằm trong ô A1 và bạn muốn trả về tên tiểu bang trong cột bên cạnh số điện thoại, trong ô B1, bạn sẽ nhập:

=VLOOKUP(VALUE(LEFT(A1,3)),StateCodes,2,FALSE)

Hàm VALUE và LEFT được sử dụng để chỉ lấy ba ký tự đầu tiên từ số điện thoại. Điều này sau đó được sử dụng trong công thức VLOOKUP để tìm mã vùng trong bảng StateCodes. Excel trả về tên của trạng thái tương ứng với mã vùng.

Một cách khác mà bạn có thể lấy ra mã vùng (cần thiết cho việc tra cứu) là sử dụng hàm FLOOR, như được hiển thị ở đây:

=VLOOKUP(FLOOR(A1/10000000,1),StateCodes,2,FALSE)

Lưu ý rằng cách tiếp cận này yêu cầu số điện thoại phải được lưu trữ dưới dạng số, vì vậy nó có thể chia cho 10.000.000.

Các cách tiếp cận được thảo luận ở đây hoạt động hiệu quả, miễn là số điện thoại của bạn luôn ở định dạng được chỉ định (3035551212). Nếu số điện thoại của bạn có định dạng khác — có lẽ là định dạng sử dụng dấu ngoặc đơn và dấu gạch ngang — thì công thức sẽ không hoạt động và sẽ cần được điều chỉnh để xem mã vùng thực sự nằm ở đâu trong số điện thoại. Nếu bạn có số điện thoại không ở một định dạng duy nhất, thì tất cả các cược sẽ tắt và nhiệm vụ tra cứu trở nên khó khăn hơn rất nhiều.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (8065) ​​áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: