اكسل

29 طريقة لتوفير الوقت باستخدام صيغ Excel

29 Ways Save Time With Excel Formulas

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



فيديو: 20 نصيحة لتوفير الوقت باستخدام صيغ Excel

1. لا تقم بإضافة الأقواس الأخيرة إلى دالة

لنبدأ بشيء سهل حقًا! عندما تقوم بإدخال دالة واحدة بمفردها (SUM ، AVERAGE ، إلخ.) لن تحتاج إلى إدخال أقواس الإغلاق النهائية. على سبيل المثال ، يمكنك فقط إدخال:





= SUM (A1: A10

والصحافة عودة. سيضيف Excel أقواس الإغلاق نيابة عنك. إنه شيء صغير ، لكنه مناسب.



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

قبل - تم حذف أقواس الإغلاق

بعد الضغط على مفتاح الإدخال - يتم إضافة الأقواس تلقائيًا

2. انقل صيغة وابقِ المراجع من التغيير

إحدى أقوى ميزات Excel هي العناوين النسبية - عندما تنسخ صيغة إلى موقع جديد ، ستتغير جميع العناوين ذات الصلة. غالبًا ما يكون هذا هو ما تريده بالضبط ، لأن إعادة استخدام الصيغ هي إحدى أهم تقنيات ورقة عمل جيدة التصميم وسهلة الصيانة.

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

إذا كنت تقوم فقط بنقل صيغة إلى موقع قريب ، فحاول السحب والإفلات. سيؤدي السحب إلى الحفاظ على جميع العناوين سليمة وبدون تغيير.

انتزاع حافة التحديد

اسحب إلى مكان جديد

إذا قمت بنقل صيغة إلى مكان بعيد ، فاستخدم القص واللصق. (Win: Ctrl + X، Ctrl + V Mac: Cmd + X، Cmd + V) عندما تقوم بقص صيغة ، فإن مراجعها لا تتغير.

3. انسخ صيغة وامنع تغيير المراجع

ملاحظة: يمكنك تغيير جميع مراجع الخلايا لجعلها مطلقة ، لكن هذه التلميح تفترض أنك لا تريد القيام بذلك ، لأي سبب من الأسباب.

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

لنسخ أ مجموعة من الصيغ إلى موقع جديد دون التأثير على المراجع ، يمكنك استخدام البحث والاستبدال. حدد الصيغ التي تريد نسخها ، ثم ابحث عن واستبدل علامة المساواة (=) في الصيغ بحرف التجزئة (#). سيؤدي هذا إلى تحويل الصيغ إلى نص. الآن انسخ الصيغ والصقها في موقع جديد. بعد لصق الصيغ ، ومع تحديد جميع الصيغ ، قم بعكس عملية البحث والاستبدال. ابحث عن التجزئة (#) واستبدل علامة التساوي (=). سيؤدي هذا إلى استعادة الصيغ إلى أمر العمل. (تنسب إليه: توم أرتيس )

4. انقر نقرًا مزدوجًا فوق مقبض التعبئة لنسخ الصيغ لأسفل

عندما تضيف الصيغ إلى الجداول ، فإن أحد الأشياء التي تقوم بها في أغلب الأحيان هو نسخ الصيغة من الصف الأول في الجدول إلى الصف الأخير في الجدول. إذا كنت تعرف اختصارات لوحة المفاتيح للتنقل بين البيانات في Excel ، فيمكنك استخدامها للصق بسرعة في عمود كامل من الصيغ بضربات مفاتيح قليلة. ومع ذلك ، فإن مقبض التعبئة أسرع ، لأنه لا داعي للتنقل إلى أسفل الجدول.

مقبض التعبئة هو المستطيل الصغير الموجود في الزاوية اليمنى السفلية لجميع التحديدات في Excel. طالما أن الصيغة الموجودة في عمود بجوار عمود آخر تحتوي على مجموعة كاملة من البيانات ، يمكنك فقط النقر نقرًا مزدوجًا فوق مقبض التعبئة لنسخ الصيغة بالكامل إلى أسفل الجدول.

مقبض التعبئة

يقوم Excel بنسخ الصيغة لأسفل

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

إذا لم يكن التحديد كبيرًا جدًا ، يمكنك أيضًا تعبئة الصيغ أسفل ورقة عمل باستخدام الاختصار لـ Fill Down (Control + D). فقط تأكد من تحديد الصيغة الأصلية والخلايا المستهدفة أولاً. هذا أسرع من النسخ / اللصق ، ولكن ليس بنفس سرعة مقبض التعبئة ، خاصة إذا كنت تنسخ الصيغة في مجموعة كبيرة من الخلايا.

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

5. استخدم جدولاً لإدخال الصيغ تلقائيًا

هناك طريقة أسرع لإدخال الصيغ وهي تحويل الجدول أولاً إلى جدول Excel رسمي. المصطلحات هنا محيرة ، نظرًا لأن أي بيانات تحتوي على أكثر من عمود هي 'جدول' تقنيًا ولكن Excel له بنية رسمية تسمى الجدول الذي يوفر العديد من الفوائد. بمجرد تحويل بياناتك إلى جدول (كلا النظامين الأساسيين: Ctrl + T) ، سيتم تلقائيًا نسخ جميع الصيغ التي تدخلها في الصف الأول إلى أسفل طول الجدول بالكامل. هذا يوفر الكثير من الوقت ويساعد أيضًا على منع الأخطاء.

أدخل الصيغة بشكل طبيعي

اضغط على Enter لنسخ الصيغة إلى أسفل

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

ملاحظة: ستستخدم الصيغ في الجدول المراجع المنظمة تلقائيًا (على سبيل المثال في المثال أعلاه = [@ Hours] * [@ Rate] بدلاً من = C4 * D4. ومع ذلك ، يمكنك كتابة عناوين الخلايا مباشرةً وسيتم الاحتفاظ بها.

6. استخدم علامة التبويب 'الإكمال التلقائي' لإدخال الوظائف

عند إدخال علامة التساوي والبدء في الكتابة ، سيبدأ Excel في مطابقة النص الذي تدخله مقابل القائمة الضخمة من الوظائف المتوفرة. أثناء الكتابة ، سترى قائمة بالوظائف 'المرشحة' تظهر أدناه. سيتم تضييق هذه القائمة مع كل حرف تكتبه. بمجرد تحديد الوظيفة التي تريدها في القائمة ، يمكنك 'مطالبة' Excel بإدخالها لك بالضغط على علامة التبويب. في Windows ، يتم تحديد الوظائف تلقائيًا أثناء الكتابة. يتم عرض خيارات على جهاز Mac ولكن لم يتم تحديدها ، لذلك عليك اتخاذ خطوة أخرى: استخدم مفتاح السهم لتحديد الوظيفة التي تريدها ، ثم اضغط على علامة التبويب أو ارجع لجعل Excel يدخل الوظيفة نيابة عنك.

قم بتمييز أفضل تطابق واضغط على علامة التبويب

وظيفة الإكمال التلقائي لبرنامج Excel

7. استخدم Control + انقر لإدخال الوسائط

لا تحب كتابة الفواصل بين الحجج؟ يمكنك جعل Excel يقوم بذلك نيابة عنك. عندما تقوم بإدخال وسيطات في دالة ، ما عليك سوى الضغط باستمرار على Control (Mac: Command) أثناء النقر فوق كل مرجع وسيقوم Excel تلقائيًا بإدخال الفواصل نيابة عنك. على سبيل المثال ، يمكنك إدخال صيغة مثل: = SUM (A1، B10، C5: C10) عن طريق إدخال '= SUM (' ثم الضغط على مفتاح التحكم مع النقر فوق كل مرجع. سيعمل هذا مع أي دالة حيث تقوم بتوفير المراجع كوسيطات.

انقر فوق الخلية التالية Control أو Command

انقر فوق Control أو Command مرة أخرى

تم إدخال جميع الفواصل بواسطة Excel

8. استخدم نافذة تلميح الصيغة لتحديد الوسائط

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

قم بالمرور فوق الوسيطة في تلميح شاشة الصيغة

انقر وسيطة لتحديد داخل الصيغة

تريد معرفة المزيد؟ نحن نقدم دورة كاملة في صيغ ووظائف Excel .

9. إدراج العناصر النائبة وسيطة الدالة مع اختصار

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

عندما تقوم بإدخال دالة ، بعد أن يتعرف Excel على اسم الوظيفة ، اكتب Control + Shift + A (كلا النظامين الأساسيين). على سبيل المثال ، إذا كتبت '= DATE (' ثم استخدمت Control + Shift + A ، فسيعطيك Excel '= DATE (السنة ، الشهر ، اليوم)'. يمكنك بعد ذلك النقر نقرًا مزدوجًا فوق كل وسيطة (أو استخدام تلميح الوظيفة نافذة لتحديد كل وسيطة) وتغييرها إلى القيمة التي تريدها.

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

يدرج Control + Shift + A الوسيطات المسماة

10. حرك 'الشيء المعادل' بعيدًا عن طريقك

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

انتزاع حافة طرف الشاشة

اسحب تلميح الشاشة بعيدًا عن طريقك

11. قم بتبديل عرض جميع الصيغ مرة واحدة

عندما تقوم بتحرير خلية تحتوي على صيغة ، يعرض Excel الصيغة تلقائيًا بدلاً من النتيجة. لكن في بعض الأحيان قد تتمكن من رؤية كل الصيغ في ورقة العمل في وقت واحد. للقيام بذلك ، ما عليك سوى استخدام اختصار لوحة المفاتيح لعرض الصيغ: Control + ~ (هذه علامة تلدة). باستخدام هذا الاختصار ، يمكنك التبديل بسرعة بين عرض جميع الصيغ في ورقة العمل أو إيقاف تشغيلها. هذه طريقة رائعة لمشاهدة جميع الصيغ مرة واحدة ، وللتحقق من الصيغ للتأكد من تناسقها.

يكشف Control + `عن جميع الصيغ

جميع الصيغ مرئية

12. حدد كل الصيغ في ورقة العمل مرة واحدة

هناك طريقة أخرى لمشاهدة جميع الصيغ في ورقة العمل وهي تحديدها. يمكنك القيام بذلك باستخدام الميزات الأكثر قوة (والمخفية) في Excel: الانتقال إلى> خاص (Ctrl + G). باستخدام هذا الأمر ، يمكنك تحديد جميع أنواع الأشياء المثيرة للاهتمام في Excel ، بما في ذلك الخلايا الفارغة والخلايا التي تحتوي على أرقام والخلايا الفارغة وغير ذلك الكثير. أحد الخيارات هو الخلايا التي تحتوي على صيغ. لتحديد جميع الخلايا التي تحتوي على صيغ في ورقة عمل ، ما عليك سوى كتابة Ctrl + G لإظهار مربع الحوار Go To ، ثم انقر فوق الزر Special ، ثم حدد Formulas. عند النقر فوق 'موافق' ، سيتم تحديد جميع الخلايا التي تحتوي على الصيغ.

Control + G لفتح Go To special

انقر فوق الزر خاص

حدد الصيغ

تم تحديد جميع الصيغ

إذا كنت تريد تحديد مجموعة فرعية فقط من الصيغ في ورقة عمل ، فحدد أولاً ، ثم استخدم نفس الأمر.

13. استخدم لصق خاص لتحويل الصيغ إلى قيم ثابتة

هناك مشكلة شائعة في Excel وهي الحاجة إلى إيقاف تغيير القيم المحسوبة. على سبيل المثال ، ربما ترغب في تبسيط ورقة العمل عن طريق إزالة أعمدة 'المساعدة' التي استخدمتها لإنشاء قيم معينة. ولكن إذا حذفت هذه الأعمدة مع استمرار الصيغ التي تشير إليها ، فستحصل على عدد كبير من أخطاء #REF. الحل هو أولاً تحويل الصيغ إلى قيم ، ثم حذف الأعمدة الإضافية. إن أبسط طريقة للقيام بذلك هي استخدام 'لصق خاص'. أولاً ، حدد الصيغ التي تريد تحويلها ونسخها إلى الحافظة. بعد ذلك ، مع استمرار تحديد الصيغ ، افتح مربع الحوار لصق خاص (Win: Ctrl + Alt + V ، Mac: Ctrl + Cmd + V) واستخدم خيار القيم. سيؤدي هذا إلى استبدال جميع الصيغ التي حددتها بالقيم التي حسبتها.

تم تحديد الصيغ ونسخها

استدعاء لصق خاص

لا مزيد من الصيغ!

14. استخدم 'لصق خاص' لضبط القيم في مكانها

مشكلة أخرى شائعة في Excel هي الحاجة إلى تغيير الكثير من القيم في مكانها. على سبيل المثال ، ربما لديك قائمة تضم 500 سعر منتج وتحتاج إلى زيادة جميع الأسعار بنسبة 5٪. أو ربما لديك قائمة من 100 موعد يجب نقلها جميعًا إلى المستقبل بأسبوع واحد؟ في مثل هذه الحالات ، يمكنك إضافة عمود 'مساعد' إلى جدولك ، وإجراء العمليات الحسابية المطلوبة ، وتحويل النتائج إلى قيم ، ثم نسخها فوق العمود الأصلي. ولكن إذا كنت تحتاج فقط إلى عملية حسابية بسيطة ، فإن Paste Special يكون أبسط وأسرع بكثير ، لأنه يمكنك تغيير القيمة مباشرة دون أي صيغ إضافية.

على سبيل المثال ، لتحويل مجموعة من التواريخ في مكانها إلى ما بعد أسبوع واحد ، قم بما يلي: أضف الرقم 7 إلى أي خلية في ورقة العمل ، ثم انسخها إلى الحافظة. بعد ذلك ، حدد جميع التواريخ التي تريد تغييرها. ثم استخدم لصق خاص> عمليات> إضافة. عند النقر فوق 'موافق' ، سيضيف Excel الرقم 7 إلى التواريخ التي حددتها ، ونقلها إلى الأمام في الوقت المناسب لمدة 7 أيام ، دون الحاجة إلى إنشاء أعمدة مساعدة.

انسخ القيمة المؤقتة وحدد التواريخ

لصق خاص مع القيم + إضافة

تم تقديم جميع التواريخ بمقدار 7 أيام

لزيادة مجموعة الأسعار بنسبة 10٪ ، استخدم نفس الأسلوب. أدخل 1.10 في خلية ، وانسخها إلى الحافظة. ثم حدد الأسعار التي تريد تغييرها ، واستخدم لصق خاص> عمليات> مضاعفة لتحويل جميع الأسعار في مكانها الصحيح. بمجرد أن تتعلم هذه النصيحة ، ستجد الكثير من الاستخدامات الذكية لها.

انسخ القيمة المؤقتة وحدد الأسعار

لصق خاص بالقيم + ضرب

زيادة جميع الأسعار بنسبة 10٪

ملاحظة: هذا يعمل فقط مع القيم. لا تحاول ذلك مع الصيغ!

15. استخدم النطاقات المسماة لجعل الصيغ أكثر قابلية للقراءة

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

= B3 * 1 دولار أسترالي
= B4 * $ A $ 1
= B5 * 1 دولار أسترالي

ولكن إذا قمت بتسمية الخلية A1 باسم 'hourly_rate' ، فستبدو الصيغ كما يلي:

= B3 * hourly_rate
= B4 * hourly_rate
= B5 * hourly_rate

نطاقات التسمية بهذه الطريقة تجعل الصيغ أكثر قابلية للقراءة ، وتوفر إدخال الكثير من علامات الدولار ($) لإنشاء مراجع مطلقة.

نطاقات التسمية سهلة. ما عليك سوى تحديد النطاق / الخلية (الخلايا) التي تريد تسميتها ، ثم اكتب اسمًا في مربع الاسم واضغط على إدخال. الآن بعد أن قمت بتسمية نطاق ، سيستخدمه Excel كلما قمت بالإشارة والنقر فوق النطاق أثناء قيامك بإنشاء صيغة - عند النقر فوق نطاق مسمى ، سترى اسمه مدرجًا تلقائيًا في الصيغة. على سبيل المكافأة ، يمكنك أيضًا التنقل بسهولة إلى النطاق المحدد وقتما تشاء. ما عليك سوى تحديد الاسم من القائمة المنسدلة التي تظهر بجوار مربع الاسم.

صيغة عادية بدون نطاق الأسماء

تسمية الخلية C2

تم تحديث الصيغة لاستخدام النطاق المسمى

16. تطبيق الأسماء على الصيغ الموجودة تلقائيًا

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

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

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

ما هو مرجع خلية مختلطة

قم بتطبيق الأسماء على علامة التبويب الصيغ الموجودة على الشريط

حدد النطاق المسمى لتطبيقه

النطاق المحدد يتم تطبيقه تلقائيًا

17. احفظ صيغة لم يتم القيام بها

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

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

18. كن على علم بالوظائف التي يوفرها برنامج Excel

توجد وظائف لحل مشاكل محددة. يمكنك التفكير في دالة على أنها صيغة مسبقة الصنع تحمل اسمًا معينًا ، والغرض ، وقيمة الإرجاع. على سبيل المثال ، للدالة PROPER غرض واحد فقط: إنها تجعل الكلمات كبيرة. أعطه نصًا مثل 'nEW york CiTY' ، وسيعيدك 'New York City'. تكون الوظائف سهلة الاستخدام بشكل لا يصدق عندما تحل مشكلة لديك ، لذلك من المنطقي أن تتعرف على الوظائف المتوفرة في Excel.

ارى: دليل وظائف Excel و 500 من أمثلة صيغة Excel للحصول على قائمة بالدوال والصيغ شائعة الاستخدام التي تستخدمها.

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

19. استخدم F4 للتبديل بين المراجع النسبية والمطلقة

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

ومع ذلك ، يمكن أن يكون تحويل المراجع ذهابًا وإيابًا بين المراجع النسبية والمطلقة أمرًا مزعجًا - فالكتابة في جميع علامات الدولار ($) مملة وعرضة للخطأ. لحسن الحظ ، هناك اختصار رائع يتيح لك التبديل بسرعة بين الخيارات الأربعة المتاحة لكل مرجع: (Windows: F4 ، Mac: Command + T). ببساطة ضع المؤشر في مرجع باستخدام الاختصار. في كل مرة تستخدمها ، سيقوم Excel 'بالتناوب' إلى الخيار التالي بهذا الترتيب: نسبي بالكامل (A1)> مطلق تمامًا ($ A $ 1)> صف مطلق (A $ 1)> عمود مطلق ($ A1).

20. تذكر أن الصيغ والوظائف ترجع قيمة. دائما.

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

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

21. استخدم F9 لتقييم أجزاء من الصيغة

يمكن للاختصار F9 (fn + F9 على جهاز Mac) حل أجزاء من الصيغة في الوقت الفعلي. هذه أداة رائعة لتصحيح أخطاء الصيغ الكبيرة ، عندما تحتاج إلى التحقق من أن نتيجة جزء معين من الصيغة هي ما تتوقعه. لاستخدام هذا التلميح ، قم بتحرير الصيغة وحدد التعبير أو الوظيفة التي تريد تقييمها (نصيحة - استخدم نافذة تلميح الوظيفة لتحديد الوسائط بالكامل). واحد لديك تحديد ، اضغط F9. ستلاحظ استبدال هذا الجزء من الصيغة بالقيمة التي ترجعها.

ملاحظة: في نظام التشغيل Windows ، يمكنك التراجع عن F9 ، ولكن لا يبدو أن هذا يعمل على جهاز Mac. للخروج من الصيغة دون إجراء تغييرات ، ما عليك سوى استخدام Esc.

فيديو: كيفية التحقق من الصيغة وتصحيحها باستخدام F9

22. استخدم أداة تقييم الصيغة لحل المعادلة خطوة بخطوة

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

فيديو: كيفية تقييم الصيغ المعقدة

ملاحظة: تقييم الصيغة متاح فقط في إصدار Windows من Excel. يتخذ إصدار Mac أسلوبًا مختلفًا ، يسمى Formula Builder ، والذي يعرض النتائج أثناء إنشاء صيغة. إنها ليست نفس الوظيفة تمامًا ، ولكنها مفيدة جدًا ، ويمكنك استخدامها مع الصيغ الموجودة بالفعل.

23. بناء صيغ معقدة في خطوات صغيرة

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

فيديو: كيفية بناء صيغة معقدة خطوة بخطوة

24. استخدام النطاقات المسماة مثل المتغيرات

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

25. استخدم التسلسل في التسميات لتوضيح الافتراضات

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

على سبيل المثال ، عادةً ، قد يكون لديك ملصق يقول 'تكلفة القهوة' متبوعًا بتكلفة محسوبة. باستخدام التسلسل ، يمكنك جعل الملصق يقول 'تكلفة القهوة (2.00 دولار / فنجان): مما يوضح أنك قد افترضت تكلفة 2.00 دولار / فنجان.

فيديو: توضيح الافتراضات مع التسلسل

26. أضف فواصل الأسطر إلى IFs المتداخلة لتسهيل قراءتها

عندما تقوم بإنشاء صيغة IF متداخلة ، فإن تتبع الحجج الصحيحة والخطأ في عاصفة من الأقواس يمكن أن يكون مربكًا للغاية. عندما تتجادل بين الأقواس ، من السهل ارتكاب خطأ في المنطق. ومع ذلك ، هناك طريقة بسيطة تجعل من السهل قراءة صيغة تحتوي على عبارات IF متعددة: ما عليك سوى إضافة فواصل أسطر إلى الصيغة بعد كل وسيطة TRUE. سيؤدي ذلك إلى جعل الصيغة أقرب إلى الجدول.

فيديو: كيفية تسهيل قراءة IFs المتداخلة

27. أدخل الوظائف باستخدام الإكمال التلقائي

عندما تقوم بإدخال وظيفة ، سيحاول برنامج Excel تخمين اسم الوظيفة التي تريدها ، ويقدم قائمة الإكمال التلقائي لتختار منها. السؤال هو ، كيف تقبل أحد الخيارات المعروضة وما زلت في وضع التحرير؟ الحيلة هي استخدام مفتاح الجدولة. عندما تضغط على علامة التبويب ، يضيف Excel الوظيفة الكاملة ويترك المؤشر نشطًا بين الأقواس حتى تتمكن من ملء الوسيطات حسب الحاجة. على جهاز Mac ، تحتاج إلى استخدام مفتاح السهم لأسفل أولاً لتحديد الوظيفة التي تريد إضافتها ، ثم الضغط على Tab لإدراج الوظيفة.

28. استخدم 'جمع تلقائي' لإدخال صيغ SUM

لا تتاح لك دائمًا فرصة استخدام هذا الجمع التلقائي ، ولكن عندما تفعل ذلك ، يكون ذلك مرضيًا. يعمل 'الجمع التلقائي' مع كل من الصفوف والأعمدة. ما عليك سوى تحديد خلية فارغة إلى اليمين أو أسفل الخلايا التي تريد جمعها ، واكتب Alt + = (Mac: Command + Shift + T). سيخمن Excel النطاق الذي تحاول جمعه وإدراج دالة SUM في خطوة واحدة. إذا كنت تريد أن تكون أكثر تحديدًا ، بحيث لا يخمن Excel ، فحدد أولاً النطاق الذي تنوي جمعه ، بما في ذلك الخلية حيث تريد أن تكون وظيفة SUM.

سوف يقوم 'الجمع التلقائي' بإدراج وظائف SUM متعددة في نفس الوقت. لتجميع عدة أعمدة ، حدد نطاقًا من الخلايا الفارغة أسفل الأعمدة. لتجميع عدة صفوف ، حدد نطاقًا من الخلايا الفارغة في عمود على يمين الصفوف.

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

29. أدخل نفس الصيغة في عدة خلايا مرة واحدة

في كثير من الأحيان ، ستحتاج إلى إدخال نفس الصيغة في مجموعة من الخلايا. يمكنك بالفعل القيام بذلك في خطوة واحدة باستخدام اختصار لوحة المفاتيح Control + Enter. ما عليك سوى تحديد جميع الخلايا في نفس الوقت ، ثم إدخال الصيغة بشكل طبيعي كما تفعل مع الخلية الأولى. بعد ذلك ، عند الانتهاء ، بدلاً من الضغط على Enter ، اضغط على Control + Enter. سيضيف Excel الصيغة نفسها إلى جميع الخلايا في التحديد ، مع ضبط المراجع حسب الحاجة. عند استخدام هذا الأسلوب ، لن تضطر إلى النسخ واللصق أو التعبئة أو استخدام مقبض التعبئة. لقد انتهيت من خطوة واحدة.

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

المؤلف ديف برونز


^