Thảo luận về loạt bài 'Những tuyệt chiêu trong Excel

[TranThanhPhong]

Thảo luận về loạt bài "Những tuyệt chiêu trong Excel"

Xin mời các bạn cùng nhau thảo luận.

Những "tuyệt chiêu" trong Excel

• Lời nói đầu

1. Tùy biến cửa sổ làm việc của bảng tính
   [*]Nhập dữ liệu đồng thời vào nhiều sheet
   [*]Ngăn chận người sử dụng thực hiện một số hành động nào đó nhất định
   [*]Ngăn chặn các nhắc nhở không cần thiết
   [*]Ẩn sheet sao cho người dùng không thể dùng lệnh unhide để hiện ra
   [*]Tự thiết kế một bảng tính mẫu (template)
   [*]Tạo chỉ mục cho các Sheet trong Workbook
   [*]Giới hạn vùng cuộn của bảng tính
   [*]Khóa và bảo vệ những ô có chứa công thức
   [*]Sử dụng định dạng theo điều kiện để tìm dữ liệu trùng
   [*]Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating
   [*]Tạo riêng một thanh công cụ cho riêng một bảng tính cụ thể
   [*]Sao chép công thức giữ nguyên tham chiếu tương đối (giống như sao chép tham chiếu tuyệt đối)
   [*]Gỡ bỏ những liên kết ma
   [*]Giảm kích thước file Excel bị phình to bất thường
   [*]Cứu dữ liệu từ một bảng tính bị lỗi
   [*]Sử dụng Data-Validation khi danh sách nguồn nằm trong một Sheet khác
   [*]Điều khiển Conditional Formating bằng checkbox
   [*]Đánh dấu những ô chứa công thức bằng Conditional Formatting
   [*]Đếm hoặc cộng những ô đã được định dạng có điều kiện
   [*]Tô màu dòng xen kẽ
   [*]Tạo hiệu ứng 3D trong các bảng tính hay các ô
   [*]Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
   [*]Dùng nhiều List cho 1 Combobox
   [*]Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác
   [*]Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn
   [*]Chuyển đổi con số dạng văn bản sang số thực
   [*]Trích xuất dữ liệu số trong 1 chuỗi bằng VBA
   [*]Tùy biến chú thích của ô bảng tính
   [*]Sort thứ tự dựa trên nhiều hơn ba cột
   [*]Sắp xếp ngẫu nhiên
   [*]Thao tác trên dữ liệu với Advanced Filter
   [*]Tạo các định dạng số cho riêng bạn
   [*]Tăng thêm số lần Undo cho Excel
   [*]Tự tạo danh sách để fill
   [*]Làm nổi các Subtotal của Excel
   [*]Chuyển đổi các hàm và công thức trong Excel thành giá trị
   [*]Thêm dữ liệu vào danh sách Validation một cách tự động
   [*]Ngày tháng trong Excel
   [*]Cho phép sử dụng tính năng Group and Outline trên bảng tính bị khoá
   [*]Bẫy lỗi để trống dữ liệu"]Bẫy lỗi để trống dữ liệu
   [*]Giảm danh sách xổ xuống của Validation, sau khi chọn 1
   [*]Thêm các danh sách có sẵn và cả danh sách tự tạo vào menu chuột phải
   [*]Thay địa chỉ dữ liệu bằng tên
   [*]Sử dụng cùng một cái tên cho dãy ở những worksheet khác nhau
   [*]Sử dụng tên để tạo hàm tự tạo
   
2. Tạo dãy có thể mở rộng và thu hẹp
3. Sử dụng dãy động cách linh hoạt nhất
4. Nhận biết tên của dãy trong worksheet
5. PivotTable
6. Chia sẻ PivotTable nhưng không chia sẻ cấu trúc dữ liệu của nó
7. Tự động tạo PivotTable
8. Di chuyển dòng Grand Total của PivotTable
9. Sử dụng PivotTable với dữ liệu từ một workbook khác
10. Cắt rời một "miếng" từ một "chiếc bánh đồ thị" Pie Chart
11. Hai biểu đồ trong một biểu đồ hình bánh
12. Tạo biểu đồ có thể thay đổi theo dữ liệu
13. Sử dụng công cụ điều khiển tự tạo để tương tác với biểu đồ
14. Bốn cách cập nhật biểu đồ nhanh chóng
15. Tạo biểu đồ nhiệt kế
16. Tạo biểu đồ cột có thể tùy biến chiều dài và chiều rộng
17. Tạo biểu đồ đồng hồ đo tốc độ
18. Liên kết chữ trong biểu đồ với ô
19. Để ô rỗng hoặc ô có công thức FALSE không hiển thị trên biểu đồ
20. Thêm mũi tên chỉ hướng cho biểu đồ đường
21. Thêm mũi tên cho trục X
22. Chỉnh lại trục X khi sử dụng dữ liệu ngày tháng
23. Vị trí thông số của trục
24. Biểu đồ cơn lốc
25. Thay thế điểm bằng hình
26. Đánh dấu tên trục theo điều kiện
27. Tạo số tổng cộng cho biểu đồ cột nhiều thành phần (Stacked Column Chart)
28. Thêm mô tả vào công thức của bạn
29. Di chuyển công thức chứa địa chỉ tương đối mà không làm thay đổi tham chiếu
30. Đối chiếu dữ liệu cũ và mới
31. Không bỏ sót ô rỗng

