Blockdit Logo (Mobile)
เลิกงานไปเล่นกับลูก
•
16 ส.ค. 2023 เวลา 05:42 • ไอที & แก็ดเจ็ต

รู้จักกับ function 'BYROW' และ 'BYCOL'

BYROW เป็นฟังก์ชัน array ที่ใช้ลักษณะการดำเนินการทั้งแถว ตามสูตรที่กำหนดเองด้วย LAMBDA
BYCOL คล้ายกับ BYROW เพียงแต่เปลี่ยนจากการดำเนินการทั้งแถวเป็นทั้งคอลัมน์แทน
ไวยากรณ์
BYROW(array_or_range, lambda)
  • ●
    array_or_range คือ ช่วงข้อมูล
  • ●
    lambda คือ ฟังก์ชัน LAMBDA สำหรับสร้างสูตรคำนวณ
BYCOL(array_or_range, lambda)
  • ●
    array_or_range คือ ช่วงข้อมูล
  • ●
    lambda คือ ฟังก์ชัน LAMBDA สำหรับสร้างสูตรคำนวณ
(รู้จักกับ function 'LAMBDA' ใน google sheet: https://www.blockdit.com/posts/64d90d48a9c2bdf361303fb9)
ปัญหาหนึ่งในการสร้างสูตร array คือบางสูตรที่สามารถรับข้อมูลได้ทั้งค่าเดียวและหลายค่า เช่น SUM, AND, OR, COUNT ฯลฯ
ตัวอย่างเช่น หากเราต้องการหาผลรวมของข้อมูลทีละแถว ดังภาพ
ภาพที่ 1
รายการตั้งแต่ A ถึง I มีข้อมูลตั้งแต่เดือน JAN ถึง JUN ในแต่ละแถว
หากเราต้องการผลรวมแถวแรก เราใส่สูตร
=SUM(B2:G2)
ภาพที่ 2
ถ้าหากเราอยากปรับเป็นสูตร array ถ้าเราเปลี่ยนสูตรใหม่เป็น
=INDEX(SUM(B2:G10))
ภาพที่ 3
การคำนวณจะไม่คำนวณให้เราทีละแถว แต่จะรวมข้อมูลทั้งหมดในช่วง B2:G10 เลย
ดังนั้นหากเราต้องการให้คำนวณทีละแถว เราจะใช้สูตร BYROW โดยจะเริ่มใส่เพียงแถวแรกก่อน สูตรคือ
=BYROW(B2:G2,LAMBDA(x,(SUM(x))))
  • ★
    ช่วงข้อมูลคือ B2:G2
  • ★
    แทนตัวแปรให้ x คือทั้งแถว B2:G2
  • ★
    การคำนวณ SUM(x) นั่นคือ SUM(B2:G2)
ภาพที่ 4
ปรับช่วงข้อมูลจาก B2:G2 เป็น B2:G10 จะได้สูตรเป็น
=BYROW(B2:G10,LAMBDA(x,(SUM(x))))
  • ★
    ช่วงเป็น B2:G10
  • ★
    x เป็นตัวแปรทีละแถว จะได้ x เป็น B2:G2; B3:G3; ...; B10:G10 แบบนี้จนครบ
  • ★
    การคำนวณก็ทำทีละแถว จาก SUM(x) เป็น SUM(B2:G2); SUM(B3:G3);... ;SUM(B10:G10) แบบนี้จนครบ
ภาพที่ 5
ก็จะได้ผลรวมทีละแถวลงมาจนครบทุกแถว
ตัวอย่างเพิ่มเติม
มีข้อมูลการตอบแบบประเมิน 5 ข้อ โดยแต่ละข้อเป็นการตอบในรูปของระดับคะแนน 1 - 5 แล้วต้องการหาค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานของแต่ละข้อ
โดยมีข้อมูลดังภาพ
ภาพที่ 6
เริ่มจากใส่หัวข้อรายการ โดยการดึงข้อมูลจาก B2:F2 มาใส่ที่ H2 โดยใช้สูตร
=TRANSPOSE(B1:F1)
ภาพที่ 7
จากนั้นหาค่าเฉลี่ย เราจะใส่สูตร BYCOL ที่ I2 โดยใช้สูตรคือ
=BYCOL(B2:F10,LAMBDA(x,AVERAGE(x)))
  • ★
    ช่วงข้อมูล B2:F10
  • ★
    ตัวแปร x แทนค่าข้อมูลทีละคอลัมน์ คือ B2:B10, C2:C10, ..., F2:F10
  • ★
    สูตร AVERAGE(x) คือหาค่าเฉลี่ยของ x ทีละคอลัมน์
ภาพที่ 8
สูตรจะแสดงผลไปทางขวา เนื่องจากคอลัมน์ที่อ้างอิงมีลักษณะเพิ่มไปทางขวา เราจะปรับให้แต่ละค่าเลื่อนลงมา โดยใช้ TRANSPOSE ครอบสูตรเดิม เหมือนกับ 'รายการ' ได้สูตรคือ
=TRANSPOSE(BYCOL(B2:F10,LAMBDA(x,AVERAGE(x))))
ภาพที่ 9
ในส่วนการกระจาย หาส่วนเบี่ยงเบนมาตรฐานก็ใช้สูตรเดียวกัน เพียงแต่เปลี่ยนจาก AVERAGE เป็น STDEV.S ได้สูตรคือ
=TRANSPOSE(BYCOL(B2:F10,LAMBDA(x,STDEV.S(x))))
ภาพที่ 10
ปรับข้อมูลช่วง I2:J6 ให้เป็นทศนิยม 2 ตำแหน่งเพื่อความสวยงาม
ภาพที่ 11
ปรับรูปแบบให้สวยงาม เป็นอันเสร็จเรียบร้อย
ภาพที่ 12
BYROW และ BYCOL เป็นฟังก์ชัน array ที่อาจไม่ได้ใช้บ่อย เพราะสามารถเลี่ยงไปใช้ฟังก์ชันปกติแล้วทำการคัดลอกลงมาได้ แต่ในบางครั้งเรามีความจำเป็นต้องใช้สูตร array เพราะไม่สามารถทำการคัดลอกระหว่างการดำเนินงานได้
หากบทความนี้เป็นประโยชน์ ฝากแชร์เพื่อเป็นกำลังใจให้ผู้เขียนด้วยนะฮับ
โฆษณา