Page 1 of 1
ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผลรวม
Posted: Fri Jun 07, 2013 3:03 pm
by sings
ผมจะใช้สูตรหรือเทคนิคอะไรในการที่ต้องการ สรุปข้อมูลในจังหวัดมีกี่ อำเภอ จำนวนเท่าไร และ มีขนาดพื้นที่ ไม่เกิน 10 และ มากกว่า 10 แต่ไม่เกิน 50 และมากกว่า 50 ลงในชีลที่ 2 ครับ
ปกติถ้าผมหาข้อมูลแบบจะใช้ วิธี Sort&Filter มานั้งนับเอาคิดว่าช้ามากน่ะครับ ถ้าน้อยมันก็ว่าไป แต่ข้อมูลที่จะทำนี้มันนับหมื่นๆ นะครับ โปรด ช่วยชี่แนะที่ครับ
ปล. ผมไม่แน่ใจน่ะว่าสร้างตารางมาตรงกับคำถามที่ต้องการหรือป่าว รู้สึกขัดๆอยู่เหมือนกับกับคำถาม แต่โดยสรุป ผมต้องการตารางในแบบ ชีลที่2 น่ะครับ
ขอบคุณครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Fri Jun 07, 2013 3:31 pm
by nattasiray
สิ่งแรกที่ต้องทำคือ คัดลอกฟิลด์ ตำบล อำเภอ จังหวัด ขนาดพื้นที่ พร้อมข้อมูลไปอยู่ที่เวิร์คชีตใหม่ครับ หากมีข้อมูลเพิ่ม ค่อยใช้ VBA ควบคุมการตัดเฉพาะข้อมูลที่เพิ่มขึ้นมานำไปต่อท้ายตารางไดนามิกส์
หลังจากนั้นสร้างตารางไดนามิกส์ กระทำตามขั้นตอนดังนี้
1 คลิกลงในตารางใหม่
2 คลิกแท็บ Home
3 คลิกปุ่ม Format As Table
4 คลิกเลือก Light แบบสีเทาสีขาวสลับบรรทัด
5 ใส่เครื่องหมายถูกหน้า My table has Header
6 คลิก OK
สุดท้ายสร้าง PivotTable กระทำดังนี้
1 คลิกแท็บ Insert
2 คลิก ปุ่ม PivotTable
3 คลิกปุ่ม OK
4 ไปที่กรอบ Pivottable Field List
5 ลากฟิลด์ "จังหวัด" ไปปล่อยที่ช่อง Rows
6 ลากฟิลด์ "ขนาดพื้นที่" ไปปล่อยที่ Columns
7 ลากฟิลด์ "จังหวัด" ไปปล่อยที่ช่อง Values
8 ในตารางไพวอตเทเบิล คลิกขวาที่บรรทัดที่มีตัวเลขแล้วคลิกเลือก Group...
9 ที่ช่อง Start At กำหนดด้วยเลข 10
10 ที่ช่อง Start At กำหนดด้วยเลข 60
แล้วดูผล
หากมีข้อมูลเพิ่มให้ คลิกขวาที่ตารางแล้วคลิกเลือก Refresh ครับ
ลองหาหนังสือ จัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล ๒๐๑๐ มาอ่านด้วยครับ มีหัวข้อ การสร้างตารางไดนามิกส์และการสร้าง PivotTable อธิบายอยู่ด้วย
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Fri Jun 07, 2013 3:52 pm
by sings
nattasiray wrote:สิ่งแรกที่ต้องทำคือ คัดลอกฟิลด์ ตำบล อำเภอ จังหวัด ขนาดพื้นที่ พร้อมข้อมูลไปอยู่ที่เวิร์คชีตใหม่ครับ หากมีข้อมูลเพิ่ม ค่อยใช้ VBA ควบคุมการตัดเฉพาะข้อมูลที่เพิ่มขึ้นมานำไปต่อท้ายตารางไดนามิกส์
หลังจากนั้นสร้างตารางไดนามิกส์ กระทำตามขั้นตอนดังนี้
1 คลิกลงในตารางใหม่
2 คลิกแท็บ Home
3 คลิกปุ่ม Format As Table
4 คลิกเลือก Light แบบสีเทาสีขาวสลับบรรทัด
5 ใส่เครื่องหมายถูกหน้า My table has Header
6 คลิก OK
สุดท้ายสร้าง PivotTable กระทำดังนี้
1 คลิกแท็บ Insert
2 คลิก ปุ่ม PivotTable
3 คลิกปุ่ม OK
4 ไปที่กรอบ Pivottable Field List
5 ลากฟิลด์ "จังหวัด" ไปปล่อยที่ช่อง Rows
6 ลากฟิลด์ "อำเภอ" ไปปล่อยที่ช่อง Rows ต่อท้ายจังหวัด
7 ลากฟิลด์ "ตำบล" ไปปล่อยที่ช่อง Rows ต่อท้ายอำเภอ
8 ลากฟิลด์ "ขนาดพื้นที่" ไปปล่อยที่ Columns
9 ลากฟิลด์ "จังหวัด" ไปปล่อยที่ช่อง Data
10 ในตารางไพวอตเทเบิล คลิกขวาที่บรรทัดที่มีตัวเลขแล้วคลิกเลือก Group...
11 ที่ช่อง Start At กำหนดด้วยเลข 10
12 ที่ช่อง Start At กำหนดด้วยเลข 60
ดูผล
หากมีข้อมูลเพิ่มให้ คลิกขวาที่ตารางแล้วคลิกเลือก Refresh ครับ
ลองหาหนังสือ จัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล ๒๐๑๐ มาอ่านด้วยครับ มีหัวข้อ การสร้างตารางไดนามิกส์และการสร้าง PivotTable อธิบายอยู่ด้วย
โทษครับขอถามหน่อย ตารางไดนามิกส์ และ PivotTable คืออะไร มันใช้ทำอะไร แล้วเข้าตรงไหน ตอนใช้ 2003 ภาษาไทย ผมก็ใช้เป็นแค่งูๆปลาๆ น่ะครับพึ่งจะเปลี่ยนมาใช้ 2010 เมนูอังกฤตลวนๆ เลย ก็วันนี้เองน่ะครับ
ขอบคุณครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Fri Jun 07, 2013 5:51 pm
by nattasiray
ลองค้นหาใน Youtube ด้วยคำว่า
PivotTables ครับ สำหรับตารางไดนามิกส์ ให้ค้นหาด้วยคำว่า
Table Excel ครับ
การสร้างตารางไดนามิกส์ใน MS Excel 2003 จะใช้คำสั่ง Data -> List -> Create List ครับ ส่วนใน MS Excel 2007 ขึ้นมาใช้คำสั่ง Insert -> Table ครับ
หากดูแล้วไม่เข้าใจ หนังสือ "จัดการฐานข้อมูลด้วย Microsoft Excel 2010" ที่ผมแนะนำให้จะช่วยคุณได้ครับ
http://www.se-ed.com/product/จัดการฐานข ... 6160811564
หนังสือเล่มดังกล่าวใช้การเรียนรู้แบบอ่านไปทำไปครับ จะเข้าใจกว่า
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Mon Jun 10, 2013 1:05 pm
by sings
ขอบคุณครับ เดียวจะลองไปยืมที่ห้องสมุดว่าจะมีป่าวครับ
ปล. ผมก็นึกว่าจะมีสูตรแบบ ง่ายๆน่ะครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Mon Jun 10, 2013 2:40 pm
by snasui
สามารถทำได้ด้วย PivotTable ซึ่งไม่ได้ยากเกินไปครับ
ดูตัวอย่างการใช้ PivotTable ตามไฟล์แนบครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Tue Jun 11, 2013 2:12 pm
by sings
ขอบคุณครับ
ตอนนี้ผมก็ได้ลองเล่นๆ ดูครับ ผมได้ทำเป็น 2 วิธี
1.วิธีแรก ผมใช้สูตร SUMPRODUCT แต่ใช้แล้วค่ามันออกมาเป้น ศูนย์ ก็ งงๆ แหะ ส่วนการจะสรุปผล ในช่องตัวเลขไม่รู้ว่าใช้ SuM ตัวไหนครับดังไฟล์ที่แนบนี้ครับ ตัวอย่างสรุป
2. วิธีที่ 2 ผมใช้ตามที่แนะนำ ไดนามิกส์ และ PivotTable แต่ผมติดที่ ข้อ 5 ครับ ดังไฟล์ที่แนบนี้ครับ ตัวอย่างสรุป1
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Tue Jun 11, 2013 2:37 pm
by nattasiray
แล้วตารางสรุปผลอยู่ไหนล่ะครับ ผมเปิดมาดูทั้ง ๒ ไฟล์ก็ไม่พบ
เรื่องติดข้อ ๕ คือข้อนี้ใช่ไหมครับ
5 ลากฟิลด์ "จังหวัด" ไปปล่อยที่ช่อง Rows
ถ้าลากไม่ได้ ก็คลิกขวาที่ชื่อฟิลด์ในกรอบ Choose Field to add report แล้วคลิกเลือก Add to Row Labels ก็ได้ครับ
เรื่องการใช้สูตร นับอย่างมีเงื่อนไข หาค่าเฉลี่ยอย่างมีเงื่อนไข,หาผลรวมอย่างมีเงื่อนไข มีหลักการเลือกใช้ดังนี้
1 ถ้าเงื่อนไขมีเพียงเงื่อนไข
เดียวให้ใช้สูตร
COUNTIF SUMIF AVERAGEIF
2 ถ้าเงื่อนไขมีมากกว่า ๒ เงื่อนไข โดยเงื่อนไขที่ใช้ตรวจสอบนั้น
ไม่ได้อยู่ในฟิลด์เดียวกัน สามารถใช้
SUMIFS COUNTIFS AVERAGEIFS ได้
2 ถ้าเงื่อนไขมีมากกว่า ๒ เงื่อนไข โดยเงื่อนไขที่ใช้ตรวจสอบนั้น
อยู่ในฟิลด์เดียวกัน ให้ใช้
SUMPRODUCT แทน
ในหนังสือจัดการฐานข้อมูลด้วย Microsoft Excel 2010 ก็มีเรื่องสูตรดังกล่าวอยู่ครับ ลองศึกษาดูครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Tue Jun 11, 2013 4:58 pm
by sings
nattasiray wrote:แล้วตารางสรุปผลอยู่ไหนล่ะครับ ผมเปิดมาดูทั้ง ๒ ไฟล์ก็ไม่พบ
เรื่องติดข้อ ๕ คือข้อนี้ใช่ไหมครับ
5 ลากฟิลด์ "จังหวัด" ไปปล่อยที่ช่อง Rows
ถ้าลากไม่ได้ ก็คลิกขวาที่ชื่อฟิลด์ในกรอบ Choose Field to add report แล้วคลิกเลือก Add to Row Labels ก็ได้ครับ
เรื่องการใช้สูตร นับอย่างมีเงื่อนไข หาค่าเฉลี่ยอย่างมีเงื่อนไข,หาผลรวมอย่างมีเงื่อนไข มีหลักการเลือกใช้ดังนี้
1 ถ้าเงื่อนไขมีเพียงเงื่อนไข
เดียวให้ใช้สูตร
COUNTIF SUMIF AVERAGEIF
2 ถ้าเงื่อนไขมีมากกว่า ๒ เงื่อนไข โดยเงื่อนไขที่ใช้ตรวจสอบนั้น
ไม่ได้อยู่ในฟิลด์เดียวกัน สามารถใช้
SUMIFS COUNTIFS AVERAGEIFS ได้
2 ถ้าเงื่อนไขมีมากกว่า ๒ เงื่อนไข โดยเงื่อนไขที่ใช้ตรวจสอบนั้น
อยู่ในฟิลด์เดียวกัน ให้ใช้
SUMPRODUCT แทน
ในหนังสือจัดการฐานข้อมูลด้วย Microsoft Excel 2010 ก็มีเรื่องสูตรดังกล่าวอยู่ครับ ลองศึกษาดูครับ
โทษที่ครับ ติด ข้อ.5นี้ครับ
5 ใส่เครื่องหมายถูกหน้า My table has Header ผมไม่รุ้จักครับว่ามันอยู่ตรงไหน
ขอบคุณครับ
อ่อ คือว่า SUMPRODUCT มัน นับมัน อักษรและตัวเลขหรือป่าวครับ ที่จริงผมเข้าใจว่ามันนับ แต่รุปแบบเซลล์ ที่เป็น ข้อความ อย่างเดียวน่ะครับ ผมกลัว ว่าเซลล์ใดๆ ถ้าถูกปรับให้เป็น รูปแบบตัวเลขมันจะไม่นับน่ะครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Tue Jun 11, 2013 10:26 pm
by nattasiray
ดูจากรูปภาพครับ
SUMPRODUCT สามารถนับตัวเลขกับข้อความได้ โดยไม่สนใจตัวหนา ตัวเอียง ขีดเส้นใต้ ขนาดของข้อความ หรือสีพื้นของเซลล์ หรือลักษณะเส้นขอบ หากต้องการใช้นับแบบตรงรูปลักษณ์ตัวอักษร เช่น ตัวพิมพ์ใหญ่ ตัวพิมพ์เล็ก ต้องใช้ร่วมกับฟังก์ชัน EXACT ด้วย
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Thu Jun 13, 2013 11:26 am
by sings
nattasiray wrote:ดูจากรูปภาพครับ
SUMPRODUCT สามารถนับตัวเลขกับข้อความได้ โดยไม่สนใจตัวหนา ตัวเอียง ขีดเส้นใต้ ขนาดของข้อความ หรือสีพื้นของเซลล์ หรือลักษณะเส้นขอบ หากต้องการใช้นับแบบตรงรูปลักษณ์ตัวอักษร เช่น ตัวพิมพ์ใหญ่ ตัวพิมพ์เล็ก ต้องใช้ร่วมกับฟังก์ชัน EXACT ด้วย
ผมนี้โง่ๆจริง ไม่ทันสังเกต ทั้งๆที่กดคลิก มันเสร็จไปแล้ว
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Fri Jun 14, 2013 11:14 am
by sings
อาจาร์ยครับผมได้ใช้ไฟล์ตัวอย่างแรก ลิงค์แรก มาลองทำดู แล้ว ไม่รู้ว่าผมทำถูกต้องแล้วหรือยัง ช่วยดูให้ที่ครับดังไฟล์ที่แนบมาล่างนี้น่ะครับ ผมทำถึงข้อ 9 รู้สึกจะติดข้อ 10 ผมไม่รู้ว่าให้คลิกตัวเลขตรงไหน หรือว่าให้คลิกที่ตารางที่มันมีเกิดขึ้น ตอนสร้าง ข้อที่ 5 ถึง ข้อที่ 9 ครับ ผมคลิกขวาที่ตัวเลขในตารางเกือบทุกตัวแล้วไม่เห็น คำว่า Group เลยครับ แล้วรูปแบบตารางมันไม่ออกมาแบบที่ออกแบบไว้ แบบชีลที่ 3 ครับหรือว่าเราต้องมาปรับแต่งเองอีกที่ครับเมือทำตาราง PivotTable เสร็จสิ้นแล้ว
ปล. หนังสือผมที่ห้องสมุคเค้ามีคนจองยาวไป 2 อาทิตย์ กว่าจะได้คงอีกนานครับระหว่างนี้ก็ขอให้อาจาร์ย เป็นที่ปรึกษาไปก่อนน่ะครับ
และตอนนี้ผมกำลังแกะสูตรของ อาจาร์ย snasui ไปในตัวอยู่ด้วยน่ะครับ
ขอบคุณครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Fri Jun 14, 2013 6:13 pm
by nattasiray
ผมคาดคะเนถูกต้องว่าหนังสือถูกยีมออกจากห้องสมุดด้วยระยะเวลายาวนาน ดังนั้นแนะนำไปหามาเป็นเจ้าของดีกว่าครับ ไม่ต้องเสียเวลาต่อคิวรอยืมอีก เพราะโอกาสที่คนก่อนหน้าอาจติดใจหนังสือของผมมีโอกาสเป็นไปได้สูงครับ
หากคุณเป็นสมาชิกของ กบข. ก็ได้ส่วนลดร้อยละ ๑๐ เมื่อแสดงบัตรสมาชิก กบข. ณ จุดชำระเงินของร้านซีเอ็ดบุกส์
เรื่องการจัดกลุ่มช่วงข้อมุลใน PivotTable ดูจากภาพขั้นตอนที่ทำให้
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Fri Jun 14, 2013 7:53 pm
by nattasiray
เห็นคุณต้องการใช้ฟอนต์ TH SarabunPSK 16 พอยนต์ แต่คุณต้องกดแป้น Ctrl+A แล้วเปลี่ยนฟอนต์ ทุกครั้งที่แทรกเวิร์คชีตใหม่ใช่หรือไม่ หากคำตอบคือ "ใช่" ไปดูเรื่องการตั้งฟอนต์เริ่มต้นให้กับแฟ้มที่กำลังเปิดให้ใช้งาน ให้ดูที่
http://www.snasui.com/viewtopic.php?f=9&t=4936
คราวต่อไปคุณไม่ต้องเสียเวลากดแป้น Ctrl+A แล้วเปลี่ยนฟอนต์ให้กับแฟ้มที่กำลังเปิดใช้งานครับ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Mon Jun 17, 2013 9:46 am
by sings
nattasiray wrote:เห็นคุณต้องการใช้ฟอนต์ TH SarabunPSK 16 พอยนต์ แต่คุณต้องกดแป้น Ctrl+A แล้วเปลี่ยนฟอนต์ ทุกครั้งที่แทรกเวิร์คชีตใหม่ใช่หรือไม่ หากคำตอบคือ "ใช่" ไปดูเรื่องการตั้งฟอนต์เริ่มต้นให้กับแฟ้มที่กำลังเปิดให้ใช้งาน ให้ดูที่
http://www.snasui.com/viewtopic.php?f=9&t=4936
คราวต่อไปคุณไม่ต้องเสียเวลากดแป้น Ctrl+A แล้วเปลี่ยนฟอนต์ให้กับแฟ้มที่กำลังเปิดใช้งานครับ
ขอบคุณครับ ที่แนะนำ
Re: ช่วยบอกสูตรหรือเทคนิดการค้นหาแบบมีเงื่อนไข และสรุปเป็นผล
Posted: Tue Jun 18, 2013 5:05 pm
by sings
ตอนนี้ได้งานออกมาแล้วครับ โดยใช้สูตร COUNTIFS สำหรับผมว่า PivotTable มันก็ไม่หน้าจะยากเท่าไร แต่เรามิได้ศึกษามาทางนี้ และอีกอย่างต้องมานั้งทำตารางใหม่อีกที่ไม่ได้นำไปใช้ได้เลย
ขอบคุณครับ