หนึ่งในความสามารถมากมายของ Microsoft Excel คือความสามารถในการเปรียบเทียบรายการข้อมูลสองรายการ ระบุรายการที่ตรงกันระหว่างรายการ และระบุว่ารายการใดบ้างที่พบในรายการเดียว สิ่งนี้มีประโยชน์เมื่อเปรียบเทียบบันทึกทางการเงินหรือตรวจสอบเพื่อดูว่ามีชื่อใดอยู่ในฐานข้อมูลหรือไม่ คุณสามารถใช้ฟังก์ชัน MATCH เพื่อระบุและทำเครื่องหมายระเบียนที่ตรงกันหรือไม่ตรงกัน หรือคุณสามารถใช้การจัดรูปแบบการปรับสภาพด้วยฟังก์ชัน COUNTIF ขั้นตอนต่อไปนี้จะบอกวิธีใช้แต่ละอย่างเพื่อให้ตรงกับข้อมูลของคุณ
ขั้นตอน
วิธีที่ 1 จาก 2: การระบุระเบียนด้วยฟังก์ชัน MATCH
ขั้นตอนที่ 1 คัดลอกรายการข้อมูลลงในเวิร์กชีตเดียว
Excel สามารถทำงานกับหลายแผ่นงานภายในสมุดงานเดียว หรือหลายสมุดงาน แต่คุณจะพบการเปรียบเทียบรายการได้ง่ายขึ้น ถ้าคุณคัดลอกข้อมูลลงในแผ่นงานเดียว
ขั้นตอนที่ 2 ให้แต่ละรายการเป็นตัวระบุที่ไม่ซ้ำกัน
ถ้าสองรายการของคุณไม่ได้ใช้วิธีการทั่วไปในการระบุ คุณอาจต้องเพิ่มคอลัมน์เพิ่มเติมลงในรายการข้อมูลแต่ละรายการที่ระบุรายการนั้นใน Excel เพื่อให้สามารถดูว่ารายการในรายการที่ระบุเกี่ยวข้องกับรายการใดรายการหนึ่งหรือไม่ ในรายการอื่น ลักษณะของตัวระบุนี้จะขึ้นอยู่กับประเภทของข้อมูลที่คุณพยายามจะจับคู่ คุณจะต้องมีตัวระบุสำหรับรายการคอลัมน์แต่ละรายการ
- สำหรับข้อมูลทางการเงินที่เกี่ยวข้องกับช่วงเวลาที่กำหนด เช่น บันทึกภาษี นี่อาจเป็นคำอธิบายของสินทรัพย์ วันที่ที่ได้มาซึ่งสินทรัพย์ หรือทั้งสองอย่าง ในบางกรณี รายการอาจถูกระบุด้วยหมายเลขรหัส อย่างไรก็ตาม ถ้าระบบเดียวกันไม่ได้ใช้สำหรับทั้งสองรายการ ตัวระบุนี้อาจสร้างการจับคู่ที่ไม่มีหรือละเว้นรายการที่ตรงกันที่ควรจะทำ
- ในบางกรณี คุณสามารถนำรายการจากรายการหนึ่งและรวมเข้ากับรายการจากรายการอื่นเพื่อสร้างตัวระบุ เช่น คำอธิบายสินทรัพย์ทางกายภาพและปีที่ซื้อ ในการสร้างตัวระบุดังกล่าว คุณจะต้องเชื่อมข้อมูล (เพิ่ม รวม) ข้อมูลจากเซลล์ตั้งแต่สองเซลล์ขึ้นไปโดยใช้เครื่องหมายและ (&) ในการรวมคำอธิบายรายการในเซลล์ F3 กับวันที่ในเซลล์ G3 โดยคั่นด้วยการเว้นวรรค คุณจะต้องป้อนสูตร '=F3&" "&G3' ในเซลล์อื่นในแถวนั้น เช่น E3 หากคุณต้องการรวมเฉพาะปีในตัวระบุ (เนื่องจากรายการหนึ่งใช้วันที่แบบเต็มและอีกรายการใช้เฉพาะปี) คุณจะต้องรวมฟังก์ชัน YEAR โดยป้อน '=F3&" "&YEAR(G3)' ในเซลล์ E3 แทน (อย่าใส่เครื่องหมายอัญประกาศเดี่ยว ใช้เพื่อระบุตัวอย่างเท่านั้น)
- เมื่อคุณสร้างสูตรแล้ว คุณสามารถคัดลอกสูตรไปยังเซลล์อื่นๆ ทั้งหมดของคอลัมน์ตัวระบุได้โดยการเลือกเซลล์ที่มีสูตรแล้วลากที่จับเติมไปไว้เหนือเซลล์อื่นๆ ของคอลัมน์ที่คุณต้องการคัดลอกสูตร เมื่อคุณปล่อยปุ่มเมาส์ แต่ละเซลล์ที่คุณลากไปจะถูกเติมด้วยสูตร โดยการอ้างอิงเซลล์จะปรับเป็นเซลล์ที่เหมาะสมในแถวเดียวกัน
ขั้นตอนที่ 3 สร้างมาตรฐานของข้อมูลหากทำได้
ในขณะที่จิตสำนึกว่า "อิงค์" และ "Incorporated" หมายถึงสิ่งเดียวกัน Excel จะไม่ทำจนกว่าคุณจะจัดรูปแบบใหม่หรืออีกคำหนึ่ง ในทำนองเดียวกัน คุณอาจถือว่าค่าต่างๆ เช่น $11, 950 และ $11, 999.95 ใกล้เคียงกันมากพอที่จะจับคู่ได้ แต่ Excel จะไม่ทำอย่างนั้นเว้นแต่คุณจะบอกให้ทำ
- คุณสามารถจัดการกับคำย่อบางตัวได้ เช่น "Co" สำหรับ "บริษัท" และ "Inc" สำหรับ "Incorporated โดยใช้ฟังก์ชันสตริง LEFT เพื่อตัดทอนอักขระเพิ่มเติม คำย่ออื่นๆ เช่น "Assn" สำหรับ "Association, " อาจดีที่สุด จัดการโดยจัดทำคู่มือรูปแบบการป้อนข้อมูลแล้วเขียนโปรแกรมเพื่อค้นหาและแก้ไขรูปแบบที่ไม่เหมาะสม
- สำหรับสตริงของตัวเลข เช่น รหัสไปรษณีย์ ซึ่งบางรายการมีส่วนต่อท้าย ZIP+4 และบางรายการไม่มี คุณสามารถใช้ฟังก์ชันสตริง LEFT อีกครั้งเพื่อจดจำและจับคู่เฉพาะรหัสไปรษณีย์หลักเท่านั้น เมื่อต้องการให้ Excel รู้จักค่าตัวเลขที่ใกล้เคียงแต่ไม่เหมือนกัน คุณสามารถใช้ฟังก์ชัน ROUND เพื่อปัดเศษค่าที่ใกล้เคียงให้เป็นตัวเลขเดียวกันและจับคู่ได้
- ช่องว่างเพิ่มเติม เช่น การพิมพ์ช่องว่างระหว่างคำสองคำแทนที่จะเป็นหนึ่งคำ สามารถลบออกได้โดยใช้ฟังก์ชัน TRIM
ขั้นตอนที่ 4 สร้างคอลัมน์สำหรับสูตรเปรียบเทียบ
เช่นเดียวกับที่คุณต้องสร้างคอลัมน์สำหรับตัวระบุรายการ คุณจะต้องสร้างคอลัมน์สำหรับสูตรที่ทำการเปรียบเทียบสำหรับคุณ คุณจะต้องมีหนึ่งคอลัมน์สำหรับแต่ละรายการ
คุณจะต้องติดป้ายกำกับคอลัมน์เหล่านี้ด้วยข้อความเช่น "หายไป?"
ขั้นตอนที่ 5. ป้อนสูตรเปรียบเทียบในแต่ละเซลล์
สำหรับสูตรเปรียบเทียบ คุณจะใช้ฟังก์ชัน MATCH ที่ซ้อนอยู่ในฟังก์ชันอื่นของ Excel นั่นคือ ISNA
- สูตรนี้อยู่ในรูปแบบ "=ISNA(MATCH(G3, $L$3:$L$14, FALSE))" โดยที่เซลล์ของคอลัมน์ตัวระบุของรายการแรกจะถูกเปรียบเทียบกับตัวระบุแต่ละรายการในรายการที่สองกับ ดูว่าตรงกับหนึ่งในนั้นหรือไม่ หากไม่ตรงกัน ระเบียนจะหายไป และคำว่า "TRUE" จะปรากฏในเซลล์นั้น หากตรงกัน จะมีบันทึก และคำว่า "FALSE" จะปรากฏขึ้น (เมื่อป้อนสูตรไม่ต้องใส่เครื่องหมายคำพูดแนบ)
- คุณสามารถคัดลอกสูตรไปยังเซลล์ที่เหลือของคอลัมน์ได้ด้วยวิธีเดียวกับที่คุณคัดลอกสูตรตัวระบุเซลล์ ในกรณีนี้ เฉพาะการอ้างอิงเซลล์สำหรับเซลล์ตัวระบุเท่านั้นที่เปลี่ยนแปลง เนื่องจากการวางเครื่องหมายดอลลาร์ไว้หน้าการอ้างอิงแถวและคอลัมน์สำหรับเซลล์แรกและเซลล์สุดท้ายในรายการของตัวระบุเซลล์ที่สองทำให้เป็นข้อมูลอ้างอิงแบบสัมบูรณ์
- คุณสามารถคัดลอกสูตรเปรียบเทียบสำหรับรายการแรกลงในเซลล์แรกของคอลัมน์สำหรับรายการที่สองได้ จากนั้น คุณจะต้องแก้ไขการอ้างอิงเซลล์เพื่อให้ "G3" ถูกแทนที่ด้วยข้อมูลอ้างอิงสำหรับเซลล์ตัวระบุแรกของรายการที่สอง และ "$L$3:$L$14" จะถูกแทนที่ด้วยเซลล์ตัวระบุแรกและตัวสุดท้ายของ รายการที่สอง (ปล่อยให้เครื่องหมายดอลลาร์และโคลอนอยู่คนเดียว) จากนั้นคุณสามารถคัดลอกสูตรที่แก้ไขนี้ลงในเซลล์ที่เหลือในแถวเปรียบเทียบของรายการที่สอง
ขั้นตอนที่ 6 จัดเรียงรายการเพื่อดูค่าที่ไม่ตรงกันได้ง่ายขึ้นหากจำเป็น
หากรายการของคุณมีขนาดใหญ่ คุณอาจต้องจัดเรียงรายการเพื่อรวมค่าที่ไม่ตรงกันทั้งหมดไว้ด้วยกัน คำแนะนำในขั้นตอนย่อยด้านล่างจะแปลงสูตรเป็นค่าเพื่อหลีกเลี่ยงข้อผิดพลาดในการคำนวณใหม่ และหากรายการของคุณมีขนาดใหญ่ จะช่วยหลีกเลี่ยงเวลาในการคำนวณใหม่ที่ยาวนาน
- ลากเมาส์ไปที่เซลล์ทั้งหมดในรายการเพื่อเลือก
- เลือกคัดลอกจากเมนูแก้ไขใน Excel 2003 หรือจากกลุ่มคลิปบอร์ดของ Ribbon หน้าแรกใน Excel 2007 หรือ 2010
- เลือกวางแบบพิเศษจากเมนูแก้ไขใน Excel 2003 หรือจากปุ่มดร็อปดาวน์วางในกลุ่มคลิปบอร์ดของริบบิ้นหน้าแรกของ Excel 2007 หรือ 2010
- เลือก "ค่า" จากรายการ วางเป็น ในกล่องโต้ตอบ วางแบบพิเศษ คลิกตกลงเพื่อปิดกล่องโต้ตอบ
- เลือกเรียงลำดับจากเมนูข้อมูลใน Excel 2003 หรือกลุ่มเรียงลำดับและกรองของ ribbon ข้อมูลใน Excel 2007 หรือ 2010
- เลือก "แถวส่วนหัว" จากรายการ "ช่วงข้อมูลของฉันมี" ในกล่องโต้ตอบ "จัดเรียงตาม" เลือก "หายไปหรือไม่" (หรือชื่อที่คุณตั้งไว้สำหรับส่วนหัวของคอลัมน์เปรียบเทียบจริงๆ) แล้วคลิกตกลง
- ทำซ้ำขั้นตอนเหล่านี้สำหรับรายการอื่น
ขั้นตอนที่ 7 เปรียบเทียบรายการที่ไม่ตรงกันเพื่อดูว่าเหตุใดจึงไม่ตรงกัน
ดังที่กล่าวไว้ก่อนหน้านี้ Excel ได้รับการออกแบบมาเพื่อค้นหาข้อมูลที่ตรงกัน เว้นแต่คุณจะตั้งค่าให้ค้นหาข้อมูลโดยประมาณ การจับคู่ที่ไม่ตรงกันของคุณอาจเป็นเรื่องง่ายๆ เหมือนกับการสลับตัวอักษรหรือตัวเลขโดยไม่ได้ตั้งใจ นอกจากนี้ยังอาจเป็นสิ่งที่ต้องมีการตรวจสอบอย่างอิสระ เช่น การตรวจสอบเพื่อดูว่าสินทรัพย์ที่อยู่ในรายการจำเป็นต้องได้รับการรายงานตั้งแต่แรกหรือไม่
วิธีที่ 2 จาก 2: การจัดรูปแบบตามเงื่อนไขด้วย COUNTIF
ขั้นตอนที่ 1 คัดลอกรายการข้อมูลลงในเวิร์กชีตเดียว
ขั้นตอนที่ 2 ตัดสินใจเลือกรายการที่คุณต้องการเน้นเรกคอร์ดที่ตรงกันหรือไม่ตรงกัน
ถ้าคุณต้องการเน้นระเบียนในรายการเดียว คุณอาจต้องการเน้นระเบียนที่ไม่ซ้ำกับรายการนั้น นั่นคือระเบียนที่ไม่ตรงกับระเบียนในรายการอื่น ถ้าคุณต้องการเน้นระเบียนในทั้งสองรายการ คุณจะต้องเน้นระเบียนที่ตรงกัน สำหรับจุดประสงค์ของตัวอย่างนี้ เราจะถือว่ารายการแรกใช้เซลล์ G3 ถึง G14 และรายการที่สองใช้เซลล์ L3 ถึง L14
ขั้นตอนที่ 3 เลือกรายการในรายการที่คุณต้องการเน้นรายการที่ไม่ซ้ำหรือตรงกัน
หากคุณต้องการเน้นรายการที่ตรงกันในทั้งสองรายการ คุณจะต้องเลือกทีละรายการและใช้สูตรเปรียบเทียบ (ที่อธิบายไว้ในขั้นตอนต่อไป) กับแต่ละรายการ
ขั้นตอนที่ 4 ใช้สูตรเปรียบเทียบที่เหมาะสม
เมื่อต้องการทำเช่นนี้ คุณจะต้องเข้าถึงกล่องโต้ตอบการจัดรูปแบบตามเงื่อนไขใน Excel เวอร์ชันของคุณ ใน Excel 2003 คุณทำได้โดยเลือกการจัดรูปแบบตามเงื่อนไขจากเมนูรูปแบบ ขณะที่ใน Excel 2007 และ 2010 คุณคลิกปุ่มการจัดรูปแบบตามเงื่อนไขในกลุ่มลักษณะของริบบิ้นหน้าแรก เลือกประเภทกฎเป็น "สูตร" และป้อนสูตรของคุณในฟิลด์ แก้ไขคำอธิบายกฎ
- ถ้าคุณต้องการเน้นระเบียนที่ไม่ซ้ำในรายการแรก สูตรจะเป็น "=COUNTIF($L$3:$L$14, G3=0)" โดยช่วงของเซลล์ของรายการที่สองจะแสดงเป็นค่าสัมบูรณ์และข้อมูลอ้างอิง ไปยังเซลล์แรกของรายการแรกเป็นค่าสัมพัทธ์ (อย่าใส่เครื่องหมายคำพูดปิด)
- ถ้าคุณต้องการเน้นระเบียนที่ไม่ซ้ำในรายการที่สอง สูตรจะเป็น "=COUNTIF($G$3:$G$14, L3=0)" โดยช่วงของเซลล์ของรายการแรกจะแสดงเป็นค่าสัมบูรณ์และข้อมูลอ้างอิง ไปยังเซลล์แรกของรายการที่สองเป็นค่าสัมพัทธ์ (อย่าใส่เครื่องหมายคำพูดปิด)
- ถ้าคุณต้องการเน้นระเบียนในแต่ละรายการที่พบในรายการอื่น คุณจะต้องมีสูตรสองสูตร สูตรหนึ่งสำหรับรายการแรกและสูตรหนึ่งสำหรับรายการที่สอง สูตรสำหรับรายการแรกคือ "=COUNTIF($L$3:$L$14, G3>0)" ในขณะที่สูตรสำหรับรายการที่สองคือ COUNTIF($G$3:$G$14, L3>0)" เช่น คุณเลือกรายการแรกเพื่อใช้สูตร แล้วเลือกรายการที่สองเพื่อนำสูตรไปใช้
- ใช้การจัดรูปแบบใดก็ได้ที่คุณต้องการเน้นระเบียนที่ถูกตั้งค่าสถานะ คลิกตกลงเพื่อปิดกล่องโต้ตอบ
วิดีโอ - การใช้บริการนี้ อาจมีการแบ่งปันข้อมูลบางอย่างกับ YouTube
เคล็ดลับ
- แทนที่จะใช้การอ้างอิงเซลล์ด้วยวิธีการจัดรูปแบบตามเงื่อนไข COUNTIF คุณสามารถป้อนค่าที่จะค้นหาและตั้งค่าสถานะรายการอย่างน้อยหนึ่งรายการสำหรับอินสแตนซ์ของค่านั้น
- เพื่อให้แบบฟอร์มเปรียบเทียบง่ายขึ้น คุณสามารถสร้างชื่อสำหรับรายการของคุณ เช่น "List1" และ "List2" จากนั้น เมื่อเขียนสูตร ชื่อรายการเหล่านี้สามารถแทนที่ช่วงเซลล์สัมบูรณ์ที่ใช้ในตัวอย่างด้านบน
