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.
Làm sao để có thể sử dụng được thành thạo các thủ thuật trong excel? Bài viết này sẽ hướng dẫn cho bạn một số hàm excel thường dùng trong văn phòng hiệu quả. Cùng thietbichothue tham khảo nhé.
Các thủ thuật excel cho dân văn phòng
Chúng tôi sẽ gợi ý cho bạn 11 thủ thuật excel cho dân văn phòng dưới đây:
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.
Xem thêm: Thietbichothue.com cung cấp dịch vụ thuê laptop giá rẻ, số lượng lớn tại TPHCM
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.
-Hàm Value thường kết hợp với LEFT, RIGHT, MID…Cùng xem cách sử dụng hàm này nhé.
-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)
Nếu muốn thu nguyên phần giá trị số là “1” thay vì “01” như kết quả trên đây thì chúng ra sử dụng VALUE
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ả.
Tìm hiểu: 20 Hàm Công Thức Cơ Bản Trong Excel Cho Dân Văn Phòng
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.
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.
-Cú pháp: =IF(D2>89,”A”,IF(D2>79,”B”,IF(D2>69,”C”,IF(D2>59,”D”,”F”))))
-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.
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”))
Bài viết cùng chủ đề:
-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 (–).
Hàm so sánh trong excel
-Đây là 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.
Chuyển đổi chữ hoa và chữ thường
Dùng hàm Upper với công thức =Upper() để chuyển đổi giữa chữ hoa và chữ thường
Cách cố định dòng trong Excel
Cố định dòng đầu tiên
Để cố định hàng đầu tiên, bạn chọn vào ô bất kỳ trong sheet bảng tính => Vào View => Freeze Panes => Freeze Top Row.
Sau khi thực hiện thao tác, dòng được cố định sẽ được đánh dấu bằng 1 đường kẻ đen, đậm hơn so với đường kẻ các dòng khác ở ngay bên dưới ô dữ liệu đã khóa.
Cố định nhiều dòng
Bước 1: Chọn nhóm dòng cần cố định, sau đó bạn chọn vào ô đầu tiên của dòng nằm phía dưới nhóm dòng cần cố định.
Bước 2: Chọn thẻ View => Freeze Panes => Freeze Panes.
sau khi thực hiện tao tác sẽ có 1 đường kẻ đen, đậm hơn so với đường kẻ các dòng khác xuất hiện để ngăn cách các hàng cố định và chưa cố định.
Cách cố định cột trong Excel
Cố định cột đầu tiên
Để cố định cột đầu tiên, bạn chọn vào ô bất kỳ trong sheet bảng tính => Vào View => Freeze Panes => Freeze First Column.
Cột được cố định sẽ được đánh dấu bằng 1 đường kẻ đen, đậm hơn so với đường kẻ các cột khác ở ngay bên dưới ô dữ liệu đã khóa.
Cố định nhiều cột
Bước 1: Chọn nhóm cột cần cố định, sau đó bạn chọn vào ô đầu tiên của cột nằm bên cạnh nhóm cột cần cố định.
Bước 2: Chọn thẻ View => Freeze Panes => Freeze Panes.
Sắp xếp trong Excel
Giả sử có bảng dữ liệu sau:
Để thực hiện sắp xếp trong Excel, bạn thực hiện
- Bước 1: Chọn toàn bộ bảng dữ liệu bằng cách: Nhấn chọn ô A2 => Nhấn tổ hợp phím Ctrl + Shift + mũi tên xuống để chọn đến dòng cuối cùng => Nhấn tổ hợp phím Ctrl + Shift + mũi tên sang phải để chọn đến cột cuối cùng.
- Bước 2: Vào thẻ Data => Sort. Ở bước này, nếu muốn sắp xếp cho thẻ dữ liệu nào, các bạn điền vào mục “Sort by”, “Sort on” và “Order” như sau:
+ Sort by: Sắp xếp theo “Ngày thanh toán“
+ Sort on: Values, tức là từng giá trị ở trong ô
+ Order: Trình tự sắp xếp từ nhỏ nhất tới lớn nhất, hay là cũ nhất tới mới nhất, giá trị tăng dần theo thời gian.
- Bước 3: Nhấn OK và kết quả là:
Cách căn lề trong Excel
Trước khi căn lề, bạn cần chắc chắn rằng đơn vị đo trong Excel của mình là Centimeters. Nếu chưa ở đơn vị cm, bạn hãy thực hiện các bước sau để chuyển về đơn vị cm: Mở Excel => Chọn mục File => More => Options => Chọn Advanced => Chọn Centimeters tại Rule units => OK.
Sau đó, bạn thực hiện
Bước 1: Chọn mục Page Layout => Margins => Chọn Custom Margins.
Bước 2: Căn chỉnh lề => Nhấn OK
Cách phím tắt hữu ích trong Excel
- ALT + = :Tự động tính tổng không cần dùng hàm
- Ctrl + Shift + L :Bật tính năng lọc Data Filter cực nhanh
- Ctrl + mũi tên : Di chuyển con trỏ nhanh
- Ctrl + Shift + mũi tên : Bôi đen một vùng dữ liệu liền kề
- Ctrl + Pg Up, Pg Dn: Di chuyển giữa các Sheet
- Crtl + Shift + Tab : Di chuyển giữa các Workbook
- Ctrl + F1 : Ẩn hiện thanh ribbon
- Ctrl + Alt + V : hiển thị Paste Special
- Ctrl + Home : Di chuyển tới ô A1
- Shift + F4 : Hiển thị kết quả tiếp theo của việc tìm kiếm với
- Ctrl + G và Alt + S : mở hộp thoại Go to special
- Alt + Enter: Tạo một dòng mới trong excel
- Ctrl +: Chèn ngày hiện tại
- Ctrl + Shift +: Chèn giờ hiện tại
- Ctrl + D: Quét công thức, sao chép dữ liệu xuống
- Ctrl + 1 : Định dạng mọi thứ trong excel
- Các kiểu định dạng Formart Cells với Ctrl + Shift + ….
- Lặp lại thao tác cùng phím F4
- Ẩn cột (column) với Ctrl + )
- Ẩn và hiển thị dòng (row) với Ctrl + (
- Tạo một biểu đồ ở Sheet (bảng tính) mới với F11
- Hiển thị trước khi in với Ctrl + F2
- Lưu với tên khác cùng F12
- Bật tính năng giúp đỡ của excel với F1
- Mở danh sách xổ xuống bằng Alt + mũi tên xuống
- Di chuyển màn hình với Alt + Pg Dn, Alt + Pg Up
- Di chuyển tới ô đầu tiên của 1 dòng bằng phím home
- Kích hoạt chế độ chọn dữ liệu với Shift + F8
- Chọn đến cuối vùng dữ liệu liền kề với Ctrl + Shift + mũi tên
- Chọn dữ liệu sang trái, phải một màn hình với Alt + Shift + Pg Up, Pg Dn
- Mở rộng vùng chọn đến ô đầu tiên của hàng với Shift + Home
- Mở rộng vùng chọn đến ô đầu tiên của Sheet với Ctrl + Shift + Home
- Mở rộng vùng chọn đến ô cuối cùng có dữ liệu với Ctrl + Shift + End
- Chọn ô có chứa comment với Ctrl + Shift + O
- Chỉ chọn những ô không ẩn cùng Alt + ;
- Chèn, sửa comment với Shift + F2
- Sang trái, phải trong một ký tự hoặc một từ trong một ô excel
– Sang trái phải một ký tự: phím mũi tên sang trái hoặc phải
– Sang trái, phải MỘT TỪ: Ctrl + mũi tên sang trái hoặc phải - Chọn dữ liệu trong một ô excel
– Chọn sang trái, phải 1 ký tự: Shift + phím mũi tên sang trái phải
– Chọn sang trái, phải 1 từ: Ctrl + Shift + phím mũi tên sang trái phải
– Chọn đến vị trí bắt đầu dữ liệu của ô: Shift + Home
– Chọn đến vị trí kết thúc dữ liệu của ô: Shift + End - Xóa dữ liệu trong một ô excel
– Xóa dữ liệu bên trái con trỏ: Backspace
– Xóa dữ liệu đằng sau con trỏ: Delete
– Xóa từ vị trí con trỏ cho tới tận cùng của ô : Ctrl + Delete (tổ hợp phím này khá hữu dụng) - “Enter” dữ liệu
Xuống dưới: Enter
Lên trên: Shift + Enter
Sang trái: Shift + tab
Sang phải: Tab
Đứng yên tại chỗ: Ctrl + Enter - Nhập dữ liệu cho một vùng dữ liệu: Chọn vùng dữ liệu => viết dữ liệu bạn muốn nhập => Nhấn “Ctrl + enter”
- Sao chép công thức từ ô trên dưới
– Sao chép công thức từ ô trên : Ctrl + ‘
– Sao chép công thức từ ô dưới : Ctrl + Shift + “ - Chèn siêu liên kết hyperlink: Ctrl + K
- Mở hộp thoại cài đặt font chữ: Ctrl + Shift + F
- Gạch ngang giữa chữ: Ctrl + 5
- Căn trái phải, giữa ô với tổ hợp phím Alt +
– Căn trái: Alt + H A L
– Căn phải: Alt + H A R
– Căn giữa: Alt + H A C - Tăng giảm cỡ chữ
– Tăng cỡ chữ: Alt + H F G
– Giảm cỡ chữ: Alt + H F K - Tạo và bỏ đường viền bao quanh ô
– Tạo đường viền bao quanh ô: Ctrl + Shift + & (viền ngoài)
– Xóa đường viền bao quanh ô: Ctrl + Shift + –
– Tạo tất cả viền trong ngoài cho một vùng dữ liệu gồm nhiều ô: Alt + H B A
– Xóa tất cả viền trong ngoài cho một vùng dữ liệu gồm nhiều ô: Alt + H B N - Mở hộp thoại điền công thức Excel: Shift + F3
- Mở rộng thanh công thức: Ctrl + Shift + U
- Đặt tên cho một vùng dữ liệu: Ctrl + F3 (Chọn vùng trước – Ctrl F3 – Enter – Viết tên – enter)
- Tạo Sheet mới: Shift + F1
- Nhân đôi đối tượng với Ctrl + D
- Ẩn tất cả các đối tượng với Ctrl + 6
- Hiển thị menu chuột phải trong excel
– Nhấn chuột phải
– Dùng phím chức năng mở chuột phải có sẵn trên bàn phím
– Shift + F10. - Mở trình biên tập VBA với Alt + F11
- Bật chức năng gõ tắt trong Excel: Alt + T + A
- Hiển thị công thức: Ctrl + ~
- Chuyển đổi qua lại giữa các tab Ctrl + Page up
- Chuyển đổi qua lại giữa các Sheet: Ctrl + Page down
- Lập biểu đồ cho dữ liệu: ALT+F1
- Làm nổi bật dữ liệu: Ctrl+T
Trên đây chúng đã chia sẻ cho bạn 11 thủ thuật Excel cho dân văn phòng cần thiết và các phím tắt hữu ích cho công việc của bạn. Hy vọng những chia sẻ trên sẽ là hữu ích. Chúc các bạn sử dụng các thủ thuật trong excel thành công.
