การใช้สูตร Hyperlink ในสมุดงานเดียวกัน

[borsalino]

อาจารย์ครับ มีวิธีให้ hyperlink ไป cell ถัดจาก ROW สุดท้ายใน sheet อื่นในสมุดงานเดียวกันมั้ยครับ

ผมใช้สูตร =HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!A6","ไป") มันไปที่ A6

แต่ผมอยากให้มันไป cell ถัดจาก ROW สุดท้าย ต้องเปลี่ยนสูตรยังไงครับ

[snasui]

ลองปรับสูตรเป็นด้านล่างครับ

=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"A"&MATCH(TRUE,INDEX($A$1:$A$100<>"",0),0)+1,"ไป")

[borsalino]

ลองปรับสูตรเป็นด้านล่างครับ

=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"A"&MATCH(TRUE,INDEX($A$1:$A$100<>"",0),0)+1,"ไป")

อาจารย์ครับ สูตรนี้ตัว =MATCH(TRUE,INDEX($A$1:$A$100<>"",0),0)
มันได้เลข ROW สุดท้ายของ sheet1 ที่ผมใช้ค้นหาอ่ะครับ มันไม่ได้ตาม ROW สุดท้ายของ sheet อื่นๆตาม "&B3&" ครับ

คือ sheet1 ผมใช้ให้ hyperlink ไป sheet อื่น(2,3,4,..) และกำหนด cell ถัดจาก ROW สุดท้าย

[borsalino]

ต้องเป็นสูตรประมาณ =HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"A"&MATCH จากชีทอื่น,"ไป") หรือเปล่าครับ

หรือว่าหลักการ มันควรจะเป็น
=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"A"&ตามด้วยเลข,"ไป")

ซึ่งผมต้องไป MATCH สูตรลงใน sheet2,3,4,... แล้วค่อยดึงค่ามาใช้เป็น

=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"J"&INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&B3&"'!$O$2"),"ไป")
สูตรนี้ผม MATCH ลงไปในcell O2

ปล.ในไฟล์ผมมีหลายชีท พอมีสูตรพวก VLOOKUP INDIRECT มันทำให้ไฟล์มาช้าลงมากๆเลย มีวิธีแก้มั้ยครับ

[snasui]

ควรแนบไฟล์ตัวอย่างมาด้วยเพื่อสะดวกในการตอบครับ

สำหรับการ Match ตามค่าใน Sheet ที่ระบุใน B2 สามารถปรับสูตรเป็น

=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"A"&MATCH(TRUE,INDEX(INDIRECT("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"$A$1:$A$100")<>"",0),0)+1,"ไป")

การใช้ Vlookup จำนวนมากสามารถปรับเป็นพวก Index, Match เพื่อเพิ่มความเร็วได้

กรณี Indirect ควรใช้เท่าที่จำเป็น หากใช้มากจะทำให้คำนวณช้า ควรปรับฐานข้อมูลให้อยู่ในชีทเดียวกัน ไม่ควรแยกเป็นหลายชีท

[borsalino]

ควรแนบไฟล์ตัวอย่างมาด้วยเพื่อสะดวกในการตอบครับ

สำหรับการ Match ตามค่าใน Sheet ที่ระบุใน B2 สามารถปรับสูตรเป็น

=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"A"&MATCH(TRUE,INDEX(INDIRECT("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"$A$1:$A$100")<>"",0),0)+1,"ไป")

การใช้ Vlookup จำนวนมากสามารถปรับเป็นพวก Index, Match เพื่อเพิ่มความเร็วได้

กรณี Indirect ควรใช้เท่าที่จำเป็น หากใช้มากจะทำให้คำนวณช้า ควรปรับฐานข้อมูลให้อยู่ในชีทเดียวกัน ไม่ควรแยกเป็นหลายชีท

ทำได้แล้วครับอาจารย์ ผมปรับสูตรเป็น

=HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"J"&MATCH(FALSE,INDEX(INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&B3&"'!"&"$A$6:$A$1000")<>"",0),0)+4,"ไป")

ตอนนี้ติดปัญหาคคือ ในกรณีที่ "&B3&" เป็นข้อความที่มีเว้นวรรคหรือวงเล็บ มันจะขึ้น popup คำว่า
การอ้างอิงไม่ถูกต้อง

ผมกำลังจำลองไฟล์แนบให้อาจารย์อยู่ครับ เผื่อจะได้ถามเรื่อง Vlookup เป็น Index, Match

[borsalino]

แนบไฟล์จำลองครับ

ที่ ชีท รายชื่อ คอลัมม์ C ผมปรับสูตรตามไฟล์จำลองนะครับ

=IF(C2="",0,HYPERLINK("[สต๊อก - สินค้า57.xlsm]"&B3&"!"&"B"&MATCH(FALSE,INDEX(INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&B3&"'!"&"$A$1:$A$1000")<>"",0),0)-1,"ไป"))

ที่ C3,C4 สามารถลิงค์ไปยังชีทได้ครับ แต่ C5,C6 ไม่สามารถลิงค์ได้ครับ

ปล.เรื่องสูตรนะครับ ในกรณีนี้ ผมเลี่ยงโดยไม่ใช้ Indirect ได้มั้ยครับ

[snasui]

ลองตามนี้ครับ

เซลล์ C2 ปรับสูตรเป็น

=IF(C1="",0,HYPERLINK("#'"&B2&"'!"&"B"&MATCH(FALSE,INDEX(INDIRECT("'"&B2&"'!"&"$A$1:$A$1000")<>"",0),0)-1,"ไป"))

Enter > Copy ลงด้านล่าง

กรณีทำ Hyperlink ในไฟล์ปัจจุบันไม่จำเป็นต้องอ้างถึงชื่อไฟล์ครับ

[borsalino]

1.ปรับตามนี้ใช้งานได้ไม่มีปัญหาแล้วครับ
ในลักษณะเรื่องความเร็วนี่ เปลี่ยนไปใช้สูตรอื่น ที่ไม่ใช้ Indirect ตามอาจารย์บอกได้มั้ยครับ

2.ผมทำไฟล์จำลอง ในการรวมราคาของแต่ละวันที่มาเรียบร้อยด้วยสูตร ในเซลล์ B13

=SUMPRODUCT(SUMIF(INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&AllSh&"'!A2:A1000"),$A13,INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&AllSh&"'!E2:E1000")))

อาจารย์แนะนำเปลี่ยนสูตรใช้ Match, Index หน่อยครับ

[snasui]

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

สำหรับสูตรแรกหากเป็นการใช้ใน Hyperlink ถึงแม้จะใช้สูตรอืนมาช่วยเช่น Cell, Address ก็ไม่ได้ทำให้เร็ว เพราะเป็นสูตรที่อยู่ในกลุ่มหรือเป็นประเภทเดียวกับ Indirect ซึ่งเป็นสูตรที่เป็น Volatile หรือสูตรที่ถูกกระทบให้เปลียนแปลงได้โดยง่่าย ที่ช้าเพราะเกิดการคำนวณทุกครั้งที่มีการเปลี่ยนแปลงในเซลล์ใด ๆ แม้ไม่เกียวกับเซลล์ที่เขียนสูตรก็ตาม

[borsalino]

=SUMPRODUCT(SUMIF(INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&AllSh&"'!A2:A1000"),$A13,INDIRECT("'[สต๊อก - สินค้า57.xlsm]"&AllSh&"'!E2:E1000")))

ขอคำแนะนำ ในการเปลี่ยน CODE ด้านบนเป็น VBA หน่อยครับ เป็นการดึงข้อมูลที่อยู่ในวันเดียวกันของแต่ละชีทมาบวกกัน
ผมมีการใช้ Indirect เยอะ แค่เปิดไฟล์มันก็รอคำนวนนานมากแล้วล่ะครับ

[snasui]

Code ต้องเขียนมาเองตามกฎข้อ 5 ด้านบน ติดตรงไหนแล้วค่อยถามกันต่อครับ