Xóa Validation Trong Excel / TOP 5 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

NGUYÊN NHÂN CÓ KIỆN NHẬP DỮ LIỆU NGOÀI Ý MUỐN TRONG EXCEL

Tại sao bạn chưa biết về Data Validation mà nó lại xuất hiện trong file Excel của bạn? Rõ ràng bạn không hề thiết lập nó. Nguyên nhân chủ yếu của việc này là do bạn đã Copy 1 vùng dữ liệu đang áp dụng Data Validation. Sau đó lại dán trực tiếp toàn bộ những gì đã copy sang vị trí mới. Khi đó vị trí mới cũng sẽ mang theo thiết lập Data Validation. Trong một số trường hợp việc copy/paste này làm nội dung thiết lập bên trong Data Validation bị thay đổi, dẫn tới bạn nhập gì cũng sai hết (hoặc cứ báo lỗi mà không biết tại sao lại sai).

CÁCH XÓA BỎ ĐIỀU KIỆN NHẬP DỮ LIỆU TRONG EXCEL

CÁCH 1: TRÁNH LỖI SAO CHÉP MANG THEO DATA VALIDATION

Để tránh mắc phải lỗi trên, khi dán nội dung được copy, bạn hãy chú ý KHÔNG DÁN TRỰC TIẾP BẰNG PASTE THÔNG THƯỜNG (phím tắt Ctrl + V), mà chỉ nên dán Giá trị mà thôi.

CÁCH 2: XÓA BỎ DATA VALIDATION ĐÃ THIẾT LẬP

Để có thể xóa bỏ Data Validation đã thiết lập (hoặc mang theo khi dán nội dung), bạn có thể thực hiện theo các bước sau:

Bước 1: Chọn vùng dữ liệu đang áp dụng Data Validation. Trường hợp không biết rõ phạm vi vùng ô nào, bạn có thể chọn cả cột hoặc tất cả các ô trong Sheet.

Bước 2: Trên thanh công cụ bạn chọn thẻ Data, chọn tiếp Data Validation (trong nhóm Data Tools)

Khi đó bạn có thể nhập dữ liệu bình thường mà không bị quy định bởi Data Validation nữa.

KẾT LUẬN

Việc mắc phải lỗi trên là do bạn còn chưa biết về các công cụ trên Excel thường sử dụng trong công việc. Điều này dẫn tới những khó khăn khi làm việc: làm việc chậm hơn, hay gặp lỗi, phải tìm cách sửa… mà không có tính chủ động. Do đó bạn nên trang bị cho mình những kiến thức về Excel một cách đầy đủ, hệ thống để có thể tự tin và chủ động hơn trong công việc.

Nguồn: https://blog.hocexcel.online/

I. Validation (Ràng buộc dữ liệu)

Khi xây dựng bảng tính trong Excel, một số trường hợp yêu cầu nhập liệu là giá trị cụ thể hoặc trong danh sách sẵn có. Chức năng Data Validation sẽ giúp bạn làm được điều này.

Cửa sổ Dava Validation hiện ra

Whole number: Chỉ cho phép nhập số nguyên (khi chọn chức năng Data xuất hiện cho phép khống chế phạm vi giá trị nhập)

Between: Trong vùng …

Not Between: Ngoài vùng …

Equal to: Bằng

No Equal to: Không bằng

Greater then: Lớn hơn

Less than: Nhỏ hơn

Less than or equal to: <=

Decimal: Chỉ cho phép nhập số nguyên hoặc số thập phân.

List: Chỉ cho phép nhập từ một danh sách có sẵn (danh sách này có thể nhập trực tiếp, từ một vùng trong sheet hoặc từ tên “Name” vùng được gán sẵn). Nên sử dụng chức năng này khi chúng ta cần nhập liệu được lấy từ cơ sở dữ liệu sẵn có nhằm hạn chế sai sót, rút ngắn thời gian.

Date: Chỉ cho phép nhập dữ liệu kiểu ngày. Việc điều khiển về khoảng ngày nhập giống Whole number.

Text length: Cho phép nhập liệu là chuỗi có độ dài xác định.

Thẻ Input Message: Cho phép hiển thị thông tin nhập liệu khi di chuyển chuột vào ô đó, từ đó định hướng cho việc nhập liệu.

Thẻ Error Alert: Xuất hiện hộp thoại cảnh báo nếu nhập liệu không đúng qui định

Mục Style: Shop (Không cho nhập), Warninh (Cảnh báo), Information: (Thông tin)

Title: Gõ tiêu đề hộp thoại cảnh báo.

Error message: Nội dung cảnh báo.

Là vùng mà khi bớt xóa sửa thì các đối tượng có tham chiếu list này sẽ được cập nhật theo

2. Cách tạo

Quét khối dữ liệu có sẵn hoặc để trỏ tại vùng cần tạo List. Vào Insert Table

Cửa sổ Create Table hiện ra

My table has header: Lấy dòng đầu làm tiêu đề.

3.

Table Name: Tên vùng list

Resize Table: Thay đổi địa chỉ vùng list

Remove Duplicates: Loại bỏ các vùng trùng lắp

Convert to Range: Chuyển đổi dữ liệu trong table thành vùng dữ liệu trên sheet

Table Style Options: Các tùy chọn thể hiện thông tin

Table Styles: Các dạng trình bày table

Sau khi tạo list nên đặt tên để sau này cần tham chiếu đến chỉ sử dụng tên thay vì dùng địa chỉ vùng.

CF trong Excel là một công cụ mạnh giúp chúng ta định dạng các chuỗi văn bản trong các ô, các giá trị về màu sắc, kiểu mẫu nền, kẻ khung…

CF khi áp dụng vào các ô nó sẽ đè lên các định dạng thông thường của ô về màu sắc, kiểu thể hiện văn bản và số. Tuy nhiên khi chúng ta xóa bỏ CF thì các các định dạng trước kia của các ô sẽ được phục hồi.

2. Cách tạo

Chọn ô hay vùng muốn áp dụng một CF, sau đó vào Home Style Conditional Formatting và có một số lựa chọn như:

Higlight Cells Rules: Chứa các qui luật định dạng làm nổi các ô chứa giá trị lớn hơn một giá trị xác định, nằm giữa hai giá trị, chứa một chuỗi xác định hoặc các giá trị trùng nhau…

Top/Bottom Rules: Chứa các qui luật định dạng làm nổi các ô chứa nhóm giá trị lớn nhất, nhóm 10% các giá trị lớn nhất và nhóm các giá trị trung bình…

Data Bars: Áp dụng các đồ thị hình thang trực tiếp trong các ô tương ứng với giá trị đang chứa trong ô.

Color Scales: Áp dụng màu nền tương ứng với giá trị chứa trong ô.

Icon Sets: Hiển thị các Icon trực tiếp trong các ô, Các Icon hiển thị phụ thuộc vào giá trị chứa trong ô.

New Rules: Cho phép ta chọn lựa CF khác, bao gồm cả qui luật CF dựa vào kết quả luận lý của công thức.

Clear Rules: Xóa tất cả các CF đang áp dụng cho các ô đang chọn.

Manage Rules: Hiển thị hộp thoại Conditional Formatting Rules Manager. Ở đây bạn có thể tạo mới, chỉnh sửa hoặc xóa các CF.

3. Các qui luật có thể áp dụng với CF tự tạo (New Rules)

Format all cells based on their values: Định dạng theo điều kiện căc cứ vào giá trị số chứa trong các ô bảng tính. Hiển thị các kiểu định dạng như là data bars, color scales hoặc icon sets.

Format only cells that contain: Định dạng theo điều kiện căn cứ vào nội dung chứa trong các ô. Có thể áp dụng các phép so sánh (lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng, bằng, khoảng, ngoài khoảng), các qui luật có thể áp dụng cho các kiểu dữ liệu chuỗi, ngày tháng, ô trống, và các lỗi trong ô.

Format only top or bottom ranked values: Qui luật này chỉ áp dụng định dạng nhóm các ô chứa các giá trị số lớn nhất và nhỏ nhất (tính theo %).

Format only unique or duplicate values: Qui luật này áp dụng cho các ô chứa giá trị số là duy nhất hoặc trùng lắp.

Use a formula to determine which cells to format: Qui luật này áp dụng định dạng dựa trên kết quả luận lý của công thức.

Vd1: Sử dụng Data Bars để vẽ đồ thị để so sánh số lượng học viên tham gia các khóa học.

Chọn vùng C3:C8

Vào Home Conditional Formatting Data Bars

Data Bars chỉ cung cấp 6 màu cơ bản, nếu muốn chọn thêm thì bấm vào More Rules… hộp thoại New Formatting Rule cung cấp thêm một số lựa chọn:

Show bar only: Ẩn các giá trị trong ô.

Type và Value: Để điều chỉnh cách hiển thị của các thanh đồ thị.

B1: Chọn vùng cần định dạng B3:E11

B2: Vào Home Conditional Formatting New Rules

Xuất hiện hộp thoại New Formatting Rule chọn Format only cells that contain.

B3: Tại Format only cells with chọn Cell Value.

Chọn toán tử so sánh là greater than.

Nhập vào giá trị 100 tại hộp thoại bên phải.

Vd3: Tô màu hồng cho các ô có ngày <=01/05/2011

Ở bài trước chúng ta đã học cách tạo drop-down list có giá trị phụ thuộc một list khác bằng INDIRECT trong Data Validation, trong bài này chúng ta có một lựa chọn khác để tạo liên kết List động trong Excel bằng hàm OFFSET thay cho hàm INDIRECT. Trước tiên chúng ta hãy tìm hiểu về cách sử dụng hàm OFFSET

Lưu ý: Để thực hiện theo hướng dẫn bài viết các bạn có thể tải file mẫu TẠI ĐÂY

1. Cách sử dụng hàm OFFSET trong Excel

Hàm Offset có công dụng: Trả về tham chiếu đến một vùng nào đó, cách một ô hoặc một dãy ô một khoảng cách với số dòng hoặc số cột được chỉ định trước. Chúng ta có thể chỉ định số dòng, số cột của vùng tham chiếu trả về.

Cú pháp (syntax): =OFFSET(reference, rows, cols, height, width)

reference: đối số bắt buộc, là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!.

rows:đối số bắt buộc, là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.

cols: đối số bắt buộc, là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.

height: đối số tự chọn, là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.

width: đối số tự chọn, là số cột của vùng tham chiếu cần trả về. Width phải là số dương.

Nếu rows và cols làm cho tham chiếu trả về vượt ra ngoài phạm vi của một worksheet, hàm Offset sẽ báo lỗi #REF!

Nếu bỏ qua height và width, thì height và width sẽ có kích thước mặc định là height và width của reference.

2. Kết hợp OFFSET trong Data validation để tạo List động

Sau khi đã hiểu rõ cách sử dụng hàm OFFSET, bây giờ chúng ta sẽ sử dụng OFFSET thay cho INDIRECT trong Data validation để tạo List động trong Excel.

Ví dụ cho bảng danh sách 1 loạt hãng xe và loại xe như sau

Bước 3: Chọn ô F4 trong thẻ Data chọn Data validation, trong thẻ settings mục Allow chọn List, mục Fomular nhập công thức như sau: =OFFSET($A$3,MATCH(E4,$A:$A,0)-3,1,COUNTIF($A:$A,E4),1)

và nhấn OK, như hướng dẫn ở hình sau:

Giải thích hàm: =OFFSET($A$3,MATCH(E4,$A:$A,0)-3,1,COUNTIF($A:$A,E4),1)

Reference: điểm bắt đầu với tham chiếu là ô A3.

Rows: Sử dụng hàm MATCH để tìm vị trí dòng đầu tiên của tên hãng xe (ô E4) trong danh sách hãng xe ở cột A của sheet, trừ đi 3 cho 2 ô trống A1,A2 và tiêu đề A3.

N hập giá trị là “1” để di chuyển qua bên phải 1 cột (trong ví dụ là cột B).

Height: Sử dụng COUNTIF để đếm số lần xuất hiện của tên hãng xe ở cột A, ví dụ như hãng Mercedes trả về 12, tức là vùng dữ liệu sẽ có chiều cao là 12 dòng.

Width: Vùng dữ liệu rộng 1 cột (cột B)

Sau thao tác này ta được kết quả như sau:

Kết thúc bước 3, ta đã hoàn thành việc tạo danh sách LIST động sử dụng OFFSET trong Data validation và thu được kết quả như hình trên. Đây sẽ là một lựa chọn khác để tạo danh sách động ngoài cách sử dụng INDIRECT trong Data validation.Thực tế, nếu dữ liệu lớn thì việc sử dụng indirect để tạo list trong data validation sẽ tốt hơn cho việc xử lý dữ liệu.

Các ô Excel có thể bao gồm nhiều không gian trống nếu bạn sao chép và dán dữ liệu từ các trang web hoặc các tài liệu khác vào bảng tính của bạn. Tất nhiên, bạn có thể chỉnh sửa thủ công từng ô để xóa khoảng cách trống theo yêu cầu. Tuy nhiên, có thể mất một thời gian nếu có nhiều ô bao gồm khoảng trắng trống rộng. Tốt hơn là xóa khoảng cách trong các ô bằng một số chức năng và công cụ của Excel. Đây là cách bạn có thể loại bỏ dấu cách trong bảng tính Excel bằng TRIM, SUBSTITUTE, công cụ Tìm và Thay thế và tiện ích bổ sung Kutools.

Hàm TRIM

TRIM là một hàm Excel loại bỏ hầu hết khoảng cách từ một chuỗi văn bản ngoại trừ khoảng cách đơn. Vì vậy, đây là một chức năng tốt để loại bỏ tất cả các khoảng trắng đầu và cuối trong các ô. Tuy nhiên, nó không hoạt động với các ký tự khoảng trống ().

Ví dụ, mở một bảng tính Excel trống và nhập ‘5634 525626’ vào ô B2 với hai khoảng trắng phía trước, ba dấu cách giữa các số và hai dấu cách ở cuối. Sau đó nhấp vào ô C2 và nhấn nút fx để mở cửa sổ Chèn Hàm. Chọn TRIM và nhấn OK để mở cửa sổ bên dưới.

Nhấn nút tham chiếu ô của trường Văn bản và chọn B2. Nhấn nút OK để đóng cửa sổ. Điều đó sẽ loại bỏ tất cả các khoảng trắng đầu và cuối và hai khoảng trắng giữa các số.

Để loại bỏ khoảng cách trong nhiều ô cột, bạn có thể sao chép hàm TRIM sang các ô liền kề. Đầu tiên, chọn C2 và nhấn phím tắt Ctrl + C. Sau đó, bạn nên nhấp chuột trái vào góc dưới cùng bên phải của C2 và kéo hình chữ nhật trên các ô khác để sao chép hàm vào chúng như bên dưới.

Hàm SUBSTITUTE

SUBSTITUTE là một hàm tốt hơn nếu bạn cần xóa tất cả khoảng cách từ các ô. Hàm này thay thế các giá trị văn bản hoặc số bằng dữ liệu thay thế, nhưng bạn cũng có thể loại bỏ tất cả khoảng cách trống với nó. Cú pháp SUBSTITUTE là: SUBSTITUTE (văn bản, old_text, new_text, [instance_num]) .

Để thêm SUBSTITUTE vào bảng tính của bạn, chọn ô D2 và nhấn nút fx . Chọn SUBSTITUTE từ cửa sổ Chèn Hàm. Nhấn OK để mở cửa sổ ngay bên dưới.

Nhấn nút tham chiếu ô của trường Văn bản. Chọn ô bạn đã nhập 5634 525626 in, là B2. Sau đó, nhập “” vào ô Old_text và nhập “” vào hộp New_text như được hiển thị trong ảnh bên dưới.

Công cụ Tìm và Thay thế

Tìm và thay thế là một công cụ tương tự như hàm SUBSTITUTE. Nó tìm và thay thế văn bản trong các ô. Bạn cũng có thể xóa hàng đầu, dấu và khoảng cách thừa giữa văn bản trong phạm vi ô được chọn bằng công cụ. Công cụ Tìm và Thay thế lợi thế là bạn có thể xóa các khoảng trống trực tiếp từ các ô mà không thêm các cột bảng tính bổ sung cho các hàm.

Đầu tiên, nhập các số ‘435 789’ vào ô B3 với hai dấu cách đầu và hai dấu cách cộng với ba dấu cách giữa các giá trị. Chọn B3 và nhấn phím nóng Ctrl + H để mở cửa sổ Tìm và Thay thế được hiển thị ngay bên dưới. Thông thường, bạn sẽ nhập văn bản để thay thế trong hộp Tìm gì và sau đó nhập thay thế trong Thay thế bằng. Để xóa khoảng cách khỏi ô đã chọn, nhấn Dấu cách hai lần trong hộp văn bản Tìm gì.

Nhấn nút Thay thế tất cả . Một cửa sổ hộp thoại bật lên thông báo cho bạn biết rằng Excel đã thực hiện một số thay thế. Nhấn OK để đóng cửa sổ đó. Bây giờ B3 sẽ chỉ bao gồm một khoảng trống giữa hai số không có dấu cách đầu hoặc cuối.

Để xóa tất cả khoảng cách giữa các ô bằng công cụ Tìm và Thay thế, chọn B3 lần nữa và nhấn Ctrl + H. Bây giờ, hãy nhập một dấu cách vào hộp văn bản Tìm gì. Trường Thay thế bằng không nên bao gồm bất kỳ khoảng trắng nào. Điều đó sẽ loại bỏ một không gian còn lại trong B3 khi bạn nhấn Replace All .

Xóa Spaces bằng Kutools

Kutools là một trong những tiện ích tốt nhất của Excel, được bán lẻ với giá 39 đô la trên trang web này. Kutools cũng bao gồm công cụ Xóa không gian để xóa khoảng cách ô. Nếu bạn đã cài đặt tiện ích bổ sung đó, hãy chọn một dải ô để xóa khoảng cách. Sau đó nhấp vào tab Kutools trong Excel, Văn bản và chọn Xóa Spaces khỏi trình đơn. Điều đó sẽ mở ra một cửa sổ Hủy bỏ không gian mà từ đó bạn có thể chọn tất cả các không gian để xóa khoảng cách. Ngoài ra, bạn cũng có thể chọn các tùy chọn để xóa hàng đầu, dấu sau hoặc khoảng cách thừa.

Vì vậy, Excel có một vài chức năng, công cụ và tiện ích để loại bỏ khoảng cách từ các ô bảng tính. Chúng chắc chắn sẽ có ích cho việc xóa định dạng không gian khỏi nội dung ô đã được dán. Để biết thêm chi tiết về cách xóa thêm khoảng trống trong ô, hãy xem video YouTube này.