Expert Excel Tips

Expert Excel Tips. เทคนิคการใชงาน Excel อยางมืออาชีพ. [Excel มีสูตรการใชงานมากมายจริงๆ มากจนใชไมหมด แตสูตรทีใ่ ชบอยๆ ก็มีไมเทาไร แลวคุณใชเปนจริงหรือไม? เอกสารนี้มีแคสูตรธรรมดาๆ แตจงนําไปใชใหมีคามากที่สุด.] Mr.Pongthap Reawruad [[email protected]] November 2008

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

5 สูตรพื้นฐานที่ขาดไมได ถาเอยถึง Excel แลวมีนอยคนนักที่จะไมรูจัก ยิ่งถาเปนพนักงานที่ตองทํางานประจําออฟฟศดวย แลว ยิ่งตองคุนเคยและไดสัมผัสกันแทบทุกวันก็วาได และแนนอนวายิ่งใชงานบอยยอมตองเจอ ปญหาบอยตามมาดวยเชนกัน เชน ทําไมคํานวณไมตรง? สูตรผิดตรงไหนหรือเปลานะ? ทําไม ทศนิยมไมตรงกับที่คิดไวเลย? และอีกหลากหลายปญหาที่บางคนก็เจอ บางคนก็ไมเจอ (เพราะใชงาน เปนอยูแคนน ั้ เอง บวก ลบ คูณ หาร) ¾ กอนอื่นมารูจักวิธีคิดคํานวณของ Excel กันกอน สิ่งซึ่งผูใช Excel หลงผิดไปใชกันโดยหารูไมวา สิ่งที่ตนเองทําลงไปนั้นเปนการทํารายตนเองก็คือ การเลือกใช Format เปลี่ยนแปลงรูปแบบการแสดงผล ไมวาจะเปนรูปแบบที่จัดใหคาในเซลลชิดซาย ชิดขวา หรือแสดงตรงกลางเซลล หรือจัดใหตัวเลขแสดงหลักทศนิยมตามที่ตนตองการก็ตาม การใช

คําสั่ง Format ไมไดสงผลใหคาในเซลลเปลี่ยนแปลงไปจากเดิม มีแตรูปแบบที่แสดงออกมาเทานั้นที่ เปลี่ยนไป

ยกตัวอยางเชน เมื่อนําเซลล 2 เซลลซึ่งมีตัวเลข 0.33 เหมือนกันมาบวกกันใหแสดงผลลัพธใน เซลลอีกเซลลหนึ่งไดผลลัพธเปน 0.66 แลวเราเปลี่ยน Format ใหแสดงทศนิยมเพียงหลักเดียว เลข 0.33 จะแสดงเปน 0.3 เลข 0.66 จะแสดงเปน 0.7 เมื่อเปน 0.7 แลวจะทําใหผูใช Excel สงสัยกันทีเดียววา ไฉน 0.3 บวกกัน 2 ครั้งจึงไดผลลัพธ ออกมาเปน 0.7 ไปได ... นี่แหละตัวอยางงายๆ ซึ่งชวยใหเห็นความนากลัวของการใชคาํ สั่ง Format โดยไมรูตัววา เมือ ่ ใดควรจะใช Format จึงจะทําใหเห็นวา 0.3 บวกกัน 2 ครั้ง ยอมไดผลเปน 0.6 ถูกตองตามที่เห็น ดังนั้น ขอใหใชสูตรปรับคาที่ไดจากการคํานวณกอน เพื่อใหไดคามีผลลัพธเทากับที่ตองการอยางแทจริง จากนั้นจึงคอยใชคําสั่ง Format ตามทีหลัง เพราะ Excel ใชวิธีคํานวณตัวเลข โดยคิดแบบเลขฐาน 2 จากนั้นพอไดผลลัพธแลวจะแปลงกลับมาเปนเลข ฐาน 10 ซึ่งมีความละเอียดของตัวเลขสูงสุด (Excel Precision) 15 หลักเทานั้น เชน เมือ ่ พิมพเลข 12345678901234567890 ลงไปในเซลล จะพบวา Excel ปรับตัวเลขใหมเหลือเพียง 12345678901234500000 เทานั้น.

