Tieto 3 vzorce v Exceli používam na automatizáciu mojich nudných povinností

Život je hektický a zvládanie desiatok aplikácií na „nevyhnutné“ úlohy pridáva do toho chaos. Keďže už každý deň pracujem v Exceli, rozšíril som jeho použitie na automatizáciu troch mojich najnudnejších úloh. Tieto nastavené a zabudnuté vzorce uľahčujú život a ich vytvorenie trvá len niekoľko minút.

Proporcionálny delenie účtu

V mojom tabuľkovom hárku nižšie, keď zadám účet, vzorec sa vyhýba nepríjemným rokovaniam tým, že generuje rozdelenie vážené podľa platu a samo sa upravuje.

Dosiahnutie tohto vyžaduje iba zadanie jedného vzorca do bunky C5 a jeho pretiahnutie do bunky D5:

=(A$2/($A$2+$B$2))*T_Expenses[@[Total]:[Total]]

Keď pridám výdavok do nasledujúceho riadku, pretože údaje sú formátované ako tabuľka v Exceli, všetky jej vzorce a formátovanie sa automaticky rozšíria.

Tu je vysvetlené, ako funguje vzorec.

Časť 1: Pomer

Prvá časť vzorca vypočíta percento príjmu osoby A z celkového príjmu domácnosti.

=(A$2/($A$2+$B$2))

Znak dolára v Excelových vzorcoch určuje, či sa odkaz má zmeniť pri kopírovaní alebo pretiahnutí vzorca do novej bunky. A$2 zamyká iba riadok, takže keď sa vzorec aplikuje na riadky nižšie v tabuľke, vždy sa použije riadok so mzdou. Keďže pred písmenom A nie je znak dolára, stĺpec je relatívny, takže keď pretiahnete vzorec zo stĺpca C do stĺpca D, A sa správne zmení na B. V časti súčtu sú všetky riadky a stĺpce zamknuté, pretože sa nemusia meniť pri žiadnych výpočtoch.

Časť 2: Spoločný účet

Podiel osoby sa vynásobí celkovým účtom, aby sa vypočítala konečná suma.

*T_Expenses[@[Total]:[Total]]

Táto syntax zabezpečuje, že odkaz na stĺpec Celkom sa nezmení, keď presuniete vzorec doprava. Tu je rozpis toho, čo sa deje:

• Symbol @: Implicitný operátor prieniku hovorí Excelu, aby hľadal iba v aktuálnom riadku stĺpca Celkom.
• Zátvorky a dvojbodka: Toto je tá časť, ktorá uzamkne odkaz, aby zostal pripevnený k stĺpcu Celkom.
• Názov tabuľky: Hneď ako prejdete z jednoduchej štruktúrovanej referencie na zložitejšiu, Excel od vás vyžaduje zadanie názvu tabuľky.

Časť 3: Druhá osoba

Nakoniec kliknite a potiahnite rukoväť vyplnenia v bunku C5 do bunky D5.

Chytrý nákupný zoznam

V tomto hárku, hneď ako aktualizujem číslo v stĺpci Sklad, stĺpec Akcia zobrazuje, čo kúpiť a koľko.

Tu je vzorec, ktorý používam, aby sa to stalo:

=IF([@Target]>[@Stock],"Buy "&[@Target]-[@Stock]&" "&[@Unit]&IF([@Target]-[@Stock]>1,IF(OR(RIGHT([@Unit],1)="o",RIGHT([@Unit],1)="s",RIGHT([@Unit],1)="x"),"es","s"),"")&" of "&[@Item],"-")

Áno, na prvý pohľad to vyzerá komplikovane, ale v skutočnosti je to logické a intuitívne.

Časť 1: Medzera v inventári

Vzorec začína logickým hradlom, ktoré rozhoduje, či je potrebná akcia. Ak je váš cieľ vyšší než zásoby, musíte kúpiť viac. Ak nie je, vzorec preskočí priamo na koniec a vráti „-„.

=IF([@Target]>[@Stock],...,"-")

Časť 2: Výpočet

