Pautan untuk ikon : https://www.flaticon.com/ atau https://icons8.com/
Sortn: Kegunaan untuk remove duplicate data; boleh pilih yg data terkini dipaparkan atau data yg lebih lama untuk dipaparkan
Formula untuk Data terkini: =sortn(A2:F,2000,2,B2:B&D2:D&F2:F,False)
Formula untuk Data lama: =sortn(sort(A2:F,2,false,3,false),2000,2,4,False,6,FALSE)
SortN Data Terkini (Penggiliran PdPC) : =sortn(sort(A2:C,1,false),2000,2,2,false)
Formula lain: =sortn(a2:g,2000,2,b2:b&d2:d&f2:f,false)
Tutorial Penggunaan:
Arrayformula: =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B,HADIRM,2,0),"")
Arrayformula advance: =if(A2:A<>"",ARRAYFORMULA(A2:A))
Subsitute: ={"ID SISTEM";ARRAYFORMULA(SUBSTITUTE(B2:B," ",""))}
Split: =arrayformula(iferror(split(A2:A," "),""))
Query: =query(datb!A1:H,"Select * Where B='Pembinaan Portal PSS Peringkat Sekolah & KPM'")
Vlookup: =VLOOKUP(B2:B,HADIRM,2,0
Qrcode: =ARRAYFORMULA(IF(J2:J="","","https://chart.googleapis.com/chart?cht=qr&chs=200x200&choe=UTF-8&chl="&ENCODEURL(J2:J)))
Nombor Siri: =ArrayFormula(IF(LEN(F:F),if(ROW(F:F)=1,"NOMBOR SIRI",TEXT(ROW(E:E)-1,"000000000000")),))
Auto Number: BIL={"BIL";IFERROR(SEQUENCE(COUNTA(B2:B),1))}
Countifs: =arrayformula(COUNTIFS('FORM NILAM'!H:H,E1,'FORM NILAM'!B:B,A2:A3102))
Sumifs: =SUMIFS(Combine!E:E,Combine!B:B,B2)
Appsheet Promo: APPS4CV19
Whatsapps api: https://api.whatsapp.com/send?phone=+6
Importrange: =IMPORTRANGE("1ymTVu0-aLwaVzQ18lljY20y5INaCGfmBQFiHSyFMSBM","a:h")
Skript bagi check box: SkripCikguSarid
If : =arrayformula(if('Form responses 1'!E2:E="Ya","1","0"))
Salin Cepat: http://tiny.cc/salincepat
Formula utk gambar data studio: =arrayformula(if(row(D2:D)=1,"photo id",substitute(D2:D,"open?","uc?export=view&")))
Formula utk data studio banyak: =arrayformula(if(row(A:A)=1,"photo1 photo2 photo3 photo4 photo5",substitute(D:D,"open?","uc?export=view&")))
Split gambar: =arrayformula(IFERROR(SPLIT(E:E,", ")))
NOTA BOT CIKGU MIE : https://docs.google.com/presentation/d/19TbYyce1cyTzLwOyGVTspknmPoEr8zhLBRE-t-D9w5o/edit?usp=sharing
Advance Split Cikgu Mi: https://docs.google.com/document/d/16vhEltECp6l71RvtPqikrl5UpLBgag-IwQyYmUCF9B0/edit?usp=sharing
Formula carian : lebih dari 1 item menggunakan query oleh cikgu mi : https://docs.google.com/spreadsheets/d/1B6Y3BlTKtDudclzXrMNaDLTA9klyOoowEaLb8wY4r08/edit?usp=sharing
formula utk gambar | HYPERLINK(URL SIJIL,IMAGE("https://i.ibb.co/YDjbmv0/icons8-desktop-download-24.png" ))
Formula trim: =ArrayFormula(IFERROR(TRIM(split(D2:D,",")),""))
data studio: formula jika kotak null : case when Timestamp is null then "TIDAK HADIR" else "HADIR" end
utk spreadsheet: formula substitute={"ID MURID";ARRAYFORMULA(SUBSTITUTE(B2:B," ",""))}
query colum tertentu sahaja: =QUERY(importrange("1AJCuRPQofmKwVnkMuAFXp8twdiKG8KNUCOJ3dRwuEFc","A4:BJ"),"select Col1, Col4, Col14, Col3, Col9, Col10, Col40, Col56 where Col1 is not Null")
Slider Animasi : https://docs.google.com/presentation/d/14KXgcJon4xzUJCCYd4Yi9ac9LIR_IShXAnPC-eRNA50/edit#slide=id.p1
Formula Apps eRPH SK (2) - PILIH ENUM : SELECT(DSKP[SK],AND([SUBJEK]=[_THISROW].[SUBJEK],[TINGKATAN]=[_THISROW].[TINGKATAN]))
Formula Apps eRPH SP (2) - PILIH ENUM : SELECT(DSKP[SP],AND([SUBJEK]=[_THISROW].[SUBJEK],[TINGKATAN]=[_THISROW].[TINGKATAN]))
regex utk replace (, ..atau space bar) : =REGEXREPLACE(B2,"[ ]","")
Spreadsheet tanpa Sript: ={"TIMESTAMP","NAMA PELAJAR","KELAS";ArrayFormula(QUERY(SPLIT( FLATTEN(RekodM!BP2:BP &"~"& SPLIT(substitute(RekodM!BQ2:BQ,", ",","),",",1,1) &"~"&RekodM!C2:C),"~",1,0),"where Col2 is not null order by Col1",0))}
Trime menggunakan Regex place : ={"nama";arrayformula(REGEXREPLACE(B2:B,"[ ]",""))}
Buang data responses di Google Form : function delRsp() {FormApp.getActiveForm().deleteAllResponses();}