2

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

¾ Repeating Binary Numbers ตัวเลขซึ่งมักใชเปนตัวอยางใหเห็นปญหาของ Repeating Binary Numbers เชน เลข 0.1 ซึ่งเปน ผลจากการคํานวณจากสูตรใดๆ ก็ได เมื่อสรางสูตร =22.3-22.2 หรือ =2.3-2.2 จะพบวาในเซลลไดผลลัพธเปน 0.1 แตถาปรับ Format ใหแสดงทศนิยมใหเห็นหลายๆตําแหนง จะพบวา 0.1 ที่ไดนั้น ไมใช 0.1 จริงๆ สูตร =22.3-22.2 จะไดผลลัพธ 0.100000000000001 สูตร =2.3-2.2 จะไดผลลัพธ 0.0999999999999996 สาเหตุที่ครั้งแรกตัวเลขในเซลลแสดงผลลัพธ 0.1 เทานั้น เนื่องจาก Excel จะใช Format ตัวเลข ตามแบบตัวเลขที่เราพิมพลงไป ในเมื่อ 22.3 หรือ 22.2 หรือ 2.3 หรือ 2.2 ใชทศนิยมหลักเดียว จึง ทําใหผลลัพธแสดงดวยทศนิยมหนึง่ หลักตาม คา 0.1 ซึ่ง Excel คํานวณไดในเลขฐานสอง จะเปนเลข 000110011001100110011 ซ้ําไมรูจบ แตเมือ ่ แปลงกลับมาเปนเลขฐานสิบ ภายใต Precision ของตัวเลข 15 หลัก จึงตองถูกปดหรือตัดทิ้ง ใหเหลือเทาที่จะนําไปใชตอได สูตรคํานวณ 0.1 นี้เปนเพียงตัวอยางงายๆ ทีช ่ วยใหเห็นความนากลัวของ Excel ไดดี ซึ่งยังมี โอกาสอีกมากมายที่สูตรคํานวณอื่นๆ ทั้งที่เปนสูตรบวกลบคูณหารและสูตรที่เปน Function สําเร็จรูป สามารถคํานวณแลวใหผลลัพธถูกตองตามแบบของ Excel แตผิดเพี้ยนไมถูกตองตามความตองการ ของมนุษย ซึ่ง Format ไมไดชวยใหตัวเลขเกิดความแมนยําตางไปจากเดิมแตอยางใด เราตองใชสูตร

อื่นๆมาชวยปรับคาตัวเลขใหถูกตองตรงตามที่ตองการกอน จากนั้นจึงคอยใช Format ตามทีหลัง ------------------------------------------------------™ สูตร Round

สูตร Round นี้เชื่อวาผูใช Excel ทั่วไปรูจักกันดี แตไมเคยนํามาใชกันกับการคํานวณทุกเซลล เนื่องจากไมทราบเรื่อง Repeating Binary Numbers กันมากอน สูตร Round ทําหนาที่ปดตัวเลขทั้งเลขหลักทศนิยมหรือเลขหลักอื่น ใหเหลือคาเทาจํานวนหลักที่ ตองการ =Round( ตัวเลข, จํานวนหลัก) มาดูคําอธิบายพรอมตัวอยางกันครับ จํานวนหลักในสูตร ถาเปนเลขบวก เชน =Round(1234.567,2) จะปดหลักทศนิยมใหเปนแค 2 หลัก จึงไดคาเปน 1234.57 (เนื่องจากทศนิยมหลักที่ 3 เปนเลข 7 ซึ่งมากกวาหรือเทากับเลข 5 จึง ปดขึ้น)

3

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

จํานวนหลักในสูตร ถาเปนเลข 0 เชน =Round(1234.567,0) จะปดหลักทศนิยมใหเปนแค 0 หลัก จึงไดคาเปน 1235.00 (เนื่องจากทศนิยมหลักที่ 1 เปนเลข 5 ซึ่งมากกวาหรือเทากับเลข 5 จึงปดขึ้น)

