Uporabite Ctrl + Shift + Enter (formule CSE), da svoje formule v Excelu napolnite! Da, res je - v Excelu obstaja skrivni razred formul. Če poznate čarobne tri tipke, lahko dobite eno Excelovo matrično formulo, ki nadomesti tisoče drugih formul.
95% uporabnikov Excela ne ve za formule CSE. Ko večina ljudi sliši svoje pravo ime, pomisli, da "to ne zveni niti najmanj koristno", in se nikoli ne trudijo izvedeti več o njih. Če se vam zdi, da sta SumIf in CountIf kul, boste kmalu odkrili, da bodo formule Ctrl + Shift + Enter (CSE) vodile kroge okoli SumIf in CountIf. Formule CSE vam omogočajo, da dobesedno zamenjate 1000 celic formul z eno samo formulo. Ja, kolegi Excel Guruji, nekaj tako močnega nam sedi pod nosom in tega nikoli ne uporabimo.
Preden je bil SumIf, ste lahko uporabili formulo iskalnika poizvedb, da naredite isto kot SumIf. Microsoft nam je dal SumIf in CountIf, vendar formule CSE niso zastarele. O ne, in zmorejo še veliko več! Kaj pa, če želite narediti AverageIf? Kaj pa GrowthIf? AveDevIf? Tudi MultiplyByPiAndTakeTheSquareRootIf. Skoraj vse, kar si predstavljate, lahko naredite z eno od teh formul iskalnikov po meri.
Evo, zakaj mislim, da se formule CSE nikoli niso prijele. Prvič, njihovo pravo ime prestraši vse. Drugič, za delo potrebujejo tuje, protiintuitivno ravnanje. Ko vnesete formulo iskalnika po meri, ne morete samo pritisniti Enter. Celice ne morete zapustiti samo s klikom puščične tipke. Tudi če dobite pravilno formulo in pritisnete tipko Enter, vam Excel da popolnoma neprimerno za uporabnika "VREDNOST!" napaka. Ne piše: "Vau - to je čudovito. Na poti ste 99,1%," kar ste verjetno tudi bili.
Tu je skrivnost: ko vnesete formulo iskalnika po meri, morate pritisniti tipki Ctrl in Shift in nato pritisniti Enter. Zgrabite lepljivo opombo in jo zapišite: Ctrl Shift Enter. Uporabniki Power Excela bodo imeli te formule možnost približno enkrat na mesec. Če trenutno ne napišete Ctrl Shift Enter, boste to pozabili, ko se bo spet kaj pojavilo.
Preglejte ta primer: Recimo, da ste želeli povprečiti samo vrednosti v stolpcu C, kjer je bil stolpec A v vzhodni regiji.
Formula v celici A13 je primer formule CSE.
=AVERAGE(IF(A2:A10="East",C2:C10))
Priključite to in dobili boste 7452.667, povprečje le vzhodnih vrednosti. Kul? Pravkar ste ustvarili svojo različico neobstoječe Excelove funkcije AverageIf. Ne pozabite: Pritisnite Ctrl + Shift + Enter, da vnesete formulo.
Oglejte si naslednji primer spodaj.
V tem primeru formulo CSE naredimo bolj splošno. Namesto da navedete, da iščemo "vzhod", navedite, da želite kakršno koli vrednost je v A13. Formula je zdaj =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Čudno je, da vam bo Excel omogočil kopiranje in lepljenje formul iskalnikov po meri brez posebnih pritiskov tipk. Kopiral sem C13 v C14: C15 in zdaj imam povprečja za vse regije.
Naš glavni računalnik hrani količino in ceno na enoto, ne pa tudi razširjene cene. Seveda je dovolj enostavno dodati stolpec C in ga izpolniti z =A2*B2in nato skupni stolpec C, vendar vam ni treba!
Tukaj je naša formula CSE =SUM(A2:A10*B2:B10). Vsaka celica v A2: A10 vzame, pomnoži z ustrezno celico v B2: B10 in sešteje rezultat. Vnesite formulo, medtem ko pritisnete tipko Enter, držite Ctrl in Shift in namesto 10 odgovorite v eni formuli.
Ali vidite, kako močna je to? Tudi če bi imel 10.000 vrstic podatkov, bo Excel vzel eno samo formulo, naredil 10.000 množenja in mi dal rezultat.
V redu, tukaj so pravila: kadar koli vnesete ali uredite te formule, morate pritisniti Ctrl + Shift + Enter. Če tega ne storite, nastane popolnoma dvoumna #Vrednost! napaka. Če imate več obsegov, morajo imeti vsi enako splošno obliko. Če imate obseg, pomešan z enimi celicami, se bodo posamezne celice v pomnilniku "podvojile", da se bodo ujemale z obliko vašega obsega.
Ko uspešno vnesete eno od njih in si jo ogledate v vrstici s formulami, bi morali imeti okrogle oklepaje okoli formule. Nikoli sami ne vstopite v zavite oklepaje. Če pritisnete Ctrl + Shift + Enter, jih postavite tja.
Te formule je težko obvladati. dobi glavobol že ob razmišljanju o njih. Če moram vstopiti težko, grem v Orodja> Čarovniki> Čarovnik za pogojno vsoto in se sprehodim po pogovornih oknih. Rezultat čarovnika za pogojno vsoto je formula iskalnika po meri, zato mi to ponavadi da dovolj namigov, kako vnesti tisto, kar v resnici potrebujem.
Ali ste morali v 11. razredu z gospodom Irwinom vzeti BASIC? Ali še huje, ali ste na fakulteti z gospo Duchess dobili "D" iz linearne algebre? V tem primeru ste v dobri družbi in se boste zgrozili, ko zaslišite besedo "matrika". - Vriščem tečem v drugo smer, kadar koli kdo reče array. Razumem jih, toda to je Excel, tukaj ne potrebujem nizov. Zla skrivnost je, da Excel in Microsoft imenujeta te formule "matrične formule". Ustavite se - ne bežite. Res je v redu. jih je preimenoval v formule iskalnikov po meri, ker se sliši manj strašljivo in ker vam ime pomaga zapomniti, kako jih vnašati - Ctrl Shift Enter. Tu omenim samo pravo ime, če naletite na nekoga iz Microsofta, ki nikoli ni imel gospe Duchess za linearno algebro, ali če se odločite za pregled datotek s pomočjo. Če greste po pomoč,iščite po zlobnem vzdevku "matrična formula", toda med nami prijatelji, recimo jim CSE - v redu?
Preberite Uporaba posebne Excelove matrične formule za simulacijo SUMIF z dvema pogojema.
