Snasui Excel Forum

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

ขอบคุณครับ

ที่ B8 ลองคีย์แบบนี้ครับ

=INDEX($I$5:$P$6,MATCH($B$4,$H$5:$H$6,0),COLUMNS($A8:B8)-COLUMN($A8))

Enter ครับ
แล้วคัดลอกไปทางขวาครับ

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

ผมขอรบกวนอีกครั้งนะครับ
ขอบคุณครับสำหรับคำตอบ และขอโทษอย่างยิ่งครับ

ที่ B8 ลองปรับสูตรเป็น

=INDEX(OFFSET($I$5,,($D$4=2554)*4,2,4),MATCH($B$4,$H$5:$H$6,0),COLUMNS($A8:B8)-COLUMN($A8))

Enter แล้วคัดลอกไปทางขวาครับ

ขอบคุณมากครับ สำหรับคำตอบครับ

ขอรบกวนอีกครั้งนะครับ
แล้วถ้าหากจะเพิ่มเป็นปีต่อๆ ไป จะต้องแก้เงื่อนไขส่วนไหนบ้างครับ
อย่างเช่นถ้าเพิ่มชุดข้อมูลปี 2555, 2556, ... ,n แบบนี้ครับ

ขอบคุณครับ

ถ้าอย่างนั้นต้องปรับสูตรใหม่ครับ รูปแบบตารางเหมือนเดิม แค่เติมข้อมูลปีอื่นๆ ไปทางขวาใช่ไหมครับ?

หากใช่ ปรับสูตรเป็นแบบนี้ครับ
=INDEX(OFFSET($I$5, ,LOOKUP(2,1/(3:3=$D$4),COLUMN(3:3)-9),2,4),MATCH($B$4,$H$5:$H$6,0),COLUMNS($A8:B8)-COLUMN($A8))

ถ้าอย่างนั้นต้องปรับสูตรใหม่ครับ รูปแบบตารางเหมือนเดิม แค่เติมข้อมูลปีอื่นๆ ไปทางขวาใช่ไหมครับ?
ใช่ครับ

สูตรตรงส่วนนี้ผมไม่เข้าใจความหมายเท่าไหร่ อยากให้ช่วยรบกวนอธิบายได้ไหมครับ เพื่อครั้งต่อไปจะได้ทราบครับ
LOOKUP(2,1/(3:3=$D$4),COLUMN(3:3)-9)
มองค่าแรก=2, หลังจากส่วนนี้ไม่เข้าใจครับ

ประเด็นเพิ่มเติม หากมีการอ้างอิงข้าม sheet ตรงส่วนนี้จะเป็นชื่อ sheet หรือเปล่าครับ
LOOKUP(ค่าที่ค้น,'ชื่อชีท'คอลัมน์,อาร์กิวเมนต์)

ขอบคุณครับสำหรับข้อมูล

=INDEX(OFFSET($I$5, ,LOOKUP(2,1/(3:3=$D$4),COLUMN(3:3)-9),2,4),MATCH($B$4,$H$5:$H$6,0),COLUMNS($A8:B8)-COLUMN($A8))

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

ส่วนเรื่องการอธิบายเดี๋ยวมาต่อให้นะครับ

--ต่อครับ--
LOOKUP(2,1/(3:3=$D$4),COLUMN(3:3))

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

มาดูที่ 1/(3:3=$D$4)
จะมองค่าในแถวที่ 3 ว่าเท่ากับ D4 หรือไม่ หากเท่าก็ True ไม่ก็ False เสร็จแล้วจับเอาไปหาร 1 สมมติว่าคอลัมน์ที่ 3 ในแถว 3 มีค่าเท่ากับ D4 นะครับ จะได้

