Các câu hỏi về hàm dò tìm dữ liệu (Lookup, Vlookup, Hlookup...)

[ducdien]

Cảm Cảm ơn anh

En đang có ý định từ phiếu kho chuyển thành phiếu bán hàng nên en định làm vậy , anh gúp em nhé!

 

[Po_Pikachu]

Do dữ liệu đầu vào của bạn không giống với cột "So CT" nên bạn phải làm thêm 1 cột phụ để trích dữ liệu cho chính xác với yêu cầu hơn. Bạn quét chọn cột D:F rồi bấm phải chuột chọn Unhide sẽ thấy công thức trong cột ẩn ấy! Bạn xem file!

 

[ndu96081631]

En đang có ý định từ phiếu kho chuyển thành phiếu bán hàng nên en định làm vậy , anh gúp em nhé!

Vậy thì dùng OFFSET không thôi, cần gì VLOOKUP
Bạn dùng công thức:

=OFFSET($E$3,MATCH($G$3,$B$4:$B$17,0)+ số dòng,)

Nếu muốn dịch xuống dưới 1, 2 hoặc 3 dòng thì thay số dòng này = 1, 2 hoặc 3
Nếu bạn bỏ trống số dòng (tức số dòng = 0) thì xem như là tìm chính xác (giống như VLOOKUP)

 

[Po_Pikachu]

Nếu muốn xác định vị trí không thì đâu cần xác định Match làm gì. Mình chỉ dùng hàm Offset không cũng được vậy!

=OFFSET($E$4,$G$3+ROW()-7,0)

Chỉ có điều là ô G3 phải biết chính xác theo thứ tự cột "So CT". Thân.

 

[boyxin]

En đang có ý định từ phiếu kho chuyển thành phiếu bán hàng nên en định làm vậy , anh gúp em nhé!

Nhập công thức cho ô

G5 =OFFSET(E1,MATCH($G$3,$B:$B,0)-1,)

Thích lấy xuống bao nhiêu hàng thì copy (kéo fill) xuống cho đủ

 

[ducdien]

vkookup

các bác gúp hộ Em dính kèm

 

[ndu96081631]

Nếu muốn xác định vị trí không thì đâu cần xác định Match làm gì. Mình chỉ dùng hàm Offset không cũng được vậy!

=OFFSET($E$4,$G$3+ROW()-7,0)

Chỉ có điều là ô G3 phải biết chính xác theo thứ tự cột "So CT".
Thân.

Đâu có được! Công thức này chỉ đúng trong bài cụ thể này thôi... vì may mắn số thứ tự trong cột B đã được sắp xếp đúng vị trí... Nếu số 004 không nằm ở dòng 5, số 007 không nằm ở dòng 8... vân... vân thì công thức... Tèo ---> Cuối cùng vẩn phải MATCH là chắc ăn
Xem lại file mới nhất ở bài #9 thì thấy bạn đã đi trật hướng rồi... Đây là bài toán trích dử liệu theo điều kiện... Dạng này có rất nhiều trên diển đàn...
Đại khái người ta sẽ dùng name động để xác định vùng dử liệu, sau đó sẽ dùng INDEX trích ra từng Record
---------------------
Làm cho bạn luôn đây (chưa hay lắm nhưng có thể tham khảo)

 

[boyxin]

các bác gúp hộ Em dính kèm

Góp thêm 2 cách (OFFSET & INDIRECT)​

 

[SonMaritime]

Chào các bạn
Mình gặp phải một bài toán tính hệ số lương như sau:
Nếu Doanh số từ 20 SP trở xuống thì Hệ số là 1.0
Nếu Doanh số từ trên 20 đến 40 SP thì Hệ số là 1.1
Nếu Doanh số từ trên 40 đến 60 SP thì Hệ số là 1.2
Nếu Doanh số từ trên 60 đến 80 SP thì Hệ số là 1.3
Nếu Doanh số từ trên 80 đến 100 SP thì Hệ số là 1.4
Nếu Doanh số từ trên 100 đến 120 SP thì Hệ số là 1.5
Nếu Doanh số từ trên 120 đến 140 SP thì Hệ số là 1.6
Nếu Doanh số từ trên 140 đến 160 SP thì Hệ số là 1.7
Nếu Doanh số trên 160 SP thì Hệ số là 1.8
(Máy bị lỗi không up được file đính kèm)

