Snasui Excel Forum

เรียนทุกท่าน รบกวนขอสอบถามปัญหาดังนี้ครับ

จากตารางที่แนบมานั้น ใน worksheet "RAW DATA" เป็นข้อมูลลูกหนี้ที่ทั้งหมดโดย Column "E" เป็นข้อมูลที่บอกว่าสถานะการชำระหนี้ของลูกหนี้แต่ละราย

คำถามคือ ผมต้องการที่จะสรุปข้อมูลว่ามีลูกหนี้รายใดบ้างที่มีการชำระหนี้ล่าช้าเกินกว่า 60 วัน ใน worksheet "summary" โดยให้ดึงข้อมูลลูกหนี้, กลุ่ม, ยอดหนี้ ของลูกหนี้ที่มีชำระหนี้ล่าช้าเกินกว่า 60 วัน มาจากตารางใน worksheet "RAW DATA" ครับ ซึ่งตามตารางจะมีลูกหนี้ที่ชำระหนี้ล่าช้าเกินกว่า 60 วัน ทั้งสิ้น 3 รายคือ F, K, L

ขอบคุณล่วงหน้าครับ

เซลล์ C7 คีย์สูตรตามด้านล่างครับ

=IFERROR(INDEX('RAW DATA'!C$7:C$26,AGGREGATE(15,6,1/('RAW DATA'!$E$7:$E$26=4)*(ROW('RAW DATA'!$E$7:$E$26)-ROW('RAW DATA'!$E$7)+1),ROWS(C$7:C7))),"")

Enter > Copy ลงด้านล่าง จากนั้น Copy ไปใช้ยังคอลัมน์ที่เกี่ยวข้องโดยปรับช่วงข้อมูลที่ผมระบายสีให้ตรงกับข้อมูลที่ต้องการนำมาแสดงครับ

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

ขออนุณาตถามต่อครับ สูตรนี้สามารถใช้กับ excel 2007 ได้รึป่าวครับ เนื่องจากตอนที่ลองครั้งแรกกับ excel 2010 ใช้ได้ แต่พอมาใช้กับ excel 2007 มันใช้ไม่ได้ครับ

Excel 2007 ไม่มีฟังก์ชัน Aggregate ครับ หากต้องใช้ในเวอร์ชัน 2007 ลองปรับสูตรที่ C7 เป็น

=IFERROR(INDEX('RAW DATA'!C$7:C$26,SMALL(IF('RAW DATA'!$E$7:$E$26=4,ROW('RAW DATA'!$E$7:$E$26)-ROW('RAW DATA'!$E$7)+1),ROWS(C$7:C7))),"")

จากนั้นกด Ctrl+Shift ค้างไว้ แล้ว Enter --> คัดลอกลงและปรับช่วงตรงสีแดงเมื่อคัดลอกไปคอลัมน์อื่นครับ

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

ได้ครับ จะลองดูนะครับ
จากสูตร
=IFERROR(INDEX('RAW DATA'!C$7:C$26,SMALL(IF('RAW DATA'!$E$7:$E$26=4,ROW('RAW DATA'!$E$7:$E$26)-ROW('RAW DATA'!$E$7)+1),ROWS(C$7:C7))),"")

ดูจากข้างนอกเข้าไปแล้วกันครับ

(1) =Iferror(...(2)...,"")
สูตรนี้เอาไว้ดักค่า error ครับ หากผลลัพธ์จากข้อ (2) เป็นค่า error สูตรก็จะให้แสดงค่าว่างแทน

(2) INDEX('RAW DATA'!C$7:C$26,[color=#FF0000]n[/color])
Index จะคืนค่าเป็นข้อมูลใน 'RAW DATA'!C$7:C$26 ตำแหน่งใดตำแหน่งหนึ่งขึ้นกับค่า n ครับ เช่น ค่า n=2 ก็จะเอาค่าในเซลล์ C8 ในชีท Raw Data มาแสดง โดยค่า n ได้มาจากข้อ (3)

(3) SMALL(...(4)...,[color=#FF00FF]x[/color])
สูตร Small ตามชื่อเลยครับ คือจะเลือกเอาค่าที่น้อยที่สุดเป็นลำดับที่ x มาจาก (4) โดยค่า x นี่ได้มาจากสูตร ROWS(C$7:C7) สูตรนี้เป็นการนับจำนวนแถวระหว่างเซลล์ C7:C7 ก็คือ 1 (หมายความว่าให้เอาค่าน้อยที่สุดเป็นอันดับ 1 ในข้อ (4) มาแสดง หากคัดลอกลงไปเรื่อยๆ สูตร Rows ก็จะให้ค่า 2, 3, 4, 5, ... ไปเรื่อยๆ ครับ

(4) IF('RAW DATA'!$E$7:$E$26=4,ROW('RAW DATA'!$E$7:$E$26)-ROW('RAW DATA'!$E$7)+1)
สูตร If ใช้เพื่อหาว่าบรรทัดไหนใน 'RAW DATA'!$E$7:$E$26 มีค่าเป็น 4 บ้าง หาก =4 ก็ให้แทนค่าด้วยผลลัพธ์ที่ได้จาก

ROW('RAW DATA'!$E$7:$E$26)-ROW('RAW DATA'!$E$7)+1

สูตรนี้เป็นการสร้างลำดับให้ข้อมูลในช่วง E7:E26 ครับ คือ
={7;8;9;10;11;...;26}-{7}+1
={0;1;2;3;4;...;19}+1
={1;2;3;4;5;...;20}

สมมติว่าข้อมูลในเซลล์ E7, E8 และ E26 เท่านั้น ที่เป็น 4 ผลที่ได้จากสูตร If คือ
{1;2;False;False;False;...;False;20}


สรุปคร่าวๆ คือ สูตร If จะเลือกว่าข้อมูลลำดับไหนในชีท RawData ที่เราต้องการบ้าง เสร็จแล้วสูตร Small จะเลือกตำแหน่งที่ใกล้ที่สุดที่พบออกมา (หากเอาตามที่สมมติข้างบน บรรทัดแรกสูตร Small จะให้ค่า 1, บรรทัดที่สองจะให้ค่า 2, บรรทัดที่สามจะให้ค่า 20) แล้ว Index ก็จะคืนค่าเป็นข้อมูลในตำแหน่งไหนในช่วงอ้างอิงก็ขึ้นกับค่าที่ได้จาก Small ครับ
สูตรข้างต้นนี้เป็นสูตรอาร์เรย์ จึงต้องกดรับสูตรด้วย Ctrl+Shift+Enter ครับ

ส่วนสูตร Aggregate ที่อาจารย์ให้มาเป็นสูตรใหม่ การทำงานหลากหลายมากและอาจไม่ต้องกดรับสูตรแบบอาร์เรย์ ลองอ่านเพิ่มเติมได้ที่ลิงค์ด้านล่างครับ (สูตรอื่นๆ ก็ค้นหาได้จาก Microsoft Office support เช่นกันครับ)
OfficeSupport-Aggregate

ขอบคุณมากครับสำหรับคำตอบและคำอธิบาย ผมได้ลองหาตาม internet โดยเฉพาะ Microsoft Office Support แล้วครับ แต่อ่านไม่รู้เรื่อง T_T ของคุณทั้ง 2 ท่านมากครับ