Bạn đang xem bài viết Hướng Dẫn Cách Sử Dụng Lệnh If Trong Excel Đơn Giản Dễ Hiểu được cập nhật mới nhất 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.
Hàm if là hàm điều kiện phổ biến trong Excel giúp người dùng xác định đầu ra của dữ liệu, thoạt nhìn bạn sẽ nghĩ nó khó dùng nhưng bài viết này của chúng tôi sẽ cho bạn thấy việc dùng Hàm if vô cùng đơn giản.
Cú pháp của Hàm IF
=If(Điều kiện, “giá trị 1”, “giá trị 2”)
Những lưu ý khi dùng Hàm if
Nếu thỏa mãn điều kiện hàm sẽ trả về “giá trị 1”, ngược lại nếu không thỏa mãn sẽ nhận “giá trị 2”.
Nếu “điều kiện” ở dạng chuỗi ký tự, Hàm if sẽ đánh giá mọi ký tự trong chuỗi kể cả ký tự khoảng trống.
Có thể lồng nhiều Hàm if vào nhau nếu có nhiều hơn một điều kiện cần xét. Thứ tự xét điều kiện từ trái sang phải, nếu đã thỏa mãn điều kiện thứ nhất những điều kiện còn lại sẽ không được xét tới.
Có thể lồng Hàm if với nhiều hàm khác để tối ưu các điều kiện như: Hàm VLOOKUP hoặc HLOOKUP để tìm kiếm dữ liệu, Hàm COUNTIF hoặc COUNTIFS để đếm các số với điều kiện cho trước, Hàm SUMIF hoặc SUMIFS để cộng các số với điều kiện cho trước,…
Nếu bạn bỏ qua không nhập “Giá trị 1” hoặc “Giá trị 2” hàm sẽ hiển thị kết quả là 0
Nếu bạn có kết quả là ô trống, hãy nhập “Giá trị 1” hoặc “Giá trị 2” là “”
Ví dụ 1: Xác định học sinh đỗ hay trượt bằng Hàm if (Sử dụng Hàm if cơ bản):
Cho bảng điểm thi của 4 học sinh, biết rằng 5 điểm trở lên là đỗ, dưới 5 điểm thì phải thi lại. Hãy phân loại các học sinh này:
Giá trị 1 của chúng ta là “Đậu”, “Giá trị 2” là “Trớt”, trong cú pháp này điểm thi của học sinh là 8 lớn hơn 5 nên thỏa mãn điều kiện ô E4 hiển thị giá trị 1 là Đậu.
Ví dụ 2: Xác định xếp loại học lực của học sinh bằng Hàm if (Sử dụng Hàm if nâng cao)
Cho bảng điểm thi của 6 học sinh, biết rằng 8 điểm trở lên là học sinh giỏi, từ dưới 8 điểm đến 6.5 là học sinh khá, dưới 5 điểm là học sinh trung bình và dưới 3.5 là học sinh yếu. Hãy xếp loại học lực các học sinh này:
Hàm if thứ 1: Điều kiện của chúng ta là điểm thi của học sinh (ô D4) nếu lớn hơn hoặc bằng 8 thì thỏa mãn điều kiện, ô E4 hiển thị là Giỏi, nếu nhỏ hơn 8 chuyển sang thực hiện hàm if thứ 2.
Hàm if thứ 2: Điều kiện điểm thi của học sinh (ô D4) nhỏ lớn hơn 8 và lớn hơn hoặc bằng 6.5 thì thỏa mãn điều kiện, ô E4 hiển thị là Khá, nhỏ hơn 6.5 chuyển sang thực hiện hàm if thứ 3.
Hàm if thứ 3: Điều kiện điểm thi của học sinh (ô D4) nhỏ lớn hơn 6.5 và lớn hơn hoặc bằng 5 thì thỏa mãn điều kiện, ô E4 hiển thị là Trung bình, nếu nhỏ hơn 5 ô E4 hiển thị là Yếu.
Với những cú pháp lồng nhiều hàm chú ý đến số lượng các dấu đóng mở ngoặc.
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 Vẽ Sơ Đồ Lớp Học Trong Word Đơn Giản Và Dễ Hiểu
Bước 1: Nhấn chọn Page Layout → Columns và chọn Two để chia văn bản Word thành 2 cột. Các bạn cũng có thể chia thành nhiều cột hơn tùy thuộc số lượng hàng dọc bàn ghế trong lớp.
Bước 2: Chọn tab Insert. Tại đây chọn Table để bắt đầu kẻ bảng bàn ghế. Thông thường bàn học sẽ có 4 học sinh ngồi, các bạn nhấn Table và kéo 4 hàng ngang cùng số hàng dọc tương ứng
Ví dụ: Chọn bảng gồm 4 cột và 6 hàng như sau:
Tiếp tục chuyển sang cột bên cạnh và thực hiện tương tự để tạo số bàn ghế. Nhấn vào góc của bảng để kéo căn chỉnh kích thước tùy ý.
Vậy là ta đã có 2 dãy bàn học với số lượng mỗi bên là 6 bàn và mỗi bàn có 4 chỗ cho Học Sinh ngồi.
Để xác định được đâu là dãy trong và dãy ngoài ta xây dựng thêm một ô là bàn giáo viên và cửa ra vào.
Bước 3: Có thể tạo thêm bàn giáo viên ở góc bằng cách nhấn Table → chọn 1 ô.
Ô tạo ra kích thước có thể chưa phù hợp, nên các bạn chọn vào ô đó, trên thanh công cụ hiển thị tab Layout, để điều chỉnh độ cao và rộng của cột cho phù hợp bạn chọn Height và Width.
Để trình bày đẹp hơn, chúng ta vẽ thêm một “mũi tên chỉ dẫn cửa lớp”
Bạn chọn tab Insert → Shapes → Chọn dấu mũi tên.
Sau khi tạo dấu mũi tên xong, các bạn tạo thêm text box ghi thêm “Cửa ra vào” để rõ hơn về sơ đồ lớp.
Chọn tab Insert → Text box. Sau đó, bạn xóa nội dung mặc định sẵn đi và gõ nội dung mình muốn ghi vào.
Đây là sơ đồ sau khi chúng ta thêm đầy đủ các vị trí trong lớp.
Bước 4: Nhập đầy đủ tên học sinh sau đó chỉnh sửa kiểu chữ, kích thước phù hợp là bạn đã có sơ đồ lớp học đơn giản, trực quan.
Quá đơn giản và dễ thực hiện với cách tạo sơ đồ lớp học bằng Word đúng không nào. Với những dạng vẽ sơ đồ đơn giản như vậy, bạn nên sử dụng Word để tiết kiệm nhất thời gian tạo sơ đồ.
?Hàm Left Trong Excel Là Gì? Hướng Dẫn Sử Dụng Hàm Left Đơn Giản
Qua đó, chúng ta có thể tóm gọn lại tác dụng của việc sử dụng hàm Left như sau:
– Giúp lấy những ký tự mình muốn ra khỏi 1 chuỗi các ký tự dài.
– Đơn giản, dễ dàng, tiện dụng và nhanh chóng.
– Có thể làm được trên nhiều chuỗi dữ liệu khác nhau.
Cách sử dụng hàm Left trong Excel để cắt chuỗi các ký tự bên trái
Cách này dùng để lấy những ký tự như số, chữ ở phía bên trái của chuỗi ký tự ra một ô khác được chúng ta chọn. Mà vẫn không làm mất đi chuỗi ký tự ban đầu.
Hướng dẫn thực hiện nhanh hàm lấy ký tự trong Excel
Bước 1: Chọn một ô bất kỳ để nhập công thức
Mẹo nhỏ: Bạn nên chọn một ô gần với chuỗi dữ liệu để sau khi làm xong dễ dàng so sánh.
Bước 2: Nhập công thức =Left(
Bước 3: Ở phần Text, bạn chọn ô có chuỗi ký tự sau đó bấm dấu phẩy”,” hoặc dấu chấm phẩy “;” tùy theo công thức yêu cầu.
– Không được để trống
– Chú ý đến dấu ngăn cách giữa Text và Num_chars (có thể là dấu “,” hoặc chấm phẩy “;”)
– Nếu bạn nhập sai dấu ngăn cách sẽ dẫn đến lỗi. Excel sẽ báo để bạn có thể sửa lại lỗi.
Bước 4: Ở phần Num_chars bạn hãy nhập số lượng ký tự muốn lấy. Rồi sau đó bấm dấu ngoặc phải “)” (ở trong ví dụ này mình lấy số là 3 vì đề bài yêu cầu lấy ra 3 ký tự bên trái).
– Không được để trống, nếu để trống máy sẽ tự động điền 0 hoặc 1.
– Num_chars phải lớn hơn giá trị 0
– Ở cuối công thức bắt buộc phải có dấu ngoặc phải “)”.
Bước 5: Nhấn phím Enter và bạn đã có được chuỗi ký tự bạn muốn.
Bước 6: Nếu có nhiều chuỗi ký tự. Thì bạn hãy giữ chuột ở ô vuông nhỏ góc phải của ô dữ liệu đồng thời kéo xuống.
Hàm cắt chuỗi có điều kiện trong Excel là gì?
Hàm cắt chuỗi là một loại hàm thuộc dạng công thức được mặc định sẵn. Hàm này có chức năng tách một chuỗi các ký tự thành các phần tùy vào mục đích của người sử dụng. Các hàm cơ bản thường sử dụng nhất trong nhóm hàm cắt chuỗi có thể kể đến như hàm RIGHT, LEFT, MID, TRIM,…
Trong cuộc sống, khi bạn thao tác với các bảng dữ liệu. Chắc chắn sẽ phát sinh rất nhiều trường hợp mà bạn cần phải sử dụng đến nhóm hàm cắt chuỗi có điều kiện trong Excel. Một số mục đích sử dụng của nhóm hàm này có thể kể đến như sau:
Tách riêng một ô chứa chuỗi ký tự thành từng ô khác nhau
Các bảng danh sách như thông tin nhân viên, học viên hoặc khách hàng,… thường yêu cầu bạn phải tách riêng Họ, Tên đệm, Tên từ một ô dữ liệu có chứa họ tên đầy đủ của những người này.
Nếu bạn thực hiện nhập thủ công tên, họ, tên đệm vào từng ô cho cả danh sách hiển nhiên là rất tốn thời gian và công sức. Chính vì vậy, hàm cắt chuỗi có điều kiện trong Excel sẽ giúp các bạn giải quyết vấn đề này một cách vô cùng hiệu quả và nhanh chóng.
Xác định các ký tự cần lấy nằm trong một dãy có nhiều ký tự khác nhau.
Là kế toán của một công ty. Sếp yêu cầu bạn phải thực hiện trả thưởng cho nhân viên dựa vào các ký tự đặc biệt nào đó. Dãy ký tự này nằm trong dãy mã số nhân viên. Ví dụ nhân viên A có mã số là ABCDGD, nhân viên B có mã số là ABCDNV và ông C có mã nhân viên là DEGHNV.
Nếu hai ký tự cuối chứa GD mức thưởng là 1.000.000đ, NV có mức thưởng là 500.000đ. Lúc này hàm cắt chuỗi sẽ giúp bạn xác định ra hai ký tự cuối trong dãy mã số nhân viên là gì. Để bạn dễ dàng hơn khi tiến hành trả thưởng cho nhân viên A, nhân viên B, và nhân viên C.
Trong quá trình nhập liệu, có thể xảy ra những trường hợp người cung cấp thông tin gõ dư quá nhiều các ký tự không cần thiết. Ví dụ như dấu trắng ( khoảng cách) ở đầu hoặc ở cuối họ và tên, số ký hiệu thứ tự nhân viên,…
Điều này sẽ làm ảnh hưởng đến kết quả tính toán cho toàn bộ danh sách. Kiểm tra và chỉnh sửa thủ công theo từng hạng mục lúc này là một việc vô cùng mất thời gian. Chính vì thế, hàm cắt chuỗi có điều kiện trong Excel sẽ thay các bạn thực hiện điều này mà không hề tốn quá nhiều thời gian và công sức.
Hàm Left: Left sẽ giúp người dùng tách các ký tự trong một dãy từ trái sang phải thành một phần riêng biệt.
Cấu trúc là: =LEFT( chuỗi ký tự, số lượng ký tự cần lấy).
Một số hàm lấy ký tự trong chuỗi khác cũng được sử dụng nhiều như:
– Hàm RIGHT: là một hàm thuộc nhóm hàm cắt chuỗi. Có công dụng lấy các ký tự trong một dãy từ phải sang trái tạo thành một phần riêng biệt.
Cấu trúc là: = RIGHT( chuỗi ký tự, số lượng ký tự cần lấy).
– Hàm MID: Cũng là hàm dùng để lấy một số ký tự nằm trong chuỗi. Tuy nhiên, thay vì từ trái hay từ phải qua. Hàm MID sẽ cho phép chúng ta lấy được những ký tự nằm ở giữa chuỗi lớn.
Cấu trúc là: =MID(chuỗi ký tự, vị trí bắt đầu lấy,số lượng ký tự cần lấy ra)
– Hàm TRIM: Tuy không được sử dụng để tách và xuất các ký tự trong chuỗi ra thành một ô riêng biệt. Tuy nhiên, hàm TRIM lại cực kỳ hữu ích khi giúp người sử dụng có thể loại bỏ những khoảng trắng mà không cần thiết ở đầu và cuối chuỗi ký tự. Điều này rất cần thiết ở trong các trường hợp cần tính toán số lượng ký tự trong một chuỗi mà không bao gồm các khoảng trắng đầu và cuối.
Cấu trúc: =TRIM(chuỗi ký tự)
Những lỗi thường gặp khi sử dụng hàm Left trong Excel và cách khắc phục.
Cách sử dụng hàm Left không chỉ đơn thuần đòi hỏi bạn áp dụng đúng công thức mà còn phải đáp ứng được những điều kiện cần và đủ. Nếu hàm LEFT không thực hiện được trong trang tính, bạn sẽ vướng phải một số lỗi quan trọng như sau:
Nếu công thức hàm LEFT trả về kết quả #VALUE (lỗi). Để biết được thao tác đã làm sai ở đâu. Bạn cần kiểm tra lại giá trị của Num_chars ở trong công thức. Theo đó nếu num_chars là một số âm. Quá đơn giản! Bạn chỉ cần loại bỏ đi các cấu trúc lỗi là xong.
Thông thường, lỗi về cách sử dụng hàm LEFT này xuất hiện khi num_chars chứa nhiều chức năng khác nhau. Để khắc phục thì bạn chỉ cần copy hàm đó vào một ô khác hoặc chọn trên thanh công thức. Trên thanh công thức bạn chỉ cần nhấn F9 để xem hàm tương đương nhận thấy được giá trị nhỏ hơn 0. Nên bạn hãy kiểm tra lại hàm lỗi chi tiết nhất và sửa chữa chúng.
Muốn phân xuất mã quốc gia từ số điện thoại bất kỳ được cung cấp. Sẽ áp dụng công thức sau:
LEFT (A2, SEARCH(“-“, A2)-1)
Theo đó, hàm SEARCH ở trong phần num_chars sẽ tìm ra vị trí của dấu (-) đầu tiên có trong chuỗi nguồn. Bạn có thể thực hiện thêm thao tác trừ 1 để xóa dấu (-) khỏi kết quả trả về. Nếu bạn thay thế -1 với -11, kết quả sẽ trả về là lỗi #VALUE. Nguyên nhân vì num_chars đã tương đương với số âm.
Bạn nên kiểm tra lại những khoảng trống hay còn gọi là những dấu cách ở đầu giá trị nguồn. Ngoài ra, nếu bạn có thực hiện copy dữ liệu từ một trang web hay từ một loại nguồn bên ngoài. Đây sẽ là nguyên nhân gây ra lỗi của công thức hàm.
Theo đó, phương pháp tốt nhất để loại bỏ những dấu cách trong đầu trang tính. Ngoài ra, bạn nên sử dụng đến hàm Trim hoặc Cell Cleaner trong công thức tính.
Trong Excel, hầu như ngày tháng được cài mặc định là số nguyên như từ 1/1/1900 là số 1. Để hiểu hơn về cách sử dụng hàm Left ở trong trường hợp này bạn có thể áp dụng:
Trong ô A1 có 11- tháng 1 – 2018, khi thực hiện phân xuất bởi công thức LEFT (A1,2). Lúc này bạn sẽ thu được kết quả là 42 – 2 chữ số đầu tiên trong số 42746 biểu thị ngày 11/1/2018.
Một số mẹo nhỏ khi sử dụng hàm Left trong Excel
Left là một loại hàm trong Excel khá thông dụng. Bởi vậy cách sử dụng lệnh của hàm này được xem là kiến thức cơ bản mà bất kỳ ai sử dụng Excel cũng nên biết. Chỉ cần bạn bỏ công để tìm hiểu một chút là có thể thành thạo được rồi.
Trong trường hợp bạn sử dụng hàm để phân xuất một phần của chuỗi văn bản đứng trước một ký tự nhất định. Chúng ta thực hiện một ví dụ để tìm hiểu cách làm:
Bạn muốn lấy họ (first name) trong cột tên đầy đủ (full name). Tiêu đề đã cho sẵn trước đó hoặc cần tách mã quốc gia trừ cột số điện thoại? Bạn chỉ cần lưu ý mỗi tên và mã chứa số lượng ký tự khác nhau.
Theo đó, trong trường hợp họ và tên được phân cách bởi ký tự dấu cách. Ta sẽ tìm vị trí của ký tự khoảng trống được tính nhờ vào hàm Search hoặc Find, cụ thể như sau:
=LEFT (A2, SEARCH(” “, A2))
Tiếp theo để có thể phát triển công thức. Đồng thời loại đi khoảng trống sẵn có ta sẽ thực hiện trừ 1 từ kết quả ở hàm Search. Công thức:
=LEFT (A2, SEARCH(” “, A2)-1)
Tương tự, bạn có thể lấy mã quốc gia ở cột của số điện thoại trong công thức. Khác biệt duy nhất ở chỗ là khi dùng hàm Search đặt vị trí dấu (“-“) ở trước khoảng trống. Công thức áp dụng:
=LEFT (A2, SEARCH(“-“, A2)-1)
Công thức để tách chuỗi ký tự con đứng trước bất kỳ kí tự nào đó được đúc kết là:
LEFT (chuỗi, SEARCH (ký tự,chuỗi)-1)
Chức năng của hàm chính là phân xuất chuỗi ký tự con từ chuỗi ký tự văn bản có sẵn. Vì vậy khi muốn dời một số ký tự từ phần cuối của chuỗi văn bản. Đồng thời, lại đặt phần còn lại vào một ô khác thì cần làm như thế nào? Để giải được ví dụ trên ta sẽ kết hợp dùng hàm Len và Left theo công thức: LEFT (chuỗi, LEN(chuỗi) – số kí_tự_ muốn dời).
Nguyên tắc hoạt động của công thức này là: Hàm LEN sẽ lấy tổng số ký tự trong một chuỗi có trong văn bản. Tách rồi trừ đi lượng ký tự không mong muốn. Tính từ tổng độ dài của ký tự văn bản đó. Hàm Left lúc này đóng vai trò là trả lại số ký tự còn lại cần lấy.
Đặc tính của hàm tách chuỗi là sẽ luôn luôn thu về cho bạn một kết quả là văn bản chữ. Thậm chí ngay cả khi bạn đặt một vài chữ số ở trong chuỗi ký tự. Kết quả thu được vẫn là dạng văn bản.
Bởi vậy, câu hỏi được đặt ra cho nhiều người đó là làm thế nào để kết quả của Left có thể giúp bạn thu về là con số chứ không phải chuỗi văn bản? Rất dễ dàng, bạn chỉ cần sử dụng đến hàm VALUE. Công thức để giúp bạn sử dụng đó là: VALUE(LEFT ()).
Cách Sử Dụng Hàm Sumproduct Trong Excel Đơn Giản Nhất
Hàm Sumproduct là gì? Cách sử dụng hàm Sumproduct trong excel
Khi bạn nghe cái tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ là một công thức vô ích – công thức tính tổng thông thường của các tích số. Nhưng định nghĩa này không cho thấy thậm chí là chỉ một phần nhỏ tính năng của hàm SUMPRODUCT.
Về mặt kỹ thuật, hàm SUMPRODUCT trong Excel nhân các con số trong mảng xác định, rồi trả về tổng của các tích số đó.
Cú pháp của hàm SUMPRODUCT rất đơn giản:
SUMPRODUCT(array1, [array2], [array3], …)
Trong đó, mảng 1, mảng 2, … là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại.
Số mảng tối thiểu là 1. Trong trường hợp này, hàm SUMPRODUCT chỉ đơn giản tính tổng các thành phần mảng rồi trả về tổng số.
Mặc dù hàm SUMPRODUCT xử lý mảng, nhưng nó không yêu cầu phím tắt mảng (Ctrl + Shift + Enter). Bạn hoàn thành một công thức SUMPRODUCT theo cách thông thường bẳng cách nhấn phím Enter.
Lưu ý:
Tất cả mảng trong công thức SUMPRODUCT phải có cùng số hàng và số cột , nếu không thì bạn sẽ nhận lỗi #VALUE!.
Nếu bất cứ câu lệnh mảng nào chứa giá trị phi số, chúng sẽ được xử lý như số 0.
Nếu mảng là phép thử lô gic, thì nó sẽ trả về giá trị TRUE và FALSE. Thông thường, bạn cần phải đổi các giá trị TRUE và FALSE này thành 1 và 0 bằng cách sử dụng toán tử đơn phân (-). Hãy xem qua ví dụ hàm SUMPRODUCT có nhiều điều kiện để nắm rõ hơn.
Hàm SUMPRODUCT không hỗ trợ ký tự đại diện.
CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL
Để có cái nhìn tổng quát về cách hàm SUMPRODUCT hoạt động, hãy xem qua ví dụ sau.
Giả sử bạn có số lượng ở các ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phép toán ở trường, thì bạn sẽ nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, bạn có thể nhận được kết quả chỉ với một công thức SUMPRODUCT:
=SUMPRODUCT(A2:A4,B2:B4)
Công thức chọn số đầu tiên trong mảng đầu tiên rồi nhân nó cho số đầu tiên trong mảng thứ hai, rồi lấy số thứ hai trong mảng đầu tiên nhân cho số thứ hai trong mảng thứ hai rồi cứ tiếp tục như thế.
Nói cách khác, công thức SUMPRODUCT biểu diễn công thức toán học sau đây:
=A2*B2 + A3*B3 + A4*B4
Hãy nghĩ đến việc bạn có thể tiết kiệm bao nhiều thời gian nếu bảng của bạn không chứa 3 hàng dữ liệu, mà là 3 trăm hay 3 ngàn hàng!
CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:
HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN
Thông thường trong Microsoft Excel, luôn có nhiều hơn một cách để hoàn thành công việc. Nhưng khi nói đến việc so sánh hai hay nhiều mảng, đặc biệt là có nhiều điều kiện, nếu không nói là duy nhất, hàm SUMPRODUCT là hàm hiệu quả nhất. À thì, hàm SUMPRODUCT hay công thức mảng đều được.
=SUMPRODUCT(-(C2:C10<B2:B10))
hay
=SUMPRODUCT((C2:C10<B2:B10)*1)
Trong đó C2:C10 là doanh số thực và B2:B10 là doanh số dự kiến.
=SUMPRODUCT(-(C2:C10<B2:B10), -(A2:A10=”táo”))
Hay, bạn có thể sử dụng cú pháp sau:
=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))
CÁCH CÔNG THỨC SUMPRODUCT CÓ MỘT ĐIỀU KIỆN HOẠT ĐỘNG:
Đối với người mới bắt đầu, hãy chia nhỏ công thức đơn giản hơn – công thức so sánh các con số trong hai cột hàng theo hàng, và nói cho chúng ta biết số lần cột C ít hơn cột B:
=SUMPRODUCT(-(C2:C10<B2:B10))
Nếu bạn chọn phần (C2:C10<B2:B10) trong thanh công thức, và nhấn F9 để xem giá trị ẩn, thì bạn sẽ thấy mảng sau:
Cái chúng ta có ở đây đó là giá trị hàm BOOLEAN TRUE và FALSE, trong đó, TRUE có nghĩa là đã đáp ứng điều kiện xác định (ví dụ, một giá trị ở cột B ít hơn một giá trị ở cột C trong cùng một hàng), và FALSE cho thấy điều kiện vẫn chưa được đáp ứng.
Hai dấu trừ (-), về mặt kỹ thuật được gọi là hai toán tử đơn phân, nó ép buộc TRUE và FALSE thành 1 và 0: {0;1;0;0;1;0;1;0;0}.
Cách khác để đổi giá trị lôgic thành giá trị số đó là nhân mảng với 1:
=SUMPRODUCT((C2:C10<B2:B10)*1)
Cách nào cũng được, vì chỉ có một mảng trong công thức SUMPRODUCT, nên nó chỉ nhân 1 trong mảng cho kết quả và chúng ta nhận được kết quả đếm mong muốn. Rất dễ, đúng không nào?
=SUMPRODUCT(-(C2:C10<B2:B10), -(A2:A10=”táo”))
hay
=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))
Điểm khác biệt duy nhất về mặt kỹ thuật giữa hai công thức đó là phương pháp ép buộc TRUE và FALSE thành 1 và 0 – bằng cách sử dụng hai toán tử đơn phân hay phép nhân. Kết quả là, chúng ta nhận được hai mảng 1 và 0:
ĐẾM/TÍNH TỔNG/TÍNH TRUNG BÌNH CÓ ĐIỀU KIỆN CÁC Ô VỚI NHIỀU TIÊU CHUẨN
Trong Excel 2003 và các phiên bản trước thì không có hàm IFS, một trong những cách dùng thông dụng nhất của hàm SUMPRODUCT đó là tính tổng hay đếm có điều kiện các ô với nhiều tiêu chuẩn. Bắt đầu với Excel 2007, Microsoft giới thiệu một chuỗi hàm được đặc biệt thiết kế cho những nhiệm vụ này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS.
Trong các phiên bản Excel gần đây: Excel 2016, 2013, 2010 và 2007, bạn có thể dễ dàng hoàn thành nhiệm vụ bằng cách sử dụng công thức SUMIFS, COUNTIFS và AVERAGEIFS. Nếu bạn đang tìm một hướng đi khó khăn hơn, hay nếu bạn vẫn sử dụng Excel 2003 hay phiên bản cũ hơn, thì bạn vẫn đạt được kết quả mong muốn với hàm SUMPRODUCT.
Để đếm doanh số táo bán ra ở khu vực phía Bắc:
=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”))
hay
=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”))
Để tính tổng doanh số táo bán ra ở khu vực phía Bắc:
=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”), C2:C12)
hay
=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”)*C2:C12)
Để tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc:
Để tính trung bình cộng, chúng ta chỉ chia tổng cho phép đếm như thế này:
=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”), C2:C12) / SUMPRODUCT( -(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”))
=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”), C2:C12)
Trong mảng đầu tiên, 1 đại diện cho khu vực phía Bắc, và 0 đại diện cho các khu vực khác.
Trong mảng thứ hai, 1 đại diện cho Táo, và 0 đại diện cho các món hàng khác.
Mảng thứ ba chứa chính các con số xuất hiện từ ô C2 đến C12.
VÍ DỤ 2. CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC OR
Cộng các con số trong mảng trên cho kết quả mong muốn – doanh số Táo bán ra ở khu vực phía Bắc.
Để công hay đếm có điều kiện các ô sử dụng hàm lôgic OR, hãy sử dụng dấu cộng (+) giữa các mảng.
Trong công thức Excel SUMPRODUCT, cũng như trong công thức mảng, dấu cộng đóng vai trò làm toán tử OR hướng dẫn Excel trả về giá trị TRUE nếu bất kỳ điều kiện nào trong biểu thức xác định được đáp ứng.
Ví dụ, để đếm tổng doanh số Táo và Chanh không phân biệt vùng miền, hãy sử dụng công thức này:
=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”))
Nói đơn giản, công thức trên có nghĩa là: Hãy đếm số ô nếu dải ô B2:B12=”táo” OR B2:B12=”chanh”.
Để tính tổng doanh số Táo và Chanh, hãy thêm một câu lệnh chứa dải ô Doanh số:
=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”), C2:C12)
Một trong những giải pháp khả thi đó là kết hợp hai hay nhiều hàm SUMIFS+SUMIFS hay COUNTIFS+COUNTIFS.
Cách khác đó là sử dụng hàm SUMPRODUCT trong đó:
Dấu hoa thị (*) được dùng như toán tử AND.
Dấu cộng (+) được dùng như toán tử OR.
Để đếm số lần Táo và Chanh được bán ở khu vực phía Bắc, hãy lập công thức với hàm lôgic sau:
=Count If ((Vùng miền=”miền Bắc”) AND ((Món hàng=”Táo”) OR (Món hàng=”Chanh”)))
Khi vừa sử dụng theo cú pháp hàm SUMPRODUCT, công thức sẽ có dạng như sau:
=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”)))
Để tính tổng doanh số Táo và Chanh bán ra ở khu vục phía Bắc, hãy dùng công thức trên rồi thêm dải ô Doanh số cùng với hàm lôgic AND:
=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”))*C2:C12)
Ở một trong những ví dụ trước, chúng ta đã bàn về công thức SUMPRODUCT dùng để tính trung bình có điều kiện. Cách dùng phổ biến khác của hàm SUMPRODUCT trong Excel đó là tính trung bình có trọng lượng trong đó mỗi giá trị có một trọng lượng nhất định.
Công thức SUMPRODUCT tính trung bình có trọng lượng tổng quát có dạng như sau:
SUMPRODUCT( values, weights) / SUM( weights)
Giả sử các giá trị nằm ở dải ô B2:B7 và trọng lượng ở dải ô C2:C7, công thức SUMPRODUCT tính trung bình có trọng lượng có dạng như sau:
HÀM SUMPRODUCT ĐƯỢC DÙNG LÀM HÀM THAY THẾ CHO CÔNG THỨC MẢNG
Tôi tin rằng ngay bây giờ, bạn sẽ không gặp khó khăn trong việc hiểu quy luật công thức nữa.
Ngay cả khi bạn đọc bài viết này chỉ để có thêm thông tin và bạn không còn nhớ rõ các chi tiết nữa, hãy nhớ một điểm mấu chốt quan trọng – hàm SUMPRODUCT trong Excel xử lý mảng. Và vì hàm SUMPRODUCT có các khả năng của công thức mảng, nên nó có thể trở thành hàm thay thế dễ sử dụng cho công thức mảng.
Ưu điểm là gì? Về mặt cơ bản, bạn có khả năng quản lý công thức một cách dễ dàng mà không cần phải nhấp Ctrl + Shift + Enter mỗi lần bạn nhập một công thức mảng mới hay chỉnh sửa công thức mảng.
Ví dụ, chúng ta có thể sử dụng một công thức mảng đơn giản để đếm số ký tự trong dải ô xác định:
{=SUM(LEN( range))}
và biến nó thành công thức thông thường:
=SUMPRODUCT(LEN( range))
Lời Kết
Xem những video Game hay tại Game Hot 24hEditor for chúng tôi
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Sử Dụng Lệnh If Trong Excel Đơn Giản Dễ Hiểu 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!