iT邦幫忙

0

Google試算表 輸入數字 自動填入表格

今天想要做一個休假排班表格,想要有一種方式是,我在下方設立一個輸入的小表格,表格內容左邊為員工名字,右邊為休假日期。想要在休假日期輸入日期後,上方休假表的日期內會自動輸入休。
請問各位大大這是有辦法辦到的嗎?

dragonH iT邦超人 5 級 ‧ 2019-10-02 10:02:05 檢舉
應該有

1 個回答

1
阿展展展
iT邦好手 1 級 ‧ 2019-10-02 11:04:29
最佳解答
=CONCATENATE(IF(B5=1,A5,""),IF(B6=1,A6,""),IF(B7=1,A7,""),IF(B8=1,A8,""),IF(B9=1,A9,""),IF(B10=1,A10,""))
=CONCATENATE(IF(C5=1,A5,""),IF(C6=1,A6,""),IF(C7=1,A7,""),IF(C8=1,A8,""),IF(C9=1,A9,""),IF(C10=1,A10,""))
=CONCATENATE(IF(D5=1,A5,""),IF(D6=1,A6,""),IF(D7=1,A7,""),IF(D8=1,A8,""),IF(D9=1,A9,""))
=CONCATENATE(IF(E5=1,A5,""),IF(E6=1,A6,""),IF(E7=1,A7,""),IF(E8=1,A8,""),IF(E9=1,A9,""),IF(E10=1,A10,""))

https://ithelp.ithome.com.tw/upload/images/20191002/201195463ZHYdvbnrG.jpg

不知道這樣有沒有符合你的需求

CONCATENATE 中間還能再插空格或是其他的東西

google表單真好玩

https://docs.google.com/spreadsheets/d/1gIZOYrQ9oPuvjfBpdSI5msBr3-1Qu8uPqPVXDYJY8qw/edit?usp=sharing

看更多先前的回應...收起先前的回應...

大致上可以,但是有幾個問題
1.出來的人名需要隔開(eric、louis)
2.https://ithelp.ithome.com.tw/upload/images/20191002/201217806tzhTlYTya.png
因為我的表格是這樣 想請問有辦法修改嗎

=CONCATENATE(IF(B5=1,A5,""),"  ",IF(B6=1,A6,""),"  ",IF(B7=1,A7,""),"  ",IF(B8=1,A8,""),"  ",IF(B9=1,A9,""),"  ",IF(B10=1,A10,""))

或是「空格」要用「、」「:」「=」「-」取代都可以

謝謝你的回答,我了解許多東西 ,若有更新麻煩在貼上來:)謝謝你

/images/emoticon/emoticon25.gif

您好 請教下如果我想要有數字才有分隔號/空白要怎設定呢,我現在會出現這樣https://ithelp.ithome.com.tw/upload/images/20191002/20121780M8KWQbibRH.png

你的function怎麼寫的~~

https://ithelp.ithome.com.tw/upload/images/20191002/20121780BSD0itvMlS.png
這樣看得清楚嗎 還是我直接CTRL +C上來

呃~~~把你的function複製貼上來
你的 "、"是一定會出現的
IF(C4='休',C2,'')
這個IF的意思是:true時印出C2 false時印出 ''

=CONCATENATE(IF(C4="休",C2,""),"、",IF(D4="休",D2,""),"、",IF(E4="休",E2,""),"、",IF(F4="休",F2,""),"、",IF(G4="休",G2,""),"、",IF(H4="休",H2,""),"、",IF(I4="休",I2,""),"、",IF(J4="休",J2,""),"、",IF(K4="休",K2,""),"、",IF(L4="休",L2,""),"、",IF(M4="休",M2,""),"、",IF(N4="休",N2,""),"、",IF(O4="休",O2,""),"、",IF(P4="休",P2,""),"、",IF(Q4="休",Q2,""),"、",IF(R4="休",R2,""),"、",IF(S4="休",S2,""),"、",IF(T4="休",T2,""),"、",IF(U4="休",U2,""),"、",IF(V4="休",V2,""),"、",IF(W4="休",W2,""),"、",IF(X4="休",X2,""),"、",IF(Y4="休",Y2,""),"、",IF(Z4="休",Z2,""),"、",IF(AA4="休",AA2,""),"、",IF(AB4="休",AB2,""),"、",IF(AC4="休",AC2,""),"、",IF(AD4="休",AD2,""),"、",IF(AE4="休",AE2,""),"、",IF(AF4="休",AF2,""),"、",IF(AG4="休",AG2,""),"、",)
是的我也是剛剛才想到這樣打一定會出現,但是我如果打在IF內也不行,請問有什麼方法是可以IF是TRUE的時候才會出現分隔符號的嗎?

=CONCATENATE(IF(B16="休",B15,""),IF(B16="休","、",""),IF(C16="休",C15,""),IF(C16="休","、",""),IF(D16="休",D15,""),IF(D16="休","、",""),IF(E16="休",E15,""),IF(E16="休","、",""),IF(F16="休",F15,""),IF(F16="休","、",""))

分開來打
https://ithelp.ithome.com.tw/upload/images/20191002/20119546gEwQtxOes1.jpg

可是我是要我有休才會有分隔號,這樣的話如果我休的日期更改她就會在亂掉了不是嗎

我了解了 等於一格要有兩個IF去跑的意思嗎

我要發表回答

立即登入回答