فهرست
هنگام ذکر پایگاه های داده (DB)، اولین چیزی که به ذهن می رسد، البته، انواع کلمات کلیدی مانند SQL، Oracle، 1C یا حداقل Access است. البته، اینها برنامه های بسیار قدرتمند (و در بیشتر موارد گران قیمت) هستند که می توانند کار یک شرکت بزرگ و پیچیده را با داده های زیاد خودکار کنند. مشکل اینجاست که گاهی اوقات چنین قدرتی به سادگی مورد نیاز نیست. کسب و کار شما ممکن است کوچک و با فرآیندهای تجاری نسبتا ساده باشد، اما شما همچنین می خواهید آن را خودکار کنید. و این برای شرکت های کوچک است که اغلب موضوع بقا است.
برای شروع، بیایید TOR را فرموله کنیم. در بیشتر موارد، یک پایگاه داده برای حسابداری، به عنوان مثال، فروش کلاسیک باید بتواند:
- نگه داشتن در جداول اطلاعات کالاها (قیمت)، معاملات انجام شده و مشتریان و پیوند این جداول به یکدیگر
- راحت باشند فرم های ورودی داده ها (با لیست های کشویی و غیره)
- به طور خودکار برخی از داده ها را پر کنید فرم های چاپی (پرداخت ها، قبوض و غیره)
- لازم را صادر کند گزارش برای کنترل کل فرآیند کسب و کار از دیدگاه مدیر
مایکروسافت اکسل می تواند با کمی تلاش از پس همه اینها بربیاید. بیایید سعی کنیم این را پیاده سازی کنیم.
مرحله 1. داده های اولیه در قالب جداول
ما اطلاعات مربوط به محصولات، فروش و مشتریان را در سه جدول (در یک برگه یا در برگه های مختلف - مهم نیست) ذخیره می کنیم. بسیار مهم است که آنها را به "جدول های هوشمند" با اندازه خودکار تبدیل کنید تا در آینده به آن فکر نکنید. این کار با دستور انجام می شود قالب بندی به صورت جدول برگ صفحه اصلی (صفحه اصلی - قالب بندی به عنوان جدول). در برگه ای که ظاهر می شود سازنده (طرح) نام های توصیفی را در فیلد به جداول بدهید نام جدول برای استفاده بعدی:
در مجموع، ما باید سه "جدول هوشمند" داشته باشیم:
لطفاً توجه داشته باشید که جداول ممکن است حاوی داده‌های روشن‌کننده بیشتری باشند. بنابراین، برای مثال، ما قیمتحاوی اطلاعات اضافی در مورد دسته (گروه محصول، بسته بندی، وزن و غیره) هر محصول و جدول است مشتری - شهر و منطقه (آدرس، TIN، مشخصات بانکی و غیره) هر یک از آنها.
جدول حراجی بعداً توسط ما برای وارد کردن تراکنش های تکمیل شده در آن استفاده خواهد شد.
مرحله 2. یک فرم ورود داده ایجاد کنید
البته می توانید اطلاعات فروش را مستقیماً در جدول سبز وارد کنید حراجی، اما این همیشه راحت نیست و مستلزم بروز خطاها و اشتباهات تایپی ناشی از "عامل انسانی" است. بنابراین، بهتر است یک فرم مخصوص برای وارد کردن داده ها در یک برگه جداگانه مانند این ایجاد کنید:
در سلول B3، برای دریافت تاریخ و زمان فعلی به روز شده، از تابع استفاده کنید TDATA (اکنون). اگر زمان لازم نیست، در عوض TDATA تابع را می توان اعمال کرد امروز (امروز).
در سلول B11، قیمت محصول انتخابی را در ستون سوم جدول هوشمند بیابید قیمت با استفاده از تابع VPR (VLOOKUP). اگر قبلاً با آن برخورد نکرده اید، ابتدا ویدیو را اینجا بخوانید و تماشا کنید.
در سلول B7، ما به یک لیست کشویی با محصولات از لیست قیمت نیاز داریم. برای این کار می توانید از دستور استفاده کنید داده ها - اعتبارسنجی داده ها (اعتبار سنجی داده ها)، به عنوان یک محدودیت مشخص کنید فهرست (فهرست) و سپس وارد فیلد شوید منبع (منبع) پیوند به ستون نام از میز هوشمند ما قیمت:
به طور مشابه، یک لیست کشویی با مشتریان ایجاد می شود، اما منبع محدودتر خواهد بود:
=INDIRECT("مشتریان [مشتری]")
عملکرد غیر مستقیم (غیر مستقیم) در این مورد مورد نیاز است، زیرا اکسل، متأسفانه، پیوندهای مستقیم به جداول هوشمند را در قسمت Source درک نمی کند. اما همان پیوند در یک تابع "پیچیده شده" است غیر مستقیم در همان زمان، آن را با صدای بلند کار می کند (اطلاعات بیشتر در مورد این در مقاله در مورد ایجاد لیست های کشویی با محتوا بود).
مرحله 3. افزودن یک کلان ورود به فروش
پس از پر کردن فرم، باید اطلاعات وارد شده به آن را به انتهای جدول اضافه کنید حراجی. با استفاده از پیوندهای ساده، خطی را درست در زیر فرم اضافه می کنیم:
آن ها سلول A20 پیوندی به =B3، سلول B20 پیوندی به =B7 و غیره خواهد داشت.
حالا یک ماکرو ابتدایی 2 خطی اضافه می کنیم که رشته تولید شده را کپی کرده و به جدول فروش اضافه می کند. برای انجام این کار، ترکیب را فشار دهید Alt + F11 یا دکمه ویژوال بیسیک برگ توسعه دهنده (توسعه دهنده). اگر این تب قابل مشاهده نیست، ابتدا آن را در تنظیمات فعال کنید فایل - گزینه ها - راه اندازی نوار (فایل - گزینه ها - سفارشی کردن نوار). در پنجره ویرایشگر ویژوال بیسیک که باز می شود، یک ماژول خالی جدید را از طریق منو وارد کنید درج - ماژول و کد ماکرو ما را در آنجا وارد کنید:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy the data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . ردیف 'تعداد آخرین ردیف جدول را تعیین می کند. کاربرگ های فروش ("فروش"). سلول ها (n + 1، 1). PasteSpecial Paste:=xlPasteValues 'در خط خالی بعدی Worksheets ("Input Form"). Range("B5,B7,B9") قرار دهید. ClearContents 'فرم فرعی پایان پاک اکنون می توانیم دکمه ای به فرم خود اضافه کنیم تا ماکرو ایجاد شده را با استفاده از لیست کشویی اجرا کنیم قرار دادن برگ توسعه دهنده (توسعه دهنده - درج - دکمه):
پس از کشیدن آن، با نگه داشتن دکمه سمت چپ ماوس، اکسل از شما می‌پرسد که کدام ماکرو را باید به آن اختصاص دهید - ماکرو ما را انتخاب کنید. افزودن_فروش. می توانید با کلیک راست روی آن و انتخاب دستور، متن روی دکمه را تغییر دهید تغییر متن.
اکنون پس از پر کردن فرم، به سادگی می توانید بر روی دکمه ما کلیک کنید و داده های وارد شده به طور خودکار به جدول اضافه می شوند. حراجی، و سپس فرم برای ورود به معامله جدید پاک می شود.
مرحله 4 پیوند جداول
قبل از ساختن گزارش، بیایید جداول خود را به هم پیوند دهیم تا بعداً بتوانیم به سرعت فروش را بر اساس منطقه، مشتری یا دسته محاسبه کنیم. در نسخه‌های قدیمی‌تر اکسل، این کار به استفاده از چندین توابع نیاز دارد. VPR (VLOOKUP) برای جایگزینی قیمت ها، دسته ها، مشتریان، شهرها و غیره در جدول حراجی. این به زمان و تلاش ما نیاز دارد و همچنین منابع اکسل زیادی را "می خورد". با شروع اکسل 2013، همه چیز را می توان بسیار ساده تر با تنظیم روابط بین جداول پیاده سازی کرد.
برای انجام این کار، در برگه داده ها (تاریخ) کلیک روابط (روابط). در پنجره ای که ظاهر می شود، روی دکمه کلیک کنید ساختن (جدید) و از لیست های کشویی جدول ها و نام ستون هایی را که باید با آنها مرتبط باشند انتخاب کنید:
یک نکته مهم: جداول باید به این ترتیب مشخص شوند، یعنی جدول پیوندی (قیمت) نباید در ستون کلید (نام) محصولات تکراری، همانطور که در جدول اتفاق می افتد حراجی. به عبارت دیگر، جدول مرتبط باید جدولی باشد که با استفاده از آن داده ها را جستجو کنید VPRاگر استفاده می شد
البته میز به روشی مشابه به هم وصل شده است حراجی با میز مشتری توسط ستون مشترک مشتری:
پس از تنظیم پیوندها، پنجره مدیریت پیوندها می تواند بسته شود. شما مجبور نیستید این روش را تکرار کنید.
مرحله 5. ما گزارش ها را با استفاده از خلاصه می سازیم
حال، برای تجزیه و تحلیل فروش و ردیابی پویایی فرآیند، اجازه دهید، برای مثال، نوعی گزارش با استفاده از جدول محوری ایجاد کنیم. سلول فعال را روی جدول قرار دهید حراجی و تب روی روبان را انتخاب کنید درج - PivotTable (درج - جدول محوری). در پنجره ای که باز می شود، اکسل از ما در مورد منبع داده (یعنی جدول) سوال می کند حراجی) و مکانی برای بارگذاری گزارش (ترجیحاً در یک برگه جدید):
نکته حیاتی این است که باید چک باکس را فعال کنید این داده ها را به مدل داده اضافه کنید (افزودن داده به مدل داده) در پایین پنجره به طوری که اکسل بفهمد که ما می خواهیم گزارشی را نه تنها روی جدول فعلی بسازیم، بلکه از همه روابط نیز استفاده کنیم.
پس از کلیک روی OK یک پانل در نیمه سمت راست پنجره ظاهر می شود فیلدهای جدول محوریکجا روی لینک کلیک کنید معرفیبرای دیدن نه تنها جدول فعلی، بلکه همه "جدول های هوشمند" که در کتاب هستند به طور همزمان. و سپس، مانند جدول محوری کلاسیک، می‌توانید به سادگی فیلدهای مورد نیاز خود را از هر جدول مرتبط به آن منطقه بکشید. فیلتر, ردیف, استولبتسف or ارزش‌ها – و اکسل فوراً هر گزارشی را که نیاز داریم در برگه ایجاد می کند:
فراموش نکنید که جدول محوری باید به صورت دوره ای (هنگامی که داده های منبع تغییر می کند) با کلیک راست روی آن و انتخاب دستور به روز شود. به روز رسانی و ذخیره (تازه کردن)، زیرا نمی تواند آن را به طور خودکار انجام دهد.
همچنین با انتخاب هر سلول در خلاصه و فشردن دکمه نمودار محوری (نمودار محوری) برگ تحلیل و بررسی (تحلیل و بررسی) or پارامترهای (گزینه ها) شما می توانید به سرعت نتایج محاسبه شده در آن را تجسم کنید.
مرحله 6. موارد قابل چاپ را پر کنید
یکی دیگر از وظایف معمول هر پایگاه داده پر کردن خودکار فرم ها و فرم های چاپی مختلف (فاکتورها، فاکتورها، اعمال و غیره) است. قبلاً در مورد یکی از راه های انجام این کار نوشتم. در اینجا ما به عنوان مثال، فرم را با شماره حساب پر می کنیم:
فرض بر این است که در سلول C2 کاربر یک عدد (شماره ردیف در جدول) را وارد می کند حراجی، در واقع)، و سپس داده های مورد نیاز ما با استفاده از تابع از قبل آشنا کشیده می شود VPR (VLOOKUP) و ویژگی ها شاخص (فهرست مطالب).
- نحوه استفاده از تابع VLOOKUP برای جستجو و جستجوی مقادیر
- نحوه جایگزینی VLOOKUP با توابع INDEX و MATCH
- پر کردن خودکار فرم ها و فرم ها با داده های جدول
- ایجاد گزارش با PivotTables