تبدیل ماتریس به جدول 3 ستونی در اکسل ( "unpivot"، "Flattin"، "normalize")

  جهت وارونه کردن عملیات Pivot در اکسل مراحل ذیل را انجام دهید تا به یک فهرست دست یابید

1.  هر سلولی را در جدول خلاصه خود فعال کنید و Data - PivotTable و PivotChart Report را انتخاب کنید:

For later versions access the Wizard with Alt+D, P.

For Excel for Mac 2011, it's ⌘+Alt+P (See here).

2. Select Multiple consolidation ranges and click Next.

3. In “Step 2a of 3”, choose I will create the page fields and click Next.

4. In “Step 2b of 3” specify your summary table range in the Range field (A1:E5 for the sample data) and click Add, then Next.

5. In “Step 3 of 3”, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):

6. Click Finish to create the pivot table:

7. Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or 7):

8. The PT may now be deleted.
9. The resulting Table may be converted to a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range.