列中數值為1的佔整列的百分比,請注意第一行為列名,並且行數不固定,每一列都要統計,這個要怎麼做。最後用的這個公式運算的 =COUNTIF(G3:G1000,"1")/COUNTA(G3:G1000),請問如何把它做成一個宏按鈕,一按全算出來了,並且列數和行數還不定,要求統計出來的結果顯示在每一列的最後一行
要統計的列中資料只會是1或0,統計出來的結果,就像最後一張圖即可
回覆列表
-
1 # wangsir1121
-
2 # 精進Excel
來先看一下最終實現的效果:
如圖所示,在最後一行顯示了列中數值為1的佔整列的百分比,當我們新追加一行時,只要填入每一列的得分值(0或者1),百分比就會自動重新計算。
這樣無論你新增或刪減多少行資料,都不需要重新修改公式,依然能夠計算出來最新的佔比情況。
如何實現這樣的功能,答案非常簡單,就是利用智慧表格!
①按Ctrl+T將資料區域轉化為智慧表格區域
②調出彙總行並寫入公式
生成智慧表格之後,表格的式樣會發生變化,切換到【設計】選項卡,勾選【彙總行】
這時候,在智慧表格的最後一行,會出現【彙總】行,彙總行中會自動生成下拉選單,在選單中可以選擇常用的函式。
=COUNTIF([第一題],1)/COUNTA([第一題])
(注意:轉化成智慧表格後,函式的引用會變成格式化引用,所以上面的公式中[第一題]指的就是標題“第一題”下面的資料列,我們仍然是選擇資料列即可,公式會自動變成結構化引用)
完成之後,每當我們新增或者刪減行時,公式依然保持結構化引用,永遠計算的都是最新的結果。
模擬資料如圖
VBA程式碼如下:
Sub Collect()
Dim arr, brr, r&, c&, i&, j&, s&
r = Cells(Rows.Count, 1).End(xlUp).Row
c = Cells(1, Columns.Count).End(xlToLeft).Column
Rows(r + 1).ClearContents
arr = Range(Cells(2, 1), Cells(r, c))
ReDim brr(1 To c)
For j = 6 To UBound(arr, 2)
For i = 1 To UBound(arr)
s = s + val(arr(i, j)) "避免文字資料導致資料型別不匹配
Next
brr(j) = Format(s / (r - 1), "0.00%")
s = 0
Next
Cells(r + 1, 1).Resize(, c) = brr
End Sub