Google Sheets: INDIRECT() with a Range

问题:

This question could be rephrased to, "Using a programmatically generated range in FILTER()" depending on the approach one takes to answer it.

EDIT- It seems that I inadvertently included too much information in my attempts to demonstrate what I've tried so that my question was unclear. The changes I made in this edit should remedy that.

I am currently filtering using the following function:

Code Block 1

=filter('Data Import'!1:10000,'Data Import'!D:D<12)

After importing data, Column D:D can change positions (eg, it could be in column F:F), but always has the header "student.grade".

The question is: How can I reference this variable-position column with a fixed header in a filter function as in the one given in code block 1? In other words, can I replace 'Data Import'!D:D` with valid code that will allow this function to work regardless of the location of the column with header "student.grade?"

What I've tried:

I can use the following code to correctly find the address of the column (whatever it happens to be after data import) as a string:

Code Block 2

=substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")&":"&substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")

The function in code block 2 above returns "D:D" when the header "student.grade" is in cell D1, and "F:F" when "student.grade" is in cell F1. I thought I could simply plug this value into a FILTER() function and be on my merry way, but in order to convert my string to a usable address, I attempted to use an INDIRECT() function on the string produced in code block 2 above.

Code Block 3

=filter('Data Import'!1:3351,'Data Import'!indirect(substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,"")&":"&substitute(address(1,match("student.grade",'Data Import'!1:1,0),4),1,""),TRUE)<12)

The formula won't parse correctly.

Simplifying the indirect portion of the same function to test whether or not it will work when given a range produces the same error:

Code Block 4

=filter('Data Import'!1:3351,indirect('Data Import'!&"D:D")<12)

This leads me to believe INDIRECT() doesn't handle ranges, or if it does, I don't know the syntax. This Stack Overflow post seems to suggest this is possible, but I can't work out the details.

This question is NOT an attempt to get others to help me solve my programming dilemma. I can do that with various scripts, giant columns of secondary if statements, and more.

This question is asked for the sake of understanding how to pass a variable range into a filter function (if it's possible).

回答1:

once again, maybe this is what you want:

=FILTER('Data Import'!1:100000, 
 INDIRECT("'Data Import'!"&
 ADDRESS(1,       MATCH("student.grade", 'Data Import'!1:1, 0), 4)&":"&
 ADDRESS(1000000, MATCH("student.grade", 'Data Import'!1:1, 0), 4)) < 12)

回答2:

The OP's existing solution is based on Filter command. The challenge is that the column containing "student.grade" is not fixed, however player0 has provided an excellent formula-based solution.

An alternative might be to make use of a named range. The following code finds "student.grades" in the header (row 1) and re-defines the named range accordingly.

function so54541923() {

  // setup the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Data Import";
  var sheet = ss.getSheetByName(sheetname);

  // define the header row
  var getlastColumn = sheet.getLastColumn();
  var headerRange = sheet.getRange(1, 1, 1, getlastColumn);
  Logger.log("DEBUG: Header range = " + headerRange.getA1Notation()); //DEBUG

  // assign a variable for student Grades
  var grades = "student.grade";

  // get the headers and find the column containing "student grades"
  var headerData = headerRange.getValues();
  var gradesIndex = headerData[0].indexOf(grades);
  // add one to the index number to account for start=zero
  gradesIndex = gradesIndex + 1;
  Logger.log("DEBUG: gradesIndex = " + gradesIndex); //DEBUG

  // convert the column number to a letter 
  // assumes that student.grade will never get past column Z
  var temp, letter = '';
  if (gradesIndex > 0) {
    temp = (gradesIndex - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    gradesIndex = (gradesIndex - temp - 1) / 26;
  }
  Logger.log("DEBUG: the column is " + letter); //DEBUG

  //var newrange = "'" + sheetname + "'!"+letter+":"+letter+";";
  // Logger.log("the new range is "+newrange); 

  // get the named ranges
  var namedRanges = ss.getNamedRanges();
  Logger.log("DEBUG: number of ranges: " + namedRanges.length); //DEBUG

  // if named range is student grades, then update range
  if (namedRanges.length > 0) {
    for (var i = 0; i < namedRanges.length; i++) {
      var thename = namedRanges[i].getName();
      Logger.log("DEBUG: Loop: i: " + i + ", and the named range is " + thename); //DEBUG
      if (thename = "student.grade") {

        // Logger.log("DEBUG: The named range is student.grade");//DEBUG

        // set the new range based on the column found earlier
        var nonstringrange = sheet.getRange("'" + sheetname + "'!" + letter + ":" + letter);
        namedRanges[i].setRange(nonstringrange);
        Logger.log("DEBUG: The new range is " + namedRanges[i].getRange().getA1Notation()); //DEBUG

      } else {
        Logger.log("DEBUG: The named range is NOT grades"); //DEBUG
      }
    }
  }
}

回答3:

I have no idea what you want to achieve but take a look at this:

={'Data Import'!1:1;
 FILTER('Data Import'!1:10000, 'Data Import'!D:D < 12)}

or:

=QUERY(FILTER('Data Import'!1:10000, 'Data Import'!D:D < 12), 
 "select * label Col4 'student.grade'", 0)