Microsoft Excel là một công cụ bảng tính mạnh mẽ và không thể thiếu trong nhiều lĩnh vực, từ quản lý tài chính cá nhân đến phân tích dữ liệu phức tạp trong doanh nghiệp. Hàng trăm tính năng và công cụ tích hợp sẵn trong Excel có thể khiến người dùng cảm thấy choáng ngợp. Tuy nhiên, nếu bạn đang muốn nâng cao trình độ và tối ưu hóa hiệu suất làm việc với bảng tính, việc nắm vững một số tính năng cốt lõi là điều cực kỳ quan trọng.
Bài viết này trên webthuthuat.net sẽ đưa bạn vào một chuyến tham quan nhanh gọn nhưng đầy đủ về 6 tính năng thiết yếu trong Microsoft Excel. Đây là những công cụ không chỉ giúp bạn tổ chức và phân tích dữ liệu hiệu quả hơn mà còn tối ưu hóa quy trình làm việc, từ đó giúp bạn làm chủ bảng tính Excel một cách chuyên nghiệp. Dù bạn là người mới bắt đầu hay đã có kinh nghiệm, việc thành thạo những tính năng này sẽ mở ra nhiều khả năng mới và nâng cao đáng kể năng suất của bạn.
6 Tính Năng Excel Thiết Yếu Giúp Bạn Nâng Tầm Kỹ Năng Bảng Tính
Dưới đây là 6 tính năng hàng đầu mà mọi người dùng Excel nên biết và thành thạo để khai thác tối đa sức mạnh của công cụ này.
1. Bảng Excel (Excel Tables): Tổ Chức Dữ Liệu Hiệu Quả
Về bản chất, một trang tính Microsoft Excel là một bảng lớn được tổ chức thành các cột và hàng, với mỗi giao điểm cột-hàng tạo thành một ô nơi bạn có thể nhập dữ liệu hoặc công thức. Tuy nhiên, trong cấu trúc lớn đó, bạn có thể tạo các tập dữ liệu nhỏ hơn hoạt động độc lập, và đây chính là lúc “Bảng Excel” (Excel Tables) phát huy tác dụng.
Bảng Excel mang lại nhiều lợi ích vượt trội: chúng đi kèm với khả năng lọc và sắp xếp tích hợp sẵn, tự động mở rộng khi có dữ liệu mới, tự động sao chép công thức xuống toàn bộ cột, và cho phép bạn nhanh chóng thêm hàng tổng (Total row) để phân tích dữ liệu tức thì. Hơn nữa, bạn có thể sử dụng các tiêu đề cột của bảng trong công thức – còn được gọi là tham chiếu cấu trúc (structured references) – cũng như chọn từ nhiều tùy chọn định dạng bảng khác nhau.
Cách tạo và sử dụng Bảng Excel:
Nếu bạn bắt đầu với một trang tính trống, hãy nhấp vào “Table” (Bảng) trên tab “Insert” (Chèn) trên thanh Ribbon.
Biểu tượng 'Table' (Bảng) được chọn trong tab Insert (Chèn) trên thanh Ribbon của Excel, minh họa cách tạo bảng mới.
Sau đó, nhấp vào “OK” trong hộp thoại “Create Table” (Tạo Bảng).
Nút 'OK' được chọn trong hộp thoại 'Create Table' (Tạo Bảng) của Excel, xác nhận phạm vi dữ liệu cho bảng mới.
Trong bảng vừa tạo, bạn có thể đổi tên cột đầu tiên và bắt đầu nhập dữ liệu của mình.
Hình ảnh minh họa hai ô đầu tiên của bảng Excel, một ô chứa tiêu đề cột 'Name' (Tên) và ô còn lại là dữ liệu 'Jane', thể hiện cấu trúc ban đầu của bảng.
Ngay khi bạn nhập vào ô trống đầu tiên bên dưới bảng và nhấn Enter, bảng sẽ tự động nhận diện ô đó và bao gồm nó như một phần của tập dữ liệu. Tương tự, việc thêm giá trị vào cột trống đầu tiên bên cạnh bảng sẽ mở rộng định dạng sang phải.
Bảng Excel tự động mở rộng, hiển thị hai tên và các ô trống sẵn sàng để nhập dữ liệu tuổi tương ứng, minh họa tính năng tự động phát triển của bảng.
Nếu bạn đã có sẵn tập dữ liệu và muốn chuyển đổi nó thành một bảng đã định dạng, hãy chọn bất kỳ ô nào trong phạm vi đó. Sau đó, trên tab “Home” (Trang chủ) trên thanh Ribbon, nhấp vào “Format As Table” (Định dạng dưới dạng Bảng) và chọn một kiểu.
Một ô trong tập dữ liệu Excel được chọn, và menu 'Format As Table' (Định dạng dưới dạng Bảng) trong tab Home (Trang chủ) trên thanh Ribbon được mở, chuẩn bị chuyển đổi dữ liệu thành bảng.
Tiếp theo, nếu bảng của bạn có tiêu đề, hãy chọn tùy chọn này trong hộp thoại “Create Table”. Nếu không, hãy bỏ chọn tùy chọn này và Excel sẽ tự động thêm một hàng tiêu đề cho bạn.
Tùy chọn 'My Table Has Headers' (Bảng của tôi có tiêu đề) được chọn trong hộp thoại 'Create Table' (Tạo Bảng) của Excel, đảm bảo dòng tiêu đề được nhận diện chính xác.
Khi bạn nhấp “OK”, dữ liệu của bạn sẽ ngay lập tức được chuyển đổi thành một bảng được trình bày gọn gàng.
Bảng Excel được định dạng gọn gàng, chứa thông tin chi tiết về doanh số bán lẻ của các loại trái cây, minh họa dữ liệu được tổ chức trong bảng.
Lưu ý: Nếu tập dữ liệu của bạn chứa các công thức tham chiếu đến các cột khác trong phạm vi, bạn sẽ cần tạo lại chúng bằng cách sử dụng tham chiếu cấu trúc (structured references) sau khi đã định dạng dữ liệu dưới dạng Bảng Excel. Nói cách khác, nếu một công thức đọc =SUM(A2+B2), hãy xóa A2 và B2 và thay thế các tham chiếu trực tiếp này bằng tham chiếu cột bằng cách nhấp vào các ô liên quan.
Dù bạn bắt đầu từ đầu hay chuyển đổi một tập dữ liệu đã có sẵn thành một bảng được định dạng, một bước quan trọng cần thực hiện là đặt tên cho bảng của bạn. Điều này có nghĩa là bảng có thể được tham chiếu trong các công thức bên ngoài bảng, và bạn có thể chuyển đến nó bất kỳ lúc nào thông qua hộp tên (name box) ở góc trên bên trái cửa sổ Excel.
Để thực hiện điều này, hãy chọn bất kỳ ô nào trong bảng. Sau đó, trong tab “Table Design” (Thiết kế Bảng), đi đến nhóm “Properties” (Thuộc tính) và nhập một tên mới.
Một bảng trong Excel được đổi tên thành 'Fruit_Prices' trong tab 'Table Design' (Thiết kế Bảng), cho phép tham chiếu dễ dàng hơn trong công thức.
Tab “Table Design” cũng là nơi bạn có thể thay đổi cách bảng của mình hiển thị và hoạt động. Bạn có thể điều chỉnh bố cục tổng thể của bảng trong nhóm “Table Styles” (Kiểu Bảng), và bạn có thể chọn có bao gồm các yếu tố như hàng tổng (total row) hoặc dải màu xen kẽ (banding) trong nhóm “Table Style Options” (Tùy chọn Kiểu Bảng). Hơn nữa, bạn có thể thêm Slicers vào bảng của mình để dễ dàng lọc dữ liệu hơn.
Nếu bạn chưa bao giờ sử dụng Bảng Excel trước đây, bạn sẽ ngạc nhiên về việc chúng giúp quản lý và tổ chức dữ liệu dễ dàng hơn đến mức nào.
2. Các Hàm Tra Cứu (Lookup Functions): Chìa Khóa Tra Cứu Dữ Liệu Nhanh Chóng
Là một chuyên gia SEO và biên tập viên dày dặn kinh nghiệm trong lĩnh vực công nghệ, tôi có thể khẳng định rằng các hàm tra cứu là một trong những loại hàm tôi sử dụng nhiều nhất trong các bảng tính Excel của mình. Chúng là một cách mạnh mẽ và hiệu quả để tra cứu một giá trị trong một tập dữ liệu và trả về một kết quả tương ứng.
Microsoft liệt kê 36 hàm tra cứu và tham chiếu khác nhau, nhưng XLOOKUP và INDEX với MATCH là hữu ích nhất cho việc tham chiếu dữ liệu hàng ngày. Hãy cùng tôi xem một số ví dụ dưới đây.
Ví dụ 1: Trả về một giá trị trong tra cứu một chiều
Trong ví dụ đơn giản này, mục tiêu là sử dụng các hàm này để trả về tổng điểm của cầu thủ có tên được nhập trong ô F2.
Bố cục trang tính Excel với bảng dữ liệu 'Scores' (Điểm số) bên trái và khu vực tra cứu bên phải, chuẩn bị sử dụng hàm XLOOKUP hoặc INDEX/MATCH để trích xuất điểm.
Để thực hiện điều này trong ô G2 bằng cách sử dụng hàm XLOOKUP, bạn cần nhập:
=XLOOKUP($F$2,Scores[Name],Scores[Total])Trong đó:
$F$2là ô chứa giá trị cần tra cứu (trong trường hợp này là “Tom”).Scores[Name]cho Excel biết tìm giá trị này trong cột “Name” của bảng có tên “Scores”.Scores[Total]trả về giá trị tương ứng từ cột “Total” của bảng “Scores”.
Khi bạn nhấn Enter, hàm tra cứu sẽ trả về chính xác tổng điểm là 9.
Kết quả hàm XLOOKUP trong Excel trả về điểm số '9' cho tên 'Tom', minh họa cách hàm tra cứu hoạt động trong trường hợp một chiều.
Để có cùng kết quả bằng cách sử dụng các hàm INDEX và MATCH, trong ô G3, bạn cần nhập:
=INDEX(Scores[[Day 1]:[Total]],MATCH($F$2,Scores[Name],0),3)Trong đó:
Scores[[Day 1]:[Total]]là mảng chứa giá trị bạn muốn trả về.MATCH($F$2,Scores[Name],0)lấy giá trị trong F2 và trả về số hàng của giá trị tương ứng trong cột “Name” của bảng “Scores”.3trả về giá trị trong mảng nằm ở cột thứ ba của hàng đó.
Kết quả hàm INDEX và MATCH trong Excel trả về điểm số '9' cho tên 'Tom', cho thấy cách kết hợp hai hàm này để tra cứu dữ liệu một chiều.
Lưu ý rằng, khi so sánh các ví dụ này, công thức XLOOKUP đơn giản hơn nhiều. XLOOKUP được giới thiệu vào Excel sau INDEX và MATCH, vì vậy nó là một cách tinh tế hơn để hoàn thành nhiệm vụ tra cứu một chiều này.
Ví dụ 2: Trả về một giá trị trong tra cứu hai chiều
XLOOKUP và INDEX với MATCH cũng có thể thực hiện tra cứu hai chiều. Trong ví dụ này, trong các ô H6 và H7, bạn muốn sử dụng các hàm này để trả về điểm của một cầu thủ vào một ngày cụ thể, tùy thuộc vào tên trong ô F6 (trong trường hợp này là “Mike”) và ngày trong ô G6 (trong trường hợp này là “Day 2”).
Trang tính Excel hiển thị bảng điểm và khu vực tra cứu, chuẩn bị thực hiện tra cứu hai chiều để tìm điểm của một người vào một ngày cụ thể.
Để thực hiện điều này trong ô H6 bằng XLOOKUP, bạn cần nhập:
=XLOOKUP($G$6,Scores[[#Headers],[Day 1]:[Total]],XLOOKUP($F$6,Scores[Name],Scores[[Day 1]:[Total]]))Trong đó, XLOOKUP đầu tiên tra cứu giá trị trong ô G6 trong các tiêu đề cột, và XLOOKUP thứ hai tra cứu giá trị trong ô F6 trong cột “Name”. Sau đó, nó trả về kết quả từ mảng.
Hàm XLOOKUP lồng nhau trong Excel trả về điểm '6' cho 'Mike' vào 'Day 2', minh họa cách thực hiện tra cứu hai chiều.
Sử dụng INDEX và MATCH, trong ô H7, nhập:
=INDEX(Scores,MATCH($F$6,Scores[Name],0),MATCH($G$6,Scores[#Headers],0))Trong đó, phần INDEX của công thức xác định mảng chứa giá trị cần trả về, MATCH đầu tiên tra cứu giá trị trong ô F6 trong cột “Name”, và MATCH thứ hai tra cứu giá trị trong G6 trong các tiêu đề cột.
Kết quả hàm INDEX và MATCH lồng nhau trong Excel trả về điểm '6' cho 'Mike' vào 'Day 2', thể hiện hiệu quả của phương pháp này trong tra cứu hai chiều.
Trong trường hợp này, công thức khi sử dụng kết hợp INDEX-MATCH-MATCH ngắn hơn công thức khi sử dụng XLOOKUP-XLOOKUP, vì vậy tôi có xu hướng sử dụng công thức trước cho các tra cứu hai chiều.
Ví dụ 3: Sử dụng hàm tra cứu trong một bảng
Trong các ví dụ trên, việc tra cứu được thực hiện bên ngoài các bảng dữ liệu chính. Tuy nhiên, bạn cũng có thể sử dụng các hàm này trong một bảng Excel đã được định dạng.
Trong ví dụ 3, mục tiêu của bạn là trả về điểm chữ của học sinh dựa trên điểm số của họ.
Hai bảng trong Excel: một bảng chứa điểm số học sinh và một bảng tra cứu điểm số thành điểm chữ, chuẩn bị sử dụng hàm tra cứu để gán điểm chữ.
Để thực hiện điều này bằng XLOOKUP, trong ô C10, nhập:
=XLOOKUP([@Score],$G$10:$G$14,$H$10:$H$14,"FAIL",-1)Trong đó:
[@Score]lấy điểm số của từng học sinh.$G$10:$G$14cho Excel biết tìm từng điểm số trong các ô đó.$H$10:$H$14là giá trị cần trả về (trong trường hợp này là điểm chữ) trong ô đang hoạt động."FAIL"là giá trị cần trả về nếu giá trị tra cứu không tìm thấy.-1cho Excel biết tìm kiếm khớp chính xác hoặc trả về mục nhỏ nhất tiếp theo (cho tra cứu gần đúng).
Khi bạn nhấn Enter, công thức sẽ tự động được sao chép sang các ô còn lại trong cột.
Hàm XLOOKUP được sử dụng trong bảng Excel để tự động trả về điểm chữ của học sinh dựa trên điểm số của họ, minh họa tra cứu giá trị gần đúng.
Để thực hiện điều này bằng INDEX và MATCH, trong ô C14, nhập:
=INDEX($G$10:$H$14,MATCH([@Score],$G$10:$G$14,1),2)Trong đó:
$G$10:$H$14là phạm vi chứa các giá trị tra cứu.MATCH([@Score],$G$10:$G$14,1)khớp điểm số trong cột “Grade” với các điểm số trong bảng tra cứu.1tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu (cho tra cứu gần đúng).2cho Excel biết tìm kết quả trong cột thứ hai.
Sau đó, nhấn Enter để xác nhận công thức và áp dụng nó cho các ô khác trong cột đó.
Hàm INDEX và MATCH được sử dụng trong bảng Excel để trả về điểm chữ tương ứng cho từng điểm số của học sinh, thể hiện cách tra cứu giá trị gần đúng.
Cả XLOOKUP và INDEX với MATCH đều yêu cầu năm đối số, và công thức của chúng có độ dài tương tự. Tuy nhiên, vì XLOOKUP cho phép bạn chỉ định giá trị cần trả về nếu không tìm thấy, nên nó là lựa chọn ưu tiên của tôi trong những tình huống này.
3. Định Dạng Có Điều Kiện (Conditional Formatting): Trực Quan Hóa Dữ Liệu Ngay Lập Tức
Bạn có thể biết cách sử dụng tất cả các công thức Excel phức tạp, nhưng điều đó sẽ không có ích gì nếu các số liệu chúng tạo ra không dễ đọc và diễn giải. Đó là lý do tại sao định dạng có điều kiện (Conditional Formatting) là một công cụ thiết yếu cần sử dụng trong mọi bảng tính. Nó thực hiện đúng như tên gọi của nó – định dạng các ô dựa trên các điều kiện nhất định.
Hãy tưởng tượng bạn sở hữu một cửa hàng tạp hóa và bạn muốn trực quan hóa rõ ràng số tiền bạn đã kiếm được từ mỗi loại trái cây.
Bảng dữ liệu doanh thu cửa hàng trái cây trong Excel, trước khi áp dụng định dạng có điều kiện, hiển thị các cột như sản phẩm, giá, số lượng bán và tổng doanh thu.
Bạn có thể thấy các số liệu trong cột E, nhưng hiện tại chúng không dễ dàng để diễn giải. Để khắc phục điều này, hãy chọn dữ liệu trong cột bằng cách nhấp vào mũi tên đen xuất hiện khi bạn di chuột qua tiêu đề cột. Sau đó, trong tab “Home” (Trang chủ) trên thanh Ribbon, nhấp vào menu thả xuống “Conditional Formatting” (Định dạng có điều kiện), di chuột qua “Color Scales” (Thang màu) và chọn tùy chọn màu xanh lá cây đến đỏ.
Các ô doanh thu được chọn trong Excel và tùy chọn 'Green-Red Color Scale' (Thang màu Xanh-Đỏ) được chọn trong menu 'Conditional Formatting' (Định dạng có điều kiện) để trực quan hóa dữ liệu.
Bây giờ, bạn có thể ngay lập tức thấy số liệu nào cao hơn và số liệu nào thấp hơn một cách trực quan.
Cột 'Doanh thu' trong bảng Excel đã được áp dụng 'Color Scales' (Thang màu), giúp người dùng dễ dàng nhận biết các giá trị cao (xanh) và thấp (đỏ).
Bạn cũng có thể sử dụng định dạng có điều kiện để đặt các quy tắc cụ thể hơn. Ví dụ, thay vì tô màu cột E bằng thang màu, bạn muốn tô sáng toàn bộ hàng của bất kỳ loại trái cây nào có doanh thu trên 500 đô la. Để thực hiện điều này, hãy chọn ô trên cùng bên trái đầu tiên bên dưới tiêu đề cột, sau đó nhấn Ctrl+A để chọn toàn bộ phạm vi. Tiếp theo, nhấp vào “Conditional Formatting” > “New Rule” (Quy tắc Mới).
Dữ liệu trong bảng Excel được chọn và tùy chọn 'New Rule' (Quy tắc Mới) được chọn trong menu 'Conditional Formatting' (Định dạng có điều kiện), chuẩn bị tạo quy tắc tùy chỉnh.
Bây giờ, trong hộp thoại “New Formatting Rule” (Quy tắc Định dạng Mới), nhấp vào “Use A Formula To Determine Which Cells To Format” (Sử dụng công thức để xác định ô nào cần định dạng), và trong trường văn bản, nhập:
=$E2>500Công thức này sẽ lấy tất cả các ô trong cột E, bắt đầu từ ô E2, và đánh giá giá trị của chúng để xem liệu chúng có lớn hơn 500 hay không.
Công thức '=$E2>500' được thêm vào hộp thoại 'New Formatting Rule' (Quy tắc Định dạng Mới) trong Excel, dùng để định dạng các dòng có doanh thu trên 500.
Cuối cùng, nhấp vào “Format” (Định dạng) để chọn kiểu ô sẽ được áp dụng khi tiêu chí trên được đáp ứng. Trong trường hợp này, tôi chọn màu nền tím nhạt.
Hộp thoại 'Format Cells' (Định dạng Ô) của Conditional Formatting, hiển thị màu nền tím nhạt được chọn để áp dụng khi điều kiện định dạng được đáp ứng.
Nhấp “OK” để đóng các hộp thoại và hoàn tất quá trình.
Vì bạn đã chọn tất cả các cột ở bước đầu tiên, toàn bộ hàng cho mỗi giá trị đủ điều kiện giờ đây sẽ được tô sáng.
Bảng doanh thu trong Excel sau khi áp dụng định dạng có điều kiện, các hàng có giá trị doanh thu lớn hơn 500 đã được tô sáng bằng màu tím nhạt.
Định dạng có điều kiện có thể được sử dụng để áp dụng định dạng trong nhiều ngữ cảnh khác nhau, bao gồm định dạng các ngày cụ thể, hoặc áp dụng định dạng nếu giá trị của một ô nằm trên hoặc dưới mức trung bình.
4. Xác Thực Dữ Liệu (Data Validation): Đảm Bảo Tính Nhất Quán và Ngăn Ngừa Lỗi
Cho dù bạn có kế hoạch chia sẻ bảng tính của mình với người khác hay chỉ giữ nó để sử dụng riêng, xác thực dữ liệu (Data Validation) là một công cụ tuyệt vời để đảm bảo các ô chứa các giá trị hoặc loại giá trị mà bạn mong đợi. Nhờ đó, nó đảm bảo tính nhất quán, giúp ngăn ngừa lỗi chính tả và tiết kiệm thời gian.
Đầu tiên, hãy chọn (các) ô mà bạn muốn áp dụng quy tắc xác thực dữ liệu. Sau đó, trong tab “Data” (Dữ liệu) trên thanh Ribbon, nhấp vào “Data Validation” (Xác thực Dữ liệu). Nếu bạn thấy một menu thả xuống, hãy nhấp lại vào “Data Validation”.
Ô O1 trong Excel được chọn và biểu tượng 'Data Validation' (Xác thực Dữ liệu) được tô sáng trong tab Data (Dữ liệu), chuẩn bị thiết lập quy tắc nhập liệu.
Theo mặc định, bất kỳ giá trị nào cũng có thể được nhập vào một ô trong Excel. Tuy nhiên, nếu bạn mở rộng menu thả xuống “Allow” (Cho phép) của hộp thoại “Data Validation”, bạn có thể thấy các loại giới hạn mà bạn có thể áp dụng. Ví dụ, nếu bạn chọn “Whole Number” (Số nguyên), “Decimal” (Số thập phân), “Date” (Ngày), “Time” (Thời gian) hoặc “Text Length” (Độ dài văn bản), bạn sẽ được cung cấp tùy chọn để chỉ định các tham số cho các loại dữ liệu đó.
Tùy chọn 'Whole Number' (Số nguyên) được chọn trong trường 'Allow' (Cho phép) của hộp thoại 'Data Validation' (Xác thực Dữ liệu) trong Excel, thiết lập giới hạn chỉ cho phép nhập số nguyên.
Mặt khác, nếu bạn chọn “List” (Danh sách), bạn có thể nhập trực tiếp một danh sách các giá trị được phép (phân cách bằng dấu phẩy) vào trường “Source” (Nguồn), hoặc tham chiếu đến các ô chứa các giá trị được phép.
Các ô chứa danh sách giá trị hợp lệ được tham chiếu trong trường 'Source' (Nguồn) của hộp thoại 'Data Validation' (Xác thực Dữ liệu) trong Excel, để tạo danh sách thả xuống.
Sau khi bạn nhấp “OK”, một nút thả xuống sẽ xuất hiện khi ô bị ảnh hưởng được chọn, bạn có thể nhấp vào đó để hiển thị các tùy chọn nhập liệu.
Một danh sách thả xuống (drop-down list) xuất hiện trực tiếp trong ô Excel, cho phép người dùng chọn giá trị từ danh sách đã xác định, đảm bảo tính nhất quán dữ liệu.
Cuối cùng, hộp thoại “Data Validation” có hai tab khác, nơi bạn có thể tinh chỉnh thêm khả năng “sạch” của dữ liệu trong các ô đã chọn:
- Input Message (Thông báo nhập): Tab này cho phép bạn tạo một thông báo xuất hiện khi ô được chọn. Đây là một cách tốt để nhắc nhở người nhập dữ liệu rằng chỉ có các giá trị nhất định mới được phép trong ô đó.
- Error Alert (Thông báo lỗi): Trong tab này, bạn có thể tạo một thông báo xuất hiện khi nhập sai loại dữ liệu vào ô.
5. Power Query: Công Cụ Mạnh Mẽ Để Thao Tác Dữ Liệu
Power Query là một công cụ bị đánh giá thấp nhưng mọi người – từ người mới bắt đầu Excel đến người dùng thành thạo – đều có thể và nên sử dụng. Nó cho phép bạn nhập và kết nối các tập dữ liệu từ nhiều nguồn khác nhau, bao gồm web, tệp PDF và các trang tính riêng biệt trong một sổ làm việc, cũng như thao tác dữ liệu để đáp ứng nhu cầu của bạn.
Ví dụ, bạn có thể tách các ô thành hai hoặc nhiều ô mới, thay thế lỗi bằng các giá trị có ý nghĩa hơn, unpivot dữ liệu để hỗ trợ phân tích, điền các ô trống dựa trên ô phía trên và nhiều hơn nữa. Bạn cũng có thể ghép nối các bảng có cùng tiêu đề cột từ các trang tính khác nhau bằng Power Query.
Để biến đổi dữ liệu hiện có trong trang tính của bạn, hãy chọn bất kỳ ô nào trong phạm vi liên quan. Sau đó, trong tab “Data” (Dữ liệu) trên thanh Ribbon, nhấp vào “From Table/Range” (Từ Bảng/Phạm vi).
Một ô trong tập dữ liệu Excel được chọn, và tùy chọn 'From Table/Range' (Từ Bảng/Phạm vi) được chọn trong tab 'Data' (Dữ liệu) của thanh Ribbon, khởi động Power Query Editor.
Trình chỉnh sửa Power Query (Power Query Editor) sau đó sẽ mở ra, nơi bạn có thể bắt đầu thao tác dữ liệu của mình. Tab “Transform” (Chuyển đổi) là nơi hầu hết các phép thuật xảy ra, vì vậy đây là một nơi tốt để bắt đầu. Nhấp chuột phải vào các tiêu đề cột cũng cho phép bạn truy cập vào nhiều công cụ cụ thể cho cột.
Tab 'Transform' (Chuyển đổi) được chọn trong Power Query Editor của Excel, nơi người dùng có thể thực hiện các thao tác biến đổi dữ liệu mạnh mẽ.
Khi bạn đã hoàn tất việc chỉnh sửa dữ liệu của mình, nhấp vào “Close And Load” (Đóng và Tải) trong tab “Home” (Trang chủ) trên thanh Ribbon để gửi tập dữ liệu đến một trang tính mới trong sổ làm việc của bạn.
Để nhập một bảng từ một trang web, hãy quay lại tab “Data” (Dữ liệu) trên thanh Ribbon chính của Excel, nhấp vào “From Web” (Từ Web) và dán URL vào trường văn bản của cửa sổ xuất hiện.
Tùy chọn 'From Web' (Từ Web) được chọn trong tab 'Data' (Dữ liệu) của Excel và URL của trang web được dán vào hộp thoại, chuẩn bị nhập dữ liệu từ web bằng Power Query.
Khi bạn nhấp “OK”, hộp thoại “Navigator” (Trình điều hướng) sẽ xuất hiện, nơi bạn có thể chọn bảng bạn muốn nhập và chỉnh sửa, trước khi nhấp “Transform Data” (Chuyển đổi Dữ liệu) để mở Trình chỉnh sửa Power Query.
Một bảng dựa trên web được chọn trong hộp thoại 'Navigator' (Trình điều hướng) của Excel, và nút 'Transform Data' (Chuyển đổi Dữ liệu) được nhấn để mở Power Query Editor.
6. Ký Tự Đại Diện (Wildcards): Tìm Kiếm Linh Hoạt và Đối Sánh Một Phần
Ký tự đại diện (Wildcards) trong Excel đại diện cho các ký tự không xác định hoặc không cụ thể trong các chức năng tìm kiếm, lọc và công thức. Việc hiểu và sử dụng chúng sẽ giúp bạn thao tác với dữ liệu một cách linh hoạt hơn rất nhiều.
Có hai loại ký tự đại diện bạn có thể sử dụng để xác định các giá trị có sự khớp “mờ” (fuzzy matches):
- Dấu hoa thị (*): Đại diện cho bất kỳ số lượng ký tự nào (bao gồm không có ký tự nào). Ví dụ, “OK*” sẽ trả về kết quả khớp cho “Oklahoma” nhưng không phải “look”.
- Dấu chấm hỏi (?): Đại diện cho một ký tự duy nhất. Ví dụ, “?OK” sẽ trả về kết quả khớp cho “wok” nhưng không phải “Joke”.
- Ký tự đại diện hỗn hợp: Bạn cũng có thể kết hợp dấu hoa thị và dấu chấm hỏi để tinh chỉnh tìm kiếm của mình hơn nữa. Ví dụ, “*OK?” sẽ trả về kết quả khớp cho “Books” nhưng không phải “Took”.
Nếu bạn muốn tìm kiếm dấu chấm hỏi hoặc dấu hoa thị như các ký tự riêng biệt, hãy đặt dấu ngã (~) trước một trong hai ký hiệu để hủy chức năng ký tự đại diện của chúng. Ví dụ, ~* sẽ tìm kiếm ký tự dấu hoa thị, không phải ký tự đại diện.
Ký tự đại diện thường được sử dụng nhất khi tìm kiếm các giá trị nhất định trong Excel. Trong ví dụ này, sau khi nhấn Ctrl+F để mở tab “Find” (Tìm) của hộp thoại “Find And Replace” (Tìm và Thay thế), việc nhập *2??A sẽ trả về hai kết quả từ tập dữ liệu: UK255A và CAN267A.
Sử dụng ký tự đại diện (*2??A) trong hộp thoại 'Find and Replace' (Tìm và Thay thế) của Excel để tìm kiếm các mã sản phẩm cụ thể có chứa mẫu ký tự, minh họa tìm kiếm linh hoạt.
Sử dụng cùng tập dữ liệu, bạn có thể sử dụng ký tự đại diện trong hàm SUMIF để cộng giá của các mặt hàng đến từ Vương quốc Anh (UK):
=SUMIF(A2:A18,"UK*",B2:B18)
Công thức SUMIF sử dụng ký tự đại diện ('UK*') trong Excel để tính tổng giá của tất cả các sản phẩm có mã bắt đầu bằng 'UK', thể hiện sức mạnh của wildcard trong hàm.
Lưu ý quan trọng: Ký tự đại diện và giá trị văn bản phải luôn được đặt trong dấu ngoặc kép trong các công thức trong Excel.
Cuối cùng, sau khi nhấn Ctrl+Shift+L để thêm các nút bộ lọc vào tập dữ liệu, việc nhấp vào nút bộ lọc ở đầu cột A và nhập:
*Avào trường văn bản sẽ trả về tất cả các mã sản phẩm kết thúc bằng chữ “A”.
Tìm kiếm trong bộ lọc dữ liệu của Excel sử dụng ký tự đại diện ('*A') để hiển thị các mã sản phẩm kết thúc bằng chữ 'A', giúp lọc dữ liệu nhanh chóng và chính xác.
Kết Luận
Việc thành thạo Microsoft Excel không chỉ dừng lại ở việc biết cách nhập dữ liệu hay tính toán các phép cộng cơ bản. Để thực sự làm chủ bảng tính và nâng cao năng suất làm việc, việc khai thác tối đa các tính năng mạnh mẽ của Excel là điều vô cùng cần thiết. 6 tính năng thiết yếu mà webthuthuat.net đã giới thiệu trong bài viết này – từ Bảng Excel thông minh, các hàm tra cứu linh hoạt, đến định dạng có điều kiện trực quan, xác thực dữ liệu chặt chẽ, Power Query biến đổi dữ liệu, và ký tự đại diện cho tìm kiếm nâng cao – đều là những công cụ then chốt giúp bạn xử lý mọi tác vụ từ đơn giản đến phức tạp một cách hiệu quả nhất.
Chúng tôi hy vọng rằng qua bài viết này, bạn đã có được cái nhìn sâu sắc và nắm bắt được cách vận dụng những tính năng này vào công việc hàng ngày của mình. Hãy bắt đầu áp dụng ngay hôm nay để thấy sự khác biệt trong cách bạn làm việc với Excel. Đừng ngần ngại thực hành với các ví dụ cụ thể, thử nghiệm các công thức và khám phá thêm các tùy chọn để biến chúng thành kỹ năng của riêng bạn.
Nếu bạn có bất kỳ câu hỏi nào hoặc muốn chia sẻ kinh nghiệm sử dụng các tính năng Excel, hãy để lại bình luận bên dưới bài viết này trên webthuthuat.net. Kiến thức công nghệ là để chia sẻ, và chúng tôi luôn sẵn lòng cùng bạn học hỏi và phát triển. Hãy theo dõi webthuthuat.net để không bỏ lỡ những thủ thuật và hướng dẫn công nghệ hữu ích khác!
