รวม

Tricks DD

ทค่ี วรรู้ เลม่ 3

FREE รวมบทเรยี น Tricks Excel สุดคูล DOWNLOAD จากเพจวศิ วกรสรา้ งภาพ

FREE

DOWNLOAD

แอดมนิ บ๊อบ วิศวกรสรา้ งภาพ

Dollar Sign

$ เคร่อื งหมายมหศั จรรย์ $B$2 เครื่องหมายนม้ี คี วามสาคัญอย่างมากในโปรแกรม B2 F4 B$2 Excel ทาหน้าท่ลี อ็ คเซลลห์ รือชว่ งเซลลเ์ อาไว้ไมใ่ ห้ ขยับเวลาทล่ี าก Copy สูตร เรียกวา่ การใส่สูตร $B2 แบบ Smart คอื ใส่สูตรแคเ่ ซลลเ์ ดยี ว แตส่ ามารถ ทางานได้ทกุ เซลลน์ นั่ เอง

ไมใ่ ส่เลย =B2 ใส่ทงั้ หน้า B และหน้า 2 =$B$2

ไมล่ ็อคท้งั คอลัมน์และแถว ล็อคทั้งคอลมั นแ์ ละแถว

ใส่หน้า B เทา่ นน้ั =$B2 ใส่หนา้ 2 เทา่ นั้น =B$2

ล็อคคอลมั น์อยา่ งเดยี ว ล็อคแถวอยา่ งเดียว

Page: 2

ส่ือ ความหมาย

ผ่าน icon

.xlsx ไฟลเ์ อกสารของ Excel 2007 ขนึ้ ไป ไมส่ ามารถเปิดใน 2003 หรอื ตา่ กว่าได้

.xlsm เปน็ ไฟลท์ มี่ ี Code VBA หรอื Macro ฝังอยู่ หากจะเปิดตอ้ งระวังอันตราย ! จาก Code ทีบ่ างครัง้ อาจทาลายเอกสาร ทงั้ หมดได้เลยทีเดียว

.xlsb ไฟล์ท่ีสามารถบันทึกไดท้ ั้งแบบไมม่ ีหรือมี Code VBA ข้อดคี อื มขี นาดเลก็ แต่ข้อ ควรระวงั คอื หากมี Code Macro เราจะไม่ สามารถรูไ้ ดน้ อกจากจะเปดิ ไฟลข์ นึ้ มาเทา่ นนั้ (อันตรายเหมือนกันนะหากมี Code)

.xls ไฟลร์ ุ่นเกา่ ของ Excel 2003 ลงไป แต่สามารถเปิดไดท้ ุกเวอรช์ นั่ เลยจา้

เปน็ ไฟลข์ อ้ ความ

.csv ประเภทหนึ่งท่ี ใช้สาหรบั เก็บข้อมูล ในรปู แบบตาราง รองรบั การใชง้ านกบั โปรแกรมฐานขอ้ มูลต่างๆ ไดห้ ลากหลาย แต่ขอ้ ควร ระวงั กค็ อื มนั มีขนาดใหญ่ นะครับ

Page: 3

เทคนคิ การ Fill Down

เตมิ ขอ้ ความให้เหมอื น ดา้ นบน งา่ ยๆ

กดปุ่ม

ลากคลมุ พ้ืนท่ีทั้งหมด

เรียบรอ้ ยครบั เลอื ก Special 4

Page: 4 3 ติก๊ Blank

พิมพ์เครอ่ื งหมาย 5

\=6 คลิก OK

และกดลกู ศรขน้ึ 1 คร้ัง

< สุดทา้ ย กด 2 ปมุ่ น้พี รอ้ มกนั

Ctrl Enter

เทคนิค การทา Data Validation Drop-Down List

