เปรียบเทียบสองตาราง

เรามีตารางสองตาราง (เช่น เวอร์ชันเก่าและเวอร์ชันใหม่ของรายการราคา) ซึ่งเราต้องเปรียบเทียบและค้นหาความแตกต่างอย่างรวดเร็ว:

เป็นที่ชัดเจนในทันทีว่ามีการเพิ่มบางอย่างในรายการราคาใหม่ (วันที่ กระเทียม …) มีบางอย่างหายไป (แบล็กเบอร์รี่ ราสเบอร์รี่ …) ราคาสินค้าบางอย่างเปลี่ยนแปลงไป (มะเดื่อ แตง …) คุณต้องค้นหาและแสดงการเปลี่ยนแปลงทั้งหมดอย่างรวดเร็ว

สำหรับงานใดๆ ใน Excel มักจะมีมากกว่าหนึ่งโซลูชัน (โดยปกติคือ 4-5) สำหรับปัญหาของเรา สามารถใช้วิธีการต่างๆ ได้มากมาย:

• ฟังก์ชัน VPR (วีลุคอัพ) — มองหาชื่อผลิตภัณฑ์จากรายการราคาใหม่ในรายการราคาเก่าและแสดงราคาเก่าถัดจากรายการราคาใหม่แล้วจับความแตกต่าง
• รวมสองรายการเป็นหนึ่งเดียวแล้วสร้างตารางเดือยตามนั้น ซึ่งจะเห็นความแตกต่างได้ชัดเจน
• ใช้ Power Query Add-in สำหรับ Excel

ลองนำพวกเขาทั้งหมดตามลำดับ

วิธีที่ 1 เปรียบเทียบตารางกับฟังก์ชัน VLOOKUP

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

โดยทั่วไป ฟังก์ชันนี้ใช้เพื่อดึงข้อมูลจากตารางหนึ่งไปยังอีกตารางหนึ่งโดยการจับคู่พารามิเตอร์ทั่วไปบางตัว ในกรณีนี้ เราจะใช้เพื่อผลักดันราคาเก่าให้เป็นราคาใหม่:

ผลิตภัณฑ์เหล่านั้นซึ่งเกิดข้อผิดพลาด #N/A นั้นไม่อยู่ในรายการเก่า กล่าวคือ ถูกเพิ่มเข้าไป การเปลี่ยนแปลงราคายังมองเห็นได้ชัดเจน

ข้อดี วิธีนี้: เรียบง่ายและชัดเจน "คลาสสิกของแนวเพลง" อย่างที่พวกเขาพูด ทำงานใน Excel เวอร์ชันใดก็ได้

จุดด้อย ก็อยู่ที่นั่นด้วย ในการค้นหาสินค้าที่เพิ่มในรายการราคาใหม่ คุณจะต้องทำขั้นตอนเดียวกันในทิศทางตรงกันข้าม กล่าวคือ ดึงราคาใหม่เป็นราคาเดิมด้วยความช่วยเหลือของ VLOOKUP หากพรุ่งนี้ขนาดของตารางเปลี่ยนแปลง จะต้องปรับสูตร และบนโต๊ะขนาดใหญ่จริงๆ (> 100 แถว) ความสุขทั้งหมดนี้จะลดลงอย่างเหมาะสม

วิธีที่ 2: เปรียบเทียบตารางโดยใช้ pivot

มาคัดลอกตารางของเรากัน โดยเพิ่มคอลัมน์ที่มีชื่อของรายการราคา เพื่อที่คุณจะได้เข้าใจในภายหลังว่ารายการใดแถวใด:

ตอนนี้ ตามตารางที่สร้างขึ้น เราจะสร้างสรุปผ่าน แทรก – PivotTable (แทรก — ตารางหมุน). มาขว้างสนามกันเถอะ ผลิตภัณฑ์ ไปยังพื้นที่เส้นสนาม ราคา ไปยังพื้นที่คอลัมน์และฟิลด์ ЦENA อยู่ในช่วง:

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

ผลรวมทั้งหมดในตารางดังกล่าวไม่สมเหตุสมผลและสามารถปิดการใช้งานบนแท็บได้ ตัวสร้าง – ผลรวมทั้งหมด – ปิดใช้งานสำหรับแถวและคอลัมน์ (การออกแบบ — ผลรวมทั้งหมด).

หากราคาเปลี่ยนแปลง (แต่ไม่ใช่ปริมาณของสินค้า!) ก็เพียงพอแล้วที่จะอัปเดตสรุปที่สร้างขึ้นโดยคลิกขวาที่มัน – รีเฟรช.

ข้อดี: วิธีนี้เป็นลำดับความสำคัญเร็วกว่าด้วยตารางขนาดใหญ่กว่า VLOOKUP 

จุดด้อย: คุณต้องคัดลอกข้อมูลภายใต้กันและกันและเพิ่มคอลัมน์ที่มีชื่อของรายการราคา หากขนาดของโต๊ะเปลี่ยนไป คุณต้องทำทุกอย่างใหม่ทั้งหมดอีกครั้ง

วิธีที่ 3: เปรียบเทียบตารางกับ Power Query

Power Query เป็น Add-in ฟรีสำหรับ Microsoft Excel ที่ให้คุณโหลดข้อมูลลงใน Excel ได้จากเกือบทุกแหล่ง แล้วแปลงข้อมูลนี้ในลักษณะที่ต้องการ ใน Excel 2016 Add-in นี้มีอยู่แล้วในแท็บ ข้อมูล (ข้อมูล), และสำหรับ Excel 2010-2013 คุณต้องดาวน์โหลดแยกต่างหากจากเว็บไซต์ Microsoft และติดตั้ง – รับแท็บใหม่ Power Query.

