การค้นหาข้อมูลที่ต้องการเมื่อพบให้คัดลอกนำมาวางในเซลที่กำหนด

[snasui]

การจะเข้าใจความหมายต้องเข้าใจความหมายของฟังก์ชั่น If เสียก่อนครับ ฟังก์ชั่นข้างบนใช้ฟังก์ชั่น IF ในการแสดงผล

ไวยากรณ์ของ If คือ

=If(เงื่อนไข, ผลลัพธ์หากเงื่อนไขเป็นจริง, ผลลัพธ์หากเงื่อนไขเป็นเท็จ)

สำหรับตรงเงื่อนไข จะมีได้สองค่าเท่านั้นคือ True (จริง) และ False (เท็จ)

จากสูตร

=IF(ROWS(H$36:H36)>$G$36,"",ROWS(H$36:H36))

มาแทนสูตรตามด้านบนจะได้ว่า

1. เงื่อนไขคือ ROWS(H$36:H36)>$G$36 ซึ่งจะต้องได้ผลลัพธ์เป็น True หรือ False อย่างใดอย่างหนึ่งเท่านั้น
2. ผลลัพธ์ถ้าเงื่อนไขเป็นจริงคือให้แสดง "" หมายถึงไม่ให้แสดงข้อมูลใด หรือหมายถึงให้แสดงให้เห็นเป็นค่าว่าง
3. ผลลัพธ์ถ้าเงื่อนไขเป็นเท็จคือให้แสดงผลลัพธ์จาก ROWS(H$36:H36)

เมื่อ ROWS(H$36:H36) มีค่าเป็น 1 และ $G$36 มีค่าเป็น 11

ROWS(H$36:H36)>$G$36 จึงเป็น False (เท็จ) เพราะ 1 ไม่ได้มากกว่า 11

ดังนั้นคำตอบคือ ROWS(H$36:H36) หรือจำนวนบรรทัดระหว่าง H$36:H36 ซึ่งก็คือ 1 บรรทัด จากที่ได้ลองอธิบายมาก็ถูกต้องแ้ล้วครับ

[kong]

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

[kong]

ความไม่รู้เป็นทุกข์อย่้างยิ่ง ผมกำลังติดกับดักของ EXCEL ครับ สนุกดีครับที่ท่านอาจารย์อธิบาย ช่วยอธิบายเพิ่มเติมด้วยครับ สงสัยฟังก์ชัน INDEX ครับช่วยอธิบายด้วยครับ ศึกษาในหนังสือบอกว่า ฟังก์ชัน INDEX ใช้ในการหาค่าโดยการใช้ดัชนีเพื่อกำหนดจุดตัดในอาร์เรย์/หรือจุดตัดในอ้างอิง ช่วยอธิบายไวยากรณ์ของINDEXด้วยครับ จะใช้INDEXเมื่อข้อมูลต้องจัดเรียงลำดับหรือไม่ มีข้อจำกัดอย่างไรบ้าง
จากที่อาจารย์เคยอธิบายสูตรข้างล่างข้างล่าง INDEX ทำหน้าที่อะไร ทำตามคำสั่งของ IF ใช่ไหม

ที่ I36 คีย์สูตรเพื่อ List รายชื่อผู้ที่ได้คะแนนมากกว่าหรือเท่ากับ 70

=IF(N($H36),INDEX(B$4:B$33,SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"")
ช่วยอธิบายเพิ่มเติมด้วยนะครับโดยเฉพาะมีตรง+1เพิ่มเข้ามาด้วย

[snasui]

ก่อนจะไปอธิบายฟังก์ชั่นตามที่ถามมาซึ่งค่อนข้างซับซ้อน ให้ทำความเข้าใจ Index ในรูปแบบง่าย ๆ ก่อนดีกว่าครับ

Index สามารถให้ผลลัพธ์จากตารางแบบ 2 มิติได้ คือทั้งทางคอลัมน์และทางบรรทัด

2 มิติมีลักษณะเป็นอย่างไร จะขอยกเป็นตัวอย่างแล้วกันครับ เช่น

1. A1:A20 นี่คือมิติเดียว มีเฉพาะคอลัมน์ A และลึกไปยังบรรทัดที่ 20
2. A1:C20 นี่คือ 2 มิติเนื่องจากมี 3 คอลัมน์ คือ A, B, C และยังลึกไปยังบรรทัดที่ 20
3. A1:H1 นี่คือมิติเดียว มีเฉพาะบรรทัดที่ 1 และกว้างไปถึงคอลัมน์ที่ 8

Index มีไวยากรณ์ 2 แบบ แต่ที่ผมนำมาใช้นี้เป็นแบบแรก คือแบบด้านล่าง

=Index(ช่วงข้อมูล, ลำดับของข้อมูลในทางคอลัมน์ (ทางลึก) ที่ต้องการนำมาแสดง, ลำดับของข้อมูลในทางบรรทัด (ทางขวาง) ที่ต้องการนำมาแสดง)

และที่ตอบคำถามไปนั้นลดรูปมาเหลือ

=Index(ช่วงข้อมูล, ลำดับของข้อมูลที่ต้องการนำมาแสดง) การใช้ตามรูปแบบนี้ช่วงข้อมูลจะต้องเป็นตามข้อ 1 หรือ 3 ตามด้านบน ซึ่งตามคำถามที่ถามมาช่วงข้อมูลจะเป็นตามข้อ 1 คือลึกลงด้านล่าง

สมมุติมีข้อมูลอยู่ที่ A1:A5 เป็น
   A  B
1. 5
2. 4
3. 3
4. 2
5. 1

หากที่ B1 คีย์สูตรเป็น

=Index(A1:A5,4)

เราสามารถแทนค่าตามไวยากรณ์ได้ว่า

1. A1:A5 คือช่วงข้อมูล
2. เลข 4 คือลำดับของข้อมูลที่ต้องการนำมาแสดง

คำตอบจะได้ 2 ทั้งนี้เพราะ ลำดับที่ 4 ของช่วงข้อมูลคือเลข 2

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

จาก INDEX(B$4:B$33,SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36))

แทนค่าตามไวยากรณ์

1. B$4:B$33 คือช่วงข้อมูล
2. SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36) คือ ลำดับของข้อมูลที่ต้องการนำมาแสดง

ไม่เข้าใจตรงไหนสามารถถามเพิ่มได้ หากเข้าใจแล้วก็ถามในส่วนที่ยังไม่เข้าใจต่อได้เรื่อย ๆ ครับ

[kong]

เข้าใจเพิ่มขึ้นเยอะเลยครับ ขอคำอธิบายเพิ่ม อย่างละเอียดเลยนะครับ จากสูตร

=IF(N($H36),INDEX(B$4:B$33,SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"") ตรงระบายแถบสีแดง
หมายความว่าหาค่าน้อยที่สุดจากผลลัพธ์ของIF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"")
ช่วยแทนค่าให้ดูหรือบอกวิธีคิดให้ด้วยครับว่าทำไมผลออกมาเป็นชื่อของบุคคลที่อยู่ในเซลนั้นได้ครับ(งงตรงมีเครื่องหมาย+ - ด้วย)

ขอบคุณครับ

[snasui]

ในส่วนของ Small เป็นการใช้งานแบบ Array ครับ ก่อนจะไปเข้าใจ Array ให้เข้าใจ Small แบบปกติก่อนครับ

ไวยากรณ์คือ

=Small(ช่วงข้อมูลที่เป็นตัวเลข, ค่าลำดับ)

ยกตัวอย่างสมมุติข้อมูลเป็นตามด้านล่าง

    A B
1. 5
2. 8
3. 6
4. 2
5. 4

หากที่ B1 เขียนสูตรว่า

=Small(A1:A5,3)

หมายถึงว่า ให้หาค่าที่น้อยที่สุดเป็นลำดับที่ 3 ของช่วงข้อมูล A1:A5 คำตอบจะได้ 5

