ุ6 ปุ่มที่ให้ผล Paste option ใน Excel ที่แตกต่างกันไป

ในการใช้คุณสมบัติการวาง (Paste) ได้มีการปรับเปลี่ยนรูปแบบมาอยู่ตลอดเวลา เช่น Excel 2003 วาง Paste special บนเมนู Edit ส่วน Excel 2007 และ Excel 2010 ย้ายไปไว้ที่ Clipboard group ใน Home Tab ส่วน Excel  2013 ขึ้นมา คุณ ๆ สามารถใช้ Paste option จาก Home tab หรือการคลิกเมาส์ขวา นั้นเอง

บทความนี้ จะอธิบายการใช้ Paste option ในแบบต่างๆ (เฉพาะ 6 ปุ่มแรก) ด้วยวิธีการคลิกขวาที่เซลล์ที่ คุณ ๆ ต้องการวาง

รูปที่ 1 Paste Option

จากรูปที่ 1 จะมีตัวเลือกของปุ่ม Paste 6 แบบ ซึ่งมีรายละเอียด ดังนี้
ปุ่มที่ 1 คือ Paste (P) สามารถทำการวางทั้งข้อความ (Text) รูปแบบ (Format) และสูตร (Formula)
ตัวอย่าง

รูปที่ 2 แสดงผลการคัดลอง Paste (P) Option

จากรูปที่ 2 ประกอบด้วย 2 ตารางย่อย คือ ตารางบน แสดงให้เห็นว่าเซลล์ B2 มีการเขียนสูตรและจัดรูปแบบ (Format) ให้กับข้อความ แต่ในตารางล่าง แสดงผลการคัดลอกข้อความในเซลล์ B2 มาว่างในเซลล์ C2 จะพบว่า Paste (P) Option จะคัดลอกมาทั้งข้อความ รูปแบบ และสูตร 

ปุ่มที่ 2 คือ Values (V) สามารถวางได้เฉพาะข้อความอย่างเดียว ไม่ว่าข้อความต้นทางที่คัดลอก (Copy) มาจะมีการจัดรูปแบบต่างๆ ก็จะคัดลอก (Clear) หมด
ตัวอย่าง

รูปที่ 3 แสดงผลการคัดลอง Values (V) Option

จากรูปที่ 3 ประกอบด้วย 2 ตารางย่อย คือ ตารางบน แสดงให้เห็นว่าเซลล์ B2 มีการดำเนินการทั้งการเขียนสูตร =TODAY() และจัดรูปแบบ (Format) ให้เป็นตัวหนาและพื้นหลังเป็นสีส้ม เมื่อคุณ ๆ ทำการคัดลอก (Copy) ข้อมูลในเซลล์ B2 ไปวางในเซลล์ C2 ด้วย Values Option ผลลัพธ์จะได้ตามตารางล่าง ที่แสดงผลการคัดลอกด้วย Values (V) Option เพียงข้อความ (วันที่) ตามสูตรที่เขียนไว้ใน B2 เท่านั้น ดังนั้น ข้อมูลใน C2 จึงแสดงเพียงข้อมูลวันที่นั้นเอง

ปุ่มที่ 3 คือ Formula (F) สามารถวางสูตรที่คัดลอก (Copy) ได้เพียงอย่างเดียว 
ตัวอย่าง

รูปที่ 4 แสดงผลการวาง โดยใช้ Formula Option

จากรูปที่ 4 ประกอบด้วย 2 ตารางย่อย คือ ตารางบน แสดงให้เห็นว่าเซลล์ B4 มีการดำเนินการทั้ง การเขียนสูตร =SUM(C2:C3) และ จัดรูปแบบ (Format) ให้พื้นหลังเป็นสีฟ้า เมื่อคุณ ๆ ทำคัดลอก (Copy) ข้อมูลในเซลล์ B4 ไปวางในเซลล์ C4 ด้วย Formula Option ผลลัพธ์จะเป็นดังตารางด้านล่าง คือ Formula Option จะคัดลอกเพียงสูตร =SUM(C2:C3) มาเท่านั้น ดังนั้น ผลการคำนวณ (7) จึงเปลี่ยนไปตามเซลล์ใหม่นั้นเอง 

ปุ่มที่ 4 คือ Transpose (T) สามารถหมุนข้อความที่คัดลอกมา 
ตัวอย่าง

รูปที่ 5 แสดงผลการวาง โดยใช้ Transpose option

จากรูปที่ 5 ประกอบด้วย 2 ตารางย่อย คือ ตารางบน แสดงให้เห็นว่าเซลล์ B2 ถึง B3 มีการดำเนินการทั้ง ข้อความที่จัดรูปแบบ (Format) และ B4 เขียนสูตร =SUM(ฺB2:B3) กับจัดรูปแบบ (Format) 

เมื่อคุณ ๆ ทำคัดลอก (Copy) ข้อมูลในเซลล์ B2 ถึง B4 ไปวางในเซลล์ B7 ด้วย Transpose option ผลลัพธ์จะเป็นดังตารางด้านล่าง คือ Transpose option จะคัดลอกทั้งข้อความ รูปแบบและสูตร โดยหมุนให้ตรงข้ามกัน 

ปล. ถ้าทำการคัดลอก B7 ถึง D7 ไปวางแบบ Transpose option ผลลัพธ์จะได้แบบตารางบน

ปุ่มที่ 5 คือ Formatting (R) สามารถวางได้เฉพาะรูปแบบ (Format) อย่างเดียว โดยไม่มีข้อความที่คัดลอก (Copy) มาด้วย
ตัวอย่าง

รูปที่ 6 แสดงผลการวาง โดยใช้ Formatting (R) Option

จากรูปที่ 6 ประกอบด้วย 2 ตารางย่อย คือ ตารางบน แสดงให้เห็นว่าเซลล์ B2 มีการดำเนินการทั้ง การเขียนข้อความ และ จัดรูปแบบ (Format) ให้พื้นหลังเป็นสีชมพูกับข้อความเป็นตัวเอียง 

เมื่อคุณ ๆ ทำคัดลอก (Copy) ข้อมูลในเซลล์ B2 ไปวาง (Paste) ในเซลล์ C2 ด้วย Formatting Option ผลลัพธ์จะเป็นดังตารางด้านล่าง คือ Formula Option จะคัดลอกเพียงรูปแบบ (Format) เท่านั้น 

ปุ่มที่ 6 คือ Paste Link (N) สามารถวางเซลล์อ้างอิง ของข้อความต้นทาง เมื่อมีการแก้ไขเปลี่ยนแปลงข้อความต้นทาง ข้อความปลายทางจะเปลี่ยนตามไปด้วย
ตัวอย่าง

รูปแบบที่ 7 แสดงผลการวาง โดยใช้ Paste Link option

จากรูปที่ 7 ประกอบด้วย 2 ตารางย่อย คือ ตารางบน แสดงให้เห็นว่าเซลล์ B2 มีการดำเนินการทั้ง สูตร =10+28-5*3 และ จัดรูปแบบ (Format) ให้พื้นหลังเป็นสีส้ม ข้อความเป็นตัวเอียง ขีดเส้นใต้และตัวหนังสือสีฟ้า

เมื่อคุณ ๆ ทำคัดลอก (Copy) ข้อมูลในเซลล์ B2 ไปวาง (Paste) ในเซลล์ C2 ด้วย Paste Link Option ผลลัพธ์จะเป็นดังตารางด้านล่าง คือ Paste Link Option จะวางเซลล์อ้างอิงเซลล์ต้นทาง =$B$2

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

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

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