Các câu hỏi về hàm dò tìm dữ liệu (Lookup, Vlookup, Hlookup...) (1 người xem)

ducdien · 6/9/08

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 · 6/9/08

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 · 7/9/08

ducdien đã viết:
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 · 7/9/08

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 · 7/9/08

ducdien đã viết:
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 ô

PHP:

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 · 7/9/08

vkookup

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

ndu96081631 · 7/9/08

Po_Pikachu đã viết:
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)

Nhấp chuột vào đây để mở rộng...

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 · 7/9/08

ducdien đã viết:
các bác gúp hộ Em dính kèm

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

SonMaritime · 4/10/08

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 · 4/10/08

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 · 5/10/08

SonMaritime đã viết:
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 · 5/10/08

Po_Pikachu đã viết:
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 · 5/10/08

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

11)).

BNTT · 5/10/08

SonMaritime đã viết:
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 · 5/10/08

BNTT đã viết:
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 · 5/10/08

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 · 5/10/08

yeudoi đã viết:
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 · 5/10/08

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

cadafi · 5/10/08

SonMaritime đã viết:
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 · 7/10/08

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?

BNTT · 7/10/08

Bạn nêu một ví dụ về cái sai đó được không ?

leminhhai · 7/10/08

tôi nghĩ bạn nên để ý giá trị tuyệt đối hoặc tương đối, chứ khi đã đúng cấu trúc hàm thi không thê sai được và cũng có thể bạn đánh sai giá trị hàm.

ndu96081631 · 7/10/08

doantrang57 đã viết:
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?

Tôi đoán có lẽ bạn thiếu tham số thứ tư:
Cú pháp VLOOKUP là:

=VLOOKUP(Trị dò, bảng dò, cột dò, cách dò)

Bạn xem lại có phải bạn đã bỏ mất tham số thứ tư này không? Nếu đúng như thế thì việc dò sẽ là "Dò không chính xác"... Trong 1 danh sách không được sort trước sẽ cho kết quả khong đúng như ý muốn

Po_Pikachu · 7/10/08

Bạn thêm số 0 vào cuối dòng lệnh xem có chính xác không? Nếu không thì coi chừng trong mãng dữ liệu có ô trùng nhau đó!
=Vlookup(<ô giá tri>,$<mãng dữ liệu>,<số cột muốn lấy>,0)
Thân.

doantrang57 · 7/10/08

Nếu mảng dữ liệu trùng nhau có cách nào giải quyết ko? Bạn po_pikachu có nick chat ko mình gửi file cho bạn xem hộ mình. mình gửi file lên diễn đàn ko được

ndu96081631 · 7/10/08

doantrang57 đã viết:
Nếu mảng dữ liệu trùng nhau có cách nào giải quyết ko? Bạn po_pikachu có nick chat ko mình gửi file cho bạn xem hộ mình. mình gửi file lên diễn đàn ko được

Nếu gặp dử liệu trùng thì VLOOKUP sẽ tìm dòng đầu tiên mà nó tìm thấy... Vậy bạn muốn tìm cái gì đây?
Nói rõ hơn 1 chút nhé

sealand · 7/10/08

Bạn hỏi vậy thật khó, đến mình là người mà bảo chọn 1 trong 2 cô Hồng do bạn giới thiệu cũng chịu huống chi công thức. Để chính xác bạn phải sửa lại tiêu chuẩn dò thôi.

Người Đưa Tin · 7/10/08

doantrang57 đã viết:
Mình ko gửi file nên được có bác chỉ giáo giúp

Tôi đã nhắn tin nói bạn gởi file vào hộp thư của tôi để các anh chị xem trả lời cho bạn.
Mà bạn cứ nói vòng vo mất thời gian của anh chị quá. Nếu bạn thật sự có nhu cầu để được trợ giúp bạn hãy gởi file vào hộp thư tôi : leminhtri1956@yahoo.com

SonMaritime · 7/10/08

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 140 đến 160 SP thì Hệ số là 1.7
Nếu Doanh số trên 160 SP thì Hệ số là 1.8

Theo yêu cầu mới nhất, thay vì Hệ số lương được tính theo ở trên. Tháng này có sự thay đổi như sau:
Phần Doanh số 20 SP đầu tiên, Hệ số là 1.0
Phần Doanh từ 21 đến 40 SP, Hệ số là 1.1
Phần Doanh từ 41 đến 60 SP, Hệ số là 1.2
Phần Doanh từ 61 đến 80 SP, Hệ số là 1.3
Phần Doanh từ 81 đến 100 SP, Hệ số là 1.4
Phần Doanh từ 101 đến 120 SP, Hệ số là 1.5
Phần Doanh từ 121 đến 140 SP, Hệ số là 1.6
Phần Doanh từ 141 đến 160 SP, Hệ số là 1.7
Phần Doanh trên 161 SP, Hệ số là 1.8

Như vậy, Hệ số sẽ được tính như một số ví dụ sau:
DSo: 85 SP
HSL=(20*1.0)+(20*1.1)+(20*1.2)+(20*1.3)+(5*1.4)=99
DSo: 195 SP
HSL=(20*1.0)+(20*1.1)+(20*1.2)+(20*1.3)+(20*1.4)+(20*1.5)+(20*1.6)+(20*1.7)+(35*1.8)=279

Mình chưa tìm ra cách giải nào mà không dùng giá trị trung gian hoặc công thức nào tối ưu nhất cho bài toán này.

Rất mong các anh chị trong diễn đàn giúp đỡ

doantrang57 · 7/10/08

Hàm vlookup, hlookup chỉ trả về giá trị đầu tiên mà nó tìm thấy nếu trong dãy có nhiều dữ liệu trùng thì làm sao để tìm kiếm hàm giá trị đúng mà mình muốn tìm. Bác nào có cách gì ko

Po_Pikachu · 7/10/08

Bạn dùng code này bảo đảm luôn!

PHP:

Function hsluong(cel As Integer)
For i = 0 To Int(cel / 20)
If i > 8 Then k = 8 Else: k = i
    If i = 0 Then
    temp = 20
    ElseIf 20 * (i + 1) >= cel Then
    temp = temp + (cel - 20 * i) * Val("1." & k)
    Else
    temp = temp + 20 * Val("1." & k)
    End If
Next
hsluong = temp
End Function

Thân.

tigertiger · 7/10/08

Po_Pikachu đã viết:
Bạn dùng code này bảo đảm luôn!

PHP:

Function hsluong(cel As Integer) For i = 0 To Int(cel / 20) If i > 8 Then k = 8 Else: k = i If i = 0 Then temp = 20 ElseIf 20 * (i + 1) >= cel Then temp = temp + (cel - 20 * i) * Val("1." & k) Else temp = temp + 20 * Val("1." & k) End If Next hsluong = temp End Function

Thân.

Sao loằng ngoằng thế?

Val("1." & k) thay thành 1+k/10
và các cái khác nữa, NHƯNG bài này dùng công thức là được