จํานวนหลักในสูตร ถาเปนเลขลบ เชน =Round(1234.567,-2) จะปดตัวเลขหลักหนวยและสิบให เปนหลักรอย จึงไดคาเปน 1200.00 (เนื่องจากเลขหลักที่ 2 เปนเลข 3 ซึ่งไมมากกวาหรือเทากับเลข 5 จึงตัดทิ้ง) ------------------------------------------------------™ สูตร Trunc สูตร Trunc ทําหนาที่ตัดตัวเลขทั้งเลขหลักทศนิยมหรือเลขหลักอื่น ใหเหลือคาเทาจํานวนหลักที่ ตองการ โดยไมตองดูวาหลักถัดไปเปนเลขใด สูตร Trunc ยอมาจากคําวา Truncate แปลวา ตัดทิ้ง

=Trunc( ตัวเลข, จํานวนหลัก) ไปดูคําอธิบายพรอมตัวอยางกันเลยครับ จํานวนหลักในสูตร ถาเปนเลขบวก เชน =Trunc(1234.567,2) จะตัดหลักทศนิยมใหเปนแค 2 หลัก จึงไดคาเปน 1234.56 จํานวนหลักในสูตร ถาเปนเลข 0 เชน =Trunc(1234.567,0) หรือ =Trunc(1234.567) จะตัดด หลักทศนิยมใหเปนแค 0 หลัก จึงไดคาเปน 1234.00 จํานวนหลักในสูตร ถาเปนเลขลบ เชน =Round(1234.567,-2) จะตัดตัวเลขหลักหนวยและสิบให เปนหลักรอย จึงไดคาเปน 1200.00 -------------------------------------------------------

4

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

™ สูตร Int สูตร Int ทําหนาที่ตัดตัวเลขหลักทศนิยม ใหเหลือเปนเลขจํานวนเต็มที่นอยกวาเลขเดิม Int มาจากคําวา Integer แปลวา จํานวนเต็ม =Int( ตัวเลข) ตัวอยางพรอมคําอธิบายครับ =Int(1234.567) จะไดผลลัพธ 1234 แตถาใชสูตรหาจํานวนเต็มของคาลบ =Int(-1234.567) จะไดผลลัพธ -1235 ------------------------------------------------------™ สูตร Mod สูตร Mod ยอมาจากคําวา Modulus แปลวา เศษที่เหลือจากการหาร ดังนั้นสูตร Mod จึงทําหนาที่ หาเศษที่เหลือจากการหารกัน

=Mod( เลขตัวตั้ง, เลขตัวหาร) ไปดูตัวอยางกันเลยดีกวา =Mod(7,3) ไดผลลัพธ 1 =Mod(7,4) ไดผลลัพธ 3 =Mod(7,5) ไดผลลัพธ 2 =Mod(1234.567,1) นาจะไดผลลัพธเฉพาะเศษ 0.567 แตกลับได 0.567000000000007 มี สวนทายเกินมา ซึ่งตองอาศัยสูตร Round หรือ Trunc ชวยแกไขอีกชั้นหนึ่ง

-------------------------------------------------------

5

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

¾ เมื่อใดที่ควรใชสูตรกอน Format เมื่อเห็นสูตรขางตน หลายคนอาจบอกวา ตนรูจักสูตรเหลานี้มานาน หรือใชเปนกันอยูแลว และเห็น วาเปนสูตรงายๆ แตที่นาคิดนาถามตัวเองก็คือ แลวตนเคยนําสูตร Round Trunc Int และ Mod มาใช กันบอยมากนอยแคไหน ในงานสําคัญซึ่งตองใชตัวเลขในการวางแผนตัดสินใจ เชน งานบัญชี งานการเงิน งานภาษีอากร ตลอดจนงานอื่นๆซึ่งมีขอจํากัดของตัวเลข ซึ่งเกิดจากขอกําหนดจากภายนอกที่เราควบคุมไมได เชน กฎเกณฑบัญชี กฎภาษีอากร หรือกฎทางการเงินที่ประกาศใหลูกคาและบุคคลภายนอกทราบ ในงาน เหลานี้จําเปนตองใชสูตรดังกลาวในทุกขั้นตอนของการคํานวณ หามละไวใชสูตรครั้งเดียวกับผลลัพธ สุดทาย เพราะจะทําใหตัวเลขที่อยูระหวางการคํานวณถูกตัดสินผิดพลาดจากกฎที่มีอยู สวนงานซึ่งใชตัวเลขเปนการภายใน สามารถคลาดเคลื่อนไดบาง และไมเกี่ยวของกับการตัดสินใจ เราไมตองใชสูตรเหลานี้เลยก็ได แตตองเปนที่ทราบกันทั่วไปวา ผลลัพธที่เห็นอาจไมตรงกับคาที่ แทจริงเสมอไป ------------------------------------------------------™ สูตร If สูตร If เปนสูตรซึ่งจะชวยในการตัดสินใจไดดีในระดับหนึ่ง และมีความสลับซับซอนอยูบาง พอสมควร จริงๆ แลวก็ไมไดยากเย็นอะไร เพียงแตการเขียนสูตรคอนขางที่จะยาว (ยิ่งหลายเงื่อนไขก็ ยิ่งยาว) เพราะตองมีการตัดสินใจเขามาเกี่ยวของดวย ทําใหเกิดความสับสนและทําใหผด ิ พลาดได งาย แตถาไดศึกษาทําความเขาใจและรูจักใชแลว สูตร If จะเปนตัวชวยที่ดีสําหรับการตัดสินใจเลย ทีเดียว โดยทั่วไปงานแทบทุกประเภท ยอมมีความเกีย ่ วของกับ การวางแผนและตัดสินใจบางไมมากก็ นอย ทําใหสูตร If เปนสูตรซึ่งถูกเรียกใชงานอยูเสมอ อีกทั้งยังชวยใหใชเซลลสูตรเพียงเซลลเดียว หาผลลัพธไดตามตองการ โดยไมจําเปนตองใชเซลลหลายเซลลสรางสูตรซ้ํา หรือใชชีทหนาตา เหมือนกันซ้ําหลายๆ ชีท หรือใชไฟลที่เหมือนกันอยางกับแกะ ซ้ําๆๆๆๆ กัน พฤติกรรมของหัวหนากับลูกนองเมื่อใชสูตร If ในการมอบหมายงาน หัวหนาตองกําหนดขอบเขตของงานใหชด ั เจน ระบุใหชัดวามีเรื่องใดบางซึ่ง อาจมีการเปลี่ยนแปลงแกไขตางไปจากวันนี้ เชน • • •

ขอบเขตระยะเวลาของการคํานวณควรแยกเปนรายเดือน รายวัน หรือรายป ชวงเวลาที่ใชอาจ เปลี่ยนจากเดิม 6 เดือน เปน 12 เดือน หรืออาจไมแนนอน ตัวแปรใดบางซึ่งอาจเปลี่ยนแปลงไปจากเดิม และตัวแปรเรื่องใด ที่ตองการใหแยกออกมาให หัวหนาหรือบุคคลอื่นรวมกันใชงานได ตัวแปรเรือ ่ งใดตองการปกปดไมใหผูอื่นเห็น วิธีคํานวณมีเงื่อนไขอะไรบาง จะมีการเปลี่ยนแปลงสูตรคํานวณในอนาคตหรือไม อยางไร

6

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

รายละเอียดขางตนเปนขอมูลอยางนอยที่สุด สําหรับหัวหนาซึ่งใช Excel ไมคอยเปน ควรระบุให ชัดเจนในการมอบหมายงาน สวนหัวหนาซึ่งใช Excel เกงมากๆ อาจสรางงานใหดูเปนตัวอยาง และ มอบหมายใหลูกนองปรับปรุงแกไขไฟลนั้นตอไป หัวหนาควรใหเวลาสําหรับลูกนอง ในการใชคิดวางแผนการสรางงาน ใหสามารถใชเวลาได มากกวาปกติ แตเมื่อสรางงานเสร็จแลว พอมีการเปลี่ยนแปลงเงื่อนไขตัวแปร หรือแมแตแกไขสูตร จะตองใชเวลานอยมาก ไมจําเปนตองทํางาน OT อาจเรียกไดวา เมื่อหัวหนาสั่งแกไข ลูกนองก็ สามารถสงงานไดทันทีเกือบจะในพริบตา ถาลูกนองเกงมากๆ สามารถสรางงานไดดี มีความยืดหยุน  สามารถใช Excel สรางงานซึง่ สนองตอบตอความตองการที่ตางไปจากเดิมไดสารพัด จะทําใหหัวหนาที่ใช Excel ไมคอยเปน แทบ ไมตองกวนลูกนองใหปรับตัวเลข หรือสั่งใหแกไขอะไรใหมใหอก ี เลย หัวหนานัน ่ แหละ จะใชไฟล ตอไปไดเอง หัวหนาจะรูสึกสนุกกับการที่ตนสามารถใช Excel เปลี่ยนแปลงตัวแปรและเงื่อนไขที่ เกี่ยวของ แลวพิจารณาทางเลือกทางธุรกิจจากผลการคํานวณไดดวยตนเอง

โครงสรางสูตร If =If(เงื่อนไข, ผลกรณีถูกเงื่อนไข, ผลกรณีผิดเงื่อนไข) สูตร If เปนสูตรแรกที่ควรคิดถึง ถาเงื่อนไขที่เกี่ยวของมีความสลับซับซอนไมมาก เพราะสูตรทุก สูตรมีขอจํากัดวา ภายในวงเล็บหนึ่ง จะมีสูตรอยูภายในวงเล็บไดไมเกิน 7 สูตร ดังนั้นสูตร If จึง สามารถสรางสูตร If ซอนกันไดไมเกิน 7 ชั้น รวมสูตร If นอกวงเล็บดวยจึงนับ If ได 8 If =If(A1=1,"A",If(A1=2,"B",If(A1=3,"C",If(A1=4,"D", If(A1=5,"E", If(A1=6,"F",If(A1=7,"G",If(A1=8,"H","I" )))))))) ใหคลิกในชอง Formula Bar หนาตําแหนงที่ตองการใหปรับขึ้นบรรทัดใหม เชน หนา If แลวกด Alt+Enter จะไดสูตรที่อานไดงายกวาเดิม =If(A1=1,"A", If(A1=2,"B", If(A1=3,"C", If(A1=4,"D", If(A1=5,"E", If(A1=6,"F", If(A1=7,"G", If(A1=8,"H","I")))))))) หากพยายามนําสูตรซอนเขาไปอีก จะพบคําเตือนวา The formula you typed contains error โดย Excel จะไมบอกหรอกวา สาเหตุเปนเพราะซอนสูตรหลายชั้นเกินกวาที่จะรับได ผูที่สรางสูตรตอง เขาใจเองวาเปนเพราะมีสูตรเกินกวา 7 สูตรในวงเล็บ

7

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

ประเด็นที่ตองคํานึงเวลาซอนสูตร If ก็คือ ตองกําหนดเงือ ่ นไขในสูตร If ที่เปดโอกาสให เงื่อนไขทั้งหมดมีโอกาสไดใชงาน เชน ถาเงื่อนไขของ If ตัวแรก ใชตรวจสอบตัวเลขที่มากกวา หรือเทากับ 5 เงื่อนไขของ If ตัวถัดไป ใชตรวจสอบตัวเลขที่มากกวาหรือเทากับ 4, 3, 2, 1 ได ตามลําดับ แตถากําหนดเงื่อนไขแรกใหตรวจสอบตัวเลขที่มากกวาหรือเทากับ 1 เสียแลว จะปดโอกาสกัน ไมใหเงื่อนไขใน If ที่ซอนตัวถัดไปไดใชงาน

