Nguyễn Khắc Duy
GPE phát hành sách "Lập trình VBA trong Excel - Phần cơ bản" tái bản năm 2019
SÁCH TẠM HẾT HÀNG: CẬP NHẬT NGÀY 05/01/2021
Giải Pháp Excel xin trân trọng giới thiệu đến các anh chị thành viên quyển sách "Lập trình VBA trong Microsoft Excel - Phần cơ bản" tái bản năm 2019 của tác giả Phan Tự Hướng. Quyển sách này được tác giả hiệu chỉnh, bổ sung và sắp xếp lại từ nội dung quyển sách Lập trình VBA trong Excel đã được tái bản nhiều lần trước nhằm giúp cho người mới học dễ theo dõi và tự học VBA được dễ dàng hơn.
Một số thông tin quyển sách:
Tác giả: ThS. Phan Tự Hướng
Giá phát hành: 179.000
Kích thước: 19x27cm
Ngày phát hành: 1/2019
Số trang: 348 trang
Nhà xuất bản Thông tin và Truyền thông
MỤC LỤC SÁCH:
Chương 1: LÀM QUEN VỚI NGÔN NGỮ VBA
Chương 2: ĐỐI TƯỢNG LẬP TRÌNH VBA TRONG EXCEL
Chương 3: MỘT SỐ ĐỐI TƯỢNG CƠ BẢN TRONG EXCEL
Chương 4: KHAI BÁO VÀ...
Gõ ký tự và Filter
Gõ ký tự và Filter
Bạn có một danh sách rất lớn và bạn cần sử dụng chức năng Filter của Excel để tìm kiếm. Tuy nhiên, thật sự rất mất thời gian khi mỗi lần bạn muốn filter cái gì là bạn phải bấm vào mũi tên rồi gõ vào ô Search.
Tuy nhiên, chỉ cần một vài thủ thuật VBA nho nhỏ là bạn hoàn toàn có thể khiến cho quá trình này trở nên nhanh chóng hơn.
Cách 1: Sử dụng ô làm nơi tìm kiếm
Đầu tiên, tôi sẽ tạo 1 ô để gõ ký tự cần cho Filter. Sau đó, bạn cần chọn Sheet có chứa ô đó, giả sử ở đây của tôi là Sheet1, là bởi vì tôi sẽ sử dụng sự kiện của Sheet. Tiếp theo bạn chọn Worksheet và sự kiện Change giống như hình dưới đây (vùng khoanh màu đỏ):
Kế đến, bạn hãy chép đoạn code này vào:
Bạn có một danh sách rất lớn và bạn cần sử dụng chức năng Filter của Excel để tìm kiếm. Tuy nhiên, thật sự rất mất thời gian khi mỗi lần bạn muốn filter cái gì là bạn phải bấm vào mũi tên rồi gõ vào ô Search.
Tuy nhiên, chỉ cần một vài thủ thuật VBA nho nhỏ là bạn hoàn toàn có thể khiến cho quá trình này trở nên nhanh chóng hơn.
Cách 1: Sử dụng ô làm nơi tìm kiếm
Đầu tiên, tôi sẽ tạo 1 ô để gõ ký tự cần cho Filter. Sau đó, bạn cần chọn Sheet có chứa ô đó, giả sử ở đây của tôi là Sheet1, là bởi vì tôi sẽ sử dụng sự kiện của Sheet. Tiếp theo bạn chọn Worksheet và sự kiện Change giống như hình dưới đây (vùng khoanh màu đỏ):
Kế đến, bạn hãy chép đoạn code này vào:
Mã:
Private Sub Worksheet_Change(ByVal Target...Bài viết: Filter vùng ô đã bị merge nhưng dữ liệu filter vẫn đầy đủ
Filter vùng ô đã bị merge nhưng dữ liệu filter vẫn đầy đủ
Bạn có dữ liệu đã được trình bày rất đẹp như sau:
Mọi thứ đều đẹp, chỉ có điều nó đã bị merge, và điều đó gây rắc rối cho bạn rất lớn, nhất là khi filter. Vì khi filter, Excel chỉ hiểu và chọn duy nhất dòng đầu tiên trong một vùng dữ liệu bị merge tương ứng. Ví dụ bạn nhìn hình trên, BR có 2 mã, nhưng khi filter tôi chỉ thấy được 1 mã.
Lẽ dĩ nhiên, có rất nhiều bài viết khuyên rằng bạn không nên merge cell vì điều đó khiến cho dữ liệu của bạn không được thống nhất, gây rắc rối cho bạn trong việc sử dụng công cụ có sẵn của Excel, cũng như sử dụng công thức hay còn rất nhiều thứ khác nữa. Chẳng hạn như hình dưới đây, khi tôi xài COUNTIF cho BR, tôi chỉ nhận được kết quả 1 dù thực chất chúng ta đều biết đáng...
Bạn có dữ liệu đã được trình bày rất đẹp như sau:
Mọi thứ đều đẹp, chỉ có điều nó đã bị merge, và điều đó gây rắc rối cho bạn rất lớn, nhất là khi filter. Vì khi filter, Excel chỉ hiểu và chọn duy nhất dòng đầu tiên trong một vùng dữ liệu bị merge tương ứng. Ví dụ bạn nhìn hình trên, BR có 2 mã, nhưng khi filter tôi chỉ thấy được 1 mã.
Lẽ dĩ nhiên, có rất nhiều bài viết khuyên rằng bạn không nên merge cell vì điều đó khiến cho dữ liệu của bạn không được thống nhất, gây rắc rối cho bạn trong việc sử dụng công cụ có sẵn của Excel, cũng như sử dụng công thức hay còn rất nhiều thứ khác nữa. Chẳng hạn như hình dưới đây, khi tôi xài COUNTIF cho BR, tôi chỉ nhận được kết quả 1 dù thực chất chúng ta đều biết đáng...
Kết hợp VLOOKUP và INDIRECT trong dò tìm nhiều sheet
Kết hợp VLOOKUP và INDIRECT trong dò tìm nhiều sheet
Đã bao giờ bạn gặp trường hợp giá trị bạn cần có mặt ở nhiều sheet và bạn có nhiệm vụ lấy các giá trị đó để thể hiện trên một sheet Tổng cộng?
Để dễ hình dung, giả sử tôi có dữ liệu chấm công được xuất ra từ hệ thống với cấu trúc ngày tháng năm thể hiện theo từng sheet và cấu trúc dữ liệu của các sheet thì hoàn toàn giống nhau như sau:
Và tôi có một sheet Tổng cộng có cấu trúc sau:
Bạn có thể thấy yêu cầu của bảng trên hình, đó là tôi muốn thấy được thời gian đi làm của từng nhân viên theo từng ngày. Như vậy chúng ta sẽ làm như thế nào?
Một cách phổ biến, đa phần mọi người đều "cam chịu" làm tay theo từng cột. Điều này có nghĩa là, tôi sẽ viết hàm VLOOKUP cho cột D trước như sau:
...
Đã bao giờ bạn gặp trường hợp giá trị bạn cần có mặt ở nhiều sheet và bạn có nhiệm vụ lấy các giá trị đó để thể hiện trên một sheet Tổng cộng?
Để dễ hình dung, giả sử tôi có dữ liệu chấm công được xuất ra từ hệ thống với cấu trúc ngày tháng năm thể hiện theo từng sheet và cấu trúc dữ liệu của các sheet thì hoàn toàn giống nhau như sau:
Và tôi có một sheet Tổng cộng có cấu trúc sau:
Bạn có thể thấy yêu cầu của bảng trên hình, đó là tôi muốn thấy được thời gian đi làm của từng nhân viên theo từng ngày. Như vậy chúng ta sẽ làm như thế nào?
Một cách phổ biến, đa phần mọi người đều "cam chịu" làm tay theo từng cột. Điều này có nghĩa là, tôi sẽ viết hàm VLOOKUP cho cột D trước như sau:
...
Tắt mở Conditional Formatting theo ý bạn muốn
Tắt mở Conditional Formatting theo ý bạn muốn
Conditional Formatting là một công cụ rất mạnh. Nó giúp bạn trực quan hóa dữ liệu của bạn, giúp bạn tìm thấy những điểm khác biệt (như giá trị cao nhất, thấp nhất, giá trị trùng lắp,…) một cách dễ dàng nhờ việc tô đậm giá trị đó bằng font chữ, hay màu nền,…
Tuy nhiên, sẽ có nhiều lúc bạn không cần Conditional Formatting, nhưng không phải là hoàn toàn. Điều này có nghĩa là, có lúc bạn cần bạn bật, có lúc bạn không cần bạn tắt đi. Tuy nhiên, sẽ thật vô lý nếu lúc bạn không cần, bạn xóa Conditional Formatting, lúc cần thì lại ngồi thiết lập lại. Do vậy, Excel cũng cung cấp cho bạn một khả năng bật tắt Conditional Formatting theo ý muốn một cách hoàn hảo.
Để làm được điều này, trước tiên, tôi có dữ liệu như sau:
Bây giờ, tôi sẽ thiết lập Conditional Formatting như bình thường để đánh dấu những môn học của học sinh...
Conditional Formatting là một công cụ rất mạnh. Nó giúp bạn trực quan hóa dữ liệu của bạn, giúp bạn tìm thấy những điểm khác biệt (như giá trị cao nhất, thấp nhất, giá trị trùng lắp,…) một cách dễ dàng nhờ việc tô đậm giá trị đó bằng font chữ, hay màu nền,…
Tuy nhiên, sẽ có nhiều lúc bạn không cần Conditional Formatting, nhưng không phải là hoàn toàn. Điều này có nghĩa là, có lúc bạn cần bạn bật, có lúc bạn không cần bạn tắt đi. Tuy nhiên, sẽ thật vô lý nếu lúc bạn không cần, bạn xóa Conditional Formatting, lúc cần thì lại ngồi thiết lập lại. Do vậy, Excel cũng cung cấp cho bạn một khả năng bật tắt Conditional Formatting theo ý muốn một cách hoàn hảo.
Để làm được điều này, trước tiên, tôi có dữ liệu như sau:
Bây giờ, tôi sẽ thiết lập Conditional Formatting như bình thường để đánh dấu những môn học của học sinh...
Sử dụng Excel như Photoshop – Cách xóa hình nền bằng Excel
Sử dụng Excel như Photoshop - Cách xóa hình nền bằng Excel
Sẽ có nhiều lúc bạn cần xóa đi hình nền của một tấm hình nhằm biến nó thành trong suốt và bạn ước có một phần mềm tinh chỉnh như Photoshop trong tay. Tuy nhiên, không phải máy tính nào cũng cài Photoshop và đôi lúc bạn không nhất thiết phải bật nó, khi mà bạn có thể tận dụng sức mạnh của Excel trong việc chỉnh sửa này.
Để thử, bạn có thể tìm 1 tấm hình, ở đây tôi sẽ dùng logo 10 năm của GPE như sau:
Bạn có thấy chức năng Picture Tools trên thanh Ribbon của Excel không? Mọi thứ bạn cần sẽ ở chỗ này. Đầu tiên hãy chọn bức hình -> chọn Format trên thanh Ribbon. Kế đó, bạn sẽ thấy tại Adjust là nút Color, bạn bấm vào đó và một danh sách sẽ xổ ra.
Bạn có thể tùy biến hình ảnh của mình như thế nào cũng được với chức...
Sẽ có nhiều lúc bạn cần xóa đi hình nền của một tấm hình nhằm biến nó thành trong suốt và bạn ước có một phần mềm tinh chỉnh như Photoshop trong tay. Tuy nhiên, không phải máy tính nào cũng cài Photoshop và đôi lúc bạn không nhất thiết phải bật nó, khi mà bạn có thể tận dụng sức mạnh của Excel trong việc chỉnh sửa này.
Để thử, bạn có thể tìm 1 tấm hình, ở đây tôi sẽ dùng logo 10 năm của GPE như sau:
Bạn có thấy chức năng Picture Tools trên thanh Ribbon của Excel không? Mọi thứ bạn cần sẽ ở chỗ này. Đầu tiên hãy chọn bức hình -> chọn Format trên thanh Ribbon. Kế đó, bạn sẽ thấy tại Adjust là nút Color, bạn bấm vào đó và một danh sách sẽ xổ ra.
Bạn có thể tùy biến hình ảnh của mình như thế nào cũng được với chức...
Đăng ký lớp học Excel, VBA và phân tích dữ liệu tháng 6
LỊCH KHAI GIẢNG
LỚP HỌC EXCEL THÁNG 6
Đặc biệt CHỈ TRONG THÁNG 6:
PIVOTTABLE
PivotTable là một trong những công cụ mạnh nhất và được sử dụng nhiều nhất hiện nay bởi tính năng ưu việt của nó trong việc phân tích và xử...
LỚP HỌC EXCEL THÁNG 6
Đặc biệt CHỈ TRONG THÁNG 6:
- Giảm 20% khi đăng ký các COMBO sau:
- COMBO Phân tích dữ liệu: Làm sạch Dữ Liệu - Phân tích Thống kê cùng Excel
- Giảm 10% cho người giới thiệu khi giới thiệu thêm 1 người cùng tham gia khóa học.
- Giảm 10% khi đăng ký học trọn vẹn hai khóa liên tiếp trở lên và đóng tiền trước.
EXCEL VÀ CÁC CHUYÊN ĐỀ
PIVOTTABLE
- Thời gian : 19h – 21h tối thứ ba, năm, bảy – 12, 14 và 16/6/2018
- Phí tham dự : 170.000/buổi hoặc 340.000/2 buổi hoặc 510.000/3 buổi (được quyền đăng ký theo buổi)
- Người hướng dẫn chính: Hồ Ngọc Phi
PivotTable là một trong những công cụ mạnh nhất và được sử dụng nhiều nhất hiện nay bởi tính năng ưu việt của nó trong việc phân tích và xử...
[Thủ thuật nhanh trong ngày] Thay đổi định dạng của một vài ký tự trong chuỗi trong ô
[Thủ thuật nhanh trong ngày] Thay đổi định dạng của một vài ký tự trong chuỗi trong ô
Rất là dễ dàng khi thay đổi định dạng của cả một ô, bao gồm thay đổi font chữ, màu sắc, thậm chí là chữ nghiêng, chữ đậm hoặc kích thước của chữ. Tuy nhiên, sẽ rất nhiều người thắc mắc, làm cách nào để thay đổi một vài ký tự thay vì toàn bộ nội dung của ô?
Đây là một thủ thuật rất đơn giản nhưng có khá nhiều người không biết, bởi vì chúng ta thường hay sử dụng Excel với đơn vị là ô, và thói quen đó khiến chúng ta không để ý đến cách định dạng tưởng chừng như rất nhỏ này.
Để có thể làm được điều đó, đầu tiên, bạn hãy chọn một ô bạn muốn. Tới đây, bạn hãy nhấn đúp vào ô đó và chọn cụ thể những ký tự mà bạn muốn định dạng.
Bây giờ, bạn hãy định dạng tự do như ý bạn muốn nhé, rất dễ dàng phải không?
...
Rất là dễ dàng khi thay đổi định dạng của cả một ô, bao gồm thay đổi font chữ, màu sắc, thậm chí là chữ nghiêng, chữ đậm hoặc kích thước của chữ. Tuy nhiên, sẽ rất nhiều người thắc mắc, làm cách nào để thay đổi một vài ký tự thay vì toàn bộ nội dung của ô?
Đây là một thủ thuật rất đơn giản nhưng có khá nhiều người không biết, bởi vì chúng ta thường hay sử dụng Excel với đơn vị là ô, và thói quen đó khiến chúng ta không để ý đến cách định dạng tưởng chừng như rất nhỏ này.
Để có thể làm được điều đó, đầu tiên, bạn hãy chọn một ô bạn muốn. Tới đây, bạn hãy nhấn đúp vào ô đó và chọn cụ thể những ký tự mà bạn muốn định dạng.
Bây giờ, bạn hãy định dạng tự do như ý bạn muốn nhé, rất dễ dàng phải không?
...
Dùng Excel để gửi mail trong Lotus Note
Dùng Excel để gửi mail trong Lotus Note
Nếu bạn có một danh sách người nhận mail cùng nội dung cần gửi và bạn muốn dùng Excel để gửi mail tự động cho một danh sách với các nội dung khác nhau thông qua Lotus Note, bài viết này là dành cho bạn.
Giả sử tôi có một danh sách như sau bao gồm tên người nhận, địa chỉ email và địa chỉ lưu file. Mục đích của tôi là muốn gửi 1 bảng lương như một file đính kèm trong mail và nội dung mail để thông báo lương.
Bạn có thể dùng đoạn code sau đây:
Nếu bạn có một danh sách người nhận mail cùng nội dung cần gửi và bạn muốn dùng Excel để gửi mail tự động cho một danh sách với các nội dung khác nhau thông qua Lotus Note, bài viết này là dành cho bạn.
Giả sử tôi có một danh sách như sau bao gồm tên người nhận, địa chỉ email và địa chỉ lưu file. Mục đích của tôi là muốn gửi 1 bảng lương như một file đính kèm trong mail và nội dung mail để thông báo lương.
Bạn có thể dùng đoạn code sau đây:
Mã:
Sub Send_Email_via_Lotus_Notes_Attachment(receiver As String, subject As String, bodyletter As String, stAttachment As String)
Dim obAttachment As Object, EmbedObject As Object
Const EMBED_ATTACHMENT As Long = 1454
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1...Cách chạy macro trong sheet bị protect
Cách chạy macro trong sheet bị protect
Khi cần chia sẻ file cho nhiều người, bạn sẽ muốn protect sheet để tránh cho các thao tác không mong muốn từ người sử dụng. Tuy nhiên, việc này cũng kéo theo macro của bạn không thể chạy được, nếu nó cần tương tác với các ô đã bị khóa. Giả sử, bạn khóa ô A1, nhưng macro của bạn cần ghi giá trị vào ô A1, do đó nó sẽ báo lỗi ngay lập tức.
Vậy làm cách nào để chạy macro này?
Cách thứ nhất
Một cách logic, chúng ta có thể dùng code để unprotect sheet trước, sau đó khi đoạn code bạn cần được thực hiện xong, chúng ta sẽ protect trở ngược lại.
Như bạn thấy, cách này lập tức phát huy hiệu quả, chữ "Giaiphapexcel.com" đã được ghi vào ô A1. Tuy nhiên, cách này tiềm ẩn một rủi ro cao. Giả sử, nếu đoạn...
Khi cần chia sẻ file cho nhiều người, bạn sẽ muốn protect sheet để tránh cho các thao tác không mong muốn từ người sử dụng. Tuy nhiên, việc này cũng kéo theo macro của bạn không thể chạy được, nếu nó cần tương tác với các ô đã bị khóa. Giả sử, bạn khóa ô A1, nhưng macro của bạn cần ghi giá trị vào ô A1, do đó nó sẽ báo lỗi ngay lập tức.
Vậy làm cách nào để chạy macro này?
Cách thứ nhất
Một cách logic, chúng ta có thể dùng code để unprotect sheet trước, sau đó khi đoạn code bạn cần được thực hiện xong, chúng ta sẽ protect trở ngược lại.
Như bạn thấy, cách này lập tức phát huy hiệu quả, chữ "Giaiphapexcel.com" đã được ghi vào ô A1. Tuy nhiên, cách này tiềm ẩn một rủi ro cao. Giả sử, nếu đoạn...
Tra cứu hình ảnh trong Excel, tại sao không?
TRA CỨU HÌNH ẢNH TRONG EXCEL, TẠI SAO KHÔNG?
Người dùng Excel đã quá quen thuộc với các hàm tra cứu như VLOOKUP, INDEX và MATCH để tìm kiếm thông tin từ 1 danh sách. Nhưng làm thế nào để thực hiện việc tra cứu mà kết quả trả về là 1 hình ảnh?
Điều này đòi hỏi thêm một chút sáng tạo, vì 1 VLOOKUP tiêu chuẩn không có khả năng trả kết quả là 1 hình ảnh từ 1 danh sách.
GPE xin giới thiệu với bạn video clip dưới đây để chia sẻ cách tạo ra 1 tra cứu hình ảnh. Làm thế nào để kết quả là 1 hình ảnh của 1 con vật phụ thuộc vào tên con vật được lựa chọn từ 1 danh sách?
Chuẩn bị dữ liệu
Trong ví dụ này, chúng ta có 1 danh sách các con vật và hình ảnh của chúng. 1 điều quan trọng bạn cần lưu ý, đó là hình ảnh các con vật được nằm gọn trong 1 ô. Hình ảnh tuyệt nhất là hình có khoảng trắng giữa viền hình ảnh với viền ô. Nếu bạn để hình sát viền ô như GPE...
Người dùng Excel đã quá quen thuộc với các hàm tra cứu như VLOOKUP, INDEX và MATCH để tìm kiếm thông tin từ 1 danh sách. Nhưng làm thế nào để thực hiện việc tra cứu mà kết quả trả về là 1 hình ảnh?
Điều này đòi hỏi thêm một chút sáng tạo, vì 1 VLOOKUP tiêu chuẩn không có khả năng trả kết quả là 1 hình ảnh từ 1 danh sách.
GPE xin giới thiệu với bạn video clip dưới đây để chia sẻ cách tạo ra 1 tra cứu hình ảnh. Làm thế nào để kết quả là 1 hình ảnh của 1 con vật phụ thuộc vào tên con vật được lựa chọn từ 1 danh sách?
Chuẩn bị dữ liệu
Trong ví dụ này, chúng ta có 1 danh sách các con vật và hình ảnh của chúng. 1 điều quan trọng bạn cần lưu ý, đó là hình ảnh các con vật được nằm gọn trong 1 ô. Hình ảnh tuyệt nhất là hình có khoảng trắng giữa viền hình ảnh với viền ô. Nếu bạn để hình sát viền ô như GPE...
Ctrl + Enter và ứng dụng trong việc loại bỏ dòng trống
Ctrl + Enter và ứng dụng trong việc loại bỏ dòng trống
Bạn đã bao giờ nghe nói tới Ctrl + Enter chưa? Nếu chưa, bạn nên thử nó. Giả sử như, khi bạn nhấn Enter tại A1, thì bạn sẽ được đưa xuống dòng A2 ngay lập tức.
Tuy nhiên, với Ctrl + Enter, bạn sẽ vẫn ở yên tại chỗ mà bạn vừa nhập giá trị, nghĩa là, nếu bạn đang ở A1, thì bạn sẽ ở yên tại đấy.
Nhưng nếu chỉ nói đến đó thì đó vẫn chưa phải tất cả sức mạnh của Ctrl + Enter. Thật sự phím tắt này thật sự hữu ích hơn những gì bạn nghĩ. Nó sẽ giúp bạn có khả năng nhập dữ liệu vào tất cả các ô mà bạn đã chọn ngay lập tức mà nếu bạn dùng Enter, bạn sẽ phải nhập từng ô một.
Ví dụ như, bạn có thể nhập nguyên một mảng hình chữ nhật cùng lúc như sau:
...
Bạn đã bao giờ nghe nói tới Ctrl + Enter chưa? Nếu chưa, bạn nên thử nó. Giả sử như, khi bạn nhấn Enter tại A1, thì bạn sẽ được đưa xuống dòng A2 ngay lập tức.
Tuy nhiên, với Ctrl + Enter, bạn sẽ vẫn ở yên tại chỗ mà bạn vừa nhập giá trị, nghĩa là, nếu bạn đang ở A1, thì bạn sẽ ở yên tại đấy.
Nhưng nếu chỉ nói đến đó thì đó vẫn chưa phải tất cả sức mạnh của Ctrl + Enter. Thật sự phím tắt này thật sự hữu ích hơn những gì bạn nghĩ. Nó sẽ giúp bạn có khả năng nhập dữ liệu vào tất cả các ô mà bạn đã chọn ngay lập tức mà nếu bạn dùng Enter, bạn sẽ phải nhập từng ô một.
Ví dụ như, bạn có thể nhập nguyên một mảng hình chữ nhật cùng lúc như sau:
...