Microsoft Excel là một trong những ứng dụng vô cùng hữu ích và cần thiết trong công việc, đặc biệt liên quan đến các số liệu. Việc sử dụng thành thạo công cụ Excel giúp cho công việc thêm chuyên nghiệp và nhanh chóng hơn. Dưới đây là một số thủ thuật Excel quen thuộc chắc chắn sẽ giúp ích cho bạn.
Mục lục
Remove Duplicates
Excel giúp bạn dễ dàng dọn dẹp và sắp xếp dữ liệu của mình. Tính năng Remove Duplicates (xóa bản sao) rất dễ sử dụng và đúng như tên gọi của nó, tính năng này sẽ loại bỏ các giá trị trùng lặp khỏi tập dữ liệu. Để sử dụng xóa bản sao, chỉ cần chọn dữ liệu bạn muốn làm sạch và đi tới Data > Remove Duplicates.
Tìm kiếm thẻ Wild trong Excel
Có thể bạn đã quen với việc khởi chạy chức năng tìm kiếm bằng cách sử dụng CTRL + Fhoặc ⌘ + F. Bạn có thể mở rộng chức năng tìm kiếm bằng các ký tự đại diện. Điều này hữu ích khi bạn đang tìm kiếm các kết quả phù hợp không chính xác đáp ứng các tiêu chí cụ thể.
Có hai thẻ đại diện bạn có thể sử dụng:
?viết tắt của một ký tự*viết tắt của một hoặc nhiều ký tự
Nếu kết quả bạn đang tìm bao gồm ?hoặc *, hãy sử dụng toán tử dấu ngã ~ở phía trước (ví dụ: ~?hoặc ~*).
Sử dụng Index và Match để tìm kiếm bảng tính
Hàm Index trả về một giá trị dựa trên giao điểm của hàng và cột.
Ví dụ sau cho chúng ta thấy hàm chỉ mục bao gồm:
- Mảng chúng ta muốn được tìm kiếm. [A1: B2 đại diện cho phần của bảng mà chúng ta muốn truy vấn, cụ thể là vùng giữa ô A1 (phía trên bên trái) và ô B2 (phía dưới bên phải). Khu vực này bao gồm 2 hàng đầu tiên và 2 cột đầu tiên trong bảng].
- Các giá trị muốn được trả về. Trong ví dụ này, đang yêu cầu các giá trị trong cột đầu tiên, hàng đầu tiên (1,1) trong mảng.
Hàm Match tìm kiếm một giá trị trong một mảng và trả về vị trí tương đối của nó. Ví dụ: giả sử chúng ta muốn vị trí tương đối của “blue” trong mảng bao gồm các ô A1 và A2:
Bằng cách kết hợp các chức năng Index và Match, bạn có thể tìm kiếm trong toàn bộ bảng tính của mình các giá trị:
=INDEX(your-values, MATCH(value-to-lookup, lookup-column, sorting-identifier))
Proper
Hàm Proper rất hữu ích nếu tập dữ liệu chứa các giá trị văn bản được format kỳ lạ. Ví dụ: giả sử rằng ô A1 có nội dung “i LoVe tO sInG”, sử dụng PROPER(A1), bạn sẽ thấy I Love To Sing.
Khóa tài liệu tham khảo bằng cách sử dụng ký hiệu Dollar
Như bạn đã biết, Excel đại diện cho các ô bằng cách sử dụng các cặp hàng / cột chẳng hạn như A1. Khi tham chiếu đến một ô cụ thể, bạn có thể khóa giá trị cột hoặc hàng để tham chiếu không thay đổi đột ngột.
- Nếu bạn muốn khóa tham chiếu hàng, hãy nhập
A$1. - Nếu bạn muốn khóa tham chiếu cột, hãy nhập
$A1. - Nếu bạn muốn khóa tham chiếu hàng và cột, hãy nhập
$A$1.
Điều này đặc biệt hữu ích khi làm việc với cơ sở dữ liệu lớn hoặc khi bạn muốn thực hiện các phép tính theo hàng (hoặc từng cột) vì bạn có thể đảm bảo rằng công thức tham chiếu đến các ô chính xác.
Concatenate
Sử dụng Concatenate, bạn có thể kết hợp các giá trị của nhiều ô thành một ô duy nhất:
=CONCATENATE(F3,G3,H3,I3,J3)
Đây là kết quả:
Ngoài ra, bạn có thể nối chỉ bằng ký hiệu và:=F3&G3&H3&I3&J3.
Câu lệnh If
Câu lệnh “if” cho phép bạn xuất giá trị mà bạn chọn là hợp lệ (hoặc ngược lại, sai). Ví dụ: giả sử bạn đã nhập thông tin sau:
=IF(B2>C2,"Pass", "Fail")
Điều này tương tự như nói, “Nếu giá trị của ô B2 lớn hơn giá trị của ô C3, hãy điền vào ô này bằng “Pass”. Nếu không, hãy điền vào ô này bằng “Fail”.
Bạn có thể xây dựng dựa trên các câu lệnh “If” đơn giản bằng cách sử dụng SUMIF, COUNTIFvà AVERAGEIF. Ví dụ: nếu bạn có một tập hợp các kết quả khảo sát, bạn có thể sử dụng COUNTIF để đếm số hàng mà người trả lời là nữ bằng cách sử dụng:
=COUNTIF(B17:B19, "Female")
Format có điều kiện
Có thể bạn đã quen với việc sử dụng format có điều kiện để mã màu cho các ô của mình, nhưng bạn có thể sử dụng các bộ biểu tượng tích hợp để biểu thị các điểm ưa thích cụ thể. Bạn có thể truy cập khu vực Format có điều kiện thông qua dải băng trang chủ.
Di chuột qua Icon Sets và nhấp vào More rules.
Tạo các quy tắc xác định biểu tượng nào được sử dụng khi nào.
Trong ví dụ dưới đây, đã sử dụng các biểu đồ thanh khác nhau để phản ánh lượng thời gian dành cho một chủ đề nhất định.
3-D Sum
Nếu bạn có nhiều bảng tính có format giống hệt nhau, bạn có thể dễ dàng tính toán tổng các giá trị trải rộng trên các bảng tính.
Giả sử rằng bạn có một bảng tính ngân sách để theo dõi chi phí hàng tuần của mình. Đối với mỗi tuần, bạn có một tab riêng trong bảng tính (tab cho tuần đầu tiên là Week 1, tab cho tuần thứ hai là Week 2,…). Sử dụng 3-D Sum, bạn có thể cộng tổng chi tiêu của mình. Ví dụ: bạn có thể muốn biết tổng số tiền bạn đã chi cho xăng trong vài tuần. Để làm điều này, bạn nhập:
=SUM('Week 1:Week 7'!B10)
Goal Seek
Chức năng Goal Seek rất hữu ích để xác định các điều kiện cần thiết cho một kết quả cụ thể. Một số ví dụ bao gồm:
- Với doanh số bán hàng của tôi từ tháng 1 đến tháng 3, tôi cần bán gì trong tháng 4 để đạt được một ngưỡng nhất định?
- Nếu tôi đã tiết kiệm và kiếm được tiền lãi trên tài khoản của mình trong mười năm qua, tôi cần tiết kiệm thêm bao nhiêu để tôi có 2 triệu đô la trong ngân hàng khi tôi nghỉ hưu ở tuổi 67?
Khi bạn đã điền dữ liệu của mình trong Excel, bạn có thể khởi chạy chức năng Goal Seek bằng cách chuyển đến Tools > Goal Seek.
Trong ví dụ bên dưới đã cung cấp chi tiết thu nhập dựa trên giá tính trong một tháng nhất định.
Muốn biết mình cần bán bao nhiêu trong tháng 4 để đạt được mục tiêu 1.500 đô la.
Mặc dù ví dụ này đơn giản, Goal Seek thường được sử dụng bởi các nhà tư vấn, chuyên gia bán hàng và bất kỳ ai quan tâm đến việc tối ưu hóa một giá trị không xác định.
Pivot Tables
Nếu bạn có một lượng lớn dữ liệu và bạn muốn có được cái nhìn “toàn cảnh” về dữ liệu của mình, bạn có thể sử dụng bảng tổng hợp.
Để tạo bảng tổng hợp, hãy đi tới Data > Summarize with Pivot Tables. Bạn sẽ được yêu cầu chọn dữ liệu bạn muốn phân tích và nơi bạn muốn đặt bảng tổng hợp.
Tiếp theo, bạn sẽ thấy trình tạo PivotTable. Bạn bao gồm nhiều (hoặc ít) cột tùy thích và bạn có thể chọn lọc dữ liệu của mình, thêm cột hoặc hàng mới và tạo các biến tóm tắt mới.
Sparklines
Sparklines là biểu đồ cung cấp hình ảnh trực quan đơn giản về dữ liệu. Bạn có thể nhúng khá nhiều toàn bộ biểu đồ vào một ô, điều này cho phép bạn xem dữ liệu của mình trông như thế nào ở cấp rất cao. Bạn có thể chọn tạo ba loại biểu đồ Sparklines: Line, Column, and Win/Loss.
Bạn có thể tạo biểu đồ Sparklines của riêng mình bằng cách chọn dữ liệu bạn muốn được vẽ và sau đó đi tới Insert > Sparklines > [Type of Sparkline].
Trong ví dụ dưới đây đã vẽ biểu đồ kết quả của ba lần thử nghiệm bơi tự do dài 100 yard bằng cách sử dụng Column Sparklines.
