إنشاء نماذج في Excel

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

محتوى

تطبيق أدوات التعبئة
- الطريقة 1: كائن مضمّن لإدخال بيانات Excel
- الطريقة 2: إنشاء نموذج مخصص
أسئلة وأجوبة

تطبيق أدوات التعبئة

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

الآن دعونا ننظر في كيفية استخدام هذين النوعين من الأدوات.

الطريقة 1: كائن مضمّن لإدخال بيانات Excel

أولاً ، دعونا نتعرف على كيفية استخدام النموذج المدمج لإدخال بيانات Excel.

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

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

بعد ذلك ، سيتم عرض الأداة التي تحتاجها على الجانب الأيمن من النافذة. انقر على زر "موافق" .

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

الآن ، لكي تتمكن الأداة من فهم ما تحتاج إليه بالضبط ، من الضروري ترتيب رأس الجدول وتدوين أي قيمة فيه. دع مصفوفة الجدول فينا تتكون من أربعة أعمدة تحمل أسماء "Product name" و "Quantity" و "Price" و "Sum" . أدخل بيانات الاسم في نطاق أفقي عشوائي من الورقة.

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

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

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

أدخل القيم التي نعتقد أنها ضرورية في الحقول المتبقية ، ثم انقر فوق الزر "إضافة" .

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

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

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

وهكذا ، نقوم بتعبئة مصفوفة الجدول بكل القيم التي نريد إدخالها فيها.

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

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

كما ترى ، حدث التغيير على الفور في منطقة الجدول.

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

يفتح مربع حوار التحذير لإعلامك بأنه سيتم حذف السطر. إذا كنت متأكدًا من أفعالك ، فانقر فوق الزر "موافق" .

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

بعد ذلك ، لجعل صفيف الجدول يبدو أكثر بصرية ، يمكنك تهيئته.

الطريقة 2: إنشاء نموذج مخصص

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

كما هو الحال في الطريقة السابقة ، أولاً وقبل كل شيء ، تحتاج إلى إنشاء رأس الجدول المستقبلي على الورقة. سيتكون من خمس خلايا تحمل الأسماء: "№ п / п" ، "Name of goods" ، "Quantity" ، "Price" ، "Amount" .

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

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

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

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

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

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

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

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

من الخيارات المفتوحة ، حدد العنصر "قائمة" .

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

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

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

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

كما ترى ، تم عرض الموضع الذي تم اختياره على الفور في الحقل "Product name" .

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

وبنفس الطريقة بالضبط ، فإننا نحدد اسم "Volum" إلى الخلية التي سندخل فيها كمية البضائع.

الخلية ذات السعر هي "السعر" .

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

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

في إطار الحفظ الذي يفتح ، في حقل "نوع الملف" ، حدد الخيار "مصنف Excel مع دعم الماكرو (.xlsm)" . ثم انقر فوق الزر "حفظ" .

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

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

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

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

