Cách sử dụng Excel: 14 mẹo, thủ thuật và lối tắt Excel đơn giản

Đôi khi, Excel có vẻ quá tốt là đúng. Tất cả tôi phải làm là nhập một công thức, và khá nhiều bất cứ điều gì tôi đã bao giờ cần phải làm bằng tay có thể được thực hiện tự động.
Cần hợp nhất hai trang tính với dữ liệu tương tự? Excel có thể làm điều đó. Cần làm toán đơn giản? Excel có thể làm điều đó. Cần kết hợp thông tin trong nhiều ô? Excel có thể làm điều đó.

Nếu bạn gặp phải tình huống mà bạn cần phải cập nhật dữ liệu của mình theo cách thủ công, có thể bạn đang bỏ lỡ một công thức có thể làm điều đó cho bạn. Trước khi chi tiêu hàng giờ và đếm các ô hoặc sao chép và dán dữ liệu, hãy tìm một bản sửa lỗi nhanh trên Excel - bạn có thể sẽ tìm thấy một.

Với tinh thần làm việc hiệu quả hơn và tránh được công việc thủ công, tẻ nhạt, đây là một vài thủ thuật Excel để bạn bắt đầu với cách sử dụng Excel. (Và tất cả những người hâm mộ Harry Potter ở đó ... bạn đều được chào đón trước.)

Cách sử dụng Excel

Nếu bạn chỉ mới bắt đầu với Excel, có một vài lệnh cơ bản mà chúng tôi khuyên bạn nên làm quen với. Đây là những thứ như:
  • Tạo bảng tính mới từ đầu.
  • Thực hiện tính toán cơ bản trong bảng tính, như cộng, trừ, nhân và chia trong bảng tính.
  • Viết và định dạng văn bản và tiêu đề cột.
  • Các tính năng tự động điền của Excel.
  • Thêm hoặc xóa các cột, hàng và bảng tính đơn lẻ. Bên dưới, chúng ta sẽ tìm hiểu cách thêm những thứ như nhiều cột và hàng.
  • Giữ tiêu đề cột và hàng hiển thị khi bạn cuộn qua chúng trong bảng tính để bạn biết bạn đang điền dữ liệu nào khi bạn di chuyển xuống dưới tài liệu.
Để biết sâu hơn về những điều cơ bản này, hãy xem hướng dẫn toàn diện của chúng tôi về Cách sử dụng Excel .

Được rồi, sẵn sàng để có được thành nitty-gritty? Chúng ta hãy đi đến đó.

14 Mẹo Excel để Thử

1) Bảng xoay vòng
Bảng tổng hợp được sử dụng để sắp xếp lại dữ liệu trong bảng tính. Chúng sẽ không thay đổi dữ liệu mà bạn có, nhưng chúng 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 những gì bạn muốn chúng 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ẽ có ích.

Để tạo Bảng tổng hợp, tôi chuyển đến Dữ liệu> Bảng tổng hợp. Excel sẽ tự động điền Bảng Pivot của bạn, nhưng bạn luôn có thể thay đổi theo thứ tự của dữ liệu. Sau đó, bạn có bốn tùy chọn để chọn.
  1. Bộ lọc báo cáo: Đ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 nhà, tôi có thể chọn chỉ bao gồm sinh viên ở Gryffindor thay vì tất cả sinh viên.
  2. Nhãn cột: Đây có thể là tiêu đề của bạn trong tập dữ liệu.
  3. Nhãn hàng: Đây có thể là các hàng của bạn trong tập dữ liệu. Cả hai hàng và cột nhãn có thể chứa dữ liệu từ các cột của bạn (ví dụ như tên đầu tiên có thể được kéo đến một trong hai hàng hoặc nhãn cột - nó chỉ phụ thuộc vào cách bạn muốn xem dữ liệu.)
  4. Giá trị: Phần này cho phép bạn xem dữ liệu của mình khác nhau. Thay vì chỉ kéo 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, đế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. Thực tế, theo mặc định, khi bạn kéo một trường vào Value, nó luôn luôn đếm.
