Snasui Excel Forum

ใช้สูตรเรียงข้อมูล เรียงค่าน้อยไปหาค่ามาก โดยเอาเลขหน้าเรียงกันถ้าเหมือนกันให้ดูเลขถัดไปเรื่อยๆ จนถึง 4ตัวสุดท้าย ถ้าเหมือนกันให้เรียงตามลำดับ
เช่น เลขหน้าเหมือนกัน 384-AAA-62/xxxx และเลข 4ตัวสุดท้ายคือ 0477,0478 0479 ให้เรียงตามลำดับ

ใช้สูตรแต่กลับได้ค่าซ้ำกันเป็น 0477 ต้องปรับสูตรอย่างไรคะ

C4
=INDEX(B:B,MOD(AGGREGATE(15,6,COUNTIF($B$4:$B$28,"<"&$B$4:$B$28)*1000+ROW($B$4:$B$28),ROWS(C$4:C4)),1000))

แต่ทำไมไม่กด Sort

เพราะว่า สูตรนี้มีสูตรก่อนหน้า นั้นเรียงตามวันที่น้อยไปมาก แล้วค่อยเรียงตามลำดับเลขนี้น่ะค่ะ

ช่วยอธิบายสูตรหน่อยได้ไหมคะ
ทำไมต้อง *1000

=INDEX(B:B,MOD(AGGREGATE(15,6,COUNTIF($B$4:$B$28,"<"&$B$4:$B$28)*1000+ROW($B$4:$B$28),ROWS(C$4:C4)),1000))

COUNTIF($B$4:$B$28,"<"&$B$4:$B$28) สำหรับเรียงค่า *1000 เพื่อให้ + Rowได้ โดยไม่กระทบกับการเรียง
โดยที่ Row ต้องไม่เกิน 1000

เมื่อเรียงเสร็จแล้วก็ Mod( ,1000) เพื่อดึงค่า Row ออกมา เป็น Row number สำหรับ Index

ทำไมปรับสูตรแล้วขึ้น 0 และ #REF คะ

=INDEX($L:$L,MOD(AGGREGATE(15,6,1/(1/COUNTIF($L$224:$L$500,"<="&$L$224:$L$500))*1000+ROW($L$224:$L$500),ROWS(M$224:M224)),1000))

ถามอีกนิดค่ะ ทำไมใช้สูตรแล้วข้อมูลดึงได้ค่าซ้ำหลายรายการคะ

ที่h7=INDEX($B$7:$B$51,AGGREGATE(15,6,(ROW(B$7:B$51)-ROW(B$7)+1)/($D$7:$D$51=J7),COUNTIF(J$7:$J7,J7))

H7
=INDEX(B:B,MOD(AGGREGATE(15,,-$D$7:$D$36*10^6+ROW($D$7:$D$36),ROWS(H$7:H7)),10^3))

ได้คำตอบที่ต้องการ ทั้งสองท่านค่ะ แต่อยากทราบว่ามีวิธีการคิดอย่างไรถึงได้เขียนสูตรได้คำตอบตามนี้คะ ขอบคุณทั้ง 2 ท่านมากค่ะ

รบกวนอีกนิดนึงค่ะ ที่ถูกต้องแบบนี้ค่ะ
เอายอดของลูกค้าที่รวมกันค่ามากที่สุดเป็นอันดับ 1 เรียงกันมา

เช่น นางส่าว พรจันทร์มี 3 จำนวนให้เอายอดมารวมกันแล้วแสดงชื่อเป็นรายการเดียว

ถ้ายอดเท่ากันให้เรียงลำดับตามชื่อ ก-ฮ

ตัวอย่างสูตรตามด้านล่างครับ

1. แทรกคอลัมน์ช่วย 2 คอลัมน์คือ G:H เมื่อแทรกแล้วตัวอย่างคำตอบที่เขียนไว้เดิมจะไปเริ่มที่คอลัมน์ I
2. ที่ F7 คีย์
   =IF(COUNTIF(C$7:C7,C7)=COUNTIF(C$7:C$37,C7),SUMPRODUCT(--(G7<G$7:G$37))+SUMPRODUCT(--(C7<C$7:C$37))/100-COUNTIF(G$7:G$37,""),"")
   Enter > Copy ลงด้านล่าง
3. ที่ G7 คีย์
   =IF(COUNTIF(C$7:C7,C7)=COUNTIF(C$7:C$37,C7),SUMIF(C$7:C$37,C7,D$7:D$37),"")
   Enter > Copy ลงด้านล่าง
4. ที่ J7 คีย์
   =INDEX(C$7:C$37,MATCH(SMALL($F$7:$F$37,I7),$F$7:$F$37,0))
   Enter > Copy ลงด้านล่าง
5. ที่ K7 คีย์
   =SUMIF($C$7:$C$37,$J7,D$7:D$37)
   Enter > Copy ไป L7 > Copy ลงด้านล่าง

H7
=INDEX(C:C,MOD(AGGREGATE(14,6,ROUND(SUMIFS($D$7:$D$51,$C$7:$C$51,$C$7:$C$51)*10^6,-6)-COUNTIF($C$7:$C$51,"<"&$C$7:$C$51)*10^3+ROW($C$7:$C$51)/ISERROR(MATCH($C$7:$C$51,H$6:H6,)),1),1000))

I7
=SUMIFS($D$7:$D$51,$C$7:$C$51,H7)

J7
=SUMIFS($E$7:$E$51,$C$7:$C$51,H7)

ทำไมไม่ใช้ Pivot คลิกไม่กี่ทีก็เสร็จแล้ว

ได้แล้วค่ะ ขอบคุณอาจารย์และคุณ Bory ค่ะ

ตอบคำถามคุณ Bory ค่ะ
บางครั้งดึงข้อมูลที่เป็น Pivot Table แล้วข้อมูลหายไปเมื่อมีการเปลี่ยนข้อมูล หัวเรื่อง ขยายcolumn บ้าง หรือเพิ่มตาราง จึงจำเป็นต้องใช้สูตรแทนค่ะ

มีปัญหานิดนึงน่ะค่ะ

ถ้าเลือกวันที่ที่ cell D3 แล้วให้ดึงข้อมูลตามหัวข้อที่ column B โดยดึงข้อมูลที่ sheet Details แล้วขึ้น VALUE และไม่ได้คำตอบตามที่ต้องการค่ะ
ลองปรับแล้วไม่ค่อยได้ค่ะ

March201711 wrote: Fri Jan 10, 2020 6:54 am ตอบคำถามคุณ Bory ค่ะ
บางครั้งดึงข้อมูลที่เป็น Pivot Table แล้วข้อมูลหายไปเมื่อมีการเปลี่ยนข้อมูล หัวเรื่อง ขยายcolumn บ้าง หรือเพิ่มตาราง จึงจำเป็นต้องใช้สูตรแทนค่ะ

ทั้ง Pivot หรือ สูตร ถ้าข้อมูลหาย pivot และสูตร ก็หายเหมือนกัน ต้องเขียนสูตรขนาดนี้ คลิก Pivot ไม่กี่ทีก็ได้แล้ว

March201711 wrote: Fri Jan 10, 2020 11:32 am มีปัญหานิดนึงน่ะค่ะ

ถ้าเลือกวันที่ที่ cell D3 แล้วให้ดึงข้อมูลตามหัวข้อที่ column B โดยดึงข้อมูลที่ sheet Details แล้วขึ้น VALUE และไม่ได้คำตอบตามที่ต้องการค่ะ
ลองปรับแล้วไม่ค่อยได้ค่ะ

D10
=SUMIFS(INDEX(Details!$D$5:$J$999,MATCH($D$3,Details!$A$5:$A$999,),),Details!$D$3:$J$3,"<>Total")/1000

E10
=SUMIFS(INDEX(Details!$D$5:$J$999,MATCH("MTD-"&TEXT($D$3,"[$-en]mmm"),Details!$A$5:$A$999,),),Details!$D$3:$J$3,"<>Total")/1000

F10
=SUMIFS(INDEX(Details!$D$5:$J$999,MATCH($K$8,Details!$A$5:$A$999,),),Details!$D$3:$J$3,"<>Total")/1000