تحويل البيانات من افقي إلى عمودي بشرط

[Abu Rafat]

السلام عليكم ورحمة الله وبركاته

رمضان مبارك أخواني وتقبل الله صالح أعمالكم

عندي ملف لموظفين يحتوي على رواتبهم لثلاثة أشهر وتم ترتيبهم بحسب الأحرف الأبجدية على سبيل المثال

م	الاسم	الشهر	الراتب
1	محمد سعيد	يناير	700
2	محمد سعيد	فبراير	900
3	محمد سعيد	مارس	1000
4	احمد صالح	يناير	950
5	احمد صالح	فبراير	450
6	احمد صالح	مارس	600

 

والمطلوب ترتيبهم بالشكل التالي:

م	الاسم	يناير	فبراير	مارس
1	محمد سعيد	700	900	1000
2	احمد صالح	950	450	600
3	عبدالرحمن علي	700	600	300

هل هناك طريقة لان الموظفين اكثر من 100 واليدوي متعب جدا.. وجزاكم الله خير

 

المعذرة اخواني لم استطع اضافة المرفق حيث وعندما اضغط لاختيار ملف لاتظهر النافذة

 

 

تم تعديل أبريل 10, 2023 بواسطه Abu Rafat

[محي الدين ابو البشر]

RAFAT.xlsm

[محمد حسن المحمد]

السلام عليكم ورحمة الله وبركاته

إن أذن لي أخي الكريم @محي الدين ابو البشر لإثراء الموضوع

قمت بحله بطريقتين :

• طريقة الجداول المحورية
• دمج الجداول المحورية بالمعادلات في جدول 

كلتيهما بحاجة تحديث فقط .

أرجو لكم جميعاً التوفيق والسداد

والسلام عليكم

 

 

Abu Rafat.xlsx

[lionheart]

Try

Sub Test()
    Dim lr As Long
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        ConvertData .Range("A1:D" & lr), .Range("H1")
    End With
End Sub

Public Sub ConvertData(ByVal sourceRange As Range, ByVal targetCell As Range)
    Const NAME_COL As Long = 2, MONTH_COL As Long = 3
    Dim vName, vMonth, outputRange As Range, dicName As Object, dicMonth As Object, i As Long
    Set dicName = CreateObject("Scripting.Dictionary")
    Set dicMonth = CreateObject("Scripting.Dictionary")
    For i = 2 To sourceRange.Rows.Count
        If Not dicName.Exists(sourceRange(i, NAME_COL).Value) Then
            dicName.Add sourceRange(i, NAME_COL).Value, dicName.Count + 1
        End If
        If Not dicMonth.Exists(sourceRange(i, MONTH_COL).Value) Then
            dicMonth.Add sourceRange(i, MONTH_COL).Value, dicMonth.Count + 1
        End If
    Next i
    Set outputRange = targetCell.Resize(dicName.Count + 1, dicMonth.Count + 2)
    outputRange.Cells(1, 1).Value = "S"
    outputRange.Cells(1, 2).Value = "Name"
    For Each vMonth In dicMonth.Keys
        outputRange.Cells(1, dicMonth(vMonth) + 2).Value = vMonth
    Next vMonth
    For Each vName In dicName.Keys
        outputRange.Cells(dicName(vName) + 1, 1).Value = dicName(vName)
        outputRange.Cells(dicName(vName) + 1, 2).Value = vName
        For Each vMonth In dicMonth.Keys
            For i = 2 To sourceRange.Rows.Count
                If sourceRange(i, NAME_COL).Value = vName And sourceRange(i, MONTH_COL).Value = vMonth Then
                    outputRange.Cells(dicName(vName) + 1, dicMonth(vMonth) + 2).Value = sourceRange(i, 4).Value
                    Exit For
                End If
            Next i
        Next vMonth
    Next vName
End Sub

 

[بلانك]

استاذي ليون هارت لماذا تبدأ الشهور بداية من فبراير بدلا من يناير؟؟؟؟؟؟؟؟؟؟؟؟

تم تعديل أبريل 10, 2023 بواسطه بلانك

[lionheart]

Because the code extracts the unique values from column c regardless the order of the months. After the execution of the code you can cut the columns and reorder the results according to your needs

[بلانك]

Thanks for the clarification

[Abu Rafat]

اساتذتي الافاضل..
بيض الله وجوهكم على تفاعلكم.. المعذرة على تأخري بالرد لانقطاع الانترنت


استاذي العزيز محي الدين..
جزاك الله خير.. وجعله في ميزان حسناتك.. سأقوم بالتحربة


استاذي الكريم محمد حسن المحمد..
جزاك الله خير على التحليل واثراءك الأنيق.. ساقوم بالتحربة ايضا..

استاذي الفاضل ليون..
جزاك الله خير ايضا على الاضافة الراقية.. ساقوم بالتجربة كذلك

[Abu Rafat]

في 10‏/4‏/2023 at 15:01, محي الدين ابو البشر said:

RAFAT.xlsm 25.6 kB · 8 downloads

أستاذي العزيز محي الدين...

بعد التحربة وجد ان عند اضافة الاشهر الثلاثة اللاحقة لمارس لاتظهر ويتم اضافة القيم على انها الأشهر الثلاثة الأولى

في 10‏/4‏/2023 at 15:28, محمد حسن المحمد said:

السلام عليكم ورحمة الله وبركاته

إن أذن لي أخي الكريم @محي الدين ابو البشر لإثراء الموضوع

قمت بحله بطريقتين :

• طريقة الجداول المحورية
• دمج الجداول المحورية بالمعادلات في جدول 

كلتيهما بحاجة تحديث فقط .

أرجو لكم جميعاً التوفيق والسداد

والسلام عليكم

 

 

Abu Rafat.xlsx 29.7 kB · 6 downloads

أستاذي الكريم محمد حسن المحمد...

بعد التجربة وجدت انها أنيقة التعامل ولا تحتاج للVBA  وعند اضافة الاشهر الثلاثة اللاحقة تظهر بشكل اعتيادي عكس نموذج الأستاذ العزيز محي الدين

في 10‏/4‏/2023 at 15:41, lionheart said:

Try

Sub Test()
    Dim lr As Long
    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        ConvertData .Range("A1:D" & lr), .Range("H1")
    End With
End Sub

Public Sub ConvertData(ByVal sourceRange As Range, ByVal targetCell As Range)
    Const NAME_COL As Long = 2, MONTH_COL As Long = 3
    Dim vName, vMonth, outputRange As Range, dicName As Object, dicMonth As Object, i As Long
    Set dicName = CreateObject("Scripting.Dictionary")
    Set dicMonth = CreateObject("Scripting.Dictionary")
    For i = 2 To sourceRange.Rows.Count
        If Not dicName.Exists(sourceRange(i, NAME_COL).Value) Then
            dicName.Add sourceRange(i, NAME_COL).Value, dicName.Count + 1
        End If
        If Not dicMonth.Exists(sourceRange(i, MONTH_COL).Value) Then
            dicMonth.Add sourceRange(i, MONTH_COL).Value, dicMonth.Count + 1
        End If
    Next i
    Set outputRange = targetCell.Resize(dicName.Count + 1, dicMonth.Count + 2)
    outputRange.Cells(1, 1).Value = "S"
    outputRange.Cells(1, 2).Value = "Name"
    For Each vMonth In dicMonth.Keys
        outputRange.Cells(1, dicMonth(vMonth) + 2).Value = vMonth
    Next vMonth
    For Each vName In dicName.Keys
        outputRange.Cells(dicName(vName) + 1, 1).Value = dicName(vName)
        outputRange.Cells(dicName(vName) + 1, 2).Value = vName
        For Each vMonth In dicMonth.Keys
            For i = 2 To sourceRange.Rows.Count
                If sourceRange(i, NAME_COL).Value = vName And sourceRange(i, MONTH_COL).Value = vMonth Then
                    outputRange.Cells(dicName(vName) + 1, dicMonth(vMonth) + 2).Value = sourceRange(i, 4).Value
                    Exit For
                End If
            Next i
        Next vMonth
    Next vName
End Sub

 

أستاذي العزيز ليون...

بعد التجربة وجدت انها رائعة والتعامل معاها اسهل وعند اضافة الاشهر الثلاثة اللاحقة تظهر دون مشاكل..

 

 

بيض الله وجيهكم وكتب لكم الأجر.. جميع المشاركات تؤدي إلى نفس النتيجة لكن هنا اختلفت الأفكار واللمسات والنتيجة ذاتها..

وإن سمحتم لي بعد اذنكم عندي اضافة بسيطة.. النموذج السابق كان عبارة عن مبلغ الخصم امام كل شهر.. علما ان لكل موظف راتب محدد وبحسب الكفاءة يتم زيادة الراتب.. هل استطيع اضافة عمود لمعرفة كم نسبة الخصم% اعتمادا على مجموع الراتب لثلاثة أشهر.. مثال:

م	الراتب	الاسم	الشهر	الخصم
1	1000	محمد سعيد	يناير	700
2	1100	محمد سعيد	فبراير	900
3	1000	محمد سعيد	مارس	1000
4	1200	احمد صالح	يناير	950
5	1800	احمد صالح	فبراير	450
6	1000	احمد صالح	مارس	600
7	1000	عبدالرحمن علي	يناير	700
8	1000	عبدالرحمن علي	فبراير	600
9	1000	عبدالرحمن علي	مارس	300

والنتيجة

م	الاسم	يناير	فبراير	مارس	نسبة الخصم
1	محمد سعيد	700	900	1000
2	احمد صالح	950	450	600
3	عبدالرحمن علي	700	600	300

مثال الموظف الأول محمد سعيد إجمالي رواتبه لثلاثة أشهر 3100 ومبلغ الخصم لثلاثة أشهر 2600 من المفترض نسبة الخصم تتعدى 80%.. هل في إمكانية لعمل ذلك؟

وجزاكم الله خير وجعله في ميزان حسناتكم

 

 

 

 

 

 

تم تعديل أبريل 12, 2023 بواسطه Abu Rafat
تجربة النماذج واضافة الملاحظات

[محمد حسن المحمد]

السلام عليكم

ولكم بمثل ما دعوتم آمين

تفضل أخي الكريم 

تم التعديل على الجداول دون Pivot Table

تقبل تحياتي

 

 

Abu Rafat.xlsx

تم تعديل أبريل 12, 2023 بواسطه محمد حسن المحمد

[Abu Rafat]

18 ساعات مضت, محمد حسن المحمد said:

السلام عليكم

ولكم بمثل ما دعوتم آمين

تفضل أخي الكريم 

تم التعديل على الجداول دون Pivot Table

تقبل تحياتي

 

 

Abu Rafat.xlsx 313.88 kB · 15 downloads

بيض الله وجهك ووجيه كل الأساتذة وجعله في ميزان حسناتكم..

[محمد حسن المحمد]

الحمد لله الذي بنعمته تتم الصالحات

[Abu Rafat]

عيدكم مبارك مؤخراً وكل عام والأساتذة بصحة وسلامة..

وعطفا على مساعداتكم السابقة - جزاكم الله خيرا - واخص الأستاذ محمد حسن الاحمد.. ولأني مبتدئ ولم استطع التعديل على النماذج السابقة.. تكرموا مشكورين بعد اذنكم إضافة شيء بسيط وأرجوا ألا أكون قد اثقلت عليكم  وهو التالي:

1- القسم: حيث وأن هناك اقسام مختلفة.. ويكون ترتيبه قبل الأسم.

2- الرقم الوظيفي: حيث وأن هناك رقم مميز لكل موظف... ويكون ترتيبه بعد القسم.

3- إجمالي الراتب: اجمالي راتب 3 أشهر لكل موظف... ويكون ترتيبه بعد الاسم.

وبالتالي يكون ترتيب الأعمدة كالتالي: (م - القسم - الرقم الوظيفي - الاسم - اجمالي الراتب - خصم الأشهر الثلاثة في كل عمود منفصل - إجمالي الخصم - نسبة الخصم %).

 

جزاكم الله وجعله في ميزان حسناتكم

تم تعديل يوليو 7, 2023 بواسطه Abu Rafat

[محمد حسن المحمد]

السلام عليكم أخي الكريم أبو رأفت

عساكم من عواده، أسأل الله تعالى أن يتقبل منا ومنكم صالح الأعمال،كل عام وأنتم إلى الله تعالى أقرب ...آمين

أما بعد: لم توضح أخي الكريم شكل النتائج المتوقعة وفي أي ورقة تريد التعديل، وبما أنك لم تحدد ذلك فإنني

قمت بإضافة شيت - سمه ما شئت - تضع فيه القسم ورقم الموظف واسمه لمرة واحدة 

وفي شيت النتائج قمت بالتعديل وليس كل ما طلبت، حيث أنك طلبت خصم الأشهر الثلاثة يمكن إضافة ذلك حسب أرباع السنة

وفيما يلي أعرض لك نتيجة ما قمت به ضمن الملف المرفق:

 

تقبل تحياتي

Abu Rafat.xlsx

تم تعديل يوليو 8, 2023 بواسطه محمد حسن المحمد

[Abu Rafat]

اعتذر لعدم التوضيح.. كنت ارغب في اضافة القسم والرقم الوظيفي في مصنف البيانات الاولية ويظهر كذلك في النتيجة مع اضافة عمود لاجمالي الراتب في مصنف النتيجة.. ساقوم بتجرية مقترحاتك استاذي الكريم محمد حسن وابلغك.. شاكر ومثمن جهودك سلفا وجعلها الله في ميزان حسناتك

[Abu Rafat]

جزاك الله خيرا استاذي الكريم... والملاحظات كالتالي: الاعمدة الموجودة في المسنف Data1 وهي القسم والرقم الوظيفي اريد دمجها في مصنف Data بجانب الاعمدة الحالية... في مصنف Results هناك خطا في الاعمدة يناير وفبراير ومارس من المفترض اظهار الخصم وليس الراتب.. ويكون ترتيب الاعمدة على النحو التالي:

م - القسم - الرقم الوظيفي - الاسم - اجمالي الراتب - خصم يناير - خصم فبراير - خصم مارس - إجمالي الخصم (للأشهر الثلاثة) - نسبة الخصم %.

وكتبه الله في ميزان حسناتك ووفقك

تم تعديل يوليو 8, 2023 بواسطه Abu Rafat

[محمد حسن المحمد]

السلام عليكم أخي الكريم ...... ولكم بمثل ما دعوتم آمين

بناء على طلبك - أخي الكريم - تم التعديل،

مع ملاحظة أن الورقتين Data & Result تعتمدان في معرفة القسم والرقم الوظيفي على الورقة الأولى Data1

وكذلك يمكنك التحديث لكتابة الأسماء آلياً بتحديث Pivot table بجانب الجدول في ورقة Result 

أما إن أردت كتابة الاسم يدوياً ستقوم بإلغاء المعادلة التي تربط خلايا الاسم بالجدول المجاور لكن لا يضمن أن تكتب الاسم بحذافيره دون زيادة أو نقصان فتكون النتائج تابعة

لدقة كتابتك للاسم

تقبل تحياتي والسلام عليكم.

 

Abu Rafat (1).xlsx

[Abu Rafat]

نعم.. الاسماء ربما تصادف مشكلة... اذن المرحلة الاولى اقوم باضافة القسم والرقم الوظيفي والاسم في مصنف data1 وبعد ذلك اعيد ادخال القسم والرقم الوظيفي والاسم والشهر والراتب والخصم في data... 

جزا الله  خير وجعله في ميزان حسناتك

[محمد حسن المحمد]

أخي الكريم عندما تدخل الاسم في Data ستتم كتابة الرقم الوظيفي والقسم آلياُ ...

وجزاكم بمثل ما دعوتم أخي الكريم