برنامج Excel life hack للمشاركين في إعداد التقارير ومعالجة البيانات

2024 مؤلف: Malcolm Clapton | [email protected]. آخر تعديل: 2024-01-13 00:36

في هذا المنشور ، يشارك Renat Shagabutdinov ، مساعد المدير العام لـ Mann و Ivanov و Ferber Publishing House ، بعض الاختراقات الرائعة في Excel. ستكون هذه النصائح مفيدة لأي شخص يشارك في إعداد التقارير المختلفة ومعالجة البيانات وإنشاء العروض التقديمية.

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

بدائل بسيطة لـ VLOOKUP و HLOOKUP ، إذا لم تكن القيم المطلوبة في العمود الأول من الجدول: LOOKUP و INDEX + SEARCH

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

خلاف ذلك ، هناك خياران:

1. استخدم وظيفة LOOKUP.

   يحتوي على بناء الجملة التالي: LOOKUP (lookup_value ؛ lookup_vector ؛ result_vector). ولكن لكي تعمل بشكل صحيح ، يجب فرز قيم نطاق view_vector بترتيب تصاعدي:

   

2. استخدم مجموعة من وظائف MATCH و INDEX.

   تُرجع الدالة MATCH الرقم الترتيبي لعنصر في المصفوفة (بمساعدتها ، يمكنك العثور على أي صف من الجدول يكون العنصر الذي تم البحث فيه) ، وتقوم الدالة INDEX بإرجاع عنصر مصفوفة برقم معين (سنكتشفه) باستخدام وظيفة MATCH).

   

   بناء جملة الوظيفة:

   • SEARCH (search_value؛ search_array؛ match_type) - بالنسبة لحالتنا ، نحتاج إلى نوع مطابق "مطابقة تامة" ، وهو يتوافق مع الرقم 0.

   • INDEX (مجموعة ؛ line_number ؛ [column_number]). في هذه الحالة ، لا تحتاج إلى تحديد رقم العمود ، لأن المصفوفة تتكون من صف واحد.

كيفية ملء الخلايا الفارغة في قائمة بسرعة

تتمثل المهمة في ملء الخلايا في العمود بالقيم في الأعلى (بحيث يكون الموضوع في كل صف من الجدول ، وليس فقط في الصف الأول من مجموعة الكتب حول الموضوع):

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

كيف تجد أخطاء في صيغة

حساب جزء منفصل من الصيغة

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

1. لحساب جزء من الصيغة مباشرة في شريط الصيغة ، حدد هذا الجزء واضغط على F9:

   

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

2. انقر فوق الزر Calculate Formula في مجموعة الصيغ على الشريط:

   

   في النافذة التي تظهر ، يمكنك حساب الصيغة خطوة بخطوة وتحديد في أي مرحلة وفي أي وظيفة يحدث الخطأ (إن وجد):

   

كيفية تحديد ما تعتمد عليه الصيغة أو تشير إليه

لتحديد الخلايا التي تعتمد عليها الصيغة ، في مجموعة الصيغ على الشريط ، انقر فوق الزر "الخلايا المتأثرة":

تظهر الأسهم للإشارة إلى ما تعتمد عليه نتيجة الحساب.

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

من خلال النقر عليه ، يمكننا أن نرى بالضبط مكان وجود الخلايا أو النطاقات المؤثرة:

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

يسمح لك الزر "إزالة الأسهم" ، الموجود في نفس الكتلة ، بإزالة الأسهم للخلايا المؤثرة ، أو الأسهم للخلايا التابعة ، أو كلا النوعين من الأسهم في وقت واحد:

كيفية العثور على مجموع (العدد ، المتوسط) لقيم الخلايا من أوراق متعددة

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

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

سوف تتلقى مجموع الخلايا بالعنوان B3 من الأوراق "Data1" ، "Data2" ، "Data3":

يعمل هذا العنونة للأوراق الموجودة باتساق … الصيغة كما يلي: = FUNCTION (first_list: last_list! Range reference).

كيفية بناء عبارات القالب تلقائيًا

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

• نقوم بتسلسل النص باستخدام العلامة & (يمكنك استبداله بوظيفة CONCATENATE ، لكن هذا ليس له معنى كبير).
• النص مكتوب دائمًا بعلامات اقتباس ، وتكون دائمًا الإشارات إلى الخلايا التي تحتوي على نص بدونها.
• للحصول على حرف الخدمة "علامات الاقتباس" ، استخدم الدالة CHAR مع الوسيطة 32.

مثال على إنشاء عبارة نموذجية باستخدام الصيغ:

نتيجة:

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

TEXT (قيمة ؛ تنسيق)

يتم تحديد التنسيق بعلامات اقتباس ، تمامًا كما لو كنت تقوم بإدخال تنسيق مخصص في نافذة تنسيق الخلايا.

يمكن أيضًا أتمتة النصوص الأكثر تعقيدًا. في ممارستي ، كانت هناك أتمتة التعليقات الطويلة ولكن الروتينية لتقارير الإدارة في الشكل "انخفض / ارتفع المؤشر بمقدار XX بالنسبة للخطة ، ويرجع ذلك أساسًا إلى نمو / انخفاض FACTOR1 بحلول XX ، نمو / انخفاض FACTOR2 بواسطة YY … "بقائمة متغيرة من العوامل. إذا كنت تكتب مثل هذه التعليقات في كثير من الأحيان ويمكن خوارزمية عملية كتابتها ، فمن الجدير بالاهتمام بمجرد إنشاء صيغة أو ماكرو يوفر لك على الأقل بعض العمل.

كيفية تخزين البيانات في كل خلية بعد التسلسل

عند دمج الخلايا ، يتم الاحتفاظ بقيمة واحدة فقط. يحذر Excel من هذا عند محاولة دمج الخلايا:

وفقًا لذلك ، إذا كانت لديك صيغة تعتمد على كل خلية ، فستتوقف عن العمل بعد دمجها (خطأ # N / A في الأسطر 3-4 من المثال):

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

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

إذا كنت بحاجة إلى إنشاء محور من عدة مصادر بيانات في وقت واحد ، فسيتعين عليك إضافة "PivotTable and Chart Wizard" إلى الشريط أو لوحة الوصول السريع ، التي تحتوي على مثل هذا الخيار.

يمكنك القيام بذلك على النحو التالي: "ملف" ← "خيارات" ← "شريط أدوات الوصول السريع" ← "كافة الأوامر" ← "معالج الجدول والجداول" ← "إضافة":

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

عند النقر فوقه ، يظهر مربع حوار:

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

في النافذة التالية ، أضف جميع النطاقات التي سيتم بناء المحور على أساسها ، وقم بتسميتها:

بعد ذلك ، في مربع الحوار الأخير ، حدد مكان وضع تقرير الجدول المحوري - في ورقة حالية أو جديدة:

تقرير الجدول المحوري جاهز. في فلتر "الصفحة 1" ، يمكنك تحديد مصدر واحد فقط من مصادر البيانات ، إذا لزم الأمر:

كيفية حساب عدد تكرارات النص أ في النص ب ("تعريفة MTS SuperMTS" - تكراران للاختصار MTS)

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

لحل هذه المشكلة ، يمكنك استخدام صيغة معقدة تتكون من الوظائف التالية:

1. DLSTR (LEN) - تحسب طول النص ، الوسيطة الوحيدة هي النص. مثال: DLSTR ("الجهاز") = 6.
2. الاستبدال - استبدال نص معين في سلسلة نصية بآخر. بناء الجملة: SUBSTITUTE (نص ؛ نص قديم ؛ نص جديد). مثال: SUBSTITUTE ("سيارة" ، "تلقائي" ، "") = "جوّال".
3. UPPER - يستبدل كل الأحرف في سلسلة بأحرف كبيرة. الحجة الوحيدة هي النص. مثال: UPPER ("machine") = "CAR". نحتاج إلى هذه الوظيفة لإجراء عمليات بحث غير حساسة لحالة الأحرف. بعد كل شيء ، UPPER ("car") = UPPER ("Machine")

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

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Tariff Super") = 6

ثم اقسم هذا الاختلاف على طول السلسلة التي نبحث عنها:

6 / DLSTR ("MTS") = 2

إنه بالضبط مرتين يتم تضمين الخط "MTS" في الخط الأصلي.

يبقى كتابة هذه الخوارزمية بلغة الصيغ (دعنا نشير بكلمة "نص" إلى النص الذي نبحث فيه عن الأحداث ، وبعبارة "المطلوب" - الذي نهتم بعدد مرات حدوثه):

= (DLSTR (text) -LSTR (SUBSTITUTE (UPPER (text) ؛ UPPER (search)، ""))) / DLSTR (بحث)

في مثالنا ، تبدو الصيغة كما يلي:

= (DLSTR (A2) -LSTR (البديل (العلوي (A2) ، الأعلى ($ E $ 1) ، ""))) / DLSTR ($ E $ 1)