19 mẹo, thủ thuật và phím tắt Excel

Đôi khi, Excel có vẻ quá tốt để giúp bạn thực hiện các công việc tính toán rất dễ dàng. Tất cả những gì bạn phải làm là nhập một công thức và hầu như mọi việc bạn cần làm thủ công đều có thể được thực hiện tự động.

Cần hợp nhất hai trang có dữ liệu tương tự nhau? Excel có thể làm được điều đó.

Cần làm các phép tính đơn giản? Excel có thể làm được điều đó.

Cần kết hợp thông tin trong nhiều ô? Excel có thể làm được điều đó.

Trong bài đăng này, chúng tôi sẽ giới thiệu các mẹo, thủ thuật và phím tắt Excel hay nhất mà bạn có thể sử dụng ngay bây giờ để đưa kiến thức Excel của mình lên một tầm cao mới

Lưu ý : Ảnh GIF và hình ảnh trực quan là từ phiên bản Microsoft Excel cũ. Có thể bài viết này đã được cập nhật để cung cấp hướng dẫn cho người dùng cả phiên bản Excel mới hơn.

1. Sử dụng bảng Pivot để nhận biết và hiểu dữ liệu

Bảng tổng hợp được sử dụng để sắp xếp lại dữ liệu trong bảng tính. Họ sẽ không thay đổi dữ liệu mà bạn có nhưng họ có thể tổng hợp các giá trị và so sánh các thông tin khác nhau trong bảng tính của bạn, tùy thuộc vào việc bạn muốn họ làm.

Chúng ta hãy xem một ví dụ. Giả sử tôi muốn xem có bao nhiêu người ở trong mỗi ngôi nhà ở Hogwarts. Bạn có thể nghĩ rằng tôi không có quá nhiều dữ liệu, nhưng đối với các tập dữ liệu dài hơn, điều này sẽ rất hữu ích.

Để tạo Pivot Table, tôi vào Data > Pivot Table . Nếu bạn đang sử dụng phiên bản Excel mới nhất, bạn sẽ đi tới Insert > Pivot Table . Excel sẽ tự động điền vào Bảng tổng hợp của bạn nhưng bạn luôn có thể thay đổi thứ tự của dữ liệu. Sau đó, bạn có bốn tùy chọn để lựa chọn.

• Report Filter: Điều này cho phép bạn chỉ xem xét các hàng nhất định trong tập dữ liệu của mình. Ví dụ: nếu tôi muốn tạo bộ lọc theo từng nhà, tôi có thể chọn chỉ bao gồm học sinh trong Gryffindor thay vì tất cả học sinh.
• Column Labels: Đây sẽ là tiêu đề của bạn trong tập dữ liệu.
• Row Labels: Đây có thể là các hàng của bạn trong tập dữ liệu. Cả Hàng và Cột đều có thể chứa dữ liệu từ các cột của bạn (ví dụ: Tên có thể được kéo vào Hàng hoặc Cột — điều này chỉ phụ thuộc vào cách bạn muốn xem dữ liệu.)
• Value: Phần này cho phép bạn xem dữ liệu của mình theo cách khác. Thay vì chỉ lấy bất kỳ giá trị số nào, bạn có thể tính tổng, đếm, trung bình, tối đa, tối thiểu, đếm số hoặc thực hiện một số thao tác khác với dữ liệu của mình. Trên thực tế, theo mặc định, khi bạn kéo một trường vào Giá trị, trường đó luôn được tính.

Vì tôi muốn đếm số học sinh trong mỗi nhà nên tôi sẽ đi tới trình tạo bảng Pivot và kéo cột House vào cả Row Labes và Values. Điều này sẽ tổng hợp số lượng sinh viên liên kết với mỗi nhà.

2. Chèn nhiều hàng hoặc cột

Khi bạn xử lý dữ liệu của mình, bạn có thể thấy mình liên tục cần thêm nhiều hàng và cột hơn. Đôi khi, bạn thậm chí có thể cần thêm hàng trăm hàng. Làm từng việc một sẽ cực kỳ tẻ nhạt. May mắn thay, luôn có cách dễ dàng hơn.

Để chèn nhiều hàng hoặc cột trong Excel, hãy đánh dấu cùng số lượng hàng hoặc cột có sẵn mà bạn muốn thêm. Sau đó, nhấp chuột phải và chọn “Insert”.

Trong ví dụ bên dưới, tôi muốn thêm ba hàng bổ sung. Bằng cách đánh dấu ba hàng rồi nhấp vào Insert, tôi có thể thêm ba hàng trống bổ sung vào bảng tính của mình một cách nhanh chóng và dễ dàng.

