Page 2 of 2
Re: ขอสูตร filter data ใน google sheet
Posted: Thu Sep 17, 2020 3:46 pm
by Jancha
ที่ O4 สมมติว่าตัวเลขอยู่ระหว่างตัวอักษร xxxx50xxx จะยังไม่ได้ครับ
ที่ P4 ยังไม่ได้ตามเงือนไขทั้งหมดเหลือ ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0 ครับ
Re: ขอสูตร filter data ใน google sheet
Posted: Thu Sep 17, 2020 4:27 pm
by snasui
คอลัมน์ K นอกจากคำว่า "บาท" แล้วจะมีคำไหนได้อีกบ้างครับ
ส่วนคอลัมน์ P กรุณาปรับสูตรมาเองก่อน เพิ่มเงื่อนไขแรกสุดให้แสดงผลลัพธ์เป็น 0 หากเข้าเงื่อนไข มีตัวอย่างการใช้การเชื่อมให้ดูในสูตรนั้นอยู่แล้วครับ
Re: ขอสูตร filter data ใน google sheet
Posted: Thu Sep 17, 2020 6:10 pm
by Jancha
column k มีหลายอย่างครับเช่น น้ำมัน 500 บาท, ทางด่วน 50.-, -, #N/A หรืออาจมีค่าใช้จ่ายหลายๆอย่างรวมกันใน cell เดียวน้ำมัน 500ทางด่วน50 เป็นต้น ตรงส่วนนี้แล้วแต่คนจะกรอกข้อมูลใส่เข้ามา ระบุชัดเจนไม่ได้ หลักๆคือต้องดึกตัวเลขออกมาจากตัวอักษรเพื่อดูค่าใช้จ่ายครับ
P4=Arrayformula(IF(M$4:M="~Standby~",0,IF(M$4:M="~หยุด~",0,"ใส่สูตรจากกระทู้ #20")))
P4=Arrayformula(IF(M$4:M="~Standby~",0,IF(M$4:M="~หยุด~",0,IF(W$4:W&M$4:M="อา.`ทำงาน`",INDEX($DN$4:$DN,MATCH(B$4:B,$DE4:$DE,0)),if(W$4:W&M$4:M<>"อา.`ทำงาน`",INDEX(DM$4:DM,MATCH(B$4:B,DE$4:DE,0)))))))
ประมาณนี้เปล่าครับ
Re: ขอสูตร filter data ใน google sheet
Posted: Thu Sep 17, 2020 11:32 pm
by Jancha
snasui wrote: ↑Thu Sep 17, 2020 2:58 pm
ลองดูว่าใช่ที่ต้องการหรือครับ
O4
=Arrayformula(if(K4:K=0,0,right(SUBSTITUTE(K4:K," ",rept(" ",20)),20)+0))
P4
=Arrayformula(IF(W$4:W&M$4:M="อา.`ทำงาน`",INDEX($DN$4:$DN,MATCH(B$4:B,$DE4:$DE,0)),if(W$4:W&M$4:M<>"อา.`ทำงาน`",INDEX(DM$4:DM,MATCH(B$4:B,DE$4:DE,0)))))
แก้ไขครับ ขออภัยลงเงือนไขผิด บริเวณสีแดงต้องเป็น ไม่เท่า อา. แต่ว่าเท่ากับ `ทำงาน` ปรับให้อยู่ในรูปแบบ Arrayformula อย่างไรครับ
ถ้าเป็นสูตรลากมือ น่าจะเขียนแบบนี้ P4 = if(Or(M4="~Standby~",M4="~หยุด~"),0,IF(AND(M4="`ทำงาน`",W4="อา."),INDEX($DN$4:$DN,MATCH($B4,$DE$4:$DE,0)),IF(or(M4="`ทำงาน`",W4<>"อา."),INDEX($DM$4:$DM,MATCH($B4,$DE$4:$DE,0)))))
แจกแจงเข้าเงื่อนไขที่ P4 ได้ตามนี้
ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0
--> = if(Or(M4="~Standby~",M4="~หยุด~"),0
ถ้า column M = "`ทำงาน`" และ column W = "อา." ให้ lookup ค่าจาก column DN
-->IF(AND(M4="`ทำงาน`",W4="อา."),INDEX($DN$4:$DN,MATCH($B4,$DE$4:$DE,0))
ถ้า column M = "`ทำงาน`" และ column W <> "อา." ให้ lookup ค่าจาก column DM
--> IF(or(M4="`ทำงาน`",W4<>"อา."),INDEX($DM$4:$DM,MATCH($B4,$DE$4:$DE,0)))))
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Sep 19, 2020 9:52 am
by snasui
คอลัมน์ O ผมยังไม่มีวิธีทำให้เป็น Array ได้ครับ
ส่วนคอลัมน์ P ดูที่เขียนมาตามโพสต์ #23 ก็น่าจะหาคำตอบได้ ไม่ทราบติดตรงไหน อย่างไร กรุณาคีย์สูตรนั้นคาไว้จะได้ช่วยดูให้ได้ครับ
หลักการหนึ่งคือช่วงเซลล์ต้องเขียนแบบ Array ไม่ใช่เทียบกับเซลล์เดี่ยว ๆ ครับ
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Sep 19, 2020 10:35 am
by Jancha
โพสต์ #23 เงือนไขยังไม่ครบครับ
อันนี้เงือนไขครบแล้วครับ
P4 =ARRAYFORMULA(IF(B4:B="","", if((M4:M="~Standby~")+(M4:M="~หยุด~"),0,IF(M4:M&W4:W="`ทำงาน`อา.",INDEX($DN$4:$DN,MATCH($B4:B,$DE$4:$DE,0)),IF((M4:M="`ทำงาน`")+(W4:W<>"อา."),INDEX($DM$4:$DM,MATCH($B4:B,$DE$4:$DE,0)))))))
แต่ค่าที่ส่งกลับคืนมายังไม่ตรงเพี้ยนไป ดูเหมือน index+match จะใช้ไม่ได้กับ arrayformula หรือเปล่าครับไม่แน่ใจ
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Sep 19, 2020 3:12 pm
by snasui
ช่วยสรุปเงื่อนไขที่ครบถ้วนในแบบที่เป็นข้อความมาด้วย จะได้อ่านประกอบกับสูตรครับ
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Sep 19, 2020 6:24 pm
by Jancha
เงื่อนไขที่ P4
ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0
if((M4:M="~Standby~")+(M4:M="~หยุด~"),0,
ถ้า column M = "`ทำงาน`" และ column W = "อา." ให้ lookup ค่าจาก column DN
IF(M4:M&W4:W="`ทำงาน`อา.",INDEX($DN$4:$DN,MATCH($B4:B,$DE$4:$DE,0)),
ถ้า column M = "`ทำงาน`" หรือ column W <> "อา." ให้ lookup ค่าจาก column DM
IF((M4:M="`ทำงาน`")+(W4:W<>"อา."),INDEX($DM$4:$DM,MATCH($B4:B,$DE$4:$DE,0)))))
P4 =ARRAYFORMULA(IF(B4:B="","", if((M4:M="~Standby~")+(M4:M="~หยุด~"),0,IF(M4:M&W4:W="`ทำงาน`อา.",INDEX($DN$4:$DN,MATCH($B4:B,$DE$4:$DE,0)),IF((M4:M="`ทำงาน`")+(W4:W<>"อา."),INDEX($DM$4:$DM,MATCH($B4:B,$DE$4:$DE,0)))))))
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Sep 19, 2020 6:47 pm
by snasui
เงื่อนไขที่เป็นคำบรรยายที่ควรแจ้งมาคือตามนี้ครับ
- ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0
- ถ้า column M = "`ทำงาน`" และ column W = "อา." ให้ lookup ค่าจาก column DN
- ถ้า column M = "`ทำงาน`" หรือ column W <> "อา." ให้ lookup ค่าจาก column DM
ข้อ 2 และข้อ 3 ควรบอกด้วยว่านำค่าใดมาใช้ Lookup ในคอลัมน์นั้น ๆ
เท่าที่เขียนสูตรเทียบกับคำบรรยายมาก็ถือว่าถูกต้องแล้วครับ
Note:
หรือ แทนด้วยการ
บวกกันหรือใช้ฟังก์ชั่น
Or
และ แทนด้วยการ
เชื่อมหรือใช้ฟังก์ชั่น
And หรือใช้การ
คูณกัน
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Sep 19, 2020 10:40 pm
by Jancha
snasui wrote: ↑Sat Sep 19, 2020 6:47 pm
่ข้อ 2 และข้อ 3 ควรบอกด้วยว่านำค่าใดมาใช้ Lookup ในคอลัมน์นั้น ๆ
ขออภัยครับ นำค่าจาก column b มา lookup เงื่อนไขผ่านแล้วจากการทดสอบครับ น่าจะเกิดจาก index+match ใช้ lookup กับสูตร array ไม่ได้(หรืออาจได้ผมไม่เข้าใจเอง จริงๆอยากให้ได้ครับเพราะมันยืดหยุดกว่า vlookup ที่เพิ่ม column ภายหลังผลลัพธ์ไม่เปลี่ยน) เมื่อเปลี่ยนเป็น vlookup ได้ผลลัพธ์กลับมาตรงเลยครับ
x P4 =ARRAYFORMULA(IF(B4:B="","", if((M4:M="~Standby~")+(M4:M="~หยุด~"),0,IF(M4:M&W4:W="`ทำงาน`อา.",
INDEX($DN$4:$DN,MATCH($B4:B,$DE$4:$DE,0)),IF((M4:M="`ทำงาน`")+(W4:W<>"อา."),
INDEX($DM$4:$DM,MATCH($B4:B,$DE$4:$DE,0)))))))
✓ P4 =ARRAYFORMULA(IF(B4:B="","", if((M4:M="~Standby~")+(M4:M="~หยุด~"),0,IF(M4:M&W4:W="`ทำงาน`อา.",
VLOOKUP(B4:B,DE4:DQ,10,0),IF((M4:M="`ทำงาน`")+(W4:W<>"อา."),
VLOOKUP(B4:B,DE4:DQ,9,0))))))
Re: ขอสูตร filter data ใน google sheet
Posted: Thu Oct 29, 2020 10:47 am
by Jancha
ไปเจอ code apps script ที่ทำการ copy สูตรจาก row ด้านบนลง row ด้านล่างทุกครั้งเมื่อมีการเพิ่มข้อมูลจาก form เข้ามาที่ sheet ครับ
เนื่องจาก arrayformula อาจเขียนไม่ได้กับทุกสูตรเสมอไปดังนั้น code apps script จึงน่าจะตอบโจทย์ได้มากกว่า จาก script ถ้าต้องการเพิ่มเงือนไขอีกว่า เมื่อ copy สูตรลงมาแล้วให้ขึ้นไปยัง row ด้านบนของสูตรนั้นแล้ว copy paste แบบ value เพื่อทำลายสูตรให้ไฟล์ทำงานได้เร็วขึ้น ต้องเพิ่ม code ว่าอะไรครับรบกวนหน่อยครับ ขอบคุณ
Code: Select all
function copyDown() {
var Column1 = 15 //คอลัมน์ที่ มี สูตร ต้องการให้ copy ลง
var Column2 = 16 //คอลัมน์ที่ มี สูตร ต้องการให้ copy ลง
// var sheet = SpreadsheetApp.getActiveSheet(), lastRow = sheet.getLastRow(); //กำหนด sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"), lastRow = sheet.getLastRow(); //กำหนด sheet
var beforeLastRow = lastRow -1;
sheet.getRange(beforeLastRow, Column1).copyTo(sheet.getRange(lastRow ,Column1) , {contentsOnly:false}); //copy column 15
sheet.getRange(beforeLastRow, Column2).copyTo(sheet.getRange(lastRow ,Column2) , {contentsOnly:false}); //copy column 16
}
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Oct 31, 2020 3:21 pm
by snasui
ลักษณะงานน่าจะเป็นแบบนี้ครับ
บรรทัดแรกกำหนดให้เป็นสูตรเอาไว้เพื่อจะใช้คัดลอกไปยังเซลล์ที่ว่างหลังสุด จากนั้นคัดลอกบรรทัดหลังสุดแล้ววางเป็น Value
Code สำหรับการวางเป็น Value คือกำหนดค่าเป็น
...{contentsOnly:true})
Re: ขอสูตร filter data ใน google sheet
Posted: Sat Oct 31, 2020 6:46 pm
by Jancha
ขอบคุณครับทำได้แล้วครับ
Code: Select all
function copyDownTest() {
var Column1 = 15 //คอลัมน์ที่ มี สูตร ต้องการให้ copy ลง
var Column2 = 16 //คอลัมน์ที่ มี สูตร ต้องการให้ copy ลง
// var sheet = SpreadsheetApp.getActiveSheet(), lastRow = sheet.getLastRow(); //กำหนด sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"), lastRow = sheet.getLastRow(); //กำหนด sheet
var beforeLastRow = lastRow -1;
sheet.getRange(beforeLastRow, Column1).copyTo(sheet.getRange(lastRow ,Column1) , {contentsOnly:false}); //copy column 15
sheet.getRange(beforeLastRow, Column1).copyTo(sheet.getRange(beforeLastRow, Column1) , {contentsOnly:true}); //copy column 15 row รองสุดท้ายเพื่อ paste value
sheet.getRange(beforeLastRow, Column2).copyTo(sheet.getRange(lastRow ,Column2) , {contentsOnly:false}); //copy column 16
sheet.getRange(beforeLastRow, Column2).copyTo(sheet.getRange(beforeLastRow, Column2) , {contentsOnly:true}); //copy column 16 row รองสุดท้ายเพื่อ paste value
}