Obliczanie zacienionych komórek


10

Korzystam z Arkuszy kalkulacyjnych Google do tworzenia macierzy (czas / aktywność) jako codziennego harmonogramu logowania pracowników.

Czy istnieje formuła do obliczania liczby komórek w kolumnie, które są cieniowane (lub nie cieniowane) określonego koloru?

Na przykład: 20 komórek jest szare, a 31 nie jest szare (zacieniowany na inny kolor) i zamiast ręcznie je zliczać, oblicza to na dole kolumny zasięgu.


Problem z rozwiązaniem @ w0lf pochodzi z pamięci podręcznej. Ta funkcja nie jest gotowa do pobierania aktualizacji na żywo. Próbowałem zdobyć tę zdolność, dodając „now ()” jako dodatkowy parametr, ale to nie działało.
ciepło

Nie mogę odpowiedzieć, więc zostawię komentarz. Jeśli komórki nie zawierają żadnego tekstu, łatwiej jest dodać dowolny tekst w tym samym kolorze co tło (więc jest ukryty), a następnie wystarczy wykonać proste = counta (), aby policzyć niepuste komórki. Jeśli oczywiście jest to bardzo specyficzne w zależności od przypadku.
sfratini

Odpowiedzi:


17

Znalazłem sposób, aby to zrobić, korzystając z funkcji skryptowej udostępnianej przez Google Spreadsheet.

Oto jak to zrobić:

  • otwórz swój arkusz kalkulacyjny
  • w menu przejdź do Narzędzia -> Edytor skryptów ...; otworzy się nowe okno, w którym możesz wpisać kod
  • skopiuj poniższy kod
  • wklej kod w oknie edytora skryptów i naciśnij CTRL+, Saby go zapisać
  • zamknij okno edytora skryptów i wróć do arkusza kalkulacyjnego

Kod:

function getBackgroundColor(rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange(rangeSpecification);
  return cell.getBackground();
}

function sumWhereBackgroundColorIs(color, rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackground() == color)
        x += parseFloat(cell.getValue());
    }
  }
  return x;
}

function countCellsWithBackgroundColor(color, rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackground() == color)
        x++;
    }
  }
  return x;
}

Po wykonaniu powyższych kroków w arkuszu kalkulacyjnym dostępne są jeszcze trzy funkcje:

  • countCellsWithBackgroundColor(<color>, <range specification>)
  • sumWhereBackgroundColorIs(<color>, <range specification>)
  • getBackgroundColor(<cell specification>)

Należy pamiętać, że <range specification>i <cell specification>są wyrażone w notacji A1 i muszą być ujęte w cudzysłów .

Na przykład, aby uzyskać liczbę wszystkich komórek z zakresu B2: F13, dla których kolor tła jest ustawiony na biały , należy wprowadzić następującą formułę:

=countCellsWithBackgroundColor("white", "B2:F13")

i aby obliczyć sumę tych samych komórek, użyj wzoru:

=sumWhereBackgroundColorIs("white", "B2:F13")

Niektóre komórki mogą nie mieć tła w kolorze takim jak „biały”, „szary”, ale podobny do koloru RGB #6fa8dc. Nie możesz zgadnąć, jaki jest kolor, więc jeśli chcesz znaleźć kolor komórki (na przykład B9), wpisz tę formułę w komórce:

=getBackgroundColor("B9")

a następnie użyj tej wartości jako parametru dla dwóch powyższych funkcji.

Wygląda na to, że jeśli kolor tła komórki to „kolor motywu”, getBackground()zawsze niepoprawnie zwraca #ffffff. Nie znalazłem innego rozwiązania niż użycie koloru ze standardowego zestawu.

Ponownie zwróć uwagę na cytaty we wszystkich powyższych przykładach; bez nich formuły nie działałyby.


Próbowałem zastosować się do twoich wskazówek, ale w tej dziedzinie używam formuły, którą otrzymuję #LICZBA !, czy wiesz, dlaczego tak jest? Próbowałem przeprowadzić badania, ale nie mogłem znaleźć na to dokładnej odpowiedzi. Dzięki milion, dami

@Dami możesz opublikować tutaj swoją formułę? Sprawdź także pisownię - Myślę, że w nazwach formuł rozróżniana jest wielkość liter
Cristian Lupascu,


getBackgroundColor()Jest przestarzała. Być może możesz zaktualizować kod?
Jacob Jan Tuinstra

Kiedy zmieniam kolor tła na inną, suma nie jest aktualizowana. Czy istnieje sposób automatycznej aktualizacji sumy po wprowadzeniu pewnych zmian?
Michal Kováč,

0

W Dokumentach Google nie znalazłem sposobu bezpośredniego zliczania komórek o określonym kolorze.

Co sprawia, że ​​są zacienione. Czy to jest automatyczne? Jeśli mają wartość, są automatycznie cieniowane? W takim przypadku możesz użyć funkcji counta () do zliczenia dowolnej komórki z wartością. Jeśli w grę wchodzi bardziej złożony zestaw reguł, może być konieczne użycie funkcji countif ().

Uwaga: może być konieczne uwzględnienie dodatkowych wierszy u góry lub u dołu kolumny

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.