Snasui Excel Forum

ถ้าจะดึงชื่อจาก Column C ว่า ชื่ออยู่ใน No. ไหน ต้องใช้สูตรอะไรคะ

เช่น ตรง cell C5 มีชื่อ Mr.AAAA (ในสูตร =IF(Mr.AAAA!$N$4>0,Mr.AAAA!$N$4,0) ) ให้หาจาก column B11:B14 ว่าอยู่ No.ไหน แล้วมาใส่ที่ Cell A5 คือ 930092-9 High light สีเหลือง ค่ะ

สำหรับ Excel 2013 ขึ้นไป
A5
=LOOKUP(,-SEARCH(MID(FORMULATEXT(C5),5,FIND("!",FORMULATEXT(C5))-5),$B$11:$B$14),$A$11:$A$14)

ถามต่ออีกนิดนึงค่ะ
ดึงข้อมูลจาก sheet ชื่อคน โดยเอายอดรวมขา BU และ SE และ วันที่ ตาม high light สีเหลืองคะ

เช่น ดึงข้อมูล Sheet Mr.Chai โดยดึงขา BU จะได้ 620,458.59 (hight สีเหลือง) และ SE จะได้ 57,286.12 (high light สีเขียว) ของวันที่ 26/02/2019 และ 553,260.11 ของวันที่ 28/02/2019

D5:D7
=SUMIFS(Mr.Chai!$I:$I,Mr.Chai!$C:$C,$C5,Mr.Chai!$E:$E,MID(D$2,7,2))

หรือ D5:D7 copy ไป D10
=SUMIFS(INDIRECT(LEFT(LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5),FIND(" ",LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5))-1)&"!I:I"),INDIRECT(LEFT(LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5),FIND(" ",LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5))-1)&"!c:c"),$C5,INDIRECT(LEFT(LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5),FIND(" ",LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5))-1)&"!e:e"),MID(D$2,7,2))

ช่วยอธิบายสูตรได้ไหมคะ ใช้อย่างไรจะได้ประยุกต์ไปใช้งานอื่นค่ะ

Sumifs น่าจะเข้าใจนะ
=SUMIFS(Mr.Chai!$I:$I,Mr.Chai!$C:$C,$C5,Mr.Chai!$E:$E,MID(D$2,7,2))
MID(D$2,7,2) จะได้ Se กับ Bu เป็นเงื่อนไข

range
Mr.Chai!$I:$I = Indirect("Mr.Chai" &"!I:I")

"Mr.Chai" เอามาจาก ฺB5=> Mr.Chai Akaras =LEFT(B5,FIND(" ",B5)-1) ="Mr.Chai"

ต้องการให้ลากลงแล้วยังเป็น Mr.Chai อยู่ และเมื่อ copy ไป C10 ให้เปลี่ยนเป็น "Mr.Path"

C5 =LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5) = "Mr.Chai Akaras" เมื่อลากลงไป C6
C6 =LOOKUP(2,1/($B$5:$B6<>" "),$B$5:$B6) = Lookup(2,1/({B5<>" ";B6<>" "}),$B$5:$B6)
= Lookup(2,1/({True;False}),$B$5:$B6) = Lookup(2,{1/1;1/0}),$B$5:$B6)
= Lookup(2,{1;#DIV/0!}),$B$5:$B6) = B5 = "Mr.Chai Akaras"
Lookup จะหาค่าสุดท้ายที่น้อยกว่า 2 จะได้1 จากตำแหน่ง B5 และคืนค่า B5 จึงได้ "Mr.Chai Akaras"

รวมกันก็จะได้ช่วง Sum range สำหรับ Sumifs, Criteria range ก็แค่เปลี่ยน I:I เป็น C:C สำหรับวันที่และ E:E สำหรับ Bu, Se
=INDIRECT(LEFT(LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5),FIND(" ",LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5))-1)&"!I:I")
=INDIRECT("Mr.Chai!I:I")

ขอบคุณมากค่ะ จะพยายามทำความเข้าใจ คุณ Bo_ry เก่งอะไรเบอร์นี้ คุณทำงานด้าน IT หรอคะ ถึงใช้สูตรเก่งจังค่ะ

งานใช้แทบไม่ได้ใช้ Excel ชอบเล่นเกม แต่เปลี่ยนมาเล่นแก้โจทย์ Excel แทน

ค่ะ คุณ Bo_ry ทำไมใช้สูตรแล้ว รู้สึกว่าเครื่องคำนวณช้าๆหน่วงๆน่ะค่ะ เป็นเพราะสูตรนี้หรือเปล่า เวลา run marco จะคำนวณนานมากค่ะ

ถ้าลบสูตรออกแล้วเร็วขึ้นก็แสดงว่าใช่

แล้วต้องปรับเปลี่ยนสูตรอย่างไรดีคะ ที่ไม่ทำให้ marco run แล้วช้าๆและหน่วงๆค่ะ

March201711 wrote: Fri Mar 15, 2019 10:46 pm แล้วต้องปรับเปลี่ยนสูตรอย่างไรดีคะ ที่ไม่ทำให้ marco run แล้วช้าๆและหน่วงๆค่ะ

สูตร กลุ่มที่เป็นลักษณะ Array เช่น Lookup,MMULT จะช้ามากเมื่อข้อมูลปริมาณเยอะหลาย
ส่วนที่ผมเคยใช้คือ อาจจำกัดช่วง เช่น ตัวนี้ =LOOKUP(2,1/($B$5:$B5<>" "),$B$5:$B5) อาจแทรก
บรรทัดก่อนหน้าใช้สูตรกลุ่มนี้ที่มีการดึงข้อมูลแรกมาถึงข้อมูลสุดท้าย $B$5:$B5 จนสุดท้ายอาจไปถึง
$B$5:$B100000 โดยที่กะว่าข้อมูลทิ้งช่วงห่างกันไม่เกิน 100 บรรทัด ก็แทรกด้านบนไป 100 บรรทัด
และปรับเป็น $B1:$B101 ข้อมูลจะทิ้งช่วงห่างกัน 100 บรรทัด ตามข้อมูลที่คิดว่าไม่เกินจากนี้
หรืออีกวิธี คือให้ VBA กับสูตรนี้ให้รันเป็นค่าไปแทนสูตรครับ

=SUMIFS(Mr.Chai!$I$1:$I$100,Mr.Chai!$C$1:$C$100,$C5,Mr.Chai!$E$1:$E$100,MID(D$2,7,2))

และเปลี่ยนชื่อ sheet ของแต่ละคน

ค่ะ คงเปลี่ยนเป็นสูตรที่คุณ Bo_ry ให้มาล่าสุดค่ะ ขอบคุณค้าาา