Tổng hợp những "tuyệt chiêu" trong Excel

[ptm0412]

Chiêu 33: Tạo các định dạng số cho riêng bạn (Tiếp theo)

​

Bảng 2.4: Các mã định dạng về thời gian​

​

​

Bảng 2.5: Các mã định dạng khác:​

​

​

Hãy chú ý đến điểm đặc biệt ở mã định dạng cuối cùng trong bảng 2-5: các toán tử so sánh. Giả sử bạn muốn định dạng số là: 0.00_ ;[Red](-0.00) để hiển thị số âm là màu đỏ và trong ngoặc đơn nếu số nhỏ hơn -100. Để làm điều này, bạn làm như sau: 0.00_ ;[Red][<-100](-0.00);0.00​

​

Mã định dạng [Red][<-100](-0.00) được đặt trong phần số âm tạo nên kết quả này. Việc sử dụng phương pháp này kết hợp với định dạng có điều kiện bạn có thể nhân đôi số điều kiện định dạng có điều kiện từ 3 lên đến 6 điều kiện.​

​

Thường người sử dụng muốn hiện thị giá trị dollar ở dạng chữ. Để làm điều này, ở khung type của Format cells/number/custom, bạn gõ: 0 "Dollars and" .00 "Cents"​

​

Định dạng này sẽ ảnh hưởng đến một số được nhập vào như 55.25 được hiển thị là "55 Dollars and .25 Cents". Nếu bạn muốn đổi số thành dollars và cents, hãy tham khảo thêm hai hàm tự tạo từ link sau của Microsoft: http://www.ozgrid.com/VBA/ValueToWords.htm and http://www.ozgrid.com/VBA/CurrencyToWords.htm.​

​

Bạn cũng có thể sự dụng một định dạng riêng để hiển thị các từ như : Low, Average, hay High cùng với số được gõ vào. Đơn giản chỉ việc sử dụng mã định dạng sau: [<11]"Low"* 0;[>20]"High"* 0;"Average"* 0​

​

Hãy chú ý đên việc sử dụng dấu "*". Dấu này sẽ lặp lại các ký tự tiếp theo trong định dạng để điền đầy ô theo độ rộng cột, nghĩa là tât cả các từ Low, Average, or High sẽ bị dồn về bên phải, trong khi số sẽ bị dồn về bên trái.​

​

(Hết)​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[BNTT]

Chiêu 34: Tăng thêm số lần Undo cho Excel

Tất cả chúng ta đều đã quen thuộc với chức năng Undo của Excel, cho phép chúng ta làm lại những sai lầm của mình. Tuy nhiên, mặc định, chúng ta chỉ có thể Undo được 16 lần. Chiêu này giúp bạn có thể tăng số lần Undo lên nhiều hơn, có thể đến 100 lần.​

​

Khi bạn sử dụng chức năng Undo của Excel, và bạn đã thực hiện Undo 16 lần, thì nút Undo bị mờ đi, không thể Undo được nữa. Ngoài ra, khi bạn nhấn nút Save để lưu bảng tính, thì nút Undo cũng bị mờ đi, và danh sách những tác vụ (những hành động đã thực hiện trên bảng tính) mà bạn đã thực hiện bị mất sạch. Đó là do khi bạn lưu bảng tính, Excel cho rằng bạn đã hài lòng với bảng tính này (thì mới nhấn Save), và nó thấy rằng không cần thiết phải giữ lại danh sách những tác vụ trước khi lưu nữa.​

​

Bạn có thể thấy rằng, quay lui lại 16 lần là không đủ, nhưng làm cách nào để tăng số lần này lên? Nghĩa là làm cách nào để tăng danh sách các tác vụ của bạn lên? Thưa rằng, có cách, và bạn có thể tăng con số này lên đến 100 lần.​

​

Để làm điều này, trước hết, bạn hãy thoát khỏi Excel. Sau đó bạn chạy lệnh Run của Windows (Start | Run, hoặc nhấn phím Window + R), gõ vào đó Regedit.exe và nhấn OK. ​

​

​

Khi bạn đã mở được chương trình Regedit, hãy tìm đến khóa:​

HKEY_CURRENT_USER \ Software \ Microsoft \ Office \ 11.0 \ Excel \ Options ​

Con số 11.0 ở trên đây có thể khác, ví dụ với Excel2000 thì nó là 10.0, với Excel 2007 thì nó là 12.0​

​

​

​

Nhấn Edit | New | DWORD Value (xem hình). Một mục mới New Value #1 sẽ được tạo ra trong khung bên phải, bạn nhập vào đó chữ UndoHistory, và nhấn Enter.​

​

​

​

Nhấn đúp (double-click) vào mục UndoHistory mới tạo, rồi gõ vào hộp thoại mở ra một con số bất kỳ, miễn là lớn hơn 16 và nhỏ hơn 100. Nhấn OK, rồi thoát Regedit.​

​

​

Bạn hãy khởi động lại Excel và thử làm gì trong đó, rồi nhấn vào nút Undo xem bạn có thể Undo được bao nhiêu lần nhé. Hy vọng rằng Undo 100 lần, đã là quá đủ cho bạn. Tuy nhiên, nếu bạn nhấn lưu bảng tính, thì danh sách các tác vụ có thể Undo vẫn mất.​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[ptm0412]

Chiêu 35: Tự tạo danh sách để fill

​

Bằng cách tự tạo 1 danh sách, bạn có thể chỉ cần gõ 1 giá trị đầu, sau đó fill xuống hoặc fill ngang bằng cái Fill Handle, danh sách sẽ được tự động điền vào tuần tự.​

Excel cung cấp cho bạn 1 khả năng tiết kiệm đáng kể thời gian cho việc nhập liệu, không chỉ cho số mà cả cho chuỗi. Nó có tạo sẵn cho bạn vài danh sách: tháng trong năm, thứ trong tuần. Bạn chỉ cần gõ 1 từ đầu (có trong list), rồi kéo cái Fill Handle, danh sách sẽ tự động được điền vào với những giá trị kế tiếp của list. Thí dụ bạn gõ Tue, và fill xuống, các ô kế sẽ tự động được điền là Wed, Thu, Fri, Sat, Sun, Mon, và trở lại Tue nếu còn tiếp.​

​

Bạn hoàn toàn có thể tự tạo 1 list riêng của bạn để dùng sau này. Một cách đơn giản để tạo list là gõ danh sách bạn cần vào các ô. Thí dụ bạn cần danh sách các tháng trong năm bằng tiếng Việt. Vậy bạn hãy gõ vào các ô từ A1 đến A12:​

​

​

Rồi chọn trong Office button ➝ Excel Options ➝ Popular ➝ Edit Custom Lists (đối với Excel trước 2007, Tools ➝ Options ➝ Custom Lists), trong vùng Import List in cells, chọn $A$1:$A$12. Nhấn Import và nhấn OK.​

​

​

Một khi bạn đã tạo 1 list như vậy, chỉ cần gõ vào 1 ô với 1 giá trị trong list, rồi nắm kéo cái fill Handle, bạn sẽ được kết quả:​

​

​

Bạn cũng có thể fill giá trị ngược chiều nếu bạn gõ 2 giá trị, 1 nằm dưới trong danh sách, 1 nằm kế trên trong danh sách, tô chọn cả 2 ô rồi Fill:​

​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[TranThanhPhong]

Chiêu thứ 36: Làm nổi các Subtotal của Excel

Khi làm việc với subtotal trong Excel bằng lệnh Data ➝ Outline ➝ Subtotal, các dòng subtotal rất khó phân biệt với các số liệu khi có nhiều cột dữ liệu. Cụ thể là, các cột subtotal xuất hiện bên phải tập dữ liệu trong khi tiêu đề của nó thường lại nằm ở cột đầu tiên và các subtotal lại không được định dạng in đậm như các tiêu đề nên gây khó khăn cho việc đọc các kết quả. Trong phần này sẽ hướng dẫn bạn các cách sử dụng định dạng theo điều kiện để định dạng các subtotal sao cho dễ phân biệt và dễ đọc hơn.

Trước tiên, chúng ta cần có tập số liệu để thực hành. Các bạn nhập vào các số liệu như hình sau:

Tiếp theo là các bạn hãy tính subtotal cho từng Region bằng cách đặt ô hiện hành vào dùng số liệu và vào Data ➝ Outline ➝ Subtotal (E2003: Data ➝ Subtotals). Hộp thoại Subtotal xuất hiện, bạn thiết lập các tuỳ chọn như hình sau và nhấn nút OK.

Trong hình trên, bạn thấy rằng các tiêu đề subtotal của từng Region tại cột A được in đậm nhưng các giá trị subtotal bên cột B thì không. Đối với tập số liệu trên thì cũng không quá khó để đọc các kết quả, tuy nhiên chúng ta sẽ khó nhận biết ngay các subtotal khi có nhiều cột số liệu hơn.

Chúng ta sẽ dùng định dạng theo điều kiện để định các các giá trị subtotal in đậm một cách tự động và bạn phải thực hiện điều này trước khi dùng lệnh subtotal. Do vậy nếu đã thực hiện lệnh subtotal thì bạn hãy xoá bỏ nó theo cách sau: chọn ô hiện hành trong vùng kết quả subtotal và vào Data ➝ Outline ➝ Subtotal ➝ chọn Remove All.

Sau đó chọn vùng A1:B16, lưu ý để ô hiện hành tại tại ô A1, sau đó vào Home ➝ Styles ➝ Conditional Formatting ➝ New Rules… ➝ Use a formula to determine which cells to format (E2003: Format ➝ Conditional Formatting…➝ Formula Is) và nhập vào công thức sau tại hộp “Format values where this formula is true”:

=Right($A1,5)=”Total”

Lưu ý phải cố định cột A (thêm $ vào trước A) và dùng tham chiếu tương đối cho dòng. Nhấn nút Format… ➝ vào hộp Format Cells ➝ chọn ngăn Font ➝ chọn Bold tại Font Style và chọn màu đỏ tại Color ➝ nhấn OK ➝ nhấn tiếp OK để trở về bảng tính.

Tiếp theo, bạn chọn một ô nào đó trong vùng số liệu và vào Data ➝ Outline ➝ Subtotal ➝ để các tuỳ chọn mặc định ➝ OK. Kết quả như hình sau:

Do bạn chọn ô A1 là ô mặc định và sau khi áp dụng định dạng theo điều kiện, Excel sẽ thay đổi địa chỉ tham chiếu trong công thức định dạng trên cho các ô trong vùng chọn. Ví dụ như tại ô A2 và B2 sẽ có công thức định dạng theo điều kiện là =Right($A2,5)=”Total” và các ô A3, B3 sẽ là =Right($A3,5)=”Total”. Khi công thức trong ô nào trả về True thì ô đó sẽ được áp dụng định dạng theo thiết lập trong lệnh định dạng theo điều kiện.

Cải tiến định dạng cho subtotal

Các định dạng theo hướng dẫn trên sẽ như nhau cho các dòng có tính subtotal, do vậy cũng sẽ khó phân biệt dòng Grand Total với các dòng subtotal khác. Phần này, chúng ta cũng sẽ dùng định dạng theo điều kiện để làm cho chúng có định dạng khác nhau.

Trước tiên, bạn phải xoá định dạng theo điều kiện ở phần trước bằng cách đặt ô hiện hành trong vùng số liệu rồi vào Home ➝ Styles ➝ Conditional Formatting ➝ Manage Rules ➝ chọn Rule và nhấn nút Delete Rule.

Bạn phải xoá vùng kết quả Subtotal tương tự như hướng dẫn ở phần trước. Sau đó, bạn chọn vùng A1:B16, nhớ chọn ô A1 là ô hiện hành rồi vào Home ➝ Styles ➝ Conditional Formatting ➝ Manage Rule ➝ New Rule ➝“Use a formula to determine which cells to format” (E2003, Format ➝ Conditional Formatting… ➝ Formula Is) ➝ nhập công thức sau tại hộp “Format values where this formula is true”

=$A1=”Grand Total”

Nhấn nút Format ➝ chọn ngăn Font ➝ chọn Bold tại Font Style ➝ chọn màu xanh tại Color ➝ nhấn OK. Tiếp tục, bạn nhấn nút New Rule (E2003: nhấn Add) ➝ chọn “Use a formula to determine which cells to format” ➝nhập công thức sau tại hộp “Format values where this formula is true”:

=Right($A1,5)=”Total”

Nhấn nút Format ➝ chọn ngăn Font ➝ chọn Bold Italic tại Font Style ➝ chọn Single tại Underline ➝ nhấn OK ➝ OK. Nhấp tiếp OK để trở lại màn hình bảng tính.

Trong Excel 2007, Rule thêm vào trước sẽ nằm dưới cùng. Do vậy bạn chọn Rule tạo đầu tiên =$A1=”Grand Total” và nhấn Move Up để đưa nó lên đầu tiên trong danh sách các Rule.

Cuối cùng, bạn vào Data ➝ Outline ➝ chọn Subtotal ➝ nhấn OK và xem kết quả như hình sau:

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[ptm0412]

Chiêu 37: Chuyển đổi các hàm và công thức trong Excel thành giá trị.

Hầu hết các bảng tính Excel đều chứa các công thức. Thỉnh thoảng bạn chỉ muốn hiện lên kết quả của một công thức trong một ô, thay vì để công thức ở đó, vì nó sẽ thay đổi khi dữ liệu mà nó tham chiếu đến thay đổi.​

​

Bạn có thể làm điều này bằng tay theo hai cách hoặc bạn có thể sử dụng một macro mà sẽ làm cho công việc nhẹ đi. Trước hết hãy xem các phương pháp bằng tay.​

​

1. Dùng Paste Special​

​

Bạn có thể sao chép các kết quả của các công thức và vẫn để lại công thức tại ô gốc bằng việc sử dụng công cụ Paste Special của Excel. Giả sử bạn có các công thức ở trong vùng A1:A100. Chọn vùng này, chọn lệnh copy (bạn có thể làm điều này bằng cách chọn các lựa chọn trong Clipboard ở tab Home hoặc nhấn chuột phải), sau đó chọn ô bắt đầu để dán kết quả (giả sử ô B1). Chọn Clipboard ➝ Paste ➝ Paste Values (hoặc nhấn phải chuột và chọn Paste Special ➝ Values hoặc dùng phím tắt Alt+E+S+V) và nhấn OK. Lúc này bạn sẽ thấy vùng B1:B100 sẽ là các kết quả của công thức ở các ô trong vùng A1:A100 nhưng chỉ là các giá trị.​

​

Nếu bạn muốn chép đè các công thức gốc với kết quả của chúng, chọn dãy công thức và chọn Copy. Vẫn ở dãy công thức được chọn đó, chọn Paste ➝ Paste Values (hoặc click phải chuột và chọn Paste Special ➝ Values hoặc dùng phím tắt Alt+E+S+V), sau đó click OK.​

​

2. Sử dụng "Copy Here As Values Only"​

​

Bạn cũng có thể sao chép các kết quả của công thức mà vẫn để lại các công thức ở ô gốc bằng cách sử dụng một menu ẩn mà thậm chí nhiều người dùng không biết sự tồn tại của nó. Chọn dãy công thức, nhấn phải chuột ở đường biên bên phải hoặc bên trái vùng chọn (nói cách khác là bạn có thể click chuột phải ở bất kỳ đường biên nào của vùng chọn ngoại trừ việc bạn sử dụng nút "fill handle" có hình dấu thập). Trong lúc nhấn giữ chuột phải, kéo chuột đến nơi cần dán, nhả chuột phải và nhấn "Copy Here as Values Only" từ một menu xổ xuống.​

​

Bạn cũng có thể chép đè lên các công thức với kết quả của chúng. Chọn dãy có công thức, sau đó nhấn phải chuột ở đường biên bên phải hoặc bên trái vùng chọn (nhắc lại một lần nữa là bạn có thể click chuột phải ở bất kỳ đường biên nào của vùng chọn ngoại trừ việc bạn sử dụng nút "fill handle" có hình dấu thập). Trong khi nhấn phải chuột (hoặc nhấn Ctrl), kéo chuột qua một cột bên phải hoặc bên trái và sau đó quay về dãy ban đầu, nhả chuột phải và nhấn "Copy Here as Values Only" từ kết quả của một một menu xổ xuống.​

​

​

​

3. Sử dụng Macro​

​

Nếu bạn thường xuyên chuyển đổi các ô có công thức hoặc hàm thành giá trị thì bạn có thể sử dụng macro đơn giản như sau:​

​

Sub ValuesOnly( )[/COLOR][/INDENT]
[COLOR=Navy]
[INDENT]Dim rRange As Range[/INDENT]

[INDENT]On Error Resume Next[/INDENT]

[INDENT]Set rRange = Application.InputBox(Prompt:="Select the formulas", _[/INDENT]

[INDENT]Title:="VALUES ONLY", Type:=8)[/INDENT]

[INDENT]If rRange Is Nothing Then Exit Sub[/INDENT]

[INDENT]rRange = rRange.Value[/INDENT]

[INDENT]End Sub

​

​

Để sử dụng macro này, chọn Developer ➝ Code ➝ Visual Basic (với phiên bản trước Excel 2007, chọn Tools ➝ Macro ➝ Visual Basic Editor) hoặc nhấn Alt + F11 để chèn một module chuẩn. Sao chép và dán đoạn code ở trên trực tiếp vào module.​

​

Nhấn chọn nút Close của cửa sổ code hoặc nhấn Alt + Q để quay trở lại bảng tính Excel. Chọn Developer ➝ Code ➝ Macros (với phiên bản trước Excel 2007, chọn Tools ➝ Macro ➝ Macros) hoặc nhấn Alt+F8, chọn ValuesOnly, sau đó click chọn nút Options, ở shortcut key bạn thêm một phím nào đó để gán một nút tắt cho macro này (Ví dụ: bạn gõ ở shortcut key là E thì sau này muốn sử dụng macro này bạn chỉ cần gõ Ctrl+E). Chú ý là bạn không nên chọn các phím tắt trùng với các phím tắt có sẳn của Excel như Ctrl+V, Ctrl+C, Ctrl+S....​

​

Khi bạn sử dụng macro, bạn sẽ thấy một Input box được hiển thị và hỏi dãy chứa công thức của bạn là dãy nào. Địa chỉ của dãy được chọn sẽ được hiển thị một cách tự động trong Inputbox, và tất cả những gì bạn cần để làm thực hiện việc chuyển đổi là nhấn OK.​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[TranThanhPhong]

Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động

Nếu bạn đã từng sử dụng validation, bạn sẽ thấy đó là một tính năng rất hay. Có lẽ điểm ấn tượng nhất của nó chính là khả năng thêm một danh sách lựa chọn vào bất kỳ ô nào trên bảng tính và cho phép người sử dụng chọn lựa. Nó sẽ tuyệt hơn hay không nếu khi bạn nhập vào một tên mới trong một ô đang áp dụng tính năng validation thì Excel sẽ tự động thêm tên này vào trong danh sách validation? Điều này có thể thực hiện được khi bạn làm theo các hướng dẫn trong bài này.

Giả sử bạn có danh sách tên trong vùng A1:A10 như hình sau:

Danh sách này chính là tên của các nhân viên trong một công ty. Tại một ô đang áp dụng validation, bạn sẽ không thể nào nhập vào được tên một nhân viên mới ngoài danh sách lựa chọn, mà bạn phải thêm tên nhân viên mới này vào dòng cuối trong danh sách và điều chỉnh lại vùng dữ liệu của validation trước. Điều này sẽ rất bất tiện trong sử dụng.

Để hạn chế nhược điểm này, tại ô A11 bạn nhập vào công thức bên dưới và sao chép đến A20 (dự trù trước sẽ thêm 10 tên mới).

=IF(OR($D$1="",COUNTIF($A$1:A10,$D$1)),"x",$D$1)

Chọn Formulas ➝ Defined Names ➝ Define Name (E2003: Insert ➝ Name ➝ Define), và nhập vào tên MyName tại hộp Names. Tại Refers To, bạn nhập vào công thức bên dưới rồi nhấp OK (E2003: nhấn Add ➝ OK).

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Theo tôi, ta thay bằng công thức
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A:$A,"=x"),1)

Choạ ô D1, vào Data ➝ Data Tools ➝ Data Validation (E2003: Data ➝ Validation). Chọn List từ hộp Allow, và tại Source nhập vào =MyNames, bạn đảm bảo rằng đã chọn thêm hộp In-Cell dropdown. Chọn ngăn Error Alert và bỏ chọn Show error alert after invalid data is entered. Nhấn nút OK khi hoàn tất.

Nhấp phải chuột lên tên Sheet1 và chọn View Code. Sau đó bạn nhập vào đoạn mã sau:

[highlight=VB] Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$1" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub
[/highlight]

Đóng cửa sổ VBE và lưa bảng tính lại, sau đó chọn ô D1 và nhập vào một tên mới chưa có trong danh sách validation và nhấn Enter. Bạn sẽ thấy tên mới xuất xuất hiện trong danh sách dữ liệu trên bảng tính tại ô A11 và nếu chọn ô D1 và mở danh sách validation bạn cũng sẽ thấy tên mới đã được bổ sung vào.

Các bạn xem thêm một số cải tiến trong tập tin đính kèm.

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[ptm0412]

Chiêu thứ 39: Ngày tháng trong Excel

Ngày tháng trong Excel được xử lý rất tuyệt khi được tạo và sử dụng trong Excel, tuy nhiên có những vướng mắc khi xử lý với những dữ liệu ngoại lai import vào. May thay, Excel có những công cụ và phương thức để giải quyết khi nó không đúng như bạn muốn.
Mặc định, Excel sử dụng hệ thống ngày dựa trên năm gốc 1900. Nó coi ngày 01/01/1900 có giá trị 1, ngày 02/01/1900 có giá trị 2, và cứ thế. Các giá trị này giúp cho Excel thực hiện những yêu cầu tính toán thời gian.
Giờ phút cũng tương tự như vậy, nhưng Excel coi giờ như là số thập phân, với 1 tương đương 24:00 hay 00:00. 18:00 được coi như là 0,75 vì 18 giờ là ¾ của 1 ngày.
9ể có thể thấy giá trị số của ngày và giờ, bạn hãy định dạng ô là General. Thí dụ dữ liệu ngày giờ đầy đủ 03/05/2007 03:00:00 PM có 1 giá trị số là 39.025,625, nghĩa là đã 39.025 ngày tính từ ngày gốc 01/01/1900 và kèm theo 0,625 ngày tương đương 15/24 ngày.

Tính toán cho kết quả nhiều hơn 24 giờ:

Bạn có thể cộng các giá trị giờ với nhau bằng toán tử cộng hoặc dùng hàm Sum(). Vì vậy Sum(A1:A5) sẽ cho kết quả là tổng giờ của vùng A1:A5 nếu trong vùng có những giá trị giờ đúng nghĩa. Thế nhưng nếu bạn không để ý bạn sẽ tưởng kết quả sai nếu như tổng tính được nhiều hơn 24 giờ: mỗi 24 giờ Excel coi như 1 ngày, và do định dạng giờ phút, con số chỉ ngày không hiển thị. bạn hãy thử thì biết:

Trong 1 ô nào đó bạn gõ công thức =5:00 + 17:00 + 6:00, bạn cho rằng kết quả phải là 28:00, nhưng Excel lại chỉ cho bạn thấy trên ô là 4:00, vì 24 giờ đã bị chuyển thành 1 ngày, chỉ còn 4 giờ lẻ.

Muốn buộc Excel hiện rõ số giờ lớn hơn 24, bạn phải định dạng custom cho ô đó là [h]:mm hoặc [h]:mm:ss.

Bạn cũng có thể dùng kiểu định dạng đó để biết số phút hoặc số giây của 1 giá trị giờ, hoặc ngày. Thí dụ như 1 ô có giá trị 1 (24 giờ) và định dạng [m] sẽ hiển thị số phút của 24 giờ là 1.440. Nếu bạn định dạng , bạn sẽ thấy số giây là 86.400.

Tính toán ngày và giờ:

Để tính toán ngày giờ bạn cần nhớ những con số sau:
1 ngày = 24 giờ = 1.440 phút = 86.400 giây
1 giờ = 60 phút = 3.600 giây
Khi đã nhớ các con số này, bạn có thể dễ dàng sử dụng để tính toán quy đổi đơn vị thời gian.

Thí dụ bạn có con số 5,5 trong ô A1, và bạn muốn:
- Bạn muốn nó là 5:30 hoặc 5:30 AM, bạn dùng công thức =A1/24
- Bạn muốn nó là 17:30 hoặc 5:30 PM, bạn dùng công thức =A1/24 + 0,5
- ngược lại bạn muốn tính xem trong 5,5 ngày có bao nhiêu giờ, dùng công thức =A1*24

Thí dụ khác: bạn có 1 giá trị ngày (đúng nghĩa) như là 22/05/2007 15:36 và bạn chỉ muốn:
- Lấy số ngày : = Int(A1)
- Lấy số giờ: =A1- Int(A1), hoặc = Mod(A1, 1)

Để tìm khoảng thời gian tính bằng ngày giữa 2 mốc ngày, bạn tính bằng công thức:
=DatedIf(A1, A2, “d”), với A1 là ngày sớm hơn (có giá trị nhỏ hơn)

Hàm DatedIf có thể dùng với tham số “m” cho tháng, “y” cho năm.
Hàm datedif không có trong danh sách hàm của Excel vì nó là 1 hàm của Lotus123.

Nếu bạn không biết chắc ngày nào nhỏ hơn ngày nào trong công thức tính, hãy dùng kết hợp Min và Max như sau:
=DatedIf(Min(A1, A2), max(A1, A2), ”d”)

Để hiển thị giờ âm sau khi tính toán:

Khi tính toán thời gian, khi có những kết quả âm, Excel sẽ bị lỗi và hiển thị tràn số: #########.
Chẳng hạn bạn tính khoảng thời gian giữa giờ bắt đầu và giờ kết thúc: =A2 - A1. Nếu như giờ bắt đầu là 17:00 và kết thúc là 5:00 sáng hôm sau thì công thức trên sẽ bị lỗi. Bạn hãy dùng công thức này:
=A2- A1+ If(A2<A1, 1)
Còn nếu bạn cần tính hiệu số của 2 giờ trong cùng ngày, bạn dùng công thức
=Max(A1:A2)- Min(A1:A2)
hoặc = ABS(A2- A1)

Cách này là chuyển số âm thành số dương, còn có 1 cách để hiện số giờ âm: chỉnh Option - Calculation - Đánh dấu chọn vào mục 1904 Date Systems. Tuy nhiên cách này có thể gây lỗi tính toán cho những công thức khác,
Đây còn gọi là hệ thống ngày MacinTosh, sẽ nói kỹ hơn trong Chiêu 87.

Bạn cũng có thể yêu cầu Excel cộng thêm vào giá trị ngày có sẵn 1 khoảng thời gian mà bạn muốn (khi tính thời hạn này nọ kể từ 1 thời điểm), bằng cách dùng hàm date() với cú pháp sau:
=Date(Year(A1)+ số năm, Month(A1) + số tháng, Day(A1) + số ngày)

