Dùng các hàm tìm kiếm (Excel 2016) kết hợp với điều kiện (Data Validation) để trích xuất dữ liệu.

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Chào các Thầy, Cô, Anh, Chị trên diễn đàn GPE,

Như tiêu đề ở trên, mình chân thành kính nhờ các Thầy, Cô, quý Anh, Chị trên diễn đàn GPE giúp đỡ, cụ thể :

Mình có 01 file (vui lòng xem file đính kèm) gồm có các sheet : Tham khảo, Tổng hợp, Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Tham khảo dùng để tạo dữ liệu nguồn cho Data Validation (được dùng ở các sheet Trích xuất dữ liệu (1), (2), (3) và (4)).

Sheet Tổng hợp là nguồn dữ liệu chính để sử dụng các hàm tìm kiếm tham chiếu và lấy dữ liệu cho sheet Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Trích xuất dữ liệu (1), (2), (3) và (4) ở đây mình tạo ra, để cho các Thầy, Cô, quý Anh, Chị dễ hiểu cho từng trường hợp trích xuất dữ liệu (dựa vào điều kiện các Data Validation). Thực tế, chỉ cần 01 sheet Trích xuất dữ liệu thôi.

- Dựa vào các điều kiện Data Validation và sheet Tổng hợp, mình kính mong các Thầy, Cô, quý Anh, Chị giúp đỡ, chỉ cho mình biết công thức dùng các hàm tìm kiếm (Excel 2016) để trích xuất dữ liệu.

Chân thành cám ơn quý Thầy, Cô, Anh, Chị.
 

File đính kèm

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Mình kính mong quý Thầy, Cô, các Anh, Chị giúp đỡ. Cám ơn.
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,468
Được thích
12,124
Điểm
1,560
Chào các Thầy, Cô, Anh, Chị trên diễn đàn GPE,

Như tiêu đề ở trên, mình chân thành kính nhờ các Thầy, Cô, quý Anh, Chị trên diễn đàn GPE giúp đỡ, cụ thể :

Mình có 01 file (vui lòng xem file đính kèm) gồm có các sheet : Tham khảo, Tổng hợp, Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Tham khảo dùng để tạo dữ liệu nguồn cho Data Validation (được dùng ở các sheet Trích xuất dữ liệu (1), (2), (3) và (4)).

Sheet Tổng hợp là nguồn dữ liệu chính để sử dụng các hàm tìm kiếm tham chiếu và lấy dữ liệu cho sheet Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Trích xuất dữ liệu (1), (2), (3) và (4) ở đây mình tạo ra, để cho các Thầy, Cô, quý Anh, Chị dễ hiểu cho từng trường hợp trích xuất dữ liệu (dựa vào điều kiện các Data Validation). Thực tế, chỉ cần 01 sheet Trích xuất dữ liệu thôi.

- Dựa vào các điều kiện Data Validation và sheet Tổng hợp, mình kính mong các Thầy, Cô, quý Anh, Chị giúp đỡ, chỉ cho mình biết công thức dùng các hàm tìm kiếm (Excel 2016) để trích xuất dữ liệu.

Chân thành cám ơn quý Thầy, Cô, Anh, Chị.
Bạn kiểm tra file
 

File đính kèm

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,898
Điểm
860
Nơi ở
Sài Gòn
Chào các Thầy, Cô, Anh, Chị trên diễn đàn GPE,

Như tiêu đề ở trên, mình chân thành kính nhờ các Thầy, Cô, quý Anh, Chị trên diễn đàn GPE giúp đỡ, cụ thể :

Mình có 01 file (vui lòng xem file đính kèm) gồm có các sheet : Tham khảo, Tổng hợp, Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Tham khảo dùng để tạo dữ liệu nguồn cho Data Validation (được dùng ở các sheet Trích xuất dữ liệu (1), (2), (3) và (4)).

