Dải ô được đặt tên động trên Google Trang tính

Tôi đã thấy câu hỏi này và câu trả lời được chấp nhận của nó, nhưng tôi không thể hiểu được cách/nếu nó có thể được sử dụng cho tình huống của tôi

Tôi đang lấy dữ liệu động từ một số API nên dữ liệu có thể khác bất kỳ lúc nào, kể cả vị trí thực của dữ liệu trên trang tính của tôi. Tôi muốn xác định các ô khác nhau trong dữ liệu được trả về, dưới dạng các phạm vi được đặt tên. Nhưng nếu không biết chắc chắn chính xác từng phần dữ liệu sẽ nằm trong ô nào, thì các phạm vi được đặt tên không hoạt động tốt cho việc này. Lý tưởng nhất là tôi có thể nói vlookup trong một dải ô được đặt tên, nhưng bạn hoàn toàn không thể sử dụng công thức trong các dải ô được đặt tên, trong Google Trang tính

Tôi không cập nhật công thức thường xuyên, nhưng khi tôi thực hiện phép tính của mình thì phạm vi được đặt tên không còn chính xác nữa. Tôi sẽ phải sử dụng vlookup trong hàng chục công thức trên nhiều trang tính và nó chỉ làm cho các công thức trở nên to và vô duyên, khi một phạm vi được đặt tên giữ cho chúng cực kỳ gọn gàng và dễ hiểu. GAS tốt với tôi miễn là tôi biết phải làm gì với nó

Có cách nào để đạt được kết quả hiệu quả tương tự khi sử dụng phạm vi được đặt tên không?

CHỈNH SỬA. Tôi hy vọng tôi có thể cung cấp thêm một chút ví dụ ở đây. Một bộ dữ liệu giả định có thể là

ABC   123456
DEF   987654
GHI   246802
ABC   537910

Nhưng lần sau khi tôi lấy dữ liệu, nó có thể trông như thế này

XYZ   135791
ABC   123456
LMN   086420
DEF   987654
GHI   246802
ABC   537910
LMN   776655

Vì vậy, bây giờ 3 giá trị ban đầu của tôi vẫn còn đó, nhưng chúng đã di chuyển trong hàng của chúng. Một vlookup sẽ thực hiện điều này rất dễ dàng vì nó sẽ luôn thích ứng với bất kỳ vị trí nào của giá trị đích và trả về giá trị liền kề. Nhưng tôi cần mỗi một trong những giá trị liền kề đó là phạm vi được đặt tên riêng, điều đó sẽ luôn đúng bất kể dữ liệu di chuyển đến đâu

Ngoài ra, thường có bội số của giá trị khóa trong dữ liệu và một công thức đã cho ở nơi khác trong trang tính cần lấy giá trị đầu tiên, hoặc thứ hai hoặc thứ ba, v.v.

Tại đây, bạn có thể tìm hiểu một cách độc đáo để tạo DẢI ĐỘNG trong Google Trang tính. Có thể tạo Dynamic Ranges trong Google Sheets mà không cần Helper Cell

Bằng cách nói Ô trợ giúp, tôi muốn nói rằng bản thân công thức có thể xử lý các phạm vi động mà không cần tham chiếu đến bất kỳ ô hoặc cột bổ sung nào

Tương tự. Cột Trình trợ giúp ảo trong Bảng tính Google Tài liệu

Đây là sự kết hợp của các hàm làm cho phạm vi của bạn trong công thức luôn động. Bạn chỉ cần đặt và để lại phạm vi động trong SUM, SUMIF, SUMIFS, COUNTIF, MAX, MIN hoặc bất kỳ công thức nào chấp nhận phạm vi cột

Sau này, bạn có thể thêm hoặc xóa bất kỳ số hàng nào trong dữ liệu của mình, số hàng đó sẽ tự động được điều chỉnh trong phạm vi. Phạm vi động trong Google Trang tính không có ô trợ giúp là một thực tế

Phải đọc. Hướng dẫn chức năng của Google Sheets (tìm hiểu tất cả các chức năng hữu ích nhất của Google Sheets)

Trước đây tôi đã giải thích cách tạo dải động bằng hàm OFFSET trong Google Trang tính. Sự khác biệt giữa công thức đó với công thức mới này là gì?

Công thức bù động so với công thức mới (So sánh nhanh)

Cả hai đều có điểm cộng và điểm trừ. Đây là sự so sánh

Ảnh chụp màn hình #1

Trước khi đi đến hướng dẫn phạm vi động mới và mới mẻ này, bạn có thể vui lòng xem các hướng dẫn sau của tôi để hiểu các chức năng chính được sử dụng trong công thức mới này. Họ là GIÁN TIẾP và ĐỊA CHỈ. Điều này sẽ giúp bạn đọc công thức của tôi tốt hơn

Tạo Dynamic Ranges trong Google Sheets mà không cần Helper Cell (The Ultimate Way)

Công thức Phạm vi động bên dưới sẽ chỉ xem xét các giá trị hiện có trong một phạm vi cột và tự động sửa đổi phạm vi khi bạn thêm các giá trị mới vào cuối các giá trị hiện có. Nói cách khác, phạm vi động kết thúc khi một ô trống xuất hiện trong cột

