excel – iT Blog

การเตรียมแฟ้มข้อสอบนั้นมี 9 ขั้นตอน

ก่อนหน้านี้ได้พัฒนาระบบข้อสอบแบบออนไลน์ และเตรียมโค้ดสำหรับเผยแพร่ พร้อมกับอธิบายผ่าน comment ในโค้ด และเผยแพร่เป็นรุ่น 2.0 บน github ตามลิงก์ ซึ่งพบว่าการใช้งานโค้ดในโรงเรียน คุณครูจำเป็นต้องมีแฟ้มข้อสอบของตนเองบนเครื่องบริการ เพื่อให้ถูกเรียกใช้ได้ตรงตามเนื้อหาและแผนการสอน เช่น สอนวิชาภาษาอังกฤษก็จะมีข้อสอบเรื่องกริยา สอนวิชาภาษาไทยก็จะมีข้อสอบเรื่องคำที่มักเขียนผิด เป็นต้น ดังนั้น จึงได้เขียนแนะนำขั้นตอนการเตรียมแฟ้มข้อสอบไว้ ดังนี้

การเตรียมแฟ้มข้อสอบนั้นมี 9 ขั้นตอน สำหรับ แบบทดสอบเตรียมสู่อุดมศึกษา
https://github.com/thaiall/code/blob/main/test3.php
มีดังนี้

1. นำแฟ้มข้อสอบตัวอย่าง ไปเปิดบนโปรแกรม Excel ซึ่งง่ายที่สุดสำหรับการใช้เครื่องมือพื้นฐาน
แล้วเลือก Get External Data: From Text และเลือกประเภทแฟ้มเป็น All Files (.)
2. ข้อสอบ ตัวเลือก และเฉลยแต่ละข้อจะอยู่รวมกันใน 1 บรรทัด ซึ่งแยกด้วย Delimited: Tab
แฟ้มข้อสอบโดยปกติมีการ Encoding แบบ UTF-8 ถ้าอ่านไม่ออกให้เปลี่ยนเป็นแบบ 874 : Thai (Windows)
3. ผลการใช้ Delimited จะทำให้แต่ละคอลัม แยกออกจากกันชัดเจน
สามารถตรวจสอบผลการแบ่งคอลัมในส่วนของ Data preview ได้
4. ผลการนำเข้าแฟ้มข้อสอบตัวอย่าง ลงในโปรแกรม Excel สำเร็จด้วยดี
ต่อจากนี้คือการป้อนข้อสอบชุดใหม่ ทั้งคำถาม เฉลย และตัวเลือกจนแล้วเสร็จ
5. บันทึกแฟ้มข้อสอบด้วย Save as และเปลี่ยนชื่อแฟ้ม และ Extension เป็น .php
แล้วกดปุ่ม Save ซึ่งชื่อแฟ้ม และการเปลี่ยน Extension สามารถทำได้บน Explorer
6. เปิดแฟ้มข้อสอบที่บันทึกแล้ว ด้วยการกด Right click บนชื่อแฟ้มใน Explorer
ในเบื้องต้นสามารถเปิดด้วย Notepad เพื่อสำรวจว่ามีข้อสอบชุดที่ได้ปรับปรุงแล้ว
7. ถ้าแฟ้มข้อสอบมีรหัสตัดบรรทัด คือ 13 และ 10 ก็จะเห็นข้อสอบเรียงข้ออ่านง่าย
แต่ถ้าแฟ้มข้อสอบมีรหัสตัดบรรทัด คือ 10 ก็จะเห็นข้อมูลทั้งหมดอยู่ในบรรทัดเดียวบน notepad
ซึ่งโปรแกรม test3.php สามารถนำแฟ้มข้อสอบมาใช้ได้ทั้ง 2 กรณี
8. ถ้าแฟ้มข้อสอบมีรูปแบบที่ถูกต้อง มีชื่อแฟ้ม และ extension ตามที่กำหนด
และเมื่อทดสอบบนเครื่องบริการร่วมกับ test3.php ก็จะปรากฎตัวเลือกมาให้ฝึกทำ
9. เมื่อได้ทำข้อสอบแล้วเสร็จ ระบบจะเก็บผลสอบจำนวน 100 รายการล่าสุด
และสามารถเปลี่ยน URL จาก top=10 เป็น top=100 เพื่อขยายรายการแสดงผลได้
ระบบนี้ผู้สอบทุกคนสามารถเปิดรายงานได้ ดังนั้นการนำไปใช้ในระบบปิดจะปลอดภัยกว่า

โค้ดชุดนี้ถูกใช้งานที่ http://www.thainame.net/quiz/test3.php

กำหนดรูปแบบให้ตัวเลขแบบ leading Zero

มีเพื่อนต้องการแฟ้ม .csv ที่มี leading zero
คือ นำหน้าด้วยเลข 0 หากจำนวนหลักไม่ครบตามต้องการ
เพราะเป็นเงื่อนไขของหน่วยเหนือ เค้าขอมาอย่างไรก็ต้องจัดให้
กำหนดมาให้มีข้อมูล 3 หลัก เช่น 007 ส่งแต่ 7 ไปเค้าก็ไม่ปลื้ม
แต่ข้อมูลในระบบเก็บเป็นเลข 7
เดิมนั้นคุณปอ นำข้อมูลออกจากระบบ (export)
แล้วเขียนในรูปแบบ .htm แต่เปลี่ยนสกุลเป็น .xls อัตโนมัติ เพื่อใช้ใน excel
จากนั้นคุณนอ ก็เปิดใน excel เพื่อประมวลผลอีกซะป๊ะ
แล้วจึง save as เป็น .csv เพื่อนำเข้าระบบ (import) ให้หน่วยเหนือ

สรุปขั้นตอน
1. ข้อมูลเลข 7 ในระบบ ปรับเป็น 007 แล้วส่งเป็น .htm จะพบ 007 ผ่าน table
ไม่พบปัญหา เมื่อแสดงผล 007 ใน browser
2. นำแฟ้ม .htm แปลงเป็น .xls ด้วยการเปลี่ยนนามสกุล เพื่อนำไปเปิดใน excel
ไม่พบปัญหา เพราะแฟ้มทั่วไปเปลี่ยนนามสกุลได้
3. เปิดแฟ้ม .xls ที่มีเลข 007 พบว่า excel ปรับการแสดงผลเป็น 7 ทันที
พบปัญหา ว่าเลข 007 เมื่อแสดงใน excel จะพบเพียงเลข 7
4. เดิมแก้ด้วยการปรับแบบ manual ใน excel
พร้อมไปกับการประมวลผลอื่น แล้วส่งออกเป็น .csv ก็รู้สึกว่าเป็นการทำงานหลายรอบ
คุณนอ บอกคุณปอ ว่าไม่สะดวก ช่วยปรับระบบให้หน่อย

สรุปว่า
ในขั้นตอนที่ 1 เขียนข้อมูลออกจากระบบ
โดยปรับเพิ่ม css เข้าไปใน table เพื่อให้การแสดงผลใน excel ถูกต้อง
ด้วยการเติม css ว่า style=”mso-number-format:000;”
เท่านี้ใน excel ก็จะมองเห็น 000 ตามต้องการแล้ว ซึ่งใช้งานได้กับ excel
เมื่อส่งออกไปเป็น .csv ก็พบว่ารูปแบบ 000 ติดไปด้วย และส่งไปให้หน่วยเหนือได้

ตัวอย่าง source code ใน .xls
<body>
<table>
<tr><td style=”mso-number-format:000;”>0</td></tr>
<tr><td style=”mso-number-format:000;”>000</td></tr>
</table>
</body>