TTP

 

[thanhtu]

Góp ý với loạt bài "Những tuyệt chiêu trong Excel"

Mình có vấn đề cần hỏi về chiêu thứ 24 của anh ptm0412, mọi người giải đáp dùm nha
Trong VD đó anh ptm0412 dùng Combobox trong Control Toolbox, mình cũng thử dùng trong Control Toolbox và thấy các List không hiện hết như trong Form
Mình để Number đến 31, Month đến December thì chỉ list Number đến 12
Mình chưa rõ về vấn đề này lắm và muốn hỏi mọi người xem có cách nào để có thể List hết Number cũng như khi thêm dữ liệu vào các cột.

 

[Tinker_Bell]

Mình có vấn đề cần hỏi về chiêu thứ 24 của anh ptm0412, mọi người giải đáp dùm nha
Trong VD đó anh ptm0412 dùng Combobox trong Control Toolbox, mình cũng thử dùng trong Control Toolbox và thấy các List không hiện hết như trong Form
Mình để Number đến 31, Month đến December thì chỉ list Number đến 12
Mình chưa rõ về vấn đề này lắm và muốn hỏi mọi người xem có cách nào để có thể List hết Number cũng như khi thêm dữ liệu vào các cột.

Nói thiệt, bạn áp dụng công thức thì phải tuỳ biến, không thể bê nguyên xi để đưa vào cách làm của bạn được.

Nhìn vào chiêu thứ 24 mà xem, bạn sẽ thấy cái này - cũng là cái quan trọng nhất:

Ở ô D6 gõ công thức =ADDRESS(1;$F$1)&":"&ADDRESS(7;$F$1)

Như vậy, ở đây combobox chỉ lấy tối đa 7 record (dòng) dữ liệu, bạn lấy đâu ra số 12 ??

Muốn combobox thể hiện hết số record (số dòng) dữ liệu thì hãy sửa số 7 thành số 31 trong công thức tại ô D6.

Còn để linh hoạt hơn, không cố định số record/số dòng, mà tuy thuộc vào độ lớn dữ liệu thì hãy dùng cách sau:

Ở ô D6 gõ công thức =ADDRESS(1;$F$1)&":"&ADDRESS(Counta(A:A);$F$1)

Còn nếu chưa được thì bạn hãy gửi file lên đây. Tui xem thử nha!

 

[cadafi]

To Thanhtu: Tinker_Bell nói đúng đó! Bạn sửa công thức tại ô D6 lại là được. Khi vùng dữ liệu co giãn theo số lượng dòng, thì việc cố định dòng trongcông thức của anh ptm0412 sẽ làm cho bạn nhầm tưởng là công thức sai.

 

[ptm0412]

Bạn Thanh Tu có xem phần bổ sung chưa?

Bổ sung:

Có 1 vấn đề là 3 list không phải lúc nào cũng dài bằng nhau (bằng 7 trong thí dụ trên), thí dụ số từ 1 đến 10, thứ trong tuần từ Chủ nhật đến thứ bảy, tháng từ 1 đến 12. Vậy ta sẽ phải làm gì?

Ta hãy làm từ từ nhé, trước tiên là cách dùng ô phụ (tác giả cũng dùng 2 ô phụ).

Gõ công thức sau vào ô G1:
=COUNTA(INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS(100;$ F$1)))

Với công thức trên, G1 sẽ có các giá trị là 10, 7, 12 tương ứng với F1 là 1, 2, 3.
Sửa tiếp số 7 chết trong công thức ô D6 thành $G$1 cho động:
=ADDRESS(1;$F$1)&":"&ADDRESS($G$1;$F$1)

Kết quả hoàn toàn mỹ mãn. Ô D6 sẽ lần lượt là $A$1:$A$10, $B$1:$B$7, $C$1:$S$12

Và sau đó là phần loại bỏ ô phụ nữa. Làm được rồi nhớ quay lại nhấn thank nha!

 

[thanhtu]

Không nhầm đâu, mình để counta, Combobox trong Form thì list hết nhưng combobox trong control không list hết, mình muốn biết tại sao lại thế?

Rất cám ơn anh pm0412 về bài viết, mình hiểu là Công thức không sai, nhưng không hiểu sao Combobox trong control toolbox lại không list hết, giải thích dùm với nha.

 

[ptm0412]

Quả thực là combobox của control toolbox không tự cập nhật name động như combobox của form.
Cám ơn bạn đã nhận xét và góp ý.

Phạm Thành Mỹ

 

[BNTT]

Bạn vietthoa có góp ý như sau:

Chiêu thứ 9, theo tôi nên làm như sau thì hiệu quả hơn
1/ Ctrl+a
2/format cells ....
3/protection locked chon hidden rồi ok
4/nháy lock cell
5/protect sheet vào password đã song

Thú thật tôi không hiểu lắm.
Đã:

3/protection locked chon hidden rồi ok​

Rồi còn tiếp:

4/nháy lock cell​

Là sao?

Còn câu này:

5/protect sheet vào password đã song​

Không hiểu bạn muốn nói điều gì...

 

[BNTT]

Chiêu số 7, đoạn thứ 2:

Bằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóng và dễ dàng điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ đưa bạn đến chính xác nơi bạn muốn đến mà không có sự fuss. Bạn có thể tạo một chỉ mục trong một vài cách: bằng tay, tự động tạo ra bởi mã VBA, hoặc là sử dụng trình đơn tùy chọn theo ngữ cảnh (thậm chí có thể dùng các hàm Macro4 – không trình bày ở đây).

Xin dịch giả cho biết sự fuss là sự gì ?

 

[hoangvuluan]

ComboBox trong Control với danh sách thay đổi được

Quả thực là combobox của control toolbox không tự cập nhật name động như combobox của form.
Cám ơn bạn đã nhận xét và góp ý.

Phạm Thành Mỹ

Có thể vượt qua được hạn chế này bằng VBA với sự kiện: _GotFocus

 

[ptm0412]

To Hoàng Vũ Luân:
Vấn đề trong bài tuyệt chiêu 24 không phải combobox của control toolbox không cập nhật List Filled Range khi 1 name thay đổi kích thước đơn thuần, mà là không cập nhật khi name thay đổi hẳn 1 Range khác có kích thước khác.
Cám ơn bạn đã đóng góp.

 

[hoangvuluan]

Name động và ComboBox của Control

To Hoàng Vũ Luân:
Vấn đề trong bài tuyệt chiêu 24 không phải combobox của control toolbox không cập nhật List Filled Range khi 1 name thay đổi kích thước đơn thuần, mà là không cập nhật khi name thay đổi hẳn 1 Range khác có kích thước khác.
Cám ơn bạn đã đóng góp.

Với giải pháp gán list cho ComboBox thì dẫu cho name có set sang đâu thì ta vẫn lấy được danh sách tương ứng.
Trong file đính kèm, tôi có thể làm cho combobox chuyển từ 1 cột sang nhiều hơn 1 cột tùy ý

Private Sub ComboBox2_GotFocus()
Dim va()
Dim ra As Range
Set ra = ActiveSheet.Range("data1")
va = ra
With Me.ComboBox2
    .ColumnCount = ra.Columns.Count
    .List() = va
End With
End Sub

 

[ptm0412]

To Hoàng Vũ Luân,
Giải pháp của bạn rất hay, nhưng rất tiếc, tôi không áp dụng được vào file đính kèm của Tuyệt chiêu 24 Dùng nhiều List cho 1 Combobox . Bạn có thể làm giúp không?
Có lẽ tôi sẽ dùng Combobox của Form, tự cập nhật!

 

[hoangvuluan]

Multi list in combobox

Trong file này, tôi tạo lại name MyRange1 để tham chiếu đến 3 vùng tùy theo việc chọn. Khi đó ComboBox sẽ tự động set đến các list tương ứng. Bạn kiểm tra lại nhé!

 

[ptm0412]

Hoàng Vũ Luân cho hỏi thêm: 3 list để ngang hàng (A1, B1, C1, D1) được không? hay bắt buộc phải để so le?

 

[hoangvuluan]

Multi_List

Hoàng Vũ Luân cho hỏi thêm: 3 list để ngang hàng (A1, B1, C1, D1) được không? hay bắt buộc phải để so le?

À đó chỉ là kỹ thuật (mẹo vặt!) để lấy số cột cho nhanh thôi. Về nguyên tắc, thường thì các list này chúng ta đã xác định chính xác số cột, chỉ có số dòng là động. Tuy nhiên nếu muốn thêm điều kiện số cột cũng thay đổi thì có thể nghĩ ra 1 cách gì đó cho nhanh, công thức xác định cho gọn thế thôi.

Chẳng hạn, với bài ví dụ ở trên, nếu đơn giản có thể dùng IF(..=1, ... IF(..=2,...,...)) để tạo ra các vùng tham chiếu cho combo.
Thân!

 

[ngocmaipretty]

Chiêu số 43: Thêm một danh sách tự tạo vào Fill handle:

Đọc hết bài thì tôi hiểu là: sau khi tự tạo 1 danh sách (list), dùng VBA thêm đầu và đuôi của list vào menu chuột phải của ô chọn (trong thí dụ là thêm "A...Z" và những list khác có sẵn của Application).

Và khi nhấn chuột phải vào 1 ô, (có hình minh họa), và chọn 1 trong các list trong menu ngữ cảnh, giá trị đầu của list sẽ được gán vào ô chọn.

Vậy tựa đề của chiêu nên là: "Thêm các danh sách có sẵn và cả danh sách tự tạo vào menu chuột phải"

và câu cuối:

Muốn sử dụng bạn chỉ cần kích phải chuột lên Custom List mình cần và dùng Fill handle để điền các giá trị trong danh sách vào bảng tính

nên là: Muốn sử dụng, bạn chỉ cần nhấn chuột phải vào ô chọn, rồi chọn 1 trong các list hiện ra trong menu ngữ cảnh, bạn sẽ có giá trị thứ nhất của list. Sau đó kéo cái Fill handle để fill list như mọi khi.

Nếu không, tên chiêu là Vô Tướng Kiếp Chỉ nhằm điểm huyệt này, mà khi sử lên thì lại là Nhất Dương chỉ điểm nhằm huyệt khác. Mà lỡ huyệt khác lại ở chỗ .... chỗ khác, thì chết!

 

[TranThanhPhong]

Chiêu số 7, đoạn thứ 2:

Xin dịch giả cho biết sự fuss là sự gì ?

Đính chính chiêu 7:


Xin các bạn đọc lại câu

"Bằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóng và dễ dàng điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ đưa bạn đến chính xác nơi bạn muốn đến mà không có sự fuss."

thành

"... mà không lo bị nhầm lẫn."

TTP.

 

[TranThanhPhong]

Chiêu số 43: Thêm một danh sách tự tạo vào Fill handle:

Đọc hết bài thì tôi hiểu là: sau khi tự tạo 1 danh sách (list), dùng VBA thêm đầu và đuôi của list vào menu chuột phải của ô chọn (trong thí dụ là thêm "A...Z" và những list khác có sẵn của Application).

Và khi nhấn chuột phải vào 1 ô, (có hình minh họa), và chọn 1 trong các list trong menu ngữ cảnh, giá trị đầu của list sẽ được gán vào ô chọn.

Vậy tựa đề của chiêu nên là: "Thêm các danh sách có sẵn và cả danh sách tự tạo vào menu chuột phải"

và câu cuối:

nên là: Muốn sử dụng, bạn chỉ cần nhấn chuột phải vào ô chọn, rồi chọn 1 trong các list hiện ra trong menu ngữ cảnh, bạn sẽ có giá trị thứ nhất của list. Sau đó kéo cái Fill handle để fill list như mọi khi.

Cảm ơn chị ngocmaipretty và các anh chị em thành viên GPE đã góp ý!

Thật sự là khi làm chiêu 43 xong Phong mới thấy tựa đề mà tác giả đặt không sát với những gì nội dung thực hiện, tuy nhiên lúc đó lại không muốn sửa lại. Nội dung chiêu 43 sẽ đổi lại theo góp ý của đọc giả.

Các giai đoạn sau của loạt bài này, các chiêu có thể sẽ được điều chỉnh tựa đề khi cần thiết.

Ngoài ra nhóm biên tập xin có lời mời chị ngocmaipretty (em dịch là ngọc mai xinh đẹp không biết có đúng không nữa) tham gia biên tập tài liệu này vì còn một chặn đường rất dài phía trước (gần 100 chiêu nữa).

Nếu không, tên chiêu là Vô Tướng Kiếp Chỉ nhằm điểm huyệt này, mà khi sử lên thì lại là Nhất Dương chỉ điểm nhằm huyệt khác. Mà lỡ huyệt khác lại ở chỗ .... chỗ khác, thì chết!

Xem ra chị cũng am tường nhiều loại võ công quá nhỉ! Em giã từ VL lâu rồi nên cũng quên rất nhiều chiêu. Hôm nào thọ giáo cao thủ mới được !

TTP

 

[qhthong]

Chiêu thứ 9, Khi copy file (đã protect) sang một máy khác thì chức năng protect bị loại. Vậy để phân phối file này cho người khác xem mà vẫn giữ nguyên những cài đặt của mình thì phải làm sao? Dùng VBA có được không. Câu lệnh tương ứng cho việc protect thế nào? Kính nhờ các cao thủ chỉ giúp