Trong hộp Thể loại, hãy bấm vào Chung để áp dụng định dạng số mặc định hoặc bấm vào định dạng ngày, giờ hoặc số mà bạn muốn
Excel cung cấp các tính năng như bộ lọc cho phép bạn ẩn hàng dựa trên giá trị ô. Tuy nhiên, nếu bạn muốn ẩn các cột dựa trên các giá trị ô, thì rất tiếc, không có 'tính năng' hoặc mục trình đơn chuyên dụng nào cho việc đó
Điều đó không có nghĩa là không thể ẩn cột trong Excel dựa trên giá trị ô. Bạn chỉ cần mất thêm một chút nỗ lực để làm điều đó
Nhập macro Excel và VBA
Các macro Excel cùng với VBA cung cấp các công cụ tuyệt vời cho phép bạn làm bất cứ điều gì bạn muốn làm với trang tính Excel của mình. Điều đó bao gồm ẩn và hiện các cột tùy thuộc vào giá trị trong các ô nhất định
Trong hướng dẫn này, chúng ta sẽ xem cách bạn có thể sử dụng Excel VBA để ẩn các cột dựa trên giá trị ô
Trước tiên chúng ta sẽ xem xét một ví dụ trong đó các thay đổi chỉ diễn ra khi bạn chạy macro. Sau đó, chúng ta sẽ xem xét một ví dụ khác trong đó các thay đổi diễn ra trong thời gian thực, bất cứ khi nào giá trị của một ô thay đổi
Mục lục
Ẩn các cột dựa trên giá trị ô khi thực thi Macro
Trong ví dụ này, chúng tôi sẽ chỉ cho bạn cách ẩn tất cả các cột chứa một giá trị cụ thể trong một ô nhất định
Giá trị, dựa vào đó bạn muốn ẩn các cột, có thể là bất kỳ thứ gì bạn thích. Nó có thể là một số, một chữ cái, một từ hoặc thậm chí là một cụm từ
Hãy để chúng tôi sử dụng bộ dữ liệu sau đây để chứng minh
Giả sử bạn có các cột chứa số liệu bán hàng từ Thứ Hai đến Thứ Sáu và bạn muốn chạy macro để ẩn tất cả các cột có chữ X ở hàng 8
Đối với điều này, chúng ta cần một macro sẽ lặp qua từng ô của hàng 8 và ẩn cột tương ứng
Về cơ bản, nó sẽ phân tích từng ô từ A8 đến F8 và điều chỉnh thuộc tính 'Ẩn' của cột mà bạn muốn ẩn
Viết mã VBA
Đây là mã chúng tôi đã sử dụng
Sub HideCols()
Dim cell As Range
For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
If cell.Value = "X" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End SubĐể nhập mã trên, tất cả những gì bạn phải làm là sao chép và dán mã vào cửa sổ nhà phát triển của bạn. Đây là cách
- Từ ruy-băng menu Nhà phát triển, chọn Visual Basic
- Khi cửa sổ VBA của bạn mở ra, bạn sẽ thấy tất cả các tệp và thư mục dự án của mình trong Project Explorer ở bên trái. Nếu bạn không thấy Project Explorer, hãy nhấp vào View->Project Explorer
- Đảm bảo rằng 'ThisWorkbook' được chọn trong dự án VBA có cùng tên với sổ làm việc Excel của bạn
- Nhấp vào Chèn-> Mô-đun. Bạn sẽ thấy một cửa sổ mô-đun mới mở ra
- Bây giờ bạn có thể bắt đầu mã hóa. Sao chép các dòng mã trên và dán chúng vào cửa sổ mô-đun mới
- Trong ví dụ của chúng tôi, chúng tôi muốn ẩn các cột có chứa 'X' ở hàng 8. Nhưng bạn có thể thay thế số hàng từ “8” ở dòng 3 thành số hàng mà bạn định đặt chữ 'X' vào
- Đóng cửa sổ VBA
Ghi chú. Nếu bạn không thể thấy dải băng Nhà phát triển, từ menu Tệp, hãy chuyển đến Tùy chọn. Chọn Tùy chỉnh Ribbon và kiểm tra tùy chọn Nhà phát triển từ Tab chính. Cuối cùng bấm OK
Chạy Macro
Thế là xong, macro của bạn đã sẵn sàng để sử dụng. Bây giờ bất cứ khi nào bạn cần sử dụng nó, bạn chỉ cần chạy nó. Đây là cách
- Đặt X vào hàng 8 cho tất cả các hàng mà bạn muốn ẩn. Chúng tôi muốn ẩn các cột cho Thứ Hai và Thứ Tư (Cột B và D), vì vậy chúng tôi đã thêm dấu X vào các ô B8 và D8
- Chọn tab Nhà phát triển
- Nhấp vào nút Macro (trong nhóm Mã)
- Điều này sẽ mở Cửa sổ Macro, nơi bạn sẽ tìm thấy tên của tất cả các macro mà bạn đã tạo cho đến nay
- Chọn macro (hoặc mô-đun) có tên ‘HideCols’ và nhấp vào nút Chạy
- Bạn sẽ thấy tất cả các cột được đánh dấu X ở hàng 8 bị ẩn (cột B và D)
Giải thích về Bộ luật
Hãy để chúng tôi dành vài phút để hiểu mã này
- Trong dòng 1, chúng tôi đã xác định tên hàm
Sub HideCols()
- Trong dòng 2, chúng tôi đã xác định một biến có tên là ô, có thể tham chiếu đến một phạm vi ô
Dim cell As Range
- Trong các dòng từ 3 đến 7, chúng tôi đã lặp qua từng ô trong hàng “8” của Trang tính đang hoạt động. Nếu ô chứa giá trị “X” thì ta set thuộc tính “Hidden” của toàn bộ cột (tương ứng với ô đó) là True, nghĩa là muốn ẩn toàn bộ cột tương ứng
For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
If cell.Value = "X" Then
cell.EntireColumn.Hidden = True
End If
Next cell- Dòng 8 chỉ đơn giản là phân định ranh giới của phần cuối của quy trình phụ HideCols
End Sub
Bằng cách này, đoạn mã trên ẩn tất cả các cột có chứa 'X' ở hàng 8
Bỏ ẩn các cột dựa trên giá trị ô khi thực thi Macro
Bây giờ chúng ta muốn xem lại các hàng đã ẩn thì phải làm thế nào?
Nó khá đơn giản. Tất cả những gì bạn cần làm là thực hiện một thay đổi nhỏ đối với chức năng HideCols. Lặp lại các bước tương tự như trên để tạo macro mới. Sao chép và dán đoạn mã sau vào nó
Sub UnhideCols()
Dim cell As Range
For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
If cell.Value = "X" Then
cell.EntireColumn.Hidden = False
End If
Next cell
End SubLưu ý tất cả những gì chúng tôi đã làm là thay đổi dòng 5 từ
cell.EntireColumn.Hidden = True
đến
cell.EntireColumn.Hidden = False
Nói cách khác, chúng tôi đặt thuộc tính 'Ẩn' cho cột thành Sai, vì chúng tôi muốn Excel bỏ ẩn (hoặc hiển thị) các cột tương ứng có chứa ký tự 'X' trong hàng 8
Bạn có thể chạy macro này theo cách chính xác như HideCols
Ẩn cột trong thời gian thực dựa trên giá trị ô
Trong ví dụ đầu tiên, các cột chỉ bị ẩn khi macro chạy. Tuy nhiên, hầu hết thời gian, chúng tôi muốn ẩn cột nhanh chóng, dựa trên giá trị trong một ô cụ thể
Vì vậy, bây giờ chúng ta hãy xem một ví dụ khác chứng minh điều này. Trong ví dụ này, chúng tôi có tập dữ liệu sau
Trong tập dữ liệu trên, chúng ta có danh sách tên nhân viên nam và nữ ở 2 cột riêng biệt (A và B). Chúng tôi chỉ muốn hiển thị tên nhân viên nam khi ô E2 chứa giá trị “M”. Nói cách khác, chúng tôi muốn giấu tên của các nhân viên nữ
Tương tự, chúng ta chỉ muốn hiển thị tên nhân viên nữ khi ô E2 chứa giá trị “F”. Nói cách khác, chúng tôi muốn ẩn tên của các nhân viên nam khi điều đó xảy ra
Khi không có gì trong ô E2, chúng tôi muốn hiển thị cả tên nhân viên nam và nữ
Chúng tôi muốn điều này xảy ra trong thời gian thực, mỗi khi giá trị trong ô E2 thay đổi. Đối với điều này, chúng ta cần sử dụng hàm Worksheet_SelectionChange của Excel
Hàm Worksheet_SelectionChange
Thủ tục Worksheet_SelectionChange là một hàm có sẵn trong Excel và được cài đặt sẵn trong trang tính. Nó được gọi bất cứ khi nào người dùng chọn một ô và sau đó thay đổi lựa chọn của mình sang một số ô khác
Vì chức năng này được cài đặt sẵn trong bảng tính, bạn cần đặt nó trong mô-đun mã của trang tính bên phải để bạn có thể sử dụng nó. Trong mã của chúng tôi, chúng tôi sẽ đặt tất cả các dòng của mình bên trong hàm này để chúng được thực thi bất cứ khi nào người dùng thay đổi giá trị trong E2 và sau đó chọn một thứ khác
Viết mã VBA
Đây là mã chúng tôi đã sử dụng
________số 8Để nhập mã ở trên, tất cả những gì bạn phải làm là sao chép và dán mã đó vào cửa sổ nhà phát triển của bạn, bên trong quy trình Worksheet_SelectionChange của trang tính của bạn. Đây là cách
- Từ ruy-băng menu Nhà phát triển, chọn Visual Basic
- Khi cửa sổ VBA của bạn mở ra, bạn sẽ thấy tất cả các tệp và thư mục dự án của mình trong Project Explorer ở bên trái
- Trong Project Explorer, bấm đúp vào tên trang tính của bạn, trong dự án VBA có cùng tên với sổ làm việc Excel của bạn. Trong ví dụ của chúng tôi, chúng tôi đang làm việc với Sheet2
- Điều này sẽ mở ra một cửa sổ mô-đun mới cho trang tính đã chọn của bạn
- Sao chép các dòng mã trên và dán chúng vào cửa sổ mã
- Trong ví dụ của chúng tôi, chúng tôi muốn ẩn cột A khi ô E2 chứa giá trị 'M'. Bạn có thể thay tên cột ‘A’ (ở các dòng 3, 6, 9) bằng tên cột muốn ẩn
- Tương tự, chúng tôi muốn ẩn cột B khi ô E2 chứa giá trị 'F'. Bạn có thể thay tên cột ‘B’ (ở các dòng 4, 7, 10) bằng tên cột muốn ẩn
- Bạn cũng có thể thay giá trị ‘M’ và ‘F’ ở dòng 2 và 5 bằng giá trị bạn cần
- Bạn có thể thay đổi tham chiếu ô “E2” ở dòng 2 và 5 thành tham chiếu đến ô chứa giá trị quyết định của bạn
- Đóng cửa sổ VBA
Chạy Macro
Thủ tục Worksheet_SelectionChange bắt đầu chạy ngay sau khi bạn mã hóa xong. Vì vậy, bạn không cần phải chạy macro một cách rõ ràng để nó bắt đầu hoạt động
Hãy thử gõ chữ 'M' bằng chữ in hoa trong ô E2, sau đó nhấp vào bất kỳ ô nào khác. Bạn sẽ thấy cột B (chứa tên nhân viên nữ) biến mất
Bây giờ hãy thử gõ chữ 'F' viết hoa trong ô E2, sau đó nhấp vào bất kỳ ô nào khác. Bạn sẽ thấy cột A (chứa tên nhân viên nam) biến mất và cột A (chứa tên nhân viên nữ) xuất hiện
Bây giờ hãy thử xóa giá trị trong ô E2 và để trống. Sau đó bấm vào bất kỳ ô nào khác. Bạn sẽ thấy cả hai cột A và B hiển thị trở lại
Điều này có nghĩa là mã đang hoạt động trong thời gian thực khi các thay đổi được thực hiện đối với ô E2
Giải thích về Bộ luật
Hãy để chúng tôi dành vài phút để hiểu mã này ngay bây giờ
- Dòng 1 chứa tiêu đề cho thủ tục Worksheet_SelectionChange. Lưu ý rằng chúng tôi đã chuyển một tham số, Mục tiêu cho quy trình này, thuộc loại dữ liệu Phạm vi. Tham số này đề cập đến Phạm vi thay đổi lựa chọn và nó có thể bao gồm một hoặc nhiều ô
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dòng 2 đến 4 kiểm tra xem giá trị trong ô E2 có bằng 'M' không. Nếu đúng như vậy thì cột B sẽ ẩn và cột A sẽ hiển thị. Nếu ô E2 không chứa giá trị 'M', thì điều khiển sẽ chuyển sang dòng 5
Sub HideCols()
0- Dòng 5 đến 7 kiểm tra xem giá trị trong ô E2 có bằng 'F' không. Nếu có, thì cột A bị ẩn và cột B được hiển thị. Nếu ô E2 không chứa giá trị 'F' hay 'M', thì điều khiển sẽ chuyển sang dòng 8
Sub HideCols()
1- Các dòng 8 đến 10 được thực thi nếu ô E2 không chứa 'F' và 'M'. Điều đó có nghĩa là nếu ô E2 trống, thì các dòng 8 đến 10 được thực thi. Do đó, cả hai cột A và B đều hiển thị
Sub HideCols()
2Theo cách này, đoạn mã trên ẩn cột B (trong khi cột A được hiển thị) khi ô E2 chứa giá trị 'M'. Nó ẩn cột A (trong khi cột B được hiển thị) khi ô E2 chứa giá trị 'F'
Nếu E2 không chứa bất kỳ giá trị nào thì mã sẽ hiển thị cả hai cột A và B
Trong hướng dẫn này, chúng tôi đã chỉ cho bạn cách bạn có thể sử dụng Excel VBA để ẩn các cột dựa trên giá trị ô
Chúng tôi đã làm điều này với sự trợ giúp của hai ví dụ đơn giản – một ví dụ chỉ loại bỏ các cột bắt buộc khi macro được chạy rõ ràng và một ví dụ khác hoạt động trong thời gian thực
Chúng tôi hy vọng rằng chúng tôi đã thành công trong việc giúp bạn hiểu khái niệm đằng sau mã macro VBA để bạn có thể tùy chỉnh và sử dụng nó trong các ứng dụng của riêng mình. Mã hóa vui vẻ
Có một công thức để ẩn các cột trong Excel?
Phím tắt để ẩn cột trong Excel là Ctrl + 0 . Để rõ ràng, phím cuối cùng là số 0, không phải chữ hoa "O". Để ẩn một cột, hãy chọn bất kỳ ô nào trong đó, sau đó sử dụng phím tắt. Để ẩn nhiều cột, hãy chọn một hoặc nhiều ô trong mỗi cột, rồi nhấn tổ hợp phím.
Làm cách nào để ẩn hoặc bỏ ẩn các cột dựa trên lựa chọn thả xuống trong Excel?
Để ẩn cột hoặc hàng, hãy chọn cột hoặc hàng, bấm chuột phải vào chúng và bấm Ẩn
Để bỏ ẩn chúng, hãy chọn cột hoặc hàng ở một bên của các cột hoặc hàng bị ẩn, kéo chuột sang phía bên kia, nhấp chuột phải và nhấp vào Bỏ ẩn
Để ẩn một trang tính, bấm chuột phải vào tab của trang tính, rồi bấm Ẩn
