Công thức mảng là một trong những tính năng khó hiểu nhất trong Excel, nhưng dường như lại là một trong những điều thú vị và hấp dẫn nhất.
6. Thực hiện tính toán trên các con số trong dải khác nhau trong công thức mảng
Dưới đây là một tình huống điển hình cho nhiều nhà cung cấp – chính là đơn giá thay đổi tùy thuộc vào số lượng mua, và vấn đề đặt ra cho bạn là viết một công thức tính tổng số tiền cho bất kỳ giá đầu vào nào ở một 1 ô cụ thể.
Vấn đề này có thể dễ dàng giải quyết bằng cách sử dụng các công thức IF lồng nhau như sau:
= B8 * IF (B8> = 101, B6, IF (B8> = 50, B5, IF (B8> = 20, B4, IF (B8> = 11, B3, IF (B8> = 1, B2, )))))
Tuy nhiên, cách tiếp cận này có một hạn chế đáng kể. Bởi vì công thức tham chiếu đến mỗi giá trong các ô B2:B6 là độc lập, nên bạn sẽ phải cập nhật công thức ngay khi người dùng thay đổi bất kỳ dải ô nào hoặc thêm vào một dải số mới.
Để làm cho công thức linh hoạt hơn, nên để thao tác trên mảng hơn là trên các ô riêng lẻ. Trong trường hợp này, bất kể có bao nhiêu giá trị được thay đổi, thêm hoặc xóa, bạn sẽ chỉ phải cập nhật một tham chiếu dải ô trong công thức.
= SUM (B8 * (B2: B6) * (– (B8> = VALUE (LEFT (A2: A6, FIND (“”, A2: A6))))) * (– (B8 <= VALUE (RIGHT (A2: A6, LEN (A2: A6) – FIND (“to”, A2: A6) -LEN (“to”))))))
Thực hiện tính toán trên các con số trong dải khác nhau trong công thức mảng
Việc chia nhỏ công thức nàyđể phân tích chi tiết có thể sẽ yêu cầu một bài báo riêng, do đó tôi sẽ chỉ đưa ra một cái nhìn khái quát về mặt logic của công thức. Nếu bạn chọn từng phần của công thức trong thanh công thức và nhấn F9, bạn sẽ thấy nó đánh giá 3 mảng sau đây (với số lượng là 100 (ô B8) như trong ảnh chụp màn hình ở trên):
= SUM (B8 * {20; 18; 16; 13; 12} * {1; 1; 1; 1; 0} * {0; 0; 0; 1; 1}
Năm phần tử đầu tiên của mảng chính là giá cả trong các ô B2: B6. Và 2 mảng cuối cùng của 0 và 1 xác định chính xác giá nào sẽ được sử dụng để tính toán. Vì vậy, câu hỏi chính là – lấy được 2 mảng này như thế nào và ý nghĩa của chúng?
Công thức mảng bao gồm 2 hàm VALUE, như sau: (B8> = VALUE ()) * (B8 <= VALUE ())
Hàm thứ nhất kiểm tra xem giá trị của B8 có lớn hơn hay bằng giới hạn dưới của mỗi dải “số lượng đơn vị” và hàm thứ 2 để kiểm tra nếu B8 nhỏ hơn hoặc bằng với giới hạn trên của mỗi dãy (kết hợp các hàm LEFT, RIGHT, FIND và LEN để lấy ra các giá trị giới hạn trên và dưới). Thì sau đó, bạn sẽ nhận được 0 nếu điều kiện không được thỏa, và 1 nếu thỏa điều kiện.
Cuối cùng, hàm SUM sẽ nhân số lượng trong B8 bởi với giá tương ứng trong mảng đơn giá (B2: B6) và mỗi phần tử của mảng 0 và 1. Vì nhân với 0 luôn cho 0, nên luôn chỉ có một giá được sử dụng cho mỗi sản phâm.
Trong ví dụ này, số lượng được nhân với 13 đô la nằm trong khoảng giá trị “50 đến 100”. Đây là mục thứ 4 của mảng giá (ô B5 trong phạm vi B2: B6), và nó là thành phần duy nhất có mặt trong hai mảng cuối cùng.
Để công thức làm việc chính xác, hãy chắc chắn kiểm tra lại hai điều sau:
- Số lượng trong các ô A2: A6 nên tạo thành một khoảng giá trị liên tiếp để không có giá trị nào bị bỏ lại.
- Tất cả các khối lượng trong các ô A2: A6 phải được nhập vào trong mẫu “X đến Y” này vì nó được mã hoá trong công thức. Nếu số lượng của bạn được nhập bằng một cách khác, hãy nói “1 – 10”, sau đó thay “đến” bằng “-” trong công thức.
Nếu bạn muốn hiển thị một thông báo “Nằm ngoài dải” khi số lượng đầu vào trong B8 nằm ngoài dải số tiền, thì hãy tham khảo câu lệnh IF sau đây:
= IF (AND (B8> = VALUE (LEFT (A2, FIND (“”, A2))), B8 <= VALUE (RIGHT (A6, LEN (A6) – FIND(“to”, A6) -LEN ( “to”)))), SUM (…))
Công thức hàm If này dù nhìn có vẻ phức tạp nhưng thực sự lại rất đơn giản – nó kiểm tra rằng nếu giá trị trong B8 lớn hơn hoặc bằng với ràng buộc dưới trong A2 và ít hơn hoặc bằng với các ràng buộc trên trong ô A6. Nói cách khác, nó kiểm tra điều kiện này: AND (B8> = 1, B8 <= 200).
Công thức đầy đủ mà bạn cần như sau:
= IF (AND (B8> = VALUE (LEFT (A2, FIND (“”, A2))), B8 <= VALUE (RIGHT (A6, LEN (A6) -FIND (“to”, A6) -LEN (” “), SUM (B8 * (B2: B6) * (- (B8> = VALUEỊ (LEFT (A2: A6, FIND (” “, A2: A6))))) * (- ( B8 <= VALUE (RIGHT (A2: A6, LEN (A2: A6) -FIND (“to”, A2: A6) -LEN (“to”)))))), “Out of range”)
7. Các hàm do người dùng định nghĩa trong công thức mảng Excel
Ví dụ này nhằm dành cho những người biết và hiểu về các macro VBA trong Excel và các hàm do người dùng tự định nghĩa.
Bạn có thể sử dụng hàm đã được người dùng (bạn) định nghĩa từ trước trong các công thức mảng trong Excel, cho phép một công thức xác định hỗ trợ các phép tính trong các mảng.
Ví dụ, một trong những chuyên gia Excel của chúng tôi đã viết một hàm gọi là GetCellColor có thể nhận biết được một màu của tất cả các ô trong một dải, y như tên của nó. Bạn có thể lấy mã của hàm công thức trên từ bài viết này – Làm thế nào để đếm và tính tổng các ô dựa trên màu sắc trong Excel.
Và bây giờ, chúng ta hãy xem làm thế nào mà bạn có thể sử dụng hàm GetCellColor trong một công thức mảng. Giả sử bạn có một bảng với một cột mã màu và bạn muốn tổng hợp các giá trị đáp ứng một số điều kiện, bao gồm màu của ô. Ví dụ: hãy tính tổng doanh số các cột màu “xanh” và “vàng” của Neal:
Các hàm do người dùng định nghĩa trong công thức mảng Excel
Như đã trình bày trong hình trên, chúng ta sử dụng công thức mảng Excel như sau:
=SUM(–($ A$ 2:$ A$ 10=$ F$ 1) * ($ C$ 2:$ C$ 10) * (–(GetCellColor($ C$ 2:$ C$ 10)=GetCellColor($ E$ 2))))
Trong đó, ô F1 là tên người bán hàng và E2 là mẫu màu.
Công thức tính các ô trong khoảng C2: C10 nếu thỏa mãn hai điều kiện sau:
- $ A$ 2:$ A$ 10 = $ F$ 1 – kiểm tra nếu một ô trong cột A khớp với tên người bán trong F1, tức là Neal như trong ví dụ này.
- GetCellColor ($ C$ 2:$ C$ 10) = GetCellColor ($ E $ 2) – sử dụng hàm được người dùng định nghĩa tùy chỉnh để lấy màu của các ô C2 đến C10 và kiểm tra xem nó có phù hợp với mẫu màu trong E2 hay không, trong trường hợp này.
Hãy chú ý rằng chúng ta sử dụng toán tử đơn vị với cả hai biểu thức trên để chuyển đổi các giá trị Boolean TRUE và FALSE trở về 1 và 0 để hàm SUM có thể hoạt động trên dữ liệu này. Nếu cả hai điều kiện được đáp ứng, nghĩa là hai giá trị được trả lại, SUM sẽ cộng thêm số tiền bán hàng từ một ô tương ứng trong cột C.
Chúc các bạn thành công nhé.
Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ
Bài viết liên quan
