Excel SUMIFS nhiều trang tính

(Để đơn giản hóa việc nhập hai tiêu chí, tôi đã sử dụng 'xác thực dữ liệu', với danh sách đầu vào cho từng tiêu chí. )

Đây là bảng tính - hoặc nó sẽ như vậy nếu nó có thể được sao chép và dán đúng cách

Tiêu chí 2 Larder
mứt tiêu chí 1 #VALUE. ←←

địa điểm → Larder Tủ lạnh Tủ lạnh
thứ ↓ bánh quy 10 20 3 ← số
bơ 3 0 1
phô mai
jam 15 5 0

…….
Công thức (trong F16). SUMIFS (F19. H22, E19. E22, E14, F18. H18, F13)
[khoảng trắng chỉ để đọc dễ hiểu hơn]
--- Trong 'Tiếng Anh'. Tổng nếu ( Σphạm vi, C1phạm vi, C1, C2phạm vi, C2)

Trả lời

Serkan nói.
Ngày 9 tháng 2 năm 2021 lúc 2. 51 giờ chiều

Tôi có một câu hỏi về sumifs với nhiều tiêu chí. Có một ví dụ hiển thị sản phẩm (cam, táo, chuối), nhà cung cấp (Pete, Mike, ) và số lượng. Tôi áp dụng logic/công thức tương tự cho bảng tính có bố cục hơi khác và nó trả về lỗi (#value). Hãy tưởng tượng một bảng tính có tên của các nhà cung cấp (Pete, Mike, v.v. ) ở hàng 1 và được lặp lại nhiều lần trên nhiều cột. Bây giờ hãy tưởng tượng trong cột A là tên của các sản phẩm (cam, chuối, v.v. ) và chúng cũng xuất hiện nhiều lần trong một số hàng. Bên trong bảng tính, nơi nhà cung cấp/sản phẩm giao nhau là những con số (Số lượng). Khi tôi áp dụng cùng một công thức/logic), nó sẽ trả về lỗi. Bảng của tôi có 10 cột (B đến K) và 10 hàng (A2 đến A11). Đây là công thức của tôi. =SUMIFS(B2. K11,B1. K1,"Pete",A2. A11,"quả táo"). Điều này đưa ra một thông báo lỗi (#value). Tại sao nó không đếm/cộng số lượng trong các ô dựa trên tiêu chí trong loại bảng tính này?

Trả lời

db nói.
Ngày 7 tháng 12 năm 2020 lúc 3. 21 giờ tối

Xin chào Alexander,

Tôi cần trợ giúp về hàm sumifs 2D. Công ty của tôi đã có nhân viên sắp xếp chi tiết thời gian của họ trong một bảng tính. Cột A là thời gian bắt đầu (ngày tiêu chuẩn theo gia số 6 phút bắt đầu từ "8. 00. 00 AM"), cột B là thời gian dừng, cột c là dự án vào ngày 1/1, cột d là nhận xét vào ngày 1/1, cột e là dự án vào ngày 2/2, cột f là nhận xét 2/2, v.v.

Tôi muốn viết một công thức duy nhất trong một bảng tóm tắt nơi tôi có thể tham chiếu từng bảng chấm công của nhân viên và liệt kê từng dự án trong cột a và từng ngày trong hàng 1, đồng thời tóm tắt dữ liệu trong bảng kết quả. của anh ấy sẽ là một công thức sao chép và dán đơn giản nếu không có nhận xét, nhưng tôi không muốn xóa tất cả các cột bổ sung đó cho mọi nhân viên. Tôi đã xem các bài viết có tổng 2D, nhưng chỉ mục(match() luôn được sử dụng trên tổng và không bao giờ trên tiêu chí

Cảm ơn

Trả lời

Jasmine Donaldson nói.
13 tháng 11 năm 2020 lúc 2. 29 giờ tối

Xin chào,

Tôi đang cố gắng viết một công thức để tính tổng tất cả các chiều cao cho từng mẫu có giá trị lớn hơn 10% giá trị chiều cao cao nhất. Vì vậy, đối với mẫu đầu tiên, chiều cao cao nhất là 604 nên ngưỡng sẽ là 60. 4 và mẫu thứ hai có chiều cao cao nhất là 605 nên ngưỡng sẽ là 60. 5. Tôi muốn tính tổng tất cả các đỉnh trên 60. 4 và 60. 5 tương ứng

Tôi có thể lấy excel để tính tổng tất cả các độ cao của đỉnh.
=IF(B2=B1,"",SUMIF(B. B,B2,G. G))

Hoặc tôi có thể sử dụng mã này để nhận giá trị chính xác, nhưng tôi phải chọn một phạm vi theo cách thủ công để mã này hoạt động.
=SUMIF(G2. G27,"">"&W3)

Tôi tin rằng công thức đầu tiên có thể được điều chỉnh để thêm một tiêu chí khác để nói =IF G. G > X. Nhưng tôi không biết làm thế nào để kết hợp hai công thức của tôi

Nếu bạn có dữ liệu trên nhiều trang tính, bạn có thể cần biết một số kỹ thuật để sử dụng hàm SUMIF. Trong bài viết này, tôi sẽ giới thiệu cho bạn ba phương pháp để bạn có thể sử dụng SUMIF trên nhiều trang tính trong Excel

Giả sử, trong biểu dữ liệu của chúng tôi, chúng tôi có doanh số hàng quý của những người bán hàng khác nhau trên các trang tính khác nhau. Bây giờ chúng tôi muốn tính doanh thu hàng năm của các nhân viên bán hàng khác nhau. Để làm được điều đó, chúng tôi phải tổng hợp doanh số các quý khác nhau của từng nhân viên bán hàng

Mục lục

Tải sách bài tập thực hành

SUMIF trên nhiều trang tính. xlsm

Ba phương pháp để sử dụng Sumif trên nhiều trang tính

Phương pháp 1. Sử dụng hàm SUMIF cho từng trang tính

Một cách để tính toán là sử dụng hàm SUMIF cho mọi trang tính. Giả sử, chúng ta muốn tính doanh số bán hàng hàng năm của mỗi nhân viên bán hàng trong một trang tính có tên Tóm tắt doanh số. Nhập công thức sau vào ô C5,

=SUMIF('Quarter 1'!B5:B9,'Sales Summary'!B5,'Quarter 1'!C5:C9)+SUMIF('Quarter 2'!B5:B9,'Sales Summary'!B5,'Quarter 2'!C5:C9)+SUMIF('Quarter 3'!B5:B9,'Sales Summary'!B5,'Quarter 3'!C5:C9)

Đây, ‘Hiệp 1′. B5. B9’ = Phạm vi trong trang tính Quý 1 nơi các tiêu chí sẽ được so khớp

'Tóm tắt bán hàng'. B5′ = Tiêu chí

'Quý 1'. C5. C9’ = Phạm vi trong trang tính Quý 1 từ đó giá trị cho tổng sẽ được lấy

Theo cách tương tự, SUMIF được sử dụng cho tất cả các trang tính

Sau khi nhấn ENTER, bạn sẽ nhận được tổng doanh số của cả ba quý của Mark trong ô C5

Kéo doanh số C5 đến cuối tập dữ liệu của bạn và bạn sẽ nhận được doanh số bán hàng hàng năm của tất cả nhân viên bán hàng

Đọc thêm. SUMIF cho nhiều tiêu chí trên các trang tính khác nhau trong Excel (3 phương pháp)

Phương pháp 2. Sử dụng hàm SUMPRODUCT SUMIF và INDIRECT

Không cần lặp lại hàm SUMIF nhiều lần, bạn có thể sử dụng đồng thời hàm SUMPRODUCT, hàm SUMIF và hàm INDIRECT để nhận được kết quả tương tự. Trước tiên, chúng tôi sẽ chèn tên của các trang tính (Quý 1, Quý 2, Quý 3) vào trang tính mà chúng tôi sẽ thực hiện tính toán cho doanh số hàng năm

Sau đó, Nhập công thức sau vào ô C5,

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E$5:$E$7&"'!$B$5:$B$9"),B5,INDIRECT("'"&$E$5:$E$7&"'!$C$5:$C$9")))

Đây, $E$5. $E$7 đề cập đến các trang tính khác nhau cho các giá trị doanh thu hàng quý

B$5. $B$9 = phạm vi tra cứu cho tiêu chí

B5 là tiêu chí (Mark)

$C$5. $C$9 = phạm vi giá trị nếu phù hợp với tiêu chí

Sau khi nhấn ENTER, bạn sẽ nhận được tổng doanh số của cả ba quý của Mark trong ô C5

Kéo doanh số C5 đến cuối tập dữ liệu của bạn và bạn sẽ nhận được doanh số bán hàng hàng năm của tất cả nhân viên bán hàng

---

bài đọc tương tự

• SUMIF với nhiều tiêu chí (5 ví dụ dễ nhất)
• Hàm SUMIF trong Excel cho nhiều tiêu chí (3 phương pháp + tiền thưởng)
• Cách kết hợp Excel SUMIF & VLOOKUP trên nhiều trang tính

---

Phương pháp 3. Sử dụng VBA để tính tổng trên nhiều trang tính

Nếu bạn có nhiều trang tính, hai phương pháp trên có thể rất tốn thời gian và phức tạp. Để tính toán nhanh hơn, bạn có thể nhờ sự trợ giúp của Ứng dụng Visual Basic (VBA) và tạo một công thức tùy chỉnh

Đầu tiên nhấn ALT+F11 để mở cửa sổ VBA. Nhấp chuột phải vào tên trang tính và chọn Chèn> Mô-đun

Một cửa sổ mã sẽ xuất hiện

Sao chép và dán đoạn mã sau vào cửa sổ này,

Function SUMIFOS(lookup_value As Range, ParamArray cellranges() As Variant)

Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean

Dim rng2 As Variant, value As Variant, j As Single

If (UBound(cellranges) + 1) Mod 2 <> 0 Then

  Exit Function

End If

For i = LBound(cellranges) To UBound(cellranges) Step 2

    If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then

    End If

    If cellranges(i).Columns.Count <> 1 Then

        Exit Function

End If

    rng1 = cellranges(i).value

    rng2 = cellranges(i + 1).value

    For j = LBound(rng1) To UBound(rng1)

        For Each value In lookup_value

            If UCase(rng1(j, 1)) = UCase(value) Then a = True

        Next value

        If a = True Then temp = temp + rng2(j, 1)

        a = False

    Next j

Next i

SUMIFOS = temp

End Function

Sau đó, đóng cửa sổ VBA và nhập công thức sau vào ô C5,

=SUMIFOS(B5,'Quarter 1'!B5:B9,'Quarter 1'!C5:C9,'Quarter 2'!B5:B9,'Quarter 2'!C5:C9,'Quarter 3'!B5:B9,'Quarter 3'!C5:C9)

Ở đây, SUMIFOS là hàm tùy chỉnh, B5 là giá trị tra cứu, Quý 1′. C5. C9 là phạm vi cho giá trị trong trang tính có tên là Quý 1 và Quý 1′. B5. B9 là phạm vi cho các tiêu chí trong trang tính có tên Quý 1. Bạn có thể chèn giá trị từ bao nhiêu trang tính tùy ý trong công thức này

Sau khi nhấn ENTER, bạn sẽ nhận được tổng doanh số của cả ba quý của Mark trong ô C5

Kéo doanh số C5 đến cuối tập dữ liệu của bạn và bạn sẽ nhận được doanh số bán hàng hàng năm của tất cả nhân viên bán hàng

Đọc thêm. SUMIF Nhiều dãy [6 cách hữu ích]

Phần kết luận

Sử dụng phương pháp đầu tiên không thuận tiện lắm đối với một số lượng lớn tờ vì nó sẽ tiêu tốn rất nhiều thời gian. Bạn có thể sử dụng phương pháp 1 nếu bạn chỉ có một vài tờ. Nhưng phương pháp 2 và 3 sẽ hiệu quả đối với số lượng trang tính rất lớn

Nếu bạn gặp phải bất kỳ vấn đề nào trong khi sử dụng bất kỳ phương pháp nào để áp dụng SUMIF trên nhiều trang tính, vui lòng để lại nhận xét