EXCEL: Suma brojeva

[Mikeyy]

Ako imam u retku ovakav niz unosa:
8 N1 N3 10 N5 N2 3 N9
Kako najjednostavnije dobiti sumu brojeva koji se nalaze iza slova, i to samo njih, bez ovih samostalnih brojeva?

U mom slučaju je to niz od 31 polje u kojima može biti, a ne mora neki unos. A kada ima unos on je jedan od ova 3.
1. broj
2. slovo
3. slovo+broj

Znači kako detektirati ove slovo+broj unose i dobiti ukupnu sumu brojeva iza slova?

[E-mil]

Extracting numbers from alphanumeric string

[Mikeyy]

Zahvaljujem. Znam izvući broj iz niza, što bi trebao ako je uopće moguće je rješenje u jednoj čeliji za gornji problem.

Imam već rješenje kako ga napraviti sa pomoći isto toliko polja koliko ima i brojeva.

=VALUE(IF(LEFT(A1)="n";RIGHT(A1);IF(LEFT(A1)="N";RIGHT(A1);0)))

Ako niz kreće od npr A1:H1 stavi se nakon H1 gornja formula i povuče na desno za isti broj polja. I onda još jedno polje u kojem je suma svega.

Da li postoji rješenje bez toliko polja, da se cijeli niz detektira i sumira u jednom polju?

[IvanVk]

Autor Mikeyy

Da li postoji rješenje bez toliko polja, da se cijeli niz detektira i sumira u jednom polju?

Na brzinu a možda ti pomogne, uzmimo za primjer da su ti podaci u prvom redu u range A1:AE1 (31 ćelija)
U ćeliju AF1 postavi slijedeću formulu

=SUM(IF(A1:AE1<>"";SUBSTITUTE(SUBSTITUTE(A1:AE1;"N";"");"N";"")+0))-SUM(A1:AE1)

Ova formula je formula polja ( ARRAY formula ili matrična formula )

Prema podacima koje si dao u prvom postu rezultat u ćeliji AF1 treba biti 20 (N1+N3+N5+N2+N9=20)
Uz drugi uvjet koji si dao u prvom postu, da slovo koje je samostalno ne bude samostalno npr: N => N0 (dakle obavezno nula uz N)

Ako ti ovo nije pomoglo napiši kako si riješio radi drugih čitalaca teme.

[IvanVk]

Autor Mikeyy

A kada ima unos on je jedan od ova 3.
1. broj (x)
2. slovo (N)
3. slovo+broj (Nx)
Znači kako detektirati ove slovo+broj unose i dobiti ukupnu sumu brojeva iza slova?

Evo ti formula i za sva tri uvjeta kako si naveo

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(A1:AE1;"N";"");"N";""))+0)-SUM(A1:AE1)

Ova formula uz ova tri uvjeta zbraja samo slovo+broj,

[Mikeyy]

Zahvaljujem. Tu sam negdje i sam došao, ovo je moja formula direktno iz tablice:
=SUM(IF(LEFT($D9:$AH9;1)="G";VALUE(RIGHT($D9:$AH9;1));IF(LEFT($D9:$AH9;1)="g";VALUE(RIGHT($D9:$AH9;1));0)))

Dugačka je zato što sam htio da vrijedi i za G i g slova.

Pojednostavljena da vrijedi samo za jedno slovo je:
=SUM(IF(LEFT($D9:$AH9;1)="G";VALUE(RIGHT($D9:$AH9;1));0))

I to sve se ukuca ne sa enter nego sa CTRL + SHIFT + ENTER kao ARRAY formula.

Znači formula prvo provjerava da li unos u polju sadržava slovo G na prvom mjestu sa lijeve strane. Ako sadržava onda uzima prvo slovo sa desne strane i pretvara ga u broj.

Ako se prvi uvjet ne ispuni, tj. prvo slovo nije G, onda uzima 0 kao rezultat.

Ima manu, ako se ukuca samo G onda izbacuje grešku. Ali možda i nije loše, bar će korisnik znati da nešto nije dobro napravio.
Vjerovatno bih mogao i to izbjeći sa još jednom if funkcijom.

