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

การหาผลรวมด้วย SUM() / SUMIF() / SUMIFS() ใน Excel

ปกติในโปรแกรม Excel จะมีฟังก์ชั่นในการหาผลรวมที่อยู่ในช่วง (Range) เพื่อให้ผู้ใช้สามารถลดเวลาในการจัดการข้อมูลจำนวนมากๆ ได้อย่างสะดวกขึ้น

1. ฟังก์ชั่น SUM

ไวยากรณ์ =SUM(number1,[number2],…])

ตัวอย่าง เป็นการหาผลรวมของการขายสินค้าทั้งหมด ดังรูปที่ 1

รูปที่ 1 แสดงตัวอย่างการใช้ SUM หาผลรวม

จากรูปที่ 1 ที่เซลล์ E10 คุณสามารถเขียนฟังก์ชั่นเพื่อหาผลรวมของการขายสินค้าทั้งหมด =SUM(E3:E9) ซึ่งระบบจะทำการหาผลรวมใน E3 ถึง E9 นั้นเอง 

แต่ถ้าคุณต้องการคำนวณโดยใส่เงื่อนไขบางอย่างละ ตัวอย่างเช่น คำนวณเฉพาะผลการขายสินค้าที่เป็น Shirt คุณจะทำอย่างไร ???

เพื่อให้เป็นการง่าย ไม่ต้องมาหาผลรวมที่ละสินค้า หรือว่ามาทำการลบผลการขายสินค้าที่เป็น Shirt ออกไป โปรแกรม Excel ได้มีฟังก์ชั่น SUMIF() 

😊😊😊😊😊

2. ฟังกชั่น SUMIF()

SUMIF() ใช้สำหรับการหาผลรวมของช่วงที่ต้องการ โดยต้องสอดคล้องกับเงื่อนไขที่กำหนด ... (เริ่มมีเงื่อนไขเข้ามากำหนด ไม่งั้นจะไม่หาผลรวม)

ไวยากรณ์ =SUMIF(Range, Criteria, [sum_range])

หลักการ

Range หมายถึง ช่วงเซลล์ที่มีเงื่อนไขที่ระบุใน criteria

Criteria หมายถึง เงื่อนไขที่ระบุ โดยจะเป็นตัวเลขหรือความข้อความก็ได้

Sum_rang หมายถึง ช่วงเซลล์ที่ต้องการให้หาผลรวมตามเงื่อนไขที่เราระบุไว้

ตัวอย่าง จากตัวอย่างเดิม ต้องการหาผลรวมของการขายสินค้าทั้งหมด ยกเว้นสินค้าที่เป็น Shirt คุณสามารถเขียนฟังก์ชั่นได้ ดังนี้

=SUMIF(A3:A9,"<>shirt",E3:E9)

ถ้าโจทย์ในการทำงานเปลี่ยนไป ต้องการหาผลรวมเฉพาะกระโปรง (Skirt) และ ราคาต่อชิ้นต้องมากกว่าหรือเท่ากับ 800 บาทขึ้นไป (เงื่อนไขในการคำนวณมากกว่า 1 เงื่อนไข) คุณจะทำอย่างไร ???

คุณ ๆ คงจะเริ่มเห็นข้อจำกัดของฟังก์ชั่นนี้กันแล้วใช่ไหม 😁😁 ข้อจำกัดของมัน คือ SUMIF() สามารถระบุได้เพียง 1 เงื่อนไขเท่านั้น 

😱😱😱😱😱😱

3. ฟังก์ชั่น SUMIFS()

SUMIFS() ใช้สำหรับการหาผลรวมของช่วงที่ต้องการ ซึ่งสามารถกำหนดได้หลายเงื่อนไข ... (เมื่อมีเงื่อนไขมากกว่า 1)

ไวยากรณ์ =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

หลักการ

Sum_range (จำเป็น) คือ ช่วงของเซลล์ที่ต้องการหาผลรวม 

Criteria_range1 (จำเป็น) คือ ช่วงที่จะถูกทดสอบจะใช้ Criteria1

Criteria1 (จำเป็น) คือ เกณฑ์ที่กำหนดว่าเซลล์ใดใน Criteria_range1 จะถูกมีเงื่อนไขเป็นอะไรในการพิจารณา เช่น ">32", "แอปเปิ้ล“, "32“

Criteria_range2, criteria2, …    (ระบุหรือไม่ก็ได้) คือ ช่วงเพิ่มเติมและเกณฑ์ที่สัมพันธ์กันของช่วง คุณสามารถใส่ได้ถึง 127 คู่ของช่วง/เงื่อนไข

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

รูปที่ 2 แสดงสูตรที่คำนวณเฉพาะสินค้ากระโปร่งและรามามากกว่า 800 บาท

จากตัวอย่างของการหาผลรวมทั้ง 3 ฟังก์ชั่น หวังว่าคงจะเป็นประโยชน์กันไม่มากก็น้อยนะคะ 

Read More

การใช้ Logic Functions ใน Excel

Logic Functions คือ ฟังก์ชั่นเชิงตรรกศาสตร์ ที่ Excel เตรียมไว้

ในบทความนี้ เสนอฟังก์ชั่นที่ช่วยให้คุณทำการตัดสินใจในสูตร อย่างเช่น AND() NOT() OR() และ IF() 

ข้อควรระวังของการเขียนสูตรใน Excel จะต้องไม่มีช่องว่าง จนกว่าจะเขียนทั้งสูตรเสร็จสิ้น

👯👯👯👯👯👯👯👯

AND() เป็นการทดสอบตรรกะ/เงื่อนไขว่าต้องเป็นจริงทั้งหมด 

ไวยากรณ์ =AND(logical1, [logical2])

หลักการ

Logical1 คือ เงื่อนไขที่ต้องการทดสอบแรก

Logical2 คือ เงื่อนไขที่ต้องการทดสอบสอง 

ตัวอย่าง

AND($B3>=25,$B3<=30)
หมายถึึง เซลล์ B3 มีค่ามากกว่าหรือเท่ากับ 25 และ B3 มีค่าน้อยกว่าหรือเท่ากับ 30

👯👯👯👯👯👯👯👯

NOT() เป็นการทดสอบตรรกะ/เงื่อนไข ว่าต้องเป็นเท็จ

ไวยากรณ์ =NOT(logical1)

ตัวอย่าง

NOT($B3>=25)

หมายถึง เซลล์ B3 ต้องไม่มี ค่าน้อยกว่าหรือเท่ากับ 25

👯👯👯👯👯👯👯👯

OR() เป็นการทดสอบตรรกะ/เงื่อนไข ว่าต้องมีอย่างน้องหนึ่งตัวที่เป็นจริง

ไวยากรณ์ =OR(logical1, [logical2])

ตัวอย่าง

OR($B3>=25,$B3<=30)

หมายถึง เซลล์ B3 ต้องมีค่ามากกว่าหรือเท่ากับ 25 หรือ มีค่าน้อยกว่าหรือเท่ากับ 30 

👯👯👯👯👯👯👯👯

IF() เป็นฟังก์ชั่นที่ใช้ทดสอบตรรกะ/เงื่อนไขว่าเป็นจริงหรือเท็จ และเปรียบเทียบตรรกะ/เงื่อนไขกับค่า Value ที่ระบุไว้

ไวยากรณ์ =IF(logical_test,Value_if_true,Value_if_False)

หลักการ
logical_test เป็นการระบุ ตรรกะ/เงื่อนไข ที่ต้องการตรวจสอบ 
value_if_true เป็นการระบุ ค่าที่โปรแกรมต้องส่งกลับ เมื่อเงื่อนไขเป็นจริง (จะใส่เป็นค่าหรือสูตรก็ได้) 
value_if_false เป็นการระบุ ค่าที่โปรแกรมต้องส่งกลับ เมื่อเงื่อนไขเป็นเท็จ (จะใส่เป็นค่าหรือสูตรก็ได้)

ตัวอย่าง

=IF($B13>100,"มากกว่า","น้อยกว่า")  
หมายถึง ถ้าเซลล์ B13 มีค่ามากกว่า 100 จะแสดงข้อความ มากกกว่า แต่ถ้าเซลล์ B13 มีค่าน้อยกว่า 100 จะแสดงข้อความ น้อยกว่า

=IF($B13>100,$B13+3,$B13+10)
หมายถึง ถ้าเซลล์ B13 มีค่ามากกว่า 100 จะแสดงผลรวมของค่าในเซลล์ B13 กับ 3 แต่ถ้าเซลล์ B13 มีค่าน้อยกว่า 100 จะแสดงผลรวมของค่าในเซลล์ B13 กับ 10

👯👯👯👯👯👯👯👯

ทั้ง 3 ฟังก์ชั่น คือ AND OR NOT สามารถนำไปใช้คู่กับ ฟังก์ชั่น IF ก็จะมำให้สูตรที่คุณเขียนมีลูกเล่นมากขึ้น 

ตัวอย่าง การใช้ IF และ AND
เขียนสูตรที่ตรวจสอบคะแนนของนักเรียนแต่ละวิชาต้องมีค่ามากกว่าหรือเท่ากับ 20 คะแนน ทุกวิชาถึงจะผ่าน

รูปที่ 1 แสดงตัวอย่างผลการตรวจสอบคะแนนว่าผ่านหรือไม่ผ่านใน Excel

ซึ่งคุณสามารถเขียนสูตรที่คอลัมน์ D ดังรูปที่ 2

รูปที่ 2 แสดงสูตรที่เขียนใน Excel

จากรูปที่ 2 เป็นการใช้ IF และ AND เพื่อเขียนสูตร โดยในเซลล์ 
D2 หมายถึง ถ้า เซลล์ B2 และ C2 มีค่ามากกว่าหรือเท่ากับ 20 ให้แสดงคำว่า ผ่าน

เมื่อคุณเขียนสูตรเสร็จแล้วต้องการใช้สูตรเดียวกันกับเซลล์ D3-D5 ให้คุณๆ ทำการ Copy ไปวางได้ทันที่ โดยสามารถอ่านเพิ่มเติมได้ในบทความเรื่อง เทคนิคการคัดลอกข้อมูลในโปรแกรม Excel

Read More