Ak máte nedostatok zásob, Excel vytvára prvú časť poučujúcej vety. Matematika vypočíta rozdiel medzi tým, čo chcete, a tým, čo máte, a potom to spojuje s vašou jednotkou merania. Ampersandy (&) fungujú ako lepidlo, ktoré spája čísla a slová do jedného textového reťazca.

"Buy "&[@Target]-[@Stock]&" "&[@Unit]

Časť 3: Gramatika

Toto je najdlhšia časť vzorca. Jej úlohou je rozhodnúť, či pridať k jednotke množné príponu (napríklad premeniť „Box“ na „Boxes“) alebo ju nechať tak. Najprv skontroluje, či kupujete viac ako jednu jednotku. Ak potrebujete len jednu, pridá prázdny reťazec („“) na zachovanie jednotného čísla. Ak však potrebujete dve alebo viac jednotiek, pozrie sa na posledné písmeno slova v stĺpci Jednotka. Ak končí na o, s alebo x, vzorec zvolí „es“. Inak pridá jednoduché „s“.

IF([@Target]-[@Stock]>1,IF(OR(RIGHT([@Unit],1)="o",RIGHT([@Unit],1)="s",RIGHT([@Unit],1)="x"),"es","s"),"")

Časť 4: Záverečný reťazec

Keď je pluralita jednotky vyriešená, záverečná časť pridá slovo „z“ a zahrnie názov položky:

&" of "&[@Item]

Sledovač predplatného

V tomto hárku, keď zadám podrobnosti o predplatnom, mi Excel povie, koľko dní zostáva do ďalšej platby.

Vzorec je nasledovný:

=(IF([@Period]="Monthly",EDATE([@First],(YEAR(TODAY())-YEAR([@First]))*12+MONTH(TODAY())-MONTH([@First])+(DAY(TODAY())>DAY([@First]))),EDATE([@First],(YEAR(TODAY())-YEAR([@First])+(DATE(YEAR(TODAY()),MONTH([@First]),DAY([@First]))<TODAY()))*12))-TODAY())

Takto to funguje.

Časť 1: Prepínač

Vzorec začína identifikáciou fakturačného cyklu. Ak je obdobie mesačné, Excel vykoná prvý výpočet. Ak je to čokoľvek iné (ročné), vykoná druhý výpočet.

Správa How-To Geek

Správa How-To Geek

Tým, že sa prihlásite na odber, súhlasíte s prijímaním newslettera a marketingových e-mailov a akceptujete Podmienky používania a Zásady ochrany osobných údajov spoločnosti Valnet. Odber môžete kedykoľvek zrušiť.

=IF([@Period]="Monthly"

Časť 2: Mesačný motor

Toto vypočíta ďalšie mesačné výročie. Vypočíta počet mesiacov od vašej prvej platby a pridá ďalší mesiac, ak dnešný deň už prešiel vaším obvyklým dátumom fakturácie. To zabezpečuje, že nájdený dátum je vždy v budúcnosti.

EDATE([@First],(YEAR(TODAY())-YEAR([@First]))*12+MONTH(TODAY())-MONTH([@First])+(DAY(TODAY())>DAY([@First])))

Časť 3: Ročný motor

Podobne ako mesačný mechanizmus, tento sa pozerá na uplynulé roky. Vytvorí dočasný dátum pre tohtoročný výročný deň - ak tento dátum už prešiel, pridá 12 mesiacov, aby našiel dátum v budúcom roku.

EDATE([@First],(YEAR(TODAY())-YEAR([@First])+(DATE(YEAR(TODAY()),MONTH([@First]),DAY([@First]))<TODAY()))*12))

Časť 4: Odpočet

Keď vzorec nájde správny budúci dátum obnovenia, odpočíta od neho aktuálny dátum. Tým sa kalendárny dátum premení na počet dní.

-TODAY())

Aj keď môže byť vytváranie vzorcov vlastnými silami uspokojujúce, ak chcete skočiť rovno na to, stiahnite si bezplatnú kópiu hotového pracovného hárka. Po kliknutí na odkaz nájdete tlačidlo na stiahnutie v pravom hornom rohu obrazovky. Po otvorení súboru nájdete každý vzorec na samostatnej karte pracovného hárka.