Vì tôi muốn đếm số lượng học sinh trong mỗi ngôi nhà, tôi sẽ chuyển đến Bảng tổng hợp và kéo cột Nhà tới cả Nhãn hàng và Giá trị. Điều này sẽ tổng hợp số lượng học sinh được liên kết với mỗi ngôi nhà.
Bảng xoay vòng
2) Thêm nhiều hơn một hàng hoặc cột mới
Khi bạn chơi xung quanh với dữ liệu của mình, bạn có thể thấy mình liên tục cần phải 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 điều này một-by-một sẽ là siêu tẻ nhạt. May mắn thay, luôn luôn có một cách dễ dàng hơn.

Để thêm nhiều hàng hoặc cột trong bảng tính, hãy đánh dấu cùng một số hàng hoặc cột có sẵn từ trước mà bạn muốn thêm. Sau đó, nhấp chuột phải và chọn "Chèn".

Trong ví dụ bên dưới, tôi muốn thêm ba hàng bổ sung. Bằng cách làm nổi bật ba hàng và sau đó nhấp vào chèn, tôi có thể thêm ba hàng trống vào bảng tính của tôi một cách nhanh chóng và dễ dàng.
Bộ lọc
3) Bộ lọc
Khi bạn đang xem các tập dữ liệu rất lớn, bạn thường không cần xem từng hàng đơn cùng một lúc. Đôi khi, bạn chỉ muốn xem dữ liệu phù hợp với các tiêu chí nhất định. Đó là nơi các bộ lọc đi vào.

Bộ lọc cho phép bạn chia nhỏ dữ liệu của mình để chỉ xem các 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 mỗi cột trong dữ liệu của bạn - và từ đó, bạn có thể chọn ô nào 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 Dữ liệu và chọn "Bộ lọc". 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 dữ liệu của mình được sắp xếp theo thứ tự tăng dần hay giảm dần, cũng như các hàng cụ thể bạn muốn hiển thị.

Trong ví dụ 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 chuyên nghiệp: Sao chép và dán các giá trị trong bảng tính khi Bộ lọc bật để thực hiện phân tích bổ sung trong một bảng tính khác.

4) Xóa các mục trùng lặp
Tập dữ liệu lớn hơn có xu hướng có nội dung trùng lặp. Bạn có thể có 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 bạn có. Trong các tình huống như thế này, việc loại bỏ các bản sao sẽ khá tiện dụng.

Để xóa các mụ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 các bản sao của. Sau đó, chuyển đến tab Dữ liệu và chọn "Xóa các mục trùng lặp" (trong Công cụ). Cửa sổ bật lên sẽ xuất hiện để xác nhận bạn muốn làm việc với dữ liệu nào. Chọn "Xóa các mục trùng lặp" và bạn đã sẵn sàng sử dụng.
Xóa các mục 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 với thông tin của Harry Potter và bạn chỉ cần xem một thông tin, thì bạn có thể chọn toàn bộ tập dữ liệu và sau đó xóa các bản sao dựa trên email. Danh sách kết quả của bạn sẽ chỉ có các tên duy nhất mà không có bất kỳ bản sao nào.

5) Transpose
Khi bạn có các hàng dữ liệu thấp trong bảng tính của mình, bạn có thể quyết định bạn thực sự muốn chuyển đổi các mục trong một trong các hàng đó thành các cột (hoặc ngược lại). Phải 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 tiếp cho phép bạn thực hiện 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 theo cách khác.

Bắt đầu bằng cách làm nổi bật 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 "Sao chép". Tiếp theo, chọn các ô trên bảng tính của bạn nơi bạn muốn hàng hoặc cột đầu tiên bắt đầu. Nhấp chuột phải vào ô, sau đó chọn "Dán đặc biệt". Một mô-đun sẽ xuất hiện - ở dưới cùng, bạn sẽ thấy một tùy chọn để chuyển đổi. Đánh dấu vào ô đó và chọn OK. Bây giờ cột của bạn sẽ được chuyển sang một hàng hoặc ngược lại.
6) Văn bản thành Cột
Điều gì sẽ xảy ra nếu bạn muốn tách thông tin trong một ô thành hai ô khác nhau? Ví dụ: có thể bạn muốn rút tên công ty của một người nào đó thông qua địa chỉ email của họ. Hoặc có thể bạn muốn tách họ tên của một người nào đó thành tên và họ của các mẫu tiếp thị email của bạn.

