Formule de calcul mai elegante
Scris: Sâm Apr 06, 2024 7:10 pm
Într-un tabel de pontaj aș dori să fac formulele mai elegante.
De exemplu formula =COUNTIF(B6:AF6,"=7-15")*8+COUNTIF(B6:AF6,"=15-23")*8+COUNTIF(B6:AF6,"=23-7")*8+COUNTIF(B6:AF6,"=7-19")*12+COUNTIF(B6:AF6,"=19-7")*12+COUNTIF(B6:AF6,"=7-11")*4+COUNTIF(B6:AF6,"=11-19")*8 sa o comprim folosind AND si COUNTIFS.
Exemplul de calcul ore de sambata/duminica este foarte stufos si nu ruleaza pe Excel 2007
=COUNTIFS(B6:AF6,"7-15",$B5:$AF$5,"=Sa")*8+COUNTIFS(B6:AF6,"7-15",B$5:$AF$5,"=Du")*8+COUNTIFS(B6:AF6,"15-23",$B$5:$AF$5,"=Sa")*8+COUNTIFS(B6:AF6,"15-23",$B$5:$AF$5,"=Du")*8+COUNTIFS(B6:AF6,"23-7",$B$5:$AF$5,"=Vi")*7+COUNTIFS(B6:AF6,"23-7",$B$5:$AF$5,"=Sa")*8+COUNTIFS(B6:AF6,"23-7",$B$5:$AF$5,"=Du")*1+COUNTIFS(B6:AF6,"7-19",$B$5:$AF$5,"=Sa")*12+COUNTIFS(B6:AF6,"7-19",$B$5:$AF$5,"=Du")*12+COUNTIFS(B6:AF6,"19-7",$B$5:$AF$5,"=Vi")*7+COUNTIFS(B6:AF6,"19-7",$B$5:$AF$5,"=Sa")*12+COUNTIFS(B6:AF6,"19-7",$B$5:$AF$5,"=Du")*5+COUNTIFS(B6:AF6,"11-197",$B$5:$AF$5,"=Du")*8+COUNTIFS(B6:AF6,"11-19",$B$5:$AF$5,"=Sa")*8
Daca se comprima probabil ca va functiona si pe Excel 2007
Aplicatia mea functioneaza corect, dar nu sunt suficient de priceput si as dori putin ajutor.
Multumesc anticipat
De exemplu formula =COUNTIF(B6:AF6,"=7-15")*8+COUNTIF(B6:AF6,"=15-23")*8+COUNTIF(B6:AF6,"=23-7")*8+COUNTIF(B6:AF6,"=7-19")*12+COUNTIF(B6:AF6,"=19-7")*12+COUNTIF(B6:AF6,"=7-11")*4+COUNTIF(B6:AF6,"=11-19")*8 sa o comprim folosind AND si COUNTIFS.
Exemplul de calcul ore de sambata/duminica este foarte stufos si nu ruleaza pe Excel 2007
=COUNTIFS(B6:AF6,"7-15",$B5:$AF$5,"=Sa")*8+COUNTIFS(B6:AF6,"7-15",B$5:$AF$5,"=Du")*8+COUNTIFS(B6:AF6,"15-23",$B$5:$AF$5,"=Sa")*8+COUNTIFS(B6:AF6,"15-23",$B$5:$AF$5,"=Du")*8+COUNTIFS(B6:AF6,"23-7",$B$5:$AF$5,"=Vi")*7+COUNTIFS(B6:AF6,"23-7",$B$5:$AF$5,"=Sa")*8+COUNTIFS(B6:AF6,"23-7",$B$5:$AF$5,"=Du")*1+COUNTIFS(B6:AF6,"7-19",$B$5:$AF$5,"=Sa")*12+COUNTIFS(B6:AF6,"7-19",$B$5:$AF$5,"=Du")*12+COUNTIFS(B6:AF6,"19-7",$B$5:$AF$5,"=Vi")*7+COUNTIFS(B6:AF6,"19-7",$B$5:$AF$5,"=Sa")*12+COUNTIFS(B6:AF6,"19-7",$B$5:$AF$5,"=Du")*5+COUNTIFS(B6:AF6,"11-197",$B$5:$AF$5,"=Du")*8+COUNTIFS(B6:AF6,"11-19",$B$5:$AF$5,"=Sa")*8
Daca se comprima probabil ca va functiona si pe Excel 2007
Aplicatia mea functioneaza corect, dar nu sunt suficient de priceput si as dori putin ajutor.
Multumesc anticipat