วิธีซอน If ใหเกินกวา 7 ชั้น ใหนําสูตร If ที่ซอนกันจนเต็มแลวแตละชุด นํามาตอกัน โดยใชเครือ ่ งหมาย & หรือนํามาบวกกัน หากผลลัพธของสูตรเปนตัวอักษร ใหนํา If ที่ซอน If กันจนเต็มแลวแตละชุด มาตอกันโดยใช เครื่องหมาย & เปนตัวเชื่อม โดยตองกําหนดใหเงื่อนไขตัวสุดทาย ในกรณีที่ผิดเงื่อนไขนั้น กลายเปน ชองวาง โดยใชเครื่องหมาย "" (Null Text) เชน ตามตัวอยาง If ซอนกันขางตนนัน ้ หากตองการ ตรวจสอบวา ถา A1 มีคาเปนเลข 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, หรือ 13 แลวใหสูตร If คืน คาเปนตัวอักษร A, B, C, D, E, F, G, H, I, J, K, L, หรือ M ตามลําดับ ตองใชสูตร If ตอกัน ดังนี้ =IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D", IF(A1=5,"E", IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H","" )))))))) & IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12, L", IF(A1=13,"M",""))))) หากผลลัพธของสูตรเปนตัวเลข ใหนํา If ที่ซอน If กันจนเต็มแลวแตละชุด มาบวกกัน โดย ตองกําหนดใหเงื่อนไขตัวสุดทาย ในกรณีที่ผิดเงื่อนไขนั้น กลายเปนเลข 0 เชน หากตองการ ตรวจสอบวา ถา A1 มีคาเปนตัวอักษร A, B, C, D, E, F, G, H, I, J, K, L, หรือ M แลวใหสูตร If คืน คาเปนตัวเลข 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, หรือ 13 ตามลําดับ ตองใชสูตร If ตอกัน ดังนี้ =IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4, IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,0)))))))) + IF(A1="I",9,IF(A1="J",10,IF(A1="K",11,IF(A1="L",12, IF(A1="M",13,0)))))

If ที่ไมตองใชสูตร If เงื่อนไขหนึ่งซึ่งใชกันบอยครั้ง ไดแก การควบคุมคาที่คํานวณไดไมใหต่ํากวาคาที่กําหนด หรือ หามเกินกวาคาสูงสุดที่กําหนดไว อาจกําหนดเปนชวงในเงื่อนไขของอัตราภาษี หรือใชในการคํานวณ ตนทุนขาย หรือใชกันแบบงายๆ เชน หามต่ํากวา 0 หรือ หามเกินกวา 0 เปนตน ถาคิดจะใชสูตร If ตองเขียนสูตรดังนี้

8

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

กรณีหามนําคาที่ต่ํากวา 0 ในเซลล A1 ไปใช =If( A1<=0, 0, A1) กรณีหามนําคาที่เกินกวา 100 ในเซลล A1 ไปใช =If( A1>=100, 100, A1) แทนที่จะใชสูตร If ซึ่งตองเสียเวลากําหนดทั้งเงื่อนไขและผลลัพธที่ตองการซ้ําลงไปในสูตร If ใหเปลี่ยนมาใชสูตร Max หรือ Min แทน โดยใหยึดหลักวา ถาตองการควบคุมคา ไมใหตา ่ํ กวา ใหใชสูตร Max ถาตองการควบคุมคา ไมใหสูงกวา ใหใชสูตร Min จํางายๆวา ใหใชสูตรที่ตรงกันขามกับทิศทางของการควบคุม ทิศต่ํากลับใช Max สวนทิศสูงกลับ ใชสูตร Min กรณีหามนําคาที่ต่ํากวา 0 ในเซลล A1 ไปใช =Max(0, A1) กรณีหามนําคาที่เกินกวา 100 ในเซลล A1 ไปใช =Min(100, A1)