Nhờ Excel, cả hai đều có thể. Đầu tiên, đánh dấu cột mà bạn muốn chia nhỏ. Tiếp theo, chuyển đến tab Dữ liệu và chọ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.

Đầu tiên, bạn cần chọn "Delimited" hoặc "Fixed Width".
  • "Được phân tách" 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.
  • "Chiều rộng cố định" 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 chia tách xảy ra.
Trong trường hợp ví dụ bên dưới, hãy chọn "Phân cách" để chúng tôi có thể tách tên đầy đủ thành tên và họ.

Sau đó, đã đến lúc chọn Dấu phân tách. Đây có thể là một tab, dấu chấm phẩy, dấu phẩy, dấu cách hoặc thứ khác. ("Cái gì khác" có thể là dấu "@" được sử dụng trong một địa chỉ email, ví dụ.) Trong ví dụ của chúng ta, hãy chọn không gian. Excel sau đó sẽ hiển thị cho bạn bản xem trước về 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, nhấn "Tiếp theo". 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 "Hoàn tất".
Công thức Excel

Công thức Excel

7) 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 phép tính số học đơ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êm, hãy sử dụng dấu +.
  • Để trừ, sử dụng dấu -.
  • Để nhân, sử dụng dấu *.
  • Để chia, sử dụng dấu /.
Bạn cũng có thể sử dụng dấu ngoặc đơn để đảm bảo các tính toán nhất định được thực hiện trước tiên. Trong ví dụ dưới đây (10 + 10 * 10), số thứ hai và thứ ba 10 được nhân với nhau trước khi thêm 10. Tuy nhiên, nếu chúng ta tạo nó (10 + 10) * 10, số thứ nhất và thứ hai sẽ được cộng với nhau trước .
Tính toán đơn giản
Tiền thưởng: Nếu bạn muốn mức trung bình của một tập hợp các số, bạn có thể sử dụng công thức = AVERAGE (Dải ô). Nếu bạn muốn tổng hợp một cột số, bạn có thể sử dụng công thức = SUM (Dải ô).

8) Công thức định dạng có điều kiện
Đị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ờ các số nhất định trên mức trung bình hoặc 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 các 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 thông tin quan trọng đối với bạn.

Để bắt đầu, hãy chọn nhóm ô bạn muốn sử dụng định dạng có điều kiện. Sau đó, chọn "Định dạng có điều kiện" từ menu Trang chủ và chọn logic của bạn từ trình đơn thả xuống. (Bạn cũng có thể tạo quy tắc của riêng mình nếu bạn muốn một thứ gì đó khác.) 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 bạn. 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.
Công thức định dạng có điều kiện
9) IF tuyên bố
Đôi khi, chúng tôi không muốn đếm số lần 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ó một ô tương ứng với thông tin đó.

Ví dụ, trong tình huống dưới đây, tôi muốn trao 10 điểm cho tất cả những ai thuộc về nhà Gryffindor. Thay vì nhập bằng tay vào 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 THEN Excel để nói rằng nếu học sinh ở trong Gryffindor, thì họ sẽ nhận được mười điểm.

Công thức: IF (logical_test, value_if_true, value of false)

Ví dụ được 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 hợp lý, giá trị của giá trị true, true). Chúng ta hãy thâm nhập vào từng biến này.
  • Logical_Test: Phép thử 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 ta muốn đảm bảo rằng ô tương ứng với học sinh nói "Gryffindor". Hãy chắc chắn để đặ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à true. Trong trường hợp này, chúng tôi muốn ô hiển thị "10" để cho biết rằng sinh viên đã được trao 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ì một số.
  • Value_if_False: Đây là những gì chúng ta muốn ô hiển thị nếu giá trị là false. Trong trường hợp này, đối với bất kỳ học sinh nào không có trong Gryffindor, chúng tôi muốn ô hiển thị "0" để hiển thị 0 đ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ì một số.
Lưu ý: Trong ví dụ trên, tôi đã trao 10 điểm cho mọi người trong Gryffindor. Nếu sau này tôi muốn tổng hợp tổng số điểm, tôi sẽ không thể bởi vì 10 của các dấu ngoặc kép, do đó làm cho chúng văn bản và không phải là một số mà Excel có thể tổng hợp.

