Category: Spreadsheet

ใช้ Excel แยก ชื่อ ชื่อกลาง นามสกุล

สิ่งที่ผมมักพบบ่อย ๆ เลย clean data ที่ได้มาทำต่อ ก็มักจะเป็น คนที่ให้ข้อมูลเรามา ใส่ข้อมูล 2 หรือ 3 อย่างไว้ใน cell เดียวกัน 😆

แน่นอนครับ คนเราส่วนมากไม่รู้ตั้งแต่แรกหรอกว่า ควรจะทำ data normalization 😲 ก่อนเก็บข้อมูล

[26 ม.ค. 2566] แยกชื่อ-สกุลด้วยสูตร ⬅️อ่านจากลิงค์นี้

โดยเฉพาะชื่อ-สกุลของคน มาหลากหลายรูปแบบมาก ดังนี้เราจะค่อย ๆ ดูไปทีละแบบเท่าที่เคยเจอมาก็แล้วกัน

😁 เริ่มจากกรณีที่เข้าใจว่า ง่ายที่สุดก่อนก็แล้วกัน

กรณีแรก: ชื่อ-สกุล ไม่มีคำนำหน้า

ชื่อ-สกุล	Full Name
ฐิติสร เสถียรไทย	Thitisorn Sathianthai
แกล้วกล้า วิจิตรพงศ์พันธ์	Klaew-kla Vijitpongpun
ธนุธรรม แสนสุรินทร์	Tanutam Sansurin
ปุรันต์ โรจนสุขชัย	Puran Rojjanasukchai
โมกขเวสา กาสันต์	Mokkhavesa Gason
บุปกรณ์ พญา	Buppakorn Phaya
ภรา พิชิต	Phara Pichit
ธนศร กาญจนพาสน์	Tanusorn Kanjanapas
ฐิติวุฒิ แก้วมณี	Thitiwut Kaewmanee
ทรงพล เตมีรักษ์	Songpol Temirak
ปมุต ศุภรัตน์	Pamut Suparat
ทัดพงศ์ สิทธิราษฎร์	Tadpong Sitdhiras
ปวรุต ประเวศ	Pawarut Praves
ปัณณธร พฤกษาพรรณ	Pannathorn Prugsanapan
เนติวิทย์ มณีรัตนา	Netiwit Maneerattana
อรุณ ชื่นจำรัส	Aroon Cheenchamras
เจริญ ศุภชัย	Charoen Supachai
ถวัลย์ วิจิตรพงศ์พันธ์	Thawan Vijitpongpun
ปัทมา ทับชุมพร	Pattama Thabchumpon

กรณีแบบนี้คุณต้องขอบคุณ คนที่ให้ข้อมูลมาเป็นอย่างมาก 🥰 ถือว่า แทบไม่ต้องทำอะไรเพิ่มเลย

มี 2 ทางเลือกใช้เราใช้

Text to Column (กด Alt ➡ A ➡ E) หรือไปที่แถบ Data > Text to Column
ใช้สูตร

Text to Column

เพิ่มคอลัมภ์เปล่าด้านขวามาอีก 1 คอลัมภ์ หรือ 2 คอลัมภ์ หากมีชื่อกลาง
เลือกคอลัมภ์ “ชื่อ-สกุล” แล้วไปที่แถบ Data > Text to Column หรือกดปุ่ม Alt ▶ A ▶ E
ที่ตัวเลือก เลือกเป็น “Delimited”
เลือกประเภทของ ตัวคั่นข้อมูล (Delimit) ที่เราต้องการ ซึ่งกรณีนี้คือ ช่องว่าง (Space) และอย่าลืม ติ๊ก ที่ Treat consecutive as one เผื่อกรณีที่มี space มากกว่า 1 คั่นระหว่างชื่อ-สกุล แล้วกดปุ่ม Next
เลือกประเภทข้อมูล ซึ่งในที่นี้เป็น “ชื่อ-สกุล” เลือกเป็น Text และเลือก cell ที่เราต้องนำข้อมูลไปใส่ (หลังคำว่า Destination) ในตัวอย่างผมใส่ cell เดิม คือ A1
หากคุณ เลือกให้ข้อมูลทับจุดเดิม จะมีกล่องข้อความเตือนแสดงขึ้นมา ถ้าแน่ในให้ตอบ 🆗 ไป

เลือกประเภทของ ตัวคั่นข้อมูล (Delimit) ที่เราต้องการ ซึ่งกรณีนี้คือ ช่องว่าง (Space) และอย่าลืม ติ๊ก ที่ Treat consecutive as one เผื่อกรณีที่มี space มากกว่า 1 คั่นระหว่างชื่อ-สกุล

เลือกประเภทข้อมูล และที่ที่จะให้เขาข้อมูลไปใส่ (หลังคำว่า Destination)

หากคุณเลือกให้ใส่ผลลัพธ์ในช่องที่มีข้อมูลอยู่ จะมีกล่องข้อความเตือนแสดงขึ้นมา เลือก 🆗 ถ้าไม่มั่นใจก็ Cancel แล้วไปเลือก Destination ใหม่นะ 😅

จะสังเกตได้ว่าถึงจะมี “คำนำหน้า” มาด้วย แต่มีการใช้ space หรือช่องวางคั่นไว้ ก็สามารถใช้ Text to Column เช่นกัน

ใช้สูตรแยก ชื่อ-สกุล

สำหรับการใช้สูตร (formula) จากตัวอย่างเดิม ผมแทรกคอลัมภ์ เพิ่มขึ้นมา B ใช้เก็บชื่อ ส่วน C ใช้เก็บนามสกุล ดังภาพ

การแยกชื่อ-สกุล โดยใช้สูตร โดยใน B3 จะเป็นสูตรที่ใช้ใน B2 และ C3 เป็นที่ใช้ใน C2

สำหรับสูตรแยกชื่อ จะเรียกใช้ฟังก์ชันอยู่ 3 ฟังก์ชัน ก็คือ TRIM(), LEFT(), และ FIND()

ซึ่งอัลกอริทึมก็คือ “ให้นับเอาตัวอักขระจากซ้ายมือไปจนกว่าจะเจอช่องว่าง“

สูตรที่ใช้ใน B2 คือ

=TRIM(LEFT(A2,FIND(" ",A2)))

ดังนั้นเราก็เลยฟังก์ชัน LEFT() ซึ่งจะทำหน้าที่เก็บอักขระตั้งแรกตัวแรกไปจนกว่า ฟังก์ชัน FIND() จะพบว่า ช่องว่าง หรือ space อยู่ลำดับที่เท่าไหร่ ซึ่งตรงนี้ใครจะใช้ SEARCH() ก็ได้นะ 😙

และเมื่อได้ข้อความ (ในที่นี้คือ ชื่อ) มาแล้ว เราจะใช้ฟังก์ชัน TRIM() ตัดช่องวาง (space) ส่วนเกินออกไป ไม่ว่าจะอยู่ด้านหน้า ด้านหลัง หรือเกินมาระหว่างคำ (ในภาษาอังกฤษ เพราะภาษาไทยเราไม่ได้เว้นช่อวว่างระหว่างคำ) ก็ตาม ซึ่งถ้าอยู่ระหว่างคำจะเก็บไว้ 1 ช่องว่าง

ส่วนการแยกเอา “นามสกุล” นั้นจะมีอัลกอริทึมกลับกันกับ “ชื่อ” คือ ให้นับเอาอักขระจากขวามือ โดยจำนวนอักขระที่จะเอา เท่ากับความยาวของอักขระทั้งหมดใน cell นั้น ลบด้วยจุดแรกที่พบ ช่องว่าง

สูตรที่ใช้ใน C2 คือ

=TRIM(RIGHT(A2,LEN(A2)-FIND(" ",A2)))

การนับเอาอักขระจากด้านขวามือ เราจะใช้ฟังก์ชัน RIGHT() และมีอีกฟังก์ชันเพิ่มขึ้นมาก็คือ LEN() ซึ่งจะใช้นับจำนวนอักขระที่มีอยู่ใน cell นั้น ๆ และสุดท้ายเราจะใช้ TRIM() ตัดช่องว่างที่เกินมาเหมือนเดิม

กรณีที่สอง: ชื่อ ชื่อกลาง 😮 และสกุล ไม่มีคำนำหน้า

