Snasui Excel Forum

จากไฟล์ที่แนบมาครับ วันก่อนผมได้วิธีส่งข้อมูลข้ามชีท แต่เป็นแบบแนวคอลัมน์ ซึ่งผมอยากได้แบบแนวแถวครับ ก็คือไฟล์ที่แนบมานี้ ผมต้องการจะพิมพ์ข้อมูลลงในชีทหน้าหลัก แล้วให้มันแยกข้อมูลไปเก็บในชีทลูกอม ตามล็อกของแต่ละตัวครับ เนื่องจากว่า สินค้ามีเยอะมากครับ ถ้าจะทำแยกชีทละ 1 ตัวมันไม่ไหวครับ ก็เลยอยากจะแบ่งหมวดหมู่ แล้วค่อยแยกย่อยภายในอีกที ดังที่แนบไปครับ ไม่ทราบว่าพอจะมีสูตร ไรช่วยได้ไหมครับ

ชีทลูกอมมีเหตุผลใดที่ต้องแยกเป็นหลาย ๆ ฟอร์มครับ ทำฟอร์มเดียวแล้วเลือกรหัสสินค้าแทนจะได้หรือไม่ครับ ช่วยระบายสีให้เห็นเด่นชัดว่าต้องการนำค่าใดมาแสดงในชีทลูกอมและอยู่ในเซลล์ใดบ้างครับ

อันนี้ลงสีแล้วครับ คือ อยากจะให้พอเราคีย์รหัสในชีทหน้าหลัก แล้วมันจะไปดึงข้อมูลจากชีทลูกอมมาให้ถูกต้องครับตรงตามสีนั้น ๆ ครับ หรือจะใช้วิธีเลือกรหัสก็ได้ครับ แต่ต้องการให้มันเรียงข้อมูลเป็นแนวนอนตามแบบที่ส่งไป พอจะมีสูตรไหมครับ ผมลองปรับตรง COLUMN เป็น ROW มันก็ไม่ได้ครับ ที่ต้องเรียงเป็นแนวนอน เพราะว่าอันนี้เป็นตัวอย่างแต่ของจริงจะเยอะมาก ซึ่งต้องใช้พื้นที่ด้านล่างในชีทลูกอมเพื่อเก็บข้อมูลลูกค้าที่ซื้อไว้ต่อกันเรื่อยๆ ในวันถัดมาครับ

ลองตามนี้ครับ

ที่ชีทหน้าหลักเซลล์ E4 คีย์

=OFFSET(ลูกอม!$A$2,0,MATCH($C4,ลูกอม!$2:$2,0)+COLUMNS($E4:E4)-1,1)

Enter > Copy ไปทางขวาและลงด้านล่าง

snasui wrote: ลองตามนี้ครับ

ที่ชีทหน้าหลักเซลล์ E4 คีย์

=OFFSET(ลูกอม!$A$2,0,MATCH($C4,ลูกอม!$2:$2,0)+COLUMNS($E4:E4)-1,1)

Enter > Copy ไปทางขวาและลงด้านล่าง

ขอบคุณมากครับ ผมกำลัง งม VLOOKUP อยู่พอดีครับ

ขอรบกวนอีกนิดครับ คือว่า ผมลองทำเชื่อมข้อมูล แบบวันก่อนที่เคยถามไป ซึ่งมันใช้ได้เฉพาะกับบรรทัดแรก (ในชีทลูกอม) แถวต่อมันมีไรผิดพลาดซักอย่าง ซึ่งอ่านที่ในโปรแกรมแนะนำมาแล้วก็ไม่เข้าใจครับ ช่วยดูให้หน่อยครับ

เนื่องจากสูตรไม่ถูกต้องและกดแป้นให้รับสูตรไม่ถูกต้องครับ

จากสูตร

=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108="1",ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

ให้ปรับเป็น

=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108=1,ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

เมื่อคีย์สูตรเสร็จแทนที่จะกดแป้น Enter เพียงแป้นเดียวเพื่อให้รับสูตร ให้กดแป้น Ctrl+Shift ค้างไว้ก่อน แล้วตามด้วย Enter

กรณี Copy สูตรจากข้อความในโพสต์นี้ไปวาง ให้กดแป้น F2 เพื่อทำการ Edit สูตร จากนั้นกดแป้น Ctrl+Shift แล้วตามด้วย Enter

ขอบคุณมากเลยครับ

มีปัญหาใหม่ครับ
=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108=amt10,ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))
ตรงที่ใส่สีแดงครับ มันคือ รหัสสินค้าผมครับ พอจะกด ctrl+shift+enter มันจะไม่แสดงผล มันกลายเป็นว่า ไปอ้างอิงเป็น logical test ในสูตร IF ครับ แบบนี้ผมต้องแก้ที่เปลี่ยนรหัสผม หรือว่า มีวิธีอื่นช่วยได้ไหมครับไม่ให้มันอ้างอิงครับ

แนบไฟล์ตัวอย่างมาดูครับ

เป็นปัญหาเดิมครับ นั่นคือ กรณีเป็น Text จะต้องครอบด้วยเครื่องหมาย "" ครับ ดังนั้นตามสูตรที่ยกมา ควรจะเป็น

=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108="amt10",ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

อ่านเรื่อง Number และ Text ที่นี่จะทำให้เข้าใจเรื่องนี้มากขึ้นครับ http://snasui.blogspot.com/2010/10/excel.html และกรณีเป็น Text หากนำมาใช้ในสูตรจะต้องครอบด้วยเครื่องหมาย "" เสมอครับ

bank9597 wrote: แนบไฟล์ตัวอย่างมาดูครับ

กรณีไม่ใช่คำตอบช่วยแนบไฟล์มาตามที่คุณแบงค์แจ้งไปครับ

ตรง #NUM คือมันต้องกด ctrl+shift+enter ก่อน ค่อยลากไปทางขวา แล้วลงใช่ไหมครับ แต่มันกด ctrl+shift+enter แล้วจะกลายเป็น 0 ทันที ไม่สามารถดึงค่ามาแสดงได้เลยครับ

ลองตามนี้ครับ
ที่เซลล์ A1 คีย์ =COUNTIF(หน้าหลัก!$C$4:$C$108,B2)

ที่เซลล์ A4 คีย์ =IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108=$B$2,ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

กด Ctrl+Shift+Enter คัดลอกไปทางขวามือ แล้วลงล่างพร้อมกันครับ

ลองศึกษานเรื่อง Number และ Text ตามที่อาจารย์แจ้งดูครับ
http://snasui.blogspot.com/2010/10/excel.html

อ่อ พอเข้าใจละครับ แต่ผมขอถามเพิ่มอีกนิดครับ จากที่ อ.พูดคราวก่อน ผมยัง งง นิด ๆ ครับ

snasui wrote: เนื่องจากสูตรไม่ถูกต้องและกดแป้นให้รับสูตรไม่ถูกต้องครับ

จากสูตร

=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108="1",ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

ให้ปรับเป็น

=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108=1,ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

ผมก็เลยสงสัยว่า ตอนแรกใส่ " มันไม่รับสูตร พอเอาออกมันรับสูตร แต่มาวันนี้ ต้องใส่ถึงจะรับสูตร ตรงนี้มันเกิดจากอะไรเหรอครับ

หรือว่า ถ้าเป็น text ต้องใส่ " " ทุกกรณี แต่ถ้าตัวเลข ไม่ต้องใส่ "" แม้แต่กรณีเดียวแบบนี้เหรอครับ

มีหลายองค์ประกอบด้วยกันครับ
ประการแรกคือการกดแป้นรับสูตร ให้ถูกต้อง คือกด ctrl+shift+enter แทนการกด Enter

การใส่เงื่อนไขในสูตรก็เป็นส่วนสำคัญมาก บางครั้งเราต้องใส่ "" บางครั้งไม่ต้องใส่ คำตอบก็อย่างที่อาจารย์ได้บอกไว้ หากเป็นตัวเลข เช่น เลข 1,2,3....ไม่จำเป็นต้องมี "" (ผมเรียกฟันหนู) แต่หากข้อมูลเป็น text ก็จำเป็นต้องมี "" ครับ

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

ยกตัวอย่าง เราต้องการหารายการที่มีชื่อว่า Name_01 เราใช้ชื่อไปอ้างอิงเลย เราก็จะใช้สูตร
=COUNTIF(หน้าหลัก!$C$4:$C$108,"Name_01")
แต่ถ้าเราอ้างอิงจากเซลล์ เราก็แค่คีย์ชื่อ Name_01 ลงในเซลล์ใดเซลลืหนึ่ง เช่น A1 สูตรที่ได้คือ
=COUNTIF(หน้าหลัก!$C$4:$C$108,A1)

ซึ่งคำตอบก็ออกมาเท่ากัน แต่ความสะดวกจะต่างกัน เช่นเดียวกับสูตรที่ใช้แปลงค่าออกมา หากเราเลือกวิธีแรก สูตรที่ได้คือ
=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108="Name_01",ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))

