Page 1 of 1

สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Thu Dec 07, 2017 6:46 pm
by Chanon.witt
จากไฟล์แนบ มี 2 Sheet โดยมี Sheet : Before ,After
ซึ่ง Sheet :Before เป็นข้อมูลที่ Export ออกมาจากระบบ
และต้องจัดรูปแบบให้เหมือน Sheet : After
ผมรบกวนขอความรู้ และวิธีการทำแบบเร็วๆด้วยครับ
Sample1.JPG

Sample2.JPG


ขอบพระคุณ อาจารย์และพี่ๆล่วงหน้าครับ

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Fri Dec 08, 2017 7:02 pm
by parakorn
Sheet "Before"
F1 =IF(AND(N(G3),G3<>""),G3,E1) Enter+Copy ไปทางขวา
C8 =IF(B8="",C7,B8) Enter+Copy ลงด้านล่าง

Sheet "After"

N4 ถึง N8 ใส่ข้อมูล SVO,PLN,OLOP,TKJ,POTC ตามลำดับ
O4 และ O5 ใส่ Order,Forecast

K3 =SUMPRODUCT(1/COUNTIF(Before!$C$8:$C$62,Before!$C$8:$C$62))
K4 =IF(ROWS($K$3:K3)>$K$3,"",INDEX(Before!$C$8:$C$62,SMALL(IF(FREQUENCY(MATCH(Before!$C$8:$C$62,Before!$C$8:$C$62,0),ROW(Before!$C$8:$C$62)-ROW(Before!$C$8)+1),ROW(Before!$C$8:$C$62)-ROW(Before!$C$8)+1),ROWS(Before!$C$8:C8)))) Ctrl+Shift+Enter +Copy ลงด้านล่าง
L4 พิมพ์ 1
L5 =COUNTA($M$4:$M$8)*COUNTA($N$4:$N$8)*COUNTA($O$4:$O$5)+L4 Enter+Copy ลงด้านล่างจนสุด
M3 =SUMPRODUCT(1/COUNTIF(Before!$G$1:$AD$1,Before!$G$1:$AD$1))
M4 =IF(ROWS($M$3:M3)>$M$3,"",INDEX(Before!$G$1:$AD$1,,SMALL(IF(TRANSPOSE(FREQUENCY(MATCH(Before!$G$1:$AD$1,Before!$G$1:$AD$1,0),COLUMN(Before!$G$1:$AD$1)-COLUMN(Before!$G$1)+1)),COLUMN(Before!$G$1:$AD$1)-COLUMN(Before!$G$8)+1),ROWS(Before!$C$8:C8)))) Ctrl+Shift+Enter +Copy ลงด้านล่าง

P4 =MID(Before!$I$2,FIND("/",Before!$I$2)+1,2)+0

ตารางผลลัพท์
B3 =LOOKUP(ROWS($B$3:$B3),$L$4:$L$23,$K$4:$K$23) Enter+Copy ลงด้านล่าง
C3 =IF($B3<>"",INDEX($N$4:$N$8,MOD(ROWS($C$3:$C3)+4,COUNTA($N$4:$N$8))+1)) Enter+Copy ลงด้านล่าง
D3 =IF($B3<>"",INDEX($O$4:$O$5,MOD(INT((ROWS($A$1:A1)-1)/5)+1,2)+1)) Enter+Copy ลงด้านล่าง
E3 =IF(B3<>"",$P$4) Enter+Copy ลงด้านล่าง
F3 =IF($B3<>"",INDEX($M$4:$M$8,MOD(INT((ROWS($A$1:A1)-1)/10)+1,$M$3))) Enter+Copy ลงด้านล่าง
G3 =SUM(IF(Before!$F$4:$AD$4=$C3,IF(Before!$F$1:$AD$1=$F3,IF(Before!$E$8:$E$62=After!$D3,IF(Before!$C$8:$C$62=After!$B3,IF(ISNUMBER(Before!$F$8:$AD$62+0),Before!$F$8:$AD$62+0)))))) Ctrl+Shift+Enter +Copy ลงด้านล่าง

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Sat Dec 09, 2017 5:25 pm
by Chanon.witt
พี่ parakorn ครับ
ผมได้ลองทำตามที่พี่บอกแล้วมี Column B ,Sheet :After ,Customer มาแต่ A1,A2 ครับ
ผมต้องแก้ไขอย่างไรครับ
SAMPLE.jpg

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Sat Dec 09, 2017 5:29 pm
by snasui
:D แนบไฟล์ที่ได้ทดลองทำเองแล้วมาด้วยเพื่อน ๆ จะได้ช่วยดูให้ได้ครับ

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Sat Dec 09, 2017 5:38 pm
by Chanon.witt
ขอโทษด้วยครับ ลืมครับอาจารย์ ><"

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Sat Dec 09, 2017 7:16 pm
by snasui
:D อีกตัวอย่างสูตรครับ

ที่ชีต Before

  1. ที่ C8 คีย์
    =IF(E8="","",LOOKUP(CHAR(255),B$8:B8))
    Enter > Copy ลงด้านล่าง
  2. ที่ D8 คีย์
    =IF(C8="","",COUNTA($F$4:$AD$4)*COUNTIF(C$8:C8,"*?")-COUNTA($F$4:$AD$4)+1)
    Enter > Copy ลงด้านล่าง
  3. ที่ C63 คีย์
    =COUNTA(F4:AD4)*COUNTIF(C8:C62,"*?")

ที่ชีต After

  1. ที่ B3 คีย์
    =IF(ROWS(B$3:B3)>Before!$C$63,"",LOOKUP(ROWS(B$3:B3),Before!$D$8:$D$62,Before!$C$8:$C$62))
    Enter > Copy ลงด้านล่าง
  2. ที่ C3 คีย์
    =IF(B3="","",INDEX(Before!$F$4:$AD$4,MOD((ROWS(C$3:C3)-1),COUNTA(Before!$F$4:$AD$4))+1))
    Enter > Copy ลงด้านล่าง
  3. ที่ D3 คีย์
    =IF(B3="","",INDEX(Before!$E$8:$E$9,MOD((INT((ROWS(D$3:D3)-1)/(COUNTA(Before!$F$4:$J$4)))+1)-1,COUNTA(Before!$E$8:$E$9))+1))
    Enter > Copy ลงด้านล่าง
  4. ที่ E3 คีย์
    =IF(B3="","",MONTH(LEFT(Before!$I$2,FIND("-",Before!$I$2)-1)))
    Enter > Copy ลงด้านล่าง
  5. ที่ F3 คีย์
    =IF(B3="","",LOOKUP(9.99999999999999E+307,OFFSET(Before!$F$3,0,1,1,MOD((ROWS(F$3:F3)-1)/COUNTA(Before!$E$8:$E$9),COUNTA(Before!$F$4:$AD$4))+1)))
    Enter > Copy ลงด้านล่าง
  6. ที่ G3 คีย์
    =IF(B3="","",IFERROR(INDEX(Before!$F$8:$AD$62,MATCH(1,IF(Before!$C$8:$C$62=B3,IF(Before!$E$8:$E$62=D3,1)),0),MATCH(F3,Before!$F$3:$AD$3,0)-1+MATCH(C3,Before!$F$4:$J$4,0)-1),""))
    Ctrl+Shift+Enter > Copy ลงด้านล่าง


!
Note: Ctrl+Shift+Enter
เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง
  1. กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น
    Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array
  2. กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน
    จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array
  3. หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)}
    ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น
    ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง

  4. การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Sat Dec 09, 2017 7:42 pm
by Chanon.witt
ได้แล้วครับ ขอบพระคุณมากครับ
จะลองแก้สูตรและทำความเข้าใจ ถ้าไม่เข้าใจตรงไหนจะมารบกวนอีกรอบนะครับ
ขอบพระคุณพี่ๆและอาจารย์ทุกท่านอีกครั้งครับ

Re: สอบถามวิธีการจัดรูปแบบข้อมูล

Posted: Sat Dec 09, 2017 8:42 pm
by parakorn
Chanon.witt wrote:พี่ parakorn ครับ
ผมได้ลองทำตามที่พี่บอกแล้วมี Column B ,Sheet :After ,Customer มาแต่ A1,A2 ครับ
ผมต้องแก้ไขอย่างไรครับ
SAMPLE.jpg


ถ้าในส่วนของสูตรผม ลืม Copyสูตรจาก L5 ลงมาครับ :lol: :lol: