ฟังก์ชั่นในการจัดการ 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

2 วิธีใช้สร้างผลรวมย่อย (Subtotal) ในโปรแกรม Excel

ฟังก์ชั่นในโปรแกรม Excel หนึ่งที่หลายคนรู้จักคงจะไม่พ้นฟังก์ชั่นที่เกี่ยวกับการหาผลรวมแน่นอน ซึ่งก่อนหน้านี้ เราได้อธิบายอย่าง การหาผลรวมด้วย SUM() / SUMIF() / SUMIFS ใน Excel หรือ SUBPRODUCT() ใช้ในการหาผลรวมของคอลัมน์ตั้งแต่ 2 คอลัมน์คูณกัน 

ในบทความนี้ เราเสนอตัวอย่างการนำ SUBTOTAL Function มาช่วยลดขั้นตอนการทำงานของคุณ ๆ ว่าแล้วเข้าเรื่องสักที่ดีกว่านะคะ 😉😉😉

SUBTOTAL Function
หมายถึง ฟังก์ชั่นที่ช่วยคำนวณเซลล์ย่อย ๆ ของผลรวมในตาราง โดยมีการทำอยู่ 2 แบบ ดังนี้

ไวยากรณ์

=Subtotal(function_num, ref1, [ref2], ...)

หลักการ

• function_num คือ คุณต้องระบุตัวเลข ซึ่งมี 2 แบบ คือ 1-11 และ 101-111 เพื่อคืนค่าตาม Function ที่ต้องการ โดยเงื่อนไขในการกำหนดอยู่ในตารางที่ 1 
• ref1, [ref2], ... คือ คุณระบุเลข 1-254 การอ้างอิงเพื่อให้ฟังก์ชั่นหาผลรวมย่อยของข้อมูล

ตารางที่ 1 แสดงความหมายของตัวเลขใน Function_num 

Function	คำนวณค่าที่ซ่อน	ไม่คำนวณค่าที่ซ่อน
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV	7	107
STDEVP	8	108
SUM	9	109
VAR	10	110
VARP	11	111

หมายเหตุ

• ในตาราง Function แต่ละเลข (เช่น 1 กับ 101 / 2 กับ 102 / ...) จะถูกจับคู่ไว้อยู่แล้ว เมื่อคุณเลือก
• Function_num เป็นค่าระหว่าง 1-11 ฟังก์ชั่น SUBTOTAL จะคำนวณตัวเลขทุกตัว แม้ว่าตัวเลขจะถูกซ่อนไว้
• Function_num เป็นค่าระหว่าง 101-111 ฟังก์ชั่น SUBTOTAL จะ ไม่ นำตัวเลขที่ถูกซ่อนไว้มาคำนวณ
• แต่ถ้าคุณทำการกรองข้อมูลด้วย Autofilter แล้วฟังก์ชั่น SUBTOTAL จะไม่สนใจว่าคุณกำหนด Function_Num เป็นอะไร มันจะ ไม่ นำค่าในตารางที่คุณกรองข้อมูลนั้นมาคำนวณเด็ดขาด
• ฟังก์ชั่น SUBTOTAL จะสามารถทำงานตามที่บอกไว้กับข้อมูลแนวตั้ง แต่ถ้าข้อมูลเป็นลักษณะแนวนอน (Horizontal) ฟังก์ชั่น SUBTOTAL จะนำค่าที่ซ่อนไว้มารวมด้วย

ตัวอย่างที่ 1 เขียนฟังก์ชั่นแบบ Command ดังนี้

รูปที่ 1 แสดงตัวอย่าง Subtotal 

จากรูปที่ 1 ถ้าเขียน =SUBTOTAL(9,B1:B3) หรือ =SUBTOTAL(109,B1:B3) จะได้ผลลัพธ์เป็น 9 ทั้ง 2 ฟังก์ชั่น และมีความหมายว่าให้ทำการบวกค่าระหว่าง B1 ถึง B3 แต่ถ้าคุณซ่อน Row 2 ไป ผลลัพธ์ที่ได้จะเป็น 9 และ 5 ตามลำดับ 

แบบที่ 2 ใช้ Subtotal แบบ Outline ดังนี้

รูปที่ 2 แสดงการทำ Subtotal แบบ Outline

ในการทำ Subtotal แบบ Outline จะช่วยให้คุณไม่ต้องมาเขียนสูตรที่ละส่วน แต่การจะใช้เครื่องมือตัวนี้ เราต้องมารู้ก่อนว่ามันมีคุณลักษณะการทำงานอย่างไรบ้าง 

ขั้นตอนการทำงาน Subtotal แบบ Outline
1. ก่อนจะใช้เครื่องมือ Subtotal ได้ประสิทธิภาพที่สุด คุณควรจัดเตรียมข้อมูลให้โปรแกรมสามารถจัดการบริหารกับข้อมูลได้ง่ายขึ้นนั้นก็คือ ต้องใส่ข้อมูลให้เต็มทุกเซลล์ในตาราง / ทำการจัดเรียง (Sort) ข้อมูล ที่จะทำ Subtotal แบบ Outline ดังรูปที่ 3

รูปที่ 3 แสดงการเตรียมข้อมูลให้เหมาะสมกับการทำ Subtotal แบบ Outline

ตามรูปที่ 3 เราได้ทำการเติมข้อมูลให้เต็มและจัดเรียงข้อมูล ในคอลัมน์ปี (Year) นั้นเอง

2. เลือกเซลล์ส่วนใดส่วนหนึ่งบนตาราง และ เลือกที่ Data >> Subtotal โปรแกรมจะแสดง PopUp ดังรูปที่ 4

รูปที่ 4 แสดง Subtotal outline 

แต่ละส่วนของ Subtotal Popup มีดังนี้

• At each change in: เป็นการเลือกคอลัมน์คุม ตารางที่ทำ Subtotal 
• Use Function: เป็นการเลือกว่าจะใช้ Function ใดในการทำ Subtotal 
• Add subtotal to : เป็นการเลือกคอลัมน์ที่ต้องการให้คำนวณ ตาม Function ที่เลือกไว้ 
• Replace current subtotal เป็นการกำหนดให้สามารถเพิ่มผลรวมย่อยได้ โดยจะถูกใช้เมื่อคุณทำ Subtotal ไปก่อนหน้านี้แล้ว แต่ต้องการจะเพิ่ม Subtotal กับคอลัมน์อื่นเพิ่ม โดยไม่ให้กระทบกับข้อมูลที่ทำ Subtotal ไว้ก่อนหน้านี้นั้นเอง
• Page break between groups เป็นการกำหนดให้แบ่งเป็นหน้า
• Summary below data เป็นการกำหนดให้แสดงผลสรุปไว้ท้ายของตาราง

โดยในรูปที่ 4 คือการเลือกคอลัมน์ปี (Year) เป็นคอลัมน์คุมการแสดงผลรวมย่อย (Subtotal) ซึ่งจากการเติมข้อมูลให้เต็มทั้งตารางและจัดเรียงข้อมูลไว้ก่อนหน้านี้แล้ว คุณจะได้ข้อมูลผลรวมย่อยที่นำไปวิเคราะห์ได้ ดังรูปที่ 5

รูปที่ 5 แสดงผลลัพธ์การทำผลรวมย่อย (Subtotal - Outline)

แต่หากคุณขี้เกียจ 😝😝😝 ไม่เติมข้อมูลให้เต็มหรือจัดเรียงข้อมูลให้เรียบร้อยก่อน คุณอาจจะได้ผลรวมย่อย (Subtotal) หลายรายการต่อข้อมูลในคอลัมน์เดียวกัน ซึ่งคุณก็คงต้องเริ่มต้นทำใหม่ ซึ่งถ้าคุณต้องการเริ่มต้นใหม่สามารถคลิกที่ปุ่ม Remove all ได้

นอกจากนี้ คุณยังสามารถทำการเพิ่ม Subtotal ที่ 2 ในตารางได้ โดยคลิกที่ Replace current subtotal แล้วเลือกคอลัมน์ที่ต้องการจะทำ Subtotal ที่ 2 ในส่วน Add subtotal to ดังรูปที่ 6 

รูปที่ 6 แสดงตัวอย่างการทำผลรวมย่อยที่2 (Subtotal แบบ outline) 

โดยในรูปที่ 6 จะทำการคลิกที่ Replace current subtotal และเลือกส่วนของ Add subtotal to: Product ผลลัพธ์ที่ได้เพิ่ม Subtotal ที่ 2 จะแสดงดังรูปที่ 7

รูปที่ 7 แสดงผลลัพธ์ของการทำผลรวมย่อยที่ 2 (Subtotal แบบ Outline)

หวังว่าบทความนี้ คงพอจะให้คุณสามารถนำไปใช้สรุปผลรวมย่อยกับงานของคุณ ๆ ได้อย่างดีนะคะ 

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

Flash Fill เป็นการจัดรูปแบบให้กับข้อมูลใน Excel

Flash Fill 

เป็นเครื่องมือใหม่ เริ่มมีใช้ตั้งแต่ Excel Version 2013 ขึ้้นไป ซึ่งความสามารถของ Flash Fill ก็เพื่อใช้จัดรูปแบบข้อมูลต่าง ๆ ได้ตามต้องการ

คุณลักษณะของ Flash Fill มี 2 ลักษณะ ดังนี้
1. การสกัดหรือดึง (Extract) ข้อมูลตามรูปแบบที่ต้องการออกมา
2. การรวม (Join) ข้อมูลตามรูปแบบที่ต้องการออกมา

จากรูปที่ 1 เราจะแสดงตัวอย่างการสกัด และ การรวม ข้อมูลตามรูปแบบที่ต้องการด้วยวิธี Flash Fill 

รูปที่ 1 แสดงตัวอย่างการสกัดข้อมูลออกมาด้วย Flash Fill

ตัวอย่างที่ 1 การสกัดหรือดึงข้อมูลที่เป็นตัวอักษร ตามรูปแบบที่ต้องการ ออกมาแสดง 

รูปที่ 2 แสดงวิธีการใช้ Flash Fill ใน Excel

จากรูปที่ 1 จะมีข้อมูลพื้นฐาน 2 คอลัมน์ คือ A, B ซึ่ง โจทย์ต้องการให้แยกชื่อ สกุล ออกจากเซลล์ B มาใส่ในคอลัมน์ C และ D ตามลำดับ นอกจากนี้ คอลัมน์ E ใช้จัดรูปแบบ email Addree จากชื่อ-สกุล (คอลัมน์ B)

พิมพ์ชื่อที่คอลัมน์ C กับ พิมพ์นามสกุลที่คอลัมน์ D และ พิมพ์ e-mail ที่คอลัมน์ E ที่ด้านบนสุด เพื่อให้โปรแกรมเอ็กเซล ใช้เป็นตัวอย่างในการทำ Flash Fill ดังรูปที่ 2

เมื่อเราได้รูปแบบข้อมูลที่ต้องการครบแล้ว คุณก็วางเมาส์ที่ C3 เพื่อทำการสกัดหรือดึงข้อมูล "ชื่อ" จากคอลัมน์ B2 แล้วไปที่ Data >> Flash Fill หรือ Ctrl+E ดังรูปที่ 3

รูปที่ 3 แสดงวิธีการทำ Flash Fill ในเอ็กเซล

หลังจากนั้น ทำ Flash Fill ในส่วนอื่น ๆ ต่อไป โดยในตัวอย่างนี้ ให้วางเมาส์ที่ D3 เพื่อทำการสกัดหรือดึงข้อมูล "นามสกุล" จากคอลัมน์ B2 แล้วไปที่ Data >> Flash Fill หรือ Ctrl+E 

ตัวอย่างที่ 2 การร่วมข้อมูลที่เป็นตัวอักษร ตามรูปแบบที่ต้องการ ออกมาแสดง 

จากรูปที่ 1 จะเหลือส่วนของ e-mail ซึ่งก็ต้องวางเมาส์ที่ E3 เพื่อทำการสกัดหรือดึงข้อมูล "e-mail" จากคอลัมน์ B2 แล้วไปที่ Data >> Flash Fill หรือ Ctrl+E ดังรูปที่ 4

รูปที่ 4 แสดงตัวอย่างหลังจากการทำ Flash Fill

ตัวอย่างที่ 3 การจัดรูปแบบข้อมูลตัวเลขได้ง่าย ๆ เหมือนข้อมูลที่เป็นตัวอักษร เช่น การจัดรูปแบบของเบอร์โทรศัพท์ได้ ดังรูปที่ 5

รูปที่ 5 แสดงตัวอย่างการทำ Flash Fill ที่เป็นตัวเลข

จากตัวอย่างที่ 3 นี้เราจะอธิบายการจัดรูปแบบตัวเลข ตามที่เราต้องการ ดังนั้น คุณก็ต้องทำเหมือนตัวอย่างก่อนหน้านี้ คือ วางเมาส์ที่ B2 แล้วพิมพ์รูปแบบที่คุณต้องการ ดังรูปที่ 6

รูปที่ 6 แสดงตัวอย่างการใส่รูปแบบตัวเลขที่ต้องการ เพื่อเตรียมทำ Flash Fill 

เมื่อเราได้รูปแบบที่ต้องการแล้ว ก็ต้องวางเมาส์ที่ B3 แล้วคลิก Data >> Flash Fill หรือ Ctrl+E ดังรูปที่ 7

รูปที่ 7 แสดงผลการทำ Flash Fill ข้อมูลที่เป็นตัวเลข

จากตัวอย่างทั้ง 3 จะพบว่า เครื่องมือ Flash Fill มีคุณลักษณะที่ใช้ในการจัดการรูปแบบทั้งตัวเลข ตัวอักษร ได้อย่างง่าย ๆ แต่เครื่องมือนี้ก็ยังมีข้อจำกัดเช่นกัน 

ข้อจำกัดของ Flash Fill 
คือ ถ้าข้อมูลพื้นฐานมีการเปลี่ยนแปลง อย่างเพิ่มข้อมูลใหม่ ๆ ขึ้นมา ส่วนที่เป็นผลลัพธ์จะไม่เปลีี่ยนตามข้อมูลพื้นฐานนั้น เพราะ ว่าเครื่องมือนี้เป็นเหมือนการ Stamp ค่าคงที่ลงไป ไม่ได้ มีคุณลักษณะเหมือนการใช้สูตรหรือฟังก์ชั่น ที่จะปรับเปลี่ยนไปตามข้อมูลพื้นฐานนั้นเอง

😅😄😄😄😄😄😄

Read More

3 วิธีในการผสานหรือรวมข้อความของ Excel เป็นเซลล์เดียว

ใครคิดว่า Excel ต้องเก็บข้อมูลเป็นโครงสร้าง (Structure) เสมอบ้าง ... ยกมือขึ้น 🙋🙋🙋

แม้โปรแกรม Excel จะมีลักษณะเป็นโครงสร้างหรือตาราง แต่บ้างครั้ง ข้อมูลที่เราเก็บลงโปรแกรม Excel อาจจะไม่ได้มีลักษณะเป็นโครงสร้าง (Structure) เสมอไป เพราะว่า หลายครั้งที่เรามีการผสาน/รวมคำจาก 2 เซลล์ หรือมากกว่านั้นลงใน 1 เซลล์ หรือ สิ่งที่ตรงข้ามกันอย่างการแยกข้อมูลออกเป็นแต่ละเซลล์ 

ในบทความนี้ เราจะมาพูดถึงกรณีการผสาน/รวมคำจาก 2 เซลล์ หรือมากกว่านั้น มารวมกัน ซึ่งใน Excel เรามีวิธีในการจัดการได้ 3 แบบ คือ Merge, Concatenate และ Flash Fill

ความแตกต่างระหว่าง Merge และ Concatenate 

Merge 

👉 ผลลัพธ์ที่ได้จากการ Merge จะแสดงเพียงคำแรกของเซลล์ ที่คุณทำการ Merge ส่วนคำอื่นของเซลล์อื่น ๆ จะหายไป ดังรูปที่ 1

รูปที่ 1 แสดงผลลัพธ์ของการ Merge

👉 พื้นที่หลังการ Merge ขนาดเท่ากับจำนวนเซลล์ ที่คุณทำการ Merge 

วิธีการทำผสาน/รวมข้อมูลด้วยการ Merge
👉 เลือกเซลล์ที่ต้องจะรวมคำ ซึ่งตามรูปที่ 1 คือ A1 และ B1
👉 เลือกปุ่ม Home >> Merge and Center 
👉 Pop Up จะแจ้งว่า ถ้าคุณคลิกปุ่ม Ok จะแสดงข้อความเพียงเซลล์แรกเซลล์เดียวเท่านั้น 

ปล. ควรประยุกต์ กรณีผสาน/รวมคำเพียงไม่มา ให้ทำการ Merge ให้ได้พื้นที่ที่ต้องการก่อน แล้วค่อยพิมพ์ข้อความที่ต้องการลงไป แต่ถ้าต้องผสาน/รวมคำหลายแถว (Row) คุณควรจะเขียน Concatenate Function แทน

⌨ ⌨ ⌨ ⌨ ⌨

Concatenate 

👉 ผลลัพธ์ที่ได้จากการ Concatenate จะได้รวมคำทั้งหมดในเซลล์ใหม่เพียงเซลล์เดียว
👉 พื้นที่หลังการทำ Concatenate จะได้แสดงข้อมูลทั้งหมดในเซลล์เพียงเซลล์เดียว

วิธีการทำผสาน/รวมข้อมูลด้วยการ Concatenate 
👉 เขียนฟังก์ชั่น Concatenate สามารถอ่านได้ที่บทความเรื่อง การผสานหรือรวมข้อความด้วยฟังก์ชั่น CONCATENATE ของโปรแกรม Excel


นอกจาก ทั้ง 2 แบบข้างบนแล้ว ใน Excel ยังมีเครื่องมือ Flash Fill ที่สามารถทำการผสาน/รวมคำจาก 2 เซลล์ หรือมากกว่านั้น มารวมกันได้เช่นกัน โดยความหมายของ Flash Fill คือ เครื่องมือที่ใช้ในการจัดรูปแบบทั้งข้อความหรือตัวเลข ตามที่คุณต้องการได้อย่างง่ายๆ

⌨ ⌨ ⌨ ⌨ ⌨

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

รูปที่ 2 แสดงตัวอย่างการใช้และผลลัพธ์ของเครื่องมือ Flash Fill เพื่อผสาน/รวมคำ

วิธีการทำผสาน/รวมข้อมูลด้วยการ Flash Fill 
👉 เขียนรูปแบบที่เซลล์ C1 เพื่อเป็นต้นแบบ 
👉 คลิกเซลล์ที่ต้องการจะคัดลอกรูปแบบ ในที่นี้คลิกที่เซลล์ C2 
👉 ไปที่ Data >> Flash Fill หรือ กด Ctrl+E
👉 ผลลัพธ์จะแสดงตาม C1 

มาถึงตอนท้ายของบทความนี้ คุณ ๆ จะพบว่าในโปรแกรม Excel มีวิธีการได้หลายแบบ ไม่ว่าจะเป็น Merge, Concatenate และ Flash Fill ซึ่งตามคุณสมบัติของ Flash Fill ยังมีความสามารถอื่น ๆ หากสนใจให้อ่านบทความเรื่อง ความสามารถของ Flash Fill 

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