Xu Hướng 2/2024 # Hướng Dẫn Giải Bài Tập Excel Có Dùng Hàmvlookup, Hàm If, Hàm Sum # Top 3 Xem Nhiều

Bạn đang xem bài viết Hướng Dẫn Giải Bài Tập Excel Có Dùng Hàmvlookup, Hàm If, Hàm Sum được cập nhật mới nhất tháng 2 năm 2024 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 tập ứng dụng hàm IF, hàm VLOOKUP, hàm SUM trong Excel

(Các bạn có thể tải file bài tập kèm theo đáp án theo link tải ở bên dưới bài viết này)

Xác định đơn giá các sản phẩm trong vùng B2:B7 dựa theo bảng kê sản phẩm

Tính kết quả cột Khuyến mại (vùng E2:E7)

Tính kết quả cột Thành tiền (vùng F2:F7)

Tính tổng tiền phải trả tại ô F8

Trong bài tập này chúng ta có 4 yêu cầu như sau:

Dùng hàm VLOOKUP tìm đơn giá sản phẩm

Bạn phải tìm một cái gì đó

Bạn tìm dựa theo 1 bảng nào đó

Với yêu cầu đầu tiên, bạn sẽ phải xác định đơn giá sản phẩm dựa theo bảng kê các sản phẩm ở phía dưới. Thủ thuật ở đây là:

Cú pháp của hàm VLOOKUP là:

=VLOOKUP( Tìm_dựa_theo_cái_gì, Tìm_ở_đâu, Cột_chứa_kết_quả, Phương_pháp_tìm_kiếm)

Tìm dựa theo cái gì? Đó chính là mã sản phẩm

Tìm ở đâu? Căn cứ theo Bảng kê sản phẩm: vùng bảng A12:C17, trong đó cột đầu tiên trong bảng chứa giá trị tìm kiếm. Chú ý cần phải cố định vùng bảng này.

Cột chứa kết quả là cột thứ mấy trong bảng? Ở đây cần xác định đơn giá, do đó cột chứa kết quả là cột thứ 3 trong bảng

Phương pháp tìm kiếm? Ở đây tìm theo đúng mã sản phẩm, do đó phương pháp tìm kiếm là Exact (tìm chính xác)

Như vậy chúng ta thấy:

Bạn có thể viết công thức VLOOKUP cho ô D2 như sau:

VLOOKUP(A2, $A$12:$C$17, 3, 0)

Kết quả thu được như sau:

Bước 1: Chọn vùng ô D2:D7

Bước 2: nhấn tổ hợp phím Ctrl + D

Khi đó chúng ta chỉ cần kéo công thức tại ô D2 xuống các ô trong vùng D2:D7 bằng cách sử dụng kỹ thuật Filldown như sau:

Kết quả:

Số lượng từ 1 tới 100: không được khuyến mại (bằng 0)

Số lượng từ 101 tới 230: khuyến mại giảm 2% tổng tiền

Số lượng từ 231 tới 300: khuyến mại giảm 3% tổng tiền

Số lượng từ 301 trở lên: khuyến mại giảm 4% tổng tiền

Dùng hàm IF để tính khuyến mãi

Trong câu tiếp theo, các bạn hãy chú ý đề bài yêu cầu xác định kết quả khuyến mại dựa theo một loạt những yêu cầu:

Chúng ta có thể hiểu là: Nếu cột số lượng có giá trị từ 1 tới 100 (hay là nhỏ hơn hoặc bằng 100) thì khuyến mại bằng 0%

Bạn có thể sử dụng hàm IF để biện luận logic này như sau:

hàm IF thứ 1 =IF(C2<=100, 0%, xét trường hợp lớn hơn 100)

Tương tự như vậy chúng ta có ở mốc số lượng nhỏ hơn hoặc bằng 230 như sau:

hàm IF thứ 2 =IF(C2<=230, 2%, xét trường hợp lớn hơn 230)

Trong trường hợp nhỏ hơn hoặc bằng 300, chúng ta có:

hàm IF thứ 3 =IF(C2<=300, 3%, xét trường hợp lớn hơn 300)

hàm IF thứ 1 viết trước

hàm IF thứ 2 sẽ đặt vào vị trí tham số thứ 3 của hàm IF thứ 1

hàm IF thứ 3 sẽ đặt vào vị trí tham số thứ 3 của hàm IF thứ 2

Và trường hợp lớn hơn 300 thì nhận giá trị khuyến mại là 4%

Tới đây bạn có thể lồng ghép các hàm IF vào nhau theo cách:

kết quả:

=IF(C2<=100, 0%, IF(C2<=230, 2%, IF(C2<=300, 3%, 4%)))

Ở đây chúng ta sử dụng 3 hàm IF nên sẽ cần sử dụng 3 dấu đóng ngoặc

Dùng các phép tính toán học để tính cho cột Thành tiền

Ở yêu cầu thứ 3, chúng ta chỉ cần áp dụng các phép tính toán học là cộng, trừ, nhân, chia để viết lại cho biểu thức tính kết quả thành tiền như sau:

Thành tiền = Số lượng * Đơn giá * (1 – Khuyến mại)

Các phép tính nhân chia được thực hiện trước

Các phép tính cộng trừ sau.

Nếu phép tính cộng trừ muốn tính trước thì phải đặt biểu thức tính trong dấu ngoặc đơn.

Viết công thức trong ô F2 như sau:

F2 = C2 * D2 * (1-E2)

Chọn ô F8 (là ô muốn dùng hàm SUM)

Bấm tổ hợp phím tắt ALT và dấu bằng (=)

Chú ý khi viết biểu thức tính này các bạn cần đảm bảo nguyên tắc:

Kết quả là:

Dùng hàm SUM tính kết quả tổng số tiền

Có một cách rất đơn giản để viết hàm SUM đó là các bạn sử dụng chức năng AutoSum bằng cách:

Bạn muốn tìm hiểu thêm kiến thức về Excel và ứng dụng Excel vào công việc? Hãy tham gia ngay khóa học EXG01 – Excel từ cơ bản tới nâng cao dành cho người đi làm 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. Ngoài ra khóa học cũng có nhiều bài tập để bạn thực hành. 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.

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,…

Bạn có thể tải file bài tập sử dụng trong bài viết này tại đường link ở phía cuối bài viết.

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:

Bài Tập Excel Có Lời Giải 004: Bài Tập Hàm Vlookup, Hàm If

Chào mừng bạn trở lại với chuyên mục bài tập Excel tổng hợp trên website Đỗ Bảo Nam Blog! Bài tập ngày hôm nay, mình sẽ tiếp tục chia sẻ với bạn một dạng bài tập cơ bản. Bài này giúp bạn luyện tập các kỹ năng cơ bản về Excel. Đồng thời, bạn sẽ được luyện tập thêm về cách sử dụng của các hàm thông dụng. Đó là hàm IF, hàm VLOOKUP.

Đây là 02 hàm rất thông dụng trong Excel. Và nó được sử dụng phổ biến trong học tập và công việc. Ví dụ nếu bạn đang còn trên ghế nhà trường, thì các đề thi Excel chắc chắn sẽ có ít nhất 1 hoặc cả 2 hàm này trong đề thi.

Nội dung bài tập Excel tổng hợp có lời giải số 004

Trong bài thực hành này, bạn tiếp tục luyện tập với những thao tác Excel cơ bản. Đồng thời, bạn sẽ luyện tập thêm về cách dùng hàm Vlookup, hàm IF. Nội dung bài tập này khá ngắn gọn, bạn có thể download file thực hành ở phía dưới bài viết.

Thực hành các thao tác Excel cơ bản: Cũng như thường lệ, trong bài tập Excel tổng hợp này bạn sẽ tiếp tục thực hành các thao tác Excel cơ bản. Đó là những công việc như nhập dữ liệu, định dạng dữ liệu theo mẫu. Ngoài ra, bạn còn thực hành thêm về cách kẻ bảng trong Excel, cách gộp ô trong Excel…

