PDA

View Full Version : Dò tìm hai chiều và hàm INDEX



songlon
19-07-06, 01:05 PM
Có thể nói: Hàm VLOOKUP và HLOOKUP dùng để dò tìm theo một chiều.
Việc kết hợp hàm MATCH và hàm INDEX, chúng ta có thể dò tìm giá trị trong bảng theo hàng và cột.
Bài toán
Bạn là chủ cửa hàng bán quần - áo, có một khách hàng bước vào. Bạn cao 165, nhỏ con. Quan sát khách, bạn biết anh ta sẽ dùng chiếc áo số mấy. Dĩ nhiên, bảng dữ liệu là đúc kết từ một thống kê nào đó. Bạn mặc quần size 31 rất vừa vì đó là chuẩn từ các kết quả đo hàng vạn người.
Bảng hai chiều này gồm:
Giá tri hàng: Chiều cao của khách
Giá trị cột: Cỡ khung người (lớn con, nhỏ con...)
Giao điểm của hàng - cột: SIZE khuyến cáo đưa cho Khách thử.

Dữ liều cần nhập là : Chiều cao của Khách và cỡ người.
Thông tin nhận được: SIZE khuyến cáo.

HÀM MATCH gồm 3 tham số như sau:1. Giá trị chúng ta đưa ra để dò
2. Dãy (mảng) các giá trị cho sẵn (cho trước) theo thứ tự hoặc không.
3.Kiểu dò tìm (là hằng số -1, 0, 1):

3a. Nếu là -1: Dãy các giá trị cho sẵn phải sắp xếp từ lớn đến nhỏ. Tìm đến vị trí giá trị lớn hoặc bằng giá trị chúng ta đưa ra

3b. giá trị là 0, Dãy giá trị cho trước không cần thứ tự. Tìm đến vị trí giá trị đầu tiên bằng với giá trị chúng ta đưa ra.

3c. 1, Ngược với -1. Dãy cho trước xếp từ nhỏ đến lớn. Tìm đến vị trí giá trị nhỏ hoặc bằng giá trị chúng ta đưa ra.
Hàm MATCH trả về một giá trị và đó chính là vị trí. Vị trí tương đối hoặc chính xác so với giá trị chúng ta đưa ra để dò.

HÀM INDEX()
Hàm có 3 tham số:
1. Mảng cho trước (Mảng 2 chiều).
2. Giá trị vị trì của hàng (tính từ 1)
3. Giá trị vị trí của cột (tính từ 1)

Index và Match phối hợp nhau cũng khá hay phải không các bạn!

dhxd49
08-04-08, 12:24 AM
Cảm ơn bạn. Bài của bạn cho phép dò tìm theo 2 chiều nhưng mình muốn nội suy theo 2 chiều thì nên làm như thế nào? Mình có 1 bảng, Giá trị biến thiên trong bảng tham chiếu theo 2 chiều. Mình muốn tìm 1 giá trị ở trong bảng mà phải nội suy thì phải viết hàm như thế nào? Mình có file đính kèm dưới đây!

tedaynui
08-04-08, 02:29 AM
To Songlon
Thấy file của bạn hay hay nên tiện tay mình trang trí thêm tí.

TDN

TRA KHUC RIVER
06-12-08, 07:57 AM
Mình không hiểu cú pháp của hàm index ai biết xin chỉ giáo cho mình với nếu có ví dụ càng tốt. Xin cảm ơn

DOSNET
06-12-08, 08:11 AM
Bạn tham khảo, trích từ Ebook Trần Quang Hải


INDEX
Công dụng
Trả về một giá trị, hoặc một tham chiếu đến một giá trị nằm bên trong một bảng hoặc một mảng dựa vào chỉ số dòng và cột. Hàm INDEX có 2 dạng: Mảng và Tham chiếu. Dạng mảng luôn luôn trả về một giá trị hoặc một mảng giá trị. Dạng tham chiếu luôn trả về một tham chiếu.


Dạng mảng
Công thức =INDEX(array,row_num,column_num) array là một dãy ô hoặc mảng hằng.
Nếu array chỉ có một dòng hoặc một cột, các đối số row_num hoặc column_num là tùy chọn.
Nếu array có nhiều hơn một dòng hoặc một cột, chỉ có một đối số row_num hoặc column_num được dùng.
row_num chỉ số dòng cần trả về giá trị. Nếu không nhập thì hàm sẽ lấy chỉ số cột colum_num.
colum_num chỉ số cột cần trả về giá trị. Nếu không nhập thì hàm sẽ lấy chỉ số dòng row_num.
Lưu ý! Nếu cả 2 đối số row_num và colum_num đều được dùng thì hàm trả về giá trị trong ô giao điểm giữa row_num và colum_rum.
Nếu cả 2 đối số row_num và colum_num đều là 0. Hàm sẽ trả về một mảng giá trị theo thứ tự. Để trả về một mảng giá trị trong Excel bạn phải nhập công thức dưới dạng công thức mảng. Để nhập công thức mảng: Bôi đen vùng ô cho công thức, Nhấn phím F2, nhập công thức và nhấn tổ hợp phím CTRL + SHIFT + ENTER. Nếu không hàm sẽ trả về lỗi #VALUE! (http://www.giaiphapexcel.com/forum/ERROR.htm#VALUE)
row_num và column_num phải chỉ vào một thứ tự của mảng giá trị. Nếu không hàm INDEX sẽ trả về lỗi #REF! (http://www.giaiphapexcel.com/forum/ERROR.htm#REF%21)

