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

google google 試算表

ss19960429ss 2019-10-02 09:57:26 ‧ 5367 瀏覽

分享至

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

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

應該有

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

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

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

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

google表單真好玩

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

回應 14
分享
檢舉

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

ss19960429ss iT邦新手 5 級 ‧ 2019-10-02 13:39:53 檢舉

大致上可以，但是有幾個問題
1.出來的人名需要隔開(eric、louis)
2.
因為我的表格是這樣想請問有辦法修改嗎

阿展展展 iT邦好手 1 級 ‧ 2019-10-02 13:41:50 檢舉

=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,""))

阿展展展 iT邦好手 1 級 ‧ 2019-10-02 13:42:53 檢舉

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

ss19960429ss iT邦新手 5 級 ‧ 2019-10-02 14:00:00 檢舉

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

阿展展展 iT邦好手 1 級 ‧ 2019-10-02 14:05:46 檢舉

ss19960429ss iT邦新手 5 級 ‧ 2019-10-02 14:08:17 檢舉

您好請教下如果我想要有數字才有分隔號/空白要怎設定呢，我現在會出現這樣

阿展展展 iT邦好手 1 級 ‧ 2019-10-02 14:09:47 檢舉

你的function怎麼寫的~~

ss19960429ss iT邦新手 5 級 ‧ 2019-10-02 14:14:59 檢舉

這樣看得清楚嗎還是我直接CTRL +C上來

阿展展展 iT邦好手 1 級 ‧ 2019-10-02 14:18:59 檢舉

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

ss19960429ss iT邦新手 5 級 ‧ 2019-10-02 14:22:00 檢舉

=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的時候才會出現分隔符號的嗎?

阿展展展 iT邦好手 1 級 ‧ 2019-10-02 14:28:26 檢舉

=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="休","、",""))