إلحاق عدة أوراق في Excel بـ LuckyTemplates

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

سيتم استخدام ملف بيانات Excel هذا في هذا البرنامج التعليمي.

يحتوي الملف على 60 ورقة بيانات بنفس التخطيط.

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

جدول المحتويات

• استيراد أوراق Excel إلى LuckyTemplates
• إخراج شريط الأمان من Excel إلى LuckyTemplates
• إزالة الصفوف الفارغة
• تكرار استعلام
• تعيين أنواع البيانات للأعمدة
• إضافة عمود شريط الأمان من Excel إلى LuckyTemplates
• إنشاء عمود مخصص لاستعلام السندات
• خاتمة

استيراد أوراق Excel إلى LuckyTemplates

أول شيء يجب فعله هو فتح محرر Power Query وإنشاء معلمة لتخزين موقع الملف باستخدام خيار إدارة المعلمات .

داخل مربع Manage Parameters ، انقر فوق New وأدخل FileLocation كاسم معلمة. بعد ذلك ، قم بتعيين نوع البيانات إلى نص والقيم المقترحة على أي قيمة . انسخ مسار ملف ملف Excel والصقه في حقول القيمة الحالية.

بعد الضغط على "موافق" ، انقر فوق "مصدر جديد" وحدد Excel Workbook .

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

حدد الورقة الأولى واضغط على موافق. بعد ذلك ، سترى الجدول داخل محرر Power Query.

الشيء التالي الذي يجب فعله هو تغيير مسار الملف ذي الترميز الثابت إلى معلمة الملف. افتح نافذة Advanced Editor وقم بتغيير مسار الملف في التعليمات البرمجية المصدر إلى FileLocation .

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

إخراج شريط الأمان من Excel إلى LuckyTemplates

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

يمكن أن تساعدك واجهة المستخدم في استخراج قيمة المؤشر. انقر بزر الماوس الأيمن فوق الخلية وحدد Add As New Query .

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

باستخدام هذه الرموز ، يمكنك الآن استخراج القيمة. عد إلى جدول Bond واحصل على بيانات Bond. أولاً ، قم بإزالة أعلى ثمانية صفوف ثمانية. انقر فوق Remove Rows وحدد Remove Top Rows .

بعد ذلك ، أدخل 8 في حقل Number Of Rows واضغط على OK.

ثم انقر فوق استخدام الصف الأول كرؤوس لتعيين الرؤوس.

بمجرد الانتهاء من ذلك ، ستبدو طاولة Bond الآن على هذا النحو.

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

إزالة الصفوف الفارغة

لإزالة الصفوف الفارغة ، انقر فوق "إزالة الصفوف" وحدد "إزالة الصفوف الفارغة" .

يولد هذا التحول بناء الجملة هذا:

يقوم Record.FieldValues ​​بإحضار جميع القيم من الصف الحالي في الجدول كقائمة. يزيل List.RemoveMatchingItems كافة القيم الموجودة في القائمة الأولى التي لها تطابق في القائمة الثانية. تحتوي القائمة الثانية فقط على سلسلة نصية فارغة أو خالية. هذه هي القيم التي سيتم استبعادها من القائمة الأولى.

إذا تمت إزالة جميع السلاسل النصية الفارغة والقيم الفارغة من القائمة بقيم حقل السجل ، فيجب أن تكون القائمة فارغة وسيتم تقييم List.IsEmpty إلى True. ثم ، Table.SelectRows سيحتفظ Trues.

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

بصرف النظر عن الصفوف الفارغة ، تحتاج أيضًا إلى إزالة الأعمدة الفارغة. ولكن قبل ذلك ، ألق نظرة على ما ينشئه Power Query عند إزالة عمود. حدد العمود الرابع وانقر فوق إزالة الأعمدة .

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

تقوم باستدعاء الدالة Table.RemoveColumns ثم تقوم بالإشارة إلى الخطوة السابقة وتمريرها في جزء "الخطوات المطبقة" كوسيطة أولى. يمرر التحويل أيضًا قائمة تحتوي على اسم عمود الأعمدة التي تريد إزالتها.

تكرار استعلام

الآن ، قم بتكرار الاستعلام وحدد خطوة الترويج للرؤوس في جزء الخطوات المطبقة. بعد ذلك ، انقر بزر الماوس الأيمن فوق هذه الخطوة وحدد حذف حتى النهاية .

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

