Category مهارات Excel

التصفية المتقدمة في الاكسل advanced filter

التصفية المتقدمة advanced filter تُعتبر ميزة إضافية لأداة filter التصفية في برنامج الاكسل تتيح لك عمل تصفية بشرط محدد او اكثر مع توضيح تلك الشروط في خلايا منفصلة ، فمثلاً اذا كان لديك جدول يحتوي على عدد كبير من البيانات وتُريد عمل فلتر للبيانات وتكرار هذه العملية اكثر من مرة بدون ان يكون هناك مجال للخطأ او انك تريد بعد كل عملية تصفية طباعة الجدول بعد التصفية ، عندها يكون استخدام أداة auto filter التصفية التلقائية العادية شيء مرهق وغير عملي لذلك يأتي هنا دور التصفية المتقدمة advanced filter في عمل ذلك بكل سهولة وكفاءة  ، لذلك نُكمل ما تحدثنا عنه من قَبل عن شرح لفرز البيانات وتصفيتها في excel سنتحدث اليوم عن كيفية عمل التصفية المتقدمة في الاكسل Advanced filter للبيانات وفي أي وقت نحتاج لعمل هذه التصفية وامثلة عملية نُطبق عليها هذه التصفية وكل التفاصيل والقواعد الخاصة ب التصفية المتقدمة advanced filter .

شرح كيفية تطبيق التصفية المتقدمة advanced filter

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

1- نقوم بتحديد الجزء الموضح في الصورة رقم 1 وهو ” للقسم التابع له ” و ” صافي الراتب ” (وهو الشرط التي سيتم الاعتماد عليها ) ثم نسخها copy

2- نقوم بعمل لصق paste لهذا الجزء فوق الجدول

3- في الجدول العلوي نقوم بترك ” باحث ” كما هي لأنها هي الشرط الأول ( ولو لم تكن هي فيتم تعديلها وكتابة الشرط ) ونقوم بتعديل صافي المرتب الي <2000 ( اقل من 2000 ) الشرط الثاني .. كما هو موضح بالصورة

4- ثم الضغط على أي خلية داخل الجدول الرئيسي واختيار advanced او خيارات متقدمة من قسم sort & filter الموجودة في تبويب data بيانات

 

وستظهر لنا هذه النافذة الصغيرة

شرح نافذة advanced filter التصفية المتقدمة :

list range \ نطاق القائمة : هو الجدول الرئيسي الذي سيتم التصفية منه ( يتم تحديده تلقائياً )

Criteria Range \ نطاق المعيار : هو النطاق الخاص بالشروط والتي قمنا بكتابتها بالأعلى

unique records only \ السجلات الفريدة فقط : نقوم بتحديد هذا الخيار لجعل النتائج مطابقة للشروط

 

5- بعد فتح هذه النافذة نقوم بالضغط على مربع Criteria Range نطاق المعيار وتحديد جدول الشروط الذي قمنا بإنشائه في الأعلى ثم نقوم بالتحديد على unique records only السجلات الفريدة فقط ثم الضغط على OK

 

يمكنك الاطلاع على التصفية التلقائية auto filter من خلال هذا الدرس : كيفية عمل فرز البيانات وتصفيتها في excel

 

لاحظ ان :

1- اذا كنت تريد عرض البيانات الناتجة عن عملية التصفية في مكان اخر نقوم بعمل بالتحديد على copy another location النسخ الي موقع اخر ثم نقوم بالضغط على copy to نسخ الي واختيار المكان الذي تريد النسخ اليه ثم الضغط على OK

2- لعمل تصفية جديدة يجب عليك مسح التصفية الحالية من خلال الضغط على Clear مسح الموجودة في قسم sort & filter الموجودة في تبويب data بيانات

 

ملاحظات على التصفية المتقدمة

1- لعمل تصفية لأكثر من شرط ولكن احدهم وليس كليهما يتم عمل نطاق المعيار Criteria Range اكثر من صف ( حسب عدد الشروط ) وكتابة كل شرط في صف لوحده

أي فمثلاً تريد عرض الموظفين الذين يعملون كـ باحث او الذين يكون مرتبهم اقل من 2000 في هذه الحالة نقوم بكتابة الشروط كما هو موضح بالصورة :

 

2- ولكن عليك ان تعرف انه عندما يكون تحديد Criteria Range او نطاق المعيار اكثر من صف فيجب بكتابة الشرطين في نفس السطر وتكرارهم في جميع السطور بهذا الشكل :

ففي هذه الحالة تم اختيار Criteria Range او نطاق المعيار أربعة صفوف فكان من اللازم تكرار الشروط في الأربعة صفوف

3- ليس شرط ان تكتب المعايير الخاصة بالفلتر فوق الجدول فيمكنك كتابتها في أي مكان في الشيت

4- من ضمن استخدامات التصفية في مكان اخر copy another location هو عندما نكون بحاجة الي طباعة هذه البيانات مع الاحتفاظ بالجدول الرئيسي كما هو .

5- يجب ان لا يكون هناك أي دمج merge للخلايا في الجدول الرئيسي او جدول المعايير ( الشروط )

 

موضوع قد يهمك عن انشاء وتعديل القائمة المنسدلة : تعديل قائمة منسدلة في Excel

 

التصفية المتقدمة الديناميكية في الاكسل

البعض منا قد يري استخدام طريقة التصفية المتقدمة للبيانات مملة بعض الشيء وغير عملية بالأخص مع جداول البيانات الكبيرة والحاجة الي تكرار هذه العملية الي العديد من المرات من حيث انه في كل عملية تصفية يجب عليك تحديد نطاق المعايير وبعدها تقوم بمسح الفلترة لعمل تصفية جديدة ، لذلك فطريقة التصفية المتقدمة الديناميكية تسهل علينا كثيراً هذه الأمور من خلال ربط التصفية المتقدمة advanced filter بالماكرو macro دعونا نتعرف كيف يمكننا عمل ذلك ..

