Projekt Internetová Kaviareň som si zvolil
práve preto, že v minulosti som často navštevoval bratislavské internetové
kaviarne. Bolo ich viacero, no nie v každej bola úroveň evidencie zákazníkov
na úrovni, hoci ich pracovníci mali k dispozícii osobné počítače. V jednom
prípade dokonca vypočítavali zákazníkom platbu na kalkulačke a údaje potom
zaznamenali práve do Excelu. Dnes, po nadobudnutí znalostí z tohto tabuľkového
kalkulátora som zjednodušenú evidenciu zákazníkov sám pripravil.
Opis jednotlivých listov
List Konštanty
Obsahuje hodnoty, ktorými sa riadi výpočet
v hlavnom liste Evidencia.
počet počítačov k dispozícii zákazníkom
– Koľko je počítačov určených pre zákazníkov na vyžívanie služieb Internetovej
Kaviarni. Od tohto údaja sa odvádza počet volných počítačov, prípadne varovanie,
že kapacita je vyčerpaná.
paušálna začiatočná platba
– Suma peňazí, ktorú zaplatí návštevník bez ohľadu na dobu trvania návštevy.
minútová sadzba
– Suma peňazí, ktorú zaplatí návštevník za každú minútu od zaevidovania
po odhlásenie u službukonajúceho pracovníka.
Kaviareň poskytuje vybraným zákazníkom
viaceré zvýhodnenia vo forme zliav na celkovej platbe.
uznávaná karta –
Držiteľ karty Euro<26, alebo špeciálnej Klubovej karty
má po preukázaní nárok na celkovú zľavu v objeme p% z celkovej platby.
zľava Z% pre každého N-tého zákazníka
– Aby firma prilákala nových zákazníkov poskytuje každému n-tému zľavu
z%. Tento údaj preto verejne prezentuje.
Pre zákazníkov, ktorí splňujú stanovené
kritérium, sú zaradení do nejakej zo skupín neplnoletý, študent, dôchodca.
Z tohto titulu im plynie zvýhodnenie vo forme p% zľavy z hrubej
platby.
List Evidencia
Ide o hlavný list projektu. Sem sa zapisujú
zákazníci, vypočítava veľkosť ich platby, počíta štatistika.
Opis zhora nadol.
Pri uvádzaní vzorcov používam anglické
názvy funkcií nakoľko Excel 97, v ktorom bol projekt vypracovaný, neobsahuje
v slovenskej mutácii preložené názvy funkcií.
Bunka B1. =NOW()
Obsahuje údaj aktuálnom systémovom čase,
podľa ktorého službukonajúci pracovník prihlasuje a odhlasuje zákazníkov.
Na aktualizáciu sa list prepočíta funkčnou klávesou F9.
Bunka B2. =IF(O61>0;O61;"!!!")
Funkcia využíva hodnotu bunky O61. Ak je
voľných počítačov viac ako 0, tak vráti ich počet, inak varovné výkričníky.
Bunka C2. =IF(O61>0;"počítačov volných";"POZOR,
VŠETKY POČÍTAČE OBSADENÉ")
Slúži ako doplnok výstupu vzorca z bunky
B2. Ak sú voľné počítače, tak k počtu z B2 doplní text, inak varovnú
správu.
Riadok číslo 4
obsahuje záhlavia stĺpcov.
Okno je horizontálne rozdelené tak, aby
prvé 4 riadky boli vždy viditeľné. Napomáha to orientácii. Je vidieť aktuálny
čas, počet voľných počítačov, ako aj záhlavia stĺpcov.
Ďalej budem menovať stĺpce podľa ich záhlavia
uvedeného vo 4. riadku a opisovať ich funkciu.
Záhlavia stĺpcov.
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|
Číslo
|
Meno
|
Priezvisko
|
typ zľavy
|
karta
|
príchod
|
Odchod
|
|
|
H
|
I
|
J
|
K
|
L
|
M
|
N
|
O
|
|
identif
|
Doba
|
Doba
v min. |
hruba
platba |
perc.zľava
z kategórie |
špec.
zľava |
celk.
zľava |
Čistá
platba |
|
Stĺpec Číslo. (konštanta)
Obsahuje poradové číslo zákazníka od začiatku
dňa. Nepredpokladá sa, že by bolo v dni zákazníkov viac ako 50, čo je konečná
hodnota tohto stĺpca.
Stĺpec Meno. (vstupný)
Prvé meno zákazníka.
Stĺpec Priezvisko. (vstupný)
Priezvisko zákazníka.
Stĺpec Typ zľavy. (vstupný)
Akceptuje sa jedno z písmen N, S, D, alebo
prázdna hodnota. Inak bude vrátená chyba v ďalších stĺpcoch daného riadku.
Písmená reprezentujú jednotlivý typ zľavy pre danú kategóriu. Viď tabuľku.
Tabuľka.
Ku každej kategórii prislúcha jej označenie
a zľava.
| Označenie
typu |
kategória |
zľava |
|
S
|
študent |
20%
|
|
N
|
neplnoletý |
30%
|
|
D
|
dôchodca |
10%
|
|
Stĺpec Identif. =IF(D5="";Konštanty!$B$24;VLOOKUP(D5;Konštanty!$A$21:$C$24;2;0))
Pre kontrolu vráti danú kategóriu reprezentujúcu
hodnotu zo stĺpca Typ zľavy.
V prípade, že je stĺpec Typ zľavy
prázdny, vráti hodnotu z bunky Konštanty!$B$24 znejúcu "- -". Znamená to,
že zľava sa neuplatňuje.
Ak stĺpec Typ zľavy obsahuje písmeno
reprezentujúce kategóriu zľavy, je použitá vyhľadávacia funkcia na uvedenie
tejto kategórie.
Stĺpec Karta. (vstupný)
Význam karty je uvedený vyššie. Vstup "1"
ak ju zákazník predloží, prázdna bunka ak nie.
Stĺpec Príchod. (vstupný)
Slúži na zaregistrovanie času príchodu
zákazníka.
Stĺpec Odchod. (vstupný)
Keď sa zákazník rozhodne ukončiť svoju
prácu, prípadne sa skončí otváracia doba, bude sem zapísaný čas jeho odchodu.
V oboch prípadoch je použitý časový formát
bunky.
Pri použití vhodného makra
by sa dala táto činnosť automatizovať. Počítač by doplniť čas aktuálny
na dané miesto. Projekt túto funkciu neobsahuje.
Stĺpec Doba. =IF(ISBLANK(G5);0;G5-F5)
V časovom formáte vypočíta dobu, akú zákazník
strávil za počítačom. V prípade, že ešte nebol odhlásený, vracia sa hodnota
0.
Ak informačná funkcia ISBLANK(odchod) zistí,
že odchod nie je uvedený, a
teda zákazník je ešte za počítačom vráti sa 0. Ak už čas odchodu
je k dispozícii, tak od neho odpočíta čas príchodu
a vráti dobu strávenú v kaviarni. Nepredpokladá sa polnočná prevádzka.
Stĺpec Doba v min. =HOUR(I5)*60+MINUTE(I5)
Pomocou časových funkcií a bunky zo stĺpca
Doba vypočíta čas v minútach,
potrebný pre ďalšie výpočty.
Stĺpec Hrubá platba. =IF(I5<>0;Konštanty!$B$7+Konštanty!$B$8*J5;0)
Ak sa Doba v min. nerovná nule,
keďže Doba sa tiež nerovná nule,
tak z listu Konštanty použije
hodnoty pre Paušálnu začiatočnú platbu, ku ktorej pripočíta súčin
Minútovej sadzby a Doby v min. Výsledok je Hrubá
platba.
Stĺpec Perc.zľava z kategórie. =VLOOKUP(H5;Konštanty!$B$21:$C$24;2;0)
Vyhľadávacia funkcia. Hľadá zo stĺpca Identif.
Kategóriu zľavy a z tabuľky v liste Konštanty vracia príslušnú
sadzbu. Percentový formát.
Stĺpec Špec. zľava. =IF(MOD(A5;Konštanty!$C$17)=0;K5*(Konštanty!$B$17);0)
Špeciálna zľava predstavuje zľavu Z% pre
každého N-tého zákazníka. Je použitá funkcia MOD(poradové
číslo). Ak je zvyšok po delení číslom
N nula, tak vypíše príslušnú sadzbu zľavy z listu Konštanty.
Ak tomu nie je tak, vráti nulu.
Stĺpce I až N, teda hodnoty: {Doba,
Doba v min., Hruba platba, Perc.zľava z kategórie, Špec. zľava, Celk. zľava}
Sú zoskupené, aby sa dali z dôvodu prehľadnosti schovať. Obsahujú
len čiastkové výpočty, ktoré nie je potrebné mať vždy na očiach.
Stĺpec Celk. Zľava. =(K5*L5)+M5
Výpočet zľavy, ktorá vyplýva zákazníkovi
z toho, že spadá do danej kategórie. Hrubá platba je prenásobená
Percentuálnou zľavou. K tomu je
pripočítaná Špec. zľava.
Stĺpec Čistá platba. =IF(E5;(K5-N5)*(1-Konštanty!$B$13);K5-N5)
Ide o výpočet koncovej čistej platby po
zohľadnení všetkých kritérií, ktorú zákazník platí. Ide o rozdiel
Hrubej platby a Celkovej zľavy.
V prípade, že zákazník je vlastníkom Karty,
tak je táto suma vynásobená doplnkom príslušného koeficientu zľavy do jednotky.
Bunka O56. =SUM(O5:O54)
Sumarizáciou Čistých platieb
jednotlivých zákazníkov sa spočíta denná tržba.
Bunka O58. =COUNT(F5:F54)
Celkový doterajší počet zákazníkov. Štatistická
funkcia vracia počet zadaných časov príchodov.
Bunka O59. =O58-COUNT(G5:G54)
Rozdielom Celkového počtu zákazníkov a
poctu zákazníkov ktorý už odišli (počet časov odchodov) zistíme počet zákazníkov
pracujúcich pri počítačoch.
Bunka O61. =Konštanty!B3-Evidencia!O59
Počet voľných počítačov zistíme
rozdielom Počtu počítačov a
Počtu zákazníkov pri počítačoch.
Oblasť od riadku 65 dole.
Zisťujem sumu peňazí, ktorú firma získala
od neplnoletých, študentov, dôchodcov a ostatných, ktorí nespĺňajú ani
jednu z týchto kategórií.
Nižšie sú zadané kritériá, ktoré sú použité
v riadku 65 na vlastné výpočty.
Použitá je databázová funkcia DSUM na spočítanie
údajov Čistá platba pri zákazníkoch
splňujúcich jednotlivé kritériá.
=DSUM($A$4:$O$54;15;B$69:B$70)
$A$4:$O$54 - oblasť celej databázy
15 - poradové číslo stĺpca Čistá platba
B$69:B$70 - kritéria pre jednotlivé kategórie
List Výsledky.
Obsahuje údaje uvedené a vypočítané v liste
Evidencia. Popísané sú vyššie.
List Graf - štruktúra tržieb.
Obsahuje koláčový graf, ktorý využíva výsledky
z listu Evidencia, kde boli
počítané sumy peňazí podľa jednotlivých kategórií.
List Zoradenie.
Údaje databázy sú radené podľa doby strávenej
za počítačom (Doba v min.) zostupne,
potom podľa Čistej platby zostupne. Tu vidieť, že niektorí platia
menej (dôsledkom zliav), hoci strávili dlhší čas v kaviarni.
List Filtrovanie.
Obsahuje dva filtráty hlavnej databázy. V prvom prípade sú vyfiltrovaní
Študenti, ktorí využili kartu.
V druhom prípade prešli filtrom zákazníci, ktorí platili presne, alebo
viac ako 200, alebo boli dôchodci.
Berú sa do úvahy tí, čo už zaplatili a odišli.
Záver
Týmto som stručne popísal môj projekt z
predmetu Informatika A: Internetová Kaviareň. Projekt si nekládol za úlohu
vyčerpávajúce vytvorenie plnofunkčného nástroja tohto typu, ide len o spôsob
demonštrovania využitia možností Excelu prostredníctvom použitých funkcií.
Ukážka okna projektu
|
Čas:
|
20:58
|
(F9)
pre aktualizáciu |
|
|
|
|
|
|
Stav:
|
7
|
počítačov
volných |
Internetová Kaviareň
|
|
| |
|
|
|
|
|
|
|
|
|
číslo
|
meno
|
priezvisko
|
typ zľavy
|
karta
|
príchod
|
odchod
|
identif
|
čistá
platba |
|
1
|
Ivana |
Mílová |
s
|
1
|
7:00
|
7:45
|
študent
|
58,00 Sk
|
|
2
|
Miroslav |
Roško |
d
|
|
7:05
|
8:30
|
dôchodca
|
166,50 Sk
|
|
3
|
Andrej |
Máte |
s
|
1
|
7:25
|
8:15
|
študent
|
60,00 Sk
|
|
4
|
Zuzana |
Dudášková |
d
|
|
7:30
|
10:30
|
dôchodca
|
252,00 Sk
|
|
5
|
Marek |
Buran |
d
|
|
7:44
|
9:20
|
dôchodca
|
176,40 Sk
|
|
6
|
Adam |
Viskup |
n
|
|
8:00
|
9:45
|
neplnoletý
|
143,50 Sk
|
|
7
|
Paulína |
Horňáková |
|
|
8:15
|
9:50
|
--
|
195,00 Sk
|
|
8
|
Peter |
Lovich |
n
|
1
|
8:22
|
10:00
|
neplnoletý
|
69,30 Sk
|
|
9
|
Daniela |
Rakovská |
|
|
8:30
|
9:45
|
--
|
175,00 Sk
|
|
10
|
Zuzana |
Humajová |
s
|
|
8:35
|
10:50
|
študent
|
129,25 Sk
|
|
11
|
Zuzana |
Dumanová |
|
|
8:44
|
9:30
|
--
|
146,00 Sk
|
|