3. Sử dụng filter (bộ lọc) để đơn giản hóa dữ liệu của bạn

Khi xem các tập dữ liệu rất lớn, bạn thường không cần phải xem từng hàng cùng một lúc. Đôi khi, bạn chỉ muốn xem dữ liệu phù hợp với những tiêu chí nhất định. Vị vậy, bạn cần tới công cụ filter trong Excel

Bộ lọc cho phép bạn giảm bớt dữ liệu của mình để chỉ xem một số hàng nhất định cùng một lúc. Trong Excel, một bộ lọc có thể được thêm vào từng cột trong dữ liệu của bạn và từ đó bạn có thể chọn những ô bạn muốn xem cùng một lúc.

Chúng ta hãy xem ví dụ dưới đây. Thêm bộ lọc bằng cách nhấp vào tab Data và chọn “Filter”. Nhấp vào mũi tên bên cạnh tiêu đề cột và bạn sẽ có thể chọn xem bạn muốn sắp xếp dữ liệu của mình theo thứ tự tăng dần hay giảm dần cũng như những hàng cụ thể nào bạn muốn hiển thị.

Trong ví dụ về Harry Potter của tôi, giả sử tôi chỉ muốn gặp các học sinh ở Gryffindor. Bằng cách chọn bộ lọc Gryffindor, các hàng khác sẽ biến mất.

Mẹo: Sao chép và dán các giá trị trong bảng tính khi Filter được bật để thực hiện phân tích bổ sung trong bảng tính khác.

4. Loại bỏ các dữ liệu trùng lặp.

Tập tin dữ liệu lớn có xu hướng có nội dung trùng lặp. Bạn có thể có một danh sách nhiều địa chỉ liên hệ trong một công ty và chỉ muốn xem số lượng công ty mà bạn có. Trong những tình huống như thế này, việc loại bỏ các dữ liệu trùng lặp trong Excel là khá hữu ích.

Để xóa các trùng lặp của bạn, hãy đánh dấu hàng hoặc cột mà bạn muốn xóa. Sau đó, chuyển đến tab Data và chọn “Remove Duplicates” (nằm dưới tiêu đề phụ thanh Tools trong phiên bản Excel cũ). Một cửa sổ bật lên sẽ xuất hiện để xác nhận dữ liệu bạn muốn làm việc. Chọn “Remove Duplicates” và bạn đã hoàn thành việc xóa dữ liệu trùng lặp.

Bạn cũng có thể sử dụng tính năng này để xóa toàn bộ hàng dựa trên giá trị cột trùng lặp. Vì vậy, nếu bạn có ba hàng chứa thông tin của Harry Potter và bạn chỉ cần xem một hàng thì bạn có thể chọn toàn bộ tập dữ liệu rồi xóa các bản sao trùng lặp dựa trên email. Danh sách kết quả của bạn sẽ chỉ có tên duy nhất mà không có bất kỳ tên trùng lặp nào.

5. Chuyển hàng thành cột trong Excel

Khi bạn có các hàng dữ liệu trong bảng tính của mình, bạn có thể quyết định rằng bạn thực sự muốn chuyển đổi các mục ở một trong các hàng đó thành cột (hoặc ngược lại). Sẽ mất rất nhiều thời gian để sao chép và dán từng tiêu đề riêng lẻ — nhưng tính năng chuyển vị cho phép bạn làm chỉ đơn giản là di chuyển dữ liệu hàng của bạn vào các cột hoặc ngược lại.

Bắt đầu bằng cách đánh dấu cột mà bạn muốn chuyển thành hàng. Nhấp chuột phải vào nó và sau đó chọn “Copy”. Tiếp theo, chọn các ô trên bảng tính nơi bạn muốn bắt đầu hàng hoặc cột đầu tiên. Nhấp chuột phải vào ô và sau đó chọn “Past Special”. Một module sẽ xuất hiện ở phía dưới, bạn sẽ thấy tùy chọn chuyển đổi, tích chọn ‘ Transpose’ và chọn OK. Cột của bạn bây giờ sẽ được chuyển sang một hàng hoặc ngược lại.

Trên các phiên bản Excel mới hơn, một menu thả xuống sẽ xuất hiện thay vì cửa sổ bật lên.

6. Chia nhỏ thông tin văn bản giữa các cột