Để giải quyết mình đã tạo một bảng hệ số theo yêu cầu trên và dùng hàm Vlookup để tính hệ số lương đạt được, tuy nhiên hàm VLookup chỉ trả về kết quả trong trường hợp lớn hơn hoặc bằng. Vậy các bạn có thể chỉ giáo giúp trường hợp này nên dùng hàm gì để trả về kết quả tìm kiếm nhỏ hơn hoặc bằng không.

 

[Po_Pikachu]

Mình không chắc từ "từ" của bạn nên mình cho bạn 2 cái. Bạn cho cái nào =VALUE(1&"."&INT((A1-0.1)/20)) Cái này thì với A1 = 60 thì trả về 1.2 =VALUE(1&"."&INT(A1/20)) Cái này thì là 1.3. Thân.

 

[SonMaritime]

Chào các bạn
Mình gặp phải một bài toán tính hệ số lương như sau:
Nếu Doanh số từ 20 SP trở xuống thì Hệ số là 1.0
Nếu Doanh số từ trên 20 đến 40 SP thì Hệ số là 1.1
Nếu Doanh số từ trên 40 đến 60 SP thì Hệ số là 1.2
Nếu Doanh số từ trên 60 đến 80 SP thì Hệ số là 1.3
Nếu Doanh số từ trên 80 đến 100 SP thì Hệ số là 1.4
Nếu Doanh số từ trên 100 đến 120 SP thì Hệ số là 1.5
Nếu Doanh số từ trên 120 đến 140 SP thì Hệ số là 1.6
Nếu Doanh số từ trên 140 đến 160 SP thì Hệ số là 1.7
Nếu Doanh số trên 160 SP thì Hệ số là 1.8
(Máy bị lỗi không up được file đính kèm)

Vì không Up được file nên diễn giải tương đối khó
Mục đích là căn cứ vào doanh số hàng tháng hệ số lương sẽ tự động cập nhật
Ngoài ra khi bảng hệ số lương hàng tháng được điều chỉnh sẽ không cần phải lập lại công thức

Ví dụ:
Tháng này thông báo bảng HS lương như trên.
(Trong bảng chỉ ghi 20 thì hiểu là đến 20 SP, ghi 40 là từ trên 20 đến 40...)

Nguyễn Văn A có DSố lả 19 SP => HS lương: 1.0
Nguyễn Văn A có DSố lả 20 SP => HS lương: 1.0
Nguyễn Văn A có DSố lả 21 SP => HS lương: 1.1
......

Rất mong các bạn giúp đỡ

 

[SonMaritime]

Mình không chắc từ "từ" của bạn nên mình cho bạn 2 cái. Bạn cho cái nào
=VALUE(1&"."&INT((A1-0.1)/20))
Cái này thì với A1 = 60 thì trả về 1.2
=VALUE(1&"."&INT(A1/20))
Cái này thì là 1.3.
Thân.

Mình cảm ơn bạn Pikachu đã hướng dẫn, theo như bài toán của mình thì cách 1 có đáp án chính xác hơn.
Tuy nhiên khi A1>180 thì KQ ko còn chính xác nữa, mặt khác bảng hệ số lại thay đổi và được thông báo theo từng thời điểm kinh doanh.
Vì vậy mình rất mong có cách nào chỉ phải thay đổi thông tin trong bảng hệ mà không phải sửa lại công thức là giải pháp tốt nhất

 

[yeudoi]

Cái này bạn có thể dùng hàm sumproduct cũng được. VD: =SUMPRODUCT(--(C15>B3:B11),--(C15<=C3:C11),--(D311)).

 

[BNTT]

Chào các bạn
Mình gặp phải một bài toán tính hệ số lương như sau:
Nếu Doanh số từ 20 SP trở xuống thì Hệ số là 1.0
Nếu Doanh số từ trên 20 đến 40 SP thì Hệ số là 1.1
Nếu Doanh số từ trên 40 đến 60 SP thì Hệ số là 1.2
Nếu Doanh số từ trên 60 đến 80 SP thì Hệ số là 1.3
Nếu Doanh số từ trên 80 đến 100 SP thì Hệ số là 1.4
Nếu Doanh số từ trên 100 đến 120 SP thì Hệ số là 1.5
Nếu Doanh số từ trên 120 đến 140 SP thì Hệ số là 1.6
Nếu Doanh số từ trên 140 đến 160 SP thì Hệ số là 1.7
Nếu Doanh số trên 160 SP thì Hệ số là 1.8
(Máy bị lỗi không up được file đính kèm)