Sheet Tổng hợp là nguồn dữ liệu chính để sử dụng các hàm tìm kiếm tham chiếu và lấy dữ liệu cho sheet Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Trích xuất dữ liệu (1), (2), (3) và (4) ở đây mình tạo ra, để cho các Thầy, Cô, quý Anh, Chị dễ hiểu cho từng trường hợp trích xuất dữ liệu (dựa vào điều kiện các Data Validation). Thực tế, chỉ cần 01 sheet Trích xuất dữ liệu thôi.

- Dựa vào các điều kiện Data Validation và sheet Tổng hợp, mình kính mong các Thầy, Cô, quý Anh, Chị giúp đỡ, chỉ cho mình biết công thức dùng các hàm tìm kiếm (Excel 2016) để trích xuất dữ liệu.

Chân thành cám ơn quý Thầy, Cô, Anh, Chị.
Góp thêm cách khác cho phong phú với bạn hiền:
1. Tạo các name sau:

PHP:
SoCTrenN=IFERROR(IFERROR(RIGHT('Trich xuat du lieu'!$C$4,2)*1,RIGHT('Trich xuat du lieu'!$C$4,1)*1),0)
SoCDuoiN=IFERROR(IFERROR(RIGHT('Trich xuat du lieu'!$D$4,2)*1,RIGHT('Trich xuat du lieu'!$D$4,1)*1),0)
SoCTrenX=IFERROR(IFERROR(RIGHT('Trich xuat du lieu'!$H$4,2)*1,RIGHT('Trich xuat du lieu'!$H$4,1)*1),0)
SoCDuoiX=IFERROR(IFERROR(RIGHT('Trich xuat du lieu'!$I$4,2)*1,RIGHT('Trich xuat du lieu'!$I$4,1)*1),0)
PHP:
CanTrenN=IF(ISERROR(FIND(">=",'Trich xuat du lieu'!$C$4)),IF(NOT(ISERROR(FIND(">",'Trich xuat du lieu'!$C$4)))+('Trich xuat du lieu'!$C$4="")*('Trich xuat du lieu'!$D$4<>""),VungSLN>SoCTrenN,VungSLN=SoCTrenN),VungSLN>=SoCTrenN)
CanDuoiN=IF(ISERROR(FIND("<=",'Trich xuat du lieu'!$D$4)),IF(NOT(ISERROR(FIND("<",'Trich xuat du lieu'!$D$4)))+('Trich xuat du lieu'!$C$4<>"")*('Trich xuat du lieu'!$D$4=""),VungSLN<SoCDuoiN+('Trich xuat du lieu'!$D$4="")*100,VungSLN=SoCDuoiN),VungSLN<=SoCDuoiN)
CanTrenX=IF(ISERROR(FIND(">=",'Trich xuat du lieu'!$H$4)),IF(NOT(ISERROR(FIND(">",'Trich xuat du lieu'!$H$4)))+('Trich xuat du lieu'!$H$4="")*('Trich xuat du lieu'!$I$4<>""),VungSLX>SoCTrenX,VungSLX=SoCTrenX),VungSLX>=SoCTrenX)
CanDuoiX=IF(ISERROR(FIND("<=",'Trich xuat du lieu'!$I$4)),IF(NOT(ISERROR(FIND("<",'Trich xuat du lieu'!$I$4)))+('Trich xuat du lieu'!$H$4<>"")*('Trich xuat du lieu'!$I$4=""),VungSLX<SoCDuoiX+('Trich xuat du lieu'!$I$4="")*100,VungSLX=SoCDuoiX),VungSLX<=SoCDuoiX)
2. Công thức Mảng cho Vùng kết quả:
PHP:
B7=IFERROR(OFFSET('Tong hop'!B$4,AGGREGATE(15,6,ROW(OFFSET('Tong hop'!$A$1,,,COUNTA('Tong hop'!$B$5:$B$2000)))/(((VungNX=$C$3)*CantrenN*CanduoiN)+((VungNX=$H$3)*CantrenX*CanduoiX)),ROW($A1)),),"")
Enter, copy qua phải, rồi fill xuống toàn bộ.
Khi thay đổi C4, D4, H4, I4 thì số liệu sẽ được trích lọc theo.

