Excel Tips Function

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function

: ABS

ความหมาย

ABS คือ การส่งกลับค่าสัมบูรณ์ของตัวเลข - ส่งค่าตัวเลขที่เป็นลบให้เป็นค่าบวก - delete ( ' ) หน้าตัวเลข - สามารถเปลี่ยนตัวเลขจาก Text ให้มาเป็น Number ได้

ข้อจำากัด

- ไม่สามารถใช้กับข้อมูลที่เป็นตัวอักษรได้

ประโยชน์

- ได้รับข้อมูลที่ถูกต้อง - ถ้าต้องการให้เปลี่ยนตัวเลขจาก ( - ) มาเป็น (+) ก็สามารถทำาได้สะดวก เปลี่ยนจาก ค่า ( - ) มาเป็นค่า ( + )

เปลี่ยนตัวเลข Text มาเป็น Number

Function

Function

=ABS(Number)

=ABS(Number)

=ABS(C19) Input

Total :

-25.20 -600.00 -7,000.56 -50.26 -20.37 -90.60 -7786.99

=ABS(F19)

Output 25.20 600.00 7000.56 50.26 20.37 90.60 7786.99

Input

Total :

02.50 400.00 0001 500.00 2,000.00 1000.00 2,400

Output 2.50 400.00 1.00 500.00 2,000.00 1,000.00 3,904

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function : Concatenate ความหมาย

- การรวมข้อความต่าง ๆ เข้าด้วยกัน (สามารถใช้เครือ่ งหมาย & แทนได้ Function Concatenate)

ประโยชน์

- ได้รบั Data ละเอียดขึ้น - ทำางานได้ถกู ต้องและรวดเร็วขึ้นทางด้านการค้นหาข้อมูล (กรณีใช้สูตร Vlookup ร่วมด้วย) Function

=C ONCATEN ATE( ข้อค วาม 1, ข้อค วาม 2...) Function =CONCATENATE(C13,D13) Input

Customer

Part#

HT LL LT LT LT MC MC

500060002 328541567 328500563 228541568 128541563 222541563 728541569

Function

Output HT500060002 LL328541567 LT328500563 LT228541568 LT128541563 MC222541563 MC728541569

=( ข้อคว าม 1, ข้อค วาม 2… ) =(G13&H13) Input

Customer

Part#

NT OP OP LT PL SH SH

328411563 928541563 628541561 244156325 328541567 528541560 328111560

Output NT328411563 OP928541563 OP628541561 LT244156325 PL328541567 SH528541560 SH328111560

=( ข้ อคว าม 1& "-"& ข้อค วาม 2...) =(C27&"-"&D27) Input

Output

Customer

Part#

HT

500560526

HT-500560526

LL

328541567

LL-328541567

LT

328500563

LT-328500563

LT

228541568

LT-228541568

LT

128541563

LT-128541563

MC

222541563

MC-222541563

MC

728541569

MC-728541569

เงื่อนไข 1. Output = Cus. & Part# 2. ต้องการให้มี ( - ) เพิ่มระหว่าง Cus. และ Part#

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function : Left& Right ความหมาย

- LEFT คือ การตัดจำานวนตัวอักษรทีอ่ ยู่ทางซ้าย Column ตามจำานวนทีก่ ำาหนด - RIGHT คือ การตัดจำานวนตัวอักษรทีอ่ ยู่ทางขวา Column ตามจำานวนที่กำาหนด

ประโยชน์

- ได้รับข้อมูลทีเ่ ราต้องการ และสามารถนำาไปใช้กับสูตรอื่นต่อได้ - สามารถแยกข้อมูลให้เป็นกลุ่มได้ Function Shipment# LL LL LT LT LT MC MC MC LL OP OP PL PL SH SH

=LEFT(ข้อความ,ลำาดับ) =LEFT(D13,2) PartNumber LL-T6510050A00 LL3280UDVS LT161230019 LT16431263 LT16441261 MC182000686005 MC470474104 MC470474106 LL-6300008 OP100000406 OP610000100 PL1612E1230038 PL313914723851 SH5100M3A SH5100M3AS TOTAL :

Std.Cost 1.5000 5.2240 1.3000 1.2230 1.3660 0.2000 0.8900 0.8790 0.5000 0.4000 0.6000 0.2000 1.0000 2.0000 3.0000

Qty. 200 300 50,000 2,000 6,000 4,000 2,000 3,000 2,000 100 200 300 500 6,000 2,000

Total 300.00 1,567.20 65,000.00 2,446.00 8,196.00 800.00 1,780.00 2,637.00 1,000.00 40.00 120.00 60.00 500.00 12,000.00 6,000.00 102,146.20

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function MID

ความหมาย ประโยชน์

- MID เป็น Function ทีส่ ามารถตัดข้อความที่เราต้องการมาใช้ได้ - ได้ข้อความที่ตอ้ งการ และสามารถใช้ร่วมกับสูตรอื่นได้ Function

=MID(T ext ,เร ิ่ มที ่ , ลำาด ับ ) =MID(C10,4,2) Input

Output

S5-04-0023

04

S-04

A5-04-3152

04

A-04

S5-03-0024

03

S-03

A5-04-3155

04

A-04

A4-04-3156

04

A-04

S4-04-0025

04

S-04

A5-03-3161

03

A-03

S4-04-0026

04

S-04

A5-02-3169

02

A-02

กรณีต้องการเพิ่ม ( - ) เข้าไปใน PartNumber =LEFT(C24,2)&(REPLACE(C24,1,2,"-")) LL563982336

LL-563982336

MC58965656

MC-58965656

LT256300562

LT-256300562

=LEFT(C10,1)&"-"&D10

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function LEN

ความหมาย ประโยชน์

- การนับจำานวนตัวอักษรในข้อความ, อักขระ, วรรณยุกต์, สัญลักษณ์และช่องว่าง - รวดเร็วและถูกต้องแม่นยำาในการนับ Digit Function

=LEN (ข้อ คว าม ) =LEN(C10) Input

Output

S5-04-0023

10

Excel Tip

9

ฟั งก์ชัน LEN

12

LEN © LEN

9

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function LOWER & UPPER & PROPER

ความหมาย ประโยชน์ -

LOWER : แปลงข้อควำมให้เป็นตัวพิมพ์เล็กทั้งหมด UPPER : แปลงข้อควำมให้เป็นตัวพิมพ์ใหญ่ทั้งหมด PROPER : เปลี่ยนตัวอักษรแรกในแต่ละคำำเป็นตัวพิมพ์ใหญ่ สำมำรถแก้ตัวอักษรได้รวดเร็ว และถูกต้องแม่นยำำ

Set ตัวอักษรให้เป็นตัวพิมพ์เล็กทัง้ หมด

Set ตัวอักษรให้เป็นตัวพิมพ์ใหญ่ทั้งหมด

เปลี่ยนตัวอักษรแรกในแต่ละคำำให้เป็นตัวพิมพ์ใหญ่

Function

Function

Function

Input

=LOWER(ข้อความ) =LOWER(C13) Output

Input

=UPPER(ข้อความ) =UPPER(F13) Output

Input

=PROPER(ข้อความ) =PROPER(I13) Output

BASIC & ADVANCE

basic & advance

basic & advance

BASIC & ADVANCE

basic & advance

Basic & Advance

EXCEL TRIP

excel trip

excel trip

EXCEL TRIP

excel trip

Excel Trip

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function CEILING & FLOOR & ROUND

ความหมาย - (CEILING) ปัดเศษทศนิยมขึน้ - (FLOOR) ปัดเศษทศนิยมลง - (ROUND) ปัดเศษทศนิยมตามตำาแหน่งทีร่ ะบุ ปัดเศษทศนิยมขึ้น Function

=CEILING(ข้อความ,ตำาแหน่ง) =CEILING(C12,1)

Input

Output

ปัดเศษทศนิยมลง

Function =FLOOR(ข้อความ,ตำาแหน่ง) =FLOOR(F12,1) Input

Output

ปัดเศษทศนิยมตามตำาแหน่งที่ระบุ

Function =ROUND(ข้อความ,ตำาแหน่ง) =ROUND(I12,0) Input

Output

45.600

46.000

45.999

45.000

45.900

46.000

89.300

90.000

56.500

56.000

42.200

42.000

569.000

569.000

365.352

365.000

56.693

57.000

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function EXACT

ความหมาย ประโยชน์

คือ การตรวจสอบข้อความว่าเหมือนกันหรือไม่ ถ้าเหมือนให้ผลลัพธ์เป็น TRUE ถ้าไม่เหมือนให้ผลลัพธ์เป็น FALSE มีประโยชน์ในการตรวจสอบข้อความเพื่อใช้จัดการฐานข้อมูล เช่นการจัดเรียง หรือการจัดทำาผลรวมย่อยของแต่ละข้อความ Function

=EXACT(ข้อความ1,ข้อความ2) =EXACT(C12,D12)

ข้อความ 1

ข้ อควา ม 2

Output

Excel

Excel

TRUE

excel

Excel

FALSE

E xcel

Excel

FALSE

Eecel

Excel

FALSE

Excel

Excel

TRUE

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function TRIM

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

Function Input

Output

S5-04-0023

S5-04-0023

A5-04-3152

A5-04-3152

Mr. Bar

Mr. Bar

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

FUNCTION IF ฟังก์ชั่น IF เป็นอีกฟังก์ชันที่ผู้ใช้ Excel นิยมกันมาก เนื่องจากสามารถนำาไปประยุกต์ใช้งาน เพราะบางครั้งการคำานวณหนึ่ง ๆ อาจจะต้องใช้การตรวจสอบว่าตรงกับเงื่อนไขที่เราจะให้สูตร คำานวณให้หรือไม่

Function IF(logical_test,value_if_true,value_if_false) Logical_test Value_if_true Value_if_false

ข้อมูลที่ใช้ทดสอบเงื่อนไข การคำานวณหรือค่า เมื่อทดสอบตามเงื่อนไขแล้วว่าเป็นจริง การคำานวณหรือค่า เมื่อทดสอบตามเงื่อนไขแล้วไม่ถูกต้องตามเงื่อนไข

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function IF (If แบบมีเงื่อนไข) ประโยชน์

- ได้รับข้อมูลรวดเร็ว, ถูกต้องแม่นยำา - ลด Column ที่ซับซ้อนลง เงื่อนไข คะแนนน้อยกว่า 49 คะแนน 50-59 คะแนน 60-69 คะแนน 70-79 คะแนน 80 ขึ้นไป

F D C B A

Function

=IF(บริเวณที่ต้องการให้ตรวจสอบเงื่อนไข,"เงื่อนไข") =IF(D17>=80,"A",IF(D17>=70,"B",IF(D17>=60,"C",IF(D17>=50,"D",IF(D17<=49,"F"))))) Name

Pisit Somchai Somkit Saijai Kanya

Points 90 80 87 50 69

Grade A A A D C

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function (Count IF)

ประโยชน์

- สามารถนับข้อมูลที่ต้องการได้รวดเร็ว, ถูกต้องแม่นยำาโดยการกำาหนดเงื่อนไขก่อน ซึ่งสามารถนับได้ทั้ง Number และ Text Function

=C OU NTIF (บริ เวณ ที่ ต้อ งการ นั บ ," เง ื่ อนไข ") =COUNTIF(F10:F19,"1")

SHIPNUM

CLEAR_DATE

ARR_DATE

AGE

A5-04-3151 A5-04-3152 A5-04-3153 A5-04-3155 A5-04-3156 A5-04-3157 A5-04-3181 A5-04-3182 A5-04-3186 A5-04-3192

21-May-05 20-May-05 20-13-05 20-May-05 20-May-05 20-May-05 20-.1-05 22-May-05 23-May-05 21-May-05

19-May-05 19-May-05 18-May-05 18-May-05 18-May-05 18-May-05 19-May-05 18-May-05 18-May-05 17-May-05

2 1 -38490 2 2 2 -38491 4 5 4

AGE Error AGE 1 Day AGE 2 Day AGE >3 Day

0 1 4 3

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function IF

ประโยชน์

ช่วยสรุปข้อมูลในการผลิตสินค้าให้สะดวกขึ้น เงื่อนไข Function ตารางสรุปสินค้าที่ต้องผลิต ชื่อสินค้า สินค้า A สินค้า B สินค้า C สินค้า D สินค้า E สินค้า F สินค้า G สินค้า H

สินค ้ า <= 100 ให้ ผลิ ต แต ่ ถ้ า >100 ไม่ ต้อ งผลิต =I F( บริ เวณที ่ต ้อง กา รให้ต รวจส อบ เงื ่อ นไข ,"เงื ่อ นไข ") =I F(D 11: D18< =100, "ผลิ ต","- ") สินค้าคงเหลือ 1,000 100 200 8,000 150 30 9,000 100

คำาสั่ง ผลิต ผลิต ผลิต

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

Function SUMIF

ความหมาย ประโยชน์

คือ การทำางานแบบมีเงื่อนไข สามารถ Sum ข้อมูลได้ตามต้องการ Function

=S UMIF (บริ เว ณที่ต ้ อง การ ให้ ต รว จส อบ เงื ่อ นไข ," เง ื่ อน ไข ")

Function

=SUM(D10:D20)

Part Number

QTY

LT161230019 LT1614B1430055 LT16441261 LT16441262 LT16441269 MC182000686005 MC470474105 MC470474105 PL313914723851 OP100000408 OP100000405 TOTAL :

35,026 2,536 500 253 1,000 50,000 900 6,000 #N/A 3,000 #N/A

=SUMIF(G10:G20,"<>#n/a") Part Number

OP100000406 OP100000408 MC182000686005 PL313914723851 LT16441261 NT-6300008 LT16441269 MC470474105 SH5100M3A MC470474105 SH5100M3A #N/A

TOTAL :

QTY #N/A 3,000 50,000 #N/A 500 #N/A 50,000 900 #N/A 900 #N/A #N/A

Excel Tips : เทคนิคการใช้ Excel By Mr. Pongthap Reawruad ([email protected])

ข้อความจากข้อผิดพลาด

ประโยชน์

รู้ทัน แก้ไขเร็ว งานเดินหน้า ปัญหาไม่จม ข้อความผิดพลาด ##### #VALUE! #NUM! #NAME? #N/A #REF! #NULL!

สาเหตุ จะเกิดขึน้ เมื่อตัวเลขในเซลล์ยาวกว่า ขนาดของเซลล์ จะเกิ ดขึน้ เมื่อใช้สูตรผิดหลักไวยากรณ์ของสูตร

วิธีแก้ปัญหา แก้ไขได้โดยการขยายขนาด ความกว้ งของเซลล์ แก้ ไขได้โาดยการสำ ารวจดูว่าประเภทของข้อมูล

จะเกิดเมื่อใช้ 0 เป็นตัวหาร เช่น 10/0 ซึ่งทำาไม่ได้

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

เช่น การนำาตัวเลขไปบวกกับตัวอักษร

จะเกิดเมื่อในสูตรมีขอ้ ความที่ Excel ไม่สามารถบอกได้ว่าคืออะไร เช่น A1+A โดยที่ตวั อักษร A แปลกปลอมเข้าไปหรือไม่ จะเกิดขึ้นเมื่อกรอกตัวแปรผิดประเภทให้กับฟังก์ชัน

ถูกต้องตามหลักคณิตศาสตร์หรือไม่

แก้ไขได้โดยการตรวจสอบว่าประเภทตัวแปร ของฟังก์ชันคืออะไร แล้วเปลีย่ นให้ถูกต้อง

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

แก้ไขได้โดยการตรวจสอบตำาแหน่งอ้างอิงเซลล์

ในสูตรได้ มักพบเมื่อหายไปอ้างเซลล์ข้ามชีทหรือข้ามสมุดงาน จะเกิดขึ้นเมื่อกำาหนดพื้นที่เซลล์สองเซลล์ที่ไม่ได้มีส่วนใดต่อกัน แต่ลมื แบ่งแยกด้วยเครือ่ งหมายคัน่ (,) เช่น SUM(A1:B2,C2:D5) เขียนผิดเป็น SUM(A1:B2 C2:D5)

แก้ไขได้โดยการใส่เครื่องหมายคั่นให้ถูกต้อง