แลกเปลี่ยน (คัดลอก นำเข้า ส่งออก) ข้อมูลระหว่าง Excel และ Access

การใช้งาน Excel แพร่หลายและ Excel มักเป็นแอปพลิเคชัน Office ที่เหมาะสำหรับการป้อนข้อมูล การวิเคราะห์ และการจัดการ Microsoft Access ให้ความสามารถฐานข้อมูลเชิงสัมพันธ์ในสภาพแวดล้อมเดสก์ท็อปขนาดกะทัดรัด มีคนใช้ Access น้อยลง แม้ว่าจะมีฟีเจอร์มากมายสำหรับการจัดการข้อมูลและอินเทอร์เฟซก็ใช้งานง่าย Access มีฟังก์ชันทั้งหมดของแอปพลิเคชันฐานข้อมูลเชิงสัมพันธ์ รวมถึงความสามารถในการสร้างตาราง ระบุและจำกัดชนิดข้อมูลสำหรับแต่ละตาราง สร้างคิวรีแบบธรรมดาและขั้นสูง การจัดการ SQL และการสร้างฟอร์มสำหรับการป้อนข้อมูลของผู้ใช้ นอกจาก Excel จะเป็นอินเทอร์เฟซที่คุ้นเคยแล้ว ยังมีความสามารถในการสร้างแผนภูมิขั้นสูง รวมถึงฟังก์ชันและตัวเลือก Pivot Table ที่ขยายออกไป

การผสานรวมของ Excel และ Access ช่วยให้สามารถใช้ประโยชน์จากความง่ายในการป้อนข้อมูลและความสามารถในการสร้างแผนภูมิขั้นสูงของ Excel รวมกับอินเทอร์เฟซฐานข้อมูลเชิงสัมพันธ์และสภาพแวดล้อมของ Access

ต่อไปนี้คือเคล็ดลับที่มีประโยชน์บางประการสำหรับการแลกเปลี่ยนข้อมูลระหว่างแอปพลิเคชัน Office สองแอปพลิเคชัน:

1) ทำความเข้าใจตรรกะที่เกี่ยวข้องกับโครงสร้างและการสร้างตารางการเข้าถึง

Access เป็นฐานข้อมูลเชิงสัมพันธ์ตามที่กล่าวไว้ ซึ่งหมายความว่าตารางในฐานข้อมูล Access ทั้งหมดมีความเกี่ยวข้องกัน แต่ละระเบียนในฐานข้อมูล Access ควรจะสามารถระบุได้โดยไม่ซ้ำกัน สิ่งนี้ทำได้โดยการใช้คีย์หลัก คีย์หลักนี้มักจะประกอบด้วยฟิลด์เดียว อย่างไรก็ตาม ในบางกรณี คีย์หลักนี้สามารถรวมกันได้มากกว่าหนึ่งฟิลด์ คีย์หลักนี้บังคับ และ Access จะเพิ่มคอลัมน์ ID โดยอัตโนมัติ ซึ่งทำหน้าที่เป็นคีย์หลักสำหรับตาราง ใน Excel คีย์หลักไม่ใช่ข้อกำหนดบังคับเมื่อสร้างตาราง ดังนั้นผู้ใช้มักจะละทิ้งมัน

สถานการณ์นี้แสดงอยู่ด้านล่าง รูปภาพแรกแสดงตาราง Access ทั้งในแผ่นข้อมูลและมุมมองออกแบบ และคีย์หลักที่เพิ่มโดยอัตโนมัติ รูปที่สองแสดงตาราง Excel และวิธีการ ข้อมูลส่วนใหญ่จะถูกป้อนใน Excel

การป้อนข้อมูลในตาราง Excel ไม่ถูกต้อง อย่างไรก็ตาม หากคุณวางแผนที่จะใช้ Excel เป็นซอฟต์แวร์ป้อนข้อมูล แล้วนำเข้าตารางไปยัง Access ในภายหลัง (ซึ่งคล้ายกับการใช้ Excel ในรูปแบบที่ใช้งานง่ายสำหรับ Access ในทางใดทางหนึ่ง) จะเป็นการดีที่สุดตั้งแต่เริ่มแรก เพื่อเพิ่มคีย์หลักลงในตาราง Excel เมื่อตั้งค่าสเปรดชีตใน Excel

ทั้งนี้เพื่อให้แน่ใจว่าผู้ใช้จะทำความคุ้นเคยและปรับแนวกับแนวคิดคีย์หลัก และขจัดความซ้ำซ้อนของข้อมูลและความซ้ำซ้อนตั้งแต่เนิ่นๆ ให้มากที่สุด สิ่งนี้ทำได้ง่ายใน Excel เมื่อสร้างตารางใน Excel หากคุณมีตารางที่ค่อนข้างเล็ก ซึ่งยังไม่มีคอลัมน์คีย์หลัก และไม่มีความซ้ำซ้อนหรือความซ้ำซ้อนในตาราง Excel คุณสามารถเพิ่มคอลัมน์นี้ได้อย่างง่ายดาย เพียงคลิกขวาที่เซลล์ในคอลัมน์แรกของคุณในตาราง แล้วเลือก แทรก คอลัมน์ตารางทางซ้าย จากนั้นเปลี่ยนชื่อคอลัมน์นี้และเรียกมันว่า ID หากต้องการเติมข้อมูลในคอลัมน์นี้ ให้เลือกเซลล์แรกของคอลัมน์และพิมพ์ 1 จากนั้นไปที่เซลล์ที่สองของคอลัมน์แล้วพิมพ์ 2 จากนั้นไฮไลต์สองเซลล์ด้านบนแล้วลากลงโดยใช้การป้อนอัตโนมัติเพื่อเติมข้อมูลในคอลัมน์ที่เหลือ .

นอกจากนี้ เคล็ดลับที่มีประโยชน์ที่ควรทราบก็คือชื่อเขตข้อมูลในตาราง Access จะเทียบเท่ากับส่วนหัวของคอลัมน์ในตาราง Excel

คุณยังสามารถคัดลอกข้อมูลในตารางจาก Access แล้ววางลงในแผ่นงานใหม่ใน Excel ได้อีกด้วย ขอแนะนำสำหรับตาราง Access ที่มีข้อมูลจำนวนเล็กน้อย เลือกแถวทั้งหมดใน Access ที่คุณต้องการคัดลอก ไปที่แท็บหน้าแรก ในกลุ่มคลิปบอร์ด เลือกคัดลอก หรือกด CTRL-C บนแป้นพิมพ์ของคุณ

จากนั้นในแผ่นงานใหม่ในสมุดงาน Excel ให้กด CTRL-V บนแป้นพิมพ์ของคุณเพื่อวางตาราง Access ในแผ่นงาน Excel ตาราง Access ถูกวางเป็นช่วงที่มีส่วนหัวและสามารถแปลงเป็นตาราง Excel ได้โดยการเลือกเซลล์ในช่วงและกด CTRL-T บนแป้นพิมพ์ของคุณ ตรวจสอบให้แน่ใจว่าได้เลือกช่องทำเครื่องหมาย ตารางของฉันมีส่วนหัว จากนั้นกด Ok เพื่อแปลงช่วงเป็นตาราง Excel

ต่อจากข้อที่กล่าวมา ใน Access ข้อมูลจะถูกแบ่งออกเป็นเอนทิตีหรือหัวเรื่องหลัก ตัวอย่างเช่น ฐานข้อมูล Access ที่ออกแบบมาสำหรับร้านค้าขนาดเล็กมักจะมีตารางแยกกันที่เรียกว่าผลิตภัณฑ์ คำสั่งซื้อ ลูกค้า และพนักงาน (โดยกำหนดความสัมพันธ์ไว้) ใน Excel ไม่ใช่เรื่องแปลกที่จะเห็นข้อมูลซึ่งจะถูกจัดเก็บไว้ในตารางแยกต่างหากใน Access ซึ่งรวมอยู่ในตาราง Excel เดียวดังที่แสดงด้านล่าง

ตามที่กล่าวไว้ วิธีการป้อนข้อมูลลงในตาราง Excel นั้นไม่ถูกต้อง อย่างไรก็ตาม ถ้า Excel และ Access จะถูกรวมเข้าด้วยกัน ในแง่ของ Excel ก็คือซอฟต์แวร์การป้อนข้อมูล และ Access จะให้ฟังก์ชันการสืบค้นและฐานข้อมูล เป็นการดีที่สุดที่จะแบ่งข้อมูลใน Excel ที่จัดเก็บไว้ในตารางขนาดใหญ่หนึ่งตารางออกเป็นตารางขนาดเล็กสี่ตาราง และวางแต่ละตารางบนแผ่นงานของตัวเองดังที่แสดงด้านล่าง นอกจากนี้ ขอแนะนำให้จับคู่ชื่อของเขตข้อมูลในตารางใน Access กับส่วนหัวของคอลัมน์ในตารางใน Excel เพื่อหลีกเลี่ยงความสับสนและรักษาความสอดคล้อง

2) การลบรายการที่ซ้ำกันใน Excel เพื่อความสะดวกในการออกแบบตารางในภายหลังหรือการรวมข้อมูลต้นฉบับลงใน Access

มักจะเป็นเรื่องยากที่จะเห็นค่าที่ไม่ซ้ำกันโดยสรุปในตาราง Excel ที่มีข้อมูลหลายแถว สมมติว่าต้องการดูจำนวนผลิตภัณฑ์ที่ไม่ซ้ำกันสำหรับตาราง Excel ที่มีข้อมูล 100 แถว เพื่อออกแบบตารางที่มีประสิทธิภาพใน Access โดยใช้ข้อมูลที่รวบรวมจากเวิร์กบุ๊ก Excel สมุดงาน Excel ในตัวอย่างนี้อ้างอิงจากข้อมูลบางส่วนจากผู้ค้าปลีกออนไลน์ที่สมมติขึ้น ตาราง Excel ประกอบด้วยวันที่ขายผลิตภัณฑ์ ชื่อผลิตภัณฑ์ และปริมาณที่ขายได้ดังแสดงด้านล่าง

ขั้นแรก ทำสำเนาของสเปรดชีตที่มีตารางในเวิร์กบุ๊ก เพื่อเก็บข้อมูลต้นฉบับไว้ครบถ้วน จากนั้นจึงดำเนินการกับสำเนา เมื่อเลือกเซลล์ในตาราง Excel เพื่อเปิดใช้งานตัวเลือกเครื่องมือตารางตามบริบท ให้เลือกแท็บออกแบบ และในกลุ่มเครื่องมือ ให้เลือกตัวเลือกลบรายการที่ซ้ำกันดังที่แสดงด้านล่าง

กล่องโต้ตอบ Remove Duplicates ควรปรากฏขึ้นและยกเลิกการเลือก Date_Of_Sale และ Quantity แล้วคลิกตกลง กล่องข้อความควรปรากฏขึ้นเพื่อระบุจำนวนค่าที่ซ้ำกันถูกลบออก คลิกตกลง

ตารางผลลัพธ์ใน Excel สามารถใช้เพื่อช่วยรวบรวมข้อมูลสำหรับตาราง Access สำหรับฐานข้อมูล Access ที่ผู้ค้าปลีกออนไลน์เสนอ

อ่านเพิ่มเติม:วิธีคัดลอกค่าที่ไม่ซ้ำไปยังแผ่นงานอื่นใน Excel (5 วิธี)

3) การใช้ฟังก์ชัน TRIM() เพื่อลบช่องว่างที่ไม่จำเป็น และใช้ขีดล่างเพื่อรักษาความสอดคล้องระหว่างชื่อฟิลด์ใน Access และส่วนหัวของคอลัมน์ใน Excel

ชื่อช่อง Access ไม่สามารถเริ่มต้นด้วยช่องว่างนำหน้าได้ ดังนั้นจึงสามารถใช้ฟังก์ชัน TRIM () กับชื่อส่วนหัวของคอลัมน์ของตารางใน Excel เพื่อลบช่องว่าง

ท้ายที่สุด เราต้องนำเข้าข้อมูลไปยัง SQL Server หรือฐานข้อมูลอื่น เช่น Oracle ดังนั้น โฟลว์ของโซลูชันแบบรวมจะเป็นการป้อนข้อมูลใน Excel จากนั้นส่งออกข้อมูลนี้ไปยัง Access แล้วนำเข้าตาราง Access ลงในฐานข้อมูลระดับองค์กร เช่น SQL Server หรือ Oracle ฐานข้อมูลอื่นไม่อ่านช่องว่างระหว่างชื่อฟิลด์ได้เป็นอย่างดี ดังนั้น แทนที่จะเรียกชื่อคอลัมน์หัวเรื่องในตาราง Excel ชื่อผลิตภัณฑ์จะเรียกว่า Name_of_Product โดยใช้เครื่องหมายขีดล่างเพื่อความสะดวกในการรวมเข้ากับฐานข้อมูลอื่นๆ SQL ยังจัดการรูปแบบการตั้งชื่อของตารางได้ดีกว่าชื่อที่มีช่องว่าง

คุณสามารถใช้คุณสมบัติคำอธิบายภาพใน Access ดังนั้นถ้าชื่อเขตข้อมูลของคุณในตารางของคุณคือ Name_of_Product ให้ป้อนคำอธิบายภาพหรือป้ายชื่อเป็นชื่อผลิตภัณฑ์ในมุมมองออกแบบ ดังนั้นในมุมมองแผ่นข้อมูลหรือบนฟอร์ม Name of Product จะถูกใช้แทน ในขณะที่ชื่อฟิลด์พื้นฐานมีขีดล่าง ซึ่งมีวัตถุประสงค์หลักเพื่อความสวยงามสำหรับผู้ดูส่วนหน้าของฐานข้อมูลใน Access

การอ่านที่คล้ายกัน

• ข้อผิดพลาดรันไทม์ 1004:วิธีการ PasteSpecial ของคลาสช่วงล้มเหลว
• สูตรสำหรับคัดลอกและวางค่าใน Excel (5 ตัวอย่าง)
• วิธีวางลิงก์และสลับเปลี่ยนใน Excel (8 วิธีด่วน)
• คัดลอกและวางใน Excel ด้วยเซลล์ที่ผสาน (2 วิธี)
• วิธีการคัดลอกเฉพาะเซลล์ที่มองเห็นได้โดยไม่ต้องใช้ส่วนหัวโดยใช้ VBA

4) การนำเข้าข้อมูลเข้าสู่ Access จาก Excel – จัดการกับสองสถานการณ์หลัก

• ในสถานการณ์ที่หนึ่ง ฐานข้อมูลหนึ่งมีฐานข้อมูล Access ที่ว่างเปล่าทั้งหมด และต้องการนำเข้าข้อมูลจาก Excel (ตารางใน Excel ได้รับการตั้งค่าด้วย ID และความซ้ำซ้อนและการทำซ้ำจะลดลงมากที่สุดเท่าที่จะมากได้) และใช้ Excel เป็น โปรแกรมหรือแบบฟอร์มการป้อนข้อมูล เมื่อเปิดฐานข้อมูล Access ให้ไปที่แท็บข้อมูลภายนอก ในกลุ่มนำเข้าและลิงก์ ให้เลือก Excel ตามที่แสดง

รับข้อมูลภายนอก – ตัวช่วยสร้างสเปรดชีต Excel ควรปรากฏขึ้น นำทางไปยังสมุดงาน Excel ที่มีข้อมูล เลือกลิงก์ไปยังแหล่งข้อมูลโดยการสร้างตัวเลือกตารางที่ลิงก์ อนุญาตให้ป้อนข้อมูลใน Excel เนื่องจากเราต้องการให้ผู้ใช้ป้อนข้อมูลใน Excel การเปลี่ยนแปลงใดๆ ที่ทำกับข้อมูลนี้ใน Excel จะแสดงใน Access แต่จะไม่สามารถแก้ไขได้ใน Access เกือบจะพร้อมใช้งานเฉพาะใน Access ในแง่ของการป้อนข้อมูลในมุมมองแผ่นข้อมูล คลิกตกลง

ถ้าตารางได้รับการตั้งค่าอย่างถูกต้องใน Excel Access จะตรวจหาและตรวจสอบว่าแถวแรกมีส่วนหัวของคอลัมน์ตามที่แสดงด้านล่างโดยอัตโนมัติหรือไม่

การคลิกถัดไปจะนำไปสู่ขั้นตอนสุดท้ายในกระบวนการ ซึ่งจะขอให้ป้อนชื่อตารางที่เชื่อมโยงแล้วคลิกเสร็จสิ้น

ข้อความจะปรากฏขึ้นเพื่อแจ้งว่าตารางถูกเชื่อมโยงแล้ว

ซึ่งจะแสดงข้อมูล Excel ให้กับฟังก์ชันการทำงานของ SQL เพิ่มเติม และตัวสร้างนิพจน์สำหรับการสร้างคิวรีที่ซับซ้อน ซึ่งพร้อมใช้งานในสภาพแวดล้อมของ Access ผลลัพธ์ของคิวรีที่ซับซ้อนสามารถใช้ในแดชบอร์ดของ Excel ได้ในภายหลัง ขอแนะนำ แต่ไม่จำเป็นต้องเก็บตารางการป้อนข้อมูล Excel และฐานข้อมูล Access ที่มีตารางที่เชื่อมโยงในโฟลเดอร์หรือไดเรกทอรีเดียวกันเพียงเพื่อวัตถุประสงค์ขององค์กร

• ในสถานการณ์ที่สอง ฐานข้อมูลหนึ่งมีฐานข้อมูล Access ว่างหรือเติมข้อมูล และอีกรายการหนึ่งต้องการนำเข้าตาราง Excel แต่ไม่มีการเชื่อมโยงไว้ หนึ่งต้องการแก้ไขในการเข้าถึง ด้วยวิธีนั้นการเปลี่ยนแปลงใดๆ ก็ตามที่ทำในภายหลังในแผ่นงาน Excel จะไม่มีผลใน Access และการเปลี่ยนแปลงใดๆ ก็ตามใน Access สำหรับการแก้ไขและคิวรีจะมีข้อมูลทั้งหมด โดยปกติจะเป็นตัวเลือกที่เลือกเมื่อต้องการควบคุมประเภทข้อมูลที่ป้อนในตารางอย่างเคร่งครัด

อีกครั้ง เมื่อเปิดฐานข้อมูล Access ให้ไปที่แท็บข้อมูลภายนอก ในกลุ่มนำเข้าและลิงก์ ให้เลือก Excel ตามที่แสดง

ในกรณีนี้ เมื่อตัวช่วยสร้างปรากฏขึ้น ให้เลือกนำเข้าข้อมูลต้นฉบับลงในตารางใหม่ในฐานข้อมูลปัจจุบัน เพื่อสร้างสำเนาของตารางใน Access ที่ตอนนี้ยกเลิกการลิงก์กับตาราง Excel ต้นฉบับแล้ว คลิกตกลง

คลิกถัดไปในหน้าจอถัดไป

จากนั้นจะระบุข้อมูลเกี่ยวกับฟิลด์ที่กำลังนำเข้าดังที่แสดงด้านล่าง ตัวช่วยสร้างยังช่วยให้สามารถเลือกฟิลด์ที่จะข้ามได้เช่นกัน เนื่องจากตาราง Excel มักมีข้อมูลที่จำเป็นต้องอยู่ในตารางอื่นใน Access จริงๆ จึงสามารถหลีกเลี่ยงการนำเข้าคอลัมน์เหล่านี้ลงในตารางปัจจุบันได้

จากนั้นให้ Access เพิ่มคีย์หลักหรือเลือกคีย์หลักของตนเองได้ ในกรณีนี้ เนื่องจากตารางมีคอลัมน์ ID ที่กำหนดไว้ใน Excel แล้ว จึงเลือกตัวเลือกที่สองดังที่แสดง

คลิกถัดไป

จากนั้นเลือกชื่อสำหรับตารางใน Access แล้วคลิกเสร็จสิ้น

ระบบจะถามคุณว่าต้องการบันทึกขั้นตอนการนำเข้าหรือไม่ ตอนนี้ให้คลิกปิด The table is now in Access for further editing, entry, and manipulation and is unlinked to the Excel workbook.

5) Using Table Analyzer to normalize imported data and split the data into separate tables in Access

Later versions of Access, have the Table Analyzer tool, which helps users separate one table into many tables. This is especially useful if one did import the data from a big Excel table and one is struggling to separate the tables. One can launch this wizard as part of the import process and check the box, I would like a wizard to analyze my table after importing the data as shown below.

Or one can access it, if the tables are already imported as standalone Access tables, by going to Database Tools, in the Analyze group, choose Analyze Table.

The Table Analyzer wizard should be launched, which will guide you through the steps of creating separate tables.

Table Analyzer, however, does not always guess right, so use it with caution.

6) Importing data from Access into Excel and using VLOOKUP to link the data and populate columns

One can import Access tables into Excel worksheets. With an Excel workbook opened, go to the Data tab, in the Get External Data group, choose From Access as shown below.

Navigate to the Access database containing the tables, you want to import into your Excel spreadsheet.

Select the table one wants to import, in this case, the ProductT.

Click Ok.

Choose to Import the data as a Table, on the existing worksheet, choose cell A1, and then Click Ok.

The table is imported as an Excel table.

The same was done for the three other tables in the database, namely Orders, Employees, and Customers tables.

One now has an Orders table which looks like the one below.

This table with numbers in Customer_ID, Product_ID, and Employee_ID can be counter-intuitive and confusing for Excel users not used to the relational database theory and tables. Thus one could add three helper columns to the table called Actual_Customer, Actual_Product, and Actual_Employee as shown below.

In Cell F2, the following formula was used:

=VLOOKUP([@[Customer_ID]],Table_TablesToBeImportedIntoExcel.accdb5,2,FALSE)

Where, [@[Customer_ID]] refers to cell C2, which contains the lookup value, Table_TablesToBeImportedIntoExcel.accdb5 refers to the imported Customers table on the Customers sheet, (Excel gives this table a default name), highlight the data, but not the column headings, the same way one would for a normal VLOOKUP, the customer name is in column 2, and the type of lookup is an exact match.

Double click and send the formula down to populate the Actual_Customer column.

Do the same thing to populate the Actual_Product and Actual_Employee columns. So one can quickly repopulate Excel tables with actual text values as needed, using the VLOOKUP functionality in order to orientate the Excel users.

7) Using data already set up in a relational Access database, for Power Pivot manipulation

Power Pivot is a Business Intelligence tool available with Excel 2013 and later versions of Excel. It also requires data to be input in a relational manner. So, what you can do when you are importing Access tables into Excel (already normalized and designed using relational data logic), is to add the imported tables to the data model.

In a blank workbook, go to Data, get External data and choose from Access as for the case above. However, this time when asked to select a table, check the Enable selection of multiple tables option in the Select Table dialog box and select all the tables as shown below:

Click Ok.

Make sure the PivotTable Report option is selected, the Add this data to the Data Model should be checked because we imported multiple tables. If you only import one table at a time, you will need to check this option yourself. If you click on the drop-down arrow next to Properties, you should see that the relationships between the tables will also be imported.

Click Ok to create the Pivot TableReport, with the tables already added to the Data Model. The relationships should be visible in the PowerPivot for Excel Window, Diagram view. Thus the data is ready for further manipulation using Power Pivot functionality.

Download Files

Exchange-Data-Between-Access-and-Excel

บทสรุป

Excel and Access integration allow one tallow advantage of the combination of features in Excel and Access. The key to integrating Excel and Access successfully is understanding a little bit about relational database design basics.

Understanding a little bit of relational design and table structure will also set you in good stead with respect to taking advantage of Power Pivot and related Business Intelligence tools in the Excel environment.

Please feel free to comment and tell us if you integrate Excel and Access, whether or not you use Power Pivot, and any other tips for integrating Excel and Access, that you may have.

บทความที่เกี่ยวข้อง

• Excel VBA:Copy Cell Value and Paste to Another Cell
• Excel VBA:Copy Range to Another Workbook
• [Fixed]:Right Click Copy and Paste Not Working in Excel (11 Solutions)
• Difference Between Paste and Paste Special in Excel
• How to Copy Alternate Rows in Excel (4 Ways)
• How to Apply VBA PasteSpecial and Keep Source Formatting in Excel