請教版上大神GOOGLE EXCEL 公式技巧

柯吉霸
個人積分：0分
文章編號：86737459

0分

樓主

2023-01-25 19:38

請問版上大大~

為了不讓同仁太過操勞，也不能讓同仁連續休假太多避免不公，
因此小弟有統計班表"連續上班"跟"連續休假"次數的需求，
想請問是否有大神可以指導公式的編寫技巧，在此先對看此文章的各位說聲謝謝，
班表如下圖~

請教版上大神GOOGLE EXCEL 公式技巧

2023-01-25 19:38 發佈

文章關鍵字請教版公式 Google excel 技巧

天馬座 - 星矢

天馬座 - 星矢
個人積分：77分
文章編號：86740041

77分

2樓

2023-01-26 5:23

yt上很多教學，去看一下就會了

柯吉霸樓主

好的~我有嘗試爬文卻找不到相關訊息, 才會上來請教大家的指導可能會有更好的方式, 我會再去YT找看看,謝謝你的提醒唷

2023-01-26 18:59

jaredsam

jaredsam
個人積分：1547分
文章編號：86740869

1547分

3樓

2023-01-26 10:02

我只會輔助格~

比如
資料是從A3:A999 你可以在E3打
=IF(B1=5,"",IF(B2="",IF(COUNTIF(A3:A7,"值班")=5,5,IF(COUNTIF(A3:A6,"值班")=4,4,IF(COUNTIF(A3:A5,"值班")=3,3,""))),""))

第一個與第二個判斷主要是避免連續，因為優先判斷連續5天，最後再3天，但會出現符合5天的下一格符合4天，在下一格符合3天

-------------------------------------------
另外上面只是輔助格
最後你連續5天處，要=countif(E3:E999,5)

柯吉霸樓主

感謝提醒~ 確實多了"=" 重新測試功能正常了, 謝謝大大^^~ 不過剛才有發現個小問題, 當連續上班6天時, 會導致連續5天上班會計算2次, 想問問這個是否有什麼好的解決方式呢? m(＿＿)m

2023-01-26 20:20

jaredsam

因為你上面只提說連續345阿...，你可以稍微修改，或是把完整需求說出來...，不然就會讓天數少的重複出現

2023-01-26 22:26

xccx010x

xccx010x
個人積分：1002分
文章編號：86741275

1002分

4樓

2023-01-26 11:21

可以使用连续性函数来计算连续上班或连续休假的天数。

例如，如果你有一个班表的表格，其中一列是日期，另一列是上班/休假的状态，可以使用以下公式：

计算连续上班天数：
=MAX(IF(A2:A31=A2,IF(B2:B31="上班",COUNTIF(A2:A31,A2:A31),""),0))

计算连续休假天数：
=MAX(IF(A2:A31=A2,IF(B2:B31="休假",COUNTIF(A2:A31,A2:A31),""),0))

其中 A 列是日期列，B 列是上班/休假状态列。

在上述公式中，MAX 函数用于找出最大值，IF 函数用于判断当前日期是否与前一天相同，COUNTIF 函数用于统计连续相同状态的天数。

柯吉霸樓主

謝謝大大指導~ 不才小弟嘗試將你公式轉貼至表格中卻出現錯誤，是否我還有遺漏其它設定，希望大大能再不吝指導，再次感謝~錯誤訊息「MAX 函式第 1 參數」需要數字值，但「」是文字，無法強迫成為數字。

2023-01-26 18:56

錦子

錦子
個人積分：2128分
文章編號：86743887

2128分

5樓

2023-01-26 19:13

我的是用EXCEL作法：
在3次工作表B2輸入公式
=IF(COUNTIF(OFFSET(工作表1!B2,,,-MIN(ROW(B1),3)),"值班")=3,1,"")
再將公式複製到B2:D32。(預計31天)

點我看大圖

在4次工作表B2輸入公式
=IF(COUNTIF(OFFSET(工作表1!B2,,,-MIN(ROW(B1),4)),"值班")=4,1,"")
再將公式複製到B2:D32。(預計31天)

點我看大圖

在5次工作表B2輸入公式
=IF(COUNTIF(OFFSET(工作表1!B2,,,-MIN(ROW(B1),5)),"值班")=5,1,"")
再將公式複製到B2:D32。(預計31天)

點我看大圖

再在工作表1G2:
=SUM('3次'!B:B)
向右複製公式到H2:I2。
再在工作表1G3:
=SUM('4次'!B:B)
向右複製公式到H3:I3。
再在工作表1G4:
=SUM('5次'!B:B)
向右複製公式到H4:I4。
點我看大圖

錦色如月，子耀光芒。

錦子

在3次(4次、5次)工作表B2輸入公式=IF(COUNTIF(OFFSET(B1,,,-MIN(ROW(B1),2)),1)=0,原公式,"")再將公式複製到B2:D32。(預計31天)

