programming excel

Hằng cột giữ Excel

Nếu bạn đang thắc mắc tại sao bạn thậm chí muốn sử dụng bảng dữ liệu thay vì chỉ các ô được sắp xếp hợp lý trong Excel, thì có lẽ bạn nên ôn lại nhanh…

Các bảng dữ liệu trong Excel đặc biệt hữu ích vì chúng phát triển và thay đổi để chấp nhận dữ liệu mới khi dữ liệu đó được thêm vào bảng mà không cần cập nhật công thức hoặc tham chiếu. Điều này cực kỳ có giá trị khi bạn muốn sao chép và dán dữ liệu vào bảng tính từ nguồn bên ngoài để luôn cập nhật bảng tính. Trong bảng dữ liệu, bạn có thể tạo các cột được tính tham chiếu đến các phần khác của bảng dữ liệu. Khi dữ liệu mới được thêm vào, các cột được tính toán sẽ tự động cập nhật thông tin mới và hàng mới

Các công thức tóm tắt như

8 và

9 có thể được thiết kế để tham chiếu toàn bộ bảng hoặc các cột cụ thể, sau đó tự động thay đổi để cập nhật phạm vi của chúng khi bảng tăng hoặc giảm

Tính linh hoạt của bảng dữ liệu khiến chúng trở thành lựa chọn đầu tiên để làm việc với thông tin có cấu trúc, vì vậy điều đó có nghĩa là chúng ta cần học cách làm việc với chúng. Tiếp tục đọc để tìm hiểu làm thế nào

Tham chiếu bảng dữ liệu thông thường hoạt động như thế nào

Tham chiếu cột bảng dữ liệu

Khi bạn đang làm việc với các bảng dữ liệu trong Excel, các tham chiếu ô trông hơi khác so với tổ hợp chữ cái-số A1 thông thường cho các hàng cột. Điều này là do mỗi hàng của bảng hoạt động giống như trong bảng tính 1 hàng của riêng nó. Điều đó có nghĩa là các tham chiếu chỉ cần tham chiếu đến tên bảng và cột. Hàng được coi là chính nó

Hãy nhìn vào một bảng đơn giản.

Nếu chúng ta muốn cộng tất cả cột Doanh số trong bảng dữ liệu, thì công thức sẽ như sau.

=SUM[Table1[Sales]]

Đầu ra cho công thức này sẽ là

Lưu ý rằng nó không yêu cầu bắt đầu hoặc kết thúc hàng. Nó chỉ hỏi tên cột và bảng

Bây giờ, hãy thêm dữ liệu vào bảng.

Công thức tính tổng cột Doanh số vẫn giữ nguyên.

=SUM[Table1[Sales]]

Tuy nhiên, hiện tại, đầu ra cho công thức này đã tự động cập nhật

________số 8

Không làm bất cứ điều gì, các tính toán chứa dữ liệu bổ sung. Không cần thay đổi tham chiếu trong công thức SUM[]

Ví dụ về bảng dữ liệu có nhiều tham chiếu

Hãy hiển thị thêm một ví dụ về công thức sử dụng cùng một bảng.

Thay vì tạo công thức bên ngoài bảng, hãy thêm một cột đã tính để cộng tổng cho từng Khu vực. Đặt tên cho một cột mới ở bên phải được gọi là Tổng khu vực. Trong ô đầu tiên của cột, hãy nhập công thức sau.

=SUMIFS[[Sales],[Region],[@Region]]

Nó sẽ giống như thế này

Lưu ý rằng thuật ngữ cuối cùng – tiêu chí cho SUMIFS[] – sử dụng ký hiệu

=SUM[Table1[Sales]]

0 [“tại”]. Ký hiệu này có nghĩa là bạn đang đề cập đến giá trị của cột Khu vực trong hàng hiện tại. Chỉ cần nhấn ENTER để điền công thức đó vào toàn bộ cột và bạn có thể thấy kết quả của tham chiếu này trong bảng đã hoàn thành

Phép tính SUMIF[] tính tổng tất cả Doanh số bán hàng cho từng Khu vực, đưa ra tổng số phù hợp cho mọi hàng từ khu vực phía Bắc [và cả cho từng hàng khác]

Giờ đây, bạn có thể thấy sức mạnh của bảng dữ liệu Excel, có thể bạn sẽ muốn sử dụng chúng… Để xây dựng các công thức phức tạp một cách nhanh chóng và đáng tin cậy bằng cách sử dụng bảng dữ liệu, bạn cần có khả năng khóa các tham chiếu, vì vậy, đó là điều chúng ta sẽ tìm hiểu tiếp theo