Điều gì sẽ xảy ra nếu bạn muốn chia thông tin trong một ô thành hai ô khác nhau? Ví dụ: có thể bạn muốn lấy tên công ty của ai đó thông qua địa chỉ email của họ. Hoặc có lẽ bạn muốn tách tên đầy đủ của ai đó thành họ và tên cho các mẫu tiếp thị qua email của mình.

Nhờ có Excel, cả hai đều có thể thực hiện được. Đầu tiên các bạn bôi đen cột muốn tách. Tiếp theo, chuyển đến tab Dữ liệu và chọn “Chuyển văn bản thành Cột”. Một mô-đun sẽ xuất hiện với thông tin bổ sung.

Trước tiên, bạn cần chọn “Delimited” hoặc “Fixed Width”.

• “Delimited” có nghĩa là bạn muốn chia nhỏ cột dựa trên các ký tự như dấu phẩy, dấu cách hoặc tab.
• “Fixed Width” có nghĩa là bạn muốn chọn vị trí chính xác trên tất cả các cột mà bạn muốn phân chia xảy ra.

Trong trường hợp ví dụ bên dưới, hãy chọn “Delimited” để chúng ta có thể tách tên đầy đủ thành họ và tên.

Sau đó, đã đến lúc chọn Dấu phân cách. Đây có thể là tab, dấu chấm phẩy, dấu phẩy, dấu cách hoặc thứ gì khác. (“Ví dụ: Nội dung khác” có thể là ký hiệu “@” được sử dụng trong địa chỉ email.) Trong ví dụ của chúng tôi, hãy chọn khoảng trắng. Sau đó, Excel sẽ hiển thị cho bạn bản xem trước các cột mới của bạn sẽ trông như thế nào.

Khi bạn hài lòng với bản xem trước, hãy nhấn “Next”. Trang này sẽ cho phép bạn chọn Định dạng nâng cao nếu bạn chọn. Khi bạn hoàn tất, hãy nhấp vào “Finish”.

7. Sử dụng công thức để tính toán đơn giản

Ngoài việc thực hiện các phép tính khá phức tạp, Excel có thể giúp bạn thực hiện các phép tính đơn giản như cộng, trừ, nhân hoặc chia bất kỳ dữ liệu nào của bạn.

• Để thực hiện phép cộng, hãy sử dụng dấu +
• Để thực hiện phép trừ, sử dụng dấu –
• Để thực hiện phép nhân, hãy sử dụng dấu *
• Để thực hiện phép chia, hãy sử dụng dấu /

Bạn cũng có thể sử dụng dấu ngoặc đơn để đảm bảo một số phép tính nhất định được thực hiện trước. Trong ví dụ bên dưới (10+10*10), 10 thứ hai và thứ ba được nhân với nhau trước khi cộng thêm 10. Tuy nhiên, nếu chúng ta tạo thành (10+10)*10 thì 10 thứ nhất và thứ hai sẽ được cộng lại với nhau trước.

8. Lấy giá trị trung bình của các số trong ô của bạn

Nếu bạn muốn tính trung bình cộng của một tập hợp số, bạn có thể sử dụng công thức =AVERAGE(Cell1:Cell2) . Nếu bạn muốn tính tổng một cột số, bạn có thể sử dụng công thức =SUM(Cell1:Cell2) .

9. Sử dụng Conditional Formatting để làm cho các ô tự động đổi màu dựa trên dữ liệu

Định dạng có điều kiện cho phép bạn thay đổi màu của ô dựa trên thông tin trong ô. Ví dụ: nếu bạn muốn gắn cờ một số số nhất định trên mức trung bình hoặc nằm trong 10% dữ liệu hàng đầu trong bảng tính của mình, bạn có thể làm điều đó. Nếu bạn muốn tô màu điểm chung giữa các hàng khác nhau trong Excel, bạn có thể làm điều đó. Điều này sẽ giúp bạn nhanh chóng xem được thông tin quan trọng đối với bạn.

Để bắt đầu, hãy đánh dấu nhóm ô bạn muốn sử dụng định dạng có điều kiện. Sau đó, chọn “Conditional Formatting” từ menu Home và chọn logic của bạn từ danh sách thả xuống. (Bạn cũng có thể tạo quy tắc của riêng mình nếu muốn điều gì đó khác biệt) Một cửa sổ sẽ bật lên nhắc bạn cung cấp thêm thông tin về quy tắc định dạng của mình. Chọn “OK” khi bạn hoàn tất và bạn sẽ thấy kết quả của mình tự động xuất hiện.

10. Sử dụng công thức IF để tự động hóa một số hàm Excel nhất định.

