Συνάρτηση Αναζήτησης σε πολλά φύλλα στο Excel

[demessakis]

1 ώρα πριν, kostas 234 είπε

καλημέρα έβαλα αυτές της συνάρτησής και το αποτέλεσμα είναι στο κελί ΑΚ (Καθημερινές) μου βγάζει όλες της υπηρεσίες στο κελί AL (Αργίες) τίποτα  και στο κελί ΑΜ (Σύνολο) εκεί είναι ένταξή

Καλημέρα! Ή θα πρέπει να κάνετε αυτό που λέει ο Μαστρογιάννης:

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

Πάνω από την αρίθμηση των ημερών (περιοχή F2-AJ2) βάζε την τιμή 1 σε κάθε κελί που η από κάτω μέρα πέφτει σε αργία.

Ή αν ως αργίες εννοείτε μόνο τα Σαββατοκύριακα, τότε βάλτε αυτή την συνάρτηση στο AL5:

=SUMPRODUCT((($F$4:$AJ$4="Σαβ")+($F$4:$AJ$4="Κυρ"))*F5:AJ5)

και σύρτε προς τα κάτω.

Η παραπάνω μετράει τις βάρδιες που πέφτουν σε Σ/Κ, αν θέλετε τις μέρες βάλτε αυτή την συνάρτηση:

=SUMPRODUCT((($F$4:$AJ$4="Σαβ")+($F$4:$AJ$4="Κυρ"))*(F5:AJ5>0))

 

 

Επεξ/σία 9 Μαρτίου 2022 από demessakis

[kostas 234]

ευχαριστώ το έφτιαξα όπως είπες αλλά μου άρεσε και αυτό που είπες πως θα το κάνω με την αργίες για κάθε μήνα να τα βρίσκει μόνο του ?

[micos000]

Δε χρειάζεται να βάλεις τους άσσους πάνω από τις αργίες. Χρησιμοποίησε την συνάρτηση του demessakis...

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

Ή αν ως αργίες εννοείτε μόνο τα Σαββατοκύριακα, τότε βάλτε αυτή την συνάρτηση στο AL5:

=SUMPRODUCT((($F$4:$AJ$4="Σαβ")+($F$4:$AJ$4="Κυρ"))*(F5:AJ5>0))

Θα σου μετράει τις μέρες που γράφεις ως Σαβ και Κυρ.

Αν θέλεις μπορείς να προσθέσεις ένα + ακόμα και να ορίζεις και τις κινούμενες αργίες γράφοντας Αργ στην γραμμή 2 πάνω από τις μέρες που είναι επίσημες αργίες, όπως για παράδειγμα στις 25 Μαρτίου θα βάλεις στο κελί AD2 το Αργ και θα κάνεις την συνάρτηση ως...

=SUMPRODUCT((($F$4:$AJ$4="Σαβ")+($F$4:$AJ$4="Κυρ")+($F$2:$AJ$2="Αργ"))*(F5:AJ5>0))

Απλά δεν θα βάζεις το Αργ πάνω από τα Σαββατοκύριακα γιατί είναι έτσι κι αλλιώς αργίες.

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

Π.Χ.

 

EDIT:

Να ρωτήσω κάτι που μου ήρθε στο μυαλό;

Γίνεται να κάνουμε μορφοποίηση υπό όρους π.χ. στο παραπάνω φύλλο και αφού δώσουμε δύο όρους για Σαβ και Κυρ να τα χρωματίζει και μετά να χρωματίζει και τα κελιά προς τα κάτω με το ίδιο χρώμα;

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

Ειμαι σίγουρος ότι θα γίνεται με vba αλλά έλεγα μήπως δεν χρειαστεί να μπλέξουμε με το scripting.

Επεξ/σία 9 Μαρτίου 2022 από micos000

[kostas 234]

@demessakis & @micos000 ευχαριστώ πολύ το έφτιαξα όπως μου είπατε  να ευχαριστήσω για ακόμα μια φορά

[demessakis]

4 ώρες πριν, micos000 είπε

Γίνεται να κάνουμε μορφοποίηση υπό όρους π.χ. στο παραπάνω φύλλο και αφού δώσουμε δύο όρους για Σαβ και Κυρ να τα χρωματίζει και μετά να χρωματίζει και τα κελιά προς τα κάτω με το ίδιο χρώμα;

 

Η Μορφοποίηση Υπό Όρους, είναι παιχνιδάκι αν πιάσεις το νόημά της. Για το παραπάνω, θα πρέπει να επιλεχθούν τα κελιά από Fx έως AJy, και να μπει η συνάρτηση:

=OR(F$4="Σαβ";F$4="Κυρ")

[kostas 234]

επιλέγω τα κελιά από F έως AJ βάζω την συνάρτηση αλλά δεν βλέπω να κάνει κάτι η εκτός αν θέλει και τίποτα άλλο και δεν έχω καταλάβει

 

 

οκ το έφτιαξα είναι πολύ ωραίο

ευχαριστώ!!!!

Επεξ/σία 9 Μαρτίου 2022 από kostas 234

[MastroGiannis]

Σύντροφοι,

Διαφωνώ κάθετα με τη χρήση κειμένων σε διαδικασίες ταυτοποίησης.

Ο αριθμός 1 είναι μοναδικός στο σύμπαν των αριθμών αλλά και στο σύμπαν των κωδικοσελίδων. Επίσης, απαιτεί μία πληκτρολόγηση αντί των τριών του "Αργ".

Η έκφραση "Κυρ"="Kυρ" μπορεί και να μην ισχύει, ειδικά αν το ένα "Κ" είναι λατινικό και το άλλο ελληνικό. Το ίδιο ισχύει και με το "Αργ".

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

Εφόσον το αρχείο είναι ακόμη σε αρχικό στάδιο αυτοματισμού, σκέφτηκα να προτείνω αρχικά τους χειροκίνητους άσσους ώστε στο επόμενο βήμα να αντικατασταθούν από μια λογική συνάρτηση, όταν για παράδειγμα οι ημέρες του πίνακα θα περιέχουν πραγματικές τιμές ημερομηνίας, με κατάλληλη προσαρμοσμένη μορφή κελιού (custom format), και θα συμπληρώνονται αυτόματα από μια κεντρική επιλογή μηνός και έτους. Σε μια τέτοια περίπτωση, ο τύπος = -(WEEKDAY(F3;2)>5) στο κελί F2, θα επέστρεφε -1 αν ήταν Σάββατο ή Κυριακή και 0 για τις καθημερινές.

Η αναζήτηση της κάθε ημέρας του πίνακα σε μια στήλη με τις ημερομηνίες αργιών, θα μπορούσε να μας πληροφορήσει αν είναι αργία ή όχι – είναι δεν είναι Σ-Κ. Με την τιμή -1 για Σάββατο και Κυριακή, και την τιμή 1 για τις επίσημες αργίες, μπορούμε τις χειριστούμε διαφορετικά ως περιπτώσεις αλλά και να τις αθροίσουμε ως τιμές με τη χρήση της συνάρτησης ABS(). Οπότε ο τύπος στο F2 τώρα γίνεται:

=IF(ISNA(MATCH(F3;HollyDays;0));-(WEEKDAY(F3;2)>5);1)

όπου HollyDays η στήλη με τις ημερομηνίες των επίσημων αργιών.

Ο δε τύπος στο κελί για το σύνολο των αργιών, τώρα γίνεται:

=SUMPRODUCT(--(F5:AJ5>0);ABS($F$2:$AJ$2))

ενώ οι υπόλοιποι παραμένουν οι ίδιοι.

Με αριθμητική απλότητα, ακρίβεια και στιβαρότητα.

 

Δείτε αν θέλετε την εφαρμογή των παραπάνω στο συνημμένο.

Πρόγραμμα Ημερήσιο στης Μηχανές(AutoDate).xlsx

14 ώρες πριν, demessakis είπε

=SUMPRODUCT((($F$4:$AJ$4="Σαβ")+($F$4:$AJ$4="Κυρ"))*F5:AJ5)

Ο παραπάνω τύπος αθροίζει τις τιμές που "πέφτουν" σε ΣΚ, δεν τις καταμετράει, πράγμα το οποίο κάνει σωστά ο

14 ώρες πριν, demessakis είπε

=SUMPRODUCT((($F$4:$AJ$4="Σαβ")+($F$4:$AJ$4="Κυρ"))*(F5:AJ5>0))