Chúc anh em ngày thiệt vui.
 

File đính kèm

Lần chỉnh sửa cuối:

be09

TNMT_Đồng Nai
Tham gia ngày
9 Tháng tư 2011
Bài viết
8,870
Được thích
8,549
Điểm
560
Tuổi
62
Nơi ở
Biên Hòa, Đồng Nai
Chào các Thầy, Cô, Anh, Chị trên diễn đàn GPE,

Như tiêu đề ở trên, mình chân thành kính nhờ các Thầy, Cô, quý Anh, Chị trên diễn đàn GPE giúp đỡ, cụ thể :

Mình có 01 file (vui lòng xem file đính kèm) gồm có các sheet : Tham khảo, Tổng hợp, Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Tham khảo dùng để tạo dữ liệu nguồn cho Data Validation (được dùng ở các sheet Trích xuất dữ liệu (1), (2), (3) và (4)).

Sheet Tổng hợp là nguồn dữ liệu chính để sử dụng các hàm tìm kiếm tham chiếu và lấy dữ liệu cho sheet Trích xuất dữ liệu (1), (2), (3) và (4).

Sheet Trích xuất dữ liệu (1), (2), (3) và (4) ở đây mình tạo ra, để cho các Thầy, Cô, quý Anh, Chị dễ hiểu cho từng trường hợp trích xuất dữ liệu (dựa vào điều kiện các Data Validation). Thực tế, chỉ cần 01 sheet Trích xuất dữ liệu thôi.

- Dựa vào các điều kiện Data Validation và sheet Tổng hợp, mình kính mong các Thầy, Cô, quý Anh, Chị giúp đỡ, chỉ cho mình biết công thức dùng các hàm tìm kiếm (Excel 2016) để trích xuất dữ liệu.

Chân thành cám ơn quý Thầy, Cô, Anh, Chị.
Theo dõi gì mà rối như canh hẹ.
Trích xuất dữ liệu (1), (2), (3) và (4) làm gì phải theo dõi nhiều thế.
 

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Bạn Quocgiacan oi, file của bạn gửi lên diễn đàn GPE, mình đang trong công ty đang dùng Excel 2007, mình vui mừng mở file của bạn và của anh HieuCD lên liền thì file anh Hiếu hiện kết quả (số, danh sách, ...), còn file của bạn mở lên thì không hiện kết quả như mong muốn (mặc dù trong các ô của sheet Trích lọc dữ liệu còn các công thức của bạn). Sau một hồi tìm hiểu suy nghĩ các kiểu thì mới thấy trong file của bạn có công thức mới (công thức đó chỉ có Excel 2016 mới phát huy sức mạnh của nó, còn máy tính trong công ty của mình thì cùi bắp (ý là đang dùng Excel 2007)). Thôi thì để về nhà mình mở file của bạn bằng Excel 2016 thôi.
Mình nghiêng mình cúi đầu khâm phục trí tuệ về Excel của các bạn, các Anh.
Trân trọng.
 

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,898
Điểm
860
Nơi ở
Sài Gòn
Bạn Quocgiacan oi, file của bạn gửi lên diễn đàn GPE, mình đang trong công ty đang dùng Excel 2007, mình vui mừng mở file của bạn và của anh HieuCD lên liền thì file anh Hiếu hiện kết quả (số, danh sách, ...), còn file của bạn mở lên thì không hiện kết quả như mong muốn (mặc dù trong các ô của sheet Trích lọc dữ liệu còn các công thức của bạn). Sau một hồi tìm hiểu suy nghĩ các kiểu thì mới thấy trong file của bạn có công thức mới (công thức đó chỉ có Excel 2016 mới phát huy sức mạnh của nó, còn máy tính trong công ty của mình thì cùi bắp (ý là đang dùng Excel 2007)). Thôi thì để về nhà mình mở file của bạn bằng Excel 2016 thôi.
Mình nghiêng mình cúi đầu khâm phục trí tuệ về Excel của các bạn, các Anh.
Trân trọng.
Đúng vậy, vì hàm Aggregate() chỉ hoạt động được từ excel phiên bản >=2010. Do thấy bạn 'vui mừng' hê lên mình đang xài 2016, nên nghĩ sẽ không có sự cố gì.

