2  คีย์ลัดทีน่าสนใจ สำหรับการใช้งาน EXCEL

ไฟล์สำหรับฝึกปฎิบัติ คือ “diamonds.xlsx” นศ. สามารถ download ได้จาก google drive diamonds.xlsx

คำถาม สำหรับการฝึกนี้

  1. แถวสุดท้ายอยู่บรรทัดที่เท่าไหร่?

  2. จงขึ้นมาบรรทัดแรกให้เร็วที่สุด

  3. ต้องการ copy ตารางนี้ทั้งหมดไปที่ sheet2

  4. ต้องการ copy ข้อมูลเฉพาะตัวแปร carat color clarity depth table price และ y

ความเป็นมืออาชีพ

ในการทำงานจริงๆ นศ.จะใช้วิธีการใดก็ได้ ในการทำงาน เพื่อให้ได้ผลลัพธ์ที่ต้องการ แต่ถ้านศ.รู้เทคนิคเล็กๆ น้อยๆ เหล่านี้แล้ว ย่อมสร้างความประทับใจให้เพื่อนร่วมงาน รุ่นพี่ และเจ้านายได้ไม่น้อยเลยทีเดียว

2.1 คีย์ลัดสำหรับการแก้ปัญหานี้

คีย์ลัด

Crtl+\(\rightarrow\) ไปที่ขวาสุดของตารางข้อมูล

Crtl+\(\leftarrow\) ไปที่ซ้ายสุดของตารางข้อมูล

Crtl+\(\uparrow\) ไปที่บนสุดของตารางตารางข้อมูล

Crtl+\(\downarrow\) ไปที่ล่างสุดของตารางข้อมูล

Crtl+A ไฮไลท์ตารางข้อมูลทั้งหมด

Crtl+C คัดลอกข้อมูลในเซล หรือที่ไฮไลท์ไว้ทั้งหมด

Crtl+V วางข้อมูลที่คัดลอก ลงในเซลที่ต้องการ หรือจะใช้การกดปุ่ม enter ในเซลที่ต้องการวางก็ได้

เมื่อไฮไลท์ และส่วนใดที่ไม่ต้องการกด Crtl ค้างไว้ แล้วนำเม้าส์ไปเลือกในเซล คอลัมภ์ หรือแถวที่ไม่ต้องการออก แล้วจึงค่อยคัดลอกและวางในเซลที่ต้องการตามปกติ

  1. จงลบข้อมูลตั้งแต่แถวที่ 201 ลงไปทั้งหมด (ประยุกต์ใช้ คีย์ลัดด้านบนโดยมีต้องกดปุ่ม Shift ร่วมด้วย เช่น Crtl+Shirt+\(\rightarrow\))

  2. จากข้อ 5 จงปริ้นตารางออกมาโดยให้ทุกหน้าต้องปรากฏหัวตาราง (หรือชื่อตัวแปรที่บรรทัดแรกทุกหน้า)

ขั้นตอนการทำ

1) เลือกเมนู Page Layout

2) เลือกเมนู Print Titles

กำหนดค่าตามรูป แล้วเลือก print previews หรือ print หรือ OK ก็ได้
  1. จงจัดขนาดความกว้างของตารางให้พอดีดังภาพด้านล่างจากไฟล์ ToothGrowth.xlsx จาก googledrive

ตารางที่มีความกว้างไม่พอดี

ขั้นตอนวิธีการทำ

ขั้นที่ 1 ไฮไลท์คลุมทั้งตาราง

นำเม้าไปวางเส้นขอบขวาของคอลัมภ์ A และกด double click และ นำเม้าไปวางเส้นขอบล่างของแถวที่ 1 และกด double click

ผลลัพธ์ที่ได้

2.2 ฟังก์ชันใน EXCEL ที่ควรทราบ

เปิดไฟล์ “EconSale.xlsx” จาก Link googledrive

ข้อมูล EconSale