Dạng tham chiếu
Công thức =INDEX(reference,row_num,column_num,area_num) reference tham chiếu đến một hoặc nhiều dãy ô.

Nếu bạn nhập một dãy ô không liên tục thì phải đặt chúng trong dấu ngoặc đơn.
Nếu một vùng tham chiếu chỉ có một dòng hoặc một cột thì các tham số row_num, column_num là tùy chọn, theo thứ tự định sẵn.

row_num chỉ số dòng cần trả về tham chiếu. column_num chỉ số cột cần trả về tham chiếu. area_num thứ tự dãy tham chiếu cần trả về tham chiếu. Lưu ý! Nếu row_num, colum_num là 0, INDEX trả về tham chiếu đầy đủ từ reference. Khi đó bạn cần nhập công thức dưới dạng công thức mảng. Để nhập công thức mảng: Chọn vùng ô cần cho công thức, nhấn F2, nhập công thức và nhấn tổ hợp phím CTRL + Shift + ENTER. Nếu không hàm trả về lỗi #VALUE! (http://www.giaiphapexcel.com/forum/ERROR.htm#VALUE)
row_num, colum_num phải chỉ vào thứ tự trong vùng tham chiếu. Nếu không hàm INDEX trả về lỗi #REF! (http://www.giaiphapexcel.com/forum/ERROR.htm#REF%21)

