Bruk Ctrl + Shift + Enter (CSE-formler) for å overbelaste formlene i Excel! Ja, det er sant - det er en hemmelig klasse med formler i Excel. Hvis du kjenner de magiske tre tastene, kan du få en enkelt Excel-matriseformel som erstatter tusenvis av andre formler.
95% av Excel-brukere vet ikke om CSE-formler. Når folk flest hører deres virkelige navn, tenker de "Det høres ikke så nyttig ut" og bryr seg aldri om å lære om dem. Hvis du synes SumIf og CountIf er kule, vil du snart oppdage at Ctrl + Shift + Enter (CSE) formler vil kjøre sirkler rundt SumIf og CountIf. CSE-formler lar deg bokstavelig talt erstatte 1000-tallet av celler med formler med en enkelt formel. Ja, mine andre Excel-guruer, det er noe denne kraftige sitter rett under nesen vår, og vi bruker den aldri.
Før det var SumIf, kunne du bruke en CSE-formel til å gjøre det samme som SumIf. Microsoft ga oss SumIf og CountIf, men CSE-formler er ikke foreldede. Å nei, og de kan gjøre mye mer! Hva med hvis du vil gjøre AverageIf? Hva med GrowthIf? AveDevIf? Selv MultiplyByPiAndTakeTheSquareRootIf. Omtrent alt du kan forestille deg kan gjøres med en av disse CSE-formlene.
Her er hvorfor jeg tror CSE-formler aldri fanget opp. For det første skremmer deres virkelige navn alle bort. For det andre krever de en utenlandsk, kontraintuitiv håndtering for å få dem til å fungere. Etter at du har skrevet inn en CSE-formel, kan du ikke bare trykke Enter. Du kan ikke bare gå ut av cellen med et klikk på en piltast. Selv om du får formelen riktig og trykker på enter-tasten, gir Excel deg den helt ikke-brukervennlige "VERDI!" feil. Det står ikke "Wow - det er vakkert. Du er 99,1% av veien dit," som du sannsynligvis var.
Her er hemmeligheten: Når du har skrevet en CSE-formel, må du holde nede Ctrl og Shift-tastene, og deretter trykke Enter. Ta en lapp og skriv det ned: Ctrl Shift Enter. Power Excel-brukere vil ha muligheten til å bruke disse formlene omtrent en gang i måneden. Hvis du ikke skriver Ctrl Shift Enter ned akkurat nå, vil du glemme det når noe kommer opp igjen.
Undersøk dette eksemplet: Si at du ønsket å gjennomsnittliggjøre bare verdiene i kolonne C der kolonne A var i øst-regionen.
Formelen i celle A13 er et eksempel på en CSE-formel.
=AVERAGE(IF(A2:A10="East",C2:C10))
Koble dette til, så får du 7452.667, gjennomsnittet av bare østverdiene. Kul? Du har nettopp opprettet din egen versjon av den ikke-eksisterende Excel-funksjonen AverageIf. Husk: Trykk Ctrl + Skift + Enter for å angi formelen.
Sjekk ut neste eksempel nedenfor.
I dette eksemplet gjør vi CSE-formelen mer generell. I stedet for å spesifisere at vi leter etter "Øst", indikerer du at du vil ha hvilken verdi som helst i A13. Formelen er nå =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Merkelig nok lar Excel deg kopiere og lime inn CSE-formler uten spesielle tastetrykk. Jeg kopierte C13 til C14: C15 og har nå gjennomsnitt for alle regionene.
Mainframe-systemet vårt lagrer mengde og enhetspris, men ikke den utvidede prisen. Visst, det er lett nok å legge til en kolonne C og fylle den med =A2*B2og deretter total kolonne C, men du trenger ikke!
Her er vår CSE-formel =SUM(A2:A10*B2:B10). Det tar hver celle i A2: A10, multipliseres med den tilsvarende cellen i B2: B10 og summerer resultatet. Skriv inn formelen, hold nede Ctrl og Shift mens du trykker på enter, og du har svaret i en formel i stedet for 10.
Ser du hvor kraftig dette er? Selv om jeg hadde 10 000 rader med data, vil Excel ta denne enkeltformelen, gjøre 10 000 multiplikasjoner og gi meg resultatet.
OK, her er reglene: Du må trykke Ctrl + Shift + Enter når du skriver inn eller redigerer disse formlene. Unnlatelse av å gjøre det resulterer i den helt tvetydige #VALUE! feil. Hvis du har flere områder, må de alle ha samme generelle form. Hvis du har et område blandet med enkeltceller, blir "enkeltcellene" replikert "i minnet for å matche formen på området ditt.
Etter at du har angitt en av disse og sett på den i formellinjen, bør du ha krøllete bukseseler rundt formelen. Du kommer aldri inn i krøllene. Å trykke Ctrl + Shift + Enter setter dem der.
Disse formlene er vanskelige å mestre. får hodepine bare ved å tenke på dem. Hvis jeg har en tøff å gå inn på, går jeg til Verktøy> Veivisere> Veiviser for betinget sum og går gjennom dialogboksene. Resultatet av veiviseren for betinget sum er en CSE-formel, så dette kan vanligvis gi meg nok tips til hvordan jeg skriver inn det jeg virkelig trenger.
Måtte du ta BASIC i 11. klasse med Mr. Irwin? Eller, enda verre, fikk du en "D" i lineær algebra med fru hertuginne på college? I så fall er du i godt selskap og vil skjelve når du hører ordet "array". - Jeg løper skrikende i den andre retningen når noen sier matrise. Jeg forstår dem, men dette er Excel, jeg trenger ikke matriser her !. Den onde hemmeligheten er at Excel og Microsoft kaller disse formlene for "matriseformler". Stopp - ikke stikk av. Det er OK, egentlig. har omdøpt dem til CSE-formler fordi det høres mindre skummelt ut, og fordi navnet hjelper deg å huske hvordan du skriver inn dem - Ctrl Shift Enter. Jeg nevner bare det virkelige navnet her hvis du støter på noen fra Microsoft som aldri hadde fru hertuginne for lineær algebra, eller hvis du bestemmer deg for å lese hjelpefilene. Hvis du hjelper,søk under det onde aliaset til "array formula", men mellom oss venner, la oss kalle dem CSE - OK?
Les Bruk en spesiell Excel Array Formula for å simulere SUMIF med to forhold.
