seychellesartprojects.org

Làm thế nào để tạo tham chiếu có cấu trúc trong Excel?

Tham chiếu có cấu trúc bắt đầu với bảng excel. Ngay sau khi các bảng được tạo trong excel, nó sẽ tự động tạo các tham chiếu có cấu trúc cho bạn.

Bây giờ hãy nhìn vào hình ảnh dưới đây.

• Bước 1: Tôi đã đưa ra một liên kết đến ô B3, thay vì hiển thị liên kết như B2, ​​nó đang hiển thị dưới dạng Table1 [@Sales]. Ở đây Table1 là tên của bảng và @Sales là cột mà chúng ta đang đề cập đến. Tất cả các ô trong cột này được gọi bằng tên Bảng và theo sau là tên tiêu đề cột.
• Bước 2: Bây giờ tôi sẽ thay đổi tên bảng thành Data_Table và thay đổi tiêu đề cột thành Số tiền .
• Bước 3: Để thay đổi tên bảng, hãy đặt con trỏ vào bên trong bảng> vào Thiết kế> Tên bảng.

• Bước 4: Đề cập đến tên bảng là Data_Table.

• Bước 5: Bây giờ thay đổi cung cấp một tham chiếu đến ô B3.

Như vậy chúng ta đã hiểu rằng tham chiếu có cấu trúc có hai phần Tên bảng & Tên cột.

Các ví dụ

Bạn có thể tải xuống Mẫu Excel tham chiếu có cấu trúc này tại đây - Mẫu Excel tham chiếu có cấu trúc

Ví dụ 1

Sử dụng tham chiếu có cấu trúc, bạn có thể làm cho công thức của mình trở nên động. Không giống như các tham chiếu ô bình thường, nó cho phép công thức tồn tại trong trường hợp thêm và xóa trong phạm vi dữ liệu.

Hãy để tôi áp dụng công thức SUM cho cả phạm vi bình thường và bảng excel.

Công thức SUM cho Phạm vi Bình thường.

Công thức SUM cho Bảng Excel.

Hãy để tôi thêm một vài dòng vào dữ liệu của cả bảng thông thường và bảng excel. Tôi đã thêm 2 mục hàng vào dữ liệu, bây giờ hãy thấy sự khác biệt.

Tham chiếu có cấu trúc trong bảng excel hiển thị giá trị được cập nhật nhưng phạm vi dữ liệu bình thường không hiển thị các giá trị được cập nhật trừ khi bạn thực hiện một số thay đổi đối với công thức theo cách thủ công.

Ví dụ số 2

Bây giờ, hãy xem thêm một ví dụ. Tôi có thông tin Tên Sản phẩm, Số lượng và Giá cả. Sử dụng thông tin này, tôi cần đến Giá trị bán hàng.

Để có được giá trị bán hàng, công thức là Số lượng * Giá . Hãy áp dụng công thức này trong bảng.

Công thức cho biết [@QTY] * [@PRICE]. Điều này dễ hiểu hơn so với tham chiếu thông thường của B2 * C2. Chúng tôi không nhận được tên bảng nếu chúng tôi đang đặt công thức bên trong bảng.

Sự cố với tham chiếu có cấu trúc trong Excel

Trong khi sử dụng tham chiếu có cấu trúc, chúng tôi gặp phải một số vấn đề được nêu dưới đây.

Vấn đề số 1

Các tham chiếu có cấu trúc cũng có những vấn đề riêng. Tất cả chúng ta đều quen thuộc với việc áp dụng công thức excel và sao chép hoặc kéo nó sang các ô còn lại khác. Đây không phải là quá trình tương tự trong Tham chiếu có cấu trúc, nó hoạt động hơi khác một chút.

Bây giờ, hãy xem ví dụ dưới đây. Tôi đã áp dụng công thức SUM trong excel cho phạm vi bình thường.

Nếu tôi muốn tính tổng Giá và Giá trị Bán hàng, tôi sẽ chỉ cần sao chép và dán hoặc kéo công thức hiện tại vào hai ô còn lại và nó sẽ cung cấp cho tôi giá trị SUM của Giá và Giá trị Bán hàng.

Bây giờ áp dụng cùng một công thức cho bảng excel cho cột Qty.

Bây giờ chúng ta có tổng của cột Qty. Giống như phạm vi bình thường, công thức sao chép công thức hiện tại và dán vào cột Giá để nhận tổng Giá.

