[es] - [Excel] Kako da izvucem 3 maksimalne vrednosti iz niza + bojenje tih celija?

Nextbtg
BTN
Beograd

Član broj: 169988
Poruke: 16
*.beotel.net.

+4 Profil

[Excel] Kako da izvucem 3 maksimalne vrednosti iz niza + bojenje tih celija?

^{16.11.2010. u 14:57 - pre 175 meseci}

Pozdrav svima...pokusavam da uradim kompleksno-banalnu stvar

ceo dan, imam 3 uslova, 2 sam ispunio, ali treci nikako

Opis problema:
Imam 6 dobavljaca. Za svaki proizvod imam bar 1 cenu od svakog dobavljaca.
1. zelim da ukoliko za 1 proizvod imam vise od 3 cene u obzir uzmem 3 najniže cene i od njih izračunam prosečnu vrednost (10, 7 , 9, 4 , 6 , 8 = 5,66)
2. ukoliko imam 3 cene, izbacujem najvecu i prikazujem sledeću najvecu cenu (5, 4, 2)
3. ukoliko imam manje od 3 cene, uzimam najveću (4, 8)
*za svaki proizvod nemam uvek cene svih dobavljaca
* bilo bi lepo kada bi koriscene vrednosti mogle da se oboje (primer dat u fajlu u prilogu)
Bilo bi dobro da u okviru jedne funkcije objedinim sva tri uslova, ako ne - može i svaka zasebno u posebnim kolonama, pa cu rucno - to je najmanji problem

.

Primer fajla se nalazi u fajlu koji sam zakačio, prvi rezultat u istoimenoj koloni je ručno napravljen...bilo bi sjajno ako bi uspeli da automatizuemo sve.

Na početku poruke sam napisao da sam stavke 2 i 3 resio (nisam naveo u fajlu, kako bi eventualno neko objedinio sve u 1 formuli), ali nikako nisam uspeo da izguglam kako da dobijem npr. 3 maksimalne vrednosti od 4+ vrednosti...

_{[Ovu poruku je menjao Nextbtg dana 16.11.2010. u 20:06 GMT+1]}

Prikačeni fajlovi

Primer_Next.xls - 61.5k

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2286
*.wimax.verat.net.

Sajt: www.gowi.rs

+112 Profil

Re: [Excel] Kako da izvucem 3 maksimalne vrednosti iz niza + bojenje tih celija?

^{16.11.2010. u 18:03 - pre 175 meseci}

Koristiš funkcije LARGE, odnosno SMALL da dobiješ i-tu najveću, odnosno najmanju vrednost u opsegu.
Na osnovu vrednosti u koloni I gde si prebrojao koliko imaš ponuda predlažem da izračunaš tri rezultata
1/ Kolona J: Prosek od tri najniže cene.

Code:
=AVERAGE(SMALL(C2:H2;1);SMALL(C2:H2;2);SMALL(C2:H2;3))

2/ Kolona K: Druga najviša/najniža cena od ponuđenih (svejedno pošto se to računa za slučaj kad imaš 3 ponude)

Code:
=SMALL(C2:H2;2)

3/ Kolona L: Najveću vrednost od ponuđenih

Code:
=MAX(C2:H2)

Sada će konačan rezultat biti jedna od ove tri vrednosti zavisno od broja ponuda (kolone I)

Code:
=IF(I2>3;J2;IF(I2=3;K2;L2))

Naravno moguće je napraviti jednu MEGA forumulu koja sve ovo sadrži, ali ti preporučujem da ostaviš ovako i ne praviš složenu formulu jer će biti teška za proveru i ispravljanje..

Što se tiče "bojenja" koristiš slične funkcije samo u obrnutom smeru a u okviru uslovnog formatiranja. Definišeš tri pravila
a/ Ako je u istom redu u koloni I vrednost >= 3 AND ćelija koja se proverava <= trećoj najmanjoj vrednosti u tom redu (SMALL) AND ćelija koja se proverava nije prazna (<>0) - onda je oboj plavo
b/ Ako je u istom redu u koloni I vrednost = 3 AND ćelija koja se proverava = drugoj najmanjoj vrednosti u tom redu - oboj je ljubičasto
c/ Ako je u istom redu u koloni I vrednost < 3 AND ćelija koja se proverava = MAX - oboj je zeleno

Prilikom kreiranja ovih uslova bitno je razumeti i ispravno koristiti apsolutno/relativno adresiranje kako bi se jednom napisani uslovi mogli primeniti na ceo opseg.

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Prikačeni fajlovi

Primer_Next.xls - 71.5k

Odgovor na temu

ib.kroki
Beograd

Član broj: 188338
Poruke: 816
212.178.246.*

+37 Profil

Re: [Excel] Kako da izvucem 3 maksimalne vrednosti iz niza + bojenje tih celija?

^{16.11.2010. u 18:21 - pre 175 meseci}

@Nextbtg

Citat:

Nextbtg:
1. zelim da ukoliko za 1 proizvod imam vise od 3 cene u obzir uzmem 3 najniže cene i od njih izračunam prosečnu vrednost (10, 7 , 9, 4 , 6 , 8 = 9)

Navodiš da uzimaš najniže cene, a u primeru si uzeo najviše.
Proveri ...

Zvoni, kucanje ne radi ...

Odgovor na temu

Nextbtg
BTN
Beograd

Član broj: 169988
Poruke: 16
212.178.239.*

+4 Profil

Re: [Excel] Kako da izvucem 3 maksimalne vrednosti iz niza + bojenje tih celija?

^{16.11.2010. u 19:11 - pre 175 meseci}

@Jpeca
Hvala na detaljnom odgovoru, puno si mi pomogao!
Vidim da si obojio celije, al' ne vidim gde si ispisao formulu za bojenje (osim u tvom postu)...mada koliko sam neispavan mozda mi je i pred nosom, a ne vidim...Hvala jos jednom!

@ib.kroki
Prevideo sam, editovao sam post, sada je ok - hvala.

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2286
*.wimax.verat.net.

Sajt: www.gowi.rs

+112 Profil

Re: [Excel] Kako da izvucem 3 maksimalne vrednosti iz niza + bojenje tih celija?

^{16.11.2010. u 21:56 - pre 175 meseci}

U primeru možeš videti kako su postavljena pravila za bojenje ako otvoriš Conditional Formating (Uslovno formatiranje). U 2007 Home kartica -> Conditional Formating -> Manage Rules

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu