Cách áp dụng cùng một công thức cho nhiều ô trong Excel với các giá trị khác nhau

Đối với excel, không có gì tiết kiệm thời gian hơn việc sử dụng các công thức. Trên thực tế, đây là nguồn sống của ứng dụng Microsoft Office này. Tuy nhiên, tạo ra một công thức hữu ích là điều khó nhất trong tất cả những việc phải làm với excel. Bên cạnh đó, bạn cần biết cách áp dụng công thức cụ thể đó cho nhiều ô tùy thuộc vào số lượng dữ liệu

Vì lý do đó, chúng tôi biết bạn không thể làm việc với chúng nếu không có một số thông tin. Chúng tôi ở đây để giúp bạn tìm hiểu cách áp dụng cùng một công thức cho nhiều ô trong Excel và cuối cùng chia sẻ một số mẹo về cách tiết kiệm thời gian khi sử dụng excel với các công thức

Có một số cách bạn có thể áp dụng cùng một công thức cho nhiều ô trong Excel. Tại thời điểm này, chúng ta sẽ thảo luận về một số trong số chúng, đặc biệt là những cách đơn giản và rất hiệu quả.

Mục lục

• Sử dụng Tự động điền
• Sử dụng VBA
• Để tiết kiệm thời gian với các công thức, hãy sử dụng các mẹo bên dưới

Sử dụng Tự động điền

Bạn luôn có thể sử dụng tính năng Tự động điền để áp dụng công thức trong nhiều ô. Để làm điều này, hãy làm theo quy trình dưới đây;

Chọn một ô trống và nhập công thức bạn cần

Chọn một trong các ô trong trang tính và cuối cùng nhập công thức bạn muốn thêm. Giả sử đó là =SUM(A2. B2). Sau đó, bạn có thể kéo núm điều khiển tự động điền sang bên phải để có thể điền công thức vào tất cả các hàng

Bây giờ hãy kéo núm điều khiển tự động điền xuống phạm vi bạn muốn. Thao tác này sẽ điền công thức vào tất cả các ô trong cột

Một cách đơn giản khác để thực hiện việc này là chọn phạm vi bạn muốn công thức được áp dụng và cuối cùng nhấp vào Trang chủ>Điền>Xuống và điền> Phải

Sử dụng VBA

VBA là một phương pháp khác mà bạn có thể sử dụng để áp dụng cùng một công thức cho nhiều ô. Để sử dụng VBA, hãy làm theo quy trình dưới đây;

• Nhấn Alt và F11 cùng lúc. Thao tác này sẽ mở cửa sổ Microsoft Visual Basic cho ứng dụng
• Sau đó, chỉ cần nhấp vào Mô-đun>Chèn, và cuối cùng nó sẽ được chèn vào cửa sổ Mô-đun. Sau này, bạn sẽ cần sao chép VBA sau vào cửa sổ

Sub SetFormula()
'Updateby20140827
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Đặt WorkRng = Ứng dụng. InputBox("Phạm vi", xTitleId, WorkRng. Địa chỉ, Loại. =8)
Ứng dụng. Cập nhật màn hình = Sai
Đối với mỗi Rng Trong WorkRng
    Rng. Giá trị = (Rng. Giá trị * 3) / 2 + 100
Tiếp theo
Ứng dụng. ScreenUpdating = True
End Sub

• Sau đó, nhấp vào áp dụng và một cửa sổ bật lên sẽ xuất hiện mà sau này bạn có thể nhấp vào để chọn phạm vi
• Bây giờ hãy nhấp vào ok và toàn bộ lề đã chọn sẽ nhận được công thức

Đó là hai cách chính để bạn có thể áp dụng cùng một công thức trên nhiều ô

Để tiết kiệm thời gian với các công thức, hãy sử dụng các mẹo bên dưới

Sao chép công thức và giữ cho các tham chiếu không bị thay đổi

Bạn luôn nên sao chép công thức vào khay nhớ tạm vì bạn sẽ rất dễ dàng và đơn giản để dán công thức vào một vị trí mới. Nếu bạn có nhiều công thức, bạn nên sử dụng tìm và dán. Điều này có nghĩa là bạn nên bắt đầu bằng việc chọn công thức bạn muốn sao chép và cuối cùng thay thế dấu bằng trong công thức bằng dấu thăng

Nhấp đúp vào núm điều khiển điền để sao chép công thức

Khi bạn có các công thức ở định dạng sao chép, sẽ khá đơn giản để dán chúng bất cứ khi nào bạn muốn. Điều này có thể giúp bạn tiết kiệm thời gian mà lẽ ra bạn có thể sử dụng để viết lại công thức

Sử dụng Autocomplete+tab để nhập chức năng

