使用Google电子表格统计多行和多列的重复单元格 [英] Counting duplicate cells on multiple rows and columns with Google Spreadsheet
本文介绍了使用Google电子表格统计多行和多列的重复单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
因此,对于Google Apps Script,我有一个电子表格,如下所示:
AB C ...
apple香蕉橙...
香蕉橙葡萄...
香蕉苹果橙...
... ... ...
...我想这样做:
apple 2
banana 3
grape 1
orange 3
在这种情况下,最简单的方法是什么?
我试图在Google Apps Spreadsheet上使用查询功能,但由于存在多列,因此很难排序和计数。
需要帮助!
$ b 需要帮助!
<
function myFunction() {
var objList = {};
var ss = SpreadsheetApp.getActive()。getActiveSheet();
var data = ss.getDataRange()。getValues();
for(var i in data){
for(var j in data [i]){
if(typeof objList [data [i] [j]] ==undefined) {
objList [data [i] [j]] = 1;
}
else {
objList [data [i] [j]] + = 1;
}
}
}
var objTable = [];
for(var k in objList){
objTable.push([k,objList [k]]);
}
Logger.log(objTable);
ss.clear();
ss.getRange(1,1,objTable.length,objTable [0] .length).setValues(objTable);
}
So with Google Apps Script, I have a spreadsheet as following:
A B C...
apple banana orange...
banana orange grape...
banana apple orange...
... ... ...
...and I want to make it like this:
apple 2
banana 3
grape 1
orange 3
What would be the easiest way to make this possible?
I have tried to use query function on Google Apps Spreadsheet, but since there are multiple columns it is difficult to sort and count. I have also tried to use join and countif function but it tend to get redundant since there are too many columns.
Need help!
解决方案
this should do the work:
function myFunction() {
var objList={};
var ss = SpreadsheetApp.getActive().getActiveSheet();
var data = ss.getDataRange().getValues();
for(var i in data){
for(var j in data[i]){
if(typeof objList[data[i][j]]=="undefined"){
objList[data[i][j]]=1;
}
else{
objList[data[i][j]]+=1;
}
}
}
var objTable=[];
for(var k in objList){
objTable.push([k,objList[k]]);
}
Logger.log(objTable);
ss.clear();
ss.getRange(1, 1, objTable.length, objTable[0].length).setValues(objTable);
}
这篇关于使用Google电子表格统计多行和多列的重复单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文