Bạn đang xem bài viết Tạo Tham Chiếu Ngoại Trong Excel Để Dẫn Tới Trang Tính/Bảng Tính Khác được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bài viết này, Học Excel Online sẽ giúp bạn giải thích tham chiếu ngoại trong Excel một cách cơ bản và hướng dẫn cách lập tham chiếu cho một trang tính hoặc bảng tính trong công thức của bạn.
Khi tính toán dữ liệu trong Excel, đôi khi bạn sẽ gặp phải trường hợp cần lấy dữ liệu từ một trang tính hoặc thậm chí một file Excel khác. Để làm được điều này, bạn chỉ cần tạo liên kết giữa hai trang tính (cùng một bảng tính hoặc khác bảng tính) bằng cách sử dụng ô tham chiếu ngoại hoặc đường link.
Ô tham chiếu ngoại trong Excel là sự tham chiếu dẫn tới một ô hoặc một mảng các ô (range) không nằm trong trang tính hiện tại. Lợi ích của việc dùng tham chiếu ngoại là bất cứ khi nào ô tham chiếu thay đổi, giá trị trả về bởi ô tham chiếu ngoại sẽ tự động cập nhật.
Giữa tham chiếu ngoại trong Excel và ô tham chiếu vẫn có những điểm khác biệt mặc dù chúng tương tự nhau. Trong bài hướng dẫn này, chúng ta cùng bắt đầu với những bước cơ bản và tạo một vài loại tham chiếu ngoại qua các ví dụ cụ thể
Tạo tham chiếu từ một trang tính khácĐể tham chiếu một ô hoặc một mảng các ô từ một trang tính khác cùng nằm trong một bảng tính, đưa tên trang tính đằng sau dấu (!) nằm trước đại chỉ ô.
Công thức có dạng như sauTên_trang_tính!Địa_chỉ_ô
Ví dụ tham chiếu tới ô A1 nằm trong Sheet2, đánh Sheet2!A1.
Tên_trang_tính!Ô_đầu:Ô_cuối
Ví dụ tham chiếu tới các ô A1:A10 trong Sheet2, đánh Sheet2!A1:A10
Lưu ý: Nếu tên trang tính chứa dấu cách hoặc kí tự không phải chữ cái, bạn phải đặt tên trang tính trong dấu nháy. Ví dụ một tham chiếu ngoại tới ô A1 trong trang tính có tên là Thống kê, công thức như sau: ‘Thống kê’!A1.
Trong thực tế khi nhân giá trị ô A1 trong trang tính Thống kê với 10, công thức tham chiếu ngoại như sau
=’Thống kê’!A1*10
Tạo tham chiếu tới một trang tính khác trong ExcelKhi viết công thức tham chiếu tới ô nằm trong trang tính khác, bạn có thể đánh tên trang tính khác đằng sau dấu (!) và tham chiếu một ô một cách thủ công và dễ mắc lỗi.
Cách tốt hơn là chỉ tới ô nằm trong trang tính khác mà bạn muốn công thức tham chiếu tới và để Excel tự sửa cú pháp. Để Excel chèn tham chiếu tới một trang tính khác, làm theo các bước sau
Đánh công thức vào vị trí ô hoặc vào thanh công thức
Khi muốn chèn tham chiếu tới trang tính khác, chuyển tới trang tính và chọn một ô hoặc một mảng ô bạn muốn tham chiếu tới
Kết thúc đánh công thức và ấn Enter để hoàn tất
Ví dụ, nếu bạn có một danh sách Doanh số bán hàng và bạn muốn tính thuế giá trị gia tăng (19%) cho mỗi sản phẩm trong một trang tính khác tên VAT, thực hiện theo các bước sau
Đánh công thức =19%* vào ô B2 nằm trong trang VAT
Ấn Enter để hoàn thành công thức
Lưu ý Khi chèn tham chiếu Excel tới một trang tính khác sử dụng phương thức trên, mặc định của Excel Microsoft là thêm tham chiếu tương đối (không có kí tự $). Vậy trong ví dụ trên, bạn có thể copy công thức cho các ô khác trong cột B nằm trong trang tính VAT, ô tham chiếu có thể điều chỉnh cho mỗi hàng, và bạn sẽ có VAT cho mỗi mẫu được tính toán chính xác.
Tương tự như trên, bạn có thể tham chiếu một mảng các ô trong trang tính khác. Điểm khác biệt duy nhất là chọn nhiều ô từ trang tính. Ví dụ, để tìm tổng doanh số bán hàng trong các ô B2:B5 trong trang tính Doanh số , bạn nhập công thức sau
Trong công thức Microsoft Excel, tham chiếu ngoại từ bảng tính khách được trình bày theo hai cách dựa trên vị trí nguồn của bảng tính mở hay đóng
Khi nguồn bảng tính mở, tham chiếu ngoại chứa tên bảng tính đặt trong dấu ngoặc vuông (bao gồm phần mở rộng tệp) đứng trước tên trang tính, dấu (!), và ô tham chiếu hoặc mảng tham chiếu. Cú pháp như sau:
[Tên_bảng_tính]Tên_trang_tính!Địa_chỉ_ô_tham_chiếu
Ví dụ tham chiếu ngoại tới B2:B5 trong trang tính Tháng 1 nằm trong bảng tính Doanh số.xlsx:
[Tổng.xlsx]’T1’B2:B5
Nếu muốn tính tổng các ô, công thức tham chiếu bảng tính như sau:
=SUM ([Tổng. xlsx]T1!B2:B5)
Khi tham chiếu từ một bảng tính khác, bảng tính này không nhất thiết phải mở. Nếu nguồn của bảng tính là đóng, bạn phải chèn đường dẫn tới tham chiếu ngoại
Ví dụ, để chèn ô B2:B5 vào trang tính Tháng 1 từ bảng tính Doanh số. xlsx nằm trong folder Báo cáo trong ổ D, công thức như sau:
=SUM (D:BC[Tổng. xlsx]’T1′!B2:B5)
với
Đường dẫn file: chỉ ra ổ và đường dẫn tới nơi lưu trữ file Excel (D:BC như trong ví dụ này)
Tên bảng tính: gồm tệp mở rông (.xlsx, .xls, hoặc xslm) và luôn được đặt trong ngoặc vuông như [Tổng.xlsx] trong công thức trên
Tên trang tính: nơi chứa ô tham chiếu. Phần này bao gồm tên trang tính và dấu chấm than theo sau (như trong ví dụ này là ‘T1’!)
Ô tham chiếu: chỉ ô hoặc mảng ô tham chiếu trong công thức
Nếu bạn đã tạo tham chiếu tới một bảng tính mở và sau đó đóng nguồn của bảng tính, tham chiếu ngoại sẽ tự động cập nhật bao gồm cả đường dẫn đầy đủ.
Lưu ý: Nếu tên trang tính hoặc tên bảng tính hoặc cả hai chứa kí tự trống hoặc kí tự không phải chữ cái, bạn phải đặt tên hoặc đường dẫn trong dấu nháy. Ví dụ:
=SUM(‘[Doanh số.xlsx]T1’!B2:B5) =SUM (‘[Tổng.xlsx] Tháng 1’!B2:B5) =SUM (‘D:Báo cáo[Doanh số.xlsx] Tháng 1’!B2:B5)
Giống như trường hợp tạo công thức Excel tham chiếu tới trang tính khác, bạn không cần phải đánh tham chiếu tới bảng tính một cách thủ công. Chỉ cần chuyển sang bảng tính khác khi đang nhập công thức và chọn ô hoặc mảng bạn muốn tham chiếu tới. Microsoft Excel sẽ làm phần việc còn lại
Lưu ý: Khi tạo tham chiếu tới trang tính khác bằng cách chọn ô, Excel luôn chèn tham chiếu tuyệt đối. Do đó, nếu bạn định copy công thức mới lập sang các ô khác, hãy chắc rằng kí hiệu dollar ($) được bỏ khỏi ô tham chiếu để đưa tham chiếu về dạng tương đối hoặc hỗn hợp dựa vào mục đích của bạn.
Tham chiếu tới tên trong cùng hoặc khác bảng tínhĐể tạo tham chiếu ngoại gọn hơn, bạn có thể tạo tên trong nguồn trang tính sau đó tham chiếu tới tên đó từ trang tính khác nằm trong cùng hoặc khác bảng tính.
Trong hộp thoại New Name, đánh tên bạn muốn (nhớ rằng kí tự trống không được sử dụng cho tên Excel), và kiểm tra mảng tham chiếu được ghi trong ô Refers to.
Ví dụ đây là cách tạo tên ( Doanh_số_T1) cho ô B2:B5 trong trang tính Tháng 1
Lưu ý: Hình ảnh trên sử dụng Excel 2023 nên giao diện Define Name sẽ khác so với Excel 2013 trở về trước với Refers to tương đương với Select the range of cells, Name tương đương Enter a name for the data range.
Lưu ý: Tên được tạo mặc định với level bảng tính (ghi trong mục Scope tương đương Name in workbook). Bạn cũng có thể lập tên ở level trang tính bằng cách chọn trang tính tương ứng từ danh sách trong Scope (Names in workbook). Đối với tham chiếu Excel, giới hạn tên vô cùng quan trọng bởi nó xác định giới hạn mà tên được sử dụng khi tham chiếu.
Các bạn nên tạo tên ở level bảng tính (trừ khi bạn có lý do để không sử dụng nó) bởi chúng đơn giản hoá việc tạo lập tham chiếu ngoại hơn nhiều so với tên trang tính.
Để tham chiếu tên level bảng tính trong cùng một bảng tính, bạn chỉ cần đánh tên vào tham số của hàm
=Function (tên)
Ví dụ để tìm tổng của các ô nằm trong tên Doanh_số_T1 mà bạn vừa lập xong, sử dụng công thức sau=SUM(Doanh_số_T1)
Để tham chiếu tên level trang tính từ một trang tính khác nằm trong cùng bảng tính, bạn phải đặt tên trước tên trang tính và dấu chấm than đằng sau:
=Function (Tên_trang_tính!tên)
Ví dụ
=SUM (T1!Doanh_số_T1)
Nếu tên trang tính chứa dấu cách hoặc kí tự không phải chữ cái, hãy nhớ đặt chúng bên trong dấu nháy như sau
=SUM(‘Báo cáo T1’!Doanh_số_T1)
Tham chiếu tới tên level bảng tính ở khác bảng tính bao gồm tên bảng tính (gồm tệp mở rộng), dấu chấm than và tên (tên của mảng)
=Function (Tên_bảng_tính!tên)
Ví dụ
=SUM (Tổng.xlsx!Doanh_số_T1)
Để tham chiếu tên level trang tính trong bảng tính khác, tên trang tính và dấu chấm than theo sau cũng phải nằm trong công thức, và tên bảng tính phải đặt trong ngoặc vuông. Ví dụ:
=SUM (‘CDocumentsTổng.xlsx’!Doanh_số_T1)
Nếu bạn đã tạo nhiều tên khác nhau trong trang tính Excel, bạn không cần thiết phải nhớ tất cả tên của chúng. Để chèn tham chiếu tên vào công thức, thực hiện theo các bước sau
Chọn vị trí ô, nhâp dấu (=) và đánh công thức hoặc phép toán
Khi muốn chèn tham chiếu tên, bạn làm như sau
Nếu tham chiếu tên ở level bảng tính nằm trong bảng tính khác, chuyển tới bảng tính đó. Nếu tên nằm trong trang tính mà cùng bảng tính, bỏ qua bước này
Nếu bạn đang lập tham chiếu tới tên ở level trang tính, di chuyển tới trang tính đó (nằm trong cùng hoặc khác bảng tính)
Nhập xong công thức hoặc phép tính và ấn Enter
Như vậy bạn đã biết cách tạo tham chiếu ngoại trong Excel, bạn có thể sử dụng chúng để lấy dữ liệu cho các phép tính dễ dàng hơn.
Cách Sử Dụng Hàm Index Để Lấy Mảng Tham Chiếu Trong Excel
Hàm INDEX sẽ trả về kết quả được tham chiếu đến một phần tử của một hoặc nhiều mảng. Và khi nhìn vào hàm INDEX thì bạn sẽ biết vị trí của phần tử trong mảng được sử dụng.
Cách sử dụng hàm INDEX trong ExcelBây giờ bạn có thể mở Excel lên và sử dụng lệnh =INDEX( thì bạn sẽ thấy 2 cú pháp được hiển thị. Như vậy, trong Excel hàm INDEX có 2 cách sử dụng sau đây:
Cú pháp=INDEX(array; row_num; [column_num])
👉Trong đó:
array: là một mảng giá trị hay một Range các Cells liên tiếp nhau bao gồm hàng và cột.
row_num: là vị trí hàng trong array.
[column_num]: là vị trí cột trong array
Mô tảKhi sử dụng dạng mảng thì hàm INDEX sẽ trả về một phần tử trong mảng, kết quả trả về là của giao điểm row_num và [column_num].
Ví dụ👉Kết quả của hàm trên, mình sẽ có giá trị của giao điểm hàng thứ 2 và cột thứ 6 là Cells F3 trong Range A2:F6.
Cú pháp:=INDEX(reference; row_num; [column_num]; area_num)
👉Trong đó:
reference: tham chiếu đến một hay nhiều Range giá trị.
row_num: là vị trí hàng trong reference.
[column_num]: là vị trí cột trong reference
area_num: vị trị của vùng giá trị trong reference. Nếu trong reference sử dụng nhiều Range thì giá trị area_num sẽ qui định row_num và [column_num] thuộc Range nào.
Mô tả:Dạng tham chiếu của hàm INDEX cho phép chọn nhiều Range nếu các Range đó không liền kề nhau. Kết quả trả về là giao điểm row_num và [column_num] và nằm trong Range số area_num của reference.
1) Sử dụng hàm =INDEX((A2:F4;A7:F8);2;6;2) thì:
reference là (A2:F4;A7:F8)
row_num = 2 (các bạn lưu ý, ở đâu mình lấy area_num = 2 nên row_num = 2 là vị trí hàng thứ 2 trong Range A7:F8.
[column_num] = 6
area_num = 2, tức là Range A7:F8. Trong reference bạn có thể thêm nhiều Range giá trị và Range đầu tiên là 1.
👉Kết quả là giao điểm hàng thứ 2 và cột thứ 6 trong Range A7:F8
2) Ngoài ra bạn có thể kết hợp hàm INDEX với các hàm khác để tính toán trong các Range riêng. Ví dụ: sử dụng hàm =SUM(INDEX((A2:F4;A7:F8);0;6;1)) sẽ tính được tổng giá Range thứ nhất của reference hoặc =SUM(INDEX((A2:F4;A7:F8);0;6;2)) để tính tổng giá Range thứ 2 của reference.
✅ Trong cả 2 dạng của hàm INDEX thì nếu row_num =0 hoặc [column_num]=0 thì hàm INDEX sẽ trả về mảng là toàn bộ mảng của cột hoặc hàng tương ứng. Và nếu sử dụng với hàm SUM() như ví dụ ở trên thì kết quả sẽ là tổng các giá trị trong mảng của cột hoặc hàng.
👉Thông thường nếu bạn sử dụng hàm INDEX mà để đối số row_num =0 hoặc [column_num]=0 thì hàm sẽ trả về kết quả là #VALUE!. Vì vậy sau khi nhập hàm xong bạn sử dụng tổ hợp phím[CTRL + SHIFT + ENTER] thì hàm sẽ trả về giá trị đầu tiên trong mảng.
OK! Qua bài viết về cách sử dụng hàm INDEX thì mình hy vọng sẽ giúp các bạn giải quyết được vấn đề đang gặp phải.
Tạo Một Danh Sách Tùy Chọn Trong Excel: Tĩnh, Động, Từ Một Bảng Tính Khác
Hướng dẫn này trình bày bốn cách nhanh chóng để tạo một danh sách tùy chọn của Excel – dựa trên một danh sách các giá trị, phạm vi của ô, dải ô được đặt tên và một danh sách tùy chọn động. Đồng thời cũng thấy cách để tạo ra một danh sách tùy chọn từ một bảng tính khác, chỉnh sửa và xóa danh sách.
Danh sách tùy chọn của Excel, hoặc danh sách tùy chọn hoặc hộp kết hợp, được sử dụng để nhập dữ liệu vào bảng tính từ danh sách các mục được xác định trước. Mục đích chính của việc sử dụng các danh sách tùy chọn trong Excel là để hạn chế số lượng các lựa chọn có sẵn cho người dùng. Ngoài ra, danh sách tùy chọn ngăn cản lỗi chính tả và làm cho dữ liệu nhập nhanh hơn.
Tạo danh sách tùy chọn có giá trị được phân tách bằng dấu phẩy
Đây là cách nhanh nhất để tạo danh sách tùy chọn chỉ trong 3 bước trên tất cả các phiên bản Excel 2013, 2010, 2007 và 2003.
Chọn một ô hoặc dãy ô cho danh sách tùy chọn của bạn
Bạn bắt đầu bằng cách chọn một ô hoặc nhiều ô mà bạn muốn danh sách tùy chọn xuất hiện. Đây có thể là một ô duy nhất, một dải ô hoặc toàn bộ cột. Nếu bạn chọn toàn bộ cột, một danh sách tùy chọn sẽ được tạo ra trong mỗi ô của cột đó, đó gọi là một trình tiết kiệm thời gian, ví dụ như khi bạn cần tạo bảng câu hỏi.
Sử dụng Excel Data Validation để tạo một danh sách tùy chọn.
Nhập vào mục danh sách và chọn các tùy chọn.
Trong cửa sổ Data Validation, trên tab , hãy thực hiện theo các bước sau:
Trong hộp Source, nhập các mục mà bạn muốn xuất hiện trong danh sách tùy chọn của bạn phân cách nhau bằng dấu phẩy (có hoặc không có dấu cách).
Đảm bảo chọn ô In-cell dropdown.
Theo tùy chọn, chọn Ignore blanks nếu bạn muốn cho phép người dùng để trống ô.
Nhấp OK và bạn đã hoàn tất!
Nếu bạn muốn chỉnh sửa danh sách tùy chọn Excel của bạn trong tương lai, bạn sẽ phải thay đổi tất cả các ô trích dẫn từ Data Validation. Đây không phải là vấn đề lớn nếu bạn chỉ có một vài ô như vậy nằm trên cùng một sheet, nhưng nếu bạn có hàng chục hoặc hàng trăm ô rải rác trên các bảng tính khác nhau, sẽ tốn kha khá thời gian để tìm và chỉnh sửa chúng.
Tạo danh sách tùy chọn của Excel dựa trên một phạm vi được đặt tên
Phương pháp tạo một danh sách tùy chọn trong Excel tốn nhiều thời gian hơn, nhưng về lâu về dài nó có thể tiết kiệm nhiều thời gian hơn.
Nhập các mục cho danh sách tùy chọn của bạn.
Chọn các mục mà bạn muốn xuất hiện trong danh sách tùy chọn của bạn trong một bảng tính hiện có hoặc gõ các mục trong một trang tính mới. Các giá trị này phải được nhập vào một cột hoặc một hàng mà không có bất kỳ ô trống nào.
Ví dụ: hãy tạo danh sách tùy chọn của các thành phần cho công thức nấu ăn yêu thích của bạn:
Tạo một phạm vi được đặt tên.
Bạn thực sự có thể bỏ qua bước này và tạo danh sách tùy chọn dựa trên một dãy ô , nhưng các dải ô được đặt tên thực sự giúp cho việc quản lý các danh sách tùy chọn của Excel dễ dàng hơn.
Chọn tất cả các mục mà bạn muốn đưa vào danh sách tùy chọn, nhấp chuột phải vào chúng, và chọn Define Name, từ danh sách tùy chọn. Ngoài ra, bạn có thể nhấp vào Name Manager trên tab hoặc nhấn .
Trong hộp thoại Name Manager, nhấp vào New…
Trong trường , đặt tên cho mục nhập của bạn, hãy chắc chắn rằng trong hộp Refers to đã hiển thị chính xác vùng địa chỉ của các mục đã nhập, và sau đó nhấp vào Ok. Đảm bảo không có dấu cách hay dấu gạch ngang trong tên của vùng phạm vi bạn vừa đặt, hãy sừ dụng dấu gạch dưới (_) thay thế.
Để thực hiện việc này, bạn có thể nhập =your_table_name [column_name] vào trường , hoặc chọn tất cả các ô trừ ô tiêu đề cột trước khi mở Name Manager và để hộp Refers to tự động xử lý.
Chọn vị trí cho danh sách tùy chọn của bạn
Đơn giản chỉ cần nhấp vào ô mà bạn muốn có danh sách tùy chọn. Điều này có thể nằm trong cùng một bảng, nơi danh sách mục của bạn được đặt hoặc trong một bảng tính khác. Bạn cũng có thể chọn một dải ô, hoặc toàn bộ cột.
Trong cửa sổ Data Validation, hãy chuyển tới tab và chọn các tùy chọn sau:
Trong hộp , nhập vào tên của vùng mà bạn đã đặt ở bước trên, đằng trước đặt thêm dấy bằng, ví dụ =Ingredients.
Đảm bảo chọn ô In-cell dropdown.
Đánh dấu vào Ignore blanks nếu người dùng được phép để ô trống.
Và cuối cùng, nhấp vào nút OK để hoàn tất việc tạo danh sách tùy chọn của bạn.
Bạn bỏ qua bước 2 – tạo một phạm vi được đặt tên .
Trong bước 5 , khi cài đặt danh sách tùy chọn của bạn, thay vì nhập tên của dải ô, hãy nhấp vào biểu tượng Collapse Dialog bên cạnh hộp Source và chọn tất cả các ô có mục nhập bạn muốn đưa vào danh sách tùy chọn của bạn. Chúng có thể ở trong cùng hoặc trong một bảng tính khác. Nếu ở bảng tính khác, bạn chỉ cần đi đến sheet đó và chọn một phạm vi bằng cách sử dụng chuột.
Tạo một danh sách tùy chọn động (tự động cập nhật)
Khóa học Excel nâng cao Hà Nội
Nếu bạn thường xuyên chỉnh sửa các mục trong danh sách tùy chọn, bạn có thể muốn tạo danh sách tùy chọn động trong Excel. Trong trường hợp này, danh sách của bạn sẽ được cập nhật tự động trong tất cả các ô có chứa nó, khi bạn xoá hoặc thêm các mục mới vào danh sách nguồn.
Cách dễ nhất để tạo danh sách tùy chọn được cập nhật tự động trong Excel là tạo một danh sách có tên dựa trên một bảng . Nếu vì lý do nào đó bạn thích một phạm vi tên thông thường, thì tham khảo cách sử dụng công thức OFFSET, như được giải thích bên dưới.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Bạn bắt đầu bằng cách tạo một danh sách tùy chọn thông thường dựa trên một phạm vi được đặt tên như mô tả ở trên.
Trong bước 2, khi tạo một tên, bạn đặt công thức sau vào hộp Refers to.
Trong đó:
Sheet1 – tên của sheet đó
A – cột nơi chứa các mục của danh sách tùy chọn của bạn
$A$1 – ô chứa mục đầu tiên trong danh sách
Như bạn thấy, công thức bao gồm 2 hàm Excel – OFFSET và COUNTA. Hàm COUNTA tính tất cả các ô có ký tự trong cột được chỉ định. OFFSET lấy số đó và trả về một tham chiếu đến một phạm vi chỉ bao gồm các ô có ký tự, bắt đầu từ ô đầu tiên bạn chỉ định trong công thức.
Nếu bạn tò mò muốn có hiểu logic của công thức trên.
Trong Microsoft Excel, hàm OFFSET trả về một tham chiếu đến một dãy bao gồm một số lượng các hàng và cột nhất định. Bạn có thể sử dụng nó trong các trường hợp khác nhau khi bạn cần phải có một phạm vi động, tức là liên tục thay đổi.
OFFSET(reference, rows, cols, [height], [width])
Cú pháp của hàm OFFSET như sau:
reference – ô đầu tiên hoặc một dãy ô liền kề mà bạn muốn căn cứ vào giá trị bù đắp.
rows – số hàng, lên hoặc xuống, mà bạn muốn mà bạn muốn ô ở góc trên bên trái tham chiếu tới
cols – tương tự như các hàng, tức là số cột, sang trái hoặc phải, cho ô phía trên bên trái tham chiếu tới.
height, Tùy chọn – số hàng trong phạm vi trả về.
width, Tùy chọn – số cột trong trong phạm vi trả về.
Vì vậy, trong công thức OFFSET của chúng ta =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1), chúng ta xác định các đối số sau:
Tạo một danh sách tùy chọn từ một bảng tính khác
reference – ô $A$1 trong Sheet1, là mục đầu tiên trong danh sách tùy chọn của bạn;
rows & cols – 0 vì bạn không muốn thay đổi phạm vi trả về theo chiều dọc hoặc chiều ngang;
height – số ô không rỗng trong cột A, được trả về bởi hàm COUNTA;
width – 1, tức là một cột.
Bạn có thể tạo một danh sách tùy chọn trong Excel bằng cách sử dụng một danh sách từ một bảng làm nguồn. Để thực hiện việc này, bạn sẽ phải tạo ra 2 phạm vi được đặt tên – một trong bảng tính nguồn và cái còn lại trong bảng tính mà bạn muốn sử dụng danh sách Data Validation.
Chú ý. Đối với danh sách tùy chọn từ một bảng tính khác để làm việc, bảng tính với danh sách nguồn phải được mở.
Danh sách tùy chọn tĩnh từ một bảng tính khác
Danh sách tùy chọn được tạo theo cách này sẽ không cập nhật tự động khi bạn thêm hoặc xóa các mục trong danh sách nguồn và bạn sẽ phải sửa đổi phần trích dẫn danh sách nguồn theo cách thủ công.
Tạo một phạm vi được đặt tên cho danh sách nguồn
Tạo một vùng được đặt tên trong bảng tính chính
Mở bảng tính mà bạn muốn danh sách tùy chọn xuất hiện và tạo một tên trích dẫn đến danh sách nguồn của bạn. Trong ví dụ này, tham chiếu hoàn thành là = SourceBook.xlsx! Source_list
Danh sách tùy chọn được tạo theo cách này sẽ được cập nhật ngay khi bạn thực hiện bất kỳ thay đổi nào đối với danh sách nguồn.
Data Validation không hoạt động
Tạo một tên phạm vi trong bảng tính Nguồn với công thức OFFSET, như được giải thích trong Tạo một danh sách tùy chọn động .
Trong bảng tính chính, áp dụng Data Validation theo cách thông thường.
Tùy chọn Data Validation có màu xám hoặc không khả dụng? Có một vài nguyên nhân có thể xảy ra:
Các tùy chọn bổ sung cho danh sách tùy chọn của Excel
Danh sách tùy chọn không thể được thêm vào bảng tính được bảo vệ hoặc chia sẻ. Loại bỏ bảo vệ hoặc ngừng chia sẻ bảng tính, và sau đó thử nhấp lại vào Data Validation một lần nữa.
Bạn đang tạo ra một danh sách tùy chọn từ một bảng Excel được liên kết đến một trang SharePoint. Hủy liên kết bảng hoặc xóa định dạng bảng và thử lại.
Nếu bạn muốn hiển thị cho người dùng một thông báo bật lên khi họ nhấp vào bất kỳ ô nào chứa danh sách tùy chọn của bạn, hãy tiếp tục theo cách này:
Hãy đảm bảo chọn Show input message when cell is selected
Nhập tiêu đề và thông báo vào các trường tương ứng (tối đa 225 ký tự).
Nhấp vào nút OK để lưu tin nhắn và đóng hộp thoại.
Cho phép người dùng nhập dữ liệu của họ vào một hộp kết hợp
Tổng hợp thủ thuật Excel hay nhất mọi thời đại
Theo mặc định, danh sách tùy chọn mà bạn tạo ra trong Excel là không thể chỉnh sửa, tức là bị giới hạn trong các giá trị trong danh sách. Tuy nhiên, bạn có thể cho phép người dùng nhập các giá trị của họ vào hộp.
Về mặt kỹ thuật, điều này sẽ biến một danh sách tùy chọn vào một hộp kết hợp Excel. Thuật ngữ “hộp kết hợp” có nghĩa là một danh sách tùy chọn có thể chỉnh sửa cho phép người dùng chọn một giá trị từ danh sách hoặc nhập một giá trị trực tiếp vào hộp.
Chọn hộp kiểm “how error alert after invalid data is entered” nếu bạn muốn hiển thị cảnh báo khi người dùng cố gắng nhập một số dữ liệu vào hộp kết hợp khác với trong danh sách tùy chọn của bạn (điều này không ngăn người nhập Dữ liệu riêng của họ). Nếu bạn không muốn một thông báo xuất hiện, hãy bỏ chọn nó.
Nếu bạn đã chọn hiển thị một thông điệp cảnh báo, hãy chọn một trong các tuỳ chọn từ hộp Stylevà nhập tiêu đề và thông báo. Chọn Hoặc Information hoặc Warning để cho phép người sử dụng nhập văn bản riêng của họ trong hộp combo. Các tùy chọn chỉ khác nhau về biểu tượng được hiển thị cùng với thông báo và nút được chọn theo mặc định.
Một lời nhắn Information sẽ được hiển thị với “i” biểu tượng và nút chọn OK. Nên chọn tùy chọn này được nếu người dùng của bạn phải nhập các lựa chọn của riêng họ khá thường xuyên.
Một lời nhắn Warningsẽ được hiển thị với “!” Biểu tượng và nút “No” được chọn trước. Đây được coi là một cách tốt để hiển thị cảnh báo nếu bạn muốn người dùng của mình chọn một mục từ danh sách tùy chọn thay vì nhập dữ liệu của riêng họ.
Stop sẽ ngăn người khác nhập bất kỳ dữ liệu nào không có trong danh sách tùy chọn Excel của bạn.
Làm thế nào để chỉnh sửa một danh sách tùy chọn Excel
Mẹo. Nếu bạn không biết nhập tiêu đề hoặc tin nhắn nào, bạn có thể để trống. Trong trường hợp này, Microsoft Excel sẽ hiển thị cảnh báo mặc định “The value you entered is not valid. A user has restricted values that can be entered into this cell.”
Nếu bạn đã tạo một danh sách tùy chọn bằng dấu phẩy, hãy tiếp tục các bước sau:
Chọn một ô hoặc các ô trích dẫn danh sách Data Validation Excel của bạn, ví dụ các ô có chứa danh sách tùy chọn mà bạn muốn chỉnh sửa.
Xóa hoặc nhập các mục mới trong hộp Source.
Nhấn OK để lưu các thay đổi và đóng cửa sổ Data Validation Excel .
Nếu bạn đã tạo ra một danh sách tùy chọn bằng cách chỉ định một dãy ô chứ không phải là tham chiếu đến một dải ô được đặt tên, thì tiến hành theo cách sau.
Đi qua bảng tính có chứa các mục xuất hiện trong danh sách tùy chọn của bạn và chỉnh sửa danh sách theo cách bạn muốn.
Chọn ô hoặc ô có chứa danh sách tùy chọn của bạn.
Trong cửa sổ Data Validation Excel , trên tab , thay đổi các tham chiếu ô trong hộp Nguồn. Bạn có thể chỉnh sửa chúng bằng tay hoặc nhấp vào biểu tượng Collapse Dialog.
Nhấp vào nút OKđể lưu các thay đổi và đóng cửa sổ.
Chỉnh sửa danh sách tùy chọn của Excel dựa trên một phạm vi được đặt tên
Mẹo. Nếu bạn muốn áp dụng các thay đổi cho tất cả các ô có chứa danh sách tùy chọn này, hãy chọn tùy chọn “Apply these changes to all other cells with the same settings
Nếu bạn đã tạo một danh sách tùy chọn được đặt tên, thì bạn chỉ có thể chỉnh sửa các mục của phạm vi của mình và sau đó thay đổi trích dẫn đến phạm vi được Đặt tên. Tất cả các danh sách tùy chọn dựa trên phạm vi được đặt tên này sẽ được cập nhật tự động.
Thêm hoặc xóa các mục trong phạm vi được đặt tên. Mở bảng tính chứa phạm vi được đặt tên của bạn, xóa hoặc nhập mới. Hãy nhớ sắp xếp các mục theo thứ tự bạn muốn chúng xuất hiện trong danh sách tùy chọn Excel của bạn.
Thay đổi tham chiếu đến phạm vi được Đặt tên.
Trong cửa sổ Name Manager, hãy chọn phạm vi đã đặt tên mà bạn muốn cập nhật.
Thay đổi tham chiếu trong hộp bằng cách nhấp vào biểu tượng Collapse Dialog và chọn tất cả các mục nhập cho danh sách tùy chọn của bạn.
Nhấp vào nút , sau đó trong thông báo xác nhận xuất hiện, nhấp vào Yesđể lưu các thay đổi của bạn.
Nếu bạn không muốn có danh sách tùy chọn trong bảng tính Excel, bạn có thể xóa chúng khỏi một hoặc tất cả các ô.
Loại bỏ một danh sách tùy chọn từ ô đã chọn
Chọn một ô hoặc vài ô mà từ đó bạn muốn bỏ các danh sách tùy chọn.
Trên tab Cài đặt, chọn nút Clear All.
Bằng cách này, bạn có thể loại bỏ một danh sách tùy chọn từ tất cả các ô liên kết trong bảng tính hiện tại. Thao tác này sẽ không xóa cùng một danh sách tùy chọn từ các ô trong các bảng tính khác, nếu có.
Chọn bất kỳ ô chứa danh sách tùy chọn của bạn.
Trong cửa sổ Data Validation, trên tab , chọn hộp kiểm “Apply these changes to all other cells with the same settings“. Khi đã chọn, tất cả các ô trích xuất danh sách Data Validation Excel này sẽ được chọn.
Nhấp vào nút để xóa danh sách tùy chọn .
Nhấp để lưu các thay đổi và đóng cửa sổ Data Validation.
Hướng Dẫn 3 Cách Tham Chiếu Ngược Từ Phải Qua Trái Trong Excel
Tham chiếu ngược là gì?
Tham chiếu “xuôi” là dạng tham chiếu từ phải qua trái. Đây là dạng thông thường chúng ta gặp phải.
Cột địa chỉ nằm ở phía bên phải của cột Code. Do đó sử dụng hàm vlookup rất dễ dàng.
Tuy nhiên nếu yêu cầu cho 1 địa chỉ, tìm code tương ứng mà vẫn giữ nguyên cấu trúc bảng A1:B9 thì sao?
Tham chiếu ngược với hàm lookupĐây là 1 hàm tham chiếu rất hữu hiệu trong excel nhưng hay bị bỏ quên. Hàm Lookup cho phép chúng ta tham chiếu một cách đa dạng và hiệu quả.
Cách làm như sau:
Xét từng giá trị trong vùng B2:B9 với giá trị tại ô E1. Trong phép so sánh B2:B9=E1, ta có các kết quả TRUE/FALSE. Đem số 1 chia cho biểu thức trên, ta có TRUE=1 và FALSE=#DIV/0. Khi đó các kết quả lỗi chia cho 0 sẽ không được tính, chỉ còn lại giá trị đúng là 1
Tìm kiếm giá trị 1 (hoặc số bất kỳ lớn hơn 0) trong kết quả của phép so sánh 1/(B2:B9=E1)
Với kết quả đúng thu được, trả về giá trị tương ứng ở vùng A2:A9
Hàm được viết như sau:
Hàm LOOKUP và công thức ví dụ trong Excel 2023 2013 2010 2007 2003
Tham chiếu ngược với hàm VLOOKUPVlookup? Vâng, bạn không nghe nhầm đâu. Chúng ta có thể dùng hàm VLOOKUP để tham chiếu từ phải qua trái. Để làm được điều này, chúng ta sẽ kết hợp hàm VLOOKUP với hàm CHOOSE như sau:
Ta có tham số Table Array trong hàm VLOOKUP là:
CHOOSE({1,2},$B$2:$B$9,$A$2:$A$9)
Khi đó:
Cột thứ 1 là vùng B2:B9
Cột thứ 2 là vùng A2:A9
Như vậy hàm Choose tạo ra 1 bảng dữ liệu mới gồm 2 cột, trong đó cột đầu tiên là cột Địa chỉ, cột thứ 2 là cột Code.
Chúng ta sử dụng hàm VLOOKUP để tham chiếu Địa chỉ, lấy kết quả là Code tương ứng.
Tham chiếu ngược với hàm INDEX+MATCHIndex và Match là 2 hàm tham chiếu được kết hợp với nhau để tạo ra phương thức tham chiếu dạng ma trận, là giao điểm giữa dòng và cột. Do đó nó không phụ thuộc vào phương, chiều tham chiếu.
Kết hợp trong hàm Index, chúng ta có vùng A2:A9 chính là cột cần tìm kết quả.
Giao điểm giữa dòng (tìm bởi hàm Match) và cột (quy định trong tham số đầu tiên của hàm index) chính là vị trí kết quả cần tìm.
Cập nhật thông tin chi tiết về Tạo Tham Chiếu Ngoại Trong Excel Để Dẫn Tới Trang Tính/Bảng Tính Khác trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!