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

[kyo]

Chiêu thứ 53: Di chuyển dòng Grand Total của PivotTable

Một trong những điều bất tiện mà PivotTable gây ra chính là dòng Grand Total - dòng tổng cộng luôn ở phía cuối của bảng, điều đó cũng có nghĩa là bạn phải kéo thanh trượt xuống thì mới có thể thấy được con số. Do đó, sẽ dễ dàng hơn nhiều nếu bạn có thể di chuyển dòng Grand Total đó lên trên để bạn có thể lướt nhanh con số tổng cộng chỉ bằng một cái chớp mắt.

Trước khi chúng ta thực hiện cụ thể công việc này, tôi nghĩ bạn cần nên tham khảo một chút về hàm GETPIVOTDATA, là một hàm được thiết kế đặc biệt để lấy dữ liệu từ PivotTable. Cú pháp hàm như sau: = GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2,...). Bạn có thể tham khảo cụ thể hơn tại đây:

Thực ra, bạn cũng chẳng cần phải nhớ cú pháp làm gì, trái lại, nếu bạn muốn lấy một dữ liệu nào đó trong PivotTable, bạn chỉ cần gõ dấu bằng “=” và sau đó dùng chuột nhấn vào bất kỳ một ô chứa dữ liệu nào đó trong PivotTable, chẳng hạn như bạn nhấn vào giá trị Grand Total, tức thì bạn sẽ có được một công thức hoàn chỉnh.

​

Lưu ý rằng: Nếu bạn sử dụng Function Wizard hoặc đã gõ =GETPIVOTDATA() từ trước, sau đó bạn mới nhấn chuột vào PivotTable, khi đó, Excel sẽ lồng thêm một hàm GETPIVOTDATA vào trong cái bạn đã đánh, chẳng hạn như =GETPIVOTDATA(GETPIVOTDATA()).

​

Ngoài ra, bạn cũng có thể thực hiện công việc lấy dữ liệu Grand Total một cách dễ dàng hơn bằng cách đặt tên cho cột chứa Grand Total và sau đó sử dụng hàm MAX (bởi vì giá trị Grand Total bao giờ cũng là giá trị lớn nhất). Chẳng hạn như ở đây, tôi đặt tên cho cột Grand Total là “TongCong”, vậy tôi sẽ có công thức là =MAX(TongCong).

​

Ngoài ra, bạn cũng có thể linh hoạt sử dụng hàm LARGE hoặc SMALL để lấy những giá trị lớn thứ hai, lớn thứ ba. Chẳng hạn ở đây tôi lấy giá trị lớn thứ hai, sau giá trị Grand Total bằng cách dùng công thức =LARGE(TongCong,2).

​

Vậy là bạn đã học được cách lấy giá trị Grand Total của PivotTable rồi đấy. Giờ đây, bạn có thể chèn thêm một dòng phía trên PivotTable và sử dụng những gì đã được học, thế là bạn đã có thể đưa giá trị Grand Total lên đầu một cách dễ dàng.

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

 

[kyo]

Chiêu thứ 54: Sử dụng PivotTable với dữ liệu từ một workbook khác

Khi bạn tạo PivotTable trong Excel, bạn có rất nhiều lựa chọn khi chọn nguồn dữ liệu cho nó. Dễ dàng nhất chính là việc bạn sử dụng dữ liệu có sẵn trên workbook. Tuy nhiên, đời không như là mơ và nhiều khi bạn vẫn rơi vào trường hợp là dữ liệu của bạn nằm ở trên một workbook khác.

Sử dụng tên dãy động là một cách rất tốt để giảm thiểu số lần làm mới (refresh) cần thiết cho PivotTable của bạn cập nhật dữ liệu. Bởi vì khi bạn không thể tham chiếu đến tên dãy động từ một workbook khác, điều đó cũng có nghĩa là bạn đã ngăn chặn nguy cơ PivotTable tham chiếu đến hàng ngàn ô rỗng khác cũng như dung lượng file của bạn có thể sẽ tăng lên. Với cách làm này, bạn có thể lấy dữ liệu từ workbook khác và sử dụng nó làm nền tảng dữ liệu cho PivotTable của bạn trên chính workbook đó mà không cần phải tham chiếu từ bên ngoài. Chúng ta cùng tham khảo cách thực hiện nhé. Giả sử dữ liệu của bạn cần như hình dưới đây:

1. Tại workbook sẽ chứa PivotTable, bạn hãy tạo một sheet mang tên Data.
2. Mở workbook chứa dữ liệu mà bạn muốn, và bạn phải bảo đảm rằng sheet chứa dữ liệu của bạn đang được kích hoạt (tức là bạn đang mở sheet đó). Tại một ô trống nào đó trên sheet, bạn hãy gõ công thức =IF(A1="","",A1) với A1 là tiêu đề đầu tiên của dữ liệu bảng.
   
   
3. Sau đó hãy cut ô bạn vừa đánh công thức (Ctrl + X) và paste nó (Ctrl + V) qua ô A1 của sheet Data ở workbook sẽ chứa PivotTable của bạn. Với việc này, bạn đã có một liên kết với một workbook khác.
   
   
4. Chọn thẻ Formulas -> tại Defined Names, chọn Define Name (với Excel 2003, chọn Insert -> Name -> Define).
5. Tạo một cái tên tại sheet Data với tên là PivotData (gõ tại dòng “Names:” với công thức là =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) (gõ tại dòng “Refers To:”) rồi OK.
   
   
6. Sau đó, bạn hãy cho đoạn code VBA vào bằng cách nhấn Alt + F11 -> chọn This Workbook.

Private Sub Workbook_Open()
    With Worksheets("Data")
        .Range("2:100").Clear
        .Range("1:1").AutoFill .Range("1:100")
        .Range("2:100") = .Range("2:100").Value
    End With
End Sub

​

​

​

7. Sau đó, hãy lưu lại file Excel của bạn.​

​

Ở đoạn code trên, bạn có thể thấy con số 100. Đó chính là con số biểu thị dữ liệu tồn tại đến hàng thứ 100. Nếu dữ liệu của bạn ít hơn với con số này, hãy thay đổi cho phù hợp.

Một điều lưu ý rằng, vì bạn đã sử dụng đến macro, hãy bảo đảm rằng bạn đã bật chức năng chạy macro trong Excel, nếu không thì đoạn code của bạn sẽ không thể nào chạy được. Đoạn code bạn vừa thêm vào sẽ tự động chạy mỗi khi bạn mở file. Và nếu như bạn muốn, bạn có thể giấu sheet chứa dữ liệu bằng cách nhấn chuột phải vào sheet đó và chọn Hide, hoặc bạn có thể làm theo chiêu thứ 5: "Ẩn sheet sao cho người dùng không thể dùng lệnh unhide để hiện ra" đã được giới thiệu ở trước đó, bạn có thể tham khảo tại đây.

Bây giờ, bạn hãy chọn bất kỳ một ô nào đó mà bạn sẽ đặt PivotTable, chọn thẻ Insert -> chọn PivotTable -> PivotTable -> tại dòng “Select a Table or Range” gõ =PivotData. Với Excel 2003, chọn Data -> PivotTable and PivotChart Report… -> xuất hiện hộp thoại Wizard, chọn Microsoft Excel List or Database tại dòng “Where is the data that you want to analyze?” và nhấn Next -> gõ =PivotData rồi nhấn Finish.

​

​

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

 

[kyo]

Chiêu thứ 55: Cắt rời một “miếng” từ một “chiếc bánh đồ thị” (Pie Chart)