ปล. ข้อมูลเรื่องนี้ได้จากการย้อนรอยกลับขึ้นไปจากตัว excel เองครับ

เพื่อนปรึกษาว่า รับแฟ้ม excel มา แล้วเปิดไม่ได้

1. วันหนึ่ง เพื่อนชื่อนางลำ รับอีเมลจาก เพื่อนชื่อนายบาง
ในอีเมลมีแฟ้มอยู่ 6 แฟ้ม คือ

allsheet.xls
filelist.xml
sheet001.htm
sheet002.htm
stylesheet.css
tabstrip.htm

แล้วนางลำก็ forward mail ถามผมว่าเปิดอย่างไร
พยายามเปิดแล้วก็ไม่ได้
เพราะแฟ้มที่ได้รับมาเป็น excel ก็ใช้ excel รุ่นใหม่แล้ว
นามสกุลก็ชัดเจนว่าเป็น .xls จะเป็นอื่นไปไม่ได้

2. เมื่อเพื่อนชื่อลำสอบถามจากเพื่อนชื่อนายบาง ว่าเปิดไม่ได้สงสัยแฟ้มเสีย
เพื่อนชื่อนายบางก็ส่งแฟ้มกลับมาให้อีกรอบ และเปิดไม่ได้เหมือนเดิม
กรณีแฟ้มเสียจึงไม่น่าจะเป็นไปได้ เพราะส่งอีเมลมาให้ถึง 2 รอบ

3. ผมลองเปิดดู ก็พบกับข้อความว่า
“รูปแบบไฟล์และนามสกุลของ ‘allsheet.xls’ ไม่ตรงกัน
ไฟล์อาจเสียหายหรือไม่ปลอดภัย
อย่าเปิดไฟล์ดังกล่าวถ้าคุณไม่เชื่อถือในแหล่งที่มา
คุณต้องการเปิดไฟล์นี้หรือไม่”

4. ก็ต้องเลือก “ใช่” เข้าสู่ขั้นต่อไป
แต่กลับพบข้อความว่า มีปัญหาเกิดขึ้นระหว่างการโหลดในส่วนต่อไปนี้
แสดงว่าเปิดแฟ้ม .xls จะไปเรียกแฟ้มอื่นมาทำงานด้วย
อยู่ใน allsheet.files\sheet001.htm
และ allsheet.files\sheet002.htm

5. ทำการสร้าง folder ชื่อ allsheet.files
ในห้องเดียวกับที่เก็บแฟ้ม allsheet.xls
แล้วย้าย 5 แฟ้มเข้าไป ยกเว้นแฟ้ม allsheet.xls เข้าห้องที่สร้างขึ้น

6. เปิด allsheet.xls ก็พบว่ามีข้อมูลที่ต้องการ

—
[Download]
สำหรับผู้ที่ต้องการทดสอบ
ผมมีแฟ้มภาพ และแฟ้ม allsheet.xls ให้นำไปลองจัดโครงสร้างเอง
สามารถดาวน์โหลดได้ที่
https://www.facebook.com/groups/thaiebook/664429603707861/
—
[Looking back]
แต่เมื่อคิดย้อนกลับ ว่า [เพื่อนชื่อนางบาง] สร้างแฟ้ม allsheet.xls ขึ้นมาได้อย่างไร
ผมจึงได้ทดสอบสร้างข้อมูลขึ้นมา 2 sheets
แล้ว save as แล้วเปลี่ยนประเภทเป็น Web Page (*.htm;*.html)
แต่บังคับชื่อแฟ้มและ extension ด้วยการพิมพ์ว่า “allsheet.xls”
จากนั้นเพื่อนชื่อนายบาง ก็ส่งอีเมลเป็นแฟ้มทั้งหมดให้นางลำ
โดยส่งแฟ้ม allsheet.xls พร้อมกับแฟ้มทั้งหมดในห้อง allsheet.files
—
จึงเป็นที่มาที่ทำให้ได้จัดโครงสร้างของแฟ้มแบบย้อนกลับ
เพื่อจะได้เปิดแฟ้ม allsheet.xls ตามประสงค์ของเพื่อนชื่อนางลำ
ซึ่งผลที่ได้คือเลือกเปิดแฟ้มนี้ได้ทั้งโปรแกรม excel และ chrome