Còn cái vụ 'phía dưới', thì không có gì ghê gớm đâu bạn, từ từ rồi bạn cũng sẽ đạt được trong tương lai gần, nếu bạn biết nhẫn nại và chịu khó tìm hiểu.

Chung quy cũng chỉ là "biết và chưa biết" mà thôi.

Chúc bạn ngày vui.
 
Lần chỉnh sửa cuối:

be09

TNMT_Đồng Nai
Tham gia ngày
9 Tháng tư 2011
Bài viết
8,870
Được thích
8,549
Điểm
560
Tuổi
62
Nơi ở
Biên Hòa, Đồng Nai

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,468
Được thích
12,124
Điểm
1,560
Bạn Quocgiacan oi, file của bạn gửi lên diễn đàn GPE, mình đang trong công ty đang dùng Excel 2007, mình vui mừng mở file của bạn và của anh HieuCD lên liền thì file anh Hiếu hiện kết quả (số, danh sách, ...), còn file của bạn mở lên thì không hiện kết quả như mong muốn (mặc dù trong các ô của sheet Trích lọc dữ liệu còn các công thức của bạn). Sau một hồi tìm hiểu suy nghĩ các kiểu thì mới thấy trong file của bạn có công thức mới (công thức đó chỉ có Excel 2016 mới phát huy sức mạnh của nó, còn máy tính trong công ty của mình thì cùi bắp (ý là đang dùng Excel 2007)). Thôi thì để về nhà mình mở file của bạn bằng Excel 2016 thôi.
Mình nghiêng mình cúi đầu khâm phục trí tuệ về Excel của các bạn, các Anh.
Trân trọng.
bạn chỉnh công thức lại cho gọn
Mã:
L1 =--MID(C4,L2+1,3)
copy ô L1 dán vào ô M1
Mã:
L3 =--MID(H4,L4+1,3)
copy dán vào M3
 

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
bạn chỉnh công thức lại cho gọn
Mã:
L1 =--MID(C4,L2+1,3)
copy ô L1 dán vào ô M1
Mã:
L3 =--MID(H4,L4+1,3)
copy dán vào M3
Cám ơn bạn HieuCD. Đúng là công thức bạn kêu mình chỉnh lại, có phần gọn hơn và mình dễ hiểu hơn ở chỗ công thức chỉnh sửa này.
 

nguoi_ham_hoc

Thành viên hoạt động
Tham gia ngày
1 Tháng hai 2017
Bài viết
125
Được thích
83
Điểm
180
Mình cũng cám ơn sự trợ giúp thêm 01 cách giải và nhiệt tình của bạn Thiên thần Bóng đêm (DarkAngel). Tất cả những bài giúp đỡ của các Anh, Chị, các bạn, mình hân hoan đều đón nhận hết. Mỗi một cách giải là những bài học, những kinh nghiệm quý giá cho mình và các bạn chưa rành về excel tích lũy kinh nghiệm và học hỏi thêm.
Đúng như câu : Không thầy đố mày làm nên.
Và : Học thầy không tầy (bằng) học bạn.
Cách giải của bạn Thiên thần Bóng đêm hay. Hình như cách giải của bạn ít cột phụ, thông tin phụ hơn thì phải (theo mình mới liếc nhìn).
Chân thành cám ơn.
 

dazkangel