Thực hành về một số hàm Excel cơ bản: Trong bài này, bạn sẽ thực hành với 02 hàm rất phổ biến. Đó là hàm IF và hàm VLOOKUP. Mức độ sử dụng của 02 bài này là cơ bản. Nếu bạn muốn nâng cao hơn, bạn có thể tham khảo các bài tập khác tại website dobaonamblog.com.

Về các yêu cầu tính toán của bài tập, trong bài này bạn sẽ thực hành với các thao tác tính toán cơ bản. Bên cạnh đó, bạn sẽ ôn lại cách dùng hàm If, hàm Vlookup trong Excel. Đây là 02 hàm được sử dụng rất nhiều trong học tập và công việc thực tế.

File Excel này được Đỗ Bảo Nam Blog chia sẻ ngay dưới video. Bạn có thể download file Excel mẫu và lời giải về tham khảo. Tuy nhiên để bạn có thể luyện tập hiệu quả, bạn nên tự nhập liệu, tính toán. Sau đó, bạn so sánh với công thức trong lời giải. Như vậy bạn sẽ nhanh chóng sử dụng thành thạo phần mềm Excel.

Video giải bài tập số 004 Tải file bài tập Excel cơ bản tổng hợp có lời giải về máy

Trong file Excel mà bạn tải về, Đỗ Bảo Nam Blog đã có lời giải sẵn. Và với đa số các bài toán, bạn có thể sử dụng cách tính toán khác nhau. Cách tính trong video (file Excel) là một trong những lời giải hay và dễ hiểu nhất.

Ngoài bài tập này, trên kênh Đỗ Bảo Nam Blog còn rất nhiều các bài tập Excel từ cơ bản đến nâng cao khác. Bạn có thể download bài tập và xem đáp án của bài trong mỗi bài viết. Mỗi bài tập sẽ giúp bạn luyện tập với những hàm khác nhau. Vì vậy nếu có thể, bạn nên download càng nhiều bài tập càng tốt. Sau khi thực hành, bạn sẽ cảm thấy cách dùng Excel khá dễ dàng.

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ướng Dẫn Cách Dùng Hàm Index Và Match Qua Bài Tập Có Lời Giải

Bài tập 1: Sử dụng hàm MATCH tìm vị trí theo dòng, cột

Cho bảng tính sau:

Nếu muốn biết thông tin của người có tên “Lê Thị Thủy” thì bạn phai 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.

Bằng cách sử dụng hàm MATCH, bạn sẽ biết được 1 người nào đó nằm ở dòng thứ mấy trong bảng.

Ta có công thức sau:

=MATCH(“Lê Thị Thủy”, cột Họ tên, số 0)

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.

Vậy việc tìm ra số 3 thì có ý nghĩa gì? Hay nói cách khác biết người đó ở vị trí dòng thứ 3 trong bảng thì có tác dụng gì?

Bài tập 2: Kết hợp hàm INDEX với MATCH tìm Email theo tên

Khi nhìn vào bảng trên, ta thấy tại vị trí dòng 3, trong cột Email sẽ xuất hiện Email cần tìm. Như vậy có thể hiểu: Chỉ cần dóng theo cột Email, tại vị trí dòng 3 trong bảng là chúng ta có kết quả cần tìm.

Điều đó chính là cách kết hợp hàm INDEX với MATCH:

=INDEX(E3:E8,MATCH(D12,C3:C8,0))

Công thức này, hàm INDEX có nhiệm vụ:

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 ở trên

Giao điểm giữa dòng và cột trong hàm INDEX tìm chính là kết quả email cần tìm.

Bài tập 3: Dò tìm từ phải qua trái với hàm INDEX kết hợp MATCH

Thông thường chúng ta hay dò tìm theo chiều từ Trái qua Phải. Nhưng nếu phải tìm từ Phải qua Trái (ngược lại với thông thường) thì làm thế nào?

Ví dụ như sau: Tìm tên dựa vào thông tin Email?

Như vậy để dò tìm họ tên dựa vào Email tức là chúng ta đang tìm từ Phải qua trái.

Quá trình thực hiện như sau:

Dùng hàm MATCH để tìm vị trí dòng của email trong cột Email =MATCH(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))

Rất đơn giản phải không nào.

Bài tập 4: Xác định đơn giá của mặt hàng theo nhiều điều kiện

Ví dụ ta có bảng đơn giá như sau:

Điều này tương đương việc chúng ta phải xét đồng thời 2 điều kiện: Mã hàng và Mã công ty

Để thực hiện yêu cầu này, chúng ta sẽ sử dụng hàm Index kết hợp hàm Match như sau:

Tham chiếu giá trị Mã hàng trong dòng 3 (dòng chứa thông tin mã hàng làm căn cứ đối chiếu)

Hàm MATCH trả về kết quả là 2, tương ứng với cột thứ 2

=MATCH(H2,A3:E3,0)

Tham chiếu giá trị Mã công ty trong cột A (cột chứa thông tin mã công ty làm căn cứ đối chiếu)

Hàm MATCH trả về kết quả là 3, tương ứng với dòng thứ 3

=MATCH(H3,A3:A7,0)

Như vậy giao điểm của dòng 3, cột 2 chính là ô B5 (bởi bảng bắt đầu tính từ dòng 3 trở đi, nên dòng 5 ứng với vị trí dòng thứ 3 của bảng đơn giá)

Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX

Việc còn lại là dùng hàm INDEX để kết nối giữa Dòng và Cột đã xác định được ở trên.

Thay vì tham chiếu tới 1 cột cố định tại tham số đầu tiên, chúng ta tham chiếu cho cả vùng bảng A3:E7.

Trong bảng này, số dòng xác định bởi hàm MATCH tại ô I3, số cột xác định bởi hàm MATCH tại ô I2.

Kết quả của hàm INDEX là giao điểm giữa số dòng và số cột đã xác định được.

=INDEX(A3:E7,I3,I2)

Như vậy thông qua 4 bài tập ví dụ, 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. Khi ứng dụng tốt cách làm này, bạn hoàn toàn có thể thực hiện được những yêu cầu tìm kiếm phức tạp mà hàm VLOOKUP khó đáp ứng được.

Thay vì phải đắn đo sử dụng hàm VLOOKUP hay HLOOKUP, làm công thức dài dòng khi phải kết hợp VLOOKUP với hàm MATCH, HLOOKUP với hàm MATCH, thì chúng ta chỉ cần dùng INDEX kết hợp MATCH là xong.

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

Bài Tập Mẫu Hàm Vlookup Trong Excel (Có Lời Giải)

Bài này còn có ví dụ cho việc áp dụng hàm VLOOKUP kết hợp với các làm trích kí tự LEFT, RIGHT để dò tìm và lấy giá trị từ một bảng cho trước.

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 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.

Bảng dữ liệu bài tập

Câu 1) Dựa vào kí tự đầu và 2 kí tự cuối của “Mã HĐ”, tìm trong “Bảng Danh Mục” đề lấy tên mặt hàng điền vào cột “Mặt Hàng”.

Câu 2) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn vị”

Câu 3) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn giá”

Câu 4) Tính cột “Thành tiền” bằng “Đơn giá” nhân “SLượng”

Câu 5) Tính và điền giá trị vào bảng “Tổng số tiền bán được của các mặt hàng”

Câu 1:

Tại ô C4, gõ hàm:

Ý nghĩa là: Lấy bên trái của B4 1 kí tự rồi ghép với 2 kí tự bên phải, đem tìm cụm này trong “Bảng Danh Mục” theo chiều dọc và lấy kết quả là giá trị ở cột thứ 2 của bảng. Tìm chính xá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:

Tại ô E4, gõ hàm:

Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 3 của bảng (Tương ứng là cột Đơn vị).

Câu 3:

Tại ô G4, gõ hàm:

Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 4 của bảng (Tương ứng là cột Đơn giá).

Câu 4:

Tại ô H4, gõ công thức:

=F4*G4

Ý nghĩa: Lấy giá trị cột F4 nhân cột G4.

Câu 5:

– Tại ô B29, gõ hàm:

= SUMIF(B4:B13, “S*M?”, H4:H13)

