TRIM Function ปะทะ Clear Function

ในบทความนี้ จะรวบรวมฟังก์ชั่นที่ใช้ในการลบช่องว่างในโปรแกรม Excel เช่น TRIM CLEAN 

TRIM Function

นี้เป็นคำสั่งที่ใช้ในการลบช่องว่างเฉพาะตำแหน่งหัวและท้ายของคำ (TEXT) แต่ไม่ลบช่องว่างระหว่างคำ นอกจาก ช่องว่างระหว่าคำมีมากกว่า 1 ช่อง คำสั่งนี้จะปรับให้เหลือเพียง 1 เท่านั้น

ไวยากรณ์  =TRIM(text)

ตัวอย่าง ใช้คำสั่ง TRIM Function ในการลบช่องว่างในข้อความ ดังนี้

ประโยคในเซลล์ A1 เป็น text ซึ่งในประโยคที่เป็น text นี้จะมีช่องว่างตามที่ทำสีเหลือง

A1=         TRIM Function to remove leading    & trailing spaces from text and     to remove spaces only single spaces between words.        "

เมื่อระบุ TRIM Function ด้วยการเขียน TRIM(A1) ดังรูปที่ 1 

รูปที่ 1 แสดงตัวอย่างการใช้ Trim Function 

จาก TRIM Function ทำการตัดช่องว่างหน้าประโยค และลบช่องว่างระหว่างคำทั้ง 3 ตำแหน่ง 

เงื่อนไขการทำงาน TRIM

• ตัดช่องว่างต้น-ท้ายประโยค
• ตัดช่องว่างระหว่างคำให้เลือก 1 ช่อง

ผลลัพธ์ใน A2 คือ TRIM Function to remove leading & trailing spaces  from text and to remove spaces only single spaces between words. "

👇👇👇👇👇

Clear Function
คำสั่งนี้เป็นการที่ Clear บรรทัด (line breaks) และอักษรที่ไม่สามารถพิมพ์ได้ (non-printable characters) ออกไป 

ไวยากรณ์ =Clear(text)

ตัวอย่าง การใช้คำสั่ง Clear ในการนำบรรทัดและอักษรที่ไม่สามารถพิมพ์ได้ ออก โดยโจทย์ให้ Clear ข้อความในเซลล์ A1 ซึ่งมีข้อความดังนี้
A1="CLEAN function takes a text string and 
returns text that has been "cleaned" of 

line breaks 

and"&CHAR(5)&" other"&CHAR(7)&"  non-printable characters."&CHAR(17) ดังรูปที่ 2

รูปที่ 2 แสดงข้อความที่ต้องการจะใช้คำสั่ง Clear

จากรูปที่ 2 จะเห็นว่า ประโยคในเซลล์ A1 มีการขึ้นบรรทัดใหม่ที่ return, line, and นอกจากนี้ ยังมีอักษรที่ไม่สามารถพิมพ์ได้ CHAR(5), CHAR(7), CHAR(17) เมื่อใช้คำสั่ง Clear Function จะได้ผลลัพธ์ ดังรูปที่ 3

รูปที่ 3 แสดงผลลัพธ์การใช้คำสั่ง Clear Function

Read More

ฟังก์ชั่นในการจัดการข้อความ (Text Function) ในโปรแกรม Excel

ในโปรแกรม Excel จะมีฟังก์ชั่น Text หลายตัวเพื่อให้คุณสามารถนำไปประยุกต์จัดการข้อความได้ตามสถานการณ์ต่าง ๆ ซึ่งในบทความนี้ ขอนำเสนอการใช้ LEN LEFT MID RIGHT TRIM FIND SEARCH ดังนี้

ฟังก์ชั่นในการนับตัวอักษรทั้งหมดในข้อความที่สนใจ (ในที่นี้ text)

ไวยากรณ์ =LEN(text)

ตัวอย่างที่ 1 ต้องการนับตัวอักษรในข้อความ สามารถเขียนได้ ดังนี้

=LEN("แสดงการนำตัวอักษรจากประโยคนี้")

ผลลัพธ์ คือ 29 ตัวอักษร

A1 = LEN Function returns the number of characters in a text string.

=LEN(A1)

ผลลัพธ์ คือ 63 ตัวอักษร

😁😁😁

ฟังก์ชั่นในการดึงตัวอักษรในข้อความ (text) โดยนับจากทางซ้ายของ Text ไปกี่ตัวอักษร (num_char) 

ไวยากรณ์ =LEFT(text, [num_chars])

หลักการ

• Text (จำเป็น) คือ ข้อความที่ต้องการจะดึงมาแสดง
• num_chars (ไม่จำเป็น) คือ จำนวนอักษรต้องการให้แสดง โดยเริ่มนับจากทางซ้ายของข้อความ

ตัวอย่างที่ 2 ต้องการดึงตัวอักษรจากทางซ้ายของข้อความ ดังนี้
A1 = LEFT function extracts a given number of characters from the left side of a supplied text string.

=LEFT(A1,14)

ผลลัพธ์ คือ LEFT function

😁😁😁

ฟังก์ชั่นในการดึงตัวอักษรในข้อความ (text) ซึ่งสามารถเริ่มดึงข้อความได้ตรงไหนของข้อความก็ได้ โดยระบุตำแหน่งที่ต้องการเริ่มให้ดึง (start_num) และ ต้องการดึงไปกี่ตัว (num_chars)

ไวยากรณ์ =MID(text,start_num,num_chars)

หลักการ

• Text (จำเป็น) คือ ข้อความที่ต้องการจะตัด
• start_num คือ จำนวนอักษรแรกที่ต้องการให้ตัด
• num_char คือ จำนวนอักษรสุดท้ายที่ต้องการให้ตัด

ตัวอย่างที่ 3 ต้องการดึงตัวอักษรในข้อความจากตรงกลางไป 10 ตัวอักษร ดังนี้
A1 = MID function extracts a given number of characters from the middle of a supplied text string.

=MID (A1,42,10)

ผลลัพธ์ คือ characters

😁😁😁

ฟังก์ชั่นในการดึงตัวอักษรในข้อความ (text) โดยเริ่มต้นนับจากทางขวาของ Text ไปกี่ตัวอักษร (num_char)

ไวยากรณ์ =RIGHT(text, [num_chars])

ตัวอย่างที่ 4 ต้องการดึงตัวอักษรจากทางขวาของข้อความ ดังนี้
A1 = RIGHT function extracts a given number of characters from the right side of a supplied text string.

=RIGHT(A1,12)

ผลลัพธ์ คือ text string.

😁😁😁

ฟังก์ชั่นในการตัดช่องว่าง ที่อยู่ข้างหน้า และ ข้างหลังของข้อความที่กำหนดไว้ 

ไวยากรณ์ =TRIM(text)

ตัวอย่างที 5 ต้องการตัดช่องว่าง ดังนี้

=TRIM(" TRIM function ")

ผลลัพธ์ คือ TRIM function

😁😁😁

ฟังก์ชั่นในการค้นหาคำที่ต้องการ ว่าอยู่ในตำแหน่งอะไรของข้อความนั้น 

ไวยากรณ์   =FIND(find_text, within_text, [start_num])

หลักการ

• find_text คือ คำที่คุณต้องการหา
• within_text คือ คำนั้นต้องการหาจากที่ไหน
• [start_num] (ไม่จำเป็น) คือ ตำแหน่งที่คุณต้องการเริ่มต้นหา

ตัวอย่างที 6 ต้องการค้นกาคำ ดังนี้
A1 = FIND function returns the position (as a number) of one text string inside another. 

=FIND("number",A1)

ผลลัพธ์ คือ 42

=FIND("returns","FIND function returns the position (as a number) of one text string inside another. ")

ผลลัพธ์ คือ 15

=FIND("FUNCTION",A1)

ผลลัพธ์ คือ #VALUE!

สังเกตของ Find Function

• ฟังก์ชั่นนี้จะคืนค่าตำแหน่งตัวแรก ของคำที่คุณค้นหา (find_text)
• ฟังก์ชั่นนี้จะคืนค่าตำแหน่งของคำที่คุณค้นหา (within_text)
• ฟังก์ชั่นนี้จะแสดง #VALUE! เมื่อค้นหาคำ find_text ไม่พบใน within_text
• ฟังก์ชั่นนี้จะสนใจตัวอักษรเล็ก-ใหญ่ ถ้าเป็นคำเดียวกันแต่ต่างกันที่ตัวอักษรเล็ก-ใหญ่ เมื่อไม่พบโปรแกรมจะแสดง #VALUE! 

แต่ถ้าไม่ต้องการให้โปรแกรม serious กับตัวอักษรเล็ก-ใหญ่ ต้องใช้ฟังก์ชั่นการค้นหาด้วย Search() 

😁😁😁

ฟังก์ชั่นที่ใช้สำหรับค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่ต้องการค้นหา โดยใช้การหาแบบ Wildcard 

ไวยากรณ์ =SEARCH(find_text, within_text, [start_num])

ตัวอย่างที่ 7 

A1 = SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.

=SEARCH("RETURNS",A1)

ผลลัพธ์ คือ 17

😁😁😁

Read More

ฟังก์ชั่นในการจัดการ Text ในโปรแกรมเอ็กเซล

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

😉😉😉😉😉

TEXT Function 

เป็นฟังก์ชั่นในเอ็กเซลที่ทำการแปลง (Convert) ตัวเลขให้เป็นข้อความ ตามรูปแบบที่คุณ ๆ เป็นผู้กำหนดด้วยตนเอง

ไวยากรณ์ของ Text Function

=TEXT(Value, format_text)

หลักเกณฑ์

• Value เป็นการระบุตัวเลขที่ต้องการแปลงเป็นข้อความ ซึ่งคุณ ๆ จะใส่ได้ทั้งที่เป็นตัวเลข (Number), วันที่ (Date), การอ้างอิงเซลล์ที่คืนค่ามาเป็นตัวเลข และ ฟังก์ชั่นอื่นที่คืนค่ามาเป็นตัวเลขหรือวันที่
• Format_text เป็นการระบุรูปแบบที่คุณต้องการจะแสดง ซึ่งควรจะอยู่ในเครื่องหมายคำพูด (quotation marks) เช่น "DD/MMMM/YYYY" เป็นต้น

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

คำถาม คือ แล้ว Format_text มีเงื่อนไขในการกำหนดรูปแบบหรือไม 😱😱😱😱 

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

รูปแบบข้อความ	คำอธิบาย	ตัวอย่างการใช้รูปแบบ
#	แสดงตัวเลข	=TEXT(12,"#,###.##") หมายถึง ให้แสดงตัวเลข 12 โดยไม่แสดง 0 หน้า 12 และไม่มี 0 หลัง 12 [ผลลัพธ์ คือ 12.]
0	แสดงตัวเลข	=TEXT(12,"0,000.00") หมายถึง ให้แสดงตัวเลข 12 ด้วยรูปแบบ 0,000.00 [ผลลัพธ์ คือ 0,012.00]

ส่วนของวันที่ (Date) และเวลา (Time) มีเงื่อนไขในการกำหนดรูปแบบ ดังนี้

รูปแบบ	คำอธิบาย	ตัวอย่างการใช้รูปแบบ
d	แสดงวัน	d - แสดงตัวเลข 1 - 2 หลัก โดยไม่มีศูนย์นำหน้า เช่น 1 ถึง 31 dd - แสดงตัวเลข 1-2 หลัก โดยมีศูนย์นำหน้า เช่น 01 ถึง 31 ddd - แสดงตัวอักษรย่อ เช่น จ. ถึง อา. หรือ Mon ถึง Sun dddd - แสดงชื่อเต็ม เช่น จันทร์ ถึง อาทิตย์ หรือ Monday ถึง Sunday
m	แสดงเดือน	m - แสดงตัวเลข 1 - 2 หลัก โดยไม่มีศูนย์นำหน้า เช่น 1 ถึง 12 mm - แสดงตัวเลข 1-2 หลัก โดยมีศูนย์นำหน้า เช่น 01 ถึง 12 mmm - แสดงตัวอักษรย่อ 3 ตัวอักษร เช่น ม.ค ถึง ธ.ค. หรือ Jan ถึง Dec  mmmm - แสดงชื่อเต็ม เช่น มกราคม ถึง ธันวาคม หรือ January ถึง December
y	แสดงปี	yy - แสดงตัวเลข 2 หลัก เช่น  60 หมายถึง 2560 หรือ 16 หมายถึง 2016  yyyy - แสดงตัวเลข 4 หลัก เช่น  2560, 2016
h	แสดงชั่วโมง	h - แสดงตัวเลข 1-2 หลัก โดยมีศูนย์นำหน้า เช่น 1 ถึง 24 hh - แสดงตัวเลข 2 หลัก โดยมีศูนย์นำหน้า เช่น 01 ถึง 24
m	แสดงนาที	m - แสดงตัวเลข 1-2 หลัก โดยมีศูนย์นำหน้า เช่น 1 ถึง 60 mm - แสดงตัวเลข 2 หลัก โดยมีศูนย์นำหน้า เช่น 01 ถึง 60)
s	แสดงวินาที	s - แสดงตัวเลข 1-2 หลัก โดยมีศูนย์นำหน้า เช่น 1 ถึง 60 ss - แสดงตัวเลข 2 หลัก โดยมีศูนย์นำหน้า เช่น 01 ถึง 60
AM/PM	แสดงเวลาเป็น 12 ชั่วโมง, ลงท้าย "AM" หรือ "PM"

นอกจากนี้ คุณสามารถใส่เครื่องหมายพิเศษ ลงไปในรูปแบบที่ต้องการได้ด้วย ดังนี้ 

สัญลักษณ์	ความหมาย
+ and -	เครื่องหมายบวกและลบ
( )	เครื่องหมายวงเล็บ
:	เครื่องหมายทวิภาค (Colon)
^	เครื่องหมายหมวก (Caret)
'	เครื่องหมายลูกน้ำ (Apostrophe)
{ }	เครื่องหมายปีกกา (Curly brackets)
< >	เครื่องหมายมากกว่าและน้อยกว่า (Less-than and greater than signs)
=	เครื่องหมายเท่ากับ (Equal sign)
/	เครื่องหมายทับ (Forward slash)
!	เครื่องหมายอัศเจรีย์ (Exclamation point)
&	เครื่องหมายแอนด์ (Ampersand)
~	เครื่องหมายตัวหนอน (Tilde)
	ช่องว่าง (Space character)

ตัวอย่างที่ 1 การเขียน Text Function ต่าง ๆ เช่น ตัวเลข วันที่

รูปที่ 1 แสดงตัวอย่างการเขียน Text Function

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

😁😁😁😁😁

ตัวอย่างที่ 2 การเขียน Text Function มารวมกับข้อความ ซึ่งสามารถทำได้ 2 แบบ ดังนี้ 

แบบที่ 2.1: เป็นการเขียน Text Function มารวมกับข้อความธรรมดา จะใช้ & ในการผสานข้อความกับฟังก์ชั่นนั้นเอง

="วัน" &TEXT(14/10/2016,"dddd") 

ผลลัพธ์ คือ วันเสาร์

แบบที่ 2.2: เป็นการนำ CONCATENATE Function มารวมกับ Text Function (ตัวที่พื้นเป็นสีเทา)

=CONCATENATE("วัน",TEXT(14/10/2016,"dddd"))

ผลลัพธ์ คือ วันเสาร์ เหมือนกันกับแบบที่ 2.1

ปล. ใครไม่รู้เรื่อง CONCATENATE สามารถไปทบทวนในบทความ การผสานหรือรวมข้อความด้วยฟังก์ชั่น CONCATENATE ของโปรแกรม Excel

👀 👀 👀 👀 👀

Read More

SUMPRODUCT() ใช้ในการหาผลรวมของคอลัมน์ตั้งแต่ 2 คอลัมน์คูณกัน

จากบทความ การหาผลรวมด้วย SUM() / SUMIF() / SUMIFS() ใน Excel อาจมีบางคนที่ว่า ก็ยังต้องทำงานหลายขั้นตอน ในการหาผลรวมที่มีเงื่อนไขอยู่ดี 

แล้วอะไร คือ การทำงาน 2 ขั้นตอนละ 😫😫

รูปที่ 1 แสดงตัวอย่างการทำงาน 2 ขั้นตอน

การทำงาน 2 ขั้นตอนที่ว่านั้น ก็คือ คุณต้องนำราคา (Price) มาคูณกับจำนวนผลการขาย (Amount) ก่อน ถึงจะหาผลรวมได้นั้นเอง (คุณต้องทำงานที่เป็นตัวเลขสีส้ม คอลัมน์ D) 😨😨 แล้วจะมีวิธีการอะไรที่ลดขั้นตอนการทำงานลงได้บ้างละ 

ในบทความนี้ อธิบายวิธีการทำงานที่จะลดขั้นตอนการทำงานตามตัวอย่างข้างบนได้อย่างมีประสิทธิภาพที่เดียว ซึ่งวิธีการนั้น ก็คือ การเขียนฟังก์ชั่น SUMPRODUCT() 

SUMPRODUCT() Function

หมายถึง ฟังก์ชั่นที่ใช้ในการหาผลรวม (บวก) ของคอลัมน์ตั้งแต่ 2 คอลัมน์คูณกัน

ไวยากรณ์

=SUMPRODUCT(array1 [array2], [array3], ... )

หลักการ

• SUMPRODUCT ต้องการข้อมูลที่เป็น array หรือ Range เช่น เป็นข้อมูลในคอลัมน์
• ถ้ามีมากกว่า 1 คอลัมน์ ข้อมูลนั้น ต้องมีจำนวนแถวเท่ากัน
• เราสามารถเพิ่มความยืดหยุ่น โดยการกำหนดเงื่อนไขในแต่ละคอลัมน์ได้ โดยการกำหนดเงื่อนไข และ ใช้เครื่องหมายคูณ (*) เป็นตัวเชื่อม เงื่อนไขเหล่านั้น

เมื่อคุณรู้ไวยากรณ์ และหลักการของ SUMPRODUCT() แล้ว ปัญหาข้างต้น (ตามรูปที่ 1) เราสามารถลดขั้นตอนการทำงาน จาก 2 ขั้นตอน เหลือเพียง 1 ขั้นตอน ด้วยการเขียนฟังก์ชั่น ดังนี้

=SUMPRODUCT(B2:B6,C2:C6)

คุณ ๆ จะพบว่า ในวงเล็บของฟังก์ชั่นนี้เป็นไปตามหลักการของ SUMPRODUCT คือ มีอากิวเมนต์ 2 ตัว ที่เป็น Array หรือ Range และจำนวนแถวของทั้ง 2 มีจำนวนเท่ากัน นั้นก็คือ B2:B6 และ C2:C6 แต่ถ้าการคำนวณนั้นมีเงื่อนไขเพิ่มเข้ามา เราจะเขียนฟังก์ชั่นนี้อย่างไร

ตัวอย่างที่ 1 ต้องการหาผลรวมของยอดการขายทั้งหมดในตาราง (รูปที่ 1) ยกเว้น Shirt สามารถเขียนได้ ดังนี้

=SUMPRODUCT((A3:A6<>"Shirt")*B3:B6,C3:C6)

คุณ ๆ จะพบว่า เงื่อนไขที่เพิ่มขึ้นมาจะเขียนไว้ข้างหน้า Array หรือ Range และใช้เครื่องหมายคูณ (*) เป็นสัญลักษณ์ในการเชื่อมนั้นเอง

ตัวอย่างที่ 2 ต้องการหาผลรวมยอดการขายทั้งหมดในตาราง (รูปที่ 1) ยกเว้น Shirt และ Skirt  สามารถเขียนได้ ดังนี้

=SUMPRODUCT((A3:A6<>"Shirt")*(A3:A6<>"Skirt")*B3:B6,C3:C6)

จากตัวอย่างที่ 2 นี้ มีเงื่อนไข 2 เงื่อนไขเข้ามา เราก็จะใช้เครื่องหมายคูณ (*) เป็นตัวเชื่อมกับเงื่อนไขทั้ง 2 เช่นกัน

Read More

การผสานหรือรวมข้อความด้วยฟังก์ชั่น CONCATENATE ของโปรแกรม Excel

Concatenate Function
เป็นฟังก์ชั่นในการผสาน/รวมคำที่เป็นข้อความ (Text) เข้าด้วยกันในเซลล์เดียว นอกจากนั้น ยังสามารถนำฟังก์ชั่นอื่น แทรกเข้าไปใน Concatenate Function ได้เช่นกัน

ไวยากรณ์

  =CONCATENATE(text1, [text2], …)

กฏการใช้ ✎

1. ฟังก์ชั่นนี้ต้องประกอบด้วยอาร์กิวเมนต์ที่เป็นข้อความ (String) อย่างน้อย 1 ตัว 
2. ฟังก์ชั่นนี้ สามารถผสาน/รวมข้อความได้ถึง 225 ข้อความ (Strings) หรือตัวอักษรได้ถึง 8,192 ตัวอักษร (Characters)
3. แม้ข้อความในเซลล์ก่อนที่จะใช้ฟังก์ชั่นนี้จะไม่เป็นข้อความ (String) แต่ผลลัพธ์ที่ได้หลังจากผ่านฟังก์ชั่นนี้แล้ว จะเป็นข้อความ (String) ทั้งหมด
4. ฟังก์ชั่นนี้ ไม่รู้จักอาร์กิวเมนต์ที่เป็น Array แบบนี้ =CONCATENATE(A1:A3) ดังนั้น ควรเขียนแบบนี้แทน =CONCATENATE(A1, A2, A3) 
5. เมื่อคุณเขียนฟังก์ชั่นนี้ Error ผลลัพธ์ที่โปรแกรมแสดงออกมา คือ #VALUE!

୫୫୫୫୫୫୫

ตัวอย่างที่ 1 การรวมข้อมูล String จากเซลล์หลายเซลล์ เป็นเซลล์เดียว ดังรูปที่ 1

รูปที่ 1 แสดงการใช้ฟังก์ชั่น concatenate

จากรูปที่ 1 เราได้เขียนฟังก์ชั่นในเซลล์ E1 ดังนี้ 

=concatenate(A1,B1,C1.D1) 

ข้อควรระวัง ผลลัพธ์จากการใช้ฟังก์ชั่นนี้ จะไม่มีการเว้นช่องว่างระหว่างอาร์กิวเมนต์ (Text 1, Text 2,....) ให้ ดังนั้น ถ้าคุณต้องการผสาน/รวมข้อความ ก็ควรมีการเว้นช่องว่างเอง " " หรือ เครื่องหมายที่ใช้คั่นข้อความต่างๆ เช่น , / - เป็นต้น ดังนี้

=CONCATENATE(A1," ",B1,"-",C1,"-",D1) หรือ 

=CONCATENATE(A2," ",B2," / ",C2," / ",D2) หรือ 

=CONCATENATE(A3," , ",B3," , ",C3," , ",D3) 

ดังรูปที่ 2

รูปที่ 2 แสดงการใช้ฟังก์ชั่น concatenate 

ตัวอย่างที่ 2 การรวมเซลล์ที่เก็บข้อความ (String) และเซลล์ที่เก็บวันที่ (Date) ให้อยู่ในเซลล์เดียว

ข้อควรระวัง ฟังก์ชั่น Concatenate นี้จะไม่สามารถใช้ฟังก์ชั่นอื่นภายในฟังก์ชั่น Concatenate ได้ แต่หากจำเป็นต้องใช้ฟังก์ชั่นอื่น คุณ ๆ สามารถใช้ Text() รวมด้วยได้ ดังรูปที่ 3

รูปที่ 3 การใช้ concatenate ใน excel

จากรูปที่ 3 ได้คุณ ๆ ต้องรวมคำใน A1 B1 ในเซลล์ C1 โดยต้องระบุวันที่ปัจจุบันของเครื่อง ก็ต้องใช้ฟังก์ชั่น Today() แต่เมื่อต้องเขียนฟังก์ชั่นอื่นในฟังก์ชั่น Concatenate ดังนั้น คุณต้องแปลงค่าที่ได้จากฟังก์ชั่น Today() ด้วย Text() ดังนี้

=CONCATENATE(A1,B1," ",TEXT(TODAY(),"DD MM YYYY"))

นอกจาก ฟังก์ชั่น Concatenate โปรแกรม Excel ยังมี "&" operator เพื่อใช้ในการรวม ๆ คำได้เช่นกัน 

="วันนี้เป็นวัน " & "07 10 2017"

แล้วระหว่าง Concatenate และ "&" operator อะไรที่จะมีประสิทธิภาพมากกว่ากัน

Read More

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