เมื่อเปรียบเทียบรายการเทียบเคียงหลายรายการใน สเปรดชีต Excelข้อมูลมักจะถูกจัดเรียงเป็นคอลัมน์เพื่อให้ง่ายต่อการเปรียบเทียบลักษณะของวัตถุเหล่านี้ทีละบรรทัด ตัวอย่างเช่นรุ่นรถยนต์โทรศัพท์กลุ่มทดลองและกลุ่มควบคุมร้านค้าปลีกจำนวนมากเป็นต้นด้วยเส้นจำนวนมากการวิเคราะห์ด้วยภาพจึงไม่สามารถเชื่อถือได้ ฟังก์ชัน VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) สะดวกในการเปรียบเทียบข้อมูลตามเซลล์และไม่ให้ภาพรวม คุณจะทราบได้อย่างไรว่าคอลัมน์โดยทั่วไปมีความคล้ายคลึงกันอย่างไร คอลัมน์เหมือนกันไหม
Add-in คอลัมน์แผนที่ช่วยให้คุณสามารถแมปคอลัมน์และดูภาพรวมได้:
- เปรียบเทียบคอลัมน์สองคอลัมน์ขึ้นไปซึ่งกันและกัน
- เปรียบเทียบคอลัมน์กับค่าอ้างอิง
- คำนวณเปอร์เซ็นต์การจับคู่แบบตรงทั้งหมด
- นำเสนอผลลัพธ์ในตารางเดือยที่มองเห็นได้
ภาษาของวิดีโอ: อังกฤษ คำบรรยาย: รัสเซีย, อังกฤษ (ข้อควรระวัง: วิดีโออาจไม่สะท้อน อัพเดทล่าสุด... ใช้คำแนะนำด้านล่าง)
เพิ่ม "Match Columns" ใน Excel 2016, 2013, 2010, 2007
เหมาะสำหรับ: Microsoft Excel 2016-2007 เดสก์ท็อป Office 365 (32 บิตและ 64 บิต)
วิธีทำงานกับ Add-in:
วิธีเปรียบเทียบคอลัมน์ตั้งแต่สองคอลัมน์ขึ้นไปและคำนวณเปอร์เซ็นต์การจับคู่
พิจารณาตัวอย่างการพัฒนาผลิตภัณฑ์ สมมติว่าคุณจำเป็นต้องเปรียบเทียบต้นแบบที่เสร็จแล้วหลายชิ้นด้วยกันและดูว่ามันเหมือนกันแตกต่างกันอย่างไรและอาจเหมือนกันได้อย่างไร
- คลิกปุ่ม Map Columns ในแถบเครื่องมือ XLTools\u003e เลือก Map Columns ซึ่งกันและกัน
- คลิกตกลง\u003e
คำแนะนำ:
โปรดเลือก ตารางสรุป ผลลัพธ์\u003e คลิกที่ไอคอน Express Analysis\u003e ใช้ "Color Scale"
การอ่านผลลัพธ์: ต้นแบบแบบที่ 1 และแบบที่ 3 เกือบจะเหมือนกันอัตราการจับคู่ 99% หมายความว่า 99% ของพารามิเตอร์ในบรรทัดเหมือนกัน ประเภทที่ 2 และประเภท 4 มีความคล้ายคลึงกันน้อยที่สุด - พารามิเตอร์ตรงกันเพียง 30%
วิธีเปรียบเทียบคอลัมน์กับค่าอ้างอิงและคำนวณระดับการจับคู่
พิจารณาตัวอย่างการพัฒนาผลิตภัณฑ์ สมมติว่าคุณต้องเปรียบเทียบต้นแบบสำเร็จรูปหลายตัวกับมาตรฐานเป้าหมายที่กำหนดและคำนวณว่าต้นแบบนั้นตรงตามมาตรฐานเหล่านั้นได้ดีเพียงใด
- เลือกคอลัมน์ที่จะเปรียบเทียบ
ตัวอย่างเช่นคอลัมน์ที่มีข้อมูลต้นแบบ - คลิกปุ่ม Map Columns ในแถบเครื่องมือ XLTools
- เลือกแผนที่ไปยังช่วงคอลัมน์อ้างอิง\u003e เลือกคอลัมน์อ้างอิง
ตัวอย่างเช่นคอลัมน์ที่มีมาตรฐาน - เลือก "คอลัมน์มีชื่อเรื่อง" หากมี
- เลือก "แสดงเปอร์เซ็นต์การจับคู่" เพื่อแสดงการจับคู่เป็นเปอร์เซ็นต์
มิฉะนั้นผลลัพธ์จะแสดงเป็น 1 (การแข่งขันแบบเต็ม) หรือ 0 (ไม่ตรงกัน) - ระบุตำแหน่งที่ควรวางผลลัพธ์: บนแผ่นงานใหม่หรือบนแผ่นงานที่มีอยู่
- คลิกตกลง\u003e เสร็จสิ้นผลลัพธ์จะปรากฏในตาราง Pivot
คำแนะนำ: เพื่อให้ง่ายต่อการตีความผลลัพธ์ให้ใช้การจัดรูปแบบตามเงื่อนไขกับมัน:
เลือกตาราง Pivot ของผลลัพธ์\u003e คลิกที่ไอคอนการวิเคราะห์ด่วน\u003e ใช้ "มาตราส่วนสี"
การอ่านผลลัพธ์: ต้นแบบ Type 2 เป็นไปตามมาตรฐาน 2 99% นั่นคือ 99% ของพารามิเตอร์ในสตริงเหมือนกัน ผลิตภัณฑ์ 5 ใกล้เคียงกับมาตรฐาน 3 มากที่สุด - 96% ของพารามิเตอร์เหมือนกัน ในขณะเดียวกันผลิตภัณฑ์ 4 ยังห่างไกลจากการปฏิบัติตามมาตรฐานสามข้อใด ๆ ตอนนี้เราสามารถสรุปได้ว่าแต่ละต้นแบบเบี่ยงเบนไปจากค่าอ้างอิงเป้าหมายเท่าใด
Add-in Match Columns จะช่วยงานอะไรบ้าง
Add-in จะสแกนเซลล์ทีละบรรทัดและคำนวณเปอร์เซ็นต์ของค่าที่เหมือนกันในคอลัมน์ XLTools Match Columns ไม่เหมาะสำหรับการเปรียบเทียบค่าปกติในเซลล์ - ไม่ได้ออกแบบมาเพื่อค้นหาค่าที่ซ้ำกันหรือค่าที่ไม่ซ้ำกัน
Add-in คอลัมน์แผนที่มีวัตถุประสงค์ที่แตกต่างกัน ภารกิจหลักคือการค้นหาว่าโดยทั่วไปชุดข้อมูล (คอลัมน์) มีความเหมือนหรือแตกต่างกันอย่างไร Add-on ช่วยในการวิเคราะห์ข้อมูลจำนวนมากเมื่อคุณต้องการดูระดับมหภาคที่กว้างขึ้นเช่น ตอบคำถามดังกล่าว:
- ประสิทธิภาพของกลุ่มทดลองมีความคล้ายคลึงกันเพียงใด
- ผลลัพธ์ของกลุ่มทดลองและกลุ่มควบคุมมีความคล้ายคลึงกันเพียงใด
- ผลิตภัณฑ์หลายประเภทที่เหมือนกัน / แตกต่างกันอย่างไร
- KPI ของพนักงานใกล้เคียงกับตัวชี้วัดที่วางแผนไว้แค่ไหน?
- ตัวบ่งชี้ของร้านค้าปลีกต่างๆ ฯลฯ มีความคล้ายคลึงกันเพียงใด
คำแนะนำ
ใช้ฟังก์ชันการเปรียบเทียบเซลล์ COUNTIF ในตัวหากคุณต้องการเปรียบเทียบค่าข้อความในเซลล์คอลัมน์ของตารางกับข้อความตัวอย่างและคำนวณใหม่ที่ตรงกันทั้งหมด เริ่มต้นด้วยการกรอกค่าข้อความในคอลัมน์จากนั้นในคอลัมน์อื่นให้คลิกเซลล์ที่คุณต้องการดูผลการนับและป้อนสูตรที่เหมาะสม ตัวอย่างเช่นหากค่าที่เลือกอยู่ในคอลัมน์ A และควรวางผลลัพธ์ไว้ในเซลล์แรกของคอลัมน์ C เนื้อหาควรเป็นดังนี้: \u003d COUNTIF ($ A: $ A; "Grape") ที่นี่ "Grape" คือค่าสตริงที่ จะเปรียบเทียบค่าของเซลล์ทั้งหมดในคอลัมน์ A คุณสามารถข้ามการระบุในสูตรได้ แต่วางไว้ในเซลล์แยกต่างหาก (เช่นใน B1) และแทรกลิงก์ที่เกี่ยวข้องลงในสูตร: \u003d COUNTIF ($ A: $ A; B1)
ใช้ตัวเลือก การจัดรูปแบบตามเงื่อนไขหากคุณต้องการเน้นให้เห็นผลลัพธ์ของการเปรียบเทียบตัวแปรสตริงในตาราง ตัวอย่างเช่นหากคุณต้องการเลือกเซลล์ในคอลัมน์ A ข้อความที่ตรงกับรูปแบบในเซลล์ B1 จากนั้นเริ่มต้นด้วยการเลือกคอลัมน์นี้ - คลิกที่ส่วนหัว จากนั้นคลิกปุ่มการจัดรูปแบบตามเงื่อนไขในกลุ่มคำสั่งสไตล์บนแท็บหน้าแรกบนเมนู Excel ไปที่ส่วน "กฎการเลือกเซลล์" แล้วเลือกบรรทัด "เท่ากับ" ในหน้าต่างที่เปิดขึ้นให้ระบุเซลล์ตัวอย่าง (คลิกเซลล์ B1) แล้วเลือกตัวเลือกสำหรับบรรทัดที่ตรงกันในรายการดรอปดาวน์ จากนั้นคลิกปุ่ม "ตกลง"
ใช้การรวมกันของฟังก์ชัน IF และ CONCATENATE ในตัวเมื่อคุณต้องการจับคู่เซลล์ข้อความมากกว่าหนึ่งเซลล์กับรูปแบบ ฟังก์ชัน CONCATENATE เชื่อมต่อค่าที่ระบุเป็นตัวแปรสตริงเดียว ตัวอย่างเช่นคำสั่ง CONCATENATE (A1; "และ"; B1) จะเพิ่มข้อความ "และ" ลงในแถวจากเซลล์ A1 และหลังจากนั้นจะวางแถวจากเซลล์ B1 จากนั้นสตริงที่สร้างด้วยวิธีนี้สามารถเปรียบเทียบกับรูปแบบโดยใช้ฟังก์ชัน IF เมื่อคุณต้องการเปรียบเทียบมากกว่าหนึ่งสตริงการตั้งชื่อของคุณเองให้กับเซลล์ตัวอย่างจะสะดวกกว่า ในการดำเนินการนี้ให้คลิกและทางด้านซ้ายของ สตริง สูตรแทนชื่อเซลล์ (เช่น C1) ให้พิมพ์ชื่อใหม่ (ตัวอย่างเช่น "ตัวอย่าง") จากนั้นคลิกเซลล์ที่ผลลัพธ์ของการเปรียบเทียบควรจะเป็นแล้วป้อนสูตร: IF (CONCATE (A1; "and"; B1) \u003d sample; 1; 0) หน่วยที่นี่คือค่าที่เซลล์ที่มีสูตรจะมีหากการเปรียบเทียบ จะให้ผลลัพธ์ที่เป็นบวกและเป็นศูนย์สำหรับผลลัพธ์ที่เป็นลบ คูณสูตรนี้สำหรับทุกอย่าง สตริง ตารางที่ต้องเปรียบเทียบกับตัวอย่างนั้นง่ายมาก - เลื่อนเคอร์เซอร์ไปที่มุมล่างขวาของเซลล์และเมื่อเคอร์เซอร์เปลี่ยนไป (กลายเป็นกากบาทสีดำ) ให้กดปุ่มซ้ายของเมาส์แล้วลากเซลล์นี้ลงไปที่เซลล์สุดท้ายเพื่อทำการเปรียบเทียบ สตริง.
นี่คือบทหนึ่งจากหนังสือ: Michael Girwin Ctrl + Shift + Enter การเรียนรู้สูตรอาร์เรย์ใน Excel
ตัวอย่างตามเงื่อนไขอย่างน้อยหนึ่งข้อแถว ฟังก์ชัน Excel ใช้ตัวดำเนินการเปรียบเทียบ ตัวอย่างเช่น SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF และ AVERAGEIFS ฟังก์ชันเหล่านี้ทำการเลือกตามเงื่อนไข (เกณฑ์) อย่างน้อยหนึ่งข้อ ปัญหาคือฟังก์ชันเหล่านี้สามารถเพิ่มนับและค่าเฉลี่ยเท่านั้น จะเป็นอย่างไรหากคุณต้องการกำหนดเงื่อนไขในการค้นหาของคุณเช่นค่าสูงสุดหรือส่วนเบี่ยงเบนมาตรฐาน ในกรณีเหล่านี้เนื่องจากไม่มีฟังก์ชันในตัวคุณจึงต้องคิดค้นสูตรอาร์เรย์ มักเกิดจากการใช้ตัวดำเนินการเปรียบเทียบอาร์เรย์ ตัวอย่างแรกในบทนี้แสดงวิธีการคำนวณค่าต่ำสุดภายใต้เงื่อนไขเดียว
มาใช้ฟังก์ชัน IF เพื่อเลือกองค์ประกอบอาร์เรย์ที่ตรงตามเงื่อนไข ในรูป 4.1 ในตารางด้านซ้ายมีคอลัมน์ที่มีชื่อเมืองและคอลัมน์พร้อมเวลา จำเป็นต้องหาเวลาต่ำสุดสำหรับแต่ละเมืองและวางค่านี้ในเซลล์ที่เกี่ยวข้องในตารางด้านขวา เงื่อนไขในการคัดเลือกคือชื่อเมือง หากคุณใช้ฟังก์ชัน MIN คุณจะหาค่าต่ำสุดสำหรับคอลัมน์ B ได้ แต่คุณจะเลือกเฉพาะตัวเลขที่ใช้กับโอ๊คแลนด์ได้อย่างไร และคุณจะคัดลอกสูตรลงในคอลัมน์ได้อย่างไร? เนื่องจาก Excel ไม่มีฟังก์ชัน MINESLI ในตัวคุณจึงต้องเขียนสูตรดั้งเดิมที่รวมฟังก์ชัน IF และ MIN
รูป: 4.1. วัตถุประสงค์ของสูตร: เลือกเวลาขั้นต่ำสำหรับแต่ละเมือง
ดาวน์โหลดบันทึกในรูปแบบหรือรูปแบบ
ดังแสดงในรูป 4.2 คุณควรเริ่มป้อนสูตรในเซลล์ E3 ด้วยฟังก์ชัน MIN แต่คุณไม่สามารถโต้แย้งได้ หมายเลข 1 ค่าทั้งหมดในคอลัมน์ B !? คุณต้องการเลือกเฉพาะค่าที่เฉพาะเจาะจงสำหรับโอ๊คแลนด์
ดังแสดงในรูป 4.3 ในขั้นตอนต่อไปให้ป้อนฟังก์ชัน IF เป็นอาร์กิวเมนต์ หมายเลข 1 เป็นเวลา MIN. คุณใส่ IF ภายใน MIN
โดยการวางเคอร์เซอร์ในตำแหน่งที่ป้อนอาร์กิวเมนต์ log_expression ฟังก์ชัน IF (รูปที่ 4.4) คุณเลือกช่วงที่มีชื่อเมือง A3: A8 จากนั้นกด F4 เพื่อให้การอ้างอิงเซลล์เป็นค่าสัมบูรณ์ (ดูตัวอย่างสำหรับรายละเอียดเพิ่มเติม) จากนั้นคุณพิมพ์ตัวดำเนินการเปรียบเทียบเครื่องหมายเท่ากับ สุดท้ายคุณจะเลือกเซลล์ทางด้านซ้ายของสูตร - D3 โดยให้การอ้างอิงถึงมันสัมพันธ์กัน เงื่อนไขที่กำหนดจะอนุญาตให้คุณเลือกเฉพาะ Aucklands เมื่อดูช่วง A3: A8
รูป: 4.4. สร้างตัวดำเนินการอาร์เรย์ในอาร์กิวเมนต์ log_expression ฟังก์ชัน IF
คุณได้สร้างตัวดำเนินการอาร์เรย์โดยใช้ตัวดำเนินการเปรียบเทียบ เมื่อใดก็ได้ในระหว่างการประมวลผลอาร์เรย์ตัวดำเนินการอาร์เรย์จะเป็นตัวดำเนินการเปรียบเทียบดังนั้นผลลัพธ์ของการดำเนินการจะเป็นอาร์เรย์ของค่า TRUE และ FALSE ในการตรวจสอบสิ่งนี้ให้เลือกอาร์เรย์ (ในการดำเนินการนี้ให้คลิกในคำแนะนำเครื่องมือบนอาร์กิวเมนต์ log_expression) และกด F9 (รูปที่ 4.5) คุณมักจะใช้อาร์กิวเมนต์เดียว log_expression, ส่งคืน TRUE หรือ FALSE; ที่นี่อาร์เรย์ผลลัพธ์จะส่งคืนค่า TRUE และ FALSE หลายค่าดังนั้นฟังก์ชัน MIN จะเลือกจำนวนต่ำสุดสำหรับเมืองที่ตรงกับค่า TRUE เท่านั้น
รูป: 4.5. หากต้องการดูอาร์เรย์ของค่า TRUE และ FALSE ให้คลิกในคำแนะนำเครื่องมือบนอาร์กิวเมนต์ log_expression แล้วกด F9
เรามีสองตารางคำสั่งที่คัดลอกลงในแผ่นงานเดียว จำเป็นต้องทำการเปรียบเทียบข้อมูลของสองตารางใน Excel และตรวจสอบว่าตำแหน่งใดอยู่ในตารางแรก แต่ไม่ใช่ในตารางที่สอง ไม่มีประเด็นในการเปรียบเทียบค่าของแต่ละเซลล์ด้วยตนเอง
เปรียบเทียบสองคอลัมน์สำหรับรายการที่ตรงกันใน Excel
จะทำการเปรียบเทียบค่าใน Excel ของสองคอลัมน์ได้อย่างไร? ในการแก้ปัญหานี้เราขอแนะนำให้ใช้การจัดรูปแบบตามเงื่อนไขซึ่งสามารถเน้นรายการที่มีสีเพียงคอลัมน์เดียวได้อย่างรวดเร็ว แผ่นงานพร้อมตาราง:
ขั้นตอนแรกคือการกำหนดชื่อให้กับทั้งสองตาราง สิ่งนี้ช่วยให้เข้าใจได้ง่ายขึ้นว่ากำลังเปรียบเทียบช่วงเซลล์ใด:
- เลือกเครื่องมือ FORMULAS - Defined Names - Assign Name
- ในหน้าต่างที่ปรากฏขึ้นในช่อง "ชื่อ:" ให้ป้อนค่า - Table_1
- คลิกซ้ายที่ช่องป้อนข้อมูล "ช่วง:" และเลือกช่วง: A2: A15 แล้วคลิกตกลง
สำหรับรายการที่สองให้ทำเช่นเดียวกัน แต่กำหนดชื่อ - Table_2 และระบุช่วง C2: C15 - ตามลำดับ
คำแนะนำที่เป็นประโยชน์! สามารถกำหนดชื่อช่วงได้เร็วขึ้นโดยใช้ฟิลด์ชื่อ จะอยู่ทางด้านซ้ายของแถบสูตร เพียงแค่เลือกช่วงของเซลล์และในฟิลด์ชื่อพิมพ์ชื่อที่เหมาะสมสำหรับช่วงแล้วกด Enter
ตอนนี้ให้ใช้การจัดรูปแบบตามเงื่อนไขเพื่อทำการเปรียบเทียบระหว่างสองรายการใน Excel เราจำเป็นต้องได้รับผลลัพธ์ดังต่อไปนี้:
ตำแหน่งที่อยู่ใน Table_1 แต่ไม่อยู่ใน Table_2 จะแสดงเป็นสีเขียว ในขณะเดียวกันตำแหน่งที่อยู่ใน Table_2 แต่ไม่มีอยู่ใน Table_1 จะถูกไฮไลต์เป็นสีน้ำเงิน
หลักการเปรียบเทียบข้อมูลของสองคอลัมน์ใน Excel
เราใช้ฟังก์ชัน COUNTIF เพื่อกำหนดเงื่อนไขในการจัดรูปแบบเซลล์คอลัมน์ ที่ ตัวอย่างนี้ ฟังก์ชันนี้จะตรวจสอบจำนวนครั้งที่ค่าของอาร์กิวเมนต์ที่สอง (เช่น A2) เกิดขึ้นในรายการของอาร์กิวเมนต์แรก (ตัวอย่างเช่น Table_2) ถ้าจำนวนครั้ง \u003d 0 สูตรจะส่งกลับ TRUE ในกรณีนี้เซลล์จะถูกกำหนดรูปแบบที่กำหนดเองซึ่งระบุไว้ในตัวเลือกการจัดรูปแบบตามเงื่อนไข การอ้างอิงในอาร์กิวเมนต์ที่สองเป็นแบบสัมพัทธ์ซึ่งหมายความว่าเซลล์ทั้งหมดของช่วงที่เลือกจะถูกตรวจสอบในทางกลับกัน (ตัวอย่างเช่น A2: A15) สูตรที่สองทำงานในลักษณะเดียวกัน หลักการเดียวกันนี้สามารถนำไปใช้กับงานต่างๆที่คล้ายคลึงกันได้ ตัวอย่างเช่นเพื่อเปรียบเทียบราคาสองราคาใน Excel แม้กระทั่ง
