Xin trợ giúp hàm dò tìm giá trị

[nhunghr91]

Dear all,
Lại là em - nhunghr91 đây ạ
Em lại phiền mọi người giúp em công thức 1 xíu.
Điều em cần là trong sheet 4.BM_danh_gia khi đánh cấp độ vào thì sẽ tự động nhảy điểm cho tiêu chí đó.
Điểm của các tiêu chí thì nằm ở sheet 3_diem_yeu_to
Mọi người bớt chút thời gian giúp em 1 chút ạ.
Em cảm ơn

 

[Phan Thế Hiệp]

Dear all,
Lại là em - nhunghr91 đây ạ
Em lại phiền mọi người giúp em công thức 1 xíu.
Điều em cần là trong sheet 4.BM_danh_gia khi đánh cấp độ vào thì sẽ tự động nhảy điểm cho tiêu chí đó.
Điểm của các tiêu chí thì nằm ở sheet 3_diem_yeu_to
Mọi người bớt chút thời gian giúp em 1 chút ạ.
Em cảm ơn

Dữ liệu làm ví dụ mà xóa hết thì làm sao biết đúng sai!??
Thử:

E9=HLOOKUP($D9,'3.Diem_yeu_to'!$G$5:$N$32,MATCH(LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9),'3.Diem_yeu_to'!$D$5:$D$32,),)

Enter, fill xuống.
Xem file kèm.

Thân

 

[nhunghr91]

Dữ liệu làm ví dụ mà xóa hết thì làm sao biết đúng sai!??
Thử:

E9=HLOOKUP($D9,'3.Diem_yeu_to'!$G$5:$N$32,MATCH(LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9),'3.Diem_yeu_to'!$D$5:$D$32,),)

Enter, fill xuống.
Xem file kèm.

Thân

Dạ, Em cảm ơn anh Phan Thế Hiệp nhé . em nghĩ cả chiều qua mà không ra

Bài đã được tự động gộp: 18/9/18

Dữ liệu làm ví dụ mà xóa hết thì làm sao biết đúng sai!??
Thử:

E9=HLOOKUP($D9,'3.Diem_yeu_to'!$G$5:$N$32,MATCH(LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9),'3.Diem_yeu_to'!$D$5:$D$32,),)

Enter, fill xuống.
Xem file kèm.

Thân[/QUOT
Dạ không phải e xóa mà là phần trọng số link từ sheet thứ 2 sang sheet 3 thì còn phải chờ sếp chốt nên e cứ lập công thức trước ạ.
Em cảm ơn anh nhé.

 

[nhunghr91]

Dữ liệu làm ví dụ mà xóa hết thì làm sao biết đúng sai!??
Thử:

E9=HLOOKUP($D9,'3.Diem_yeu_to'!$G$5:$N$32,MATCH(LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9),'3.Diem_yeu_to'!$D$5:$D$32,),)

Enter, fill xuống.
Xem file kèm.

Thân

Anh Hiệp ơi,
Em giải thích cho em 1 chút từ hàm Lookup được không ạ? em chưa hiểu lắm

 

[Phan Thế Hiệp]

Anh Hiệp ơi,
Em giải thích cho em 1 chút từ hàm Lookup được không ạ? em chưa hiểu lắm

E9=HLOOKUP($D9,'3.Diem_yeu_to'!$G$5:$N$32,MATCH(LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9),'3.Diem_yeu_to'!$D$5:$D$32,),)

LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9): Nhằm lấy các mục 'Stt' mà nó thuộc về, để làm 'Giá trị tìm' cho hàm MATCH( 'Giá trị tìm', '3.Diem_yeu_to'!$D$5:$D$32,) . Xem ví dụ trong cột H file kèm.

Xem thêm giải thích về công năng của hàm Lookup(2,1/....) tại: https://www.giaiphapexcel.com/diend...ập-hàng-chân-thành-cám-ơn.123588/#post-773512

Thân

 

[nhunghr91]

E9=HLOOKUP($D9,'3.Diem_yeu_to'!$G$5:$N$32,MATCH(LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9),'3.Diem_yeu_to'!$D$5:$D$32,),)

LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9): Nhằm lấy các mục 'Stt' mà nó thuộc về, để làm 'Giá trị tìm' cho hàm MATCH( 'Giá trị tìm', '3.Diem_yeu_to'!$D$5:$D$32,) . Xem ví dụ trong cột H file kèm.

Xem thêm giải thích về công năng của hàm Lookup(2,1/....) tại: https://www.giaiphapexcel.com/diendan/threads/nhờ-các-anh-chị-lập-công-thức-tính-số-ngày-nhập-hàng-chân-thành-cám-ơn.123588/#post-773512

Thân

Dạ nhưng em không hiểu sao trong cú pháp hàm Lookup lại là Lookup(2, 1/Len(...)
2 : đóng vai trò gì? (Vì sao lookup_value lại = 2 ?)
1/len($B$8:$B9)>1 vì sao lookup_vector (cột) là thành phần này?
Em rất sorry nếu hơi chậm hiểu làm a khó chịu nhưng rất mong a giải đáp giúp em ạ (

 

[befaint]

@Chủ thớt:
1. Đọc hết và đọc kỹ nội quy: https://www.giaiphapexcel.com/diendan/threads/nội-quy-diễn-đàn-cập-nhật.76052/
Rồi sửa lại tiêu đề bài.

2. Không bao giờ viết tắt dù nửa chữ.
Không xen tiếng Anh và tiếng Việt lung tung (Dear all đia eo, Em rất sorry...). Nếu bạn đi ứng tuyển thì đã rớt ngay lập tức rồi.

 

[nhunghr91]

@Chủ thớt:
1. Đọc hết và đọc kỹ nội quy: https://www.giaiphapexcel.com/diendan/threads/nội-quy-diễn-đàn-cập-nhật.76052/
Rồi sửa lại tiêu đề bài.

2. Không bao giờ viết tắt dù nửa chữ.
Không xen tiếng Anh và tiếng Việt lung tung (Dear all đia eo, Em rất sorry...). Nếu bạn đi ứng tuyển thì đã rớt ngay lập tức rồi.

Dạ vâng, em sẽ nghiêm túc rút kinh nghiệm cho những lần sau ạ .

 

[Phan Thế Hiệp]

Dạ nhưng em không hiểu sao trong cú pháp hàm Lookup lại là Lookup(2, 1/Len(...)
2 : đóng vai trò gì? (Vì sao lookup_value lại = 2 ?)
1/len($B$8:$B9)>1 vì sao lookup_vector (cột) là thành phần này?
Em rất sorry nếu hơi chậm hiểu làm a khó chịu nhưng rất mong a giải đáp giúp em ạ (

Cú pháp: LOOKUP( 'Giá trị tìm' , 'Véctơ chỉ hướng', 'Giá trị trả về' ).
Vậy căn cứ vào dữ liệu của bạn nên công thức sẽ là =LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9), với:

1. 'Giá trị tìm' = 2
2. 'Véctơ chỉ hướng' = 1/ (LEN($B$8:$B9)>1)
3. 'Giá trị trả về' = $B$8:$B9

Giải thích:

• 'Véctơ chỉ hướng' = 1/ (LEN($B$8:$B9)>1): Trong cột B: 'Stt' của bạn từ dòng B8 đến B169 thì các đề mục lớn như "I.1, I.2,...VI.5" có chiều dài (đo bằng hàm LEN()) lớn hơn các 'Stt' của mục con như: "1, 2, 3, 4...", và các mục con này chỉ có 1 ký tự (tức <10, 10 có 2 ký tự), cho nên dùng LEN($B$8:$B9) để tính chiều dài của ô B8 và B9, dĩ nhiên ô B8="I.1"-->3 ký tự nên thỏa điều kiện >1, còn B9="1" nên không thỏa điều kiện. Tính chung lại, thì (LEN($B$8:$B9)>1) sẽ cho ra 1 Mảng dọc là {1;0} (Xem cột I8: I9 file đính kèm). Tương tự, khi công thức kéo xuống thêm 1 dòng nữa thì công thức sẽ chuyển thành (LEN($B$8:$B10)>1), nó lại tạo ra 1 Mảng {1;0;0}, và cứ tuần tự xuống dòng cho đến dòng 17, tức (LEN($B$8:$B17)>1), nó lại tạo ra 1 Mảng {1;0;0;0;0;0;0;0;0;1} (Xem cột Q8: Q17 file đính kèm). Bạn đã biết với biểu thức toán 1 chia 1 số nguyên dương bất kỳ sẽ nhỏ hơn hoặc bằng 1, và nếu chia 0 thì sẽ báo lỗi #DIV!0, cho nên: 1/(LEN($B$8:$B17)>1) sẽ tạo ra một Mảng như sau:
  • 1/(LEN($B$8:$B17)>1) = 1/ {1;0;0;0;0;0;0;0;0;1} = {1;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;1}
  • Với Véctơ chỉ hướng này, ta thấy nó báo chỉ có 2 giá trị =1 (màu xanh lá và đỏ) tại dòng đầu (B8) và dòng công thức đang tại (B17), để cung cấp cho Lookup(,, 'Giá trị trả về' ) quy tương ứng dòng trong 'Giá trị trả về' để lấy ra kết quả. (Xem cột W8: W17 file đính kèm)
• 'Giá trị tìm' = 2.
  • Như nói trên biểu thức 1/n sẽ nhỏ hơn hoặc bằng 1, nên 'Giá trị tìm' bạn có thể cho một số bất kỳ (là: 2, 3, 4, 5, 10, 100, 1Triệu...), miễn sao nó lớn hơn 1 là được. Thường ta chọn bằng 2.
  • Để dễ hiểu thêm tôi cho ví dụ: Tìm các tháng của cuối mỗi quý, ta làm như sau:
    • A1=8
    • B1=LOOKUP(A1,{1,4,7,10})+2= 9
    • Tức nó đem so giá trị 8 với khung {1,4,7,10} thì 8 nằm trong khoảng từ 7 đến 10, nên nó sẽ lấy giá trị 7, 7 là tháng đầu tiên của quý 3, cộng thêm 2 sẽ thành 9 chính là tháng cuối quý 3.
  • Với bài bạn hỏi thì 2 sẽ lớn hơn các giá trị 'Véctơ chỉ hướng', nên nó sẽ lấy đúng giá trị 1 trả về (tương tự như ví dụ trên: 8 lớn hơn 7, thì mang 7 trả về). Tuy nhiên, ở đây ta có 2 giá trị 1 (màu xanh lá: dòng 8; và đỏ: dòng 17) trong 'Véctơ chỉ hướng', và Lookup() sẽ lấy 'hướng' của giá trị thỏa sau cùng, tức như Mảng trên nó sẽ lấy 'hướng' giá trị 1 (màu đỏ), tức sẽ tìm xuống dòng 17 tương xứng trong 'Giá trị trả về' trong Vùng B8: B17, hay chính là ô B17= "I.2" (Xem kết quả của ô H17)

Thân

 

[nhunghr91]

Cú pháp: LOOKUP( 'Giá trị tìm' , 'Véctơ chỉ hướng', 'Giá trị trả về' ).
Vậy căn cứ vào dữ liệu của bạn nên công thức sẽ là =LOOKUP(2,1/(LEN($B$8:$B9)>1),$B$8:$B9), với:

1. 'Giá trị tìm' = 2
2. 'Véctơ chỉ hướng' = 1/ (LEN($B$8:$B9)>1)
3. 'Giá trị trả về' = $B$8:$B9

Giải thích:

• 'Véctơ chỉ hướng' = 1/ (LEN($B$8:$B9)>1): Trong cột B: 'Stt' của bạn từ dòng B8 đến B169 thì các đề mục lớn như "I.1, I.2,...VI.5" có chiều dài (đo bằng hàm LEN()) lớn hơn các 'Stt' của mục con như: "1, 2, 3, 4...", và các mục con này chỉ có 1 ký tự (tức <10, 10 có 2 ký tự), cho nên dùng LEN($B$8:$B9) để tính chiều dài của ô B8 và B9, dĩ nhiên ô B8="I.1"-->3 ký tự nên thỏa điều kiện >1, còn B9="1" nên không thỏa điều kiện. Tính chung lại, thì (LEN($B$8:$B9)>1) sẽ cho ra 1 Mảng dọc là {1;0} (Xem cột I8: I9 file đính kèm). Tương tự, khi công thức kéo xuống thêm 1 dòng nữa thì công thức sẽ chuyển thành (LEN($B$8:$B10)>1), nó lại tạo ra 1 Mảng {1;0;0}, và cứ tuần tự xuống dòng cho đến dòng 17, tức (LEN($B$8:$B17)>1), nó lại tạo ra 1 Mảng {1;0;0;0;0;0;0;0;0;1} (Xem cột Q8: Q17 file đính kèm). Bạn đã biết với biểu thức toán 1 chia 1 số nguyên dương bất kỳ sẽ nhỏ hơn hoặc bằng 1, và nếu chia 0 thì sẽ báo lỗi #DIV!0, cho nên: 1/(LEN($B$8:$B17)>1) sẽ tạo ra một Mảng như sau:
  • 1/(LEN($B$8:$B17)>1) = 1/ {1;0;0;0;0;0;0;0;0;1} = {1;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;1}
  • Với Véctơ chỉ hướng này, ta thấy nó báo chỉ có 2 giá trị =1 (màu xanh lá và đỏ) tại dòng đầu (B8) và dòng công thức đang tại (B17), để cung cấp cho Lookup(,, 'Giá trị trả về' ) quy tương ứng dòng trong 'Giá trị trả về' để lấy ra kết quả. (Xem cột W8: W17 file đính kèm)
• 'Giá trị tìm' = 2.
  • Như nói trên biểu thức 1/n sẽ nhỏ hơn hoặc bằng 1, nên 'Giá trị tìm' bạn có thể cho một số bất kỳ (là: 2, 3, 4, 5, 10, 100, 1Triệu...), miễn sao nó lớn hơn 1 là được. Thường ta chọn bằng 2.
  • Để dễ hiểu thêm tôi cho ví dụ: Tìm các tháng của cuối mỗi quý, ta làm như sau:
    • A1=8
    • B1=LOOKUP(A1,{1,4,7,10})+2= 9
    • Tức nó đem so giá trị 8 với khung {1,4,7,10} thì 8 nằm trong khoảng từ 7 đến 10, nên nó sẽ lấy giá trị 7, 7 là tháng đầu tiên của quý 3, cộng thêm 2 sẽ thành 9 chính là tháng cuối quý 3.
  • Với bài bạn hỏi thì 2 sẽ lớn hơn các giá trị 'Véctơ chỉ hướng', nên nó sẽ lấy đúng giá trị 1 trả về (tương tự như ví dụ trên: 8 lớn hơn 7, thì mang 7 trả về). Tuy nhiên, ở đây ta có 2 giá trị 1 (màu xanh lá: dòng 8; và đỏ: dòng 17) trong 'Véctơ chỉ hướng', và Lookup() sẽ lấy 'hướng' của giá trị thỏa sau cùng, tức như Mảng trên nó sẽ lấy 'hướng' giá trị 1 (màu đỏ), tức sẽ tìm xuống dòng 17 tương xứng trong 'Giá trị trả về' trong Vùng B8: B17, hay chính là ô B17= "I.2" (Xem kết quả của ô H17)

Thân

Em cảm ơn anh Phan Thế Hiệp về bài viết rất có tâm của anh .
Mấy hôm định vào diễn đàn để cảm ơn anh nhưng diễn đàn đang bảo trì nên hôm nay em mới vào được.
Nhờ có sự hỗ trợ của anh mà em đã hoàn thiện 1/3 Dự án em đang thực hiện