ขอสูตร filter data ใน google sheet

[Jancha]

ที่ O4 สมมติว่าตัวเลขอยู่ระหว่างตัวอักษร xxxx50xxx จะยังไม่ได้ครับ
ที่ P4 ยังไม่ได้ตามเงือนไขทั้งหมดเหลือ ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0 ครับ

[snasui]

คอลัมน์ K นอกจากคำว่า "บาท" แล้วจะมีคำไหนได้อีกบ้างครับ

ส่วนคอลัมน์ P กรุณาปรับสูตรมาเองก่อน เพิ่มเงื่อนไขแรกสุดให้แสดงผลลัพธ์เป็น 0 หากเข้าเงื่อนไข มีตัวอย่างการใช้การเชื่อมให้ดูในสูตรนั้นอยู่แล้วครับ

[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)))))))
ประมาณนี้เปล่าครับ

[Jancha]

ลองดูว่าใช่ที่ต้องการหรือครับ

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)))))

[snasui]

คอลัมน์ O ผมยังไม่มีวิธีทำให้เป็น Array ได้ครับ

ส่วนคอลัมน์ P ดูที่เขียนมาตามโพสต์ #23 ก็น่าจะหาคำตอบได้ ไม่ทราบติดตรงไหน อย่างไร กรุณาคีย์สูตรนั้นคาไว้จะได้ช่วยดูให้ได้ครับ

หลักการหนึ่งคือช่วงเซลล์ต้องเขียนแบบ Array ไม่ใช่เทียบกับเซลล์เดี่ยว ๆ ครับ

[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 หรือเปล่าครับไม่แน่ใจ

[snasui]

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

[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)))))))

[snasui]

เงื่อนไขที่เป็นคำบรรยายที่ควรแจ้งมาคือตามนี้ครับ

1. ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0
2. ถ้า column M = "`ทำงาน`" และ column W = "อา." ให้ lookup ค่าจาก column DN
3. ถ้า column M = "`ทำงาน`" หรือ column W <> "อา." ให้ lookup ค่าจาก column DM

ข้อ 2 และข้อ 3 ควรบอกด้วยว่านำค่าใดมาใช้ Lookup ในคอลัมน์นั้น ๆ

เท่าที่เขียนสูตรเทียบกับคำบรรยายมาก็ถือว่าถูกต้องแล้วครับ

Note:
หรือ แทนด้วยการบวกกันหรือใช้ฟังก์ชั่น Or
และ แทนด้วยการเชื่อมหรือใช้ฟังก์ชั่น And หรือใช้การคูณกัน

[Jancha]

่ข้อ 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))))))

[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
}

[snasui]

ลักษณะงานน่าจะเป็นแบบนี้ครับ

บรรทัดแรกกำหนดให้เป็นสูตรเอาไว้เพื่อจะใช้คัดลอกไปยังเซลล์ที่ว่างหลังสุด จากนั้นคัดลอกบรรทัดหลังสุดแล้ววางเป็น Value

Code สำหรับการวางเป็น Value คือกำหนดค่าเป็น ...{contentsOnly:true})

[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
}