Κλείδωμα Γραμμών-Κελιών; στο EXCEL

[profile]

Παιδιά δεν ξέρω αν μπορεί να γίνει αυτό. Δεν ξέρω πολλά από ecxel.

Όπως βλέπεται στον πίνακα ecxel έχουμε:

1. Ημέρες ΔΕ, ΤΡ, ΤΕ, ΠΕ, ΠΑ, ΣΑ, ΚΥ
2. Άνθρωποι A,B,C,D,E,F,G,H
3. Σύνολα Ανθρώπων(σταθερά) A+E,  B+F,  C+G,  D+H

Οι Άνθρωποι έχουν κάθε ημέρα ένα αποτέλεσμα
π.χ Άνθρωπος (Α) ΔΕ-10 ,  Άνθρωπος (Ε) ΔΕ-22 ...κτλ

Στη συνέχεια προστίθενται τα Σύνολα Ανθρώπων με βάση τα ημερήσια αποτελέσματα
με την συνάρτηση =SUM   
π.χ Σύνολο Ανθρώπων (Α+Ε) ΔΕ-32 Σύνολο Ανθρώπων (Β+F) ΔΕ-20  κ.ο.κ

Τα εβδομαδιαία αποτελέσματα των Ανθρώπων προστίθενται και στην στήλη (Σ)
με την συνάρτηση =SUM
π.χ Άνθρωπος (Α) ΔΕ-10 , ΤΡ-18, ΤΕ-6,  Σύνολο 34 

Τα εβδομαδιαία αποτελέσματα των Συνόλων Ανθρώπων προστίθενται στην στήλη (Σ)
με την συνάρτηση =SUM
π.χ Σύνολο Ανθρώπων (Α+Ε) ΔΕ-32 , ΤΡ-30, ΤΕ-12,  Σύνολο 74

Ως εδώ καλά (αν είμαι κατανοητός).

Επειδή θέλω να κάνω και ταξινόμηση με βάση τα σύνολα στη στήλη (Σ) από το
μεγαλύτερο στο μικρότερο και για τους Ανθρώπους και για τα Σύνολα Ανθρώπων
το πράγμα όπως είναι φυσικό χαλάει.

Με την ταξινόμηση τα κελιά με τις προσθέσεις =SUM αλλάζουν θέση οπότε
χαλάει όλος ο πίνακας.

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

π.χ 
Δηλαδή, η γραμμή 10 του Ανθρώπου(Α) και η γραμμή 16 του Ανθρώπου(Ε) να είναι
κλειδωμένες με την γραμμή 4 του Συνόλου Ανθρώπων(Α+Ε) όπου και αν μετακινηθούν
μετά την ταξινόμηση στη στήλη (Σ) και για τα δυο, οπότε να μην χαλάνε τα αθροίσματα.

Παρατηρώ οτι αν κάνω χειροκίνητα την ταξινόμηση από το μεγαλύτερο στο μικρότερο με (αποκοπή-επικόλληση) ολόκληρες τις γραμμές δεν χαλάει ο πίνακας.

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

Επεξ/σία 6 Απριλίου 2021 από profile

[XPHSTOS_]

Αν θυμάμαι καλά με το $ μπορείς να κλειδώσεις το reference σε μια στήλη ή μια γραμμή.
Δηλαδή αν σε μια συνάρτηση έχεις το κελί Α5 και το γράψεις Α$5 τότε η 5η γραμμή θα είναι κλειδωμένη.
Αν το γράψεις $Α5 τότε η στήλη Α θα είναι κλειδωμένη και τέλος αν το γράψεις $Α$5 είναι κλειδωμένο το συγκεκριμένο κελί.
Κλειδωμένο = σταθερό στην συνάρτηση, όταν κάνεις drag προς τα κάτω δηλαδή για να γεμίσεις τα υπόλοιπα κελιά οι τιμές με το $ θα μένουν σταθερές

[profile]

Καταρχάς σε ευχαριστώ.

Κάνω όλα τα κελιά στον επάνω πίνακα (Σύνολα Ανθρώπων)  σε μορφή    πχ το κελί F4  που είναι η Δευτέρα για το Α+Ε     =SUM(F$10+F$16) .

Ωραία αυτό δούλεψε όταν έκανα ταξινόμηση στη στήλη (Σ) για τα Σύνολα Ανθρώπων  και ήρθε το Σύνολο B+F(78)  πάνω από το σύνολο  A+E(74)

παρέμειναν τα αθροίσματα σωστά.

Όταν κάνω όμως  ταξινόμηση στη στήλη (Σ) για το Άνθρωποι, όπου έρχεται  το σύνολο του Β(43)  πάνω από το σύνολο του Α(34)  χαλάνε τα αθροίσματα επάνω.

Ότι και να δοκίμασα με το $, και στα επιμέρους κελιά αλλά και στη στήλη (Σ) δεν κατάφερα τίποτα. 

Έχεις κάποια πρόταση.

[MastroGiannis]

Αντί για την απλή SUM(), δοκίμασε τη SUMIF() εισάγοντας τον τύπο =SUMIF($B$10:$B$20;$B4;F$10:F$20) στο κελί F4 και συμπλήρωσε δεξιά και κάτω (μέχρι το κελί  L8 ή ακόμα και το M8). Έτσι, τα αθροίσματα ακολουθούν το "ID" της κάθε εγγραφής.

[profile]

5 ώρες πριν, MastroGiannis είπε

Αντί για την απλή SUM(), δοκίμασε τη SUMIF() εισάγοντας τον τύπο =SUMIF($B$10:$B$20;$B4;F$10:F$20) στο κελί F4 και συμπλήρωσε δεξιά και κάτω (μέχρι το κελί  L8 ή ακόμα και το M8). Έτσι, τα αθροίσματα ακολουθούν το "ID" της κάθε εγγραφής.

Με την =SUMIF($B$10:$B$20;$B4;F$10:F$20) στο F4 και μέχρι το κελί  M8 έχω αυτό το αποτέλεσμα, που είναι σωστό

 

Όταν πάω να κάνω ταξινόμηση στους ABCD που γίνονται BADC (μεγάλο --> μικρό) παίρνω αυτό το αποτέλεσμα επάνω που είναι λάθος

 

Εάν ταξινομήσω τις γραμμές 10,11,12,13 με αποκοπή-επικόλληση ,  μόνο στην στην περίπτωση του @XPHSTOS_ δουλεύει.

 

 

[MastroGiannis]

1 ώρα πριν, profile είπε

Όταν πάω να κάνω ταξινόμηση στους ABCD που γίνονται BADC (μεγάλο --> μικρό) παίρνω αυτό το αποτέλεσμα επάνω που είναι λάθος

Sorry, θεώρησα ότι ταξινομείται μαζί και η στήλη Β.

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

Με βάση αυτή, στο κελί F4 εισάγεις τον τύπο =SUMIF($D$10:$D$20;LEFT($D4);F$10:F$20)+SUMIF($D$10:$D$20;RIGHT($D4);F$10:F$20)

και συμπληρώνεις δεξιά και κάτω.

Μεγαλύτερη όμως ευελιξία θα έχεις αν αντιγράψεις τις τιμές της στήλης Β στη στήλη E (αν είναι κενή), όπως έχουν πριν την ταξινόμηση, ώστε να ταξινομούνται και αυτές μαζί με τα δεδομένα. Μπορείς αν θέλεις να τις κάνεις αόρατες με διάφορους τρόπους.

Έτσι, ο αρχικός τύπος στο F4 γίνεται: =SUMIF($E$10:$E$20;$E4;F$10:F$20)

Αυτή είναι και η λύση που συνιστώ.

[profile]

32 λεπτά πριν, MastroGiannis είπε

Μια ακόμα λύση είναι η χρήση.....

Φίλε μου σε υπερευχαριστώ.  