ก่อนที่จะโหลดรายการราคาของเราลงใน Power Query จะต้องแปลงเป็นตารางอัจฉริยะก่อน ในการดำเนินการนี้ ให้เลือกช่วงที่มีข้อมูลและกดรวมกันบนแป้นพิมพ์ Ctrl+T หรือเลือกแท็บบนริบบิ้น หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง). ชื่อของตารางที่สร้างขึ้นสามารถแก้ไขได้บนแท็บ นวกรรมิก (ฉันจะออกจากมาตรฐาน 1 ตาราง и 2 ตารางซึ่งได้มาโดยปริยาย)

โหลดราคาเก่าใน Power Query โดยใช้ปุ่ม จากตาราง/ช่วง (จากตาราง/ช่วง) จากแท็บ ข้อมูล (วันที่) หรือจากแท็บ Power Query (ขึ้นอยู่กับรุ่นของ Excel) หลังจากโหลดเราจะกลับไปที่ Excel จาก Power Query ด้วยคำสั่ง ปิดและโหลด - ปิดและโหลดใน... (ปิด & โหลด — ปิด & โหลดไปที่…):

… และในหน้าต่างที่ปรากฏขึ้น ให้เลือก เพียงแค่สร้างการเชื่อมต่อ (การเชื่อมต่อเท่านั้น).

ทำซ้ำเช่นเดียวกันกับรายการราคาใหม่ 

ตอนนี้ มาสร้างแบบสอบถามที่สามที่จะรวมและเปรียบเทียบข้อมูลจากสองก่อนหน้านี้ เมื่อต้องการทำสิ่งนี้ ให้เลือกใน Excel บนแท็บ ข้อมูล – รับข้อมูล – รวมคำขอ – รวม (ข้อมูล — รับข้อมูล — ผสานการสืบค้น — ผสาน) หรือกดปุ่ม รวมกัน (ผสาน) แถบ Power Query.

ในหน้าต่างการรวม เลือกตารางของเราในรายการดรอปดาวน์ เลือกคอลัมน์ที่มีชื่อสินค้าอยู่ในนั้น และตั้งค่าวิธีการเข้าร่วมที่ด้านล่าง – ภายนอกที่สมบูรณ์ (นอกเต็ม):

หลังจากคลิกที่ OK ตารางที่มีสามคอลัมน์จะปรากฏขึ้น โดยในคอลัมน์ที่สาม คุณต้องขยายเนื้อหาของตารางที่ซ้อนกันโดยใช้ลูกศรคู่ในส่วนหัว:

เป็นผลให้เราได้รับการรวมข้อมูลจากทั้งสองตาราง:

เป็นการดีกว่าที่จะเปลี่ยนชื่อคอลัมน์ในส่วนหัวโดยดับเบิลคลิกที่ชื่อที่เข้าใจได้มากขึ้น:

และตอนนี้ที่น่าสนใจที่สุด ไปที่แท็บ เพิ่มคอลัมน์ (เพิ่มคอลัมน์) และคลิกที่ปุ่ม คอลัมน์เงื่อนไข (คอลัมน์เงื่อนไข). จากนั้นในหน้าต่างที่เปิดขึ้น ให้ป้อนเงื่อนไขการทดสอบต่างๆ ด้วยค่าเอาต์พุตที่ตรงกัน:

มันยังคงคลิกที่ OK และอัปโหลดรายงานผลลัพธ์ไปยัง Excel โดยใช้ปุ่มเดียวกัน ปิดและดาวน์โหลด (ปิด & โหลด) แถบ หน้าแรก (บ้าน):

ความงาม

นอกจากนี้ หากมีการเปลี่ยนแปลงใดๆ ในรายการราคาในอนาคต (บรรทัดจะถูกเพิ่มหรือลบ การเปลี่ยนแปลงราคา ฯลฯ) เพียงแค่อัปเดตคำขอของเราด้วยแป้นพิมพ์ลัดก็เพียงพอแล้ว Ctrl+อื่น ๆ+F5 หรือโดยปุ่ม รีเฟรชทั้งหมด (รีเฟรชทั้งหมด) แถบ ข้อมูล (วันที่).

ข้อดี: อาจเป็นวิธีที่สวยงามและสะดวกที่สุดก็ได้ ทำงานอย่างชาญฉลาดกับโต๊ะขนาดใหญ่ ไม่ต้องการการแก้ไขด้วยตนเองเมื่อปรับขนาดตาราง

จุดด้อย: ต้องติดตั้ง Add-in ของ Power Query (ใน Excel 2010-2013) หรือ Excel 2016 ต้องไม่เปลี่ยนชื่อคอลัมน์ในแหล่งข้อมูล มิฉะนั้น เราจะได้รับข้อผิดพลาด “ไม่พบคอลัมน์ดังกล่าวและไม่พบ!” เมื่อพยายามอัปเดตแบบสอบถาม

• วิธีรวบรวมข้อมูลจากไฟล์ Excel ทั้งหมดในโฟลเดอร์ที่กำหนดโดยใช้ Power Query
• วิธีค้นหารายการที่ตรงกันระหว่างสองรายการใน Excel
• รวมสองรายการโดยไม่ซ้ำกัน