Snasui Excel Forum

ขอสอบถามท่านผู้รู้ทุกท่าน
ต้องการออกแบบใบเสร็จรับเงินเพื่อใช้งานในสถาบันกวดวิชาแห่งหนึ่ง และใช้เพียงสูตร excel อย่างเดียว ตามเงื่อนไขด้านล่างนี้สามารถทำได้ทั้งหมดเลยหรือไม่
1. รันเลขที่ใบเสร็จอัตโนมัติโดยอ้างอิงจากเลขที่ใบเสร็จล่าสุดในฐานข้อมูล
ต้องการให้ cell J2 ในชีต "keybill" เป็นเลขที่ใบเสร็จรับเงินที่รันอัตโนมัติต่อจากบรรทัดที่มีเลขที่ใบเสร็จรับเงินล่าสุดในชีต "Data"
คอลัมน์ c ชื่อ "New Bill No."
2. ค้นหาข้อมูลจากฐานข้อมูลถ้าไม่พบให้กรอกใหม่ได้
เมื่อกรอกข้อมูลใน ชีต "keybill" เซล D6 ให้นำข้อมูลในเซล D6 ไปตรวจสอบกับข้อมูลในชีต "Data" คอลัมน์ E ชื่อ "Student Name" ถ้าพบข้อมูลให้ข้อมูลใน ชีต "keybill" เซล B6 แสดงข้อมูลจากชีต "Data" คอลัมน์ F ชื่อ "Nickname" กับ เซล B7 แสดงข้อมูลจากชีต "Data" คอลัมน์ AB ชื่อ "tel" แบบอัตโนมัติโดยดึงมาจากชีต Data
แต่ถ้าไม่พบข้อมูลในชีต Data ให้เป็นช่องว่างสามารถกรอกใหม่ได้เลย โดยที่สูตรก็ไม่หายไปด้วย แบบนี้สามารถทำได้หรือไม่
3. การเก็บชุดข้อมูลตามเลขที่ใบเสร็จนั้นๆ ไปเก็บลงฐานข้อมูล
ให้ Cell ในหน้าใบเสร็จรับเงิน ตามด้านล่างนี้ ไปเก็บในชีต Data
ชีต "keybill" เซล J2 ไปเก็บที่ ชีต "Data" คอลัมน์ C ชื่อ "New Bill No."
ชีต "keybill" เซล B6 ไปเก็บที่ ชีต "Data" คอลัมน์ F ชื่อ "Nickname"
ชีต "keybill" เซล B7 ไปเก็บที่ ชีต "Data" คอลัมน์ AB ชื่อ "tel"
ชีต "keybill" เซล D6 ไปเก็บที่ ชีต "Data" คอลัมน์ E ชื่อ "Student Name"
ชีต "keybill" เซล D7 ไปเก็บที่ ชีต "Data" คอลัมน์ T ชื่อ "เข้าบัญชีวันที่"
ชีต "keybill" เซล H7 ไปเก็บที่ ชีต "Data" คอลัมน์ S ชื่อ "รหัสอ้างอิง"
ชีต "keybill" เซล B10:B18 ไปเก็บที่ ชีต "Data" คอลัมน์ G ชื่อ "รหัสCourse"
ชีต "keybill" เซล C10:C18 ไปเก็บที่ ชีต "Data" คอลัมน์ H ชื่อ "รายละเอียดใบเสร็จ" ,คอลัมน์ U ชื่อ "Courseที่ลงแล้ว"
ชีต "keybill" เซล E10:E18 ไปเก็บที่ ชีต "Data" คอลัมน์ V ชื่อ "Start"
ชีต "keybill" เซล F10:F18 ไปเก็บที่ ชีต "Data" คอลัมน์ W ชื่อ "Final"
ชีต "keybill" เซล G10:G18 ไปเก็บที่ ชีต "Data" คอลัมน์ X ชื่อ "เวลา"
ชีต "keybill" เซล H10:H18 ไปเก็บที่ ชีต "Data" คอลัมน์ Y ชื่อ "มูลค่าต่อCourse"
ชีต "keybill" เซล J21 ไปเก็บที่ ชีต "Data" คอลัมน์ K ชื่อ "ส่วนลด promotion"
ชีต "keybill" เซล J22 ไปเก็บที่ ชีต "Data" คอลัมน์ I ชื่อ "ราคาชำระตามใบเสร็จ"
4. นับครั้งในเดือนโดยวันที่แต่ละครั้งที่ตรงกับวันหยุดไม่นำมานับครั้ง
จากเดิม ชีตชื่อ "course" เซล J2 ใส่สูตร =ROUNDUP((I2-H2)/7+1,0)+IF(MOD(H2,7)+1<MOD(I2,7)+1,1,0)
หากต้องการให้ เซล J2 ไม่นับวันหยุดที่อยู่ในชีตชื่อ "holiday" เซล A1:A20 จะต้องใส่สูตรเพิ่มเข้าไปได้อย่างไรบ้างค่ะ

ampertise wrote: Mon Oct 12, 2020 12:14 pm ต้องการออกแบบใบเสร็จรับเงินเพื่อใช้งานในสถาบันกวดวิชาแห่งหนึ่ง และใช้เพียงสูตร excel อย่างเดียว ตามเงื่อนไขด้านล่างนี้สามารถทำได้ทั้งหมดเลยหรือไม่

ทำด้วยสูตรไม่ได้ทั้งหมดครับ

ampertise wrote: Mon Oct 12, 2020 12:14 pm 1. รันเลขที่ใบเสร็จอัตโนมัติโดยอ้างอิงจากเลขที่ใบเสร็จล่าสุดในฐานข้อมูล
ต้องการให้ cell J2 ในชีต "keybill" เป็นเลขที่ใบเสร็จรับเงินที่รันอัตโนมัติต่อจากบรรทัดที่มีเลขที่ใบเสร็จรับเงินล่าสุดในชีต "Data"
คอลัมน์ c ชื่อ "New Bill No."

ข้อนี้จะต้องทราบรายละเอียดการรันค่า New Bill No เท่าที่ดูคร่าว ๆ มีตัวอักษรต่อท้ายเข้ามาด้วยเช่น C37, C38 และคิดว่าจะมีโอกาสเป็นค่าอื่น ๆ ได้อีก

ampertise wrote: Mon Oct 12, 2020 12:14 pm 2. ค้นหาข้อมูลจากฐานข้อมูลถ้าไม่พบให้กรอกใหม่ได้
เมื่อกรอกข้อมูลใน ชีต "keybill" เซล D6 ให้นำข้อมูลในเซล D6 ไปตรวจสอบกับข้อมูลในชีต "Data" คอลัมน์ E ชื่อ "Student Name" ถ้าพบข้อมูลให้ข้อมูลใน ชีต "keybill" เซล B6 แสดงข้อมูลจากชีต "Data" คอลัมน์ F ชื่อ "Nickname" กับ เซล B7 แสดงข้อมูลจากชีต "Data" คอลัมน์ AB ชื่อ "tel" แบบอัตโนมัติโดยดึงมาจากชีต Data
แต่ถ้าไม่พบข้อมูลในชีต Data ให้เป็นช่องว่างสามารถกรอกใหม่ได้เลย โดยที่สูตรก็ไม่หายไปด้วย แบบนี้สามารถทำได้หรือไม่

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

เซลล์ B5 คีย์

=IFERROR(INDEX(Data!F:F,MATCH($D$6,Data!E:E,0)),"")

Enter

เซลล์ B6 คีย์

=IFERROR(INDEX(Data!AB:AB,MATCH($D$6,Data!E:E,0)),"")

Enter

ampertise wrote: Mon Oct 12, 2020 12:14 pm 3. การเก็บชุดข้อมูลตามเลขที่ใบเสร็จนั้นๆ ไปเก็บลงฐานข้อมูล
ให้ Cell ในหน้าใบเสร็จรับเงิน ตามด้านล่างนี้ ไปเก็บในชีต Data
ชีต "keybill" เซล J2 ไปเก็บที่ ชีต "Data" คอลัมน์ C ชื่อ "New Bill No."
ชีต "keybill" เซล B6 ไปเก็บที่ ชีต "Data" คอลัมน์ F ชื่อ "Nickname"...

งานการเก็บข้อมูลต่อท้ายข้อมูลเดิมหรือแก้ไขข้อมูลเดิมใน Database ต้องอาศัย VBA ตัวอย่างดูที่นี่ครับ wordpress/record-data-to-database/

ข้อ 1. เรื่องรหัสอาจจะยังไม่ชัดเจน ยังไงขอไปถามให้เรียบร้อยอีกทีก่อนค่ะ
ข้อ 2. ลองใส่สูตรแล้วแสดงค่าว่างได้จริง แต่ว่าถ้าหากกรอกข้อมูลใหม่ลงไปก็จะทับสูตรหายไปด้วย อันนี้น่าจะทำไม่ได้ใช่ไหมค่ะ
ข้อ 3. ขอไปศึกษาก่อนนะค่ะ
ขอบคุณคุณ snasui ค่ะ
ข้อ 4. เรียนสอบถามผู้รู้ทุกท่านค่ะ

ampertise wrote: Tue Oct 13, 2020 2:18 pm ข้อ 2. ลองใส่สูตรแล้วแสดงค่าว่างได้จริง แต่ว่าถ้าหากกรอกข้อมูลใหม่ลงไปก็จะทับสูตรหายไปด้วย อันนี้น่าจะทำไม่ได้ใช่ไหมค่ะ

จะต้องไม่คีย์ทับเซลล์ที่เป็นสูตร ถ้าคีย์ทับสูตรก็จะหายไปครับ

ampertise wrote: Tue Oct 13, 2020 2:18 pm ข้อ 4. เรียนสอบถามผู้รู้ทุกท่านค่ะ

ผู้ถามต้องปฏิบัติตามกฎการใช้บอร์ดข้อ 5 ด้านบน ผู้รู้จึงจะให้คำตอบครับ

ขอถามท่านผู้รู้ทุกท่านค่ะ

4. นับครั้งในเดือนโดยวันที่แต่ละครั้งที่ตรงกับวันหยุดไม่นำมานับครั้ง
จากเดิม ชีตชื่อ "course" เซล J2 ใส่สูตร หากต้องการให้ เซล J2 ไม่นับวันหยุดที่อยู่ในชีตชื่อ "holiday" เซล A1:A20 จะต้องใส่สูตรเพิ่มเข้าไปได้อย่างไรบ้างค่ะ

ตัวอย่างสูตรครับ

เซลล์ P2 คีย์

=ROUNDUP((I2-H2+1-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(H2&":"&I2)),holiday!$A$1:$A$20,0))))/7,0)

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

ขอบคุณค่ะ คุณ snasui มากค่ะ
ได้สูตรของข้อ 4 ตรงตามที่ต้องการเลยค่ะ