รบกวนสอบถามสูตรการดึงข้อมูลข้ามชีท

[natthaporn]

ดิฉันรบกวนสอบถามท่านผู้รู้เกี่ยวกับสูตรการดึงข้อมูลข้ามชีท ดังนี้คะ ดิฉันต้องการดึงข้อมูลจากชึท data ไปที่ชีท reportA1 ดังนี้คะ
ผลลัพธ์ของ C ในชีท reportA1
1. ถ้าข้อมูลที่ C3 ของชีท reportA1 ตรงกับข้อมูลที่่ C2:AJ2 ของชีท data
2. ถ้าข้อมูลที่ B4:B599 ของชีท reportA1 ตรงกับข้อมูลที่่ B4:B2999 ของชีท data
3. ถ้าข้อมูลที่ C5 ของชีท reportA1 ตรงกับข้อมูลที่่ C3:AJ3 ของชีท data
ให้ดึงข้อมูลที่ C4:AJ2999 ชีท data มาไว้ที่ C6:C599 ของชีท reportA1

ผลลัพธ์ของ D ในชีท reportA1
1. ถ้าข้อมูลที่ C3 ของชีท reportA1 ตรงกับข้อมูลที่่ C2:AJ2 ของชีท data
2. ถ้าข้อมูลที่ B4:B599 ของชีท reportA1 ตรงกับข้อมูลที่่ B4:B2999 ของชีท data
3. ถ้าข้อมูลที่ D5 ของชีท reportA1 ตรงกับข้อมูลที่่ C3:AJ3 ของชีท data
ให้ดึงข้อมูลที่ C4:AJ2999 ชีท data มาไว้ที่ D6:D599 ของชีท reportA1

ปกติแล้วดิฉันใช้สูตรตามด้านล้าง
{=SUM(($B6=data!$B$4:$B$2999)*(D$5=data!$C$3:$AJ$3)*($C$3=data!$C$2:$AJ$2)*data!$C$4:$AJ$2999)}
แต่ process การทำงานค่อนข้างช้า เพราะมีข้อมูลเป็นจำนวนมาก เพราะมี sheet report = 17 ชีทคะ
รบกวนท่านผู้รู้ช่วยแนะนำสูตรด้วยคะ ขอบคุณคะ

[snasui]

ลองปรับสูตรที่ C6 เป็นตามด้านล่างครับ

Code: Select all

=INDEX(data!$C$4:$AJ$11,MATCH($B6,data!$B$4:$B$11,0),MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0))

Ctrl+Shift+Enter > Copy ไปยังเซลล์ต่าง ๆ ที่เกี่ยวข้อง หรือ

Code: Select all

=INDEX(data!$C$4:$AJ$11,MATCH($B6,data!$B$4:$B$11,0),MATCH(1,INDEX((data!$C$2:$AJ$2=$C$3)*(data!$C$3:$AJ$3=C$5),0),0))

Enter > Copy ไปยังเซลล์ต่าง ๆ ที่เกี่ยวข้อง

[natthaporn]

ได้ผลลัพธ์ตามที่ต้องการแล้วคะ และดิฉันได้ลองไปใช้กับ file จริงแล้ว process การทำงานเร็วขึ้นกว่าเดิมมากเลยคะ ดิฉันมีเรื่องรบกวนสอบถามเพิ่มเติมเกี่ยวกับความหมายของสูตรที่อาจารย์แนะนำมา เพราะดิฉันจะนำไปปรับใช้กับ file อื่น ๆ ดังนี้คะ
1. โดยปกติแล้วดิฉันเห็นว่าสูตร =INDEX(xxx,0,MATCH แต่สูตรนี้เป็น =INDEX(xxx,MATCH มี 0 และไม่มีความหมายแตกต่างกันอย่างไรคะ
2. MATCH(1, มีความหมายว่าอย่างไรคะ

ขอบคุณคะ

[snasui]

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

[natthaporn]

ก่อนอื่นต้องขอโทษด้วยคะ จริงที่อาจารย์พูด เพราะการถามแบบเดิมนั้นดิฉันจะเข้าอยู่คนเดียว คนอื่นจะมองภาพไม่ออก
1. สูตร
=INDEX(data!$C$4:$AJ$11,MATCH($B6,data!$B$4:$B$11,0),MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0))
และ
=INDEX(data!$C$4:$AJ$11,0,MATCH($B6,data!$B$4:$B$11,0),MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0))
มี 0 กับ ไม่มี 0 มีความหมายต่างกันอย่างไรคะ

2. =INDEX(data!$C$4:$AJ$11,MATCH($B6,data!$B$4:$B$11,0),MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0))

