Cách sử dụng Consolidate trong Excel để gộp dữ liệu trùng lặp

Cách sử dụng Consolidate trong Excel để gộp dữ liệu trùng lặp

  1. 1. Giới thiệu về  Consolidate

Consolidate cũng là một công cụ gộp dữ liệu trùng lặp trong bảng tính. Tính năng giống với Remove Duplicates. Tuy nhiên, công cụ Consolidate ưu việt hơn ở chỗ, nó có thể tạo ra một báo cáo tổng quát theo trường dữ liệu.

  1. 2. Cách sử dụng Consolidate

Để hiểu rõ hơn cách dùng và những ứng dụng của nó, chúng ta cùng đi tìm hiểu nó ngay sau đây.

Dưới đây là một phần bảng dữ liệu báo cáo bán hàng. Bảng này được xuất ra từ phần mềm kế toán Fast Accounting.

consolidate-1

Trên bảng dữ liệu này, chúng ta thấy mã vật tư và tên vật tư được lặp lại nhiều lần. Yêu cầu quản lý cần chúng ta cung cấp thông tin về số lượng và doanh thu bán hàng của từng mặt hàng. Chúng ta sẽ rất mất thời gian nếu làm thủ công.

Thay vào đó, chúng ta có một công cụ mạnh giúp tính tổng nhanh và loại bỏ các giá trị trùng lặp. Công cụ này giúp loại bỏ giá trị trùng trong bảng tính theo cột và sử dụng luôn các giá trị tại cột đầu tiên của vùng thao tác làm đầu hàng – Công cụ Consolidate trong excel. Mời các bạn xem các bước làm có hình ảnh minh họa để hiểu cách dùng của công cụ này.

Bước 1: Đầu tiên, ta chọn một vị trí mới để tạo báo cáo. Vị trí mới này có thể nằm ngay trong sheet hiện tại hoặc nằm ở sheet khác.

Chúng ta cũng có thể đặt tên cho các trường dữ liệu (các vị trí ở đầu cột) để tạo một báo cáo mới. (Nếu không đặt tên, excel Consolidate sẽ tự lấy các đầu cột tại vùng dữ liệu được chọn để làm tên các trường – đầu cột)

Bước 2: Tiếp theo, ta vào thẻ Data trên thanh Ribbon => Tìm đến mục Data Tools và chọn Consolidate. Cửa sổ Consolidate sẽ hiện ra như sau:

consolidate-2

Bước 3: Kế tiếp đến việc chọn Function (Chức năng) phù hợp với yêu cầu công việc. Ở đây chúng ta cần tính tổng số lượng và doanh thu bán hàng theo từng mặt hàng nên ta chọn Function là Sum (Chức năng tính tổng). Ngoài ra, Consolidate còn có các tính năng sau:
  1. Sum: Tính tổng
  2. Count: đếm tất cả các cột
  3. Average: tính trung bình cộng
  4. Max: lấy giá trị lớn nhất
  5. Min: lấy giá trị nhỏ nhất
  6. Product: tính tích
  7. Count Numbers: chỉ đếm những cột có giá trị là số
  8. StdDev: Ước tính độ lệch chuẩn của mẫu
  9. StdDevp: Tính độ lệch chuẩn của toàn bộ tập hợp
  10. Var: Tính phương sai trên mẫu
  11. Varp: Tính phương sai trên toàn bộ tập hợp
Bước 4: Tại khu vực Referrence (tham chiếu), các bạn thấy một ô vuông nhỏ, có mũi tên đỏ bên trong chính là khu vực ta cần đặt địa chỉ vùng dữ liệu tham chiếu hay vùng dữ liệu cần xử lý với Consolidate vào đó.

Ta có thể điền trực tiếp địa chỉ của vùng tham chiếu vào khu vực tham chiếu này hoặc click vào ô vuông nhỏ đó và chọn vùng tham chiếu trên bảng tính. Nếu click chuột vào ô vuông nhỏ này, excel sẽ thu gọn cả cửa sổ lại và bạn có thể di chuyển con trỏ chuột tới vùng cần tính:

consolidate-3

Ở đây, chúng ta cần tính tổng số lượng và doanh thu bán hàng theo mặt hàng, nên vùng được chọn sẽ là G1:L31. (Chính là vùng trong viền đỏ như hình  dưới)

consolidate-4

Vùng này phải thỏa mãn 2 điều kiện:

  • Một là cột đầu tiên trong vùng phải chứa tên hàng (Chứa tiêu đề) và chính là vùng chứa giá trị bị lặp để gộp. (Nếu sử dụng Remove Duplicates, chúng ta có thể gộp theo bất cứ cột nào mình muốn thì với Consolidate, nhất định cột được gộp dữ liệu trùng lặp phải là cột đầu tiên của vùng tham chiếu hay vùng dữ liệu cần xử lý)
  • Hai là các cột tiếp theo chứa giá trị cần tính toán sau khi gộp dữ liệu bị trùng.

Có một lưu ý:

Nếu vùng dữ liệu của bạn ở một file khác, bạn có thể sử dụng tính năng Browse… ngay bên cạnh hộp Reference, nhưng bạn phải nhớ được địa chỉ của vùng tham chiếu nằm ở sheet nào, từ ô nào tới ô nào trong file dữ liệu đó.

Bước 5:  Sau khi bạn chọn được vùng tham chiếu, bấm vào ô vuông cũ, cửa sổ Consolidate lại hiện ra và bạn bấm Add để đặt vùng dữ liệu này vào trong hộp thoại All References như hình dưới.

consolidate-5

Nếu muốn thay đổi, bạn chọn vùng tham chiếu rồi bấm Delete. Ở đây bạn có thể chọn nhiều vùng dữ liệu và Add vào hộp thoại All references.

Bước 6: Tại khu vực Use labels in (Sử dụng nhãn – đề mục, chính là khu vực được đóng khung màu xanh ở hình trên). Tại đây ta có các lựa chọn:
  • Top row: sử dụng chính đề mục của vùng dữ liệu tham chiếu để làm nhãn và phân chia việc tính toán theo các đầu cột này. Nếu bỏ mục này thì đầu cột sẽ trắng trơn.
  • Left column: Sử dụng chính phần dữ liệu được cộng gộp ở cột đầu tiên trong bảng tham chiếu để làm đầu dòng. Nếu bỏ chọn mục này, thì đầu dòng sẽ trắng trơn. (Ở bài này chính là cột tên hàng)
  • Creat links to source data: Tạo liên kết tới dữ liệu nguồn
Bước 7: Bước này đơn giản nhất, bấm OK và xem kết quả thu được như hình dưới.

consolidate-6

Thật đơn giản phải không nào. Như vậy chúng ta đã cùng tìm hiểu về cách sử dụng công cụ Consolidate trong excel để loại bỏ các giá trị trùng lặp tại và lập báo cáo tổng quát theo các giá trị còn lại sau loại bỏ.

Hi vọng sau bài viết này các bạn có thể hiểu và sử dụng tốt công cụ Consolidate để phục vụ tốt hơn công việc của mình. Đặc biệt là khi phải tạo ra một báo cáo nhanh trong trường hợp chỉ có một bảng nhật ký như trong ví dụ trên.

    1. Thu Vân

    Add Your Comment

    Time limit is exhausted. Please reload the CAPTCHA.