Hướng dẫn tach data validation ra nhieu sheet trong excel năm 2024

Sử dụng nguồn cho danh sách chọn là 1 Table hay 1 cột trong 1 Table nào đó sẽ giúp cho Data Validation trở nên linh hoạt hơn rất nhiều thay vì sử dụng 1 vùng cố định (không phải Table) hay sử dụng công thức phức tạp để làm danh sách chọn động cho Data Validation.

Khi bạn thêm dữ liệu vào trong Table thì dữ liệu cũng sẽ được nhận vào trong danh sách chọn cho bạn và bạn không phải sửa lại nguồn cho Data Validation List.

1.1. Trường hợp Table lấy làm nguồn chỉ có 1 cột

Với trường hợp Table chỉ có 1 cột duy nhất thì bạn chỉ cần tham chiếu trực tiếp tới tên của Table đó luôn. Trong Data Validation -> List, phần Source bạn gõ như sau: \=INDIRECT(“ tên Table”)

Trong đó:

Tên Table là Table mà bạn muốn lấy nó làm nguồn cho danh chọn cho 1 ô hay 1 vùng nhập liệu nào đó. Cụ thể trong trường hợp này Table của mình là Table1 thì mình chỉ cần gõ như sau: \=INDIRECT(“Table1”)

Với trường hợp Table của bạn có nhiều hơn 1 cột (>=2 cột) thì sẽ phải tham chiếu thêm tới tên cột cụ thể.

1.2. Trường hợp Table lấy làm nguồn có nhiều hơn 1 cột

Ví dụ Table1 của mình có 2 cột là HH và DG như sau. Mình muốn làm danh sách chọn cho ô E2 là những đơn giá trong Table1 đó. Và vì Table này có hơn 1 cột nên khi làm danh sách chọn với Data Validation List ta sẽ phải tham chiếu tới cụ thể cột nào đó.

Ta sẽ có công thức chung như sau: \=INDIRECT(“[]”)

Cụ thể ta có công thức tại Source cho mục Data Validation List như sau (chú ý dấu mở đóng ngoặc vuông nhé): \=INDIRECT(“Table1[DG]”)

2. Cách loại bỏ dấu chấm trong số điện thoại

2.1. Sử dụng công cụ Find & Replace

Giả sử mình có danh sách SĐT như sau (đang chứa các dấu chấm) như sau:

