Excel Forum

ต้องการดึงข้อมูลข้าม sheet แบบมีเงื่อนไขหลายตัวครับ

**ต้องการดึงข้อมูลข้าม sheet โดยดึงตัวเลขในช่องคอลัมภ์สีเหลืองของ sheet A ไปใส่ในคอลัมภ์สีเขียวของ sheet output
โดยมีเงื่อนไขข้อมูลที่จะดึงมาต้องอ้างอิงกับคอลัมภ์สีฟ้าทั้งหมด เช่น รหัส A1-078 ก็จะต้องอ้างอิงชื่อโครงการ ประเภทบ้าน ความสูง ขนาดที่ดิน ขนาดบ้าน รูปแบบ หน้ากว้าง วันเปิดตัว ระดับราคาให้ตรงกัน จึงจะค่อยดึงข้อมูลในคอลัมภ์ L และ M ไปใส่ในคอลัมภ์สีเขียวของ sheet output

ตามไฟล์แนบครับ
ขอบคุณครับ

ตัวอย่างสูตรครับ

L4 คีย์

=LOOKUP(2,1/(($B4='sheet A'!$B$4:$B$20)*($C4='sheet A'!$C$4:$C$20)*($D4='sheet A'!$D$4:$D$20)*($E4='sheet A'!$E$4:$E$20)*($F4='sheet A'!$F$4:$F$20)*($G4='sheet A'!$G$4:$G$20)*($H4='sheet A'!$H$4:$H$20)*($I4='sheet A'!$I$4:$I$20)*($J4='sheet A'!$J$4:$J$20)*($K4='sheet A'!$K$4:$K$20)),'sheet A'!L$4:L$20)

Enter > Copy ไป M4 แล้ว Copy ลงด้านล่าง

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

พบ error กรณีแถวสลับกันครับ แบบนี้จะแก้ไขยังไงครับ??

แนบไฟล์ที่พบว่า Error มาด้วย จะได้ช่วยดูให้ได้ครับ

snasui wrote: ↑Thu May 12, 2022 6:17 pm แนบไฟล์ที่พบว่า Error มาด้วย จะได้ช่วยดูให้ได้ครับ

ไฟล์ตัวอย่างนี้ครับ อาจารย์

การสลับบรรทัดไม่ใช่ปัญหา แต่ค่าที่ไม่เหมือนกันคือปัญหาครับ

ยกตัวอย่างคำว่า TreeYard

ชีต output เซลล์ G12 เขียนว่า TreeYard (ไม่วรรค)
ชีต input เซลล์ G11 เขียนว่า Tree Yard (มีวรรค)

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

snasui wrote: ↑Fri May 13, 2022 1:49 pm การสลับบรรทัดไม่ใช่ปัญหา แต่ค่าที่ไม่เหมือนกันคือปัญหาครับ

ยกตัวอย่างคำว่า TreeYard

ชีต output เซลล์ G12 เขียนว่า TreeYard (ไม่วรรค)
ชีต input เซลล์ G11 เขียนว่า Tree Yard (มีวรรค)

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

อ่อ เข้าใจแล้วครับอาจารย์ ขอบคุณครับ

L2
=INDEX(input!K$4:K$19,LOOKUP(9^9,MATCH({9,10},MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0),)))

Bo_ry wrote: ↑Fri May 13, 2022 11:09 pm L2
=INDEX(input!K$4:K$19,LOOKUP(9^9,MATCH({9,10},MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0),)))

ขอบคุณครับ

Bo_ry wrote: ↑Fri May 13, 2022 11:09 pm L2
=INDEX(input!K$4:K$19,LOOKUP(9^9,MATCH({9,10},MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0),)))

ผมขอสอบถามเพิ่มเติมครับ สูตรตรง >> ,ROW($Z$1:$Z$10)^0)
ใช้อ้างอิง หรืออ้างอิงมาจากตรงไหนครับ???

ROW($Z$1:$Z$10)^0 ={1;1;1;1;1;1;1;1;1;1}

เอาไว้ คูณ Matrix

https://youtu.be/RyorWHIKPC0

Bo_ry wrote: ↑Fri May 13, 2022 11:09 pm L2
=INDEX(input!K$4:K$19,LOOKUP(9^9,MATCH({9,10},MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0),)))

----

รบกวนขอทราบวิธีอ่านสูตรด้านบนครับ เช่น (9^9,MATCH({9,10} มีที่มาอ้างอิงจากไหนครับ??

9 ยกกำลัง 9 มาจากไหนครับ??

maanoii wrote: ↑Mon May 20, 2024 3:23 pm =INDEX(input!K$4:K$19,LOOKUP(9^9,MATCH({9,10},MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0),)))

ตัวอย่างคำอธิบายครับ

1. input!K$4:K$19: นี่คือช่วงข้อมูลในแผ่นงานชื่อ input คอลัมน์ K ตั้งแต่แถวที่ 4 ถึงแถวที่ 19 ที่เราจะดึงค่าจากสูตร INDEX มาแสดงผล
2. LOOKUP(9^9, ... ): ฟังก์ชัน LOOKUP ใช้ในการค้นหาค่าใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ 9^9 (ซึ่งเป็นจำนวนที่ใหญ่มาก จึงเป็นวิธีที่จะดึงค่าที่มากที่สุดในอาร์เรย์ที่ให้มา)
3. MATCH({9,10}, ... ): ฟังก์ชัน MATCH ใช้เพื่อค้นหาตำแหน่งของค่าในอาร์เรย์ที่ตรงกับเกณฑ์ที่กำหนด โดยที่ {9,10} คือค่าที่เราต้องการหา
4. MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0):
   • ($A4:$J4=input!$A$4:$J$19): ตรวจสอบว่าค่าในช่วง $A4:$J4 เท่ากับค่าในช่วง input!$A$4:$J$19 หรือไม่ (จะได้ผลลัพธ์เป็น TRUE หรือ FALSE)
   • --(...): แปลง TRUE/FALSE เป็น 1/0
   • ROW($Z$1:$Z$10)^0: สร้างอาร์เรย์ที่มีค่าเป็น 1 ทั้งหมด (เนื่องจาก ROW($Z$1:$Z$10) ให้ค่าตั้งแต่ 1 ถึง 10 และการยกกำลัง 0 ของตัวเลขใด ๆ จะได้ 1 เสมอ)
   • MMULT(... , ...): ฟังก์ชันการคูณเมทริกซ์ ใช้ในการคูณอาร์เรย์ 2 อัน ซึ่งผลลัพธ์จะเป็นอาร์เรย์ใหม่ที่ได้จากการคูณเมทริกซ์
