Tóm lược
Để tạo số lượng đang chạy trong Bảng Excel, bạn có thể sử dụng hàm INDEX với tham chiếu có cấu trúc để tạo phạm vi mở rộng. Trong ví dụ được hiển thị, công thức trong F5 là:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Khi được sao chép xuống cột, công thức này sẽ trả về số lượng đang chạy cho mỗi màu trong cột Màu.
Trong một số phiên bản Excel, đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Về cơ bản, công thức này đã sử dụng INDEX để tạo tham chiếu mở rộng như sau:
INDEX((Color),1):(@Color) // expanding range
Ở phía bên trái của dấu hai chấm (:), hàm INDEX trả về một tham chiếu đến ô đầu tiên trong cột cột.
INDEX((Color),1) // first cell in color
Điều này hoạt động bởi vì, hàm INDEX trả về một tham chiếu đến ô đầu tiên, không phải giá trị thực. Ở phía bên phải của dấu hai chấm, chúng tôi nhận được một tham chiếu đến hàng hiện tại của cột màu như sau:
(@Color) // current row of Color
Đây là cú pháp tham chiếu có cấu trúc chuẩn cho "hàng này". Được kết hợp với dấu hai chấm, hai tham chiếu này tạo ra một phạm vi mở rộng khi công thức được sao chép xuống bảng. Vì vậy, chúng tôi hoán đổi các tham chiếu này thành hàm SUM, chúng tôi có:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Mỗi biểu thức ở trên tạo ra một mảng các giá trị TRUE / FALSE và dấu âm kép (-) được sử dụng để chuyển đổi các giá trị này thành 1s và 0s. Vì vậy, ở hàng cuối cùng, chúng tôi kết thúc với:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Phần còn lại của công thức chỉ cần nối màu từ hàng hiện tại với số được trả về bởi SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Mở rộng phạm vi đơn giản?
Tại sao không sử dụng một phạm vi mở rộng đơn giản như thế này?
SUM(--($B$5:B5=(@Color)))
Vì một số lý do, loại tham chiếu hỗn hợp này bị hỏng trong Bảng Excel khi các hàng được thêm vào. Sử dụng INDEX với tham chiếu có cấu trúc sẽ giải quyết được vấn đề.