โดยค่าที่น้อยที่สุดแต่ละลำดับจะเป็นดังนี้
ค่าที่น้อยที่สุดเป็นลำดับที่ 1 คือ 2
ค่าที่น้อยที่สุดเป็นลำดับที่ 2 คือ 4
ค่าที่น้อยที่สุดเป็นลำดับที่ 3 คือ 5
...

ทีนี้ก็มายังสูตรที่ถามมาครับ

จาก SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36))

เมื่อแทนค่าตามไวยากรณ์

1. ช่วงข้อมูลที่เป็นตัวเลขคือ IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1)
2. ค่าลำดับคือ $H36

ทีนี้มาแกะสูตร If แบบ Array กันครับ จากด้านบนเป็นสูตรที่ครอบคลุมช่วงข้อมูลปริมาณเยอะ จะอธิบายให้เห็นภาพได้ยาก

จะยกตัวอย่างเล็ก ๆ ตามด้านล่างครับ

     K L
1. 65
2. 83
3. 40
4. 75
5. 90

หากที่ L1 คีย์เป็น

=IF($K$1:$K$5>=70,ROW($K$1:$K$5)-ROW($K$1)+1)

Ctrl+Shift+Enter

หมายความว่า หาก $K$1:$K$5 มากกว่าหรือเท่ากับ 70 ให้แสดงค่าลำดับ หากไม่ใช่ให้แสดง False

มาแกะสูตรด้วยการกดแป้น F9 กันทีละขั้นครับ

1. ลากเมาส์คลุม $K$1:$K$5 แล้วกดแป้น F9 จะได้ =IF({65;83;40;75;90}>=70,ROW($K$1:$K$5)-ROW($K$1)+1)
2. ลากเมาส์คลุม {65;83;40;75;90}>=70 แล้วกดแป้น F9 จะได้ =IF({FALSE;TRUE;FALSE;TRUE;TRUE},ROW($K$1:$K$5)-ROW($K$1)+1)
3. ลากเมาส์คลุม ROW($K$1:$K$5) แล้วกดแป้น F9 จะได้ =IF({FALSE;TRUE;FALSE;TRUE;TRUE},{1;2;3;4;5}-ROW($K$1)+1)
4. ลากเมาส์คลุม ROW($K$1) แล้วกดแป้น F9 จะได้ =IF({FALSE;TRUE;FALSE;TRUE;TRUE},{1;2;3;4;5}-{1}+1)
5. ลากเมาส์คลุม {1;2;3;4;5}-{1} แล้วกดแป้น F9 จะได้ =IF({FALSE;TRUE;FALSE;TRUE;TRUE},{0;1;2;3;4}+1)
6. ลากเมาส์คลุม {1;2;3;4;5}-{1}+1 แล้วกดแป้น F9 จะได้ =IF({FALSE;TRUE;FALSE;TRUE;TRUE},{1;2;3;4;5})
7. ลากเมาส์คลุม =IF({FALSE;TRUE;FALSE;TRUE;TRUE},{1;2;3;4;5}) แล้วกดแป้น F9 จะได้ ={FALSE;2;FALSE;4;5}
มาถึงขั้นนี้ให้สังเกตว่าหากค่าด้านหน้าเป็น True จะนำค่าด้านหลังมาแสดง หากเป็น False ก็จะแสดงค่า False

เมื่อสูตร If อยู่ใน Small อีกที และสมมุติต่อว่าเราต้องการหาค่าที่น้อยที่สุดเป็นลำดับ 3 จากผลลัพธ์ที่ได้จาก IF จะได้เป็น

=Small({FALSE;2;FALSE;4;5},3)

คำตอบคือ 5 เนื่องจากเป็นค่าที่น้อยที่สุดเป็นลำดับ 3

ตอนนี้เราก็แกะสูตรมาถึง Small แล้ว สมมุติต่อว่าเรานำผลลัพธ์ของ Small มาใช้ใน Index ซึ่งต้องการให้แสดงข้อมูลใน J1:J5 ด้านล่าง ด้วยผลลัพธ์ของ Small ด้านบน

...J  K L
1. A 65
2. B 83
3. C 40
4. D 75
5. E 90

ที่ L2 สามารถเขียนสูตร Index โดยมีผลจาการแกะสูตร Small เป็นส่วนประกอบจะมีลักษณะตามด้านล่าง

=Index(J1:J5,Small({FALSE;2;FALSE;4;5},3))

และเมื่อ Small({FALSE;2;FALSE;4;5},3) ผลลัพธ์คือ 5 สูตรก็จะกลายเป็น

=Index(J1:J5,5)

นั่นคือให้นำลำดับที่ 5 ของ J1:J5 มาแสดง คำตอบที่ได้คือ E

Note: การบวกด้วย 1 เพื่อให้ค่าลำดับเริ่มที่เลข 1 เสมอ

[kong]

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

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

[kong]

หวัดดีครับ อาจารย์ ผมรวบรวมผลงานที่อาจารย์ตอบได้หลายแผ่นแล้วครับ สูตรเดิมครับอาจารย์

=IF(N($H36),INDEX(B$4:B$33,SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"")
ผมจะขออธิบายสูตรตามความเข้าใจของผม เพราะผมจะได้ทราบว่าผมเข้าใจผิดตรงไหน
=IF(N($H36), ถ้าเซลH36 เป็นตัวเลข ให้แสดงผลลัพธ์จากสูตร
INDEX(B$4:B$33 ค้นหาข้อมูลในช่วง B$4:B$33
ให้นำผลลัพธ์ของ SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"")
มาแสดง
SMALL(IF($K$4:$K$33>=70 หาค่าน้อยที่สุด ถ้า$K$4:$K$33มีค่ามากกว่าหรือเท่ากับ70 ให้แสดง
ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"") ตรงนี้สำคัญเพราะผมเข้าใจว่า
ROW($K$4:$K$33)มีค่า= 4 (จากฟังก์ชัน ROW)
ROW($K$4) มีค่า = 4 (จากฟังก์ชัน ROW)
แทนค่าแล้วจะได้ (4-4)+1)1 ซึ่ง$H36 ก็มีค่าเท่ากับ 1 ใช่ไหมครับ ตรงจุดนี้และครับงงที่สุดในโลกกกกกกกกกกกกก...........ถ้าแทนค่าตามนี้ผลจะออกมาไม่ถูกเพราะเหตุผลใดครับ
ห้ามหัวเราะนะครับ ช่วยตรวจด้วยครับ ผมอยากเข้าใจ ถ้าเอาสูตรอาจารย์ไปใช้โดยตัวเองไม่เข้าใจสูตรก็รู้สึกไม่ค่อยดีครับ

อีกข้อหนึ่งครับ ดูในsheet แล้วชื่อนางสาวศิวพร เศษตะคำ อยู่ในเซลB5และC5 เป็นคนแรกที่อยู่ในเงื่อนไขของคะแนน ซึ่งได้คะแนนรวม 77 คะแนน(ไม่ทราบว่าได้คะแนนอันดับที่เท่าไร และข้อมูลก็ไม่ได้เรียงคะแนนไว้ นั่นก็แสดงว่า INDEX ค้นหาข้อมูลที่ตรงเงื่อนไขมากกว่าหรือเท่ากับ70 ค่าแรกที่พบนำมาแสดงใช่ไหมครับโดยไม่สนว่ามีค่ามากหรือน้อยเป็นอันดับที่เท่าไร)

[snasui]

...
SMALL(IF($K$4:$K$33>=70 หาค่าน้อยที่สุด ถ้า$K$4:$K$33มีค่ามากกว่าหรือเท่ากับ70 ให้แสดง
ROW($K$4:$K$33)-ROW($K$4)+1),$H36)),"") ตรงนี้สำคัญเพราะผมเข้าใจว่า
ROW($K$4:$K$33)มีค่า= 4 (จากฟังก์ชัน ROW)
ROW($K$4) มีค่า = 4 (จากฟังก์ชัน ROW)
แทนค่าแล้วจะได้ (4-4)+1)1 ซึ่ง$H36 ก็มีค่าเท่ากับ 1 ใช่ไหมครับ ตรงจุดนี้และครับงงที่สุดในโลกกกกกกกกกกกกก...........ถ้าแทนค่าตามนี้ผลจะออกมาไม่ถูกเพราะเหตุผลใดครับ
ห้ามหัวเราะนะครับ ช่วยตรวจด้วยครับ ผมอยากเข้าใจ ถ้าเอาสูตรอาจารย์ไปใช้โดยตัวเองไม่เข้าใจสูตรก็รู้สึกไม่ค่อยดีครับ

ROW($K$4:$K$33) ไม่ได้มีค่าเท่ากับ 4 ครับ สูตรนี้เป็นสูตร Array จะแสดงเป็นชุดของตัวเลขคือ 4, 5, 6,...33 ครับ แปลจากฟังก์ชั่น Row() ที่ใช้แบบปกติเช่นนั้นไม่ได้ ซึงผมก็ได้แสดงการแกะสูตรให้ดูแล้วด้านบนให้ทบทวนจากตรงนั้น หากอธิบายซ้ำจะเสียเวลามากครับ

อีกข้อหนึ่งครับ ดูในsheet แล้วชื่อนางสาวศิวพร เศษตะคำ อยู่ในเซลB5และC5 เป็นคนแรกที่อยู่ในเงื่อนไขของคะแนน ซึ่งได้คะแนนรวม 77 คะแนน(ไม่ทราบว่าได้คะแนนอันดับที่เท่าไร และข้อมูลก็ไม่ได้เรียงคะแนนไว้ นั่นก็แสดงว่า INDEX ค้นหาข้อมูลที่ตรงเงื่อนไขมากกว่าหรือเท่ากับ70 ค่าแรกที่พบนำมาแสดงใช่ไหมครับโดยไม่สนว่ามีค่ามากหรือน้อยเป็นอันดับที่เท่าไร)

ถูกต้องครับ เจอค่าใดมากกว่าหรือเท่ากับ 70 ก็นำมาแสดงเลย

[kong]

กำลังศึกษาเรื่องสูตรและฟังก์ชันตามที่ท่านอาจารย์ แนะนำครับ เข้าใจเพิ่มขึ้นมากครับ เจอสูตรนี้
=IF(ROWS(A$5:A5)<=MAX(Sheet1!$E:$E), LOOKUP(ROWS(A$5:A5), Sheet1!$E:$E, Sheet1!A:A),"")
ขอคำอธิบายอย่างละเอียดด้วยครับ ขอขอบคุณล่วงหน้าครับ

[snasui]

สูตรที่เจอมานั้นไม่ขออธิบายละเอียดนะครับ หลัก ๆ ที่่ต้องเข้าใจก็คือช่วง LOOKUP(ROWS(A$5:A5), Sheet1!$E:$E, Sheet1!A:A) ซึ่งอยู่ในฟังก์ชั่น IF ส่วนฟังก์ชั่น IF อธิบายไปแล้วในความเห็นก่อน ๆ

Lookup มีไวยากรณ์คือ

=Lookup(ค่าที่ต้องการค้นหา, ช่วงข้อมูลทีบรรจุค่าที่ต้องการค้นหา, ช่วงข้อมูลที่ต้องการนำมาแสดงผล)

เมื่อแทนค่าตามไวยากรณ์จะได้ว่า

่1. ROWS(A$5:A5) คือค่าที่ต้องการค้นหา
2. Sheet1!$E:$E คือช่วงข้อมูลที่ต้องการค้นหาค่าตามข้อ 1. ซึ่งช่วงข้อมูลตามข้อ 2 นี้จำเป็นต้องเรียงจากน้อยไปหามากเสมอ
3. Sheet1!A:A คือช่วงข้อมูลที่ต้องการนำผลมาแสดง

แปลอีกทีคือ ค้นหาค่าที่น้อยกว่าหรือเท่ากับ ROWS(A$5:A5) จาก Sheet1!$E:$E เมื่อพบแล้วให้นำค่าที่ตรงกันในช่วง Sheet1!A:A มาแสดง เมื่อไม่พบจะแสดง #N/A

[kong]

ขอบพระคุณอย่างสูงครับ