สูตร excel เพื่อสะสมค่าแบบ accumulate

[BTR]

อยากขอคำแนะนำสูตร excel สำหรับบันทึกค่าสะสมใน row, column เดิมในอีก tab sheet ที่มี table / content เดียวกันครับ

ผมลองใช้ =IF(template!D2>0,accumulate_data!D2+template!D2,D2) แล้วไม่เป็นไปตามที่ต้องการ

โดยต้องการป้อนข้อมูลใน template (ตัวอย่างข้อมูลช่องสีส้ม, สีเขียว) แล้วไปแสดงค่าเก็บสะสมใน accumulate_data ที่ row, column เดียวกันสะสมค่าไปเรื่อยๆ
ซึ่งบางวัน บาง row, column ไม่มีรายการ ก็ให้ข้อมูลคงเดิมใน sheet accumulate_data
โดยเปลี่ยนเพิ่ม หรือ ลดตามที่บันทึกจาก sheet template เท่านั้น
และมีการเก็บข้อมูลที่บันทึกทุกรายการในวันที่ลงข้อมูลใน tab sheet recording ครับ

[snasui]

การทำเช่นนั้นควรใช้ VBA เข้ามาจัดการ ลองเขียนมาเองก่อน ติดตรงไหนค่อยมาถามกันต่อโดยต้องระบุ Procedure และบรรทัดที่ติดปัญหาให้ชัดเจนครับ

[BTR]

ชื่อกระทู้ควรจะเปลี่ยนเป็น vb code เพื่อสะสมค่าแบบ accumulate

1 มี code ที่สั้นๆ ที่ใช้แทนค่ากรณีที่ต้องเขียนซ้ำๆจำนวนมากๆ vb sheet1!d3:ac22) อย่างไรครับ
เพราะคิดว่าถ้าเขียนอย่างที่ผมทำอยู่นี้ ไฟล์คงใหญ่เกินจำเป็น
และยุ่งยากในกรณีต้องเพิ่มจำนวนคน(pname) หรือ products (a3:ac3)

Set d3sumrange = Range("d3")
Set d4sumrange = Range("d4")
Set d5sumrange = Range("d5")
....
bd3currentval = Range("Bd3").Value
bd4currentval = Range("Bd4").Value
bd5currentval = Range("Bd5").Value
...
If Not Intersect(target, [d3]) Is Nothing Then
Range("Bd3").Value = Application.WorksheetFunction.Sum(bd3currentval, d3sumrange)
End If
....

2 ณ ตอนนี้ flow เป็นการป้อนข้อมูลในรูป excel (sheet1!d3:ac22) โดยอาศัย vb sheet1 ส่งข้อมูลทันทีที่ enter แต่ละ cell ไปที่ bd3:bac22 ในชีทเดียวกัน
โดยเมื่อมีการกด clear พื้นที่ป้อนข้อมูล ค่าที่อยู่บน cell bd3:bac22 ยังคงอยู่ และรอรวมสะม เมื่อมีการป้อนข้อมูลใหม่ในพื้นที่เดิม
ส่วน tab sheet overview สร้างโดยการ link ข้อมูลจาก sheet1!bd3:bac22 เพื่อใช้งานด้านการคำนวนและ pivot table
หากต้องการลดความซ้ำซ้อน โดยให้ส่งค่าตรงเข้าไปใน tab sheet overview เลย ต้องกำหนด code อย่างไรครับ?

[snasui]

ควรจะเพิ่มชีทสำหรับเก็บข้อมูล Year to date เอาไว้ 1 ชีท แล้วค่อยนำค่าใน Sheet1 เข้าไปบวกเพื่อเป็นรายการสะสมแทนที่จะอยู่ในชีทเดียวเพราะจะจัดการลำบากเขียนเขียน Code ซับซ้อน

ลองปรับตามด้านบนมาดูก่อน ติดตรงไหนค่อยุถามกันต่อครับ

[BTR]

ผมกำหนดให้ run vb code เมื่อกดปุ่ม week ใน sheet "Wage"
เพื่อ copy data จาก sheet "OverView" ไป sheet "weekly!a3:ae28
และ copy data จาก sheet "Wage" ไป sheet "weekly!aj3:bn23

โดยให้เก็บ data ต่อท้ายต่อเนื่องกันไปทุกครั้งที่กดปุ่ม week

code แจ้งมีข้อผิดพลาดตามรูปนี้ เมื่อกดครั้งที่ 2

20140926error.jpg

ขอคำแนะนำปรับ code ที่ถูกต้องเหมาะสมด้วยครับ

ขอบคุณ

[snasui]

ตามที่ทดสอบคลิกไม่พบกว่าผิดพลาดครับ

[BTR]

ตามที่ทดสอบคลิกไม่พบกว่าผิดพลาดครับ

หลังจาก save แล้ว ไม่พบประเด็นอย่างที่ถามไปอีกเลยครับ

...............

ในกรณีที่ต้องการให้ vba บันทึกข้อมูลวันที่ให้อัตโนมัติ เช่นเดียวกับ tab daily!a3 จนครบทุกแถวที่มีข้อมูลใน column A
ผมอยากขอคำแนะนำว่าต้องเพิ่ม vba code ไปอย่างไรครับ?

Code: Select all

Sub Picture2_Click()

'for copy an input data from tab sheet1 to tab daily

Dim source As Range
Dim Target As Range
    
    With Sheets("Sheet1")
        Set source = .Range("a3:ag22")
    End With
    
    With Sheets("daily")
        If .Range("a3") = "" Then
            Set Target = .Range("a3")
        Else
            Set Target = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
    
    Target.Value = Date - 1
'    Target.Interior.ColorIndex = 6
'    Target.Font.Size = 12
    Target.Offset(0, 1).Resize(source.Rows.Count, _
        source.Columns.Count).Value = source.Value

End Sub

[snasui]

ตัวอย่าง Code ครับ

Code: Select all

Range("a3").AutoFill Destination:=Range("a3", Range("b" & Rows.Count).End(xlUp).Offset(0, -1)) _
    , Type:=xlFillCopy

[BTR]

1 ข้อมูลวันที่ลงใน area ที่กำหนดตาม code ที่ช่วยแนะนำแล้ว เมื่อกด f5 ในโหมด macro edit
แต่พอกดจากปุ่มที่กำหนดไว้ในหน้า sheet 1: Click to keep data to daily
ข้อมูลที่บันทึกเข้า tab sheet daily ไม่มีการเปลี่ยนแปลงตาม code
ผมผิดพลาดตรงไหนครับ?
ตัวอย่างใน tab sheet daily

2 background color ที่กำหนดไว้เพื่อแบ่งกลุ่มข้อมูล แสดงผลเต็มเหมือนกันไปด้วยครับ
หากต้องการให้สลับทุกครั้งที่บันทึกข้อมูล ระหว่างสี 4 กับ xlnone
ต้องเพิ่ม code เป็นอย่างไรครับ
ตัวอย่างใน tab sheet weekly

Code: Select all

Target1.Interior.ColorIndex = 4

ขอบคุณอาจารย์ครับ

[snasui]

อธิบายข้อ 1 มาใหม่ ค่อย ๆ แก้ปัญหาทีละเรื่องครับ

Code ชื่ออะไร อยู่ใน Module ไหน เขียนว่าอย่างไร ต้องการผลลัพธ์เป็นอย่างไร ติดขัดบรรทัดใดครับ

[BTR]

ประเด็น 1 คือ code ไม่แสดง error ใดๆครับ
แต่มีความต่างของผลที่แสดงใน tab sheet daily

เมื่อทดลอง run macro กด f5 Module 3 ในหน้า VBA edit code

pressF5.jpg

กด f5 ครั้งที่ 1 ได้ข้อมูลเก็บใน sheet daily row3:22 ครบถ้วนตามต้องการ
กด f5 ครั้งที่ 2 ได้ข้อมูลแถบสีเทาเก็บใน sheet daily row23:42 ครบถ้วนตามต้องการ

กดครั่้งที่ 3 จากปุ่ม click for keep data to tab daily row43:62 ไม่ได้ข้อมูลวันที่ตามต้องการ ตาม code ที่เพิ่มเข้าไปล่าสุด

clicktokeep.jpg

Code: Select all

Sub Picture2_Click()
'for copy an input data from tab sheet1 to tab daily

Dim source As Range
Dim Target As Range
    
    With Sheets("Sheet1")
        Set source = .Range("a3:ag22")
    End With
    
    With Sheets("daily")
        If .Range("a3") = "" Then
            Set Target = .Range("a3")
        Else
            Set Target = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
    
    
    Target.Interior.ColorIndex = 6

     Target.Value = Date - 1
'    Target.Font.Size = 12
    Target.Offset(0, 1).Resize(source.Rows.Count, _
        source.Columns.Count).Value = source.Value
    Range("a3").AutoFill Destination:=Range _
    ("a3", Range("b" & Rows.Count).End(xlUp).Offset(0, -1)) _
            , Type:=xlFillCopy

End Sub

พยายามหาว่าพลาดที่ตรงไหนแต่หาไม่เจอ? ที่ไม่ได้ข้อมูลวันที่ใน column A tab sheet daily เมื่อกด run macro จากหน้า tab sheet1 ครับ

อธิบายข้อ 1 มาใหม่ ค่อย ๆ แก้ปัญหาทีละเรื่องครับ

Code ชื่ออะไร อยู่ใน Module ไหน เขียนว่าอย่างไร ต้องการผลลัพธ์เป็นอย่างไร ติดขัดบรรทัดใดครับ

[snasui]

ปุ่มที่วงกลมไว้เป็นการ Run Macro ที่ชื่อ Group4_Click แต่ไม่พบว่าได้แนบมาด้วยครับ

[BTR]

แนวข้อมูลเดิมเป็น picture2 ตอนที่สร้าง macro
เมื่อสร้าง vba เสร็จ create word art ไปรวม group กับรูป picture2

group4 ไม่เคยสร้าง marco vba ไว้ครับ

ปุ่มที่วงกลมไว้เป็นการ Run Macro ที่ชื่อ Group4_Click แต่ไม่พบว่าได้แนบมาด้วยครับ

[snasui]

ตัวอย่างการปรับ Code ตามด้านล่างครับ

Code: Select all

With Sheets("daily")
    .Range("a3").AutoFill Destination:=.Range("a3", _
        .Range("b" & .Rows.Count).End(xlUp).Offset(0, -1)) _
        , Type:=xlFillCopy
End With

[BTR]

ผมอาจสื่อความไม่ชัดเจน ต้องขอโทษอาจารย์ด้วยครับ

ที่มา project นี้เป็นการบันทึกข้อมูลรายวัน, วันละ1ครั้ง, fix จำนวนคนงาน
โดยป้อนข้อมูลที่ tab sheet1 กดบันทึกข้อมูลสะสมใน tab sheet!Overview และ tab sheet Wage
พร้อมกับจัดเก็บข้อมูลที่ป้อนเข้ารายวันเพื่ออ้างอิง จาก sheet1 ไป sheet daily และ weekly โดยใช้ table โครงสร้างเดียวกันครับ

ต่อเนื่องจากที่ได้รับความช่วยเหลือแนะนำจากอาจารย์......
หลังจากนำ code (ล่าสุดที่กรุณาแนะนำ) เพิ่มเข้าไป วันที่ทั้งหมดใน sheet daily เปลี่ยนไปตาม column A3
row3:22, row23:42 และ row43:62 เป็นข้อมูล3ชุด ป้อนข้อมูลคนละวันที่กัน (สมมติว่าข้อมูลภายใน sheet1:c3:ag22 เปลี่ยนไปทุกวัน) ตามภาพนี้

current.jpg

โดยอยากให้ข้อมูล รวมวันที่ แสดงผลใน sheet daily!a3:ag22 เป็น 1 ชุดข้อมูลของวัน date-1 (งานที่ทำเมื่อวาน)
จากการ run code vba module 3, picture2 ใน sheet1
และต่อเนื่องกันในวันถัดไป ตามภาพนี้

target.jpg

แถบสีสลับรายวัน เพื่อให้เห็นแยกข้อมูลได้ง่ายเมื่อใช้งานครับ

[snasui]

จากโพสต์ด้านบน ขอทราบปัญหาที่เป็นและคำตอบที่ต้องการครับ

[BTR]

code ที่อาจารย์แนะนำให้ ผมใส่แบบนี้

Code: Select all

Sub Picture2_Click()
'for copy an input data from tab sheet1 to tab daily

Dim source As Range
Dim Target As Range
    
    With Sheets("Sheet1")
        Set source = .Range("a3:ag22")
    End With
    
    With Sheets("daily")
        If .Range("a3") = "" Then
            Set Target = .Range("a3")
        Else
            Set Target = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
    
    
    Target.Interior.ColorIndex = 6

     Target.Value = Date - 1
'    Target.Font.Size = 12
    Target.Offset(0, 1).Resize(source.Rows.Count, source.Columns.Count).Value = source.Value

With Sheets("daily")
   .Range("a3").AutoFill Destination:=.Range("a3", .Range("b" & .Rows.Count).End(xlUp).Offset(0, -1)), Type:=xlFillCopy
End With

End Sub

ทำให้วันที่ทั้งหมดใน sheet daily เปลี่ยนเป็นเหมือนกับ column A3 ตามภาพนี้
column A แถบสีเหลืองทั้งหมด เปลี่ยนเป็นวันเดียวกัน ต้นแบบ A3

current.jpg

โดยอยากให้แสดงข้อมูล รวมวันที่ ใน sheet daily!a3:ag22 เมื่อกดบันทึกข้อมูล 1 ครัั้ง
และแสดง background color xlnone เมื่อทำกดบันทึกข้อมูลวันถัดไป สลับกันทุกวัน ตามภาพที่สองนี้

target.jpg

อยากให้ช่วยแนะนำเพิ่มครับ

จากโพสต์ด้านบน ขอทราบปัญหาที่เป็นและคำตอบที่ต้องการครับ

[snasui]

หากต้องการให้สลับสีกันทุกครั้งที่มีการ Copy วันที่ จะต้องมีเซลล์ใด ๆ มาเก็บค่าการ Paste โดยให้มีค่า 0 และ 1 สลับกันไป

ยกตัวอย่างเช่น

1. เริ่มแรก ในเซลล์สำหรับใช้เก็บค่า ให้ใส่ค่า 0 เอาไว้ก่อน
2. เมื่อต้องการ Paste ให้ใช้ Code ตรวจสอบว่าเซลล์นั้นมีค่าเป็น ค่า 0 หรือ 1 โดยเงื่อนไขคือถ้าค่าเป็น 1 ให้ระบายสี ถ้าเป็น 0 ไม่ต้องระบายสี
3. สำหรับครั้งแรกเมื่อตรวจสอบด้วย Code จะพบว่าเป็นค่า 0 และเมื่อเป็นค่า 0 หลังจาก Paste แล้วไม่ต้องระบายสี และเปลี่ยนเป็นค่าในเซลล์นั้นให้เป็น 1
4. ในครั้งถัดไปเซลล์นั้นก็ยังเป็นค่า 1 ให้ใช้ Code ตรวจสอบค่าเช่นเดิม และเมื่อเป็นค่า 1 เมื่อ Paste แล้วเราจะระบายสี จากนั้นให้เปลี่ยนค่าเป็น 0