Bạn đang xem bài viết Hàm If Kết Hợp Hàm Mid Trong Excel L Cách Sử Dụng Hàm Mid Kết Hợp If được cập nhật mới nhất tháng 9 năm 2023 trên website Utly.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.
Hàm IF kết hợp hàm MID trong excel 2003, 2007 và các phiên bản trở về sau gồm: Excel 2010, 2013 đều không có gì khác biệt cả. Mấu chốt ở đây là việc vận dụng hàm MID để lấy giá trị làm điều kiện cho hàm IF.
HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF KẾT HỢP HÀM MIDVí dụ sau đây: Sẽ hướng dẫn bạn cách sử dụng khi kết hợp 2 hàm:
Bảng trên là Bảng kê / Sổ theo dõi tình hình cho thuê băng đĩa tại một cửa hàng cho thuê băng đĩa. Một ngày có thể phát sinh nhiều lần cho thuê băng đĩa với nhiều thể loại và mức giá khác nhau.
PHÂN TÍCH DỮ LIỆU
Trong phần bảng kê/ sổ theo dõi có cột mã khách hàng trong gồm 3 ký tự:
– Ký tự đầu tiên: thể hiện loại đĩa theo như Bảng giá phía dưới, ví dụ: C = Cải Lương,…
– Tiếp theo ký tự thứ 2: chỉ có 2 trường hợp là B hoặc L tương ứng là phim bộ hoặc phim lẻ.
– Ký tự cuối cùng: tương ứng với băng Gốc (1) hoặc băng Sao (2)
Nguồn để lấy thông tin về giá cho thuê băng đĩa là “Bảng Giá”
Căn cứ để biết giá là “Mã” của băng đĩa khi chúng ta cho thuê
Và một trong các thành tố đó là Loại phim Lẻ hoặc Bộ.
Trong Bảng kê/ Sổ có chỉ tiêu về Loại phim, chúng ta cần phải điền vào đó mỗi lượt cho thuê: Phim Bộ hay Phim Lẻ.
Vậy làm thế nào để lấy được Loại phim ở đây, dùng hàm gì để biết được đĩa phim cho thuê là Bộ hay Lẻ.
Chúng ta chỉ có 2 loại: 01 là Phim bộ, 02 là Phim Lẻ.
Để biết được đĩa phim cho thuê là Lẻ hay Bộ ta căn cứ vào ký tự thứ 2 (ký tự ở giữa).
Chúng ta sẽ lập luận như sau (sau đó việc kết hợp hàm sẽ rất dễ dàng):
Nếu Ký tự ở giữa của “Mã” là B thì giá trị trả về là Phim Bộ nếu không phải thì giá trị trả về là Phim Lẻ
Cú pháp hàm IF : Cấu trúc hàm theo kiểu diễn giải:
= IF (Ký tự ở giữa của mã hàng = “B”, “Phim Bộ”, Phim Lẻ)
Vấn đề mấu chốt là ở đây: Làm thế nào để tách ra được ký tự giữa của “Mã”
Hàm Mid sẽ giải quyết nhiệm vụ này
= Mid (Mã , Số thứ tự của Ký tự đầu tiên cần tách, Số ký tự cần tách)
Trong ví dụ trên là tìm ra chữ B hoặc L trong Mã, thì chúng ta sẽ vận dụng hàm Mid như sau:
= Mid (Mã, 2, 1)
Mã : Đây là mã từng lần cho thuê đĩa gồm có 3 ký tự. Trong đó ký tự thứ 2 sẽ quyết định là Phim bộ hay Phim lẻ
2 : Số thứ tự của ký tự đầu tiên cần tách. Trong trường hợp này thì đó chính là số thứ tự của Chữ “B” trong phần Mã tính từ bên trái sang
1 : Số ký tự cần tách, trong ví dụ của chúng ta thì chỉ cần tách chữ “B”, khi đó số ký tự cần tách là 1
Trong đó: Mã sẽ được thay bằng địa chỉ ô của các giá trị có trong cột Mã.
Hàm Mid Và Cách Sử Dụng Hàm Mid Trong Excel
Trình duyệt của bạn không hỗ trợ nghe audio.
Hàm MID trong Excel – Cú pháp và cách sử dụng cơ bảnNói một cách hàn lâm hàm MID trong Excel được thiết kế để trích một chuỗi dữ liệu nằm ở giữa văn bản gốc. Nói một cách cụ thể hơn thì hàm MID trả về một số ký tự bất kì được chỉ định sẵn bắt đầu từ vị trí bạn thiết lập.
Hàm MID trong Excel có những đối số sau: MID(text, start_num, num_chars)Trong đó
Text là chuỗi văn bản gốc
Start_num là vị trí của dữ liệu đầu tiên mà bạn muốn trích xuất.
Num_chars là số ký tự trích xuất
Ví dụ, để lấy ra 3 ký tự từ chuỗi văn bản ở hàng A5, bắt đầu từ ký tự thứ 5, ta sử dụng công thức sau:
=MID(A5,5,3)
Kết quả hiển thị sẽ giống hình vẽ bên dưới:
Các điều cần biết về hàm MID trong ExcelKhi sử dụng hàm MID các bạn không thể vội vàng và cần phải ghi nhớ một số điều để tránh việc mắc phải những lỗi sai phổ biến.
Hàm MID luôn trả về một chuỗi văn bản, ngay cả khi chuỗi văn bản đó chỉ chứa toàn các chữ số. Điều này rất đáng lưu ý nếu bạn muốn sử dụng kết quả của phép tính sử dụng hàm MID trong các phép tính khác. Để chuyển đổi đầu ra của hàm MID là một số thì cần kết hợp hàm MID với hàm VALUE.
Nếu start_num lớn hơn tổng chiều dài của văn bản gốc, công thức tính theo hàm MID sẽ trả về một chuỗi rỗng (“”).
Nếu start_num nhỏ hơn 1, công thức tính của hàm MID trả về sẽ bị lỗi #VALUE.
Nếu num_chars nhỏ hơn 0 (số âm), công thức MID trả về #VALUE! Nếu num_chars bằng 0 sẽ trả về một chuỗi rỗng (ô rỗng).
Nếu tổng của start_num và num_chars vượt quá tổng chiều dài của chuối văn bản gốc, hàm Excel MID sẽ trả về một chuỗi ký tự bắt đầu từ start_num cho đến ký tự cuối cùng trong chuỗi văn bản gốc đó.
Ví dụ minh họa sử dụng công thức tính của hàm MIDKhi sử dụng hàm MID trong Excel bạn thường sẽ phải kết hợp nó với những hàm khác, như những ví dụ sau đây.
Cách xuất tên và họChúng ta có thể trích xuất họ, tên lần lượt bằng cách sử dụng hàm LEFT và hàm RIGHT. Tuy nhiên, chúng ta cũng có thể thực hiện được bằng phương pháp khác.
Sử dụng hàm MID để trích xuất họGiả sử, họ và tên đầy đủ nằm trong ô A5, họ và tên được phân cách với nhau bằng dấu cách, bạn có thể trích xuất họ bằng cách sử dụng công thức sau:
=MID(A5,1,SEARCH(" ",A5)-1)Chức năng SEARCH được dùng để bỏ dấu cách và trả lại vị trí của nó, vì thế bạn có thể bỏ qua dấu cách đó. Sau đó, bạn dùng hàm MID để trích ra chuỗi con bắt đầu từ ký tự đầu tiên cho đến ký tự đứng trước dấu cách, từ đó ta thu được họ.
Sử dụng hàm MID để trích xuất TênĐể trích xuất tên từ ô A5, các bạn sử dụng công thức sau:
=TRIM(MID(A5,SEARCH(" ",A5),LEN(A5)))Lần này, SEARCH được sử dụng để xác định vị trí bắt đầu (từ chỗ dấu cách). Không cần tính chính xác vị trí kết thúc (vì nếu start_num và num_chars cộng lại lớn hơn tổng chiều dài chuỗi thì hàm sẽ trả về tất cả các ký tự còn lại trong chuỗi văn bản gốc). Vì vậy, trong đối số num_chars, bạn chỉ cần cung cấp tổng chiều dài của chuỗi ban đầu được trả về bằng cách sử dụng hàm chức năng LEN. Nếu không dùng hàm LEN thì bạn có thể đặt một số thật dài cho chuỗi ký tự cần trích xuất, ví dụ như 100. Cuối cùng hàm TRIM sẽ loại bỏ phần dấu cách và cho ra kết quả như sau:
Tiếp tục áp dụng công thức trên với ô kết quả chúng ta thu được Tên
Cách trích xuất chuỗi ký tự nằm giữa 2 dấu cách
Như ví dụ ở bên trên, nếu ngoài họ và tên còn chứa tên đệm thì làm thế nào mới trích xuất được tên đệm đó?
Bạn có thể thực hiện theo phương pháp sau đây:
Kết hợp lại với nhau chúng ta có được công thức MID để trích ra chuỗi ký tự đứng giữa hai khoảng không gian tạo bởi hai dấu cách:
Giống như ở ví dụ trước, chúng ta vẫn sử dụng hàm SEARCH để xác định vị trí của dấu cách đầu tiên, cộng thêm 1 vào đó để chuỗi ký tự trích ra bắt đầu bằng ký tự đứng ngay sau dấu cách. Từ đó, bạn sẽ có được tham số start_num từ công thức MID: SEARCH(” “,A5)+1
Tiếp theo, để có được vị trí của khoảng cách thứ 2 thì chúng ta sử dụng hàm SEARCH với việc bắt đầu tìm kiếm từ ký tự đầu tiên sau dấu cách thứ 2: SEARCH(” “,A5,SEARCH((” ” ,A5)+1)
Để có được chuỗi ký tự trả về, cần trừ đi vị trí 2 khoảng không của 2 dấu cách. Từ đó, chúng ta có đối số num_chars: SEARCH (” “, A5, SEARCH (” “,A5)+1) – SEARCH (” “,A5)
Các bạn có thể xem kết quả trong hình ảnh bên dưới:
=MID(A5,SEARCH(” “,A5)+1,SEARCH(” “,A5,SEARCH(” “,A5)+1)-SEARCH(” “,A5)-1)
Tương tự, bạn có thể trích xuất một chuỗi con nằm giữa hai dấu cách bất kỳ:
MID(string,SEARCH(delimiter, string)+1,SEARCH(delimiter, string,SEARCH(delimiter, string)+1)-SEARCH(delimiter, string)-1)Ví dụ, để trích ra một chuỗi con được ngăn cách bằng dấu phẩy và khoảng cách, bạn sử dụng công thức sau:
=MID(A5,SEARCH(",",A5)+1,SEARCH(",",A5,SEARCH(",",A5)+1)-SEARCH(",",A5)-1)Xem hình bên dưới để thấy sự hiệu quả của công thức này:
Trích xuất từ thứ N trong một chuỗi văn bảnVí dụ này là một ví dụ điển hình cho việc kết hợp sáng tạo công thức MID, kết hợp của 5 hàm khác nhau.
Ta có công thức chung như sau:
TRIM(MID(SUBSTITUTE(string," ",REPT(" ",LEN(string))), (N-1)*LEN(string)+1, LEN(string)))Trong đó:
LEN – để lấy được tổng chiều dài của chuỗi văn bản gốc
REPT – để lặp lại một ký tự cụ thể với số lần nhất định
SUBSTITUTE – thay thế một ký tự bằng một ký tự khác
MID – trích xuất một chuỗi con
TRIM – loại bỏ khoảng không gian dấu cách thêm vào
Ví dụ, để trích xuất từ thứ 2 trong chuỗi văn bản ô A5, ta sử dụng công thức sau:
=TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",LEN(A5))), (2-1)*LEN(A5)+1, LEN(A5)))Hoặc, bạn có thể nhập số thứ tự từ cần trích xuất (N) trong nhiều ô trong cùng công thức, như hình chụp màn hình bên dưới:
String là chuỗi văn bản ban đầu chứa dữ liệu bạn muốn trích xuất.
N là số thứ tự của từ cần trích xuất
Cách hoạt động của công thức trên như sau:Công thức trên hoạt động tốt trong hầu hết các trường hợp nhưng nếu có từ 2 khoảng trống sát nhau giữa các từ thì kết quả nhận được sẽ bị sai. Để khắc phục lỗi sai này thì nên lồng ghép hàm TRIM vào hàm SUBSTITUTE để loại bỏ các khoảng trống nằm giữa những khoảng trống khác.
Nếu chuỗi văn bản gốc chứa nhiều khoảng trống giữa các từ và các từ trong đó quá lớn hoặc quá nhỏ thì bạn nên thêm hàm TRIM lồng trong mỗi hàm LEN để tránh xảy ra lỗi.
Công thức này có vẻ khá phức tạp nhưng lại đảm bảo không xảy ra lỗi.
Trích xuất một từ chứa một hoặc nhiều ký tự cụ thể:
Hàm SUBSTITUTE và REPT thay thế mỗi khoảng trống đơn lẻ trong một chuỗi bằng nhiều khoảng trống. Số lượng khoảng trống được thêm vào bằng tổng chiều dài của chuỗi văn bản gốc được trả về bới hàm LEN: SUBSTITUTE(A2,” “,REPT(” “,LEN(A2)))
Bạn có thể coi kết quả mà bạn muốn trích ra từ chuỗi văn bản gốc cũng giống như một “hành tinh nhỏ” trôi dạt trong một không gian rộng lớn gồm: khoảng trống-từ thứ nhất-khoảng trống-từ thứ hai-khoảng trống-từ thứ ba-…. Từ chuỗi văn bản như vậy chúng ta nhận được đối số của công thức MID.
Tiếp đến, bạn tính ra vị trí bắt đầu của chuỗi con mà bạn cần trích xuất (đối số start_num) sử dụng phương trình sau: (N-1) * LEN (A1) +1. Phép tính này trả về ví trí của ký tự đầu tiên trong chuỗi con cần trích xuất hoặc vị trí của một số khoảng trống trong tổng số những khoảng trống trước đó.
Số ký tự trích xuất (đối số num_chars) là phần đơn giản nhất vì chúng ta có thể tìm ra bằng cách lấy tổng chiều dài của chuỗi văn bản gốc: LEN(A2). Qua đó, bạn sẽ loại bỏ được những khoảng trống trong chuỗi dài gồm cả khoảng trống và từ.
Cuối cùng, hàm TRIM sẽ loại bỏ khoảng trống nằm ở đầu và cuối.
TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,99)),MAX(1,FIND(char,SUBSTITUTE(string,” “,REPT(” “,99)))-50),99))
Giả sử văn bản gốc nằm ở ô A5, bạn cần tìm kiếm chuỗi con chứa kí tự “@” (a còng) bạn dùng công thức sau:
Tương tự, bạn có thể trích tên trang web (dựa trên “www”), ….
Cách hoạt động của công thức trên như sauGiống như trong ví dụ trước, các hàm SUBSTITUTE và hàm REPT biến mọi khoảng trống trong chuỗi văn bản gốc thành nhiều khoảng trống, chính xác hơn là 99 khoảng trắng.
Hàm FIND xác định vị trí của ký tự mong muốn (trong ví dụ này là $), tiếp đó bạn trừ đi 50. Làm như vậy bạn sẽ nhận lại được 50 ký tự và đặt ở giữa khối 99 khoảng trống đứng trước chuỗi con chứa ký tự được chỉ định.
Hàm MAX được sử dụng để xử lý tình huống khi chuỗi con mong muốn xuất hiện ở đầu chuỗi văn bản ban đầu. Trong trường hợp này, kết quả của FIND () – 50 sẽ là số âm, và MAX (1, FIND () – 50) sẽ được thay thế bằng 1.
=TRIM(MID(SUBSTITUTE(A5,” “,REPT(” “,99)),MAX(1,FIND(“@”,SUBSTITUTE(A5,” “,REPT(” “,99)))-50),99))
Qua đó, hàm MID sẽ thu thập 99 ký tự tiếp theo và trả về chuỗi con bạn cần. Hàm TRIM giúp bạn loại bỏ hết những khoảng trống ở xung quanh chuỗi con bạn cần đó.
Mẹo: Nếu chuỗi văn bản gốc quá lớn, bạn có thể thay thế 99 và 50 thành con số lớn hơn, ví dụ như 1000 và 500.
Cách khiến hàm MID trả về 1 sốGiống như những hàm văn bản khác, Excel MID luôn trả về chuỗi văn bản, ngay cả khi nó chứa các chữ số trông giống như một con số. Để chuyển đầu ra thành một số, chúng ta chỉ cần thay đổi hàm MID một chút, sử dụng VALUE để chuyển đổi một giá trị văn bản đại diện cho một số.
Ví dụ, để trích xuất 3 ký tự, bắt đầu từ ký tự thứ 5 và chuyển chuỗi văn bản kết quả thành dạng số thì ta sử dụng công thức:
=VALUE(MID(A5,5,3))
Với cách tiếp cận tương tự áp dụng cho những công thức phức tạp hơn. Như trong ví dụ trên, giả sử các mã lỗi (Erro) có độ dài khác nhau thì bạn vẫn có thể trích xuất chúng bằng cách sử dụng công thức MID để nhận được chuỗi ký tự con nằm giữa hai dấu hai chấm, lồng bên trong hàm VALUE:
=VALUE(MID(A5,SEARCH(“:”,A5)+1,SEARCH(“:”,A5,SEARCH(“:”,A5)+1)-SEARCH(“:”,A5)-1))
Kết Hợp Hàm If Và Hàm And L Cách Sử Dụng Hàm If Kết Hợp And
Kết hợp hàm IF và hàm And. Đây là một trong những cách kết hợp phổ biến của hàm IF bởi nó được áp dụng rất nhiều trong các tình huống thực tế tại Doanh nghiệp, Trường học, Trung tâm,…
Để hiểu được cách kết hợp của 2 hàm này chúng ta phải hiểu được cách sử dụng của từng hàm:
2. Ví dụ về cách kết hợp hàm if và hàm and
Tiếp đến, chúng ta sẽ cùng giải quyết 1 yêu cầu trong bài tập về cách kết hợp hàm Excel để thấy được hàm If và hàm and kết hợp như thế nào:
Yêu Cầu: Dựa vào Điểm Thực Hành (TH) và Điểm Lý Thuyết (LT) để tính điểm nhận xét biết rằng:
A/ – Nếu cả 2 điểm LT và TH đều lớn hơn hoặc bằng 8 thì Điểm nhận xét là 1
B/ – Nếu cả 2 điểm LT và TH đều lớn hơn hoặc bằng 7.5 thì Điểm nhận xét là 0.5
C/ – Còn lại thì điểm nhận xét bằng 0
3. Phân tích đề bài và cách sử dụng hàm
Như vậy trong trường hợp này mình có 3 điều kiện và chúng ta phải sử dụng hàm IF trong trường hợp này:
If (A thỏa mãn, 1, If(B thỏa mãn, 0.5, 0))
Vấn đề nằm ở chỗ là làm thế nào để biết A hoặc B có thỏa mãn hay không bởi lẽ mình có 2 điểm (TH và LT) dùng để so sánh:
Nếu điều kiện của chúng là tất cả các giá trị đều phải thỏa mãn điều kiện con của nó thì chúng ta sẽ dùng hàm AND:
Chúng ta có thể thấy:
HS số 1 (Kim), Số 3 (Minh) có 2 điểm LT và TH đều lớn hơn hoặc bằng 8 nên được 1 điểm nhận xét
HS số 2 (Hoa) có cả 2 điểm nhỏ hơn 7.5 nên 0 điểm nhận xét
HS số 4 (Quân) có 1 điểm nhỏ hơn 8 nhưng lớn hoặc bằng 7.5 nên chỉ được 0.5 điểm nhận xét
Còn đối với HS số 5 (Duy) lại có 1 điểm nhỏ hơn 7.5 nên dù điểm còn lại có là 10 thì cũng 0 điểm nhận xét.
Đó là ý nghĩa của việc dùng hàm AND (Tất cả đều phải thỏa mãn)
Trung Tâm Đào tạo Kỹ năng làm việc chúng tôi
Hàm Vlookup Kết Hợp If, Left, Mid, Right, Hlookup
Cách dùng hàm Vlookup kết hợp trong Excel khá đa dạng, linh hoạt. Trên thực tế, Khi các yêu cầu công việc phức tạp đòi hỏi phải tùy biến, kết hợp nhiều hàm với nhau thì mới giải quyết được yêu cầu. Vậy hôm nay cùng Hocexcelcoban hiểu cách kết hợp hàm Vlookup!!
1. CẤU TRÚC HÀM VLOOKUP:Hàm VLOOKUP ( Tra cứu dọc) tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định.
Cú pháp:
=VLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])
Lookup_value – Giá trị cần tìm trong cột đầu tiên của bảng.
Table – Bảng để truy xuất một giá trị.
Col_index – Cột trong bảng để truy xuất một giá trị.
range_lookup – [tùy chọn] TRUE = đối sánh gần đúng (mặc định). FALSE = đối sánh chính xác
2. HÀM VLOOKUP KẾT HỢP HÀM TRONG EXCEL 2.1 SỰ KẾT HỢP CỦA HÀM VLOOKUP VỚI HÀM IF Ví dụ 1: Dùng IF để so sánh giá trị trong hàm VLOOKUPMô tả: Dựa vào cột dữ liệu có trong các ô , hãy tìm hiểu xem tên James được đề cập trong ô có phần thưởng dựa trên doanh số lớn hơn 10000$ hay không?
Các bước thực hiện:
Bước 3: Nhấn Enter được kết quả trả về như hình phía bên dưới
Yêu cầu: Sử dụng hàm VLOOKUP kết hợp với hàm IF để tìm ra các lỗi
Khi thực hiện xong sẽ trả về lỗi #N/A. Có nghĩa là tên Jam không tồn tại trong các ô
Vì vậy, Để xử lý lỗi này, ta thực hiện bằng cách lồng hàm VLOOKUP và ISNA vào bên trong hàm IF.
Bước 2: Nhập công thức: =IF(ISNA(VLOOKUP(E3,B3:C9,2,FALSE )), “Name not found”, VLOOKUP(E3,B3:C9,2,FALSE )) vào ô F3.
Bước 3: Nhấn Enter được kết quả trả về như hình dưới
Ví dụ 3: Dùng IF để tra cứu hai giá trị trong hàm VLOOKUPXét ví dụ, các ô B2:B7 chứa giá trị cho các sản phẩm ở hai hàng khác nhau.
Để tìm giá trị của sản phẩm trong ô F3 , bạn thực hiện như sau:
Bước 2: Trong ô H3 nhập công thức sau:
Bước 3: Ấn Enter được kết quả như hình dưới
2.2 HÀM VLOOKUP KẾT HỢP HÀM LEFT, MID, RIGHTa) HÀM LEFT : Hàm tách ký tự từ phía bên trái với số lượng ký tự lấy ra do mình quy định.
Cú pháp:
= LEFT(text, num_chars)
Text: đoạn văn bản cần tách ký tự
Num_chars : số ký tự muốn tách tích từ bên trái. Nếu không nhập thì sẽ tự nhận giá trị là 1
VÍ DỤ HÀM VLOOKUP VỚI HÀM LEFT :Chúng ta có một bảng dữ liệu như hình dưới
Nghĩa là chúng ta sẽ thay giá trị dùng để tìm kiếm $C4 thành LEFT(B4,2).
Cú pháp:
= MID(text, start_num, num_chars)c) HÀM RIGHT : Nếu hàm LEFT để tách chuỗi ký tự bên trái thì hàm RIGHT ngược lại, tách chuỗi ký tự từ bên phải trong một dãy ký tự mà người dùng lựa chọn
Cú pháp:
= RIGHT(text, num_chars)
Text: đoạn văn bản cần tách ký tự
Num_chars: số ký tự muốn tách tích từ bên phải. Nếu không nhập thì sẽ tự nhận giá trị là 1
2.3 SỬ DỤNG HÀM VLOOKUP VỚI HÀM SUMIF, SUMHàm SUMIF trong Excel thì tương tự với hàm SUM về mặt tính tổng các giá trị. Điểm khác biệt chính là hàm SUMIF tính tổng chỉ các giá trị đáp ứng tiêu chuẩn mà bạn đã định rõ.
Bảng Table 2 chứa nhiều mục có cùng số ID theo thứ tự ngẫu nhiên.
Bạn không thể thêm cột “Tên người bán” vào bảng Table 2.
CÔNG THỨC:
=SUMIF(E:E,VLOOKUP(I2,B4:C10,2,FALSE),F:F)
SumiF và Vlookup là tên hàm tính tổng và hàm tìm kiếm theo điều kiện.
E:E là vùng được lựa chọn để chứa các ô điều kiện
I2 là giá trị đối chiếu với cột doanh số, là giá trị dò tìm. Tại đây khi bạn thay đổi tên thì cột doanh số cũng thay đổi theo.
B2:C10 là thứ tự cột cần lấy dự liệu để dò gì cho giá trị I2 ở trên.
Số 2 là thứ tự xuất giá trị, hiển thị lên màn hình tùy theo cột cần lấy dữ liệu có mấy cột, vì cột Mã ID ở vị trí thứ 2 nên chúng ta đặt là 2.
False là phạm vi tìm kiếm tuyệt đối cho kết quả chính xác thay vì sử dụng True cho kết quả tương đối.
Chúng ta có ví dụ như sau:
=D5*VLOOKUP(HLOOKUP(B5,$C$11:$F$12,2,FALSE),$B$15:$D$18,3,FALSE)Bạn có thể hiểu công thức trên như sau: Giá trị trả về của hàm HLOOKUP là giá trị tìm kiếm của hàm VLOOKUP.
Để có thể sử dụng tốt việc kết hợp các hàm với nhau, chúng ta cần nắm rõ được logic của vấn đề trước, sau đó mới xác định sử dụng hàm nào, đặt thứ tự các hàm tại vị trí nào.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Ngoài ra còn rất nhiều hàm nâng cao khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công cụ như Data validation, Pivot table, Power Query…
VIDEO THỰC HÀNH MỘT SỐ TÌNH HUỐNG THỰC TIỄN :Cách Sử Dụng Hàm Vlookup Kết Hợp Hàm Left Trong Excel
Hôm nay tôi hướng dẫn bạn cách sử dụng hàm vlookup kết hợp với hàm Left để mọi người có các nhìn trực quan về hàm vlookup trong sử dụng quản trị công việc của mình và hỗ trợ các bạn đang tìm hiểu về tin học văn phòng nâng cao và thích sử dụng excel
Ví dụ chúng ta có 1 bảng danh sách nhân viên với 3 cột: Mã nhân viên, Họ tên, Bộ phận như sau:
Bảng E1:F5 bao gồm nội dung chi tiết tên các bộ phận.
Vậy làm thế nào để có thể tra cứu được Bộ phận của từng người căn cứ vào Mã của họ?
Cách làm:
Để tra cứu được bộ phận theo mã nhân viên, chúng ta cần làm 2 việc sau:
Thứ 1: Tách được 2 ký tự đầu trong mã nhân viên bằng hàm LEFT
Thứ 2: Sử dụng kết quả của hàm LEFT để tham chiếu trong bảng E1:F5 để tìm bộ phận tương ứng.
Về cách sử dụng hàm LEFT chúng ta có:
=LEFT(text, [num_chars])
text: đoạn văn bản cần tách ký tự
num_chars: số ký tự cần tách. Nếu không nhập thì sẽ tự nhận giá trị là 1
Trong trường hợp này, số ký tự cần tách là 2, do đó ta có:
Tiếp theo, chúng ta tham chiếu kết quả của hàm LEFT tới vùng bảng E1:F5 để tìm tên bộ phận. Khi đó coi kết quả hàm LEFT chính là giá trị cần tìm.
Viết hàm VLOOKUP lồng với hàm LEFT như sau:
Trong đó kết quả cần tìm là cột thứ 2 trong vùng bảng E1:F5. Phương thức tham chiếu là tìm chính xác theo ký hiệu nên sử dụng số 0.
THAM CHIẾU VỚI 1 SỐ Ở ĐỊNH DẠNG TEXTCó thể ít khi bạn gặp trường hợp này, nhưng trong Kế toán thì gặp nhiều, đó là khi sử dụng các tài khoản kế toán, chúng ta rất hay nhầm lẫn không biết con số đó ở dạng Text hay dạng số. Do đó khi sử dụng hàm VLOOKUP ra kết quả lỗi #N/A như sau:
Trong vùng DM_TK!A5:E315 thì cột Số TK (cột A) lại là dạng Text
Để khắc phục điều này, chúng ta sẽ sử dụng hàm LEFT và hàm LEN để chuyển giá trị tại ô I1 về dạng TEXT. Khi đó tham chiếu bằng hàm VLOOKUP sẽ chính xác:
Các lý do cần đọc trước khi quyết định liên hệ tôi
Tham gia group Mua bán Google Ads và Google Shopping :
Tham gia group mua bán cho thuê tài khoản BM Agency :
AGOLA chỉ cung cấp dịch vụ không có dịch vụ tư vấn hiểu tới đâu bán tới đó !
Các sản phẩm dịch vụ không có thương thảo giá và cũng không có hỏi thêm
Không bán vĩnh viễn mọi dịch vụ mua nhầm đòi đổi sang gói dịch vụ khác
Thông tin bảo hành gần như không có nhưng cũng có một số dịch vụ có bảo hành đề nghị đọc kỹ trước khi quyết định mua
Quyết định mua hàng là đồng ý các điều khoản mua bán và điều khoản không được lấy lại tiền và không bồi hoàn
Mua hàng thì xin đọc kỹ điều khoản ở từng mô tả trước khi quyết định nhấc máy liên hệ ở thông tin của từng gói dịch vụ
Mọi giao dịch đều được phải bắt buộc chụp màn hình chuyển khoản gửi vào Zalo
Các đối tượng cố ý chuyển tiền gói này để trục lợi sử dụng gói khác đều được coi là hình vi lừa đảo ! Cố ý ghi nội dung hiểu lầm về chuyển tiền để đôi co đòi sử dụng gói dịch vụ khác cũng vậy !
Tôi là dân khối A nên việc viết sai lỗi chính tả thì kệ mẹ tôi thích thì mua không thích thì đừng soi đi ra chỗ khác do tôi không có soát lại chính tả nên các bạn đọc thay vì thắc mắc thì thông cảm giùm tôi ! miễn sao tôi truyền tải thông tin bạn đọc bạn thấy hiểu được bạn sử dụng không được bạn tìm bên khác
Tôi không làm việc với bọn cử nhân viên là các công ty Agency , truyền thông gọi hỏi dò hỏi luyên thuyên ! Nếu cố tình tôi sẽ dùng CRM Agency cấm lock chạy Ads vĩnh viễn khi nhìn thấy đuôi theo rõi của doanh nghiệp đó mà tôi đã gán.
Tôi không làm việc với bọn nào thì tôi chạy tiền tỷ tôi tiêu tiền tỷ nhưng chạy demo trước nếu ngon thì vít lớn nhưng sau 3 ngày thuê thấy đéo thấy như cam kết về ngân sách
Tôi không hợp tác ăn chia hay kiểu Win Win đôi bên có lợi
Tất cả những câu hỏi khi gọi điện khiến tôi nghĩ rằng bạn chưa đọc kỹ thông tin trên website sẽ được tôi từ chối vô điều kiện
Các đối tượng cố tình lên mạng tìm kiểm thông tin tôi và không có trao đổi được tôi dùng Zalo : O9O9.456.866 gửi STK thông tin thanh toán của tôi qua zalo đó mà vẫn chuyển tiền vào tài khoản của tôi được coi là hình vi lừa đảo cố tình trọc phá và lợi dụng sử dụng lừa đảo để mong muốn sử dụng dịch vụ của tôi nhưng vì cấm nên phải làm vậy
Kiến thức của đại đa số dân trên mạng không sai mà chỉ đúng với bọn gà mờ mà thôi kiểu như bạn học lớp 1 cô giáo nói 1 không trừ được cho 2 lên cấp 2 cô giáo nói 1 trừ được cho 2 ! Lúc đó cô giáo cấp 1 hay thầy giáo cấp 2 không nói ai đúng ai sai được mà tại thời điểm đó bạn thấy nó đúng còn khi đi sâu vào nó đéo đúng 😀 ! Cũng như việc tôi bán dịch vụ cho dân trong nghề việc tôi có bắt mấy thằng cấp 1 nuốt kiến thức chuyên sâu của cấp 2 là không được bởi vậy dân nào hiểu sẽ liên hệ mua chứ đừng kiểu lơ mở hỏi cho vui cửa vui nhà
Không làm việc với bọn hở miệng ra là em từng có BM2500 với BM1000
Không lamg việc với bọn hở mồm ra là fake mà đến cái via kiểm tra như thế nào là 1 cái via Việt hay via us… cũng đéo biết kiểm tra thế mà cứ đi nghe mấy thằng trên mạng Fake Ip , Fake đổi ngôn ngữ này ngôn ngữ kia … tùm lum tà la login đổi via các kiểu con đa điểu rồi nghe thì vui tai mà làm cũng được không đéo có biết lý do chỉ có lý do vớ vẩn – Không tin tao đố bọn mày hỏi thử nó 1 câu via Việt là như thế nào đố thằng đó biết đó xàm xí – hay via việt là thông tin Việt Nam hahaha !
Tiếp tục sắp tới sẽ thanh lọc toàn bộ Invoice
Hàm Vlookup Kết Hợp Hàm If Trong Excel Và Hàm Hlookup + If
Hàm VLOOKUP kết hợp hàm if / Hàm hlookup trong excel với các phiên bản hoàn toàn giống nhau. Trường cũng nói về cách sử dụng hàm HLOOKUP kết hợp hàm if.
Vlookup là một hàm tìm kiếm rất phổ biến trong excel. Bài viết gồm có: Cú pháp, ví dụ minh họa, bài tập thực hành.
1/ Khi nào dùng hàm vlookup và hlookup trong excel.Vlookup – là hàm tìm kiếm theo cột:
Hlookup – là hàm tìm kiếm theo dòng.
Điểm chung giữa 2 hàm tìm kiếm này:– Đều là hàm tìm kiếm giá trị
– Cú pháp hàm đều gồm 4 thành tố: Điều kiện tìm kiếm, vùng tìm kiếm, cột/ dòng chỉ định và kiểu tìm kiếm.
Sự khác nhau giữa hai cú pháp hàm excel trên là ở thành tố thứ 3:+ Cột chỉ định – áp dụng cho hàm vlookup
+ Dòng chỉ định – áp dụng cho hàm hlookup
2/ Ví dụ về minh họa vlookup kết hợp if/ hlookup kết hợp ifTính Số Điện tiêu thụ trong và ngoài định mức – Dựa trên “Bảng Định Mức” phía dưới.
Nếu số tiêu thụ < số định mức: Số trong định mức = số tiêu thụ
Số tiêu thụ = Số mới – Số cũ
Số định mức: dùng hàm vlookup hoặc hàm hlookup để tìm từ Bảng định mức.
Nhiệm vụ tiếp theo là tìm số tiêu thụ định mức để so sánh với số tiêu thụ là chúng ta sẽ biết được số trong và ngoài định mức.
Làm thế nào để tính được Số tiêu thụ định mức của từng hộ gia đình.Theo như Bảng Định Mức ở dưới thì Số tiêu thụ định mức của mỗi hộ được xác định dựa trên 2 yếu tố:
Cột: A, B, C, D
Dòng: NN, CB, ND, KD, SX
Ở trong Bảng Thanh Toán Tiền Điện:
Chúng ta sẽ thấy mỗi một hộ gia đình đều có Mã Hộ ở cột D, trong đó Mã hộ gồm 2 phần:
– 2 Ký tự đầu tiên: là một trong 5 loại sau: NN, CB, ND, KD, SX
– 1 Ký tự cuối cùng: là một trong 4 loại sau: A, B, C, D
Như vậy ta có thể dùng hàm Vlookup để tìm theo A, B, C, D hoặc dùng hàm Hlookup để tìm theo NN, CB, ND, KD, SX.
Trong bài này tôi sẽ hướng dẫn dùng hàm Vlookup.
Cấu trúc của hàm Vlookup ( Lookup_value, Table_array , Col_index_num, Range_lookup )
2.1/ Cách sử dụng hàm Vlookup kết hợp hàm if trong ví dụ này:IF(LEFT(D3,2)=”NN”,2,IF(LEFT(D3,2)=”CB”,3,IF(LEFT(D3,2)=”ND”,4,
(2) – Table_array: Vùng tìm kiếm, chính là Bảng Định Mức, trong đó cột đầu tiên trong vùng tìm kiếm là cột chứa A, B, C và D
Vùng điều kiện nên được cố định cả dòng cả cột.
Vì như thế khi copy công thức sang ô khác thì vùng điều kiện không bị thay đổi.
Bởi lẽ tất cả các hộ tiêu thụ điện đều có số định mức nằm trong bảng định mức.
(3) – Col_index_num: Số thứ Cột chứa giá trị đang tìm kiếm (Cột chỉ định)
Ở trường số (3) chúng ta sẽ dễ dàng nhận thấy rằng:
Nếu 2 ký tự đầu của mã hộ là NN thì Cột chỉ định sẽ là 2 tính từ cột đầu tiên. Còn nếu không phải NN mà là CB thì cột chỉ định sẽ là 3, …
Chúng ta suy luận tương tự thì chúng ta sẽ tìm được tất cả các trường hợp cho cột chỉ định.
Ở trường này chúng ta sẽ phải dùng hàm Left để tách ra 2 ký tự đầu tiên trong Mã hộ. Sau đó dùng kết quả của hàm này để tìm số thứ tự cột chỉ định
(4) – Range_lookup: Kiểu tìm kiếm là 0 – Kiểu tìm kiếm chính xác 100%.
2.2/ Cách sử dụng hàm Hlookup kết hợp hàm if trong ví này:Sự khác nhau giữa 2 hàm này là:
– Tên hàm: Hlookup thay vì Vlookup
– Điều kiện tìm kiếm: khi dùng hàm hlookup thì dòng đầu tiên (row 18) của vùng dữ liệu chứa 2 ký tự đầu của mã hộ NN hoặc CB. Do đó điều kiện tìm kiếm cần tách ra 2 ký tự đầu tiên của mã từng hộ dân.
– Thành tố thứ 3 là Dòng chỉ định, thay vì cột chỉ định như CỘT chỉ định.
Nhìn ở Bảng định mức:
A: là dòng thứ 2 tính từ dòng 18
B: là dòng thứ 3 tính từ dòng 18
….
Như vậy, Trường đã hướng dẫn xong các bạn về Hàm vlookup kết hợp hàm if/ Hàm hlookup kết hợp hàm if.
Tổng hợp bài tập excel cơ bản đến nâng cao
Cập nhật thông tin chi tiết về Hàm If Kết Hợp Hàm Mid Trong Excel L Cách Sử Dụng Hàm Mid Kết Hợp If trên website Utly.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!