Thí dụ như thêm 3 tháng vào ô A1: = Date(Year(A1, Month(A1) + 3, Day(A1))
AddIns Analysis ToolPak còn hỗ trợ nhiều hàm trong đó có những hàm thời gian như Edate() để cộng trừ 1 số tháng vào 1 ngày cho trước. Hoặc hàm EoMonth() để tính ngày cuối tháng của 1 thời điểm bất kỳ.

Ghi chú:
Excel có 1 lỗi là coi năm 1900 là 1 năm nhuận, trong khi không phải vậy. Điều ngạc nhiên là anh Bill cố tình làm vậy, theo như họ tuyên bố.
Sau đây là 1 số link hữu ích về xử lý ngày tháng:

HOW TO: Use Dates and Times in Excel 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214094#6
Text or Number Converted to Unintended Number Format
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214233
Maximum Times in Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214386
Dates and Times Displayed as Serial Numbers When Viewing Formulas
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241072
Controlling and Understanding Settings in the Format Cells Dialog Box
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264372
How to Use Dates and Times in Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;214094

Xử lý Ngày giờ là 1 trong những điều kỳ diệu của Excel. Nắm vững những thủ thuật này bạn sẽ tiết kiệm vô khối thời gian với nó.​

​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[TranThanhPhong]

Chiêu 40: Cho phép sử dụng tính năng Group and Outline trên bảng tính bị khoá

Chiêu 40: Cho phép sử dụng tính năng Group and Outline trên bảng tính bị khoá

Để thực hiện điều này, trước tiên bạn hãy thiết lập Group and Outline cho dữ liệu của mình. Chọn vùng dữ liệu rồi vào Data ➝ Group ➝ Outline và chọn Auto Outline (E2003: Data ➝ Group & Outline) kết quả như hình sau:

Sau đó, bạn dùng tính năng Protect Sheet để khoá bảng tính lại: vào Review ➝ tại nhóm Changes ➝ chọn Protect Sheet và đặt vào mật mã bảo vệ. (Ví dụ như mật mã là Secret).

Khi bảng tính trong chế độ bảo vệ thì bạn không thể sử dụng được tính năng Group and Outline. Do vậy chúng ta sẽ sử dụng tham số UserInterfaceOnly trong phương thức Protect kết hợp với sự kiện mở bảng tính (Workbook_Open) để bật UserInterfaceOnly và EnableOutlining thành True.

Sau khi áp dụng Group and Outline xong, bạn nhấn ALT+F11 để vào cửa sổ VBE. Bạn chọn ThisWorkbook và nhập vào đoạn mã sau (Tên Sheet1 là CodeName của Sheet Formulas Exercise, bạn cũng có thể thay bằng Index của sheet trong bảng tính hoặc dùng TabName):

[highlight=VB]Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub[/highlight]

Sau đó nhấn Save và đóng cửa sổ VBE để trở về cửa sổ bảng tính, hãy lưu bảng tính và đóng lại. Sau đó, bạn mở lại tập tin và cho phép Macro thực thị (nếu nhận được hộp thoại cảnh báo), khi đó bạn có thể sử dụng được các nút Group and Outline trong bảng tính đang bị khoá (các nút lệnh đã bị mờ).

Ghi chú thêm:

Tham số UserInterfaceOnly của phương thức Protect

• là True: sẽ bảo vệ giao diện người dùng (user interface) nhưng cho phép dùng macro để thay đổi.
• nếu không khai báo thì nhận giá trị mặc định là False: bảo vệ cả giao diện người dùng và ngăn các lệnh macro can thiệp vào giao diện người dùng.
• EnableOutlining = True ➝ bật các nút Show/ Hide Detail

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[ptm0412]

Chiêu 41: Bẫy lỗi để trống dữ liệu

Bằng cách dùng Data Validation, chúng ta có thể bảo đảm rằng 1 trường dữ liệu nào đó nhất thiết phải nhập liệu, không được để trống. Thí dụ ta tạo 1 bảng dữ liệu 2 trường, với tên trường là Tên và Bộ phận nằm ở 2 ô A1 và B1.​

Bên dưới, bạn muốn rằng bất kỳ người nào nhập liệu vào bảng cũng phải nhập đủ 2 nội dung này. Nếu bạn bắt đầu với 1 bảng dữ liệu có sẵn những ô bị để trống, cần điền đầy vào, hãy xem phần sau:​

​

Điền dữ liệu vào các ô trống:​

​

Một số các công cụ hoặc hàm của Excel không làm việc với ô trống như Pivot table, Sorting, Filter, hàm SumProduct, … Giả sử bạn có 1 cột A với 1 đống những ô trống nằm xen kẽ nhau. bạn hãy nhấn F5, hoặc Control – G, nhấn Special, chọn ô chọn blank, và nhấn OK. Bây giờ tất cả các ô trống đã được chọn. Nếu bạn muốn điền vào đó những giá trị hoặc công thức giống ô liền ngay trên của nó, hãy nhấn dấu bằng (=), nhấn mũi tên lên, rồi nhấn Ctrl-Enter.​

​

​

Bây giờ đến phần chính:​

​

Tô chọn từ ô A3 đến ô B100 chẳng hạn, theo độ lớn của vùng bạn định nhập dữ liệu. Mở hộp thoại Data – Validation, trong tab setting chọn Custom trong ô Allow, rồi điền công thức sau vào khung công thức:​

​

=AND(COUNTA($A$2:$A2)=ROW()-2,COUNTA($B$2:$B2)=ROW( )-2)​

​

Chú ý các ký hiệu $ liên quan đến tham chiếu tương đối và tuyệt đối, nếu sai hoặc thiếu, validation không hoạt động như ý muốn ráng chịu.​

​

​

​

Vào tab Alert, gõ tiêu đề cho thông báo cảnh báo trong ô Title, gõ câu thông báo của bạn vào ô Error Message, chọn Stop trong khung Error Style.​

​

​

​

Bây giờ mỗi khi bạn nhập thiếu dòng trên, mà nhảy xuống nhập dòng dưới, bạn sẽ được cảnh báo như sau:[/​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[ptm0412]

Chiêu 42: Giảm danh sách xổ xuống của Validation, sau khi chọn 1.

Rất hữu ích cho người dùng Excel khi bạn cho họ 1 danh sách để chọn bằng Validation, nhưng cái danh sách này tự động ngắn lại mỗi khi họ chọn 1 mục trong danh sách, để khỏi phải lựa chọn trong 1 danh sách quá dài, đồng thời tránh việc nhập trùng dữ liệu (đôi khi cần thiết).

Bước 1:
Trong 1 sheet bất kỳ (thí dụ sheet1) bạn có 1 danh sách trong vùng A1:A10. Bạn hy đặt name cho danh sách này bằng cách tô chọn vùng danh sách, rồi gõ tên MyList trong hộp namebox.

Bước 2:
Trong 1 sheet khác mà bạn muốn dùng validation dựa vào danh sách MyList nói trên, tô chọn vùng bạn cần, mở menu Data – Validation, chọn Allow là List, source là =MyList, rồi nhấn OK.

Bước 3: mở cửa sổ VBA của sheet đó, copy đoạn code sau:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strVal As String
    Dim strEntry As String
    On Error Resume Next
    strVal = Target.Validation.Formula1
    If Not strVal = vbNullString Then
        strEntry = Target
        Application.EnableEvents = False
        With Sheet1.Range("MyList")
            .Replace What:=strEntry, _
            Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False
            .Sort Key1:=.Range("A1"), Order1:=xlAscending, _
            Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom
            .Range("A1", .Range("A65536").End(xlUp)).Name = "MyList"
        End With
    End If
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Đóng cửa sổ VBA rồi quay về Excel. Thử chọn 1 mục trong validation, sang 1 ô khác và xổ cái validation xuống, bạn sẽ thấy mục vừa chọn xong không còn trong list nữa.
Lưu ý trong code, khi nói đến name MyList, phải xác định rõ Sheet1.MyList, vì nếu không, VBA sẽ cho rằng name này nằm chung sheet với sheet chứa code và sẽ không tìm thấy và báo lỗi.​

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[TranThanhPhong]

Chiêu 43: 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

Chiêu 43: 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

Tạo một danh sách để fill trong Excel thông qua Fill handle là việc làm rất hay để nhanh chóng nhập danh sách các chuỗi số hoặc chuỗi văn bản vào bảng tính. Excel đã xây dựng sẵn một số Custom List về các ngày trong tuần (Sun - Sat), danh sách các tháng (Jan – Dec) và các chuỗi số nhưng bạn cũng có thể tự tạo thêm các danh sách mới cho mình. Phần này sẽ hướng dẫn bạn các tạo thêm Custom List cho Fill Handle.

Trước tiên, bạn cần nhập vào danh sách các phần tử cần đưa vào Custom List trên bảng tính. Ví dụ như bạn nhập và 26 chữ cái trong các ô A1:A26 trên Sheet1. Sau đó nhấn vào nút Office ➝ Excel Options ➝ Popular ➝ Edit Custom Lists (E2003: Tools ➝ Options ➝ Custom Lists). Nhấp chọn nút nằm bên trái nút Import và dùng chuột quét chọn vùng dữ liệu A1:A26. Sau đó nhấn nút Import và OK. Kể từ lúc này danh sách các chữ cái sẽ có thể sử dụng để fill trong tất cả bảng tính trên máy tính này.

Tiếp theo, để đưa các Custom List vào trong thực đơn ngữ cảnh thì bạn nhấn tổ hợp ALT+F11 rồi vào Insert ➝ Module. Sau đó, bạn nhập vào đoạn mã như sau:

Sub AddFirstList()
    Dim strList As String
    strList = Application.CommandBars.ActionControl.Caption
    If Not strList Like "*...*" Then Exit Sub
    ActiveCell = Left(strList, InStr(1, strList, ".", vbTextCompare) - 1)
End Sub[/highlight]

Bạn kích chuột hai lần lên ThisWorkbook trong cửa sổ VBAProject và nhập vào đoạn mã sau:

[highlight=VB]
Private Sub Workbook_SheetBeforeRightClick _
        (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim cBut As CommandBarButton
    Dim lListCount As Long
    Dim lCount As Long
    Dim strList As String
    Dim MyList
    On Error Resume Next
    With Application
        lListCount = .CustomListCount
        For lCount = 1 To lListCount
            MyList = .GetCustomListContents(lCount)
            strList = .CommandBars("Cell").Controls(MyList(1) & "..." & _
             MyList(UBound(MyList))).Caption
            .CommandBars("Cell").Controls(strList).Delete
            Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
            With cBut
                .Caption = MyList(1) & "..." & MyList(UBound(MyList))
                .Style = msoButtonCaption
                .OnAction = "AddFirstList"
            End With
        Next lCount
    End With
    On Error GoTo 0
End Sub

Đóng cửa sổ VBE và lưu bảng tính lại. Sau đó nhấp phải chuột vào một ô nào đó trên bảng tính, bạn sẽ thấy các Custom List dựng sẵn và do bạn tạo xuất hiện trong trình đơn ngữ cảnh.

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

Mời các bạn tham gia thảo luận các tuyệt chiêu tại đây

 

[kyo]

Chiêu thứ 44: Thay địa chỉ dữ liệu bằng tên.

Mặc dù các ô trong Excel đã được định thứ tự sẵn ngay từ đầu, tuy nhiên, sẽ thật dễ dàng biết bao nếu như chúng được nhớ bằng tên, chẳng hạn như, bạn sẽ thấy mã số hàng hóa có tính gợi mở hơn là A1:A100 chứ phải không?

Excel sử dụng cùng một kỹ thuật để định danh cho ô lẫn cho dãy, đó là sử dụng Name box (hộp Name) ở phía ngoài cùng bên trái thanh công thức. Và để đặt tên cho một dãy, bạn hãy chọn dãy bạn muốn, sau đó, bạn hãy đánh một cái tên bạn mong muốn vào Name box và nhấn phím Enter.

​

Bạn có thấy mũi tên ở phía bên phải Name box không? Khi bạn nhấn vào đó, sẽ có một danh sách các tên được xổ xuống để bạn có thể tìm những tên của ô hay của dãy mà bạn đã sử dụng.

​

Cũng vậy, nếu như bạn chọn ô hoặc dãy nào đã được định danh thì ngay ở Name box, bạn sẽ không còn thấy địa chỉ tham chiếu nữa mà thay vào đó là tên của ô hay dãy đó.

Bạn cũng có thể sử dụng tên thay vì địa chỉ ngay tại thanh công thức. Chẳng hạn, nếu bạn đặt tên cho F4 là “number4”. Vậy, bạn có thể đánh là =number4 thay vì phải đánh là =F4 như trước. Tương tự vậy, giả sử như bạn đặt tên cho A1:A10 là “soluong” và bạn cần tính tổng của nó. Bạn có thể dùng công thức =SUM(soluong) thay vì phải dùng là =SUM(A1:A10).

​

Như vậy, bạn có thể thấy là kỹ thuật định danh này đặc biệt vô cùng hữu ích khi mà bảng tính của bạn ngày một “phình to” hơn và phức tạp hơn.

Bạn cũng có thể tham khảo thêm một cách chi tiết về vấn đề này tại đây.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 45: Sử dụng cùng một cái tên cho dãy ở những worksheet khác nhau.

Thỉnh thoảng, sẽ thật tiện lợi biết bao nếu sử dụng cùng một cái tên cho cùng một địa chỉ cho nhiều worksheet khác nhau nhưng vẫn chung một workbook.

Thông thường, khi bạn sử dụng tên cho dãy xác định ở một worksheet xác định, tất nhiên là cùng chung một workbook, nếu như tên đó đã được dùng rồi thì bạn sẽ không được dùng lại nó để định danh cho một dãy khác ở một worksheet khác. Mặc dù vậy, bạn vẫn có thể sử dụng một số kỹ thuật để “qua mặt”.

Giả sử bạn có một workbook chứa ba worksheet khác nhau lần lượt mang tên là Sheet1, Sheet2 và Sheet3. Và bạn muốn sử dụng cái tên “MyRange” để tham chiếu đến A1:A10 trong Sheet1, A1:A10 trong Sheet2 và cả A1:A10 trong Sheet3. Bạn có thể làm thế này, sử dụng Name box như chiêu số 44, bạn hãy đặt tên Sheet1!MyRange cho dãy A1:A10 trong Sheet1, tương tự Sheet2!MyRange trong Sheet2, Sheet3!MyRange trong Sheet3.

Như vậy, khi bạn xổ danh sách tên trong Name box ra, tất cả những gì bạn thấy chỉ là tên MyRange. Và khi bạn chọn nó, bạn sẽ được tham chiếu trực tiếp đến A1:A10 của chính worksheet bạn đang kích hoạt mà không hề ảnh hưởng gì đến worksheet khác.

Bạn cũng có thể kiểm tra bằng cách vào Formulas -> Defined Names -> Name Manager, bạn sẽ thấy được các tên dãy mà bạn đã tạo (với Excel 2003 là Insert -> Name -> Define, bạn sẽ chỉ thấy duy nhất một cái tên và nó tham chiếu trực tiếp đến worksheet đang được kích hoạt).

​

Nếu tên worksheet của bạn có khoảng trắng, chẳng hạn như: Danh Sach, bạn không thể định danh cho A1:A10 trong worksheet Danh Sach bằng cách gõ Danh Sach!MyRange, mà bạn phải gõ là ‘Danh Sach’!MyRange. Thậm chí cả với tên worksheet không có khoảng trắng, bạn cũng có thể làm tương tự (chẳng hạn ‘DanhSach’!MyRange). Do đó, cách tốt nhất là bạn cứ dùng dấu nháy đơn cả khi tên worksheet có khoảng trắng hay không có khoảng trắng.

Sử dụng tham chiếu tương đối

Thông thường, dãy mà bạn đặt tên sẽ có địa chỉ tham chiếu tuyệt đối. Mặc dù vậy, bạn không nhất thiết phải sử dụng tham chiếu tuyệt đối. Bạn hãy làm các thao tác như sau:

1. Chọn ô A11, nhấn chuột phải chọn Name a Range (với Excel 2003, chọn Insert -> Name -> Define).
   
   
2. Tại dòng “Name:”, gõ tên “GPENumber” (với Excel 2003, dòng đó tên là “Names in Workbook:”).
3. Tại dòng “Refers To:”, gõ =A$1:A$10 và chọn OK.
   
   
4. Bạn hãy tạo một bảng dữ liệu như trong hình dưới đây.
   
   
5. Tại ô A11, gõ =SUM(GPENumber).
6. Tại ô B11, cũng gõ =SUM(GPENumber).

Kết quả bạn nhận được tại A11 là 55 và tại B11 là 10, bởi vì khi bạn định danh A$1:A$10, bạn đã sử dụng tham chiếu tương đối cho cột và tham chiếu tuyệt đối cho hàng.

​

Bạn cũng nên lưu ý rằng, dù cho bạn dùng =SUM(GPENumber) ở một worksheet khác, nó vẫn sẽ tham chiếu đến worksheet mà bạn đã dùng để định danh, chẳng hạn như trong trường hợp trên thì worksheet đã được dùng khi định danh mang tên Sheet1.

Đơn giản hóa tổng: Nếu bạn muốn đơn giản hóa tổng, tức là mỗi khi bạn dùng tên, nó sẽ tự động cộng 10 ô đầu tiên trong cột lại với nhau, bạn hãy làm như sau:

1. Bạn hãy giả lập lại toàn bộ các bước 1, 2, 3 và 4 như đã từng làm phía trên. Tuy nhiên, ở bước 2, bạn đặt tên là “GPESum” và ở bước 3, tại dòng “Refers To:”, gõ =SUM(A$1:A$10) thay vì là =A$1:A$10.
2. Tại ô A11, gõ =GPESum.
3. Tại ô B11, cũng gõ =GPESum.

Kết quả bạn nhận được cũng như trước, tuy vậy, công thức mà bạn phải gõ thì đã được lược giản đi rất nhiều. Qua đây, bạn có thể thấy rằng, chỉ vài bước kết hợp đơn giản giữa địa chỉ tham chiếu tương đối, địa chỉ tham chiếu tuyệt đối cùng một vài hàm, bạn đã có thể tiết kiệm rất nhiều công sức cho chính mình.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 46: Sử dụng tên để tạo hàm tự tạo

Mặc dù tham chiếu dữ liệu bằng tên thì rất tiện lợi, thỉnh thoảng nó cũng hữu ích với cả việc dùng tên để lưu trữ giá trị hằng số hay một công thức nào đó, đặc biệt kể cả nếu bạn dùng để tạo một hàm VBA tự tạo nào đó.

Giả sử bạn có tỷ suất thuế là 10% và bạn cần nó trong suốt quá trình tính toán của mình. Thay vì bạn cứ phải gõ 10% hoặc 0.1 vào từng công thức, bạn có thể dùng tên TaxRate và Excel sẽ hiểu nó là 0.1, bằng cách:

1. Chọn thẻ Formulas -> chọn Defined Names -> Define Name (với Excel 2003 thì là Insert -> Name -> Define).
2. Ở dòng “Names:”, gõ TaxRate.
3. Ở dòng “Refers To:”, gõ =0.1 rồi nhấn Add.

Một trong những ưu điểm lớn nhất khi sử dụng cách này, đó là, chẳng hạn như tỷ suất thuế có thay đổi thì bạn chỉ cần vào lại thẻ Formulas -> Defined Names -> Define Name (Excel 2003: Insert -> Name -> Define) rồi chọn tên TaxRate và sau đó chỉnh lại cho phù hợp mà không cần phải cất công rà từng công thức một để thay đổi.

Bên cạnh việc dùng giá trị hằng số như trên, bạn cũng có thể sử dụng công thức ở dòng Refers To trong bảng Name. Giả sử như bạn muốn tạo lập một công thức mà khi bạn gõ vào một ô, nó sẽ tự động lấy tổng (SUM) của 10 ô phía trên ô bạn vừa gõ. Bạn sẽ làm như sau:

1. Chọn A11 -> Click chuột phải -> chọn Name a Range (với Excel 2003, Insert -> Name -> Define).
2. Tại dòng “Name:”, gõ TongCong và tại dòng “Refers To:”, gõ =SUM(A1:A10). Sau đó chọn OK.
3. Gõ 10 con số bất kỳ trong 10 ô phía dưới liên tiếp mà con số bắt đầu có thể là ở bất kỳ hàng nào, cột nào. Giả sử ở đây tôi gõ từ 1 đến 10 lần lượt vào C2:C11.
4. Sau đó, tại hàng thứ 11 mà ở ví dụ trên, tôi gõ tại ô C12: =TongCong.

Kết quả mà bạn thấy, đó chính là, nó sẽ tự động cộng liên tục 10 ô phía trên ô mà bạn gõ =TongCong.

​

Nếu bạn muốn tạo một tên chứa công thức mà nó không bị giới hạn bởi 10 ô, nhưng nó sẽ lấy toàn bộ tất cả các ô phía trên ô chứa công thức =TongCong. Bạn làm như sau:

1. Chọn B11 -> Chọn thẻ Formulas -> Ở Defined Names, chọn Name Manager (với Excel 2003, chọn Insert -> Name -> Define) -> Chọn tên TongCong mà bạn đã tạo từ ví dụ trước.
2. Ở dòng “Refers To:”, bạn sẽ thấy nó ghi là =SUM(Sheet1!B1:B10), bạn hãy sửa lại là =SUM(B$1:B10).

Bây giờ bạn hãy thử lại đi, hãy tạo một bảng dữ liệu bất kỳ theo một cột nào đó, và ở ô tiếp theo trong cột đó, bạn hãy dùng =TongCong, lập tức bạn sẽ thấy rằng, nó sẽ cộng toàn bộ các ô phía trên ô bạn nhập =TongCong lại bắt đầu từ ô số 1, bất kể là nó có bao nhiêu ô đi chăng nữa. Đó là bởi vì bạn đã đặt tham chiếu tuyệt đối vào hàng 1.

​

Sử dụng tên với Intersect (tạm dịch: phép giao)

Bằng việc kết hợp dùng tên với toán tử giao (một trong những đặc trưng cơ bản của Excel mà rất ít người biết đến), bạn có thể dễ dàng tạo nên được một hàm dò tìm phức tạp. Nếu bạn vẫn còn chưa rành về phương thức Intersect hoạt động ra sao, chúng ta hãy cùng thực hành một ví dụ nho nhỏ:

1. Tại A1, gõ tiêu đề là “Họ Tên”, tương tự với B1 là “Lương (đơn vị: triệu đồng)”, C1 là “Nghề nghiệp”.
2. Bạn hãy đặt hai cái tên lần lượt ở A2 và A3, chẳng hạn như tôi đặt là “Nguyễn Văn A” và “Trần Thị B”.
3. Hãy cho hai mức lương lần lượt vào B2 và B3, ở đây tôi đặt là “10” và “20”.
4. Hai người này làm nghề gì? Tôi đặt lần lượt là “Giáo viên” và “Bác sĩ”.
5. Sau đó, bạn hãy quét chọn A1:C3 -> vào thẻ Formulas -> ở Defined Names, chọn Create from Selection (với Excel 2003, chọn Insert -> Name -> Create). Hãy bảo đảm rằng lựa chọn “Top row” và “Left column” đã được đánh dấu check -> Nhấn OK.

​

Kể từ đây, khi bạn gõ công thức theo cú pháp =Tên Tiêu_đề (lưu ý là nếu tên có khoảng trắng hoặc các dấu như “(“, “:” thì bạn phải thay là dấu gạch dưới _), bạn sẽ nhận được thông tin như bạn mong muốn.

​

Cũng xin lưu ý khác là, khoảng trắng giữa Tên và Tiêu đề là rất quan trọng, vì nó thể hiện cho Excel biết rằng, đây là toán tử giao.

Dựa trên những lý thuyết trên, bạn có thể kết hợp nó với tên để làm cho công việc đơn giản đi cũng như giúp bạn dễ sử dụng, dễ đọc và dễ nhớ hơn. Bạn hãy thử làm như sau:

Đầu tiên, bạn hãy tạo một bảng dữ liệu như hình dưới đây và bạn hãy dùng cách Create from Selection như bước 5 ở phía trên:

​

Sau đó, bạn hãy làm các thao tác như sau:

1. Bấm chuột phải vào một ô bất kỳ, chọn Name a Range (với Excel 2003, chọn Insert -> Name -> Define).
2. Gõ một cái tên vào “Name:”, ở đây tôi gõ là DuyAnhJob
3. Tại “Refers To:”, gõ =Thái_Duy_Anh Nghề_nghiệp rồi chọn OK.

Từ đây, bạn có thể chỉ cần dùng =DuyAnhJob là bạn sẽ có ngay thông tin nghề nghiệp của Thái Duy Anh. Bạn có thể linh hoạt biến hóa hơn để phục vụ nhu cầu của bạn, chắc chắn, nó sẽ giúp cho công việc của bạn được dễ dàng hơn.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 47: Tạo dãy có thể mở rộng và thu hẹp

Nếu bạn có nhu cầu cần phải cập nhật và thêm dữ liệu, hoặc nếu bạn đang làm việc với biểu đồ và PivotTables, bạn sẽ muốn tạo một tên cho dãy động, mà bản thân nó có thể mở rộng hoặc thu hẹp cho phù hợp với dữ liệu của bạn.

Để hiểu hơn về cách hoạt động của tên dãy động, điều đầu tiên bạn cần phải làm quen chính là hàm OFFSET của Excel.

Chúng ta sẽ bắt đầu với một tên dãy động đơn giản. Giả sử như nếu trong cột A của bạn có 10 dòng liên tiếp chứa dữ liệu, tức là bạn phải có một cái tên của dãy A1:A10. Hãy làm theo bước sau để có được nó:

Vào thẻ Formulas, ở Defined Names, chọn Define Name (Excel 2003: Insert -> Name -> Define) và đánh MyRange vào dòng “Names:”. Tại dòng “Refers To:”, bạn hãy gõ =OFFSET($A$1,0,0,COUNTA($A$1:$A$100),1) và sau đó OK.

​

Bạn cần lưu ý rằng, khi bạn sử dụng hàm COUNTA, bạn phải cưỡng lại cơn cám dỗ thực hiện hết toàn bộ cột, bởi vì nếu làm như vậy, bạn đang vô tình bắt hàm COUNTA phải đếm cả hàng ngàn dòng một cách vô ích.

Sau khi bạn làm xong công việc trên, bạn hãy cho một vài dữ liệu vào cột A. MyRange sẽ thực hiện công việc hợp tất cả các dòng có dữ liệu liên tục lại với nhau, bắt đầu từ A1. Tuy vậy, tên dãy động sẽ không hiện ra trong hộp Name (Name box). Nhưng, bạn vẫn có thể biết được kết quả của MyRange bằng cách gõ thẳng tên MyRange vào hộp Name và nhấn Enter, lập tức sẽ xuất hiện một vùng chọn báo hiệu cho bạn biết. Tất nhiên, bạn cũng có thể vào thẻ Home, ở Editting, chọn Find & Select rồi chọn Go To…(Ctrl + G). Sau đó, bạn hãy gõ MyRange vào dòng “Reference:” và OK.

​

Bạn có thể thấy rằng, tên dãy động của bạn ở ví dụ trên đã được ẩn mình phía sau hàm COUNTA là tham số Height của hàm OFFSET.

Lưu ý rằng: hàm COUNTA sẽ đếm tất cả các ô không rỗng, bao gồm cả những ô có chứa công thức nhưng trả về giá trị rỗng.

Nếu bạn có một danh sách bao gồm các dữ liệu kiểu số, và ở cuối danh sách ấy lại có lưu dữ liệu kiểu chuỗi. Tất nhiên, bạn không muốn tên dãy động của bạn có chứa kiểu dữ liệu chuỗi ấy. Hãy dùng hàm COUNT thay vì hàm COUNTA vì hàm COUNT chỉ đếm các dữ liệu kiểu số mà thôi.

Trong ví dụ dưới đây, bạn sẽ dùng tên dãy động để định nghĩa một bảng dữ liệu mà bạn muốn nó động. Để làm điều đó, hãy điền công thức sau vào dòng “Refers To:”:
=OFFSET($A$1,0,0,COUNTA($A$1:$A$100),COUNTA($1:$1))

​

Với công thức này, tên dãy động của bạn sẽ mở rộng không chỉ theo chiều dọc mà còn theo chiều ngang nữa. Nếu bạn chắc chắn rằng số cột của bạn là cố định, bạn có thể thay hàm COUNTA thứ hai trong công thức bằng một số nào đó.

Một vấn đề khá rắc rối khi sử dụng tên dãy động cho bảng dữ liệu là nếu cột dài nhất trong bảng lại không phải là cột A như các bảng dữ liệu phổ biến khác, bạn sẽ làm gì? Để có thể khắc phục lỗi đó, hàm MAX sẽ là một sự lựa chọn tốt. Bạn hãy tạo một bảng dữ liệu như hình dưới đây:

​

Nếu bạn dùng COUNTA, bạn sẽ nhận được kết quả là 3 ở cột A và điều đó sẽ dẫn đến kết quả sai. Do đó, bạn cần dùng hàm MAX như sau:
=OFFSET($A$1,0,0,MAX($1:$1),COUNTA($1:$1))

Tiếp tục đến với một ví dụ khác, danh sách số của bạn lại có các ô rỗng và điều đó khiến cho bạn không thể sử dụng hàm COUNT cũng như COUNTA, bởi vì khi sử dụng, nó sẽ ra một kết quả hoàn toàn sai so với số dòng thực sự chứa dữ liệu. Để rõ hơn, bạn hãy tạo một bảng dữ liệu khác như hình dưới đây.

Bạn thấy đấy, bạn có tất cả 10 dòng chứa dữ liệu nhưng số thứ tự chỉ có 6, và do đó, tất nhiên, hàm COUNT sẽ trả về kết quả là 6. Để khắc phục vấn đề này, bạn hãy sử dụng hàm MATCH. Hàm MATCH sẽ trả về cho bạn một vị trí tương đối của một giá trị nào đó có trong mảng mà nó khớp với điều kiện có trong hàm. Với ví dụ này, nếu bạn sử dụng hàm này như thế này: =MATCH(6,$A$1:$A$100,0), kết quả bạn nhận được sẽ là 11 vì giá trị 6 nằm ở dòng 11 trong cột A.

​

Một điều dễ nhận thấy rằng, khi bạn sử dụng hàm MATCH như một phần của tên dãy động, con số cuối cùng của dãy chắc chắn là không dễ nhận biết. Bởi vậy, bạn cần yêu cầu hàm MATCH trả về giá trị gần đúng con số lớn nhất bằng cách chỉnh tham số cuối cùng của hàm là 1.

Như ví dụ trên, vì bạn đã yêu cầu tìm chính xác giá trị 6, nhưng nếu bạn không biết giá trị lớn nhất là bao nhiêu, bạn hãy làm thế này: =MATCH(1E+306,$A$1:$A$100,1)

Vậy, tổng kết lại phía trên, bạn sẽ có một công thức đầy đủ để điền vào dòng “Refers To:” là =OFFSET($A$1,0,0,MATCH(1E+306,$A$1:$A$100,1),1).

Một kiểu biến thể khác, không chỉ dữ liệu kiểu số nhưng còn bao gồm cả kiểu chuỗi nữa, và tất nhiên là có cả các ô rỗng xen giữa. Như vậy, hàm MATCH sẽ được thiết kế lại như sau: MATCH("*",$A$1:$A$100,-1).

Giờ đây bạn có thể dùng công thức dưới đây để vượt qua các ô rỗng và có thể lấy được toàn bộ dữ liệu bao gồm cả kiểu số lẫn kiểu chuỗi trong bảng dữ liệu của chính bạn:

=MAX(MATCH("*",$A$2:$A$100,-1),MATCH(1E+306,$A$2:$A$100,1))

Áp dụng vào bảng dữ liệu, bạn có thể dùng công thức như sau vào tên dãy động:

=OFFSET($A$2,0,0, MAX(MATCH("*",$A$2:$A$100,-1),MATCH(1E+306,$A$2:$A$100,1)),1)

Để tổng kết lại tất cả các điều trên, giả sử chúng ta có một bảng dữ liệu trên cột A, bạn hãy tạo một cái tên dãy động theo công thức như sau:

- Nếu toàn bộ bảng là dữ liệu kiểu số: =OFFSET($A$1,0,0,COUNT($A:$A),1)

- Nếu toàn bộ bảng là dữ liệu hoặc kiểu số hoặc kiểu chuỗi hoặc cả hai: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

- Nếu bảng có ô rỗng và dòng cuối cùng của bảng là một dữ liệu kiểu số: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A))

- Nếu bảng có ô rỗng và dòng cuối cùng của bảng là một dữ liệu kiểu chuỗi: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

Ngoài ra, bạn cũng có thể vận dụng công thức cho một số trường hợp khác như:

- Mở rộng bảng theo chiều dọc với độ dài là giá trị từ một ô khác (chẳng hạn như B1): =OFFSET($A$1,0,0,$B$1,1)

- Mở rộng bảng theo chiều dọc với độ dài là tháng hiện tại: =OFFSET($A$1,0,0,MONTH(TODAY( )),1)

- Mở rộng bảng theo chiều dọc với độ dài là thứ tự tuần hiện tại trong năm: =OFFSET($A$1,0,0,WEEKNUM(TODAY( )),1)

Tất nhiên, nó đòi hỏi bạn phải cài đặt Analysis ToolPak. Bạn có thể chọn nó bằng cách chọn nút Office -> Excel Options -> Add-ins (Excel 2003: Tools -> Add-Ins).

Một bổ sung nho nhỏ về chiêu 47 (của Lê Duy Thương), đó là để có được một dãy động có khả năng mở rộng hay thu hẹp tùy ý, bạn cũng có thể sử dụng công cụ có sẵn của Excel, đó chính là Table. Bằng cách vào thẻ Insert -> Table, bạn hãy chọn dãy nguồn và từ đó, mỗi lần bạn thêm dữ liệu vào hàng kế tiếp hàng cuối cùng của bảng, lập tức bảng sẽ "phình to" ra thêm và thậm chí, nếu bạn áp dụng với Data Validation (dùng List trỏ vào bảng nguồn, giả sử như A2:A10) thì khi bảng phình to ra, Data Validation của bạn sẽ cho nguồn là A2:A11 tương ứng.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 48: Sử dụng dãy động cách linh hoạt nhất

Một cái tên dãy động này có thể trú ngụ trong một cái tên dãy động khác, đây là điều vô cùng hữu ích cho bạn trong nhiều vấn đề, chẳng hạn như khi bạn có một danh sách dài những cái tên.

Chúng ta hãy thử một ví dụ bằng việc lập một cái tên để khi gọi nó, nó sẽ tham chiếu đến những cái tên đã được xếp thứ tự mà bắt đầu bằng chữ D.

Trước tiên, hãy tạo một bảng dữ liệu như hình dưới đây tại cột A và bạn nên nhớ rằng tên đã được xếp theo thứ tự bảng chữ cái. Sau đó, bạn hãy làm tiếp những bước sau:

1. Chọn thẻ Formulas -> ở Defined Names, chọn Define Name (với Excel 2003, chọn Insert -> Name -> Define).
2. Bạn hãy tạo một cái tên với tên gọi là Ten và sử dụng công thức: =OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1) rồi sau đó nhấn OK.
3. Sau đó, tiếp tục tạo một cái tên khác với tên gọi là DTen và sử dụng công thức: =OFFSET(INDIRECT(ADDRESS(MATCH("D*",Ten,0)+1,1)),0,0,COUNTIF(Ten,"D*"), 1) với D chính là chữ cái đầu của những cái tên mà bạn muốn chọn rồi sau đó nhấn OK.

Sau khi làm xong, kết quả bạn nhận được sẽ như thế này:

​

Nếu bạn muốn, bạn có thể tạo từng cái tên tương ứng với từng chữ cái trong bảng chữ cái, chẳng hạn như ATen, ĂTen, ÂTen,... Tuy vậy, cách làm đó quả thật là rất mất thời gian. Thay vì vậy, bạn có thể áp dụng một cách khác là tạo một bảng lựa chọn các ký tự ngay trên ô của bảng tính và tên dãy động sẽ dựa vào ô đó. Bạn có thể làm như sau:

1. Chọn một ô nào đó mà bạn không sử dụng tới, đặt tên cho ô đó là KyTu.
2. Chọn thẻ Data -> ở Data Tools, chọn Data Validation (với Excel 2003, chọn Data -> Validation).
3. Trong danh sách của dòng “Allow:”, chọn List và bạn gõ tất cả bảng chữ cái vào dòng “Source:” như A*,Ă*,Â*,B*,… và sau đó nhấn OK.
4. Chọn thẻ Formulas -> ở Defined Names -> Define Name (với Excel 2003, chọn Insert -> Name -> Define).
5. Đặt một cái tên như Ten và gõ công thức vào dòng “Refers To:” là =OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1) và sau đó nhấn OK.
6. Tiếp tục tạo một cái tên khác với tên gọi là KyTuDau với công thức sử dụng là: =OFFSET(INDIRECT(ADDRESS(MATCH(KyTu,Ten,0)+1,1)),0,0,COUNTIF(Ten, KyTu),1) và OK.