10) Ký hiệu đô la
Bạn đã bao giờ nhìn thấy một dấu đô la trong một công thức Excel? Khi được sử dụng trong một công thức, nó không đại diện cho đồng đô la 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 trong các hàng liền kề.

Bạn thấy đấy, một tham chiếu ô - khi bạn tham khảo ô A5 từ ô C5, ví dụ - là tương đối theo mặc định. Trong trường hợp đó, bạn đang thực sự đề 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à công thức tương đối. Khi bạn sao chép một công thức tương đối từ một ô này sang ô khác, công thức sẽ điều chỉnh các giá trị trong công thức dựa trên nơi nó được di chuyển. Nhưng đôi khi, chúng tôi muốn những giá trị đó vẫn giữ nguyên cho dù chúng có di chuyển hay không - và chúng ta có thể làm điều đó bằng cách làm cho công thức trong ô thành một 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 tôi sẽ đặt trước giá trị hàng và cột theo 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 .)

Hàm Excel

11) Hàm VLOOKUP
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 chưa?

Ví dụ: bạn có thể có danh sách tên người bên cạnh địa chỉ email của họ trong một bảng tính và danh sách các địa chỉ email của cùng một người đó bên cạnh tên công ty của họ ở bên kia - nhưng bạn muốn tên, địa chỉ email và công ty tên 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, VLOOKUP là công thức của tôi. 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 nhau ở cả hai nơi. Xáo trộn 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 bạn giống hệt nhau, bao gồm không có khoảng trắng thừa.

Công thức: = VLOOKUP (giá trị tra cứu, mảng bảng, số cột, [phạm vi tra cứu])

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

Trong công thức này, có một số biến. Sau đây là sự thật khi bạn muốn kết hợp thông tin trong Trang tính 1 và Trang 2 vào Trang tính 1.
  • Giá trị tra cứu: Đây là giá trị giống hệt nhau mà 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).
  • Bảng Array: Phạm vi của các cột trên Bảng 2 bạn sẽ kéo dữ liệu của bạn 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, địa chỉ email) trong Bảng 1 cũng như cột dữ liệu bạn ' đang cố gắng sao chép vào trang tính 1. Trong ví dụ của chúng tôi, đây là "Sheet2! A: B." "A" có nghĩa là Cột A trong Bảng 2, là cột trong Trang 2 nơi dữ liệu giống với giá trị tra cứu của chúng tôi (email) trong Trang tính 1 được liệt kê. Chữ "B" có nghĩa là Cột B, chứa thông tin chỉ có sẵn trong Trang tính 2 mà bạn muốn dịch sang Trang tính 1.
  • Số cột: Nếu mảng bảng (phạm vi các cột bạn vừa chỉ ra) điều này cho Excel biết cột nào bạn muốn sao chép dữ liệu mới vào Bảng 1. Trong ví dụ của chúng tôi, đây sẽ là cột "Nhà" nằm in. "House" là cột thứ hai trong phạm vi cột của chúng tôi (mảng bảng), vì vậy số cột của chúng tôi 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à lên Bảng 1, bạn vẫn có thể sử dụng VLOOKUP. Bạn chỉ cần thay đổi "2" thành "3" để nó kéo trở lại giá trị trong cột thứ ba: = VLOOKUP (C2: Sheet2! A: C, 3, false).]
  • Tra cứu phạm vi: Sử dụng FALSE để đảm bảo bạn chỉ lấy các giá trị khớp chính xác.
Trong ví dụ bên dưới, Trang tính 1 và Trang 2 chứa các danh sách mô tả thông tin khác nhau về cùng một người và chuỗi chung giữa hai địa chỉ này là địa chỉ email của họ. Giả sử chúng ta muốn kết hợp cả hai bộ dữ liệu sao cho tất cả thông tin nhà từ Bảng 2 chuyển sang Bảng 1.
Hàm VLOOKUP
Vì vậy, khi chúng ta gõ vào công thức = VLOOKUP (C2, Sheet2! A: B, 2, FALSE) , chúng ta mang tất cả dữ liệu về nhà vào Sheet 1.