การใส่ caption ใน proshow producer ต่างกับ gold

การจัดบันทึกใส่กระดาษว่าข้อความใด เริ่มต้นนาทีที่เท่าใด

ครั้งที่แล้ว เด็กที่บ้านต้องการทำ karaoke ส่งคุณครูที่สอน ม.3 บว.

แล้วเครื่องที่มีก็ลง proshow gold ไว้แล้ว

จึงปล่อยให้เด็กใช้โปรแกรมนี้ทำ karaoke

ซึ่งการใส่ข้อความในคลิ๊ป และกำหนดให้ caption ประจำ scene

ให้ปรากฎออกมาแตกต่างกันในแต่ละช่วงเวลา อย่างที่พบใน karaoke

พบว่าต้องทำการตัดคลิ๊ปด้วย trim ออกเป็น scene แล้วใส่ caption

เพราะ proshow gold สามารถใส่ได้คลิ๊ปละ 1 caption เท่านั้น

กว่าจะใส่เข้าไปครบ 28 caption ก็ต้องนั่งบวกลบคูณหารกันนานทีเดียว

เพราะต้องตัดคลิ๊ปในเวลาที่กำหนดให้ได้ถึง 28 คลิ๊ป

แต่ถ้าใช้โปรแกรม proshow producer

แม้เป็น version เก่าก็สามารถใส่ได้หลาย caption ใน 1 คลิ๊ป

แล้วยังกำหนดช่วงเวลาที่จะปรากฎในแต่ละ caption ได้แตกต่างกัน

ถ้าทำ karaoke ด้วย producer ก็จะกลายเป็นเรื่อง จิ๊ป ๆ ไปเลย

ไม่ต้องตัดคลิ๊ป แล้วใส่ caption ไปทีละคลิ๊ปเหมือนใช้ proshow gold

ภาพนี้แสดงการทำงานของ proshow producer 4.0.2437

การคำนวณจึงเป็นเรื่องจำเป็นเพื่อแยกช่วงเวลาของแต่ละ caption ออกจากกัน
และโปรแกรม excel ก็เป็นเครื่องมือที่น่านำมาใช้อย่างยิ่ง

ฺBig Big World : Unlisted : jmEXpfeSQvw

เรื่องราวของช่องว่าง (space)

พอทราบมาว่า มีหน่วยงานราชการบางแห่ง
มอบหมายให้หน่วยงานที่ขึ้นตรง
กรอกข้อมูลใน excel แล้วจัดเก็บเป็น xls หรือ csv
ส่งให้หน่วยต้นสังกัด หรือหน่วยเหนือนำข้อมูลไปรวมกัน
ซึ่งผมก็ใช้เหมือนกัน เพราะง่ายกับผู้ใช้
แต่วันนี้ นำข้อมูลมาใช้ แล้วพบปัญหาในแฟ้ม regist.csv
ดังภาพ เมื่อนำตารางนี้ไปใช้
พบว่าวิชา 000 250 ตอน 1 ไม่มีปัญหา เชื่อมข้อมูลได้ถูกต้อง
แต่วิชา 000 250 ตอน 2 มีปัญหา เชื่อมไม่สำเร็จ
เพราะตอนพิมพ์รหัสวิชา ตอน 2 มีช่องไฟต่อท้ายรหัสวิชา
ที่ถูกคือ “000 250” แต่ไปพิมพ์ “000 250 “
สรุปว่าไปลบช่องไฟที่ต่อท้ายเลข 250 ในตอนที่ 2
ก็จะทำให้ข้อมูลเชื่อมโยงกันได้ปกติ