1/{False,False,True,False,...}
=1/{0,0,1,0,...}
={#div0,#div0,1,#div0,...}

ข้อมูลใหม่ที่ได้จะมีแค่ 1 กับค่าผิดพลาด (เพราะถูกหารด้วย 0) แค่สองตัวนี้เท่านั้น และจากสูตร Lookup ให้มองหา 2 เมื่อมันไม่เจอมันก็จะเอาค่าที่มากที่สุดที่น้อยกว่า 2 คือ 1 ก็จะตรงกับข้อมูลตำแหน่งที่ 3 ครับ

จากนั้นสูตรก็จะเอาค่าจาก COLUMN(3:3) ในตำแหน่งที่ 3 มาแสดง นั่นก็คือตำแหน่งที่พบปีที่ต้องการครับ แล้วเอาไปประโยชน์ต่อในสูตร Offset

ในส่วนสุดท้ายตรง COLUMN(3:3)-9) ค่า -9 นี่ใช้สำหรับอะไรเหรอครับ
ส่วนอื่นๆ ผมเข้าใจ concept แล้วครับ
ขอบคุณครับ

raiver wrote:ในส่วนสุดท้ายตรง COLUMN(3:3)-9) ค่า -9 นี่ใช้สำหรับอะไรเหรอครับ
ส่วนอื่นๆ ผมเข้าใจ concept แล้วครับ
ขอบคุณครับ

ต้องเข้าใจสูตร Offset ก่อนครับ

Offset(ตำแหน่งอ้างอิง,เลื่อนตำแหน่งอ้างอิงขึ้นลง,เลื่อนตำแหน่งอ้างอิงไปขวาซ้าย,[ขนาดความสูงของช่วงอ้างอิง],[ขนาดความกว้างของช่วงอ้างอิง])

ผมใช้สูตรนี้เพราะช่วงอ้างอิงจะเปลี่ยนตามปีโดยมีการเลื่อนช่วงอ้างอิงไปทางขวา

--ดังนั้น ตำแหน่งอ้างอิงจึงกำหนดให้เริ่มจาก $i$5 ครับ

--การเลื่อนตำแหน่งขึ้นลง เว้นไว้ (คือ 0) คือไม่ต้องการให้เลื่อนตำแหน่งอ้างอิงขึ้นหรือลง (หากจะเลื่อน ตำแหน่งนี้จะเป็นได้ทั้งจำนวนเต็มบวก-เลื่อนลง และจำนวนเต็มลบ-เลื่อนขึ้น)

--การเลื่อนตำแหน่งซ้ายขวา จะได้ค่าจาก Lookup ที่อธิบายก่อนหน้านี้ แต่ที่ต้องเอามาลบออกด้วย 9 เพราะตำแหน่งอ้างอิง เราเริ่มด้วยคอลัมน์ i (คอลัมน์ลำดับที่ 9)
สมมติว่าเราใส่ปี 2554 สูตร Lookup จะพบปีนี้ในคอลัมน์ M และหากไม่มี -9 ก็จะคืนค่า 13 มา แต่ในการเลื่อน เราต้องการเลื่อนจากคอลัมน์ i -->m ซึ่งต้องเลื่อนไปอีกเพียง 4 จึงต้องเอาค่าที่ได้จาก Lookup ไปลบออกด้วย 9 ครับ (จำนวนเต็มบวกเลื่อนขวา เต็มลบเลื่อนซ้าย)

--ส่วนขนาดความสูงกับความกว้างของช่วงอ้างอิงไม่ต้องใส่ก็ได้ แต่กรณีนี้ใส่ 2, 4 คือ ให้ช่วงอ้างอิงมีขนาดสูง 2 เซลล์ กว้าง 4 เซลล์ (เริ่มนับจากตำแหน่งอ้างอิงที่ถูกเลื่อนแล้วครับ)

เข้าใจแล้วครับ ยังไงต้องขอขอบคุณเป็นอย่างยิ่งด้วยครับ ที่ให้ข้อมูลที่เป็นประโยชน์ครับ

ขอบคุณครับ