หากเลือกวิธีที่สอง สูตรที่ได้คือ
=IF(ROWS(A$4:A4)>$A$1,"",INDEX(หน้าหลัก!A$4:A$108,SMALL(IF(หน้าหลัก!$C$4:$C$108=$A$1,ROW(หน้าหลัก!$A$4:$A$108)-ROW(หน้าหลัก!$A$4)+1),ROWS(A$4:A4))))
ผลที่ออกมาก็ได้เหมือนกัน ต่างกันที่ความสะดวกในการแก้ไขเงื่อนไขเท่านั้น

การทำแบบวิธีที่ 2 นั้น ผู้ใช้ต้องเคยใช้สูตรอาร์เรย์มาค่อนข้างเยอะแล้ว จึงจะเข้าใจและเเก้ไขข้อผิดพลาดที่เกิดขึ้นได้
สำหรับผู้ที่ยังเริ่มศึกษา ควรใช้วิธีการที่ 1 เพื่อที่ได้มุ่งไปที่เงื่อนไขในสูตรเป็นสำคัญ แต่อย่างไรก็ดีผมไม่คอนเฟิร์มได้ทั้งหมดว่าอะไรดีกว่าอะไร แล้วแต่การใช้ของแต่ละคนครับ

ปล. สูตรที่ยกมาเป็นแค่ตัวอย่างให้เห็นภาพครับ กรณียังไม่เข้าใจหรือไม่มั่นใจในคำตอบ อาจารย์จะเข้ามาเสริมให้อีกครับ

อืม ล้ำลึกมาก ขอบคุณครับ ผมเคยเรียนคอมพิวเตอร์พื้นฐาน 1 เทอมเองครับ อันนี้มันสุดยอดจริงๆ มีแต่สูตรที่ไม่เคยเห็นเลยครับ

nidnykung wrote:หรือว่า ถ้าเป็น text ต้องใส่ " " ทุกกรณี แต่ถ้าตัวเลข ไม่ต้องใส่ "" แม้แต่กรณีเดียวแบบนี้เหรอครับ

กรณีเป็น Text ใส่ทุกกรณีถูกต้องแล้วครับ Text ที่พูดถึงอยู่นี้หมายความรวมถึงตัวเลขล้วน ๆ แต่ถูกจัด Format ให้เป็น Text ดังนั้น จะเห็นได้ว่า Text และ Number ไม่ได้พิจารณาด้วยสิ่งที่มองเห็นด้วยตาแต่ต้องตรวจสอบไปถึงการจัด Format

bank9597 wrote:การทำแบบวิธีที่ 2 นั้น ผู้ใช้ต้องเคยใช้สูตรอาร์เรย์มาค่อนข้างเยอะแล้ว จึงจะเข้าใจและเเก้ไขข้อผิดพลาดที่เกิดขึ้นได้
สำหรับผู้ที่ยังเริ่มศึกษา ควรใช้วิธีการที่ 1 เพื่อที่ได้มุ่งไปที่เงื่อนไขในสูตรเป็นสำคัญ

การเขียนสูตรให้อ้างอิงเซลล์เงื่อนไขเป็นการลดภาระการแก้ไขสูตรครับ ดังนั้น ไม่ว่ามือใหม่หรือมือเก่า หากเป็นไปได้ควรสร้างเซลล์เงื่อนไขแยกไว้ต่างหาก เพื่อให้ง่ายต่อการแก้ไขเพียงไม่กี่เซลล์แทนที่จะตามไปแก้ไขทุก ๆ สูตร ยกเว้นเขียนแค่สูตรเดียวเท่านั้นก็สามารถเขียนเงื่อนไขเข้าไปในสูตรได้เลยเพื่อลดการสิ้นเปลืองเซลล์

การเขียนสูตรให้อ้างอิงเซลล์เงื่อนไขเป็นการลดภาระการแก้ไขสูตรครับ ดังนั้น ไม่ว่ามือใหม่หรือมือเก่า หากเป็นไปได้ควรสร้างเซลล์เงื่อนไขแยกไว้ต่างหาก เพื่อให้ง่ายต่อการแก้ไขเพียงไม่กี่เซลล์แทนที่จะตามไปแก้ไขทุก ๆ สูตร ยกเว้นเขียนแค่สูตรเดียวเท่านั้นก็สามารถเขียนเงื่อนไขเข้าไปในสูตรได้เลยเพื่อลดการสิ้นเปลืองเซลล์

ขอบคุณอาจารย์มากครับ