Ta cần loại bỏ những dấu chấm trong các số điện thoại. Ta chọn toàn bộ vùng chứa SĐT mà ta cần loại bỏ dấu chấm (ở đây vùng dữ liệu của mình là từ B3:B10.

Với chức năng Find & Replace bạn có thể ấn Ctrl + H để mở hộp thoại này

Trong cửa sổ này, ta nhập như sau:

Find what: nhập dấu chấm

Replace with: để trống (không nhập gì)

Ấn Replace All để thực hiện thay thế tất cả các dấu chấm trong vùng chọn thành rỗng (đồng nghĩa với việc bỏ dấu chấm).

Sau khi ấn Replace All, ta được kết quả như hình trên. Tuy nhiên, bạn có thể thấy số 0 ở trước mỗi đầu số điện thoại đã biến mất, và giờ đây bạn cần thêm số 0 vào trước mỗi số đó thì đơn giản là bạn chỉ cần thực hiện nối số 0 với SĐT đó: 0 &

Nếu như với cách làm này bạn thấy hơi dài dòng và rắc rối thì, với cách dùng hàm Excel sau hy vọng sẽ giúp bạn có được kết quả dễ dàng hơn.

2.2. Sử dụng hàm Excel

Với cách dùng hàm này thì ta sẽ sử dụng hàm SUBSTITUTE, Ta có công thức đơn giản tại ô C3 như sau: \=SUBSTITUTE(B3,”.”,””)

Như vậy bài viết đã hướng dẫn cho bạn cách sử dụng Data Validation List với nguồn là Table và cách loại bỏ dấu chấm trong số điện thoại. Chỉ cần vài thao tác đơn giản, bạn đã có thể sử dụng nguồn Data Validation và loại bỏ dấu chấm trong Excel một cách nhanh chóng rồi. Chúc các bạn thành công!

Hôm nay Bluesofts sẽ hướng dẫn các bạn làm sổ chi tiết động trên Excel một cách nhanh chóng, dễ dàng, chính xác. Đây là một trong những công việc vô cùng quen thuộc của các kế toán viên.

Trong thực tế, khi chúng ta làm sổ chi tiết, chúng ta phải in nhiều đối tượng khác nhau. Theo phương thức Excel truyền thống trước đây, khi kế toán lập sổ cái thường phải làm rất nhiều sheet, mỗi sheet đại diện cho một tài khoản hoặc đối tượng, điều này khiến cho file Excel có dung lượng lớn, trở nên rất cồng kềnh mà lẽ ra với chỉ một sheet làm công thức, ta có thể in cho tất cả các tài khoản và các mã đối tượng.

Để tạo ra một sheet động như trên, ta cần thực hiện các công việc sau:

Thứ nhất, tạo vùng dữ liệu động – Validation List.

Thứ hai, tạo sổ chi tiết (Bài viết này hướng dẫn các bạn cách làm sổ chi tiết với các công thức Excel thường sử dụng.

9. Tạo vùng chọn dữ liệu động – Validation List

Video quay lại nội dung đào tạo Excel Nâng Cao tại Bluesofts. Chi tiết khóa học tại đây

Bước 1: Tạo mục chọn Validation List cho mục “Tài khoản”.

1. Tạo Name

1.1. Ta vào danh mục tài khoản, bôi đen vùng cột mã tài khoản.

1.2. Ta vào “Formulas”, chọn “Define Name”, tại mục “Name” ta đặt tên bảng là MA_TK.

1.3. Nhấn OK để kết thúc quá trình tạo NAME.

2. Tạo Validation List

2.1.Vào sổ cái, đặt con trỏ vào ô cần tạo list, chọn “Data”, chọn tiếp “Data validation”.

2.2.Tại mục “Setting”, tại “Allow” chúng ta chọn “List”.

2.3.Tại phần “Source”, gỗ “=” và tên chúng ta đã tạo hoặc nhấn phím F3 để tìm kiếm nhanh. Tại đây chúng ta chọn “MÃ_TK”.

2.4. Nhấn “OK”

Sau khi xong các thao tác trên, chúng ta đã tạo ra được mục chọn theo danh sách. Ưu điểm của phương pháp là khi chúng ta chọn đối tượng ngoài danh sách, Excel sẽ báo lỗi cho chúng ta, buộc chúng ta phải chọn đúng tên đối tượng.

2.5. Tiếp tục tạo tên tài khoản bằng hàm VLOOKUP() trong đó:

- lookup_value: Giá trị tìm kiếm, là giá trị bạn muốn tra cứu. Ở đây giá trị tìm kiếm là tại ô C4 “Tài khoản”.

- table_array: mảng dữ liệu hay bảng dữ liệu ta muốn tìm giá trị. Ở đây là bảng danh mục tài khoản.

- col_index_num: Chỉ số cột giá trị muốn trả về. Ở đây chúng ta chọn giá trị cột trả về là 2.

- Chọn 0 nếu kết quả trả về chính xác. Chọn 1 kết quả trả về tương đối. Ở đây ta chọn giá trị 0.

Bước 2: Tạo đối tượng chi tiết theo tài khoản

- Điều kiện lấy nguồn

Tài khoản 131 – Lấy ra mã khách hàng.

Tài khoản 141 – Lấy ra mã nhân viên.

Tài khoản 331 – Lấy mã nhà cung cấp.

Theo quy trình ta tạo Name trước:

+ Vào danh mục người bán, tạo tên cho mã người bán.

Bôi đen vùng danh mục người bán, vào menu Excel chọn “Fomulas”.

Chọn “Define Name”, đặt tên vùng là MA_NB.

Tạo tên cho vùng danh mục người bán, đặt tên là DMNB.

+ Tương tự như thế, ta tạo tên cho vùng mã và danh mục khách hàng, đặt tên là MA_KH và DMKH, tạo tên cho mã và vùng danh mục nhân viên, đặt tên là MA_NV và DMNV