Excel4Us

Workbook Statistics in Excel 365

2020-02-28T21:58:00.002-08:00

Workbook Statistics in Excel 365

اضافة جميلة و جديدة من مايكروسوفت اكسيل على نسخة الاكسيل التي تعمل على الاوفيس 365 لمشتركي الـ

Insider

و هي خاصية إحصائية للملف الذي نعمل عليه و الذي تعطينا معلومات عن الملف الذي نعمل عليه من ناحيتين :

1. الصفحة التي نعمل عليها Current Sheet .

2. الملف كامل Workbook

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

1. أخر خلية تم العمل عليها End of sheet.

2. الخلايا التي تحتوي بيانات Cells with data.

3. الجداول Tables ( الفرق بين الجداول و النطاقات, الجداول هي التي تم تحويلها من خلال خاصية الجداول Table او Ctrl T فعند النظر اليها بالشكل متساويين ).

4. الجداول المحورية PivotTable .

5. المعادلات Formulas

6. الرسومات البيانية Charts .

7. العناصر Objects.

8. الصور Pictures.

و هذه الخيارات ستظهر عند توفرها في الملف.

و للعمل مع هذا الخيار موجود في تاب

Review

Workbook Statistics

اذا كان الملف الذي نعمل عليه فارغ سنحصل على الشاشه التالية :

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

أما إذا تعاملنا مع ملف يحتوي :

1. خلايا بها بيانات .

2. جداول .

3. صور.

4. إلخ ....

فإن النتيجة تظهر بالشكل التالي :

دمتم في حفظ الله

يحيى حسين

المعادلة SORT

2019-02-21T03:33:00.000-08:00

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

المعادلة SORT

وظيفتها عمل إعادة تترتيب للبيانات, حيث تعمل على ترتيب النتائج بالشكل الذي نختاره تنازليا أو تصاعدياً.

و لها أربع متغيرات :

1. Array : متغير إجباري و هو النطاق الذي يحتوي القيمة التي نريد ترتيبها و قد تكون عامود واحد أو عدة أعمدة.

2. Sort_index : متغير إختياري, و يأخذ قيمته حسب عدد الأعمدة في النطاق المحدد في المتغير الأول, فإذا كانت النطاق يحتوي عامود واحد فهنا القيمة تلقائياً هي 1 , أما إذا كان النطاق المحدد أكثر من عامود فإن عملية الترتيب ستكون على العامود الأول في حالة عدم تحديد أي عامود آخر, فإذا تم تحديد رقم عامود آخر في النطاق فسيكون إعادة الترتيب بناء عليه, وإذا إخترت رقم عامود أكثر من عدد الأعمدة الموجودة في النطاق ستكون نتيجة المعادلة في هذه الحالة خطأ #VALUE!.

3. Sort_order : المقصود به خيارات الترتيب هل هي تصاعدية أم تنازلية:

a. القيمة 1 : Ascending إعادة الترتيب تصاعدي .

b. القيمة -1 : Descending إعادة الترتيب تنازلي.

4. By_col : المقصود بها هل الترتيب سيكون بإتجاه الأعمدة أو الأسطر, فكما نعلم أنه عملية الترتيب تطون دائماً من الأعلى للأسفل و يمكن أن نغير التريب من اليسار إلى اليمين , وهذا هو المقصود بهذا المتغير.

لتوضيح آلية عملها كما في الصورة التالية لدينا أسماء الدول و مبيعاتها و عند تطبيق المعادلة حددنا عامود واحد فقط, لذلك سنحصل على إعادة ترتيب القيم و هو أسماء الدول بالترتيب الأبجدي :

و هذه هي النتيجة أسماء الدول بترتيب أبجدي من A to Z :

و لو إخترنا النطاق الخاص بالأرقام فقط سنحصل على الترتيب الأرقام من الأصغر إلى الأكبر

و لكن إذا إخترنا الجدول كامل في المعادلة, فكما ذكرنا سابقاُ سيكون الترتيب على العامود الأول و لكن سنطبق المتغيرات الأخرى في المعادلة, حيث سنختار الرقم 2 و التي تشير إلى العامود رقم 2 بالنطاق المحدد, و ثم سنختار الرقم -1 حتى يكون الترتيب من الأكبر إلى الأصغر.

و كما ذكرنا سابقاً في حال وجود أي قيمة في الوسط سنحصل على خطأ إسمه #SPILL .

في الفيديو المرفق شرح تفصيلي :

رابط الفيديو

دمتم في حفظ الله

يحيى حسين

Excel MVP

المعادلة UNIQUE

2019-02-19T21:43:00.000-08:00

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

المعادلة UNIQUE

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

و لها ثلاث متغيرات هم :

