Pomocou klávesov Ctrl + Shift + Enter (vzorce CSE) doplňte svoje vzorce v programe Excel! Áno, je to pravda - v programe Excel existuje tajná trieda vzorcov. Ak poznáte magické tri klávesy, môžete získať jeden vzorec poľa programu Excel, ktorý nahradí tisíce ďalších vzorcov.
95% používateľov programu Excel nevie o vzorcoch VVN. Keď väčšina ľudí počuje svoje skutočné meno, myslí si „To neznie ani trochu užitočne“ a nikdy sa neobťažujú o nich dozvedieť. Ak si myslíte, že SumIf a CountIf sú v pohode, čoskoro zistíte, že vzorce Ctrl + Shift + Enter (CSE) budú krúžiť okolo SumIf a CountIf. Vzorce VVN umožňujú doslova nahradiť 1 000 buniek vzorcov jediným vzorcom. Áno, moji kolegovia Excel Guru, niečo také silné nám sedí priamo pod nosom a nikdy to nepoužívame.
Predtým, ako existoval SumIf, ste mohli použiť vzorec CSE na vykonanie rovnakej veci ako SumIf. Spoločnosť Microsoft nám dala SumIf a CountIf, ale vzorce CSE nie sú zastarané. Ach nie, a môžu urobiť oveľa viac! A čo keď chcete robiť AverageIf? Čo tak GrowthIf? AveDevIf? Dokonca aj MultiplyByPiAndTakeTheSquareRootIf. S jedným z týchto vzorcov VVN sa dá urobiť takmer všetko, čo si dokážete predstaviť.
Tu je dôvod, prečo si myslím, že vzorce CSE sa nikdy neuchytili. Po prvé, ich skutočné meno všetkých vydesí. Po druhé, na to, aby fungovali, je potrebné cudzie a intuitívne zaobchádzanie. Po zadaní vzorca CSE nemôžete stlačiť kláves Enter. Bunku nemôžete opustiť iba kliknutím na šípku. Aj keď vzorec dostanete správne a kliknete na kláves Enter, Excel vám poskytne úplne nenáročnú hodnotu „VALUE!“. chyba. Nehovorí sa tam: „Páni - to je krásne. Ste tam 99,1% cesty“, čo ste asi boli.
Toto je tajomstvo: Po zadaní vzorca VVN musíte podržať klávesy Ctrl a Shift a potom stlačiť kláves Enter. Chyťte rýchlu poznámku a zapíšte si ju: Ctrl Shift Enter. Používatelia programu Power Excel budú mať možnosť tieto vzorce používať približne raz za mesiac. Ak práve nenapíšete klávesovú skratku Ctrl Shift Enter, zabudnete to, kým sa niečo znova objaví.
Preskúmajte tento príklad: Povedzme, že chcete spriemerovať iba hodnoty v stĺpci C, kde bol stĺpec A v oblasti Východ.
Vzorec v bunke A13 je príkladom vzorca CSE.
=AVERAGE(IF(A2:A10="East",C2:C10))
Zapojte to a získate 7452 667, čo je priemer iba východných hodnôt. V pohode? Práve ste vytvorili svoju vlastnú verziu neexistujúcej excelovej funkcie AverageIf. Nezabudnite: Stlačením Ctrl + Shift + Enter zadajte vzorec.
Nižšie si pozrite nasledujúci príklad.
V tomto príklade urobíme vzorec CSE všeobecnejším. Namiesto toho, aby ste špecifikovali, že hľadáme výraz „východ“, označte, že chcete mať akúkoľvek hodnotu v A13. Vzorec je teraz =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Je zvláštne, že program Excel vám umožní kopírovať a vkladať vzorce CSE bez akýchkoľvek špeciálnych stlačení klávesov. Skopíroval som C13 do C14: C15 a teraz mám priemery pre všetky regióny.
Náš systém sálových počítačov ukladá množstvo a jednotkovú cenu, ale nie rozšírenú cenu. Iste, je dosť ľahké pridať stĺpec C a naplniť ho =A2*B2a potom celkový stĺpec C, ale nemusíte!
Tu je náš vzorec CSE =SUM(A2:A10*B2:B10). Trvá každú bunku v A2: A10, vynásobí zodpovedajúcou bunkou v B2: B10 a sčíta výsledok. Zadajte vzorec, podržte stlačený kláves Ctrl a Shift, kým stlačíte kláves Enter, a namiesto 10 budete mať odpoveď v jednom vzorci.
Vidíte, aké je to silné? Aj keby som mal 10 000 riadkov údajov, Excel vezme tento jediný vzorec, urobí 10 000 násobení a dá mi výsledok.
Dobre, tu sú pravidlá: Kedykoľvek zadáte alebo upravíte tieto vzorce, musíte stlačiť Ctrl + Shift + Enter. Ak to neurobíte, výsledkom bude úplne nejednoznačná #HODNOTA! chyba. Ak máte viac rozsahov, všetky musia mať rovnaký všeobecný tvar. Ak máte rozsah zmiešaný s jednotlivými bunkami, jednotlivé bunky sa „replikujú“ v pamäti tak, aby zodpovedali tvaru vášho rozsahu.
Po úspešnom zadaní jedného z nich a jeho prezeraní na riadku vzorcov by ste okolo vzorca mali mať zložené zátvorky. Nikdy nevstúpiš do zložených zátvoriek sám. Stlačením Ctrl + Shift + Enter ich tam umiestnite.
Tieto vzorce je ťažké zvládnuť. len pri pomyslení na nich bolí hlava. Ak mám náročného na vstup, prejdem do ponuky Nástroje> Sprievodcovia> Sprievodca podmienenou sumou a prechádzam dialógovými oknami. Výstupom sprievodcu podmienenou sumou je vzorec CSE, takže mi zvyčajne dá dostatok tipov, ako zadať to, čo skutočne potrebujem.
Museli ste absolvovať BASIC v 11. ročníku s pánom Irwinom? Alebo ešte horšie, dostali ste „D“ v lineárnej algebre s pani vojvodkyňou na vysokej škole? Ak je to tak, ste v dobrej spoločnosti a budete sa striasť, kedykoľvek budete počuť slovo „pole“. - Krik kričím opačným smerom, kedykoľvek niekto povie pole. Rozumiem im, ale toto je Excel, ja tu nepotrebujem polia !. Zlým tajomstvom je, že Excel a Microsoft nazývajú tieto vzorce „maticové vzorce“. Prestaň - neutekaj. Je to v poriadku. Používateľ ich premenoval na vzorce CSE, pretože to znie menej strašidelne a pretože názov vám pomôže spomenúť si, ako ich zadávať - Ctrl Shift Enter. Skutočné meno tu uvádzam iba v prípade, že narazíte na niekoho z Microsoftu, ktorý nikdy nemal pani vojvodkyňu pre lineárnu algebru, alebo v prípade, že sa rozhodnete prezrieť si súbory Pomocníka. Ak pôjdeš pomôcť,hľadajte pod zlým aliasom „formule poľa“, ale medzi nami priateľmi, nazvime ich CSE - OK?
Prečítajte si článok Špeciálny vzorec poľa Excel na simuláciu SUMIF s dvoma podmienkami.
