วันจันทร์ที่ 11 กุมภาพันธ์ พ.ศ. 2556

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


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

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

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

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

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

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

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

details of COUNTIF function
Result of COUNTIF function

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

Using Countif function in formatting rule to find different data

  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 เพื่อให้รายการที่แตกต่างแสดงสีอื่นออกมา
Result of using Countif+Formatting rule

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

Reactions:

0 comments:

แสดงความคิดเห็น