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

[kong]

รบกวนหน่อยครับ ขอยกตัวอย่างง่ายๆนะครับ ผลการสอบของนักเรียน40คนมีได้คะแนนตั้งแต่20-100 ต้องการให้ค้นหาข้อมูลผู้ที่ได้คะแนนตั้งแต่50คะแนนขึ้นไป เมื่อพบให้คัดลอกชื่อและนามสกุลนักเรียนพร้อมคะแนนที่ได้มาวางในเซลที่กำหนดหรือเลือกไว้

[snasui]

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

[kong]

แนบไฟล์มาให้แล้วครับ

[snasui]

ลองตามไฟล์แนบครับ

1. เซลล์ G3o คีย์สูตรเพื่อนับว่าคะแนนรวมที่มากกว่าหรือเท่ากับ 70 คะแนนมีกี่คน

=COUNTIF(K4:K33,">=70")

Enter

2. เซลล์ H36 คีย์สูตรเพื่อให้ลำดับซึ่งต้องไม่เกินจำนวนตามข้อ 1

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

Enter > Copy ลงด้านล่าง

3. ที่ 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)),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง การปรับปรุงแก้ไขสูตรนี้จะต้องกดแป้นให้รับสูตรด้วย 3 แป้นดังกล่าวเสมอ เนื่องจากเป็นสูตร Array

4. ที่ J36, K36 ใช้สูตรตามข้อ 3 แต่เปลี่ยนเฉพาะช่วงข้อมูลในฟังก์ชั่น Index จากนั้น Copy ลงด้านล่างครับ

[kong]

ขอบพระคุณเป็นอย่างสูงครับ แต่ขอความกรุณาอธิบายสูตรในข้อ 3 ด้วยครับเพราะยังงงๆอยู่ครับ

[snasui]

ดาวน์โหลดไฟล์ที่ผมแนบไปดูแล้วยังครับ

สูตรดังกล่าวเป็นสูตร Array ต้องค่อย ๆ ทำความเข้าใจ ซึ่งผมจะแปลผลลัพธ์จากฟังก์ชั่นด้านนอกเข้าด้านในตามด้านล่างครับ

1. จาก 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)),"")

หมายความว่า หาก H36 เป็นตัวเลขก็ให้แสดงผลลัพธ์จากสูตร INDEX(B$4:B$33,SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36)) หากไม่ใช่ตัวเลขก็ให้แสดงค่าว่าง

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

หมายความว่า ให้ดูในช่วงข้อมูล B$4:B$33 โดยนำลำดับที่ได้จากผลลัพธ์ของ SMALL(IF($K$4:$K$33>=70,ROW($K$4:$K$33)-ROW($K$4)+1),$H36) มาแสดง

3. จาก 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

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

หมายความว่า หาก $K$4:$K$33 มากกว่าหรือเท่ากับ 70 แ้ล้วให้แสดงผลลัพธ์ของ ROW($K$4:$K$33)-ROW($K$4)+1 ซึ่งนั่นก็คือให้แสดงหมายเลขลำดับเริ่มจาก 1 ขึ้นไป ถ้าไม่มากกว่าหรือเท่ากับ 70 แล้วก็ให้แสดงค่า False

[kong]

วิทยายุทธ์ยังมีน้อยครับ ช่วยอธิบายสูตรนี้ด้วยครับ =IF(ROWS(H$36:H36)>$G$36,"",ROWS(H$36:H36))
ช่วยแนะนำเพิ่มเติมด้วยครับว่า จะต้องไปศึกษาสูตรหรือฟังก์ชันใดเพิ่มเติมครับ บางครั้งยังไม่ค่อยเข้าใจครับ

[snasui]

จากสูตร =IF(ROWS(H$36:H36)>$G$36,"",ROWS(H$36:H36))

หมายความว่า ถ้า ROWS(H$36:H36)>$G$36 เป็นจริง ให้แสดงค่าว่าง ถ้าไม่เป็นจริงให้แสดง ROWS(H$36:H36)

ROWS(H$36:H36) คือ จำนวนบรรทัดระหว่าง H$36:H36 ครับ จากสูตรนี้จะแสดงผลลัพธ์เป็น 1

เนื่องจากคำถามนี้เป็นคำถามที่ต้องใช้ฟังก์ชันซ้อนฟังก์ชั่น ไม่ง่ายที่จะทำความเข้าใจสำหรับมือใหม่ ฟังก์ชั่นที่ควรศึกษาเพิ่มก็เท่าที่เขียนไว้ทั้งหมดครับ เช่น

Index, Small, If, Rows, Row, Countif สามารถศึกษาได้จากที่นี่ครับ http://office.microsoft.com/th-th/excel ... 04211.aspx

[kong]

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

[kong]

เรียนท่านปรมาจารย์ คนควน ที่นับถือ
ตอนนี้ผมพึ่งกินยาแอสไพรินไป 1 เม็ด ก็อย่างที่ท่านอาจารย์เคยกล่าวไว้ นั่นแหละ คือ มันไม่ง่ายนักสำหรับมือใหม่ที่จะทำความเข้าใจฟังก์ชันซ้อนฟังก์ชัน ผมทำตามที่อาจารย์แนะนำศึกษาฟังก์ชันที่เกีี่ยวข้องดูแล้ว มันไม่เหมือนกระบวนยุทธ์ที่ท่านอาจารย์แสดงเลย ในตำราเป็นแค่ขั้นพื้นฐาน แต่กระบวนท่าที่ท่านอาจารย์แสดงให้ดู มันช่างสุดยอด ลึกล้ำ พลิกแพลง แปรเปลี่ยนไม่มีที่สิ้นสุด บรรลุถึงขั้นใช้กระบวนท่าตามอำเภอใจได้อย่างแท้จริง บางกระบวนท่าช่างรวดเร็ว ดูไม่ทัน ตาลาย ที่จริงผมซื้อตำรา EXCEL มาประมาณ 7-8 เล่ม เพราะในบรรดาโปรแกรมของ Microsoft ผมชอบ EXCEL มากที่สุดเพราะมีประโยชน์มาก ช่วยให้ผมรอดตายจากงานประจำมาแล้วหลายครั้ง แต่ผมก็เป็นผู้ที่สนใจเท่านั้นไม่เชี่ยวชาญอะไร เพราะไม่มีความรู้พื้นฐาน จากคำอธิบายของอาจารย์ของคราวที่แล้ว ผมขอถามอาจารย์เพิ่มเติมนะครับ เพราะผมต้องการคำิอธิบายที่ค่อนข้างละเอียด

จากสูตร =IF(ROWS(H$36:H36)>$G$36,"",ROWS(H$36:H36))

หมายความว่า ถ้า ROWS(H$36:H36)>$G$36 เป็นจริง ให้แสดงค่าว่าง ถ้าไม่เป็นจริงให้แสดง ROWS(H$36:H36)

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

[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),"")
ขอคำอธิบายอย่างละเอียดด้วยครับ ขอขอบคุณล่วงหน้าครับ