Cách xây dựng tài liệu tham khảo khóa

Thông thường, thật dễ dàng để tạo các cột và hàng bằng cách khóa các tham chiếu trong công thức bằng cách sử dụng F4 hoặc thêm ký hiệu đô la vào hàng và cột của tham chiếu.. Điều này cho phép bạn kéo các công thức xuống nhiều hàng hoặc qua nhiều cột để nhanh chóng tạo một bảng tính hoạt động

Ví dụ về tham chiếu ô tương đối và tuyệt đối [đã khóa] bình thường

Lấy ví dụ đơn giản này

Sau đây ta xây dựng công thức tính doanh thu theo quý theo sản lượng và đơn giá. Số lượng sẽ thay đổi theo từng quý trong cột mới, nhưng đơn giá sẽ giữ nguyên, vì vậy, chúng tôi khóa tham chiếu bằng cách nhấn F4 hoặc nhập ký hiệu đô la cho

=SUM[Table1[Sales]]

1 theo cách thủ công

Sau khi nhập công thức, chúng tôi nhấp vào ở góc dưới bên phải để kéo công thức qua các cột cho các quý khác

Khi chúng tôi giải phóng nhấp chuột, các công thức cho mỗi quý được tính toán. Tham chiếu ô cho ô âm lượng [màu xanh lam] vẫn là tham chiếu “tương đối” di chuyển theo vị trí của ô công thức. Tuy nhiên, tham chiếu cho đơn giá [màu đỏ] đã trở thành "tuyệt đối", nghĩa là nó bị khóa ở vị trí ban đầu

Tạo tham chiếu tuyệt đối [khóa] trong bảng dữ liệu

Rất tiếc, việc sử dụng F4 hoặc thêm ký hiệu đô la không khóa tham chiếu trong bảng dữ liệu. Nó thậm chí không tính toán ô, thay vào đó đưa ra một lỗi. May mắn thay, có một cách để đạt được hành vi tương tự, nhưng nó yêu cầu sử dụng cú pháp bảng dữ liệu của Excel mà chúng tôi đã đề cập ở trên

Cách dễ nhất để chỉ ra cách thực hiện điều này là xem qua một ví dụ. Hãy kết hợp hai cấu trúc dữ liệu mẫu mà chúng ta đang làm việc để thiết kế một vấn đề cần giải quyết trong phần tiếp theo. Nhấn Next để tiếp tục

Cách tạo tham chiếu khóa trong bảng dữ liệu

Dữ liệu mẫu

Hãy mở rộng bảng Doanh số khu vực của chúng tôi lên toàn bộ thời gian 6 tháng. Đây có thể là hình thức dữ liệu của bạn nếu bạn đang nhập dữ liệu từ cơ sở dữ liệu hoặc phần mềm báo cáo khác. Nó rất dễ đọc bằng máy nhưng không dễ đọc bằng con người. Trong ví dụ này, chúng ta sẽ đặt tên bảng trong Excel là

=SUM[Table1[Sales]]

Bây giờ, hãy tạo một bảng để chuyển dữ liệu sang định dạng dễ đọc hơn. Trong trường hợp này, mục tiêu của chúng tôi là có một cột riêng cho mỗi tháng và một hàng riêng cho từng khu vực

Bảng này sẽ được gọi là

=SUM[Table1[Sales]]

3. Để điền dữ liệu vào bảng này, chúng ta sẽ sử dụng một công thức SUMIFS[] đơn giản khác, nhưng chúng ta sẽ làm việc bên trong một bảng và tra cứu thông tin bên trong một bảng khác…

Xây dựng công thức đề cập đến các cột và hàng trong bảng dữ liệu bị khóa

Bắt đầu từ cột phía Bắc và hàng ngày 16 tháng 1, hãy xây dựng công thức này

Nó sẽ trông giống thế này

Chúng tôi đang khóa tham chiếu theo ba cách riêng biệt trong công thức này

Đề cập đến toàn bộ cột bị khóa trong bảng dữ liệu
Tham chiếu đến hàng hiện tại của cột bị khóa trong bảng dữ liệu
Đề cập đến hàng tiêu đề bị khóa của bảng dữ liệu

Mỗi cái trong số này sử dụng cú pháp hơi khác nhau, vì vậy tôi sẽ hiển thị từng cái theo thứ tự

1. Khóa toàn bộ cột bảng dữ liệu

Các tham chiếu cột của bảng dữ liệu bình thường trông như thế này khi bạn ở trong cùng một bảng

Khi bạn ở bên ngoài bảng chứa cột, chúng trông như thế này

Mẹo để khóa tham chiếu cho cột liên quan đến việc coi nó giống như một dải cột chỉ bao gồm chính nó. Bạn cũng phải luôn tham khảo tên bảng, ngay cả từ bên trong bảng. Do đó, cú pháp cột bảng dữ liệu bị khóa trông như thế này

2. Tham chiếu hàng hiện tại của cột bảng dữ liệu bị khóa

Các tham chiếu hàng hiện tại bình thường cho các bảng dữ liệu trông như thế này khi bạn ở trong cùng một bảng

Khi bạn ở bên ngoài bảng chứa cột, chúng trông như thế này

Tương tự như khóa toàn bộ cột, như đã thấy ở trên, mẹo để khóa tham chiếu hàng hiện tại là coi nó giống như một phạm vi, nhưng ký hiệu @ chỉ được sử dụng ở đầu tham chiếu phạm vi

3. Khóa tham chiếu tiêu đề bảng dữ liệu

Làm việc với các tiêu đề bảng dữ liệu Excel hơi khó khăn vì Excel coi chúng như các phần tử cố định. Điều đó có nghĩa là nó sẽ không nhớ rằng chúng là ngày tháng hoặc các loại dữ liệu đặc biệt khác. Chúng trở thành tên trường. Giả định là số lượng cột trong bảng dữ liệu của bạn sẽ không thay đổi theo thời gian – chỉ là số lượng hàng

Điều này có nghĩa là để tham chiếu tiêu đề trong bảng dữ liệu trong công thức [và để công thức đó có thể kéo được], bạn cần khóa tham chiếu. May mắn thay, hàng tiêu đề của bảng dữ liệu luôn ở cùng một vị trí, vì vậy bạn có thể khóa nó giống như một tham chiếu ô bình thường trong Excel. Trong công thức ví dụ trên, đó chính xác là những gì chúng tôi đã làm

Ký hiệu này đảm bảo rằng khi công thức được kéo ngang, nó sẽ luôn tham chiếu Hàng 1 trong cột hiện tại. [Số hàng bị khóa, nhưng cột vẫn tương đối. ]

Công thức bảng dữ liệu đã hoàn thành

Sau khi kéo công thức xuống và qua bảng dữ liệu PivotTable mới của bạn, bạn sẽ thấy phương trình tự động thay đổi để điền vào các ô còn lại

Công thức mới trong ô E7 đọc

Thực hiện đúng, công thức trông rất giống nhau trong mỗi ô của bảng dữ liệu. Điều duy nhất sẽ thay đổi là các tham chiếu đến hàng tiêu đề của bảng dữ liệu, vì đây là các tham chiếu khóa bình thường cho các ô Excel

Đây là bảng dữ liệu cuối cùng, hoàn chỉnh với thông tin xoay vòng. Nó dễ đọc hơn nhiều và có thể được sử dụng để tạo biểu đồ và các đầu ra báo cáo khác khi cần thiết

Tải phần bổ trợ Excel vào bản đồ “Khóa tham chiếu bảng dữ liệu” thành F4

Quá trình thêm phạm vi vào tất cả các tham chiếu bảng dữ liệu của bạn để biến chúng thành giá trị tuyệt đối có thể gây rắc rối nếu bạn cần thực hiện thường xuyên… May mắn thay, Jon Acampora tại Excel Campus đã tạo một phần bổ trợ sẽ ánh xạ các phạm vi khóa này vào bàn phím

Bạn có thể kiểm tra bổ trợ của anh ấy tại đây. Tham chiếu có cấu trúc tuyệt đối trong Công thức bảng Excel

Nhận các mẹo và thủ thuật Excel mới nhất bằng cách tham gia bản tin

Andrew Roberts đã giải quyết các vấn đề kinh doanh với Microsoft Excel trong hơn một thập kỷ. Excel Tactics được dành riêng để giúp bạn thành thạo nó

Tham gia bản tin để cập nhật những bài viết mới nhất. Đăng ký và bạn sẽ nhận được hướng dẫn miễn phí với 10 phím tắt tiết kiệm thời gian