Lọc dữ liệu trùng từ 2 cột và tính tổng

Liên hệ QC

hocexcel_1991

Bắt đầu học Excel
Tham gia
9/7/18
Bài viết
759
Được thích
1,315
Chào anh/chị
Nhờ anh/chị giúp em bài Lọc dữ liệu trùng từ 2 cột và tính tổng theo như file đính kèm nhé.
Xin cám ơn.
 

File đính kèm

  • Book1.xlsx
    8.4 KB · Đọc: 23
Cám ơn anh nhiều, nhìn vào công thức mà em thấy "choáng":):)
Chúc anh buổi tối vui vẻ
Anh biết là: em, @congnt92@khi ta 20 đang học hỏi về "Công thức Mảng" nên cố tình giải bài và cắt nghĩa đôi chút để các em tham khảo.
Tìm lại các nickname sau sẽ học được nhiều về công thức Mảng:
4/ @HieuCD
Chúc anh em ngày vui.
Thân
 
Anh biết là: em, @congnt92@khi ta 20 đang học hỏi về "Công thức Mảng" nên cố tình giải bài và cắt nghĩa đôi chút để các em tham khảo.
Tìm lại các nickname sau sẽ học được nhiều về công thức Mảng:
4/ @HieuCD
Chúc anh em ngày vui.
Thân
Chào anh em xin góp vui:
Mã:
H10=IFERROR(INDIRECT(MID(REPT(TEXT(AGGREGATE(15,6,(COLUMN(A:D)*1000+ROW($2:$6))/ISTEXT(A$2:D$6)/(COUNTIF(H$9:H9,A$2:D$6)=0),1),"C000R000"),2),5,8),0),"")
 
Chào anh em xin góp vui:
Mã:
H10=IFERROR(INDIRECT(MID(REPT(TEXT(AGGREGATE(15,6,(COLUMN(A:D)*1000+ROW($2:$6))/ISTEXT(A$2:D$6)/(COUNTIF(H$9:H9,A$2:D$6)=0),1),"C000R000"),2),5,8),0),"")
Để tăng thêm phần khó cho bài, anh đính kèm dữ liệu theo file với hai yêu cầu:
1/ Lấy TEN, với sắp xếp TEN từ A->Z. (Như các cách tại bài trên, chỉ lấy theo thứ tự cột, chứ chưa lấy theo Alphabet)
2/ Lấy TEN, với SL tổng của từng tên được sắp xếp từ Lớn đến Nhỏ.

Dĩ nhiên, là không dùng cột phụ.
Chúc anh em ngày vui.
/-*+//-*+//-*+/
 

File đính kèm

  • Cauhoi.xlsx
    10.8 KB · Đọc: 48
Lần chỉnh sửa cuối:
Cột H là "nghề" của bác ở Can Duoc ;)
Đúng vậy! Tính tình ảnh hiền hòa, đơn giản dễ thương, nên cách xử lý bài của ảnh cũng không rối rắm phức tạp.
Mấy nay, không thấy anh @HieuCD vào, chắc lại lo cho đàn em thân yêu rồi, quên đàn em GPE!
Khà khà khà!
/-*+//-*+//-*+/
 
Để tăng thêm phần khó cho bài, anh đính kèm dữ liệu theo file với hai yêu cầu:
1/ Lấy TEN, với sắp xếp TEN từ A->Z. (Như các cách tại bài trên, chỉ lấy theo thứ tự cột, chứ chưa lấy theo Alphabet)
2/ Lấy TEN, với SL tổng của từng tên được sắp xếp từ Lớn đến Nhỏ.

Dĩ nhiên, là không dùng cột phụ.
Chúc anh em ngày vui.
/-*+//-*+//-*+/
Có bài hơi tương tự theo link: https://www.giaiphapexcel.com/diendan/threads/tự-động-liệt-kê-và-sắp-xếp-dữ-liệu.136579/#post-871935
Nhưng mức độ còn khó hơn nữa nè.
Anh em xem thử và giải giúp cho chủ thớt.

Chúc anh em ngày vui.
 
Để tăng thêm phần khó cho bài, anh đính kèm dữ liệu theo file với hai yêu cầu:
1/ Lấy TEN, với sắp xếp TEN từ A->Z. (Như các cách tại bài trên, chỉ lấy theo thứ tự cột, chứ chưa lấy theo Alphabet)
2/ Lấy TEN, với SL tổng của từng tên được sắp xếp từ Lớn đến Nhỏ.

