Bạn có biết thủ thuật tiện lợi là tạo tham chiếu đến tiêu đề cột trong ô không trống cuối cùng của hàng không? Tốt nhất: Bạn không cần bất kỳ dòng hoặc cột phụ trợ nào. Nó là dễ dàng:
Kết hợp bốn hàm IFERROR (), INDEX (), MAX () và IF ()
Giám đốc quản lý bán hàng sẽ gửi cho bạn danh sách các hợp đồng đã ký mỗi tháng đối với các sản phẩm sắp được loại bỏ ①. Bạn nên sử dụng công thức trong cột N để chỉ định tháng bán hàng cuối cùng cho từng sản phẩm - không có bất kỳ dòng hoặc cột bổ trợ nào. Nếu không có thêm hợp đồng nào được ký kết, hãy nhập một ô trống trong cột N.
Ví dụ này thoạt nhìn có vẻ tầm thường, hóa ra lại là một thứ khó bẻ gãy nếu không sử dụng các đường hoặc cột phụ trợ. như Excel cho thực hành-Readers crack the nut! Chúng tôi có công thức mảng sau trong ô để giải quyết vấn đề N2 đã tạo ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Hãy xem cách nó hoạt động từng bước
Bắt đầu với điều kiện IF () được tích hợp trong công thức mảng: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Điều kiện IF () tạo ra một dòng phụ hư cấu trong công thức mảng và kiểm tra các ô B2 cho đến khi M2cho dù chúng có trống hay không. Nếu một ô trống, giá trị -1 được trả về thông qua hàm COLUMN (), số cột tương ứng trừ đi giá trị 1.
Phép trừ 1 là bắt buộc trong công thức vì cột đầu tiên trong bảng không chứa tên tháng mà là tên sản phẩm. Dưới đây, bạn sẽ học cách sử dụng hàm INDEX () để hiển thị tên tháng tương ứng, - nếu bạn không trừ đi 1 - vì cột bổ sung được sử dụng MỘT. sẽ không chính xác bởi một cột.
Nếu tất cả các ô trong phạm vi B2: M2 trống, giá trị -1 (không bán sản phẩm) tạo ra lỗi mà chúng tôi sử dụng để đại diện cho một ô trống. Dòng phụ hoạt động có thể được nhìn thấy trong Hình ③ ở dòng 3.
Trong bước tiếp theo, bạn đọc ra giá trị lớn nhất với hàm MAX (), trong đó điều kiện IF () được lồng vào nhau. Đây là giá trị 12 ở dòng 3 (cột 13 trừ 1; xem dòng phụ trong Hình ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Bạn chuyển giá trị MAX này vào hàm INDEX (). Sau đó, tháng lịch tương ứng được đọc ra ở dòng 1. Vùng dữ liệu của hàm INDEX () là vùng $ B $ 1: $ M $ 1. Giá trị MAX được truyền - trong ví dụ 12 - có nghĩa là giá trị thứ mười hai trong danh sách, tức là Tháng mười hai trong tháng 12:
{= IFERROR(CHỈ SỐ ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Nếu tất cả các ô trong phạm vi tháng của bảng đều trống, thì giá trị lớn nhất là -1 (xem điều kiện IF () ở đầu). Nếu giá trị -1 được chuyển cho hàm INDEX (), điều này chắc chắn dẫn đến giá trị lỗi, vì vùng danh sách của hàm INDEX () chỉ chứa mười hai mục nhập và do đó không thể tìm thấy mục nhập -1. Bạn bắt giá trị lỗi này bằng hàm IFERROR () và thay vào đó trả về một chuỗi trống. Trong ví dụ, đây là trường hợp của sản phẩm C ở dòng 4, vì không có hợp đồng nào nữa có thể được ký kết cho sản phẩm này:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Vì đây là công thức mảng nên hãy hoàn tất việc nhập công thức bằng tổ hợp phím Ctrl + Shift + Enter.