Ser etter Riktig Entry av Array Formler

April 2  by Eliza

Jeffrey Selskapet har en rekke rapporter som bruker et omfattende antall CSE (Ctrl + Shift + Enter) matriseformler. Når noen glemmer å holde Ctrl og Shift når du trykker på Enter, trenger de resulterende formler ikke lik det riktige svaret. Revisjon hver celle, på jakt etter de {} parentes er både kjedelig og tidkrevende. Jeffrey lurer på om det er en rask måte å finne de "manglende parentes" eller heve en feil flagg hvis Ctrl + Shift + Enter ikke trykkes når det skal være?

Det er ingen egenverdi eller formalistisk metode for å gjøre dette i Excel. Dette betyr at du trenger å slå til en løsning som er basert på en makro. Heldigvis VBA tilbyr flere forskjellige måter du kan nærme seg dette problemet. En tilnærming er å bare bruke en formel for å sørge for at hver formel i et utvalg er faktisk en matriseformel.

Sub MakeCSE1 ()
Dim Rceii Som Range

For Hver Rceii i utvalg
rCell.FormulaArray = rCell.Formula
Neste Rceii
End Sub

Denne makroen forutsetter at du vil velge cellene til å bli "konvertert" før faktisk kjører makroen. Hvis du foretrekker det, kan du definere en rekke celler (gi området et navn) og deretter kjøre en lignende makro som alltid gjør sitt arbeid på dette området.

Sub MakeCSE2 ()
Dim RNG Som Range
Dim Rceii Som Range
Dim rArea Som Range

Satt RNG = Range ("CSERange")
For Hver rArea I rng.Areas
For Hver Rceii I rArea.Cells
Hvis rCell.HasArray = False Deretter
rCell.FormulaArray = rCell.Formula
End If
Neste Rceii
Neste rArea
End Sub

Denne makroen ser for en rekke navngitt CSERange og deretter sjekker hver eneste celle i området. Hvis den ikke inneholder en matrise formel, så formelen omdannes til en matrise formel.

Legg merke til at bruk av HasArray egenskap for å kontrollere om en celle inneholder en matrise formel. Denne egenskapen kan faktisk være nyttig på andre måter. For eksempel kan du lage en enkel brukerdefinert funksjon, slik som dette:

Funksjon NoCellArray1 (RNG Som Range) Som boolsk
NoCellArray1 = Ikke rng.HasArray
End Function

Denne funksjonen returnerer True hvis cellen blir pekt på ikke inneholder en matriseformel. Hvis den gjør det inneholde en, så False returneres. Du kan deretter bruke denne funksjonen som grunnlag for en betinget format. Alt du trenger å gjøre er å lage et format som bruker det på denne måten:

= NoCellArray1 (A5)

Siden NoCellArray returnerer True hvis cellen ikke inneholder en matriseformel, kan din betinget format sette farge på cellen til rødt eller sett noen andre synlige tegn på at cellen ikke har den nødvendige matriseformel. Du kan også bruke følgende funksjon for å utføre samme oppgave:

Funksjon NoCellArray2 (RNG Som Range) Som boolsk
NoCellArray2 = (Evaluere (rng.FormulaArray) <> rng.Value)
End Function

En helt annen tilnærming er å legge noe til dine formler som tillater dem å lett bli gjenkjent som matriseformler. For eksempel kan du legge til følgende på slutten av noen av dine matriseformler:

+ N ("{")

Dette påvirker ikke beregningen på noen måte, men kan enkelt sjekkes for å se om det er der. Kontrollen kan gjøres ved en hendelseshåndterer, for eksempel følgende:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Hvis Høyre (Selection.FormulaArray, 5) = "(" "{" ")" Da
ActiveCell.Select
Selection.FormulaArray = ActiveCell.Formula
End If
End Sub

Vær oppmerksom på at behandleren sjekker om formelen ender med ("{") og, hvis den gjør det, tvinger formelen for å bli behandlet som en matriseformel. Det flotte med denne tilnærmingen er at du aldri trenger å trykke Ctrl + Shift + Enter på regnearket på nytt-hendelseshåndterer tar vare på den for deg. Dersom det på et tidspunkt du ønsker å konvertere formelen tilbake til en vanlig (ikke-array) versjon, rett og slett endre formelen slik at den ikke omfatter + N ("{").

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (473) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Kontroll for Riktig Entry av Array formler.