พบปัญหา The file is corrupt and cannot be opened

เครื่องของเพื่อนเปิดแฟ้ม .xlsx ด้วย excel 2010 ไม่ได้ มีอาการดังนี้
1. download แฟ้มจาก email แล้ว double click ไม่ได้ แต่เปิดผ่าน browser ได้
2. download จากเว็บไซต์ แล้ว double click ก็ไม่ได้
3. แต่ไม่พบปัญหา หากสร้างแฟ้มในเครื่อง และเปิดในเครื่อง

ปัญหาที่พบ คือ เครื่องฟ้องว่า The file is corrupt and cannot be opened
แล้วก็เปิดแฟ้มไม่ได้

วิธีแก้ไขโดยปรับ option บางตัว มี 6 ขั้นตอน ดังนี้
1. Open Excel 2010.
2. Click on File > Options.
3. Select Trust Center > Trust center settings.
4. Select Protected view.
5. Uncheck all the options under Protected View > OK.
6. Restart Excel 2010 and try to open Excel documents.

http://newsandguides.com/solution-the-file-is-corrupt-and-cannot-be-opened-excel-2010/

นับจุดขาวกับจุดดำใน mapping

20 มี.ค.56 ได้รับมอบหมายให้นับจำนวนจุดขาวกับจุดดำ หรือวงโปร่งกับวงทึบ ใน microsoft word จำนวน 748 วง ซึ่งเป็นข้อมูลในแผนที่แสดงการกระจายความรับผิดชอบมาตรฐานผลการเรียนรู้จากหลักสูตรสู่รายวิชา (Curriculum Mapping) ที่ทุกหลักสูตรในประเทศต้องจัดทำ และต้องนับจำนวนจุดขาว กับจุดดำ นั่งทำใจอยู่พักหนึ่งก็เห็นว่าเป็นตาราง และมีรูปแบบตายตัว หากใช้ countif(range,criteria) ใน excel ก็น่าจะช่วยนับได้ง่าย ไม่อย่างนั้นต้องไล่บรรทัดกว่า 46 วิชา และแล้วก็ตัดสินใจคัดลอกข้อมูลตารางใน word ไปวางใน excel เมื่อนับด้วยฟังก์ชันเสร็จแล้วก็ถึงคิวต้องสร้างวงให้ได้จำนวนเท่าเลขที่นับได้ ก็นึกขึ้นได้อีกว่าในการเขียนโปรแกรมด้วย clipper มีฟังก์ชั่นสั่งเขียนซ้ำตัวอักษรได้ แล้วก็พบว่า excel มี rept(text,number_times) ช่วยสั่งเขียนซ้ำได้ จึงทำให้งานที่น่าจะใช้เวลานับชั่วโมง นับเสร็จในเวลาอันรวดเร็ว (อันที่จริงผมสุ่มนับ 2 – 3 column จากทั้งหมด 29 column เพื่อให้แน่ใจว่าคอมพิวเตอร์นับถูก)

แฟ้มตัวอย่างการใช้ 2 ฟังก์ชัน
http://www.thaiall.com/tqf/cpsc/mapping.xlsx

วันนี้พบปัญหา editor ไม่ refresh เมื่อ paste ข้อมูลที่มี chr(10)

line feed was hidden in editplus when i paste data from excel cell

เล่าสู่กันฟัง