2023-01-27 9:03

柯吉霸樓主

謝謝錦子老師的指導,讓我學了新技巧

2023-01-27 20:14

jaredsam

jaredsam
個人積分：1547分
文章編號：86744874

1547分

6樓

2023-01-26 22:39

樓主這種很難通用
因為一般你系統上看跳出很簡易的
其實是用多表格統計
只是它呈現主表
附表隱藏在系統數據端計算~

我的公式你可以參考我如何避免重複，用類似的邏輯即可
可是若有其他需求要先說~
不太可能通用

不過可能我功力淺薄
錦子老師才是真大師

比如你要7天以上，甚至連8連9
你可以改+判斷式判斷他是連續值即呈現空白
E2
=IF(COUNTIF(A2:A3,"值班")=2,"",IF(COUNTIF(A3:A7,"值班")=5,5,IF(COUNTIF(A3:A6,"值班")=4,4,IF(COUNTIF(A3:A5,"值班")=3,3,""))))

柯吉霸樓主

謝謝大大的指導~雖然很難通用,但對我來初入門來說算是很容易理解的方式,再次感謝你的回覆 ^^

2023-01-27 20:16

柯吉霸樓主

謝謝jaredsam大大~ 想額外再請教你，如果日後有統計連續2次或5次以上的需求時，公式上是否可以再進行升級呢? 我想有空時先研究看看，如果有空再請不吝指教唷~

2023-01-28 19:08

monguly

monguly
個人積分：6678分
文章編號：86745315

6678分

7樓

2023-01-27 0:03

用一般EXCEL，樞紐分析表拉一拉答案就出來了

mk2-paul

mk2-paul
個人積分：345分
文章編號：86746451

345分

8樓

2023-01-27 2:02

E6
=IF(B7<>"值班",IF(B6="值班",COUNTIF(B$6:B6,"值班"),0),0)

H6
=E6-MAX(E$5:E5)

K6
=IF(B7<>"休",IF(B6="休",COUNTIF(B$6:B6,"休"),0),0)

N6
=K6-MAX(K$5:K5)

B1
=COUNTIF(H$6:H$21,MID($A1,2,1))

F1
=COUNTIF(N$6:N$21,MID($E1,2,1))

柯吉霸樓主

真的是太棒了，真沒想到你把我的需求都做到了，感謝大神

2023-01-27 20:17

錦子

錦子
個人積分：2128分
文章編號：86747582

2128分

9樓

2023-01-27 9:55

jaredsam wrote:
樓主這種很難通用因...(恕刪)

謝謝你，讓我又有另外的解法。
E6:
=IF(COUNTIF(B6:B7,"值班")=2,2,"")&IF(COUNTIF(B6:B8,"值班")=3,3,"")&IF(COUNTIF(B6:B9,"值班")=4,4,"")&IF(COUNTIF(B6:B10,"值班")=5,5,"")&IF(COUNTIF(B6:B11,"值班")=6,6,"")
再將公式複製到E6:G32
B1:
=COUNT(FIND(ROW()+2,E6:E36))
再將公式複製到B2:D4

點我看大圖

錦色如月，子耀光芒。

柯吉霸樓主

真的把公式用的超出我的想像, 我會找時間來試著設定看看,謝謝指教

2023-01-27 20:18

錦子

錦子
個人積分：2128分
文章編號：86751320

2128分

10樓

2023-01-27 21:59

E6:
=IF(B6="值班",IF(B7="值班","",IF(COUNTIF(B1:B6,"值班")=6,6,IF(COUNTIF(B4:B6,"值班")=5,5,IF(COUNTIF(B4:B6,"值班")=4,4,IF(COUNTIF(B4:B6,"值班")=3,3,""))))),"")
將公式複製到E6:G36
H6:
=IF(B6="休",IF(B7="休","",IF(COUNTIF(B1:B6,"休")=6,6,IF(COUNTIF(B4:B6,"休")=5,5,IF(COUNTIF(B4:B6,"休")=4,4,IF(COUNTIF(B4:B6,"休")=3,3,""))))),"")
將公式複製到H6:J36
F1:
=COUNTIF(E$6:E$36,ROW()+2)
將公式複製到F1:H3
J1:
=COUNTIF(H$6:H$36,ROW()+2)
將公式複製到J1:L3

點我看大圖

錦色如月，子耀光芒。

柯吉霸樓主

謝謝錦子老師的指導~ 有你的協助我已經完成年度休假統計了,再次感謝~只是想再額外請教，如果日後有統計連續2次、或是5次以上的需求時，是否能以原公式再改寫呢? 感謝指教~

2023-01-28 19:10

錦子

可以。只是再用IF與COUNTIF結合部份四層變成五層(6,5,4,3,2)，實際要感謝的是六樓JAREDSAM，若沒他的解法，我不會想到此解法。

2023-01-28 22:01