Xin trợ giúp hàm tham chiếu ngược

[thaocnc]

Mình cần điền mục đơn giá ở cột F dựa theo mã hợp đồng cột B và vùng dữ liệu ở bên dưới
Bạn nào biết cách làm xin hướng dẫn giúp mình.
Cấu trúc của bảng xin giữ nguyên không thay
Cảm ơn các bạn

 

[VetMini]

Làm thì không khó nhưng với điều kiện của bạn "Cấu trúc của bảng xin giữ nguyên không thay" thì rất rườm rà
Nhất là cái vụ merged cells và cái vụ bảng tra nằm dưới bảng dữ liệu chính. Nó là hai đầu mối chính để dẫn đến truy cập sai chỗ, và mỗi lần thêm bớt dữ liệu thì lại tùm lum.

Chỉ dẫn:
2 ký tự đầu là quốc gia. Hàm Left (lấy 2).
8 ký tự cuối là ngày. Hàm Right (lấy 8). Nếu muốn đổi sang ngày thật thì phải dùng hàm Mid và DateValue
2 ký tự đầu của 10 ký tự cuối là loại hàng. Hàm Right (lấy 10) và Left (lấy 2).
Trừ mấy cái trên thì còn lại số lượng hàng. Hàm Mid (lấy kể từ 3, cho đến độ dài -10)

 

[thukhon2014]

View attachment 229310
Mình cần điền mục đơn giá ở cột F dựa theo mã hợp đồng cột B và vùng dữ liệu ở bên dưới
Bạn nào biết cách làm xin hướng dẫn giúp mình.
Cấu trúc của bảng xin giữ nguyên không thay
Cảm ơn các bạn

Bạn thử công thức này, điều kiện là số ngày bạn nhập phải là 8 số (01012020 chẳng hạn).

=INDEX($C$15:$E$17,IF(MID(B3,LEN(B3)-9,2)="LO",1,IF(MID(B3,LEN(B3)-9,2)="CH",2,IF(MID(B3,LEN(B3)-9,2)="BP",3))),IF(LEFT(B3,2)="VN",1,IF(LEFT(B3,2)="TQ",2,IF(LEFT(B3,2)="MA",3,))))*21500

Bên lề: làm bài này, format của nó làm mình nhớ về thời đi thi học kì Excel của Thầy cô !

 

[thukhon2014]

Làm thì không khó nhưng với điều kiện của bạn "Cấu trúc của bảng xin giữ nguyên không thay" thì rất rườm rà
Nhất là cái vụ merged cells và cái vụ bảng tra nằm dưới bảng dữ liệu chính. Nó là hai đầu mối chính để dẫn đến truy cập sai chỗ, và mỗi lần thêm bớt dữ liệu thì lại tùm lum.

Chỉ dẫn:
2 ký tự đầu là quốc gia. Hàm Left (lấy 2).
8 ký tự cuối là ngày. Hàm Right (lấy 8). Nếu muốn đổi sang ngày thật thì phải dùng hàm Mid và DateValue
2 ký tự đầu của 10 ký tự cuối là loại hàng. Hàm Right (lấy 10) và Left (lấy 2).
Trừ mấy cái trên thì còn lại số lượng hàng. Hàm Mid (lấy kể từ 3, cho đến độ dài -10)

Mình nghĩ đây giống như đề kiểm tra ấy, không phải bài thực tế số liệu công ty nên mình cũng không nên khó quá !

 

[thaocnc]

Bạn thử công thức này, điều kiện là số ngày bạn nhập phải là 8 số (01012020 chẳng hạn).

=INDEX($C$15:$E$17,IF(MID(B3,LEN(B3)-9,2)="LO",1,IF(MID(B3,LEN(B3)-9,2)="CH",2,IF(MID(B3,LEN(B3)-9,2)="BP",3))),IF(LEFT(B3,2)="VN",1,IF(LEFT(B3,2)="TQ",2,IF(LEFT(B3,2)="MA",3,))))*21500

Bên lề: làm bài này, format của nó làm mình nhớ về thời đi thi học kì Excel của Thầy cô !

Cảm ơn bạn
Hướng dẫn của bạn áp dụng cho bài này mình thấy được rồi nhưng có vấn đề là nếu như có nhiều mã hàng và mã nước thì công thức sẽ rất dài.
Bạn xem giúp có phương án nào tốt hơn không.ẻ
Lưu ý: Cấu trúc của 2 bảng không được thay đổi nhưng vị trí bảng đơn giá có thể tùy ý không nhất thiết phải ở dưới,có thể đưa sang sheet khác

Bài đã được tự động gộp: 1/12/19

