Excel là một công cụ văn phòng rất hữu ích, với nhiều tính năng hay giúp rút gọn nhiều công đoạn làm việc. Vì thế để sử dụng Excel hiệu quả, hãy cùng tìm hiểu những thủ thuật, phím tắt, hàm lệnh cơ bản để sử dụng Excel thành thạo và chuyên nghiệp nhất.
Thủ Thuật khắc phục lỗi #NA trong excel
1.1. Nguyên nhân xuất hiện lỗi #NA trong hàm vlookup
#NA có nghĩa là dò tìm không có giá trị. Những trường hợp làm xảy ra lỗi #N/A thường gặp:
– Lỗi #N/A khi giá trị chúng ta tìm không có trong bảng tìm kiếm
Khi giá trị chúng ta tìm không có trong bảng tìm kiếm sẽ dẫn đến việc tính toán theo hàm Vlookup không có kết quả. Đây trường hợp thường gặp khi chúng ta dùng các hàm tham chiếu hoặc tìm kiếm mà nội dung cần tham chiếu hay tìm kiếm không có sẵn trong vùng bạn chọn.
– Sử dụng Vlookup để dò tìm gần đúng
Ngoài ra, nếu bạn đang sử dụng một công thức với đối sánh gần đúng (khi tham số range_lookup bạn bỏ qua hoặc để là TRUE) thì lúc này công thức Vlookup của bạn có thể trả về lỗi # N/A trong hai trường hợp:
Khi giá trị bạn tra cứu nhỏ hơn giá trị nhỏ nhất trong bảng tính
Các con số nằm trong cột tra cứu không được sắp xếp theo thứ tự tăng dần
– Cột tra cứu nằm bên phải của bảng dữ liệu
Một trong những hạn chế lớn nhất của hàm VLOOKUP là nó không thể dò tìm các dữ liệu nằm phía bên phải cột tra cứu. Vì thế khi bạn thực hiện lệnh này, bảng tính sẽ hiện ra dòng chữ: The lookup column should be the leftmost column of the table array, otherwise VLOOKUP displays the N/A error.
Điều này là một lỗi khá thường gặp vì chúng ta thường quên đi điều này và dẫn đến việc không thể tra cứu dữ liệu vì lỗi N/A, mặc dù bạn đã kiểm tra tất cả các thông số trong hàm và đảm bảo nó đã đúng.
Giải pháp: Sử dụng hàm INDEX, MATCH nhằm thay thế cho hàm VLOOKUP trong trường hợp bạn không thể thay thế cột dữ liệu sang bên phải.
– Sai định dạng của cột số đang tra cứu
The indications of numbers being formatted as text in Excel – Là thông báo cho việc các con số đang được định dạng dưới kiểu văn bản, trong vùng tra cứu hoặc cột dò tìm làm xảy ra lỗi không thể tra cứu.
Bạn có thể xác nhận là số hay chữ dựa vào canh lề mặc định của con số đó. Nếu thấy chữ số đang ở lề phải thì có thể nó đang được canh chỉnh là ở định dạng chữ, vì số thường được canh lề trái của ô.
Nếu đây chỉ là số bị sai định dạng, bạn có thể chọn con số, nhấp chuột phải và chọn “Convert To Number”.
Nếu có nhiều số bị định dạng sai thì hãy chọn tất cả vùng đó, nhấp chuột phải và chọn Format Cells -> Number tab -> Number và chọn OK.
Cách khóa ô trong Excel?
Nguyên nhân
Chức năng khóa ô trong Excel nhằm khóa loại các ô để đảm bảo rằng chúng không thể chỉnh sửa được, từ đó có thể bảo vệ trang tính. Bạn nên sử dụng chức năng này trong những trường hợp sau:
- Hạn chế việc lỡ tay xóa mất công thức, dữ liệu trong file trong quá trình làm việc
- Không muốn người khác tự ý sửa, xóa các công thức đã làm khi gửi file cho người khác. Vì nếu ai đó chỉnh sửa mà không hỏi ý kiến ý của chủ sở hữu có thể làm thông tin bị sai lệch. Bất kỳ chỉnh sửa nào trong các hàm hiện có cũng có thể khiến toàn bộ các công thức khác bị sai lệch và xảy ra các lệnh lỗi.
- Muốn bảo vệ các ô có chứa công thức quan trọng.
Cách thực hiện
- Bước 1: Chọn các ô cần khóa. Nếu bạn muốn khóa toàn bộ trang tính, nhấn Ctrl + A hoặc nhấp vào nút Select All. Nếu bạn chọn cách này, bảng tính sẽ được khóa toàn bộ vì thế bạn phải đảm bảo tất cả các ô đầu tiên phải được mở khóa. Nếu bạn muốn chọn riêng biệt các ô, bấm vào vị trí góc trên bên trái thanh Heading – là thanh thể hiện số dòng, tên cột. Nếu các ô không liền kề, chọn ô đầu tiên sau bấm và giữ phím Ctrl, và chọn các ô khác hoặc các dải ô khác.
- Bước 2: Nhấn chuột phải, sau đó chọn Format Cells (hoặc nhấn tổ hợp phím tắt là Ctrl + 1).
- Bước 3: Trong cửa sổ Format cells, tại thẻ Protection, bạn chọn locked và nhấn Ok. Những mục có định dạng locked thì sẽ được khóa lại hết.
- Bước 4: Nhập mật khẩu để bảo vệ trang tính
Trong Protect Sheet, bạn có thể lựa chọn việc có nên khóa sheet bằng mật khẩu hay không. Nếu khóa bằng mật khẩu, bạn có thể chia sẻ quyền chỉnh sửa cho một số người dùng nhất định, chỉ cần nhập đúng mật khẩu.
Tuy nhiên hãy chọn mật khẩu dễ nhớ hoặc lưu lại vào note, vì nếu bạn quên mật khẩu thì sẽ không có cách chỉnh sửa hoặc thay đổi bất kỳ các yếu tố được bảo vệ mà bạn đã setup. Vì thế nếu bạn lựa chọn dùng mật khẩu thì hãy nhập ký tự vào mục Password to unprotect Sheet. Xác nhận mật khẩu lần hai và nhấn ok để khóa các mục bạn muốn bảo vệ.
Cách chỉnh kích thước ô trong excel?
Nguyên nhân
Bạn đang nhập dữ liệu vào các ô Excel nhưng dữ liệu của bạn quá dài và các ô Excel quá nhỏ, vậy chúng ta cần canh chỉnh các kích thước ô trong excel như độ dài hoặc độ rộng để có thể chứa được hết dữ liệu.
Cách thực hiện thủ công
Thay đổi kích thước của dòng trong Excel:
Nhấp vào cạnh của ô thuộc dòng của bạn muốn thay đổi kích thước, sau đó nhấn giữ chuột trái và kéo xuống dưới đến vị trí phù hợp để đảm bảo ô có chiều rộng vừa đủ.
Thay đổi kích thước của cột trong Excel: Di chuyển chuột đến cạnh của tiêu đề cột muốn thay đổi kích thước -> Nhấn giữ chuột trái và kéo qua phải đến khi cảm thấy phù hợp.
Cách thực hiện bằng công cụ
Như hình minh họa trên đây, bạn có thể dễ dàng nhận thấy chiều dài của cột họ và tên chưa đúng khiến cho tên của các em học sinh bị cắt mất một phần ở phía sau. Điều này sẽ gây khó khăn cho việc tra cứu vì thế chúng ta cần thay đổi kích thước phù hợp cho cột G. Bài viết sẽ hướng dẫn bạn thực hiện bằng công cụ để chúng ta có thể dễ dàng sử dụng, không chỉ trong việc thay đổi kích thước mà còn ứng dụng được trong nhiều thao tác khác.
- Bước 1: Chọn toàn bộ nội dung bạn muốn thay đổi kích thước, chọn nút Home trên thành Menu bạn chuyển sang tab Format.
- Bước 2: Tùy chọn các lệnh phù hợp nhất theo mong muốn của bạn. Ví dụ đề bài muốn thay đổi kích thước của cột G nên chúng ta sẽ chọn vào AutoFit Column Width để tự động căn chỉnh thay đổi độ rộng cột phù hợp với nội dung có trong ô tính đó.
Ngoài ra bạn có thể tùy chọn các tab khác tùy thuộc vào các tính năng dưới đây:
- Row Height : Thay đổi chiều cao của ô tính bằng cách điền vào các thông số. Ví dụ bạn điền vào hộp thoại Row Height là 3.45.
- Column Width: Thay đổi độ rộng của cột. Ví dụ bạn điền vào hộp thoại Column Width là 7.
- AutoFit Row Height: excel sẽ tự động căn chỉnh chiều cao dòng phù hợp với nội dung bạn chọn.
- AutoFit Column Width: excel sẽ tự động căn chỉnh thay đổi độ rộng cột phù hợp với nội dung có trong ô tính đó.
- Default Width: Dùng lệnh này khi bạn muốn căn chỉnh độ rộng mặc định cho Worksheet hay cả Workbook.
Thủ thuật mở khoá không cho nhập dữ liệu vào ô trong excel
Để cung cấp cho người dùng cụ thể quyền sửa các phạm vi trong một trang tính được bảo vệ, bạn cần biết cách cấp phép cho từng người dùng của bảng tính.
Đây là sheet đã bị khóa mục địa chỉ, khiến người dùng không thể chỉnh sửa được. Vì thế để tiến hành sửa đổi, bạn phải mở khóa mục này theo các nước dưới đây:
Bước 1: Chọn vào dữ liệu bạn muốn mở khóa, chọn tab Review, chọn Unprotect sheet trên thanh công cụ và nhấp vào Allow Users to Edit Ranges. Sau đó nhấp vào nút New Range
Bước 2: Sau khi cửa sổ New Range, hãy thực hiện theo các bước sau
Trong hộp Title, hãy nhập tên dải ô bạn muốn, ví dụ: Địa chỉ
Trong ô Refers to cells, nhập phạm vi bạn muốn mở khóa, trong ví dụ trên thì bạn nên chọn vào biểu tượng ở góc phải ô và bôi đen phạm vi bạn cần. Dữ liệu sẽ tự động hóa.
Trong hộp Range password, nhập mật khẩu.
Nhấp vào nút OK và sau đó xác nhận mật khẩu một lần nữa để mở khóa.
Chèn ảnh và tách dữ liệu trong ô Excel
Chèn ảnh vào ô excel
Tính năng này cực kỳ hữu dụng nếu bạn đang kinh doanh, và thao tác nhiều với nhiều mặt hàng với hình ảnh và mã sản phẩm tương ứng.
Cách thực hiện
- Bước 1: Mở tab Insert.
- Bước 2: Click vào Pictures
- Bước 3: Hộp thoại ‘Insert Picture’ xuất hiện, hãy tìm đến thư mục chứa hình ảnh muốn chèn, sau đó bôi đen để chọn (có thể chèn 1 hoặc nhiều ảnh cùng lúc).
- Bước 4: Thay đổi kích thước của hình ảnh sao cho vừa vặn với kích thước ô Excel. Nếu bạn muốn chèn nhiều hình ảnh cùng lúc vào trong ô, bạn có thể chọn nhiều ảnh một lúc khi đang ở bước 4.
Tách dữ liệu trong ô excel
Ví dụ ở bảng tính sau, các em học sinh kèm theo điểm số của mình đang nằm cùng trong cột Họ và tên, và bạn muốn tách riêng phần cột điểm A,B, C sang cột bên cạnh. Nếu dùng phương pháp thủ công sẽ tốn rất nhiều thời gian, đặc biệt khi số lượng lên đến cả trăm hàng. Vì thế bạn cần dùng thủ thuật để tách nhanh chóng và chính xác trong trường hợp này.
Cách thực hiện
- Bước 1: kéo thả để chọn tất cả nội dung có trong vùng cần tách, chỉ bao gồm các giá trị.
- Bước 2: Trên thanh công cụ, chọn mục Text to Columns.
- Bước 3: Lúc này sẽ xuất hiện giao diện hộp thoại Convert Text to Columns Wizard. Chú ý vào những vùng được chọn, đúng ta sẽ thấy bạn cần điền vào ba bước:
- Đầu tiên là chọn vào cách hiển thị của các dữ liệu bạn cần tách
Nếu như các ký tự được ngăn cách bằng tab, dấu gạch ngang, dấu phẩy, khoảng trắng… thì chọn Delimited.
Nếu nằm ngoài trường hợp này thì chọn Fixed with: tách cột theo chiều rộng của dữ liệu.
Ở ví dụ được nêu, hai dạng thông số cần tách được gắn liền với nhau bởi dấu gạch ngang, vì thế chúng ta tích vào ô Delimited.
- Nhớ xem lại các khung cần tách dữ liệu đã đúng với mong muốn hay chưa ở cột Preview.
- Nếu mọi thông số đã đúng, nhấn Next ở bên dưới.
- Bước 4:
Hộp thoại Convert text ở bước số 4 yêu cầu chúng ta chọn định dạng dữ liệu của từng cột. Ví dụ khi bạn chọn General thì ở cột bôi đen nằm trong bảng Data preview sẽ thay đổi thành General. Còn cột thứ 2 thì bạn nên chọn định dạng Text là phù hợp nhất. Sau khi chọn xong thì bấm nút Finish.
- Bước 5: Xác nhận Ok để hộp thoại dưới đây để tiến hành tách xuất dữ liệu.
Và dưới đây là kết quả sau khi bạn thực hiện tách dữ liệu ở một cột thành 2 cột.
Một số thủ thuật, hàm lệnh thường dùng trong excel
Hàm đếm ô trống trong excel
Tình huống sử dụng: Hãy tưởng tượng bạn là một cô giáo và đang tính số lượng các em học sinh nghỉ học trong học kỳ vừa rồi. Với những giá trị là 0, chúng ta mặc định em đó đã đi học đầy đủ, và các em khác có số lượng ngày nghỉ nhiều hơn 0, sẽ được tính là có nghỉ học. Vì thế để thống kế tất cả các em học sinh đã nghỉ học, bạn cần đếm số ô có chứa giá trị là 0 (có thể được mặc định là số ô trống). Trong trường hợp này, bạn có thể sử dụng hàm đặc biệt COUNTBLANK để đếm số ô trống (rỗng) trong vùng chọn đã xác định trong bảng dữ liệu.
Cách thực hiện hàm COUNTBLANK: =COUNTBLANK(range)
Trong đó: Range là vùng dữ liệu muốn đếm ô dữ liệu trống.
Áp dụng vào ví dụ, bạn sẽ sử dụng công thức =COUNTBLANK(E5:E14), trong đó hàng E thể hiện số ngày vắng mặt của từng học sinh, với các dữ liệu 0 được mặc định là vùng trống, bạn có thể điền hoặc không điền. Đáp án thu được là 14, tương ứng với số học sinh đã từng nghỉ học.
Hàm cộng các ô trong excel
Như tên gọi của mình, hàm Sum sẽ dùng để tính tổng các dải ô nằm trong một bảng tính và cho chúng ta kết quả nhanh và chính xác nhất. Cùng điểm qua ví dụ dưới đây để hiểu rõ hơn về hàm tính này.
Ví dụ bạn đang muốn tính doanh thu của cửa hàng trong một ngày, khi bạn đã bán rất nhiều mặt hàng như sữa tươi milo, mì tôm hảo hảo, bimbim. Nếu nhân thủ công từng mặt hàng * số lượng* đơn giá sau đó cộng lại tất cả thì sẽ tốn rất nhiều thời gian. Vì thế chúng ta có thể sử dụng hàm Sum để tính tổng các giá trị sau khi bạn đã nhân tất cả thành tiền ở cột F. Vì thế bạn hoàn toàn có thể dựa vào bảng dữ liệu có sẵn trong bảng, áp dụng công thức hàm SUM để tính tổng giá trị, ở đây chính là = SUM(F6:F13). Vì thế bảng tính sẽ tự cộng tất cả giá tiền của sản phẩm để cho ra con số cuối cùng chỉ trong 1 thao tác.
Nếu bạn nhập công thức tại ô F14 thì kết quả hàm trả về tại ô F14 – được mặc định là doanh thu của cả cửa hàng trong ngày hôm đó.
Hàm tìm giá trị Vlookup
Cú pháp và các tham số trong hàm Vlookup
= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
hoặc =VLOOKUP(giá trị tra cứu, dải ô chứa giá trị tra cứu, số cột trong dải ô chứa giá trị trả về, Kết quả khớp tương đối (TRUE) hoặc Kết quả khớp chính xác (FALSE)).
Nhìn vào ví dụ trên hình, giá trị lookup_value đầu tiên đó chính là Fontana. Ở đây Fontana được hiểu là giá trị bạn muốn tra cứu.
Table_array là dải ô chứa giá trị tra cứu, trong ví dụ minh họa được xác định là các tất cả giá trị nằm trong bảng tính kéo dài từ B2:E7.
Col_index_num là số cột chứa giá trị trả về trong dải ô. Ví dụ: nếu bạn muốn tìm giá trị nằm ở cột thứ mấy tính từ giá trị đầu tiên là cột chứa Fontana thì bạn sẽ nhập số đó. Ở ví dụ minh họa người dùng chọn số 2 nên hàm sẽ trả về giá trị là Olivier, tương tự nếu bạn chọn số 3 thì giá trị trả về sẽ là Phó chủ tịch (bán hàng).
Sau đó là [range_lookup] để bạn chỉ định TRUE hoặc FALSE. Nếu bạn một kết quả khớp tương đối thì chọn TRUE hoặc FALSE nếu bạn muốn có một kết quả khớp chính xác ở giá trị trả về.
Hàm Nếu thì (IF)
Có hai dạng hàm nếu thì, đó chính là Hàm IF và hàm IFS
Hàm IF
Hàm IF cho phép bạn tạo so sánh logic giữa một giá trị cho sẵn với một giá trị dự kiến bằng việc kiểm tra điều kiện, rồi trả về kết quả nếu True hay False. Đây là câu lệnh được sử dụng nhiều nhất nhưng cũng bao dễ sai nhất, vì thế bạn nên sử dụng câu lệnh này cho những điều kiện rõ ràng và đơn giản như Yes/No, Đúng/Sai, Nam/Nữ..
Cách thực hiện
=IF(Biểu thức so sánh, giá trị điều kiện đúng, giá trị điều kiện sai)
=IF(logical_test,value_if_true,value_if_false)
Như vậy chúng ta có thể thấy rằng các đối số trong hàm IF sẽ được nhập thành các cặp giá trị / kết quả. Vì vậy, một câu lệnh IF có thể có hai kết quả trả về TRUE hoặc FALSE.
Lưu ý: hàm if vẫn có thể sử dụng để tính toán các giá trị là văn bản, nhưng khi nhập lệnh bạn phải sử dụng kèm nháy kép “”. Ví dụ trong hình minh họa, khi bạn muốn tìm đối tượng là trứng gà, thì từ này phải được nằm trong dấu nháy kép: “trứng gà”. Tuy nhiên khi bạn sử dụng hai chữ TRUE hoặc FALSE thì không cần để trong dấu nháy kép vì đây là nội dung mà Excel có thể tự động hiểu.
Excel cho phép bạn lồng hàm IF trong đến 64 điều kiện khác nhau, nhưng nếu sử dụng quá nhiều sẽ khiến người dùng gặp nhiều khó khăn khi sử dụng, vì chỉ cần một lần lồng sai thì sẽ rất khó kiểm tra và sửa đổi. Trên thực tế thì việc lồng quá nhiều hàm IF thì khả năng 75 % là công thức có thể hoạt động được nhưng 25 % sẽ trả về kết quả không mong muốn. Rất tiếc, khả năng bạn tìm ra nguyên nhân của 25 % sai là rất nhỏ.
Hàm IFS
Đây là hàm thay thế cho hàm IF cho phép chúng ta sử dụng các phép lồng đơn giản hơn nhiều. Đây là hàm thuộc nhóm Logical và thường được sử dụng trong những phép toán có liên quan đến tính logic. Nếu bạn có tính logic cao thì nên sử dụng hàm này để mang lại kết quả nhanh chóng và đảm bảo tính chính xác cao. So với hàm IF, Hàm này chỉ hoạt động khi điều kiện đầu tiên có kết quả là TRUE, và nếu ngược lại là FALSE thì hàm sẽ trả về lỗi # N/A.
Cú pháp sử dụng hàm IFS:
=IFS( logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3], … )
Trong đó:
Logical_test1 là lệnh để kiểm tra điều kiện đầu tiên. Nó là một đối số bắt buộc để chúng ta kiểm tra xem đối sổ đầu tiên là TRUE hoặc FALSE.
Value1 là kết quả khi logical_test1 là TRUE. Nếu điều kiện này không hợp lệ, hàm sẽ không hoạt động nữa và bạn phải thiết lập lại từ đầu.
Phần còn lại của đối số logical_test và Value sẽ là tùy chọn.
Hàm IFS cho phép bạn kiểm tra tối đa 127 điều kiện khác nhau. Tuy nhiên như đã nói thì chúng ta không nên lồng quá nhiều điều kiện, vì mỗi điều kiện cần được nhập theo đúng thứ tự và có thể rất khó để xây dựng, kiểm tra và cập nhật.
Còn rất nhiều những thủ thuật, phím tắt, hàm lệnh quan trọng khác mà chúng ta sẽ cùng tìm hiểu trong bài viết tiếp theo. Việc sử dụng thành thạo những thao tác này sẽ giúp bạn quản lý và sử dụng bảng tính excel một cách thuận tiện nhất. Đặc biệt thao tác khóa ô trong excel là vô cùng quan trọng mà bạn nên biết khi sử dụng bảng tính của mình. Bạn cũng có thể đọc thêm các bài viết khác về cách chuyển PDF sang doc, excel hoặc những thủ thuật cơ bản cần biết trong word nhé.