Đôi khi, chúng ta không muốn đếm số lần một giá trị xuất hiện. Thay vào đó, chúng ta muốn nhập thông tin khác nhau vào một ô nếu có ô tương ứng chứa thông tin đó.

Ví dụ, trong tình huống dưới đây, tôi muốn thưởng mười điểm cho tất cả những người thuộc nhà Gryffindor. Thay vì nhập thủ công số 10 bên cạnh tên của mỗi học sinh Gryffindor, tôi có thể sử dụng công thức IF Excel để nói rằng nếu học sinh đó ở Gryffindor thì họ sẽ nhận được 10 điểm.

Công thức là: IF(logic_test, value_if_true, [value_if_false])

Ví dụ hiển thị bên dưới: =IF(D2=”Gryffindor”,”10″,”0″)

Nói chung, công thức sẽ là IF(Kiểm tra logic, giá trị đúng, giá trị sai). Hãy đi sâu vào từng biến này.

• Logical_Test : Kiểm tra logic là phần “IF” của câu lệnh. Trong trường hợp này, logic là D2=”Gryffindor” vì chúng tôi muốn đảm bảo rằng ô tương ứng với học sinh ghi “Gryffindor”. Hãy nhớ đặt Gryffindor trong dấu ngoặc kép ở đây.
• Value_if_True : Đây là những gì chúng ta muốn ô hiển thị nếu giá trị là đúng. Trong trường hợp này, chúng tôi muốn ô hiển thị “10” để cho biết rằng học sinh đã được thưởng 10 điểm. Chỉ sử dụng dấu ngoặc kép nếu bạn muốn kết quả là văn bản thay vì số.
• Value_if_False : Đây là những gì chúng ta muốn ô hiển thị nếu giá trị sai. Trong trường hợp này, đối với bất kỳ học sinh nào không thuộc Gryffindor, chúng tôi muốn ô hiển thị “0”. Chỉ sử dụng dấu ngoặc kép nếu bạn muốn kết quả là văn bản thay vì số.

Lưu ý: Trong ví dụ trên, tôi đã thưởng 10 điểm cho mọi người trong Gryffindor. Nếu sau này tôi muốn tính tổng tổng số điểm thì tôi sẽ không thể làm được vì số 10 nằm trong dấu ngoặc kép, do đó chúng biến thành văn bản chứ không phải là số mà Excel có thể tính tổng.

Sức mạnh thực sự của hàm IF xuất hiện khi bạn xâu chuỗi nhiều câu lệnh IF cùng nhau, hoặc làm tổ chúng. Điều này cho phép bạn đặt nhiều điều kiện, nhận được kết quả cụ thể hơn và cuối cùng sắp xếp dữ liệu của bạn thành các phần dễ quản lý hơn.

Phạm vi là một cách để phân đoạn dữ liệu của bạn để phân tích tốt hơn. Ví dụ: bạn có thể phân loại dữ liệu thành các giá trị nhỏ hơn 10, 11 đến 50 hoặc 51 đến 100. Đây là cách thực hiện:

=IF(B3<11,”10 or ít hơn”,IF(B3<51,”11 to 50”,IF(B3<100,”51 to 100”)))

Việc này có thể mất một số lần thử và sai, nhưng một khi bạn đã quen với nó, các công thức IF sẽ trở thành người bạn tốt nhất trong Excel mới của bạn.

11. Sử dụng ký hiệu $ để giữ nguyên công thức tham chiếu của một ô bất kể nó di chuyển đến đâu

Bạn đã bao giờ nhìn thấy ký hiệu $ trong công thức Excel chưa? Khi được sử dụng trong công thức, nó không đại diện cho đồng đô la Mỹ mà thay vào đó, nó đảm bảo rằng cột và hàng chính xác được giữ nguyên ngay cả khi bạn sao chép cùng một công thức ở các hàng liền kề.

Ví dụ: khi bạn tham chiếu đến ô A5 từ ô C5 – theo mặc định là tương đối. Trong trường hợp đó, thực ra bạn đang đề cập đến một ô có năm cột ở bên trái (C trừ A) và trong cùng một hàng (5). Đây được gọi là một công thức tương đối. Khi bạn sao chép một công thức tương đối từ ô này sang ô khác, nó sẽ điều chỉnh các giá trị trong công thức dựa trên vị trí được di chuyển. Nhưng đôi khi, chúng ta muốn những giá trị đó giữ nguyên cho dù chúng có được di chuyển xung quanh hay không và chúng ta có thể làm điều đó bằng cách chuyển công thức thành công thức tuyệt đối.

Để thay đổi công thức tương đối (=A5+C5) thành công thức tuyệt đối, chúng ta sẽ đặt trước các giá trị hàng và cột bằng ký hiệu đô la, như sau: (=$A$5+$C$5). Tìm hiểu thêm trên trang hỗ trợ của Microsoft Office tại đây.

12. Sử dụng hàm VLOOKUP để kéo dữ liệu từ vùng này sang vùng khác.

Bạn đã bao giờ có hai bộ dữ liệu trên hai bảng tính khác nhau mà bạn muốn kết hợp thành một bảng tính duy nhất chưa?

Ví dụ: bạn có thể có danh sách tên của mọi người bên cạnh địa chỉ email của họ trong một bảng tính và danh sách địa chỉ email của những người đó bên cạnh tên công ty của họ trong bảng tính khác — nhưng bạn muốn có tên, địa chỉ email và tên công ty của những người đó xuất hiện ở một nơi.

Tôi phải kết hợp các tập dữ liệu như thế này rất nhiều — và khi tôi làm vậy, VLOOKUP là công thức tôi sử dụng.

Tuy nhiên, trước khi bạn sử dụng công thức, hãy chắc chắn rằng bạn có ít nhất một cột xuất hiện giống hệt nhau ở cả hai vị trí. Quét các tập dữ liệu của bạn để đảm bảo cột dữ liệu bạn đang sử dụng để kết hợp thông tin của mình hoàn toàn giống nhau, không có khoảng trắng thừa.

Công thức: =VLOOKUP(lookup value, table array, column number, So sánh gần đúng (TRUE) hoặc So sánh chính xác (FALSE))

Công thức có các biến từ ví dụ của chúng tôi bên dưới: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

Trong công thức này, có một số biến. Điều sau đây đúng khi bạn muốn kết hợp thông tin trong Sheet 1 và Sheet 2 vào Sheet 1.

