Sưu tầm: gỡ rối khi dùng hàm VLOOKUP, MATCH làm thế nào xác định cell là kiểu text hoặc value


Chuyển đổi qua lại giữa các kiểu dữ liệu Text và Number
A. Chuyển dữ liệu Text thành Number:
1/ Giới thiệu về Text :
Excel phân biệt dữ liệu kiểu số và dữ liệu kiểu Text, mặc dù nhìn chúng có vẻ giống nhau.
Khi bạn nhập dữ liệu vào một Cell, Excel thực thi ngay lập tức và quyết định xem bạn đang nhập 1 công thức, một số (kể cả ngày tháng hoặc thời gian) hoặc bất cứ thứ gì khác. Bất cứ những gì khác đó được xem là Text. Một Cel có thể chứa đến 32.000 ký tự
Nếu bạn muốn một số đ ược xem là Text, bạn có thể thực hiện các điều sau đây :
– Sử dụng Format / Cells, nhấp Tab Number v à chọn Text từ danh sách Categories. Nếu bạn không áp dụng định dạng canh l ề tr ái ph ải, thì dạng Text sẽ xu ất hiện cạnh trái của Cell.
– Đặt một dấu apostrophe (‘) trước s ố. Nếu dấu (‘) không hiển thị, nhưng mục nhập trong ô sẽ đ ược xem như là Text

 

B. Chuyển dữ liệu Number thành Text :
1. Khi bạn có một Data bao gồm d ữ liệu kiểu Text và kiểu Number như hình 319, một cách thông thường để bạn chuyển đổi những dữ liệu kiểu số thành Text là :

– Vào Format Cell sẽ có hộp thoại như hình 320
– Trong hộp Category, chọn Text
– Các dữ liệu kiểu Number sẽ chuyển thành Text. Tuy vậy, chúng vẫn khác với những dữ liệu kiểu Text đã được định dạng từ trước. Khi bạn chuyển dãy dữ liệu kiểu Text vừa Format xong trở lại kiểu Number, thì những ô Text ban đầu vẫn giữ nguyên kiểu dữ liệu Text không thay đổi.

2. Bạn có thể ch ọn Cell chứa Text . Nhấn F2 để thực hiện chế độ Edit Cell . Khi nhấn F2 xong, bạn se thấy con trỏ n ằm ngay trong Cell. Nh ấn phím Home để đưa vị trí con trỏ về vị trí bắt đầu của Cell. Gõ dấu (‘) để chuyển dữ liệu số thành Text. Xong, nh ấn Enter để di chuyểnsang các Cel bên cạnh. Tuy nhiên, việc làm này không được sử dụng nếu phải thực hiện trên nhiều Cell cần chuyển đổi.

3. Đối với cột có quá nhiều dữ liệu kiểu Text trong đó có cả Value thì có thể dùng Text to Column là hay nhất:

Chọn tất cả Data trong cột. Từ Menu, chọn Data – Text to Column. Bước đầu tiên của hộp thoại Wizard, bạn chọn Fixed Width như hình vẽ:

image

image

Bước 2, có thể có đường vẽ dọc trong Data Preview như hình 322 . Nếu có, bạn có thể bỏ nó đi bằng cách Double Click vào đường kẻ này.

Bước 3 của Wizard, tại Column Data Format, bạn chọn Text như hình 323

Sau khi bạn kết thúc bằng nhấn Finish, tất cả dữ liệu trong cột sẽ được chuyển thành dạng Text
Bạn không thể sử dụng nút AZ để Sort dữ liệu trong trường hợp này, bạn phải vào Menu Data – Sort. Sauk hi bạn nhấp OK, sẽ có hộp cảnh báo như hình 324. Bạn chọn Sort numbers and numbers stored as text separately.

 

Tổng kết sơ bộ:

– Các cách làm trên đều không thoả mãn thực tế của vấn đề chuyển giữa Text sang number số hoặc ngược lại, đơn giản vì Microsoft Excel luôn ngầm hiểu Text chứa Number chứ number không chứa text.

– Trong Text chứa number bằng cách của Excel là chứa phân định bằng đánh dấu ký tự ‘ trước các con số.

– Theo cách của tôi muốn triệt tiêu vấn đề, nên áp dụng hàm Excel để phân định, xác định, ép kiểu hay nhận dạng ra các giái trị nào là Text, giá trị nào là Số thì mới là cách toàn vẹn, đúng đắn nhất.

Cách giải quyết của tôi:

Ví dụ: cho 2 bảng tính “Bảng Bán hàng” để tìm ra số tồn ở “bảng Tồn”, cần dùng hàm VLOOKUP theo mã Hàng hoá để tìm ra các số liệu đã bán rồi mới tính tồn theo cột Nhập hàng trong bảng tồn.

image

Bảng bán hàng, có mã hàng là kiểu Text.

image

Bảng tồn, có mã hàng là kiểu text, sử dụng hàm VLOOKUP để tìm mã hàng trong bảng bán hàng để tính ra hàng tồn.

Lỗi: cứ các mã hàng có kiểu Text “ví dụ: 0000006 … 090145 thì tìm ra đúng kết quả, còn từ 100000 … 999999 thì không tìm ra kết quả, chỉ báo là #N/A: không có giá”

Sau 1 giờ đồng hồ mò mẫm tìm hiểu, tôi hiểu ra là vì Excel định dạng Text cho các giá trị có đầu bằng chữ số 0, còn các số có đầu chữ số 1 ..9 thì nó sẽ hiểu là Value có giá trị số, còn cell vẫn định nghĩa là Text giúp cho người dùng Search theo kiểu text, còn Match, vlookup / hlookup là kiểu so sánh giá trị thì không thể áp dụng cách trên được.

Bước thực hiện:

Bước 1.

Tôi dùng hàm If để xác định vị trí ký tự đầu tiên bên phải của Cell là số không “0” thì sẽ ép “xác định” được cell đó là Text, nếu bên trái là khác không “0” thì sẽ ép “xác định” được cho cell đó là kiểu Value.

ví dụ: IF(LEFT(A2,1) =”0″,TEXT(A2,”000000″),VALUE(A2))

Bước 2.

Sau khi ép “xác định” được kiểu của Cell, chúng ta sẽ đơn giản đưa hàm đó vào trong hàm Vlookup là coi như mọi việc trở lại bình thường.

ví dụ: =VLOOKUP(IF(LEFT(A2,1) =”0″,TEXT(A2,”000000″),VALUE(A2)),’TH Ban’!$A$2:$G$8130,5,FALSE)

image

 

Trân trọng cảm ơn các bạn đã post bài tham khảo trên GiaiphapExcel.com để tôi và nhiều người tham khảo, tìm hiểu ra cái được, cái chưa được.

Tôi hy vọng bài này sẽ thể giúp các bạn vượt qua được cái khó khi sử dụng Excel và các kỹ năng xử lý trong kinh tế, thống kê.

 

About thangletoan

Hallo Aloha

Posted on 17/09/2012, in Công nghệ và Giáo dục, Chính sách CNTT, Microsoft Excel, Microsoft Excel và thống kê, Sống và đam mê khoa học, Tài chính kế toán bằng Excel, Thống kê và thuật toán. Bookmark the permalink. Để lại bình luận.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: