Tin Học Văn Phòng
4 cách tách họ và tên thành cột riêng trong excel chi tiết nhất
Trong quá trình làm việc với excel, họ và tên là những dữ liệu thường hay bắt gặp nhất. Nếu bạn muốn tách họ và tên trong excel thành 2 cột khác nhau thì bạn cần phải học mẹo, chứ bạn không thể nào ngồi đánh từng tên từng họ được vì thao tác đó sẽ tốn rất là nhiều thời gian. Sau đây, Unica sẽ hướng dẫn cách tách cột họ và tên trong excel đơn giản và dễ dàng nhất.
Lợi ích khi tách họ và tên trong Excel?
Bạn nên thực hiện cách tách tên và họ trong excel là vì:
Tăng tính khả dụng: Tách họ và tên thành hai cột riêng biệt sẽ giúp bạn quan sát và sử dụng dữ liệu được dễ dàng hơn. Khi tách thành 2 cột bạn dễ dàng lọc và sắp xếp dữ liệu.
Tăng tính chính xác: Tách họ và tên thành hai cột riêng biệt sẽ giúp giảm thiểu lỗi trong quá trình nhập dữ liệu vào trong bảng tính.
Tăng tính linh hoạt: Tách họ và tên trong excel thành hai cột riêng biệt sẽ giúp bạn linh hoạt hơn trong việc sử dụng dữ liệu. Ví dụ, bạn có thể sử dụng cột họ để tạo danh sách khách hàng hoặc sử dụng cột tên để tạo danh sách nhân viên.
Tách họ và tên giúp bạn quan sát và sử dụng dữ liệu dễ dàng hơn
Cách tách họ và tên trong excel
Sau khi đã hiểu được lý do tại sao phải tách họ và tên trong excel, phần tiếp theo của bài viết chúng tôi sẽ hướng dẫn bạn cách tách tên họ trong excel dễ dàng và nhanh chóng nhất. Cụ thể cách tách tên họ trong excel như sau:
Cách tách họ và tên bằng Find and replace
Tách họ và tên bằng Find and replace (Tìm kiếm và thay thế)
Hướng dẫn cách tách tên
Bước 1: Bạn có một bảng họ và tên, để tách riêng ra thành 2 cột bạn sẽ copy và dán cột họ và tên sang cột tên.
Copy cột họ và tên sang cột tên
Bước 2: Tiến hành bôi đen toàn bộ phần họ và tên, sau đó nhấn tổ hợp Ctrl + H để mở hộp thoại Find and Replace.
Mở hộp thoại Find and Replace
Bước 3: Trong hộp thoại Find and Replace, ở mục Find what bạn nhập * (chuỗi ký tự gồm dấu sao và phím cách) để tách tên. Sau đó chọn Replace All.
Nhấn dấu * và phím cách
Bước 4: Như vậy, bạn đã hoàn tất cách tách tên ra khỏi họ một cách đơn giản nhất.
Nhấn chọn Replace all
Hướng dẫn cách tách họ
Bước 1: Để tách lấy dữ liệu cho cột họ, bạn thực hiện tương tự copy dữ họ và tên vào cột họ. Sau đó, nhấn tổ hợp phím Ctrl + H để mở cửa sổ Find and Replace.
Hướng dẫn tách họ bằng tính năng Find and Replace
Bước 2: Ở mục Find What, bạn nhập *(chuỗi ký tự bao gồm dấu cách và ký tự *) > sau đó nhấn Replace All.
Sau khi thực hiện các bước, kết quả thu về được là
Sau đó, kết quả khi được trả về đã được tách từ họ và tên của cột A.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Tách tên họ và tên bằng cách sử dụng kết hợp nhiều hàm Excel
Quan sát ví dụ sau để biết cách tách tên đệm trong excel:
Bước 1: Trong ô C2, bạn nhập công thức hàm RIGHT, LEN và hàm FIND trong Excel như sau:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
Trong đó:
Hàm RIGHT: dùng để cắt chuỗi từ phải qua.
Hàm Len: dùng để lấy độ dài của chuỗi.
Hàm SUBSTITUTE: dùng để thay thế văn bản cụ thể trong chuỗi văn bản.
Nhập hàm
Bước 2: Kéo chuột đến hết ô A11 để hiển thị hết kết quả.
Kết quả cột tên
Bước 3: Để lấy họ tên đệm, tại ô B2, bạn nhập hàm LEFT trong excel:
=LEFT(A2,LEN(A2)-LEN(C2))
Trong đó:
Hàm LEFT: dùng để cắt chuỗi từ trái qua.
Hàm LEN: dùng để lấy độ dài của chuỗi.
Nhập hàm
Bước 4: Tiếp tục dùng Fill Handle để sao chép công thức xuống các ô phía dưới.
Kết quả tách họ và tên bằng hàm
Tách họ và tên trong Excel bằng chức năng Text to Columns
Bước 1: Dùng chuột tô đen các ô chứa họ và . Trên thanh công cụ chọn Data => Chọn Text to Columns.
Thao tác tách họ và tên
Bước 2: Hộp thoại Convert Text to Columns Wizard sẽ hiện ra. Bạn chọn Delimited => Chọn Next.
Chọn Next
Bước 3: Trong phần Delimiters, bạn chọn Space và nhấn Next.
Chọn Space và nhấn next.
Bước 4: Trong hộp thoại Convert Text to Columns Wizard - Step 1 or 3 bạn chọn Text và tại phần Destination, bạn chọn ô sẽ chứa dữ liệu sau khi tách. Tại ví dụ này mình chọn ô B2 và cuối cùng nhấn Finish.
Chọn ô chứa dữ liệu sau khi tách
Kết quả sau khi thực hiện:
Kết quả cuối nhận được
Cách tách họ và tên trong Excel bằng tính năng Flash Fill
Excel có rất nhiều công cụ mạnh mẽ giúp bạn xử lý dữ liệu nhanh chóng và đơn giản nhất. Ngoài những cách tách cột họ tên trong excel như bên trên đã chia sẻ, bạn cũng có thể sử dụng công cụ Flash Fill. Đối với công cụ này cách thực hiện như sau:
Ví dụ: Cho một bảng dữ liệu với yêu cầu là tách thông tin đúng theo từng cột. Cách thực hiện như sau:
Bảng thông tin dữ liệu
Bước 1: Đầu tiên bạn gõ thủ công cho người tên dầu tiên như hình dưới đây. Sau đó, bổ sung các cột mới đặt bên cạnh cột Họ và Tên rồi lần lượt nhập mẫu ở hàng đầu tiên để làm mẫu.
Gõ thủ công người đầu tiên
Bước 2: Bạn tiến hành chọn vùng C3:C12. Đặt con trỏ vào từng cột, trên thanh công cụ mở mục Data > chọn Flas Fill hoặc nhấn tổ hợp phím tắt Ctrl + E. Việc này giúp Excel tự động nhận diện và điền thông tin theo mẫu sẵn.
Kết quả cuối cùng nhận được
Những lưu ý khi thực hiện cách tách tên trong Excel
Nhìn chung, thao tác thực hiện cách tách họ tên trong excel không quá khó khăn và phức tạp. Tuy nhiên để mang lại tỷ lệ thành công cao nhất ngay từ lần đầu tiên thực hiện bạn hãy đặc biệt chú ý một số vấn đề sau đây nhé:
Nhập đúng và đầy đủ hàm: Đây là yêu cầu bắt buộc để tránh sai sót. Khi bạn nhập đầy đủ hàm và đúng ký tự thì kết quả sẽ đảm bảo trả về đúng, hạn chế được mọi sai sót.
Sao chép công thức hàm nhanh cho các ô sau bằng tính năng Fill Handle: Để sao chép hàm cho các ô còn lại nhanh nhất bạn nên sử dụng tính năng Fill Handle. Cách sử dụng tính năng vô cùng đơn giản, bạn chỉ cần đặt con trỏ chuột ở góc dưới cùng của ô có kết quả từ hàm công thức, sau đó đợi đến khi xuất hiện hình dấu “+” thì giữ chuột và kéo xuống hết những ô còn lại là được.
Tổng kết
Trên đây là hướng dẫn chi tiết 4 cách tách họ và tên trong excel. Hy vọng rằng với chia sẻ hướng dẫn và cách lệnh tách tên trong excel trên đây bạn đã thực hiện thao tác này nhanh chóng nhất. Hiện tại Unica đang cho ra mắt cuốn sách Excel đang được rất nhiều quan tâm mời bạn cùng theo dõi và đặt hàng. Tham khảo cuốn sách này chắc chắn bạn sẽ tích luỹ thêm được cho mình rất nhiều kiến thức tin học văn phòng bổ ích.
Cảm ơn và chúc các bạn thành công!
>>> Xem thêm: Hàm tách số ra khỏi chuỗi trong excel cực dễ chỉ 5 giây
>>> Xem thêm: 4 cách gộp họ và tên trong excel siêu nhanh mà bạn nên thử
16/02/2019
14669 Lượt xem
Cách kết hợp hàm sumif và hàm vlookup chi tiết nhất
Kết hợp hàm sumif và hàm vlookup tạo nên công thức tính đơn giản, giải quyết được các yêu cầu phức tạp của bảng tính excel. Sau đây, Unica sẽ cung cấp cho bạn các thông tin liên quan đến hàm SUMIF và hàm VLOOKUP, đồng thời hướng dẫn bạn cách kết hợp SUMIF và VLOOKUP trong excel, cùng khám phá nhé.
Giới thiệu hàm SUMIF, VLOOKUP
Hàm SUMIF, VLOOKUP là 2 hàm cơ bản được sử dụng trong nhiều bảng tính excel. Tuy nhiên, nếu không sử dụng thường xuyên bạn sẽ rất dễ bị quên công thức.
Hàm SUMIF
Công thức hàm SUMIF:
=SUMIF(range;condition;sum_range)
Trong đó:
Range: Phạm vi vùng dữ liệu chúng ta sẽ xét điều kiện.
Condition: Điều kiện để xác định các ô sẽ lấy giá trị.
Sum_range: Dải ô tính tổng có dữ liệu phù hợp với điều kiện yêu cầu.
Hàm SUMIF trong Excel
Ví dụ minh họa:
Ví dụ hàm SUMIF trong excel
Yêu cầu: Hãy tính tổng giá bán của hãng Oppo
Cách thực hiện:
Để xác định được tổng giá bán sản phẩm của hãng Oppo chúng ta sử dụng công thức sau:
=SUMIF(C3:C11;C14;D3:D11)
Trong đó:
C3:C11: là vùng dữ liệu để xác định yêu cầu “Hãng - Oppo”
C14: là điều kiện để xác định các ô sẽ lấy dữ liệu “Oppo”
D3:D11: là dải ô tính tổng giá bán có chứa giá bán hãng Oppo (điều kiện yêu cầu).
Khi áp dụng vào bảng tính chúng ta có kết quả sau đây:
Ví dụ minh hoạ công thức hàm SUMIF
Hàm VLOOKUP
Hàm VLOOKUP là hàm dùng để tra cứu giá trị theo cột. Chức năng của hàm VLOOKUP là sẽ gò tìm giá trị theo chiều dọc và dò tìm theo thứ tự từ trái sang phải. Nếu giá trị tra cứu được tìm thấy thì kết quả trả về giá trị tương ứng từ một cột khác. Nếu giá trị tra cứu không được tìm thấy thì kết quả trả về sẽ báo lỗi #N/A.
Công thức hàm VLOOKUP:
=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])
Trong đó:
Lookup_value: Là giá trị dùng để tra cứu.
Table_array: Là bảng dữ liệu chứa giá trị muốn dò tìm.
Col_index_num: Số thứ tự của cột có chứa giá trị bạn muốn dò tìm tính từ trái qua phải.
Range_lookup: là giá trị dò tìm tương đối nếu bạn chọn 0 và là giá trị dò tìm tuyệt đối nếu bạn chọn 1.
Ví dụ minh họa:
Bảng dữ liệu quản lý sản phẩm
Yêu cầu: Hãy dò tìm giá bán theo mã sản phẩm 48042
Cách thực hiện:
Áp dụng công thức =VLOOKUP(C14;A2:D11;4;0)
Trong đó:
C14: là ô chứa giá trị cần tra cứu
A2:D11: là bảng dữ liệu cần tra cứu
4: vị trí cột giá bán ở vị trí cột thứ 4 tính từ trái qua phải
0: Giá trị dò tìm là tương đối
Khi áp dụng công thức vào bảng tính chúng ta có kết quả như sau:
Nhập công thức hàm VLOOKUP
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Cách kết hợp hàm sumif và vlookup
Để giúp bạn nắm bắt nhanh cách kết hợp hàm SUMIF và hàm VLOOKUP, chúng tôi sẽ giải thích công thức sử dụng căn cứ theo ví dụ minh họa cụ thể dưới đây.
Căn cứ theo bảng tính:
Ví dụ minh hoạ cách dùng hàm SUMIF và VLOOKUP
Yêu cầu: TÌm doanh thu tương ứng với họ và tên
Cách thực hiện:
Trong trường hợp này nếu chúng ta chỉ sử dụng hàm SUMIF thì kết quả doanh số theo tên nhân viên sẽ không chính xác. Do đó, chúng ta sẽ sử dụng kết hợp giữa hàm SUMIF và hàm VLOOKUP theo công thức cụ thể sau đây:
=SUMIF(D:D,VLOOKUP(A8,A2:B5,2,FALSE),E:E)
Trong đó:
D:D: là cột chứa giá trị dò tìm.
A8: là ô chứa giá trị đối chiếu theo yêu cầu (họ và tên).
A2:B5: là bảng dữ liệu tham chiếu
2: là thứ tự của cột lấy giá trị tham chiếu
False: dùng để xác định phạm vi tìm kiếm tuyệt đối
E:E: là cột chứa dải số có dữ liệu phù hợp với điều kiện yêu cầu (Doanh thu).
Khi áp dụng vào bảng tính chúng ta có kết quả như sau:
Kết quả nhận được cuối cùng
Các lỗi thường gặp khi kết hợp hàm SUMIF và hàm VLOOKUP
Khi kết hợp hàm VLOOKUP và SUMIF chúng ta sẽ gặp 3 lỗi phổ biến đó là: Lỗi #N/A, lỗi trả về 0, lỗi #ERROR. Cụ thể những lỗi này có nguyên nhân và cách khắc phục như sau:
Lỗi #N/A
Nguyên nhân: Lỗi na trong excel là do nhập liệu thiếu đối số hoặc đối số sum_range trong công thức SUMIF không phải dải số.
Cách khắc phục: Bạn hãy kiểm tra lại các đối số xem đã nhập đúng và đủ hay chưa.
Dưới đây là ví dụ minh họa
Ví dụ lỗi #N/A
Lỗi trả về 0
Nguyên nhân: Do tổng kết quả các đối số là 0 hoặc do các đối số khai báo sai kiểu dữ liệu.
Cách khắc phục: Bạn hãy kiểm tra các đối số xem có bị lỗi nhập liệu hay không.
Dưới đây là ví dụ minh họa
Ví dụ lỗi trả về 0
Lỗi #ERROR
Nguyên nhân: Do nhập sai cú pháp hàm.
Cách khắc phục: Bạn hãy kiểm tra lỗi chính tả tên hàm và vị trí sắp xếp các thành phần trong công thức xem đã nhập đúng hay chưa. Khi bạn nhấp chuột vào công thức tính, excel sẽ hiển thị gợi ý công thức, bạn hãy căn cứ theo công thức gợi ý này để so sánh, đối chiếu và chỉnh sửa lại công thức chính xác hơn.
Dưới đây là ví dụ minh họa
Ví dụ lỗi #ERROR
Một số lưu ý khi kết hợp hàm SUMIF với VLOOKUP
Khi kết hợp hàm VLOOKUP và SUMIF bạn cần lưu ý những điểm sau:
Trong công thức hàm VLOOKUP và hàm SUMIF đều không phân biệt chữ hoa và chữ thường. Do đó, bạn cứ nhập tên hàm bình thường theo font chữ hiện dùng.
Hàm VLOOKUP có 2 định dạng tìm kiếm là tuyệt đối và tương đối, bạn nên chú ý điểm này khi sử dụng kết hợp với hàm SUMIF.
Lưu ý khi kết hợp hàm SUMIF và hàm VLOOKUP
Hàm VLOOKUP sẽ mặc định dò tìm dữ liệu từ trái qua phải. Do đó, lúc xác định vị trí cột sẽ lấy dữ liệu bạn hãy đếm từ trái qua phải.
Đối số Range và sum_Range trong hàm SUMIF phải có cùng kích thước và ngang nhau. Điều này sẽ giúp bạn không bị thiếu dữ liệu dẫn đến kết quả không chính xác
Hàm SUMIF kết hợp hàm VLOOKUP chỉ có thể tính toán theo yêu cầu 1 điều kiện. Nếu yêu cầu có nhiều điều kiện đi kèm bạn hãy sử dụng hàm SUMIF kết hợp với hàm VLOOKUP.
Câu hỏi thường gặp
Câu 1: Hàm VLOOKUP có thể kết hợp được với hàm nào nữa không?
Bên cạnh việc kết hợp với hàm SUMIF thì hàm VLOOKUP còn có thể kết hợp được với một số hàm khác để giải quyết các yêu cầu phức tạp hơn. Điển hình như hàm LEFT, hàm RIGHT, hàm IF, hàm COUNTIF, hàm SUM, hàm INDEX, hàm MATCH, hàm QUERY,...
Câu 2: Hàm SUMIF có thể kết hợp được với hàm nào nữa không?
Bên cạnh việc kết hợp với hàm VLOOKUP thì hàm SUMIF còn có thể kết hợp với nhiều hàm khác như hàm TODAY, LEFT, RIGHT, INDEX,...
Kết luận
Hy vọng với những chia sẻ cụ thể về hàm SUMIF, VLOOKUP có kèm ví dụ minh họa cụ thể đã giúp bạn có thể ứng dụng cách kết hợp hàm SUMIF và hàm VLOOKUP vào bảng tính. Nếu bạn muốn biết thêm các mẹo hay excel hãy thường xuyên theo dõi chúng tôi.
Ngoài ra, để nâng cao trình độ và kỹ năng excel, kỹ năng sử dụng công thức excel mở rộng hãy tham gia khóa học liên quan đến khóa học Excel trực tuyến tại Unica nhé.
16/02/2019
13772 Lượt xem
.png?v=1726827367)
Cách sử dụng hàm SUMIFS trong Excel tính tổng nhiều điều kiện có ví dụ
SUMIFS là một hàm dùng phổ biến và rất hữu ích trong Excel. Chúng cho phép bạn tính tổng các giá trị thỏa mãn một hoặc nhiều điều kiện khác nhau một cách hiệu quả. Sau đây, Unica sẽ chia sẻ Cách sử dụng hàm SUMIFS trong Excel tính tổng nhiều điều kiện chi tiết kèm ví dụ minh họa. Cùng tìm hiểu ngay.
Hàm SUMIFS là gì? Ứng dụng của hàm SUMIFS trong Excel
Hàm SUMIFS là một hàm toán học và thống kê trong Excel, có tác dụng tính tổng các giá trị trong một vùng dữ liệu (hay còn gọi là mảng) thỏa mãn một hoặc nhiều điều kiện xác định. Hàm sumif trong excel có cùng chức năng nhưng chỉ cho phép bạn nhập một điều kiện duy nhất.
Cách sử dụng hàm sumifs trong excel
Hàm SUMIFS trong Excel được ứng dụng để:
Nhanh chóng tính toán tổng dãy số theo các điều kiện cần thiết.
Tăng sự thuận tiện trong công việc bằng việc kết hợp nhiều dạng hàm trong Excel.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Hướng dẫn sử dụng hàm sumifs trong Excel
Công thức hàm SUMIFS trong Excel
Công thức của hàm SUMIFS trong Excel được viết như sau
=SUMIFS(sum_range, criteria_range1,criteria1, [criteria_range2, criteria2],...)
Trong đó:
Sum_range (bắt buộc): Xác định phạm vi ô cần tính tổng
Criteria_range1 (bắt buộc): Xác định phạm vi được kiểm tra bằng cách sử dụng Criteria1.
Criteria1 (bắt buộc): Tiêu chí xác định ô nào trong Criteria_range1 sẽ được cộng vào.
Criteria_range2, criteria2 (tùy chọn): Các phạm vi bổ sung và các tiêu chí liên kết chúng. Ở đây có thể nhập tối đa 127 cặp phạm vi/tiêu chí.
Lưu ý: Criteria_range1 và Criteria1 là một cặp tìm kiếm, nghĩa là phạm vi được tìm kiếm theo các tiêu chí cụ thể. Giá trị được tìm thấy trong phạm vi tương ứng trong Sum_range sẽ được cộng vào.
>>> Xem thêm: Cách dùng hàm SUM trong Excel để tính tổng cho người mới bắt đầu
Cách sử dụng hàm SUMIFS trong Excel
Để bạn hiểu rõ hơn cách sử dụng hàm SUMIFS trong Excel, Unica sẽ hướng dẫn bạn cách dùng hàm thông qua 3 ví dụ dưới đây:
Ví dụ 1: Ứng dụng hàm SUMIFS để tính tổng tiền các nhân viên ở bộ phận kỹ thuật có lương nhiều hơn 3.000.000 đồng qua bảng sau.
Lưu ý:
Để tính giá trị lớn hơn bạn sử dụng dấu >, còn tính giá trị nhỏ hơn bạn sử dụng dấu <.
Trong trường hợp bạn muốn tính giá trị bằng, bạn cần nhập thêm giá trị điều kiện.
Bước 1: Mở file Excel có chứa dữ liệu bảng > nhập hàm =SUMIFS(D2:D8,C2:C8,”Kỹ thuật”,D2:D8,”>3000000”) vào ô muốn hiển thị kết quả.
Trong đó:
Sumifs: Lệnh đặt hàm tính tổng điều kiện.
D2:D8: Xác định khoảng vùng tham chiếu dữ liệu.
C2:C8: Vùng tham chiếu dữ liệu cho điều kiện 1.
“Kỹ thuật”: Điều kiện 1 muốn lấy ở ô tham chiếu.
D2:D8: Vùng tham chiếu dữ liệu cho điều kiện 2.
“>3000000”: Điều kiện 2 lấy trong ô tham chiếu.
Ví dụ 1 về cách sử dụng hàm Sumiffs trong Excel
Bước 2: Sau đó, bạn ấn Enter để Excel tự động trả về kết quả.
Kết quả ví dụ 1 về cách sử dụng hàm Sumiffs trong Excel
Ví dụ 2. Ứng dụng hàm SUMIFS tính tổng lương cho nhân viên nữa bán được hơn 100 sản phẩm.
Bước 1: Mở file Excel có chứa dữ liệu bảng > nhập hàm =SUMIFS(F2:F8,C2:C8,”Nữ”,E2:E8,”>100”) vào ô muốn hiển thị kết quả.
Trong đó:
Sumifs: Lệnh đặt hàm tính tổng điều kiện.
F2:F8: Xác định khoảng vùng tham chiếu dữ liệu.
C2:C8: Vùng tham chiếu dữ liệu cho điều kiện 1.
“Nữ”: Điều kiện 1 muốn lấy ở ô tham chiếu.
E2:E8: Vùng tham chiếu dữ liệu cho điều kiện 2.
“>100”: Điều kiện 2 lấy trong ô tham chiếu.
Ví dụ 2 về cách sử dụng hàm Sumiffs trong Excel
Bước 2: Sau đó, bạn ấn Enter để Excel tự động trả về kết quả.
Kết quả ví dụ 2 về cách sử dụng hàm Sumiffs trong Excel
Ví dụ 3. Ứng dụng hàm SUMIFS để tính tổng giá tiền của các loại trái cây trừ trái cam.
Bước 1: Mở file Excel có chứa dữ liệu bảng > nhập hàm =SUMIFS(D2:D7,C2:C7,”>70”,B2:B7,”<>Cam”) vào ô muốn hiển thị kết quả.
Trong đó:
Sumifs: Lệnh đặt hàm tính tổng điều kiện.
D2:D7: Xác định khoảng vùng tham chiếu dữ liệu.
C2:C7: Vùng tham chiếu dữ liệu cho điều kiện 1.
“>70”: Điều kiện 1 muốn lấy ở ô tham chiếu.
B2:B7: Vùng tham chiếu dữ liệu cho điều kiện 2.
“<>Cam”: Điều kiện 2 lấy trong ô tham chiếu.
Ví dụ 3 về cách sử dụng hàm Sumiffs trong Excel
Bước 2: Sau đó, bạn ấn Enter để Excel tự động trả về kết quả.
Kết quả ví dụ 3 về cách sử dụng hàm Sumiffs trong Excel
>>> Xem thêm: Cách kết hợp hàm sumif và hàm vlookup chi tiết nhất, kèm ví dụ
Những cách thực hành hàm SUMIFS tốt nhất
Dùng ký tự đại diện
Trong Criteria 1, 2 bạn có thể sử dụng ký tự đại diện như dấu hỏi chấm (?) và dấu sao (*) để tìm thấy kết quả tương tự nhưng không chính xác.
Dấu hỏi chấm (?) khớp với ký tự đơn lẻ bất kỳ.
Dấu sao (*) khớp với chuỗi ký tự bất kỳ.
Nếu bạn muốn tìm dấu sao hoặc dấu hỏi chấm, bạn cần thêm ký tự ~ (sóng ngã) trước dấu.
Ví dụ: Dễ thấy trong bảng trên có tên bắt đầu bằng “To” và kết thúc bằng một chữ cái có thể thay đổi. Do đó, ám dụng cách trên kết quả hiển thị sẽ là Tom.
Ví dụ dùng ký tự đại diện trong Excel
Sự khác biệt giữa SUMIFS và SUMIF
Sự khác biệt giữa SUMIFS và SUMIF nằm ở thứ tự của các đối số:
Đối số sum_range là đối số thứ nhất trong SUMIFS.
Đối sối sum_range là đối số thứ ba trong hàm SUMIF.
Đây chính là nguyên nhân phổ biến nhất gây ra các vấn đề khi sử dụng hàm nay. Nếu có người sao chép và sửa những hàm giống nhau này, bạn cần kiểm tra và đặt lại các đối số theo đúng thứ tự.
Sự khác biệt giữa SUMIFS và SUMIF
Sử dụng cùng một số hàng và cột cho đối số phạm vi
Đối số Criteria_range trong Excel phải chứa số hàng và số cột bằng với số cột bằng với đối số Sum_range.
Như ở ví dụ 3, ta thấy đối số criteria_range là D2:D7 có số hàng và số cột bằng với đối số Dum_range C2:C7.
Sử dụng cùng một số hàng và cột cho đối số phạm vi
Lưu ý khi dùng hàm SUMIFS
Mỗi ô trong vùng phạm vi sum_range đã được chọn sẽ được cộng nếu thỏa mãn được tất cả các điều kiện đã được xác định chính xác cho ô đó.
Các ô trong phạm vi sum_range nếu là TRUE sẽ được coi là 1 và nếu là FALSE sẽ được coi là 0.
Criteria_range cần đảm bảo có cùng kích thước vùng, cùng số hàng, số cột tương tự như sun_range.
Trong Criteria, bạn thay thế ký tự đơn bằng dấu hỏi chấm và dấu sao để thay cho chuỗi ký tự. Bạn cần thêm dấu ngã ~ ở trước khi sử dụng dấu hỏi chấm, dấu sao. Giá trị điều kiện ở dạng văn bản sẽ được để trong dấu “.
Nếu áp dụng nhiều điều kiện cùng một lúc, hàm SUMIFS có thể kết hợp với hàm AND.
Nếu thấy báo lỗi #VALUE nghĩa là phạm vi mà bạn cung cấp không cùng với số lượng cột, hàng giống với tổng giải ô.
Các ô phạm vi không bắt buộc phải liền kề với nhau.
Các văn bản trong điều kiện phải được đặt trong dấu ngoặc kép “. Chẳng hạn như “Cam”, “Quýt”, “>700”.
Tham chiếu trong ô điều kiện sẽ không được đặt trong dấu ngoặc kém.
Hàm SUMIFS và SUMIF đều có khả năng xử lý nhiều phạm vi nhưng không có khả năng xử lý một mảng. Nghĩa là trong phạm vi tiêu chí, bạn không thể sử dụng các hàm YEAR bởi kết quả sẽ trả về một mạng. Nếu cần hãy sử dụng hàm Sumproduct.
Hàm SUMIFS có thứ tự đối số phạm vi tổng ở đầu tiên, còn SUMIF lại có thứ tự đối số ở thứ ba.
Video cách sử dụng của hàm SUMIFS trong Excel
Hướng dẫn cách sử dụng hàm SUMIFS trong Excel
Kết luận
Như vậy, Bài viết trên đã giới thiệu cho bạn về cách sử dụng hàm SUMIFS trong Excel với các ví dụ minh họa, cũng như sự kết hợp của hàm SUMIFS với các hàm khác. Bài viết cũng đã đề cập đến lưu ý và hạn chế của hàm SUMIFS. Hy vọng, Bài viết này giúp bạn áp dụng hàm SUMIFS hiệu quả trong công việc và học tập của bạn. Cảm ơn bạn đã theo dõi.
16/02/2019
15183 Lượt xem
Cách sử dụng conditional formatting trong excel để tô màu, đánh dấu ô
Trong quá trình làm việc với excel, nếu như dữ liệu của tập tin quá lớn thì sẽ gây khó khăn cho người xem. Để khắc phục vấn đề này, bạn có thể sử dụng công cụ Conditional formatting trong Excel để định dạng dữ liệu ô. Conditional formatting excel là định dạng có điều kiện cho phép bạn áp dụng định dạng đặc biệt cho các ô trong bảng tính đáp ứng các tiêu chí nhất định. Bài viết dưới đây hướng dẫn cách sử dụng conditional formatting cho người dùng cơ bản và nâng cao, hãy cùng theo dõi nhé.
Conditional formatting là gì?
Conditional formatting trong Excel hay định dạng có điều kiện trong excel một tính năng cho phép bạn áp dụng định dạng cụ thể cho các ô trong bảng tính đáp ứng các tiêu chí nhất định. Các tiêu chí này có thể bao gồm: giá trị ô, màu ô, kiểu chữ, v.v. Định dạng có điều kiện có thể được sử dụng để áp dụng cho nhiều cột, ô, hàng hoặc toàn bộ trang tính tuỳ vào nhu cầu sử dụng của mỗi người.
Conditional formatting trong Excel
Ưu điểm khi sử dụng Excel conditional formatting đó là: Nhấn mạnh các giá trị cao hoặc thấp trong một phạm vi ô, người dùng dễ dàng tìm thấy các giá trị trùng lặp trong một phạm vi ô., thuận tiện theo dõi các thay đổi trong dữ liệu của bạn.
Nhìn chung, áp dụng định dạng có điều kiện là một cách dễ dàng và đơn giản để giúp cho bạn và các thành viên trong nhóm cập nhật dữ liệu - kêu gọi sự chú ý trực quan đến các thời gian quan trọng, nhiệm vụ và bài tập,... Điều này giúp bạn làm việc hiệu quả hơn, hỗ trợ làm giảm thời gian sử dụng dữ liệu thủ công và giúp xác định xu hướng dễ dàng hơn.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Tìm hiểu chi tiết các định dạng theo điều kiện có sẵn
Conditional formatting được sử dụng phổ biến với quy tắc nổi bật áp dụng định dạng màu cho các ô đáp ứng các tiêu chí cụ thể đã xác định. Chúng là loại quy tắc định dạng có điều kiện cơ bản nhất và cung cấp nhiều hàm tô màu trong Excel theo điều kiện.
Highlight Cells Rules
Đây là cách định dạng ô của bạn theo giá trị trong ô với giá trị thỏa mãn điều kiện có sẵn hoặc do bạn tạo ra.
Một số chức năng như:
Greater Than: Định dạng ô có giá trị lớn hơn giá trị bạn chọn.
Less Than: Định dạng ô có giá trị nhỏ hơn giá trị bạn chọn.
Between: Định dạng các giá trị nằm trong khoảng bạn chọn.
Equal To: Định dạng ô chứa giá trị bạn chọn.
Text That Contains: Ô chứa chữ bạn chọn sẽ được định dạng.
A Date Occurring: Định dạng ô có chứa thời gian bạn thiết lập.
Duplicate Values: Định dạng các giá trị trùng lặp.
Ví dụ: Đánh dấu ô có giá trị lớn hơn 8.5 tại cột Toán.
Bước 1: Quét chọn giá trị trong cột Toán.
Chọn giá trị trong cột toán
Bước 2: Chọn Conditional Formatting > Chọn Highlight Cells Rules > Chọn Greater Than.
Chọn Greater Than
Bước 3: Nhập 8.5 vào ô => Nhấn OK.
Nhập 8.5
Top / Bottom Rules
Là định dạng các giá trị trong ô theo xếp hạng của một vùng dữ liệu mà bạn chọn.
Các ô được định dạng theo các chức năng như:
Top 10 Items: Ô có 10 giá trị lớn nhất.
Top 10%: 10% ô chứa giá trị lớn nhất.
Bottom 10 Items: Ô có 10 giá trị nhỏ nhất.
Bottom 10%: 10% ô chứa giá trị nhỏ nhất.
Above Average: Ô có giá trị lớn hơn giá trị trung bình của vùng dữ liệu bạn chọn.
Below Average: Ô có giá trị nhỏ hơn giá trị trung bình của vùng dữ liệu bạn chọn.
Ví dụ: Định dạng 2 giá trị lớn nhất tại cột Văn.
Bước 1: Quét chọn các giá trị trong cột Văn.
Chọn giá trị trong cột văn
Bước 2: Chọn Conditional Formatting => Chọn Top/Bottom Rules => Chọn Top 10 Items.
Chọn Top 10 Items
Bước 3: Giảm giá trị xuống 2 => Chọn OK
Chọn OK
Data Bar
Có chức năng hiển thị mức độ lớn nhỉ của giá trị trong một vùng dữ liệu mà bạn chọn
Ví dụ: Sử dụng Data Bar để định dạng cột Anh.
Bước 1: Quét chọn giá trị trong cột Anh.
Chọn giá trị trong cột Anh
Bước 2: Chọn Conditional Formatting => Chọn Data Bars => Chọn một bảng màu tại Gradient Fill.
Chọn một bảng màu tại Gradient Fill
Color Scales
Có chức năng sử dụng màu sắc để phân biệt cacsc ô của một vùng dữ liệu nào đó dựa vào giá trị lớn nhỏ trong ôn đó.
Ví dụ: Phân biệt giá trị lớn nhỏ tại cột Lý.
Bước 1: Quét chọn giá trị tại cột Lý.
Chọn vùng giá trị trong cột Lý
Bước 2: Chọn Conditional Formatting > Chọn Color Scales > Chọn bảng màu.
Chức năng Color Scales
Xem thêm: Bộ khóa học tin học văn phòng giúp tăng hiệu suất công việc 100%
Icon Sets
Dùng các biểu tượng để phân biệt các giá trị trong ô của một vùng dữ liệu.
Ví dụ: Sử dụng chức năng Icon Sets tại cộng Hóa.
Bước 1: Quét chọn giá trị tại cột Hóa.
Chọn vùng giá trị trong cột Hoá
Bước 2: Chọn Conditional Formatting > Chọn Icon Sets > Chọn 1 nhóm biểu tượng.
Chức năng Icon Sets
Cách sử dụng Conditional Formatting trong Excel
Sau khi đã biết rõ về khái niệm hàm Conditional Formatting trong excel. Tiếp theo, để biết cách sử dụng Conditional Formatting có điều kiện trong excel như thế nào? Ngay sau đây chúng tôi sẽ hướng dẫn chi tiết cho bạn từng bước thực hiện, hãy tham khảo nhé.
Bước 1: Chọn vùng giá trị
Việc đầu tiên bạn cần làm khi sử dụng Conditional Formatting trong excel đó chính là chọn vùng giá trị
Thao tác chọn vùng giá trị
Bước 2: Mở New Formatting Rule để thêm định dạng có điều kiện
Sau khi đã chọn vùng giá trị xong, tiếp theo từ thanh menu bạn chọn thẻ Home => Chọn Conditional Formatting => Chọn New Rule.
Chọn New Rule
Khi này hộp thoại New Formatting Rule hiện ra, bạn thêm định dạng có điều kiện.
Hộp thoại New Formatting Rule hiện ra
Bước 3: Cài đặt điều kiện định dạng
Tại hộp thoại New Formatting Rule, bạn chọn kiểu quy tắc mình đang muốn dùng. Ở trong bài viết này, Unica chọn quy tắc Format only cells that contain để định dạng ô theo các điều kiện bên dưới.
Chọn Format only cells that contain để định dạng ô theo điều kiện
Tại phần Format only cells with ở phía dưới bạn lần lượt chọn các giá trị bao gồm: Cell Value, Greater than và điền giá trị 250. Điều kiện này có ý nghĩa là giá trị của các ô sẽ >250.
Lần lượt điền các giá trị tương ứng
Bước 4: Cài đặt Format Cells cho ô tính thỏa mãn điều kiện
Thực hiện bước trên xong, tiếp theo ở phần Preview phía dưới, bạn chọn Format sẽ xuất hiện hộp thoại Format Cells hiện ra.
Hộp thoại Format Cellls xuất hiện
Tại bảng Format Cells, bạn chọn vào Fill => Chọn More Colors sau đó chọn màu xanh lá rồi nhấn OK để xác nhận.
Nhấn More Color để chọn màu xanh lá
Bước 5: Xác nhận kết quả
Cuối cùng bạn quay trở lại bảng New Formatting Rule ban đầu, kiểm tra lại rồi OK là xác nhận.
Bấm OK để xác nhận
Khi này ta được kết quả định dạng như hình.
Kết quả định dạng như hình
Ví dụ về định dạng có điều kiện trong excel Conditional formatting?
Mở một bảng tính trong Excel hoặc nếu chưa có hãy lập một bảng tính mới, ví dụ dưới đây là cho ở bảng và tìm các tháng có doanh số bán < 100, 100 - 300 và > 300.
Ví dụ tô màu có điều kiện trong Excel
Tìm giá trị các ô < 100
Thực hiện các thao tác từ bước chọn vùng các giá trị của dữ liệu và làm theo các bước như trong hình bên dưới:
Chọn vùng các giá trị
Trong phần Format cells that are LESS THAN của ô cửa sổ Less Than nhập giá trị 100 và cùng dòng là với nơi chọn màu --> OK.
Nhập giá trị 100
Khi OK cho ra được kết quả như trong hình, các giá trị < 100 được bôi đậm màu đỏ:
Kết quả nhận về như hình
Tô màu các ô có giá trị 100 - 300
Áp dụng Conditional formatting trong Excel, bạn thực hiện các thao tác như các bước đầu của phần 1 cho các giá trị < 100, nhưng ở thao tác thứ 5 chọn Between:
Ví dụ tô màu có điều kiện trong Excel
Nhập các giá trị trong phần cửa sổ Between, chọn từ vùng giá trị 100 đến 300 và chọn màu đánh dấu khác với nhóm thứ nhất như hình bên dưới đây:
Chọn từ vùng giá trị 100 đến 300
Nhấn OK và kết quả cho ra được như trong bảng:
Kết quả như hình
Tìm các giá trị > 300
Cũng thực hiện các thao tác như trong các bước của tô màu cho các giá trị thuộc 2 vùng trên, ở mục này chọn Greater Than:
Chọn Greater Than
Nhập giá trị là 300, và lựa chọn màu không trùng lặp với 2 vùng trên cho cửa sổ mới mở ra:
Nhập giá trị là 300
Chọn ok và cho ra kết quả ở bảng dữ liệu:
Kết quả cuối cùng
Tìm hiểu định dạng theo điều kiện riêng
Conditional Formatting trong Excel còn cho phép bạn có thể tự thiết lập một quy định để định dạng ô chứa giá trị.
Ví dụ: Trong bảng mô tả mà Unica cung cấp dưới đây, định dạng ô tại cột Anh có giá trị >6.9 do bạn tự thiết lập.
Bước 1: Click vào cột E để chọn các giá trị cột Anh.
Chọn vào cột E
Bước 2: Chọn Conditional Formatting, sau đó chọn New Rule.
Chọn Ruler
Bước 3: Chọn Use a formula to determine which cells to format. Sau đó bạn nhập công thức “=E4 > 6.9” vào ô Format values where this formula is true => chọn Format.
Nhập công thức vào ô Format values where this formula is true
Bước 4: Trong hộp thoại Format Cells, bạn chọn màu sắc tại Fill rồi nhấn OK.
Chọn màu rồi chọn OK
Bước 5: Nhấn OK để hoàn thành.
Nhấn OK là hoàn thành
>>> Xem thêm: Cách sử dụng remove duplicate xóa trùng nội dung trùng lặp trong Excel
Một số câu hỏi liên quan đến Conditional Formatting trong Excel
Để có thêm nhiều những kiến thức hữu ích khác liên quan đến format condition trong excel, các bạn hãy tham khảo một số những câu hỏi phổ biến kèm câu trả lời mà Unica đã tổng hợp được và chia sẻ dưới đây nhé.
Làm sao để đánh dấu các giá trị trùng lặp bằng màu đỏ?
Để đánh dấu các giá trị trùng lặp bằng màu đỏ bạn thực hiện theo các bước sau:
Bước 1: Dùng chuột trái bôi đen vùng dữ liệu.
Chọn vùng cần đánh dấu
Bước 2: Trên thanh công cụ bạn chọn mục Conditional Formatting > Chọn Highlight Cells Rules > Chọn Duplicate Values.
Chọn Duplicate Values
Bước 3: Trong của sổ Duplicate Values, bạn chọn Light Red Fill with Dark Red Text. Sau đó nhấn vào OK.
Nhấn OK để hoàn thành
Làm sao để tạo ra bản đồ nhiệt
Các bước để tạo bản đồ nhiệt bạn cần biết đó là"
Bước 1: Dùng chuột bôi đen vùng dữ liệu cần tạo biểu đồ nhiệt.
Chọn vùng cần tạo biểu đồ nhiệt
Bước 2: Trên thanh công cụ bạn chọn mục Conditional Formatting -> chọn tiếp Color Ruler -> sau đó chọn 1 bảng màu.
Chọn Color Scales
Bước 3: Kết quả sau khi hoàn thành.
Kết quả hoàn thành
Ngoài những nội dung mà Unica đã chia sẻ qua bài viết trên, nếu bạn muốn tìm hiểu chi tiết hơn nữa về cách cách định dạng theo điều kiện Conditional Formatting thì có thể tham khảo nội dung Video bài giảng dưới đây:
Hướng dẫn định dạng theo điều kiện Conditional Formatting
Kết luận
Trên đây Unica đã hướng dẫn chi tiết tới bạn đọc cách sử dụng công cụ Conditional Formatting trong Excel. Với những chia sẻ hữu ích về học Excel cơ bản đến nâng cao này chắc chắn bạn đã có thể thực hiện thành thạo việc tìm và tô màu có điều kiện trong Excel. Định dạng có điều kiện trong Excel được đánh giá là một trong những tính năng quan trọng bậc nhất và có tính ứng dụng cao trong trong rất nhiều nghiệp vụ khác nhau. Vì vậy bạn hãy nhớ kỹ những kiến thức mà chúng tôi chia sẻ nhé.
Chúc bạn thành công!
16/02/2019
21914 Lượt xem
Hướng dẫn chi tiết cách sử dụng hàm Dcounta trong excel
Trong quá trình tính toán các dữ liệu trong Excel, bạn sẽ phải làm việc với rất nhiều hàm và công thức quan trọng. Một trong số đó phải kể đến hàm Dcounta. Hàm DCOUNTA trong excel là một trong những hàm thống kê giúp bạn đếm các ô trong danh sách dữ liệu phù hợp với điều kiện xác thực một cách nhanh chóng. Hiện nay, hàm Dcounta được sử dụng khá phổ biến trong excel. Vậy DCOUNTA là hàm gì? Trong nội dung bài viết này, Unica sẽ đưa ra các ví dụ minh họa giúp bạn biết hiểu rõ hơn về cách dùng hàm Dcounta trong các trường hợp cụ thể.
Hàm DCOUNTA trong Excel là gì?
Hàm DCOUNTA là một hàm tích hợp trong Excel thuộc chuỗi Hàm Database. Nó có thể được sử dụng như một hàm bảng tính (WS) trong Excel. Là một hàm trang tính, hàm DCOUNTA Excel có thể được nhập như một phần của công thức trong một ô của trang tính.
Hàm DCOUNTA trong Excel dùng để đếm các ô không trống trong một cột theo một hay là nhiều điều kiện xác định.
Hàm DCOUNTA sử dụng để đếm các ô không trống
Kết quả: Hàm dcounta nhiều điều kiện trả về một giá trị số.
Hàm DCOUNTA áp dụng đối với Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Cấu trúc hàm DCOUNTA
Hàm Dcounta trong excel có cấu trúc như sau:
=DCOUNTA(Database; Field; Criteria)
Trong đó:
Database: là phạm vi dữ liệu bắt đầu từ tiêu đề cột.
Field: là tên cột được dùng trong hàm hoặc số thứ tự của cột được dùng trong bảng tính. Field cũng có thể là tiêu đề cột. Trong trường hợp bạn nhập trực tiếp tiêu đề cột vào công thức tính phải đặt tên tiêu đề cột trong dấu ngoặc kép “”.
Criteria: là phạm vi dữ liệu chứa điều kiện. Trong đó, criteria phải chứa ít nhất 1 tiêu đề cột và 1 ô chứa điều kiện bên dưới ô tiêu đề cột.
>>> Xem thêm: Cách dùng hàm INDIRECT trong Excel để tham chiếu dễ hiểu nhất
Cấu trúc hàm DCOUNTA trong excel
Cách sử dụng hàm DCOUNTA trong Excel
Hàm DCOUNTA cách sử dụng không khó, tuy nhiên không phải ai cũng biết, nhất là với những người mới làm quen với hàm này. Dưới đây là 3 ví dụ minh họa tương ứng với 3 trường hợp ứng dụng hàm Dcounta trong excel.
Đếm số lượng mặt hàng có đơn giá thay đổi
Trong trường hợp này, hàm Dcounta sẽ giúp chúng ta đếm các thông tin có sự thay đổi trong bảng tính. Dưới đây là ví dụ minh họa cụ thể.
Chúng ta có bảng tính:
Cho trước một bảng tính
Yêu cầu: Hãy đếm số lượng mặt hàng có thay đổi giá.
Cách thực hiện:
Bước 1: Xác định các thành phần trong công thức Dcounta
Database: Phạm vi dữ liệu của chúng ta trong trường hợp này là '=A1:D11'
Field: Tiêu đề cột F, “Ghi chú”
Criteria: Chúng ta cần tạo vùng điều kiện trong bảng tính excel. Đồng thời, bạn cũng cần xác định thêm ô chứa kết quả sau khi dùng hàm Dcount. Trong bảng minh họa dưới đây Criteria là G1:G2
Xác định các thành phần của công thức
Bước 2: Tạo công thức hàm Dcounta vào ô hiển thị kết quả trong bảng tính. Ở đây chúng tôi sẽ chọn ô D13.
Tạo công thức hàm vào ô D13
Bước 3: Nhấn Enter để nhận kết quả
Kết quả nhận được
>>> Xem thêm: Cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu có ví dụ
Đếm số lượng mặt hàng có đơn giá tăng
Trong trường hợp này, hàm Dcounta sẽ giúp chúng ta đếm các thông tin có sự thay đổi tăng trong bảng tính. Có nghĩa là hàm Dcounta sẽ giúp chúng ta đếm các dữ liệu có điều kiện phân loại mở rộng hơn. Dưới đây là ví dụ minh họa cụ thể.
Chúng ta có bảng tính:
Bảng tính ban đầu
Yêu cầu: Hãy đếm số lượng sản phẩm có thay đơn giá tăng.
Cách thực hiện:
Bước 1: Xác định các thành phần trong công thức Dcounta
Database: Phạm vi dữ liệu của chúng ta trong trường hợp này là '=A1:D11'
Field: Tiêu đề cột F, “Ghi chú”
Criteria: Chúng ta cần tạo vùng điều kiện trong bảng tính excel. Đồng thời, bạn cũng cần xác định thêm ô chứa kết quả sau khi dùng hàm Dcount. Trong bảng minh họa dưới đây Criteria là G1:G2. Cụ thể hơn, bên dưới cột tiêu đề G1 là một điều kiện cụ thể “Tăng”. Do yêu cầu không xác định thông tin là tăng bao nhiêu hay tăng như thế nào nên chúng ta có thể sử dụng dấu “*” đặt cạnh từ tăng “Tăng*” để giúp hàm Dcounta xác định điều kiện.
Xác thực thành phần trong công thức
Bước 2: Tạo công thức hàm Dcounta vào ô hiển thị kết quả trong bảng tính
Tạo công thức hàm Dcounta
Bước 3: Nhấn Enter để nhận kết quả
Kết quả tính hàm nhận được
Đếm số lượng mặt hàng có đơn giá giảm trên 10%
Trong trường hợp này, hàm Dcounta sẽ giúp chúng ta đếm các thông tin có sự thay đổi tăng 10% trong bảng tính. Có nghĩa là hàm Dcounta sẽ giúp chúng ta đếm các dữ liệu có điều kiện phân loại cụ thể. Dưới đây là ví dụ minh họa cụ thể.
Chúng ta có bảng tính:
Cho trước một bảng tính
Yêu cầu: Hãy đếm số lượng sản phẩm có thay đơn giá tăng 10%.
Cách thực hiện:
Bước 1: Xác định các thành phần trong công thức Dcounta
Database: Phạm vi dữ liệu của chúng ta trong trường hợp này là '=A1:D11'
Field: Tiêu đề cột F, “Ghi chú”
Criteria: Vùng điều kiện của chúng ta là G1:G2. Trong đó G1 là tiêu đề cột trùng với tiêu đề cột Field. Và G2 là điều kiện cụ thể “Tăng 10%”.
Xác định các thành phần
Bước 2: Tạo công thức hàm Dcounta vào ô hiển thị kết quả trong bảng tính.
Tạo công thức tính hàm
Bước 3: Nhấn Enter để nhận kết quả
Kết quả nhận được
>>> Xem thêm: Hàm đếm có nhiều điều kiện trong excel COUNTIFS: Công thức và cách dùng
Một số lưu ý khi dùng hàm DCOUNTA trong Excel
Trong quá trình sử dụng hàm DCOUNTA trong Excel bạn cần đặc biệt chú ý một số những lưu ý sau:
Hàm DCOUNT có thể dùng cho bất kỳ phạm vi nào cho đối số Criteria nhưng phải chứa ít nhất một tiêu đề cột và một ô chứa điều kiện dưới ô tiêu đề cột.
Không đặt phạm vi điều kiện bên dưới danh sách vì thông tin mới sẽ được thêm vào cuối danh sách.
Phạm vi điều kiện không ghi đè lên danh sách.
Để thao tác toàn bộ một cột trong danh sách nên nhập 1 dòng trống bên dưới hay bên trên tiêu đề cột trong phạm vi điều kiện.
Kết luận
Trên đây là bài viết hướng dẫn chi tiết về cách sử dụng hàm đếm theo điều kiện DCOUNTA trong excel. Hàm DCOUNTA trong excel vô cùng đơn giản và dễ thực hiện, thay vì phải ngồi đếm từng giá trị cần tính toán một cách thủ công, mò mẫn nhầm lẫn như trước đây thì hàm DCOUNTA đã giúp bạn, chỉ cần một công thức đơn giản là bạn đã có thể tính toán những giá trị cần biết trong một danh sách có thể lên tới hàng trăm con số.
Nếu bạn muốn nâng cao kiến thức sử dụng các hàm Excel hay thủ thuật mời bạn đọc tham khảo những khoá học Excel hấp dẫn nhất trên Unica nhé.
Hy vọng bài viết của mình sẽ giúp ích được cho các bạn trong công việc và học tập.
15/02/2019
7145 Lượt xem
Excel không chạy công thức: Nguyên nhân và cách khắc phục triệt để
Excel là một phần mềm tính toán và xử lý dữ liệu phổ biến và hữu ích. Tuy nhiên, trong quá trình sử dụng Excel, có thể gặp phải tình trạng excel không chạy công thức, tức là công thức được nhập vào ô không được tính toán và hiển thị kết quả. Đây là một lỗi thường gặp và ảnh hưởng đến hiệu quả của công việc. Vậy nguyên nhân và cách khắc phục triệt để khi excel không nhảy công thức là gì? Hãy cùng Unica tìm hiểu trong bài viết này.
Tại sao excel không chạy công thức?
Có nhiều nguyên nhân có thể dẫn đến tình trạng excel không chạy công thức, trong đó có:
Công thức được hiển thị dưới định dạng văn bản
Nếu bạn nhập công thức vào ô có định dạng text, ví dụ như “=A1+B1”, Excel sẽ hiển thị kết quả là “A1+B1” mà không tính toán. Để khắc phục, bạn cần chuyển đổi ô sang định dạng số, ví dụ như “=A1+B1”, hoặc sử dụng hàm SUM để tính toán kết quả.
Lỗi định dạng văn bản
Trang tính chứa tham chiếu vòng tròn
Nếu bạn có một trang tính chứa các công thức được lấy từ các ô khác, ví dụ như “=SUM(A1:A10)”, và bạn muốn tính toán kết quả của trang tính này, Excel sẽ không chạy trang tính đó vì nó có tham chiếu vòng tròn (circular reference). Để khắc phục, bạn cần loại bỏ các ô có tham chiếu vòng tròn khỏi trang tính hoặc sử dụng hàm OFFSET để tạo ra các ô mới không có tham chiếu vòng tròn.
Excel đang ở chế độ hiện công thức
Với lỗi này, khi bạn nhập công thức bằng tay, excel thay vì trả lại kết quả tính toán sẽ hiển thị lại công thức bạn vừa nhập. Ví dụ, bạn muốn tính tổng của ô C3 và D3. Tại ô E3 bạn nhập công thức có dạng = Sum(C3:D3) nhưng thay vì trả lại kết quả tính thì ô E3 sẽ hiện công thức = Sum(C3:D3).
Lỗi hiển thị công thức trong excel
Các cách khắc phục lỗi excel không chạy công thức
Sau khi xác định nguyên nhân của lỗi excel không chạy công thức, bạn có thể áp dụng các cách sau để sửa lỗi:
Cách sửa lỗi excel không tự nhảy công thức do nhập công thức vào ô có định dạng text
Những ô tính đang được để ở định dạng TEXT có thể không thể nhảy công thức tính toán. Trong trường hợp này, nếu bạn muốn khắc phục công thức excel không chạy thì hãy thử cách sau:
Bước 1: Bôi đen toàn bộ vùng dữ liệu đang ở định dạng text.
Bôi đen vùng dữ liệu cần chuyển định dạng
Bước 2: Trong thẻ Home, phần Number, bạn chọn định dạng General.
Chọn định dạng General
Bước 3: Nhấp 2 lần vào từng ô vừa được sửa định dạng thì lỗi text sẽ được khắc phục.
Nhấp 2 lần vào từng ô
Đặt lại định dạng tính toán
Với những lỗi công thức trong excel do lỗi định dạng tính toán, bạn có thể khắc phục lỗi này như sau:
Bước 1: Mở file excel cần sửa định dạng tính toán, nhấn chọn thẻ File.
Chọn thẻ File
Bước 2: Tìm và chọn thẻ More > Chọn Options.
Chọn Options
Bước 3: Khi hộp thoại Excel Options hiện lên, bạn chọn mục Formulas.
Chọn mục Formulas
Bước 4: Tới phần Caculation Options, bỏ tích chọn Manual. Sau đó, bạn tích chọn vào Automatic rồi nhấn OK.
Nhấn OK
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Tắt chế độ tính toán thủ công
Nhiều trường hợp excel không nhảy công thức do người dùng đang bật chế độ hiển thị công thức trong excel. Muốn sửa excel bị lỗi công thức này, mời bạn tham khảo cách dưới đây:
Bước 1: Mở file excel cần sửa định dạng tính toán, nhấn chọn thẻ File.
Chọn file
Bước 2: Tìm và chọn thẻ More > Chọn Options.
Chọn Options
Bước 3: Khi hộp thoại Excel Options hiện lên, bạn chọn mục Advanced.
Chọn mục Advanced
Bước 4: Đến phần Display options for this worksheet. Bỏ tích chọn Show formulas in cells instead of their calculated results. Rồi nhấn OK.
Nhấn OK
Dù là khi thực hiện phép tính hàm AVERAGEIF trong Excel, hàm Sum, Vlookup hay bất kỳ hàm nào mà không nhảy công thức thì bạn vẫn cần kiểm tra xem đã tắt chế độ tính toán thủ công chua.
Excel không chạy công thức do bị lỗi Circular References
Đối với lỗi Circular References, bạn có thể khắc phục bằng cách sau:
Bước 1: Trên thanh công cụ, chọn thẻ Formulas
Chọn thẻ Formulas
Bước 2: Ở phần Formula Auditing, click chọn Error checking
Chọn Error checking
Bước 3: Ở danh sách xổ xuống, chọn Circular References. Sau khi thực hiện thao tác này, toàn bộ các ô bị lỗi vòng lặp sẽ hiện lên.
Chọn Circular References
Bước 4: Khi đã xác định được chính xác ô cần xóa, chuyển công thức, bạn cần sửa lại tham chiếu trong công thức là lỗi sẽ được khắc phục.
Khắc phục lỗi vòng lặp trong Excel
Sửa lỗi Excel không nhảy công thức cho Excel 2003
Để khắc phục và sửa lỗi excel quên việc nhảy công thức đối với bản excel 2003, chúng ta cần kích hoạt lại chế độ tự động nhảy công thức, bởi có lẽ bạn đã chưa bật chế độ tự động hay thiết bị trong quá trình làm việc đã tự động tạm dừng.
Thực hiện thao tác với mục Tool > chọn Options
Làm theo bước làm như trong hình ảnh bên dưới, tính năng tự động nhảy công thức đã được kích hoạt thành công và việc sửa lỗi do excel không nhảy được công thức cho bản 2003 đã được giải quyết.
Sửa lỗi excel không tự nhảy công thức
Sửa excel không nhảy công thức cho bản 2007 và 2010
Mỗi bản excel mỗi năm đều không giống nhau nhưng việc sửa lỗi excel không nhảy công thức đối với bản excel 2007 và 2010 không giống sửa lỗi như bản 2003 nhưng các thao tác sửa không hề phức tạp. Bạn cũng sẽ làm đối với phiên bản excel của 2 năm này là việc vào kích hoạt chế độ tự hiện nhập công thức excel.
Để sửa công thức không chạy trong excel bản 2007 và 2010, bạn làm như sau:
Trên thanh công cụ, bạn chọn File, tìm chọn mục Options.
Tìm chọn mục Options
Khi hộp thoại hiện ra, bạn chọn Formulas, ở Calculation options thì tích chọn Automatic.
Chọn Automatic
Tổng kết
Vậy là chúng ta đã tìm hiểu được nguyên nhân và cách khắc phục triệt để khi excel không chạy công thức. Hy vọng bài viết này đã giúp bạn giải quyết được vấn đề của mình. Ngoài kiến thức về cách sửa lỗi Excel không tự nhảy công thức bạn có thể tham khảo các khóa học Excel online với sự hướng dẫn và giảng dạy từ những chuyên gia hàng đầu Unica.vn trong lĩnh vực tin học văn phòng đang chờ bạn khám phá.
Chúc các bạn may mắn!
15/02/2019
16451 Lượt xem
Cách dùng hàm mod trong excel kèm ví dụ chi tiết
Hàm MOD dùng để lấy số dư của phép chia, sau đó kết hợp và sử dụng với các hàm khác thực hiện tính toán trên bảng tính Excel. Nếu bạn là người hay làm việc trên excel, chắc chắn hàm MOD không còn xa lạ gì với bạn nữa. Thế nhưng nếu là bạn là người mới tìm hiểu đến các hàm trong tin học văn học thì hẳn là sẽ còn nhiều bỡ ngỡ với hàm này, không biết cách dùng hàm MOD trong excel như thế nào? Trong bài viết này, Unica sẽ chia sẻ cho bạn một số thông tin xoay quanh hàm MOD excel, hãy tham khảo nhé.
Hàm MOD là gì?
Hàm lấy số dư MOD trong Excel là hàm trả về kết quả số dư của một số và kết quả trả về sẽ cùng dấu với ước số. Thực tế hàm lấy số dư MOD không hề khó sử dụng tuy nhiên thông thường hàm này không được tách riêng mà sẽ được sử dụng kết hợp với các hàm khác.
Hàm MOD - hàm lấy số dư trong excel
Công thức của hàm MOD trong Excel
Công thức MOD trong Excel:
=MOD (number,divisor)
Trong đó:
Number: Bắt buộc, là số mà bạn muốn tìm số dư
divisor: Bắt buộc, là số mà bạn chia số cho nó
Lưu ý:
Nếu số bị chia hoặc số chia là giá trị văn bản, MOD sẽ trả về giá trị lỗi #VALUE!
Hàm MOD có thể được biểu diễn bằng các số hạng của hàm INT trong Excel (lấy phần kết quả nguyên): MOD(n,d) = n - d*INT(n/d)
Công thức hàm MOD
Ví dụ hàm mod trong excel
Cho bảng sau, yêu cầu bạn tính số dư của phép chia. Khi này bạn sử dụng mà mod tại ô C2 theo công thức như sau: =MOD(A2,B2) với A2 là số bị chia, B2 là số chia.
Nhập công thức hàm mod để tính
Nhấn Enter kết quả sẽ hiển thị
Kết quả hiển thị
Giải thích kết quả này như sau:
Kết quả tại C2 bằng 2 vì 5 chia 3 bằng 1, dư 2.
Kết quả tại C3 bằng 4 vì mang dấu của số chia
Tương tự kết quả ô C4, C5, C6 cũng vậy.
Kết quả tại C7 bị lỗi #DIV/0! vì số chia là 0.
Hướng dẫn cách dùng hàm MOD trong excel
Hàm MOD có thể được ứng dụng để dùng trong rất nhiều trường hợp khác nhau. Bạn có thể học cách sử dụng hàm MOD trong từng trường hợp, trong bài viết sau chúng tôi sẽ hướng dẫn chi tiết cho bạn tham khảo.
Sử dụng hàm MOD dùng để lấy phần dư trong Excel
Để hiểu rõ hơn công thức tính MOD lấy phần dư bạn hãy theo dõi ví dụ bên dưới:
Ví dụ: Tính số dư của phép chia
Bước 1: Trong bảng tính excel tại ô D5 bạn nhập hàm theo cú pháp: =MOD(A3,B3).
Nhập công thức hàm
Trong đó:
A3: Là tử số hay chính là số bị chia.
C5: Là mẫu số hay chính là số chia.
Bước 2: Nhấn Enter để xem đáp án nhận lại được là bao nhiêu. Như vậy theo kết quả này hàm MOD đã giúp bạn lấy được phần dư là 1.
Kết quả nhận được
Bước 3: Để áp dụng công thức hàm Mod xem kết quả cho những ô phía dưới thì bạn bấm giữ 1 ô vuông ở phía góc phải, sau đó kéo chuột xuống dưới Như vậy, bạn sẽ thấy dãy kết quả cuối cùng.
Kết quả cuối cùng của cả bảng
Giải đáp kết quả từng ô này như sau:
Kết quả tại ô C4: Xuất hiện lỗi #DIV/0! do số chia là 0.
Kết quả tại ô C5: Số bị chia có giá trị là 0 nên ô kết quả cũng sẽ có giá trị là 0.
Kết quả tại ô C6: Kết quả chứa dấu âm do số chia là số âm.
Kết quả tại ô C7: Số bị chia là số âm thì kết quả vẫn là số dương do không bị ảnh hưởng.
Kết quả tại ô C8: Số bị chia và số chia đều có dấu âm nên ô kết quả sẽ có dấu âm.
Sử dụng hàm MOD dùng để đánh dấu các hàng
Ví dụ: Cho bảng sau, yêu cầu đánh dấu các hàng thứ 2
Cho bảng dữ liệu
Để thực hiện đánh dấu các hàng bằng hàm Mod bạn thực hiện theo các bước sau:
Bước 1: Vào Conditional Formating => Chọn New Rule
Chọn New Ruler
Bước 2: Bảng New Formatting Rule xuất hiện bạn chọn Use a formula to determine which cells to format
Chọn Use a formula to determine which cells to format
Bước 3: Nhập công thức =MOD(ROW(A1),2)=0 vào ô Edit the rule như hình dưới.
Nhập công thức để đánh dấu
Bước 4 : Vào ô Format để chọn kiểu chữ, màu chữ, màu nền đánh dấu
Chọn màu chữ và kiểu chữ
Sau đó nhấn OK chúng ta có kết quả, các hàng thứ 2 đã được đánh dấu.
Kết quả nhận được
>>> Xem thêm: Hướng dẫn cách dùng hàm Frequency trong Excel chi tiết
Ứng dụng hàm MOD dùng để tìm số ngày lẻ
Ví dụ: Cho một bảng tính có các dữ liệu gồm: khách hàng, ngày check-in, check-out. Nhiệm vụ của bạn là phải tính số ngày lẻ.
Số ngày lẻ chính là số ngày lẻ không đủ 7 ngày. Hiểu một cách đơn giản, số ngày lẻ chính là phần dư của phép tính. Hàm MOD để tìm số ngày lẻ trong ví dụ này như sau: = (Số ngày giữa khoảng thời gian Check-out trừ đi thời gian Check-in)/7.
Áp dụng vào bảng tính với khách hàng Lương Minh Đức bạn nhập công thức như sau: =MOD(C2-B2;7)
Ví dụ tìm số ngày lẻ
Kéo chuột xuống bên dưới để áp dụng cho những khách hàng tiếp theo.
Kết quả xác định số ngày lẻ cuối cùng
Sử dụng hàm MOD dùng để tính toán các ô
Ví dụ: Sử dụng hàm MOD để tính toán tìm ra số dư của phép chia. Nhiệm vụ: Tính số dư của phép chia tại ô C2.
Áp dụng công thức =MOD(B2,A2) (B2 là số chia còn A2 là số bị chia).
Áp dụng công thức số chia trong execl
Nhấn Enter để xem kết quả tương ứng. Kéo xuống dưới để hiển thị giá trị tính toán cho các ô bị chia.
Kết quả nhận được
Sử dụng hàm MOD để đếm các ô có chứa số lẻ hoặc số chẵn
Ví dụ: Cho bảng dữ liệu sau yêu cầu lấy số dư của các ô tham chiếu.
Cách thực hiện sử dụng công thức:
Số chẵn: =MOD (A2,2), Số lẻ: =MOD (A2,2)>=0
Ứng dụng của hàm mod trong excel
Để kiểm tra độ chính xác, bạn có thể kiểm tra bằng hàm IF
Bước 1: Nhập công thức vào địa chỉ ô B2 công thức sau:
=IF(MOD(A2,2)=0,“Is even”,“Is odd”)
Hàm IF kết hợp hàm MOD
Bước 2: Nhấn Enter
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Sử dụng MOD trong Excel để tô sáng các ô
Để có thể tô sáng các ô bạn cần phải tạo một quy tắc định dạng có điều kiện dựa trên công thức.
Ví dụ: Làm nổi bật số thập phân và số nguyên.
Để xác định số nguyên bạn cần nhớ nguyên tắc: Bất kỳ số nào chia cho 1 bằng chính nó thì tức là số nguyên, phần còn lại bằng 0, công thức MOD như sau: =MOD(A2,1)=0
Trường hợp phần còn lại lớn hơn 0 thì tức là số thập phân, công thức MOD là: =MOD(A2,1)>0
Sử dụng hàm MOD trong excel để tô sáng các ô
Sử dụng MOD để xác thực dữ liệu
Có thể bạn không biết nhưng hàm MOD còn có một cách sử dụng khác đó là xác thực dữ liệu, ngăn chặn việc nhập dữ liệu nhất định vào các ô được chọn. Để tạo ra quy tắc xác thực dữ liệu bạn cần thực hiện một số thao tác trước như: Từ menu chọn Data => Chọn Data Validation. Trên tab Settings, tại hộp Allow bạn chọn Custom => Nhập công thức vào hộp Formula.
Ví dụ: Để xác thực dữ liệu các số nguyên trong các ô A2:A8, bạn thực hiện quy tắc: =MOD(A2,1)=0
Ví dụ cách xác thực dữ liệu
Hướng dẫn dùng hàm MOD trong Excel và phối hợp hàm INT
Thông thường, hàm MOD hay được sử dụng để làm hàm chia lấy số dư trong ô kết quả. Tuy nhiên, khi ứng dụng trong thực tế thì hàm MOD lại hay được sử dụng để tính toán số ngày trả phòng khi họ sử dụng dịch vụ nhà nghỉ, khách sạn. Do đó, hàm MOD hay được sử dụng kết hợp với hàm INT. Sau đây là một ví dụ cụ thể giúp bạn hiểu rõ hơn vấn đề này.
Ví dụ: Yêu cầu tính toán số ngày mà khách phải trả phòng khi họ sử dụng dịch vụ khách sạn.
Bước 1: Tính toán số tuần mà khách đã lưu trú tại khách sạn
Để tính toán số tuần mà khách đã lưu trú thì bạn không dùng tháng để tính vì số ngày trong tháng sẽ không cố định, có tháng 28, tháng lại 30, 31 ngày. Khi này, bạn sẽ sử dụng hàm INT để tính số tuần mà khách đã lưu trú. Như vậy tức là bạn sẽ phải tính từ ngày đầu khách đến cho đến ngày khách trả phòng. Cụ thể như sau:
Nếu là 3 ngày thì bạn sẽ tính là 1 tuần.
Nếu là 8 ngày thì bạn sẽ tính là 2 tuần.
Nấu là 20 ngày thì bạn sẽ tính là 3 tuần.
Như vậy, cú pháp hàm INT như sau: =INT((B3-A3)/7.
Giải thích hàm INT này như sau: Ngày khách đi (Ngày khách trả phòng) trừ cho ngày khách đến (Ngày khách nhận phòng). Sau đó, lấy kết quả này đem chia ra cho 7 (1 tuần). Như vậy sẽ xác định được khách ở khách sạn mấy tuần.
Công thức hàm INT áp dụng
Bước 2: Tính toán số ngày mà khách sẽ trả phòng sớm
Phần dư của phép chia sẽ thể hiện số ngày còn lại tính tới thời điểm khách trả phòng. Để tính toán phần dư chúng ta sử dụng hàm MOD trong excel. Cú pháp hàm MOD như sau: =MOD(B3-A3,7).
Giải thích hàm MOD này như sau: Lấy số ngày mà khách trả phòng trừ đi số ngày mà khách nhận phòng. Sau đó, lấy kết quả của phép trừ này chia cho số 7 để tính xem số dư ra trong kết quả là bao nhiêu và xét hai trường hợp như sau:
Nếu như (Số ngày khách trả – Số ngày khách nhận phòng) : 7 = 0. Đây là phép chia hết, điều này tức là khách họ đã ở khách sạn hết thời hạn đặt phòng lúc đầu.
Nếu như (Số ngày khách trả – Số ngày khách nhận phòng) : 7 = 2, 3, 4,… Đây là phép chia có dư, điều này có nghĩa là khách vẫn chưa ở khách sạn hết thời hạn đã đặt lúc đầu.
Cuối cùng, bạn tính được số ngày mà khách ở khách sạn như sau: Số tuần * 7 – Số ngày ở còn dư.
Số ngày còn dư chưa sử dụng
Lỗi thường gặp trong hàm MOD
Khi dùng hàm MOD trong excel, bạn rất hay thấy xuất hiện lỗi #VALUE! và #DIV/0!. Cụ thể những lỗi này như sau:
Lỗi #VALUE!
Khi sử dụng hàm MOD Excel, nếu đối số bạn nhập không phải là số mà là chữ hoặc ký tự thì sẽ xuất hiện lỗi #VALUE. Ngoài ra, lỗi này còn xuất hiện trong trường hợp khi bạn ghi ngày tháng, nguyên nhân là vì định dạng ngày tháng năm mà bạn nhập trong Excel không đúng như định dạng ngày tháng năm trong máy tính của bạn. Để khắc phục lỗi này, bạn chỉ cần để đồng nhất định dạng ngày tháng năm trong Excel và Laptop là được.
Lỗi #DIV/0!
Nếu số chia là số 0 thì sẽ xảy ra lỗi #DIV/0.
- -
Lỗi thường gặp khi dùng hàm MOD
Kết luận
Như vậy, Unica đã hướng dẫn các bạn cách sử dụng hàm MOD trong Excel chi tiết và cụ thể đầy đủ nhất. Để có thể dễ hình dung cũng như sớm hiểu bản chất của hàm này Unica khuyến khích bạn vừa tham khảo kiến thức vừa thực hành ngay trên máy tính, như vậy bạn sẽ sớm thực hiện thành thạo hàm lấy số dư MOD này. Bạn đọc quan tâm đến những kiến thức tin học văn phòng khác có thể tham khảo thêm các khóa học tin học văn phòng trên Unica như khoá học Excel và toàn bộ hệ thống hàm trong Excel. Từ đó bạn có thểdễ dàng thống kê, xử lý số liệu hay lập báo cáo một cách nhanh chóng, chính xác nhất, từ đó giảm thiểu tối đa thời gian cũng như nâng cao hiệu suất công việc tối đa.
Chúc bạn thành công!
>>> Xem thêm: Hướng dẫn 3 cách tính căn bậc hai trong excel siêu nhanh, đơn giản
>>> Xem thêm: 8 cách đánh thứ tự trong excel cực nhanh và đơn giản
15/02/2019
13572 Lượt xem
Hướng dẫn sử dụng hàm VLOOKUP trong Excel có ví dụ cụ thể
Hàm VLOOKUP là một trong những hàm quan trọng nhất trong Excel, cho phép bạn tra cứu dữ liệu từ một bảng dữ liệu theo một tiêu chí nào đó. Hàm VLOOKUP có thể giúp bạn tìm kiếm thông tin nhanh chóng và chính xác, đặc biệt khi bảng dữ liệu lớn và phức tạp. Trong bài viết này, Unica sẽ Hướng dẫn cách sử dụng hàm VLOOKUP trong Excel một cách dễ hiểu. Cùng tìm hiểu ngay.
Vlookup là hàm gì?
VLOOKUP là viết tắt của Visual Lookup, có nghĩa là tra cứu trực quan. Hàm VLOOKUP được phát triển bởi Microsoft và được tích hợp sẵn trong Excel từ phiên bản 97. VLOOKUP cho phép bạn tra cứu một giá trị từ một ô hoặc một hàng trong một bảng dữ liệu theo một tiêu chí nào đó.
Hàm VLOOKUP cho phép bạn tra cứu một giá trị
Hàm VLOOKUP có thể được sử dụng để:
Tìm kiếm thông tin từ một bảng dữ liệu theo tên, số điện thoại, địa chỉ hoặc bất kỳ tiêu chí nào khác.
So sánh hai giá trị và trả về kết quả phù hợp.
Tạo ra các biểu đồ hoặc bảng số liệu từ các giá trị trong một bảng dữ liệu.
Lọc ra các hàng hoặc cột có giá trị mong muốn.
Công thức hàm VLOOKUP
Trước khi vào cách sử dụng hàm vlookup, bạn cần biết công thức hàm VLOOKUP. Hàm này có dạng như sau:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Trong công thức này:
lookup_value: là giá trị bạn muốn tra cứu trong bảng dữ liệu. Nó có thể là một số nguyên hoặc văn bản.
table_array: là bảng dữ liệu chứa các ô hoặc hàng bạn muốn tra cứu. Nó phải có cùng số lượng hàng và cột với lookup_value.
col_index_num: là chỉ số của cột bạn muốn trả về kết quả. Nó phải nằm trong khoảng từ 1 đến số lượng cột của table_array.
- [range_lookup] là một tham số tùy chọn để chỉ ra xem bạn muốn tra cứu theo nguyên hay không. Nếu bạn nhập TRUE (hoặc 1), hàm sẽ tra cứu theo nguyên; nếu bạn nhập FALSE (hoặc 0), hàm sẽ tra cứu theo văn bản.
Công thức hàm VLOOKUP
Cách sử dụng hàm VLOOKUP trong Excel
VLOOKUP là hàm tìm kiếm được sử dụng nhiều trong Excel vì dễ thực hiện. Để giúp bạn hiểu hơn về cách sử dụng hàm này, dưới đây sẽ là 3 ví dụ về 3 trường hợp dùng VLOOKUP:
Cách sử dụng hàm vlookup cơ bản
Để sử dụng vlookup cơ bản, bạn cần làm theo các bước sau:
Bước 1: Chọn một ô trong bảng dữ liệu mà bạn muốn tra cứu.
Bước 2: Nhập công thức hàm VLOOKUP vào ô đó, theo dạng: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Bước 3: Nhập giá trị bạn muốn tra cứu vào ô khác trong cùng bảng dữ liệu.
Bước 4: Nhấn Enter để xem kết quả.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Ví dụ: Giả sử bạn có một bảng dữ liệu như sau:
Cho một bảng dữ liệu
Yêu cầu: Tìm Phụ cấp của từng nhân viên theo chức vụ trong bảng 1 từ bảng số 2.
Cách dùng vlookup tìm giá trị trên như sau:
Bước 1: Nhập công thức vào ô C3=VLOOKUP(B3;$A$8:$B$11;2;0).
Nhập công thức
Bước 2: Nhấn enter sẽ thu được kết quả như sau:
Kết quả sau khi nhập công thức
Bước 3: Đặt con trỏ chuột bên phía dưới ô C3 rồi kéo xuống phía dưới để copy công thức. Bảng kết quả cuối cùng như sau:
Bảng kết quả cuối cùng
Ngoài cách dùng hàm VLOOKUP cơ bản như bên trên, bạn có thể dùng hàm VLOOKUP kết hợp với If để lọc giá trị theo điều kiện cụ thể.
Cách sử dụng hàm vlookup giữa 2 sheet
Nếu bạn muốn tra cứu giá trị từ một sheet sang một sheet khác trong Excel, bạn có thể sử dụng hàm Vlookup kết hợp giữa 2 sheet với một số thay đổi nhỏ:
Nhập tên sheet và chỉ số của sheet bạn muốn tra cứu vào các ô khác trong công thức hàm VLOOKUP.
Sử dụng ký hiệu & để nối các tên sheet và chỉ số của sheet lại với nhau.
Phải sử dụng ký hiệu $ để chỉ rõ các ô hoặc hàng của sheet bạn muốn tra cứu.
Ví dụ: Cho 2 bảng số liệu sau:
Cho bảng số liệu
Yêu cầu: Tìm lương tháng của từng nhân viên ở Sheet 2 theo bảng dữ liệu đã có ở Sheet 3.
Cách dùng hàm vlookup trong excel tính ví dụ này như sau:
Bước 1: Ở ô D2, bạn nhập công thức =VLOOKUP(C2;Sheet3!$A$2:$B$4;2;FALSE).
Nhập công thức
Bước 2: Nhấn enter, kết quả sẽ tự nhảy ra như hình dưới đây:
Kết quả sau khi nhập công thức
Bước 3: Kéo thả chuột xuống bên dưới để được bảng kết quả hoàn thiện như sau:
Kéo thả chuột xuống bên dưới
Các lỗi thường gặp khi dùng hàm VLOOKUP
Trong quá trình áp dụng hàm Vlookup trong quá trình học Excel cơ bản bạn sẽ gặp một số lỗi cơ bản như sau.
Lỗi #N/A
Lỗi #N/A là lỗi khi bạn nhập một giá trị không tồn tại trong bảng dữ liệu hoặc không được nhận diện được. Điều này có nghĩa là hàm VLOOKUP không thể tìm kiếm được giá trị bạn muốn
Để khắc phục lỗi #N/A, bạn cần làm theo các bước sau:
Bước 1: Kiểm tra lại giá trị bạn muốn tra cứu có tồn tại trong bảng dữ liệu không. Nếu không, bạn cần nhập lại giá trị hoặc sử dụng một giá trị khác.
Bước 2: Kiểm tra lại bảng dữ liệu có được định dạng và cấu trúc đúng không. Nếu không, bạn cần sửa lại bảng dữ liệu hoặc sử dụng một bảng dữ liệu khác.
Bước 3: Kiểm tra lại công thức hàm VLOOKUP có được nhập đúng không. Nếu không, bạn cần sửa lại công thức hàm VLOOKUP hoặc sử dụng một công thức hàm VLOOKUP khác.
Lỗi #N/A khi sử dụng hàm Vlookup
Lỗi #REF!
Lỗi #REF! là lỗi khi bạn nhập một ô hoặc một hàng không tồn tại trong bảng dữ liệu. Điều này có nghĩa là hàm VLOOKUP không thể tìm kiếm được ô hoặc hàng bạn muốn tra cứu. Để khắc phục lỗi #REF!, bạn cần làm theo các bước sau:
Bước 1: Kiểm tra lại ô hoặc hàng bạn muốn tra cứu có tồn tại trong bảng dữ liệu không. Nếu không, bạn cần nhập lại ô hoặc hàng hoặc sử dụng một ô hoặc hàng khác.
Bước 2: Kiểm tra lại bảng dữ liệu có được định dạng và cấu trúc đúng không. Nếu không, bạn cần sửa lại bảng dữ liệu hoặc sử dụng một bảng dữ liệu khác.
Bước 3: Kiểm tra lại công thức hàm VLOOKUP có được nhập đúng không. Nếu không, bạn cần sửa lại công thức hàm VLOOKUP hoặc sử dụng một công thức hàm VLOOKUP khác.
Lỗi #REF! khi dùng VLOOKUP
Lỗi #VALUE!
Lỗi #VALUE! là lỗi khi bạn nhập một giá trị không phải là số nguyên trong hàm VLOOKUP. Điều này có nghĩa là hàm VLOOKUP không thể xử lý được giá trị của lookup_value. Để khắc phục lỗi #VALUE!, bạn cần làm như sau:
Bước 1: Kiểm tra lại giá trị của lookup_value có phải là số nguyên không. Nếu không, bạn cần nhập lại giá trị của lookup_value hoặc sử dụng một giá trị của lookup_value khác.
Bước 2: Kiểm tra lại công thức hàm VLOOKUP có được nhập đúng không. Nếu không, bạn cần sửa lại công thức hàm VLOOKUP hoặc sử dụng một công thức hàm VLOOKUP khác.
Lỗi #VALUE! là lỗi khi bạn nhập một giá trị không phải là số nguyên trong hàm VLOOKUP
>>> Xem thêm: Cách kết hợp hàm sumif và hàm vlookup chi tiết nhất, kèm ví dụ
Lỗi #NAME?
Lỗi #NAME? xảy ra khi bạn viết sai chính tả, nhập sai phạm vi dữ liệu, viết sai chính tả tên phạm vi, text không đặt trong ngoặc kép, công thức tham chiếu sai, thiếu dấu hai chấm, chưa bật hỗ trợ hàm. Cách khắc phục là bạn cần kiểm tra lại lỗi chính vả và cú pháp hàm để đảm bảo rằng mình đã nhập đúng tên.
Lỗi #NAME? xảy ra khi bạn viết sai chính tả
Một số lưu ý khi dùng hàm VLOOKUP
Hàm VLOOKUP là một trong những hàm tìm kiếm thông dụng trong Excel. Hàm này cho phép bạn tra cứu một giá trị trong một bảng dữ liệu theo một tiêu chí nào đó, và trả về giá trị tương ứng ở một ô khác trong cùng bảng. Tuy nhiên, để sử dụng hàm VLOOKUP hiệu quả, bạn cần lưu ý một số điều sau:
Sử dụng tham chiếu tuyệt đối
Để sử dụng hàm vlookup excel, bạn cần có hai tham chiếu: một tham chiếu vào ô tìm kiếm, và một tham chiếu vào ô trả về. Tham chiếu vào ô tìm kiếm phải là một số nguyên, tức là chỉ số của hàng và cột trong bảng dữ liệu. Tham chiếu vào ô trả về phải là một số nguyên hoặc ký hiệu, tức là chỉ số của hàng và cột trong bảng dữ liệu. Nếu bạn sử dụng tham chiếu không tuyệt đối, ví dụ như “A” hoặc “B”, hàm VLOOKUP sẽ không biết bạn muốn tra cứu giá trị ở hàng và cột nào trong bảng.
>>> Xem thêm: Học tin học văn phòng cơ bản
Không lưu trữ giá trị số dưới dạng văn bản
Nếu bạn lưu trữ giá trị số của hàm vlookup excel vào một ô văn bản, ví dụ như “100” hoặc “200”, hệ thống Excel sẽ không hiểu được đó là một số nguyên và không cho phép bạn so sánh hoặc tính toán với các số khác. Do đó, bạn nên lưu trữ giá trị số của hàm VLOOKUP vào một ô số nguyên, ví dụ như “100” hoặc “200”.
Không lưu trữ giá trị số dưới dạng văn bản
Bảng dò tìm chứa những giá trị bị trùng
Nếu bạn muốn tra cứu giá trị của một hàng trong bảng dữ liệu theo nhiều tiêu chí khác nhau, ví dụ như mã sản phẩm, tên sản phẩm và giá sản phẩm, bạn có thể sử dụng hàm VLOOKUP để kết hợp nhiều tham chiếu vào ô tìm kiếm và ô trả về. Tuy nhiên, bạn cần chú ý rằng nếu có hai hoặc nhiều hàng trong bảng có giá trị tương tự hoặc gần gũi nhau, hàm VLOOKUP có thể không chọn được hàng chính xác và mang lại kết quả sai lệch. Do đó, bạn nên kiểm tra lại các tiêu chí của hàm VLOOKUP và điều chỉnh lại các tham chiếu để loại bỏ các hàng không mong muốn.
Kết luận
Trong bài viết này, Unica đã giới thiệu cho bạn về Hướng dẫn sử dụng hàm VLOOKUP trong Excel có ví dụ cụ thể. Hy vọng bạn đã hiểu rõ hơn về hàm này và có thể áp dụng chúng vào công việc của mình.
14/02/2019
13818 Lượt xem
Cách dùng hàm COUNTIFS - hàm đếm có nhiều điều kiện trong excel
Hàm đếm có nhiều điều kiện COUNTIFS là hai hàm tính thông dụng nhất. COUNTIFS có thể đánh giá các điều kiện khác nhau trong cùng hoặc trong các phạm vi khác nhau. Để giúp bạn đọc có thể hiểu rõ hơn về hàm đếm COUNTIFS có nhiều điều kiện trong excel, mời bạn tham khảo nội dung bài viết sau đây để có câu trả lời nhé.
Công thức và cách dùng hàm đếm có nhiều điều kiện trong excel COUNTIFS
Như đã đề cập, hàm COUNTIFS trong Excel được dùng để đếm số lần xuất hiện giá trị trong Excel trên nhiều phạm vi dựa trên một hoặc một số điều kiện. Hàm COUNTIFS có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, vì vậy bạn có thể sử dụng các ví dụ dưới đây trong bất kỳ phiên bản Excel nào.
Công thức COUNTIFS:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Trong đó:
criteria_range1: Vùng chứa điều kiện thứ nhất, đây là phần bắt buộc phải có.
criteria1: Điều kiện thứ nhất (thuộc trong vùng điều kiện thứ nhất) gồm có các dạng số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.
criteria_range2: Vùng chứa điều kiện thứ hai( yêu cầu vùng criteria_range2 phải có độ lớn bằng nhau).
criteria2, ... Điều kiện thứ hai (thuộc trong vùng điều kiện thứ nhất). Bạn có thể chỉ định tối đa 127 cặp phạm vi / điều kiện trong công thức của mình.
Tương tự như vậy với các điều kiện khác cũng sẽ áp dụng như vậy.
Công thức hàm đếm có nhiều điều kiện trong excel
Thực tế, bạn không cần phải nhớ cú pháp của hàm đếm có điều kiện COUNTIFS. Microsoft Excel sẽ hiển thị các đối số của hàm ngay khi bạn bắt đầu nhập; đối số bạn đang nhập vào lúc này được tô đậm.
Bạn chỉ cần nhớ bạn muốn sử dụng hàm COUNTIFS trong Excel là được.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Lưu ý:
Bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm các ô trong một phạm vi với một điều kiện duy nhất cũng như trong nhiều phạm vi với nhiều điều kiện. Nếu sau này, chỉ những ô đáp ứng tất cả các điều kiện được chỉ định mới được tính.
Criteria1 bạn có thể biểu diễn chúng giống như các ví dụ: 10, "<=32", A4, "gord"...
Mỗi phạm vi bổ sung phải có cùng số hàng và cột như phạm vi đầu tiên.Cả phạm vi tiếp giáp và không liền kề đều được cho phép.
Nếu điều kiện là một tham chiếu đến một ô trống , hàm COUNTIFS coi nó là giá trị 0 (0).
Bạn có thể sử dụng các ký tự đại diện trong điều kiện - dấu hoa thị (*) và dấu hỏi (?).
Sử dụng hàm COUNTIFS trong Excel để đếm có điều kiện
>>> Xem thêm: Trọn bộ tin học văn phòng cơ bản từ giảng viên hàng đầu ưu đãi chỉ từ 99k: Xem tại đây
Cách sử dụng hàm COUNTIFS trong Excel
Dưới đây bạn sẽ tìm thấy một số ví dụ về công thức minh họa cách sử dụng các hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện.
Thông qua những ví dụ được minh họa dưới đây bạn sẽ hình dung ra được cách sử dụng những hàm này trong các trường hợp cụ thể tương ứng để có thể áp dụng hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện khác nhau
Cách đếm ô tính với nhiều điều kiện (AND logic)
Công thức 1. Công thức COUNTIFS với nhiều điều kiện khác nhau
Ví dụ bạn có một danh sách sản phẩm như hiển thị trong ảnh chụp màn hình bên dưới. Bạn muốn có được một số lượng các mặt hàng trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị là cột C bằng 0).
Khi này bạn thực hiện bằng cách sử dụng công thức đếm có điều kiện:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
Và số lượng là 2 ("Anh đào " và " Chanh "):
Công thức hàm Countifs
Công thức 2. Công thức COUNTIFS có cùng tiêu chí
Khi bạn muốn đếm các mục có điều kiện giống hệt nhau, bạn vẫn cần cung cấp từng cặp điều kiện_range / điều kiện .
Ví dụ: đây là công thức đúng để đếm các mục có 0 cả trong cột B và cột C:
=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")
Công thức COUNTIFS này trả về 1 vì chỉ " Nho " có giá trị "0" trong cả hai cột
Công thức hàm COUNTIFS có cùng tiêu chí
Cách đếm ô có nhiều điều kiện khác nhau (logic OR)
Như bạn đã thấy trong các ví dụ trên, việc đếm các ô đáp ứng tất cả các điều kiện đã chỉ định là dễ dàng vì hàm COUNTIF nhiều điều kiện được thiết kế để hoạt động theo cách này.
Nhưng nếu bạn muốn đếm các ô mà ít nhất một trong các điều kiện được chỉ định là TRUE , tức là dựa trên logic OR thì sao? Nhìn chung, có hai cách để thực hiện việc này - bằng cách thêm một số công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.
Công thức 1. Thêm hai hoặc nhiều công thức trong COUNTIFS
Ví dụ: để có được số lượng đơn đặt hàng " Đã hủy " và " Đang chờ xử lý" cho " Táo ", hãy sử dụng công thức sau:
=COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đã hủy") + COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đang chờ xử lý")
Công thức đếm ô tính có nhiều điều kiện
Công thức 2. SUMIFS với hằng số mảng
Trong các tình huống khi bạn phải đánh giá rất nhiều điều kiện thì cách trên không phải là cách tốt nhất vì công thức của bạn sẽ quá dài.
Để thực hiện các phép tính tương tự trong một công thức nhỏ gọn hơn, hãy liệt kê tất cả các điều kiện của bạn trong một hằng số mảng và cung cấp mảng đó cho đối số điều kiện của hàm đếm có điều kiện COUNTIFS.
Ta sẽ dùng công thức sau:
=SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",…}))
Trong bảng dưới đây, để đếm ‘Táo” có trạng thái " Đã hủy " hoặc " Đang chờ xử lý " hoặc " Nhập kho ", công thức sẽ như sau:
=SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,{"Đã hủy","Đang chờ xử lý","Nhập kho"}))
SUMIFS với hằng số mảng
Cách đếm số trong khoảng cụ thể
Ngoài những cách dùng hàm đếm có nhiều điều kiện trong excel như bên trên đã chia sẻ, bạn cũng có thể sử dụng hàm COUNTIFS để đếm số trong khoảng thời gian cụ thể. Đối với vấn đề này thì sẽ có 2 công thức cơ bản nhất, cụ thể đó là:
Công thức 1: Hàm COUNTIFS đếm các ô trong khoảng số
Công thức COUNTIFS 2 điều kiện sử dụng để đếm các ô giữa hai số
Để tìm hiểu có bao nhiêu số từ 5 đến 10 (không bao gồm 5 và 10) được chứa trong các ô từ C2 đến C10, hãy sử dụng công thức này:
= COUNTIFS (C2: C10, "> 5", C2: C10, "<10")
Hàm COUNTIFS đếm các ô trong khoảng số
Để đếm các số 5 và 10 trong số đếm, hãy sử dụng công thức "lớn hơn hoặc bằng" và "nhỏ hơn hoặc bằng":
= COUNTIFS (B2: B10, "> = 5", B2: B10, "<= 10").
Công thức COUNTIF để đếm số giữa X và Y
Bạn có thể áp dụng 2 công thức sau:
=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong vùng dữ liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức đếm có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng dữ liệu C2:C10, bao gồm cả 5 và 10.
Trong đó:
Công thức đầu tiên dùng để đếm những số lớn hơn giá trị giới hạn dưới (Ví dụ là 5)
Công thức thứ 2 loại bỏ những cố lớn hơn giá trị giới hạn trên (Ví dụ là 10)
Do vậy kết quả nhận được sẽ nằm trong khoảng giá trị nhận được trên.
>>> Xem thêm: Cách đếm số lần xuất hiện ký tự trong Excel đơn giản và chuẩn nhất
Cách sử dụng COUNTIFS với các ký tự đại diện
Trong các câu thức hàm đếm có nhiều điều kiện trong excel, bạn có thể sử dụng các ký tự đại diện như sau:
? : được sử dụng để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
* " được sử dụng để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.
Ví dụ bạn có danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã ký kết, tức là cột B có chứa tên người. Hãy thêm một điều kiện thứ hai - End Date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,”<>”&””))
Lưu ý, bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ 2 vì cột D chứa kiểu dữ liệu ngày tháng chứ không phải dạng text. Đó là lý do tại sao bạn sử dụng tiêu chí “<>”&”” để tìm ô chứa dữ liệu.
Hàm COUNTIFS với các ký tự đại diện
Cách dùng hàm COUNTIFS theo điều kiện ngày tháng
Hàm countifs có thể giúp bạn làm được rất nhiều việc, tiêu biểu trong đó là việc dùng hàm countifs theo điều kiện ngày tháng để đếm ngày. Có 3 cách để bạn có thể dùng hàm countifs đếm thời gian, tiêu biểu đó là:
Đếm ngày trong một khoảng thời gian cụ thể
Với ví dụ này, bạn có thể sử dụng 2 hàm COUNTIFS nhiều điều kiện và COUNTIF.
Ví dụ, công thức sau được dùng để đếm số ngày trong ô C2-C10 với thời gian từ 1/6/2014 - 7/7/2014:
=COUNTIFS(C2:C9,”=6/1/2014″,C2:C9,”<=6/7/2014″)
=COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)
Công thức đếm ngày trong một khoảng thời gian cụ thể
Đếm ngày với nhiều điều kiện
Để đếm ngày trong các cột khác nhau từ 2 điều kiện trở lên, bạn có thể sử dụng hàm COUNTIFS.
Ví dụ: Tìm ra xem có bao nhiêu sản phẩm được mua sau 20/5 và giao sau 1/6,
Công thức áp dụng như sau: =COUNTIFS(C2:C9,”>5/1/2014″,D2:D9,”6/7/2014″)
Công thức đếm ngày với nhiều điều kiện
Đếm ngày với nhiều điều kiện dựa trên ngày hiện tại
Với trường hợp này, bạn có thể sử dụng hàm COUNTIFS kết hợp với hàm TODAY.
Ví dụ: Dùng hàm COUNTIFS để xác định có bao nhiêu sản phẩm đã được mua nhưng chưa giao hàng:
=COUNTIFS(C2:C9,”<“&TODAY(),D2:D9,”>”&TODAY())
Công thức đếm ngày dựa trên ngày hiện tại
Hàm đếm trong excel có điều kiện COUNTIFS kết hợp SUM trong Excel
Về cơ bản hàm COUNTIFS cũng không khác gì hàm COUNTIF là mấy, chẳng qua cách thức sử dụng của nó có phần phức tạp hơn. Nếu bạn muốn sử dụng hàm COUNTIFS và SUM để đếm có điều kiện trong excel thì bạn thực hiện như sau:
Ví dụ: Lập bảng trong excel chứa thông tin về màu sắc táo và kích thước của chúng. Mục tiêu cuối cùng của ví dụ này đó là đếm số lượng táo đỏ cỡ lớn.
Lập bảng trong excel
Tiếp theo bạn chọn ô muốn hiển thị kết quả của công thức => Nhập công thức =SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big")) => Enter. Khi này, excel đã đếm cho bạn số lượng táo cỡ lớn hoặc táo có màu đỏ.
Kết quả đếm cuối cùng
Cách sửa các lỗi thường gặp khi dùng hàm đếm có điều kiện trong excel
Trong quá trình sử dụng hàm đếm có điều kiện lớn hơn và nhỏ hơn người dùng rất hay gặp lỗi. Sau đây là một số lỗi thường gặp cùng cách khắc phục cho bạn tham khảo, lưu lại ngay nhé.
COUNTIFS không hoạt động khi đếm các giá trị text
Khi đếm các giá trị text, để hoạt động chúng phải được điền vào trong dấu ngoặc kép. Nếu không được chèn, hàm COUNTIFS sẽ không thể đếm được, khi không đếm được chuỗi văn bản sẽ trả về giá trị 0.
Ví dụ dưới đây text không đặt trong dấu ngoặc kép nên kết quả trả về bằng 0
Kết quả trả về bằng 0 do sai công thức
Để khắc phục lại này bạn chỉ cần nhập lại công thức có dấu ngoặc ở text là được. Công thức: =COUNTIFS(E5:E12, "Car")
Công thức đúng khi đếm giá trị text
COUNTIFS không hoạt động vì sai tham chiếu phạm vi
Khi sử dụng hàm đếm có nhiều điều kiện trong excel bạn rất hay gặp lỗi hàm COUNTIFS không hoạt động vì sai tham chiếu phạm vi. Vì vậy khi dùng nhiều hơn một tiêu chí trong hàm COUNTIFS, phạm vi của ô tiêu chí khác phải có cùng số lượng. Nếu không hàm COUNTIFS sẽ lỗi.
Ví dụ: Bạn muốn đếm doanh số bán ô tô ở Austin. Công thức bạn nhập vào bảng đó là =COUNTIFS(E5:E12,"Car",D5:D11,"Austin"). Quan sát kỹ công thức, bạn sẽ thấy phạm vi tiêu chí đầu tiên ở đây là E5:E12 nhưng phạm vi ở tiêu chí thứ hai là D5:D11. Khi số ô trong phạm vi cho tiêu chí không giống nhau thì kết quả sẽ trả về lỗi #VALUE!.
Công thức nhập vào bảng tiêu chí không giống nhau
Cách khắc phục đó là bạn nhập lại công thức thành =COUNTIFS(E5:E12,"Car",D5:D12,"Austin"). Như vậy mới trả về kết quả đúng được.
COUNTIFS không hoạt động vì lỗi trong công thức
Trong quá trình làm việc với COUNTIFS nếu không chèn công thức chính thức thì chắc chắn sẽ bị lỗi hàm không hoạt động. Nếu bạn dùng toán tử học, ví dụ như: dấu lớn hơn (>), nhỏ hơn (<), bằng (=), không bằng (<>), thì cả toán tử và tiêu chí số đều phải được nhập vào bên trong cùng một phương trình.
Ví dụ: Bạn đang muốn tìm doanh số bán hàng lớn hơn 100.000USD, bạn nhập công thức là: =COUNTIFS(F5:F12,">" 100000). Khi này bạn chỉ mới chèn toán tử bên trong phương trình chứ không có tiêu chí số nên kết quả lỗi hiển thị " “There’s a problem with this formula”.
Cách khắc phục đó là bạn nhập công thức: =COUNTIFS(F5:F12,">100000") => Enter. Khi đã nhập cả hai toán tử và tiêu chí bên trong dấu ngoặc thì bạn sẽ nhận được kết quả đúng như mong muốn.
Kết quả trả về khi nhập đúng công thức
Những điều cần lưu ý khi dùng hàm COUNTIFS
Dưới đây là một số điều cần lưu ý khi dùng hàm COUNTIFS, bạn hãy ghi nhớ nhé:
Các vùng chọn bổ sung cần phải có cùng số hàng và cột với vùng criteria_range1. Nếu không, hàm COUNTIFS sẽ trả về kết quả sai.
Điều kiện vùng chọn tham chiếu đến ô trống thì hàm COUNTIFS tự động coi giá trị là 0.
Có thể dùng các ký tự đại diện— dấu hỏi (?) để tìm kiếm các giá trị không xác định. Ví dụ, công thức =COUNTIFS(A2:A10,"A") sẽ đếm tất cả các ô trong phạm vi A2:A10 có chứa chữ "A" ở bất kỳ vị trí nào.
Lưu ý rằng hàm COUNTIFS không phân biệt chữ hoa chữ thường. Ví dụ, công thức =COUNTIFS(A2:A10,"a") sẽ đếm tất cả các ô trong phạm vi A2:A10 có chứa chữ "a" hoặc "A".
>>> Xem thêm: Cách sử dụng hàm countif trong excel cho người mới bắt đầu
Kết luận
Trên đây là kiến thức về hàm đếm có nhiều điều kiện - COUNTIFS trong excel. Hàm đếm có điều kiện là cách thông dụng nhất để bạn thực hiện thao tác đếm trong excel đơn giản, từ đó dễ dàng thống kê và phân tích số liệu một cách hiệu quả, cải thiện tối đa hiệu suất công việc.
Ngoài ra, bạn có thể tham khảo các khóa học Excel online tại Unica với cực kỳ nhiều ưu đãi.
13/02/2019
22655 Lượt xem
.png?v=1727517104)
Cách sử dụng hàm VLOOKUP kết hợp với hàm IF kèm ví dụ
Để tìm kiếm các giá trị thỏa mãn điều kiện của người dùng đặt ra người ta thường sử dụng hàm Hàm Vlookup kết hợp với hàm If trong excel. Sự kết hợp này giúp người dùng tiết kiệm thời gian và nhanh chóng tra cứu dữ liệu theo điều kiện đặt ra. Qua bài viết này, Unica sẽ hướng bạn Cách sử dụng hàm VLOOKUP kết hợp với hàm IF kèm ví dụ minh họa chi tiết. Cùng tìm hiểu ngay.
Hàm IF trong Excel
Trong Excel, hàm If được sử dụng để tìm kiếm các giá trị thỏa mãn điều kiện trong biểu thức logic. Nếu thỏa mãn điều kiện cho trước trong chuỗi đó, hàm sẽ trả về giá trị TRUE, nếu không thì hàm trả về giá trị FALSE.
Cú pháp của hàm IF như sau:
=IF(logical_test,[value_if_true],[value_if_false])
Cú pháp hàm IF trong Excel
Hàm VLOOKUP trong Excel
Trong Excel, hàm VLOOKUP được sử dụng để tìm dò những giá trị theo cột.
Cú pháp của hàm IF như sau:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Cú pháp hàm VLOOKUP trong Excel
Cú phàm hàm VLOOKUP kết hợp với hàm IF
Trong một số trường hợp như dùng để so sánh các giá trị hàm Vlookup trả về so với giá trị mẫu ta sẽ sử dụng đến sự kết hợp giữa hai hàm này. Khi kết hợp hàm VLOOKUP và hàm IF trong Excel sẽ trả về kết quả True/False, Yes/No,...
Cú pháp hàm Vlookup kết hợp hàm If trong Excel có dạng như sau
=IF(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
Lookup_value: Giá trị ô hoặc văn bản mà ta đang tìm kiếm.
Table_array: Phạm vi ô hoặc vùng dữ liệu cần tìm kiếm.
Col_index_number: Số cột mà chúng ta muốn trả về giá trị.
Range_lookup: Tham số này True/False, nhận kết quả khớp chính xác hoặc dữ liệu tương tự.
Cú phàm hàm VLOOKUP kết hợp với hàm IF trong Excel
Cách dùng hàm VLOOKUP kết hợp với hàm IF
Dưới đây là hướng dẫn chi tiết cách dùng hàm VLOOKUP kết hợp với hàm IF trong excel, bạn hãy lưu lại để sử dụng trong từng tình huống cụ thể nhé.
Ví dụ 1: Kết hợp VLOOKUP và hàm IF để trả về giá trị TRUE/FALSE hoặc 1 và 0
Giả sử: Đây là bảng dữ liệu thể hiện danh sách các mặt được liệt kê trong cột A và số lượng sản phẩm ở cột B. Bạn đang cần kiểm tra số lượng một mặt hàng ở cột E1 để thông báo cho người dùng mặt hàng đó còn hay đã hết.
Do đó, bạn sử dụng công thức hàm VLOOKUP như sau:
=VLOOKUP(E1,$A$2:$B$10,2,FALSE)
Hướng dẫn kết hợp VLOOKUP và hàm IF để trả về giá trị TRUE/FALSE hoặc 1 và 0
Sau đó, bạn kết hợp với hàm IF để hệ thống tự động so sánh kết quả hàm Vlookup. Kết quả trả về là 0 và kết quả là “No” nếu giá trị này bằng 9 hoặc “Yes” nếu không phải:
=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,”No”,”Yes”)
Ngoài ra, bạn có thể sử dụng kết quả trả về là TRUE/FALSE hoặc Stock/Sold thay cho kết quả Yes No bằng cú pháp sau đây.
=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,”Sold out”,”In stock”)
Ví dụ 2: Để so sánh giá trị
Giả sử: Nước giải khát cần nhập hàm khi số lượng hàng còn lại nhỏ hơn 10. Như vậy, bạn cần điền trạng thái các loại nước giải khát trong bản sau bằng cách sử dụng hàm VLOOKUP kết hợp hàm IF trong Excel để so sánh giá trị.
Cho bảng dữ liệu sau để so sánh dữ liệu trong Excel
Cú pháp như sau:
= IF(VLOOKUP(E1, $A$2:$B$7;2;0)<10;’Cần nhập hàng’;’Không’)
Với công thức này, bạn dễ dàng truy xuất nhanh trạng thái của hàng hóa, nếu bạn thay đổi giá trị thì trạng thái này sẽ thay đổi tương ứng.
Kết quả thu được sau khi áp dụng hàm
Ví dụ 3: Dùng IF để bẫy lỗi cho hàm VLOOKUP
Trong trường hợp không tìm thấy loại giá trị nào đó trong công thức, bạn có thể bẫy lỗi #N/A bằng cách sử dụng hàm IF.
Giả sử: Mỗi một loại sản phẩm sẽ có những mức thuế khác nhau. Dựa vào bảng phụ, bạn điền các mức thuế tương ứng với các loại sản phẩm. Tuy nhiên, có loại D mới chưa kịp cập nhật vào bảng phụ thì bạn có thể sử dụng hàm VLOOKUP kết hợp với hàm IF để bẫy lỗi #N/A.
Cấu trúc như sau:
=IF(and(B2<$E$3;B2<$E$4;B2<$E$2)=true;';VLOOKUP(B2;$E$2:$F$4;2;0))
Hướng dẫn dùng IF để bẫy lỗi cho hàm VLOOKUP
Như vậy, phương pháp này đã tạo ra điều kiện lọc. Nếu phân loại khác với các giá trị trong bảng phục thì sẽ trả về khoảng trống, còn thuộc bảng phụ thì thực hiện tính toán.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Ví dụ 4: Dùng IF để tùy biến vị trí cột tham chiếu trong hàm VLOOKUP
Hàm VLOOKUP kết hợp với hàm IF có thể giúp bạn thực hiện phép tìm giữa nhiều bảng giá trị. Hàm IF lúc này sẽ đóng vai là Dải ô trong công thức của VLOOKUP.
Giả sử: Bạn cần thống kế sản phẩm của siêu thị A và B trong cùng bảng từ hai bảng dữ liệu được cho sản.
Cho bảng dữ liệu như sau
Để thực hiện, bạn làm những bước như sau:
Tạo bảng dạng Drop List bằng cách chọn một ô > vào thẻ Data.
Ở mục Data chọn Data Validation.
Trong mục Allow, bạn chọn List rồi chọn phạm vi ô.
Hướng dẫn các bước tạo bảng dạng Drop List trong Excel
Lúc này bạn sẽ có kết quả như sau.
Kết quả sau khi tạo bảng xong
Tại vị trí ô H2 bạn sử dụng công thức sau:
=VLOOKUP(G2,IF(H1=A1,$A$3:$B$7,$D$2:$E$7),2,0)
Áp dụng công thức hàm để được kết quả như hình
Ví dụ 5: Xác định đơn giá sản phẩm khi đơn giá có sự thay đổi theo tháng
Để giải quyết bài toán tra cứu đơn giá của sản phẩm thay đổi theo từng thời điểm, ví dụ với bảng dữ liệu có sự thay đổi đơn giá giữa tháng 4 và tháng 5, bạn có thể sử dụng sự kết hợp giữa hàm VLOOKUP và hàm IF.
Cho bảng dữ liệu như sau để xác định đơn giá sản phẩm
Dưới đây là cách thực hiện:
Bước 1: Xác định tháng với hàm MONTH
Trước tiên, bạn cần xác định tháng của từng lần phát sinh đơn giá. Điều này có thể thực hiện bằng hàm MONTH. Hàm này sẽ trả về số tháng từ một ô chứa giá trị thời gian.
Ví dụ:
=MONTH(A2) sẽ trả về kết quả là 4 (tương ứng với tháng 4).
=MONTH(A5) sẽ trả về kết quả là 5 (tương ứng với tháng 5).
Bước 2: Biện luận điều kiện bằng hàm IF
Dựa vào kết quả từ hàm MONTH, chúng ta sử dụng hàm IF để chọn đúng bảng đơn giá tương ứng:
Nếu giá trị tháng là 4, tham chiếu đến bảng đơn giá của tháng 4.
Nếu giá trị tháng là 5, tham chiếu đến bảng đơn giá của tháng
Công thức đơn giản như sau:
=IF(MONTH(A2)=4, VLOOKUP(B2, G3:H7, 2, 0), VLOOKUP(B2, J3:K7, 2, 0))
Bước 3: Thực hiện tra cứu đơn giá bằng hàm VLOOKUP
Sử dụng hàm VLOOKUP để tìm đơn giá tương ứng trong bảng đơn giá:
Với tháng 4: =VLOOKUP(B2, G3:H7, 2, 0)
Với tháng 5: =VLOOKUP(B2, J3:K7, 2, 0)
Trong đó:
B2: Tên sản phẩm cần tra cứu.
G3:H7 và J3:K7: Các bảng đơn giá tương ứng cho tháng 4 và tháng 5.
2: Cột chứa đơn giá.
0: Phương thức tìm kiếm chính xác.
Bước 4: Kết hợp hàm VLOOKUP và IF trong một công thức
Kết hợp các hàm đã xác định để tạo thành một công thức hoàn chỉnh:
=IF(MONTH(A2)=4, VLOOKUP(B2, $G$3:$H$7, 2, 0), VLOOKUP(B2, $J$3:$K$7, 2, 0))
Trong công thức trên, các vùng tham chiếu bảng (G3 và J3) được cố định bằng dấu $ để tránh thay đổi khi sao chép công thức sang các ô khác.
>>> Xem thêm: Hướng dẫn chi tiết 8 cách in file PDF chuẩn xác và đơn giản nhất
Áp dụng công thức hàm ta được kết quả như sau
Các lỗi thường gặp khi hàm IF lồng VLOOKUP
Trong quá trình sử dụng hàm IF kết hợp với hàm VLOOKUP không tránh khỏi có lúc bạn sẽ gặp lỗi. Vậy lỗi đó là gì và cách khắc phục như thế nào, cùng tham khảo nội dung bài viết sau đây để biết câu trả lời.
Lỗi #ERROR!
Đây là lỗi mà công thức hoặc hàm không được đánh giá, tính toán đúng cách. Lỗi này xảy ra khi có những vấn đề với dữ liệu hoặc từ công thức bạn đang sử dụng. Nguyên nhân có thể kể đến như sau:
Đặt sai dấu chấm, dấu phẩy trong khi nhập công thức.
Đây là trường hợp trong quá trình nhập liệu, bạn đã nhập sai công thức định dạng. Lúc này bạn cần kiểm tra lại định dạng công thức, thay đổi toàn bộ dấu.
Cách khắc phục lỗi #ERROR! do nhập sai dấu chấm, dấu phẩy
Nhầm lẫn giữa số và chuỗi trong công thức
Đây là trường hợp bạn không sử dụng dấu ngoặc kép đối với các chuỗi công thức If sẽ xuất hiện lỗi này. Lúc này, bạn hãy kiểm tra lại và điền dấu ngoặc kép vào chuỗi tương ứng.
Cách khắc phục lỗi #ERROR! do nhầm lẫn giữa số và chuỗi trong công thức
Lỗi #N/A
Nguyên nhân xuất hiện lỗi #N/A là do hàm hoặc công thức không tìm thấy giá trị trong phạm vi được chỉ đó. Trong trường hợp kết hợp hàm VLOOKUP và hàm IF thì lỗi #N/A thường xuất hiện ở công thức VLOOKUP.
Lỗi #N/A khi kết hợp hàm VLOOKUP và hàm IF trong Excel
Cách khắc phục lỗi #N/A
Cách 1: Sao chép thông tin trực tiếp từ bảng chính.
Cách khắc phục thứ nhất khi gặp lỗi #N/A
Cách 2: Sử dụng Drop list các giá trị.
Bước 1: Chọn 1 ô bất kỳ trong bảng dữ liệu, trên thanh công cụ chọn Data > vào Data Validation.
Chọn ngẫu nhiên một ô bất kỳ trong bảng dữ liệu
Bước 2: Trong phần Allow chọn List > quét khối A2:A5 để nhập giá trị > chọn OK để hoàn tất.
Hướng dẫn cài đặt để khắc phục lỗi #N/A
Bước 3: Nhập công thức =IF(VLOOKUP(A2,$A$2:$B$5,2,0)=0,'Thưởng','Không') vào ô E2 > lúc này bạn sẽ thấy giá trị Drop List sẽ được cập nhập theo và khắc phục được lỗi #N/A.
Nhập công thức để khắc phục lỗi #N/A
Lỗi #VALUE!
Đây là lỗi mà công thức hoặc hàm yêu cầu một loại dữ liệu cụ thể, nghĩa là bạn cần phải gán định dạng cho dữ liệu bạn cung cấp.
Chẳng hạn, bạn cần thay đổi trạng thái của các mặt hàng. Nếu số lượng hàng hóa nhỏ hơn 10 thì cần điều chỉnh số lượng hàng hiện tại cộng thêm 100. Ngược lại, nếu lớn hơn 10 thì sẽ tự động điền Không nhập hàng.
Nhưng trong công thức sau đây, người sử dụng đã điền nhầm ô A2 (dạng chuỗi) dẫn đến việc không thể thực hiện phép cộng được.
Lỗi #VALUE! trong khi kết hợp hàm If và hàm VLOOKUP
Để khắc phục, bạn cần sử lại công thức VLOOKUP(E1;$A$2:$B$7;2;0) để trích xuất giá trị rồi cộng thêm 100.
Cách khắc phục lỗi #VALUE! trong khi kết hợp hàm If và hàm VLOOKUP
Lỗi #NAME?
Trong quá trình sử dụng hàm VLOOKUP kết hợp với hàm IF, bạn có thể gặp lỗi #NAME? khi bị nhập sai công thức. Trong trường hợp bạn cần khắc phục lại bằng cách kiểm tra lại công thức và sửa lại cho chính xác.
Cách khắc phục lỗi #NAME? trong khi kết hợp hàm If và hàm VLOOKUP
Lưu ý khi sử dụng hàm IF kết hợp VLOOKUP
Khi học cách kết hợp hàm VLOOKUP với hàm IF trong Excel chúng ta cần nên lưu ý một số vấn đề sau đây:
Hãy luôn cố định bảng tham chiếu để đảm bảo kết quả chính xác khi chúng ta copy hoặc di chuyển công thức tính đến ô khác
Đối với những hàm IF kết hợp với hàm VLOOKUP phức tạp bạn hãy dùng từng bước phân tích mệnh đề như ví dụ 3 chúng tôi đã chia sẻ ở trên.
Hàm VLOOKUP có 2 dạng tìm kiếm hoặc là tìm kiếm kết quả tương đối hoặc là tìm kiếm kết quả tuyệt đối. Do vậy, bạn cần phải xác định dạng tìm kiếm để kết quả đảm bảo chuẩn xác nhất.
Hàm VLOOKUP sẽ dò tìm kết quả dữ liệu từ trái qua phải. Do đó, khi xác định cột lấy giá trị tham chiếu, bạn hãy đếm số cột theo thứ tự từ trái qua phải.
>>> Xem thêm: Bộ khóa học tin học văn phòng cơ bản đến nâng cao
Kết luận
Trên đây, Unica đã hướng dẫn cách sử dụng hàm VLOOKUP kết hợp với hàm IF chuẩn một cách cụ thể qua các ví dụ. Hy vọng rằng với ví dụ minh hoạ và lời giải chi tiết về hàm VLOOKUP IF trong excel chắc chắn đã giúp bạn hiểu được công dụng cũng như áp dụng hàm VLOOKUP hiệu quả nhất. Bạn đọc muốn học Excel từ cơ bản đến nâng cao hãy đặt mua ngay sách Excel từ cơ bản đến nâng cao từ giảng viên uy tín tại Unica.
13/02/2019
16449 Lượt xem
Cách thêm số 0 vào đầu giá trị trong excel siêu dễ dàng
Đôi khi nhập số 0 ở đầu ô dữ liệu trong Excel nhưng khi nhấn Enter thì thấy chỉ hiện ra những số sau số 0. Điều này khiến bạn cảm thấy vô cùng khó chịu vì bảng tính trông rất thiếu khoa học. Sau đây, Unica sẽ Hướng dẫn cách thêm số 0 vào đầu giá trị trong excel siêu dễ dàng. Cùng tìm hiểu ngay.
Cách thêm số 0 vào đầu dãy số trong excel
Thêm số 0 bằng sử dụng hàm TEXT
Hàm Text được sử dụng để chuyển đổi một giá trị số thành định dạng Văn bản bằng cách sử dụng các chuỗi định dạng đặc biệt. Đây là cách áp dụng rất phổ biến được dân văn phòng ứng dụng nhiều mỗi khi gặp vấn đề mất số 0 ở đầu số điện thoại, định dạng số điện thoại trong Excel giúp bạn có thể sử dụng dễ dàng hơn.
Công thức hàm TEXT:
=TEXT(value, format_text)
Trong đó:
value: là giá trị mà bạn muốn định dạng.
format_text: là chuỗi ký tự mô tả cách bạn muốn định dạng giá trị.
Ví dụ 1: Nếu bạn muốn thêm số 0 trước số điện thoại 0987654321 và hiển thị 10 chữ số, bạn có thể sử dụng công thức sau:
=TEXT(987654321, “0000000000”)
Kết quả sẽ là 0987654321.
Ví dụ 2: Cho bảng số liệu như sau"
Cho bảng số liệu
Yêu cầu thêm số 0 vào trước các số điện thoại bằng hàm Text.
Bước 1: Ở ô B2 nhập công thức là =TEXT(A2;"0000000000")
Nhập công thức Text
Bước 2: Nhấn enter sẽ thu được kết quả như hình dưới đây
Nhấn enter
Bước 3: Kéo chuột xuống bên dưới để copy công thức, bảng kết quả cuối cùng bạn thu được sẽ như sau:
Kết quả nhận được
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Thêm số 0 bằng công cụ Format Cell
Trên thanh công cụ có tính năng Format Cell có tác dụng định dạng ô trong Excel. Cách này thực hiện khá nhanh chóng để giúp hiển thị số 0 trong số điện thoại.
Cách thêm số 0 trong excel bằng tính năng này như sau:
Bước 1: Nhấn chọn mục Format trên thanh công cụ Home
Ở thanh xổ xuống, chọn lệnh Format Cell.
Chọn lệnh Format Cell
Trong bảng chọn Number, bạn chọn Custom, trong phần Type gõ 10 số 0, tương ứng với tổng số điện thoại để thêm số 0 vào số điện thoại.
Sau đó bạn ấn OK.
Cách thêm số 0 trong excel bằng công cụ Format Cell
Bước 2: Bạn có thể thấy rằng ô đã được định dạng trực tiếp với số 0 ở trước mỗi số. Những thay đổi này đã được thực hiện trong chính cột A mà không cần sang cột khác. Thêm số 0 vào số điện thoại trong excel bằng cách này rất đơn giản đúng không nào, đồng thời cũng giúp cho cách nhập số điện thoại trong excel đơn giản và nhanh chóng hơn.
Kết quả thêm số 0 đằng trước sđt
Thêm số 0 bằng dấu ' trước dãy số
Một cách đơn giản nhất để thêm số 0 trước số điện thoại trong Excel là thêm dấu nháy đơn (‘) đằng trước dãy số.
Ví dụ: Nếu bạn muốn định dạng số điện thoại trong excel là 982167587, bạn chỉ cần gõ ‘982167587 vào ô Excel. Lúc này, Excel sẽ hiểu rằng bạn muốn nhập một chuỗi văn bản, không phải một số, và sẽ giữ nguyên số 0 đầu tiên.
Hình ảnh minh họa
Tuy nhiên, cách này có một hạn chế là bạn không thể sử dụng các hàm tính toán hay lọc dữ liệu với các ô chứa dấu nháy đơn. Nếu bạn muốn làm được điều này, bạn có thể sử dụng các cách khác sau đây.
Thêm số 0 bằng hàm CONCATENTE
Một cách khác để thêm số 0 trước số điện thoại trong Excel là sử dụng hàm CONCATENATE. Hàm CONCATENATE có công thức như sau:
=CONCATENATE(text1, text2, …)
Trong đó:
text1, text2, … là các chuỗi văn bản mà bạn muốn nối lại với nhau.
Ví dụ: Giả sử, bạn muốn thêm số 0 vào trước sdt trong bảng bên dưới bằng cách dùng hàm CONCATENATE.
Bước 1: Trong cột mô tả số điện thoại đầy đủ, tại ô đầu tiên trong cột số điện thoại, bạn nhập hàm =CONCATENATE("0";B6).
Nhập công thức hàm CONCATENATE
Nhấn enter là số điện thoại đã tự động thêm số 0 đằng trước.
Số điện thoại đã được thêm số 0 đằng trước
Bước 2: Click vào biểu tượng dấu + và rê chuột để sao chép công thức xuống các dòng bên dưới.
Nhấn vào biểu tượng dấu cộng bên dưới và kéo xuống các ô phía dưới
Toàn bộ các ô bên dưới sẽ được copy công thức nên sẽ chuyển thành dãy số điện thoại được thêm số 0 như hình dưới đây:
Toàn bộ số điện thoại sẽ được thêm số 0 đằng trước
Bước 3: Sao chép cột số điện thoại đầy đủ bằng cách nhấn chuột phải và chọn "Copy"
Chọn biểu tượng copy
Bước 4: Dán số điện thoại đã sao chép vào cột số điện thoại cũ. Chọn định dạng dán như hình mô tả để không dán cả công thức vào cột số điện thoại cũ.
Chọn định dạng số
Bước 5: Xóa cột số điện thoại chứa công thức hàm.
Xóa cột số điện thoại chứa công thức hàm
Bảng kết quả cuối cùng:
Bảng kết quả cuối cùng
Cách thêm số 0 vào excel này có lợi là bạn có thể thêm bất kỳ ký tự nào vào đầu các ô chứa số điện thoại, không chỉ là số 0. Bạn cũng có thể nối nhiều chuỗi văn bản lại với nhau để tạo ra các định dạng khác nhau.
Lưu ý: Kết quả trả về của hàm CONCATENATE là một chuỗi văn bản, không phải một số. Do đó, bạn không thể sử dụng các hàm tính toán hay lọc dữ liệu với các ô chứa hàm CONCATENATE.
Thêm số 0 bằng thiết lập định dạng "0"#
Bước 1: Chọn ô mà bạn muốn nhập số điện thoại. Sau đó nhấn chuột phải và chọn "Format Cells" (Ctrl +1).
Chọn Format Cells
Bước 2: Ở mục Category, chọn Custom và nhập "0#' vào ô Type sau đó nhấn OK là hoàn thành thêm số 0 vào đầu trong excel.
Nhập "0#' vào ô Type sau đó nhấn OK
Lưu ý: Ký tự # xuất hiện trong định dạng này sẽ biểu thị một chữ số tuỳ ý, còn dấu "0" đặt ở đằng trước có chức năng đảm bảo rằng mỗi chữ số sẽ hiển thị, bao gồm cả số 0 ở đằng trước.
Khi bạn nhập một dãy số ở đây, Excel sẽ hiển thị số 0 trước nó, đồng thời cũng sẽ duy trì chiều dài của số. Như vậy, nó rất thích hợp để định dạng với số điện thoại có độ dài cố định.
Bảng kết quả cuối cùng sau khi thêm số 0
Cách thêm số 0 vào đầu chuỗi văn bản trong excel
Ngoài việc thêm số 0 vào đầu các ô chứa số điện thoại hay mã sản phẩm, bạn cũng có thể muốn thêm số 0 vào đầu các ô chứa chuỗi văn bản. Ví dụ, bạn có một danh sách mã sinh viên gồm 8 ký tự, nhưng có một số mã chỉ gồm 7 ký tự. Bạn muốn thêm số 0 vào đầu các mã này để tạo ra một danh sách đồng nhất.
Để thêm số 0 vào đầu excel, bạn có thể sử dụng hai công thức sau:
Thêm số 0 bằng hàm RIGHT
Công thức hàm Right:
=RIGHT(text, num_chars)
Trong đó:
text: là chuỗi văn bản mà bạn muốn lấy một số ký tự ở cuối.
num_chars: là số ký tự mà bạn muốn lấy ở cuối chuỗi văn bản.
Ví dụ: Cho bảng giá trị sau. Yêu cầu là thêm số 0 đằng trước các dãy số ở ô Giá trị ban đầu.
Với dãy số 123456, ta sẽ áp dụng công thức ở cột B4 là:
B4 = RIGHT("0"&A4;7) = 0123456
Áp dụng công thức thêm số 0 trước số trong excel tương tự ở ô B4 để tính các dãy số bên dưới. Sẽ có bảng kết quả sau:
Kết quả giá trị được chèn thêm số 0
Thêm số 0 hàm REPT và LEN
Ngoài những cách thêm số 0 đã chia sẻ ở trên, bạn cũng có thể sử dụng hàm REPT và LEN để thêm số 0 vào đầu văn bản.
Hàm LEN: hàm đếm số ký tự trong một dãy ký tự hoặc trong một văn bản.
Cú pháp hàm LEN:
=LEN(dãy ký tự hoặc văn bản).
Hàm REPT: hàm lặp lại chuỗi ký tự, từ, số cho trước sao cho dãy kết quả cuối cùng đủ số ký tự được chỉ định.
Cú pháp hàm REPT:
=REPT(văn bản muốn lặp, số lần lặp).
Công thức hàm REPT và LEN:
=REPT(“0”, num_chars - LEN(text))&text
Trong đó:
text: là chuỗi văn bản mà bạn muốn thêm số 0 vào đầu.
num_chars: là số chữ số tối đa mà bạn muốn hiển thị.
Ví dụ: Tại ô A1 đang chứa dãy ký tự là AB01 bao gồm 4 ký tự có lẫn chữ và số. Nhiệm vụ của bạn là thêm 2 số 0 vào trước để thành dãy số 00AB01.
Khi này, để thêm được số 0 bạn sẽ nhập công thức: =REPT(0,6-LEN(A1))&A1
Trong đó:
0: ký tự bạn muốn thêm vào.
6-LEN(A1): chuỗi văn bản bạn muốn thêm số 0. Vì ô A1 đang có sẵn 4 ký tự, bạn muốn thêm 2 ký tự 0 thì nên nhập 6.
&A1: ghép kết quả tính được từ hàm REPT với dãy kí tự của ô A1.
Nhập công thức để thêm số 0 vào đầu văn bản
Cách loại bỏ, ẩn số 0 đứng đầu trong Excel
Ngoài việc chèn số 0 vào số điện thoại trong excel hay mã sản phẩm, bạn cũng có thể muốn loại bỏ các số 0 không cần thiết đứng đầu các ô chứa số hay chuỗi văn bản. Ví dụ, bạn có một danh sách mã sản phẩm gồm 8 ký tự, nhưng có một số mã có các số 0 đứng đầu, như “00012345” hay “00123456”. Bạn muốn loại bỏ các số 0 này để tạo ra một danh sách gọn gàng hơn.
Để làm được điều này, bạn có thể sử dụng ba cách sau:
Bỏ số 0 bằng cách thay đổi định dạng ô
Một cách đơn giản nhất để loại bỏ các số không đứng đầu trong Excel là thay đổi định dạng ô từ Custom (hoặc Tùy chỉnh) sang General (hoặc Chung). Bạn có thể làm theo các bước sau:
Bước 1: Chọn các ô chứa số hay chuỗi giá trị mà bạn muốn loại bỏ các số 0 đứng đầu.
Bôi đen vùng dữ liệu cần xóa số 0
Bước 2: Nhấn chuột phải và chọn Format Cells (hoặc nhấn Ctrl + 1).
Mở hộp thoại Format Cells
Bước 3: Trong hộp thoại Format Cells, chọn tab Number và chọn General (hoặc Chung).
Bước 4: Nhấn OK để xác nhận.
Nhấn OK
Lúc này, Excel sẽ tự động loại bỏ các số 0 đứng đầu các ô chứa số hay chuỗi văn bản. Cách này có lợi là bạn không cần sử dụng bất kỳ công thức nào, chỉ cần thay đổi một lần định dạng ô.
Tuy nhiên, cách này có một hạn chế là nếu bạn muốn lưu lại định dạng ô ban đầu, bạn sẽ phải thay đổi lại sau khi loại bỏ các số 0. Ngoài ra, cách này không áp dụng được cho các ô chứa hàm hay công thức, mà chỉ áp dụng được cho các ô chứa giá trị tĩnh.
Bỏ số 0 bằng cách chuyển sang dạng số
Một cách khác để loại bỏ các số không đứng đầu trong Excel là chuyển các ô chứa số hay chuỗi văn bản sang dạng số. Cách thêm số 0 trong excel này gồm các bước sau:
Bước 1: Chọn các ô chứa số hoặc bảng giá trị mà bạn muốn loại bỏ các số 0 đứng đầu.
Bôi đen vùng dữ liệu cần xóa số 0
Bước 2: Nhấn chuột phải và chọn Copy (hoặc nhấn Ctrl + C).
Copy dữ liệu
Bước 3: Chọn một ô trống nào đó và nhấn chuột phải và chọn Paste Special (hoặc nhấn Ctrl + Alt + V).
Mở hộp thoại Paste Special
Bước 4: Trong hộp thoại Paste Special, chọn Values (hoặc Giá trị) và nhấn OK.
Nhấn chọn OK
Lúc này, Excel sẽ sao chép giá trị của các ô đã chọn sang một vùng mới và tự động loại bỏ các số 0 đứng đầu.
Kết quả
Cách này có lợi là bạn có thể giữ nguyên định dạng ô ban đầu của các ô đã chọn, và chỉ tạo ra một bản sao mới đã loại bỏ các số 0.
Tuy nhiên, cách này cũng có một hạn chế là bạn sẽ phải tạo ra một vùng mới để sao chép giá trị, và không thể sử dụng lại vùng ban đầu. Ngoài ra, cách này không áp dụng được cho các ô chứa hàm hay công thức, mà chỉ áp dụng được cho các ô chứa giá trị tĩnh.
Bỏ số 0 bằng cách sử dụng công thức
Một cách nữa để loại bỏ các số không đứng đầu trong Excel là sử dụng công thức. Bạn có thể sử dụng hai hàm sau:
Hàm VALUE có công thức như sau:
=VALUE(text)
Trong đó:
text: là chuỗi văn bản mà bạn muốn chuyển sang dạng số.
Ví dụ: Loại bỏ số 0 trong bảng giá trị sau bằng cách dùng hàm Value:
Cho bảng giá trị
Bước 1: Nhập hàm Value vào ô C4 như hình:
Nhập hàm value
Bước 2: Nhấn enter sẽ thu được kết quả như sau:
Nhấn enter
Bước 3: Copy công thức xuống các ô bên dưới sẽ có được bảng kết quả như sau:
Copy công thức xuống các ô
Tuy nhiên, cách này cũng có một hạn chế là kết quả trả về của hàm VALUE hoặc NUMBERVALUE là một số, không phải một chuỗi văn bản. Do đó, bạn sẽ mất đi các ký tự khác trong chuỗi văn bản, như dấu gạch ngang (-), dấu chấm (.), dấu phẩy (,), v.v.
Ẩn số 0 đứng đầu trong Excel
Ngoài việc loại bỏ các số 0 đứng đầu trong Excel, bạn cũng có thể muốn ẩn các số 0 không cần thiết trong các ô chứa giá trị bằng 0. Ví dụ, bạn có một bảng tính toán doanh thu của một cửa hàng, nhưng có một số ngày không có doanh thu nào. Bạn muốn ẩn các giá trị 0 này để tạo ra một bảng tính gọn gàng hơn.
Để làm được điều này, bạn có thể ẩn các số 0 trong Excel bằng cách thay đổi tùy chọn hiển thị. Các bước làm như sau:
Bước 1: Nhấn nút File (hoặc Office) trên thanh công cụ của Excel.
Nhấn nút File
Bước 2: Chọn Options (hoặc Excel Options) để mở hộp thoại Excel Options.
Mở hộp thoại Excel Options
Bước 3: Trong hộp thoại Excel Options, chọn Advanced (hoặc Nâng cao) và kéo xuống phần Display options for this worksheet (hoặc Tùy chọn hiển thị cho bảng tính này).
Kéo xuống phần Display options for this worksheet
Bước 4: Bỏ chọn ô Show a zero in cells that have zero value (hoặc Hiển thị số 0 trong các ô có giá trị bằng 0).
Bỏ chọn ô Show a zero in cells that have zero value
Bước 5: Nhấn OK để xác nhận.
Nhấn OK
Lúc này, Excel sẽ tự động ẩn các số 0 trong các ô chứa giá trị bằng 0. Cách này có lợi là bạn không cần sử dụng bất kỳ công thức nào, chỉ cần thay đổi một lần tùy chọn hiển thị của Excel.
Tuy nhiên, cách này có một hạn chế là nó chỉ áp dụng cho một bảng tính cụ thể, không áp dụng cho toàn bộ workbook. Nếu bạn muốn áp dụng cho toàn bộ workbook, bạn sẽ phải lặp lại các bước trên cho mỗi bảng tính. Ngoài ra, cách này không thay đổi giá trị của các ô chứa số 0, mà chỉ thay đổi cách hiển thị của chúng.
Kết luận
Như vậy, Unica đã hướng dẫn cách thêm số 0 trong Excel đơn giản và hiệu quả, cũng như cách loại bỏ hoặc ẩn các số 0 không mong muốn. Bạn có thể sử dụng các cách này tùy theo mục đích và nhu cầu của bạn. Hy vọng bài viết này sẽ giúp bạn sử dụng Excel một cách hiệu quả hơn.
Bên cạnh đó Unica còn mang đến bạn đọc những kiến thức về các khoá học Excel từ cơ bản đến nâng cao đang chờ bạn khám phá.
>>> Xem thêm:
3 cách bỏ dấu phẩy trước số trong excel đơn giản và hiệu quả
Hướng dẫn định dạng dữ liệu trong excel với Format Cells
13/02/2019
29634 Lượt xem
Cách sử dụng hàm TEXT trong Excel, kèm ví dụ minh họa
Hàm TEXT là một hàm rất hữu ích trong Excel, cho phép bạn định dạng các giá trị số hoặc ngày thành các định dạng văn bản mong muốn. Bài viết này sẽ giới thiệu cho bạn về hàm TEXT, công thức, các loại mã định dạng và cách sử dụng hàm TEXT trong các trường hợp thực tế. Nếu bạn đang tìm hiểu sâu hơn về cách sử dụng hàm text trong excel, hãy cùng Unica theo dõi bài viết dưới đây.
Hàm TEXT là gì? Ứng dụng của hàm TEXT trong Excel
Hàm TEXT thuộc nhóm hàm Văn bản trong Excel, có chức năng chuyển đổi một giá trị số hoặc ngày thành một chuỗi văn bản theo một định dạng cụ thể. Hàm TEXT có thể được sử dụng để:
Kết nối văn bản và số hoặc ngày trong một ô hoặc công thức.
Thêm số 0 vào đầu số điện thoại hoặc mỗi dãy số để tạo mã số.
Hàm text excel định dạng số bên trong chuỗi văn bản, ví dụ như định dạng tiền tệ, phần trăm, phân số, số thập phân,...
Chuyển giá trị thành số điện thoại theo định dạng mong muốn.
Hàm TEXT thuộc nhóm hàm Văn bản trong Excel
Công thức hàm TEXT
Muốn sử dụng hàm text trong excel, bạn cần biết công thức hàm này. Công thức text trong excel cụ thể như sau:
=TEXT(value;format_text)
Trong đó:
Value: là giá trị số hoặc ngày cần được định dạng thành văn bản.
Format_text: là mã định dạng văn bản mong muốn, có thể được nhập trực tiếp hoặc tham chiếu từ một ô khác.
Công thức cơ bản của hàm TEXT
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
[blog_custom:2]
[trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]]
Các loại mã định dạng hàm TEXT
Mã định dạng văn bản của lệnh text trong excel có thể được tạo từ các ký tự sau:
0: hiển thị số 0 nếu không có chữ số nào ở vị trí đó.
#: hiển thị chữ số nếu có chữ số ở vị trí đó, nếu không thì bỏ qua.
?: hiển thị chữ số nếu có chữ số ở vị trí đó, nếu không thì hiển thị một khoảng trắng.
.: hiển thị dấu phẩy để ngăn cách phần nguyên và phần thập phân.
,: hiển thị dấu phẩy để ngăn cách hàng nghìn.
%: nhân giá trị với 100 và hiển thị kèm theo ký hiệu phần trăm.
E+ E- e+ e-: hiển thị giá trị theo định dạng số mũ.
$ - + / ( ) space: hiển thị các ký tự này như là một phần của chuỗi văn bản.
": hiển thị các ký tự trong dấu ngoặc kép như là một phần của chuỗi văn bản.
: hiển thị ký tự sau dấu gạch chéo như là một phần của chuỗi văn bản.
@: hiển thị văn bản trong ô.
Các loại mã định dạng hàm TEXT
Ngoài ra, bạn cũng có thể sử dụng các mã sau để định dạng ngày và giờ:
m: hiển thị số tháng từ 1 đến 12.
mm: hiển thị số tháng từ 01 đến 12.
mmm: hiển thị tên tháng viết tắt từ Thg1 đến Thg12.
mmmm: hiển thị tên tháng đầy đủ từ Tháng Một đến Tháng Mười Hai.
mmmmm: hiển thị chữ cái đầu tiên của tên tháng từ T đến C.
d: hiển thị số ngày từ 1 đến 31.
dd: hiển thị số ngày từ 01 đến 31.
ddd: hiển thị tên ngày viết tắt từ T2 đến CN.
dddd: hiển thị tên ngày đầy đủ từ Thứ Hai đến Chủ Nhật.
yy: hiển thị hai chữ số cuối của năm, ví dụ 23 cho năm 2023.
yyyy: hiển thị bốn chữ số của năm, ví dụ 2023 cho năm 2023.
h: hiển thị số giờ từ 0 đến 23.
hh: hiển thị số giờ từ 00 đến 23.
m: hiển thị số phút từ 0 đến 59.
mm: hiển thị số phút từ 00 đến 59.
s: hiển thị số giây từ 0 đến 59.
ss: hiển thị số giây từ 00 đến 59.
AM/PM A/P am/pm a/p: hiển thị ký hiệu buổi sáng hoặc chiều theo định dạng 12 giờ.
Cách dùng hàm text trong excel
Dưới đây là một số ví dụ minh họa cách sử dụng hàm TEXT trong Excel.
Kết nối văn bản và số hoặc ngày
Bạn có thể sử dụng hàm TEXT để kết nối văn bản và số hoặc ngày trong một ô hoặc công thức.
Ví dụ: Cho bảng dữ liệu 1:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm Text để chèn định dạng số bên trong một chuỗi văn bản trong bảng dữ liệu đã cho.
Cách sử dụng hàm text trong excel:
Bước 1: Nhập công thức hàm Text sau vào ô I3:
="Thành tiền: "&TEXT(F3*E3*(1-G3);"$###;###.00").
Nhập công thức hàm Text
Bước 2: Nhấn Enter, Excel sẽ trả về kết quả là Thành tiền: $24.
Kết quả sau khi nhập công thức
Bước 3: Copy công thức xuống bên dưới sẽ thu được bảng kết quả như sau:
Copy công thức xuống bên dưới
Thêm số 0 vào đầu mỗi dãy số
Bạn có thể sử dụng hàm TEXT để thêm số 0 vào đầu mỗi dãy số để tạo mã số.
Ví dụ: Cho bảng dữ liệu 2:
Cho bảng dữ liệu
Yêu cầu: Thêm số 0 vào đầu mỗi số điện thoại đã cho trong bảng.
Cách sử dụng hàm định dạng text trong excel:
Bước 1: Ở ô G15, bạn nhập công thức sau:
="0"&TEXT(F15;"## ### ###").
Nhập công thức tính
Bước 2: Nhấn Enter, Excel sẽ trả về kết quả là 034 259 541.
Kết quả sau khi nhập công thức tính
Bước 3: Kéo thả chuột xuống bên dưới, bạn sẽ thu được bảng kết quả như sau:
Bảng kết quả thu được
Lưu ý: Bạn cần sử dụng dấu 0 để chỉ định số chữ số mong muốn trong mã định dạng văn bản của hàm TEXT.
Chuyển giá trị thành số điện thoại theo định dạng
Trong quá trình sử dụng hàm Text, bạn hoàn toàn có thể sử dụng dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng để tạo thành những chuỗi văn bản có định dạng phù hợp với mục đích sử dụng của bản thân.
Ví dụ: Cho bảng ví dụ 3:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm Text để thay đổi định dạng của các số điện thoại.
Cách sử dụng hàm text trong excel:
Bước 1: Nhập công thức dưới đây vào ô G27:
=TEXT(F27;"[<=9999999]##-###;(###) ##-###").
Nhập công thức tính
Bước 2: Nhấn Enter, bạn sẽ thu được kết quả là (342) 59-541.
Kết quả sau khi nhập công thức
Bước 3: Copy công thức xuống các hàng bên dưới, Excel sẽ trả về bảng kết quả như sau:
Copy công thức xuống các hàng bên dưới
Lưu ý: Bạn cần sử dụng các ký tự 0 và các ký tự khác để chỉ định các định dạng số điện thoại mong muốn trong mã định dạng văn bản của hàm TEXT.
Lỗi hàm TEXT
Hàm TEXT có thể gặp một số lỗi khi sử dụng, ví dụ như:
Lỗi #NAME?
Lỗi #NAME? xảy ra khi Excel không nhận ra tên của hàm hoặc mã định dạng văn bản. Để khắc phục lỗi này, bạn cần kiểm tra lại chính tả của hàm và mã định dạng văn bản và đảm bảo rằng chúng được nhập chính xác.
Lỗi #NAME? xảy ra khi Excel không nhận ra tên của hàm hoặc mã định dạng văn bản
Lỗi cú pháp
Lỗi cú pháp xảy ra khi công thức của hàm TEXT bị thiếu hoặc sai tham số, dấu ngoặc, dấu phẩy,... Để khắc phục lỗi này, bạn cần kiểm tra lại công thức của hàm TEXT và đảm bảo rằng nó tuân theo cú pháp đúng.
Tổng kết
Hàm TEXT là một hàm rất hữu ích trong Excel, cho phép bạn định dạng các giá trị số hoặc ngày thành các định dạng văn bản mong muốn. Bạn có thể sử dụng hàm text trong excel để kết nối văn bản và số hoặc ngày, thêm số 0 vào đầu mỗi dãy số, định dạng số bên trong chuỗi văn bản, chuyển giá trị thành số điện thoại theo định dạng,... Bạn cần nhớ công thức, các loại mã định dạng và cách khắc phục các lỗi của hàm TEXT để sử dụng hàm này hiệu quả. Để biết được thêm nhiều kiến thức hữu ích để áp dụng hơn vào trong công việc mời bạn đọc tham khảo nhiều hơn các khoá học Excel trên Unica.
Chúc các bạn thành công!
>>> Xem thêm: 4 hàm nối chuỗi trong Excel cực hay và hữu ích nhất
>>> Xem thêm: Cách chuyển ngày tháng năm sang dạng text bằng các hàm cơ bản
13/02/2019
14373 Lượt xem
