V tomto článku sa naučíme Ako zoskupiť dátumy podľa počtu dní v programe Excel.
Scenár:
Problém je, keď máme v kontingenčnej tabuľke množinu dátumových polí. A používateľ chce zoskupiť pole dátumu podľa určitého počtu dní namiesto rokov alebo diskrétnych dátumov. Napríklad zoskupenie importu a exportu produktov každých 60 dní v programe Excel. Alebo zoskupenie údajov zamestnancov podľa mesiacov alebo týždňov v programe Excel. Na riešenie týchto problémov používame kontingenčné tabuľky a možnosti zoskupovania. Existuje ešte jeden spôsob, akým môžu užívatelia používať agregačnú funkciu. Poďme pochopiť tieto dva procesy jeden po druhom.
Skupina v kontingenčnej tabuľke v programe Excel
Vyberte údaje> Vložiť> Kontingenčná tabuľka> (Vložiť novú kontingenčnú tabuľku)> Prejsť na kontingenčnú tabuľku pridať pole dátumu do riadkov> Hodnota dátumu pravým tlačidlom myši> Skupina…> Vyberte mesiace alebo dni> Kliknutím na tlačidlo OK potvrďte
Alternatívny spôsob s agregačnou funkciou
Pridajte počiatočný dátum a bunku prírastku v každom intervale 60. Použite súhrnný vzorec programu Excel s viacerými kritériami.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako používať funkciu na príklade. Tu máme ukážkové údaje o tržbách a zisku podľa štátov. Údaje musíme zoskupiť do 60 dní (približne 2 mesiace).
Zoraďte údaje podľa poľa dátumu. Vyberte tabuľku a vložte kontingenčnú tabuľku údajov. Pridajte dátum k riadkom a tržby a zisk k hodnotám.
Ako vidíte, pole dátumu je vo formáte roku. Chceme však zoskupiť do 60 dní. Kliknite teda pravým tlačidlom myši na hodnotu dátumu> Vybrať skupinu …
Kliknite na Ok.
Dátum je zoskupený podľa 60 -dňového intervalu spolu s hodnotou tržieb a zisku.
Toto je najľahší spôsob zoskupenia dátumu podľa mesiacov. Ak však potrebujete vyššie uvedené údaje pomocou vzorca sumifov, bude to časovo náročnejšie, ale je vhodné efektívne využívať funkcie.
Alternatívny spôsob pomocou vzorca SUMIFS
Pre rovnaké údaje vytvorte novú tabuľku s názvom počiatočného dátumu a pridajte 59 dní k počiatočnému dátumu, ako je uvedené nižšie.
A v bunke A5 použite = B4+1 na získanie ďalšieho dátumu. Teraz skopírujte a prilepte vzorec bunky B4, kým nezíska dátum ukončenia. Skopírujte a prilepte vzorec bunky A5 do koncového rozsahu dátumov.
Teraz použite vzorec sumifov
= SUMIFS (rozsah súčtov, stĺpček dátumu z tabuľky, "> =" & A4, dátum stĺpca z tabuľky, "<=" & B4)
Filtrujte prázdnu bunku alebo bunku s hodnotou 0, aby ste získali rovnaké pole, aké sme získali pomocou kontingenčnej tabuľky.
Zatiaľ čo kontingenčná tabuľka vám ponúka rôzne agregačné funkcie, ako sú priemer, počet, súčet, max, min, var, stdev, ale excelske funkcie AVERAGEIFS, COUNTIFS, SUMIFS, MAXIFS a MINIFS, z ktorých si môžete vyberať. MAXIFS a MINIFS sú aktualizačné funkcie nad Excel 2016 alebo nový Excel 365.
Tu sú všetky poznámky k pozorovaniu pomocou vysvetlenej metódy v programe Excel
Poznámky:
- Uprednostnite alebo použite metódu kontingenčnej tabuľky, je to jednoduché a na výber máte veľa funkcií.
- Excel ukladá dátumy a čas ako čísla a používa ich na výpočet funkcia. Odporúča sa preto použiť dátumy ako referenciu na bunku alebo použiť funkciu DATE namiesto toho, aby ste funkciu priamo argumentovali. Skontrolujte validáciu hodnôt dátumu, inak funkcia vráti #HODNOTA! Chyba.
- Pre „> =“ a „<=“ používajte úvodzovky so vzorcom súhrnov.
- MAXIFS a MINIFS nová a aktualizovaná verzia MIN a MAX s kritériami. Alebo použite vzorec poľa s MAX a MIN na mieste pre MINIFS a MAXIFS staršie verzie programu Excel.
Dúfam, že tento článok o zoskupení dátumov podľa počtu dní v programe Excel je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt dátumu a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Kontingenčné tabuľky programu Excel : Kontingenčné tabuľky sú jedným z najúčinnejších nástrojov a ten, kto pozná všetky funkcie kontingenčných tabuliek, môže exponenciálne zvýšiť svoju produktivitu. V tomto článku sa podrobne dozvieme všetko o kontingenčných tabuľkách.
Počítajte sviatky medzi dátumami v Exceli : počítajte dni pracovného pokoja medzi týmito dvoma dátumami pomocou funkcie DATEDIF a NETWORKDAYS v programe Excel.
Podmienené formátovanie pre kontingenčnú tabuľku : Podmienené formátovanie v kontingenčných tabuľkách je rovnaké ako podmienené formátovanie na bežných údajoch. Pri podmienenom formátovaní kontingenčných tabuliek však musíte byť opatrní, pretože údaje sa dynamicky menia.
Ako previesť dátum na text v programe Excel : V tomto článku sme sa dozvedeli, ako previesť text na dátum, ale ako previesť dátum v programe Excel na text. Na konverziu dátumu Excel na text máme niekoľko techník.
Extrahujte dni medzi dátumami, pričom roky v programe Excel ignorujte : spočítajte dni medzi týmito dvoma dátumami pomocou rôznych funkcií a matematickej operácie v programe Excel.
V programe Excel počítajte dátumy narodenia podľa mesiaca : pomocou funkcie SUMPRODUCT a MONTH v programe Excel spočítajte počet dátumov ležiacich v konkrétnom mesiaci.
Ako používať funkciu NETWORKDAYS v programe Excel : Vráti pracovné dni medzi týmito dvoma dátumami pomocou funkcie NETWORKDAYS.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.