SonMaritime đã viết:
Theo yêu cầu mới nhất của Lãnh đạo, thay vì Hệ số lương được tính theo trích dẫn ở trên. Tháng này có sự thay đổi như sau:
Phần Doanh số 20 SP đầu tiên, Hệ số là 1.0
Phần Doanh từ 21 đến 40 SP, Hệ số là 1.1
Phần Doanh từ 41 đến 60 SP, Hệ số là 1.2
Phần Doanh từ 61 đến 80 SP, Hệ số là 1.3
Phần Doanh từ 81 đến 100 SP, Hệ số là 1.4
Phần Doanh từ 101 đến 120 SP, Hệ số là 1.5
Phần Doanh từ 121 đến 140 SP, Hệ số là 1.6
Phần Doanh từ 141 đến 160 SP, Hệ số là 1.7
Phần Doanh trên 161 SP, Hệ số là 1.8

Như vậy, Hệ số sẽ được tính như một số ví dụ sau:
DSo: 85 SP
HSL=(20*1.0)+(20*1.1)+(20*1.2)+(20*1.3)+(5*1.4)=99
DSo: 195 SP
HSL=(20*1.0)+(20*1.1)+(20*1.2)+(20*1.3)+(20*1.4)+(20*1.5)+(20*1.6)+(20*1.7)+(35*1.8)=279

Mình chưa tìm ra cách giải nào mà không dùng giá trị trung gian hoặc công thức nào tối ưu nhất cho bài toán này.

Rất mong các anh chị trong diễn đàn giúp đỡ

Nếu đúng thế thì dùng công thức là đủ dùng, vì hệ số tăng theo cấp số cộng
Giả sử số DSo đặt tại A1, thì tại ô cần tính hệ số như sau

PHP:

=IF(A1>160,(A1-160)*1.8+20*(1+1.7)/2*8,MOD(A1,20)*(1+TRUNC(A1/20)/10)+20*(1+1+(TRUNC(A1/20)-1)/10)/2*TRUNC(A1/20))

tôi vẫn để các số theo biểu thưc tổng cấp số cộng để hiểu cách tính ví dụ (1+1.7),.... bạn có thể thay nó bằng số luôn 2.7,... chẳng hạn, để đỡ rối và nhanh hơn

SonMaritime · 7/10/08

Po_Pikachu đã viết:
Bạn dùng code này bảo đảm luôn!

PHP:

Function hsluong(cel As Integer) For i = 0 To Int(cel / 20) If i > 8 Then k = 8 Else: k = i If i = 0 Then temp = 20 ElseIf 20 * (i + 1) >= cel Then temp = temp + (cel - 20 * i) * Val("1." & k) Else temp = temp + 20 * Val("1." & k) End If Next hsluong = temp End Function

Thân.

Dùng code thì các bài toán đều dễ dàng giải quyết.
Tuy nhiên code gặp phải máy có cài diệt virus thì tiêu luôn, lại phải viết lại.
Mặt khác mặc định một số máy để security là High thì lại phải điều chỉnh lại.

Vì vậy mình luôn muốn tận dụng tối đa khả năng của hàm sẵn có là tối ưu nhất.
Mình cảm ơn bạn Tigertiger. Bạn có thể giúp mình xem còn cách nào để tận dụng ngay bảng hệ số cho sẵn không. Vì đây chỉ là ví dụ cho một dòng SP, còn các SP khác lại có bảng hệ số khác.

ptm0412 · 7/10/08

Mình chưa tìm ra cách giải nào mà không dùng giá trị trung gian hoặc công thức nào tối ưu nhất cho bài toán này.

Công thức trong file này dài nhưng tổng quát, chỉ cần các mốc số lượng SP cách đều nhau, hệ số bất kỳ (không cách đều)

tigertiger · 7/10/08

SonMaritime đã viết:
Mình cảm ơn bạn Tigertiger. Bạn có thể giúp mình xem còn cách nào để tận dụng ngay bảng hệ số cho sẵn không. Vì đây chỉ là ví dụ cho một dòng SP, còn các SP khác lại có bảng hệ số khác.

ptm0412 đã viết:
Công thức trong file này dài nhưng tổng quát, chỉ cần các mốc số lượng SP cách đều nhau, hệ số bất kỳ (không cách đều)

Tổng quát luôn, các khoảng chia mốc SP và cả HS có thể bất kỳ
Công thức cũng dựa trên SUMPRODUCT nhưng tổng quát
có thể áp dụng cho các Sp khác, như bạn mong muốn
xem file gửi kèm

Tiger62 · 7/10/08

Bạn dùng VLOOKUP để tìm giá trị thì có thể là bạn đang xử lý một cơ sở dữ liệu excel bằng công thức thì theo tôi bạn có thể thực hiện theo vài bước sau thì có hiệu quả tốt hơn:
1. Tối ưu hóa bảng dữ liệu theo nội dung quản lý.
2. Bố trí vùng trả kết quả dò bên trên CSDL. Nếu bạn định dò theo một số giá trị thì bố trí số dòng của vùng này đủ cho số giá trị cần dò.
3. Khi lập công thức bạn có thể đánh số cột trên đầu dòng để tham chiếu số thứ tự cột cho cột dò sau đó bạn coppy công thức tiện hơn.

cop_kh · 8/10/08

Thông thường thì các yếu tố không tìm thấy là do dữ liệu ở trong bảng tra có khoảng trắng ở phía sau cùng của dữ liệu. Vì vậy bạn nên dùng hàm trim() cho cột mã trong bảng tìm kiếm.

dungnguyen99 · 8/10/08

Hàm vlookup lại lỗi:

Mình ko hiểu sao ham :=VLOOKUP(D5,$F$3:$G$7,2,0)lại báo lỗi. Àh tiên đây các bạn cho mình hỏi dấu $ ở đây có tác dụng gì khác nêu ko có nó. và làm sao để thêm dấu $ vào nhanh nhất. thanks

BNTT · 8/10/08

Bạn lại gửi bài không đúng chỗ rồi. Đây là Box Hướng dẫn sử dụng diễn đàn chứ đâu phải là Box để bạn hỏi công thức ?
Bạn gửi bài ở đây sẽ không ai dám trả lời cho bạn hết, bởi vì họ biết chắc rằng bài này sẽ bị xóa.
Tôi nhớ không lầm thì Admin đã nhắc nhở bạn một lần, ở đây.
Bạn gửi lại bài vào đúng chỗ đi. Vì tôi sẽ xóa bài này.
Cả bài này cũng vậy: http://www.giaiphapexcel.com/forum/showthread.php?t=14564
Bạn cần nêu câu hỏi cho rõ ràng và gõ tiếng Việt có đầy đủ các dấu.
Nếu có thời gian, bạn nên đọc qua bài "Đặt một câu hỏi thông minh như thế nào" ở ngay dưới chữ ký của tôi.

tuoinguyen.tn · 8/10/08

doantrang57 đã viết:
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?

Theo mình nghĩ là có thể bạn dò cột và dò số sai thôi, bạn hãy cho vd lên để mọi người cùng xem và giúp bạn

@$@!^%

bonghongden · 8/10/08

Tôi gặp lỗi khi sử dụng Hàm VLOOKUP mà không hiểu tại sao

HI! GPE các bác giúp mình một chút nhé. không hiểu sao trong công thức của mình =VLOOKUP(D5,$F$3:$G$7,2,0) lại báo lỗi. àh tiện đây các bác cho hỏi là có $ và không có $ có gì khác nhau và làm sao để khi nhập công thức có $ mình nhập nhanh nhất. Thanks

cadafi · 8/10/08

bonghongden đã viết:
HI! GPE các bác giúp mình một chút nhé. không hiểu sao trong công thức của mình =VLOOKUP(D5,$F$3:$G$7,2,0) lại báo lỗi. àh tiện đây các bác cho hỏi là có $ và không có $ có gì khác nhau và làm sao để khi nhập công thức có $ mình nhập nhanh nhất. Thanks

Bạn có thể nói rõ lỗi excel thông báo là lỗi gì không?

Ký tự $ cho biết vùng đó trong công thức là cố định, khi copy công thức xuống các dòng dưới (hay sang các cột khác) thì địa chỉ vùng sẽ không bị thay đổi.

Muốn nhập nhanh thì sau khi gõ địa chỉ vùng, ví dụ: F3:G7, bạn hãy ấn phím F4

hai2hai · 8/10/08

Ban đầu cứ tưởng Microsoft viết hàm VLookup bị lỗi. Ai dè đó là do bạn áp dụng thì bị lỗi.

Tiêu đề này choáng quá!

haonlh · 8/10/08

Lệnh
=VLOOKUP(D5,$F$3:$G$7,2,0)
bị lỗi, khả năng là giá trị ô D5 không có trong cột 1 của vùng $F$3:$G$7

Bạn cũng nên cảm ơn ý mà hai2hai đã nêu: Câu hỏi đặt không chính xác. Nếu hỏi lại thì nên viết:
Tôi áp dụng hàm VLOOKUP như sau thì bị lỗi.

BNTT · 8/10/08

Tôi đã sửa lại tiêu đề. Mong rằng sau này bạn sẽ đặt tiêu đề cho bài viết chính xác hơn, giống như điều bạn muốn hỏi hơn.

Tiger62 · 8/10/08

Để bạn hiểu nhanh, trong hàm VLOOKUP ô chứa giá trị dò phải ở trong vùng dò->sửa lại vùng dò là $d$3:$g$7.

Nguyễn Xuân Sơn · 9/10/08

Bạn thay số 0 ở VLOOKUP(D5,$F$3:$G$7,2,0) bằng false xem!

rockydatalone · 9/10/08

Theo mình nghĩ bị lỗi sau:
1/ Là điều kiện dò tìm không thấy giá trị tại D5 không có trong vùng tìm báo lỗi: NA
2/ Vùng dữ liệu không đúng, giả sử cột thứ 1 bạn muốn lấy giá trị, nhưng bạn lại nhập cột thứ 2 báo lỗi: REF
3/ Số 0, False là tìm chính xác số bạn tìm, còn True là tìm vừa đúng => bạn xem nên dùng True hay false nha bạn.

haonlh · 9/10/08

Gửi riêng Nguyễn Xuân Sơn:

PHP:

Bạn thay số 0 ở VLOOKUP(D5,$F$3:$G$7,2,0) bằng false xem!

Tham số thứ 3 trong VLOOKUP là 1 biểu thức logic. Đúng là dùng False. Nhưng viết hơi dài, dùng số 0 cũng được, vì Excel coi 0 là False
</DIV>

SonMaritime · 9/10/08

PHP:

Phần Doanh số 20 SP đầu tiên, Hệ số là 1.0
Phần Doanh từ 21 đến 40 SP, Hệ số là 1.1
Phần Doanh từ 41 đến 60 SP, Hệ số là 1.2
Phần Doanh từ 61 đến 80 SP, Hệ số là 1.3
Phần Doanh từ 81 đến 100 SP, Hệ số là 1.4
Phần Doanh từ 101 đến 120 SP, Hệ số là 1.5
Phần Doanh từ 121 đến 140 SP, Hệ số là 1.6
Phần Doanh từ 141 đến 160 SP, Hệ số là 1.7
Phần Doanh trên 161 SP, Hệ số là 1.8
 
Như vậy, Hệ số sẽ được tính như một số ví dụ sau:
DSo: 85 SP
HSL=(20*1.0)+(20*1.1)+(20*1.2)+(20*1.3)+(5*1.4)=99
DSo: 195 SP
HSL=(20*1.0)+(20*1.1)+(20*1.2)+(20*1.3)+(20*1.4)+(20*1.5)+(20*1.6)+(20*1.7)+(35*1.8)=279

Theo sự gợi ý của bạn Tigertiger
Mình vừa tìm ra một cách tính mới, có thể áp dụng cả trong trường hợp các mức tăng không theo cấp số cộng mà có thể tăng bất kỳ.

=(A1*1.0)+SUMPRODUCT(--(A1>{20;40;60;80;100;120;140;160});(A1-{20;40;60;80;100;120;140;160})*({1.1;1.2;1.3;1.4;1.5;1.6}-{1;1.1;1.2;1.3;1.4;1.5}))

Nếu dùng bảng trung gian để thay thế cho các giá trị trong mảng. Công thức sẽ trở thành

=(A1*1.0)+SUMPRODUCT(--(A1>SP);(A1-SP)*({1.1;1.2;1.3;1.4;1.5;1.6}-HeSo))

Trong đó:
SP={20;40;60;80;100;120;140;160} hoặc các mức tăng tùy ý
HeSo={1;1.1;1.2;1.3;1.4;1.5} hoặc các mức tăng tùy ý
{1.1;1.2;1.3;1.4;1.5;1.6} là trong bảng HeSo lấy từ dòng 2 và cộng thêm 1 dòng 1.6

Với cách tính này nếu áp dụng cho bài toán trên của mình giả sử chỉ tăng theo cấp số cộng thì có thể rút gọn lại còn như sau:

=A1+SUMPRODUCT(--(A1>20*ROW(1:8));(A1-20*ROW(1:8))*0.1)

Tuy nhiên mình đang gặp phải vấn đề khi copy công thức thì giá trị hàm ROW bị thay đổi theo.
Có bạn nào chỉ giúp cách giữ cho hàm row ko bị thay đổi trong trường hợp copy công thức không.

ThuNghi · 9/10/08

SonMaritime đã viết:
Mình vừa tìm ra một cách tính mới, có thể áp dụng cả trong trường hợp các mức tăng không theo cấp số cộng mà có thể tăng bất kỳ. Tuy nhiên công thức hơi dài.

PHP:

