R102 Work Automation and Vlookup with R

การใช้โปรแกรม R ในการทำงานแบบเป็นอัตโนมัติ และการ Vlookup ในแบบโปรแกรม R

Written by Siwasit Jitpanu 20/7/2021

บทความนี้เราจะแนะนำวิธีการใช้โปรแกรม R ในการทำงานแบบเป็นอัตโนมัติ ตั้งแต่การจัดการรูปแบบการทำงาน จนไปถึงเทคนิคที่ช่วยให้เพื่อนๆสามารถแบ่งปันโปรแกรม R ที่เขียนให้คนอื่นใช้ได้ง่าย และการ Vlookup ในแบบโปแกรม R ทำง่ายๆเพียงไม่กี่ขั้นตอน ถ้าพร้อมแล้ว ลองทำตาม กันเลย

Package ที่ใช้ในบทความนี้ (openxlsx และ dplyr)

เพื่อนๆ สามารถเข้าไปดูวิธีติดตั้ง และเรียกใช้งาน Package ได้ที่นี่ คลิก

คำสั่งติดตั้ง และเรียกใช้งาน Package openxlsx และ dplyr

install.packages("openxlsx", dependencies = TRUE)
library(openxlsx)
install.packages("dplyr")
library(dplyr)

ข้อมูลที่ใช้กับบทความนี้

Master.xlsx ใช้เป็นฐานข้อมูลสามารถ Download ได้ที่นี่ คลิก
Data.xlsx ใช้เป็นข้อมูลตั้งต้นสำหรับ Vlookup ข้อมูลที่ต้องการจากไฟล์ Master สามารถ Download ได้ที่นี่ คลิก

1. การใช้งานโปรแกรม R ในการทำงานแบบอัตโนมัติ

1.1 การจัดการ Folder เพื่อความสะดวกในการใช้งานโปรแกรม R

รูปแบบการทำงาน

ในการทำงานจริงเราจะมีขั้นตอนในการทำงานง่ายๆ ในลักษณะนี้

วางไฟล์ Data ใน Folder Input
วางไฟล์ Master ใหม่(กรณีมีการอัพเดท Master)ใน Folder Master
Run ภาษา R(EP4.R) ที่เขียนคำสั่งตามขั้นตอนการทำงาน Excel ผ่าน Run.bat
ได้ไฟล์ที่ผ่านกระบวนการจัดการแล้วใน Folder Output

(Tip : การจัดการ Folder ในลักษณะนี้สามารถแบ่งปันให้เพื่อนไปใช้งานได้เพียงแค่ ติดตั้งโปรแกรม R ไว้ในเครื่อง และการแก้ไขไฟล์ Run.bat)

คำอธิบายในแต่ละส่วน

Folder Input คือ Folder สำหรับวางไฟล์ข้อมูลดิบที่ต้องการนำไปใช้สรุป ซึ่งมีกระบวนการทำงานใน Excel ที่ชัดเจนทำซ้ำบ่อยๆ เช่น สรุปข้อมูล ทุกวัน ทุกเดือน

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

Folder Output คือ Folder ที่จะ Export ไฟล์สรุปที่ผ่านกระบวนการทำงานของโปรแกรม R เรียบร้อยแล้ว

R File(EP4.R) คือ ภาษา R ที่เขียนคำสั่งให้จัดการไฟล์ตามรูปแบบการทำงานใน Excel

Run.bat คือ ไฟล์ชุดคำสั่งที่ใช้เพื่อ Run ภาษา R(R File : EP4.R) ใน Folder

1.2 ไฟล์ Run.bat

วิธีการสร้างไฟล์ Run.bat

เปิดโปรแกรม Notepad
พิมพ์คำสั่ง
- @echo off
- "C:\Users\siwasit.j\Documents\R\R-4.1.0\bin\x64\Rscript.exe" "%~dp0\*.R"
File > Save As...
ตั้งค่า File name: เป็น Run.bat
ตั้งค่า Save as type: เป็น All File

* C:\Users\siwasit.j\Documents\R\R-4.1.0\ คือ Directory ที่ติดตั้งโปรแกรม R ไว้ซึ่งจำเป็นต้องเปลี่ยนตามเครื่องที่ใช้งาน

(Tip1 : เมื่อมีการติดตั้งโปรแกรม R Version ใหม่ต้องมาแก้ Directory ด้วยหากต้องการใช้โปรแกรม Version ใหม่ในการ Run)

(Tip2 : เพื่อป้องการการลืมว่าไฟล์ Run.bat นี้ใช้ R Version อะไรสามารถตั้งชื่อโดยวงเล็บ Version ไปในชื่อได้ด้วย เช่น Run(R4.1.0).bat)

(Tip3 : Run.bat นี้สามารถ Copy ไปใช้กับโปรเจค R อื่นๆในเครื่องเพียงจัด Folder ในลักษณะเดียวกัน)

1.3 เทคนิคการ Read, Write ไฟล์จาก Folder โดยไม่กำหนด Directory ตายตัว

การใช้เทคนิคนี้ในการทำงาน จะดีกว่าการกำหนด Directory แบบตายตัว คือสามารถแบ่งปันให้คนอื่นนำไปใช้ได้เพียงแค่วางไฟล์ข้อมูลใน Input และไฟล์สรุปที่ผ่านการจัดการเสร็จแล้วจะอยู่ใน Output โดยไม่ต้องไปแก้คำสั่งในโปรแกรม R

* การใช้เทคนิคนี้เราจะต้อง Save ไฟล์ R ไว้ในที่เดียวกับ Folder Input และ Output

1.3.1 วิธีการ Read ไฟล์ใน Folder Input

คำสั่งที่ใช้

setwd("Input")
fname <- list.files(getwd())
Data <- read.xlsx(fname)
setwd("..")

คำอธิบายคำสั่ง

setwd คือ การเข้าไปใน Folder นั้นๆ

setwd("..") คือ ออกกลับออกมาจาก Folder นั้นๆ

getwd() คือ การแสดง Directory ปัจจุบัน

list.files คือ เก็บค่าชื่อไฟล์ทั้งหมดใน Directory

หลักการทำงาน

เข้า Folder Input
เก็บค่าชื่อไฟล์ใน Folder Input ไว้ในตัวแปล fname
Read ไฟล์ Excel จากชื่อ fname
ออกมาจาก Folder Input

1.3.2 วิธีการ Write ไฟล์ไว้ใน Folder Output

คำสั่งที่ใช้

setwd("Output")
write.xlsx(Data,paste0("Final_",fname))
setwd("..")

คำอธิบายคำสั่ง

setwd คือ การเข้าไปใน Folder นั้นๆ

setwd("..") คือ ออกกลับออกมาจาก Folder นั้นๆ

paste0 คือ ต่อชื่อไฟล์

หลักการทำงาน

เข้า Folder Output
Write ข้อมูล Data เป็นไฟล์ Excel โดยต่อชื่อ Fianl_fname (เช่น fname=data ไฟล์ที่ออกมาคือ Final_data.xlsx)
ออกมาจาก Folder Output

2. การ Vlookup ในโปรแกรม R

คำสั่ง left_join

2.1 คำสั่งที่ใช้ใน Vlookup โดยเลือกข้อมูลจากชื่อ Column

คำสั่ง left_join(Data,Master[c("A","B","C")],by="A")

Data คือ ชุดข้อมูลตั้งต้นเพื่อหาข้อมูลจาก Master มา Vlookup

Master คือ ชุดฐานข้อมูลที่จะไปดึงข้อมมูลมาจับคู่กับ Index ใน Data

"A" คือ ชื่อ Column ที่ใช้เป็น Index ในการ Vlookup

"B","C" คือ ชื่อ Column ที่จะนำข้อมูลมา

ตัวอย่างการ Vlookup

Data <- left_join(Data,Master[c("Material","Value","Stock")],by="Material")

และกด ctrl+enter หรือ คลิกที่ run

เราจะได้ข้อมูล Data ที่ดึงข้อมูลใน Columns "Value" และ "Stock" ตาม Index("Material") จากข้อมูลใน Master

2.2 คำสั่งที่ใช้ใน Vlookup โดยเลือกข้อมูลจากตำแหน่งของ Column

คำสั่ง left_join(Data,Master[c(1,2,3)],by="A")

Data คือ ชุดข้อมูลตั้งต้นเพื่อหาข้อมูลจาก Master มา Vlookup

Master คือ ชุดฐานข้อมูลที่จะไปดึงข้อมมูลมาจับคู่กับ Index ใน Data

1 คือ ตำแหน่งของ Column Index ในการ Vlookup

"A" คือ ชื่อ Column ที่ใช้เป็น Index ในการ Vlookup

2,3 คือ ตำแหน่งของ Column ที่จะนำข้อมูลมา

ตัวอย่างการ Vlookup

Data <- left_join(Data,Master[c(1,4,5)],by="Material")

และกด ctrl+enter หรือ คลิกที่ run

เราจะได้ข้อมูล Data ที่ดึงข้อมูลใน Columns ที่ 4 และ 5 ตาม Index(Column ที่ 1 หรือ "Material") จากข้อมูลใน Master

3. ตัวอย่างการใช้งานโปรแกรม R แบบเป็นอัตโนมัติ

ไฟล์ตัวอย่างในบทความนี้

สามารถ Download ไฟล์ทั้งหมดได้ที่นี่ คลิก

โจทย์ : ต้องการข้อมูล Value และ Stock ของ Material ในไฟล์ Data

เมื่อ Download แล้วเพื่อนๆ ลองทำตามขั้นตอนดังนี้

เปลี่ยน Directory ที่ติดตั้งโปรแกรม R ในไฟล์ Run.bat โดยการเปิดแก้ไขด้วย Notepad
จัดวาง Folder ตามรูปแบบที่แนะนำ
Double Click ไฟล์ Run.bat และดูไฟล์สรุปใน Folder Output

เพียงเท่านี้เพื่อนๆก็ได้ไฟล์ที่ต้องการตามโจทย์แล้ว เพื่อนๆ สามารถประยุกต์ใช้คำสั่ง R เพื่อสรุปข้อมูล และลดข้อผิดพลาดในการทำงานในโปรแกรม Excel

Page updated

Google Sites

Report abuse