1. Array : متغير إجباري و هو النطاق الذي يحتوي القيمة المكررة و قد يكون عامود واحد أو عدة أعمدة, فإذا إخترت عامود واحد ستكون النتيجة القيمة الفريدة فقط داخل العامود المحدد, و إذا إخترت الأعمدة كلها فسيحسب التكرار على كل الأعمدة.

2. By_col : متغير إختياري لإختيار القيم الفريدة في سطر أو عامود.

3. Occurs_once : متغير إختياري لإختبار القيم الفريدة التي تكررت (مرة واحدة )أو (مرة واحدة أو أكثر).

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

و عند الضغط على انتر سنحصل على النتيجة التالية :

و هي أسماء الدول في العامود المحدد, هنا يجب أن ننتبه لأمرين :

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

2. الأمر الثاني أن المعادلة كتبت مرة واحد في أول خلية و نسخت تلقائياً في كل الخلايا و لكن اذا وضع مؤشر الماوس على أي خلية منهم لن تجد بداخلها معادلة, و إذا نظرت إلى الـ Formula Bar ستجد أن المعادلة في الخلايا التي تأتي بعد ثاني خلية قد أخذت لون باهتاً كما في الصورة التالية :

و هنا يتبادر إلى الأذهان سؤال, ماذا لو كتبت أي قيمة داخل أي خلية تحت المعادلة الأصلية

سنجرب ذلك و نرى ما هي النتيجة :

سنحصل على خطأ إسمه #SPILL, و سببه عدم قدرة الإكسيل على تجاوز الخلية التي وجدت في إتجاه سحب المعادلة و نلاحظ وجود خط أزرق متقطع.

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

و في الفيديو المرفق شرح تفصيلي

على هذا الرابط

دمتم في حفظ الله

يحيى حسين

Excel MVP

جمع المبلغ من عامودين في الاكسيل في حال إختلفت وحدة القياس

2019-01-28T07:18:00.000-08:00

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

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

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

فكما نرى في الصورة, عامود الدنانير منفصل عن عامود الفلسات :

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

ثم الحصول على المجموع النهائي :

حيث أخذنا مجموع المبلغ بالدينار و أضفنا اليه مجمود المبلغ بالفلس مقسوما على ألف لان كل دينار يحتوي 1000 فلس ( فلو كانت العملة التي تعمل عليها تقاس بالمائة كالريال السعودي مائة هللة يكون القسمة على مائة ), و من ثم سحب المعادلة أعلاه و الخطوة الأخيرة جمع البيانات كما في الشكل التالي بحيث يكون المجموع الكلي 344.492 دينار.

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

و لعمل ذلك سنتخدم معادلة

MOD

مع معادلة

SUM

حيث ستقوم هذه المعادلة بعد جمع العامود بحساب باقي القسمى على الف و التي ستحقق المطلوب 492 دينار كما في الشكل التالي :

و بذلك نكون قدد حصلنا على مجموع الفلسات

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

الحصول على مجموع عامود الدناينر.
اضافة مجموع عامود الفلسات اليه و لكن بعد طرح فرق الفلسات منه.
قسمة فرق الفلسات على الف لتحويل 2000 الى رقم 2 .

و المعادلة التالية تفي بالغرض ان شاء الله.

و بذلك نحصل على المجموع مطابق في العامودين.

و القيمة مطابقة ما بين عامود E و عامود C,D

و لمشاهدة الفيديو على اليوتيوب

على هذا الرابط

دمتم في حفظ الله

يحيى حسين

Excel MVP

معادلة إحتساب الضريبة حسب قانون ضريبة الدخل الجديد للموظفين لعام 2019

2019-01-26T00:39:00.000-08:00

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

مع بداية عام 2019 و تطبيق قانون الضريبة الجديد, اتصل معي عدد من الاخوة و الاخوات بخصوص معادلة أو آلية او طريقة لإحتساب ضريبة الدخل على القانون الجديد.

و لتوضيح الفكرة

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

فالمتزوج حد الإعفاء لغاية 20000 دينار أردني سنوي

و الأعزب حد الإعفاء لغاية 10000 دينار سنوي

و ما زاد عن ذلك يتم إحتساب الضريبة عليه حسب الشرائح أعلاه

اول 5000 ضريبتها 5%

ثاني 5000 ضريبتها 10%

و هكذا

و لو تم إحتسابها بشكل شهري فستكون الأرقام كالتالي

و لتوضيح آلية الإحتساب

لو فرضنا أن الموظف راتبه 5250 دينار و متزوج و لحساب ضريبته الشهرية

فهو في هذه الحالة عند أول 1666.667 لا يوجد ضريبة

و 416.667 تخضع 5%

و 416.667 تخضع 10%

و 416.667 تخضع 15%

و 416.667 تخضع 20%

و ما تبقى من راتبه بعد جمع الأرقام أعلاه سيكون 1916.67 ستخصع ل 25%

حسب الشكل التالي

