Công thức mảng là công thức rất hữu ích và mạnh mẽ được sử dụng để thực hiện một số phép tính rất phức tạp trong excel, công thức mảng còn được gọi là công thức CSE vì để thực thi công thức mảng chúng ta cần nhấn CTRL + SHIFT + ENTER thay vì chỉ nhấn enter, có hai loại công thức mảng, một loại cho chúng ta một kết quả duy nhất và một loại khác cho chúng ta nhiều kết quả.
Công thức mảng trong Excel
Mảng trong excel có thể được gọi là công thức mảng trong excel. Mảng trong excel là công thức rất mạnh mẽ cho phép chúng ta thực hiện các phép tính phức tạp.
Đầu tiên, điều đầu tiên, Array là gì? Mảng là tập hợp các giá trị hoặc biến trong tập dữ liệu như (a, b, c, d) là một mảng có các giá trị từ “a” đến “d”. Tương tự, trong mảng excel là một dải ô có giá trị,
Trong ảnh chụp màn hình ở trên, các ô từ B2 đến ô G2 là một mảng hoặc dải ô.
Chúng còn được gọi là “công thức CSE” hoặc “Control Shift Enter Formulas” vì chúng ta cần nhấn Ctrl + Shift + Enter để tạo công thức mảng trong Excel.
Trong excel, chúng ta có hai loại công thức Mảng:
- Một trong đó cho chúng ta một kết quả duy nhất.
- Một cái khác cho chúng ta nhiều hơn một kết quả.
Chúng ta sẽ tìm hiểu cả hai loại công thức Mảng về chủ đề này.
Giải thích công thức mảng trong Excel
Như đã giải thích, công thức mảng trong Excel là công thức mạnh mẽ giúp chúng ta thực hiện các phép tính rất phức tạp.
Từ các ví dụ trên, chúng ta đã học được các công thức này trong excel giúp đơn giản hóa các phép tính phức tạp và dài dòng. Tuy nhiên, có một điều cần nhớ, trong ví dụ 2, nơi chúng tôi trả về nhiều giá trị bằng cách sử dụng các công thức này trong excel. Chúng ta không thể thay đổi hoặc cắt giá trị của ô vì nó là một phần của mảng.
Giả sử chúng ta muốn xóa ô G6 excel sẽ cho chúng ta một lỗi hoặc nếu chúng ta thay đổi giá trị trong loại ô, bất kỳ giá trị ngẫu nhiên nào trong excel của ô sẽ báo lỗi.
Ví dụ: nhập một số ngẫu nhiên vào ô G6 và nhấn enter nó sẽ xuất hiện lỗi sau,
Khi nhập một giá trị ngẫu nhiên 56 vào ô G6, nó sẽ báo lỗi rằng chúng ta không thể thay đổi một phần của mảng.
Nếu chúng ta cần chỉnh sửa công thức mảng, hãy chuyển đến thanh chức năng và chỉnh sửa các giá trị hoặc phạm vi được cung cấp, hoặc nếu chúng ta muốn xóa công thức mảng, hãy xóa toàn bộ mảng, tức là phạm vi ô từ B6 đến G6.
Cách sử dụng công thức mảng trong Excel?
Hãy để chúng tôi tìm hiểu Công thức mảng bằng một vài ví dụ:
Công thức mảng có thể được sử dụng trong hai loại:
- Nếu chúng ta muốn trả về một giá trị duy nhất, hãy sử dụng các công thức này trong một ô duy nhất, như trong ví dụ 1.
- Nếu chúng ta muốn trả về nhiều hơn một giá trị, hãy sử dụng các công thức này trong Excel bằng cách chọn phạm vi ô như trong ví dụ 2.
- Nhấn CTRL + Shift + Enter để tạo công thức mảng.
Ví dụ 1
Trong dữ liệu bán hàng của nhà hàng, có các sản phẩm, giá của từng sản phẩm và số lượng sản phẩm đã bán.
Chủ sở hữu muốn tính toán tổng doanh số bán hàng được thực hiện bởi các sản phẩm đó.
Chủ sở hữu có thể tiếp tục và nhân số lượng mặt hàng đã bán của mỗi sản phẩm sau đó tổng chúng lại như trong phương trình dưới đây,
Và anh ấy sẽ nhận được tổng doanh thu,
Nhưng đây là một nhiệm vụ dài, và nếu dữ liệu lớn hơn, nó sẽ tẻ nhạt hơn nhiều. Excel cung cấp các công thức Mảng cho các tác vụ như vậy.
Chúng tôi sẽ tạo công thức Mảng đầu tiên của chúng tôi trong ô B7.
# 1 - Trong ô B7, nhập = sum, sau đó nhấn nút tab trên bàn phím. Nó mở ra công thức tổng.
# 2 - Chọn phạm vi ô B2 đến G2.
# 3 - Bây giờ đặt dấu hoa thị “*” sau đó để nhân lên.
# 4 - Chọn dải ô từ B3 đến G3.
# 5 - Thay vì nhấn phím enter, hãy nhấn CTRL + SHIFT + ENTER.
Excel đưa ra giá trị của tổng doanh thu bằng cách nhân số lượng sản phẩm với giá trong mỗi cột và cộng lại. Trong phần được đánh dấu, chúng ta có thể thấy rằng excel đã tạo một mảng cho phạm vi ô B2 đến G2 và B3 đến G3.
Ví dụ trên giải thích cách các công thức mảng trong excel trả về một giá trị duy nhất cho một mảng hoặc tập dữ liệu.
Ví dụ số 2
Tiếp tục với cùng dữ liệu bây giờ, điều gì sẽ xảy ra nếu chủ sở hữu muốn biết doanh số bán hàng cho từng sản phẩm riêng biệt, tức là doanh số bán sản phẩm 1 và sản phẩm 2, v.v.
Anh ta có thể đi một chặng đường dài và trong mỗi ô, thực hiện một chức năng sẽ tính toán giá trị bán hàng.
Phương trình trong ô B5, anh ta phải lặp lại nó cho ô c4, d4, v.v.
Một lần nữa nếu đó là dữ liệu lớn hơn, đó sẽ là một nhiệm vụ tẻ nhạt và mệt mỏi.
Trong ví dụ này, chúng ta sẽ tìm hiểu cách các công thức mảng trong excel trả về nhiều giá trị cho một tập hợp các mảng.
# 1 - Chọn các ô mà chúng tôi muốn tổng phụ của mình, tức là trên mỗi sản phẩm bán được cho mỗi sản phẩm. Trong trường hợp này, nó là dải ô từ B8 đến G8.
# 2 - Nhập một dấu bằng để ký “=” a
# 3 - Chọn phạm vi ô B2 đến G2.
# 4 - Nhập dấu hoa thị “*” sau đó.
# 5 - Bây giờ, chọn dải ô từ B3 đến G3.
# 6 - Đối với công thức mảng, không nhấn enter, nhấn CTRL + SHIFT + ENTER.
Từ ví dụ trên, sẽ giải thích cách một công thức mảng có thể trả về nhiều giá trị cho một mảng trong excel.
Các ô B6 đến G6 là một mảng.
Ví dụ # 3
Đối với cùng một chủ nhà hàng, anh ta có dữ liệu về doanh số bán hàng của nhà hàng trong năm tháng, tháng 1, tháng 2, tháng 3, tháng 4, tháng 5 và tháng 6. Anh ta muốn biết tốc độ tăng trưởng trung bình cho doanh số bán hàng.
Bây giờ, những gì chủ sở hữu thường có thể làm là trừ giá trị bán hàng của tháng 2 đến tháng 1 trong ô C2, sau đó tính giá trị trung bình.
Một lần nữa nếu đây là một dữ liệu lớn hơn, đó sẽ là một nhiệm vụ rất mệt mỏi. Hãy để chúng tôi làm điều này với mảng trong công thức excel.
# 1 - Trong ô D8, nhập = trung bình và sau đó nhấn nút TAB.
# 2 - Để tính tăng trưởng, chúng ta cần trừ các giá trị của một tháng so với tháng trước để chọn phạm vi ô từ B3 đến B7.
# 3 - Đặt dấu trừ (-) sau đó,
# 4 - Bây giờ chọn các ô từ B2 đến B6.
# 5 - Đối với mảng trong excel, không nhấn enter, nhấn CTRL + SHIFT + ENTER.
Công thức mảng trong excel dễ dàng tính toán mức tăng trưởng trung bình cho doanh thu mà không gặp bất kỳ rắc rối nào.
Chủ sở hữu bây giờ không cần phải tính toán tăng trưởng mỗi tháng và sau đó thực hiện chức năng trung bình sau đó.
Những điều cần ghi nhớ
- Chúng còn được gọi là Công thức CSE hoặc Control Shift Nhập Công thức Excel.
- Không tạo dấu ngoặc đơn cho một mảng; bản thân excel làm điều đó, nó sẽ trả về một lỗi hoặc giá trị không chính xác.
- Nhập dấu ngoặc đơn “(“ theo cách thủ công, excel sẽ coi nó như một văn bản.
- Không nhấn enter; thay vào đó, hãy nhấn CTRL + SHIFT + Enter để sử dụng công thức mảng.
- Chúng ta không thể thay đổi ô của một mảng, vì vậy, để sửa đổi công thức mảng, hãy sửa đổi công thức từ thanh chức năng hoặc xóa công thức và thiết kế lại nó theo định dạng mong muốn.
