تبدیل لیست دوبعدی به لیست یک بعدی در اکسل

آپدیت بهمن ۱۴۰۱: 
کدهای نوشته شده در این مقاله دیگر کاربردی ندارد زیرا این کار یعنی تبدیل لیست ۲ بعدی به ۱ بعدی توسط ابزار پاورکوئری به سادگی و با فرمان UNPIVOT قابل انجام است. از آن جهت این مقاله حذف نشده است، تا خوانندگان دریابند که چطور پاورکوئری با چند کلیک ساده می‌تواند یکی از از چالش‌های اکسلی -که جز با برنامه نویسی (و یا فرمول نویسی بسیار پیچیده) قابل حل نبوده - با سرعت انجام دهد.

البته کماکان در این مثال از کد نویسی VBA شما با حلقه‌های تو در تو آشنا خواهید شد و حل این مسئله برای یادگیری و تسلط بر کدنویسی زبان VBA کماکان توصیه می‌شود.

بگذارید از اول بگویم !

در یکی از روزهای سال ۱۳۹۲ آقای محمد رضا به من زنگ زد و گفت که یک فایل دارند که می‌خواهند از آن گزارش بگیرند و نمی‌شود. بیشتر که توضیح دادند متوجه شدم که فایل آن ها به شکل 2 بعدی تایپ شده است.

در واقع برای ساده تر شدن در تایپ ایشان یک ستون را اسامی (ستون A) و یک سطر هم نام پروژه ها را نوشته بودند (سطر 1) و اعداد نشان دهنده یک چیزی بوده است (مثلا بودجه که صرف شده است یا زمان) و حال می خواستند که انواع گزارش های مدیریتی را با ابزار Pivot Table در Excel بسازند.

مثلا چه کسانی در پروژه A شرکت کرده اند؟ و جمع بودجه فرید و فرشید در پروژه A و D کلا چقدر بوده است.

چند درصد از کل هزینه ، به هر پروژه اختصاص یافته است و ده ها گزارش دیگر که تمامی آنها را با چند کلیک می شود با Excel و ابزار Pivot Table تهیه کرد به شرطی اینکه داده ها به شکل ستونی (شکل زیر وارد شده باشند) و از شکل بالا بسیاری از گزارش ها با Pivot قابل ساختن نیست.

واقعیت آن است که اکثر کاربران ترجیح می دهند که اطلاعات را به صورت 2 بعدی (یعنی شکل اول) تایپ کنند ، اما در دنیای کامپیوتر اطلاعات به صورت لیست (شکل دوم) کاربرد دارد و ابزارهای بر روی این لیست ها کار می کنند.

حال برای تبدیل لیست اولی( 2 بعدی) به لیست دومی، چند راه وجود دارد که ساده ترین آن کد نویسی در اکسل است که البته همه بلد نیستند و من در اینجا برای شما کد آنرا نوشته ام که به راحتی بتوانید اینکار را انجام دهید.

1- فایل پیوست را از انتهای همین صفحه دانلود کنید.

2-  ابتدا ماکروی فایل را فعال کنید و سپس با زدن کلید ALT+F8 ماکروی Convert2D را اجرا کنید.

اگر با مفاهیم ماکرو آشنا نیستید، در ابتدا حتما مقاله "چگونه ماکروی یک فایل Excel را فعال کنیم" را بخوانید.

در این برنامه، یک پنجره به شما نشان داده می‌شود که باید لیست خود را انتخاب کنید و بلافاصله این لیست دو بعدی به یک لیست یک بعدی در اکسل تبدیل می شود.

Sub ConvertDATA()

' Author: Farshid Meidani
'   Site: www.farsaran.com
'  Email: f.meidani@farsaran.com

Dim rg As Range
Dim sh As Worksheet
Dim r As Long
Dim c As Long
Dim i As Long

    Set rg = Application.InputBox("Select Your DATA range .", , , , , , , 8)
    Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
    
    For r = 2 To rg.Rows.Count
        For c = 2 To rg.Columns.Count
        If rg.Cells(r, c) <> "" Then
            i = i + 1
            With sh
                .Cells(i, 1) = rg.Cells(r, 1)
                .Cells(i, 2) = rg.Cells(1, c)
                .Cells(i, 3) = rg.Cells(r, c)
            End With
         End If
        Next c
    Next r
    
End Sub