Ý nghĩa: Tính tổng doanh số của tất cả các hóa đơn bán đĩa mềm. Đây là tính tổng có điều kiện, vì vậy ta dùng SUMIF. Điều kiện là hóa đơn bán đĩa mềm, tức là các “Mã HĐ” sẽ có dạng

Vì vậy công thức trên có ý nghĩa là tìm trong cột B4:B13, Mã HĐ nào có dạng S*M? thì cộng các giá trị tương ứng trong cột H4:H13

– Tương tự, tại ô C29, gõ hàm:

– Tương tự, tại ô D29, gõ hàm:

Password giải nén là:

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 Sử Dùng Hàm Thời Gian Year, Date, Month Trong Excel Có Bài Tập

Hàm YEAR để lấy giá trị Năm từ một đối tượng thời gian

Để lấy được giá trị Số năm từ một đối tượng thời gian thì chúng ta có thể sử dụng hàm YEAR trong Excel.

Cấu trúc của hàm YEAR

=YEAR(serial_number)

Trong đó serial_number là một giá trị về thời gian, là dữ liệu dạng số, không phải dạng Text

Như vậy chúng ta thấy: Dù giá trị thời gian viết dưới định dạng nào thì hàm YEAR vẫn lấy đúng số Năm trong giá trị thời gian đó.

Ví dụ: Để xác định năm hiện tại là năm nào thì chúng ta kết hợp hàm YEAR với hàm TODAY như sau:

=YEAR(TODAY())

Kết quả của hàm YEAR ra một giá trị có loại dữ liệu là dạng Number, không phải dạng thời gian.

Hàm MONTH để lấy giá trị Tháng từ một đối tượng thời gian

Để lấy được giá trị Số Tháng từ một đối tượng thời gian thì chúng ta có thể sử dụng hàm MONTH trong Excel.

Cấu trúc của hàm MONTH

=MONTH(serial_number)

Trong đó serial_number là một giá trị về thời gian, là dữ liệu dạng số, không phải dạng Text

Xét một số ví dụ áp dụng hàm tách số tháng từ 1 giá trị thời gian như sau:

Kết quả của hàm MONTH ra một giá trị có loại dữ liệu là dạng Number, không phải dạng thời gian.

Hàm DAY để lấy giá trị Ngày từ một đối tượng thời gian

Tương tự như hàm YEAR, hàm MONTH ở trên, hàm DAY sẽ giúp chúng ta xác định số Ngày từ một giá trị thời gian trong Excel.

Cấu trúc hàm DAY

=DAY(serial_number)

serial_number là một giá trị thời gian, dữ liệu dạng số.

Kết quả của hàm DAY ra một giá trị có loại dữ liệu là dạng Number, không phải dạng thời gian.

Hàm DATE để tạo ra 1 giá trị thời gian từ việc ghép các đối tượng Năm, Tháng, Ngày

Để tạo ra một giá trị thời gian thì chúng ta phải dùng hàm DATE. Hàm Date giúp xác định ra một giá trị thời gian có đầy đủ Ngày, Tháng, Năm và được viết đúng định dạng chuẩn là Thời gian.

Cấu trúc của hàm DATE

=DATE(year, month, day)

Một số ví dụ về việc ứng dụng hàm Date để tạo ra giá trị biểu diễn thời gian:

Trong kết quả tại D4 và D5 có sử dụng hàm IF để biện luận giá trị năm. Nếu số năm nhỏ hơn hoặc bằng 18 (năm hiện tại là 2024) thì sẽ coi năm đó thuộc năm 2000 trở đi. Còn không phải thì sẽ tính cho những năm thuộc 1900 tới trước 2000

Kết quả tại D3 có sử dụng hàm TEXT kết hợp hàm DATE để thay đổi cách định dạng giá trị ngày theo ý muốn.

Để có thể làm tốt bài tập trên, các bạn cần tìm hiểu thêm một số kiến thức về các hàm: VLOOKUP, CHOOSE, SUMIFS, WEEKDAY

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,…

Cập nhật thông tin chi tiết về Hướng Dẫn Giải Bài Tập Excel Có Dùng Hàmvlookup, Hàm If, Hàm Sum 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!