5. รวมกัน:
   • MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0) จะได้ผลลัพธ์เป็นจำนวนที่เกิดจากการตรวจสอบค่าที่ตรงกันและรวมกัน
   • MATCH({9,10}, ... ) จะคืนค่าตำแหน่งของค่า 9 หรือ 10 ในอาร์เรย์ที่ได้จาก MMULT
   • LOOKUP(9^9, ...) จะค้นหาค่าที่ใกล้เคียงที่สุดในอาร์เรย์นี้
   • สุดท้าย INDEX(input!K$4:K$19, ...) จะใช้ค่าที่ได้จาก LOOKUP เป็นตัวระบุตำแหน่งในช่วง input!K$4:K$19 เพื่อดึงค่ามาแสดง

สรุป สูตรนี้ใช้ในการดึงค่าจากช่วง input!K$4:K$19 โดยค้นหาค่าในช่วง $A4:$J4 ที่ตรงกับช่วง input!$A$4:$J$19 และใช้การคำนวณต่างๆ เพื่อหาค่าที่ตรงกันมากที่สุดและนำค่าที่ตรงนั้นไปดึงค่าจากช่วงที่กำหนดในแผ่นงาน input ครับ

snasui wrote: ↑Wed May 22, 2024 8:25 am

maanoii wrote: ↑Mon May 20, 2024 3:23 pm =INDEX(input!K$4:K$19,LOOKUP(9^9,MATCH({9,10},MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0),)))

ตัวอย่างคำอธิบายครับ

1. input!K$4:K$19: นี่คือช่วงข้อมูลในแผ่นงานชื่อ input คอลัมน์ K ตั้งแต่แถวที่ 4 ถึงแถวที่ 19 ที่เราจะดึงค่าจากสูตร INDEX มาแสดงผล
2. LOOKUP(9^9, ... ): ฟังก์ชัน LOOKUP ใช้ในการค้นหาค่าใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ 9^9 (ซึ่งเป็นจำนวนที่ใหญ่มาก จึงเป็นวิธีที่จะดึงค่าที่มากที่สุดในอาร์เรย์ที่ให้มา)
3. MATCH({9,10}, ... ): ฟังก์ชัน MATCH ใช้เพื่อค้นหาตำแหน่งของค่าในอาร์เรย์ที่ตรงกับเกณฑ์ที่กำหนด โดยที่ {9,10} คือค่าที่เราต้องการหา
4. MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0):
   • ($A4:$J4=input!$A$4:$J$19): ตรวจสอบว่าค่าในช่วง $A4:$J4 เท่ากับค่าในช่วง input!$A$4:$J$19 หรือไม่ (จะได้ผลลัพธ์เป็น TRUE หรือ FALSE)
   • --(...): แปลง TRUE/FALSE เป็น 1/0
   • ROW($Z$1:$Z$10)^0: สร้างอาร์เรย์ที่มีค่าเป็น 1 ทั้งหมด (เนื่องจาก ROW($Z$1:$Z$10) ให้ค่าตั้งแต่ 1 ถึง 10 และการยกกำลัง 0 ของตัวเลขใด ๆ จะได้ 1 เสมอ)
   • MMULT(... , ...): ฟังก์ชันการคูณเมทริกซ์ ใช้ในการคูณอาร์เรย์ 2 อัน ซึ่งผลลัพธ์จะเป็นอาร์เรย์ใหม่ที่ได้จากการคูณเมทริกซ์
5. รวมกัน:
   • MMULT(--($A4:$J4=input!$A$4:$J$19),ROW($Z$1:$Z$10)^0) จะได้ผลลัพธ์เป็นจำนวนที่เกิดจากการตรวจสอบค่าที่ตรงกันและรวมกัน
   • MATCH({9,10}, ... ) จะคืนค่าตำแหน่งของค่า 9 หรือ 10 ในอาร์เรย์ที่ได้จาก MMULT
   • LOOKUP(9^9, ...) จะค้นหาค่าที่ใกล้เคียงที่สุดในอาร์เรย์นี้
   • สุดท้าย INDEX(input!K$4:K$19, ...) จะใช้ค่าที่ได้จาก LOOKUP เป็นตัวระบุตำแหน่งในช่วง input!K$4:K$19 เพื่อดึงค่ามาแสดง

สรุป สูตรนี้ใช้ในการดึงค่าจากช่วง input!K$4:K$19 โดยค้นหาค่าในช่วง $A4:$J4 ที่ตรงกับช่วง input!$A$4:$J$19 และใช้การคำนวณต่างๆ เพื่อหาค่าที่ตรงกันมากที่สุดและนำค่าที่ตรงนั้นไปดึงค่าจากช่วงที่กำหนดในแผ่นงาน input ครับ

.......

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