Chỉnh sửa google sheet
|
Hướng dẫn này sẽ trình bày cách sử dụng chức năng SUMIFS để tổng hợp dữ liệu với các ngày cụ thể trong Excel và Google Trang tính Show
 Tổng giá trị nếu ngày bằng nhauHàm SUMIFS tổng hợp dữ liệu trả lời các tiêu chí định trước. Cú pháp của nó là Ví dụ này sẽ tổng hợp số lượng giao hàng theo kế hoạch vào một ngày cụ thể bằng cách sử dụng các chức năng SUMIFS và ngày. Giao hàng theo kế hoạch vào một ngày cụ thể bằng cách sử dụng hàm SUMIFS và DATE Trong ví dụ này, chúng tôi muốn tổng hợp các giao dịch được lên kế hoạch vào ngày 2/1/2021. Để nhập ngày này làm tiêu chí trong công thức của chúng tôi, chúng tôi sử dụng hàm ngày. Kế hoạch giao hàng vào ngày 2/1/2021. Để nhập Ngày này làm tiêu chí trong công thức của chúng tôi, chúng tôi sử dụng Hàm DATE Trong trường hợp này, biểu thức hàm ngày ở trên là tất cả những gì chúng ta cần kiểm tra xem ngày có bằng 1/2/2021 hay không. Tuy nhiên, một thử nghiệm hợp lý trong hàm SUMIFS thường yêu cầu một toán tử trong các trích dẫn kép (Hồi), do đó, các tiêu chí cũng có thể được viết là Giá trị tổng hợp nếu ngày bằng nhau - tham chiếu ôThông thường, đó là thực tế xấu đối với các giá trị ngày cứng đối với các công thức. Thay vào đó, việc sử dụng một ô riêng biệt sẽ linh hoạt hơn để xác định ngày được sử dụng làm tiêu chí tổng thể Điều này cho phép người dùng thay đổi ngày được sử dụng trong công thức theo cách nhanh hơn và trực quan hơn bằng cách chỉnh sửa ô F3. Ngày được sử dụng trong công thức theo cách nhanh hơn và trực quan hơn bằng cách chỉnh sửa ô F3 Lưu ý rằng các toán tử logic khác cũng có thể được sử dụng trong SUMIFS, chẳng hạn như
Key tài liệu tham khảo tế bàoĐể làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không bị khóa tham chiếu ô Nhưng các công thức này sẽ không hoạt động chính xác khi sao chép và dán vào nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tài liệu tham khảo ô đã bị khóa như thế này Đọc bài viết của chúng tôi về khóa tham chiếu tế bào để tìm hiểu thêm Tổng giá trị nếu ngày bằng nhau trong Google Trang tínhCác công thức này hoạt động chính xác giống nhau trong Google Trang tính như trong Excel Hướng dẫn tìm cách sử dụng chức năng SUMIF trong bảng tính Google để tổng hợp các ô. Bạn sẽ tìm thấy các ví dụ công thức cho văn bản, số và ngày và tìm hiểu cách tổng hợp với nhiều tiêu chí Một số chức năng tốt nhất trong Google Trang tính là chức năng giúp bạn tóm tắt và phân loại dữ liệu. Hôm nay, chúng ta sẽ xem xét kỹ hơn một trong những chức năng như vậy - SUMIF - một công cụ mạnh mẽ để tổng hợp các ô. Trước khi nghiên cứu các ví dụ về cú pháp và công thức, hãy để tôi bắt đầu với một vài nhận xét quan trọng Google Trang tính có hai chức năng để bổ sung các số dựa trên các điều kiện. SUMIF và SUMIF. Cái đánh giá trước chỉ là một điều kiện trong khi hợp trường sau đó có thể kiểm tra nhiều điều kiện tại một thời điểm. Trong hướng dẫn này, chúng tôi sẽ chỉ tập trung vào hàm SUMIF, việc sử dụng hàm SUMIF sẽ được cập nhật trong bài viết tiếp theo. SUMIF và SUMIFS. Cái trước chỉ đánh giá một điều kiện trong khi cái sau có thể kiểm tra nhiều điều kiện cùng một lúc. Trong hướng dẫn này, chúng tôi sẽ chỉ tập trung vào hàm SUMIF, việc sử dụng SUMIFS sẽ được đề cập trong bài viết tiếp theo Nếu bạn biết cách sử dụng SUMIF trong máy tính để bàn Excel hoặc Excel trực tuyến, SUMIF trong Google Trang tính sẽ là một miếng bánh ngọt cho bạn vì cả hai về cơ bản đều giống nhau. Nhưng đừng vội vã đóng trang này - bạn có thể tìm thấy một vài công thức tổng hợp không đáng tin cậy nhưng rất hữu ích mà bạn không biết SUMIF trong Google Sheets - Cú pháp và mục đích sử dụng cơ bảnHàm SUMIF là Google Trang tính được thiết kế để tổng hợp số liệu dựa trên một điều kiện. Cú pháp của nó như sau SUMIF (phạm vi, tiêu chí, [sum_range]) Ở đâu
Ví dụ, hãy tạo một công thức đơn giản sẽ có tổng số trong cột B nếu cột A có chứa một mục bằng "mục mẫu" Đối với điều này, chúng tôi xác định các đối số sau
Đặt tất cả các đối số lại với nhau, chúng tôi có công thức sau Và nó hoạt động chính xác như nó nên Google Sheets Sumif ví dụTừ ví dụ trên, bạn có thể có ấn tượng rằng việc sử dụng các công thức SUMIF trong bảng tính của Google dễ dàng đến mức bạn có thể làm điều đó với đôi mắt nhắm. Trong hầu hết các trường hợp, nó thực sự là như vậy. ) nhưng vẫn có một số thủ thuật và cách sử dụng không tầm thường có thể làm cho công thức của bạn hiệu quả hơn. Các ví dụ dưới đây cho thấy một vài trường hợp sử dụng điển hình. Để làm ví dụ dễ theo dõi hơn, tôi mời bạn mở Sumif Sumif của chúng tôi Công thức SUMIF với tiêu chí văn bản (khớp chính xác)Để bổ sung các số có một văn bản cụ thể trong một cột khác trong cùng một hàng, bạn chỉ cần cung cấp văn bản quan tâm trong đối số tiêu chí của công thức SUMIF của bạn. Như thường lệ, bất kỳ văn bản nào trong bất kỳ đối số nào của bất kỳ công thức nào cũng nên được đặt trong "trích dẫn kép" Ví dụ. để có được tổng số chuối, bạn sử dụng công thức này Hoặc, bạn có thể đặt tiêu chí vào một số ô và tham khảo ô đó Công thức này rõ ràng, phải không? . trừ chuối? Nếu một "mục loại trừ" được bắt đầu trong một ô, thì bạn sẽ gửi lại không bằng toán tử trong trích dẫn kép ("") và kết hợp toán tử và tham chiếu ô bằng cách sử dụng dấu và (&). Ví dụ Ảnh chụp màn hình sau đây có thể hiển thị cả "tổng nếu bằng" và "tổng nếu không bằng" các công thức trong hành động Xin lưu ý rằng tổng số trong Google Trang tính tìm kiếm chính xác văn bản được chỉ định. Trong ví dụ này, chỉ có số lượng chuối được tổng hợp, chuối xanh và chuối Goldfinger không được bao gồm. Để tổng hợp phù hợp với một phần, hãy sử dụng các ký tự ký tự đại diện như trong ví dụ tiếp theo. một cách chính xác. Trong ví dụ này, chỉ số lượng Chuối được tổng hợp, không bao gồm chuối xanh và chuối Goldfinger. Để tính tổng bằng khớp một phần, hãy sử dụng các ký tự đại diện như minh họa trong ví dụ tiếp theo Công thức SUMIF với các ký tự đại diện (kết hợp một phần)Trong các trường hợp bạn muốn tổng hợp các ô trong một cột nếu một ô trong một cột khác chứa văn bản hoặc ký tự cụ thể có thể là một phần của ô nội dung, bao gồm một trong các ký tự đại diện sau trong tiêu đề . một phần của nội dung ô, bao gồm một trong các ký tự đại diện sau trong tiêu chí của bạn
Ví dụ, để tổng hợp số lượng của tất cả các loại chuối, hãy sử dụng công thức này Bạn cũng có thể sử dụng các ký tự đại diện giống với các tài liệu tham khảo di động. Đối chiếu với điều này, kèm theo các ký tự đại diện trong các dấu ngoặc kép và nối nó với một ô tham chiếu Dù bằng cách nào, công thức SUMIF của chúng tôi cộng lại số lượng của tất cả các chuối Để phù hợp với một dấu hỏi thực tế hoặc dấu hoa thị, tiền tố nó với nhân vật Tilde (~) như "~?" Ví dụ. to the total number in the column B has a dấu hoa thị trong cột A trong cùng một hàng, hãy sử dụng công thức này Thậm chí bạn có thể nhập dấu hoa thị vào một số tế bào, nói B1 và cuối tế bào đó với Tilde Char Tổng hợp cảm ứng trường trong google sheetTheo mặc định, SUMIF trong Google Sheets không thấy sự khác biệt giữa chữ cái nhỏ và chữ hoa. Để buộc nó phải đặt chữ hoa và các ký tự thông thường khác nhau, hãy sử dụng SUMIF để kết hợp với các hàm tìm kiếm và ArrayFormula SUMIF(ArrayFormula(found("văn bản", phạm vi)), 1, sum_range) Giả sử bạn có một danh sách các đơn hàng trong A5. A13 and a number of match in C5. C13, in that the same number menu xuất hiện trong một số hàng. Bạn nhập ID đích thứ tự trong một ô số, giả sử B1 và sử dụng công thức sau để trả về tổng số đơn hàng Cách thức hoạt động của công thức nàyĐể hiểu rõ hơn về logic của công thức, hãy chia nó thành các phần có ý nghĩa Phần khó nhất là đối số phạm vi. ArrayFormula (Tìm (B1, A5. A13))) Bạn sử dụng chức năng tìm trường hợp cảm ứng với trường hợp để tìm ID chính xác thứ tự. Vấn đề là một công thức tìm kiếm thông thường chỉ có thể được tìm kiếm trong một ô. Để tìm kiếm trong một phạm vi, cần có một mảng công thức, vì vậy bạn sẽ tìm thấy bên trong ArrayFormula Khi kết hợp ở trên tìm thấy một kết hợp chính xác, nó sẽ trả về 1 (vị trí của ký tự được tìm thấy đầu tiên), nếu không thì lỗi #Value. Vì vậy, điều duy nhất còn lại để bạn làm là tổng hợp các số tiền tương ứng với 1. Đối với điều này, bạn đặt 1 trong đối số tiêu chí và C5. C13 trong đối số SUM_RANGE. xong Công thức Sumif cho các sốĐể tổng hợp câu trả lời cho một điều kiện định trước, hãy sử dụng một trong các so sánh toán học trong công thức SUMIF của bạn. Trong hầu hết các trường hợp, việc chọn một toán tử thích hợp không phải là vấn đề. Nhúng nó vào tiêu chí đúng cách có thể là một chế độ thức Tổng nếu lớn hơn hoặc ít hơnĐể so sánh các nguồn với một số cụ thể, hãy sử dụng một trong các logic toán tử sau
Ví dụ. to add the number in B5. B13 lớn hơn 200, hãy sử dụng công thức này Xin lưu cú pháp chính xác của tiêu chí. Một số tiền tố với so sánh toán tử và toàn bộ cấu trúc cấu trúc được đặt trong các dấu ngoặc kép Hoặc, bạn có thể nhập số vào một số ô và kết nối toán tử so sánh với tham chiếu ô Thậm chí bạn có thể nhập số liệu so sánh toán tử và số vào các ô riêng biệt và kết nối các tế bào đó Theo cách tương tự, bạn có thể sử dụng các logic toán tử khác như Tổng nếu lớn hơn hoặc bằng 200 Total if small more than 200 Tổng nếu nhỏ hơn hoặc bằng 200 Total if byĐể tổng hợp các số bằng một số cụ thể, bạn có thể sử dụng dấu bình đẳng (=) cùng với số hoặc bỏ qua dấu bình đẳng và chỉ bao gồm các số trong đối số tiêu chí Ví dụ. to add amount in the column B has a number in the column C by 10, use any công thức nào dưới đây hoặc hoặc Trong đó B1 là tế bào với số lượng cần thiết Total if not equalTo other number with the number is only, used not by numerical (). không bằng toán tử (<>) Trong ví dụ của chúng tôi, để thêm số lượng trong cột B có bất kỳ số lượng nào ngoại trừ 10 trong cột C, đi ngược lại với một trong các công thức này Ảnh chụp màn hình bên dưới đây cho thấy kết quả Google Sheets Sumif Công thức cho ngàyĐể tổng hợp các giá trị dựa trên các tiêu chí hàng ngày, bạn cũng sử dụng các phép toán so sánh như được hiển thị trong các ví dụ trên. Trục vít là một ngày nên được cung cấp theo định dạng mà Google Trang tính có thể hiểu được Exterminate, to the money in B5. B13 cho ngày giao hàng trước ngày 11 tháng 9 năm 2018, hãy xây dựng tiêu chí theo một trong những cách này Trong đó B1 là ngày đích Trong trường hợp bạn muốn tổng hợp các ô dựa trên có điều kiện dựa trên ngày hôm nay, hãy bao gồm hàm TODAY () trong các đối số tiêu chí. ngày hôm nay, bao gồm hàm TODAY() trong đối số tiêu chí Ví dụ, chúng ta hãy tạo một công thức bổ sung số tiền cho công việc giao hàng ngày nay Lấy ví dụ xa hơn, chúng ta có thể tìm thấy tổng số giao hàng trong quá khứ và tương lai Trước hôm nay Sau ngày hôm nay Tổng số dựa trên các ô trống hoặc không trốngTrong nhiều vấn đề, bạn có thể cần phải tổng hợp các giá trị trong một cột nhất định nếu một ô tương ứng trong một cột khác là hoặc không trống Đối với điều này, hãy sử dụng một trong các tiêu chí sau trong công thức tổng hợp Google Trang tính của bạn Total if void
Total if not empty
Ví dụ. để tổng hợp các số tiền mà ngày giao hàng đã được đặt (một ô trong cột C không trống), hãy sử dụng công thức này. không trống), sử dụng công thức này To have been total number does not have date of delivery (một ô trong cột C trống), hãy sử dụng số tiền này. trống), hãy sử dụng cái này Google Trang tính Sumif với nhiều tiêu chí (hoặc logic)Hàm SUMIF trong Google Trang tính được thiết kế để bổ sung các giá trị chỉ dựa trên một tiêu chí. Để tổng hợp với nhiều tiêu chí, bạn có thể thêm hai hoặc nhiều hàm tổng hợp với nhau Ví dụ. tổng hợp ứng dụng và cam, sử dụng công thức này Hoặc, đặt tên vật phẩm trong hai ô riêng biệt, nói B1 và B2 và sử dụng từng ô đó làm tiêu chí Xin lưu ý rằng công thức này hoạt động như SUMIF với hoặc logic - nó tổng hợp các giá trị nếu ít nhất một trong các tiêu chí chỉ được đánh giá là câu trả lời. HOẶC hợp lý - tính tổng các giá trị nếu đáp ứng ít nhất một trong các tiêu chí đã chỉ định Trong ví dụ này, chúng tôi thêm các giá trị trong cột B nếu cột A bằng "Táo" hoặc "Orange". Nói cách khác, SUMIF () + SUMIF () hoạt động giống như định dạng giả sau (không phải là thật, nó chỉ có thể hiện logic. ). Sumif (A. A, "Táo" hoặc "Cam", B. b) Nếu bạn đang tìm cách tổng hợp các điều kiện với logic, tức là thêm các giá trị khi tất cả các tiêu chí chỉ được trả lời đúng, hãy sử dụng hàm SUMFS của Google Sheets. VÀ logic, tôi. e. cộng các giá trị khi đáp ứng tất cả các tiêu chí đã chỉ định, hãy sử dụng hàm SUMIFS của Google Trang tính Google Sheets Sumif - Những điều cần nhớBây giờ bạn đã biết các loại hạt và bu lông của hàm SUMIF trong Google Trang tính, có thể là một ý tưởng tốt để tạo ra một bản tóm tắt về những gì bạn đã học 1. Sumif chỉ có thể đánh giá một điều kiệnCú pháp của hàm SUMIF chỉ cho phép một phạm vi, một tiêu chí và một phạm vi tổng. Để tổng hợp với nhiều tiêu chí, hãy thêm một hàm tổng hợp với nhau (hoặc logic) hoặc sử dụng các công thức SUMIFS (và logic). tính tổng với nhiều tiêu chí, cộng nhiều hàm SUMIF với nhau (logic OR) hoặc sử dụng công thức SUMIFS (logic AND) 2. Hàm SUMIF không cảm ứng trường hợpNếu bạn đang tìm kiếm một công thức SUMIF cảm ứng trường hợp có thể phân biệt giữa các ký tự chữ hoa và chữ thường, hãy sử dụng SUMIF kết hợp với ArrayFormula và tìm kiếm như trong ví dụ này 3. Cung cấp phạm vi có kích thước bằng nhau và sum_rangeTrên thực tế, đối số SUM_RANGE chỉ xác định ô trên cùng bên trái của phạm vi thành tổng, khu vực còn lại được xác định bởi kích thước của đối số phạm vi To set it other nhau, SUMIF(A1. A10, "Táo", B1. B10) và SUMIF (A1. A10, "Táo", B1. B100) sẽ có cả hai giá trị tổng trong phạm vi B1. B10 because it has the same size as range vi (A1. A10) Vì vậy, ngay cả khi bạn nhầm lẫn một số phạm vi số tiền sai, Google Trang tính vẫn sẽ tính toán công thức của bạn, với điều kiện ô trên cùng bên trái của SUM_RANGE là chính xác Điều đó nói rằng, vẫn nên cung cấp phạm vi có kích thước như nhau và tổng phạm vi để tránh tội lỗi và ngăn chặn các vấn đề không quán nhất. 4. Dumping cú pháp của SUMIF SUMIFĐối với công thức tổng hợp Google Trang tính của bạn đang hoạt động chính xác, hãy thể hiện các tiêu chí đúng cách
5. Phạm vi key key với các tham chiếu ô tuyệt đối nếu cầnNếu bạn có kế hoạch sao chép hoặc chuyển công thức SUMIF của mình vào một điểm sau, hãy chỉnh sửa các phạm vi bằng cách sử dụng các tham chiếu ô tuyệt đối (có dấu $) như SUMIF ($ a $ 2). $A$10, "Táo", $B$2. $$B$10) Đây là cách bạn sử dụng chức năng SUMIF trong Google Sheets. Để có cái nhìn kỹ hơn về các công thức được thảo luận trong hướng dẫn này, bạn có thể mở Sumif Sumif của chúng tôi. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới You can also quan tâmLàm thế nào để bạn tổng hợp các giá trị với cùng một ngày?Để tổng hợp các giá trị trong phạm vi ngày định trước nhất, hãy sử dụng công thức SUMIFS với ngày bắt đầu và kết thúc làm tiêu chí. Cú pháp của hàm SUMIFS yêu cầu trước bạn chỉ xác định các giá trị để bổ sung (sum_range), sau đó cung cấp các cặp phạm vi/tiêu chí. Trong trường hợp của chúng tôi, phạm vi (một danh sách ngày) sẽ giống nhau cho cả hai tiêu chí. sử dụng công thức SUMIFS với ngày bắt đầu và ngày kết thúc làm tiêu chí . Cú pháp của hàm SUMIFS yêu cầu trước tiên bạn chỉ định các giá trị cần cộng (sum_range), sau đó cung cấp các cặp phạm vi/tiêu chí. Trong trường hợp của chúng tôi, phạm vi (danh sách ngày) sẽ giống nhau cho cả hai tiêu chí. Làm cách nào để tổng hợp các giá trị tương ứng có cùng tên trong Google Trang tính?Cách kết hợp các hàng lặp, hợp nhất các giá trị và bổ sung các quyền phụ trong Google Trang tính. . Select your data Chọn các cột chính với các bản sao Chọn cách mang Uniques đến một hàng. Tính toán các số hoặc hợp nhất các giá trị đề cập đến cùng một bản ghi |
