Tag موقع حل تقني

دالة countif في excel

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

شرح دالة countif

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

حيث ان تُرجع الدالة countif عدد الخلايا التي تحقق شرط معين

صيغة دالة countif :

COUNTIF  ( range ,  criteria  )=

تتكون دالة countif من وسيطين :

range النطاق : وهو النطاق الذي تريد ان تقوم الدالة بالبحث فيه عن المعيار بمعني ( اين تريد ان تبحث )

criteria المعيار : وهو المعيار او الشرط الذي تشترطه في البحث بمعني ( ما الذي تريد البحث عنه )

 

ناتج الدالة :

عدد الخلايا من النطاق range التي تحقق الشرط او المعيار criteria.

 

تطبيق على دالة countif في excel

فمثلاً امامنا جدول يحتوي على أسماء الطلاب وتقديراتهم ونريد إيجاد عدد الطلاب الذين حصلوا على تقدير امتياز و جيد جدا و جيد و مقبول .. نقوم بعمل الاتي :

1- نقوم بعمل جدول صغير يحتوي على المعايير التي نريد ان نقوم بإيجاد عدد وجودها في الجدول وفي حالتنا هذه هي ( امتياز – جيد جدا – جيد – مقبول ) وعمل عمود لـ ” العدد ” .. أي عمل جدول مثل الموجود بالصورة التالية :

 

2- نقوم بكتابة دالة COUNTIF بالشكل التالي

• نقوم بكتابة يساوي = ثم COUNTIF مع فتح القوس
• نقوم بتحديد الجدول الذي نريد بالبحث فيه ثم نقوم بتثبيت الجدول
• كتابة علامة الفاصلة هذه , من خلال shift + حرف الواو
• ثم اختيار الخلية التي ستبحث الدالة عن عدد وجودها في الجدول

لتصبح الدالة بالشكل التالي

=COUNTIF ( $H$3:$H$10 , L5 )

للتعرف على كيفية كتابة الدوال في excel بالتفصيل قُم بزيارة هذه الموضوع :

أهم 8 أساسيات لتعلم إدخال معادلات excel للمبتدئين

شرح دالة COUNTIF : 

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

– $H$3:$H$10 : هو الجدول الذي يحتوي على البيانات التي نريد ان نقوم بعَدّ عناصر منها ( range النطاق )

– L5  : هي الخلية الخاصة بالعنصر الذي نريد البحث عنه ( criteria المعيار ) ، مع العلم يمكن كتابته يدوياً بدلا من كتابته في خليه منفصلة ولكن بطبيعة الحال يُفضل كتابة المعايير في خلايا لوحدها من خلال الكتابة وذلك من خلال كتابته بين اقواس مزدوجة ” “

مع ملاحظة انه تم تثبيت الجدول حتي لا يحدث مشاكل عند تكرار الدالة في خلايا اخري

 

 

 

 

 

 

 

ثم نقوم بالضغط علي زر enter

 

3- نقوم بتكرار الدالة حتي نقوم بإيجاد عدد كل تقدير

ملاحظات على دالة CountIf

1- المعيار criteria يمكن ان يكون خلية معينة او نص او رقم او علامة من العلامات الحسابية

2- عند كتابة criteria المعيار داخل الدالة نفسها ( أي ليس اختيار خلية اخري ) يجب كتابتها في ” ” علامات اقتباس مزدوجة من خلال زر shift مع حرف ال ط في الكيبورد

3- يمكن البحث عن المعيار criteria من خلال العلامات الحسابية و رقم ( اكبر من – اصغر من – اكبر من او يساوي – اصغر من او يساوي – يساوي ) .. مثلاً نريد كتابة :

اكبر من 50 فتُكتب بين علامتين اقتباس “>50”

مع العلم العلامات الحسابية هي :

< اصغر من

> اكبر من

<= اصغر من او تساوي

>= اكبر من او تساوي

<> لا تساوي

4- يمكن عمل المعايير من خلال قائمة منسدلة

5- يجب تثبيت ال range بالأخص عند الحاجة لتكرار الدالة

6- اذا كنت تريد عَد الخلايا التي بها كلمة ” جيد ” حتي لو كان مقترنة بكلمة أخري فتكون بالشكل التالي :

=COUNTIF( $F$2:$H$10 , “*جيد*” )

7- اذا كنت تريد عَد خلايا أسماء الطلاب التي تبدأ بها كلمة ” محمد ” فتكون صيغة الدالة بالشكل التالي :

=COUNTIF( $F$2:$H$10, “محمد*” )

دالة Countifs بشرطين او اكثر

اذا كان المطلوب هو إيجاد عدد الخلايا التي تحتوي على شرطين معاً ، فمثلاً :

امامنا الجدول التالي ونريد ايجاد عدد الطلاب الذين حصلوا على تقدير امتياز مع ان يكونوا في الصف الأول ، عندئذ يكون استخدام دالة CountIfS وذلك من خلال الاتي :

 

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

2- كتابة دالة countifs بالشكل التالي :

=COUNTIFS ( $H$3:$H$10 , L5 , $F$3:$F$10 , K5 )

بعد كتابة =COUNTIFS نقوم باختيار العمود الذي يحتوي على المعيار الأول وفي حالتا هذه هو عمود التقديرات ، ثم نقوم بتحديد المعيار الأول ، ثم نقوم بتحديد العمود الذي يحتوي على المعيار الثاني وهو عمود ” الصف ” ثم تحديد المعيار الثاني

 

شرح دالة countifs :

COUNTIFS : دالة إيجاد عدد الخلايا التي تحقق فيها شرطين او اكثر

$H$3:$H$10 : هو العمود الذي يحتوي على التقديرات ( criteria_range1 ) نطاق المعيار الأول

L5 : هي خلية المعيار او الشرط الأول ( criteria1 )

$F$3:$F$10 : هو العمود الذي يحتوي علي ” الصفوف ” ( criteria_range2 ) نطاق المعيار الثاني

K5 : هي خلية المعيار او الشرط الثاني ( criteria2 )

 

صيغة دالة COUNTIFs :

COUNTIFs ( criteria_range1 , criteria1 , [criteria_range2 , criteria2] ,…)

 

يجب ملاحظة الاتي :

– مما سبق يتضح لنا ان criteria_range1 و criteria1 هما إلزاميان أما أي شروط اخري فهي اختيارية .

– تقوم دالة COUNTIFs بإيجاد عدد الخلايا التي تجتمع بها الشرطين criteria1 و criteria2 او اكثر الذي تم اضافتهم .

 

وهكذا قد قُمنا بشرح ميسر ومُفصل لـ دالة countif في excel و دالة countifs تكملة لما قُمنا بشرحه عن موضوع دوال IF في الموضوع السابق والذي انصحك بشدة ان تقوم بالمرور عليه لما فيه من الاستفادة التي ستحصل عليها منه وهو موضوع :

 شرح مبسط عن دالة IF في excel بالأمثلة

 

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

• كيفية عمل قائمة منسدلة تقبل البحث
• شرح مبسط عن دالة IF في 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 من اهم طرق تحليل البيانات حيث ان أدوات تحليل البيانات تعتبر من المهام الرئيسية في برنامج Microsoft Excel حيث تُستخدم أداة الفرز Sort لترتيب جداول البيانات حسب معيار محدد اما أداة التصفية Filter تستخدم لعرض جزء معين من البيانات والتي ينطبق عليها معيار محدد أيضاً ، ولتوضيح الفرق بين الفرز والتصفية نوضح ذلك من خلال المثالين التاليين فمثلاً لديك جدول ببيانات الطلاب بالأسماء والصفوف وتريد عرض بيانات الطلاب الموجودين في الصف الأول فقط فهنا يمكننا استخدام أداة filter اما اذا كنت تريد ترتيب أسماء هذه الطلاب حسب الترتيب الابجدي فهنا علينا باستخدام أداة sort ، لذلك سنتعرف في درس اليوم عن شرح وافي ومبسط عن كيفية استخدام أداة الفرز sort و أداة التصفية filter في فرز البيانات وتصفيتها في excel و الخصائص المتنوعة التي تشملها تلك الاداتين .

كيفية فرز البيانات Sort

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

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

1- ترتيب الموظفين حسب تاريخ التعيين من الاقدم للأحدث

2- ترتيب الموظفين حسب الترتيب الابجدي أي أ ب ت وهكذا

3- ترتيب الموظفين حسب مرتباتهم من الأقل للأعلى

قبل أي شيء علينا ان نعرف كيف يمكننا الوصول الي أداة الفرز

هناك ثلاثة طرق يمكننا فرز البيانات sort من خلالهم ، الطريقة الاولي :

– بعد ان نقوم بتظليل الجدول المطلوب عمل فرز له .. من خلال تبويب Data بيانات في قسم sort & filter فرز وتصفية قم باختيار خيار sort فرز

الطريقة الثانية للوصول لأداة sort الفرز وهي :

– بعد ان نقوم بتظليل الجدول المطلوب عمل فرز له .. من خلال الشريط الرئيسي Home في قسم Editing التحرير قم باختيار sort & filter او الفرز والتصفية ومنها custom sort فرز مخصص

الطريقة الثالثة للوصول لأداة sort الفرز وهي :

– قم بتظليل الجدول المطلوب عمل فرز له ثم قم بالضغط على كليك يمين واختر sort او فرز ثم اختر custom sort او فرز مخصص

من المطلوب في المثال السابق يتضح لنا أنواع الفرز في برنامج Excel فهذه هي المعايير التي يتم فرز البيانات على أساسها :

1- فرز من الاقدم للأحدث Sort Oldest to Newest او العكس فرز من الاحدث للأقدم Sort Newest to Oldest

2- فرز حسب الترتيب الابجدي Sort A to Z او العكس Sort Z to A

3- فرز من الأصغر الي الأكبر Sort Smallest to Largest او العكس فرز من الأكبر للأصغر Sort Largest to Smallest

أولاً : ترتيب الموظفين حسب تاريخ التعيين من الاقدم للأحدث

– بعد فتح أداة فرز البيانات بأي من الطرق السابق ذكرها ستظهر لنا هذه النافذة في الصورة التالية

بطبيعة الحال فإن الجدول الذي موجود بين أيدينا الان له مقدمة header ( وهي المكتوب بها تاريخ التعيين والاسم وخلافه .. ) لذلك سنقوم بتحديد خيار My data has header او تحتوي البيانات على رؤوس ، عندها ستظهر رؤوس الجدول معنا في قائمة sort by مخصص حسب

من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” تاريخ التعيين ”

ومن قائمة order او الترتيب اختر Oldest to Newest او من الأقدم للأحدث

ثم الضغط على ok

سنلاحظ حينها انه تم ترتيب بيانات الموظفين حسب تاريخ التعيين من الأقدم للأحدث ( ليس فقط عمود التاريخ بل جميع الاعمدة والتي تم تظليلها من البداية )

ثانياً : ترتيب الموظفين حسب الترتيب الابجدي أي أ ب ت ( ترتيب الأسماء ابجديا Excel )

1- بعد فتح أداة sort قم بتحديد خيار My data has header او تحتوي البيانات على رؤوس

2- من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” الاسم ”

3- ومن قائمة order او الترتيب اختر A to Z او أ الي ي

ثالثاً : ترتيب الموظفين حسب مرتباتهم من الأقل للأعلى

1- بعد فتح أداة sort قم بتحديد خيار My data has header او تحتوي البيانات على رؤوس

2- من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” صافي المرتب ”

3- ومن قائمة order او الترتيب اختر Smallest to Largest او من الأصغر الي الأكبر

نلاحظ مما سبق ان :

• أيا كانت طريقة الفرز المطلوبة فإن الوصول الي أداة الفرز في اكسل واحدة
• و نلاحظ انه تختلف خيارات الفرز ( order / الترتيب ) حسب نوع البيانات ( sort by / فرز حسب ) سواء كانت نص أو رقم او تاريخ حيث ان :

عندما يكون الذي يتم الفرز على اساسه نص يكون الترتيب حسب الترتيب الابجدي Sort A to Z والعكس

وعندما يكون الفرز هو ارقام يكون الترتيب Sort Smallest to Largest من الأصغر الي الأكبر والعكس

واذا كان الفرز هو تاريخ يكون الترتيب Sort Oldest to Newest من الاقدم للأحدث والعكس

خيارات متقدمة في فرز البيانات في excel

– فرز عمودين او أكثر حيث يمكننا عمل ذلك من خلال اختيار add level او إضافة مستوي واتباع نفس الخطوات الفرز

– فرز حسب قائمة مخصصة Custom List

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

عندئذ نقوم بعمل الاتي :

1- بعد فتح أداة sort قم بتحديد خيار My data has header او تحتوي البيانات على رؤوس

2- من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” القسم التابع له ”

3- ومن قائمة order او الترتيب اختر قائمة مخصصة او Custom List

4-ومن النافذة التي ستظهر لك قم بكتابة الأقسام بالترتيب الذي تريده في قسم list entries او ادخالات القائمة ثم قم بالضغط علي add اضافة ثم اخترها من custom lists او قوائم مخصصة ثم الضغط علي ok ثم ok مرة اخري

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

– فرز البيانات حسب تنسيق الخلية مثل لون النص او لون الخلية وذلك من خلال الاتي :

تعديل sort on الفرز الي cell color لون الخلية لفرز البيانات حسب لون الخلية

او font color لون الخط لفرز البيانات حسب لون الخط

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

– فرز الصفوف في excel

يمكننا فرز الصفوف كما نقوم بفرز الاعمدة وذلك من خلال الاتي :

– بعد فتح أداة sort الفرز قم بالضغط على Options خيارات ثم قم بتحديد خيار Sort Right to Left الفرز من اليمين الي اليسار ثم ok

مع اتباع اياً من الحالات السابقة لفرز الاعمدة

كيفية تصفية البيانات Filter

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

طريقة الوصول لأداة التصفية Filter في اكسل

يوجد طريقتان للوصول لأداة التصفية ، الطريقة الاولي :

– بعد ان نقوم بتظليل الجدول المطلوب عمل تصفية له .. من خلال الشريط الرئيسي Home في قسم Editing التحرير قم باختيار sort & filter او الفرز والتصفية ومنها اختر Filter تصفية

الطريقة الثانية للوصول لأداة التصفية :

– بعد ان نقوم بتظليل الجدول المطلوب عمل تصفية له .. من خلال تبويب Data بيانات في قسم sort & filter فرز وتصفية قم باختيار خيار filter تصفية

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

تطبيق مثال :

فمثلاً عندنا جدول للموظفين ( مثل الجدول السابق ) ونريد عرض الموظفين الذين يعملون بوظيفة باحث و وظيفة مصحح فقط ففي هذه الحالة علينا باستخدام أداة filter التصفية لأننا نريد عرض لجزء من جدول البيانات لذلك فنقوم بعمل الاتي :

1- استخدام أداة التصفية filter بأي طريقة من الطريقتين السابقتين

2- الضغط على السهم الصغير الموجود عند عمود ” القسم التابع له ” ثم الضغط على select all لإزالة التحديد عن الكل ثم نقوم بالتحديد على العناصر التي نريد عرضها فقط ثم الضغط على ok

ليظهر معك الجدول بالشكل التالي :

وهذه التصفية تسمي بـ ” التصفية التلقائية AutoFilter “

المعايير الخاصة بأداة filter للتصفية

وسنتناول الان المعايير الخاصة بأداة filter للتصفية والتي يتم عرض البيانات على أساسها

بعد الضغط على السهم الخاص بأداة التصفية ستظهر لنا هذه القائمة

– Filter by color التصفية حسب اللون

وهو لعمل تصفية وعرض للخلايا الملونة بلون معين او حتي التي بدون لون blank

– Number Filters عوامل تصفية الأرقام

وهي تقوم بعمل تصفية للأرقام حسب معايير محددة ، فعند الوقوف عليها ستظهر لنا هذه القائمة :

وهذا هو معني كل اختيار من هذه الاختيارات

موضوع قد يهمك : تنسيق الأرقام في Excel

وعند الضغط على أي خيار منهم يظهر لك هذه النافذة :

فيمكن أيضاً تعديل الاختيار الذي قمت باختياره من هذه القائمة من خلال المستطيل رقم 1

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

ثم الضغط على ok

مع العلم ان هذه القائمة تختلف عندما يكون المحتوي عبارة عن نص وتختلف أيضاً عندما يكون المحتوي عبارة عن تاريخ ، سنتعرف الان على عوامل تصفية النصوص text filters ووظيفة كل واحدة منها و أيضاً سنتعرف علي عوامل تصفية التاريخ date filters ووظيفة كل واحدة منها من خلال الصورتين التاليين
– عوامل تصفية النصوص text filters

– عوامل تصفية التاريخ date filters

موضوع ذو صلة : كيفية تعديل صيغة التاريخ Excel

موضوع ذو علاقة يمكنك الاطلاع علي :

اسهل طريقة لحذف التكرار في excel

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

ملاحظات علي التصفية في اكسل

1- إذا كانت هناك بعض الخلايا الفارغة في الجدول سيقوم البرنامج بتضمين هذا الخيار في قائمة التصفية بإسم ( Blanks ) او (فراغات)

2- لإيقاف التصفية التلقائية Auto filter بشكل تام من خلال تبويب Data بيانات في قسم sort & filter فرز وتصفية قم باختيار خيار filter تصفية

3- لعمل جدول بيانات بدون حدوث اخطاء بالاخص مع اداة filter يُفضل إلغاء اي دمج merge في الخلايا

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

– شرح مبسط عن دالة IF في excel بالأمثلة

– شرح دالة vlookup بالصور

دالة IF في Excel

تعتبر من الدوال المنطقية في برنامج مايكروسوفت اكسل والتي تقوم بإجراء اختبار منطقي تشترط فيه حدوث امر معين حتى يتم عمل امر آخر واذا لم يتم حدوثه فيحدث امر مختلف، فمثلاً نريد دالة تختبر ما اذا كانت نتيجة الطالب تخطت ال 125 درجة لكي يُكتب ناجح واذا لم تتخطي هذه الدرجة يُكتب راسب فهنا الشرط هو تخطي ال 125 درجة والامر الذي ستفعله الدالة اذا حدث هذا الشرط هو كتابة ” ناجح ” والامر الذي ستفعله الدالة اذا لم يحدث هو كتابة ” راسب ” ، لذلك فتعتبر دالة IF من ضمن الدوال الشرطية و المنطقية الأكثر استخداماً في برنامج Excel ، نتيجة للتعدد والمدي الواسع في استخداماتها فهي أيضاً غير مرتبطة بالأرقام فقط بل النصوص أيضاً وأي قيم عامة ، حيث تساعد دالة IF في الاكسل على إجراء الحسابات بشكل أكثر دقة وفعالية وتوفير الوقت والجهد في إدارة البيانات ، لذلك سنتعرف في هذا الدرس عن شرح دالة IF في excel شرح مبسط مُدعم بالأمثلة والتطبيقات .

كيف يتم استخدام دالة IF

دالة IF تعتبر كباقي الدوال لها صيغة معينة يجب اتباعها وهي :

=IF(logical_test; [value.if true]; [value_if false])

دعونا نتعرف ببساطة عن شرح هذه الصيغة

IF تعني ” لو ”
وسيطات دالة IF هي

• logical test او الاختبار المنطقي وهو الشرط الذى نقوم بإجراء المقارنة عليه
• value if true ( حالة تحقق الشرط ) هو القيمة التي ستظهر لو تم تحقق الشرط
• value if false (حالة عدم تحقق الشرط ) هو القيمة التي ستظهر لو لم يتم تحقق الشرط

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

1- كتابة يساوي في الخلية التي تريد ان يظهر بها نتيجة الدالة

2- اكتب if ثم الضغط على زر Tab

3- تحديد ال logical test وهو يمكن ان يكون دالة او خلية معينة

ثم كتابة رمز { ; } ( وهو موجود في حرف الكاف بعد تحويل اللغة الي الإنجليزية ) او الرمز { , } ( الموجود في حرف الواو بعد تحويل اللغة الي الإنجليزية )
ملحوظة

• يتم تحديد الرمز { ; } او { , } حسب الظاهر امامك في الصيغة الخاصة بالدالة حيث يختلف ذلك حسب اصدار الاكسل .

4- تحديد ال value if true وهو يمكن ان يكون دالة او خلية معينة او نص او رقم

5- تحديد ال value if false وهو يمكن ان يكون دالة او خلية معينة او نص او رقم

وهذا يرد على سؤال هل يمكن ان تكون نتيجة دالة IF أي شيء مثل نص أو رقم أو دالة أخرى؟

والان سنتعرف على مثال حي يمكننا تطبيق هذه الخطوات عليه ..

دالة IF البسيطة

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

1- كتابة الدالة كما تعرفنا في الفقرة السابقة بالشكل التالي :

=IF(J9>=60,”سلبي”,”إيجابي”)

حيث ان :

• J9 : هي خلية نسبة التحليل
• 60=< : اكبر من او تساوي

حيث يعتبر “J9>=60 ” هو الـ logical test ( الشرط الذى نقوم بإجراء المقارنة عليه )

• “إيجابي” هي الكلمة التي ستُكتب لو تم تحقق الشرط – value if true ( حالة تحقق الشرط )
• “سلبي” هي الكلمة التي ستُكتب لو لم يتم تحقق الشرط – value if false (حالة عدم تحقق الشرط )

مع العلم انه اذا كانت حالة تحقق الشرط او حالة عدم تحقق الشرط عبارة عن نص فـيجب كتابة النص بين double quotation marks علامات الاقتباس المزدوجة ” ”

2- تكرار المعادلة في باقي الخلايا لتصبح بالشكل التالي :

دالة If المركبة / المتداخلة

دالة IF المركبة او المتداخلة وهي تداخل اكثر من دالة IF في دالة واحدة وبالتالي يتم تحديد الشرط الأول في الدالة الأولى، وفي حال عدم تحققه يتم الانتقال إلى الدالة الثانية وهكذا لذلك تسمي بـ دالة IF بشرطين او اكثر ـ فمثلاً ..

اذا كان عندنا جدول يحتوي على قائمة بأسعار المنتجات ونريد عمل خصم على المنتجات بناءاً على الكمية المشتراه حيث ان اذا كان الكمية المشتراه اكثر من 50 كرتونة يكون الخصم 20% واذا كان اكثر من 20 كرتونة يكون الخصم 10% واذا كانت الكمية المشتراه اكثر من 10 كراتين يكون الخصم 5% واذا كان اقل من 10 لا يكون هناك خصم ..

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

=IF( E5>=$M$5 , $K$5 , IF(E5>=$M$6 ,$K$6 , IF(E5>=$M$7,$K$7,0 )))

كما في الصورة التالية :

شرح دالة IF المركبة :

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

الجزء الأول : =IF( E5>=$M$5 , $K$5

الجزء الثاني : IF(E5>=$M$6 ,$K$6

الجزء الثالث : IF(E5>=$M$7,$K$7,0)))

حيث ان :

E5 هي خلية الكمية المشتراه

M5 هي خلية مكتوب بها عدد الكراتين 50

K5 هي خلية مكتوب بها النسبة 20%

الجزء الأول وهو يعني ان لو ان الكمية E5 اكبر من او تساوي الخلية 5$M$ ( وهي خلية مكتوب بها 50 يقصد 50 كرتونة ) تكون النتيجة هي الخلية 5$K$ ( وهي خلية نسبة الخصم 20% )

في حالة عدم تحقق الشرط سيتم الانتقال الي الجزء الثاني وهو يعني
لو ان الكمية E5 اكبر من او تساوي الخلية 6$M$ ( وهي خلية مكتوب بها 20 ) تكون النتيجة هي الخلية 6$K$ ( وهي خلية نسبة الخصم 10% )

وفي حالة عدم تحقق الشرط سيتم الانتقال الي الجزء الثالث وهو يعني
لو ان الكمية E5 اكبر من او تساوي الخلية 7$M$ ( وهي خلية مكتوب بها 10 ) تكون النتيجة هي الخلية 7$K$ ( وهي خلية نسبة الخصم 5% )

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

يجب ملاحظة الاتي :

1- مع كل شرط جديد يتم فتح دالة IF جديدة

2- متاح إضافة شروط أيا كان عددها

3- علامة اكبر من او اصغر من تُكتب قبل علامة يساوي

4- تم تثبيت خلية نسبة الخصم و عدد الكراتين حتي تتم عملية تكرار المعادلة بنجاح

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

5- يتم غلق دالة IF بعدد الاقواس التي تم فتحها بمعني انه اذا تم فتح 3 اقواس يتم غلق 3 اقواس وهكذا ..

يمكنك مراجعة الدالة والمثال من خلال تحميل ملف الشرح من هنا

دالة IFERROR

تستخدم دالة IFERROR لمعالجة الأخطاء التي قد تحدث في دالة اكسل ، فهي تختبر ما اذا كانت الدالة تعطي خطأ ام لا حيث انها اذا أعطت خطأ تقوم دالة IFERROR بإرجاع قيمة محددة بدلاً من رسالة الخطأ value# ، علي سبيل المثال :

لدينا جدول يعتمد علي تضريب الكمية في السعر لإعطاءنا الإجمالي ، فبطبيعة الحال لا تستطيع دالة الضرب بضرب الرقم في نص وبالتالي يظهر لنا الخطأ value# كما هو موضح بالصورة التالية

ولإخفاء هذا الخطأ نقوم باستخدام دالة IFERROR من خلال كتابة الدالة قبل دالة الضرب لكي تظهر بالشكل التالي :

=IFERROR(H8*I8,””)

حيث ان :

صيغة دالة IF Error هي

=IFERROR(value,value_if_error)

value هي الدالة المطلوب إخفاء الأخطاء بها

value_if_error هو المطلوب كتابته عند ظهور خطأ وفي حالتنا هذه نريد ان لا يظهر شيء لذلك قمنا بكتابة علامة الاقتباس المزدوجة “”

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

استخدامات دالة IF

بعد ان تعرفنا على كيفية استخدام دالة IF في Excel نود ان نتعرف في عُجالة عن ماهيه اهم الأمور التي يجب ان نستخدم بها دالة IF

1- التحقق من صحة البيانات وتنظيمها

2- التحقق من ان قيم معينة تقع ضمن نطاق معين او انها تلبي شروطاً محددة ام لا

3- تصنيف و فرز البيانات حسب معايير محددة

4- انشاء تقارير تفصيلية بناءا على شروط محددة مثل تقارير المبيعات او غيرها

5- في مجال التعليم : فرز درجات الطلاب من حيث الناجح و الراسب

6- في مجال المرتبات والأجور : فرز الموظفين من حيث الراتب منخفض او متوسط او مرتفع

وغيرها من المجالات التي استخدامنا فيها لدالة IF بشكل من الاشكال يضيف له ويوفر عليه الكثير من الوقت والمجهود .

المشاكل التي يمكن ان تواجهك عند استخدام دالة IF

المشاكل الشائعة في دالة IF

1- عندما يكون النص الموجود في جدول البيانات يحتوي على مسافة فبالتالي لن تعمل دالة IF بشكل صحيح ، فمثلا لدينا جدول يقوم بتصنيف الملابس الي اطفالي و رجالي فإذا كان اطفالي يكون الخصم 20% واذا كان رجالي يكون الخصم 15% ، كما هو موضح بالصورة

بالنظر في المثال نلاحظ ان ” التيشرت ” اخذ نسبة خصم 20% رغم انه رجالي وذلك بسبب وضع مسافة بالخطأ مع كلمة ” رجالي ” ، ولحل هذه المشكلة نستخدم دالة TRIM التي تقوم بإزالة أي مسافة يمكن ان تكون موجودة في logical test وذلك من خلال تعديل الدالة لتصبح بالشكل التالي :

من خلال كتابة TRIM قبل logical test

ثم تكرار الدالة في جميع الخلايا

2- لإرجاع قيمة نصية في دالة IF تحتاج الي إحاطة النص بعلامات الاقتباس المزدوجة ” ” ، لتكون شكل دالة IF بهذا الشكل

=IF(J9>=50,“راسب“,“ناجح“)

مع العلم انه عند ارجاع قيم منطقية لا نحتاج لإحاطة القيم بعلامات الاقتباس المزدوجة ، هذه القيم مثل True و False لتكون شكل الدالة كالآتي :

=IF(J10>=60,TRUE,FALSE)

3- لإرجاع فراغ في دالة IF تحتاج الي كتابة علامات الاقتباس المزدوجة بهذا الشكل “”

4- ليس فقط الرموز التي يمكن استخدامها هي اكبر من و اصغر من ، فهناك رموز اخري يمكننا استخدامها لتسهل علينا بعض الأمور والتي سنتعرف عليها الان :

= يساوي

<> لا يساوي

< اكبر من

> اصغر من

=< اكبر من او يساوي

=> اصغر من او يساوي

علي سبيل المثال :

=IF(H12<>0,”Not zero”,”zero”)

هذه الدالة البسيطة تختبر ما اذا كان الموجود في الخلية H12 هو صفر ام لا
5- خطأ #NAME؟ : يعني هذا الخطأ هو وجود خطأ كتابي في صيغة الدالة

كما عودناكم فإن جميع الأمثلة التي تم ذِكرها في الشرح بالدوال موجودة في الملف التالي برابط تحميل مباشر

رابط تحميل ملف الشرح

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

• كيفية حساب العمر في excel
• تحميل برنامج محاسبة مجاني Excel بسيط

دالة Vlookup

Vlookup هو اختصار لـ Vertical يعني العمودي و lookup يعني البحث (البحث العمودي) فهي دالة بحث من دوال البحث التي يوفرها برنامج Microsoft Excel فهي تستخدم للبحث عن قيمة معينة (القيمة المبحوث عنها ) في عمود محدد من خلال الاعتماد على قيمة أخرى معلومة مقابلة لها في نفس الصف فهذه العملية تتم علي جدول يحتوي علي بيانات مختلفة ، اي اذا كان لديك كمية كبيرة من البيانات وتريد جلب معلومة او اكثر من هذه البيانات ، لذلك فإن دالة Vlookup تعتبر من اهم الدوال واكثر الدوال استخداماً في برنامج Microsoft excel حيث يمكن استخدامها في البحث عن رواتب الموظفين او درجات الطلاب او أسعار المنتجات وغيرها من الاستخدامات حيث ان دالة vlookup لا غنى  عنها أياً كان المجال الخاص بك ، لذلك سنتحدث في هذا الموضوع عن شرح دالة vlookup بالصور التوضيحية و تطبيقات عملية و الأخطاء التي قد تواجهك عند استخدام دالة vlookup .

شرح دالة Vlookup

قبل ان نقوم بشرح الدالة نود ان نعرف اولاً ما هي صيغة دالة Vlookup ، فالدالة تتكون من اربع بنود

تطبيق على دالة Vlookup

ولكي يتضح لنا بشكل مبسط شرح دالة Vlookup نقوم بتطبيق مثال بسيط يوضح لنا ذلك :

مثلاً لدينا جدول بأسماء الطلاب و درجاتهم في كل مادة

كما عرفنا ان دالة Vlookup تشمل على اربعة بنود ، سنتطرق الآن الي كل بند بها مع شرحه من خلال التطبيق العملي :

1- اسم الطالب وهو lookup value وهو عبارة عن خلية محددة تكتب فيها اسم الطالب الذي تريد معرفة درجته ولتكن هذه الخلية مثلا D11 و ليكن اسم الطالب هو ” حسن ”

مع العلم : 

– اسم الطالب هو القيمة المعلومة

– درجة المادة هي القيمة المبحوث عنها

2- الجدول الذي ستقوم الدالة بالبحث فيه وهو table array ( نقوم بتحديد الجدول بالكامل ) ، واذا كنا سنقوم بتكرار المعادلة على اكثر من خلية فيجب تثبيت الجدول من خلال الضغط علي زر F4

 

3- تحديد العمود الذي تريد الدالة بالبحث فيه وهو col index num فمثلاً نريد معرفة درجة مادة الرياضيات فكيف نقوم بتحديد العمود ؟

دالة vlookup لا يمكنها تحديد العمود يدوي ولكن يتم التحديد من خلال رقم العمود وذلك من خلال الاتي :

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

فيكون عمود الرياضيات هو رقم 3 ، اذا فـ col index num تساوي 3

سؤال : 

كيف نعرف اذا كان الشيت من اليمين الي اليسار او العكس بطريقة سهلة ؟

اذا كان العمود A يمين الشيت اذا فهو من اليمين الي اليسار واذا كان العمود A في اليسار اذا فاتجاه الشيت من اليسار الي اليمين

 

4- اما خانة [range_lookup] فهي المسؤولة عن دقة النتائج ، لجعل النتيجة دقيقة 100% اكتب 0 او False ، اما في حالات معينة قد نحتاج الي نتائج تقريبية فنكتب 1 او True .

بعد ادخال هذه المدخلات فتصبح المعادلة بالشكل التالي :

=VLOOKUP(   D11  ,  $B$2:$H$8 ,  3  ,   FALSE  )

 

ملحوظة : 

يمكنك عمل قائمة منسدلة في خلية ” اسم الطالب ” بدلا من كتابة الاسم في كل مرة ، ولعمل قائمة منسدلة قم بزيارة هذا الموضوع : تعديل قائمة منسدلة في Excel

 

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

 

رابط تحميل تطبيق دالة Vlookup 

رابط التحميل

كيفية استخدام الدالة vlookup في excel

يوجد طريقتان لاستخدام دالة vlookup ، الطريقة الاولي 

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

1- كتابة علامة يساوي ثم كتابة vlookup والضغط على زر tab

2- ثم اختر الخلية التي ستكتب بها اسم الطالب الذي تريد معرفة درجته

وبعد تحويل لغة الكيبورد ( لوحة المفاتيح ) الي اللغة الإنجليزية قم بالضغط على زر حرف الكاف لكتابة العلامة الفاصلة بين كل مدخل وهي هذه العلامة ;

او العلامة , الموجودة في حرف الواو  على حسب اصدار الاكسل عندك

3- ثم قم بتحديد الجدول بأكمله ثم العلامة ; او ,

4- قم بكتابة رقم العمود الخاص بالمادة التي تريد ان تعرف درجتها ثم العلامة ; او ,

5- ثم قم باختيار false ثم اغلق المعادلة من خلال علامة القوس (

 

الطريقة الثانية لاستخدام دالة vlookup

1- من خلال الضغط على علامة fx المشار اليها في الصورة التالية واختر من select category اختر ALL واكتب في مربع البحث Vlookup وقم بالضغط على GO ، ستظهر لك الدالة بالأسفل قم باختيارها

2- سيظهر لك هذه النافذة

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

وفي خانة table array قم بتحديد الجدول بالكامل وقم بتثبيته من خلال زر F4

وفي خانة col index num اكتب رقم العمود الذي تريد معرفة درجة المادة كما تعرفنا منذ قليل

وفي خانة [range_lookup] اكتب 0

ثم اضغط OK

استخدامات دالة vlookup

1- اذا كان لديك الكثير من الموظفين ولديك كشف بمعلومات عنهم وتريد معرفة معلومة معينة بدون مجهود .

2- اذا كنت مدرس ولديك كشف بأسماء الطلاب و معلومات عنهم او درجاتهم او تقديراتهم .

3- اذا كنت تاجر ولديك قائمة كبيرة من أسعار المنتجات وتريد معرفة سعر منتج بعينة بدون عناء البحث .

4- اذا كنت تعمل في مكتب محاسبة فهي لا غني عنه

5- بشكل عام فهي تفيد اكثر عندما تكون كمية البيانات كبيرة وتريد البحث او جلب معلومة او اكثر من هذه البيانات .

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

دالة vlookup باكثر من شرط

بعد ان فهمنا جيدا طريقة عمل دالة vlookup فعلمنا ان الدالة تبحث من خلال شرط واحد ( اسم الطالب مثلاً ) ، فماذا لو كنا بحاجة للبحث بأكثر من شرط ؟! .. نوضح ذلك من خلال المثال التالي :

لدينا جدول لأسماء الطلاب والفصول الخاصة بهم ولكن هنا أسماء طلاب متكررين كما هو واضح في الصورة التالية ، فهناك اكثر من ” محمد ” واكثر من ” علي ” .. فماذا نفعل ؟!

 

حل المثال 

1- نقوم بعمل عمود قبل الاسم ولنسميه مثلا ” مساعد ” ونكتب به علامة يساوي ثم اختر اسم الطالب ثم اكتب علامة & ثم اختر الفصل ، ليكون شكل الدالة كالاتي :

=C4&D4

معني ذلك اننا قمنا بإضافة الاسم مع الفصل ليكونوا في خلية واحدة

ونقوم بتكرار المعادلة لاسفل

2- وفي الخلية التي سنكتب بها دالة vlookup نقوم بكتابة يساوي vlookup وعند اختيار lookup value نقوم باختيار اسم الطالب ثم كتابة علامة & ثم اختيار الفصل ، واكمال باقي الدالة كما تعرفنا ، لتكون الدالة كالآتي :

=VLOOKUP(  F12&G12   , $B$3:$J$9  ,  4  ,  0  )

 

كما لاحظنا في الصورة السابقة ان المعادلة تمت بنجاح وذلك من خلال اننا قمنا بتعديل دالة vlookup لكي تبحث عن شرطين ( اسم الطالب + الفصل ) وبنفس الطريقة يمكنك تعديل دالة vlookup لكي تبحث عن 3 شروط او اكثر

دالة vlookup لا تعمل

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

1- يجب ان يكون القيمة الذي نبحث عنه في اول عمود في الجدول ( اذا كان الشيت من اليمين الي اليسار فيكون اول عمود في اليمين واذا كان الشيت من اليسار الي اليمين فيكون او عمود في اليسار ) .

2- يجب ان يكون هناك تطابق بين المبحوث عنه في lookup value و الموجود في العمود الأول ، حيث ان اذا كان هناك حرف زيادة او نافص او رقم زيادة او ناقص فستكون النتيجة error خطأ ، لذلك يُنصح دائماً بعمل قائمة منسدلة في الخلية لتفادي هذا الخطأ .

3- يجب ألا يحتوي table array عن اي خلايا مدمجة Merge .

4- افهم جيدا طريقة معرفة رقم العمود في الجدول كما شرحناها في بداية الدرس col index num واذا احتجت الي اي مساعدة لا تتردد في كتابة استفسارك في التعليقات .

5- في حالة اختيار ان تكون المطابقة غير تامة true او 1 ( البند الرابع في المعادلة )  فيجب ان يكون العمود الاول في table array مُرتب ترتيب تصاعدي وإلا ستكون النتيجة خاطئة

خيار المطابقة غير تامة او True يتم استخدامه عندما تكون النتيجة المطلوبة تقريبية ،

فمثلاً لدينا مجموعة من الطلاب و اجمالي درجاتهم ونريد ان نعطي كلاً منهم تقدير حسب درجته ، حيث ان الدرجة النهائية هي 250 ، حينئذ يجب استخدام خيار المطابقة غير التامة لان من التأكيد ان يكون هناك درجات حصلو عليها الطلاب وغير موجودة في الجدول ( انظر الصورة التالية ) ، لذلك فبرنامج الاكسل في هذه الحالة اذا لم يجد الدرجة موجودة ينظر الي الدرجة الأقل منها وبالتالي التقدير الأقل منه وهذا هو المطلوب ..

 

وهذه هي صيغة دالة vlookup المستخدمة :

=VLOOKUP(  C4  ,  $G$6:$H$11  ,  2  ,TRUE)

C4 هي خلية درجات الطالب 

$G$6:$H$11  هو جدول التقديرات 

2 هو رقم عمود التقديرات 

TRUE وهي حالة المطابقة غير التامة

6- لاحظ ان شكل الفاصلة تختلف من اختلاف إصدارات الاكسل ففي البعض يكون  ;   والبعض الاخر يكون   ,    ، فعليك ملاحظة ذلك من خلال التعليمات التي يظهرها لك اكسل عند كتابة دالة vlookup او قم باستخدام الطريقة الثانية التي تم شرحها لكتابة دالة vlookup .

 

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

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

برنامج محاسبة مجاني excel هو عبارة عن شيت اكسل يحتوي على برمجة معينة (ماكرو  Macro ) او لا ، ولكنه ليس كـشيت اكسل عادي بل هو عبارة عن شيت متكامل ومرتبط ببعضه عن طريق المعادلات او الدوال حيث انه بهذا التكامل والتلقائية التي يتميز بها الشيت يكاد يكون يصل الي مستوي البرنامج ، حيث يمكن إنشاء جداول البيانات لتتبع المصروفات و الإيرادات. يمكن أيضًا استخدام برنامج Excel لإدارة الجداول الزمنية و المواعيد الهامة للمشروع ،ومراقبة الأرباح و الخسائر ، وتقديم تقارير مالية مفصلة ، وإدارة الميزانية ، وغيرها من الأمور التي تساعدك في القيام بحل مشاكل تواجهك اثناء مسيرتك في المشروع ، حيث يمكن استخدام برنامج Excel لإدارة حسابات مشروع صغير كان او متوسط الحجم فهو يصلح ايضاً للمحلات والمؤسسات التجارية والشركات ، و تخصيص Excel لتلبية احتياجات المشروع وتحسين إدارته بشكل فعال ، لذلك سنتعرف في موضوع اليوم عن مميزات و استخدامات اكثر من برنامج محاسبة مجاني excel بسيط او اكثر تعقيداً و روابط مباشرة للتحميل .

استخدامات برنامج محاسبة مجاني excel

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

1- تسجيل ومتابعة الإيرادات و المصروفات

2- إدارة وتسجيل فواتير المبيعات و المشتريات

3- قوائم العملاء و الموردين

4-إعداد قوائم الدخل و المركز المالي

5- حساب الأجور و المرتبات و الاستقطاعات و الضرائب

6- إدارة المخزن من خلال متابعة المخزون و البضاعة المباعة و البضاعة المُشتراه .

برنامج محاسبة مجاني excel بسيط للإيرادات و المصروفات ( يومية الخزينة )

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

كيفية استخدام برنامج محاسبة مجاني excel بسيط للايرادات و المصروفات

1- قم بإدخال الرصيد الافتتاحي ( أي الرصيد الموجود بالخزنة بالفعل الان )

2- ابدأ بإدخال الحركات من إيرادات او مصروفات او المقبوضات و المدفوعات ثم البيان ( بيان المصروف او الايراد )

3- حاول بقدر الإمكان ان تكون المسميات في البيان واحدة بمعني ان اذا كنت ستقوم بكتابة مصروف ” كهرباء “، فتقوم بتثبيت المسمى مثلا ” فاتورة كهرباء ” حتي عند عمل Filter او تصفية من خلال البيان يمكنك في أي وقت معرفة اجمالي فواتير الكهرباء المسددة خلال الفترة السابقة بسهولة .

4- لا تنسي دائما عليك بكتابة التاريخ الخاص بالحركة .

 

موضوع قد يُهمك : شرح عمل شيت حركة الخزينة في Excel

 

تحميل برنامج محاسبة بسيط مجاني عربي excel

رابط التحميل

برنامج محاسبة مجاني excel لحسابات العملاء

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

مميزات برنامج محاسبة مجاني excel لحسابات العملاء

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

يمكنك زيارة هذا الموضوع للمزيد : شرح مبسط عن شجرة الحسابات excel ( دليل الحسابات )

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

3- يمكنك أيضاً طباعة كشف حساب للعملاء

4- يمكنك من طباعة قيد يومية بعينه

5- يقوم بعمل اليومية العامة تلقائيا بمجرد ادخال البيانات

6- يمكنه عمل كشف بأرصدة العملاء الحاصة بك

كيفية استخدام شيت اكسل لحسابات العملاء

1- بعد قيامك بتحميل و فتح الملف عليك بالقبول على تفعيل الرسائل الصفراء التي تظهر اعلى شيت الاكسل من خلال الضغط على ” enable ” ، فهي خاصة بتفعيل ميزة الماكرو macro لكي يعمل الشيت بشكل جيد ، فهذه الرسالة تظهر عند اول مرة فقط .

2- ملف الاكسل يحتوي على 7 شيتات سنتناول وظيفة واهمية كل شيت منها وكيف يمكن استخدامه

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

 

• شيت Journal Entry : وهو شيت خاص بالحركات التي تتم في شركتك في صورة قيود يومية ، فمثلاً اذا كان عميل عندك قام بإرسال دفعة 5000 وهذا المبلغ قد دخل الخزينة الخاصة بك ، فهذه العملية ستؤثر علي حسابين حساب الخزينة و حساب العميل ، حساب الخزينة سوف يزيد اذا فهو في الجانب المدين وحساب العميل سوف يقل اذا فهو في الجانب الدائن ..

كيفية ادخال هذا القيد علي البرنامج : 

1- بادخال كود الحساب في خانة ” الكود ” 1201 كود حساب الخزينة و كود العملاء هو 1202 – هذه الاكواد يمكنك معرفتها من شيت ” Trail Balance ”

2- كتابة المبلغ ال 5000 في الجانب المدين و الدائن

3- كتابة رقم الخزينة 01 او رقم البنك وكتابة رقم العميل

4- كتابة رقم الفاتورة الخاصة بالعميل و التاريخ وفي صف الخزينة اكتب رقم لمستند القبض

يمكنك مراقبة البيانات المكتوبة للتعرف اكثر عن كيفية كتابة البيانات

وبعد كتابة القيود اليومية الخاصة بالعملاء سيتم تلقائياً تسميع هذه البيانات في باقي الشيتات ،

• فمثلاً ستقوم بفتح شيت ” متابعة فواتير آجلة ” ستجد الفواتير التي قمت بإضافتها من خلال حساب ايراد المبيعات

 

• واذا كنت تريد عمل كشف حساب لعميل قم بفتح شيت ” كشف حساب ” واكتب رقم العميل فقط سيقوم بإنشاء كشف حساب تلقائياً 

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

 

• اما اذا كنت تريد طباعة جميع قيود اليومية قم بالانتقال الي شيت ” يومية عامة ” واضغط علي صورة الطابعة

 

• اما اذا كنت تريد كشف بأرصدة جميع العملاء فأنتقل الي شيت ” تقرير بالعملاء ” وسيظهر معك جميع ارصدة العملاء الخاصة بالشركة كما انه يمكنك طباعة هذا الكشف بسهولة من خلال الضغط على صورة الطابعة

ملاحظات عند العمل على الشيت :

1- لا تقم بتعديل اي خلية بها معادلات وذلك لان المعادلات في الملف مرتبطة ببعضها البعض فاي مسح لمعادلة سيؤثر ذلك علي الخلايا المرتبطة بها في كل الشيتات

2- عند فتح البرنامج لأول مرة سيطلب منك اعطاءه صلاحيات بتفعيل macro لكي يعمل البرنامج بشكل صحيح لذا فعليك بالضغط علي enable للتفعيل

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

 

رابط تحميل برنامج محاسبة مجاني excel لحسابات العملاء

رابط التحميل

برنامج محاسبة مجاني excel للأجور و المرتبات

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

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

1- ادخال أسماء الموظفين في ” قائمة أسماء الموظفين ” في شيت الحضور والانصراف

2- البدء في كتابة اسم الموظف ( او تكرار اسماء الموظفين في الشهر كله ) في جدول الحضور و الانصراف وكتابة التاريخ

3- كتابة المواعيد الرسمية لشركتك في هذه الخانة

3- البدء في كتابة موعد الحضور و الانصراف

4- الانتقال الي شيت المرتبات لحساب صافي المرتبات واجمالي الاستحقاقات واجمالي الاستقطاعات

5- اكتب اسم الموظف واجر الساعة الخاص به ، وقم بتعديل نسب التأمينات الاجتماعية ونسب ضريبة كسب العمل لتتناسب مع الدولة التي انت فيها

6- اضف البدلات ان وجد واضف الجزاءات ان وجد

7- وبالتالي ينتج معك صافي المرتب الذي سيحصل عليه الموظف

 

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

 

رابط تحميل برنامج محاسبة مجاني excel للاجور والمرتبات

رابط التحميل

برنامج محاسبة مجاني excel للقوائم المالية والميزانية

الذي سنقدمه لك الان هو ملف اكسل احترافي  ( يستخدمه مكاتب المحاسبة فعلياً وبالطبع يصلح للشركات ايضاً ) وحصري جداً لدي موقع حل تقني  ، حيث انه يقوم بعمل :

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

كيفية استخدام شيت القوائم المالية والميزانية

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

ملحوظة : 

عند فتح الملف الأول والثاني  سيظهر لك رسالة صفراء قم بالضغط علي enable content او تفعيل المحتوى ثم قم بالضغط على continue او استمرار

 

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

 

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

قم بتعديل الأسماء في قائمة ” التحليل ” و ” التوجيه ” قم بالاتجاه لأعلى الشيت ثم الانتقال يساراً حتي العمود AC و AD وقم بالتغيير الذي تريده

يمكنك التجول الآن بين بين الشيتات ( أوراق العمل )  والتعرف عليها ، فهي كنز بمعني الكلمة وستكتشف بنفسك ذلك ..

مميزات برنامج محاسبة مجاني excel للقوائم المالية والميزانية

1- يعمل علي أي اصدار Microsoft excel كان قديم هو او حديث

2- يصلح لمكاتب المحاسبة و الشركات ويصلح أيضاً لطالب او خريج حديث لكي يتدرب عليه

3- نظام متكامل من المعادلات المرتبطة ببعضها البعض بين الملفات و يقوم بالتحديث تلقائياً

4- يقوم بعمل ميزانية الشركة تلقائياً بمجرد ادخال الحركات

5- يقوم بالعمل بمجرد ادخال الحركات التي تمت في الشركة

6- مجاني تماماً بدون اي حدود فهو ملف مفتوح المصدر يمكنك التعديل فيه كيفما شئت

7- حصري جداً لدي موقع حل تقني .

8- رابط مباشر للتحميل

9- الملف كامل باللغة العربية

 

موضوع ذو صلة : نموذج قيد يومية excel

 

رابط تحميل برنامج محاسبة مجاني excel للقوائم المالية والميزانية

رابط التحميل

برنامج محاسبة مجاني excel لإدارة المخزون

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

مميزات برنامج محاسبة مجاني excel لإدارة المخزون

1- سهل الاستخدام فهو بمجرد ادخال الحركة التي تمت يقوم بإعداد التقارير المختلفة .

2- يقوم بعمل تقرير تفصيلي لكل مخزن على حدى لجميع الأصناف من حيث الوارد و المنصرف والرصيد المتبقي

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

4- يقوم بعمل تقرير الأصناف لكل صنف على حدي و علي كل المخازن

5- الملف يمكن أيضاً ان يساعد الشخص المبتدئ في مجال المخازن للمعرفة اكثر والتدرب مبدئياً على محاسبة المخازن

6- الملف مجاني تماما ويمكنك التحميل برابط مباشر .

كيفية استخدام برنامج محاسبة مجاني excel لإدارة المخزون

عند فتح الملف سيطلب منك تفعيل ميزة الماكرو macro وهي خاصة بالبرمجة في excel ، لذلك عليك بتفعيلها من خلال الضغط علي Enable content او تفعيل المحتوى حتى يعمل الملف بشكل جيد

1- قم بفتح شيت ” تكويد المخازن ” لإضافة الأصناف واضافة أسماء المخازن الخاصة بالشركة

 

ولتوسيع هذه القائمة من الأصناف او المخازن للاضافة قم بزيارة هذا الموضوع : تعديل قائمة منسدلة في excel

 

2- ابدأ بتسجيل الحركة التي تتم على المخازن من خلال شيت ” يومية المخزن ” من خلال ادخال رقم الاذن وليكن 1 ثم ادخال رصيد بداية الفترة للأصناف وبالتالي اختر ” مخزن الإضافة ” ثم نوع الاذن وهو ” بداية الفترة ” ثم التاريخ و اسم الصنف و الوحدة و الكمية و السعر ( السعر اختياري فيمكنك عدم إدخاله الا اذا كنت تريد معرفة قيمة المخزون ) وكتابة البيان الخاص بهذه الحركة

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 لمؤسسة ما ولم تكن تعرف كيف يمكنك عملها ومما تتكون لذلك سنتعرف في هذا الدرس عن شجرة الحسابات وكيفية عمل شجرة الحسابات excel بالتفصيل .

ماهي شجرة الحسابات

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

يتكون المستوي الأول في الدليل المحاسبي من الحسابات الرئيسية الخمسة وهي :

الأصول – الخصوم – حقوق الملكية – الإيرادات – المصروفات

ويعتبر هذا التقسيم الشائع للمستوي الأول في الدليل المحاسبي

وبطبيعة الحال سُميت ب ” شجرة ” لان كل فرع منها يندرج تحته عدة فروع ، فهذه الفروع تعتبر المستوي الثاني في الدليل المحاسبي وهي الحسابات الفرعية

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

الأصول

يندرج تحتها ( الأصول الثابتة – الأصول المتداولة )

وكل منها يندرج تحته كل اصل على حدي وهو يعتبر المستوي الثالث

الخصوم

يندرج تحتها ( الخصوم المتداولة – الخصوم طويلة الاجل – الخصوم قصيرة الاجل )

حقوق الملكية 

يندرج تحتها ( رأس المال – جاري شركاء – الأرباح والخسائر – المسحوبات الشخصية – الاحتياطات )

الإيرادات 

يندرج تحتها ( إيرادات النشاط الرئيسي – إيرادات اخري )

وكل منها يندرج تحت بند الإيرادات على حدي يعتبر المستوي الثالث

المصروفات 

يندرج تحتها ( تكلفة المبيعات – مصاريف البيع والتسويق – مصاريف إدارية وعمومية )

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

مع العلم تختلف عدد مستويات شجرة الحسابات حسب احتياجات وحجم الشركة .

عمل شجرة الحسابات excel

فكرة عمل شجرة حسابات على برنامج Excel هي عبارة عن إعطاء كل حساب رقم يشير إليها او ترقيم كل حساب من هذه الحسابات حسب المستوى الخاص به وحسب تسلسل رقمي

فمثلا :

حساب الأصول نقوم بجعل رقمه هو 1 وهو المستوى الأول

اذا الأصول الثابتة تأخذ رقم 11 وهي المستوي الثاني

والمباني تأخذ رقم 111 وهي المستوي الثالث

حيث ان هذه الطريقة تعتمد علي ان المستوي الأول يأخذ رقم من 1 الي 5 وذلك لان المستوي الأول عبارة عن خمس حسابات

والمستوي الثاني من 11 الي 99

والمستوي الثالث من 101 الي 99 وهكذا ..

مع العلم ان هذه الطريقة ليست الوحيدة للترقيم ، أي ان تعتمد طريقة ترقيم وتسلسل الحسابات علي اختيار الإدارة المالية للمؤسسة .

طريقة عمل شجرة حسابات excel ( دليل الحسابات )

1- نقوم بعمل جدول مكون من 6 أعمدة – اذا كان عدد المستويات هو ثلاثة – وعدد غير محدد من الصفوف ، لكي يكون بهذا الشكل

 

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

2- نقوم بحصر جميع الحسابات الخاصة بالمؤسسة وتصنيفها تحت أي حساب تندرج من الحسابات الخمسة الرئيسية وبالتالي تحديد المستوي الخاص بها

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

 

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

يختلف شكل دليل الحسابات حسب المجال الخاص بالمؤسسة وذلك لاختلاف الحسابات الخاصة بها ولكن يظل الأساس واحد كما تم شرحه ، ومن امثلة اشكال دليل الحسابات هو الدليل الحسابي الصناعي – الدليل المحاسبي الموحد الحكومي- دليل الشركات التجارية – دليل حوالة وصرافة – دليل شركة النفط – الدليل المحاسبي للشركات ذات الفروع

نموذج دليل حسابات excel جاهز للتعديل عليه

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

رابط تحميل نموذج دليل الحسابات excel برابط مباشر

 

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

• شرح مفصل عن اهم الدوال في excel للمبتدئين
• شرح عمل شيت حركة الخزينة في Excel

في بداية استخدامك لبرنامج excel لعمل جدول ستُلاحظ ان الجدول صغير للغاية بالأخص يظهر صِغر حجم الجدول عند طباعته لذلك تريد تكبير الجدول في excel بأسرع واسهل الطرق للطباعة او عرضه على شخص مسؤول او مراجع او تحويل الشيت لملف pdf ، لذلك سنتعرف عن اسرع واسهل الطرق لتكبير حجم الجدول في excel .

اسهل طريقة تكبير الجدول في excel

باختصار يتلخص تكبير حجم  الجدول في نقطتين :

الاولي : تكبير حجم الخط

وذلك من خلال تظليل او تحديد الخلايا ثم التحكم في أداة تكبير الخط في excel الموجودة في تبويب Home القائمة الرئيسية في قسم Font الخط تلك الأدوات المُشار اليها في الصورة التالية :

الثانية : تكبير حجم الخلايا

 

بعد تكبير حجم الخط ستلاحظ ان حجم الخلايا لا يزال صغير وتريد عمل توسعة لعرض العمود وارتفاع الصف سنقوم بعمل ذلك من خلال الاتي :

تغيير عرض الأعمدة باستخدام الماوس

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

فيديو قصير للتوضيح

تغيير ارتفاع الصفوف باستخدام الماوس

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

فيديو قصير للتوضيح

للمزيد عن طرق تغيير عرض العمود وارتفاع الصف اضغط هنا

تكبير عرض الجدول بشكل مؤقت

يمكنك تكبير الجدول بشكل مؤقت للعرض فقط من خلال عمل zoom In تكبير وذلك من خلال مؤشر الزائد + المُشار اليه في الصورة التالية ومؤشر الناقص – يقوم بعمل zoom Out تصغير

ولإضافة صفوف وأعمدة أو إزالتها من الجدول تفضل بزيارة هذا الموضوع

 

مواضيع سَتُفيدك بالتأكيد :

• شرح برنامج 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