Dĩ nhiên, là không dùng cột phụ.
Chúc anh em ngày vui.
/-*+//-*+//-*+/
Cho tôi thử:
Mã:
J10=INDIRECT(TEXT(MIN(IF(((COUNTIF($A$2:$C$6,"<"&$A$2:$C$6)+1-SUM(COUNTIF($A$2:$C$6,J$9:J9)))*{1,0,1}=1),(ROW($2:$6)*10^2+{1,0,3}))),"R0C00"),)
Bấm Ctrl+Shift+Enter, copy xuống, trường hợp còn lại, sửa ">" thành "<"!!!
 
Cho tôi thử:
Mã:
J10=INDIRECT(TEXT(MIN(IF(((COUNTIF($A$2:$C$6,"<"&$A$2:$C$6)+1-SUM(COUNTIF($A$2:$C$6,J$9:J9)))*{1,0,1}=1),(ROW($2:$6)*10^2+{1,0,3}))),"R0C00"),)
Bấm Ctrl+Shift+Enter, copy xuống, trường hợp còn lại, sửa ">" thành "<"!!!
Thiệt chứ thử gì em!!
Dữ hông hôm nay mới ra mặt

Khà khà khà!
Còn câu 2 mần luôn, là sắp sếp theo SL tổng từ Lớn đến nhỏ, nhưng suy cho TEN. Không dùng cột SL đã cộng sẵn bằng công thức SUMIF(). Tức tìm TEN trước rồi mới SUMIF() cho SL.

Thân
 
Để tăng thêm phần khó cho bài, anh đính kèm dữ liệu theo file với hai yêu cầu:
1/ Lấy TEN, với sắp xếp TEN từ A->Z. (Như các cách tại bài trên, chỉ lấy theo thứ tự cột, chứ chưa lấy theo Alphabet)
2/ Lấy TEN, với SL tổng của từng tên được sắp xếp từ Lớn đến Nhỏ.

Dĩ nhiên, là không dùng cột phụ.
Chúc anh em ngày vui.
/-*+//-*+//-*+/
Em đang làm nên giờ mới trả lời được, công thức hơi lủng củng:
Mã:
J10=IFERROR(INDIRECT(TEXT(RIGHT(AGGREGATE(15,6,(COLUMN(A:C)+ROW($2:$6)*1000+COUNTIF(A$2:D$6,"<="&A$2:D$6)*10^7)/ISTEXT(A$2:C$6)/(COUNTIF(J$9:J9,A$2:C$6)=0),1),6),"R0C000"),0),"")
Mã:
M10=IFERROR(INDIRECT(TEXT(RIGHT(AGGREGATE(14,6,(COLUMN(A:C)+ROW($2:$6)*1000+SUMIF(A$2:C$6,A$2:C$6,B$2:D$6)*10^7)/ISTEXT(A$2:C$6)/(COUNTIF(M$9:M9,A$2:C$6)=0),1),6),"R0C000"),0),"")
 
Lần chỉnh sửa cuối:
Thiệt chứ thử gì em!!
Dữ hông hôm nay mới ra mặt

Khà khà khà!
Còn câu 2 mần luôn, là sắp sếp theo SL tổng từ Lớn đến nhỏ, nhưng suy cho TEN. Không dùng cột SL đã cộng sẵn bằng công thức SUMIF(). Tức tìm TEN trước rồi mới SUMIF() cho SL.

Thân
Câu 2 chắc dễ hơn câu 1, nhưng chắc dài hơn, tôi đang dùng đt, để tý làm thử.
 
Em đang làm nên giờ mới trả lời được, công thức hơi lủng củng:
Mã:
J10=IFERROR(INDIRECT(TEXT(RIGHT(AGGREGATE(15,6,(COLUMN(A:C)+ROW($2:$6)*1000+COUNTIF(A$2:D$6,"<="&A$2:D$6)*10^7)/ISTEXT(A$2:C$6)/(COUNTIF(J$9:J9,A$2:C$6)=0),1),6),"R0C000"),0),"")
Mã:
M10=IFERROR(INDIRECT(TEXT(RIGHT(AGGREGATE(14,6,(COLUMN(A:C)+ROW($2:$6)*1000+SUMIF(A$2:C$6,A$2:C$6,B$2:D$6)*10^7)/ISTEXT(A$2:C$6)/(COUNTIF(M$9:M9,A$2:C$6)=0),1),6),"R0C000"),0),"")
Đúng rồi có gì mà lủng củng!
Vậy em có thể giải cho chủ thớt bài https://www.giaiphapexcel.com/diendan/threads/tự-động-liệt-kê-và-sắp-xếp-dữ-liệu.136579/#post-871935 được rồi.
Chúc anh em ngày vui.
Có thể thay ISTEXT() bằng {1,0,1}
/-*+//-*+//-*+/
 
Công thức nhìn khủng quá, em xem mà không hiểu, ai có thể "chặt" ra từng khúc giải thích cho em được không? :)

Em xin cám ơn
 
Công thức nhìn khủng quá, em xem mà không hiểu, ai có thể "chặt" ra từng khúc giải thích cho em được không? :)

Em xin cám ơn
Đừng hoang mang! có thời gian anh sẽ giải thích rõ các công thức trên. Bài lấy ô dữ liệu kết hợp các trường hợp đặc thù của Mảng, rất hay.
Chắc khoảng chiều tối nay sẽ có lời giải thích.
Thân.
Đó! có hai anh chàng anh liệt kê ở trên, ra rồi đó. ;)
 
Thiệt chứ thử gì em!!
Dữ hông hôm nay mới ra mặt

Khà khà khà!
Còn câu 2 mần luôn, là sắp sếp theo SL tổng từ Lớn đến nhỏ, nhưng suy cho TEN. Không dùng cột SL đã cộng sẵn bằng công thức SUMIF(). Tức tìm TEN trước rồi mới SUMIF() cho SL.

Thân
Thử câu 2:
Mã:
J10=INDIRECT(TEXT(MIN(IF(1/MIN(IFERROR(1/SUMIF($A$2:$C$6,IF(COUNTIF($J$9:J9,$A$2:$C$6)=0,$A$2:$C$6),$B$2:$D$6),""))=SUMIF($A$2:$C$6,IF(COUNTIF($J$9:J9,$A$2:$C$6)=0,$A$2:$C$6),B$2:D$6),(ROW($2:$6)*10^2+{1,0,3}))),"R0C00"),)
Bấm Ctrl+Shift+Enter!!!! Copy xuống, sắp xếp lớn tới nhỏ thì sửa MIN thứ 2 trong công thức thành MAX.
 
Đúng vậy! Tính tình ảnh hiền hòa, đơn giản dễ thương, nên cách xử lý bài của ảnh cũng không rối rắm phức tạp.
Mấy nay, không thấy anh @HieuCD vào, chắc lại lo cho đàn em thân yêu rồi, quên đàn em GPE!
Khà khà khà!
/-*+//-*+//-*+/
Hình như là cứ 1 năm vào khoảng tháng này anh ấy sẽ mất tích độ 15 đến 20 ngày gì đó Anh ạ. :p
 
Công thức nhìn khủng quá, em xem mà không hiểu, ai có thể "chặt" ra từng khúc giải thích cho em được không?
Như đã hứa, anh ghi sơ dưới đây vài nét chính để em tìm hiểu về các công thức Mảng tại bài trên.
Từ từ rồi em sẽ có sự tổng hợp về các cách giải quyết của công thức mảng theo từng phân đoạn.
Như các bài trên phải xử lý:
  1. Gộp (hay nối) hay không 1 Mảng từ nhiều mảng.
  2. Loại bỏ dữ liệu trùng lắp, hay liệt kê toàn bộ dữ liệu không phân biệt có trùng hay không.
  3. Truy xuất ô, hoặc vị trí của 1 thành phần hiện hữu trong Mảng.
1/ Gộp (hoặc nối đuôi) có hay không từ nhiều mảng về 1 Mảng:
Như bài #2 anh giải, dùng =OFFSET($A$2,MOD(ROW($1:$10)-1,5),INT((ROW($1:$10)-1)/5)*2), tức nối đuôi Vùng C2:C6 vào Vùng A2:A6, tạo thành Mảng 1 chiều dọc gồm 10 phần tử {A;A;B;B;C;C;D;A;A;A}. Từ đó, khi muốn lấy ra phần tử nào thỏa điều kiện thì dùng Match(): trả về kết quả dòng, hoặc Lookup()...tùy biến, sao cho tìm được số dòng của nó là giải quyết bài toán. (Hàm cơ bản MOD() và INT() em đã biết nên không bàn ở đây).​
Nói thêm: Tùy theo yêu cầu, khi ta cần tạo Mảng gộp từ 2 hoặc nhiều Vùng, liền kề hay cách khoảng, ta có thể dùng các cách sau:​
  • OFFSET(...,....,...)
  • VLOOKUP/HLOOKUP(...,...,{1,2,3,...,n},...)
  • CHOOSE({1,2,...,n}, Vùng 1, Vùng 2,..., Vùng n)
  • LOOKUP( 'Mảng 1 chiều dọc/ngang', 'Mảng chọn hướng' , Vùng/Mảng giá trị cùng chiều với 'Mảng chọn hướng')
Nếu không gộp, thì khi xử lý Mảng ta phải định vị trí Hàng (row) và Cột (column) để tìm cách đánh dấu nó, cho nên phải dùng công thức:​
=ROW(1:n)*10^m + COLUMN(1:d) -> Vd: ứng với A2:A6 là {2001;3001;4001;5001;6001}, tương tự cho Vùng A2:C6 là {2001,2002,2003;3001,3002,3003;.....;6001,6002,6003}.​
Nhìn chung, dù gộp hay không cũng phải có liên quan đến hai hàm mảng ROW() và COLUMN() (hoặc Mảng hằng số như {2,3,4,5,6} hay {5;7;8;9;10}.v.v..), nhờ nó mà từ công thức thường chỉ chứa những thông số có giá trị đơn thành công thức Mảng, hay những công thức chấp nhận có đối số là Mảng.​
2/ Loại bỏ dữ liệu trùng lắp, hay liệt kê toàn bộ dữ liệu không phân biệt có trùng hay không.
Cách thường hay dùng của công thức để loại bỏ trùng lắp là:​
= INDEX( 'Vùng/Mảng chứa toàn bộ dữ liệu' , MATCH( 0, COUNTIF( 'Vùng chứa kết quả' , 'Vùng/Mảng chứa toàn bộ dữ liệu' ) , 0 ))
Nhờ hàm COUNTIF() như trên khi xét thấy 'Vùng chứa kết quả' có giá trị tồn tại trong Vùng khớp với 'Vùng/Mảng chứa toàn bộ dữ liệu' nó sẽ đếm là 1, không khớp thì bằng 0, nhờ Match(0,...) truy tìm giá trị 0 đầu tiên sẽ ra kết quả dòng nào cần lấy ra. Tức lúc bấy giờ hàm COUNTIF() có chức năng báo hiệu hễ dữ liệu nào trùng thì báo 1, không trùng là 0.​

3/ Truy xuất ô, hoặc vị trí của 1 thành phần hiện hữu trong Mảng.
Nếu dùng cách gộp 2 Vùng về thành 1 Mảng thì chỉ cần dùng =INDEX(..,Match(0,..,0)) như trên là tìm ra phần tử thỏa điều kiện.​
Nếu dùng cách 'đánh dấu' phần tử Mảng bằng Row()*10^n+Column() thì có nhiều cách để xử lý, nhưng cách ngắn gọn nhất là dùng bộ công thức INDIRECT(...,0) (Đối số 0 nhằm chọn kết quả có dạng RnCn, Vd: như thông thường ta ghi là D7, thì tương đương =R7C4) kết hợp với TEXT(....,"R00xC00y"), cụ thể là:​
=INDIRECT(TEXT( 'Mảng kết quả trả về' ,"R00xC00y"),0)
Riêng về 'Mảng kết quả trả về': tùy theo yêu cầu của bài nhưng không ra khỏi dạng tổng quát dùng Small()/Large() hay Aggregate(14(15),6,...,.) mà kết hợp thêm với điều kiện Loại bỏ trùng lắp COUNTIF() ở trên đã nêu, ví dụ:
Để sắp thứ tự từ nhỏ đến lớn thì thêm hàm: COUNTIF(Vùng a,"<"&Vùng b)
Hoặc tính tổng SL rồi sắp từ Lớn đến nhỏ dùng thêm SUMIF(Vùng a, Vùng tiêu chí, Vùng giá trị).
Anh chỉ đưa ra hướng nhìn về công thức Mảng để các em tự lần ra và nghiên cứu thêm thì mới thấy cái hay của nó.
Muốn dò thì phải dùng công cụ để tìm hiểu: em có thể chọn trọn đoạn công thức nào muốn tìm hiểu xong nhấn F9 sẽ thấy kết quả của đoạn đó.
Hoặc chọn cả công thức, dùng công cụ hỗ trợ Formulas\Evaluate Formula, rồi nhấn Evaluate từng lần một sẽ thấy cách hoạt động và trả kết quả của công thức đó. Cũng phải tự dò tìm những bài hay về công thức Mảng mà anh đã cung cấp tên của những nickname ở bài #4 để bổ sung kiến thức Mảng của mình.


Chúc em học tập vui với anh em GPE

Thân
 
Web KT

Bài viết mới nhất

Back
Top Bottom