Để giải quyết mình đã tạo một bảng hệ số theo yêu cầu trên và dùng hàm Vlookup để tính hệ số lương đạt được, tuy nhiên hàm VLookup chỉ trả về kết quả trong trường hợp lớn hơn hoặc bằng. Vậy các bạn có thể chỉ giáo giúp trường hợp này nên dùng hàm gì để trả về kết quả tìm kiếm nhỏ hơn hoặc bằng không.

Bạn nhầm rồi. VLOOKUP, nếu không dùng tham số thứ 4, thì trả về giá trị lớn nhất mà nhỏ hơn giá trị cần tìm, chỉ với một điều kiện là danh sách dò tìm của bạn phải được sắp xếp từ nhỏ đến lớn.

Do đó, bài toán của bạn hoàn toàn có thể sử dụng VLOOKUP, với một chút "mẹo".

Bạn xem bảng sau:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

1​

​

|

SP​

​

|

HS​

​

|

​

​

|

SP​

​

|

HS​

​

|

2​

​

|

0​

|

1​

| |

19​

|

1​

|

3​

​

|

20​

|

1.1​

| |

20​

|

1​

|

4​

​

|

40​

|

1.2​

| |

151​

|

1.7​

|

5​

​

|

60​

|

1.3​

| |

40​

|

1.1​

|

6​

​

|

80​

|

1.4​

| |

4​

|

1​

|

7​

​

|

100​

|

1.5​

| |

2​

|

1​

|

8​

​

|

120​

|

1.6​

| |

167​

|

1.8​

|

9​

​

|

140​

|

1.7​

| |

60​

|

1.2​

|

10​

​

|

160​

|

1.8​

| |

125​

|

1.6​

|

11​

​

| | | |

141​

|

1.7​

|

12​

​

| | | |

5​

|

1​

|

13​

​

| | | |

35​

|

1.1​

|

14​

​

| | | |

189​

|

1.8​

|

15​

​

| | | |

170​

|

1.8​

|

16​

​

| | | |

72​

|

1.3​

|

17​

​

| | | |

93​

|

1.4​

|

18​

​

| | | |

131​

|

1.6​

|

19​

​

| | | |

175​

|

1.8​

|

20​

​

| | | |

190​

|

1.8​

|

Khối cell A1:B10 là bảng dò tìm, tôi đặt tên cho nó là Heso

Tại cột E, ô E2 chẳng hạn, nếu tôi dùng công thức:

=VLOOKUP(D2, Heso, 2)​

Thì kết quả sẽ cho ra hệ số tương ứng với mức sản phẩm: từ 0 đến 19, từ 20 đến 39, từ 40 đến 59...

Tuy nhiên, vì bạn muốn là: từ 0 đến 20, từ 21 đến 40, từ 41 đến 60... Do đó, như ở trên tôi có nói cái "mẹo", tức là bạn đem số sản phẩm cần tìm hệ số trừ bớt đi 1, như công thức sau đây (ở E2):

=VLOOKUP(D2-1, Heso, 2)​

Bạn xem thử, ứng với mỗi sản phẩm bên cột D, thì hệ số tương ứng của nó bên cột E có đúng ý bạn không ?

 

[SonMaritime]

Bạn nhầm rồi. VLOOKUP, nếu không dùng tham số thứ 4, thì trả về giá trị lớn nhất mà nhỏ hơn giá trị cần tìm, chỉ với một điều kiện là danh sách dò tìm của bạn phải được sắp xếp từ nhỏ đến lớn.

Do đó, bài toán của bạn hoàn toàn có thể sử dụng VLOOKUP, với một chút "mẹo".

Bạn xem bảng sau:

|

A​

|

B​

|

C​

|

D​

|

E​

|

1​

|

SP​

|

HS​

||

SP​

|

HS​

|

2​

|

0​

|

1​

| |

19​

|

1​

|

3​

|

20​

|

1.1​

| |

20​

|

1​

|

4​

|

40​

|

1.2​

| |

151​

|

1.7​

|

5​

|

60​

|

1.3​

| |

40​

|

1.1​

|

6​

|

80​

|

1.4​

| |

4​

|

1​

|

7​

|

100​

|

1.5​

| |

2​

|

1​

|

8​

|

120​

|

1.6​

| |

167​

|

1.8​

|

9​

|

140​

|

1.7​

| |

60​

|

1.2​

|

10​

|

160​

|

1.8​

| |

125​

|

1.6​

|

11​

| | | |

141​

|

1.7​

|

12​

| | | |

5​

|

1​

|

13​

| | | |

35​

|

1.1​

|

14​

| | | |

189​

|

1.8​

|

15​

| | | |

170​

|

1.8​

|

16​

| | | |

72​

|

1.3​

|

17​

| | | |

93​

|

1.4​

|

18​

| | | |

131​

|

1.6​

|

19​

| | | |

175​

|

1.8​

|

20​

| | | |

190​

|

1.8​

|

Khối cell A1:B10 là bảng dò tìm, tôi đặt tên cho nó là Heso

Tại cột E, ô E2 chẳng hạn, nếu tôi dùng công thức:

=VLOOKUP(D2, Heso, 2)​

Thì kết quả sẽ cho ra hệ số tương ứng với mức sản phẩm: từ 0 đến 19, từ 20 đến 39, từ 40 đến 59...

Tuy nhiên, vì bạn muốn là: từ 0 đến 20, từ 21 đến 40, từ 41 đến 60... Do đó, như ở trên tôi có nói cái "mẹo", tức là bạn đem số sản phẩm cần tìm hệ số trừ bớt đi 1, như công thức sau đây (ở E2):

=VLOOKUP(D2-1, Heso, 2)​

Bạn xem thử, ứng với mỗi sản phẩm bên cột D, thì hệ số tương ứng của nó bên cột E có đúng ý bạn không ?

Cảm ơn bạn BNTT
Cách của bạn rất hay, mình đã giải quyết được yêu cầu của bài toán. Trong trường hợp của mình thì công thức sửa lại một chút:

=VLOOKUP(D2-0.01, Heso, 2)

Tuy nhiên với bảng hệ số lương cũng phải chỉnh sửa lại để có kq theo yêu cầu:

Thay vì bảng hệ số được giao là
SP HS
20 1.0
40 1.1
60 1.2
80 1.3
100 1.4
120 1.5
140 1.6
160 1.7
>160 1.8

Thì phải sửa thành
SP HS
0 1.0
20 1.1
40 1.2
60 1.3
80 1.4
100 1.5
120 1.6
140 1.7
160 1.8

Có thể áp dụng hàm nào để tận dụng được ngay bảng được giao chỉ phải chỉnh sửa tối thiểu không

P/S: Tại sao mình up file toàn bị báo lỗi nhỉ
Mình muốn post bảng như của bạn BNTT lên thì làm như thế nào

 

[Po_Pikachu]

Nếu vậy bạn chỉ cần sử dụng thêm 1 lệnh IF để cho các số > 160 chỉ có 1 đáp án thôi! Vậy sữa lại như vầy: =IF(A1>160,1.8,VALUE(1&"."&INT(A1/20))) Tại vì chúng có chu trình tuần hoàn mà, và cũng đở phải lặp bảng nữa! Thân.

 

[SonMaritime]

Cái này bạn có thể dùng hàm sumproduct cũng được. VD: =SUMPRODUCT(--(C15>B3:B11),--(C15<=C3:C11),--(D311)).

Cảm ơn bạn, nhưng hàm này sử dụng sao đây bạn.
Vì mới làm excel nên còn nhiều bỡ ngỡ, Mình không hiểu dấu -- này có tác dụng gì nhỉ
Bạn hướng dẫn giúp mình với

 

[hoang_B]

Bạn xem ở đây nha!
http://www.giaiphapexcel.com/forum/showthread.php?t=7174

 

[cadafi]

Cảm ơn bạn, nhưng hàm này sử dụng sao đây bạn.
Vì mới làm excel nên còn nhiều bỡ ngỡ, Mình không hiểu dấu -- này có tác dụng gì nhỉ
Bạn hướng dẫn giúp mình với

Bạn thử mở file excel bất kỳ lên, bạn gõ lần lượt như sau sẽ rõ:

A1 = -True
A2 = --True

Bạn xem thêm tại đây nhé!
http://www.giaiphapexcel.com/forum/showpost.php?p=78602&postcount=7

 

[doantrang57]

Hỏi về hàm vlookup

Các bác cho em hỏi giùm. Em dùng hàm vlookup nhưng nhiều lúc trả về giá trị sai là nguyên nhân vì sao? có cách nào khắc phục ko?