การนับสิ่งที่สนใจแบบหลายเงื่อนไข ไว้...ไวในพริบตา ด้วยสูตร CountIFS() ตอนที่ 2

จากบทความ นับสิ่งที่สนใจแบบมีเงื่อนไขในพริบตา...ด้วยสูตร CountIF() ตอนที่ 1 คุณ ๆ รู้จักการใช้ COUNTIF ไปแล้ว ในตอนนี้นำเสนอ COUNTIFS (เติม S) เพื่อใช้กับกรณีเงื่อนไขมากกว่า 1 เงื่อนไข 

ทั้ง 2 สูตรนี้ ถูกนำมาใช้สลับกันบ่อยมาก เพราะว่าหน้าตาที่ดูคล้ายกันมาก ๆ และยังมีคุณสมบัติในการนับเซลล์ด้วยเงื่อนไขต่าง ๆ เช่น ตัวเลข (numbers), การอ้างอิงเซลล์ (call reference), วันที่ (dates), ตัวอักษร (text), สัญลักษณ์ (wildcard characters), ช่องว่าง (non-blank cells) เป็นต้น

เป้าหมายของบทความนี้ เพื่อชี้ให้เห็นความแตกต่างของทั้ง 2 สูตร จนทำให้คุณสามารถเลือกสูตรไปใช้ได้เหมาะสมกับแต่ละงานนั้นเอง

ความแตกต่างของ COUNTIF และ COUNTIFS 
COUNTIF() คือ การนับช่วงเซลล์ ตามเงื่อนไขเพียง 1 เงื่อนไข
COUNTIFS() คือ การนับช่วงเซลล์ ตามเงื่อนไขเพียง 1 เงื่อนไข หรือหลายเงื่อนไขก็ได้

ไวยากรณ์ (Syntax)
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
หลักการ

  • criteria_range1 - กำหนดช่วงที่ต้องการนับ (เกณฑ์ที่ 1)
  • criteria1 - กำหนดเงื่อนไขในรูปแบบต่าง ๆ (เงื่อนไขที่ 1) เช่น ตัวเลข (numbers), การอ้างอิงเซลล์ (call reference), วันที่ (dates), ตัวอักษร (text), สัญลักษณ์ (wildcard characters), ช่องว่าง (non-blank cells)
  • [criteria_range2, criteria2] - เกณฑ์ที่ 2 และ เงื่อนไขที่ 2


เมื่อคุณ ๆ เข้าใจไวยากรณ์ และ หลักการ แล้ว ต่อไปเป็นตัวอย่างการประยุกต์ COUNTIFS ในรูปแบบต่าง ๆ ดังนี้

  • การนับเซลล์แบบหลายเงื่อนไขด้วยตัวอักษร (text)  
  • การนับตัวเลขระหว่าง X และ Y
  • การนับเซลล์แบบหลายเงื่อนไขด้วยการอ้างอิงเซลล์ (call reference)
  • การนับเซลล์แบบหลายเงื่อนไขด้วยสัญลักษณ์ (wildcard characters)
  • การนับเซลล์แบบหลายเงื่อนไขด้วยวันที่ (dates)


ตัวอย่างที่ 1 วิธีการนับเซลล์แบบหลายเงื่อนไขด้วยตัวอักษร (textดังรูปที่ 1
รูปที่ 1 แสดงตัวอย่างการนับเซลล์หลายเงื่อนไขด้วยตัวอักษร
จากตัวอย่างที่ 1 ผลลัพธ์จะได้ศูนย์ (0) เพราะต้องการนับเซลล์ที่ต้องมีชื่อรายการอาหารทั้ง 2 ซึ่งเขียนเป็นสูตรได้ ดังนี้
=COUNTIFS(B2:B16,"ข้าวผัดหมู",B2:B16,"ข้าวผัดกุ้ง")

แต่ถ้าโจทย์เดียวกันนี้ คุณใช้สูตร COUNTIF ผลลัพธ์ที่ได้จะเป็น 2 เพราะโปรแกรมจะเลือก B4 + B6 ซึ่งเขียนเป็นสูตรได้ ดังนี้
=COUNTIFS(B2:B16,"ข้าวผัดหมู")+COUNTIFS(B2:B16,"ข้าวผัดกุ้ง")
👱👱👱

ตัวอย่างที่ 2 วิธีการนับตัวเลขระหว่าง X และ Y
นับเซลล์ด้วย CountIFS โดยมีหลายเงื่อนไข
รูปที่ 2 แสดงการใช้สูตร COUNTIFS  
จากรูปที่ 2 ต้องการให้นับเซลล์ที่ตรงกับเงื่อนไขที่ 1 และ 2 ที่หมายความว่า นับจำนวนสินค้าที่ยังมีสินค้าอยู่ใน Stock มากกว่าศูนย์ชิ้น และเป็นสินค้าที่ยังไม่ได้ขายไปสักชิ้นอยู่เท่าไร 

เขียนสูตรตามหลักไวยากรณ์ ดังนี้
=COUNTIFS($B$2:$B$21,">0",$C$2:$C$21,"=0")

จากตัวอย่างนี้จะเห็นได้ว่าสูตร COUNTIFS เป็นเครื่องมือใช้นับเซลล์ที่มีเงื่อนไขแบบนี้ได้อย่างดีที่เดียว

แต่ถ้าคุณ ๆ ต้องการนับเซลล์ ที่มีหลายเงื่อนไข โดยที่มีอย่างน้อย 1 เงื่อนไขที่เป็นจริง คุณจะเขียนสูตรได้อย่างไร !!!!
👱👱👱

ตัวอย่างที่ 3 วิธีการนับเซลล์แบบหลายเงื่อนไขการอ้างอิงเซลล์ (call reference) จากรูปที่ 2 เราเพิ่ม D22 ที่เขียน 0 ไว้ แล้วเราก็เปลี่ยนวิธีการเขียนใหม่

เขียนสูตรตามหลักไวยากรณ์ ดังนี้
=COUNTIFS($B$2:$B$21,">"&D22,$C$2:$C$21,"="&D22)

👱👱👱

ตัวอย่างที่ 4 วิธีการนับเซลล์แบบหลายเงื่อนไขด้วยสัญลักษณ์ (wildcard characters)
จากรูปที่ 1 ถ้าเรานับรายการอาหารที่มีคำบางส่วนเป็นส่วนประกอบสามารถใช้แบบตัวอย่างนี้ได้เลย
=COUNTIFS(B2:B16,"*หมู*",B2:B16,"*กุ้ง")
ปล. ตัวอย่างที่ 3 นี้จะได้ผลลัพธ์เหมือนกับตัวอย่างที่ 1 [=COUNTIFS(B2:B16,"ข้าวผัดหมู",B2:B16,"ข้าวผัดกุ้ง")]

👱👱👱

ตัวอย่างที่ 5 วิธีการนับเซลล์แบบหลายเงื่อนไขด้วยวันที่ (dates)
การนับเซลล์หลายเงื่อนไข
รูปที่ 3 แสดงตัวอย่างการนับเซลล์แบบหลายเงื่อนไขด้วยวันที่
เขียนสูตรตามหลักไวยากรณ์ได้ ดังนี้

=COUNTIFS(E2:E16,">14/09/2560",E2:E16,"<20/09/2560")
ผลลัพธ์จากสูตรนี้จะนับเซลล์ตาม E7 ถึง E9


นี้เป็นตัวอย่างแบบต่าง ๆ สำหรับใช้นับเซลล์หลายเงื่อนไข เพื่อให้คุณ ๆ สามารถนำไปประยุกต์ใช้กับงานได้อย่างมีประสิทธิภาพมากที่สุด
Share:

นับสิ่งที่สนใจแบบมีเงื่อนไขเดียวในพริบตา...ด้วยสูตร CountIF() ตอนที่ 1

ถ้าคุณได้รับงานให้ค้นหาคำในไฟล์ Excel ขนาดใหญ่ ให้เรามาใช้สายตาตรวจคงจะไม่ไหว ดังนั้น ในบทความนี้ จะนำเสนอสูตรที่ช่วยให้คุณนับคำที่เราสนใจได้ในพริบตา

CountIF() ถูกใช้กับการนับเซลล์ ในช่วงที่คุณกำหนดเป็นเงื่อนไข 

โดยไวยากรณ์สูตรต้องเขียน ดังนี้
ไวยากรณ์:   COUNTIF(range, criteria)
หลักการ
  • range หมายถึง ช่วงของเซลล์ที่คุณต้องการจะนับ เช่น A1:A20 เป็นต้น 
  • criteria หมายถึง เงื่อนไขที่คุณต้องการ ซึ่งจะเป็นตัวเลข ข้อความ หรือตัวแปรก็ได้ 
ตัวอย่าง1 ทำการนับคำด้วย COUNTIF() โดยเงื่อนไขสามารถเป็นตัวแปร หรือข้อความก็ได้
COUNTIF Formula Example
รูปที่ 1 แสดงตัวอย่างการใช้สูตร COUNTIF

จากตัวอย่างนี้ ใช้สูตร COUNTIF เข้ามาช่วยนับคำที่กำหนด ซึ่งสูตรนี้จะไม่ตรวจสอบตัวอักษรใหญ่-เล็ก (case insensitive) และเงื่อนไขสามารถกำหนดเป็นตัวแปร (เหมือนเซลล์ B6 และ B8) หรือข้อความ (เซลล์ B7) ก็ได้ 

นอกจาก เงื่อนไขของ COUNTIF สามารถเป็นทั้งตัวแปรและข้อความแล้ว ยังสามารถใส่เป็นตัวเลข หรือสูตรได้ด้วย

ตัวอย่างที่ 2 ทำการนับคำด้วย COUNTIF() โดยเงื่อนไขสามารถเป็นตัวเลข หรือสูตรได้
จากตัวอย่างที่ 2 นี้ 
=COUNTIF($A$2:$D$4,2) คือ สูตรนี้นับเซลล์ตั้งแต่ A2 ถึง D4 โดยให้นับเลข 2 
=COUNTIF($A$2:$D$4,"*Ap*") คือ สูตรนี้นับเซลล์ตั้งแต่ A2 ถึง D4 โดยให้นับคำที่มีส่วนประกอบ Ap ซึ่งข้างหน้าและหลังคำนี้ จะประกอบด้วยตัวอักษรอะไร จำนวนเท่าไรก็ได้ 
=COUNTIF($A$2:$D$4,"?????es") คือ สูตรนี้นับเซลล์ตั้งแต่ A2 ถึง D4 โดยให้นับคำที่มีตัวอักษรอะไรก็ได้ แต่จำนวนตัวอักษรต้องมี 5 ตัว และต้องปิดท้ายคำด้วย es 
=COUNTIF($A$2:$D$4,"????es") คือ สูตรนี้นับเซลล์ตั้งแต่ A2 ถึง D4 โดยให้นับคำที่มีตัวอักษรอะไรก็ได้ แต่จำนวนตัวอักษรต้องมี 4 ตัว และต้องปิดท้ายคำด้วย es 

แต่ถ้าต้องการค้นหาตัวคำว่า "?" หรือ "*" ให้ใส่ "~" นำหน้า "?" หรือ "*" โปรแกรมจะค้นหา "?" หรือ "*" ได้ปกติ

ตัวอย่างที่ 3 ทำการนับคำด้วย COUNTIF() โดยเงื่อนไขสามารถดูว่ามีค่ามากกว่า น้อยกว่า หรือเท่ากับ ก็ได้
ถ้าต้องการนับตัวเลขที่

  • มากกว่า 5                 =COUNTIF($A$2:$D$4,">5")
  • มากกว่าหรือเท่ากับ 5 =COUNTIF($A$2:$D$4,">=5")
  • น้อยกว่า 5                 =COUNTIF($A$2:$D$4,"<5")
  • น้อยกว่าหรือเท่ากับ 5 =COUNTIF($A$2:$D$4,"<=5")
  • เท่ากับ 5                   =COUNTIF($A$2:$D$4,"=5")
  • ไม่เท่ากับ 5               =COUNTIF($A$2:$D$4,"<>5")

ก่อนหน้านี้ COUNTIF ใช้กับเงื่อนไขเพียง 1 เงื่อนไข แต่ถ้าต้องการนับ หลายเงื่อนไขละ จะทำได้อย่างไร


ตัวอย่างที่ 4 ทำการนับคำด้วย COUNTIF() โดยมีหลายเงื่อนไข
ถ้าใช้โจทย์เดียวกับตัวอย่างที่ 3 คุณสามารถ + หรือ - ค่าที่ได้จากเงื่อนไขที่กำหนดไว้เป็นตัวเลข ดังนี้
=COUNTIF($A$2:$D$4,">=4")-COUNTIF($A$2:$D$4,"<7")+COUNTIF($A$2:$D$4,"=7")
จากสูตรนี้ จะมี 3 COUNTIF()  ได้ 8-7+2 คำตอบที่ได้ คือ 3

ถ้าใช้โจทย์เดียวกับตัวอย่างที่ 2 คุณสามารถใช้ + หรือ - ค่าที่ได้จากเงื่อนไขที่กำหนดเป็นตัวอักษร ดังนี้
=COUNTIF($A$5:$D$7,"*apples")+COUNTIF($A$5:$D$7,"oranges")
จากสูตรนี้ จะมี COUNTIF() ได้ 4+2 คำตอบที่ได้ คือ 6


Share:

การเปรียบเทียบ 2 คอลัมน์แบบทีละแถว

ในการวิเคราะห์ข้อมูลด้วย Excel คุณจะเลี่ยงงานที่ต้องเปรียบเทีียบข้อมูลในแต่ละแถวไม่ได้เลย ซึ่งเราก็มีวิธีการต่างๆ ให้คุณได้นำไปประยุกต์ใช้ โดยในบทความนี้จะให้คุณเปรียบเทียบคำใน 2 คอลัมน์ จากตารางที่ 1 
เปรียบเทียบคอลัมน์
ตารางที่ 1 แสดงข้อมูลที่จะใช้ทำการเปรียบเทียบ

ตัวอย่างที่1 การเปรียบเทียบ 2 คอลัมน์ ที่ละแถว โดยใช้ IF Function ดังนี้
1. เขียนสูตรในเซลล์ C1 ดังนี้ =IF($A2=$B2,"Match","-"

ปล.

  • "$" หน้าชื่อเซลล์แบบนี้มีประโยชน์อย่างไร อ่านที่นี่ 
  • สูตร IF() อ่านได้ว่า ถ้าเงื่อนไขสีน้ำเงินเป็นจริง (ค่าในเซลล์ A2 เท่ากับ B2) ให้แสดงข้อความสีเขียว แต่ถ้าเงื่อนไขสีน้ำเงินเป็นเท็จ ให้แสดงข้อความสีแดง
  • สามารถไปศึกษาเพิ่มเติมที่ การใช้ Logic Function ที่ Excel เตรียมไว้

2. คัดลอกข้อมูลลงมาถึงเซลล์ C12 .... เทคนิคการคัดลอก มีแบบไหนบ้าง อ่านที่นี่
เพียงแค่นี้คุณจะได้ ดังรูปที่ 1
การเปรียบเทียบคอลัมน์ในเอ็กเซล
รูปที่ 1 แสดงผลการใช้สูตร IF ในการเปรียบเทียบคอลัมน์
จากคอลัมน์ผลลัพธ์ จะเห็นว่าโปรแกรมสามารถเปรียบเทียบคอลัมน์ว่าเหมือนหรือต่างกันได้อย่างถูกต้อง แต่วิธีนี้จะไม่สามารถตรวจสอบตัวอักษรตัวใหญ่-เล็กได้ (case-sensitive) แบบเดียวกับ แถวที่ 4, 9 

ตัวอย่างที่2 การเปรียบเทียบค่า 2 คอลัมน์ ว่าเหมือนหรือต่างกัน โดยดูที่ตัวอักษรด้วยว่าตัวใหญ่หรือเล็ก (case-sensitive) ด้วย EXACT Function ดังนี้
1. เขียนสูตรแบบนี้ =IF(EXACT($A2,$B2),"Match","-"

ปล. 

  • สูตร IF() อ่านได้ว่า ถ้าเงื่อนไขสีน้ำเงินเป็นจริง (ค่าในเซลล์ A2 มีลักษณะเหมือนกับ B2 ทุกตัวอักษร) ให้แสดงข้อความสีเขียว แต่ถ้าเงื่อนไขสีน้ำเงินเป็นเท็จ ให้แสดงข้อความสีแดง
  • สามารถไปศึกษาเพิ่มเติมที่ การใช้ Logic Function ที่ Excel เตรียมไว้

2. คัดลอกข้อมูลลงมาถึงเซลล์ C12 เพียงแค่นี้คุณจะได้ ดังรูปที่ 2
การเปรียบเทียนคอลัมน์ในเอ็กเซล
รูปที่ 2 แสดงผลการใช้สูตร Exact ในการเปรียบเทียบคอลัมน์
จากรูปที่ 2 คอลัมน์ที่ 4 และ 9 จึงแสดงว่ามีค่าที่ไม่ Match เพราะ Exact Function จะเปรียบเทียบถึงตัวอักษรเล็ก-ใหญ่ด้วย 


😏😏😏😏 

จากทั้ง 2 ตัวอย่างข้างบน เป็นการเปรียบเทียบระหว่าง 2 คอลัมน์ แต่ถ้าคุณ ๆ ต้องการเปรียบเทียบ 3 คอลัมน์ขึ้นไปจะต้องทำอย่างไร !!! 

ตัวอย่างที่ 3 การเปรียบเทียบค่าตั้งแต่ 2 คอลัมน์ขึ้นไป ว่าเหมือนหรือต่างกัน โดยใช้ สูตร IF() และ สูตร AND() เพื่อให้ได้ ดังรูปที่ 3
Compare two compumn
รูปที่ 3 การเปรียมเทียบหลายคอลัมน์ใน Excel (Compare multiple columns)
1. เขียนสูตรแบบนี้ =IF(AND(A2=B2, A2=C2), "Full match", "-") ที่เซลล์ D2

ปล.

  • สูตร IF() หมายถึง ถ้าเงื่อนไขสีน้ำเงินเป็นจริง ให้แสดงข้อความสีเขียว แต่ถ้าเงื่อนไขสีน้ำเงินเป็นเท็จ ให้แสดงข้อความสีแดง
  • สูตร AND() หมายถึง เมื่อ A2 มีค่าเท่ากับ B2 และ A2 มีค่าเท่ากับ C2 
  • สามารไปศึกษาเพิ่มเติมที่ การใช้ Logic Function ที่ Excel เตรียมไว้

2. คัดลอกข้อมูลลงมาถึงเซลล์ D12 เพียงแค่นี้คุณจะได้ ดังรูปที่ 3 

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





Share:

Blogger มีช่องทางผูก Search Console

คุณๆ ที่เขียน Blog ด้วย Blogger นั้นทาง Blogger มีช่องทางในการผูกกับ Google Analytics ได้อย่างง่ายๆ ดังนี้

1. เข้าสู่หน้า Dashboard ของ Blogger >> Settings >> Search Preferences
2. คลิก Edit ของ Google Analytics ในส่วน Crawlers and indexing ดังรูปที่ 1
Blogger ผูกกับ Google Analytics
รูปที่ 1 แสดงช่องทางที่ Blogger เชื่อมกับ Google Analytics
3. ระบบเปิดไปหน้า https://www.google.com/webmasters/tools/home?hl=en เลือกหน้าเว็บลงทะเบียนไว้ ดังรูปที่ 2
Blogger ผูกกับ Google Analytics
รูปที่ 2 แสดงช่องทางการเชื่อมกับ Google Analytics ผ่าน Blogger
4. คลิกปุ่ม Setting ด้านขวาบน >> เลือก Google Analytics Property ดังรูปที่ 3
Blogger ผูกกับ Google Analytics
รูปที่ 3 แสดงช่องทางการเชื่อมกับ Google Analytics 
5. เข้าสู่หน้า Search Console ให้คลิกปุ่ม สร้างบัญชี Google Analytics ดังรูปที่ 4
Blogger ผูกกับ Google Analytics
รูปที่ 4 แสดงหน้าจอ Search Console
6. ระบบจะไปที่หน้าของ Google Analytics เพื่อให้คุณ ๆ ต้องใส่รายละเอียดการลงทะเบียนในครั้งแรก ซึ่งจะมีรายละเอียด ดังรูปที่ 5
Setup of Google Analytics
รูปที่ 5 แสดงหน้าลงทะเบียนครั้งแรกของ Google Analytics
7. เมื่อกรอกข้อมูลครบหมดแล้ว ให้กดปุ่มรับรหัสติดตาม ด้านล่างสุด จะมี PopUp ขึ้นมาให้คุณอ่านเงื่อนไขการให้บริการของ Google Analytics ถ้าคุณยอมรับให้คลิกปุ่มฉันยอมรับ เพื่อเข้าใช้ Google Analytics ดังรูปที่ 6
ข้อกำหนดในการให้บริการของ Google Analytics
รูปที่ 6 แสดงหน้าจอข้อกำหนดในการให้บริการของ Google Analytics
8. เมื่อยอมรับเรียบร้อยแล้ว ระบบจะแสดงหน้า Property ใน Google Analytics ซึ่งเมื่อกลับไปที่หน้า Search Console >> เลือก Google Analytics Property ให้ทำการเชื่อมโยง Search Console ใน Google Analytics ดังรูปที่ 7
Search Console ใน Google Analytics
รูปที่ 7 แสดงหน้าจอ Search Console ใน Google Analytics 
เรียบร้อยคุณสามารถติดตามดูเว็บของคุณได้
Share:

3 ขั้นตอนการเปรียบเทียบไฟล์ 2 ไฟล์ใน Excel

หากคุณมี Workbooks 2 ตัวขนาดไม่ใหญ่ การใช้สายตาในการเปรียบเทียบทั้ง 2 Workbooks ก็ไม่มีน่าจะมีปัญหา แต่ถ้าข้อมูลเยอะ ๆ การตรวจสอบด้วยสายตาอาจจะไม่ work เท่าไร 

ในบทความนี้ แนะนำเครื่องมือในการจัดการไฟล์หลาย ๆ ไฟล์ เพื่อให้เราเห็นข้อมูลทั้งหมด และ สูตรให้โปรแกรมทำการเปรียบเทียบข้อมูล

ส่วนนี้จะกล่าวถึง เครื่องมือในการจัดการมุมมองของไฟล์หลาย ๆ ไฟล์ทั้งหมดพร้อมกัน คือ View Side นั้นเอง

1. คุณเปิด workbooks 2 workbooks เปรียบเทียบกัน 
2. เปิด View tab >> View Side by Side ดังรูปที่ 1
เปรียบเทียบ workbooks ใน Excel
รูปที่ 1 แสดงวิธีการเปรียบเทียบไฟล์ 2 ไฟล์
3. โปรแกรมจะเปิด workbooks 2 workbooks ในแนวนอน (Horizontally) ดังรูปที่ 2
เปรียบเทียบไฟล์ 2 ไฟล์ ใน excel
รูปที่ 2 แสดงตัวอย่างเมื่อคลิกปุ่ม View Side by Side
4. หากต้องการให้โปรแกรมแสดงทั้ง 2 ไฟล์ในแนวตั้ง (Vertically) ดังรูปที่ 3 
การเปรียบเที่ยบไฟล์ 2 ไฟล์ ใน Excel
รูปที่ 3 แสดงวิธีการเปรียบเทียบไฟล์ 2 ไฟล์
5. โปรแกรมจะเปิด workbooks 2 workbooks ในแนวตั้ง (Vertically) ดังรูปที่ 4
วิธีการเปรียบเทียบไฟล์ 2 ไฟล์ (Compare two Excel files)
รูปที่ 4 แสดงตัวอย่างหน้าจอเมื่อเลือก Vertically
แต่ถ้าคุณไม่ตรวจสอบด้วยสายตา เรามี วิธีการเขียนสูตรใน Excel เพื่อเปรียบเทียบค่าในเซลล์แต่ละเซลล์ ดังนี้
1. รวมข้อมูลในไฟล์เดียวกัน โดยการแบ่งเป็น Sheet 

2. เขียนสูตร นี้ 
=IF(Sheet1!$A2<>Sheet2!$A2,"sheet1:"&Sheet1!A2&" vs  Sheet2:"&Sheet2!$A2,"-"

ถ้า งง สูตรคุณควรอ่าน IF() ในบทความ การใช้ Logic Functions ใน Excel และ ถ้า งง $ ให้อ่านบทความ 3 ประเภทการอ้างอิงเซลล์ (Cell Reference) ใน Excel ก่อน

จากสูตรด้านบน คือ 
- สีฟ้า หมายถึง เงื่อนไขที่กำลังจะตรวจสอบ ในโจทย์นี้ เราต้องการเปรียบเทียบข้อมูล A2 ของ Sheet1 กับ Sheet2 
- สีเขียว หมายถึง เมื่อเงื่อนไข (สีฟ้า) เป็นจริง จะทำในส่วนนี้ คือ แสดงข้อความ Sheet1: [ค่า A2 ใน Sheet1] เทียบกับ Sheet2 : [ค่า A2 ใน Sheet2] 
- สีแดง หมายถึง เมื่อเงื่ิอนไข (สีฟ้า) เป็นเท็จ จะทำในส่วนนี้ คือ ขีดเส้น 

3. Copy สูตรไปวางเซลล์อื่นๆ ให้เท่ากับจำนวนคอลัมน์และแถวของทั้ง 2 ตาราง ดังรูปที่ 5 
การปรียบเทียบค่าระหว่างเซลล์ ใน Excel
รูปที่ 5 แสดงผลลัพธ์จากการเขียนสูตร
เพียงแค่นี้คุณก็ไม่ต้องมาตรวจสอบด้วยตาตัวเอง แต่ให้สูตรตรวจสอบเพียงไม่กี่นาที
Share:

4 ขั้นตอนง่าย ๆ ใช้ตรวจสอบความผิดพลาดการทำ Conditional Formatting ใน Excel

จากหลายบทความเกี่ยวกับ Conditional Formatting ถ้าคุณ ๆ ทำแล้วไม่ได้ผลตามตัวอย่าง นั้นเป็นสัญญาณว่าคุณอาจจะทำอะไรผิดพลาดแล้ว

เรามี 4 วิธีง่าย ๆ ใช้ตรวจสอบความผิดพลาดการทำ Conditional Formatting ใน Excel มาเสนอให้คุณสามารถใช้ตรวจสอบตนเองได้เบื้องต้น 
เอ็กเซล

1. คุณใช้ "การอ้างอิงเซลล์แบบสัมพันธ์ (Relative Reference)" หรือ "การอ้างอิงเซลล์แบบสัมบูรณ์ (Absolute Reference)" หรือ "การอ้างอิงเซลล์แบบผสม (Mixed Reference)" ถูกต้องหรือไม่
สำหรับมือใหม่หัดเขียน Excel อาจเป็นสิ่งยากในการเขียนอ้างอิงเซลล์แบบต่างๆ แต่ถ้าคุณ ๆ หัดใช้ให้เคยชิน รับรองว่าวิธีนี้ส่งผลให้ลดข้อผิดพลาดจากการอ้างอิงเซลล์เพี้ยนได้ 100% 

2. ตรวจสอบช่วงเซลล์ที่ใช้ในการกำหนดเงื่อนไขว่าถูกต้องหรือไม่
กฎง่าย ๆ คือ คุณจะเลือกเซลล์ทั้งหมด หรือเลือกเฉพาะแถว ที่คุณต้องการจัดรูปแบบ แต่ต้องไม่รวมส่วนของหัวคอลัมน์ หรือ Headers

3. ในการกำหนดเงื่อนไขลงไปในตาราง ให้เขียนเงื่อนไขที่เซลล์บน-ซ้ายสุดของตาราง
ตัวอย่างเช่น 

4. ตรวจสอบกฎในการสร้างว่าถูกต้องหรือไม่
ไม่ว่าจะเป็น เปิด-ปิดวงเล็บ สูตรที่นำมาใช้ เครื่องหมาย เป็นต้น โดยสามารถตรวจสอบ ด้วยการไปที่ Conditional Formatting >> Manage Rules 

นี้เป็นข้อวิธีการพื้นฐานที่คุณ ๆ ควรฝึกไว้เพื่อเป็นการตรวจสอบเบื้องต้นค่ะ


Share:

Conditional Formatting การใช้สูตรใน Excel สำหรับการจัดรูปแบบข้อมูลตามเงื่อนไข ตอนที่ 4

ก่อนหน้านี้เรามีการอธิบาย ถึงการจัดรูปแบบข้อมูลตามเงื่อนไข (Conditional Formatting) ด้วย Hightlight, Data Bar, Icon Set ในโปรแกรม Excel ไปแล้วถึง 3 ตอน

ในบทความนี้ ขอนำเสนอการจัดรูปแบบข้อมูลตามเงื่อนไข (Conditional Formatting) ด้วยสูตร (Formula) เพื่อให้คุณๆ มีวิธีในการจัดรูปแบบข้อมูลในโปรแกรม Excel ได้ตรงกับเงื่อนไขมากขึ้น  

ซึ่งการนำสูตรเข้ามาช่วยในการทำ Conditional Formatting นั้น คุณสามารถพบได้ใน Excel 2016, 2013 และ 2010 เท่านั้น


วิธีการ ใช้สูตรในการจัดรูปแบบตามเงื่อนไข  
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร 
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule  ดังรูปที่ 1
Conditional Formation with Formula
รูปที่ 1 แสดงเมนูการสร้าง Conditional Formatting ด้วยสูตร

4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. 
6. กำหนดรูปแบบของข้อความที่ปุ่ม Format 

เมื่อคุณรู้วิธีการสร้าง Conditional Formatting ด้วยสูตรแล้ว เรามาดูตัวอย่างการประยุกต์ใช้ ดังนี้ 

วิธีการการเปรียบเทียบค่า (ข้อความและตัวเลข) ใน Excel ด้วย Conditional Formatting โดยใช้สูตรต่างๆ
ปกติคุณสามารถใช้คุณลักษณะของการกำหนดรูปแบบข้อความหนึ่ง (Conditional Formatting แบบ Hightlight) ในการจัดการเซลล์ (ตัวอักษร) โดยมีเงื่อนไขว่าค่าในเซลล์นั้น ต้องมีค่ามากกว่า หรือ น้อยกว่า หรือ เท่ากับ ค่า (ตัวเลข) ที่คุณระบุ (The value you specify) ตัวอื่น เช่น 
การกำหนดรูปแบบข้อความ
รูปที่ 1  แสดงตัวอย่างผลลัพธ์ของการใช้เครื่องมือ Conditional Formatting 
จะเห็นว่าการ Hightlight ที่เกิดจากการใส่เงื่อนไขลงไป จะปรากฎที่เซลล์ที่เป็นเงื่อนไขเท่านั้น โดยที่คุณจะ ไม่ สามารถใช้คุณลักษณะ (Conditional Formatting แบบ Hightlight) นี้ กับกรณีการจัดรูปแบบเซลล์หนึ่ง กับค่าในเซลล์อื่น (A cell's value in another column) ได้ 

แล้วถ้าต้องการจัดรูปแบบ โดยเปรียบเทียบเซลล์หนึ่งกับค่าในเซลล์อื่นละ !!!!!

ต่อไปนี้เป็นตัวอย่างที่ใช้สูตรเข้ามาประยุกต์ใช้ เพื่อให้เหมาะสมตามเงื่อนไขต่าง ๆ มากขึ้น

ตัวอย่างที่ 1 การเขียนสูตร (Formula) ให้เงื่อนไขทำการเปรียบเทียบคอลัมน์หนึ่งกับค่าหนึ่ง ซึ่งในตัวอย่างนี้จะให้ทำ Conditional Formatting ที่ Product Column โดยให้ตรวจสอบเงื่อนไขว่าค่าใน In Stock Column มีค่ามากกว่า 0 หรือไม่ 
เมื่อทำตามโจทย์นี้ คุณต้องได้ผลเหมือนตาราง ดังรูปที่ 2
Conditional Formatting
รูปที่ 2 แสดงตัวอย่างการใช้ Formula ใน Conditional Formatting

ในที่นี้ให้คุณประยุกต์ตามหัวข้อ "วิธีการใช้สูตรในการจัดการรูปแบบตามเงื่อนไข" ดังนี้
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร ซึ่งโนโจทย์นี้จะเลือก A13:A19
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule
4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. โดยในโจทย์นี้ให้ใส่สูตรนี้ =$B13>0 ..... อ่านมาถึงจุดนี้ ถ้าใคร งง ว่าต้อง $B13 ให้ไปศึกษาได้ที่ "การอ้างอิงเซลล์ใน Excel"

6. กำหนดรูปแบบของข้อความที่ปุ่ม Format
7. คลิกที่ปุ่ม OK ... 


วิธีการ Hightlight ทั้งแถว ใน Excel ด้วย Conditional Formatting โดยใช้สูตรต่างๆ
จากตัวอย่างก่อนหน้านี้ เรานำเสนอการจัดการรูปแบบข้อมูลในแบบต่างๆ แต่ทุกแบบเป็นการจัด Format เฉพาะเซลล์เท่านั้น ซึ่งถ้าหาต้องการจัด Format ทั้ง Row ให้เลือกทั้งตาราง แล้วจึงค่อยใส่เงื่อนไขลงไป

ตัวอย่างที่ 2
 การเขียนสูตร (Formula) ให้โปรแกรมทำการเปรียบเทียบระหว่างคอลัมน์กับคอลัมน์ ซึ่งในตัวอย่างนี้จะให้ทำ Conditional Formatting โดยดูจากเงื่อนไขว่าค่าใน In Stock Column มีค่าน้อยกว่า Sold Column หรือไม่ ดังรูปที่ 3
Conditional Formatting
รูปที่ 3 แสดงตารางที่ใช้ Formula ใน Conditional Formatting
ประยุกต์ตามหัวข้อ "วิธีการใช้สูตรในการจัดการรูปแบบตามเงื่อนไข" ดังนี้
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร ซึ่งโนโจทย์นี้จะเลือก A13:C19 (ในที่นี้กำหนดให้ทำ Conditional Formatting ทั้ง Row ทำให้ข้อต้องเลือกทั้งตาราง)
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule
4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. โดยในโจทย์นี้ให้ใส่สูตรนี้ =$B13<$C13 ..... ต้องการรู้ว่าการอ้างอิงเซลล์นี้คืออะไร อ่านที่ "การอ้างอิงเซลล์ใน Excel"
6. กำหนดรูปแบบของข้อความที่ปุ่ม Format
7. คลิกที่ปุ่ม OK ... 

จากทั้ง 2 ตัวอย่างข้างต้น การเขียนสูตรในการตรวจสอบได้แค่สูตรเดียว แต่ถ้าคุณต้องการตรวจสอบมากกว่า 1 สูตร จะทำอย่างไรละ ....

ตัวอย่างที่ 3 การเขียนสูตรโดยใช้ AND, OR เข้ามาช่วย ซึ่งในตัวอย่างนี้จะให้ทำ Conditional Formatting ที่ตาราง โดยมีเงื่อนไขว่า In Stock Column ต้องน้อยกว่า Sold Column และ Manufacturing Column ต้องผลิตใน Thailand หรือ Singapore เท่านั้น
เมื่อทำตามโจทย์นี้ คุณต้องได้ผลเหมือนตาราง ดังรูปที่ 4
Conditional Formatting
รูปที่ 4 แสดงผลที่ใช้ Formula ใน Conditional Formatting
ประยุกต์ตามหัวข้อ "วิธีการใช้สูตรในการจัดการรูปแบบตามเงื่อนไข" ดังนี้
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร ซึ่งโนโจทย์นี้จะเลือก A2:D8 (ในที่นี้กำหนดให้ทำ Conditional Formatting ทั้ง Row ทำให้ข้อต้องเลือกทั้งตาราง)
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule
4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. โดยในโจทย์นี้ให้ใส่สูตรนี้ =AND($B2<$C2,OR($D2="Thailand",$D2="Singapore")) ..... ถ้าสงสัยว่าทำไมต้องมี $หน้าชื่อเซลล์ ให้อ่านที่ "การอ้างอิงเซลล์ใน Excel"
6. กำหนดรูปแบบของข้อความที่ปุ่ม Format
7. คลิกที่ปุ่ม OK ...

ข้อสังเกต การใช้สูตรมาประกอบการทำ Conditional Formatting คุณจะต้องมีความรู้เรื่องสูตร และ การอ้างอิงเซลล์ เพิ่มขึ้นมาด้วย 


Share:

จำนวนการดูหน้าเว็บรวม

Popular Posts

ขับเคลื่อนโดย Blogger.

Dashboards คืออะไร มีกี่ประเภท

Dashboards คืออะไร และสามารถช่วยเราในการทำงานอย่างไร Dashboards คือ การนำข้อมูลมาสร้างรายงานที่เป็นภาพรวมทางธุรกิจ ให้ผู้บริหารสามารถ...

Recent Posts

Keywords

เอ็กเซล Data-Management Functions การจัดการข้อมูลในเอ็กเซล Blogger Basic-Excel Create-Blogger การจัดการข้อมูล Excel Conditional Formatting excel Data-Analysis Drop down list Excel สูตร Computer knowledge Feed RSS Atom คือ อะไร Index Match function excel SEO Search Console Search engine chart excel คือ excel data validate paste option Excel vlookup approximate Match exact Match vlookup function excel การใช้ concatenate ใน excel สร้าง drop down list สร้าง กราฟ เอ็กเซล Advance Filter Auto Filter by Color Auto Filter by Text Content Syndication DATEDIF() Datedif Function Excel SUM Function Excel SUMIF Function Excel SUMIFS Function Formula Values Transpose Formatting Function excel Gantt Chart excel Gantt Chart excel ทำยังไง HLOOKUP Icon Set Index Match function คือ Knowledge Line Chart Scatter Chart LogicFunction Match function excel Name Manager Paste Special Pie Doughnut chart excel Robots Header Tag Sumproduct function การใช้ สูตร เอ็กเซล Template Text Function Excel Trim Clear Function Excel Values column chart excel condition countif excel count if excel 2010 countifs data validation excel countifs เงื่อนไข ตัวอักษร มากกว่า น้อยกว่า excel index match formula excel match function reference cell excel sort and filter excel เบืื้องต้น excel เบื้องต้น flash fill excel คือ flash fill คือ อะไร function คือ highlight in dropdownlist index excel match vlookup index match ใช้ยังไง lookup excel กราฟ แผนภูมิ Excel การ เรียง ข้อมูล excel การ เรียง ลําดับ ข้อมูล excel การกรองข้อมูล Excel การตัดข้อความ เอ็กเซล การทํา chart excel การทําcontrol chart excel การสร้าง ตาราง กราฟ excel การสร้าง chart excel การสร้างฟีต การหาผลรวมในเอ็กเซล การเผยแพร่เนื้ือหา การเพิ่ม Subscription ให้ Blogger การแยก ข้อความ การใช้ if การใช้ index match excel การใช้งาน Subtotal outline excel การใช้ฟังก์ชั่น concatenate การใช้แผนภูมิ chart excel ค้นหาข้อมูล เอ็กเซล ค้นหาเลขคอลัมน์ ค้นหาเลขแถว เอ็กเซล ตัดช่องวางในเอ็กเซล ผูกเว็บกับ Google Analytics ฟังก์ชั่น Text การใช้ วิธีการตัดข้อความใน Excel วิธีทำ แผนภูมิ วงกลม Excel สูตร COUNTIF สูตรexcel concatenate สูตรการหาผลรวมใน Excel หาผลต่างระหว่างเดือน เพิ่มรายการใน Data Validation แผนภูมิ คอลัมน์ excel แผนภูมิคอลัมน์ เรียงซ้อน ใส่สีให้ dropdownlist