جدول محوري يحتوي على نص في القيم

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

دعنا نحاول الالتفاف حول هذا القيد والتوصل إلى "عكازين" في موقف مشابه.

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

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

الطريقة الأولى. أسهل طريقة لاستخدام Power Query

Power Query هي أداة قوية للغاية لتحميل البيانات وتحويلها في Excel. تم تضمين هذه الوظيفة الإضافية في Excel افتراضيًا منذ عام 2016. إذا كان لديك برنامج Excel 2010 أو 2013 ، فيمكنك تنزيله وتثبيته بشكل منفصل (مجاني تمامًا).

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

إذا لم يكن من الممكن استخدام Power Query ، فيمكنك حينئذٍ اتباع طرق أخرى - من خلال جدول محوري أو صيغ. 

الطريقة 2. الملخص المساعد

دعنا نضيف عمودًا آخر إلى جدولنا الأصلي ، حيث باستخدام صيغة بسيطة نحسب عدد كل صف في الجدول:

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

الآن ، بطريقة قياسية ، سنقوم ببناء جدول محوري من النوع المطلوب بناءً على بياناتنا ، ولكن في حقل القيمة ، سنقوم بإسقاط الحقل رقم السطر بدلا مما نريد حاوية:

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

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

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

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

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

ربما تكون النقطة الوحيدة غير الواضحة هنا هي الكلمة المزدوجة وعاء في الصيغة. هذا الشكل الغريب من الكتابة:

لوازم[[وعاء]:[وعاء]]

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

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

الطريقة الثالثة. الصيغ

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

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

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

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

• كيفية إنشاء تقرير باستخدام جدول محوري
• كيفية إعداد الحسابات في الجداول المحورية
• العد الانتقائي باستخدام SUMIFS و COUNTIFS وما إلى ذلك.