Sẽ tốt biết bao nếu các ô tự động được lấp đầy? Hầu hết thời gian chúng ta sẽ thích điều đó. Hôm nay chúng tôi sẽ hướng dẫn bạn cách tự động điền ô trong excel dựa trên giá trị từ một ô khác. Đối với phiên này, chúng tôi sẽ sử dụng Excel 2019, vui lòng sử dụng phiên bản ưa thích của bạn.
Điều đầu tiên, hãy tìm hiểu về tập dữ liệu là cơ sở cho các ví dụ ngày nay của chúng ta.
Ở đây chúng tôi có một bảng chứa thông tin của nhân viên như tên, id, địa chỉ, bộ phận tương ứng và ngày gia nhập của họ. Sử dụng dữ liệu này, chúng tôi sẽ thấy cách tự động điền các ô.
Lưu ý rằng đây là tập dữ liệu cơ bản với dữ liệu giả. trong một tình huống thực tế, bạn có thể gặp phải một tập dữ liệu phức tạp và lớn hơn nhiều.
Sách bài tập Thực hành
Bạn có thể tải xuống sách bài tập thực hành từ liên kết bên dưới.
Tự động điền các ô dựa trên một ô khác
Ở đây, chúng tôi làm ví dụ theo cách mà bằng cách cung cấp tên nhân viên, chúng tôi sẽ tự động tìm thấy thông tin của anh ta.
Ở đây chúng tôi đã giới thiệu các trường thông tin được tách ra từ bảng gốc. Giả sử chúng tôi đặt Tên, Robert .
Sau đó, chúng ta sẽ nhận được thông tin chi tiết về Robert . Hãy khám phá cách chúng ta có thể làm điều đó.
1. Sử dụng hàm VLOOKUP
Chỉ trong giây lát, hãy quên “tự động điền” và nghĩ về việc truy xuất dữ liệu phù hợp với các tiêu chí, chức năng nào đang xuất hiện trong tâm trí bạn? Điều đó khá rõ ràng, VLOOKUP là một trong số đó.
VLOOKUP tìm kiếm dữ liệu, được tổ chức theo chiều dọc. Để biết thêm thông tin, hãy xem bài viết VLOOKUP này.
Bây giờ chúng ta sẽ viết một công thức bằng cách sử dụng VLOOKUP hàm sẽ tìm nạp dữ liệu chính xác mà chúng ta muốn trong một ô.
Hãy viết công thức để lấy id của nhân viên
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"")
Trong VLOOKUP , chúng tôi đã chèn tên ( I4) dưới dạng lookup_value . Sau đó, toàn bộ phạm vi bảng dưới dạng lookup_array .
ID nhân viên là cột thứ 2, vì vậy chúng tôi đã đặt 2 là column_num .
Chúng tôi đã sử dụng IFERROR hàm để kết thúc VLOOKUP công thức. Điều này sẽ loại bỏ bất kỳ lỗi nào phát sinh từ công thức (để biết về hàm, hãy truy cập bài viết:IFERROR).
Để lấy được tên bộ phận, chúng ta cần sửa đổi công thức,
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")
Ở đây, chúng tôi đã thay đổi column_num theo vị trí trong bảng gốc. Vụ là cột thứ 3, vì vậy chúng tôi đã sử dụng 3.
Cho Ngày tham gia và Địa chỉ, công thức sẽ là
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"")
Và
=IFERROR (VLOOKUP ($ I $ 4, $ B $ 4:$ F $ 9,5,0), ””)
Chúng tôi đã tìm thấy các chi tiết cho nhân viên. Bây giờ hãy thay đổi tên và các ô sẽ được tự động cập nhật.
VLOOKUP với Danh sách thả xuống
Trước đó chúng tôi đã cung cấp tên theo cách thủ công. Đôi khi việc này có vẻ tốn thời gian cũng như khó hiểu.
Để giải quyết vấn đề, chúng tôi có thể tạo một danh sách thả xuống cho tên nhân viên. Kiểm tra bài viết để biết về cách tạo danh sách thả xuống.
Trong phần Xác thực dữ liệu hộp thoại chọn Danh sách và chèn tham chiếu ô của tên.
B4:B9 là phạm vi chứa các tên.
Bây giờ chúng ta sẽ tìm thấy danh sách thả xuống.
Bây giờ chúng ta có thể chọn tên hiệu quả và nhanh chóng hơn.
Các ô khác đang được điền tự động khi chúng tôi sử dụng VLOOKUP .
2. Sử dụng hàm INDEX - MATCH
Thao tác chúng tôi đã thực hiện thông qua VLOOKUP có thể được thực hiện theo cách khác. Chúng tôi có thể sử dụng kết hợp INDEX-MATCH để tự động điền các ô.
TRẬN ĐẤU định vị vị trí của giá trị tra cứu trong một hàng, cột hoặc bảng. INDEX trả về giá trị tại một vị trí nhất định trong một phạm vi. Để biết thêm, hãy truy cập các bài viết:INDEX, MATCH.
Công thức sẽ là công thức sau
=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")
Ở đây công thức của chúng tôi lấy số id vì chúng tôi đã cung cấp phạm vi id trong INDEX và MATCH hàm cung cấp số hàng, khớp với giá trị tiêu chí trong bảng ( B4:B9 ).
Để lấy được Cục chúng tôi sẽ thay đổi phạm vi trong INDEX và công thức sẽ là công thức sau
=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")
Các phòng ban nằm trong phạm vi D4 tới D9 .
Công thức cho Ngày tham gia sẽ là
=IFERROR(INDEX($E$4:$E$9,MATCH($I$4,$B$4:$B$9,0)),"")
Và cho địa chỉ
=IFERROR(INDEX($F$4:$F$9,MATCH($I$4,$B$4:$B$9,0)),"")
Bây giờ để làm rõ, hãy xóa lựa chọn và chọn bất kỳ tên nào trong số các tên
Bạn sẽ thấy các ô khác được điền tự động.
3. Sử dụng hàm HLOOKUP
Nếu dữ liệu của bạn được định hướng theo chiều ngang thì bạn cần sử dụng HLOOKUP hàm số. Để biết về chức năng, hãy truy cập bài viết này:HLOOKUP.
Tên sẽ được đặt từ danh sách thả xuống. Và phần còn lại của trường sẽ được điền tự động.
Đối với id dẫn xuất, chúng tôi sẽ sử dụng công thức sau
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,2,0),"")
Thao tác tương tự như VLOOKUP công thức. Trong HLOOKUP , chúng tôi đã cung cấp tên là lookup_value và bảng dưới dạng lookup_array . Id nằm ở hàng thứ 2, vì vậy row_num là 2. Và 0 cho kết quả khớp chính xác.
Bây giờ, đối với bộ phận, công thức sẽ là
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,3,0),"")
Vụ là hàng thứ 3, vì vậy row_num là 3 ở đây.
Hãy viết công thức cho ngày tham gia
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")
Ngày tham gia là hàng thứ 4, vì vậy row_num là 4 ở đây. Sau đó, đối với địa chỉ, hãy thay đổi số hàng thành 5.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,5,0),"")
Hãy xóa các ô và chọn một tên từ danh sách thả xuống
Sau khi chọn tên, bạn sẽ thấy các ô khác đang tự động điền.
4. INDEX-MATCH cho các hàng
Chúng tôi cũng có thể sử dụng INDEX MATCH kết hợp cho các hàng. Công thức sẽ là công thức sau
=IFERROR(INDEX($C$4:$H$4,MATCH($C$11,$C$3:$H$3,0)),"") Điều này là để lấy id, vì vậy chúng tôi đã sử dụng C4:H4 trong INDEX , là ID nhân viên hàng.
Thay đổi phạm vi hàng để tìm bộ phận
=IFERROR(INDEX($C$5:$H$5,MATCH($C$11,$C$3:$H$3,0)),"")
Tương tự, thay đổi số hàng cho ngày và địa chỉ tham gia
=IFERROR(INDEX($C$6:$H$6,MATCH($C$11,$C$3:$H$3,0)),"") Đây C6:H6 là Ngày tham gia hàng.
Và C7:H7 là Địa chỉ hàng, vì vậy công thức lấy địa chỉ sẽ giống như công thức được nêu bên dưới
=IFERROR (INDEX ($ C $ 7:$ H $ 7, TRẬN ĐẤU ($ C $ 11, $ C $ 3:$ H $ 3,0)), ”)
Kết luận
Đó là tất cả cho ngày hôm nay. Chúng tôi đã liệt kê một số cách để tự động điền các ô dựa trên một ô khác. Hy vọng bạn sẽ thấy điều này hữu ích. Vui lòng bình luận nếu bất cứ điều gì có vẻ khó hiểu. Hãy cho chúng tôi biết bất kỳ phương pháp nào khác mà chúng tôi đã bỏ qua tại đây.
Bài đọc thêm
- Cách sử dụng Công thức Tự động điền trong Excel (6 Cách)
- Tự động điền ô dựa trên một ô khác trong Excel (5 phương pháp)
- Đánh số tự động trong Excel (9 Cách tiếp cận)
- Cách Tự động điền Số trong Excel (12 Cách)
- Khắc phục:Tính năng Tự động điền của Excel không hoạt động (7 Sự cố)
- Cách Nhập Ngày Tuần tự Trên Nhiều Trang tính trong Excel