Ako odoslať s každou 10. objednávkou nad 100 eur darček?
Juraj má internetový obchod s nábytkom a rozhodol sa, že bude s každou 10. objednávkou, ktorej hodnota je vyššia ako 100 €, odosielať malý darček. Objednávok je dosť veľa a potrebuje „nejakú tabuľku“, kde by mal automaticky vypísané, ku ktorým objednávkam má pribaliť darček. Poskytol nám údaje o objednávkach od októbra do 17. novembra v tvare:
Navrhli sme nasledovnú tabuľku:
Stĺpec Poradie ľahko doplníme pomocou automatického napĺňanie hodnotami. Napr. napíšeme hodnoty 10, 20, označíme bunky s týmito hodnotami a ťaháme smerom nadol ľavým tlačidlom myši.
Do stĺpca Odoslané si budeme značiť, že objednávka s darčekom bola už odoslaná. Náročnejšie bude vytvoriť vzorec, ktorý vypíše ID objednávky s nárokom na darček. Tak poďme na to.
Najskôr si ošetríme, aby sa v prípade, ak ID objednávky s nárokom na darček neexistuje, zobrazila prázdna bunka. Riešením je funkcia IFERROR, kde druhý argument budú prázdne „“, aby nezobrazilo v prípade chyby nič.
=IFERROR(tu bude pokračovanie, ak existuje ID;““)
Na vypísanie ID použijeme funkciu INDEX.
=IFERROR(INDEX(pole;číslo_riadka;číslo_stĺpca);““)
Pole vieme, lebo zdrojové údaje sa nachádzajú v hárku údaje v stĺpcoch A až C. Nesmieme zabudnúť na absolútne adresy (použitie $).
=IFERROR(INDEX(údaje!$A:$C;číslo_riadka;číslo_stĺpca);““)
Číslo stĺpca je poradové číslo stĺpca v oblasti, z ktorej potrebujeme ID objednávok a to je prvý stĺpec, preto číslo stĺpca = 1.
=IFERROR(INDEX(údaje!$A:$C;číslo_riadka;1);““)
A teraz prichádza ťažšia časť…
Využijeme funkciu LARGE(pole;k). Ak je k počet údajových bodov v rozsahu, potom LARGE(pole;k) vráti najnižšiu hodnotu. A to je presne to, čo potrebujeme. Potrebujeme prechádzať všetky objednávky s hodnotou väčšou ako 100 a vracať naspäť každú desiatu v poradí.
pole … (údaje!$C:$C>100)*ROW(údaje!$C:$C)
k … COUNTIF(údaje!$C:$C;“>100″)-A4+1
A keďže ide o maticový vzorec, po jeho zadaní do bunky stlačíme CTRL+SHIFT+ENTER.
=IFERROR(INDEX(údaje!$A:$C;LARGE((údaje!$C:$C>100)*ROW(údaje!$C:$C);COUNTIF(údaje!$C:$C;“>100″)-A4+1);1);““)
Stiahnite si ukážkový súbor:
