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

[TekachiNguyen]

Có cách nào lọc sản phẩm có số tồn ở nhiều kệ thành 1 con số tồn không vậy các bác ???

 

[Ba Tê]

Có cách nào lọc sản phẩm có số tồn ở nhiều kệ thành 1 con số tồn không vậy các bác ???

Bạn nghiên cứu Pivot Table thử xem.

 

[khi ta 20]

Có cách nào lọc sản phẩm có số tồn ở nhiều kệ thành 1 con số tồn không vậy các bác ???

Xem thử nha

 

[hml89]

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

@Phan Thế Hiệp: Tưởng không khó mà khó không tưởng. @@ Làm sao để học tốt được đây ta ?

 

[Phan Thế Hiệp]

@Phan Thế Hiệp: Tưởng không khó mà khó không tưởng. @@ Làm sao để học tốt được đây ta ?

"Dục tốc bất đạt"

Cứ "nấu" từ từ! rồi cái gì cũng phải "nhừ".

Chuyện vui trong kiếm hiệp: "Ỷ Thiên Đồ Long ký"
Do may mắn hiểu được 1 đoạn văn bị in lên mặt của sứ giả Ba Tư, nên Trương Vô Kỵ đã đoạt được toàn bộ "Thánh Hỏa Lệnh Minh giáo". Lại nhờ Tiểu Siêu phiên dịch dùm, do nóng vội lấy cái ngắn nhất ra đọc, đọc xong Vô Kỵ ngẩn tò te chẳng biết đâu mà mò! bấy giờ Triệu Minh góp ý, lấy cái nào giống với cái lệnh đã in lên mặt sứ giả, thì tìm được cái dài thứ 2, và sau khi phiên dịch lại hiểu được nhiều hơn, bèn lấy cái dài nhất ra đọc thì hiểu hết hoàn toàn.

Rút ra rằng: Trong công thức chọn lấy cái cơ bản nhất, tuy nó dài nhưng đọc nó sẽ dễ hiểu, rồi từ từ áp dụng đến các công thức ngắn hơn.

Thân.

 

[hml89]

"Dục tốc bất đạt"

Cứ "nấu" từ từ! rồi cái gì cũng phải "nhừ".

Chuyện vui trong kiếm hiệp: "Ỷ Thiên Đồ Long ký"
Do may mắn hiểu được 1 đoạn văn bị in lên mặt của sứ giả Ba Tư, nên Trương Vô Kỵ đã đoạt được toàn bộ "Thánh Hỏa Lệnh Minh giáo". Lại nhờ Tiểu Siêu phiên dịch dùm, do nóng vội lấy cái ngắn nhất ra đọc, đọc xong Vô Kỵ ngẩn tò te chẳng biết đâu mà mò! bấy giờ Triệu Minh góp ý, lấy cái nào giống với cái lệnh đã in lên mặt sứ giả, thì tìm được cái dài thứ 2, và sau khi phiên dịch lại hiểu được nhiều hơn, bèn lấy cái dài nhất ra đọc thì hiểu hết hoàn toàn.

Rút ra rằng: Trong công thức chọn lấy cái cơ bản nhất, tuy nó dài nhưng đọc nó sẽ dễ hiểu, rồi từ từ áp dụng đến các công thức ngắn hơn.

Thân.

Đọc câu truyện của bạn tôi lại thấy tiếc thay cho nhà văn Kim Dung. Chắc tôi phải nên học cách bình tĩnh, không nóng vội. Nhưng nhiều khi ngõ cụt,không hiểu gì nên là cứ bị nản. Tôi nhớ thời đầu tiên biết dùng công thức Vlookup. Tôi toàn đi copy thôi, mãi lâu lâu mới biết là cái điều kiện ở mỗi dấu phẩy là gì, từ đó nhớ lâu hơn hẳn.
Cám ơn bạn đã chỉ dạy cho tôi!

 

[khi ta 20]

Đọc câu truyện của bạn tôi lại thấy tiếc thay cho nhà văn Kim Dung.

Sao lại tiếc vậy anh ?

 

[hml89]

Sao lại tiếc vậy anh ?

à, thì tôi nghe nói ông ấy mất rồi! Nên là sẽ không còn được đọc truyện mới của ông ấy nữa.
Chúc bạn ngày mới vui nha!

 

[khi ta 20]

à, thì tôi nghe nói ông ấy mất rồi! Nên là sẽ không còn được đọc truyện mới của ông ấy nữa.
Chúc bạn ngày mới vui nha!

Truyện mới nhất của ông í viết cách đây cũng chừng nữa thế kỷ rồi anh ạ, với cuốn Lộc đỉnh ký .