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

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi WhoamI, 30 Tháng năm 2005.

10,055 lượt xem

  1. WhoamI

    WhoamI Thành viên thân thiết

    Bài viết:
    534
    Đã được thích:
    0
    Nơi ở:
    Cố lên, cứ đi rồi sẽ tới!
    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)
     

    Các file đính kèm:

    Last edited: 30 Tháng năm 2005
    #1
  2. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    Đâ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.
     
    #2
  3. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    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.
     
    #3
  4. WhoamI

    WhoamI Thành viên thân thiết

    Bài viết:
    534
    Đã được thích:
    0
    Nơi ở:
    Cố lên, cứ đi rồi sẽ tới!
    Đú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!
     
    #4
  5. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    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ác file đính kèm:

    Last edited: 31 Tháng năm 2005
    #5
  6. WhoamI

    WhoamI Thành viên thân thiết

    Bài viết:
    534
    Đã được thích:
    0
    Nơi ở:
    Cố lên, cứ đi rồi sẽ tới!
    Đú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!
     
    #6
  7. lily_xu_nghe

    lily_xu_nghe Thành viên thân thiết

    Bài viết:
    72
    Đã được thích:
    2
    Nơi ở:
    Ha noi
    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.
     
    #7
  8. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    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)}
     
    #8
  9. workman

    workman Thành viên hoạt động

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    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 đó.
     
    Last edited: 1 Tháng tám 2005
    #9
  10. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    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-.....
     
    #10
  11. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    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 ...
     

    Các file đính kèm:

    #11
  12. SA_DQ

    SA_DQ Thành viên thân thiết

    Bài viết:
    432
    Đã được thích:
    37
    Nơi ở:
    HCM city
    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:
     
    #12

Chia sẻ trang này