Snasui Excel Forum

เรียนอาจารย์และเพื่อนสมาชิก

อยากเรียงข้อมูลในคอลัมน์เดียวกัน มี 3 กรณี ดังนี้

กรณีที่ 1
ข้อมูลคอลัมน์ B ที่ B2:B10 เรียงแถวเว้นแถวกันอยู่
ให้ไปเรียงข้อมูลใหม่ในคอลัมน์ P ที่ P3:P7 ติดกันไป

กรณีที่ 2
ข้อมูลคอลัมน์ D , F , H แต่ละคอลัมน์เรียงแถวเว้นแถวและเว้นคอลัมน์ระหว่างคอลัมน์ด้วย
ให้ไปเรียงข้อมูลใหม่ในคอลัมน์ R ที่ R3:R20 ติดกันไป

กรณีที่ 3
ข้อมูลคอลัมน์ J , K , L แต่ละคอลัมน์เรียงแถวติดกันและเรียงคอลัมน์ติดกัน
ให้ไปเรียงข้อมูลใหม่ในคอลัมน์ T ที่ T3:T20 ติดกันไป

ทั้ง 3 กรณีเป็นได้ทั้งตัวเลขและตัวอักษร แต่ละคอลัมน์อาจมีข้อมูลแถวเท่ากันหรือไม่เท่ากันมีมากขึ้นหรือน้อยลงก็ได้ เมื่อมีข้อมูลเพิ่มเติมเข้าไป ก็ให้คอลัมน์ P , R , T เรียงเพิ่มเข้าในคอลัมน์เดียวกันตามแต่ละกรณี


ขอบคุณครับ

R3
=IFERROR(INDEX(A:L,MOD(AGGREGATE(15,6,COLUMN(D$2:H$20)*10^4+ROW(D$2:H$20)/(D$2:H$20<>""),ROWS(R$3:R3)),10^4),AGGREGATE(15,6,COLUMN(D$2:H$20)/(D$2:H$20<>""),ROWS(R$3:R3))),"")

ขอแยกเป็นกรณีครับ ทั้ง 3 กรณีไม่ได้สัมพันธ์กันหรือเกี่ยวข้องกันครับ ที่ยกตัวอย่างอยู่ใน sheet เดียวกัน เพื่อสะดวกในการตอบคำถามครับ

กรณีที่1 คอลัมน์ B ไปเรียงข้อมูลใน คอลัมน์ P

กรณีที่2 คอลัมน์ D:H ไปเรียงข้อมูลใน คอลัมน์ R

กรณีที่3 คอลัมน์ J:L ไปเรียงข้อมูลใน คอลัมน์ T

ขอบคุณครับ

ก็ใช้สูตรเดียวกัน แค่เปลี่ยนช่วง สีน้ำเงินให้เป็น B2:B20 , D2:H20 , J2:L20

=IFERROR(INDEX(A:L,MOD(AGGREGATE(15,6,COLUMN(D$2:H$20)*10^4+ROW(D$2:H$20)/(D$2:H$20<>""),ROWS(R$3:R3)),10^4),AGGREGATE(15,6,COLUMN(D$2:H$20)/(D$2:H$20<>""),ROWS(R$3:R3))),"")

Bo_ry wrote: Wed May 13, 2020 3:09 pm ก็ใช้สูตรเดียวกัน แค่เปลี่ยนช่วง สีน้ำเงินให้เป็น B2:B20 , D2:H20 , J2:L20

=IFERROR(INDEX(A:L,MOD(AGGREGATE(15,6,COLUMN(D$2:H$20)*10^4+ROW(D$2:H$20)/(D$2:H$20<>""),ROWS(R$3:R3)),10^4),AGGREGATE(15,6,COLUMN(D$2:H$20)/(D$2:H$20<>""),ROWS(R$3:R3))),"")

