Δημιουργήστε ένα heat map δεδομένων σε υπολογιστικά φύλλα χρησιμοποιώντας μια μακροεντολή ONLYOFFICE

Στην ανάλυση δεδομένων, η αναγνώριση των διαφορών στις αριθμητικές τιμές με μια ματιά είναι κρίσιμη για τη λήψη αποφάσεων. Η μη αυτόματη μορφοποίηση κάθε κελιού μπορεί να είναι χρονοβόρα και επιρρεπής σε λάθη. Αυτό το μακρο ONLYOFFICE λύνει αυτό το πρόβλημα προσαρμόζοντας δυναμικά τα χρώματα των κελιών, διευκολύνοντας την ερμηνεία πολύπλοκων δεδομένων με αποτελεσματικό τρόπο.

Κατασκευή του μακρο

var sheet = Api.GetActiveSheet();
var range = sheet.GetSelection();
var data = range.GetValue();

Ξεκινώντας, αρχικοποιούμε το ενεργό φύλλο, την επιλογή και την περιοχή στις μεταβλητές sheet, range και data, αντίστοιχα.

Χειρισμός σφάλματος “No data selected”

Αμέσως μετά, ελέγχουμε αν έχουν επιλεγεί πραγματικά δεδομένα. Αν δεν έχουν επιλεγεί δεδομένα, τερματίζουμε τη λειτουργία της μάκροεντολής με ένα κατάλληλο μήνυμα: “No data selected.”

//Ελέγχουμε αν δεν έχουν επιλεγεί δεδομένα και εμφανίζουμε μήνυμα σε αυτή την περίπτωση
  if (!data) {
    console.log("Δεν έχουν επιλεγεί δεδομένα");
    return;
  }

Εξαγωγή δεικτών γραμμών και στηλών από μια επιλογή

Αν υπάρχουν δεδομένα, προχωράμε ανακτώντας τις παραμέτρους τους – δείκτες αρχής στήλης, τέλους στήλης, αρχής γραμμής και τέλους γραμμής.

//Δείκτες που υποδεικνύουν πού ξεκινούν και τελειώνουν οι γραμμές και οι στήλες
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;

Υπάρχουν πολλοί τρόποι με τους οποίους μπορούμε να λάβουμε τους δείκτες, αλλά ένας από αυτούς φαίνεται στο παραπάνω απόσπασμα κώδικα:

• range.GetCells() – προσπελαύνουμε τα κελιά της επιλογής μας και προσαρτώντας .Row() ή .Col(), λαμβάνουμε τον δείκτη της πρώτης γραμμής και της πρώτης στήλης.
• Μόλις το έχουμε αυτό, μπορούμε να χρησιμοποιήσουμε data.length, που θα μας δώσει τον αριθμό των γραμμών στα δεδομένα μας. Στη συνέχεια, προσθέτουμε αυτό στη μεταβλητή firstRowIndex για να λάβουμε τον δείκτη της τελευταίας γραμμής. Κάνουμε το ίδιο για τις στήλες, χρησιμοποιώντας data[0].length για να λάβουμε τον αριθμό των στηλών και στη συνέχεια προσθέτοντας τη μεταβλητή firstColIndex.

Γιατί data[0];

Εφόσον δεν γνωρίζουμε πόσες γραμμές έχουμε και καθένα από τα data[0], data[1], κ.λπ. αντιπροσωπεύει μια γραμμή, το μόνο που μπορούμε να είμαστε σίγουροι είναι ότι τα δεδομένα μας θα έχουν πάντα τουλάχιστον μία γραμμή. Αυτό σημαίνει ότι το data[0] θα είναι πάντα έγκυρο εάν η μεταβλητή data περιέχει οποιεσδήποτε τιμές.

Συλλογή αριθμών από επιλεγμένα κελιά υπολογιστικού φύλλου

Στη συνέχεια, δημιουργούμε τον πίνακα values και τον γεμίζουμε με αριθμούς από την επιλογή μας.

var values = []; //Θα αποθηκεύσουμε αριθμούς από τα επιλεγμένα δεδομένα εδώ


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Ελέγχουμε αν η τιμή είναι αριθμός
      //Αν είναι, την αποθηκεύουμε στον πίνακα values
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

Εύρεση της ελάχιστης και μέγιστης τιμής

//Αποθήκευση ελάχιστων και μέγιστων τιμών από τον πίνακα values
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);

Εφαρμογή χρωμάτων

Τώρα που έχουμε όλες τις απαραίτητες πληροφορίες, μπορούμε να εφαρμόσουμε προσαρμοσμένα χρώματα στα κελιά που περιέχουν αριθμούς.

Έχουμε τόσο τον ελάχιστο όσο και τον μέγιστο αριθμό από τα επιλεγμένα κελιά, οπότε μπορούμε να επαναλάβουμε τα κελιά για άλλη μια φορά.

for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Ελέγχουμε ξανά αν η τιμή είναι αριθμός
      //Αν είναι, δημιουργούμε το χρώμα ανάλογα με αυτή την τιμή
      //Καθώς και την ελάχιστη και μέγιστη τιμή από τον πίνακα
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        //Θέλουμε τα χρώματα να πηγαίνουν από πράσινο σε κόκκινο
      }
    }
  }

Όταν δημιουργούμε ένα χρώμα χρησιμοποιώντας το σύστημα RGB, χρειαζόμαστε τις παραμέτρους κόκκινο, πράσινο και μπλε για να δημιουργήσουμε οποιοδήποτε επιθυμητό χρώμα. Σε αυτή την περίπτωση, το χρώμα πρέπει να κυμαίνεται από πράσινο σε κόκκινο, που σημαίνει ότι η παράμετρος μπλε παραμένει στο 0 ενώ οι παράμετροι κόκκινο και πράσινο ποικίλλουν ανάλογα με την επιθυμητή απόχρωση.

Εάν ο αριθμός είναι μικρός – πιο κοντά στην ελάχιστη τιμή – το χρώμα θα είναι πιο πράσινο (η ελάχιστη τιμή αντιστοιχεί σε καθαρό πράσινο). Αντίθετα, εάν ο αριθμός είναι μεγάλος, το χρώμα θα τείνει προς το κόκκινο (η μέγιστη τιμή αντιστοιχεί σε καθαρό κόκκινο).

Για να προσδιορίσουμε πόσο μεγάλος ή μικρός είναι ο αριθμός, χρησιμοποιούμε τη μέθοδο Κανονικοποίησης Min-Max για να λάβουμε ένα “λόγο” μεταξύ 0 και 1. Ένας μεγαλύτερος αριθμός έχει ως αποτέλεσμα ένα λόγο πιο κοντά στο 1, ενώ ένας μικρότερος αριθμός δίνει ένα λόγο πιο κοντά στο 0. Αυτός ο λόγος υπολογίζεται χρησιμοποιώντας τις μεταβλητές minValue και maxValue.

Μπορούμε στη συνέχεια να χρησιμοποιήσουμε αυτόν τον λόγο για να προσδιορίσουμε τις παραμέτρους κόκκινο και πράσινο. Σε αντίθεση με τον λόγο, που κυμαίνεται από 0 έως 1, τόσο οι τιμές κόκκινου όσο και του πράσινου κλιμακώνονται από 0 έως 255, οπότε πολλαπλασιάζουμε τον λόγο ανάλογα.

Εφόσον το κόκκινο πρέπει να είναι πιο κυρίαρχο για μεγαλύτερους αριθμούς, το υπολογίζουμε πολλαπλασιάζοντας τον λόγο επί 225 και στρογγυλοποιώντας στον πλησιέστερο ακέραιο αριθμό.

Για το πράσινο, ο υπολογισμός είναι διαφορετικός. Το πράσινο πρέπει να είναι πιο κυρίαρχο για μικρότερους αριθμούς, οπότε χρησιμοποιούμε 1−ratio, πολλαπλασιάζοντάς το επί 225. Όταν ο αριθμός είναι μεγαλύτερος, ο λόγος πλησιάζει το 1, κάνοντας το 1−ratio μικρότερο, που με τη σειρά του μειώνει την τιμή του πράσινου.

Μόλις λάβουμε τις παραμέτρους κόκκινο και πράσινο, χρησιμοποιούμε Api.CreateColorFromRGB(r, g, b) για να δημιουργήσουμε το χρώμα και .SetFillColor(color) για να το εφαρμόσουμε στο κελί.

Ολόκληρος ο κώδικας του μακρο

(function () {
  var sheet = Api.GetActiveSheet();
  var range = sheet.GetSelection();
  var data = range.GetValue();


  //Ελέγχουμε αν δεν έχουν επιλεγεί δεδομένα και εμφανίζουμε μήνυμα σε αυτή την περίπτωση
  if (!data) {
    console.log("Δεν έχουν επιλεγεί δεδομένα");
    return;
  }


  //Δείκτες που υποδεικνύουν πού ξεκινούν και τελειώνουν οι γραμμές και οι στήλες
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;


  console.log(firstColIndex + " " + lastColIndex); //Δοκιμή αν λάβαμε τους σωστούς δείκτες στηλών
  console.log(firstRowIndex + " " + lastRowIndex); //Δοκιμή αν λάβαμε τους σωστούς δείκτες γραμμών


  var values = []; //Θα αποθηκεύσουμε αριθμούς από τα επιλεγμένα δεδομένα εδώ


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Ελέγχουμε αν η τιμή είναι αριθμός
      //Αν είναι, την αποθηκεύουμε στον πίνακα values
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }


  //Αποθήκευση ελάχιστων και μέγιστων τιμών από τον πίνακα values
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Ελέγχουμε ξανά αν η τιμή είναι αριθμός
      //Αν είναι, δημιουργούμε το χρώμα ανάλογα με αυτή την τιμή
      //Καθώς και την ελάχιστη και μέγιστη τιμή από τον πίνακα
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        //Θέλουμε τα χρώματα να πηγαίνουν από πράσινο σε κόκκινο
      }
    }
  }
})();

Ας εκτελέσουμε τη μακροεντολή και ας δούμε πώς λειτουργεί!

Πριν εκτελέσουμε τη μακροεντολή:

Μετά την εκτέλεση της μακροεντολής:

Τώρα μπορείτε εύκολα να απεικονίσετε την κατανομή των δεδομένων στο υπολογιστικό σας φύλλο, κάνοντας την ανάλυση πιο διαισθητική. Είτε εργάζεστε στην έκδοση για επιτραπέζιους υπολογιστές είτε στην ιστοσελίδα του ONLYOFFICE, αυτό το μακρο ενσωματώνεται απρόσκοπτα στη ροή εργασίας σας.

Μη χάσετε την ευκαιρία να εξερευνήσετε το πλήρες δυναμικό του ONLYOFFICE API. Με μια ευρεία γκάμα μεθόδων, μπορείτε να δώσετε ζωή στις ιδέες σας για αυτοματοποίηση. Αν έχετε οποιεσδήποτε ερωτήσεις ή καινοτόμες ιδέες, μη διστάσετε να τις μοιραστείτε μαζί μας. Εκτιμούμε τη γνώμη σας και ανυπομονούμε να συνεργαστούμε μαζί σας. Καλή τύχη στις εξερευνήσεις σας!

Σχετικά με τον συγγραφέα