=(A1*1.0)+SUMPRODUCT(--(A1>{20;40;60;80;100;120;140;160});(A1-{20;40;60;80;100;120;140;160})*({1.1;1.2;1.3;1.4;1.5;1.6}-{1;1.1;1.2;1.3;1.4;1.5}))

Nếu cần có thể dùng bảng trung gian để thay thế cho các giá trị trong mảng.
Các bạn có thể chỉ giúp xem còn cách nào ngắn và dễ hiểu hơn thì giúp mình

Với cách tính này để áp dụng cho bài toán của mình giả sử chỉ tăng theo cấp số cộng thì có thể rút gọn lại còn như sau:

HTML:

=A1+SUMPRODUCT(--(A1>20*ROW(1:8));(A1-20*ROW(1:8))*0.1)

Nhưng lại gặp phải vấn đề khi copy công thức thì giá trị hàm ROW bị thay đổi theo.
Có cách nào giữ cho hàm row ko bị thay đổi trong trường hợp tăng theo cấp số cộng không.

Với những bài tóan như thế này, tôi nghĩ bạn nên theo cách của BNTT (lập bảng) và dùng vlookup là tốt nhất, khi xếp yêu cầu thay đổi thì chỉ cần thay đổi định mức, không thay công thức, còn muốn áp dụng cho nhiều sp thì thêm cột masp và offset theo cột này.

SonMaritime · 9/10/08

ThuNghi đã viết:
Với những bài tóan như thế này, tôi nghĩ bạn nên theo cách của BNTT (lập bảng) và dùng vlookup là tốt nhất, khi xếp yêu cầu thay đổi thì chỉ cần thay đổi định mức, không thay công thức, còn muốn áp dụng cho nhiều sp thì thêm cột masp và offset theo cột này.

Mình rất cảm ơn sự hướng dẫn của bạn BNTT, Cách giải của BNTT rất hay nhưng trong một số tình huống ở những điểm tăng giữa các mốc Hệ số thường phải tính theo con số phán đoán (giảm 1 hay 0.1) nên chưa đảm bảo chính xác theo những trường hợp khác nhau.

BNTT đã viết:

=VLOOKUP(D2-1, Heso, 2)

Nhấp chuột vào đây để mở rộng...

Giả sử hệ số lương được tính theo từng quý căn cứ theo TB của 3 tháng quý trước, khi đó con số đạt được sẽ ở mức lẻ như 0.5; 20.5 hay 40.5, Lúc này kết quả sẽ không phản ánh đúng hoặc có thể sinh lỗi

Ngoài ra yêu cầu của bài toán cũng thay đổi so với bài hướng dẫn của BNTT thay vì tính HSL cho toàn bộ SP theo một mức HS thì bây giờ phải chia làm nhiều mốc với từng mức HS khác nhau. Vì vậy phải đưa ra cách giải mới sao cho phù hợp hơn.

Tiger62 · 9/10/08

Hàm VLOOKUP nếu kết hợp với điều kiện dò tìm xếp theo cột đầu tiên và các cột dữ liệu tiếp theo thì kết xuất được một bảng dữ liệu . Ví dụ :về dữ liệu nhân sự nếu bạn tổ chức dữ liệu theo mã NS và thứ tự các dòng dữ liệu của nhân sự đó theo các cột thì với hàm VLOOKUP bạn sẽ kết xuất được một bảng dữ liệu nhân sự của 1 người với nhiều dòng và nhiều cột tùy ý.

khai du toan · 10/10/08

Dùng VLOOKUP trong trường hợp này như thế nào?

Mục này hay. Xin các anh, chị cho hỏi hàm này:
Tôi dùng hàm VLOOKUP và MAX như sau
=VLOOKUP(MAX($A2:$A3)$A:$H,5,0), thì có kết quả đúng thí dụ: NGHỀ NGHIỆP tại cell E5, tôi muốn kết hợp với cell Ê6 có tên là Y TÁ thì dùng hàm như sau:
=VLOOKUP(MAX($A2:$A3)$A:$H,5-6,0) nhưng kết quả không được. Ai biết chỉ dùm với công trên có gì sai và sửa thế nào để cho kết quả là NGHỀ NGHIỆP Y TÁ . Chân thành cảm ơn

Po_Pikachu · 10/10/08

Bạn viết hàm Vlookup này mình thấy có gì đó không ổn thì phải.
Dòng dưới 5-6 = -1 thì làm sao có cột nào mà tìm chứ, tất nhiên là lỗi rồi!
Giá trị ở chỗ đó chỉ nhảy từ 2 đến số cột quét cuối cùng thôi! Ví dụ quét lấy 5 cột thì số đó sẽ là từ 2 đến 5 thôi! Mình nghĩ nó phải viết như vầy:
=VLOOKUP(MAX($A2:$A3),$A:$H,6,0)
Bạn có thể gửi vài dòng dữ liệu lên được không hoặc file càng tốt! Chứ nói vầy thì chẳng biết phải giúp như thế nào cả?

ptm0412 · 10/10/08

Ý là nối 2 kết quả tìm kiếm ở cột 5 và cột 6 lại đó mà.
=VLOOKUP(MAX($A2:$A3),$A:$H,5,0)& ": "&VLOOKUP(MAX($A2:$A3),$A:$H,6,0)
Nhưng sao lại hỏi chen ngang bài không đúng nội dung thế?

thaibinh_excel · 22/10/08

Hiện Kết Quả Vlookup ngay trên Form

Dear các bác,

Mình có tạo 01 UserForm truy tìm dữ liệu ( nhập code vào thì sẽ cho ra tên chi tiết) Nhưng mình muốn cài công thức Vlookup vào VBA luôn để khi nhấn nút thì kết quả hiện ra ngay trên Form, hoặc khi gõ mã là kết quả sẽ hiện ra trên Form luôn, nhưng mình không biết phải viết code thế nào để làm được điều này. Các Bác xem qua file và giúp dùm mình với.

Cám ơn các bác rất nhiều,

cadafi · 22/10/08

thaibinh_excel đã viết:
Dear các bác,

Mình có tạo 01 UserForm truy tìm dữ liệu ( nhập code vào thì sẽ cho ra tên chi tiết) Nhưng mình muốn cài công thức Vlookup vào VBA luôn để khi nhấn nút thì kết quả hiện ra ngay trên Form, hoặc khi gõ mã là kết quả sẽ hiện ra trên Form luôn, nhưng mình không biết phải viết code thế nào để làm được điều này. Các Bác xem qua file và giúp dùm mình với.

Cám ơn các bác rất nhiều,

Anh xử lý sự kiện TextBox1_AfterUpdate như sau:

PHP:

Private Sub TextBox1_AfterUpdate()
On Error GoTo ThaiBinh_Excel
Me.TextBox1.Value = UCase(Me.TextBox1.Value)
Me.TextBox2 = Application.WorksheetFunction.VLookup(Me.TextBox1.Value, Sheets("sheet1").[B:C], 2, 0)

Exit_Sub:
Exit Sub

ThaiBinh_excel:
    MsgBox "Ma Nhap vao khong dung, vui long nhap lai!", vbInformation, "Thong bao"
    Resume Exit_Sub
End Sub

Duong gia · 22/10/08

thaibinh_excel đã viết:
Dear các bác, Mình có tạo 01 UserForm truy tìm dữ liệu ( nhập code vào thì sẽ cho ra tên chi tiết) Nhưng mình muốn cài công thức Vlookup vào VBA luôn để khi nhấn nút thì kết quả hiện ra ngay trên Form, hoặc khi gõ mã là kết quả sẽ hiện ra trên Form luôn, nhưng mình không biết phải viết code thế nào để làm được điều này. Các Bác xem qua file và giúp dùm mình với. Cám ơn các bác rất nhiều,

Bạn mở file đính kèm. Cái này gọn hơn, đáp ứng được yêu cầu của bạn. Bạn có thể gõ trực tiếp vào hay chọn từ danh sách cũng được.

anhphuong · 22/10/08

Sao không dùng Combobox nhỉ?

sealand · 22/10/08

Bạn có thể tham khảo bài của mình ở link sau:
http://www.giaiphapexcel.com/forum/showthread.php?t=14735

thaibinh_excel · 22/10/08

ca_dafi đã viết:

Anh xử lý sự kiện TextBox1_AfterUpdate như sau:

PHP:

Private Sub TextBox1_AfterUpdate()
On Error GoTo ThaiBinh_Excel
Me.TextBox1.Value = UCase(Me.TextBox1.Value)
Me.TextBox2 = Application.WorksheetFunction.VLookup(Me.TextBox1.Value, Sheets("sheet1").[B:C], 2, 0)

Exit_Sub:
Exit Sub

ThaiBinh_excel:
    MsgBox "Ma Nhap vao khong dung, vui long nhap lai!", vbInformation, "Thong bao"
    Resume Exit_Sub
End Sub

Ca_dafi cho mình hỏi, mình dùng Format Cell chỉnh đối tượng truy xuất là số thành định dạng theo ý mình, ví dụ số 01 , mình mặc định là "S"&0000000 trong Format Cell ~ S00000001, khi đó code chuyển chữ thường thành chữ hoa không cần thiết nữa, mình đã xóa đi.

Như vậy mình có 02 colum : 1 là số thứ tự, 2 là tên công ty ( ví dụ thế) nhưng lúc đó khi mình đánh số thứ tự thì code của bạn hình như không hiểu được nên cứ báo lỗi. Khắc phục thế nào vậy bạn ơi ?

Mong giúp dùm. Cám ơn các bạn

XuanThanh · 23/10/08

1/ Thêm vào form một Textbox nữa đặt tên là Textbox3 có thuộc tính Visible = True
2/ Câu lệnh xử lý sẽ là :

PHP:

Private Sub TextBox1_AfterUpdate()
On Error GoTo ThaiBinh_Excel
Me.TextBox3 = "S" & Right("0000000" & Me.TextBox1, 8)    '   Tùy vào cách đặt dữ liệu ở cột A mà sửa lại cho phù hợp
Me.TextBox2 = Application.WorksheetFunction.VLookup(Me.TextBox3.Value, Sheets("sheet1").[A:C], 3, 0)
 
Exit_Sub:
Exit Sub
 
ThaiBinh_excel:
    MsgBox "Khong co so hieu nay, vui long nhap lai!", vbInformation, "Thong bao"
    Resume Exit_Sub
End Sub

</SPAN></SPAN>

thaibinh_excel · 23/10/08

XuanThanh đã viết:
1/ Thêm vào form một Textbox nữa đặt tên là Textbox3 có thuộc tính Visible = True
2/ Câu lệnh xử lý sẽ là :

PHP:

Private Sub TextBox1_AfterUpdate() On Error GoTo ThaiBinh_Excel Me.TextBox3 = "S" & Right("0000000" & Me.TextBox1, 8) ' Tùy vào cách đặt dữ liệu ở cột A mà sửa lại cho phù hợp Me.TextBox2 = Application.WorksheetFunction.VLookup(Me.TextBox3.Value, Sheets("sheet1").[A:C], 3, 0) Exit_Sub: Exit Sub ThaiBinh_excel: MsgBox "Khong co so hieu nay, vui long nhap lai!", vbInformation, "Thong bao" Resume Exit_Sub End Sub

</SPAN></SPAN>

Cám ơn bác Xuân Thanh, nhưng hình như bác hiểu nhấm ý của mình rồi. Cụ thể là Form của mình sẽ có 02 TextBox, TextBox 1 là mã code, TextBox 2 là dữ liệu hiên ra. Theo file cũ thì textbox 1 là chữ nên gõ chữ vào thì truy xuất được, nhưng nếu mình chuyển dữ liệu trong file của mình thành 1,2,3 chứ không phải là AA,BB,AC thì khi mình gõ vào TextBox 1 nó báo lỗi " k tìm thấy dữ liệu". Mình sẽ viết code gì hay điều chỉnh thế nào vậy ?

Thanks and Best Regards,
TB

XuanThanh · 23/10/08

1/ Mục đich phải format trong VBA là để giá trị của Textbox3 giống với giá trị của cột A trong Sheet1 và giá trị này để ta dùng VLOOKUP tìm kiếm
2/ Khi gõ 1,2,3... vào Textbox2 thì Textbox3 tự format theo
Thân

P/S : Việc format ở cột A trong Sh và việc format Textbox trong Form là hai chuyện hoàn toàn khác nhau nhưng mục đích chung là để 2 dữ liệu giống nhau phục vụ cho việc tìm kiếm bằng VLOOKUP

thaibinh_excel · 23/10/08

XuanThanh đã viết:
1/ Mục đich phải format trong VBA là để giá trị của Textbox3 giống với giá trị của cột A trong Sheet1 và giá trị này để ta dùng VLOOKUP tìm kiếm
2/ Khi gõ 1,2,3... vào Textbox2 thì Textbox3 tự format theo
Thân

P/S : Việc format ở cột A trong Sh và việc format Textbox trong Form là hai chuyện hoàn toàn khác nhau nhưng mục đích chung là để 2 dữ liệu giống nhau phục vụ cho việc tìm kiếm bằng VLOOKUP

Vậy là phải dùng 01 TextBox trung gian hả bác? Mình không thể VLOOKUP theo number trực tiếp từ TEXTBOX1 luôn sao ? Nếu như vậy sao mình không cài câu lệnh Right ở TextBox1 luôn nhỉ ?

XuanThanh · 23/10/08

VLOOKUP là hàm dò tìm chính xác. Vì vậy điều kiện dò tìm là phải chính xác nếu không sẽ không dò tìm được. Bình xem lại cú pháp của hàm VLOOKUP sẽ hiểu ngay thôi. Nếu không muốn dùng trung gian thì cột A đừng có format, cứ để theo số TT tự nhiên
Thân

P/S : Khi nãy gọi điện cho chú mà không được. Xóa số rồi hả ?

