สูตรคำนวณ

[ChoBkuN]

รู้สึกว่า Excel จะมอง b20 เป็นค่าอย่างอื่นที่ไม่ใช่ 0

ยังเป็นปัญหาเหมือนกับ row(indirect("1:"&len()) ในสูตรดึงตัวเลข
อาจารย์เคยบอกไว้ว่า เป็นการมองการเปลี่ยนแปลงครั้งสุดท้าย
แต่ลองดู row = บอกตำแหน่งrow ของ cell นั้นๆ
indirect = อ้างอิง cell ที่อ้างอิง cell นั้นๆ
รวมกันแล้วกลายเป็นจำนวนตัวอักษรได้อย่างไร ????

[snasui]

ช่วยยกตัวอย่างสูตรทั้งหมดมาด้วยครับ จะได้ช่วยดูได้ สำหรับสูตร Row(Indirect("1:"&Len())) ไม่น่าจะมีประเด็นครับ

[ChoBkuN]

เป็นการดึงค่าเฉพาะตัวเลขในข้อความ
ผมมักใช้ในการคำนวณรวมใบกำกับภาษีอย่างย่อ เช่น ค่าเช่าลอกเกอร์ 30 บาท
ผมก็จะใช้สูตรนี้ดึงค่าตัวเลข 30 ออก มาเพื่อใช้ในการคำนวณต่อไป คือ คูณกับจำนวนใบกำกับภาษีที่มี ก็จะได้จำนวนเงินทั้งหมด

ยกตัวอย่างว่า "ค่าเช่าลอกเกอร์ 30 บาท" อยู่ใน cell A1

=lookup(9.99999999999999e+307,--mid(a1,min(search({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),row(indirect("1:"&len(a1)))))

ผมคิดว่าเข้าใจทุกอย่างแล้ว ยกเว้น row(indirect())

mid(a1,ค่า min ที่ได้จากการ search, จำนวนตัวอักษร ซึ่งมาจากสูตร Row(Indirect())
สูตร row indirect รู้ได้ไงว่ามีตัวเลข กี่ตัวอักษร

[snasui]

สูตรนี้เป็นสูตรที่ซับซ้อนและเป็นสูตร Array มองแต่ row(indirect("1:"&len(a1))) ไม่ได้ครับ

เนื่องจากมันอยู่ในฟังก์ชั่น Mid จึงต้องมอง

mid(a1,min(search({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),row(indirect("1:"&len(a1))))

ซึ่ง row(indirect("1:"&len(a1))) จะให้ตัวเลขลำดับออกมาครับ ลำดับสุดท้ายเป็นเท่าไรก็แล้วแต่ว่านับ Len(A1) เป็นเท่าไร ถ้า Len(A1) แล้วได้ 5 ผลลัพธ์ของสูตรนี้ก็จะเป็น

{1,2,3,4,5} เช่นนี้เป็นต้น

ส่วน min(search({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) เป็นการหาค่าน้อยที่สุดที่เจอค่าตัวเลขใด ๆ จาก 0-9 ใน A1&"0123456789" ผมสมมุติว่าเจอลำดับที่ 5

เมื่อนำเข้ามาประกอบกันสูตรก็จะกลายเป็น

=Mid(A1,5,{1,2,3,4,5})

หมายความว่าให้ตัด A1 ดังนี้ เริ่มจากอักขระที่ 5 มา 1 ตัว, เริ่มจากอักขระที่ 5 มา 2 ตัว ,..., เริ่มจากอักขระที่ 5 มา 5 ตัว และเมื่อตัดมาแล้วก็ใช้ -- เข้าไปแปลงค่าที่ตัดมาได้ให้เป็นตัวเลข

จากนั้นใช้ Lookup(9.99999999999999e307 เข้าไปหาว่าตัวเลขสุดท้ายคืออะไรให้เอามาแสดงผล

[ChoBkuN]

สมมติผมพิมพ์ 0123456789
มีคีย์ลัดให้กลายเป็น {0,1,2,3,4,5,6,7,8,9} หรือเปล่าครับ

[snasui]

ไม่มีครับ

[ChoBkuN]

ขอบคุณครับ ผมพอจะเข้าใจแล้วครับ
lookup นี้หลักการเดียวกับที่เขียนใน snasui.blog ~~
มองค่าสุดท้ายใน column หรือใน row
=lookup(9.999999999999e307,1:1)
ก็คือจะมองค่าสุดท้ายที่เป็นตัวเลขเท่านั้นนั่นเอง อย่างนี้นี่เอง!!

แล้วสูตรมองค่าสุดท้ายทั้ง ตัวเลขและตัวอักษร
=Lookup(2,1/(A1:A65535<>""),A1:A65535) >> หาในคอลัมน์ A
ทำไม lookup อันนี้ ถึงให้มองค่า 2
lookup vector อ้างอิง 1/(A1:A65535<>"") >> มันไม่ใช่ช่วง array นี่ครับ มี เครื่องหมายหาร กับ logic <>"" ด้วย
result a1:a65535 >> ให้แสดงผลใน column a นั้น

คำถามก็คือ 2 คืออะไร
1/a1:65535<>"" คืออะไร ครับ

[snasui]

lookup vector อ้างอิง 1/(A1:A65535<>"") >> มันไม่ใช่ช่วง array นี่ครับ

มันคือช่วง Array ครับ

ประการแรกการคำนวณจะดูก่อนว่า (A1:A65535<>"") ได้ค่าอะไรบ้าง ซึ่งเป็นการเทียบช่วงข้อมูลกับค่าเดี่ยว ๆ นั่นคือ

A1<>""
A2<>""
...
A65535<>""

ผลลัพธ์ที่ได้มี 2 ค่าคือ True และ False ซึ่งมองภาพรวมสูตรจะออกมาเป็น

1/({True,False,True,False...) เช่นนั้นเป็นต้น

เมื่อนำ 1 ไปตั้งหารด้วย True ก็จะได้ค่า 1 และเมื่อหารด้วย False ก็จะได้ค่า #Div/0! สูตรก็จะได้ออกมาเป็น

{1,#Div/0!,1,#Div/0!...) เช่นนี้เป็นต้น

และเมื่อมองภาพใหญ่ก็จะได้เป็น

=Lookup(2,{1,#Div/0!,1,#Div/0!...),A1:A65535)

ความหมายคือให้หาค่าที่น้อยกว่าหรือเท่ากับ 2 จาก {1,#Div/0!,1,#Div/0!...) แล้วนำค่าที่ตรงกันใน A1:A65535 มาแสดง

และจากสูตรด้านบนเราจะไม่พบว่ามีค่า 2 อยู่ในนั้นอย่างเด็ดขาดเพราะมันจะมีแต่ 1 เท่านั้นที่เป็นตัวเลข ผลลัพธ์คือค่าใน A1:A65535 ที่ตรงกับเลข 1 ตัวสุดท้ายใน {1,#Div/0!,1,#Div/0!...) ครับ

ซึ่งผมได้เขียนอธิบายไว้แล้วที่ http://snasui.blogspot.com/2009/12/blog-post_4428.html

[ChoBkuN]

ถ้างั้นเราเปลี่ยน 2 เป็น 1 ก็ได้หรือเปล่าครับ

[snasui]

เปลี่ยนได้ครับ แต่การค้นหาด้วย 2 ดูเหมือนเป็นตัวเร่งให้คำนวณได้เร็วกว่า

[snasui]

ที่ว่าจำนวนอนันต์ที่ผมบอกให้ใส่ครับ
อาจารย์บอกไม่ต้องใส่ก็ได้คำตอบเหมือนกัน
ทำไมถึงเป็นเช่นนั้น ?

ก็คือ min เลือก ค่า ต่ำสุด 0 กับ 500000 ก็น่าจะเลือก 0 สิครับ

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

รู้สึกว่า Excel จะมอง b20 เป็นค่าอย่างอื่นที่ไม่ใช่ 0
...

คำตอบคือ Min จะละเลยค่าว่างเปล่าครับ เมื่อ B20 เป็นค่าว่างเปล่าก็เลยไม่ถูกนำมาคำนวณครับ

[ChoBkuN]

อ๋อ
เยี่ยงนี้เอง