Mình nghĩ đây giống như đề kiểm tra ấy, không phải bài thực tế số liệu công ty nên mình cũng không nên khó quá !

Đúng là bài kiểm tra bạn ạ nên cấu trúc bảng của thầy không được thay đổi.
Mình thấy ở bảng đơn giá cột mã hàng lại nằm ngoài cùng bên phải nên mình nghĩ phải dùng tham chiếu ngược,nhưng mình không biết phải làm như thế nào

 

[thukhon2014]

Cảm ơn bạn
Hướng dẫn của bạn áp dụng cho bài này mình thấy được rồi nhưng có vấn đề là nếu như có nhiều mã hàng và mã nước thì công thức sẽ rất dài.
Bạn xem giúp có phương án nào tốt hơn không.ẻ
Lưu ý: Cấu trúc của 2 bảng không được thay đổi nhưng vị trí bảng đơn giá có thể tùy ý không nhất thiết phải ở dưới,có thể đưa sang sheet khác

Bài đã được tự động gộp: 1/12/19

Đúng là bài kiểm tra bạn ạ nên cấu trúc bảng của thầy không được thay đổi

Nếu nhiều mã hàng và mã nước thì bắt buộc trong bài này phải có một bảng tra mã hàng, trong bài mình chưa thấy bảng để tra mã hàng. Còn về mã nước trong bảng hiện tại thì mình dùng hàm Match sẽ gọn hơn và tương tự với mã hàng nếu có bảng tra mã hàng, như thế này tại ô F3

=INDEX($C$15:$E$17,IF(MID(B3,LEN(B3)-9,2)="LO",1,IF(MID(B3,LEN(B3)-9,2)="CH",2,IF(MID(B3,LEN(B3)-9,2)="BP",3))),MATCH(LEFT(B3,2),$C$13:$E$13,0))*21500

 

[thaocnc]

Có mã hàng ở bên phải của bảng đơn giá đó bạn.
Vấn đề là cái cột mã hàng nó lại nằm bên phải nên mình không biết làm

 

[VetMini]

...
Đúng là bài kiểm tra bạn ạ nên cấu trúc bảng của thầy không được thay đổi.
Mình thấy ở bảng đơn giá cột mã hàng lại nằm ngoài cùng bên phải nên mình nghĩ phải dùng tham chiếu ngược,nhưng mình không biết phải làm như thế nào

Nhìn cái cấu túc bảng tra và lối ngôn ngữ tiền Mẽo là biết nó không phải là đồ thực tế.
Thầy/Cô kém kinh nghiệm thực tế mới có chuyện đó. Cái từ "rất rườm rà" là tôi muốn phê bình Thầy/Cô của bạn.

Bạn nghĩ tham chiếu ngược là đúng rồi.
Muốn dùng VLOOKUP thì dùng thêm hàm CHOOSE. Hàm này có thể đổi vị trí cột.
VLOOKUP( trị, CHOOSE( {1, 2}, cột 1, cột 2), 2, 0 )

 

[Phuocam]

Trong yêu cầu của bài, không có dòng nào bắt buộc dùng VLOOKUP, nên có thể dùng:

=INDEX($C$15:$E$18,MATCH(LEFT(RIGHT(B3,10),2),$G$15:$G$18,0),MATCH(LEFT(B3,2),$C$13:$E$13,0))

 

[thaocnc]

=INDEX($B$15:$B$17,1,MATCH(LEFT(LEFT(RIGHT(B3,10),2),2),$G$15:$G$17,0))&" xuất xứ "&INDEX($C$14:$E$17,1,MATCH(LEFT(B3,2),$C$13:$E$13,0))&""
Mình dùng code này ở cột diễn giải nhưng khi kéo xuống các ô bên dưới thì báo lỗi,các bạn chỉ giúp mình nó lỗi chỗ nào với

 

[ndu96081631]

View attachment 229310
Mình cần điền mục đơn giá ở cột F dựa theo mã hợp đồng cột B và vùng dữ liệu ở bên dưới
Bạn nào biết cách làm xin hướng dẫn giúp mình.
Cấu trúc của bảng xin giữ nguyên không thay
Cảm ơn các bạn

Sang mấy sheet bên xem sao. Làm cả rồi còn hỏi gì nữa bạn?

 

[TaiKhiem]

View attachment 229310
Mình cần điền mục đơn giá ở cột F dựa theo mã hợp đồng cột B và vùng dữ liệu ở bên dưới
Bạn nào biết cách làm xin hướng dẫn giúp mình.
Cấu trúc của bảng xin giữ nguyên không thay
Cảm ơn các bạn