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… Show
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ư 31675, 31676, 31677, 31678 và 31679 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àoTham chiếu cột bảng dữ liệuKhi 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. =SUM(Table1[Sales]) Đầu ra cho công thức này sẽ là 3167 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. =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ố 8Khô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ếuHãy hiển thị thêm một ví dụ về công thức sử dụng cùng một bảng. =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ànhPhé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óaThô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ườngLấ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ôngSau 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ệuRấ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ệuDữ liệu mẫuHã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]) 2Bâ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óaBắ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 31674 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
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ệuCá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 31675 Khi bạn ở bên ngoài bảng chứa cột, chúng trông như thế này 31676 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 31677 2. Tham chiếu hàng hiện tại của cột bảng dữ liệu bị khóaCá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 31678 Khi bạn ở bên ngoài bảng chứa cột, chúng trông như thế này 31670 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 31671 3. Khóa tham chiếu tiêu đề bảng dữ liệuLà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 31672 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ànhSau 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 31673 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 F4Quá 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 tinAndrew 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 |