แสดงบทความที่มีป้ายกำกับ Math แสดงบทความทั้งหมด

วันพุธที่ 24 เมษายน พ.ศ. 2556

ตัดเศษด้วยฟังก์ชัน TRUNC กับ INT

การทำงานของฟังก์ชัน TRUNC นั้น คล้ายกับการทำงานของฟังก์ชัน INT คือ ส่งกลับค่าที่เป็นจำนวนเต็ม

ต่างกันแค่วิธีตัดเศษ ซึ่งการเลือกใช้ ก็ขึ้นอยู่ที่ว่าต้องการ รูปแบบของผลลัพท์ อย่างไร

ข้อแตกต่างของ TRUNCกับ INT

ฟังก์ชัน TRUNC จะลบจำนวน ที่เป็นเศษส่วนออกและถ้าใช้ค่าที่เป็นลบ เช่น TRUNC (-2.5) ฟังก์ชัน จะส่งกลับค่า -2
ฟังก์ชัน INT จะปัดเศษขึ้น เป็นจำนวนเต็มที่ใกล้ที่สุด โดยยึดค่าของเศษส่วน เป็นหลัก เมื่อมีการใช้ตัวเลขลบ เช่น INT( -2.5) จะส่งกลับค่า -3 เพราะเป็นตัวเลข ที่น้อยกว่า
ฟังก์ชัน TRUNC กำหนดจำนวนทศนิยมได้ ในขณะที่ ฟังก์ชัน INT ทำไม่ได้

ไวยากรณ์ฟังก์ชัน TRUNC

=TRUNC (number,num_digits)

number คือตัวเลขที่คุณต้องการตัดเศษทิ้ง
num_digits คือจำนวนที่ใช้ระบุความแม่นยำของการตัดเศษ ซึ่งมีค่าเริ่มต้นคือ 0 (ศูนย์)

ไวยากรณ์ฟังก์ชัน INT

=INT (number)

number คือจำนวนจริงที่คุณต้องการปัดเศษเพื่อให้เหลือเป็นจำนวนเต็ม

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

Row	Column
	A	B	C	D	E
1	ข้อมูล	1.2	4.8	-5.3	-8.8
2	แทนค่า INT	=INT(B1)	=INT(B1+C1)	=INT(D1/B1)	=INT(E1)
3	ผลลัพท์ INT	1	6	-5	-9
4	แทนค่า TRUNC	=TRUNC(B1-C1)	=TRUNC(C1,2)	=TRUNC(D1-C1)	=TRUNC(E1*B1)
5	ผลลัพท์ TRUNC	-3	4.80	-10	-7

อธิบายการใช้ฟังก์ชัน INT กับ ฟังก์ชัน TRUNC จากตารางข้างบน

เมื่อตัวเลขเป็นด้านบวก ทั้งสองฟังก์ชันจะตัดเศษออก และให้ผลลัพท์เหมือนกัน
ถ้าตัวเลขเป็นด้านลบ ทั้งสองฟังก์ชันจะแสดงผลที่ต่างกัน INT ปัดเศษ แต่ TRUNC ตัดเศษ
B1+C1 = 6 เมื่อผลลัพท์เป็นจำนวนเต็มก็ไม่มีการตัดเศษ ซึ่งทั้งสองฟังก์ชันจะให้ผลลัพท์เท่ากัน
INT(E1) = -9 เพราะ E1 = 8.8, ฟังก์ชัน INT ปัดเศษเป็น -8 แต่ถ้าเป็น TRUNC(E1) จะได้ผลลัพท์เป็น -9
D1/B1 = -4.41666666666667 ฟังก์ชัน INT ปัดเศษเป็นเลขจำนวนเต็มที่ใกล้ที่สุด คือ -5
B1-C1 = -3.6 ฟังก์ชัน TRUNC ตัดเศษ ให้เหลือเลขจำนวนเต็ม คือ -3
D1-C1 = -10.1 ฟังก์ชัน TRUNC ตัดเศษ ให้เหลือเลขจำนวนเต็ม คือ -10
TRUNC(C1,2) = 4.80 กำหนดทศนิยม 2 ตำแหน่งให้กับฟังก์ชัน TRUNC ถ้าไม่กำหนดผลลัพท์จะได้ 4

วันศุกร์ที่ 8 มีนาคม พ.ศ. 2556

การคูณตัวเลขใน Excel ด้วยสูตร PRODUCT กับ SUMPRODUCT

หลากหลายวิธีการคูณใน Excel

การคูณใน Excel จะใช้เครื่องหมายดอกจัน (*) หรือ asterisk ในการหาผลคูณ เช่น 2*3 = 6
นอกจากการใช้เครื่องหมาย * แล้วสามารถใช้ฟังก์ชัน PRODUCT หรือ SUMPRODUCT ให้คืนค่าผลคูณได้

ทั้งสองฟังก์ชัน มีลักษณะการทำงานที่ต่างกัน แต่มีฟีเจอร์ที่ครอบคลุมเรื่องการคูณได้กว้างกว่า การใช้ asterisk หรือ ดอกจัน(*)

ไวยากรณ์ของฟังก์ชัน PRODUCT

PRODUCT(number1,number2,...)

number1, number2 .. เป็นตัวเลข 1 ถึง 255 จำนวน ที่ต้องการคูณ

เปรียบเทียบการคูณใน Excel ด้วยการใช้ * (asterisk) กับ ฟังก์ชัน PRODUCT

	A		B	C	D	E	F	G	H
1	Line 1		1	2	3	4	3	2	1
2	Line 2		2	3	4	3	2	1	2
3	Line 3		2	3	4	3	2	1	2
4	เปรียบเทียบการคูณ Line 1กับ Line 2 ด้วย (*) กับ PRODUCT
5	ใช้ * (asterisk)เป็นตัวคำนวณ		=B1B2C1C2D1D2E1E2F1F2G1G2H1*H2
6	ฟังก์ชัน PRODUCT	number อยู่ติดกัน	=PRODUCT(B1:H2)
6	ฟังก์ชัน PRODUCT	number ต่างชีท+ไม่ติดกัน	=PRODUCT(Sheet11!B1:H1,Sheet11!B3:H3)
8	ผลลัพท์คำนวณทั้งสองวิธี		=41472

ข้อสังเกตุช่วยให้เข้าใจการใช้ฟังก์ชัน PRODUCT ได้ง่ายขึ้น

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

ถ้าข้อมูลอยู่ต่าง Sheet เมื่อพิมพ์ =PRODUCT( แล้วให้เลือกคลิกไปที่ sheet ที่ต้องการคำนวณแล้วลากเม้าส์คลุมพื้นที่ทั้งหมดที่ต้องการผลคูณ

หากข้อมูลที่ต้องการคูณไม่ใช่เซลล์ที่อยู่ติดกัน ให้กด CTRL ขณะที่เลือกพื้นที่เซลล์

ไวยากรณ์ของฟังก์ชัน SUMPRODUCT

SUMPRODUCT(array1,array2,array3, ...)

array1, array2, array3 ... คืออาร์เรย์ 2 ถึง 255 ที่มีคอมโพเนนต์ที่ต้องการคูณแล้วบวก

เช่น การคำนวณราคาสินค้าต่อหน่วยของสินค้าหลายๆ ชนิดในใบเสร็จใบเดียว โดยคูณราคาสินค้ากับจำนวนที่ซื้อ แล้วนำราคารวมของสินค้าแต่ละชนิดมารวมกัน ซึ่ง SUMPRODUCT จะมีความเหมาะสมกับมากกว่าการใช้สูตร SUM แต่อาร์กิวเมนต์อาร์เรย์ที่ใช้ในฟังก์ชัน SUMPRODUCT ต้องมีขนาดเท่ากัน ถ้าไม่เท่ากัน ฟังก์ชันจะส่งกลับค่าความผิดพลาด #VALUE!

เปรียบเทียบการคูณใน Excel ด้วย * กับฟังก์ชัน SUMPRODUCT

	A	B	C	D	E	F	G	H
1	Unit Price-A	1	2	3	4	5	6	7
2	Unit Price-B	5	6	7	8	7	6	5
3	Qty-A	8	9	1	2	3	4	5
4	Qty-B	10	10	10	10	10	10	10
5	เปรียบเทียบการคูณ Unit Price กับ Qty ด้วย (*) กับ SUMPRODUCT
6	ใช้ * หาราคาสุทธิ A	=(B1B3)+(C1C3)+(D1D3)+(E1E3)+(F1F3)+(G1G3)+(H1*H3)
7	ฟังก์ชัน SUMPRODUCT	array อยู่ติดกัน		=SUMPRODUCT(B1:H1,B3:H3)
8		array ต่างชีท		=SUMPRODUCT(Data!B2:H2,Data!B4:H4)
9		รวมทั้ง A,B		=SUMPRODUCT(Data!B1:H2,Data!B3:H4)
10	ผลลัพท์การคำนวณทั้งสองวิธี	PRODUCT A=111, PRODUCT B=440, รวม=551

เทคนิคช่วยให้ใช้ฟังก์ชัน SUMPRODUCT ง่ายขึ้น

ฟังก์ชัน SUMPRODUCT ช่วยคำนวณราคาสุทธิ (Unit Price*Qty) ง่ายกว่าการใช้ (*) นอกจากนี้ยังสามารถใส่ได้ถึง 255 อาร์เรย์

จำนวน arrary ในฟังก์ชันต้องมีขนาดเท่ากัน ไม่เช่นนั้นสูตรจะคืนค่า #VALUE!

จากตาราง รวมทั้ง A,B ฟังก์ชันจะทำงานโดย จับคู่คูณต้้งแต่แถว B1*B3, C1*C3, D1*D3, E1*E3, F1*F3, G1*G3, H1*H3 และเป็นแบบเดียวกันระหว่าง B2-H2 กับ B4-H4

ข้อมูลต่าง Sheet เมื่อพิมพ์สูตร =SUMPRODUCT( แล้วให้เลือกคลิกไปที่ sheet ที่ต้องการคำนวณ แล้วลากเม้าส์คลุมพื้นที่ทั้งหมดที่ต้องการ

หากข้อมูลที่ต้องการคูณไม่ใช่เซลล์ที่อยู่ติดกัน ให้กด CTRL ขณะที่เลือกพื้นที่เซลล์

วันพุธที่ 14 พฤศจิกายน พ.ศ. 2555

การใช้ IF กับ INT หาเงินทอนแบบแยกแบงค์แยกเหรียญ

การใช้ฟังก์ชัน IF กับ INT คำนวณเงินทอนหรือแลกเงินด้วยจำนวนธนบัตรและเหรียญเท่าที่จำเป็น

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

จากตัวอย่างรูป Figure A เป็นการ
หาเงินทอนแยกแบงค์แยกเหรียญ ด้วยโจทย์ดังนี้

ยอดรับเงิน 2,000 (เซลล์ C2)
หักค่าสินค้า 1,067.50 (เซลล์ D2)
เหลือเงินที่ต้องทอนให้ลูกค้า 932.75 (เซลล์ E2)

Figure A

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

ฟังก์ชันที่ใช้กับตารางนี้มี 2 ฟังก์ชัน

ฟังก์ชัน IF กำหนดเงื่อนไข
ฟังก์ชัน INT ปัดเศษทศนิยมออก ไวยากรณ์ INT คือ INT(number)

Logic การใช้ IF ของสูตรชุดนี้ คือ ถ้าเงินที่ต้องทอนหักหน่วยเงินแล้วมากกว่า, เป็นจริง-ใช้ INT ปัดเศษผลลัพท์ จากเงินทอนรวม หักหน่วยเงินที่ใหญ่กว่า แล้วจึงคูณหน่วยเงิน ,ไม่จริง-เป็นศูนย์

Row	Column
	B	C	D	E	F
1		รับเงิน	ค่าสินค้า	เงินทอน
2		2,000.00	1,067.25	932.75

ส่วนต่างๆ ของตารางแสดงสูตร โดยตำแหน่งคอลัมท์และแถวที่ใช้ (กำหนดจาก FigureA)

หน่วยเงิน (column C)แยกประเภทของเหรียญและธนบัตร เช่น เหรียญ 25 สตางค์, เหรียญ 5 บาท, ธนบัตรใบละ 50 บาท, ธนบัตรใบละ 100 บาท เป็นต้น
สูตรในคอลัมท์เงินทอน แสดงการใช้ฟังก์ชัน ของ column E
เงินทอน/บาท(column E) แสดงผลลัพท์ที่คำนวณได้

	B	C		E	F
3	หน่วยเงิน		สูตรในคอลัมท์เงินทอน	เงินทอน/ บาท	จำนวน
4	สต.	0.25	=IF($E$2-$E5>$C4,INT(($E$2-SUM($E5:$E$14))/C4)*$C4,0)	0.25	1
5	สต.	0.50	=IF($E$2-$E6>$C5,INT(($E$2-SUM($E6:$E$14))/C5)*$C5,0)	0.50	1
6	บาท	1	=IF($E$2-$E7>$C6,INT(($E$2-SUM($E7:$E$13))/C6)*$C6,0)	0
7	บาท	2	=IF($E$2-$E8>$C7,INT(($E$2-SUM($E7:$E$14))/C7*$C7,0)	2	1
8	บาท	5	=IF($E$2-$E9>$C8,INT(($E$2-SUM($E9:$E$14))/C8)*$C8,0)	0
9	บาท	10	=IF($E$2-$E10>$C9,INT(($E$2-SUM($E10:$E$14))/C9)*$C9,0)	10	1
10	บาท	20	=IF($E$2-$E11>$C10,INT(($E$2-SUM($E11:$E$14))/C10)*$C10,0)	20	1
11	บาท	50	=IF($E$2-$E12>$C11,INT(($E$2-SUM($E12:$E$14))/C11)*$C11,0)	0
12	บาท	100	=IF($E$2-$E13>$C12,INT(($E$2-SUM($E13:$E$14))/C12)*$C12,0)	400	4
13	บาท	500	=IF($E$2-$E14>$C13,INT(($E$2-SUM($E14:$E$14))/C13)*$C13,0)	500	1
14	บาท	1000	=IF($E$2>$C14,INT($E$2/$C14)*$C14,0)	0
15		รวมเงินทอน		932.75	10

จากภาพ Figure A และตารางตัวอย่าง
column F เป็นผลคำนวณหาจำนวนเหรียญ/ธนบัตร ที่ต้องทอนให้กับลูกค้า ในขณะที่ column E เป็นการคำนวณหาเงินทอนตามมูลค่าของเหรียญ/ธนบัตร ความแตกต่างของสองคอลัมท์นี้ มีเพียงแค่ตัวคูณของหน่วยเงินเท่านั้น คือ ตัวคูณมูลค่าของเหรียญหรือ ธนบัตร (ในที่นี้คือ คอลัมท์ C)
หมายเหตุ

ในเซลล์ E4 : =IF(E2-E5>C4,INT((E2-SUM(E5:E14))/C4)*C4,0)
ในเซลล์ F4 : =IF(E2-E5>C4,INT((E2-SUM(E5:E14))/C4),0)
ในคอลัมท์ C ที่เป็นค่าของเหรียญหรือธนบัตร หากใช้ custom format cells เราสามารถใส่หน่วย "สต." หรือ "บาท" ได้พร้อมทั้งแทนค่าในสูตรด้วยได้เลย โดยไม่ต้องแยกหน่วยที่คอลัมท์ B
ตารางที่แสดงด้านล่างได้เพิ่มหน่วยเงิน 2 บาทเข้าไป

วันอังคารที่ 30 ตุลาคม พ.ศ. 2555

Condtional Formatting สลับสีระหว่างแถวด้วยฟังก์ชัน ODD กับ EVEN ใน Excel

ด้วยฟีเจอร์ที่หลากหลายของ Conditional Formatting ผนวกกับ Formatting ที่ใช้งานง่ายช่วยให้การทำงานบน Excel เป็นไปได้มากกว่าหลายๆ คนจะคาดถึง ดังนั้นในบทความนี้

จะประยุกต์การใช้ Conditional Formatting เพื่อสร้างแถบริ้วสลับสีใน Excel

ซึ่งก่อนอื่นต้องทำความรู้จักกับฟังก์ชันอย่าง ODD และ EVEN ก่อน เพราะงานนี้เราจะใช้ Conditional Formatting เป็นหัวโจกแล้ว EVEN กับ ODD เป็นสมุนซ้ายขวา

การทำงานของ ODD และ EVEN

EVEN (number) ส่งกลับค่าตัวเลขที่ปัดเศษขึ้นเป็นเลขคู่ที่อยู่ใกล้ที่สุด ฟังก์ชันนี้เหมาะที่จะใช้คำนวณสิ่งของที่ต้องอยู่เป็นคู่ๆ
ODD (number) ทำหน้าที่ส่งคืนค่าตัวเลขที่ปัดเศษขึ้นเป็นเลขคี่ที่อยู่ใกล้ที่สุด

ข้อสังเกตการทำงานของฟังก์ชัน EVEN และ ODD

Number คือค่าที่จะปัดเศษ
ถ้าค่า number ไม่ใช่ตัวเลข ฟังก์ชัน ODD และ EVEN จะส่งกลับค่าความผิดพลาด #VALUE!
การปัดเศษขึ้นของทั้งสองฟังก์ชัน ทำให้ค่าผลลัพท์ที่ได้จะออกห่างจาก 0 มากขึ้น
เช่นเดียวกับ EVEN ถ้า number เป็นจำนวนคู่อยู่แล้วจะไม่มีการปัดเศษขึ้น
สำหรับ ODD ถ้า number เป็นจำนวนคี่อยู่แล้วจะไม่มีการปัดเศษขึ้น

วิธีการสร้างแถวสลับสีใน Excel โดยใช้ Conditional Formatting และฟังก์ชัน ODD กับ EVEN

เปิดชีทงานที่ต้องการ คลุมพื้นที่เซลล์หรือ Table ที่เราต้องการทำการสลับสี

ไปที่ Home > Styles > Conditional Formatting > New Rule
ให้เลือกรายการสุดท้ายใต้หัวข้อ Select Rule Type:
คือ Use a formula to determine which cells to format:
ใต้ Format values where this formula is true: จะเห็นช่องให้ใส่ Function เราจะใช้ฟังก์ชัน EVEN กับ ODD ใส่ที่นี่เพื่อกำหนดการสลับสีแถวใน Table (ทำสองครั้ง)
- ฟังก์ชันที่ทำริ้วสีสีำหรับแถวคูู่ : =EVEN(ROW())=ROW()
- ฟังก์ชันที่ทำริ้วสีสำหรับแถวคี่ : =ODD(ROW())=ROW()
เราอาจจะกำหนดริ้วสีเพียงแถวคู่หรือแถวคี่อย่างเดียวก็ได้ Table ก็จะแสดงริ้วสีสลับกับสีขาว

Manage Rules in Conditional Formatting Menu

ถ้าเลือกใช้คำสั่ง New Rule ใน Conditional Formatting เราต้องเรียกใช้คำสั่งนี้สองครั้ง

แต่ถ้าใช้คำสั่ง Manage Rules แทน นอกจากเพิ่มเงื่อนไขได้แล้วก็ยังแก้ไข หรือ ลบ เงื่อนไขได้

รวมถึงให้แสดงผลทันทีโดยไม่ต้องปิดกล่องโต้ตอบ

ซึ่งถ้าต้องการให้แสดงผลทันที ให้กดที่ Apply

วันพฤหัสบดีที่ 11 ตุลาคม พ.ศ. 2555

การใช้ฟังก์ชัน IFERROR กับ VLOOKUP

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

IFERROR ทำหน้าที่คล้าย IF เพียงแต่เพิ่มศักยภาพการจัดการกับข้อมูลได้กว้างขึ้นและตรวจสอบความถูกต้องไปพร้อมๆ กัน

ไวยากรณ์และความหมาย

IFERROR(value,value_if_error)

value คืออาร์กิวเมนต์ที่ใช้ตรวจสอบเพื่อหาข้อผิดพลาด
value_if_error คือค่าที่จะส่งกลับถ้า value ได้ผลลัพท์เป็นค่าความผิดพลาด(Error_val) และชนิดของค่าความผิดพลาดที่จะทำให้ฟังก์ชันส่งคืนค่า value_if_error มีอยู่ 7 ชนิด
#N/A

#VALUE!

#REF!

#DIV/0!

#NUM!

#NAME?

#NULL!

โจทย์ตัวอย่างทดสอบการใช้ IFERROR

Row	Column
2	A	B	C	D	E
3	Product	Price	Dis.	Dis.Price	Formula in Dis.Price
4	Ice-Cream Maker C30	5,400	3%	5,238	=IFERROR(B4-(B4*C4),B4)
5	Ice-Cream Maker C45	9,100	5%	8,645	=IFERROR(B5-(B5*C5),B5)
6	Ice-Cream Maker C50	17,900	7%	16,647	=IFERROR(B6-(B6*C6),B6)
7	Mini Prep Processor	3,600		3,600	=IFERROR(B7-(B7*C7),B7)
8	Soup Blender SB30	10,000	3%	9,700	=IFERROR(B8-(B8*C8),B8)
9	Stick hand Blender	3,000		3,000	=IFERROR(B9-(B9*C9),B9)
10	Waffle Maker WF20	5,800	2%	5,684	=IFERROR(B10-(B10*C10),B10)

จากตัวอย่างกำหนดให้ Dis.Price เป็นราคาสุทธิที่หักส่วนลด (Dis.) ตามเปอร์เซนต์ที่กำหนดไว้โดยให้ Price เป็นราคาฐานที่่ใช้คำนวณส่วนลด และถ้าสินค้าชนิดใดไม่มีส่วนลด (Dis.) ให้ใช้ราคาปกติ (Price) ซึ่งจะแสดงสูตรที่ใช้ในคอลัมท์ Formula in Dis.Price

นอกจากนี้ถ้าเราใช้ IFFEROR ร่วมกับ Vlookup เพื่อให้สูตรคืนค่าผลลัพท์จากสองฐานข้อมูลได้ในเวลาเดียวกัน ซึ่งจะเป็นข้อมูลจาก Worksheet เดียวกัน หรือ ต่าง Worksheet ก็ได้

ตัวอย่างถัดไปเป็นการทดสอบ IFERROR ร่วมกับ VLOOKUP
เราจะใช้ข้อมูลจากรูปเป็นฐานข้อมูล ซึ่งข้อมูลด้านซ้ายเป็นจำนวนสต็อคสินค้าของ LED TV และด้านขวาเป็นจำนวนสต็อคสินค้าของ Kitchenware

Row	Column
2	A	B	C
3		LED TV	QTY
4	1	LG	300
5	2	SAMSUNG	250
6	3	PANASONIC	120
7	4	BRAVIA	90
8	5	TOSHIBA	560
9	6	SUNYO	50
10
11
12


E	F	G
	KICHENWARE	QTY
1	OTTO	1,110
2	IMARFLEX	500
3	HOUSE WORTH	459
4	MISAWA	50
5	VICTOR	89
6	SHARP	300
7	PHILIPS	70
8	DEBRANDT	79
9	CUISINART	250

เราจะใช้การค้นหาจำนวนสต็อคของสินค้า 2 หมวดนี้ โดยใช้ฟังก์ชัน IFERROR ที่ โดยใช้เซลล์ A13 เป็นตัวกำหนดเงื่อนไข ให้หาจำนวนสินค้าในสต็อคแบรนด์ Cuisinart ว่ามีจำนวนกี่หน่วย ซึ่งผลลัพท์เท่ากับ 250 ที่เซลล์ B13 และ C13 เป็นรูปแบบฟังก์ชัน IFERROR ที่ใช้ใน B13
และ ที่ เซลล์ A14 เป็นการทดสอบสูตรแบบเดียวกันเพียงแต่เปลี่ยนเป็น แบรนด์ LG ได้คำตอบ 300 หน่วย (B14), แสดงสูตรที่ (C14)

Row	Column
11	A	B	C
12	Brand	Qty.	Formula in Qty. (Column B)
13	CUISINART	250	=IFERROR(VLOOKUP(A13,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))
14	LG	300	=IFERROR(VLOOKUP(A14,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))

วันเสาร์ที่ 21 กรกฎาคม พ.ศ. 2555

Copy ใน Excel โดยให้สูตรติดมาด้วยสตริง($) และ ประโยชน์ของ Round

ตัวช่วยที่ทำให้การคำนวณใน Excel ง่ายขึ้นและเร็วขึ้น สำหรับปริมาณข้อมูลมากๆ คือการใช้ $ (สตริงเรียกคำสั่งนี้ด้วยการกด F4) เพื่อล็อคตำแหน่งที่อ้างอิงในสูตร เมื่อเรา copy สูตรนั้นไปใช้ที่เซลล์อื่นๆ ค่าที่อยู่หลังเครื่องหมาย $ จะไม่ถูกเปลี่ยนแปลงไปเป็นการ fix สูตร เพราะสามารถ copy ให้สูตรแสดงผลเดียวกับเซลล์เดิม มาที่เซลล์ใหม่ด้วย โจทย์ตั้งเงื่อนไขให้หาราคาสินค้า Ink-Jet Printer 4 รุ่นที่ต้องการมาทำส่วนลดการขายรุ่นละ 10% ซึ่งผลลัพท์จะแสดงใน Dis. 10%(Column D) และเมื่อนำส่วนลดรวมกับราคาจะได้ราคาใหม่ คือ New Price (Column E) และแสดงสูตรที่ Formula (Column F) ซึ่งเป็นสูตรที่ใช้ใน Dis.10% (Column D)

ตารางตัวอย่างการใช้ฟังก์ชัน Round+$

	B	C	D	E	F
1
2	1	2	3	4	5
3	Table_array
4	Multi Ink-Jet Printer	Price	Dis.10%	New Price	สูตรใน Column D
5	Brother MFC-J825DW	8,990	899	8,091	=Round(C5*$D$4,0)
6	Epson Me Office620F	3,990	399	3,591	=Round(C6*$D$4,0)
7	Canon E500	2,790	279	2,511	=Round(C7*$D$4,0)
8	Canon Pixma MG5370	3,190	319	2,871	=Round(C8*$D$4,0)

ใช้ฟังก์ชัน ROUND เพื่อให้สูตรคืนค่าผลลัพท์ไม่มีเศษทศนิยม(ปัดเศษ)

ถ้าไม่ใช้ ROUND คลุม สูตรที่เซลส์ D5 จะเท่ากับ =C5*$D$4
ไวยากรณ์ฟังก์ชัน ROUND คือ =ROUND(เซลล์หรือค่าที่ต้องการ,ตำแหน่งทศนิยมที่ต้องการ) เมื่อเราเลือกค่า "0" คือจะไม่มีเศษทศนิยม
สมมุติว่าหา 10% ของราคาใหม่ Canon E500 ซึ่งเท่ากับ (2,511*10/100) จะได้ 251.1 เมื่อใส่ round เข้าไปเศษ .1 จะถูกปัดออก ผลลัพท์จะเท่ากับ 251

การเรียกใช้คำสั่ง $ ผ่านคีย์ลัด F4

พิมพ์สูตรแล้วให้กด F4 เช่นพิมพ์ =B2 แล้ว กด F4 จะได้ผลลัพท์ =$B$2
ถ้าฟังก์ชันนั้นยาวมากก็ให้กด F4 หลังจากเลือกหรือพิมพ์พื้นที่เซลล์ที่ต้องการฟิกซ์ เช่น =vlookup(A4,แล้วกด F4 2 ครั้งหน้าตาสูตรจะเป็น =vlookup(A$4,
ถ้าฟังก์ชันมีอยู่ก่อนแล้วและต้องการเพิ่ม $ เพื่อฟิกซ์สูตรก็ให้ไปที่เซลล์ที่มีฟังก์ชันนั้น แล้ว กด F2 แล้วเลือกช่วงที่ต้องการกำหนดแล้วจึงกด F4

ตารางเปรียบเทียบการใช้ $

1	A	B	C	D
Row	Column
2		1	3	7
3		2
4		4
5	=$B$2	1	1	1
6		1	1	1
7	=B$2	1	3	7
8		1	3	7
9	=$B2	1	1	1
10		2	2	2

ทดสอบใช้ $ ล็อคสูตรในเซลล์

ที่เซลล์ A5 พิมพ์ =B2 แล้วกด F4 1 ครั้ง ตัวสตริง $ อยู่ทั้งหน้าและหลัง Column Letter จะได้ =$B$2 แล้วก๊อปปี้ไปยัง B5 ถึง D6 ผลลัพท์เท่ากับ 1 เสมอ (สีเหลือง) เพราะเป็นการล็อคเซลล์ B2 ไม่ว่าจะก๊อปไปไหนสูตร จะย้อนมาอ้างอิงเซลล์ B2 เท่านั้น

ที่เซลล์ B7 พิมพ์ =B2 แล้วกด F4 2 ครั้ง ตัวสตริง $ อยู่หลัง Column Letter จะได้ =B$2 แล้วก๊อปปี้จาก B7 ถึง D8 ผลลัพท์จะได้ 1,3,7 ทั้งสองแถว(สีเขียว) เพราะ $ ทำหน้าที่ล็อคแถว 2 (Row2) เสมอ สูตรในคอลัมท์ C จะเปลี่ยนเป็น C$2 และคอลัมท์ D เป็น D$2

ที่เซลล์ B9 พิมพ์ =B2 แล้วกด F4 3 ครั้ง ตัวสตริง $ อยู่หน้า Column Letter จะได้ =$B2 แล้วก๊อปปี้จาก B9 ถึง D10 ผลลัพท์จะได้ 1,1,1 ที่แถวแรก และ 2,2,2 ที่แถวสอง (สีชมพู)ตัว $ ล็อคเฉพาะ Column B สูตรในเซล์ B10 ถึง d10 จะเปลี่ยนเป็น $B3

Welcome to Kesinee Blog

Search

วันพุธที่ 24 เมษายน พ.ศ. 2556

ตัดเศษด้วยฟังก์ชัน TRUNC กับ INT

ตัดเศษด้วยฟังก์ชัน TRUNC กับ INT

=TRUNC (number,num_digits)

=INT (number)

วันศุกร์ที่ 8 มีนาคม พ.ศ. 2556

การคูณตัวเลขใน Excel ด้วยสูตร PRODUCT กับ SUMPRODUCT

หลากหลายวิธีการคูณใน Excel

PRODUCT(number1,number2,...)

เปรียบเทียบการคูณใน Excel ด้วยการใช้ * (asterisk) กับ ฟังก์ชัน PRODUCT

ข้อสังเกตุช่วยให้เข้าใจการใช้ฟังก์ชัน PRODUCT ได้ง่ายขึ้น

SUMPRODUCT(array1,array2,array3, ...)

เปรียบเทียบการคูณใน Excel ด้วย * กับฟังก์ชัน SUMPRODUCT

เทคนิคช่วยให้ใช้ฟังก์ชัน SUMPRODUCT ง่ายขึ้น

วันพุธที่ 14 พฤศจิกายน พ.ศ. 2555

การใช้ IF กับ INT หาเงินทอนแบบแยกแบงค์แยกเหรียญ

การใช้ฟังก์ชัน IF กับ INT คำนวณเงินทอนหรือแลกเงินด้วยจำนวนธนบัตรและเหรียญเท่าที่จำเป็น

วันอังคารที่ 30 ตุลาคม พ.ศ. 2555

Condtional Formatting สลับสีระหว่างแถวด้วยฟังก์ชัน ODD กับ EVEN ใน Excel

จะประยุกต์การใช้ Conditional Formatting เพื่อสร้างแถบริ้วสลับสีใน Excel

การทำงานของ ODD และ EVEN

วันพฤหัสบดีที่ 11 ตุลาคม พ.ศ. 2555

การใช้ฟังก์ชัน IFERROR กับ VLOOKUP

IFERROR(value,value_if_error)

วันเสาร์ที่ 21 กรกฎาคม พ.ศ. 2555

Copy ใน Excel โดยให้สูตรติดมาด้วยสตริง($) และ ประโยชน์ของ Round

ใช้ฟังก์ชัน ROUND เพื่อให้สูตรคืนค่าผลลัพท์ไม่มีเศษทศนิยม(ปัดเศษ)

ทดสอบใช้ $ ล็อคสูตรในเซลล์

Popular Pots

Tips

หาค่าที่ไม่ซ้ำใน Excel ด้วย Advanced Filter,Data Validation และ Conditional Formatting

Tag