เป็นอีกหนึ่งเครอ่ื งมือพื้นฐานทผี่ ใู้ ชง้ าน Excel นา้ ยาลา้ งจาน จาเป็นต้องเรียนรู้ เพราะมปี ระโยชน์มากๆ ในการบนั ทกึ ข้อมลู ในลักษณะของงานทตี่ ้องกรอกข้อมูลซา้ ๆ หรือชอ่ื ผงซักฝอก ยาวๆ ด้วยวิธีการพิมพ์ซงึ่ มโี อกาสผดิ พลาดสูงมาก ยาสีฟนั ปญั หานปี้ ้องกันได้โดยการทา Drop-Down List ครับ น้ายาลา้ งจาน แชมพู นา้ ยาปรบั ผ้าน่มุ

1 ไปทเี่ มนู Data

คลกิ ท่ีเซลล์ทีจ่ ะทา 2 Drop-Down เลอื ก Data Validation

เรยี บรอ้ ยแลว้ ครับ =$B$3:$B$7

เลอื ก List

5 คลิก OK

เลอื ก ช่วงเซลลท์ ี่ 6 ตอ้ งการ

Page: 5

เทคนิคง่ายๆ การสร้าง Pop-Up Picture

เมื่อนา ไปช้ที ขี่ อ้ ความ 3

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

1 คลกิ ขวาทเี่ ซลล์

2 คลิกขวาทบ่ี รเิ วณ เลือก Format กรอบ Comment Comment เลอื ก Insert Comment 4

เลือก Colors and Lines เลือก Picture

5 8 6 10 คลิก Drop-Down ตรง Color เลือกรูปภาพที่ 9 ต้องการ

Select Picture 11

12 Insert

7 13 เรยี บร้อยครับ

เลอื ก Fill Effects

Page: 6

วธิ กี ารบนั ทึก เวลา

ท่ถี ูกต้อง ในโปรแกรม Excel

11.20 11:20

.ใช้เคร่อื งหมาย :ใชเ้ ครื่องหมาย

หาก บันทกึ รูปแบบเวลาไมถ่ ูกตอ้ ง หาก บนั ทกึ รปู แบบเวลาถูกต้อง เมอื่ เรา บวกลบเวลา เราจะได้ ผลลพั ธ์ เมอื่ เรา บวกลบเวลา เราจะได้ ผลลพั ธ์ ที่ ไม่ถูกต้อง ไปดว้ ย ที่ ถกู ต้อง งา่ ยๆ แบบนี้

Page: 7

สูตร ตัดเกรด

เปรยี บเทยี บ 3 ฟงั ก์ชนั

IF IFS VLOOKUPApproximate Match

Excel 2007 Up Excel 2019, Microsoft 365 Excel 2007 Up

\=IF(F3<50,"F", =IFS( F3<50,"F", =VLOOKUP(F3, IF(F3<60,"D", F3<60,"D", $B$3:$C$7,2,1) IF(F3<70,"C", F3<70,"C", IF(F3<80,"B", F3<80,"B", "A")))) F3<=100,"A")

Page: 8

กาหนด สีตัวเลข

ตามเงือ่ นไข ใน Custom Format

กดป่มุ เข้าเมนู Format Cell

[Blue]#,###;[Red]-#,###;0;

1 พิมพ์สูตรในช่อง Type: 4 ลากคลุมช่วงเซลล์ 3 บวก [+] ใหเ้ ปน็ สีน้าเงิน ลบ [-] ใหเ้ ป็น สีแดง เลือก Custom

คลิก OK

Page: 9

รวมมติ ร การเขียนสูตร Date Function Group

อกี หนง่ึ กลุม่ ฟงั ก์ชันท่ีสาคัญมากๆ ในโปรแกรม Excel ท่ีคู่ควร แกก่ ารสละเวลาศึกษาเรยี นรูเ้ ปน็ อย่างยิ่ง เพราะฟงั กช์ ันกลมุ่ นี้จะ ช่วยใหเ้ ราสามารถสรา้ งงานตา่ งๆ ทเ่ี กีย่ วข้องกบั เรื่องของวนั หรอื Time Period ไดอ้ ย่างงา่ ยดาย

หาเลข ปี หาเลข เดอื น หาเลข วนั

หา 5 วนั ถดั ไปไม่รวม Sat-Sun หา 5 วนั ถดั ไปไม่รวม Sat-Sun

และวนั หยดุ พิเศษ หา 5 วนั ถัดไป ไมร่ วม Sun หา 5 วันถดั ไป

ไมร่ วม Sun และวันหยุด พิเศษ

หา วันสุดท้ายของเดอื น หา วันสุดทา้ ยของเดอื นก่อนหน้า หา วนั สุดท้ายของเดอื นถดั ไป

แปลงเปน็ วันที่ 1 ของเดือน แปลงเปน็ เดอื นคกู่ ับปี แปลงเปน็ เดอื นคู่กบั ปีแบบเลขไทย แปลงเปน็ วันเดือนปีแบบเลขฮนิ ดูอารบกิ

หา ลาดับวันของสัปดาห์ แปลง ลาดับวันเป็นชอ่ื วันของสัปดาห์

Page: 10

เปลีย่ นรูปแบบตวั เลข

อารบิก เลขไทย

กดปุม่ เขา้ เมนู Format Cell

[$-D07041E]#,###,

0

1 พิมพ์ Code ในชอ่ ง Type:

ลากคลมุ ชว่ งเซลล์ 4

เรยี บรอ้ ยครบั

เลือก Custom

คลกิ OK

Page: 11

TEXT CODE EN-DATE

สาหรับเปลยี่ นวนั ที่ ให้เป็น ชอื่ ยอ่ ภาษาอังกฤษ เสมอ

แม้วา่ เครอื่ งจะตงั้ คา่ แสดงผลเปน็ ภาษาไทยก็ตาม

เอาไว้ใช้กรณที ีต่ ้องส่งไฟล์ =TEXT(B3,"[$-409]mmm yy") ใหก้ บั คนอนื่ ปอ้ งกนั ไมใ่ ห้ รปู แบบวนั ที่แสดงผลเป็น ช่ือเดือนภาษาไทยครบั

บางเคร่อื งจะตงั้ ค่าแสดงผลวันท่ี เปน็ ภาษาไทยแบบนี้

\=TEXT(B3,"mmm yy")

หากใส่ Code ปกตจิ ะ แสดงผลเป็นชือ่ เดอื น ภาษาไทยแบบนค้ี รับ

Page: 12

เปรยี บเทียบขอ้ มลู

MTD / QTD / YTD

MTD Month to Date เปรยี บเทยี บ เดือนตอ่ เดอื น

ระยะเวลาตงั้ แต่ วันแรกของเดือนปจั จบุ นั จนถึงวนั ปจั จบุ นั

\=C4-C3 สมมตุ ิวา่ วนั นีค้ อื 31 Aug 2021 MTD2021-MTD2020 = -100

QTD Quarter to Date =SUM(K4:M4) เปรียบเทียบ

\=SUM(K5:M5) ไตรมาสต่อไตรมาส

ระยะเวลาต้งั แต่ วันแรกของไตรมาสปจั จบุ ัน จนถงึ วันปัจจบุ นั

\=N5-N4 สมมุตวิ ่าวันนค้ี อื 30 Sep 2021 (Q3) QTD2021-QTD2020 = +100 YTD Year to Date เปรยี บเทียบ \=SUM($C$3:C3) =C6-C5 ตงั้ แต่ต้นปถี งึ \=SUM($C$4:C4) วนั ปัจจบุ ัน

Page: 13 ระยะเวลาตั้งแต่ เร่ิมต้นปี (หรือปีงบประมาณ) จนถงึ วนั ปัจจุบนั

สมมุตวิ า่ วนั นคี้ อื 31 Oct 2021 YTD2021-YTD2020 = +400

เลอื ก Sheets ทต่ี ้องการ

ง่ายๆ By Activate Index

คลิกขวา

แล้วเลอื ก Sheet ทตี่ ้องการได้เลยครับ

บริเวณนี้ *เหมาะสาหรับลักษณะไฟล์งานทมี่ ี Sheets จานวนมากๆ

Page: 14

