按条件统计数组元素个数
countif函数第一个参数必须是单元格引用。废了九牛二虎之力吭哧出一个函数。第一个参数可以是常量数组或动态数组。
function COUNTIFA(array,conditions){
console.clear();
let typeof1 = Object.prototype.toString.call(array);
let typeof2 = Object.prototype.toString.call(conditions);
if (typeof1 === "[object Object]" && typeof2 === "[object Object]" ){
let array_address = array.Address();
let conditions_address = conditions.Address();
return Application.Evaluate(`COUNTIF(${array_address},${conditions_address})`) //可以注释掉 //下面的判断写法有点复杂，慢慢优化吧。
}
console.log("typeof1:"+ typeof1 + "typeof2:" + typeof2);
let array_array = [[]];
let conditions_array = [[]];
if (typeof1 === "[object Object]"){
array_array = array.Value2;
console.log("1a "+JSON.stringify(array_array))
if(!Array.isArray(array_array)){
array_array = [[array_array]];
}else{
if(!Array.isArray(array_array[0])){
array_array = [array_array];
}
}
}else{
if (typeof1 === "[object Array]"){
if(Array.isArray(array[0])){
array_array = array;
}else{
console.log(JSON.stringify(array))
array_array[0]=array;
}
}else{
array_array[0].push(array);
}
}
if ( typeof2 === "[object Object]" ){
conditions_array = conditions.Value2;
console.log("2a "+JSON.stringify(conditions_array))
if(!Array.isArray(conditions_array)){
conditions_array = [[conditions_array]];
}else{
if(!Array.isArray(conditions_array[0])){
conditions_array = [conditions_array];
}
}
}else{
if ( typeof2 === "[object Array]" ){
if(Array.isArray(conditions[0])){
conditions_array = conditions;
}else{
console.log(JSON.stringify(conditions))
conditions_array[0]=conditions;
}
}else{
console.log(5)
conditions_array[0].push(conditions);
}
}
let conditions_rlength = conditions_clength = array_array_rlength = array_array_clength = 0;
if( Array.isArray(conditions_array[0])){
console.log("a");
conditions_rlength = conditions_array.length;
conditions_clength = conditions_array[0].length;
}else{
console.log("b");
conditions_clength = conditions_array.length;
conditions_rlength = 1;
}
if( Array.isArray(array_array[0])){
console.log("c");
array_array_rlength = array_array.length;
array_array_clength = array_array[0].length;
}else{
console.log("d");
array_array_clength = array_array.length;
array_array_rlength = 1;
}
console.log("查找值数组行数:" + conditions_rlength, "列数:" + conditions_clength)
console.log("被找区域数组行数:" + array_array_rlength, "列数:" + array_array_clength);
let result_array = Array(conditions_rlength).fill().map(()=>Array(conditions_clength));
console.log("结果计数数组 初值:"+JSON.stringify(result_array))
typeof1 = Object.prototype.toString.call(array_array);
typeof2 = Object.prototype.toString.call(conditions_array);
console.log("typeof1:"+ typeof1 + "typeof2:" + typeof2);
console.log("conditions_array " + JSON.stringify(conditions_array));
console.log("array_array " + JSON.stringify(array_array));
for(var i=0;i<conditions_rlength;i++){
for(var j=0;j<conditions_clength;j++){
let counter = 0;
for(var m=0;m<array_array_rlength;m++){
console.log("array_array[m]:" + JSON.stringify(array_array[m]));
let findindex = 0;
if(Array.isArray(array_array[m])){
if(Array.isArray(conditions_array[i])){
console.log("i:"+i+",j:"+j+"conditions_array[i][j]:" +conditions_array[i][j]+"WDK" );
while(findindex!=-1){
findindex = array_array[m].indexOf(conditions_array[i][j],findindex)
if(findindex>=0){
findindex++;
counter++;
}
console.log("0-findindex"+findindex);
}
}else{
console.log("i:"+i+",j:"+j+"conditions_array[i]:" +conditions_array[i]+"WDK" );
while(findindex!=-1){
findindex = array_array[m].indexOf(conditions_array[i],findindex);
if(findindex>=0){
findindex++;
counter++;
}
console.log("1-findindex"+findindex);
}
}
}else{
//以下是基本没什么用的代码
if(Array.isArray(conditions_array[i])){
console.log("i:"+i+",j:"+j+"conditions_array[i][j]:" +conditions_array[i][j]+"WDK" );
while(findindex!=-1){
findindex = array_array.indexOf(conditions_array[i][j],findindex)
if(findindex>=0){
findindex++;
counter++;
}
console.log("3-findindex"+findindex);
}
}else{
console.log("i:"+i+",j:"+j+"conditions_array[i]:" +conditions_array[i]+"WDK" );
while(findindex!=-1){
findindex = array_array.indexOf(conditions_array[i],findindex);
if(findindex>=0){
findindex++;
counter++;
}
console.log("4-findindex"+findindex);
}
}
}
}
console.log("counter:" +counter)
result_array[i][j]=counter;
}
}
return result_array;
}
