एक्सेल में एक डेटाबेस बनाएं

डेटाबेस (DB) का उल्लेख करते समय, सबसे पहली बात जो दिमाग में आती है, वह है SQL, Oracle, 1C, या कम से कम एक्सेस जैसे सभी प्रकार के buzzwords। बेशक, ये बहुत शक्तिशाली (और अधिकांश भाग के लिए महंगे) प्रोग्राम हैं जो बहुत सारे डेटा के साथ एक बड़ी और जटिल कंपनी के काम को स्वचालित कर सकते हैं। परेशानी यह है कि कभी-कभी ऐसी शक्ति की आवश्यकता नहीं होती है। आपका व्यवसाय छोटा हो सकता है और अपेक्षाकृत सरल व्यावसायिक प्रक्रियाओं के साथ हो सकता है, लेकिन आप इसे स्वचालित भी करना चाहते हैं। और यह छोटी कंपनियों के लिए है कि यह अक्सर अस्तित्व की बात होती है।

आरंभ करने के लिए, आइए टीओआर तैयार करें। ज्यादातर मामलों में, लेखांकन के लिए एक डेटाबेस, उदाहरण के लिए, क्लासिक बिक्री में सक्षम होना चाहिए:

• रखना तालिकाओं में माल (कीमत), पूर्ण किए गए लेनदेन और ग्राहकों की जानकारी और इन तालिकाओं को एक दूसरे से जोड़ते हैं
• आराम से रहें इनपुट फॉर्म डेटा (ड्रॉप-डाउन सूचियों के साथ, आदि)
• स्वचालित रूप से कुछ डेटा भरें मुद्रित प्रपत्र (भुगतान, बिल, आदि)
  
• आवश्यक जारी करें रिपोर्टों प्रबंधक के दृष्टिकोण से संपूर्ण व्यावसायिक प्रक्रिया को नियंत्रित करने के लिए

Microsoft Excel यह सब थोड़े से प्रयास से संभाल सकता है। आइए इसे लागू करने का प्रयास करें।

चरण 1. तालिका के रूप में प्रारंभिक डेटा

हम उत्पादों, बिक्री और ग्राहकों के बारे में तीन तालिकाओं में जानकारी संग्रहीत करेंगे (एक ही शीट पर या अलग-अलग पर - इससे कोई फर्क नहीं पड़ता)। उन्हें ऑटो-साइज़ के साथ "स्मार्ट टेबल" में बदलना मौलिक रूप से महत्वपूर्ण है, ताकि भविष्य में इसके बारे में न सोचें। यह आदेश के साथ किया जाता है तालिका के रूप में प्रारूपित करें टैब होम (होम - तालिका के रूप में प्रारूपित करें). फिर दिखाई देने वाले टैब पर निर्माता (डिज़ाइन) फ़ील्ड में टेबल को वर्णनात्मक नाम दें तालिका नाम बाद में उपयोग के लिए:

कुल मिलाकर, हमें तीन "स्मार्ट टेबल" मिलने चाहिए:

कृपया ध्यान दें कि तालिकाओं में अतिरिक्त स्पष्टीकरण डेटा हो सकता है। तो, उदाहरण के लिए, हमारे मूल्य प्रत्येक उत्पाद की श्रेणी (उत्पाद समूह, पैकेजिंग, वजन, आदि) और तालिका के बारे में अतिरिक्त जानकारी शामिल है ग्राहक — उनमें से प्रत्येक का शहर और क्षेत्र (पता, टिन, बैंक विवरण, आदि)।

तालिका बिक्री हमारे द्वारा बाद में इसमें पूर्ण लेनदेन दर्ज करने के लिए उपयोग किया जाएगा।

चरण 2. डेटा एंट्री फॉर्म बनाएं

बेशक, आप बिक्री डेटा को सीधे हरी तालिका में दर्ज कर सकते हैं बिक्री, लेकिन यह हमेशा सुविधाजनक नहीं होता है और "मानव कारक" के कारण त्रुटियों और टाइपो की उपस्थिति पर जोर देता है। इसलिए, कुछ इस तरह की एक अलग शीट पर डेटा दर्ज करने के लिए एक विशेष फॉर्म बनाना बेहतर होगा:

सेल B3 में, अद्यतन वर्तमान दिनांक-समय प्राप्त करने के लिए, फ़ंक्शन का उपयोग करें टीडीएटीए (अब). अगर समय की जरूरत नहीं है, तो इसके बजाय टीडीएटीए समारोह लागू किया जा सकता है टुडे (आज).

सेल B11 में, स्मार्ट टेबल के तीसरे कॉलम में चयनित उत्पाद की कीमत ज्ञात करें मूल्य फ़ंक्शन का उपयोग करना VPR (VLOOKUP). अगर आपने पहले इसका सामना नहीं किया है, तो पहले यहां वीडियो पढ़ें और देखें।

सेल B7 में, हमें मूल्य सूची के उत्पादों के साथ एक ड्रॉपडाउन सूची की आवश्यकता है। इसके लिए आप कमांड का इस्तेमाल कर सकते हैं डेटा - डेटा सत्यापन (आंकड़ा मान्यीकरण), एक बाधा के रूप में निर्दिष्ट करें सूची (सूची) और फिर क्षेत्र में प्रवेश करें स्रोत (स्रोत) कॉलम का लिंक नाम हमारी स्मार्ट टेबल से मूल्य :

इसी तरह, क्लाइंट के साथ एक ड्रॉप-डाउन सूची बनाई जाती है, लेकिन स्रोत संकरा होगा:

= अप्रत्यक्ष ("ग्राहक [ग्राहक]")

समारोह अप्रत्यक्ष (अप्रत्यक्ष) इस मामले में आवश्यकता है, क्योंकि एक्सेल, दुर्भाग्य से, स्रोत फ़ील्ड में स्मार्ट टेबल के सीधे लिंक को नहीं समझता है। लेकिन एक ही लिंक "लिपटे" एक समारोह में अप्रत्यक्ष उसी समय, यह एक धमाके के साथ काम करता है (इसके बारे में अधिक सामग्री के साथ ड्रॉप-डाउन सूचियां बनाने के बारे में लेख में था)।

चरण 3. एक बिक्री प्रविष्टि मैक्रो जोड़ना

फॉर्म भरने के बाद, आपको इसमें दर्ज किए गए डेटा को तालिका के अंत में जोड़ना होगा बिक्री. सरल लिंक का उपयोग करके, हम फ़ॉर्म के ठीक नीचे जोड़ने के लिए एक लाइन बनाएंगे:

वे। सेल A20 में =B3 का लिंक होगा, सेल B20 में =B7 का लिंक होगा, इत्यादि।

अब एक 2-पंक्ति का प्राथमिक मैक्रो जोड़ते हैं जो उत्पन्न स्ट्रिंग की प्रतिलिपि बनाता है और उसे बिक्री तालिका में जोड़ता है। ऐसा करने के लिए, संयोजन दबाएं ऑल्ट + F11 या बटन Visual Basic के टैब विकासक (डेवलपर). यदि यह टैब दिखाई नहीं दे रहा है, तो इसे पहले सेटिंग में सक्षम करें फ़ाइल - विकल्प - रिबन सेटअप (फ़ाइल - विकल्प - रिबन को अनुकूलित करें). खुलने वाली Visual Basic संपादक विंडो में, मेनू के माध्यम से एक नया खाली मॉड्यूल डालें सम्मिलित करें - मॉड्यूल और वहां हमारा मैक्रो कोड दर्ज करें:

उप Add_Sell() वर्कशीट्स ("इनपुट फॉर्म")। रेंज ("ए 20: ई 20")। कॉपी 'डेटा लाइन को फॉर्म एन = वर्कशीट्स ("सेल्स") से कॉपी करें। रेंज ("ए 100000")। एंड (एक्सएलअप) . पंक्ति 'तालिका में अंतिम पंक्ति की संख्या निर्धारित करती है। सेल्स वर्कशीट्स ("सेल्स")। सेल (एन + 1, 1)। पेस्ट स्पेशल पेस्ट: = xlPasteValues ​​'अगले खाली लाइन वर्कशीट्स ("इनपुट फॉर्म") में पेस्ट करें। रेंज ("बी 5, बी 7, बी 9")। ClearContents 'क्लियर एंड सब फॉर्म  

अब हम ड्रॉपडाउन सूची का उपयोग करके बनाए गए मैक्रो को चलाने के लिए अपने फॉर्म में एक बटन जोड़ सकते हैं सम्मिलित करें टैब विकासक (डेवलपर - सम्मिलित करें - बटन):

आपके द्वारा इसे ड्रा करने के बाद, बाएँ माउस बटन को दबाए रखते हुए, एक्सेल आपसे पूछेगा कि आपको इसे किस मैक्रो को असाइन करने की आवश्यकता है - हमारे मैक्रो का चयन करें जोड़ें_बेचें. आप बटन पर राइट-क्लिक करके और कमांड का चयन करके टेक्स्ट को बदल सकते हैं टेक्स्ट बदलें.

अब, फॉर्म भरने के बाद, आप बस हमारे बटन पर क्लिक कर सकते हैं, और दर्ज किया गया डेटा स्वचालित रूप से तालिका में जुड़ जाएगा बिक्री, और फिर एक नया सौदा दर्ज करने के लिए फॉर्म को मंजूरी दे दी जाती है।

चरण 4 लिंकिंग टेबल

रिपोर्ट बनाने से पहले, आइए अपनी तालिकाओं को एक साथ जोड़ते हैं ताकि बाद में हम क्षेत्र, ग्राहक या श्रेणी के आधार पर बिक्री की शीघ्र गणना कर सकें। एक्सेल के पुराने संस्करणों में, इसके लिए कई कार्यों के उपयोग की आवश्यकता होगी। VPR (VLOOKUP) तालिका में कीमतों, श्रेणियों, ग्राहकों, शहरों आदि को प्रतिस्थापित करने के लिए बिक्री. इसके लिए हमें समय और प्रयास की आवश्यकता होती है, और साथ ही बहुत सारे एक्सेल संसाधनों को "खाती" है। एक्सेल 2013 से शुरू होकर, तालिकाओं के बीच संबंध स्थापित करके सब कुछ बहुत अधिक सरलता से लागू किया जा सकता है।

ऐसा करने के लिए, टैब पर जानकारी (तारीख) क्लिक करें संबंध (संबंधों). दिखाई देने वाली विंडो में, बटन पर क्लिक करें बनाएं (नया) और ड्रॉप-डाउन सूची से उन तालिकाओं और स्तंभों के नामों का चयन करें जिनसे वे संबंधित होने चाहिए:

एक महत्वपूर्ण बिंदु: तालिकाओं को इस क्रम में निर्दिष्ट किया जाना चाहिए, अर्थात लिंक की गई तालिका (मूल्य ) कुंजी कॉलम में शामिल नहीं होना चाहिए (नाम) डुप्लिकेट उत्पाद, जैसा कि तालिका में होता है बिक्री. दूसरे शब्दों में, संबद्ध तालिका वह होनी चाहिए जिसमें आप डेटा का उपयोग करके खोज करेंगे VPRअगर इसका इस्तेमाल किया गया था।

बेशक, तालिका इसी तरह से जुड़ी हुई है बिक्री तालिका के साथ ग्राहक सामान्य कॉलम द्वारा ग्राहक:

लिंक स्थापित करने के बाद, लिंक प्रबंधित करने के लिए विंडो बंद की जा सकती है; आपको इस प्रक्रिया को दोहराने की जरूरत नहीं है।

चरण 5. हम सारांश का उपयोग करके रिपोर्ट बनाते हैं

अब, बिक्री का विश्लेषण करने और प्रक्रिया की गतिशीलता को ट्रैक करने के लिए, उदाहरण के लिए, पिवट तालिका का उपयोग करके किसी प्रकार की रिपोर्ट बनाते हैं। सक्रिय सेल को टेबल पर सेट करें बिक्री और रिबन पर टैब चुनें सम्मिलित करें - पिवोटटेबल (सम्मिलित करें - पिवट टेबल). खुलने वाली विंडो में, एक्सेल हमसे डेटा स्रोत के बारे में पूछेगा (अर्थात तालिका बिक्री) और रिपोर्ट अपलोड करने का स्थान (अधिमानतः एक नई शीट पर):

महत्वपूर्ण बात यह है कि चेकबॉक्स को सक्षम करना आवश्यक है इस डेटा को डेटा मॉडल में जोड़ें (डेटा मॉडल में डेटा जोड़ें) विंडो के निचले भाग में ताकि एक्सेल यह समझ सके कि हम न केवल वर्तमान तालिका पर एक रिपोर्ट बनाना चाहते हैं, बल्कि सभी संबंधों का भी उपयोग करना चाहते हैं।

क्लिक करने के बाद OK खिड़की के दाहिने आधे हिस्से में एक पैनल दिखाई देगा पिवट टेबल फील्डलिंक पर कहाँ क्लिक करें सबन केवल वर्तमान को देखने के लिए, बल्कि सभी "स्मार्ट टेबल" जो एक ही बार में पुस्तक में हैं। और फिर, जैसा कि क्लासिक पिवट टेबल में होता है, आप किसी भी संबंधित टेबल से उन फ़ील्ड्स को आसानी से खींच सकते हैं जिनकी हमें आवश्यकता होती है फ़िल्टर, पंक्तियाँ, स्टोलबत्सोव or मान - और एक्सेल तुरंत शीट पर हमारी जरूरत की कोई भी रिपोर्ट तैयार करेगा:

यह न भूलें कि पिवट टेबल को समय-समय पर (जब स्रोत डेटा बदलता है) उस पर राइट-क्लिक करके और कमांड का चयन करके अद्यतन करने की आवश्यकता होती है अपडेट करें और सहेजें (ताज़ा करें), क्योंकि यह इसे स्वचालित रूप से नहीं कर सकता।

साथ ही, सारांश में किसी भी सेल का चयन करके और बटन दबाकर पिवट चार्ट (पिवट चार्ट) टैब विश्लेषण (विश्लेषण) or पैरामीटर्स (विकल्प) आप इसमें परिकलित परिणामों की शीघ्रता से कल्पना कर सकते हैं।

चरण 6. प्रिंट करने योग्य भरें

किसी भी डेटाबेस का एक अन्य विशिष्ट कार्य विभिन्न मुद्रित प्रपत्रों और प्रपत्रों (चालान, चालान, अधिनियम, आदि) को स्वचालित रूप से भरना है। मैंने ऐसा करने के तरीकों में से एक के बारे में पहले ही लिखा था। यहां हम लागू करते हैं, उदाहरण के लिए, खाता संख्या द्वारा फॉर्म भरना:

यह माना जाता है कि सेल C2 में उपयोगकर्ता एक नंबर दर्ज करेगा (तालिका में पंक्ति संख्या बिक्री, वास्तव में), और फिर हमें जो डेटा चाहिए वह पहले से ही परिचित फ़ंक्शन का उपयोग करके खींचा जाता है VPR (VLOOKUP) और विशेषताएं सूचकांक (अनुक्रमणिका).

• मूल्यों को देखने और देखने के लिए VLOOKUP फ़ंक्शन का उपयोग कैसे करें
• VLOOKUP को INDEX और MATCH फ़ंक्शंस से कैसे बदलें
• तालिका से डेटा के साथ प्रपत्रों और प्रपत्रों का स्वत: भरना
• PivotTables के साथ रिपोर्ट बनाना