Lấy giá trị thỏa mãn cả cột&hàng, Lấy giá trị max, min của một đối tượng trong mảng

[WhoamI]

Lại lí bí rồi! :wall: Nhờ các bác ra tay cứu giúp! Em cảm ơn nhiều ah!


1: Lấy giá trị thỏa mãn cả điều kiện hàng và cột của một bảng tính (Sheet Bang gia)

2: Bảng khác gồm có hai cột Tên và Số tiền, Tên được xuất hiện nhiều lần trên bảng tính mỗi lần xuất hiện cùng với số tiền có thể giống & khác nhau. Muốn biết số tiền lớn nhất và nhỏ nhất của mỗi Tên. Em cũng chỉ tạm thời giải quyết được = pivot table nhưng thấy không tiện lắm! Nhờ các bác chỉ giúp cho em cái công thức ah! ( Sheet Max-Min)

 

[ForestC]

Đây là công thức cho ý 1, vì anh chưa hiểu hết ý 2 cho nên ý 2 anh chưa làm được, sau vài phút nữa anh sẽ upload file bài giải lên cho em nếu như anh hiểu hết ý 2 nhé:

=INDEX($B$11:$I$22;MATCH(A2;$A$11:$A$22;1);MATCH(B2;$B$10:$I$10;0))

Ý 1 này, dòng ta tìm một cách tương đối, còn cột thì tìm tuyệt đối phải không nào.

 

[ForestC]

Công thức cho Sheet Max-Min của em, hiện tại anh mới chỉ làm được cái max, còn cái Min không hiểu sao .. toàn = 0 cho nên anh không thể áp dụng theo cùng cách với công thức max được.

