Tải xuống sổ làm việc mẫu
Chúng ta đã thảo luận trong các bài viết khác về cách có các hàm như OFFSET và INDIRECT dễ thay đổi. Nếu bạn bắt đầu sử dụng nhiều ô trong số này trong bảng tính hoặc có nhiều ô phụ thuộc vào chức năng dễ bay hơi, bạn có thể khiến máy tính của mình mất một khoảng thời gian đáng kể để thực hiện các phép tính lại mỗi khi bạn cố gắng thay đổi một ô. Thay vì trở nên thất vọng về cách máy tính của bạn không đủ nhanh, bài viết này sẽ khám phá các cách thay thế để giải quyết các tình huống phổ biến mà mọi người sử dụng OFFSET và INDIRECT.
Thay thế OFFSET để tạo danh sách động
Sau khi tìm hiểu về hàm OFFSET, có một quan niệm sai lầm phổ biến rằng đó là cách duy nhất để trả về kết quả có kích thước động bằng cách sử dụng một vài đối số cuối cùng. Hãy xem danh sách trong cột A, nơi người dùng của chúng tôi có thể quyết định sau đó thêm các mục bổ sung.
Để tạo danh sách thả xuống trong ô C2, bạn có thể xác định Dải ô được đặt tên bằng công thức dễ thay đổi như
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Với thiết lập hiện tại, điều này chắc chắn sẽ trả về một tham chiếu đến phạm vi A2: A5. Tuy nhiên, có một cách khác là sử dụng INDEX không biến động. Để làm điều này, hãy nghĩ về việc chúng tôi viết một tham chiếu đến phạm vi đi từ A2 đến A5. Khi bạn viết “A2: A5”, đừng nghĩ đây là một phần dữ liệu đơn lẻ, mà hãy coi đây là một “Điểm khởi động” và “Điểm kết thúc” được phân tách bằng dấu hai chấm (ví dụ: Điểm khởi động: Điểm kết thúc). Trong một công thức, cả Điểm khởi động và Điểm kết thúc đều có thể là kết quả của các hàm khác.
Dưới đây là công thức chúng tôi sẽ sử dụng để tạo phạm vi động bằng Hàm INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Lưu ý rằng chúng tôi đã tuyên bố rằng Điểm khởi đầu cho phạm vi này sẽ luôn là A2. Ở phía bên kia của dấu hai chấm, chúng tôi đang sử dụng INDEX để xác định vị trí của EndingPoint. COUNTA sẽ xác định rằng có 5 ô có dữ liệu trong cột A và vì vậy INDEX của chúng tôi sẽ tạo một tham chiếu đến A5. Do đó, công thức được đánh giá như vậy:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Sử dụng kỹ thuật này, bạn có thể xây dựng động một tham chiếu đến bất kỳ danh sách nào hoặc thậm chí là một bảng hai chiều bằng cách sử dụng hàm INDEX. Trong một bảng tính có rất nhiều hàm OFFSET, việc thay thế các hàm OFFSET bằng INDEX sẽ cho phép máy tính của bạn bắt đầu chạy nhanh hơn nhiều.
Thay thế INDIRECT cho tên trang tính
Hàm INDIRECT thường được gọi khi sổ làm việc được thiết kế với dữ liệu nằm rải rác trên nhiều trang tính. Nếu bạn không thể lấy tất cả dữ liệu trên một trang tính, nhưng không muốn sử dụng một hàm dễ thay đổi, bạn có thể sử dụng CHOOSE.
Hãy xem xét bố cục sau, nơi chúng tôi có dữ liệu Bán hàng trên 3 trang tính khác nhau. Trên trang Tóm tắt của chúng tôi, chúng tôi đã chọn quý mà chúng tôi muốn xem dữ liệu từ đó.
Công thức của chúng tôi trong B3 là:
= CHỌN (MATCH (B2, D2: D4, 0), Fall! A2, Winter! A2, Spring! A2)
Trong công thức này, hàm MATCH sẽ xác định vùng nào chúng ta muốn trả về. Sau đó, điều này cho hàm CHOOSE biết phạm vi nào sau đây sẽ trả về là kết quả.
Bạn cũng có thể sử dụng chức năng CHOOSE để trả về một phạm vi lớn hơn. Trong ví dụ này, chúng tôi có một bảng dữ liệu bán hàng trên mỗi trang tính trong số ba trang tính của chúng tôi.
Thay vì viết hàm INDIRECT để xây dựng tên trang tính, bạn có thể để CHOOSE xác định bảng cần thực hiện tìm kiếm. Trong ví dụ của tôi, tôi đã đặt tên cho ba bảng là tbFall, tbWinter và tbSpring. Công thức trong B4 là:
= VLOOKUP (B3, CHỌN (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Trong công thức này, MATCH sẽ xác định rằng chúng ta muốn 2NS mục từ danh sách của chúng tôi. CHOOSE sau đó sẽ lấy 2 đó và trả về tham chiếu đến tbWinter. Cuối cùng, VLOOKUP của chúng tôi sẽ có thể hoàn thành việc tìm kiếm trong bảng đã cho và sẽ thấy rằng tổng doanh số bán hàng cho Banana trong mùa đông là $ 6000.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Kỹ thuật này bị hạn chế bởi thực tế là bạn phải điền vào hàm CHOOSE với tất cả các khu vực bạn có thể muốn lấy một giá trị từ đó, nhưng nó mang lại cho bạn lợi ích là tránh được một công thức dễ bay hơi. Tùy thuộc vào số lượng phép tính bạn cần phải hoàn thành, khả năng này có thể được chứng minh là khá có giá trị.