– เหตุเกิดจากพัฒนา thaiall.com/research/glossary.php และ glossary.xlsx
– โดยเริ่มทำข้อมูลใน excel 2010 แล้วมีบาง cell ที่ใส่ข้อมูลเป็น \n หรือ chr(10) หรือรหัสตัดบรรทัด
– แล้วคัดลอกทั้งหมดไปวางใน editplus 2 ซึ่งวางข้อมูลทั้งหมดเข้าไปในตัวแปรตัวหนึ่ง
– แล้ว split ด้วย \n ปรากฎว่าผลการแยกไม่ถูกต้อง จึงสงสัยว่าข้อมูลหลังวางแล้วเป็นอะไรกันแน่ใน glossary.php
– เมื่อมองด้วย debug ถึงทราบว่า editplus ไม่ refresh
– ถ้า refresh ก็ต้องมีการตัดบรรทัดหลัง paste ข้อมูลที่มี chr(10) ลงไป
– เมื่อปิดโปรแกรม editplus แล้วเปิดใหม่ ก็พบว่าข้อมูลมีการปัดบรรทัดในตำแหน่งที่สงสัยจริง
– ทำให้ทราบว่าข้อมูลที่ paste จาก excel จะทำให้เกิดการปัดบรรทัด แต่ไม่แสดงผลในทันทีบน editplus 2
– จึงแก้ไขด้วยการเติมอักษรพิเศษที่ cell แรกของระเบียน แล้ว split ตามอักษรพิเศษนั้นแทนการใช้ \n
– ผมเลือก 255 เพราะไม่คิดว่า text ธรรมดาจะต้องใช้อักษรนี้

เพิ่มเติม

เหตุที่ไม่ใช้ระบบฐานข้อมูล ก็เพื่อให้เพื่อน ๆ ที่สนใจข้อมูล สามารถ download glossary.xlsx ไปใช้ต่อยอดได้ การกรอกข้อมูลก็สะดวกผ่าน excel จะย้ายไปประมวลผลบน server ตัวใดก็ทำได้ทันที หรืออาจมีเพื่อนมาช่วยปรับข้อมูลก็สามารถทำได้ง่าย ไม่ต้องคิดเรื่อง import , export ให้ซับซ้อน

There was a problem sending the command

พบข้อความว่า There was a problem sending the command to the program ในโปรแกรม excel 2007 และ 2010 เมื่อ double click บน icon ของ excel file จะต้องใช้วิธี open file ตามปกติจาก menu bar

แก้ไขโดย

1. คลิ๊ก Office button, Excel Options, Advanced

2. คลิ๊ก checkbox หน้าตัวเลือก Ignore Other Applications that use Dynamic Data Exchange (DDE)

http://support.microsoft.com/kb/211494/en-us

macro to copy 3 sheets to sheet1 & sort

25 ม.ค.55 มีโอกาสเขียน macro ใน excel คัดลอกข้อมูลจาก 3 sheets มาต่อกัน แล้วก็จัดเรียงตามวันที่ ซึ่งอยู่ใน column b และ ยังไม่ได้ปรับ code ให้เป็น function ครับ ทำให้ code ค่อนข้างยาว แต่ผมว่าอ่านง่าย ตรงไปตรงมาดี

Sub copy_sheets()
Dim start As String, tr As Integer
start = “a3” ‘ start row 3
tr = 0
‘=========
ActiveWorkbook.Worksheets(“¡”).Select
Set tbl = Range(start).CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count – 2, tbl.Columns.Count).Copy
tr = tr + tbl.Rows.Count
Range(start).CurrentRegion.Copy
ActiveWorkbook.Worksheets(“sheet1”).Select
Range(“a1”).Activate
ActiveSheet.Paste
‘=========
ActiveWorkbook.Worksheets(“¢”).Select
Set tbl = Range(start).CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count – 2, tbl.Columns.Count).Copy
tr = tr + tbl.Rows.Count
ActiveWorkbook.Worksheets(“sheet1”).Select
Range(“a13”).Activate
ActiveSheet.Paste
‘=========
ActiveWorkbook.Worksheets(“¤”).Select
Set tbl = Range(start).CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count – 2, tbl.Columns.Count).Copy
tr = tr + tbl.Rows.Count
ActiveWorkbook.Worksheets(“sheet1”).Select
Range(“a23”).Activate
ActiveSheet.Paste
‘=========
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“B3:B” & tr) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A3:L” & tr)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub