Ajutor cu o gestiune de tip hotelier, va rog!

[mogastar]

Salut!
Am si eu nevoie sa creez un fisier in excel care sa poata sa imi "spuna" cat are de plata un anume client sa un anumit hotel.
Astfel datele care se introduc sunt : numele persoanei, hotelul la care a fost cazat si perioada de cazare zi cu zi, firma de la care provine cazatul. Pentru perioada de cazare trebuie facuta diferenta dintre 2 perioade ( luni, marti, miercuri, joi - ca zile lucratoare si vineri,sambata, duminica - ca zile de week-end) pentru a se sti intotdeauna daca plata se face de companie sau de catre client, dupa caz.
Eu am incercat sa fac ceva de genul dar nu am reusit decat pana in punctul de a afla cat are persoana "x" de plata la un anumit hotel. Mie mi-ar trebui sa stiu si dupa compania de la care vine, astfel incat sa pot sa am un total pe companie direct si eventual impartit pe cele doua 2 perioade de cazare.
Am atasat fisierul de care am spus anterior.
Daca are cineva vreo idee sau vreo sugestie i-as fi recunoscator.

Va multumesc anticipat!
Toate cele bune si un an nou plin de prosperitate!

[IPP]

Buna seara

La modul cum aveti organizate datele in acest moment cred ca singurul avantaj este acela ca puteti vedea mai usor ce camere sunt ocupate si cand. Daca vorbim de extragere de informatii / calcule….

In fisierul atasat gasiti o propunere de aranjare a datelor (vedeti foaia Baza) si afisare a rezultatelor folosind instrumentul Table, pivot table (pentru asta vedeti foaia PT) si niste formule de extragere a informatiei.

Formulele respective se gasesc in foaia “Baza”.
Pe coloana D se afla o formula care verifica in ce zi a saptamanii cade data calendaristica aflata pe acelasi rand si returneaza “Frm” – firma daca rezultatul e 1,2,3 sau 4 (corespunzator pentru zilele, Luni, Marti, Miercuri, Joi si “PF” – persoana fizica daca rezultatul este 5,6 sau 7 (corespunzator pentru zilele Vineri, Sambata sau Duminica).
Pe coloana G se afla o formula de cautare (vlookup) care are menirea de a extrage costul camerei (single) in functie de Hotel. Sursa se afla in foaia “Hrates”

Teoretic, cel putin, pentru o singura luna, o singura foaie ar putea adaposti intreaga informatie de care aveti nevoie avand avantajul totalizarilor usoare dupa diverse criterii.

Probabil problema dvs. este mai complexa (ma gandesc ca aveti si camere "duble" sau "apartament" cu alte tarife, de ex.) insa propunerea mea se refera strict la ceea ce ati atasat.

IP
PS. Pe viitor incercati sa deschideti subiectul intr-una dintre sectiunile dedicate care, in cazul dvs. ar putea fi Excel 2007 sau Excel 2010

[gecs]

Solutia din fisierul atasat e asemanatoare cu cea furnizata de IPP, dar foloseste o structurare a datelor pe cate doua tabele pentru fiecare problema (hoteluri si ocupare). Intr-un hotel exista mai multe camere, iar o persoana poate avea mai multe sejururi la unul sau mai multe dintre hotelurile inregistrate de aplicatie - din aceste considerente e bine ca datele despre hoteluri/camere si ocupanti/ocupare sa fie in tabele diferite, relationate one-to-many. Relatiile pot fi instituite in Excel doar prin interogari in care din cele doua (sau mai multe) tabele se creaza unul singur, prezentat apoi sub ce forma se doreste: Table sau PivotTable.

Pentru ca solutia din fisierul atasat sa functioneze, trebuie modificat Connection string-ul celor doua interogari create cu MS Query pe baza carora sunt realizare cele doua PivotTable-uri din foile Central_Occup si Central_Rates. Aceste modificari trebuie facute in functie de folderul in care e salvat fisierul atasat (calea catre fisier trebuie modificata) in dialogul Connection Properties (activata foaia cu PT-ul -> Data -> Connections -> Properties -> Definition):

Conn_Prop.jpg

Orice modificare, adaugare/stergere de inregistrare in cele 4 tabele care formeaza baza de date (Occupants, Occupancy, Hotels si Rooms) se va reflecta in cele doua PT-uri in urma unui Refresh (fie de la tab-ul Data, fie de la tab-ul Options al PivotTable Tools), dar numai dupa ce fisierul a fost salvat.

PT-ul din foaia Central_Occup are 3 campuri in zona Report Filter (Hotel, Years, Months) din care se pot filtra dalete din PT. In afara de asta, prezenta campului Hotel in aceasta zona permite realizarea cate unui PT similar (in foaie deparata) pentru fiecare hotel in parte pentru anul, luna si ziua selectate din celelalte filtre. Pentru asta se activeaza foaia Central_Occup si de la tab-ul Options al PivotTable Tools -> grupul PivotTable -> butonul Options -> Show Report Filter Pages... -> in dialogul cu acelasi nume se selecteaza Hotel -> OK. Aceste foi se pot apoi sterge si regenera, la nevoie.

Inca ceva: la un numar de cca. 200 de camere urmarite, ar rezulta cca. 73.000 de inregistrari pe an (sunt 1.048.575 de inregistrari posibile intr-o foaie de calcul + header-ul), ceea ce conduce la ideea ca intr-un astfel de fisier pot fi pastrate inregistrari pentru mai multi ani.

[mogastar]

Va multumesc frumos pentru sprijinul acordat !
Imi sunteti de mare ajutor.
Ambele solutii sunt geniale, insa as dori sa efectuez urmatoarea modificare : in loc de ID in foaia "Occupancy"sa fie folosit numele celui cazat si sa se sorteze automat in ordine alfabetica, astfel incat sa apara numele pentru fiecare cazare in parte, nu id-ul.

In solutia prezentata de Mr. gecs as vrea daca se poate ca in foaia "Occupants" sa introduc Data Validation astfel incat sa nu pot sa introduc aceiasi persoana de 2 ori. Imi va afecta acest lucru tabelul?

Imi cer scuze ca unele intrebari pe care le adresez va par fara rost, dar sin pacate sunt mediocru intr-ale Excelului deocamdata.

Permiteti-mi sa va multumesc inca o data pentru sprijinul acordat si amabilitatea de care ati dat dovada raspunzandu-mi atat de promt mai ales ca am postat in prag de sarbatori.
VA MULTUMESC !!!

[gecs]

...
insa as dori sa efectuez urmatoarea modificare : in loc de ID in foaia "Occupancy"sa fie folosit numele celui cazat si sa se sorteze automat in ordine alfabetica, astfel incat sa apara numele pentru fiecare cazare in parte, nu id-ul.

In solutia prezentata de Mr. gecs as vrea daca se poate ca in foaia "Occupants" sa introduc Data Validation astfel incat sa nu pot sa introduc aceiasi persoana de 2 ori. Imi va afecta acest lucru tabelul?
...

Nu cred ca ar fi bine de folosit numele in loc de ID pe post de cheie primara in tabelul Occupants, pentru motivul ca pot exista doua, sau mai multe, persoane diferite cu acelasi nume, dar ID-ul va fi unic, pentru ca exista intr-adevar impusa acea regula despre care vorbesti, cu Data Validation (verifica pentru orice celula a campului ID). Pentru realizarea relatiei one-to-many dintre tabelele Occupants si Occupancy e absolut necesar ca valorile de pe campul considerat cheie primara (ID) sa fie unice in tabelul-parinte, dar pot fi repetate in tabelul-copil. Exista insa posibilitatea sa vizualizezi tabelul Occupants simultan cu introducerea de date in tabelul Occupancy: cu foaia Occupants activa, de la View -> grupul Window -> New Window -> Arrange All -> bifata optiunea Vertical (sau Horizontal, cum preferi) si optiunea Windows of the active workbook -> OK -> se va deschide o noua fereastra cu workbook-ul in care poti activa foaia Occupancy. In mod normal, in tabelul Occupancy ar tebui sa existe si o cheie primara pentru sejur - ID Sejur (o persoana poate avea mai multe sejururi), campurile din Occupancy sa fie: [ID] si [ID Sejur], iar datele despre sejurul respectiv ([ID Sejur], [Date], [ID Hotel], [Room No], [Day], [Rate]) sa stea in alt tabel.

Eu chiar ma gandeam si la un tabel cu firme, cu ID Firma pentru relatia cu tabelul Occupants, iar PT-ul sa fie cu sursa de date in tabelul rezultat din relationarea celor 3 tabele (4, daca adaugam si tabelul de sejururi) - poate incerc maine varianta asta. Atat pentru problemele ridicate pana acum, cat si pentru dezvoltarea ulterioara a aplicatiei (emis facturi, inregistrari plati etc.) acesta e modul optim de organizare a datelor, recomandat si de principiile de normalizare a datelor. In tabelele Occupants si Hotels pot fi adaugate informatii specifice obiectelor respective (CNP, varsta, sex, adresa etc. pentru ocupanti, adresa, conturi, alte date fiscale pentru hoteluri, adresa, date fiscale etc. pentru firme) care sa fie implicate in alte feluri de interogari si sa nu fie folosite ca output in tabelele-sursa pentru PT-urile care sunt acum in fisier.

[mogastar]

Domnule gecs, iti multumesc din suflet!!!
Nu este nevoie sa fie inregistrate id-urile sejururilor pentru ca oricum exista datele de cazare, iar tipurile de camere sunt single la toate hotelurile.

Foaia Central_Rates din pacate nu am inteles ce face...de fapt se vede clar ce face dar nu inteleg cum lucreaza....

Multumesc frumos!

[gecs]

Domnule gecs, iti multumesc din suflet!!!
Nu este nevoie sa fie inregistrate id-urile sejururilor pentru ca oricum exista datele de cazare, iar tipurile de camere sunt single la toate hotelurile.

Foaia Central_Rates din pacate nu am inteles ce face...de fapt se vede clar ce face dar nu inteleg cum lucreaza....

Multumesc frumos!

N-am mai adaugat tabelul pentru sejururi (desi in mod normal ar exista atribute ale unui sejur care sa-l justifice), dar am modificat tabelele Occupants si Occupancy. In Occupants am inlocuit numele firmei cu ID-ul firmei (am adaugat si tabelul pentru firme), iar in Occupancy sunt mai multe modificari:

1 - am inlocuit numele hotelului cu ID-ul;

2 - selectia pentru [Room No] se face din lista creata cu Data Validation, specifica ID-ului hotelului selectat pe coloana [ID Hotel]; daca selectia de [ID Hotel] se modifica, se sterge automat si selectia de la [Room No] pentru ca s-ar putea sa nu mai corespunda; pentru ca acest mecanism sa functioneze a fost nevoie de cateva linii de cod VBA, asa ca fisierul a devenit .xlsm si trebuie sa permiti activarea macro-urilor la deschiderea fisierului;

3 - am transformat inregistrarile pentru fiecare zi intr-o singura inregistrare pentru sejur si in felul asta au aparut campurile [Arrival Date], [Leave Date], [WK Days] si [WE Days] - ultimele doua calculate prin formule.

Inainte de a continua cu explicatiile iti recomand sa citesti cu atentie (si ar fi bine sa si experimentezi cu exemplele de acolo) subiectul [TIP] Despre organizarea datelor.

Dupa ce modifici Connection string-ul, cum am explicat mai sus, cu foaia Central_Occup activa, deschizi dialogul Connection Properties (am explicat mai sus cum ajungi la el) si de la tab-ul Definition apesi pe butonul Edit Query... - se va deschide aplicatia MS Query unde poti vedea structura tabelului care constituie sursa de date pentru PivotTable-ul din foaia Central_Occup.

MS_Qry_1.jpg

In partea de sus a ferestrei sunt 4 liste cu numele foilor de calcul in care se afla tabelele care participa cu date la tabelul rezultat in urma interogarii (query-ului), iar aceste liste sunt legate intre ele prin linii. Acele linii reprezinta relatiile intre tabele si dupa cum se vede ele sunt stabilite intre campuri din categoria ID (am explicat mai sus de ce se folosesc asemenea campuri pentru ceea ce se numesc chei primare, sau unice intr-un tabel). O data stabilite aceste relatii, in partea de jos a ferestrei se pot aduce campuri din oricare dintre cele 4 tabele relationate ([Name] e din Occupants, [Hotel] e din Hotels, [Comp Name] e din Companies, iar celelalte sunt din Occupancy). Dupa cum se vede, desi campurile ID sunt folosite la relationare, in rezultatul final al interogarii nu e obligatoriu sa apara vreun asemenea camp.

In subiectul despre organizarea datelor la care am facut trimitere mai sus, exista link-uri catre subiecte de la Tips & Tricks unde se prezinta mai in detaliu modul de lucru cu MS Query - e bine sa le citesti si pe alea.

[mogastar]

Multumes frumos!
o intrebare, daca imi permiteti : In foaia Central_Ocup la report filter se poate adauga si numele persoanei cazate si hotelul?
Multumesc!

[gecs]

Multumes frumos!
o intrebare, daca imi permiteti : In foaia Central_Ocup la report filter se poate adauga si numele persoanei cazate?
Multumesc!

Se poate adauga daca se dubleaza campul in tabelul-sursa, creat cu MS Query - adica sa apara de doua ori in lista de campuri a PivotTable-ului (va aparea ca Name2, probabil). Dar nu prea vad rostul acestei adaugiri, atata vreme cat se poate folosi filtrul de pe coloana [Name]. Am dublat campul [Hotel] pentru a-l avea si in Report filter pentru ca am vazut ca in fisierul tau initial aveai cate o foaie de calcul pentru fiecare hotel si ca un exemplu de cum se pot genera mai multe foi cu PT-uri "dedicate". Daca insa consideri ca ai nevoie sa creezi, la un moment dat, cate un PT pentru fiecare persoana inregistrata, poti sa modifici chiar tu in MS Query si sa adaugi a doua oara campul [Name] din Occupants - ar fi un exercitiu foarte bun de editare a query-ului si a PT-ului

[mogastar]

Domnule gecs,
Va rog frumos, ma mai puteti ajuta cu o problema?
Nu pot sa editez query-ul.

Multumesc anticipat!