Bài viết: Chuyển đổi dữ liệu dạng ma trận (ngang dọc) thành dạng phẳng

[kyo]

Chuyển đổi dữ liệu dạng ma trận (ngang dọc) thành dạng phẳng


Sẽ có những lúc bạn cảm thấy bức bối vì dữ liệu của bạn đang thể hiện dạng ma trận, còn bạn lại muốn chuyển nó thành dạng phẳng (với dòng là các thông tin và cột là các trường dữ liệu như cơ sở dữ liệu). Để dễ hình dung, bạn có thể xem sự khác biệt của 2 loại dữ liệu đó ở đây:

Như hình, với dữ liệu kiểu ma trận, nó trông có vẻ dễ dàng trong việc tra cứu, tìm kiếm bằng mắt thường và kiểu phân bố này thường thấy trong các file về kế hoạch, chẳng hạn kế hoạch sản xuất, kế hoạch nhân sự, kế hoạch đào tạo,… Tuy nhiên, vấn đề là, khi bạn cần xử lý dữ liệu khối lớn hoặc lọc theo điều kiện phức tạp, kiểu dữ liệu này bỗng dưng trở thành một cơn ác mộng khi nó khiến bạn phải lao tâm suy nghĩ về một công thức phức tạp hơn hoặc thậm chí phải dùng VBA để đáp ứng nhu cầu của bạn.

Còn với dữ liệu dạng phẳng như hình dưới, nó trông có vẻ khá khó chịu nếu bạn nhìn (khi bạn không quen) nhưng nó lại là kiểu dữ liệu chuẩn dùng để tra cứu, thiết lập các công thức,… bởi vì Excel xử lý dữ liệu chủ yếu là theo hàng dọc (và đây cũng là dạng dữ liệu chuẩn trong tổ chức cơ sở dữ liệu).

Vậy làm cách nào để chuyển? Sẽ có rất nhiều cách giải quyết vấn đề này, từ việc dùng công thức, dùng công cụ Excel cho đến dùng VBA, tuy nhiên kyo xin giới thiệu đến bạn một công cụ cực kỳ đơn giản nhưng giúp bạn giải quyết vấn đề này rất nhanh chóng.

Giả sử bạn có dữ liệu như sau:

Bây giờ, để chuyển nó, trước hết bạn cần sử dụng chức năng Wizard của PivotTable (dùng tổ hợp phím Alt + D + P) -> chọn Multiple Consolidation Ranges -> Next.

Tiếp theo bạn chọn I will create the page fields -> Next.

Sau đó bạn quét chọn bảng dữ liệu của bạn -> Next.

Và cuối cùng bạn chọn chỗ cho PivotTable mới xuất hiện (tại một sheet mới hoặc tại một ô được chỉ định nào đó) -> Finish. Và dữ liệu của bạn sẽ tự động chuyển thành PivotTable.

Bây giờ dữ liệu của bạn đã là PivotTable rồi, bạn chỉ việc tìm đến ô cuối cùng góc phải của PivotTable (cũng là ô Sum Total của cả dòng lẫn cột) -> nhấp chuột hai lần để hiện dữ liệu của toàn PivotTable, và bạn sẽ có được dữ liệu như sau:

Rất dễ phải không? Bây giờ dữ liệu của bạn đã chuyển sang dạng phẳng rồi.

Trong trường hợp bạn không nhớ tổ hợp phím trên, bạn có thể tìm thấy nút bấm để kích hoạt công cụ đó bằng cách vào thẻ File -> Option -> Chọn Quick Access Toolbar -> Chọn Commands Not in the Ribbon -> Tìm PivotTable and PivotChart Wizard -> Add -> OK.

Và bạn sẽ thấy nút đó ở đây:

Chúc bạn thành công!

Một số bài viết có liên quan:
1/ Spreadsheet Design: TRÌNH BÀY BẢNG TÍNH
2/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
3/ Chiêu thứ 21: Tô màu dòng xen kẽ
4/ Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting
5/ Sử dụng định dạng có điều kiện (Conditional Formatting) để làm nổi bật ngày tháng trong Excel
6/ Chiêu số 18: Điều khiển Conditional Formating bằng checkbox
7/ Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating
8/ Chiêu thứ 8: Giới hạn vùng cuộn của bảng tính
9/ Ebook: Dữ liệu & Báo cáo trong Excel 2013
10/ Phân tích tồn kho theo phương pháp ABC

 

[Tieu Uyen]

Bài Hay, cảm ơn anh

Mến

Tiểu Uyên

 

[Đăng Trần]

chào bạn!
Bạn cho mình hỏi là có cách nào chuyển ngược lại ko bạn?
Thanks!

 

[ChanhTQ@]

chào bạn! & cho mình hỏi là có cách nào chuyển ngược lại ko bạn?

Mình thử sức với vấn đề bạn nêu theo fương thức FIND() & kết quả được đưa ra trong hình:

Option Explicit
Sub ChuyenSangDangMaTran()
 Dim Cls As Range, Rng As Range, sRng As Range, Rg0 As Range, Cll As Range
 Dim MyAdd As String
 Dim Rw As Integer, CR As Boolean, Col As Byte
 Set Rng = Range([A1], [A1].End(xlDown))
 Rw = Rng.Rows.Count
 Set Rg0 = Range("F1").Resize(, Rw)
 Rg0.Resize(9).ClearContents
 For Each Cls In Range([e2], [e2].End(xlDown))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        MyAdd = sRng.Address
        Do
            For Each Cll In Rg0
                If Cll.Value = sRng.Offset(, 1).Value Then
                    CR = True:          Exit For
                End If
            Next Cll
            If CR Then
                Cells(Cls.Row, Cll.Column).Value = sRng.Offset(, 2).Value
                CR = Not CR
            Else
                Col = Rg0(Rg0.Columns.Count).End(xlToLeft).Offset(, 1).Column
                Cells(1, Col).Value = sRng.Offset(, 1).Value
                Cells(Cls.Row, Col).Value = sRng.Offset(, 2).Value
            End If
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    End If
 Next Cls
End Sub

 

[quanluu]

chào bạn!
Bạn cho mình hỏi là có cách nào chuyển ngược lại ko bạn?
Thanks!

Làm ngược lại thì bạn lại dùng pivot thui

 

[phanvantruong]

Mình cũng đã rơi vào trường hợp như này và chuyển ngược lại từ data phẳng sang ma trận và nhập vao file theo dõi cv. Ngồi nghĩ tìm cách chuyển mãi mới nhớ ra Pivotabel làm việc này vưa nhanh lại đơn giản. Và thế là công viêcbớt overheat đi rất nhiều. Thanks