นอกจากนี้ยงั สามารถใชสูตรคูณมาบวกกันธรรมดา คํานวณไดคําตอบแบบเดียวกันไดอีก กรณีหามนําคาที่ต่ํากวา 0 ในเซลล A1 ไปใช =(A1<=0)*0 + (A1>0)*A1 กรณีหามนําคาที่เกินกวา 100 ในเซลล A1 ไปใช =(A1>100)*0 +(A1<=100)*100 สูตรคํานวณแบบนีใ้ ชหลักวา เมือ ่ นําคามาตรวจสอบเงื่อนไขใสไวในวงเล็บ (โดยไมจําเปนตอง อาศัยสูตร If) ถาเงื่อนไขเปนจริง เมื่อนําไปคํานวณตอ Excel จะถือวามีคาเทากับ 1 แตถาเงือ ่ นไขเปน เท็จ เมื่อนําไปคํานวณตอ Excel จะถือวามีคาเทากับ 0

9

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

แบบสูตรจะดูวายาวกวาสูตร If Max หรือ Min ก็ตาม แตถามีเงื่อนไขซับซอนมากขึ้น จะกลายเปน สูตรที่สั้นกวา เชน หากตองการตรวจสอบวา ถา A1 มีคาเปนตัวอักษร A, B, C, D, E, F, G, H, I, J, K, L, หรือ M แลวใหสูตร If คืนคาเปนตัวเลข 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, หรือ 13 ตามลําดับ ตองใชสูตร If ตอกัน ดังนี้ =IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4, IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,0)))))))) + IF(A1="I",9,IF(A1="J",10,IF(A1="K",11,IF(A1="L",12, IF(A1="M",13,0))))) เมื่อปรับใหมกลายเปนสูตรสั้นลง และมีโครงสรางซึ่งสามารถปรับแกไขไดงายขึ้น =(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4 +(A1="E")*5+(A1="F")*6+(A1="G")*7+(A1="H")*8 +(A1="I")*9+(A1="J")*10+(A1="K")*11+(A1="L")*12 +(A1="M")*13

------------------------------------------------------¾ แถมทายดวยกับดัก Error ในทันทีที่เซลลแสดง Error ซึ่งนําหนาดวยเครื่องหมาย # ขึ้นมา เชน #DIV/0! #N/A #NAME? #NULL! #NUM! #REF! #VALUE! งานที่ใช Excel ตั้งใจสรางขึ้นอยางดี อาจกลายเปนไรคา เพราะดู แลวเกิดสงสัยวา สูตรคํานวณถูกตองอยางที่ตองการหรือไม ทั้งๆที่ Error ที่แสดงขึ้นมาเหลานี้ ไมได หมายความวา สูตรที่สรางขึ้นนั้นผิดพลาดเสมอไปก็ตาม แตผูใช Excel ซึ่งไมรูจักคําเตือน Error ดี เมื่อเห็น Error แสดงขึ้น ก็มก ั ตกอกตกใจ และพาลหาเรื่องกับผูที่สรางงานขึ้นมาทีเดียววา งานที่คุณ สรางขึ้นมานั้นผิด Error ไมไดแสดงขึ้นเพื่อเตือนวามีสิ่งผิดพลาดเสมอไป แตอาจแสดงขึ้นตามปกติ เมื่อสูตรที่สราง ขึ้นไมสามารถคํานวณ หรือคํานวณแลวพบสิ่งที่นาสังเกต ตองเตือนใหผูใชทราบไวกอน #DIV/0! เตือนเมื่อสูตรไมสามารถใชเลข 0 มาเปนตัวหาร เชน =5/0 #N/A เตือนเมื่อสูตร Lookup คนหาคาที่ตองการไมพบ เชน =Match(MyData, MyRange, 0) แตไมมี MyData #NAME? เตือนเมื่อสูตรใชชอ ื่ เซลล แตไมพบชื่อที่ตั้งไว เชน =One+Two แตไมมี Range Name ชื่อ One กับ Two #NULL! เตือนเมื่อสูตรอางถึงตําแหนงเซลลที่ไมมีตัวตน เชน =(F:F H:H) เมื่อ Column F ไมไดตัดกับ Column H

10

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

#NUM! เมื่อสูตรใชตัวเลขที่เปนไปไมไดในการคํานวณ เชน =Sqrt(-1) #REF! เมือ ่ ตําแหนงเซลลที่อางในสูตร เซลลที่อางถูกลบทิ้ง #VALUE! เมื่อสูตรไมสามารถคํานวณหาคาเปนตัวเลขได เชน =5/"Test" หากเซลลใดเซลลหนึ่งที่ใชในสูตร เกิด Error ขึ้นมา ถาผูกสูตรตอกันไปทั้งตาราง จะสงผลให เซลลอื่นซึ่งนําคาจากเซลล Error ไปคํานวณตอ เกิด Error ตามไปดวย ดังนัน ้ แทนที่จะปลอยใหการ เกิด Error ของเซลลใดเซลลหนึ่ง แลวสงผลเสียตองานทั้งหมด จึงควรหาทางปองกันไมใหเกิด Error ขึ้นตั้งแตตน ซึ่งเรียกวา วิธีสรางกับดัก Error (Error Trapping) โดยใชสูตร IF ตรวจสอบเงื่อนไขซึ่ง อาจทําใหเกิด Error กอน เชน เดิมสรางสูตรหาร =A1/A2 แตถา A2=0 จะทําใหสูตรหารนี้แสดง Error #DIV/0! ซึ่งตองปองกัน ไมใหแสดง Error โดยใชสูตรตอไปนี้แทนสูตร =A1/A2 =IF(A2=0, 0, A1/A2) ถา A2=0 จะทําใหสูตร If คืนคาเปน 0 แทนไปกอน แตถา A2 ไมเทากับ 0 จะคํานวณไดผลลัพธ ตามสูตรเดิม =A1/A2 ไมควรใชสต ู ร =IF(A2=0, "", A1/A2) เพราะเครื่องหมาย Null Text "" นั้นถือวาเปน Text ไม สามารถนําคาไปคํานวณตอ แตเนือ ่ งจากสูตรหารนัน ้ อาจเกิด Error ขึ้นอีกไดเสมอเมือ ่ ตัวหารเปนตัวอักษร จึงแนะนําใหแกไข สูตร If โดยใชสูตร IsError ชวย ดังนี้ =If( IsError(A1/A2), 0, A1/A2) ถาสูตร A1/A2 ทําใหเกิด Error จะทําใหสูตร If คืนคาเปน 0 แทนไปกอน แตถาสูตร A1/A2 ไม เกิด Error จะคํานวณไดผลลัพธตามสูตรเดิม =A1/A2

(แตกอนที่จะนําสูตร A1/A2 มาใช จะตองตรวจสอบสูตรกอนวาคํานวณไดถูกตอง เพราะเมื่อนําสูตร IsError มาชวยปองกัน จะทําใหจากนี้ไปจะไมมีทางพบ Error แสดงขึ้นมาใหเห็นอีกเลย)

11

Expert Excel Tips : เทคนิคการใชงาน Excel อยางมืออาชีพ. By Mr. Pongthap Reawruad ([email protected]) Mobile : 081‐636‐7625

สูตรกลุม IS ซึ่งสามารถนํามาใชกับสูตร If ในการปองกัน Error เชน =IsError(สูตร) ตรวจสอบ Error ทุกประเภท =IsErr(สูตร) ตรวจสอบ Error เกือบทุกประเภท เวน #N/A =IsNA(สูตร) ตรวจสอบ Error #N/A เทานั้น =IsText(เซลล) ตรวจสอบวาเซลลมีคาเปน Text หรือไม =IsNumber(เซลล) ตรวจสอบวาเซลลมีคาเปน Number =IsBlank(เซลล) ตรวจสอบวาเซลลเปนเซลลวางหรือไม -------------------------------------------------------

12