ปรับช่วงที่ B2:B20 , D2:H20 , J2:L20 กำหนดได้ผลลัพธ์ที่ต้องการจริง แต่
สิ่งที่ต้องการ ในแต่ละกรณีไม่ได้เกี่ยวข้องกันแยกอิสระกันเป็นกรณีๆไปครับ เพราะสูตรเป็น INDEX(A:L... เกี่ยวเนื่องกันอยู่ครับ
ซึ่งที่อยากได้คือประมาณนี้ =INDEX(B2:B20,MOD(AGGREGATE(15,6,COLUMN($B$2:$B$20)... เป็นต้น

remark กรณีทั้ง 3 เสมือนหนึ่งว่า เป็นแต่ละกรณี เป็นคนละsheets ดังนี้ว่า sheets1 กรณีที่1 sheets2 กรณีที่2 และsheets3 กรณีที่3 ที่เห็นอยู่ตามตัวอย่างรวมกันนั้น ให้สังเกตง่ายๆใน sheet1 เท่านั้นครับ
ผมทำ sheets ตัวอย่างแยกเป็นกรณีแต่ละ sheets ไว้ให้แล้ว
ขอบคุณครับ

ไม่เข้าใจ ทำตัวอย่างมาใหม่แยกอิสระกันไปเลย

Totem wrote: Wed May 13, 2020 4:04 pm ซึ่งที่อยากได้คือประมาณนี้ =INDEX(B2:B20,MOD(AGGREGATE(15,6,COLUMN($B$2:$B$20)... เป็นต้น

ตัวอย่างการ Scope พื้นที่ใน Index ครับ

ที่ Sheet1 เซลล์ E3

=IFERROR(INDEX($B$2:$B$20,MOD(AGGREGATE(15,6,(100000*COLUMN($B$2:$B$20)+(ROW($B$2:$B$20)-ROW($B$2)+1))/($B$2:$B$20<>""),ROWS(E$3:E3)),100000),AGGREGATE(15,6,(COLUMN($B$2:$B$20)-COLUMN($B$2)+1)/($B$2:$B$20<>""),ROWS(E$3:E3))),"")

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

กรณีนำไปใช้กับช่วงอื่นเปลี่ยนเฉพาะช่วงที่ระบายสีครับ

snasui wrote: Wed May 13, 2020 11:22 pm

Totem wrote: Wed May 13, 2020 4:04 pm ซึ่งที่อยากได้คือประมาณนี้ =INDEX(B2:B20,MOD(AGGREGATE(15,6,COLUMN($B$2:$B$20)... เป็นต้น

ตัวอย่างการ Scope พื้นที่ใน Index ครับ

ที่ Sheet1 เซลล์ E3

=IFERROR(INDEX($B$2:$B$20,MOD(AGGREGATE(15,6,(100000*COLUMN($B$2:$B$20)+(ROW($B$2:$B$20)-ROW($B$2)+1))/($B$2:$B$20<>""),ROWS(E$3:E3)),100000),AGGREGATE(15,6,(COLUMN($B$2:$B$20)-COLUMN($B$2)+1)/($B$2:$B$20<>""),ROWS(E$3:E3))),"")

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

กรณีนำไปใช้กับช่วงอื่นเปลี่ยนเฉพาะช่วงที่ระบายสีครับ

ขอบคุณครับ ได้ตามต้องการครับ

จากสูตรถ้าจะนำไปใช้กับ office 2007 จะปรับสูตรอย่างไรครับ ขอบคุณครับ

Totem wrote: Mon Feb 22, 2021 3:27 pm ใช้กับ office 2007 จะปรับสูตรอย่างไรครับ

ตัวอย่างการปรับสูตรครับ

E3

=IFERROR(INDEX($B$2:$B$20,MOD(SMALL(IF($B$2:$B$20<>"",100000*COLUMN($B$2:$B$20)+(ROW($B$2:$B$20)-ROW($B$2)+1)),ROWS(E$3:E3)),100000),SMALL(IF($B$2:$B$20<>"",COLUMN($B$2:$B$20)-COLUMN($B$2)+1),ROWS(E$3:E3))),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

snasui wrote: Tue Feb 23, 2021 5:58 am

Totem wrote: Mon Feb 22, 2021 3:27 pm ใช้กับ office 2007 จะปรับสูตรอย่างไรครับ

ตัวอย่างการปรับสูตรครับ

E3

=IFERROR(INDEX($B$2:$B$20,MOD(SMALL(IF($B$2:$B$20<>"",100000*COLUMN($B$2:$B$20)+(ROW($B$2:$B$20)-ROW($B$2)+1)),ROWS(E$3:E3)),100000),SMALL(IF($B$2:$B$20<>"",COLUMN($B$2:$B$20)-COLUMN($B$2)+1),ROWS(E$3:E3))),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ขอบคุณครับ ได้ตามต้องการครับ