เปล่ียน รูปแบบวันที่

จาก TEXT ให้กลาย เปน็ DATE Format ด้วย สูตร

เม่ือเจอ รปู แบบวันที่ แบบน้ี

(มจี ดุ คัน่ วัน.เดือน.ปี ไว้)

Excel จะถอื ว่า

เป็น TEXT =VALUE(SUBSTITUTE(B3,".","/"))

Date Format

Page: 15

ใส่เครื่องหมาย °C Degree ใหต้ วั เลข Temperature \=C3&CHAR(176)&" C"

*ฟงั ก์ชัน CHAR(176) จะแสดงเครื่องหมาย Degree Symbol (°C)

Page: 16

วธิ ีเขยี นสูตรหา

Cumulative Sum

ผลรวมสะสม ง่ายๆ

1 เรียกฟงั ก์ชัน Sum และคลิกเซลล์ 2 ใส่เครื่องหมาย : แลว้ คลิกที่เซลล์ แรกท่ีจะรวมพรอ้ มล็อคเซลล์ดว้ ย $ แรกทจ่ี ะรวมอกี คร้งั แตค่ ราวนีไ้ ม่ ทงั้ Row และ Column ต้องล็อคเซลล์

3 เขยี นสูตรเสรจ็ แลว้ Enter 4 ลากสูตร Copy จากเซลล์แรก ทเ่ี ขยี นสูตร ลงมาดา้ นลา่ งไดเ้ ลย

Page: 17

เทคนิคเขียนสูตร

นบั คา ที่ประกอบด้วย ตัวอกั ษร ท่ีตอ้ งการ งา่ ยๆ \=COUNTIF(B3:B7,"*b*")

Page: 18

เปลี่ยนหน่วย ตวั เลขเปน็

ลา้ นบาท พร้อมใส่ตวั อกั ษร MB

13 #.00,, "MB"

Ctrl 1 4

22 5

“ มลู ค่าจริงยงั เปน็ 23,830,578 นะครบั “

Page: 19

สูตร Distinct Count

นบั ไม่ซ้า เปรียบเทยี บ 2 วธิ ีการ

All Version Microsoft 365 Only

SUMPRODUCT COUNTA

C+ OUNTIF U+ NIQUE

\=SUMPRODUCT( =COUNTA(UNIQUE(B3:B10)) 1/COUNTIF(B3:B10,B3:B10)) Enter Enter

Page: 20

สร้างรหสั Quarter

จากวันทีง่ ่ายๆ

\="Q"&CEILING.MATH( MONTH(B3)/3)

Page: 21

สูตร คานวณภาษี

เงินได้บคุ คลธรรมดา

แบบขั้นบันได 0-35% ง่ายๆ

\=E4-D5 =G2 \=MIN(C5,E4) \=D5*F5

\=SUM(D5:D12) =SUM(G5:G12)

Page: 22

เทคนคิ เขียนสูตร

Get fiscal quarter from Date Start Q1 Apr

\="Q"&CHOOSE(MONTH(B3), 4,4,4,1,1,1,2,2,2,3,3,3)

เร่มิ ตน้ Q1 เดอื น Apr นะครบั

Page: 23

เทคนคิ เขยี นสูตร

Get Month Name from Date แบบ Special

\=TEXT(B3-20,"mmm")

เร่ิมต้นเดือนเป็นวันที่ 21

21 Jan – 20 Feb = Jan 21 Feb – 20 Mar = Feb 21 Mar – 20 Apr = Mar 21 Apr – 20 May = Apr 21 May – 20 Jun = May 21 Jun – 20 Jul = Jun 21 Jul – 20 Aug = Jul 21 Aug – 20 Sep = Aug 21 Sep – 20 Oct = Sep 21 Oct – 20 Nov = Oct 21 Nov – 20 Dec = Nov 21 Dec – 20 Jan = Dec

Page: 24

ใส่เลข 0 ขา้ งหน้า ตัวเลข ตามหลกั ท่ีต้องการ

Ctrl 1 4

22 พิมพ์เลข 0 ตามจานวนหลกั 00000 ที่ตอ้ งการ

เรยี บรอ้ ย

Page: 25

เคลด็ (ไม)่ ลับ ของ Format Painter

ถ้าคลิก Format Painter ครัง้ เดียว

กจ็ ะ Paint Copy ไดแ้ ค่ 1 ครัง้ เทา่ นน้ั !

2 1 3

แต่ถ้า Double คลกิ Format Painter

จะสามารถ Paint Copy หลายครงั้ ไปเรอ่ื ยๆ ได้

2 1 35 ลองดนู ะ ครบั 46

Page: 26

รวมตัวเลข หลาย Sheets

ทเี่ ซลล์อยู่ ตาแหนง่ เดยี วกัน

3-D Reference

ชอื่ Sheet แรก

ชอื่ Sheet สุดทา้ ย

เซลลแ์ รกท่จี ะรวม

\=SUM('001:005'!C5)

Page: 27

สูตรหาคา่ ต่าสุด

ไม่สนใจ ค่าติดลบ และ ค่า 0

All Version Excel 2019 Up Microsoft 365

ARRAY* เวอรช์ ่นั เก่าใชแ้ บบนน้ี ะครบั MINIFS INDEX Formula \=MINIFS(C3:C9, X+ MATCH ** ตอนเขยี นสูตรไมต่ อ้ งพิมพ์ { } นะ C3:C9,">0") Exact match or next ครบั เดยี๋ วมันจะปรากฏขนึ้ มาเองตอน larger item กด 3 ปุ่มดา้ นล่างน้ี Enter \=INDEX(C3:C9, {=MIN(IF(C3:C9>0, XMATCH(0.00001, C3:C9))} C3:C9,1))

Ctrl Shift Enter Enter

“ไม่สนใจค่าลบ และค่า 0“

MIN

Page: 28

Format ทแ่ี ทจ้ รงิ ของ วันท่ี คอื ตัวเลข

ข้อควรรู้

Excel เร่มิ นบั 1 วันท่ี 1 January 1900

เปน็ เวลากวา่ 44,000 วนั แล้วที่ Excel นับวนั ให้เรา

มาจนถึงปัจจุบัน

Page: 29

คดิ ราคาส่วนลด %

เขียนสูตร อย่างไรดี ?

แบบที่ 1 แบบที่ 2 แบบท่ี 3

\=B3-(B3*25%) =B3*75% =B3*0.75

“ แอดมินชอบ เขียนแบบน้ี “

เหลอื เพียง จากราคาเตม็ 200,000 150,000

เทา่ น้ัน

Page: 30

SUMIFS หาผลรวม

รูปแบบต่างๆ อยา่ งมเี งื่อนไข

ยอดขายตาม สินคา้

1 เงอื่ นไข

\=SUMIFS(E3:E12, D3:D12,H2)

ยอดขายตาม สินค้าและภูมภิ าค 2 เงอื่ นไข

\=SUMIFS(E3:E12, D3:D12,H2, C3:C12,H3)

ยอดขายรวม รายเดือน ทางานรว่ มกับ EOMONTH

\=SUMIFS(E3:E12, B3:B12,">="&H2, B3:B12,"<="&EOMONTH(H2,0))

Page: 31

สูตร กรองขอ้ มูล

ตามเงอ่ื นไข เปรียบเทยี บ 2 วธิ กี าร

All Version Microsoft 365 Only

* เวอรช์ นั่ เก่าใช้แบบนี้นะครบั

Array FILTERFormula

{=IFERROR( =FILTER($C$3:$C$12, E$2=$B$3:$B$12,"") INDEX($C$3:$C$12, Enter SMALL(IF(E$2=$B$3:$B$12,

** ตอนเขยี นสูตรไมต่ ้อง ROW($B$3:$B$12)-2), พิมพ์ { } นะครับเดี๋ยวมนั จะปรากฏขึ้นมาเองตอน ROW()-2)),"")} กด 3 ปมุ่ ดา้ นลา่ งนี้

