Cập nhật nội dung chi tiết về Tra 2 Điều Kiện Với Hàm Lookup Trong Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp 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
Cách Dùng Hàm Điều Kiện Hàm If Trong Excel
Excel là một phần phềm tính toán vô cùng hưu dụng cho tất cả mọi người thường xuyên phải sử dụng. Và nhất là sử dụng hàm IF để tính toán trong excel. Nếu bạn chưa biết cách sử dụng hàm IF thì hôm nay chúng tôi sẽ giúp các bạn hiểu hơn về hàm IF
Hướng dẫn sử dụng hàm IF trong excel
Cấu trúc của hàm IF
Cú pháp: =if(giá trị logic, giá trị trả về là đúng, giá trị trả về là sai)
Giá trị logic hay cũng chính là hàm điều kiện, đúng và sai cũng có thể hiểu là các giá trị nếu không là cái này thì là cái kia.
Ví Dụ hàm IF
Để hiểu rõ hơn về hàm IF ta có thể xem qua 1 ví dụ đơn giản sau đây
Theo bảng như hình vẽ ta điền vào cột thuế là có hay không. Nếu là VN thuế = 0 còn nhập khẩu thuế = 10%
Ta sẽ có công thức hàm điều kiện như sau: =IF(C3=”VN”,0,”10%”)
Sau khi đánh công thức và copy công thức ta sẽ có kết quả như trong hình.
Cách sử dụng hàm IF lông
ở ví dụ trên thì chúng ta sẽ dụng hàm IF với một điều kiện, vậy khi sử dụng hàm IF có nhiều điều kiến thì chúng ta phải làm như thế nào, lúc này thì chúng ta phải sử dụng hàm IF lông vào nhau để sử lý tình uống này.
Ta sử dụng hàm IF để điền vào cột xệp loại theo điều kiện như sau:
Để có thể điền được vào ô xệp loại theo điều kiện trên thì ta phải sử dụng hàm IF lồng vào nhau theo công thức như sau:
Các bạn Enter và copy công thức sẽ có được kết quả như trong hình
Với nhiều bạn khi sử dụng hàm IF lồng có thể thấy khó hiểu nhưng mình tin với ví dụ nhỏ trên có thể giúp các bạn hiểu được hàm IF lồng một cách dễ dàng hơn phải không nào.
Một điều các bạn cần chú ý là thứ tự cảu điều kiện cũng rất quan trọng, vì hàm excel sẽ kiểm tra điều kiện theo thứ tự khi chúng ta đánh công thức, và khi một điều kiện được đáp ứng thì kiều kiện sau đó không được đánh giá.
Hàm IF có thể lông tối đa được bao nhiêu hàm
Hàm IF lồng có thể lồng tới 7 hàm trong một công thức khi bạn sử dụng Excel 2003 và từ 2007 đến 2013 thì bạn có thể lông đến 64 hàm if trong cùng một công thức.
Nhưng mình nghĩ sử dụng nhiều hạm if như vậy sẽ khiến bạn rất dễ bị nhầm lẫn, do đó để giúp công thức tính ngắn gọn hơn thì chúng ta có thể sử dụng hàm khác để thay thế:
Nếu muốn kiếm tra nhiều điều kiện thì chúng ta có thể sử dụng hàm Vlookup, hàm Index hay hàm Choose.
Để có thể xác định được tên máy như yêu cấu trên ta sử dụng hàm vlookup kết hợp với hàm IF.
Tại ô C4 ta có công thức hàm như sau:
=VLOOKUP(A4;$A$11:$D$14;IF(B4=$B$11;2;IF(B4=$C$11;3;4));0)
Giải thích công thức:
Đầu tiên sử dụng hàm vlookup:
VLOOKUP (A4 sẽ là giá trị tìm kiếm
$A$11:$D$14: Vùng dữ liệu tìm kiếm và các $ chính là cố định hàng
Đối số thứ 3 là cột trả về nhưng ở đây trả về 3 cột do đó ta cần phải kiểm tra xem công suất lạnh của từng tên máy để điền vào ô tên máy, lúc này ta phải sử dụng hàm IF.
IF(B4=$B$11;2: Nếu Cột B4 mà bằng cột B11 (chính là bằng 9000) thì sẽ trả về giá trị cột 2
IF(B4=$C$11;3;4: Nếu Cột B4 mà bằng cột C11 (chính là bằng 12000) thì sẽ trả về giá trị cột 3, còn ngược lại sẽ trẻ về giá trị cột 4
0 là kiểu do chính xác.
Các bạn Enter để có được kết quả tên của máy như trong hình
Các bạn copy công thức để lấy được tên của toàn bộ cột
Như vậy là hàm if kết hợp với hàm vlookup cũng rất đơn giản phải không các bạn!
Để có thể thực hiện được yêu cầu trên thì chúng ta sẽ sử dụng hàm IF và h àm or với hàm AND.
Tại ổ E4 chúng ta sẽ có công thức:
Giải thích công thức hàm:
Đâu tiên ta sử dụng hàm IF
Và nếu hàm if đúng thì thực hiện tính tiền thưởng với công thức D4*0,05 và ngượi lại là không được thưởng
Enter và chúng ta sẽ được kết quả như trong hình
Hàm Sumifs Trong Excel Tính Tổng Nhiều Điều Kiện
Hàm sumifs trong excel là một hàm được nâng cấp từ hàm sumif từ phiên bản Excel 2007 trở đi. Và là hàm tính tổng nhiều điều kiện trong Excel. Hôm nay hiếu sẽ giới thiệu cho các bạn về cú pháp, định nghĩa. Cũng như cách vận dụng nó một cách hiệu quả nhất trong công việc.
1, Hàm sumifs trong excel là gì?
Hàm sumifs trong excel là một hàm dùng để tính tổng có nhiều điều kiện. Hàm tính tổng các ô trong một vùng hay phạm vi lựa chọn để thỏa mãn một hay nhiều điều kiện khác nhau
Lưu ý: Hàm sumifs trong excel chi được sử dụng ở các phiên bản Excel 2007, 2010 và 2013. Không sử dụng được cho Excel 2003.
2, Cú pháp hàm sumifs trong excel tính tổng nhiều điều kiện:
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)
Trong đó:
– sum_range: là tham số bắt buộc, vùng tính tổng bao gồm các ô cần tính tổng như các số hoặc tên, phạm vi hoặc tham chiếu các ô có chứa các số. Giá trị trống và giá trị chuỗi text được bỏ qua.
– criteria_range1: Vùng điều kiện 1: phạm vi cần phải đánh giá bằng điều kiện, bắt buộc.
– criteria1: điều kiện tính tổng số số 1, dưới dạng một số, biểu thức hay tham chiếu ô, bắt buộc.
– criteria_range2,criteria2,… : tùy chọn các phạm vi và điều kiện bổ sung khác. Hàm sumifs trong excel cho phép tối đa 127 cặp criteria_range,criteria.
Lưu ý khi sử dụng hàm sumifs trong excel
– Mỗi ô trong vùng điều kiện, phạm vi sum_range sẽ được cộng tổng lại. Khi nó thỏa mãn tất cả các điều kiện tương ứng đã có xác định là đúng cho ô đó.
– Các ô trong vùng sum_range có chứa TRUE được xem là 1, các ô chưa FALSE được xem là 0.
– Với criteria_range phải có cùng một kích thước và hình dạng giống với sum_range. Tức là criteria_range và sum_range phải giống nhau có cùng số hàng và cùng số cột.
– Trong phần điều kiện criteria thì có thể sử dụng các ký tự đại diện như dấu hỏi chấm (?) thay cho một ký tự đơn nào đó, dấu sao (*) sẽ thay cho một chuỗi kí tự nào đó. Nếu trường hợp điều kiện là dấu hỏi chấm hay dấu sao thì nhập thêm dấu ~ ở phía trước đó.
3, Ví dụ sử dụng hàm sumifs trong excel:
Yêu cầu: Cho bảng kê bán hàng của công ty ABC. Có số lượng và doanh số bán hàng của ba mặt hàng khác nhau tại 2 cửa hàng.
Hãy xác định doanh số bán hàng của Cam 1 của cửa hàng LA từ đầu tháng đến bây giờ là bao nhiêu?
Với 2 dòng tô vàng như trên thì các bạn dễ tính ra nhưng với 1000 dòng thì các bạn khó mà tính chính xác được.
Cần thỏa mãn được 2 điều kiện là Cam 1 và LA:
= SUMIFS(“Thành tiền”, ” Cửa hàng”, “LA”, “Tên hàng”, “Cam 1”)
Vùng tính tổng: Thành tiền.
” Cửa hàng”, “LA”: nhóm vùng điều kiện và điều kiện số 1.
“Tên hàng”, “Cam 1”: nhóm vùng điều kiện và điều kiện số 2.
Từ đấy ta áp dụng vào bảng tính bằng các ô địa chỉ là xong:
Cách Dùng Hàm Đếm Có Điều Kiện (Hàm Countif) Trong Excel
Hàm COUNTIF được dùng để làm gì?
Hãy tưởng tượng rằng bạn đang có một tệp tổng hợp các mục chưa được sắp xếp gồm: sản phẩm, doanh số bán hàng, số tháng và bạn đang muốn biết tần suất bán hàng của một sản phẩm cụ thể. Thay vì bạn sắp xếp chúng một cách thủ công, thì hãy sử dụng tính năng của hàm COUNTIF.
Hàm COUNTIF là gì?
Trong Microsoft Excel, COUNTIF là một hàm được dùng để thống kê số liệu. Lấy ví dụ, bằng cách tìm hiểu tần suất bán hàng của một danh sách sản phẩm, bạn sẽ dễ dàng nhận biết được mặt hàng nào đang có doanh số bán tốt nhất. Những dữ liệu thống kê này sau đó còn có thể được sử dụng tạo cách thành phần trực quan như sơ đồ và đồ thị.
Hàm COUNTIF trong Excel về cơ bản là sự kết hợp giữa hàm IF và hàm COUNT hoặc COUNTA (hàm thống kê). Sự kết hợp này để đảm bảo rằng Excel chỉ đếm những ô đáp ứng một số tiêu chí nhất định mà bạn đưa ra.
Chú ý: Bạn đừng nhầm lẫn giữa hàm COUNTIF và SUMIF. Một hàm sẽ trả về đếm số lượng của một giá trị cụ thể (COUNTIF), còn một hàm sẽ tính tổng các giá trị thỏa điều kiện bạn đặt ra (SUMIF).
Công thức hàm COUNTIF
=COUNTIF (RANGE, CRITERIA)
Range: Phạm vi các ô được tính toán, thống kê.
Hàm COUNTIF – Cách sử dụng và ví dụ minh họa
=COUNTIF(A3:A11,E3)
Range: A3:A11. Do bạn muốn đếm các giá trị có thể đáp ứng giá trị Criteria trong phạm vi những ô này.
Criteria : E3. Do bạn chỉ muốn đếm các giá trị trong phạm vi nêu trên nếu nó có giá trị trùng khớp với ô E3, chính là Toyota.
Và kết quả trả về sẽ là 4.
Thay vì sử dụng tham chiếu ô ( E3) làm tiêu chí lựa chọn, bạn cũng có thể thay thế nó bằng một giá trị văn bản -bằng cách gõ ” Toyota ” để có kết quả tương tự. Một lợi thế khác của việc sử dụng tham chiếu ô đó là bạn có thể đếm luôn cả các thương hiệu xe hơi khác nhau (nếu muốn). Lúc đó bạn chỉ cần kéo chuột xuống để copy công thức thay vì phải nhập thủ công tên các hãng xe khác nhau vào từng công thức.
Mẹo: Nếu như bạn nhìn thấy có khá nhiều ô chứa giá trị văn bản bị sai chính tả, thì bạn có thể nhanh chóng sửa lại chúng bằng cách dùng tính năng REPLACE. Bạn nhấn phím CTRL + H và một Menu sẽ được bật lên, sau đó bạn hãy nhập văn bản sai (văn bản bạn muốn thay đổi) và nhập giá trị văn bản mới ở trường bên dưới nó. Cuối cùng bạn nhấp vào REPLACE ALL.
Cách dùng hàm COUNTIF để đếm ô chứa văn bản trong Excel
Bạn có thể sử dụng hàm COUNTA để đếm cả giá trị số và giá trị văn bản, sau đó dùng hàm COUNT để đếm các giá trị số riêng lẻ và sau đó thực hiện phép tính trừ giữa các con số trả về của hàm COUNTA và hàm COUNT. Tuy nhiên, cách làm này khá rườm rà và làm mất thời gian của bạn, hôm nay tôi sẽ chỉ cho bạn cách dễ dàng hơn để đạt được kết quả tương tự.
Bạn hãy xem xét ví dụ minh họa bên dưới:
=COUNTIF(A3:A10,"*")
Range : A3:A10. Đây là phạm vi bạn muốn đếm các giá trị văn bản trong phạm vi.
Criteria : “*”. Có ý nghĩa rằng bạn chỉ muốn đếm các giá trị văn bản chèn một dấu HOA THỊ (*) được bọc trong dấu ngoặc kép.
Và kết quả trả về sẽ là 5.
Dấu HOA THỊ (*) được sử dụng làm ký tự đại diện để thể hiện cho một hay nhiều ký tự. Vì bạn chỉ muốn đếm tất cả các ô chứa giá trị văn bản nên việc chèn dấu HOA THỊ sẽ giúp bạn đạt được mục đích này.
Bạn đang đọc nội dung bài viết Tra 2 Điều Kiện Với Hàm Lookup Trong Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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!