Hãy nhớ rằng VLOOKUP sẽ chỉ lấy lại các giá trị từ trang tính thứ hai ở bên phải của cột chứa dữ liệu giống hệt nhau 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 thay thế.

12) INDEX MATCH
Giống như hàm VLOOKUP, hàm INDEX và MATCH kéo 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:
  1. 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 yêu cầu hàng nghìn lần tra cứu, sử dụng hàm INDEX MATCH sẽ làm giảm đáng kể thời gian tải trong Excel.
  2. Hàm INDEX MATCH làm việc từ phải sang trái, trong khi công thức VLOOKUP chỉ hoạt động như tra cứu từ trái qua 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ủa 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 lỗi.
Vì vậy, nếu tôi muốn kết hợp thông tin trong Bảng 1 và Bảng 2 vào Trang tính 1, nhưng các giá trị cột trong Trang tính 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, tôi sẽ chọn để làm một INDEX MATCH thay thế.

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

Vậy công thức là gì? Công thức INDEX MATCH thực sự là công thức MATCH được lồng 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 (mảng bảng, công thức MATCH)

Điều này sẽ trở thành: = INDEX (mảng bảng, MATCH ( lookup_value, lookup_array) )

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

Dưới đây là các biến:
  • Bảng Array: Phạm vi của các cột trên Bảng 2 có chứa dữ liệu mới mà bạn muốn chuyển sang Trang tính 1. Trong ví dụ của chúng tôi, "A" có nghĩa là Cột A, chứa thông tin "Người bảo trợ" cho mỗi người.
  • Giá trị tra cứu: Đây là cột trong Trang tính 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 Trang tính 1, là Cột C. Vì vậy: Sheet1! C: C.
  • Mảng tìm kiếm: Đây là cột trong Trang 2 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ập đến cột "email" trên Trang 2, điều này cũng xảy ra với Cột C. Vì vậy: Sheet2! C: C.
Khi bạn có các biến của mình thẳng, hãy nhập công thức INDEX MATCH vào ô trên cùng của cột Vị trí trống trên Trang tính 1, nơi bạn muốn thông tin được kết hợp hiển thị.
INDEX MATCH
13) Hàm COUNTIF
Thay vì đếm theo cách thủ công tần suất một giá trị hoặc số nào đó xuất hiện, hãy để Excel thực hiện công việc cho 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 tôi.

Công thức: = COUNTIF (phạm vi, tiêu chí)

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:
  • Phạm vi: Phạm vi mà chúng tôi muốn công thức bao gồm. Trong trường hợp này, vì chúng ta chỉ tập trung vào một cột, chúng ta sử dụng "D: D" để chỉ ra rằng cột đầu tiên và cuối cùng là cả D. Nếu tôi nhìn vào các cột C và D, tôi sẽ sử dụng "C: D . "
  • Tiêu chí: Dù số lượng hoặc phần văn bản bạn muốn Excel được tính. 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ì một 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ẽ hiển thị cho tôi số lần từ "Gryffindor" xuất hiện trong tập dữ liệu.
Hàm COUNTIF
14) Kết hợp các tế bào bằng cách sử dụng "&"
Cơ sở dữ liệu có xu hướng tách ra dữ liệu để làm cho nó chính xác nhất có thể. Ví dụ: thay vì có dữ liệu 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ách nhau bởi thành phố, tiểu bang và mã zip. Trong Excel, bạn có thể kết hợp các ô với dữ liệu khác nhau vào một ô bằng cách sử dụng dấu "&" trong hàm của bạn.

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

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

Nhưng bạ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 trống giữa tên và họ 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 quanh không gian cho Excel biết để đặt dấu cách ở giữa tên và họ.

Để thực hiện điều này đúng 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ụ.
Kết hợp các tế bào bằng cách sử dụng "&"
Chúng tôi hy vọng bạn thấy bài viết này hữu ích! Đánh dấu trang để giữ các mẹo Excel hữu ích này trong túi sau của bạn.

Nguồn bài viết: https://blog.hubspot.com/marketing/how-to-use-excel-tips

Mời các bạn viết lời bình cho bài viết!

Nhấp bài bình luận SEO