تكنوليبريان

 كان فيه سؤال لاحد الاصدقاء بيقول ؟ 

لو عندي ملف اكسيل فيه جدول ببيانات حضور وانصراف الموظفين وساعات العمل الاضافية وبيتم تكرارها كل شهر

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

بيانات الموظفين

جدول البيانات لكل موظف

هل انا مضطر اعمل نسخ لورقة العمل الخاصة ب اول موظف ثم اعادة تسمية ورقة العمل بنفس اسم الموظف من جدول الموظفين او اني اقول New Sheet  وبعدين اعمل Rename  واكتب اسم من الاسماء اللي موجودة في الجدول  وبعدين انسخ جدول بيانات الحضور والانصراف وساعات العمل ولاينفع تتعمل تلقائي باستخدام VBA 

طبعاً لو كان عدد الموظفين في الجدول كبير فطبيعي يكون الموضوع مرهق وخاصة في حالة تكراره مره كل شهر

طب والحل 

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

تعالى نشوف ياعم ايهاب 😸

الكود 

Sub wajeeh2()

Dim sh1 As Worksheet, sh2 As Worksheet, c As Range

Set sh1 = Sheets("هنا حطلي اسم ورقة العمل اللي فيها الجدول")

Set sh2 = Sheets("وهنا اسم ورقة العمل اللي فيها اسماء الموظفين")

    For Each c In sh2.Range("b2", sh2.Cells(Rows.Count, 2).End(xlUp))

        sh1.Copy After:=Sheets(Sheets.Count)

        ActiveSheet.Name = c.Value

        ActiveSheet.Range("a1").Value = ActiveSheet.Name

    Next

End Sub

عنوان الجدول عبارة عن اسم ورقة العمل

التنسيق الشرطي او الـ Conditional Formatting  الاداة التي تستخدم في تنسيق نطاق من الخلايا بناءً على شرط او مجموعة شروط يتم تحديدها من قبل المستخدم ومش بس كده لاء ده كمان بيكون شرطي يعني Variable يعني هيتغير مع تغير المحتوى على عكس التنسيق التقليدي

على سبيل المثال اذا كان لدينا جدول بيانات الموظفين كما بالشكل التالي

وأردنا اظهار مسمى الوظيفة اذا كان المسمى " مهندس "  بتنسيق محدد " لون خط احمر وسميك مع تظليل الخلية باللون الرمادي "

ومسمى الوظيفة " طبيب " يتم تنسيقه بحيث يكون لون الخط ازرق قاتم واطارة الخلية باللون الاحمر مع تعبئة بلون سماوي ….. الخ مع باقي المسميات الوظيفية

في هذه الحالة اذا كان الجدول لدينا لا يتعدى 5 تسجيلات او صفوف كما هو واضح من الشكل السابق وبالتالي عدد المسميات الوظيفية لا يتعدى الخمسة فمن السهل والممكن استخدام الطريقة التقليدية في تنسيق الخلايا وفقا لمحتوى كل خلية  ولو حبينا نستخدم التنسيق الشرطي وده افضل طبعاُ هنعمل ايييه 

حضرتك هتحدد نطاق الخلايا اللي هنعنل علية التنسيق الشرطي ومن التاب Home  هنلاقي ناحية اليمين الاداة Conditional Formatting  كما بالشكل التالي 

وهي كالتالي :

Highlight Cell Rules

وهي المجموعة الامثر استخداما وهنلاقي فيها اذا كان محتوى الخلية اكبر من او اقل من او بين رقمين او يساوي او نص يحتوي على او تاريخ محدد او القيم المكررة

Top / Bottom Rules

ودي المجموعة اللي هنلاقي فيها تنسيقات جاهزة خاصة باعلى واقل عدد من القيم كأن نقول اعلى 3 مرتبات او اعلى 5 مبيعات وهكذا 

Data Bars

هنا هنلاقي مجموعة تنسيقات متعلقة بتدرج لوني وتعبئة للخلايا وفقا للمحتوى وترتيبة بين باقي الخلايا ويمكن تطبيقها على الخلايا التي تحتوي على قيم غير نصية 

Color Scales

وفيها هنلاقي مجموعة تنسيقات خاصة بالتدرجات اللونية مثال اللون الاحمر بكل درجات الشفافية Transparency  الخاصة به ويتم تطبيقها على القيم غير النصية ايضا 

Icon Sets

بها مجموعة من الرموز التى تستخدم للتعبير عن مؤشرات الارقام من حيث الصعود والهبوط 

New Rules 

هنلاقي هنا طرق انشاء تنسيق شرطي من الالف الى الياء " حاجة مخصصة يعني "

Clear Rules

حذف التنسيقات المطبقة 

Manage Rules 

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

تعالوا بقى نشوف الامثلة العملية على التنسيق الشرطي 

بالنسبة لمثالنا الاول اظهار المسميات الوظيفة بتنسيق معين بناءً على محتوى الخلية 

زي ما قلنا قبل كده هنروح على اداة التنسيق الشرطي ونختار مجموعة التنسيقات Highlight Cell Rules واللي منها هنختار الشرط او المعيار Equal To  كما بالشكل التالي 

مثال 2

في الشكل السابق يتم تنسيق Only Cells With  الخلايا التي تحتوي القيمة  Cell Value  " اكبر من او تساوي "

أحد الزملاء اتصل وهو بيجري " احمد انا عندي مشكلة " 👀

خييييييييير فيه ايه ؟

عندي اسماء الموظفين في شيت اكسيل وكنت عايز اعمل لكل موظف منهم مجلد او فولدر باسمه ضروري والقائمة كبيرة والاسماء كتير و مفيش وقت اعمل مجلد باسم كل واحد بالطريقة التقليدية  فلو فيه طريقة ينفع بيها اعمل المجلدات دي اعتمادا على شيت الاكسيل 

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

طب ازاي الكلام ده ؟

بص ياسيدي الكود اهو 👈

Sub MakeFolders()

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1

Loop

Next c

End Sub

انسخ بقى حضرتك الكود واعمله ادراج في ميديول على شيت الاكسيل اللي فيه الاسماء وادي الطريقة  Developer   أو بالضغط على Alt + F11

ومنها على Visual Basic  عندك هناااك اهي في الشمال 

حضرتك حدد نطاق الخلايا اللي فيه الاسماء اللي انت عايز تعمل مجلدات ليها واعمل تشغيل للكود هتلاقي عدد مجلدات مساوي لعدد الاسماء اللي حضرتك كنت محددها يعني لو عندك 50 اسم هتلاقي 50 مجلد العربي عربي والانجليزي انجليزي مش هتفرق 

طب هنلاقي المجلدات دي فين ؟

في نفس المكان الموجود فيه ملف الاكسيل اياً كان مكانه 

 طريقة انشاء قائمة منسدلة فرعية من قائمة اخرى

تحدثنا في تدوينة سابقة عن طريقة انشاء القوائم المنسدلة واهميتها وفي هذه التدوينة سوف نتحدث عن انشاء قائمة منسدلة معتمدة على الاختيار من قائمة اخرى 

يعني ايه الكلام ده ؟

يعني لما حضرتك تسئلني هو انا ينفع اعمل قائمة اول ما اختار عنصر فيها يتم تغيير الخيارات في القائمة التالية لها اعتماداُ على هذا العنصر ؟ 

اقولك طبعا ينفع وتعالى نشوف ازاي 

مثال 

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

وهفترض ان المحافظات اللى هيتم التعامل معها هي " الفيوم ، القليوبية ، الجيزة ، المنوفية ، القاهرة ، الاسكندرية ، بني سويف "

وطبعا كل محافظة من المحافظات دي تحتها عدد من المدن ولو نزلنا بالمستوى هنلاقي تحت كل مدينة عدد من القرى او النجوع 

المهم 

عشان اعمل الكلام ده هعمل زي ما حضرتك شايف كده 👇

بحيث يبقى عندي اسماء المحافظات وتحت كل محافظة اسماء المدن التابعة كلها زي كده 👇

وكنا اتكلمنا في التدوينة الخاصة بانشاء قائمة منسدلة عن طرق التعامل مع نطاقات خلايا المصدر لعناصر القائمة واللي فيها اما ان حضرتك تحدد نطاق الخلايا من L1 : P1  للاشارة الى مصدر البيانات الخاص بالمحافظة او اني اعمل تسمية لنطاق خلايا المحافظات زي ما هنشوف حالا

1 - هنحدد نطاق الخلايا من L1 : P1  ونروح على مربع اسم الخلية Name Box من شريط العمليات الحسابية Formula Bare ,k ونكتب الاسم اللي احنا عايزين نسمي بيه النطاق ولنفرض " المحافظة " على اعتبار استخدام اسم دال على محتوى النطاق👇

او اننا نستخدم الطريقة التانية لتسمية النطاق وهي اني اروح على شريط العمليات الحسابية Formulas Bar  ومنها Define Name  كما بالشكل التالي 

طبعا هنا النطاق صحيح لاني كنت محدد نطاق الخلايا قبل ما اروح للاداة Define Name  ويمكن التحديد من حديد بالسحب والاسقاط بالماوس اذا كان التحديد غير صحيح نتيجة عدم التحديد قبل استخدام الاداة 

2 - زي ما حضرتك عملت تسمية لنطاق الخلايا الخاص باسماء المحافظات هتكرر نفس الخطوات مع نطاق الخلايا الخاص باسماء المدن وهكدا بالنسبة للقرى كما يلي 👇

.

 

3 -  ودي الخطوة الاخيرة اللي فيها هيتم عمل القائمة الاساسية والقائمة الفرعية منها 

هنا طبعا نا قلت ان مصدر البيانات في عمود او بيان المحافظة هيروح الاكسيل يجيبة من القائمة او نطاق الخلايا اللي انا سميته " المحافظة " عشان تظهر القائمة بالشكل التالي 👇

بعد ما خلصنا قائمة المحافظات هننتقل للقائمة الفرعية الخاصة بالمدن وفقاً لاختياري للمحافظة في القائمة الاولى بمعنى اني لو اخترت محافظة الفيوم الاقي القائمة الفرعية بمدن محافظة الفيوم وعند تغيير الاختيار الى محافظة اخري يتم تغيير اسماء المدن وفقا لها وهكذا وعشان اعمل الموضوع ده لازم استخدم الدالة INDIRECT زي ما هو ظاهر في الشكل التالي 

بعد كتابة الدالة والاشارة الى الخلية B2 اللي هيا المحافظة يعني هنلاحظ ان الخلية كتبت كخلية مطلقة 2$B$ وده طبعا خطاء كبير ولو سبناه على نفس الوضع هنلاحظ ان اي قائمة بالمدن في اي مكان في الجدول هتتبع او محافظة انا اخترتها في الخلية B2 وعشان كده هنحول الخلية من مطلقة الى خلية نسبية بالضغط على F4  وبعد الضغط على OK

عند تغيير اسم المحافظة 

محافظة ثالثة 

هنتبع نفس الخطوات في حالة الرغبة في انشاء قائمة بالقرى والنجوع وطبعا مش لازم التذكير انك هتروح تكتب اسماء المدن لجميع المحافظات وتحت كل مدينة اسماء القرى ونرجع نعمل تسمية لنطاقات القرى باسماء المدن دي 👀 

 من اهم فوائد انشاء القوائم المنسدلة بصفة عامة وفي الاكسيل بصفة خاصة هي سرعة ادخال البيانات والتأكد من صحتها Data Validation 

طريقة انشاء القائمة المنسدلة

يمكن انشاء القائمة المنسدلة بسهولة باستخدام الاداة Data Validation  والخاصة بالتحقق من صحة البيانات على سبيل المثال لو كن عندي جدول بيانات وفيه بيان خاص بالمدينة كما بالشكل التالي 

وعشان نعمل الكلام ده هنحدد نطاق الخلايا من C 2:C 7  اللي هو حقل المدينة ثم نذهب الى التاب Data  ومنها على Data Validation  

بعد الضغط على Data Validation  هيظهر عندنا المربع الحواري اللي ظاهر في الشكل التالي 

عند التاب Settings هنختار الامر List  من القائمة المنسدلة تحت كلمة  Allow 

هيظهر لنا الخيار  Source  والخاص بتحديد عناصر القائمة المنسدلة  واللي هيكون مصدرها حاجة من اتنين اما اني اكتب اسماء المدن دي في نفس المكان وافصل بينهم بفاصلة منقوطة ; او اني اكتب اسماء المدن بتعتي في نطاق معين من الخلايا وبعد كده اروح احدد النطاق ده وهنشوف حالا الطريقتين لكتابة عناصر القائمة 

الطريقة الاولى 

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

بعد التطبيق هنلاحظ انه اتعامل مع الجيزة والقاهرة على انها عنصر واحد والكلام ده لاني كان لازم استخدم الفاصلة المنقوطة ; ومينفعش استخدم فواصل غيرها 

الطريقة الثانية 

هفترض معاك اني بتعامل مع عدد كبير من المدن فمش منطقي ابداً اني اقعد اكتب الاسماء دي كلها مع الفواصل واللي وارد جدا اني اكتب اسم منهم خطاء او استخدام فاصل غير الفاصلة المنقوطة 

طب ايه الحل ؟ 

حضرتك هتروح تكتب بيانات او اسماء المدن اللي انت بتتعامل معاها في اي عمود على جنب وبعيد عن الجدول بتاعك كما بالشكل التالي 

وفيه طريقة اسهل من الاثتين وهيا ان حضرتك تعمل تسمية لنطاق الخلايا اللي فيه اسم المحافظات ده باي اسم وتكتب الاسم في المربع النصي Source 

طب وانا اعمل تسمية لنطاق الخلايا ده ازاي ؟

حدد نطاق الخلايا وعند المربع النصي الخاص باسم الخلية اكتب الاسم اللي انت عايزه ثم Enter  

 

هتظهر القائمة المنسدلة زي ما حضرتك شايف كده 

 

 

 الدالة SUMPRODUCT

 من الدوال الهامة جداً والتي يجهل كثيراً من مستخدمي الاكسيل استخدامها والطرق المختلفة للتطبيق عليها حيث انه قد شاع استخدام الدالة في نطاق ضيق نظراً للفهم الخاطىء في يعض الاحيان لطبيعة هذه الدالة وطرق تطويعها المختلفة في ايجاد حلول 

تركيب الدالة 

=SUMPRODUCT( Arry1 ; Arry2 ; Arry3 ;;;;;)

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

الامثلة 

مثال 1

لو كان عندي جدول بالاصناف وسعر كل صنف والكمية المباعة منه وكان مطلوب مني اجمالي مبيعات الاصناف واللي احنا عارفين طبعاً انه عبارة عن حاصل ضرب الكمية المباعة في السعر لكل صنف على حده ثم تجميعهم في نهاية الجدول 

الكلام ده ممكن يتعمل بسهولة باني ازود عمود واضرب الكمية في السعر وفي نهاية العمود اجمع النواتج اللي طالعة 

او اني اشتغل ع الوضع الحالي ولكن مع استخدام الدالة اللي ممكن تضرب محتوى مصفوفة في مصفوفة اخرى وتجمعهم مع بعض في النهاية وهي دالة SUMPRODUCT كما بالشكل التالي 

مثال 2

على نفس الجدول انا عندي مبيعات الاصناف خلال فترة  مع اسعار متفاوتة وكنت عايز اجمالي مبيعات صنف محدد طبعا مفروض نروح بذهننا ناحية الدالة SUMIF او SUMIFS لكن خد بالك هنا انا مش بقول عايز اجمالي كميات صنف او او اجمالي اسعار صنف 

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

طب ازاي ؟ اقولك ياسيدي 

بالبلدي كده انا عايز ادور على محتوى الخلية C13  اياً كان محتواها بقى في نطاق الخلايا اللي مفترض يكون فيه اللي انا هكتبة في الخلية وهو طبعا حقل الاصناف B2:B12 وبعدين اي حاجة تلاقيها تتفق مع الشرط ده روح اضرب سعر في كمية واجمع زي ما عملنا في المثال 1

الناتج بعد التطبيق

وعشان نتأكد انا ضربت السعر في الكمية لكل بيان خاص بالليبتون وكان الناتج فعلا نفس ناتج الدالة 

مثال 3

المثال الثالث ده بقى احنا مش بنطوع فيه الدالة وبس ده احنا روضناها وخليناها تعمل اللي احنا عايزينه وزيادة كمان ، اقولك ليه

بس كده 👇

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

اينعم الموضوع سهل وبسيط وممكن ييجي بمليون طريقة لكن احنا بنمهد للي جاي ، المهم بقى انا هقول للدالة SUMPRODUCT بتاعتنا انا عايزك تبصي في محتوى الخلية B8 وتروحي للجدول تجمعي البيانات اللي تتفق معها ..... يعني كده 

انا قلتله من B1:E1 وده صف العناوين روح ابحث عن محتوى الخلية B8 فيه ولو لقيتها اجمع اي حاجة تلاقيها تحتها !!!!

ايه ده طب ماهو ده نفس الكلام في الامثلة اللي فاتت !!

منا بقولك احنا بنمهد للخطوة اللي جاية واللي فيها هنضيق نطاق البحث ونقول انا عايز اجمع بيانات الاسبوع ؟ الخاص بمدينة ؟