10 معادلات هامة جداً ومفيدة فى برنامج الاكسيل | الجزء الثالث من سلسة أهم دوال الاكسيل
لا شك أن برنامج الإكسيل هو واحد من أكثر البرامج إستخداماً فى جميع المنشآت المجالات لما له من فوائد كثيرة وأيضاً مهما كثيرة ومهمة جداً ، منذ عدة أسابيع قمت بنشر فيديو على القناة الخاص بنا على اليوتيوب وكان هذا الفيديو عبارة عن كورس كامل وشامل لتعلم الاكسيل من الصفر حتى الإحتراف ، ومنذ نشر هذا الفيديو قد تلقيت طلبات كثيرة بعمل شروحات خاصة بمعادلات أو دوال برنامج الاكسيل .
وإستجابةً لإصدقائى المتابعين قمت بعمل سلسلة شروحات وافية ومفيدة جداً خاصة بدوال الإكسيل بحيث أنه سيتم شرح أكثر من 30 دالة مهمة ومفيدة جداً على أكثر من جزء ، ولكن قبل البدء دعنا نعرف ما هى فائدة الدوال أو المعادلات .
فائدة دوال الاكسيل :-
لابد وأن نعلم أن المعادلات ستساعدنا بشكل كبير جداً على العمل على برنامج الاكسيل وتوفر لنا الكثير من الوقت ، حيث أن المعادلات أو الدوال هى عبارة عن أوامر نقوم بإدخالها على شيت الاكسيل الخاص بنا مرة واحدة ومن خلال هذه المعادلات يتم عرض النتيجة بشكل تلقائى.
وفى الحقيقة يوجد كثير من الدوال وبأنواع مختلفة ، وفى هذه المقالة ومن خلال الفيديو على قناة اليوتيوب الخاصة بنا سأقوم بشرح أكثر من 30 معادلة مهمة جداً ولا غنى عنها كما ذكرت سابقاً ولكن على أكثر من جزء حتى يكون الأمر سهل الإستيعاب ولا يكون ممل بسبب الإطالة ، وجميع هذه المعادلات إن شاء الله ستكون مفيدة ومهمة جداً.
المعادلات التى سيتم شرحها فى الجزء الثالث: -
فى هذه المقالة وفى الفيديو المشار إليه بالأسفل سيتم شرح 10 دوال مهمة جداً ومفيدة جداً وهى كالتالى :-
1. CountBlank.
2. Match.
3. Concatenate.
4. &.
5. Text.
6. Lookup.
7. Vlookup.
8. Hlookup.
9. Sumproduct.
10. Subtotal.
ولكن قبل البدء فى شرح المعادلات يجب التنبيه على شئ هام جداً وهو أن أى معادلة يتم بدأها بعلامة = وبعدها إسم المعادلة ، ثم نضع القيم بين قوسين ، ويجب أن نلاحظ أيضاً شئ مهم جداً وهو أننا يجب الضغط على علامة , بعد تحديد كل قيمة ، وهذا شئ أساسى لعمل أى معادلة حتى تكون بطريقة صحيحة.
1. معادلة عد الخلايا الفارغة – CountBlank:-
معادلة الـ CountBlank هى معادلة نستخدمها فى عد الخلايا الفارغة ، حيث أننا نقوم بإدخال دالة العد هذه لكى نستنتج أعداد الخلايا الفارغة ويقوم برنامج الاكسيل بعرض عدد الخلايا الفارغة بشكل تلقائي.
فعلى سبيل المثال من خلال الصورة السابقة يوجد بيان بأسماء طلاب وكذلك درجاتهم ونريد عد الخلايا الفارغة ، هنا نقوم بعمل الدالة وهى
=CountBlank(هنا نحدد الصف الذى يحتوى على الخلايا الفارغة التى نريد عدها)
ثم نقوم بالضغط على Enter .
2. معادلة الجمع بشرط – Match: -
معادلة الـ Match هى معادلة نستخدمها فى معرفة رقم الخاص بخلية معينة أو ، حيث أنه يتم تحديد خلية معينة والتى تحتوى على اسم طالب محدد وبشكل تلقائى يظهر رقم الخلية التى تحتوى إسم هذا الطالب ، ولكن فى هذه الدالة يجب عمل خطوة قبلها كما سيتم توضيحه.
فعلى سبيل المثال من خلال الصورة السابقة والتى تحتوى على بيان بالطلاب ودرجاتهم ونريد أن نقوم بإنشاء معادلة بحيث أنه عند إختيار طالب محدد يظهر رقم الخلية بشكل تلقائى.
فيجب أن نقوم بعمل خطوة قبل عمل المعادلة وهذه الخطوة هى Dropdown List ، حيث أننا نذهب لقائمة الـ Data ومنها نختار Data Validation ومنها نختار List ثم نقوم بتحديد الخلايا التى تحتوى على الأسماء ، كما هو موضح بالصور التالية ،
وبعد أن قمنا بعمل هذه الخطوة وهى الـ Dropdown List ، نقوم بمعل معادلة الـ Match وهى
=Match(نقوم بتحديد الخلية التى تحتوى على الدروب داون ليست, نقوم بتحديد الصف الذى يحتوى الأسماء, 0)
ثم نقوم بالضغط على Enter ، وهكذا عن إختيار أى إسم من الـ Dropdown list يظهر رقم الخلية التى تحتوى على إسم الطالب بشكل تلقائى فى الخانة التى قمنا بعمل المعادلة فيها ، وكتبنا رقم 0 فى آخر المعادلة لكى يعطينا الناتج بشكل سليم وصحيح ، فرقم 0 يعنى True.
ويجب أن نلاحظ شئ مهم جداً وهو أننا يجب الضغط على علامة , بعد تحديد كل قيمة ، وهذا شرط أساسى فى جميع المعادلات.
3. معادلة جمع بيانين من خليتان بحيث يكونوا فى خلية واحدة بوجود فاصل بين الكلمتين – Concatenate: -
معادلة الـ Concatenate هى معادلة نستخدمها فى جمع بيانين من خليتان مختلفتان بحيث يكونوا فى خلية واحدة ، فعلى سبيل المثال عندنا البيان الموضح بالجدول التالى ، ونريد أن نجعل إسم الطالب حازم بجوار رقمه فى خلية واحدة بدل خليتان ، بحيث تكون الخلية مكتوبة فيها (حازم 55) بشكل متصل فى خلية واحدة ،،،
ونقوم بعمل هذه الدالة بـ
=Concatenate(نحدد الخلية التى تحتوى على الرقم ," ", نقوم بتحديد الخلية التى تحتوى على الإسم)
ثم نقوم بالضغط على Enter.
ونلاحظ هنا أننا وضعنا هذه العلامات ," ", وذلك حتى يكون هناك فاصل بين الإسم والرقم ، أما إذا لا نريد فاصل فنتركها متصلة.
4. معادلة جمع بيانين من خليتان بحيث يكونوا فى خلية واحدة بوجود فاصل بين الكلمتين - &:-
معادلة الـ & هى معادلة نستخدمها فى جمع بيانين من خليتان مختلفتان بحيث يكونوا فى خلية واحدة ، فعلى سبيل المثال عندنا البيان الموضح بالجدول التالى ، ونريد أن نجعل إسم الطالب حازم بجوار رقمه فى خلية واحدة بدل خليتان ، بحيث تكون الخلية مكتوبة فيها (حازم55) بشكل متصل فى خلية واحدة ،،،
ونقوم بعمل هذه الدالة بـ
=(نحدد الخلية التى تحتوى على الرقم & نقوم بتحديد الخلية التى تحتوى على الإسم)
ثم نقوم بالضغط على Enter.
والفرق بين هذه الدالة والدالة السابقة Concatenate ، هو أن فى هذه الدالة & يتم جمع الخليتين فى خلية واحدة ولكن بدون فاصل بين الكلمة والرقم ، أما فى دالة الـ Concatenate يتم جمع الخليتين فى خلية واحدة ولكن بفاصل بين الكلمتين.
5. معادلة كتابة اليوم بشكل تلقائي – Text: -
معادلة الـ Text هى معادلة نستخدمها فى كتابة اليوم بشكل تلقائى عند إدخال تاريخ معين ، فعلى سبيل المثال عندنا خلية تحتوى على تاريخ 16-11-2021 ونريد أن نعرف ماذا كان هذا اليوم من أيام الأسبوع ، فهنا نقوم بعمل دالة الـ Text ، ونقوم بعمل هذه الدالة بـ
=Text(نقوم بتحديد الخلية التى تحتوى على التاريخ"dddd" ,)
ثم نقوم بالضغط على Enter.
وهنا نلاحظ أننا كتبنا "dddd" وهذه هي الصيغة التى نريد أن يتم كتابة التاريخ بها.
6. معادلة البحث فى الصفوف والأعمدة - Lookup:-
معادلة الـ lookup هى معادلة نستخدمها فى البحث فى الشيت الخاص بنا سواء البحث فى الأعمدة أو الصفوف ، فمن خلال هذه الدالة يكون البحث فى صف وتظهر النتيجة فى عامود ، أو يمكن البحث فى عامود وتظهر النتيجة فى صف ، ولكن فى هذه الدالة يجب عمل خطوة قبلها كما سيتم توضيحه.
فعلى سبيل المثال من خلال الصورة السابقة والتى تحتوى على بيان بالطلاب ودرجاتهم ونريد أن نقوم بإنشاء معادلة بحيث أنه عند إختيار طالب محدد سوف تظهر نتيجته بشكل تلقائى.
فيجب أن نقوم بعمل خطوة قبل عمل المعادلة وهذه الخطوة هى Dropdown List ، حيث أننا نذهب لقائمة الـ Data ومنها نختار Data Validation ومنها نختار List ثم نقوم بتحديد الخلايا التى تحتوى على الأسماء ، كما هو موضح بالصور التالية ،
وبعد أن قمنا بعمل هذه الخطوة وهى الـ Dropdown List ، نقوم بمعل معادلة الـ Lookup وهى
=Lookup(نقوم بتحديد الخلية التى تحتوى على الدروب داون ليست, نقوم بتحديد الصفوف التى تحتوى على الأسماء نقوم بتحديد الصفوف التى تحتوى على الأرقام ,)
ثم نقوم بالضغط على Enter ،
وهكذا عن إختيار أى إسم من الـ Dropdown list يظهرنتيجة الطالب بشكل تلقائى فى الخانة التى قمنا بعمل المعادلة فيها .
ويجب أن نلاحظ شئ مهم جداً وهو أننا يجب الضغط على علامة , بعد تحديد كل قيمة ، وهذا شرط أساسى فى جميع المعادلات.
وفى معادلة الـ Lookup يجب التنويه على شئ مهم جداً وهو أن الشئ الذى نبحث عنه يجب أن يكون فى الصف الأول ، وكذلك ينبغى أن يكون عدد الخلايا متساويين سواء فى الصفوف أو الأعمدة.
7. معادلة البحث فى الأعمدة فقط – Vlookup:-
معادلة الـ vlookup هى معادلة نستخدمها فى البحث فى الشيت الخاص بنا ولكن فى الأعمدة فقط ، فحرف الـ V هو إختصار لكلمة Vertical والتى تعنى عامودى باللغة الإنجليزية ، فمن خلال هذه الدالة يكون البحث فى عامود وتظهر النتيجة فى عامود آخر ، ولكن فى هذه الدالة يجب عمل خطوة قبلها كما سيتم توضيحه.
فعلى سبيل المثال من خلال الصورة السابقة والتى تحتوى على بيان بالطلاب ودرجاتهم ونريد أن نقوم بإنشاء معادلة بحيث أنه عند إختيار طالب محدد سوف تظهر نتيجته بشكل تلقائى.
فيجب أن نقوم بعمل خطوة قبل عمل المعادلة وهذه الخطوة هى Dropdown List ، حيث أننا نذهب لقائمة الـ Data ومنها نختار Data Validation ومنها نختار List ثم نقوم بتحديد الخلايا التى تحتوى على الأسماء ، كما هو موضح بالصور التالية ،
وبعد أن قمنا بعمل هذه الخطوة وهى الـ Dropdown List ، نقوم بمعل معادلة الـ Vlookup وهى
=Vlookup(نقوم بتحديد الخلية التى تحتوى على الدروب داون ليست,
نقوم بتحديد الأعمدة التى تحتوى على الأسماء والدرجات,
2, 0)
ثم نقوم بالضغط على Enter ،
وهكذا عن إختيار أى إسم من الـ Dropdown list يظهرنتيجة الطالب بشكل تلقائى فى الخانة التى قمنا بعمل المعادلة فيها ، ونلاحظ أننا كتبنا رقم 2 فى آخر المعادلة لأن هذا هو رقم العامود الذى يحتوى على أرقام النتائج وكذلك الرقم 0 بحيث يعطينا النتيجة الصحيحة.
ويجب أن نلاحظ شئ مهم جداً وهو أننا يجب الضغط على علامة , بعد تحديد كل قيمة ، وهذا شرط أساسى فى جميع المعادلات.
8. معادلة البحث فى الصفوف فقط – Hlookup:-
معادلة الـ Hlookup هى معادلة نستخدمها فى البحث فى الشيت الخاص بنا ولكن فى الصفوف فقط ، فحرف الـ H هو إختصار لكلمة Horizontal والتى تعنى أفقى باللغة الإنجليزية ، فمن خلال هذه الدالة يكون البحث فى صف وتظهر النتيجة فى صف آخر ، ولكن فى هذه الدالة يجب عمل خطوة قبلها كما سيتم توضيحه.
فعلى سبيل المثال من خلال الصورة السابقة والتى تحتوى على بيان بالطلاب ودرجاتهم ونريد أن نقوم بإنشاء معادلة بحيث أنه عند إختيار طالب محدد سوف تظهر نتيجته بشكل تلقائى.
فيجب أن نقوم بعمل خطوة قبل عمل المعادلة وهذه الخطوة هى Dropdown List ، حيث أننا نذهب لقائمة الـ Data ومنها نختار Data Validation ومنها نختار List ثم نقوم بتحديد الخلايا التى تحتوى على الأسماء ، كما هو موضح بالصور التالية ،
وبعد أن قمنا بعمل هذه الخطوة وهى الـ Dropdown List ، نقوم بمعل معادلة الـ Hlookup وهى
=Hlookup(نقوم بتحديد الخلية التى تحتوى على الدروب داون ليست,
نقوم بتحديد الأعمدة التى تحتوى على الأسماء والدرجات,
2, 0)
ثم نقوم بالضغط على Enter ،
وهكذا عن إختيار أى إسم من الـ Dropdown list يظهرنتيجة الطالب بشكل تلقائى فى الخانة التى قمنا بعمل المعادلة فيها ، ونلاحظ أننا كتبنا رقم 2 فى آخر المعادلة لأن هذا هو رقم الصف الذى يحتوى على أرقام النتائج وكذلك الرقم 0 بحيث يعطينا النتيجة الصحيحة.
ويجب أن نلاحظ شئ مهم جداً وهو أننا يجب الضغط على علامة , بعد تحديد كل قيمة ، وهذا شرط أساسى فى جميع المعادلات.
9. معادلة اجمالى الضرب والجمع لمجموعة خلايا – Sumproduct: -
معادلة الـ Sumproduct هى معادلة نستخدمها فى إيجاد مجموع الضرب والجمع لعدد خلايا محدد بخطوة واحدة ، فعلى سبيل المثال لدينا البيان التالى ونريد أن نستنتج إجمالى ضرب العامود الأول فى العامود الثالث ثم جمعهم ،
ولعمل هذه المعادلة :-
=Sumproduct(نقوم بتحديد العامود الثانى ,نقوم بتحديد العامود الأول)
ثم نقوم بالضغط على Enter.
10. معادلة إيجاد قيمة محددة من إجمالى المجموع – Subtotal:-
معادلة الـ Subtotal هى معادلة نستخدمها فى إيجاد قيمة محددة وخاصة بخلية محددة من إجمالى المجموع الكلى ، فعلى سبيل المثال لدينا الشيت التالى والذى يحتوى على بيانات طلاب ودرجاتهم ونريد أن يظهر لنا الدرجة الخاصة بكل طالب عن إختيار إسمه ،
ولكن قبل البدء فى عمل هذه المعادلة يجب إنشاء فلتر على رأس كل عامود كما موضح بالصورة السابقة ، ثم نقوم بعمل المعادلة وهى كالتالى :
=Subtotal(9, نقوم بتحديد الخلايا التى تحتوى على الدرجات)
ثم نقوم بالضغط على Enter.
وبعد عمل هذه المعادلة وعند إختيارأى إسم من خلال الفلتر ستظهر النتيجة الخاصة به بشكل تلقائى فى الخلية التى قمنا بعمل المعادلة بها.
وهكذا نكون قد شرحنا الجزء الثالث من سلسلة معادلات الاكسيل والتى تحتوى على عدد كبير جداً من المعادلات المهمة والمفيدة.
ولشرح تفصيلى بشكل أكثر ، يرجى مشاهدة الفيديو التالى ،،،