เนื้อหา
เมื่อพูดถึงฐานข้อมูล (DB) สิ่งแรกที่นึกถึงก็คือคำศัพท์ทุกประเภท เช่น SQL, Oracle, 1C หรืออย่างน้อย Access แน่นอนว่านี่เป็นโปรแกรมที่ทรงพลังมาก (และส่วนใหญ่มีราคาแพง) ที่สามารถทำให้งานของบริษัทขนาดใหญ่และซับซ้อนเป็นไปโดยอัตโนมัติด้วยข้อมูลจำนวนมาก ปัญหาคือบางครั้งไม่จำเป็นต้องใช้พลังงานดังกล่าว ธุรกิจของคุณอาจมีขนาดเล็กและมีกระบวนการทางธุรกิจที่ค่อนข้างง่าย แต่คุณต้องการทำให้เป็นอัตโนมัติด้วย และสำหรับบริษัทขนาดเล็กมักเป็นเรื่องของการอยู่รอด
เริ่มต้นด้วย มากำหนด TOR กัน ในกรณีส่วนใหญ่ ฐานข้อมูลสำหรับการบัญชี ตัวอย่างเช่น การขายแบบคลาสสิก ควรจะสามารถ:
- เก็บ ในตารางข้อมูลเกี่ยวกับสินค้า (ราคา) ธุรกรรมที่เสร็จสมบูรณ์และลูกค้าและเชื่อมโยงตารางเหล่านี้เข้าด้วยกัน
- มีความสะดวกสบาย แบบฟอร์มการป้อนข้อมูล ข้อมูล (พร้อมรายการดรอปดาวน์ ฯลฯ)
- กรอกข้อมูลบางส่วนโดยอัตโนมัติ แบบพิมพ์ (การชำระเงิน บิล ฯลฯ)
- ออกของที่จำเป็น รายงาน เพื่อควบคุมกระบวนการทางธุรกิจทั้งหมดจากมุมมองของผู้จัดการ
Microsoft Excel สามารถจัดการทั้งหมดนี้ได้ด้วยความพยายามเพียงเล็กน้อย มาลองใช้สิ่งนี้กัน
ขั้นตอนที่ 1 ข้อมูลเริ่มต้นในรูปแบบของตาราง
เราจะจัดเก็บข้อมูลเกี่ยวกับผลิตภัณฑ์ การขาย และลูกค้าไว้ในสามตาราง (ในแผ่นงานเดียวกันหรือคนละตาราง ไม่สำคัญ) การเปลี่ยนให้เป็น "สมาร์ทเทเบิล" ด้วยการปรับขนาดอัตโนมัติเป็นสิ่งสำคัญโดยพื้นฐาน เพื่อไม่ให้คิดถึงเรื่องนี้อีกในอนาคต เสร็จสิ้นด้วยคำสั่ง จัดรูปแบบเป็นตาราง แถบ หน้าแรก (หน้าแรก — รูปแบบเป็นตาราง). บนแท็บที่ปรากฏขึ้น นวกรรมิก (ออกแบบ) ให้ชื่อตารางอธิบายในช่อง ชื่อตาราง เพื่อใช้ในภายหลัง:
โดยรวมแล้ว เราควรได้ “สมาร์ทเทเบิล” สามตัว:
โปรดทราบว่าตารางอาจมีข้อมูลชี้แจงเพิ่มเติม ตัวอย่างเช่น . ของเรา ราคามีข้อมูลเพิ่มเติมเกี่ยวกับหมวดหมู่ (กลุ่มผลิตภัณฑ์ บรรจุภัณฑ์ น้ำหนัก ฯลฯ) ของแต่ละผลิตภัณฑ์ และตาราง ไคลเอนต์ — เมืองและภูมิภาค (ที่อยู่, TIN, รายละเอียดธนาคาร ฯลฯ) ของแต่ละแห่ง
ตาราง การขาย เราจะใช้ในภายหลังเพื่อทำธุรกรรมที่เสร็จสมบูรณ์
ขั้นตอนที่ 2 สร้างแบบฟอร์มการป้อนข้อมูล
แน่นอน คุณสามารถป้อนข้อมูลการขายลงในตารางสีเขียวได้โดยตรง การขายแต่สิ่งนี้ไม่สะดวกเสมอไปและทำให้เกิดข้อผิดพลาดและการพิมพ์ผิดเนื่องจาก "ปัจจัยมนุษย์" ดังนั้นจึงเป็นการดีกว่าที่จะสร้างฟอร์มพิเศษสำหรับการป้อนข้อมูลในชีตแยกต่างหากของสิ่งนี้:
ในเซลล์ B3 หากต้องการรับวันที่-เวลาปัจจุบันที่อัปเดต ให้ใช้ฟังก์ชัน ทีดาต้า (ตอนนี้). ถ้าไม่ต้องการเวลาก็แทน ทีดาต้า ใช้งานได้จริง วันนี้ (วันนี้).
ในเซลล์ B11 ค้นหาราคาของผลิตภัณฑ์ที่เลือกในคอลัมน์ที่สามของตารางอัจฉริยะ ราคา การใช้ฟังก์ชัน VPR (วีลุคอัพ). หากคุณไม่เคยพบเจอมาก่อน ให้อ่านและดูวิดีโอที่นี่ก่อน
ในเซลล์ B7 เราจำเป็นต้องมีรายการดรอปดาวน์ที่มีสินค้าจากรายการราคา สำหรับสิ่งนี้คุณสามารถใช้คำสั่ง ข้อมูล – การตรวจสอบข้อมูล (การตรวจสอบข้อมูล), ระบุเป็นข้อจำกัด รายการ (รายการ) แล้วเข้าสนาม แหล่ง (ที่มา) ลิงค์ไปยังคอลัมน์ Name จากโต๊ะอัจฉริยะของเรา ราคา:
ในทำนองเดียวกัน รายการดรอปดาวน์พร้อมไคลเอ็นต์จะถูกสร้างขึ้น แต่แหล่งที่มาจะแคบลง:
=INDIRECT(“ลูกค้า[ไคลเอนต์]”)
ฟังก์ชัน ทางอ้อม (ทางอ้อม) ในกรณีนี้จำเป็นต้องใช้ เนื่องจาก Excel ไม่เข้าใจลิงก์โดยตรงไปยังตารางอัจฉริยะในฟิลด์แหล่งที่มา แต่ลิงก์เดียวกัน "ห่อ" ในฟังก์ชัน ทางอ้อม ในขณะเดียวกันก็ใช้งานได้ดี (เพิ่มเติมเกี่ยวกับเรื่องนี้ในบทความเกี่ยวกับการสร้างรายการแบบเลื่อนลงพร้อมเนื้อหา)
ขั้นตอนที่ 3 การเพิ่มแมโครรายการขาย
หลังจากกรอกแบบฟอร์ม คุณต้องเพิ่มข้อมูลที่ป้อนลงในส่วนท้ายของตาราง การขาย. ใช้ลิงก์ง่าย ๆ เราจะสร้างบรรทัดที่จะเพิ่มด้านล่างแบบฟอร์ม:
เหล่านั้น. เซลล์ A20 จะมีลิงก์ไปยัง =B3 เซลล์ B20 จะมีลิงก์ไปยัง =B7 เป็นต้น
ตอนนี้ มาเพิ่มแมโครพื้นฐาน 2 บรรทัดที่คัดลอกสตริงที่สร้างขึ้นและเพิ่มลงในตารางการขาย เมื่อต้องการทำเช่นนี้ ให้กดชุดค่าผสม Alt + F11 หรือปุ่ม ของ Visual Basic แถบ ผู้พัฒนา (ผู้พัฒนา). หากมองไม่เห็นแท็บนี้ ให้เปิดใช้งานก่อนในการตั้งค่า ไฟล์ – ตัวเลือก – การตั้งค่าริบบอน (ไฟล์ — ตัวเลือก — กำหนด Ribbon เอง). ในหน้าต่างตัวแก้ไข Visual Basic ที่เปิดขึ้น ให้แทรกโมดูลว่างใหม่ผ่านเมนู แทรก – โมดูล และป้อนรหัสมาโครของเราที่นั่น:
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 = Worksheets("Sales") . แถว 'กำหนดจำนวนแถวสุดท้ายในตาราง Sales Worksheets("Sales")).Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'paste into the next lineว่างแผ่นงาน("Input Form").Range("B5,B7,B9") ClearContents 'ล้างแบบฟอร์มย่อยสิ้นสุด ตอนนี้เราสามารถเพิ่มปุ่มในแบบฟอร์มของเราเพื่อเรียกใช้แมโครที่สร้างขึ้นโดยใช้รายการแบบเลื่อนลง สิ่งที่ใส่เข้าไป แถบ ผู้พัฒนา (ผู้พัฒนา — แทรก — ปุ่ม):
หลังจากที่คุณวาดมัน ให้กดปุ่มซ้ายของเมาส์ค้างไว้ Excel จะถามคุณว่าคุณต้องกำหนดมาโครใดให้กับมัน เลือกมาโครของเรา เพิ่ม_ขาย. คุณสามารถเปลี่ยนข้อความบนปุ่มได้โดยคลิกขวาที่ปุ่มนั้นแล้วเลือกคำสั่ง เปลี่ยนข้อความ.
ตอนนี้ หลังจากกรอกแบบฟอร์ม คุณสามารถคลิกที่ปุ่มของเรา และข้อมูลที่ป้อนจะถูกเพิ่มลงในตารางโดยอัตโนมัติ การขายแล้วล้างแบบฟอร์มเพื่อเข้าสู่ข้อตกลงใหม่
ขั้นตอนที่ 4 การเชื่อมโยงตาราง
ก่อนสร้างรายงาน เรามาเชื่อมโยงตารางของเราเข้าด้วยกัน เพื่อให้เราสามารถคำนวณยอดขายตามภูมิภาค ลูกค้า หรือหมวดหมู่ได้อย่างรวดเร็วในภายหลัง ใน Excel เวอร์ชันเก่า จะต้องใช้หลายฟังก์ชัน VPR (วีลุคอัพ) เพื่อแทนที่ราคา หมวดหมู่ ลูกค้า เมือง ฯลฯ ลงในตาราง การขาย. สิ่งนี้ต้องใช้เวลาและความพยายามจากเรา และยัง "กิน" ทรัพยากร Excel จำนวนมากด้วย เริ่มต้นด้วย Excel 2013 ทุกอย่างสามารถนำไปใช้ได้ง่ายขึ้นโดยการตั้งค่าความสัมพันธ์ระหว่างตาราง
ในการทำเช่นนี้บนแท็บ ข้อมูล (วันที่) คลิก ความสัมพันธ์ (ความสัมพันธ์). ในหน้าต่างที่ปรากฏขึ้น ให้คลิกปุ่ม สร้างบัญชีตัวแทน (ใหม่) และเลือกจากดรอปดาวน์แสดงรายการตารางและชื่อคอลัมน์ที่เกี่ยวข้องกัน:
จุดสำคัญ: ต้องระบุตารางตามลำดับนี้ เช่น ตารางที่เชื่อมโยง (ราคา) ต้องไม่มีอยู่ในคอลัมน์คีย์ (Name) สินค้าที่ซ้ำกัน ตามที่ปรากฏในตาราง การขาย. กล่าวอีกนัยหนึ่ง ตารางที่เกี่ยวข้องจะต้องเป็นตารางที่คุณจะค้นหาข้อมูลโดยใช้ VPRถ้ามันถูกใช้
แน่นอนว่าตารางเชื่อมต่อในลักษณะเดียวกัน การขาย พร้อมโต๊ะ ไคลเอนต์ ตามคอลัมน์ทั่วไป ลูกค้า:
หลังจากตั้งค่าลิงก์แล้ว คุณสามารถปิดหน้าต่างสำหรับจัดการลิงก์ได้ คุณไม่จำเป็นต้องทำซ้ำขั้นตอนนี้
ขั้นตอนที่ 5. เราสร้างรายงานโดยใช้ข้อมูลสรุป
ในตอนนี้ ในการวิเคราะห์การขายและติดตามการเปลี่ยนแปลงของกระบวนการ เรามาสร้างรายงานบางประเภทโดยใช้ตารางสรุปข้อมูลกัน ตั้งค่าเซลล์ที่ใช้งานเป็นตาราง การขาย แล้วเลือกแท็บบนริบบิ้น แทรก – PivotTable (แทรก — ตารางหมุน). ในหน้าต่างที่เปิดขึ้น Excel จะถามเราเกี่ยวกับแหล่งข้อมูล (เช่น table การขาย) และที่สำหรับอัปโหลดรายงาน (ควรอยู่ในแผ่นงานใหม่):
จุดสำคัญคือจำเป็นต้องเปิดใช้งานช่องทำเครื่องหมาย เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล (เพิ่มข้อมูลลงในตัวแบบข้อมูล) ที่ด้านล่างของหน้าต่างเพื่อให้ Excel เข้าใจว่าเราต้องการสร้างรายงานไม่เพียงแต่ในตารางปัจจุบัน แต่ยังใช้ความสัมพันธ์ทั้งหมดด้วย
หลังจากคลิกที่ OK แผงจะปรากฏขึ้นที่ครึ่งขวาของหน้าต่าง เขตข้อมูลตาราง Pivotคลิ๊กลิงค์ไหนคะ ทั้งหมดเพื่อดูไม่เพียงแต่ปัจจุบันเท่านั้น แต่ยังรวมถึง "สมาร์ทเทเบิล" ทั้งหมดที่อยู่ในหนังสือพร้อมกันด้วย จากนั้น เช่นเดียวกับในตาราง Pivot แบบคลาสสิก คุณเพียงแค่ลากเขตข้อมูลที่เราต้องการจากตารางที่เกี่ยวข้องลงในพื้นที่ ตัวกรอง, แถว, สโตลบ์ตซอฟ or ความคุ้มค่า – และ Excel จะสร้างรายงานใดๆ ที่เราต้องการบนแผ่นงานทันที:
อย่าลืมว่าต้องอัปเดตตารางสาระสำคัญเป็นระยะ (เมื่อข้อมูลต้นทางเปลี่ยนแปลง) โดยคลิกขวาที่ตารางและเลือกคำสั่ง อัปเดตและบันทึก (รีเฟรช)เพราะมันไม่สามารถทำได้โดยอัตโนมัติ
นอกจากนี้ โดยการเลือกเซลล์ใดๆ ในสรุปแล้วกดปุ่ม แผนภูมิ Pivot (แผนภูมิ Pivot) แถบ การวิเคราะห์ (การวิเคราะห์) or พารามิเตอร์ (ตัวเลือก) คุณสามารถเห็นภาพผลลัพธ์ที่คำนวณได้อย่างรวดเร็ว
ขั้นตอนที่ 6. กรอกข้อมูลที่พิมพ์ได้
งานทั่วไปอีกอย่างของฐานข้อมูลคือการกรอกแบบฟอร์มและแบบฟอร์มต่างๆ ที่พิมพ์ออกมาโดยอัตโนมัติ (ใบแจ้งหนี้ ใบแจ้งหนี้ การกระทำ ฯลฯ) ฉันได้เขียนเกี่ยวกับวิธีการทำสิ่งนี้แล้ว ที่นี่เราใช้เช่นกรอกแบบฟอร์มตามหมายเลขบัญชี:
สันนิษฐานว่าในเซลล์ C2 ผู้ใช้จะป้อนตัวเลข (หมายเลขแถวในตาราง การขายอันที่จริง) จากนั้นข้อมูลที่เราต้องการจะถูกดึงขึ้นมาโดยใช้ฟังก์ชันที่คุ้นเคยอยู่แล้ว VPR (วีลุคอัพ) และคุณสมบัติ ดัชนี (ดัชนี).
- วิธีใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาและค้นหาค่า
- วิธีแทนที่ VLOOKUP ด้วยฟังก์ชัน INDEX และ MATCH
- กรอกแบบฟอร์มและแบบฟอร์มอัตโนมัติด้วยข้อมูลจากตาราง
- การสร้างรายงานด้วย PivotTables