หาข้อมูลหลายคอลัมน์ไปแสดงให้ตรงกับสิ่งที่ต้องการ

[Totem]

เรียนอาจารย์และเพื่อนสมาชิก

ต้องการให้แผนกต่าง ๆ คือช่อง I3:I12
หาข้อมูลจากช่อง A2:F22 ไปแสดงให้ตรงกับสิ่งที่ต้องการ
เช่น เมื่อมีสินค้า Q ช่อง H3 คำตอบที่ได้ในช่อง I3 คือ แผนก A

ขอบคุณครับ

หาข้อมูลหลายคอลัมน์.xlsx

[DhitiBank]

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

=IFERROR(INDEX($A$2:$F$20,SMALL(IF(ISNUMBER(SEARCH(H3,$A$2:$F$20)),ROW($A$2:$F$20)-ROW($A$2)+1),1),SMALL(IF(ISNUMBER(SEARCH(H3,$A$2:$F$20)),COLUMN($A$2:$F$20)-COLUMN($A$2)+1),1)+1),"")

Ctrl+Shift+Enter --> แล้วคัดลอกลงครับ

[snasui]

อีกตัวอย่างสูตรครับ เซลล์ I3 คีย์

=INDEX($A$2:$F$22,MATCH(2,1/((MMULT(COLUMN($A$2:$F$2)^0,--TRANSPOSE($A$2:$F$22=H3)))>0)),MATCH(2,1/((MMULT(TRANSPOSE(ROW($A$2:$A$22)^0),--($A$2:$F$22=H3)))>0))+1)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[DhitiBank]

โอ้ เมทริกซ์ ขอบคุณครับ

[Totem]

ขอบคุณครับ

ทั้ง2สูตรได้ตามที่ต้องการครับ
ขอคำอธิบายตรงสูตรนี้ครับ ยังไม่ค่อยเข้าใจครับ
1/((MMULT(COLUMN($A$2:$F$2)^0,--TRANSPOSE($A$2:$F$22=H3)))>0)

[snasui]

Mmult เป็นการหา Matrix ของผลคูณของ 2 Array ซึ่งจากสูตรที่ผมเขียนด้านบนเป็นการประยุกต์ใช้อีกทอดหนึ่งจึงทำความเข้าใจได้ยากมาก

ผลคูณของ 2 Array เป็นการนำ Array1 * Array2 โดยมีข้อกำหนดคือ Array2 จะต้องมีจำนวนบรรทัดเท่ากับจำนวนคอลัมน์ของ Array1

ยกตัวอย่างตามด้านล่าง

Code: Select all

   A B
1  1 2
2
3  4 5
4  6 7

Array1 คือ {1,2} และ Array2 คือ {4,5;6,7} สังเกตเครื่องหมาย ; ว่าคั่นเพื่อแสดงว่าขึ้นบรรทัดใหม่

เมื่อเขียน Mmult เช่น =Sum(Mmult({1,2},{4,5;6,7})) ผลรวมจะได้ 35 โดยมีลักษณะการคูณคือ =1*4+1*5+2*6+2*7

แต่หากนำ Transpose มาเปลี่ยนแกนของ Array โดยคีย์เป็น =Sum(Mmult({1,2},Transpose({4,5;6,7}))) สูตรจะกลายเป็น =Sum(Mmult({1,2},{4,6;5,7})) ผลลัพธ์จะได้ 34 โดยมีลักษณะการคูณคือ =1*4+1*6+2*5+2*7

ประเด็นสำคัญคือการเปลี่ยนแกนของ Array เพื่อให้แสดงผลในแกนที่เราต้องการ จำเป็นต้องทดสอบ ทดลองด้วยตนเองด้วยพื้นที่ที่มีขนาดใหญ่ขึ้นมาอีกสักเล็กน้อยเพื่อจะได้เห็นภาพ สำหรับตัวอย่างนี้เป็นตัวอย่างเล็ก ๆ เพื่อให้เห็นลักษณะการทำงานเท่านั้น

ในส่วนของการนำผลของ Array ไปเทียบกับค่าใด ๆ เช่น =Mmult({1,2},Transpose({4,5;6,7})>0 ก็เพื่อให้แสดงเป็น True, False โดยหากเข้าเงื่อนไขก็จะแสดงเป็น True หากไม่เข้าเงื่อนไขก็จะแสดงเป็น False ซึ่งจะมีลักษณะเป็น ={True,False,False...} เป็นต้น

เมื่อนำไปหารเลข 1 ก็จะได้เป็น 1/{True,False,False...} โดยค่าที่เป็น True จะแสดงผลลัพธ์เป็น 1 ส่วนค่าที่เป็น False จะแสดงผลลัพธ์เป็น #Div/0! ซึ่งจะได้เป็น {1,#Div/0!,#Div/0!...} เช่นนี้เป็นต้น

สามารถศึกษา Mmult ได้จาก Link นี้ครับ Mmult

[Totem]

ขอบคุณครับ

^ = ตัวยกกำลังใช่ไหมครับ ^0 = ยกกำลัง 0 เพื่อ ครับ

ก่อนหน้านี้เข้าไปดูตามlink ที่อาจารย์ให้มาแล้ว ยังไม่เข้าใจ
ได้อาจารย์อธิบายเพิ่ม พอเข้าใจ จะลองไปศึกษาดู เวลาจะปรับใช้เข้าใจยากครับ

[snasui]

^ = ตัวยกกำลังใช่ไหมครับ ^0 = ยกกำลัง 0 เพื่อ ครับ

ลองคีย์ค่าใด ๆ ที่ไม่ใช่ 0 แล้วตามด้วย ^0 แล้วสังเกตดูว่าได้ค่าใด นั่นคือคำตอบครับ

[Totem]

^ = ตัวยกกำลังใช่ไหมครับ ^0 = ยกกำลัง 0 เพื่อ ครับ

ลองคีย์ค่าใด ๆ ที่ไม่ใช่ 0 แล้วตามด้วย ^0 แล้วสังเกตดูว่าได้ค่าใด นั่นคือคำตอบครับ

ขอบคุณครับ

[DhitiBank]

สาเหตุที่เลขใดๆ ยกกำลัง 0 แล้วเท่ากับ 1 ครับ

ปกติแล้วเลขฐานสิบใดๆ จะมีเลขยกกำลังอยู่แล้วคือเลข 1 ครับ เช่น
2 = 2^1
จะเรียกเลข 2 ว่า "เลขฐาน" และเรียกเลข 1 ว่า "เลขชี้กำลัง"

หากเลขยกกำลังที่มีเลขฐานเท่ากัน มาคูณกัน เราสามารถเอาเฉพาะเลขชี้กำลังมาบวกกันได้เลย เช่น

power1.jpg

ในทางตรงกันข้าม หากเลขยกกำลังที่มีเลขฐานเท่ากัน มาหารกัน เราจะเอาเลขชี้กำลังของตัวตั้ง มาหักออกด้วยเลขชี้กำลังของตัวหาร เช่น

power2.jpg

แต่หากเลขฐานทั้งตัวตั้งและตัวหาร ต่างมีเลขชี้กำลังเท่ากัน เราชินกันอยู่แล้วว่ามันต้องเท่ากับ 1 แต่พอมาเขียนในรูปเลขยกกำลัง อาจดูแปลกตา เช่น

power3.jpg

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

ส่วนเรื่องเมทริกซ์ ดังที่อาจารย์อธิบายครับ เมื่อเรามีสิ่งที่ต้องการค้นหากระจายอยู่ในกลุ่มข้อมูลที่มีหลายแถวหลายคอลัมน์ การคูณเมทริกซ์ด้วยเมทริกซ์ (ลักษณะแถวและคอลัมน์ใน excel จะคล้ายเมทริกซ์ หรือจะว่ามันเป็นเมทริกซ์รูปแบบหนึ่งก็ได้ เมทริกซ์คือชุดข้อมูลที่อยู่ในรูปแถว x คอลัมน์) จะสามารถช่วยรวบให้ข้อมูลนั้นเหลือเพียง 1 แถว หรือ 1 คอลัมน์ได้ คร่าวๆ ก็ประมาณนี้ครับ

จบชั่วโมงคณิตศาสตร์...

[DhitiBank]

ยกเว้นเลข 0 ครับ ลืมใส่ข้อยกเว้นไป
0^0 ไม่เท่ากับ 1 และไม่เท่ากับ 0 แต่เท่ากับ #DIV/0!

[Totem]

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

[jelelite]

อีกสูตรที่ผมคิดได้ที่เซลล์ I3 คีย์
=IFERROR(INDEX($B$2:$B$22,MATCH($H3,$A$2:$A$22,0)),IFERROR(INDEX($D$2:$D$22,MATCH($H3,$C$2:$C$22,0)),IFERROR(INDEX($F$2:$F$22,MATCH($H3,$E$2:$E$22,0)),"")))

แล้ว Copy ลงด้านล่างครับ

[snasui]

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

[jelelite]

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