MOD-02 การหาค่าโดยการเว้นบรรทัดคงที่

ในบางกรณีเราต้องการหาค่าใดๆ โดยการเว้นระยะคงที่ของแถว เช่น ต้องการหาค่าเฉพาะของแถวที่เว้นไป 3 แถว หรือ 5 แถว หรือ 7 แถว เป็นต้น เราสามารถใช้ฟังก์ชั่น MOD + IF ช่วยหาค่าได้ดังนี้

โดยดูค่าที่เหลือจากการหาร ของ บรรทัดแรกเป็นหลัก เหลือเท่าใด ให้ยึดว่า บรรทัดต่อๆไปที่ต้องการจะต้อง เหลือเศษเท่านั้นเสมอ

ตัวอย่าง

ในเซลล์ A1ถึงA10 มีค่าเรียงกันลงไปตั้งแต่ 1 ถึง 10 ต้องการให้ดึงเอาเฉพาะค่าที่เว้นไป 3 บรรทัด

ในที่นี้บรรทัดแรกคือ A1 และต้องการให้เว้นไป 3 แถว

1. ที่เซลล์ B1 ลองพิมพ์ =MOD(ROW(A1),3) จะได้ผลลัพธ์ คือ 1

2. จากนั้น Copy สูตรลงไปจนถึง B10 จะพบว่า จำนวนแถวที่ต้องการจะเท่ากับค่า ที่ได้จากแถวแรกเสมอ (ในที่นี้คือ 1)

โดยหลักการนี้ เราจะใช้ IF ช่วยบังคับ สิ่งที่ไม่ต้องการให้เป็นค่าว่าง ดังนั้นในการหาค่าที่เว้นไป 3 บรรทัดในที่นี้จะได้ว่า

1.ที่เซลล์ B1 พิมพ์ =IF(MOD(ROW(A1),3)=MOD(ROW($A$1),3),A1,"")

2.Copy สูตรลงล่าง

และถ้าต้องการเว้น 4 บรรทัด 5บรรทัด หรือกี่บรรทัดก็ใช้หลักการนี้ทั้งหมดได้เช่นกัน ลองเล่นดู

ARRAY และกล ARRAY-01

ลองดูผลคูณและการหาผลรวมของผลคูณดังต่อไปนี้








คราวนี้ลองสังเกตุในกรอบสี น้ำเงินและสีส้ม

แทนที่จะแยก ผลคูณในแต่ละบรรทัดลองเขียนผลคูณแบบนี้ดู ที่เซลล์ใดๆก็ได้
1. ยกผลคูณมาเขียน ทั้งชุดในบรรทัดเดียว =(A1:A3*B1:B3)

2.จากนั้นใช้ SUM ครอบเข้าไปเพื่อหาผลรวม=SUM(A1:A3*B1:B3)

3.กด Ctrl + Shift ค้างไว้ แล้วกด Enter

ติดรูป {=SUM(A1:A3*B1:B3)} ผลลัพธ์ เท่ากับ 38
จะเห็นว่าได้ผลลัพธ์ ผลรวมเป็น 38 เท่ากันกับการกระจายออกทีละบรรทัดแล้วหาผลรวม

มาดูการคูณแบบ 2 คอลัมน์บ้าง


จากรูป ผลลัพธ์ 42 มาจาก นำเอา คอลัมน์ A คูณ คอลัมน์ C บวกด้วย คอลัมน์ B คูณ คอลัมน์ Dโดยการคูณกันทีละบรรทัดแล้วหาผลรวม ถ้าเราลองเขียนสูตรแบบนี้ดู


1. ยกผลคูณมาทั้งชุดพร้อมกับนำมาบวกกันในตัว =(A1:A3*C1:C3+B1:B3*D1:D3)


2.ใช้ SUMครอบเข้าไปเพื่อหาผลรวมของทั้ง2ชุด คือ คูณกันแต่ละชุดแล้วนำมาบวกกัน=SUM(A1:A3*C1:C3+B1:B3*D1:D3)


3. กด Ctrl + Shift ค้างไว้ จากนั้น กด Enter

ติดรูป {=SUM(A1:A3*C1:C3+B1:B3*D1:D3)}

จะเห็นว่าได้ผลลัพธ์ ผลรวมเป็น 42 เท่ากันกับการกระจายออกทีละบรรทัดแล้วหาผลรวม



มาดูโจทย์ที่เกี่ยวกับเงื่อนไขบ้าง เงื่อนไขมีอยู่ว่า ที่เซลล์ A1:A10 มีจำนวนเรียงกันลงมาตั้งแต่ 1-10ให้หาผลรวมของค่าที่มากกว่า 6




จากรูป เราหาค่าของแต่ละบรรทัดออกมาโดยการใช้ IFบังคับให้เป็นไปตามโจทย์นั่นก็คือ เช่นที่เซลล์ B1=IF(A1>6,A1,"") ถ้า A1 มากกว่า 6 ให้เป็น A1 ถ้าไม่ใช่ให้เป็นค่าว่าง จากนั้น Copyสูตรลงมาด้านล่าง


จากโจทย์เดิมลองสังเกตุในกรอบสีน้ำเงินและสีส้มในรูปด้านล่างนี้

ถ้าเขียนเงื่อนไขแบบนี้

1.ยกเงื่อนไขมาเขียนทั้งชุดในบรรทัดเดียว(เซลล์ใดๆเซลล์เดียว)
=IF(A1:10>6,A1:A10,"")

2.ใช้ SUMครอบเข้าไปเพื่อหาผลรวม
=SUM(IF(A1:A10>6,A1:A10,""))

3.กด Ctrl + Shift ค้างไว้ แล้วกด Enter

ติดรูป {=SUM(IF(A1:A10>6,A1:A10,""))}
ผลลัพธ์ เท่ากับ 34 เหมือนกับการกระจายเงื่อนไขออกทีละบรรทัดแล้วหาผลรวม