• Lookup value: Đây là giá trị giống hệt bạn có trong cả hai bảng tính. Chọn giá trị đầu tiên trong bảng tính đầu tiên của bạn. Trong ví dụ sau, điều này có nghĩa là địa chỉ email đầu tiên trong danh sách hoặc ô 2 (C2).
• Table array: Mảng bảng là phạm vi các cột trên Trang tính 2 mà bạn sẽ lấy dữ liệu từ đó, bao gồm cột dữ liệu giống với giá trị tra cứu của bạn (trong ví dụ của chúng tôi là địa chỉ email) trong Trang tính 1 cũng như cột dữ liệu bạn đang cố sao chép vào Trang tính 1. Trong ví dụ của chúng tôi, đây là “Trang tính2!A:B.” “A” có nghĩa là Cột A trong Trang tính 2, là cột trong Trang tính 2 nơi liệt kê dữ liệu giống với giá trị tra cứu (email) của chúng tôi trong Trang tính 1. “B” có nghĩa là Cột B, chứa thông tin chỉ có trong Trang 2 mà bạn muốn dịch sang Trang 1.
• Column number: Điều này cho Excel biết dữ liệu mới mà bạn muốn sao chép vào Trang 1 nằm ở cột nào. Trong ví dụ của chúng tôi, đây sẽ là cột chứa “Nhà”. “Nhà” là cột thứ hai trong phạm vi của chúng tôi cột (mảng bảng) nên số cột của chúng ta là 2. [ Lưu ý : Phạm vi của bạn có thể nhiều hơn hai cột. Ví dụ: nếu có ba cột trên Trang 2 — Email, Tuổi và Nhà — và bạn vẫn muốn đưa Nhà vào Trang 1, bạn vẫn có thể sử dụng hàm VLOOKUP. Bạn chỉ cần thay đổi “2” thành “3” để nó lấy lại giá trị ở cột thứ ba: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• So sánh gần đúng (TRUE) hoặc chính xác (FALSE) : Sử dụng FALSE để đảm bảo bạn chỉ lấy được các kết quả khớp có giá trị chính xác. Nếu bạn sử dụng TRUE, hàm sẽ đưa ra các kết quả khớp gần đúng.

Trong ví dụ bên dưới, Sheet 1 và Sheet 2 chứa các danh sách mô tả thông tin khác nhau về cùng một người và điểm chung giữa hai trang này là địa chỉ email của họ. Giả sử chúng tôi muốn kết hợp cả hai tập dữ liệu để tất cả thông tin về ngôi nhà từ Trang 2 được chuyển sang Trang 1.

Vì vậy, khi chúng ta nhập công thức =VLOOKUP(C2,Sheet2!A:B,2,FALSE) , chúng ta sẽ đưa tất cả dữ liệu ngôi nhà vào Sheet 1.

Hãy nhớ rằng VLOOKUP sẽ chỉ lấy lại các giá trị từ trang thứ hai ở bên phải cột chứa dữ liệu giống hệt của bạn. Điều này có thể dẫn đến một số hạn chế, đó là lý do tại sao một số người thích sử dụng hàm INDEX và MATCH hơn.

13. Sử dụng công thức INDEX và MATCH để lấy dữ liệu từ các cột ngang.

Giống như VLOOKUP, hàm INDEX và MATCH lấy dữ liệu từ một tập dữ liệu khác vào một vị trí trung tâm. Dưới đây là những khác biệt chính:

• VLOOKUP là một công thức đơn giản hơn nhiều. Nếu bạn đang làm việc với các tập dữ liệu lớn cần hàng nghìn lượt tra cứu thì việc sử dụng hàm INDEX và MATCH sẽ giảm đáng kể thời gian tải trong Excel.
• Công thức INDEX và MATCH hoạt động từ phải sang trái, trong khi công thức VLOOKUP chỉ hoạt động tra cứu từ trái sang phải. Nói cách khác, nếu bạn cần thực hiện tra cứu có cột tra cứu ở bên phải cột kết quả thì bạn phải sắp xếp lại các cột đó để thực hiện VLOOKUP. Điều này có thể tẻ nhạt với các tập dữ liệu lớn và/hoặc dẫn đến sai sót.

Vì vậy, nếu tôi muốn kết hợp thông tin trong Sheet 1 và Sheet 2 vào Sheet 1, nhưng các giá trị cột trong Sheet 1 và 2 không giống nhau, thì để thực hiện VLOOKUP, tôi cần phải chuyển đổi các cột của mình. Trong trường hợp này, thay vào đó tôi sẽ chọn thực hiện INDEX và MATCH.

Hãy xem một ví dụ. Giả sử Sheet 1 chứa danh sách tên mọi người và địa chỉ email Hogwarts của họ và Sheet 2 chứa danh sách địa chỉ email của mọi người và Thần hộ mệnh mà mỗi học sinh có. (Đối với những người không phải là người hâm mộ Harry Potter, mọi phù thủy hoặc pháp sư đều có một người bảo vệ động vật được gọi là “Thần hộ mệnh” gắn liền với họ.) Thông tin có trong cả hai trang là cột chứa địa chỉ email, nhưng cột địa chỉ email này có số cột khác nhau trên mỗi tờ. Tôi sẽ sử dụng công thức INDEX và MATCH thay vì VLOOKUP để không phải chuyển đổi bất kỳ cột nào.

Vậy công thức là gì? Công thức này thực chất là công thức MATCH được lồng bên trong công thức INDEX. Bạn sẽ thấy tôi đã phân biệt công thức MATCH bằng cách sử dụng một màu khác ở đây.

Công thức: =INDEX(table array, MATCH formula)

Điều này trở thành: =INDEX(table array, MATCH (lookup_value, lookup_array))

Công thức có các biến từ ví dụ của chúng tôi bên dưới: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Dưới đây là các biến:

• Table array: Phạm vi các cột trên Sheet 2 chứa dữ liệu mới mà bạn muốn chuyển sang Sheet 1. Trong ví dụ của chúng tôi, “A” có nghĩa là Cột A, chứa thông tin “Thần hộ mệnh” cho mỗi người.
• Lookup value: Đây là cột trong Bảng 1 chứa các giá trị giống hệt nhau trong cả hai bảng tính. Trong ví dụ sau, điều này có nghĩa là cột “email” trên Sheet 1, là Cột C. Vậy: Sheet1!C:C.
• Lookup array: Đây là cột trong Bảng 2 chứa các giá trị giống hệt nhau trong cả hai bảng tính. Trong ví dụ sau, cột này đề cập đến cột “email” trên Sheet 2, đồng thời cũng là Cột C. Vì vậy: Sheet2!C:C.

Sau khi bạn đã xác định rõ các biến của mình, hãy nhập công thức INDEX và MATCH vào ô trên cùng của cột Patronus trống trên Sheet 1, nơi bạn muốn hiển thị thông tin kết hợp.

14. Sử dụng hàm COUNTIF để đếm từ hoặc số trong phạm vi ô bất kỳ

Thay vì đếm tần suất xuất hiện của một giá trị hoặc số nhất định theo cách thủ công, hãy để Excel thực hiện công việc đó giúp bạn. Với hàm COUNTIF, Excel có thể đếm số lần một từ hoặc số xuất hiện trong bất kỳ phạm vi ô nào.

Ví dụ: giả sử tôi muốn đếm số lần từ “Gryffindor” xuất hiện trong tập dữ liệu của mình.

Công thức: =COUNTIF(range, criteria)

Công thức có các biến từ ví dụ của chúng tôi bên dưới: =COUNTIF(D:D,”Gryffindor”)

Trong công thức này, có một số biến:

• Range: Phạm vi mà chúng ta muốn công thức bao gồm. Trong trường hợp này, vì chúng tôi chỉ tập trung vào một cột nên chúng tôi sử dụng “D:D” để biểu thị rằng cột đầu tiên và cột cuối cùng đều là D. Nếu tôi đang xem cột C và D, tôi sẽ sử dụng “C:D .”
• Criteria: Bất kể số lượng hoặc đoạn văn bản nào bạn muốn Excel đếm. Chỉ sử dụng dấu ngoặc kép nếu bạn muốn kết quả là văn bản thay vì số. Trong ví dụ của chúng tôi, tiêu chí là “Gryffindor”.

Chỉ cần nhập công thức COUNTIF vào bất kỳ ô nào và nhấn “Enter” sẽ cho tôi biết từ “Gryffindor” xuất hiện bao nhiêu lần trong tập dữ liệu.

15. Kết hợp các ô bằng cách sử dụng ký tự &

Cơ sở dữ liệu có xu hướng phân tách dữ liệu để làm cho dữ liệu chính xác nhất có thể. Ví dụ: thay vì có một cột hiển thị tên đầy đủ của một người, cơ sở dữ liệu có thể có dữ liệu dưới dạng tên và sau đó là họ trong các cột riêng biệt. Hoặc, nó có thể có vị trí của một người được phân tách theo thành phố, tiểu bang và mã zip. Trong Excel, bạn có thể kết hợp các ô có dữ liệu khác nhau vào một ô bằng cách sử dụng dấu “&” trong hàm.

Công thức có các biến từ ví dụ của chúng tôi bên dưới: =A2&” “&B2

Chúng ta hãy cùng nhau xem xét công thức bằng một ví dụ. Giả sử chúng ta muốn kết hợp họ và tên thành họ và tên đầy đủ trong một cột. Để thực hiện việc này, trước tiên chúng tôi đặt con trỏ vào ô trống nơi chúng tôi muốn tên đầy đủ xuất hiện. Tiếp theo, chúng tôi sẽ đánh dấu một ô chứa tên, nhập dấu “&”, sau đó đánh dấu ô có họ tương ứng.

Nhưng bạn vẫn chưa hoàn thành — nếu tất cả những gì bạn nhập là =A2&B2 thì sẽ không có khoảng cách giữa họ và tên của người đó. Để thêm không gian cần thiết đó, hãy sử dụng hàm =A2&” “&B2 . Dấu ngoặc kép xung quanh dấu cách yêu cầu Excel đặt dấu cách ở giữa họ và tên.

Để thực hiện điều này cho nhiều hàng, chỉ cần kéo góc của ô đầu tiên đó xuống dưới như trong ví dụ.

16. Thêm checkbox (hộp kiểm)

Nếu bạn đang sử dụng bảng Excel để theo dõi dữ liệu khách hàng và muốn theo dõi nội dung nào đó không thể định lượng được, bạn có thể chèn hộp kiểm vào một cột.

Ví dụ: nếu bạn đang sử dụng trang tính Excel để quản lý triển vọng bán hàng của mình và muốn theo dõi xem bạn đã gọi cho họ trong quý vừa qua hay chưa, bạn có thể có thông báo “Đã gọi trong quý này?” và đánh dấu các ô trong đó khi bạn gọi cho khách hàng tương ứng.

Đây là cách để làm điều đó.

Đánh dấu ô bạn muốn thêm hộp kiểm vào bảng tính của mình. Sau đó, nhấp vào DEVELOPER. Sau đó, trong Form Controls, hãy nhấp vào hộp kiểm hoặc vòng tròn lựa chọn được đánh dấu trong hình ảnh bên dưới.

Khi hộp xuất hiện trong ô, hãy sao chép nó, đánh dấu các ô mà bạn cũng muốn nó xuất hiện rồi dán nó.

17. Liên kết một ô trong Excel tới một trang web

Nếu bạn đang sử dụng trang tính của mình để theo dõi số liệu về trang web hoặc mạng xã hội, việc có một cột tham chiếu chứa các liên kết mà mỗi hàng đang theo dõi sẽ rất hữu ích. Nếu bạn thêm URL trực tiếp vào Excel, URL đó sẽ tự động có thể nhấp được. Tuy nhiên, nếu bạn phải siêu liên kết các từ, chẳng hạn như tiêu đề trang hoặc tiêu đề của bài đăng bạn đang theo dõi thì đây là cách thực hiện.

Đánh dấu những từ bạn muốn siêu liên kết, sau đó nhấn Shift K. Từ đó một hộp sẽ bật lên cho phép bạn đặt URL siêu liên kết. Sao chép và dán URL vào hộp này và nhấn hoặc nhấp Enter.

Nếu phím tắt không hoạt động vì bất kỳ lý do gì, bạn cũng có thể thực hiện việc này theo cách thủ công bằng cách đánh dấu ô và nhấp vào Insert > Hyperlink .

18. Thêm menu thả xuống (Drop menu) trong Excel

Đôi khi, bạn sẽ sử dụng bảng tính của mình để theo dõi các quy trình hoặc những thứ định tính khác. Thay vì viết lặp đi lặp lại các từ vào trang tính của bạn, chẳng hạn như “Có”, “Không”, “Giai đoạn khách hàng”, “Khách hàng tiềm năng” hoặc “Khách hàng tiềm năng”, bạn có thể sử dụng menu thả xuống để nhanh chóng đánh dấu những điều mô tả về người liên hệ của mình hoặc bất cứ điều gì bạn muốn. đang theo dõi.

Đây là cách thêm danh sách thả xuống vào ô của bạn.

Đánh dấu các ô bạn muốn thả xuống, sau đó nhấp vào menu Data ở điều hướng trên cùng và nhấn Validation.

Từ đó, bạn sẽ thấy hộp thoại Data Validation mở ra. Nhìn vào các tùy chọn Cho phép, sau đó nhấp vào Data Validation. Kiểm tra nút thả xuống Trong ô, sau đó nhấn OK.

19. Sử dụng công cụ vẽ định dạng.

Như bạn có thể đã nhận thấy, Excel có rất nhiều tính năng để xử lý số liệu và phân tích dữ liệu của bạn một cách nhanh chóng và dễ dàng. Nhưng nếu bạn đã từng dành chút thời gian để định dạng một trang tính theo ý thích của mình thì bạn biết rằng việc này có thể hơi tẻ nhạt.

Đừng lãng phí thời gian lặp đi lặp lại các lệnh định dạng giống nhau. Sử dụng công cụ vẽ định dạng để dễ dàng sao chép định dạng từ vùng này sang vùng khác của trang tính. Để làm như vậy, hãy chọn ô bạn muốn sao chép, sau đó chọn tùy chọn trình vẽ định dạng (biểu tượng cọ vẽ) từ thanh công cụ trên cùng.

Phím tắt Excel

Việc tạo báo cáo trong Excel khá tốn thời gian. Làm cách nào chúng ta có thể dành ít thời gian hơn cho việc điều hướng, định dạng và chọn các mục trong bảng tính của mình? Rất vui vì bạn đã hỏi. Có rất nhiều các phím tắt Excel, bao gồm một số phím tắt yêu thích của chúng tôi được liệt kê bên dưới.

Tạo một sổ làm việc mới

Windows: Ctrl + N | Mac: Command + N

Chọn toàn bộ hàng

Windows: Shift + Space | Mac: Shift + Space

Chọn toàn bộ cột

Windows: Ctrl + Space | Mac: Control + Space

Chọn phần còn lại của cột

Windows: Ctrl + Shift + Xuống/Lên | Mac: Command + Shift + Xuống/Lên

Chọn phần còn lại của hàng

Windows: Ctrl + Shift + Phải/Trái | Mac: Command + Shift + Phải/Trái

Thêm siêu liên kết

Windows: Ctrl + K | Mac: Command + K

Mở cửa sổ định dạng ô

Windows: Ctrl + 1 | Mac: Command + 1

Tự động tính tổng các ô đã chọn

Windows: Alt + = | Mac: Command-Shift + T

Sử dụng Excel để tự động hóa các quy trình

Ngay cả khi bạn không phải là kế toán, bạn vẫn có thể sử dụng Excel để tự động hóa các nhiệm vụ và quy trình trong công việc của mình. Với các mẹo và thủ thuật mà chúng tôi đã chia sẻ trong bài viết này, bạn chắc chắn sẽ sử dụng Excel ở mức khá và tận dụng tối đa phần mềm.