Linia de fund: Aflați cum să grupați orele într-un set de date pentru rapoarte și diagrame rezumate. Vom folosi tabele pivot și funcțiile FLOOR, TRUNC și VLOOKUP pentru diferitele soluții.

Nivelul de aptitudine: Intermediar

moduri

În acest articol vom învăța câteva moduri diferite de a rezuma datele pe baza grupelor de timp (incrementări de ore sau minute). Acest lucru este minunat dacă doriți să analizați tendințele pe parcursul orelor unei zile pentru a vedea la ce oră sunteți cel mai ocupat și la ce oră puteți face un pui de somn 🙂 🙂 Vom vedea, de asemenea, cum să creați blocuri de timp personalizate și inegale.

Descarca

Descărcați fișierul de urmat.

Datele conțin o coloană cu data și ora

În acest scenariu avem un tabel cu date de vânzări care conține un rând pentru fiecare tranzacție. A doua coloană conține data și ora tranzacției.

Șeful vrea să vadă un raport care arată câte tranzacții au avut loc în fiecare oră a zilei. El dorește, de asemenea, un raport care să aibă grupuri la fiecare două ore și un alt raport care să combine orele peste noapte într-un singur grup.

S-ar putea să existe o mulțime de coloane în setul dvs. de date, dar chiar ne vom concentra doar pe coloana care conține valorile date-ora pentru acest exercițiu.

Soluția # 1 - Timp de grup cu un tabel pivot

Cea mai rapidă și mai ușoară soluție ar fi utilizarea caracteristicii de grup într-un tabel pivot. Această soluție este, de asemenea, cea mai limitativă, deoarece veți putea grupa doar orele Incremente de 1 oră.

  1. Primul pas este crearea unui tabel pivot și adăugarea câmpului Data în zona Rânduri.
  2. Faceți clic dreapta pe orice celulă din zona Rânduri și alegeți Grup ... (Notă: dacă opțiunea Grupare este dezactivată, atunci câmpul dvs. de dată conține text sau spații goale. Toate celulele din coloana de date din setul de date trebuie să conțină valori de dată.)
  3. Alegeți doar Ore din meniul Grupare.
  4. Acum ar trebui să vedeți o listă cu trepte de o oră în zona Rânduri a tabelului pivot. Valorile din zona Valori ale tabelului pivot vor fi însumate pentru a include toate tranzacțiile care au avut loc în fiecare oră. Imaginea de mai jos arată că au avut loc 4 tranzacții între orele 00:00 și 00:59.

Vă rugăm să verificați seria mea video pe tabelele pivot și tablouri de bord pentru mai multe detalii despre această tehnică. Vă explic cum să grupați datele în cel de-al treilea videoclip al acelei serii.

Aceasta este o soluție rapidă, dar suntem capabili să grupăm orele doar în trepte de o oră. Va trebui să folosim o altă soluție pentru a grupa tranzacțiile în blocuri de timp de 2 ore sau mai mult.

Soluția # 2 - Funcția FLOOR

Dacă șeful dorește să vadă orele grupate în trepte altele decât 1 oră, atunci putem folosi funcția FLOOR pentru a rotunji orele.

Funcția FLOOR are două argumente:

număr este valoarea care trebuie rotunjită. În acest caz, este valoarea datei noastre.

semnificaţie este multiplu pentru rotunjirea numărului până la. De asemenea, putem specifica aici o valoare a timpului.

În acest caz, am specificat „2:00” pentru rotunjirea valorii timpului la cel mai apropiat increment de 2 ore. Partea drăguță este că FLOOR începe la miezul nopții și se rotunjește în orice increment pe care îl specificați. Deci 12:25 PM și 1:25 PM ambele vor fi întotdeauna rotunde până la 12:00 PM.

De asemenea, puteți specifica trepte mai mici pentru a grupa orele la fiecare jumătate de oră „0:30” sau la fiecare cinci minute „0:05”.

Ce se întâmplă dacă aveți mai mult de o zi?

Dacă setul dvs. de date conține vânzări pentru mai mult de o zi, atunci veți dori să utilizați Funcția TRUNC pentru a împărți ora și data. Funcția TRUNC trunchiază un număr într-un număr întreg.

În Excel datele sunt stocate ca numere întregi. Datele cu orele sunt stocate ca numere cu zecimale. Deci scăderea datei din data trunchiată va restitui doar numărul zecimal, care este ora.

Dacă formatați data: 25.09.2015 05:54 ca număr, veți vedea: 42272.2460

42272 este data, iar .2460 este timpul ca o fracțiune de zi (5 ore 54 minute).

Numărul 42.272 reprezintă numărul de zile care au trecut de la 1 ianuarie 1900. Acesta este începutul calendarului în Excel.

Formula [@Date] -TRUNC [[Date] returnează .2460, care este ora.

* Veți observa că folosesc tabele Excel în imaginea de mai sus, dar puteți utiliza și referințe de celule obișnuite.

Rezumați-l cu un tabel pivot

Această rotunjire cu funcția FLOOR a atribuit practic un nume de grup fiecărui rând al datelor sursă.

Acum puteți adăuga coloana care utilizează funcția FLOOR în zona Rânduri din tabelul pivot, în loc de câmpul Data grupată.

Aceasta ne oferă un rezumat al numărului de tranzacții cu blocuri de două ore. Dacă setul dvs. de date se întinde pe mai multe zile, atunci acest lucru vă va arăta cu adevărat ce ore din zi faceți cel mai mult volum. Puteți să-l analizați în continuare pentru a vedea zilele săptămânii, săptămânile lunii etc.

Soluția # 3 - Funcția VLOOKUP

Ce se întâmplă dacă nu vrem aceeași perioadă de timp pentru fiecare grup? În imaginea de la începutul articolului prezint un raport care are un bloc de timp între 12 AM - 6 AM (6 ore), apoi intervale de 2 ore pentru restul zilei.

Șeful ar putea dori să-l vadă așa, deoarece nu există prea multe afaceri peste noapte și ocupă mult spațiu în raport sau diagramă.

Utilizați VLOOKUP pentru a returna cea mai apropiată potrivire - tehnica de grupare

Pentru această soluție putem folosi VLOOKUP pentru a căuta ora într-un tabel de căutare.

Prima coloană din tabelul nostru de căutare conține valoarea de timp pentru căutare. A doua valoare conține un nume de grup. Acesta este doar text pe care l-am creat cu o formulă folosind funcția TEXT.

Observați că ultimul argument din formula mea VLOOKUP este ADEVĂRAT, ceea ce înseamnă că îl folosim pentru a returna cea mai apropiată potrivire. Iată un articol despre returnarea celui mai apropiat meci cu VLOOKUP. Este o tehnică excelentă de învățat pentru lucruri precum calcularea comisioanelor sau a ratelor de impozitare.

Dacă sunteți complet nou în VLOOKUP, verificați acest articol în care vă explic VLOOKUP la Starbucks.

Rezumați grupurile de timp neuniform cu un tabel pivot

Adăugarea coloanei cu formulele VLOOKUP în zona Rânduri a tabelului pivot ne oferă un rezumat al tranzacției pe baza grupelor de timp inegale.

Un dezavantaj al acestei metode este că va trebui să sortați manual numele grupurilor în zona Rânduri a tabelului pivot. Aceste nume de grupuri sunt text, nu numere, așa că Excel nu le va putea sorta ca ori.

Concluzie

Există o mulțime de moduri de grupare a timpilor în Excel. Cea mai rapidă și mai simplă metodă este, probabil, utilizarea caracteristicii Grup într-un tabel pivot (soluția nr. 1). Dacă doriți să grupați orele în trepte de ore multiple sau fracțiuni de oră, atunci funcțiile FLOOR și VLOOKUP vă pot ajuta să grupați orele.

Vă rugăm să lăsați un comentariu mai jos cu orice întrebări. Mulțumiri!