หากทุกคนมีชื่อกลาง เราจะสามารถใช้ Text to Column ได้ แต่ในความเป็นจริง คงไม่เป็นเช่นนั้น ลองดูตัวอย่างนี้

ในกรณีนี้การแยกชื่อกลาง จะมีเงื่อนไขเข้ามาเกี่ยวข้อง

November 11, 2021

การพยากรณ์ หรือประมาณการด้วย Excel
ในงานบริหารจัดการด้านต่าง ๆ มีสิ่งที่ต้องทำควบคู่ไปเสมอคือ การวางแผนงานล่วงหน้า และความแม่นยำในการวางแผนมักจะขึ้นอยู่กับการประมาณการสถานการณ์ล่วงหน้า หรือการพยากรณ์จากข้อมูลเดิมที่เรามีอยู่ ไม่ว่าจะเป็น
- แน้วโน้มทางการตลาดจะเป็นอย่างไร?
- ยอดขายจะเป็นเท่าไหร่?
- สินค้าคงคลังจะเป็นเท่าไหร่?
- และควรจะต้องมีพนักงานฝ่ายต่าง ๆ จำนวนเท่าไหร่?
หรืองานอะไรก็ตาม ซึ่งผู้บริหารไม่ว่าจะระดับไหนก็ต้องวางแผนทรัพยากรล่างหน้าอยู่เสมอ ไม่ว่าจะเป็นการเพิ่มหรือลดก็ตาม

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

ในตอนนี้เราจะขอใช้สูตร Excel มาช่วยในการประมาณการสิ่งที่จะเกิดขึ้น ซึ่งน่าจะช่วยให้เป็นตัวอย่างให้เรานำไปใช้ในงานของผู้อ่านได้

ตัวอย่างที่เราจะใช้ในตอนนี้คือ ประมาณการยอดผู้ติดเชื้อโควิด 19 ในประเทศไทยของเราเอง ซึ่งข้อมูลเราได้มาจาก รายงาน COVID-19 ประจำวัน ของ data.go.th แล้วนำมาสรุปเป็นยอดผู้ติดเชื้อต่อวัน แล้วก็จะได้ไฟล์ตัวอย่าง ซึ่งกดไปดูหรือดาวน์โหลดได้จากลิงค์นี้

ตัวอย่าง Excel worksheet สำหรับประมาณการยอดผู้ติดเชื้อโควิด 19 ต่อวันในประเทศไทย

โดยในไฟล์ตัวอย่าง ค่าในคอลัมภ์ต่าง ๆ จะเป็นตามนี้ ส่วนสูตรคำนวณจะอธิบายเพิ่มเติมอีกที
- คอลัมภ์ A คือ Date ซึ่งก็คือวันที่
- คอลัมภ์ B คือ CasePerDay ซึ่งก็คือ ยอดผู้ติดเชื้อต่อวัน ซึ่งเรารวมมาจากข้อมูลที่ได้จาก data.go.th ในลิงค์ที่ให้ไว้ด้านบน โดยเราสามารถนำข้อมูลยอดผู้ติดเชื้อรายวันของแต่ละวันมาป้อนลงในช่องนี้ได้ เพื่อให้การพยากรณ์วันต่อ ๆ ไปมีความแม่นยำขึ้น
- คอลัมภ์ C คือ LowerConfidenceBound พูดง่าย ๆ ก็คือ ยอดต่ำสุดที่เป็นได้จากการพยากรณ์
- คอลัมภ์ D คือ Forecast หรือเป็นค่าพยากรณ์ที่ได้จากข้อมูลย้อนหลัง
- คอลัมภ์ E คือ UpperConfidenceBound พูดง่าย ๆ ก็คือ ยอดสูงสุดที่เป็นได้จากการพยากรณ์
- คอลัมภ์ F คือ Error ซึ่งจะได้จากการเอายอดผู้ติดเชื้อจริงในวันนั้น ๆ เทียบกับยอดที่พยากรณ์ออกมาและทำเป็นเปอร์เซ็นต์
สูตร Excel สำหรับการพยากรณ์จากข้อมูลเดิมที่เรามีอยู่แล้ว

กราฟตัวอย่างผลประมาณการยอดผู้ติดเชื้อโควิค 19 ในประเทศไทย