MATCH(1, มีความหมายว่าอย่างไรคะ

ขอบคุณมากคะ

[snasui]

สำหรับ Index นั้นมีส่วนประกอบ 2 แบบตามด้านล่างครับ

1. รูปแบบอาร์เรย์
   INDEX(array, row_num, [column_num])
2. รูปแบบการอางอิง
   INDEX(reference, row_num, [column_num], [area_num])

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

จากสูตร

=INDEX(data!$C$4:$AJ$11,MATCH($B6,data!$B$4:$B$11,0),MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0))

หมายถึง จากช่วงข้อมูล data!$C$4:$AJ$11 ให้นำบรรทัดที่เป็นผลลัพธ์ของสูตร MATCH($B6,data!$B$4:$B$11,0) และคอลัมน์ที่เป็นบรรทัดของสูตร MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0) มาแสดง

หาก MATCH($B6,data!$B$4:$B$11,0) ได้ผลลัพธ์เป็น 2 และ
MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0) ได้ผลลัพธ์เป็น 3 สูตรจะได้เป็น

=INDEX(data!$C$4:$AJ$11,2,3) นั่นหมายความว่า จากช่วงข้อมูล data!$C$4:$AJ$11 ให้นำบรรทัดที่ 2 และคอลัมน์ที่ 3 มาแสดง หรือนั่นคือค่าในเซลล์ E5 นั่นเอง

สำหรับ MATCH(1,IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)),0) หมายถึง ให้หาว่า 1 อยู่ในลำดับที่เท่าไรของ IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1))

ซึ่ง IF(data!$C$2:$AJ$2=$C$3,IF(data!$C$3:$AJ$3=C$5,1)) หมายถึง ถ้า data!$C$2:$AJ$2=$C$3 เป็นจริง และ IF(data!$C$3:$AJ$3=C$5 เป็นจริงแล้ว ให้แสดงเลข 1 ถ้าไม่เป็นจริงให้แสดงค่า False สูตรก็จะได้เป็น เช่น

=Match(1,{false,false,1,1,1,...,false},0)

จากตัวอย่าง ผลลัพธ์จะได้ 3 นันคือ เลข 1 อยู่ในลำดับที่ 3 ของ {false,false,1,1,1,...,false} ซึ่งจะใช้เป็นส่วนประกอบของฟังก์ชั่น Index ต่อไป

การจะเข้าใจสูตรพวกนี้ได้จำเป็นอย่างยิ่งที่จะต้องทำความเข้าใจสูตร Index และ Match แบบธรรมดาเสียก่อนเพื่อจะเป็นพื้นฐานให้เข้าใจสูตรแบบ Array ได้ในลำดับต่อไป โดยศึกษาได้ที่นี่ครับ

Index Help on this function
Match Help on this function

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

[snasui]

ลืมตอบเรื่อง มี 0 กับไม่มี 0

หากสังเกต

รูปแบบอาร์เรย์
INDEX(array, row_num, [column_num])

จะเห็นว่างตรงคอลัมน์จะมีก้ามปูครอบ ก้ามปูนี้หมายความว่าเป็น Option หรือเป็น Argument ที่จะมีหรือไม่มีก็ได้ ปกติถ้าไม่มี 0 รูปแบบจะเป็นเช่น

=Index(A1:A10,8)

หรือ

=Index(B4:Z4,7)

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

[natthaporn]

ขอขอบคุณอาจารย์สำหรับคำอธิบายและคำแนะนำคะ ข้อมูลที่ดิฉันได้มาเพื่อทำรายงานนั้น ถ้าเป็นข้อมูลที่ดิฉันขอให้ฝ่าย IT download ข้อมูล มาจากฐานข้อมูลของบริษัท ข้อมูลพวกนี้ก็จะมีลักษณะเป็น database แต่ถ้าเป็นข่อมูลที่ดิฉันได้มาจากหน่วยงานอื่นซึ่งจะเป็นลักษณะของรายงาน หลาย ๆ ชุด และดิฉ้นต้องนำมาคัดกรองเพิ่อดึงข้อมูลบางส่วนมาใช้ อันหลังนีไม่มีทางเลือกจริง ๆคะ จำเป็นต้องใช้ และรูปแบบของหลายงานจากแต่ละฝ่ายก็ไม่เหมือนกัน ดังนั้นดิฉันจึงต้องหาสูตรยาก ๆ มาช่วยอย่างที่ อาจารย์กล่าวมา
และช่วงนี้ดิฉันกำลังปรับปรุงรูปแบบของรายงาน และสูตรต่าง ๆ ที่ใช้เพื่อดึงข้อมูลอยู่คะ ก็เลยอาจจะต้องรบกวนสอบถามบ่อยครั้งคะ

[snasui]

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

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

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

ลักษณะการนำรายงานในเรื่องเดียวกันจากหลายแหล่งสามารถนำมาทำเป็นฐานข้อมูลอย่างเร็ว ๆ คือ

1. นำรายงานมาต่อกันในชีทเดียวกัน
2. ให้รหัสกำกับว่าเป็นของแหล่งใด
3. ใส่หัวคอลัมน์ให้ครบทุกคอลัมน์ ซึ่งหัวคอลัมน์คือบรรทัดแรกของข้อมูลเท่านั้น
4. เพิ่มคอลัมน์ของงวด เช่น เดือน ปี

แค่นี้ก็ได้ฐานข้อมูลอย่างเร็ว ๆ มาใช้ สามารถดูเป็นงวด ๆ ที่ต้องการได้ ซึ่งไม่ต้องพึ่งพา VBA แต่อย่างใด ลองนำไปปรับใช้กับงานของตนเองดูครับ

ข้อคิดเพิ่มเติม
ผมสามารถเขียนสูตรเพื่อหาข้อมูลจากรายงานมาทำรายงานได้ไม่ยาก แต่ข้อเสียคือ

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

[natthaporn]

อาจารย์คะ ไม่รู้จะกล่าวคำไหนนอกจากขอขอบคุณด้วยใจจริงคะ สำหรับคำแนะนำที่ดี ๆ เช่นนี้ ทุกวันนี้ใน computer ของดิฉันเก็บ file แทบเป็นพัน ๆ file มีแต่รายงานทั้งนั้น เวลาฝ่าย IT back-up ข้อมูลครั้งหนึ่งเฉพาะฝ่ายของดิฉันใช้เวลาครั้งวันเลยคะ
ทุกวันนี้ในการขอข้อมูลจากแต่ละฝ่าย ดิฉันก็พยายามสร้างเป็น form ที่เป็น standard ไว้ แต่ได้เฉพาะบางส่วน เพราะการที่จะเปลี่ยนคนอื่นได้ทั้งหมดเปลี่ยนยากคะอาจารย์ แต่ก็ดีเหมือนกันนะคะอาจารย์ เพราะทำให้ดิฉันได้พัฒนาตัวเองขึ้นเยอะ ทำให้ดิฉันต้องศึกษาหาความรู้อยู่ตลอดเวลาเพือรับมือกับ report หน้าตาแปลก ๆ ที่มีเข้ามาในแต่ละเดือน