المحتويات
عند ذكر قواعد البيانات ، فإن أول ما يتبادر إلى الذهن ، بالطبع ، هو كل أنواع الكلمات الطنانة مثل SQL أو Oracle أو 1C أو على الأقل Access. بالطبع ، هذه برامج قوية جدًا (ومكلفة في معظمها) يمكنها أتمتة عمل شركة كبيرة ومعقدة بها الكثير من البيانات. المشكلة هي أنه في بعض الأحيان لا تكون هناك حاجة إلى مثل هذه القوة. قد يكون عملك صغيرًا وبه عمليات تجارية بسيطة نسبيًا ، ولكنك تريد أيضًا أتمتة ذلك. وبالنسبة للشركات الصغيرة ، غالبًا ما تكون هذه مسألة بقاء.
بادئ ذي بدء ، لنقم بصياغة TOR. في معظم الحالات ، يجب أن تكون قاعدة البيانات الخاصة بالمحاسبة ، على سبيل المثال ، المبيعات الكلاسيكية قادرة على:
- احتفظ في الجداول معلومات عن البضائع (السعر) والمعاملات المكتملة والعملاء وربط هذه الجداول ببعضها البعض
- يكون مرتاحا أشكال الإدخال البيانات (مع القوائم المنسدلة ، وما إلى ذلك)
- ملء بعض البيانات تلقائيًا استمارات مطبوعة (المدفوعات ، الفواتير ، إلخ.)
- إصدار ما يلزم تقارير للتحكم في العملية التجارية بأكملها من وجهة نظر المدير
يمكن لبرنامج Microsoft Excel التعامل مع كل هذا بقليل من الجهد. دعنا نحاول تنفيذ هذا.
الخطوة 1. البيانات الأولية في شكل جداول
سنقوم بتخزين المعلومات حول المنتجات والمبيعات والعملاء في ثلاثة جداول (على نفس الورقة أو على جداول مختلفة - لا يهم). من المهم بشكل أساسي تحويلها إلى "جداول ذكية" ذات حجم تلقائي ، حتى لا تفكر في الأمر في المستقبل. يتم ذلك مع الأمر تنسيق كجدول علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - تنسيق كجدول). في علامة التبويب التي تظهر بعد ذلك منشئ (التصميم) إعطاء أسماء وصفية للجداول في هذا المجال اسم الطاولة لاستخدامها لاحقًا:
في المجموع ، يجب أن نحصل على ثلاثة "جداول ذكية":
يرجى ملاحظة أن الجداول قد تحتوي على بيانات توضيحية إضافية. لذلك ، على سبيل المثال ، لدينا السعريحتوي على معلومات إضافية حول الفئة (مجموعة المنتجات والتعبئة والوزن وما إلى ذلك) لكل منتج والجدول العميل - المدينة والمنطقة (العنوان ، رقم التعريف الضريبي ، التفاصيل المصرفية ، إلخ) لكل منهما.
طاولات ومكاتب المبيعات سيتم استخدامه من قبلنا لاحقًا لإدخال المعاملات المكتملة فيه.
الخطوة الثانية. قم بإنشاء نموذج إدخال بيانات
بالطبع ، يمكنك إدخال بيانات المبيعات مباشرة في الجدول الأخضر المبيعات ، ولكن هذا ليس مناسبًا دائمًا وينطوي على ظهور أخطاء وأخطاء مطبعية بسبب "العامل البشري". لذلك ، سيكون من الأفضل عمل نموذج خاص لإدخال البيانات في ورقة منفصلة لشيء مثل هذا:
في الخلية B3 ، للحصول على تاريخ الوقت الحالي المحدث ، استخدم الوظيفة TDATA (حاليا). إذا لم تكن هناك حاجة إلى الوقت ، فعندئذٍ بدلاً من ذلك TDATA يمكن تطبيق الوظيفة TODAY (اليوم).
في الخلية B11 ، ابحث عن سعر المنتج المحدد في العمود الثالث من الجدول الذكي السعر باستخدام الوظيفة VPR (البحث). إذا لم تكن قد واجهتها من قبل ، فاقرأ أولاً وشاهد الفيديو هنا.
في الخلية B7 ، نحتاج إلى قائمة منسدلة بالمنتجات من قائمة الأسعار. لهذا يمكنك استخدام الأمر البيانات - التحقق من صحة البيانات (تأكيد صحة البيانات)، حدد كقيد قائمة (قائمة) ثم أدخل في الحقل مصدر (مصدر) ارتباط بالعمود الاسم من طاولتنا الذكية السعر:
وبالمثل ، يتم إنشاء قائمة منسدلة تتضمن العملاء ، لكن المصدر سيكون أضيق:
= غير مباشر ("العملاء [العميل]")
المسمى الوظيفي غير مباشر (غير مباشر) مطلوب ، في هذه الحالة ، لأن Excel ، للأسف ، لا يفهم الارتباطات المباشرة للجداول الذكية في حقل المصدر. لكن نفس الرابط "ملفوف" في دالة غير مباشر في الوقت نفسه ، يعمل بشكل كبير (المزيد حول هذا كان في المقالة حول إنشاء قوائم منسدلة مع المحتوى).
الخطوة 3. إضافة ماكرو إدخال المبيعات
بعد ملء النموذج ، تحتاج إلى إضافة البيانات المدخلة فيه إلى نهاية الجدول المبيعات . باستخدام روابط بسيطة ، سنشكل سطرًا لإضافته أسفل النموذج مباشرةً:
أولئك. سيكون للخلية A20 ارتباط بـ = B3 ، وسيكون للخلية B20 ارتباط بـ = B7 ، وهكذا.
الآن دعنا نضيف ماكرو أساسي مكون من سطرين ينسخ السلسلة التي تم إنشاؤها ويضيفها إلى جدول المبيعات. للقيام بذلك ، اضغط على المجموعة ALT + F11 أو زر Visual Basic علامة التبويب المطور (مطور). إذا كانت علامة التبويب هذه غير مرئية ، فقم بتمكينها أولاً في الإعدادات ملف - خيارات - إعداد الشريط (ملف - خيارات - تخصيص الشريط). في نافذة محرر Visual Basic التي تفتح ، أدخل وحدة نمطية فارغة جديدة من خلال القائمة إدراج - وحدة وأدخل رمز الماكرو الخاص بنا هناك:
Sub Add_Sell () Worksheets ("Input Form"). Range ("A20: E20"). Copy 'Copy' Copy the data line from the form n = Worksheets ("Sales"). Range ("A100000"). End (xlUp) . صف "يحدد رقم الصف الأخير في الجدول. أوراق عمل المبيعات ("المبيعات"). الخلايا (ن + 1 ، 1). لصق خاص: = لصق xlPasteValues في السطر الفارغ التالي أوراق العمل ("نموذج الإدخال"). النطاق ("B5 ، B7 ، B9"). شكل نهاية واضح للمحتويات الآن يمكننا إضافة زر إلى نموذجنا لتشغيل الماكرو الذي تم إنشاؤه باستخدام القائمة المنسدلة إدراج علامة التبويب المطور (مطور - إدراج - زر):
بعد رسمه ، مع الاستمرار في الضغط على زر الماوس الأيسر ، سيسألك Excel عن الماكرو الذي تريد تعيينه إليه - حدد الماكرو الخاص بنا أضف بيع. يمكنك تغيير النص الموجود على زر بالنقر بزر الماوس الأيمن فوقه واختيار الأمر تغيير النص.
الآن ، بعد ملء النموذج ، يمكنك ببساطة النقر فوق الزر الخاص بنا ، وستتم إضافة البيانات المدخلة تلقائيًا إلى الجدول المبيعات
، ثم يتم مسح النموذج للدخول في صفقة جديدة.
الخطوة 4 ربط الجداول
قبل إنشاء التقرير ، دعنا نربط جداولنا معًا حتى نتمكن لاحقًا من حساب المبيعات بسرعة حسب المنطقة أو العميل أو الفئة. في الإصدارات القديمة من Excel ، يتطلب ذلك استخدام العديد من الوظائف. VPR (البحث) لاستبدال الأسعار والفئات والعملاء والمدن وما إلى ذلك بالجدول المبيعات . وهذا يتطلب منا وقتًا وجهدًا ، كما أنه "يأكل" الكثير من موارد Excel. بدءًا من Excel 2013 ، يمكن تنفيذ كل شيء بشكل أكثر بساطة عن طريق إنشاء العلاقات بين الجداول.
للقيام بذلك ، في علامة التبويب البيانات (تاريخ) انقر العلاقات (علاقات). في النافذة التي تظهر ، انقر فوق الزر إنشاء (الجديد) وحدد من القوائم المنسدلة أسماء الجداول والأعمدة التي يجب أن ترتبط بها:
نقطة مهمة: يجب تحديد الجداول بهذا الترتيب ، أي جدول مرتبط (السعر) يجب ألا يحتوي في عمود المفتاح (الاسم) منتجات مكررة ، كما يحدث في الجدول المبيعات . بمعنى آخر ، يجب أن يكون الجدول المرتبط هو الجدول الذي ستبحث فيه عن البيانات باستخدام VPRإذا تم استخدامه.
بالطبع ، الجدول متصل بطريقة مماثلة المبيعات مع طاولة العميل حسب العمود المشترك التسجيل كعميل:
بعد إعداد الروابط ، يمكن إغلاق نافذة إدارة الروابط ؛ ليس عليك تكرار هذا الإجراء.
الخطوة 5. نبني التقارير باستخدام الملخص
الآن ، لتحليل المبيعات وتتبع ديناميكيات العملية ، دعنا ننشئ ، على سبيل المثال ، نوعًا من التقارير باستخدام جدول محوري. تعيين الخلية النشطة إلى الجدول المبيعات وحدد علامة التبويب على الشريط إدراج - PivotTable (إدراج - جدول محوري). في النافذة التي تفتح ، سيسألنا Excel عن مصدر البيانات (مثل الجدول المبيعات ) ومكان لتحميل التقرير (يفضل أن يكون على ورقة جديدة):
النقطة الحيوية هي أنه من الضروري تمكين مربع الاختيار أضف هذه البيانات إلى نموذج البيانات (إضافة البيانات إلى نموذج البيانات) في أسفل النافذة حتى يفهم Excel أننا نريد إنشاء تقرير ليس فقط في الجدول الحالي ، ولكن أيضًا استخدام جميع العلاقات.
بعد النقر على OK ستظهر لوحة في النصف الأيمن من النافذة حقول الجدول المحوريمكان النقر فوق الارتباط الكللمشاهدة ليس فقط الجدول الحالي ، ولكن جميع "الجداول الذكية" الموجودة في الكتاب في وقت واحد. وبعد ذلك ، كما هو الحال في الجدول المحوري الكلاسيكي ، يمكنك ببساطة سحب الحقول التي نحتاجها من أي جداول مرتبطة إلى المنطقة الفرز, الصفوف, ستولبتسوف or القيم - وسيقوم Excel على الفور بإنشاء أي تقرير نحتاجه على الورقة:
لا تنس أن الجدول المحوري يحتاج إلى التحديث بشكل دوري (عندما تتغير بيانات المصدر) عن طريق النقر بزر الماوس الأيمن عليه واختيار الأمر تحديث وحفظ (ينعش)، لأنه لا يمكنه القيام بذلك تلقائيًا.
أيضًا ، عن طريق تحديد أي خلية في الملخص والضغط على الزر مخطط بياني محوري (مخطط بياني محوري) علامة التبويب تحليل الأداء (التحليلات) or المعلمات (خيارات) يمكنك تصور النتائج المحسوبة فيه بسرعة.
الخطوة 6. املأ المواد القابلة للطباعة
مهمة نموذجية أخرى لأي قاعدة بيانات هي الملء التلقائي للنماذج والنماذج المطبوعة المختلفة (الفواتير ، الفواتير ، الأعمال ، إلخ). لقد كتبت بالفعل عن إحدى طرق القيام بذلك. هنا ننفذ ، على سبيل المثال ، تعبئة النموذج برقم الحساب:
من المفترض أن يقوم المستخدم في الخلية C2 بإدخال رقم (رقم الصف في الجدول المبيعات
، في الواقع) ، ثم يتم سحب البيانات التي نحتاجها باستخدام الوظيفة المألوفة بالفعل VPR (البحث) والميزات INDEX (فهرس).
- كيفية استخدام دالة VLOOKUP للبحث عن القيم والبحث عنها
- كيفية استبدال VLOOKUP بوظائف INDEX و MATCH
- تعبئة تلقائية للنماذج والنماذج ببيانات من الجدول
- إنشاء التقارير باستخدام جداول PivotTable