回答網友提問:統計區域內連續爲 1 的次數,連續 3 次算一次達標,計算出總達標次數。
案例:按以下規則計算出下圖 1 中每個人業績達標次數:
每連續 3 次符合要求計 1 次達標;
如果連續不滿 3 次,則作廢,從重新開始計數;
如果連續次數超過 3,則每 3 次算 1 次達標,例如,連續 6 次算 2 次達標,連續 9 次算 3 次達標,依此類推
效果如下圖 2 所示。
1. 將 D 列設置爲輔助列 a,在 D2 單元格中輸入以下公式 --> 下拉複制公式:
=IF(C1=1,(D1+1)*C2,C2)
公式釋義:
如果上方單元格 C1=1,那麽將 (D1+1) 後與 C2 相乘;
當 C2 是 1 時,公式開始累計求和,達到了計算 1 累計出現次數的效果;
如果 C2 是 0,需要返回 0 值,此時 *C2 就正好發揮了功效;
如果上方單元格不是 1,則重新計數,于是就等于 C2 單元格的值 1 或 0
2. 將 E 列設置爲輔助列 b,在 E2 單元格中輸入以下公式 --> 下拉複制公式:
=QUOTIENT(IF(C3=0,D2,0),3)
公式釋義:
IF(C3=0,D2,0):如果下方的單元格 C3=0,說明 C2 是連續爲 1 的最後一個單元格,那麽取 D2 的值;否則,一概不需要取值,都爲 0;
QUOTIENT(...,3):
QUOTIENT 函數的作用是返回除法的商的整數部分;
語法爲 QUOTIENT(除數,被除數)
此處的公式就是將 if 的結果除以 3,得出 3 的整數倍
3. 選中數據表的任意單元格 --> 選擇工具欄的“插入”-->“數據透視表”
4. 在彈出的對話框中選擇“現有工作表”及所需上傳至的位置 --> 點擊“確定”
5. 在右側的“數據透視表字段”區域按以下方式拖動字段:
行:“姓名”
值:b
6. 雙擊值列的標題 --> 在彈出的對話框中將名稱修改爲“達標次數”--> 點擊“確定”
這樣就成功計算出了每個人的達標次數。