Học không giới hạn 600+ khóa học chỉ với 199K / tháng Xem ngay

Danh mục

Doanh nghiệp

Hội viên

Đăng nhập

Đăng ký

Tin Học Văn Phòng

Cách dùng hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel

Cách dùng hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel Hàm SUMPRODUCT có chức năng tính tổng nhiều điều kiện trong excel. Đây là bí quyết giúp người làm văn phòng tiết kiệm thời gian và nâng cao năng suất làm việc. Sau đây, Unica sẽ Hướng dẫn cách dùng hàm SUMPRODUCT trong Excel chi tiết và dễ làm. Hàm sumproduct là gì? Hàm sumproduct tính tổng nhiều điều kiện là một hàm tính toán trong các bảng tính trong học Excel. Nó thực hiện phép nhân giữa các phần tử tương ứng của hai hoặc nhiều mảng và sau đó tính tổng của các kết quả nhân đó. Hàm SUMPRODUCT thường được sử dụng để tính tổng các giá trị Ý nghĩa hàm sumproduct trong excel Hàm sumproduct tính tổng nhiều điều kiện trong Excel có ý nghĩa quan trọng trong việc thực hiện các tính toán phức tạp. Cụ thể, ý nghĩa của hàm SUMPRODUCT là: Tính tổng các giá trị nhân: Hàm sumproduct tính tổng nhiều điều kiện cho phép tính tổng các kết quả nhân của các phần tử tương ứng trong các mảng khác nhau. Tính năng này sẽ giúp bạn tính tổng các giá trị nhân trong một dãy số hoặc trong các phép tính tương tự như nhân ma trận. Áp dụng các tiêu chí lọc dữ liệu: Hàm tính tổng nhiều điều kiện sumproduct trong Excel có thể được sử dụng để chọn lọc các tiêu chí lọc dữ liệu và tính tổng các giá trị phù hợp với các điều kiện được chỉ định. Bằng cách sử dụng các biểu thức logic như "<", ">", "=", "<=", ">=", "<>", bạn có thể lọc dữ liệu và tính tổng các giá trị thỏa mãn các tiêu chí mình đặt ra. Tính toán trên các phạm vi không liên tục: Một điểm mạnh của hàm sumproduct tính tổng nhiều điều kiện là cho phép bạn thực hiện tính toán trên các phạm vi không liên tục trong một bảng tính. Bạn có thể chỉ định các phạm vi không liên tục và hàm SUMPRODUCT sẽ tính tổng các giá trị tương ứng trong các phạm vi mà bạn khoanh vùng. Kết hợp với các hàm khác cùng SUMPRODUCT: Không chỉ dùng riêng lẻ mà hàm tổng có điều kiện SUMPRODUCT có thể kết hợp với các hàm khác trong Excel như IF, SUMIF, COUNTIF và nhiều hàm khác để thực hiện các tính toán phức tạp. Bằng cách kết hợp các hàm này, bạn có thể tạo ra các công thức mạnh mẽ để phân tích dữ liệu và thực hiện các tính toán đa dạng. Kết hợp SUMPRODUCT với các hàm khác Tóm lại, hàm cộng có nhiều điều kiện trong excel sumproduct là một công cụ quan trọng trong Excel để tính toán, lọc dữ liệu và phân tích dữ liệu. Hàm này có tính linh hoạt cao, cho phép người dùng sử dụng riêng lẻ hoặc kết hợp với một số hàm khác để đạt được kết quả mình mong muốn. Công thức tính của hàm SUMPRODUCT Về mặt kỹ thuật, hàm sumproduct tính tổng nhiều điều kiện trong Excel sẽ nhân các số trong các mảng đã chỉ định và trả về tổng của các sản phẩm đó. Công thức hàm Sumproduct: = SUMPRODUCT (array1, [array2], [array3],...) Trong đó: array1: là mảng đầu tiên bắt buộc phải có nếu muốn nhân các thành phần của nó rồi cộng tổng lại. [array2], [array3] (Mảng 1, mảng 2, v.v.): là các phạm vi ô hoặc mảng liên tục tiếp theo có các phần tử mà bạn muốn nhân, sau đó cộng tổng vào. Số lượng mảng tối thiểu là 1 trong trường hợp này, công thức SUMPRODUCT chỉ đơn giản là cộng tất cả các phần tử mảng và trả về tổng. Số lượng 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 Excel trước đó. Mặc dù hàm tổng nhiều điều kiện SUMPRODUCT hoạt động với các mảng, nhưng nó không yêu cầu sử dụng phím tắt mảng (Ctrl + Shift + Enter). Bạn cạnh tranh một công thức SUMPRODUCT theo cách thông thường bằng cách nhấn phím Enter. Dùng hàm SUMPRODUCT Lưu ý: Tất cả các mảng trong công thức SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không, bạn sẽ nhận được #VALUE! lỗi. Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số, chúng sẽ được coi là số không. Nếu một mảng là một bài kiểm tra logic, nó sẽ dẫn đến các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn cần chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử đơn nguyên kép (-). Tổng hợp không hỗ trợ các ký tự đại diện. >> Xem thêm: Cách kết hợp hàm sumif và hàm vlookup chi tiết nhất, kèm ví dụ Ví dụ cách sử dụng hàm SUMPRODUCT Giả sử bạn có số lượng trong các ô C2:C8, giá trong các ô D2:D8 và bạn muốn tìm hiểu tổng số. Nếu bạn đang làm bài kiểm tra toán ở trường, bạn sẽ tính tích số lượng với giá cho mỗi mục, sau đó cộng các tổng phụ. Trong Microsoft Excel, bạn có thể nhận kết quả bằng một công thức SUMPRODUCT duy nhất: =SUMPRODUCT(C2:C8,D2:D8) Ví dụ về hàm SUMPRODUCT >>> Xem thêm: Cách sử dụng hàm SUMIFS trong Excel tính tổng nhiều điều kiện 6 Cách tính tổng trong Excel siêu nhanh và chính xác Cách sử dụng hàm SUMPRODUCT cơ bản Hiện tại có 3 cách sử dụng hàm sumproduct để tính tổng các mã giống nhau, tính tổng theo điều kiện và tính tổng theo điều kiện có kết hợp với OR (hoặc). Từng cách cụ thể như sau: Hàm SUMPRODUCT tính tổng các mã giống nhau Bạn có thể sử dụng hàm sumproduct khi học Excel để tính tổng nhiều điều kiện để tính tổng các giá trị trong một dãy nếu điều kiện của chúng thỏa mãn. Ví dụ, bạn muốn tính tổng các giá trị trong một dãy A1:A11 nếu chúng giống nhau với giá trị trong ô B3, bạn có thể sử dụng công thức sau: =SUMPRODUCT((A1:A11=B3)*(A1:A11)) Hàm SUMPRODUCT tính tổng theo điều kiện Hàm tính tổng có điều kiện trong excel SUMPRODUCT cũng có thể được sử dụng để tính tổng các giá trị trong một dãy theo một điều kiện. Ví dụ, bạn muốn tính tổng các giá trị trong một dãy C2:C7 nếu chúng lớn hơn 6, bạn có thể sử dụng công thức sau: =SUMPRODUCT((C2:C7>6)*(C2:C7)) Dùng hàm SUMPRODUCT để tính tổng theo điều kiện Hàm SUMPRODUCT tính tổng theo điều kiện có kết hợp với OR (hoặc) Ngoài sử dụng một điều kiện để tính tổng các giá trị trong một dãy thì bạn có thể kết hợp nhiều điều kiện với nhau. Lúc này, thay vì dùng hàm SUMPRODUCT tính tổng theo 1 điều kiện thì bạn sẽ dùng hàm SUMPRODUCT kết hợp cùng OR theo ví dụ dưới đây: Tính tổng các giá trị trong một dãy A1:A10 nếu chúng lớn hơn 6 hoặc nhỏ hơn 5, bạn có thể sử dụng công thức sau: =SUMPRODUCT((C2:C7>6)+(C2:C7<5)*(C2:C7)) Tính tổng nhiều điều kiện với SUMPRODUCT Lưu ý: Trong các công thức hàm sumproduct tính tổng nhiều điều kiện bên trên, chúng ta sử dụng toán tử "*" để thực hiện phép nhân và "+" để thực hiện phép cộng logic OR. Bạn có thể điều chỉnh các phạm vi và điều kiện trong công thức sao cho phù hợp với mong muốn của mình. Video hướng dẫn sử dùng hàm sumproduct Cách dùng hàm SUMPRODUCT nâng cao Hàm SUMPRODUCT rất linh hoạt, bạn có thể sử dụng hàm này kết hợp với một số hàm khác để thực hiện những phép tính phức tạp hơn. Để giúp bạn hiểu hơn về cách dùng hàm sumproduct trong excel, ở phần này, chúng tôi sẽ lấy một vài ví dụ nâng cao như sau: Đếm số sản phẩm thỏa mãn điều kiện so sánh với 1 số cụ thể Thông thường, bạn sẽ sử dụng hàm đếm có điều kiện COUNTIF để thực hiện đếm số sản phẩm theo 1 điều kiện cụ thể. Nhưng thay vì dùng COUNTIF, bạn có thể dùng công thức sumproduct có điều kiện sau để thực hiện việc này: = SUMPRODUCT(-(X:Y>Z)) Trong đó: Biểu thức (X:Y>Z) để thực hiện so sánh từng phần tử của mảng với giá trị Z Sau khi so sánh xong từng phần tử trong mảng, hàm Sumproduct lúc này sẽ trở thành dạng: SUMPRODUCT(- {FALSE, FALSE, TRUE, TRUE, FALSE}) Lý do đặt dấu “-” trước mảng với mục đích là để biến các giá trị TRUE/ FALSE về dạng số nguyên -1 và 0 thì kết quả mới có thể chính xác. Hoặc bạn cũng có thể sử dụng 2 dấu “-” để các giá trị TRUE/ FALSE trả về 1 và 0. Ví dụ: Đếm số sản phẩm từ ô C2 đến C7 thỏa mãn điều kiện lớn hơn 150 =SUMPRODUCT(--(C2:C7>150))= 3 Đếm số sản phẩm thỏa mãn điều kiện so sánh với 1 số cụ thể Đếm số sản phẩm khi so sánh 2 mảng cùng với nhau Khi đếm số sản phẩm khi cùng so sánh hai mảng với nhau thì bạn không thể sử dụng hàm COUNTIF. Thay vào đó, bạn nên dùng hàm sumproduct tính tổng nhiều điều kiện theo công thức: =SUMPRODUCT(-(A:B>C:D)) Trong đó: Biểu thức (A:B>C:D) sẽ thực hiện so sánh từng phần tử của cả hai mảng với nhau. Cụ thể, bạn sẽ so sánh A với C, B với D Khi so sánh xong các phần tử của cả 2 mảng, SUMPRODUCT sẽ trở thành dạng: SUMPRODUCT(- {TRUE, FALSE, FALSE, FALSE, TRUE}) Lý do đặt dấu “-” trước mảng để đưa hàm về dạng SUMPRODUCT({-1, 0, 0, 0, -1}), kết quả bằng -2. Nếu bạn sử dụng thêm 1 dấu nữa thì kết quả trả về sẽ là 2. Ví dụ: =SUMPRODUCT(-(D2:D7>C2:C7))=2 Đếm số sản phẩm khi so sánh 2 mảng cùng với nhau Dùng hàm SUMPRODUCT nhiều điều kiện để thay cho hàm COUNTIF Tính tổng có 2 điều kiện trong excel là phép toán phổ biến. Để giúp bạn hiểu rõ hơn về hàm tính tổng trong excel có điều kiện này, chúng tôi sẽ lấy một ví dụ cụ thể về tính tổng theo điều kiện trong excel như sau: =SUMPRODUCT((A4:A10=”táo”)*(C4:C10>100))=2 Trong đó: A4:A10=”táo”: Lọc toàn bộ giá trị là “táo” C4:C10>100: Lọc các giá trị lớn hơn 100 (A4:A10=”táo”)*(C4:C10>100): Kết hợp lọc kết hợp các giá trị là “táo” và lớn hơn 100 >>> Xem thêm: Bật mí các phím tắt tính tổng trong excel nhanh, đơn giản nhất TẤT CẢ ĐỀU CÓ TRONG CUỐN SÁCH "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Một số lỗi khi sử dụng hàm SUMPRODUCT Khi sử dụng hàm SUMPRODUCT, bạn sẽ thường gặp một vài lỗi như sau: Lỗi #VALUE! Lỗi này thường xảy ra khi các phạm vi hoặc mảng không có cùng kích thước. Để tránh lỗi này khi dùng hàm tính tổng theo điều kiện, bạn cần đảm bảo rằng các phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT có cùng kích thước. Lỗi #VALUE! thường xảy ra khi các phạm vi hoặc mảng không có cùng kích thước Lỗi #DIV/0! Lỗi #DIV/0! xảy ra khi bạn chia một số cho 0 trong hàm sumproduct tính tổng nhiều điều kiện. Bạn cần kiểm tra lại các phép tính trong hàm SUMPRODUCT để đảm bảo không có chia cho 0 xảy ra. Lỗi #NAME? Lỗi này xảy ra khi Excel không nhận ra tên hàm SUMPRODUCT do bạn viết sai chính tả hoặc thiếu chữ trong từ “SUMPRODUCT”. Cách khắc phục lỗi này là bạn cần nhập đúng cú pháp và viết chính xác tên hàm SUMPRODUCT. Lỗi #NAME? xảy ra khi Excel không nhận ra tên hàm SUMPRODUCT Lỗi #REF! Lỗi #REF! xảy ra khi các phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT đã bị thay đổi hoặc xóa bỏ. Hãy kiểm tra lại các phạm vi và mảng trong hàm SUMPRODUCT để đảm bảo chúng không bị thay đổi hoặc xóa bỏ so với lúc ban đầu. Lỗi #NUM! Nguyên nhân dẫn tới Lỗi #NUM! là khi các giá trị trong các phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT không hợp lệ. Cách tránh lỗi này là bạn cần đảm bảo các giá trị bạn sử dụng trong hàm SUMPRODUCT đúng định dạng và phù hợp với yêu cầu của công thức. Lỗi #N/A Lỗi #N/A xảy ra khi không tìm thấy giá trị phù hợp trong các phạm vi hoặc mảng được sử dụng trong hàm SUMPRODUCT. Cách khắc phục là bạn cần kiểm tra lại các giá trị và điều kiện trong hàm SUMPRODUCT để đảm bảo tìm thấy giá trị phù hợp. Lỗi #N/A xảy ra khi không tìm thấy giá trị phù hợp trong các phạm vi Tóm lại, khi gặp lỗi trong lúc dùng hàm SUMPRODUCT, bạn cần kiểm tra lại công thức và các phạm vi, mảng, giá trị được sử dụng trong hàm SUMPRODUCT. Đồng thời, hãy đảm bảo rằng các đối tượng được tham chiếu trong hàm SUMPRODUCT không bị thay đổi hoặc xóa bỏ. Ngoài những bước mà Unica đã chia sẻ, nếu bạn đọc muốn tìm hiểu chi tiết hơn nữa về cách sử dụng hàm sumproduct tính tổng nhiều điều kiện trong Excel thì có thể tham khảo Video bài giảng sau: Kết luận Như vậy, Unica đã hướng dẫn các bạn về hàm SUMPRODUCT và cách tính tổng có điều kiện trong Excel đúng và nhanh chóng, hy vọng bài viết này sẽ giúp ích được nhiều cho bạn trong quá trình làm việc sớm hoàn thành công việc của mình.

25446 Lượt xem

Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ kèm theo

Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ kèm theo Hàm Subtotal trong Excel thường được sử dụng vào việc tính tổng dãy số có điều kiện hoặc dùng để đếm các ô không trống trong một vùng dữ liệu. Đây là một trong những hàm quan trọng đối với những người làm kế toán bởi khả năng làm việc linh hoạt. Trong nội dung bài viết này, Unica sẽ cùng bạn tìm hiểu cách dùng hàm Subtotal để thực hiện các tác vụ công việc trong Excel một cách đơn giản, nhanh chóng. Hàm SUBTOTAL là gì? Hàm Subtotal là một hàm tính toán tập hợp con của bảng dữ liệu hoặc danh sách. Nhiệm vụ chính của hàm Subtotal đó chính là để tính tổng phụ trong bảng dữ liệu hoặc danh sách. Nó có khả năng tính toán các giá trị subtotal trong khi bỏ qua các ô được ẩn hoặc đã được lọc trong Excel. Ngoài ra, bạn có thể sử dụng kết hợp các hàm khác như hàm Sum, hàm Min, hàm Max,... Với tính năng độc đáo của mình, hàm Subtotal là một hàm được ứng dụng nhiều trong việc tạo và tổng hợp các báo cáo, dữ liệu từ các tệp dữ liệu lớn. Đặc biệt, khi còn phải làm việc với các dữ liệu, dữ liệu đã lọc thì nó là một công cụ hữu ích trong Excel. Hàm SUBTOTAL là một hàm tính tổng >>> Xem thêm: Hướng dẫn cách tạo ghi chú trong excel cho người mới Công thức tính của hàm SUBTOTAL Hàm Subtotal có công thức như sau: =SUBTOTAL(function_num, ref1, ref2,...) Trong đó: function_num: Số xác định loại phép tính cần thực hiện. Hàm Subtotal thực hiện được nhiều các loại phép tính khác nhau, bạn có thể chọn các số tương ứng với các phép tính bạn muốn. Các số này là một giá trị từ 1 đến 11 (tính cả các giá trị ẩn), hoặc từ 101 đến 111 (bỏ qua các giá trị ẩn). ref1: Phạm vi hoặc tập hợp các giá trị muốn thực hiện phép tính trên. [ref2]: Cung cấp nhiều phạm vi hoặc các tập hợp giá trị khác để cùng thực hiện phép tính. Công thức hàm Subtotal trong Excel Đối sổ ref1 là bắt buộc bởi vì đât là phạm vi ban đầu cần phải tính tổng bằng 1 trong 11 hàm đó. Bạn có thể thêm tới 254 phạm vi ô khác nhau. Nó sẽ tạo ra một tổng hợp kết hợp bằng tổng số liệu trên tất cả các phạm vi ô bao gồm. Trong hàm SUBTOTAL có sẵn 11 hàm khác nhau, tương ứng như sau: 1 hoặc 101: Hàm Average (Trung bình). 2 hoặc 202: Hàm Count (Đếm số ô có giá trị là số). 3 hoặc 103: Hàm Counta (Đếm số ô có giá trị là số và chữ). 4 hoặc 104: Hàm Max (Giá trị lớn nhất). 5 hoặc 105: Hàm Min (Giá trị nhỏ nhất). 6 hoặc 106: Hàm Product (Tính tích nhân của các giá trị). 7 hoặc 107: Hàm Stdev (Tính độ lệch chuẩn mẫu của một tập hợp các dữ liệu). 8 hoặc 108: Hàm Stdevp (Tính độ lệch chuẩn dựa trên toàn bộ dữ liệu). 9 hoặc 109: Hàm Sum (Tính tổng). 10 hoặc 110: Hàm Var (Tính phương sai mẫu của tập hợp dữ liệu). 11 hoặc 111: Hàm Varp (Tính phương sai dựa trên toàn bộ dữ liệu). Hàm SUBTOTAL thường được ứng dụng nhiều trong ngành kế toán Để bạn có thể hiểu rõ hơn về hàm Subtotal, dưới đây là một số ví dụ bạn có thể ứng dụng trong Excel: Tính tổng trong 1 bảng: =SUBTOTAL(9,D4:D9). Tính số tiền trung bình: =SUBTOTAL(1,D4:D19). Tìm giá trị nhỏ nhất trong bảng: =SUBTOTAL(5,D5:D10) >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Cách dùng SUBTOTAL trong Excel Như đã chia sẻ ở phần trên, hàm SUBTOTAL trong excel có thể làm được rất nhiều việc, tiêu biểu nhất là để tính tổng, đếm các ô không khoảng trống và đánh số thứ tự. Cụ thể cách dùng hàm SUBTOTAL trong các trường hợp như sau: Tính tổng vùng được lọc Ví dụ: Quan sát bảng sau, yêu cầu lọc ra KPI cho đội A. Bước 1: Dùng chuột bôi đen bảng Excel chứa dữ liệu. Trên thanh công cụ, chọn Home > Chọn Sort and Filter > Chọn Filter. Thao tác trên thanh công cụ Bước 2: Trong cột C, bạn nhấn vào biểu tượng tam giác lộn ngược rồi tích chọn A và nhấn OK. Nhấn vào biểu tượng tam giác lộn ngược Bước 3: Nhập công thức =SUBTOTAL(9,D2:D11) tại ô bạn cần lấy kết quả. Giải thích công thức: function_num là 9 tại vì đây là giá trị tương ứng mà hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này được hiển thị ngay khi nhập công thức Subtotal. Vậy nên để tính tổng thì sẽ tương ứng với hàm Sum và là số 9. ref1 = D2:D11 là xác định phạm vi cần tính tổng. Kết quả sau khi hoàn thành như sau: Nhập hàm rồi nhấn enter để có kết quả Lưu ý: Tùy vào nhu cầu sử dụng, bạn có thể sử dụng 9 để tính tổng cả các giá trị bị ẩn, hoặc sử dụng 109 để bỏ qua các giá trị bị ẩn/đã lọc. >>> Xem thêm: Học tin học văn phòng online trọn đời chỉ từ 99K Đếm các ô được lọc không trống Tương tự như các cách dùng SUBTOTAL trong excel chia sẻ như trên, cách dùng hàm SUBTOTAL để đếm các ô được lọc không trống cũng tương đối đơn giản, không có gì quá phức tạp cả. Sau đây là hướng dẫn sử dụng hàm SUBTOTAL trong excel đếm các ô được lọc không trống chi tiết cho bạn tham khảo. Ví dụ: Quan sát bảng dữ liệu, yêu cầu đếm số lượng sinh viên nhóm C đã có link bài tập để thống kê. Bước 1: Lọc ra các sinh viên nhóm C như các thao tác lọc đã hướng dẫn phía trên. Bước 2: Nhập công thức =SUBTOTAL(3,D4:D14) vào ô muốn lấy kết quả. Trong đó: function_num = 3: Gía trị đối số tương ứng với hàm cần sử dụng, trong ví dụ này chúng ta chọn hàm COUNTA - tương ứng với số 3. ref1 = D4:D14: là phạm vi cần tính tổng. Lưu ý: Bạn có thể dùng giá trị đối số là 3 (tính cả các giá trị ẩn và giá trị đã lọc) hoặc 103 (bỏ qua giá trị ẩn và giá trị đã lọc) để đếm các ô không trống trong các hàng đã lọc. Kết quả sau khi hoàn thành như sau: Kết quả sau khi hoàn thành Đánh số thứ tự nhanh Cách subtotal trong excel vô cùng đơn giản, bạn chỉ cần nhập công thức =SUBTOTAL(3,$B$2:B2) vào ô A2. Trong đó: Funcation_num là 3: Ứng với hàm COUNTA - đếm ô không trống. ref1 là $B$2:B2: Đếm ký tự phát sinh trong khoảng $B$2:B2, trong đó ô B2 được cố định bằng dấu"$" ở trước địa chỉ hàng và ô. Khi bạn sao chép công thức suống dưới, các giá trị sẽ lần lượt thay đổi như kiểu $B$2:B3, $B$2:B4,... và hàm sẽ đếm đúng, tạo ra kết quả như mong muốn. Kết quả sau khi hoàn thành như sau: Kết quả sau khi hoàn thành Cách kết hợp hàm Subtotal với các hàm khác trong Excel Hàm Subtotal với hàm Vlookup Để kết hết hàm Subtotal với hà Vlookup trong Excel, bạn cần áp dụng công thức sau: =SUBTOTAL(function_num, VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup]) Hàm Vlookup được ứng dụng khi bạn muốn tìm dữ liệu trong một bảng và sẽ trả về dữ liệu khớp với các hàng ngang tương ứng. Vì vậy, khi kết hợp hai hàm này với nhau, bạn sẽ nhanh chóng phân tích được dữ liệu mà không cần phải tổng hợp một bảng khác phức tạp trước đó. Để bạn hiểu rõ hơn về cách kết hợp hai ví dụ này, dưới đây là ví dụ: Cho hai bảng dữ liệu DANH SÁCH NHÂN VIÊN NHÓM và ĐIỂM KPI CỦA NHÓM. Để tìm được điểm KPI cao nhất tháng của những nhân viên thuộc nhóm 2 bạn có thể làm theo các bước sau: Bước 1: Nhập công thức tại ô cần trả kết quả trong Excel: =SUBTOTAL(4;VLOOKUP(F5;A3:D7;3;false);VLOOKUP(F7;A3:D7;3;false)) Trong đó đối số 4 là mã hàm tương ứng với hàm Max. Hướng dẫn kết hợp hàm Subtotal với hàm Vlookup Bước 2: Nhấn Enter để nhận được kết quả. Kết quả thu được như sau Như vậy, hàm Vlookup đã nhanh chóng tìm được điểm KPI cho nhóm nhân viên 2. Hàm Subtotal kết hợp với hàm If Trong trường hợp cần lọc các dữ diệu có điều kiện hay đánh số thứ tự theo cột, bạn có thể kết hợp sử dụng hàm Subtotal với hàm If để nhanh chóng thực hiện yêu cầu của đề bài. Khi kết hợp hai hàm này với nhau, bạn áp dụng công thức sau đây: =IF(logical_test, value_if_true, SUBTOTAL(function_num, fef1,...)) Để bạn có thể dễ dàng hiểu hơn về các áp dụng này, chúng ta có một ví dụ dưới đây. Bạn có sẵn một bảng dữ liệu cần đánh số thứ tự, nhưng nó lại lộn xộn và có nhiều hàng trống. Bạn có thể kết hợp hàm If và hàm Subtotal để xử lý các tác vụ như sau: Bước 1: Ở cột STT, bạn nhập công thức =IF(B2=”,”,SUBTOTAL(3,$B$2:B2)). Hướng dẫn kết hợp hàm Subtotal và hàm If Trong đó: logical_test: Điều kiện của hàm If sẽ lấy các giá trị ở ô B2 bằng khoảng trắng. Nếu ô trắng sẽ trả về kết quả đúng. value_if_true: Giá trị trả về trống nếu điều kiện If đúng. value_if_false: Giá trị trả về nếu điều kiện hàm IF sai. Lúc này, bạn sẽ dùng hàm Subtotal để đánh số thứ tự. function_num là 3 tương ứng với hàm Counta - hàm đếm số ô không trống. Bước 2: Ấn Enter, rồi di chuyển chuột tới góc phải bên dưới của ô dữ liệu cho đến khi xuất hiện dấu cộng > ấn và kéo để áp dụng cho tất cả các ô còn lại. Kết quả thu được như sau. Kết quả thu được như sau Một số lỗi cơ bản thường xảy ra khi tính hàm SUBTOTAL trong Excel Trong quá trình sử dụng hàm SUBTOTAL để làm việc với excel chắc chắn sẽ có lúc gặp lỗi. Một số lỗi cơ bản thường xảy ra khi tính hàm SUBTOTAL trong Excel có thể kể đến như sau: #DIV/0! Lỗi #DIV/0! trong Excel xảy ra khi cần chia một tổng cụ thể cho 0 (chẳng hạn như tính giá trị trung bình hoặc độ lệch chuẩn của một dải ô không chứa số). Ngoài ra, lỗi này cũng có thể xuất hiện khi tham chiếu đến vùng dữ liệu chứa các ô trống hoặc ô không chứa giá trị số. Để khắc phục lỗi này, bạn cần đảm bảo rằng số chia trong hàm SUBTOTAL không phải là 0. Bên cạnh đó cũng cần đảm bảo rằng tham chiếu đến vùng dữ liệu chỉ chứa các ô chứa giá trị số. Lỗi #DIV/0! #VALUE! Lỗi #VALUE xảy ra khi số xác định chức năng funcation_num không nằm trong khoảng 1-11 hoặc101-111. Hoặc tham chiếu ref tham chiếu đến các ô trong một trang tính. Ví dụ: bạn đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy nào đó ở sheet STT1. Fix lỗi#VALUE! trong hàm SUBTOTAL Cách fix lỗi như sau: Nếu sai số xác định chức năng, điều đầu tiên bạn cần làm là kiểm tra lại xem mình đã dùng đúng số chức năng chưa hoặc trong quá trình nhập có dư ký tự nào không. Nếu nguyên nhân là do lỗi tham chiếu, bạn hãy xóa phần tham chiếu sai đi, thay thế lại bằng dãy ô đúng được đặt trong cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL. #NAME Khi bạn viết tên hàm SUBTOTAL bị sai chính tả thì sẽ xuất hiện lỗi #NAME. Để khắc phục lỗi này bạn hãy chú ý viết thật đúng tên hàm nhé. Kết luận Trên đây Unica đã chia sẻ với bạn tất tần tật các thông tin có liên quan tới hàm SUBTOTAL trong excel. Hy vọng rằng những thông tin này hữu ích giúp bạn có quá trình làm việc với excel được chủ động và mang lại hiệu quả cao hơn. Chúc các bạn có một buổi học Excel online hiệu quả.

17135 Lượt xem

Cách sử dụng hàm power trong excel để tính bình phương, lũy thừa

Cách sử dụng hàm power trong excel để tính bình phương, lũy thừa Bạn biết gì về hàm POWER trong excel? Bạn đã biết chức năng và cách sử dụng của hàm này hay chưa? Nếu bạn chưa chắc chắn với cách sử dụng của hàm này, thì bài viết dưới đây sẽ giúp các bạn giải đáp những thắc mắc về hàm POWER nhé. Hàm power trong excel là gì? Hàm POWER là hàm bình phương trong excel, dùng để tính lũy thừa của một số. Công thức power trong excel như sau: =POWER(số_cơ_sở, số_mũ) Trong đó: Số_cơ_sở: là số mà bạn muốn tính lũy thừa. Số_mũ: là số mũ mà bạn muốn nâng số_cơ_sở lên. Hàm POWER là hàm bình phương trong excel Ví dụ: Tính số mũ bậc 3 của 2. =POWER(2,3) sẽ trả về kết quả là 8, vì 2 mũ 3 bằng 8. >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Ứng dụng của hàm POWER trong thực tế Hàm power excel có rất nhiều ứng dụng trong Toán học và trong công việc. Để tìm hiểu sâu hơn, mời bạn theo dõi nội dung dưới đây: Ứng dụng của hàm power trong excel với Toán học Lệnh power trong excel có thể dùng để tính các giá trị toán học phức tạp, như căn bậc hai, căn bậc ba, hay logarit. Ví dụ: Để tính căn bậc ba của một số, bạn có thể dùng hàm POWER với số_mũ là 1/3. Ví dụ: =POWER(27,1/3) sẽ trả về kết quả là 3, vì căn bậc ba của 27 bằng 3. Để tính logarit cơ số 10 của một số, bạn có thể dùng hàm POWER với số_cơ_sở là 10 và số_mũ là -1. Ví dụ: =POWER(10,-1) sẽ trả về kết quả là -1, vì logarit cơ số 10 của 10 bằng -1. Để tính căn bậc hai trong Excel của một số, bạn có thể dùng hàm POWER với số_mũ là 0.5. Ví dụ: =POWER(9,0.5) sẽ trả về kết quả là 3, vì căn bậc hai của 9 bằng 3. Kết quả căn bậc hai của 9 bằng 3 Ứng dụng trong các Công ty Hàm POWER có thể dùng để tính các chỉ số kinh doanh, như lãi suất kép, tăng trưởng theo năm hay giá trị hiện tại. Chi tiết như sau: Để tính lãi suất kép của một khoản đầu tư Bạn có thể dùng hàm POWER với số_cơ_sở là (1 + lãi suất) và số_mũ là số năm. Ví dụ: Cho bảng dữ liệu 1: Cho bảng dữ liệu Yêu cầu: Nếu bạn đầu tư 100 triệu đồng với lãi suất 10% mỗi năm, thì sau 5 năm bạn sẽ nhận được bao nhiêu tiền lãi? Cách dùng hàm mũ trong excel: Bước 1: Nhập công thức =POWER(1+0,1;5)*100 vào ô D24. Nhập công thức POWER Bước 2: Nhấn Enter sẽ thu được kết quà là 161,051. Kết quả sau khi nhập công thức Dùng hàm power trong excel tính tăng trưởng theo năm của một doanh nghiệp Bạn có thể dùng hàm POWER với số_cơ_sở là (doanh thu năm sau / doanh thu năm trước) và số_mũ là (1 / số năm). Ví dụ: Cho bảng dữ liệu 2: Cho bảng dữ liệu Yêu cầu: Nếu doanh thu của một công ty năm 2020 là 500 triệu đồng, và năm 2023 là 800 triệu đồng, thì tăng trưởng theo năm của công ty là bao nhiêu? Cách tính hàm mũ trong excel: Bước 1: Ở ô C32, nhập công thức bình phương trong excel sau: =POWER(800/500;1/3)-1. Nhập công thức vào ô C32 Bước 2: Nhấn Enter, sẽ thu được kết quả là 0,169607095 hay 16,96070953%. Kết quả sau khi nhập công thức tính Để tính giá trị hiện tại của một khoản tiền trong tương lai Bạn có thể dùng hàm POWER với số_cơ_sở là (1 / 1 + lãi suất) và số_mũ là số năm. Ví dụ: Cho bảng dữ liệu 3: Cho bảng dữ liệu Yêu cầu: Tính giá trị hiện tại của 100 triệu đồng mà bạn sẽ nhận được sau 10 năm với lãi suất 5% mỗi năm. Cách tính lũy thừa trong excel: Bước 1: Ở ô D42, bạn nhập công thức sau: =POWER(1/1,05;10)*100. Nhập công thức hàm POWER vào D42 Bước 2: Nhấn Enter, sẽ thu được kết quả là 61,39132535 triệu đồng. Kết quả sau khi nhập công thức Hướng dẫn cách sử dụng hàm power trong excel Ở phần này, chúng tôi sẽ hướng dẫn bạn cách sử dụng hàm power trong Excel. Thông thường sẽ có hai cách dùng hàm này là: Sử dụng trực tiếp hàm power trong excel Để sử dụng trực tiếp hàm POWER trong excel, bạn có thể làm theo các bước sau: Bước 1: Chọn một ô trống trong bảng tính, ví dụ ô A1. Bước 2: Nhập công thức =POWER(số_cơ_sở, số_mũ) vào ô A1, ví dụ =POWER(2,3). Bước 3: Nhấn phím Enter để hoàn tất công thức. Kết quả sẽ hiển thị trong ô A1, ví dụ 8. Ví dụ: Cho bảng dữ liệu 4: Cho bảng dữ liệu Yêu cầu: Dùng hàm POWER để tính lũy thừa bậc 2 và 3 của từng số trong bảng 4. Cách bình phương trong excel: Bước 1: Nhập công thức tính mũ trong excel =POWER(A55;2) vào ô B55. Nhập công thức lũy thừa bậc 2 Bước 2: Nhấn Enter sẽ thu được kết quả là 4. Kéo thả chuột xuống các ô bên dưới sẽ thu được kết quả như sau: Nhấn Enter Bước 3: Để tính lũy thừa bậc 3, bạn nhập công thức =POWER(A55;3) vào ô C55. Nhập công thức lũy thừa bậc 3 Bước 4: Nhấn Enter sẽ thu được kết quả là 8. Kéo thả chuột xuống các ô bên dưới sẽ thu được kết quả như sau: Nhấn Enter sẽ thu được kết quả Sử dụng dấu ‘^’ Một cách khác để sử dụng hàm POWER trong excel là dùng dấu ‘^’ (dấu mũ). Cách này ngắn gọn và tiện lợi hơn cách trên. Bạn có thể làm theo các bước sau: Bước 1: Chọn một ô trống trong bảng tính, ví dụ ô A1. Bước 2: Nhập công thức số_cơ_sở^số_mũ vào ô A1, ví dụ 2^3. Bước 3: Nhấn phím Enter để hoàn tất công thức. Kết quả sẽ hiển thị trong ô A1, ví dụ 8. Ví dụ: Cho bảng ví dụ 5: Cho bảng dữ liệu Yêu cầu: Yêu cầu: Dùng dấu ‘^’ để tính lũy thừa bậc 2 và 3 của từng số trong bảng 5. Cách làm: Bước 1: Nhập công thức hàm tính bình phương trong excel =A67^2 vào ô B67. Nhập công thức vào ô B67 Bước 2: Nhấn Enter sẽ thu được kết quả là 4. Kéo thả chuột xuống các ô bên dưới sẽ thu được kết quả như sau: Nhấn Enter sẽ thu được kết quả Bước 3: Nhập công thức tính lũy thừa bậc 3 là =A67^3 vào ô C67. Nhập công thức tính lũy thừa bậc 3 Bước 4: Nhấn Enter sẽ thu được kết quả là 8. Kéo thả chuột xuống các ô bên dưới sẽ thu được kết quả như sau: Nhấn Enter sẽ thu được kết quả Tổng kết Trong bài viết này, tôi đã giới thiệu cho bạn về hàm POWER trong excel, cách sử dụng nó để tính bình phương , lũy thừa, và các ứng dụng của nó trong thực tế. Tôi cũng đã hướng dẫn bạn cách sử dụng hàm POWER bằng hai cách: sử dụng trực tiếp hàm POWER hoặc sử dụng dấu ‘^’. Cuối cùng, tôi đã đưa ra một số ví dụ về cách bình phương trong excel bằng hàm POWER. Hy vọng bài viết này sẽ có ích cho bạn trong việc học Excel cũng như sử dụng excel hiệu quả hơn. Cảm ơn bạn đã đọc bài viết của tôi. >>> Xem thêm: Cách sử dụng hàm LOGARIT trong excel có ví dụ minh họa >>> Xem thêm: Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ kèm theo Video hướng dẫn cách sử dụng hàm power trong excel Hướng dẫn sử dụng hàm POWER trong Excel

7750 Lượt xem

Cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu có ví dụ

Cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu có ví dụ Hàm OFFSET được xem là hàm có thể kết hợp với hầu hết các hàm khác trong bảng tính, chỉ cần các hàm kết hợp có sự xuất hiện của ô hoặc dải ô tham chiếu là được. Để biết hàm OFFSET là gì? Công chức chuẩn như thế nào và cách sử dụng hàm OFFSET trong excel ra sao? Bạn hãy tham khảo ngay nội dung bài viết bên dưới nhé. Hàm OFFSET trong Excel là gì? Hàm OFFSET trong excel trả về một ô hoặc phạm vi ô là số lượng hàng và cột được chỉ định từ một ô hoặc một phạm vi ô. Trong Excel, hàm OFFSET được sử dụng với mục đích: Tiết kiệm thời gian trong việc tính toán dữ liệu của một ô. Hàm này kết hợp với hàm khác giúp bạn nâng cao hiệu suất làm việc. Hàm OFFSET trong excel giúp tiết kiệm thời gian khi tính toán >>> Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm Dcounta trong excel Ứng dụng của hàm OFFSET trong excel Lệnh OFFSET trong excel thường được sử dụng trong một số trường hợp sau đây: Được dùng để tham chiếu dữ liệu của một ô hoặc một dải ô trong bảng tính. Được dùng để tính toán dữ liệu của một ô hoặc một dải ô trong bảng tính căn cứ theo vùng tham chiếu sẵn có trước đó. Được dùng để kết hợp được với các hàm khác trong excel để hỗ trợ giải quyết công việc. >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Công thức OFFSET trong excel Công thức Offset trong Excel =OFFSET (tham chiếu, hàng, cols, chiều cao, chiều rộng ) Trong đó: Tham chiếu: là khu vực mà bạn muốn tham chiếu đến phần nào đó Hàng: có thể là tích cực hoặc tiêu cực. Cols: Số lượng cột, ở bên trái hoặc bên phải, mà bạn muốn ô trên bên trái của tham chiếu được trả về để tham chiếu. Ví dụ, sử dụng cols bằng 2 chỉ định rằng ô trên bên trái của tham chiếu được trả về sẽ là hai cột ở bên phải của ô tham chiếu trên cùng bên trái. Cols có thể là tích cực hoặc tiêu cực. Chiều cao: tùy chọn, theo số lượng hàng, mà bạn muốn tham chiếu được trả về. Chiều cao phải là một số dương. Nếu chiều cao bị bỏ qua, tham chiếu được trả về sẽ có cùng chiều cao với tham chiếu. Chiều rộng: tùy chọn, theo số lượng cột, mà bạn muốn tham chiếu được trả về. Chiều rộng phải là một số dương. Nếu chiều rộng bị bỏ qua, tham chiếu được trả về sẽ có cùng chiều rộng với tham chiếu. Công thức hàm OFFSET trong Excel Cách sử dụng hàm OFFSET trong Excel Để giúp bạn hiểu rõ hơn về công dụng hàm OFFSET trong excel, chúng tôi sẽ hướng dẫn chi tiết cách dùng hàm OFFSET excel kèm ví dụ minh họa cụ thể ngay bên dưới đây. Tạo các dải động bằng hàm OFFSET Cách tạo dải động bằng hàm OFFSET sẽ giúp ích rất nhiều cho các bảng tính cần thêm các cột dữ liệu. Ví dụ như: Bảng tính cần thêm các cột thưởng hàng tháng. Lúc này chúng ta sẽ ứng dụng công thức hàm OFFSET như sau: = OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1) Cụ thể từng bước thực hiện sẽ tiến hành theo trình tự sau: Bước 1: Đặt tên cho dải bằng cách vào thanh công cụ của bảng tính, di chuyển đến tab Formulas, chọn mục Date $ Time. Sau khi bảng tùy chọn hiển thị bạn hãy chọn MONTH. Khi bảng tùy chỉnh khác hiển thị bạn nhập số thứ tự của tháng vào rồi nhấn Enter. Ví dụ tạo các dải động bằng hàm OFFSET Bước 2: Tiếp theo bạn hãy chọn mục Define Name cũng trong tab Formulas. Khi giao diện hiển thị, bạn hãy nhập công thức = OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1) vào ô Refers to. Sau đó chọn OK. Thao tác nhập hàm OFFSET Bước 3: Sau khi bạn đã tạo một dải động bằng công thức OFFSET thì bạn có thể dùng Excel Data Validation để tạo danh sách theo mục. Cụ thể như sau: bạn hãy vào tab Data. Trên thanh công cụ bạn hãy chọn Data Validation, khi bảng tùy chọn hiển thị bạn hãy chọn Data Validation… theo hình minh họa bên dưới. Thao tác tạo danh sách theo mục Lúc này danh sách sẽ luôn được cập nhật tự động cập nhật ngay khi bạn thêm hoặc xóa các mục từ bảng tính gốc. Bước 4: Khi hộp thoại Data Validation hiển thị. Bạn hãy vào tab Setting. Ở mục Allow bạn hãy chọn List. Sau đó. tại mục Source bạn hãy điền dải ô cần tham chiếu vào. Trong ví dụ minh họa chúng ta có dải ô tham chiếu là A2:A8. Lưu ý dải ô tham chiếu này phải đặt trong tham chiếu tuyệt đối ($). Điền dải ô tham chiếu Bước 5: Nhấn Ok. >>> Xem thêm: Cách sử dụng hàm choose trong excel chi tiết kèm ví dụ minh họa Lấy dải từ ô gốc bằng hàm OFFSET trong Excel Ví dụ: Bạn sử dụng hàm OFFSET trong excel để lấy dải từ ô gốc trong bảng tính. Trong ví dụ này, vị trí ô sẽ nằm ở cột 2 dòng 5. Bước 1: Đầu tiên bạn nhập công thức hàm theo cú pháp: =OFFSET(B5,3,2) Nhập công thức hàm Bước 2: Sau khi nhập công thức kết quả hiển thị như sau: Kết quả hiển thị Hàm OFFSET thường kết hợp với hàm nào? Sau khi đã hiểu hàm OFFSET trong excel là gì, phần tiếp theo của bài viết chúng tôi sẽ hướng dẫn bạn cách dùng hàm OFFSET kết hợp với những hàm khác trong excel, bạn hãy khám phá ngay nhé. Hàm OFFSET kết hợp với hàm SUM Ví dụ: Tính tổng lương và thưởng của nhân viên Nguyễn Văn B bắt đầu từ ô B6 (lương của nhân viên đầu tiên). Áp dụng hàm: =SUM(OFFSET(B6,3,0,1,2) các bạn sẽ được kết quả như sau: Hàm OFFSET kết hợp với hàm SUM Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN Bước 1: Cho ví dụ như mô tả dưới ảnh, để tính giá trị trung bình, bạn nhập công thức sau: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)). Hàm OFFSET kết hợp với hàm AVERAGE Bước 2: Nhấn Enter để hiển thị kết quả. Hình minh họa kết quả nhận được Bước 3: Để tính số lớn nhất trong dải, bạn nhập công thức sau: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)). Hàm OFFSET kết hợp hàm MAX Bước 4: Nhấn Enter để nhận kết quả. Nhấn Enter hiện kết quả Bước 5: Để tìm số nhỏ nhất trong dải, bạn nhập công thức hàm min trong excel: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)). Hàm OFFSET kết hợp hàm MIN Bước 6: Nhấn Enter để nhận kết quả. Kết quả hàm MIN và OFFSET kết hợp Hàm OFFSET kết hợp với hàm VLOOKUP Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức =VLOOKUP(B1,A5:B11,2,FALSE). Hàm OFFSET kết hợp với hàm VLOOKUP Bước 2: Nhấn Enter để nhận kết quả. Kết quả nhận được Ngoài ra, hàm OFFSET có thể kết hợp với nhiều hàm khác để tính toán và xử lý dữ liệu trong excel. Hàm sẽ giúp ích các bạn rất nhiều nếu các bạn biết áp dụng và kết hợp hàm một cách hiệu quả. Những điều lưu ý khi sử dụng hàm OFFSET Cách sử dụng hàm OFFSET trong excel sẽ chuẩn hơn khi bạn ghi nhớ những điểm sau đây: Hàm OFFSET trong Excel chỉ trả về một tham chiếu trong phạm vi dữ liệu sẵn có chứ không di chuyển đến bất kỳ ô hoặc dải nào trong bảng dữ liệu. Kết quả trả về của hàm OFFSET là một dải ô thì các đối số rows và các đối số cols được đề cập đến luôn luôn là phía trên bên trái. Đối số reference trong công thức hàm OFFSET phải là một ô hoặc một dải ô liền kề. Nếu bạn nhập sai đối số Reference thì kết quả trả về sẽ là lỗi #VALUE!. Nếu đối số rows và đối số cols xác định một ô tham chiếu vượt khỏi phạm vi của trang tính Excel, thì kết quả tham chiếu sẽ báo lỗi #REF!. Lưu ý khi sử dụng hàm OFFSET Nếu bạn bỏ qua các đối số height và đối số width trong công thức thì tham chiếu trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu gốc. Nếu đối số height và đối số width lớn hơn 1, nghĩa là hàm phải trả về kết quả nhiều hơn 1 ô. Lúc này, bạn phải sử dụng công thức mảng hàm OFFSET, nếu không kết quả sẽ báo lỗi #VALUE!. Đối số height và đối số width phải là số dương. Hàm OFFSET có thể được kết hợp với các trong Excel nếu hàm chấp nhận tham chiếu ô hoặc dải xuất hiện trong các đối số của nó. Kết luận Cách sử dụng hàm OFFSET trong excel sẽ đơn giản và dễ thực hiện hơn nhiều khi bạn thực hành ứng dụng hàm OFFSET nhiều lần cho đến khi thành thạo. Vì vậy, hãy chăm chỉ rèn luyện và học tập nhé. Nếu bạn muốn nâng cao thêm kỹ năng tin học văn phòng, hãy tham gia khoá học Excel, Word, Powpoint trên Unica nhé. Mời bạn đọc cùng khám phá.

9002 Lượt xem

Cách lọc dữ liệu trùng nhau từ 2 sheet trong Excel chi tiết nhất

Cách lọc dữ liệu trùng nhau từ 2 sheet trong Excel chi tiết nhất Excel là công cụ rất phổ biến hiện nay, nó thường được các doanh nghiệp ứng dụng để quản lý kho, nhân viên, thống kê doanh thu hay sắp xếp nội dung công việc. Trong quá trình sử dụng excel, chắc chắn sẽ có lúc bạn gặp phải tình trạng dữ liệu bị trùng lặp với nhau gây khó khăn trong việc thống kê. Đừng lo lắng! Trong bài viết sau, Unica sẽ hướng dẫn bạn cách lọc dữ liệu trùng nhau từ 2 sheet giúp quá trình làm việc với excel được đơn giản và nhanh chóng hơn, hãy tham khảo ngay nhé. Lý do cần lọc dữ liệu trùng nhau trên 2 sheet Excel Có rất nhiều lý do để người dùng phải lọc trùng 2 sheet khi sử dụng excel. Một số những lý do tiêu biểu lý giải cho vấn đề tại sao phải lọc dữ liệu trùng trong 2 sheet excel có thể kể đến như: Phát hiện dữ liệu bị thiếu hoặc thừa Dữ liệu trùng nhau sẽ làm cho bảng tính lộn xộn, rắc rối, biết cách lọc dữ liệu từ 2 sheet khác nhau sẽ giúp bạn dễ dàng phát hiện dữ liệu bị thiếu hoặc thừa, dễ dàng loại bỏ các dữ liệu không cần thiết. Từ đó, bảng tính trở nên gọn gàng, quá trình làm việc với bảng tính trở nên đơn giản và dễ sử dụng hơn. Lọc dữ liệu trùng nhau gúp bạn dễ dàng phát hiện dữ liệu bị thừa hoặc thiếu Kiểm tra tính nhất quán Những dữ liệu trùng nhau trên excel sẽ gây khó khăn cho việc tìm kiếm và phân tích dữ liệu. Thao tác lọc dữ liệu trùng lặp giúp đảm bảo tính nhất quán của dữ liệu, giúp các kết quả của các công thức và hàm được chính xác hơn. Điều này đồng thời cũng giúp bạn tiết kiệm thời gian, công sức khi tìm kiếm dữ liệu trong cả một trang tính dài. Tối ưu hóa hoặc cập nhật dữ liệu Ngoài những lý do trên, việc lọc dữ liệu trùng nhau trên 2 sheet trong excel cũng giúp tối ưu hoá quá trìn nhập hoặc cập nhật dữ liệu. Trong một số trường hợp, dữ liệu trùng nhập có thể có định dạng khác nhau. Việc lọc dữ liệu giống nhau giúp chuẩn hoá dữ liệu, các dữ liệu trong bảng tính được thống nhất, rất dễ và thuận tiện trong quá trình sử dụng. Giảm thiểu lỗi nhập liệu Trong quá trình nhập dữ liệu, không tránh khỏi lúc gặp sai sót. Biết cách tìm dữ liệu trùng trong 2 file excel giúp giảm thiểu lỗi nhập liệu, hạn chế các dữ liệu sai sót. Từ đó bảng tính được chính xác hơn, tránh lỗi trong các báo cáo và phân tích dữ liệu. Biết cách lọc dữ liệu bạn sẽ giảm thiểu lỗi được lõi khi nhập liệu Giảm dung lượng tệp Dữ liệu trùng lặp có thể chiếm nhiều dung lượng trong tệp excel, đặc biệt nếu dữ liệu đó được lưu trữ dưới dạng hình ảnh hoặc các định dạng tệp khác. Việc lọc dữ liệu trùng lặp sẽ loại bỏ các dữ liệu này, giúp giảm dung lượng tệp excel. Cách so sánh dữ liệu giữa 2 sheet trong excel Để biết xem dữ liệu giữa 2 sheet có bị trùng nhau không thì bạn cần phải biết cách so sánh dữ liệu. Thao tác so sánh dữ liệu giữa hai tập tin excel rất quan trọng và cần thiết, đặc biệt là với những người làm trong lĩnh vực bán hàng. Sau đây là hướng dẫn cụ thể cách so sánh dữ liệu trùng trong 2 file excel cho bạn tham khảo: Dùng tính năng View Side by Side Nếu bạn muốn so sánh hai tập tin nằm riêng biệt trong excel hoặc 2 sheet trong cùng một cửa sổ làm việc thì bạn hãy dùng tính năng View Side by Side. Tuy nhiên, với tính năng này người dùng chỉ cần sử dụng để so sánh đối với những tệp tin có ít dữ liệu và có thể so sánh thủ công một cách dễ dàng. So sánh hai tập tin riêng bằng tính năng View Side by Side như sau: Bước 1: Tại cửa sổ làm việc chính của excel bạn chọn thẻ View => Chọn View Side by Side. Chọn View Side by Side Bước 2: Mặc định trên cửa số excel 2 file so sánh sẽ hiển thị theo chiều nằm ngang. Nếu bạn thấy chiều ngang khó so sánh thì bạn có thể chuyển sang so sánh dọc bằng cách chọn tuỳ chọn Arrange All (tuỳ chọn này cũng nằm trên thẻ View), sau đó bạn chọn tiếp Vertical => Chọn OK là file sẽ chuyển sang nằm dọc. Thao tác chuyển sang so sánh dọc Bước 3: Nếu bạn muốn cuộn 2 bảng tính cùng một lúc để dễ đọc hơn thì bạn thực hiện như sau: Từ thẻ View chọn Synchronous Scrolling. Tính năng cuộn bảng tính trong excel >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] So sánh hai sheet trong cùng một bảng tính Bạn cũng có thể sử dụng tính năng View Side by Side để so sánh hai sheet trong cùng một bảng tính. Tuy nhiên trước khi chọn tuỳ chọn View Side by Side thì bạn cần thực hiện các thao tác sau: Bước 1: Tại cửa sổ làm việc Excel bạn chọn vào thẻ tab View => Chọn New Window. Chọn New Window Bước 2: Tiếp theo bạn chọn sheet 1 trong cửa sổ đầu tiên, sau đó chọn sheet 2 trong cửa số thứ 2. Chọn sheet 1 và sheet 2 Bước 3: Sau đó chọn tuỳ chọn View => Chọn View Side by Side rồi thực hiện tương tự các bước 2 và 3 ở trên. Bật tùy chọn View Side by Side Sử dụng hàm IF Đối với những trường hợp trang tính có nhiều dữ liệu, việc so sánh bằng tuỳ chọn View Side by Side sẽ tốn rất nhiều thời gian. Khi này, bạn có thể sử dụng hàm IF để so sánh. Cách so sánh kiểm tra dữ liệu trùng trong 2 file excel này như sau: Bước 1: Tại giao diện làm việc chính của excel bạn tạo trang tính mới bằng cách bấm vào dấu + ở phía dưới. Tạo trang tính mới Bước 2: Tại trang tính mới vừa tạo, ô A1 bạn nhập vào đó hàm =IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "") => Nhấn Enter. Nhập hàm vào ô A1 Bước 3: Tiếp tục bạn kéo chuột đến vùng cần so sánh, khi này excel sẽ hiện lên những điểm khác biệt giữa 2 sheet. Kéo chuột đến vùng cần so sánh Dùng Compare and Merge Workbooks Nếu bạn muốn so sánh và hợp nhất thành một file hoàn chỉnh các phiên bản khác nhau của một excel bạn hãy dùng Compare and Merge Workbooks. Khi dùng tuỳ chọn này những chỗ khác nhau sẽ được nổi bật lên. Cách so sánh bằng tuỳ chọn này như sau: Bước 1: Đầu tiên bạn vào File => Chọn Options. Chọn Options Bước 2: Tiếp theo bạn chọn Quick Access Toolbar => Chọn All Commands => Chọn Compare and Merge Workbooks => Chọn Add => Nhấn OK để xác nhận. Thao tác để Compare and Merge Workbooks Bước 3: Tiếp tục tại trang excel bạn chọn Compare and Merge Workbooks => Ở cửa sổ hiện ra mới bạn chọn những file cần so sánh và hợp nhất. Chọn file cần so sánh File sau khi được hợp nhất những điểm khác nhau sẽ được nổi bật lên giúp bạn dễ dàng nhận biết Điểm khác nhau nổi bật lên Cách lọc dữ liệu trùng nhau từ 2 sheet Để biết cách lọc dữ liệu từ nhiều sheet trong excel như thế nào? Trong nội dung phần tiếp theo của bài viết Unica sẽ hướng dẫn chi tiết ngay cho bạn tham khảo: Dùng hàm vlookup lọc trùng dữ liệu trùng Excel Cách dùng hàm vlookup để lọc dữ liệu trùng trước hết đó là việc tìm kiếm các dữ liệu trùng lặp để thực hiện thao tác cùng hàm Vlookup đó chính là cách lọc dữ liệu trùng trong 2 file excel. Theo dõi ví dụ cụ thể để hiểu rõ hơn về cách làm: Bước 1: Đưa ra 2 bảng của 2 sheet, sheet 1 và sheet 2. Sheet 1: Tạo sheet 1 Sheet 2: Tạo sheet 2 Với sheet 1 chúng ta có list các danh sách tên để lọc các value trùng Sheet 2 là list các giá trị value gốc hay sẵn có. Bước 2: Tại sheet 1 --> nhấp chuột vào Formulas --> chọn Insert Function Mở ra hộp thoại mới ta làm thao tác như hình và OK Thực hiện thao tác chọn VLOOKUP Bước 3: Sau khi Ok hộp thoại mới mở ra, thao tác tiếp theo là nhập giá trị vào từng vùng dữ liệu của Vlookup để tìm dữ liệu trùng nhau giữa 2 sheet trong Excel. Tại vùng dữ liệu thứ nhất Lookup_value nhấp vào sheet 1 > click vào tên đầu tiên trong bảng và tự động giá trị nhập vào vùng dữ liệu này sẽ được hiện trên cửa sổ. Vùng dữ liệu tiếp theo Table_array chọn vào sheet 2 > chọn tất cả vùng giá trị cần kiểm tra và tự động dữ liệu xuất hiện trên cửa sổ đang làm việc. Ở dòng nhập dữ liệu thứ 3 Col_index_num vùng này yêu cầu nhập số vị trí cột mà muốn kiểm tra dữ liệu nếu là trùng, ở đây sẽ nhập là 1 để kiểm tra trong bảng dữ liệu ở sheet 1. Nhập giá trị vào từng vùng dữ liệu Phần cuối cùng thường hay để số 0 => OK. => Đưa ra kết quả tại bảng sheet 1 tên người trùng Bước 4: Nhấp kéo giữ chuột từ ô đầu tiên xuống hết các giá trị dưới, được kết quả. Dữ liệu có kết quả là #N/A là những giá trị không trùng với dữ liệu khác. Với cách tìm dữ liệu trùng trong 2 file Excel này rất đơn giản phải không bạn? bạn hãy áp dụng vào để lọc dữ liệu cho bảng tính của mình nhé. Kết hợp hàm Vlook, hàm If, hàm ISNA bỏ lỗi #N/A Trong trường hợp cột kiểm tra dữ liệu có những ô không bị trùng lặp sẽ hiểu thị lỗi #N/A, nếu như vậy bạn có thể kết hợp với hàm ISNA để kiểm tra lỗi #N/A Nếu dữ liệu là #N/A thì hàm ISNA sẽ trả về kết quả là TRUE và ngước lại nếu không phải thì hàm sẽ trả về giá trị FALSE. Lúc này hàm IF sẽ kiểm tra điều kiện, nếu đúng thì sẽ trả về giá trị a mà bạn chỉ định, nếu sai thì trả về giá trị b mà người dùng chỉ định. Bước 1: Tại Sheet 1 bạn nhập thêm 1 cột nữa để so sánh với cột Kiểm tra. Tại ô đầu tiên bạn sẽ nhập công thức dưới đây. =IF(ISNA(VLOOKUP(Sheet1!C3,Sheet2!$C$2:$E$11,1,0)),"Không","Trùng") Hàm ISNA sẽ kiểm tra giá trị của hàm Vlookup. Nếu hàm Vlookup trả về lỗi #N/A thì hàm ISNA sẽ trả về giá trị TRUE. Trong đó #N/A là dữ liệu không trùng lặp nên hàm If sẽ trả về kết quả Không. Nếu hàm VLOOKUP trả về giá trị cụ thể thì hàm ISNA sẽ trả về giá trị FALSE. Hàm IF sẽ trả về điều kiện sai là “Trùng”. Nhập công thức vào bảng Bước 2: Kết quả ở ô đầu tiên sẽ thông báo Trùng. Tiếp tục kéo xuống những ô bên dưới sẽ được các thông báo khác nhau, tương ứng với từng giá trị. Kéo xuống các ô bên dưới Bước 3: Như vậy bạn đã có kết quả thông báo Trùng và Không cho bảng kiểm tra. Cuối cùng chúng ta có thể xóa cột Kiểm tra để làm gọn bảng dữ liệu và giao diện bảng khoa học hơn khi không có báo lỗi #N/A. Chọn Entire column Kết quả bảng dữ liệu Sheet 1 hoàn chỉnh như hình. Kết hợp hoàn chỉnh nhận được Cách lọc dữ liệu từ nhiều sheet trong excel thông qua công cụ Macro Công cụ Macro hay hàm VBA thường được ưu tiên lựa chọn để thực hiện thao tác lọc dữ liệu từ nhiều sheet trong excel. Đối với thao tác lọc dữ liệu trong Excel này bạn thực hiện như sau: Bước 1: Trên thanh công cụ chọn View => Chọn Record Macro. Chọn Record Macro Bước 2: Tiếp theo bạn click chọn vào biểu tượng tam giác ngược đầu cột để thực hiện thao tác lọc dữ liệu với một nội dung bất kỳ. Bước 3: Sau khi lọc bạn tiếp tục quay lại mục Macro => Chọn Stop recording để dừng ghi hình. Bước 4: Sử dụng tổ hợp phím Alt + F11 hoặc vào mục Developer => Chọn Visual Basic để kiểm tra xem có đoạn cốt nào chưa được lọc không. Kết luận Như vậy chỉ với vài thao tác rất đơn giản bạn đã có thể dễ dàng và thực hiện nhanh gọn việc check và lọc dữ liệu trùng nhau từ 2 sheet trong excel rất chính xác rồi phải không ạ? Mong rằng những thông tin chúng tôi mang tới bạn nhiều hữu ích. Ngoài kiến thức về lọc dữ liệu trùng nhau trong Excel các bạn có thể tham khảo các khóa học tin học văn phòng từ những giảng viên hàng đầu tại Unica. Chúc các bạn thành công!

21906 Lượt xem

Cách lọc dữ liệu trùng nhau trong 2 cột excel chính xác nhất

Cách lọc dữ liệu trùng nhau trong 2 cột excel chính xác nhất Bạn có thể dùng hàm để tìm giá trị giống nhau giữa 2 cột trong excel nhanh chóng và chính xác. Việc này giúp bạn tối ưu hóa thời gian làm việc hơn rất nhiều với việc tìm thủ công. Trong bài viết này, Unica sẽ giới thiệu cho bạn 5 cách lọc dữ liệu trùng nhau trong 2 cột excel. Cùng tìm hiểu ngay. Tìm giá trị trùng lặp trong excel để làm gì? Trước khi đi vào chi tiết các dùng hàm kiểm tra 2 cột giống nhau trong excel, bạn có thể tự hỏi tại sao lại cần làm việc này? Có những lý do nào khiến bạn muốn tìm ra những giá trị giống nhau ở 2 cột khác nhau trong Excel? Một số lý do phổ biến có thể kể đến là: Để kiểm tra tính đồng nhất và chính xác của dữ liệu nhập vào Excel từ các nguồn khác nhau, ví dụ như từ các file, website, email hoặc nhập tay. Loại bỏ hoặc đánh dấu những dòng dữ liệu bị trùng lặp hoặc không liên quan để tối ưu hóa dung lượng và hiệu suất của bảng tính. So sánh và phân tích sự khác biệt hoặc tương đồng giữa hai danh sách, ví dụ như danh sách khách hàng, sản phẩm, đơn hàng hoặc bất kỳ loại dữ liệu nào khác. Kết hợp hoặc ghép nối hai danh sách lại với nhau theo một tiêu chí nào đó, ví dụ như mã sản phẩm, tên khách hàng hoặc bất kỳ trường thông tin nào khác. Lợi ích của việc lọc giá trị trùng nhau trong Excel Như vậy, việc lọc số trùng nhau giữa 2 cột trong excel có thể giúp bạn giải quyết nhiều vấn đề liên quan đến dữ liệu một cách hiệu quả và chính xác. Tùy vào mục đích và yêu cầu của bạn, bạn có thể lựa chọn cách lọc dữ liệu trùng nhau trong 2 cột excel phù hợp nhất. Ngoài cách lọc dữ liệu trùng nhau trong 2 cột, bạn có thể lọc dữ dữ liệu trùng nhau từ 2 sheet trong Excel bằng các hàm tính như IF hoặc vlookup. >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Làm nổi các giá trị giống nhau ở 2 cột Một cách đơn giản và nhanh chóng để tìm ra những giá trị giống nhau ở 2 cột trong Excel là làm nổi các giá trị đó bằng cách sử dụng màu sắc hoặc kiểu chữ khác biệt. Bằng cách này, bạn có thể dễ dàng nhận biết được những dòng dữ liệu có sự trùng lặp hoặc không đồng nhất giữa hai cột. Để làm nổi các giá trị giống nhau ở 2 cột trong Excel, bạn có thể sử dụng một trong hai cách sau: Dùng công cụ Find and Replace (Ctrl + H) Công cụ Find and Replace để tìm kiếm và thay thế các giá trị trùng nhau bằng màu sắc hoặc kiểu chữ khác biệt. Bạn chỉ cần nhập giá trị cần tìm vào ô Find what, chọn Options >>, chọn Format và thiết lập màu sắc hoặc kiểu chữ mong muốn, sau đó nhấn Replace All. Lưu ý rằng bạn phải chọn phạm vi tìm kiếm là hai cột muốn so sánh trước khi thực hiện công cụ này. Tìm các giá trị giống nhau bằng Find and Replace So sánh 2 cột trong excel bằng conditional formatting Conditional Formatting để áp dụng một điều kiện cho hai cột muốn so sánh và định dạng màu sắc hoặc kiểu chữ cho các giá trị thỏa mãn điều kiện đó. Bạn có thể chọn một trong các tùy chọn có sẵn trong danh sách Highlight Cells Rules hoặc Top/Bottom Rules, hoặc tạo một điều kiện mới bằng cách chọn New Rule >>. Lưu ý rằng bạn phải chọn phạm vi áp dụng là hai cột muốn so sánh trước khi thực hiện công cụ này. So sánh 2 cột bằng Conditional Formatting >>> Xem thêm: Trọn bộ khóa học tin học văn phòng giúp gia tăng 100% hiệu suất công việc Cách lọc dữ liệu trùng nhau trong 2 cột excel Ngoài việc làm nổi các giá trị giống nhau ở 2 cột trong Excel, bạn cũng có thể lọc ra chỉ những dòng dữ liệu có sự trùng lặp hoặc không đồng nhất giữa hai cột. Bằng cách này, bạn có thể loại bỏ hoặc xử lý các dòng dữ liệu không mong muốn một cách dễ dàng và tiện lợi. Để lọc dữ liệu trùng nhau trong 2 cột excel, bạn có thể sử dụng một trong 4 cách sau: VLOOKUP - Hàm kiểm tra dữ liệu giống nhau trong excel Hàm tìm giá trị trùng nhau trong excel VLOOKUP cho phép bạn tra cứu và lấy ra các giá trị từ một bảng dữ liệu theo một tiêu chí nào đó. Bạn có thể lọc dữ liệu trùng nhau từ 2 cột trong excel bằng hàm VLOOKUP, bạn cần làm như sau: Tạo một cột mới bên cạnh hai cột muốn so sánh, ví dụ cột D. Nhập công thức =VLOOKUP(A2,B:B,1,FALSE) vào ô D2, trong đó A2 là giá trị cần tra cứu, B:B là phạm vi chứa giá trị tra cứu, 1 là số thứ tự của cột trả về giá trị (trong trường hợp này là cùng cột B), và FALSE là yêu cầu giá trị tra cứu phải khớp chính xác với giá trị cần tra cứu. Nhấn Enter để thực hiện công thức. Nếu giá trị A2 có tồn tại trong cột B, công thức sẽ trả về giá trị đó. Nếu không, công thức sẽ trả về lỗi #N/A. Kéo công thức xuống các ô dưới để áp dụng cho toàn bộ cột A. Sau khi có kết quả, bạn có thể sử dụng công cụ Filter (Trang chủ >> Sort & Filter >> Filter) để lọc ra chỉ những dòng có giá trị #N/A hoặc không có giá trị #N/A tùy theo nhu cầu. Để hiểu hơn cách lọc dữ liệu bằng hàm VLOOKUP, mời bạn theo dõi ví dụ dưới đây: Ví dụ: Cho bảng dữ liệu sau, kiểm tra tên học sinh trùng lặp ở Sheet 1 và 2 bằng cách dùng VLOOKUP: Cho bảng dữ liệu Bước 1: Ở ô C3 nhập công thức VLOOKUP là: =VLOOKUP(Sheet1!B3;Sheet2!$C$4:$E$7;1;0) Nhập hàm VLOOKUP Bước 2: Nhấn Enter, kết quả trả về là Nguyễn Minh Nhật chứng tỏ 2 file dữ liệu đang trùng tên học sinh này. Kết quả sau khi dùng hàm Bước 3: Đặt con trỏ chuột vào dấu cộng phía bên dưới ô C3 và kéo chuột xuống bên dưới để copy công thức. Kết quả thu được như hình dưới đây: Bảng kết quả cuối cùng Note: Ô dữ liệu nào báo lỗi N/A tức là dữ liệu đó không xuất hiện ở file số 2. Cách lọc dữ liệu trùng nhau trong 2 cột excel bằng conditional formatting Một cách khác để lọc dữ liệu trùng nhau từ 2 cột trong excel là sử dụng công cụ Conditional Formatting kết hợp với hàm MATCH. Hàm MATCH cho phép bạn tìm kiếm một giá trị trong một danh sách và trả về vị trí của nó. Bạn có thể áp dụng một điều kiện để định dạng màu sắc hoặc kiểu chữ cho các giá trị có vị trí khớp nhau hoặc không khớp nhau giữa hai cột. Ví dụ: Cho bảng dữ liệu sau, tìm dữ liệu trùng trong 2 cột excel để kiểm tra tên người nhận đơn và tên đơn hàng trả về: Cách lọc dữ liệu trùng nhau Bước 1: Bôi đen vùng dữ liệu từ C4 đến D8. Bôi đen vùng dữ liệu cần lọc Bước 2: Trên thanh công cụ, nhấn vào lệnh Conditional Formatting, chọn Highlight Cells Rules, sau đó bạn chọn tiếp Duplicate Value. Chọn tiếp Duplicate Value Bước 3: Ở bảng Duplicate Value, bạn chọn lệnh Duplicate và màu sắc mong muốn ở ô bên cạnh. Nhấn OK Nhấn OK Bước 4: Toàn bộ dữ liệu trùng nhau sẽ được tô màu mà bạn đã chọn như ở hình dưới đây: Dữ liệu trùng nhau được tô màu Lọc trùng 2 cột trong excel với Advanced Filter Một phương pháp nâng cao hơn để thực hiện cách lọc dữ liệu trùng nhau trong 2 cột excel là sử dụng công cụ Advanced Filter. Công cụ này cho phép bạn lọc ra các dòng dữ liệu theo nhiều tiêu chí khác nhau, bao gồm cả tiêu chí trùng nhau hoặc không trùng nhau giữa hai cột. Bạn có thể lọc dữ liệu trực tiếp trên bảng tính hiện tại hoặc sao chép ra một bảng tính khác. Ví dụ: Cho bảng dữ liệu sau: Cho bảng dữ liệu Yêu cầu: Lọc 2 cột cùng lúc trong excel bằng công cụ Advanced Filter. Bước 1: Trên thanh công cụ, bạn chọn thẻ Data, chọn lệnh Advanced. Chọn thẻ Data, chọn lệnh Advanced Bước 2: Trong bảng Advanced Filter, bạn tích chọn Copy to another location và điều chỉnh các thông số như trong hình dưới đây: Trong đó: List range: Vùng chứa dữ liệu. Criteria range: Vùng chứa điều kiện. Chỉnh thông số trong Advanced Filter Bước 3: Tích vào Unique records only rồi nhấn OK sẽ thu được bảng kết quả đã được lọc như sau: Kết quả được lọc Tìm giá trị trùng nhau ở 2 cột trong excel bằng hàm COUNTIF Một cách khác để lọc dữ liệu trùng nhau từ 2 cột trong excel là sử dụng hàm COUNTIF. Hàm COUNTIF cho phép bạn đếm số lần xuất hiện của một giá trị trong một phạm vi nào đó. Bạn có thể sử dụng hàm này để kiểm tra xem một giá trị có tồn tại trong một cột khác hay không, và đánh dấu các giá trị đó bằng màu sắc hoặc kiểu chữ. Ví dụ: Cho bảng dữ liệu sau: Cho bảng dữ liệu Yêu cầu: So sánh dữ liệu 2 cột trong excel để tìm tên học sinh có trong nhóm 1 nhưng không có trong nhóm 2 bằng hàm COUNTIF Bước 1: Tô đen toàn bộ từ D4 đến D8, di chuyển tới ô Name Box và đặt tên là Nhom1 rồi nhấn OK. Đặt tên là Nhom1 Bước 2: Tô đen toàn bộ từ F4 đến F8, di chuyển tới ô Name Box và đặt tên là Nhom2 rồi nhấn OK. Đặt tên là Nhom2 Bước 3: Tô đen toàn bộ Nhom1 rồi nhấn vào thẻ Home. Nhấn vào thẻ Home Bước 4: Chọn Conditional Formatting => Chọn New Rule. Chọn Conditional Formatting và chọn New Rule Bước 5: Ở hộp thoại New Formatting Rule, bạn chọn Use a formula to determine which cells to format. Chọn Use a formula to determine which cells to format Bước 6: Nhập công thức =COUNTIF(Nhom2;D4)=0 rồi chọn Format. Nhập công thức và chọn Format Trong đó: Công thức này có nghĩa là bạn sẽ đếm tên học sinh trên Nhom2 và so sánh với giá trị của Nhom1 bắt đầu từ ô D4. Phần =0 thì có nghĩa là nếu như trên Nhom2 không có tên học sinh giống ở Nhom1 thì Excel sẽ hiểu tên học sinh đó là 0 và chúng ta sẽ tiến hành định dạng cho giá trị 0 đó theo màu giống ví dụ. Bước 7: Hộp thoại Format Cells xuất hiện kích chọn thẻ Fill => Lựa chọn màu đánh dấu tên học sinh không có trong Nhom2 giống với ví dụ > Nhấn OK. Chọn màu rồi nhấn OK Bước 8: Nhấn OK lần nữa để áp dụng vào Nhom1. Dưới đây sẽ là bảng kết quả, những ô được tô màu sẽ là tên học sinh có ở nhóm 1 nhưng không có ở nhóm 2. Bảng kết quả Cách lọc dữ liệu trùng nhau trong 2 cột excel bằng mã VBA Việc sử dụng mã VBA (Visual Basic for Applicatons) để lọc dữ liệu trong excel giúp cho thao tác thực hiện trở nên nhanh chóng và dễ dàng hơn bao giờ hết. Thực hiện theo những bước sau. Bước 1: Trên thanh công cụ của Excel chọn tab Developer. Sau đó bạn bấm chọn Visual Basic hoặc nhấn tổ hợp phím tắt là Alt + F11. Nếu bạn không tìm thấy tab Developer ở trên thanh công cụ, bạn hãy truy cập vào File => Options => Customize Ribbon. Tại menu Customize the Ribbon xổ xuống và chọn Main Tabs, tiếp đến tích chọn hộp kiểm Developer ở bên dưới. Nhấn OK để đóng cửa sổ lại. Tìm giá trị trùng nhau ở 2 cột trong excel bằng mã VBA Bước 2: Chọn Insert rồi bấm chọn lệnh Module. Tìm giá trị trùng nhau ở 2 cột trong excel bằng mã VBA Bước 3: Tiếp đó bạn tiến hành sao chép và dán đoạn mã sau đây vào trong cửa sổ Module và quay lại trang tính của bạn. Sub Tim_Kiem_Trung_Lap() Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = “KutoolsforExcel” Set Range1 = Application.Selection Set Range1 = Application.InputBox(“Range1 :”, xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox(“Range2:”, xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng1 In Range1 xValue = Rng1.Value For Each Rng2 In Range2 If xValue = Rng2.Value Then If outRng Is Nothing Then Set outRng = Rng1 Else Set outRng = Application.Union(outRng, Rng1) End If End If Next Next outRng.Select Application.ScreenUpdating = True End Sub Bước 4: Trong nhóm công cụ Developer, bạn chọn Macros hoặc nhấn tổ hợp phím tắt Alt + F8. Bước 5: Cửa sổ Macro xuất hiện chọn Tim_Kiem_Trung_Lap trong danh sách bên khung dưới. Nhấn Run để chạy chương trình tìm giá trị trùng nhau ở 2 cột trong Excel. Tìm giá trị trùng nhau ở 2 cột trong excel bằng mã VBA Bước 6: Chọn cột đầu tiên bằng cách nhập địa chỉ dải ô vào trong khung nhập vừa xuất hiện hoặc dùng chuột để bôi đen những ô trong cột đầu tiên. Nhấn OK. Tìm giá trị trùng nhau ở 2 cột trong excel bằng mã VBA Bước 7: Chọn cột thứ 2 để tìm những giá trị trùng nhau theo cách tương tự ở trên. Nhấn OK để xem kết quả. Nhấn OK để xem kết quả Những giá trị trùng nhau ở hai cột sẽ có định dạng là viền đậm, qua đó bạn sẽ xác định được những giá trị không trùng nhau cũng như giá trị giống nhau ở cả hai cột. Những giá trị trùng nhau ở hai cột sẽ có định dạng là viền đậm Kết luận Trong bài viết này, tôi đã giới thiệu cho bạn 5 cách lọc dữ liệu trùng nhau trong 2 cột excel chính xác nhất, từ đơn giản đến phức tạp, từ thủ công đến tự động. Bạn có thể áp dụng cách nào phù hợp với nhu cầu và tình huống của mình. Hy vọng bài viết này sẽ có ích cho bạn trong việc xử lý và phân tích dữ liệu trong Excel. Nếu bạn có thắc mắc hay góp ý gì, xin vui lòng để lại bình luận bên dưới. Bạn đọc quan tâm muốn biết thêm nhiều chứng chỉ tin học văn phòng hãy nhanh tay đăng ký và tham khảo khoá học chinh phục chứng chỉ IC3 nhé.

33276 Lượt xem

5 cách so sánh dữ liệu 2 cột trong excel chuẩn và nhanh nhất

5 cách so sánh dữ liệu 2 cột trong excel chuẩn và nhanh nhất Excel là một công cụ thông minh có số lượng người dùng đông đảo, đặc biệt là dân văn phòng bởi những tính năng tuyệt vời, chuyên nghiệp mà nó mang lại. Để kiểm tra sự khác biệt thông tin giữa các cột trong excel bạn sẽ phải dùng hàm so sánh. Đây là hàm rất cần thiết và quan trọng, đặc biệt là khi bạn sử dụng excel để nhập liệu. Biết cách so sánh dữ liệu 2 cột trong excel bằng hàm bạn sẽ tối ưu hoá thời gian làm việc, có quá trình làm việc trên excel được nhanh chóng và thuận tiện hơn. Để biết cách đối chiếu dữ liệu 2 cột trong excel như thế nào? Các bạn hãy tham khảo nội dung chia sẻ trong bài viết sau nhé. Khi nào cần so sánh dữ liệu 2 cột? Người dùng thường sử dụng excel để so sánh dữ liệu trong một số trường hợp chính sau: Kiểm tra sự trùng khớp giữa hai cột dữ liệu. Ví dụ, bạn muốn so sánh danh sách khách hàng đã đăng ký và danh sách khách hàng đã mua hàng để kiểm tra xem có trường hợp khách hàng nào đã mua hàng nhưng chưa đăng ký hay không. Tìm kiếm các dữ liệu khác biệt giữa hai cột dữ liệu. Ví dụ: Sử dụng bảng excel so sánh danh sách sản phẩm hiện tại và danh sách sản phẩm cũ để tìm ra các sản phẩm đã bị ngừng sản xuất hoặc thay đổi giá. So sánh kết quả của hai phép tính hoặc công thức. Ví dụ: So sánh kết quả của phép tính tổng số tiền bán hàng theo tháng của hai phòng ban để kiểm tra xem có sự chênh lệch hay không. Khi cần so sánh số lượng dữ liệu giữa 2 cột. Vì dụ: Bạn muốn so sánh để biết xem cột nào có chứa số lượng dữ liệu nhiều hơn. So sánh dữ liệu 2 cột khi muốn kiểm tra sự trùng khớp giữa 2 cột Cách so sánh dữ liệu 2 cột trong excel Có rất nhiều cách so sánh giá trị 2 cột trong excel, bạn có thể sử dụng hàm ISERROR COUNTIF, VLOOKUP, IF hay MATCH đều được. Để biết cụ thể cách so sánh dữ liệu 2 cột trong excel bằng hàm như thế nào, sau đây Unica sẽ hướng dẫn chi tiết cho bạn: Sử dụng hàm COUNTIF để so sánh dữ liệu 2 cột Excel cung cấp tính năng so sánh dữ liệu 2 cột bằng nhiều cách khác nhau, nhưng chuẩn nhất và dễ dàng nhất cho người sử dụng bằng hàm COUNTIF. Đây là hàm thống kê được sử dụng nhiều nhất khi thực hiện thủ thuật so sánh giá trị 2 cột trong Excel. Hàm COUNTIF được dùng để đếm các ô thỏa mãn những điều kiện nào đó được đưa ra trong phạm vi điều kiện khoanh vùng. Khi thực hiện so sánh hai cột dữ liệu sẽ dùng hàm so sánh 2 cột trong Excel. Đây cũng là một trong số các công thức hàm cơ bản của hệ thống hàm trên trang tính Excel, với chức năng đếm ô theo yêu cầu trong phạm vi nhất định đã được định ra sẵn, ngoài ra được dùng với chức năng chủ yếu là để so sánh dữ liệu trùng 2 cột trong excel để tìm ra sự tương đồng giống và khác nhau. Hàm COUNTIF được sử dụng nhiều để so sánh dữ liệu 2 cột Ưu điểm của hàm COUNTIF: Hàm này không phân biệt chữ hoa, chữ thường, so sánh được 2 cột dữ liệu chính xác. Nhược điểm của hàm COUNTIF: Các bước thực hiện phức tạp. Cú pháp của hàm: =COUNTIF(Range;Criteria) Trong đó: Range: Vùng chứa dữ liệu cần đếm (tham số bắt buộc). Criteria: Điều kiện dùng để đếm dữ liệu (tham số bắt buộc). Bước 1: Truy cập trang tính Excel và mở 2 dữ liệu cần so sánh Truy cập trang tính Excel và mở 2 dữ liệu cần so sánh Bước 2: Chọn bảng hàng cột thứ nhất khoanh vùng chọn > thay đổi tên bất kì viết liền không dấu, rồi Enter, tương tự với cột thứ 2 cũng vậy. Cách so sánh 2 cột dữ liệu trong excel Bước 3: Thao tác tiếp theo ở giữa khoảng trống của hàng so sánh thứ nhất nhập theo công thức hàm =COUNTIF(list2,A5), sau đó Enter và thực hiện thao tác bắt đầu so sánh theo công thức từ hàng đầu tiên. Cách so sánh 2 cột dữ liệu trong excel Như vậy sau khi thực hiện so sánh cho ra kết quả: Cách so sánh 2 cột dữ liệu trong excel Bước 4: Vào phần name box để chọn theo kí hiệu tam giác chọn list1 > vào mục Styles > chọn Conditional Formatting > chọn New ruler Cách so sánh 2 cột dữ liệu trong excel Hiện lên cửa sổ New formatting Rule, click vào dòng cuối cùng Cách so sánh 2 cột dữ liệu trong excel Bước 5: Tab mới mở ra, nhập công thức của hàm COUNTIF > Format > chọn trong bảng màu một màu > Format để hoàn thành. .=> Bạn sẽ thấy được xuất hiện trong bảng tính ở cột 1 những những giá trị được bôi theo màu bạn chọn, đó là những giá trị không có ở trong cột 2. Cách so sánh 2 cột dữ liệu trong excel Thực hiện thao tác tương tự với cột 2, trên màn hình sẽ hiện những giá trị không được trùng lặp trên trang tính là những giá trị được bôi màu trong cả 2 cột, như vậy việc khảo sát và kiểm tra so sánh dữ liệu 2 cột Excel của bạn đã hoàn thành. >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Sử dụng hàm so sánh trong excel EXACT Hàm EXACT để so sánh hai cột trong Excel. Ưu điểm của hàm EXACT: Dễ sử dụng dành cho những người mới tiếp xúc và làm quen với Excel. Nhược điểm của hàm EXACT: Hàm này phân biệt chữ hoa và chữ thường, ngoài ra hàm này chỉ so sánh được dữ liệu trên một hàng ngang. Cú pháp hàm: =EXACT(Text1;Text2) Trong đó: Text1: Dữ liệu thứ 1 cần so sánh. Text2: Dữ liệu thứ 2 cần so sánh. Cách thực hiện như sau: Bước 1: Quan sát ví dụ sau và so sánh 2 cột như hình. Hàm EXACT để so sánh dữ liệu Bước 2: Nhập công thức =Exact($A$2:$A$7;$B$2:$B$7) vào ô cần so sánh. Trong đó: $A$2:$A$7: Dữ liệu của cột 1 cần so sánh $B$2:$B$7: Dữ liệu của cột 2 cần so sánh. Nhập công thức hàm EXACT Bước 3: Nhấn Enter để nhận kết quả: True - giống nhau, False - khác nhau. Nhấn Enter để nhận kết quả Bước 4: Nhấn giữ ô vuông ỏ góc nhỏ ô vừa mới nhập công thức. Bạn kéo xuống để nhận kết quả ở những ô còn lại. Kéo ô vuông để nhận kết quả những ô còn lại >>> Xem thêm: Cách lọc dữ liệu trùng nhau trong 2 cột excel chính xác nhất Sử dụng hàm VLOOKUP để so sáng giá trị 2 cột Việc sử dụng hàm VLOOKUP so sánh 2 cột trong Excel để timg ra các giá trị trùng nhau nhanh chóng và hiệu quả nhất. Công thức: =VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup) Trong đó: Lookup_value: Giá trị cần dò tìm Table_array: Bảng giới hạn dò tìm. Col_index_num: Số thứ tự từ trái qua phải của cột lấy dữ liệu trong bảng cần dò tìm Range_lookup: Tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn Range_lookup = 1 (TRUE): Dò tìm tương đối. Range_lookup = 0 (FALSE): Dò tìm chính xác. Bạn có thể xem cách sử dụng công thức qua ví dụ sau: Tìm giá trị trùng nhau ở cột 1 so với cột 2 =VLOOKUP(=VLOOKUP(E4;$C$4:$C$8;1;0) Hàm Vlookup Những ô có kết quả trùng lặp sẽ xuất hiện giá trị trùng lặp, còn nếu không sẽ có biểu tượng lỗi #N/A. Sử dụng hàm IF, ISERROR và MATCH để so sánh dữ liệu trùng nhau trong excel Bên cạnh những lệnh so sánh 2 giá trị trong excel bằng hàm như bên trên đã chia sẻ, bạn cũng có thể sử dụng hàm IF, ISERROR và MATCH để so sánh 2 cột số liệu trong excel. Trước khi đi vào chi tiết cách so sánh dữ liệu 2 cột trong excel bằng các hàm này như thế nào, bạn cần hiểu rõ thông tin về các hàm này trước. Cụ thể như sau: Hàm IF: Đây là hàm điều kiện dùng để so sánh logic giữa giá trị này với giá trị khác do chính bạn chỉ định so sánh. Hàm ISERROR: Đây là hàm dùng để kiểm tra giá trị lỗi trong excel. Ngoài chức năng đó ra, hàm này cũng thường được thực hiện để so sánh tìm ra sự khác biệt. Hàm MATCH: Hàm này thường được được ứng dụng để tìm một mục được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của mục đó trong phạm vi này. Sau khi đã hiểu rõ các hàm này, sau đây chúng tôi sẽ hướng dẫn bạn cách sử dụng hàm để so sánh 2 cột excel. Bước 1: Trước hết bạn cần tạo dữ liệu cho cột A và B, công thức để so sánh dữ liệu trùng nhau trong excel sẽ nhập vào cột C. Bảng dữ liệu đã tạo Bước 2: Tiếp theo bạn tìm các giá trị giống nhau: Nhập vào cột C công thức hàm: =IF(ISERROR(MATCH(A1,$B$1:$B$7,0)),"",A1). Nhập công thức hàm để tìm các giá trị giống nhau Ý nghĩa của công thức hàm này đó là: Trong ô C1 hàm MATCH sẽ trả về số 5 (chữ A thuộc vị trí thứ 5 trong vùng dữ liệu B1:B7). Kết quả thu được là hàm ISERROR sẽ trả về FALSE và hàm IF trả về giá trị trong ô A1. Trong ô C4 hàm MATCH sẽ trả về lỗi #N/A (bởi vì không có chữ D trong phạm vi B1:B7). Kết quả cuối cùng hàm ISERROR sẽ trả về FALSE, còn hàm IF trả về chuỗi trống. Bước 3: Sau khi đã tìm được các giá trị giống nhau giữa 2 cột ở cột A. Tiếp theo bạn sẽ tìm các giá trị giống nhau giữa 2 cột ở cột B. Công thức tìm giá trị giống nhau lúc này sẽ là: =IF(ISERROR(MATCH(B1,$A$1:$A$7,0)),"",B1). Công thức tìm giá trị giống nhau ở cột B So sánh dữ liệu trùng 2 cột trong excel bằng Conditional Formatting Conditional Formatting là tính năng để đánh dấu các giá trị. Việc đánh dấu này giúp bạn tìm ra được các giá trị giống nhau hoặc khác nhau tuỳ theo yêu cầu của bạn. Để giúp bạn dễ dàng hình dung hơn unica sẽ hướng dẫn bạn cách dùng Conditional Formatting để so sánh dữ liệu 2 cột trong excel bằng, sau đây chúng tôi sẽ hướng dẫn chi tiết cho bạn. Bước 1: Đầu tiên từ thanh menu bạn chọn Home => Chọn Conditional Formatting => Chọn New Rule. Cửa sổ New Formatting Rule xuất hiện, trong mục Select a Rule Type bạn bấm chọn Use a formula to determine which cells to format => Nhập vào phần Format values where this formula is true công thức =MATCH(A2,$C$2:$C$12,0). Thao tác so sánh dữ liệu trùng 2 cột excel Bước 2: Tiếp theo bạn bấm chọn Format, hệ thống chuyển tới phần Format Cells => Tại mục Fill bạn tiến hành chọn màu. Chọn Fill để thay đổi màu Bước 3: Cuối cùng bạn bấm OK để kết thúc quá trình so sánh dữ liệu dữ 2 cột trong excel. Kết quả hiển thị ra như hình bên dưới. Kết quả hiển thị Một số lỗi thường gặp khi so sánh dữ liệu giữa 2 cột trong excel Cách so sánh dữ liệu 2 cột trong excel không có gì quá phức tạp. Vì vậy ai cũng có thể dễ dàng thực hiện nhanh chóng để quá trình làm việc với excel được thuận tiện nhất. Trong quá trình so sánh dữ liệu giữa 2 cột trong excel, bạn cần đặc biệt chú ý để khắc phục một số lỗi sau: Lỗi không khớp dữ liệu Lỗi không khớp dữ liệu khi so sánh dữ liệu giữa 2 cột trong excel có thể xảy ra do một số nguyên nhân như: Dữ liệu nhập sai hoặc không chính xác, dữ liệu được sắp xếp theo thứ tự khác nhau,... Để khắc phục vấn đề này bạn hãy: kiểm tra lại dữ liệu để xem đã nhập chính xác hay chưa? Lỗi sai cú pháp công thức Bên cạnh lỗi không khớp dữ liệu thì lỗi sai cú pháp công thức cũng là một lỗi phổ biến nhất. Nguyên nhân gây lỗi này là do: nhập sai tên hàm, sai cú pháp hàm, sử dụng tham chiếu ô không chính xác,... Để khắc phục này không có cách nào khác ngoài việc bạn phải nhập đúng cú pháp công thức hàm. Ngoài ra, bạn cũng cần phải đảm bảo tham chiếu ô trong công thức chính xác. Lỗi sai định dạng dữ liệu Dữ liệu được định dạng không giống nhau thì cũng sẽ gây lỗi trong quá trình so sánh dữ liệu giữa 2 cột trong excel. Để khắc phục lỗi trong tình huống này, bạn cần phải sử dụng công cụ định dạng để định dạng dữ liệu giống nhau trong cả hai cột. Lỗi sai định dạng dữ liệu rất hay thấy khi làm việc với excel Lỗi vùng dữ liệu không chính xác Lỗi vùng dữ liệu không chính xác thường xuất hiện do một số nguyên nhân như: chọn sai vùng dữ liệu, định dạng vùng dữ liệu không giống nhau. Cách khắc phục lỗi này vô cùng đơn giản, bạn chỉ cần đảm bảo chọn đúng dữ liệu vùng so sánh và đảm bảo vùng chọn được định dạng giống nhau là được. Lỗi số liệu trùng lặp Trong quá trình làm việc với excel, lỗi số liệu trùng lặp xuất hiện khá nhiều. Bạn cần đặc biệt chú ý tới lỗi này để quá trình làm việc được nhanh chóng và chủ động nhất. Để khắc phục lỗi số liệu trùng lặp này, bạn có thể sử dụng công cụ tìm kiếm để tìm các ô có dữ liệu trùng lặp. Kết luận Trên đây là tổng hợp 5 cách so sánh dữ liệu 2 cột trong excel. Đây đều là những thao tác đơn giản và tối ưu nhất mà Unica đã tổng hợp được nên hãy hãy lưu lại và áp dụng ngay để nhanh chóng tối ưu thời gian làm việc của mình. Để nâng cao kiến thức cũng như kỹ năng thực hành mời bạn đọc tham khảo các khóa tin học văn phòng trên trang Unica của chúng tôi nhé.

23394 Lượt xem

3 Cách sao chép và chèn bảng Excel vào Word chi tiết nhất

3 Cách sao chép và chèn bảng Excel vào Word chi tiết nhất Độ nhanh nhạy của con người tỉ lệ thuận với với sự tăng tiến của công nghệ hiện tại, làm việc chuyên nghiệp giờ đây không ai còn sử dụng nhiều việc viết và tính toán bằng tay. Hỗ trợ tích cực và hữu ích hiện nay là sử dụng phần mềm excel và word được dùng phổ biến ở tất cả mọi nơi, tất cả các doanh nghiệp trường học. Nhưng ít ai lại dùng kết hợp cả hai phần mềm vào cùng một công việc, bạn không thử nghĩ xem có phải rất tiện hay sao? Bạn có tò mò cách sao chép và chèn bảng excel vào word không? Chắc chắn sẽ khiến bạn thích thú đến nhường nào. 1. Microsoft Word & Microsoft Excel - hai phần mềm thông dụng Word và Excel hai thuật ngữ chẳng còn gì là xa lạ với chúng ta hiện nay, bởi tính năng tiện dụng và thông minh mà khiến 2 phần mềm luôn đứng đầu trong list các công cụ hỗ trợ làm việc xuất sắc nhất của bất kỳ ai. - Chúng ta cũng biết rằng WORD là chương trình soạn thảo văn bản ưu trội với của phần mềm microsoft . - Excel lại phổ biến với người dùng là một bảng tính chuyên nghiệp và vô cùng xuất sắc, khiến người sử dung phải thốt lên rằng: “ excel là thế giới của sự tiện lợi và thông minh nhất ” nó cũng thuộc sự quản lý của microsoft. => Nếu đã là hoàn hảo đến như vậy, chúng ta phải tạo thành quả của cả 2 tính năng để mà chất lượng công việc hơn những gì chúng ta muốn. Vậy ai muốn học Excel cũng như Word thành thạo hãy cùng theo dõi các bước dưới đây. 2. Sao chép, chèn bảng Excel vào word đơn giản nhất Kết hợp hai phần mềm với nhau bạn nghĩ là khó, chắc là nó sẽ rất phức tạp? Sao không tham khảo cách chèn bảng excel vào word dưới đây bạn sẽ nghĩ rằng nó sẽ khiến bạn thích thú hơn rất nhiều đấy. - Bước 1: Bạn chỉ cần mở vào trang tính, chọn bảng excel bạn muốn copy, copy bảng như các thao tác bình thường. Chọn bảng excel muốn copy >> Xem thêm: Cách chèn và sử dụng ký tự đặc biệt trong excel - Bước 2: Vào word để thực hiện tiếp thao tác paste bảng tính excel vào vị trí mong muốn. Mở file word bạn muốn chèn bảng. - Thực hiện thao tác paste nhưng lưu ý trước khi paste trực tiếp vào ở bản word bạn hay nhấp vào Paste > chọn paste special Chọn Paste > chọn paste special - Sẽ có cửa sổ tab mới hiện ra ngay khi bạn nhấp vào, bạn chỉ cần chọn Device Independent Bitmap > OK và trên bản word sẽ hiện được bảng bảng tính mà bạn cần. Chọn Device Independent Bitmap > OK - Vậy là bạn đã chuyển thành công, việc của bạn chỉ cần căn chỉnh sao bảng tính có kích thước vị trị như bạn mong muốn bằng cách nhấp vào bảng tính và căn chỉ vậy là đã hoàn thành, rất đơn giản và không có gì phức tạp. Thao tác chuyển thành công Lưu ý: khi thực hiện việc sao chép bảng excel vào word bạn không thể quan tâm tới việc như sau, khi bạn save cả bảng tính ở file gốc thì khi chuyển sang file excel các giữ liệu ở file gốc sẽ được hiển thị toàn bộ ở file copy và việc chỉnh sửa trực tiếp ở file gốc sẽ thay đổi luôn ở file copy trong bản word. >> Xem thêm: Hướng dẫn 4 cách sử dụng hàm hyperlink trong Excel Vậy nên bạn không muốn thay đổi thì khi mở lại bản word bạn vừa copy bảng excel, sẽ hiển thị lên: Xác nhận yes or no Việc của bạn chỉ cần xác nhận cho sự thay đổi là “yes” or “no”. 3. Sao chép, chèn bảng excel vào word bằng đối tượng liên kết - Bước 1: Mở File Word và File Excel mà bạn muốn chèn. - Bước 2: Dùng chuột kéo toàn bộ phần bảng tính mà bạn cần chèn từ File Excel vào File Word. - Bước 3: Sau đó, trên Tab Home, bạn nhấn nút Paste link. Tiếp tục chọn Paste Special. Cửa sổ Paste Special xuất hiện > Nhấp chuột vào Microsoft Excel Worksheet Object > Nhấn OK. 4. Chèn bảng Excel vào Word bằng Tab Insert - Bước 1: Mở File Word, trên thanh công cụ, bạn chọn mục Insert > Object > create from file > Browse. - Bước 2: Chọn File muốn chèn rồi nhấn Insert. - Bước 3: Dùng chuột và tích vào ô Link to File, sau đó nhấn OK để chèn File Excel vào Word. Như vậy thông qua bài viết trên đây, Unica đã cùng bạn tìm hiểu về 3 cách sao chép, chèn bảng Excel vào Word vô cùng đơn giản. Cảm ơn bạn đọc đã quan tâm bài viết của chúng tôi. Chúc bạn thành công! >> Xem thêm: Hướng dẫn cách chèn link vào trong excel kèm ví dụ minh họa

4096 Lượt xem

Không lưu được file excel: Nguyên nhân và cách khắc phục lỗi

Không lưu được file excel: Nguyên nhân và cách khắc phục lỗi Trong quá trình sử dụng Excel, bạn có thể gặp phải tình trạng không lưu được file excel, dẫn đến mất dữ liệu hoặc không thể chia sẻ file với người khác. Đây là một lỗi rất khó chịu và gây phiền toái cho người dùng. Vậy nguyên nhân của lỗi này là gì và làm thế nào để khắc phục? Hãy cùng tìm hiểu trong bài viết này. Tại sao không lưu được file excel? Có nhiều nguyên nhân có thể dẫn đến lỗi không lưu được file excel. Tuy nhiên, chúng ta có thể phân loại thành hai nhóm chính là nguyên nhân do thao tác lưu và nguyên nhân của ứng dụng. Lỗi excel không save được do thao tác lưu Một số nguyên nhân do thao tác lưu rất hay gặp khiến bạn không lưu được file excel là: Không có quyền truy cập vào vị trí lưu file, ví dụ như ổ đĩa bị khóa, bị đầy hoặc thuộc mạng LAN. Nhập sai tên file hoặc đường dẫn, ví dụ như sử dụng ký tự đặc biệt, khoảng trắng hoặc quá dài. Bạn lưu file vào một định dạng không được hỗ trợ bởi Excel, ví dụ như PDF, TXT hoặc CSV. Lưu file vào một phiên bản Excel cũ hơn phiên bản hiện tại, ví dụ như Excel 2003. Nguyên nhân không lưu được file excel do thao tác lưu gặp trục trặc Excel không cho lưu do lỗi ứng dụng Lỗi file excel không lưu được có thể xuất phát từ một số nguyên nhân của lỗi ứng dụng. Đó có thể là: Excel bị treo, đóng bất ngờ hoặc xảy ra xung đột với các ứng dụng khác. File excel bị hỏng, nhiễm virus hoặc chứa macro không an toàn. Excel bị thiếu các thành phần cần thiết để chạy, ví dụ như .NET Framework hoặc Visual Studio Tools for Office. Excel bị ảnh hưởng bởi các Add-In (tiện ích mở rộng) không tương thích hoặc gây ra xung đột. Nguyên nhân không lưu được file excel do lỗi ứng dụng Cách sửa lỗi excel không lưu được Tùy thuộc vào nguyên nhân của lỗi, bạn có thể áp dụng các cách sửa lỗi excel không lưu được khác nhau. Dưới đây là một số cách phổ biến và hiệu quả mà bạn có thể thử: Cách khắc phục lỗi không lưu được file excel bằng việc lưu file bằng tên mới Một cách đơn giản và nhanh chóng để sửa lỗi không thể lưu file excel là bạn có thể thử lưu file bằng một tên mới. Bạn chỉ cần: Bước 1: Chọn thẻ File trên thanh công cụ. Chọn thẻ File trên thanh công cụ Bước 2: Tìm và chọn Save As. Chọn Save As Bước 3: Sau đó nhập một tên mới cho file và chọn một vị trí lưu khác. Điều này có thể giúp bạn tránh được các nguyên nhân do thao tác lưu. Tìm nơi lưu file và nhập tên file >>> Xem thêm: Cách lấy lại file excel chưa lưu, bị lưu đè, bị xóa đơn giản Cách sửa lỗi lưu file excel: Thay đổi định dạng file Nếu bạn không lưu được file excel vì định dạng file không được hỗ trợ, bạn có thể thay đổi định dạng file sang một định dạng khác mà Excel có thể nhận diện. Bạn có thể chọn File > Save As, sau đó chọn một định dạng khác trong danh sách Save as type, ví dụ như XLSX, XLSM hoặc XLS. Tuy nhiên, bạn cần lưu ý rằng việc thay đổi định dạng file có thể làm mất một số tính năng hoặc dữ liệu của file. >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Sửa lỗi không lưu được file excel bằng cách lưu file excel dưới định dạng HTML Một cách khác để sửa lỗi không lưu được file excel là bạn có thể lưu file excel dưới định dạng HTML. Điều này có thể giúp bạn bảo toàn được nội dung và bố cục của file, đồng thời tránh được các nguyên nhân do ứng dụng. Bạn có thể chọn File > Save As, sau đó chọn HTML trong danh sách Save as type. Sau khi lưu file thành công, bạn có thể mở lại file bằng Excel và lưu lại dưới định dạng mong muốn. Sửa lỗi không lưu được file excel bằng cách lưu file excel dưới định dạng HTML Thử lưu file excel bị lỗi vào vị trí khác Nếu bạn không lưu được file excel vào vị trí hiện tại, bạn có thể thử lưu file excel vào một vị trí khác, ví dụ như ổ đĩa khác, USB hoặc máy tính khác. Điều này có thể giúp bạn kiểm tra xem vị trí lưu file có bị vấn đề gì không, ví dụ như bị khóa, bị đầy hoặc bị hỏng. Cách khắc phục hiệu quả lỗi excel không lưu được: Lưu file excel mới vào vị trí ban đầu Một cách khắc phục hiệu quả và an toàn cho lỗi excel không lưu được là bạn có thể tạo một file excel mới và sao chép nội dung từ file cũ sang file mới. Sau đó, bạn có thể lưu file mới vào vị trí ban đầu của file cũ. Điều này có thể giúp bạn loại bỏ các nguyên nhân do file excel bị hỏng, nhiễm virus hoặc chứa macro không an toàn. Vô hiệu hóa Add-In Excel – đơn giản nhưng hiệu quả Một cách cuối cùng để sửa lỗi excel không lưu được là bạn có thể vô hiệu hóa các Add-In Excel (tiện ích mở rộng) mà bạn đã cài đặt cho Excel. Các Add-In Excel có thể mang lại nhiều tính năng hữu ích cho Excel, nhưng cũng có thể gây ra xung đột hoặc không tương thích với Excel. Bạn có thể vô hiệu hóa các Add-In Excel bằng cách chọn File > Options > Add-Ins, sau đó chọn Manage > COM Add-ins > Go. Trong hộp thoại COM Add-Ins, bạn bỏ chọn các Add-In Excel mà bạn muốn vô hiệu hóa và nhấn OK. Sau đó, bạn khởi động lại Excel và thử lưu file lại. Vô hiệu hóa Add-In Excel Tạm kết Trên đây là một số nguyên nhân và cách sửa lỗi khi không lưu được file excel mà bạn có thể tham khảo. Hy vọng bài viết này đã giúp bạn khắc phục được lỗi này và lưu file excel thành công. Nếu bạn muốn học chuyên sâu về excel, hãy tham khảo ngay các khóa học Excel online của Unica. Tại đây, các chuyên gia của chúng tôi sẽ mang tới những kiến thức thú vị và hấp dẫn để bạn có thể hiểu và thực hành được ngay sau khi học xong. >>> Xem thêm: Bỏ túi 10 cách khắc phục file excel bị lỗi không mở được >>> Xem thêm: Nguyên nhân và cách khắc phục lỗi không copy được trong Excel >>> Xem thêm: Thủ thuật lấy lại file excel chưa lưu, bị lưu đè, bị xóa đơn giản

14161 Lượt xem

Cách sử dụng hàm NETWORKDAYS trong excel để tính ngày

Cách sử dụng hàm NETWORKDAYS trong excel để tính ngày Trong bài viết này, chúng ta sẽ tìm hiểu về hàm NETWORKDAYS trong excel, công dụng của nó và cách sử dụng nó để tính số ngày làm việc giữa hai ngày bất kỳ. Hàm NETWORKDAYS là một hàm rất hữu ích khi bạn muốn lập kế hoạch, quản lý thời gian hoặc theo dõi hiệu suất làm việc của mình hoặc của nhân viên. Giới thiệu về hàm networkdays trong excel Bạn đã biết NETWORKDAY là hàm gì và công dụng của nó trong Excel chưa? Nếu chưa biết, mời bạn cùng Unica tìm hiểu qua bài viết dưới đây: Networkdays là hàm gì? Hàm networkdays trong excel là một hàm thuộc nhóm hàm ngày và giờ trong excel. Hàm này trả về số ngày làm việc giữa hai ngày bất kỳ, bỏ qua các ngày nghỉ cuối tuần (thứ bảy và chủ nhật) và các ngày nghỉ lễ (nếu có). Hàm NETWORKDAYS là một hàm thuộc nhóm hàm Ngày và giờ trong excel Công dụng của hàm networkdays trong Excel Hàm NETWORKDAYS có nhiều công dụng trong excel, chẳng hạn như: Tính số ngày làm việc còn lại trong một dự án, một công việc hoặc một kỳ hạn. Tính số ngày làm việc đã trôi qua từ một ngày bắt đầu cho đến một ngày kết thúc. Tính số ngày làm việc trung bình trong một khoảng thời gian nhất định. Tính số ngày làm việc tối đa hoặc tối thiểu trong một khoảng thời gian nhất định. Tính tỷ lệ hoàn thành công việc theo số ngày làm việc. Tính lương, thưởng, phụ cấp hoặc trừ phạt theo số ngày làm việc. >>> Xem thêm: Học tin học văn phòng trọn đời tại Unica chỉ từ 99K Công dụng của hàm Networkday Hướng dẫn cách sử dụng hàm networkdays trong excel Ở phần này, chúng tôi sẽ giới thiệu về cú pháp, cách sử dụng hàm NETWORKDAYS và ví dụ minh họa. Chi tiết như sau: Cú pháp hàm tính số ngày làm việc trong excel Cú pháp của hàm NETWORKDAYS trong excel là: =NETWORKDAYS(start_date, end_date, [holidays]) Cú pháp hàm NETWORKDAYS Trong đó: start_date: là ngày bắt đầu của khoảng thời gian cần tính số ngày làm việc. Ngày này phải được nhập theo định dạng ngày tháng năm hợp lệ trong excel, ví dụ 01/01/2023 hoặc 1-Jan-2023. end_date: là ngày kết thúc của khoảng thời gian cần tính số ngày làm việc. Ngày này cũng phải được nhập theo định dạng ngày tháng năm hợp lệ trong excel, ví dụ 31/12/2023 hoặc 31-Dec-2023. [holidays]: là một danh sách các ngày nghỉ lễ (không bao gồm cuối tuần) mà bạn muốn loại bỏ khỏi số ngày làm việc. Đây là một tham số tùy chọn, nếu bạn bỏ qua nó, hàm sẽ chỉ tính số ngày làm việc không bao gồm cuối tuần. Danh sách này có thể được nhập trực tiếp vào công thức hoặc được tham chiếu từ một ô hoặc một vùng ô chứa các ngày nghỉ lễ. Các ngày nghỉ lễ cũng phải được nhập theo định dạng ngày tháng năm hợp lệ trong excel. >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Ví dụ minh họa về công thức tính ngày làm việc trong excel Để hiểu hơn về cách sử dụng hàm NETWORKDAYS, mời bạn tham khảo ví dụ dưới đây: Ví dụ 1: Cho bảng dữ 1 Cho bảng dữ liệu 1 Yêu cầu: Thực hiện cách tính ngày làm việc trong excel của từng nhân viên. Cách tính số ngày làm việc trong excel: Bước 1: Ở ô E3, bạn nhập công thức =NETWORKDAYS(B3;C3;D3). Nhập công thức vào ô E3 Bước 2: Nhấn enter sẽ thu được kết quả là 13 ngày. Kết quả sau khi nhập công thức Bước 3: Kéo con trỏ chuột xuống phía dưới sẽ thu được bảng kết quả như hình: Kết quả Ví dụ 2: Cho bảng dữ 2 Cho bảng dữ liệu 2 Yêu cầu: Tính số ngày làm việc thực tế của từng nhân viên. Cách dùng hàm tính ngày làm việc trong excel: Bước 1: Nhập công thức =NETWORKDAYS(B12;C12;D12) vào ô E12. Nhập công thức NETWORKDAYS Bước 2: Nhấn Enter sẽ thu được kết quả là 12. Kết quả sau khi nhập công thức Bước 3: Copy công thức xuống các ô bên dưới sẽ thu được bảng kết quả là: Copy công thức xuống các ô bên dưới Sau khi tính toán, bạn có thể đặt bảo vệ trang tính để bảo mật thông tin cá nhân và tránh bị chỉnh sửa gây ra lỗi trang tính. Lưu ý trong cách sử dụng hàm networkday trong excel Khi sử dụng hàm NETWORKDAYS trong excel, bạn cần lưu ý một số điểm sau: Mặc định, hàm NETWORKDAYS không tính cuối tuần (Thứ 7 và Chủ Nhật) làm việc. Nếu bạn muốn tính cả cuối tuần, bạn cần sử dụng hàm NETWORKDAYS.INTL và cung cấp thông tin về các ngày cuối tuần. Hàm NETWORKDAYS sẽ không tính các ngày trùng với cuối tuần hoặc ngày nghỉ lễ. Nếu bạn muốn bao gồm chúng, bạn sẽ phải điều chỉnh thủ công sau khi sử dụng hàm. Luôn luôn kiểm tra kết quả trước khi sử dụng kết quả từ hàm NETWORKDAYS, để đảm bảo nó phù hợp với mong đợi của bạn. Khi bạn nhập giá trị nào không phải là ngày hợp lý, hàm NETWORKDAYS sẽ trả về giá trị lỗi #VALUE. Đảm bảo xử lý các trường hợp lỗi, chẳng hạn như trường hợp ngày bắt đầu lớn hơn ngày kết thúc. Lưu ý khi sử dụng hàm NETWORKDAYS Kết luận Hàm NETWORKDAYS trong excel là một hàm rất hữu ích khi bạn muốn tính số ngày làm việc giữa hai ngày bất kỳ, bỏ qua các ngày nghỉ cuối tuần và các ngày nghỉ lễ. Hàm này có cú pháp đơn giản và dễ sử dụng. Bạn chỉ cần nhập hoặc chọn ngày bắt đầu, ngày kết thúc và danh sách các ngày nghỉ lễ (nếu có) vào công thức. Nếu bạn muốn thay đổi ngày nghỉ cuối tuần theo quy định khác, bạn có thể sử dụng hàm NETWORKDAYS.INTL với một số hoặc một chuỗi để xác định các ngày nghỉ cuối tuần. Hy vọng bài viết này đã giúp bạn hiểu rõ hơn về cách sử dụng hàm NETWORKDAYS trong excel để tính số ngày làm việc. Để biết được thêm nhiều kiến thức hữu ích để áp dụng hơn vào trong công việc mời bạn đọc tham khảo nhiều hơn các khoá học Excel trên Unica bạn nhé. Hy vọng bài viết này sẽ giúp ích cho các bạn. >>> Xem thêm: Cách dùng hàm datedif trong excel tính khoảng thời gian >>> Xem thêm: Cách sử dụng hàm IF với điều kiện ngày tháng trong Excel >>> Xem thêm: Cách sử dụng hàm TODAY trong Excel tính thời gian thực có ví dụ

14933 Lượt xem

Hướng dẫn cách tạo form nhập liệu trong excel cho người mới

Hướng dẫn cách tạo form nhập liệu trong excel cho người mới Bạn muốn nhập dữ liệu nhanh hơn, hạn chế mọi sai sót các con số, thống kế trong quá trình nhập liệu, để làm sao đúng quy trình công việc, vậy chúng ta cần tạo một form nhập liệu để tối ưu công việc của mình. Dưới đây chúng ta sẽ cùng nhau học cách tạo form nhập liệu trong excel thật đơn giản và dễ dàng thực hiện nhé! Nào bắt đầu thôi! Form trong Excel là gì? Form trong Excel là một công cụ hỗ trợ nhập liệu vào bảng tính một cách nhanh chóng và tiện lợi. Form cho phép bạn nhập, sửa, xem và xóa dữ liệu trong các ô của bảng tính mà không cần phải di chuyển con trỏ chuột hay sử dụng bàn phím. Form có giao diện đơn giản và dễ sử dụng, thích hợp cho người mới bắt đầu học Excel. Form trong Excel là một công cụ hỗ trợ nhập liệu vào bảng tính một cách nhanh chóng và tiện lợi Vì sao cần tạo form nhập liệu trong Excel? Tạo form nhập liệu trong Excel có nhiều lợi ích, như: Tiết kiệm thời gian và công sức khi nhập liệu vào bảng tính, đặc biệt khi số lượng dữ liệu lớn và có nhiều trường thông tin. Giảm thiểu sai sót khi nhập liệu do không cần phải nhớ vị trí của các ô hay kiểu dữ liệu của các trường. Dễ dàng kiểm tra và chỉnh sửa dữ liệu đã nhập bằng cách sử dụng các nút điều hướng trên form. Bảo mật dữ liệu bằng cách ẩn bảng tính khi sử dụng form và chỉ cho phép người dùng có quyền truy cập vào form. Tạo form nhập liệu trong Excel có nhiều lợi ích Cách tạo form nhập liệu trong excel Để tạo form nhập liệu trong excel, bạn cần thực hiện hai bước chính là tạo bảng và thêm lệnh mở form. Hướng dẫn tạo form nhập liệu trong excel chi tiết sẽ ở dưới đây: Chuẩn bị các công cụ để tạo Form trong Excel Bước 1: Khi tạo form điền thông tin trong excel, bạn cần tạo tiêu đề bạn cần đưa vào form, ở đây tôi sẽ tạo 4 tiêu đề gồm: Họ và tên Lớp Năm sinh Quê quán Tạo tiêu đề bạn cần đưa vào form >>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO" [blog_custom:2] [trial-btn-v4[link=https://unica.vn/order/step1?id=2851&quantity=1][text=ĐĂNG KÝ MUA NGAY][color=#ffffff][width=275px][height=50px][bgcolor=#f26c50][newtab=true]] Bước 2: Bôi đen vùng chứa tiêu đề từ A1:D1. Sau đó, bạn chọn thẻ Insert > Chọn Table. Chọn Table Bước 3: Khi hộp thoại Create Table xuất hiện, bạn chọn My table has headers để Form được tạo sẽ hiện tiêu đề bạn vừa nhập. Nhấn OK. Nhấn OK Bước 4: Chọn thẻ File. Chọn file Bước 5: Tìm và chọn Option. Chọn Option Bước 6: Trong hộp thoại Excel Option, bạn chọn mục Customize Ribbon > Nhìn sang Choose Commands from > Chọn tiếp All Commands. Chọn tiếp All Commands Bước 7: Kéo chuột xuống phía dưới > Chọn Form > Nhấn vào New Tab. Nhấn vào New Tab Bước 8: Chọn vào New Group (Custom) > Nhấn chuột vào Rename. Chọn Rename Bước 9: Khi hộp thoại Rename xuất hiện, bạn nhập Form tại Display name. Sau đó, bạn chọn icon giống trong hình. Chọn icon giống trong hình Bước 10: Nhấn vào New Tab (Custom) vừa tạo. Để đổi tên cho thẻ mới tạo, bạn nhấn vào Rename. Nhấn chọn Rename Bước 11: Chọn Form giống ở bước 6. Để thêm Form vào thanh Ribbon, bạn nhấn Add. Thêm Form vào thanh Ribbon Bước 12: Khi đã thêm thành công bạn nhấn OK hoàn thành các bước chuẩn bị trước khi tạo Form trong Excel. Nhấn OK hoàn thành Tạo Form nhập dữ liệu trong Excel Bước 1: Bôi đen vùng tiêu đề đã tạo ban đầu. Tiếp đến, bạn chọn thẻ tạo Form > Chọn Form. Bôi đen vùng tiêu đề đã tạo ban đầu Bước 2: Sheet để điền dữ liệu Form xuất hiện. Các mục bạn cần biết là: New: Thêm một hàng mới, trống vào bảng hiện có. Delete: Xóa hàng hiện tại bạn đang chỉnh sửa. Restore: Khôi phục (đặt lại) trường dữ liệu bạn đang chỉnh sửa về giá trị ban đầu. Find Prev: Di chuyển form bạn đang nhập về trước. Find Next: Di chuyển form bạn đang nhập đến form tiếp theo. Criteria: Cho phép bạn tìm kiếm các form cụ thể trong bảng. Close: Đóng biểu mẫu nhập dữ liệu. Các mục trong Sheet1 Bước 3: Nhập thông tin mà bạn muốn vào từng mục. Tiếp đó, bạn chọn Criteria để áp dụng vào file Excel. Chọn Criteria để áp dụng vào file Excel Bước 4: Để đóng Form đang nhận, bạn chọn Close. Chọn Close Bước 5: Bôi đen toàn bộ tiêu đề và dòng mới được nhập > Chọn thẻ Tạo Form > Chọn Form. Chọn Form Bước 6: Chọn New để thêm các dữ liệu mới. Chọn New Bước 7: Bạn cần thực hiện lại các bước trên cho đến khi hoàn thành cách tạo form trong excel. Các bước tạo form nhập liệu excel VBA Excel Ngoài cách tạo form nhập liệu trong excel bằng cách sử dụng công cụ có sẵn, bạn cũng có thể tạo form nhập liệu trong excel bằng cách sử dụng VBA (Visual Basic for Applications). VBA là một ngôn ngữ lập trình cho phép bạn tạo các ứng dụng tùy biến trong Excel và các ứng dụng Office khác. Tạo UserForm trong Excel Bước 1: Mở cửa sổ VBA Excel để tiến hành tạo form nhập liệu bằng VBA bằng cách vào tab Developer > Chọn nhóm Code > Visual Basic. Bước 2: Tạo Module mới bằng cách nhấn chuột phải tại Sheet1 > Chọn Insert > Chọn Module. Module này sẽ là cửa sổ viết code của bạn. Chọn Module Bước 3: Nhấn chuột phải tại Sheet1 > Insert > UserForm để tạo một form nhập liệu bằng VBA. Chọn UserForm để tạo một form nhập liệu bằng VBA Bước 4: Tại hộp thoại Toolbox xuất hiện cùng UserForm, bạn sẽ tạo các đề mục bằng cách nhấn vào biểu tượng đề mục (label) và kéo vẽ một đề mục trong form dữ liệu. Tên mặc định hiển thị của đề mục là Label1, việc của bạn là đổi nó thành Họ và tên. Đổi tên mặc định thành Họ và tên Bước 5: Để chỉnh sửa font chữ và kích cỡ, bạn hãy nhìn sang cửa sổ Properties - Sheet 1 bên trái màn hình. Kéo xuống một chút, nhấn vào dấu ... tại mục Font để sửa đổi font, hiệu ứng chữ và kích cỡ chữ theo ý muốn của mình. Chỉnh sửa font chữ và kích cỡ Bước 6: Để tạo tất cả các đề mục, bạn không cần lặp lại các bước trên mà chỉ cần Copy Paste đề mục Họ và tên rồi tiến hành đổi tên đề mục lần lượt thành Email và Số điện thoại. Copy Paste đề mục Họ và tên rồi tiến hành đổi tên đề mục lần lượt thành Email và Số điện thoại Nếu muốn chiều ngang các ô đề mục hiển thị bằng nhau, bạn hãy chọn tất cả đề mục, nhấn chuột phải > Chọn Make Same Size > Chọn Width. Các đề mục tại UserForm trong Excel sẽ được co dãn về cùng chiều ngang. Chọn Width Tạo ô nhập liệu cho UserForm trong Excel Bước 1: Để tạo form nhập liệu bằng VBA, bạn nhấn vào biểu tượng TextBox trong hộp thoại Toolbox. Nhấn vào biểu tượng TextBox trong hộp thoại Toolbox Bước 2: Bạn Copy Paste để tạo thêm 2 ô nhập liệu cho 2 đề mục còn lại. Trong trường hợp các ô có chiều rộng không đều, bạn có thể chọn tất cả các ô, nhấn chuột phải > Chọn Make Same Size > Chọn Width như ở phần trên để đưa các ô về cùng một chiều rộng. Đưa các ô về cùng một chiều rộng Tạo nút xác nhận cho UserForm trong Excel Bước 1: Trong hộp thoại Toolbox, hãy nhấn vào biểu tượng CommandButton và vẽ nút xác nhận tại cửa sổ Userform trong Excel. Sau thao tác này, một nút bấm sẽ được tạo. Bạn cần đổi tên nút thành Nhập dữ liệu. Đổi tên nút thành Nhập dữ liệu Bước 2: Đổi tên của các vật thể tại Userform trong Excel tương ứng với loại vật thể. Nơi thực hiện đổi tên vật thể để đơn giản hóa quá trình tạo form nhập liệu bằng VBA chính là ở ngay dòng đầu của phần Properties - Sheet 1. Ô nhập liệu: Đổi tên ô nhập liệu lần lượt thành txtName, txtEmail và txtMobile. Đổi tên ô nhập liệu Nút nhập dữ liệu: Đổi tên thành btnInsert. Đổi tên thành btnInsert Hướng dẫn mở Form nhập dữ liệu trong Excel Sau khi tạo form nhập liệu trong excel, bạn có thể mở form để nhập, sửa, xem và xóa dữ liệu trong bảng tính. Bạn có thể mở form theo hai cách là sử dụng lệnh mở form trên QAT hoặc sử dụng mã VBA. Sử dụng lệnh mở form trên QAT Bạn chỉ cần nhấn vào biểu tượng hình mẫu đơn trên QAT để mở form nhập liệu cho bảng tính hiện tại. Bạn nên chọn một ô bất kỳ trong bảng trước khi nhấn vào biểu tượng này để form có thể nhận diện được các trường thông tin của bảng. Sử dụng mã VBA Bạn có thể viết một đoạn mã VBA để gọi UserForm bạn đã tạo từ một nút, một hình ảnh, một liên kết hoặc một đối tượng khác trong bảng tính. Bạn có thể sử dụng phương thức Show của UserForm để hiển thị form lên màn hình. Ví dụ, nếu bạn đặt tên UserForm là frmDataEntry, bạn có thể viết mã VBA như sau: Sub ShowForm() frmDataEntry.Show End Sub Cách tạo Form nhập dữ liệu trong Excel bằng phím tắt nhanh nhất Nếu bạn không muốn sử dụng lệnh mở form trên QAT hoặc mã VBA, bạn cũng có thể tạo form nhập liệu trong excel bằng phím tắt nhanh nhất. Bạn chỉ cần nhấn tổ hợp phím Alt + D + O để mở form nhập liệu cho bảng tính hiện tại. Tuy nhiên, bạn cần chú ý rằng phím tắt này chỉ hoạt động khi bạn đã thêm lệnh mở form vào QAT trước đó. Cách tạo Form nhập dữ liệu trong Excel bằng phím tắt nhanh nhất Tổng kết Trong bài viết này, chúng tôi đã hướng dẫn bạn cách tạo form nhập liệu trong excel cho người mới bằng ba cách khác nhau: sử dụng công cụ có sẵn, sử dụng VBA và sử dụng phím tắt. Bạn có thể lựa chọn cách nào phù hợp với nhu cầu và kỹ năng của bạn để tạo form nhập liệu trong excel một cách dễ dàng và hiệu quả. Mời bạn đọc quan tâm chủ đề tin học văn phòng cùng những chủ đề hot khác tham khảo thêm những khoá học Excel mới tại Unica! >>> Xem thêm: Cách đổi tên File Excel chỉ mất 5 giây >>> Xem thêm: Hướng dẫn chi tiết cách tạo macro excel nhanh chóng và đơn giản >>> Xem thêm: Cách dùng data validation trong Excel tạo list nhập nhanh dữ liệu

10031 Lượt xem

Cách lấy lại file excel chưa lưu, bị lưu đè, bị xóa đơn giản

Cách lấy lại file excel chưa lưu, bị lưu đè, bị xóa đơn giản Khi bạn đóng file Excel mà quên lưu, máy gặp sự cố mất điện đột ngột hoặc laptop gặp trục trặc, có thể dẫn đến việc mất mát dữ liệu trong file Excel của bạn. Để giúp bạn khôi phục lại tài liệu excel, file bị ghi đè hoặc bị xóa một cách đơn giản. Sau đây, Unica hướng dẫn cách lấy lại file excel chưa lưu, bị lưu đè, bị xóa đơn giản. Cùng tìm hiểu ngay. Có lấy lại được file Excel chưa lưu không? Bạn có thể dễ dàng lấy lại các file Excel chưa lưu một cách trọn vẹn. Tuy nhiên, để có thể xem lại và khôi phục file Excel chưa lưu, bạn cần kích hoạt tính năng Auto Save (Tự động lưu) hoặc AutoRecover (Tự động khôi phục). Chỉ khi đó, bạn mới có thể lấy lại được file Excel chưa lưu. Dưới đây là một số tình huống mà bạn cần khôi phục file Excel chưa lưu một cách nhanh chóng nhất: Máy tính bị mất điện đột ngột hoặc lỗi cắm điện. Khi bạn đóng file nhưng quên lưu trước đó. Khi file Excel bị ghi đè bởi dữ liệu mới so với dữ liệu cũ. Không lưu được file có thể do virus hoặc lỗi ổ cứng Hướng dẫn bật AutoSave và AutoRecover AutoSave và AutoRecover là hai tính năng quan trọng trong Microsoft Excel giúp bảo vệ dữ liệu và giảm thiểu mất mát thông tin khi làm việc trên các tập tin Excel. Dưới đây là tổng quan và cách bật cả hai tính năng này: Tổng quan về AutoSave và AutoRecover AutoSave Chức năng: AutoSave tự động lưu các thay đổi trong tài liệu Excel mỗi khi bạn làm việc trên nó. Điều này giúp người dùng không cần phải nhớ lưu tài liệu thủ công và đảm bảo rằng dữ liệu mới nhất được lưu lại. Thao tác: AutoSave hoạt động tự động và liên tục trong quá trình làm việc trên Excel. Bạn không cần phải thực hiện bất kỳ thao tác cụ thể nào để kích hoạt AutoSave. AutoSave tự động lưu các thay đổi trong tài liệu Excel mỗi khi bạn làm việc trên nó AutoRecover Chức năng: AutoRecover tự động tạo bản sao lưu tạm thời của tài liệu Excel định kỳ. Trong trường hợp sự cố xảy ra như mất điện đột ngột hoặc Excel bị đóng không đúng cách, AutoRecover giúp phục hồi tài liệu từ các bản sao lưu này. Thao tác: AutoRecover tự động chạy ngầm và không yêu cầu người dùng thực hiện bất kỳ thao tác nào. Tuy nhiên, bạn có thể tùy chỉnh tần suất lưu và nơi lưu trữ các bản sao lưu tạm thời của AutoRecover trong cài đặt của Excel. AutoRecover tự động tạo bản sao lưu tạm thời của tài liệu Excel định kỳ Cách bật AutoSave và AutoRecover AutoSave và AutoRecover là hai công cụ quan trọng trong Microsoft Excel, giúp người dùng tự động lưu và khôi phục các tập tin một cách dễ dàng. Dưới đây là hướng dẫn cách kích hoạt hai tính năng này trên các phiên bản Excel khác nhau: Đối với Microsoft Excel 2010, 2013, 2016, 2019 Bước 1: Mở Excel và chọn thẻ File. Chọn thẻ File Bước 2: Chọn Options trong danh sách More. Nếu có sẵn Options sau khi chọn File, bạn có thể chọn luôn Options. Chọn Options Bước 3: Trong hộp thoại Excel Options, chọn mục Save. Chọn mục Save Bước 4: Tại mục Save, tích chọn Save Auto Recover và Keep the last Autosave, sau đó nhấn OK. Thời gian tự động lưu theo khuyến nghị của Microsoft là 15 phút, bạn có thể chỉnh thấp hoặc cao hơn con số này đều được. Nếu cấu hình máy tính của bạn thấp thì bạn không nên chỉnh thời gian tự động lưu quá thấp (1 phút > 5 phút) vì nó có thể làm cho máy của bạn bị giật lag. Nhấn ok là hoàn thành Đối với Microsoft Excel phiên bản 2007 Bước 1: Mở file Excel và chọn Microsoft > Excel Options. Chọn Excel Options Bước 2: Trong hộp thoại Excel Options, chọn thẻ Save. Chọn thẻ Save Bước 3: Chọn Save AutoRecover information every và điều chỉnh thời gian tự động lưu theo ý muốn. Điều chỉnh thời gian tự động lưu Đối với phiên bản Excel 2003 Bước 1: Trong giao diện Excel, chọn thẻ Tool > Options. Bước 2: Chọn thẻ Save trong hộp thoại Options. Bước 3: Tại mục Save AutoRecover info every, tích vào ô và thiết lập thời gian tự động lưu cho Excel. Cách bật AutoSave, AutoRecover đối với phiên bản Excel 2003 AutoSave và AutoRecover là hai tính năng quan trọng giúp người dùng Excel tiết kiệm thời gian và bảo vệ dữ liệu trong trường hợp có sự cố xảy ra. Hãy trang bị kiến thức về hai tính năng này nếu bạn là người mới học Excel, chúng sẽ rất hữu ích trong quá trình làm việc. Cách lấy lại file excel chưa lưu Trong trường hợp bạn ấn nhầm nút tắt file Excel hoặc quên chưa lưu, bạn vẫn có thể lấy lại được file. Cách này có thể áp dụng được cho các phiên bản Excel 2007 trở lên. Để lấy lại file, bạn thực hiện theo các bước sau: Hướng dẫn nhanh Mở file Excel > chọn File > nhấp vào mục Open > chọn Recover Unsaved Documents > chọn phiên bản file Excel chưa lưu > nhấn vào Save As > chọn vị trí mới để lưu lại file chưa lưu. Hướng dẫn chi tiết Bước 1: Mở một file Excel mới > trên thanh công cụ chọn thẻ File. Chọn thẻ file trên thanh công cụ Bước 2: Nhấn vào mục Open. Chọn mục Open Bước 3: Trên giao diện sẽ có mục Recent > chọn Recover Unsaved Workbooks (Khôi phục những tài liệu chưa lưu). Ở mục Recent chọn Recover Unsaved Workbooks Bước 4: Lúc này sẽ xuất hiện hộp thoại có các phiên bản file Excel mà bạn chưa kịp lưu > bạn chọn file > nhấn Open để mở. Xuất hiện hộp thoại có các phiên bản file Excel mà bạn chưa kịp lưu Bước 5: Tại mục This is a recovered file that is temporarily stored on your computer chọn Save As. Chọn Save As Bước 6: Chọn vị trí file mà bạn muốn lưu, bạn đặt tên file tùy ý > nhấn Save để lưu. Chọn vị trí rồi ấn Save Cách lấy file Excel đã bị ghi đè Không chỉ lấy lại được file Excel chưa lưu, bạn còn lấy lại được những file đã ghi đè nội dung mới mà bạn đã nhấn lưu. Cách này được áp dụng cho tất cả các phiên bản Excel từ 2012 và cho cả MacOS. Cách thực hiện như sau. Hướng dẫn nhanh Mở file Excel > chọn thẻ File > chọn mục Information/Info > chọn Manage Document > Chọn các phiên bản trước khi bị ghi đè. Hướng dẫn chi tiết Bước 1: Mở một file Excel mới > trên thanh công cụ chọn thẻ File. Chọn thẻ file trên thanh công cụ Bước 2: Chọn mục Information/Info Chọn mục Info Bước 3: Chọn Manage Workbook > chọn Recover Unsaved Workbooks. Chọn Manage Workbook rồi chọn Recover Unsaved Workbooks. Bước 4: Chọn các phiên bản trước khi bị ghi đè > ấn Open. Xuất hiện hộp thoại có các phiên bản file Excel mà bạn chưa kịp lưu Bước 5: Tại mục This is a recovered file that is temporarily stored on your computer chọn Save As. Chọn Save As Bước 6: Chọn vị trí file mà bạn muốn lưu, bạn đặt tên file tùy ý > nhấn Save để lưu. Chọn vị trí rồi ấn Save Hướng dẫn cách lấy lại file excel chưa lưu bởi phần mềm Nhắc tới những cách lấy lại file chưa lưu trong excel chắc hẳn ai cũng nghĩ ngay đến việc sử dụng phần mềm. Sử dụng phần mềm thậm chí bạn còn lấy được những file đã bị xoá. Dưới đây sẽ là phần mềm Recuva dùng để lấy lại file excel đã bị xóa: Giới thiệu phần mềm Recuva Phần mềm Recuva là một phần mềm miễn phí giúp bạn khôi phục dữ liệu mới xóa và chưa bị ghi đè lên cực kỳ hiệu quả. Nó có thể thực hiện trên các phiên bản Excel và cả trên MacOS. Ngoài ra, bạn có thể sử dụng thêm phần mềm GetDataBack để khôi phục lại dữ liệu. Phần mềm này có yêu cầu trả phí, nhưng vẫn có bản dùng thử cho bạn. Recuva là một phần mềm phục hồi dữ liệu miễn phí Cách lấy lại file excel chưa lưu bằng phần mềm Recuva Bước 1: Bắt đầu bằng việc cài đặt và quét dữ liệu cần khôi phục theo hướng dẫn trong video ở phía dưới. Thời gian quét sẽ phụ thuộc vào lượng dữ liệu, có thể nhanh hoặc chậm tùy thuộc vào trường hợp cụ thể. Quét dữ liệu cần khôi phục Bước 2: Sau khi quét hoàn tất, chương trình sẽ liệt kê các file đã bị xóa, được phân biệt bởi các màu sau: Màu xanh lá cây: Dữ liệu có thể khôi phục hoàn toàn. Màu đỏ: Dữ liệu không thể khôi phục. Màu vàng: Dữ liệu có khả năng khôi phục nhưng thấp. Bước 3: Chọn những ô chứa dữ liệu mà bạn muốn khôi phục và nhấp vào nút "Recover". Chọn Recover Bước 4: Recuva cũng có tính năng lọc dữ liệu theo định dạng file: Chọn Scan > Scan Content Chọn Scan Content Trong hộp thoại hiện ra, chọn File Mask và nhập định dạng file muốn khôi phục (ví dụ: *.doc, *.docx, *.htm,...) và nhập tên file vào Search String. Cuối cùng, nhấn "Scan" để tiến hành quét và lọc dữ liệu theo yêu cầu. Nhấn Scan để tiến hành quét và lọc dữ liệu theo yêu cầu Xem thêm: Bỏ túi 10 cách khắc phục file excel bị lỗi không mở được Một số lưu ý để tránh những sự cố bị mất file Có hai cách lấy lại file excel chưa lưu mà thoát là dùng tính năng AutoSave và AutoRecover. Điều đặc biệt là chúng được tích hợp sẵn trong Excel, không cần phải tải thêm phần mềm về máy, giúp tiết kiệm dung lượng bộ nhớ và giảm nguy cơ máy tính bị nhiễm virus. Cả hai tính năng này đều rất hữu ích để đề phòng việc mất dữ liệu không mong muốn. Tuy nhiên, để tránh mất dữ liệu quan trọng hoặc tốn công sức phục hồi, có một số điều bạn cần lưu ý: Mặc dù có khả năng khôi phục lại nếu gặp sự cố, nhưng tình trạng khôi phục có thể khác nhau tùy từng tài liệu. Có những file có thể khôi phục được toàn bộ, nhưng cũng có thể chỉ khôi phục được một phần, và đôi khi không đạt được kết quả như mong muốn. Do đó, bạn cần phải cẩn thận hơn khi làm việc trên Excel để hạn chế việc gây ra các lỗi hoặc mất file do các thao tác không mong muốn. Cách hiệu quả nhất để lấy lại file Excel chưa lưu hoặc bị ghi đè là sử dụng tính năng này của Microsoft Excel. Chương trình sẽ hiển thị danh sách các phiên bản dựa trên khoảng thời gian bạn đã lưu hoặc tự động lưu. Vì vậy, để khôi phục đúng phiên bản bạn cần, hãy nhớ chính xác khoảng thời gian bạn đã thực hiện các chỉnh sửa. Bạn cũng có thể lưu tệp dữ liệu bạn đang làm việc trên các dịch vụ lưu trữ đám mây như Dropbox, OneDrive, hoặc Google Drive. Với các công cụ này, bạn có thể dễ dàng xem lịch sử các phiên bản và khôi phục hoặc tải xuống phiên bản cụ thể một cách tiện lợi. Những lưu ý cần biết để tránh sự cố bị mất file Kết luận Mong rằng với những chia sẻ về cách lấy lại file excel chưa lưu mà Unica vừa chia sẻ đã giúp bạn hiểu rõ cách làm. Với những chia sẻ hữu ích này, trong quá trình làm việc với excel nếu chưa kịp lưu mà chẳng may xảy ra sự cố thì bạn cũng không còn phải lo lắng sợ mất file nữa. Chúc bạn thực hiện thao tác khôi phục file Excel chưa lưu thành công!

17181 Lượt xem

«
43
44
45
46
47
48
»

247 Cầu Giấy, TP.Hà Nội

Hotline 1: 19001568

Hotline 2: 090 488 6095

Email: cskh@unica.vn

08h00 - 17h30, thứ 2 - thứ 7

Về Unica

Hợp tác

Mobile

Công ty cổ phần đào tạo trực tuyến Unica - Số 247 Cầu Giấy, P. Dịch Vọng, Q. Cầu Giấy, TP Hà Nội.

Đại Diện: Ông Nguyễn Trọng Thơ

Mã số thuế: 0107695756 cấp tại Phòng đăng ký kinh doanh Sở Kế hoạch và Đầu tư Thành phố Hà Nội

Tiếng Việt