impim wrote:
รบกวนช่วยแก้สูตรช่วงเซลสีเขียวให้หน่อยได้ไหมคะ
คิดว่าตรงนี้หน้าจะผิดค่ะ.. คือไม่เข้าใจสูตรเลยแก้ไม่ถูกค่ะ รบกวนช่วยอธิบายตรงนี้ให้หน่อยนะคะ
--(--($B$4:$B$58)=INDEX({56,57},INT((COLUMNS(H22:$H22)-1)/12)+1)
ตอบแบบสั้นๆ คือ สูตรนั้นสำหรับใช้ในตารางที่ปีเดือนอยู่ในแนวนอนครับ พอเอามาใช้ขณะที่ปีเดือนอยู่แนวตั้งเลยให้ผลไม่ถูก
ส่วนของสูตรที่ยกมาเป็นเงื่อนไขเอาไว้
ตรวจปี โดยอาศัยจำนวนของคอลัมน์
>>ส่วนแรกคือ --($B$4:$B$58) คิดว่าคงเข้าใจแล้ว
(ก่อนอธิบายต่อ ตกลงกันก่อนว่า จะลองทำตามไปด้วยโดยลากคลุมสิ่งที่กำลังอธิบายในแถบสูตร -->กด F9 --> กด Esc เพื่อเข้าใจง่ายขึ้น
)
>>INDEX({56,57},INT((COLUMNS(H22:
$H22)-1)/12)+1) มี 3 สูตรที่ต้องทำความเข้าใจครับ
>>>>(1) COLUMNS(H22:$H22) สูตรนี้เป็นการนับจำนวนคอลัมน์ภายในช่วงที่ระบุครับ (ถ้าตามสูตรนี้ก็ได้ 1) ในช่วงอ้างอิงจะเห็นว่ามีเครื่องหมาย $ หน้าอักษรคอลัมน์ทั้งนี้เพื่อล็อคตำแหน่งไว้ เวลาคัดลอกสูตรไปทางขวา คอลัมน์เริ่มต้นจะไม่เปลี่ยน เช่น หากคัดลอกไปทางขวา 1 คอลัมน์ จะได้
=columns($h22:i22) =2
แบบนี้ไปเรื่อยๆ เพื่อนับจำนวนคอลัมน์แทนจำนวนเดือนครับ พอคัดลอกไปทางขวา 12 คอลัมน์ก็จะได้ ลำดับ
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
โดยH22 คือเดือน 1, ..., S22 คือเดือน 12
หากคัดลอกต่อไปทางขวาอีกก็จะได้
13, 14, 15, 16, 17, ...
และผมเอา =columns(...)
-1
ลำดับที่ได้ก็จะเปลี่ยนครับ เป็น
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
เพื่อเอาไปใช้ต่อในสูตร INT ครับ
>>>>(2) Int((columns(...)-1)/12)
สูตร int จะตัดทศนิยมทิ้งโดยไม่ปัดเศษครับ เช่น
0.2 --> 0, 0.9 --> 0
จากลำดับในข้อ (1) เมื่อเอามาหารด้วย 12 จะได้
0, 1/12, 1/6, 1/4, 1/3, 5/12, 1/2, 7/12, 2/3, 3/4, 5/6, 11/12
จากเลขชุดข้างบนจะมีค่าตั้งแต่ 0 - 0.9... ไม่ถึง 1 สูตร int จะปัดเหลือ 0 ทั้งหมด นั่นแสดงว่าต้องผ่านไปทุกๆ 12 คอลัมน์ จึงจะทำให้ค่าเพิ่มขึ้นครั้งละ 1
และผมเอา int()+1 เพื่อจะได้ลำดับ
1, 2, 3, ...
(เห็นภาพไหมครับ 12 คอลัมน์แรกจะเป็น 1 ทั้งหมด 12 คอลัมน์ถัดมาก็ 2 ทั้งหมด ไปเรื่อยๆ)
ทำแบบนี้เพื่อเอาไปใช้ต่อในสูตร Index
>>>>(3) Index({56,57},...(2)...)
สูตรนี้จะเอาข้อมูลอ้างอิงมาแสดงตามเลขลำดับครับ เช่น
index({56,57},1) = 56
index({56,57},2) = 57
จบแล้วครับ
แต่สูตรของอาจารย์จะทดแทนด้วย
Lookup(2,1/(...),Result Array)
ซึ่งไม่จำเป็นต้องนับคอลัมน์ แต่มองหาเซลล์ที่ "ไม่ว่าง" ลองทำความเข้าใจดูนะครับ
อ่อ สูตรข้างต้น หากจะแก้ไข ต้องเปลี่ยนจาก columns( ) เป็น ROWS( ) ครับ และเปลี่ยนการใช้เครื่องหมาย $ ใหม่ ลองทำดูก่อนนะครับ
