Bài viết: 29 cách tiết kiệm thời gian với các công thức Excel (phần 2)

[kyo]

29 cách tiết kiệm thời gian với các công thức Excel (phần 2)

11. Chuyển đổi hiển thị tất cả các công thức cùng một lúc

Bất cứ khi nào bạn chỉnh sửa một ô có chứa hàm, Excel sẽ tự động hiển thị công thức thay vì kết quả của nó. Nhưng đôi khi bạn muốn xem tất cả các công thức trên một bảng tính cùng một lúc. Để làm điều này, chỉ cần sử dụng tổ hợp phím tắt sau để hiển thị các hàm: Ctrl + ~ (1 dấu ngã).

Với phím tắt này, bạn có thể nhanh chóng chuyển đổi hiển thị tất cả các công thức trên một bảng tính hoặc không. Đây là một cách tốt để xem tất cả các công thức cùng một lúc, và để kiểm tra công thức cho nhất quán.

12. Chọn tất cả các công thức trong bảng tính cùng một lúc

Có một cách khác để chọn tất cả các công thức trong bảng tính cùng một lúc. Đó là sử dụng tính năng mạnh mẽ hơn (nhưng ẩn) trong Excel: Go To >> Special (Ctrl + G).

Với lệnh này, bạn có thể chọn tất cả những điều thú vị trong Excel, bao gồm các ô trống, các ô có chứa các số, và nhiều hơn nữa. Một trong số đó là các ô có chứa công thức. Để chọn tất cả các ô có chứa công thức trên một bảng tính, chỉ cần gõ Ctrl + G để vào hộp thoại Go To, sau đó nhấn nút Special, sau đó chọn Formulas. Khi bạn nhấp OK, tất cả các ô có chứa công thức sẽ được lựa chọn.

Nếu bạn muốn chọn chỉ một nhóm của các công thức trong bảng tính, hãy tạo vùng chọn trước, sau đó làm tương tự như trên

13. Sử dụng "Paste special" để chuyển các công thức sang giá trị tĩnh

Một vấn đề thường gặp trong Excel là nhu cầu ngăn chặn các giá trị đã tính toán bị thay đổi. Ví dụ, có thể bạn muốn đơn giản hóa bảng tính bằng cách xóa bớt 1 cột mà bạn đã dùng để tạo ra các giá trị nhất định. Nhưng nếu bạn xóa bỏ các cột này với các công thức vẫn đề cập đến chúng, bạn sẽ nhận được kết quả là lỗi #REF.

Cách giải quyết là: Chuyển các công thức thành các giá trị tĩnh >> xóa các cột thêm vào.

Nhưng làm thế nào để chuyển các công thức thành các giá trị tĩnh? Cách đơn giản nhất để làm điều đó là sử dụng chức năng Paste Special. Đầu tiên, chọn các công thức muốn chuyển đổi và Copy vào Clipboard. Tiếp theo, với các công thức đã được chọn, mở hộp thoại Paste Special bằng nhấn tổ hợp phím Ctrl+Alt+V, và chọn mục Values. Như vậy, bạn sẽ thay thế tất cả các công thức được chọn với các giá trị đã được tính toán.

14. Sử dụng "Paste special" để hiệu chỉnh giá trị tại chỗ

Một vấn đề khác thường gặp trong Excel là nhu cầu thay đổi nhiều giá trị tại chỗ. Ví dụ, bạn có một danh sách giá cả của 500 sản phẩm và muốn tăng tất cả giá lên 10%. Hoặc có danh sách 100 ngày muốn được chuyển sang tương lai 1 tuần. Trong các trường hợp này, bạn có thể thêm một cột để trợ giúp trong bảng tính, thực hiện các tính toán cần thiết, chuyển các kết quả thành các giá trị tĩnh, sau đó copy chúng vào cột ban đầu. Nhưng nếu chỉ muốn một tính toán đơn giản, Paste Special sẽ đơn giản và nhanh hơn, bởi vì có thể thay đổi trực tiếp các giá trị mà không cần thêm các công thức.

Ví dụ, muốn chuyển danh sách các ngày tại chỗ sang 1 tuần sau đó, hãy làm như sau: thêm số 7 vào bất kỳ ô nào trong vùng làm việc, sau đó copy nó vào clipboard.

Tiếp theo, chọn tất cả các ngày muốn thay đổi. Sau đó, sử dụng Paste Special >> Operations >> Add >> OK

Excel sẽ tự động cộng thêm 7 ngày vào các ngày đã chọn mà không cần phải tạo thêm cột để trợ giúp.

Để tăng 10% các giá tiền, sử dụng cách làm tương tự. Gõ 1.10 vào một ô và copy nó sang clipboard. Sau đó chọn các giá tiền muốn tăng và dùng Paste Special >> Operations >> Multiply >> OK để chuyển các giá tiền tại chỗ.

Lưu ý : Paste Special chỉ làm việc với các giá trị. Không có tác dụng với các công thức.

15. Sử dụng "Named ranges” để làm các công thức dễ đọc hơn

Một trong những mẹo lâu đời nhưng chuyên nghiệp và vẫn hay được sử dụng đó là Name vùng. Chúng làm cho công thức dễ đọc hơn.
Ví dụ, giả sử có một bảng tính làm việc đơn giản, mô tả số giờ đã làm việc của một nhóm nhỏ. Với mỗi cá nhân trong nhóm, bạn muốn nhân số giờ làm việc với một mức lương theo giờ, giả sử mức lương theo giờ trong ô C2, công thức sẽ như thế này:
= B5*$C$2
= B6*$C$2
= B7*$C$2

