Công thức chuyển bảng nhiều cột thành 2 cột:Unpivot

 Công thức chuyển bảng nhiều cột thành 2 cột

1.       Tạo giá trị duy nhất cho các ô nằm trong bảng, lưu ý:title của dòng phải nằm kế bên trái của header cột

2.       Tạo 1 bảng có cột Gía trị duy nhất, cột Header column và cột Header Row

3.       Cột Header Column (Ngày) điền công thức sau:

=INDEX(Sheet1!$U$1:$AF$1,SUMPRODUCT(MAX((Sheet1!$U$2:$AF$111=A2)*(COLUMN(Sheet1!$U$2:$AF$111))))-COLUMN(Sheet1!$U$1)+1)

-          Trong đó:

·       INDEX(Sheet1!$U$1:$AF$1:Vùng chứa Header column cần tìm

·       SUMPRODUCT(MAX((Sheet1!$U$2:$AF$111=A2):bảng chứa giá trị duy nhất=giá trị dò

·       (COLUMN(Sheet1!$U$2:$AF$111):bảng chứa giá trị duy nhất

·       COLUMN(Sheet1!$U$1):ô đầu tiên của vùng chứa Header column

4.       Cột Header Row (Item) điền công thức:

=INDEX(Sheet1!$T$2:$T$33,MATCH(Sheet2!A2,OFFSET(Sheet1!$T$2:$T$33,0, MATCH(B2,Sheet1!$U$1:$AF$1,0)),0))

-          Trong đó:

·       INDEX(Sheet1!$T$2:$T$33:Vùng chứa Header Row cần tìm

·       MATCH(Sheet2!A2  : giá trị dò duy nhất

·       OFFSET(Sheet1!$T$2:$T$33,0 : Vùng chứa Header Row cần tìm

·       MATCH(B2 :Gía trị Header Column

·       Sheet1!$U$1:$AF$1: Vùng chứa Header column

5.      Sau khi tìm ra Header Row VÀ Column thì dùng 2 giá trị này để Index ra giá trị số nằm trong bẳng. nếu Header Row hoặc Header Column cóa giá trị trùng thì dùng Sumproduct bảng thay vì Index