Tham chiếu và tìm kiếm ? (1 người xem)

[Hoàng Nhật Phương]

Xin chào tất cả các bạn,
Không biết tiêu đề O.Thơ đặt như vậy đã đúng hay chưa?
Nhưng vấn đề O.Thơ mong muốn đã nêu trong file kèm.Rất mong các bạn giúp đỡ.

Xin cảm ơn.

 

[giaiphap]

Xin chào tất cả các bạn,
Không biết tiêu đề O.Thơ đặt như vậy đã đúng hay chưa?
Nhưng vấn đề O.Thơ mong muốn đã nêu trong file kèm.Rất mong các bạn giúp đỡ.

Xin cảm ơn.

Bài toán này sẽ dễ hơn nếu các sheet có cấu trúc giống nhau, còn kiểu này thì mình thua chắc luôn.

 

[giaiphap]

Không gì là không thể...Chỉ sợ làm không được...kaka...& làm biếng mà thôi!

P/s: Cấu trúc tất nhiên là phải giống nhau rồi...Họ lại chơi đặt ở dòng/cột tùm lum không ở một chổ...: Và bắt buộc trên sheet phải có 1 cụm từ là "Tên SV" ở 1 cell nào đó...Nếu xuất hiện cụm thứ 2 trên sheet này thì tèo...ah nha...haha...
Nếu tìm được địa chỉ của cell chứa cụm này rồi thì Set vùng là 4 dòng+2 cột sau cell này là ra ngay thôi...

 B2 =IFERROR(OFFSET(INDIRECT("'SV"&$A2&"'!"&ADDRESS(SUMPRODUCT((INDIRECT("'SV"&$A2&"'!A1:AZ1000")="Tên SV")*ROW($A$1:$AZ$1000 )),SUMPRODUCT((INDIRECT("'SV"&$A2&"'!A1:AZ1000")="Tên SV")*COLUMN($A$1:$AZ$1000)))),COLUMN()-1,1,,),"")

Đáng học hỏi, cảm ơn bạn cách làm hay quá, nhưng có lẽ sẽ nghiên cứu lại sau. Tôi thấy bạn mới tốt nghiệp mẫu giáo sao lại trả lời nhiều bài viết hay quá.

 

[be_09]

Xin chào tất cả các bạn,
Không biết tiêu đề O.Thơ đặt như vậy đã đúng hay chưa?
Nhưng vấn đề O.Thơ mong muốn đã nêu trong file kèm.Rất mong các bạn giúp đỡ.

Xin cảm ơn.

Sao thích làm ngược vậy:

Theo tôi thì chỉ cần 1 sheet SV nhập dữ liệu vào sheet Thong ke, muốn cái gì thì truy vấn ngược lại sheet SV.

Làm như bạn thì không lẽ 1 trường có cả ngàn SV lại làm cả ngàn sheet.

 

[Hoàng Nhật Phương]

Chạy code có được không?

Option Explicit


Public Sub GPE()
Dim Rng As Range, Ws As Worksheet, Arr, dArr
Dim I As Long, K As Long
ReDim dArr(1 To Sheets.Count, 1 To 5)
For Each Ws In Worksheets
If Ws.Name <> "Thong ke" Then
    Set Rng = Ws.Cells.Find("Tên SV", , xlValues, xlWhole, , , True)
    K = K + 1
    Arr = Rng(2).Resize(4, 2).Value
    For I = 1 To UBound(Arr)
        dArr(K, 1) = K
        dArr(K, I + 1) = Arr(I, 2)
    Next I
End If
Next Ws
With Sheets("Thong ke")
    .Range("A2:E9").ClearContents
    .Range("A2").Resize(K, 5).Value = dArr
End With
End Sub

Bài này O.Thơ muốn dùng công thức thôi hpkhuong ạ.
Tuy nhiên càng có nhiều giải pháp O.Thơ càng dễ lựa chọn hơn ạ.
-------

Không gì là không thể...Chỉ sợ làm không được...kaka...& làm biếng mà thôi!

P/s: Cấu trúc tất nhiên là phải giống nhau rồi...Họ lại chơi đặt ở dòng/cột tùm lum không ở một chổ...: Và bắt buộc trên sheet phải có 1 cụm từ là "Tên SV" ở 1 cell nào đó...Nếu xuất hiện cụm thứ 2 trên sheet này thì tèo...ah nha...haha...
Nếu tìm được địa chỉ của cell chứa cụm này rồi thì Set vùng là 4 dòng+2 cột sau cell này là ra ngay thôi...

 B2 =IFERROR(OFFSET(INDIRECT("'SV"&$A2&"'!"&ADDRESS(SUMPRODUCT((INDIRECT("'SV"&$A2&"'!A1:AZ1000")="Tên SV")*ROW($A$1:$AZ$1000 )),SUMPRODUCT((INDIRECT("'SV"&$A2&"'!A1:AZ1000")="Tên SV")*COLUMN($A$1:$AZ$1000)))),COLUMN()-1,1,,),"")

Kết quả OK rồi , thật tuyệt vời!
Cảm ơn hpkhuong nhiều nhé !

 

[Hoàng Nhật Phương]

Sao thích làm ngược vậy:

Theo tôi thì chỉ cần 1 sheet SV nhập dữ liệu vào sheet Thong ke, muốn cái gì thì truy vấn ngược lại sheet SV.

Làm như bạn thì không lẽ 1 trường có cả ngàn SV lại làm cả ngàn sheet.

O.Thơ hỏi bài hộ bạn be09 ạ.
Cảm ơn bạn đã góp ý ạ.

 

[SA_DQ]

Đúng là xây dựng CSDL như vậy là tùy tiện & không bình thường. Bất kì lý do gì đưa ra cũng là ngụy biện mà thôi!

 

[jindodiep]

có thể nói bài của O.Thơ rất hay. VD bạn đi làm sẽ gặp những vấn đề tương tự khi bạn tổng kết dữ liệu cho boss từ nhiều đối tác khác nhau và 1 đối tác sẽ có 1 cách trình bày khác nhau nhưng sẽ có 1 số điểm chung và sếp bạn đang cần điểm chung đó. Để si ngẫm roài post kế quả .

 

[Hoàng Nhật Phương]

Tìm kiếm ?

Xin chào các bạn,
Các bạn giúp Oanh Thơ công thức cho ô màu vàng trong file đính kèm này với ạ.

 

[befaint]

Xin chào các bạn,
Các bạn giúp Oanh Thơ công thức cho ô màu vàng trong file đính kèm này với ạ.

L9=VLOOKUp($K$9,OFFSET($F$1,MATCH($J$9,$E$1:$E$20,0)-1,0,100,2),2,0)

 

[Ba Tê]

Xin chào các bạn,
Các bạn giúp Oanh Thơ công thức cho ô màu vàng trong file đính kèm này với ạ.

Thấy sao "mần" vậy nghe. Áp dụng vào dữ liệu thật được hay không thì hổng biết.

=INDEX(OFFSET($G$5;MATCH(J9;$E$6:$E$20;0);;5);--RIGHT(K9);)

 

[Ba Tê]

L9=VLOOKUp($K$9,OFFSET($F$1,MATCH($J$9,$E$1:$E$20,0)-1,0,100,2),2,0)

Viết hàm "thủ thả" nhiều vậy, ngắn lại chút để cho tác giả tự "điều chỉnh" nữa chứ bạn.

=VLOOKUP(K9;OFFSET($F$5;MATCH(J9;$E$6:$E$20;0);;5;2);2;0)

 

[Hoàng Nhật Phương]

Viết hàm "thủ thả" nhiều vậy, ngắn lại chút để cho tác giả tự "điều chỉnh" nữa chứ bạn.

=VLOOKUP(K9;OFFSET($F$5;MATCH(J9;$E$6:$E$20;0);;5;2);2;0)

Cảm ơn befaint , cảm ơn Ba Tê
Với công thức kết hợp này của 2 bạn tôi đã áp dụng được cho file thực của mình kết quả đã OK.
Xin cảm ơn 2 bạn rất nhiều.

--
Tôi muốn hỏi mở rộng 1 chút trong trường hợp chỉ có 1 điều kiện, bài toán dạng như vlookup lệch dòng như file đính kèm thì công thức sẽ như thế nào ạ:

 

[eke_rula]

Cảm ơn befaint , cảm ơn Ba Tê
Với công thức kết hợp này của 2 bạn tôi đã áp dụng được cho file thực của mình kết quả đã OK.
Xin cảm ơn 2 bạn rất nhiều.

--
Tôi muốn hỏi mở rộng 1 chút trong trường hợp chỉ có 1 điều kiện, bài toán dạng như vlookup lệch dòng như file đính kèm thì công thức sẽ như thế nào ạ:

Nếu dò tìm một đk là "c" thì có nhiều kết quả( 1100,1200,1300,1400...) , bạn phải cho biết căn cứ vào đâu để lấy số 1300 , ví dụ vị trí thứ 3, hay nhỏ thứ 3 chặng hạn, chứ 1 điều kiện vlookup nó lấy giá trị đầu tiên rồi!!!

 

[Hoàng Nhật Phương]

Nếu dò tìm một đk là "c" thì có nhiều kết quả( 1100,1200,1300,1400...) , bạn phải cho biết căn cứ vào đâu để lấy số 1300 , ví dụ vị trí thứ 3, hay nhỏ thứ 3 chặng hạn, chứ 1 điều kiện vlookup nó lấy giá trị đầu tiên rồi!!!

ừ nhỉ,, hihi cảm ơn eke_rula đã nhắc
điều kiện lệch 3 dòng xuống dưới so với từ khóa tìm kiếm ạ.

 

[eke_rula]

ừ nhỉ,, hihi cảm ơn eke_rula đã nhắc
điều kiện lệch 3 dòng xuống dưới so với từ khóa tìm kiếm ạ.

Nếu vậy bạn có thể dùng như thế này cũng được:

=VLOOKUP(J9,CHOOSE({1,2},$E$6:$E$20,$G$8:$G$22),2,0)
Hoặc
=OFFSET($G$5,MATCH(J9,$E$6:$E$20,)+2,)

2 Công thức này vẫn có chỗ sai vì làm theo file của bạn nên sẽ đúng, nếu a,b,c chỉ có 1 hoặc 2 kết quả thì công thức sẽ không đúng, nếu bạn cần CT tổng quát mình sẽ viết lại cho bạn!!!

 

[Hoàng Nhật Phương]

Nếu vậy bạn có thể dùng như thế này cũng được:

=VLOOKUP(J9,CHOOSE({1,2},$E$6:$E$20,$G$8:$G$22),2,0)
Hoặc
=OFFSET($G$5,MATCH(J9,$E$6:$E$20,)+2,)

2 Công thức này vẫn có chỗ sai vì làm theo file của bạn nên sẽ đúng, nếu a,b,c chỉ có 1 hoặc 2 kết quả thì công thức sẽ không đúng, nếu bạn cần CT tổng quát mình sẽ viết lại cho bạn!!!

Cảm ơn eke_rula, tổng quát bao giờ cũng là tối ưu nhất.
Vậy làm phiền bạn 1 lần nữa nhé.

 

[eke_rula]

Cảm ơn eke_rula, tổng quát bao giờ cũng là tối ưu nhất.
Vậy làm phiền bạn 1 lần nữa nhé.

Vậy bạn dùng CT này:

=INDEX(IF(LOOKUP(ROW(E6:E17),IF(E6:E17<>"",ROW(E6:E17)),E6:E17)=J9,G6:G17,"Khong co so lieu"),MATCH(J9,E6:E17,)[SIZE=4][COLOR=#ff0000][B]+2[/B][/COLOR][/SIZE])

Bấm Ctrl+Shift+Enter
Chỗ màu đỏ để tính lệch dòng, lệch 3 dòng thì sửa là +2 , lệch 4 thì +3, lệch 2 thì+1....

 

[Hoàng Nhật Phương]

Vậy bạn dùng CT này:

=INDEX(IF(LOOKUP(ROW(E6:E17),IF(E6:E17<>"",ROW(E6:E17)),E6:E17)=J9,G6:G17,"Khong co so lieu"),MATCH(J9,E6:E17,)[SIZE=4][COLOR=#ff0000][B]+2[/B][/COLOR][/SIZE])

Bấm Ctrl+Shift+Enter
Chỗ màu đỏ để tính lệch dòng, lệch 3 dòng thì sửa là +2 , lệch 4 thì +3, lệch 2 thì+1....

Cảm ơn eke_rula, kết quả OK rồi.