<New Horizons>
Tham gia ngày
28 Tháng hai 2017
Bài viết
2,751
Được thích
3,450
Điểm
360
Nơi ở
Đồng Nai
Mình cũng cám ơn sự trợ giúp thêm 01 cách giải và nhiệt tình của bạn Thiên thần Bóng đêm (DarkAngel). Tất cả những bài giúp đỡ của các Anh, Chị, các bạn, mình hân hoan đều đón nhận hết. Mỗi một cách giải là những bài học, những kinh nghiệm quý giá cho mình và các bạn chưa rành về excel tích lũy kinh nghiệm và học hỏi thêm.
Đúng như câu : Không thầy đố mày làm nên.
Và : Học thầy không tầy (bằng) học bạn.
Cách giải của bạn Thiên thần Bóng đêm hay. Hình như cách giải của bạn ít cột phụ, thông tin phụ hơn thì phải (theo mình mới liếc nhìn).
Chân thành cám ơn.
File có nhầm 1 chút, bạn xem lại:
 

File đính kèm

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,898
Điểm
860
Nơi ở
Sài Gòn
File có nhầm 1 chút, bạn xem lại:
Còn thiếu chưa xử lý 1 số trường hợp nữa em, như: Phát sinh của Phiếu Nhập Xuất: Có cận trên nhưng không có cận dưới, hoặc ngược lại; Và chỉ khi không có cận nào thì không phát sinh, tức không hiện dòng dữ liệu cho Phiếu đó.

Ví dụ:
  1. Phiếu Nhập/Xuất: cận trên: >0, cận dưới: trống rỗng--> hiện tất cả Phiếu Nhập/Xuất.
  2. Phiếu Nhập/Xuất: cận trên: trống rỗng, cận dưới <=3--> hiện Phiếu Nhập/Xuất tương ứng <=3.
Chúc anh em ngày vui.
 

dazkangel

<New Horizons>
Tham gia ngày
28 Tháng hai 2017
Bài viết
2,751
Được thích
3,450
Điểm
360
Nơi ở
Đồng Nai
Còn thiếu chưa xử lý 1 số trường hợp nữa em, như: Phát sinh của Phiếu Nhập Xuất: Có cận trên nhưng không có cận dưới, hoặc ngược lại; Và chỉ khi không có cận nào thì không phát sinh, tức không hiện dòng dữ liệu cho Phiếu đó.

Ví dụ:
  1. Phiếu Nhập/Xuất: cận trên: >0, cận dưới: trống rỗng--> hiện tất cả Phiếu Nhập/Xuất.
  2. Phiếu Nhập/Xuất: cận trên: trống rỗng, cận dưới <=3--> hiện Phiếu Nhập/Xuất tương ứng <=3.
Chúc anh em ngày vui.
Hi em đã nghĩ tới và làm thêm 1 cột phụ nhưng thôi ^^, của em làm đơn giản nên nhập và xuất phải chọn ô nhất định :D
Chúc anh ngày vui.
 

quocgiacan

-⍟-
Tham gia ngày
22 Tháng ba 2008
Bài viết
3,397
Được thích
5,898
Điểm
860
Nơi ở
Sài Gòn
Hi em đã nghĩ tới và làm thêm 1 cột phụ nhưng thôi ^^, của em làm đơn giản nên nhập và xuất phải chọn ô nhất định :D
Chúc anh ngày vui.
Đã làm thì phải tính cho hết mọi động thái của người sử dụng chứ.
Đâu cần thêm cột phụ chỉ cần tạo name cho các cận trên dưới của Nhập Xuất là được.

Chúc anh em ngày vui.
 

File đính kèm

dazkangel

<New Horizons>
Tham gia ngày
28 Tháng hai 2017
Bài viết
2,751
Được thích
3,450
Điểm
360
Nơi ở
Đồng Nai
Đã làm thì phải tính cho hết mọi động thái của người sử dụng chứ.
Đâu cần thêm cột phụ chỉ cần tạo name cho các cận trên dưới của Nhập Xuất là được.

Chúc anh em ngày vui.
Hi, tại bài này anh với anh HieuCD làm rồi, em góp vui thôi ạ.
Cái cận trên dưới này để em học thêm đã :D
 
Top Bottom