Bây giờ, bạn hãy tận hưởng thành quả của mình bằng việc lựa chọn một ký tự và xem thử cái tên của bạn hoạt động thế nào nhé.

​

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 49: Nhận biết tên của dãy trong worksheet

Excel cho phép người dùng đặt một cái tên có nghĩa cho một dãy xác định trong worksheet. Tuy vậy, với số lượng ngày càng lớn những cái tên, bạn sẽ cần đến công cụ để nhận biết các dãy đã được đặt tên.

Có rất nhiều cách để nhận biết, nhưng trong chiêu này sẽ chỉ đề cập đến hai cách dễ nhận biết nhất (ngoài hai cách dưới đây, bạn cũng có thể sử dụng hộp Name hay dùng hộp thoại Go To..., dùng Name Manager,...)

Cách 1

Chọn thẻ Formulas -> ở Defined Names, chọn Use in Formula -> Paste Names (với Excel 2003, chọn Insert -> Name -> Paste) hoặc nhấn F3. Tại hộp thoại Paste Name, nhấn Paste List và Excel sẽ liệt kê toàn bộ tên cùng địa chỉ tham chiếu cho bạn. Mặc dù cách này tỏ ra khá hữu dụng nhưng nó đòi hỏi bạn lựa chọn một cái tên cụ thể.

