Sau khi đã học các hàm tra cứu và tham chiếu dữ liệu, chúng ta sẽ chuyển sang một nhóm kỹ năng rất quan trọng trong Microsoft Excel: quản lý, lọc, sắp xếp và phân tích dữ liệu.
Trong phần này, bạn sẽ làm việc với những tính năng thường xuyên xuất hiện trong thực tế và cũng rất quan trọng khi luyện thi MOS Excel, như:
Sort dữ liệu
Filter dữ liệu
Advanced Filter
Excel Table
Structured References
Remove Duplicates
Data Validation
Drop-down List
Conditional Formatting
Highlight Cells Rules
Top/Bottom Rules
Data Bars, Color Scales và Icon Sets
64. Sort dữ liệu
Sort là chức năng dùng để sắp xếp dữ liệu theo một hoặc nhiều điều kiện.
Ví dụ, ta có bảng:
| STT | Họ tên | Phòng ban | Lương |
|---|---|---|---|
| 1 | Nguyễn Văn A | Kế toán | 12000000 |
| 2 | Trần Văn B | Kinh doanh | 15000000 |
| 3 | Lê Văn C | Kỹ thuật | 10000000 |
| 4 | Phạm Văn D | Kinh doanh | 18000000 |
Ta có thể sắp xếp:
Họ tên từ A → Z.
Họ tên từ Z → A.
Lương từ nhỏ → lớn.
Lương từ lớn → nhỏ.
Phòng ban theo thứ tự A → Z.
Sắp xếp theo nhiều cột cùng lúc.
Sắp xếp nhanh
Chọn một ô trong cột cần sắp xếp.
Vào:
Data → Sort A to Z
hoặc:
Data → Sort Z to A
Ví dụ muốn sắp xếp cột Lương từ cao xuống thấp:
Data → Sort Largest to Smallest
Excel sẽ sắp xếp toàn bộ bảng theo cột được chọn.
Sort nhiều điều kiện
Chọn bảng dữ liệu → Data → Sort.
Trong hộp thoại Sort:
Sort by: chọn cột chính.
Sort On: chọn loại dữ liệu.
Order: chọn thứ tự.
Sau đó chọn:
Add Level
để thêm điều kiện thứ hai.
Ví dụ:
Phòng ban A → Z.
Trong mỗi phòng ban, lương từ cao → thấp.
Đây là kỹ năng rất quan trọng khi xử lý bảng dữ liệu lớn.
65. Filter dữ liệu
Filter cho phép chỉ hiển thị những dòng dữ liệu thỏa mãn điều kiện.
Ví dụ bảng có hàng trăm nhân viên nhưng chúng ta chỉ muốn xem:
Nhân viên thuộc phòng Kinh doanh.
Chọn bảng dữ liệu →:
Data → Filter
Excel sẽ thêm nút lọc vào tiêu đề các cột.
Nhấn nút lọc tại cột Phòng ban và chỉ chọn:
Kinh doanh
Excel sẽ tạm thời ẩn các dòng không phù hợp.
Filter theo số
Với cột số, Excel cung cấp các điều kiện như:
Equals
Does Not Equal
Greater Than
Greater Than Or Equal To
Less Than
Less Than Or Equal To
Between
Ví dụ:
Number Filters → Greater Than → 10000000
Kết quả chỉ hiển thị những nhân viên có lương lớn hơn 10 triệu.
Filter theo ngày
Excel cũng hỗ trợ lọc theo:
Today
Yesterday
Tomorrow
This Week
Last Week
This Month
Last Month
This Year
66. Advanced Filter
Advanced Filter là công cụ lọc dữ liệu nâng cao, cho phép sử dụng vùng điều kiện riêng biệt.
Ví dụ bảng nhân viên:
| Họ tên | Phòng ban | Lương |
|---|---|---|
| Nguyễn Văn A | Kế toán | 12000000 |
| Trần Văn B | Kinh doanh | 15000000 |
| Lê Văn C | Kỹ thuật | 10000000 |
| Phạm Văn D | Kinh doanh | 18000000 |
Ta có thể tạo vùng điều kiện:
| Phòng ban | Lương |
|---|---|
| Kinh doanh | >14000000 |
Sau đó sử dụng:
Data → Advanced
Excel sẽ sử dụng vùng điều kiện này để lọc dữ liệu.
Khi nào dùng Advanced Filter?
Advanced Filter hữu ích khi:
Điều kiện lọc phức tạp.
Có nhiều điều kiện.
Muốn sao chép kết quả lọc sang vị trí khác.
Muốn lưu một vùng điều kiện để sử dụng lại.
67. Excel Table
Excel Table là một trong những tính năng quan trọng nhất khi làm việc với dữ liệu.
Thay vì chỉ có một vùng dữ liệu thông thường, chúng ta có thể chuyển vùng dữ liệu thành Table.
Chọn dữ liệu →:
Insert → Table
hoặc sử dụng phím tắt:
Ctrl + T
Excel sẽ mở hộp thoại:
Create Table
Kiểm tra vùng dữ liệu và chọn:
My table has headers
nếu bảng đã có hàng tiêu đề.
Sau khi tạo Table, Excel sẽ tự động cung cấp:
Bộ lọc.
Định dạng bảng.
Tên cột.
Tự động mở rộng khi nhập dữ liệu mới.
Structured References.
Total Row.
Lợi ích của Table
Table giúp dữ liệu dễ:
Quản lý.
Lọc.
Sắp xếp.
Định dạng.
Viết công thức.
Mở rộng.
Đây là một kỹ năng rất đáng học kỹ khi luyện thi MOS Excel.
68. Structured References
Khi sử dụng Excel Table, Excel cho phép sử dụng tên cột thay vì địa chỉ ô truyền thống.
Ví dụ Table có tên:
Table1
và có cột:
Số lượng
Đơn giá
Ta có thể viết:
=[@Số lượng]*[@[Đơn giá]]
Thay vì:
=C2*D2
Đây được gọi là Structured Reference.
Ví dụ
Bảng:
| Sản phẩm | Số lượng | Đơn giá | Thành tiền |
|---|---|---|---|
| Chuột | 10 | 150000 | ? |
| Bàn phím | 5 | 300000 | ? |
Trong cột Thành tiền có thể sử dụng:
=[@Số lượng]*[@[Đơn giá]]
Excel sẽ tự động áp dụng công thức cho toàn bộ cột.
Tham chiếu toàn bộ cột
Có thể sử dụng:
=SUM(Table1[Số lượng])
Cách viết này dễ đọc hơn:
=SUM(B2:B100)
đặc biệt khi bảng có rất nhiều dữ liệu.
69. Remove Duplicates
Remove Duplicates dùng để tìm và loại bỏ các dữ liệu trùng lặp.
Ví dụ:
| Mã KH | Họ tên |
|---|---|
| KH001 | Nguyễn Văn A |
| KH002 | Trần Văn B |
| KH001 | Nguyễn Văn A |
| KH003 | Lê Văn C |
Trong bảng có khách hàng KH001 xuất hiện hai lần.
Chọn bảng →:
Data → Remove Duplicates
Excel sẽ hiển thị các cột để lựa chọn tiêu chí kiểm tra trùng.
Ví dụ chọn:
Mã KH
Sau đó nhấn:
OK
Excel sẽ giữ lại một bản ghi và loại bỏ bản ghi trùng.
Lưu ý
Remove Duplicates xóa dữ liệu thật khỏi bảng.
Vì vậy trước khi thực hiện, nên kiểm tra dữ liệu cẩn thận hoặc tạo một bản sao nếu dữ liệu quan trọng.
70. Data Validation
Data Validation dùng để kiểm soát dữ liệu mà người dùng được phép nhập vào ô.
Ví dụ:
Một ô chỉ được nhập số từ:
0 đến 100
hoặc một cột chỉ được phép nhập:
Nam
Nữ
hoặc chỉ được phép chọn:
Đang xử lý
Hoàn thành
Hủy
Thực hiện:
Data → Data Validation
Trong mục Allow, Excel cung cấp nhiều lựa chọn:
Whole Number
Decimal
List
Date
Time
Text Length
Custom
Ví dụ giới hạn số
Chọn:
Allow → Whole Number
Data → Between
Minimum → 0
Maximum → 100
Khi người dùng nhập giá trị ngoài phạm vi, Excel sẽ cảnh báo.
71. Drop-down List
Drop-down List cho phép người dùng lựa chọn dữ liệu từ một danh sách có sẵn thay vì tự nhập.
Ví dụ cột Trạng thái chỉ có:
Đang xử lý
Hoàn thành
Hủy
Tạo danh sách:
Đang xử lý,Hoàn thành,Hủy
Sau đó chọn vùng ô cần áp dụng:
Data → Data Validation
Tại:
Allow → List
Nhập nguồn dữ liệu vào Source.
Kết quả trong ô sẽ xuất hiện một nút mũi tên.
Người dùng chỉ cần chọn giá trị.
Lợi ích
Drop-down List giúp:
Nhập dữ liệu nhanh.
Hạn chế lỗi chính tả.
Chuẩn hóa dữ liệu.
Dễ dàng lọc và phân tích dữ liệu.
Ví dụ:
Nếu không dùng Drop-down, người dùng có thể nhập:
Hoàn thành
hoan thanh
Hoàn Thành
Hoànthành
Excel sẽ xem đây là những giá trị khác nhau.
72. Conditional Formatting
Conditional Formatting cho phép Excel tự động định dạng ô dựa trên điều kiện.
Ví dụ:
Điểm dưới 5 → nổi bật.
Điểm từ 8 trở lên → nổi bật.
Doanh thu cao → màu khác.
Ngày quá hạn → cảnh báo.
Vào:
Home → Conditional Formatting
Excel cung cấp nhiều nhóm chức năng.
Các nhóm quan trọng gồm:
Highlight Cells Rules
Top/Bottom Rules
Data Bars
Color Scales
Icon Sets
Ví dụ
Bảng điểm:
| Học viên | Điểm |
|---|---|
| Nguyễn Văn A | 9 |
| Trần Văn B | 7 |
| Lê Văn C | 4 |
| Phạm Văn D | 8 |
Chọn cột Điểm →:
Home → Conditional Formatting → Highlight Cells Rules → Less Than
Nhập:
5
Excel sẽ tự động làm nổi bật các ô có điểm dưới 5.
73. Highlight Cells Rules
Highlight Cells Rules là nhóm công cụ dùng để làm nổi bật các ô thỏa mãn điều kiện.
Các lựa chọn phổ biến:
Greater Than
Less Than
Between
Equal To
Text That Contains
A Date Occurring
Duplicate Values
Ví dụ Greater Than
Chọn cột Doanh thu:
Conditional Formatting → Highlight Cells Rules → Greater Than
Nhập:
100000000
Excel sẽ làm nổi bật những ô có doanh thu lớn hơn 100 triệu.
Ví dụ Duplicate Values
Chọn cột Mã sản phẩm:
Conditional Formatting → Highlight Cells Rules → Duplicate Values
Excel sẽ tự động đánh dấu các giá trị xuất hiện nhiều lần.
Tính năng này đặc biệt hữu ích khi kiểm tra dữ liệu trước khi sử dụng Remove Duplicates.
74. Top/Bottom Rules
Top/Bottom Rules dùng để xác định những giá trị cao nhất hoặc thấp nhất trong một vùng dữ liệu.
Các lựa chọn phổ biến:
Top 10 Items
Top 10%
Bottom 10 Items
Bottom 10%
Above Average
Below Average
Ví dụ Top 10
Giả sử có danh sách doanh thu của 100 nhân viên.
Chọn cột Doanh thu →:
Conditional Formatting → Top/Bottom Rules → Top 10 Items
Excel sẽ làm nổi bật 10 nhân viên có doanh thu cao nhất.
Có thể thay đổi số 10 thành:
5
để tìm Top 5.
Above Average
Có thể chọn:
Above Average
Excel sẽ tự động xác định giá trị nào lớn hơn giá trị trung bình của vùng dữ liệu.
75. Data Bars, Color Scales và Icon Sets
Đây là nhóm công cụ giúp trực quan hóa dữ liệu ngay bên trong bảng tính.
Data Bars
Data Bars tạo các thanh dữ liệu trong ô.
Ví dụ:
| Nhân viên | Doanh thu |
|---|---|
| A | 20 |
| B | 50 |
| C | 80 |
| D | 100 |
Khi áp dụng:
Conditional Formatting → Data Bars
giá trị càng lớn thì thanh càng dài.
Nhờ đó có thể nhanh chóng nhận biết dữ liệu lớn nhỏ mà không cần đọc từng con số.
Color Scales
Color Scales sử dụng màu sắc để biểu diễn mức độ của dữ liệu.
Ví dụ:
| Nhân viên | Điểm |
|---|---|
| A | 3 |
| B | 6 |
| C | 8 |
| D | 10 |
Excel có thể sử dụng thang màu để biểu diễn:
Thấp → Trung bình → Cao
Tính năng này rất hữu ích khi phân tích:
Điểm số.
Doanh thu.
Chi phí.
Lợi nhuận.
Số lượng.
Hiệu suất.
Icon Sets
Icon Sets sử dụng biểu tượng để biểu diễn dữ liệu.
Ví dụ:
Mũi tên lên.
Mũi tên ngang.
Mũi tên xuống.
Các biểu tượng trạng thái.
Các mức đánh giá.
Ví dụ:
| Sản phẩm | Tăng trưởng |
|---|---|
| A | 15% |
| B | 5% |
| C | -3% |
Sau khi áp dụng Icon Sets, Excel có thể hiển thị biểu tượng tương ứng để giúp người dùng nhanh chóng nhận biết xu hướng.
🎯 TỔNG KẾT PHẦN 8
Trong phần này, chúng ta đã học các công cụ quan trọng để quản lý và phân tích dữ liệu trong Excel.
| Bài | Nội dung |
|---|---|
| 64 | Sort dữ liệu |
| 65 | Filter dữ liệu |
| 66 | Advanced Filter |
| 67 | Excel Table |
| 68 | Structured References |
| 69 | Remove Duplicates |
| 70 | Data Validation |
| 71 | Drop-down List |
| 72 | Conditional Formatting |
| 73 | Highlight Cells Rules |
| 74 | Top/Bottom Rules |
| 75 | Data Bars, Color Scales và Icon Sets |
Sau khi hoàn thành Phần 8, bạn đã có thể xử lý một bảng dữ liệu tương đối hoàn chỉnh: sắp xếp → lọc → loại bỏ dữ liệu trùng → kiểm soát dữ liệu nhập → tạo bảng → định dạng theo điều kiện → trực quan hóa dữ liệu.
Ở Phần 9, chúng ta sẽ chuyển sang Biểu đồ và trình bày dữ liệu, bao gồm Column Chart, Bar Chart, Line Chart, Pie Chart, Doughnut Chart, Combo Chart, Data Labels, Sparklines và các kỹ thuật trình bày dữ liệu trực quan.
Không có nhận xét nào:
Đăng nhận xét