mlaco.sk
Naše cykloturistické výlety
- zážitky z ciest bicyklom doma i v zahraničí




Bicyklista na tento deň: Miso
Miso



Náhodný vtip:
Oficiálne vyhlásenie firmy Microsoft:
Kvôli drobným technickým problémom sa termín vydania nového operačného systému Windows 2000 odsúva na prvý kvartál roku 1901.

ďalší vtip

Vyber si kategóriu:
vseobecné, blondínky, počítače, sexuálne, ženy, škola, alkohol, hlody, múdrosti, zaujímavosti, všetky










Dnes je štvrtok, 28. marca 2024.  Meniny má Soňa


Projekt z Informatiky A. Excelovská evidencia zákazníkov vo fiktívnej Internetovej Kaviarni. Robí evidenciu, počíta platby a štatistiku, nejaký graf, filtrovanie, zoradenie, prehľady a podobné záležitosti.
12/1998

[ZIP]
35 kb

Internetová Kaviareň


Pozadie projektu

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 




www.mlaco.sk  ·  © Marek Laco 1998-2024  ·  mlaco@mlaco.sk