Οι τιμές 1,2 και 3 του πίνακα, δεν είναι πλήθος βαρδιών αλλά κωδικοί πόστου.

Μίλα ρε Κώστα! Εγώ θα τα λέω;

 

9 ώρες πριν, micos000 είπε

Γίνεται να κάνουμε μορφοποίηση υπό όρους π.χ. στο παραπάνω φύλλο και αφού δώσουμε δύο όρους για Σαβ και Κυρ να τα χρωματίζει και μετά να χρωματίζει και τα κελιά προς τα κάτω με το ίδιο χρώμα;

Αυτό ακριβώς πρότεινα εδώ:

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

Tip: Επιλέγοντας όλα τα κελιά ημερών του πίνακα, μπορείς να εισάγεις μορφοποίηση υπό όρους με τύπο =F$2=1 και με κάποιον σκούρο φόντο, ώστε οι στήλες που ορίζεις ως αργίες να χρωματίζονται αυτόματα.

αλλά έπρεπε να ήμουν πιο σαφής. Έπρεπε να πω: "όλα τα κελιά βαρδιών του πίνακα".

 

[kostas 234]

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

αυτό που έγραψε ο MastroGiannis για το 1,2 και 3 όντος είναι κωδικοί πόστου.

καλά και αυτό που έκανες φίλε MastroGiannis  είναι απεχτο

να ευχαριστήσω για ακόμα μια φορά τα παιδιά @demessakis & @micos000 @MastroGiannis

[MastroGiannis]

Να 'σαι καλά Κώστα!

Τα έχεις στήσει ωραία τα πινακάκια σου και βοηθάνε στον αυτοματισμό τους.

Όπως βλέπεις, είναι ευχάριστο να ανακαλύπτεις τις δυνατότητες των εργαλείων σου αλλά και να φαντάζεσαι και νέες ώστε να τα εξελίσσεις. Γι' αυτό σου έριξα και το "δόλωμα" με τους άσσους στις αργίες.

Σε ό,τι κι αν κολλήσεις ή για ό,τι άλλο σκαρφιστείς, εδώ είμαστε να βοηθήσουμε.

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

 

[kostas 234]

Στο πρώτο φύλλο πως θα το κάνω να βγάζει και εκεί της αργίες με χρώμα ? βάζω της συνάρτησής και δεν βγάζω άκρη

Επεξ/σία 10 Μαρτίου 2022 από kostas 234

[demessakis]

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

Σύντροφοι,

Διαφωνώ κάθετα με τη χρήση κειμένων σε διαδικασίες ταυτοποίησης.

Ο αριθμός 1 είναι μοναδικός στο σύμπαν των αριθμών αλλά και στο σύμπαν των κωδικοσελίδων. Επίσης, απαιτεί μία πληκτρολόγηση αντί των τριών του "Αργ".

Η έκφραση "Κυρ"="Kυρ" μπορεί και να μην ισχύει, ειδικά αν το ένα "Κ" είναι λατινικό και το άλλο ελληνικό. Το ίδιο ισχύει και με το "Αργ".

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

Αναλόγως τα δεδομένα, βγαίνουν και τα αποτελέσματα. Αν το "Κ" είναι λατινικό ή αν το "1" είναι... Ρωμαϊκό (δηλ. "I"), ό,τι συνάρτηση και να βάλετε, θα λάβετε λάθος αποτέλεσμα. Προφανώς δεν θα είναι λάθος η συνάρτηση, αλλά τα δεδομένα. Επίσης, ναι, γλιτώνουμε bytes, αλλά δεν ξέρω και τι επίπτωση θα μπορεί να έχει η βοηθητική σειρά. Anyway...

Δεν γνωρίζω αν χρειάζονται κάπου τα -1 και 1, αλλά αν στην WEEKDAY, μπει διπλό "-" στην αρχή, μετατρέπεται σε θετικό.

Πρόταση:

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

[kostas 234]

Φίλε @MastroGiannis θα μπορούσες να μου κάνεις και αυτό το αρχείο ecxel είναι το πρώτο που φτιάξαμε  όπως έκανες το ποιο πάνω ?

Το έψαξα αλλά δεν κατάλαβα πως το έκανες.

ΥΠΗΡΕΣΙΕΣ 2022.xlsx

[micos000]

11 ώρες πριν, kostas 234 είπε

Στο πρώτο φύλλο πως θα το κάνω να βγάζει και εκεί της αργίες με χρώμα ? βάζω της συνάρτησής και δεν βγάζω άκρη

Θα μαρκάρεις τα κελιά A11έως H41 και θα κάνεις μορφοποίηση υπό όρους->Διαχείριση κανόνων όπου θα προσθέσεις δύο κανόνες στο τέλος της λίστας με χρήση τύπου ως εξής:

=$b11="Σάββατο"
=$b11="Κυριακή"

Αν θέλεις να δηλώνεις και άλλες αργίες μπορείς να βάλεις έναν άσσο στην στήλη i και να προσθέσεις στο τέλος της λίστας με τους κανόνες για το ίδιο range το...

=$i11=1

 

5 ώρες πριν, kostas 234 είπε

Φίλε @MastroGiannis θα μπορούσες να μου κάνεις και αυτό το αρχείο ecxel είναι το πρώτο που φτιάξαμε  όπως έκανες το ποιο πάνω ?

Παρόμοια και σε αυτό.

[kostas 234]

@micos000 ευχαριστώ πολύ το έφτιαξα

[MastroGiannis]

 

Καλησπέρα σε όλους!

9 ώρες πριν, demessakis είπε

Αναλόγως τα δεδομένα, βγαίνουν και τα αποτελέσματα. Αν το "Κ" είναι λατινικό ή αν το "1" είναι... Ρωμαϊκό (δηλ. "I"), ό,τι συνάρτηση και να βάλετε, θα λάβετε λάθος αποτέλεσμα.

Ο αριθμός 1, είναι αριθμός. Το ρωμαϊκό "Ι",  το ιταλικό "Uno", το αγγλικό "One", το ελληνικό "Ένα" ή ο "Άσσος" κτλ, είναι κείμενα που πληκτρολογούνται από διαφορετικά του αριθμητικού πληκτρολόγια.

Ο τύπος =CODE(1) και ο τύπος =CODE("1"), στο Excel επιστρέφουν την ίδια τιμή (49), σε όποια γλώσσα κι αν γράψεις το 1.

Μπορείς να μαντέψεις από εδώ, ποιανού συμβόλου τιμή θα επιστρέψει ο τύπος =CODE("l");

Έχεις προσέξει ότι σε πολλές περιπτώσεις, το τονισμένο κεφαλαίο Ά, δεν εμφανίζεται στους υπότιτλους ταινιών;

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

9 ώρες πριν, demessakis είπε

Προφανώς δεν θα είναι λάθος η συνάρτηση, αλλά τα δεδομένα.

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

9 ώρες πριν, demessakis είπε

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

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

9 ώρες πριν, demessakis είπε

Δεν γνωρίζω αν χρειάζονται κάπου τα -1 και 1, αλλά αν στην WEEKDAY, μπει διπλό "-" στην αρχή, μετατρέπεται σε θετικό.

Το εξηγώ εδώ αυτό:

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

Με την τιμή -1 για Σάββατο και Κυριακή, και την τιμή 1 για τις επίσημες αργίες, μπορούμε τις χειριστούμε διαφορετικά ως περιπτώσεις αλλά και να τις αθροίσουμε ως τιμές με τη χρήση της συνάρτησης ABS().

αλλά πλέον δεν έχει σημασία. Αντί για -1 και 1, στο παρόν συνημμένο, τα ΣΚ παίρνουν την τιμή 1, οι αργίες την τιμή 2 και οι αργίες που πέφτουν σε ΣΚ την τιμή 3. Επίσης, οι τύποι έχουν απλοποιηθεί κι άλλο. Για παράδειγμα, ο ISNA(MATCH(F7;HollyDays;0))

έχει αντικατασταθεί από τον COUNTIF(HollyDays;F$7)

και, όλος ο τύπος του κελιού F6 έχει γίνει

=(WEEKDAY(F$7;2)>5)+(COUNTIF(HollyDays;F$7)*2).

9 ώρες πριν, demessakis είπε

Πρόταση:

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

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

 

Πρόγραμμα Ημερήσιο στης Μηχανές(HollyDays).xlsx

Επεξ/σία 10 Μαρτίου 2022 από MastroGiannis