ادغام شیت ها

Collapse
X
 
  • زمان
  • نمایش
حذف همه
new posts
  • alireza123456

    • 2016/07/26
    • 22

    ادغام شیت ها

    سلام خدمت اساتید
    چگونه می توان داده های میزان درآمد شرکت های مختلف را که در چند شیت آمده ، در یک شیت جای داد در حالیکه اسامی یک شرکت می تواند در یک شیت تکراری باشد و یا در شیت دیگری نباشد.

    با توجه به حجم زیاد داده ها انجام این کار با ادغام kutools و منوهای اکسل بسیار زمانبر است شاید دوستانی که با کدنویسی آشنا هستند بتوانند کمک کنند

    با سپاس
    فایل های پیوست شده
  • mohammad_ashooryan

    • 2014/08/13
    • 480
    • 80.00

    #2
    سلام
    با روشی که توی لینک زیر توضیح داده شده اینکار رو انجام دادم.
    4 ways to create Excel pivot table from multiple sheets or tables. Power Query, formula, macros, pivot table wizard. Videos, written steps, get Excel files


    فایل پیوست زیر رو ببینید:
    R921.xlsx
    Last edited by mohammad_ashooryan; 2016/08/11, 12:11.

    کامنت

    • alireza123456

      • 2016/07/26
      • 22

      #3
      ممنونم
      اگه زحمت نیست روشی رو که ارجاع دادید روی نمونه ارسالی توضیح دهید تا مطلب روشنتر شود
      Last edited by alireza123456; 2016/08/10, 18:29.

      کامنت

      • iranweld

        • 2015/03/29
        • 3341

        #4
        با سلام

        فایل ذیل با کدنویسی تهیه گردید. ابتدا یک لیست یونیک از تمامی شماره های موجود در ستون A شیت های موجود در شیت DATA ایجاد شده و سپس جمع هر یک در جلوی آنها قید میگردد

        کد PHP:
        Private Sub CommandButton1_Click()

        Sheets("DATA").Columns("A:B").ClearContents



         Dim list1 
        As New Collection
         
          
         
         On Error Resume Next

        For Each Sheet In Worksheets

        If Sheet.Name <> "data" Then

        Z1 
        Sheet.Cells(Rows.Count"A").End(xlUp).Row


        For 1 To Z1

        list1
        .Add Sheet.Range("A" I), CStr(Sheet.Range("A" I))

        Next


        End 
        If

        Next Sheet

        Sheets
        ("data").Select

        Range
        ("B1") = "income"


        For 1 To list1.Count

        Sheets
        ("data").Range("a" I) = list1.Item(I)

        Next

        Application
        .ScreenUpdating False

        Z2 
        Sheets("DATA").Cells(Rows.Count"A").End(xlUp).Row

        For 2 To Z2

        For Each Sheet In Worksheets

        Z1 
        Sheet.Cells(Rows.Count"A").End(xlUp).Row


        For 1 To Z1

        If Sheet.Range("A" I) = Range("A" JThen


        Sum 
        Sum Sheet.Range("B" I)

        End If

        Next

        Next

        Range
        ("B" J) = Sum
        Sum 
        0

        Next


        Application
        .ScreenUpdating True

        End Sub 
        فایل های پیوست شده

        کامنت

        • alireza123456

          • 2016/07/26
          • 22

          #5
          نوشته اصلی توسط alireza123456
          ممنونم
          اگه زحمت نیست روشی رو که ارجاع دادید روی نمونه ارسالی توضیح دهید تا مطلب روشنتر شود
          ممنونم
          تونستم انجام بدم از زحمت شما تشکر می کنم

          کامنت

          • alireza123456

            • 2016/07/26
            • 22

            #6
            از شما استاد گرامی که مانند همیشه از راهنمایی بی نظیر شما استفاده کردم سپاسگزارم
            نوشته اصلی توسط iranweld
            با سلام

            فایل ذیل با کدنویسی تهیه گردید. ابتدا یک لیست یونیک از تمامی شماره های موجود در ستون A شیت های موجود در شیت DATA ایجاد شده و سپس جمع هر یک در جلوی آنها قید میگردد

            کد PHP:
            Private Sub CommandButton1_Click()

            Sheets("DATA").Columns("A:B").ClearContents



             Dim list1 
            As New Collection
             
              
             
             On Error Resume Next

            For Each Sheet In Worksheets

            If Sheet.Name <> "data" Then

            Z1 
            Sheet.Cells(Rows.Count"A").End(xlUp).Row


            For 1 To Z1

            list1
            .Add Sheet.Range("A" I), CStr(Sheet.Range("A" I))

            Next


            End 
            If

            Next Sheet

            Sheets
            ("data").Select

            Range
            ("B1") = "income"


            For 1 To list1.Count

            Sheets
            ("data").Range("a" I) = list1.Item(I)

            Next

            Application
            .ScreenUpdating False

            Z2 
            Sheets("DATA").Cells(Rows.Count"A").End(xlUp).Row

            For 2 To Z2

            For Each Sheet In Worksheets

            Z1 
            Sheet.Cells(Rows.Count"A").End(xlUp).Row


            For 1 To Z1

            If Sheet.Range("A" I) = Range("A" JThen


            Sum 
            Sum Sheet.Range("B" I)

            End If

            Next

            Next

            Range
            ("B" J) = Sum
            Sum 
            0

            Next


            Application
            .ScreenUpdating True

            End Sub 
            Last edited by alireza123456; 2016/08/10, 19:07.

            کامنت

            • alireza123456

              • 2016/07/26
              • 22

              #7
              با تشکر
              از آنجا که برخی از شیت ها ماهانه و برخی سالانه است آیا می توان کدها را به گونه ای تغییر داد که سلول های شیت های ماهانه در 12 ضرب و سپس با شیت های سالانه جمع شوند
              به طور مثال 3 شیت اول ماهانه و بقیه سالانه باشند
              Last edited by alireza123456; 2016/08/10, 19:09.

              کامنت

              • iranweld

                • 2015/03/29
                • 3341

                #8
                نحوه تشخیص شیت ماهانه و سالانه چگونه هست؟

                کامنت

                • alireza123456

                  • 2016/07/26
                  • 22

                  #9
                  با فرض اینکه همه شیت ها غیر از شیت آخر ماهانه است

                  کامنت

                  • iranweld

                    • 2015/03/29
                    • 3341

                    #10
                    برای اینکه شیت ماهانه قابل تشخیص باشد در هر شیت ماهانه در سلول c1 حرف M را قرار دهید

                    کد PHP:
                    For 1 To Z1

                    If Sheet.Range("A" I) = Range("A" J) And LCase(Sheet.Range("C1")) = "m" Then

                    Sum 
                    Sum Sheet.Range("B" I) * 12

                    ElseIf Sheet.Range("A" I) = Range("A" JThen

                    Sum 
                    Sum Sheet.Range("B" I)

                    End If 
                    فایل های پیوست شده

                    کامنت

                    • iranweld

                      • 2015/03/29
                      • 3341

                      #11
                      روش دیگر:
                      تجمیع شیت ها در یک شیت و گزارشگیری با پیوت تیبل که این روش از سرعت بالاتری برخوردار است

                      کد PHP:
                      Private Sub CommandButton1_Click()

                      Sheets("DATA").Columns("A:C").ClearContents



                       
                      For Each Sheet In Worksheets

                      If Sheet.Name <> "data" Then

                      z1 
                      Sheet.Cells(Rows.Count"A").End(xlUp).Row

                      z2 
                      Sheets("DATA").Cells(Rows.Count"A").End(xlUp).Row 1

                      Sheet
                      .Range("A2:B" z1).Copy Destination:=Sheets("DATA").Range("B" z2)

                      For 
                      z2 To z2 z1 2

                      Range
                      ("A" i) = Sheet.Name

                      Next

                      End 
                      If

                      Next Sheet

                      Sheets
                      ("data").Select

                      Range
                      ("a1") = "Sheet.name"

                      Range("b1") = "address"

                      Range("c1") = "income"

                       
                      ActiveWorkbook.RefreshAll

                      Application
                      .ScreenUpdating True

                      End Sub 
                      فایل های پیوست شده
                      Last edited by iranweld; 2016/08/13, 13:53.

                      کامنت

                      • alireza123456

                        • 2016/07/26
                        • 22

                        #12
                        سلام خیلی متشکرم
                        با توجه به اینکه در برخی شیت ها بیش از 500000 ورودی دارم این ماکرو در انجام عملیات کمک بیشتری می کند
                        در سوالات قبلی نیز علیرغم اجرایی بودن ماکروها حجم بالای داده ها امکان استفاده از آنها را محدود می کرد.

                        موفق باشید

                        کامنت

                        چند لحظه..