Doorgaan naar hoofdcontent

Excel: Some Reflections on Matrices in Names based on Text, Used in Graphs

Data:

A1:D12 filled with jan-dec:

jan
jan
jan
jan
feb
feb
feb
feb
mar
mar
mar
mar
apr
apr
apr
apr
may
may
may
may
jun
jun
jun
jun
jul
jul
jul
jul
aug
aug
aug
aug
sep
sep
sep
sep
oct
oct
oct
oct
nov
nov
nov
nov
dec
dec
dec
dec

Example A

AllText =A1:D12 &""

Adding &"" changes the whole name into one continuous array, reading from left to right and from top to bottom. We can use this in a graph (see Example A). We can not read the elements using the INDEX function like this:

=INDEX(AllText;13)

The INDEX does not see this as a continuous array.

We still could use:

=INDEX(AllText;4;3)

But there is nothing special about that.

Example B

ColumnText1 =A1:A12
ColumnText 2 =D1:D12

ColumnText Total = ColumnText 1: ColumnText 2

This name refers to the individual names including the columns between the individual names.

ColumnText Total = ColumnText 1: ColumnText 2 &""

Adding &""  changes the range into one continuous range, identical to AllText.

Example C

ColumnText Combi = ColumnText 1; ColumnText 2

This is automatically a continuous range without adding &"". We can use this in a graph but not as validation to a list.

Example D

With new data from A64:M76:

jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
jan
jan-jan
jan-feb
jan-mar
jan-apr
jan-may
jan-jun
jan-jul
jan-aug
jan-sep
jan-oct
jan-nov
jan-dec
feb
feb-jan
feb-feb
feb-mar
feb-apr
feb-may
feb-jun
feb-jul
feb-aug
feb-sep
feb-oct
feb-nov
feb-dec
mar
mar-jan
mar-feb
mar-mar
mar-apr
mar-may
mar-jun
mar-jul
mar-aug
mar-sep
mar-oct
mar-nov
mar-dec
apr
apr-jan
apr-feb
apr-mar
apr-apr
apr-may
apr-jun
apr-jul
apr-aug
apr-sep
apr-oct
apr-nov
apr-dec
may
may-jan
may-feb
may-mar
may-apr
may-may
may-jun
may-jul
may-aug
may-sep
may-oct
may-nov
may-dec
jun
jun-jan
jun-feb
jun-mar
jun-apr
jun-may
jun-jun
jun-jul
jun-aug
jun-sep
jun-oct
jun-nov
jun-dec
jul
jul-jan
jul-feb
jul-mar
jul-apr
jul-may
jul-jun
jul-jul
jul-aug
jul-sep
jul-oct
jul-nov
jul-dec
aug
aug-jan
aug-feb
aug-mar
aug-apr
aug-may
aug-jun
aug-jul
aug-aug
aug-sep
aug-oct
aug-nov
aug-dec
sep
sep-jan
sep-feb
sep-mar
sep-apr
sep-may
sep-jun
sep-jul
sep-aug
sep-sep
sep-oct
sep-nov
sep-dec
oct
oct-jan
oct-feb
oct-mar
oct-apr
oct-may
oct-jun
oct-jul
oct-aug
oct-sep
oct-oct
oct-nov
oct-dec
nov
nov-jan
nov-feb
nov-mar
nov-apr
nov-may
nov-jun
nov-jul
nov-aug
nov-sep
nov-oct
nov-nov
nov-dec
dec
dec-jan
dec-feb
dec-mar
dec-apr
dec-may
dec-jun
dec-jul
dec-aug
dec-sep
dec-oct
dec-nov
dec-dec

Ranges = All!$A$65:$A$76&"-"& All!$B$64:$M$64

From B65:M76 I filled in the matrix formula:

{=Ranges}


You can download the file NamesText.zip through this link:

Reacties

Populaire posts van deze blog

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik: Sub WachtwoordCrack()     Dim a As Integer, b As Integer, c As Integer, d As Integer, _     e As Integer, f As Integer, g As Integer, h As Integer, _  I As Integer, j As Integer, k, m As Integer     Dim begin As Date, eind As Date     Dim duur As String     Dim objSheet As Worksheet     begin = TimeValue(Time)     On Error Resume Next     For Each objSheet In Application.Worksheets         For a = 65 To 66: For b = 65 To 66: For c = 65 To 66             For d = 65 To 66: For e = 65 To 66: For f = 65 To 66                 For g = 65 To 66: For h = 65 To 66: For I = 65 To 66                     For j = 65 To 66: For k = 65 To 66: For m = 32 To 126                         ActiveSheet.Unprotect Chr(a) & Chr(b) & _   Chr(c) & Chr(d) & Chr(e) & Chr(f) & _   Chr(g) & Chr(h) &  Chr(I) & Chr(j) & C

Excel 2013: uniek aantal in draaitabel

Tot en met versie 2010 was het in Excel lastig om in een draaitabel een uniek aantal (DISTINCT COUNT) te tellen. We geven een voorbeeld op basis van een verkoperslijst. In deze lijst kunnen we zien welke verkopers welke artikelen hebben verkocht. Willen we nu in een draaitabel laten zien hoeveel artikelen een verkoper heeft verkocht, dan krijgen we wel de aantallen maar niet de unieke aantallen te zien. Om toch de unieke aantallen te laten zien, hebben we een aantal stappen nodig. Op het moment dat we de draaitabel invoegen, krijgen we in Excel 2013 dit dialoogvenster: Onderaan zien we daar een nieuwe optie: Deze gegevens toevoegen aan het gegevensmodel . Deze optie moeten we aanvinken, voor we op OK klikken. We krijgen dan een iets ander beeld dan normaal: Normaliter krijgen we alleen de veldnamen. Nu zien we er het woord Bereik boven staan. Voor het voorbeeld heb ik nu Verkoper toegevoegd aan Rijen en Artikelomschrijving aan Waarden . Het resultaat is identiek

Excel: laatste datum voor een groep, draaitabel of matrixformule?

Via een Excel groep krijg ik de vraag hoe je de laatste datum voor een groep er uit kunt pikken. We geven hier even de voorbeelddata: Voor zover ik kan zien zijn er in ieder geval twee mogelijkheden: met matrixformules en met een draaitabel . Oplossing: draaitabel We zullen het in dit voorbeeld maar even helemaal volgens de regels van de Excel kunst doen. Voor het maken van de draaitabel heb ik de lijst eerst omgezet naar een tabel ( INVOEGEN => DRAAITABEL ). De naam veranderen we dan even van Tabel1 in draaitabel . Vervolgens maken we de draaitabel. Via Waardeveldinstellingen kiezen we dan voor het datumveld voor Max en bij Getalnotatie voor Datum . De kopjes zetten we even om naar Naam en Laatste datum . Klaar. Oplossing: matrixformules Voor dat we de matrixformules gaan maken, creëren we eerst namen met flexibele bereiken: datum =VERSCHUIVING(Blad1!$B$2;0;0;AANTALARG(Blad1!$B:$B)-1;1) naam =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A