เปรียบเทียบข้อมูลสองคอลัมท์เพื่อหาค่าที่ไม่ตรงกันใน Excel ด้วย Formatting Style

เปรียบเทียบรายการสต็อคสินค้าที่เก็บตัวเลขมาจาก2 แผนก ซึ่งแบ่งเป็น 2 คอลัมท์

คอลัมท์แรกได้มาจากฝ่ายบัญชี และคอลัมท์ที่สองมาจากการตรวจนับของสาขา

ซึ่งรายการสินค้าคงหลือจากสองแผนกมีบางรายการไม่ตรงกัน

สาเหตุอาจเกิดความผิดพลาดจากการคีย์อิน บันทึกรายการผิด หรือ บันทึกผลตรวจนับผิด ฯลฯ

สำหรับเคสลักษณะนี้สามารถหารายการที่ไม่ตรงกันใน Excel ด้วยฟังก์ชัน COUNTIF และ Auto Sort หรือใช้ Countif ไปพร้อมกับ Conditional Formatting ทีเดียวเลย ซึ่งแบบหลังรายการที่ไม่เหมือนกันจะได้แสดงตัวออกมาอย่างชัดเจน

การทำงานของฟังก์ชัน COUNTIF

ไวยากรณ์ COUNTIF(range, criteria)

Countif เป็นฟังก์ชันที่ให้นับจำนวนโดยที่มีเงื่อนไขกำกับไว้ด้วย ความหมายของ Countif คือถ้าในช่วงเซลล์ที่เลือก (range) มีรายการที่ตรงกับข้อกำหนด (criteria) ก็จะแสดงผลเป็นค่าตัวเลข

การหาข้อมูลที่แตกต่างจากการเปรียบเทียบ 2 คอลัมท์

1. เลือกเซลล์ในคอลัมท์ที่ต้องการแสดงผลการเปรียบเทียบคือเซลล์ A3 ถึง A22
2. เลือก Conditional Formatting เข้า new rule หรือคีย์ลัด Alt+H+LR  แล้วเลือก new rule)
3. เลือกรายการสุดท้าย Use a formula to determine which cells to format 
4.  ที่ใต้หัวข้อ Format value where this formula is true: ให้ใส่ฟังก์ชัน COUNTIF ตามด้านล่าง
   
   = COUNTIF(B3:B22, A3)=0  หรือ COUNTIF(B$3:B$22, A3)=0
5. อย่าลืมเข้าไปกำหนด Format เพื่อให้รายการที่แตกต่างแสดงสีอื่นออกมา

หมายเหตุ

1. รายการใน Column A ที่ไม่เหมือนกับ Column B จะแสดงแถบสีเหลือง
2. การแทนค่า Criteria ใน Countif ไม่ต้องใส่ $
3. ถ้า 2 คอลัมท์ที่เปรียบเทียบ มีการจัดเรียงข้อมูล การแทนค่าใน Countif ไม่ต้องใช้ $ ก็ได้
4. แต่ถ้าไม่มีการเรียงข้อมูล การแทนค่า Range ใน Countif ต้องมี $ หน้า Row  
5. จากภาพด้านล่างเปลี่ยนไปใช้ column A กับ K, ซึ่ง คอลัมท์ K ไม่ได้จัดเรียงข้อมูล

แสดงการเปรียบเทียบกับคอลัมท์ K ที่ไม่ได้จัดเรียงข้อมูลก็ได้ผลลัพท์อย่างเดียวกัน

การจัดลำดับด้วยฟังก์ชัน Rank ใน Excel

ฟังก์ชัน Rank

การทำงานของฟังก์ชัน Rank เหมือนกับการจัดลำดับผู้ที่เข้าสอบแข่งขัน หรือจัดอันดับทีมที่เข้าประกวดการแสดง


โดยใช้คะแนนของผู้เข้าแข่งขันทุกหน่วยเป็นค่าอ้างอิงในการจัดอันดับ
และในฟังก์ชัน Rank ก็มีเงื่อนไขที่ใช้กำหนดวีธีเรียงอันดับ 2 แบบ คือการเรียงจากมากไปน้อย หรือ เรียงจากน้อยไปมาก

ไวยากรณ์ :

RANK (number,ref,order)

• Number คือตัวเลขที่ต้องการค้นหา และเป็นหนึ่งในรายการทั้งหมด


• Ref คืออาร์เรย์หรือการอ้างอิง รายการตัวเลข และค่าที่ไม่ใช่ตัวเลขใน ref จะถูกละเว้น


• Order ตัวเลขที่ใช้กำหนดวิธีจัดเรียงถ้าไม่ระบุไว้หรือเป็น 0 จะเรียงจากมากไปน้อย(Z->A) และถ้าเป็น 1 หรือ มากกว่าจะเรียงจากน้อยไปมาก (A->Z)

สมมุติว่าค่าที่ให้จัด Rank มีค่าซ้ำกัน ฟังก์ชัน RANK จะให้ลำดับที่ซ้ำด้วยเช่นกัน และตัวเลขซ้ำจะมีผลกระทบถึงลำดับที่ของตัวเลขตัวถัดมา เช่น ถ้าในรายการที่เรียงลำดับ มีลำดับที่ 2 สองรายการ ลำดับถัดไปต้องเป็นลำดับที่ 4 คือจะข้ามลำดับที่ 3 ไป

ตัวอย่างการใช้ ฟังก์ชัน RANK
ให้จัดอันดับพนักงานขายโดยใช้ยอดขายที่ทำได้ในเดือนก่อนโดยอ้างอิงจากยอดขายเดือนที่ผ่านมา

Row	Column
	A	B	C	D	E	F
1	Sales/ยอดขาย		แทนค่าฟังก์ชัน(Z->A)	ผลลัพท์ Z->A	แทนค่าฟังก์ชัน(A->Z)	ผลลัพท์ A->Z
2	กรณ์	105	=RANK($B2,B$2:B$8)	1	=RANK($B2,B$2:B$8,1)	6
3	ไข่มุก	97	=RANK($B3,B$2:B$8)	3	=RANK($B3,B$2:B$8,2)	5
4	จุตินันท์	88	=RANK($B4,B$2:B$8,0)	5	=RANK($B4,B$2:B$8,3)	3
5	ชลิดา	105	=RANK($B5,B$2:B$8,0)	1	=RANK($B5,B$2:B$8,1)	6
6	ปีใหม่	92	=RANK($B6,B$2:B$8)	4	=RANK($B6,B$2:B$8,1)	4
7	พิชิต	79	=RANK($B7,B$2:B$8,0)	7	=RANK($B7,B$2:B$8,2)	1
8	อินทนน	81	=RANK($B8,B$2:B$8)	6	=RANK($B8,B$2:B$8,1)	2

หมายเหตุ

• การเรียงจากมากไปน้อยหรือน้อยไปมาก จะยึดจากตัวเลขยอดขายเป็นหลัก ไม่ใช่ตัวเลขของผลลัพท์ที่ใช้ฟังก์ชันแล้ว


• คอลัมท์ C ให้ Order เป็น 0 และ เว้นว่างไว้แสดงผลลัพท์ในคอลัมท์ D
  
  • กรณ์กับชลิดา ซึ่งมียอดขายเท่ากับ 105 กลายเป็นอันดับที่ 1 ทั้งสองคน
  • อันดับถัดไปคือ ไข่มุก ยอดขายเท่ากับ 97 ข้ามไปเป็นอันดับ 3 เลย


• คอลัมท์ E ให้ Order เป็น 1 หรือ มากกว่า จะเรียงอันดับจากยอดขายน้อยที่สุด ไปหายอดขายที่มากกว่า