thaibinh_excel · 23/10/08

XuanThanh đã viết:
VLOOKUP là hàm dò tìm chính xác. Vì vậy điều kiện dò tìm là phải chính xác nếu không sẽ không dò tìm được. Bình xem lại cú pháp của hàm VLOOKUP sẽ hiểu ngay thôi. Nếu không muốn dùng trung gian thì cột A đừng có format, cứ để theo số TT tự nhiên
Thân

P/S : Khi nãy gọi điện cho chú mà không được. Xóa số rồi hả ?

Em gui lai cho anh File luôn nè, chỉnh theo đúng hướng dẫn của anh a nhưng mà hổng ra hi`hi`

XuanThanh · 23/10/08

Do việc Format ở hai vị trí là khác nhau nên VBA nó hổng có hiểu
Vậy làm theo cách vòng vo vậy. Mượn Sheet để dò tìm
Bình xem file kèm theo sẽ rõ
Thân

ptm0412 · 23/10/08

Không cần vòng vo gì đâu, nếu biết chắc cột A là số thì thêm Val() là được:

PHP:

Private Sub TextBox1_AfterUpdate()
On Error GoTo ThaiBinh_excel
Me.TextBox2 = Application.VLookup(Val(TextBox1), Range("bangdo"), 3, 0)
Range("A1").Select
Exit_Sub:
Exit Sub
 
ThaiBinh_excel:
    MsgBox "Khong co so hieu nay, vui long nhap lai!", vbInformation, "Thong bao"
    TextBox1 = "" : TextBox2 = ""
    TextBox1.SetFocus
    Resume Exit_Sub
End Sub

thaibinh_excel · 23/10/08

XuanThanh đã viết:
Do việc Format ở hai vị trí là khác nhau nên VBA nó hổng có hiểu
Vậy làm theo cách vòng vo vậy. Mượn Sheet để dò tìm
Bình xem file kèm theo sẽ rõ
Thân

Cám ơn anh, nhưng như thế không ổn ạ vì nếu vậy thì em cho Vlookup trực tiếp ngoài dữ liệu rồi set TEXBOX.VALUE = dữ liệu bên ngoài là được rồi. Em chỉ muốn Vlookup trực tiếp trên Form thôi a.

Best Regards.

Lệnh Hồ Đại Hiệp · 23/10/08

thaibinh_excel đã viết:
Cám ơn anh, nhưng như thế không ổn ạ vì nếu vậy thì em cho Vlookup trực tiếp ngoài dữ liệu rồi set TEXBOX.VALUE = dữ liệu bên ngoài là được rồi. Em chỉ muốn Vlookup trực tiếp trên Form thôi a.

Best Regards.

Bạn thử xem nhé :

- Dùng Listview : Dùng và sửa lại File của các cao thủ trên diễn đàn

- Dùng UDF : File của bạn

Chúc vui.

hdt_dragonking · 25/10/08

Nhờ chỉ cách dùng công thức:Vlookup(mid(),..,..,..)!

Em thực hành Excel, dùng công thức này không được:"=vlookup(mid(.....),......,...,...)".Công thức em đã soạn ra trong " ĐỀ 4",của file em đính kèm ,dưới mấy dòng chữ đỏ!.Mấy bác giải thích dùm em là em đã sai ở chỗ nào ,và cách khắc phục ạh! Em xin chân thành cảm ơn!

http://hdtdragonking.googlepages.com...2222222222.XLS

cadafi · 25/10/08

hdt_dragonking đã viết:
Em thực hành Excel, dùng công thức này không được:"=vlookup(mid(.....),......,...,...)".Công thức em đã soạn ra trong " ĐỀ 4",của file em đính kèm ,dưới mấy dòng chữ đỏ!.Mấy bác giải thích dùm em là em đã sai ở chỗ nào ,và cách khắc phục ạh! Em xin chân thành cảm ơn!

http://hdtdragonking.googlepages.com...2222222222.XLS

Bạn phải biết rằng hàm MID sẽ trả về giá trị kiểu TEXT, trong khi vùng đích để tìm kiếm của bạn là giá trị số, nên hàm Vlookup không cho ra giá trị là đúng.

Công thức vlookup của bạn nên sửa lại như sau:

=VLOOKUP(--MID(B7,2,1),$G$16:$H$19,2,0)

Hoặc

=VLOOKUP(0+MID(B7,2,1),$G$16:$H$19,2,0)

Hoặc

=VLOOKUP(MID(B7,2,1)*1,$G$16:$H$19,2,0)

Và xem thêm tại đây nhé!
http://www.giaiphapexcel.com/forum/showpost.php?p=108770&postcount=11

cop_kh · 25/10/08

Cách khác bạn có thể dùng thêm hàm value như sau:

=VLOOKUP(VALUE(MID(B7,2,1)),$G$16:$H$19,2,0)

Po_Pikachu · 25/10/08

Còn 1 cách nữa là bạn đổi G16:G19 thành kiểu text hết thì OK. Bạn quét chọn vùng G16:G19 rồi bấm phải chuột chọn Format Cells -> chọn tab Number -> Nhấn vào chữ Text -> OK. Rồi nhập lại số trong vùng G16:G19 là được. Thân.

thaibinh_excel · 25/10/08

ptm0412 đã viết:
Không cần vòng vo gì đâu, nếu biết chắc cột A là số thì thêm Val() là được:

PHP:

Private Sub TextBox1_AfterUpdate() On Error GoTo ThaiBinh_excel Me.TextBox2 = Application.VLookup(Val(TextBox1), Range("bangdo"), 3, 0) Range("A1").Select Exit_Sub: Exit Sub ThaiBinh_excel: MsgBox "Khong co so hieu nay, vui long nhap lai!", vbInformation, "Thong bao" TextBox1 = "" : TextBox2 = "" TextBox1.SetFocus Resume Exit_Sub End Sub

PTM0412 oi, trong trường hợp sheet để dò tìm nằm ở 1 sheet khác thì sao? Câu lệnh của bác khi apply vào file của mình cứ báo lỗi range ( "wrong number of arguement or invalid property assignment"). Mình không rõ tại sao nữa ?

Code của mình :

On Error GoTo ThaiBinh_excel
Me.TextBox7 = Application.VLookup(Val(TextBox1), Range("data", 26, 0))
Sheets("general").Range("B6").Select

Exit_Sub:
Exit Sub

ThaiBinh_excel:
MsgBox "Khong co so hieu nay, vui long nhap lai!", vbInformation, "Thong bao"
Resume Exit_Sub

Nhờ bác xem giúp. Cám ơn bác.

ptm0412 · 25/10/08

Me.TextBox7 = Application.VLookup(Val(TextBox1), Range("data", 26, 0))
Chắc là lỗi dòng này.
1. "data" là name bạn đặt như thế nào? name có giá trị toàn wokbook hay name chỉ có giá trị trong worksheet? nếu như vầy là đúng: = sheet1!$A$1:$Z$200
2. vùng data đó có bao nhiêu cột? (tìm tới cột thứ 26 lận)
3. Có 1 chỗ sai màu đỏ không biết là chỉ sai trong bài viết trên hay trong code?

Sửa lại như vầy: Me.TextBox7 = Application.VLookup(Val(TextBox1), Range("data"), 26, 0)

Bổ sung:

XuanThanh đã viết:
VLOOKUP là hàm dò tìm chính xác. Vì vậy điều kiện dò tìm là phải chính xác nếu không sẽ không dò tìm được. Bình xem lại cú pháp của hàm VLOOKUP sẽ hiểu ngay thôi. Nếu không muốn dùng trung gian thì cột A đừng có format, cứ để theo số TT tự nhiên

Đã là số mà định dạng custom "S"000000 thì vẫn cứ là số, khi dò tìm chỉ cần giá trị dò tìm là số thì sẽ tìm được. Thí dụ thể hiện là S000015 nhưng chỉ cần tìm 15 (số) là ra. Chỉ khi tìm "15" (text) mới không ra, lúc đó phải dùng Val() chuyển về số.

thaibinh_excel · 26/10/08

ptm0412 đã viết:
Me.TextBox7 = Application.VLookup(Val(TextBox1), Range("data", 26, 0))
Chắc là lỗi dòng này.
1. "data" là name bạn đặt như thế nào? name có giá trị toàn wokbook hay name chỉ có giá trị trong worksheet? nếu như vầy là đúng: = sheet1!$A$1:$Z$200
2. vùng data đó có bao nhiêu cột? (tìm tới cột thứ 26 lận)
3. Có 1 chỗ sai màu đỏ không biết là chỉ sai trong bài viết trên hay trong code?

Sửa lại như vầy: Me.TextBox7 = Application.VLookup(Val(TextBox1), Range("data"), 26, 0)

Bổ sung:

Đã là số mà định dạng custom "S"000000 thì vẫn cứ là số, khi dò tìm chỉ cần giá trị dò tìm là số thì sẽ tìm được. Thí dụ thể hiện là S000015 nhưng chỉ cần tìm 15 (số) là ra. Chỉ khi tìm "15" (text) mới không ra, lúc đó phải dùng Val() chuyển về số.

Cám ơn bác,

Mình đã tìm ra chổ sai rồi, đúng là mình đánh công thức chưa chính xác.
Về định dạng thì mình cũng nghi là như vậy, nhưng không hiểu tại sao vào TextBox lại không hiểu. Sau khi cài thêm Val() thì okie.

Thân.

bbq1401 · 29/10/08

Hỏi về Công Thức Vlookup

em đã ghi hết yêu cầu ở trong sheets đó. Mong GPE giúp đỡ

canhlv · 29/10/08

Vlookup là hàm điền giá trị vào ô cần điền từ bảng tham chiếu chứ ko phải là hàm đếm, bạn dùng công thức sai rồi; Ban có thể tham khảo hàm count or sumif , chức thành công

bbq1401 · 29/10/08

Đúng là mình dùng để điền giá trị tìm thấy. Mình chỉ muốn thêm là nếu nó không tìm thấy giá trị cần tìm thì điền giá trị 0

xichdragon · 29/10/08

Bạn dùng kết hợp với hàm ISNA
=if(ISNA(vlookup(..));0;vlookup(..))

tranvietdung · 29/10/08

bạn muốn trả giá trị =0 nếu ko tìm thấy dữ liệu thì phải dùng hàm if kết hợp với isna.

Baton1123 · 29/10/08

Hỏi về hàm Vlookup.

Tôi có bảng tính nhập xuất tồn kho. Tôi dùng hàm Vlookup để lấy tên vật tư từ mã vật tư. Từ mã SU01-SU99 thì Excel tìm ra tên Vật tư.Nhưng từ mã SU100 thì Excel không tìm đúng.
Nhờ anh/chị xem giúp tôi như file đính kèm.

Cảm ơn nhiều!

sealand · 29/10/08

Bạn thêm 1 tham số quy định cách tìm:
=VLOOKUP(B105,'MÃ VT'!$B$2:$E$199,2,0)
Thêm số 0 quy định tìm chính xác
(Công thức của bạn từ SU01 đến SU99 bạn cho là chính xác nhưng nếu sắp xếp trong sheet Ma VT thay đổi thì kết quả thật tệ)

huutrai · 29/10/08

mình mới gia nhập vào GPE. mong mọi người chỉ giáo cho. mình muốn tìm xem trong bảng tính có bao nhiêu người chọn lô số 1, số 2. Giúp mình với nhé!!!!!!!!!

xedien_bus · 29/10/08

Bạn bôi đen vùng tìm kiếm sau đó vào name box đặt cho vùng đó một tên. =VLOOKUP(B8,'MÃ VT'!$B$2:$E$199,2). Vùng 'MÃ VT'!$B$2:$E$199 đặt thành LOVE và công thức sẽ là : Vlookup(B8,love,2,0) là được.

bbq1401 · 29/10/08

Mình đã sử dụng thành công rồi. cam ơn bạn xichdragon

Xin các bạn hướng dẫn để có thể làm trong sheets2 nhé

ST-Lu! · 3/11/08

Hỏi cách dùng Vlookup( tìm 1 giá trị ở 02 bảng..)

MOD xóa giúp em bài này
------------

Em làm được rồi

TrungChinhs · 3/11/08

Hàm VLookup với vùng tham chiếu "động"

Thông thường ta vẫn dùng hàm VLOOKUP như ví dụ sau: VLOOKUP($I9;$B$6:$G$15;3;0) trong đó các cột tham chiếu đều là "tĩnh" (trong vd này là cột 3) do đó phải rất thận trọng với bảng nguồn vì nếu thêm hoặc bớt cột của bảng nguồn thì vị trí của cột tham chiếu cũng thay đổi và kết quả không còn đúng nữa, thật là tai hại khi đã gán công thức đi khắp nơi chẳng biết đằng nào mà lần ?.

