Video ด้านล่างนี้ได้อธิบายความหมายของแต่ละฟังก์ชั่นข้างต้น พร้อมสาธิตวิธีการใช้งาน เพื่อเป็นแนวทางในการจัดทำข้อมูลให้เกิดความสะดวก รวดเร็ว เหมาะกับทุกสาขาอาชีพ
แสดงบทความที่มีป้ายกำกับ Counta แสดงบทความทั้งหมด
แสดงบทความที่มีป้ายกำกับ Counta แสดงบทความทั้งหมด
วันอาทิตย์ที่ 24 กันยายน พ.ศ. 2560
ฟังก์ชั่น Count, Counta, Countif, Sumif และ Match
วันอาทิตย์ที่ 5 กุมภาพันธ์ พ.ศ. 2560
ฟังก์ชั่นพื้นฐานที่ใช้บ่อย
แม้จะเป็นระดับการประยุกต์การใช้งานที่ซับซ้อนก็ตาม เราก็ต้องอาศัยฟังก์ชั่นเหล่านี้เป็นหลัก ซึ่งได้แก่
✏ Min หาค่าที่น้อยที่สุด เช่น Min(A1:C10)
✏ Max หาค่าที่มากที่สุด เช่น Max(D8:E20)
✏ Average หาค่าเฉลี่ย เช่น Average(B5:F50)
✏ Count นับว่ามีตัวเลขกี่ตัว เช่น Count(I5:K8)
✏ Counta นับว่าไม่เป็นเซลล์ว่างกี่เซลล์ เช่น Counta(H8:V3)
✏ Countifs นับตามเงื่อนไข เช่น Countifs(A2:A5,"OK",B2:B5,1) เป็นการนับว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Sumifs รวมตามเงื่อนไข เช่น Sumifs(C2:C5,A2:A5,"OK",B2:B5,1) เป็นการรวมค่าใน C2:C5 โดยมีเงื่อนไขว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Left เป็นการตัดอักขระด้านซ้าย เช่น Left(A2,8) เป็นการตัดอักขระด้านซ้ายของเซลล์ A2 มา 8 อักขระ
✏ Right เป็นการตัดอักขระด้านขวา เช่น Right(B2,4) เป็นการตัดอักขระด้านขวาของเซลล์ B2 มา 4 อักขระ
✏ Mid เป็นการตัดอักขระโดยระบุตำแหน่งเริ่มและจำนวนอักขระที่ตัด เช่น Mid(D2,3,4) เป็นการตัดอักขระในเซลล์ D2 โดยเริ่มอักขระที่ 3 มาจำนวน 4 อักขระ
✏ Len เป็นการนับจำนวนอักขระ เช่น Len(C8) เป็นการนับว่าในเซลล์ C8 มีกี่อักขระ
✏ If เป็นการพิจารณาตามเงื่อนไขว่าจริงหรือเท็จ เช่น If(A2=5,"Yes","No") เป็นการพิจารณาว่า A2 เท่ากับ 5 จริงหรือไม่ หากเป็นจริงให้แสดงคำตอบเป็น Yes หากเป็นเท็จให้แสดงคำตอบเป็น No
✏ Vlookup เป็นการค้นหาค่าใด ๆ ในคอลัมน์แรกของตารางแล้วแสดงผลลัพธ์เป็นค่าในคอลัมน์ใด ๆ ของตารางตามที่กำหนด เช่น Vlookup("Finish",B3:F100,2,0) เป็นการค้นหาคำว่า Finish ใน B3:B100 (คอลัมน์แรกของตาราง) แล้วแสดงผลลัพธ์เป็นค่าใน C3:C100 (คอลัมน์ที่ 2 ของตาราง) หากไม่พบคำว่า Finish ใน B3:B100 จะให้ผลลัพธ์เป็น #N/A
✏ Sumproduct เป็นการหาผลรวมของผลคูณ เช่น Sumproduct(A2:A4,B2:B4) เป็นการหาผลรวมของผลคูณของ A2:A4 และ B2:B4 ลักษณะการทำงานคือ A2*B2+A3*B3+A4*B4
✏ Index เป็นการระบุพื้นที่แล้วแสดงผลลัพธ์ในตำแหน่งที่กำหนด เช่น Index(A3:C8,1,3) เป็นการนำบรรทัดที่ 1 คอลัมน์ที่ 3 ของพื้นที่ A3:C8 มาแสดง ค่าผลลัพธ์คือ C3
✏ Match เป็นการค้นหาค่าใด ๆ ในช่วงแล้วแสดงผลลัพธ์เป็นลำดับที่พบ เช่น Match(True,B5:B10,0) เป็นการหาว่าค่า True อยู่ในลำดับที่เท่าไรของช่วง B5:B10 หากไม่พบค่า True จะแสดงผลลัพธ์เป็น #N/A
✏ Indirect เป็นการอ้างอิงโดยอ้อม เช่น B2 มีค่าเท่ากับ 5, A10 มีค่าเท่ากับ B2 เซลล์ใด ๆ คีย์ Indirect("A10") คำตอบจะได้ 5 ลักษณะการทำงานของสูตรคือ หาก่อนว่าค่า A10 คืออะไร เมื่อพบว่า A10 คือ B2 ฟังก์ชั่นนี้จึงแสดงคำตอบที่เป็นค่าใน B2 สังเกตว่าค่าที่จะคีย์ลงไปใน Indirect ได้จะต้องเป็นการอ้างอิงไปยังปลายทางที่เป็นเซลล์หรือช่วงเซลล์เท่านั้น
✏ Offset เป็นการระบุตำแหน่งเซลล์โดยมีจุดเริ่ม ตำแหน่งบรรทัดที่ถัดจากจุดเริ่ม ตำแหน่งคอลัมน์ที่ถัดจากจุดเริ่ม ความสูงของข้อมูล และความกว้างของข้อมูล เช่น Offset(B4,0,0,1,5) เป็นการระบุช่วงข้อมูลโดยเริ่มที่ B4 ตำแหน่งบรรทัดที่ห่างจาก B4 เป็น 0 ตำแหน่งคอลัมน์ที่ห่างจาก B4 เป็น 0 แสดงว่ายังอยู่ที่จุดเริ่มเช่นเดิมคือ B4 ความสูงของข้อมูลเป็น 1 ความกว้างของข้อมูลเป็น 5 แสดงว่าจาก B4 ขยายไปทางขวา 5 คอลัมน์ ผลลัพธ์จะได้เป็นช่วงเซลล์ B4:F4
สิ่งสำคัญอย่างหนึ่งสำหรับการใช้งานฟังก์ชั่นได้อย่างคล่องแคล่วคือการฝึกฝนใช้งานบ่อย ๆ หากเข้าใจและใช้งานฟังก์ชั่นตามด้านบนนี้ได้เป็นอย่างดี จะสามารถนำไปพัฒนาต่อยอดเพื่อหาคำตอบที่ซับซ้อนขึ้นเรื่อย ๆ ได้อย่างไร้กังวลครับ
มีปัญหาการใช้งาน Microsoft Excel and VBA สอบถามได้ที่ snasui.com
Revised: December 23, 2017 at 12:04
วันศุกร์ที่ 10 มิถุนายน พ.ศ. 2554
การนำข้อมูลที่อยู่ในคอลัมน์เดียวไปเรียงหลายคอลัมน์
สำหรับการนำข้อมูลที่เรียงข้อมูลในคอลัมน์เดียวไปเรียงใหม่ให้อยู่ในหลายคอลัมน์ โดยกำหนดได้ว่าแต่ละคอลัมน์มีข้อมูลเพียงกี่บรรทัดเราสามารถใช้สูตรมาจัดการได้ครับ
ตัวอย่างเช่นข้อมูลอยู่ที่ A2:A100 และต้องการเรียงใหม่ที่คอลัมน์ B เริ่มจาก B2 เป็นต้นไปโดยสามารถเลือกได้ว่าแต่ละคอลัมน์จะให้มีข้อมูลเพียงกี่บรรทัด
สามารถทำได้ดังนี้
- ที่เซลล์ B1 ทำ Validation เพื่อเลือกจำนวนบรรทัดที่ต้องการ
- ที่เซลล์ B2 คีย์สูตรเพื่อจัดเรียงข้อมูลใหม่
=IF(ROWS(B$2:B2)>$B$1,"",IFERROR(INDEX($A$2:$A$100,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))),"")
Enter > Copy ไปด้านขวาและลงด้านล่างตามต้องการ
สูตรข้างต้นใช้กับ Excel Version 2007 เป็นต้นไป ส่วน Excel 2003 สามารถใช้สูตรตามด้านล่างครับ
=IF(OR(ROWS(B$2:B2)>$B$1,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))>COUNTA($A$2:$A$100)),"",INDEX($A$2:$A$100,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))))
Enter > Copy ไปทางด้านขวาและลงด้านล่าง
ภาพประกอบการนำข้อมูลที่อยู่ในคอลัมน์เดียวไปเรียงหลายคอลัมน์
วันพุธที่ 9 ธันวาคม พ.ศ. 2552
การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว
สำหรับข้อมูลที่อยู่ในฟอร์มเดียวกันแต่แยกเป็นหลาย Sheet และต้องการนำมาต่อกันเป็นฐานข้อมูลใน Sheet เดียวกันนั้น สามารถใช้สูตรทำได้ครับ แต่อาจจะมีวิธีการหลายขั้นตอน ดังจะแสดงตามด้านล่าง
ภาพ Sheet ที่ใช้รวมข้อมูล 
ภาพ Sheet ที่ 1 
ภาพ Sheet ที่ 2 
ภาพ Sheet ที่ 3 
จากภาพด้านบนเป็นการนำข้อมูล Sheet1, 2, 3 มาต่อกัน ให้ทำตามลำดับดังนี้ครับ
- เขียนชื่อชีทเรียงกันไว้ตามต้องการที่ G1:G3
- เซลล์ H1 คีย์สูตรเพื่อนับว่ามีข้อมูลใน Sheet1 เท่าไร
=COUNTA(INDIRECT("'"&G1&"'!"&"A2:A65536"))
Enter แล้ว Copy ลงด้านล่าง - เซลล์ H4 รวมจำนวนจากด้านบน คีย์
=SUM(H1:H3)
Enter - เซลล์ I1 ใช้สูตรในการ Run ข้อมูลใหม่เพื่อใช้ในสูตร Lookup
=SUM($H$1:H1)-H1+1
Enter แล้ว Copy ลงด้านล่าง - เซลล์ E2 คีย์สูตรเพื่อ List รายชื่อ Sheet
=IF(ROWS($E$2:E2)>$H$4,"",LOOKUP(ROWS($E$2:E2),$I$1:$I$3,$G$1:$G$3))
Enter แล้ว Copy ไปด้านล่าง - เซลล์ A2 คีย์สูตรเพื่อนำข้อมูลใน Sheet ต่าง ๆ มาแสดงต่อกัน
=IF($E2<>"",INDIRECT("'"&$E2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($E$2:$E2,$E2)+1),"")
Enter แล้ว Copy ไปทางขวาและลงด้านล่าง
จะเห็นว่าการนำข้อมูลจากหลาย ๆ Sheet มาต่อกันนั้น ไม่ใช่เรื่องยากอีกต่อไปสำหรับพวกเราครับ
สมัครสมาชิก:
บทความ (Atom)