คำถามที่ 1 ถ้า Snack มีราคา unit ละ 80 บาท Coffee มีราคา unit ละ 90 บาท และ Water มีราคา unit ละ 45 บาท จงสร้างตัวแปร ชื่อ price per unit เพื่อแสดงราคาของสินค้าแต่ละชนิด

คำสั่ง IF()

คำสั่ง IF ใน EXCEL เป็นฟังก์ชันที่ใช้ในการตรวจสอบเงื่อนไขและทำการคืนค่าผลลัพธ์ตามเงื่อนไขที่กำหนด ฟังก์ชันนี้มีรูปแบบดังนี้:

=IF(logical_test, value_if_true, value_if_false)

  • logical_test: เป็นเงื่อนไขที่ต้องการตรวจสอบ ซึ่งสามารถเป็นการเปรียบเทียบ เช่น A2 > 10, B2 = "Yes", หรือ C3 <= D4

  • value_if_true: เป็นค่าที่จะคืนกลับเมื่อเงื่อนไขเป็นจริง

  • value_if_false: เป็นค่าที่จะคืนกลับเมื่อเงื่อนไขเป็นเท็จ

    การทำงานของฟังก์ชัน IF()

การใช้งานฟังก์ชัน IF สามารถช่วยในการวิเคราะห์ข้อมูลและแสดงผลลัพธ์ตามเงื่อนไขที่กำหนด ทำให้การทำงานกับข้อมูลใน EXCEL มีความยืดหยุ่นมากขึ้น

2.2.1 ตัวดำเนินการเปรียบเทียบ

  • = : เท่ากับ

  • <> : ไม่เท่ากับ

  • > : มากกว่า

  • < : น้อยกว่า

  • >= : มากกว่าหรือเท่ากับ

  • <= : น้อยกว่าหรือเท่ากับ

สามารถวาดได้เป็น flowchart ได้ดังนี้

Flowchart ของการใช้ IF ที่มีหลายเงื่อนไข

คำสั่ง IF()
=IF(C2="Snack",80,IF(C2="Water",45,90))
การคัดลอก สูตรไปจนสุดตาราง

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

  1. สร้างตัวแปรใหม่ ชื่อว่า value และค่าในตารางคือ ค่าในตัวแปร unit คูณกับ price per unit

สูตรการคูณ
=D2*E2

หมายเหตุ กรณีที่เงื่อนไขตั้งแต่ 4 เงื่อนไขขึ้นไป แนะนำให้ให้ใช้คำสั่ง IFS() แทน จะง่ายกว่า

2.3 ฟังก์ชัน IFS()

ฟังก์ชัน IFS() ใน Excel เป็นฟังก์ชันที่ใช้สำหรับการตรวจสอบหลายเงื่อนไขในคราวเดียว ฟังก์ชันนี้เป็นการผสานการตรวจสอบเงื่อนไขหลายๆ อย่างเข้าในฟังก์ชันเดียว ทำให้การเขียนสูตรมีความสะดวกและเข้าใจง่ายขึ้นเมื่อเปรียบเทียบกับการใช้ฟังก์ชัน IF() ซ้อนกันหลายชั้น

โครงสร้างของฟังก์ชัน IFS() มีดังนี้:

IFS( logical_test1, value_if_true1, 
     logical_test2, value_if_true2, 
     …,
     logical_testk, value_if_truek)
  • logical_test1: เป็นเงื่อนไขแรกที่ต้องการตรวจสอบ
  • value_if_true1: เป็นค่าที่จะคืนกลับถ้าเงื่อนไขแรกเป็นจริง
  • logical_test2: เป็นเงื่อนไขที่สองที่ต้องการตรวจสอบ (ไม่จำเป็น)
  • value_if_true2: เป็นค่าที่จะคืนกลับถ้าเงื่อนไขที่สองเป็นจริง (ไม่จำเป็น)
  • … สามารถมีได้หลายเงื่อนไข

ตัวอย่างการใช้งาน

2.3.0.1 ตัวอย่างที่ 1:

การให้คะแนนตามคะแนนสอบ:

ในตัวอย่างนี้:

  • ถ้าคะแนนมากกว่าหรือเท่ากับ 90 จะให้ค่าเป็น “A”

  • ถ้าคะแนนมากกว่าหรือเท่ากับ 80 แต่ไม่ถึง 90 จะให้ค่าเป็น “B”

  • ถ้าคะแนนมากกว่าหรือเท่ากับ 70 แต่ไม่ถึง 80 จะให้ค่าเป็น “C”

  • ถ้าคะแนนมากกว่าหรือเท่ากับ 60 แต่ไม่ถึง 70 จะให้ค่าเป็น “D”

  • ถ้าคะแนนน้อยกว่า 60 จะให้ค่าเป็น “F”

แน่นอนครับ ด้านล่างนี้คือตัวอย่างข้อมูลคะแนนของนักเรียน 10 คน พร้อมสูตรการใช้ฟังก์ชัน IFS() เพื่อกำหนดเกรด:

ตารางข้อมูลนักเรียน

ลำดับ ชื่อ คะแนน
1 สมชาย 95
2 สมหญิง 85
3 สมศักดิ์ 72
4 สมร 65
5 สมหมาย 50
6 สมพร 88
7 สมใจ 93
8 สมบูรณ์ 77
9 สมคิด 82
10 สมศรี 55

ผลลัพธ์เมื่อใช้ฟังก์ชัน IFS() ตามสูตรที่ให้ไปในแต่ละแถว เราจะได้ผลลัพธ์ดังนี้:

=IFS(C2<60,"F",
     C2<70,"D",
     C2<80,"C",
     C2<90,"B",
     C2>=90,"A")
ลำดับ ชื่อ คะแนน เกรด
1 สมชาย 95 A
2 สมหญิง 85 B
3 สมศักดิ์ 72 C
4 สมร 65 D
5 สมหมาย 50 F
6 สมพร 88 B
7 สมใจ 93 A
8 สมบูรณ์ 77 C
9 สมคิด 82 B
10 สมศรี 55 F

ตัวอย่าง IFS() ใน EXCEL

ตารางนี้แสดงถึงการใช้ฟังก์ชัน IFS() ในการตรวจสอบและกำหนดเกรดตามคะแนนที่นักเรียนแต่ละคนได้รับครับ

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

3.สร้างตัวแปร Month เพื่อ บันทึกเดือนที่ขายได้ จากตัวแปร date

คำสั่ง TEXT()

คำสั่ง TEXT() ใน EXCEL ใช้สำหรับการจัดรูปแบบข้อมูลตัวเลขหรือวันที่ตามรูปแบบที่กำหนด โดยมีรูปแบบดังนี้:

=TEXT(value, format_text)
  • value: ค่าที่ต้องการจัดรูปแบบ (เช่น เซลล์ที่มีข้อมูลวันที่หรือตัวเลข)
  • format_text: รูปแบบที่ต้องการแสดงผล

ตัวอย่างการใช้งานคำสั่ง TEXT() เพื่อแสดงผลวัน เดือน หรือ ปี มีดังนี้:

  1. แสดงผลวัน (Day)

    • แสดงเฉพาะวันเป็นตัวเลข (เช่น 1, 2, 3, …, 31)
=TEXT(A2, "d")
-   แสดงวันเป็นเลขสองหลัก (เช่น 01, 02, 03, ..., 31)
=TEXT(A2, "dd")
-   แสดงวันเป็นชื่อย่อ (เช่น Mon, Tue, Wed, ...)
=TEXT(A2, "ddd")
-   แสดงวันเป็นชื่อเต็ม (เช่น Monday, Tuesday, Wednesday, ...)
=TEXT(A2, "dddd")

  1. แสดงผลเดือน (Month)

    • แสดงเฉพาะเดือนเป็นตัวเลข (เช่น 1, 2, 3, …, 12)
=TEXT(A2, "m")
-   แสดงเดือนเป็นเลขสองหลัก (เช่น 01, 02, 03, ..., 12)
=TEXT(A2, "mm")
-   แสดงเดือนเป็นชื่อย่อ (เช่น Jan, Feb, Mar, ...)
=TEXT(A2, "mmm")
-   แสดงเดือนเป็นชื่อเต็ม (เช่น January, February, March, ...)
=TEXT(A2, "mmmm")

  1. แสดงผลปี (Year)

    • แสดงปีเป็นเลขสองหลัก (เช่น 21 สำหรับปี 2021)
=TEXT(A2, "yy")
-   แสดงปีเป็นเลขสี่หลัก (เช่น 2021)
=TEXT(A2, "yyyy")

ตัวอย่างการใช้งาน: - ถ้าเซลล์ A2 มีค่าเป็นวันที่ 1 ม.ค. 2024: - =TEXT(A2, "dddd") จะให้ผลลัพธ์เป็น “Monday” - =TEXT(A2, "mmm") จะให้ผลลัพธ์เป็น “Jan” - =TEXT(A2, "yyyy") จะให้ผลลัพธ์เป็น “2024”

คำสั่ง TEXT() ช่วยในการจัดรูปแบบข้อมูลตามความต้องการ เพื่อให้การแสดงผลเป็นไปตามรูปแบบที่ต้องการใน EXCEL

ตัวอย่างการใช้ คำสั่ง TEXT()
  1. ยอดขายรวมทั้งหมด และยอดขายรวมเฉพาะ product เป็นอย่างไร ให้สร้างตารางนี้ แยกออกเพื่อหาผลรวม

ตารางผลรวม
คำสั่ง SUM() และ SUMIF()

คำสั่ง SUM() และ SUMIF() ใน EXCEL เป็นฟังก์ชันที่ใช้ในการรวมค่าต่าง ๆ ตามที่กำหนด โดยมีความแตกต่างกันดังนี้:

คำสั่ง SUM()

ฟังก์ชัน SUM() ใช้ในการรวมค่าตัวเลขในช่วงเซลล์ที่กำหนด รูปแบบคำสั่งคือ:

=SUM(number1, number2, ...)
  • number1, number2, ... เป็นค่าหรือช่วงของเซลล์ที่ต้องการรวม

ตัวอย่างการใช้งาน:

  1. รวมค่าจากเซลล์ F2 ถึง F10
=SUM(F2:F10)
  1. รวมค่าจากเซลล์ F2, F3 และ F5
=SUM(F2, F3, F5)

คำสั่ง SUMIF()

ฟังก์ชัน SUMIF() ใช้ในการรวมค่าตัวเลขในช่วงเซลล์ที่กำหนด โดยพิจารณาตามเงื่อนไขที่กำหนด รูปแบบคำสั่งคือ:

=SUMIF(range, criteria, [sum_range])
  • range: ช่วงของเซลล์ที่ต้องการตรวจสอบเงื่อนไข
  • criteria: เงื่อนไขที่ใช้ในการตรวจสอบเซลล์ในช่วง range
  • [sum_range] (ถ้ามี): ช่วงของเซลล์ที่ต้องการรวมค่าถ้าเซลล์ในช่วง range ตรงตามเงื่อนไข ถ้าไม่ได้ระบุ จะใช้ช่วง range เป็นช่วงที่จะรวมค่า

ตัวอย่างการใช้งาน:

  1. รวมค่าจากเซลล์ F2 ถึง F10 ที่มีค่าใหญ่กว่า 2000
=SUMIF(F2:F10, ">2000")
  1. รวมค่าจากเซลล์ F2 ถึง F10 ที่ตรงกับเงื่อนไขในช่วงเซลล์ C2 ถึง C10 ที่เท่ากับ “Snack”
=SUMIF(C2:C10,"=Snack",F2:F10)

ฟังก์ชัน SUMIF() เหมาะสำหรับการรวมค่าที่ต้องการตามเงื่อนไขที่กำหนด ช่วยในการคำนวณและสรุปข้อมูลได้อย่างแม่นยำและรวดเร็ว

สูตรการคำนวณ
=SUMIF(C2:C153,"Coffee",F2:F153)

สูตรการคำนวณอีกแบบ
=SUMIF($C$2:$C$153,J3,$F$2:$F$153)

หมายเหตุ การตรึงค่าเซล์ที่ต้องการอ้างอิง ด้วยเครื่อง \$ สามารถไฮไลท์ ข้อความที่ต้องการก่อนแล้วปุ่ม F4 จนกว่าจะได้การตรึงค่าที่ต้องการ

การหาผลรวมทั้งหมด ในแถวสุดท้าย สามารถคีย์ลัดคือ Alt+= หรือ เลือกจาก เมนู Home แล้วไปที่รูป AutoSum ก็ได้

\(\displaystyle\sum AutoSum\)
  1. จงเปลี่ยนตารางนี้

ตารางต้นแบบ

ให้เป็น

ตารางใหม่
ภาคบ่าย

ปัญหานี้ สามารถทำได้ง่ายถ้าใช้การทำ Pivot Table

2.4 การแทนค่าช่องว่างโดยใช้ ปุ่ม F5 และเมนู Fill

ขั้นตอนการทำแทนค่าช่องว่าง

ขั้นที่ 1 ไฮไลท์ตารางทั้งหมดที่ต้องการแทนค่า

ขั้นที่ 2 กดปุ่ม F5 \(\rightarrow\) Go To Special \(\rightarrow\) Blanks \(\rightarrow\) OK

ขั้นที่ 3 กำหนดเซล M3 ให้เท่าค่าเท่ากับเซล M2 แล้วกด enter

ขั้นที่ 4 กดไปที่ เมนู Fill ที่อยู่ใต้ AutoSum เลือก Down
  1. สร้างตารางมีประกอบด้วยรายชื่อ Sale รายการ product และยอดขายแยก ตามประเภท และสรุปด้วยยอดขายรวมทั้งหมดอีกดังนี้
คำสั่ง SUM() และ SUMIF()

ฟังก์ชัน SUMIFS() ใน EXCEL ใช้ในการรวมค่าตามหลายเงื่อนไขที่กำหนด โดยจะรวมเฉพาะค่าที่ตรงกับทุกเงื่อนไขที่ระบุไว้ ฟังก์ชันนี้เป็นการขยายความสามารถจาก SUMIF() ที่รองรับเพียงเงื่อนไขเดียว

2.4.1 รูปแบบคำสั่ง SUMIFS()

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range: ช่วงของเซลล์ที่ต้องการรวมค่าหากเงื่อนไขทั้งหมดตรงกัน
  • criteria_range1: ช่วงของเซลล์ที่จะตรวจสอบเงื่อนไขแรก
  • criteria1: เงื่อนไขแรกที่ต้องการให้ตรง
  • [criteria_range2, criteria2], ...: ช่วงของเซลล์และเงื่อนไขเพิ่มเติม (สามารถเพิ่มได้หลายคู่)

ตัวอย่างการใช้งาน SUMIFS()

รวมค่าตามหลายเงื่อนไข

เซล Best สินค้า Coffee

=SUMIFS($F$2:$F$153,$B$2:$B$153,M2,$C$2:$C$153,N2)

ฟังก์ชัน SUMIFS()

ข้อควรระวัง - ช่วงของเซลล์ใน sum_range และ criteria_range ต้องมีขนาดเท่ากัน - ฟังก์ชัน SUMIFS() สามารถใช้ได้กับหลายเงื่อนไขและหลายช่วง ทำให้สามารถปรับใช้ในการคำนวณตามเงื่อนไขที่ซับซ้อนได้อย่างยืดหยุ่น

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