Để làm cho làm cho các cột tham chiếu trở thành "động" tức là khi vùng nguồn nở ra (khi thêm cột) hoặc co lại (khi bớt cột) kết quả vẫn luôn đúng ta thay số 3 bằng hàm Columns($cột điều kiện:cột tham chiếu) vd: VLOOKUP($I$9,$B$6:$G$15,COLUMNS($B

,0) từ đây bạn có thể tuỳ ý thay đổi không gian vùng nguồn (miễn là không xoá cột đầu tiên và các cột đã tham chiếu) xem chi tiết trong file đính kèm.
Mong rằng chiêu này giúp ích ít nhiều cho bạn nào hay dùng VLookup.

ndu96081631 · 3/11/08

Trung Chinh đã viết:
Thông thường ta vẫn dùng hàm VLOOKUP như ví dụ sau: VLOOKUP($I9;$B$6:$G$15;3;0) trong đó các cột tham chiếu đều là "tĩnh" (trong vd này là cột 3) do đó phải rất thận trọng với bảng nguồn vì nếu thêm hoặc bớt cột của bảng nguồn thì vị trí của cột tham chiếu cũng thay đổi và kết quả không còn đúng nữa, thật là tai hại khi đã gán công thức đi khắp nơi chẳng biết đằng nào mà lần ?.

Để làm cho làm cho các cột tham chiếu trở thành "động" tức là khi vùng nguồn nở ra (khi thêm cột) hoặc co lại (khi bớt cột) kết quả vẫn luôn đúng ta thay số 3 bằng hàm Columns($cột điều kiện:cột tham chiếu) vd: VLOOKUP($I$9,$B$6:$G$15,COLUMNS($B,0) từ đây bạn có thể tuỳ ý thay đổi không gian vùng nguồn (miễn là không xoá cột đầu tiên và các cột đã tham chiếu) xem chi tiết trong file đính kèm.
Mong rằng chiêu này giúp ích ít nhiều cho bạn nào hay dùng VLookup.

Bạn dùng cái này:

=VLOOKUP($I$9,$A$6:$G$16,COLUMNS($A),0)

Tôi góp vui cái này:

=OFFSET($D$6,MATCH($I$9,$A$6:$A$16,0)-1,)

Còn nếu đãm bảo dòng không bị chèn thêm thì công thức càng gọn hơn:

=OFFSET($D$6,$I$9-1,)

Cry207 · 6/11/08

Nhờ giải bài tập này (Đề cho dùng IF và Hlookup hoặc IF và VLOOKUP)

Đề bài: Phân loại thí sinh đỗ hoặc trượt theo từng KV và theo số báo danh (A=Anh, P=Pháp, N=Nga, T=Trung). (Dựa vào bảng điểm chuẩn)
bác nào biết cách giải xem giúp. thanks

Po_Pikachu · 6/11/08

Bạn dùng công thức như vầy xem có ổn không? [J2] =IF(I2>=HLOOKUP(CHOOSE(MATCH(LEFT(E2,1),{"A","P","N","T"}),"ANH","PhÁP","NGA","TRUNG"),$E$11:$I$14,RIGHT(D2,1)+1),"Đậu","Rớt") Thân.

cop_kh · 6/11/08

Cái này chỉ có Vlookup và IF thôi:

J2=IF(I2>=VLOOKUP(D2,$F$12:$I$14,IF(LEFT(E2)="A",2,IF(LEFT(E2)="P",3,IF(LEFT(E2)="N",4,5))),0),"Đậu","Rớt")

Cry207 · 6/11/08

Ko được bác cọp ah, cách của bác pikachu có vẻ ổn, nhưng em chưa học. hix. Bác giải thix cho em cái. Quên cách của bác cọp được. Sao lần trước em làm ko ra nhỉ, thanks 2 bác

Po_Pikachu · 6/11/08

Bạn xem công thức của bác cop_kh chắc bạn cũng đã phần nào hiểu được hàm IF rồi đúng không. Vấn đề là việc chọn cột nào trong vùng $F$12:$I$14 theo thứ tự: Cột 2: Anh; Cột 3: Pháp; Cột 4: Nga; Cột 5: Trung. Vậy các hàm IF ở trong vùng chọn cột dùng để chọn xem ký tự đầu trong SBD là thuộc cột nào. Và cho nó đúng con số của cột mình muốn. Vậy thôi! Bạn muốn biết chính xác các hàm và cách dùng thì có thể tra cứu ở trang này! http://www.giaiphapexcel.com/forum/showthread.php?t=7971 Chúc thành công! Thân.

vostro 1500 · 7/11/08

Công thức đúng của bác Trung Chinh là : VLOOKUP($I$9,$B$6:$G$15,COLUMNS($B),0). Cám ơn bác , cách này hay quá! @$@!^%

Hix bác ndu96 có thể nói rõ công thức của bác cụ thể có tác dụng gì? Mình áp dụng thử mà ko ra kết quả.

nhatchidao · 7/11/08

Hàm Vlookup không hoạt động?

View attachment Analysis Domitary Test2.rar

Các anh chị xem giúp em file này. Vùng màu vàng chứa hàm Vlookup em định hỏi.
Giả sử giá trị cần tìm là 8 thì kết quả là 15 mới đúng nhưng sao hàm lại trả về 5. Các giá trị cần tìm khác cũng không đúng.

Anh chị nào rõ xin chỉ giúp. Thanks

boyxin · 7/11/08

nhatchidao đã viết:
View attachment 17879

Các anh chị xem giúp em file này. Vùng màu vàng chứa hàm Vlookup em định hỏi.
Giả sử giá trị cần tìm là 8 thì kết quả là 15 mới đúng nhưng sao hàm lại trả về 5. Các giá trị cần tìm khác cũng không đúng.

Anh chị nào rõ xin chỉ giúp. Thanks

Công thức cũ của bạn:

PHP:

=VLOOKUP(D30,E11:K24,7)

Bạn sửa lại công thức như sau

=VLOOKUP(D30,E11:K24,7,0)

Các câu hỏi về hàm dò tìm dữ liệu (Lookup, Vlookup, Hlookup...) (1 người xem)

Closed poll

1

2

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Po_pikachu@ymail.com

File đính kèm

Huyền thoại GPE

Po_pikachu@ymail.com

Members actively

Thành viên mới

File đính kèm

Huyền thoại GPE

File đính kèm

Members actively

File đính kèm

Thành viên mới

Po_pikachu@ymail.com

Thành viên mới

Thành viên mới

Thành viên gắn bó

Bùi Nguyễn Triệu Tường

Thành viên mới

Po_pikachu@ymail.com

Thành viên mới

Thành viên chính thức

Hành động từ trái tim

Thành viên mới

Bùi Nguyễn Triệu Tường

Thành viên mới

Huyền thoại GPE

Po_pikachu@ymail.com

Thành viên mới

Huyền thoại GPE

Thành viên gạo cội

Hạt cát sông Hằng

Thành viên mới

Thành viên mới

Po_pikachu@ymail.com

Coming back ...

Thành viên mới

Bad Excel Member

File đính kèm

Coming back ...

File đính kèm

Thành viên hoạt động

Hôm qua em đến trường...

Thành viên mới

Bùi Nguyễn Triệu Tường

Thành viên mới

Thành viên mới

Hành động từ trái tim

VNUNi®

Thành viên tích cực

Bùi Nguyễn Triệu Tường

Thành viên hoạt động

Thành viên thường trực

Thành viên mới

Thành viên tích cực

Thành viên mới

Hãy cho rồi sẽ nhận!

Thành viên mới

Thành viên hoạt động

Thành viên mới

Po_pikachu@ymail.com

Bad Excel Member

Thành viên hoạt động

File đính kèm

Hành động từ trái tim

File đính kèm

Thành viên thường trực

File đính kèm

Thân Thương

Thành viên gạo cội

Thành viên hoạt động

Thành viên thường trực

Thành viên hoạt động

Thành viên thường trực