Bạn đang xem bài viết Bài Tập Mẫu Hàm If Kết Hợp Left, Right, Mid Trong Excel (Có Lời Giải) được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bài này còn có ví dụ cho việc sử dụng tính năng ADVANCED FILTER để trích lọc dữ liệu từ một bảng cho trước. Phần này sẽ được tách ra trong bài kế tiếp.
Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ tin học văn phòng hiệu quả.
Ở cuối bài, có file Excel XLS chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.
Lý thuyết các bạn có thể xem bài lý thuyết và cách dùng các hàm trong excel.
Bảng dữ liệu bài tập
Câu 1. Điền cột Mã Xuất Nhập Khẩu. Cột này nhận 2 ký tự đầu của cột Mã Số.
Câu 2. Điền cột Tên Sản Phẩm. Biết nếu ký tự thứ ba bằng “G” là Gạo; “D” là Đường; “T” là Than.
Câu 3. Điền cột Số Hiệu. Số hiệu nhận 3 kí tự cuối của cột Mã Số.
Câu 4. Trích lọc những hàng hóa có Mã Số Hiệu là 003.
Câu 1. Điền cột Mã Xuất Nhập Khẩu. Cột này nhận 2 ký tự đầu của cột Mã Số
Theo yêu cầu, để tìm được Mã Xuất Nhập Khẩu ta chỉ cần trích 2 kí tự đầu của chuỗi Mã Số (Cột B). Tức ta dùng hàm LEFT.
Tại ô C4, gõ công thức:
Thủ thuật về cách kéo để copy công thức
Thủ thuật về cách kéo để copy công thức
Câu 2. Điền cột Tên Sản Phẩm. Biết nếu ký tự thứ ba bằng “G” là Gạo; “D” là Đường; “T” là Than.
Tên sản phẩm phụ thuộc vào điều kiện của kí tự thứ 3 trong cột Mã số. Như vậy ở đây ta phải dùng hàm IF kết hợp với hàm lấy ký tự thứ 3 trong một chuỗi. Ta dùng hàm MID để lấy ký tự bất kì trong chuỗi.
Ví dụ lấy ký tự thứ 3 trong chuỗi Mã số, ta dùng như sau:
= MID(…Nội dung chỉ dành cho thành viên… )
Nghĩa là, Từ chuỗi B4, trích ra một chuỗi, bắt đầu tứ vị trí thứ 3, lấy một ký tự. Tức ta sẽ được giá trị là “G”
Trở lại yêu cầu bài toán, dựa vào kí tự thứ 3, nếu nó là “G” thì Tên Sản Phẩm là “Gạo”, nếu là “D” thì Tên Sản Phẩm là “Đường”, Nếu là “T” thì Tên Sản Phẩm là “Than”.
Như vậy, tại ô D4, ta gõ công thức sau:
= IF(MID(B4,3,…Nội dung chỉ dành cho thành viên… =”T” , “Than”)))
Lý giải chung cho công thức trên như sau: Nếu kí tự thứ 3 của Mã số là “G” thì hiện ra kết quả là …Nội dung chỉ dành cho thành viên…
Câu 3. Điền cột Số Hiệu. Số hiệu nhận 3 kí tự cuối của cột Mã Số.
Số hiệu nhận 3 kí tự cuối của cột Mã Số tức ta sẽ lấy 3 ký tự từ phía phải qua. Dùng hàm RIGHT.
Tại ô E4, gõ công thức sau:
= RIGHT(B4,3)
Câu 4. Trích lọc những hàng hóa có Mã Số Hiệu là 003
Cập nhật: 15/5: Lời giải trích lọc cho câu 4, mời các bạn xem video này https://www.youtube.com/watch?v=P7zEp3VDdCE (Nhớ chọn chế độ HD và phóng lớn để xem cũng như đừng quên chia sẻ lên FB cho bạn bè.)
Tải file XLS lời giải
http://hoc.trungtamtinhoc.edu.vn/download/IF-LEFT-RIGHT.zip
Password: …Nội dung chỉ dành cho thành viên…
Trần Triệu Phú – chúng tôi
Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.
Bài Tập Mẫu Hàm Countif Và Countifs Trong Excel (Có Lời Giải)
Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ A tin học văn phòng hiệu quả.
Ở cuối bài, có file Excel XLSX chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.
Bảng dữ liệu bài tập
Yêu cầu tính toán
Lập công thức excel để đếm:
Số nhân viên phòng Kinh Doanh;
Số Nhân viên có nghỉ phép trong tháng;
Số nhân viên họ Trần;
Số Nhân viên phòng kinh doanh không nghỉ phép;
Số Nhân viên phòng kinh doanh nghỉ phép trên 5 ngày.
Khi đề bài yêu cầu đếm số lượng các giá trị nào đó, ta nghĩ ngay đến bộ hàm COUNT.
Nếu đề bài yêu cầu đếm có 1 điều kiện, thì hàm phải dùng là COUNTIF
Nếu đề bài yêu cầu đếm có nhiều điều kiện, thì hàm phải dùng là COUNTIFS
Bây giờ ta bắt đầu ứng dụng hàm COUNTIF và COUNTIFS để tính theo yêu cầu của bài tập Excel này.
Số nhân viên phòng Kinh Doanh:
Yêu cầu đếm có 1 điều kiện là phòng “Kinh doanh”, do đó ta dùng hàm COUNTIF
Tại ô E31, ta gõ hàm như sau:
= COUNTIF(B6:B29,”Kinh doanh”)
Enter sẽ cho kết quả
Số Nhân viên có nghỉ phép trong tháng:
Yêu cầu đếm có 1 điều kiện là có nghỉ phép, tức cột “Số ngày nghỉ” khác 0, do đó ta dùng hàm COUNTIF
Tại ô E32, ta gõ hàm như sau:
Enter sẽ cho kết quả.
Số nhân viên họ Trần:
Yêu cầu đếm có 1 điều kiện là nhận viênc ó họ” Trần”, tức cột “Tên” bắt đầu bằng chữ “Trần”. Do đó ta dùng hàm COUNTIF
Tại ô E33, ta gõ hàm như sau:
Enter sẽ cho kết quả.
Tại ô E35, ta gõ hàm như sau:
= COUNTIFS(B6:B29,”Kinh doanh”,C6:C29,)
Tại ô E36, ta gõ hàm như sau:
Số Nhân viên phòng kinh doanh không nghỉ phép:
Yêu cầu đếm có 2 điều kiện là “Phòng” “Kinh doanh” và “Số ngày nghỉ” là 0, do đó ta dùng hàm COUNTIFS
Tại ô E37, ta gõ hàm như sau:
Tại ô E38, ta gõ hàm như sau:
Video hướng dẫn
Chú ý chọn chế độ xem HD rồi phóng to màn hình để nhìn rõ chữ.
Tải file .XLSX giải đề
Trần Triệu Phú
Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.
Hướng Dẫn Giải Bài Tập Dùng Hàm Index Kết Hợp Hàm Match Có File Mẫu
Bạn có biết hàm INDEX và MATCH là những hàm tìm kiếm rất hiệu quả và hữu ích, tuy nhiên do phải kết hợp 2 hàm này nên nhiều người thấy khó. Để hiểu rõ hơn cách sử dụng hai hàm này, Gitiho sẽ hướng dẫn các bạn cách sử dụng hàm INDEX, hàm MATCH qua một số bài tập ứng dụng. Các bạn có thể tải file bài tập này ở cuối bài viết.
Cách dùng hàm MATCH để dò tìm trong Excel
Ví dụ chúng ta có bảng tính sau:
Trong bảng này chúng ta có thông tin về họ tên, số điện thoại, Email, khu vực làm việc của 6 nhân viên. Nếu muốn biết thông tin của nhân viên “Lê Thị Thủy” thì bạn phải làm thế nào? Chắc hẳn việc đầu tiên bạn cần xác định xem người đó nằm ở vị trí thứ mấy trong bảng tính này.
Khi đó bạn có thể sử dụng hàm MATCH như sau:
Cú pháp hàm MATCH gồm 3 phần:
lookup_value: giá trị làm căn cứ tìm kiếm (tìm theo cái gì); ở đây là tên Lê Thị Thủy
lookup_array: là nơi dò tìm (bạn sẽ tìm ở đâu); ở đây là cột Họ tên
match_type: (Tham số này không bắt buộc): phương pháp tìm kiếm (tìm đúng theo giá trị hay tìm theo khoảng giá trị); ở đây tìm chính xác theo tên.
Do đó chúng ta có thể viết hàm MATCH như sau:
=MATCH(Tên cần tìm, cột Họ tên, tìm chính xác theo tên)
Tại ô D12 nhập tên Lê Thị Thủy
cột Họ tên là vùng C3:C8
Như vậy hàm MATCH cho kết quả là số 3, tương ứng với dòng thứ 3 trong bảng dữ liệu trên.
Hàm INDEX kết hợp với MATCH để tìm kiếm trong Excel
Khi bạn đã có vị trí là dòng thứ 3 trong bảng, bạn có thể lấy được bất kỳ thông tin nào ở dòng này nếu xác định thêm được cột chứa nội dung cần tìm. Khi đó bạn có thể kết hợp hàm INDEX với MATCH theo cách:
Hàm INDEX chỉ định cột chứa kết quả cần tìm
Hàm MATCH chỉ định dòng cần tìm
Khi đó giao điểm giữa cột và dòng này chính là vị trí kết quả cần tìm
Tìm Email của nhân viên Lê Thị Thủy
Trong công thức tại ô D15 tìm Email, chúng ta thấy:
=INDEX(E3:E8,MATCH(D12,C3:C8,0))
Công thức này, hàm INDEX có tác dụng:
Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8
Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH trong ví dụ trước.
Kết quả là giao điểm của cột E với dòng 3, cho ra vị trí ô E3 là ô chứa giá trị cần tìm.
Phương pháp tìm kiếm từ phải qua trái với hàm INDEX+MATCH
Thông thường để tìm kiếm trong Excel chúng ta thường dùng hàm VLOOKUP. Nhưng hàm này có nhược điểm là không thể tìm được theo chiều từ phải qua trái. Nhưng bạn có thể thực hiện điều này rất dễ dàng với hàm INDEX kết hợp hàm MATCH. Hãy tìm hiểu ví dụ sau: Tìm tên dựa vào thông tin Email, biết cột Email ở bên phải cột Họ tên.
Cách làm như sau:
Viết hàm MATCH để tìm vị trí dòng chứa thông tin Email
=MATCH(tên Email, vùng chứa Email, 0)
Kết hợp với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên (cột C), vùng C3:C8
=INDEX(C3:C8,MATCH(D12,E3:E8,0))
Như vậy về cách viết hàm không có gì thay đổi, nhưng phương pháp tìm kiếm có thể cho phép tìm từ phải qua trái, từ dưới lên trên một cách dễ dàng.
Bài tập tìm đơn giá của mặt hàng theo nhiều điều kiện
Trong trường hợp giá trị tìm kiếm của bạn thay đổi trên cả dòng và cột trong 1 bảng dữ liệu, bạn vẫn có thể sử dụng hàm INDEX MATCH để tìm kiếm được. Hãy xem ví dụ về tìm kiếm đơn giá của 1 sản phẩm khi thay đổi cả về mã sản phẩm và mã công ty:
Trong ví dụ này, chúng ta có Mã hàng và Mã công ty là các điều kiện cần tìm. Mã này có thể thay đổi:
Mã hàng thay đổi trong vùng B3:E3
Mã công ty có thể thay đổi trong vùng A4:A7
Với bất kỳ mã nào, bạn cũng phải xác định được đơn giá phù hợp.
Các bước thực hiện như sau:
Bước 1: Xác định vị trí Mã hàng nằm trên cột thứ mấy trong bảng
Bạn sẽ dùng hàm MATCH để tìm Mã hàng (ô H2) trong vùng chứa tên mã hàng (A3:E3) để xem mã hàng cần tìm nằm ở cột thứ mấy trong bảng
=MATCH(H2,A3:E3,0)
Chúng ta thấy hàm MATCH cho kết quả là số 2, tương ứng mã hàng này ở cột thứ 2 trong bảng.
Bước 2: Xác định vị trí Mã công ty nằm trên dòng thứ mấy trong bảng
Bạn sẽ dùng hàm MATCH để tìm Mã công ty (ô H3) trong vùng chứa tên công ty (A3:A7) để xem mã công ty cần tìm nằm ở dòng thứ mấy trong bảng:
=MATCH(H3,A3:A7,0)
Kết quả hàm MATCH trong trường hợp này cho là số 3, tương ứng với vị trí dòng thứ 3 trong bảng.
Như vậy bạn đã có cả 2 vị trí: số dòng và số cột trong bảng đơn giá, chỉ cần xác định giao điểm của dòng và cột này là có thể cho ra kết quả đơn giá cần tìm. Để làm việc này chúng ta sẽ sử dụng hàm INDEX.
Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX
Khi viết hàm INDEX, bạn cần chỉ ra 3 vị trí:
Bảng cần tìm: bảng A3:E7
Dòng cần tìm: hàm MATCH tìm theo mã công ty
Cột cần tìm: hàm MATCH tìm theo mã hàng
Khi đó chúng ta có hàm INDEX như sau:
=INDEX(A3:E7,I3,I2)
Trong đó:
ô I3 là ô chứa kết quả hàm MATCH tìm theo mã công ty
ô I2 là ô chứa kết quả hàm MATCH tìm theo mã hàng
Nếu viết 1 cách tổng quát, chúng ta có:
=INDEX(A3:E7, MATCH(H3,A3:A7,0), MATCH(H2,A3:E3,0))
Trong trường hợp này chúng ta viết hàm INDEX gồm 3 tham số, khác với 2 ví dụ trước chỉ có 2 tham số. Bởi vì:
Khi vùng tìm kiếm của hàm INDEX chỉ có duy nhất 1 cột thì bạn không cần viết tham số thứ 3 (là cột cần tìm).
Khi vùng tìm kiếm của hàm INDEX có nhiều hơn 1 cột thì bạn bắt buộc phải viết tham số thứ 3 để chỉ có tìm kết quả ở cột nào.
Như vậy chúng ta đã có thể hình dung được cách sử dụng hàm INDEX kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, tìm kiếm trong Excel thông qua 4 ví dụ tiêu biểu rồi. Một số kết luận có thể rút ra là:
Hàm MATCH dùng để tìm ra vị trí số dòng, số cột
Hàm INDEX có 2 cách viết: Viết rút gọn nếu chỉ xác định trên 1 cột (hoặc 1 hàng), viết đầy đủ nếu tìm trên 1 vùng gồm nhiều dòng, nhiều cột. Khi viết đầy đủ thì cần tới 2 hàm MATCH
Hàm INDEX+MATCH thay thế được cho hàm VLOOKUP, HLOOKUP trong mọi trường hợp tìm kiếm, và phương pháp tìm kiếm không bị hạn chế.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.
Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel Phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp
Tải về file mẫu trong bài viết
Bạn có thể tải về file mẫu sử dụng trong bài viết tại địa chỉ bên dưới:
Tài liệu kèm theo bài viết
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hàm Left Trong Excel Là Gì? Cách Kết Hợp Hàm Left Và Right
Hàm trích xuất ký tự bên trái trong Excel là gì?
#1. Tổng quan về hàm LEFT
Trong công việc hàng ngày đối với dân văn phòng, Excel là một công cụ không thể thiếu. Một trong những hàm Excel cơ bản mà người dùng thường xuyên làm việc với Excel cần nắm rõ đó là hàm LEFT. Hàm LEFT được sử dụng để cắt chuỗi ký tự bên trái chuỗi văn bản. Ngoài ra, hàm này có thể kết hợp với những hàm khác trong Excel để xử lý những bảng tính phức tạp, đòi hỏi nhiều thao tác nếu như sử dụng từng hàm riêng lẻ. Vậy hàm LEFT trong Excel là gì?
Hàm LEFT trong Excel được sử dụng dùng để trích ra “n” ký tự bên trái một chuỗi Text cho trước với tham số n là số ký tự cần trích ra từ chuỗi ký tự đó. Tham số này có thể có hoặc không, nếu không có tham số thì Excel hiểu mặc định giá trị là 1.
#2. Cú pháp và cách sử dụng hàm LEFT
#2.1. Cú pháp cơ bản
Cú pháp của hàm này như sau:
=LEFT(text,[num_chars])
Trong công thức này, các tham số:
– Text (Bắt buộc): Chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất.
– Num_chars (Tùy chọn): Chỉ rõ số ký tự mà bạn muốn hàm LEFT trích xuất:
+ Num_chars phải lớn hơn hoặc bằng không;
+ Nếu num_chars lớn hơn độ dài của văn bản, hàm LEFT trả về toàn bộ văn bản;
+ Nếu num_chars được bỏ qua, thì nó được giả định là 1.
Bước 1: Tại ô G8, chúng ta viết công thức:
Bước 2: Bấm phím Enter, kết quả trả về là “Phạm”.
Như vậy, chúng ta đã trích được 4 ký tự bên trái của 1 chuỗi văn bản cho trước.
Một cách khác, chúng ta có thể trích xuất số lượng ký tự của 1 chuỗi văn bản bất kỳ bằng cách viết công thức như sau:
=LEFT(“chuỗi văn bản”;[số lượng ký tự cần trích xuất])
Ví dụ: Cần trích xuất 6 ký tự bên trái của chuỗi văn bản “Nguyễn Thành Nam”. Các bạn làm như sau:
Tại 1 ô bất kỳ trên Excel, các bạn viết công thức: =LEFT(“Nguyễn Thành Nam”;6) và bấm phím Enter
Kết quả trả về là “Nguyễn”.
#2.1. Kết hợp hàm LEFT với một số hàm thông dụng
Bước 1: Sử dụng hàm RIGHT để chọn số cuối cùng trong mã số nhân viên. Sau đó dùng LEFT để trích xuất số ký tự bằng kết quả của hàm RIGHT.
Bước 2: Bấm phím Enter và ra kết quả:
Vậy, các bạn có thể sử dụng cách kết hợp này trong một số trường hợp khác nhằm đáp ứng yêu cầu công việc.
Để giải quyết tính huống này, ta cần kết hợp với hàm IF để lọc những nhân viên có giới tính là nam. Sau đó mới lấy 10 ký tự bên trái của họ và tên. Các thao tác xử lý như sau:
Bước 1: Tại ô G8, các bạn viết công thức sau:
==IF(E8=”Nam”;LEFT(C8;10);””””)
Giải thích công thức: Ta dùng hàm IF với điều kiện là “Nam”; nếu những người nào có giới tính là nam thì lúc đó công thức LEFT sẽ trích xuất 10 ký tự bên trái trong họ và tên của họ. Nếu không thỏa mãn điều kiện hàm IF thì trả về giá trị là khoảng trống
Bước 2: Sau khi bấm phím Enter, kết quả trả về sẽ như sau:
Vậy, nhân viên “Phạm Thành” có giới tính là nam, thỏa mãn điều kiện hàm IF. 10 ký tự bên trái của tên nhân viên là “Phạm Thành”.
Bước 1: Tại ô G8 các bạn viết công thức như sau:
=LEFT(C8;(LEN(B8)+LEN(E8)))
Giải thích: Chúng ta sẽ dùng hàm LEN để tìm ra số ký tự cần trích xuất trước sau đó dùng thêm LEFT để thực hiện trích xuất.
Bước 2: Kết quả của công thức sẽ là:
Vậy, các bạn đã hiểu cách kết hợp hàm này với hàm LEN chưa nào?
d. Kết hợp với hàm SEARCH
Tình huống: Trích xuất trong tên nhân viên, số ký tự ở bên trái bằng số thứ tự của ký tự cuối cùng trong mã của nhân viên.
Bước 1: Sử dụng hàm SEARCH để tìm số thứ tự cuối cùng của mã nhân viên. Sau đó, sử dụng thêm công thức LEFT để trích xuất số ký tự bằng kết quả của hàm SEARCH.
Bước 2: Kết quả như sau:
Hỏi: Hàm LEFT dùng để làm gì?
Trả lời: Hàm này dùng để trích xuất bên trái của 1 chuỗi văn bản với 1 số lượng ký tự tùy chọn
Hỏi: Cách kết hợp hàm LEFT và RIGHT trong Excel
Hỏi: Tải file hướng dẫn công thức hàm LEFT ở đâu?
Cập nhật thông tin chi tiết về Bài Tập Mẫu Hàm If Kết Hợp Left, Right, Mid Trong Excel (Có Lời Giải) trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!