Nhưng nếu đặt tên ô C2 "Luong_gio", công thức sẽ trông như thế này:
= B5*Luong_gio
= B6*Luong_gio
= B7*Luong_gio

Đặt tên vùng làm cho các công thức dễ đọc hơn, và tiết kiệm nhiều lần nhấn dấu $ để tạo ra các địa chỉ tuyệt đối. Vậy làm thế nào để đặt tên vùng? Rất đơn giản. Chỉ cần chọn vùng/ô muốn đặt tên, sau đó đánh một tên trong Namebox và nhấn Enter. Bây giờ, đã có tên cho vùng, Excel chỉ sử dụng nó bất cứ lúc nào bạn trỏ hay nhấn chuột vào vùng mà đang viết công thức - khi nhấn vào một Name vùng, bạn sẽ thấy tên tự động được cài vào hàm. Thêm nữa, có thể dễ dàng định hướng Name vùng bất cứ lúc nào bạn muốn. Chỉ việc chọn tên xuất hiện trong danh sách thả xuống bên phải mục Namebox.

16. Sử dụng Name vào công thức đang có một cách tự động

Chuyện gì sẽ xảy ra khi bạn đã tạo các công thức và sau đó tạo 1 Name vùng để dùng trong công thức? Không có gì xảy ra. Thật sự là vậy. Excel sẽ không tạo bất kì thay đổi nào cho các công thức hiện có hoặc gợi ý các Name vùng một cách tự động. Tuy nhiên, có một cách để đưa các Name vùng vào các công thức hiện có. Chỉ việc chọn công thức mà muốn đưa Name vào, sau đó dùng chức năng Apply Names.
Một khi cửa sổ Apply Names được mở, chọn Name muốn đưa vào và nhấn OK. Excel sẽ thay thế bất kì các tham chiếu tương ứng với các Name được chọn.

17. Lưu một công thức chưa hoàn thành

Nếu bạn đang làm việc với một công thức phức tạp, sẽ mất một khoảng thời gian để hoàn thiện công thức đúng. Nhưng bạn có việc cần phải ngừng lại và sẽ quay trở lại với công thức sau đó để hoàn thiện. Thật không may, Excel sẽ không cho phép bạn nhập một công thức mà chưa hoàn thiện. Nếu còn cố gắng, Excel sẽ hiện thông báo là công thức bị lỗi, và sẽ không cho bạn tiếp tục cho đến khi giải quyết được các vấn đề.
Tuy nhiên, có một cách để giải quyết cho tình huống này là chuyển đổi công thức tạm thời sang dạng chữ (text). Để làm điều đó, bạn có thể thêm vào một dấu ~ để bắt đầu công thức (trước dấu =), hoặc xóa dấu =. Trong cả hai trường hợp, Excel sẽ ngừng việc đánh giá công thức và sẽ cho phép bạn nhấn Enter. Sau đó, bạn có thể làm việc khác và quay trở lại với công thức để hoàn thiện 1 lúc nào đó.

18. Hiểu biết các hàm mà Excel cung cấp

Các hàm tồn tại để giải quyết các vấn đề nhất định. Bạn có thể nghĩ về hàm như là công thức được xây dựng trước với một tên nhất định, mục đích và giá trị trả về. Ví dụ hàm PROPER chỉ có một mục đích: chuyển thành chữ in hoa đầu mỗi từ. Đưa ra một dòng chữ "nEW york CiTY" và nó sẽ chuyển thành "New York City". Các hàm sẽ hữu dụng khi chúng giải quyết một vấn đề cho bạn, do vậy bạn nên làm quen với các hàm có sẵn trong Excel.

Lưu ý : Mọi người hay nhầm lẫn các thuật ngữ được sử dụng để nói về các hàm và các công thức. Một cách dễ để nghĩ về điều đó như sau: mọi thứ bắt đầu với dấu bằng trong Excel là công thức. Bằng cách định nghĩa này, tất cả các hàm đều là công thức, và công thức có thể chứa nhiều hàm.

19. Sử dụng F4 để chuyển tham chiếu tương đối và tuyệt đối

Chìa khóa trong việc xây dựng công thức để bạn có thể sao chép sang vị trí mới mà vẫn hoạt động cách chính xác, đó là sử dụng thành thục, kết hợp nhuần nhuyễn các tham chiếu tuyệt đối và tương đối. Điều này rất quan trọng vì nó cho phép bạn sử dụng lại công thức hiện tại thay vì tạo ra những công thức mới, và tái sử dụng cùng 1 công thức nhằm làm giảm đáng kể khả năng sai sót trong 1 bảng tính, bằng cách hạn chế số lượng các công thức cần phải kiểm tra.

Tuy nhiên, việc chuyển đổi qua lại giữa các tham chiếu tương đối và tuyệt đối có thể là một mối phiền toái - gõ vào tất cả những ký tự $ thật là tẻ nhạt và dễ bị lỗi. May mắn thay, có một phím tắt tuyệt vời cho phép bạn nhanh chóng chuyển đổi qua 4 tùy chọn có sẵn cho mỗi tham chiếu, F4.

Bạn chỉ cần đặt con trỏ vào 1 tham chiếu rồi sử dụng phím tắt. Mỗi khi bạn bấm F4, Excel sẽ "xoay" tới các tùy chọn theo thứ tự : hoàn toàn tương đối (A1) >> hoàn toàn tuyệt đối ($A$1) >> dòng tuyệt đối (A$1) >> cột tuyệt đối ($A1).

Nguyễn Bảo Khanh.

Một số bài viết có liên quan:
1/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
2/ Cách viết hàm hiệu quả
3/ Giới thiệu VBA trong Excel