Snasui Excel Forum

เรียนทุกท่านครับ

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

ขอบคุณครับ

ตัวอย่างสูตรตามด้านล่างครับ

ที่ B3 คีย์

=IFERROR(INDEX(ฐานข้อมูล!$B$4:$B$10,SMALL(IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1),COLUMNS($B3:B3))),"")

Ctrl+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง สำหรับสูตรลักษณะนี้มีจำนวนมากในฟอรัมนี้ ตัวอย่างอื่น ๆ ครับ Lookup มากกว่า 1 ค่า

!

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

ทดสอบดูแล้ว ตรงตามความต้องการ
ขอบคุณมากครับอาจารย์คนควน

=IFERROR(INDEX(ฐานข้อมูล!$B$4:$B$10,SMALL(IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1),COLUMNS($B3:B3))),"")

<< ความหมายสูตรคือ
ใช้ index (Row, Column) ในการหาพิกัดValue โดย SET range = Column B (ชื่อผลไม้) โดย lookup จากชื่อคน (Column A)
<<<ตรงนี้ไม่เข้าใจโลจิก น่ะค่ะ รบกวนช่วยอธิบายเพิ่มได้ไหมคะ
ทำไมต้องมี SMALL , ครอบเงื่อนไขIF คะ
แล้ว ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1) หมายถึงอะไร


-ขอบคุณล่วงหน้าค่ะ

Index ที่ใช้นี้เป็นแบบ Index(array,row_num) แปลว่าจากข้อมูลใน array ให้นำข้อมูลลำดับที่ row_num มาแสดง

row_num ในที่นี้คือ SMALL(IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1),COLUMNS($B3:B3)) นั่นเอง

Small(array,k) หมายถึง จากข้อมูล array ให้นำค่าที่น้อยที่สุดในลำดับที่ k มาแสดง เทียบกับสูตรข้างต้นจะได้ว่า จาก IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1) ให้นำค่าที่น้อยที่สุดในลำดับที่ COLUMNS($B3:B3) มาแสดง

จากสูตร IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1) หมายถึง หาก ฐานข้อมูล!$A$4:$A$10=$A3 เป็นจริงแล้วให้แสดงผลลัพธ์ของ ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1

จากสูตร ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1 หมายถึง ให้แสดงค่าลำดับโดยเริ่มจากเลข 1 เป็นต้นไปจนเท่ากับขนาดของจำนวนบรรทัดในช่วง ฐานข้อมูล!$A$4:$A$10 จะได้ผลลัพธ์เป็นเช่น {1,2,3,4,5,6,7}

ภาพรวมสูตรจะได้เป็นเช่น Small(If({True,True,False,True,False,False,False},{1,2,3,4,5,6,7}),COLUMNS($B3:B3))

ด้านหน้าที่เป็น True จะนำค่าตัวเลขด้านหลังมาแสดง จะได้เป็นเช่น Small({1,2,False,4,False,False,False},COLUMNS($B3:B3))

เนื่องจาก COLUMNS($B3:B3) คือจำนวนคอลัมน์ในช่วง $B3:B3 ผลลัพธ์คือ 1 คอลัมน์ ภาพสูตรจะได้เป็นเช่น Small({1,2,False,4,False,False,False},1) หมายถึงจากช่วง {1,2,False,4,False,False,False} ให้นำค่าที่น้อยสุดในลำดับที่ 1 มาแสดง ผลลัพธ์จะได้เป็น 1

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