Hiển thị kết quả tìm kiếm từ 1 đến 9 trên tổng số: 9
  1. #1
    Tham gia ngày
    08 2007
    Nơi Cư Ngụ
    Hà nội
    Bài gởi
    1,626
    Cảm ơn
    1,133
    Được cảm ơn 2,352 lần trong 1,069 bài viết

    11 Ví dụ hay về hàm SUMPRODUCT

    Giới thiệu với các bạn 11 Ví dụ hay để hiểu thêm về cách dùng hàm Sumproduct, nhân tiện nhờ các bạn dịch giúp ra tiếng Việt giúp.
    Nguồn

    Example 1: Count the number of items where the date, in A42:A407 is earlier than today, and J42:J407 is equal to a variable array of values

    Solution:
    The date test is handled with ($A$42:$A$407<TODAY()). The variable array of values is setup in a range, and this is used in conjunction with the MATCH and ISNUMBER functions.

    =SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A $42:$A$407<TODAY()))
    Example 2: To count the number of sales in 3 locations of service since a given time period.
    Solution: In it's basic elements, this is a simple test. If the date to be tested against is in a cell it would be a simple

    =SUMPRODUCT((C5:C309>$A$1))*(H5:H309="A"))

    But this formula shows a technique to use embedded date strings that works, as far as I am aware, in all international versions of Excel.

    =SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))
    Example 3: Instead of typing the multiple criteria into the formula, can I have them typed into cells, and just reference the cells?

    Solution:
    This seeemd a simple request to which a solution of

    =SUMPRODUCT((B5:B63=L1:N1)*(C5:C63))

    was suggested.

    This failed because the requester wanted the criteria in a column, not a row, so this required the TRANSPOSE function to incorporate in SUMPRODUCT. This was the result

    =SUMPRODUCT((B5:B63=TRANSPOSE(P46:P48))*(C5:C63))

    which, because it uses the TRANSPOSE function, has to be entered as an array formula.
    Example 4: I originally had this,

    =SUMIF(J2:J196,J209,L2:L196)

    but I need to have these extra ranges aggregated.
    R2:R196,U2:U196,V2:V196,Z2:Z196

    Solution:
    This could easily be solved by having separate SUMPRODUCT functions for each of the separate test ranges, but with a bit of ingenuity, it can be resolved in one, by using the '+' operator.

    =SUMPRODUCT(--(J2:J196=J209),L2:L196+R2:R196+U2:U196+V2:V196+Z2: Z196)
    Example 5: Find the occurrences of a string, value of 'good', in a range A1:A100. Some of the cells could include leading and/or trailing spaces, or even HTML non -breaking spaces.

    Solution: The basic count of the string is very simple. Allowing for leading and trailing spaces is also handled by including TRIM in the foirmula. However, TRIM doesn't handle the HTML non-breaking spaces, these have to be extracted from the range being tested with the SUBSTITUTE function.good

    =SUMPRODUCT(--(TRIM(SUBSTITUTE(A1:A100,CHAR(160),""))="good"))

    Example 6:
    Count the number of unique values in a range.

    Solution:
    The first version works if the whole range, A1:A20, is occupied

    =SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))

    However, this returns #DIV/0 if any of the range is blank. This can be corrected with

    =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

    And finally, to overcome a bug in the implicit intersection of COUNTIF/SUMIF 1st argument with that argument's parent worksheet's used range., which can also return #DIV/0, we can use

    =SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20="")))
    1-Ultimate Add-ins (All in one)| 2-Name manager 4.1 (Công cụ quản lý name xuất sắc) |3-Advantages of SUMPRODUCT(11 ví dụ hay) |



  2. #2
    Tham gia ngày
    08 2007
    Nơi Cư Ngụ
    Hà nội
    Bài gởi
    1,626
    Cảm ơn
    1,133
    Được cảm ơn 2,352 lần trong 1,069 bài viết
    Tiếp tục từ VD 7 đến VD 11.

    Example 7:

    Count the instances of either of two conditions being met in two different ranges.For this example, consider the range A1:A10 with countries, and B1:B10 with continents, C1:C10 with a flag saying whether they are G7 countries or not. We want to count the number of countries that are in Europe, or are G7 countries.

    Solution: To count the number of countries that are eoither in Europe, or G7 countries, we could use

    =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))

    The problem with this is that it will double-count the countries tin Europe that are also G7 countries. This can be overcome with

    =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--(C1:C10="Y"))

    which uses a SUMPRODUCT to calculate the number of countries that are both in Europe and G7 countries, and is then subtracted from the double-counting formula.
    Example 8: Count the instances of more than one value in a given range. This example is counting how many Fords and Chryslers are in the range A1:A10.

    Solution: This can be solved by using OR as described in the prevuious example, but on a single range, that is

    =SUMPRODUCT((A1:A10="Ford")+(A1:A10="Chrysler"))


    But in this in stance, as we are looking for two values in a single range, it is better to test against an array of values, or

    =SUMPRODUCT(--(A1:A10={"Ford","Chrysler"}))
    Example 9: Having a range of stock numbers, A1:A10, corresponding sites in B1:B10, and stock numbers in C1:C10, we need to count how many items of a particular part at a particular site. This would be a straight-forward SUMPRODUCT normally, but in this case, the stock number contains a number of components, so the part id is embedded within this. SUMIF can use wildcards, but only for one test, but SUMPRODUCT doesn't support wildcards directly.

    Solution: To solve this problem, we can use the FIND function to test whetehr our part number if embebbed within the stock number. The ISNUMBER function is used to test whether it is found or not (avoiding the dreaded #VALUE error)

    =SUMPRODUCT(--(ISNUMBER(FIND("ATN",A1:A10))),--(B1:B10="Birmingham"),--(C1:C10))
    FIND
    is case sensitive, if case sensitivity is not required, use SEARCH instead.

    =SUMPRODUCT(--(ISNUMBER(SEARCH("ATN",A1:A10))),--(B1:B10="Birmingham"),--(C1:C10))
    Example 10: Count the number of a certain day between two dates, excluding any holidays that may fall on those days.

    Solution: SUMPRODUCT can be used to calculate the number of a particular day between two dates. For example, assuming that the two dates are in cells A1 and A2, this formula returns the number of Wednesdays bewteen those two dates.

    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=4))

    This solution utilises the fact that as Excel stores dates as serial numbers from 1st Jan 1900, the two dates can be used in an INDIRECT function to 'virtually' load all of the dates into rows, which can then be tested using the WEEKDAYand the ROW function to determine whether any of those row dates are the day in question. This does of course place a limit on the later date, which is 06-Jun-2079, as Excel is restricted to 65336 rows.

    The NETWORKDAYS function provides a facility to exclude holidays in the count. Again, we can achieve this with our function by adding a test against the holdays. Assuming that the holidays are in a named range, holidays, we would use

    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=4),--(COUNTIF(holidays,ROW(INDIRECT($A$1&":"&A2)))=0))


    We could also simulate the NETWORKDAYS function to count the number of days between two dates, excluding Saturdays and Sundays amd holidays using

    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>1),(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>7)),--(COUNTIF(holidays,ROW(INDIRECT($A$1&":"&A2)))=0))

    This might seem unnecessary, as we could more easily use the NETWORKDAYS function, but it does offer one small advantage over that function, it doesn't matter what order the dates are in. It could also be used to exclude any 1,2, 3 or whatever days, not just the Saturdays and Sundays, by changing the weekday value.
    Example 11:

    Sum only the visible cells that match a certain criteria. For instance, in a range A1:A100, sum all cells that have a value of "North" in B1:B100, where some rows are not visble due to a Data Filter having been applied on the data.

    Solution:
    This solution takes advantage of the function which ignores non-visible cells.
    The first part is a straight-forward conditional test on range B1:B100 for a value of 'North, and the sum of the cells A1:A100

    --($B$1:$B$100="North"),$A$1:$A$10

    The counting of the visible cells is more complicated. As mentioned above, it uses SUBTOTAL, together with ROW, INDEX and OFFSET functions, like so

    --(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1)

    The total formula then becomes

    =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)
    thay đổi nội dung bởi: DOSNET, 13-01-09 lúc 09:38 AM
    1-Ultimate Add-ins (All in one)| 2-Name manager 4.1 (Công cụ quản lý name xuất sắc) |3-Advantages of SUMPRODUCT(11 ví dụ hay) |



  3. #3
    Tham gia ngày
    12 2007
    Bài gởi
    661
    Cảm ơn
    185
    Được cảm ơn 350 lần trong 219 bài viết
    Công thức cuối cùng của ví dụ 11 là:
    =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)
    Có thể (và nên) thay mọi 2 dấu -- bằng 1 dấu - ; vì sẽ có 2 dãy đổi dấu nên tích của chúng không đổi.
    Nếu có lẻ cặp 2 dấu -- thì kết quả âm. Thêm dấu - và đầu công thức để đổi dấu nó.
    Tôi cũng chưa thử công thức trên, nhưng nên thay như vậy để giảm thời gian tính: mỗi dấu - sẽ đổi dấu 100 số (theo công thức trên), 4 dấu là 400 lần đổi dấu. Tức là thời gian để đổi dấu giảm 2 lần.

  4. Thành viên sau cảm ơn haonlh về bài viết này:


  5. #4
    Tham gia ngày
    05 2007
    Nơi Cư Ngụ
    SAPA
    Bài gởi
    4,133
    Cảm ơn
    9,566
    Được cảm ơn 10,885 lần trong 3,739 bài viết
    Trích Nguyên văn bởi haonlh View Post
    Công thức cuối cùng của ví dụ 11 là:
    =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)
    Có thể (và nên) thay mọi 2 dấu -- bằng 1 dấu - ; vì sẽ có 2 dãy đổi dấu nên tích của chúng không đổi.
    Nếu có lẻ cặp 2 dấu -- thì kết quả âm. Thêm dấu - và đầu công thức để đổi dấu nó.
    Tôi cũng chưa thử công thức trên, nhưng nên thay như vậy để giảm thời gian tính: mỗi dấu - sẽ đổi dấu 100 số (theo công thức trên), 4 dấu là 400 lần đổi dấu. Tức là thời gian để đổi dấu giảm 2 lần.
    Tôi lại nghĩ khác, "--" chẳng qua nó là một phép toán chứ không phải là ký hiệu để đổi dấu, và thông thường người ta thường dùng *1 hoặc -- hoặc value() hoặc ^1 để làm một phép tính với một mảng biểu thức logic True/False để trả về giá trị có thể tính toán được là {0,1}

    Một lý do nữa để không dùng "-" mà nên dùng "--" là khi lập công thức, dùng "--" người lập công thức không cần bận tâm có bao nhiêu điều kiện, điều kiện lẻ hay chẵn để thêm dấu trừ nữa!.

    Một lý do nữa là đối với công thức phức tạp và dài, ngăn cách giữa các điều kiện bằng dấu "--" sẽ dễ nhận biết hơn là dấu "-" ở giữa một rừng công thức!

  6. Có 4 thành viên cảm ơn cadafi về bài viết này:


  7. #5
    Tham gia ngày
    08 2007
    Nơi Cư Ngụ
    Hà nội
    Bài gởi
    1,626
    Cảm ơn
    1,133
    Được cảm ơn 2,352 lần trong 1,069 bài viết
    Trích Nguyên văn bởi haonlh View Post
    Nếu có lẻ cặp 2 dấu -- thì kết quả âm. Thêm dấu - và đầu công thức để đổi dấu nó.
    Tôi cũng chưa thử công thức trên, nhưng nên thay như vậy để giảm thời gian tính: mỗi dấu - sẽ đổi dấu 100 số (theo công thức trên), 4 dấu là 400 lần đổi dấu. Tức là thời gian để đổi dấu giảm 2 lần.
    Bạn đang quan tâm tới giải pháp cải thiện tốc độ của SUMPRODUCT, có lẽ bạn hiểu hơi nhầm một chút phần mình tô đậm, mình đống ý với những gì Ca_dafi đã giải thích, ngoài ra dùng --( trong một số trường hợp giúp ta tính được mà không báo lỗi #VALUE khi trong mảng có cả giá trị kiểu TEXT, đây là điểm hay nhất mình thường áp dụng !

    Trong 11 Ví dụ trên có những chỗ bản thân mình cũng chưa hiểu rõ lắm, chẳng hạn ở VD 1

    Code:
       =SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A  $42:$A$407<TODAY()))
    Fred có phải là một Name ?
    thay đổi nội dung bởi: DOSNET, 13-01-09 lúc 10:47 AM
    1-Ultimate Add-ins (All in one)| 2-Name manager 4.1 (Công cụ quản lý name xuất sắc) |3-Advantages of SUMPRODUCT(11 ví dụ hay) |


  8. #6
    Tham gia ngày
    06 2006
    Nơi Cư Ngụ
    TP Hội An
    Bài gởi
    2,127
    Cảm ơn
    3,731
    Được cảm ơn 5,401 lần trong 1,679 bài viết
    Trích Nguyên văn bởi DOSNET View Post
    Bạn đang quan tâm tới giải pháp cải thiện tốc độ của SUMPRODUCT, có lẽ bạn hiểu hơi nhầm một chút phần mình tô đậm, mình đống ý với những gì Ca_dafi đã giải thích, ngoài ra dùng --( trong một số trường hợp giúp ta tính được mà không báo lỗi #VALUE khi trong mảng có cả giá trị kiểu TEXT, đây là điểm hay nhất mình thường áp dụng !

    Trong 11 Ví dụ trên có những chỗ bản thân mình cũng chưa hiểu rõ lắm, chẳng hạn ở VD 1

    Code:
       =SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A  $42:$A$407<TODAY()))
    Fred có phải là một Name ?
    Ngoài ra khi dùng dấu "--" có tác dụng tăng tốc độ tính toán.
    Ai cũng một lần chết. Sống sao cho đến khi chết không phải hổ thẹn với chính mình.

  9. #7
    Tai Example 6: Em dang su dung ham =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&"")) de dem co bao nhieu so khong trung nhau. Ket qua la chinh xac
    Y nghia va ket qua thi e da bit, e muon hoi nguyen tac van hanh cua no nhu the nao, mong cac bac send cau tra loi nhe
    E thanks truoc nhe!

  10. Thành viên sau cảm ơn socola7287 về bài viết này:


  11. #8
    Tham gia ngày
    03 2011
    Bài gởi
    8
    Cảm ơn
    0
    Được cảm ơn 0 lần trong 0 bài viết
    chào các bạn ! tôi nhận thấy hàm SUMPRODUCT này rất hay . nhưng tôi đọc bài các bạn hướng dẫn mà thấy khó quá . Bởi vì các bạn viết giải thích bằng tiếng anh mà tôi thì mù tịt tiếng anh. làm phiền các bạn viết bằng tiếng việt được không ? cảm ơn các bạn nhiều! Qua đây làm ơn hãy gỡ rối giúp tôi với . tôi có một bảng tính trong đó có các cột ghi tên các dân tộc . tôi muốn đếm số lượng dân tộc nói chung ( ví dụ như: dao, tày , nùng , thái . trong đó trừ người kinh ra ) bạn nào có thể giúp tôi được không / xin chân thành cảm ơn các bạn ! Nếu có thể hãy gửi cho tôi theo địa chỉ basang76@ymail.com

  12. #9
    Tham gia ngày
    03 2013
    Bài gởi
    40
    Cảm ơn
    68
    Được cảm ơn 12 lần trong 6 bài viết
    Trích Nguyên văn bởi basang76 View Post
    chào các bạn ! tôi nhận thấy hàm SUMPRODUCT này rất hay . nhưng tôi đọc bài các bạn hướng dẫn mà thấy khó quá . Bởi vì các bạn viết giải thích bằng tiếng anh mà tôi thì mù tịt tiếng anh. làm phiền các bạn viết bằng tiếng việt được không ? cảm ơn các bạn nhiều! Qua đây làm ơn hãy gỡ rối giúp tôi với . tôi có một bảng tính trong đó có các cột ghi tên các dân tộc . tôi muốn đếm số lượng dân tộc nói chung ( ví dụ như: dao, tày , nùng , thái . trong đó trừ người kinh ra ) bạn nào có thể giúp tôi được không / xin chân thành cảm ơn các bạn ! Nếu có thể hãy gửi cho tôi theo địa chỉ basang76@ymail.com
    Bạn đưa file lên mọi người sẽ dễ dàng giúp bạn. Theo bạn diễn tả thì chỉ cần countif là được rồi. Ví dụ minh họa
    Tên dân tộc đếm
    ngyuen van a thai COUNTIF($G$11:$G$15;G11)
    nguyen van b tay 2
    nguyen thi c nung 1
    nguyen thi d thai 2
    ngyen van f tay 2

Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 1 người đang xem đề tài này. (0 thành viên và 1 khách)

Đề tài tương tự

  1. Nhờ giúp đỡ công thức Sumproduct
    Viết bởi Tiny07 trong chuyên mục Hướng dẫn sử dụng các hàm trong Excel
    Trả lời: 13
    Bài mới gởi: 11-07-12, 03:24 PM
  2. Hàm SUMPRODUCT
    Viết bởi haitcv trong chuyên mục Hàm và công thức Excel
    Trả lời: 13
    Bài mới gởi: 12-05-11, 10:40 PM
  3. Nhờ Các Bạn Kiểm Tra Giúp Công Thức SumProduct
    Viết bởi thaibinh_excel trong chuyên mục Hàm và công thức Excel
    Trả lời: 11
    Bài mới gởi: 24-04-11, 10:19 PM
  4. Giúp đỡ về hàm NPV và SUMPRODUCT
    Viết bởi vietoil trong chuyên mục Excel và Kế Toán
    Trả lời: 6
    Bài mới gởi: 26-10-09, 12:39 PM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]