Агуулга
Өгөгдлийн санг (DB) гэж хэлэхэд хамгийн түрүүнд SQL, Oracle, 1C, эсвэл наад зах нь Access гэх мэт олон алдартай үгс санаанд орж ирдэг. Мэдээжийн хэрэг, эдгээр нь асар их өгөгдөл бүхий том, нарийн төвөгтэй компанийн ажлыг автоматжуулах боломжтой маш хүчирхэг (мөн ихэнх тохиолдолд үнэтэй) програмууд юм. Асуудал нь заримдаа ийм хүч шаардлагагүй байдаг. Таны бизнес жижиг, харьцангуй энгийн бизнесийн үйл явцтай байж болох ч та үүнийг автоматжуулахыг хүсч байна. Жижиг компаниудын хувьд энэ нь ихэвчлэн оршин тогтнох асуудал юм.
Эхлэхийн тулд TOR-г томъёолъё. Ихэнх тохиолдолд нягтлан бодох бүртгэлийн мэдээллийн сан, жишээлбэл, сонгодог борлуулалт хийх боломжтой байх ёстой:
- байлгах Хүснэгтэнд бараа (үнэ), гүйцэтгэсэн гүйлгээ, худалдан авагчдын талаарх мэдээллийг оруулж, эдгээр хүснэгтүүдийг хооронд нь холбоно
- тав тухтай байх оролтын маягтууд өгөгдөл (унадаг жагсаалт гэх мэт)
- зарим өгөгдлийг автоматаар бөглөнө хэвлэсэн маягтууд (төлбөр, тооцоо гэх мэт)
- шаардлагатайг гаргах мэдээ менежерийн байр сууринаас бизнесийн үйл явцыг бүхэлд нь хянах
Microsoft Excel энэ бүхнийг бага зэрэг хүчин чармайлтаар зохицуулж чадна. Үүнийг хэрэгжүүлэхийг хичээцгээе.
Алхам 1. Хүснэгт хэлбэрийн анхны өгөгдөл
Бид бүтээгдэхүүн, борлуулалт, үйлчлүүлэгчдийн талаархи мэдээллийг гурван хүснэгтэд (нэг хуудсан дээр эсвэл өөр хуудсан дээр - энэ нь хамаагүй) хадгалах болно. Ирээдүйд энэ талаар бодохгүйн тулд тэдгээрийг автомат хэмжээтэй "ухаалаг ширээ" болгон хувиргах нь үндсэндээ чухал юм. Үүнийг тушаалаар гүйцэтгэдэг Хүснэгт хэлбэрээр форматлах таб Нүүр хуудас (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах). Дараа нь гарч ирэх таб дээр байгуулагч (Дизайн) талбарт хүснэгтүүдийг тайлбарлах нэр өгнө Хүснэгтийн нэр дараа ашиглах:
Нийтдээ бид гурван "ухаалаг ширээ" авах ёстой.
Хүснэгтүүд нэмэлт тодруулгатай өгөгдөл агуулж болохыг анхаарна уу. Тиймээс, жишээ нь, манай ҮнэБүтээгдэхүүн бүрийн ангилал (бүтээгдэхүүний бүлэг, сав баглаа боодол, жин гэх мэт) болон хүснэгтийн талаархи нэмэлт мэдээллийг агуулсан болно Клиент — хот, бүс нутаг (хаяг, TIN, банкны дэлгэрэнгүй мэдээлэл гэх мэт) тус бүрээс.
Хүснэгт Борлуулалтын Бид үүнийг дараа нь дуусгасан гүйлгээг оруулахад ашиглах болно.
Алхам 2. Мэдээлэл оруулах маягт үүсгэ
Мэдээжийн хэрэг та борлуулалтын мэдээллийг ногоон хүснэгтэд шууд оруулж болно Борлуулалтын, гэхдээ энэ нь үргэлж тохиромжтой байдаггүй бөгөөд "хүний хүчин зүйл" -ээс шалтгаалан алдаа, үсгийн алдаа гарахад хүргэдэг. Тиймээс, ийм төрлийн тусдаа хуудсан дээр өгөгдөл оруулах тусгай маягт хийх нь дээр.
B3 нүдэнд шинэчлэгдсэн огноо цагийг авахын тулд функцийг ашиглана уу TDATA (ОДОО). Хэрэв цаг хугацаа шаардагдахгүй бол оронд нь TDATA функцийг ашиглаж болно TODAY (ӨНӨӨДӨР).
В11 нүдэнд ухаалаг хүснэгтийн гурав дахь баганаас сонгосон бүтээгдэхүүний үнийг ол Үнэ функцийг ашиглан VPR (ХАРАХ). Хэрэв та өмнө нь таарч байгаагүй бол эхлээд эндээс видеог уншаад үзээрэй.
B7 нүдэнд үнийн жагсаалтын бүтээгдэхүүнүүдтэй унждаг жагсаалт хэрэгтэй. Үүний тулд та тушаалыг ашиглаж болно Өгөгдөл - Өгөгдлийн баталгаажуулалт (Өгөгдөл - Баталгаажуулалт), хязгаарлалт гэж зааж өгнө Жагсаалт (Жагсаалт) дараа нь талбарт оруулна уу Эх сурвалж (Эх сурвалж) багана руу холбох нэр манай ухаалаг ширээнээс Үнэ:
Үүний нэгэн адил, үйлчлүүлэгчидтэй унадаг жагсаалт үүсгэгдсэн боловч эх сурвалж нь илүү нарийссан болно:
=ШУУД БУС(“Хэрэглэгчид[Үйлчлүүлэгч]”)
Чиг үүрэг ҮНЭГҮЙ (ШУУД) Харамсалтай нь Excel нь Эх сурвалж талбар дахь ухаалаг хүснэгтүүдийн шууд холбоосыг ойлгодоггүй тул энэ тохиолдолд хэрэгтэй. Гэхдээ ижил холбоос нь функцэд "боогдсон" ҮНЭГҮЙ Үүний зэрэгцээ, энэ нь тэсрэлттэй ажилладаг (энэ талаар дэлгэрэнгүй мэдээллийг контент бүхий унждаг жагсаалт үүсгэх тухай өгүүлэлд бичсэн болно).
Алхам 3. Борлуулалтын бичилтийн макро нэмэх
Маягтыг бөглөсний дараа түүнд оруулсан өгөгдлийг хүснэгтийн төгсгөлд нэмэх шаардлагатай Борлуулалтын. Энгийн холбоосыг ашиглан бид маягтын доор нэмэх мөрийг үүсгэнэ.
Тэдгээр. A20 нүд нь =B3, B20 нүд нь =B7 гэх мэт холбоостой байна.
Одоо үүсгэсэн мөрийг хуулж Борлуулалтын хүснэгтэд нэмдэг 2 мөрийн анхан шатны макро нэмье. Үүнийг хийхийн тулд хослолыг дарна уу Alt + F11 эсвэл товчлуур Visual Basic таб Developer (Хөгжүүлэгч). Хэрэв энэ таб харагдахгүй байвал эхлээд тохиргооноос идэвхжүүлнэ үү Файл - Сонголтууд - Туузны тохиргоо (Файл - Сонголтууд - Туузыг тохируулах). Нээгдэх Visual Basic редакторын цонхонд цэсээр дамжуулан шинэ хоосон модулийг оруулна уу Оруулах - Модуль тэнд манай макро кодыг оруулна уу:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Data мөрийг хуулах n = Worksheets("Sales").Range("A100000").End(xlUp) . Мөр ' Хүснэгтийн сүүлчийн эгнээний дугаарыг тодорхойлно. Борлуулалтын ажлын хуудас("Борлуулалт"). Нүднүүд(n + 1, 1). PasteSpecial Paste:=xlPasteValues'-ыг дараагийн хоосон мөрөнд буулгана Worksheets("Оролтын маягт"). Range("B5,B7,B9"). ClearContents-ийн төгсгөлийн дэд маягтыг арилгах Одоо бид унадаг жагсаалтыг ашиглан үүсгэсэн макро ажиллуулах товчийг маягтдаа нэмж болно оруулах таб Developer (Хөгжүүлэгч - Оруулах - Товч):
Үүнийг зурсны дараа хулганы зүүн товчийг дарахад Excel та ямар макро оноохыг асуух болно - манай макро сонгоно уу. Нэмэх_зарах. Та товчлуур дээр байгаа текстийг хулганы баруун товчийг дараад командыг сонгосноор өөрчлөх боломжтой Текстийг өөрчлөх.
Одоо маягтыг бөглөсний дараа та зүгээр л манай товчлуур дээр дарж, оруулсан өгөгдөл автоматаар хүснэгтэд нэмэгдэх болно. Борлуулалтын, дараа нь шинэ хэлцэл хийх маягтыг цэвэрлэнэ.
Алхам 4 Хүснэгтүүдийг холбох
Тайланг бүтээхээсээ өмнө хүснэгтүүдээ хооронд нь холбож өгье, ингэснээр дараа нь бид борлуулалтыг бүс нутаг, үйлчлүүлэгч эсвэл ангиллаар нь хурдан тооцоолох боломжтой болно. Excel-ийн хуучин хувилбаруудад энэ нь хэд хэдэн функцийг ашиглах шаардлагатай болно. VPR (ХАРАХ) үнэ, ангилал, үйлчлүүлэгчид, хот гэх мэтийг хүснэгтэд орлуулах Борлуулалтын. Энэ нь биднээс цаг хугацаа, хүчин чармайлт шаарддаг бөгөөд Excel-ийн маш их нөөцийг "иддэг". Excel 2013-аас эхлэн хүснэгт хоорондын харилцааг тохируулах замаар бүх зүйлийг илүү хялбархан хэрэгжүүлэх боломжтой.
Үүнийг хийхийн тулд таб дээр Өгөгдөл (Огноо) дарж Харилцаа холбоо (Харилцаа). Гарч ирэх цонхонд товчлуур дээр дарна уу бий болгох (шинэ) доош унах жагсаалтаас тэдгээртэй холбоотой байх хүснэгт болон баганын нэрийг сонгоно уу:
Чухал зүйл: хүснэгтүүдийг энэ дарааллаар зааж өгөх ёстой, өөрөөр хэлбэл холбогдсон хүснэгт (Үнэ) түлхүүр баганад агуулагдах ёсгүй (нэр) хүснэгтэд байгаа шиг давхардсан бүтээгдэхүүн Борлуулалтын. Өөрөөр хэлбэл, холбогдох хүснэгт нь таны өгөгдлийг ашиглан хайж олох хүснэгт байх ёстой VPRашигласан бол.
Мэдээжийн хэрэг, хүснэгт нь ижил төстэй байдлаар холбогдсон байна Борлуулалтын ширээтэй Клиент нийтлэг баганаар Харилцагчийн:
Холбоосыг тохируулсны дараа холбоосыг удирдах цонхыг хааж болно; та энэ процедурыг давтах шаардлагагүй.
Алхам 5. Бид хураангуйг ашиглан тайлан гаргадаг
Одоо борлуулалтад дүн шинжилгээ хийж, үйл явцын динамикийг хянахын тулд жишээлбэл, пивот хүснэгтийг ашиглан ямар нэгэн тайланг үүсгэцгээе. Хүснэгтэнд идэвхтэй нүдийг тохируулах Борлуулалтын мөн туузан дээрх табыг сонгоно уу Оруулах - Пивот хүснэгт (Оруулах - Пивот хүснэгт). Нээгдсэн цонхонд Excel биднээс өгөгдлийн эх сурвалжийн талаар асуух болно (жишээ нь хүснэгт Борлуулалтын) болон тайланг байршуулах газар (шинэ хуудсан дээр байвал сайн):
Хамгийн чухал зүйл бол шалгах хайрцгийг идэвхжүүлэх шаардлагатай байна Энэ өгөгдлийг өгөгдлийн загварт нэмнэ үү (Өгөгдлийн загварт өгөгдөл нэмэх) Цонхны доод талд байгаа тул Excel нь бид тайланг зөвхөн одоогийн хүснэгтэд төдийгүй бүх харилцааг ашиглахыг хүсч байгааг ойлгох болно.
Үргэлжлүүлсний дараа OK цонхны баруун талд самбар гарч ирнэ Пивот хүснэгтийн талбаруудхолбоос дээр дарж хаана мэдээлэлЗөвхөн одоогийнхыг төдийгүй номонд байгаа бүх "ухаалаг ширээ" -ийг нэг дор үзэх. Дараа нь сонгодог пивот хүснэгтийн нэгэн адил та холбогдох хүснэгтээс бидэнд хэрэгтэй талбаруудыг зүгээр л чирж болно. Шүүлтүүр, Мөр, Столбцов or утгууд – мөн Excel нь хуудсан дээр бидэнд хэрэгтэй бүх тайланг шууд бүтээх болно:
Пивот хүснэгт дээр хулганы баруун товчийг дараад командыг сонгох замаар үе үе (эх мэдээлэл өөрчлөгдөх үед) шинэчлэгдэх шаардлагатайг бүү мартаарай. Шинэчлэх & Хадгалах (Сэргээх), учир нь үүнийг автоматаар хийх боломжгүй.
Мөн хураангуй хэсгээс дурын нүдийг сонгоод товчлуурыг дарна Пивот диаграм (пивот диаграм) таб дүн шинжилгээ (Шинжилгээ) or Үзүүлэлтүүд (Сонголтууд) та үүн дээр тооцоолсон үр дүнг хурдан төсөөлж чадна.
Алхам 6. Хэвлэх боломжтой зүйлсийг бөглөнө үү
Аливаа мэдээллийн сангийн өөр нэг ердийн ажил бол янз бүрийн хэвлэмэл маягт, маягтуудыг (нэхэмжлэх, нэхэмжлэх, акт гэх мэт) автоматаар бөглөх явдал юм. Үүнийг хийх нэг арга замуудын талаар би аль хэдийн бичсэн. Энд бид жишээ нь дансны дугаараар маягт бөглөхийг хэрэгжүүлдэг.
С2 нүдэнд хэрэглэгч тоо оруулна (хүснэгт дэх мөрийн дугаар Борлуулалтын, үнэндээ), дараа нь бидэнд хэрэгтэй өгөгдлийг аль хэдийн танил болсон функцийг ашиглан татаж авдаг VPR (ХАРАХ) болон боломжууд INDEX (INDEX).
- VLOOKUP функцийг ашиглан утгыг хайх, хайх
- VLOOKUP-г INDEX болон MATCH функцээр хэрхэн солих вэ
- Хүснэгтийн мэдээллээр маягт, маягтыг автоматаар бөглөх
- Пивот хүснэгт ашиглан тайлан үүсгэх