Lợi ích của việc sử dụng phạm vi động là bạn luôn có thêm chỗ trong Bảng tính bên dưới phạm vi dữ liệu hiện tại của mình

Nếu bạn sử dụng một phạm vi vô hạn như G2. G hoặc G. G trong công thức, bạn không thể sử dụng các ô trống bên dưới cho một số mục đích khác

Bạn chỉ cần bỏ qua một hàng và giữ cho hàng đó luôn trống. Bạn có thể sử dụng các hàng còn lại cho các mục đích khác

Bây giờ là lúc để đi đến công thức

Tương tự. Cách sử dụng Dải động trong Công thức SUMIF trong Google Trang tính (Sử dụng hàm Offset)

Đây là Công thức mới và Dữ liệu mẫu của chúng tôi. Tôi đang sử dụng phạm vi động trong hàm SUM tại đây

Công thức có vẻ phức tạp. Nhưng nó không phải như vậy. Bạn có thể sử dụng nó ra khỏi hộp

Ảnh chụp màn hình #2

Công thức phạm vi động trong Google Sheets mà không cần giới thiệu ô trợ giúp

Trước khi giải thích cách tạo công thức dải động ở trên, hãy để tôi cho bạn biết cách bạn có thể sử dụng nó

Công thức trên Tính tổng cột G. Bạn có thể nhập bất kỳ giá trị nào vào các ô G10, G11, G12, v.v. Nó sẽ được thêm vào phạm vi SUM

Nếu bất kỳ ô trống nào xuất hiện trong cột này, công thức sẽ trả về câu trả lời sai. Vì công thức chỉ tính tổng đến khoảng đó thôi

Nếu bạn chèn thêm một hàng vào giữa dãy nhớ ít nhất phải để giá trị 0 ở cột tương ứng đang sử dụng (cột G ở đây). Hãy ghi nhớ điều này nếu bạn sử dụng công thức của tôi

Ảnh chụp màn hình #3

Giải thích công thức

Bạn có thể sao chép công thức phạm vi động dưới đây của tôi

Công thức

=sum(G2:indirect(address(ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1))),7,4)))

Ở đây, G2 là địa chỉ ô bắt đầu của dãy. Nó có thể là bất kỳ địa chỉ ô nào trong bất kỳ cột nào dựa trên phạm vi dữ liệu của bạn

Dưới đây là chi tiết về cách tôi đã mã hóa công thức tuyệt vời trên

=ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1)

Nó đếm phạm vi G2. G cho đến khi bất kỳ ô trống nào xuất hiện. Tôi đã trình bày chi tiết công thức này trong một hướng dẫn riêng. Xem liên kết ngay bên dưới phần giải thích công thức này

Công thức này chỉ trả về giá trị 9. Vui lòng tham khảo ảnh chụp màn hình số 2 để biết dữ liệu mẫu và đặc biệt chú ý đến cột G. Nếu bạn sử dụng công thức Đếm thông thường, kết quả sẽ chỉ là 8. Công thức trên trả về số đếm + 1

Khi công thức này được bao bọc bởi một công thức Địa chỉ, nó sẽ như thế này;

=address(9,7,4)

Ở đây bạn phải thay 9 bằng công thức đếm trên

=address(ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1))),7,4)

Công thức địa chỉ này trả về địa chỉ ô G9. Đây 7 là số cột của G và 4 là 'chế_độ_tương_đối' trong hàm Địa chỉ

Công thức cuối cùng là như thế nào;

=sum(G2:indirect(G9)

Trong G9 này là công thức Địa chỉ ở trên

Cách đếm cho đến khi một hàng trống trong Google Sheets

Làm cách nào tôi có thể sử dụng Dải động này trong Trang tính của riêng mình?

Tôi biết bạn có thể muốn sử dụng công thức này trong một cột khác ngoài G. Nếu vậy, đây là điều duy nhất bạn có thể muốn thay đổi

Trong công thức, chỉ cần thay đổi;

1. G2 – Thay đổi địa chỉ ô này thành địa chỉ ô bắt đầu của bạn trong phạm vi
2. G– Không cần phải nói
3. 7 – Đây là số cột của G. Số cột cho A =1, B=2… và G=7. Thay đổi nó cho phù hợp

Cuối cùng, bạn có thể sử dụng các dải động ở trên trong Google Trang tính trong các công thức khác ngoài SUM

Hãy nhớ rằng chúng tôi chưa sử dụng bất kỳ ô hoặc cột trợ giúp nào để tạo dải động này. Đó là vẻ đẹp của công thức này

Chỉ sử dụng nó khi hiểu được nhược điểm hạn chế của nó trong việc xử lý các ô trống. Đó là tất cả cho bây giờ. Vui thích

Phạm vi được đặt tên có thể động trong Google Trang tính không?

Phạm vi được đặt tên có thể động không?

Tôi có thể sử dụng dải ô đã đặt tên ở định dạng có điều kiện trong Google Trang tính không?