1- نقوم بنسخ copy رأس الجدول والصف الذي تحته ووضعه paste في مكان ما داخل الشيت

 

2- تشغيل الماكرو Macro في الاكسل من خلال الضغط على الأيقونة الموجودة بالأسفل ثم اضغط على OK

 

3- نقوم بعمل خطوات التصفية المتقدمة في مكان اخر copy another location كما تعرفنا عليها منذ قليل وهي :

الضغط على أي خلية داخل الجدول واختيار advanced او خيارات متقدمة ثم نقوم بتحديد Criteria Range نطاق المعيار من خلال تحديد الجدول الذي قمنا بلصقه ثم نقوم بالتحديد على copy another location واختيار من خانة copy to نسخ الي اختيار المكان الذي نريد ان تظهر فيه البيانات الناتجة عن التصفية ثم التحديد على unique records only السجلات الفريدة فقط ثم الضغط على OK

3- نقوم بإيقاف الماكرو macro من خلال نفس الزر الذي قُمنا بتشغيله منه

نقوم الان بمسح البيانات الموجودة في جدول الشروط و المُشار اليها في الصورة التالية :

4- ثم نقوم بعمل زر للبحث من خلال إضافة شكل shape من خلال تبويب insert ادراج ثم shapes أشكال  واختيار الشكل المربع ورسم مربع البحث بين الجدولين مثلاً ثم كتابة كلمة ” بحث ” في المربع و تنسيقه من خلال تكبير الخط وجعله في الوسط وجعله سميك Bold

ولربط الزر بالماكرو نقوم بالضغط علي زر ” بحث ” كليك يمين واختيار assign macro او تعيين ماكرو واختيار الماكرو الذي تم عمله وفي حالتنا هذه هو macro 1 ثم الضغط على OK

5- نقوم بالتجربة الان وكتابة الشروط التي نريد ان يتم التصفية على أساسها في جدول نطاق المعيار Criteria Range ثم الضغط على زر بحث فمثلاً في الصورة التالية قمنا بكتابة ” باحث ” وان يكون صافي المرتب اقل من 2000 وسنلاحظ ان التصفية المتقدمة الديناميكية تعمل بنجاح

شيء مهم يجب ان تفعله لحفظ ملف الاكسل بالماكرو بشكل جيد وهو ان نقوم بالضغط على File ملف واختر save as حفظ ك ثم اختيار صيغة xlsm واضغط على save حفظ

استخدامات التصفية المتقدمة في الاكسل advanced filter

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

2- عندما تكون بحاجة الي تكرار عملية التصفية العديد من المرات بأشكال مختلفة

3- عندما يكون جدول البيانات يحتوي علي المئات والمئات من البيانات فيُنصح جدا باستخدامها

4- عندما تريد ان تكون البيانات الناتجة من عملية التصفية ان تكون في مكان اخر في الشيت او في شيت اخر

 

مواضيع قد تُهمك :

• تعديل قائمة منسدلة في Excel
• اسهل طريقة لحذف التكرار في excel

في كثير من الأحيان يتطلب منك كمُحاسب او تشغل منصب اداري في الشركة حساب العمر الخاص بالموظفين او حساب مدة عمل الموظف داخل الشركة بغرض صرف علاوة مثلاً ، او تريد حساب العمر عند تاريخ محدد ، وبطبيعة الحال فإن برنامج excel يمكنه حساب كل هذه الأمور كما اننا سنتعرف عن كيفية حساب العمر في excel بالسنين وحساب العمر بالسنين والشهور والايام .

كيفية حساب العمر في excel

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

معادلة حساب العمر في excel

يمكننا حساب العمر من تاريخ الميلاد وذلك من خلال استخدام معادلة حساب العمر في excel وهي معادلة Year وهي اسهل واسرع طريقة حساب العمر في excel ، وطريقة استخدام معادلة حساب العمر معادلة Year كالاتي :

1- عمل عمود خاص بعُمر الأشخاص

2- كتابة معادلة حساب العمر في excel وهي :

=(YEAR(NOW())-YEAR(F6))

شرح المعادلة :

نقوم بطرح تاريخ العام الحالي وهو اختصار ل YEAR(NOW() .. من تاريخ ميلاد الموظف وهي الخلية F6

ولمعرفة كيفية تعديل صيغة التاريخ في excel اضغط هنا

حساب العمر عند تاريخ محدد

اذا اردنا حساب عمر الموظف حتى تاريخ معين بالسنين أي مثلا نريد حساب عمر الموظف حتي تاريخ 1-1-2022 في هذه الحالة نستخدم دالة DateIF والطريقة كالاتي :

1- عمل عمود خاص بعمر الموظف

2- كتابة المعادلة الاتية :

=DATEDIF(F6,$H$3,”Y”)

شرح المعادلة :

الدالة DATEDIF : تحسب هذه الدالة عدد الايام أو الأشهر أو السنوات بين تاريخين.

F6 تاريخ ميلاد الموظف

$H$3 خلية التاريخ المحدد الذي نريد حساب العمر عنده بعد تثبيت الخلية من خلال علامة الدولار $

“Y” هي اختصار Years

حساب العمر باليوم والشهر والسنة

يمكنك أيضا حساب العمر باليوم والشهر والسنة في Excel اذا اردنا معرفة عمر شخص بالسنين والشهور والايام معاً ، نستخدم دالة حساب العمر باليوم والشهر والسنة وهي دالة DateIF وهي الدالة المسؤولة عن حساب عدد الايام او الشهور او السنين بين تاريخين

طريقة استخدام دالة حساب العمر باليوم والشهر والسنة دالة DateIF كالاتي :

1- عمل عمود خاص بعمر الشخص

2- كتابة دالة حساب العمر باليوم والشهر والسنة وهي

=DATEDIF(F7,TODAY(),”Y”) & ” سنة, ” & DATEDIF(F7,TODAY(),”YM”) & “شهور, ” & DATEDIF(F7,TODAY(),”MD”) & “ايام”

شرح المعادلة :

الدالة تتكون من ثلاثة معادلات

1- معادلة حساب السنين :

DATEDIF : تحسب هذه الدالة عدد الايام، أو الأشهر أو السنوات بين تاريخين

F7 : تاريخ البداية وفي حالتنا هذه هو تاريخ ميلاد الموظف

TODAY() : اختصار لتاريخ اليوم

“Y” : وحدة حساب العمر وهي اختصار لكلمة Years اي السنين

& ” سنة, ” & : إضافة كلمة “سنة” بعد رقم السنين

2- معادلة حساب الشهور :

نفس المعادلة السابقة مع تغيير وحدة حساب العمر ل “YM” وبدلاً من “سنة” كتابة “شهور”

3- معادلة حساب الايام :

نفس معادلة حساب السنين مع تغيير وحدة حساب العمر ل “MD” وبدلاً من “سنة” كتابة “أيام”

نستنتج الاتي :

اننا اذا اردنا حساب العمر بالسنين فقط نستخدم معادلة حساب العمر بالسنين بهذه الطريقة

=DATEDIF(F7,TODAY(),”Y”)

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

=DATEDIF(F7,TODAY(),”M”)

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

=DATEDIF(F7,TODAY(),”D”)

استخراج السنة من تاريخ الميلاد

قد تكون بحاجة الي استخراج السنة فقط من تاريخ الميلاد لاستخدامها في أي شيء اخر ، في هذه الحالة نستخدم دالة Year وهي الدالة المسؤولة عن استخراج السنة من تاريخ الميلاد

طريقة استخدام دالة Year

1- قم باختيار عمود خاص باستخراج السنة من التاريخ

2- اكتب دالة استخراج السنة من تاريخ الميلاد وهي :

=YEAR(F6)

مع العلم ان F6 هي خلية تاريخ الميلاد

شرح معادلة DateIF لحساب الفرق بين تاريخين

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

وظيفة معادلة DateIF 

تحسب هذه الدالة عدد الايام أو الاشهر أو السنوات بين تاريخين

مكونات معادلة DateIF

حيث ان معادلة DateIF تتكون من 3 مكونات

=DATEDIF(start_date,end_date,”unit”)

=DATEDIF(تاريخ النهاية,تاريخ البداية,”Unit”)

– تاريخ البداية : وهو مثلاً تاريخ الميلاد او اي تاريخ قديم

– تاريخ النهاية : وهو مثلا تاريخ اليوم او تاريخ معين يتم تحديده ” تاريخ احدث ”

– الوحدة \ unit : بأي وحدة تريد عرض الفارق بين التاريخين

شيت جاهز لحساب العمر في Excel

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

رابط تحميل شيت جاهز لحساب العمر

مواضيع ذات صلة :

• كيفية تعديل صيغة التاريخ Excel
• طريقة حساب ساعات العمل في Excel

يمكنك اجراء عملية تحويل الأرقام إلى حروف باللغة العربية excel والتي تُسمى بـ التفقيط بكل بساطة وسهولة ، حيث ان التفقيط عبارة عن عملية متعارف عليها في عُرف التجارة وفي جميع أنحاء العالم فهي تقوم بتحويل الأرقام العادية 1،2،3 .. إلي كتابة وحروف باللغة العربية مثلاً واحد ، اثنين ، ثلاثمئة الف .. حيث ان هذه العملية أصبحت من اساسيات كتابة الفواتير والغرض منها هو تُثبيت او تأكيد على المبلغ لعدم السماح بالتلاعب بالرقم بأي شكل من الاشكال ، وبطبيعة الحال برنامج Microsoft excel يقوم بإجراء هذه العملية بأكثر من طريقة لذلك سنتعرف في هذا الدرس على اسهل طريقة تحويل الأرقام إلى حروف باللغة العربية في Excel .

طريقة تحويل الأرقام إلى حروف باللغة العربية excel

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

اظهار تبويب developer المطور

1- الانتقال الي options خيارات من قائمة file ملف

2- ثم اختيار من القائمة الجانبية customize Ribbon تخصيص الشريط ونقوم بوضع علامة الصح امام developer المطور ثم الضغط على Ok

إضافة كود التفقيط فيجوال بيسك في excel

لتفعيل ميزة تحويل الأرقام إلى كتابة باللغة العربية excel يجب إضافة كود visual basic فيجوال بيسك معين في مصنف اكسل ( ملف اكسل ) بكل بساطة من خلال الخطوات الاتية :

1- نقوم باختيار visual basic من تبويب developer المطور

2- ومن النافذة التي ظهرت لك اختر insert ثم module ستظهر لك نافذة بيضاء

3- نقوم بنسخ copy الكود التالي و لصقة paste في النافذة البيضاء التي ظهرت لك

رابط الكود

4- ثم تقوم بالضغط على file ثم Close to Return to Microsoft Excel

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

وهي الدالة المسؤولة عن عملية تحويل الأرقام إلى حروف باللغة العربية بغض لنظر عن العملة التي ستستخدمها ، حيث اننا سنقوم بتحديد العملة عند كتابة الدالة في الخطوة التالية ..

استخدام دالة التفقيط

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

مكونات دالة تفقيط الارقام :

=NumberToText(اسم العملة الفرعية”,”اسم العملة الرئيسية”,خلية المبلغ”)

=NumberToText(H11,”هللة”,”ريال سعودي”)

شرح المعادلة :

NumberToText : هي الدالة التي قُمنا بإنشاءها من خلال محرر visual basic

H11 : هي الخلية المكتوب بها الرقم

“ريال سعودي” : اسم العملة الرئيسية يجب ان يكون بين علامتين تنصيص “” ويمكن تغييره لأي عملة تريدها مثلا “جنية مصري”

“هللة” : هي العملة الفرعية بالنسبة للريال السعودي و “قرش” بالنسبة للجنية المصري ويمكن تغيرها ايضا كما تريد

 

حفظ ملف excel يحتوي على كود دالة التفقيط

يجب حفظ الملف بصيغة Excel Macro-Enabled Workbook لأنه يحتوي على كود برمجي visual basic من خلال الخطوات الاتية

1- من قائمة File ملف اختر save as حفظ باسم

2- اختر مكان الحفظ Computer ثم Browse استعراض 

 

 

3- قم بتحديد مكان الحفظ ثم من قائمة Save as type حفظ كنوع اختر Excel Macro-Enabled Workbook

 

ملحوظة : عند فتح الملف مرة آخري قد تظهر رسالة صفراء تشير لك ان تقوم بتفعيل macro قم بالضغط على تفعيل

 

ملاحظات مهمة :

• استخدم اختصار لوحة المفاتيح Alt + F11 لفتح محرر Visual Basic (VBE) مباشرة بدون الحاجة لإظهار وضع المطور
• اضغط على Alt + Q لغلق محرر كود Visual Basic والعودة الي excel وسيتم حفظة تلقائياً
• لعدم حفظ كود Visual Basic يجب مسحه ثم غلق المحرر
• قد تواجه مشكلة وهي ان المحرر لا يقوم بقراءة اللغة العربية بشكل صحيح ويظهر علامات استفهام بدلا منها ، حل هذه المشكلة يتلخص في استخدام دالة التفقيط NumberToText ثم قم بفتح محرر الاكواد ( visual basic من تبويب المطور ) وقم بمسح الكود الموجود ثم وضع الكود مرة آخري ستلاحظ ان اللغة العربية تظهر بنجاح ثم قم بغلق المحرر
• الكود الذي قمنا بإضافته يعمل في مصنف اكسل الحالي فقط ولإضافته في مصنف آخر يجب تكرار الخطوات

مواضيع قد تُهمك :

• حساب النسبة المئوية في Excel
• طريقة حساب ساعات العمل في Excel

قائمة منسدلة تقبل البحث

في وقت ما قد تجد نفسك تستهلك الكثير من الوقت في انجاز عملية ادخال البيانات في برنامج excel من خلال القائمة المنسدلة وذلك لان هناك الكثير من البيانات وتحتاج في كل مرة تقوم بإدخال عنصر بالبحث اليدوي عنه ، لذلك فإن قائمة منسدلة تقبل البحث ستوفر عليك كل هذا الوقت والمجهود وذلك من خلال قابليتها للبحث داخل القائمة المنسدلة وإظهار فقط النتائج التي تحتوي على الكلمة او الحروف التي قُمت بكتابتها حيث اننا سوف تُمكننا حتى من البحث بمجرد كتابة اول حرف من خلال القائمة المنسدلة ، لذلك سوف نتعرف اليوم عن طريقة عمل قائمة منسدلة تقبل البحث بسهولة وبالصور .

خطوات عمل قائمة منسدلة تقبل البحث

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

1- عمل قائمة منسدلة عادية

نقوم بعمل قائمة منسدلة عادية بالطريقة المعتادة من خلال الاتي :

تحديد الخلايا التي تريد ان تظهر بها القائمة المنسدلة

 

ومن تبويب Data بيانات اختر Data validation التحقق من صحة البيانات

 

ومن النافذة التي ظهرت لك اختر من Allow او السماح اختر list قائمة ثم اضغط على المربع المُشار إليه في الصورة التالية وقم بتحديد نطاق الخلايا الخاص بالقائمة المنسدلة يدوياً ثم اضغط OK

لتظهر لك القائمة المنسدلة العادية بهذا الشكل

2- اظهار تبويب developer المطور

نقوم بإظهار تبويب developer المطور اذا لم يكن ظاهراً عندك من خلال الضغط كليك يمين Right click علي أي جزء فارغ في شريط الخيارات واختر customize the Ribbon تخصيص الشريط

ونقوم بوضع علامة الصح امام developer المطور ثم الضغط على Ok

3- كود قائمة منسدلة تقبل البحث في Excel

نقوم بوضع كود معين لكي نقوم بعمل القائمة المنسدلة القابلة للبحث وذلك من خلال الخطوات الاتية :

نقوم بالضغط على كليك يمين Right click على اسم الشيت بالاسفل واختر view code او عرض التعليمات البرمجية

ثم نقوم بنسخ الكود الموجود في الرابط التالي Copy ولصقة paste في النافذة البيضاء التي ظهرت لك

رابط الكود

ثم تقوم بالضغط على Alt +Q او اختيار من قائمة File اختيار Close to Return to Microsoft Excel

ملحوظة هامة : بعد اجراء هذه الخطوة لا تضغط في علي أي خلية حتى لا يظهر لك خطأ

4- عمل قائمة منسدلة تقبل البحث

بعد التأكد من القيام بالخطوات السابقة فقد اقتربنا من الانتهاء من خطوات عمل قائمة منسدلة تقبل البحث ..

انتقل الي خيار insert ادراج الموجودة في تبويب developer المطور ثم اضغط على الأيقونة المشار عليها في الصورة التالية وهي combo box او ” مربع تحرير وسرد “ باللغة العربية

 

قم بعمل شكل مستطيل من خلال الماوس لرسم المستطيل ليظهر لك بالشكل التالي

 

الانتقال الي properties خصائص في تبويب المطور

 

وتغيير name الي DropListTemp ولا تقلق عزيزي فهذه الكلمة مكتوبة داخل الكود فيمكنك اخذها نسخ copy ولصقها paste

 

وتغيير Text align إلي Center

ثم الخروج من هذه النافذة من خلال علامة ال X

ثم اضغط عل design mode وضع التصميم لإلغاء هذا الوضع

 

اضغط على أي خلية في الشيت ثم الضغط على عمود القائمة المنسدلة سنلاحظ انه تم عمل قائمة منسدلة تقبل البحث بنجاح .

 

حفظ ملف Excel يحتوي على قائمة منسدلة قابلة للبحث

يجب حفظ الملف بصيغة Excel Macro-Enabled Workbook لأنه يحتوي على كود فيجوال بيسيك

1- من قائمة File ملف اختر save as حفظ باسم

2- اختر مكان الحفظ Computer ثم Browse استعراض

 

3- قم بتحديد مكان الحفظ ثم من قائمة Save as type حفظ كنوع اختر Excel Macro-Enabled Workbook

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

مواضيع قد تُهمك :

• تعديل قائمة منسدلة في Excel
• اسهل طريقة تحويل الأرقام إلى حروف باللغة العربية excel

من المعروف ان الصفر علي الشمال ليس له قيمة لذلك في برنامج excel ايضاً لا يُعطيه قيمة من خلال انه لا يُظهره في الاساس ، فمهما كتبت من اصفار علي يسار الرقم لن يظهر لك في الحالة الطبيعية ، ولكن في حالات معينة قد نحتاج الي اظهار الصفر على الشمال فمثلاً نريد كتابة ارقام هواتف ( رقم التليفون ) او كود الدولة الخاص برقم الهاتف ، او عندما نكون بصدد كتابة اكواد لأصناف سيتم ادخالها في برنامج حسابات مثلاً وبالتالي يجب ان يكون كود الصنف مكون من عدد ارقام معين (5ارقام مثلاً ) .. وغيرها من الاستخدامات التي تُفيدنا عند اظهار الصفر علي الشمال ، لذلك سنتعرف في هذا الدرس عن طرق عديدة لاظهار الصفر علي الشمال في Excel.

كيفية اظهار الصفر على الشمال في برنامج excel

هناك العديد من الطرق لاظهار الصفر على الشمال والتي سنتعرف عليها في هذا الدرس ، فيمكنك تجربة الطريقة المناسبة لك :

الطريقة الاولي : طريقة format cells تنسيق الخلايا

1- نقوم بتظليل select للارقام التي نريد إضافة لها صفر علي الشمال

2- نقوم بالضغط كليك يمين Right click واختيار format cells تنسيق الخلايا

3- من قسم Number ومن category الفئة اختر custom مخصص ، اضغط علي الخانة الموجود اسفل Type النوع وقم بمسح محتواها

4- ثم قم بكتابة بها اصفار بعدد الرقم الذي تريد ان يكون الرقم عليه ، فمثلاً رقم الموبايل مكون من 11 رقم والرقم الاول هو صفر اذاً نقوم بكتابة عدد 11 صفر ، واذا كان كود الصنف يتكون من 5 ارقام اذا نقوم بكتابة عدد 5 اصفار وهكذا ..

الطريقة الثانية : طريقة علامة &

1- عمل خلية خاصة برقم الصفر الذي نريد اضافته .

2- نقوم في عمود آخر بكتابة هذه المعادلة

=$L$4&+M6

شرح المعادلة :

$L$4 هي الخلية الخاصة برقم الصفر بعد ان قُمنا بتثبيت الخلية لتكرار المعادلة مع باقي الارقام

& تعني اضافة

M6 هي الخلية المكتوب بها رقم الهاتف

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

وسنلاحظ فوراً ظهور الصفر في جميع ارقام الهاتف كما بالصورة التالية

الطريقة الثالثة : طريقة الدالة REPT

1- نقوم في عمود جديد بكتابة هذه المعادلة :

=REPT(“0”,5-LEN(M6))&M6

شرح المعادلة :

REPT هي دالة تقوم بإضافة نص معين علي محتوي الخلية

“0” هو الرقم الذي نريد إضافته

5 هو عدد الارقام الفعلي لكود الصنف بعد إضافة الصفر

M6 هي خلية الرقم الذي نريد اضافة له صفر

LEN تقوم بحساب عدد الارقام في الخلية

ميزة هذه الطريقة انها لا تُضيف صفر واحد فقط ، فهي تضيف عدد اصفار علي اليسار حتي يصل عدد الرقم للعدد الذي قمت بإدخاله في المعادلة وهو 5 ، فمثلا اذا كان الرقم هو 123 فستضيف له صفرين ليصبح 00123 واذا كان الرقم 1234 فستضيف له صفر واحد ليصبح 01234 وهكذا ..

الطريقة الرابعة : طريقة معادلة Text

1- نقوم في عمود جديد بكتابة هذه المعادلة :

=TEXT(M6,”00966##########”)

شرح المعادلة :

معادلة text تقوم بتنسيق رقم وتحويله إلى نص

M6 هي خلية الرقم التي نريد اضافة له صفر

00966 هو الرقم الذي نريد إضافته الي الرقم الاصلي وهو عبارة عن كود السعودية

اما عن ########## هي شبابيك بعدد الارقام الاصلية وهي 10 شباك

ميزة هذه الطريقة اننا يمكننا ليس فقط اضافة الاصفار يسار الرقم ولكن يمكننا إضافة كود دولة مثلاً كود السعودية وهو 00966

 

وهكذا قد تناولنا تقريباً جميع طرق اظهار الصفر على الشمال في برنامج excel والتي تساعدك في امور اخري ، اذا كان لديك/ي اي استفسار لا تتردد في كتابته في التعليقات .

مواضيع قد تُهمك :

• تنسيق الارقام في Excel
• شرح كامل عن الترقيم التلقائي في Excel

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

طريقة حساب ساعات التأخير excel

هناك طريقتين لحساب ساعات التأخير .. الطريقة السهلة هي الطريقة الثانية

الطريقة الاول لحساب دقائق التأخير

1- نقوم بعمل جدول مثل الموجود الصورة التالية

ولمعرفة كيفية عمل جدول علي excel وتنسيقة اضغط هنا

حيث ان 1  2  3  .. هي تاريخ اليوم .. ويجب ان ننتبه إلي عدد ايام الشهر 31 يوم ام 30 يوم ام غير ذلك ..

2- نقوم بتحديد الخلايا في صف “موعد الحضور” والضغط علي كليك يمين Right click واختيار format cells تنسيق الخلايا

ومن قسم Number اختر من الفئة category اختيار الوقت Time

واختر التنسيق المُشار إليه في الصورة

وبعد ذلك نبدأ في كتابة موعد حضور الموظف بهذا الشكل 8:00

3 – اذا كان هناك حد للتأخير اي ان الخصم يتم اذا تخطي هذا الحد نقوم بعمل خلية ثابتة لحد التأخير خارج الجدول ونقوم بكتابة ساعة حد التأخير فمثلاً يكون في حالتنا هو 8:15

4 – وفي صف عدد دقائق التأخير نقوم بكتابة هذه المعادلة :  معادلة حساب التأخير

=IF(C5>$N$2,TEXT(C5-$N$2,”hh:mm”),0)

شرح المعادلة :

( معادلة If تعني لو ) :  لو كانت خلية موعد الحضور C5 اكبر من خلية حد التأخير N2 , اذاً تقوم بطرحهم من بعض ( خلية موعد الحضور – خلية حد التأخير )  ويكون تنسيق الناتج بهذا الشكل hh:mm , واذا كان غير ذلك يعطي النتيجة صفر.

ستلاحظ في هذه المعادلة الاتي : 

• قمنا بإدخال معادلة text لان التنسيق ليس ارقام عادية ولكن هو وقت Time
• قمنا بتثبيت خلية حد التأخير لانها بطبيعة الحال ستكون ثابته عندما نقوم بتكرار المعادلة

ولشرح كامل عن تثبيت الخلية في Excel اضغط هنا

5 – نقوم بتكرار المعادلة في جميع الايام

6 – ولإيجاد اجمالي هذه الدقائق نأتي في خلية الاجمالي ونقوم بكتابة هذه المعادلة :

=TEXT(C6+D6+E6+F6+G6+H6+I6+

J6+K6+L6+M6+N6+O6+P6+Q6+R6+

S6+T6+U6+V6+W6+X6+Y6+Z6+AA6+

AB6+AC6+AD6+AE6+AF6+AG6,“hh:mm”)

شرح المعادلة :

• قمنا بإستخدام معادلة Text ايضاً لاننا نتعامل مع قيم وقتية Time  وليس قيم رقمية عادية.
• اما خلايا c6 d6 e6 .. هي خلايا عدد دقائق التأخير وقمنا بجمعها يدوياً وليس بإستخدام معادلة sum لان للاسف لا يمكن استخدام معادلة sum في هذه الحالة مع معادلة Text.
• hh:mm هي الصيغة التي سيتم كتابة بها اجمالي عدد دقائق التأخير.

الطريقة الثانية لحساب ساعات التاخير excel

1- نقوم بعمل جدول مثل الموجود الصورة التالية

حيث ان 1  2  3  .. هي تاريخ اليوم .. يجب ان ننتبه الي عدد ايام الشهر 31 يوم ام 30 يوم ام غير ذلك

2- نقوم بكتابة عدد الدقائق الحضور التي فوق الساعة فمثلاً اذا كان موعد حضور الموظف هو 8:25 نقوم بكتابة 25 واذا كان 9:05 نقوم بكتابة 65 وهكذا

3 – اذا كان هناك حد للتأخير اي ان الخصم يتم اذا تخطي هذا الحد نقوم بعمل خلية ثابتة لحد التأخير خارج الجدول ونقوم بكتابة دقيقة حد التأخير فمثلاً يكون في حالتنا هو 15

4 – وفي صف عدد دقائق التأخير نقوم بكتابة هذه المعادلة

=IF(C5>$N$2,C5-$N$2,0)

شرح المعادلة : 

( معادلة If تعني لو ) لو كانت خلية موعد الحضور C5 اكبر من خلية حد التأخير N2 , اذاً تقوم بطرحهم من بعض ( خلية موعد الحضور – خلية حد التأخير ) , واذا كان غير ذلك يعطي النتيجة صفر

5 – نقوم بتكرار المعادلة في جميع الايام

6 – ولإيجاد اجمالي هذه الدقائق نقوم بإستخدام معادلة Sum المجموع

=SUM(C6:AG6)

طريقة حساب دقائق التأخير كـقيمة

الآن نريد حساب قيمة هذه الدقائق او الساعات من المرتب نقوم بعمل الاتي :

1- نقوم بعمل عمود لقيمة التأخير و عمود اخر لصافي المرتب الذي سيتم حساب علي اساسه قيمة الخصم

اذا طبقنا الطريقة الاولي نقوم بكتابة المعادلة الاتية في خلية قيمة التأخير

=(AJ6/AG4/8)*AH6*24

ملحوظة : يجب الالتزام بالاقواس واماكنها

شرح المعادلة : 

AJ6 هي خلية المرتب

AG4 هي إشارة لآخر يوم في الشهر وهو 31

8 هو عدد ساعات العمل الرسمية يمكنك تغييرها اذا كان غير ذلك

ما بين القوسين هو لإيجاد قيمة الساعة الخاصة بالموظف

AH6 هي خلية اجمالي دقائق التأخير

24 هي عدد ساعات اليوم

 

اذا طبقنا الطريقة الثانية نقوم بكتابة المعادلة التالية في خلية قيمة التأخير

=(AJ6/(AG4*8*60))*AH6

ملحوظة: يجب الالتزام بالاقواس واماكنها

شرح المعادلة :

AJ6 هي خلية المرتب

AG4 هي اشارة لآخر يوم في الشهر وهو 31

8 هو عدد ساعات العمل الرسمية يمكنك تغييرها اذا كان غير ذلك

60 هو عدد الدقائق بالساعة

AH6 هي خلية اجمالي دقائق التأخير

 

مواضيع ذات صلة :

• أهم 8 أساسيات لتعلم إدخال معادلات excel للمبتدئين
• شرح مفصل عن اهم الدوال في excel للمبتدئين

قد يقف أمامك عقبة وهي انك تريد حساب عدد الساعات بين وقتين والتعامل معها في المعادلات مثلاً حساب ساعات العمل لموظف او عامل وقد تظن ان برنامج excel لن يستطيع القيام بعمل ذلك! .. ولكن في الحقيقة يُمكن لبرنامج excel بالتعامل مع الوقت كما يتعامل مع الارقام ايضاً ، لذلك سنتعرف في هذا الدرس عن كيف سنجعل برنامج excel يتعامل مع الوقت من خلال تنسيق الوقت في الخلايا و حساب ساعات العمل في excel وغيرها من الامور المتعلقة بهذا الأمر .

طريقة حساب ساعات العمل في Excel

مثلاً اذا كان امامنا هذا الجدول الموجود في الصورة التالية لحساب ساعات العمل لموظف اسمه بكر وهذا الموظف يعمل بدوام جزئي ، ويتم حسابه علي اساس ان الساعة ب 50 جنيهاً

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

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

ثانياً : وبعد ان قُمنا بعمل الجدول قُمنا بتغيير تنسيق التاريخ ..

ثالثاً : نقوم الآن بتعديل تنسيق الخلايا في عمود “الحضور” وعمود “الانصراف” لكي نُهيئ الخلايا للتعامل مع الوقت ( الساعة ) من خلال تنسيق الوقت والطريقة كالآتي :

1- قم بتظليل او تحديد الخلايا التي سيتم كتابة بها الوقت وفي حالتنا هذه تحديد خلايا عمود الحضور وعمود الانصراف

2- اضغط كليك يمين واختر format cell او تنسيق الخلايا 

3- ومن النافذة التي ستظهر لك اختر قسم Number ومن ال category او الفئة اختر Time او الوقت ومن قسم type يمكنك اختيار الصيغة المناسبة لتنسيق الوقت ثم اضغط ok

الآن بعد تعديل تنسيق خلايا عمود”الحضور” وعمود “الانصراف” نقوم بكتابة ساعة الحضور وساعة الانصراف مع الإلتزام بهذه القواعد :

1- كتابة الساعة بالشكل التالي 8:00 اذا كان في تمام الساعة او كتابة عدد الدقائق مثلاً بهذا الشكل 8:20

2- اذا كانت الساعة مساءاً تُكتب بتنسيق الـ 24ساعة أي اذا كانت الرابعة مساءاً تكتب 16:00 .. رقم 16 لأن الساعة 4 مساءاً + 12 ساعة = 16  .. واذا كان الساعة 9 مساءاً تكتب 21:00 لأن 9 + 12ساعة = 21 وهكذا ..

لحساب عدد ساعات العمل الآن نقوم بكتابة المعادلة التالية :

=TEXT(E8-D8,“hh:mm”)

شرح المعادلة :

معادلة Text في هذه الحالة تقوم بتنسيق رقم وتحويله إلى صيغة الوقت hh:mm

خلية E8 هي ساعة الانصراف

خلية D8 هي ساعة الحضور

ووظيفة المعادلة انها ستقوم بطرح ( ساعة الانصراف – ساعة الحضور  ) وتحويلها لصيغة الوقت وهي hh:mm

ونقوم بتكرار المعادلة في باقي خلايا عمود “عدد ساعات العمل”

ولكن قد تواجهنا مشكلة في حالات معينة وهي عندما يكون مدة عمل الموظف علي مدار يومين فمثلاً :

الموظف يأتي للعمل الساعة 10 مساءاً وينصرف 7 صباحاً اليوم التالي .. سيعطيك البرنامج في هذه الحالة رسالة خطأ value#

والسبب هو ان اكسل يعتبر ساعة الانصراف هذه اقل من ساعة الحضور _ حيث ان اكسل يقوم بتحويل الساعة الي قيمة رقمية بشكل ما _ وبالتالي لا يمكن الطرح لان النتيجة ستكون سالب _ وبالطبع لا يوجد وقت بالسالب _ لذلك يظهر لك خطأ value#

والحل هنا هو وضع 1+ بين اسماء الخلايا _ليتحول السالب الي موجب وتكون النتيجة صحيحة _

وستكون المعادلة بالشكل التالي :

=TEXT(E8+1-D8,“hh:mm”)

ولكن ايضاً هناك مشكلة مع هذه المعادلة حيث انها لا تصلح في الحالة الاولي عندما يكون مدة عمل الموظف على مدار يوم واحد

بالطبع هناك حل عزيزي القارئ فبرنامج excel لا يمكن ان يقف امامه عقبة ..

اذا فما هو الحل النهائي ؟!

يجب ان نستخدم معادلة تصلح مع الحالتين الاولي ( حالة ان مدة عمل الموظف على مدار يوم واحد ) والثانية  ( حالة ان مدة عمل الموظف علي مدار يومين )

الحل هو استخدام دالة IF وستكون بهذا الشكل :

=IF(E8>=D8,TEXT(E8-D8,”hh:mm”),TEXT(E8+1-D8,”hh:mm”))

شرح المعادلة :

بكل بساطة فهذه المعادلة دمج بين معادلة الحالة الاولى ومعادلة الحالة الثانية .. حيث ان إذا كانت ساعة الانصراف اكبر من او تساوي ساعة الحضور _ وهي حالة ان مدة عمل الموظف علي مدار يوم واحد _ يتم استخدام معادلة الحالة الاولي واذا كان غير ذلك سيقوم تلقائياً بإستخدام معادلة الحالة الثانية

حساب أجر عدد ساعات العمل

وبعد ان قُمنا بحساب عدد ساعات العمل للموظف نريد الآن حساب أجر هذه الساعات .. والطريقة كالاتي :

1- نقوم بإضافة عمود لإجمالي الاجر وتم تسميته ب “القيمة”

2- سنستخدم في عمود القيمة هذه المعادلة :

=F8*G8*24

شرح المعادلة :

هي عملية ضرب عدد ساعات الحضور F8 في اجر الساعة G8 في 24

و 24 هنا تشير إلي 24 ساعة

حساب ساعات العمل الاضافي في Excel

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

في هذه الحالة

عدد الساعات الاضافية = اجمالي ساعات الحضور – ساعات العمل الأساسية

ولطريقة حساب ساعات العمل الاضافي سنقوم بعمل الاتي :

1- عمود خاص بعدد ساعات العمل الاضافي

2- وقمنا بعمل خلية لساعات العمل الأساسي وهي الخلية J5

بإفتراض ان عدد ساعات العمل الاساسية هي 5 ساعات

قُمنا بتغيير تنسيق الخلية الي هذا التنسيق المُشار اليه في الصورة التالية

وكتابة عدد الساعات العمل الاساسية بهذا الشكل 5:00:00   hh:mm:ss  ساعات : دقائق : ثواني

3- سنستخدم لحساب عدد ساعات العمل الاضافي هذه المعادلة في عمود عدد ساعات العمل الاضافي :

=MAX(F8-$J$5,0)

عناصر المعادلة هي : 

دالة max

F8 = عدد ساعات الحضور

J5 = عدد ساعات العمل الاساسية

0 = رقم ثابت

شرح المعادلة :

معادلة Max وهي المسؤولة عن ايجاد ايهم ذو قيمة اكبر

وفي حالتنا هذه القيمة الاولي هي ( نتيجة طرح عدد ساعات الحضور F8 – عدد ساعات العمل الاساسية J5 = ساعات العمل الإضافية) اما القيمة الثانية وهي ( الصفر )..

حيث اذا كان عدد ساعات العمل الإضافية = صفر يعطي النتيجة صفر

واذا كانت سالبة عندما تكون عدد ساعات الحضور اقل من ساعات العمل الاساسية تكون النتيجة صفر ايضاً لأن الصفر اكبر من اي رقم سالب

واذا كان غير ذلك يعطينا عدد ساعات العمل الاضافية الفعلية لانه بالطبع سيكون اكبر من الصفر

 

4- عمل عمود خاص بأجر ساعات العمل الاضافي

5- تعديل معادلة عمود القيمة الي هذا الشكل لتقوم بحساب ساعات العمل الاضافي بشكل صحيح ..

حيث ان في هذه الحالة

قيمة اجر الموظف = (عدد ساعات العمل الاساسية * أجرها) + (عدد ساعات العمل الاضافية * أجرها)

=(F8-G8)*H8*24+(G8*I8*24)

شرح المعادلة : 

F8 = عدد ساعات الحضور

G8 = عدد ساعات العمل الاضافي

H8 = اجر الساعة الطبيعي

I8 = اجر ساعات العمل الاضافي

اي انها = (عدد ساعات الحضور- عدد الساعات الاضافية ) * اجر الساعة الطبيعي + ( ساعات العمل الاضافي * اجر ساعات العمل الاضافي )

اجمالي عدد ساعات العمل

لإيجاد اجمالي ساعات العمل للموظف .. سنقوم بعمل الآتي :

1- كتابة هذه المعادلة التالية اسفل الجدول في عمود عدد ساعات العمل / الحضور

=TEXT(F8+F9+F10+F11+F12+F13+F14,”hh:mm“)

شرح المعادلة : 

كما قلنا سابقاً ان معادلة Text في هذه الحالة تقوم بتنسيق رقم وتحويله إلى صيغة الوقت hh:mm

حيث ان F8 F9 F10 F11 F12 F13 F14 هي خلايا عمود عدد ساعات العمل

الطريقة الوحيدة المتاحة هي جمع الخلايا يدوياً ( فلن تصلح معادلة sum في هذه الحالة )

 

موضوعات قد تُهمك ايضاً :

• شرح كامل عن تثبيت الخلية في Excel
• تكرار المعادلة الحسابية على الخلايا في excel

من المشاكل التي قد تواجهنا في التعامل مع ملف excel اننا قد نجد بعض البيانات المكررة والتي ليس لها اي فائدة فقط تزيد من مساحة ملف العمل وتشتت التركيز على البيانات المطلوبة ، والطريقة التقليدية للبحث عنها وحذفها هي ان نقوم بالبحث عن العناصر المكررة يدويا او من خلال Ctrl + F ونقوم بعمل تحديد لكل عنصر منها ثم نقوم بحذفها ولكن هذه العملية تكون مرهقة بالاخص عندما يكون هناك عدد كبير من البيانات ، لذلك سنتعرف في هذا الدرس عن اداة لحذف التكرار في excel بكل سهولة .

 

اداة Remove Duplicates لحذف التكرار في Excel

تعتبر من الادوات الرائعة في Excel والتي تقوم بالبحث عن البيانات المكررة وحذفها نهائياً بضغطة زر واحدة

اولاً يجب عليك تحديد او Select المنطقة التي تريد ان تحذف منها الخلايا المكررة

يمكنك الإطلاع علي : كيفية عمل جدول على Excel وتنسيقه بالصور

اذهب الي ” البيانات ” ثم  قسم  ” ادوات البيانات ” ثم  ” ازاله التكرارات ”

وباللغة الانجليزية اذهب الي ” Data ” ثم قسم ” Data Tools ” ثم ” Remove Duplicates ”

ويمكنك اختيار العواميد التي تريد حذف منها البيانات المكررة او تركها كما هي

ثم اضغط علي Ok

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