الآن ، بالنسبة إلى المثال الملموس لمثالنا ، نحتاج إلى كتابة الشفرة التالية في الحقل:
Sub DataEntryForm() Dim nextRow As Long nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row With Producty If .Range("A2").Value = "" And .Range("B2").Value = "" Then nextRow = nextRow - 1 End If Producty.Range("Name").Copy .Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues .Cells(nextRow, 3).Value = Producty.Range("Volum").Value .Cells(nextRow, 4).Value = Producty.Range("Price").Value .Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value .Range("A2").Formula = "=IF(ISBLANK(B2), """", COUNTA($B$2:B2))" If nextRow > 2 Then Range("A2").Select Selection.AutoFill Destination:=Range("A2:A" & nextRow) Range("A2:A" & nextRow).Select End If .Range("Diapason").ClearContents End With End Sub
لكن هذا القانون ليس شاملاً ، أي أنه في شكل لا يتغير ومناسب لحالنا فقط. إذا كنت ترغب في تعديله وفقًا لاحتياجاتك ، فيجب تعديله وفقًا لذلك. حتى تتمكن من القيام بذلك بنفسك ، دعنا نحلل ما يتكون هذا الكود ، وما يجب استبداله ، وما لا يجب تغييره.
لذا ، السطر الأول:
Sub DataEntryForm()

"DataEntryForm" هو اسم الماكرو نفسه. يمكنك تركها كما هي ، أو يمكنك استبدالها بأخرى ، والتي تتوافق مع القواعد العامة لإنشاء أسماء وحدات الماكرو (لا توجد مسافات ، فقط حروف الأبجدية اللاتينية ، إلخ). تغيير الاسم لن يؤثر على أي شيء.
أينما تظهر كلمة "Producty" في التعليمة البرمجية ، يجب استبدالها بالاسم الذي عينته سابقًا للورقة الخاصة بك في الحقل "(Name)" في منطقة "Properties" في برنامج تحرير الماكرو. بطبيعة الحال ، يجب القيام بذلك فقط إذا قمت بتسمية الورقة بطريقة مختلفة.
الآن النظر في هذا الخط:
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

الرقم "2" في هذا السطر يعني العمود الثاني من الورقة. يوجد في هذا العمود عمود يسمى "Product Name" . على ذلك سننظر في عدد الصفوف. لذلك ، إذا كان عمود مشابه في حالتك يحتوي على ترتيب مختلف في الحساب ، فأنت بحاجة إلى إدخال الرقم المناسب. قيمة "End (xlUp) .Offset (1، 0) .Row" في أي حال ، تبقى دون تغيير.
المقبل ، والنظر في الصف
If .Range("A2").Value = "" And .Range("B2").Value = "" Then

"A2" هي إحداثيات الخلية الأولى ، حيث سيتم عرض ترقيم الخطوط. "B2" هي إحداثيات الخلية الأولى ، والتي سيتم استخدامها لإخراج البيانات ( "اسم البضائع" ). إذا كانت مختلفة ، فأدخل بياناتك بدلاً من هذه الإحداثيات.
نحن نمر الى الخط
Producty.Range("Name").Copy

В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.
В строках
.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues .Cells(nextRow, 3).Value = Producty.Range("Volum").Value .Cells(nextRow, 4).Value = Producty.Range("Price").Value .Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value
наименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.
В этих же строках, которые мы указали выше, цифры «2» , «3» , «4» , «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара» , «Количество» , «Цена» и «Сумма» . Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.
В строке производится умножение количества товара на его цену:
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value

Результат, как видим из синтаксиса записи, будет выводиться в пятый столбец листа Excel.
В этом выражении выполняется автоматическая нумерация строк:
If nextRow > 2 Then Range("A2").Select Selection.AutoFill Destination:=Range("A2:A" & nextRow) Range("A2:A" & nextRow).Select End If
Все значения «A2» означают адрес первой ячейки, где будет производиться нумерация, а координаты « A» — адрес всего столбца с нумерацией. Проверьте, где именно будет выводиться нумерация в вашей таблице и измените данные координаты в коде, если это необходимо.
В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:
.Range("Diapason").ClearContents

Не трудно догадаться, что ( «Diapason» ) означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.
Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.
После того, как вы записали код макроса в окно редактора, следует нажать на значок сохранения в виде дискеты в левой части окна. Затем можно его закрывать, щелкнув по стандартной кнопке закрытия окон в правом верхнем углу.

После этого возвращаемся на лист Excel. Теперь нам следует разместить кнопку, которая будет активировать созданный макрос. Для этого переходим во вкладку «Разработчик» . В блоке настроек «Элементы управления» на ленте кликаем по кнопке «Вставить» . Открывается перечень инструментов. В группе инструментов «Элементы управления формы» выбираем самый первый – «Кнопка» .

Затем с зажатой левой клавишей мыши обводим курсором область, где хотим разместить кнопку запуска макроса, который будет производить перенос данных из формы в таблицу.

После того, как область обведена, отпускаем клавишу мыши. Затем автоматически запускается окно назначения макроса объекту. Если в вашей книге применяется несколько макросов, то выбираем из списка название того, который мы выше создавали. У нас он называется «DataEntryForm» . Но в данном случае макрос один, поэтому просто выбираем его и жмем на кнопку «OK» внизу окна.

После этого можно переименовать кнопку, как вы захотите, просто выделив её текущее название.
В нашем случае, например, логично будет дать ей имя «Добавить» . Переименовываем и кликаем мышкой по любой свободной ячейке листа.

Итак, наша форма полностью готова. Проверим, как она работает. Вводим в её поля необходимые значения и жмем на кнопку «Добавить» .

Как видим, значения перемещены в таблицу, строке автоматически присвоен номер, сумма посчитана, поля формы очищены.

Повторно заполняем форму и жмем на кнопку «Добавить» .

Как видим, и вторая строка также добавлена в табличный массив. Это означает, что инструмент работает.

В Экселе существует два способа применения формы заполнения данными: встроенная и пользовательская. Применение встроенного варианта требует минимум усилий от пользователя. Его всегда можно запустить, добавив соответствующий значок на панель быстрого доступа. Пользовательскую форму нужно создавать самому, но если вы хорошо разбираетесь в коде VBA, то сможете сделать этот инструмент максимально гибким и подходящим под ваши нужды.

نماذج إدخال البيانات في Microsoft Excel

تطبيق أدوات التعبئة

الطريقة 1: كائن مضمّن لإدخال بيانات Excel

الطريقة 2: إنشاء نموذج مخصص