Excel, τρόπος να μην παίρνουμε υπόψη κενά κελιά

[Speculoos]

Ας πούμε ότι έχω το παρακάτω:

	A	B
1	10	2
2
3	15	1
4	25	5

 

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

Θέλω να βρω πόσα κουτιά συνολικά έχω στην αποθήκη. Ουσιαστικά θέλω να βρω τον λόγο κάθε σειράς και να τον προσθέσω. Το θέμα είναι ότι δεν μπορώ να κάνω =SUM(A1/B1,A2/B2,A3/B3,A4/B4) επειδή η σειρά 2 έχει κενά κελιά.

Σκέφτηκα να χρησιμοποιήσω την εξής φόρμουλα:

=SUM(IF(A1="",0,A1/B1),IF(A2="",0,A2/B2),IF(A3="",0,A3/B3),IF(A4="",0,A4/B4))

 

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

Υπάρχει κάποιος πιο σύντομος τρόπος να πετύχω το ίδιο αποτέλεσμα ή τουλάχιστον να γίνει autofill όλη η φόρμουλα;

 

[asmilon]

Θα προτεινα να φτιαξεις μια στηλη C και να βαλεις την παρακατω φορμουλα στο C1:

=IFERROR(A1/B1;0)

Αυτο θα εκτελεσει την διαιρεση A1/B1 που θες και αν βρει λαθος (δηλαδη στην περιπτωση σου δεδομενα κενα) θα επιστρεψει την τιμη 0, αρα μηδεν κουτια.

Σερνεις αυτον τον τυπο μεχρι κατω και μετα βρισκεις τo συνολο σου βαζοντας σε ενα διπλανο κελι =SUM(C:C). Aν πιστευεις πως  η στηλη C θα σου χαλασει την μορφοποιηση, απλα κανε δεξι κλικ πανω της και επελεξε αποκρυψη, οποτε δε θα φαινεται, αλλα οι υπολογισμοι θα συνεχισουν να γινονται...

Επεξ/σία 23 Απριλίου 2018 από asmilon

[koumos]

Σωστή η λύση του asmilon απλά να πω και έναν πιο εύκολο τρόπο. Χρησιμοποιήσε την formula aggregate(9;6;XX) η οποία έχει ειδικό function άθροισης χωρίς να λαμβάνει υπόψιν τα errors.

(έχει γενικά πολλές επιλογές η φορμουλα αυτή θα τις δεις καθώς την γράφεις πάνω στη μπάρα)

[Speculoos]

Ευχαριστώ, μάλλον δε γλιτώνω το insert column δηλαδή.

[demessakis]

Εφόσον δεν θέλεις να προσθέσεις μια έξτρα στήλη, τότε δοκίμασε την παρακάτω φόρμουλα:
=SUMPRODUCT(A1:A4;IF(B1:B4>0;1/(B1:B4);0))

Θα πρέπει να τοποθετηθεί με ctrl+shift+enter.

[theibanez]

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

Εφόσον δεν θέλεις να προσθέσεις μια έξτρα στήλη, τότε δοκίμασε την παρακάτω φόρμουλα:
=SUMPRODUCT(A1:A4;IF(B1:B4>0;1/(B1:B4);0))

Θα πρέπει να τοποθετηθεί με ctrl+shift+enter.

εναλλακτικά 

=SUMPRODUCT(A1:A4;IFERROR(1/B1:B4;1))

[Speculoos]

Πολύ καλή ιδέα να χρησιμοποιήσω array formulas. Eυχαριστώ!

 

EDIT: Η formula αυτή δουλεύει μια χαρά και όταν δεν έχουμε τον πίνακα σε συνεχόμενα κελιά όπως στο παράδειγμα παρακάτω με μια μικρή τροποποίηση (χρήση CHOOSE)

A	B	C	D	E	F	G	H
10	2			15	1	25	5

 

=SUMPRODUCT(CHOOSE({1,2,3,4},A1,C1,E1,G1),IFERROR(1/CHOOSE({1,2,3,4},B1,D1,F1,H1),1)

Επεξ/σία 24 Απριλίου 2018 από Speculoos