公式忽略隐藏的列

问题描述：

我试图计算电子表格中的可见列，但没有运气。我一直在尝试使用 SUBTOTAL 函数，但该函数仅适用于隐藏/可见行。我还尝试过使用 CELL（ width）函数，但是当单元格被隐藏时它不会返回0

I am trying to count visible columns in a spreadsheet with no luck. I've been trying using SUBTOTAL function but it's applied to hidden/visible rows only. I also tried working with CELL("width") function, but it doesn't return 0 when a cell is hidden

还有其他选项可以忽略计数公式中的隐藏列吗？

Is there any other option to ignore hidden columns in a count formula?

答

您绝对可以使用Google Apps创建自己的自定义函数脚本。

You can definitely create your own custom function using Google Apps Script.

例如，以下函数计算活动工作表中可见的列数：

For example, the following function counts the number of visible columns in your active sheet:

function countVisibleColumns() {
  
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var n_cols = sheet.getMaxColumns();
var hidden_cols = []
var cnt = 0;
  
for (var i=1; i<=n_cols ; i++) {
  if ( sheet.isColumnHiddenByUser(i) ){
  continue;}
  else {cnt +=1} }
Logger.log(cnt)
return cnt;
}

您只需点击 Tools =>脚本编辑器，然后将上述代码复制到空白脚本中。然后，您可以将函数直接用作Google工作表中的公式，例如 = countVisibleColumns（）。
有关更多信息，请参见所附屏幕截图。

You just need to click on Tools => Script editor and then copy the aforementioned code into a blank script. Then you can directly use the function as a formula in the google sheet like =countVisibleColumns(). See screenshot attached for more information.

相关推荐