Excel - zlozita funkcia (pre expertov)

it123:
Mám (zatím) dva dotazy:
(1) Počet mužstev je stále stejný?
(2) Co smím použít? Pouze funkce z Microsoft Excelu nebo mohu programovat i makra?

Není tam něco jako rank(sloupec1-sloupec2)?

Já zatím dokáži najít řádek, kde je hodnota rozdílu nejvyšší. Ovšem nedaří se mi následně vypsat jméno klubu, u kterého tato skutečnost nastala.

Mistr:
„Já zatím dokáži najít řádek, kde je hodnota rozdílu nejvyšší. Ovšem nedaří se mi následně vypsat jméno klubu, u kterého tato skutečnost nastala“

Já jsem na OpenOffice.org (Calc), ale pokud jsi našel řádek, kde je hodnota rozdílu nejvyšší, pak by jsi to celé mohl zkusit vložit do INDEX MATCH konstrukce:

INDEX(A1:A3;MATCH(B3;B1:B3;0))

Zkusit dát to na místo B slopců

A1:A3 - výběr sloupce s názvy klubů
Místo B použít výsledek z tvé funkce

it123:
Koľko je tam asi tak riadkov?

it123:
Tak to mám, ale řešení je - pravda - dosti "humpolácké" (nic proti Hliníkově rodišti z filmu Marečku, podejte mi pero).

Mám tabulku o čtyřech řádcích a třech sloupečcích. Počet řádků lze samozřejmě poupravit, ale pro příklad ty čtyři stačí.

V prvním sloupečku je název týmu, v dalším počet vstřelených branek a v posledním počet branek inkasovaných. Tedy tak, jak znělo zadání.

Tabulku jsem si v Excelu vytvořil v buňkách A1 až C4.

Využívám nejprve excelovské funkce MAX pro určení maximální hodnoty:
=MAX(B1-C1;B2-C2;B3-C3;B4-C4)

A následně využívám funkce KDYŽ, tedy když je podmínka splněna, vypiš či udělej cosi, není-li podmínka splněna, vypiš nebo proveď něco jiného.

Spojím tyto funkce dohromady na principu je-li rozdíl vstřelených a obdržených branek prvního mužstva roven maximu, vypiš do buňky XY jméno tohoto mužstva; není-li, pak porovnej rozdíl vstřelených a obdržených branek u druhého mužstva atd. Výsledný vzorec pak má podobu (nakopíruj si jej do buňky, v níž chceš mít výsledek, tedy jméno nejlepšího mužstva):
=KDYŽ(B1-C1=MAX(B1-C1;B2-C2;B3-C3;B4-C4);A1;KDYŽ(B2-C2=MAX(B1-C1;B2-C2;B3-C3;B4-C4);A2;KDYŽ(B3-C3=MAX(B1-C1;B2-C2;B3-C3;B4-C4);A3;KDYŽ(B4-C4=MAX(B1-C1;B2-C2;B3-C3;B4-C4);A4;0))))

A teď už si jen hraj s počty vstřelených a inkasovaných branek a uvidíš, jak se Ti bude jméno nejlepšího mužstva měnit.

Pokud bude mužstev více, musíš si tenhle dlouhatánský vzorec upravit a ještě více jej rozšířit.


A teď, až se vzbudí kolega Chamurappi, tak asi nebude mít radost, že tady řešíme Excel. :-)

Mistr:
„Tak to mám, ale řešení je - pravda - dosti "humpolácké"“
Vzhledem k tomu, že bych řekl, že zadání byl něco jako domácí úkol z informatiky, by profesor nebyl pravděpodobně s tímto spokojen... Čistě z mého pohledu je tohle dost nepraktické, (což zajisté chápeš), protože pokud bych chtěl přidávat a odebírat řádky, musel bych vždy měnit řetězec pro výpočet. Taky kdyby bylo řádků několik desítek až stovek, tak by to nebyla zrovna primitivní podmínka.

Ostatně ale nevím, proč to vůbec píšu, když to nejsem schopný vyřešit. (minimálně ne v tuto hodinu).

it123:
„Inak, kebyze mozem pouzit pomocny stlpec Nastrielane goly minus Inkasove goly, tak to viem spravit.“
Možná by nám pomohlo, kdybychom věděli, jak to udělat s tím pomocným sloupcem. Mě totiž nenapadá ani to.

Mistr:
„A teď, až se vzbudí kolega Chamurappi, tak asi nebude mít radost, že tady řešíme Excel. :-)“
Excel je taky databáze, ne? :)

Amunak:
„Taky kdyby bylo řádků několik desítek až stovek, tak by to nebyla zrovna primitivní podmínka.“
Mám dojem, že počet vnořených podmínek je v Excelu omezen na šest.

Pokud by v excelu existovala nějaké funkce, která by dokázala odkazovat na pomocí vzorce vytvořenou oblast, pak by se to dalo zjednodušit a bylo by to možné bez rozšiřování použít na více řádků:

=INDEX(A1:A3;MATCH(MAX(B1-C1;B2-C2;B3-C3);xxx;0))

