ฟังก์ชั่นในการจัดการข้อความ (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

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