จากตัวอย่างทั้ง 3 ตัวอย่างพอจะเห็นได้ว่าเราสามารถเขียนสูตรเป็นชุดๆในเซลล์ใดๆเพียงเซลล์เดียวแทนการกระจายสูตรออกเป็นบรรทัดๆ ได้ การรวบสูตรเข้ามาเป็นชุดๆแบบนี้ เรียกว่า ARRAY และการจะเป็น ARRAY ได้นั้นหลังจากเขียนสูตรเรียบร้อยแล้ว ต้องกด Ctrl + Shift ค้างไว้ จากนั้นกด Enter จะเกิดสัญลักษณ์ { } ปิดหน้าหลังสูตรให้โดยอัตโนมัติ

ARRAY เป็นฟังก์ชั่นการทำงานอย่างหนึ่งของ Excel ที่มีประโยชน์และมีประสิทธิภาพมากในการทำงานต่างๆบน Excel พึงจำไว้ว่า ARRAY จะคำนวณเป็นชุดๆ และการจะติดเป็น ARRAYได้นั้น จะต้องกด Ctrl + Shift ค้างไว้แล้วตามด้วย Enter เสมอ

หากเทียบ Excel กับการเล่นหมากกระดาน ARRAY ถือเป็นกลในExcel ชนิดหนึ่ง และมีกลพิสดารทาง ARRAYมากมาย ที่ชวนให้พิศวงและน่าศึกษาและการนำไปใช้ประยุกต์ในการทำงานบน Excel ต่อไป

บทต่อๆไปจะอธิบายถึงพื้นฐานที่สำคัญของ ARRAY และกล ARRAY ตลอดจนการนำ ARRAY ไปใช้ ในรูปแบบต่างๆ ที่พบเจออยู่บ่อยๆ

MOD

ไวยากรณ์
=MOD(number,divisor)
=MOD(จำนวน,ตัวหาร)

ผลลัพธ์ของฟังก์ชั่น MOD คือเศษจากการหาร
ตัวอย่าง

=MOD(1,4) ผลลัพธ์ =1 เกิดจาก 1/4 ไม่ลงตัว
=MOD(2,4)ผลลัพธ์ =2 เกิดจาก 2/4 ไม่ลงตัว
=MOD(3,4)ผลลัพธ์ =3 เกิดจาก 3/4 ไม่ลงตัว
=MOD(4,4)ผลลัพธ์ =0 เกิดจาก 4/4 ลงตัว พอดี ไม่เหลือเศษ
=MOD(5,4)ผลลัพธ์ =1 เกิดจาก 5/4 เศษ 1
=MOD(6,4)ผลลัพธ์ =2 เกิดจาก 6/4 เศษ 2
=MOD(7,4)ผลลัพธ์ =3 เกิดจาก 7/4 เศษ 3
=MOD(8,4)ผลลัพธ์ =0 เกิดจาก 8/4 ลงตัว พอดี ไม่เหลือเศษ
=MOD(9,4)ผลลัพธ์ =1 เกิดจาก 9/4 เศษ 1
=MOD(10,4)ผลลัพธ์ =2 เกิดจาก 10/4 เศษ 2
=MOD(11,4)ผลลัพธ์ =3 เกิดจาก 11/4 เศษ 3
=MOD(12,4)ผลลัพธ์ =0 เกิดจาก 12/4 ลงตัว พอดี ไม่เหลือเศษ
=MOD(13,4)ผลลัพธ์ =1 เกิดจาก 13/4 เศษ 1
=MOD(14,4) ผลลัพธ์ =2 เกิดจาก 14/4 เศษ 2
=MOD(15,4)ผลลัพธ์ =3 เกิดจาก 15/4 เศษ 3
=MOD(16,4)ผลลัพธ์ =0 เกิดจาก 16/4 ลงตัว พอดี ไม่เหลือเศษ
=MOD(17,4)ผลลัพธ์ =1 เกิดจาก 17/4 เศษ 1

และถ้า =MOD(2.4,0.5) ผลลัพธ์จะเท่ากับ 0.4 เกิดจาก 2.4/0.5 เหลือเศษ 0.4 นั่นเอง

ตัวอย่าง

ที่เซลล์ A1ถึงA10 มีตัวเลขเรียงกันลงมาตั้งแต่ 1 ถึง 10 ให้หาผลรวมของ

1.เฉพาะบรรทัดคี่

2.เฉพาะบรรทัดคู่

หาผลรวมเฉพาะบรรทัดคี่

1.ที่ B1 พิมพ์ =IF(MOD(ROW(A1),2)=0,"",A1)

2. Copy สูตรลงล่าง

หาผลรวมเฉพาะบรรทัดคู่
1.ที่เซลล์ B1 พิมพ์ =IF(MOD(ROW(A1),2)=0,A1,"")

2.Copy สูตรลงล่าง

FLOOR

ไวยากรณ์

=FLOOR(number,significance)

=FLOOR(จำนวน,significance)

ตัว Significance นี้ให้มองเสมือนตัวหารตัวหนึ่ง และถ้าหารไม่ลงตัว Excel จะปัดค่าเศษนั้นโดย"ปัดลง"ให้เป็นจำนวนเต็มเพื่อให้หารด้วยตัว Significanceนี้ ได้ลงตัว

จำไว้ว่า FLOOR ปัดให้ติดพื้น

ตัวอย่าง เช่น

อธิบายได้ว่า

=FLOOR(1,3) ผลลัพธ์ 0 เกิดจาก 1/3 ไม่ลงตัว จึงปัดลง เป็น 0
=FLOOR(2,3) ผลลัพธ์ 0 เกิดจาก 2/3 ไม่ลงตัว จึงปัดลง เป็น 0
=FLOOR(3,3) ผลลัพธ์ 3 เกิดจาก 3/3 ลงตัวพอดี
=FLOOR(4,3) ผลลัพธ์ 3 เกิดจาก 4/3 ไม่ลงตัว จึงปัดลง เป็น 3
=FLOOR(5,3) ผลลัพธ์ 3 เกิดจาก 5/3 ไม่ลงตัว จึงปัดลง เป็น 3
=FLOOR(6,3) ผลลัพธ์ 6 เกิดจาก 6/3 ลงตัวพอดี
=FLOOR(7,3) ผลลัพธ์ 6 เกิดจาก 7/3 ไม่ลงตัว จึงปัดลง เป็น 6
=FLOOR(8,3) ผลลัพธ์ 6 เกิดจาก 8/3 ไม่ลงตัว จึงปัดลง เป็น 6
=FLOOR(9,3) ผลลัพธ์ 9 เกิดจาก 9/3 ลงตัวพอดี
=FLOOR(10,3)ผลลัพธ์ 9 เกิดจาก 10/3 ไม่ลงตัว จึงปัดลง เป็น 9
=FLOOR(11,3) ผลลัพธ์ 9 เกิดจาก 11/3 ไม่ลงตัว จึงปัดลง เป็น 9
=FLOOR(12,3) ผลลัพธ์ 12 เกิดจาก 12/3 ลงตัวพอดี
=FLOOR(13,3) ผลลัพธ์ 12 เกิดจาก 13/3 ไม่ลงตัว จึงปัดลง เป็น 12
=FLOOR(14,3) ผลลัพธ์ 12 เกิดจาก 14/3 ไม่ลงตัว จึงปัดลง เป็น 12
=FLOOR(15,3) ผลลัพธ์ 15 เกิดจาก 15/3 ลงตัวพอดี
=FLOOR(16,3) ผลลัพธ์ 15 เกิดจาก 16/3 ไม่ลงตัว จึงปัดลง เป็น 15
=FLOOR(17,3) ผลลัพธ์ 15 เกิดจาก 17/3 ไม่ลงตัว จึงปัดลง เป็น 15

ตัวอย่างการนำไปใช้

จากตารางต้องการให้ข้อมูลจากคอลัมน์ A มาเรียงกันเป็นชุด ชุดละ 3 เรียงต่อกันลงมาที่คอลัมน์ C

ทำได้หลายๆวิธี แต่ในที่นี้จะใช้ FLOOR + INDEX

1.ที่เซลล์ C1 พิมพ์ =INDEX($A$1:$A$5,FLOOR(ROW(A3),3)/3)

2. Copy สูตรลงล่าง

ให้สังเกตุ ในข้อนี้สูตรที่ C1 นั้นจะเริ่มที่ ROW(A3).. ไม่ได้เริ่มที่ ROW(A1) เนื่องจาก เพื่อความเหมาะสมในการแก้ปัญหานั่นเอง ถ้าเริ่มที่ ROW(A1) จะหาค่าแรกยาก เพราะ FLOOR จะปัดลงเป็น 0

โจทย์ข้อนี้ เหมือนกับ โจทย์ที่ให้ไว้ในการใช้ CEILING ลองสังเกตุเปรียบเทียบกันดู

CEILING

ไวยากรณ์

=CEILING(number,significance)

=CELING(จำนวน,significance)

ตัว Significance นี้ให้มองเสมือนตัวหารตัวหนึ่ง และถ้าหารไม่ลงตัว Excel จะปัดค่าเศษนั้นโดย"ปัดขึ้น"ให้เป็นจำนวนเต็มเพื่อให้หารด้วยตัว Significanceนี้ ได้ลงตัว

จำไว้ว่า CEILING ปัดให้ชนเพดาน

เช่น
=CEILING(1,3) ผลลัพธ์ =3 เกิดจาก 1/3 เหลือเศษจึงปัดให้ชนเพดานเป็น 3
=CEILING(2,3) ผลลัพธ์ =3 เกิดจาก 2/3 เหลือเศษจึงปัดให้ชนเพดานเป็น 3
=CEILING(3,3) ผลลัพธ์ =3 เกิดจาก 3/3 ลงตัวพอดี (ชนเพดาน)
=CEILING(4,3) ผลลัพธ์ =6 เกิดจาก 4/3 เหลือเศษจึงปัดให้ชนเพดานเป็น 6
=CEILING(5,3) ผลลัพธ์ =6 เกิดจาก 5/3 เหลือเศษจึงปัดให้ชนเพดานเป็น 6
=CEILING(6,3) ผลลัพธ์ =6 เกิดจาก 6/3 ลงตัวพอดี (ชนเพดาน)
=CEILING(7,3) ผลลัพธ์ =9 เกิดจาก 7/3 เหลือเศษจึงปัดให้ชนเพดานเป็น 9
=CEILING(8,3) ผลลัพธ์ =9 เกิดจาก 8/3 เหลือเศษจึงปัดให้ชนเพดานเป็น 9
=CEILING(9,3) ผลลัพธ์ =9 เกิดจาก 9/3 ลงตัวพอดี (ชนเพดาน)

ตัวอย่างการนำไปใช้

จากตารางต้องการให้ข้อมูลจากคอลัมน์ A มาเรียงกันเป็นชุด ชุดละ 3 เรียงต่อกันลงมาที่คอลัมน์ C

ทำได้หลายๆวิธี แต่ในที่นี้จะใช้ CEILING + INDEX
1. ที่เซลล์ C1 พิมพ์ =INDEX($A$1:$A$5,CEILING(ROW(),3)/3)

2. Copyลงล่าง

** สังเกตุว่าตัวเลข สีแดง คือจำนวนชุดที่ต้องการให้เรียงกัน ถ้าให้เรียงกัน มากน้อยกว่านั้นก็ปรับตัวเลขไปตามนั้น เช่น เรียง 5 ชุดก็ให้เป็น 5 **

จะไม่อธิบายละเอียดลองแยกสูตรออกมาดู โดย ยังไม่ต้องใช้ INDEX ให้ดูเฉพาะ CEILINGก่อน โดย = CEILING(ROW(),3/,3) แล้ว Copy ลงไปทุกบรรทัด สังเกตุดูจะพบว่ามันจะเป็นชุดตัวเลข ที่เรียงกันลงมาเป็น ชุดๆตามต้องการ

COLUMNS

ไวยากรณ์
=COLUMNS(Array)
=COLUMNS( จำนวนคอลัมน์ตามช่วงของคอลัมน์)

COLUMNS บอกถึงจำนวนคอลัมน์ทั้งหมด เช่น
=COLUMNS(B1) มีค่า = 1 เพราะมีจำนวนคอลัมน์เดียว
=COLUMNS(A1:C5) มีค่า = 3 เพราะตั้งแต่คอลัมน์ A1 ถึง คอลัมน์ C5 มีจำนวน 3 คอลัมน์
=COLUMNS(D3:H3) มีค่า = 5 เพราะตั้งแต่คอลัมน์ D3 ถึงคอลัมน์ H3 มีจำนวน 5 คอลัมน์
=COLUMNS(B3:AA8) มีค่า =26 เพราะตั้งแต่คอลัมน์ B3 ถึงคอลัมน์ AA8 มี จำนวน 26 คอลัมน์

ROWS

ไวยากรณ์
=ROWS(Array)
=ROWS(จำนวนแถวตามช่วงของแถว)

ROWS บอกถึงจำนวนแถวทั้งหมด เช่น
=ROWS(A1) มีค่า = 1 เพราะมีจำนวนแถวเดียว
=ROWS(A1:A5) มีค่า = 5 เพราะตั้งแต่แถว A1 ถึง แถว A5 มีจำนวน 5 แถว
=ROWS(A2:A5) มีค่า = 4 เพราะตั้งแต่แถว A2 ถึงแถว A5 มีจำนวน 4 แถว
=ROWS(B3:B8) มีค่า = 6 เพราะตั้งแต่แถว B3 ถึงแถว B8 มี จำนวน 6 แถว

COLUMN

ไวยากรณ์
=COLUMN(reference)
=COLUMN(เซลล์อ้างอิง)
การใช้คำสั่ง COLUMN ใน Excel มีประโยชน์มากในการประยุกต์ใช้กับหลายๆฟังก์ชั่น
หน้าตาของ COLUMN หลักๆที่พบเจอบ่อยๆ จะมีดังต่อไปนี้

แบบที่ 1. ระบุชื่อเซลล์ =COLUMN(ชื่อเซลล์)ผลลัพธ์จะได้ค่าตาม COLUMN นั้นๆ
=COLUMN(A10) ผลลัพธ์ 1 เพราะ คอลัมน์ A. เป็น คอลัมน์ ที่ 1 ใน Excel
=COLUMN(D7) ผลลัพธ์ 4 เพราะ คอลัมน์ D. เป็น คอลัมน์ ที่ 4 ใน Excel
=COLUMN(N5) ผลลัพธ์ 14 เพราะ คอลัมน์ N. เป็น คอลัมน์ ที่ 14 ใน Excel
=COLUMN(N99)ผลลัพธ์ 14 เพราะ คอลัมน์ N. เป็น คอลัมน์ ที่ 14 ใน Excel


แบบที่ 2. ไม่ระบุชื่อเซลล์=COLUMN() จะได้ค่าตาม COLUMN นั้นๆ
เช่น =COLUMN() ที่ C7 จะได้ค่า 3 เพราะ คอลัมน์ C. เป็น คอลัมน์ ที่ 3 ใน Excel
เช่น =COLUMN() ที่ AB99 จะได้ค่า 28 เพราะ คอลัมน์ AB. เป็น คอลัมน์ ที่ 28 ใน Excel


แบบที่ 3. ระบุเป็นช่วงเซลล์ ผลลัพธ์จะได้ค่าตาม COLUMN ของเซลล์แรก...เช่น
=COLUMN(A2:C10) ผลลัพธ์ 1 เพราะ คอลัมน์ A. เป็น คอลัมน์ ที่ 1 ใน Excel
=COLUMN(B5:D5) ผลลัพธ์ 2 เพราะ คอลัมน์ B. เป็น คอลัมน์ ที่ 2 ใน Excel

แบบที่ 3 นี้มีประโยชน์มาก พบบ่อยๆในการใช้ร่วมกับสูตรแบบ ARRAY

ROW

ไวยากรณ์
=ROW(reference)
=ROW(เซลล์อ้างอิง)


การใช้คำสั่ง ROW ใน Excel มีประโยชน์มากในการประยุกต์ใช้กับหลายๆฟังก์ชั่น
หน้าตาของ ROW หลักๆที่พบเจอบ่อยๆ จะมีดังต่อไปนี้

แบบที่ 1. =ROW(ระบุชื่อเซลล์)ผลลัพธ์จะได้ค่าตาม ROW นั้นๆ
=ROW(A7) ผลลัพธ์ 7
=ROW(D7) ผลลัพธ์ 7
=ROW(N5) ผลลัพธ์ 5

แบบที่ 2.ไม่ระบุชื่อเซลล์=ROW() จะได้ค่าตาม ROW นั้นๆ
เช่น ROW() ที่ ROW ที่ C7 จะได้ค่า 7
เช่น ROW() ที่ ROW ที่ AB99 จะได้ค่า 99

แบบที่ 3. =ROW(ช่วงเซลล์)ผลลัพธ์จะได้ค่าตาม ROW ของเซลล์แรก...เช่น
=ROW(A2:A10) ผลลัพธ์ 2
=ROW(B5:A99) ผลลัพธ์ 5

แบบที่ 3 นี้มีประโยชน์มาก พบบ่อยๆในการใช้ร่วมกับสูตรแบบ ARRAY

INDEX

ไวยากรณ์
=INDEX(ชุดข้อมูล,Rowที่..,Columnที่..)



ตัวอย่าง
400 =INDEX(A2:B6,2,2) =INDEX(ชุดข้อมูล,Rowที่..2,Columnที่.. 2)
150 =INDEX(A2:B6,4,2) =INDEX(ชุดข้อมูล,Rowที่..4,Columnที่.. 2)
2009 =INDEX(A2:B6,3,1) =INDEX(ชุดข้อมูล,Rowที่..3,Columnที่.. 1)

กรณีที่ INDEX เพียงแถวใด แถวเดียว หรือ คอลัมน์ใดคอลัมน์เดียวนั้น มักจะละส่วนสุดท้ายไว้
เช่น....
2009 =INDEX(A2:A6,3) =INDEX(ชุดข้อมูล,Rowที่..3)
2009 =INDEX(A2:A6,3,1) =INDEX(ชุดข้อมูล,Rowที่..3,Columnที่.. 1)


การละส่วนสุดท้ายไว้นั้นสามารถทำได้ดังกล่าวมาแล้ว
แต่ควรเข้าใจว่า สูตรเต็มๆนั้นจะต้องมีทั้ง ส่วนของ Row และ Column และต้องไม่ลืมว่า Row มาก่อน Column เสมอ

การนำ INDEX ไปใช้ส่วนใหญ่ที่พบเจอบ่อยๆคือการนำไปใช้กับฟังก์ชั่น MATCH
โดยการหาตำแหน่งของข้อมูลจากการใช้ MATCH จากนั้นจึง INDEX

ตัวอย่างง่ายๆอีกซักชุด




=INDEX(ชุดข้อมูล,Rowที่…,Columnที่…)


=INDEX(B3:D6,1,1) ผลลัพธ์---> Honda
=INDEX(B3:D6,2,3) ผลลัพธ์---> 40


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

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

1.หาค่า MAX ก่อน โดย = MAX( ชุดข้อมูล) ในที่นี้คือ MAX(E4:E9) ได้ตำตอบคือ 90
2.ขั้นต่อไปจะใช้ INDEX หาออกมาว่าเป็นผู้ใดที่ได้ยอดขายสูงสุด(90) จะหา INDEX ได้ ต้องทราบ ตำแหน่งก่อน ....จึงหาตำแหน่งจาก ค่าสูงสุดที่ได้ เป็น MATCH(MAX(E4:E9),E4:E9,0) ...ได้ตำแหน่งของ ค่าสูงสุดออกมา
3.จากนั้น ใช้ INDEX หาจากรายชื่อโดยใช้ตำแหน่งเดียวกันกับที่หาได้จาก ค่าMAX จึงออกมาเป็น
INDEX(A4:A9,MATCH(MAX(E4:E9),E4:E9,0))


ตัวอย่างเบาๆ

MATCH

ฟังก์ชั่น MATCH ใช้หา"ตำแหน่ง"ในช่วงของชุดข้อมูลนั้นๆ ใช้หาตำแหน่งได้ทั้งตัวเลขและ ตัวอักษร และCharacter ต่างๆ

โดยปกติแล้วเรามักจะไม่ใช้ฟังก์ชั่น MATCH แบบโดดๆ (อาจจะมีบ้างบางกรณี ) เรามักจะใช้ร่วมกับฟังก์ชั่น อื่นๆ เช่น โดยทั่วๆไป มักจะใช้ ฟังก์ชั่น MATCH คู่กับฟังก์ชั่น INDEX ในการหาค่าต่างๆแทน VLOOKUPหรือ HLOOKUP เป็นต้น เนื่องจากบางตำแหน่งในชุดข้อมูลไม่สามารถใช้คำสั่ง VLOOKUP ได้ เช่นตำแหน่งของผลลัพท์ที่ต้องการ ไม่ได้อยู่ทางขวาของ เซลล์อ้างอิง ทำให้ไม่สามารถใช้คำสั่ง VLOOKUP หาค่าได้ (VLOOKUP มองค่าจากซ้ายไปขวาได้เท่านั้น ไม่สามารถมองค่าจากขวาไปซ้ายได้) จึงเลี่ยงไปใช้ฟังก์ชั่น MATCH + INDEX เพื่อหาค่านั้นๆแทน

ไวยากรณ์
= MATCH(lookup_value, lookup_array, match_type)
= MATCH(สิ่งที่ต้องการหาตำแหน่ง,ชุดข้อมูล, Match_type)

Match type นี้จะแยกเป็น 3 แบบ คือ 0,1,-1
โดยปกติในการใช้ฟังก์ชั่น MATCH โดยทั่วไปจะเลือกใช้ Match type = 0 เป็นหลักเนื่องจากไม่จำเป็นต้องจัดเรียงค่าในชุดข้อมูล จากน้อยไปมากหรือ มากไปน้อยนั่นเอง แต่ในที่นี้จะอธิบายถึง Match type ทั้ง 3 แบบ

ก่อนจะอธิบายถึงความแตกต่างของ Match type ทั้ง 3 แบบนั้นจะขอยกตัวอย่างการใช้ฟังก์ชั่น MATCH ให้ดูเป็นตัวอย่างสักเล็กน้อย ก่อนดังนี้

เช่น ถ้าช่วงเซลล์ A1 ถึง A4 ประกอบด้วย 15,5,12,8 หาเราต้องการหา"ตำแหน่ง"ของ 12 จะเขียนสูตรได้ดังนี้ =MATCH(12,A1:A4,0) และได้"ตำแหน่ง"คือ 3 เนื่องจาก 12 เป็นเป็นรายการที่สาม ในชุดข้อมูลนั่นเอง

ตัวอย่างต่อไป ลองสังเกตุการหา"ตำแหน่ง" ของค่าต่างๆ ตามที่กำหนด ไม่ว่าเราจะพิมพ์ตัวเลขนั้นๆลงไป เลย หรือจะพิมพ์เซลล์อ้างอิงเพื่อหาตำแหน่งของข้อมูลก็จะได้ผลลัพท์ที่เท่ากัน






Match type ,0 (ไม่บังคับเรียงชุดข้อมูล)

จะเรียงข้อมูล จากมากไปน้อย หรือน้อยไปมาก หรือไม่ก็ตาม
สามารถหา แหน่ง ของค่า ที่มีอยู่ ในชุดข้อมูลนั้นได้ เสมอ
แต่ถ้า ไม่มีค่านั้นๆ อยู่ในชุดข้อมูล จะหา ค่านั้นๆ ไม่ได้ ส่งกลับค่าเป็น #N/A
นั่นก็หมายถึง ชุดข้อมูล จะค่ามากน้อยสลับกันไปมา หรือเรียงจาก มากไปน้อย หรือ จากน้อยไปมาก จะไม่มีผลในการหาตำแหน่งของข้อมูลเลย




Match Type ,1 (บังคับ เรียงชุดข้อมูลน้อยไปมาก)
หากไม่เรียง ข้อมูล จาก น้อยไปมาก อาจจะหาตำแหน่ง ถูก หรือ ผิดก็ได้ ไม่แน่นอน
ดังนั้นจึง บังคับ เรียงข้อมูล จาก น้อยไปมาก จึงจะหา ตำแหน่งที่ถูกต้องได้


ข้อน่าสนใจของการใช้ Match type ,1
กรณีที่ไม่มีค่านั้นๆ อยู่ใน ชุดข้อมูล Match Type ,1 จะแสดง ตำแหน่ง ของค่า
ที่น้อยกว่า ที่อยู่ใกล้ค่านั้นที่สุด เสมอ ตัวอย่าง...



ในที่นี้ 58 ไม่มีในชุดข้อมูล ดังนั้น Match type ,1 จึงแสดงตำแหน่งของค่าที่น้อยกว่า 58 คือ 50 แทน และจะได้ตำแหน่งคือ 3

ในที่นี้ 72 ไม่มีในชุดข้อมูล ดังนั้น Match type ,1 จึงแสดงตำแหน่งของค่าที่น้อยกว่า 72 คือ 70แทน และจะได้ตำแหน่งคือ 5

ในทีนี้ 20 ไม่มีในชุดข้อมูล และไม่มีค่าที่น้อย กว่า 20 ในชุดข้อมูล ดังนั้น Match type ,1 จึงไม่สามารถหาตำแหน่งใดๆ ออกมาได้ (#N/A)


Match Type ,-1 (บังคับเรียงชุดข้อมูลมากไปน้อย )
หากไม่เรียง ข้อมูล จาก มากไปน้อย อาจจะหาตำแหน่ง ถูก หรือ ผิดก็ได้ ไม่แน่นอน
ดังนั้นจึง บังคับ เรียงข้อมูล จาก มากไปน้อย จึงจะหา ตำแหน่งที่ถูกต้องได้



ข้อน่าสนใจของ Match type ,-1
กรณีที่ไม่มีค่านั้นๆ อยู่ใน ชุดข้อมูล Match Type ,-1 จะแสดง ตำแหน่ง ของค่า ที่ มากกว่า ที่อยู่ใกล้ค่านั้นที่สุด เสมอ


ในที่นี้ 20ไม่มีในชุดข้อมูล ดังนั้น Match type ,-1 จึงแสดง ตำแหน่งของค่าที่ มากกว่า 20 คือ 30 แทน และจะได้ตำแหน่งคือ 5

ในที่นี้ 58 ไม่มีในชุดข้อมูล ดังนั้น Match type ,-1 จึงแสดง ตำแหน่งของค่าที่ มากกว่า 58 คือ 60 แทน และจะได้ตำแหน่งคือ 2

ในที่นี้ 72 ไม่มีในชุดข้อมูล และไม่มีค่าที่มาก กว่า 72 ในชุดข้อมูล ดังนั้น Match type ,-1 จึงไม่สามารถหาตำแหน่งใดๆ ออกมาได้ (#N/A)

RANK

RANK ใช้จัดอันดับตามคะแนนมากไปน้อยลดหลั่นกันลงไป หรือจะจัดลำดับจากคะแนนน้อยไปหามากก็ได้

ไวยากรณ์
=RANK(number,ref,order)
=RANK(จำนวน หรือเซลล์อ้างอิง,ชุดข้อมูลที่ให้อ่านค่า,0 หรือ 1)


กรณีจัดอันดับจากคะแนน มากไปหาน้อย
โดยกำหนดให้ คะแนนมากที่สุดได้อันดับ 1 คะแนนถัดลงไปได้อันดับถัดลงไป กรณีแบบนี้ สามารถ ละ ,0 ไว้จะไม่พิมพ์ลงไปในสูตร ก็จะได้ผลลัพท์จะเท่ากัน

ตัวอย่าง


กรณีเรียงอันดับจากมากไปน้อย คือ คะแนนมากอยู่อันดับหนึ่ง คะแนนน้อยกว่าอยู่อันดับถัดมา แบบนี้สามารถละ ,0 ได้

กรณีจัดอันดับจากคะแนน น้อยไปหามาก
โดยกำหนดให้ คะแนน น้อยที่สุดได้อันดับ 1 คะแนนมากขึ้นไปได้อันดับถัดลงไป กรณีแบบนี้ บังคับให้พิมพ์ ,1 ไว้ท้ายสูตรเสมอ
เช่นคะแนนการแข่งขันกอล์ฟ ซึ่งจะคิดคะแนนของผู้ที่ตีเสียน้อยที่สุดได้อันดับหนึ่ง ส่วนผู้ที่ตีเสียมากกว่า จะได้อันดับถัดๆลงไป




การจัดอันดับคะแนนแบบ"กอล์ฟ"

โจทย์แบบนี้เจอบ่อย ขอเรียกว่าการจัดอันดับคะแนนแบบกอล์ฟก็แล้วกัน เช่นการแข่งขันกอล์ฟที่มีแต้มต่อ และกรณีที่มีผู้ที่ได้คะแนนเท่ากันจะมองที่แต้มต่อ

โจทย์ต่อไปนี้กำหนดว่า หากคะแนนเท่ากันให้มองที่แต้มต่อถ้าใครแต้มต่อ น้อยกว่าให้ถือเป็นผู้ชนะ(จะกำหนดให้ผู้มีแต้มต่อมากกว่าชนะก็ได้ แล้วแต่กรณี)

หลักการง่ายๆคือ ทำแต้มต่อให้เป็นจุดทศนิยม แล้วนำไปบวกกับคะแนนที่ได้ จะได้คะแนนที่ไม่ซ้ำกันออกมา แล้วจึงไปหาลำดับ ในที่นี้ให้ แต้มต่อถูกหารด้วย 100 ก่อน แล้วนำไปบวกกับคะแนนที่ได้

การหารแต้มต่อเพื่อให้เป็นทศนิยมในที่นี้ ควรหารด้วย 100 ขึ้นไป เนื่องจากแต้มต่อเป็นเลขไม่เกินสองหลัก

หากโจทย์กำหนดว่าให้หาอันดับที่ 1,2,3 ออกมาเลยว่าเป็นใคร จากตัวอย่างข้างบน ค่า RANK ที่ออกมาจะพบว่า คุณ E ได้ที่ 1 คุณ A ได้ที่ 2 และ คุณ C ได้ที่ 3

แต่กรณีแบบนี้จะไม่ใช้ RANK เลยก็ได้ เนื่องจากโจทย์ไม่ได้ต้องการหา"ค่า" RANK แต่ต้องการให้หาว่าใครชนะ ที่ 1,2,3 จึงควรใช้ คะแนนที่น้อยที่สุดเป็นตัววัดแล้ว ชี้ลงไปว่า ใครได้ที่ 1,2,3 การวัดค่าน้อยไปมาก เราจะใช้ฟังก์ชั่น SMALL มาช่วย โดยไม่ต้องใช้ RANK เลย แล้วใช้ MATCH + INDEX หารายชื่อของผู้ที่ได้คะแนนตามต้องการออกมา

จะเปลี่ยนค่าตัวเลขเพื่อหาค่าน้อยไปมาก ที่ SMALL

กรณีโจทย์ดังกล่าวหากไม่เพิ่มคอลัมน์คะแนน-2 ขึ้นมาเลยก็ทำได้ โดยทำเป็น Array ดังนี้ หลักการยังคงใช้ SMALL + MATCH+INDEX เหมือนเดิม

ที่เซลล์ใดๆ ในที่นี้คือ D12 พิมพ์ =INDEX($B$4:$B$10,MATCH(SMALL($D$4:$D$10+$C$4:$C$10/100,1),$D$4:$D$10+$C$4:$C$10/100,0))
จากนั้น(บังคับ) กดแป้น Ctrl +Shift +Enter พร้อมกัน
ติดรูป กล Array

{=INDEX($B$4:$B$10,MATCH(SMALL($D$4:$D$10+$C$4:$C$10/100,1),$D$4:$D$10+$C$4:$C$10/100,0))}

RANK กระโดดและ RANK ไม่กระโดด

โดยปกติทั่วๆไปนั้น ฟังก์ชั่น Rank จะจัดอันดับแบบกระโดดให้เสมอเช่น มีลำดับที่ 3 อยู่ 3 คน ดังนั้นคนที่ได้คะแนนถัดไปจะถูกจัดเป็นลำดับที่ 6 ทันที เนื่องจากถือว่า คนที่ได้ที่ 3 ทั้ง 3คนนั้น ถูกมองว่าจะจัดอันดับเป็น 3-4-5 ดังนั้น คนที่ได้อันดับถัดมาจะถูกแสดงเป็นอันดับ 6
ถ้าหากต้องการทำให้เป็นแบบ Rank ไม่กระโดดจะทำอย่างไร เช่น กรณีที่มีผู้ที่ได้อันดับ 3 อยู่ 3 คน คนที่ได้อันดับถัดไป ให้แสดงเป็นอันดับ 4 ไม่ใช่อันดับ 6


ตัวอย่าง มีคะแนนอยู่ชุดหนึ่งต้องการจัดลำดับแบบไม่กระโดด

สามารถทำได้หลายๆวิธี จะยกตัวอย่างสัก 2 วิธี

วิธีที่ 1 หากสามารถ Sort data ได้ จะช่วยลดความยุ่งยากลงไปได้มาก
แล้วใช้ IF ธรรมดาเงื่อนไขเดียว จัดอันดับออกมา



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



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

ถ้าเข้าใจได้ดี จะพบว่า การซ้อนๆอะไรๆ เข้าไปในสูตรเพื่อให้มันเป็นสูตรเดียวกันที่กระชับนั้น จะมีลีลาของมันเอง และวิชา Excel ของท่านจะก้าวหน้าไปอย่างเงียบๆโดยไม่รู้ตัว

เชื่อผมเต๊อะ !!

รายละเอียดสูตรต่างๆ เช่น IF และ VLOOKUP ในที่นี้จะไม่อธิบายตัวสูตรละเอียด เนื่องจาก ในบทความก่อนๆนี้ ทั้ง IF และ VLOOKUP ผมได้อธิบายมาค่อนข้างละเอียดระดับหนึ่งแล้ว

และต่อๆไป อาจจะอธิบายในสูตรต่างๆ ที่เคยเขียนไว้ให้น้อยลง จะไปเน้นถึง ลีลา การเขียนสูตรและความเข้าใจว่า จะต้องเขียนสูตรอย่างไร และจะใช้สูตรนั้นๆ อย่างไร ให้มากขึ้น

SMALL

SMALL ใช้หาค่าในข้อมูลหรือชุดข้อมูลว่า เป็นเท่าใดตามลำดับกำหนด เรียงลำดับที่น้อยที่สุดไปมากที่สุด

นั่นคือฟังก์ชั่น SMALL จะตรงข้ามกับฟังก์ชั่น LARGE นั่นเอง

ไวยากรณ์
=SMALL(array,k)
=SMALL(ข้อมูลหรือชุดข้อมูล , ลำดับที่น้อยต้องการ)

ตัวอย่าง

ให้หาค่าที่เรียงลำดับจากน้อยที่สุดไปมากที่สุด

LARGE

LARGE ใช้หาค่าในข้อมูลหรือชุดข้อมูลว่า เป็นเท่าใด ตามลำดับที่กำหนด


ไวยากรณ์

=LARGE(array,k)
=LARGE(ข้อมูลหรือชุดข้อมูล , ลำดับที่ต้องการ)


ตัวอย่าง
มีคะแนนอยู่ ชุดหนึ่งตามตารางด้านล่าง ต้องการหาว่า อันดับ 1-2-3-4-5 คือคะแนนอะไร

CONCATENATE หรือ &

CONCATENATE หรือ & ใช้เชื่อมเซลล์ หรือ Character ใดๆ ตั้งแต่ 2 เซลล์ขึ้นไปเข้าด้วยกัน

ไวยากรณ์
=CONCATENATE (text1,text2,...)
หรือจะใช้เครื่องหมาย & เป็นตัวเชื่อมก็ได้ โดยปกติ มักจะนิยม ใช้ & เชื่อมมากกว่าการใช้ฟังก์ชั่น CONCATENATE

ตัวอย่าง



CONCATENATE มีประโยชน์มาก ในการเชื่อมเซลล์หรือ Character ต่างๆ เนื่องจากในการใช้งานบน Excel นั้นบางครั้งไม่สามารถจะใช้สูตรให้อ่านค่าจากเซลล์นั้นๆโดยตรงได้ จำเป็นต้องใช้ CONCATENATE เข้าช่วยเพื่อให้เซลล์นั้นๆ สามารถอ่านค่าได้ถูกต้อง

ตัวอย่างที่เห็นได้ชัดและ เจอบ่อยๆ เช่นการใช้ VLOOKUP เป็นต้น
และ CONCATENATE ยังใช้ได้กับกรณีอื่นๆ อีกมากมาย

SUBSTITUTE

SUBSTITUTE จัดเป็น อีกหนึ่ง String Function ที่ใช้บ่อย

บางครั้งในการใช้งานบน Excel นั้น มีบางอย่างที่เราต้องการให้เปลี่ยนไป ในเซลล์นั้นๆ เราสามารถใช้ฟังก์ชั่น Substitute เข้าช่วยได้

ไวยากรณ์

=Substitute( text, old_text, new_text, nth_appearance )
=Substitute(เซลล์อ้างอิง,สิ่งที่ต้องการให้เปลี่ยนไป,ให้เปลี่ยนเป็น,ลำดับของตัวที่ต้องการให้เปลี่ยน)

ตัวอย่าง



=SUBSTITUTE(B3,"t",999,1) ต้องการเปลี่ยน t ในเซลล์ B3 ให้เป็น 999 , t ลำดับที่ 1

=SUBSTITUTE(B4,"t",999,2) ต้องการเปลี่ยน t ในเซลล์ B4 ให้เป็น 999 , t ลำดับที่ 2

=SUBSTITUTE(B6,"t",999,0) ต้องการเปลี่ยน t ในเซลล์ B6 ให้เป็น 999 , t ลำดับที่ 0
ลำดับที่ 0 ไม่มีในเซลล์ B6 จึงส่งกลับค่าเป็น #VALUE!

=SUBSTITUTE(B8,"t",999,3) ต้องการเปลี่ยน tในเซลล์ B8 ให้เป็น 999, t ลำดับที่ 3 ในเซลล์ B8 มี t เพียง 2 ตัว ดังนั้น จึงส่งกลับค่า เป็น ตัวเดิม


ลำดับที่ต้องการให้เปลี่ยนนี้สำคัญ ถ้าไม่ระบุไว้ว่าเป็นลำดับไหน หากในเซลล์นั้นๆ มีตัวที่ต้องการให้เปลี่ยนซ้ำกัน ตัวที่ซ้ำกันเหล่านั้นจะถูกเปลี่ยนให้เป็นตัวใหม่ทั้งหมด เช่น


ในเซลล์ B2 ไม่ได้ถูกระบุลำดับที่ต้องการให้เปลี่ยน ดังนั้น t จึงถูกเปลี่ยนเป็น 999 ทั้งหมด ( 2 ตัว)

ในเซลล์ B3 ไม่ได้ถูกระบุลำดับที่ต้องการเปลี่ยน ดังนั้น - จึงถูกเปลี่ยนเป็น ค่าว่างทั้งหมด (4 ตัว)


ข้อสังเกตุอีกประการ คือ ตัวอักษรภาษาอังกฤษ ตัวพิมพ์เล็กพิมพ์ใหญ่ มีผลต่อการใช้คำสั่งนี้ด้วย เช่น



ที่เซลล์ B4 ไม่มีตัว T (พิมพ์ใหญ่) ทำให้ไม่สามารถเปลี่ยนแปลงใดๆ จึงส่งกลับค่าออกมาเป็นตัวเดิม (Start)

ลองดูอีกซักชุด


ตัวอย่างน่าสนใจ เจอบ่อย


ตามด้วย