Ôi chúa ơi!!! Nó không hiển thị tổng của cột Giá thay vì nó vẫn chỉ hiển thị tổng của cột Qty. Vì vậy, chúng tôi không thể sao chép và dán công thức này vào ô liền kề hoặc bất kỳ ô nào khác để tham chiếu đến cột hoặc hàng tương đối.

Kéo công thức để thay đổi tham chiếu

Bây giờ chúng tôi biết hạn chế của nó, chúng tôi không thể thực hiện công việc sao chép dán nữa với các tham chiếu có cấu trúc. Sau đó, làm thế nào để chúng ta khắc phục hạn chế này?

Giải pháp rất đơn giản chúng ta chỉ cần kéo công thức thay vì sao chép. Chọn ô công thức và sử dụng chốt điền và kéo nó vào hai ô còn lại để thay đổi tham chiếu cột thành Giá và Giá trị bán.

Bây giờ chúng tôi đã cập nhật các công thức để lấy tổng số tương ứng.

Vấn đề số 2

Chúng tôi đã thấy một vấn đề với các tham chiếu cấu trúc và chúng tôi cũng tìm ra giải pháp, nhưng chúng tôi có một vấn đề nữa ở đây, chúng tôi không thể thực hiện cuộc gọi dưới dạng tham chiếu tuyệt đối nếu chúng tôi đang kéo công thức đến các ô khác.

Bây giờ chúng ta hãy xem xét ví dụ dưới đây. Tôi có một bảng bán hàng với nhiều mục nhập và tôi muốn tổng hợp dữ liệu bằng cách sử dụng hàm SUMIF trong excel.

Bây giờ tôi sẽ áp dụng hàm SUMIF để lấy các giá trị bán hàng tổng hợp cho từng sản phẩm.

Tôi đã áp dụng công thức cho tháng 1, vì đó là tham chiếu có cấu trúc nên chúng tôi không thể sao chép và dán công thức vào hai cột còn lại, nó sẽ không thay đổi tham chiếu đến tháng 2 và tháng 3, vì vậy tôi sẽ kéo công thức.

Oh!! Tôi không nhận được bất kỳ giá trị nào trong cột Tháng 2 & Tháng 3. Vấn đề sẽ là gì ??? Xem xét kỹ công thức.

Chúng tôi đã kéo công thức từ tháng 1. Trong đối số đầu tiên của hàm SUMIF là Phạm vi tiêu chí Sales_Table [Sản phẩm]  vì chúng tôi đã kéo công thức nó có các thay đổi thành Bán hàng _Table [Tháng 1].

Vậy chúng ta phải giải quyết như thế nào ?? Chúng ta cần tạo đối số đầu tiên, tức là cột Sản phẩm là tuyệt đối và các cột khác là tham chiếu tương đối. Không giống như tham chiếu thông thường, chúng tôi không có quyền sử dụng phím F4 để thay đổi kiểu tham chiếu.

Giải pháp là chúng ta cần sao chép cột tham chiếu như trong hình dưới đây.

Bây giờ chúng ta có thể kéo công thức sang hai cột doa lại khác. Phạm vi tiêu chí sẽ không đổi và các tham chiếu cột khác sẽ thay đổi tương ứng.

Mẹo chuyên nghiệp: Để đặt ROW làm tham chiếu tuyệt đối, chúng tôi cần tạo mục nhập ROW kép nhưng chúng tôi cần đặt ký hiệu @ trước tên ROW.

= Sales_Table [@ [Sản phẩm]: [Sản phẩm]]

Làm thế nào để tắt tham chiếu có cấu trúc trong Excel?

Nếu bạn không phải là người yêu thích tham chiếu có cấu trúc, bạn có thể tắt bằng cách làm theo các bước dưới đây.

• Bước 1: Đi tới TỆP> Tùy chọn.
• Bước 2: Công thức> Bỏ chọn Sử dụng tên bảng trong công thức.

Những điều cần ghi nhớ

• Để tạo tham chiếu tuyệt đối trong tham chiếu có cấu trúc, chúng ta cần phải nhân đôi tên cột.
• Chúng ta không thể sao chép công thức của tham chiếu có cấu trúc thay vào đó chúng ta cần kéo công thức.
• Chúng tôi không thể thấy chính xác ô mà chúng tôi đang đề cập đến trong các tham chiếu có cấu trúc.
• Nếu bạn không quan tâm đến các tham chiếu có cấu trúc, bạn có thể tắt chúng.