Lớp học GPE tháng 10 - TPHCM: Conditional Formatting và Data Validation (tối 4, 6/10) | Excel cơ bản (tối 9, 11, 13/10) |
Thuần thục các hàm dò tìm (tối 10, 12/10) | Tất tần tật về PivotTable (tối 16, 18, 20/10) |
Tất tần tật về Filter và Advanced Filter (tối 23, 25/10) | Name động và biểu đồ (tối 24, 26, 28/10)

Đăng ký học Khởi đầu cùng Google Spreadsheet - 2 chủ nhật 1 và 8/10 - TPHCM

Đăng ký học Xây dựng ứng dụng Form bằng VBA - 2 chủ nhật 15 và 22/10 - TPHCM

Xin chỉ giáo về câu lệnh sumif và Countif

Thảo luận trong 'Giải thích, gỡ rối, xử lý lỗi công thức' bắt đầu bởi hoangpn, 17 Tháng mười 2010.

  1. hoangpn

    hoangpn Thành viên mới

    Em dang sử dụng Excel2k3 có một vấn đề thế này:
    Ví dụ bảng dữ liệu của em có 1 cột là :
    Mã Số Nhân Viên
    Mã Số Nhân Viên ------- Lương
    GD08001 ----------------91800000
    TP05002 --------------- 45900000
    TP05001 ---------------- 41310000
    PP06002 --------------- 21735000
    PP06001 --------------- 22356000
    NV04001 ---------------14400000
    NV05002 ---------------18746000

    với GD: Giám đốc - TP: Trưởng phòng - PP: phó phòng - NV - nhân viên

    Giờ em không muốn thêm cột nhưng muốn: tính tổng lương của nhân viên, đếm xem có có bao nhiêu trưởng phòng và phó phòng. Nếu không dùng 2 hàm đó thì làm cách nào khác không.
     
    Lần chỉnh sửa cuối: 17 Tháng mười 2010
  2. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Dữ liệu của bạn chắc giống thế này chăng:

    untitled.JPG

    Nếu đúng thế thì:
    - Tổng lương nhân viên: =SUMIF($A$2:$A$8,"NV*",$B$2:$B$8)
    - Đếm số lượng trưởng phòng: =COUNTIF($A$2:$A$8,"TP*")
    - Đếm số lượng phó phòng: =COUNTIF($A$2:$A$8,"PP*")
     
  3. Ếch Xanh

    Ếch Xanh Thành viên tích cực

    Thêm 1 công thức cho đếm theo chức danh nữa:

    =SUM(IF(LEFT($A$1:$A$7,2)="TP",1,0))


    Chép công thức vào rồi Ctrl+Shift+Enter
     
  4. hoangpn

    hoangpn Thành viên mới

    Vậy nếu em muốn đếm tổng số Trưởng phòng và Phó phòng thì có phải sẽ là: =COUNTIF($A$2:$A$8,"TP*")+COUNTIF($A$2:$A$8,"PP*"). Có cách viết nào khác để ngắn gọn hơn không?
     
  5. hoangpn

    hoangpn Thành viên mới

    Vậy mình muốn tính tổng tiền lương của Trưởng phòng và Phó phòng bằng hàm trên thì có được không?
    =SUMIF($A$2:$A$8,"TP*",$B$2:$B$8)+SUMIF($A$2:$A$8,"pp*",$B$2:$B$8) => có cách viết nào ngắn gọn hơn không?
     
  6. hoangminhtien

    hoangminhtien Thành viên tích cực

    Nếu chỉ có trưởng phòng và phó phòng có chữ "P" thì bạn dùng công thức sau
     
  7. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Nếu trong danh sách mà có ông PHÓ GIÁM ĐỐC (PGD) thì coi như... toi
    Tôi nghĩ nên dùng cách này:
    - Đếm số TP và PP:
    Mã:
    =SUMPRODUCT(COUNTIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B]))
    - Tổng lương TP và PP:
    Mã:
    =SUMPRODUCT(SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8))
    Thêm bao nhiêu điều kiện nữa, cứ cho vào khu vực màu đỏ
     
  8. tinhco01

    tinhco01 Thành viên mới

    Công thức này quá hay bác ơi, em đang nghiên cứu và ứng dụng nó đây!
     
  9. tongpro

    tongpro Thành viên mới

    cám ơn a rất nhiều. Rất gọn
     
  10. stomperinky

    stomperinky Thành viên mới

    Em chưa hiểu

    1. Hàm
    Mã:
    =SUMPRODUCT(SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8))
    nghĩa là gì? Tại sao không phải là
    Mã:
    =SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8)

    đứng riêng rẽ
    , ai giải thích giúp em được không ạ?

    2. 2 hàm dưới đây tương đương nhau phải không ạ?
    Mã:
    =SUMPRODUCT(SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8))
    Mã:
    =SUMPRODUCT(($A$2:$A$8=[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B])*$B$2:$B$8))
     
    Lần chỉnh sửa cuối: 2 Tháng ba 2015
  11. giangleloi

    giangleloi Mới tốt nghiệp tiểu học

    SUMIF dùng để tính tổng thỏa mãn với 1 điều kiện, ở đây kết hợp với SUMPRODUCT để lấy hết các giá trị thỏa mãn 1 trong 2 điều kiện trong mảng cần tính.
     
  12. stomperinky

    stomperinky Thành viên mới

    Cảm ơn anh nhưng em thấy không đúng vì SUMIF có thể dùng với nhiều điều kiện dạng OR (áp dụng cho 1 cột) tương đương với SUMIFS vì đã có dấu {} rồi:
    Mã:
    =SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8)
    =SUMIFS($B$2:$B$8,$A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B])
    hay
    Mã:
    =SUMPRODUCT(SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8))
    =SUMPRODUCT(SUMIFS($B$2:$B$8,$A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B]))
    Xin hỏi lại cho ai biết ạ, em chưa hiểu

    1. Hàm
    Mã:
    =SUMPRODUCT(SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8))
    nghĩa là gì? Tại sao không phải là
    Mã:
    =SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8)

    đứng riêng rẽ
    , ai giải thích giúp em được không ạ?

    2. 2 hàm dưới đây tương đương nhau phải không ạ?
    Mã:
    =SUMPRODUCT(SUMIF($A$2:$A$8,[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B],$B$2:$B$8))
    Mã:
    =SUMPRODUCT(($A$2:$A$8=[B][COLOR=red]{"TP*";"PP*"}[/COLOR][/B])*$B$2:$B$8))
     
  13. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Thay vì hỏi, bạn có thể tự mình thử nghiệm trực tiếp trên bảng tính mà. Nhìn kết quả, phân tích, suy luận... bạn sẽ nghiệm ra được biết bao nhiêu điều bổ ích hơn bất cứ lời giải thích nào
     
  14. giangleloi

    giangleloi Mới tốt nghiệp tiểu học

    Chắc là do mình giải thích sai đó. Bạn cứ hiểu ý nghĩa của {} là gì đi đã. Rồi tìm hiểu sự khác nhau giữa sumif và sumifs trên diễn đàn có đấy. Rồi tự kết luận xem tương đương hay không theo cách suy nghĩ của bạn. Híc Híc
     

Chia sẻ trang này