تنزيل مثال على الجدول المحوري. خارقة الحياة في Excel للإبلاغ ومعالجة البيانات

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

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

بدائل بسيطة لـ VLOOKUP و GLOOKUP إذا لم تكن القيم التي تبحث عنها في العمود الأول من الجدول: LOOKUP و INDEX + MATCH

نتيجة:

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

نص ( معنى؛ شكل)

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

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

كيفية الاحتفاظ بالبيانات في كل خلية بعد الدمج

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DLSTR ("MTS Super MTS Tariff") - DLSTR ("التعريفة الفائقة") = 6

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

6 / DLSTR ("MTS") = 2

يتم تضمين السلسلة "MTS" في الأصل مرتين.

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

= (DLSTR ( نص) -DLSTR (البديل (العلوي ( نص) ؛ العلوي ( مرغوب) ؛ ""))) / DLSTR ( مرغوب)

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

= (LEN (A2) - طويل (الاستبدال (العلوي (A2) ، الأعلى ($ E $ 1) ، ""))) / LEN ($ E $ 1)

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

1. في نافذة الورقة المفتوحة ، حدد أي خلية في نطاق البيانات.
2. انتقل إلى علامة التبويب "إدراج" وفي مجموعة "الجداول" ، قم بتوسيع قائمة الزر "جدول محوري".
3. في قائمة الأوامر ، حدد عنصر "Pivot Table" (الشكل 5.59).
4. في نافذة "إنشاء جدول محوري" (الشكل 5.60) ، في عمود "الجدول أو النطاق" ، سيتم عرض عنوان النطاق المحدد من البيانات التي تم تحليلها.







إذا كنت بحاجة إلى تغيير النطاق ، فحدد نطاقًا جديدًا على الورقة دون إغلاق نافذة "إنشاء PivotTable". يتم إدخال نطاق من الخلايا الموجود في ورقة أخرى من مصنف أو في مصنف آخر على النحو التالي: ([اسم الكتاب] اسم الورقة! نطاق).

5. في مجموعة "تحديد مكان وضع تقرير PivotTable" ، حدد:
   • "إلى ورقة جديدة" - لوضع تقرير PivotTable على ورقة إضافية في الكتاب.
   • "إلى ورقة موجودة" - لوضع تقرير الجدول المحوري في نفس الورقة مثل البيانات المصدر.

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

6. أغلق النافذة بالزر موافق.
7. سيظهر جدول محوري فارغ على الورقة (جديد أو موجود) في شكل مخطط تخطيطي ، وسيتم فتح منطقة "قائمة حقول الجدول المحوري" على يمين الورقة لإنشاء تقرير (الشكل 5.61). يمكن إدخاله في جدول محوريحقول جديدة ، وإنشاء تخطيط وتخصيص التقرير.




8. تحقق في نافذة "قائمة الحقول" من أسماء الأعمدة لإنشاء جدول محوري.

لإخفاء نافذة "قائمة الحقول" ، انقر فوق أي خلية حرة في ورقة العمل. بدلاً من ذلك ، في علامة التبويب خيارات ، في المجموعة إظهار / إخفاء ، قم بإيقاف تشغيل قائمة الحقول.

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

يحتوي كل سطر على معلومات كاملة حول معاملة قرض واحدة:

• اسم المقترض
• رقم الصفقة
• تاريخ الإصدار وتاريخ سداد القرض
• نوع القرض
• مبلغ الائتمان

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

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

يمكن الإجابة على جميع الأسئلة بسهولة أكبر مما تعتقد. ثلاث دقائق ، حوالي ثلاث. مع واحدة من أكثر الأدوات المدهشة مايكروسوفت اكسل - الجداول المحورية.

يذهب...

حدد جدولنا بأكمله بالبيانات وانقر على القائمة إدراج - PivotTable (إدراج-PivotTable).

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

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

العمل مع التخطيط

ما تراه عند النقر فوق الزر نعميسمى تخطيط (تخطيط) الجدول المحوري:

من السهل التعامل معها - تحتاج إلى سحب أسماء الأعمدة (الحقول) من النافذة بالماوس قائمة حقول الجدول المحوري (قائمة حقول PivotTable)في المنطقة الصفوف (تسميات الصفوف) والأعمدة (تسميات الأعمدة) والفلاتر (عامل تصفية التقرير) وبيانات التخطيط (القيم). التحذير الوحيد - افعل ذلك بدقة أكبر ، لا تفوت! يذهب...

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

ونحصل على النظرة التالية:

ليس الأمر معقدًا إلى هذا الحد ، أليس كذلك؟

ملاحظة.

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

تتمثل إحدى ميزات Microsoft Excel العديدة في قدرته على أتمتة التقارير. يمكنك إنشاء جداول بيانات تفاعلية لتسهيل إدخال البيانات في ملف كتب اكسللأشخاص آخرين ، ويمكنك أيضًا أتمتة إنشاء التقارير. تتطلب كلتا هاتين الميزتين بعض المعرفة بـ Visual Basic. يتم وصف الخطوات لإكمال كلا المهمتين أدناه.

خطوات

قم بإنشاء جدول بيانات تفاعلي

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

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

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

   اضغط على مفتاحي Alt و F11 في نفس الوقت.يفتح محرر Microsoft Visual Basic.

   في الزاوية اليسرى العلوية من لوحة "Project-VBA Project" ، انقر نقرًا مزدوجًا فوق "ThisWorkbook".سيتم فتح نافذة بها شفرة المصدر في القسم الرئيسي للمحرر.

   من قائمة "إدراج" ، حدد الإجراء.يفتح مربع الحوار "إضافة إجراء".

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

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

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

   • يبدو السطر الأول من التعليمات البرمجية كما يلي: "Range (" cellname "). حدد ، حيث" cellname "هو الخلية التي سيتم تخزين محتويات حقل الإدخال فيها. يجب أن تكون هذه هي الخلية الموجودة على يمين التسمية ؛ إذا كانت التسمية النصية موجودة في الخلية A2 ، فيجب عليك وضع حقل الإدخال في الخلية B2 (النطاق ("B2"). حدد). قم بإحاطة اسم الخلية بعلامات اقتباس ، ولكن لا تقم بإحاطة سطر التعليمات البرمجية بعلامات اقتباس.
   • يبدو السطر الثاني من التعليمات البرمجية على النحو التالي: "ActiveCell.Value = InputBox (" InputPrompt ")" ، حيث "InputPrompt" هو النص الذي سيطلب من المستخدم إدخال نوع البيانات. على سبيل المثال ، إذا كانت خلية إدخال البيانات مخصصة لحساب تكلفة الغداء ، فاستبدل "InputPrompt" بعبارة "أدخل التكلفة الإجمالية للغداء ، بما في ذلك النصائح". (قم بإحاطة نص المطالبة بعلامات اقتباس ، ولكن يجب عليك إحاطة الأمر بأكمله بعلامات اقتباس.)

3. اكتب الكود لكل حقل محسوب.يمكنك استخدام السطرين أعلاه ، ولكن هذه المرة ActiveCell.Value هي نتيجة عملية حسابية أو دالة رقمية مثل SUM بدلاً من وظيفة InputBox المستخدمة أعلاه لعرض تلميح الأداة.

   أضف سطرًا من التعليمات البرمجية لحفظ الجدول التفاعلي.تنسيقه هو: "ActiveWorkbook.SaveAs Filename: =" Filename.xls "،" حيث يكون "Filename" هو اسم جدول البيانات التفاعلي. (اترك علامات الاقتباس حول "Filename.xls" ، لكنك لست بحاجة إلى اقتباس السلسلة بأكملها.)

   • إذا كان لديك برنامج Excel 2007 أو إصدار أحدث ، فيمكنك استبدال ".xls" بـ ".xlsx" ، ولكن بعد ذلك لن يتمكن بعض مستخدمي جدول البيانات التفاعلي الذين تم تثبيت Excel 2003 أو إصدار سابق لهم من استخدام جدول البيانات بدون مكون إضافي خاص للقراءة.

4. اضغط على مفتاحي Alt و Q في نفس الوقت.يتم إغلاق نافذة محرر Visual Basic.

   اضغط على مفتاحي Alt و F8 في نفس الوقت.يفتح مربع الحوار "وحدات الماكرو".

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

   انقر فوق الزر "خيارات".سيُطلب منك إدخال حرف لاستخدامه مع مفتاح Ctrl كاختصار لوحة مفاتيح. حدد حرفًا لم يتم استخدامه كمفتاح تشغيل سريع ، مثل "z" لـ "سجل".

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

أتمتة إنشاء التقارير

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

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

   • افتح الجدول في وضع القراءة فقط.
   • قم بتحديث البيانات وحفظ التقرير ، في هذا المثال كملف PDF بطابع زمني. إذا كان المستند الخاص بك سيكون بتنسيق مختلف ، فقم بتغيير الامتداد في مثال ".pdf" إلى الامتداد الذي تحتاجه.
   • أغلق جدول البيانات دون حفظه ، ثم أغلق Excel.
   • استخدم ".xlsx" بدلاً من ".xls" كملحق للجدول إذا تم حفظه في Excel 2007 أو التنسيقات التي تستند إلى XML لاحقًا.

2. اكتب ملف .bat يقوم بتشغيل برنامج نصي في Visual Basic.يجب أن يتم ذلك حتى يبدأ البرنامج النصي المرئي تلقائيًا. بدون ملف bat ، يجب تشغيل البرنامج النصي VB يدويًا.

   • يجب أن يكون البرنامج النصي الخاص بك بهذا التنسيق ، واستبدل المجلد واسم الملف المحددين باسمك ولا تقم بتضمين الأقواس:

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

   • تحقق من وجود الملف الناتج. إذا كان تنسيق الملف المستلم ليس PDF ، في المثال ، استبدل ".pdf" بالتنسيق المطلوب.
   • إذا كان الملف / التقرير الناتج موجودًا ، أرسله بالبريد الإلكتروني إلى الأشخاص الذين يحتاجون إلى التقرير.
   • إذا كان الملف / التقرير الناتج غير موجود في الموقع المحدد ، فيجب أن يكون لديك إجراء يرسل لك رسالة تفيد بفشل إنشاء الملف. [آخر (sendemail -f [بريد إلكتروني محمي]-t [بريد إلكتروني محمي]-u التقرير لم يتم تشغيله - الملف m٪ rreport٪ غير موجود في \\ pdfloc \ -s الخادم الخاص بك: port -xu username -xp password)]
   نصيحة
   • من الأفضل تشغيل المهام المجدولة من الخادم ، عادةً كحساب نظام مع حقوق المسؤول. الإزعاج الوحيد لتشغيل المهام نيابة عن حساب النظام هو عدم وجود واجهة مستخدم ؛ ومع ذلك ، عادةً ما يتم تشغيل المهام التلقائية في الخلفية دون أي إخطار للمستخدم.