ไม่ใช่แค่สูตร VLookup เท่านั้น สูตรเก่าที่เคยใช้ทุกสูตร เมื่อนำมาใช้กับ Excel 365 ที่ทำงานแบบ Dynamic Array จะมีวิธีการสร้างสูตรแบบใหม่ตามนี้
1. ไม่ต้องใส่เครื่องหมาย $ อีกต่อไป
2. ไม่ต้องสร้างสูตรหนึ่งเพื่อหาค่าหนึ่งอีกต่อไป
3. ไม่ต้องเสียเวลา Copy สูตรไปใช้หาค่าอื่นอีกต่อไป
เมื่อใช้ Excel 365 ให้เริ่มจากสร้างสูตรแค่เซลล์แรกเซลล์เดียว พอกดปุ่ม Enter จะพบว่า Excel ช่วยกระจายสูตรที่สร้างไว้นั้นหาทุกค่าที่ต้องการให้ครบทุกค่าทันที ทั้งนี้วิธีการสร้างสูตรต้องเปลี่ยนตามไปด้วย กล่าวคือ
วิธีสร้างสูตรแบบเดิม
=VLookup( ค่าที่ใช้หา, พื้นที่ตารางฐานข้อมูล, เลขที่ column ของคำตอบ, 0 )
เปลี่ยนตรงส่วนของค่าที่ใช้หาเป็น
=VLookup( พื้นที่ตารางเก็บค่าที่ใช้หา, พื้นที่ตารางฐานข้อมูล, เลขที่ column ของคำตอบ, 0 )
ตามภาพนี้ เมื่อต้องการใช้ทุกค่าในตาราง F3:F4 ว่ามีชื่ออะไรกับตัวเลขเป็นเท่าไร
เซลล์ G3 สร้างสูตร =VLOOKUP( F3:F4, B3:D7, 2, 0 )
เซลล์ H3 สร้างสูตร =SUMIF( B3:B7, F3:F4, D3:D7 )
ลองแกะตัวอย่างนี้ดู จะพบว่าสูตร Match กับ Index ทำงานแบบ Dynamic Array เช่นกัน
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1KDKJDE0JV0in9T5KkYTsDGorwArsK77P/view?usp=sharing
Dashboard ที่สร้างร่วมกับ Dynamic Array จะกลายเป็น Dynamic Dashboard ที่ปรับการแสดงผลได้ยืดหยุ่นยื่งขึ้น สามารถใช้พื้นที่ตารางเดียวที่จำกัดพื้นที่บนหน้าจอให้เปลี่ยนไปแสดงผลแบบอื่น
แฟ้มที่แชร์ให้ใช้ร่วมกันจะสามารถนำมาใช้งานได้ง่ายขึ้น จากเดิมที่ใช้ PivotTable แล้วต้องตามคนสร้างให้มาแกัไขใหม่ จะเปลี่ยนวิธีการใช้งานไปเป็นให้คลิกเปลี่ยนค่าที่เป็นเงื่อนไขแล้วสูตรจะปรับโครงสร้างรายงานใหม่ให้เลย ไม่ว่าใครก้อทำได้โดยเฉพาะหัวหน้าที่ใช้ Excel ไม่คล่อง
จากเดิมที่เราต้องคอยแก้สูตรให้ขยายตัวตามจำนวนข้อมูลหรือต้องเปลี่ยนตารางให้เป็น Table ก่อน พอใช้ Dynamic Array จะช่วยปรับตำแหน่งอ้างอิงตามให้ทันที แม้แต่เส้นกราฟก็จะปรับตามโดยอัตโนมัติ
Dynamic
array จะช่วยทำให้ไม่ต้องเลือกพื้นที่ทั้ง column เช่น =Sum(A:A)
โดยเปลี่ยนไปให้อ้างอิงกับเซลล์แรกของพื้นที่ที่เป็น Dynamic Array
=Sum(A1#) แทน Excel จะหายอดรวมจากเซลล์ A1 ถึงเซลล์สุดท้ายให้ทันที
Array คือ ข้อมูลที่มีค่าตั้งแต่ 2 ค่าขึ้นไป หรือพื้นที่ตารางตั้งแต่ 2 เซลล์ขึ้นไป ซึ่งใน Excel รุ่นก่อน 365/2021 หากอยากนำมาใช้งานต้องเสียเวลาเลือกพื้นที่ให้มีขนาดพอดีแล้วกดปุ่ม Ctrl + Shift + Enter เพื่อสร้างวงเล็บปีกกาปิดหัวท้ายสูตรก่อนเสมอ ส่วนใน Excel รุ่นใหม่สามารถใช้งานได้ทันทีโดยไม่ต้องกด 3 ปุ่มนี้พร้อมกันอีกต่อไป แค่สร้างสูตรลงไปในเซลล์เดียว พอกดปุ่ม Enter จะพบว่า Excel สร้างสูตรกระจายค่าทั้งหมดลงไปในพื้นที่ตารางหลายเซลล์ให้เอง
ถ้าพื้นที่ไม่ว่างพอ ไม่สามารถกระจายค่าลงไปให้ครบก็จะแสดงคำเตือนว่า #SPILL!
Dynamic array จะช่วยทำให้ไม่ต้องเลือกพื้นที่ทั้ง column เช่น =Sum(A:A) โดยเปลี่ยนไปให้อ้างอิงกับเซลล์แรกของพื้นที่ที่เป็น Dynamic Array =Sum(A1#) แทน Excel จะหายอดรวมจากเซลล์ A1 ถึงเซลล์สุดท้ายให้ทันที
แต่ถ้าอยากดึงค่าเพียงค่าเดียวในพื้นที่ Dynamic Array ให้ใส่ @ นำหน้าตำแหน่งเซลล์ จะได้ค่าตรงตำแหน่ง row เดียวกันออกมาใช้
นอกจาก Dynamic Array ที่ Excel จัดการให้แล้ว ต้องเรียนรู้วิธีการคำนวณแบบ Array ด้วยจะทำให้สามารถพลิกแพลงสูตรรุ่นใหม่ได้ตามใจ
#SPILL! เป็น Error ที่พบบ่อยมากใน Excel 365 หรือรุ่นตั้งแต่ 2021 เป็นต้นมา ที่เริ่มต้นความสามารถพิเศษที่เรียกว่า Dynamic Array โดยสาเหตุหนึ่งที่คำเตือนนี้จะเกิดขึ้น คือ ต่อเมื่อสูตรที่สร้างขึ้นนั้นไม่สามารถกระจายค่าที่หาได้ออกไปจนครบทั้งตาราง
Spill แปลว่า ล้น หรือ หก เช่น เวลาเทน้ำจนเต็มแก้วแล้วล้น หรือมีอะไรมาขวางทางน้ำที่ไหล น้ำก็จะล้น
วิธีแก้ไขแบบง่ายๆ ให้ใช้สูตร CountA ตรวจสอบก่อนว่าพื้นที่ด้านล่างที่ติดกับสูตรที่จะกระจายค่าออกไปนั้นเป็นช่องว่างที่ไม่มีอะไรกรอกไว้ไหม โดยใช้สูตรแบบนี้
=IF( CountA(Range) = 0, สูตรที่ต้องการใช้, 0)
ถ้านับแล้วพบว่าในพื้นที่นั้นสูตร CountA = 0 แสดงว่าไม่มีอะไรมาขวางก็ให้ใช้สูตรนั้นได้ แต่ถ้าไม่ใช่ให้แสดงค่า 0 แทน
แทนที่จะใช้สูตรแก้ไข แนะนำให้สร้างสูตรควบคุมให้ตัวสูตรกระจายผลลัพธ์ออกไปเท่าที่มีพื้นที่ว่างเตรียมไว้เพียงพอ โดยใช้สูตรตามนี้
=CHOOSEROWS( MyData, SEQUENCE( Transactions, 1, (Transactions*(PageNum-1))+1) )
MyData เป็นพื้นที่ข้อมูลทั้งหมด ซึ่งมีจำนวน 380 รายการ หาได้จากสูตร Rows(MyData) ในเซลล์ C2
SEQUENCE( Transactions, 1, (Transactions*(PageNum-1))+1) ) ทำหน้าที่ดึงข้อมูลแต่ละหน้า โดย
เซลล์สีเขียว F2 ตั้งชื่อว่า Transactions เป็นจำนวนรายการต่อหน้า ตอนนี้สมมติว่าอยากให้แสดงทีละ 20 รายการต่อหน้า
(Transactions*(PageNum-1))+1) เป็นสูตรหาเลขที่ลำดับรายการเริ่มต้นของแต่ละหน้า
หน้าที่ 1 หาเลขที่รายการเริ่มต้นมาจาก 20*(1-1)+1 ก็จะเริ่มต้นที่รายการที่ 1
หน้าที่ 2 หาเลขที่รายการเริ่มต้นมาจาก 20*(2-1)+1 ก็จะเริ่มต้นที่รายการที่ 21
หน้าที่ 3 หาเลขที่รายการเริ่มต้นมาจาก 20*(3-1)+1 ก็จะเริ่มต้นที่รายการที่ 41
หน้าที่ 9 หาเลขที่รายการเริ่มต้นมาจาก 20*(9-1)+1 ก็จะเริ่มต้นที่รายการที่ 161
หน้าที่ 15 หาเลขที่รายการเริ่มต้นมาจาก 20*(15-1)+1 ก็จะเริ่มต้นที่รายการที่ 281
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1EJaRC4PNHa3gArrYkpai0TmEmzA2qJYA/view?usp=sharing
วิธีนี้นอกจากใช้ช่วยแก้ Spill แล้ว ยังช่วยทำให้สามารถเลือกดูรายการไหนก็ได้โดยไม่ต้องเสียเวลาไปเลื่อนหน้าจอ หรือให้นำข้อมูลที่เลือกมาแต่ละหน้านี้ไปใช้คำนวณต่อ Excel จะใช้เวลาคำนวณเหลือนิดเดียว
ถ้าเป็นแฟนพันธุ์แท้ของ PivotTable พอใช้คำสั่ง Filter เลือกให้แสดงรายการไหนก็จะเหลือแต่รายการนั้นๆ รายการอื่นที่ไม่ใช่จะหายไปใช่ไหม
สูตร GroupBy มีทางเลือกที่เหนือกว่า PivotTable
1. อยากให้แสดงเฉพาะรายการนั้นโดยตัดรายการอื่นที่ไม่ใช่ทิ้งไป แบบ PivotTable ตามภาพด้านหลัง
หรือ
2. อยากให้ตำแหน่งรายการนั้นยังคงอยู่ที่เดิม โดยรายการอื่นแสดงค่าเท่ากับ 0 ตามภาพด้านหน้า
วิธีการใช้สูตร GroupBy แต่ละแบบ ต่างกันตรงวิธีนำเงื่อนไขไปใส่ลงไปในวงเล็บว่าจะใส่ไว้ในส่วนไหน ซึ่งตามตัวอย่างนี้ใช้เงื่อนไข ((COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0)) ที่ทำหน้าที่ควบคุมการแสดงรายการตามที่กรอกไว้ในตาราง ItemChoice
แบบแรก ให้นำสูตรที่ใช้เป็นเงื่อนไข ใส่ลงไปในวงเล็บหลังคอมมา 4 ตัว ซึ่งเป็นส่วนที่เรียกว่า filter-array
=GROUPBY( CHOOSECOLS(B3:E27,K1), F3:G27, SUM,,,,((COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0)))
แบบที่สอง นำส่วนของเงื่อนไขไปคูณกับ Range ตัวเลข
=GROUPBY( CHOOSECOLS(B3:E27,K1), F3:G27
*((COUNTIF(ITEMChoice,ITEM)>=1)+(COUNTA(ITEMChoice)=0)),SUM)
Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1Ff0ymF_4saU7bQ5sUuWE0OATk7_X73_F/view?usp=sharing
คุณคิดว่าหลักการสร้างรายงานที่ดี ควรเลือกใช้แบบไหนดีกว่ากัน
ในส่วนตัวผมชอบแบบที่สองที่แสดงเลข 0 เพราะโดยทั่วไปคนที่มองรายงานเป็นประจำจะคุ้นเคยกับหน้าตารายงานว่ารายการไหนอยู่ตรงไหน พอเปิดหน้ารายงานขึ้นมาก็จะเหลือบตาไปมองตรงตำแหน่งเดิม "บนหน้าจอ หรือ บนหน้ากระดาษ" สะดวกกว่าการมองหารายการที่ย้ายไปที่อื่น
😍 ที่สบายใจมากขึ้นก็ตอนที่มีทางเลือกทำให้หัวหน้าได้ดูอย่างที่หัวหน้าอยากดูนี่แหละครับ
ปล แต่ถ้ารายการที่ต้องการมองหาไปอยู่ไกลๆล่ะ แทนที่จะต้องเสียเวลาเลื่อน row ไปมองหา ยังมีวิธีการอื่นที่จะช่วยมองหาได้ง่ายขึ้นครับ รออีกนิดจะนำมาอวดให้ดู
ถ้าสงสัยสูตรว่าคิดมาได้ยังไง ทำไมจึงซ้อนกันหลายชั้น ขอให้ติดตามดูโพสต์ก่อนหน้าตามลิงก์นี้ครับ ผมเก็บโพสต์ไว้ให้ย้อนไปศึกษากัน
https://excelexpertlibrary.blogspot.com/2025/04/pivottable-groupby-excel-365.html
https://excelexpertlibrary.blogspot.com/2025/04/groupbycountif-pivottable.html
https://excelexpertlibrary.blogspot.com/2025/04/pivottable-filter-365-groupby-data.html
ในกรอบสีม่วง อยากดู Item รายการอะไรก็คลิกเลือกได้
👆👆 ถ้าคลิกเลือกซ้ำ จะถูกเตือนด้วยสีแดง แต่ยอดที่หาได้ยังคงถูกต้อง
👋 ถ้าไม่เลือก Item เลย ตารางด้านขวาจะเปลี่ยนไปหายอดทั้งหมดแทน
ปรับสูตรนิดหน่อย จากเดิม
=GROUPBY(CHOOSECOLS(B3:E27,K1),F3:G27*(COUNTIF(ITEMChoice,ITEM)>=1),SUM)
เพิ่มสูตรนับต่อท้ายเข้าไปเป็น
=GROUPBY(CHOOSECOLS(B3:E27,K1),F3:G27*((COUNTIF(ITEMChoice,ITEM)>=1)
+(COUNTA(ITEMChoice)=0)),SUM)
☝️ ถ้าไม่ได้เลือกแสดง Item อะไรเลย สูตร COUNTA(ITEMChoice) จะนับว่า =0 ซึ่งพอสูตร COUNTA(ITEMChoice)=0 นี้เป็นจริงขึ้นมา จะคืนค่า TRUE ออกมา ทำให้เมื่อนำไปคูณกับ F3:G27 ก็จะเลือกตัวเลขทั้งหมดมาใช้คำนวณหายอดรวม
Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1OCXLG1GUGZnwSP3IXykBgj1U3EwN9Ru3/view?usp=sharing
++++++++++++++++++++++++++++++++++
ถ้าสงสัยสูตรว่าคิดมาได้ยังไง ทำไมจึงซ้อนกันหลายชั้น ขอให้ติดตามดูโพสต์ก่อนหน้าในเพจนี้หรือไปดูตามลิงก์นี้ครับ ผมเก็บโพสต์ไว้ให้ย้อนไปศึกษากัน
https://excelexpertlibrary.blogspot.com/2025/04/pivottable-groupby-excel-365.html
https://excelexpertlibrary.blogspot.com/2025/04/groupbycountif-pivottable.html
https://excelexpertlibrary.blogspot.com/2025/04/pivottable-filter-365-groupby-data.html
PivotTable ว่ากันตรงๆเป็นของใช้กับรายงานสำหรับเด็กๆ ไม่เหมาะสำหรับให้ผู้ใหญ่หรือผู้บริหารนักหรอก
ถ้าในฐานข้อมูลมีหัวข้อเยอะมาก เวลาที่ต้องการแสดงแค่บางหัวข้อต้องเสียเวลามา Filter หรือใช้ Slicer ตัดรายการที่ไม่ต้องการทิ้งไป เช่น ถ้าต้องการแสดงแค่บางจังหวัด พอสร้างตาราง Pivot ออกมาจะแสดงทุกจังหวัดที่บันทึกไว้ทั้งหมดขึ้นมาก่อน จากนั้นต้องเสียเวลามาใช้ Filter/Slicer เพื่อเลือกแสดงแค่บางจังหวัด
หลักการใช้ Excel สร้างรายงานสำหรับผู้บริหาร ต้องยึดหลัก Management by Exception นั่นคือ ตั้งแต่แรกต้องทำให้แสดงเฉพาะข้อมูลที่สำคัญหรืออยากจะแสดงเท่านั้น ไม่ใช่ว่าต้องมาเสียเวลาตัดทิ้งทีหลัง
ตัวอย่างตามภาพนี้ ในกรอบสี่เหลี่ยมสีเขียว ใช้คำสั่ง Data > Validation สำหรับให้ผู้บริหารคลิกเลือกเฉพาะ ItemChoice ที่อยากจะแสดง จากนั้นตารางฐานข้อมูลด้านซ้ายกับตารางหายอดรวมแยกประเภทด้านขวาสุดก็จะเปลี่ยนสีพื้นแสดงรายการที่ตรงกับ ItemChoice ที่เลือกให้เห็นชัดเจนว่าอยู่ตรงไหน พร้อมหายอดรวมที่เกี่ยวข้องให้เท่านั้นด้วย รายการอื่นจะกลายเป็น 0 ไปเลย
สูตรที่ทำหน้าที่แบบนี้ได้มาจากการซ้อนสูตร CountIF เข้าไป
=GROUPBY( CHOOSECOLS( B3:E27,K1 ), F3:G27*(COUNTIF(ITEMChoice,ITEM)>=1), SUM )
CHOOSECOLS( B3:E27,K1 ) ทำหน้าที่เลือกแสดงตามหัวตารางที่เลือกได้ในช่องสีเหลือง
F3:G27 เป็นพื้นที่ตารางตัวเลข Budget กับ Actual
(COUNTIF(ITEMChoice,ITEM)>=1) เป็นสูตรสำคัญที่ช่วยเลือกเฉพาะรายการที่ตรงกับที่เลือกใน ItemChoice ซึ่งสูตรนี้แหละที่ทำให้เหนือกว่าการใช้ PivotTable
ถ้าลากทับส่วนของสูตร (COUNTIF(ITEMChoice,ITEM)>=1) แล้วกดปุ่ม F9
จะพบว่าสูตรนี้ทำหน้าที่ตรวจสอบหาตำแหน่งรายการว่าตรงไหนที่ตรงกับ ItemChoice บ้าง
{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
ตำแหน่งที่เป็น TRUE เป็น Item ที่ตรงกับที่เลือก พอนำไปคูณกับ F3:G27 จะเก็บตัวเลขเฉพาะตำแหน่งที่เท่ากับ TRUE ส่วนที่เป็น FALSE จะไม่นำไปใช้หายอดรวม
สาเหตุที่ใช้สมการ >=1 นั่นเพื่อให้ไม่ว่าจะคลิกช่องสีเขียวแล้วเผลอเลือก Item ซ้ำก็จะไม่ทำให้คำนวณผิดพลาด ลองดูแล้วจะเห็นเองครับ
Download ได้จาก
https://drive.google.com/file/d/1OCXLG1GUGZnwSP3IXykBgj1U3EwN9Ru3/view?usp=sharing
ตัวอย่างนี้ต่อเนื่องมาจากตัวอย่างก่อนหน้าที่ไม่ซับซ้อนเท่าไร เชิญติดตามดูได้จาก Excel Expert Blog ที่
"ต้องหาทางทำตารางเดียวชีทเดียวเปลี่ยนเป็นอะไรก็ได้"
นี่เป็นหลักการใช้ Excel สร้างรายงานที่ผมแนะนำมาตลอด
จากภาพนี้พอคลิกเปลี่ยนชื่อหัวตารางในเซลล์ I2 ที่ใส่สีเหลืองจากคำว่า CODE > DEPARTMENT > CATEGORY > ITEM จะสร้างรายงานสรุปยอดตัวเลข BUDGET กับ ACTUAL ออกมาให้ทันที ... ตรงตารางตำแหน่งเดียวกัน
เซลล์ I3 ใช้สูตร =GROUPBY( CHOOSECOLS(B3:E27, I1), F3:G27, SUM)
เซลล์ I1 ใช้สูตร =MATCH(I2, B2:E2, 0) ทำหน้าที่หาว่าชื่อหัวตารางในเซลล์ I2 ที่คลิกเปลี่ยนได้นั้นมาจาก Column ที่เท่าไร เช่น
CODE เป็น Column ที่ 1
DEPARTMENT เป็น Column ที่ 2
CATEGORY เป็น Column ที่ 3
ITEM เป็น Column ที่ 4
พอเซลล์ I1 หาเลขที่ Column ได้แล้ว สูตร CHOOSECOLS( B3:E27, I1) จะเลือกพื้นที่ Column ตามออกมาใช้ในสูตร GroupBy
=GROUPBY( CHOOSECOLS(B3:E27, I1), F3:G27, SUM)
มีโครงสร้างภายในวงเล็บตามนี้
=GROUPBY( พื้นที่ column ที่ต้องการจัดกลุ่ม, พื้นที่ column ที่เก็บตัวเลข, หายอดโดยใช้สูตร SUM)
นอกจากสูตร GroupBy ใช้แทน PivotTable ได้แล้วยังทำหน้าที่แทนสูตร Filter, Unique, SumIF, CountIF ได้อีกด้วย หากเปลี่ยนคำว่า SUM ด้านท้ายเป็นสูตรอื่นได้อีกสิบกว่าสูตรทีเดียว
Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1YjRLA2jLLRBgfnDTnd6YZ7L9Ur-uDW_t/view?usp=sharing
+++++++++++++++++++++++++++
นอกจาก GroupBy แล้วยังมีสูตร PivotBy ที่ทำหน้าที่เหมือน PivotTable ที่ใช้ 2 แกนทั้ง Row Field กับ Column Field ได้อีกด้วย
เวลาไปหาหมอดู จะถูกถามว่าเกิดวันไหน เวลาอะไร เกิดที่ไหน เพื่อนำไปผูกดวงหาลัคนาและตำแหน่งดาว จะผิดหรือถูก เริ่มต้นนี่แหละสำคัญมาก
เวลาตกฟาก คือ ตอนที่ออกมาท้องคุณแม่แล้วร้องอุแว้แรก ถือว่าธาตุครบทั้งดินน้ำไฟลมครบถ้วนเป็นเวลาไหน ถ้าไม่รู้จะทำนายให้ถูกยากมาก ทุก 4 นาที ลัคนาจะเปลี่ยนไป 1 องศา
เกิดที่ไหน จังหวัดอะไร ถ้าบอกได้ถึงอำเภอตำบลด้วยยิ่งดี แต่ละเมืองจะเห็นเวลาที่ดวงอาทิตย์ขึ้นต่างกัน จังหวัดทางอีสานจะเห็นก่อนกรุงเทพ
ที่กล่าวมานี้เป็นแค่จุดเริ่มที่ต้องทราบรายละเอียด พอถึงขั้นจะนำมาหาลัคนากับตำแหน่งดาวนี่แหละ หมอดูส่วนใหญ่ที่เป็นแค่หมอดูแต่ไม่เชี่ยวชาญด้านการคำนวณ น้อยคนนักที่จะทราบว่าวิธีคำนวณหาลัคนากับวิธีคำนวณหาตำแหน่งดาวคิดกันมาได้โดยวิธีไหน
เริ่มต้นจากเวลาเกิด ต้องทราบไว้ก่อนว่าทุกจังหวัดในประเทศไทยใช้เวลามาตรฐานเดียวกันทุกที่ จึงต้องเอาตำแหน่งเมืองที่เกิดมาปรับระยะทาง เพื่อปรับเวลาที่ดวงอาทิตย์ขึ้นก่อนหลังแล้วจะได้เวลาเกิดที่แท้จริง
เวลาเกิดที่ปรับแล้วนี่แหละที่นำไปใช้หาลัคนา แต่อย่านำเวลาไปใช้กับการหาตำแหน่งดาวทีเดียวล่ะ เพราะสูตรที่ใช้คำนวณตำแหน่งดาวใช้เวลากรีนิช+7 ชั่วโมง โดยถือว่าประเทศไทยไม่ว่าจะเป็นจังหวัดหรือเกิดที่ตำแหน่งไหนก็ใช้เวลาเดียวกันทุกแห่ง
ถ้าใช้วิธีเปิดปฏิทินโหรที่บอกตำแหน่งดาวตอนเที่ยงคืนไว้ ก็มักใช้วิธีเทียบบัญญัติไตรยางค์กันเป็นสัดส่วนระหว่างวันเป็นเท่าไร ซึ่งวิธีนี้ง่ายแต่ไม่ถูกต้องเพราะดวงดาวไม่ได้โคจรเคลื่อนที่เป็นเส้นตรง ช่วงนั้นอาจเดินช้าเดินเร็วหรือเดินถอยหลังก็ได้
การคำนวณหาลัคนา โดยทั่วไปมักใช้แบบอันโตนาทีที่แบ่งเวลาที่ดวงอาทิตย์ใช้เวลาโคจรในแต่ละราศีเป็นเท่านั้นเท่านี้นาที วิธีนี้ผิดพลาดได้ง่ายที่สุด แต่ชอบใช้กันเพราะง่ายดี สามารถทำได้โดยใช้เครื่องคิดเลขหรือแผ่นหมุน
ตำแหน่งของลัคนา คือ ตำแหน่งทางทิศตะวันออกของแผ่นดินว่าตอนที่เกิดนั้นชี้ไปทางราศีไหน วิธีการคำนวณที่ดีกว่าอันโตนาทีคือการใช้สูตรคำนวณทางวิทยาศาสตร์ที่ใช้พิกัดเส้นรุ้งเส้นแวงมาเป็นตัวแปรในการคำนวณ บอกทราบสถานที่กับเวลาเกิดแล้ว สูตรคำนวณจะหาตำแหน่งลัคนาออกมาให้เองโดยไม่ต้องไปปรับเวลาให้ยุ่งยาก แต่ถ้าจะคำนวณให้แม่นยำมากขึ้นควรใช้พิกัดความสูงต่ำของสถานที่เกิดมาใช้คำนวณหาลัคนาด้วย เพราะคนที่เกิดบนตึกสูงย่อมเห็นดวงอาทิตย์โผล่พ้นขอบฟ้าเร็วกว่า
สูตรที่ใช้คำนวณหาตำแหน่งดาว จะหาตำแหน่งดาวโดยมองลงมาจากจักรวาลนอกโลก ถือว่าดวงอาทิตย์เป็นศูนย์กลาง (Heliocentric) จากนั้นจึงใช้สูตรเปลี่ยนเป็นมุมที่มองออกจากพื้นโลกไปบนท้องฟ้า (Geocentric) แล้วปรับต่ออีกชั้นให้เป็นตำแหน่งดาวตามตำราโหราศาสตร์ไทย ซึ่งตัวเลขนี้เรียกว่า ค่าอายนางศะ (Ayanamsa)
ค่าอัยนางศ์ มีวิธีคิดหลายแบบว่าจะใช้ตามแบบไหน จะใช้ค่าคงที่ตามนาย Lahiri หรือตามนาย Fagan Bradley คิดมาให้ หรือจะใช้สูตรคำนวณที่คิดค้นกันขึ้นมาก็ยังได้อีก
จากเกิดวันไหน เวลาอะไร เกิดที่ไหน ยังมีตัวแปรอีกหลายอย่างที่ใช้ประกอบในการคำนวณ ตำแหน่งลัคนา ตำแหน่งดาว หรือแม้แต่ราศี อาจแปรเปลี่ยนต่างไปก็ได้ ยิ่งการทำนายที่ใช้ราศีหรือภพแบ่งเป็นช่องละ 30 องศา แค่ได้ตำแหน่งที่คร่อมแกนของราศีหรือขยับข้ามแกนไปนิดๆไม่กี่องศา คำทำนายจะกลายเป็นอีกอย่างไปแล้ว
นอกจากนี้ยังขึ้นกับสูตรที่ใช้คำนวณในโปรแกรมนั้นด้วยว่าสร้างสูตรมานานมากแค่ไหน เพราะในการคำนวณวงโคจรของดาวแต่ละดวงมีการกำหนค่าคงที่ไว้ด้วย ซึ่งค่าคงที่นี้จะยึดจากตำแหน่งดาวในปีใดปีหนึ่งเป็นหลัก (Epoch Year) ถ้าจะให้แม่นยำมากขึ้นต้องคอยปรับค่าคงที่นี้ไปเรื่อยๆด้วย
Dynamic Range เป็นพื้นที่ตารางที่ยืดได้หดได้ ทำให้สูตรคำนวณได้เร็วขึ้นเพราะไม่จำเป็นต้องใช้ข้อมูลทั้งหมดที่มี หากทำให้ดีขึ้นต้องหาทางทำให้ Moveable คือ สามารถกำหนดรายการแรกที่เริ่มได้ด้วย ไม่จำเป็นต้องใช้ข้อมูลตั้งแต่รายการแรกเสมอไป ซึ่งแต่เดิมนั้นต้องใช้สูตร Offset มาช่วย
พอเปลี่ยนมาใช้ Excel 365 ที่เซลล์ F7 ให้ใช้สูตรตามนี้แทน
=CHOOSEROWS( ChartData, SEQUENCE(5,1,Start) )
โดย SEQUENCE(5,1,Start) ทำหน้าที่สร้างเลขลำดับทีละ 5 ตัว อยากให้เลขเริ่มจากเลขอะไรก็กำหนดได้ตาม Start
ถ้า Start = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5
ถ้า Start = 2 จะได้เลขลำดับ 2; 3; 4; 5; 6
ถ้า Start = 3 จะได้เลขลำดับ 3; 4; 5; 6; 7
พอนำสูตร Sequence ไปซ้อนในสูตร ChooseRows จะช่วยเลือกรายการจากตารางเก็บข้อมูล ChartData ด้านซ้ายสุดได้รายการตามเลขลำดับที่ต้องการออกมาให้ แสดงไว้ในพื้นที่ตารางตรงกลางที่เตรียมไว้เป็น Chart Template สำหรับนำไปสร้างกราฟด้านขวาต่ออีกที
พอคลิกเปลี่ยนเลขที่ในช่องสีเหลืองที่ตั้งชื่อว่า Start จะทำให้เส้นกราฟเลื่อนไปเริ่มต้นแสดงตามปีที่ต้องการ
แทนที่จะดึงข้อมูลออกมาพร้อมกันทุก Column ใน Chart Data ถ้าอยากจะดึงข้อมูลออกมาทีละ Column จะใช้สูตร ChooseCols ซ้อนเข้าไปอีกชั้นก็ได้ แต่เพื่อทำให้เข้าใจได้ง่ายขึ้นก็เพียงเปลี่ยนสูตรมาเป็น
=CHOOSEROWS( Year, SEQUENCE(Interval,1,Begin) ) เพื่อดึงช่วงปีที่ต้องการ
=CHOOSEROWS( Income, SEQUENCE(Interval,1,Begin) ) เพื่อดึงรายการ Income ช่วงเดียวกันกับปีที่ต้องการ
ถ้า Interval = 6 และ Begin = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5; 6
ถ้า Interval = 7 และ Begin = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5; 6; 7
ถ้า Interval = 7 และ Begin = 2 จะได้เลขลำดับ 2; 3; 4; 5; 6; 7; 8
นำสูตรไปตั้งเป็น Formula Name ในชื่อ XAxis กับ YAxis แล้วนำชื่อไปเปลี่ยนสูตร Series บนเส้นกราฟ
จากสูตรเดิมที่ใช้ตำแหน่งอ้างอิง $B$5:$B$16 กับ $C$5:$C$16 ตามสูตรนี้
=SERIES( Dynamic!$C$4, Dynamic!$B$5:$B$16, Dynamic!$C$5:$C$16, 1)
ให้พิมพ์ชื่อที่ตั้งไว้ XAxis ทับ $B$5:$B$16 และพิมพ์ YAxis ทับ $C$5:$C$16 แล้วกดปุ่ม Enter สูตรจะนำชื่อแฟ้มนำหน้าชื่อสูตรมาใส่ให้เอง สูตร Series จะเปลี่ยนเป็น
=SERIES( Dynamic!$C$4, DynamicChart365.xlsb!XAxis, DynamicChart365.xlsb!YAxis, 1)
พอคลิกเปลี่ยนเลขในช่อง Interval กับ Begin เส้นกราฟก็จะยืดได้หดได้และเลื่อนปีที่เริ่มได้ตามสบาย โดยไม่ต้องพึ่งตาราง Template อีกต่อไป
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1kw00MUGDh75rNTcE2H6P8Hyfw_Z0xp_z/view?usp=sharing
+++++++++++
สำหรับ Excel รุ่นเก่าก่อน 365 เชิญดูวิธีการได้ที่หลักสูตรเคล็ดการสร้างกราฟให้เห็นปุ๊บ เข้าใจปั้บ บทเรียนที่ 26 ใช้สูตร Offset
เชิญสมัครเรียนออนไลน์ ฟรี 1 ปีได้ที่เว็บ XLSiam.com
เรียน 2 วันๆละ 3 ชั่วโมง จะได้เรียนกันเบาๆครับ
เป็นคลาสที่น่าภูมิใจมาก ได้รับเชิญให้มาถ่ายทอดความรู้ให้กับน้องๆรุ่นลูกรุ่นหลาน เพื่อเตรียมพร้อมเข้าทำงาน
อบรมวันที่สองผ่านพ้นไปแล้ว อีกครั้งหนึ่งของความภาคภูมิใจในชีวิต
ขอบคุณมากที่ให้โอกาสได้รับเชิญมาสอนคลาสนี้ เป็นความฝันที่อยากทำมานานครับ
ใช้เวลาเตรียมเนื้อหาเกือบ 3 เดือน แฟ้มตัวอย่างต้องเข้าใจได้ง่าย ช่วยทำให้เห็นว่าเวลาทำงานต้องสร้างงานทีละขั้นอย่างไร จากหัวข้อที่กำหนดไว้นั้นต้องใช้อะไรก่อนหลัง เพื่ออะไร เพราะอะไร
ระยะเวลาเรียนวันละ 3 ชั่วโมงก็สำคัญมาก ถ้าให้เรียนเต็มวัน 6 ชมก็ไม่เหมาะเพราะไม่คุ้นเคยกัน คิดถึงสมัยตัวเองเป็นนักเรียนแล้วเรียนไม่ถึงชมก็หลับแล้ว
จากภาพนี้ในเซลล์ G3 ใช้สูตร =MyLookup ทำหน้าที่ค้นหาว่ารหัส a002 มีชื่อ b กับ จำนวน 20 ออกมาให้
MyLookup นี้เป็นชื่อที่ตั้งให้กับสูตรที่ใช้อยู่เดิม วิธีนี้เรียกว่าการตั้งชื่อ Formula Name ซึ่งจะเกิดประโยชน์ ดังนี้
1. จะตั้งชื่อให้เป็นอะไรก็ได้ตามใจ ช่วยสื่อความหมายว่าสูตรนั้นทำหน้าที่อะไร เหมาะอย่างยิ่งในการใช้งานร่วมกัน
2. ช่วยย่อสูตรยากๆยาวๆให้เหลือสั้นลง เวลาจะใช้สูตรแค่กดปุ่ม F3 ดึงชื่อมาสูตรมาใช้หรือจะพิมพ์เองก็แค่พิมพ์ =ชื่อที่ตั้งไว้
3. เมื่อต้องการแก้ไข ไม่จำเป็นต้องค้นหาว่าเคยสร้างสูตรไว้ที่เซลล์ไหนบ้าง แค่เข้าไปแก้ได้โดยตรงที่เมนู Formulas > Name Manager > Edit แล้วแก้สูตรในช่อง Refers to จะส่งผลให้ทุกเซลล์ที่ใช้สูตรชื่อนั้นปรับการคำนวณใหม่ให้ทันที
วิธีการตั้งชื่อสูตร
ให้ใช้เมนู Formulas > Define Name แล้วตั้งชื่อสูตรลงไปในช่อง Name กับใส่สูตรที่ต้องการลงไปในช่อง Refers to:
เคล็ดลับการตั้งชื่อสูตร ซึ่งจะทำให้ตั้งชื่อสูตรได้ง่ายขึ้นมาก
ในช่อง Refer to: อย่าไปสร้างสูตรลงไปในช่องนี้โดยตรง เพราะยากที่จะตรวจสอบว่าสูตรทำงานได้ตามที่ต้องการหรือไม่ แต่ให้สร้างสูตรที่ต้องการลงไปในเซลล์ที่ไหนก็ได้โดยใส่ $ ให้ครบทั้งหน้า row และ column ทดสอบสูตรว่าทำงานได้เรียบร้อยก่อน จากนั้นจึงลอกสูตรไปวางลงไปในช่อง Refers to
พอกดปุ่ม OK อย่าตกใจหากเจอว่าตำแหน่งอ้างอิงในสูตรจะมีชื่อชีทแทรกเพิ่มให้เอง ในตัวอย่างนี้สูตรที่สร้างไว้เป็นสูตรตามนี้
=XLOOKUP( $F$3, $B$3:$B$7, $C$3:$D$7 )
พอลอกสูตรไปวางในช่อง Refers to แล้วกดปุ่ม OK สูตรจะเปลี่ยนไปเป็น
=XLOOKUP( Sheet1!$F$3, Sheet1!$B$3:$B$7, Sheet1!$C$3:$D$7 )
นอกจากนี้แทนที่จะใช้ตำแหน่งอ้างอิงโดยตรง ยังสามารถนำชื่อ Range Name ซ้อนเข้าไปแทนได้ด้วย ช่วยทำให้แกะสูตรได้เข้าใจได้ง่ายขึ้น
หากตั้งชื่อ Range Name ที่ลิงก์ข้ามแฟ้มเอาไว้ จะช่วยทำให้สามารถใช้ชื่อสั้นๆทำงานแทนสูตรลิงก์ข้ามแฟ้มที่มีโครงสร้างสูตรยาวเหยียด
Download แฟ้มตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1-uWmUqrn_sej6xDw-H5AwTXmkpeOfG56/view?usp=sharing
ชมคลิปได้จากหลักสูตรสุดยอดเคล็ดลับและลัดของ Excel ซึ่งเปิดให้เรียนออนไลน์ ฟรี 1 ปีได้ทุกที่ทุกเวลาที่สะดวก เชิญสมัครและเข้าเรียนได้ที่เว็บ XLSiam.com