BNTT
06-12-08, 08:22 AM
Mình không hiểu cú pháp của hàm index ai biết xin chỉ giáo cho mình với nếu có ví dụ càng tốt. Xin cảm ơn
Bạn xem thêm ở đây: Hàm INDEX (http://www.giaiphapexcel.com/forum/showpost.php?p=48591&postcount=6)()

soloshevcento
31-07-09, 08:29 PM
Mình có 2 bảng (ở 2 sheet) trong file đính kèm, mục đích là dò tìm hai chiều đ63 lấy giá trị thỏa điều kiện nhự minh họa đã được tô màu, nhờ các bạn giúp đỡ.
Cái khó của bảng là lấy giá trị theo khoảng ở cả 2 chiều chứ ko phải là giá trị cố định.

haituan
31-07-09, 11:52 PM
dhxd49: M làm cho bạn nội suy tự động rồi đó.ko dùng VBA.Bạn tải về tham khảo nhé! trong file công thức hơi lung tung. Nếu bạn muốn xem thì dùng pass: 26384 mở ra xem công thức nhé.

http://www.4shared.com/file/121984943/4d659af5/Noi_suy_2_chieu.html
Bonne chance!

soloshevcento (http://www.giaiphapexcel.com/forum/member.php?u=45875): Bạn xem file m làm nhé! M ko biết có đúng ý bạn ko. Nhưng mà nhiều thông số cũng làm được mà!!!
http://www.4shared.com/file/121988226/2dd699a5/_2__test.html






---------------------------------------------------------------------------
Học làm gì cho đầu to, mắt trố????

nguyen vna giang
25-09-10, 11:01 AM
Chào các Bác trong diễn đàn Giải Pháp Excel,
Qua tìm hiểu em được biết rằng diễn đàn Giải Pháp Excel có thể hướng dẫn hay trả lời giúp một số rắc rối trong Excel 2007.Vì thế em muốn nhờ diến đàn giúp em .
Vấn đề :
em có 2 file : thông tin sx va form như em đã tải lên .
trong file thông tin sx em có rất nhiều thông tin về các mã hàng khác nhau (trong file đó chi lấy vd cho một số mã hàng)
bây giờ e muốn khi nhập bất kỳ mã hàng nào vd QC2-7902 vào file Form đó thì các thông tin trong vùng D10:H12 của file form đựơc link một cách tự động.
Vậy các Bác xem có cách link nào không , giúp em với.
Rất mong nhận đựơc câu trả lời sớm , em xin cảm ơn trước.

nguyen vna giang
21-10-10, 04:54 PM
Các Bác trong diễn đàn Giải Pháp Excel ơi,
các bác trả lời sớm giúp em rắc rối ở trên với, e đã post bài lâu rùi mà chưa nhận đựơc câu trả lời.
các bác giúp e với nhá.
Thanks!

hoangoclan_1268
12-02-11, 09:09 AM
em không biết sử dụng hàm index, anh/chị nào biết chỉ rùm em và cho em một vài ví dụ cho dễ hiểu nha

NH_DK
12-02-11, 09:24 AM
em không biết sử dụng hàm index, anh/chị nào biết chỉ rùm em và cho em một vài ví dụ cho dễ hiểu nha

Bạn tham khảo hàm Index ở đây (http://www.google.com.vn/custom?domains=www.giaiphapexcel.com&q=H%C3%A0m+index&sitesearch=www.giaiphapexcel.com) nhé!

thuongamex
25-04-11, 01:58 PM
bạn có thể làm 1 ví dụ về cách lấy dữ liệu từ các sheet khác nhau của bảng cel đc ko?
thanks !

quean
11-05-11, 01:05 PM
Các bạn ơi, mình sử dụng hàm index kết hợp với ham match được rồi, ứng dụng để lấy tỷ giá theo ngày tương ứng với từng đơn vị tiền tệ. Tuy nhiên mỗi lần sort data là index lấy dòng cột của cell trước đó mặc dù cell này đã thay đổi dòng cột.
Mình gửi file lên nhờ các bạn tư vấn giúp, hoac sử dụng hàm khác cũng được.
Xin cảm ơn trước nhé

Chuotdong
12-05-11, 06:46 PM
Mình có 2 bảng (ở 2 sheet) trong file đính kèm, mục đích là dò tìm hai chiều đ63 lấy giá trị thỏa điều kiện nhự minh họa đã được tô màu, nhờ các bạn giúp đỡ.
Cái khó của bảng là lấy giá trị theo khoảng ở cả 2 chiều chứ ko phải là giá trị cố định.
Dùng VBA thì chắc nhiều người vẫn làm. Nhưng mình không biết gì về VBA cả.
Còn nếu dùng công thức thì vẫn làm được nhưng đến 3 chiều thì chịu, với 2 chiều tuyến tính nếu quả thực không ai trên diễn đàn làm được(điều này thì mình không nghĩ vậy) nếu cần thiết thì pm mình hướng dẫn thêm

sealand
12-05-11, 07:48 PM
Cái chính là bạn phải nói rõ là làm thế nào đã, ví dụ ngày 17/3/2011 không có thì bạn lấy tỷ giá ở đâu ra USD=20925

quean
19-05-11, 09:04 AM
Cái chính là bạn phải nói rõ là làm thế nào đã, ví dụ ngày 17/3/2011 không có thì bạn lấy tỷ giá ở đâu ra USD=20925

A, mình quên nói, ngày nào không có tỷ giá trên bảng thì mình lấy giá trị max của những ngày trước đó (dòng cuối cùng)

sealand
19-05-11, 10:32 AM
Vậy thì trong dữ liệu của bạn có đến 114 dòng có Value Date< Treasury dealing FX (Sheet: daily Exrate). Vậy nó lấy tỷ giá ở đâu.
Vidụ: Bảng tỷ giá chỉ có từ ngày 21/3/2011 vậy ngày từ 05/01/2011 đến 18/3/2011 biết lấy tỷ giá ở đâu? (Vì nếu tỷ giá không có lấy ngày trước nó)

quean
19-05-11, 11:14 AM
Vậy thì trong dữ liệu của bạn có đến 114 dòng có Value Date< Treasury dealing FX (Sheet: daily Exrate). Vậy nó lấy tỷ giá ở đâu.
Vidụ: Bảng tỷ giá chỉ có từ ngày 21/3/2011 vậy ngày từ 05/01/2011 đến 18/3/2011 biết lấy tỷ giá ở đâu? (Vì nếu tỷ giá không có lấy ngày trước nó)

ôp...... hihi, tại lúc đó mình chưa nghĩ ra công thức này nên mình gõ tay vô mỗi ngày đó

sealand
19-05-11, 01:33 PM
Theo mình bỏ hàm index mà dùng hàm Hlookup gọn hơn (Bảng tỷ giá ngày công bố tỷ giá nếu không thay đổi thì fill ngày trước xuống kẻo công thức nhầm tỷ giá bằng 0
Bạn xem file ví dụ nha

quean
23-05-11, 04:31 PM
Minh lam duoc roi, cam on dien dan nhe

phihndhsp
17-06-11, 03:29 PM
bạn có thể giải thích thêm định dạng có điều kiện những ô màu đó dùm được không? vì mình không hiểu lắm, cảm ơn bạn nhiều

hmtam81
04-11-11, 06:17 PM
Mình có chút vấn đề, các cô các bác có thể júp mình với.
- Bây giờ mình muốn dùng hàm dò tìm dựa vào Mã Cửa Hàng và Shop để lấy Thưởng từ Sheet 1 qua Sheet 2, vậy làm sao các bác.
- Hàm VLOOKUP dò được có 1 điều kiện à, có ai chỉ mình dò dựa vào hàm index và match được không.

Ba Tê
04-11-11, 07:31 PM
Mình có chút vấn đề, các cô các bác có thể júp mình với.
- Bây giờ mình muốn dùng hàm dò tìm dựa vào Mã Cửa Hàng và Shop để lấy Thưởng từ Sheet 1 qua Sheet 2, vậy làm sao các bác.
- Hàm VLOOKUP dò được có 1 điều kiện à, có ai chỉ mình dò dựa vào hàm index và match được không.
Trong file của bạn, mỗi mã cửa hàng chỉ có một Shop (Tên cửa hàng) đâu có mã cửa hàng nào có 2 - 3 tên Shop đâu mà cần 2 điều kiện. Vlookup() theo Mã cửa hàng hoặc bằng Shop cũng được mà.
Nếu thật sự có như vậy thì đưa dữ liệu thật lên đây xem nó thế nào.

quoc_huy1702
19-05-12, 02:33 PM
Mình có nội dung thế này:
-Dựa vào 2 bảng giá sĩ và bảng giá lẻ , thiết lập hàm dò tìm đơn giá dựa vào 2 yếu tố qui cách và tên hàng hóa


Cách mình làm là dùng hàm if. Cho mình hỏi ngoài cách mình sử dụng như file đính kèm. Còn có phương pháp nào khác để được như thê không? ( dùng hàm khác hoặc cách tham chiếu khác; hoặc macro,...)%#^#$

hoamattroicoi
19-05-12, 02:51 PM
Mình có nội dung thế này:
-Dựa vào 2 bảng giá sĩ và bảng giá lẻ , thiết lập hàm dò tìm đơn giá dựa vào 2 yếu tố qui cách và tên hàng hóa


Cách mình làm là dùng hàm if. Cho mình hỏi ngoài cách mình sử dụng như file đính kèm. Còn có phương pháp nào khác để được như thê không? ( dùng hàm khác hoặc cách tham chiếu khác; hoặc macro,...)%#^#$
Hem nghĩ ra cách nào không dùng If nhưng có cách khác bạn dùng Offset thế này xem :

=IF(F22="Sĩ",OFFSET(quicach,MATCH(D22,$D$5:$D$8,0),MATCH(E22,$ E$4:$I$4,0),1,1),OFFSET($D$12:$I$12,MATCH(D22,$D$1 3:$D$16,0),MATCH(E22,$E$12:$I$12,0),1,1))

vodoi2x
19-05-12, 08:29 PM
Vẫn là IF, nhưng CT ngắn hơn chút


=INDEX(IF(F22="Lẻ",$D$12:$I$16,$D$4:$I$8),MATCH(D22,tenhh,0),MATCH(E 22,quicach,0))

Lê Duy Thương
19-05-12, 09:21 PM
Hem nghĩ ra cách nào không dùng If nhưng có cách khác bạn dùng Offset thế này xem :

=IF(F22="Sĩ",OFFSET(quicach,MATCH(D22,$D$5:$D$8,0),MATCH(E22,$ E$4:$I$4,0),1,1),OFFSET($D$12:$I$12,MATCH(D22,$D$1 3:$D$16,0),MATCH(E22,$E$12:$I$12,0),1,1))
không có if nè còi . note :chỉ sử dụng cho bài này

=VLOOKUP($D22,OFFSET($D$2,MATCH("*"&$F22,$C$2:$C$16,0),,6,7),MATCH($E22,$D$4:$I$4,0),0 )

jimmytransi
04-07-12, 04:35 PM
Bạn oi,
Công thức này như thế nào vậy?? Bạn có thể chỉ giúp mình không?

arexthena
26-10-12, 04:22 PM
Hi, bạn ơi, thấy cách trang trí của bạn hay quá, bạn có thể chỉ cho mình cách làm sao khi mình thay đổi giá trị trong các ô thì màu trong bảng nhảy lên nhảy xuống như vậy được không. Thanks bạn nhiều!


To Songlon
Thấy file của bạn hay hay nên tiện tay mình trang trí thêm tí.

TDN

OZOCUONG
15-12-12, 09:49 PM
Anh ơi cái file Match2 của anh em thấy hay quá anh cho em hỏi xíu : làm sao để có thể bôi được màu khi anh chọn cái list sổ xuống vậy , chỉ em với nhé anh.

alic_menk
23-05-13, 04:18 PM
SAo pass mở không được vậy bạn Hai tuan