จะดึงข้อมูลจากชีทอื่นมาใช้งานยังไงคับ

[sakkatos]

ถ้าเราต้องการจะดึงข้อมูลชุดเดียวกันจาก sheet 1 ไปใช้ใน sheet 2 กับ 3 ยังไงคับ โดยที่sheet2 เรียงตามรหัส และsheet3 เรียงตามวันที่

[snasui]

ผมทำตัวอย่างให้ดูการเรียงข้อมูลใหม่ตามไฟล์แนบครับ ซึ่งสูตรจะค่อนข้างซับซ้อน ทั้งนี้เนื่องจากข้อมูลต้นทางมีลักษณะเป็นข้อมูลที่ยังไม่พร้อมสำหรับการทำงานแบบง่าย ๆ ใน Excel ซึ่งผมได้เขียน Comment ไว้แล้วใน Sheet ที่ 1 หากปรับข้อมูลวันที่ให้อยู่ในรูปแบบตัวเลข, ลำดับให้อยู่ในรูปแบบตัวเลข สูตรจะลดความซับซ้อนลงได้ครับ

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

[sakkatos]

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

[snasui]

การอธิบายสูตร Array ทั้งหมดที่ผมเขียนให้นี้ใช้เวลาค่อนข้างมากครับ สงสัยสูตรไหนยกมาถามกันได้เลย สำหรับตัวอย่างการแกะสูตรแบบ Array สามารถดูตัวอย่างแบบภาพเคลื่อนไหวได้ที่นี่ครับ http://snasui.blogspot.com/2009/12/sumproduct.html

กรณีที่้ข้อมูลมีการเพิ่มลด สามารถสร้าง List ให้กับฐานข้อมูลได้ครับโดย

คลิกขวาลงบนฐานข้อมูลที่ Sheet1 > Create List > OK ซึ่งจะทำให้สูตรสามารถยืดหรือหดได้ตามปริมาณข้อมูลที่เปลี่ยนแปลงไป

[sakkatos]

จากไฟล์ Ans_ResortData.xls นะคับ ใน sheet2 ช่อง A2 ที่เรียงตามรหัส
=IF(ROWS($A$2:A2)>$E$1,"",SMALL(INDEX(Sheet1!$A$2:$A$15+0,0),ROWS($A$2:A2)))

ตรง INDEX(Sheet1!$A$2:$A$15+0,0) มันใช้ยังไงเหรอคับแล้วมันใช้เก็บค่าประเภทไหนเหรอคับเพราะผมลองเอาไปใช้กับเลขบัตรประชาชนแล้วมันใช้ไม่ได้

และเครื่องหมาย $ ในสูตรหมายถึงอะไรคับ

[snasui]

จากไฟล์ Ans_ResortData.xls นะคับ ใน sheet2 ช่อง A2 ที่เรียงตามรหัส
=IF(ROWS($A$2:A2)>$E$1,"",SMALL(INDEX(Sheet1!$A$2:$A$15+0,0),ROWS($A$2:A2)))

ตรง INDEX(Sheet1!$A$2:$A$15+0,0) มันใช้ยังไงเหรอคับแล้วมันใช้เก็บค่าประเภทไหนเหรอคับเพราะผมลองเอาไปใช้กับเลขบัตรประชาชนแล้วมันใช้ไม่ได้

และเครื่องหมาย $ ในสูตรหมายถึงอะไรคับ

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

เครื่องหมาย $ หมายถึงการ Lock ค่าให้คงที่ครับ เนื่องจากในแต่ละเซลล์จะมีตำแหน่งคอลัมน์และบรรทัด เช่น

A1 คือ คอลัมน์ A บรรทัดที่ 1
B20 คือ คอลัมน์ B บรรทัดที่ 20

คอลัมน์และบรรทัดสามารถที่จะ Lock ให้คงที่ไม่เปลี่ยนแปลงได้ ยกตัวอย่างเช่นเซลล์
D1:D5 มีค่า 1, 2, 3, 4, 5 ตามลำดับ
E1:E5 มีค่า 6, 7, 8, 9, 10 ตามลำดับ

