Bí kiếp võ lâm truyền kỳ không lo thất truyền ” công thức trong excel chuyên dụng” không thể bỏ lỡ. Skill văn phòng không bao giờ là đủ với thời đại ngại gì không bỏ túi ngay những bí quyết tuyệt đỉnh, thiết yếu của phần mềm excel đúng không nào! Lại là blogphanmem.vn đây, cùng theo chân chúng mình “tu luyện” nhé!
Bạn đang xem bài viết: Công thức trong excel chuyên dụng
Nội dung bài viết
Công thức trong Excel – những điều căn bản
Trong MS Excel, bí quyết excel là các phương trình thực hiện các tính toán không giống nhau trong các bảng tính. mặc dù Microsoft giới thiệu thêm nhiều hàm mới qua các năm, quan điểm bí quyết bảng tính Excel vẫn giống nhau trong toàn bộ các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và thấp hơn.
Xem thêm: 3 hàm liệt kê trong Excel kèm ví dụ chi tiết
- tất cả các bí quyết Excel bắt đầu với một dấu bằng (=).
- Sau dấu bằng, bạn điền hoặc là phép tính hoặc hàm. Ví dụ: để cộng các thành quả trong các ô từ B1 tới B5, bạn có thể:
- Gõ toàn bộ phương trình: = B1 + B2 + B3 + B4 + B5
- sử dụng hàm SUM: = SUM (B1: B5)
- Nhấn phím Enter để coi như hoàn tất bí quyết. Làm xong!
bí quyết Microsoft Excel – những điều căn bản
Các yếu tố của công thức trong Excel
khi mà bạn tạo một bí quyết trong Excel, bạn có thể sử dụng các phần tử khác nhau để mang lại dữ liệu nguồn cho bí quyết và chỉ ra các toán tử nên được thực hiện trên những dữ liệu đấy. tùy thuộc vào loại công thức mà bạn tạo ra, nó có thể bao gồm bất kỳ hoặc toàn bộ các phần sau: ·
- Hằng số – số hoặc các giá trị văn bản mà bạn nhập trực tiếp vào một công thức, như = 2 * 3. ·
- Tham chiếu ô – tham chiếu đến ô chứa giá trị bạn mong muốn dùng trong bí quyết Excel của chúng ta, ví dụ: = SUM (A1, A2, B5). Để tham khảo dữ liệu trong hai hoặc nhiều ô tiếp giáp, hãy sử dụng một tham chiếu phạm vi như A1: A5. VD, để tổng hợp giá trị trong toàn bộ các ô giữa A1 và A5, sử dụng công thức này: = SUM (A1: A5).·
- Tên – được nắm rõ ràng cho một phạm vi ô, hằng, bảng, hoặc hàm, ví dụ = SUM (my_name). ·
- Các hàm – các công thức được xác định trước trong Excel thực hiện tính toán sử dụng các giá trị được cung cấp trong các đối số của chúng. ·
- Toán tử – các ký hiệu đặc biệt chỉ định loại hoạt động hoặc tính toán sẽ được làm.
các yếu tố của bí quyết Microsoft Excel
Các hàm công thức trong Excel căn bản – đáng nhớ
Công thức Excel hàm IF
bí quyết Excel hàm IF là một công thức giúp bạn kiểm tra điều kiện đạt được thuyết phục hay không và trả về giá trị đúng/sai theo điều kiện mà bạn nói ra.
Hàm IF có bí quyết như sau: =IF(điều kiện,"giá trị đúng","giá trị sai")
tuy nhiên, Nó là bí quyết cho 1 điều kiện và Nếu chúng ta muốn Áp dụng cho nhiều điều kiện hơn thì bạn có thể phải dùng hàm IF lồng nhau.
Trong VD ở hình trên, chúng ta sẽ kiểm tra coi ô A có thỏa mãn được điều kiện nói ra là lớn 10 hay không bằng công thức =IF(A1>10,”Correct”,”Incorrect”) và do A có thành quả là 12>10 nên kết quả nhận được sẽ trả về Correct.
Bí quyết tính tổng hàm SUM
Một bí quyết Excel đơn giản, là một hàm căn bản trong Excel mà người sử dụng mới nào cũng biết tới đấy là bí quyết Excel tính tổng hàm SUM. Hàm SUM giúp tính tổng các con số lại với nhau và công thức hàm SUM là
=SUM(ô cần tính 1,ô cần tính 2,ô cần tính 3,...)
Trong VD trên, con người tính tổng của ô A2 và B2 và kết quả sẽ hiển thị tại ô B3. Để tính nhiều ô, bạn chỉ phải nhấn Ctrl để thêm ô mong muốn tính vào thông tin của hàm.
bí quyết trên giúp cho bạn tính tổng các ô mà bạn mong muốn tính còn Nếu chúng ta muốn tính tổng một cột hay một dòng, bạn chỉ phải nhập =SUM sau đấy quét toàn bộ các ô của dòng/cột cần tính.
Hàm tính tổng SUMIF có điều kiện
Để tính tổng các ô dựa trên một điều kiện (ví dụ, lớn hơn 9), hãy dùng hàm SUMIF sau (hai đối số).
Để tính tổng các ô dựa trên một tiêu chí (ví dụ, green), hãy dùng hàm SUMIF với 3 đối số (đối số cuối cùng là phạm vi cần tính tổng).
Hàm tính tổng SUMIFS với nhiều điều kiện
Để tính tổng các ô dựa trên nhiều điều kiện (ví dụ, blue và green), hãy dùng hàm SUMIFS sau (đối số trước tiên là phạm vi bảng tính cần tính tổng).
Hàm tính thành quả trung bình AVERAGE
Hàm =AVERAGE thực hiện đúng chức năng như tên gọi của Đây là nói ra con số có giá trị trung bình trong những số được chọn. ví dụ bạn muốn tính thành quả trung bình từ ô A10 đến ô J10, chỉ phải gõ =AVERAGE(A10:J10) rồi nhấn Enter.
Kết quả đưa ra trong ô K10 là con số có thành quả trung bình giữa các ô từ A10 đến J10. ngoài ra, bạn cũng có thể trải nghiệm con trỏ chuột kéo thả và đánh dấu chọn vùng dữ liệu hoặc có khả năng cùng lúc đó nhấn phím Ctrl rồi nhấn chuột vào từng ô riêng rẽ trong trường hợp nếu các ô không nằm kế cận nhau.
tương tự như SUMIF, COUNTIF bạn có thể trải nghiệm AVERAGEIF và AVERAGEIFS để tính giá trị trung bình của các ô dựa trên một hoặc nhiều tiêu chí.
Hàm đếm ô trống COUNTBLANK
Bạn sử dụng hàm này khi cần đếm các ô trống. Cú pháp =COUNTBLANK(phạm vi bảng tính cần đếm).
Hàm đếm ô không trống COUNTA
Hàm COUNTA có chức năng dùng để đếm số ô có chứa nội dung bất kỳ bao gồm chữ số, chữ cái hay biểu tượng, hay Theo một cách khác nó dùng để đếm các ô không trống. Cú pháp =COUNTA(phạm vi bảng tính cần đếm).
Công thức Countifs trong Excel
bí quyết Countifs trong Excel
= COUNTIF (X: X, “*”)
Đếm các ô có thành quả cụ thể. Ví dụ: nếu bạn có = COUNTIF (A1: A10, “TEST”) trong ô A11, thì bất kỳ ô nào từ A1 đến A10 có từ “kiểm tra” có thể được tính là một. Vì điều đó, nếu như bạn có năm ô trong phạm vi đấy có chứa bài kiểm tra từ, A11 sẽ nói “5.”
Công thức hàm VLOOKUP
Công thức Excel VLOOKUP là một dạng thủ thuật excel tra cứu và tham chiếu giúp chọn lựa các thành quả trong cột ngoài cùng bên trái của bảng sau đó trả về giá trị trong hàng/cột được chỉ định. Hàm VLOOKUP có công thức:
=VLOOKUP(ô chứa thành quả chọn lựa, vùng bảng chứa thành quả tìm kiếm và trả về, cột chứa thành quả trả về)
Trong ví dụ trên, dùng hàm VLOOKUP để tìm số IDD (104) trong cột ngoài cùng bên trái của bảng $E$4:$G$7 rồi trả về thành quả trong cùng một hàng từ cột thứ 3 (đối số là 3). Đối số thứ 4 đặt là FALSE để trả lại kết quả đúng hoặc lỗi #N/A nếu như không tìm được thành quả.
Vùng tham chiếu cần giữ nguyên nên bạn phải cần dùng ký hiệu $ để tạo tham chiếu tuyệt đối. Cả 3 hàm IF, SUM và VLOOKUP đều là các hàm được ứng dụng như công thức Excel tính lương cho người làm công.
Hàm MIN/MAX: Hàm cho thành quả lớn nhất, nhỏ nhất
– chức năng hàm Min/Max: Cách sử dụng hàm MAX và hàm MIN để tìm giá trị khổng lồ nhất, nhỏ nhất của các đối số hay vùng dữ liệu.
– Cú Pháp:
- MAX (number 1, number 2, …)
- MIN (number 1, number 2, …)
- Trong Đó:
- Number 1, number 2 là các đối số hoặc vùng dữ liệu.
- Trong Đó:
– Chức Năng: Trả về thành quả lớn nhất, nhỏ nhất của các đối số hoặc vùng dữ liệu có chứa số
Ví dụ:
– Đưa về thành quả lớn nhất trong dãy các số 10, -20, 3, 4, 11
- Công thức: MAX (10, -20, 3, 4, 11) = 11.
– Đưa về thành quả nhỏ nhất trong các số: 20, 100, 30, 45.
- Công thức: MIN (20, 100, 30, 45) = 20.
Hàm TRIM: Hàm loại bỏ khoảng trống trong văn bản
– chức năng hàm Trim: Hàm TRIM là một trong các hàm cơ bản Excel không thể làm ngơ giúp bạn giải quyết các khoảng trắng nằm rải rác trong bảng tính khi bạn nhập hay dán văn bản từ một cơ sở dữ liệu, một Web, một chương trình văn bản khác,… Giả sử, bạn mong muốn loại bỏ các khoảng trắng trong cột A2, bạn chỉ phải sử dụng hàm TRIM ở ô B2 với công thức: =TRIM(A2). Bạn copy bí quyết xuống phía cuối danh sách để có kết quả loại bỏ các khoảng trắng tương tự ở B2.
– Cú Pháp: TRIM(Text)
- Trong Đó: Text là đoan văn bản chứa khoảng trắng cần loại bỏ
Ví Dụ: Bạn có một bảng tính có cột họ tên chứa các ký tự trắng, bây giờ cần loại bỏ các khoảng trắng này
Hàm xóa khoảng trắng dư thừa bỏ khoảng trắng trong Excel, hàm cơ bản trong excel
Để loại bỏ các khoảng trắng, tại ô D6 nhập công thức =TRIM(A6) ta được kết quả như hình.
Hàm INDEX trong Excel
Hàm INDEX là gì?
Hàm INDEX trong Excel là một Function (chức năng) được sử dụng nhằm mục đích trả về một ô tham chiếu trong một mảng hoặc một dải cụ thể. Theo một cách khác, bạn dùng INDEX khi bạn biết (hoặc có khả năng tính toán) vị trí của một phần tử trong dải (bảng tính Excel) và bạn muốn nhận giá trị thực của phần tử đấy.
Xem thêm: Tổng hợp 5 hàm Excel nâng cao bạn nên biết
Cú pháp cơ bản của hàm INDEX
=INDEX(array;row_num;[column_num])
Trong công thức này:
– array: vùng ô hoặc một hàng số mảng nào đó;
– row_num: chọn hàng trong mảng từ đấy trả về một giá trị;
– column: chọn cột trong mảng từ đó trả về một thành quả.
Cách dùng hàm INDEX căn bản
Ví dụ: sử dụng hàm INDEX để tìm phần tử khi biết vị trí dòng và cột trong bảng dưới đây:
Hàm MATCH trong Excel
Hàm MATCH là gì?
Hàm MATCH tìm một mục được chỉ định trong phạm vi của ô, sau đấy trả về vị trí tương đối của mục đó trong phạm vi này.
VD, nếu như phạm vi từ ô A1 đến ô A3 chứa thành quả “Mã NV”, “Họ và tên”, “Giới tính” thì công thức =MATCH(“Giới tính”,A1:A3,0) sẽ trả về số 3, vì “Giới tính” là mục thứ 2 trong phạm vi này.
Cú pháp hàm MATCH
=MATCH(lookup_value,lookup_array,[match_type])
Trong công thức này:
– Lookup_value (bắt buộc): thành quả mà bạn mong muốn tìm vị trí tương đối trong mảng;
– Lookup_arry (bắt buộc): Phạm vi ô được tìm kiếm;
– match_type (tùy chọn): khái niệm cách tìm kiếm:
+ nếu như là 1 hoặc bỏ qua: Hàm MATCH chọn lựa giá trị khổng lồ nhất mà giá trị đó nhỏ hơn hoặc bằng giá trị chọn lựa (lookup_value);
+ nếu như là 0: Hàm MATCH tìm chính xác thành quả cần tìm trong vùng phạm vi;
+ nếu là -1: Hàm Match sẽ tìm thành quả nhỏ nhất mà giá trị đó lớn hơn hoặc bằng giá trị chọn lựa
Công thức trong Excel – INDEX và MATCH
ngoài ra, hàm INDEX còn có các cách dùng nâng cao và đặc biệt là một sự kết hợp có hiệu quả rất mạnh giữa hàm INDEX và MATCH.
sử dụng hàm INDEX và MATCH trong Excel để thực thi các thao tác tra cứu nâng cao (dành cho người giàu kinh nghiệm dùng Excel). con người có VD phía dưới minh họa việc sử dụng INDEX và MATCH để tra cứu 2 cột.
Chú ý: bí quyết trên tra cứu mức lương của James Clark mà không phải James Smith hay James Anderson. Bạn có thể dùng INDEX và MATCH trong Excel để gây độc đáo tốt trong mắt sếp của bạn.
Hàm LEN trong Excel
Hàm LEN là gì?
Hàm LEN là hàm sử dụng để đếm toàn bộ ký tự trong một ô, gồm có chữ cái, chữ số, ký tự đáng chú ý, và toàn bộ khoảng trống. Hàm này là một trong các hàm thông dụng và dễ nhớ nhất trong Excel. Tên hàm bắt nguồn từ 3 ký tự đầu của từ “LENGTH” – có nghĩ là chiều dài.
Cú pháp của hàm LEN
=LEN(TEXT)
trong đó, đối số text có thể là một chuỗi ký tự, văn bản hay một ô chứa các ký tự bao gồm cả chỗ trống.
Cách sử dụng hàm LEN
Bài toán dưới đây sẽ giúp các bạn hình dung cơ bản về hàm LEN.
Tổng kết
Bộ bí kíp công thức trong excel trên có làm bạn thỏa mãn. Để theo dõi nhiều hơn nữa công thức trong excel, cũng như là nguồn động lực để blogphanmem.vn có thể ra thêm nhiều hơn nữa bài viết tổng hợp về excel. Các bạn độc giả hãy theo dõi và để lại mong muốn, nguyện vọng của mình, chúng mình sẽ ra những bài học, chia sẻ kiến thức sớm nhất gửi đến cho các bạn nhé! Cảm ơn các bạn đã theo dõi bài viết của blogphanmem.vn