​

Bên cạnh đó, khi bạn sử dụng cách này và có được một loạt tên (chẳng hạn như tôi bắt đầu cho liệt kê tên ở ô A1). Bạn có thể thay thế các địa chỉ tương đối ở ô B1 trở về sau bằng hàm HYPERLINK đơn giản để nó có thể trực tiếp liên kết luôn với tên bằng cách dùng công thức này cho B1: =HYPERLINK("[Book1.xls]"&A1,A1). Tất nhiên, bạn phải lưu file với cái tên là Book1.xls thì liên kết của bạn mới hoạt động. Sau đó, hãy kéo xuống tương ứng với B2, B3,…

​

Cách 2

Một cách khác cũng rất hay nhưng ít người biết đến, đó là bạn hãy sử dụng chức năng Zoom của Excel. Hãy thu nhỏ lại vào cỡ <40% và bạn sẽ thấy được tất cả cái tên trong worksheet hiện hành.

Lưu ý là cách 2 không thể áp dụng với tên dãy động.

​

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 50: PivotTable

Bản thân chiêu thứ 50 này chính là giới thiệu về PivotTable nên kyo thiết nghĩ, diễn đàn GPE chúng ta đã có hẳn một tài liệu rất chi tiết về vấn đề này do chính tác giả ptm0412 biên soạn, vì vậy sẽ thật không nên khi kyo lại đi giới thiệu về PivotTable một lần nữa.

Sẽ có một số người biết và một số người không biết, nhưng đây chính là một trong những món quà mà GPE gửi tặng các bạn nhân dịp SN lần thứ 4 của diễn đàn. Vậy thì, còn chờ gì nữa mà không đọc tài liệu này, bạn nhỉ! Link download tài liệu tại đây.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 51: Chia sẻ PivotTable nhưng không chia sẻ cấu trúc dữ liệu của nó

Chiêu này sẽ tạo một hình chụp PivotTable của bạn mà không cần thiết phải cho người xem biết được cấu trúc dữ liệu của nó (cấu trúc dữ liệu ở đây ý chỉ là người xem không thể nào tùy chỉnh lại PivotTable, tức là không thể kéo thả lại Row, Column, Data,... được)

Bạn cần gửi PivotTable của bạn cho người khác xem nhưng bạn lại không muốn họ biết những gì ẩn đằng sau nó. Trong trường hợp này, bạn có thể tạo ra một bản sao chép PivotTable để người xem chỉ có thể xem được những gì cần xem mà thôi. Tất nhiên là bản sao của bạn cần phải có dung lượng thấp hơn so với dung lượng của bản chính.

Giả sử bạn có một PivotTable trong một workbook, tất cả những gì bạn cần phải làm là chọn toàn bộ nó, nhấn copy và paste nó tại một sheet sạch sẽ nào đó bạn muốn bằng cách dùng Paste Special… -> Values.

​

Tuy vậy, có một điều trở ngại là Excel sẽ chỉ sao chép giá trị mà không sao chép định dạng PivotTable của bạn (bởi vì nếu bạn chọn sao chép định dạng, vô tình bạn cũng để lộ luôn cấu trúc dữ liệu). Điều này sẽ khiến cho người xem cảm thấy bối rối vì khó đọc và bản thân nó cũng không gây được ấn tượng cho họ. Do vậy, nếu bạn muốn có một bản sao vừa có giá trị và cũng vừa có định dạng dữ liệu, bạn có thể tạo một bản sao bằng hình ảnh.

Để làm được việc đó, bạn cần phải quét chọn PivotTable -> chọn thẻ Home -> ở Clipboard, chọn Paste -> As Picture -> Copy as Picture… -> chọn As shown on screen và Picture tại hộp thoại Copy Picture -> OK sau đó bạn nhấn Paste. Với Excel 2003, bạn nhấn giữ Shift, chọn Edit -> Copy Picture -> chọn bất kỳ ô nào ngoài PivotTable -> chọn Edit -> Paste.

​

Kết quả bạn có sẽ như thế này:

​

Tất nhiên, bạn cũng có thể dùng cách khác, chẳng hạn như dùng Print Screen có sẵn chẳng hạn.

Một cách khác là bạn có thể sử dụng công cụ Camera của Excel. Để sử dụng nó, bạn chọn nút Office -> Excel Options -> Customize -> chọn Commands Not in the Ribbon từ dòng Choose Commands From: -> chọn Camera -> Add -> OK. Với Excel 2003, chọn View -> Toolbars -> Customize… -> Tại hộp thoại From the Customize, chọn thẻ Commands -> tại hộp Categories, chọn Tools và tại hộp Commands ở phía bên phải, bạn kéo thanh trượt xuống cho đến khi bạn thấy Camera -> chọn và kéo rê nó vào bất kỳ thanh công cụ nào bạn muốn.

​

Sau đó, bạn hãy quét chọn những gì bạn muốn chụp và nhấn Camera, sau đó bạn hãy chọn một ô nào đó ở bên ngoài và thế là bạn có thể tận hưởng được thành quả của mình rồi đó.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

Chiêu thứ 52: Tự động tạo PivotTable

Để tạo một PivotTable, bạn sẽ cần phải tốn một ít công sức. Tuy vậy, thông thường công sức mà bạn bỏ ra là dư thừa khi mà chỉ cần với một chút vốn liếng VBA, bạn có thể tạo một PivotTable đơn giản một cách tự động.

PivotTable là một công cụ mạnh mẽ và rất thông minh mà bạn có thể tận dụng để lưu trữ một bảng hoặc một danh sách nào đó. Mặc dù đôi khi tạo một PivotTable cũng rất phức tạp, tuy nhiên trong nhiều trường hợp, bạn vẫn có thể có một PivotTable cho mình một cách nhanh chóng và dễ dàng. Chẳng hạn như, đa phần người sử dụng đều quan tâm đến hai câu hỏi là làm sao để đếm được tất cả các phần tử trong danh sách và làm sao để tạo một danh sách những phần tử duy nhất từ một danh sách có nhiều phần tử trùng nhau. Trong chiêu thứ 52 này, bạn sẽ học được cách trả lời hai câu hỏi này một cách nhanh nhất.

Giả sử bạn có một danh sách những cái tên trùng nhau như hình dưới đây. Để biết được trong danh sách có bao nhiêu cái tên và tất nhiên là bạn muốn có một danh sách những cái tên duy nhất, bạn hãy làm như sau:

1. Chọn toàn bộ bảng -> chọn thẻ Insert -> PivotTable (với Excel 2003, chọn Data -> PivotTable Report).
2. Bạn chọn một nơi để chứa bảng PivotTable, sau đó nó sẽ có giao diện như hình dưới đây.
3. Kéo tiêu đề “Tên” vào vùng “Row Labels”.
4. Một lần nữa kéo “Tên” vào vùng “Values” rồi OK.

Với Excel 2003, bạn hãy làm theo trình tự sau:

1. Data -> PivotTable and PivotChart Report để khởi động PivotTable Wizard.
2. Trước khi bắt đầu, bạn hãy bảo đảm rằng bạn đã chọn một ô trong danh sách mà bạn muốn tạo PivotTable.
3. Tại mục “What kind of report do you want to create?”, bạn hãy chọn PivotTable.
4. Bấm Next, Excel sẽ tự động khoanh vùng cột A, cột dữ liệu mà bạn muốn.
5. Nếu vùng dữ liệu bạn muốn đã được khoanh vùng, hãy bấm Next. Còn không, bạn có thể dùng chuột để chọn vùng dữ liệu bạn muốn.
6. Bấm chọn Layout -> kéo “Tên” vào vùng DATA.
7. Lại kéo “Tên” vào vùng ROW -> OK.
8. Chọn nơi mà bạn muốn để PivotTable của bạn rồi nhấn Finish.

​

Lưu ý rằng, bạn có thể chọn Sum, Average,… chứ không nhất thiết phải để Count như mặc định của Excel đã dùng (Excel sẽ mặc định phép Count với dữ liệu chuỗi và phép Sum với dữ liệu số).

Và bạn hãy tận hưởng thành quả của mình đi nào.

Tiết kiệm thời gian với Macro

Bạn muốn có một macro thực hiện những công việc mà bạn làm từ nãy đến giờ không? Tức là nó sẽ tạo một PivotTable trên một cột nào đó mà bạn muốn. Nếu bạn dùng chức năng Record, bạn sẽ chỉ nhận được kết quả của cột A, do đó, bạn sẽ cần một chút biến hóa.

Hãy nhấn Alt + F11 để vào cửa sổ Visual Basic Editor -> Insert -> Module và gõ đoạn code sau:

Sub GetCount()
    Dim Pt As PivotTable
    Dim strField As String
    strField = Selection.Cells(1, 1).Text
    Range(Selection, Selection.End(xlDown)).Name = "Items"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
        SourceData:="=Items").CreatePivotTable TableDestination:="", _
        TableName:="ItemList"
    Set Pt = ActiveSheet.PivotTables("ItemList")
    ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)
    Pt.AddFields RowFields:=strField
    Pt.PivotFields(strField).Orientation = xlDataField
End Sub

Và, trước khi bạn chạy đoạn code này, hãy bảo đảm rằng bạn đã nhấp chuột chọn tiêu đề của danh sách (như ví dụ trước, tiêu đề là “Tên”) và danh sách của bạn không có ô rỗng. Để tránh được ô rỗng, bạn có thể dùng một thủ thuật đơn giản là sort dữ liệu vì ô rỗng sẽ luôn được đưa xuống dòng cuối cùng của danh sách. Đoạn code sẽ tự động tạo một tên dãy động là Items và sẽ tạo một PivotTable dựa trên tên đó và nó sẽ được cư ngụ tại một sheet mới.

MỤC LỤC Những tuyệt chiêu trong Excel
​