فالمبلغ المستحق الان كضريبة 687.5 دينار

و لعمله بمعادلة واحدة سنستخدم المعادلات

IF
MAX

MIN

لحساب الضريبة

و سبب استخدام معادلة

MAX

حتى تستثني حساب القيم السالبة فعند وضع الرقم 0 مع اي قيمة سالبة فإن النتيجة مع معادلة

MAX

ستكون الصفر و بذلك سيتم تحييد القيم السالبة

و إستخدام معادلة ال

MIN

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

و الذي يمثل كم ذكرنا الحد لأول 5000 دينار من الشرائح المعتمدة

فإذا كان الفرق على سبيل المثال 550 فإن نتيجة المعادلة ستكون 416.667 لانها اقل من 550

أما إذا كان الرقم 250 فستكون النتيجة هي 250 لانها اصغر من 416.667

و هكذا حتى يتم إحتساب كل قيمة و ضربها بالنسبة المقابلة لها

و من ثم دمج كل هذه المعادلات للحصول على النتيجة النهائية

و نفس الخطوات أعلاه سيتم تكرارها مع الضريبة في حالة كان الموظف أعزب ما إختلاف الحدود

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

رابط الفيديو على اليوتيوب

لمشاهدة الفيديو على موقع اليوتيوب

لتحميل الملف من خلال الرابط التالي

رابط التحميل

دمتم في حفظ الله

يحيى حسين

Excel MVP

دالة CONCATENATE , CONCAT

2019-01-23T23:35:00.000-08:00

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

الدالة CONCATENATE

هي من الدوال التي تستخدم في عملية دمج القيم من عدة خلايا في خلية واحدة, مع عدد محدود من المتغيرات يصل إلى 255.

و هي من الدوال التي إستبدلتها شركة مايكروسوفت بالدالة الجديدة

CONCAT

فعند التعامل مع

Excel Mobile

و الاكسيل 2016

سنجد المعادلة CONCAT

مع الإبقاء على المعادلة السابقة

CONCATENATE

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

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

CONCAT

كتعريف هي نفسها نفس تعريف المعادلة أعلاهز

الشكل العام لها

متغيراتها

المتغير text1

و يقصد به النص او الخلية التي تحتوي القيمة

ثم text2 و هكذا حتى نصل لمتغيرات عددها253

و لتوضيح عملها كمعادلة

لدينا البيانات التالية

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

يمكننا إستخدام المعادلة بعدة أشكال

الشكل الأول :

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

الشكل الثاني :

بإستخدام النطاق كامل من الخلية

B4:E4

الشكل الثالث :

يخص المعادلة القديمة

CONCATENATE

سنحصل على النتيجة بشكل متشابه بإستثناء الشكل الثاني لعدم وجود فراغات بين المتغيرات

و هذا هو الشكل النهائي للنتائج

و في الفيديو المرفق شرح تفصيلي للمعلومات أعلاه

دمتم في حفظ الله

يحيى حسين

Excel MVP

ايقاف عمل الدالة GETPIVOTDATA

2019-01-22T23:19:00.002-08:00

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

كثير من الأحيان عند التعامل مع الجداول المحورية
PivotTable

عند محاولة الربط قيمة من خارج الجداول المحورية بقيمة داخل الجدوال المحورية ظهر لنا الدالة
GETPIVOTDATA

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

في نسخة الاكسيل 2010 و 2007 كان اسمه
Option

ثمن من ضمن خيارات الـ
PivotTable

نلغي علامة الصح من جانب الخيار
Generate GetPivotData

و الفيديو التالي يوضح الآلية

دمتم في حفظ الله

يحيى حسين
Excel MVP

الدالة TEXTJOIN

2019-01-22T22:37:00.001-08:00

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

في تدوينة اليوم سنتحدث عن الدالة

TEXTJOIN

و هي من الدوال الجديدة و المضافة في الاكسيل 2016

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

DELIMITER

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

متغيراتها

لهذه المعادلة المتغيرات التالية :

المتغير الأول :

DELIMITER

و الذي من خلاله يتم تحديد نوع المتغير بين الخلايا

المتغير الثاني :

ignore_empty

و هو خيارين

TRUE

لكي يتجاهل الخلايا الفارغة

False

لكي يتعامل مع الخلايا الفارغة

المتغير الثالث :

text1

و هو الخلية أو مجموعة الخلايا التي ستم التعامل معها

باقي المتغيرات

إختياريه في الة إضاة نصوص أخرى .

و لتوضيح آلية عمل هذه المعادلة :

لدينا القيم الأربعة التالية في أربع خلايا

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

Yahya Hussien Excel MVP

نريد النتيحة هكذا

لعمل ذلك يوجد في الاكسيل أكثر من طريقة

فالطريقة التقلدية لعمل ذلك بإستخدام علامة الدمج &

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

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

TEXTJOIN