Ctrl Shift Enter

Page: 32

2-Ways LOOKUP

เทคนิค คน้ หา 2 ทาง

All Version INDEX Microsoft 365

V Exact Match +MExact Match XLOOKUP LOOKUP ATCH +MExact Match X+ LOOKUP ATCH +MExact Match ATCH

\=VLOOKUP(H5, =INDEX(C5:F9, =XLOOKUP(H5,B5:B9, B5:F9,MATCH(I5, MATCH(H5,B5:B9,0), XLOOKUP(I5, B4:F4,0),0) MATCH(I5,C4:F4,0)) C4:F4,C5:F9))

Page: 33

เชื่อมคา เปรยี บเทยี บ 3 วธิ ี

แบบ มชี อ่ งว่างคนั่ “”

& CONCATENATE TEXTJOIN Excel 2007 Up ใช้ & เชือ่ มโดยไม่ใช้ฟงั กช์ ัน Excel 2019, Microsoft 365

\=B3&" "&B4&" “ =CONCATENATE( =TEXTJOIN(

&B5&" "&B6&" “ B3," ",B4," ",B5, " ",TRUE,B3:B7)

&B7 " ",B6," ",B7)

Ctrl

Page: 34

หาค่าสูงสุด (หรือตา่ สุด)

แบบมเี งอ่ื นไข

All Version Excel 2019, Microsoft 365

Array* เวอร์ช่ันเกา่ ใช้แบบน้นี ะครบั MAXIFS Formula \=MAXIFS(D3:D11,C3:C11,F3) {=MAX(IF(C3:C11=F3,D3:D11))} Enter ** ตอนเขียนสูตรไม่ตอ้ งพิมพ์ { } นะครับ เดี๋ยวมนั จะปรากฏขน้ึ มาเองตอนกด 3 ปุ่ม ดา้ นล่างน้ี

Ctrl Shift Enter

MAX

Page: 35

เปลี่ยน Date เป็น

Days of Week

วิธีที่ 1 วิธที ่ี 2 วธิ ีท่ี 3

ปรบั Format Cell TEXTใช้ฟงั กช์ นั C ใช้ฟงั ก์ชนั HOOSE Ctrl 1 W+ EEKDAY

ddd =TEXT(B3,"dddd") =CHOOSE( \=TEXT(B3,"ddd") WEEKDAY(B3), "Su","Mo","Tu", Custom "We","Th","Fr", "Sa")

*ใชก้ รณที ่ตี อ้ งการ ช่ือย่อตามท่ีกาหนดเอง

Page: 36

แยก Date กับ Time

ออกจากกนั งา่ ยๆ

Date Time

Format Cell: dd/mm/yyyy Format Cell: hh:mm

\=INT(B3) =B3-D3

Page: 37

IF AND OR

ฉนั (และ) หรอื เธอ ท่ีเปล่ียนไป

AND OR

\=IF(AND( =IF(OR( C2="ไม่เปลี่ยน",C3="ไมเ่ ปลย่ี น"), C2="ไมเ่ ปลย่ี น",C3="ไมเ่ ปลีย่ น"), "ยงั รกั กันอยู่","ไมร่ กั กนั แลว้ ") "ยังรักกนั อยู่","ไม่รกั กันแล้ว")

AB A B

ดีกันนะ เตง

AB “ ขอเพียงเรา 2 คน ไม่เปลยี่ นไป ยงั ไงเรากจ็ ะยงั รกั กนั เหมอื นเดมิ และ ตลอดไป… “

Page: 38

สูตร LOOKUP

จาก ขวา มาซา้ ย

Exact Match

V V ILOOKUP Exact Match

LOOKUP NDEX +C +MHOOSE Exact Match

ATCH

\=VLOOKUP(I3, =VLOOKUP(I3, =INDEX(C3:C7, B3:G7,2,0) CHOOSE({1,2}, MATCH(I3,G3:G7, G3:G7,C3:C7), 0)) จะไดผ้ ลลัพธเ์ ป็น 2,0) ค่า