Δουλεύουν και οι δυο λύσεις που μου έδωσες.  Στη δεύτερη όμως όταν κάνω ταξινόμηση μου βγάζει σφάλματα τα οποία φυσικά δεν επηρεάζουν το αποτέλεσμα.

Δεν με απασχολεί θα χρησιμοποιήσω την πρώτη.

 

Να σε παιδέψω λίγο ακόμα;

Το φύλλο μου στην τελική του μορφή ενδέχεται να έχει πάνω από 100 γραμμές(Άνθρωποι). 

Κάποιοι επειδή δεν θα ανήκουν σε (σύνολα Ανθρώπων)  θα θέλω να εξαιρεθούν από το επάνω άθροισμα αλλά να συνεχίσουν να αθροίζονται

στην γραμμή τους και να ταξινομούνται στην στήλη Μ.

Αυτό γίνεται ή είναι μεγάλη ιστορία;

 

[MastroGiannis]

Να ‘σαι καλά φίλε μου!

Τα "σφάλματα" είναι προειδοποιήσεις ότι η φόρμουλα δεν περιλαμβάνει (δήθεν) όλα τα κελιά, αφού το Excel "νομίζει" πως έχεις ξεχάσει τη στήλη Ε έξω από τη SUM() της στήλης Μ.  Απλά τα αγνοείς.

17 λεπτά πριν, profile είπε

Αυτό γίνεται ή είναι μεγάλη ιστορία;

Με την λύση που διάλεξες (την πρώτη) αυτό γίνεται ήδη.

Τα αθροίσματα της περιοχής «Σύνολα Ανθρώπων» περιλαμβάνουν μόνο τις γραμμές που τα γράμματά τους εμφανίζονται στη στήλη D. Αρκεί να είναι πάντα σε ζεύγη (A+E, B+F κτλ). Αν χρειαστεί να περιλαμβάνουν και τρίτη γραμμή (πχ A+E+K) ο τύπος θα χρειαστεί επέκταση.

Επίσης, αν τα πραγματικά δεδομένα δεν είναι απλά γράμματα της αλφαβήτου αλλά λέξεις ή ονόματα, ο τύπος πάλι θα χρειαστεί τροποποίηση.

Κάνε μια δοκιμή σε ένα αντίγραφο των πραγματικών σου δεδομένων να δεις αν προκύπτει κάποιο πρόβλημα.

Καλή συνέχεια!

[profile]

Κοίταξε να δεις τι έκανα. 

Έκανα εισαγωγή μια νέα στήλη δίπλα στην Ε .  Στην στήλη Ε και στην  καινούρια στον επάνω πίνακα έγραψα τα δυο ονοματεπώνυμα σε κάθε κελί.

Δεν πείραξα την D.

Φυσικά ο τύπος που μου έδωσες αυτόματα μετονόμασε το F σε G κτλ. 

Και στον κάτω πίνακα την στήλη Ε κανονικά τα ονοματεπώνυμα ABCDEFGH στο κελί τους.

Συμπλήρωσα και δυο-τρία ονοματεπώνυμα στις θέσεις 5-6 του κάτω πίνακα που δεν λαμβάνουν μέρος στο επάνω άθροισμα.

Κρύβω και την στήλη D να μην φαίνεται.  Κάνω τις δόκιμες και έχω την εντύπωση(το κεφάλι έχει γίνει καζάνι) οτι δουλεύει.  

Σε Ευχαριστώ και πάλι , τώρα έτσι όπως είμαι μπορεί να νομίζω οτι δουλεύει.

Το μόνο που θα χρειαστώ είναι ένα παράδειγμα του τύπου  =SUMIF($D$10:$D$20;LEFT($D4);F$10:F$20)+SUMIF($D$10:$D$20;RIGHT($D4);F$10:F$20)

για 100 γραμμές ώστε να ξέρω πως θα διαμορφώσω το τελικό φύλλο. 

Καλό ξημέρωμα.

 

[MastroGiannis]

17 ώρες πριν, profile είπε

Σε Ευχαριστώ και πάλι , τώρα έτσι όπως είμαι μπορεί να νομίζω οτι δουλεύει.

Να 'σαι καλά!

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

Θα χρειαστεί λοιπόν, για κάθε ζεύγος (ή ομάδα) ανθρώπων, να δημιουργήσεις ένα μοναδικό χαρακτηριστικό (ID, κωδικό κτλ), ώστε, οι άνθρωποι που συμμετέχουν σε κάποιο ζεύγος να φέρουν αυτό το χαρακτηριστικό και να περιλαμβάνονται μέσω αυτού στις αντίστοιχες αθροίσεις, ανεξάρτητα από το ονοματεπώνυμό τους ή τις παραλλαγές του σε διάφορα σημεία του φύλλου.

Σου αφήνω ένα δείγμα στημένο σύμφωνα με την παραπάνω προσέγγιση (βλέπε συνημμένο) ώστε να τη δεις στην πράξη.

Στο φύλλο "Data" εισάγεις τα "σταθερά" στοιχεία των ανθρώπων/προσώπων, των ζευγών/ομάδων και τις εβδομαδιαίες τιμές του κάθε προσώπου αναλυτικά.

Στο φύλλο "Totals" παίρνεις τα αθροίσματα των ομάδων.

Με το «Αυτόματο Φίλτρο», σε κάθε περίπτωση, φιλτράρεις και ταξινομείς τα δεδομένα ανά στήλη όπως επιθυμείς.

Τα φύλλα είναι κλειδωμένα, δίχως κωδικό, μόνο και μόνο για την προστασία των τύπων από ακούσια διαγραφή.

Αν δεις ότι σε βολεύει, μπορείς να το προσαρμόσεις εύκολα στις ανάγκες σου.

Καλή συνέχεια!

TotalGroups.zip

[profile]

Τι να πω νιώθω υποχρεωμένος. Ευχαριστώ για τον κόπο σου .

Κατάλαβα την λογική του δείγματός σου αλλά να σου πω οτι το τελικό μου excel θα είναι σε ένα φύλλο, θα μετατρέπεται σε Pdf και τελικά 

θα ανεβαίνει κάθε εβδομάδα υπό μορφή jpeg σε site. 

Κρατώ όμως το δείγμα σου για μελλοντική χρήση. Είμαι σίγουρος οτι θα μου χρειαστεί.  

 

Τι έχω κάνει λοιπόν στην προηγούμενη φόρμα με εισαγωγή μιας νέας στήλης.

Στις στήλες E και F στον επάνω πίνακα (Σύνολα Ανθρώπων) τα δυο ονοματεπώνυμα σε κάθε κελί.

Στην στήλη Ε  στον κάτω πίνακα (Άνθρωποι)  ονοματεπώνυμα σε κάθε κελί.

Στην στήλη D στο (Σύνολα Ανθρώπων)  έχω  (ας πούμε τα IDs)  -->  A+B  C+D   E+F  G+H  κτλ

Στην στήλη D στο (Άνθρωποι) έχω (ας πούμε τα IDs)  -->  A Β C D E F G H  κτλ

Μετέτρεψα τον τύπο που μου έδωσες σε  =SUMIF($D$19:$D$117;LEFT($D4);G$19:G$117)+SUMIF($D$19:$D$117;RIGHT($D4);G$19:G$117)

Και ναι δουλεύει. Ότι δοκιμές και να έκανα , τα αθροίσματα και οι ταξινομήσεις δουλεύουν σωστά.

Προέκυψε ένα πρόβλημα γιατί μου τελείωσαν τα γράμματα. Δοκίμασα με  ΑΑ+ΒΒ αλλά όχι. 

Δοκίμασα Ελληνικά γράμματα και δουλεύουν και αυτά σωστά. Στη συνέχεια δοκίμασα σύμβολα # $ % & *  και δουλεύουν και αυτά σωστά. 

Οπότε προς το παρόν είμαι καλυμμένος με την ποσότητα των  " IDs".

Εύχομαι να είναι όπως τα λέω και να μην προκύψει κανένα πρόβλημα στο μέλλον.

Πολύτιμη η βοήθεια σου. Να σαι καλά @MastroGiannis .

 

[MastroGiannis]

2 ώρες πριν, profile είπε

Τι να πω νιώθω υποχρεωμένος. Ευχαριστώ για τον κόπο σου

Να 'σαι καλά! Χαρά μου είναι να βοηθάω και να ακούω ότι δουλεύει. :-)

Φαντάζομαι θα γνωρίζεις πως μπορείς να αντιγράψεις μια περιοχή κελιών "Ως Εικόνα" (As Picture). Το αναφέρω σε περίπτωση που σε γλιτώνει από το βήμα εξαγωγής σε PDF.

Ο τύπος που έχεις λειτουργεί μόνο για έναν χαρακτήρα δεξιά-αριστερά από το σύμβολο συν.

Ο παρακάτω λειτουργεί με οποιοδήποτε αριθμό χαρακτήρων δεξιά-αριστερά του συν, ακόμα δηλαδή και με ολόκληρα τα ονόματα:

=SUMIF($D$19:$D$117;LEFT(D4;FIND("+";$D4)-1);G$19:G$117)+SUMIF($D$19:$D$117;MID($D4;FIND("+";$D4)+1;99);G$19:G$117)

Φρόντιζε να μην υπάρχουν κενά αμέσως δεξιά κι αριστερά του συν για να γλιτώνουμε την TRIM() έξω από την FIND().

Οπότε, το δοκιμάζεις αν δουλεύει και στη στήλη ονομάτων, αρκεί να είναι ίδια ακριβώς, στην κανονική και τη μικτή μορφή (Όνομα+Όνομα).

Τα λέμε!...

[profile]

3 ώρες πριν, MastroGiannis είπε

Φαντάζομαι θα γνωρίζεις πως μπορείς να αντιγράψεις μια περιοχή κελιών "Ως Εικόνα" (As Picture).

Ναι, αλλά χρειάζομαι και το Pdf.😊

 

3 ώρες πριν, MastroGiannis είπε

=SUMIF($D$19:$D$117;LEFT(D4;FIND("+";$D4)-1);G$19:G$117)+SUMIF($D$19:$D$117;MID($D4;FIND("+";$D4)+1;99);G$19:G$117)

Παιδεύτηκα πολύ μέχρι να ανακαλύψω οτι έλειπε ένα $ αλλά τελικά το βρήκα 

=SUMIF($D$19:$D$117;LEFT($D4;FIND("+";$D4)-1);G$19:G$117)+SUMIF($D$19:$D$117;MID($D4;FIND("+";$D4)+1;99);G$19:G$117)

Δουλεύει πολύ καλά. 

 

Και μιας και το ξεχειλώσαμε δε μου λες και τον τύπο για άθροισμα τριών ή τεσσάρων ονομάτων.

Μπας και χρειαστεί.  

[MastroGiannis]

7 ώρες πριν, profile είπε

Και μιας και το ξεχειλώσαμε δε μου λες και τον τύπο για άθροισμα τριών ή τεσσάρων ονομάτων.

Μπας και χρειαστεί.  

Μάλλον ήγγικεν η ώρα να περάσεις σε αναγνωριστικό αριθμού αντί κειμένου. Κάτι ήξεραν οι φαντάροι που "αντί για όνομα είχαν αριθμό".

Όχι ότι δεν γίνεται αλλά θα γίνει μεγάλο μακρυνάρι και τώρα πρέπει να φύγω.

Sorry για το $ αλλά ήταν και ώρα δύσκολη (τις δουλειές της νύχτας...).

Καλό μεσημέρι!

[profile]

1 ώρα πριν, MastroGiannis είπε

Όχι ότι δεν γίνεται αλλά θα γίνει μεγάλο μακρυνάρι και τώρα πρέπει να φύγω.

Δεν πειράζει, άστο. Η δουλειά μου γίνεται και με το παραπάνω.