فكما نلاحظ إستخدمنا المتغير الأول و هو إضافة الفراغ بين الخلايا و هو المحدد

المتغير الثاني رقم 1 و هو القيمة المقابلة لـ

TRUE

و المتغير الثالث حددنا النطاق الذي يحتوي الخلايا

و بذلك نحصل على النتيجة التالية

و في الفيديو المرفق شرح تفصيلي لها مع بعض الإضافات

دمتم في حفظ الله

يحيى حسين

Excel MVP

Excel4Us

2019-01-22T22:07:00.001-08:00

Excel4Us Training Center مركزنا متخصص في تدريب الاكسيل بكل مستوياته ( المبتدأ و المتوسط و المتقدم و المعادلات و الداش بورد و البرمجة و ذكاء الأعمال ) بإشراف مجموعة متخصص من المدربين المعروفين على مستوى العالم العربي و بإشراف الخبير المتخصص الأستاذ يحيى حسين أول عربي معتمد من مايكروسوف و العربي الوحيد الحاصل على شهادة MVP و التي تعني Most Valuable Professional الخبراء الأعلى قيمة و الأستاذ يحيى حسين له خبرة تمتد لأكثر من عشرين سنة في و أكثر من عشر سنوات في درب خلال كبرى الشركات في و في العربي كما قدمنا الخدمات الإستشارية لمعظم عملائنا و التي ساهمت بشكل كبير و فعال في رفع كفاءة و إنتاجية الأقسام التي دربناها, و قمنا بتصميم العديد من النماذج المالية و المهنية التي من شأنها تقليل الوقت و الكلفة على الشركات. من عملائنا : • Microsoft. • Hikma Pharmaceutical. • PEPSI. • Zain Jordan. • Coca Cola Jordan. • Nestle. • Nuqul Group. • Manaseer Group. • iDJ (PepsiCo/Almarai Joint Venture International Dairy and Juice Limited Acquires) • EDGO Group. • Abu Khader Group. • Naouri Group. • Pharma International. • Canadian Embassy Amman. • Dar Al Dawa. • Cargill. • Amcan Group. للتواصل معنا : هاتفنا الأرضي : 0096265688546 موبايل 00962795157525 إيميل : info@Excel4Us.com

افتراضي طريقة فصل الكلمة العربية عن الإنجليزية في خلية واحدة

2019-01-22T22:06:00.000-08:00

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

A
3 Accountant المحاسب
4 Accountants المحاسبون
5 Audit Report تقرير المراجعة
6 Auditing المراجعة - تدقيق الحسابات

المطلوب أن يتم فصل الكلمة العربية في الخلية B و الكلمة الإنجليزية في الخلية C بحيث تكون النتيجة بالشكل التالي :

B C
3 المحاسب Accountant
4 المحاسبون Accountants
5 تقرير المراجعة Audit Report
6 المراجعة - تدقيق الحسابات Auditing

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

Code

حيث ان عمل هذه الدالة يقوم على إعادة رقم الحرف كما هو في نظام

Ascii

و الأحرف العربية تبدأ من رقم 192

و ربطها بالدالة

MID

بحيث نعرف نقطة البداية في فصل الكلمات , فكانت الدالة التالية :

كود PHP:

=MID(A3,MATCH(1,IF(CODE(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))>=192,1),0),255)

__________________
و كما نرى لقد قمت بإستخدام الدالتين
ROW and INDIRECT
بهدف عمل تسلسل رقم حسب عدد احرف الكلمة داخل الخلية
و من ثم استخدمت الدالة
MID
مرة أخرى ذلك لكي يتشكل عندي مجموعة منفصل من الأحرف و لكي يسهل عمل الدالة
CODE
في معرف رقم كل حرف و التي ستلزمنا بشكل كبير عن عملية الفحص مع الدالة
IF
بحث اذا كان رقم الحرف اكبر او يساوي 192 فستقوم بتعويض الرقم 1 بدل منها و بذلك نحصل على صفيف من الرقم 0 و 1 و هنا يأتي دور الدالة
MATCH
التي ستبحث عن الرقم 1 في الصفيف لتعيد لنا موقعه ولكن لاحظ أننا إستخدمنا معيار البحث 0 و الذي يعني الوقوف عن ايجاد اول قيمة مطابقة لموضع البحث, و بعد ان يتم تحديد الموقع تكون بداية عمل الدالة
MID
بحيث سيتم عملية الفصل من الموقع الذي تم تحديده و حتى 255 حرف
و بهذا الخطوة نحصل على الكلمة العربية الموجودة داخل الخلية
و لا ننسى أن هذه معادلة صفيف يجب إدخالها بالضغط على
Ctrl+Shift+Enter

و الخطوة الثانية و التي تهدف للحصول على الكلمة الإنجليزية في الخلية
C3
استخدمنا المعادلة البسيطة التالية :

كود PHP:

=TRIM(LEFT(A3,LEN(A3)-LEN(B3)))

__________________
و هي ببساطة لبداية عملية الفصل من الشمال و لمعرفة عدد العناصر التي سنقتطعها نقوم بطرح عدد احرف الخلية الاصلية من عدد احرف الخلية التي تحتوي كلمة عربية
=============
أتمنى ان تستفيدو من شرح هذه الدالة

دمتم في حفظ الله

يحيى حسين
Excel MVP

إضافة الأصفار على يسار الرقم في الاكسيل

2017-01-05T01:07:00.001-08:00

بسم الله الرحمن الرحيم

إحدى القضايا التي تواجهنا عند التعامل مع الاكسيل هي كيفية إضافة الأصفار على يسار الرقم, و صعوبة ذلك بسبب ان الأصفار على اليسار لا قيمة لها, فلذلك يقوم الاكسيل بإلغائها, و هنا السؤال :

كيف يمكنني إضافة الصفر على يسار الرقم في الإكسيل ؟

لحل ذلك يوجد أكثر من طريقة :

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

هذه الطريقة تعتمد على تغيير تنسيق الخلية من وضع العام General الى نص Text

حيث نقوم بتحديد الخلية او الخلايا التي نريد تغيير تنسيقها ثم

Right Click

Text

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

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

بإضافة أصفر بعدد الرقم الذي سيكتب مع الاصفار التي نريد إظهارها, فعلى سبيل المثال :

لو كان لدينا الرقم التالي

798868863

و نريد إضافة صفر قبله ليصبح

0798868863

عدد خانات الرقم قبل الصفر تساوي 9

و عند اضافة صفر سيصبح عددها 10

لذلك سنقوم بوضع عشرة أصفار في تنسيق الخلايا ليضهر الصفر, و لعمل ذلك نتبع الخطوات التالية :

Right Click

Custom

نكتب في خانة Type

0000000000

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

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

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

و بهذه الطريقة سيبقى الرقم كنص

و لمشاهدة الفيديو

دمتم في حفظ الله

يحيى حسين

Excel MVP

طرق تحويل الجدول من أفقي لعامودي في الإكسيل

2017-01-01T22:55:00.000-08:00

بسم الله الرحمن الرحيم

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

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

الطريقة الأولي :

بإستخدام عملية النسخ و اللصق الخاص

Copy and Pase Special

نحدد الجدول الأصلي .
ثم نقوم بنسخ الجدول بإستخدام Ctrl+C
نضع مؤشر الماوس على الخلية A5
نضغط Ctrl+Alt+V لفتح شاشة الـ PaseSpecial
Ok

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

هذه الطريقة تعتمد على إستخدام المعادلات

و المعادلة المستخدمة هنا هي معادلة

TRANSPOSE

و هذه من معادلات الصفيف أي أنه يجب عند إدخال هذه المعادلات الضغط على

Ctrl+Shift+Enter

و اذا تم ادخالها بالضغط على

Enter

لوحدها ستكون النتيجة خطاً

و لتطبيق هذه المعادلة يجب إتباع الخطوات التالية :

تحديد مساحة الجدول الجديد و الذي هو بإتجاه عامود و يجب ان تكون مساحه التحديد مساوية لمساحة الجدول من حيث عدد الأسطر و الأعمدة و اذا كان هناك زيادة ستظهر كخطأ.
كتابة المعادلة TRANSPOSE
و متغيرات هذه المعادلة تحديد الجدول الرئيسي كامل
الضغط على Ctrl+Shift+Enter

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

أيضاً هذه الطريقة تعتمد على المعادلات و المعادلة المستخدمة هنا هي :

INDEX

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

نحدد الجدول كما في الطريقة الثانية أو نضع مؤشر الماوس على الخلية A5
نكتب المعادلة التالية
ثم نضغط Enter
و نقوم بسحبها حسب مساحة الجدول.

الطريقة الرابعة :

أيضاً هذه الطريقة تعتمد على المعادلات و المعادلة المستخدمة هنا هي :

Offset

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

نحدد الجدول كما في الطريقة الثانية أو نضع مؤشر الماوس على الخلية A5
نكتب المعادلة التالية
ثم نضغط Enter
و نقوم بسحبها حسب مساحة الجدول.

هذه أربعة طرق سريعة و سهله للتعامل مع هذه الحالة

و لمشاهدة الفيديو من هنا

أتمنى لكم المتعة و الفائدة

دمتم في حفظ الله

يحيى حسين

Excel MVP

حذف الأسطر الفارغة في الاكسيل

2016-12-25T22:27:00.002-08:00

بسم الله الرحمن الرحيم

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

فالأسطر الملونة باللون الأصفر نريد حذفها
الأخطوة الأولى : أن نقوم بتحديد أحد الأعمدة من بداية الجدول إلى نهايته, ثم الضغط على
F5
او
Ctrl + G

ثم نختار
Special

و نختار
Blanks
و ذلك حتى يقوم الاكسيل بتحديد فقط الأسطر الفارغة
ثم
Right Click
على أي خلية فارغه و نختار
Delete

و سيظهر الخيار التالي لتحديد الحذف و نختار هنا
Entire Row

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

لمشاهدة الفيديو من هنا

دمتم في حفظ الله

يحيى حسين
Excel MVP

البحث داخل التعليقات في الاكسيل بدل من البحث داخل الخلايا

2016-12-19T22:25:00.000-08:00

بسم الله الرحمن الرحيم

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

ثم نبدأ بعملية البحث بإستخدام
Ctrl + F
و سنبحث عن كلمة
Excel4Us

الخيارات التلقائية في مربع البحث لا تسمح لنا بالبحث داخل التعليقات و لذلك من الخيار
Option
سنقوم بتغيير خيارات الـ
Look in
الى
Comments

ثم نعبيد عملية البحث و سيقوم الاكسيل بتحديد جميع الخلايا التي تحتوي عبارة
Excel4US

و لمشاهدة الفيديو

دمتم في حفظ الله

يحيى حسين
Excel MVP

لماذا لا يمكنني فلترة البيانات للتواريخ بكتابة التاريخ في خانة البحث في خيارات الفلترة

2016-12-18T22:11:00.000-08:00

بسم الله الرحمن الرحيم

سؤال طرحه علي أحد الأصدقاء
لماذا لا يمكنني فلترة البيانات للتواريخ بكتابة التاريخ في خانة البحث في خيارات الفلترة
و لتوضيح سؤاله
عندما نقوم بفلترة بيانات في الإكسيل تحتوي تاريخ , يقوم الإكسيل بعمل عملية تجميع
Group for dates
و لذلك تظهر خيارات الفلترة بالشكل التالي :

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

فلن نحصل على نتائج و السبب في عدم الحصول على نتائج هو عملية التجميع التي قام بها الإكسيل للتواريخ و التي منعته من أن يجد نتائج مطابقة لعملية البحث, و لحل هذه المشكلة يجب أن نلغي خيارات التجميع في الاكسيل للتواريخ, و لعمل ذلك نتبع الخطوات التالية :
File
Option
Advanced
نلغي علامة الصح من الخيار
Group Dates in The AutoFilter menu
Ok

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

عند كتابة أي تاريخ على سبيل المثال
13/06/2016

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

و لمشاهدة الفيديو من هنا

دمتم في حفظ الله

يحيى حسين
Excel MVP

البحث على النت من خلال قيمة خلية في الاكسيل مباشرة بالضغط على ALT

2016-12-17T21:46:00.001-08:00

بسم الله الرحمن الرحيم

هذا الموضوع يوضح كيفية إستخدام بعض خصائص الاكسيل في عملية البحث على النت من خلال إستخدام
ALT
و كليك بالماوس
لفتح خانة
Research
و الحصول على مواقع النت التي تتحدث عن نتيجة البحث, و لتوضيح هذه الآلية
سنقوم بكتابة إسم
Yahya Hussien

في أي خلية من خلايا الإكسيل

ثم بالضغط على
ALT
و على الماوس سيقوم الإكسيل بفتح خانة
Research
و يعطينا جميع النتائج المرتبطة في الخلية الموجودة في الإكسيل من خلال موقع Bing

و بالضغط على أي رابط سيتم فتح الرابط مباشرة

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

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

ثم من الضغط بالماوس على الخلية

و سيقوم الإكسيل بالبحث عن هذا الخطأ

و لمشاهدة الفيديو

أتمنى أن تكون المعلومة مفيدة لكم

دمتم في حفظ الله

يحيى حسين
Excel MVP

كيفية إظهار متغيرات المعادلات في الاكسيل بإستخدام Ctrl Shift A

2016-12-16T22:22:00.000-08:00

بسم الله الرحمن الرحيم

سنتحدث عن كيفية إظهار متغيرات المعادلة في الاكسيل في نفس الخلية

فكما هو معلوم لدينا عند كتابة أي معادلة في الاكسيل و فتح أول قوس لها يظهر الإكسيل

Tips

صغيرة تحت المعادلة لمعرفة هذه المتغيرات و لتحديد موقعنا من هذه المتغيرات

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

Ctrl + Shift +A

لتكون النتيجة بالشكل التالي :

و لمشاهدة الفيديو لهذه العملية

دمتم في حفظ الله

يحيى حسين

Excel MVP

الوقت و التاريخ في نفس الخلية و كل منهما في سطر بإستخدام المعادلات و الإختصار Ctrl J

2016-12-14T22:16:00.000-08:00

بسم الله الرحمن الرحيم

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

الطريقة الأولي :

و هي بالمعادلات و سنستخدم معادلات

NOW

TEXT

CHAR

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

Wrap Text

و المعادلة المستخدمة هنا هي :

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

Wrap Text

من تاب

HOME

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

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

NOW

في الخلية

ثم من خيارات تنسيق الخلايا

Right Click --> Format Cells

Custom

نضع مؤشر الماوس ما بين تنسيق التاريخ و تنسيق الوقت و نضغط على الإختصار

Ctrl J

ثم أوكي

و هذه هي النتيجة النهائية

و لمشاهدة الفيديو من خلال هذا الرابط

دمتم في حفظ الله

يحيى حسين

Excel MVP

طرق تحويل الرقم الى تاريخ

2016-12-13T21:33:00.001-08:00

الطريقة الثالثة : Convert From Number To Date طريقة تحويل الرقم إلى تاريخ بإستخدام FlashFill

2016-12-12T22:12:00.000-08:00

بسم الله الرحمن الرحيم

في التدوينة السابقة تحدثنا عن طريقة تحويل الرقم إلى تاريخ بإستخدام المعادلات و يمكنك الإطلاع عليها من هنا
في تدوينة أخرى بإستخدام خاصية TextToColumn و للإطلاع عليها من هنا

أما الطريقة الثالثة
فهي تعتمد على نسخة الأوفيس في جهازك حيث أنها تتطلب
Excel 2013
Excel 2016

و هي تعرف بإسم
FlashFill

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

ثم من تاب
Data
نختار
FlashFill

و إختصارها على الكيبورد
Ctrl + E

و سنحصل على النتيحة التالية

و لمشاهدة الفيديو من هنا

أتمنى لكم المتعة و الفائدة
دمتم في حفظ الله

يحيى حسين
Excel MVP

الطريقة الثانية : Convert From Number To Date طريقة تحويل الرقم إلى تاريخ بإستخدام TextToColumn

2016-12-11T00:25:00.004-08:00

بسم الله الرحمن الرحيم

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

و في موضوع اليوم سنضيف طريقة أخرى بإستخدام خاصية
TextToColumn

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

ثم من تاب
Data
نختار
TextToColumn

و الخطوة الأولى نبقيها كما هي :

و الخطوة الثانية نبقيها كما هي :

أما في الخطوة الثالثة
من ضمن مجموعة
Column data Format
نختار
Date
و من خيارات ال
Dropdown list
نختار التنسيق المتناسب مع البيانات

و في حالتنا
dmy
و هي ترمز
DayMonthYear

ثم الخطوة الأخيرة

و ستكون النتيجة بالشكل التالي

و لمشاهدة الفيديو من هنا

أتمنى لكم المتعة و الفائدة

دمتم في حفظ الله

يحيى حسين
Excel MVP

الطريقة الأولى : Convert From Number To Date طريقة تحويل الرقم إلى تاريخ

2016-12-10T05:36:00.000-08:00

بسم الله الرحمن الرحيم

الطريقة الأولى لتحويل الرقم الى تاريخ
واحدة من المشاكل التي تواجهننا إذا وجد لدينا الرقم
15012016
و هو في الأساس يمثل تاريخ 15/01/2016

و نريد تحويله إلى تاريخ الحالة الحقيقة للتاريخ
15/01/2016
في هذه الطريقة سنتلكم عن إستخدام المعادلات لحل هذه المشكلة
و المعادلات التي سنستخدمها هي :
LEFT,MID & RIGHT

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

و بالنسبة رقم الشهر فإنها الرقمين في الوسط
01
و سنستخدم معادلة
MID
كما يلي

و رقم السنة هو أخر أربع أرقام
2015
و سنستخدم معادلة
RIGHT
كما يلي

و تبقى الخطوة الأخير لدمجهم الثلاثة معاً في في خلية واحدة من خلال المعادلة التالية :
DATE
كما يلي

و بذلك نحصل على التاريخ بالشكل التالي

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

دمتم في حفظ الله

يحيى حسين
Excel MVP

الدمج بإستخدام المعادلات Consolidate by Formula

2016-05-07T08:56:00.000-07:00

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

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

Q1-09
Q2-09
Q3-09
Q4-09

و كما نلاحظ فإن ترتيب البيانات في الصفحات الأربعة مطابق من ناحيتين :
الأولى : أن أسماء الـ Headers واحدة
الثانية : أن القيم الموجودة في العامود على اليسار هي أساس الجمع

و ما سنقوم به الآن هو إستخدام معادلات لجمع القيم في جميع الصفحات في صفحة واحدة :
سنقوم بكتابة أسماء المنتجات في العامود A

و سنستخدم المعادلة التالية :

و لفهمها فإن الجزء المرتبط في المعادلة Sumif
يقوم بجمع الشروط من أكثر من صفحة, فالمتعارف عليه أن هذه الدالة تقوم بالجمع من جدول واحد , و لجعلها تجمع من اكثر من صفحة و لإعطائها قوة الـ Array
استخدمنا المعادلة
Sumproduct
أما المعادلات التي أستخدمت لبناء إسم الصفحات فهي
INDIRECT
و

ROW(INDIRECT("1:4"))

فهذه الدالة قامت ببناء تسلسل رقمي من 1 الى 4 و هو عدد الأرباع السنوية التي نريد جمعها و المعادلة الأولى

INDIRECT
قامت إرجاع القيم النصية الناتجه عن ربط

"'Q"&ROW(INDIRECT("1:4"))&"-09'!B1:B100"

إلى مرجع حتى تتمكن دوال الجمع من جمع القيم حسب أسماء الصفحات

و ذلك لتأخذ شكلها النهائي

=SUMPRODUCT(SUMIF(INDIRECT("'Q"&ROW(INDIRECT("1:4"))&"-09'!B1:B100"),A4,INDIRECT("'Q"&ROW(INDIRECT("1:4"))&"-09'!C1:C100")))

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

دمتم في حفظ الله

يحيى حسين

Excel MVP

جمع أكبر خمس قيم في الإكسيل بأكثر من طريقة

2016-04-28T08:45:00.002-07:00

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

في موضوع اليوم سا أتحدث عن طريقة جمع أكبر خمس قيم, فلو كان لدينا المبيعات التالية :

و أردنا جمع أكبر خمس قيم فإن العديد من مستخدمي الإكسيل سيلجأون إلى إستخدام الفرز و من ثم جمع القيم او منهم من يلجأ إلى إستخدام معادلة

Large

و يقوم بتكرارها خمس مرات و في كل مرة يقوم بتغيير المتغير الأخير ما بين ١ الى خ .

و في النهاية سيحصل على القيمة التي يرغب بها، و لكن الطريقة ستكون متعبه، كما في الصورة التالية :

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

SUM

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

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

و هي في الصورة أعلاه معادلة صفيف تحتاج للإدخال بصيغة الصفيف بالضغط على

Ctrl + Shift + Enter.

و إذا أردنا إستخدامها بدون صيغة الصفيف فيمكن ذلك من خلال إستخدام معادلة

SUMPRODUCT

بالشكل التالي :

و بذلك نكون قمنا بتغطية عدة طرق، و ملخصها كالتالي :

لمشاهدة الفيديو

دمتم في حفظ الله

يحيى حسين

Excel MVP

المعادلة DSUM

2016-03-01T00:24:00.000-08:00

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

الدالة DSUM

دالة تقوم بالجمع بشرط أو عدة شروط و أهم ما يميزها و هذه الشروط تحدد في بالإعتماد على رؤوس الأعمدة Headers

متغيرات المعادلة

متغيرات المعادلة هي :
DataBase : مقصود بها الجدول الذي يحتوي البيانات
Field : رأس العامود او ما يعرف بالـ
Header
و الذي نريد جمع البيانات منه
Criteria : معيار البحث و يجب أن تحدد مع الـ
Header

ملاحظة : متغير الـ Field يمكن كتابته بين علامتي تنصيص ""
كما سنراه في المثال ان شاء الله

لفهم المعادلة و التطبيق عليها سنقوم بعمل جدول بسيط يحتوي البيانات التالية :

لجمع مبيعات Yahya

سنقوم بإضافة منطقة إستعلام تحتوي نفس رؤوس الأعمدة كما في الصورة التالية

في الخلية E2 سنقوم بكتابة إسم المندوب لجمع مبيعاته سواء كميات أو مبيعات
في الخلية F2 سنقوم بكتابة المعادلة التالية

=DSUM(A1:C13,F1,E1:E2)

المتغير الأول : A1:C13
يمثل مساحة الجدول الذي يحتوي البيانات

المتغير الثاني : F1
يمثل العامود الذي يحتوي القيم الرقمية التي سيتم جمعها

المتغير الثالث : E1:E2
يمثل المعيار و هو اسم المندوب و هنا يجب ملاحظة أن المعيار إحتوى Headers

الشكل الثاني للمعادلة

=DSUM(A1:C13,"Sales",E1:E2)

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

"Sales"

مثال آخر على تعدد الشروط في هذه الدالة

نريد جمع مبيعات يحيى و التي تجاوزت كمايتها 25

في هذه الحالة المعيار أصبح مرتبط بشرطين هما

اسم المندوب : Yahya

و الكمية : أكبر من 25

و لتطبيق المعادلة

من الأمثلة اعلاه نلاحظ ان هذه المعادلة يمكنها العمل على معيار واحد او اكثر من معيار

و نلاحظ أهميه Headers في التعامل معها .

أتمنى لكم المتعة و الفائدة إن شاء الله

يحيى حسين

Excel MVP