A1:A3 - sloupec pro výběr mužstva
MAX(B1-C1;B2-C2;B3-C3) - získání nejvyššího rozdílu
xxx - oblast, ze které se má vybírat podle výše uvedeného kritéria - pokud by byla nějaká funkce, která by vytvořila oblast za pomocí rozdílu (B1-C1 = x), (B2-C2 = y), (B3-C3 = z), pak by se porovnávaly hodnoty MATCH(MAX) s oblastí x,y,z

Příklad:
Slovan 10 5
Praha 5 2
Brno 20 1

vyber mužstvo - INDEX (Slovan, Praha, Brno)
maximální rozdíl MAX(B1-C1;B2-C2;B3-C3) (19 u Brna)
xxx - řádky s jednotlivými rozdíly: 1. řádek - 5, 2. řádek - 3, 3. řádek - 19 (něco jako VYTVOŘ OBLAST(B1-C1;B2-C2;B3-C3)

s tím, že by MATCH porovnával 19 ve vytvořené oblasti s hodnotami 5,3,19 a INDEX už jen přiřadí název mužsta u vybraného řádku.


Mistr
třeba to Chamurappi přehlídne :-)

Bubák:
„počet vnořených podmínek je v Excelu omezen na šest.“
Tím spíš by to bylo docela omezující :)

Amunak:
Možná by nám pomohlo, kdybychom věděli, jak to udělat s tím pomocným sloupcem. Mě totiž nenapadá ani to.

Způsobů může být hodně, např.



Vzorec pro sloupec D:
(B1-C1)

Vzorec pro výběr mužstva:
=INDEX(A1:A3;MATCH(MAX(D1:D3);D1:D3;0))

a tipuju, že by to mohlo být ještě jednoduší

Ak je v sú prvom riadku popisky stĺpcov, v prvom stĺpci názvy tímov, v druhom stĺpci strelené góly, v treťom stĺpci dostaté góly, tak pre 100 družstiev je vzorec:

Treba ho zadať ako maticový vzorec, teda CTRL+SHIFT+ENTER.
V českom Exceli sú možno názvy funkcií iné.

it123:
Ak to je pre niekoho iného, tak sa aspoň priznaj, že si na to neprišiel sám.

Mistr:
Na taký škaredý vzorec zneužiť moderátorské farbičky :-)

_es:
Treba ho zadať ako maticový vzorec, teda CTRL+SHIFT+ENTER
Tak to je to, co mě celou dobu unikalo :-(
{=INDEX(A1:A3;MATCH(MAX(B1:B3-C1:C3);B1:B3-C1:C3;0))}

díky

it123:
„A vies mi vysvetlit, preco to musi byt ako maticovy vzorec?“

Maticový vzorec môže pracovať s celými oblasťami aj tam, kde to bežné vzorce alebo funkcie nepodporujú a miesto celej oblasti by pracovali len s jednou bunkou alebo vypísali chybu.

velice zajímavé, jen doplním, že funkce MATCH se v českém excelu jmenuje POZVYHLEDAT jinak to nepůjde.

SwimX:
V Exceli, a hlavne v českej verzii, to s tou lokalizáciou trochu prehnali.

_es:
„V Exceli, a hlavne v českej verzii, to s tou lokalizáciou trochu prehnali.“
Já si to radši předělal zpátky na anglické názvy, protože to co tam bylo česky byl děs a hrůza...

Amunak:
„zadání byl něco jako domácí úkol z informatiky“
Ano, také mne to napadlo.

„je tohle dost nepraktické, (což zajisté chápeš), protože pokud bych chtěl přidávat a odebírat řádky, musel bych vždy měnit řetězec pro výpočet. Taky kdyby bylo řádků několik desítek až stovek, tak by to nebyla zrovna primitivní podmínka.“
Ano, i s touto Tvou poznámkou souhlasím. Však jsem psal, že se jedná o řešení poněkud humpolácké.

Bubák:
„Mám dojem, že počet vnořených podmínek je v Excelu omezen na šest.“
To je možné, nevím.

_es:
Na tu funkci INDEX ve spolupráci s maticí jsem také pomýšlel, leč neuměl jsem ji správně nastavit. Jsi holt šikovnější.

it123:
Dakujem Vam vsetkym za pomoc.
Pomohli ste mi velmi.
Nemáš zač.


„Moderátor Chamurappi: To pochybuji :-)“
Tušil jsem to. :-)

Aby to nevyzeralo, že je táto téma mimo fóra, tak sa to dá riešiť aj SQL dotazom:
Vytvoriť nový ODBC databázový dotaz na ten istý súbor, kde výsledkom bude len jedna hodnota.
Určite sa to dá, no nechce sa mi písať presný postup.

_es:
Dalo by se na to napsat i makro (v jazyce Visual Basic).

Mistr:
a bylo by to nejjednodušší, jen si vzpomenout na sytaxi.. :D

Mistr:
„napsat i makro“

Makrá sú obvykle oveľa pomalšie ako vzorce.

Ne obvykle, vždycky. Což je přirozené, protože VBA je interpretovaný jazyk.