>

Bỏ Túi 11 Thủ Thuật Excel Cho Dân Văn Phòng

298e14c6 9314 4f28 9232 c3b1a1245570 1

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 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:

ket hop cong thuc mang de ham vlookup tim kiem 2 dieu kien trong excel 1
Hàm vlookup nhiều điều kiệ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.info 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ụ:

0001
Hàm lấy giá trị của ô trong excel

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)

thietbichothue.info
Hàm lấy giá trị của ô trong excel

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ả.

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:

 

1234 1
Hàm lấy giá trị của ô trong excel

Nhập công thức: =COUNTIF(B3:B17;B3)

12344 1
Hàm đếm số lần xuất hiện trong excel

Kết quả sau khi nhấn Enter sẽ là:

123456 1
Hàm đếm số lần xuất hiện trong excel

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”))))

298e14c6 9314 4f28 9232 c3b1a1245570 1
Hướng dẫn dùng hàm if có 3 điều kiện

-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)) 

 

82 1
Hàm sumproduct tính tổng nhiều điều kiện

 

  • 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”))

 

83 1
Hàm sumproduct tính tổng nhiều điều kiện

-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ị TRUEFALSE. Thông thường, cần phải đổi các giá trị TRUEFALSE 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:

ham exact trong excel so sanh 2 chuoi van ban 1 1
Hàm so sánh chuỗi trong excel

-Kết quả trả về là:

ham exact trong excel so sanh 2 chuoi van ban 2 1 1
Hàm so sánh chuỗi trong excel

-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

chuyen chu thuong thanh chu hoa trong

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.

cach co dinh cot dong trong

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.

cach co dinh cot dong trong excel 1

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.

cach co dinh cot dong trong excel 2

Bước 2: Chọn thẻ View => Freeze Panes => Freeze Panes.

cach co dinh cot dong trong excel 3

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.

cach co dinh cot dong trong excel 4

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.

cach co dinh cot dong trong excel 5

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.

cach co dinh cot dong trong excel 6

Bước 2: Chọn thẻ View => Freeze Panes => Freeze Panes.

cach co dinh cot dong trong excel 7

Sắp xếp trong Excel

Giả sử có bảng dữ liệu sau:

sap xep trong

Để 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.

sap xep trong excel 1

  • Bước 3: Nhấn OK và kết quả là:

sap xep trong excel 2

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.

cach can chinh le trong

Bước 2: Căn chỉnh lề => Nhấn OK

cach can chinh le trong excel 1 1

Cách phím tắt hữu ích trong Excel

  1. ALT + = :Tự động tính tổng không cần dùng hàm
  2. Ctrl + Shift + L :Bật tính năng lọc Data Filter cực nhanh
  3. Ctrl + mũi tên : Di chuyển con trỏ nhanh
  4. Ctrl + Shift + mũi tên : Bôi đen một vùng dữ liệu liền kề
  5. Ctrl + Pg Up, Pg Dn: Di chuyển giữa các Sheet
  6. Crtl + Shift + Tab : Di chuyển giữa các Workbook
  7. Ctrl + F1 : Ẩn hiện thanh ribbon
  8. Ctrl + Alt + V : hiển thị Paste Special
  9. Ctrl + Home : Di chuyển tới ô A1
  10. Shift + F4 : Hiển thị kết quả tiếp theo của việc tìm kiếm với
  11. Ctrl + G và Alt + S : mở hộp thoại Go to special
  12. Alt + Enter: Tạo một dòng mới trong excel
  13. Ctrl +:  Chèn ngày hiện tại
  14. Ctrl + Shift +: Chèn giờ hiện tại
  15. Ctrl + D: Quét công thức, sao chép dữ liệu xuống
  16. Ctrl + 1 : Định dạng mọi thứ trong excel
  17. Các kiểu định dạng Formart Cells với Ctrl + Shift + ….
  18. Lặp lại thao tác cùng phím F4
  19. Ẩn cột (column) với Ctrl + )
  20. Ẩn và hiển thị dòng (row) với Ctrl + (
  21. Tạo một biểu đồ ở Sheet (bảng tính) mới với F11
  22. Hiển thị trước khi in với Ctrl + F2
  23. Lưu với tên khác cùng F12
  24. Bật tính năng giúp đỡ của excel với F1
  25. Mở danh sách xổ xuống bằng Alt + mũi tên xuống
  26. Di chuyển màn hình với Alt + Pg Dn, Alt + Pg Up
  27. Di chuyển tới ô đầu tiên của 1 dòng bằng phím home
  28. Kích hoạt chế độ chọn dữ liệu với Shift + F8
  29. Chọn đến cuối vùng dữ liệu liền kề với Ctrl + Shift + mũi tên
  30. Chọn dữ liệu sang trái, phải một màn hình với Alt + Shift + Pg Up, Pg Dn
  31. Mở rộng vùng chọn đến ô đầu tiên của hàng với Shift + Home
  32. Mở rộng vùng chọn đến ô đầu tiên của Sheet với Ctrl + Shift + Home
  33. Mở rộng vùng chọn đến ô cuối cùng có dữ liệu với Ctrl + Shift + End
  34. Chọn ô có chứa comment với Ctrl + Shift + O
  35. Chỉ chọn những ô không ẩn cùng Alt + ;
  36. Chèn, sửa comment với Shift + F2
  37. 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
  38. 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
  39. 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)
  40. “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
  41. 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”
  42. 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 + “
  43. Chèn siêu liên kết hyperlink: Ctrl + K
  44. Mở hộp thoại cài đặt font chữ: Ctrl + Shift + F
  45. Gạch ngang giữa chữ: Ctrl + 5
  46. 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
  47. Tăng giảm cỡ chữ
    – Tăng cỡ chữ: Alt + H F G
    – Giảm cỡ chữ: Alt + H F K
  48. 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
  49. Mở hộp thoại điền công thức Excel: Shift + F3
  50. Mở rộng thanh công thức: Ctrl + Shift + U
  51. Đặ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)
  52. Tạo Sheet mới: Shift + F1
  53. Nhân đôi đối tượng với Ctrl + D
  54. Ẩn tất cả các đối tượng với Ctrl + 6
  55. 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.
  56. Mở trình biên tập VBA với Alt + F11
  57. Bật chức năng gõ tắt trong Excel: Alt + T + A
  58. Hiển thị công thức: Ctrl + ~
  59. Chuyển đổi qua lại giữa các tab Ctrl + Page up
  60. Chuyển đổi qua lại giữa các Sheet: Ctrl + Page down
  61. Lập biểu đồ cho dữ liệu: ALT+F1
  62. 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.

Trả lời

Email của bạn sẽ không được hiển thị công khai.