Hàm OFFSET của Excel sẽ làm chậm quá trình tính toán của sổ làm việc của bạn. Có một giải pháp thay thế tốt hơn: một cú pháp bất thường của INDEX.
Đây là một mẹo thích hợp. Có một chức năng linh hoạt đáng kinh ngạc được gọi là OFFSET. Nó linh hoạt vì nó có thể trỏ đến một phạm vi có kích thước khác nhau được tính toán nhanh chóng. Trong hình ảnh bên dưới, nếu ai đó thay đổi trình đơn thả xuống # Qtrs trong H1 từ 3 thành 4, đối số thứ tư của OFFSET sẽ đảm bảo rằng phạm vi mở rộng để bao gồm bốn cột.
Các chuyên gia về bảng tính ghét OFFSET vì nó là một hàm dễ bay hơi. Nếu bạn đi đến một ô hoàn toàn không liên quan và nhập một số, tất cả các hàm OFFSET sẽ tính toán. Ngay cả khi ô đó không liên quan gì đến H1 hoặc B2. Hầu hết thời gian, Excel rất cẩn thận để chỉ lãng phí thời gian tính toán các ô cần tính toán. Nhưng khi bạn giới thiệu OFFSET, tất cả các ô OFFSET, cộng với mọi thứ từ tuyến dưới từ OFFSET, bắt đầu tính toán sau mỗi thay đổi trong trang tính.
Tôi đang đánh giá vòng chung kết ModelOff 2013 tại Thành phố New York khi một vài người bạn của tôi từ Úc chỉ ra một cách giải quyết kỳ lạ. Trong công thức bên dưới, có dấu hai chấm trước hàm INDEX. Thông thường, hàm INDEX được hiển thị bên dưới sẽ trả về 1403 từ ô D2. Nhưng khi bạn đặt dấu hai chấm ở hai bên của hàm INDEX, nó sẽ bắt đầu trả về địa chỉ ô D2 thay vì nội dung của D2. Điều này là hoang dã rằng nó hoạt động.
Vì sao vấn đề này? INDEX không biến động. Bạn nhận được tất cả tính tốt linh hoạt của OFFSET mà không cần phải tính toán lại nhiều lần.
Lần đầu tiên tôi học mẹo này từ Dan Mayoh tại Fintega. Cảm ơn Access Analytic đã đề xuất tính năng này.
Xem video
Bản ghi video
Học Excel từ podcast, tập 2048 -: INDEX sẽ thay thế một OFFSET dễ bay hơi!
Này, tôi đang podcasting tất cả các mẹo của mình từ cuốn sách này, hãy nhấp vào chữ "i" ở góc trên bên phải để truy cập danh sách phát!
Được rồi, OFFSET là một chức năng tuyệt vời! OFFSET cho phép chúng ta chỉ định một ô góc trên cùng bên trái, sau đó sử dụng các biến để xác định từ đó có bao nhiêu hàng xuống, bao nhiêu hàng trên và sau đó xác định một hình dạng. Vì vậy, ở đây, nếu tôi muốn cộng hoặc tính trung bình, giả sử là 3/4, công thức này ngay sau đây sẽ xem xét công thức. OFFSET cho biết chúng ta đang bắt đầu từ B2, bắt đầu từ Q1, chúng ta sẽ đi xuống 0, trên 0, hình dạng sẽ cao 1 hàng và nó sẽ là H1, nói cách khác là rộng 3 ô, được rồi. Vì vậy, trong trường hợp này, tất cả những gì chúng tôi thực sự đang làm là thay đổi số lượng ô mà chúng tôi đang cộng dồn, chúng tôi luôn bắt đầu lại ở B2, B3 hoặc B4 khi tôi sao chép xuống, và sau đó nó quyết định chiều rộng bao nhiêu ô. Được rồi, OFFSET là một điều tuyệt vời, nó thực hiện tất cả các loại chức năng tuyệt vời, nhưng rắc rối ở đây là nó rất dễ bay hơi!Điều đó có nghĩa là ngay cả khi thứ gì đó không có trong chuỗi tính toán, thì Excel cũng sẽ dành thời gian để tính toán lại, điều này sẽ làm mọi thứ chậm lại, được thôi.
Phiên bản tuyệt vời này của INDEX, đúng vậy, thông thường nếu tôi yêu cầu INDEX của 4 ô này, 3, nó sẽ trả về số 1403. Tuy nhiên, khi tôi đặt dấu hai chấm trước hoặc sau INDEX, điều gì đó rất khác sẽ xảy ra, chúng ta sẽ xem xét công thức này ở đây. Vì vậy, chỉ mục của 4 ô, tôi muốn ô nào, tôi muốn ô thứ 3, nhưng bạn thấy có một: ngay tại đó. Vì vậy, chúng ta sẽ luôn đi từ B2: E2, và tôi sẽ chỉ cho bạn nếu chúng ta đi đến Công thức, Đánh giá Công thức, được rồi, vì vậy ngay tại đây, nó sẽ tính toán số 3. Và đây, INDEX với: tiếp theo với nó, thay vì cho chúng tôi biết 1403, đó là cách INDEX thông thường sẽ tính toán, nó sẽ trả về $ D2 và sau đó AVERAGE sẽ thực hiện mức trung bình của 3. Hoàn toàn tuyệt vời, cách thức hoạt động và lợi ích bổ sung, nó không dễ bay hơi, được rồi,và điều này thậm chí có thể được sử dụng để thay thế một OFFSET cực kỳ phức tạp.
Vì vậy, ở đây với OFFSET này, chúng tôi dựa trên các giá trị này. Được rồi, nếu tôi chọn Q2 và Central, các công thức này đang sử dụng MATCH và COUNTIF để tìm ra bao nhiêu hàng xuống, bao nhiêu cột trên, cao bao nhiêu, rộng bao nhiêu. Và sau đó OFFSET ở đây đang sử dụng tất cả các giá trị đó để tìm ra giá trị trung bình. Chúng tôi sẽ chỉ thực hiện một bài kiểm tra để đảm bảo rằng nó đang hoạt động, vì vậy = MEDIAN của dải màu xanh lam ở đó, tốt hơn là 71, được rồi, và chúng tôi sẽ chọn một cái gì đó khác ở đây, Q3 và West, vì vậy. Nhấn F2, tôi sẽ kéo nó qua và thay đổi kích thước để viết lại công thức đó, nhấn Enter, và nó hoạt động với OFFSET.
Ở đây, bằng cách sử dụng INDEX, tôi thực sự có dấu hai chấm ở giữa với INDEX ở bên trái và INDEX ở bên phải, hãy xem điều này được tính toán trong Công thức đánh giá. Vì vậy, nó bắt đầu, sẽ Đánh giá, Đánh giá và ngay tại đây INDEX sắp biến nó thành một địa chỉ ô. Vì vậy, H16 là 1, Tây, Q3, và hơn ở phía bên phải sẽ đánh giá, cặp đôi nhiều hơn, và sau đó bên phải nó chuyển thành I20. Vì vậy, mát mẻ, mát mẻ không bay hơi để thay thế một OFFSET bằng cách sử dụng chức năng INDEX. Được rồi, mẹo này và nhiều mẹo khác trong sách này, hãy nhấp vào chữ “i” ở góc trên bên phải để mua sách.
Được rồi tóm tắt lại: OFFSET, chức năng tuyệt vời, linh hoạt, một khi bạn thành thạo, bạn có thể làm mọi thứ. Trỏ đến một ô trên cùng bên trái có thể thay đổi, trỏ đến một phạm vi có hình dạng thay đổi, nhưng nó dễ bay hơi và do đó chúng tính toán ở mọi phép tính. Excel thường thực hiện một phép tính thông minh hoặc tính toán lại các ô cần tính toán, nhưng với OFFSET, nó sẽ luôn được tính toán. Thay vì OFFSET, bạn có thể sử dụng INDEX: hoặc: INDEX hoặc thậm chí INDEX: INDEX, bất cứ lúc nào INDEX có dấu hai chấm bên cạnh, nó sẽ trả về địa chỉ ô thay vì giá trị của ô đó. Và lợi ích là INDEX không biến động!
Được rồi, tôi muốn cảm ơn bạn đã ghé qua, chúng ta sẽ gặp lại bạn vào lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2048.xlsm
