Cập nhật thông tin chi tiết về Tra 2 Điều Kiện Với Hàm Lookup Trong Excel mới nhất trên website Trucbachconcert.com. 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.
Phải làm việc thường xuyên với Excel thì hẳn là ai cũng phải biết đến hàm Vlookup, hàm hành động tra cứu dọc chính xác và là hàm tích hợp trong Excel, có khả năng như một bảng tính hay là một hàm trang tính được sử dụng phổ thông cực kì được ưa dùng trong Excel. Tuy vậy hàm này cơ bản được tốt lên và được sử dụng nâng cao hơn là hàm Vlookup trong Excel với 2 điều kiện, hàm được sử dụng để xử lý công việc yêu cầu cao hơn trong việc tìm kiếm.
Hàm Vlookup trong Excel là gì?
Hàm Vlookup là hàm dùng để tìm kiếm các giá trị trong Excel và trả về kết quả theo hàng dọc. không chỉ có vậy, hàm này còn được sử dụng để tổng hợp và thống kê, dò tìm dữ liệu rất nhanh chóng tiện lợi mà không mất quá nhiều thời gian và công sức.
Bí quyết dùng hàm Vlookup với 2 điều kiện
Sử dụng hàm Vlookup trong Excel trong hoàn cảnh tạo cột phụ
Thường thì chúng ta sẽ tìm bằng việc sử dụng hàm Vlookup tuy nhiên chỉ tìm được với 1 điều kiện thôi, không tìm được theo 2 điều kiện, tuy nhiên sử dụng hàm Vlookup 2 điều kiện con người có thể tìm kiếm một cách giản đơn, bằng việc tạo thêm một cột phụ mới ở trước cột mã sản phẩm, như ở bảng trên thì cột phụ đặt là cột hàng A: 1. Nhập công thức tại cột phụ để ghép các mã mặt hàng với ca ở từng các hàng cột:
2. Hành động việc tìm sản lượng của mặt hàng theo ca, sản sinh ra một bảng tìm riêng:
Nhập mã và ca còn sản lượng để thực thi việc nhập công thức để tìm, đối với việc tìm sản lượng của mã A và ca 1 thì nhập công thức tại ô G6:
Việc dùng công thức mảng đừng nên biết nhiều bởi cách dùng có vẻ hơi khó khăn, tuy nhiên dùng công thức mảng không chỉ chỉ tìm được với 2 điều kiện cho trước mà còn sử dụng với nhiều điều kiện. – Dùng hàm bằng cách tạo công thức mảng không chỉ riêng dùng với mỗi hàm Vlookup mà sử dụng cùng với hàm Choose. – Hàm Choose cũng được dùng để tìm kiếm, hàm sẽ tìm kiếm 1 thành quả trong một danh sách các giá trị không giống nhau. Khi sử dụng hai hàm kết hợp để tìm sản lượng theo 2 tiêu chí ở ví dụ trên thì ở phần này con người không cần sử dụng cột phụ và nhập trực tiếp công thức:=VLOOKUP(G3&G4;CHOOSE(12;(B2:B6)&(C2:C6);D2:D6);2;0)
Tùy vào từng máy mà có khả năng dấu dùng dấu ( ; ) hay dấu ( , ) một khi nhập hàm công thức xong nhấn tổ hợp Ctrl + Shift + Enter:
Lưu ý
Tuy nhiên dùng cách để tạo công thức mảng có thể sử dụng mà không cần tạo cột phụ, nhưng nhập công thức quá dài và khó nhớ làm cho dễ nhầm lẫn. Cách dùng hàm Vlookup nâng cao này có thể dùng cho 2 bí quyết là tạo bảng và dùng công thức mảng để tìm kiếm có điều kiện, hay tìm kiếm nhiều điều kiện. Độc giả có khả năng sử dụng 1 trong 2 cách tùy theo dùng cách nào thấy hợp lý cho công việc. Có thể các bạn sẽ tìm được lời giải khác nhanh hơn và hay hơn cho hàm tìm kiếm với nhiều các điều kiện.
Excel Vlookup với nhiều tiêu chí:
Hàm VLOOKUP thực sự có ích khi tìm kiếm một thành quả chắc chắn trên một cơ sở dữ liệu. Tuy nhiên, nó có điểm làm giảm lớn – cú pháp của nó chỉ cho phép tra cứu một giá trị. Nếu bạn muốn tìm kiếm theo nhiều điều kiện thì sao? Ta có phương pháp phía dưới.
Một công thức VLOOKUP bình thường sẽ không xử lý được trong hoàn cảnh này, bởi nó trả về giá trị tìm thấy trước tiên hợp lý với giá trị tra cứu mà bạn chỉ định. Do đó, nếu bạn muốn được biết số lượng “Sweets” được sắp xếp bởi “Jeremy Hill” và viết công thức =VLOOKUP(B1,$A$5:$C$14,3,FALSE), hậu quả trước tiên tìm thấy trả về là “15” tương ứng với “Apples”.
Phương pháp đơn giản là tạo một cột cung cấp nối tất cả các tiêu chí mà bạn muốn, trong ví dụ này là các cột Customer và Product . Hãy nhớ rằng cột liên kết luôn luôn là cột ngoài cùng bên trái trong phạm vi tra cứu của bạn vì đây chính là nơi mà VLOOKUP luôn tìm kiếm thành quả tra cứu.
Do đó, bạn thêm một cột phụ vào bảng của bạn và sao chép một công thức như thế này =B2&C2 qua cột đấy (hoặc =B2&” “&C2 nếu như bạn muốn tách các thành quả được nối bằng khoảng trắng để giúp cho dữ liệu dễ đọc hơn).
Và sau đó, bạn có thể sử dụng một công thức VLOOKUP giản đơn như sau:
=VLOOKUP(“Jeremy Hill Sweets”,$A$5:$C$14,3,FALSE)
hoặc là
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
Trong đó B1 chứa giá trị tra cứu ( lookup_value) và 3 là số cột chứa dữ liệu bạn mong muốn tìm ( col_index_num).
Ứng dụng CỦA HÀM LOOKUP NHƯ THẾ NÀO?
Thành quả tìm kiếm (lookup_value) là: 2, vì sao lại có số 2? Giản đơn vì số 2 hoặc bất kỳ số nào lớn hơn 1 đều được. Bạn sẽ hiểu hơn ở trình bày tiếp theo.
Và cuối cùng, là vùng hậu quả trả về, bạn có thể thu thập kết quả là giá trị của ô, hoặc là vị trí của dòng đấy với hàm ROW(), hoặc COLUMN() để lấy ra cột, tùy thuộc theo áp dụng của bạn.
Lưu ý: Vùng tìm kiếm và vùng giá trị trả về cần có cùng số dòng, ví dụ: A1:A100, thì vùng kết quả cũng phải tương tự B1:B100.
Lộc Đạt-Tổng hợp
Hàm Đếm Có Điều Kiện Trong Excel
Mô tả
Hàm COUNTIF là hàm đếm có điều kiện trong Excel, các bạn có thể đếm dữ liệu dựa trên điều kiện chỉ định sẵn.
Cú pháp
=COUNTIF(range;criteria)
Trong đó range là bắt buộc, là vùng mà các bạn muốn đếm dữ liệu. Nó có thể chứa số, mảng hoặc tham chiếu có chứa số… Các giá trị trống được bỏ qua.
Criteria (bắt buộc), đây là điều kiện để đếm các giá trị trong range, nó có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.
Nếu các bạn muốn đếm với nhiều điều kiện thì các bạn sử dụng hàm COUNTIFS. (chèn link http://thuthuatphanmem.vn/ham-countifs-trong-excel/ vào tên COUNTIFS giúp mình)
Lưu ý
Hàm COUNTIF trả về kết quả sai khi bạn sử dụng điều kiện với các chuỗi dài hơn 255 ký tự.
Đối số criteria cần ghi trong dấu ngoặc kép.
Criteria không phân biệt chữ hoa/thường.
Có thể sử dụng ký tự đại diện dấu chấm hỏi (?) và dấu sao (*) trong điều kiện criteria (1 dấu hỏi tương ứng với 1 ký tự, 1 dấu sao tương ứng với một chuỗi ký tự). Nếu các bạn muốn sử dụng dấu chấm hỏi hay dấu sao không phải làm ký tự đại diện thì các bạn cần thêm ký tự ~ phía trước.
Ví dụ
Cho bảng số liệu như sau:
1. Đếm số lượng đơn hàng có tên mặt hàng là Cam.
Để thực hiện yêu cầu này các bạn nhập công thức hàm CountIf như sau:
=COUNTIF(B6:B14;”Cam”)
Kết quả các bạn sẽ thấy có 3 đơn hàng có mặt hàng là Cam.
2. Đếm số lượng đơn hàng có tên mặt hàng không phải là Cam.
4. Đếm số đơn hàng có tên mặt hàng là Cam sử dụng ký tự thay thế *
Thay vì các bạn nhập Cam trong điều kiện hàm CountIf thì các bạn có thể nhập C* để đếm.
=COUNTIF(B6:B14;”C*”)
Kết quả các bạn cũng được 3 đơn hàng:
5. Đếm số lượng đơn hàng có tên mặt hàng khác ô B7 (Cam).
Các bạn có thể sử dụng ký tự “&” trước ô tham chiếu trong điều kiện hàm CountIf như sau:
Cách Dùng Hàm Dsum Để Tính Tổng Với Điều Kiện Phức Tạp Trong Excel
Hướng dẫn tính tổng có điều kiện bằng DSUM
Hàm DSUM là hàm cộng các số trong một trường, cột trong danh sách dữ liệu thỏa mãn với các điều kiện xác định. Cú pháp hàm là DSUM =DSUM(database; field; criteria).
Trong đó:
Database là đối số bắt buộc, là cơ sở dữ liệu được tạo từ 1 phạm vi ô. Danh sách dữ liệu này sẽ chứa các dữ liệu là các trường, gồm trường để kiểm tra điều kiện và trường để tính tổng. Danh sách chứa hàng đầu tiên là tiêu đề cột.
Field là đối số bắt buộc chỉ rõ tên cột dùng để tỉnh tổng các số liệu. Có thể nhập tên tiêu đề cột trong dấu ngoặc kép, hoặc dùng 1 số thể hiện vị trí cột trong danh sách không dùng dấu ngoặc kép, hay tham chiếu tới tiêu đề cột muốn tính tổng.
Criteria là đối số bắt buộc, phạm vi ô chứa điều kiện muốn hàm DSUM kiểm tra.
Lưu ý người dùng
Có thể dùng phạm vi bất kỳ cho criteria nếu phạm vi đó chứa ít nhất 1 nhãn cột và ít nhất 1 ô bên dưới tiêu đề cột mà sẽ xác định điều kiện cho cột đó.
Không đặt phạm vi điều kiện ở phía dưới danh sách vì sẽ không có vị trí thêm các thông tin khác vào danh sách.
1. Tính tổng số tiền đã bán của sản phẩm iPhone Bước 1:
Trước hết chúng ta tạo phạm vi điều kiện cho hàm DSUM để tính tổng số tiền đã bán của các sản phẩm iPhone. Điều kiện cho hàm DSUM là iPhone*.
Bước 2:
Tiếp đến người dùng nhập công thức hàm DSUM là =DSUM(A6:E13;”Thành Tiền”;C2:C3).
Trong đó:
A6:E13 là phạm vi cơ sở dữ liệu chứa cột cần tính tổng và cột chứa điều kiện cần kiểm tra.
“Thành Tiền” là tiêu đề cột sẽ sử dụng giá trị trong cột đó để tính tổng.
C2:C3 là phạm vi điều kiện chứa tiêu đề cột và 1 giá trị điều kiện.
Nhấn Enter và chúng ta sẽ được kết quả chính xác như hình.
Hoặc người dùng có thể thay giá trị trong Field thành tham chiếu tới cột Thành tiền. Công thức nhập là =DSUM(A6:E13;E6;C2:C3). Kết quả cũng tương tự như khi bạn nhập cột Thành tiền vào công thức.
Bước 2:
Chúng ta nhập công thức hàm là =DSUM(A6:E13;E6;C2:C3) và nhấn Enter.
Kết quả tổng số tiền sẽ như trong hình.
Nhìn chung cách sử dụng hàm DSUM trên Excel vô cùng đơn giản. Bạn có thể sử dụng hàm để tính tổng các giá trị theo cột với điều kiện đơn giản. Với những điều kiện phức tạp hơn chúng ta có thể sử dụng hàm SUMIF.
Hàm Sumproduct Trong Excel: Nâng Cao &Amp; Nhiều Điều Kiện (Pro Excel)
Cách sử dụng Hàm sumproduct trong excel với nhiều điều kiện nâng cao cùng 11 ví dụ hay (để đếm, để tính tổng,…)
Hơn 1 tỉ năm rồi Trường mới viết bài, nhưng một khi viết thì phải thật chất lượng và có chiều sâu. Minh chứng là một đề tài hot như thế này đã hoàn tất trong 2 ngày Trường miệt mài viết lách.
– Các ví dụ về so sánh mảng
– Tính tổng có điều kiện
– Đếm các ô thỏa mãn nhiều điều kiện
– Và nhiều nội dung hay khác
1. Hàm sumproduct là gì và cú pháp trong excel
Về cơ bản, hàm sumproduct là một hàm excel cho phép nhân các số trong các mảng (vùng) cụ thể rồi cộng các kết quả của các tích này để cho ra một kết quả cuối cùng.
Có thể hiểu đơn giả như sau:
Sumproduct = Sum ( Product )
Sumproduct là Tổng của các Tích Lưu ý:
Trên thực tế, đây là một hàm Khó ở cả 2 cấp độ:
– Khó hiểu để viết hàm
– Khó vận dụng để tính toán trong excel
Nhưng hàm này cực kỳ hiệu quả trong nhiều trường hợp khi bạn biết cách vận dụng.
= Sumproduct (Array1, Array2, Array3, …)
Array là mảng.
Lưu ý quan trọng về Array/ Mảng:
– Bạn có thể dùng tới 255 mảng trong excel 2007 trở đi, và 30 mảng đối với excel 2003
– Độ lớn các mảng phải giống nhau, ngược lại Sumproduct sẽ trả về kết quả lỗi #VALUE
– Bất kỳ dữ liệu nào có trong các mảng không phải dữ liệu số, hàm sẽ trả về kết quả là 0
2. Cách sử dụng hàm sumproduct nâng cao/ nhiều điều kiện.
2.1. Ví dụ về cách sử dụng cơ bản của hàm Tính tổng của tích
– Thêm cột Doanh thu
– Tính doanh thu cho từng lần bán
– Tính tổng doanh thu ở cuối cùng của danh sách hàng bán.
Nhưng làm như vậy sẽ hơi mất công.
Giải pháp:
Dùng hàm sumproduct để tính tổng của các tích (tích ở đây là số lượng * giá bán)
Cú pháp hàm tại trường hợp này như sau:
= Sumproduct (A2:A5, B2:B5) = Sum (A2*B2, A3*B3, A4*B4, A5*B5) = 135
Trong đó:
2.2. Cách sử dụng Hàm sumproduct nâng cao/ nhiều điều kiện
a/ Sử dụng hàm sumproduct để đếm với 1 điều kiện
Nhiệm vụ của ta là đếm xem có bao nhiêu giao dịch bán hàng có lượng bán thực tế ít hơn dự kiến.
Nếu không biết dùng hàm sumproduct thì bạn sẽ phải thêm cột phụ rồi dùng countif để đếm, hơi mất công cũng như sẽ khó làm khi bảng kê có cả nghìn giao dịch.
Còn với hàm sumproduct thì quá đơn giản như sau:
Chỉ trong nháy mắt là ra ngay.
b/ Hàm sumproduct với nhiều điều kiện dùng để đếm
Tiếp nối phần a của phần ứng dụng nâng cao hàm sumproduct. Ta thêm 1 điều kiện nữa khi đếm đó là:
Đếm số giao dịch của mặt hàng A thỏa mãn điều kiện Số lượng bán thực tế < dự kiến.
Thực sự là khó nhằn hơn rất nhiều,
= Sumproduct ((A2:A10 = “A”)*(B2:B10<C2:C10)) = 2
Dấu * sẽ giúp kết nối các điều kiện với nhau.
Đây là cách vận dụng rất phổ biến của hàm tính tổng của tích Sumproduct trong thực tế công việc.
3. 11 ví dụ hay về hàm sumproduct
Trên thực tế, khi bạn đi sâu vào công việc và nghiên cứu bạn sẽ còn thấy rất nhiều cách vận dụng khác của hàm này.
Trường xin trích dẫn lại 11 ví dụ hay về hàm sumproduct của một website chuyên về excel của nước ngoài. Các bạn hãy download về tự nghiên cứu và phát triển thêm.
Như vậy, Trường đã hướng dẫn xong các bạn cách sử dụng hàm Sumproduct trong excel.
Rất hi vọng rằng, những chia sẻ của Trường sẽ giúp ích nhiều cho các bạn.
Nếu gặp bất kỳ khó khăn nào bạn hãy email cho Trường tại hòm mail: Webkynang.vn@gmail.com
KHÓA HỌC HÀM EXCEL NÂNG CAO VỚI CAO THỦ.
Hoặc bạn nào có nhu cầu học nâng cao hơn nữa về Hàm sumproduct và các hàm excel khác dưới sự dẫn dắt, chỉ bảo của người có kinh nghiệm.
Trường sẽ tự vấn cụ thể cũng như sắp xếp thầy để hướng dẫn tận tình cho các bạn.
Đã khá muộn rồi, nên Trường cần phải đi ngủ đã, Trường rất quyết tâm để hoàn thành bài viết này sau một ngày dài Hỗ trợ khách hàng sử dụng Phần mềm Webkynang.vn.
Hẹn các bạn vào tuần tới, sẽ có thêm nhiều điều bất ngờ đang chờ đợi các bạn.
Thân,
Truongpx – Admin webkynang.vn
Bạn đang xem bài viết Tra 2 Điều Kiện Với Hàm Lookup Trong Excel trên website Trucbachconcert.com. 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. Chúc bạn một ngày tốt lành!