หากที่ A1 เราเขียนสูตร =$D$1 จากนั้น Copy สูตรนี้ไปใช้ที่ A1:B5 ค่าที่ได้ใน A1:B5 คือค่าในเซลล์ D1 เท่านั้น เพราะเรา Lock ทั้่งคอลัมน์และทั้งบรรทัด

หากที่ A1 เราเขียนสูตร =$D1 จากนั้น Copy สูตรนี้ไปใช้ที่ A1:B5 ค่าที่ได้ใน A1:A5, B1:B5 คือค่าในเซลล์ D1:D5 เท่านั้น เพราะเรา Lock คอลัมน์แต่ไม่ได้ Lock บรรทัด

ลองเปลี่ยนสูตรที่ A1 ใหม่เป็น =D$1 และ =D1 แล้วลอง Copy ไปใช้ที่ A1:B5 แล้วสังเกตดูผลแตกต่างครับ

ส่วนสูตร =IF(ROWS($A$2:A2)>$E$1,"",SMALL(INDEX(Sheet1!$A$2:$A$15+0,0),ROWS($A$2:A2)))

ความหมายคือ หากจำนวนแถวใน $A$2:A2 (ฟังก์ชั่น Rows($A$2:A2) จะเป็นการนับจำนวนแถวระหว่าง $A$2:A2สังเกตการ Lock และไล่ดูบรรทัดล่าง ๆ ของสูตรจะเห็นว่าช่วงข้อมูลมีการเปลี่ยนแปลงไปเรื่อย ๆ) มากกว่าค่าในเซลล์ E1 แล้ว ให้แสดงค่าว่าง ถ้าจำนวนแถวใน $A$2:A2 ไม่มากกว่าค่าในเซลล์ E1 แล้ว ให้แสดงผลจากสูตร SMALL(INDEX(Sheet1!$A$2:$A$15+0,0),ROWS($A$2:A2))

ฟังก์ชั่น Small เป็นการหาค่าที่น้อยที่สุดโดยสามารถระบุลำดับได้ เช่นค่าทีน้อยที่สุดเป็นลำดับที่ 1, 2 เป็นต้น

ซึ่งจากสูตรด้านบนหมายความว่าให้ค่าที่น้อยที่สุดในช่วง INDEX(Sheet1!$A$2:$A$15+0,0) โดยดูำลำดับได้จากสูตร ROWS($A$2:A2)

โดยปกติถ้าข้อมูลถูกคีย์มาในรูปแบบตัวเลขเราไม่ต้องมีฟังก์ชั่น Index สูตรจะเหลือแค่เพียง

=IF(ROWS($A$2:A2)>$E$1,"",SMALL(Sheet1!$A$2:$A$15,ROWS($A$2:A2)))

ที่เลือกใ้ช้ในที่นี้ก็เพราะว่าข้อมูล Sheet1$A$2:$A$15 เป็น Text จึงต้องใ้ช้ฟังก์ชั่น Index เข้ามาช่วยให้แสดงค่า Array แบบไม่ต้องกดแป้น Ctrl+Shift+Enter

กรณีการคีย์ตัวเลขให้เป็น Text เราต้องแปลงจาก Text ให้เป็นตัวเลขเพื่อให้สามารถหา ค่าน้อย ค่ามาก ได้โดยง่าย ซึ่งจะเห็นว่ามีการบวกด้วย 0 เข้าไปด้วย

ต้องค่อย ๆ ทำความเข้าใจครับ

[sakkatos]

ขอบคุณมากคับ ถ้าผมไม่เข้าใจจะมาขอความกรุณาใหม่นะคับ

[sakkatos]

จากไฟล์ Ans_ResortData ถ้าผมจะนำไปใช้กับข้อมูลที่เพิ่มขึ้นในอนาคตคุณบอกว่าให้ทำ create list ตรงฐานข้อมูลแต่ผมหาไม่เจอน่ะคับ

