Thông thường, khi làm việc với Microsoft Excel, chúng ta có thể cần chèn dữ liệu từ một trang tính Excel khác. Nghe có vẻ phức tạp phải không? . Trong bài viết này, chúng tôi sẽ trình bày 4 cách để tham chiếu tên của trang tính trong công thức Excel. Ngoài ra, chúng ta cũng sẽ học cách lấy tên của trang tính đang hoạt động và tham chiếu một trang tính khác dựa trên giá trị
Mục lục ẩn
Tải sách bài tập thực hành
4 cách để tham chiếu tên bảng tính trong công thức Excel
1. Tên trang tính tham khảo không có dấu cách hoặc ký tự chấm câu
2. Tên trang tính tham khảo có dấu cách hoặc ký tự chấm câu
3. Tham chiếu ô động trong trang tính khác
4. Tạo tham chiếu đến một sổ làm việc khác
Cách lấy tên của bảng tính đang hoạt động trong Excel
Cách tham chiếu một trang tính khác dựa trên giá trị ô trong Excel
phần thực hành
Phần kết luận
Những bài viết liên quan
Tải sách bài tập thực hành
Reference_Worksheet_Name_in_Excel_Formula. xlsx
Tham chiếu bảng tính động. xlsm
Tham khảo từ một sổ làm việc khác. xlsx
4 cách để tham chiếu tên bảng tính trong công thức Excel
Đầu tiên và quan trọng nhất, hãy xem xét Dữ liệu bán hàng cho tháng 1 trong trang tính "Tháng 1", mô tả "Tên sản phẩm" và "Doanh số" bằng USD
Theo cách tương tự, chúng ta có Dữ liệu bán hàng cho tháng 2 trong trang tính “Tháng 2”. Ở đây, chúng tôi muốn có được "Tổng doanh số" bằng cách lấy dữ liệu từ trang tính "Tháng 1 và Tháng 2". Vì vậy, không chậm trễ hơn nữa, hãy khám phá cách tham chiếu tên trang tính trong công thức Excel
Ở đây, chúng tôi đã sử dụng phiên bản Microsoft Excel 365;
1. Tên trang tính tham khảo không có dấu cách hoặc ký tự chấm câu
Trước hết, chúng tôi sẽ chứng minh trường hợp tên trang tính không có dấu cách hoặc ký tự dấu câu giữa chúng
📌 Các bước.
- Ngay từ đầu, hãy chuyển đến ô C5 >> nhập công thức được cung cấp bên dưới
=January!C5+February!C5
Ở đây, “Tháng Giêng và Tháng Hai” đề cập đến tên trang tính và ô C5 tương ứng với “Doanh số Máy tính để bàn” trong hai tháng này
Đọc thêm. Cách liên kết trang tính Excel với trang tính khác [5 cách]
2. Tên trang tính tham khảo có dấu cách hoặc ký tự chấm câu
Ngoài ra, phương pháp này xem xét trường hợp có khoảng trắng hoặc ký tự dấu chấm câu giữa tên của các trang tính. Trong trường hợp này, giả sử bảng tính "Doanh số tháng 1 và tháng 2". Bây giờ chúng tôi muốn trích xuất dữ liệu “Doanh số” từ hai trang tính và trả về “Tổng doanh số” trong một trang tính khác
📌 Các bước.
- Để bắt đầu, hãy di chuyển đến ô C5 >> nhập biểu thức bên dưới
='January Sales'!C5+'February Sales'!C5
Trong trường hợp này, "Doanh số tháng Giêng" và "Doanh số tháng Hai" đại diện cho tên trang tính;
Đọc thêm. Tham chiếu từ Sổ làm việc Excel khác mà không cần mở [5 ví dụ]
bài đọc tương tự
- Cách liên kết nhiều ô từ một trang tính khác trong Excel [5 cách dễ dàng]
- Cách liên kết tệp trong Excel [5 cách tiếp cận khác nhau]
- Liên kết sổ làm việc Excel để cập nhật tự động [5 phương pháp]
- Cách liên kết tài liệu Word với Excel [2 phương pháp dễ dàng]
3. Tham chiếu ô động trong trang tính khác
Trên thực tế, những gì chúng tôi đã làm cho đến nay là tốt, nhưng có một vấn đề lớn. Nói một cách đơn giản, nếu có nhiều trang tính, thì chúng ta phải nhập tên của chúng theo cách thủ công. Đừng tuyệt vọng. Chúng ta có thể tự động hóa tác vụ lặp đi lặp lại này bằng Mã VBA và hàm INDIRECT trả về tham chiếu ô của một chuỗi
📌 Các bước.
- Đầu tiên, điều hướng đến tab Nhà phát triển >> nhấp vào nút Visual Basic
Bây giờ, thao tác này sẽ mở Trình soạn thảo Visual Basic trong một cửa sổ mới
- Thứ hai, chuyển đến tab Chèn >> chọn Mô-đun
Để tiện cho việc tham khảo, bạn có thể copy đoạn code từ đây và dán vào cửa sổ như hình bên dưới
Function Active_Work_Sheet_Name[]
Dim wbk As Workbook
Set wbk = ActiveWorkbook
Dim result As Variant
Dim j As Variant
j = wbk.Sheets.Count - 1
ReDim result[j, 0]
Dim k As Variant
For k = 0 To j
result[k, 0] = wbk.Sheets[k + 1].Name
Next k
Active_Work_Sheet_Name = result
End Function⚡ Phân tích mã.
Ở đây, chúng tôi sẽ giải thích mã VBA để tạo tên trang tính
- Trong phần đầu tiên, thủ tục con được đặt tên, ở đây là Active_Work_Sheet_Name[]
- Tiếp theo, xác định các biến wbk, result, j và k và gán kiểu dữ liệu Workbook và Variant tương ứng
- Trong phần thứ hai, sử dụng thuộc tính Count để đếm số trang tính và Vòng lặp For để lặp qua tất cả các trang tính trong sổ làm việc
- Thứ ba, đóng cửa sổ VBA >> nhập hàm Active_Work_Sheet_Name[] để lấy tất cả tên sheet
=Active_Work_Sheet_Name[]
- Cuối cùng, điều hướng đến ô C5 >> chèn phương trình vào Thanh công thức
=INDIRECT["'"&E5&"'!C5"]+INDIRECT["'"&E6&"'!C5"]
Chẳng hạn, các ô E5 và E6 trỏ đến tên trang tính “Tháng 1 và Tháng 2” trong khi ô C5 đề cập đến “Doanh số” tương ứng của chúng
Đọc thêm. Chuyển dữ liệu cụ thể từ một trang tính này sang trang tính khác cho báo cáo
4. Tạo tham chiếu đến một sổ làm việc khác
Đối với một điều, chúng ta có thể tạo một tham chiếu đến các sổ làm việc khác [sổ làm việc nguồn] để đưa dữ liệu vào một sổ làm việc khác [sổ làm việc đích]. Vì vậy, chỉ cần làm theo
📌 Các bước.
- Để bắt đầu, hãy sao chép và dán công thức bên dưới vào ô C5
=[Referencing_Worksheet_Name_in_Excel_Formula.xlsx]January!C5+[Referencing_Worksheet_Name_in_Excel_Formula.xlsx]February!C5
Ví dụ: “[Tham chiếu_Worksheet_Name_in_Excel_Formula. xlsx]” là tên sổ làm việc chứa trang tính “tháng 1”. Ngoài ra, ô C5 biểu thị các giá trị “Doanh số”
Cách lấy tên của bảng tính đang hoạt động trong Excel
Ngược lại, chúng ta có thể trích xuất tên của trang tính đang hoạt động bằng cách kết hợp các hàm MID, FIND và CELL. Thật đơn giản và dễ dàng, vì vậy hãy xem quy trình chi tiết
📌 Các bước.
- Để bắt đầu, hãy nhấp vào ô B5 >> chèn phương trình sau
=MID[CELL["filename",B5],[FIND["]",CELL["filename",B5]]+1],45]
Phân tích công thức
- CELL[“filename”,B5] → trả về thông tin về định dạng, vị trí của nội dung ô. Ở đây, “filename” là đối số info_type trả về tên và vị trí tệp. Tiếp theo, ô B5 là đối số tham chiếu tùy chọn nơi trả về kết quả
- FIND[“]”,CELL[“filename”,B5]] → trả về vị trí bắt đầu của một chuỗi văn bản trong một chuỗi văn bản khác. Ở đây, “]” là đối số find_text trong khi CELL[“filename”,B5] là đối số within_text. Ở đây, hàm FIND trả về vị trí của dấu ngoặc vuông trong chuỗi văn bản
- Đầu ra → 103
- MID[CELL[“filename”,B5],[FIND[“]”,CELL[“filename”,B5]]+1],45] → trở thành
- MID[CELL[“filename”,B5],[103+1],45] → trả về các ký tự từ giữa chuỗi văn bản, đã cho vị trí bắt đầu và độ dài. Ở đây, CELL[“filename”,B5] là đối số văn bản, [103+1] là đối số start_num và 45 là đối số num_chars đại diện cho số ký tự tối đa trong tên trang tính
- Đầu ra → “Tên trang tính hiện hoạt”
Cách tham chiếu một trang tính khác dựa trên giá trị ô trong Excel
Cuối cùng nhưng không kém phần quan trọng, chúng ta cũng có thể tham khảo một bảng tính Excel khác dựa trên giá trị ô. Nhân dịp này, chúng ta hãy xem xét Dữ liệu bán hàng PC và Phụ kiện hiển thị tên "Sản phẩm", "Doanh số tháng 1" và "Doanh số tháng 2" tương ứng
📌 Các bước.
- Trước tiên, hãy chuyển đến tab Dữ liệu >> nhấp vào Xác thực dữ liệu >> sau đó làm theo các bước được hiển thị trong GIF được cung cấp bên dưới
- Sau đó chuyển đến ô B7 >> sử dụng hàm VLOOKUP như hình bên dưới >> tính “Tổng Doanh Số” bằng hàm SUM
=VLOOKUP[C4,'Sales Data'!B5:D13,2,FALSE]
Trong trường hợp này, ô C4 là "Mục" được chọn từ danh sách thả xuống
Phân tích công thức
- VLOOKUP[C4,'Dữ liệu bán hàng'. B5. D13,2,FALSE] → tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng, sau đó trả về một giá trị trong cùng một hàng từ một cột mà bạn chỉ định. Ở đây, C4 [ đối số lookup_value] được ánh xạ từ 'Dữ liệu bán hàng'. B5. D13 [đối số table_array] là trang tính "Dữ liệu bán hàng". Tiếp theo, 2 [đối số col_index_num] đại diện cho số cột của giá trị tra cứu. Cuối cùng, FALSE [đối số range_lookup] đề cập đến Kết quả khớp chính xác của giá trị tra cứu
- Đầu ra → $1090
Ngoài ra, bạn có thể làm theo các bước trong thời gian thực bằng cách tham khảo ảnh GIF động được hiển thị bên dưới
phần thực hành
Chúng tôi đã cung cấp phần Thực hành ở bên phải của mỗi tờ để bạn có thể tự thực hành. Hãy chắc chắn để làm điều đó một mình
Như một lưu ý, “Tham chiếu bảng tính động. xlsx” và “Tham chiếu từ sổ làm việc khác. xlsx” được sử dụng trong Phương pháp 3 và Phương pháp 4. Ngược lại, “Tên trang tính tham chiếu trong công thức Excel. xlsx” chứa các phương thức còn lại
Phần kết luận
Tóm lại, hướng dẫn này khám phá tất cả thông tin chi tiết về cách tham chiếu tên trang tính trong công thức Excel. Bây giờ, chúng tôi hy vọng tất cả các phương pháp được đề cập ở trên sẽ nhắc bạn áp dụng chúng vào bảng tính Excel của mình hiệu quả hơn. Hơn nữa, nếu bạn có bất kỳ câu hỏi hoặc phản hồi nào, vui lòng cho tôi biết trong phần bình luận. Hoặc bạn có thể xem các bài viết khác của chúng tôi liên quan đến hàm Excel trên ExcelDemy