Snasui Excel Forum

ดึงข้อมูลอย่างไรให้ได้ หน้า sheet result นี้ โดยsum ข้อมูลที่เริ่มเดือนที่เลือก ที่ cell H1 list dropdown

เช่น เดือน Mar-19 ต้องเริ่มรวมข้อมูลที่ cell F69-F74 ที่ sheet data ของ ชื่อแต่ละคน ยอด amt และ ยอด fee ค่ะ

E103 ลากขวาลากลง
=IF($D103="","",SUMIFS(data!K:K,data!$D:$D,$D103,data!$F:$F,">="&$H$1,data!$F:$F,"<"&EDATE($H$1,1)))

หรือถ้าเช็คชื่อด้วย
=IF($D103="","",SUMIFS(data!K:K,data!$D:$D,$D103,data!$F:$F,">="&$H$1,data!$F:$F,"<"&EDATE($H$1,1),data!$E:$E,"*"&$C103&"*"))

ถ้าเป็นตารางที่ว่างเปล่า แล้วให้แสดงผลลัพธ์แต่ละ column คือ No Name Dept ต้องปรับสูตรอย่างไรคะ

ลองดูที่
B102=INDEX(data!$B$58:$E$74,AGGREGATE(15,6,(ROW(data!$B$58:$E$74)-ROW(data!$B$58)+1)/(MONTH(data!$F$58:$F$74)=MONTH($H$1)),ROWS(B$102:B102)),MATCH(B$101,data!$B$55:$E$55,0)) คัดลอกลงครับ

ไม่เห็นได้เหมือนคำตอบเลยค่ะ กลับเพิ่มรายการขึ้นอีกค่ะ

ปรับเป็นที่ B102=IFERROR(INDEX(LEFT(data!$E$58:$E$77,4),SMALL(IF(FREQUENCY(IF(MONTH(data!$F$58:$F$77)=MONTH($H$1),MATCH(data!$E$58:$E$77,data!$E$58:$E$77,0)),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROWS(B$102:B102))),"") กด Ctrl+Shift+Enter

C102=IFERROR(INDEX(MID(data!$E$58:$E$77,6,9),SMALL(IF(FREQUENCY(IF(MONTH(data!$F$58:$F$77)=MONTH($H$1),MATCH(data!$E$58:$E$77,data!$E$58:$E$77,0)),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROWS(C$102:C102))),"") กด Ctrl+Shift+Enter

E102=IFERROR(INDEX(data!$D$58:$D$77,SMALL(IF(FREQUENCY(IF(MONTH(data!$F$58:$F$77)=MONTH($H$1),MATCH(data!$E$58:$E$77,data!$E$58:$E$77,0)),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROWS(D$102:D102))),"") กด Ctrl+Shift+Enter

ทำไมเลือกเดือน Jan-19 แล้วข้อมูลไม่ดึงมาคะ

ปรับเป็นที่ B102=IFERROR(INDEX(LEFT(data!$E$58:$E$77,4),SMALL(IF(FREQUENCY(IF(MONTH(data!$F$58:$F$77)=MONTH($H$1),MATCH(data!$E$58:$E$77&"",data!$E$58:$E$77&"",0)),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROWS(B$102:B102))),"") กด Ctrl+Shift+Enter

C102=IFERROR(INDEX(MID(data!$E$58:$E$77,6,9),SMALL(IF(FREQUENCY(IF(MONTH(data!$F$58:$F$77)=MONTH($H$1),MATCH(data!$E$58:$E$77&"",data!$E$58:$E$77&"",0)),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROWS(C$102:C102))),"") กด Ctrl+Shift+Enter

E102=IFERROR(INDEX(data!$D$58:$D$77&"",SMALL(IF(FREQUENCY(IF(MONTH(data!$F$58:$F$77)=MONTH($H$1),MATCH(data!$E$58:$E$77&"",data!$E$58:$E$77&"",0)),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROW(data!$E$58:$E$77)-ROW(data!$E$58)+1),ROWS(D$102:D102))),"") กด Ctrl+Shift+Enter

B102
=IFERROR(AGGREGATE(15,6,(LEFT(data!$E$58:$E$77,FIND("-",data!$E$58:$E$77)-1))/(TEXT(data!$F$58:$F$77,"myy")=TEXT($H$1,"myy"))/(--LEFT(data!$E$58:$E$77,FIND("-",data!$E$58:$E$77)-1)>MAX(B$101:B101)),1),"")

C102
=IF(B102="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($B102&"-*",data!$E$58:$E$77,1,),"-"," ")," ",REPT(" ",40)),40,80)))

D102
=IF(B102="","",INDEX(data!$D$58:$D$77,MATCH($B102&"-*",data!$E$58:$E$77,)))

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

March201711 wrote: Sat Mar 23, 2019 11:26 am ใช้ได้แล้วค่ะ แต่งงทั้ง 2 ท่านที่เขียนสูตรให้ค่ะ ช่วยอธิบายให้เข้าใจได้ไหมคะ เป็นความรู้สำหรับคนไม่เก่งค่ะ

ลองเช็ค​สูตรดูก่อนนะครับ ถ้าคัต คอลัมน์​แรกมาเป็นตัวหาคอลัมน์ต่อไป แล้วติดอะไรไหมครับเช่น ถ้า
2223-EEEE YYYY F789 เดือนนั้นมีส่วนที่ไม่ใช่ 4 ตัวแรก ไม่เหมือนกันเป็น 2223-EEEE XXXX FXxx
ข้อมูล​จะถูกหรือไม่

ฺB102
=IFERROR(AGGREGATE(15,6,(LEFT(data!$E$58:$E$77,FIND("-",data!$E$58:$E$77)-1))/(TEXT(data!$F$58:$F$77,"myy")=TEXT($H$1,"myy"))/(--LEFT(data!$E$58:$E$77,FIND("-",data!$E$58:$E$77)-1)>MAX(B$101:B101)),1),"")

a. LEFT(data!$E$58:$E$77,FIND("-",data!$E$58:$E$77)-1) ดึงตัวอักษรหน้า "-" มาหารกับ
b.(TEXT(data!$F$58:$F$77,"myy")=TEXT($H$1,"myy")) ที่เช็คว่าเดือนและปีตรงกัน
สูตรจะใช้ได้ถ้าตัวอักษรหน้า "-" เป็นตัวเลข เมื่อหารเดือนและปีตรงกันจะได้ค่าเดิม ถ้าไม่ตรงจะได้ #Div/0 เป็นการกรองค่าที่ไม่ตรงออก
และหารด้วย
c. (--LEFT(data!$E$58:$E$77,FIND("-",data!$E$58:$E$77)-1)>MAX(B$101:B101)) เพื่อเช็คว่าค่าที่ได้ต้องมากกว่าค่าด้านบนของเซลตัวเอง เป็นการตัดค่าซ้ำ

AGGREGATE(15,6, a/b/c ,1) จะได้เลขน้อยสุดของเดือนปีนั้นๆ เมื่อลากลงจะได้ค่าน้อยสุดที่มากกว่าค่าด้านบน

C102
=IF(B102="","",TRIM(MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($B102&"-*",data!$E$58:$E$77,1,),"-"," ")," ",REPT(" ",40)),40,80)))

d. VLOOKUP($B102&"-*",data!$E$58:$E$77,1,) จากหาชื่อเต็มจาก B102&"-*" เติม "-*" เพื่อหาตัวหลังNoที่เป็นชื่ออะไรก็ได้
ดังนั้น ชื่อเดียวกันต้องเป็น No เดียวกัน

e. TRIM(MID(SUBSTITUTE(SUBSTITUTE(d),"-"," ")," ",REPT(" ",40)),40,80))
ตัดเอาคำที่ 2,3 จาก "No-Name LName code" จะได้ "Name LName"

ค่ะ ขอบคุณ คุณ Bo_ry มากเลยค่ะ และคุณ puriwutpokin ด้วยค่ะ

สอบถามเพิ่มเติมอีกนิดค่ะ
ดึงยอดของเดือนถัดไปโดยดูยอด ณ สิ้นเดือนที่เลือก drop down แล้วไปดึงที่ column I

เช่น เลือก เดือน Mar-19 ที่ drop down cell T54 ให้ข้อมูลที่ดึงที่ column B โดยดูว่า เดือนที่เลือกสิ้นสุดเดือนไหน ให้เลือกยอดของเดือนถัดไป โดยดูที่ Column I สิ้นเดือน 31-Mar-19 ข้อมูลที่ดึงมาเป็น No.68-62/0083 และ 72-62/0091 ส่วน 71-62/0090 จะไม่ดึงมาเพราะยังคงเป็นเดือน Mar-19 ตามที่ Hight สีส้ม

ที่ B81=IFERROR(INDEX($B$58:$K$77,SMALL(IF(MONTH($I$58:$I$77)=MONTH($T$54),ROW($B$58:$B$77)-ROW($B$58)+1),ROWS(B$81:B81)),CHOOSE(COLUMNS($B81:B81),1,5,10)),"") กด Ctrl+Shift+Enter คัดลอกไปทางขวาและลงล่างครับ

หรือไม่เอา Array
ที่ B81=IFERROR(INDEX($B$58:$K$77,AGGREGATE(15,6,(ROW($B$58:$B$77)-ROW($B$57))/(MONTH($I$58:$I$77)=MONTH($T$54)),ROWS(C$81:C81)),CHOOSE(COLUMNS($B81:B81),1,5,10)),"") คัดลอกไปทางขวาและลงล่างครับ

ไม่ได้ยอดตามคำตอบที่ถูกต้อง ต้องดึงยอดที่เป็นต้นเดือน Apr-19 ที่ Column I ค่ะ

ลองเปลี่ยน​เดือนดูยังครับ

ก้อเปลี่ยนเดือน mar-19แล้ว ยังไม่ได้ตามข้อมูลตามที่ต้องการเลยค่ะ

March201711 wrote: Mon Mar 25, 2019 7:37 pm ก้อเปลี่ยนเดือน mar-19แล้ว ยังไม่ได้ตามข้อมูลตามที่ต้องการเลยค่ะ

ดูตามไฟล์แนบครับ เลือกเดือน apr-19 ตามเงื่อนไข ถ้า mar-19 ก็เป็นอีกเงื่อนไขนะครับ

แสดงว่าต้องเลือก drop down เดือน Apr-19 ข้อมูลถึงวิ่งมาใช่ไหมคะ
ถ้าเลือกเดือน Mar-19 จะเป็นอีกข้อมูลนึง