จากไฟล์ ตรวจการโอนเงิน ชีทเรียงตามเลข คอลั่ม C,D,E,F จากสูตรที่คุณให้มา (Ans_ResortData sheet2 คอลั่มB)
=INDEX(Sheet1!B$2:B$15,MATCH(TEXT($A2,"000"),Sheet1!$A$2:$A$15,0))
ตรงคำสั่ง MATCH น่ะ คับถ้าผมใช้สูตรนี้จะมีข้อเสียอะไรมั้ยคับ
=INDEX(data!B$2:B$167,MATCH($B2,data!$A$2:A$167,0))

และจากไฟล์ Ans_ResortData sheet3 รบกวนช่วยอธิบายสูตรนี้ให้หน่อยคับ และวงเล็บ{}นี่มันหมายความว่ายังไงเหรอคับ พอผมกดเข้าไปแล้ววงเล็บมันก็หายไป พอ enter/tab ค่ามันก็เปลี่ยนไป
{=IF(ROWS($A$2:D2)>$E$1,"",INDEX(Sheet1!D$2:D$15,SMALL(IF(Sheet1!$C$2:$C$15=$C2,ROW(Sheet1!$C$2:$C$15)-ROW(Sheet1!$C$2)+1),COUNTIF($C$2:$C2,$C2))))}

[snasui]

จากไฟล์ Ans_ResortData ถ้าผมจะนำไปใช้กับข้อมูลที่เพิ่มขึ้นในอนาคตคุณบอกว่าให้ทำ create list ตรงฐานข้อมูลแต่ผมหาไม่เจอน่ะคับ

คลิกขวาลงในฐานข้อมูล > Create List > ทำเครื่องหมายตรง My list has header

จากไฟล์ ตรวจการโอนเงิน ชีทเรียงตามเลข คอลั่ม C,D,E,F จากสูตรที่คุณให้มา (Ans_ResortData sheet2 คอลั่มB)
=INDEX(Sheet1!B$2:B$15,MATCH(TEXT($A2,"000"),Sheet1!$A$2:$A$15,0))
ตรงคำสั่ง MATCH น่ะ คับถ้าผมใช้สูตรนี้จะมีข้อเสียอะไรมั้ยคับ
=INDEX(data!B$2:B$167,MATCH($B2,data!$A$2:A$167,0))

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

และจากไฟล์ Ans_ResortData sheet3 รบกวนช่วยอธิบายสูตรนี้ให้หน่อยคับ และวงเล็บ{}นี่มันหมายความว่ายังไงเหรอคับ พอผมกดเข้าไปแล้ววงเล็บมันก็หายไป พอ enter/tab ค่ามันก็เปลี่ยนไป
{=IF(ROWS($A$2:D2)>$E$1,"",INDEX(Sheet1!D$2:D$15,SMALL(IF(Sheet1!$C$2:$C$15=$C2,ROW(Sheet1!$C$2:$C$15)-ROW(Sheet1!$C$2)+1),COUNTIF($C$2:$C2,$C2))))}

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

หมายความว่า ถ้า จำนวนแถวใน A2:D2 มีค่ามากกว่าค่าใน E1 ให้แสดงค่า ว่าง
ถ้าไม่มากกว่า ให้นำข้อมูลในช่วง D2:D15 มาแสดง แต่การนำมาแสดงนั้นมีเงื่อนไขว่า
ข้อมูลใน Sheet1 ช่วงเซลล์ C2:C15 ต้องมีค่าเท่ากับ เซลล์ C2 ใน Sheet ปัจจุบัน หากเท่ากัน
ให้แสดงหมายเลขลำดับตั้งแต่เลข 1 ไปจนถึงลำดับที่มากที่สุดของปริมาณข้อมูล แล้วนำลำดับที่น้อยที่สุด
ในลำดับที่เป็นผลมาจาก COUNTIF($C$2:$C2,$C2) มาแสดง ยกตัวอย่างเช่น หาก COUNTIF($C$2:$C2,$C2) ได้ค่า 5
ก็ให้นำค่าใน Sheet1!D$2:D$15 ที่ตรงกับเลขลำดับที่น้อยที่สุดเป็นลำดับที่ 5 จากลำดับทั้งหมดมาแสดง