ดึงข้อมูลโดยมีเงือนไขว่าต้องเอารอบ(ค่า)ที่มากที่สุด

[godman]

ผมอยากทราบว่าจะเขียนสูตรอย่างไรเพื่อดึงค่าจาก heet Database ฟิลล์ EN และฟิลล์ Round โดยเอารอบที่มากที่สุดและ EN มาใส่ใน Sheet 1 ช่อง A1 กบ B1 ซึ่งระบายเป็นสีเหลืองไว้ทั้งหมด โดยต้องใช้ Round ที่มากที่สุดครบเป็ฯตวต้ง แต่ก่อนอื่นจะ ลิตส์รายชื่อ EN ที่ไม่ซ็ำกันมาใส่ก่อนใช้สูตรใหนดครบ

[snasui]

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

ที่ชีท Sheet1

1. เซลล์ E1 คีย์สูตรเพื่อนับว่ามีค่าที่ไม่ซ้ำจำนวนเท่าไร

=ROUND(SUMPRODUCT(1/COUNTIF(Database!B2:B189,Database!B2:B189)),0)

Enter

2. เซลล์ A2 คีย์สูตรเพื่อ List รายการที่ไม่ซ้ำ

=IF(ROWS(A$2:A2)>$E$1,"",INDEX(Database!$B$2:$B$189,SMALL(IF(FREQUENCY(MATCH(Database!$B$2:$B$189,Database!$B$2:$B$189,0),ROW(Database!$B$2:$B$189)-ROW(Database!$B$2)+1),ROW(Database!$B$2:$B$189)-ROW(Database!$B$2)+1),ROWS(A$2:A2))))

Ctrl+Shift+Enter

3. เซลล์ B2 คีย์สูตรเพื่อหา Round ที่มากที่สุดจากรหัสใน A2

=IF(ROWS(B$1:B1)>$E$1,"",MAX(IF(A2=Database!$B$2:$B$189,Database!$F$2:$F$189)))

Ctrl+Shift+Enter

4. เซลล์ C2 คีย์สูตรเพื่อหาแผนก

=IF(ROWS(C$2:C2)>$E$1,"",INDEX(Database!$D$2:$D$189,MATCH(1,IF(Database!$B$2:$B$189=$A2,IF(Database!$F$2:$F$189=$B2,1)),0)))

Ctrl+Shift+Enter

5. เซลล์ D5 คีย์สูตรเพื่อหาก Model

=IF(ROWS(C$2:C2)>$E$1,"",INDEX(Database!$I$2:$I$189,MATCH(1,IF(Database!$B$2:$B$189=$A2,IF(Database!$F$2:$F$189=$B2,1)),0)))

Ctrl+Shift+Enter

[godman]

ขอบคุณครับ เยี่ยมเลยครับ แต่สูตรยาวผมขอศีกษาสูตรดูก่อน ผมอยากทราบว่า ผมชอบใช้แบบลากทั้งคอลัมพ์ เช่น อาจารย์จะจำกัด B2:B189 แต่ผมจะเรียกว่าเอาสะดวกเข้าว่า ลาก B:B ยาวเลย ไม่ทราบจะส่งผลเสียหรือปล่าวครับ เช่น ทำให้เครื่องช้าลง หรือปล่าวเป็นวิธีที่ถูกไหมครับ

[snasui]

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

[godman]

วิธีที่ดีที่สุดคือการกำหนด Defene name โดยใช้สูตร offset (, counta) ก่อนทุกครั้งใช่ไหมครับ

[snasui]

การทำให้ช่วงเซลล์ยืดหยุ่นตามปริมาณข้อมูลสามารถใช้สูตรกำหนด Range name ตามที่คุณ Godman ทราบ หรือใช้ Table ครับ สำหรับ Excel 2003 ถ้ากำหนดเป็น Table โปรแกรมจะคำนวณทุกครั้งที่ Table มีการเปลี่ยนแปลงทำให้คำนวณนาน

Excel 2007 เป็นต้นไปไม่มีปัญหานี้แล้วทำให้สะดวกขึ้นมาก สำหรับการให้ Range name เพื่อให้เกิดความยืดหยุ่นค่อนข้างจะเสียเวลาในการเขียนสูตรและหากว่ามีเงื่อนไขที่เกี่ยวข้องในการสร้างสูตรจำนวนมากก็จะต้องสร้าง Range name จำนวนมากตามไปด้วย นอกจากนี้หากมีการใช้ Range name จำนวนมาก ๆ บวกกับใช้ Range name แบบข้ามไฟล์แล้ว ปัญหาที่เคยพบใน Excel 2003 คือไฟล์ใหญ่ คำนวณช้าลงอย่างเห็นได้ชัดครับ

วิธีที่ดีที่สุดจึงอยู่ที่ความสะดวก การปรับใช้กับงานที่ทำครับ