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

[Jancha]

รบกวนขอสูตรที่ sheet Database ครับ

1. ที่ cell AB3 ดึงข้อมูลจาก row สุดท้ายใน column B มาเป็นหลักสำหรับไว้ตรวจสอบเงื่อนไข
2. ที่ column ac4 ลงไป ให้ filter ช่วงวันที่อิงตามช่วง ac1:ac2 ถ้าชื่ออ้างอิงใน cell ab3 ยังรันวันที่ไม่ถึง cell ac2 ให้แสดงถึงวันที่ล่าสุดที่อัพไว้ใน column D (จากตัวอย่างเริ่มที่ 26/7/2020 - 20/8/2020)
3. ที่ column ad4 ลงไป filter ข้อมูลจาก column M อ้างอิงตามชื่อใน cell ab3 โดยถ้าไม่พบข้อมูลตรงวันที่ให้แทนด้วย `ยังไม่อัพ งาน` กรณีตรวจพบข้อมูลซ้ำในวันที่เดียวกันแทนด้วย `อัพงานซ้ำ`
4. AE4:AE... รวมข้อมูล AC4:AD.... (จัดรูปแบบให้เป็นวันที่ //ข้อมูล)

ลิงค์ google sheet ที่ขอสูตร
https://docs.google.com/spreadsheets/d/ ... 2052305296




หมายเหตุ
*** google sheet นี้รับข้อมูลจาก google form สูตรต้อง support กับ row ที่เพิ่มขึ้น
*** พอจะมีวิธีเขียนสูตรที่หัว column เพียง cell เดียวไม่ต้องลากสูตรลงสูตรจะทำงานลากลงอัตโนมัติ Automatically Drag Down เหมือนใน ms365 พวก function filter อะไรประมาณนั้นครับ


ตัวอย่างสูตรประเภท Drag Down

[snasui]

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

AB3

=Lookup(char(255),B:B)

AC4

=If(Or(Rows(AC$4:AC4)+$AC$1-1>Index(A:A,Match(char(255),B:B)),Rows(AC$4:AC4)+$AC$1-1>$AC$2),"",Rows(AC$4:AC4)+$AC$1-1)

AD4

=IF(AC4<>"",Index(FILTER($M$4:$M1000,$B$4:$B1000=$AB$3),Rows(AD$4:AD4)),"")

[Jancha]

column ad ยังต้องการอีกสองเงื่อนไขครับ
1. ถ้าไม่พบข้อมูลในวันที่นั้นๆให้แทนด้วย `ยังไม่อัพ งาน` (เป็นกรณีที่ลืมอัพเดทงานในวันนั้นๆ จึงไม่มีข้อมูล)
2. กรณีตรวจพบข้อมูลซ้ำในวันที่เดียวกันแทนด้วย `อัพงานซ้ำ` (เป็นกรณีที่อัพเดทงานเกินมามากกว่า 1 รอบ)

[snasui]

ลองปรับสูตรเป็นด้านล่างครับ

AD4

=IfNa(If(CountA(Filter($M$4:$M$1000,$B$4:$B$1000=$AB$3,$D$4:$D$1000=$AC4))>1,"อัพงานซ้ำ",Filter($M$4:$M1000,$B$4:$B1000=$AB$3,$D$4:$D$1000=$AC4)),"ยังไม่อัพงาน")

[Jancha]

ขอบคุณสำหรับสูตรครับอาจารย์ พอจะทำให้สูตรที่ลากเลยวันที่ใน column ad, ae เสมือนเป็น cell เปล่าๆไม่มีค่าอะไรในนั้นได้ไหมครับ เนื่องจากผมนำค่าที่ทำได้จากสูตรเหล่านี้ไปส่งเข้า line group แต่ปรากฎว่ามีค่าว่างจากสูตรถูกส่งไปด้วยครับ

[snasui]

ใช้สูตร If เข้าไปช่วยได้ครับ

=If(AC4="","",Current_Formula)

Current_Formula คือสูตรเดิมที่เขียนไว้แล้วครับ

ผลลัพธ์ค่าว่างที่ได้จากสูตรไม่เทียบเท่ากับเซลล์ว่างเปล่าครับ

การนำผลลัพธ์ไปใช้ในการส่ง LINE จะต้องเช็คก่อนว่าเป็นเซลล์ที่มีค่าหรือไม่ หากไม่มีค่าก็ไม่ต้องส่ง เช่นนี้เป็นต้นครับ

[Jancha]

เช่นนั้นรบกวนแก้ code ให้ด้วยครับอาจารย์

Code: Select all

function getDatatest() 
{
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database")
  var data = sheet.getDataRange().getValues();
  var token = ["token group1", "token group2"];
  
  
      var msg = '\n      ' + data[2][27] + '\n––––•(-• (Time line) •-)•––––\n\n'
      for (var i=3; i<18; i++) 
          {
            msg = msg + data[i][30] + '\n'  
          }          
      for(var j=0; j<token.length; j++)
          {
            sendLineNotify(msg, token[j]);
          }
          
     //แบ่งข้อมูลส่งรอบ 2     
     var msg2 = "\n"
      for (var k=18; k<data.length; k++) 
          {
            msg2 = msg2 + data[k][30] + '\n'  
          }          
      for(var l=0; l<token.length; l++)
          {
            sendLineNotify(msg2, token[l]);
          }
}


/////////////////////// send Multigroup
function sendLineNotify(message, token)      {
  var options =
   {
     "method"  : "post",
     "payload" : 
     {
       "message" : message,
     },
     "headers" : {"Authorization" : "Bearer " + token}
   };
   UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

[snasui]

ลองดูตัวอย่างนี้ซึ่งเป็นการใช้ If เข้าไปดักก่อนที่จะนำข้อความมาเชื่อมกันครับ

Code: Select all

if(data[i][30].length > 0){
   msg = msg + data[i][30] + '\n'
}

[Jancha]

ใช้ได้ตามที่ต้องการเลยครับอาจารย์ ขอบคุณนะครับ

Code: Select all

function getDatatest() 
{  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database")
  var data = sheet.getDataRange().getValues();
  var token = ["token group1", "token group2"];
  
  
      var msg = '\n      ' + data[2][27] + '\n––––•(-• (Time line) •-)•––––\n\n'
      for (var i=3; i<18; i++) 
          {
            if(data[i][30].length > 0){        //ใส่ if ดักจับค่าว่าง
              msg = msg + data[i][30] + '\n'
             }
          }          
      for(var j=0; j<token.length; j++)
          {
            sendLineNotify(msg, token[j]);
          }
          
     //แบ่งข้อมูลส่งรอบ 2     
     var msg2 = '\n      ' + data[2][27] + '\n––––•(-• (Time line) •-)•––––\n\n'
      for (var k=18; k<32; k++) 
          {
            if(data[k][30].length > 0){        //ใส่ if ดักจับค่าว่าง
             msg2 = msg2 + data[k][30] + '\n'  
            }
          }          
      for(var l=0; l<token.length; l++)
          {
            sendLineNotify(msg2, token[l]);
          }
}
/////////////////////// send Multigroup
function sendLineNotify(message, token)      {
  var options =
   {
     "method"  : "post",
     "payload" : 
     {
       "message" : message,
     },
     "headers" : {"Authorization" : "Bearer " + token}
   };
   UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

ขอสอบถามเพิ่มเติมอีกนะครับ กรณีถ้าต้องการให้ line notify ส่งข้อความหาเพื่อนในกลุ่มชนิดเจาะจงตัวตัวบุคคล เหมือนกับตอนเราพิมพ์ @ชื่อเพื่อน ในกลุ่ม line แล้วกดส่งคนนั้นจะรู้ว่ามีข้อความถึงตนเองได้ แต่กรณีส่งด้วย line notify เข้าไปยังกลุ่ม @ชื่อเพื่อน นั้นจะกลายเป็นเพียงข้อความธรรมดา(ตามภาพประกอบ) พอจะมี code สลับสนุนเพื่อการนี้ไหมครับ

[snasui]

น่าจะทำไม่ได้ครับ https://www.line-community.me/ja/questi ... dcfa2f1305

[Jancha]

ครับ ขอบคุณครับอาจารย์สำหรับความช่วยเหลือ

[Jancha]

รบกวนปรับสูตรที่ column O:Z ให้สูตรลากลงโดยอัตโนมัติ (auto formula down automatically) ทีครับ คือเมื่อมีการกรอก form แล้วจะมีบรรทัดเพิ่มเข้ามาใหม่เรื่อยๆ ต้องการให้สูตรทำงานได้เองตามบรรทัดที่เพิ่มขึ้นครับ


https://docs.google.com/spreadsheets/d/ ... 2052305296

[snasui]

หากลากสูตรเผื่อไว้ตามที่คิดว่าข้อมูลจะขยายไปถึง จะติดปัญหาอะไรหรือไม่ครับ

[Jancha]

กรณีนี้เป็นแบบรับข้อมูลจาก google form ครับเป็นการแทรกแถวข้อมูลเข้ามาจึงทำให้สูตรหายขาดตอนไปครับ

[snasui]

กรณีเป็นการแทรกแถวก็ต้องอาศัยพวก Programming เช่น JavaScript เข้าไปช่วยถึงจะเติมสูตรในบรรทัดที่ว่างได้ครับ

[Jancha]

พอทำได้บาง column แล้วครับ โดยดูตัวอย่างการใช้ ARRAYFORMULA() จากคลิปนี้ https://youtu.be/0v-hQ3EecdE?t=470 คลุมสูตร ใส่สูตรแค่ row ที่ 4 เท่านั้นที่เหลือสูตรลากลงให้อัตโนมัติครับ

https://docs.google.com/spreadsheets/d/ ... 2052305296
column ที่ยังติดอยู่ 2 column คือ
column O4:O เงื่อนไขคือ ดูดค่าจาก column K ตัดเอาเฉพาะตัวเลขออกจากตัวอักษร
column P4:P เงื่อนไขว่า
ถ้า column M = "`ทำงาน`" และ column W = "อา." ให้ lookup ค่าจาก column DN
ถ้า column M = "`ทำงาน`" และ column W <> "อา." ให้ lookup ค่าจาก column DM
ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0

column ที่ทำได้แล้วคือ
Q4 =ARRAYFORMULA( IF(J4:J="","",J4:J*50))
R4 =ARRAYFORMULA(IF(B4:B="","",VLOOKUP(B4:B,DE4:DQ,13,0)))
S4 =ARRAYFORMULA(IF(P4:P+Q4:Q+R4:R=0,"",P4:P+Q4:Q+R4:R))
T4 =ARRAYFORMULA(IF(M4:M="","",IF(M4:M="`ทำงาน`","ทำงาน","หยุด")))
U4 =ARRAYFORMULA(IF(D4:D="","","WK_"&WEEKNUM(D4:D)))
V4 =ARRAYFORMULA(IF(D4:D="","",int(text(D4:D,"dd"))))
W4 =ARRAYFORMULA(IF(D4:D="","",text(D4:D,"ddd")))
X4 =ARRAYFORMULA(IF(D4:D="","", if($D4:D="","",IFERROR(IF(MONTH(D4:D)=1,"Jan",IF(MONTH(D4:D)=2,"Feb",IF(MONTH(D4:D)=3,"Mar",IF(MONTH(D4:D)=4,"Apr",IF(MONTH(D4:D)=5,"May",IF(MONTH(D4:D)=6,"Jun",IF(MONTH(D4:D)=7,"Jul",IF(MONTH(D4:D)=8,"Aug",IF(MONTH(D4:D)=9,"Sep",IF(MONTH(D4:D)=10,"Oct",IF(MONTH(D4:D)=11,"Nov",IF(MONTH(D4:D)=12,"Dec","")))))))))))),""))))
Y4 =ARRAYFORMULA(IF(D4:D="","",int(text(D4:D,"yyyy"))))
Z4 =ARRAYFORMULA(IF(D4:D="","",IF(D4:D<$AC$1,"✓","O")))

[snasui]

แบบนี้ไม่เรียกว่าแทรก แต่เป็นการต่อท้าย ไม่ทราบว่าถ้าลากสูตรไว้จะเกิดการแทรกลักษณะไหน อย่างไร ลองจับภาพมาดูกันครับ

[Jancha]

column A:N รับค่าจาก google form
column O:Z เขียนสูตรไว้ที่ row 4 ของแต่ละ column สูตรทำงานอัตโนมัติเมื่อได้ค่าจาก form เข้ามา
column O:P ยังเขียนไม่สำเร็จ (ต้องการความช่วยเหลือ)
column Q:Z เขียนได้สำเร็จแล้ว (ดูจากภาพเมื่อได้ค่าจาก form แล้วสูตรรันอัตโนมัติ)

จากภาพ column O:P เมื่อลากสูตรรอไว้ จากนั้นกด submit google form ค่าที่รับเข้ามาจะต่อท้ายจากค่าเดิมใน sheet สูตรที่ลากรอไว้จะถูกแทรกด้วยค่าว่าง ต่างจาก column Q:Z ที่เขียนสูตรไว้ที่ row4 ของแต่ละ column เท่านั้น ไม่ต้องลากสูตรมารอจนสุดถึง row สุดท้ายก็ทำงานได้ทันทีเมื่อมีค่าจาก form เข้ามา ดังนั้นจึงต้องการวิธีเขียนสูตรที่ column O:P ให้ได้อย่าง column Q:Z ครับ

[Jancha]

column Q:Z สูตรที่เขียนเสร็จแล้วอยู่ที่ row 4 ของแต่ละ column ครับ ไม่ต้องลากสูตรลงมา ค่าที่ได้จากสูตรสามารถลบเล่นได้จะกลับสู่สภาพเดิมได้เท่าที่ตัวสูตร row 4 ยังมีอยู่ ซึ่ง column O:P ยังทำเช่นเดียวกันไม่ได้ ตอนนี้ก็ manual ลากสูตรเองอยู่ครับ ต้องรบกวนช่วยปรับแก้สูตรให้ด้วยครับ

column O4:O เงื่อนไขคือ ดูดค่าจาก column K ตัดเอาเฉพาะตัวเลขออกจากตัวอักษร
column P4:P เงื่อนไขว่า
ถ้า column M = "`ทำงาน`" และ column W = "อา." ให้ lookup ค่าจาก column DN
ถ้า column M = "`ทำงาน`" และ column W <> "อา." ให้ lookup ค่าจาก column DM
ถ้า column M = "`หยุด`" หรือ "~Standby~" ให้ lookup ค่าเป็น 0

[snasui]

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

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