Einde van een Excel matrix variabel maken

[alhofwegen]

Hoi,

Ik heb al redelijk wat afgezocht op internet, maar ik kan nergens vinden hoe ik het bereik van een excel matrix variabel kan maken. Ik werk bijvoorbeeld in een excelblad met de formule:
=SNIJPUNT(B18:B53;C18:C53)

Maar in plaats van B53 moet deze formule kolom b tot de laatste ingevulde waarde pakken. Dat kan variëren tussen B30 en B300 ongeveer. Kan iemand mij helpen.


Relevante software en hardware die ik gebruik
Excel 2016


Wat ik al gevonden of geprobeerd heb
Ik heb geprobeerd de formule VERGELIJKEN te gebruiken, maar die lijkt niet te werken. Volgens mij ook niet als ik daar een aparte naam aan geef.

[battler]

Misschien dat je iets met vlookup kan?

[Herby]

Met vba kan je een macro maken die je een adres geeft van de laatst bewerkte cell, misschien kan je daar wat mee? http://excel.tips.net/T00...anged_in_a_Worksheet.html

[naitsoezn]

Als je verder geen informatie in de betreffende kolommen hebt staan, kun je B:B en C:C gebruiken. Anders zou je mogelijk iets met OFFSET i.c.m. COUNT kunnen doen.

[Boeryepes]

=SNIJPUNT(IF(B18:B300<>"";B18:B300;"",IF(B18:B300<>"";C18:C300;"")) en dan als array formule opslaan (CTRL SHIFT ENTER)

[naitsoezn]

Boeryepes schreef op donderdag 20 oktober 2016 @ 19:11:
=SNIJPUNT(IF(B18:B300<>"";B18:B300;"",IF(B18:B300<>"";C18:C300;"")) en dan als array formule opslaan (CTRL SHIFT ENTER)

Hoe wordt het einde daar variabel van? Als er info staat vanaf B301 / C301?

[Boeryepes]

niet. De topicstarter beschrijft dat de matrix ongeveer loopt tot B300, dus deze formule is variabel t/m B300 (of willekeurig welke limiet die je opgeeft in je formule)

[pedorus]

Maar SNIJPUNT negeert lege en zelfs tekstcellen gewoon, dus waarom is die IF dan nodig? Alternatief is er misschien iets met VERSCHUIVING nodig, maar dan moet TS aangeven hoe je erachter komt waar de range eindigt..

[Chief]

Mocht je het echt variabel maken, dan is het handigste om OFFSET icm COUNTIF te combineren om te bepalen waar de einde van de matrix te zoeken (je kan vast de Nederlandstalige equivalent vinden):

*Maak bv van cel A1 een hulpcel. Deze cel telt over hoeveel rijen je SNIJPUNT wil laten uitreken. Doe dat met: =COUNTIF(B18:B10000,<>"")
Deze functie telt op tenzij een cell leeg is en dus als het aantal toeneemt/afneemt dan zie je dat in cel A1.
* Doe hetzelfde voor cel A2 voor kolom C: =COUNTIF(C18:C10000,<>"")
* De functie wordt dan: =INTERCEPT(B18:OFFSET(B18,A1),C18:OFFSET(C18,A2))

[JPM85]

Wat ook kan, is een gedefineerde naam maken.
In de naam zet je de volgende formule

=OFFSET($B$18;0;0;COUNTA($B:$B);1)

COUNTA telt het aantal argumenten in kolom B, dus indien er meer cellen gevuld zijn in kolom B, na de functie COUNTA afsluiten met een negatien dus COUNTA($B:$B)-x (x= aantal cellen gevuld die niet mee moeten tellen).

Deze naam kan je in de functie gebruiken i.p.v. B18:B53. Zelfde geldt natuurlijk voor C18:C53

[Lustucru]

Beide oplossingen gaan mank op het voorkomen van lege cellen binnen de gewenste range:
{1,2,3,,,5,6} wordt dan {1,2,3,,}

Pedorus slaat de spijker op de kop:

dan moet TS aangeven hoe je erachter komt waar de range eindigt..

[alhofwegen]

Dank je wel! Uiteindelijk lijkt deze functie te werken:

=intercept($B$18:offset(B18;0;0;countif($B18:$B1000;"<>");1);$C$18:offset(C18;0;0;countif($C18:$C1000;"<>")))

[Tazzios]

Is het niet makkelijker om je bereik als Excel-tabel te definiëren? die groeit ook vanzelf mee.
Komt je formule er ook een stuk makkelijker uit te zien: =intercept(tNaampje1;tNaampje2)