Microsoft Excel, một phần mềm bảng tính quen thuộc, sở hữu hàng trăm công cụ và hàm chức năng mạnh mẽ, nhưng không phải tất cả đều được người dùng khám phá và tận dụng triệt để. Trong số đó, có những “viên ngọc ẩn” có thể thay đổi hoàn toàn cách bạn làm việc với dữ liệu, giúp bạn tiết kiệm thời gian, nâng cao độ chính xác và biến những bảng tính phức tạp trở nên dễ quản lý hơn. Với vai trò là chuyên gia công nghệ tại thichcongnghe.net, chúng tôi sẽ chia sẻ 4 thủ thuật Excel nâng cao ít được biết đến này, những bí quyết mà bất kỳ ai muốn tối ưu hiệu suất làm việc với Excel đều không nên bỏ qua. Từ việc lập kế hoạch tài chính thông minh đến trực quan hóa dữ liệu ấn tượng, những tính năng ẩn này chắc chắn sẽ đưa kỹ năng Excel của bạn lên một tầm cao mới. Hãy cùng khám phá!
1. Goal Seek (Tìm kiếm Mục tiêu): Bí quyết lập kế hoạch tài chính và mục tiêu thông minh
Công cụ Goal Seek (Tìm kiếm Mục tiêu) ít được biết đến của Microsoft Excel có mặt để hỗ trợ bạn khi bạn đã biết kết quả cuối cùng mong muốn, nhưng lại không biết làm thế nào để đạt được nó. Đây là một tính năng cực kỳ hữu ích trong các tình huống cần lập kế hoạch hoặc phân tích ngược.
Hãy lấy một ví dụ cụ thể: Bạn đang có kế hoạch mua nhà trong hai năm tới. Hiện tại, bạn tiết kiệm 400 đô la mỗi tháng với lãi suất hàng năm là 2.5%. Trong hai năm, tổng số tiền bạn tiết kiệm được sẽ là 9833.55 đô la, được tính toán thông qua hàm Future Value (FV) như sau:
=FV(B1/12,B2,-B3)Trong đó, B1/12 chuyển đổi lãi suất hàng năm (rate, ô B1) thành lãi suất hàng tháng, B2 là số kỳ thanh toán (nper), và B3 là giá trị khoản thanh toán (pmt) được ghi dưới dạng số âm (vì đó là tiền rời khỏi tài khoản thường xuyên của bạn).
Hàm FV trong Excel được sử dụng để tính tổng số tiền tiết kiệm.
Tuy nhiên, mục tiêu của bạn là tiết kiệm tổng cộng 15.000 đô la để chi trả cho khoản đặt cọc và các chi phí phát sinh khi chuyển nhà. Vì vậy, bạn cần tìm ra số tiền phải tiết kiệm mỗi tháng trong hai năm để đạt được mục tiêu này. Đây chính là lúc công cụ Goal Seek của Excel phát huy tác dụng.
Cách sử dụng Goal Seek để tìm khoản tiết kiệm hàng tháng cần thiết
Đầu tiên, hãy chọn ô chứa công thức tính toán (trong ví dụ này là ô B4). Sau đó, trên thanh công cụ Data (Dữ liệu), nhấp vào What-If Analysis (Phân tích giả định) > Goal Seek (Tìm kiếm Mục tiêu).
Lưu ý quan trọng: Giá trị cuối cùng phải là một kết quả của phép tính. Goal Seek sẽ không hoạt động nếu bạn nhập giá trị này theo cách thủ công.
Công cụ Goal Seek trong tab Data của Microsoft Excel được chọn.
Hộp thoại Goal Seek xuất hiện sẽ có ba trường:
- Set Cell (Đặt ô): Đây là ô chứa kết quả tính toán hiện tại, trong ví dụ này là ô B4. Vì bạn đã chọn ô này trước khi mở hộp thoại, nó sẽ tự động được điền.
- To Value (Đến giá trị): Đây là giá trị bạn cần đạt được để hoàn thành mục tiêu cuối cùng. Trong trường hợp này là 15000.
- By Changing Cell (Bằng cách thay đổi ô): Biến số có thể thay đổi trong phép tính này là số tiền tiết kiệm mỗi tháng (ô B3).
Hộp thoại Goal Seek của Excel với ô B4 là Set Cell, 15000 là To Value và B3 là By Changing Cell.
Sau khi bạn nhấp “OK”, Excel sẽ chạy qua nhiều lần lặp tính toán trước khi điều chỉnh ô By Changing Cell mà bạn đã tham chiếu. Trong trường hợp này, nó cho bạn biết rằng bạn phải tiết kiệm 610.16 đô la mỗi tháng trong hai năm để đạt được mục tiêu của mình.
Goal Seek được sử dụng trong Excel để xác định số tiền tiết kiệm hàng tháng để đạt mục tiêu cuối cùng là 15000.
Tùy chỉnh Goal Seek để xác định thời gian đạt mục tiêu
Tuy nhiên, tại thời điểm này, bạn nhận ra rằng mình không thể tiết kiệm số tiền lớn như vậy mỗi tháng. Thay vào đó, bạn quyết định tiếp tục gửi 400 đô la mỗi tháng và sẽ chuyển nhà khi đạt được mục tiêu 15.000 đô la. Lần này, bạn muốn Excel cho bạn biết điều đó sẽ mất bao lâu.
Đầu tiên, hãy đặt lại giá trị pmt trong ô B3 thành 400 đô la theo cách thủ công. Sau đó, chọn ô chứa phép tính giá trị cuối cùng, và nhấp What-If Analysis (Phân tích giả định) > Goal Seek (Tìm kiếm Mục tiêu) trong thanh công cụ Data (Dữ liệu).
Lần này, ô đặt là B4, và giá trị cuối cùng vẫn là 15.000, nhưng ô có thể thay đổi là ô tháng, tức là B2.
Hộp thoại Goal Seek của Excel với ô B4 là Set Cell, 15000 là To Value và B2 là By Changing Cell.
Cuối cùng, nhấp “OK” để tiết lộ rằng việc tiết kiệm 15.000 đô la bằng cách trả 400 đô la mỗi tháng với lãi suất 2.5% sẽ mất hơn 36 tháng.
Goal Seek được sử dụng trong Excel để xác định số tháng tiết kiệm cần thiết để đạt mục tiêu cuối cùng là 15000.
Như vậy, công cụ Goal Seek của Excel đã giúp bạn xác định rằng nếu bạn muốn chuyển nhà trong hai năm, bạn sẽ cần tiết kiệm 610.16 đô la mỗi tháng. Mặt khác, nếu bạn muốn tiếp tục chỉ trả 400 đô la mỗi tháng, bạn có thể chuyển nhà trong hơn ba năm.
Ngoài việc hỗ trợ bạn với các khoản tiết kiệm cá nhân, Goal Seek còn có thể giúp bạn tính toán số lượng sản phẩm cần bán để đạt mục tiêu doanh thu, hoặc mức lãi suất bạn cần đảm bảo để một khoản vay trở nên khả thi. Đây là một công cụ phân tích giả định không thể thiếu cho mọi người dùng Excel muốn lập kế hoạch thông minh hơn.
2. Camera Tool (Công cụ Camera): Chụp ảnh dữ liệu động cho Dashboard chuyên nghiệp
Công cụ Camera trong Microsoft Excel cho phép bạn chụp ảnh nhanh một phần dữ liệu và dán nó dưới dạng hình ảnh. Điều đặc biệt là hình ảnh này có tính chất động, nghĩa là nó sẽ tự động cập nhật để phản ánh bất kỳ thay đổi nào trong dữ liệu gốc. Đây là một thủ thuật Excel nâng cao cực kỳ hữu ích cho việc trực quan hóa.
Công cụ này có thể rất hữu ích nếu bạn muốn tạo một bảng điều khiển (dashboard) tổng hợp dữ liệu từ nhiều trang tính khác nhau trong cùng một workbook. Nó cũng tiện lợi khi bạn làm việc với các bộ dữ liệu lớn và muốn giữ các thông tin chính yếu luôn hiển thị. Vì dữ liệu được nhân đôi dưới dạng hình ảnh, nó sẽ nằm trên các ô của Excel mà không ảnh hưởng đến bố cục của bảng tính của bạn.
Trong số các công cụ được đề cập trong hướng dẫn này, Camera có lẽ là ẩn nhất vì nó không có sẵn ngay lập tức trên thanh ribbon. Thay vào đó, bạn phải thêm nó vào Quick Access Toolbar (QAT) của mình.
Cách thêm Camera Tool vào Quick Access Toolbar (QAT)
Để làm điều này, nhấp vào mũi tên xuống ở phía bên phải của thanh ribbon. Nếu bạn thấy “Hide Quick Access Toolbar” (Ẩn Thanh công cụ truy cập nhanh), điều đó có nghĩa là QAT đã được kích hoạt. Tuy nhiên, nếu bạn thấy “Show Quick Access Toolbar” (Hiện Thanh công cụ truy cập nhanh), hãy nhấp vào tùy chọn này để kích hoạt nó.
Tùy chọn Show Quick Access Toolbar trong Excel được làm nổi bật.
Sau đó, nhấp vào mũi tên xuống trong QAT ở đầu cửa sổ Excel và chọn “More Commands” (Lệnh khác).
Menu thả xuống Tùy chỉnh Thanh công cụ truy cập nhanh trong Excel, với More Commands được chọn.
Tiếp theo, trong menu thả xuống “Choose Command From” (Chọn lệnh từ), nhấp “All Commands” (Tất cả lệnh), cuộn xuống và chọn “Camera” (Máy ảnh). Sau đó, nhấp “Add” (Thêm) để thêm nó vào QAT của bạn và nhấp “OK” để đóng hộp thoại.
Hộp thoại Tùy chọn Excel, với lộ trình cần thiết để thêm Camera vào QAT.
Giờ đây, công cụ Camera đã nằm trên QAT của bạn, sẵn sàng để sử dụng.
Biểu tượng Camera trong Excel.
Sử dụng Camera Tool để tạo ảnh chụp nhanh dữ liệu
Giả sử bạn cần chụp ảnh nhanh một số dữ liệu đã nhập vào một bảng Excel. Để làm điều này, hãy chọn các ô chứa dữ liệu đó và nhấp vào biểu tượng “Camera” trong QAT của bạn.
Để chọn tất cả các ô trong một bảng Excel, trước tiên hãy chọn một ô bất kỳ trong bảng, sau đó nhấn Ctrl+A.
Một phần dữ liệu Excel được chọn và công cụ Camera trong Quick Access Toolbar được làm nổi bật.
Bây giờ, hãy nhấp vào ô mà bạn muốn dán phiên bản hình ảnh của dữ liệu đã sao chép này.
Công cụ Camera có thể hoạt động trên các workbook riêng biệt, nhưng các thay đổi đối với dữ liệu gốc sẽ chỉ được phản ánh trong hình ảnh đã dán nếu bạn đã đăng nhập vào tài khoản Microsoft của mình và kích hoạt AutoSave trên cả hai tệp.
Vì dữ liệu được nhân đôi dưới dạng hình ảnh, bạn có thể nhấp và kéo các tay cầm để thay đổi kích thước đồ họa, và bạn có thể định dạng ảnh chụp nhanh bằng cách sử dụng tab Picture Format (Định dạng ảnh) trên thanh ribbon.
Một tập dữ liệu trong Excel được nhân đôi thông qua công cụ Camera, với tay cầm thay đổi kích thước và tab Picture Format được làm nổi bật.
Công cụ Camera cũng hoạt động trên các yếu tố khác trong Excel, như biểu đồ và hình dạng. Đơn giản chỉ cần chọn các ô nằm phía sau và xung quanh đối tượng, và nhấp vào công cụ Camera trong QAT của bạn. Sau khi bạn dán dữ liệu dưới dạng hình ảnh, hãy cắt ảnh để loại bỏ bất kỳ khoảng trắng không mong muốn nào từ các cạnh.
Hãy ẩn các đường lưới (Alt > W > V > G) và các nút lọc (Ctrl+Shift+L) khỏi dữ liệu của bạn trước khi chụp bằng công cụ Camera. Làm như vậy sẽ làm cho hình ảnh trông gọn gàng hơn, và vì các nút lọc không hoạt động trong đồ họa đã dán, nên không cần chúng phải ở đó.
Camera Tool là một thủ thuật Excel vô cùng mạnh mẽ để tạo các báo cáo động và dashboard chuyên nghiệp mà không làm ảnh hưởng đến cấu trúc dữ liệu gốc.
3. Data Groups (Nhóm Dữ liệu): Quản lý bảng tính khổng lồ một cách khoa học
Nếu bạn đang làm việc trên một bảng tính Microsoft Excel chứa rất nhiều cột, bạn có thể cảm thấy khó chịu khi phải liên tục cuộn sang trái và phải để đọc dữ liệu của mình. Ngoài ra, đôi khi, một số cột nhất định có thể không cần thiết phải hiển thị mọi lúc. Data Groups là một thủ thuật Excel giúp giải quyết vấn đề này.
Giả sử bạn có một workbook chứa số liệu doanh số hàng tháng cho nhiều cửa hàng khác nhau, và một cột tổng số hàng năm ở ngoài cùng bên phải.
Một trang tính Excel chứa tổng doanh số hàng tháng, tổng doanh số hàng quý và tổng doanh số hàng năm cho mười cửa hàng.
Để phân tích dữ liệu hàng năm và làm cho các số liệu dễ đọc hơn, bạn muốn ẩn các cột từ B đến Q, có nghĩa là các cột A và R sẽ nằm cạnh nhau trên trang tính của bạn.
Tại thời điểm này, bạn có thể muốn chọn các cột từ B đến Q, nhấp chuột phải vào một trong các tiêu đề cột đã chọn, và nhấp “Hide” (Ẩn). Tuy nhiên, rất dễ quên rằng các cột này đang bị ẩn, và quá trình hiện lại chúng khá cồng kềnh.
Thay vào đó, hãy chọn các cột từ B đến Q bằng cách nhấp vào tiêu đề của cột B và kéo lựa chọn đến cột Q.
Các cột từ B đến Q được chọn trong một trang tính Excel chứa dữ liệu doanh số cửa hàng.
Excel cũng cho phép bạn sử dụng phương pháp tương tự để tạo các nhóm hàng (row groups).
Tạo nhóm cột chính để xem tổng quan
Tiếp theo, trong tab Data (Dữ liệu), nhấp vào “Outline” (Dàn bài), và chọn nửa trên của nút “Group” (Nhóm).
Nút Group trong menu thả xuống Outline của tab Data trên thanh ribbon của Excel.
Bây giờ, bạn có thể nhấp vào dấu trừ hoặc đường chỉ báo nhóm phía trên các cột đó để ẩn nhóm đó.
Các cột được nhóm trong Excel, với đường và ký hiệu dấu trừ chỉ ra nhóm được làm nổi bật.
Hãy chú ý, khi bạn làm vậy, dấu trừ sẽ biến thành dấu cộng, mà bạn có thể nhấp vào để mở rộng lại nhóm đó nếu muốn.
Dấu cộng trong Excel, chỉ ra rằng các cột được nhóm đang bị ẩn.
Tạo nhóm con (Subgroup) để quản lý dữ liệu chi tiết hơn
Bạn cũng có thể tạo các nhóm con trong các nhóm chính, miễn là mỗi nhóm con được phân tách bằng một cột trống hoặc cột tổng.
Sử dụng ví dụ tương tự như trên, giả sử bạn cũng muốn thu gọn dữ liệu hàng tháng, chỉ để lại tổng số hàng quý và hàng năm hiển thị. Điều này có thể thực hiện được vì các cột E, I, M và Q đóng vai trò là rào cản giữa mỗi nhóm con.
Một trang tính Excel chứa một nhóm chính, bốn tập hợp cột sẽ được biến thành các nhóm con được làm nổi bật.
Để tạo một nhóm con cho Quý 1, hãy chọn các cột từ B đến D, và nhấp “Group” (Nhóm) trong tab Data (Dữ liệu).
Các cột từ B đến D trong Excel được chọn, và nút Group trong tab Data trên thanh ribbon được làm nổi bật.
Bây giờ, ngoài việc có một nhóm chính bao gồm các ô từ B đến Q, bảng tính của bạn còn có một nhóm con bao gồm các ô từ B đến D.
Một nhóm con các cột trong Excel, với đường nhóm và dấu trừ chỉ ra nhóm này được làm nổi bật.
Lặp lại quá trình này cho các quý còn lại cho đến khi tất cả các nhóm con có các đường chỉ báo nhóm và dấu trừ riêng lẻ.
Sau đó, nhấp vào dấu trừ của bốn nhóm con để chỉ hiển thị tổng số hàng quý và hàng năm, nhớ rằng bạn có thể nhấp vào dấu cộng để mở rộng chúng trở lại.
Một trang tính Excel chứa bốn nhóm con cột đã được thu gọn, với dấu cộng để mở rộng lại các nhóm con được làm nổi bật.
Ngoài việc nhấp vào dấu trừ và dấu cộng để thu gọn và mở rộng một nhóm hoặc nhóm con, bạn có thể nhấp vào các nút số ở bên trái các đường chỉ báo nhóm. Nhấp vào số cao nhất sẽ hiển thị tất cả dữ liệu đã được nhóm, trong khi nhấp vào số thấp nhất sẽ giảm dữ liệu xuống nhóm con nhỏ nhất mà bạn đã tạo.
Data Groups là một thủ thuật Excel tuyệt vời để quản lý dữ liệu lớn, tạo ra các báo cáo có cấu trúc rõ ràng và cải thiện khả năng đọc của bảng tính.
4. Custom Number Formats (Định dạng Số Tùy chỉnh): Trực quan hóa dữ liệu siêu tốc
Công cụ Custom Number Format (Định dạng Số Tùy chỉnh) là một trong những tính năng ẩn yêu thích của chúng tôi trong Microsoft Excel vì nó giúp tiết kiệm thời gian đáng kể. Đây là một thủ thuật Excel mạnh mẽ cho phép bạn kiểm soát cách dữ liệu được hiển thị mà không làm thay đổi giá trị thực của chúng.
Mỗi ô trong một bảng tính mới đều có định dạng số General (Chung).
Một ô trong Excel được chọn và định dạng số General trong tab Home được làm nổi bật.
Điều này có nghĩa là các số được hiển thị chính xác như khi chúng được nhập, trừ khi chúng là một số thập phân quá rộng cho ô hoặc một số bắt đầu bằng số không.
Tuy nhiên, khi bạn chèn một loại số nhất định—như ngày tháng hoặc phần trăm—vào một ô, Excel sẽ nhận diện và cập nhật định dạng số tương ứng.
Một trang tính Excel hiển thị phần trăm trong ô L1, và định dạng số tương ứng trong nhóm Number của tab Home phản ánh loại dữ liệu này.
Một định dạng số ẩn khác là Custom (Tùy chỉnh), cho phép bạn định dạng một ô theo cách không có trong các định dạng số tiêu chuẩn của Excel.
Truy cập và hiểu cấu trúc định dạng tùy chỉnh
Chọn ô hoặc các ô mà bạn muốn áp dụng định dạng sắp nhập, và trong tab Home (Trang chủ) trên thanh ribbon, nhấp vào biểu tượng mở hộp thoại (dialog box launcher) ở góc của nhóm Number (Số).
Ngoài ra, bạn có thể nhấn Ctrl+1 để mở hộp thoại Format Cells (Định dạng ô), nhấn Tab để mở menu Category (Danh mục), và gõ Cus.
Biểu tượng mở hộp thoại Định dạng số trong Excel được làm nổi bật, và định dạng số Tùy chỉnh trong hộp thoại Định dạng ô được chọn.
Thông tin bạn nhập vào trường Type (Loại) phụ thuộc vào các loại dữ liệu bạn muốn định dạng.
Trường Type trong phần Custom của Excel.
Đối số đầu tiên bạn nhập định dạng số dương, đối số thứ hai định dạng số âm, đối số thứ ba định dạng số không, và đối số thứ tư định dạng văn bản. Lưu ý cách dấu chấm phẩy được sử dụng để phân tách các đối số.
POSITIVE;NEGATIVE;ZERO;TEXTMột sự kết hợp bạn có thể nhập vào trường này là:
;;;Điều này cho Excel biết bạn muốn ô đó xuất hiện trống vì bạn chưa chỉ định bất kỳ định dạng nào cho bất kỳ loại dữ liệu nào.
Mặt khác, việc gõ:
[Green]#.00;[Red]#.00;[Blue];sẽ biến các số dương thành giá trị màu xanh lá cây với hai chữ số thập phân, các số âm thành giá trị màu đỏ với hai chữ số thập phân, số không thành màu xanh dương, và tạo ra một ô trống cho bất kỳ giá trị văn bản nào.
Bốn ô trong Excel được định dạng theo mã định dạng số Tùy chỉnh được chèn trong hộp thoại Định dạng số.
Bạn có thể quen với việc đạt được kết quả tương tự bằng cách sử dụng công cụ Conditional Formatting (Định dạng có điều kiện) của Excel, vì công cụ này nổi bật hơn trên thanh ribbon so với định dạng số Tùy chỉnh. Tuy nhiên, Conditional Formatting yêu cầu bạn nhập nhiều quy tắc khác nhau và tốn nhiều thời gian hơn.
Thêm màu sắc và biểu tượng vào định dạng số
Nếu bạn muốn định dạng giá trị bằng các màu khác ngoài đen, trắng, đỏ, xanh lá cây, xanh dương, vàng, magenta, và cyan, hãy nhập mã màu trong dấu ngoặc vuông thay vì tên màu.
Các mã màu có thể được sử dụng trong định dạng số tùy chỉnh trong Microsoft Excel.
Bạn cũng có thể thêm các biểu tượng vào định dạng số Tùy chỉnh của ô. Ví dụ, gõ:
▲;▼;"=";sẽ hiển thị một mũi tên lên thay vì số dương, một mũi tên xuống thay vì số âm, và dấu bằng cho số không.
Nếu bạn đang sử dụng hệ điều hành Windows, nhấn Alt+30 để có ▲, và Alt+31 để có ▼.
Hơn nữa, bạn có thể kết hợp tất cả các mã trên để tạo ra các ô được định dạng gọn gàng và cải thiện tính thẩm mỹ trực quan của dữ liệu. Ví dụ, gõ
[Green]▲#%;[Red]▼#%;"-";cho định dạng Tùy chỉnh của các ô D2 đến D12 sẽ tạo ra kết quả này:
Một danh sách các đội thể thao trong Excel, tổng điểm của họ qua hai mùa giải, và phần trăm thay đổi được định dạng qua định dạng số tùy chỉnh.
Định dạng số Tùy chỉnh là một thủ thuật Excel cực kỳ mạnh mẽ để nhanh chóng trực quan hóa dữ liệu, giúp bạn truyền tải thông điệp một cách rõ ràng và chuyên nghiệp hơn mà không cần đến Conditional Formatting phức tạp.
Kết luận
Microsoft Excel không chỉ là một công cụ bảng tính đơn thuần mà còn là một kho tàng các tính năng mạnh mẽ đang chờ được khám phá. Bốn thủ thuật Excel nâng cao mà thichcongnghe.net đã giới thiệu – Goal Seek, Camera Tool, Data Groups và Custom Number Formats – là minh chứng rõ ràng cho điều đó. Việc nắm vững những bí kíp này không chỉ giúp bạn giải quyết các vấn đề phức tạp một cách hiệu quả hơn mà còn tối ưu hóa quy trình làm việc, biến bạn thành một người dùng Excel chuyên nghiệp.
Từ việc lập kế hoạch tài chính chính xác đến việc tạo ra các dashboard động và quản lý bảng tính khổng lồ một cách khoa học, những tính năng ẩn này sẽ nâng cao đáng kể năng suất và kỹ năng phân tích dữ liệu của bạn. Hãy dành thời gian để thực hành và tích hợp chúng vào công việc hàng ngày của mình. Bạn sẽ ngạc nhiên về sự khác biệt mà chúng mang lại.
Bạn đã từng sử dụng những thủ thuật Excel này chưa? Hay bạn có những bí kíp Excel độc đáo nào khác muốn chia sẻ? Hãy để lại bình luận bên dưới và cùng thảo luận với cộng đồng yêu công nghệ tại thichcongnghe.net nhé!
