การนับสิ่งที่สนใจแบบหลายเงื่อนไข ไว้...ไวในพริบตา ด้วยสูตร 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

รูปที่ 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

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

Read More

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

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

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

โดยไวยากรณ์สูตรต้องเขียน ดังนี้

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

หลักการ

• range หมายถึง ช่วงของเซลล์ที่คุณต้องการจะนับ เช่น A1:A20 เป็นต้น 
• criteria หมายถึง เงื่อนไขที่คุณต้องการ ซึ่งจะเป็นตัวเลข ข้อความ หรือตัวแปรก็ได้ 

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

รูปที่ 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

Read More

การเปรียบเทียบ 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

รูปที่ 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 วิธี ซึ่งคุณต้องเลือกใช้ให้เหมาะสมกับแต่ละงาน

Read More

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

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

1. เข้าสู่หน้า Dashboard ของ Blogger >> Settings >> Search Preferences
2. คลิก Edit ของ Google Analytics ในส่วน Crawlers and indexing ดังรูปที่ 1

รูปที่ 1 แสดงช่องทางที่ Blogger เชื่อมกับ Google Analytics

3. ระบบเปิดไปหน้า https://www.google.com/webmasters/tools/home?hl=en เลือกหน้าเว็บลงทะเบียนไว้ ดังรูปที่ 2

รูปที่ 2 แสดงช่องทางการเชื่อมกับ Google Analytics ผ่าน Blogger

4. คลิกปุ่ม Setting ด้านขวาบน >> เลือก Google Analytics Property ดังรูปที่ 3

รูปที่ 3 แสดงช่องทางการเชื่อมกับ Google Analytics 

5. เข้าสู่หน้า Search Console ให้คลิกปุ่ม สร้างบัญชี Google Analytics ดังรูปที่ 4

รูปที่ 4 แสดงหน้าจอ Search Console

6. ระบบจะไปที่หน้าของ Google Analytics เพื่อให้คุณ ๆ ต้องใส่รายละเอียดการลงทะเบียนในครั้งแรก ซึ่งจะมีรายละเอียด ดังรูปที่ 5

รูปที่ 5 แสดงหน้าลงทะเบียนครั้งแรกของ Google Analytics

7. เมื่อกรอกข้อมูลครบหมดแล้ว ให้กดปุ่มรับรหัสติดตาม ด้านล่างสุด จะมี PopUp ขึ้นมาให้คุณอ่านเงื่อนไขการให้บริการของ Google Analytics ถ้าคุณยอมรับให้คลิกปุ่มฉันยอมรับ เพื่อเข้าใช้ Google Analytics ดังรูปที่ 6

รูปที่ 6 แสดงหน้าจอข้อกำหนดในการให้บริการของ Google Analytics

8. เมื่อยอมรับเรียบร้อยแล้ว ระบบจะแสดงหน้า Property ใน Google Analytics ซึ่งเมื่อกลับไปที่หน้า Search Console >> เลือก Google Analytics Property ให้ทำการเชื่อมโยง Search Console ใน Google Analytics ดังรูปที่ 7

รูปที่ 7 แสดงหน้าจอ Search Console ใน Google Analytics 

เรียบร้อยคุณสามารถติดตามดูเว็บของคุณได้

Read More

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

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

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

ส่วนนี้จะกล่าวถึง เครื่องมือในการจัดการมุมมองของไฟล์หลาย ๆ ไฟล์ทั้งหมดพร้อมกัน คือ View Side นั้นเอง
1. คุณเปิด workbooks 2 workbooks เปรียบเทียบกัน 
2. เปิด View tab >> View Side by Side ดังรูปที่ 1

รูปที่ 1 แสดงวิธีการเปรียบเทียบไฟล์ 2 ไฟล์

3. โปรแกรมจะเปิด workbooks 2 workbooks ในแนวนอน (Horizontally) ดังรูปที่ 2

รูปที่ 2 แสดงตัวอย่างเมื่อคลิกปุ่ม View Side by Side

4. หากต้องการให้โปรแกรมแสดงทั้ง 2 ไฟล์ในแนวตั้ง (Vertically) ดังรูปที่ 3 

รูปที่ 3 แสดงวิธีการเปรียบเทียบไฟล์ 2 ไฟล์

5. โปรแกรมจะเปิด workbooks 2 workbooks ในแนวตั้ง (Vertically) ดังรูปที่ 4

รูปที่ 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 

รูปที่ 5 แสดงผลลัพธ์จากการเขียนสูตร

เพียงแค่นี้คุณก็ไม่ต้องมาตรวจสอบด้วยตาตัวเอง แต่ให้สูตรตรวจสอบเพียงไม่กี่นาที

Read More

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 

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

Read More

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

รูปที่ 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

รูปที่ 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

รูปที่ 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

รูปที่ 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 คุณจะต้องมีความรู้เรื่องสูตร และ การอ้างอิงเซลล์ เพิ่มขึ้นมาด้วย 

Read More