ต้องการหาผลรวมแบบมีเงื่อนไข

[orra]

สวัสดีค่ะ ดิฉันมือใหม่หัดใช้สูตรค่ะ ต้องขอรบกวนเวลานะคะ
อยากจะหาผลรวมในช่อง I4 โดยผลรวม เป็นคำตอบที่ว่า ให้คูณ10 ในช่อง H4 แต่ผลรวมเงื่อนไขที่ว่า ฟรี 2 ชั่วโมงแรก
อยากได้คำตอบในช่อง I4 เป็นคำตอบที่หัก 2 ชม.แรกค่ะ ตามไฟล์แนบนะคะ

[nattasiray]

ให้แก้ไขตารางฐานข้อมูลก่อน

1 บรรทัดหัวตารางต้องเว้นจากหัวเรือง 1 บรรทัด
2 บรรทัดหัวตารางต้องใช้บรรทัดเดียว เพื่อให้สามารถใช้คำสั่งในเมนู Data ทั้งหมดได้ทันทีโดยไม่ต้องคลิกเลือกลุ่มเซลล์
3 ใช้คำสั่ง Data List เพือคัดลอกสูตร Data Validation Condiional Formatting จากเรคอร์ดข้างบนลงในเรคอร์ดใหม่

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

การคำนวณหาจำนวนเงินที่ต้องชำระ จากตัวเลขเวลาใช้สูตร

Code: Select all

=(ผลต่างของเวลา)*24*ราคาต่อชั่วโมง

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

[orra]

ขอบคุณสำหรับคำตอบ มีประโยชน์อย่างมากเลยค่ะ

[orra]

อยากรบกวนอีกนะคะ ตอนนี้มาดู sheet ที่คุณณัฐศิระ เฉลยให้ แร้วยังแกะสูตรไม่ได้เรยค่ะ แบบว่างง มากก ช่วยตอบคำถามอีกรอบนะคะ
1 คอลัมน์ B และ C ใส่ข้อมูลอย่างไรค่ะ ถึงจะได้ ว ดป และ เวลา โชว์หน้าตาราง ดิฉันใส่ข้อมูล ไม่เป็นค่ะ
2 คอลัมน์ E การใช้สูตร Ceiling รบกวนช่วยอธิบายให้ฟังอีกครั้งนะคะ ว่าการใช้สูตรนี้ สำหรับการใช้งานอย่างไร แล้วที่ว่า 1/24 มาจากตรงไหนค่ะ
3 คอลัมน์ F สูตรคือ นำ คอลัมน์ E4 ลบ (1/12) ในวงเล็บนี้ มาจากตรงไหนคะ และ *10 คือชม.ละ 10 บาท ส่วน *24 คือ ได้มายังไงคะ
4 คอลัมน์ I สูตรนี้ อยากให้ช่วยอธิบายให้กระจ่างได้ไม๊คะ

ขอบคุณมากกค่ะ ดิฉันไม่เข้าใจ และยัง งงๆ จิงๆค่ะ

[snasui]

อยากรบกวนอีกนะคะ ตอนนี้มาดู sheet ที่คุณณัฐศิระ เฉลยให้ แร้วยังแกะสูตรไม่ได้เรยค่ะ แบบว่างง มากก ช่วยตอบคำถามอีกรอบนะคะ
1 คอลัมน์ B และ C ใส่ข้อมูลอย่างไรค่ะ ถึงจะได้ ว ดป และ เวลา โชว์หน้าตาราง ดิฉันใส่ข้อมูล ไม่เป็นค่ะ
2 คอลัมน์ E การใช้สูตร Ceiling รบกวนช่วยอธิบายให้ฟังอีกครั้งนะคะ ว่าการใช้สูตรนี้ สำหรับการใช้งานอย่างไร แล้วที่ว่า 1/24 มาจากตรงไหนค่ะ
3 คอลัมน์ F สูตรคือ นำ คอลัมน์ E4 ลบ (1/12) ในวงเล็บนี้ มาจากตรงไหนคะ และ *10 คือชม.ละ 10 บาท ส่วน *24 คือ ได้มายังไงคะ
4 คอลัมน์ I สูตรนี้ อยากให้ช่วยอธิบายให้กระจ่างได้ไม๊คะ

ขอบคุณมากกค่ะ ดิฉันไม่เข้าใจ และยัง งงๆ จิงๆค่ะ

อ่านกฎการใช้บอร์ดทุกข้อด้านบน แล้วแก้ไขข้อความด้วยครับ

[orra]

อยากรบกวนอีกนะคะ ตอนนี้มาดู sheet ที่คุณณัฐศิระ เฉลยให้ แล้วยังแกะสูตรไม่ได้เลยค่ะ แบบว่า งง มาก ช่วยตอบคำถามอีกรอบนะคะ
1 คอลัมน์ B และ C ใส่ข้อมูลอย่างไรค่ะ ถึงจะได้ ว ดป และ เวลา โชว์หน้าตาราง ดิฉันใส่ข้อมูล ไม่เป็นค่ะ
2 คอลัมน์ E การใช้สูตร Ceiling รบกวนช่วยอธิบายให้ฟังอีกครั้งนะคะ ว่าการใช้สูตรนี้ สำหรับการใช้งานอย่างไร แล้วที่ว่า 1/24 มาจากตรงไหนค่ะ
3 คอลัมน์ F สูตรคือ นำ คอลัมน์ E4 ลบ (1/12) ในวงเล็บนี้ มาจากตรงไหนคะ และ *10 คือชม.ละ 10 บาท ส่วน *24 คือ ได้มายังไงคะ
4 คอลัมน์ I สูตรนี้ อยากให้ช่วยอธิบายให้กระจ่างได้ไม๊คะ

ขอบคุณมากกค่ะ ดิฉันไม่เข้าใจ และยัง งงๆ จริงๆค่ะ

[snasui]

สวัสดีค่ะ ดิฉันมือใหม่หัดใช้สูตรค่ะ ต้องขอรบกวนเวลานะคะ
อยากจะหาผลรวมในช่อง I4 โดยผลรวม เป็นคำตอบที่ว่า ให้คูณ10 ในช่อง H4 แต่ผลรวมเงื่อนไขที่ว่า ฟรี 2 ชั่วโมงแรก
อยากได้คำตอบในช่อง I4 เป็นคำตอบที่หัก 2 ชม.แรกค่ะ ตามไฟล์แนบนะคะ

ระหว่างรอคุณ ณัฐศิระ มาอธิบาย ผมตอบคำถามตามที่ยกมาด้านบนให้ก่อน

ที่ I4 คีย์

=MAX(0,H4-2)*10

Enter > Copy ลงด้านล่าง

[nattasiray]

ถาม คอลัมน์ B และ C ใส่ข้อมูลอย่างไรค่ะ ถึงจะได้ ว ดป และ เวลา โชว์หน้าตาราง ดิฉันใส่ข้อมูล ไม่เป็นค่ะ
ตอบ กรอกข้อมูลอยู่ในรูป d/m/yyyy hh:mm:ss เช่น 19/4/2013 12:10:50 ขอย้ำว่าต้องกรอกด้วยปีคริสตศักราช 4 หลักเท่านั้น และต้องเว้นวรรคระหว่างเลขวันเดือนปีกับเวลาด้วย

ถาม คอลัมน์ E การใช้สูตร Ceiling รบกวนช่วยอธิบายให้ฟังอีกครั้งนะคะ ว่าการใช้สูตรนี้ สำหรับการใช้งานอย่างไร แล้วที่ว่า 1/24 มาจากตรงไหนค่ะ
ตอบ ข้อกำหนดคุณต้องการปัดเศษนาทีที่เกินมาให้เป็น 1 ชั่วโมง หากใช้สูตร =CEILING(ระยะเวลา,1) จะกลายเป็นปัดขึ้น 1 วัน ดังนั้นต้องเอา 24 หาร เพื่อให้เหลือ 1 ชั่วโมงครับ

ถาม คอลัมน์ F สูตรคือ นำ คอลัมน์ E4 ลบ (1/12) ในวงเล็บนี้ มาจากตรงไหนคะ และ *10 คือชม.ละ 10 บาท ส่วน *24 คือ ได้มายังไงคะ
ตอบ คุณบอกว่า 2 ชั่วโมงแรกจอดฟรี ดังนั้น ต้องคิดหาว่า 2 ชั่วโมง เป็นเศษส่วนเท่าไหร่ของวัน ดังนั้นต้องเอา 24 หาร ซึ่งเท่ากับ 2/24 และเมื่อตัดทอนเป็นเศษส่วนอย่างต่ำคือ 1/12 ครับ จึงนำค่าดังกล่าวไปหักลบออก

ขอยกตัวอย่างการคำนวณหาค่าทำงานล่วงเวลา ครับ ซึ่งง่ายต่อการทำความเข้าใจ

เราต้องอาศัยความเข้าใจหน่วยนับพื้นฐานของค่าแรงล่วงเวลา

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

หลักการการคำนวณหาค่าแรงล่วงเวลา นำมาประยุกต์ใช้กับการคำนวณหาค่าบริการที่ต้องชำระตามระยะเวลาที่ใช้ไปครับ

ถาม คอลัมน์ I สูตรนี้ อยากให้ช่วยอธิบายให้กระจ่างได้ไม๊คะ
ตอบ ในตารางฐานข้อมูลคุณมีวันเดือนปีและเวลาอยู่แล้ว และคุณต้องการทราบว่า ในแต่ละเดือนนั้นมีรายรับเท่าไหร่ เราจึงใช้สูตร

Code: Select all

=TEXT(เซลล์วันเดือนปี.รหัสการแสดงผลตัวเลข)

เพื่่อแปลงค่าวันเดือนปี ให้เป็นชื่อเดือนแล้วตามด้วยเลขปี

รหัสการแสดงผลตัวเลขที่ผมใช้คือ "ดดดดbbbb" โดย
รหัส ดดดด คือชื่อเดือนภาษาไทยอย่างเต็ม เช่น มกราคม, กุมภาพันธ์
รหัส bbbb คือปีพุทธศักราชเลขอาระบิค 4 หลัก เช่น มกราคม, กุมภาพันธ์

สมมุติว่าเซลล์ A2 มีค่า = 1/2/2013
หากผมใช้สูตร =Text(A2,"ดดดดbbbb") จะให้ค่า กุมภาพันธ์2556

เนื่องจากเรามีเซลล์ชื่อเดือนแล้ว เรายังขาดเลขปี จึงกำหนดให้เซลล์ I1 เป็นเซลล์รับค่าปีพุทธศักราช ซึ่งแปรเปลี่ยนตลอดเวลา

ส่วนสูตร $H4&$I$1 เป็นการเชื่อมข้อมูลที่อยู่ในเซลล์ H4 กับเซลล์ I1 เช่น H4 มีค่า "มกราคม" ส่วน I1 มีค่า 2556 ดังนั้นสูตร $H4&$I$1 ให้ผลลัพธ์ มกราคม2556 ซึ่งเราใช้ค่าดังกล่าวเป็นค่าเปรียบเทียบเพื่อการตรวจสอบ

สำหรับสูตร

Code: Select all

(กลุ่มเซลล์ที่ต้องการตรวจสอบ=ค่าเปรียบเทียบ)

เป็นการตรวจสอบว่ามีค่าใดตรงบ้าง ถ้าผลลลัพธ์ของการตรวจสอบพบว่าตรงกัน จะคืนค่า จริง (TRUE) ออกมา
การคืนค่าดังกล่าว ยากแก่การทำความเข้าใจของมนุษย์ ดังนั้นเราต้องเอาค่าที่ต้องการนำมาคำนวณไปคู ซึ่งในที่นี้คือ ค่าใช้บริการ (Fee)

สำหรับ TimeIn เป็นชื่อเล่นกลุ่มเซลล์ (Range Name) ของเซลล์ B4 ลงไป ส่วน Fee คือชื่อเล่นกลุ่มเซลล์ของตั้งแต่ F4 ลงไป

สูตร SUMPRODUCT เป็นสูตรสำหรับคำนวณหาผลรวมของผลคูณครับ แต่เราสามารถใช้สูตรดังกล่าวเพื่อหาผลรวมอย่างมีเงื่อนไขตั้งแต่ 1 เงื่อนไขขึ้นไป

งานของคุณเป็นงานฐานข้อมูลครับ ลองหาหนังสือ จัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล 2010 มาอ่านด้วยครับ เพราะมีคำอธิบายในการสูตร SUMPRODUCT เพื่อหาผลรวมอย่างมีเงื่อนไข ถึงแม้ว่าคุณใช้ MS Excel 2003 ก็นำเนื้อหามาประยุกต์ได้ ในส่วนของสูตร การใช้คำสั่งในเมนู Data ทั้งหมด Conditional Formatting การออกแบบตารางบันทึกข้อมูลครับ

อ้อที่เซลล์ F4 แนะนำให้ใช้สูตร

Code: Select all

=IF(HOUR(E4)>2,(E4-(1/12))*10*24,0) หรือ =(HOUR(E4)>2)*(E4-(1/12))*10*24

หากจอดรถไม่เกิน 2 ชั่วโมง จะแสดงค่า 0 ออกมาครับ มิฉะนั้นจะคำนวณไม่ได้ครับ

ดูฉบับปรับปรุงได้ครับ ผมใช้ Conditional Formatting ตรวจสอบว่ามีค่าเท่ากับ 0 หรือไม่

[orra]

ขอบคุณ ทุกๆคำตอบทั้ง 2 ท่านนะคะ

[orra]

เรียนอาจารย์ ดิฉันยังไม่เข้าใจในการใช้
ใช้คำสั่ง Data List เพือคัดลอกสูตร Data Validation Condiional Formatting