MAX : {=if(countif(ten;a1)>1;"";max((ten=a1)*sotien)} công thức mảng (Ctrl+Shift+Enter)

MIN ra toàn không cho nên phải dùng công thức khác.

 

[WhoamI]

Đây là công thức cho ý 1, vì anh chưa hiểu hết ý 2 cho nên ý 2 anh chưa làm được, sau vài phút nữa anh sẽ upload file bài giải lên cho em nếu như anh hiểu hết ý 2 nhé:

=INDEX($B$11:$I$22;MATCH(A2;$A$11:$A$22;1);MATCH(B2;$B$10:$I$10;0))

Ý 1 này, dòng ta tìm một cách tương đối, còn cột thì tìm tuyệt đối phải không nào.

Đúng rồi ah! cảm ơn anh Maika!
Lily xu_nghe ơi! sang đây lấy gạo về mà thổi cơm này!:cheesebur
Còn vụ max-min thì không ổn rồi ah! chờ các bác ra tay tiếp!

 

[ForestC]

Who! Anh đã thử nghiệm cái thứ 2 công thức tính max và cho kết quả chính xác mà:

{=IF(COUNTIF(A$4:A4,A4)>1,"",MAX(($A$4:$A$26=A4)*($B$4:$B$26)))}

Đấy, nếu trùng thì cho 1 cái trống, còn không thì xác định xem tiền của cái nào lớn nhất.

 

[WhoamI]

Who! Anh đã thử nghiệm cái thứ 2 công thức tính max và cho kết quả chính xác mà:

{=IF(COUNTIF(A$4:A4,A4)>1,"",MAX(($A$4:$A$26=A4)*($B$4:$B$26)))}

Đúng rồi! không quá phức tạp nhỉ! nhưng Who cứ loay hoay mãi không ra chứng tỏ chưa thực sự hiểu công thức mảng :banana: thật!

 

[lily_xu_nghe]

Đúng rồi ah! cảm ơn anh Maika!
Lily xu_nghe ơi! sang đây lấy gạo về mà thổi cơm này!:cheesebur
Còn vụ max-min thì không ổn rồi ah! chờ các bác ra tay tiếp!

Cám ơn Who, và Bác Maika rất rất nhiều, chắc chắn tối nay phải về thổi cơm mời các bác đến chén mới được.

 

[handung107]

Who! Anh đã thử nghiệm cái thứ 2 công thức tính max và cho kết quả chính xác mà:

{=IF(COUNTIF(A$4:A4,A4)>1,"",MAX(($A$4:$A$26=A4)*($B$4:$B$26)))}

Đấy, nếu trùng thì cho 1 cái trống, còn không thì xác định xem tiền của cái nào lớn nhất.

Công thức tính Min sẽ cho kết quả là 0, nên phải sửa lại như sau :

- Để tính MAX :

{=IF(COUNTIF($A$3:$A3,$A3)=1,MAX(IF($A$3:$A$12=$A3,$B$3:$B$12)),0)}

- Để tính MIN :

{=IF(COUNTIF($A$3:$A3,$A3)=1,MIN(IF($A$3:$A$12=$A3,$B$3:$B$12)),0)}

 

[workman]

Công thức tính Min sẽ cho kết quả là 0, nên phải sửa lại như sau :

- Để tính MAX :

{=IF(COUNTIF($A$3:$A3,$A3)=1,MAX(IF($A$3:$A$12=$A3,$B$3:$B$12)),0)}

- Để tính MIN :

{=IF(COUNTIF($A$3:$A3,$A3)=1,MIN(IF($A$3:$A$12=$A3,$B$3:$B$12)),0)}

Không biết hàm DMAX có sử dụng trong trường hợp này được không nhỉ? mình không rành hàm này lắm, hình như dùng để tính min max trong 1 mảng hay sao đó.

 

[Tuanktcdcn]

Công thức cho Sheet Max-Min của em, hiện tại anh mới chỉ làm được cái max, còn cái Min không hiểu sao .. toàn = 0 cho nên anh không thể áp dụng theo cùng cách với công thức max được.

MAX : {=if(countif(ten;a1)>1;"";max((ten=a1)*sotien)} công thức mảng (Ctrl+Shift+Enter)

MIN ra toàn không cho nên phải dùng công thức khác.

Công thức của Maika rất sáng tạo nhưng theo công thức của chị Handung107 là hoàn chỉnh hơn.
MIN={=if(countif(ten;a1)>1;"";Min((ten=a1)*sotien)}
Công thức trên sẽ luôn cho ra giá trị =o vì trong công thức Min((ten=a1)*sotien) sẽ tập hợp một mảng (hay danh sách) các giá trị trong đó có cả giá trị là o trong trường hợp logic (ten=a1)=False . Chúng ta hiểu đơn giản là True=1 và False=0. Như vậy kết quả phân tích là {Min(0*sotien,1*Sotien)} dễ nhận ra giá trị 0 là đạt min.
Theo CT của chị Handung107:
MIN={=IF(COUNTIF(Ten,A4)>1,"",MIN(IF(Ten=A4,Sotien)))}
Dùng hàm IF($A$4:$A$26=A4,$B$4:$B$26) để liệt kê các giá trị thỏa mãn công thức, nếu không thỏa mãn (Ten=A4)=False thì là giá trị Blank-không phải là Number. Như vậy các giá trị trong mảng là các giá trị (Number) cần tính và giá trị Blank, các hàm thống kê (Sum,Min, Max,..) chỉ thực hiện với giá trị kiểu Number mà thôi vì vậy Blank bị loại bỏ.
Nếu CT: MIN={=IF(COUNTIF(Ten,A4)>1,"",MIN(IF(Ten=A4,Sotien,0)))} thì kết quả sẽ như của Maika vì có giá trị 0 ở đối số thứ 3 của hàm IF khi (Ten=A4)=False.
Việc phân tích logic của công thức là quan trọng nhất để giải quyết bài toán chúng đã làm được nhưng mội lỗi rất dễ mắc phải là kiểu giá trị (Type) trong phép tính.
Type:
1-Nhóm thống kê: Number, Date, Logig, Info, Currency...
2-Nhóm văn bản: Text
3-Nhóm kết hợp : General
4-.....

 

[ForestC]

MaiKa đã sửa lại bài toán này theo chị Dung để được một bài toán hoàn chỉnh. Tuy nhiên ở đây MaiKa vẫn dùng "" là để đỡ phải nhìn thấy các số 0 không được mỹ quan cho lắm (mặc dù có thế dùng rất nhiều cách để che nó đi như định dạng ;; hoặc Condition Format ...

 

[SA_DQ]

DMax() & DMin()

Tại Bảng tính trong VD; quét chọn từ A3:B26 đặt tên là CSDL;
Chép A3:A4 đến ô E$3:E$4 & gán nó có tên là Chuan;
Nhập vào ô C4: =Max(CSDL;B3;Chuan);
Tương tự như vậy ở ô D4: =DMin(CSDL;B3;Chuan);
Nếu được thì tiếp tục sau một hồi suy ngẫm! :atom: