Bạn có thể sử dụng công thức mảng trong Google Trang tính không?
|
Đối với nhiều người dùng bảng tính, ngay cả những người dày dặn kinh nghiệm, từ 'Công thức mảng Google Trang tính' có thể gợi lên những hình ảnh ác mộng Show
Đó là lý do tại sao hầu hết mọi người có xu hướng tránh sử dụng công thức mảng trong trang tính của họ Tuy nhiên, một khi bạn hiểu cách sử dụng và ứng dụng của nó, bạn sẽ thấy nó giúp công việc của bạn dễ dàng hơn và nhanh hơn và giữ cho trang tính của bạn trông gọn gàng hơn nhiều Trong hướng dẫn này, chúng tôi sẽ hướng dẫn bạn từng bước trong quy trình và chỉ cho bạn, với các ví dụ đơn giản, cách sử dụng công thức Mảng của Google Trang tính Mục lục Công thức mảng là gì?Trước khi chúng tôi giải thích cách sử dụng công thức mảng trong Google Trang tính, điều cần thiết là phải hiểu 'mảng' là gì. Mảng là một khái niệm toán học bắt nguồn từ ý tưởng về Ma trận (số nhiều cho ma trận). Hãy coi mảng là một tập hợp các giá trị được sắp xếp theo hàng và cột, giống như các ô trong trang tính Google Trang tính Ví dụ, tập hợp các số trong khoảng từ A1 đến C4 dưới đây có thể được coi là một mảng có 4 hàng và 3 cột Công thức mảng là công thức được áp dụng cho các phần tử của một mảng như công thức ở trên. Các công thức này có thể thực hiện nhiều phép tính trên các mục mảng và trả về một kết quả duy nhất hoặc một tập hợp kết quả dưới dạng một mảng mới Công thức mảng hoạt động như thế nào trong Google Sheets?Công thức Mảng Google Trang tính có thể phức tạp đối với nhiều người dùng trang tính. Bạn có thể coi công thức mảng là một hàng hoặc cột giá trị. Điều này có nghĩa là gì, mặc dù? . Bạn có thể đặt công thức ={11, 2, 3} trong một ô. Dấu ngoặc nhọn được sử dụng cho công thức mảng trong Google Trang tính để cho biết dữ liệu sẽ được hiển thị dưới dạng một mảng và cho CÔNG THỨC Mảng của Google Trang tính mà chúng ta sẽ thảo luận sau Nếu bạn nhấn enter, bạn sẽ nhận được các giá trị trong ba cột liền kề Dấu phẩy phân tách các cột, nhưng nếu bạn muốn sử dụng hàng thay thế, bạn sẽ sử dụng dấu chấm phẩy (;) Điều này sẽ cung cấp cho bạn các giá trị trong các hàng khác nhau; . Hy vọng rằng đây là một cách đơn giản để bạn hiểu về mảng và sẽ góp phần tìm hiểu cách hoạt động của ARRAYFORMULA trong Google Trang tính Bây giờ giả sử chúng ta đang cố nhân một số. Thông thường, hàm nhân sẽ lấy một số từ một ô và nhân nó với một số khác từ một ô khác, nghĩa là bạn sẽ chọn và kéo hàm hoặc sử dụng hàm lặp lại Thay vào đó, nếu chúng ta sử dụng hàm ARRAYFORMULA của Google Trang tính, thì chúng ta sẽ nhân một dải dữ liệu với một dải dữ liệu khác. Công thức mảng cho phép bạn sử dụng nhiều công thức cùng một lúc bằng cách cho phép các hàm không hoạt động với mảng làm như vậy. Đây là một trong những cách áp dụng công thức cho toàn bộ cột. Giống như trong ví dụ dưới đây Hai cách để áp dụng công thức mảng trong Google SheetsTrong Google Trang tính, có hai cách để bạn có thể áp dụng công thức mảng cho dữ liệu của mình. Phương pháp đầu tiên là nhấp vào thanh công thức trong khi con trỏ của bạn đang ở công thức bạn muốn chuyển đổi, sau đó nhấn phím tắt CTRL+Shift+Enter (Cmd + Shift + Enter trên máy Mac) trên bàn phím của bạn Cách thứ hai là sử dụng hàm ARRAYFORMULA Hàm ARRAYFORMULAHàm ARRAYFORMULA thực hiện các thao tác trên một dải ô và xuất ra một dải ô thay vì chỉ một giá trị. Điểm khác biệt của nó với các hàm thông thường là hàm tiêu chuẩn chỉ trả về một giá trị duy nhất, trong khi hàm ARRAYFORMULA trả về một dải ô dưới dạng đầu ra. Bạn cũng có thể sử dụng các hàm này với các hàm không phải mảng để làm cho chúng trả về một mảng (hoặc một dải giá trị) Cú pháp của hàm ARRAY FORMULACú pháp của hàm ARRAYFORMULA như sau =ARRAYFORMULA(công_thức_mảng) Như bạn có thể thấy, hàm ARRAYFORMULA chỉ chấp nhận một tham số, có thể là một trong các tham số sau
Tại sao sử dụng công thức mảng?Câu hỏi rõ ràng xuất hiện trong đầu là 'tại sao phải bận tâm'? . Bảng tính ARRAYFORMULA của Google có thể được sử dụng để thực hiện các phép tính phức tạp như tính tổng chạy Chà, nó chắc chắn đáng để rắc rối vì công thức mảng có một số lợi ích. Đây chỉ là một vài trong số họ
Công thức mảng được sử dụng như thế nào trong Google Sheets?Chúng ta hãy xem một ví dụ đơn giản để hiểu cách sử dụng công thức mảng. Giả sử bạn có dữ liệu sau với chi phí đơn vị và số lượng bán cho từng mặt hàng Để tìm tổng doanh số bán hàng cho từng mặt hàng, thông thường bạn cần nhân đơn giá và số lượng bán cho mặt hàng đầu tiên bằng cách áp dụng công thức =B2*C2, sau đó sử dụng bộ điều khiển điền để dán công thức xuống phần còn lại của . Điều này có nghĩa là đối với 5 mục, bạn có 5 công thức riêng biệt Tuy nhiên, khi sử dụng công thức mảng, bạn có thể bỏ qua bước dán xuống và trực tiếp lấy tổng doanh số cho từng mặt hàng bằng một công thức duy nhất, như sau = CÔNG THỨC Mảng(B2. B5*C2. C5) Ngay sau khi bạn nhấn phím quay lại sau khi nhập công thức trên vào ô D2, bạn sẽ nhận được kết quả sau Công thức mảng lấy từng ô từ phạm vi B2. B5 và nhân với ô tương ứng từ phạm vi C2. C5, hiển thị kết quả trong ô tương ứng của cột D. Như bạn có thể thấy, chỉ với một công thức, chúng ta đã có tổng doanh số cho cả 5 mặt hàng Nếu mục tiêu chính của bạn là đạt được Tổng doanh thu lớn (cho tất cả các mặt hàng), thì có thể bạn không muốn hiển thị cột bổ sung cho tổng doanh số bán hàng trên mỗi mặt hàng (được hiển thị trong cột D trong ảnh chụp màn hình ở trên). Công thức mảng giúp bạn có thể đạt được điều này bằng một công thức duy nhất và không cần thêm cột trợ giúp D. Đây là công thức mà bạn có thể sử dụng để có được Tổng cộng =SUM(DẠNG Mảng(B2. B5*C2. C5)) Đó là tất cả. Bạn không cần nhập thêm cột D và tất cả công việc của bạn đã hoàn thành chỉ với một công thức Ví dụ đơn giản sử dụng công thức mảng Google SheetsTìm nhanh các tổng lớn và tránh dán công thức xuống chỉ là hai trong số những cách mà công thức mảng có thể biến đổi trò chơi bảng tính của bạn. Có một số điều khác mà bạn có thể làm với công thức mạnh mẽ này. Tìm hiểu cách sử dụng công thức mảng trong Google Trang tính dễ dàng hơn với các ví dụ về ARRAYFORMULA Dưới đây là một vài cách khác mà công thức Mảng có thể được sử dụng Sử dụng công thức mảng để nhân ma trậnCông thức mảng cung cấp một cách tuyệt vời để thực hiện phép nhân ma trận. Ví dụ: Giả sử bạn có ma trận hàng có tên là Ma trận1 gồm 5 hàng và ma trận cột có tên là Ma trận2 gồm có 4 cột, như minh họa bên dưới Bạn có thể tìm tích của hai ma trận này bằng công thức mảng = CÔNG THỨC Mảng(D2. G2*B3. B7) Như bạn có thể thấy từ ảnh chụp màn hình bên dưới, điều này tạo ra một ma trận 5×4 chứa tích của Matrix1 và Matrix2 Ghi chú. Điều này cũng có thể được thực hiện bằng chức năng MMULT trong Google Trang tính Sử dụng Công thức mảng với Hàm tổng hợp như COUNTIFS và SUMIFSBạn cũng có thể sử dụng Công thức mảng với các hàm tổng hợp như SUMIF, SUMIFS, COUNTIF và COUNTIFS để tính tổng nhanh các giá trị trong một dải ô dựa trên một hoặc nhiều điều kiện Ví dụ: giả sử bạn có số tiền bán hàng sau theo mặt hàng khôn ngoan cho mỗi cửa hàng Bạn muốn biết tổng doanh thu của từng mặt hàng và hiển thị kết quả này trong ô F3. F5. Nếu bạn đã sử dụng hàm SUMIF đơn giản cho việc này, bạn sẽ cần áp dụng công thức ba lần (một lần cho mỗi mục) Điều này là do hàm SUMIF chỉ cho phép bạn chỉ định một ô trong tham số điều kiện tại một thời điểm. Tuy nhiên, với công thức mảng, có thể chỉ định một dãy ô trong tham số điều kiện như sau =ARRAYFORMULA(SUMIF(B2. B10,E3. E5,C2. C10)) Lưu ý rằng trong tham số thứ hai, chúng tôi đã chỉ định phạm vi ô E3. E5. Vì vậy, chúng tôi có thể nhận được kết quả cho cả ba mục cùng một lúc Bạn có thể sử dụng hàm COUNTIF với Công thức mảng theo cách tương tự nếu cần đếm số lượng mục nhập cho mỗi mục Áp dụng công thức mảng với các hàm SUMIFS và COUNTIFS phức tạp hơn một chút. Vì SUMIFS và COUNTIFS về cơ bản trả về tổng của một mảng dựa trên nhiều điều kiện, nên kết quả chỉ có thể là một giá trị Như vậy, dù bạn có dùng công thức mảng có hàm SUMIFS hay COUNTIFS thì cũng không khác biệt. Bạn sẽ chỉ nhận được một kết quả, như trong ví dụ bên dưới Như chúng ta có thể thấy từ ảnh chụp màn hình hiển thị bên dưới, ngay cả khi sử dụng các công thức Mảng của Google Trang tính cũng không hoạt động Để thực hiện công việc này, bạn sẽ cần ứng biến công thức của mình bằng cách sử dụng hàm SUMIF. Bây giờ, một câu hỏi rõ ràng xuất hiện trong đầu – Làm cách nào bạn có thể sử dụng nhiều điều kiện với hàm SUMIF? Vì vậy, giả sử bạn chỉ muốn tìm tổng doanh số cho từng mặt hàng từ cửa hàng ở Vương quốc Anh Công thức của bạn sau đó sẽ là =ARRAYFORMULA(SUMIF(A2. A10&B2. B10,F3. F5&E3. E5,C2. C10)) Lưu ý rằng chúng tôi có hai điều kiện ở đây
Chúng tôi chỉ cần kết hợp các phạm vi và tiêu chí tương ứng của chúng bằng cách sử dụng toán tử dấu và. Sau đó, chúng ta bọc một hàm ARRAYFORMULA xung quanh công thức SUMIF Hàm SUMIF hiện mở rộng kết quả của nó ra toàn bộ cột và thậm chí chúng ta có thể sử dụng nó để kiểm tra nhiều hơn một tiêu chí Cách sử dụng Công thức mảng Google Sheets với Hàm IFHàm IF thường được sử dụng để đặt điều kiện cho một công thức trả về hai kết quả. FALSE hoặc TRUE. Bạn cũng có thể sử dụng hàm mảng Google Trang tính với hàm IF. Hãy sử dụng một ví dụ để hiểu thêm Trong bảng dưới đây, chúng tôi có số lượng bán giày của các thương hiệu khác nhau. Giả sử bạn muốn tìm hiểu thương hiệu giày nào đã bán được hơn mười đôi giày Trước tiên, bạn chọn cột mà kết quả của bạn sẽ xuất hiện, sau đó nhập công thức IF vào thanh công thức Sử dụng phím tắt Ctrl + Shift + Enter để nhập CÔNG THỨC Mảng. (Dùng Command + Shift Enter cho Macbook) Khi bạn nhấn Enter, bạn sẽ nhận được kết quả trong cột đã chọn. Những đôi giày bán được trên 10 đôi sẽ được ghi là Có và những đôi ít đôi hơn sẽ được biểu thị là Không Bảng công việc mẫuNhấp vào liên kết nếu bạn muốn sử dụng trang tính Google mẫu của chúng tôi Cách sử dụng VLOOKUP và ARRAYFORMULA trong Google Trang tínhChúng ta có thể sử dụng công thức mảng trong Google Sheets cùng với VLOOKUP để tra cứu dữ liệu trong nhiều cột và truy xuất dữ liệu cũng như thông tin tương ứng của nó Ví dụ: giả sử chúng ta muốn biết có bao nhiêu đôi giày và bao nhiêu lần bán được từ kho giày số 67 trong bảng tính bên dưới Nếu bạn tự sử dụng công thức VLOOKUP, thì công thức này sẽ chỉ truy xuất các số được bán cho số cổ phiếu =VLOOKUP(“4006-674-67”,$A$2. $E$11,4,0) Tuy nhiên, nếu chúng ta kết hợp nó với hàm ARRAYFORMULA của Google Trang tính, chúng ta có thể nhận được nhiều giá trị cho nhiều cột hoặc hàng cùng một lúc. Bằng cách này, bạn có thể tra cứu nhiều dữ liệu hơn chỉ bằng một công thức thay vì thực hiện từng công thức VLOOKUP Nếu chúng ta muốn lấy nhiều giá trị cho một mã hàng, ví dụ: mặt hàng, thương hiệu, tổng doanh số và đã bán>10 cho mã hàng đầu tiên, chúng ta sẽ sử dụng công thức sau =ARRAYFORMULA(VLOOKUP($A$2,$A$1. $G$9,{2,3,6,7},FALSE)) Các số trong dấu ngoặc nhọn đại diện cho các cột mà bạn muốn truy xuất dữ liệu. Điều này sẽ trả lại các giá trị từ các cột vào một hàng mới, như hình bên dưới Có nhiều cách để sử dụng công thức mảng trong Google Sheets với công thức VLOOKUP, nhưng trong ví dụ này, chúng tôi đã sử dụng một phương pháp đơn giản để dễ hiểu Bạn cũng có thể sử dụng VLOOKUP với hàm ARRAYFORMULA với nhiều tiêu chí hoặc tạo trang tính mới với một số dữ liệu từ trang tính cũ. Sau đó, bạn cũng có thể sử dụng hai công thức này cùng nhau để nối hai trang tính Cách xóa các ô trống thừa trong Google Sheets ARRAYFORMULAHầu hết các hàm bạn sử dụng cùng với hàm công thức mảng, nếu có ô trống sẽ báo lỗi #REF Nếu chúng ta đang sử dụng hàm SUMIF với ARRAYFORMULA trong Google Trang tính và có thêm một số ô trống, thì công thức sẽ trả về giá trị mảng 0 cho các ô trống Để loại bỏ các ô thừa, bạn có thể sử dụng hàm IF hàm LEN bằng cách sử dụng công thức tương tự như ARRAYFORMULA này =ARRAYFORMULA(IF(LEN(I2. I11),(SUMIF(B2. B11,I2. I11,D2. D11)),)) Công thức này sẽ loại bỏ tất cả các ô trống thừa Công thức mảng trong trang tính cũng sẽ trả về 0 cho các ô trống thừa trong vùng tiêu chí. Trong trường hợp này, để loại bỏ các ô trống thừa, chúng ta có thể sử dụng IF+ISBLANK. Điều này hoạt động theo cách tương tự như IF+LEN ở trên Nếu bạn đang sử dụng ARRAYFORMULA trong Google Trang tính cùng với VLOOKUP, nó thường sẽ trả về N/A nếu có các ô trống. Để bỏ qua các ô trống, chúng ta có thể thêm công thức IFERROR trong công thức. =ARRAYFORMULA(VLOOKUP(I2. I11,B2. D11,3,0)) Thêm hàm IFERROR trước công thức mảng. Điều này sẽ loại bỏ N/A và để trống các ô Sử dụng Công thức mảng Google Sheets để nối văn bảnChúng ta cũng có thể sử dụng công thức mảng để kết hợp các cột và nối văn bản chứa trong chúng. Ví dụ: chúng tôi có trong ảnh chụp màn hình bên dưới, danh sách họ và tên Nếu bạn muốn kết hợp hai cột này trong một lần, bạn chỉ cần sử dụng công thức mảng với toán tử nối như sau = CÔNG THỨC Mảng(A2. A7&” “&B2. B7) Khi bạn nhấn phím quay lại sau khi nhập công thức trên, bạn sẽ thấy tất cả tên và họ được kết hợp với nhau bằng khoảng trắng ở giữa Theo cách tương tự, bạn có thể sử dụng toán tử nối để nhanh chóng kết hợp bất kỳ loại văn bản nào với số, ngày tháng hoặc ký tự đặc biệt Phần kết luậnTrong hướng dẫn này, chúng tôi đã cung cấp cho bạn một hướng dẫn đơn giản về các công thức Mảng của Google Sheets. Chúng tôi đã giải thích cách chúng hoạt động và với các ví dụ phù hợp, chúng tôi đã cho bạn thấy những công thức này có thể linh hoạt như thế nào. Chúng tôi hy vọng bạn thấy hướng dẫn từng bước của chúng tôi hữu ích và dễ làm theo Bài viết phổ biến nhất5 cách đơn giản để làm nổi bật các bản sao trong Google SheetsNẾU CHỨA Công thức Google Trang tính [2 Tùy chọn thông minh]Cách áp dụng công thức cho toàn bộ cột trong Google SheetsCách VLOOKUP từ một trang tính khác trong Google Trang tính Nahid AkhterNahid Akhter là một nhà văn, lập trình viên và người tạo nội dung khóa học trực tuyến. Cô có bằng Thạc sĩ Khoa học Máy tính và đã làm việc trong lĩnh vực giáo dục hơn 14 năm. Chuyên môn của cô ấy là lập trình và viết về Công nghệ, và các lĩnh vực cô ấy quan tâm bao gồm năng suất Văn phòng, Trí tuệ Nhân tạo, thiết kế và phát triển Web Google Trang tính có hỗ trợ công thức mảng không?Bạn cũng có thể tạo mảng của riêng mình theo công thức trong bảng tính bằng cách sử dụng dấu ngoặc { } . Dấu ngoặc cho phép bạn nhóm các giá trị lại với nhau, trong khi bạn sử dụng dấu chấm câu sau để xác định thứ tự các giá trị được hiển thị. dấu phẩy. Các cột riêng biệt để giúp bạn viết một hàng dữ liệu trong một mảng.
Excel có ArrayFormula như Google Trang tính không?Có thể sao chép ArrayFormula của Google Trang tính trong MS Excel bằng cách sử dụng công thức OFFSET() ngay cả khi nó không đơn giản như hàm ArrayFormula. Tài liệu đầy đủ của chức năng có thể được tìm thấy ở đây.
Công thức mảng có nhanh hơn Google Trang tính không?Chậm hơn rất, rất nhiều . Việc kết hợp các VLOOKUP với Công thức mảng để trả về nhiều giá trị cột với một lần tra cứu sẽ chậm hơn đáng kể so với việc chỉ sử dụng nhiều VLOOKUP riêng lẻ. |