Probat ću ubaciti i tvoju da vidim kako radi.

Zahvaljujem još jednom.

[IvanVk]

Autor Mikeyy

Ima manu, ako se ukuca samo G onda izbacuje grešku.

Ova formula uzima u obzir sve varijante i ne smetaju joj samostalna slova ali ignorira samostalne brojeve:
N, n, N1, 3, N2, n1, 5, => rezultat je 4

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(A1:AE1;"N";"");"n";""))+0)-SUM(A1:AE1)

[Mikeyy]

Ne radi ta formula. Daj mi objasni po dijelovima sve.
Koliko vidim nemaš nikakav VALUE() na ovim SUBSTITUTE() funkcijama. Vjerovatno rezultat ispada kao tekst, a ne broj.
Uglavnom, prvi dio fomule mi daje rezultat 0, a - drugio dio sum, sa mojim podacima daje -108, trebao bi 40. 108 je suma čistih brojeva u redu, koji nemaju slovo ispred.

[IvanVk]

Autor Mikeyy

Ako imam u retku ovakav niz unosa:
8 N1 N3 10 N5 N2 3 N9
Kako najjednostavnije dobiti sumu brojeva koji se nalaze iza slova, i to samo njih, bez ovih samostalnih brojeva?...................Znači kako detektirati ove slovo+broj unose i dobiti ukupnu sumu brojeva iza slova?

Autor Mikeyy;

Ne radi ta formula. Daj mi objasni po dijelovima sve

Ovo iznad je tvoj zadatak.

Prva formula zbraja sve brojeve koji se nalaze uz slovo "N" ili "n" i ona glasi
Dakle ignorira samostalne brojeve, ako pogledamo priloženu sliku to je rezultat u AF1 => 20

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(A1:AE1;"N";"");"n";""))+0)-SUM(A1:AE1)

Druga formula zbraja sve brojeve koji se nalaze u rasponu bez obzira jesu li uz slovo "N" ili "n" ili samostalni i ona glasi
Ako pogledamo priloženu sliku to je rezultat u AF2 => 41

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(A1:AE1;"N";"");"n";""))+0)

Treća formula zbraja sve samostalne vrijednosti u rasponu i rezultat je 21

=SUM(A1:AE1)

Ako malo pogledaš ove formule tada možeš zaključiti slijedeće

=(Druga formula)-(Treća formula) i dobiješ prvu formulu sa rezultatom 20

ili matematički 41-(8+10+3)=20 => 41-21=20
Ovi brojevi 8, 10 i 3 su samostalni brojevi a njih si htio ignorirati, zar ne

Isprobaj EVALUATE FORMULA na formuli



Download datoteke imaš OVDJE

[Mikeyy]

Skužio sam šta bi trebalo raditi, samo što to ne radi kod mene.

Šta znači ovo u formuli:
0&

Problem je do LibreOffice-a. Ja koristim njega, a ti noviji MS Office (2007 ili 2010?). Vjerovatno je MS promjenio shvaćanje te formule u novijem office-u jer kod mene dobijem -21 u prvom redu.

Al barem sada sa tvojim slikama i tablicom mogu prijaviti bug, pa nek traže greške.

[IvanVk]

Autor Mikeyy

Šta znači ovo u formuli:0&

Ako si probao Evaluate Formula (ako to postoji u LibreOffice) tada si mogao uočiti da funkcija SUBTITUTE kao međurezultat vrati brojeve pod navodnicima u array nizu. Ova nula (0) se spaja sa svakim brojem iz dotičnog niza i na kraju ostaju samo brojevi koji obrađuje funkcija SUMPRODUCT

Problem je do LibreOffice-a. Ja koristim njega

Onda ti ne odgovara naslov teme koji si stavio

[Mikeyy]

Excel ili Calc sve je to jedno te isto, 99%. Ti si našao onih 1%.

[IvanVk]

Autor Mikeyy

Excel ili Calc sve je to jedno te isto, 99%. Ti si našao onih 1%.

Valjda je tako, "Calc" nisam nikada ni vidio.
Da si napisao "Calc" umjesto Excel ja ti se ne bi ni javio
pozdrav