Bạn đang xem bài viết Hướng Dẫn Cách Dùng Hàm Sumifs Đơn Giản Qua Bài Tập được cập nhật mới nhất tháng 11 năm 2023 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.
Mô tả hàm SUMIFSHàm SUMIFS() là hàm tính tổng các ô trong vùng, phạm vi lựa chọn thỏa mãn với nhiều điều kiện khác nhau.
Cú pháp của hàm SUMIFS=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)
Trong đó:
– sum_range: bắt buộc Là các ô cần tính tổng bao gồm giá trị số. Các ô chứa giá trị trống, giá trị là chữ, văn bản sẽ được bỏ qua.
– criteria_range1: bắt buộc Là phạm vi cần tính được kiểm tra điều kiện criteria1.
– criteria1: bắt buộc Điều kiện dưới dạng một số, biểu thức, tham chiếu ô được áp dụng cho criteria_range1.
– criteria_range2, criteria2, … : tùy chọn Là các phạm vi và điều kiện bổ sung. Hàm SUMIFS cho phép tối đa 127 cặp criteria_range, criteria.
Những điều cần chú ý khi dùng hàm SUMIFS
Các criteria_range phải có cùng kích thước vùng chọn giống với đối số sum_range, tức là criteria_range và sum_range phải có số hàng và số cột giống nhau.
Các ô trong sum_range nếu có giá trị là TRUE sẽ được định nghĩa giá trị là 1 và nếu ô có giá trị là FALSE sẽ được định nghĩa giá trị là 0 (không).
Điều kiện criteria có thể chứa các ký tự đại diện như sau: dấu hỏi chấm (?) sẽ thay cho ký tự đơn, dấu sao (*) sẽ thay cho một chuỗi. Nếu trong điều kiện criteria muốn đặt là dấu hỏi chấm (?) hoặc dấu sao (*) thì hãy nhập thêm dấu ~ ở trước.
Nếu các giá trị của điều kiện (criteria) ở dạng văn bản thì cần để trong dấu ngoặc kép ” “.
Ví dụ về hàm SUMIFS trong Excel số 1Để nắm rõ hơn về cách dùng hàm SUMIFS trong Excel chúng ta sẽ cùng thực hiện ví dụ thực tế như bên dưới. Chúng ta có bảng Excel như sau:
Yêu cầu tính toán
Yêu cầu tính toán số 1: Tính tổng số lượng sản phẩm từ nhà cung cấp Adayroi với giá bán trên 6.000.000đ
Yêu cầu tính toán số 2. Tính tổng số lượng sản phẩm từ nhà cung cấp Lazada trừ sản phẩm Samsung S8
Giải yêu cầu số 1
Vùng cần tính tổng là cột Số lượng: D4:D13
Phạm vi cần kiểm tra điều kiện 1 là cột Nhà cung cấp: B4:B13
Điều kiện 1 là nhà cung cấp Adayroi ở dạng văn bản: “Adayroi”
Phạm vi cần kiểm tra điều kiện 2 là cột Giá bán: E4:E13
Đây là kết quả:
Giải yêu cầu 2
Vùng cần tính tổng là cột Số lượng: D4:D13
Phạm vi cần kiểm tra điều kiện 1 là cột Nhà cung cấp: B4:B13
Điều kiện 1 là nhà cung cấp Lazada ở dạng văn bản: “Lazada”;
Phạm vi cần kiểm tra điều kiện 2 là cột Tên sản phẩm: C4:C13
Đây là kết quả
Ví dụ về hàm SUMIFS trong Excel số 2
Cho bảng dữ liệu sau đây:
Yêu cầu 1. Tính tổng số lượng sản phẩm đã được bán bởi nhân viên tên Nga với STT < 5.
Yêu cầu 2. Tính tổng số lượng sản phẩm đã được bán bởi nhân viên tên Hương với tất cả các sản phẩm trừ “Na”.
Tương tự cách sử dụng hàm SUMIFS ở ví dụ 1,Kết quả sau khi áp dụng hàm SUMIFS() ở ví dụ này như sau:
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ênKhi 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 MATCHThô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ệnVí 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 Hàm Sumifs L Bài Tập Thực Hành Hàm Sumifs L Cú Pháp Hàm Sumifs
Bài tập hàm sumifs – Hàm sumifs có thể coi như Hàm sumif có 2 điều kiện hoặc nhiều điều kiện, sử dụng hàm sumif trong excel 2003 giống như excel 2007 2010. Tuy nhiên chỉ có phiên bản excel 2007 trở đi mới có thể dùng hàm SUMIFS – đây chính là sự khác biệt lớn các bạn cần nắm được.
Tóm tắt nội dung chính:
1. Định nghĩa và cú pháp hàm
2. Bài tập cơ bản
3. Bài tập nâng cao: Tips + Link tải
1. Định nghĩa và cú pháp hàm SUMIFS (hàm sumif có nhiều điều kiện)Định nghĩa:
Hiểu đơn giản thì Hàm sumifs là hàm cho phép tính tổng theo nhiều điều kiện khác nhau trong cùng một công thức
Cú pháp hàm:
= Sumifs (Vùng tính tổng, Vùng điều kiện 1, Điều kiện 1, Vùng điều kiện 2, Điều kiện 2, …)
Bài viết ad vừa giới thiệu tới các bạn sẽ cung cấp cho các bạn đầy đủ kiến thức từ lý thuyết, ví dụ thực hành, bài giải chi tiết. Đảm bảo các bạn sẽ hiểu cặn kẽ về một trong những hàm excel quan trọng.
2. Bài tập thực SUMIFS (Cơ bản)
Yêu cầu đưa ra của bài: Tính tổng doanh thu mặt hàng B trong ngày 01/01/2023.
Phân tích yêu cầu đề bài để vận dụng cách sử dụng hàm sumifs như sau:
Vùng tính tổng (sum_range): Cột doanh số
Vùng điều kiện 1: Cột mặt hàng
Điều kiện 1: Mặt hàng “B”
Vùng điều kiện 2: Cột ngày bán
Điều kiện 2: Ngày 01/01/2023.
Và bây giờ bạn sẽ biết là phải làm như thế nào để hoàn tất yêu cầu của đề bài rồi đúng không. Hãy email cho ad tại hòm thư: webkynan[email protected] nếu bạn gặp khó khăn.
3.1. Hướng dẫn nhanh các hàm sử dụng để giải bài tập
Trong bài tập này chúng ta sẽ phải dùng những hàm chính sau đây để tính toán các chỉ tiêu, tìm kiếm thông tin,…:
– Hàm Vlookup: Hàm tìm kiếm theo cột
– Hàm Hlookup: Hàm tìm kiếm theo dòng
– Hàm Mid: Hàm tách ký tự từ một chuỗi text
– Hàm vlookup hoặc hàm hlookup kết hợp hàm if: Sử dụng để tìm giá trị theo nhiều điều kiện khác nhau.
Bai tap thuc hanh ham excel – hlookup – sumif – sumifs – if
Pass giải nén: chúng tôi
Cách Dùng Hàm Sum, Sumif, Sumifs Qua Ví Dụ Hay Nhất
Có một điều chắc chắn rằng, hàm SUM, SUMIF, SUMIFS là những hàm tính tổng trong Excel được sử dụng rất phổ biến trong học tập, công việc. Đây đều là các hàm cơ bản trong Excel. Do vậy, việc hiểu được công thức, cách dùng của những hàm này sẽ rất có ích cho bạn sau này. Nếu bạn mới làm quen với phần mềm Excel, hoặc đang tìm hiểu về các hàm Excel cơ bản, thì đây là một bài viết rất hữu ích với bạn.
Trong bài viết này, Đỗ Bảo Nam Blog chia sẻ chi tiết với bạn về công thức, cách sử dụng của những hàm này qua ví dụ minh họa cực hay và dễ hiểu. File bài tập thực hành bạn có thể download ở phía cuối bài viết này. Những ví dụ về hàm SUMIF, hàm SUMIFS và hàm SUM được Đỗ Bảo Nam Blog thiết kế rất khoa học, dễ hiểu. Các ví dụ này được thiết kế dựa trên bảng thống kê bán hàng (cơ bản). Đây là dạng bảng bạn sẽ gặp thường xuyên trong quá trình học tập và làm việc.
Video hướng dẫn cách dùng hàm SUM, SUMIF, SUMIFS qua ví dụ Video rất hay chia sẻ về các hàm tính tổng trong Excel. File bài tập về các hàm này bạn có thể download ở phía dưới bài viết. Ví dụ về cách sử dụng hàm SUMIF, SUMIFS và hàm SUM Cách dùng hàm SUM trong ExcelHàm SUM là một trong những hàm cơ bản nhất trong Excel. Đây là hàm toán học dùng để tính tổng trong Excel. Bạn có thể dùng hàm này khi muốn tính tổng của các số, dãy số khi cần thiết…
Công thức hàm SUM trong ExcelCông thức: SUM(số thứ nhất, [số thứ hai],…)Trong đó, số thứ nhất, số thứ hai… là các giá trị cần tính tổng. Và chắc chắn khi đã dùng hàm, thì đối số thứ nhất, tức là số thứ nhất phải là bắt buộc. Các giá trị khác của hàm này là tùy chọn. Và khi dùng hàm SUM, bạn chỉ cần xác định tính tổng của những số nào hay dãy số nào.
Tất cả các hàm gắn với chữ “if” là đều có điều kiện kèm theo. Và hàm SUMIF cũng vậy. Nói một cách dễ hiểu nhất, hàm SUMIF là hàm tính tổng có điều kiện trong Excel. Hàm này được dùng khi bạn muốn tính tổng của các số khi thỏa mãn một điều kiện nào đó.
Hàm cuối cùng trong bài viết này mà Đỗ Bảo Nam Blog chia sẻ là hàm SUMIFS. Khi gắn với chữ “S” ở đằng sau, thì hàm này trở thành hàm tính tổng có nhiều điều kiện. Về cơ bản, đây là hàm tính tổng có điều kiện, và bạn có thể sử dụng nhiều điều kiện trong hàm này. Và trong thực tế, hàm SUMIFS trong Excel cũng được sử dụng rất rộng rãi trong thực tế. Vì vậy ngay bây giờ, bạn hãy trang bị cho mình những kiến thức về các hàm tính tổng để sử dụng trong công việc, học tập.
Công thức hàm SUMIFS trong ExcelSUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Hay viết một cách dễ hiểu hơn, công thức của hàm tính tổng có điều kiện SUMIFS sẽ có dạng như sau:SUMIFS(vùng cần tính tổng, vùng điều kiện 1, điều kiện 1,[vùng điều kiện 2, điều kiện 2], …)Trong công thức này, vùng cần tính tổng là vùng chứa các giá trị số cần để tính tổng. Vùng điều kiện 1 là vùng chứa điều kiện 1. Điều kiện 1 là điều kiện để tính tổng theo một tiêu chí nào đó. Vùng điều kiện 2, điều kiện 2 (tùy chọn) là các phạm vi bổ sung và các tiêu chí liên kết với chúng. Bạn có thể nhập tối đa 127 cặp phạm vi/tiêu chí.
Cách dùng hàm SUMIFS trong Excel qua ví dụ minh họaDownload bài tập hàm SUMIF, SUMIFS
Hướng Dẫn Giải Bài Tập Excel Có Dùng Hàmvlookup, Hàm If, Hàm Sum
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ãiTrong 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ềnCó 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:
Cách Dùng Hàm Vlookup Trong Excel L Bài Tập Và Video Hướng Dẫn
Trong bài viết này, Trường sẽ tập trung vào 02 điểm chính sau:
Cú pháp và cách sử dụng hàm
Giải Bài tập ví dụ áp dụng để tìm đơn giá nhằm lập bảng nhập xuất nông sản.
Link download bài tập thực hành
Các bạn chỉ cần dành thời gian đọc và thực hành bài tập được giao, ắt hẳn sẽ thành thạo. Mọi ý kiến góp ý hoặc cần hỗ trợ, bạn gửi cho Trường tại: [email protected]
1. Định nghĩa và cú pháp hàm VlookupHàm Vlookup là hàm tìm kiếm theo cột.
Vlookup ( [Lookup_value] ; [Table_array] ; [Col_index_num] ; [Range_lookup])
Lookup_value: Giá Trị dùng để tìm kiếm
Table_array: Vùng điều kiện
Col_index_num: Cột chỉ định hay còn được gọi là số thứ tự cột chứa giá trị được tìm kiếm tính từ cột đầu tiên của vùng dữ liệu
Range_lookup: Kiểu tìm kiếm – gồm có 2 kiểu 0 và 1
Mời bạn xem bài: Cách tìm kiếm dữ liệu từ sheet, file khác
2. Ví dụ thực tế về cách dùng hàm vlookup trong excel 2007 2010 2013Ví dụ cách sử dụng hàm tìm kiếm trong Excel
Yêu cầu: Căn cứ vào Bảng tồn kho đầu kỳ (bảng đầu tiên). Hãy hoàn thành Bảng kê Mua – Bán hàng trong kỳ (bảng thứ 2).
2.1. Phân tích yêu cầu và dữ liệu để vận dụng – kết hợp hàmĐể hoàn thành được Bảng kê Mua – Bán hàng thì chúng ta cần phải tìm được Đơn giá nhập và Đơn giá xuất.
Sau đó căn cứ vào số lượng bán hàng từng lần chúng ta sẽ tính được Trị giá Nhập và Xuất của từng lần giao dịch.
Chúng ta có thể tóm tắt mẫu biểu và yêu cầu sau:
Như vậy, các dữ liệu về tên, đơn giá nhập – xuất của từng mặt hàng chúng ta có thể lấy từ Bảng Tồn kho đầu kỳ.
Phần còn lại là lấy SL Nhập Xuất rồi nhân với đơn giá nhập – xuất tương ứng.
Tên hàng được sử dụng trong bảng nhập – xuất hàng đều giống với tên được sử dụng trong Bảng Tồn kho đầu kỳ.
Bên cạnh đó, tên các mặt hàng và đơn giá với mặt hàng tương ứng đều ở dạng cột. Do đó để có thể lấy đơn giá nhập – xuất từ bảng tồn kho đầu kỳ để nhập vào bảng nhập xuất hàng trong kỳ thì chúng ta hãy sử dụng Hàm tìm kiếm theo cột
=Vlookup ( Điều kiện để tìm kiếm, Vùng tìm kiếm, Số thứ tự cột chứa giá trị tìm kiếm, kiểu tìm kiếm)
Điều kiện để tìm kiếm: Tên hàng bên bảng nhập – xuất hàng ví dụ: Bạc hà, cam, chanh.
Vùng điều kiện: Chính là vùng chứa dữ liệu để ta dùng công thức lọc ra. Đó là toàn bộ Bảng tồn kho đầu kỳ cột bắt đầu là cột tên hàng. Vì ta đang dùng tên hàng là điều kiện tìm kiếm nên cột tên hàng phải là cột đầu tiên trong vùng tìm kiếm
Số thứ tự cột chứa giá trị tìm kiếm: Tính từ cột tên hàng trong vùng tìm kiếm.
Kiểu tìm kiếm: 0 là kiểu tìm kiếm chính xác 100% và là kiểu thường đc sử dụng hơn là kiểu 1.
Qua phân tích, thì các thành phần của hàm Vlookup dùng để lấy đơn giá nhập từ bảng tồn kho đầu kỳ (Vùng tìm kiếm) là:
4. Lời giải cho cách sử dụng hàm tìm kiếm theo cột để tìm đơn giá nhập – xuấtTa đánh vào ô E13 bên bảng nhập xuất để tìm đơn giá nhập theo tên hàng hóa, Công thức như sau
=Vlookup (“Bạc Hà”, $B$4:$F$9, 4, 0)
Trong đó từ Bạc Hà chúng ta có thể thay bằng địa chỉ ô chứa giá trị “Bạc hà” trong cột tìm đơn giá nhập
Vlookup (“Bạc Hà”, $B$4:$F$9, 4, 0) =Vlookup (C13, $B$4:$F$9, 4, 0)
Giải thích cách sử dụng hàm
Hàm vlookup được dùng rất nhiều trong công việc của kế toán. Do vậy Trường đã dành thời gian ghi lại video hướng dẫn cách dùng hàm vlookup trong file kế toán excel.
Các bài tập trong file trên chủ yếu phục vụ việc luyện tập cách dùng hàm vlookup của bạn.
Tuy nhiên đây là các bài tập thực tế do đó cần phải có tương đối đầy đủ các yêu cầu. Điều này khiến chúng ta phải sử dụng nhiều hàm khác cùng một lúc; Ví dụ: IF, Left, Mid,…
Đó có thể là bài toán về tổng hợp doanh thu bán hàng, tính lương nhân viên. Hoặc tính tiền điện với bảng định mức – đơn giá áp dụng cho từng trường hợp.
Cách sử dụng hàm vlookup trong các phiên bản excel 2003, 2007, 2010, 2013 đều không có gì khác nhau.
Có thể bạn cũng quan tâm:
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Dùng Hàm Sumifs Đơn Giản Qua Bài Tập 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!