จะขอเริ่มจากคอลัมภ์ D หรือ Forecast(CasePerDay) ก่อน โดยผมเริ่มใช้พยากรณ์ในเดือนสิงหา (2564) ที่เซลล์ D580 เพราะอยากรู้สถานการณ์จะรุนแรงขึ้นหรือทุเลาลง ซึ่งสูตรที่ใช้จะเป็น

=FORECAST.ETS(A580,$B$2:$B579,$A$2:$A579,0,1,1)

โดยฟังก์ชัน FORECAST.ETS() จะใช้ได้กับ Excel 2016 เป็นต้นมา ส่วนใครใช้เวอร์ชันเก่ากว่านี้ให้ใช้ FORECAST() ซึ่งใช้ได้เหมือนกัน เพียงแต่ FORECAST() จะไม่ได้ต้องการอาร์กิวเมนต์เพิ่มเติม 3 ตัวหลัง แต่มันมีความแม่นยำน้อยกว่ามาก

เหตุที่ฟังก์ชัน FORECAST.ETS() มีความแม่นยำกว่านั้นเป็นเพราะว่า ให้อัลกอริทึม (algorithm) ที่แตกต่างออกไป โดยใช้หลัการพยากรณ์แบบ AAA (Additive error, Additive trend, and Additive seasonality) ของ Exponential Triple Smooting (ETS) ถ้าอ่านแล้ว งง! ช่างมันครับ😂 เพราะมันเป็นหลักการพยากรณ์ค่าที่คนที่เขียนฟังก์ชัน (ไมโครซอฟต์) เอามาใช้ ซึ่งจะมีหลักการทำงานประมาณนี้

พยากรณ์จากการนำค่าเฉลี่ยแบบถ่วงน้ำหนัก แต่น้ำหนักจะลดลงเรื่อย ๆ แบบเท่าทวีคูณ หากข้อมูลนั้นเก่ากว่า หรือพูดอีกนัยหนึ่งก็คือ จะให้น้ำหนักกับข้อมูลใหม่มากกว่า
หน้า 171 Forecasting: Principles and Pratice, 2013

จากตัวอย่างจะเห็นว่า มีความผิดพลาดเฉลี่ยอยู่ราว ๆ 5% มากที่สุดคือ 14% ส่วนตัวผมถือว่ายอมรับได้นะ สำหรับสถานการณ์แบบโควิด 19 ที่เรา ๆ ไม่สามารถควบคุมตัวแปรไหนได้เลย ซึ่งถ้าหากเป็นการทำงานที่เรามีการควบคุมตัวแปรต่าง ๆ ไว้ คงจะมีความแม่นยำกว่านี้มาก

ไทม์ไลน์ (Timeline)

Timeline ถือว่าเป็นสิ่งสำคัญมากต่อความแม่นยำในการพยากรณ์ของ FORECAST.ETS() และต้องมีขั้นที่เท่ากันเสมอ ไม่ว่าจะเป็นทุกวัน ทุก 3 วัน ทุกสัปดาห์ ทุกเดือน หรืออาจจะเป็นขั้นของจำนวนก็ได้ เป็นต้น ที่สำคัญคือ ขั้นต้องเท่ากันเสมอ ซึ่งในตัวอย่างก็คือ ทุกวัน

ไวยากรณ์ (Syntax)
```
FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
```
- target_date [จำเป็นต้องมี] คือ จุดข้อมูลที่คุณต้องการค่าพยากรณ์ โดยอาจเป็น วันที่ เดือน เวลา หรือตัวเลข แต่ถ้าวันที่ เดือน ปี เวลา หรือตัวเลยนั้น เกิดขึ้นก่อนที่จะมีข้อมูล (พยากรณ์ค่าในอดีต) FORECAST.ETS() จะแสดง error เป็น #NUM!
- values [จำเป็นต้องมี] คือ ชุดข้อมูลในอดีตที่เราจะนำมาใช้พยากรณ์
- timeline [จำเป็นต้องมี] คือ ช่วงเวลาของชุดข้อมูลใน values อย่าลืมนะว่า ขั้นต้องเท่ากันและต้องไม่เป็นศูนย์ แต่ไม่มีความจำเป็นต้องเรียงข้อมูลก็ได้ เพราะฟังก์ชันจะนำไปเรียงเอง
  - ถ้าขั้นของไทม์ไลน์ไม่เท่ากัน FORECAST.ETS() จะแสดง error เป็น #NUM
  - ถ้าในไทม์ไลน์มีค่าซ้ำกัน FORECAST.ETS() จะแสดง error เป็น #VALUE
  - ถ้าขนาดของ range ของ values กับ timeline ไม่เท่ากัน FORECAST.ETS() จะแสดง error เป็น #N/A
- seasonality [ไม่ใส่ก็ได้] ค่านี้จะเป็นตัวเลข
  - ปกติจะเป็น 1 ซึ่งหมายถึงให้ฟังก์ชันคำนวณหาให้
  - แต่ถ้าใส่ 0 คือ ข้อมูลที่ใส่ใน values ไม่มีความเป็นฤดูกาล
  - ตัวเลขนี้ต้องเป็นจำนวนเต็มที่ระบุความเป็นฤดูกาลของข้อมูลที่เราใส่ใน values
  - ค่ามากที่สุดที่ใส่ได้คือ 8760 (จำนวนชั่วโมงใน 1 ปี) ถ้าค่ามากกว่านี้ FORECAST.ETS() จะแสดง error เป็น #NUM
- data_completion [ไม่ใส่ก็ได้] จากที่เคยบอกไปก่อนหน้านี้ว่า FORECAST.ETS() ได้ข้อมูลที่เป็นช่วง ๆ หรือขั้นที่เท่ากัน แต่ FORECAST.ETS() ก็ยอมให้ข้อมูลไม่ครบ หรือหายไปได้ 30% นะ โดย
  - ถ้าใส่ 0 คือ ปรับข้อมูลในช่วง หรือขั้นนั้นให้เป็น 0
  - ถ้าใส่ 1 คือ จะนำที่อยู่ใกล้เคียงมาเฉลี่ย
- aggregation [ไม่ใส่ก็ได้] ใช้กำหนดว่าจะให้ฟังก์ชันเอาข้อมูลที่เกิดในช่วงเวลาเดียวกัน (same time stamp) มาทำอย่างไรดี ซึ่งปกติคือ 0 หมายถึง ให้นำมาเฉลี่ย (Average) ส่วนตัวเลือกอื่น ๆ ได้แก่ นำมาบวกัน (SUM) นับจำนวน (COUNT) นับเฉพาะที่มีข้อมูล (COUNTA) เลือกเอาค่าน้อยสุด (MIN) เลือกเอาค่ามากสุด (MAX) และค่ามัธยฐาน (MEDIAN) ซึ่งก็ต้องใส่เป็นตัวเลข 1,2,3,4,5,6 แล้วแต่จะเลือกใช้
September 3, 2021
สร้าง QR CODE ฟรี โดยใช้ Google Sheets
วันนี้เห็นเข้าสร้าง QR CODE โดยใช้ Google Sheets โดยใช้สูตร แล้วสามารถ copy ไปวางไว้ใน PowerPoint หรือ Word เพื่อใช้งานต่อได้เลย

โดยปกติแล้ว เวลาสร้าง qr code ผมก็มักจะค้นใน Google แหละ และก็ใช้ web service ที่ค้นเจอมา แล้ว download หรือ save ไฟล์ภาพมาใช้งานอีกที ตัวอย่างเว็บที่ใช้บ่อย ๆ ก็จะเป็น https://www.the-qrcode-generator.com

[update – 7 ส.ค. 2564] เราสามารถสร้าง QR code ได้โดยตรงจาก address bar ของ Google Chrome และ Microsoft Edge

วันนี้เห็น YouTuber คนหนึ่งเขาใช้ Google Sheets ทำ และใช้สูตรที่ไม่ได้ซับซ้อนมากนัก ลองดูในวิดีโอลิงค์ด้านล่างนี้ก็ได้

สร้าง QR code จาก Google Sheets

ก่อนจะใช้ Google Sheets แนะนำใช้เว็บเบราว์เซอร์รุ่นใหม่ ๆ หน่อย อย่าง Google Chrome, Microshoft Edge, Firefox หรือ Opera ส่วนผู้ใช้ macOS ก็ใช้ Safari ได้เลย

และสิ่งสำคัญคือ ต้องลงชื่อเขาใช้บัญชีของ Google ด้วย อย่างเช่น Gmail หรือ Google Drive

หากใช้ Google Chrome หรือ Apple Safari สามารถพิมพ์ sheets.new ที่ address bar ได้เลย
```
sheets.new
```
ส่วนเว็บเบราว์เซอร์อื่น ๆ ผมไม่ได้ลองดู อาจจะได้เหมือนกัน โดยเฉพาะคนที่ตั้งค่าการค้นหาจาก google.com ส่วนเสิร์ชเอนจินไม่แน่ใจนะ

ภาพตัวอย่างการสร้างไฟล์ Google Sheets ใหม่จาก address bar ของเว็บเบราว์เซอร์

จากนั้นเราก็จะได้ไฟล์ Sheets ใหม่ขึ้นมาหนึ่งไฟล์ ดังภาพด้านล่าง ซึ่งเราก็สามารถต้องชื่อเป็นชื่อที่เราต้องการได้เลย

ในตัวอย่างผมจะใส่เว็บ url ที่ต้องการสร้าง qr code ใน B1 และใส่สูตรใน A1 ดังภาพ

จากภาพจะเห็น เราจะใช้ function ชื่อ image() ซึ่งใน Excel ไม่มีนะ โดย function จะไปเรียก API ซึ่งเป็น web service ในการสร้างกราฟ (chart) ของ Google อีกที ดูเหมือนจะซับซ้อน 😅 แต่เอาเป็นว่า เราเรียกใช้มันเป็นก็พอ ดังนั้นสูตรที่เราใส่ใน A1 คือ
```
=image("https://chart.googleapis.com/chart?cht=qr&chs=300x300&chl="&B1)
```
จากสูตรจะเห็นว่า ส่วนแรกจะเป็นการเรียกใช้ฟังก์ชัน image() ซึ่งมันต้องการ url ของรูป และในที่นี้เราก็เลยใส่ url ที่บอกให้ Google API สร้างรูป qr code ให้เรา โดย
- url หลักก็คือ https://chart.googleapis.com/chart? ซึ่งหลังเครื่องหมายคำถามเราต้องใส่ประเภท chart หรือกราฟที่เราต้องการลงไป
- ซึ่งในที่นี้คือ cht=qr เพื่อบอกว่า เราอยากได้ chart type ที่เป็นแบบ qr code
- ส่วนถัดไป &chs=300×300 ก็คือบอกว่า เราต้องการ chart size ขนาด 300 x 300 พิกเซล นะ
- และส่วนสุดท้ายก็คือ &chl= จะบอกว่า url หรือข้อความใน qr code นั้นคือคำว่าอะไร ซึ่งในที่นี่เราบอกให้ไปดูใน B1
หากใครต้องการดูไฟล์ตัวอย่าง คลิกที่นี่

ตัวอย่างไฟล์ภาพที่ได้จากการใช้ Google APIs สร้าง QR CODE ได้ฟรี

สร้าง QR code จาก Address bar ของ Chrome และ Microsoft Edge ก็ได้

วิธีผมก็ไม่รู้ว่า Google Chrome กับ Microsoft Edge ใส่มาตั้งแต่เมื่อไหร่นะ แต่ส่วนตัวพึ่งเห็นฟังก์ชันนี้อยู่ใน address bar ของทั้งสองเบราว์เซอร์ เมื่อไม่กี่เดือนที่ผ่านมา

วิธีการก็แสนง่ายดาย ซึ่งก็คือ
1. ไปเข้าเว็บที่เราต้องการสร้าง QR code และ
2. คลิกที่ adress bar ก็จะมีไอคอนที่หน้าตาคล้าย ๆ QR code ปรากฎขึ้นมา
3. คลิกที่ไอคอน Create QR code for this Page
4. คลิกที่ปุ่ม Download
สร้าง QR code โดยตรงจาก address bar ของ Chrome และ Edge

กดปุ่ม Download เพื่อดาวน์โหลด QR code มาไว้ที่เครื่องของเรา
November 5, 2020