popis: autor: kontakt: domovská stránka:
Excel Asistent Magazín 06/2003 - pojmenované oblasti a funkce Jiří Číhař
[email protected] www.dataspectrum.cz
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Přehled mzdy
Mzda Jana Karel Jitka Václav
2002 14 500 13 800 17 600 16 900
1. Vytvořit názvy potřebné pro využití ve výpočtech můžeme po vyznačení oblasti dat B6:E10 pomocí Vložit > Název > Vytvořit…
2003 2004 15 900 14 700 19 200 18 000
2. Pokud chceme mít větší kontrolu nad způsobem definování názvu použijeme postup pomocí Vložit > Název > Definovat ( vytvoříme oblast pojmenovanou Mzdy2003 obsahující buňky E7:E10)
do textového pole Názvy v sešitu zapíšeme název oblasti ( Mzdy2003) do textového pole Odkaz na: vložíme adresu oblasti( D7:D10) - stačí do pole umístit kurzor a pak aktivovat oblast přímo v listu 3. Vložený název se objeví v poli názvů vlevo od řádku vzorců
4. Pokud nyní potřebujeme znát součet platů v roce 2003, stačí zapsat: 67800 =SUMA(Mzdy2003)
Pozn. Definovat oblast lze i velmi jednoduše: Zvýrazněte oblast D7:D10 a zapište do pole názvů požadovaný název Mzdy2003
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Mzdy2003
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Potřebujeme vypočítat novou cenu zboží, ale nevíme přesně, o kolik procent bude cena navýšena sortiment Košile Kalhoty Kravata Klobouk
cena 610 1 200 280 560
nová cena 671 1 320 308 616
1. Definujme si pomocí Vložit > Název > Definovat konstantu s názvem KoeficientZměny a přiřďme ji nezávaznou hodnotu 1,1 ( zdražení o 10%)
2. Nyní zapíšeme do buňky D5 vzorec: =C4*KoeficientZměny a vzorec zkopírujeme směrem dolů 3. Pokud budeme potřebovat změnit procento navýšení, stačí změnit hodnotu pojmenované konstanty KoeficientZměny a výsledky veškerých vzorců jsou ihned změněny
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
VYTVOŘENÍ & POUŽITÍ DYNAMICKÝCH POJMENOVANÝCH OBLASTÍ ( testováno v prostředí Microsoft ExcelXP) tento dokument vznikl na Paula Ecklund Rodney Powell Jim Smith
základě materiálů autorů: Fuqua School of Business http://www.beyondtechnology.com/projrqst.shtml Excel Hack Extraordinaire
Otázka: Kdy můžeme použít dynamicky pojmenované oblasti? Odpověď: Kdykoliv ve svých řešeních používáme seznamy nebo oblasti dat, které nemají konstantní velikost, tj. může se zvětšovat nebo zmenšovat Otázka: V jakých typech úloh použijeme dynamicky pojmenované oblasti? Odpověď: Při vytváření seznamů v rámci activních prvků na listu, interakivních grafů, aktivních zdrojů dat pro kontingenční tabulky apod. V dalším textu si ukážeme postup vytváření těchto oblastí. Syntaxe vzorce je následující: =POSUN(List1!$A$1, 0, 0, POČET2($A:$A), 1) K čemu slouží funkce POSUN? Vrátí odkaz na oblast, která obsahuje určený počet řádků a sloupců, od určité buňky nebo oblasti buněk. Vrácený odkaz může být jedna buňka nebo oblast buněk. Počet řádků a sloupců, které se mají vrátit, můžete určit Funkce POSUN má 5 parametrů:. POSUN(odkaz;řádky;sloupce;výška;šířka) Odkaz je buňka, vůči které provádíme posun. Řádky a sloupce je počet řádků ( sloupců), o které se má posunout levá horní buňka nového odkazu (nahoru nebo dolů). Pro naše potřeby nabývá hodnoty 0. Výška je požadovaná výška (počet řádků) výsledného odkazu. Výška je vždy kladné číslo. POČET2 vrací počet buněk ve sloupci A, které obsahují data ( nejsou prázdné). Šířka je požadovaná šířka (počet sloupců) výsledného odkazu. Šířka je vždy kladné číslo. Pomocí VLOŽIT, NÁZEV, DEFINOVAT... zapíšeme název oblasti s využitím funkce POSUN
Pozn. dynamicky pojmenované oblasti se nezobrazují v poli názvů nalevo od řádku vzorců
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Vytvoření a použití dynamicky pojmenované oblasti: společně s ovládacím prvkem formuláře Dynamický seznam se nachází ve sloupci A. Název dynamické oblasti je SEZNAM_AUT. Příklad použití: 1. argument funkce POSUN: Počáteční buňka. V tomto případě A8. 2. argument: O kolik řádků má být počátek seznamu posunut od počátku. V našem případě bez posunu. 3. argument: O kolik sloupců má být počátek seznamu posunut od počátku. V našem případě bez posunu. Oblast má tedy počátek ( levý horní roh) v buňce: A8. 4. argument: Kolik řádků obsahuje oblast. POČET2 určí počet buněk obsahující hodnoty A:A znamená, že funkce hledá vyplněné buňky ve sloupci A. 5. argument: Kolik sloupců obsahuje oblast. V našem případě pouze 1 sloupec.
OPEL FORD CITROEN RENAULT SAAB VOLVO SKODA PEUGEOT AUDI BMW MERCEDES PORSCHE MAZDA HONDA
Použití dynamické pojmenované oblasti v ovládacím prvku formuláře. Princip dynamicky pojmenované oblasti pochopíme, pokud do seznamu ve sloupci A přidáme další položku. Nová položka se objeví v nabídce ovládacího prvku formuláče, který je vložen na list.
OPEL FORD CITROEN RENAULT SAAB VOLVO SKODA PEUGEOT
1
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Vytvoření a použití dynamicky pojmenované oblasti: společně s ovládacím prvkem Dynamický seznam se nachází ve sloupci A. Název dynamické oblasti je SEZNAM_AUT. Příklad použití: 1. argument funkce POSUN: Počáteční buňka. V tomto případě A8. 2. argument: O kolik řádků má být počátek seznamu posunut od počátku. V našem případě bez posunu. 3. argument: O kolik sloupců má být počátek seznamu posunut od počátku. V našem případě bez posunu. Oblast má tedy počátek ( levý horní roh) v buňce: A8. 4. argument: Kolik řádků obsahuje oblast. POČET2 určí počet buněk obsahující hodnoty A:A znamená, že funkce hledá vyplněné buňky ve sloupci A. 5. argument: Kolik sloupců obsahuje oblast. V našem případě pouze 1 sloupec.
OPEL FORD CITROEN RENAULT SAAB VOLVO SKODA PEUGEOT AUDI BMW MERCEDES PORSCHE MAZDA HONDA NISSAN
Použití dynamické pojmenované oblasti v ovládacím prvku formuláře.
OPEL FORD CITROEN RENAULT SAAB
FORD
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Vytvoření a použití dynamicky pojmenované oblasti: DYNAMICKÝ GRAF Seznam je ve sloupcích B a C. Název dynamicky pojmenované oblasti je PRODEJ.
28 32 44 39 58 40 22 19
Použijeme průvodce grafem bez vyplnění zdroje dat. V dalším kroku zadáme jako zdroj oblast PRODEJ. Dokončíme tvorbu grafu obvyklým způsobem.
70
0
60
Graf vytvořený z dynamicky pojmenované oblasti
50 40 30 20 10
Pokud chceme přidat nové položky do oblasti dat nemůžeme data vkládat za poslední záznam. Nová data musíme vložit mezi existující záznamy. Přidání dat za poslední záznam by nevedlo k překreslení grafu. Proto je do oblasti vložen pomocný, koncový záznam x=0. V následujícím listu je ukázáno řešení tohoto omezení. Definice dynamické oblasti o šířce 2 sloupce:
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
x
p en Sr
ec n ve
ve
n Č er
Č er
te n vě K
be n u D
B
ře
ze n
n or Ú
en
0 Le d
Leden Únor Březen Duben Květen Červen Červenec Srpen x
Vytvoření a použití dynamicky pojmenované oblasti: DYNAMICKÝ GRAF (2) Tento list popisuje způsob použití dynamicky pojmenované oblasti sloužící jako zdroj dat pro připojený graf. Postup zobrazený v tomto příkladu umožňuje přidat data za poslední záznam a tato data jsou automaticky zobrazena v připojeném grafu. Leden Únor Březen Duben Květen Červen Červenec Srpen
98 78 85 79 72 96,3 90 76
Postup 1. Pro data definujeme 2 samostatné dynamicky definované oblasti První oblast je pojmenována Období a druhá Skóre Oblasti jsou pojmenovány standardním způsobem - Vložit,Název,Definovat & použití funkcí POSUN a POČET2 2. Vytvoříme spojnicový graf založený na našich datech. 3. V grafu změníme nastavení na kartě Řada do položky Hodnoty zapíšeme odkaz na oblast Skóre a do položky Popisky osy X odkaz na oblast Období 4. Pokud nyní zapíšeme nové hodnoty za poslední záznam, graf bude změněn.
100
Graf vytvořený z dynamicky pojmenované oblasti (nová data lze vložit za poslední záznam)
95 90 85 80 75 70 65 60 Leden
Únor
Březen
Duben
Květen
Červen
Červenec
Srpen
Postup - názorné zobrazení 1. Pro data definujeme 2 samostatné dynamicky definované oblasti První oblast je pojmenována Období a druhá Skóre Oblasti jsou pojmenovány standardním způsobem - Vložit,Název,Definovat & použití funkcí POSUN a POČET2
2. Vytvoříme spojnicový graf založený na našich datech.
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
3. V grafu změníme nastavení na kartě Řada a) klikneme na graf
b) změníme odkaz v řádku vzorců
tak, aby měl následující podobu
c) tato změna se projeví v položkách Hodnoty a Popisky osy X na kartě Řada
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
DYNAMICKY PROMĚNNÝ ROZSAH GRAFU (tento příklad ukazuje způsob změny rozsahu grafu pomocí dynamicky pojmenované oblasti a funkce Ověření vstupních dat) Měsíc Hodnoty leden 02 557 únor 02 758 březen 02 377 duben 02 753 květen 02 887 červen 02 504 červenec 02 804 srpen 02 312 září 02 377 říjen 02 1000 listopad 02 218 prosinec 02 426
Graf ukončit v měsící: září 02
kliknutím aktivujeme seznam
Graf s dynamicky proměnným rozsahem 1200 1000 800 600 400 200 0 I-02
II-02
III-02
IV-02
V-02
VI-02
VII-02 VIII-02
IX-02
Postup 1. Změna rozsahu grafu je řízena pomocí obsahu buňky D4 2. Tato buňka je propojena s Ověřováním dat ( Data > Ověření ), pomocí něhož Excel kontroluje, zda obsah buňky odpovídá některé z hodnot zadaného seznamu. Hodnoty seznamu odpovídají obsahu buněk oblasti A4:A15 3. Vytvoříme sloupcový graf založený na našich datech. 4. Vytvoříme pomocí Vložit > Název > Definovat… dynamicky pojmenovanou oblast Měsíce. Měsíce =POSUN(Seznam5!$A$4;0;0;POZVYHLEDAT(Seznam5!$D$4;Seznam5!$A$4:$A$15;0);1) 5. Nyní "naučíme" graf omezit počet znázorněných měsíců podle obsahu buňky D4: Do položky Popisky osy X karty Řady vložíme odkaz na pojmenovanou funkci Měsíce.
Postup - názorné zobrazení 1. Změna rozsahu grafu je řízena pomocí obsahu buňky D4 2. Tato buňka je propojena s Ověřováním dat, pomocí něhož Excel kontroluje, zda obsah buňky odpovídá některé z hodnot zadaného seznamu. Hodnoty seznamu odpovídají obsahu buněk oblasti A4:A15
3. Vytvoříme sloupcový graf založený na našich datech. 4. Vytvoříme pomocí Vložit > Název > Definovat… dynamicky pojmenovanou oblast Měsíce. Měsíce protože tato hodnota je v seznamu měsíců na 4. místě
Pojmenovaná funkce Měsíce vytvoří oblast:: s počátkem v buňce obsažené v 1. argumentu: Seznam5!$A$4 obsahující tolik buněk, kolik určuje hodnota vrácená funkcí POZVYHLEDAT(Seznam5!$D$4;Seznam5!$A$4:$A$15;0)
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
počet sloupců oblasti určuje poslední argument funkce Měsíce, tedy v našem případě: 1 Jak pracuje funkce ve 4. argumentu? Vyhledá položku načtenou z buňky D4 v oblasti A4:A15 a vrátí pořadové číslo této hodnoty. Pokud např. je v buňce A4 hodnota duben 02, pak funkce vrátí číslo 4, protože tato hodnota je v seznamu měsíců na 4. místě. 5. Nyní "naučíme" graf omezit počet znázorněných měsíců podle obsahu buňky D4: Do položky Popisky osy X karty Řady vložíme odkaz na pojmenovanou funkci Měsíce. Jak postupovat? a) Klikneme ukazatelem myši na některý ze sloupců grafu:
b) Pokud jsme založili graf na vzorových datech, nalezneme v řádku vzorců následující vzorec:
c) Změníme 2. argument funkce SADA, který odkazuje na hodnoty zobrazované na ose X : Seznam5!$A$4:$A$15 přepíšeme v řádku vzorců na Seznam5!Měsíce d) Změnu potvrdíme stiskem klávesy ENTER e) Tento krok je již pouze kontrolní: Pokud klikneme pravou klávesou myši na graf a z místního menu zadáme Zdrojová data nalezneme zadaný odkaz na dynamicky pojmenovanou oblast Měsíce v položce Popisky osy X
Zcela na závěr k rekapitulaci: Pro potřeby tohoto příkladu jsme vytvořili pojmenovanou oblast: Měsíce =POSUN(Seznam5!$A$4;0;0;POZVYHLEDAT(Seznam5!$D$4;Seznam5!$A$4:$A$15;0);1)
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
nam5!$A$4:$A$15;0)
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
DYNAMICKY PROMĚNNÝ ROZSAH GRAFU(2) (tento příklad rozšiřuje možnosti předchozího grafu přidání nebo odebrání záznamů v oblasti dat je automaticky promítáno do nabídky) Měsíc leden únor březen duben květen červen červenec srpen září říjen listopad prosinec leden únor
02 02 02 02 02 02 02 02 02 02 02 02 03 03
Hodnoty 557 758 377 753 887 504 804 312 377 1000 218 400 768 660
Graf ukončit v měsící: září 02
kliknutím aktivujeme seznam
Graf s dynamicky proměnným rozsahem 1200 1000 800 600 400 200 0 I-02
II-02
III-02
IV-02
V-02
VI-02
VII-02
VIII-02
IX-02
Tento příklad významně rozšiřuje funkčnost předchozího grafu tím, že umožňuje automatickou změnu seznamu pro výběr posledníhího měsíce grafu po změně vstupní oblasti dat Popsaný postup již popisuje pouze změny proti předchozímu příkladu. V tomto příkladu jsou použity následující dynamicky pojmenované oblasti: Měsíce2 =POSUN(Seznam6!$A$4;0;0;POZVYHLEDAT(Seznam6!$D$4;Měsíce_Seznam;0);1) Měsíce_Seznam =POSUN(Seznam6!$A$4;0;0;POČET2(Seznam6!$A:$A);1) Hodnoty_Seznam =POSUN(Seznam6!$A$4;0;1;POČET2(Seznam6!$A:$A)-1;1)
Měsíce2 Oblast Měsíce2 odpovídá oblasti Měsíce v předchozím příkladu jediný rozdíl mezi nimi je v příslušnosti k odlišným listům Měsíce je oblast listu Seznam5, Měsíce2 je oblast listu Seznam6 Měsíce_Seznam Měsíce_Seznam je pojmenovaná oblast odkazující na oblast měsíců dynamicky měnící svůj rozsah podle toho, zda přidáváme nebo odebíráme záznamy Tuto oblast vložíme jako zdroj dat pro buňku D4 prostřednictvím Ověření (Data > Ověření ) dat
Hodnoty_Seznam
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Hodnoty_Seznam je pojmenovaná oblast odkazující na oblast hodnot dynamicky měnící svůj rozsah podle toho, zda přidáváme nebo odebíráme záznamy Vidíme, že je však ukotvena k první buňce oblasti dat ( v našem příkladu hodnota leden 02) =POSUN(Seznam6!$A$4;0;1;POČET2(Seznam6!$A:$A)-1;1) Protože však 3. argument funkce POSUN je hodnota 1, znamená to, že oblast je posunuta o jeden sloupec doprava, tedy do oblasti hodnot
Pro docílení požadované dynamiky musíme změnit odkazy na zdroj dat pro graf:
Jak postupovat? a) Klikneme ukazatelem myši na některý ze sloupců grafu: b) Změníme argumenty funkce SADA tak, aby odpovídali následujícímu vzoru =SADA(;ExcelAsistenMagazín06.xls!Měsíce2;ExcelAsistenMagazín06.xls!Hodnoty_Seznam;1) c) Změnu potvrdíme stiskem klávesy ENTER
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Vytvoření a použití dynamicky pojmenované oblasti: KONTINGENČNÍ TABULKA a GRAF Zdrojová data pro kontingenční tabulku se nacházejí ve sloupcích B:E Dynamicky pojmenovaná oblast obsahující tato data má název Tabulka_Prodeje
Obchod Baumax Ikea Globus Delvita
Datum 1.6.2003 4.6.2003 5.6.2003 9.6.2003
Produkt Produkt 1 Produkt 2 Produkt 18 Produkt 139
Cena 1 509,42 1 491,83 2 842,00 1 953,36
Postup 1) Definujeme 4-sloupcovou dynamicky pojmenovanou oblast
2) Vytvoříme kontingenční tabulku nad definovanou oblastí Datum Produkt
(Vše) (Vše)
Součet z Cena Obchod Celkem Baumax 1509,42 Delvita 1953,36 Globus 2842 Ikea 1491,83 Celkový součet 7796,61 3) Pokud do oblasti dat přidáme nový záznam, kontingenční tabulka je automaticky aktualizována. Musíme však zadat přkaz Aktualizovat data ( kliknutí pravým tlačítkem myši v oblasti kontingenční tabulky):
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Příjem větší než... ...a nižší než sazba Výše daně 0 2 500 0% 0 2 501 5 000 1% 25 5 001 20 000 3% 475 20 001 35 000 7% 1 525 35 001 50 000 13% 3 475 50 001 70 000 19% 7 275 70 001 100 000 24% 14 475 100 001 250 000 27% 54 975 >250 001 28%
Zadání: Tabulka ukazuje sazbu daně. Úkolem není nalézt jen hodnotu sazby daně ze zadané výše příjmu, ale přímo určit výši daňové povinnosti. Ze zadané výše příjmu a tabulky sazeb daně tedy máme určit částku, kterou musíme zaslat finančnímu úřadu. Př: Pokud je roční příjem 40 000, pak musíme zaplatit 2 175. Tj. 1 525 + 5 000x13% = 1 525 + 650 = 2 175 Při řešení se chceme vyhnout použití VBA - cílem je nalézt funkci, která by vracela hodnotu daně přímo v listu.
Příklad výpočtu výše daně pomocí vyhledávacích funkcí http://www.dataspectrum.cz
[email protected]
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Příjem větší než... ...a nižší než sazba Výše daně 0 2 500 0% 0 2 501 5 000 1% 25 5 001 20 000 3% 475 20 001 35 000 7% 1 525 35 001 50 000 13% 3 475 50 001 70 000 19% 7 275 70 001 100 000 24% 14 475 100 001 250 000 27% 54 975 >250 001 28%
zadejte výši příjmu: vypočtená výše daně:
40 000 2 175
=SVYHLEDAT(G1;$B$2:$D$9;3)+POSUN(B2;POZVYHLEDAT(SVYHLEDAT(G1;$B$2:$D $9;1);$B$2:$B$9);1)*(G1-SVYHLEDAT(G1;$B$2:$D$9;1))
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Zadání: Dobrý den, potřebuji při své práci rychle vybírat hodnoty z ceníků různých druhů žaluzií zadáním šířky a výšky. Ceníky mají v jednom vrchním řádku obvykle číslo určující šířku a v jednom sloupci výšku. Pokud jsou hodnoty šířky a výšky 100,200,300,.....2000,2100 atd. tak jsem si pomocí funkcí ZAOKROUHLIT NAHORU, SVYHLEDAT a POZVYHLEDAT problém vyřešil a aplikace mě spolehlivě fungují. Teď však mám ceník, kde výrobce kvůli prořezu má v řádku šířka např čísla 255, 325, 420, 510 atd. To neumím zaokrouhlit, tím neumím určit který sloupec (a někdy, který řádek) chci vybrat a jsem v koncích. Podstatné je, že když si například někdo objedná žaluzii 420,1 cm širokou, tak už musím určovat cenu za sloupce 515. Předem děkuji. Věřím, že to nebude zajímat jen mne. Mgr. Rudolf Vágo,
Šířka (m)
310
420
515
635
710
820
960
1150
Výška (m)
300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200
260
277
296
312
350
368
385
410
272
291
310
327
371
389
408
432
280
299
322
342
385
405
428
455
293
313
333
360
405
427
452
478
303
327
354
379
427
453
478
501
315
341
367
395
444
471
499
524
326
355
385
412
466
494
524
546
338
369
401
430
477
516
545
570
349
381
414
447
493
538
566
592
362
395
430
466
510
557
587
616
372
409
447
482
525
577
607
639
383
422
459
498
543
594
628
661
392
433
476
515
558
612
648
684
406
449
492
534
573
632
669
708
416
459
505
549
590
648
690
731
427
474
521
563
607
667
710
754
437
485
531
577
623
688
730
777
443
493
543
590
639
704
754
799
454
503
554
604
654
723
773
821
461
513
565
618
670
740
793
846
Využití vyhledávacích funkcí pro zjištění hodnoty závislé na 2 parametrech http://www.dataspectrum.cz
[email protected]
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
310
Šířka (m)
420
515
635
710
820
960
1150
Výška (m)
300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200
260
277
296
312
350
368
385
410
272
291
310
327
371
389
408
432
280
299
322
342
385
405
428
455
293
313
333
360
405
427
452
478
303
327
354
379
427
453
478
501
315
341
367
395
444
471
499
524
326
355
385
412
466
494
524
546
338
369
401
430
477
516
545
570
349
381
414
447
493
538
566
592
362
395
430
466
510
557
587
616
372
409
447
482
525
577
607
639
383
422
459
498
543
594
628
661
392
433
476
515
558
612
648
684
406
449
492
534
573
632
669
708
416
459
505
549
590
648
690
731
427
474
521
563
607
667
710
754
437
485
531
577
623
688
730
777
443
493
543
590
639
704
754
799
454
503
554
604
654
723
773
821
461
513
565
618
670
740
793
846
šířka: výška:
500 1 050
při shodě zvolím VĚTŠÍ hodnotu
VSTUP
použiji sloupec: použiji řádek: cena:
515 1100 414
při shodě zvolím MENŠÍ hodnotu
použiji sloupec: použiji řádek: cena:
515 1000 401
- vzorce pro výběr sloupce a řádku jsou maticové ( CTRL+SHIFT+ENTER) - obě řešení ( volba VĚTŠÍ / MENŠÍ ) hodnoty se liší pouze použitím vnější funkce MAX / MIN - nedostatkem tohoto řešení je, že neumí zpracovat vstupní údaje, které překračují mezní hodnoty
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
310
Šířka (m)
420
515
635
710
820
960
1150
Výška (m)
300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200
260
277
296
312
350
368
385
410
272
291
310
327
371
389
408
432
280
299
322
342
385
405
428
455
293
313
333
360
405
427
452
478
303
327
354
379
427
453
478
501
315
341
367
395
444
471
499
524
326
355
385
412
466
494
524
546
338
369
401
430
477
516
545
570
349
381
414
447
493
538
566
592
362
395
430
466
510
557
587
616
372
409
447
482
525
577
607
639
383
422
459
498
543
594
628
661
392
433
476
515
558
612
648
684
406
449
492
534
573
632
669
708
416
459
505
549
590
648
690
731
427
474
521
563
607
667
710
754
437
485
531
577
623
688
730
777
443
493
543
590
639
704
754
799
454
503
554
604
654
723
773
821
461
513
565
618
670
740
793
846
volím vždy VĚTŠÍ hodnotu
šířka: výška:
420,10 2 400,00
VSTUP
použiji sloupec: použiji řádek: cena:
515 2200 565
- vzorce pro výběr sloupce a řádku jsou maticové ( CTRL+SHIFT+ENTER) - řešení vrací i správné hodnoty při překročení mezí
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz
Standart
Šířka (mm)
310
420
515
635
710
820
960
1150
Výška (mm)
Požadavek
310
420
515
635
710
715
960
1150
300
138
260
277
296
312
350
368
385
410
400
400
272
291
310
327
371
389
408
432
500
500
280
299
322
342
385
405
428
455
600
600
293
313
333
360
405
427
452
478
700
700
303
327
354
379
427
453
478
501
800
800
315
341
367
395
444
471
499
524
900
900
326
355
385
412
466
494
524
546
1000
1000
338
369
401
430
477
516
545
570
1100
1100
349
381
414
447
493
538
566
592
1200
1200
362
395
430
466
510
557
587
616
1300
1300
372
409
447
482
525
577
607
639
1400
1400
383
422
459
498
543
594
628
661
1500
1500
392
433
476
515
558
612
648
684
Výška (mm) Šířka (mm) cena
138 715 368 Kč
Toto řešení úlohy z EAM 05/2003 zaslal p. Miroslav Kral [
[email protected]] Velmi mne zaujal jeho přístup založený na vytvoření pomocných oblastí pro určení šířky a výšky - proto jej nabízím čtenářům jako inspiraci k řešení obdobných úloh.
Excel Asistent 06/2003 Jiří Číhař, Dataspectrum http://www.dataspectrum.cz