Mặc dù biểu đồ hình bánh (Pie Chart) vô cùng dễ nhìn nhưng đôi khi bạn muốn tách rời một phần nào đó ra nhằm nhấn mạnh một điều gì đó. Tuy nhiên, với thiết lập mặc định của Excel, bạn sẽ chỉ có những phần đều nhau và được “tập hợp” lại. Và chiêu thứ 55 này sẽ chỉ cho bạn, chỉ cần 2 cú nhấp chuột, bạn sẽ dễ dàng lấy được “miếng bánh” bạn muốn dễ dàng như dùng dao cắt bánh vậy.

Để bắt đầu, bạn hãy tạo một biểu đồ đơn giản như hình dưới đây.

​

Sau đó, bạn hãy nhấn chuột vào trong biểu đồ, và nhấn chuột lần nữa vào phần mà bạn muốn tách nó ra. Tiếp đến, bạn hãy kéo nó ra bên ngoài biểu đồ. Bạn cũng có thể làm tương tự để kéo nốt các phần còn lại của biểu đồ ra bên ngoài. Và cách làm này cũng áp dụng được cho cả biểu đồ bánh 3D.

​

Nếu bạn muốn tách rời tất cả các phần ra bên ngoài. Bạn chỉ cần nhấn chuột vào biểu đồ và kéo ra bên ngoài, lập tức các phần sẽ tách rời nhau ra. Bạn càng kéo thì các phần đó sẽ ngày càng xa nhau và tất nhiên là sẽ ngày một thu nhỏ lại. Cũng với kỹ thuật tương tự, bạn kéo vào thì các phần sẽ dần to lên và dính liền lại với nhau tại tâm.

​

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

 

[kyo]

Chiêu thứ 56: Hai biểu đồ trong một biểu đồ hình bánh

Nhiều người vẫn thường nghĩ, một biểu đồ thì chỉ có thể thể hiện một tổ hợp giá trị mà thôi, nhưng với chiêu thứ 56 này, bạn sẽ học được cách thể hiện hai tổ hợp giá trị vào trong một biểu đồ hay có thể gọi nôm na là hai biểu đồ trong một.

Để bắt đầu, trước tiên chúng ta cần tạo một biểu đồ dựa trên các dữ liệu như trong hình dưới đây bằng cách. Bạn hãy tạo biểu đồ cho “Câu hỏi 1”.

​

Tiếp theo, bạn hãy chọn biểu đồ -> chọn thẻ Design -> ở Data, chọn Select Data -> ở bảng Legend Entries (Series), chọn Add. Tại dòng “Series Name:”, chọn ô C1 và tại dòng “Series Values:” chọn C2:C5 và OK. Với Excel 2003, nhấn chuột phải vào biểu đồ và chọn Format -> Source Data -> Series -> chọn Add -> chọn C1 cho dòng “Name” và C2:C5 cho dòng “Values” -> OK.

​

Sau khi làm xong, biểu đồ của bạn vẫn chẳng có gì thay đổi đúng không nào? Bây giờ chúng ta cần dựng một trục thứ hai. Để làm điều đó, bạn vào thẻ Format -> ở Current Selection, tại danh sách xổ xuống, chọn Series “Câu hỏi 1” -> chọn Format Selection cũng tại Current Selection -> chọn Secondary Axis -> Close. Với Excel 2003, nhắp chuột hai lần vào biểu đồ để chọn nó, sau đó chọn Format Data Series -> tại thẻ Axis, chọn Secondary Axis -> OK.

​

Lần này, biểu đồ vẫn chẳng có gì khác biệt cả, có chăng sự khác biệt chỉ là tên biểu đồ đã bị biến mất mà thôi. Thực ra thì biểu đồ thứ hai đang nằm phía dưới biểu đồ thứ nhất. Do đó, để có thể thấy nó, bạn cần phải tách rời các phần của biểu đồ thứ nhất ra bằng cách, chọn biểu đồ và kéo từng miếng ra khỏi tâm. Sau đó, bạn hãy chọn biểu đồ thứ hai và lại tiếp tục kéo ra, mục đích của công việc này nhằm làm cho biểu đồ thứ hai to lên và biểu đồ thứ nhất bị nhỏ đi. Sau đó, bạn lấy từng miếng của biểu đồ thứ nhất và kéo lại tâm, thế là bạn sẽ được kết quả như hình dưới đây.

Ngoài ra, vẫn còn một cách khác ở bước cuối cùng này, đó là bạn hãy làm như sau (cách của ptm0412): Nhấn chọn nguyên biểu đồ trên mặt -> Nắm kéo toàn bộ các "miếng bánh" ra xa khỏi tâm, bán kính sẽ bị nhỏ đi -> Chọn riêng từng miếng bánh, và lôi trở vào tâm.

​

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

 

[kyo]

Chiêu thứ 57: Tạo biểu đồ có thể thay đổi theo dữ liệu

Nếu bạn sử dụng tên dãy động thay vì tham chiếu tương đối, biểu đồ của bạn sẽ được vẽ lại vào thời điểm mà bạn thêm mới dữ liệu. Để có thể làm rõ vấn đề này, bạn hãy tạo một bảng dữ liệu như hình dưới đây:

​

Nếu bạn vẫn còn thắc mắc về tên dãy động, mời bạn đọc lại chiêu thứ 47: “Tạo dãy có thể mở rộng và thu hẹp” tại đây.

Bạn hãy vào thẻ Formulas -> ở Defined Names, chọn Define Name (với Excel 2003, chọn Insert -> Name -> Define) và tạo một cái tên là NhietDo_Ngay với công thức là =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1). Bạn lưu ý trong công thức có -1 tại COUNTA vì với phép trừ này, bạn sẽ bảo đảm được rằng tiêu đề sẽ không được tính vào.

​

Trên công thức bạn nhìn thấy, cột A đã được tham chiếu cho công thức COUNTA. Sự thật là sẽ tốt hơn nếu bạn chỉ cho tham chiếu một số lượng ô nhỏ phù hợp với dữ liệu của bạn thay vì là tham chiếu cả cột với cả hàng ngàn ô không cần thiết. Tuy vậy, với những phiên bản Excel gần đây thì vấn đề này cũng rất nhỏ mà thôi bởi vì Excel bây giờ đã xử lý cả ngàn ô một cách nhanh chóng hơn rất nhiều.

Tiếp đó, bạn hãy tạo tiếp một cái tên là NhietDo với công thức là =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1).

​

Bây giờ, để tạo một biểu đồ sử dụng những gì bạn vừa làm ở trên, bạn hãy quét chọn A1:B11, chọn thẻ Insert -> ở Charts, chọn Column và chọn biểu đồ đầu tiên (2D Clustered Column Chart).

​

Sau đó, chọn thẻ Design -> ở Data, chọn Select Data -> Ở phần Legend Entries (Series), chọn “Nhiệt độ” -> Edit -> thay đổi ở dòng “Series Values:” là =Sheet1!NhietDo -> OK.

​

Trở về hộp thoại Select Data Source -> chọn “Ngày” -> chọn Edit ở phần Horizontal (Category) Axis Label -> gõ vào =Sheet1!NhietDo_Ngay -> OK -> OK.

​

Với Excel 2003, bạn có thể thêm công thức ở Bước 2 của Chart Wizard tại thẻ Series, dòng “Values:” là =Sheet1!NhietDo và dòng “Category (X) axis labels:” là =Sheet1!NhietDo_Ngay.

Và kết quả của bạn có được sẽ như hình dưới đây:

​

Lưu ý: Ở phần gõ công thức, bạn phải bảo đảm rằng bạn có gõ cả tên của sheet. Bởi vì nếu không, bạn sẽ gặp phải lỗi như thế này:

​

Vậy là bây giờ, mỗi khi bạn thay đổi dữ liệu, biểu đồ của bạn cũng sẽ tự động được cập nhật theo.

​

Vẽ biểu đồ dựa trên 10 dữ liệu gần nhất

Một cách áp dụng khác cho biểu đồ là bạn có thể thiết kế để nó tự động cập nhật dựa trên 10 dữ liệu gần nhất. Bạn cũng làm như những gì đã từng làm ở trên, chỉ khác là, với tên NhietDo_Ngay, bạn thay bằng công thức: =OFFSET($A$1,COUNTA($A:$A)-10,0,10,1) và tên NhietDo, bạn thay bằng công thức: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-10,1,10,1)

Nếu bạn muốn thay đổi con số 10 bằng một con số khác, chẳng hạn như 20 dữ liệu gần nhất chẳng hạn, bạn hãy lưu ý đến số 10 trên công thức (đã được tô đậm) và thay bằng con số bạn muốn.

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

 

[kyo]

Chiêu thứ 58: Sử dụng công cụ điều khiển tự tạo để tương tác với biểu đồ

Như đã biết với chiêu thứ 57, bạn có thể sử dụng tên dãy động để tăng độ linh hoạt cho biểu đồ. Và bây giờ tôi mang đến cho bạn một tin vui khác, bạn cũng có thể dùng tên dãy động để tạo giao diện điều khiển dữ liệu trong biểu đồ. Bằng cách liên kết tên dãy động với công cụ điều khiển tự tạo, người dùng chỉ việc sử dụng công cụ tự tạo là đã có thể thay đổi dữ liệu trong biểu đồ. Cụ thể hơn trong chiêu thứ 58 này, bạn sẽ học được cách tạo thanh trượt và danh sách xổ xuống những lựa chọn dữ liệu mà người dùng muốn xem.

Sử dụng thanh trượt

Trong ví dụ dưới đây, bạn sẽ sử dụng thanh trượt để thể hiện đầy đủ trên biểu đồ dữ liệu của 12 tháng. Để bắt đầu, bạn hãy tạo một bảng dữ liệu như sau:

View attachment 224652
​

Sau đó, bạn hãy chọn thẻ Formulas -> ở Defined Names, chọn Define Name (với Excel 2003, chọn Insert -> Name -> Define) -> tạo tên là Thang và gõ công thức =OFFSET($B$2,0,0,$C$2,1).

​

Thông qua hàm OFFSET, bạn có thể sử dụng ô C2 như một thông số cho tên “Thang” mà bạn vừa tạo để qua việc thay đổi con số trong ô, bạn sẽ có một cái tên có thể mở rộng hay thu hẹp theo con số đó. Chẳng hạn, nếu bạn để trong C2 là 5, bạn sẽ có vùng B2:B7.

Một gợi ý nho nhỏ: Nếu bạn không muốn người dùng thấy được ô C2, bạn có thể giấu nó bằng cách bấm chuột phải vào ô C2 -> chọn Format Cells -> Custom -> gõ ;;; -> OK.

​

Tiếp theo, bạn hãy tạo một biểu đồ (với dữ liệu này, dùng biểu đồ đường hay biểu đồ cột là tốt nhất). Tôi sẽ chọn biểu đồ đường.

​

Tạo biểu đồ xong, bạn hãy chọn biểu đồ đó, vào thẻ Design -> ở Data, chọn Select Data -> nhấn Edit tại Legend Entries (Series) -> tại dòng Series Values, bạn hãy gõ =Sheet1!Thang -> OK -> OK. (Với Excel 2003, tại Bước 2 của Chart Wizard, thẻ Series, dòng “Values:”. Sau khi bạn làm xong, bạn sẽ có được biểu đồ như hình dưới đây:

​

Rõ ràng trong biểu đồ, để có thể thấy hết được 12 tháng, bạn sẽ cần một thanh trượt để có thể “kéo qua kéo lại”. Hãy chọn thẻ Developer -> tại Controls, chọn Insert -> chọn biểu tượng Scroll Bar tại Form Controls (với Excel 2003, bạn hãy vào View -> Toolbars -> Forms, bạn sẽ có được thanh công cụ Forms trên màn hình và chọn biểu tượng Scroll Bar).

​

Một số bạn sẽ thắc mắc không biết kiếm thẻ Developer ở đâu trên Excel, đó là bởi vì bạn chưa thiết lập. Để thiết lập nó, bạn hãy nhấn vào nút Office -> Excel Options -> đánh dấu kiểm vào dòng “Show Developer tab in the Ribbon” -> OK.

​

Sau khi bạn đã bấm chọn biểu tượng Scroll Bar, bạn hãy vẽ nó trên biểu đồ. Vẽ xong, bạn hãy nhấn chuột phải vào nó -> Format Control… -> thiết lập như trong hình -> OK.

​

Bây giờ, biểu đồ của bạn đã hoàn thiện rồi đấy. Bạn có thể kéo thanh trượt để thay đổi giá trị của C2 hoặc trực tiếp gõ một con số vào thẳng ô C2 và xem kết quả thay đổi trên biểu đồ.

​

Sử dụng danh sách xổ xuống

Lần này, chúng ta sẽ cùng đến một ví dụ khác. Công việc đầu tiên mà bạn cần làm là tạo một dữ liệu mẫu sau đây. Bạn lưu ý là để tính trung bình, bạn dùng hàm AVERAGE.

​

Tạo tên dãy động bằng cách chọn thẻ Formulas -> ở Defined Names, chọn Define Name (với Excel 2003, chọn Insert -> Name -> Define), đặt tên HocSinh và gõ công thức =OFFSET($A$3,$G$4,1,1,5).

​

Tiếp tục tạo một cái tên khác với tên gọi là TenHocSinh và công thức là =OFFSET($A$3,$G$4,0,1,1).

​

Mục đích sử dụng ô G4 để dùng nó như một tham số trong hàm OFFSET, mỗi khi con số trong ô G4 thay đổi, biểu đồ sau này của bạn cũng sẽ dễ dàng thay đổi.

Bây giờ, bạn hãy tạo biểu đồ (biểu đồ cột - Clustered Column) với dữ liệu từ A8:F9.

​

Sau đó, bạn vào thẻ Design -> ở Data, chọn Select Data -> chọn “Học sinh E” -> Edit -> bạn gõ theo hình dưới đây -> OK -> OK (Ở Excel 2003, bước này sẽ được thực hiện tại Bước 2 của Chart Wizard).

​

Tiếp theo, bạn sẽ cần một ComboBox nhằm tạo một danh sách xổ xuống. Hãy vào thẻ Developer -> ở Controls, chọn Insert (với Excel 2003, View -> Toolbars -> Forms) -> chọn biểu tượng Combobox và vẽ lên biểu đồ. Chọn nó và nhấn chuột phải -> Format Control… và thiết lập như hình sau đây -> OK.

​

Để kết thúc và cũng để trang trí cho thêm phần sống động, chọn B2 và gõ công thức sau: =CONCATENATE("Xem kết quả của ",INDEX(A4:A8,G4)). Và bây giờ, bạn hãy chọn một học sinh và tận hưởng kết quả đi nào.

​

Xin nhắc với bạn rằng, để giấu kết quả của ô G4, bạn có thể thực hiện tương tự như những gì đã trình bày ở phía trên. Bên cạnh đó, trong Excel 2007, những công cụ điều khiển mà bạn đã vẽ sẽ nổi trên biểu đồ và khi bạn di chuyển biểu đồ, những công cụ đó sẽ vẫn đứng im tại chỗ đó. Ngoài ra, với Excel 2003, khi bạn chọn biểu đồ, các công cụ đó sẽ bị ẩn đi (bị biểu đồ đè lên), nhưng trường hợp đó không diễn ra ở Excel 2007.

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

 

[kyo]

Chiêu thứ 59: Bốn cách cập nhật biểu đồ nhanh chóng

Mặc dù tạo biểu đồ mới thì rất tuyệt vời, tuy nhiên sẽ tốt hơn nếu bạn cập nhật biểu đồ để nó có thể linh hoạt đáp ứng nhu cầu của bạn. Và với chiêu thứ 59 này, bạn sẽ học được cách giảm bớt khối lượng công việc cần phải làm mỗi khi muốn thay đổi dữ liệu trong biểu đồ.

Sử dụng kéo và thả

Bạn có thể thay đổi hoặc thêm mới một cách nhẹ nhàng bằng thao tác kéo dữ liệu và thả vào biểu đồ. Sau đó, Excel sẽ tự động phân loại dữ liệu của bạn là thay đổi từ dữ liệu cũ hoặc thêm mới. Mặc dù vậy, bạn vẫn có thể “yêu cầu” Excel để cho bạn tự quyết định.

Để có thể hiểu rõ hơn, bạn hãy tạo dữ liệu mẫu và biểu đồ như sau (lưu ý rằng bạn chỉ tạo biểu đồ cho vùng A15, còn dòng 6 bạn hãy chừa ra):

​

Quét chọn vùng A66 -> rê con trỏ chuột vào đường viền ô A6 sao cho ở con trỏ chuột xuất hiện dấu mũi tên bốn hướng -> nhấn giữ chuột phải kéo vào biểu đồ và thả ra, lập tức hộp thoại Paste Special sẽ xuất hiện -> chọn tùy chọn Columns -> OK.

​

Một bật mí nhỏ: Do tùy chọn Columns có thể xem là mặc định, do đó bạn cũng không cần phải nhấn giữ chuột phải kéo vào biểu đồ mà nhấn chuột trái kéo vào luôn, lập tức bạn cũng sẽ nhận được cùng một kết quả mà không cần phải thông qua hộp thoại Paste Special.

Và bạn hãy chiêm ngưỡng thành quả đi nào.

​

Sử dụng thanh công thức

Khi nhấn chuột chọn biểu đồ, bạn hãy nhìn vào thanh công thức. Bạn sẽ thấy có một dòng công thức ở đó và cụ thể hơn, đó chính là hàm SERIES.

Hàm SERIES có tất cả 4 tham số (riêng biểu đồ bong bóng sẽ có 5 tham số). Cú pháp của hàm như sau:
=SERIES( [Tên biểu đồ] , [Giá trị dữ liệu X] , [Giá trị dữ liệu Y] , [Số thứ tự biểu đồ] )

Để hiểu rõ hơn, bạn hãy cùng tôi tạo biểu đồ cột với dữ liệu mẫu như hình dưới đây:

​

Bạn có thể thấy, công thức của biểu đồ này như sau: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1)

Cho phép tôi giải thích cho bạn rõ công thức này. Với tham số thứ nhất là tên biểu đồ, ô B1 có giá trị là “2012” và đó chính là tên như bạn thấy. Tham số thứ hai sẽ lấy dữ liệu cho X với tham chiếu vùng A2:A5, tương ứng “Tháng 1”, “Tháng 2”, “Tháng 3”, “Tháng 4”. Và tham số thứ 3 lấy dữ liệu cho Y dùng tham chiếu B2:B5 tương ứng. Còn tham số cuối cùng chính là số thứ tự mà biểu đồ này là biểu đồ đầu tiên nên có giá trị là 1 (giả sử bạn đã có một biểu đồ trước đó thì tham số này sẽ mang giá trị là 2).

Như vậy, để có thể thêm bớt dữ liệu, bạn chỉ cần làm một công việc nhỏ là sửa chữa tham chiếu trên công thức, lập tức bạn sẽ thấy sự khác biệt.

​

Tất nhiên, bạn cũng có thể thay giá trị X và Y từ tham chiếu thành những con số cố định hay mảng cố định, khi đó bạn phải dùng thêm cặp dấu ngoặc nhọn { }, chẳng hạn như sau: =SERIES("Biểu đồ",{"A","B","C","D"},{1,2,3,4},1)

Kéo dãn hay co hẹp đường biên giá trị trong biểu đồ

Trở lại ví dụ đầu tiên, tức là khi bạn đã có một biểu đồ thể hiện giá trị nhiệt độ hoàn chỉnh với 5 tháng và 3 năm. Lần này, để thay đổi giá trị của tháng 5 năm 2012 (cột màu vàng), bạn hãy nhấn thẳng vào cột đó và rê chuột ra biên, sau đó hãy kéo dãn hay thu hẹp để thay đổi.

​

Sử dụng Paste Special

Trở lại ví dụ thứ hai, tức là bạn đã có biểu đồ cột của 4 tháng đầu tiên trong năm 2012. Lần này, bạn muốn thêm mới dữ liệu của tháng 5. Ngoại trừ việc điều chỉnh công thức, bạn vẫn có thể làm như sau: quét chọn A6:B6 (tháng 5) -> Copy -> chọn biểu đồ -> chọn thẻ Home -> tại Clipboard, chọn Paste -> Paste Special -> thiết lập như ý bạn mong muốn và hãy xem kết quả.

​

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

 

[kyo]

Chiêu thứ 60: Tạo biểu đồ nhiệt kế

Trong Excel không có biểu đồ dạng nhiệt kế, do đó, nếu muốn, bạn phải tự xây dựng nó. Bằng cách tạo biểu đồ cột chứa các giá trị thuộc các hạng mục, chỉ cần một chút cố gắng điều chỉnh của bạn, biểu đồ nhiệt kế có thể được hình thành.

Trước tiên, bạn hãy tạo biểu đồ cột với dữ liệu như hình dưới đây:

​

Sau đó, bạn hãy chọn cột giá trị “Nhiệt độ hiện tại” -> chọn thẻ Format -> ở Current Selection, chọn Format Selection -> chọn Secondary Axis (với Excel 2003, bấm chuột phải vào cột giá trị “Nhiệt độ hiện tại” -> Format Data Series -> Axis -> Secondary Axis). Và biểu đồ của bạn sẽ có dạng như sau:

​

Tiếp theo, bạn hãy nhấn chuột phải vào trục bên trái -> chọn Format Axis và thiết lập như hình dưới đây. Bạn cũng làm tương tự với trục bên phải.

​

Và sau khi bạn làm xong, biểu đồ của bạn sẽ thay đổi như thế này:

​

Bạn tiếp tục đổi màu cho “Nhiệt độ tối đa” thành màu trắng và “Nhiệt độ hiện tại” thành màu đỏ và cả màu nền bên ngoài là màu trắng. Bên cạnh đó, bạn hãy xóa luôn cả đường lưới của biểu đồ. Ngoài ra, bạn hãy bấm chuột phải vào cột giá trị -> Format Data Series -> bạn hãy kéo Gap Width về bên trái để phần khung ngoài được to ra. Như vậy bạn sẽ có được một biểu đồ nhiệt kế hoàn chỉnh.

​

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

 

[kyo]

Chiêu thứ 61: Tạo biểu đồ cột có thể tùy biến chiều dài và chiều rộng

Có tuyệt không nếu bạn sở hữu một biểu đồ cột có thể dễ dàng tùy biến chiều dài và chiều rộng mỗi khi bạn thêm dữ liệu? Tất nhiên Excel không hỗ trợ trực tiếp biểu đồ tiện dụng thế này, tuy vậy, bằng cách sử dụng biểu đồ phân tán XY, bạn có thể biến nó thành biểu đồ cột như mong muốn.

Để cụ thể hơn, bạn hãy tạo một bảng dữ liệu như sau:

​

Sau đó, bạn hãy quét chọn vùng D2:E8 và chọn thẻ Insert -> ở Charts, chọn Scatter -> chọn Scatter with only Markers. Và để tạo tiêu đề cho trục Y, bạn có thể bấm vào biểu đồ, chọn thẻ Layout -> tại Labels, chọn Axis Titles -> Primary Vertical Axis Title -> chọn theo ý thích trình bày của bạn và gõ Chi phí (với Excel 2003, bạn hãy dùng Chart Wizard để tạo biểu đồ phân tán). Sau cùng, bạn sẽ được biểu đồ như sau:

​

Bây giờ, bạn hãy xóa đi đường kẻ và chú thích Series 1 trong biểu đồ bằng cách nhấn chọn và bấm Delete. Nhấn chọn trục X -> bấm chuột phải chọn Format Axis (với Excel 2003, chọn Scale) -> ngay tại phần Axis Options, chỉnh Minimum là 0, Maximum là 1, Major tick mark type là None và Axis labels là None (với Excel 2003, lựa chọn Tick Mark có thể tìm thấy ở thẻ Patterns) -> OK. Và bây giờ bạn hãy xem diện mạo mới của biểu đồ:

​

Bước tiếp theo, bạn hãy bổ sung thêm 3 thông số nữa ở trên dữ liệu mẫu như hình sau:

​

Sau đó, chọn biểu đồ -> thẻ Layout -> tại Analysis, chọn Error Bars -> More Error Bars Option… -> chỉnh sửa thông số như hình dưới đây (cách của ptm0412) -> OK -> Close.

​

Sau khi thực hiện xong công việc trên, biểu đồ của bạn sẽ có một chút kỳ dị.

​

Lần này, bạn hãy bấm vào một trong các đường kẻ ngang -> chọn thẻ Layout -> tại Analysis, chọn Error Bars -> More Error Bars Option… -> chỉnh sửa thông số như hình dưới đây (cách của ptm0412) -> OK -> Close.

​

Bây giờ, để có thể bỏ các nút trên biểu đồ, bạn hãy nhấn chuột phải vào nút -> Format Data Series -> chọn thẻ Marker Options -> None.

​

Với Excel 2003, bạn có thể thay đổi Error Bars bằng cách nhấn chuột hai lần vào một điểm trên biểu đồ để hiện hộp thoại Format Data Series. Tại “X and Y Error Bars” -> chọn “custom - range” và nhập =Sheet1!$F$2:$F$8. Tại “Y Error Bars” -> chọn “custom + range” và nhập =Sheet1!$H$2:$H$7. Ngoài ra, bạn cũng cần phải thiết lập tại mục Display là “Minus” cho “X Error Bars” và “Both” cho “Y Error Bars”.

Vậy là bạn đã hoàn tất biểu đồ rồi đó. Và để sử dụng thêm tiêu đề cho biểu đồ (trang trí cho biểu đồ thêm đẹp), bạn có thể sử dụng riêng Add-in của John Walkenbach (rất dễ xài) mà xin thứ lỗi cho kyo không đề cập cụ thể cách làm trên đây (vì sẽ có người thích xài Add-in khác, cũng có người sẽ tự tìm cách cho chính mình). Dù sao, chúc bạn vui với cách tùy biến biểu đồ trong chiêu này.

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

 

[kyo]

Chiêu thứ 62: Tạo biểu đồ đồng hồ đo tốc độ

Bạn có thể tạo một biểu đồ đồng hồ đo tốc độ đầy ấn tượng chỉ bằng cách kết hợp biểu đồ doughtnut (doughnut chart) và biểu đồ hình bánh (pie chart). Bên cạnh đó, một chút cách điệu bằng thanh trượt giúp bạn có thể điều khiển kim đo tốc độ trên biểu đồ.

Điều đầu tiên mà bạn cần phải làm chính là tạo một bảng dữ liệu như hình dưới đây:

​

Sau đó, bạn hãy quét chọn vùng B2:B5, chọn thẻ Insert -> tại Charts, chọn Other Charts -> chọn biểu đồ Doughtnut. Với Excel 2003, bạn tạo biểu đồ Doughtnut thông qua Chart Wizard. Tại bước 1 của Chart Wizard, chọn thẻ Standard Types, sau đó, tại Chart Type -> chọn biểu đồ Doughtnut đầu tiên -> sang bước 2, bạn chọn dữ liệu dùng để tạo biểu đồ và cứ Next cho đến hết. Biểu đồ của bạn sẽ có dạng như sau:

​

Bạn từ từ bấm hai lần vào miếng chiếm tỷ trọng lớn nhất (là miếng màu xanh dương) -> bấm chuột phải vào miếng đó -> Format Data Point (với Excel 2003, nhấn chọn Options) -> tại phần “Angle of first slice”, gõ vào ô là 90 -> qua thẻ Fill, chọn No fill -> Close. Biểu đồ của bạn sẽ có hình dạng mới như thế này:

​

Nhấn chọn biểu đồ -> chọn thẻ Design -> tại Data, chọn Select Data -> Add. Tại “Series Value”, chọn vùng C2:C13 -> OK. Với Excel 2003, bạn nhấn chuột phải vào biểu đồ -> chọn Source Data -> chọn thẻ Series -> nhấn nút Add. Biểu đồ của bạn sẽ biến đổi như sau:

​

Làm lại lần nữa như bước trên, nhưng thay vào đó là vùng E2:E5. Lần này, biểu đồ của bạn sẽ có thêm một lớp phía ngoài nữa.

​

Lần này, để có số đo hiện trên đồng hồ, bạn cần sử dụng Add-in của Walkenbach (tải xuống tại file đính kèm). Bạn cũng có thể lập trình VBA nếu bạn muốn hay dùng một add-in khác.

Bạn download file đính kèm về, nhấn chuột hai lần vào nó để sử dụng. Sau đó, bạn chọn biểu đồ -> chọn thẻ Add-ins -> tại Menu Commands, chọn JWalk Chart Tools -> chỉnh sửa như hình dưới đây -> OK.

​

Biểu đồ của bạn bây giờ đã có số đo. Bạn nhấn trực tiếp vào vòng thứ hai của biểu đồ -> nhấn chuột phải -> chọn Format Data Series -> chọn thẻ Fill -> chọn No fill -> Close (với Excel 2003, chọn thẻ Patterns, thiết lập đường viền và vùng trong thành None -> OK). Biểu đồ của bạn sẽ trông thế này:

​

Nhấn trực tiếp vào vòng ngoài (vòng thứ ba tính từ trong đếm ra) của biểu đồ -> chọn thẻ Design -> tại Type, chọn Change Chart Type -> chọn Pie -> chọn biểu đồ đầu tiên -> OK (với Excel 2003, nhấn chuột phải chọn Chart Type).

​

Tiếp theo, bạn hãy nhấn vào biểu đồ bánh và kéo nó ra nhằm mục đích cho nó thu nhỏ lại (đồng thời nó sẽ vỡ ra). Kế đến, hãy gắp từng miếng biểu đồ vừa vỡ ra và đưa nó vào tâm nhằm giúp nó khít với biểu đồ Doughtnut. Biểu đồ của bạn sẽ như sau:

​

Chọn biểu đồ hình bánh -> nhấn chuột phải -> chọn Format Data Series (Excel 2003, thẻ Options) -> phần “Angle of first slice”, gõ vào 90 và Close. Sau đó, bạn hãy chọn từng miếng trong biểu đồ hình bánh -> nhấn chuột phải -> Format Data Point -> thẻ Fill -> chọn No fill (ngoại trừ miếng thứ ba, miếng rất mảnh thì bạn chọn Solid Fill, chọn màu đen) -> Close.

​

Nếu bạn muốn có thêm một chút chỉ dẫn (Low, Normal, High), bạn nhấn chọn biểu đồ -> chọn thẻ Layout -> tại Labels, chọn Legend (với Excel 2003, nhấn chuột phải chọn Chart -> Data Labels -> Legend Key).

Để có thanh trượt nhằm kéo kim đo tùy thích, bạn chọn thẻ Developer -> tại Controls, chọn Insert -> Scroll Bar -> vẽ vào trong biểu đồ. Sau đó, nhấn chuột phải vào thanh trượt -> Format Control -> chọn thẻ Control -> tại Cell link, chọn ô F2 -> chỉnh sửa lại Minimum là 0 và Maximum là 100 -> OK.

​

Vậy là bạn đã xong biểu đồ đồng hồ đo tốc độ rồi đó. Cùng tận hưởng thành quả đi nào.

​

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

 

[kyo]

Chiêu thứ 63: Liên kết chữ trong biểu đồ với ô

Mỗi khi bạn cần sử dụng biểu đồ nhiều lần, sẽ thật tiện dụng biết bao nếu như chữ trong biểu đồ được liên kết với ô (chẳng hạn như tiêu đề, đề mục,…). Điều này có nghĩa là, mỗi khi bạn cần thay đổi chữ, bạn chỉ cần thay đổi giá trị trong ô là lập tức, chữ trong biểu đồ cũng được thay đổi theo.

Để bắt đầu chiêu này, bạn hãy tạo một bảng dữ liệu và biểu đồ hình cột như hình dưới đây:

​

Tiếp theo, bạn hãy gõ tiêu đề “Nhiệt độ trong 10 ngày của tháng 01” vào ô A18. Bây giờ, bạn cần phải thiết lập một liên kết giữa tiêu đề biểu đồ và ô A18. Bạn hãy nhấn vào tiêu đề của biểu đồ (là “Nhiệt độ”). Sau đó bạn nhấn vào thanh công thức, gõ dấu = và dùng chuột nhấn vào ô A18. (Nếu tiêu đề của bạn thuộc Sheet khác thì bạn chỉ việc gõ dấu = và dùng chuột di chuyển sang sheet khác, sau đó nhấn vào ô mà bạn muốn liên kết) -> Enter.

​

Bạn làm tương tự với các đề mục khác, như tên của trục X, trục Y,…

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

 

[kyo]

Chiêu thứ 64: Để ô rỗng hoặc ô có công thức FALSE không hiển thị trên biểu đồ

Khi thu thập dữ liệu cho biểu đồ, Excel sẽ không bỏ qua những ô rỗng cũng như những ô có công thức FALSE và nó sẽ biểu diễn những ô đó dưới dạng giá trị 0 trên biểu đồ. Điều này sẽ khiến cho biểu đồ của bạn xấu đi rất nhiều. Tuy nhiên, bạn đừng vội nản, bởi với chiêu thứ 64 này, bạn sẽ học được 2 cách loại bỏ những thứ “xấu xí” ấy ra khỏi biểu đồ của bạn, đó là “Giấu dòng hoặc cột” và “Sử dụng #N/A cho những ô rỗng”.

Giấu dòng hoặc cột

Bạn hãy thiết lập biểu đồ với dữ liệu mẫu như trên hình dưới đây.

​

Như bạn đã thấy đó, những ô rỗng và những ô chứa công thức FALSE sẽ khiến cho biểu đồ của chúng ta thể hiện giá trị 0. Cách đơn giản để bỏ chúng chính là bạn quét chọn dòng số 5 đến số 13 -> nhấn chuột phải -> chọn Hide. Lập tức biểu đồ của bạn sẽ được rút gọn lại và trông đẹp hơn.

​

Sử dụng #N/A cho ô rỗng

Trở về ví dụ trước, giả sử như bây giờ bạn không có một chuỗi giá trị 0 liên tục nữa nhưng mà là ngắt quãng (hoặc thậm chí không phải là giá trị 0 nhưng là ô rỗng) thì bạn sẽ xử lý thế nào? Hãy nhìn hình dưới đây, bạn sẽ thấy “nguyên bản” của việc có ô rỗng.

​

Trước tiên, chúng ta hãy thử ẩn dòng số 6, 9, 10 và 12 xem thế nào nhé.

​

Bạn có thể nhìn thấy được sự liên tục của đồ thị sau khi ẩn dòng. Tuy vậy, có vẻ như nó không được “trung thực” cho lắm do khoảng cách giữa các tháng đã không còn tuân theo quy luật nữa.

Có một cách khác là bạn hãy gõ công thức =NA() hoặc chỉ đơn giản là gõ #N/A vào các ô rỗng, các ô mang giá trị 0 và các ô có giá trị FALSE. Với cách dùng thế này, biểu đồ của bạn sẽ mang vẻ thân thiện hơn và thể hiện rõ ràng hơn sự tăng giảm.

​

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

 

[kyo]

Chiêu thứ 65: Thêm mũi tên chỉ hướng cho biểu đồ đường

Khi bạn tạo một biểu đồ đường trên Excel, sẽ rất dễ dàng để thay đổi các điểm nhấn trên biểu đồ cho phù hợp với yêu cầu của bạn. Tuy nhiên, để có được một mũi tên chỉ hướng ở cuối đường, bạn cần phải làm theo một số bước sau.

Trước tiên, bạn hãy tạo một dữ liệu mẫu cùng biểu đồ đường cho dữ liệu đó.

​

Tiếp theo, bạn nhấn vào điểm nhấn cuối cùng của biểu đồ đường. Bạn phải nhấn hai lần, bởi vì lần đầu Excel sẽ chọn tất cả các điểm, do đó phải nhấn tiếp lần thứ hai thì Excel mới hiểu và chọn điểm mà bạn nhấn. Kế đó, nhấn chuột phải -> Format Data Point -> chọn thẻ Line Style -> tại Arrow Settings, chọn hình mũi tên ở End type (bạn có thể tùy biến kiểu mũi tên cũng như độ dày) -> Close.

​

Lưu ý: Nếu bạn sử dụng biểu đồ đường có điểm nhấn xác định, bạn có thể loại bỏ điểm nhấn đó để làm cho mũi tên của bạn thêm đẹp bằng cách nhấn chọn điểm cuối -> nhấn chuột phải -> Format Data Point -> chọn thẻ Marker Fill -> No Fill -> chọn thẻ Marker Line Color -> No Fill -> Close.

Vậy là biểu đồ của bạn đã hoàn thành rồi đó. Khi bạn thay đổi số liệu cuối cùng trong dữ liệu mẫu, mũi tên của bạn cũng sẽ tự động thay đổi theo (hướng lên, hướng ngang hoặc hướng xuống).

​

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

 

[kyo]

Chiêu thứ 66: Thêm mũi tên cho trục X

Tất nhiên, chiêu thứ 66 này cũng có thể áp dụng tương tự để làm cho trục Y. Để có thể thêm mũi tên vào trục X, trước tiên, bạn cần một tọa độ “bù nhìn”. Bạn hãy tạo một bảng dữ liệu như hình dưới đây, trong đó, vùng A1:B6 là dữ liệu thật, còn A10:B10 là tọa độ bù nhìn. Và bạn hãy tạo một biểu đồ đường kẻ cho dữ liệu vùng A1:B6 này.

​

Dù rằng chiêu này dành cho tất cả các phiên bản Excel, nhưng phiên bản 2007 trở đi, cách làm sẽ đơn giản hơn rất nhiều.

Với Excel 2007, bạn chỉ cần nhấn chuột phải vào trục X -> Format Axis -> chọn thẻ Line Style -> tại thiết lập Arrow Settings, chọn mũi tên mà bạn muốn ở End Type -> Close.

​

1/ Nhấn chuột phải vào biểu đồ -> Source Data -> chọn thẻ Series -> Add -> Đặt tên và thiết lập giá trị như hình dưới đây -> OK.

​

2/ Kế đó, bạn hãy chọn Series “Mũi tên” và sửa trực tiếp trên công thức cho trục X, sau đó bạn tiếp tục chọn Series “Mũi tên” và chọn lại Chart Type thành XY Scatter Chart. Biểu đồ của bạn sau đó sẽ có dạng:

​

3/ Vẫn chọn Series “Mũi tên” -> chọn Format Data Series -> chọn thẻ Axis -> Primary axis -> OK. Lần này biểu đồ của bạn sẽ có hình dạng mới.

​

4/ Nhấn chuột phải vào biểu đồ -> Chart Options -> chọn thẻ Data Labels -> đánh dấu check vào Value -> OK.

​

5/ Bạn sửa lại Data Label của Series “Mũi tên” từ số 0 thành số 4. Sau đó, nhấn chuột phải vào nó -> Format Data Label -> chọn font Marlett -> chỉnh kích thước cho phù hợp -> OK. Biểu đồ của bạn khi hoàn tất sẽ thế này:

​

Bạn có thể áp dụng kỹ thuật tương tự để làm cho trục Y, tuy nhiên, để ra mũi tên thì ở trục Y, bạn phải dùng chữ t chứ không dùng số 4 như trục X

P/S: Cá nhân kyo thấy làm cho Excel 2003 vừa xấu lại vừa cực, làm trên Excel 2007 cho sướng

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

 

[ptm0412]

Để tạo mũi tên cho trục trong 2003, có 1 cách đơn giản hơn. Đó là định dạng Marker của trục giả.

Thí dụ tương tự như trên nhưng ta tạo 1 serie XY Scatter 2 point:

X|Y|

1​

|

0​

|

0​

|

9​

|


Cách đơn giản là copy vùng dữ liệu này, click chọn biểu đồ, Edit paste special, đánh dấu chọn như hình:

- Click chọn serie mới, nhấn chuột phải chọn change chart type và chọn XY Scatter. Excel sẽ tự tạo ra 2 trục mới.
- Xóa bỏ trục Y thứ 2 bằng cách click chọn nó và nhấn delete
- Chỉnh scale max cuả trục Y thành 9
- Chỉnh scale max của trục X thứ 2 thành 1. Đồng thời ẩn trục này: No line, no tickmark, no label
- Dùng công cụ Draw vẽ 1 mũi tên xoay ngang, ngắn thôi. Copy mũi tên này
- Click chọn serie XY, click 1 lần nữa để chỉ chọn 1 point (1,0), paste vào bằng ctrl V
- Xoay đứng cái autoshape mũi tên lên, copy 1 lần nữa
- Click chọn serie XY, click 1 lần nữa để chỉ chọn 1 point (0,9), paste vào bằng ctrl V

Viết thì dài dòng, nhưng thao tác sẽ rất nhanh.

Kết quả:

Xem thêm file. Bằng cách copy hình và paste, ta có thể cho riêng 1 point có hình bất kỳ:

 

[kyo]

Chiêu thứ 67: Chỉnh lại trục X khi sử dụng dữ liệu ngày tháng

Khi bạn dùng biểu đồ, Excel sẽ cố gắng để hiểu dữ liệu mẫu của bạn. Và nó đã làm rất tốt. Tuy nhiên, kết quả đôi khi lại không được như bạn mong muốn, đặc biệt là khi chú giải của trục X là ngày tháng. Nếu Excel biểu hiện sai, kết quả nhiều khi sẽ sai và trông rất kỳ cục.

Lý do trong trường hợp này là do Excel đã biến đổi dữ liệu trục X thành dữ liệu thời gian thay vì là dữ liệu chuỗi. Do đó mà nó đã tạo nên những khoảng cách để thể hiện giá trị thời gian cho chính xác. Để hiểu rõ hơn, bạn hãy sử dụng dữ liệu mẫu và tạo biểu đồ cột đầu tiên bình thường, bạn sẽ được kết quả như sau:

​

Để khắc phục hiện trạng trên, bạn làm các bước như sau:
Chọn trục X -> chọn thẻ Layout hoặc thẻ Format đều được -> ở Current Selection, chọn Format Selection -> chọn thẻ Axis Options -> chọn Text Axis tại phần Axis Type (với Excel 2003, nhấn chuột phải vào biểu đồ -> Chart Options -> Axes -> chọn Category option).

​

Vậy là biểu đồ của bạn sẽ trở lại như cũ đúng ý bạn muốn rồi đấy.

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

 

[kyo]

Chiêu thứ 68: Vị trí thông số của trục

Khi bạn tạo biểu đồ vạch (bar chart), các thông số trục của biểu đồ sẽ tự động được xác định. Tuy nhiên, để làm cho biểu đồ đẹp hơn, mang tính chuyên nghiệp hơn, chắc hẳn bạn sẽ cần thay đổi vị trí các thông số thể hiện trên trục của nó. Trong chiêu thứ 68 này, bạn sẽ được giới thiệu hai cách mà trong đó, cách thứ hai còn chỉ bạn cách đảo ngược thứ tự của trục nữa. Để rõ hơn, mời bạn cùng đọc.

Thay đổi vị trí thông số

Khi bạn tạo biểu đồ vạch có cả dữ liệu âm và dương, các vạch sẽ được cấu trúc thể hiện về bên trái lẫn bên phải biểu đồ. Và chính điều này sẽ khiến cho các thông số trục bị xấu do vạch biểu đồ “đè” lên. Để cụ thể hơn, mời bạn tạo một dữ liệu mẫu và biểu đồ vạch như sau, bạn sẽ thấy rất rõ điều này.

​

Như bạn thấy đấy, với dữ liệu âm, các thông số đã bị che khuất. Vậy để chỉnh lại vấn đề này, chúng ta sẽ làm như sau:

Chọn trục Y -> chuột phải -> chọn Format Axis (hoặc nhấn Ctrl + 1) -> chọn thẻ Axis Options -> chọn Low tại mục Axis Labels như hình dưới đây -> Close (với Excel 2003, chọn trục Y -> chuột phải -> Format Axis -> chọn Colors and Lines -> đánh dấu kiểm tại Low under Tick mark labels).

​

Vậy là các thông số của bạn sẽ được dời về góc trái biểu đồ như hình.

​

Đảo ngược thứ tự thông số

Bạn cũng tạo một dữ liệu mẫu và biểu đồ vạch như hình dưới đây.

​

Có thể thấy, các thông số và biểu đồ thể hiện ra theo thứ tự từ dưới lên so với thứ tự bạn liệt kê trên sheet. Tuy nhiên, đôi khi, bạn lại muốn nó thể hiện một thứ tự ngược lại. Vậy, bạn hãy làm như sau:

Chọn trục Y -> chuột phải -> Format Axis (hoặc nhấn Ctrl + 1) -> chọn thẻ Axis Options -> thiết lập các thông số như hình dưới đây -> Close (với Excel 2003, chọn “At maximum category” và “Value (y) axis crosses at maximum category” -> OK.)

​

Như vậy, bạn đã có thể thưởng thức biểu đồ được đảo ngược thứ tự rồi.

​

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

 

[kyo]

Chiêu thứ 69: Biểu đồ cơn lốc

Biểu đồ cơn lốc (cũng được biết đến là biểu đồ tháp dân số) không phải là dạng biểu đồ tiêu chuẩn trong Excel 2007, nhưng bạn có thể tạo ra nó cách dễ dàng từ biểu đồ vạch đơn giản (bar chart).

Biểu đồ cơn lốc thể hiện ra hai loại dữ liệu trên một vạch, bắt nguồn từ việc chia sẻ trên cùng một trục đứng (trục Y). Một loại dữ liệu sẽ nằm bên trái, loại dữ liệu còn lại sẽ nằm bên phải. Để cụ thể hơn, đầu tiên, bạn hãy thiết lập dữ liệu mẫu như sau và sử dụng biểu đồ vạch.

​

Sau đó, nhấn chọn thông số Nam -> chuột phải -> Format Data Series -> Secondary Axis -> Close (với Excel 2003, nhấn chuột hai lần liên tiếp vào thông số Nam -> thẻ Axis -> Secondary Axis -> OK).

​

Như vậy, hình dáng biểu đồ mới của bạn sẽ trông như thế này:

​

Tiếp theo, bạn hãy nhấn chọn trục X (trục nằm ngang ở phía dưới) -> chuột phải -> Format Axis -> thẻ Axis Options (với Excel 2003, chọn Scale) -> thiết lập thông số như hình sau -> Close.

​

Biểu đồ của bạn sẽ có dạng sau:

​

Tiếp tục với trục ngang phía trên -> chuột phải -> Format Axis -> thẻ Axis Options (với Excel 2003, chọn Scale) -> thiết lập thông số như sau -> Close.

​

Hình dáng mới của biểu đồ sẽ là:

​

Cuối cùng, bạn sẽ cần loại bỏ dấu âm trong các thông số phía dưới (do dữ liệu làm gì có số âm), bạn có thể định dạng lại bằng cách chọn trục X (bây giờ chỉ còn một trục duy nhất) -> chuột phải -> Format Axis -> thẻ Number -> chọn Custom tại Category -> gõ 0%;0% vào Format Code -> Add -> Close.

​

Như vậy, cuối cùng, bạn hoàn thành được biểu đồ vô cùng đẹp mắt.

​

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

 

[kyo]

Chiêu thứ 70: Thay thế điểm bằng hình

Sẽ thật tuyệt vời nếu bạn có thể tạo một biểu đồ sử dụng ký hiệu tùy chọn giúp cho dữ liệu được thể hiện một cách thông minh hơn, dễ hiểu hơn và chuyên nghiệp hơn. Với chiêu thứ 70 này, bạn sẽ học được cách biến một điểm trong biểu đồ thành một hình ngũ giác chỉ hướng.

Trước tiên, bạn hãy sử dụng biểu đồ đường (Line with Markers) với dữ liệu mẫu như sau (với Excel 2003, tạo bằng cách sử dụng Chart Wizard):

​

Tiếp theo, bạn chọn thẻ Insert -> tại Illustrations, chọn Shapes -> Block Arrows (với Excel 2003, Insert -> Picture -> AutoShapes -> Block Arrows), sau đó bạn hãy vẽ trên sheet và xoay cho mũi tên hướng về bên trái. Rồi bạn hãy copy nó (Ctrl + C) và nhấn chọn điểm trên biểu đồ và paste (Ctrl + V). Ngay lập tức, dấu chấm sẽ được thay bằng hình bạn vừa vẽ. Rất đơn giản phải không?

​

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

 

[kyo]

Chiêu thứ 71: Đánh dấu tên trục theo điều kiện

Với chiêu thứ 71 này, bạn sẽ học được cách đánh dấu tên trục X bằng màu theo vùng dữ liệu đã được định sẵn. Cụ thể, chẳng hạn bạn muốn tô màu đỏ khi dữ liệu của bạn bé hơn 25 và tô màu xanh khi dữ liệu bạn lớn hơn 25 chẳng hạn. Để làm được như vậy, chúng ta cùng xem hướng dẫn:

1/ Bạn hãy tạo một bảng dữ liệu như hình:

​

2/ Bạn hãy tạo biểu đồ cột theo vùng B2:B13.

​

3/ Chọn thẻ Layout -> Tại Current Selection, chọn Series “Màu đỏ”.

​

4/ Chọn thẻ Design -> Tại Type, chọn Change Chart Type (với Excel 2003, nhấn chuột phải vào nhãn “Màu đỏ” -> chọn Chart Type) -> chọn biểu đồ đường Line Chart.

5/ Làm tương tự với Series “Màu xanh”.

6/ Một lần nữa, bạn chọn Series “Màu đỏ” tại thẻ Layout -> tại Labels, chọn Data Labels -> More Data Label Options (với Excel 2003, nhấp chuột hai lần vào nhãn và chọn Data Labels) -> tại thẻ Label Options, đánh dấu chọn Category Name, đồng thời bỏ chọn Value phía dưới Category Name -> Close.

​

7/ Lặp lại tương tự với Series “Màu xanh”.

8/ Tiếp tục chọn Series “Màu đỏ” tại thẻ Layout -> tại Labels, chọn Data Labels -> chọn Below (với Excle 2003, nhấn chuột phải chọn nhãn “Màu đỏ” -> chọn thẻ Alignment -> chọn Label Position to Below).

​

9/ Cũng làm tương tự với Series “Màu xanh”.

10/ Nhấn chọn trục X cũ -> nhấn chuột phải chọn Format Axis (với Excel 2003, nhấp chuột hai lần vào trục X -> chọn thẻ Patterns) -> chọn thẻ Axis Options -> tại Major tick mark style, chọn None trong danh sách xổ xuống -> tại Axis Labels, chọn None trong danh sách xổ xuống. Việc này sẽ làm biến mất trục X cũ -> Close.

11/ Nhấn chọn nhãn “Màu đỏ” -> chọn thẻ Home (với Excel 2003, chọn thanh công cụ Formatting) -> đổi màu chữ thành màu đỏ.

12/ Làm tương tự cho nhãn “Màu xanh”, tuy vậy, nhãn này sử dụng màu chữ là màu xanh.

Vậy bạn đã thấy được thành quả của mình rồi đấy. Tuy rằng chiêu này khá vụn vặt nhưng cũng giúp bạn một cách tùy biến cho đồ thị của mình thêm màu sắc.

​

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