Điều tốt là Excel thường lưu hầu hết các hàm bất cứ khi nào bạn nhập chúng. Đây là những thuật ngữ đơn giản có nghĩa là khi bạn nhập, chúng có khả năng khớp văn bản với các chức năng có sẵn. Trong trường hợp này, hãy sử dụng AutoComplete và tab để nhập chúng

Sử dụng điều khiển + bấm để nhập đối số

Hầu hết người dùng Excel không muốn gõ dấu phẩy giữa các đối số. Do đó, trong trường hợp này, bạn không phải gặp quá nhiều rắc rối vì bạn có thể sử dụng Kiểm soát + Nhấp để nhập điều đó vào đối số.

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

Đây là một cách khác mà bạn có thể tiết kiệm rất nhiều thời gian khi chọn công thức. Thật đơn giản bởi vì khi bạn sẽ có tất cả các công thức trên ngón tay khi bạn tiếp tục

Công thức là bánh mì và bơ của Excel. Nếu bạn thường xuyên sử dụng Excel, tôi cá là bạn sử dụng rất nhiều công thức. Nhưng việc tạo ra một công thức hoạt động có thể mất quá nhiều thời gian. Trong bài viết này, tôi chia sẻ một số thủ thuật hay giúp bạn tiết kiệm thời gian khi làm việc với công thức trong Excel

Video. 20 mẹo tiết kiệm thời gian với công thức Excel

1. Đừng thêm dấu ngoặc đơn cuối cùng vào một hàm

Hãy bắt đầu với một cái gì đó thực sự dễ dàng. Khi bạn nhập một hàm riêng lẻ (SUM,AVERAGE, v.v. ) bạn không cần nhập dấu ngoặc đơn đóng cuối cùng. Ví dụ: bạn chỉ có thể nhập

=SUM(A1. A10

và nhấn quay lại. Excel sẽ thêm dấu ngoặc đơn đóng cho bạn. Chuyện nhỏ mà tiện

Ghi chú. điều này sẽ không hoạt động nếu công thức của bạn chứa nhiều hơn một bộ dấu ngoặc đơn

2. Di chuyển một công thức và giữ cho các tham chiếu không bị thay đổi

Một trong những tính năng mạnh mẽ nhất của Excel là các địa chỉ tương đối — khi bạn sao chép một công thức sang một vị trí mới, tất cả các địa chỉ tương đối sẽ thay đổi. Thường thì đây chính xác là những gì bạn muốn, bởi vì việc sử dụng lại các công thức là một trong những kỹ thuật quan trọng nhất của một trang tính được xây dựng tốt, dễ bảo trì

Nhưng đôi khi bạn cần di chuyển hoặc sao chép một công thức đến một vị trí mới và bạn không muốn công thức đó thay đổi chút nào. Có một số cách bạn có thể làm điều này

Nếu bạn chỉ đang di chuyển một công thức đến một vị trí gần đó, hãy thử kéo và thả. Thao tác kéo sẽ giữ nguyên tất cả các địa chỉ và không thay đổi

Nếu bạn đang di chuyển công thức đến một vị trí xa hơn, hãy sử dụng tính năng cắt và dán. (Thắng lợi. Ctrl + X, Ctrl + V  Máy Mac. Cmd + X, Cmd + V )  Khi bạn cắt một công thức, các tham chiếu của nó không thay đổi

3. Sao chép một công thức và giữ cho các tham chiếu không bị thay đổi

Ghi chú. bạn có thể thay đổi tất cả các tham chiếu ô để biến chúng thành tuyệt đối, nhưng mẹo này giả định rằng bạn không muốn làm điều đó vì bất kỳ lý do gì

Nếu bạn chỉ cần sao chép một công thức duy nhất, hãy chọn toàn bộ công thức trong thanh công thức và sao chép vào khay nhớ tạm. Sau đó, bạn có thể dán tại một vị trí mới. Kết quả sẽ là một công thức giống hệt với ban đầu

Để sao chép một nhóm công thức sang một vị trí mới mà không ảnh hưởng đến tham chiếu, bạn có thể sử dụng tìm và thay thế. Chọn công thức bạn muốn sao chép, sau đó tìm kiếm và thay thế dấu bằng (=) trong công thức bằng ký tự băm (#) . Điều này sẽ chuyển đổi các công thức thành văn bản. Bây giờ sao chép và dán các công thức vào một vị trí mới. Sau khi bạn dán các công thức và với tất cả các công thức đã chọn, hãy đảo ngược quy trình tìm kiếm và thay thế. Tìm kiếm hàm băm (#) và thay thế dấu bằng (=). Điều này sẽ khôi phục các công thức để làm việc. (Tín dụng. Tom Utis)

4. Nhấp đúp vào núm điều khiển điền để sao chép công thức

Khi bạn thêm công thức vào bảng, một trong những việc bạn làm thường xuyên nhất là sao chép công thức từ hàng đầu tiên của bảng sang hàng cuối cùng của bảng. Nếu bạn biết các phím tắt để điều hướng dữ liệu trong Excel, bạn có thể sử dụng chúng để nhanh chóng dán toàn bộ cột công thức chỉ bằng một vài lần nhấn phím. Tuy nhiên, điều khiển điền thậm chí còn nhanh hơn vì không cần điều hướng đến cuối bảng

Fill handle là hình chữ nhật nhỏ nằm ở góc dưới bên phải của tất cả các vùng chọn trong Excel. Miễn là công thức nằm trong một cột bên cạnh một cột khác sẽ có một bộ dữ liệu đầy đủ, bạn chỉ cần nhấp đúp vào núm điều khiển điền để sao chép toàn bộ công thức xuống cuối bảng

Ghi chú. Mẹo này sẽ không hiệu quả nếu không có đầy đủ cột dữ liệu ở bên trái công thức bạn đang nhập. Excel sử dụng dữ liệu này để tìm ra khoảng cách xuống trang tính để sao chép công thức. Tuy nhiên, bạn có thể sử dụng phương pháp tiếp theo này

Nếu vùng chọn không quá lớn, bạn cũng có thể điền công thức xuống một trang tính bằng cách sử dụng phím tắt cho Điền xuống (Control + D). Trước tiên, chỉ cần đảm bảo chọn công thức gốc và các ô đích. Thao tác này nhanh hơn thao tác sao chép/dán, nhưng không nhanh bằng thao tác điền, đặc biệt nếu bạn đang sao chép công thức vào một nhóm lớn các ô

Video. Các phím tắt để di chuyển xung quanh một danh sách lớn nhanh chóng

5. Sử dụng bảng để tự động nhập công thức

Một cách thậm chí còn nhanh hơn để nhập công thức là trước tiên hãy chuyển đổi bảng của bạn thành Bảng Excel chính thức. Thuật ngữ ở đây khó hiểu, vì bất kỳ dữ liệu nào có nhiều hơn một cột về mặt kỹ thuật đều là "bảng" nhưng Excel có cấu trúc chính thức được gọi là Bảng mang lại nhiều lợi ích. Sau khi bạn chuyển đổi dữ liệu của mình thành một bảng (Cả hai nền tảng. Ctrl + T), tất cả các công thức bạn nhập ở hàng đầu tiên sẽ được tự động sao chép xuống toàn bộ chiều dài của bảng. Điều này tiết kiệm rất nhiều thời gian và cũng giúp ngăn ngừa lỗi

Như một phần thưởng, bất cứ khi nào bạn cập nhật một công thức trong một bảng, Excel sẽ cập nhật lại tất cả các công thức giống như vậy trong cùng một cột

Ghi chú. các công thức trong một bảng sẽ tự động sử dụng các tham chiếu có cấu trúc (i. e. trong ví dụ trên =[@Giờ]*[@Rate] thay vì =C4*D4. Tuy nhiên, bạn có thể nhập địa chỉ ô trực tiếp và chúng sẽ được giữ nguyên

6. Sử dụng AutoComplete + tab để nhập chức năng

Khi bạn nhập dấu bằng và bắt đầu nhập, Excel sẽ bắt đầu khớp văn bản bạn nhập với danh sách lớn các hàm có sẵn. Khi bạn nhập, bạn sẽ thấy danh sách các chức năng "ứng viên" xuất hiện bên dưới. Danh sách này sẽ thu hẹp với mỗi chữ cái bạn gõ. Khi hàm bạn muốn được chọn trong danh sách, bạn có thể "yêu cầu" Excel nhập hàm đó cho bạn bằng cách nhấn tab. Trên Windows, các chức năng được chọn tự động khi bạn nhập. Trên máy Mac, các tùy chọn được hiển thị nhưng không được chọn, vì vậy bạn cần thực hiện thêm một bước nữa. sử dụng phím mũi tên để chọn hàm bạn muốn, sau đó nhấn tab hoặc quay lại để Excel nhập hàm cho bạn

7. Sử dụng Control + bấm để nhập đối số

Không thích gõ dấu phẩy giữa các đối số? . Khi bạn đang nhập đối số trong một hàm, chỉ cần nhấn giữ Control (Mac. Command) khi bạn nhấp vào từng tham chiếu và Excel sẽ tự động nhập dấu phẩy cho bạn. Ví dụ, bạn có thể nhập một công thức như. =SUM(A1, B10, C5. C10) bằng cách nhập "=SUM(" rồi giữ Control khi bấm vào từng tham chiếu. Điều này sẽ hoạt động với bất kỳ chức năng nào mà bạn đang cung cấp tham chiếu làm đối số

8. Sử dụng cửa sổ mẹo công thức để chọn đối số

Bất cứ khi nào bạn làm việc với công thức có chứa hàm Excel, hãy nhớ rằng bạn luôn có thể sử dụng cửa sổ gợi ý để chọn đối số. Đây có thể là một trình tiết kiệm thời gian thực nếu công thức phức tạp, đặc biệt nếu nó chứa nhiều dấu ngoặc đơn lồng nhau. Để chọn đối số, hãy thực hiện theo hai bước. Ở bước một, bấm để đặt con trỏ bên trong hàm có đối số mà bạn muốn chọn. Sau đó, Excel sẽ hiển thị một gợi ý cho hàm đó hiển thị tất cả các đối số. Ở bước hai, nhấp vào đối số bạn muốn chọn. Excel sẽ chọn toàn bộ đối số, ngay cả khi nó chứa các hàm hoặc công thức khác. Đây là một cách hay để chọn đối số khi sử dụng phím F9 để gỡ lỗi công thức

Muốn tìm hiểu thêm?

9. Chèn trình giữ chỗ đối số hàm bằng phím tắt

Thông thường, khi bạn nhập một hàm, Excel sẽ đưa ra mẹo về từng đối số khi bạn thêm dấu phẩy. Nhưng đôi khi bạn có thể muốn Excel thêm chỗ dành sẵn cho tất cả các đối số của hàm cùng một lúc. Nếu vậy, bạn sẽ rất vui khi biết rằng có một lối tắt cho điều đó

Khi bạn đang nhập một hàm, sau khi Excel đã nhận ra tên hàm, hãy nhập Control + Shift + A (cả hai nền tảng). Ví dụ: nếu bạn nhập "=DATE(" rồi sử dụng Control + Shift + A, Excel sẽ cung cấp cho bạn "=DATE(năm,tháng,ngày)". Sau đó, bạn có thể bấm đúp vào từng đối số (hoặc sử dụng cửa sổ Mẹo hàm để chọn từng đối số) và thay đổi nó thành giá trị bạn muốn

10. Di chuyển "thứ công thức" ra khỏi con đường của bạn

Đôi khi, khi bạn đang nhập công thức, cửa sổ gợi ý công thức cản trở bạn, chặn chế độ xem của bạn đối với các ô khác mà bạn muốn xem trên trang tính. Khi điều đó xảy ra, hãy nhớ rằng bạn có thể di chuyển cửa sổ gợi ý ra khỏi vị trí của mình. Chỉ cần di chuột qua cạnh của cửa sổ cho đến khi bạn thấy con trỏ thay đổi, sau đó nhấp và kéo đến vị trí mới. Sau đó, bạn có thể tiếp tục nhập hoặc chỉnh sửa công thức của mình. Tùy thuộc vào cấu trúc trang tính của bạn, một cách khác để quản lý vấn đề này là chỉnh sửa công thức trong thanh công thức thay vì trực tiếp trong ô

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 công thức, 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 có thể xem tất cả các công thức trên một trang tính cùng một lúc. Để làm điều này, chỉ cần sử dụng phím tắt để hiển thị công thức. Control + ~ (đó là 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 trang tính hoặc tắt. Đây là một cách hay để xem tất cả các công thức cùng một lúc và để kiểm tra tính nhất quán của các công thức

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

Một cách khác để xem tất cả các công thức trong một trang tính là chọn chúng. Bạn có thể làm điều này bằng cách sử dụng các tính năng mạnh hơn (và ẩn) trong Excel. là Chuyển đến > Đặc biệt (Ctrl + G). Với lệnh này, bạn có thể chọn tất cả những thứ thú vị trong Excel, bao gồm ô trống, ô chứa số, ô trống, v.v. Một trong các tùy chọn là các ô chứa công thức. Để chọn tất cả các ô chứa công thức trên một trang tính, bạn chỉ cần gõ Ctrl + G để xuất hiện hộp thoại Go To, sau đó nhấp vào nút Special, rồi chọn Formulas. Khi bạn bấm OK, tất cả các ô chứa công thức sẽ được chọn

Nếu bạn chỉ muốn chọn một tập hợp con các công thức trong một trang tính, trước tiên hãy thực hiện lựa chọn, sau đó sử dụng lệnh tương tự

13. Sử dụng dán đặc biệt để chuyển đổi công thức thành giá trị tĩnh

Một vấn đề phổ biến trong Excel là cần ngăn các giá trị được tính toán thay đổi. Ví dụ: có thể bạn muốn đơn giản hóa một trang tính bằng cách xóa các cột "trợ giúp" mà bạn đã sử dụng để tạo các giá trị nhất định. Nhưng nếu bạn xóa các cột này mà các công thức vẫn đề cập đến chúng, thì bạn sẽ nhận được vô số lỗi #REF. Giải pháp trước tiên là chuyển đổi công thức thành giá trị, sau đó xóa các cột thừa. Cách đơn giản nhất để làm điều đó là sử dụng Paste Special. Đầu tiên, chọn công thức bạn muốn chuyển đổi và Sao chép vào khay nhớ tạm. Tiếp theo, với các công thức vẫn được chọn, hãy mở hộp thoại Paste Special (Win. Ctrl + Alt + V, Mac. Ctrl + Cmd + V) và sử dụng tùy chọn Giá trị. Thao tác này sẽ thay thế tất cả các công thức bạn đã chọn bằng các giá trị mà chúng đã tính toán

14. Sử dụng Paste Special để điều chỉnh các giá trị tại chỗ

Một vấn đề phổ biến khác trong Excel là cần thay đổi nhiều giá trị tại chỗ. Ví dụ: có lẽ bạn có danh sách 500 giá sản phẩm và bạn cần tăng giá tất cả lên 5%. Hoặc có thể bạn có một danh sách gồm 100 ngày cần được chuyển đến tương lai sau một tuần? . Nhưng nếu bạn chỉ cần một phép tính đơn giản thì Paste Special đơn giản và nhanh hơn nhiều, vì bạn có thể thay đổi giá trị trực tiếp mà không cần thêm bất kỳ công thức nào

Ví dụ: để chuyển đổi một tập hợp các ngày tại chỗ thành một tuần sau, hãy làm điều này. thêm số 7 vào bất kỳ ô nào trong trang tính, sau đó sao chép số đó vào khay nhớ tạm. Tiếp theo, chọn tất cả các ngày bạn muốn thay đổi. Sau đó, sử dụng Paste Special > Operations > Add. Khi bạn nhấp vào OK, Excel sẽ thêm số 7 vào ngày bạn đã chọn, di chuyển chúng về phía trước 7 ngày mà không cần tạo cột trợ giúp

Để tăng một nhóm giá lên 10%, hãy sử dụng phương pháp tương tự. Nhập 1. 10 vào một ô và sao chép nó vào khay nhớ tạm. Sau đó, chọn giá bạn muốn thay đổi và sử dụng Dán Đặc biệt > Hoạt động > Nhân để chuyển đổi tất cả giá tại chỗ. Khi bạn nắm được mẹo này, bạn sẽ tìm thấy rất nhiều cách sử dụng thông minh cho nó

Ghi chú. điều này chỉ hoạt động với các giá trị. Đừng thử với công thức

15. Sử dụng các phạm vi được đặt tên để làm cho công thức dễ đọc hơn

Một trong những mẹo chuyên nghiệp lâu đời nhất trong cuốn sách là sử dụng các phạm vi được đặt tên trong công thức của bạn để làm cho chúng dễ đọc hơn. Ví dụ: giả sử bạn có một trang tính đơn giản hiển thị số giờ làm việc cho một nhóm nhỏ. Đối với mỗi người, bạn muốn nhân số giờ làm việc của họ với một tỷ lệ hàng giờ. Giả sử tỷ lệ hàng giờ nằm ​​trong ô A1, công thức của bạn có thể trông như thế này

=B3 * $A$1
=B4 * $A$1
=B5 * $A$1

Nhưng nếu bạn đặt tên ô A1 là "hourly_rate", công thức của bạn sẽ như thế này

=B3 * tỷ lệ hàng giờ
=B4 * tỷ lệ hàng giờ
=B5 * tỷ lệ hàng giờ

Đặt tên cho các phạm vi theo cách này làm cho công thức dễ đọc hơn rất nhiều và tiết kiệm việc nhập nhiều ký hiệu đô la ($) để tạo tham chiếu tuyệt đối

Đặt tên phạm vi là dễ dàng. Chỉ cần chọn phạm vi/(các) ô bạn muốn đặt tên, sau đó nhập tên vào hộp tên và nhấn enter. Bây giờ bạn đã đặt tên cho một phạm vi, Excel sẽ sử dụng phạm vi đó bất cứ khi nào bạn trỏ và bấm vào phạm vi đó khi bạn đang xây dựng công thức — khi bạn bấm vào một phạm vi đã đặt tên, bạn sẽ thấy tên của phạm vi đó tự động được chèn vào công thức. Ngoài ra, bạn cũng có thể dễ dàng điều hướng đến phạm vi được đặt tên bất cứ khi nào bạn muốn. Chỉ cần chọn tên từ danh sách thả xuống xuất hiện bên cạnh hộp tên

16. Tự động áp dụng tên cho các công thức hiện có

Điều gì xảy ra khi bạn đã tạo các công thức rồi tạo một dải ô đã đặt tên mà bạn muốn sử dụng trong các công thức đó? . Excel sẽ không thực hiện bất kỳ thay đổi nào đối với các công thức hiện tại của bạn hoặc đề xuất tự động áp dụng tên dải ô mới. Tuy nhiên, có một cách để áp dụng tên dãy cho các công thức hiện có. Chỉ cần chọn công thức bạn muốn áp dụng tên, sau đó sử dụng tính năng Áp dụng tên

Khi cửa sổ Áp dụng tên mở ra, hãy chọn tên bạn muốn áp dụng và nhấp vào OK. Excel sẽ thay thế mọi tham chiếu tương ứng bằng tên bạn đã chọn

17. Lưu một công thức chưa được thực hiện

Nếu bạn đang làm việc với một công thức phức tạp hơn, rất có thể sẽ mất một lúc để công thức hoạt động bình thường. Nhưng bạn có thể không có nhiều thời gian và cần quay lại công thức sau để công thức hoạt động như ý muốn. Thật không may, Excel sẽ không cho phép bạn nhập một công thức bị hỏng. Nếu bạn cố gắng, Excel sẽ lớn tiếng phàn nàn rằng công thức có lỗi và sẽ không cho phép bạn tiếp tục cho đến khi bạn giải quyết xong mọi vấn đề

Tuy nhiên, có một cách giải quyết dễ dàng. chỉ tạm thời chuyển đổi công thức thành văn bản. Để thực hiện việc này, bạn có thể thêm một dấu nháy đơn vào đầu công thức (trước dấu =) hoặc chỉ cần xóa hoàn toàn dấu bằng. Trong cả hai trường hợp, Excel sẽ ngừng cố gắng đánh giá công thức và sẽ cho phép bạn nhập nguyên trạng. Sau đó, bạn có thể quay lại công thức và tiếp tục công việc

18. Hãy nhận biết các chức năng mà Excel cung cấp

Các chức năng tồn tại để giải quyết các vấn đề cụ thể. Bạn có thể coi hàm như một công thức dựng sẵn với tên, mục đích và giá trị trả về cụ thể. Ví dụ: hàm PROPER chỉ có một mục đích. nó viết hoa các từ. Cung cấp cho nó văn bản như "nEW york City" và nó sẽ trả lại cho bạn "Thành phố New York". Các hàm cực kỳ hữu ích khi chúng giải quyết vấn đề mà bạn gặp phải, vì vậy bạn nên tự làm quen với các hàm có sẵn trong Excel

Nhìn thấy. Hướng dẫn Hàm Excel và 500 Ví dụ về Công thức Excel để biết danh sách các hàm và công thức thường được sử dụng

Ghi chú. Mọi người thường bị nhầm lẫn bởi các thuật ngữ được sử dụng để nói về chức năng và công thức. Một cách dễ dàng để nghĩ về nó là thế này. mọi thứ bắt đầu bằng dấu bằng trong Excel đều là công thức. Theo định nghĩa này, tất cả các hàm cũng 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 đổi tham chiếu tương đối và tuyệt đối

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

Tuy nhiên, việc chuyển đổi các tham chiếu qua lại giữa các tham chiếu tương đối và tuyệt đối có thể gây phiền toái — việc nhập tất cả các ký hiệu đô la ($) đó rất 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 qua 4 tùy chọn có sẵn cho mỗi tài liệu tham khảo. (Các cửa sổ. F4, máy Mac. Lệnh + T). Chỉ cần đặt con trỏ vào một tham chiếu sử dụng phím tắt. Mỗi lần bạn sử dụng, Excel sẽ "xoay vòng" sang tùy chọn tiếp theo theo thứ tự này. hoàn toàn tương đối (A1) > hoàn toàn tuyệt đối ($A$1) > Hàng tuyệt đối (A$1) > cột tuyệt đối ($A1)

20. Hãy nhớ rằng các công thức và hàm trả về một giá trị. Luôn luôn

Khi bạn gặp khó khăn với một công thức, đôi khi đó là vì bạn nghĩ rằng một phần của công thức đang trả về một giá trị nhất định nhưng trên thực tế, nó lại trả về một giá trị khác. Để kiểm tra những gì thực sự được trả về bởi một hàm hoặc một phần của công thức, hãy sử dụng mẹo F9 bên dưới

Ghi chú. Từ "return" xuất phát từ thế giới lập trình và đôi khi nó gây nhầm lẫn cho những người mới làm quen với khái niệm này. Trong ngữ cảnh của công thức Excel, nó được sử dụng như sau "Hàm LEN trả về một số" hoặc "Công thức trong ô A1 trả về lỗi". Bất cứ khi nào bạn nghe thấy từ "trả về" với công thức, hãy nghĩ ngay đến "kết quả"

21. Sử dụng F9 để đánh giá các phần của công thức

Phím tắt F9 (fn + F9 trên máy Mac) có thể giải các phần của công thức trong thời gian thực. Đây là một công cụ tuyệt vời để gỡ lỗi các công thức lớn hơn, khi bạn cần xác minh rằng kết quả của một phần cụ thể của công thức là những gì bạn đang mong đợi. Để sử dụng mẹo này, hãy chỉnh sửa công thức và chọn biểu thức hoặc hàm bạn muốn đánh giá (mẹo - sử dụng cửa sổ mẹo hàm để chọn toàn bộ đối số). Sau khi bạn có lựa chọn, hãy nhấn F9. Bạn sẽ thấy phần đó của công thức được thay thế bằng giá trị mà nó trả về

Ghi chú. Trên Windows, bạn có thể hoàn tác F9, nhưng điều này dường như không hoạt động trên máy Mac. Để thoát khỏi công thức mà không thực hiện thay đổi, chỉ cần sử dụng Esc

Video. Cách kiểm tra và gỡ lỗi công thức bằng F9

22. Sử dụng Trình đánh giá công thức để giải từng bước một công thức

Khi việc sử dụng F9 để đánh giá một công thức trở nên quá tẻ nhạt, đã đến lúc bạn cần sử dụng phần cứng lớn hơn. đánh giá công thức. Đánh giá Công thức giải công thức từng bước một. Mỗi lần bạn nhấp vào nút Đánh giá, Excel sẽ giải quyết phần được gạch chân của công thức và hiển thị cho bạn kết quả. Bạn có thể tìm thấy Đánh giá công thức trên tab Công thức của dải băng, trong nhóm Kiểm tra công thức. Để sử dụng Đánh giá Công thức, hãy chọn một công thức và bấm vào nút trên ruy-băng. Khi cửa sổ mở ra, bạn sẽ thấy công thức được hiển thị trong hộp văn bản có nút Đánh giá bên dưới. Một phần của công thức sẽ được gạch chân — đây là phần hiện đang "được đánh giá". Khi bạn nhấp vào đánh giá, phần gạch dưới của công thức được thay thế bằng giá trị mà công thức trả về. Bạn có thể tiếp tục nhấp vào đánh giá cho đến khi công thức được giải quyết hoàn toàn

Video. Làm thế nào để đánh giá các công thức phức tạp

Ghi chú. Công thức đánh giá chỉ khả dụng trong phiên bản Windows của Excel. Phiên bản Mac có một cách tiếp cận khác, được gọi là Trình tạo công thức, hiển thị kết quả khi bạn tạo công thức. Chức năng này không hoàn toàn giống nhưng rất hữu ích và bạn có thể sử dụng nó với các công thức đã tồn tại

23. Xây dựng các công thức phức tạp trong các bước nhỏ

Khi bạn cần xây dựng một công thức phức tạp hơn và không chắc chắn cách thực hiện, hãy bắt đầu với phương pháp chung và một số giá trị được mã hóa cứng. Sau đó, thêm nhiều logic hơn để thay thế các giá trị được mã hóa cứng từng bước một. Ví dụ: giả sử bạn muốn viết một công thức trích xuất tên từ một tên đầy đủ. Bạn biết rằng bạn có thể sử dụng hàm LEFT để kéo văn bản từ bên trái, nhưng bạn không chắc cách tính số lượng ký tự cần trích xuất. Bắt đầu bằng   LEFT(full_name, 5) để công thức hoạt động. Sau đó tìm hiểu cách thay thế số 5 bằng một giá trị được tính toán. Trong trường hợp này, bạn có thể tính ra số lượng ký tự cần trích xuất bằng cách sử dụng hàm FIND để xác định vị trí của ký tự khoảng trắng đầu tiên

Video. Cách xây dựng một công thức phức tạp từng bước

24. Sử dụng các phạm vi được đặt tên như các biến

Trong nhiều trường hợp, bạn nên sử dụng các phạm vi được đặt tên như các biến để làm cho công thức của bạn linh hoạt hơn và dễ làm việc hơn. Ví dụ: nếu bạn đang thực hiện nhiều thao tác nối (nối các giá trị văn bản lại với nhau), bạn có thể muốn tạo các phạm vi được đặt tên của riêng mình cho các ký tự dòng mới, ký tự tab, v.v. Bằng cách này, bạn có thể chỉ cần tham khảo trực tiếp các phạm vi được đặt tên thay vì thêm nhiều cú pháp phức tạp vào công thức của mình. Ngoài ra, nếu các dải ô đã đặt tên của bạn chứa các giá trị văn bản thì bạn không cần sử dụng bất kỳ dấu ngoặc kép nào xung quanh văn bản khi thêm chúng vào công thức. Ý tưởng này hơi khó giải thích, vì vậy hãy xem video này để biết ví dụ chi tiết

25. Sử dụng phép nối trong nhãn để làm rõ các giả định

Khi bạn tạo một trang tính dựa trên các giả định nhất định, việc thể hiện rõ ràng các giả định mà bạn đang đưa ra có thể là một thách thức. Thông thường, bạn sẽ có một khu vực nhất định trên trang tính dành cho đầu vào và một khu vực khác dành cho đầu ra và không có chỗ để hiển thị cả hai cùng một lúc. Một cách để đảm bảo các giả định chính rõ ràng là nhúng chúng trực tiếp vào các nhãn xuất hiện trên trang tính bằng cách sử dụng phép nối, thường là với hàm TEXT

Ví dụ: thông thường, bạn có thể có nhãn ghi "Chi phí cà phê" theo sau là chi phí được tính toán. Với phép nối, bạn có thể làm cho nhãn có nội dung "Chi phí cà phê ($2. 00/cốc). điều này cho thấy rõ ràng rằng bạn đã giả định chi phí là 2 đô la. 00/cốc

Video. làm rõ các giả định với nối

26. Thêm ngắt dòng vào các IF lồng nhau để dễ đọc hơn

Khi bạn đang tạo một công thức IF lồng nhau, việc theo dõi các đối số đúng và sai trong một loạt dấu ngoặc đơn có thể rất khó hiểu. Khi bạn sắp xếp các dấu ngoặc đơn, rất dễ mắc lỗi logic. Tuy nhiên, có một cách đơn giản để tạo một công thức có nhiều câu lệnh IF dễ đọc hơn nhiều. chỉ cần thêm ngắt dòng vào công thức sau mỗi đối số TRUE. Điều này sẽ làm cho công thức đọc giống như một bảng hơn

Video. Cách làm cho các IF lồng nhau dễ đọc hơn

27. Nhập các chức năng với AutoComplete

Khi bạn đang nhập một hàm, excel sẽ cố gắng đoán tên của hàm bạn muốn và hiển thị danh sách Tự động Hoàn thành để bạn chọn từ đó. Câu hỏi là, làm thế nào để bạn chấp nhận một trong các tùy chọn được hiển thị mà vẫn ở chế độ chỉnh sửa? . Khi bạn nhấn tab, Excel sẽ thêm chức năng hoàn chỉnh và để con trỏ hoạt động trong dấu ngoặc đơn để bạn có thể điền vào các đối số khi cần. Trên máy Mac, trước tiên bạn cần sử dụng phím mũi tên xuống để chọn chức năng muốn thêm, sau đó nhấn Tab để chèn chức năng

28. Sử dụng AutoSum để nhập công thức SUM

Không phải lúc nào bạn cũng có cơ hội sử dụng tính năng Tự động tính tổng này, nhưng khi bạn làm như vậy, bạn sẽ rất hài lòng. AutoSum hoạt động cho cả hàng và cột. Chỉ cần chọn một ô trống ở bên phải hoặc bên dưới các ô bạn muốn tính tổng, rồi nhập Alt + = (Mac. Lệnh + Shift + T). Excel sẽ đoán phạm vi bạn đang cố tính tổng và chèn hàm SUM trong một bước. Nếu bạn muốn cụ thể hơn để Excel không đoán, trước tiên hãy chọn phạm vi bạn định tính tổng, bao gồm cả ô mà bạn muốn dùng hàm SUM

AutoSum thậm chí sẽ chèn nhiều hàm SUM cùng một lúc. Để tính tổng nhiều cột, hãy chọn một dải ô trống bên dưới các cột. Để tính tổng nhiều hàng, hãy chọn một dải ô trống trong cột ở bên phải các hàng

Cuối cùng, bạn có thể sử dụng AutoSum để cộng cả tổng số hàng và cột cùng lúc cho toàn bộ bảng. Chỉ cần chọn một bảng số đầy đủ, bao gồm các ô trống bên dưới bảng và bên phải bảng, rồi sử dụng phím tắt. Excel sẽ thêm các hàm SUM thích hợp vào các ô trống, cung cấp cho bạn tổng cột, tổng hàng và tổng cuối trong một bước

29. Nhập cùng một công thức vào nhiều ô cùng một lúc

Thông thường, bạn sẽ cần nhập cùng một công thức vào một nhóm ô. Bạn thực sự có thể làm điều này trong một bước bằng phím tắt Control + Enter. Chỉ cần chọn tất cả các ô cùng một lúc, sau đó nhập công thức bình thường như đối với ô đầu tiên. Sau đó, khi bạn hoàn tất, thay vì nhấn Enter, hãy nhấn Control + Enter. Excel sẽ thêm cùng một công thức vào tất cả các ô trong vùng chọn, điều chỉnh tham chiếu nếu cần. Khi bạn sử dụng phương pháp này, bạn không phải sao chép và dán, điền hoặc sử dụng núm điều khiển điền. Bạn đã hoàn thành trong một bước

Bạn cũng có thể sử dụng kỹ thuật tương tự này để chỉnh sửa nhiều công thức cùng một lúc. Chỉ cần chọn tất cả các công thức cùng một lúc, thực hiện thay đổi bạn cần và nhấn Control + Enter

Làm cách nào để dán cùng một công thức vào nhiều ô mà không thay đổi phạm vi?

Làm cách nào để sao chép cùng một giá trị trong nhiều ô trong Excel bằng công thức?