Nội dung
Khi sử dụng Microsoft Excel, người dùng thường không biết trước kết quả là bao nhiêu thông tin sẽ có trong bảng. Do đó, chúng tôi không hiểu trong tất cả các tình huống, phạm vi nào nên được bảo hiểm. Rốt cuộc, một tập hợp các ô là một khái niệm biến. Để giải quyết vấn đề này, cần phải tạo phạm vi tự động để nó chỉ dựa trên lượng dữ liệu được nhập bởi người dùng.
Tự động thay đổi phạm vi ô trong Excel
Lợi thế của phạm vi tự động trong Excel là chúng giúp sử dụng công thức dễ dàng hơn nhiều. Ngoài ra, chúng giúp đơn giản hóa đáng kể việc phân tích dữ liệu phức tạp có chứa một số lượng lớn các công thức, bao gồm nhiều hàm. Bạn có thể đặt tên cho dải ô này và sau đó nó sẽ được cập nhật tự động tùy thuộc vào dữ liệu mà nó chứa.
Cách thực hiện thay đổi phạm vi tự động trong Excel
Giả sử bạn là nhà đầu tư cần đầu tư vào đối tượng nào đó. Do đó, chúng tôi muốn biết thông tin về tổng số tiền bạn có thể kiếm được trong toàn bộ thời gian mà số tiền đó sẽ hoạt động cho dự án này. Tuy nhiên, để có được thông tin này, chúng ta cần thường xuyên theo dõi tổng lợi nhuận mà đối tượng này mang lại cho chúng ta là bao nhiêu. Tạo báo cáo tương tự như trong ảnh chụp màn hình này.
Thoạt nhìn, giải pháp rất rõ ràng: bạn chỉ cần tính tổng toàn bộ cột. Nếu các mục nhập xuất hiện trong đó, số tiền sẽ được cập nhật độc lập. Nhưng phương pháp này có nhiều nhược điểm:
- Nếu vấn đề được giải quyết theo cách này, sẽ không thể sử dụng các ô trong cột B cho các mục đích khác.
- Bàn như vậy sẽ ngốn nhiều RAM dẫn đến không thể sử dụng tài liệu trên những máy tính yếu.
Do đó, cần phải giải quyết vấn đề này thông qua các tên động. Để tạo chúng, bạn phải thực hiện chuỗi hành động sau:
- Chuyển đến tab "Công thức", nằm trong menu chính. Sẽ có một phần “Tên được xác định”, nơi có nút “Chỉ định tên”, trên đó chúng ta cần nhấp vào.
- Tiếp theo, một hộp thoại sẽ xuất hiện trong đó bạn cần điền vào các trường như trong ảnh chụp màn hình. Điều quan trọng cần lưu ý là chúng ta cần áp dụng hàm = GIẢI PHÓNG cùng với chức năng KIỂM TRAđể tạo phạm vi cập nhật tự động.
- Sau đó chúng ta cần sử dụng hàm TÓM TẮT, sử dụng phạm vi động của chúng tôi làm đối số.
Sau khi hoàn thành các bước này, chúng tôi có thể thấy mức độ phù hợp của các ô thuộc phạm vi "thu nhập" được cập nhật như thế nào khi chúng tôi thêm các phần tử mới vào đó.
Hàm OFFSET trong Excel
Hãy xem xét các chức năng mà chúng tôi đã ghi lại trong trường "phạm vi" trước đó. Sử dụng chức năng TRANH CHẤP chúng ta có thể xác định số lượng phạm vi đã cho có bao nhiêu ô trong cột B được lấp đầy. Các đối số của hàm như sau:
- Bắt đầu ô. Với đối số này, người dùng có thể cho biết ô nào trong phạm vi sẽ được coi là trên cùng bên trái. Nó sẽ báo xuống bên phải.
- Khoảng bù theo hàng. Sử dụng phạm vi này, chúng tôi đặt số lượng ô mà theo đó một phần bù sẽ xảy ra từ ô trên cùng bên trái của phạm vi. Bạn không chỉ có thể sử dụng các giá trị dương, mà còn bằng không và trừ. Trong trường hợp này, sự dịch chuyển có thể hoàn toàn không xảy ra, hoặc nó sẽ được thực hiện theo hướng ngược lại.
- Khoảng bù theo cột. Tham số này tương tự như tham số trước, chỉ là nó cho phép bạn thiết lập mức độ dịch chuyển ngang của phạm vi. Ở đây bạn cũng có thể sử dụng cả giá trị XNUMX và giá trị âm.
- Số lượng phạm vi chiều cao. Trên thực tế, tiêu đề của lập luận này cho chúng ta thấy rõ ý nghĩa của nó. Đây là số ô mà theo đó phạm vi sẽ tăng lên.
- Giá trị của phạm vi theo chiều rộng. Đối số tương tự như đối số trước, chỉ nó liên quan đến các cột.
Bạn không cần chỉ định hai đối số cuối cùng nếu bạn không cần thiết. Trong trường hợp này, giá trị phạm vi sẽ chỉ là một ô. Ví dụ: nếu bạn chỉ định công thức = OFFSET (A1; 0; 0), công thức này sẽ tham chiếu đến cùng ô với ô trong đối số đầu tiên. Nếu độ lệch dọc được đặt thành 2 đơn vị, thì trong trường hợp này ô sẽ tham chiếu đến ô A3. Bây giờ hãy mô tả chi tiết ý nghĩa của hàm KIỂM TRA.
Hàm COUNT trong Excel
Sử dụng chức năng KIỂM TRA chúng tôi xác định có bao nhiêu ô trong cột B mà chúng tôi đã điền vào tổng số. Tức là, bằng cách sử dụng hai hàm, chúng tôi xác định có bao nhiêu ô trong phạm vi được lấp đầy và dựa trên thông tin nhận được, xác định kích thước của phạm vi. Do đó, công thức cuối cùng sẽ như sau: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Hãy cùng xem làm thế nào để hiểu đúng nguyên tắc của công thức này. Đối số đầu tiên trỏ đến nơi bắt đầu phạm vi động của chúng ta. Trong trường hợp của chúng ta, đây là ô B2. Các tham số khác có tọa độ bằng không. Điều này cho thấy rằng chúng ta không cần hiệu số so với ô trên cùng bên trái. Tất cả những gì chúng tôi đang điền là kích thước dọc của phạm vi, mà chúng tôi đã sử dụng hàm làm KIỂM TRA, xác định số lượng ô chứa một số dữ liệu. Tham số thứ tư mà chúng tôi đã điền là đơn vị. Do đó, chúng tôi cho thấy rằng tổng chiều rộng của phạm vi phải là một cột.
Do đó, sử dụng hàm KIỂM TRA người dùng có thể sử dụng bộ nhớ hiệu quả nhất có thể bằng cách chỉ tải những ô có chứa một số giá trị. Theo đó, sẽ không có thêm lỗi trong công việc liên quan đến hiệu suất kém của máy tính mà bảng tính sẽ hoạt động.
Theo đó, để xác định kích thước của phạm vi phụ thuộc vào số lượng cột, bạn cần thực hiện một chuỗi hành động tương tự, chỉ trong trường hợp này bạn cần chỉ định đơn vị trong tham số thứ ba và công thức trong tham số thứ tư. KIỂM TRA.
Chúng tôi thấy rằng với sự trợ giúp của các công thức Excel, bạn không chỉ có thể tự động hóa các phép tính toán học. Đây chỉ là một giọt nước trong đại dương, nhưng trên thực tế, chúng cho phép bạn tự động hóa hầu hết mọi hoạt động mà bạn nghĩ đến.
Biểu đồ động trong Excel
Vì vậy, trong bước cuối cùng, chúng ta đã có thể tạo một phạm vi động, kích thước của phạm vi này hoàn toàn phụ thuộc vào số lượng ô được lấp đầy mà nó chứa. Giờ đây, dựa trên dữ liệu này, bạn có thể tạo biểu đồ động sẽ tự động thay đổi ngay sau khi người dùng thực hiện bất kỳ thay đổi nào hoặc thêm một cột hoặc hàng bổ sung. Trình tự các hành động trong trường hợp này như sau:
- Chúng tôi chọn phạm vi của mình, sau đó chúng tôi chèn biểu đồ của loại "Biểu đồ có phân nhóm". Bạn có thể tìm thấy mục này trong phần “Chèn” trong phần “Biểu đồ - Biểu đồ”.
- Chúng ta nhấp chuột trái vào một cột ngẫu nhiên của biểu đồ, sau đó hàm = SERIES () sẽ được hiển thị trong dòng hàm. Trong ảnh chụp màn hình, bạn có thể thấy công thức chi tiết.
- Sau đó, một số thay đổi cần được thực hiện đối với công thức. Bạn cần thay thế dải ô sau “Sheet1!” vào tên của phạm vi. Điều này sẽ dẫn đến chức năng sau: = ROW (Trang tính1! $ B $ 1 ;; Trang tính1! Thu nhập; 1)
- Bây giờ bạn vẫn phải thêm bản ghi mới vào báo cáo để kiểm tra xem biểu đồ có được cập nhật tự động hay không.
Chúng ta hãy nhìn vào sơ đồ của chúng tôi.
Hãy tóm tắt cách chúng tôi đã làm điều đó. Trong bước trước, chúng tôi đã tạo một phạm vi động, kích thước của phạm vi này phụ thuộc vào số lượng phần tử mà nó chứa. Để làm điều này, chúng tôi đã sử dụng kết hợp các hàm KIỂM TRA и TRANH CHẤP. Chúng tôi đặt tên cho phạm vi này và sau đó chúng tôi sử dụng tham chiếu đến tên này làm phạm vi biểu đồ của chúng tôi. Việc chọn phạm vi cụ thể nào làm nguồn dữ liệu ở giai đoạn đầu không quá quan trọng. Điều chính là thay thế nó bằng tên của phạm vi sau này. Bằng cách này, bạn có thể tiết kiệm rất nhiều RAM.
Các phạm vi được đặt tên và sử dụng của chúng
Bây giờ chúng ta hãy nói chi tiết hơn về cách tạo chính xác các dải ô đã đặt tên và sử dụng chúng để thực hiện các tác vụ được đặt cho người dùng Excel.
Theo mặc định, chúng tôi sử dụng địa chỉ ô thông thường để tiết kiệm thời gian. Điều này rất hữu ích khi bạn cần viết một phạm vi một hoặc nhiều lần. Nếu nó cần được sử dụng mọi lúc hoặc nó cần phải thích ứng, thì các dải ô đã đặt tên nên được sử dụng. Chúng giúp việc tạo công thức trở nên dễ dàng hơn nhiều và người dùng sẽ không quá khó để phân tích các công thức phức tạp bao gồm một số lượng lớn các hàm. Hãy mô tả một số bước liên quan đến việc tạo phạm vi động.
Tất cả bắt đầu với việc đặt tên cho ô. Để thực hiện việc này, chỉ cần chọn nó, sau đó viết tên mà chúng ta cần vào trường tên của nó. Điều quan trọng là nó phải dễ nhớ. Có một số hạn chế cần xem xét khi đặt tên:
- Độ dài tối đa là 255 ký tự. Điều này là khá đủ để gán một cái tên mà trái tim của bạn mong muốn.
- Tên không được chứa khoảng trắng. Do đó, nếu nó chứa một số từ, thì có thể tách chúng ra bằng cách sử dụng ký tự gạch dưới.
Nếu sau đó trên các trang tính khác của tệp này, chúng ta cần hiển thị giá trị này hoặc áp dụng giá trị này để thực hiện các tính toán tiếp theo, thì không cần phải chuyển sang trang tính đầu tiên. Bạn chỉ có thể viết ra tên của ô phạm vi này.
Bước tiếp theo là tạo một phạm vi được đặt tên. Thủ tục về cơ bản là giống nhau. Trước tiên, bạn cần chọn một phạm vi, và sau đó chỉ định tên của nó. Sau đó, tên này có thể được sử dụng trong tất cả các hoạt động dữ liệu khác trong Excel. Ví dụ, các phạm vi được đặt tên thường được sử dụng để xác định tổng các giá trị.
Ngoài ra, có thể tạo phạm vi được đặt tên bằng tab Công thức bằng công cụ Đặt tên. Sau khi chúng tôi chọn nó, một cửa sổ sẽ xuất hiện nơi chúng tôi cần chọn tên cho phạm vi của mình, cũng như chỉ định khu vực mà nó sẽ mở rộng theo cách thủ công. Bạn cũng có thể chỉ định nơi mà phạm vi này sẽ hoạt động: trong một trang tính hoặc trong toàn bộ cuốn sách.
Nếu một phạm vi tên đã được tạo, thì để sử dụng nó, có một dịch vụ đặc biệt được gọi là trình quản lý tên. Nó không chỉ cho phép chỉnh sửa hoặc thêm tên mới mà còn có thể xóa chúng nếu chúng không còn cần thiết.
Cần lưu ý rằng khi sử dụng phạm vi được đặt tên trong công thức, thì sau khi xóa nó, công thức sẽ không tự động bị ghi đè bằng các giá trị chính xác. Do đó, có thể xảy ra sai sót. Do đó, trước khi xóa phạm vi đã đặt tên, bạn cần đảm bảo rằng phạm vi đó không được sử dụng trong bất kỳ công thức nào.
Một cách khác để tạo một phạm vi đã đặt tên là lấy nó từ một bảng. Để làm điều này, có một công cụ đặc biệt được gọi là "Tạo từ vùng chọn". Như chúng tôi hiểu, để sử dụng nó, trước tiên bạn phải chọn phạm vi mà chúng tôi sẽ chỉnh sửa, sau đó đặt nơi chúng tôi có các tiêu đề. Do đó, dựa trên dữ liệu này, Excel sẽ tự động xử lý tất cả dữ liệu và các tiêu đề sẽ được tự động gán.
Nếu tiêu đề chứa nhiều từ, Excel sẽ tự động phân tách chúng bằng dấu gạch dưới.
Do đó, chúng tôi đã tìm ra cách tạo phạm vi được đặt tên động và cách chúng cho phép bạn tự động hóa công việc với lượng lớn dữ liệu. Như bạn có thể thấy, chỉ cần sử dụng một số chức năng và các công cụ chương trình được tích hợp trong chức năng là đủ. Không có gì phức tạp cả, mặc dù thoạt nhìn có vẻ như vậy đối với người mới bắt đầu.