Excel Forum

ผมขอถามสูตรการ lookup ในการหายอดเงินต้นงวดของแต่ละเดือนจากข้อมูลดิบที่เป็นรายการเคลื่อนไหวยอดเงินฝากเข้าถอนออกในแต่ละเดือนโดยอาจใช้ฟังก์ชัน End of Month หรือ Beginning of Monthในการค้นหา

รบกวนอาจารย์ช่วยชี้แนะหน่อยนะครับว่าสูตรในช่อง O4ถึงO10จะต้องเขียนอย่างไร โดยผมได้แสดงผลลัพธ์ที่ต้องการไว้บน Cell K3ถึงL10นะครับ

Linkนี้ได้ตั้งpublic สามารถedit ได้โดยบุคคลสาธารณะแล้วนะครับ

https://docs.google.com/spreadsheets/d/ ... edit#gid=0

กรุณาอธิบาย L4 และ L5 มีวิธีคิดอย่างไรจึงแสดงค่าเช่นนั้น จะได้เข้าใจตรงกันครับ

ยอด L4มาจากการที่ตัวอย่างนี้ให้เงินยกมา ณ ต้นเดือน Jan2021 เป็น 0 ครับ

ยอด L5 มาจากการที่มีการฝากเงินระหว่างเดือน Jan2021 คือ ฝากเงิน5Jan2021จำนวน2ล้าน และฝาก13Jan2021จำนวน1ล้าน ทำให้ยอดเงินสิ้นเดือนJan2021คือ 3ล้านครับ ดังนั้นยอด L5 ซึ่งเป็นยอดเงินคงเหลือ ณ 1Feb2021 จึงเท่ากับยอดเงินสิ้นเดือน Jan2021 คือ 3ล้านครับ

ยอด L6 มาจากการที่ระหว่างเดือน Feb2021 ไม่มีการเคลื่อนไหวของเงินจึงทำให้ยอดเงินคงเหลือ ณ สิ้นเดือน Feb2021 ยังคงเท่าเดิมคือ 3ล้าน ดังนั้นยอด L6ซึ่งเป็นยอดเงินคงเหลือ ณ 1 Mar2021 จึงเท่ากับยอดเงินสิ้นเดือน Feb2021 คือ 3 ล้านครับ

ตัวอย่างสูตรในช่วง L12:L18 และสังเกตการแสดงวันที่ใน K12:K18 ครับ

เซลล์ L12 คีย์

=index($G$4:$G$9, match(K12,$D$4:$D$9))

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

ขอบคุณอาจารย์ Snasui มากครับ เปิดโลกการใช้สูตรมากๆเลยครับ สูตรfucntion นี้ผมยังไม่เคยลองใช้ครับ

ขอแสดงความนับถือครับ

https://docs.google.com/spreadsheets/d/ ... edit#gid=0

จากใน Link นี้ที่อาจารย์เคยช่วยแนะนำการผูกสูตรช่อง L12 ถึง L22 นะครับ ผมใช้งานแล้วมาพบว่าสูตรจะให้ผลลัพธ์ไม่เป็นไปตามที่ต้องการเฉพาะเมื่อคอลัมภ์ D ตรงกับวันที่ 1 ของแต่ละเดือนครับ เช่น ถ้าเปลี่ยน D7 หรือ D9 เป็นวันที่1 ของเดือนจะเกิดการแสดงผลลัพธ์ไม่ถูกต้องครับ ซึ่งผลลัพธ์ที่ต้องการจะอยู่ในCell K2ถึงL10ครับ รบกวนอาจารย์ช่วยชี้แนะการแก้ไขการผูกสูตรในช่อง L12 ถึง L22 ให้อีกทีหน่อยนะครับ ผมเปิดสิทธิ์เข้าถึงแล้วครับ

หมายเหตุ: วันที่ทำรายการฝาก/ถอนในคอลัมภ์ D จะเรียงลำดับวันที่ครับ

ยังไม่เข้าใจปัญหาครับ

K4 เป็นต้นไป ยังไม่เห็นมีการปรับให้เป็นไปตามตัวอย่างที่ให้ไปคือ K12 เป็นต้นไป ในคอลัมนื L ก็เช่นกัน เมื่อปรับสูตรตามตัวอย่างที่ให้ไปแล้วช่วยแจ้งมาใหม่ว่าคำตอบไม่ตรงกับที่ต้องการตรงไหน อย่างไร ฯลฯ ครับ

จากรูปที่แนบนี้จะแสดงปัญหาที่เกิดขึ้นจากสูตรที่อาจารย์เคยให้ไว้นะครับ

ใน cell D7 หากสมมติคีย์เปลี่ยนวันที่ฝากเงินเป็นวันที่ 2021-03-01 จะเกิดปัญหาว่าช่อง L14 จะแสดงค่า 2,950,000บาท ซึ่งผลลัพธ์ที่ต้องการจะเป็น 3,000,000บาทดังในช่องL6ครับ

ใน cell D9 หากสมมติคีย์เปลี่ยนวันที่ฝากเงินเป็นวันที่ 2021-06-01 จะเกิดปัญหาว่าช่อง L17 จะแสดงค่า 3,435,000บาท ซึ่งผลลัพธ์ที่ต้องการจะเป็น 3,450,000บาทดังในช่องL9ครับ

คือปัญหานี้จะเกิดเมื่อช่อง D7, D9 เป็นวันที่1 ของเดือนครับ ถ้าเป็นวันอื่นๆของเดือนจะไม่เกิดปัญหาการแสดงค่าผิดนะครับ

ลิงค์ที่เข้าไปจะเป็นที่นี่นะครับ ผมเปิดสิทธิ์เข้าถึงแล้วครับ https://docs.google.com/spreadsheets/d/ ... edit#gid=0

ขออธิบายสิ่งที่สูตร ณ ปัจจุบันดึงข้อมูลผิดพลาดเพิ่มเติมนะครับ

เมื่อเปลี่ยนค่า D7 เป็นวันที่2021-03-01 ดังในรูปโพสต์ก่อนหน้า ตัวระบบจะไปดึงยอดเงินคงเหลือจำนวน2,950,000 ณ สิ้นวันทำการของ 2021-03-01 ซึ่งมีการสุทธิยอดเงินถอนระหว่างวันที่2021-03-01 จำนวน-50,000บาทครับ แต่ผลลัพธ์ที่ต้องการคือยอดเงินคงเหลือเริ่มต้นวันทำการของวันที่ 2021-03-01 คือ3,000,000 บาท ที่ยังไม่รวมรายการถอนของวันที่ 2021-03-01 จำนวน -50,000บาทครับ

และเมื่อเปลี่ยนค่า D9 เป็นวันที่2021-06-01 ดังในรูปโพสต์ก่อนหน้า ตัวระบบจะไปดึงยอดเงินคงเหลือจำนวน3,435,000 ณ สิ้นวันทำการของ 2021-06-01 ซึ่งมีการสุทธิยอดเงินถอนระหว่างวันที่2021-03-01 จำนวน-15,000บาทครับ แต่ผลลัพธ์ที่ต้องการคือยอดเงินคงเหลือเริ่มต้นวันทำการของวันที่ 2021-06-01 คือ3,435,000 บาท ที่ยังไม่รวมรายการถอนของวันที่ 2021-06-01 จำนวน -15,000บาทครับ

และปัญหาตัวเลขผลลัพธ์ผิดจะเกิดเฉพาะเมื่อวันที่ทำรายการฝาก/ถอนเป็นวันที่ 1 ของเดือนซึ่งไปซ้ำกับวันที่ในช่อง K12ถึงK22ครับ

กรุณาเขียนคำตอบไว้ใน Link หลังจากเปลี่ยนวันที่เป็นวันที่ 1 แล้ว และระบุว่าแต่ละเซลล์ในคอลัมน์ L ให้ไปนำค่าในคอลัมน์ไหนมาแสดง พิจารณาอย่างไรจึงใช้ค่าในคอลัมน์นั้น โดยตัวอย่างจะต้องมีในทุกประเด็น ทั้งวันที่ 1 และใช่วันที่ 1 ของเดือน โดยจะต้องอธิบายวิธีคิดมาอย่างละเอียด เพื่อผู้ตอบจะได้ใช้สูตรคำนวณเปรียบเทียบได้ครับ

จากสูตรเดิม เป็นการนำค่าในคอลัมนื G มาเป็นคำตอบ แต่ดูเหมือนบางเงื่อนไขให้ไปใช้คอลัมน์ D มาเป็นคำตอบ ช่วยอธิบายวิธีพิจารณาว่าการจะนำคอลัมน์ไหนมาเป็นคำตอบให้ดูจากเงื่อนไขใด จะได้เข้าใจตรงกันครับ

ตามสูตรเดิมบนช่องL13ถึงL23ที่มีการนำค่าในคอลัมภ์ G มาเป็นคำตอบนั้นถูกต้องเกือบหมดครับแต่น่าจะขาดเงื่อนไขว่าถ้าวันที่ทำรายการฝาก/ถอนเป็นวันที่1 ของเดือน จะต้องแสดงด้วยค่าในคอลัมภ์Gค่าสุดท้ายของเดือนก่อนเช่นกันครับ ไม่ควรหยิบค่าคอลัมภ์Gบรรทัดเดียวกับวันที่1ของเดือนมาแสดงผลลัพธ์ เช่น ช่องD7 ถ้ามีการใส่ข้อมูลเป็น 2021-03-01 แล้วตรงช่อง L15 ซึ่งเป็นยอดเงินต้นงวด ณ 1/3/2021 ก็ควรดึงค่าG6ซึ่งคือเงินคงเหลือสิ้นวันค่าสุดท้ายของเดือนล่าสุดที่ได้มีการเคลื่อนไหวรายการฝาก/ถอนมาแสดงเป็นยอดเงินต้นงวดของเดือน แต่ตอนนี้สูตรเดิมไปดึงข้อมูลในช่อง G7มาแสดงครับทำให้ผลลัพธ์ในช่อง L15 ผิด

ตามสูตรเดิมนี้ถ้าช่อง D7 มีการใส่ข้อมูลสมมติเป็นวันที่ๆไม่ใช่วันที่1ของเดือน 2021-03-05แล้ว ผลลัพธ์บนช่อง L15 จะไปดึงค่าG6 ซึ่งเป็นเงินคงเหลือสิ้นวันค่าสุดท้ายของเดือนล่าสุดที่มีการเคลื่อนไหวรายการฝาก/ถอนมาแสดงเป็นยอดเงินต้นงวดของเดือนMar 21ซึ่งถูกต้องแล้วครับ
และขออธิบายเพิ่มเติมว่าช่อง L14 ซึ่งเป็นยอดเงินต้นงวดของ ณ 1/2/2021 ตามสูตรเดิมนั้นดึงข้อมูล 3,000,000 มานั้นถูกต้องแล้วเนื่องจากในเดือน Feb2021 ไม่มีรายการเคลื่อนไหวเงินฝาก/ถอนระหว่างเดือน ระบบจึงดึงข้อมูลยอดคงเหลือสิ้นวันสุดท้ายในคอลัมภ์ G ที่มีการเคลื่อนไหวรายการฝาก/ถอนวันสุดท้ายของเดือนJan21ซึ่งคือค่าในช่องG6มาแสดงผลครับ

อนึ่ง ผลลัพธ์ที่ต้องการในช่อง L13ถึง L23 คือยอดเงินต้นงวดของแต่ละเดือนครับ ซึ่งควรเท่ากับผลลัพธ์ที่ต้องการที่แสดงไว้ในช่อง L4 ถึง L10ครับ

ผมได้แนบลิงค์ล่าสุดไว้ด้านล่างนี้นะครับ ขออาจารย์ช่วยชี้แนะสูตรที่ควรจะเป็นบนช่อง L13ถึง L23ใหม่อีกครั้งหน่อยนะครับ https://docs.google.com/spreadsheets/d/ ... edit#gid=0

ตัวอย่างสูตรในคอลัมน์ L ครับ

=iferror(index($G$4:$G$9, max(1,match(K13,$D$4:$D$9)-(countif($D$4:$D$9,K13)>0))),0)

ที่บอกให้อธิบายอย่างละเอียดหมายถึงอธิบายที่ Google Sheet จะได้สะดวกในการทำความเข้าใจ เพราะชี้ให้เห็นได้ง่ายว่าเซลล์ไหนควรมีค่าเท่าใดด้วยเงื่อนไขใด จะได้ลดขั้นตอนในการสลับหน้าจอมาอ่านเงื่อนไข การอธิบายเพิ่มไว้ที่นี่ถือว่าเป็นส่วนเสริมครับ

ขอบคุณอาจารย์Snasui อย่างสูงครับ

และคราวหลังผมจะเขียนอธิบายใน Googlesheet เพื่อจะไม่ต้องสลับจอไปมาครับ
ผมกดโฆษณาให้รัวๆละครับ