ถามเกี่ยวกับ ARRAY ที่ หาค่าเฉลี่ยจากข้อมูลที่นับ

[godman]

คือผมไปนับใน Sheet Y-2010 นับเฉพาะรหัส 1312 ทั้งอยู่ใน OPT1 OPT2 OPT 3
พบว่ามีทั้งหมด 7 ชื่อ อันนี้ Excel ถูกต้อง แต่เมื่อผมไปนับ คอลัมพ์ Yield ของทั้ง
7 cell รวมกันหาร 7 ผมคำนวนในเครื่องคิดเลขได้ 96.84 นอกจากนี้ผมไปนั่งนับล้อท
ในชี้ตเดิมคอลัมพ์ Submit GG กับ GH ผมก็นับ First และ Pass พบว่ามีทั้งหมด
4 ล้อท แต่ใน Ecel นับได้เพียง 1 ล้อท ล้อทที่ Pass ก็ 1 ล้อท ซึ่งได้ 100% ครับซึ่งมันต้องคำนวนได้ 4/7 = 57.1 เปอร์เซนต์
แต่ผลที่สูตรคำนวนให้มันไม่ตรง
ครับ
ผมอยากทราบว่ามีความผิดพลาดที่ใหนครับ ผมอยากให้ตัวเลตรงคือได้ที่ 57.1 ครับ
ปล อยู่ใน sheet caculate ครับ

[snasui]

ตอนนีข้อมูลรหัสใน OPT1 OPT2 OPT3 ไม่เป็นมาตรฐานครับ

บางตัวก็ถูกจัด Format เป็นตัวเลขแล้วเพิ่มด้วยการถูกจัด Format ให้ชิดซ้ายของเซลล์ และเซลล์ส่วนใหญ่ถูกกำหนด Format ให้เป็น Text

การที่เป็นตัวเลขบ้างเป็นอักขระ (Text) บ้าง ทำให้การคำนวณผิดพลาดได้ครับ ยกเว้น Sumif หรือ Countif จะมีความสามารถในการคำนวณโดยไม่คำนึงถึง Format ของตัวเลข

ตอนนี้ควรปรับให้เป็น Text ให้หมดครับ โดยใน Sheet Y-2010 เซลล์ GL2 คีย์

=TEXT(H2,"0000")

Enter > Copy ไปด้านขวาจนถึง GN2 > Copy ลงด้านล่าง จากนั้น Copy ค่าที่ได้ไปวางที่ H2:Gn (n คือค่าสุดท้าย) โดยวางแบบพิเศษเฉพาะค่าเท่านั้น และลบ GL2:GLn (n คือค่าสุดท้าย) ทิ้งไป เนื่องจากสร้างขึ้นเพื่อใช้งานชั่วคราว

ใน Sheet Calculate ก็เช่นเดียวกัน คอลัมน์ D จะต้องถูกจัด Format ให้เป็น Text ให้หมด หลังจากทำครบแล้วลองสังเกตผลการคำนวณอีกรอบครับ

[godman]

ผมทำได้แล้วครับ ผมไปอ่านในที่อาจารย์เขียนไว้ โดยผมเลือกรูปรูปแบบเป็น special 0000 ทั้งหมด ทั้งใน dATABASE และ Calcualtion ตอนนี้ไม่มีปัญหาแล้วครับ ตอนนี้ผู้บริหารชื่นชอบการ TTrack ของผม+อาจารย์มากๆ555 ท่านก็อยากให้ผมทำแบบสามารถ Track ได้ถึง รายวัน ไม่ทราบมีคำแนะนำไหมครับ หรือว่าจะต้องไป define name ทุกวัน โอ้โหมีตั้ง 1000 วันมั้งง มีคำแนะนำไหมครับอาจารย์

[snasui]

การ Define Name นั้นสามารถทำให้ Dynamic ได้ ทั้งเลือกแบบเฉพาะเจาะจงกลุ่มข้อมูล เช่น วันใด ๆ ที่เรากำหนดเอง หรือเลือกตามขนาดข้อมูลที่เพิ่มลด การ Define name สามารถทำมาใช้แทน List ได้เพื่อให้คำนวณได้เร็วขึ้น เนื่องจาก List ส่งผลให้คำนวณช้า

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

ยกตัวอย่างการให้ชื่อแบบ Dynamic ของ Sheet Database หากข้อมูลเพิ่ม ชื่อก็จะเพิ่มตาม หากข้อมูลลดชื่อก็จะลดตาม

=Offset($A$1,0,0,Counta($A:$A),Counta($1:$1))

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

เป็นการกำหนดให้พื้นที่ของชื่อเริ่มตั้งแต่ A1 มีความลึกลงไปด้านล่างเท่าจำนวนบรรทัดไม่ว่างในคอลัมน์ A มีความกว้างไปทางด้านขวาเท่ากับจำนวนคอลัมน์ที่ไม่ว่างในบรรทัดที่ 1

[godman]

version 2007 มันจะมีให้ insert เป็น Table ผมลองทำแปลงเป็น Tabel มันสามารถ DYNAMIC ได้เหมือนกัน ไม่ทราบว่ามันทำได้ไหมครับ หรือว่าใช้สูตร offset กับ counta ต่างกันไหมครับ

[godman]

ผมโนบิตะ จะไปตั้งชื่อให้มันก่อนนะครับ ตั้งชือ Database ไปเลยลองดู

[snasui]

Version 2007 และ 2010 สามารถใช้ Table ได้ครับ ถูกปรับปรุงให้ลดข้อด้อยจาก 2003 ที่คำนวณได้ช้าครับ

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

[godman]

อาจารย์หายไป 2 วันผมใจหายแว้บ นึกว่าเป็นอะไรไป

[snasui]

ผมมีกิจกรรมของออฟฟิศต้องไปต่างจังหวัดเพิ่งกลับมาเมื่อตอนเย็นของวันนี้ ขอบคุณที่เป็นห่วงครับ

[godman]

คือผมกำลังก้อปปี้ชื่อของ OPT1 OPT2 OPT3 ของวันที่ 15 aug 2010 ซุ่งผมไป rangename แล้ว แต่ปัญหาคือผมต้องมานั่งลบ EN ย่อจาก Employee number นะครับออก เพื่อเอาเฉพะที่ไม่ซำกัน มีคำแนะนำไหมครับว่ามีวิธีที่ excel ทำได้ โดยไม่ต้องมี remove duplicate ซึ่งข้อมูลมีในลักษณะ array ครับ

[godman]

AVERAGE(IF((('Y-2010'!$B$2:$B$5000=8/15/2010)*('Y-2010'!$H$2:$J$5000=D4)*'Y-2010'!$AQ$2:$AQ$5000),(('Y-2010'!$B$2:$B$5000=8/15/2010)*('Y-2010'!$H$2:$J$5000=D4)*'Y-2010'!$AQ$2:$AQ$5000))) จากสูตรนี้ผมใส่ 8/15/2010 ไปแล้วทำไมมัน error หละครับเราต้องใส่ = อะไรครับ ปกติก็ใส่ สัปดาห์ เช่น 33 34 เป็นต้น

[snasui]

คือผมกำลังก้อปปี้ชื่อของ OPT1 OPT2 OPT3 ของวันที่ 15 aug 2010 ซุ่งผมไป rangename แล้ว แต่ปัญหาคือผมต้องมานั่งลบ EN ย่อจาก Employee number นะครับออก เพื่อเอาเฉพะที่ไม่ซำกัน มีคำแนะนำไหมครับว่ามีวิธีที่ excel ทำได้ โดยไม่ต้องมี remove duplicate ซึ่งข้อมูลมีในลักษณะ array ครับ

การ Remove Duplicate หรือเลือกมาเฉพาะ Unique นั้นสามารถทำได้หลายวิธีครับ เช่นสามารถใช้ PivotTable ในการ Remove ค่าที่ซ้ำออกไปได้ สามารถใช้การ Conso ก็ได้ ใช้ฟังก์ชั่นก็ได้ ดูที่นี่ครับ http://snasui.blogspot.com/2010/05/blog-post.html

AVERAGE(IF((('Y-2010'!$B$2:$B$5000=8/15/2010)*('Y-2010'!$H$2:$J$5000=D4)*'Y-2010'!$AQ$2:$AQ$5000),(('Y-2010'!$B$2:$B$5000=8/15/2010)*('Y-2010'!$H$2:$J$5000=D4)*'Y-2010'!$AQ$2:$AQ$5000))) จากสูตรนี้ผมใส่ 8/15/2010 ไปแล้วทำไมมัน error หละครับเราต้องใส่ = อะไรครับ ปกติก็ใส่ สัปดาห์ เช่น 33 34 เป็นต้น

กรณีข้อมูลจำนวนมากให้ใช้การ Remove Duplicate หรือ PivotTable กรณีต้องการให้ Dynamic ครับ

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

AVERAGE(IF((('Y-2010'!$B$2:$B$5000="8/15/2010"+0)*('Y-2010'!$H$2:$J$5000=D4)*'Y-2010'!$AQ$2:$AQ$5000),(('Y-2010'!$B$2:$B$5000="8/15/2010"+0)*('Y-2010'!$H$2:$J$5000=D4)*'Y-2010'!$AQ$2:$AQ$5000)))

หากเปลี่ยนจากกาีรคีย์วันที่เป็นการอ้างอิงมาจากเซลล์ใด ๆ สามารถใช้ได้เลยครับ