ทองจุล ขันขาว

 ไฟล์ CSV (commar seperated value) เป็นไฟล์ชนิดข้อความที่คั่นด้วยเครื่องหมายคอมม่า เราไม่สามารถเปิดได้โดยตรงจาก Excel ต้องนำเข้า ถ้ามีหลาย ๆ ไฟล์ ก็สามารถนำมาต่อกันวิเคราะห์คราวเดียวได้เลย แต่มีข้อแม้ว่า โครงสร้างต้องเหมือนกันด้วย ต่อไปนี้ จะนำไฟล์ข้อมูลจากอินเทอร์เน็ตมาวิเคราะห์ โดยมีหลายไฟล์ ซึ่งแต่ละไฟล์จะมีโครงสร้างเหมือนกัน และเก็บไว้ในห้องเดียวกัน ซึ่ง จะขอแบ่งออกเป็น 2 ตอน คือ นำเข้าและปรับข้อมูลด้วย Power Query และ วิเคราะห์ด้วย PivotTable ซึ่งมีวิธีการดังนี้ การนำเข้าข้อมูลจาก ไฟล์ CSV และปรับข้อมูลด้วย Power Query Microsoft Excel ตั้งแต่รุ่น 2016 เป็นต้นมา และ Excel 365 จะมี Power Query เพิ่มเข้ามาด้วย ทำให้การวิเคราะห์ข้อมูลทำได้สะดวกดีขึ้นมาก เรานำมาใช้ได้ ดังนี้ เปิดโปรแกรม Microsoft Excel   ไปที่  ข้อมูล > จากไฟล์ > จากโฟลเดอร์ เราไม่ใช้ จากข้อความ/CSV เพราะเราจะนำเข้าหลายไฟล์ทีเดียวเลย ไปที่ห้องที่เก็บไฟล์ และเลือก จะมีหน้าจอเกิดขึ้นให้ระบุ โดยมีชื่อไฟล์ csv ในห้องทั้งหมด พร้อมทั้งมีปุ่มด้านล่างให้กำหนดลักษณะการเปิดที่ต้องการ  ให้เลือก > ...

Excel 2019 เพิ่มฟังก์ชันใหม่ หลายฟังก์ชัน ฟังก์ชัน IFS ก็เป็นหนึ่งในนั้น ฟังก์ชัน IFS ของ Excel 2019 ทำงานเหมือนกับ ฟังก์ชัน IF ของ Excel รุ่นก่อนนะครับ แต่ว่าใช้งง่ายกว่า นั่นคือ ใช้กับการกำหนดเงื่อนไข ที่มีหลายเงื่อนไข รูปแบบ IFS(เงื่อนไข 1, เงื่อนไข 1 เป็นจริง, เงื่อนไข 2, เงื่อนไข 2 เป็นจริง, เงื่อนไข 2, เงื่อนไข 2 เป็นจริง,...) ตัวอย่าง ถ้าข้อมูลในเซลล์ B3 เท่ากับ 1 ให้แสดงข้อความว่า อาทิตย์ ที่เซลล์ D3 ถ้าข้อมูลในเซลล์  B3 เท่ากับ 2 ให้แสดงข้อความว่า จันทร์ ที่เซลล์ D3 ถ้าข้อมูลในเซลล์  B3 เท่ากับ 3 ให้แสดงข้อความว่า อังคาร ที่เซลล์ D3 เขียนเป็นสูตร ที่เซลล์ D3 ดังนี้ IFS(ฺB3=1, "อาทิตย์",  B3=2, "อาทิตย์",  B3=3, "อังคาร") แสดงผล ในกรณีที่เซลล์ B3=1 ในกรณีที่ต้องการใส่เงื่อนไขอื่น ๆ หรือที่ไม่มีตามที่กำหนด ให้ต่อด้วยคำว่า TRUE ตามด้วยเครื่องหมายคอมม่า และ ตามด้วยสิ่งที่ให้แสดง เช่น  จากตัวอย่างข้างต้น ถ้าเกิดตัวเลขในเซลล์ B3 ไม่ใช่ 1, 2 หรือ 3 ให้แสดงข้อความว่า ไม่มีในเงื่อนไข เขียนสูตร ดังนี้ IFS(ฺB3=1, ...

โดยปกติ ถ้าเราใช้สูตร Vlookup ก็สามารถข้าม Sheet ได้ โดยการอ้างอิง ชื่อ Sheet!ข้อมูล แต่ถ้าจะใช้ VBA มีหลักการ ดังนี้ ใช้สูตร FormulaR1C1 แทนการใช้ WorksheetFunction เพื่อให้สะดวก ในการเปลี่ยนตำแหน่งเซลล์ สำหรับ lookup_value ให้ Excel หาจำนวนข้อมูล เพื่อการกำหนดขอบเขต  ตัวอย่าง ในตัวอย่างต่อไปนี้ จะสร้างปุ่ม เพื่อเรียกข้อมูลจากอีก Sheet มาวางไว้ในตำแหน่งที่ต้องการ วิธีการ เปิดโปรแกรม Excel ที่ Sheet1  พิมพ์ข้อมูล และสร้างปุ่ม ดังภาพ ที่ Shhet2 พิมพ์ข้อมูล ดังภาพ เขียน VBA code ที่ปุ่ม ดังนี้ Sub Button1_Click() Dim lLastRow As Long With Sheets("Sheet1") lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("B2:B" & lLastRow) .FormulaR1C1 = "=Vlookup(RC1,'Sheet2'!R1C4:R5C5,2,FALSE)" End With End With End Sub หมายเหตุ ค่า RC1 จะเปลี่ยนไปตามการวนรอบ เช่น เป็น R2C1, R3C1, R4C1 ...  เป็นต้น ซึ่งจะทำให้ค่า ที่นำมาเป็นเงื่อนไขการค้นหา หรือ lookup_value เปลี่ยนไปโดยอัตโ...

ติดตั้ง Excel 2016 แล้ว เปิดหน้าจอ VBA แล้ว ภาษาไทยที่เป็นชื่อแผ่นงาน อ่านไม่ออก ทั้ง ๆ ที่ ได้ติดตั้ง Language Pack ที่เป็นภาษาไทยเรียบร้อยแล้ว หน้าจอเมนู ก็เป็นภาษาไทย แต่ หน้าจอ ใน VBA Explorer ไม่ยอมอ่านชื่อแผ่นงาน ที่เป็นภาษาไทย ทำให้การใช้โค้ด  VBA มีปัญหา คือ หาแผ่นงานที่กำหนดชื่อเป็นภาษาไทยไม่พบ

ใน MS Excel เราสามารถสร้างปุ่ม เพื่อคลิก และสั่งให้ทำอะไรได้ ตามต้องการ นอกจากนี้ เรายังสามารถกำหนดชื่อให้แสดงบนปุ่มได้ ปัญหาคือ ชื่อจริงของปุ่ม เมื่อสร้างแล้ว จะหาได้ค่อนข้างยาก

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

สูตร vlookup() มีรูปแบบการใช้ ดังนี้ VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) lookup_value คือ ข้อมูลที่ต้องการให้ค้นหา table_array คือ ตารางข้อมูลที่ไปทำการค้นหา col_index_num คือ ตำแหน่งคอลัมน์ในตารางข้อมูลที่ต้องการให้นำมาแสดงเมื่อพบข้อมูลที่ค้นหาแล้ว ตำแหน่งคอลัมน์แรก คือ 1 range_lookup คือ ลักษณะการค้น      ถ้าต้องการให้ค้นคำที่เหมือนกับคำค้นทุกประการ ให้กำหนดเป็น False หรือ 0      ถ้าให้ค้นแบบใกล้เคียง ให้กำหนดเป็น True หรือ 1 และต้องเรียงข้อมูลในตารางจากน้อยไปหามาก      ถ้าไม่ระบุ Excel จะใช้ถือว่าเป็น true สูตร vlookup() ของ Excel เป็นสูตรที่มีประโยชน์ แต่ก็มีข้อที่ต้องระวังหลายอย่างเหมือนกัน มิฉะนั้น จะไม่แสดงค่าตามที่ต้องการ เช่น อาจจะแสดงค่า  #N/A เป็นต้น ดูตัวอย่างข้างล่างนี้ ท่านทราบหรือไม่ว่า ทำไมสูตร vlookup() จึงไม่แสดงชื่อที่ต้องการ ถ้าพิจารณาจากสูตรที่ใช้  จะเห็นว่า เป็นการค้นหาแบบ exact match คือ เหมือนคำค้นที่ต้องการ คือ ให้แสดงชื่อคนในตาราง D26:J36 ที่ได้คะแ...

โดยปกติ การกำหนดทศนิยม ใช้การกำหนดรูปแบบในเซลล์ ซึ่งมีหลายรูปแบบให้เลือกอยู่แล้ว อย่างไรก็ตาม ถ้าหากมีการใช้สูตร และรวมค่าในช่องแถบสูตร (Formula bar) ซึ่งมีข้อความปนอยู่ด้วย ก็จะไม่สามารถใช้การกำหนดรูปแบบของเซลล์ได้ จะทำอย่างไรดี สูตรที่ต้องการกำหนดรูปแบบ ให้เป็นทศนิยม 2 ตำแหน่ง ในภาพ จะเห็นว่า มีการใช้สูตรในเซลล์ G4 และได้ผลลัพธ์ ค่าร้อยละ 66.6666666666667 เราต้องการให้แสดงเพียงทศนิยม 2 ตำแหน่ง เท่านั้น วิธีการ เนื่องจากไม่สามารถใช้การกำหนดรูปแบบเซลล์ เพื่อกำหนดทศนิยม เป็น 2 ตำแหน่งได้ เพราะมีข้อความปนอยู่ด้วย และนอกจากนี้ สูตร Format() ก็ไม่สามารถใช้ได้ เนื่องจาก ในแถบสูตร หรือ Formula bar ไม่สามารถใช้สูตร หรือ Function ของ VBA ได้ ใช้ได้แต่ Worksheet Function เท่านั้น ทางออกคือ การใช้ TEXT() ซึ่งเป็น Worksheet Function  โดยมีรูปแบบ ดังนี้                                          TEXT(ค่าตัวเลข, รูปแบบ) ฟ้งก์ชัน TEXT สามารถรับรูปแบบได้หลายอย่าง คือ ข้อมูลเดิ...

คงต้องยอมรับกันว่า เรื่องของวันที่ใน Excel ทำให้ปวดหัวอยู่ไม่น้อยทีเดียว วันนึงผมได้ไฟล์จากที่อื่นมาเปิด เพื่อถ่ายโอนเข้าโปรแกรม Access วันที่ของเดิม เขียนว่า 1-มี.ค.-58 ทำไปทำมา กลายเป็น 1/3/1958 พอเป็นภาษาไทย ก็กลายเป็น 1/3/2501 ห่างกัน 57 ปี อย่างนี้ก็ปวดหัวละซี เข้าใจว่า ไฟล๋ของเก่า คงใช้ปฏิทินแบบตะวันตก และพิมพ์เข้าไปแบบ พุทธศักราช ผลที่ได้ก็จึงเป็นปี ค.ศ. ไป เวลาเอามาคำนวณ หรือถ่ายโอน ก็เกิดปัญหาธรรมดา หลักใหญ่ของเรา คือ ต้องกำหนดรูปแบบให้ถูก โดยกำหนดให้ใช้ปีพุทธศักราช และ คลิกการป้อนให้สอดคล้องกัน การตั้งค่าดังกล่าว ให้คลิกเลือกเซลล์ที่จะป้อนวันที่ แล้วกด Ctrl +1 จะเปิดหน้าจอให้จัดรูปแบบเซลล์ ถ้าทำอย่างนี้ได้ จะหมดปัญหาเรื่องวันที่ ไม่มีเรื่องปวดหัวกวนใจ แต่ถ้าเลือกไม่ได้ ปัญหาเข้ามาเอง ก็ต้องจัดการให้อยู่หมัด มีสูตรเกี่ยวกับวันที่ ของ Excel ที่ช่วยแก้ไขสถานการณ์ได้ดีมาก คือ สูตร  Date() เป็นสูตรที่ยืดหยุ่นมาก สูตรคือ Date(year, month, day) ตัวอย่างการใช้ การเพิ่ม เดือน อีก 3 เดือน   คือ  =Date(2015, 2+3,20 จะได้ 20 พฤษภาคม 2558 เพิ่มจำนวนวัน โ...

การถ่ายโอนข้อมูล จาก Excel 2010 ไปยัง Access 2010 สามารถทำได้หลายวิธี เช่น การใช้เมนู ข้อมูลภายนอก หรือ External Data แต่ปัญหาอยู่ที่การจัดโครงสร้างของข้อมูล ถ้าโครงสร้างระหว่าง Excel และ Access ไม่ตรงกัน การถ่ายโอนก็จะทำได้ค่อนข้างลำบาก ทั้งนี้เพราะ การถ่ายโอนข้อมูลจาก Excel ไปยัง Access นั้น หัวตารางของ Excel จะต้องเหมือนกับชื่อ ฟิลด์ในตารางที่จะนำข้อมูลเข้า หรือมิฉะนั้นก็นำเข้าเป็นตารางใหม่ แล้วค่อยจัดการภายหลัง ในตัวอย่างนี้จะเป็นการถ่ายโอนเข้าตารางที่มีอยู่แล้ว ตัวอย่าง ต้องการถ่ายโอนข้อมูล ใน Excel ลงใน ตาราง ของ Access ต่อไปนี้ โครงสร้างของตาราง Access ที่ต้องการถ่ายโอนข้อมูลเข้ามา มีดังนี้ จะเห็นว่า โครงสร้างของข้อมูลใน Excel ไม่เหมือนกับใน Access ซึ่งเป็นเรื่องธรรมดา การถ่ายโอนข้อมูล จาก Excel จึงจำเป็นต้องจัดรูปแบบของข้อมูลใน Excel ให้เหมือนในตาราง ของ Access โดยถ้าจะให้ดี ก็ต้องเพิ่มชื่อหัวตาราง ให้เหมือนกับชื่อฟิลด์ในตารางของ Access ก็จะทำให้การถ่ายโอน สะดวก และง่ายได้ รูปแบบที่ต้องการ คือ ฟิลด์ ID เป็น AutoNumber จึงไม่ต้องนำเข้าจาก Excel วิธีการจัดรูปแบ...

บางครั้งเมื่อมีการให้เลือกข้อมูล จาก Drop down list ใน Excel เราต้องการแสดงสีพื้นของ Drop down list เพื่อให้เห็นชัดเจนว่า เซลล์ใดมีการแลือกข้อมูลแล้ว เซลล์ใดยังไม่มีการเลือก เช่น อาจจะกำหนดสีพื้นของเซลล์เป็นสีเทา แสดงว่ายังไม่มีการเลือกข้อมูล และเปลี่ยนสีพื้นเป็นสีขาว แสดงว่า มีการเลือกข้อมูล แล้ว ดังภาพข้างล่าง นี้ หลักการ สร้าง Drop  down list แล้วจึงไปกำหนดลักษณะ การจัดรูปแบบของเซลล์แบบมีเงื่อนไข และสร้างกฎใหม่ เพื่อกำหนดสีพื้นตามเงื่อนไขที่ต้องการ วิธีการ การสร้าง drop down list สร้างรายการสำหรับเลือก ในตัวอย่างคือ ชื่อของคน กำหนดสีพื้นของบริเวณ ที่ต้องการสร้าง Drop down list เพื่อเลิอกชื่อคน ในตัวอย่าง คือบริเวณ D3:H8 และกำหนดสีพื้นเป็นสีเทา กำหนดเส้นขอบให้มีเส้นขอบล้อมรอบทั้ง 4 ด้าน จะได้ผล ดังภาพ คลิกเลือกเซลล์ D3 เพื่อกำหนดเป็น Drop down list ไปที่ ข้อมูล > การตรวจสอบความถูกต้องของข้อมูล >  ในส่วน อนุญาต ให้เลือก รายการ และ ในส่วน แหล่งข้อมูล ให้ระบุช่วงที่เป็นข้อมูลให้เลือก คือ =$B$3:$B$6 เมื่อคลิก ตกลง จะได้ Drop down list ดังภาพ...