بمجرد الانتهاء من ذلك ، ستبدو طاولة Bond هكذا.

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

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

لقد قمت الآن بإنشاء قائمة بأسماء الأعمدة التي تريد الاحتفاظ بها. إعادة تسمية الاستعلام "أسماء الأعمدة" .

ثم ارجع إلى استعلام Bond. نظرًا لأنك قمت بإنشاء استعلام يحتوي على جميع الأعمدة التي تريد الاحتفاظ بها ، فأنت بحاجة إلى سرد كافة الأعمدة التي تريد استبعادها في دالة Table.RemoveColumns .

غيّر {Column4} بنفس الصيغة التي تم نسخها من خطوات "إزالة الصفوف الفارغة". غيّر أيضًا {""، null} إلى ColumnNames .

بعد ذلك ، تحتاج إلى تمرير قائمة بأسماء الأعمدة الفعلية من جدول Bond. قم بتغيير Record.FieldValues ​​(_) إلى Table.ColumnNames () . إدخال # "إزالة الصفوف الفارغة" داخل الأقواس لتمرير وسيطة مرجعية للجدول.

تعيين أنواع البيانات للأعمدة

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

بالنسبة لأعمدة PX_LAST و YLD_YTM_MID ، حدد نوع بيانات الرقم العشري.

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

إضافة عمود شريط الأمان من Excel إلى LuckyTemplates

في خطوة المصدر ، يمكنك رؤية جميع البيانات الموجودة داخل ملف Excel. بدلاً من إنشاء استعلام منفصل لكل ورقة ، يمكنك استخدام استعلام Bond وتحويل الجداول المتداخلة في عمود البيانات.

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

تحتاج إلى إنشاء منطق باستخدام عبارة للحصول على شريط الأمان. انتقل إلى استعلام Column2 وانسخ عنوان شريط الأمان من شريط الصيغة.

بعد ذلك ، ارجع إلى استعلام Bond وأضف عمودًا مخصصًا.

قم بتسمية العمود Security Ticker واكتب رمز M التالي.

تحتوي الصيغة على عبارة IF تقول أنه إذا تم العثور على كلمة Security في Column1 ، فستوفر قيمة الخلية من Column2 المجاورة لها. خلاف ذلك ، سيتم توفير قيمة خالية.

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

انقر فوق زر القائمة المنسدلة لعمود Security Ticker وقم بإلغاء تحديد null لإزالة جميع القيم الخالية في العمود.

بعد ذلك ، ستترك جميع معلومات السندات من جميع الأوراق. كل ما عليك فعله هو تكرار التحويلات التي تم إجراؤها في استعلام Bond (2) وتطبيقها على الجداول المتداخلة في عمود البيانات.

إنشاء عمود مخصص لاستعلام السندات

انتقل إلى استعلام Bond (2) ، وافتح نافذة Advanced Editor ، وانسخ الكود التالي:

بعد ذلك ، ارجع إلى استعلام Bond وأنشئ عمودًا مخصصًا آخر. نظرًا لأنك تحتاج إلى تطبيق تحويلات متعددة عبر خطوات متعددة ، يجب عليك استخدام بيان let . لذا ، أدخل اسمحوا والصق الكود من المحرر المتقدم.

بعد ذلك ، قم بتغيير Bond_Sheet إلى Data لتحويل الجدول في عمود البيانات.

بعد الضغط على "موافق" ، ستتم إضافة عمود جديد في الجدول.

إذا قمت بالنقر فوق المسافة البيضاء داخل أي خلية من هذا العمود ، فسترى البيانات من استعلام Bond (2).

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

قم بتوسيع العمود المخصص وقم بإلغاء تحديد المربع استخدام الاسم الأصلي كبادئة.

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

بعد هذا التحويل ، سيحدث خطأ في عمود البيانات.

لذا افحص الخطأ عن طريق النقر فوق عمود البيانات وتحديد الاحتفاظ بالأخطاء في شريط الاحتفاظ بالصفوف.

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

لحل المشكلات ، قم أولاً بإزالة خطوة "الاحتفاظ بالأخطاء". انقر فوق عمود البيانات وحدد إزالة الأخطاء من شريط إزالة الصفوف.

بعد ذلك ، قم بتعيين خيار تشكيل العمود مرة أخرى إلى أعلى 1000 صف. وهذا كل شيء!

خاتمة

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

ميليسا