Excel là phần mềm cực kỳ hữu ích nhất là trong công việc. Để thao tác với excel được nhanh, tiết kiệm thời gian, nâng cao năng suất và để mọi người thấy được sự chuyên nghiệp của bạn thì những thủ thuật excel cho dân văn phòng rất quan trọng.
Các thủ thuật excel cho dân văn phòng
1. Hàm Vlookup nhiều điều kiện
Thủ thuật excel cho dân văn phòng đầu tiên chúng tôi muốn chia sẻ là hàm Vlookup – hàm tìm kiếm có điều kiện trong excel
-Đối với hàm Vlookup cơ bản nhưng chúng ta được học thì sẽ chỉ cho phép tra cứu trên một giá trị. Nhưng nếu bạn muốn tra cứu từ 2 giá trở lên thì sao? Sẽ có cách đơn giản để thực hiện đối với trường hợp này.
Cách sử dụng hàm Vlookup nhiều điều kiện sẽ được thiết lập qua 3 bước sau:
-Thêm một cột phụ có chứa các giá trị từ những cột mà bạn muốn sử dụng để làm điều kiện.
-Sau đó, thiết lập công thức Vlookup tham chiếu đến bảng dữ liệu bao gồm cả cột phụ.
-Lưu ý cột đầu tiên trong bảng phải là cột phụ.Đối với các giá trị tra cứu, khi nối cùng với một điều kiện sẽ cùng thứ tự để phù hợp với các giá trị trong cột phụ.
Ví dụ cụ thể cho bạn:
-Công thức mảng cần phải nhấn Ctrl + Shift + Enter sau khi bạn nhập xong cú pháp hàm
F3&F4 vẫn là điều kiện tìm kiếm giống như cách 1 (là gộp của Sản phẩm và Ca làm việc)
Hàm choose kết hợp với mảng 1,2 sẽ giúp tạo ra vùng tìm kiếm được gộp bởi sản phẩm và ca
2: Số thứ tự cột tìm kiếm
0: Kiểu tìm kiếm chính xác 100%
LƯU Ý:
Bạn phải tạo thêm một cột bổ sung để liên kết các giá trị với nhau, sau đó cung cấp cho hàm VLOOKUP giá trị tìm kiếm tương tự.
Cột liên kết là một cột ngoài cùng ở phía bên trái thuộc phạm vi tra cứu vì đây chính là nơi mà Vlookup luôn tìm kiếm được giá trị tra cứu và phải chứa tất cả các giá trị tra cứu để việc ghép nối chính xác như trong tiêu chí củ bạn
Còn một điều quan trọng bạn hãy luôn nhớ đó là hàm Vlookup chỉ giới hạn trong khoảng 255 ký tự. Lưu ý điều đó và đảm bảo rằng tổng chiều dài của các tiêu chí tra cứu bạn mong muốn không vượt quá giới hạn.
2. Hàm lấy giá trị của ô trong Excel
-Bài viết này chúng tôi sẽ chia sẻ cho bạn hàm Value. Với hàm này có thể sử dụng cho các phiên bản Excel như Excel 2016, 2013, 2010, 2007, 2003 và cả Excel cho MAC, iPhone, Android.
-Cú pháp: VALUE (text)
Text là chuỗi kí tự, có thể là ngày tháng, thời gian hay hằng số.
Text phải được đặt trong cặp dấu ngoặc kép (” “) hoặc tham chiếu đến ô chứa văn bản bạn muốn chuyển đổi.
-Ví dụ:
Ta có cột Số là giá trị của mã nhân viên, nếu muốn lọc 2 số cuối của mã nhân viên ra, vậy làm như thế nào ?
Ta sẽ sử dụng Value kết hợp với Right để cắt 2 số cuối. Công thức sẽ là =RIGHT (C5,2)
Ta có công thưc: =VALUE (RIGHT (A4,2))
Cuối cùng là kéo chuột sao chép công thức xuống các ô còn lại ta sẽ được kết quả.
3. Hàm đếm số lần xuất hiện trong Excel
-Với trường hợp này ta sẽ sử dụng hàm COUNTIF để đếm số lần xuất hiện ký tự trong excel
-Ta có công thức: COUNTIF(range;criteria)
Range: phạm vi các ô để đếm.
Criteria: các tiêu chí kiểm soát các ô nào sẽ được tính
-Ví dụ: Cho một bảng danh sách các tên của các bạn trong một lớp và các ngày đi trực tuần của mỗi bạn, đếm xem số lần mỗi bạn đi trực:
Nhập công thức: =COUNTIF(B3:B17;B3)
Kết quả sau khi nhấn Enter sẽ là:
Những lưu ý cần biết:
Hàm COUNTIF trong Excel đếm số lượng ô trong phạm vi phù hợp với tiêu chí được cung cấp.
Các tiêu chí không phải là số cần được đặt trong dấu ngoặc kép nhưng tiêu chí số thì không.
Hàm này được áp dụng cho tất cả các bản microsoft excel tất cả các năm.
4. Hàm IF có 3 điều kiện – hàm IF với điều kiện là chữ
-Đối với những trường hợp dùng hàm if có 3 điều kiện thì ta có công thức: =IF(Điều gì đó là True, thì làm gì, nếu không thì làm gì khác)
-Một câu lệnh IF có thể có hai kết quả. Kết quả đầu tiên là nếu so sánh của bạn là True, kết quả thứ hai là nếu so sánh của bạn là False.
-Ví dụ: Ta sẽ có bảng chuyển đổi các điểm kiểm tra của học viên thành điểm theo chữ cái tương đương.
-Giải thích cho cú pháp này:
Nếu Điểm Kiểm tra (trong ô D2) lớn hơn 89 thì học viên nhận điểm A
Nếu Điểm Kiểm tra lớn hơn 79 thì học viên nhận điểm B
Nếu Điểm Kiểm tra lớn hơn 69 thì học viên nhận điểm C
Nếu Điểm Kiểm tra lớn hơn 59 thì học viên nhận điểm D
Các trường hợp khác học viên nhận điểm F
-Lưu ý: Hàm trong Excel đều yêu cầu dấu ngoặc tròn mở và dấu ngoặc tròn đóng (). Excel sẽ cố gắng giúp bạn xác định điểm bắt đầu và kết thúc bằng cách tô màu cho các phần khác nhau trong công thức khi bạn chỉnh sửa công thức.
5. Hàm Sumproduct tính tổng nhiều điều kiện
Hàm sumproduct là gì?
-Sumproduct là một hàm rất hữu ích khi nói đến việc so sánh dữ liệu ở hai hay nhiều dải ô và việc tính toán dữ liệu có nhiều điều kiện.
-Ta có công thức : 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ố.
Số mảng tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010, và Excel 2007, và 30 trong các phiên bản trước của Excel.
-Ví dụ: Giả sử có danh sách các món hàng ở cột A, doanh số bán dự kiến ở cột B, và doanh số bán thực ở cột C. Bạn muốn tìm xem có bao nhiêu món bán ít hơn dự kiến.
-Đối với trường hợp này, ta có cú pháp: =SUMPRODUCT(-(C2:C10<B2:B10))
Trong đó C2:C10 là doanh số thực
B2:B10 là doanh số dự kiến.
-Nhưng nếu bạn có nhiều hơn một điều kiện thì sao? Ví dụ bạn muốn đếm số lần Táo bán ít hơn dự kiến thì ta phải thêm một điều kiện nữa vào công thức SUMPRODUCT:
=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”apples”))
-Một số lưu ý đối với hàm này:
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ử logic, thì nó sẽ trả về giá trị TRUE và FALSE. Thông thường, 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 (-).
6. Hàm so sánh trong excel
-Đây là hàm cuối cùng trong thủ thuật Excel cho dân văn phòng ở bài viết này, chúng tôi sẽ nói đến hàm EXACT
-Chúng ta dùng hàm Exact để so sánh hai chuỗi văn bản và trả về TRUE nếu chúng hoàn toàn giống nhau, FALSE nếu khác. Hàm EXACT phân biệt chữ hoa, chữ thường nhưng bỏ qua khác biệt về định dạng.
-Ta có công thức: EXACT(text1, text2)
Text1 Bắt buộc. Chuỗi văn bản thứ nhất.
Text2 Bắt buộc. Chuỗi văn bản thứ hai.
-Ví dụ: Hàm Exact trong trường hợp này sẽ so sánh 4 cặp chuỗi văn bản khác nhau.
-Ta có cú pháp:
-Kết quả trả về là:
-Chú ý: Vì hàm Exact trong Excel phân biệt chữ hoa chữ thường, hàm trong ô C2 sẽ trả về kết quả là FALSE.